RECASTDB is used to copy structure information and data in selected tables from one database to another.
See Transferring the Database Structure in the DP4 Developer's Manual for further information about using this program to transfer database structure, and Transferring Data for information on using it to transfer ordinary database data.
From 4.620 RECASTDB can also be used to synchronise a "local" or "satellite" database (for example one stored on a Windows CE PDA) with a "master" database (for example one stored on a Web Server accessed via a Dial up internet connection). The term "satellite" is preferred to "local" because in some modes of operation, the "local" database will actually be remote! With synchronisation data transfer may be two way. In what follows "upload" refers to data transfer from the "satellite" to the "master" database, and "download" refers to the reverse operation. The "source" database is the database from which data is currently being copied, and the "target" database the one to which this data is copied, or from which it is deleted. The terms "upload" and "download" are used regardless of the actual method of data transfer − both uploads and downloads can be achieved by running RECASTDB at either side of a network connection - in one case end the data will be pushed to the other,in the other it will be pulled from the other. Wherever possible you should choose to push data as this will generally be much faster and consume less network resource.
Synchronisation can be controlled using information contained in a configuration file, the name of which is specified as a command line option when RECASTDB is started. It is envisaged that several different configuration files might be created to synchronise databases in different ways.
The synchronisation facilities of RECASTDB are not currently intended to cope with the situation where both databases may contain inconsistent updates to the same table.
Prior to release 4.620 the method used to reconcile two databases is as follows:
The method for deleting from a table is to read all the records from the table on the database to be updated. For each record read RECASTDB attempts to read the corresponding record on the source database. If the record is not found the record is deleted from the target database.
The method for updating a table is to read all the records from the source database. For each record read RECASTDB attempts to read the corresponding record on the target database. If the record is not found, or is different in any way the record is posted to the target database.
If RECASTDB is being used to synchronise databases held on different machines accessed via a slow network these methods can cause RECASTDB to run very slowly. In 4.620 there are options that cause RECASTDB to use alternate algorithms that may well result in its running much faster.
In 4.620 RECASTDB may be able to use timestamp information to "filter" the records in the tables being processed. For example after a "download" table has intially been synchronised with the satellite database, RECASTDB will optionally record the timestamp of the master database at the start of synchronisation on the satellite database. Next time RECASTDB is run it will optionally fetch this information again and automatically exclude from consideration records on the source (master) database whose timestamp indicates they have not changed since the last synchronisation. A similar facility is available for "upload" tables. In this case RECASTDB records the timestamp of the satellite database. During upload records unchanged on the satellite database since the last synchronisation are excluded from consideration.
Unfortunately, the need to maintain compatibility with previous releases of DP4 means that the same field has to be used for remembering both the "upload" and "download" timestamp. This means that for each table timestamp information can only be used for one of "upload" and "download". Also you cannot use the timestamp option for download tables if there is more than one "master" database. The timestamp option will not work properly if ADCs that do not pass fetch() operations on to the DP4 database manager are loaded.
In the unlikely event of a catastrophic database loss on the master machine the timestamp information on the satellites will be useless until it is reset from a new copy of the master database. In this case you will need to run a special configuration file that ignores the timestamp information when synchronising but sets it once synchronisation is completed. You should rigorously enforce a secure backup strategy to ensure the master database cannnot be lost.
For processing of deletions you can specify an algorithm that works by reading data from the source database. Records that match a specific condition are unconditionally deleted from the target database. Note that for this algorithm to be useful your DP4 application must initially avoid deleting data on the source database. Typically, instead of deleting you would update the record on the source database setting a boolean field such as "deleted" to TRUE. The condition to delete would simply be "deleted" (the syntax used is the same as for QAB and Report Writer). RECASTDB will then delete all the records that have the "deleted" flag TRUE from the target database. If the target database is a "master" database RECASTDB will optionally delete the records from the source "satellite" database as well once they have been deleted from the target "master" database. If the source database is a "master" database RECASTDB will not delete the data from it, because it is assumed that there will be many "satellite" databases requiring updating. (You would need to keep track of which "satellite" databases have been updated yourself and only delete the records from the "master" database once all the satellites have been updated.)
For processing of updates there are several new possibilities:
You can specify that data transfer should take place unconditionally using a "−copyall" option. This avoids the requirement for RECASTDB to test the record on the target database. In any case the DP4 database manager would perform a similar test prior to actually making the update. In the case where the target database is initially empty, or nearly so, or where a large proportion of the records have changed this option may reduce the time needed to transfer the table by 50%. If the test would have eliminated most transfers using this option will not necessarily be beneficial. However, it will probably still save time if the database being updated is accessed over a slow network.
You can specify that data transfer should take place based on a condition (again using QAB syntax). In this mode of operation, which can be combined with timestamp filtering, no test is done on the target database - if the condition is satisfied the record will be transferred. A typical use of conditions might be to place additional limits on the data to be transferred. For example if RECASTDB is being used to synchronise a PLU table with handheld devices it might be desirable only to transfer a subset of the price information to each handheld, perhaps based on a department code. This information would have to be recorded in the [settings] section of a configuration file. This is read automatically by the formula processor built into RECASTDB. For example if you specified a condition such as DEP_CODE=CFG_DEP_CODE, where DEP_CODE is a name of a field in the table being transferred and CFG_DEP_CODE is not, the formula processor will look for a string of the form CFG_DEP_CODE=xxxx in the [settings] section. Currently [settings] variables are always strings; they can be converted to numbers using the numtochar() function in the formula processor. Normally the configuration file is read from the machine where RECASTDB is being run. If you want to download data from the master to the satellite using information from the satellite configuration file you need to use the −remote_config option instead of the −config option. You could obtain the name of a configuration file to use by creating a remote connection from a C or QAB program and querying the database.
You can specify a field, and a formula for it. If a record is succesfully transferred to the target database the records is also posted back to the source database with the field set to the new value. You might use this to exclude the record from consideration at the next synchronisation (supposing that you had specified a condition on the transfer that involved this field).
For both update and delete operations you can specify that RECASTDB should read he table on a specified index instead of the main index. In this case you must provide a formula for the first keyfield in the selected index. Only records with this key value will be fetched from the source database. This option may allow you to make a futher improvement in performance by reducing the number of records to be read from the source database. For example rather than using the "deleted" field in a condition as in the example above you might choose to set up a secondary index on the "deleted" field. However, this should only be done if absolutely necessary - in general even quite large tables can be transferred very quickly, and the overhead of maintaining extra indexes may offset any saving in search time.
A RECASTDB configuration file allows you to gain a very high degree of control over the operation of RECASTDB. In many cases using a configuration file will allow you to synchronise data between two different copies of a database without the need for writing special programs.
The configuration files are formatted like a Windows INI file. There are a number of sections, demoted by headings in square brackets, and each section contains entries in the form key=value.
Global options are specified in the [defaults] section. For each table that is copied RECASTDB also looks in the [tablename.download] or [tablename.upload] sections for table specific settings. Default values for several of these options are taken from the [upload] and [download] sections.
| appl=1 | Omits the structure transfer phase of synchronisation. If appl=0 is specified explicitly, a partial structure transfer is performed. However the dictionary tables are not transferred − i.e. the −NODD option is always in effect for synchronisation. |
| check_structure=1 | Before transferring data RECASTDB normally checks the structure of tables is compatible on both databases. If the check_strucure=0 option is specified this check is omitted. This option should only be used where you are absolutely certain the databases have identical dictionaries, and the structure check will take too long, for instance if you are running over a very slow WAN or internet connection. |
| download=1 | If the value is 1 the download phase of synchronisation is performed. If it is 0 it is not. Ideally download should be performed by running RECASTDB on the "master" machine using the −synchronise and −local master options. This will significantly reduce the amount of data transfer over the network and hence gives improved performance. The −reverse option of tcpmgr can be used to automatically launch a script or other program that can then call RECASTDB like this to transfer data (you may even launch RECASTDB directly, but you may want to run another program that will decide whether or not to run RECASTDB based on the date or time or information held on the master database.). |
| upload=0 | If the value is 1 the upload phase of synchronisation is performed. If it is 0 it is not. Ideally download upload be performed by running RECASTDB on the "local" machine using the −synchronise option. This will significantly reduce the amount of data transfer over the network and hence gives improved performance. |
| copy=1 | If copy=1 tables are uploaded or downloaded by default, even if they are not referred to in the configuration file. |
| delete=download | If delete=1 tables are processed for deletion by default, even if they are not referred to in the configuration file. This option defaults to 1 for the [download] section and 0 for the [upload] section. |
| mode=0 | If mode=0 is specified all records selected from tables on the source database are copied. If mode=1 is specified then only records matching specified table specific conditions are processed. The condition defaults to "all", so it does not matter if you don't specify a condition for a particular table. |
| use_timestamp=download | If use_timestamp=0 is specified all records selected from tables on the source database are copied, subject to the other conditions, if any, specified for the table. If use_timestamp=1 is specified then in addition to the other conditions, records for upload are excluded unless their timestamp indicates that they have been created/updated on the satellite since the last synchronisation, and records for download are excluded unless their timestamp (on the master) indicates that they have been created/updated on the master. |
Most settings in these sections have the same format in both cases. Settings in the [tablename.upload] section are used when copying a table from the "local" to the "master" database. Settings in the download section are used in the reverse case.
| condition=boolean formula | This entry, only used when mode=1, specifies the condition used to decide whether to copy a record from the source to the target database. |
| copy=default_copy | If copy=1 the table is uploaded or downloaded in acoordance with the other settings. If copy=0 the table is not copied. |
| delete=default_delete | If delete=1 records are processed for deletion in accordance with the other settings for the table. If delete=0 the table is not processed for deletion. |
| delete_both=0 | This entry, only used when delete=1, causes data that is to be deleted from the target database also to be deleted from the source database. |
| delete_condition=boolean formula | This entry, only used when mode=1, specifies the condition used to decide whether to delete a record from the target database. |
| index=index_table.index_role delete_index=index_table.index_role |
Normally RECASTDB will search through an entire table when looking for records that should be copied/deleted. You can use this option, together with the index_value/delete_index_value entries to limit the amount of data searched. |
| index_value=formula delete_index_value=formula |
This specifies the formula to be used for the first keyfield when the index or delete_index entries are used. |
| mode=default_mode | If mode=0 is specified all records selected from the table on the source database are copied. If mode=1 is specified then the condition or delete_condition specified is used to filter records. |
| set_timestamp=use_timestamp | If set_timestamp=0 is specified (or implied) the timestamp associated with synchronisation for this table is not updated. If set_timestamp=1 is specified (or implied because use_timetamp=1) then after a successful synchronisation the timestamp associated with the table on the satellite databases is updated. |
| update_field=field_name.field_role | This specifies a field to be updated on the source database for records that are transferred during an upload (or download, though this is unlikely to be useful). Records are only updated when the transfer succeeds. Unfortunately, it is impossible to absolutely guarantee that the update will be made, though it will only fail in exceptional circumstances. |
| update_value=formula | This specifies a formula that assigns a new value to update_field. |
| use_timestamp=default_use_timestamp | If use_timestamp=0 is specified all records selected from the table on the source database are copied, subject to the other conditions, if any, specified for the table. If use_timestamp=1 is specified then in addition to the other conditions, records for upload are excluded unless their timestamp indicates that they have been created/updated on the satellite since the last synchronisation, and records for download are excluded unless their timestamp indicates that they have been created/updated on the master. |