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
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
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
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:
>
>
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
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 *
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
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
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
> 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
> 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.
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
> 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
> 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
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.
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
> 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
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
> 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
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
> 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
> 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
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.
> 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
> 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
> 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:
> 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.
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
28 matches
Mail list logo