This recipe will run through the process of adding a new feed and couple to an existing Theon managed database. This recipe will work on a development copy.
This recipe can be run from anywhere on a machine with the necessary packages installed and suitable configuration of PostgreSQL (see configuration recipe).
(1) Install the standard packages for the ModelLibrary we are using.
rpm -U \ hypatia-1.0.12174-1.noarch \ hypatia-theon-library-1.0.12159-1.noarch # the ModelLibrary\ hypatia-theon-library-devel-1.0.12159-1.noarch # development components of the ML \ hypatia-theon-library-ui-1.0.12159-1.noarch # (optional) UI components of the ML
(2) Create a local development instance.
ttkm hypatia install
(3) Create the management database. We will use a custom instance so create a LiveProfile
for that first. Below assume a default management database is in place and can be used to
bootstrap the process, if not do ttkm create first and ttkm delete after.
ttkm self attach upon hypatia using database=hypxsddb host=/disk/scratch ttkm create upon hypatia
(4) Load the ModelLibrary.
ttkm hypatia reload upon hypatia
(5) Launch a working copy of the ModelLibrary to hold the changes, associate the management database profile with it to save having to specify it, then change into it and do a test export to check reload worked.
ttkm hypatia launch at /tmp using theon=hypatia cd /tmp/hypatia ttkm export
(6) Start up the UI to make all the necessary changes. Note at the moment we have to handcraft a suitable configuration file to use. This server is being used remotely rather than on localhost hence the hostname.
cat >ui.conf <<EOF
config = {
"session_secure": False,
"database" : {
"dbname" : "hypxsddb",
"host" : "/disk/scratch"
},
"metadata" : {
"/api": [ "/usr/share/theon/self/ui/data", ],
}
}
EOF
ttkm self server ui on babel using config=ui.confNavigate a remotely running browser to babel:8000 and follow the following
steps.
Add the feed load table.
Open the "Dictionary" desktop.
On the first panel ("Model") ensure the right model is shown.
Add the following rows in the "Entities" box:
Name[Realised] Type
webmark_tsp_apply_feed_3g Table
Open the "Entity" panel.
Query for the "webmark_tsp_apply_feed_3g" record.
Add the following rows in the "Attributes" box:
Position Name[Realised] Type Content Size
1 matric Column Text
2 myname Column Text
3 vacancy Column Text
4 desiredhrs Column Text 3
5 wmusername Column Text
6 wmhash Column TextAdd the feed view - this is being used here to aggregate and tie-in data prior to being sync’ed to destination table(s).
Open the "Dictionary" desktop.
On the first panel ("Model") ensure the right model is shown.
Add the following rows in the "Entities" box:
Name[Realised] Type
webmark_tsp_course_duty_feed_3g View
Open the "Entity" panel.
Query for the "webmark_tsp_course_duty_feed_3g" record.
Enter the following definition in the "Value" field:
SELECT
c.session,
c.course,
c.type,
p.person,
f.vacancy AS comment,
'Applying' AS status,
c.duty,
COUNT(*) AS load,
f.wmhash
FROM (
SELECT wmhash, matric, UNNEST(STRING_TO_ARRAY(vacancy, '|')) AS vacancy FROM webmark_tsp_apply_feed_3g
) AS f
INNER JOIN person_3g p ON (
UPPER(f.matric) = p.euclid_student_code
)
INNER JOIN course_duty_allocation_3g c ON (
REGEXP_REPLACE(f.vacancy, '^.*\([A-Z0-9-]+\.([0-9]+).*\)$', '\1')::INTEGER = c.duty_instance
)
GROUP BY
c.session,
c.course,
c.type,
p.person,
f.vacancy,
c.duty,
f.wmhash
Add the following rows in the "Attributes" box:
Position Name[Realised] Type Content Size
1 session Column Text 2
2 course Column Text 8
3 type Column Text 26
4 person Column Text 18
5 comment Column Text 160
6 status Column Text
7 duty Column Number/Integer
8 load Column Number/Decimal 12.4
9 wmhash Column TextAdd a column to the target table to hold the sync handle.
Open the "Dictionary" desktop.
On the first panel ("Model") ensure the right model is shown.
Open the "Entity" panel.
Query for the "course_duty_3g" record.
Add the following rows in the "Attributes" box:
Position Name[Realised] Type Content
31 webmark_apply_code Column TextNow define the new Stream and Couple.
Open the "Streaming" desktop.
On the first panel ("Model") ensure the right model is shown.
Add the following rows in the "Streams" box:
Stream-Label Origin ? Feed Entity Source Format Name
wbmrk_tsp WBMRK webmark_tsp_apply_feed_3g Pipe CSV wbmrk_tsp
Open the "Stream" panel.
Query for the "wbmrk_tsp" record.
Tick the "Incremental?" field.
Add the following rows in the "Couples" box:
Position Source Entity Target Entity Name [Info]
1 webmark_tsp_course_duty_feed_3g course_duty_3g course_duty
Open the "Couple" panel and enter and save the following for the couple:
Source Origin Value = 'WBMRK'
Then add two new rows in the "Bonds" box:
Position Type
1 Handle
2 Column
Open the "Bond" panel and add rows in the "Pairs" box for the couple
as below:
For "Handle":
Position Source Column Target Column Source Value (SQL)
1 session session
2 course course
3 type type
4 person person
5 duty duty
6 wmhash webmark_apply_code
For "Column":
Position Source Column Target Column Source Value (SQL)
1 status status @::_th_course_duty_3g_status_enum
2 load load
3 comment commentControl-C the server process.
(7) Now export the changes and upgrade the database (by comparing against the installation package). First define a LiveProfile to connect to the database.
ttkm export ttkm attach using database=hypatia host=/disk/scratch ttkm upgrade using current=/usr/share/theon/model/library/hypatia
(8) Build the TheonCoupler configuration and test the stream and couple work with some sample data to load. Development database will need populated with live or test data for functionality test.
ttkm derive coupler ttkm stream agent wbmrk_tsp refresh from /tmp/tsp.csv
(9) If using a central repository would now commit back changes in schemat
and then use that to upgrade the live production database in the same way.