‎‏‎‎‎‎‎‎‎‏‎‎‎‎‏‎‎‎‏‎‎‏‎‎‎‏‏‏‏‏‏‎‏‎‏‎‏‎‎‏‎‎‏‏‎‏‎‎‎‎‎‏‏‏‏‎‏‎‎‏‏‎‏‏‎‎‎‏‏‎‏‏‏‏‎‎‏‎‏‎‏‏‏‎‏‏Navigation‏

Master Datasmith

Opening SQLite databases

posted Dec 5, 2009 10:54 AM by Tom Gleeson   [ updated Feb 7, 2010 8:25 AM ]
In the previous example (test SQL.xls) I introduced one method of opening a SQLite database within xLite, the xLiteLoadUnLoad function. This function is both useful & dangerous, it will replace the main (App) database's contents with the database provided by the first parameter. Typically the target database is the default empty in-memory (:memory:) database so no harm done, but if the default database was a file-based database, whose contents you wished to preserve, this might not be what you wanted. The second parameter if set to TRUE (FALSE being the default) works in the opposite direction i.e. replaces the specified database's contents with those of the main (App) database.

So, is there a 'less exciting' method of loading and working with database files. Several, in fact:

  • xLiteAttachDB(databaseName,alias); will attach a database to the App database and assign it an alias. See http://www.sqlite.org/lang_attach.html
  • Setting a custom property of xLiteDB to a database name in a 'driver' workbook, this database will then be used as the default App database. In test_databases.xls, the xLiteDB property is "Nwind.db".  As well as file based databases, the xLiteDB property can be set to ":temp:" or ":memory:", :temp: is like :memory: but will use RAM & a temporary file while :memory: only uses RAM.
  • The similar custom property of xLiteHelperDB controls what Helper (i.e. Logging) database to use.  In test_databases.xls this is set to "log.db".
  • Another option is to use the custom properties xLiteAttachDB & xLiteAttachDBAlias to auto-attach a database at startup.
  • Yet another method is provided by the custom properties xLiteDBSeed and xLiteHelperDBSeed. Does the the equivalent of xLiteLoadUnLoad(databaseName,FALSE) at startup.
There's also an xLiteClose function that is typically called by the 'driver' workbook on a before_close event, but can also be called at the end of script (as in test_databases.xls).  This will release resources held by the App & Helper databases and if they were seeded via xLiteDBSeed or XLiteHelperDBSeed, will save back any changes to the seed databases.

You will notice that the test_databases.xls example runs slower that the test_SQL.xls workbook, this is due to the use of an external log.db database rather than the in-memory default.  The logging functionality commits for each log record written (and being in beta mode there's lots of INFO messages being output). Because of SQLite's ACID nature, such frequent commits result in a slow application.  In fact, a general rule of SQLite; if SQLite appears slow, it's nearly always down to too frequent commits; in other words, use transactions. The logging functionality will be improved to allow for INFO-ERROR-FATAL levels to be configured which will reduce this commit hit in the future e.g. production configuration only FATAL or ERRORs would be recorded.

To see the above in action download test_databases.xls from here ...