Re: [sqlite] sqlite3_column_decltype and max and min

[Craig H Maynard]

Igor,

Two comments:

- Why Homebrew? Because it's an invaluable tool for keeping up with the latest 
release of not just SQLite but every installed package of interest.

- I think you missed the point. Niall is referring to the SQLite3 executable, 
not just the C source file. The excutable is needed to use SQLite from the 
command line.

Craig

--
Craig H Maynard
Rhode Island, USA


> On Sat, 13 Jan 2018, Igor Korot  wrote:
> 
> Why? Just include sqlite3.c in your project and recompile.
> 
> 
> On Sat, Jan 13, 2018 at 6:32 AM, Niall O'Reilly  wrote:
> 
>> It may be more convenient to use the [Homebrew package 
>> manager](https://brew.sh/), which tracks SQLite pretty closely.  In order to 
>> avoid interfering with the Apple-supplied installation, Homebrew 
>> deliberately neglects to link the executable to _/usr/local/bin_, so some 
>> care is needed when invoking SQLite in order to run the intended version.



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

Re: [sqlite] sqlite3_column_decltype and max and min

[Igor Korot]

Hi,

On Sat, Jan 13, 2018 at 6:32 AM, Niall O'Reilly  wrote:
>
>
> On 11 Jan 2018, at 13:23, Richard Hipp wrote, in reply to John G 
> :
>
>> You can
>> download and/or compile your own up-to-date SQLite that is twice as
>> fast and has all the latest features.
>
> It may be more convenient to use the [Homebrew package 
> manager](https://brew.sh/), which tracks SQLite pretty closely.  In order to 
> avoid interfering with the Apple-supplied installation, Homebrew deliberately 
> neglects to link the executable to _/usr/local/bin_, so some care is needed 
> when invoking SQLite in order to run the intended version.
>
> Here's what I have on my laptop, running Sierra.
>
> dhcp-162(niall)12: brew install sqlite
> Updating Homebrew...
> Warning: sqlite 3.21.0 is already installed
> dhcp-162(niall)13:
> dhcp-162(niall)13: which sqlite3
> /usr/bin/sqlite3
> dhcp-162(niall)14: /usr/bin/sqlite3
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> ^D
> dhcp-162(niall)15: /usr/local/Cellar/sqlite/3.21.0/bin/sqlite3
> SQLite version 3.21.0 2017-10-24 18:55:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite>
> dhcp-162(niall)16:
>
> I hope this helps.

Why?
Just include sqlite3.c in your project and recompile.

Thank you.

>
> Best regards,
> Niall O'Reilly
>
> ___
> 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

Re: [sqlite] sqlite3_column_decltype and max and min

[Niall O'Reilly]

On 11 Jan 2018, at 13:23, Richard Hipp wrote, in reply to John G 
:

> You can
> download and/or compile your own up-to-date SQLite that is twice as
> fast and has all the latest features.

It may be more convenient to use the [Homebrew package 
manager](https://brew.sh/), which tracks SQLite pretty closely.  In order to 
avoid interfering with the Apple-supplied installation, Homebrew deliberately 
neglects to link the executable to _/usr/local/bin_, so some care is needed 
when invoking SQLite in order to run the intended version.

Here's what I have on my laptop, running Sierra.

dhcp-162(niall)12: brew install sqlite
Updating Homebrew...
Warning: sqlite 3.21.0 is already installed
dhcp-162(niall)13:
dhcp-162(niall)13: which sqlite3
/usr/bin/sqlite3
dhcp-162(niall)14: /usr/bin/sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ^D
dhcp-162(niall)15: /usr/local/Cellar/sqlite/3.21.0/bin/sqlite3
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
dhcp-162(niall)16:

I hope this helps.

Best regards,
Niall O'Reilly


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_decltype and max and min

[John G]

Thanks Warren. Sorry about that, I had an old version in /opt/local/bin.

John Gillespie

On 11 January 2018 at 15:24, Warren Young  wrote:

> On Jan 11, 2018, at 5:47 AM, John G  wrote:
> >
> > Is this because I am stuck with version 3.8.8.3 which is what MacOS
> Sierra
> > provides?
>
> I have sqlite3 version 3.16.0 in /usr/bin on this 10.12.6 (Sierra) system.
> ___
> 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

Re: [sqlite] sqlite3_column_decltype and max and min

[Warren Young]

On Jan 11, 2018, at 5:47 AM, John G  wrote:
> 
> Is this because I am stuck with version 3.8.8.3 which is what MacOS Sierra
> provides?

I have sqlite3 version 3.16.0 in /usr/bin on this 10.12.6 (Sierra) system.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_decltype and max and min

[Richard Hipp]

On 1/11/18, John G  wrote:
>
> Is this because I am stuck with version 3.8.8.3 which is what MacOS Sierra
> provides?
>

You are not stuck with the software provided by Sierra.  You can
download and/or compile your own up-to-date SQLite that is twice as
fast and has all the latest features.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_decltype and max and min

[John G]

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,
   ...>cidas 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_(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,
>cidas 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,
>cidas 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

Re: [sqlite] sqlite3_column_decltype and max and min

[Dinu]

Keith Medcalf wrote
> Full Schema Tables:

Thanks for this, I will have grat use for it too, soon! Actually it's so
nice I think it could go into the documentation.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_decltype and max and min

[Bart Smissaert]

OK, thanks.
I think I will code something along similar lines, so that I have the
declared column type (the custom types, eg int_date) always easily
available.
Of course with casting and UDF's etc. it will get a bit tricky.

RBS



On Sat, Jan 6, 2018 at 11:26 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> This is how pySqlite2 (aka sqlite3) wrapper in Python achieves such data
> manipulations, by tagging the column name specially (since by default rows
> are only retrieved and accessible as positional locations in a row tuple.
> If you open the database and tell it to "parse column types" then it will
> retrieve the column name from the cursor description then split the column
> name at the space.  The righthand token will be used to lookup a
> "converter" function in a dictionary of "converters", and will then apply
> the converter function to the retrieved data for that column AFTER it is
> retrieved from SQLite3 and BEFORE it is returned to the user.
>
> That means that you could write a function called
> "JulianToDatetime(number)" and write your SQL Query (with column name
> parsing enabled) as
>
> select column1 as [column1 JulianDateTime] from table;
>
> and add a converter entry {"JulianDateTime": JulianToDateTime} and the
> field would automagically and transparently be "converted" with the
> JulianToDateTime function before it is returned to the user program.  For
> data going the other way, you can write an "adapter" function that converts
> the Python "datetime" type into a JulianDay number that occurs
> automagically between you passing the data binding to the wrapper, and the
> wrapper passing the data into SQLite3.
>
> pySqlite2 (now sqlite3) Python wrapper can also parse the table declared
> type in the same way to apply automatic coverters on data retrieval (I do
> not know how it gets the declared type -- I'd have to go look in the source
> for pySqlite2.  However, once a returned column is an aggregate or an
> expression, it likely will not carry the origin declaration type anymore
> and only overloading the datatype into the column name (via as) will work.
>
> ---
> 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 15:54
> >To: SQLite mailing list
> >Subject: Re: [sqlite] sqlite3_column_decltype and max and min
> >
> >OK, thanks will have a look at that.
> >So, how about this one then:
> >
> >select max(integer_date) as [max_integer_date int_date] from table1
> >
> >?
> >
> >RBS
> >
> >On Sat, Jan 6, 2018 at 10:35 PM, Keith Medcalf <kmedc...@dessus.com>
> >wrote:
> >
> >>
> >> Yes.  You can use "pragma table_info(tablename)" to get information
> >about
> >> a table, the whole table, and nothing but the one table.  Or you
> >can use
> >> the bunch-o-views which will give you a data dictionary to be able
> >to get
> >> the same data by treating all the various introspection pragma's as
> >tables
> >> and letting you query for specific information such as:
> >>
> >> select affinity from SysColumns where ObjectType='table' and
> >ObjectName='myTableThatIAMInterestedIn'
> >> and
> >ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';
> >>
> >> or perhaps even
> >>
> >> select ObjectType, ObjectName, from SysColumns where ColumnName = '
> >> GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';
> >>
> >>
> >> One is simply more selective that the other.  The pragma's use
> >> ill-conceived field names (containing spaces and other kaiboshery)
> >whereas
> >> the views rename such ill-conception to something not quite so ill
> >such
> >> that "escaping" is not required (which is as God intended when she
> >invented
> >> the concept of variable naming and so forth).
> >>
> >> ---
> >> 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 15:00
> >> >To: SQLite mailing list
> >> &

Re: [sqlite] sqlite3_column_decltype and max and min

[Keith Medcalf]

This is how pySqlite2 (aka sqlite3) wrapper in Python achieves such data 
manipulations, by tagging the column name specially (since by default rows are 
only retrieved and accessible as positional locations in a row tuple.  If you 
open the database and tell it to "parse column types" then it will retrieve the 
column name from the cursor description then split the column name at the 
space.  The righthand token will be used to lookup a "converter" function in a 
dictionary of "converters", and will then apply the converter function to the 
retrieved data for that column AFTER it is retrieved from SQLite3 and BEFORE it 
is returned to the user.

That means that you could write a function called "JulianToDatetime(number)" 
and write your SQL Query (with column name parsing enabled) as

select column1 as [column1 JulianDateTime] from table;

and add a converter entry {"JulianDateTime": JulianToDateTime} and the field 
would automagically and transparently be "converted" with the JulianToDateTime 
function before it is returned to the user program.  For data going the other 
way, you can write an "adapter" function that converts the Python "datetime" 
type into a JulianDay number that occurs automagically between you passing the 
data binding to the wrapper, and the wrapper passing the data into SQLite3.

pySqlite2 (now sqlite3) Python wrapper can also parse the table declared type 
in the same way to apply automatic coverters on data retrieval (I do not know 
how it gets the declared type -- I'd have to go look in the source for 
pySqlite2.  However, once a returned column is an aggregate or an expression, 
it likely will not carry the origin declaration type anymore and only 
overloading the datatype into the column name (via as) will work.

---
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 15:54
>To: SQLite mailing list
>Subject: Re: [sqlite] sqlite3_column_decltype and max and min
>
>OK, thanks will have a look at that.
>So, how about this one then:
>
>select max(integer_date) as [max_integer_date int_date] from table1
>
>?
>
>RBS
>
>On Sat, Jan 6, 2018 at 10:35 PM, Keith Medcalf <kmedc...@dessus.com>
>wrote:
>
>>
>> Yes.  You can use "pragma table_info(tablename)" to get information
>about
>> a table, the whole table, and nothing but the one table.  Or you
>can use
>> the bunch-o-views which will give you a data dictionary to be able
>to get
>> the same data by treating all the various introspection pragma's as
>tables
>> and letting you query for specific information such as:
>>
>> select affinity from SysColumns where ObjectType='table' and
>ObjectName='myTableThatIAMInterestedIn'
>> and
>ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';
>>
>> or perhaps even
>>
>> select ObjectType, ObjectName, from SysColumns where ColumnName = '
>> GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';
>>
>>
>> One is simply more selective that the other.  The pragma's use
>> ill-conceived field names (containing spaces and other kaiboshery)
>whereas
>> the views rename such ill-conception to something not quite so ill
>such
>> that "escaping" is not required (which is as God intended when she
>invented
>> the concept of variable naming and so forth).
>>
>> ---
>> 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 15:00
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] sqlite3_column_decltype and max and min
>> >
>> >Not sure how this works.
>> >I had a look at your views.
>> >Are you saying that I should get the data type (only if
>> >sqlite3_column_decltype produces null) by querying these views,
>> >passing it
>> >the column name?
>> >
>> >RBS
>> >
>> >
>> >On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf
><kmedc...@dessus.com>
>> >wrote:
>> >
>> >>
>> >> select max(integer_date) as [max_integer_date int_date] from
>table1
>> >>
>> >>
>> >> fetch the cursor.description and split the column name at the
&g

Re: [sqlite] sqlite3_column_decltype and max and min

[Bart Smissaert]

OK, thanks will have a look at that.
So, how about this one then:

select max(integer_date) as [max_integer_date int_date] from table1

?

RBS

On Sat, Jan 6, 2018 at 10:35 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Yes.  You can use "pragma table_info(tablename)" to get information about
> a table, the whole table, and nothing but the one table.  Or you can use
> the bunch-o-views which will give you a data dictionary to be able to get
> the same data by treating all the various introspection pragma's as tables
> and letting you query for specific information such as:
>
> select affinity from SysColumns where ObjectType='table' and 
> ObjectName='myTableThatIAMInterestedIn'
> and ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';
>
> or perhaps even
>
> select ObjectType, ObjectName, from SysColumns where ColumnName = '
> GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';
>
>
> One is simply more selective that the other.  The pragma's use
> ill-conceived field names (containing spaces and other kaiboshery) whereas
> the views rename such ill-conception to something not quite so ill such
> that "escaping" is not required (which is as God intended when she invented
> the concept of variable naming and so forth).
>
> ---
> 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 15:00
> >To: SQLite mailing list
> >Subject: Re: [sqlite] sqlite3_column_decltype and max and min
> >
> >Not sure how this works.
> >I had a look at your views.
> >Are you saying that I should get the data type (only if
> >sqlite3_column_decltype produces null) by querying these views,
> >passing it
> >the column name?
> >
> >RBS
> >
> >
> >On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf <kmedc...@dessus.com>
> >wrote:
> >
> >>
> >> select max(integer_date) as [max_integer_date int_date] from table1
> >>
> >>
> >> fetch the cursor.description and split the column name at the
> >space.  The
> >> right part is the datatype for your handy dandy use.
> >>
> >> You may also be able to use the same method in the table
> >declaration,
> >> provided that the type carries through, but I do not remember
> >offhand.
> >>
> >> create table1([integer_date] [integer int_date])
> >>
> >> The former is how pySqlite2 links adapters and converters to the
> >query.
> >>
> >> ---
> >> 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
>
>
>
> ___
> 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

Re: [sqlite] sqlite3_column_decltype and max and min

[Keith Medcalf]

Yes.  You can use "pragma table_info(tablename)" to get information about a 
table, the whole table, and nothing but the one table.  Or you can use the 
bunch-o-views which will give you a data dictionary to be able to get the same 
data by treating all the various introspection pragma's as tables and letting 
you query for specific information such as:

select affinity from SysColumns where ObjectType='table' and 
ObjectName='myTableThatIAMInterestedIn' and 
ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';

or perhaps even

select ObjectType, ObjectName, from SysColumns where ColumnName = 
'GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';


One is simply more selective that the other.  The pragma's use ill-conceived 
field names (containing spaces and other kaiboshery) whereas the views rename 
such ill-conception to something not quite so ill such that "escaping" is not 
required (which is as God intended when she invented the concept of variable 
naming and so forth).

---
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 15:00
>To: SQLite mailing list
>Subject: Re: [sqlite] sqlite3_column_decltype and max and min
>
>Not sure how this works.
>I had a look at your views.
>Are you saying that I should get the data type (only if
>sqlite3_column_decltype produces null) by querying these views,
>passing it
>the column name?
>
>RBS
>
>
>On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf <kmedc...@dessus.com>
>wrote:
>
>>
>> select max(integer_date) as [max_integer_date int_date] from table1
>>
>>
>> fetch the cursor.description and split the column name at the
>space.  The
>> right part is the datatype for your handy dandy use.
>>
>> You may also be able to use the same method in the table
>declaration,
>> provided that the type carries through, but I do not remember
>offhand.
>>
>> create table1([integer_date] [integer int_date])
>>
>> The former is how pySqlite2 links adapters and converters to the
>query.
>>
>> ---
>> 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



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

Re: [sqlite] sqlite3_column_decltype and max and min

[Bart Smissaert]

Not sure how this works.
I had a look at your views.
Are you saying that I should get the data type (only if
sqlite3_column_decltype produces null) by querying these views, passing it
the column name?

RBS


On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf  wrote:

>
> select max(integer_date) as [max_integer_date int_date] from table1
>
>
> fetch the cursor.description and split the column name at the space.  The
> right part is the datatype for your handy dandy use.
>
> You may also be able to use the same method in the table declaration,
> provided that the type carries through, but I do not remember offhand.
>
> create table1([integer_date] [integer int_date])
>
> The former is how pySqlite2 links adapters and converters to the query.
>
> ---
> 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

Re: [sqlite] sqlite3_column_decltype and max and min

[Keith Medcalf]

Full Schema Tables:


-- Catalog Views using sqlite_master for SysObjects (Object Names)
-- and the various pragma_(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,
   cidas 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,
   cidas ColumnID,
   name   as ColumnName,
   "desc" as IsDescendingOrder,
   coll   as Collation,
   keyas 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 nametypenotnull dflt_value  pk
>--  --  --  --  --  -
>-
>0   a   int_date0   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,
>   cidas 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  ColumnIDColumnName  AffinityIsNotNull
>DefaultValue  IsPrimaryKey
>--  --  --  --  --  -
>-    
>table   x   0   a   int_date0
>0
>
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>

Re: [sqlite] sqlite3_column_decltype and max and min

[Keith Medcalf]

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 nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   int_date0   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,
   cidas 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  ColumnIDColumnName  AffinityIsNotNull   
DefaultValue  IsPrimaryKey
--  --  --  --  --  --  
  
table   x   0   a   int_date0   
  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

Re: [sqlite] sqlite3_column_decltype and max and min

[Keith Medcalf]

select max(integer_date) as [max_integer_date int_date] from table1


fetch the cursor.description and split the column name at the space.  The right 
part is the datatype for your handy dandy use.

You may also be able to use the same method in the table declaration, provided 
that the type carries through, but I do not remember offhand.

create table1([integer_date] [integer int_date])

The former is how pySqlite2 links adapters and converters to the query.

---
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

Re: [sqlite] sqlite3_column_decltype and max and min

[Bart Smissaert]

> Which is bound to lead to problems at some point.

Not sure it is a problem if SQLite still recognizes the custom data types
as it's own data types.
Eg, int_date will be considered integer.

RBS

On Sat, Jan 6, 2018 at 5:59 PM, Simon Slavin  wrote:

> On 6 Jan 2018, at 5:45pm, Bart Smissaert  wrote:
>
> > 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.
>
> You can only get that in a documented way by parsing the CREATE TABLE
> command from sqlite_master.
>
> You’re actually trying to use your schema as if it is its own database.
> Which is bound to lead to problems at some point.  You might consider
> storing this information in another table of the database.
>
> Simon.
> ___
> 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

Re: [sqlite] sqlite3_column_decltype and max and min

[Simon Slavin]

On 6 Jan 2018, at 5:45pm, Bart Smissaert  wrote:

> 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.

You can only get that in a documented way by parsing the CREATE TABLE command 
from sqlite_master.

You’re actually trying to use your schema as if it is its own database.  Which 
is bound to lead to problems at some point.  You might consider storing this 
information in another table of the database.

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