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')
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!
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 updated | Server row last updated | Last sync’s timestamp | Action |
|---|---|---|---|
| 1 | 1 | Anything | No change |
| 1 | 2 | Anything | Sync from Server to Client |
| (no Client row for ID) | 2 | 1 | Sync from Server to Client (row was created on Server after last sync) |
| 1 | (no Server row for ID) | 2 | Delete Client (row was deleted on Server after last sync) |
| 2 | 1 | Anything | Sync from Client to Server |
| 2 | (no Server row for ID) | 1 | Sync from Client to Server (row was created on Client after last sync) |
| (no Client row for ID) | 1 | 2 | Delete 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!