[sqlite] fts4 support in distributions of SQLite?
Greetings, I'm working on an app that may end up open sourced and released publicly for others to use. It uses SQLite and the records have a comments field for which I'd like to use a fts4 vtable to enable search. My few test systems show that support for fts4 is present, but I'm trying to learn if it's a reasonable assumption that the usual SQLite build/deployment "out there" includes support for the feature. Is it a reasonable expectation that package maintainers on Linux/BSD flavors, OS X port frameworks, etc. ship the package with fts4 enabled? Is it typically only missing if someone has built locally and intentionally disabled support for e.g. optimization reasons or something? Wondering how lazy I can get on working around, disclaiming, or otherwise handling a key feature that could be missing. Thanks, -- Darren Spruell phatbuckett at gmail.com
[sqlite] db File Remains Open After Connection is Closed
Hi Ray, Sqlite.Net implements a connection pooling scheme. Even when your connection is closed, the unmanaged database handle may still be open. After you have disposed of your connection object and are ready to do file-level operations, do something like this: SQLiteConnection.ConnectionPool.ClearPool() This should force the unmanaged connection to close if indeed there are no lingering open managed connections. -- Matt Brown On Thu, Mar 5, 2015 at 12:34 PM, RNACS - Info mailto:info at rnacs.com>> wrote: "SQLconn" is a SQLiteConnection to my database file. Once the connection is closed & disposed of, what still has the .db file still open? How do I close the file so I can delete it?
[sqlite] Libstringmetrics
Dear all,
I have some problems with https://github.com/aperi2007/libstringmetrics
. For example:
When I use "similarity" in qgrams_distance, I get good results. But when
I use "metric", it works only if it is only once in the query. When
there are 2 different usages, they somehow interfere together. Is this
the right place where to report such problem?
> sqlite3
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select load_extension("libstringmetrics.dll");
sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
...> stringmetrics("qgrams_distance","similarity",a.firstname,
b.firstname,"") first_dist,
...> stringmetrics("qgrams_distance","similarity",a.lastname,
b.lastname,"") last_dist
...> from
...> (select "Milan" as firstname, "Roubal" as lastname ) a,
...> (select "Milan" as firstname, "roubal" as lastname ) b
...> ;
Milan|Milan|Roubal|roubal|100.0|62.5
sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
...> stringmetrics("qgrams_distance","metric",a.firstname,
b.firstname,"") first_dist,
...> stringmetrics("qgrams_distance","metric",a.lastname,
b.lastname,"") last_dist
...> from
...> (select "Milan" as firstname, "Roubal" as lastname ) a,
...> (select "Milan" as firstname, "roubal" as lastname ) b
...> ;
Milan|Milan|Roubal|roubal|6|6
sqlite> select a.firstname, b.firstname, a.lastname, b.lastname,
...> stringmetrics("qgrams_distance","metric",a.firstname,
b.firstname,"") first_dist
...> from
...> (select "Milan" as firstname, "Roubal" as lastname ) a,
...> (select "Milan" as firstname, "roubal" as lastname ) b
...> ;
Milan|Milan|Roubal|roubal|0
Thank you
Best Regards
Milan
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On 2015-03-06 03:19 PM, Richard Hipp wrote: > Yikes! Actually (1) cannot be determined for normal (non-virtual) > tables either because the value could be a BLOB even if the column > affinity is TEXT. And so the current LIKE optimization is not valid > *ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f I > don't yet know how we will fix this... True, but who uses LIKE on a BLOB? Any such usage has to be by accident - though granted, this can happen. Maybe a simple documentation note stating that LIKE operator on BLOB values will have undefined results?
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
Using LIKE on a BLOB is not the problem. It is the LIKE optimization that is broken, because it requires a BLOB to sort AFTER a text, which is never the case, while the LIKE function compares an expression that may contain wildcards to the raw data, which may be the case. -Urspr?ngliche Nachricht- Von: R.Smith [mailto:rsmith at rsweb.co.za] Gesendet: Freitag, 06. M?rz 2015 14:25 An: sqlite-users at mailinglists.sqlite.org Betreff: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On 2015-03-06 03:19 PM, Richard Hipp wrote: > Yikes! Actually (1) cannot be determined for normal (non-virtual) > tables either because the value could be a BLOB even if the column > affinity is TEXT. And so the current LIKE optimization is not valid > *ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f I > don't yet know how we will fix this... True, but who uses LIKE on a BLOB? Any such usage has to be by accident - though granted, this can happen. Maybe a simple documentation note stating that LIKE operator on BLOB values will have undefined results? ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On Fri, Mar 6, 2015 at 1:21 PM, Hick Gunter wrote: > And then there remain to be considered the effects of the pragma > CASE_SENSITIVE_LIKE > Good point. But that's no different from the case when an application overrides the LIKE behavior via a custom function, and the vtable can similarly decide to ignore it or not; Or can even decide changing like the semantic of LIKE, again just like an application overrides. Which is way I think the point you raise is tangential to SQLite not giving vtables the opportunity to optimize LIKE. --DD
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
And then there remain to be considered the effects of the pragma CASE_SENSITIVE_LIKE -Urspr?ngliche Nachricht- Von: Dominique Devienne [mailto:ddevienne at gmail.com] Gesendet: Freitag, 06. M?rz 2015 10:30 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On Fri, Mar 6, 2015 at 10:12 AM, R.Smith wrote: > On 2015-03-06 09:42 AM, Dominique Devienne wrote: > >> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: >> >> >>> The LIKE operator can be overridden by the application to mean >>> anything the application wants - it is not compelled to follow >>> standard SQL semantics. For that reason, virtual tables are unable >>> to optimize using LIKE since they have no way of knowing what it will do. >>> >>> Works as designed. >>> >>> Sure. But SQLite knows whether LIKE is overriden by the application >>> or >> not, >> so shouldn't it only hide LIKE from the virtual table when it detects >> it, instead of wholesale preventing the vtable from optimizing the >> "normal semantic" LIKE case? My $0.02. --DD >> > > I'm sure this is possible, but how would it be feasible? If this was > implemented, how would you know as a programmer designing a virtual > table (or many other interface things) whether or not the target you > operate on will/will not be able to run some bit of code? Even if you > could query the API to find out whether it is possible (i.e. the > normal LIKE is used), you still need to use that result as a specifier > to decide which code block to implement. And if you are going to have > to do two code blocks... might as well do the one where LIKE isn't supported > right from the start. > > Now if there was a setting where you could /force/ the standard LIKE > to be used along with supporting the v-table interface, that might be > something, but that might break a whole other universe of > possibilities for v-table users and end-user customization. That's not what I was saying. DRH is saying the vtable *never* sees the LIKE and SQLite itself a-posteriori filters out the rows based on LIKE. The vtable is not aware it's been denied seeing the LIKE. I'm saying that (in the vast majority of) cases when LIKE is not overriden by the application (globally or for that vtable), something SQLite can know, SQLite could pass the LIKE constraint to the xBestIndex, and let the vtable decide whether it can handle (i.e. optimize) LIKE or not, and whether SQLite should double-check it or not (as usual for vtable indexing). When it is overriden, it behaves as now. But that way the vtable has at least the opportunity to optimize LIKE, unlike now. Whether that's a priority, obviously not given what we've read so far, but whether that's technically possible, from 30,000 ft I don't see why not given the above. My $0.02. --DD ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On 2015-03-06 09:42 AM, Dominique Devienne wrote: > On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: > >> >> The LIKE operator can be overridden by the application to mean >> anything the application wants - it is not compelled to follow >> standard SQL semantics. For that reason, virtual tables are unable to >> optimize using LIKE since they have no way of knowing what it will do. >> >> Works as designed. >> > Sure. But SQLite knows whether LIKE is overriden by the application or not, > so shouldn't it only hide LIKE from the virtual table when it detects it, > instead of wholesale preventing the vtable from optimizing the "normal > semantic" LIKE case? My $0.02. --DD I'm sure this is possible, but how would it be feasible? If this was implemented, how would you know as a programmer designing a virtual table (or many other interface things) whether or not the target you operate on will/will not be able to run some bit of code? Even if you could query the API to find out whether it is possible (i.e. the normal LIKE is used), you still need to use that result as a specifier to decide which code block to implement. And if you are going to have to do two code blocks... might as well do the one where LIKE isn't supported right from the start. Now if there was a setting where you could /force/ the standard LIKE to be used along with supporting the v-table interface, that might be something, but that might break a whole other universe of possibilities for v-table users and end-user customization.
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On Fri, Mar 6, 2015 at 10:12 AM, R.Smith wrote: > On 2015-03-06 09:42 AM, Dominique Devienne wrote: > >> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: >> >> >>> The LIKE operator can be overridden by the application to mean >>> anything the application wants - it is not compelled to follow >>> standard SQL semantics. For that reason, virtual tables are unable to >>> optimize using LIKE since they have no way of knowing what it will do. >>> >>> Works as designed. >>> >>> Sure. But SQLite knows whether LIKE is overriden by the application or >> not, >> so shouldn't it only hide LIKE from the virtual table when it detects it, >> instead of wholesale preventing the vtable from optimizing the "normal >> semantic" LIKE case? My $0.02. --DD >> > > I'm sure this is possible, but how would it be feasible? If this was > implemented, how would you know as a programmer designing a virtual table > (or many other interface things) whether or not the target you operate on > will/will not be able to run some bit of code? Even if you could query the > API to find out whether it is possible (i.e. the normal LIKE is used), you > still need to use that result as a specifier to decide which code block to > implement. And if you are going to have to do two code blocks... might as > well do the one where LIKE isn't supported right from the start. > > Now if there was a setting where you could /force/ the standard LIKE to be > used along with supporting the v-table interface, that might be something, > but that might break a whole other universe of possibilities for v-table > users and end-user customization. That's not what I was saying. DRH is saying the vtable *never* sees the LIKE and SQLite itself a-posteriori filters out the rows based on LIKE. The vtable is not aware it's been denied seeing the LIKE. I'm saying that (in the vast majority of) cases when LIKE is not overriden by the application (globally or for that vtable), something SQLite can know, SQLite could pass the LIKE constraint to the xBestIndex, and let the vtable decide whether it can handle (i.e. optimize) LIKE or not, and whether SQLite should double-check it or not (as usual for vtable indexing). When it is overriden, it behaves as now. But that way the vtable has at least the opportunity to optimize LIKE, unlike now. Whether that's a priority, obviously not given what we've read so far, but whether that's technically possible, from 30,000 ft I don't see why not given the above. My $0.02. --DD
[sqlite] update of view via trigger
On 3/5/2015 11:58 PM, Igor Tandetnik wrote: > If the latter is OK, then you can have a single trigger doing something > like this: > > insert or replace into Clean(X, Y, Z) > select > case when new.X = ifnull(c.X, d.X) then c.X else new.X end, > case when new.Y = ifnull(c.Y, d.Y) then c.Y else new.Y end, > case when new.Z = ifnull(c.Z, d.Z) then c.Z else new.Z end > from Clean c join Dirty d on (c.rowid=d.rowid and c.rowid=new.rowid); Correction: insert or replace into Clean(rowid, X, Y, Z) select new.rowid, case when new.X = ifnull(c.X, d.X) then c.X else new.X end, ... from Clean c join Dirty d on (c.rowid=d.rowid and c.rowid=new.rowid); It's the deliberate violation of uniqueness constraint on rowid that makes INSERT OR REPLACE work here. -- Igor Tandetnik
[sqlite] Corrupted database
Hi Dave, ? thanks. In fact, I know, how to repair the database and get access to the data. What I was trying to track down a bit, is the reason for the database to get corrupted. But as Simon explained, this might have to do with "in-order-writing" of the RAID we're using. ? Alex ? ?
[sqlite] Corrupted database
Hi Simon, >So from all the above it's likely that the data in your tables is okay >, and only the indexes in that file are corrupt. ?So dropping and >?recreating your indexes should fix the file perfectly unless the >?violation of the UNIQUE requirement is in the primary index for a table. ? Yes, this seems to work. ? >Many Server-class hard disks or hard disk drivers come with settings >?which enforce correct behaviour (at the costs of slowing down writing). >?Some RAID software has settings which do the same. ? That's a good point, I will go through the configuration?and look for these settings. ? Thanks ? Alex ?
[sqlite] update of view via trigger
On Mar 6, 2015 7:33 AM, "Igor Tandetnik" wrote: A bunch of good stuff snipped... Thanks for the alternative suggestions. In thinking it over since my message, I've decided the multiple trigger approach isn't at all bad. A little verbose, but each column of the view has its own callback and SQLite figures out which is needed, probably far more optimally than I could in a query. Thanks again!
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: > On 3/5/15, Mike Nicolino wrote: > > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to > be a > > bug with Virtual Tables. Queries using 'like' in the where clause are > not > > getting the like clause passed to BestIndex as a query constraint. > > Specifically: > > > > > > - Simple query: select * from foo where name like 'a%' > > The LIKE operator can be overridden by the application to mean > anything the application wants - it is not compelled to follow > standard SQL semantics. For that reason, virtual tables are unable to > optimize using LIKE since they have no way of knowing what it will do. > > Works as designed. > Sure. But SQLite knows whether LIKE is overriden by the application or not, so shouldn't it only hide LIKE from the virtual table when it detects it, instead of wholesale preventing the vtable from optimizing the "normal semantic" LIKE case? My $0.02. --DD
[sqlite] Sqlite3 Bug Found
Hello, I was working on version 3.8.8.2 of sqlite3 and I found a bug that is trace related. It can be reproduced in 3 steps: 1. ".t log" - opens log for tracing 2. ".t" - no argument. Closes the file descriptor that was previously opened (Problem) 3. ".d" - dumps into the file that was previously closed. = > Segmentation Fault Where: .t = .trace .d = .debug Best Regards, Costin
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On 3/6/15, Dominique Devienne wrote: > > Sure. But SQLite knows whether LIKE is overriden by the application or not, > so shouldn't it only hide LIKE from the virtual table when it detects it, > instead of wholesale preventing the vtable from optimizing the "normal > semantic" LIKE case? It's more complicated than that. The optimization in question is to convert WHERE clause terms of the form: xyz LIKE 'abc%' Into: xyz>='abc' AND xyz<'abd' But this optimization is only valid if (1) xyz is a text value, not a numeric value and (2) xyz has the "nocase" collating sequence. We typically do not know either for a virtual table. You might know (2) if the expression has an explicit COLLATE clause: xyz LIKE 'abc%' COLLATE nocase But SQLite does not have a mechanism whereby a virtual table can tell the query planner that the value of a column will never be numeric. Yikes! Actually (1) cannot be determined for normal (non-virtual) tables either because the value could be a BLOB even if the column affinity is TEXT. And so the current LIKE optimization is not valid *ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f I don't yet know how we will fix this... -- D. Richard Hipp drh at sqlite.org
[sqlite] db File Remains Open After Connection is Closed
Please also note that setting an object to 'nothing' does _not_ dispose it! This was the case back in the VB6 days, but in the .NET world you need to enclose all objects implementing 'IDisposable' in a 'using' block or call Dispose() explicitly on all your objects to properly release any resources. If you don't do so, Dispose() will be called on behalf of the Garbage Collector, which may well never happen during the runtime of your code. GB RNACS - Info schrieb am 05.03.2015 um 21:34: > I have a program written in Visual Basic .NET. > > One of the functions of the program is to move all of the program's data, > including a SQLite database to a different folder. > > The program is a 32-bit application using System.Data.SQLite.dll. > > I am having a problem that after I copy the data to the new folder, the > program fails when I try to delete the old folder. The error message says > that the folder cannot be deleted because the SQLite database file is in use > by another process. > > The code I am using is: > > ' Close license database connection > If Not IsNothing(SQLconn) Then > If SQLconn.State <> ConnectionState.Closed Then > SQLconn.Close() > End If > SQLconn = Nothing > End If > > ' Move data files >'\ code to move files goes here > > ' Delete old directory >Directory.Delete(strOldLocation, > FileIO.DeleteDirectoryOption.DeleteAllContents) > > The error occurs when I try to execute the "Directory.Delete" statement. > > "SQLconn" is a SQLiteConnection to my database file. Once the connection is > closed & disposed of, what still has the .db file still open? How do I > close the file so I can delete it? > > Thank you for any assistance. > > Ray Andrews > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] db File Remains Open After Connection is Closed
Hi Ray, At the point when you attempt to delete the folder, if you brake your program there, are you able to delete it via standard windows explorer? If not, how about closing your program, and trying directly after you exit your program? Sometimes an anti-virus or other tool (such as Indexing service or such) might keep hold of the file or folder so you cannot delete it until it is done. Also, do you have permission to delete it? Is the folder near a Windows UAC protected path? (Such as Program Files, Windows, etc). If you break your program and then are able to delete the file via explorer, none of the above is a problem, it must be your program holding it. When you close sqlite (calling close() in the api) it releases the file as soon as it is done with any current outstanding operations (such as a rollback or vacuum etc.) - but returns an error code if anything did not go smoothly. If no error code is returned (i.e SQLITE_OK is returned) then the files was definitely released by SQLite and something else is holding on to them. On 2015-03-05 11:23 PM, RNACS - Info wrote: > Simon, > > Does not help. I put a breakpoint at the "SQLconn = Nothing" statement & > checked the value of SQLconn.State. It was "Closed", so I continued one & > still got the error. > > Ray > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon > Slavin > Sent: Thursday, March 05, 2015 3:58 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] db File Remains Open After Connection is Closed > > >> On 5 Mar 2015, at 8:34pm, RNACS - Info wrote: >> >> The code I am using is: >> >> ' Close license database connection >> If Not IsNothing(SQLconn) Then >> If SQLconn.State <> ConnectionState.Closed Then >> SQLconn.Close() >> End If >> SQLconn = Nothing >> End If > After "SQLconn.Close()", check again the state of the connection to see if > it was successfully closed. If not, display an error message. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
