This recipe will run through the process of making simple changes to a model on the live physical database realising that model, then folding these back into a central repository. The physical database might be the actual live production server database for non-impacting changes (e.g. permissions, adding objects such as table columns), but more likely a clone development database when destructive or wider impacting changes are being made (those that affect different dependent applications for example).
This recipe can be run from anywhere on a machine with the necessary packages installed and suitable configuration of PostgreSQL (see configuration recipe).
(1) Create a local management database.
ttkm create aok
Note that this will delete entirely and re-create the management database if it already exists. Skip this step when you keep a persistent copy around.
(2) Reload the model we are working on.
ttkm hypatia reload
This reloads the installed package version and may take a while depending on the size of the model. The installed package may or may not be the same as the current version in a central repository. If in doubt (and this may become apparent with differencing later on) use the repository version and rebuild the reload data from the HEAD first.
svn checkout https://svn.theon.inf.ed.ac.uk/svn/theon/branches/alpha-schema-management/hypatia/library/hypatia /tmp/hypatia THEON_MODEL_XSLT_PATH=/usr/share/theon/self/xslt ttkm derive at /tmp/hypatia ddldata ttkm reload at /tmp/hypatia
(3) Make the actual changes on the real database.
psql -h infdb infdb GRANT SELECT ON project TO _th_ito3g; ALTER TABLE detail_3g ADD COLUMN dietary_needs TEXT;
(4) Construct a suitable LiveProfile to communicate with the real database.
ttkm hypatia launch at /tmp upon infdb aok using database=infdb host=infdb
(5) Import the current state of the real database (with the changes just made) into the model.
ttkm hypatia import upon infdb
For the above to work the local PostrgreSQL instance must be running with sufficient authentication to communicate with the real database and query the system catalogs. Normally this will be the case as the local PostgreSQL instance will be running as yourself with your credentials anyway.
(6) Re-gather the model. This is necessary to minimize the difference of the imported model, due to how it alters View definitions. If the changes you are making are actually to a View definition then you probably won’t want to do this, however changes in View definition should really be made from the Factory and not done live. If the model does not use the Factory this step can be skipped.
ttkm hypatia gather
Alternatively just export the relevant bits below.
(6) Export the model. Export into a temporary location so we can check the differences.
ttkm hypatia export into /tmp/hypatia.new
(7) Difference the SchemaTree.
diff -r /tmp/hypatia.new/schemat /usr/share/theon/model/library/hypatia/schemat
Or when using a checked out working copy (as made above):
diff -r /tmp/hypatia.new/schemat /tmp/hypatia/schemat
For the changes above you would see something like:
diff -r /tmp/hypatia.new/schemat/relation/detail_3g.xsd /usr/share/theon/model/library/hypatia/schemat/relation/detail_3g.xsd 847,862d846 < <xs:element id="att-3a53468e-ad76-44dc-954b-187ae40f2f54" name="dietary_needs" th:class="column" nillable="true"> < <xs:annotation> < <xs:documentation> < <th:name/> < <th:summary/> < <th:description/> < </xs:documentation> < <xs:appinfo> < <th:sequence>false</th:sequence> < <th:value/> < </xs:appinfo> < </xs:annotation> < <xs:simpleType> < <xs:restriction base="xs:string"/> < </xs:simpleType> < </xs:element> diff -r /tmp/hypatia.new/schemat/role/ito3g.xsd /usr/share/theon/model/library/hypatia/schemat/role/ito3g.xsd 361,364d360 < <th:capability id="cap-9ef37998-2e9f-4c36-b2c1-bf75c76a1bf9" refer="project"> < <th:selector xpath="project"/> < <th:include right="read"/> < </th:capability>
It is often much easier to see changes with meld.
It can be difficult to necessarily know that the change is correct by reviewing the SchemaTree above. For further reassurance generate and compare the resulting DDLs.
ttkm export at /tmp/hypatia ttkm derive at /tmp/hypatia ddl cat /tmp/hypatia/derived/model/ddl/*.sql > /tmp/ddl.new.sql cat /usr/share/theon/hypatia/model/ddl/*.sql > /tmp/ddl.old.sql diff /tmp/ddl.old.sql /tmp/ddl.new.sql 3781a3782 > dietary_needs TEXT, 23717a23574 > GRANT SELECT ON project TO _th_ito3g;
Sometimes it may be necessary to add a sort on both to eliminate ordering
changes from the diff.
Note with experience (!) you will know which bits of Schema are affected by the live changes and only need to export/derive/diff/commit the relevant fragments rather than always working with the entire model. Small models are sufficiently fast to process it isn’t worth bothering about though.
(8) Happy with the changes then export them into the working copy and commit.
cd /tmp/hypatia ttkm export svn commit schemat
(9) Might want to add some annotation, use the UI for this.
ttkm self server ui
Connect locally running browser to localhost:8000. Choose the "Dictionary" desktop. There should only be one model loaded (not necessarily if you work on multiple models and have a persistent management database, in which case select the correct model tab). Choose the "Entity" panel. Lookup "detail_3g". The new column should be listed. Either enter information here on the Info fields or select the "Attribute" panel and lookup "dietary_needs". Enter the formal name, summary and description.
cd /tmp/hypatia ttkm export svn diff schemat Index: schemat/relation/detail_3g.xsd =================================================================== --- schemat/relation/detail_3g.xsd (revision 12120) +++ schemat/relation/detail_3g.xsd (working copy) 850,852c850,852 < <th:name>Dietary Needs</th:name> < <th:summary>Use this to record any special dietary needs.</th:summary> < <th:description>Free form text field to hold any dietary needs, e.g. vegetarian, vegan, gluten free. Also use to record any allergies.</th:description> --- > <th:name/> > <th:summary/> > <th:description/>
Then commit.