Database sync between iphone and server

[idima]

Hi everyone!

I would like to know, if it is possible to sync a database of an iphone app (sqlite) with any database on the server.
The goal is to develope an iphone-application that runs on several iphones as clients and syncs its database with the central database on the server.

Thank you very much!!!

[dicklacara]

Originally Posted by idima

Hi everyone!

I would like to know, if it is possible to sync a database of an iphone app (sqlite) with any database on the server.
The goal is to develope an iphone-application that runs on several iphones as clients and syncs its database with the central database on the server.

Thank you very much!!!

Sure!

1) You have to copy the iPhone SQLite DB to the iPhone's Documents folder so you can update it. (google for an example on how to do this)

2) You need a way to access the DB server-- typically through a web app or a web service.

3) You need to write the iPhone synch app to access the Server DB and update the local SQLite DB.

You prolly want to add some sort of timestamp indication at both ends to avoid unnecessary synchs

[idima]

Hi!

Thank you very much for your answer!

Is there any API or something like that, that i can simply use in Xcode and have not to develop the synchronization on my own? If not, are there any tutorials or best practice descriptions for development of the synchronization?

Thank you!

[dicklacara]

Originally Posted by idima

Hi!

Thank you very much for your answer!

Is there any API or something like that, that i can simply use in Xcode and have not to develop the synchronization on my own? If not, are there any tutorials or best practice descriptions for development of the synchronization?

Thank you!

You can't really synch your app' s content in the way Apple synchs its iPhone apps content (Contacts, Calendar, etc). There is no program mechanism, as you do not have access to the iTunes synching app.

Even if you did have an API, you can't write a (non-jail broken) iPhone app to use the iPhone USB connector.

The only thing Apple does is allow you to replace your app (and its embedded data content) with a newer iteration thru iTunes synching or app store download.

So, to maintain synch, you have to write an app that uses WiFi or Edge, and that leads us back to some sort of client-server interface. You can write a special server app, in say Perl, that communicates with your iPhone client app... but it is usually easier to interface a web app or a web service app running on the server that has access to the DB server.

Most DB servers are capable of accepting direct calls, but IT would be crazy to permit this kind of access outside of the IT department.

[idima]

Hey dicklacara, thank you very much!

The synchronization must run over EDGE, 3G or WIFI not over USB. I think, it's the best solution to implement a web service for the server, that realizes the sync. Do you have some further information or tutorials for this?

Thank you very much for your help!!!

[dicklacara]

Originally Posted by idima

Hey dicklacara, thank you very much!

The synchronization must run over EDGE, 3G or WIFI not over USB. I think, it's the best solution to implement a web service for the server, that realizes the sync. Do you have some further information or tutorials for this?

Thank you very much for your help!!!

The web service is prolly the best solution.

I haven't screwed around with web services or web programming since 2004, so my answer may be somewhat dated:

The only experience I have writing/publishing WS (Web Services) is using CF (ColdFusion) on the server. It is easy to do but CF isn't as common as PHP and some other web programing languages.

On the client side I used JavaScript.

The data was exchanged using an XML packet. Alternately, you could exchange data using a WDDX packet. WDDX is simply XML coded with a convention to to allow the server to supply JavaScript routines (along with the data) to encode/decode the data on the client,

Here's what the process looked like:

The Client Side Application:
1) receive input from the user (or client app event)
2) generate SQL request
3) XML encode the request if necessary
4) pass the request to the web service

The Server Side application:
1) receive request
2) decode (XML parse) the input if necessary
3) analyze the request
4) perform the DB query
5) XML encode the query results
6) return the results

The Client Side application:
1) receive results
2) decode (XML parse) the results if necessary
3) analyze the results if necessary
4) generate DB query to update client DB if necessary
5) perform the DB query to update client DB if necessary
6) display results if necessary

Given: that you have access to (or can write) a web service that functions as described above.

Your iPhone app would likely include:
1) an http interface to access the web service
2) an XML parser
3) an XML encoder
4) an SQLite DB interface

Since the iPhone SDK has only been around a few months, you prolly aren't going to find an example that does what you want. The SDK includes examples using XML and SQLite... the guys at pragprog.com are considering a chapter on consuming WS... and you can google for examples of other pieces.

Whew!

I, too, am interested in an iPhone app that does this. I need to add similar capability to a future version of an app that is currently being prepared for publication. I'd prefer not to spend the time to bring up a DB server, refresh my knowledge on WS publishing, learn how to do it in PHP...

Rather, if someone knows of a publicly-accessible WS that interacts with an SQL DB (receiving DB queries, returning results) then I could concentrate on the client-side iPhone app.

HTH

Dick

[smontgomerie]

Originally Posted by dicklacara

The web service is prolly the best solution.

I haven't screwed around with web services or web programming since 2004, so my answer may be somewhat dated:

The only experience I have writing/publishing WS (Web Services) is using CF (ColdFusion) on the server. It is easy to do but CF isn't as common as PHP and some other web programing languages.

On the client side I used JavaScript.

The data was exchanged using an XML packet. Alternately, you could exchange data using a WDDX packet. WDDX is simply XML coded with a convention to to allow the server to supply JavaScript routines (along with the data) to encode/decode the data on the client,

Here's what the process looked like:

The Client Side Application:
1) receive input from the user (or client app event)
2) generate SQL request
3) XML encode the request if necessary
4) pass the request to the web service

The Server Side application:
1) receive request
2) decode (XML parse) the input if necessary
3) analyze the request
4) perform the DB query
5) XML encode the query results
6) return the results

The Client Side application:
1) receive results
2) decode (XML parse) the results if necessary
3) analyze the results if necessary
4) generate DB query to update client DB if necessary
5) perform the DB query to update client DB if necessary
6) display results if necessary

Given: that you have access to (or can write) a web service that functions as described above.

Your iPhone app would likely include:
1) an http interface to access the web service
2) an XML parser
3) an XML encoder
4) an SQLite DB interface

Since the iPhone SDK has only been around a few months, you prolly aren't going to find an example that does what you want. The SDK includes examples using XML and SQLite... the guys at pragprog.com are considering a chapter on consuming WS... and you can google for examples of other pieces.

Whew!

I, too, am interested in an iPhone app that does this. I need to add similar capability to a future version of an app that is currently being prepared for publication. I'd prefer not to spend the time to bring up a DB server, refresh my knowledge on WS publishing, learn how to do it in PHP...

Rather, if someone knows of a publicly-accessible WS that interacts with an SQL DB (receiving DB queries, returning results) then I could concentrate on the client-side iPhone app.

HTH

Dick

Has anyone found anything that does this since this was first published?

Thanks

[jtara]

XML is almost certainly overkill. Not sure why everybody is so XML-crazy. XML is slow, difficult to parse, and bloaty.

CSV is light and simple. With XML you are sending several times the number of bytes over the air. (Unless you compress, in which case the advantage of CSV may be minimal in terms of bytes transmitted, as XML's bloat is primarly in redundancy.)

You can use a timestamp, or you could use revision numbers. You might prefer a timestamp, because it might be useful for internal auditing. But a revision number is lighter. The revision numbers would have to be GLOBAL within the database, just like a timestamp would be. (That is, not "revision 2 of record 10", but revision 2, period. Each record changed increments the global revision number.)

You need a table in the database that keeps track of the highest revision number (or timestamp) that each user has. Or you could keep track of that in the client.

Updates and new records are easy. Deletions are a bit more complicated, but not very much so - you can't ever really delete a record on the server. Have a "deleted" column in the master database. When you "delete" a record, you set the deleted column true, and the client needs to know when it gets an update, if the deleted column is true, to delete the record. (You CAN delete records on the client side, just not on the server side.)

When the client makes a request to the web service for an update, the server will do a database query for all records with a revision number (or timestamp) greater than the largest revision number/timestamp which the client already has.

There you go, there's your sync. Or, actually, replication.

Now, did you really mean "sync", or just "replicate"? Because if you meant "sync" that is more complicated. That is, will clients be making changes to the database, and those need to be uploaded too?

[smontgomerie]

Originally Posted by jtara

XML is almost certainly overkill. Not sure why everybody is so XML-crazy. XML is slow, difficult to parse, and bloaty.

CSV is light and simple. With XML you are sending several times the number of bytes over the air. (Unless you compress, in which case the advantage of CSV may be minimal in terms of bytes transmitted, as XML's bloat is primarly in redundancy.)

You can use a timestamp, or you could use revision numbers. You might prefer a timestamp, because it might be useful for internal auditing. But a revision number is lighter. The revision numbers would have to be GLOBAL within the database, just like a timestamp would be. (That is, not "revision 2 of record 10", but revision 2, period. Each record changed increments the global revision number.)

You need a table in the database that keeps track of the highest revision number (or timestamp) that each user has. Or you could keep track of that in the client.

Updates and new records are easy. Deletions are a bit more complicated, but not very much so - you can't ever really delete a record on the server. Have a "deleted" column in the master database. When you "delete" a record, you set the deleted column true, and the client needs to know when it gets an update, if the deleted column is true, to delete the record. (You CAN delete records on the client side, just not on the server side.)

When the client makes a request to the web service for an update, the server will do a database query for all records with a revision number (or timestamp) greater than the largest revision number/timestamp which the client already has.

There you go, there's your sync. Or, actually, replication.

Now, did you really mean "sync", or just "replicate"? Because if you meant "sync" that is more complicated. That is, will clients be making changes to the database, and those need to be uploaded too?

jtara,
I was more thinking of a framework for doing this kind of thing - it has to be a common task on the iPhone. But thanks for the suggestions on how to do this, as I suspect I'll be writing my own framework.

I was more thinking of sync (as in, client makes changes on iPhone, they are reflected in a database, which can be changed via another medium, like a web interface, re-synced back to the iPhone - so 2-way sync). I imagine it would be similar to the structure that you propose, where each table in each database (client and remote) has a timestamp, and each record must be diff'd against each other record that has changed since the last sync time.

In terms of protocols, I'm more partial to JSON. It's very lightweight, easy to parse, and much more object-friendly than CSV. And there are both great client-side libraries and server-side support for the protocol, but yeah, XML is way too heavy.

[bugzappy]

Maybe iAnywhere from Sybase will help. I have no idea how expensive it will be (or, open source?). The bad news is it's not even available for Beta yet. The good news is, if they are building infrastructure sw for this problem, then others are too..

http://blogs.sybase.com/ithain/?p=577

[smontgomerie]

Originally Posted by bugzappy

Maybe iAnywhere from Sybase will help. I have no idea how expensive it will be (or, open source?). The bad news is it's not even available for Beta yet. The good news is, if they are building infrastructure sw for this problem, then others are too..

Sybase Blogs - Ian Thain » Blog Archive » i(Phone) have my data here,now i want it there…Whats the problem?

That's a great link bugzappy, thanks! Let's hope for open source...