Ralf Junker ralfjunker at gmx.de
Thu Jun 20 18:44:15 EDT 2013 wrote:
On 19.06.2013 17:18, Tom Holden wrote:
I use the SQLiteSpy compilation of SQLite3 which recently upgraded from
pre SQLite 3.7.8 to pre 4.2.0.
You must be mistaken. As the author of SQLiteSpy, I can clearly say that
there is no SQLiteSpy version 3.7.8 nor 4.2.0. The current version is
SQLiteSpy 1.9.3 which implements SQLite 3.7.16.1.
---------------
Tom replies: The versions are those of SQLite3 or of DISQLite3 according to
your version history for SQLIteSpy at
http://www.yunqa.de/delphi/doku.php/products/sqlitespy/history. I am not
sure what you mean by "pre SQLite 3.7.8" for SQLiteSpy 1.9.1 - was it
compiled from SQLite 3.7.7?
I have Windows command line shell sqlite3.exe versions 3.7.5 and the latest
3.7.17. The older one does not have this problem nor do I recall that
SQLiteSpy 1.9.1 did either, although it was with that version that you
helped me out with a fake collation in a loadable extension which opened up
avenues for modifying data indexed by the missing collation. If SQLiteSpy
1.9.1 did not have the "false" errors and used SQLite 3.7.7 and SQLiteSpy
1.9.3 exhibits the errors and uses SQLite 3.7.16.1, then we can conclude
that changes to SQLite3 after 3.7.7 and up to 3.7.16.1 introduced the
problem.
I agree that the SQLiteSpy 1.9.3 behaviour is the same as that of
sqlite3.exe 3.7.17 with respect to these error messages for my examples just
as there were no error messages for the same examples with SQLiteSpy 1.9.1
and sqlite3.exe 3.7.5.
Other SQLite managers are going to exhibit the same behaviours, depending on
their version of SQLite3. Those based on the later, error-inducing SQLite3
are going to be less useful in dealing with databases using proprietary
collation sequences unless they support custom collations and a suitable
extension is available. I say that because collation override does not work
if the query invokes an index of a field using the missing collation.
In my SourceTable example with an index on Name collated by the (missing)
CUSTOM sequence, the following now fails, whereas it did not before:
SELECT Name FROM SourceTable COLLATE NOCASE;
------------------------------------------
Richard Hipp drh at sqlite.org
Thu Jun 20 10:47:41 EDT 2013 wrote:
The use of an index rather than the original table when doing a scan is a
feature, not a bug. You can work around it by adding "ORDER BY rowid" to
your query.
---------------
Tom replies:
I don't see how invoking an index for a table scan can have any benefit. For
me, it's an unwelcome "feature" requiring revisions of queries developed
over nearly four years for them (and only some of them) to be restored to
usability with SQLite managers that do not support custom collations. Given
that collation override does not appear to work in cases where it used to,
some of those scripts cannot be resurrected.
I realise that my usage of SQLite on a database outside of the application
that created it with a proprietary collation sequence that is unavailable to
my SQLIte manager is uncommon but surely not unique. I hope that it is
possible for SQLite3 to return to the tolerance it once had for missing
collations without jeopardising any of the gains that it has made in query
optimisation.
Tom
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
