Loading database schema files, revisited

[paurkedal]

Caqti currently provides a variant of the query parser angstrom_list_parser which can be used to split up a schema file into individual statements, which can be committed. However, since Caqti does not parse SQL apart from the lexical level, it cannot always know whether a semicolon terminates a top-level statement. The case which motivated this issue comes from #87:

CREATE TRIGGER no_update_stream_events BEFORE UPDATE ON stream_events
BEGIN
  SELECT RAISE (FAIL, 'cannot update stream_events');
END

Note that the BEGIN-END block can contain multiple statements (and thus bare semicolons).

The first question is maybe whether angstrom_list_parser is the right approach. Note here that the input is parsed as a Caqti template, not as a plain SQL schema. This is good in the sense that it allows parametrising schema names or other parts, but doing that means loosing compatibility with other tools (such as psql). This also provides en escaping mechanism which could be used to avoid the issue, though writing $(semicolon) in place of ; is rather verbose and would force non-compatibility.

A better approach if the intention is to load a plain (non-template) SQL schema would be to use a function or parser which merely splits the code at right points, or to allow multiple statements to be submitted in the same commit. The support for the latter must be investigated for MariaDB, while it already seems to work for Sqlite3, and could be supported for PostgreSQL non-prepared statements.

[anmonteiro]

the intention is to load a plain (non-template) SQL schema

this is my use case. I currently have .sql files that represent migrations and may have multiple statements. On my system startup, I load the migration files that need to be applied and execute each within a logical transaction.