Re: [sqlite] Search in multiple fields.
Hi, Not sure if that answers your question, but I think you seriously want to use FTS3. It will be both a trillion times faster than your current query - you may have trouble with your last example though. Maybe you can still go through by escaping the search terms. http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.syntax&v=1.2 Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] temp tables or virtual tables, which is faster?
> I have now learned about the concept of virtual tables. Am I better > of with my current approach because I can index the files in the temp > table, or would I be better off using a virtual table to scan the hard > drive for the images? Depends on how often you must reindex and how critical it is for you to be up-to-date. A virtual table will ensure you do not have any synchronization problem. On the other hand, if your temp table is indexed, you may expect it to be faster. You do not tell much about your join conditions. > Also, since the only writing I am doing is to a temp table, can I > connect to the SQLite database with read only? When I need to be such things, I just set my database file to be read-only at OS level. :) Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] temp tables or virtual tables, which is faster?
> Ok, here is what I am doing right now. The idea is to return all the > images in the folder to the frontend and indicate which ones the > current customer has in his/her favorite's. If your images do not change, I guess you would get good performances by indexing ImageId and using the temp table. As far as I understand, the virtual table would work as well, and would save you some memory, but you have to program it. So in my opinion both options are correct. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fts3 and JOIN sometimes results in inability to use MATCH operator
Hello everybody,
Using sqlite 3.6.0 with fts3 enabled:
create table indexes(docid int);
create virtual table texts using fts3(reading);
insert into texts values("text1");
insert into indexes values(last_insert_rowid());
select * from indexes join texts on texts.docid == indexes.docid where
texts.reading match "text1";
Last line outputs:
SQL error: unable to use function MATCH in the requested context
I first noticed that behavior in a much larger query with plenty of
joins. Reordering them gives me a query that works. Is there any
reason for this? I don't know sqlite enough to affirm this is a bug,
but this looks suspicious to me. Shall I fill in a trac ticket?
Alex.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linking databases
> Is there a way to link and do a select on multiple databases? You probably want to use the "attach" command: http://www.sqlite.org/lang_attach.html Then you can join all your tables as if they were declared in the same database, without any performance penalty. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
> I would like to generate Snippets from MATCHes in two columns, > however, I get the following error: "unable to use function MATCH in > the requested context" with the following query -- I think you ran into the same problem as I did: http://www.nabble.com/Fts3-and-JOIN-sometimes-results-in-inability-to-use-MATCH-operator-td18851478.html Unfortunately there is no solution right now. I've discussed that on the development mailing list as well where I have been confirmed this is a bug. I have also opened a trac ticket about it: http://www.sqlite.org/cvstrac/tktview?tn=3281,3 There is a workaround, which is to used nested queries instead of joins (i.e. "where x in (select ...)" instead of "join"). I've met the Snippet function for the first time in your mail and failed to find any documentation about (mind to give me a pointer here? it looks interesting) so I cannot convert your example query but I'm confident it can be fixed this way. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie command line question sqlite3
> I have the commanline sqlite3.exe in the same folder as the .db and need to > now manipulate the db to manually remove a corrupt record. > I tried: .tables to show trables but nothing happens Don't forget to run sqlite3.exec followed by the name of your database file. Otherwise you will be working on an empty database and won't be able to modify your tables. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I index a single text column on a fts table?
> I'm not sure if I understood the fts table declaration format and > posibilieties but is it possible to make SQLite index only a single text > column in a table containing multiple text columns? No. All tables in FTS3 are indexed text, regardless of how you declare them. If you don't want your other columns to be indexed, you can split them into a separate table (keeping the FTS3 table only for indexed text) and join them using the implicit docid column of your FTS3 table. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS, snippet & Unicode?
I know there is a patch at http://www.sqlite.org/cvstrac/tktview?tn=3140,38 that is supposed to improve Unicode support in FTS3. I suspect it to turn any Unicode character into a token - however maybe you can use it as a basis to implement what you need. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I load big file into a sqlite database?
> I usually used "load data infile" command in mysql to insert long list of > data. > But I could not find this kind of command in sqlite. > How do you load big file into a sqlite database?? I guess what you want to do is "sqlite3 databasefile < infile" See also the ".import FILE TABLE" command to inport a file into a single table. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I load big file into a sqlite database?
> I am interested in this issue also. > I didn't understand the first part of your answer. "sqlite3 databasefile > < infile" ?? > > The ".import FILE TABLE" works, but it is from CLI. How can I do it in > my C++ application using the sqlite3? An equivalent would be to read the file line by line and execute the statements it contains. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unable to compile fts2 as loadable extension
Any reason why you don't want to use fts3 instead? Using fts2 means potential big consistency issues if you run vacuum on your database. Moreover, fts3 should just compile and run smoothly on latest versions. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] END TRANSACTION failed with "database is locked"
> I am executing some operations in a loop and all the operations are wrapped > inside a transaction. During one iteration the "END TRANSACTION" returned > with an error "database is locked". Since this is in a loop my next request > to "BEGIN TRASACTION" failed with "cannot start a transaction within a > transaction". What is the normal way to get rid of this situation? Is it > right to check the status with "sqlite3_get_autocommit" and issue a > "ROLLBACK TRASACTION" if it inside a transaction before issuing BEGIN? This is probably because you still have one active query when during the commit. Try to sqlite3_finalize all your queries before the commit. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick FTS3 question: partial matches
> I would like to look up partial strings across several columns using FTS3 > and MATCH. For example, if I have a column that has data, > 'helloThisIsATest'... and I do: > > select * from myTable where myTable match '*This*'; You can only use wildcards as a suffix - this is due to the nature of the indexes fts3 uses. I don't know of a possible workaround for this while still using fts3. I'd say that for this kind of request, you are condemned to use '... like "%This%". Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] One writer, many interruptible readers application design
Hello everybody, I know there are lots of documentation about sqlite and multithreading and yes, I have read all of it! ;) I'm to the point where I think about restructuring my application in order to make it more reactive, and I'd like to ask for your wise comments. The scheme I would like to implement is as follow: - The main thread (which displays the GUI and must therefore not be frozen by some long query) is the only thread that does write accesses to the database. Writing remains very seldom and fast, though. - Around this thread, several other threads may exist that run read-only queries on demand by the main thread (typically, when the user wants to display something in the user interface. I need to use a different thread because I don't want to block the GUI while the request is running). When the request is finished, the result is displayed by the main thread. However, the main thread may also want to abort that request, for instance if the user decides to display something else by the meantime. The problem is that there may be several of these peripheral threads (I estimate up to 5), and they must all be interruptible individually. This is where my problem lies: sqlite has a function to interrupt queries (sqlite3_interrupt), but it stops all the queries of a given connection. Therefore, to ensure all queries are individually interruptible, I must open one sqlite connection per thread. By doing so, and considering that the main thread only writes seldomly, I think I would not experience any GUI is freeze, as all the lengthly queries would be executed by other threads that can be interrupted if the user decides to take some further action before they complete. The only freeze condition that I may have would be when the main thread wants to write to the database while other threads are still reading it. In that case, the GUI may freeze while the main thread tries to acquire its lock. However, I think I can easily workaround that using another thread that is only dedicated to writing and buffers write requests as they arrive. This design will require a major overhaul of my application, so I'd like to make sure it is a good idea. I'd like to avoid using multiple connections, but I feel like I have no choice there. Would anyone have something to say against that design or would suggest a better idea? Thanks, Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing a field between two databases?
Hi, attach is probably what you want: http://www.sqlite.org/lang_attach.html Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] extremely slow join on an fts3 table
> On these tables this query is very slow (about 1 row per second) > > select g.id from general g, general_text gt where g.id = gt.id; > > and these ones have a normal speed: > > select g.id from general g, general_text_content gt where g.id = gt.docid; I think the point is that you cannot declare the ID field of general_text as an primary key. In FTS 3 tables, the docid member is already a primary key, so it is highly possible that your join does not use an index and parses all the rows of the table. A solution would be to insure that general.id and general_text.docid have the same id, which can probably be achieved by using last_insert_rowid() after inserting to general_text (warning, there were a bug in that case if you happen to delete and insert rows again in general_text - you'd need to do that within a transaction to have the correct rowid). Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Formatted text with fts3
Never did this myself, but I think you can do what you need by writing your own tokenizer: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/README.tokenizers Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS & Doc Compression
While I am not directly concerned by the problem, a possibility to transparently compress the text of FTS3 tables (not the indexes, just the contents of the virtual column) using zlib would be great. I cut a database size in half by doing this on non-fts3 text tables. DEFLATE being very efficient in terms of speed even for embedded devices by today's standard, I'm convinced this could do wonders. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updated FTS3 compression patch
> Patch is here > http://sqlite.mobigroup.ru/src/vinfo/d3d9906674 Would love to try it - but for some reason I cannot find a way to get an actual "patch" on this page. Could you produce a diff that could be applied on top of 2.6.23's source, or even better amalgamation? Thanks, Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 query question
> But I'm not getting any results when I combine these using OR: > > SELECT rowid FROM EntryHeaders eh WHERE ( eh.Paragraph MATCH '91' ) OR ( > eh.Title MATCH 'civil' ) See http://www.sqlite.org/fts3.html#section_3 . Your query should be more like: SELECT rowid FROM EntryHeaders WHERE EntryHeaders MATCH "Paragraph:91 OR Title:civil" Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [CRASH] Segmentation fault since 3.8.0
Hi everyone, (not subscribed to the ML, please CC) The following happens since 3.8.0 (tested on both 3.8.0 and 3.8.0.1): $ cat |sqlite3 create table t1(id INTEGER); create table t2(id INTEGER, v INTEGER); insert into t1 values(1); select distinct t1.id from t1 left join t2 on t2.id = t1.id order by t2.v is null; Segmentation fault (core dumped) The same statements with 3.7.17 do *not* crash. Removing the "distinct" keyword, selecting t2.id instead of t1.id, or ordeting on just t2.v result in a successful query. This seems triggered by the usage of "distinct", a left join, and ordering on a condition, but I haven't managed to narrow it further down. I am also not familiar enough with SQLite's internals to debug it further unfortunately. Could this be related to the new query planner? Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [CRASH] Segmentation fault since 3.8.0
On Tue, Sep 3, 2013 at 9:20 PM, Richard Hipp wrote: > On Mon, Sep 2, 2013 at 11:07 PM, Alexandre Courbot wrote: >> >> Hi everyone, (not subscribed to the ML, please CC) >> >> The following happens since 3.8.0 (tested on both 3.8.0 and 3.8.0.1): >> >> $ cat |sqlite3 >> create table t1(id INTEGER); >> create table t2(id INTEGER, v INTEGER); >> insert into t1 values(1); >> select distinct t1.id from t1 left join t2 on t2.id = t1.id order by >> t2.v is null; >> Segmentation fault (core dumped) > > > Thanks for the simple test case! See > http://www.sqlite.org/src/tktview/be84e357c03 for the ticket. We'll get to > work on this right away. Seen on the ticket this has been fixed already, thanks for the quick reaction! Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [Regression?] FTS function crashes since 3.7.7
Hi everybody, While testing some database upgrade function of mine, I ran into a segmentation fault. I started looking at the possible cause in my code, but I soon realized the crash would only happen using SQLite 3.7.7 or 3.7.8. If I use 3.7.6 or lower, things go safely. Since I am not familiar with SQLite's internals, I have run the thing under Valgrind and GDB to get some backtraces. Here is Valgrind's report: ==27928== Invalid read of size 4 ==27928==at 0x646251: sqlite3Fts3PendingTermsFlush (sqlite3.c:124996) ==27928==by 0x63A2A8: fts3SyncMethod (sqlite3.c:117025) ==27928==by 0x63A983: fts3SavepointMethod (sqlite3.c:117332) ==27928==by 0x6239C5: sqlite3VtabSavepoint (sqlite3.c:100182) ==27928==by 0x5E9F07: sqlite3VdbeExec (sqlite3.c:66431) ==27928==by 0x5E40D8: sqlite3Step (sqlite3.c:61954) ==27928==by 0x5E42B9: sqlite3_step (sqlite3.c:62027) ==27928==by 0x5B3BC3: SQLite::Query::exec() (Query.cc:179) ==27928==by 0x5B3D80: SQLite::Query::exec(QString const&) (Query.cc:230) ==27928==by 0x590DF5: update8to9(SQLite::Query&) (Database.cc:206) ==27928==by 0x59128A: Database::updateUserDB(int) (Database.cc:262) ==27928==by 0x5914CD: Database::checkUserDB() (Database.cc:297) ==27928== Address 0x150 is not stack'd, malloc'd or (recently) free'd ==27928== ==27928== ==27928== Process terminating with default action of signal 11 (SIGSEGV) ==27928== Access not within mapped region at address 0x150 ==27928==at 0x646251: sqlite3Fts3PendingTermsFlush (sqlite3.c:124996) ==27928==by 0x63A2A8: fts3SyncMethod (sqlite3.c:117025) ==27928==by 0x63A983: fts3SavepointMethod (sqlite3.c:117332) ==27928==by 0x6239C5: sqlite3VtabSavepoint (sqlite3.c:100182) ==27928==by 0x5E9F07: sqlite3VdbeExec (sqlite3.c:66431) ==27928==by 0x5E40D8: sqlite3Step (sqlite3.c:61954) ==27928==by 0x5E42B9: sqlite3_step (sqlite3.c:62027) ==27928==by 0x5B3BC3: SQLite::Query::exec() (Query.cc:179) ==27928==by 0x5B3D80: SQLite::Query::exec(QString const&) (Query.cc:230) ==27928==by 0x590DF5: update8to9(SQLite::Query&) (Database.cc:206) ==27928==by 0x59128A: Database::updateUserDB(int) (Database.cc:262) ==27928==by 0x5914CD: Database::checkUserDB() (Database.cc:297) And here is the GDB backtrace (using 3.7.8): #0 0x00646251 in sqlite3Fts3PendingTermsFlush (p=0x0) at sqlite3.c:124996 #1 0x0063a2a9 in fts3SyncMethod (pVtab=0x0) at sqlite3.c:117025 #2 0x0063a984 in fts3SavepointMethod (pVtab=0x0, iSavepoint=0) at sqlite3.c:117332 #3 0x006239c6 in sqlite3VtabSavepoint (db=0x9bf4a8, op=0, iSavepoint=0) at sqlite3.c:100182 #4 0x005e9f08 in sqlite3VdbeExec (p=0x9b9ac8) at sqlite3.c:66431 #5 0x005e40d9 in sqlite3Step (p=0x9b9ac8) at sqlite3.c:61954 #6 0x005e42ba in sqlite3_step (pStmt=0x9b9ac8) at sqlite3.c:62027 Here the faulty is pVtab which should not be NULL from fts3SavepointMethod, since it is unconditionally dereferenced. fts3SavepointMethod is called through a function pointer from line 100182 which reads the following: rc = xMethod(db->aVTrans[i]->pVtab, iSavepoint); That's about all the information I can get for now. Compiling with SQLITE_DEBUG=1 does not give anything more - it just crashes earlier in fts3SavepointMethod because of an assert that dereferences pVtab. I do not understand SQLite's code well, but it seems to me that a virtual table function should not be called with a NULL virtual table pointer anyway. I wish I could send a code sample that reproduces the issue, but unfortunately the case is rather big and complex. I will try to come with a minimal program though. If there is anything more I can do to help debugging, please let me know. Thanks, Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Regression?] FTS function crashes since 3.7.7
Dan, On Sat, Oct 29, 2011 at 8:48 PM, Dan Kennedy wrote: > Found one problem: > > Ticket: http://www.sqlite.org/src/info/48f299634a > Fix: http://www.sqlite.org/src/info/3565fcf898 > > Please follow up if you try this fix and you still get > the crash. Thanks for pointing me to this. Indeed, this solves my problem. Great to see this is already fixed - guess I will just have to blacklist SQLite 3.7.7 and 3.7.8. Thanks! Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Regression?] FTS function crashes since 3.7.7
By the way, can we expect a point release (3.7.8.1?) that includes this fix soon? It seems rather serious to me, and the use case should not be so seldom. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Referencing subquery several times
Hi everybody, Here is a simplified version of the statement I try to run (let a, b, and m be tables with only one column named c containing integers): SELECT * FROM m WHERE c IN (SELECT * FROM a) OR c IN (SELECT * FROM b) AND (NOT c IN (SELECT * FROM a) OR c IN (SELECT * FROM b)); I know this could be done more easily, but I have to keep this structure (i.e. cannot JOIN for instance). My question is, how could I avoid repeating the subqueries after the AND NOT, since they will return the same set as the previous ones? In case I do not replace them, would SQLite be able to optimize and only run them once? Thanks, Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Referencing subquery several times
Hi Igor, On Nov 12, 2011 11:45 AM, "Igor Tandetnik" wrote: > This query doesn't make much sense. It appears that quite a few conditions are redundant, or else the parentheses are in the wrong places. What logic were you trying to express here? Sorry, I tried to simplify it as much as possible but this is right is does not make much sense. I have two FTS tables containing strings for definitions, one is the definition in English, the other is the same definition in another language. Both tables use the same rowid for the same definition, but while every definition has an entry in the English table, some are missing in the non-English one. I want to find out which entries have a given word in their definition. So the subqueries are actually matches against the FTS table. However, I do not want an entry to match in case the match is made against the English FTS table, and a non-matching word also exists in the non-English table. That is, the English table should only be used for entries which do not have a non-English definition. Put it differently, an entry should match if: - the looked up word is matches in the non-English table, OR - the non-English table does not have a row for the entry, AND the corresponding English definition matches. I cannot reasonnably use joins here because I would have to use left joins (because of the OR condition) and observed severe performance degradation if I do so. This is because the FTS tables' rowid does not directly correspond to an entry ID, but must be matched with another table to get the entry id. I hope this is clear enough - sorry for the long explanation. I have possible workarounds to make this work differently, but being able to reference the subqueries directly would be the cleanest to me. Thanks, Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Referencing subquery several times
On Sat, Nov 12, 2011 at 10:02 PM, Luuk wrote: > Should give same results as: > > SELECT * FROM m WHERE > c IN (SELECT * FROM a) OR > c IN (SELECT * FROM b) > AND (c IN (SELECT * FROM b)); > > Because of the 'OR' on the second line > > This can be simplified to: > > SELECT * FROM m WHERE > c IN (SELECT * FROM a) OR > c IN (SELECT * FROM b); That's right. Sorry, my query was wrong in the first place. It would have been more something like: SELECT * FROM m WHERE c IN (SELECT docid FROM a WHERE t match "blah") OR (c IN (SELECT docid FROM b WHERE t match "blah") AND NOT c IN (SELECT docid FROM a)); e.g. we can either have a match in a, but only a match in b if a does not have a row with the same docid. Which, in this case, does not involve any redundant subquery and should be fast. Sorry, my question was badly stated in the first place. At least your answers helped me notice it - thanks for that. Of course, if there is any better way to do this query, I would be glad to know it anyway. Thanks again! Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Referencing subquery several times
On Sat, Nov 12, 2011 at 11:59 PM, Black, Michael (IS) wrote: > Why are you contorting yourself into just one query? It is actually part of a larger query that joins against this result. Granted, there are ways to workaround this, but I would be surprised if it was not possible to express that in SQL so wanted to ask the community about this. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
