On 4/4/2014 1:21 PM, da...@dandymadeproductions.com wrote:
On working with the MyJSQLView database GUI access tool it has been determined that a local file/memory database would be valuable to perform recurring analysis on datasets from the connected datasource. Sqlite is being considered as the local database.
If I understand you correctly, you're suggesting making a local snapshot of a networked database to optimize performance. I'm not sure what remote database you're using, but it seems to me with properly designed prepared statements there won't be much gain in downloading everything in advance to the local machine, especially since the download will certainly include more data than is actually needed. Additionally consider the loss of coherency when the upstream database is modified but the local snapshot becomes stale.
All the underlining code has been created for conversion between datasource and local database. The code is now being developed to perform the DB to DB transfer population. The basic algorithm being considered is: Process 1, Loop: 1,2,3 1. Read Datasource row from query. 2. Create SQL INSERT statement. 3. Write SQL INSERT into queue. Process 2, Loop: 4,5 4. Read SQL INSERT from queue. 5. Write SQL INSERT to SQLite db.}
The queue seems to be an unnecessary intermediary. Simply alternate between reading from the remote database and writing the received data to the SQLite database. This simpler design is also more amenable to prepared statements which offer indispensible performance and security benefits. Do keep in mind that an SQL database consists not only of INSERTs (the data) but also CREATE TABLEs (the schema) plus indexes and triggers and views.
Perhaps someone from this forum could possibly comment on another open source project with similar type of db to db transfer that could be studied or alternative algorithm.
It doesn't matter what database you use, the algorithm remains the same: read source, write destination, repeat. Many database systems have convenient import routines for common data sources. For example, the SQLite shell has the .import command for loading a file into a table. -- Andy Goth | <andrew.m.goth/at/gmail/dot/com> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
