Documentation issue (wrong table name) in FAQ, point 7

(1) By anonymous on 2020-09-15 17:50:32 [link] [source]

FAQ Point 7 is: (7) How do I list all tables/indices contained in an SQLite database

The answer given is:

From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python bindings) you can get access to table and index names by doing a SELECT on a special table named "SQLITE_SCHEMA". Every SQLite database has an SQLITE_SCHEMA table that defines the schema for the database. The SQLITE_SCHEMA table looks like this:

    CREATE TABLE sqlite_schema (
      type TEXT,
      name TEXT,
      tbl_name TEXT,
      rootpage INTEGER,
      sql TEXT
    );

And to query all table names, this is given:

SELECT name FROM sqlite_schema
WHERE type='table'
ORDER BY name;

But this doesn’t work, as there is no table called SQLITE_SCHEMA. Executing the sample query results in 'no such table: sqlite_schema'.

Did you mean sqlite_master? That table does exist and contain the information stated.

(2) By Richard Hipp (drh) on 2020-09-15 18:01:07 in reply to 1 [link] [source]

The name has been changed to "sqlite_schema" as of version 3.33.0. The older name "sqlite_master" is still accepted as an alias for backwards compatibility. The FAQ is for version 3.33.0. You must be using an older version of SQLite.

(3) By anonymous on 2020-09-15 18:47:59 in reply to 2 [source]

Alright, thank you for the clarification. I’m using 3.31.1, which is the current default in Ubuntu 20.04.

Then maybe add “Prior to version 3.33.0, this table was named sqlite_master.” The changelog doesn’t state anything about this, either.

Or was this intended to be a silent change, so that no-one gets offended by the presence of that word anywhere? I suppose it was.

Then I apologize to anyone who might got offended by the mentioning of that word in my initial post.

(4) By anonymous on 2021-12-15 21:59:01 in reply to 3 [link] [source]

If someone gets offended by the word "master", they mostly likely aren't a programmer since they'd be pretty ancient if they had experienced any perjorative use of the term.

(5) By anonymous on 2021-12-16 07:49:10 in reply to 4 [link] [source]

Words can have multiple meanings; I am not offended by them and would hope that you wouldn't either (but of course I am not going to control that). However, the new name sqlite_schema is a better name anyways, since that is what it is (and it matches with the writable_schema pragma, too); it is the schema table. When I first saw the change, I was worried that it might ruin programs using authorizer callbacks in some cases, but I soon found out that it uses the old name in authorizer callbacks so that it doesn't break the compatibility, so that is good. The other thing is potential confusion with the SQLITE_SCHEMA error code, but the C codes are case-sensitive and SQL codes aren't case-sensitive.

Perhaps the better way when writing the programs now would be normally using the new name sqlite_schema, but the older name sqlite_master in authorizer callbacks and in programs that don't use any of the new features (including bug fixes) since the version of SQLite that the new name was introduced.

(If it was up to me, I would not have changed it in SQLite3 (since I would consider it unimportant and adding unnecessarily more complexity) (although I would call it the "schema table" in the documentation, since that is what it is), but would have changed that table name to sqlite_schema in a new major version that works differently (e.g. SQLite4), without keeping the sqlite_master name. However, now it is what it is, and that is OK.)