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:[email protected]]
>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-
>>[email protected]] 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
>>[email protected]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
