CODEWITHSUNDEEP
phpiossqlite
에이바바
에이바바

Reputation: 1031

Best way to determine if a database has been changed?

I'm working on architecting a sqlite database for a gallery application. I would like to know if using a time stamp to check if the db has been updated is good practice, or if there is a better way to do this.

Here's what I have...three tables:

buildings
  buliding_id PK
  code
  name
  description
  timestamp

building_album
 album_id PK
 building_id FK (reference to buildings->building_id)
 album_url
 timestamp

building_images
 image_id PK
 album_id FK (reference to building_album->album_id)
 image
 timestamp

As of now I would have the application check against the server's db to see if any time stamps have changed, and if they have change those fields.

Upvotes: 1

Views: 2801

Answers (1)

Rob
Rob

Reputation: 437412

I'd start here for a broader discussion on the topic: How to Sync iPhone Core Data with web server, and then push to other devices?.

But if you wanted to do some simple, one-way synchronizing of information from the server to the mobile device, one "simple" approach would be:

  1. modifying that server database to introduce the concept of some unique identifier (whether a transaction id or version id) that is used across all of the tables;

  2. making sure that the server is modified to record any creations, updates, and deletions to the "master" tables with that identifier which is automatically incremented/updated; this transaction/version identifier could either in the tables themselves or using some shared transaction log;

  3. writing a routine on the server so the client can retrieve the current identifier;

  4. have the app keep track of the last identifier for which it successfully received and completed synchronization/replication;

  5. write some server routine that the client can call with its "last successful" identifier, for which the server will return all records that have changed since that identifier; and

  6. write some mobile device code to take the response from the prior step and update its internal database accordingly.

If you want to also synchronize changes on the mobile and send them back to the server, or if you have constraints upon what changes you can make in the server database, then this all becomes significantly more complicated.

Upvotes: 4

Related Questions