I’ve updated SQLite3 to version 3.27.2, and the previous error has gone away. However, the docs are still lying, because information_schema.tables cannot be implemented (evidence below). It seems that we need a pragma_table_list() function, which takes a schema as a parameter (similar to pragma_table_info(), pragma_index_list(), etc.)
Joshuas-MBP:sqldark josh$ sqlite3 SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> ATTACH ':memory:' AS 'information_schema'; sqlite> CREATE VIEW information_schema.tables(table_name) AS ...> SELECT name FROM main.sqlite_master ...> WHERE type = 'table'; Error: view tables cannot reference objects in database main > On Feb 27, 2019, at 2:48 PM, Joshua Wise <[email protected]> wrote: > > Ahh that’s very interesting. I suspect this is the relevant entry in the > release notes: > > SQLite Release 3.26.0 On 2018-12-01 > Enhanced triggers so that they can use table-valued functions that exist in > schemas other than the schema where the trigger is defined. > > Although, we’re talking about views here, not triggers. > > >> On Feb 27, 2019, at 2:43 PM, David Raymond <[email protected] >> <mailto:[email protected]>> wrote: >> >> For me it gives an error up through 3.25.3 and starts working at 3.26.0 >> >> >> >> -----Original Message----- >> From: sqlite-users [mailto:[email protected] >> <mailto:[email protected]>] On Behalf Of Joshua >> Wise >> Sent: Wednesday, February 27, 2019 2:13 PM >> To: SQLite mailing list >> Subject: Re: [sqlite] Documentation is lying to me >> >> I can confirm that it also doesn’t work with SQLite version 3.24.0 on a >> completely different machine. >> >>> On Feb 27, 2019, at 9:23 AM, Joshua Thomas Wise <[email protected] >>> <mailto:[email protected]>> wrote: >>> >>> Joshuas-MBP:sqldark josh$ sqlite3 >>> SQLite version 3.25.0 2018-09-15 04:01:47 >>> Enter ".help" for usage hints. >>> Connected to a transient in-memory database. >>> Use ".open FILENAME" to reopen on a persistent database. >>> sqlite> SELECT sqlite_version(); >>> 3.25.0 >>> sqlite> ATTACH ':memory:' AS 'information_schema'; >>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS >>> ...> SELECT name FROM pragma_database_list(); >>> sqlite> select * from schemata; >>> Error: no such table: information_schema.pragma_database_list >>> sqlite> select * from pragma_database_list(); >>> 0|main| >>> 2|information_schema| >>> >>> It worked when using `pragma_database_list()` directly, but not from inside >>> the view. >>> >>> >>> >>>> On Feb 26, 2019, at 9:26 PM, Keith Medcalf <[email protected] >>>> <mailto:[email protected]> <mailto:[email protected] >>>> <mailto:[email protected]>>> wrote: >>>> >>>> >>>> Unable to reproduce: >>>> >>>>> sqlite >>>> SQLite version 3.28.0 2019-02-25 18:43:54 >>>> Enter ".help" for usage hints. >>>> Connected to a transient in-memory database. >>>> Use ".open FILENAME" to reopen on a persistent database. >>>> sqlite> ATTACH ':memory:' AS 'information_schema'; >>>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS >>>> ...> SELECT name FROM pragma_database_list(); >>>> sqlite> >>>> sqlite> select * from schemata; >>>> main >>>> information_schema >>>> sqlite> .exit >>>> >>>> What version of SQLite are you using? >>>> What do the following commands do? >>>> >>>> pragma database_list; >>>> >>>> select * from pragma_database_list(); >>>> >>>> >>>> --- >>>> The fact that there's a Highway to Hell but only a Stairway to Heaven says >>>> a lot about anticipated traffic volume. >>>> >>>>> -----Original Message----- >>>>> From: sqlite-users [mailto:sqlite-users- >>>>> [email protected] <mailto:[email protected]> >>>>> <mailto:[email protected] >>>>> <mailto:[email protected]>>] On Behalf Of Joshua Thomas Wise >>>>> Sent: Tuesday, 26 February, 2019 19:06 >>>>> To: [email protected] >>>>> <mailto:[email protected]> >>>>> <mailto:[email protected] >>>>> <mailto:[email protected]>> >>>>> Subject: [sqlite] Documentation is lying to me >>>>> >>>>> In this SQLite3 documentation >>>>> (https://www.sqlite.org/pragma.html#pragfunc >>>>> <https://www.sqlite.org/pragma.html#pragfunc> >>>>> <https://www.sqlite.org/pragma.html#pragfunc >>>>> <https://www.sqlite.org/pragma.html#pragfunc>>), it says that >>>>> information_schema could be implemented by doing something like this: >>>>> >>>>> ATTACH ':memory:' AS 'information_schema'; >>>>> CREATE VIEW information_schema.schemata(schema_name) AS >>>>> SELECT name FROM pragma_database_list(); >>>>> >>>>> However, when attempting to do this, we get an error: "no such table: >>>>> information_schema.pragma_database_list”. >>>>> _______________________________________________ >>>>> sqlite-users mailing list >>>>> [email protected] >>>>> <mailto:[email protected]> >>>>> <mailto:[email protected] >>>>> <mailto:[email protected]>> >>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>>> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> >>>> >>>> >>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> [email protected] >>>> <mailto:[email protected]> >>>> <mailto:[email protected] >>>> <mailto:[email protected]>> >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> >>> >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> <mailto:[email protected]> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> <mailto:[email protected]> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
