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