Metadata and SQL generators: LiteMetaADO, LiteMetaSQL, LiteMetaSQLIdxFK, and SQLlib

The three LiteMeta% classes located in SQLite/MetaSQL, as the name suggests, are specialized in metadata-related queries, while SQLlib (SQLite/SQLlib) provides more generic SQL generation functionality.

SQLlib

SQLlib's factory Create is the default member that takes a single argument representing the target table name used by some methods. Methods not using it can be safely executed using the default SQLlib instance. For example, the following immediate pane command

?SQLlib.CountSelect(LiteMetaSQL.Functions)

generates a query returning the number of available functions.

LiteMetaSQL

LiteMetaSQL focuses on database/engine metadata, so its factory method takes schema alias (defaulting to main). Otherwise, the design and usage of this class are very similar to those of SQLlib. The SQLiteIntropectionExample module in SQLite/MetaSQL/Examples provides several examples. These examples demonstrate retrieval of engine metadata via the predeclared instance and database metadata using a non-default instance of the class.

LiteMetaADO

LiteMetaADO focuses on retrieving the DDL description of database objects using queries generated by LiteMetaSQL. Its factory takes an instance of ILiteADO, and its constructor instantiates LiteMetaSQL with the main schema alias. LiteMetaADO is used, for example, by SQL-based (non-vacuum) clone routine provided by LiteMan.

LiteMetaSQLIdxFK

Unlike most classes, LiteMetaSQLIdxFK has neither factory nor constructor. Instead, the default instance provides its SQL generation functionality. LiteMetaSQL also exposes LiteMetaSQLIdxFK's functionality via encapsulation.

SQLite provides several pragmas to query information about foreign keys and indices. ForeingKeys and Indices construct derived queries combining metadata from individual pragmas into extended tables (see above-referenced module for examples).

FKChildIndices method generates a query returning information focusing on columns children from foreign key relationships and what indices for such columns are available. While the database does not require indices on foreign key children columns, such indices facilitate the processing of cascade restrict clauses. This query aims to identify foreign-key child columns without indices.

SimilarIndices is another experimental query. If the IDX1 index includes columns (A, B), and the IDX2 index includes columns (A, B, C), IDX2 can replace IDX1. SimilarIndices attempts to provide information related to any such combinations, though additional verification is necessary. It may yield some false positives. Whether it can miss indices is not clear.