[sqlite] [Regression?] FTS function crashes since 3.7.7

2011-10-29 Thread Alexandre Courbot
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

Re: [sqlite] [Regression?] FTS function crashes since 3.7.7

2011-10-29 Thread Alexandre Courbot
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

Re: [sqlite] [Regression?] FTS function crashes since 3.7.7

2011-10-29 Thread Alexandre Courbot
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

[sqlite] Referencing subquery several times

2011-11-11 Thread Alexandre Courbot
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 *

Re: [sqlite] Referencing subquery several times

2011-11-12 Thread Alexandre Courbot
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

Re: [sqlite] Referencing subquery several times

2011-11-12 Thread Alexandre Courbot
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: > >

Re: [sqlite] Referencing subquery several times

2011-11-12 Thread Alexandre Courbot
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

Re: [sqlite] FTS3 query question

2010-06-02 Thread Alexandre Courbot
> 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

[sqlite] Fts3 and JOIN sometimes results in inability to use MATCH operator

2008-08-06 Thread Alexandre Courbot
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

Re: [sqlite] Linking databases

2008-08-14 Thread Alexandre Courbot
> 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.

Re: [sqlite] FTS3 Snippet function on two column MATCHes

2008-08-15 Thread Alexandre Courbot
> 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:

Re: [sqlite] newbie command line question sqlite3

2008-08-21 Thread Alexandre Courbot
> 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

Re: [sqlite] Can I index a single text column on a fts table?

2008-08-23 Thread Alexandre Courbot
> 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

Re: [sqlite] FTS, snippet & Unicode?

2008-08-26 Thread Alexandre Courbot
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.

Re: [sqlite] How can I load big file into a sqlite database?

2008-08-28 Thread Alexandre Courbot
> 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

Re: [sqlite] How can I load big file into a sqlite database?

2008-08-29 Thread Alexandre Courbot
> 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

Re: [sqlite] Unable to compile fts2 as loadable extension

2008-10-07 Thread Alexandre Courbot
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

Re: [sqlite] END TRANSACTION failed with "database is locked"

2008-10-08 Thread Alexandre Courbot
> 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

Re: [sqlite] Comparing a field between two databases?

2008-11-16 Thread Alexandre Courbot
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

2008-12-04 Thread Alexandre Courbot
> 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

Re: [sqlite] Formatted text with fts3

2009-03-09 Thread Alexandre Courbot
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

Re: [sqlite] Search in multiple fields.

2009-06-05 Thread Alexandre Courbot
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.

Re: [sqlite] temp tables or virtual tables, which is faster?

2009-06-09 Thread Alexandre Courbot
> 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

Re: [sqlite] temp tables or virtual tables, which is faster?

2009-06-09 Thread Alexandre Courbot
> 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

Re: [sqlite] FTS & Doc Compression

2010-03-03 Thread Alexandre Courbot
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

Re: [sqlite] Updated FTS3 compression patch

2010-03-24 Thread Alexandre Courbot
> 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] [CRASH] Segmentation fault since 3.8.0

2013-09-03 Thread Alexandre Courbot
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

Re: [sqlite] [CRASH] Segmentation fault since 3.8.0

2013-09-04 Thread Alexandre Courbot
On Tue, Sep 3, 2013 at 9:20 PM, Richard Hipp <d...@sqlite.org> wrote: > On Mon, Sep 2, 2013 at 11:07 PM, Alexandre Courbot <gnu...@gmail.com> wrote: >> >> Hi everyone, (not subscribed to the ML, please CC) >> >> The following happens since 3.8.0 (tested