In a client/server environment we sometimes have to bring down large data over the network, especially if we are displaying lookup tables. Instead, if we could have a local copy of the same table and keep it in sync with the server table, we could decrease the network load.
Server Database Setup
To do so, we first need to keep track of the changed records on the online database. This is done by having an update serial number for each record.
To track the deleted records, create a table called deleted with the fields TableName C(25), PK Int, UpdateSR Int.
| TableName | Varchar(25) | Name of the table of the deleted record |
PK | Int4 | Value of primary key of the deleted record. Could also be of type GUID depending on what you use as a primary key |
UpdateSR | Int4 | Update Serial number of the DELETE |
This will have details of all deleted records of all tables.
To track added/changed records, add an integer column called UpdateSr in all the tables that we want to keep synced.
In the INSERT and UPDATE trigger, we update the value of UpdateSr to the next serial.
In the DELETE trigger, we insert a record in the ‘deleted’ table.
If the backend is PostgreSQL then the setup will be
• Create a sequence for each table
CREATE SEQUENCE clientupdate INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 11 CACHE 1;
• Create a Trigger function
CREATE OR REPLACE FUNCTION updateclient()
RETURNS "trigger" AS
$BODY$
declare nextsr int;
begin
nextsr=nextval('clientupdate');
IF tg_op="DELETE" THEN
INSERT INTO deleted (tablename,pk,updatesr) values ('client',old.id,nextsr);
RETURN old;
END IF;
new.updatesr=nextsr;
RETURN new;
End
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
• Create a Trigger
CREATE TRIGGER client
BEFORE INSERT OR UPDATE OR DELETE
ON client
FOR EACH ROW
EXECUTE PROCEDURE updateclient();
It is better to check for changes in value of only the columns that we are interested in to mark the record as changed instead of marking it as changed when there are changes in any column of the record.
Client Database Setup
Create a VFP database on the client side. Create a table called UPDATESR in it. This table will contain a list of all the tables that have to be kept synchronized. Structure of the table will be
| TableName | Varchar(25) | Name of the table of the deleted record |
PKFLDNAME | Varchar(15) | Name of the Primary Key Field |
UpdateSR | Int4 | Serial number upto which the table is synchronized |
When entering the tablename, add the word ‘Shadow’ to the server table name. So the local table name for the client table will be clientshadow. The record in UPDATESR will look like
| TableName | PKFLDNAME | UpdateSr |
| ClientShadow | ID | 0 |
Create the shadow table (clientshadow.dbf) with the same structure as that of the online client table. You do not need to have all the fields there. The primary key is necessary. Create only those fields that you want synchronized. Do not use AutoInc for the primary key otherwise it will become readonly and we want to insert records along with the primary key. Create index tags etc as are necessary.
Now we turn on event in the database
DBSETPROP(YourDatabaseName,"DATABASE","DBCEvents",.t.)
And add a procedure in dbc_AfterOpenTable
PROCEDURE dbc_AfterOpenTable
LPARAMETERS lcTableName
DO sync WITH lcTableName
Basically every time a table is opened, we are calling sync.prg. Sync.prg will query the online database and update the table accordingly. Sync.PRG is attached.
So all you have to do is open the shadow table and the local data will be up to date.
To requery a table you can create a PRG class called shadowupdate
DEFINE CLASS shadowupdate as Session
PROCEDURE init
LPARAMETERS lcTableName
USE (lcTableName)
USE
RETURN .f.
ENDPROC
ENDDEFINE
And refresh a table with
loShadowUpdate=NEWOBJECT('ShadowUpdate','shadowupdate.prg','', 'clientshadow')
Be aware that a local synchronized table can be easily opened or queried for changes. The security offered by a back end will be lost in a synced table.