SqLite to MySQL and back or ?

[John Patten]

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