wiki:TCTSqliteProposal
Last modified 9 years ago Last modified on 04/14/2010 10:29:12 AM

Proposal to add SQLite3 to the LSST stack

Package

SQLite3 is a well-supported, well-established, public domain, self-contained, serverless, cross-platform, zero-configuration, transactional SQL database engine. Its databases can be used in memory or can be persisted and retrieved from architecture-independent files. It is well-integrated with Python, and the LSST Python will include support for it if it is present on the system when Python is built. In addition, it provides a standalone command line utility that can be used to query database files.

Need

The data butler needs access to a registry of metadata for the datasets it is providing access to. In production contexts, this registry can be located in the science database, the scratch database, or another database. In debugging and other "offline" contexts, however, standard database access may not be possible or desirable.

There are three reasonable alternatives for implementing an offline metadata registry that can be packaged into a tar file with code and data:

  • Analyze the available files without any auxiliary information and maintain the registry in memory.
  • Have the registry be in a simple file format like PAF or CSV.
  • Have the registry be a SQLite3 database file.

The first two have the downside that querying the registry requires the implementation of database-like functionality to provide the equivalent of a "SELECT DISTINCT ... FROM ... WHERE ..." SQL query. If SQLite3 were to be added to the LSST stack, all three alternatives could be implemented using its library, with the first two using in-memory tables only.

Note that MySQL is not suitable for use in this arena, as its setup and configuration is considerably more complex.

Proposal

In order to avoid re-inventing a very common wheel and to save coding and testing an offline metadata database, I propose that the TCT approve the addition of SQLite3 to the stack.

Version 3.3.0 or greater should work. Note that Subversion 1.6 requires SQLite3 3.4.0 or greater and recommends 3.6.13 or greater.