= Dual System DrawMGT 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 1. 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 * All reference data are transferred from the internal to the shared system * Drawings, Comments created on the shared system are transferred to the internal system but NOT from internal to the shared system * Table Users handled specially: created and updated records are transferred from the internal to the shared system and '''password''' related fields are transferred from the shared system to the internal system to have unified login on the two systems. 1. 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. 1. Placeholder Drawings records are created on the external system for all drawingCode of internal Drawings records * Allow unique Drawings.drawingCode values * Allow to get next serial numbers 1. Placeholder Drawings records are created on the external system for all serial code of internal Drawings records * Allow unique Drawings.serialCodeTypeId, Drawings.serialCodeNo, Drawings.serialCodePartvalues * Allow to get next serial code numbers == Overview Example Record Transfer == === Internal System to Shared System === The operation is explained on an example: a Users record insert or (update) on the internal system 1. The internal system insert (or updates) a Users record in the local database linthal114dev1 2. An insert (or update) trigger '''ins_Users''' (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. Note, that mirroring is disabled by using the global SQL variable '''@mirroringDisabled''' during this operation to avoid mirroring back to the internal system. 6. 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 === The operation is explained on an example: a Comments record insert or (update) on the shared system 1. Shared system inserts (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. Note, that mirroring is disabled by using the global SQL variable '''@mirroringDisabled''' during this operation to avoid mirroring back to the shared system. 6. 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 = Other Issues = == Uniqueness of Drawing Codes == It should be checked, that the value of Drawings.drawingCode is system-wide unique on new or updated Drawings record. * The function '''DrawingsSet::doesDrawingCodeExist''' performs unique check locally on both systems * The function '''DrawingsSet::doesDrawingCodeExist''' on the internal system calls the REST URL document/unique ('''DrawingsSet::restDoesDrawingCodeExist''') to check drawingCode uniqueness and to create placeholder document on the external system == Uniqueness of Serial Codes == It should be checked, that the value set of Drawings.serialCodeTypeId, Drawings.serialCodeNo, Drawings.serialCodePartvalues is system-wide unique on new or updated Drawings record. * The function '''DrawingsSet::getDrawingIdBySerialCodeTypeIdNo''' performs unique check locally on both systems * The function '''DrawingsSet::getDrawingIdBySerialCodeTypeIdNo''' on the internal system calls the REST URL document/check/serial ('''DrawingsSet::restDoesSerialCodeExist''') to check serial code uniqueness and to create placeholder document on the external system == Placeholder Document == The placeholder documents, mentioned above have only the following fields set: * negative drawingId - to distinguish easily between normal and placeholder doczments * negative contractId - to avoid to see it on any list (nobody has rights in that contract) * drawingCode or Drawings.serialCodeTypeId, Drawings.serialCodeNo, Drawings.serialCodePartvalues * not null fields = 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. * '''document/unique - DrawingsSet::restDoesDrawingCodeExist''' - check drawingCode uniqueness and create placeholder document on the external system * '''document/check/serial - DrawingsSet::restDoesSerialCodeExist''' - check serial code uniqueness and create placeholder document on the external system == Authorization of REST transactions == * The REST transactions above are set to REST_SYSTEM. The caller's IP address is checked currently * Plan to use an asymmetric key = Configuration Issues = See [[SetupDualDrawMGTSystem|Setting Up a Dual DrawMGT System]]. == Software Packages == 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 {{{ 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; } ?> }}}