Database Synchronization Desktop and iOS

Databases
1

Xojo simplifies the work, thus Api need to use database that can work in the cloud and locally on mobile devices.

Xojo is a fantastic tool to develop quickly, the next step is that the databases can Work Related offline and online multi platform so easy as Xojo ace.

Anyone know how to create databases offline and online data Xojo? that can be synchronized through the cloud on other computers?

2

iOS has sqlite as does desktop. And you can use it on the web as well.
But there is no built in synchronization.
Synching raw data is not so hard IF you make sure everything has timestamps to know what got modified last.
However, JUST synching last changed data is often NOT adequate and requires business rules that are specific to your business / application to know what should be synched

3

[quote=181985:@Norman Palardy]iOS has sqlite as does desktop. And you can use it on the web as well.
But there is no built in synchronization.
Synching raw data is not so hard IF you make sure everything has timestamps to know what got modified last.
However, JUST synching last changed data is often NOT adequate and requires business rules that are specific to your business / application to know what should be synched[/quote]

Thanks, I wonder if an expert can do me a budget to hire the development of an exemplary synchronization desktop and iOS.

Greetings.

5

Finally there’s a post I can help with!!!

Victor, there are probably a million ways to do what you want. I’ve done something similar in FileMaker.

  • Each table needs a UUID for the Primary Key. In Xojo sqlite, I also let it use the standard auto incrementing integer, but I ignore it. You want a UUID since it is unique every time it’s generated. Since users are working in different databases, you don’t want Joe to create record 42 and Sue to also create record 42. If they did, their records would merge and those two records would be a mess.
  • On each table I have modified timestamp field based on UTC / GMT time ( http://www.worldtimeserver.com/current_time_in_UTC.aspx ). This is used to find out what time the user changed the data no matter where they are in the world.
  • On each table, have a field for a change log. You might store this in xml or json. At a minimum, you’d want the UTC / GMT timestamp when the field was modified, and the field name that was changed. Depending on your needs, you might want the prior value, current value, and who changed it. Having the prior value and current value gives you a chance to roll back…
  • In a settings table store the last time the user performed a sync with a UTC / GMT timestamp. A blank value would be never.
  • Create a table for deleted records to store the table name, record UUID, and who deleted it with a UTC / GMT timestamp. This will be used to delete records that other users deleted. It will be sync’d like any other table.
  • Now you can send a message of some sort to the other database to find all the records that have been modified since the users last sync timestamp and return the change log. You’ll do that for each table that you care about. If you didn’t store the current value of the field in the change log field, you’ll need to do another find for the current value of the field. The reason to not store it in the change log field would be to not bloat the db file.
  • I like to put ALL the change log field info I collected from ALL tables in a sync data table so that I don’t actually start merging the data until I’ve received everything. Everything means all the data from one side if you are doing a one way sync or all the data passed on both sides if you are doing a bi directional sync.
  • One thing you can do is not pass redundant modified fields to speed up the process. If you change a contact’s first name from Harry to Harold to Hal, the intermediate values don’t matter at all, except for rolling back.
  • Either way, once you have all the changed values in the sync data table you’ll sort it on the modification timestamp ascending and apply the changes to your tables.
  • BE EXTREMELY CAREFUL to make sure the modified timestamp is set to the value in the change log. If you make it be right now, you’ll end up in a syncing loop that will never end as it will appear that the field keep changing.
  • I’d suggest using a server / client model where all the clients sync with the server. That way ALL the updates are sent to the server. I’m not sure how it would work if three or more clients would sync amongst themselves.

Please let me know if this sounds crazy, but it worked great for me in FileMaker! Someday I’d love to implement this in Xojo!!!

6

I actually work with the UTC time as well. Because of that I made a simple Date Extension:

Function UTCtime( extends d as date )
   dim dat as new date
   dat.TotalSeconds = d.TotalSeconds - (d.GMTOffset * 3600)
   Return dat
End Function
7

Has anyone here tried to create a sync routine or app that works with Xojo IOS?

8
  • add a UUID column to each record
  • add a status column to each record (new, updated, deleted)
  • do not actually delete the records (set the status)

Sync routine compares the status flags and UUID in both databases and applies required transactions
and at this time, records are “deleted”

I would not use a timestamp for comparison, depending on your use, device clocks may not be 100% synched, and dual transactions COULD happen on both devices at the same “time”

9

Dave, I understand how to do it and I have done it before with Desktop Xojo apps and mysql servers, but am wondering if anyone here as used Sync in an IOS app essentially for offline mode. For example, I don’t think you could have one app the syncs the database and one app that updates via forms,etc. I am under the belief that mobile databases are essentially single user, so your Xojo IOS app would need to do both.

Just trying to get a feel how difficult it would be to do in Xojo IOS as far as programming goes.

10

Export the iOS database to the desktop that has the desktop database, let a desktop app sync them (the desktop database needs the iOS data as well as the iOS database requiring the Desktop data)… then import the sync’d data back to the iOS device.

At some point one of the devices needs access to BOTH copies of the database.

Having two iOS apps that access the same database “can be done”, but its a royal PITA… thats why I suggest the desktop do the synch work