First we need to download World and build the sample database.

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

Before we can do anything much with Theon we need an instance of XSDDB that we can use. In this walkthrough we create a specific dedicated instance to avoid conflict with any existing instance, however this is not necessary.

ttkm self launch upon walkthru into /tmp/self4walkthru using database=walkthruxsddb
ttkm create upon walkthru
rm -fr self4pagila

The above temporarily clones self in order to re-define the connection parameters so that instead of xsddb being created walkthruxsddb is created and associated with the walkthru LiveProfile. Then, after creating our custom instance of XSDDB, we can destroy our temporary ModelLibrary - the LiveProfile still exists and we use it in all further toolkit actions when necessary. If you just want to follow the default behaviour instead of above do

ttkm create

and below anywhere the self=walkthru option is used don’t use it.

Now we need to initialise a ModelLibrary for World and change directory into it so that further commands are simpler.

ttkm world launch into /tmp/world using self=walkthru
cd world

By default the physical database is named the same as the model so we don’t need to explicitly specify that.

We have now setup everything we need to work on the World sample database under Theon. The next step is to import the database.

ttkm import using self=walkthru

Above runs the import against the default LiveProfile in the current directory (ModelLibrary) - we have setup everything already so we don’t need to specify anything else.

Next we generate the SchemaTree.

ttkm export using self=walkthru tidy

This will create the schemat directory and content. The tidy option formats the XML to make it more human readable.

Now we can use the generated SchemaTree. First lets produce some documentation.

ttkm derive ddldoc complete

The generated pages can be found in derived/model/html.

Next we try re-installing a database based on the model. This should in principle be identical to the original, although for various reasons there will be some differences.

ttkm launch upon world2 aok using database=world2 self=walkthru
ttkm install upon world2

Above we create a non-default LiveProfile (called world2) to refer to the new clone database (also going to be called world2) and then we install this from a DDL file produced from the original exported SchemaTree.

We can now check for differences.

pg_dump -s world > /tmp/world.dump
pg_dump -s world2 > /tmp/world2.dump
diff /tmp/world.dump /tmp/world2.dump

You will note a few differences:

Now that we have a sufficiently valid SchemaTree in Theon representing the World database this part of the walkthrough is done. We would consider putting the SchemaTree under version control and we can then proceed to use Theon to manage the database from now on. We will come back to this model later when we look at the TheonCoupler and TheonUI.

Also we can now get rid of our XSDDB instance by doing:

ttkm delete upon walkthru

However, normally there is no harm in keeping it around.

Assuming we did delete it we need to be able to load the SchemaTree as a model instead of importing. First create an XSDDB again.

cd /tmp
ttkm self launch upon walkthru into /tmp/self4walkthru using database=walkthruxsddb
ttkm create upon walkthru
rm -fr self4walkthru

Then reload the SchemaTree from the World ModelLibrary:

cd world
# SHOULD NOT NEED TO DO THIS - IN INSTALLATION WOULD ALREADY EXIST
# BUT RELOAD NEEDS TO SAFELY REBUILD IF NOT
ttkm self derive ddlxsl complete into .
ttkm derive ddldata complete
# below ought to work instead of above as well
# ttkm self derive at ~/.theon/model/library ddlxsl complete
# END
ttkm reload upon walkthru

We can test the reload by exporting a SchemaTree somewhere else and then doing a diff, this should always be identical (ignoring live). If not a bug or unsupported feature is causing an issue.

ttkm export into ./new using self=walkthru tidy
diff -r schemat new/schemat

We will now manage the more complex database called Pagila.

First we need to download Pagila and build the sample database.

wget https://ftp.postgresql.org/pub/projects/pgFoundry/dbsamples/pagila/pagila/pagila-0.10.1.zip
unzip pagila-0.10.1.zip
createdb pagila
psql pagila < pagila-0.10.1/pagila-schema.sql
psql pagila < pagila-0.10.1/pagila-data.sql

When loading the schema you can safely ignore any errors about role "postgres" does not exist.

Assuming the XSDDB was not deleted we can do the same steps mostly as for World, if it was just create it again.

ttkm pagila launch into /tmp/pd using self=walkthru

The ModelLibrary directory name does not need to be the same as the contained model as shown above. However, if the ModelLibrary directory is in a search path it is the name of the directory that is checked for so it is sensible then to have the same name.

ttkm pd import at . using self=walkthru
ttkm pd export at . using self=walkthru tidy
ttkm pd derive at . ddldoc complete

Above we are running the commands in /tmp so we have to specify the model (but in this case actually the name of the directory it is held in). We need to add /tmp (as CWD) into the search/update path to find it.

The generated documentation pages can be found in pd/derived/model/html.

Next install a new database based on the model.

ttkm pd launch at . upon pd2 aok using database=thpagila self=walkthru
ttkm pd install upon pd2

Above the database is named differently to the model/profile, this doesn’t matter.