Accessing SQLite databases from Excel via microETL

MicroETL makes two SQLite instances available to Excel. The main database is the App database, by default an in-memory instance; the second is a Helper instance, again by default in-memory, which is used to hold logs and to enable SQLite to perform certain activities that would not be possible without two separate database instances. The main commands such as SQL() deal exclusively with the App instance, and for the most part that’s the only database that a microETL user needs to be aware of.

Within the microETL project, VBA routines can gain access to the App instance via the gAppDB global variable (gHelperDB for the Helper instance) while Python routines use the global dictionary element connDict(“App”) or connDict(“xlite”) for the App database (and connDict(“Helper”) for the Helper).

SQLScript (and in-cell formulas) can gain access to the Helper instance via the xLiteSQL(inSQL,outRange,NoHeader,PasteOver,useHelperDB) command, by setting useHelperDB to True. (The SQL() function is a thin-wrapper on this function to handle positional substitution tokens (:1, :2 …), the main functionality,such as the table() preprocessor commands and output to a range, are provided by xLiteSQL.)

These default in-memory databases are blank databases that are  instantiated in memory at workbook start-up and any tables or data created during the workbook session are discarded at shutdown. The standard use-case assumes that any data to be saved or fetched will come from Excel or perhaps via external CSVs or corporate database connections as would be the case with a normal Excel workbook, i.e. the main use for SQLite is assumed to be a provider of a SQL layer on top of Excel not a primary data-store.

However, SQLite is a superb data-store, one which is used by 1000s of applications (quite likely many of apps on your smart-phone or iPad use SQLite as their database). Its ACID nature and small foot-print makes it an ideal means of storing client-side information that would otherwise need to be communicated to a “proper” server-side database such as MySQL or SQL Server.

MicroETL supports the use of disk-based databases for both the main App and companion Helper instances via:

  • xliteAttachDB(databaseName,alias); this command will attach a database to the App database and assign it an alias. This is a wrapper on the SQLite Attach command  http://www.sqlite.org/lang_attach.html. Note: the SQL Attach command can not be used via the SQL() command, but can be used by the “raw” fastSQL() function.
  • xLiteLoadUnLoad(filename[,unload]); this function is both useful & dangerous, it will replace the main (App) database’s contents with the contents of the SQLite file 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.
  • Setting a custom property of xLiteDB to a SQLite filename in a driver workbook, this database will then be used as the default App database. 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 driver workbook is the first workbook to initiate microETL, so be careful of  multiple workbooks with different App “expectations” being accessed in the same Excel instance.
  • The similar custom property of xLiteHelperDB controls what Helper (i.e. Logging) database to use.
  • 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 an xLiteClose() function which release’s 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.

UPDATE:

As per Michael’s suggestions below, I’ve add the ability to attach an external database into an “aliased” :memory: database, see http://blog.gobansaor.com/2011/05/20/attach-a-sqlite-database-into-excels-memory-via-microetl/

Advertisements

8 responses to “Accessing SQLite databases from Excel via microETL

  1. Pingback: microETL’s SQL function | Gobán Saor

  2. Hi Tom,

    I’ve been following the evolution of MicroETL for a small while now and I really like your ideas. When I played around with the SQLite-integration, I wondered whether there is a way to directly attach a db into memory (and to “detach” it back to disk. As far as I see, at the moment it is only possible to replace the whole app-db with an in-memory instance via LoadUnload.

    Best,
    Michael

    • Michael,

      No, SQLite doesn’t support the concept of ‘attaching into memory’, but it I agree it would be useful. The only alternative I can see (assuming the idea is to maximise the amount of ‘attached data’ cached in memory) would be to play around with the PRAGMA cache_size (most PRAGMA commands can be called via SQL(“PRAGMA ..”) , or if it objects (due to its wrapping savepoint) use fastSQL() or xliteRawSQL()).

      For ultimate control the Application Defined Page Cache API http://bit.ly/keIdV8 would seem to be route to go down, but I’ve not looked in detail at it (or indeed how SQLite’s default caches work). But first maybe try setting the cache_size http://bit.ly/mdfZ12 to a very high number and see how SQLite responds (maybe ‘seeding’ the cache with common SQL statements to pre-load frequently used datasets). If it worked I could add a custom property to allow setting the cache size (in fact, microETL could probably do with a generic PRAGMA custom property mechanism for other PRAGMAs).

      Tom

  3. Tom,

    thank you for your fast reply. Extending the cache size would indeed a promising approach.
    However, delving into the SQLite documentation, I stumbled upon the SQLite Backup API, including the example C function loadOrSaveDb (which you seem to use?). This function calls the function sqlite_backup_init which takes the source and the destination database names as parameters. loadOrSaveDb uses “main” for both, but replacing the destination name with an alias of an existing attached in-memory-db puts the db from the source db into memory as an attached db (if the parameter isSave is 0) – exactly what I intended in my original question.
    I implemented this in a new function loadOrSaveDbWithAlias which additionally takes an alias name as input parameter (if no alias is provided, “main” is used as in the original function). I tested it, and it actually works (in both directions, loading and unloading). If you are interested to incorporate this into xLite, I can send you the source.

    Michael

    • Michael,

      Excellent, that would actually make the xLiteLoadUnLoad() function useful!

      And yes I use the self same loadOrSaveDB() example, so for load if I change…
      pBackup = dll_sqlite3_backup_init(pTo, “main”, pFrom, “main”);
      to
      pBackup = dll_sqlite3_backup_init(pTo, sInDBAlias, pFrom, “main”);
      while first Attach’ing a :memory: database with an same alias name as assigned to sInDBAlias and for save do …
      pBackup = dll_sqlite3_backup_init(pTo, “main”, pFrom, sInDBAlias);

      And split VBA xLiteLoadUnLoadDB() (never liked load an unload in same function) into ..
      xLiteLoadDB(inDatabaseFileName, Optional dbAlias as String=”main”, Optional attachAliasAsMem as Boolean=True)
      xLiteUnLoadDB(outDatabaseFileName, Optional dbAlias as String=”main”)

      If you could send the source of the modified C routine that would be great, first external contribution to the project!

      Tom

  4. Tom

    Splitting into two functions seems to be a good idea.
    In particular, with the signature you provided for the xLiteLoadDB function, one will only need a single function to load / attach a database. Given an alias other than “main” and with the parameter attachAliasAsMem = false , the database would be attached in the normal way. Thus, the user can decide whether he wants to load a db “into memory” or to ATTACH it. One only has to be aware of the difference that in the in-memory-case changes affect the original db only after unloading while in the other case, they are commited after each transaction.
    Indeed, this important semantic difference might be a reason to keep both functions: the xLiteAttachDB function to ATTACH a db in the sqlite semantics and the xLiteLoadDB function without the last parameter – thus loading would always mean “loading into memory” and having to unload it to commit the changes back to the on-disk-db.

    Michael

    • Michael,

      Yes, I need to revisit the whole area of Attach and Loading, put a bit of “joined-up-thinking” into it. I’m tending towards separate functions for Attach and “memory loading” due to the need to be aware of the difference in how changes are persisted.

      For datasets that need the speed of in-memory access but the reliability of ACID for updates in might be worth looking further into the idea of increasing the cache size combined with in “query seeding”; but as I said before, I would need to better understand how SQLite uses it caches i.e can SQLite recognise that cache pages loaded via a “Select * from myTable” can also serve a subsequent “Select aCol from myTable” and so on ?

      Tom

  5. Pingback: Attach a SQLite database into Excel’s memory via microETL | Gobán Saor