Replication
How does it work?
Your application will use a modified version of the SQLite library containing the liteReplica code to access your database.
The modifications on the SQLite library are internal and the interface is the same.
On the replica side, you can use another application to access the slave database, using the same liteReplica library used in the master.
The liteReplica libraries will communicate to each other, exchanging transaction data.
So when your application writes to the master database, the liteReplica code will send the transaction to the slave replica and these changes will be applied as a transaction on the slave database.
If your application rolls back the transaction, the same will occur in the slave side.
The slave database is read-only for the application accessing it. Only the lireReplica will update it.
Do I need to change my app code?
No. The litereplica uses the native SQLite3 interface.
In the master side you just need to change the URI string in the database opening from this:
“file:/path/to/main.db”
to this:
“file:/path/to/main.db?replica=master&slave=tcp://server.ip:1234”
And then compile and link your application with the litereplica code or library (for C and C++).
For other languages you must have the proper wrapper installed.
What more is needed in the Master side?
Nothing! It’s done.
What about the Slave side?
Assuming that you will use a separate application to keep the slave database, a basic standalone application used solely for the purpose of keeping a db replica would look like this:
Select a language -->
#include <sqlite3.h> char *uri = "file:/path/to/backup.db?replica=slave&bind=tcp://0.0.0.0:1234"; int main() { sqlite3 *db; sqlite3_open(&db, uri); /* open the slave replica db */ litereplica_join(); /* keep the app open */ }
import sqlite3 conn = sqlite3.conn('file:/path/to/backup.db?replica=slave&bind=tcp://0.0.0.0:1234') # keep the app open import time while True: time.sleep(60) # in seconds
var sqlite3 = require('sqlite3').verbose(); var db = new sqlite3.Database('file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234'); // keep the app open setInterval(function(){}, 5000);
import java.sql.Connection; import java.sql.DriverManager; public class Sample { public static void main(String[] args) { String uri = "file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234"; Connection connection = DriverManager.getConnection("jdbc:sqlite:" + uri); // keep the app open while (true) { Thread.sleep(5000); } } }
using System.Data; using System.Data.SQLite; public class Program { public static void Main() { string connStr = "FullUri=file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234"; SQLiteConnection connection = new SQLiteConnection(connStr); connection.Open(); // keep the app open while(true) { System.Threading.Thread.Sleep(5000); } } }
Imports System.Data Imports System.Data.SQLite Module Module1 Sub Main() Dim ConnStr As String = "FullUri=file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234" Dim Conn As New SQLite.SQLiteConnection(ConnStr) Conn.Open() ' keep the app open Do System.Threading.Thread.Sleep(5000) Loop End Sub End Module
<?php // with sqlite3: $db = new SQLite3("file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234"); // with pdo_sqlite: $pdo = new PDO("sqlite:file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234"); // keep the app open - it should not be used with apache while(1) sleep(5); ?>
use DBI; my $dbh = DBI->connect("dbi:SQLite:uri=file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234"); // keep the app open - it should not be used with apache sleep;
require 'sqlite3' db = SQLite3::Database.new "file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234" # keep the app open loop do sleep(1) end
local sqlite3 = require("lsqlite3") local db = sqlite3.open('file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234') -- keep the app open local lsocket = require("lsocket") while true do lsocket.select(5000) end
package main import ( "database/sql" _ "github.com/mattn/go-sqlite3" "time" ) func main() { db, err := sql.Open("sqlite3", "file:/path/to/replica.db?replica=slave&bind=tcp://0.0.0.0:1234") // keep the app open for { time.Sleep(1000 * time.Millisecond) } }
Where the slave database can be?
The slave db can be kept:
1. On a separate device:
2. On the same device, in another app:
3. On the same device, in the same app, using another db connection:
4. On the same device, in the same app, on the same db connection, using an attached database:
Note: If the slave db connection is in another application then this application must be kept open for the database to be updated.
Connection
So in the master side as in the slave side you have 2 options:
bind to an address
connect to the peer address
So you can choose which side will connect to the other. This is useful when one side is behind a router or firewall.
Communication Protocols
The litereplica library can use the following protocols:
tcp - in any situation
ipc - master and slave in the same device
inproc - master and slave on the same process
When using both db files on the same device, the protocol does not need to be specified on the URI string. Example:
Master database:
"file:/home/user/app.db?replica=master"
Slave database:
"file:/home/user/copy.db?replica=slave&master=/home/user/app.db"
Topology
Simple
1 master connected to 1 slave
Many replicas
1 master connected to many slaves
Chained
1 master connected to 1 slave, and this one to another slave
Usage examples
Basic replica
This is used simply to keep a backup of the database
Readable replica
This is useful when another app needs to access the data in another device even when the main device is off-line
On-line replica
Kind of a readable replica, this is used when your system has an on-line webapp or a site that access your application data
and this application needs to use a local database so that it is always accessible.
In this way when there is no access to the internet the main application can still write to its local database
and the web-clients can access the on-line replica. When the internet connection is restablished the
on-line replica is updated with the master database.
Shared Replica
This is used with applications that we cannot modify, those which we don't have access to its source code. As long as the application does not write to this database file we can use another application accessing the same db file to keep it up-to-date with a master one
CURRENT LIMITATIONS
-Savepoints are not supported, only normal transactions
-WAL mode is not supported