Full Schema Tables:

-- 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" 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 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
select ObjectType collate nocase,
       ObjectName collate nocase
  from (
        select type as ObjectType,
               name as ObjectName
          from sqlite_master
         where type in ('table', 'view', 'index')
       );

create view if not exists SysColumns
as
select ObjectType collate nocase,
       ObjectName collate nocase,
       ColumnID collate nocase,
       ColumnName collate nocase,
       Affinity collate nocase,
       IsNotNull,
       DefaultValue,
       IsPrimaryKey
from (
        select ObjectType,
               ObjectName,
               cid        as ColumnID,
               name       as ColumnName,
               type       as Affinity,
               "notnull"  as IsNotNull,
               dflt_value as DefaultValue,
               pk         as IsPrimaryKey
          from SysObjects
          join pragma_table_info(ObjectName)
     );

create view if not exists SysIndexes
as
select ObjectType collate nocase,
       ObjectName collate nocase,
       IndexName collate nocase,
       IndexID,
       IsUniqueIndex collate nocase,
       IndexOrigin collate nocase,
       IsPartialIndex
  from (
        select ObjectType,
               ObjectName,
               name     as IndexName,
               seq      as IndexID,
               "unique" as IsUniqueIndex,
               origin   as IndexOrigin,
               partial  as IsPartialIndex
          from SysObjects
          join pragma_index_list(ObjectName)
       );

create view if not exists SysIndexColumns
as
select ObjectType collate nocase,
       ObjectName collate nocase,
       IndexName collate nocase,
       IndexColumnSequence,
       ColumnID,
       ColumnName collate nocase,
       IsDescendingOrder,
       Collation collate nocase,
       IsPartOfKey
  from (
        select ObjectType,
               ObjectName,
               IndexName,
               seqno  as IndexColumnSequence,
               cid    as ColumnID,
               name   as ColumnName,
               "desc" as IsDescendingOrder,
               coll   as Collation,
               key    as IsPartOfKey
          from SysIndexes
          join pragma_index_xinfo(IndexName)
       );




---
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: Keith Medcalf [mailto:kmedc...@dessus.com]
>Sent: Saturday, 6 January, 2018 12:40
>To: 'SQLite mailing list'
>Subject: RE: [sqlite] sqlite3_column_decltype and max and min
>
>
>SQLite version 3.22.0 2018-01-02 18:11:11
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .head on
>sqlite> .mode col
>sqlite> create table x(a int_date);
>
>sqlite> pragma table_info(x);
>cid         name        type        notnull     dflt_value  pk
>----------  ----------  ----------  ----------  ----------  ---------
>-
>0           a           int_date    0                       0
>
>create view if not exists SysColumns
>as
>select ObjectType collate nocase,
>       ObjectName collate nocase,
>       ColumnID collate nocase,
>       ColumnName collate nocase,
>       Affinity collate nocase,
>       IsNotNull,
>       DefaultValue,
>       IsPrimaryKey
>from (
>        select ObjectType,
>               ObjectName,
>               cid        as ColumnID,
>               name       as ColumnName,
>               type       as Affinity,
>               "notnull"  as IsNotNull,
>               dflt_value as DefaultValue,
>               pk         as IsPrimaryKey
>          from SysObjects
>          join pragma_table_info(ObjectName)
>     );
>
>sqlite> select * from SysColumns where ObjectType = 'table' and
>ObjectName = 'x';
>ObjectType  ObjectName  ColumnID    ColumnName  Affinity    IsNotNull
>DefaultValue  IsPrimaryKey
>----------  ----------  ----------  ----------  ----------  ---------
>-  ------------  ------------
>table       x           0           a           int_date    0
>0
>
>
>
>---
>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-
>>boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert
>>Sent: Saturday, 6 January, 2018 10:45
>>To: General Discussion of SQLite Database
>>Subject: [sqlite] sqlite3_column_decltype and max and min
>>
>>Is there any way with sqlite3_column_decltype (or otherwise) to get
>>the
>>declared data type (as in the table create sql in SQLite_master)
>when
>>it is
>>a simple expression such as max and min?
>>
>>for example we have a table created like this:
>>
>>create table1([integer_date] int_date)
>>
>>and we do:
>>
>>select max(integer_date) from table1
>>
>>I would then like to get returned int_date, rather than integer.
>>
>>The custom datatype int_date is needed for formatting purpose to
>tell
>>the
>>app that receives
>>the data that the column holds dates as integers.
>>
>>To do this in application code is not that simple, so I hope there
>is
>>some
>>simpler way.
>>
>>
>>RBS
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to