Why all the complication? You just need to set pragma writable_schema=1; so that you can create a table with an ill-advised name (starting with sqlite_).
After it is created, it behaves just like any other table -- until you want to drop it -- that requires complication. SQLite version 3.12.0 2016-03-12 16:32:16 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table sqlite_mysystemtable(a); Error: object name reserved for internal use: sqlite_mysystemtable sqlite> pragma writable_schema=1; sqlite> create table sqlite_mysystemtable(a); sqlite> insert into sqlite_mysystemtable values (1); sqlite> pragma writable_schema=0; sqlite> insert into sqlite_mysystemtable values (1); sqlite> insert into sqlite_mysystemtable values (1); sqlite> update sqlite_mysystemtable set a=2 where a=1; sqlite> pragma writable_schema; 0 sqlite> drop table sqlite_mysystemtable; Error: table sqlite_mysystemtable may not be dropped sqlite> pragma writable_schema=1; sqlite> drop table sqlite_mysystemtable; Error: table sqlite_mysystemtable may not be dropped sqlite> > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Clemens Ladisch > Sent: Saturday, 12 March, 2016 11:42 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] Creating system table > > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
