Re: [sqlite] sqlite3_column_decltype and max and min
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 Korotwrote: > > 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
Hi, On Sat, Jan 13, 2018 at 6:32 AM, Niall O'Reillywrote: > > > 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
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
Thanks Warren. Sorry about that, I had an old version in /opt/local/bin. John Gillespie On 11 January 2018 at 15:24, Warren Youngwrote: > 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
On Jan 11, 2018, at 5:47 AM, John Gwrote: > > 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
On 1/11/18, John Gwrote: > > 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
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
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
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
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
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
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
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
Re: [sqlite] sqlite3_column_decltype and max and min
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
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
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
> 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 Slavinwrote: > 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
On 6 Jan 2018, at 5:45pm, Bart Smissaertwrote: > 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] 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