v3.2.1 and current differences!

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

v3.2.1 and current differences!

Edwin Knoppert
Just wanted to warn you i can not read a newly created table created with
the current release and opening it in v3.2.1 (afaik)

Sorry, i removed the older dll, i overwrote it with the latest and read the
table instantly.
Before i had 0 tables shown.

A simple query was used:

CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )

I believe i also tried first:
CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
Sorry, i forgot.

I may assume only a major version will have a different format?

Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Chris Peachment
On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:

>Just wanted to warn you i can not read a newly created table created with
>the current release and opening it in v3.2.1 (afaik)

>Sorry, i removed the older dll, i overwrote it with the latest and read the
>table instantly.
>Before i had 0 tables shown.

>A simple query was used:

>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )

>I believe i also tried first:
>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
>Sorry, i forgot.

>I may assume only a major version will have a different format?


After opening the database file with a later library version and
before doing anything else, issue the command:

        PRAGMA legacy_file_format=ON;



Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

D. Richard Hipp
In reply to this post by Edwin Knoppert
"Edwin Knoppert" <[hidden email]> wrote:

> Just wanted to warn you i can not read a newly created table created with
> the current release and opening it in v3.2.1 (afaik)
>
> Sorry, i removed the older dll, i overwrote it with the latest and read the
> table instantly.
> Before i had 0 tables shown.
>
> A simple query was used:
>
> CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )
>
> I believe i also tried first:
> CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
> Sorry, i forgot.
>
> I may assume only a major version will have a different format?
>

SQLite 3.3.0 can read and write all prior versions of SQLite
databases.  But SQLite 3.2.8 cannot read or write a database
created by SQLite 3.3.0, unless you use

  PRAGMA legacy_file_format=TRUE;

prior to creating the database, or unless you compile 3.3.0
with -DSQLITE_DEFAULT_FILE_FORMAT=1.

The file format enhancement in version 3.3.0 has caused an
inordinate amount of grief for the benefit it provides.  I
deeply regret making it the default.  I might yet, in a future
release, make the old file format the default.  The in a couple
of years time, once all the legacy versions of SQLite that
do not understand it have faded from existance, I can make
the enhanced file format the default again.

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Edwin Knoppert
In reply to this post by Chris Peachment
OK, thanks!
Must been a change in the fileformat then..
A bit odd the system does not handle backwards compatibility in minor
releases.

:)

PS, still can't find my own post, while a reply is given.. odd



----- Original Message -----
From: "C.Peachment" <[hidden email]>
To: <[hidden email]>
Sent: Monday, June 26, 2006 8:16 PM
Subject: Re: [sqlite] v3.2.1 and current differences!


> On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:
>
>>Just wanted to warn you i can not read a newly created table created with
>>the current release and opening it in v3.2.1 (afaik)
>
>>Sorry, i removed the older dll, i overwrote it with the latest and read
>>the
>>table instantly.
>>Before i had 0 tables shown.
>
>>A simple query was used:
>
>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )
>
>>I believe i also tried first:
>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
>>Sorry, i forgot.
>
>>I may assume only a major version will have a different format?
>
>
> After opening the database file with a later library version and
> before doing anything else, issue the command:
>
> PRAGMA legacy_file_format=ON;
>
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Edwin Knoppert
In reply to this post by Chris Peachment
>After opening the database file with a later library version and before
>doing anything else, issue the command:
Sorry, maybe i misunderstood.
The db is created with the latest release, v3.2.1 did not manage to read it.
Maybe the pragma helps anyway..


----- Original Message -----
From: "C.Peachment" <[hidden email]>
To: <[hidden email]>
Sent: Monday, June 26, 2006 8:16 PM
Subject: Re: [sqlite] v3.2.1 and current differences!


> On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:
>
>>Just wanted to warn you i can not read a newly created table created with
>>the current release and opening it in v3.2.1 (afaik)
>
>>Sorry, i removed the older dll, i overwrote it with the latest and read
>>the
>>table instantly.
>>Before i had 0 tables shown.
>
>>A simple query was used:
>
>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )
>
>>I believe i also tried first:
>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
>>Sorry, i forgot.
>
>>I may assume only a major version will have a different format?
>
>
> After opening the database file with a later library version and
> before doing anything else, issue the command:
>
> PRAGMA legacy_file_format=ON;
>
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Chris Peachment
You can create the database with the older Sqlite version, but any use
with the new version must be preceded by the pragma, otherwise the
new version changes the data format, making it unreadable by the
old version thereafter.

It got me confused when I started too.


On Mon, 26 Jun 2006 21:28:59 +0200, Edwin Knoppert wrote:

>>After opening the database file with a later library version and before
>>doing anything else, issue the command:
>Sorry, maybe i misunderstood.
>The db is created with the latest release, v3.2.1 did not manage to read it.
>Maybe the pragma helps anyway..


>----- Original Message -----
>From: "C.Peachment" <[hidden email]>
>To: <[hidden email]>
>Sent: Monday, June 26, 2006 8:16 PM
>Subject: Re: [sqlite] v3.2.1 and current differences!


>> On Mon, 26 Jun 2006 20:03:35 +0200, Edwin Knoppert wrote:
>>
>>>Just wanted to warn you i can not read a newly created table created with
>>>the current release and opening it in v3.2.1 (afaik)
>>
>>>Sorry, i removed the older dll, i overwrote it with the latest and read
>>>the
>>>table instantly.
>>>Before i had 0 tables shown.
>>
>>>A simple query was used:
>>
>>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )
>>
>>>I believe i also tried first:
>>>CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
>>>Sorry, i forgot.
>>
>>>I may assume only a major version will have a different format?
>>
>>
>> After opening the database file with a later library version and
>> before doing anything else, issue the command:
>>
>> PRAGMA legacy_file_format=ON;
>>
>>
>>
>>




Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Peter Bierman
In reply to this post by D. Richard Hipp
At 2:20 PM -0400 6/26/06, [hidden email] wrote:
>SQLite 3.3.0 can read and write all prior versions of SQLite
>databases.  But SQLite 3.2.8 cannot read or write a database
>created by SQLite 3.3.0, unless you use
>
>   PRAGMA legacy_file_format=TRUE;
>
>prior to creating the database, or unless you compile 3.3.0
>with -DSQLITE_DEFAULT_FILE_FORMAT=1.

Does this forward-incompatibility include the PRAGMA user_version?

Ie., Can 3.2.8 read at least the user_version from a 3.3.0 file, even
if it can't read anything else?

Are there web pages that describe the pros and cons of the new file format?

-pmb
Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

D. Richard Hipp
In reply to this post by Edwin Knoppert
"Edwin Knoppert" <[hidden email]> wrote:
> A bit odd the system does not handle backwards compatibility in minor
> releases.
>

It *does* handle backwards compatibility.  It is forwards
compatibility that causes problems.

Newer versions of SQLite can read databases generated
by all older versions.  Older versions of SQLite cannot
necessarily read databases that are generated by newer
versions of SQLite.
--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

D. Richard Hipp
In reply to this post by Chris Peachment
"C.Peachment" <[hidden email]> wrote:
> You can create the database with the older Sqlite version, but any use
> with the new version must be preceded by the pragma, otherwise the
> new version changes the data format, making it unreadable by the
> old version thereafter.
>

The file format is only updated if you VACUUM.
--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

D. Richard Hipp
In reply to this post by Peter Bierman
Peter Bierman <[hidden email]> wrote:

> At 2:20 PM -0400 6/26/06, [hidden email] wrote:
> >SQLite 3.3.0 can read and write all prior versions of SQLite
> >databases.  But SQLite 3.2.8 cannot read or write a database
> >created by SQLite 3.3.0, unless you use
> >
> >   PRAGMA legacy_file_format=TRUE;
> >
> >prior to creating the database, or unless you compile 3.3.0
> >with -DSQLITE_DEFAULT_FILE_FORMAT=1.
>
> Does this forward-incompatibility include the PRAGMA user_version?
>
> Ie., Can 3.2.8 read at least the user_version from a 3.3.0 file, even
> if it can't read anything else?

No.

>
> Are there web pages that describe the pros and cons of the new file format?
>

The new file format stores boolean values (the integers 0 and 1)
more efficiently - requiring only 1 bytes of disk space instead of
2.  There are no other changes.


Let me reemphasize that the new file format has caused so much
grief that I will likely revert to the older format with 3.4.0.
That is to say, databases created by 3.4.0 will be readable by
3.2.8.  3.4.0 will be able to read and write both the old and the
new formats, of course.  And you will still be able to use the new
format using a pragma or a compile-time option.  It just won't be
the default any more.

I have learned my lesson.  Do not enhance the file format without
a very good reason.  Saving one byte of space when storing booleans
is not a sufficiently good reason...

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Edwin Knoppert
In reply to this post by D. Richard Hipp
Thanks, but do not base your decision on my.
I'm using sqlite to little to complain, i simply was not aware of the
change.

I write software to make use of sqlite for other people (a designer).
I only need to mention this issue.



----- Original Message -----
From: <[hidden email]>
To: <[hidden email]>
Sent: Monday, June 26, 2006 8:20 PM
Subject: Re: [sqlite] v3.2.1 and current differences!


"Edwin Knoppert" <[hidden email]> wrote:

> Just wanted to warn you i can not read a newly created table created with
> the current release and opening it in v3.2.1 (afaik)
>
> Sorry, i removed the older dll, i overwrote it with the latest and read
> the
> table instantly.
> Before i had 0 tables shown.
>
> A simple query was used:
>
> CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME TEXT )
>
> I believe i also tried first:
> CREATE TABLE [TABLE1] ( ID INTEGER PRIMARY KEY, NAME )
> Sorry, i forgot.
>
> I may assume only a major version will have a different format?
>

SQLite 3.3.0 can read and write all prior versions of SQLite
databases.  But SQLite 3.2.8 cannot read or write a database
created by SQLite 3.3.0, unless you use

  PRAGMA legacy_file_format=TRUE;

prior to creating the database, or unless you compile 3.3.0
with -DSQLITE_DEFAULT_FILE_FORMAT=1.

The file format enhancement in version 3.3.0 has caused an
inordinate amount of grief for the benefit it provides.  I
deeply regret making it the default.  I might yet, in a future
release, make the old file format the default.  The in a couple
of years time, once all the legacy versions of SQLite that
do not understand it have faded from existance, I can make
the enhanced file format the default again.

--
D. Richard Hipp   <[hidden email]>


Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Peter Bierman
In reply to this post by D. Richard Hipp
At 4:01 PM -0400 6/26/06, [hidden email] wrote:

>Peter Bierman <[hidden email]> wrote:
>>  At 2:20 PM -0400 6/26/06, [hidden email] wrote:
>>  >SQLite 3.3.0 can read and write all prior versions of SQLite
>>  >databases.  But SQLite 3.2.8 cannot read or write a database
>>  >created by SQLite 3.3.0, unless you use
>>  >
>>  >   PRAGMA legacy_file_format=TRUE;
>>  >
>>  >prior to creating the database, or unless you compile 3.3.0
>>  >with -DSQLITE_DEFAULT_FILE_FORMAT=1.
>>
>>  Does this forward-incompatibility include the PRAGMA user_version?
>>
>>  Ie., Can 3.2.8 read at least the user_version from a 3.3.0 file, even
>>  if it can't read anything else?
>
>No.

Oy. Is there any way for a 3.2.x or earlier library to detect this situation?
(Maybe no way via the sqlite APIs, but probably a file format version
at a known offset?)


>Let me reemphasize that the new file format has caused so much
>grief that I will likely revert to the older format with 3.4.0.
>That is to say, databases created by 3.4.0 will be readable by
>3.2.8.  3.4.0 will be able to read and write both the old and the
>new formats, of course.  And you will still be able to use the new
>format using a pragma or a compile-time option.  It just won't be
>the default any more.
>
>I have learned my lesson.  Do not enhance the file format without
>a very good reason.  Saving one byte of space when storing booleans
>is not a sufficiently good reason...


:-)

Progress is good, I just need a way to distinguish progress from
errors. I thought the user_version PRAGMA would be the one safe value
that any version could read from any other version.

What's the rough time line for 3.4.0?  (Or even a 3.3.x that reverts
the file format.) It would be unfortunate if the next release of Mac
OS X shipped with this issue.

-pmb
Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Eugene Wee
In reply to this post by D. Richard Hipp
Hi,

[hidden email] wrote:

> The new file format stores boolean values (the integers 0 and 1)
> more efficiently - requiring only 1 bytes of disk space instead of
> 2.  There are no other changes.
>
>
> Let me reemphasize that the new file format has caused so much
> grief that I will likely revert to the older format with 3.4.0.
> That is to say, databases created by 3.4.0 will be readable by
> 3.2.8.  3.4.0 will be able to read and write both the old and the
> new formats, of course.  And you will still be able to use the new
> format using a pragma or a compile-time option.  It just won't be
> the default any more.
>
> I have learned my lesson.  Do not enhance the file format without
> a very good reason.  Saving one byte of space when storing booleans
> is not a sufficiently good reason...

I think that reverting back is not the solution. At the moment, the news about
the change in file format is several entries down the news and changes list.
People may also not be aware that newer SQLite versions are backwards compatible
but not forwards compatible. If the documentation was clearer on the file
format, its changes and compatibility, an enhancement of the file format may not
cause so much confusion.

Regards,
Eugene Wee

P.S.: any news on when 3.4.0 will be out? :D
Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

D. Richard Hipp
Eugene Wee <[hidden email]> wrote:

> Hi,
>
> [hidden email] wrote:
> > The new file format stores boolean values (the integers 0 and 1)
> > more efficiently - requiring only 1 bytes of disk space instead of
> > 2.  There are no other changes.
> >
> >
> > Let me reemphasize that the new file format has caused so much
> > grief that I will likely revert to the older format with 3.4.0.
> > That is to say, databases created by 3.4.0 will be readable by
> > 3.2.8.  3.4.0 will be able to read and write both the old and the
> > new formats, of course.  And you will still be able to use the new
> > format using a pragma or a compile-time option.  It just won't be
> > the default any more.
> >
> > I have learned my lesson.  Do not enhance the file format without
> > a very good reason.  Saving one byte of space when storing booleans
> > is not a sufficiently good reason...
>
> I think that reverting back is not the solution. At the moment,
> the news about the change in file format is several entries down
> the news and changes list. People may also not be aware that newer
> SQLite versions are backwards compatible but not forwards compatible.
> If the documentation was clearer on the file format, its changes
> and compatibility, an enhancement of the file format may not
> cause so much confusion.
>

You know what - the new file format supports an additional feature
that I completely forgot about: descending indices.  So I suppose
it was worth going to the new format after all.  I put in the
change back in December of last year and had completely forgotten
about the descending index addition.  But it is coming back to
me now.  I added descending indices and said to myself, as long
as I am having to change the file format, I might as well enhance
the boolean value representation too.  But then I completely forgot
about the descending index change.  Silly me....

>
> P.S.: any news on when 3.4.0 will be out? :D
>

I still have not made a final decision on whether it will be
3.3.7 or 3.4.0.  There is no incompatibility so 3.3.7 would
technically be correct.  But there are a lot of enhancements
so I was thinking of going to 3.4.0 just to emphasize the
magnitude of the change.

I'm thinking end of July or early August.



Reply | Threaded
Open this post in threaded view
|

Re: v3.2.1 and current differences!

Edwin Knoppert
Question remains if it isn't better to go to a new major version on such
changes.
Forward compatibility is assumed by users imo.



----- Original Message -----
From: <[hidden email]>
To: <[hidden email]>
Sent: Tuesday, June 27, 2006 1:08 PM
Subject: Re: [sqlite] v3.2.1 and current differences!


Eugene Wee <[hidden email]> wrote:

> Hi,
>
> [hidden email] wrote:
> > The new file format stores boolean values (the integers 0 and 1)
> > more efficiently - requiring only 1 bytes of disk space instead of
> > 2.  There are no other changes.
> >
> >
> > Let me reemphasize that the new file format has caused so much
> > grief that I will likely revert to the older format with 3.4.0.
> > That is to say, databases created by 3.4.0 will be readable by
> > 3.2.8.  3.4.0 will be able to read and write both the old and the
> > new formats, of course.  And you will still be able to use the new
> > format using a pragma or a compile-time option.  It just won't be
> > the default any more.
> >
> > I have learned my lesson.  Do not enhance the file format without
> > a very good reason.  Saving one byte of space when storing booleans
> > is not a sufficiently good reason...
>
> I think that reverting back is not the solution. At the moment,
> the news about the change in file format is several entries down
> the news and changes list. People may also not be aware that newer
> SQLite versions are backwards compatible but not forwards compatible.
> If the documentation was clearer on the file format, its changes
> and compatibility, an enhancement of the file format may not
> cause so much confusion.
>

You know what - the new file format supports an additional feature
that I completely forgot about: descending indices.  So I suppose
it was worth going to the new format after all.  I put in the
change back in December of last year and had completely forgotten
about the descending index addition.  But it is coming back to
me now.  I added descending indices and said to myself, as long
as I am having to change the file format, I might as well enhance
the boolean value representation too.  But then I completely forgot
about the descending index change.  Silly me....

>
> P.S.: any news on when 3.4.0 will be out? :D
>

I still have not made a final decision on whether it will be
3.3.7 or 3.4.0.  There is no incompatibility so 3.3.7 would
technically be correct.  But there are a lot of enhancements
so I was thinking of going to 3.4.0 just to emphasize the
magnitude of the change.

I'm thinking end of July or early August.