Dual System DeawMGT Design and Implementation Notes

Data Transfer

Theory of Operation

1. The internal system actively contacts the shared system using a REST request:
   • Polls for updates on shared system, stored in the staging area on the shared system
   • Sends updates to the shared system, stored in the staging area on the internal system
2. The tables that are transferred are defined as sub-site settings in the sub-site settings file (See below)
   • The list of tables to transfer is different for each system
3. Database inserts, updates and deletes cause triggers to:
   • Create a record in the local staging area containing the complete record (serialized) just inserted, updated or inserted.
4. Anything else?

Overview Example Record Transfer

Internal System to Shared System

1. The internal system insert (or updates) a User record in the local database linthal114dev1

2. An insert (or update) trigger ins_User (or upd_Users) is activated, which creates a new record in the local staging area in table linthal114stg1.MirrorRecords (see record content below)

3. The event dispatcher on the internal system, started from crontab every minutes, starts the event handler MirrorOutEH caused by a rescheduling record in table Events.

4. The event handler MirrorOutEH reads the local staging area (linthal114stg1.MirrorRecords) using Mirroring::getNextRecord() and sends the record serialized to the shared system using the REST function Mirroring::restProcessMirrorRecord. 5 The REST function Mirroring::restProcessMirrorRecord calls Mirroring::processMirrorRecord and inserts (or updates) the appropriate Users record on the shared system.

5. The event handler MirrorOutEH calls the function Mirroring::setProcessed to update the field 'processStatus" in the linthal114stg1.MirrorRecords record to processed (2) or error (-1).

Shared System to Internal System

1. Shared system insert (or updates) a Comments record in the local database linthal114dev2

2. An insert (or update) trigger ins_Comments (or upd_Comments) is activated, which creates a new record in the local staging area in table linthal114stg2.MirrorRecords (see record content below)

3. The event dispatcher on the internal system, started from crontab every minutes, starts the event handler MirrorInEH caused by a rescheduling record in table Events.

4. The event handler MirrorInEH reads the next serialized record from the remote staging area (linthal114stg2.MirrorRecords) calling the REST function Mirroring::restGetNextRecord() and calls the function Mirroring::processMirrorRecord. 5 The function Mirroring::processMirrorRecord inserts (or updates) the appropriate Comments record on the internal system.

5. The event handler MirrorOutEH calls the REST function Mirroring::restSetProcessed to update the field 'processStatus" in the linthal114stg2.MirrorRecords record to processed (2) or error (-1).

Databases and Staging Area

Databases and Configuration

Each system has two databases:

• {site}{ver}{world}{subSiteId} - linthal114dev1 - Application database

• {site}{ver}{world}{subSiteId}stg - linthal114dev1stg - Staging are database

The list of tables that are transferred are defined in the SiteSettings:

• cfg/{site}/site/config/SubSiteSettings1.php
• cfg/{site}/site/config/SubSiteSettings2.php

      $_CFG['DbMirroring']['Tables'] = array( ..list of table names.. );

The mirrorig feature is switched on in InstanceSettings on both systems:

•     $_CFG[ 'Feature' ][ 'DbMirroring'  ] = true;

The sub-site settings are set in InstanceSettings on both systems, to 1 on the internal:

•     $_CFG['SubSiteId']          = 1;

and to 2 on the shared system:

•     $_CFG['SubSiteId']          = 2;

The MirrorIn and MirrorOut events must be enabled, and the url to the shared system must be defined in the InstanceSettings of the internal system.

•     $_CFG['EventDaemon']['EnableMap'][EventTypeRef_MirrorIn] = 1;
      $_CFG['EventDaemon']['EnableMap'][EventTypeRef_MirrorOut] = 1;
      $_CFG['SubSiteSettings']['RemoteBaseURL'] = 'http://{host}/{drawmgt path}/';  # E.g. http://narvi.softxs.ch/dev/linthal114dev2/

Database Triggers

• For every tables listed in $_CFG['DbVersioning']['Tables'] or $_CFG$_CFG['DbMirroring']['Tables']['DbMirroring']['Tables'] three triggers are generated for insert, update and delete with names ins_{table}, upd_{table} and del_{table} repectively. The triggers are shared by the versioning and mirroring jobs (only one insert , update and delete trigger can be defined).

• There is no database triggers in the staging area.

• genMeta -b generates trigger code in var/{site}/{instance}/schema/drawmgt-versioning-trigger.sql. This code can be restarted any time, it recreates the triggers. All sub-site have their own script, because the set of the mirrored tables are different.

Staging Database

The staging area has a single table MirrorRecords:

+--------------------+---------------------+------+-----+---------+----------------+
| Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| id                 | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| createTimestamp    | datetime            | NO   |     | NULL    |                |
| processStatus      | int(11)             | NO   | MUL | NULL    |                |
| statusTimestamp    | datetime            | NO   |     | NULL    |                |
| tableName          | varchar(64)         | NO   |     | NULL    |                |
| operation          | char(1)             | NO   |     | NULL    |                |
| keyWhere           | varchar(255)        | NO   |     | NULL    |                |
| phpSerializeRecord | text                | YES  |     | NULL    |                |
+--------------------+---------------------+------+-----+---------+----------------+

• id - serial value, determine the sequence of the records
• createTimestamp - time of the creation of the record
• processStatus - status of the record
  • 0 - not processed, just inserted
  • 1 - already read
  • 2 - processed (the records are not deleted)
  • -1 - error occurred, needs manual intervention
• statusTimestamp time of the last processStatus change
• tableName - name of the table where the record was inserted, updated deleted
• operation - insert, update or delete flag ('i', 'u', 'd')
• keyWhere - where clause, created from the primary key of the record, can be used immediately in SQL commands
• phpSerializeRecord - the inserted or updated record (empty if operation == 'd') in form of a php serialized associative array

Main Classes

Set the debug flag in these classes to see what is happening:

• app/config/RestMap.php -- look for the mirror transactions. See below

• app/event/handlers/MirrorInEH.php -- see the example above

• app/event/handlers/MirrorOutEH.php -- see the example above

• lib/venture/util/Mirroring.php -- see function headers

Rest Requests

Mirroring Transactions

The following is a list of the REST URLs and what they are used for:

• mirror/getnext - Mirroring::restGetNextRecord - returns the next record serialized from staging area, see function header in Mirroring.php

• mirror/{?}/setproc - Mirroring::restSetProcessed - sets process status of the MirrorRecords record with id as REST parameter to the value of CGI variable 'status'. See function header in Mirroring.php

• mirror/procmirr - Mirroring::restProcessMirrorRecord - process the serialized MirrorRecords record got as HTTP POST raw data. See function header in Mirroring.php.

Authorization of REST transactions

• to be completed (Tibor leave empty this for now)

• Plan to use an asymmetric key

Configuration Issues

Software Packaged

The following packages are required (in addition to normal DrawMGT)?

• pecl_http

Testing Issues

• If a php syntax error or other fatal error occurs, than the state of the appropriate Events record stays 'Running' (6). It must be set back to 'Scheduled' (3)

  • update Events set stateId = 3 where eventTypeId =
      ( select eventTypeId from EventTypeRef where define = 'MirrorIn');
    update Events set stateId = 3 where eventTypeId =
      ( select eventTypeId from EventTypeRef where define = 'MirrorOut');

• If an error occurs processing the record from table MirrorRecords in the staging area, its processStatus is set to -1. After the error conditions disappear set the process Status back to 0 on the appropriate system.

  • -- If the original record was changed on the internal system
    update linthal114dev1.MirrorRecords set processStatus = 0 where processStatus = -1;
    -- If the original record was changed on the shared system
    update linthal114dev2.MirrorRecords set processStatus = 0 where processStatus = -1;

• Set the DEBUG defines to true in the classes above to have lot of information in the logs.

Show Next Record PHP Script

For displaying the next record to be processed. Calls the getnext REST transaction. Can be started on any system, only the url must be correct, but the main goal is to simulate the normal operation, so start it on the internal system with the url to the shared system.

Sets the processStatus field in the staging database to read, but this doesn' matter (causes a warning only, to state, that the record was already read but the processing was not completed). If you see the associated array of the originally inserted, updated database record

<?php
  $r = new HttpRequest('http://narvi.softxs.ch/dev/linthal114dev2/mirror/getnext', HttpRequest::METH_GET);
  // $r->addQueryData(array('category' => 3));
  try {
    $r->send();
    if ($r->getResponseCode() == 200) {
      $content = $r->getResponseBody();
      print "content:$content\n";
      $record = unserialize($content);
      var_export( $record );
      $dbrecord = unserialize($record['phpSerializeRecord']);
      var_export( $dbrecord );
    } else {
      print( "response code: " . $r->getResponseCode() . "\n" );
    }
  } catch (HttpException $ex) {
    echo $ex;
  }
?>