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.

ttkm create

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.

ttkm gather

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.

Dec 8, 2017