Hi Ian, > I do not need instant syncing of SQLite databases, however I do have > a table stored on server X which I would like to gradually send > changes of to various other servers connected by TCP. > > In the first instance, I'd be quite happy to send rows with ids which > do not exist in the other servers' tables, and not worry about > changes to rows
Here are two methods that you may consider, they are suitable to send new rows or sync entry value changes. 1) The first is a standard logging method. To make an example of a schema: - There are three tables: SearchIndex, ThreadSession and ThreadIndex. Each table has three indexing columns or fields. Indexing in this case means if the entry value changes, then the new information in this row should be synced with the server. There are other columns but their content is further desciption relative to the indexing columns in the table. - INSERT, UPDATE and DELETE triggers monitor the 3 indexing columns in each table using the LIKE operator. A change in the entry value of the indexing fields fires the trigger which inserts a record in a logging table with the table name and Integer Primary Key. Joining the data table with the log table generates the row containing the current values. - Use the generated row to sync the dbs. - This method has been heavily exercised and tested over consider time and proves to be reliable. Using the LIKE operator in this scenario has not been detrimental to performance and accurate. 2) Method 2 uses temporary tables to hold the queried values in a user session. The same tables as in 1) are used in this example. One temporary table holds the query result rows for each table. So for the tables SearchIndex, ThreadSession and ThreadIndex there are also SearchIndex_temp, ThreadSession_temp and ThreadIndex_temp. In addition one column flags changes based on the same criteria as 1). If the contents of one of the three indexing columns or fields in the temp tables changes, then the temp row is flagged. - In the three tables DELETE the rows that have not changed and are not flagged and what remains can be used to sync the changes. - Gary Gabriel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
