Syncronising across two database servers

Classic Servoy
1

I am embarking on a syncronisation routine based on two identical databases one on Servoy Offline and local to a machine, an one on a remote mysql server.
I am using the principal of date_modified fields in the table that needs to be syncronised.

What would be the best way to try and create an efficient syncronising routines, given that each user might have say 10,000 records each.

Is there a way that i can easily create a dataset or array based on a query that looks at:

where localdata.date_modified>remotedata.date_modified.

I was looking at the rawsql plugin and it doesnt appear to allow you to query two separate database servers simultaneously.

I am assuming that if i loop through records on forms that this will simply take too long as the data size grows. There must be a way to do this in memory as it were?

I am sure someone will have found a few simple lines of query to solve this problem, so any help or pointers would be most welcome.

David

2

Not sure if MySQL allows a ‘MERGE’ statement like Oracle which I think is the simplest way to do it. What I would do is:

  • First get both databases in sync on some date.
  • Then at some later date get the records from one of the datasets that have been updated since that original (in sync) date.
  • Script in javascript a MERGE statement (if available) by taking those records and, if one is ‘later’ than the other database create an update. Run those MERGE statements through the RAWsql plugin.
  • Repeat for the dataset from the other server.
    Then just keep track of whatever datetime this was done for the next run.
    If MERGE is not available, then you’ll have to use IF or whatever is available in MySQL. (I’ve never used MySQL). Does that work for your case? Once you have a ‘date/time last synchronized’ you don’t need to compare each record in one database against the other. Of course if there aren’t too many updates since that ‘date/time last synchronized’ you could just loop through those records in a foundset within Servoy and update them that way which is even simpler.
3

Thanks John,

I think I am tackling this in the correct way and indeed your post backs up my thinking.

So far i have pretty much done what you suggest. I have a date last synced field and then a date modified on both servers.

Working on Local Offline → Server first. My local solution finds all records changed since the last sync and then can loop through them to work out if they are more up to date than the server.

I was thinking that this was more efficiently done by not using a form and trying to run it using the database and RawSQL functions.

Where I am stuck is having go a local record that i now know need updating, but sitting in a dataset, i need to try and push that to the server.

Trying to create an “UPDATE so_appointments set …” doesnt seem to be working.

Can anyone see an easy way to do this, or am i being stupid by not doing a find for this record on a layout and then looping through the allproviders in order to update it. The reason for avoiding a relationship is that when i use my solution in offline, then if their are relationships to the remote server then they come up with an error on startup, which i have failed as yet to catch and therefore looks a bit messy for my users.

//First identify all local records that have been modified since the last sync date
//Use the local server

var maxReturnedRows = 999;//useful to limit number of rows

var query = 'select * from so_appointments where date_modified > ?'

var args = new Array();
args[0] = login_to_so_customer.date_lastsync

var dataset_local = databaseManager.getDataSetByQuery("reportwriter", query, args, maxReturnedRows);

application.output(dataset_local.getMaxRowIndex())

//Need to get the column id of the date modified


//	var local_columns=new Array()
	for (var x = 1; x <= dataset_local.getMaxColumnIndex() ; x++ )
	{
	if (dataset_local.getColumnName(x)=='date_modified')
	var dm_col=x
	
	
	}

//We now have a dataset with all the local records that have changed since the last syncronisation

//Rule one if local date_modified ID>remote date_modified then overright remote.
//Never overright a remote field with a blank or null local field

//Loop through all the modified records locally
for( var i = 1 ; i <= dataset_local.getMaxRowIndex() ; i++ )
{
	
	//Need to deal with new records first
	//>10,000,000
	//if not new
	//Check the make sure the remote date_modified is less than the local one.
	//Lets Get the remote Date_modified dataset_local.getValue(i,1)
	
	
	var query = 'select date_modified from so_appointments where id = ?'

	var dataset_remote = databaseManager.getDataSetByQuery("reportwriter_server", query, [dataset_local.getValue(i,1)], 1);
		//Verify that the local record has in fact been modified after the remote one
		if (dataset_local.getValue(i,dm_col)>dataset_remote.getValue(1,1))
		{
		//This local record has beend modified after the remote one
		application.output (dataset_local.getValue(i,dm_col)+' - '+dataset_remote.getValue(1,1))
		
		//Now need to create a rawsql statement
	
		//Execute any SQL, returns true if successful
		
		//DOESNT WORK I THINK DUE TO quotes etc. must be an easier way to do this.
		var update='update so_appointments set'
		for (var x = 2; x <= dataset_local.getMaxColumnIndex() ; x++ )
		{
		update += dataset_local.getColumnName(x)+'='+ dataset_local.getValue(i,x)
		if (x !=dataset_local.getMaxColumnIndex())
		update +=','
		}
		update +=' where id='+dataset_local.getValue(i,1)
		application.output (update)
var done = plugins.rawSQL.executeSQL("reportwriter_server","so_appointments",update)
if (done)
{
	//flush is required when changes are made in db
	plugins.rawSQL.flushAllClientsCache("reportwriter_server","so_appointments")
}
else
{
	var msg = plugins.rawSQL.getException().getMessage(); //see exception node for more info about the exception obj
	plugins.dialogs.showErrorDialog('Error',  'SQL exception: '+msg,  'Ok')
}

		
		}
		
}

Answers to my stupidity gratefully received. I am sure a seasoned SQL servoyan will do this in a line or two!

David

4

I have done this, and it is much much more difficult than you may first imagine. Below I’ll give you some brief tips from my experience

  1. Only using data modified won’t work for adds and deletes. You need to know if the record was either A. Added B. Deleted, or C. Modified. For the add and delete, i made a table event that would make a new record in an audit type of table whenever a record was added or deleted. It stored the date/time, database name, table name, pk name, pk value, and who made the change.

  2. Storing who made the change is very important, as well as managing the timestamp with a table event. So, for the date modified, I also used a table event. That way I could toggle whether or not the modified date was going to be set. Otherwise your sync will be in an infinite loop. For example, user uploads the changes from his machine to the server, those server records now get new timestamps. Obviously you want the server records to maintain their timestamps from the original modification, not when they were uploaded. So you can manage this by using a global method to update the timestamps.

  3. If you want databroadcasting to happen (without forcing a flush of the database for all of the users to see the updated data), then you must do this all through Servoy. I accomplished this by making my own plugin which would start a Headless Client on the app Server, and then I passed Datasets back and forth, and had Servoy method routines that would process the datasets.

  4. You can’t send the whole set at once or you will likely get errors, so the datasets that you pass must be broken up. You need to use things like LIMIT or TOP in your query so that you can just pass maybe 30 records at a time or something.

5

Thanks Scott,

I had clocked a number of these issues. I am slightly luck as this is pretty much a one to one usage of a Saas solution. So the data is only available to the doctor and his secretary at each end. I dont allow deletes in my databases, just cancelled. Add records will have to be dealt with as a separate subscript, based on no local records being found.

I was going to flag the datetime the sync started and then when i do the download part of the sync to offline, then I would only touch records that had been changed between the last sync and the start of the sync date and time, in order to stop the duplication. As I said, i think i am lucky because no one will be updating the data in the middle of a sync.

What about the actual exchange of data? Am i correct in trying to do this through actual queries, or would i be as well to create a foundset on a form and loop through those records. I had assumed if I could do this independent of a form then it would be a lot faster, especially if I was dealing with say 40-50 records, which in reality may be the rough volumes of changed records for a busy doc.

If so then how did you go about writing the dataset to the remote database. I dont think there is a function to do this in 3.5, so was trying to mess with creating a RawSql Update statement out of my dataset. I think I may be barking up the wrong tree with this! How did you achieve that bit, or should i just bite the bullet do a find on a form for the record to sync to and run through each column from the dataset?

Thanks for the insight.

David

6

If you want databroadcasting, then you must eventually use foundsets to process the data. However you could still use queries to retrieve the data. With databaseManager.getFoundset(…) you can get a generic foundset for processing, so you really don’t need a form.

Since you are using Servoy Offline (and not Servoy Runtime), you should be able to add a database connection to your remote database. At that point, you could query your database to get the records out of, and that would give you a DataSet. Then convert the dataset to a foundset. Then get a foundset of your remote database. and loop through the 2 to do the processing. In my code, i have this as a generic function. As you loop through the foundsets you can use foundset.getRecord in your loop. THen use foundset.loadRecords(pkvalue) to get the other foundset to be on the same record.
Then you can use databaseManager.getTable(…). and then with the JSTable you can use getColumnNames. Then you loop through the columns in each foundset doing the update.

Also, if this were all in Sybase, you could consider Mobilink