SqLite to MySQL and back or ?

[Bob Sneidar]

Typically what most do is issue a dump of the sql database. If it is not too large then this is workable. It produces a text file that can then be imported into other databases. 

But why not have your app access the mySQL database online to begin with? You would need to modify the table structures so that a student could only see the records for his own login, or else create separate tables with the students user name (limited to 8 characters or something) appended to the tables names. Something like grades_bobs. 

I get how you want to have the students work offline then sync later, but this would involve queries to delete everything for that student from the tables before importing the text file. And if for some reason the import fails you will be pulling your hair out. 

I have an interface that opens a primary and secondary database and "syncs" between the two, but it is unpolished. I DO have merging working, but I have not tested it thoroughly as of yet. It has a Database Setup card where you input the hostname (or filename for sqLite) a user and password, and the save it. You then click the connect button for each database. Once connected there is an interface for linking tables, and then columns. These are all saved in card properties. The Merge feature reads this data and only merges data that is not different between each record. 

It's all moot though if the students are in a location without access to the mySQL database right? How are you going to get the dump OR the outside connection to do what you have to do?

Bob


On Oct 17, 2011, at 12:47 PM, John Patten 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