dates, times and R

classic Classic list List threaded Threaded
37 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Simon Slavin-3
On 12 Aug 2019, at 6:01pm, Thomas Kurz <[hidden email]> wrote:

>> 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.
>
> This is what I would call "forward compatibility": You expect an old application to be able to read file formats of a future version. Do you have an example where there is really required?

A programmer uses a copy of the SQLite CLI to correct errors in a database made and maintained by a production program.  This involves making a new table, copying some data from the old data to the new table, deleting the old table, then renaming the new table.  When the programmer is finished making changes, they replace the old version of the database with the corrected version.

The production program, written three years ago, uses an old version of the SQLite library.  The editing happens on the programmer's own computer which, naturally, has an up-to-date copy of the SQLite CLI, with an up-to-date copy of the SQLite library.
_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Thomas Kurz
> A programmer uses a copy of the SQLite CLI to correct errors in a database made and maintained by a production program.  This involves making a new table, copying some data from the old data to the new table, deleting the old table, then renaming the new table.  When the programmer is finished making changes, they replace the old version of the database with the corrected version.

But this ain't a problem because the default would be a compatible version.

Maybe I should explain my idea a bit more explicit, to avoid a misunderstanding. Let's assume preparations with 3.31 and the first real change (e.g. DATE) in 3.34. For simplicity, we create a table TEST without any declaration. Application itself uses 3.16.

a) Creating table with any version <3.31 results in: CREATE TABLE TEST

b) Creating table with 3.31+ without any explicit declaration:
CREATE TABLE TEST --> will be stored as CREATE TABLE TEST VERSION=3.31
This should be no problem for any library below 3.31 as it should ignore the VERSION declaration.

Everything that is done with this table will always operate in 3.31 compatibility mode, regardless of what library version is actually being used, be it an old one or a new one.

c) To enable the new DATE interpreation, we do:
CREATE TABLE TEST VERSION=3.34 --> will be stored the same way

Now we have to distinguish three cases:

i) Library versions 3.34+ can use the modified DATE datatype. Reasonably, it should internally be encoded as INT or FLOAT, depending on what timestamp is internally being used. This will be a compatibility-fallback for case iii. Alternatively, one could use BLOB to tell case iii not to handle this data at all.

ii) Versions 3.31, 3.32 and 3.33 should reject modifying such kind of table as (when being honest) they do not know about the feature.

iii) Versions up to and including 3.30 should basically also reject modifications, but they cannot do it as they don't now about the VERSION flag. For this case, we have stored the data either as INT/FLOAT which can be handled, or as BLOB which wouldn't be touched.

The important point is that we would have introduced a mechanism which should be future-proof, but breaking compatibility as few as possible. And a programmer would always have to actively enable a "feature level". If he didn't do that, the library would operate in compatibility mode as it is the case right now. Modifying tables would not be a problem either.

_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Simon Slavin-3
On 12 Aug 2019, at 8:20pm, Thomas Kurz <[hidden email]> wrote:

> c) To enable the new DATE interpreation, we do:
> CREATE TABLE TEST VERSION=3.34 --> will be stored the same way

And now you have a file which can't be edited with old versions of the CLI.  However you cut it, you have compatibility problems.
_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Thomas Kurz
> And now you have a file which can't be edited with old versions of the CLI.  However you cut it, you have compatibility problems.

One shouldn't do it at all. It's like trying to a edit a DOCX with Word95. It's not *backward* compatibility. It's not the case you mentioned before. And finally, when using the CLI there's no reason not to update the CLI.

_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Keith Medcalf
In reply to this post by Simon Slavin-3

On Monday, 12 August, 2019 13:52, Simon Slavin <[hidden email]> wrote:

>On 12 Aug 2019, at 8:20pm, Thomas Kurz <[hidden email]> wrote:

>> c) To enable the new DATE interpreation, we do:
>> CREATE TABLE TEST VERSION=3.34 --> will be stored the same way

>And now you have a file which can't be edited with old versions of
>the CLI.  However you cut it, you have compatibility problems.

These issues already exist in the current codebase.  For example, older versions of SQLite3 have no clue what to do with things like indexes on expressions, conditional indexes, or even descending indexes.  If such a database is attempted to be accessed by a version of SQLite3 that does not understand the features, the database is treated as being corrupt.  However, since those features merely "overload" existing capabilities, removing them renders the database useable by those earlier versions.

Similarly, I suppose one could "uverload" a standard float or integer type to handle datetime data and this would not affect the underlying database structure, but would merely be seen as "corruption" by a version of SQLite3 that does not know how to parse the new sugar.  Removing the sugar would render the database once more accessible by the older versions.

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

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Ling, Andy
In reply to this post by Thomas Kurz
> This is what I would call "forward compatibility": You expect an old application
> to be able to read file formats of a future version. Do you have an example
> where there is really required?

I have an Android app that lets you share the database between users. The
app will run on a variety of versions of Android with a similar variety
of versions of sqlite. Currently any version can read the database from any other
version regardless of whether it is old reading new or vice versa.

Regards

Andy Ling


**********************************************************************
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.
_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Dominique Devienne
On Tue, Aug 13, 2019 at 10:58 AM Ling, Andy <[hidden email]>
wrote:

> > This is what I would call "forward compatibility": You expect an old
> application
> > to be able to read file formats of a future version. Do you have an
> example
> > where there is really required?
>
> I have an Android app that lets you share the database between users. The
> app will run on a variety of versions of Android with a similar variety
> of versions of sqlite. Currently any version can read the database from
> any other
> version regardless of whether it is old reading new or vice versa.
>

Then if you want to retain that, just don't use new features that might
break fwd-compatibility.
That's already the case now, e.g. without-rowid tables, if used, break
older versions of SQLite.
Or defining a view or a trigger using window-functions. Or zillions other
things that can make
a DB incompatible with old versions of SQLite. That can happen now!

And to gracefully handle cases of incompatibilities "by choice", your app
should use
https://www.sqlite.org/pragma.html#pragma_user_version to detect such
cases, and/or
have a user setting to save in "compatibility mode", foregoing better/new
features, or not.
Of course, the version of the runtime SQLite library can also be used.

The fact the SQLite file-format has little to no wiggle room for changes,
does *NOT* mean
SQLite won't or can't grow new features. It's growing them all the time.

The "for the next 35-years" comment was made by DRH himself, and do not
mean there
won't be new things in SQLite for that time, but instead that DRH and team
commit (pun intended :))
to supporting all existing and future features until 2050 at the least
(modulo the BUS FACTOR of course...),
and that commitment is backed by SQLite's outstanding 100% line and branch
coverage testing, and is
one of the primary reason DRH is always reluctant to add new requested
features (quite a testing burden).

But don't despair, FKs, Function-based Indexes, CTEs, Window-Functions,
etc... were asked
literally for years, and made it eventually. When you have a long view like
Richard does,
you're not in a rush to do anything I guess :). --DD
_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Ling, Andy
> > > This is what I would call "forward compatibility": You expect an old
> > application
> > > to be able to read file formats of a future version. Do you have an
> > example
> > > where there is really required?
> >
> > I have an Android app that lets you share the database between users. The
> > app will run on a variety of versions of Android with a similar variety
> > of versions of sqlite. Currently any version can read the database from
> > any other
> > version regardless of whether it is old reading new or vice versa.
> >
>
> Then if you want to retain that, just don't use new features that might
> break fwd-compatibility.

Yes, I am aware of this. I was just providing a "real" example to answer
the question posed by the OP.

Regards

Andy Ling


**********************************************************************
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.
_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Peter da Silva-2
If the datr/time is stored internally as utc iso8601 text then it will
remain compatible with old versions and can implement whatever new behavior
is needed on new versions. The bigger question is 'what new behavior'? The
only nee behavior seems to be 'let this third party package see it as a
date', which it should be able to figure out by looking at the schema.
_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

J Decker
In reply to this post by Tim Streater-3
On Mon, Aug 12, 2019 at 6:54 AM Tim Streater <[hidden email]> wrote:

> On 12 Aug 2019, at 14:30, J Decker <[hidden email]> wrote:
>
> > On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin <[hidden email]>
> wrote:
> >
> >> On 12 Aug 2019, at 1:27pm, Tim Streater <[hidden email]> wrote:
> >>
> >> > I don't expect to do that with SQL. My "seconds since the epoch" is
> >> based on converting any particular time to GMT and storing that. That
> >> number is then converted to a date/time with TZ info for display.
>
> > If the timezone is stored, then the time is all UTC and easily sortable.
> >  A sub-order of timeone within a sepcific time sequence ends up happening
> > *shrug*
>
> Why are you storing the timezone? You display the TZ of the user who is,
> later, viewing the data. And that user could be anywhere.

Because the actual time on the clock on the wall matters.
I want to know cashiers that are making transactions outside of 9am-5pm
(for instance).  But depending on where they are that time is different.
I also want to know the time continuously according to the linear time that
they happened in.




> > But then, I'm assuming the time would just be ISO8601; since SQLite
> > datetime functions take that as an input already.
>
> I'm a user; I don't want my times displayed as ISO8601. That's why we have
> date/time control panels so the user gets to choose how those are displayed.
>
> Then strftime it.
I don't want to see PK/FK identifiers either.


>
> --
> Cheers  --  Tim
> _______________________________________________
> 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: Backward compatibility vs. new features (was: Re: dates, times and R)

Stephen Chrzanowski
On Tue, Aug 13, 2019 at 7:30 PM J Decker <[hidden email]> wrote:

>
> > Why are you storing the timezone? You display the TZ of the user who is,
> > later, viewing the data. And that user could be anywhere.
>
> Because the actual time on the clock on the wall matters.
> I want to know cashiers that are making transactions outside of 9am-5pm
> (for instance).  But depending on where they are that time is different.
> I also want to know the time continuously according to the linear time that
> they happened in.
>
>
The time on the clock is irrelevant, completely and totally, due to
daylight saving.  Moving the clock back and forth twice a year causes a
chunk of time to go missing or a chunk of time to be duplicated.  The clock
is a User Interface problem, not a data storage problem.  When you want to
know that locations actual time, you store that stores location with their
TZ information, and your UI presents that information to you.

Because UTC does not move forward or backwards (Occasionally we add a
second due to the rotation of the earth slowing down*, but we NEVER go
backwards), anything time related in a database NEEDS to be stored in
UTC/Zulu/GMT-0 time (Or whatever you want to call it).  This is THE base
time.  Sure, you COULD store the location,  but, if you're in British
Columbia, looking at a stores transactions in New Found Land, (Or say
California versus New York), what time do you believe?

I work for a company that literally has servers all around the world, which
means Time Zones ARE a thing for us.  We set every single machine we deploy
to UTC.  If there were any times to be displayed in "local" time, it's
handled at the application/presentation layer, not the database layer.

*https://www.quora.com/Why-is-Earths-rotation-slowing-down -- First hit on
Google.
_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Warren Young
On Aug 14, 2019, at 9:55 AM, Stephen Chrzanowski <[hidden email]> wrote:

>
> On Tue, Aug 13, 2019 at 7:30 PM J Decker <[hidden email]> wrote:
>
>>> Why are you storing the timezone? You display the TZ of the user who is,
>>> later, viewing the data. And that user could be anywhere.
>>
>> Because the actual time on the clock on the wall matters.
>>
> The time on the clock is irrelevant, completely and totally, due to
> daylight saving.

I agree with your reply as far as it goes, and I agree that times stored in the DB should be in UTC or some similar format.

(By that I mean we don’t need to get into UTC vs UT1 vs whatever other “universal” time standard you prefer.  Just pick one and stick to it for all users of that DB.)

However, it *can* be helpful to store the time zone or the local UTC offset alongside the universal timestamp in the DB so you can translate the timestamp to local time in calculations.  Without it, you can’t ask the DB questions like, “Give me all records that occurred between 9am and 10am local time,” or “Across all stores, what are our busiest hours?”

The timestamp in UTC lets you ask different questions, such as “Give me the 10 most recent matching records,” which breaks if you store timestamps as local time, and your DB holds values from multiple time zones.
_______________________________________________
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: Backward compatibility vs. new features (was: Re: dates, times and R)

Stephen Chrzanowski
Yep, I agree, to which is where I pointed out that you'd need the
additional information for that store location.  But that's all you'd need,
and only in that one location.  Your UI (Or whatever specialized report
generation) would have to do the math from the UTC time, and convert it
accordingly to whatever the stores location TZ info is, then present the
appropriate information.  So even if you are in BC, you can look at NFLD
info and get the NFLD local times.

On Wed, Aug 14, 2019 at 12:24 PM Warren Young <[hidden email]> wrote:

> On Aug 14, 2019, at 9:55 AM, Stephen Chrzanowski <[hidden email]>
> wrote:
> >
> > On Tue, Aug 13, 2019 at 7:30 PM J Decker <[hidden email]> wrote:
> >
> >>> Why are you storing the timezone? You display the TZ of the user who
> is,
> >>> later, viewing the data. And that user could be anywhere.
> >>
> >> Because the actual time on the clock on the wall matters.
> >>
> > The time on the clock is irrelevant, completely and totally, due to
> > daylight saving.
>
> I agree with your reply as far as it goes, and I agree that times stored
> in the DB should be in UTC or some similar format.
>
> (By that I mean we don’t need to get into UTC vs UT1 vs whatever other
> “universal” time standard you prefer.  Just pick one and stick to it for
> all users of that DB.)
>
> However, it *can* be helpful to store the time zone or the local UTC
> offset alongside the universal timestamp in the DB so you can translate the
> timestamp to local time in calculations.  Without it, you can’t ask the DB
> questions like, “Give me all records that occurred between 9am and 10am
> local time,” or “Across all stores, what are our busiest hours?”
>
> The timestamp in UTC lets you ask different questions, such as “Give me
> the 10 most recent matching records,” which breaks if you store timestamps
> as local time, and your DB holds values from multiple time zones.
> _______________________________________________
> 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: Backward compatibility vs. new features (was: Re: dates, times and R)

Thomas Kurz
In reply to this post by Peter da Silva-2
Another reason: because PostgreSQL supports it as well (including timezone) ;-)

----- Original Message -----
From: Peter da Silva <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Tuesday, August 13, 2019, 23:18:29
Subject: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

If the datr/time is stored internally as utc iso8601 text then it will
remain compatible with old versions and can implement whatever new behavior
is needed on new versions. The bigger question is 'what new behavior'? The
only nee behavior seems to be 'let this third party package see it as a
date', which it should be able to figure out by looking at the schema.
_______________________________________________
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
|

Backward compatibility

Thomas Kurz
In reply to this post by Simon Slavin-3
We recently had a discussion about date/time support, but also other suggestions, which sooner or later end up at the point "cannot be done, would break backward compatibility". (See also: "Backward compatibility vs. new features (was: Re: dates, times and R)")

I'm always curious and monitoring trunk development, and now I have read in the draft release notes for 3.31:

> If an earlier version of SQLite attempts to read a database file that contains a generated column in its schema, then that earlier version will perceive the generated column syntax as an error and will report that the database schema is corrupt.

...which leads me to some confusion. So there actually *are* features breaking compability? Wouldn't that be a chance for full date/time support as well?

_______________________________________________
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: Backward compatibility

Graham Holden
Tuesday, October 29, 2019, 7:37:40 AM, Thomas Kurz <[hidden email]> wrote:

>> If an earlier version of SQLite attempts to read a database file
>> that contains a generated column in its schema, then that earlier
>> version will perceive the generated column syntax as an error and
>> will report that the database schema is corrupt.  

> ...which leads me to some confusion. So there actually *are*
> features breaking compability? Wouldn't that be a chance for full
> date/time support as well?

There are different types of compatibility (that probably have proper
names). The main one that the SQLite devs (seem to) want to avoid is
the case where someone has an existing app/program that uses SQLite.
They upgrade their copy of SQLite to the latest, and that program
suddenly stops working because of a change in SQLite. This type of
"breaking compatibility" is very rare (the change in behaviour of
ALTER TABLE RENAME, which now fixes references to a renamed table,
is one of the few that comes to mind).

The situaton described above is different: to get that situation, a
database has to have been deliberately created/modified in a LATER
version of SQLite and then handed over to a program using an earlier
of SQLite to process.  Note: simply _creating_ the database with a
later version is [almost always] safe (so long as you only use
features the older version understands): the problem comes if you
deliberately make use of a feature (e.g. "generated columns") that
only the later version supports. This is sort-of the equivalent of
asking Microsoft Word 2.0 to open a file created with Word 2007.

Graham


_______________________________________________
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: Backward compatibility

Dominique Devienne
In reply to this post by Thomas Kurz
On Tue, Oct 29, 2019 at 8:38 AM Thomas Kurz <[hidden email]> wrote:

> We recently had a discussion about date/time support, but also other
> suggestions, which sooner or later end up at the point "cannot be done,
> would break backward compatibility". (See also: "Backward compatibility vs.
> new features (was: Re: dates, times and R)")
>
> I'm always curious and monitoring trunk development, and now I have read
> in the draft release notes for 3.31:
>
> > If an earlier version of SQLite attempts to read a database file that
> contains a generated column in its schema, then that earlier version will
> perceive the generated column syntax as an error and will report that the
> database schema is corrupt.
>
> ...which leads me to some confusion. So there actually *are* features
> breaking compability? Wouldn't that be a chance for full date/time support
> as well?
>

To complement Graham's answer, I see 3 different levels of backward
compatibility:

1) file format: https://www.sqlite.org/fileformat.html
2) SQLite C API: https://www.sqlite.org/capi3ref.html
3) SQL language: https://www.sqlite.org/lang.html (including the built-in
SQL functions)

Anything that breaks #1 or #2 is dead on arrival basically.
SQLite4 was a brief attempt at #1, but it fizzled out.

OTOH, #3 keeps adding new features over time, because as long as you don't
use those new features, older versions of SQLite can read/process the DB
files just fine.
This is more "forward-compatibility" in a sense, when older SQLite versions
can or cannot read DB files written using newer versions of SQLite.
While "backward-compatibility" is the latest version of SQLite reading
older SQLite's DB files (mostly about #1 and #2).

Your DB files are as forward-compatible as you choose them to be, depending
on the new SQL features you use or not.
I didn't follow the date/time thread closely enough to see where the
proposed changes fall in the above categorization.
I hope this helps. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12