This recipe will run through the process of converting to Theon management the World demonstration database. This example will assume that it will be a personal database so that Theon can be added as an extension module to the live database.

This recipe can be run from anywhere on a machine with the necessary packages installed and suitable configuration of PostgreSQL (see configuration recipe).

(1) Download World and build the sample database.

cd /tmp
wget http://pgfoundry.org/frs/download.php/527/world-1.0.tar.gz
tar zxvf world-1.0.tar.gz
createdb world
psql world < dbsamples-0.1/world/world.sql

(2) Install Theon Schema Management into the World database as an extension module. First pre-requisite extensions need installed. These must be installed in the public schema (default).

psql world -c "CREATE EXTENSION dblink;"
psql world -c "CREATE EXTENSION file_fdw;"
psql world -c "CREATE EXTENSION pgcrypto;"
psql world -c "CREATE EXTENSION \"uuid-ossp\";"

Now install the Theon extension module. The schema search path must include public when running this, however subsequently this is no longer necessary.

psql world -c "CREATE EXTENSION theon;"

(3) Start a new ModelLibrary to represent World within Theon.

ttkm world launch at .

(4) Change into the created ModelLibrary directory so we don’t need to prefix actions with the relevant model name.

cd world

(5) Create a suitable custom LiveProfile to use for connecting to the Theon Schema Management catalog within the World database. We will call this world to keep things consistent. Within the World database the Theon extension will have been installed under the theon schema so this needs specified in the connnection options.

This is a bit chicken and egg as we need a Theon Schema Management catalog in order to create a LiveProfile for a Theon Schema Management catalog. So first create the default standard one in its own database, create the custom profile for the one embedded in our *World" database, then remove it. Note that create and delete actions below are destructive and we are assuming there is not one already in use (in which case the create and delete actions would not be necessary).

ttkm create aok
ttkm self attach upon world using database=world host=/disk/scratch schema=theon
ttkm delete aok

(6) Now we can use our own Theon* Schema Management catalog within the World database to create a suitable default LiveProfile to use for connecting to the World database itself. We have to specify the LiveProfile to use for our own Theon* Schema Management catalog.

ttkm attach using database=world host=/disk/scratch self=world

(7) Set default LiveProfile for the ModelLibrary. We do this so that whenever we use this ModelLibrary for managaing World we don’t have to always specify the LiveProfile for the Theon Schema Management catalog (as in doing self=world above) and instead the LiveProfile associated with the ModelLibrary is used as the default. This is done by a configuration option so (re)launch in order to set this.

ttkm launch using theon=world

(8) Load the existing World database schema into Theon.

ttkm import

(9) Export the SchemaTree for world.

ttkm export

This will create content in the schemat directory.

(10) Now we can do a test build of another World database from our SchemaTree. This will be a "managed" instance as it will contain the current SchemaTree for the model within it. Will call this OtherWorld. First derive the installation DDL.

ttkm derive ddl

This will put content in "derived/model/ddl". Now create an alternative LiveProfile for the new database.

ttkm attach upon otherworld using database=otherworld host=/disk/scratch

Install the new database.

ttkm install upon otherworld

(11) Now lets make a live change in World and then upgrade OtherWorld so it corresponds. First make the change.

psql world -c "ALTER TABLE city ADD COLUMN area FLOAT;"

Re-import, export.

ttkm import
ttkm export

Now do the upgrade. This will build a "differential DDL" between the SchemaTree in OtherWorld and the SchemaTree we have just exported from World and then apply that.

ttkm upgrade upon otherworld aok

Note that there are considerable limitations in what upgrade natively supports doing, see Appendix. Where operations are not supported install must be used instead with, if necessary, manual data dump/restore.

(12) Now will make a similar change via the UI and upgrade OtherWorld. First start the UI server, but at the moment we need to handcraft a suitable config file that points at the Theon Schema Management Database" in *World.

cat >ui.conf <<EOF
config = {
  "session_secure": False,
  "database" : {
    "dbname" : "world",
    "host" : "/disk/scratch",
    "options" : "--search_path=theon"
  },
  "metadata" : {
    "/api": [ "/usr/share/theon/self/ui/data", ],
  }
}
EOF
ttkm self server ui using config=ui.conf

Navigate a locally running browser to localhost:8000 and follow the following steps to add a column.

Open the "Dictionary" desktop.
On the first panel ("Model") ensure the right model is shown.
Open the "Entity" panel.
Query for the "city" record.
Add the following rows in the "Attributes" box:
  Position      Name[Realised]  Type    Content
  7             lastupdate      Column  Date/Time

Control-C the server process.

Export the changes, do the upgrade and check.

ttkm export
ttkm upgrade upon otherworld aok
psql otherworld -c "\d city"

(13) Note that the original database World will now be inconsistent as the change has not been applied to that (the previous change was made live and imported so it was unnecessary to run an upgrade, and in fact would have caused an error as the change had already been made). We cannot just upgrade World as it was not installed as a Theon managed database, so we should turn it into that now so that it can then always be upgraded in-situ from changes made by the UI and hence the SchemaTree. One way would be to simply destroy it and then install it again from Theon then add the theon extension. Below is an example of not doing this. The process is to simply apply the upgrade for OtherWorld to World to make it consistent - by doing the upgrade it will be converted into a Theon managed database (the upgrade needs to apply some kind of change, but we have this through the added column above). First we need to make a clean SchemaTree to update from, so we need to remove the column we added in the UI and export this to a temporary location.

ttkm self server ui using config=ui.conf

Navigate a locally running browser to localhost:8000 and follow the following steps to add a column.

Open the "Dictionary" desktop.
On the first panel ("Model") ensure the right model is shown.
Open the "Entity" panel.
Query for the "city" record.
Delete the following row from the "Attributes" box:
  Position      Name[Realised]  Type    Content
  7             lastupdate      Column  Date/Time

Create a temporary new ModelLibrary to contain the current state.

ttkm launch into /tmp/oldworld

Export the current state into the temporary ModelLibrary.

ttkm export into /tmp/oldworld

Upgrade World by comoparing the above original SchemaTree to the current one which was set after the previous change. This will add the column and also add the Theon meta data so that World can be managed directly in the future (checked below).

ttkm upgrade using current=/tmp/oldworld
psql world -c "\d _th_theon"

Now re-import, re-export and do a schema diff check for reassurance.

ttkm import
ttkm export
diff -qr schemat /tmp/oldworld/schemat

(14) Build the UI data, connect and browse.

ttkm derive ui
cat >world.conf <<EOF
config = {
  "session_secure": False,
  "database" : {
    "dbname" : "world",
    "host" : "/disk/scratch",
  },
  "metadata" : {
    "/api": [ "/tmp/world/derived/ui/data", ],
  }
}
EOF
ttkm server ui using config=world.conf

Navigate a locally running browser to localhost:8000 and choose the Browser to peruse table content.