SqLite to MySQL and back or ?

[Pete]

Sounds like what you want to is sync the databases accounting for any
inserts, deletes, updates on either database.  That gets pretty complicated!
 I think I'd probably take the route of accessing the remote mySQL db all
the time if it's at all feasible and i not, then using ftp to
upload/download the database would be my next choice, but only if you are
150% sure that each student only ever accesses their own table.

If that's not feasible, then one method I've seen (but not implemented) is
to use triggers to store a log table of all activity to a table.  The log
table would contain the same columns as the original plus a timestamp and an
indication of the activity (insert, delete, update).  The sync process
consists of getting the log table rows sorted by timestamp then taking the
appropriate action on the "other database".  If delete, then delete the row
from the other db table, if insert, insert it, if update, update it.  There
is a REPLACE command that I think inserts a row if it doesn't already exist,
or deletes the old row and inserts the new one if it does already exist, but
I haven't tried using it.

You'd have to be sure that the primary key of the table entries in both dbs
were the same for this or any other syncing scheme to work.



Pete
Molly's Revenge <http://www.mollysrevenge.com>




On Mon, Oct 17, 2011 at 12:47 PM, John Patten <johnpatten at mac.com> wrote:

> Hi All!
>
> I have a little project that is using SQLite to save user data.
> Essentially, each user has their own table created in the database and it
> saves records consisting of text content in mutiple fields in each record.
> The idea was in a school situation, where multiple students using the same
> computer, I could have different users use the same app and have access to
> just their data.  This seems to work quite well, and it doesn’t require a
> constant network connection.
>
> Now I want to allow the student to download the app when they get home and
> be able to access and update their (data) table and records on a copy of the
> app at home.
>
> I was thinking I could query the local database (sqlite) save it, then do
> the same on the mysql database in the cloud (on-rev). However it would seem
> to be pretty complex; query the mysql db for the existence of the user
> table, if not present, create the table and import all the data into a
> duplicate database on mysql or if it is present, update those records with
> matching ids and create new records for those records that were not a match.
> The student would then have to do that same process when they got back to
> school for any new info they saved. Seems like a lot a work ;-)
>
> Am I on the right track, or is their an easier way? I.e. What about...  FTP
> process that uploads the sqllite data base from user’s local machine to a
> server available on net, and then from home ftp download process that saves
> SQLite database to student’s home machine. This almost sound easier to me
> than writing a bunch of sql  and irev files to do all the sql query stuff.
>
> What do you think?  Interested in what others have done.
>
> It would be nice if there were a sql query that just essentially did a:
> on mouseUp
>  put sqlite table1 of database "MainData" into tTable1
> copy tTable1 to (new or update)  table on remote mysql database "Maindata""
> ;-)
> end mouseUp
>
>
> Thank you!
>
> John Patten
> SUSD
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>