In una recente migrazione dal mondo Microsoft ad Android, ho dovuto affrontare il tema della sincronizzazione dei database.
Intendo dire tabelle e record presenti sui database delle applicazioni client, con un database centrale.
Con Microsoft e SqlServer è abbastanza semplice: il database di Redmond mette a disposizione un framework di sincronizzazione trasparente agli utilizzatori, per cui è possibile sincronizzare i database.
I client sono smartphone con Windows Mobile (6.x) su cui è installata un App .Net (C#) con db SqlServer Compact.
Passando ad Android, si incontra qualche difficoltà. Molti parlano di sincronizzazione dati, ma intedono file o interi database. Qui si intende un’operazione più chirurgica. Anni fa, prima di adottare la soluzione Microsoft, mi capitò di dover sviluppare un tale framework in casa (al progetto ha contribuito molto Domenico Federico, emerito collega che si è occupato di affinare gli sviluppi in java su client e server). Nel nostro caso utilizzavamo il database PostgreSQL sia lato server che sui client. I client erano dei laptop dati indotazione ai macchinisti della società LeNord (gruppo FNM). Il sistema è tuttora in uso, ma utilizza la piattaforma MS (sigh! 
)
La soluzione torna in auge con Android. E’ comunque qualcosa di ‘casalingo‘ e lo prenderei solo come un’esercizio. Il database utilizzato è SqlLite, lo stesso che troviamo su Android.
Il server
Sul server è necessario avere un database e poter installare dei WebService (del tipo che preferite: SOAP, Hessian, Rest…).
Servono le seguenti tabelle:
- SYNC_TABLES – registro delle tabelle che desidero sincronizzare,
- SYNC_MASTER – tabella di log per registrare le modifiche avvenute sulle tabelle sincronizzate,
- COFFEE – la tabella che voglio sincronizzare.
La SYNC_TABLES – ha un campo numerico (chiave, autoincrementante) e un campo testo con il nome della tabella che desidero sincronizzare:
- id – integer not null autoincrement primary key – serve da id univoco per la tabella (per non trascinarmi dietro il nome)
- tname – varchar – vero nome della tabella
Le tabelle per la sincronizzazione
La SYNC_MASTER – registra ogni operazione effettuata sulle tabelle ‘sotto sync’.
- id – integer not null autoincrement primary key – chiave univoca dell’operazione
- ts – timestamp – quando ho inserito (‘i’), modificato (‘u’) o cancellato (‘d’) una riga?
- action – che operazione ho effettuato sulla riga? ‘i’,'u’ o ‘d’
- tblid – su che tabella è avvenuta l’operazione? qui trovo l’id della tabella, come definiti in SYNC_TABLES.
- rwid – su che riga della tabella è avvenuta la modifica? qui ho l’id univoco della riga della tabella; per questo ogni tabella deve avere un id univoco che sia chiave primaria e che questi id siano dello stesso tipo per tutte le tabelle sincronizzate!
La COFFEE – è la tabella che voglio sincronizzare. Ha solo 2 campi, un id (int, autoincrement not null…) e un campo name varchar(20) dove metto il nome dei caffè. Tutte le tabelle che voglio sincronizzare devono avere una chiave dello stesso tipo – per esempio int autoincrementante, questo perché le registrerò sempre nella tabella master.
Le DDL delle tabelle da creare sul server (sqllite):
CREATE TABLE coffee (
cid INTEGER PRIMARY KEY AUTOINCREMENT,
name varchar(20));
CREATE TABLE master_tables (
tblid INTEGER PRIMARY KEY AUTOINCREMENT,
tbname varchar(50));
INSERT INTO master_tables (tbname) values ('coffee');
CREATE TABLE master_sync(
uid INTEGER PRIMARY KEY AUTOINCREMENT,
tblid integer,
act char(1),
rwid integer,
ts DateTime);
I TRIGGERS
Mi dispiace tanto, ma questa soluzione si basa sui triggers! Ogni tabella che desideriamo sincronizzare ne deve avere almeno 3:
uno sulle insert, uno sulle delete ed uno sulle update. Ogni volta che una riga della tabella viene modificata, il trigger scrive un riferimento nella tabella Master.
Ecco una possibile definizione dei trigger, nell’ipotesi che sia sul client che sul server stia utilizzando sqllite:
CREATE TRIGGER tt_i_coffee after INSERT ON coffee
BEGIN
INSERT INTO MASTER_SYNC
(tblid,act,rwid,ts)
values
(1,'i', new.cid, CURRENT_TIMESTAMP );
END;
CREATE TRIGGER tt_u_coffee after UPDATE ON coffee
BEGIN
INSERT INTO MASTER_SYNC
(tblid,act,rwid,ts)
values
(1,'u', old.cid, CURRENT_TIMESTAMP );
END;
CREATE TRIGGER tt_d_coffee after DELETE ON coffee
BEGIN
INSERT INTO MASTER_SYNC
(tblid,act,rwid,ts)
values
(1,'d', old.cid, CURRENT_TIMESTAMP );
END;
Cosa accade se inserisco un record su coffee, lo modifico e lo cancello?
Inserisco un caffe di nome ‘java’; l’autoincrement assegna id pari ad 8. Poi decido di modificare il nome del caffè in ‘arabica’. Dopo qualche minuto lo cancello del tutto.
Cosa avro’ nella master? Il log delle operazioni svolte.
Logging delle operazioni sulla tabella Master
Attenzione: le operazioni è bene che avvengano sempre in una ‘direzione’: sebbene sia possibile creare una sincronizzazione nei due sensi, la cosa è abbastanza sconsigliabile e complessa. Significa che mi troverò a conciliare modifiche avvenute sul server e sul client, magari conflittuali. Se possibile si segua questo approccio:
Il client è ‘slave’: non scrive direttamente nelle tabelle sincronizzate, ma esegue delle chiamate a webservices sul server per chiedere le modifiche, dopodichè scarica i dati con una sincronizzazione. Cosa fare se non ho connessione? salvo i dati (se sono così importanti) in una tabella temporanea o anche solo in memoria e provo la riscrittura fino a che non riottengo una connessione.
il flusso dei dati è unidirezionale
Il client
Sul client avro un database Sqllite (Android). Devo creare le tabelle che voglio sincronizzare, nel nostro caso la coffee. E’ necessario tenere traccia dell’ultima sincronizzazione andata a buon fine. L’informazione si può tenere anche su un file, ma magari è più pratico avere tutto nello stesso db, quindi creaiamo anche una tabella LAST_UPDATE con un solo campo: un timestamp con la data di aggiornamento.
La DDL delle tabelle da creare sul server (sqllite):
CREATE TABLE coffee (
cid INTEGER PRIMARY KEY AUTOINCREMENT,
name varchar(20));
CREATE TABLE last_update (
last_update DateTime);
La tabella LAST_UPDATE
Quando il client avvia una sincronizzazione, recupera il timestamp dell’ultima sincro ed esegue una chiamata al server passando tale valore (es. tralasciando la data ed usando solo l’ora: 8:00:00). Il server mette da parte il timestamp in cui riceve la chiamata (le 9:30:01) e quindi esegue una select sulla tabella master:
select
*
from sync_master
where ts > '8:00:00'
order by ts
Questa deve produrre una lista di operazioni da effettuare sul database del client. Le operazioni devono essere ordinate per timestamp e sono generate, prima esaminado la master e poi recuperando i dati dalle tabelle necessarie (nel nostro esempio abbiamo una sola tabella!). Supponiamo siano in formato xml:
<sync last_update='9:30:01'>
<insert table='1' ts='8:51:10'>
<row>
<col name='id'>1</col>
<col name='name'>java</col>
</row>
</insert>
<update table='1' ts='8:52:57'>
<row>
<col name='id'>1</col>
<col name='name'>arabica</col>
</row>
</update>
<delete table='1' ts='9:01:02'>
<row>
<col name='id'>1</col>
</row>
</delete>
</sync>
Il client riceve tale lista e la passa ad un DAO che implementa le operazioni di INSERT, UPDATE o DELETE. Sono possibili delle ottimizzazioni, ovviamente, omesse per semplicità. Tutte le operazioni si fanno all’interno di una transazione: l’ultima operazione è l’aggiornamneto della LAST_UPDATE, che viene imposta a ’9:30:01′.
Flussi tra client e server
