The first part of this series discussed why you might use Theon as a Schema Management Tool for your PostgreSQL database. The second part part covered the necessary packages and installation. This final part will be an introductory tutorial.
In the first part of this tutorial we will create a database, manage it in Theon, make some changes, and use the factory to template some schema. In the second part we will use TheonCoupler to load data automatically into the database. In the third part we will use TheonUI and create a desktop for the database. In the fourth and final part we will package and distribute the result.
Before doing anything note that we need a running PostgreSQL installation we have access on to create databases with. In this tutorial we use on one localhost with /disk/scratch as the socket connection directory.
First we will create a simple database with one table. This will be used to hold installed packages.
createdb packages psql packages <<EOF CREATE TABLE package ( name TEXT, version TEXT, release TEXT, architecture TEXT ); EOF
Create the Theon management database.
Now create a directory to hold our ModelLibrary.
cd /tmp ttkm packages launch at . cd packages
Import the database we have just created as the schema. In order to do this we need to create a default LiveProfile with the necessary connection details.
ttkm attach using database=packages host=/disk/scratch ttkm import
Next test what we imported by exporting the schema.
ttkm export cat schemat/relation/package.xsd
The above is the XML representation of the table we created. We will properly check now by dropping the original database then, by deriving the necessary SQL, recreate it.
dropdb packages ttkm install psql packages -c "\d" List of relations Schema | Name | Type | Owner --------+-----------+-------+------- public | _th_theon | table | timc public | package | table | timc (2 rows)
Note the additional metadata table Theon adds. This is not mandatory(and can be removed) but it is useful for subsequent upgrades as we will see shortly.
Next we will define a view using the factory. To do this we first create the necessary hierarchy and add a template definition.
mkdir -p factory/templates cat >factory/templates/archcount.fat <<EOF %%include "relation/view.grg" %%equate relation_ViewName "archcount" %%equate relation_ViewValueBlock "SQL" %%block SQL SELECT architecture AS arch, COUNT(*) AS total FROM package GROUP BY arch %%end EOF
Then we process the factory templates and load them into the Theon management database.
Next export the changed schema and check.
ttkm export cat schemat/relation/archcount.xsd
Now we need to upgrade the installed database to add the view. This is automatic as we have the metadata table, manual machinations would be required otherwise.
ttkm upgrade psql packages -c "\d" List of relations Schema | Name | Type | Owner --------+-----------+-------+------- public | _th_theon | table | timc public | archcount | view | timc public | package | table | timc (2 rows)
The view has been successfully added.
Finally we want to add an extra column in the package table for the build time. There are various ways of doing this, here we will alter the live database and re-import the change so the schema in Theon is kept up-to-date. However, in order to keep consistent metadata, we will drop the column and then re-create the column by upgrade. This is necessary in this case as we are working with a development database which is also the live database.
psql packages -c "ALTER TABLE package ADD COLUMN buildtime INTEGER;" ttkm import ttkm export psql packages -c "ALTER TABLE package DROP COLUMN buildtime;" ttkm upgrade psql packages -c "\d package" Table "public.package" Column | Type | Modifiers --------------+---------+----------- name | text | version | text | release | text | architecture | text | buildtime | integer |
The column has been successfully added back by upgrade.
Now it is time to get some data into the database.