sqlite3_column_decltype and max and min

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_column_decltype and max and min

Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Simon Slavin-3
On 6 Jan 2018, at 5:45pm, Bart Smissaert <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> On 6 Jan 2018, at 5:45pm, Bart Smissaert <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Keith Medcalf
In reply to this post by Bart Smissaert

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Keith Medcalf
In reply to this post by Bart Smissaert

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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_<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:[hidden email]]
>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-
>>[hidden email]] 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
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Bart Smissaert
In reply to this post by Keith Medcalf
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 <[hidden email]> 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-
> >[hidden email]] 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
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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-
> >[hidden email]] 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 <[hidden email]>
> >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-
> >> >[hidden email]] 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
> >> >[hidden email]
> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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-
> >[hidden email]] 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 <[hidden email]>
> >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-
> >> >[hidden email]] 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
> ><[hidden email]>
> >> >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-
> >> >> >[hidden email]] 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
> >> >> >[hidden email]
> >> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >> >users
> >> >>
> >> >>
> >> >>
> >> >> _______________________________________________
> >> >> sqlite-users mailing list
> >> >> [hidden email]
> >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >> >users
> >> >>
> >> >_______________________________________________
> >> >sqlite-users mailing list
> >> >[hidden email]
> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Dinu
In reply to this post by Keith Medcalf
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

John Gillespie-2
In reply to this post by Keith Medcalf
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 <[hidden email]> 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:[hidden email]]
> >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-
> >>[hidden email]] 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
> >>[hidden email]
> >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Richard Hipp-3
On 1/11/18, John G <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Warren Young
In reply to this post by John Gillespie-2
On Jan 11, 2018, at 5:47 AM, John G <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

John Gillespie-2
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 <[hidden email]> wrote:

> On Jan 11, 2018, at 5:47 AM, John G <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Niall O'Reilly
In reply to this post by Richard Hipp-3


On 11 Jan 2018, at 13:23, Richard Hipp wrote, in reply to John G <[hidden email]>:

> 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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (921 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Igor Korot
Hi,

On Sat, Jan 13, 2018 at 6:32 AM, Niall O'Reilly <[hidden email]> wrote:

>
>
> On 11 Jan 2018, at 13:23, Richard Hipp wrote, in reply to John G <[hidden email]>:
>
>> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and max and min

Craig H Maynard
In reply to this post by Bart Smissaert
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 <[hidden email]> wrote:
>
> Why? Just include sqlite3.c in your project and recompile.
>
>
> On Sat, Jan 13, 2018 at 6:32 AM, Niall O'Reilly <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users