Improved version that does not execute the eval multiple times. I have not
renamed the columns match the ANSI INFORMATION_SCHEMA views, though I suppose
that might be possible (though I find that schema yucky -- it embodies the
thinking from decades ago where one would embed table names into column names
because extremely primitive RDBMS systems required all "identifiers" to be
unique notwithstanding context).
Now if only there were a pragma to collect the sqlite_master data across all
attached databases, there would be no need for the eval()
-- Catalog Views using sqlite_master for SysObjects (Object Names)
-- requires that the eval() extension function be loaded and available
-- all TEXT columns in views have "collate nocase" attachmented 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
-- table/view/index name MUST NOT contain embeded semicolons (;)
drop view if exists SysIndexColumns;
drop view if exists SysIndexes;
drop view if exists SysColumns;
drop view if exists SysObjects;
create view if not exists SysObjects
as
with table1 (sql)
as (
select group_concat('select ''' || name || ''' as schema,
type, name from ' || name || '.sqlite_master', ' union ') as sql
from pragma_database_list
),
table2 (t, r, ObjectSchema, ObjectType, ObjectName, remainder)
as (
select 0, null, null, null, null, eval(sql, ';') || ';'
from table1
union all
select t + 1,
t / 3,
case when t % 3 == 0 then substr(remainder, 1,
instr(remainder, ';') - 1) else null end,
case when t % 3 == 1 then substr(remainder, 1,
instr(remainder, ';') - 1) else null end,
case when t % 3 == 2 then substr(remainder, 1,
instr(remainder, ';') - 1) else null end,
substr(remainder, instr(remainder, ';') + 1)
from table2
where length(remainder) > 0
)
select max(ObjectSchema) collate nocase as ObjectSchema,
max(ObjectType) collate nocase as ObjectType,
max(ObjectName) collate nocase as ObjectName
from table2
where t > 0
group by r;
create view if not exists SysColumns
as
select ObjectSchema collate nocase,
ObjectType collate nocase,
ObjectName collate nocase,
ColumnID collate nocase,
ColumnName collate nocase,
Type collate nocase,
Affinity collate nocase,
isNotNull,
DefaultValue,
isPrimaryKey,
isHidden
from (
select ObjectSchema,
ObjectType,
ObjectName,
cid as ColumnID,
name as ColumnName,
type as Type,
--- Affinity Rules from https://www.sqlite.org/datatype3.html
Section 3.1
case when trim(type) = '' then 'Blob'
when instr(UPPER(type), 'INT') > 0 then 'Integer'
when instr(UPPER(type), 'CLOB') > 0 then 'Text'
when instr(UPPER(type), 'CHAR') > 0 then 'Text'
when instr(UPPER(type), 'TEXT') > 0 then 'Text'
when instr(UPPER(type), 'BLOB') > 0 then 'Blob'
when instr(UPPER(type), 'REAL') > 0 then 'Real'
when instr(UPPER(type), 'FLOA') > 0 then 'Real'
when instr(UPPER(type), 'DOUB') > 0 then 'Real'
else 'Numeric'
end as Affinity,
"notnull" as isNotNull,
dflt_value as DefaultValue,
pk as isPrimaryKey,
Hidden as isHidden
from SysObjects
cross join pragma_table_xinfo
where arg = ObjectName
and schema = ObjectSchema
and ObjectType in ('table', 'view')
);
create view if not exists 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 SysObjects
cross join pragma_index_list
where arg = ObjectName
and schema = ObjectSchema
and ObjectType = 'table'
);
create view if not exists 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 pragma_index_xinfo
where arg = IndexName
and schema = ObjectSchema
);
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
