On Sat, 12 Mar 2016 13:07:01 -0500 Igor Korot <ikorot01 at gmail.com> wrote:
> My question is: what should I do if I want to create a system table? Change the source code? A system table differs from a user table in how it's created. User tables are defined with CREATE TABLE of course, but system tables are representations of the DBMS's data. The underlying structure of a system table need not be -- and normally isn't -- the same as that of a user table. Different system tables may have different underlying structures. An analog in Linix is the /proc filesystem. System tables are a very old idea. They're mentioned in Codd's 12 rules for defining a relational system. *All* data in the system, including metadata, are to be represented as tables and manipulable using the same language as ordinary tables. One language, one datatype. For example, you might do us the service of creating sqlite_constraints, with columns tablename, colname, and constraint. When the user selects from that table, you could parse the database's DDL and return those columns. A more pressing problem from this perspective is pragmas. To discover the value of a pragma in SQLite, one uses the pragma statement. Better would be a table sqlite_pragmas listing all the current values. --jkl
