Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
On Dec 4, 2007, at 10:27 AM, Dr Gerard Hammond wrote: I have reported it as a bug - ticket is http://www.sqlite.org/ cvstrac/tktview?tn=2822 It appears as though the /src/select.c (Line1499) changed from: if( iCol<0 && mustComplete ){ to: }else if( mustComplete ){ in version 1.336 of this file - http://www.sqlite.org/cvstrac/ filediff?f=sqlite/src/select.c&v1=1.335&v2=1.336 And this change results in this bug. On 04/12/2007, at 4:59 AM, Joe Wilson wrote: --- Marco Bambini <[EMAIL PROTECTED]> wrote: Starting from version 3.4.2 I receive errors with queries like: SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY a.field or even SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY a.field error is: ORDER BY term number 1 does not match any result column Tables are created by: CREATE TABLE a (field); CREATE TABLE b (field); Please note that the above queries worked fine with sqlite 3.2.x or 3.3.x. Any idea? You probably know the workarounds: SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1; or SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER BY x; but it's odd that this one doesn't work as well: create table t1(a); create table t2(b); select t1.a from t1 union all select t2.b from t2 order by a; SQL error: ORDER BY term number 1 does not match any result column At present, expressions in the ORDER BY clause attached to a compound SELECT must be either: 1) An integer between 1 and the number of columns returned by the SELECT statement (inclusive), or 2) A simple identifier (no quotes). In this case SQLite tries to match the identifier to one of the returned columns of data by scanning the result-set of each of the individual SELECT statements, starting from the left. The identifier matches the column if the expression in the result set is either "" or " as " This means you cannot specify an arbitrary sort key for a compound statement, you can only nominate one of the returned columns to sort on. i.e., if we have: CREATE TABLE x1(a, b, c); CREATE TABLE x2(a, b, c); then the following pairs of statements are equivalent: SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY a; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2; To my mind, the logical change to make would be to allow this: SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b"; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b]; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; Because it is consistent with this kind of statement: SELECT "x1.b" FROM (SELECT x1.b FROM x1); Any opinions? Dan. Cheers. -- Dr Gerard Hammond Garvan Institute of Medical Research -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Cache resizing problem.
Hi, I am getting a problem if I am modifying cache size. This can be reproduced by the following steps:- We need two connections to reprodce this. Say the database name is: "test.db" *"test.db" SHOULD NOT BE EXISTING ALREADY, WE HAVE TO CREATE EACH TIME WE WANT TO GET THE PROBLEM.* 1. Create a connection to test.db. Here "test.db" should be created physically. If it is existing please DELETE it. We will call this connection as First Connection 2. Set PRAGMA cache_size = for this connection 3. Create another connection to test.db. This is called the Second Connection. 4. Creat a a table (say "student_master") in this second connection. 5. Insert some data in this table through second connection. 6. Then try to select from the same table with the first connection. I am getting error while preparing the SELECT statement. And the error is no such table: "student_master". So I think the changes we are making in the second connection are not visible. But I do not understand, why does it work if I am not executing that CACHE resize query! I am pasting the code for linux here. It can be reproduced in windows too. There is one macro in my code:- #define SET_CACHE_SQLITE 1 You can comment it, if you dont want the cache resize code, and then it will work properly! Is it my problem or a SQLite problem? THE CODE:- /**/ #include #include #include #include "sqlite3.h" // Define SET_CACHE_SQLITE if you want the cache to be enabled, otherwise comment it #define SET_CACHE_SQLITE 1 int func_reproduce( ) { sqlite3 *db1;// Database handle for first connection sqlite3 *db2;// Database handle for second connection sqlite3_stmt * pstmt1 = NULL; sqlite3_stmt * pstmt2 = NULL; int ret = 1; void **unused; // Open the first connection int ret1 = sqlite3_open( "test.db", &db1 ); if(ret1 != SQLITE_OK) { printf("OPEN error - db\n", sqlite3_errmsg(db1)); exit(EXIT_FAILURE); } printf("Opened db1\n"); #ifdef SET_CACHE_SQLITE // Modify Cache? // Modify the cache size in first connection char * query_cache = "PRAGMA cache_size = 5000"; //Prepare Cache query ret = sqlite3_prepare(db1, query_cache, (int)strlen(query_cache)*sizeof(char), &pstmt1, (const char **)&unused); if(ret != SQLITE_OK) { printf("Prepare Error - CACHE: (%s)\n", sqlite3_errmsg(db1)); exit(EXIT_FAILURE); } printf("Prepared: CACHE\n"); //Step Cache query ret = sqlite3_step(pstmt1); if(ret != SQLITE_DONE) { printf("Step Error - CACHE: (%s)\n", sqlite3_errmsg(db1)); exit(EXIT_FAILURE); } printf("Stepped CACHE\n"); sqlite3_finalize(pstmt1); printf("Finalized cache\n"); #endif //SET_CACHE_SQLITE //Open the second connection ret1 = sqlite3_open( "test.db", &db2 ); if(ret1 != SQLITE_OK) { printf("OPEN error - db2\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Opened db2\n"); char *query_create = "CREATE TABLE IF NOT EXISTS [student_master] ( \ [student_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, \ [student_name] VARCHAR(50))"; //Prepare CREATE TABLE query ret = sqlite3_prepare(db2, query_create, (int)strlen(query_create)*sizeof(char), &pstmt2, (const char **)&unused); if(ret != SQLITE_OK) { printf("Prepare Error - CREATE: (%s)\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Prepared for CREATE\n"); //Step CREATE TABLE query ret = sqlite3_step(pstmt2); if(ret != SQLITE_DONE) { if(ret == SQLITE_ERROR) sqlite3_finalize(pstmt2); printf("Step Error - CREATE: (%s)\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Stepped for CREATE\n"); sqlite3_finalize(pstmt2); printf("Finalized CREATE\n"); // Insert a row with the second connection char *query_insert = "INSERT INTO [student_master]([student_name]) values ('CrazyBoy')"; // Prepare for INSERT query ret = sqlite3_prepare(db2, query_insert, (int)strlen(query_insert)*sizeof(char), &pstmt2, (const char **)&unused); if(ret != SQLITE_OK) { printf("Prepare Error - INSERT: (%s)\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Prepared for INSERT\n"); // Step INSERT query ret = sqlite3_step(pstmt2); if(ret != SQLITE_DONE) { if(ret == SQLITE_ERROR) sqlite3_finalize(pstmt2); printf("Step Error - INSERT: (%s)\n", sqlite3_errmsg(db2)); exit(EXI
Re: [sqlite] Cache resizing problem.
On Dec 4, 2007, at 3:49 PM, Sabyasachi Ruj wrote: Hi, I am getting a problem if I am modifying cache size. This can be reproduced by the following steps:- We need two connections to reprodce this. Say the database name is: "test.db" *"test.db" SHOULD NOT BE EXISTING ALREADY, WE HAVE TO CREATE EACH TIME WE WANT TO GET THE PROBLEM.* 1. Create a connection to test.db. Here "test.db" should be created physically. If it is existing please DELETE it. We will call this connection as First Connection 2. Set PRAGMA cache_size = for this connection 3. Create another connection to test.db. This is called the Second Connection. 4. Creat a a table (say "student_master") in this second connection. 5. Insert some data in this table through second connection. 6. Then try to select from the same table with the first connection. This is failing because the internal representation of the database schema used by the first connection has not yet been updated to include the changes made in step 4 by the second connection. After the sqlite3_prepare() in step 6 fails with the "no such table" error, SQLite realises that it may be using an old schema version and discards it. The new schema will be loaded fresh from the database next time a call is made to sqlite3_prepare(). So the easiest fix is just to retry the sqlite3_prepare(). I am getting error while preparing the SELECT statement. And the error is no such table: "student_master". So I think the changes we are making in the second connection are not visible. But I do not understand, why does it work if I am not executing that CACHE resize query! Because in the absence of the cache-resize query, the first connection does not initialise it's internal schema until step 6. By the time it is loaded for the first time in step 6 the "student_master" table has already been added. Hence no problem. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to get older source
I'm trying to access the 3.4.2 (and previous) sources from the website by changing the version numbers in the source download link to various values, which has worked for me in the past. If I use the following links, things work and I can get both the long form and amalgamated sources: http://sqlite.org/sqlite-amalgamation-3_5_3.zip http://sqlite.org/sqlite-amalgamation-3_5_2.zip http://www.sqlite.org/sqlite-source-3_5_3.zip http://www.sqlite.org/sqlite-source-3_5_2.zip However, versions 3.5.1 and back don't work (file not found.) Note that I don't try to use the amalgamation stuff for 3.4.2 back, just the normal source links. Have historical versions been removed, or is there another place I should be looking for this source? Thanks, Ian
[sqlite] Getting values by column name
Hello, I have a prepared statement that looks like char *getSql = "select * from transducer where id = ?"; rc = sqlite3_prepare_v2(transducerDb,getSql,-1,&preparedGet,NULL); Is there a way that I can find out the number of a given column name in the query? Instead of ts.key = sqlite3_column_int(preparedGet,0); I'd like to do something like: ts.key = sqlite3_column_int(preparedGet,get_column_id(preparedGet,"id")); Is there a way to do that? Thanks, Jon
Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
--- Dan <[EMAIL PROTECTED]> wrote: > i.e., if we have: > >CREATE TABLE x1(a, b, c); >CREATE TABLE x2(a, b, c); > > then the following pairs of statements are equivalent: ... > >SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; >SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2; Don't you mean ORDER BY 1? SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; <-- I thought *only* the leftmost SELECT in the compound chain governs the selection of the column names used by the ORDER BY. The names of the subsequent compound SELECTs should be ignored. At least that's how it works on MySQL and other databases I've used: given: create table x1(a INT, b INT, c INT); insert into x1 values(1, 2, 3); create table x2(a INT, b INT, c INT); insert into x2 values(9, 0, 4); mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; +--+--+ | b| a| +--+--+ |2 |1 | |9 |0 | +--+--+ mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2; +--+--+ | b| a| +--+--+ |9 |0 | |2 |1 | +--+--+ mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; +--+--+ | b| a| +--+--+ |2 |1 | |9 |0 | +--+--+ Oracle has the same behavior as MySQL, as I recall. sqlite 3.5 produces a different result since it appears to be getting the column name from the rightmost compound select: SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; x1.b|a 9|0 2|1 Compare MySQL: create table x1(a INT, b INT, c INT); insert into x1 values(1, 2, 3); create table g2(x INT, y INT, z INT); insert into g2 values(9, 0, 4); mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y; ERROR 1054 (42S22): Unknown column 'y' in 'order clause' to sqlite: sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y; 9|0 2|1 > To my mind, the logical change to make would be to allow this: > >SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b"; >SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b]; >SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; This query is (also) unambiguous given the logic outlined above: SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; I thought all of this was already hashed in this thread: http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Getting values by column name
Jon Drnek wrote: I have a prepared statement that looks like char *getSql = "select * from transducer where id = ?"; rc = sqlite3_prepare_v2(transducerDb,getSql,-1,&preparedGet,NULL); Is there a way that I can find out the number of a given column name in the query? You can enumerate all columns and generate a map of column name to index. See sqlite3_column_count, sqlite3_column_name[16] Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single row insert speeds
All - After reviewing some of my calculations, I realized I made a mistake and that the inserts didn't improve as much as I thought. I used the PRAGMA synchronouse = OFF and I'm averaging about 0.21 ms for an insert on a Core 2 Shuttle running Fedora 3. Here is the table I've defined: CREATE TABLE sampleTable ( logHost varchar(64) DEFAULT NULL, compId smallint(5) DEFAULT NULL, pid int(10) DEFAULT NULL, version varchar(8) DEFAULT NULL, rptTime decimal(20,6) DEFAULT NULL, rptStatus tinyint(3) DEFAULT NULL, data text ); Here is a typical insert: INSERT INTO sampleTable VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", \"1196303669.06533598 8998\", \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 peak) ;\")"; For testing, I've modified the the insert to look like this: char *insertStatement = "PRAGMA synchronous=OFF;BEGIN;INSERT INTO sampleTable VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", \"1196303669.065335988998\", \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 peak);\");COMMIT;"; Is this the correct syntax for the PRAGMA statement? Can I issue it once and will it remain active as long as the connection is open? Thanks, Mark On Dec 3, 2007 6:45 PM, Mark Riehl <[EMAIL PROTECTED]> wrote: > I used the PRAGMA statement and turned off the synchronous option. It > made a huge difference. Single inserts were ranging from 5 - 50 ms, > now, they're at ~.04 ms. > > However, I guess there is a tradeoff between the safety of the > synchronous operation (in case power is lost) versus the insert > speeds. > > Thanks for the help, > Mark > > > On Dec 3, 2007 12:59 PM, <[EMAIL PROTECTED]> wrote: > > "P Kishor" <[EMAIL PROTECTED]> wrote: > > > I get 1000+ inserts a second for a random 100 byte string > > > insert ( > > > > I get 5+ inserts/sec on my Linux box. > > > > Insert speed is not the issue. It is COMMIT speed. At > > each commit, SQLite waits until all data is on oxide before > > continuing. That will typically take at least two rotations > > of the disk platter, or about 17 millisecond, depending on > > your disk drive. Waiting for data to get to oxide is > > part of being ACID. You can set: > > > >PRAGMA synchronous=OFF; > > > > and your COMMITs will go *much* faster because it will no > > longer wait on the disk drive. But if you lose > > power in the middle of a commit, you might corrupt your > > database file. > > > > Note that there is an implied BEGIN...COMMIT around every > > INSERT statement if you do not explicitly start a transaction > > using your own BEGIN. > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Writing to Flash Memory
I'm developing an application to run on an ARM-based PDA-like device. It's running Linux and I've got SQLite 3.4.1 installed. I'm trying to optimize my insert statements (in a different discussion thread) I've gotten some good suggestions to use the PRAGMA statement to disable the syncronization. On my Shuttle running Fedora 3, I see insert times of ~0.2 ms. However, the same test application running on the PDA is taking ~25 ms for the same insert. The media doesn't appear to be that slow - writing the same insert statement to a text file takes ~0.2ms. Granted, there is more going on with the SQLite insert than the file right, but, I wouldn't expect such a discrepancy. Does anyone have any experience using SQLite and flash? Any particular settings that would be helpful? Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single row insert speeds
"Mark Riehl" <[EMAIL PROTECTED]> wrote: >> > For testing, I've modified the the insert to look like this: > char *insertStatement = "PRAGMA synchronous=OFF;BEGIN;INSERT INTO > sampleTable VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", > \"1196303669.065335988998\", > \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 > peak);\");COMMIT;"; > > Is this the correct syntax for the PRAGMA statement? Can I issue it > once and will it remain active as long as the connection is open? > The PRAGMA syntax is correct. Issue it once when you initially open the connection. The BEGIN and COMMIT are automatic and can be omitted (for additional speed). SQL wants values to be quoted using single-quotes, not double-quotes. SQLite allows double-quotes but it is technically wrong. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite does not support multi-row inserts?
MS SQL 2008 will support multi-row insert statements too. http://richardsbraindump.blogspot.com/2007/07/what-new-in-sql-2008-katmai.ht ml Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 04, 2007 10:47 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite does not support multi-row inserts? I can't confirm whether it is, but Wikipedia claims it is: http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts Multirow inserts An SQL feature (since SQL-92) is the use of row value constructors to insert multiple rows at a time in a single SQL statement: INSERT INTO table (column1, [column2, ... ]) VALUES (value1a, [value1b, ...]), (value2a, [value2b, ...]), ... This feature is supported by DB2, PostgreSQL (since version 8.2), MySQL, and H2. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite does not support multi-row inserts?
--- Sander Marechal <[EMAIL PROTECTED]> wrote: > Joe Wilson wrote: > > --- Sander Marechal <[EMAIL PROTECTED]> wrote: > >> I ran into a problem when using SQLite from PHP. It appears that SQLite3 > >> does not support multi-row inserts in the form: > >> > >> INSERT INTO (col1, col2) VALUES (1, 2), (3, 4) > >> > >> Will if be implemented in the future? > > > > I doubt it. > > Too bad. I think it's in SQL92 but I'm not 100% sure (The SQL92 specs > are a bit hard to read). I can't confirm whether it is, but Wikipedia claims it is: http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts Multirow inserts An SQL feature (since SQL-92) is the use of row value constructors to insert multiple rows at a time in a single SQL statement: INSERT INTO table (column1, [column2, ... ]) VALUES (value1a, [value1b, ...]), (value2a, [value2b, ...]), ... This feature is supported by DB2, PostgreSQL (since version 8.2), MySQL, and H2. 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] search time in FTS3 tables sometimes very long
For our stock management I created a SQLITE 3.5.3 table, containing around 1,5M entries. The FTS3 table is created with statement: CREATE VIRTUAL TABLE volltext using FTS3(referenzcode, code, deut, engl, ital, sppm, rep, info) "referenzcode" and "code" are containing part numbers with 10 or 11 digits or letters, f.e. "1401326732D" "deut", "engl" and "ital" are containing descriptions in different languages of the same part, max. 80 characters. "sppm" and "rep" are internal references with up to 6 characters, describing the part usage ("mech", "elect", ...) "info" is the biggest field, containing up to some 1000 characters but also sometimes empty. "info" may contain f.e. a digitalized product manual or installation manual text. What is really strange is that FTS3 search phrases like SELECT referenzcode FROM volltext where volltext match ('installation manual') are performed really fast within some milliseconds, independent to the search phrase. But in general I allow the user to enter a search word in a dedicated form field (Windows computers), the search starts after entering of each additional character. That means: Entering the word "installation" the search starts after entering the character "i". Then, after entering the second character "n" the search field is "in" and starts again. Then the user enters "s" and the search is interrupted and starts again with search word "ins". To be able to find not only table entries containing "i", "in", "ins", "inst", ... there is automatically added the character "*" - in fact the search phrase is "i*", "in*", "ins"*, "inst*", ... Now it's very interesting that a search phrase containing at least 4 characters causes a search time of max. some seconds. But as some parts in our stock are having short names like "P7" or "E5", sometimes the search phrase is only 2 characters long plus additional "*". I found out that some character combinations are causing longer search durations than others. The search of "E5*" f.e takes 2,7 seconds and is finding 24419 entries. But if the search f.e. is "F1*" the search takes around 1128,5 seconds to find 77652 entries. Other search phrases "vi*" are taking 3,7s for 14803 entries or "ta*" takes 42,8s for 102189 entries. A "very good" example is "tm*" with 0,2s for entries. It's clear to me that the search time in some cases takes longer as in other cases. Especially I would expect that the search takes as longer as the amount of found entries is bigger. But is there anyone who could explain me, why "F1*" takes 1128,5 seconds search time? Or "F3*" takes 202,8s? What is the reason for such a long duration? In my opinion a short search phrase with "*" should be very fast. By the way, an other example: The same table with same entries is existing in a standard SQLITE database, without using FTS3. The search of every phrase, independent to length of the phrase, takes max. 55 seconds only (after first call; every further search is performed in max. 10 seconds as the table seems to be in cache then). Unfortunately this standard search is finding every entry containing the search phrase, not only the rows where the word is beginning with the search phrase. The goal - and reason for my question - is: I tried to use fulltext search as it should be faster than the standard SQLITE search method, and in fact it is faster if the search phrase is long enough. But with short search phrases the FTS3 extension seems to run in troubles. Is there any possibility to solve this behaviour? Today I tried every possible search phrase combination with 2 characters only, noticed the search time and decided to use FTS search only in case the search will likely take less than 30 seconds. As soon as the search phrase will take longer than 30 seconds I use the standard SQLITE3 search algorithm. That's a workaround for today, but I consider someone being here who could improve the algorithm behaviour of FTS3? By the way, as I wrote above, the "referenzcode" entry contains mostly numbers with only some other characters. FTS3 search with digits only like "13*" or "76*" takes always more than 120 seconds, so the use of FTS 3 is never possible in that case. It makes only sense to use FTS3 if the search phrase with digits is at least 4 characters long (f.e. "1403*"). Would be interesting to read your comments about that... Many thanks! Best regards Ingo - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite:Deletion in Joins method
Sreedhar.a wrote: CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text); CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text); CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE CASCADE,Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES ARTIST(ArtistId) ON DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT fk_Bgm_id REFERENCES BGM(BgmId)ON DELETE CASCADE ); I created a trigger as follows . Here I am checking after deleting that record in Music Table I will check wheather that Artist_id is present now in that table MUSIC i.e, (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id).And if that Artist_Id is Null then I will delete it in the ARTIST table. But this is not happening with the below trigger. Do I need to add more constraints in the below trigger. Please help to solve this. "CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN SELECT CASE WHEN (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id) IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id' END; END;" You need to use a conditional delete in your trigger. You can't do that using a select with a case statement. You could try something like this (untested): CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN delete from Artist where ArtistId = old.ArtistId and not exist (select id from music where ArtistId = old.ArtistId); delete from Album where AlbumId = old.AlbumId and not exist (select id from music where AlbumId = old.AlbumId); delete from BGM where BgmId = old.BgmId and not exist (select id from music where BgmId = old.BgmId); END;" A couple of other things to note: You should probably change your table definitions to key the phrase "INTEGER PRIMARY KEY" together so that sqlite can use its btree key optimization. Instead of this: CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); use this: CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text); Also, if you want to speed up the searches in the deletes trigger above, at the expense of slowing down all the insert and delete operations into the tables, you could add indexes on the individual Id columns in the music table. create index MusicArtistId on Muisc(ArtistId); create index MusicAlbumId on Muisc(AlbumId); create index MusicBgmId on Muisc(BgmId); HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache resizing problem.
Dan wrote: This is failing because the internal representation of the database schema used by the first connection has not yet been updated to include the changes made in step 4 by the second connection. After the sqlite3_prepare() in step 6 fails with the "no such table" error, SQLite realises that it may be using an old schema version and discards it. The new schema will be loaded fresh from the database next time a call is made to sqlite3_prepare(). So the easiest fix is just to retry the sqlite3_prepare(). Dan, Doesn't sqlite do this automatically now if the OP were to switch to the new sqlite3_prepare_v2 API instead of using the sqlite3_prepare API? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
On Dec 4, 2007, at 10:35 PM, Joe Wilson wrote: --- Dan <[EMAIL PROTECTED]> wrote: i.e., if we have: CREATE TABLE x1(a, b, c); CREATE TABLE x2(a, b, c); then the following pairs of statements are equivalent: ... SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2; Don't you mean ORDER BY 1? I'm talking about sqlite cvs, as the code is implemented right now (see matchOrderbyToColumn() in select.c). So 2 is correct, as the test you did shows. The "b" in the ORDER BY does not match "x1.b" because it is not a simple identifier (according to matchOrderbyToColumn()). It does not match either "" or " as ". After failing to find a match for "b" in the leftmost SELECT, SQLite searches the next leftmost and matches "b" to "b" (column 2). That's how it is at the moment, anyhow. http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html Cheers. I'm starting to realise why this little corner of sqlite is the way it is... Dan. __ __ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http:// mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Getting values by column name
Jon Drnek wrote: Is there a way that I can find out the number of a given column name in the query? Instead of ts.key = sqlite3_column_int(preparedGet,0); I'd like to do something like: ts.key = sqlite3_column_int(preparedGet,get_column_id(preparedGet,"id")); Is there a way to do that? Jon, I don't believe there is any API function to do that for columns (but you can do it for bound parameters). You would have to build your own map from the name to the index number after preparing the statement. You can use the sqlite3_column_count function to get the number of columns, and then loop calling sqlite3_column_name to get al the names once. If you save those names along with the associated index, you can then use that table (or an STL map if you are using C++) to translate from a column name back to its column index for use with the other sqlite3_column_* API functions. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache resizing problem.
On Dec 4, 2007, at 11:47 PM, Dennis Cote wrote: Dan wrote: This is failing because the internal representation of the database schema used by the first connection has not yet been updated to include the changes made in step 4 by the second connection. After the sqlite3_prepare() in step 6 fails with the "no such table" error, SQLite realises that it may be using an old schema version and discards it. The new schema will be loaded fresh from the database next time a call is made to sqlite3_prepare(). So the easiest fix is just to retry the sqlite3_prepare(). Doesn't sqlite do this automatically now if the OP were to switch to the new sqlite3_prepare_v2 API instead of using the sqlite3_prepare API? No. sqlite3_prepare_v2() behaves the same way in this case. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Allocating Memory For A Pool Of Database Connections
Hi there, I am having a minor issue when trying to malloc() memory for a list of database connections. I need to keep open a large number of database files and run queries against them. The application is running on Linux and is a synchronuous, single threaded application. In order to keep all these database connections open, I defined an array (simple list) but when I am trying to allocate memory for sqlite3 type I get an error complaining about sizeof(sqlite3). I can't keep defining variables for each database open because there might be too many and I don't know in advance the database name, just its structure. Here is what I am doing: struct db_cache_type { sqlite3 *db; /* database file pointer */ char *key; /* key identifies the database that was opened */ int mode; /* database open mode */ }; struct db_cache_type **db_cache_list; /* list of database connections */ int db_cache_count; /* number of databases in pool */ /* allocate memory for array */ db_cache_list = (struct db_cache_type **)malloc(1 * sizeof(struct db_cache_type *)); /* __ ERROR ON NEXT LINE at sizeof() ___*/ db_cache_list[i] = (struct sqlite3 *)malloc(db_cache_count * sizeof(struct sqlite3)); db_cache_list[i]->db = db; Any help on how to create an array of sqlite3 database connections would be greatly appreciated. Thanks in advance. David Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs
Re: [sqlite] Allocating Memory For A Pool Of Database Connections
David Gelt <[EMAIL PROTECTED]> wrote: > > The application is ... a synchronuous, single threaded application. Yes! Way to go! > > In order to keep all these database connections open, I defined > an array (simple list) but when I am trying to allocate memory > for sqlite3 type I get an error complaining about sizeof(sqlite3). You'll be storing a pointer to the connection, so you want to use sizeof(sqlite3*); -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Allocating Memory For A Pool Of Database Connections
David, You have some pointer issues going on! Maybe something like this struct db_cache_type { sqlite3 *db; /* database file pointer */ char *key; /* key identifies the database that was opened */ int mode; /* database open mode */ }; struct db_cache_type **db_cache_list; /* list of database connections */ int db_cache_count = 5; /* number of databases in pool */ /* allocate memory for array */ db_cache_list = (struct db_cache_type *)malloc(db_cache_count * sizeof(struct db_cache_type *)); /* aloocate each element pointer */ for (i= 0; i < db_cache_count; i ++) / DO NOT type cast malloc / db_cache_list[i] = malloc(sizeof(struct db_cache_type)); sqlite3_open_v2( db_cach_list->key, &db_cache_list[i]->db, flags, NULL); == SImpler method, dont use ** on the struct = struct db_cache_type { sqlite3 *db; /* database file pointer */ char *key; /* key identifies the database that was opened */ int mode; /* database open mode */ }; struct db_cache_type *db_cache_list; /* list of database connections */ int db_cache_count = 5; /* number of databases in pool */ /* allocate memory for array */ db_cache_list = malloc(db_cache_count * sizeof(struct db_cache_type)); memset(db_cach_list, 0 , sizeof( db_cache_count * sizeof(struct db_cache_type) ); Use is also simpler: sqlite3_open_v2( db_cach_list[i].key, &db_cache_list[i].db, flags, NULL); David Gelt <[EMAIL PROTECTED]> wrote: Hi there, I am having a minor issue when trying to malloc() memory for a list of database connections. I need to keep open a large number of database files and run queries against them. The application is running on Linux and is a synchronuous, single threaded application. In order to keep all these database connections open, I defined an array (simple list) but when I am trying to allocate memory for sqlite3 type I get an error complaining about sizeof(sqlite3). I can't keep defining variables for each database open because there might be too many and I don't know in advance the database name, just its structure. Here is what I am doing: struct db_cache_type { sqlite3 *db; /* database file pointer */ char *key; /* key identifies the database that was opened */ int mode; /* database open mode */ }; struct db_cache_type **db_cache_list; /* list of database connections */ int db_cache_count; /* number of databases in pool */ /* allocate memory for array */ db_cache_list = (struct db_cache_type **)malloc(1 * sizeof(struct db_cache_type *)); /* __ ERROR ON NEXT LINE at sizeof() ___*/ db_cache_list[i] = (struct sqlite3 *)malloc(db_cache_count * sizeof(struct sqlite3)); db_cache_list[i]->db = db; Any help on how to create an array of sqlite3 database connections would be greatly appreciated. Thanks in advance. David Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs
Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
--- Dan <[EMAIL PROTECTED]> wrote: > The "b" in the ORDER BY does not match "x1.b" because it is > not a simple identifier (according to matchOrderbyToColumn()). > It does not match either "" or " as ". > > After failing to find a match for "b" in the leftmost SELECT, > SQLite searches the next leftmost and matches "b" to "b" > (column 2). > > That's how it is at the moment, anyhow. > > > > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html > > > > Cheers. I'm starting to realise why this little corner of sqlite > is the way it is... I believe that there are 2 different issues with the current implementation: 1. The result set column names of a compound SELECT should drop all table qualifiers, as they've lost all meaning once in a UNION. i.e., instead of: sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2; x1.b|a value|value you should see: b|a value|value as other databases do: mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY b; +--+--+ | b| a| +--+--+ |2 |1 | |9 |0 | +--+--+ mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY x1.b; ERROR 1054 (42S22): Unknown column 'x1.b' in 'order clause' 2. The compound SELECT's ORDER BY statement elements should only be matched against the leftmost SELECT. If there is no match in the leftmost SELECT, then an error should result - even if a match could potentially be found in non-leftmost SELECTs. Or do you disagree? 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] -
Re: [sqlite] ORDER BY Performance on 30,000 records
Thanks, now it takes only few ms : ) - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: Sent: Sunday, December 02, 2007 7:52 PM Subject: Re: [sqlite] ORDER BY Performance on 30,000 records On Dec 2, 2007, at 12:01 PM, Ofir Neuman wrote: Hi All, I have some performance problem when adding ORDER BY to my query, hope you can help me speed things up. This is my table: TABLE1 { ID TEXT ParentID TEXT ModifiedDate INTEGER } ID is the PK of the table and i also have an index on ParentID. Drop the index on ParentID and replace it with this: CREATE INDEX idx2 ON table1(ParentID, ModifiedDate, ID); Then queries of the form SELECT id FROM table1 WHERE parentid=? ORDER BY modifieddate; will be very fast. Current number of records in table: 40,000 My query is very simple: SELECT ID FROM Table1 WHERE ParentID = '{---}' ORDER BY ModifiedDate According to the data in my table this query should return 30,000 records. While using ORDER BY it takes 3-4 SEC to retrieve the query result, without the ORDER BY it take something like 30 ms. Tried to index also 'ModifiedDate' but it didn't help. What am i doing wrong? Thanks, Ofir Neuman. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] fts table insert performance
Sorry for the delayed response. Was waiting for time to dig into this a little. Nov 18, 2007 2:05 AM Wang Yun <[EMAIL PROTECTED]>: > I insert rfc txt files into a full text search table, 4119 txt files are > 188MB totally. After insert, database file is 443MB. > Logic is below, it's not the real code. I've attached a tcl script I used in experimenting with this. I'm doing this on a Redhat 9 box (kernel 2.4.22 with some additional patches), running on a local disk both for input and the database. The version of rfc I'm using has 4756 files with 252M of data. I compiled tclsqlite3 with flags: -O6 -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DSQLITE_CORE=1 -DSQLITE_ENABLE_FTS1=1 -DSQLITE_ENABLE_BROKEN_FTS1=1 -DSQLITE_ENABLE_FTS2=1 -DSQLITE_ENABLE_BROKEN_FTS2=1 -DSQLITE_ENABLE_FTS3=1 -DHAVE_DLOPEN=1 [Listing all this detail because various things can cause various differences. The most-relevant parameters are probably -O6 -DNDEBUG=1.] > This cost 154 seconds, I use fts2 and my PC is Intel 2.33GHz, 2 CPUs. > If I don't use fts, just insert into normal table, will cost 11 seconds. Your results are a bit worse than I'd expect. I get 6.2s for the simple-table case, and about 45s for the fts3 case, not quite your 14x slow-down. fts2 and fts3 should be nearly identical for this kind of test. Basic table, one transaction: 2.190u 1.060s 0:06.20 52.4% 0+0k 0+0io 393pf+0w -rw-r--r-- 1 shess eng 239505408 Dec 4 11:15 dbs/baseline.db Basic table, per-insert transaction: 3.280u 1.890s 0:34.19 15.1% 0+0k 0+0io 393pf+0w -rw-r--r-- 1 shess eng 239505408 Dec 4 11:16 dbs/baseline.db fts3, one transaction: 40.820u 1.550s 0:44.44 95.3%0+0k 0+0io 414pf+0w -rw-r--r-- 1 shess eng 307900416 Dec 4 11:17 dbs/baseline.db fts3, per-insert transaction: 61.300u 3.330s 1:43.04 62.7%0+0k 0+0io 414pf+0w -rw-r--r-- 1 shess eng 348348416 Dec 4 11:21 dbs/baseline.db I'm not seeing as much bloat as you describe - perhaps I'm using a bigger page size. > I don't know when sqlite will update the full text index, after each insert? fts2/3 update the index after each transaction, and also before each insert where an explicit rowid is less than the maximum rowid seen in the current transaction, and also before any query run against the table. In this case, it should only be updating at the end of the transaction, and as needed when the in-memory table fills up. > How can I improve the performance? The optimal case for fts2/3 is to do many inserts per transaction, letting the table select rowid/docid. In that case it will collect the new data in memory and flush it to disk less frequently. For a test of this size, it might also help to have a bigger page cache so that SQLite doesn't have to flush the journal file to disk. You could define kPendingThreshold in the fts3.c (or fts2.c) source code to be larger (this doesn't seem to help me at all, though). Without going in and profiling things, though, my guess is that the time in this test is dominated by tokenization. I'm basing this on the high CPU utilization, and the lack of impact from tweaking kPendingThreshold and pragma cache_size. Possibly there's also some cost from segment merges, though those should generally be fairly I/O dominated. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] search time in FTS3 tables sometimes very long
2007/12/4 Ingo Godau-Gellert <[EMAIL PROTECTED]>: > What is really strange is that FTS3 search phrases like > SELECT referenzcode FROM volltext where volltext match ('installation > manual') are performed really fast within some milliseconds, independent > to the search phrase. > But in general I allow the user to enter a search word in a dedicated > form field (Windows computers), the search starts after entering of each > additional character. > > That means: > Entering the word "installation" the search starts after entering the > character "i". Then, after entering the second character "n" the search > field is "in" and starts again. Then the user enters "s" and the search > is interrupted and starts again with search word "ins". > > To be able to find not only table entries containing "i", "in", "ins", > "inst", ... there is automatically added the character "*" - in fact the > search phrase is "i*", "in*", "ins"*, "inst*", ... > > Now it's very interesting that a search phrase containing at least 4 > characters causes a search time of max. some seconds. In general, the more specific the term is, the faster the search will be. Very unlikely terms will be faster than common terms. If you do a search for 'installation', then fts can dig directly down to the information for 'installation' and serve it up. But if you search for 'i*', it's as if you're searching for all terms starting with 'i', so fts has to find all those terms and merge their information together. This is always going to be slower than a query for a specific term within that range of terms. The specific reasons why you're seeing slow performance, and the specific things you see slow performance on, are very dependent on the data in your system. In general, more is slower than less. So in cases where you have a large number of results, it will generally be slower. > The search of "E5*" f.e takes 2,7 seconds and is finding 24419 entries. > But if the search f.e. is "F1*" the search takes around 1128,5 seconds > to find 77652 entries. This seems a little excessive, though. I do see that there's an O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to docListUnion()). I can reasonably make that O(logN), which might help a great deal, if you're hitting it. Not really sure how to tell if you're hitting it, but I'll experiment at my end and see whether I can improve things there. > It's clear to me that the search time in some cases takes longer as in > other cases. Especially I would expect that the search takes as longer > as the amount of found entries is bigger. > > But is there anyone who could explain me, why "F1*" takes 1128,5 seconds > search time? Or "F3*" takes 202,8s? What is the reason for such a long > duration? If there are a lot of hits distributed across a few terms, that might be much faster than fewer hits distributed across a large number of terms. > In my opinion a short search phrase with "*" should be very fast. :-). Prefix searches will likely always be slower than specific searches, because they're essentially implemented as a set of specific searches OR'ed together. Better than that, of course, but still strictly slower than a single specific search. > Is there any possibility to solve this behaviour? Today I tried every > possible search phrase combination with 2 characters only, noticed the > search time and decided to use FTS search only in case the search will > likely take less than 30 seconds. As soon as the search phrase will take > longer than 30 seconds I use the standard SQLITE3 search algorithm. > That's a workaround for today, but I consider someone being here who > could improve the algorithm behaviour of FTS3? SQLite has a function sqlite3_interrupt() which you can use to interrupt statements. Since fts3 is implemented in terms of SQLite, you _should_ be able to call sqlite3_interrupt() to cause it to stop executing. If your code is asynchronous, then you could start the query in the background, and call sqlite3_interrupt() after awhile to stop execution. There are a couple different things I've considered for fts to implement to help out with this case. One thing would be a "limit" feature, so you could say something like: SELECT rowid FROM t WHERE t MATCH 'a* limit:10'; Why this is helpful is that it tells fts that it only needs to generate 10 hits, which it might be able to optimize to be very fast. Another thing I've considered is to add some sort of prefix-specific index to the system. I'm not even clear what this would mean, so I'm not going to describe it :-). But it would probably be something in the CREATE statement which indicated that you expected to do prefix queries and wanted fts to keep additional information to make those fast. OR, it might be a completely distinct table which only keeps an index to make prefix-searching fast. One reason you might find full-table-scans to sometimes be faster than fts is if fts ends up essentially looking at
[sqlite] SQLite is in Android
Surely I'm not the first person to notice that SQLite is part of Google's open-source Android platform for mobile phones. This is a *huge* win for SQLite ... - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite is in Android
Richard Klein <[EMAIL PROTECTED]> wrote: > Surely I'm not the first person to notice that > SQLite is part of Google's open-source Android > platform for mobile phones. > I've had Android listed at http://www.sqlite.org/famous.html since the day it was announced. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] search time in FTS3 tables sometimes very long
2007/12/4 Scott Hess <[EMAIL PROTECTED]>: > This seems a little excessive, though. I do see that there's an > O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to > docListUnion()). I can reasonably make that O(logN), which might help > a great deal, if you're hitting it. Not really sure how to tell if > you're hitting it, but I'll experiment at my end and see whether I can > improve things there. With the attached patch, the time to match against 't*' with the rfc dataset goes from 1m16s to 5s. It passes the tests, but I'll not guarantee that this is what I'll check in. I want to think on it. But let me know if this doesn't help. -scott Index: ext/fts3/fts3.c === RCS file: /sqlite/sqlite/ext/fts3/fts3.c,v retrieving revision 1.12 diff -u -r1.12 fts3.c --- ext/fts3/fts3.c 24 Nov 2007 00:41:52 - 1.12 +++ ext/fts3/fts3.c 5 Dec 2007 01:03:32 - @@ -,6 +,21 @@ return rc; } +/* Call docListUnion() to merge *left and *right into *out, destroying +** *left and *right. Handles left or right in the same location as +** out (in-place merge). +*/ +static void docListUnionWithDestroy(DataBuffer *left, DataBuffer *right, +DataBuffer *out) { + DataBuffer result; + dataBufferInit(&result, left->nData+right->nData); + docListUnion(left->pData, left->nData, right->pData, right->nData, + &result); + dataBufferDestroy(left); + dataBufferDestroy(right); + *out = result; +} + /* Scan pReader for pTerm/nTerm, and merge the term's doclist over ** *out (any doclists with duplicate docids overwrite those in *out). ** Internal function for loadSegmentLeaf(). @@ -5562,6 +5577,15 @@ static int loadSegmentLeavesInt(fulltext_vtab *v, LeavesReader *pReader, const char *pTerm, int nTerm, int isPrefix, DataBuffer *out){ + /* To keep merging O(logN), keep a list of merged buffers. After + ** each doclist is added, buffers are merged for the number of 0 + ** low-order bits in nDoclists. So this many doclists are merged at + ** each point: 0, 1, 0, 2, 0, 1, 0, 4, 0, 1, 0, 2, ... (like a + ** tree). + */ + DataBuffer *pBuffers = NULL; + int nBuffers = 0, nMaxBuffers = 0, nDoclists = 0; + assert( nTerm>0 ); /* Process while the prefix matches. */ @@ -5575,24 +5599,54 @@ int c = leafReaderTermCmp(&pReader->leafReader, pTerm, nTerm, isPrefix); if( c==0 ){ const char *pData = leavesReaderData(pReader); - int nData = leavesReaderDataBytes(pReader); - if( out->nData==0 ){ -dataBufferReplace(out, pData, nData); - }else{ -DataBuffer result; -dataBufferInit(&result, out->nData+nData); -docListUnion(out->pData, out->nData, pData, nData, &result); -dataBufferDestroy(out); -*out = result; -/* TODO(shess) Rather than destroy out, we could retain it for -** later reuse. -*/ + int n, nData = leavesReaderDataBytes(pReader); + if( nBuffers==nMaxBuffers ){ +++nMaxBuffers; +pBuffers = sqlite3_realloc(pBuffers, nMaxBuffers*sizeof(*pBuffers)); + } + dataBufferInit(&(pBuffers[nBuffers]), nData); + dataBufferReplace(&(pBuffers[nBuffers]), pData, nData); + nBuffers++; + assert(nBuffers<=nMaxBuffers); + + ++nDoclists; + for( n=nDoclists; (n%2)==0; n>>=1) { +assert(n); /* Can't happen if nDoclists!=0. */ +docListUnionWithDestroy(&(pBuffers[nBuffers-2]), +&(pBuffers[nBuffers-1]), +&(pBuffers[nBuffers-2])); +nBuffers--; } } if( c>0 ) break; /* Past any possible matches. */ rc = leavesReaderStep(v, pReader); -if( rc!=SQLITE_OK ) return rc; +if( rc!=SQLITE_OK ){ + while( nBuffers>0 ){ +nBuffers--; +dataBufferDestroy(&(pBuffers[nBuffers])); + } + sqlite3_free(pBuffers); + return rc; +} + } + while( nBuffers>1 ){ +docListUnionWithDestroy(&(pBuffers[nBuffers-2]), +&(pBuffers[nBuffers-1]), +&(pBuffers[nBuffers-2])); +nBuffers--; + } + if( nBuffers>0 ){ +assert(1==nBuffers); +if( out->nData==0 ){ + dataBufferDestroy(out); + *out = pBuffers[0]; +}else{ + docListUnionWithDestroy(out, &(pBuffers[0]), out); +} +sqlite3_free(pBuffers); + } else { +assert(NULL==pBuffers); } return SQLITE_OK; } - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite is in Android
On 12/4/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Richard Klein <[EMAIL PROTECTED]> wrote: > > Surely I'm not the first person to notice that > > SQLite is part of Google's open-source Android > > platform for mobile phones. > > > > I've had Android listed at http://www.sqlite.org/famous.html > since the day it was announced. > I was curious... isn't Apple's use of SQLite a lot more extensive than listed on your page? I believe they use it for file metadata (used in Spotlight searches), for Time Machine, and the fact that they provide the option for storing data in SQLite format as part of their Core Data framework. It is really almost built into the operating system in a manner of speaking. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] File Is Encrypted Or Is Not a Database
I have a sqlite db file. I have tried to open it with sqlite v 3.4.1, 2.8.17 and 3.3.5 and each of these versions give me the same error "File Is Encrypted Or Is Not a Database". How can I determine what version of sqlite will open this db? The perl application can open the database using the perl module it has. But I cant get command line access to the db. Any ideas? Thanks Mark
[sqlite] sqlite locking mechanism with threads
I work with sqlite 3.5.3. I experimented with threads and I get a strange behavior. it looks like one thread takes over sqlite and does not leave it until it is finished even though I tell it to wait (using random after). This is the script I use: package require sqlite3 package require Thread sqlite3 conn test catch {conn eval "drop table a"} conn eval "create table a (b varchar(10))" set t1 [thread::create] set t2 [thread::create] set b { package require sqlite3 sqlite3 conn test conn timeout 10 for {set i 0} {$i<10} {incr i} { puts $i-[thread::id] conn eval "insert into a values ($i-[thread::id])" £ wait for randomal time so other thread might take over sqlite after [expr {int(rand()*100)}] } thread::exit } thread:::send -async $t1 $b thread:::send -async $t2 $b thread::wait the result is: 0-65539 0-81924 1-65539 2-65539 3-65539 4-65539 5-65539 6-65539 7-65539 8-65539 9-65539 1-81924 2-81924 3-81924 4-81924 5-81924 6-81924 7-81924 8-81924 9-81924 why in the start both threads get access? why after it only thread 1 access it and thread 2 starts only when thread 1 finishes. the behavior changes if I increate the after but it looks like sqlite does not retry for a long time after it meets a lock. Yahalom Emet XPO Team Xor Financial Solutions Email: mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] www.XorTechnologies.com http://www.xortechnologies.com/>
RE: [sqlite] File Is Encrypted Or Is Not a Database
I have also tried v 3.5.3 now and still cannot open the database. I guess I need the right version of sqlite3 so that I can dump to sql and then I can rebuild in a newer version. But how do I find our what version of sqlite I need? > -Original Message- > From: Mark Easton [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 5 December 2007 3:58 p.m. > To: sqlite-users@sqlite.org > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > I have a sqlite db file. I have tried to open it with sqlite > v 3.4.1, 2.8.17 and 3.3.5 and each of these versions give me > the same error "File Is Encrypted Or Is Not a Database". How > can I determine what version of sqlite will open this db? The > perl application can open the database using the perl module > it has. But I cant get command line access to the db. Any ideas? > > Thanks > Mark > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] File Is Encrypted Or Is Not a Database
On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote: > I have also tried v 3.5.3 now and still cannot open the database. I guess I > need the right version of sqlite3 so that I can dump to sql and then I can > rebuild in a newer version. But how do I find our what version of sqlite I > need? > > > > -Original Message- > > From: Mark Easton [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, 5 December 2007 3:58 p.m. > > To: sqlite-users@sqlite.org > > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > > > I have a sqlite db file. I have tried to open it with sqlite > > v 3.4.1, 2.8.17 and 3.3.5 and each of these versions give me > > the same error "File Is Encrypted Or Is Not a Database". How > > can I determine what version of sqlite will open this db? The > > perl application can open the database using the perl module > > it has. But I cant get command line access to the db. Any ideas? > > If you can open the database with Perl then it is not encrypted. Check the version of DBD::SQLite you have installed. Its docs will tell what version of SQLite it has compiled in. Then, are you sure you are trying to open it with the right versioned SQLite? Try $ which sqlite3 to find out if you are inadvertently picking up an old SQLite program installed somewhere in your path. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite locking mechanism with threads
99.9% certain this is not a sqlite issue. But a script language issue. It depends on how the script language implements threads. Try writing this in C and using posix threads. You'll also need to have multiple cpu's to really take advantage of threading as well. yahalome <[EMAIL PROTECTED]> wrote: I work with sqlite 3.5.3. I experimented with threads and I get a strange behavior. it looks like one thread takes over sqlite and does not leave it until it is finished even though I tell it to wait (using random after). This is the script I use: package require sqlite3 package require Thread sqlite3 conn test catch {conn eval "drop table a"} conn eval "create table a (b varchar(10))" set t1 [thread::create] set t2 [thread::create] set b { package require sqlite3 sqlite3 conn test conn timeout 10 for {set i 0} {$i<10} {incr i} { puts $i-[thread::id] conn eval "insert into a values ($i-[thread::id])" £ wait for randomal time so other thread might take over sqlite after [expr {int(rand()*100)}] } thread::exit } thread:::send -async $t1 $b thread:::send -async $t2 $b thread::wait the result is: 0-65539 0-81924 1-65539 2-65539 3-65539 4-65539 5-65539 6-65539 7-65539 8-65539 9-65539 1-81924 2-81924 3-81924 4-81924 5-81924 6-81924 7-81924 8-81924 9-81924 why in the start both threads get access? why after it only thread 1 access it and thread 2 starts only when thread 1 finishes. the behavior changes if I increate the after but it looks like sqlite does not retry for a long time after it meets a lock. Yahalom Emet XPO Team Xor Financial Solutions Email: [EMAIL PROTECTED] www.XorTechnologies.com
RE: [sqlite] File Is Encrypted Or Is Not a Database
Sorry, but how do I check the version of DBD::SQLite I have installed? Perl is not my thing. When I look in the Perl code I can see DBIx::SimplePerl commands. I ran the scipt below but it did not show any installed modules. "#!/usr/bin/perl use CPAN; printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN"); foreach $a (@ARGV) { foreach $mod (CPAN::Shell->expand("Module", $a)){ printf("%-20s %10s %10s %s\n", $mod->id, $mod->inst_version eq "undef" || !defined($mod->inst_version) ? "-" : $mod->inst_version, $mod->cpan_version eq "undef" || !defined($mod->cpan_version) ? "-" : $mod->cpan_version, $mod->uptodate ? "" : "*" ); } }" Thanks > -Original Message- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 5 December 2007 5:44 p.m. > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote: > > I have also tried v 3.5.3 now and still cannot open the database. I > > guess I need the right version of sqlite3 so that I can dump to sql > > and then I can rebuild in a newer version. But how do I > find our what > > version of sqlite I need? > > > > > > > -Original Message- > > > From: Mark Easton [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, 5 December 2007 3:58 p.m. > > > To: sqlite-users@sqlite.org > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > I have a sqlite db file. I have tried to open it with sqlite v > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give > me the same > > > error "File Is Encrypted Or Is Not a Database". How can I > determine > > > what version of sqlite will open this db? The perl > application can > > > open the database using the perl module it has. But I cant get > > > command line access to the db. Any ideas? > > > > > > If you can open the database with Perl then it is not > encrypted. Check the version of DBD::SQLite you have > installed. Its docs will tell what version of SQLite it has > compiled in. > > Then, are you sure you are trying to open it with the right > versioned SQLite? Try > > $ which sqlite3 > > to find out if you are inadvertently picking up an old SQLite > program installed somewhere in your path. > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] File Is Encrypted Or Is Not a Database
I am sure on the versions of sqlite I have been using. When yoy run them it always shows the version of sqlite you are running. This is very very frustrating. I have a sqlite db which the Perl app is opening fine using DBIx::SimplePerl, yet U cannnot open the db from command line. It seems more than a little strange that I cannot determine the version of a sqlite db. > -Original Message- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 5 December 2007 5:44 p.m. > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote: > > I have also tried v 3.5.3 now and still cannot open the database. I > > guess I need the right version of sqlite3 so that I can dump to sql > > and then I can rebuild in a newer version. But how do I > find our what > > version of sqlite I need? > > > > > > > -Original Message- > > > From: Mark Easton [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, 5 December 2007 3:58 p.m. > > > To: sqlite-users@sqlite.org > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > I have a sqlite db file. I have tried to open it with sqlite v > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give > me the same > > > error "File Is Encrypted Or Is Not a Database". How can I > determine > > > what version of sqlite will open this db? The perl > application can > > > open the database using the perl module it has. But I cant get > > > command line access to the db. Any ideas? > > > > > > If you can open the database with Perl then it is not > encrypted. Check the version of DBD::SQLite you have > installed. Its docs will tell what version of SQLite it has > compiled in. > > Then, are you sure you are trying to open it with the right > versioned SQLite? Try > > $ which sqlite3 > > to find out if you are inadvertently picking up an old SQLite > program installed somewhere in your path. > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] File Is Encrypted Or Is Not a Database
On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote: > Sorry, but how do I check the version of DBD::SQLite I have installed? Perl > is not my thing. When I look in the Perl code I can see DBIx::SimplePerl > commands. I ran the scipt below but it did not show any installed modules. Use cpan. As you can see below, I have DBD::SQLite version 1.14 installed. That uses SQLite 3.4.2 (you can check the change log on CPAN). $ cpan CPAN: File::HomeDir loaded ok (v0.58) cpan shell -- CPAN exploration and modules installation (v1.9205) ReadLine support enabled cpan[1]> i DBD::SQLite CPAN: Storable loaded ok (v2.15) Going to read /Users/punkish/.cpan/Metadata Database was generated on Tue, 04 Dec 2007 23:37:19 GMT Strange distribution name [DBD::SQLite] Module id = DBD::SQLite CPAN_USERID MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>) CPAN_VERSION 1.14 CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz UPLOAD_DATE 2007-09-19 MANPAGE DBD::SQLite - Self Contained RDBMS in a DBI Driver INST_FILE /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm INST_VERSION 1.14 cpan[2]> > > "#!/usr/bin/perl > > use CPAN; > > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN"); > > foreach $a (@ARGV) { > foreach $mod (CPAN::Shell->expand("Module", $a)){ > printf("%-20s %10s %10s %s\n", > $mod->id, > $mod->inst_version eq "undef" || !defined($mod->inst_version) > ? "-" : $mod->inst_version, > $mod->cpan_version eq "undef" || !defined($mod->cpan_version) > ? "-" : $mod->cpan_version, > $mod->uptodate ? "" : "*" > ); > } > }" > > > > Thanks > > > > > -Original Message- > > From: P Kishor [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, 5 December 2007 5:44 p.m. > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote: > > > I have also tried v 3.5.3 now and still cannot open the database. I > > > guess I need the right version of sqlite3 so that I can dump to sql > > > and then I can rebuild in a newer version. But how do I > > find our what > > > version of sqlite I need? > > > > > > > > > > -Original Message- > > > > From: Mark Easton [mailto:[EMAIL PROTECTED] > > > > Sent: Wednesday, 5 December 2007 3:58 p.m. > > > > To: sqlite-users@sqlite.org > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > > > I have a sqlite db file. I have tried to open it with sqlite v > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give > > me the same > > > > error "File Is Encrypted Or Is Not a Database". How can I > > determine > > > > what version of sqlite will open this db? The perl > > application can > > > > open the database using the perl module it has. But I cant get > > > > command line access to the db. Any ideas? > > > > > > > > > > If you can open the database with Perl then it is not > > encrypted. Check the version of DBD::SQLite you have > > installed. Its docs will tell what version of SQLite it has > > compiled in. > > > > Then, are you sure you are trying to open it with the right > > versioned SQLite? Try > > > > $ which sqlite3 > > > > to find out if you are inadvertently picking up an old SQLite > > program installed somewhere in your path. > > > > -- > > > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > -- > > --- > > > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ Summer 2007 S&T Policy Fellow, The National Academies http://www.nas.edu/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] File Is Encrypted Or Is Not a Database
Aahh, ok - I have the same version. Many thanks. I will try that version of sqlite then. Fingers crossed. :) > -Original Message- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 5 December 2007 6:11 p.m. > To: [EMAIL PROTECTED] > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote: > > Sorry, but how do I check the version of DBD::SQLite I have > > installed? Perl is not my thing. When I look in the Perl code I can > > see DBIx::SimplePerl commands. I ran the scipt below but it > did not show any installed modules. > > Use cpan. As you can see below, I have DBD::SQLite version > 1.14 installed. That uses SQLite 3.4.2 (you can check the > change log on CPAN). > > $ cpan > CPAN: File::HomeDir loaded ok (v0.58) > > cpan shell -- CPAN exploration and modules installation > (v1.9205) ReadLine support enabled > > cpan[1]> i DBD::SQLite > CPAN: Storable loaded ok (v2.15) > Going to read /Users/punkish/.cpan/Metadata > Database was generated on Tue, 04 Dec 2007 23:37:19 GMT > Strange distribution name [DBD::SQLite] Module id = DBD::SQLite > CPAN_USERID MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>) > CPAN_VERSION 1.14 > CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz > UPLOAD_DATE 2007-09-19 > MANPAGE DBD::SQLite - Self Contained RDBMS in a DBI Driver > INST_FILE > /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm > INST_VERSION 1.14 > > > cpan[2]> > > > > > > "#!/usr/bin/perl > > > > use CPAN; > > > > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN"); > > > > foreach $a (@ARGV) { > > foreach $mod (CPAN::Shell->expand("Module", $a)){ > > printf("%-20s %10s %10s %s\n", > > $mod->id, > > $mod->inst_version eq "undef" || !defined($mod->inst_version) > > ? "-" : $mod->inst_version, > > $mod->cpan_version eq "undef" || !defined($mod->cpan_version) > > ? "-" : $mod->cpan_version, > > $mod->uptodate ? "" : "*" > > ); > > } > > }" > > > > > > > > Thanks > > > > > > > > > -Original Message- > > > From: P Kishor [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, 5 December 2007 5:44 p.m. > > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote: > > > > I have also tried v 3.5.3 now and still cannot open the > database. > > > > I guess I need the right version of sqlite3 so that I > can dump to > > > > sql and then I can rebuild in a newer version. But how do I > > > find our what > > > > version of sqlite I need? > > > > > > > > > > > > > -Original Message- > > > > > From: Mark Easton [mailto:[EMAIL PROTECTED] > > > > > Sent: Wednesday, 5 December 2007 3:58 p.m. > > > > > To: sqlite-users@sqlite.org > > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > > > > > I have a sqlite db file. I have tried to open it with > sqlite v > > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give > > > me the same > > > > > error "File Is Encrypted Or Is Not a Database". How can I > > > determine > > > > > what version of sqlite will open this db? The perl > > > application can > > > > > open the database using the perl module it has. But I > cant get > > > > > command line access to the db. Any ideas? > > > > > > > > > > > > > > If you can open the database with Perl then it is not encrypted. > > > Check the version of DBD::SQLite you have installed. Its > docs will > > > tell what version of SQLite it has compiled in. > > > > > > Then, are you sure you are trying to open it with the right > > > versioned SQLite? Try > > > > > > $ which sqlite3 > > > > > > to find out if you are inadvertently picking up an old SQLite > > > program installed somewhere in your path. > > > > > > -- > > > > > --- > > > To unsubscribe, send email to [EMAIL PROTECTED] > > > -- > > > --- > > > > > > > > > > > -- > Puneet Kishor > http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies > http://www.nelson.wisc.edu/ Open Source Geospatial Foundation > (OSGeo) http://www.osgeo.org/ Summer 2007 S&T Policy Fellow, > The National Academies http://www.nas.edu/ > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] File Is Encrypted Or Is Not a Database
Sorry ... how do I get sqlite v3.4.2? > -Original Message- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 5 December 2007 6:11 p.m. > To: [EMAIL PROTECTED] > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote: > > Sorry, but how do I check the version of DBD::SQLite I have > > installed? Perl is not my thing. When I look in the Perl code I can > > see DBIx::SimplePerl commands. I ran the scipt below but it > did not show any installed modules. > > Use cpan. As you can see below, I have DBD::SQLite version > 1.14 installed. That uses SQLite 3.4.2 (you can check the > change log on CPAN). > > $ cpan > CPAN: File::HomeDir loaded ok (v0.58) > > cpan shell -- CPAN exploration and modules installation > (v1.9205) ReadLine support enabled > > cpan[1]> i DBD::SQLite > CPAN: Storable loaded ok (v2.15) > Going to read /Users/punkish/.cpan/Metadata > Database was generated on Tue, 04 Dec 2007 23:37:19 GMT > Strange distribution name [DBD::SQLite] Module id = DBD::SQLite > CPAN_USERID MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>) > CPAN_VERSION 1.14 > CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz > UPLOAD_DATE 2007-09-19 > MANPAGE DBD::SQLite - Self Contained RDBMS in a DBI Driver > INST_FILE > /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm > INST_VERSION 1.14 > > > cpan[2]> > > > > > > "#!/usr/bin/perl > > > > use CPAN; > > > > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN"); > > > > foreach $a (@ARGV) { > > foreach $mod (CPAN::Shell->expand("Module", $a)){ > > printf("%-20s %10s %10s %s\n", > > $mod->id, > > $mod->inst_version eq "undef" || !defined($mod->inst_version) > > ? "-" : $mod->inst_version, > > $mod->cpan_version eq "undef" || !defined($mod->cpan_version) > > ? "-" : $mod->cpan_version, > > $mod->uptodate ? "" : "*" > > ); > > } > > }" > > > > > > > > Thanks > > > > > > > > > -Original Message- > > > From: P Kishor [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, 5 December 2007 5:44 p.m. > > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote: > > > > I have also tried v 3.5.3 now and still cannot open the > database. > > > > I guess I need the right version of sqlite3 so that I > can dump to > > > > sql and then I can rebuild in a newer version. But how do I > > > find our what > > > > version of sqlite I need? > > > > > > > > > > > > > -Original Message- > > > > > From: Mark Easton [mailto:[EMAIL PROTECTED] > > > > > Sent: Wednesday, 5 December 2007 3:58 p.m. > > > > > To: sqlite-users@sqlite.org > > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > > > > > I have a sqlite db file. I have tried to open it with > sqlite v > > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give > > > me the same > > > > > error "File Is Encrypted Or Is Not a Database". How can I > > > determine > > > > > what version of sqlite will open this db? The perl > > > application can > > > > > open the database using the perl module it has. But I > cant get > > > > > command line access to the db. Any ideas? > > > > > > > > > > > > > > If you can open the database with Perl then it is not encrypted. > > > Check the version of DBD::SQLite you have installed. Its > docs will > > > tell what version of SQLite it has compiled in. > > > > > > Then, are you sure you are trying to open it with the right > > > versioned SQLite? Try > > > > > > $ which sqlite3 > > > > > > to find out if you are inadvertently picking up an old SQLite > > > program installed somewhere in your path. > > > > > > -- > > > > > --- > > > To unsubscribe, send email to [EMAIL PROTECTED] > > > -- > > > --- > > > > > > > > > > > -- > Puneet Kishor > http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies > http://www.nelson.wisc.edu/ Open Source Geospatial Foundation > (OSGeo) http://www.osgeo.org/ Summer 2007 S&T Policy Fellow, > The National Academies http://www.nas.edu/ > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] File Is Encrypted Or Is Not a Database
The db I am trying to open is attached. Thanks > -Original Message- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 5 December 2007 6:11 p.m. > To: [EMAIL PROTECTED] > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote: > > Sorry, but how do I check the version of DBD::SQLite I have > > installed? Perl is not my thing. When I look in the Perl code I can > > see DBIx::SimplePerl commands. I ran the scipt below but it > did not show any installed modules. > > Use cpan. As you can see below, I have DBD::SQLite version > 1.14 installed. That uses SQLite 3.4.2 (you can check the > change log on CPAN). > > $ cpan > CPAN: File::HomeDir loaded ok (v0.58) > > cpan shell -- CPAN exploration and modules installation > (v1.9205) ReadLine support enabled > > cpan[1]> i DBD::SQLite > CPAN: Storable loaded ok (v2.15) > Going to read /Users/punkish/.cpan/Metadata > Database was generated on Tue, 04 Dec 2007 23:37:19 GMT > Strange distribution name [DBD::SQLite] Module id = DBD::SQLite > CPAN_USERID MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>) > CPAN_VERSION 1.14 > CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz > UPLOAD_DATE 2007-09-19 > MANPAGE DBD::SQLite - Self Contained RDBMS in a DBI Driver > INST_FILE > /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm > INST_VERSION 1.14 > > > cpan[2]> > > > > > > "#!/usr/bin/perl > > > > use CPAN; > > > > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN"); > > > > foreach $a (@ARGV) { > > foreach $mod (CPAN::Shell->expand("Module", $a)){ > > printf("%-20s %10s %10s %s\n", > > $mod->id, > > $mod->inst_version eq "undef" || !defined($mod->inst_version) > > ? "-" : $mod->inst_version, > > $mod->cpan_version eq "undef" || !defined($mod->cpan_version) > > ? "-" : $mod->cpan_version, > > $mod->uptodate ? "" : "*" > > ); > > } > > }" > > > > > > > > Thanks > > > > > > > > > -Original Message- > > > From: P Kishor [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, 5 December 2007 5:44 p.m. > > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote: > > > > I have also tried v 3.5.3 now and still cannot open the > database. > > > > I guess I need the right version of sqlite3 so that I > can dump to > > > > sql and then I can rebuild in a newer version. But how do I > > > find our what > > > > version of sqlite I need? > > > > > > > > > > > > > -Original Message- > > > > > From: Mark Easton [mailto:[EMAIL PROTECTED] > > > > > Sent: Wednesday, 5 December 2007 3:58 p.m. > > > > > To: sqlite-users@sqlite.org > > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > > > > > I have a sqlite db file. I have tried to open it with > sqlite v > > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give > > > me the same > > > > > error "File Is Encrypted Or Is Not a Database". How can I > > > determine > > > > > what version of sqlite will open this db? The perl > > > application can > > > > > open the database using the perl module it has. But I > cant get > > > > > command line access to the db. Any ideas? > > > > > > > > > > > > > > If you can open the database with Perl then it is not encrypted. > > > Check the version of DBD::SQLite you have installed. Its > docs will > > > tell what version of SQLite it has compiled in. > > > > > > Then, are you sure you are trying to open it with the right > > > versioned SQLite? Try > > > > > > $ which sqlite3 > > > > > > to find out if you are inadvertently picking up an old SQLite > > > program installed somewhere in your path. > > > > > > -- > > > > > --- > > > To unsubscribe, send email to [EMAIL PROTECTED] > > > -- > > > --- > > > > > > > > > > > -- > Puneet Kishor > http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies > http://www.nelson.wisc.edu/ Open Source Geospatial Foundation > (OSGeo) http://www.osgeo.org/ Summer 2007 S&T Policy Fellow, > The National Academies http://www.nas.edu/ > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] File Is Encrypted Or Is Not a Database
I apologise. Just discoverd that it is a Berkely db file. I am really sorry to waste your time on this. Many thanks, though, for your assistance. Mark > -Original Message- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 5 December 2007 6:11 p.m. > To: [EMAIL PROTECTED] > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote: > > Sorry, but how do I check the version of DBD::SQLite I have > > installed? Perl is not my thing. When I look in the Perl code I can > > see DBIx::SimplePerl commands. I ran the scipt below but it > did not show any installed modules. > > Use cpan. As you can see below, I have DBD::SQLite version > 1.14 installed. That uses SQLite 3.4.2 (you can check the > change log on CPAN). > > $ cpan > CPAN: File::HomeDir loaded ok (v0.58) > > cpan shell -- CPAN exploration and modules installation > (v1.9205) ReadLine support enabled > > cpan[1]> i DBD::SQLite > CPAN: Storable loaded ok (v2.15) > Going to read /Users/punkish/.cpan/Metadata > Database was generated on Tue, 04 Dec 2007 23:37:19 GMT > Strange distribution name [DBD::SQLite] Module id = DBD::SQLite > CPAN_USERID MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>) > CPAN_VERSION 1.14 > CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz > UPLOAD_DATE 2007-09-19 > MANPAGE DBD::SQLite - Self Contained RDBMS in a DBI Driver > INST_FILE > /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm > INST_VERSION 1.14 > > > cpan[2]> > > > > > > "#!/usr/bin/perl > > > > use CPAN; > > > > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN"); > > > > foreach $a (@ARGV) { > > foreach $mod (CPAN::Shell->expand("Module", $a)){ > > printf("%-20s %10s %10s %s\n", > > $mod->id, > > $mod->inst_version eq "undef" || !defined($mod->inst_version) > > ? "-" : $mod->inst_version, > > $mod->cpan_version eq "undef" || !defined($mod->cpan_version) > > ? "-" : $mod->cpan_version, > > $mod->uptodate ? "" : "*" > > ); > > } > > }" > > > > > > > > Thanks > > > > > > > > > -Original Message- > > > From: P Kishor [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, 5 December 2007 5:44 p.m. > > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote: > > > > I have also tried v 3.5.3 now and still cannot open the > database. > > > > I guess I need the right version of sqlite3 so that I > can dump to > > > > sql and then I can rebuild in a newer version. But how do I > > > find our what > > > > version of sqlite I need? > > > > > > > > > > > > > -Original Message- > > > > > From: Mark Easton [mailto:[EMAIL PROTECTED] > > > > > Sent: Wednesday, 5 December 2007 3:58 p.m. > > > > > To: sqlite-users@sqlite.org > > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database > > > > > > > > > > I have a sqlite db file. I have tried to open it with > sqlite v > > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give > > > me the same > > > > > error "File Is Encrypted Or Is Not a Database". How can I > > > determine > > > > > what version of sqlite will open this db? The perl > > > application can > > > > > open the database using the perl module it has. But I > cant get > > > > > command line access to the db. Any ideas? > > > > > > > > > > > > > > If you can open the database with Perl then it is not encrypted. > > > Check the version of DBD::SQLite you have installed. Its > docs will > > > tell what version of SQLite it has compiled in. > > > > > > Then, are you sure you are trying to open it with the right > > > versioned SQLite? Try > > > > > > $ which sqlite3 > > > > > > to find out if you are inadvertently picking up an old SQLite > > > program installed somewhere in your path. > > > > > > -- > > > > > --- > > > To unsubscribe, send email to [EMAIL PROTECTED] > > > -- > > > --- > > > > > > > > > > > -- > Puneet Kishor > http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies > http://www.nelson.wisc.edu/ Open Source Geospatial Foundation > (OSGeo) http://www.osgeo.org/ Summer 2007 S&T Policy Fellow, > The National Academies http://www.nas.edu/ > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -