4

I have a local app that uses SQLite. Whenever it has internet access it requests the whole database from the server and recreates the local one from that. Local and Server databases have the same structure, basically the point of the local one is to guarantee function even when no internet is available.

This is a very inefficient way of doing this. My question is, how to ask for only data that is missing?

Should I send the last ID from each local table and have the server send data from that ID onward? What happens if an existing ID was modified? This would mean that all data should be checked, but sending the whole database for checking and getting back the modifications or additions also seems stupid.

The configuration is Local SQLite, Server MySQL. I could probably change the server to SQLite if it's recommended.

EDIT:
Multiple clients make requests to the same server MySQL Database, PHP processes the request and replies.

How would you tackle this?
Thank you.

Francisc
  • 77,430
  • 63
  • 180
  • 276
  • 1
    I would recommend changing client to mysql -- A mysql without innodb is not that heavy. And you can do replication by log shipping. – J-16 SDiZ Aug 22 '11 at 13:25
  • That's a very good recommendation, but I'm afraid the local client cannot run a MySQL client. It needs to be SQLite or a no-config database. – Francisc Aug 22 '11 at 13:30

1 Answers1

3

I'd either timestamp the rows in the database and fetch by date, or use rsync (or librsync or similar) to synchronize the database files.

Hasturkun
  • 35,395
  • 6
  • 71
  • 104
  • Timestamp sounds good, but I'm worried about size of the database by adding a timestamp of 14 chars to each recording. From your experience, this isn't an issue? – Francisc Aug 22 '11 at 13:11
  • If the server and client aren't using the same binary format, the client doesn't actually need to receive or store the timestamps (also, MySQL most likely does not store `TIMESTAMP` as 14 chars, as suggested by its range (ends at 2038)). The client only ever needs to keep the last successful request time. – Hasturkun Aug 22 '11 at 13:21
  • MySQL timestamp has more than 14 chars because it adds dashes and spaces. I'm trying to sync SQLite (local) with MySQL (server) and I think that means that I need to store the timestamp in a column for each table because there are multiple clients that are asking to update their local SQLite from the server. – Francisc Aug 22 '11 at 13:27
  • MySQL timestamps are most likely stored as a 32 bit unix time value, representing seconds since the epoch (1970-01-01 00:00:01), which explains the range (see [`DATETIME`](http://dev.mysql.com/doc/refman/5.0/en/datetime.html) documentation. There should not be a significant storage overhead for these. Just to clarify, the timestamps should represent the row's last modification time. – Hasturkun Aug 22 '11 at 14:49
  • Yup, got that, Hasturkun. Thank you. You seem to have experience with this, so tell me, does it seem like good practice to ask the server something like "here's the last time I asked for data, send me changes" and get a reply of either SQL queries or data which I turn into queries in the application on the client side. Is that how this is done? – Francisc Aug 22 '11 at 17:38