[sqlite] Creating system table

[Clemens Ladisch]

Igor Korot wrote:
> There is a notion of system table vs. user table in the DBMS.

System tables are tables with a name beginning with "sqlite_".

> My question is: what should I do if I want to create a system table?

This is possible only by (ab)using the writable_schema pragma:

  CREATE TABLE xxx(what, ever, [...]);
  PRAGMA writable_schema = ON;
  UPDATE sqlite_master
   SET name     = 'sqlite_xxx',
       tbl_name = 'sqlite_xxx',
       sql      = replace(sql, 'xxx', 'sqlite_xxx')
   WHERE name = 'xxx';

Alternatively, modify the SQLite source code.

But why would you want to do this?  Now you have a table that is
not completely accessible, and that will not show up in the output
of .dump.


Regards,
Clemens