New Information Schema Views

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

New Information Schema Views

Keith Medcalf

This is an update to the Schema Information views that I previously posted.  This version has the capability to display information for all available schema names (attached databases) simultaneously.  It requires that the SQL function "eval" be available since it runs dynamically generated SQL and I could not figure out a better way to collect information from all the attached database sqlite_master tables simultaneously.

Particularly the SysObjects view is the one that does this.  Any improvements or suggestions welcome!

Note that if you have embeded ';' in your identifier names you are buggered as the parsing of the eval() output uses that as a column separator ...


-- Catalog Views using sqlite_master for SysObjects (Object Names)
-- and the various pragma_<infotype>(ObjectName) tables to retrieve schema data
-- all TEXT columns in 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 views is not required

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
  as (select group_concat('select ''' || name || ''' as schema, type, name from ' || name || '.sqlite_master', ' union ') as sql
        from pragma_database_list),
     table2
  as (select eval(sql, ';') || ';' as s from table1),
     table3 (t, token, remainder)
  as (select 0, '', s
        from table2
       union
      select t+1,
             substr(remainder, 1, instr(remainder, ';') - 1),
             substr(remainder, instr(remainder, ';') + 1)
        from table3
       where length(remainder) > 0),
     table4 (r, c, token)
  as (select (t - 1) / 3,
             (t - 1) % 3,
             token
        from table3
       where t > 0)
select (select token from table4 where r = row and c = 0) as ObjectSchema,
       (select token from table4 where r = row and c = 1) as ObjectType,
       (select token from table4 where r = row and c = 2) as ObjectName
  from (select distinct r as row from table4);

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: New Information Schema Views

Edward Lau
Hi Keith:
If there is a standard naming convention out there, we should consider adopting it. How about the naming listed in the link below:https://www.postgresql.org/docs/9.3/information-schema.html
https://www.cmi.ac.in/~madhavan/courses/databases10/mysql-5.0-reference-manual/information-schema.html

Regards

-----Original Message-----
From: Keith Medcalf <[hidden email]>
To: SQLite Users ([hidden email]) <[hidden email]>
Sent: Thu, Jun 6, 2019 1:55 pm
Subject: [sqlite] New Information Schema Views


This is an update to the Schema Information views that I previously posted.  This version has the capability to display information for all available schema names (attached databases) simultaneously.  It requires that the SQL function "eval" be available since it runs dynamically generated SQL and I could not figure out a better way to collect information from all the attached database sqlite_master tables simultaneously.

Particularly the SysObjects view is the one that does this.  Any improvements or suggestions welcome!

Note that if you have embeded ';' in your identifier names you are buggered as the parsing of the eval() output uses that as a column separator ...


-- Catalog Views using sqlite_master for SysObjects (Object Names)
-- and the various pragma_<infotype>(ObjectName) tables to retrieve schema data
-- all TEXT columns in 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 views is not required

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
  as (select group_concat('select ''' || name || ''' as schema, type, name from ' || name || '.sqlite_master', ' union ') as sql
        from pragma_database_list),
    table2
  as (select eval(sql, ';') || ';' as s from table1),
    table3 (t, token, remainder)
  as (select 0, '', s
        from table2
      union
      select t+1,
            substr(remainder, 1, instr(remainder, ';') - 1),
            substr(remainder, instr(remainder, ';') + 1)
        from table3
      where length(remainder) > 0),
    table4 (r, c, token)
  as (select (t - 1) / 3,
            (t - 1) % 3,
            token
        from table3
      where t > 0)
select (select token from table4 where r = row and c = 0) as ObjectSchema,
      (select token from table4 where r = row and c = 1) as ObjectType,
      (select token from table4 where r = row and c = 2) as ObjectName
  from (select distinct r as row from table4);

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: New Information Schema Views

Keith Medcalf
In reply to this post by Keith Medcalf

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: New Information Schema Views

James K. Lowden
On Fri, 07 Jun 2019 01:43:18 -0600
"Keith Medcalf" <[hidden email]> wrote:

> -- 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 (;)

I would like to play Omniscient Narrator for a moment and mention
to our listening radio audience that this is not the first substantial
contribution by the inimitable Mr. Medcalf.  The other one that I
remember was a complete set of functions for math.h.  I'm sure there
are others.  

For users coming to SQLite and knowledgable about SQL standards,
INFORMATION_SCHEMA is the obvious place to look for schema metadata,
much easier to query and remember than a variety of functions and
pragmas and whatnot.  It's not beautiful, but then very little about
SQL is beautiful.  What's important is that is works and is
understandable and standard.  

Thanks, Keith.  You just saved a lot of people a lot of work.  

--jkl




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users