Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
In applications dates/times are input, dates/times are output. Commonly the storage format of dates/times is of no concern. More effort is often spent on time zone display and input, which is an application issue rather than a data store issue. (e.g. fossil) All one *needs* is database functions to input what you output (and vice versa), For me, the major benefit of a database "date/time" types is clarity for humans when reading the schema. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Sunday, 11 August, 2019 16:02, Richard Damon wrote: >On 8/11/19 4:21 PM, Thomas Kurz wrote: >>> I do understand the value of having date/time types in SQLite, but >>> it is not easy to do while retaining backward compatibility. I do not see any value in having a date/time type is SQLite. I do not see any value whatsoever that cannot be met by the existing datatypes (though an extensible type system would be nice, if I really want one of those I can use a database that has one, the only one I know of being DB2). What "value" would a built-in date/time type have? What possible utility could it provide that does not already exist? Even if one were to add one, it would be fraught with peril since it would undoubtedly be implemented badly. Almost all date/time types in almost every existing Operating System or language or database system is implemented badly and it would be far better off if this sort of thing did not exist at all because for almost all practical uses the "builtin" crap has to be ignored and re-written to operate properly. Or you have to incorporate massive amounts of code to work around the deficiencies. Then you have the crap Operating Systems that are inherently defective in their handling and concept of date/time (which are most of them). And you have to write your own code to do it properly anyway. And that sort of defeats the whole purpose of a lite embedded database engine if it has to be overloaded with a massive amount of code to handle date/time properly -- possibly duplicating at great expense what already exists elsewhere. For example, on *some* combinations of Operating System and Language you would have the same code to do "proper" date/time handling duplicated three times, once in the OS, once in the programming language standard library, and once in the database engine. Then of course every third-party library would also have its own duplicated code base to do the same thing. And that is assuming that everyone's definition of "properly" is the same (which it definitely will not be). >>> It'll have to wait for SQLite4 or something. >> Actually I do not really understand the point about backward >> compatibility. Many very useful suggestions are rejected by just >> citing "backward comatibility". As I said previously this is a non-issue unless one wants to add a new type to be dealt with internally (which is not needed). The table column datatype is an arbitrary string. You can put in there whatever you like and interpret it accordingly. Mutatis mutandis the column names returned from a select statement. So while it may be of some (questionable) value to add additional datatypes like decimal (decimal32/decimal64/decimal128 etc), different float widths (float16, float32, float64, float128 etc), or different integer types (int8, int16, int43, int64, int128 etc., and the unsigned varients) these do not provide a whole lot of additional value (though the decimalXX types might). An extensible arbitrary-type system would be more valuable. Since date/time is stored as an offset in some units from an epoch of some type, the "datatype" declaration is nothing more than an annotation of an already existing double or integer type -- and you can already annotate your select column names and table attribute type declarations just fine. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 11 Aug 2019, at 11:51pm, Thomas Kurz wrote: > I don't see a huge problem here. Does the database file have a version number > indicating which version the file has been created with? If so, newer SQLite > libraries could easily emulate the old-style behavior by just checking this > version number. SQLite database files are compatible from version 3.0.0 introduced in 2004. This includes everything except for tables using the WITHOUT ROWID feature, introduced in 2013. > Alternatively, one could introduce a pragma statement, say PRAGMA > emulation=ver, that could default to 3.30 (or whatever) now and which doesn't > change at all This would break backward compatibility. It is necessary to be sure that database files made with current versions of SQLite can be opened with old versions back to 2013. The things you're thinking of are not bad, they're good. But people have suggested them before and we've always found problems with them. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dates, times and R
On Sunday, 11 August, 2019 07:45, Gabor Grothendieck wrote: >R supports Date and POSIXct (date/time) classes which are represented >internally as days and seconds since the UNIX Epoch respectively; >however, due to the class it knows to display and manipulate them as >dates and datetimes rather than numbers. SQLite3 can store integers and doubles. Seems to me this is quite sufficient for storing a "number of seconds" since the Unix Epoch or "number of days" since the Unix epoch. What exactly is the problem? An IEEE-754 double precision floating point number is certainly capable of storing all offsets from the Unix Epoch (either as a days or seconds offset) with far more precision that a human is likely able to discern (or with more precision than the accuracy of most atomic clocks, for that matter). >If sqldf sends a Date or POSIXct to SQLite then it is sent as a >number (days or seconds since the UNIX Epoch) but when it is sent >back it cannot know that that number is supposed to represent a >date or datetime. Why not? The column type declaration in the table is merely an "arbitrary string", and the returned column names from a select are merely "arbitrary strings". It seems like an "application deficiency" that it cannot set and retrieve "arbitrary metadata" for its own internal use (such as providing column type declarations in the CREATE TABLE or additional metadata in the column name (in select statements). Many other "applications" do so without difficulty (for example the default sqlite3 wrapper in Python). Are the R programmers somehow particularly deficient in this regard? I quite often store "timestamps" using application dependent epochs and offsets without difficulty (for example, the number of one-minute intervals since the Unix epoch). Given that just about every programming system and language ever invented seems to store datetime data as some type of offset from some epoch, and each of them different, does not seem to bother interoperability in the least. Once you know the Epoch and Offset interval, conversion is rather simple arithmetic that most children learned in primary school. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> The issue for something like a data-time field is how would you indicate that a field is a data-time field. Due to backwards compatibility it can't use the term data or time to trigger that use, as existing applications use that and expect a different result, based on published and promised rules. I don't see a huge problem here. Does the database file have a version number indicating which version the file has been created with? If so, newer SQLite libraries could easily emulate the old-style behavior by just checking this version number. Alternatively, one could introduce a pragma statement, say PRAGMA emulation=ver, that could default to 3.30 (or whatever) now and which doesn't change at all (or will at least always be some years behind the most current version) and which tells the library to behave exactly as in version . This would allow changing modes without breaking existing applications. One step more could be storing in the database as well, using the version number enabled upon database creation. This way, one would have a mechanism that could eliminate "cannot be done due to backward compatibility" once and forever :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 8/11/19 4:21 PM, Thomas Kurz wrote: >> I do understand the value of having date/time types in SQLite, but it is not >> easy to do while retaining backward compatibility. It'll have to wait for >> SQLite4 or something. > Actually I do not really understand the point about backward compatibility. > Many very useful suggestions are rejected by just citing "backward > comatibility". > > From my point of view, this is not actually a knock-out-criterium, because: > > a) Existing applications would always continue to work, even if using newer > versions of sqlite.dll as it should be no problem for any later version that > intruduced feature X to continue using any database regardless of whether or > not this database actually contains feature X. (This is actual *backward* > compatibility.) The issue for something like a data-time field is how would you indicate that a field is a data-time field. Due to backwards compatibility it can't use the term data or time to trigger that use, as existing applications use that and expect a different result, based on published and promised rules. > > b) New applications could decide whether or not to make use of any new > feature. > > c) Of course, an existing application doesn't know how to handle database > structures with feature X when using an sqlite.dll from the time before this > feature has been introduced. (I would, however, call this *forward* > compatibility.) This is true, but on the other hand, one might ask why an > arbitrary application actually might want to do this? I have often gotten the > response that it is up to the app how to handle data when reading from a > database (IIRC, DATE as a matter of fact was the topic of the discussion). So > one could as well argue that it is the app's responsibility to use up-to-date > libraries when accessing databases. (Note that this applies *only* to an app > dealing with *foreign* databases where one anyhow needs to know how to > interpret data, so this is no knock-out-problem.) > > Someone recently posted about SQLite support for the next 31 (or so) years. > Actually I hope this doesn't mean we will have to wait for three decades > until new features could be implemented...?! New features can, and have, been added. The key is that they need to use syntax that previously was an error (or at least without defined meaning) to implement it. > > Maybe a new subpage could be added to the website, named "proposed features" > or similar, just listing what has been proposed including some short > description. There have been many great ideas and it would be a pity if they > got lost in the depths of the mailing list ;) > > Just my 2cts > Thomas -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> I do understand the value of having date/time types in SQLite, but it is not > easy to do while retaining backward compatibility. It'll have to wait for > SQLite4 or something. Actually I do not really understand the point about backward compatibility. Many very useful suggestions are rejected by just citing "backward comatibility". From my point of view, this is not actually a knock-out-criterium, because: a) Existing applications would always continue to work, even if using newer versions of sqlite.dll as it should be no problem for any later version that intruduced feature X to continue using any database regardless of whether or not this database actually contains feature X. (This is actual *backward* compatibility.) b) New applications could decide whether or not to make use of any new feature. c) Of course, an existing application doesn't know how to handle database structures with feature X when using an sqlite.dll from the time before this feature has been introduced. (I would, however, call this *forward* compatibility.) This is true, but on the other hand, one might ask why an arbitrary application actually might want to do this? I have often gotten the response that it is up to the app how to handle data when reading from a database (IIRC, DATE as a matter of fact was the topic of the discussion). So one could as well argue that it is the app's responsibility to use up-to-date libraries when accessing databases. (Note that this applies *only* to an app dealing with *foreign* databases where one anyhow needs to know how to interpret data, so this is no knock-out-problem.) Someone recently posted about SQLite support for the next 31 (or so) years. Actually I hope this doesn't mean we will have to wait for three decades until new features could be implemented...?! Maybe a new subpage could be added to the website, named "proposed features" or similar, just listing what has been proposed including some short description. There have been many great ideas and it would be a pity if they got lost in the depths of the mailing list ;) Just my 2cts Thomas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does errorLogCallback interrupt my running program?
On Sunday, 11 August, 2019 10:31, test user wrote: >By FFI I mean any of the public SQLite C functions exported from via >FFI. >Ok thanks, this makes sense the error callback is called whilst >SQLite is executing exec/step. >Do I need on callback per thread? No, the callback configuration is per process (the callback address is stored in per process data). That said, the callback is executed synchronously inline by the sqlite3 code detecting the error, so the callback function is executed on whatever thread encountered the error. You cannot receive multiple simultaneous callbacks on the same thread (since one thread can only execute one instruction at a time). I do not see anything in the sqlite3_log code which would serialize callbacks per process. This means that you may, in a multithreaded program where multiple threads are active in the sqlite3 library code simultaneously (such as one connection per thread), receive callbacks simultaneously on multiple threads. This means that if your implementation or the callback function manipulates per process data, that data will have to be protected against concurrent mutation by multiple threads. >On Sun, Aug 11, 2019 at 4:31 PM Clemens Ladisch >wrote: > >> test user wrote: >> > The error log takes a callback which is called when an error >occurs from >> > any SQLite FFI function: >> >> What exactly do you mean with "FFI"? You did not mention any other >> language. >> >> > How does this interrupt my program whilst its running? >> >> As a library, SQLite is part of your program. It just calls the >callback >> while sqlite3_exec()/sqlite3_step() etc. is being executed. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How does errorLogCallback interrupt my running program?
By FFI I mean any of the public SQLite C functions exported from via FFI. Ok thanks, this makes sense the error callback is called whilst SQLite is executing exec/step. Do I need on callback per thread? On Sun, Aug 11, 2019 at 4:31 PM Clemens Ladisch wrote: > test user wrote: > > The error log takes a callback which is called when an error occurs from > > any SQLite FFI function: > > What exactly do you mean with "FFI"? You did not mention any other > language. > > > How does this interrupt my program whilst its running? > > As a library, SQLite is part of your program. It just calls the callback > while sqlite3_exec()/sqlite3_step() etc. is being executed. > > > Regards, > Clemens > ___ > 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] How does errorLogCallback interrupt my running program?
test user wrote: > The error log takes a callback which is called when an error occurs from > any SQLite FFI function: What exactly do you mean with "FFI"? You did not mention any other language. > How does this interrupt my program whilst its running? As a library, SQLite is part of your program. It just calls the callback while sqlite3_exec()/sqlite3_step() etc. is being executed. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dates, times and R
Actually sqldf has multiple heuristics and the one you suggested is already one of them (except for minor differences in syntax) but this has the disadvantage that the user must specify classes whereas if the user simply uses any of the other backends they don't have to. On Sun, Aug 11, 2019 at 10:52 AM Simon Slavin wrote: > > On 11 Aug 2019, at 2:45pm, Gabor Grothendieck wrote: > > > R supports Date and POSIXct (date/time) classes which are represented > > internally as days and seconds since the UNIX Epoch respectively; > > however, due to the class it knows to display and manipulate them as > > dates and datetimes rather than numbers. > > > > If sqldf sends a Date or POSIXct to SQLite then it is sent as a number > > (days or seconds since the UNIX Epoch) but when it is sent back it cannot > > know that that number is supposed to represent a date or datetime. There > > are some default heuristics (if any of the input tables have a column name > > the same name as an output column name then such output columns are > > automatically converted) but this is far from foolproof. > > Add a new heuristic that if the column name ends in '_Date' or '_ > POSIXct' then the value should be converted. When R creates a new SQLite > table, it should append '_Date' or '_POSIXct' to the column name it chooses > for classes of those types. Existing heuristics should continue to work. > > I do understand the value of having date/time types in SQLite, but it is not > easy to do while retaining backward compatibility. It'll have to wait for > SQLite4 or something. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dates, times and R
On 11 Aug 2019, at 2:45pm, Gabor Grothendieck wrote: > R supports Date and POSIXct (date/time) classes which are represented > internally as days and seconds since the UNIX Epoch respectively; > however, due to the class it knows to display and manipulate them as > dates and datetimes rather than numbers. > > If sqldf sends a Date or POSIXct to SQLite then it is sent as a number (days > or seconds since the UNIX Epoch) but when it is sent back it cannot know that > that number is supposed to represent a date or datetime. There are some > default heuristics (if any of the input tables have a column name the same > name as an output column name then such output columns are automatically > converted) but this is far from foolproof. Add a new heuristic that if the column name ends in '_Date' or '_ POSIXct' then the value should be converted. When R creates a new SQLite table, it should append '_Date' or '_POSIXct' to the column name it chooses for classes of those types. Existing heuristics should continue to work. I do understand the value of having date/time types in SQLite, but it is not easy to do while retaining backward compatibility. It'll have to wait for SQLite4 or something. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] dates, times and R
It's really useful that SQLite now supports window operations as that was
one of the key features that R users needed to do many manipulations
using SQLite.
From the perspective of the R language there is really one
particularly key feature left that prevents some users from easily
using SQLite in many cases. That is the lack of date and time types.
From the perspective of the sqldf package in R, the user writes:
sqldf("select ...whatever...")
and sqldf creates an empty sqlite data base, looks for all table names
in the select statement, generates create table statements for them,
uploads the tables to the new database, runs the statement, downloads
the result and deletes the database. (This package uses a lower level
R driver package for sqlite which may also be used directly.)
R supports Date and POSIXct (date/time) classes which are represented
internally as days and seconds since the UNIX Epoch respectively;
however, due to the class it knows to display and manipulate them as
dates and datetimes rather than numbers.
If sqldf sends a Date or POSIXct to SQLite then it is sent as a number (days or
seconds since the UNIX Epoch) but when it is sent back it cannot know that
that number is supposed to represent a date or datetime. There are some
default heuristics (if any of the input tables have a column name the same name
as an output column name then such output columns are automatically
converted) but this is far from foolproof.
This is not a problem for the other backend databases that are
supported since those backends have true date and datetime types so
when R receives such objects it knows to convert them to R's similar types.
Currently SQLite is the default backend but I normally recommend that users
switch to the java based H2 backend if they are doing a lot of date and datetime
processing for the above reason. A lot of data analysis does involve
dates and date times so this covers a lot of applications.
If SQLite were to support true date and datetime types, as do other databases,
and not just functions which handle numbers as if they were dates or
datetimes this problem would be addressed so this is a key remaining feature
that I think SQLite needs.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How does errorLogCallback interrupt my running program?
Hello, The error log takes a callback which is called when an error occurs from any SQLite FFI function: https://www.sqlite.org/errlog.html How does this interrupt my program whilst its running? If my program is busy, does it wait until the current function has completed and the process is idle? Also if I am using threads, do I need a callback handler per thread for all file connections on that thread? Or can I just use one error handler for all descendant threads of the root process? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Wrong return code usage for shell.c.in
Hi everyone,
since my below issue looks like a bug, can we please file a ticket for it? I
understand that I cannot file the ticket myself and this mailing list is the
preferred way of asking to create a ticket on behalf of the reporter. I hope
that this is still correct.
Thank you and all the best,
Kai.
> On Aug 4, 2019, at 10:37 AM, Kai Howelmeyer wrote:
>
> Hi,
>
> I have a database with several selftests. One of those selftests fails. This
> is correctly reported in the textual output of the sqlite shell. Yet, the
> error code when running for example
>
> ```
> sqlite3 -batch < .selftest -init
> insert into selftest(op, cmd, ans) values
> ('run', 'select 1;', '2'),
> ('run', 'select 2;', '2');
> .selftest
> SQLDOC
> ```
>
> is still 0. This only happens when after a failing selftest there is at least
> one test case which still succeeds. I believe
> https://github.com/sqlite/sqlite/blob/version-3.29.0/src/shell.c.in#L8751 is
> at fault, as it will forget previous values for `rc`. Furthermore, `nErr`
> does not have any bearing on the final return code.
>
> I tried going through open bugs, yet I could not find a corresponding bug for
> this issue.
>
>
> Best,
>
> Kai.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
