Hi all,
I'm new in the swift code world.
I'm developing a simple App for iOS to in order to register some data (i.e. customer, appointment, locations).
Using GRDB (by the way, great job!) I've created all the tables and the interface to save, update or delete data.
I would like that the App can be used in different scenarios:
1 - same person but different devices (iPhone, MacOs, iPad... I've to implement interfaces for them, but it is in the project)
2 - different persons with equal or different devices (ie. both whit an iPhone, or one with iPhone and the other with iPad...)
The best solution I think is to have the database in the web (ie. DropBox or a web site.. I have to study how to do it)
But I don't want people to be obliged to have a place in the internet, so I was also thinking to create a function to export the db, send it by email and reload it on the other device.
It's possible to copy directly the file? it will be renamed so the second device, before the merge, can check the differences and decide if use them or discard.
Another way is to extract all tables and send them, maybe in text format, directly with the function to create and populate a new db.
Let's imagine you would know nothing about GRDB. You just have an SQLite database file. What would you want to do, according to your knowledge of SQLite?
I use to be a SQL Server administrator and a RPG programer.
Let me write down what I'm thinking.
Let assume I've user A with his db and user B whit his db2.
The best case is if A and B do not use their db without advise each other. In that case A creates events or customer, backup the tables and send them to B. B updates his db and so on.
But this will be a lot constraining.
I assume A and B uses the App at the same time.
First problem: both creates a new customer.
I have a customer code that is simply the autoIncrementedPrimaryKey of GRDB.
As little as I know of SQLite it is in a single file, A can take it, send to B (with a new name) and will start a merge procedure. Make it the very beginning, both db have customer 1, with name, city, phone number and email. To see if it is the same customer the best way is the email, it had to be spelled correctly (and I have a check it contains a string, a @, another string, a dot and another string). Only difference can be upper or lower case, but that's not a problem to solve. The name can be written differently (ie My name is Marco Pirola, but A can write Pirola Marco or, even worse, use my entire name, Marco Andrea Pirola). The city is out of question. The phone number can be compared, but it's not mandatory.
So i found the customer 1 from A (A1) is different from customer 1 from B (B1). To merge the db I insert the new customer in the table... But A1 will be equal to B2 and A2 will be equal to B1.
If A create an event (to make it simple an Event had a location, a date and a customer, both location and customer are recorded in the Event table with their ID) he can't simply send the event to B, it have to be decoded.
In SQL Server probably I will create a Stored Procedure that makes the check, insert the customer and create a "decode table" that says A1 is equal to B2. When A send the event to B it first go thought the decode and arrives correctly to B. This may work if one of the two is a "master database" that collects all the changes and the decodes.
Then, on Sunday (or a choosen day and time) I can extract all database A and send it to B, replace the database of B and truncate the "decode table". At that time all the DB are the same, now that I think to it I should add a boolean column in every table that became true when the db is "fully copied", but it's false when the object is created. So I have to merge only the false row of every table, not the entire db.
This means I don't really need to know if SQLite db is in a single file or not.
During the week I can send only the false row when they are created using a script (create temporary table, makes checks insert new info in the tables and in the decode tables).
At the end of the week the Master Database send a copy of HIS false rows to the others, all the rows in all the db became true and the decode tables are truncated.
Unless I'm mistaken, it looks like you are trying to synchronize the contents of two databases. This is a very general problem that has no unique solution as far as I know.
You will need to design a "protocol" that allows two (or more) devices to eventually synchronize. This "protocol" will need some channel of communication (left to your choice), and a data structure that when transferred allows the synchronization (left to your choice as well).
I suggest you start there: design your protocol. Make it work on paper. Check that it can handle correctly all the scenarios you want to support. Check that it can handle expected failures such as network errors. Look for inspiration on your favorite internet search engine, teach yourself about synchronization.
After that you'll be able to plan an implementation. It may use some SQLite specifics, or not: it's much too early to know.
You're right.
Last night, after the post, I started to write down a possible solution.
As you said there's no a single solution for everyone, so I have to create it from scratch dressed on my db.
