Synching between Sqlite and MySql

Rails
1

Hi folks,

I am running an offline Rails app that periodically connects to the
Internet to download the latest catalogue, and upload any new orders.

I am about to implement the synching bit - and was thinking whether
there is any other solution other than writing webservices and code to
do a manual sync of the data, or whether there is a more nifty and
robust way using say replication?

Anybody done anything like this before?

Joerg

2

Well, if the structure permist why not just use ActiveRecord to handle
all of that? Just change the model’s database connetion. Assuming the
models are the same between offline and online clients this shouldn’t
that difficult.

Check out the ActiveRecord rdocs for an example of having a model use
a different database. The wiki has a few entries concerning the topic
too.

You of course would have to setup the syncing routine and all that
good stuff, but you would have to do that anyways :slight_smile:

-Nick

3

Not sure that this’ll work, as the client is on a separate computer in a
different country … it won’t be possible for me to setup an SSH
connection for them to allow their app to speak directly to the remote
db … the app is distributed on CD to various people who want it …

4

Yeah - that’s what I started doing - was just checking if there was an
easier solution … especially synching up all the primary keys from all
the different CD’s creating their own orders etc … :frowning: Yikes.

5

Hello,

You could do that with a webservice, also written in Rails. You do not
need to expose a mysql-server this way. The CD-Application connects to
the webservice which intelligently transmits only the missing data to
the client. The action simply selects the data-rows where updated_at >
syncronized_at.

regards,
Helmut

Am 12.01.2006 um 18:18 schrieb Joerg D.:

6

On Jan 12, 2006, at 8:35 AM, Nick S. wrote:

good stuff, but you would have to do that anyways :slight_smile:

-Nick

Be very careful if you decide to go this route. you cannot currently

obfuscate ruby code so your main server’s database password will be
available to anyone you give the application to.

Cheers-
-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]

7

Since Rails uses integer IDs instead of GUIDs, how are you going to
keep the IDs from colliding? (I’m assuming there’s more than 1 user.)

Here are a couple of links that explain synchronization/replication,
but they are meant to be used by sysadmins:
sqlylog http://www.webyog.com/
Using MySQL’s Built-In Replication To Maximize Availability
http://www.phpbuilder.com/columns/tanoviceanu20000912.php3?page=1

8

GUIDs would have to be the way to go. The only drawback I see of using
GUIDs is that the URL’s become very ugly.

To use GUIDs you’d have to make your id column a 32 character string,
and instead of allowing ActiveRecord to use the DB to generate the
autoincrement id’s, you set the id GUID value yourself in your model
before saving.

I haven’t yet implemented it - just deducing that these could be the
steps to make Rails use GUIDs instead of int.

You could use http://rubyforge.org/projects/uuidtools/ to generate your
GUIDs.

Any thoughts?

9

Yes, nasty ugly URLs.
I’m afraid I don’t have any experience with GUIDs since IDs have been
sufficient for my needs. I was just interested in whether you had some
new, super-cool way of merging. You might also check out,
http://dema.ruby.com.br/articles/2005/09/14/guid-uuid-as-primary-keys-in-rails
and Typo also uses GUIDs in a few places.
Lots of luck.