Re: [sqlite] Issue with index (maybe asc/desc problem)
Thanks for the quick answer. This product is great! Bye! Clodo <[EMAIL PROTECTED]> wrote: But a changelogs say: "Version 3.3.0 adds support for CHECK constraints, DESC indices, " I use the 3.5.2 version. What's the status/problem about that? How i can re-write the first select above to use an index on both fields? Thanks! 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. At some point in the future, once everybody has upgraded past SQLite 3.3.0, we will probably switch to generating the new file format by default. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Issue with index (maybe asc/desc problem)
--- [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] -
Re: [sqlite] Issue with index (maybe asc/desc problem)
Clodo <[EMAIL PROTECTED]> wrote: > > But a changelogs say: > "Version 3.3.0 adds support for CHECK constraints, DESC indices, " > > I use the 3.5.2 version. > What's the status/problem about that? > How i can re-write the first select above to use an index on both fields? > Thanks! > 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. At some point in the future, once everybody has upgraded past SQLite 3.3.0, we will probably switch to generating the new file format by default. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Issue with index (maybe asc/desc problem)
Hi to all, i have a problem with indexes. For example: CREATE TABLE T1 ( a text, b text ); CREATE INDEX I1 on T1 (a asc, b desc); 1' query: explain query plan select * from t1 order by a asc, b desc limit 10 output detail-> TABLE T1 2' query: explain query plan select * from t1 order by a asc, b asc limit 10 output detail-> TABLE T1 WITH INDEX I1 ORDER BY The only difference between first and second query are the order of the "b" field. The "Create Index" docs say: "Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order, but the sort order is ignored in the current implementation. Sorting is always done in ascending order." But a changelogs say: "Version 3.3.0 adds support for CHECK constraints, DESC indices, " I use the 3.5.2 version. What's the status/problem about that? How i can re-write the first select above to use an index on both fields? Thanks! - To unsubscribe, send email to [EMAIL PROTECTED] -
