Keith
Looks like a good idea but I get :

sqlite> 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)
   ...>      );
Error: near "(": syntax error

Is this because I am stuck with version 3.8.8.3 which is what MacOS Sierra
provides?

I see you were using 3.22.

John Gillespie

On 6 January 2018 at 20:02, Keith Medcalf <kmedc...@dessus.com> wrote:

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

Reply via email to