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