Share My Creation wmSQLtableSync - SQL table synchronisation between two databases [Class] [Source included] [B4A] [B4J]

Why/What?
A CloudKVS didn't cover my needs so I developed this class. Features:
- It synchronises a table between two SQL instances; one is called the Client, the other the Server
- All columns that exist in the Server table must be present in the Client table
- Additional columns that exist in the Client table are not manipulated
- Multiple Clients can synchronise with one Server
- Each Client is responsible for 'remembering' its last sync time
- Tested with SQLite and MySQL
- Works with B4A and B4J
- All methods contain documentation

Important/limitations
- There are no specific provisions for tables with foreign keys
- The applications that change data in either database MUST UPDATE/FILL the row's 'lastUpdated' column (the name can be chosen freely) with 'DateTime.Now' when using UPDATE or INSERT
- When using MySQL with a B4A Client, see below for library dependencies and the Conditional Symbol to add to the project's Build Configurations
- It uses a direct connection to remote databases, not jRDC2 (see https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016); communication is not encrypted

Sync logic (last updated/timestamp values here mean: '1' is before '2')
Client row last updatedServer row last updatedLast sync’s timestampAction
11AnythingNo change
12AnythingSync from Server to Client
(no Client row for ID)21Sync from Server to Client (row was created on Server after last sync)
1(no Server row for ID)2Delete Client (row was deleted on Server after last sync)
21AnythingSync from Client to Server
2(no Server row for ID)1Sync from Client to Server (row was created on Client after last sync)
(no Client row for ID)12Delete Server (row was deleted on Client after last sync)

Dependencies
- If MySQL is involved: #AdditionalJar: mysql-connector-java-5.1.40-bin (see https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/)
- Libraries: (j)SQL, (j)RandomAccessFile, mysql-connector
- Libraries (only when using MySQL with B4A, and if Conditional Symbol 'LibJdbcSQL' is present): JdbcSQL (see https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/)

Public properties
- LastError: returns the last error (a message in English) that was encountered
- LastSyncTime: gets/sets the last time the Client synced with the Server
- LogChanges: gets/sets whether changes must be logged; this is useful for debugging and understanding what is being done

Public methods
- BackupClient: creates a backup of the Client table to a file; optionally syncs first
- BackupServer: creates a backup of the Server table to a file; optionally syncs first
- CloneClientToServer: empties the Server table and loads all Client table data into it
- CloneServerToClient: empties the Client table and loads all Server table data into it
- Initialize: initialises the class
- Sync: performs the synchronisation
- RestoreClient: restores a backup of the Client table from a file created with BackupClient; optionally syncs afterwards
- RestoreServer: restores a backup of the Server table from a file created with BackupServer; optionally syncs afterwards
- SetLastUpdated: sets the 'lastUpdate' column of the Client table and/or Server table to the specified time ticks value

Implementation scenarios (see comments at the start of the source code)
- Implementation in a new application
- Retrofitting an existing application that only uses a local SQLite database
- Retrofitting an existing application that only uses a server-based database like MySQL

Demo project attached. Enjoy!

Screenshot1.png
Screenshot2.png
 

Attachments

  • wmSQLtableSyncDemo.zip
    15 KB · Views: 63
Top