Re: [sqlite] DESC indexes not available after a VACUUM?
When I compare the database bytes before and after the 2nd process VACUUM these bytes differ: zero-basedvalue value byte offset beforeafter my guess of what it is 2nd VAC 2nd VAC --- --- --- -- 272 5 File change counter? 432 5 Schema cookie? 474 1 meta[1], file format? Doesn't byte offset 47 correspond to meta[1], the file format of the schema layer? --- Joe Wilson <[EMAIL PROTECTED]> wrote: > Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ... > > I'm confused by the explanation. > Ignoring the PRAGMA for the moment, I was surprised that DESC > indexes were not available after a reconnect and VACUUM, as shown > in the example: > > $ 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=OFF; > 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> 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> .q > > $ ./sqlite3-3.5.3.bin foo.db > SQLite version 3.5.3 > Enter ".help" for instructions > 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> explain query plan select * from abc order by b desc, c asc, a desc; > 0|0|TABLE abc > > Notice that the DESC index is no longer available after the second > process' VACUUM was run. > > If this isn't the file format being changed, then why isn't the DESC > index being used? > > Is this by design? Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DESC indexes not available after a VACUUM?
Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ... I'm confused by the explanation. Ignoring the PRAGMA for the moment, I was surprised that DESC indexes were not available after a reconnect and VACUUM, as shown in the example: $ 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=OFF; 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> 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> .q $ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions 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> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc Notice that the DESC index is no longer available after the second process' VACUUM was run. If this isn't the file format being changed, then why isn't the DESC index being used? Is this by design? Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
You will always get much faster searching using a flat file and a grep-like search. For the search you quote you can do better than egrep with an integrated search algorithm like Boyer-Moore. This is no reflection on Sqlite, it is not intended to be a replacement for grep. Spiros Ioannou wrote: Hello, I had a 135MB, 1256132 lines, '@' separated text file containing various words and text fields (like a dictionary). Example record: [EMAIL PROTECTED]@[EMAIL PROTECTED],[EMAIL PROTECTED] altana : μικρός ανθόκηπος - εξώστης, ταράτσα@@@ I imported the data in sqlite3.3.6 but when querying with the 'like' operator, the performance way too slow (about 1.5-2 seconds/query): >time sqlite3 dicts.db "select * from table1 where word like 'asdf%';" 1.156u 0.491s 0:01.64 100.0%0+0k 0+0io 0pf+0w Creating an index did not help at all. Using the COLLATE NOCASE is not of help either since text encoding is not iso-8859-1 (but still is 8-bit). FYI using egrep takes only 0.14s to get results in the worse case scenario: >time egrep -i "[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@asdf" meta.txt 0.077u 0.069s 0:00.14 92.8% 0+0k 0+0io 0pf+0w 1) I know egrep is not a DB but does sqlite use such an inefficient search algorithm for content that cannot be indexed? Why not reverting to simple 'grep-like' methods? Or am I missing something trivial here? 2) Why doesn't an index raise performance at all in this case? Is it because non-latin chars are used? Please clarify, I'm really confused about this. Thank you. Spiros Ioannou p.s. if answers to (1) and (2) don't help, I'm going to write a php-egrep plugin to avoid popen-ing :-) -- Image Video & Multimedia Systems Lab. Department of Electrical & Computer Eng. National Technical University of Athens http://www.image.ece.ntua.gr - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
On 11/27/07, Spiros Ioannou <[EMAIL PROTECTED]> wrote: > I had a 135MB, 1256132 lines, '@' separated text file containing > various words and text fields (like a dictionary). > Example record: > [EMAIL PROTECTED]@[EMAIL PROTECTED],[EMAIL PROTECTED] altana : μικρός > ανθόκηπος - εξώστης, > ταράτσα@@@ > I imported the data in sqlite3.3.6 but when querying with the 'like' > operator, the performance way too slow (about 1.5-2 seconds/query): > > >time sqlite3 dicts.db "select * from table1 where word like 'asdf%';" > 1.156u 0.491s 0:01.64 100.0%0+0k 0+0io 0pf+0w > FYI using egrep takes only 0.14s to get results in the worse case scenario: > >time egrep -i "[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@asdf" > meta.txt > 0.077u 0.069s 0:00.14 92.8% 0+0k 0+0io 0pf+0w > > 1) I know egrep is not a DB but does sqlite use such an inefficient > search algorithm for content that cannot be indexed? Why not reverting > to simple 'grep-like' methods? Or am I missing something trivial here? As a database, the file contains a LOT of structure. SQLite must follow the structure to locate the table, each record in the table, and expand the text field from its stored format. (The text itself is not a big deal, but the row/column that stores it must be found and extracted.) The data is not necessarily stored end-to-end sequentially in the file, as it's impossible to do that and still maintain all the necessary properties of a structured database. egrep gets to work with a flat text file, which it can easily read sequentially and get optimal performance from the OS's file buffering/cache management. It only needs to read a piece of the file and scan for patterns, repeating until done. The only structure it needs to be aware of is line breaks, but that is so simple it can be folded into the pattern scan itself. While someone would need to do profiling to examine exactly where the time goes, it would not suirprise me to find that SQLite's LIKE pattern matcher is more efficient than egrep, but that the overhead from dealing with structured data is responsible for the time difference. I don't find the time itself surprising at all. > 2) Why doesn't an index raise performance at all in this case? Is it > because non-latin chars are used? Careful use of an index should help for the specific query you posted (see http://sqlite.org/optoverview.html#like_opt), but it's not possible for an index to speed up arbitrary patterns. If you need to perform arbitrary pattern searches on a flat text file, SQLite (and most other structured storage for that matter) is simply the wrong tool for the job. grep and friends are highly optimized for just that purpose.
Re: [sqlite] Undefined collation: Peculiar observations ...
On 11/25/07, Ralf Junker <[EMAIL PROTECTED]> wrote: > Imagine that a SQLite3 database opened in a custom application with a > registered a collation sequence named "unknown" has created the following > table: > > CREATE TABLE a (b COLLATE unknown); > > Now open this table in the default SQLite3 CLI. Up to here, everything works > as expected. > > Now some peculiar observations: > 2. Running simple queries like "SELECT * FROM a;" work fine. But subselects, > in their most basic form and with no sorting or comparisons, do not: > > sqlite> SELECT * FROM a, (SELECT * FROM a); That's not just a subselect, it's also a join. Does a subselect on its own have the same behavior? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Undefined collation: Peculiar observations ...
Ralf Junker wrote: Are there any opinions on this list, or should I just post a bug ticket? Ralf Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Now some peculiar observations: 1. Issue "PRAGMA collation_list;" and notice that "unknow" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI: sqlite> PRAGMA collation_list; 0|unknown 1|NOCASE 2|BINARY Question 1: Is this the expected behaviour, or should not "PRAGMA collation_list;" rather list registered collations only? 2. Running simple queries like "SELECT * FROM a;" work fine. But subselects, in their most basic form and with no sorting or comparisons, do not: sqlite> SELECT * FROM a, (SELECT * FROM a); SQL error: no such collation sequence: unknown This is surprising to me because I do not see where the collation sequence should matter to this query. To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here: sqlite> CREATE TABLE b (b collate nocase); sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); 0|Goto|0|17| 1|Integer|0|0| 2|OpenRead|0|3| 3|SetNumColumns|0|1| 4|Integer|0|0| 5|OpenRead|2|3| 6|SetNumColumns|2|1| 7|Rewind|0|14| 8|Rewind|2|13| 9|Column|0|0| 10|Column|2|0| 11|Callback|2|0| 12|Next|2|9| 13|Next|0|8| 14|Close|0|0| 15|Close|2|0| 16|Halt|0|0| 17|Transaction|0|0| 18|VerifyCookie|0|4| 19|TableLock|0|3|b 20|Goto|0|1| 21|Noop|0|0| Question 2: Why does this happen, and is there a way to work around the problem by issuing explicit collation sequences? Ralf, Issue #1 definitely looks like a bug to me. I would create a ticket to ensure it gets addressed. Issue #2 also looks like a bug to me. This particular query doesn't need the collation, so it should be able to process the query without an error. If sqlite was going to complain about any use of a table which uses an unregistered collation, it should do so when the database is first opened and the schema is parsed. It doesn't do this since the custom collations can't be registered until after the database connection is opened (it is needed as the first parameter to the create_collation call). Given that, it can only hope to complain when compiling SQL that requires the collation before it has been registered. Any SQL that does not require the collation should be compiled and processed without complaint. I would suggest creating a bug ticket. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Distinguishing empty-result SELECT from INSERT, UPDATE, DELETE, etc.
See also sqlite3_sql() if sqlite3_prepare_v2() or sqlite3_prepare16_v2() was used. http://www.sqlite.org/cvstrac/chngview?cn=4543 --- Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Evans, Mark (Tandem) <[EMAIL PROTECTED]> wrote: > > Would I be the first person to wish there were a way for, say, a JDBC > > driver that uses SQLite to be able to tell what kind of SQL statement > > it is executing? > > sqlite3_column_count returns 0 for a non-SELECT statement, and a > non-zero value for SELECT. Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Distinguishing empty-result SELECT from INSERT, UPDATE, DELETE, etc.
Evans, Mark (Tandem) <[EMAIL PROTECTED]> wrote: Would I be the first person to wish there were a way for, say, a JDBC driver that uses SQLite to be able to tell what kind of SQL statement it is executing? sqlite3_column_count returns 0 for a non-SELECT statement, and a non-zero value for SELECT. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Distinguishing empty-result SELECT from INSERT, UPDATE, DELETE, etc.
Greetings lite-meisters, Would I be the first person to wish there were a way for, say, a JDBC driver that uses SQLite to be able to tell what kind of SQL statement it is executing? Unless I missed an API, sqlite3_step() indicates either a SELECT that matched at least one row, or any statement including SELECT (with no matches). One use case is to facilitate capture of statistics of statement types executed, number of rows selected, inserted, updated, etc. AFAICS, the driver would have to parse the first word of each statement and behave accordingly. If there's a way to dredge this from SQLite, I'd appreciate a heads up. Thanks, Mark
Re: [sqlite] Version 3.5.3
[EMAIL PROTECTED] wrote: Gerry Snyder <[EMAIL PROTECTED]> wrote: The tclsqlite.dll file will follow soon? Try it now, please. Downloaded and in use. Thanks much. I hope that things work out and you are able to keep including FTS3 in the binaries. I think it will be a plus for most of us. Thanks again, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Version 3.5.3
Gerry Snyder <[EMAIL PROTECTED]> wrote: > The tclsqlite.dll file will follow soon? > Try it now, please. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
On Tue, 27 Nov 2007 14:54:32 +, [EMAIL PROTECTED] wrote: >Jarl Friis <[EMAIL PROTECTED]> wrote: >> [EMAIL PROTECTED] writes: >> >> > >> > The file format is portable. >> > >> > However, if you store UTF16le data, there is a performance >> > penalty for extracting it on a UTF16be machine. >> >> Thanks. That made the answer very clear. Could that clear information >> be put somewhere in the documetation pages or wiki. >> > >Can you suggest an appropriate place to put it? Just a suggestion: Perhaps even on the home page. "This the homepage for SQLite - a library that implements a self-contained, serverless, zero-configuration, _portable_, transactional SQL database engine." With a link to a 'Portable' paragraph on the 'Distinctive Features' page http://www.sqlite.org/different.html Just a suggestion: -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
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] -
[sqlite] LIKE operator extremely slow? more than 10 times slower than egrep?
Hello, I had a 135MB, 1256132 lines, '@' separated text file containing various words and text fields (like a dictionary). Example record: [EMAIL PROTECTED]@[EMAIL PROTECTED],[EMAIL PROTECTED] altana : μικρός ανθόκηπος - εξώστης, ταράτσα@@@ I imported the data in sqlite3.3.6 but when querying with the 'like' operator, the performance way too slow (about 1.5-2 seconds/query): >time sqlite3 dicts.db "select * from table1 where word like 'asdf%';" 1.156u 0.491s 0:01.64 100.0%0+0k 0+0io 0pf+0w Creating an index did not help at all. Using the COLLATE NOCASE is not of help either since text encoding is not iso-8859-1 (but still is 8-bit). FYI using egrep takes only 0.14s to get results in the worse case scenario: >time egrep -i "[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@asdf" meta.txt 0.077u 0.069s 0:00.14 92.8% 0+0k 0+0io 0pf+0w 1) I know egrep is not a DB but does sqlite use such an inefficient search algorithm for content that cannot be indexed? Why not reverting to simple 'grep-like' methods? Or am I missing something trivial here? 2) Why doesn't an index raise performance at all in this case? Is it because non-latin chars are used? Please clarify, I'm really confused about this. Thank you. Spiros Ioannou p.s. if answers to (1) and (2) don't help, I'm going to write a php-egrep plugin to avoid popen-ing :-) -- Image Video & Multimedia Systems Lab. Department of Electrical & Computer Eng. National Technical University of Athens http://www.image.ece.ntua.gr - 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] Version 3.5.3
Gerry Snyder <[EMAIL PROTECTED]> wrote: > The tclsqlite.dll file will follow soon? > > Gerry > Some kind of build problem happened. I'll have to debug it. But I have several other more pressing issue ahead of this on the queue. You want to try to patch up the mkdll.sh script for me, Gerry :-) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Version 3.5.3
Please note: typo on page http://sqlite.org/releaselog/3_5_3.html "TCL interfaced" -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 27, 2007 9:56 AM To: sqlite-users@sqlite.org Subject: [sqlite] Version 3.5.3 Importance: High SQLite version 3.5.3 is now up on the website: http://www.sqlite.org/ There are no major code changes with this release. There are a number of bug fixes, but nothing major. If you are not having problems with the version 3.5.1 or 3.5.2 you are running, there is not a compelling reason to upgrade. We are trying something new with this release. The prebuilt binaries and the amalgamation source code files now include the FTS3 full-text search extension by default. This is not a promise to continue including FTS3 by default in the future - if things do not work out we might go back and revisit the decision to include it. On the other hand, if there are no major problems, we will likely continue to include FTS3 in future releases. Some additional precompiled binarys (for example, for OSX and the TEA repositories) will be added to the download page later today. As always, please report any problems. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - 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] -
Re: [sqlite] Version 3.5.3
The tclsqlite.dll file will follow soon? Gerry - 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] -
Re: [sqlite] Version 3.5.3
Filipe Fernandes <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > SQLite version 3.5.3 is now up on the website: > > > >http://www.sqlite.org/ > > > > Sorry if I missed something here... it's not clear to me how I can > download the documentation. It's not included in the sqlite-3.5.3.tar.gz > file as specified in the download area. The release notes here... > > http://sqlite.org/releaselog/3_5_3.html > > mention that the documentation is moved out of the source tree into a > separate CM system but I'm not entirely certain how to download > documentation from there either... > I forgot to post the documentation. Look again at http://www.sqlite.org/download.html Under the "Documentation" subheading. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Version 3.5.3
[EMAIL PROTECTED] wrote: > SQLite version 3.5.3 is now up on the website: > >http://www.sqlite.org/ > Sorry if I missed something here... it's not clear to me how I can download the documentation. It's not included in the sqlite-3.5.3.tar.gz file as specified in the download area. The release notes here... http://sqlite.org/releaselog/3_5_3.html ... mention that the documentation is moved out of the source tree into a separate CM system but I'm not entirely certain how to download documentation from there either... thanks, filipe - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Version 3.5.3
--- [EMAIL PROTECTED] wrote: > SQLite version 3.5.3 is now up on the website: > As always, please report any problems. http://www.sqlite.org/releaselog/3_5_3.html * DISTINCT can now make use of an INDEX in some cases. Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Version 3.5.3
SQLite version 3.5.3 is now up on the website: http://www.sqlite.org/ There are no major code changes with this release. There are a number of bug fixes, but nothing major. If you are not having problems with the version 3.5.1 or 3.5.2 you are running, there is not a compelling reason to upgrade. We are trying something new with this release. The prebuilt binaries and the amalgamation source code files now include the FTS3 full-text search extension by default. This is not a promise to continue including FTS3 by default in the future - if things do not work out we might go back and revisit the decision to include it. On the other hand, if there are no major problems, we will likely continue to include FTS3 in future releases. Some additional precompiled binarys (for example, for OSX and the TEA repositories) will be added to the download page later today. As always, please report any problems. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] db crash when creating rows
Hi,
I am doing some programmatic stress / benchmark testing on sqlite in the
hope it'll be of use to me in my project.
I'm performing some simple stress tests to gauge read/write speeds, whereby
I fill a table with a large amount rows
However, I hit the following problem on writing:
Creating 10 rows...
died on row 1075
SQL error 2: unable to open database fi
Press any key to continue . . .
I'm using version 3.5.2 and my stress test code (written in C) is attached.
Does anyone have any ideas as to why it fails?
Thanks,
Ged
#include
#include
#include
#define TESTDB "C:\\Users\\Ged\\Desktop\\sqlite stuff\\benchmarking\\testdb"
#define MAX_ROWS 10
static int
ReadCallback(void *NotUsed,
int argc,
char **argv,
char **azColName)
{
/* do stuff */
return 0;
}
static BOOL
FillDatabase(sqlite3 *db)
{
INT ret, i;
char *zErrMsg = 0;
BOOL bRet = FALSE;
printf("Creating %d rows...\n", MAX_ROWS);
ret = sqlite3_exec(db,
"CREATE TABLE test (ID integer primary key
autoincrement, Field1 int, Field2 VARCHAR(50))",
NULL,
0,
&zErrMsg);
if (ret == SQLITE_OK)
{
for (i = 0; i < MAX_ROWS; i++)
{
ret = sqlite3_exec(db,
"INSERT INTO test(Field1, Field2) VALUES(15,
'Hello')",
NULL,
0,
&zErrMsg);
if (ret != SQLITE_OK)
{
fprintf(stderr, "died on row %d\n", i);
fprintf(stderr, "SQL error 2: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
return FALSE;
}
//Sleep(5);
}
bRet = TRUE;
}
else
{
fprintf(stderr, "SQL error 1: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
return bRet;
}
static VOID
ReadDatabase(sqlite3 *db)
{
INT ret;
char *zErrMsg = 0;
printf("Reading %d rows...\n", MAX_ROWS);
ret = sqlite3_exec(db,
"SELECT * FROM test",
ReadCallback,
0,
&zErrMsg);
if (ret != SQLITE_OK)
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
}
static BOOL
OpenDatabase(sqlite3 **db)
{
INT ret;
ret = sqlite3_open(TESTDB, db);
if (ret != SQLITE_OK)
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(*db));
}
return (ret == SQLITE_OK);
}
static VOID
CloseDatabase(sqlite3 *db)
{
if (db)
sqlite3_close(db);
}
int main (int argc, char* argv[])
{
sqlite3 *db = NULL;
DWORD startwrite = 0, startread = 0;
DWORD endwrite = 0, endread = 0;
DeleteFileA(TESTDB);
if (OpenDatabase(&db))
{
startwrite = GetTickCount();
if (FillDatabase(db))
{
endwrite = startread = GetTickCount();
ReadDatabase(db);
endread = GetTickCount();
}
CloseDatabase(db);
}
if (endwrite)
{
INT writetime = endwrite - startwrite;
if (writetime > 1000)
{
writetime /= 1000;
if (writetime > 60)
{
int min = writetime / 60;
int sec = writetime % 60;
printf("\ncreate time - %d.%d mins\n", min, sec);
}
else
printf("\ncreate time - %d secs\n", writetime);
}
else
printf("\ncreate time - %d ms\n", writetime);
if (endread)
printf("read time - %d ms\n\n", endread - startread);
}
system("PAUSE");
return 0;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Undefined collation: Peculiar observations ...
Are there any opinions on this list, or should I just post a bug ticket? Ralf >Imagine that a SQLite3 database opened in a custom application with a >registered a collation sequence named "unknown" has created the following >table: > > CREATE TABLE a (b COLLATE unknown); > >Now open this table in the default SQLite3 CLI. Up to here, everything works >as expected. > >Now some peculiar observations: > > >1. Issue "PRAGMA collation_list;" and notice that "unknow" lists next to the >other registered collations, even though "unknown" is not registered with the >default SQLite3 CLI: > >sqlite> PRAGMA collation_list; >0|unknown >1|NOCASE >2|BINARY > >Question 1: Is this the expected behaviour, or should not "PRAGMA >collation_list;" rather list registered collations only? > > >2. Running simple queries like "SELECT * FROM a;" work fine. But subselects, >in their most basic form and with no sorting or comparisons, do not: > >sqlite> SELECT * FROM a, (SELECT * FROM a); >SQL error: no such collation sequence: unknown > >This is surprising to me because I do not see where the collation sequence >should matter to this query. > >To demonstrate, here is the explain output of a table with a registered >collation sequence. No mention of the collation name here: > >sqlite> CREATE TABLE b (b collate nocase); >sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); >0|Goto|0|17| >1|Integer|0|0| >2|OpenRead|0|3| >3|SetNumColumns|0|1| >4|Integer|0|0| >5|OpenRead|2|3| >6|SetNumColumns|2|1| >7|Rewind|0|14| >8|Rewind|2|13| >9|Column|0|0| >10|Column|2|0| >11|Callback|2|0| >12|Next|2|9| >13|Next|0|8| >14|Close|0|0| >15|Close|2|0| >16|Halt|0|0| >17|Transaction|0|0| >18|VerifyCookie|0|4| >19|TableLock|0|3|b >20|Goto|0|1| >21|Noop|0|0| > >Question 2: Why does this happen, and is there a way to work around the >problem by issuing explicit collation sequences? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Memory Problems/Upgrading SEE
=?ISO-8859-1?Q?Lu=EDs_Santos?= <[EMAIL PROTECTED]> wrote: > Hi, > > I have an SQLite with SEE. > > I am currently having some troubles concerning memory allocation (I'm > getting a MALLOC_ERROR in strange circumstances), and I am trying to > upgrade my SQLite from 3.5.1 to 3.5.2 to see if there's any change to my > problem. > > Should I have some other concern about my Sqlite Encryption Extension > during the upgrade? > An SEE that works for 3.5.1 should still work in 3.5.2. Just replace all of the source files (other than crypto3.c and shell.c) with the 3.5.2 versions. Verison 3.5.3 will be out later today, FWIW... -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite Memory Problems/Upgrading SEE
Hi, I have an SQLite with SEE. I am currently having some troubles concerning memory allocation (I'm getting a MALLOC_ERROR in strange circumstances), and I am trying to upgrade my SQLite from 3.5.1 to 3.5.2 to see if there's any change to my problem. Should I have some other concern about my Sqlite Encryption Extension during the upgrade? Thanks in advance. -- Luís Eduardo Jason Santos **
Re: [sqlite] Transactional DDL
"Michael Ruck" <[EMAIL PROTECTED]> wrote: > Are all CREATE ... statements transactional or is only CREATE TABLE > transactional? > All. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: [sqlite] Transactional DDL
On Nov 27, 2007, at 10:27 PM, Michael Ruck wrote: Are all CREATE ... statements transactional or is only CREATE TABLE transactional? All of the CREATE and DROP statements work properly within transactions. If the containing transaction is rolled back, the CREATE or DROP is rolled back along with everything else. Dan. Mike -Ursprüngliche Nachricht- Von: Dan [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 27. November 2007 15:59 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Transactional DDL On Nov 27, 2007, at 9:47 PM, Igor Sereda wrote: I noticed that CREATE TABLE works well within a transaction, which was a pleasant surprise. I can create a table and insert some rows in it, all quite ACIDly - wow! My question is, is that a declared contract or just a peculiarity that may disappear in future versions? I couldn't find any specs of that behavior in documentation. If I missed it, please point me to the URL. If there are no mentions of that in docs, well, it's probably worth mentioning. It's a supported feature. Also, which statements are not transactional? VACUUM is obviously one of them, are there any other? Some of the pragma statements. Can't think of anything else. Dan. -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Transactional DDL
"Igor Sereda" <[EMAIL PROTECTED]> wrote: > I noticed that CREATE TABLE works well within a transaction, which was a > pleasant surprise. I can create a table and insert some rows in it, all > quite ACIDly - wow! > > My question is, is that a declared contract or just a peculiarity that > may disappear in future versions? It has always been our intent that DDL statements should be transactional. Though, my natural reaction to questions such as you pose is to be non-committal so as to keep my options open. Let's just say that DDL statements in SQLite have always been transactional (from 2.0.0 onward) and we have no plans to change this. > I couldn't find any specs of that > behavior in documentation. If I missed it, please point me to the URL. = > If there are no mentions of that in docs, well, it's probably worth = > mentioning. > > Also, which statements are not transactional? VACUUM is obviously one of = > them, are there any other? > ATTACH. We have thought about changing ATTACH so that you can do it within a transaction. But then we would probably have to DETACH if you rolled back and we didn't want to do that. So for now, you have to do all of your ATTACHing and DETACHing outside of transactions. Nothing else jumps to mind. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] Transactional DDL
Are all CREATE ... statements transactional or is only CREATE TABLE transactional? Mike > -Ursprüngliche Nachricht- > Von: Dan [mailto:[EMAIL PROTECTED] > Gesendet: Dienstag, 27. November 2007 15:59 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] Transactional DDL > > > On Nov 27, 2007, at 9:47 PM, Igor Sereda wrote: > > > I noticed that CREATE TABLE works well within a transaction, which > > was a pleasant surprise. I can create a table and insert some rows > > in it, all quite ACIDly - wow! > > > > My question is, is that a declared contract or just a peculiarity > > that may disappear in future versions? I couldn't find any > specs of > > that behavior in documentation. If I missed it, please point me to > > the URL. If there are no mentions of that in docs, well, it's > > probably worth mentioning. > > It's a supported feature. > > > Also, which statements are not transactional? VACUUM is obviously > > one of them, are there any other? > > Some of the pragma statements. Can't think of anything else. > > Dan. > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Transactional DDL
On Nov 27, 2007, at 9:47 PM, Igor Sereda wrote: I noticed that CREATE TABLE works well within a transaction, which was a pleasant surprise. I can create a table and insert some rows in it, all quite ACIDly - wow! My question is, is that a declared contract or just a peculiarity that may disappear in future versions? I couldn't find any specs of that behavior in documentation. If I missed it, please point me to the URL. If there are no mentions of that in docs, well, it's probably worth mentioning. It's a supported feature. Also, which statements are not transactional? VACUUM is obviously one of them, are there any other? Some of the pragma statements. Can't think of anything else. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
Jarl Friis <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] writes: > > > > > The file format is portable. > > > > However, if you store UTF16le data, there is a performance > > penalty for extracting it on a UTF16be machine. > > Thanks. That made the answer very clear. Could that clear information > be put somewhere in the documetation pages or wiki. > Can you suggest an appropriate place to put it? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Transactional DDL
I noticed that CREATE TABLE works well within a transaction, which was a pleasant surprise. I can create a table and insert some rows in it, all quite ACIDly - wow! My question is, is that a declared contract or just a peculiarity that may disappear in future versions? I couldn't find any specs of that behavior in documentation. If I missed it, please point me to the URL. If there are no mentions of that in docs, well, it's probably worth mentioning. Also, which statements are not transactional? VACUUM is obviously one of them, are there any other? Thanks! Igor - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
[EMAIL PROTECTED] writes: > > The file format is portable. > > However, if you store UTF16le data, there is a performance > penalty for extracting it on a UTF16be machine. Thanks. That made the answer very clear. Could that clear information be put somewhere in the documetation pages or wiki. Jarl Jarl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
Jarl Friis <[EMAIL PROTECTED]> wrote: > "Nuno Lucas" <[EMAIL PROTECTED]> writes: > > > If you will be sharing databases between different endienness > > systems then you care, so you will take appropriate actions to have > > the best result. The same is true with any other portable file > > format. > > So my question boils down to: Is the SQLite fileformat portable? Or is > it only portable across endianess-equivalent architectures? > The file format is portable. However, if you store UTF16le data, there is a performance penalty for extracting it on a UTF16be machine. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
On Nov 27, 2007, at 7:26 PM, Jarl Friis wrote: "Nuno Lucas" <[EMAIL PROTECTED]> writes: If you will be sharing databases between different endienness systems then you care, so you will take appropriate actions to have the best result. The same is true with any other portable file format. So my question boils down to: Is the SQLite fileformat portable? Or is it only portable across endianess-equivalent architectures? It's portable between architectures with different endianness. There is some small conversion overhead if using UTF-16 and the endianness of the database doesn't match that of the machine it is used on. But not much. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
"Nuno Lucas" <[EMAIL PROTECTED]> writes: > If you will be sharing databases between different endienness > systems then you care, so you will take appropriate actions to have > the best result. The same is true with any other portable file > format. So my question boils down to: Is the SQLite fileformat portable? Or is it only portable across endianess-equivalent architectures? Jarl - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite Manager Firefox extension
On Tue, 2007-11-27 at 11:45 +0300, Igor Sereda wrote: > It does look good, but it's not quite usable with large databases though. > For example, I couldn't wait till Browse and Search page showed a 2 million > rows table -- it seemed to load everything into memory, eating up resources > and causing Firefox to come up with "stop script" dialogs. > > Otherwise, a nice UI. I agree with both comments. Big tables are slow to load, but otherwise, great. Regards, James. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite Manager Firefox extension
It does look good, but it's not quite usable with large databases though. For example, I couldn't wait till Browse and Search page showed a 2 million rows table -- it seemed to load everything into memory, eating up resources and causing Firefox to come up with "stop script" dialogs. Otherwise, a nice UI. -- Igor -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 27, 2007 5:47 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite Manager Firefox extension It is pretty, runs well and is easy to use. Better than "decent". P Kishor wrote: > To all those looking for a decent, cross-platform SQLite gui, check > out > > > SQLite Manager Firefox add-on > > https://addons.mozilla.org/en-US/firefox/addon/5817 > > I have just started experimenting with it, and it really quite nice > even at version 0.2.9.1. Works well on my Mac. > > -- > Puneet Kishor > > -- > --- To unsubscribe, send email to > [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
