0

I've seen a few questions related to softwares that replicate data on databases but I haven't seen one that might work for my needs. I need to replicate many slaves to a single master. The master will have all the data from the slaves merged into its table. The scenario below should help explain what I'm looking for:

Each machine has the same table/DB layout using SQLite. I will use 1 table with columns Timestamp, MachineNumber, PointName, Value for these examples:

DB table on Machine 1

00:00:00 1 Point1 1.0
00:00:00 1 Point2 2.1
00:00:01 1 Point1 1.5
00:00:01 1 Point2 2.1

DB table on Machine 2

00:00:00 2 Point1 2.0
00:00:00 2 Point2 3.1
00:00:01 2 Point1 2.1
00:00:01 2 Point2 3.0

DB on MASTER (Needs replicated from each slave and incremental additions due to loss of connection)

00:00:00 1 Point1 1.0
00:00:00 1 Point2 2.1
00:00:00 2 Point1 2.0
00:00:00 2 Point2 3.1
00:00:01 1 Point1 1.5
00:00:01 1 Point2 2.1
00:00:01 2 Point1 2.1
00:00:01 2 Point2 3.0
Tacitus86
  • 1,314
  • 2
  • 14
  • 36

1 Answers1

1

Try:

sqlite3 db-master
sqlite> attach 'db1' as db1;
sqlite> attach 'db2' as db2;
sqlite> insert or ignore into data select * from db1.data;
sqlite> insert or ignore into data select * from db2.data;

Note: if the data in the slave tables continually accumulates, you probably need to define a unique ID on the data so the same information doesn't get re-added every time you sync with the master database.

varro
  • 2,382
  • 2
  • 16
  • 24
  • Ok so this will have to be called periodically. Is there any command to schedule this or to have it done continuously? As your note suggested, the slave tables will indeed be continuously accumulating. These commands will likely be issued via a java program running on each slave. I'm ok with DB commands, but is the 'ignore' command used in case the data is a duplicate entry? – Tacitus86 Apr 24 '17 at 18:23
  • 1
    Scheduling the task is outside of SQLite's purview. How you do that will depend on your operating environment - e.g., cron may be a possibility if you're on a Posix-type platform. And yes, "ignore" is used to handle duplicate entries without causing an exception. – varro Apr 25 '17 at 17:49