Does table have rowid?
(1) By anonymous on 2021-01-30 00:09:01 [link] [source]
Is there an SQL statement that will return true if a table has ROWID?
(2) By Keith Medcalf (kmedcalf) on 2021-01-30 00:41:01 in reply to 1 [link] [source]
How about:
select coalesce((select rowid == rowid from table where rowid is not null limit 1), 1);
which will return 1 if the table has an attribute accessible by the name rowid otherwise it will throw an error that the column does not exist. This can be used to test for the existence of any accessible attribute of table ...
select coalesce((select schema == schema from pragma_table_xinfo where schema is not null limit 1), 1);
will return 1 because schema is a valid attribute (hidden column) of pragma_table_xinfo.
(4) By anonymous on 2021-01-30 00:53:57 in reply to 2 [link] [source]
select coalesce((select rowid == rowid from table where rowid is not null limit 1), 1);
Appears to work; thanks. I need to test it further.
I am intrigued by == since usually SQL sees = as assignment or comparison depending on context.
(5) By Keith Medcalf (kmedcalf) on 2021-01-30 01:43:37 in reply to 4 [link] [source]
== is just a habit from using language that differentiate between equality tests and assignment.
select coalesce((select rowid IS rowid from table limit 1), 1);
should work as well since null is null is True ...
(3) By Keith Medcalf (kmedcalf) on 2021-01-30 00:50:35 in reply to 1 [link] [source]
If you mean that tells you if the table is a "rowid table", if the table rowid is implicit or explicit, and if explicit what the name is, and if it has the autoincrement attribute, the answer is no. That information is stored in the schema but it not exposed by the introspection pragma's ...
(6) By anonymous on 2021-01-30 09:34:41 in reply to 3 [link] [source]
but it not exposed by the introspection pragma's ...
That's a pity.
Therefore, the only way is to select rowid from table; and success will indicate its presence and failure will indicate its absence.
Some uncertainties I need to explore hands-on:
Can the rowid column (implicitly added unless WITHOUT ROWID is added when the table is created) can be renamed.
Can the user create a like named rowid column containing other than an auto increment value? Then, would there be TWO rowid columns, one hidden and one not?
Do views have the rowid column?
The objective is to retrieve tables in batches of n records with a sql such as this:
int n = 100 // batches of 100 records
int m = 0; // rowid starts with 1
while (test returning failure)
{
select * from my table where rowid > n limit n;
}
m = m + n
`
(7) By anonymous on 2021-01-30 11:22:26 in reply to 6 [link] [source]
This table has a rowid, but there's no way to access it:
create table corner_case (
rowid real,
_rowid_ real,
oid real);
For slicing the result of a SELECT, use LIMIT and OFFSET.
(8) By Richard Damon (RichardDamon) on 2021-01-30 11:52:44 in reply to 6 [link] [source]
First, it should be pointed out that if the table gets updated between calls like this (even fixed for the lack of use of m) isn't going to always get all the rows of the table or a snapshot at a given time unless the whole thing is wrapped in a transaction.
And once you wrap it in a transaction, you might as well just select all the rows at once and then just step to fetch them n at a time, do what you were planning on doing then step the next n.
(9) By Richard Hipp (drh) on 2021-01-30 11:57:36 in reply to 1 [source]
This query returns non-zero (true) if the table whose name is $TABLENAME is a rowid table:
SELECT count(*)==0 FROM pragma_index_info($TABLENAME);
The trick here is that a non-rowid table (that is to say a WITHOUT ROWID table) is really implemented as a stand-alone index that has no associated table. So the the "PRAGMA index_info=TABLE" statement returns some information about a WITHOUT ROWID table, since a WITHOUT ROWID table is implemented as an index. But a rowid table really is a table, and so "PRAGMA index_info" returns no information about a rowid table.
Here is a query that will list all tables in the schema and show you for each one whether or not it has a rowid:
SELECT name,
NOT EXISTS(SELECT 1 FROM pragma_index_info(x.name)) AS 'hasRowid'
FROM sqlite_schema AS x
WHERE type='table'
ORDER BY name;
(10) By Ralf on 2021-01-30 13:13:06 in reply to 9 [link] [source]
The trick here is that a non-rowid table (that is to say a WITHOUT ROWID table) is really implemented as a stand-alone index
This is interesting. Especially the misleading error message generated if an index with the same name already exists. Example:
drop table if exists t1;
create table t1 (a);
create index i1 on t1(a);
drop table if exists i1;
create table i1 (a primary key) without rowid;
Even though there is no table i1 this fails with the following error message:
there is already a table named i1
^^^^^
I would have looked forever for this table did I not know that i1 in fact conflicts with an index.
(11) By Larry Brasfield (LarryBrasfield) on 2021-01-30 15:03:47 in reply to 10 [link] [source]
I agree that this is very strange behavior, perhaps to be considered a bug.
With SQLite v3.33.0, I get a different error message from the same input:
there is already an index named i1
(12) By anonymous on 2021-01-30 15:11:20 in reply to 9 [link] [source]
This is an extremely valuable/powerful solution/hint/tip1. Thank you very much; I am much obliged.
On an off-chance, I have to ask: Is there anything in a view that can identify each row uniquely? (I know that this is highly unlikely since the underlying tables can change in real time; one workaround might be to 'freeze' the view by writing it to a temporary table).
PS: If my suggestion ever sees the light of day, it should include the hasRowID column.
1I know that I would not have figured this out, ever! Your solution puts the real value of this forum in true perspective.
(13.1) By Keith Medcalf (kmedcalf) on 2021-01-30 16:36:26 edited from 13.0 in reply to 9 [link] [source]
That is very interesting Richard -- I never thought of that -- though it is obvious.
I took a different tack and added additional columns to the table_list and table_info pragma's to extract more info from the schema and to rename the object types slightly:
sqlite> select * from pragma_table_list;
┌────────┬────────┬────────────────────┬───────┬────┬─────┬─────┬────┬────┬───────┬──────┐
│ schema │ type │ name │ ncols │ ro │ eph │ rid │ pk │ ai │ named │ shad │
├────────┼────────┼────────────────────┼───────┼────┼─────┼─────┼────┼────┼───────┼──────┤
│ main │ table │ sqlite_master │ 5 │ 1 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysForeignKeys │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysTriggers │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysIndexColumns │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysIndexes │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysColumns │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysTables │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysObjects │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysDatabases │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ table │ sqlite_temp_master │ 5 │ 1 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ view │ TZ_Lookup │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ view │ TZ_AllZones │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ vtable │ tz_geopoly │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ table │ sqlite_master │ 5 │ 1 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ table │ sqlite_stat1 │ 3 │ 0 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ table │ sqlite_stat4 │ 6 │ 0 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ table │ tz_geopoly_rowid │ 4 │ 0 │ 0 │ 1 │ 1 │ 0 │ 1 │ 0 │
│ _tz_ │ table │ tz_geopoly_node │ 2 │ 0 │ 0 │ 1 │ 1 │ 0 │ 1 │ 0 │
│ _tz_ │ table │ TZ_ZoneData │ 5 │ 0 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ table │ TZ_Version │ 2 │ 0 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ table │ tz_geopoly_parent │ 2 │ 0 │ 0 │ 1 │ 1 │ 0 │ 1 │ 0 │
│ _tz_ │ table │ TZ_Countries │ 2 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │ 0 │
│ _tz_ │ table │ TZ_Zones │ 6 │ 0 │ 0 │ 1 │ 1 │ 0 │ 1 │ 0 │
│ main │ etable │ pragma_table_list │ 11 │ 0 │ 0 │ 1 │ 0 │ 0 │ 0 │ 0 │
└────────┴────────┴────────────────────┴───────┴────┴─────┴─────┴────┴────┴───────┴──────┘
sqlite> pragma table_xinfo('tz_zones');
┌─────┬──────────────┬─────────┬─────────┬────────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │ type │ aff │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────────────┼─────────┼─────────┼────────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ 0 │ ID │ INTEGER │ INTEGER │ │ 0 │ │ 1 │ 1 │ 0 │ 0 │
│ 1 │ Country_Code │ text │ TEXT │ nocase │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 2 │ Area │ text │ TEXT │ nocase │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 3 │ Location │ text │ TEXT │ nocase │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 4 │ City │ text │ TEXT │ nocase │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 5 │ Zone │ text │ TEXT │ nocase │ 1 │ │ 0 │ 0 │ 0 │ 0 │
└─────┴──────────────┴─────────┴─────────┴────────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> pragma table_xinfo('tz_countries');
┌─────┬─────────┬──────┬──────┬────────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │ type │ aff │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼─────────┼──────┼──────┼────────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ 0 │ Code │ text │ TEXT │ nocase │ 1 │ │ 1 │ 0 │ 0 │ 0 │
│ 1 │ Country │ text │ TEXT │ nocase │ 1 │ │ 0 │ 0 │ 0 │ 0 │
└─────┴─────────┴──────┴──────┴────────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> pragma table_xinfo('sqlite_master');
┌─────┬──────────┬──────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │ type │ aff │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────────┼──────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1 │ │ │ INTEGER │ │ 0 │ │ 1 │ 1 │ 0 │ 1 │
│ 0 │ type │ text │ TEXT │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 1 │ name │ text │ TEXT │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 2 │ tbl_name │ text │ TEXT │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 3 │ rootpage │ int │ INTEGER │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
│ 4 │ sql │ text │ TEXT │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │
└─────┴──────────┴──────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
and added a database_info pragma that produced a similarly named output of all objects visible in the connections schema dictionary:
sqlite> pragma database_info;
┌────────┬────────┬─────────────────────────────────┐
│ schema │ type │ name │
├────────┼────────┼─────────────────────────────────┤
│ main │ table │ sqlite_master │
│ temp │ view │ SysForeignKeys │
│ temp │ view │ SysTriggers │
│ temp │ view │ SysIndexColumns │
│ temp │ view │ SysIndexes │
│ temp │ view │ SysColumns │
│ temp │ view │ SysTables │
│ temp │ view │ SysObjects │
│ temp │ view │ SysDatabases │
│ temp │ table │ sqlite_temp_master │
│ _tz_ │ view │ TZ_Lookup │
│ _tz_ │ view │ TZ_AllZones │
│ _tz_ │ vtable │ tz_geopoly │
│ _tz_ │ table │ sqlite_master │
│ _tz_ │ table │ sqlite_stat1 │
│ _tz_ │ table │ sqlite_stat4 │
│ _tz_ │ table │ tz_geopoly_rowid │
│ _tz_ │ table │ tz_geopoly_node │
│ _tz_ │ table │ TZ_ZoneData │
│ _tz_ │ table │ TZ_Version │
│ _tz_ │ table │ tz_geopoly_parent │
│ _tz_ │ table │ TZ_Countries │
│ _tz_ │ table │ TZ_Zones │
│ _tz_ │ index │ TZ_ZoneStart │
│ _tz_ │ index │ sqlite_autoindex_TZ_Countries_1 │
│ _tz_ │ index │ sqlite_autoindex_TZ_Countries_2 │
│ _tz_ │ index │ TZ_Country │
│ _tz_ │ index │ TZ_ZonesCountry │
│ _tz_ │ index │ TZ_ZonesArea │
│ _tz_ │ index │ TZ_ZonesLocation │
│ _tz_ │ index │ sqlite_autoindex_TZ_Zones_1 │
│ _tz_ │ index │ TZ_ZonesCity │
│ _tz_ │ index │ TZ_ZoneOffset │
│ _tz_ │ index │ TZ_ZoneDataID │
│ main │ etable │ pragma_table_list │
└────────┴────────┴─────────────────────────────────┘
"etable" equates to a eponymous virtual table module and I attribute it to "main" even though it exists quite independently of any schema name;
"vtable" is a virtual table which is merely a different attribute of a table (like a view) detected when one cursors through the tables in-memory schema;
"trigger" is also in the in-memory schema but since there is not one defined in this sample it does not show up.
I also added a rudimentary trigger_list pragma that obtains some information for triggers given schema.table. I haven't added a trigger_info pragma because I have not managed to figure out how to display interesting information about a specific trigger (such as for the ON conditions, for example).
This allows my "catalog views" to all work (across all attached schemas) without necessitating any dynamic execution (aka eval) capabilities to the codebase.
(14) By anonymous on 2021-01-30 17:33:52 in reply to 13.1 [link] [source]
Thanks for elaborating further, Keith.
Which version of SQLite3 are you using? I can't reproduce the same template when I run your queries: e.g. pragma table_xinfo('employees'); does not include the autoinc column with v3.34.1.
RE: select * from pragma_table_list;
What do the columns eph ,ai , named, shad mean/indicate?
I assume ro means read only and 1 indicates true and 0 indicates false; if so, why is it consistently 0 for 'view's?
Re: pragma table_xinfo('sqlite_master');
The column cid is the ordinal position of the column; if so, what does -1 indicate?
Re: pragma database_info;
Is _tz_ an attached database?
(17) By Keith Medcalf (kmedcalf) on 2021-01-30 18:44:28 in reply to 14 [link] [source]
Which version of SQLite3 are you using?
This is the current tip of trunk.
I can't reproduce the same template when I run your queries: e.g. pragma table_xinfo('employees'); does not include the autoinc column with v3.34.1
That is correct. These are changes I have made to the standard SQLite3 code to include extra attributes from the internal schema representation.
What do the columns eph ,ai , named, shad mean/indicate
eph is the ephemeral attribute from the internal dictionary
ai is the autoincrement attribute from the internal dictionary
named indicates that the tables rowid is explicitly named
shad is the shadow attribute from the internal dictionary
I assume ro means read only and 1 indicates true and 0 indicates false; if so, why is it consistently 0 for 'view's?
Because views are not read-only. You will note that sqlite_master is read-only because unless you use a specific pragma to enable you to write to it, that it is read only. This is an entirely different thing from a view or virtual table which is only by happenstance not writable.
Is _tz_ an attached database
Yes.
(18) By Keith Medcalf (kmedcalf) on 2021-01-30 18:48:40 in reply to 14 [link] [source]
Here is basically the same thing using my Catalog Views which expand the column names somewhat:
sqlite> select * from SysTables;
┌──────────────┬────────────┬────────────────────┬─────────┬────────────┬─────────────┬──────────┬───────────────┬─────────┬───────────────┬──────────────────┐
│ ObjectSchema │ ObjectType │ ObjectName │ NumCols │ isReadOnly │ isEphemeral │ isShadow │ hasPrimaryKey │ isRowid │ hasNamedRowid │ hasAutoIncrement │
├──────────────┼────────────┼────────────────────┼─────────┼────────────┼─────────────┼──────────┼───────────────┼─────────┼───────────────┼──────────────────┤
│ main │ table │ sqlite_master │ 5 │ 1 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ temp │ view │ SysForeignKeys │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysTriggers │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysIndexColumns │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysIndexes │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysColumns │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysTables │ 11 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysObjects │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ view │ SysDatabases │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ temp │ table │ sqlite_temp_master │ 5 │ 1 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ _tz_ │ view │ TZ_Lookup │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ view │ TZ_AllZones │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │ 0 │
│ _tz_ │ vtable │ tz_geopoly │ 0 │ 0 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ _tz_ │ table │ sqlite_master │ 5 │ 1 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ _tz_ │ table │ sqlite_stat1 │ 3 │ 0 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ _tz_ │ table │ sqlite_stat4 │ 6 │ 0 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ _tz_ │ table │ tz_geopoly_rowid │ 4 │ 0 │ 0 │ 0 │ 1 │ 1 │ 1 │ 0 │
│ _tz_ │ table │ tz_geopoly_node │ 2 │ 0 │ 0 │ 0 │ 1 │ 1 │ 1 │ 0 │
│ _tz_ │ table │ TZ_ZoneData │ 5 │ 0 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ _tz_ │ table │ TZ_Version │ 2 │ 0 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ _tz_ │ table │ tz_geopoly_parent │ 2 │ 0 │ 0 │ 0 │ 1 │ 1 │ 1 │ 0 │
│ _tz_ │ table │ TZ_Countries │ 2 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │ 0 │
│ _tz_ │ table │ TZ_Zones │ 6 │ 0 │ 0 │ 0 │ 1 │ 1 │ 1 │ 0 │
│ main │ etable │ pragma_table_list │ 11 │ 0 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ main │ etable │ sqlite_dbpage │ 3 │ 0 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
│ main │ etable │ sqlite_dbdata │ 5 │ 0 │ 0 │ 0 │ 0 │ 1 │ 0 │ 0 │
└──────────────┴────────────┴────────────────────┴─────────┴────────────┴─────────────┴──────────┴───────────────┴─────────┴───────────────┴──────────────────┘
(19.2) By Keith Medcalf (kmedcalf) on 2021-01-30 22:18:28 edited from 19.1 in reply to 14 [link] [source]
Missed one.
The column cid is the ordinal position of the column; if so, what does -1 indicate?
The cid -1 indicates the "hidden implicit rowid". This is added for completeness if the table has the "I am a ROWID table" indicator but not the "I have a named ROWID" indicator.
The aff (Affinity) column is also from the internal schema dictionary and does not depend on re-implementing the affinity deduction logic but simply reports what affinity is being used internally, which is an attribute stored in the internal schema information.
For more clarity the internal schema info has an item that indicates the cid of the iPKey. If that value is -1 (meaning there is no column that is the explicit iPKey) and the table is a rowid table, then a row for cid -1 representing the hidden iPKey is output if hidded columns are being output.
(15) By Larry Brasfield (LarryBrasfield) on 2021-01-30 17:50:55 in reply to 13.1 [link] [source]
Nice.
Have you signed the project's contributor's agreement? Those pragma enhancements would be a great optionally compiled feature.
Can you publish diffs somewhere?
(16) By Keith Medcalf (kmedcalf) on 2021-01-30 18:30:38 in reply to 15 [link] [source]
You can find context diffs for pragma.c and mkpragmatab.tcl here. The mkpragmatab.tcl diff was generated by fossil since the number on inconsequential changes is low. pragma.c.diff was created with a standard diff tool (not fossil) because there is a lot of "crud" (particularly invisible trailing spaces) that my editor removes from pragma.c when I edit it that results in a huge diff -- so I removed the "crud" from the trunk pragma.c to get rid of the crud before generating the diff so that it would only show the relevant changes without having to hand-edit out hundreds of irrelevant differences.
You need to apply the pramga.c.diff (which is against the current tip of trunk though the edits will apply to any version going back for many versions) to the src/pragma.c file.
You need to apply the mkpragmatab.tcl.diff (also against the current tip of trunk though the edits will apply to any version going back for many versions) to the tool/mkpragmatab.tcl file.
You then run the mkpragmatab.tcl script which will build the src/pragma.h
You can find the diffs here:
http://www.dessus.com/files/pragma.c.diff
http://www.dessus.com/files/mkpragmatab.tcl.diff
(20.1) By Keith Medcalf (kmedcalf) on 2021-01-30 19:14:09 edited from 20.0 in reply to 16 [link] [source]
I don't have a signed contributor agreement but these changes are pretty much "poached" from Richards already existing code -- in fact, the methods that are used to traverse the internal schema structures are all just "copies with minor modifications" of Richards code. I did this to get around some issues with my Catalog Schema views so that they are not dependent on any externalities and can handle multiple attached databases on the same connection without needing to examine and dynamically execute inspection of sqlite_master.
-- SysObjects view requires database_info pragma in SQLite3 3.31.0 and later
-- SysTriggers view requires trigger_list pragma in SQLite3 3.31.0 and later
-- SysTables view requires table_list pragma in SQLite3 3.31.0 and later
-- all TEXT columns in all views have "collate nocase" attached to the
-- output columns to ensure that where conditions on retrievals are not
-- case sensitive, Column Names in views defined so as to not conflict with
-- keywords to ensure quoting when using the views is not required
drop view if exists SysDatabases;
drop view if exists SysObjects;
drop view if exists SysTables;
drop view if exists SysColumns;
drop view if exists SysIndexes;
drop view if exists SysIndexColumns;
drop view if exists SysTriggers;
drop view if exists SysForeignKeys;
drop view if exists temp.SysDatabases;
drop view if exists temp.SysObjects;
drop view if exists temp.SysTables;
drop view if exists temp.SysColumns;
drop view if exists temp.SysIndexes;
drop view if exists temp.SysIndexColumns;
drop view if exists temp.SysTriggers;
drop view if exists temp.SysForeignKeys;
create view if not exists temp.SysDatabases
as
select Sequence,
ObjectSchema collate nocase,
FileName collate nocase
from (
select seq as Sequence,
name as ObjectSchema,
file as FileName
from temp.pragma_database_list
);
create view if not exists temp.SysObjects
as
select ObjectSchema collate nocase,
ObjectType collate nocase,
ObjectName collate nocase
from (
select schema as ObjectSchema,
type as ObjectType,
name as ObjectName
from temp.pragma_database_info
);
create view if not exists temp.SysTables
as
select ObjectSchema collate nocase,
ObjectType collate nocase,
ObjectName collate nocase,
NumCols,
isReadOnly,
isEphemeral,
isShadow,
hasPrimaryKey,
isRowid,
hasNamedRowid,
hasAutoIncrement
from (
select schema as ObjectSchema,
type as ObjectType,
name as ObjectName,
ncols as NumCols,
ro as isReadOnly,
eph as isEphemeral,
rid as isRowid,
pk as hasPrimaryKey,
ai as hasAutoIncrement,
named as hasNamedRowid,
shad as isShadow
from temp.pragma_table_list
);
create view if not exists temp.SysColumns
as
select ObjectSchema collate nocase,
ObjectType collate nocase,
ObjectName collate nocase,
ColumnID collate nocase,
ColumnName collate nocase,
Type collate nocase,
Affinity collate nocase,
Collation collate nocase,
isNotNull,
DefaultValue,
isPrimaryKey,
isRowID,
isAutoIncrement,
isHidden
from (
select ObjectSchema,
ObjectType,
ObjectName,
cid as ColumnID,
name as ColumnName,
type as Type,
aff as Affinity,
coll as Collation,
"notnull" as isNotNull,
dflt_value as DefaultValue,
pk as isPrimaryKey,
rowid as isRowID,
autoinc as isAutoIncrement,
Hidden as isHidden
from temp.SysObjects
cross join temp.pragma_table_xinfo
on schema = ObjectSchema
and arg = ObjectName
where ObjectType in ('vtable', 'etable', 'table', 'view')
);
create view if not exists temp.SysIndexes
as
select ObjectSchema collate nocase,
ObjectType collate nocase,
ObjectName collate nocase,
IndexName collate nocase,
IndexID,
isUniqueIndex,
IndexOrigin collate nocase,
isPartialIndex
from (
select ObjectSchema,
ObjectType,
ObjectName,
name as IndexName,
seq as IndexID,
"unique" as isUniqueIndex,
origin as IndexOrigin,
partial as isPartialIndex
from temp.SysObjects
cross join temp.pragma_index_list
on schema = ObjectSchema
and arg = ObjectName
where ObjectType in ('vtable', 'etable', 'table')
);
create view if not exists temp.SysIndexColumns
as
select ObjectSchema collate nocase,
ObjectType collate nocase,
ObjectName collate nocase,
IndexName collate nocase,
IndexColumnSequence,
ColumnID,
ColumnName collate nocase,
isDescendingOrder,
Collation collate nocase,
isPartOfKey
from (
select ObjectSchema,
ObjectType,
ObjectName,
IndexName,
seqno as IndexColumnSequence,
cid as ColumnID,
name as ColumnName,
"desc" as isDescendingOrder,
coll as Collation,
key as isPartOfKey
from SysIndexes
cross join temp.pragma_index_xinfo
on schema = ObjectSchema
and arg = IndexName
);
create view if not exists temp.SysTriggers
as
select ObjectSchema collate nocase,
ObjectType collate nocase,
ObjectName collate nocase,
Sequence,
TriggerName collate nocase,
TriggerWhen collate nocase,
TriggerOp collate nocase
from (
select ObjectSchema,
ObjectType,
ObjectName,
seq as Sequence,
name as TriggerName,
tm_tr as TriggerWhen,
op as TriggerOp
from temp.SysObjects
cross join temp.pragma_trigger_list
on arg == ObjectName
and schema == ObjectSchema
where ObjectType in ('vtable', 'etable', 'table')
);
create view if not exists temp.SysForeignKeys
as
select ObjectSchema collate nocase,
ObjectType collate nocase,
ObjectName collate nocase,
Id,
Sequence,
FromColumn collate nocase,
ToTable collate nocase,
ToColumn collate nocase,
OnUpdate collate nocase,
OnDelete collate nocase,
Match collate nocase
from (
select ObjectSchema,
ObjectType,
ObjectName,
id as Id,
seq as Sequence,
"from" as FromColumn,
"table" as ToTable,
"to" as ToColumn,
on_update as OnUpdate,
on_delete as OnDelete,
match as Match
from temp.SysObjects
cross join temp.pragma_foreign_key_list
on arg == ObjectName
and schema == ObjectSchema
where ObjectType in ('vtable', 'etable', 'table')
);
These are basically the same views as I have posted previously but the primary change was adding the database_info pragma so that there is no need to examine the various sqlite_master tables to find all the objects and the additional columns so that more information can be obtained.
NB: I just added a .read into .sqliterc so that these views are always loaded when I invoke the CLI
(23.1) By Larry Brasfield (LarryBrasfield) on 2021-01-31 02:00:22 edited from 23.0 in reply to 20.1 [link] [source]
Thanks for that. After some early hiccups arising from pulling a slightly incoherent trunk tip file set, that built without a hitch and appears to work as well as your previous "Catalog" does (or better.)
I am tempted to create the views in an in-memory database known as 'information_schema', not because that is (or is becoming) conventional but because it segregates the view names from application table/view names. If there was a "DETACH IF ATTACHED Whatever_Schema" feature in SQLite, I would suggest that. It would be doable if there was a way to say "ATTACH ':memory:' as information_schema" only if
SELECT COUNT(name) as ise FROM pragma_database_list
WHERE name = 'information_schema'
returns 0. Is there a clean way to do that?
(Appended by edit:)
If those views are not in the temp schema, but in information_schema, this happens:
sqlite> select * from information_schema.SysTables;
Error: unsafe use of virtual table "pragma_table_list"
Rats.
(24) By Keith Medcalf (kmedcalf) on 2021-01-31 02:32:25 in reply to 23.1 [link] [source]
The pragma vtabs are not INNOCUOUS so they can only be used from top level statements (or from temp).
Do you have trusted_schema off perchance? (.dbconfig)
(25) By Larry Brasfield (LarryBrasfield) on 2021-01-31 02:48:49 in reply to 24 [link] [source]
Yes, I have trusted_schema left at its default. (off)
Is there any reason that the schema-revealing table-valued pragma functions should not be marked as INNOCUOUS? They certainly are not writable tables. Nor do they have side-effects. How dangerous can they be?
(26.1) By Keith Medcalf (kmedcalf) on 2021-01-31 03:44:43 edited from 26.0 in reply to 25 [link] [source]
It is an information leakage exposure, is my understanding.
Also, open a big database and execute select * from pragma_integrity_check.
Do you want someone to give you a database that has a view that selects from pragma_integrity_check? Not setting the INNOCUOUS flag means that statements which execute pragmas must originate from your application (or have been put in the per connection temp table by your application) and cannot be put there by an external attacker for you to execute unbeknownst to you.
(27) By Larry Brasfield (LarryBrasfield) on 2021-01-31 15:22:16 in reply to 26.1 [link] [source]
Hmmm. The information leakage exposure seems little different from what those schema-revealing pragma's can do when ensconced in views in the temp schema.
I do not deny the utility of the (un)trusted_schema scheme. I was thinking that the table-valued pragmas are innocuous and hence could be harmlessly attributed as such in the code which makes them visible as tables. IOW, no security risk is created beyond what the same views in temp entail (which is some schema leakage.)
(28) By Keith Medcalf (kmedcalf) on 2021-01-31 18:02:22 in reply to 27 [link] [source]
I had asked Richard about this back when the innocuous flags were added and that was the reason he gave me (I used to just put all the catalog views in each database itself) and that was the answer he gave me.
I suppose you could go either way -- however -- at least if they are in the temp database you know that only you could have put them there ... subsequent to opening the database connection and that they cannot have been externally manipulated.
That is why they load into temp (and drop any equivalents that exist before they are added). The CLI can load them just fine using the .sqliterc and for other applications they can be handcoded in the application to be loaded (for example I modified APSW (by overriding the connection class) so that they are loaded into temp when each connection is opened).
(21) By doug (doug9forester) on 2021-01-30 20:52:06 in reply to 9 [link] [source]
I tried it and got this:
sqlite> select sqlite_version();
sqlite_version()
3.31.1
sqlite> SELECT name,
...> NOT EXISTS(SELECT 1 FROM pragma_index_info(x.name)) AS 'hasRowid'
...> FROM sqlite_schema AS x
...> WHERE type='table'
...> ORDER BY name;
Error: no such table: sqlite_schema
Is it version dependent?
(22) By Keith Medcalf (kmedcalf) on 2021-01-30 22:35:29 in reply to 21 [link] [source]
Yes.
sqlite_schema became as alias for sqlite_master in June 2020. (Version 3.32.3 or 3.33.0 and later) though it was not noted in the release notes.