DB-DB subscriptions for live caches

Polyhedra 9.0 introduced a new feature, to help with producing scalable systems: selective data distribution between separate databases. This is implemented via a subscription mechanism, allowing one database to specify what information it wants to cache from another database. Thus, if a site has a number of separate Polyhedra-based SCADA systems (each running part of the production line, water distribution network or whatever), then it is easy to set up an overview database with information about, say, all the analog(ue) sensors in all the subsystems. To do this, you would simply create a record in the central database to tell it about each of the subsidiary databases:

insert into journalSession (id, service, Connect_Count)
                    values (102, '', 0);

insert into journalSession (id, service, Connect_Count)
                    values (103, '', 0);

insert into journalSession (id, service, Connect_Count)
                    values (104, '', 0);

-- (add similar entries for each subsidiary database. If the database
--  it running in fault-tolerant mode on a pair of servers, use a
--  comma-separated list of access points in the service field)

and then tell it what information you want to retrieve:
insert into JournalSubscription (Session, Source_Table_Name,   
                                 Id_Column_Name, Destination_Column_Names)

       values (102, 'analog',
               'source', 'id,description,actualvalue,timestamp');

-- (add similar entries for each table to which you want to subscribe,
-- and then repeat as necessary for each remote database)

This assumes, of course, that you have already defined the analog table in the central database. The table definition in the central database does not have to match the definition in the subsidiary databases - in fact, the names of the tables do not need to match! In this case, as multiple subscriptions are populating the same table, the primary key of the 'destination' table has to have an extra column (in this case, called source) which is automatically set in each record to the primary key of the journalSession entry to indicate which remote database the record came from.

The final step is to enable all the subscriptions:

update journalsession set enable=true; commit;

Assuming the other databases are all running, the central database will now be populated with information retrieved form the remote databases, and kept up to date as values change. If the connection to one of the remote databases is lost for any reason, the relevant information in the central database will be kept in a frozen form, and brought up to date when the connection is re-established.

This is a very powerful feature, and can be used for a variety of purposes. Another use case would be to have some information in a central database (a table of users and their passwords and access privileges, say) to which all the remote databases subscribe; the information would only need to be adjusted in one place and the change would automatically propagate to all databases that subscribe to it.

 The Polyhedra release kits contains another example illustrating the use of the subscription feature, and further details can be found in the description of the journalSession and journalSubscription tables in the utility classes manual