fmSQL Synch (v2.0)

Database Integration (This page has not been updated for v2 yet. See the README file for current details.)

FM Database

The fmSQL Synch software has been designed on top of the FileMaker templating system developed by Garrison Computer Services. It can also be integrated with existing FileMaker solutions. It is fairly easy to integrate existing FileMaker databases with fmSQL Synch. You will need to add a few fields, relationships, layouts and scripts. See FileMaker Integration for full details.

SQL Database

Tables in your SQL database only need a couple of extra fields in order to work with fmSQL Synch. You may already have suitable fields in your tables. The first field holds the "name" of the user who last modified the record and the second is a date/time stamp of last modification.

user_mod varchar(50) NOT NULL,
last_mod timestamp(14) NOT NULL,

These values should be updated every time a record is changed in an SQL table. Simply modify existing update statements to include those two fields. Here is a very simple update statement to update those fields:

UPDATE customers
SET user_mod='$user', last_mod=NULL
WHERE cust_key='$key'

You will also need one additional table, delete_log, to record record deletions. This table is queried by fmSQL Synch for records that have been deleted since last synch run. After the corresponding record is deleted from FileMaker, the delete_log table is updated so the same key will not be deleted again.

CREATE TABLE delete_log (
  log_key int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
  rec_key int(10) unsigned DEFAULT '0' NOT NULL,
  table_name varchar(40) DEFAULT '' NOT NULL,
  db_status enum('new','complete') DEFAULT 'new' NOT NULL,
  user_mod char(50) DEFAULT '' NOT NULL,
  last_mod timestamp(14),
  PRIMARY KEY (log_key),
  KEY rec_key (rec_key),
  UNIQUE log_key (log_key)
)

Delete Log

Both FileMaker and SQL databases use a delete log to track which records have been deleted. Using the FileMaker DeleteLog is just a matter of importing the "Delete Record" script and using that in place of the built-in "Delete Record" command. A "Delete ALL Records" script is not supplied but should be easy to create if needed. Creating routines for adding records to the SQL delete_log table is left up to you. Basically all you need to do is add a statement like the following whenever you delete a record in your SQL application:

INSERT INTO delete_log (rec_key, table_name, db_status, user_mod, last_mod)
VALUES ($del_key, '$table', 'new', '$user', NULL)

Key Ranges

Primary keys are used to uniquely identify the same record in FileMaker and SQL tables. It is possible to create a record in FileMaker and another in an SQL table that use the same primary key. To avoid this situation it is important that FileMaker and SQL database use different key ranges for new records.

I suggest that you use a range starting at 1 for FileMaker and a large number for SQL tables (eg. 10,000). If you are using alpha-numeric keys, then you can choose your own criteria for setting ranges. It is easy to specify a starting range for serial numbers in FileMaker, while mySQL seems to always use the the maximum value + 1. (I have read that mySQL will re-use deleted keys, but that is not my experience.) To set a mySQL table to use a key range starting at 10,000, simply insert a new record using 10000 as the key value.

Merge Conflicts

If records from the same table with the same primary key in FileMaker and SQL databases have been modified since the last synch run, then they will be checked for differences on a field by field basis. Any non-matching fields will be added to the MergeConflicts.fp5 file so they can be resolved after the synching process is finished.