On 29/08/2020 23:36, John Cowan wrote:

> -table-names and -column-names can be done with ordinary SQL (by
>> querying magic SQLite-only tables). Do they need their own procedures?
>>
>
> Yes, if we ever hope to be portable off SQLite.  Providing them means that
> dynamic SQL can be constructed readily.  For example, you could have an
> application with a UI form where the user indicates the columns they want
> to use to populate a table of data.  The programmer would use
> sqlite-column-names to populate the form, and then specify a SQL statement
> like "SELECT ?cols FROM table".  By providing a list of the chosen column
> names (as symbols) as the "cols" argument, the statement will be correctly
> created with no risk of injection attacks from bogus column names.  And now
> you see why SQLite ?s are not enough.

An even more mainstream (and arguably important) use for these is in
automatic schema migration; if your application, upon opening a database
file, checks to see what tables and columns exist, it can add any that
are missing. This enables:

1) When creating a new file, the application will fill it with empty
tables so that subsequent inserts will work.

2) When V2 of your app opens a file created with V1, it will upgrade the
schema (in a way that, if your app is carefully written, will not
prevent the file being opened by V1 again, too, but that's a separate
issue!) so that V2 queries will work.

--
Alaric Snell-Pym   (M7KIT)
http://www.snell-pym.org.uk/alaric/