--- [EMAIL PROTECTED] wrote:
> DESC indices requires a backwards-compatible file format
> changes. Older versions of SQLite (prior to 3.3.0) cannot
> read or write databases that make use of DESC indicdes.
> To preserve compatibility, SQLite generates databases in
> the old format by default. This means the the DESC keyword
> is ignored. To generate a new-format database that honors
> the DESC keyword on indices, you have to do this:
>
> PRAGMA legacy_file_format=OFF;
>
> Prior to doing your very first CREATE TABLE statement. Or,
> you can compile SQLite to use the new file format by
> default using -DSQLITE_DEFAULT_FILE_FORMAT=4
>
> Note that newer versions of SQLite understand both the old
> and the new format and can read and write both kinds of
> database files. The reason for continuing to use the older
> file format is that otherwise older versions of SQLite
> could not read or write database files created by newer
> versions of SQLite.
It seems that the PRAGMA legacy_file_format=OFF and the ability
to use DESC indexes is lost after two VACUUMs and reconnects.
Or am I doing something wrong?
$ rm -f foo.db
$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> PRAGMA legacy_file_format=OFF;
sqlite> PRAGMA legacy_file_format;
0
sqlite> CREATE TABLE abc(a,b,c);
sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc);
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> vacuum;
sqlite> .q
In the next connection we see that the legacy file format reverted
back to 1, but the DESC index is still picked up...
$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc WITH INDEX abc_i ORDER BY
sqlite> vacuum;
sqlite> .q
But if connected to another time, the DESC index is not picked up...
$ ./sqlite3-3.5.3.bin foo.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> PRAGMA legacy_file_format;
1
sqlite> explain query plan select * from abc order by b desc, c asc, a desc;
0|0|TABLE abc
____________________________________________________________________________________
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
