Re: [sqlite] Best way to "split" a large DB into two DBs

[Dan Kennedy]

On 09/07/2016 11:08 PM, Dominique Devienne wrote:

Imagine there's a 25GB SQLite DB file in version v1 of an application.

Because the way this DB is structured, with large to very large blobs, this
leads to problems for the app. That DB has 35 tables, but 5 of those
represent 95% of the DB size (because of those blobs).

So for v2 of the app, there's a new design where the 30 "lightweight"
tables move into a new DB file (with additional new tables), and the 5
"blob heavy" tables remain in the existing DB file (but the 30 "moved"
tables should be dropped from it).

Initial design was to copy the DB file (app is "shutdown", so no connection
to that DB file).
Drop the 5 big tables. Vaccum it (the new DB). Add the new tables.
But the 25GB copy was deemed too long in that case (several minutes).
(after all, copying 95% of 25GB to discard all those GBs is inefficient)

So alternate design was to create the schema empty in the new DB file,
attach the old one, and insert the data from the 30 tables into the new
empty (with identical schema/structure) tables. But that's also very slow
apparently.


SQLite contains a special optimization for statements of the form:

  INSERT INTO tbl SELECT * FROM tbl2;

If both tables have the same set of columns, the same PK, the same 
indexes and identical UNIQUE constraints and no triggers, and if foreign 
keys are disabled, SQLite can copy records directly from one b-tree 
structure to another without unpacking the records. This can be much 
faster. And it writes the b-tree structures in order too, which leads to 
a more efficient use of the cache.


So if you create your new db with the tables and indexes but no 
triggers, then attach it and your main db to the same handle so that you 
can use an "INSERT INTO ... SELECT ..." statement of the form above, 
things might run a bit faster.


Dan.






Even though it's not my app, I know there are indexes and triggers on those
tables (but the triggers are mostly on deletes, so don't really apply
here), and I suggested adding those after the selects, but I doubt it's
going to make a dramatic difference time-wise.

Conceptually, the ideal situation would be to just copy the DB header,
sqlite_master page(s) (dropping the 5 big tables definitions and related
indexes/triggers from sqlite_master), and only the 5% of pages related to
the other tables (and related objects).  (chaining of pages and root pages
in sqlite_master have to be updated of course). It's almost like a form of
VACCUM, except it's not in-place and works on a subset of the tables.
IO-wise, that has the potential to be 20x faster I imagine.

But of course there's no such "out-of-place" "partial" VACCUM...

Then I though maybe .backup, which also works at the page level (I
believe), perhaps can subset what tables to backup. But no luck there
either. backup works for the whole DB, not a subset.

Am I missing other ways to achieve this "split" efficiently?
Any chance the backup API could group table-specific (and related objects)
alternate form?

Any advice would be appreciated. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug in SQLITE_CHECKPOINT_PASSIVE?

[Dan Kennedy]

On 09/06/2016 02:54 AM, Allen wrote:

I'm doing some stress testing of an app that uses
sqlite-amalgamation-3140100 compiled under gcc 4.8.5
(x86_64-posix-seh-rev0, Built by MinGW-W64 project) on Windows 7 x64 with
the compile options:

#define SQLITE_MAX_MMAP_SIZE0

#define SQLITE_OMIT_AUTORESET1
#define SQLITE_OMIT_DEPRECATED1

#define HAVE_MALLOC_USABLE_SIZE1
#define HAVE_USLEEP1

#define SQLITE_ENABLE_API_ARMOR1
#define SQLITE_DEBUG1
#define SQLITE_MEMDEBUG1

SQLite is initialized with:

sqlite3_config(SQLITE_CONFIG_MULTITHREAD)
sqlite3_config(SQLITE_CONFIG_URI, 1)
sqlite3_initialize()
sqlite3_enable_shared_cache(1)

and then a WAL database is created with:

sqlite3_open("wal.db?cache=private", )
sqlite3_busy_timeout(db, 0x7000)
sqlite3_exec(db, "PRAGMA page_size = 4096;", NULL, NULL, NULL)
sqlite3_exec(db, "PRAGMA journal_mode = WAL;", NULL, NULL, NULL)
sqlite3_exec(db, "PRAGMA PRAGMA wal_autocheckpoint = 0;", NULL, NULL, NULL)

The app has multiple threads reading and writing the database using
prepared statements, with each thread using its own sqlite3_stmt handles.


Does each thread also have its own database handle (sqlite3*)?

With SQLITE_CONFIG_MULTITHREAD, it's not safe for multiple handles to 
use sqlite3_stmt objects from the same database handle simultaneously.









The app also has a worker thread that sleeps for about a second and then
calls either sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE,
NULL, NULL) or sqlite3_wal_checkpoint_v2(db, NULL,
SQLITE_CHECKPOINT_TRUNCATE, NULL, NULL), with about 10 seconds between the
TRUNCATE checkpoints.

Before writing or calling SQLITE_CHECKPOINT_TRUNCATE, a thread acquires an
application-level write mutex that was put in to avoid having to handle
LOCKED and BUSY errors.  However, when reading the database, or when
calling SQLITE_CHECKPOINT_PASSIVE, the application-level write mutex is not
acquired, so reading the database and executing SQLITE_CHECKPOINT_PASSIVE
can both happen concurrently with one thread that is writing to the
database.

When load testing, the app hammers the database for about 10 seconds and
then, when executing sqlite3_step for a "begin exclusive;" statement on the
WAL database, it hits the assertion on line 58000 of sqlite.c which reads:

assert( p->sharable==0 || p->locked==0 || sqlite3_mutex_held(p->pBt->mutex)
);

At the time it triggers this assertion, there is only one thread that has
acquired the application-level write mutex and is attempting to execute
"begin exclusive;"--any other writer thread has by this point executed
"commit;" and released the application-level write mutex--and the worker
thread that calls sqlite3_wal_checkpoint_v2 is at the time sleeping, not
executing sqlite3_wal_checkpoint_v2.

If I turn off SQLITE_DEBUG, then instead of the assertion, I eventually get
an SQLITE_CORRUPT error.

If I change my worker thread to acquire the application-level write mutex
before calling SQLITE_CHECKPOINT_PASSIVE, then the assertion and
SQLITE_CORRUPT errors disappear and the app runs fine.  The effect of this
change is the the SQLITE_CHECKPOINT_PASSIVE will never attempt to run at
the same time another thread is inside a "begin exclusive;" block. Note
however that would seem to defeat the purpose of SQLITE_CHECKPOINT_PASSIVE,
which should be able to run even while another thread is writing to the WAL
database.

I was clued in to try this because under earlier load testing when
SQLITE_DEBUG was not enabled, the call to SQLITE_CHECKPOINT_PASSIVE would
very intermittently return SQLITE_LOCKED, even though that is not a
documented return value for SQLITE_CHECKPOINT_PASSIVE.  It was that
anomalous behavior that led me to try putting SQLITE_CHECKPOINT_PASSIVE
inside the application-level write mutex to see what would happen.

Any thoughts on what the problem might be?

Thanks much,

Allen
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size on ATTACH-ed databases

[Dan Kennedy]

On 08/13/2016 01:14 AM, Ward WIllats wrote:

Consider:

1. Create a new database, set the pragma page_size=512

2. Create a new database on the connection with ATTACH DATABASE 
'/tmp/number_two.db' AS second;

3. Issue pragma second.page_size=4096  to try and set the page size on the 
attached DB to 4096.

4. Read back with pragma second.page_size and get the default page size of 
1024. (We are still on 3.10.1)

Is it expected that the 4096 did not "stick?" Is there some relationship 
between page sizes in a main and attached DB?


Can't reproduce this problem here. Are you able to reproduce it with the 
shell tool?



  $ ./sqlite3 x.db
  SQLite version 3.10.1 2016-01-13 21:41:56
  Enter ".help" for usage hints.
  sqlite> PRAGMA main.page_size = 512;
  sqlite> ATTACH '/tmp/two.db' AS second;
  sqlite> PRAGMA second.page_size = 4096;
  sqlite> PRAGMA second.page_size;
  4096
  sqlite> PRAGMA second.journal_mode = wal;
  wal
  sqlite> CREATE TABLE second.t1(a, b);
  sqlite> PRAGMA second.page_size;
  4096
  sqlite> PRAGMA main.page_size;
  512

Dan.



Thanks

-- Ward

(In real life, we also set journal_mode=WAL on both databases AFTER issuing the 
page_size pragma, in case it makes any difference.)





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4/5 ranking function differences

[Dan Kennedy]

On 08/11/2016 04:41 PM, Jan Berkel wrote:

You could run a regular database query from within the auxiliary
function implementation. Not sure how that would perform though -
perhaps similarly to the FTS3/4 approach, perhaps a little better.
Or, you could add the "weight" as an UNINDEXED column to the fts5
documents table. Then access it via the auxiliary function xColumn()
API.

Thanks, I was under the assumption that this would be slow, since the
xColumn() access will cause the whole row to be loaded (separately,
instead of the more efficient join upfront), but I'll try it.

Another open question I have is how to access the user query expression
from within the ranking function. Don't think this is exposed anywhere.
(Looking at the source I noticed Fts5Expr *, but it is private).

The best option would then be to pass it through to the
ranking function as a parameter?


I guess so. You can't actually get at the query text from within an 
auxiliary function implementation. You can get the number of phrases, 
query the FTS index for each phrase and some other things, but the 
actual query text or parsed query is not currently available.


What exactly do you need? What do you think the API should look like?

Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4/5 ranking function differences

[Dan Kennedy]

On 08/10/2016 12:47 AM, Jan Berkel wrote:

I'm currently implementing FTS5 in my application and I'm at the stage
where
I want to rank the results in an efficient way. I'm following the
examples
from "Appendix A: Search Application Tips"
(https://www.sqlite.org/fts3.html#appendix_a).

Similar to the example there I have a static weight component (a
separate rank
table with scores) which I want to combine with a query-specific ranking
function. The FTS4 example from the appendix reads:

   SELECT docid, rank(matchinfo(documents), documents_data.weight) AS
   rank
   ...
   ORDER BY RANK desc

The rank function gets the static score passed in and calculates a new
score,
taking the query into account.

In FTS5, rank functions are defined with

  SELECT ... WHERE fts MATCH ? AND rank MATCH 'functionName(parameters)'
  ORDER BY RANK

The parameters need to be SQL literals, therefore I can't
pass in arbitrary data as done in the previous FTS4 example.

What would be the best way to pass the data to the function, in order to
achieve similar results?
As far as I can tell the function does not have access to the "outer"
data.


If an auxiliary fts5 function is bound to the "rank" column, it cannot 
be passed extra parameters from the outer query. The reason is that when 
you use "ORDER BY rank", the virtual table module sorts the rows before 
returning them to the SQLite core. And of course, the virtual table 
module knows nothing about the outer query - it only knows that SQLite 
requested a set of matching rows in sorted order.


You could run a regular database query from within the auxiliary 
function implementation. Not sure how that would perform though - 
perhaps similarly to the FTS3/4 approach, perhaps a little better.


Or, you could add the "weight" as an UNINDEXED column to the fts5 
documents table. Then access it via the auxiliary function xColumn() API.


Dan.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Dan Kennedy]

On 08/09/2016 12:48 AM, Kevin O'Gorman wrote:

On Sun, Aug 7, 2016 at 11:11 PM, Dan Kennedy <danielk1...@gmail.com> wrote:


On 08/08/2016 02:03 AM, Dominique Pellé wrote:


Kevin O'Gorman wrote:

CREATE INDEX has two problems:

1) poor default location of temporary storage.
2) gets wedged on very large indexes.

I'm using the sqlite that came with Xubuntu 14.04, I think it's version
3.8.2.


SQLite-3.8.2 is old (Dec 2013). It's better to download and
compile SQLite yourself.

There has been several optimizations since that release.
In particular, looking at release notes at
https://sqlite.org/changes.html the following
improvement which may be relevant for your issue:

=== BEGIN QUOTE https://sqlite.org/changes.html ===
3.8.10:

Performance improvements for ORDER BY, VACUUM,
CREATE INDEX, PRAGMA integrity_check, and
PRAGMA quick_check.
=== END QUOTE ===


3.8.7 introduced the multi-threaded sorter too. So with a more recent
version of SQLite,

   PRAGMA threads = 4

might help this case.

   https://sqlite.org/pragma.html#pragma_threads

Dan.


Very cool.  But 4?  I will be running this on machines with 8 and 16
cores.  Does going beyond 4 not help much?


I don't know. If you have 8 or 16 cores it very well might.

Dan.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in CREATE INDEX

[Dan Kennedy]

On 08/08/2016 02:03 AM, Dominique Pellé wrote:

Kevin O'Gorman wrote:


CREATE INDEX has two problems:
1) poor default location of temporary storage.
2) gets wedged on very large indexes.

I'm using the sqlite that came with Xubuntu 14.04, I think it's version
3.8.2.

SQLite-3.8.2 is old (Dec 2013). It's better to download and
compile SQLite yourself.

There has been several optimizations since that release.
In particular, looking at release notes at
https://sqlite.org/changes.html the following
improvement which may be relevant for your issue:

=== BEGIN QUOTE https://sqlite.org/changes.html ===
3.8.10:

Performance improvements for ORDER BY, VACUUM,
CREATE INDEX, PRAGMA integrity_check, and
PRAGMA quick_check.
=== END QUOTE ===


3.8.7 introduced the multi-threaded sorter too. So with a more recent 
version of SQLite,


  PRAGMA threads = 4

might help this case.

  https://sqlite.org/pragma.html#pragma_threads

Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

[Dan Kennedy]

On 08/06/2016 09:52 AM, Kevin O'Gorman wrote:

On Fri, Aug 5, 2016 at 2:03 PM, Dan Kennedy <danielk1...@gmail.com> wrote:


On 08/06/2016 03:28 AM, Kevin O'Gorman wrote:


On Fri, Aug 5, 2016 at 1:08 PM, David Raymond <david.raym...@tomtom.com>
wrote:

..

Apart from the default location of the files, it reads like your next main

concern is how many temp files get opened up. My bet is that it'll be a
very small number, just potentially huge in file size while it's doing
its
thing. But again, try that pragma and take a look.

My best bet is the contrary:  it starts with small files and makes

increasingly larger ones, like the sort utility does.  The problem is that
there are too many of them at the beginning for it to work with anonymous
files (which sort does not use).  This at least offers a possible
explanation of its getting wedged on large indexes: an unexpected and
untested error, handled poorly.


You could verify this by checking the number of open handles in
"/proc//fd" after your process is wedged.

Excellent idea.  I did not know about that possibility.  And sure enough,

I'm wrong.  It's using anonymous files, all right, but only one or two at a
time.  I assume they're big.  I'm in the process of bracketing where size
begins to matter.  So far, 1/10 of the data loads and indexes just fine,
albeit somewhat more slowly that the smaller samples predicted.  The
database load took 6.5 minutes, the troublesome index 10 minutes.  At
smaller sizes, indexing is faster than the database load.

I'm trying 1/3 now (500 million lines)


What does [top] tell you once the process becomes wedged? What 
percentage is the CPU running at? Or is it completely bogged down 
waiting for IO?


Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

[Dan Kennedy]

On 08/06/2016 03:28 AM, Kevin O'Gorman wrote:

On Fri, Aug 5, 2016 at 1:08 PM, David Raymond 
wrote:


There's a depreciated pragma, PRAGMA temp_store_directory =
'directory-name'; that apears to still work ok for now at least.
http://www.sqlite.org/pragma.html#pragma_temp_store_directory

I've used it for the same reasons you've both mentioned about space,
though for me it vacuuming a huge db, and the full size db copy that makes
which ate up my entire C drive. But with that pragma you can specify
exactly which folder you want to use for your temp files. I'd suggest try
using that, then monitor the folder you give it to see what shows up.


Since it's deprecated, I'll stick with TMPDIR because it's pretty standard
on Linux (it also works with the sort utility for instance).



Apart from the default location of the files, it reads like your next main
concern is how many temp files get opened up. My bet is that it'll be a
very small number, just potentially huge in file size while it's doing its
thing. But again, try that pragma and take a look.


My best bet is the contrary:  it starts with small files and makes
increasingly larger ones, like the sort utility does.  The problem is that
there are too many of them at the beginning for it to work with anonymous
files (which sort does not use).  This at least offers a possible
explanation of its getting wedged on large indexes: an unexpected and
untested error, handled poorly.


You could verify this by checking the number of open handles in 
"/proc//fd" after your process is wedged.











-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Kevin O'Gorman
Sent: Friday, August 05, 2016 3:41 PM
To: SQLite mailing list
Subject: Re: [sqlite] newbie has waited days for a DB build to complete.
what's up with this.

On Fri, Aug 5, 2016 at 12:30 PM, Igor Korot  wrote:


Hi, Kevin,

On Fri, Aug 5, 2016 at 3:18 PM, Kevin O'Gorman 
wrote:

Okay, I followed some of the advice y'all gave and got some results.

1. The original problem was compromised by malformed input.  However,

it

appears that did not cause the wedging of the process.  See (3) below.

Where are the data will come?
 From the user? Internet?

What I'm getting at is - you need to look for some malformed data in
the future as well.


I generate it.  I goofed, and I'll try not to goof in the future.



2. I separated the steps, and started small.  Time increased slightly
sub-linearly with dataset size, so I jumped to doing the whole thing.

With

proper input, the data was loaded in 68 minutes.

3. The CREATE INDEX steps failed quickly (2 minutes), reporting

"database

or disk is full" which seemed odd since most of my partitions have much
more free space than the entire database.  It turns out that whatever

does

the creation was using space on my root partition (this is Linux, so

that

means "/").  That's the only partition in my setup without a huge

amount

of

free space.  On would expect temporary stuff to go to /tmp (which has

3TB

free), but it doesn't go there.  I would go there if the system's

native

"sort" program were used.  Fortunately, it turns out that the TMPDIR
environment variable is honored, but while I could see space was being
used, there were no files visible.  I take that to mean that the

tmpfile()

function (or equivalent) was used.  This could be a bad idea for large
indexes because anonymous files have to be kept open, and there's a

limit

on the number of files that can be open at a time, around 1,000.  Sure
enough, the index creation appears to be wedged like the original run,

and

after a few hours I killed it manually.  This is a deal-killer.

The failure you saw - is it on the table with the complete data set?
Or you got it during the experimenting?

Only on the complete data set.



So the questions are: Where do bug reports go?  I seem to be running

3.8.2;

is this fixed in any later version?

You can try the "3.14" pre-released one right now. ;-)


Meh.  I submitted a bug report to this list.  I'll see what happens.



Thank you.



On Thu, Aug 4, 2016 at 9:27 AM, Kevin O'Gorman <

kevinogorm...@gmail.com>

wrote:


The metric for feasability is coding ease, not runtime.  I'm the
bottleneck, not the machine, at least at this point.

As for adding rows, it will be about like this time: a billion or so

at

a

time.  But there's no need to save the old data.  Each round can be
separate except for a persistent "solutions" table of much more modest
size.  I've been doing this for a while now, and the solutions file

has

only 10 million or so lines, each representing a game position for

which

optimum moves are known.  Getting this file to include the starting
position is the point of the exercise.

If I ever get to anything like "production" in this project, I expect

it

to run for maybe three years...  That's after I tweak it 

Re: [sqlite] Strange SQLite bug(?!)

[Dan Kennedy]

On 08/02/2016 01:01 PM, skywind mailing lists wrote:

HI,

it seems to be that I am not allowed to add zip files to these e-mails.


Thanks for reporting this. Should now be fixed here:

  http://sqlite.org/src/info/e64a4173d2899acf

Dan.





This is what I am doing to create the database:

ExecuteStatement(databaseHandle,"CREATE TABLE A(ID INTEGER PRIMARY 
KEY,AnotherID INTEGER, Notes TEXT);");
ExecuteStatement(databaseHandle,"CREATE INDEX A_1 ON A 
(AnotherID);");
ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE AFTS USING 
FTS3 (Notes);");
ExecuteStatement(databaseHandle,"CREATE TRIGGER A_DeleteTrigger 
AFTER DELETE ON A FOR EACH ROW BEGIN DELETE FROM AFTS WHERE rowid=OLD.ID; END;");
ExecuteStatement(databaseHandle,"CREATE TRIGGER A_InsertTrigger 
AFTER INSERT ON A FOR EACH ROW BEGIN INSERT INTO AFTS (rowid,Notes) VALUES 
(NEW.ID,NEW.Notes); END;");
ExecuteStatement(databaseHandle,"INSERT INTO A (AnotherID,Notes) 
VALUES(1,'Record A1');");

ExecuteStatement(databaseHandle,"CREATE TABLE B(ID INTEGER PRIMARY 
KEY,Notes TEXT);");
ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE BFTS USING 
FTS3 (Notes);");
ExecuteStatement(databaseHandle,"CREATE TRIGGER B_DeleteTrigger 
AFTER DELETE ON B FOR EACH ROW BEGIN DELETE FROM BFTS WHERE rowid=OLD.ID; END;");
ExecuteStatement(databaseHandle,"CREATE TRIGGER B_InsertTrigger 
AFTER INSERT ON B FOR EACH ROW BEGIN INSERT INTO BFTS (rowid,Notes) VALUES 
(NEW.ID,NEW.Notes); END;");
ExecuteStatement(databaseHandle,"INSERT INTO B (Notes) 
VALUES('Record B1');");

To reproduce the error I run the following commands firstly with „#if 1“ and the 
second time with „#if 0":

if (sqlite3_open_v2("Test.sldb",,SQLITE_OPEN_CREATE | 
SQLITE_OPEN_READWRITE,NULL) == SQLITE_OK)
{
ExecuteStatement(databaseHandle,"BEGIN TRANSACTION;");

#if 0
ExecuteStatement(databaseHandle,"CREATE TABLE A(ID INTEGER PRIMARY 
KEY,AnotherID INTEGER, Notes TEXT);");
ExecuteStatement(databaseHandle,"CREATE INDEX A_1 ON A 
(AnotherID);");
ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE AFTS USING 
FTS3 (Notes);");
ExecuteStatement(databaseHandle,"CREATE TRIGGER A_DeleteTrigger 
AFTER DELETE ON A FOR EACH ROW BEGIN DELETE FROM AFTS WHERE rowid=OLD.ID; END;");
ExecuteStatement(databaseHandle,"CREATE TRIGGER A_InsertTrigger 
AFTER INSERT ON A FOR EACH ROW BEGIN INSERT INTO AFTS (rowid,Notes) VALUES 
(NEW.ID,NEW.Notes); END;");
ExecuteStatement(databaseHandle,"INSERT INTO A (AnotherID,Notes) 
VALUES(1,'Record A1');");

ExecuteStatement(databaseHandle,"CREATE TABLE B(ID INTEGER PRIMARY 
KEY,Notes TEXT);");
ExecuteStatement(databaseHandle,"CREATE VIRTUAL TABLE BFTS USING 
FTS3 (Notes);");
ExecuteStatement(databaseHandle,"CREATE TRIGGER B_DeleteTrigger 
AFTER DELETE ON B FOR EACH ROW BEGIN DELETE FROM BFTS WHERE rowid=OLD.ID; END;");
ExecuteStatement(databaseHandle,"CREATE TRIGGER B_InsertTrigger 
AFTER INSERT ON B FOR EACH ROW BEGIN INSERT INTO BFTS (rowid,Notes) VALUES 
(NEW.ID,NEW.Notes); END;");
ExecuteStatement(databaseHandle,"INSERT INTO B (Notes) 
VALUES('Record B1');");
#endif

ExecuteStatement(databaseHandle,"DELETE FROM A WHERE 
AnotherID=1;");
ExecuteStatement(databaseHandle,"DELETE FROM B WHERE ID=1;");

ExecuteStatement(databaseHandle,"COMMIT;");
sqlite3_close(databaseHandle);
} /* if */
else
std::cout << sqlite3_errmsg(databaseHandle) << std::end;

I could not reproduce the error from the shell.

Regards,
Hartwig


Am 2016-08-02 um 07:56 schrieb skywind mailing lists :

Hi,

I have added a database but it seems to be that the zip file got lost.

Regards,
Hartwig



Am 2016-08-02 um 01:13 schrieb Richard Hipp :

Do you have a database schema to go with your sample program?

On 8/1/16, skywind mailing lists > wrote:

Hi,

I have got a database that works using SQLite 3.7.7 but not with version
SQLite 3.13.0. I get the assertion

Assertion failed: (((Fts3Table *)pVtab)->mxSavepoint < iSavepoint), function
fts3SavepointMethod, file /.../sqlite/sqlite3.c, line 144649.

I have compiled the SQLite amalgamation with the options SQLITE_DEBUG=1
SQLITE_MEMDEBUG=1 SQLITE_THREADSAFE=1 SQLITE_ENABLE_RTREE=1
SQLITE_ENABLE_FTS3=1 SQLITE_ENABLE_FTS3_PARENTHESIS=1. I use this program to
reproduce the assertion:

#include 

#include "sqlite3.h"

static void ExecuteStatement(sqlite3* databaseHandle, std::string const&
sqlStatement)
{
int   result;

Re: [sqlite] sqlite3_scrub_backup() -> database disk image is malformed

[Dan Kennedy]

On 07/26/2016 04:49 PM, Ralf Junker wrote:
I use the following C code to test sqlite3_scrub_backup() [1]. 
Unfortunately, it results in a malformed database disk image.


Also, I am surprised that VACUUMing the original database produces a 
smaller file than sqlite3_scrub_backup(). Should they not be the same 
size?


Is this a problem with scrub.c or with my code?


Hi Ralf,

The corrupt database thing is unexpected, of course. It might be fixed here:

  http://sqlite.org/src/info/483994a54dee3c7a

Please let us know if you get the chance to try the fix above with your 
databases.


Unlike VACUUM, sqlite3_scrub_backup() should not usually shrink the 
database. scrub_backup() simply creates a copy of the original database 
with any unused parts of the file (i.e. free pages or empty space within 
b-tree pages) zeroed out. Whereas VACUUM rebuilds the db from the ground 
up so that it is as small as possible.


Dan.








Ralf

[1] http://www.sqlite.org/src/artifact?ci=trunk=ext/misc/scrub.c

---

#include 
#include "sqlite3.h"

sqlite3 *db;

static void check(int r, int e) {
  if (r != e) {
printf ("ERROR %d %s\n", e, sqlite3_errmsg(db));
  }
}

static int callback (void *user, int nCol, char **r, char **c) {
  int i;
  for (i = 0; i < nCol; i++) {
printf("%s ", r[i]);
  }
  printf("\n");
  return 0;
}

#define Test1Db "test1.db3"
#define Test2Db "test2.db3"

int main(void)
{
  char *zErrMsg = 0;

  check(0, remove (Test1Db));
  check(SQLITE_OK, sqlite3_open (Test1Db, ));

  check(SQLITE_OK, sqlite3_exec(db,
"DROP TABLE IF EXISTS t;"
"CREATE TABLE t(a, b, c);"
"WITH r(i) AS ("
"  SELECT 1 UNION ALL SELECT i+1 FROM r WHERE i<1000"
")"
"INSERT INTO t (rowid, a, b, c)"
"  SELECT i,zeroblob(100),zeroblob(100),zeroblob(100) FROM r;"
"SELECT count() FROM t;",
callback, NULL, NULL));

  check(SQLITE_OK, sqlite3_exec(db,
"DELETE FROM t WHERE rowid > 500;"
"SELECT count() FROM t;",
callback, NULL, NULL));

  check(SQLITE_OK, sqlite3_close(db));

  check(0, remove (Test2Db));
  check(SQLITE_OK, sqlite3_scrub_backup(
Test1Db, // Source database filename
Test2Db, // Destination database filename
 ));// Write error message here
  if (zErrMsg) {
printf ("%s", zErrMsg);
sqlite3_free(zErrMsg);
  }

  /* VACUUM database 1. */
  check(SQLITE_OK, sqlite3_open (Test1Db, ));
  check(SQLITE_OK, sqlite3_exec(db,
"VACUUM",
callback, NULL, NULL));
  check(SQLITE_OK, sqlite3_close(db));

  /* Integrity-check database 2. */
  check(SQLITE_OK, sqlite3_open (Test2Db, ));
  check(SQLITE_OK, sqlite3_exec(db,
"PRAGMA integrity_check;"
"SELECT count() FROM t;",
callback, NULL, NULL));
  check(SQLITE_OK, sqlite3_close(db));

  return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[Dan Kennedy]

On 07/23/2016 08:49 PM, R Smith wrote:



On 2016/07/23 3:30 PM, Simon Slavin wrote:

On 23 Jul 2016, at 2:24pm, R Smith  wrote:

No, it's not possible to forge permanent references to/from 
temporary tables - it defies the objective of being "temporary".
The other way around should fine, though: use a permanent table to 
ensure that only legitimate values appear in a temporary table.


  I will in fact be surprised to find out you can even reference 
another temp table... perhaps this might be possible since both 
tables will definitely cease to exist upon connection closure.
I don't see why this shouldn't be allowed.  Assuming that the 
'parent' temporary table has the appropriate index.  SQLite doesn't 
support CREATE TEMPORARY UNIQUE INDEX but it should understand that 
an index created on a temporary table is temporary.


Yes, your reasoning feels right, but I'm coming from the idea that 
references (much like Indices and such) are really database objects 
themselves, stand-alone units if you will, and they need to have 
concrete (non-temporary) linking or at least not mixed 
temporary-permanent linking. This is of course not necessarily true, 
and possibly quite different in SQLite (where it might be more like a 
check-constraint or such), so your point may have merit here and 
perhaps allowing referencing FROM a temporary table to a permanent 
table could simply be a minor tweak in some line of code inside SQLite.


The trouble is that some other process may come along and modify the 
permanent table, violating the FK constraint in the temporary schema.


Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error or Crash using OR in MATCH clause

[Dan Kennedy]

On 07/21/2016 11:16 PM, Julian Dohmen wrote:

I am encountering errors or crashes when using FTS5 and MATCH expressions 
involving
  OR 
combined with
  ORDER BY rank
=


Thanks for the detailed report. I think this one was fixed a couple of 
weeks after 3.13 was released:


  http://sqlite.org/src/info/64ca1a835a89fd21

The fix will be part of 3.14.

Dan.








Simplest example [using SQLite program]:
---
DROP TABLE IF EXISTS VTest;
CREATE virtual TABLE VTest USING FTS5(Title, AUthor, tokenize ='porter 
unicode61 remove_diacritics 1', columnsize='1', detail=full);
INSERT INTO VTest (Title, Author) VALUES ('wrinkle in time', 'Bill Smith');

SELECT * FROM VTest;
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time';
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time' ORDER BY rank
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time' 
ORDER BY rank;
ERROR
Error: database disk image is malformed

Note that the error [or commonly a crash in my system with a real table/data] 
seems to occur when the OR is combined with ORDER BY
These [simplified] results were obtained with the SQLite shell program built 
with SQLite 3.13.
=

SQLite build: [Windows 10, VS 2015] Preprocessor flags:
---
WIN32
_DEBUG
_WINDOWS
SQLITE_THREADSAFE=2
SQLITE_ALLOW_COVERING_INDEX_SCAN=1
SQLITE_ENABLE_STAT4=1
SQLITE_TEMP_STORE=2
SQLITE_ENABLE_FTS5=1
SQLITE_ENABLE_FTS4=1
SQLITE_ENABLE_FTS3=1
SQLITE_ENABLE_FTS3_PARENTHESIS=1


I also see this kind of problem in SQLiteStudio with SQLite 3.12.2 and [most 
importantly to us] in our application built with SQLite3.13 [with same flags 
above]
I assume that I misunderstood something about the syntax or flags, but 
reviewing the SQLite docs on FTS5 I don't see where I went wrong.
=

Further details:
---
In our program [32-bit debug build with above flags] with VS 2015 debugger set 
to trap exceptions we see this stack trace

  ()Unknown
  [Frames below may be incorrect and/or missing]
 Alexandria.exe!fts5MultiIterNext(Fts5Index * p, Fts5Iter * pIter, int 
bFrom, __int64 iFrom) Line 186755C
  Alexandria.exe!fts5MultiIterNextFrom(Fts5Index * p, Fts5Iter * pIter, 
__int64 iMatch) Line 187418C
  Alexandria.exe!sqlite3Fts5IterNextFrom(Fts5IndexIter * pIndexIter, 
__int64 iMatch) Line 189325C
  Alexandria.exe!fts5ExprNodeNext_TERM(Fts5Expr * pExpr, Fts5ExprNode * 
pNode, int bFromValid, __int64 iFrom) Line 181773C
  Alexandria.exe!fts5ExprNodeTest_AND(Fts5Expr * pExpr, Fts5ExprNode * 
pAnd) Line 181852C
  Alexandria.exe!fts5ExprNodeNext_AND(Fts5Expr * pExpr, Fts5ExprNode * 
pNode, int bFromValid, __int64 iFrom) Line 181891C
  Alexandria.exe!fts5ExprNodeNext_OR(Fts5Expr * pExpr, Fts5ExprNode * 
pNode, int bFromValid, __int64 iFrom) Line 181821C
  Alexandria.exe!sqlite3Fts5ExprNext(Fts5Expr * p, __int64 iLast) Line 
182079C
  Alexandria.exe!fts5NextMethod(sqlite3_vtab_cursor * pCursor) Line 191208  
  C
  Alexandria.exe!sqlite3VdbeExec(Vdbe * p) Line 83260C
  Alexandria.exe!sqlite3Step(Vdbe * p) Line 75131C
  Alexandria.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 75193C
  Alexandria.exe!fts5SorterNext(Fts5Cursor * pCsr) Line 191102C
  Alexandria.exe!fts5CursorFirstSorted(Fts5Table * pTab, Fts5Cursor * pCsr, 
int bDesc) Line 191303C
  Alexandria.exe!fts5FilterMethod(sqlite3_vtab_cursor * pCursor, int 
idxNum, const char * zUnused, int nVal, Mem * * apVal) Line 191588C
  Alexandria.exe!sqlite3VdbeExec(Vdbe * p) Line 83174C
  Alexandria.exe!sqlite3Step(Vdbe * p) Line 75131C
  Alexandria.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 75193C
  Alexandria.exe!nsFTS5SearchService::`anonymous namespace'::DB_Worker::operator()(const 
std::basic_string & csQuery, unsigned int 
nMaxToFind, 
std::vector & vecResp) Line 76C++

With this query:
SELECT T2TFTSrsn FROM T2TitleFTS WHERE  T2TitleFTS MATCH '{T2TFTStitle 
T2TFTSseries}:a wrinkle in time OR wrinkle in time' ORDER BY rank LIMIT 50;
=



Julian Dohmen | developer
Evelyn Manufacturing | Alexandria & KeepnTrack
801.943.7277 ext 565 | jdoh...@companioncorp.com

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question]SQLite cannot remove journal file as soon as UPDATE transaction finishes?

[Dan Kennedy]

On 07/11/2016 11:18 AM, 刘翔 wrote:

Dear SQLite developers,

Sqlite version: 3.8.8.3
Linux version: 3.10.31-ltsi

Question:
We found when updated a sqlite database in emmc disk, it cannot remove 
journal file as soon as the transaction finished.
We know after the transaction finishes, SQLite will unlink the journal 
file.
But when we update a database and shut down in about 2~3s, the journal 
file still exists after system startups.
It seems unlink() file would not remove it immediately, until kernel 
call sync() to flush disk.
So, does this correspond to SQLite's design? Or could you give me some 
suggestion how to remove the journal file quickly.


With version 3.11.0 or greater, either building with 
-DSQLITE_EXTRA_DURABLE or execute "PRAGMA synchronous=EXTRA" after 
connecting to the database causes SQLite to sync the directory after 
deleting the rollback journal.


With older versions, using "PRAGMA journal_mode=PERSIST" or "PRAGMA 
journal_mode=TRUNCATE" provides the same level of durability.


Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Dan Kennedy]

On 07/10/2016 06:21 AM, Igor Korot wrote:

Simon,

On Sat, Jul 9, 2016 at 7:09 PM, Simon Slavin  wrote:

On 10 Jul 2016, at 12:06am, Igor Korot  wrote:


I'm trying to write some software in C{++}. Everything works fine except
when I exit the program exit I get the error "Database is locked".
I am only trying to retrieve the information about the database (queries on
sqlite_master).

What command are you executing when you get "Database is locked" in return ?

sqlite3_close();


Have you terminated your query properly ?  Did you call sqlite_finalize() on it 
?  Did you get SQLITE_OK back from that call ?

Yes, everything is finalized. And no error on finalization is produced.
That's why I'm asking if there is a tool that can check what is open.


As I think you have surmised, you most likely have an unfinalized 
statement handle hanging around somewhere. After sqlite3_close() fails, 
use this:


  https://www.sqlite.org/c3ref/next_stmt.html

to loop through any unfinalized statement handles. If you find such a 
handle, this:


  https://www.sqlite.org/c3ref/sql.html

can be useful for figuring out where it came from.

Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Extending Porter Tokenizer

[Dan Kennedy]

On 07/10/2016 01:33 PM, Abhinav Upadhyay wrote:

On Fri, Jul 8, 2016 at 3:01 AM, Matthias-Christian Ott  wrote:

On 2016-07-05 18:11, Abhinav Upadhyay wrote:

I'm wondering if it is possible to extend the functionality of the
porter tokenizer. I would like to use the functionality of the Porter
tokenizer but before stemming the token, I want to decide whether the
token should be stemmed or not.

Do I need to copy the Porter tokenizer and modify it to suit my needs
or there is a better way, to minimize code duplication?

The first argument of the Porter tokenizer is its parent tokenizer. The
Porter tokenizer calls the parent tokenizer's xTokenize function with an
xToken function that wraps the xToken function that was passed to the
xTokenize function of the Porter tokenizer and stems the tokens passed
to it. So create a custom tokenizer that extracts the original xToken
function from the xToken member of its pCtx parameter:

typedef struct PorterContext PorterContext;
struct PorterContext {
   void *pCtx;
   int (*xToken)(void *pCtx, int tflags, const char *pToken, int nToken,
   int iStart, int iEnd);
   char *aBuf;
};

typedef struct CustomTokenizer CustomTokenizer;
struct CustomTokenizer {
   fts5_tokenizer tokenizer;
   Fts5Tokenizer *pTokenizer;
};

typedef struct CustomContext CustomContext;
struct CustomContext {
   void *pCtx;
   int (*xToken)(void *pCtx, int tflags, const char *pToken, int nToken,
   int iStart, int iEnd);
};

int customToken(
   void *pCtx,
   int tflags,
   const char *pToken,
   int nToken,
   int iStart,
   int iEnd
){
   CustomContext *c = (CustomContext*)pCtx;
   PorterContext *p;

   if( stem ){
 c->xToken(c->pCtx, tflags, pToken, nToken, iStart, iEnd);
   }else{
 p = (PorterContext)c->pCtx;
 return p->xToken(p->pCtx, tflags, pToken, nToken, iStart, iEnd);
   }
}

int customTokenize(
   Fts5Tokenizer *pTokenizer,
   void *pCtx,
   int flags,
   const char *pText,
   int nText,
   int (*xToken)(void *, int, const char *, int nToken, int iStart,
   int iEnd)
){
   CustomTokenizer *t = (CustomTokenizer)pTokenizer;
   CustomContext sCtx;
   sCtx.pCtx = pCtx;
   sCtx.xToken = xToken;
   return t->tokenizer.xTokenize(t->pTokenizer, (void*), flags,
   pText, nText, customToken);
}

Note that you are accessing an internal struct and relying on
implementation details and therefore have check whether the struct or
any other relevant implementation details changed with every release.

Thanks for the detailed response. I think this would work but we are
currently using FTS4. The ability of calling a parent tokenizer is
really what I needed, but I don't think this is possible with FTS4?


No way to do that with FTS4 unfortunately. I think you'll either need to 
switch to FTS5 or make a copy of the porter stemmer code and modify it 
to suit your purpose.


Dan.





-
Abhinav
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with savepoints

[Dan Kennedy]

On 07/06/2016 11:10 PM, Chris Brody wrote:

Just for my information, what is the purpose of this temporary file?  I see

that -journal file is always stored to disk.


It's a statement journal:

   https://www.sqlite.org/tempfiles.html#stmtjrnl

Recent changes mean that the first 64KiB of a statement journal are always
stored in main-memory, and the temp file only created after the file grows
larger than 64KiB. Which is probably why you did not see the problem
immediately.


Personally I wish SQLite would fail upon open or perhaps on first write if
it cannot write the temporary file. I think this would make it easier for
programmers to detect and deal with this kind of issue. Maybe make it an
explicit compile-time option to wait until the temporary file is larger
than 64KiB before opening the temp file. Just a thought.


Right now it works the other way around - you can explicitly build or 
configure SQLite to create statement/savepoint journals as soon as they 
are required:


  https://www.sqlite.org/compile.html#stmtjrnl_spill

Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with savepoints

[Dan Kennedy]

On 07/06/2016 10:52 PM, Jeff Archer wrote:

On Wed, Jul 6, 2016 at 10:46 AM, Dan Kennedy <danielk1...@gmail.com> wrote:


On 07/06/2016 09:09 PM, Jeff Archer wrote:


Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).


Group 8 however can only do 26 successful inserts and on the 27th

sqlite3_step() is returning SQLITE_CANTOPEN (14).



​​
SQLite is failing to create a temporary file, as there is nowhere
convenient to do this on Android. The usual fix is to build SQLite to store
temp files in memory:

   -DSQLITE_TEMP_STORE=3

   https://www.sqlite.org/compile.html#temp_store

Or execute
​​
"PRAGMA temp_store = memory" after opening your db connection.


​Dan, many thanks.  This does indeed solve my problem.

Just for my information, what is the purpose of this temporary file?  I see
that -journal file is always stored to disk.


It's a statement journal:

  https://www.sqlite.org/tempfiles.html#stmtjrnl

Recent changes mean that the first 64KiB of a statement journal are 
always stored in main-memory, and the temp file only created after the 
file grows larger than 64KiB. Which is probably why you did not see the 
problem immediately.


Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with savepoints

[Dan Kennedy]

On 07/06/2016 09:09 PM, Jeff Archer wrote:

Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).



Group 8 however can only do 26 successful inserts and on the 27th
sqlite3_step() is returning SQLITE_CANTOPEN (14).



SQLite is failing to create a temporary file, as there is nowhere 
convenient to do this on Android. The usual fix is to build SQLite to 
store temp files in memory:


  -DSQLITE_TEMP_STORE=3

  https://www.sqlite.org/compile.html#temp_store

Or execute "PRAGMA temp_store = memory" after opening your db connection.

Dan.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about Precompiled Binaries for Android

[Dan Kennedy]

On 06/18/2016 07:06 AM, 风 wrote:

hi,


sqlite.org provided the Precompiled Binaries for Android(*.aar),
where to download the source code ?  (.java, jni)


http://www.sqlite.org/android/doc/trunk/www/index.wiki

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Preventing certain query keywords from getting stemmed

[Dan Kennedy]

On 05/30/2016 12:00 AM, Abhinav Upadhyay wrote:

Hi,

While running queries, sometimes there are technical keywords which
shouldn't be stemmed by the tokenizer. For example, if I query for
"lfs" (which is a file system), the porter stemmer, converts it to
"lf", which matches many other unrelated keywords in the corpus (such
as ascii lf or some other acronyms).

I'm wondering if there is an option to tell the tokenizer not to stem
certain keywords and take them as it is?


No way to do that with any of the built-in tokenizers for either fts3/4 
or fts5. The only way would be to write a custom tokenizer.


  https://www.sqlite.org/fts5.html#section_7_1

Dan.




-
Abhinav
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: FTS5 query containing OR + order by rank

[Dan Kennedy]

On 05/29/2016 04:22 AM, Jan Berkel wrote:

I'm getting "database disk image is malformed" errors when running a
query containing OR and ordering by
rank against a FTS5 index:

  
$ sqlite3

SQLite version 3.13.0 2016-05-18 10:57:30
sqlite> create virtual table foo using fts5(bar);
sqlite> insert into foo values('test');
sqlite> select rowid from foo where foo match 'test' order by rank;
rowid
1
sqlite> select rowid from foo where foo match 'test OR foo' order by
rank;
Error: database disk image is malformed


Thanks for reporting this. As you no doubt surmised, the db is not 
corrupt but a bug in FTS5 is causing it to report so. Now fixed here:


  http://sqlite.org/src/info/64ca1a835a89fd21

Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

[Dan Kennedy]

On 05/25/2016 10:54 PM, 박병언 wrote:

First of all, my English skill is not good. So please forgive me if my
sentences are rude.

I saw this is for anonymous user to report bug. I'm not sure If I need to
write down my personal information. So I didn't write my name. If I need
to, I'll send this information.

I am a Computer Science Master student in South Korea. I'm doing a research
to enhance the SQLite performance.



In PERSIST mode, journal is still remain. But the red code is always
generated in each transaction. That is, journal file descriptor is always
closed at the end of transaction and reopen at the start of next
transaction(I think this is a bug).
Do you think sync journal's directory per transaction is correct?
If this opinion is wrong, would you mind if I know why
 1. the directory must sync per transaction?
 2. PERSIST mode must close the journal file descriptor?




On UNIX, it's possible to delete a file from the file-system while 
another process has it open. In this case the other process can continue 
reading and writing its file-descriptor as normal, but the data is 
stored in memory only, not on disk (since the directory entry has been 
deleted). Once the process exits or closes the file-descriptor, the data 
is lost.


This means that if a connection using "PRAGMA journal_mode = PERSIST" 
keeps the journal file open while the database is unlocked (i.e. between 
transactions), some other process using "PRAGMA journal_mode = DELETE" 
might come along and delete the journal file while our "journal_mode = 
PERSIST" process still has it open. In this case if the "journal_mode = 
PERSIST" process then tries to execute another transaction and there is 
a power failure halfway through, there will be no journal file on disk 
following system recovery. Database corruption.


For this reason SQLite always closes the journal file at the end of a 
transaction on UNIX. On windows, where it is not possible to delete a 
file if another process has it open, SQLite holds the journal file open 
between transactions.


Dan.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite fails on rtree index creation (NuGet install)

[Dan Kennedy]

On 05/24/2016 01:16 AM, Joe Mistachkin wrote:
> Jason Doherty wrote:
>> CREATE VIRTUAL TABLE IF NOT EXISTS TASK_SPX USING rtree(id, minx, maxx,
>> miny, maxy);
>>
>> fails with
>>
>> SQLite error (1); no such table: main.sqlite_stat1
>>
> Could you run the SQL query "ANALYZE;" on the database and see if that
> clears
> the issue?

On the off chance that it doesn't completely clear the issue but does 
change the error message to one that makes more sense, can you post the 
new error here? Thanks.

Dan.

[sqlite] Calling FTS tokeniser functions manually

[Dan Kennedy]

On 05/13/2016 09:19 PM, Matt Hamilton wrote:
> Hi all,
>Anyone know if/how you can call the FTS5 tokeniser functions manually? 
> e.g. I want to look something up in the fts5vocab table but can't as I need 
> to split/stem the initial value first before querying the table?
>
> To illustrate:
>
> sqlite> CREATE VIRTUAL TABLE ft1 USING fts5(x, tokenize = porter);
> sqlite> INSERT INTO ft1 VALUES('running man');
> sqlite> CREATE VIRTUAL TABLE ft1_v_row USING fts5vocab(ft1, row);
> sqlite> SELECT * FROM ft1_v_row;
> man|1|1
> run|1|1
> sqlite> SELECT count(*) FROM ft1_v_row WHERE term = 'running';
> 0
> sqlite>
>
> How can I somehow map 'running' => 'run' in order to query the fts5vocab 
> table to get stats on that term? And how could I tokenise 'running man' => 
> 'run', 'man' in order to look up multiple tokens?

I think the only way to do that at the moment is from C code using the 
API in fts5.h:

   https://www.sqlite.org/fts5.html#section_7

Use xFindTokenizer() to grab a handle for the desired tokenizer module, 
then xCreate to create an instance and xTokenize to tokenize text.

There is example code in the fts5_test_tok.c file:

   http://sqlite.org/src/artifact/db08af63673c3a7d

The example code creates a virtual table module that looks useful enough:

   CREATE VIRTUAL TABLE ttt USING fts5tokenize('porter');

then:

   SELECT * FROM ft1_v_row WHERE term IN (SELECT token FROM ttt('running 
man'));

should probably work. More information in fts5_test_tok.c.

Dan.

[sqlite] sqlite3_auto_extension + custom FTS5 tokenizer

[Dan Kennedy]

On 05/11/2016 05:24 PM, Jan Berkel wrote:
> I?m currently implementing a custom FTS5 tokenizer which I?d like to 
> automatically register for each db connection.
>
> So I tried to register an extension hook with sqlite3_auto_extension but when 
> my code is called the FTS_* modules have not been initialized, because 
> sqlite3Fts5Init() is called *after* sqlite3AutoLoadExtensions(db).
>
> Therefore registering the tokenizer is not possible at this stage, since the 
> fts5() function is not defined yet.
>
> Is there another way? I can?t use dynamic extensions so need to use 
> sqlite3_auto_extension or something similar.

That sounds like a problem.

I think you could:

* build SQLite without SQLITE_ENABLE_FTS5,
* include the file "fts5.c" in the application build, and
* call sqlite3_fts5_init() from within the auto_extension() callback 
before creating the tokenizer.

To generate fts5.c, grab the full source package (either from here - 
http://sqlite.org/2016/sqlite-src-3120200.zip - or from fossil) and run 
"./configure && make fts5.c".

You can't just call sqlite3_fts5_init() from the auto_extension() 
callback in an SQLITE_ENABLE_FTS5 build, as the call to 
sqlite3Fts5Init() made later will replace the existing fts5 module with 
a new one - one that does not have your custom tokenizer registered with it.

I think we should probably change that last bit so that calling 
sqlite3_fts5_init() on a db handle that already has an fts5 extension 
registered with it is a no-op. That might not happen until after the 
3.13 release though.

Dan.

[sqlite] Fastest way to backup/copy database?

[Dan Kennedy]

On 05/05/2016 12:45 AM, Rob Willett wrote:
> Ryan,
>
> Ah! The penny drops, we didn?t realise that with the backup API. 
> That explains a great deal. We must have missed that in the docs. Blast.


There is one exception to this:

If the database is written to via the same database handle that is being 
used as the source db by the backup API, then the backup is not 
restarted. In this case if any pages that have already been transferred 
to the backup db are modified the new versions are written into the 
backup db at the same time as the source is updated.

Dan.





>
> We?ve looked around for other providers in Europe and the cost 
> differences are very high. We need to be in the EU for various data 
> protection reasons. Until now we haven?t had any issues as we don?t 
> move a significant amount of data around in a very short period of 
> time, so the rate limited IO has not been a problem.
>
> One of our options is to do what you suggest with a second database 
> server and run them hot/warm. We had already thought of that but not 
> got around to it as the setting up time is quite high (we need a bank 
> of servers, feeding things from one server to another), but our 
> immediate issue is simply copying the 10GB database. The downside of 
> the second server is moving 10GB data files around the internet 
> afterwards back to the failed server. Rebuilding from scratch is a 
> pain as it takes around 2-3 weeks to rebuild the database from scratch 
> as we have to process every file again (circa 200,000) in order and 
> each file takes around 4-8 secs to run.
>
> I think the backup solution is the tried and tested Keep-It-Simple 
> shell script. We pause the queue upstream which stops the update 
> process, do a cp and then restart the queue again. All of this is 
> doable in shell script.
>
> Rob
>
> On 4 May 2016, at 18:22, R Smith wrote:
>
>> On 2016/05/04 2:35 PM, Rob Willett wrote:
>>> Dominque,
>>>
>>> We put together a quick C program to try out the C API a few weeks 
>>> ago, it worked but it was very slow, from memory not much different 
>>> to the sqlite command line backup system. We put it on the back 
>>> burner as it wasn?t anywhere near quick enough.
>>
>> You do realize that the backup API restarts the backup once the 
>> database content changes, right? I'm sure at the rates you describe 
>> and update frequency, that backup would never finish. The backup API 
>> is quite fast if your destination file is on a not-too-slow drive, 
>> but you will have to stop the incoming data to allow it to finish.
>>
>> As an aside - you need a better provider, but that said, and if it 
>> was me, I would get two sites up from two different providers, one 
>> live, one stand-by, both the cheap sort so costs stay minimal 
>> (usually two cheap ones are much cheaper than the next level beefy 
>> one). Feed all updates/inserts to both sites - one then is the backup 
>> of the other, not only data-wise, but also can easily be switched to 
>> by simple DNS redirect should the first site/provider go down for any 
>> reason.  The second site can easily be interfered with / copied from 
>> / backed up / whatever without affecting the service to the public.
>>
>> I only do this with somewhat critical sites, but your use-case sounds 
>> like it might benefit from it. My second choice would be to simply 
>> stop operations at a best-case time-slot while the backup / copy 
>> completes.
>>
>> Cheers,
>> Ryan
>>
>>
>>
>> ___
>> 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

[sqlite] Outer Join with Where terms - possible optimization

[Dan Kennedy]

On 04/29/2016 09:41 PM, Josef Ku?era wrote:
> Hello,
> I have discovered a possible query planner improvement. I am using the SQLite 
> with virtual table modules.
>
> Situation 1: Lets have a query like 'SELECT V.* FROM (select A.RowID A_ROWID, 
> B.RowID B_ROWID, A.*, B.* from A left outer join B on B.ref=A.key) V WHERE 
> V.B_ROWID=?', where A and B are virtual tables. If I am not mistaken the 
> WHERE term effectively turns the outer join to an inner join and the query 
> could be evaulated as two fast key searches. Unfortunately, currently the 
> join is left as is and the execution peforms a sequentail full-scan of the A 
> table with many key searches to the B table.
>
> Situation 2: How does SQLite evaluate tables to the left of a left join? As 
> it seems it is done by order in the SQL. So query like this: select * from A 
> join B on B.ref=A.key left outer join C on C.ref=A.key where C.Value=? causes 
> a full scan of A and B before even evaulating C. By looking at the query the 
> B table does not seem to be a pre-requisite of table C, although is it marked 
> as one in where trace.

At the moment, SQLite processes everything to the left of the LEFT (or 
CROSS) JOIN before everything to the right of it.

Dan.

[sqlite] Multiple in-memory database table query

[Dan Kennedy]

On 04/22/2016 02:59 PM, Dominique Devienne wrote:
>> But, if your process has two separate database handles and they both
>> attach "file:data.db?mode=memory=shared" then both handles will be
>> connected to the same in-memory database.
>
> This is exactly what we are doing. Now I keep on reading in this list
> cache=shared is for constrained devices and one shouldn't use it ever.
>
> But how else to use an in-memory database from multiple threads and
> leverage table-level locking as opposed to DB-level locking?
>
> We are heavy users of vtables and in-memory DBs, plural, within the same
> process. And would like to have some //ism on tables.
> I'd even wish for WAL-type MVCC for in-memory databases to avoid locks even
> more. Is WAL mode supported for in-memory DBs?

Not supported. You could create a custom VFS that worked with wal mode 
but stored all files in memory instead of writing them out to the 
file-system. I don't think anyone has published anything like that yet 
though.

>
> What is "wrong" with shared cache that people keep saying don't use it?
> Dan, Richard, is shared cache broken somehow?

For many applications, using shared-cache mode with on-disk databases 
decreases concurrency, especially for wal mode databases. And a lot of 
the time the memory saved doesn't offer any perceptible performance 
benefits.

So I guess the standard advice should really be "Don't turn this on just 
because it sounds good, wait until you have a reason." - like wanting to 
access an in-memory database from more than one database handle within a 
process.

It's not broken in any way to my knowledge.

Dan.

[sqlite] Multiple in-memory database table query

[Dan Kennedy]

>   Generally speaking, no matter how they are created, a given in-memory 
> database has one and only one connection.  You cannot, for example, use a URI 
> ?filename? with mode=memory to open the same in-memory database more than 
> once (I assume that?s what you mean by ?by name??).  For example:
>
>
> $ ./sqlite3
> SQLite version 3.8.4.2 2014-03-26 18:51:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> ATTACH DATABASE "file:data.db?mode=memory" AS db1;
> sqlite> ATTACH DATABASE "file:data.db?mode=memory" AS db2;
> sqlite> ATTACH DATABASE "file:data.db?mode=memory" AS db3;
> sqlite> .databases
> seq  name file
> ---  ---  
> --
> 0main
> 2db1
> 3db2
> 4db3
> sqlite> CREATE TABLE main.t_main ( c );
> sqlite> CREATE TABLE db1.t_db1( c );
> sqlite> CREATE TABLE db2.t_db2( c );
> sqlite> select * from main.sqlite_master;
> table|t_main|t_main|2|CREATE TABLE t_main ( c )
> sqlite> select * from db1.sqlite_master;
> table|t_db1|t_db1|2|CREATE TABLE t_db1( c )
> sqlite> select * from db2.sqlite_master;
> table|t_db2|t_db2|2|CREATE TABLE t_db2( c )
> sqlite> select * from db3.sqlite_master;
> sqlite>
>
> You can see that even though I?ve opened the same ?file:data.db?mode=memory? 
> database more than once, it is actually three distinct databases.  I?m pretty 
> sure that when mode=memory, the path/filename are ignored.

If you open the databases using "file:data.db?mode=memory=shared" 
then db1, db2 and db3 will all refer to the same in-memory database. 
Which will fail, as you cannot attach the same db to a single handle 
more than once. But, if your process has two separate database handles 
and they both attach "file:data.db?mode=memory=shared" then both 
handles will be connected to the same in-memory database.

Dan.

[sqlite] Field FOREIGN KEY when it is a pointer

[Dan Kennedy]

On 04/20/2016 11:21 PM, Cecil Westerhof wrote:
> 2016-04-20 18:07 GMT+02:00 R Smith :
>
>>
>> On 2016/04/20 6:04 PM, Cecil Westerhof wrote:
>>
>>> I am thinking about creating a table where a certain field will be a
>>> description, or a key to the description. Would it be possible to have a
>>> constraint on this field that it is an id, that this id points to an
>>> existing record in a description table?
>>>
>>>
>> https://www.sqlite.org/foreignkeys.html
>
> ?If I read that correctly then a field has to be NULL or point to a record
> in the other table. I want it to point to a record if it is an INT and whem
> it is TEXT it is just the text. (And it should only be allowed to be an INT
> or TEXT.)

I don't think you can use a constraint in SQLite for that. You could 
write a trigger to check that only valid values are inserted into the 
table though (and the corresponding trigger  for updates, and for 
updates and deletes on the referenced table).

Dan.



> ?
>
>   --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible that dropping a big table takes very long

[Dan Kennedy]

On 04/16/2016 04:59 PM, Cecil Westerhof wrote:
> I am playing a bit with SQLite. I first had a table with 1E8 elements. When
> trying to drop this it looked like SQLite got hung. I tried it from DB
> Browser and a Java program.
> I just tried it with a table of 1E7 elements. That was dropped in about 13
> seconds.
> I will try it again with 1E8 elements, but it takes 4? hours to generated.
> Is it possible that SQLite has trouble dropping very large tables? It was
> 5.2 GB. With 1E7 elements the table is 512 MB.
>
> The definition of the table:
> CREATE TABLE testUniqueUUID (
>  UUIDblob,
>
>  PRIMARY KEY(UUID)
>  CHECK(TYPEOF(UUID) = 'blob'   AND
>LENGTH(UUID) = 16   AND
>SUBSTR(HEX(UUID), 13, 1) == '4' AND
>SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
>  )
> );
>

Are there any other tables in the db with foreign keys that refer to the 
table being dropped?

If so, try with foreign keys disabled:

   PRAGMA foreign_keys = 0;

Dan.

[sqlite] FTS5 query that to match all rows.

[Dan Kennedy]

On 04/13/2016 11:24 PM, John Found wrote:
> What FTS5 query should I use in order to match all rows in the table?
>

Can you use "SELECT * FROM fts_table;"?

Dan.

[sqlite] FTS5 problem ? more than 3,000 queries !

[Dan Kennedy]

On 04/04/2016 05:43 AM, Domingo Alvarez Duarte wrote:
> Hello !
>
> I'm testing fts5 on a database with data from hacker news (around 13M
> records) and it took more than 2 hours to index, I have this application to
> log all queries executed for debugging and although the search isn't slow
> it's making a lot of queries.
>
> Is this normal ? More than 3,000 queries ?

Probably. Your query does "ORDER BY rank" with "rank" set to the built 
in bm25() function. One of the things bm25() needs is the size of each 
document that it is ranking. And obtaining this requires a separate 
query on the %_sz table for each row. So if the MATCH expression matches 
3000 rows, FTS5 makes 3000 SQL queries to obtain the size data.

Dan.





>
> Cheers !
>
> 
>
> CREATE TABLE "items" (
>  'id' integer PRIMARY KEY,
>  'parent' INTEGER,
>  'by' text COLLATE NOCASE,
>  'score' integer DEFAULT 0,
>  'title' text  COLLATE NOCASE,
>  'type' text  COLLATE NOCASE,
>  'url' text  COLLATE NOCASE,
>  'deleted' BOOLEAN DEFAULT 0,
>  'dead' BOOLEAN DEFAULT 0,
>  'comment' TEXT COLLATE NOCASE,
>  'time' integer NOT NULL,
>  descendants integer default 0
> );
>
> CREATE VIRTUAL TABLE fts_idx_items USING fts5(title, comment, content=items,
> content_rowid=id, prefix=3);
>
>
> The query:
>
> SQL:  SELECT a.id, a.parent, a.by, a.score,
>  snippet(fts_idx_items, 0, '', '', '...',
> 12) as title,
>  a.type, a.url,
>  --snippet(fts_idx_items, 1, '', '',
> '...', 12) as comment,
>  a.descendants,
>  datetime(a."time", 'unixepoch') AS time,
>  (julianday('now') - julianday(datetime(a."time",
> 'unixepoch'))) as elapsed_time
>  FROM items a, fts_idx_items b
>  where fts_idx_items match 'title:sql'
>  and a.id = b.rowid
>  --and a.type != 'comment'
>  ORDER BY rank --bm25(fts_idx_items)
>  LIMIT 30 OFFSET 0
>
> Generates:
>
> SQL:-- SELECT rowid, rank FROM 'main'.'fts_idx_items' ORDER BY
> bm25(fts_idx_items) ASC
>
> Followed by 28 of this:
>
> SQL:-- SELECT pgno FROM 'main'.'fts_idx_items_idx' WHERE
> segid=? AND term<=? ORDER BY term DESC LIMIT 1
>
> Followed by around 3,000 of:
>
> SQL:-- SELECT sz FROM 'main'.'fts_idx_items_docsize' WHERE id=?
>   
>
> Followed by 18 of:
>
> SQL:-- SELECT T.'id', T.'title', T.'comment' FROM
> 'main'.'items' T WHERE T.'id'=?
>
> SQL:-- SELECT sz FROM 'main'.'fts_idx_items_docsize' WHERE
> id=?
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Debugging variable left in fts5_index.c?

[Dan Kennedy]

On 03/30/2016 04:38 PM, Ralf Junker wrote:
> These 2 lines seem to serve no purpose. Also, they are not indented 
> like the reset of the code. This makes me think they might have been 
> left in from debugging:
>
> http://www.sqlite.org/src/artifact/b271b19dd28d3501?ln=3819-3820

Thanks for reporting this. As you suspect, I accidentally left debugging 
code in. Will be removed in the next release.

Dan.

[sqlite] Broken database after experiments with fts.

[Dan Kennedy]

On 03/30/2016 12:14 AM, Cezary H. Noweta wrote:
> Hello,
>
> On 2016-03-26 15:37, John Found wrote:
>> Why cannot drop the table test?
>>
>> sqlite> begin transaction;
>> sqlite> create virtual table test using fts5;
>> Error: vtable constructor failed: test
>> sqlite> commit;
>
> It is not required. Non-commited-and-non-rolledback, dangling 
> transaction suppresses all operations on vtable ``test''. COMMIT is 
> required to damage database file permanently.
>
>> sqlite>
>> sqlite> drop table test;
>> Error: vtable constructor failed: test
>>
>> sqlite> .tables
>> test  test_content  test_docsize
>> test_config   test_data test_idx
>>
>> $sqlite3 --version
>> 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7
>
> It should not be possible to damage a database by an using of clean 
> SQL commands only. Hopefully SQLite has a helpful statement 
> transaction, which can be used in this case.
>
> I must stipulate that in the following is an ad-hoc solution, which 
> may be inaccurate and is included to illustrate a problem only.
>
> In ``vtab.c:sqlite3VtabBeginParse()'', at the beginning insert two lines:
> ==
>   sqlite3MultiWrite(pParse);
>   sqlite3MayAbort(pParse);
> ==


I think that's the right thing to do as well. See here:

   http://sqlite.org/src/info/d0a3853b37230c12

The fix should appear as part of the 3.13 release (not 3.12 - the 
version that was tagged earlier this morning).

Dan.

[sqlite] report a bug

[Dan Kennedy]

On 03/23/2016 10:48 AM, ? wrote:
> I want to report a bug.
>
>
> I write a tokenizer named thai?which is working according to the rule of thai 
> vowel,not by space.
>
>
> I build a table using fts5,like this,
> CREATE VIRTUAL TABLE tbl_tha using fts5( key1, key2,TOKENIZE="thai");
>
>
> and then insert a record:
> insert into tbl_tha  values('??','??');
>
>
> querying like this:
>
>
> SQL1:select * from tbl_tha   where tbl_tha  match '';
> SQL2:select * from tbl_tha   where tbl_tha  match '?? ??';
>
>
> SQL2 can query the result,but SQL1 return null;
>
>
> I have confirmed that,the tokenize can split  correctly to
> ??? ???  ??
>
>
> Is that a bug which can not query multi column?

I'm not 100% sure, but I don't think so. Fts5 parses query expressions 
according to the rules described here:

   https://www.sqlite.org/fts5.html#section_3

It can be complicated, but for queries like the above, it comes down to 
"split the input on whitespace". Once it is split, each component of the 
query is passed to the tokenizer. If the tokenizer returns more than one 
token, then these are handled in the same way as a phrase expression by 
fts5. So SQL1 is equivalent to:

  ... MATCH "??? + ??? +  + ??"


whereas SQL2 is:

  ... MATCH "(??? + ???) AND ( + ??)"

Maybe there should be an option for languages like Thai to tell FTS5 to 
handle this kind of thing as:

  ... MATCH "??? AND ??? AND  AND ??"

Dan.

[sqlite] FTS5 "constraint failed"

[Dan Kennedy]

On 03/23/2016 12:06 AM, Domingo Alvarez Duarte wrote:
> Hello !
>
> After seeing several times work/commits on fts5 I decided to try it on a
> table shown bellow, and when trying to populate it I get this error message:
>
>
> sqlite> INSERT INTO fts_idx_items(fts_idx_items) VALUES('rebuild');
> Error: constraint failed
>
> The table has 12,000,000 records and it show the error message after 10
> seconds working, any clue on what can be happening ?

Thanks for testing this. What does "SELECT sqlite_version();" return if 
you run it in the same shell?

Thanks,
Dan.

[sqlite] ASK matchinfo performance

[Dan Kennedy]

On 03/17/2016 12:27 PM, Christoforus Surjoputro wrote:
> Hi all. I've example database that can be downloaded here 
> http://www.megafileupload.com/rhqU/product.db or you can generate yourself by 
> using this python script:http://pastebin.com/4djPZJ21
>
> I try to compare performance between standard query and using matchinfo() 
> function.Standard queryt:http://pastebin.com/RFZwx8AJ
>
> matchinfo():http://pastebin.com/BBq28HNh
>
> Script that use matchinfo() function about 20x slower than standard query. Is 
> that normal or something wrong with my query or my database? And how to 
> improve the matchinfo() performance?

Because your query has a LIMIT 1 clause, FTS3 usually has to find just 
the first result that matches the full-text query.

However, a matchinfo call with the 'x' flag specified has to loop 
through all the rows that match the query, as one of the values returned 
by 'x' is the total number of matches in all rows. In FTS3 there is no 
way to get this information without iterating through all matching rows.

FTS5 is more faster and more flexible, IMO.

Dan.

[sqlite] ICU and FTS5

[Dan Kennedy]

On 03/10/2016 02:56 AM, Tim Uy wrote:
> Will the ICU tokenizer work with FTS5, or does some work need to be done to
> port it over (slight pun intended).

It doesn't work with FTS5.

Both FTS3/4 and FTS5 allow for user-defined tokenizers, but the 
interface is not the same. So the ICU tokenizer would need to be adapted 
or rewritten to work with FTS5.

Dan.

[sqlite] [BUG] 3.11.0: FTS3/4 index emptied by 'optimize' inside transaction

[Dan Kennedy]

On 03/09/2016 11:35 PM, Tomash Brechko wrote:
> Hello,
>
> With 3.11.0 if you run the following SQL you will get no result (which is
> wrong):
>
> -- cut --
> BEGIN;
> CREATE VIRTUAL TABLE fts USING fts4 (t);
> INSERT INTO fts (rowid, t) VALUES (1, 'test');
> INSERT INTO fts (fts) VALUES ('optimize');
> COMMIT;
> SELECT rowid FROM fts WHERE fts MATCH 'test';
> -- cut --
>
>

Thanks for reporting this. Now fixed here:

http://sqlite.org/src/info/79338b991bf01e81

Dan.

[sqlite] Creating & Dropping memory databases

[Dan Kennedy]

On 03/09/2016 12:48 AM, Simon Slavin wrote:
> On 8 Mar 2016, at 4:35pm, Dan Kennedy  wrote:
>
>> I don't think it does that. sqlite3_shutdown() is for embedded platforms to 
>> shutdown sub-systems initialized by sqlite3_initialize(). Calling it with 
>> open connections will usually either leak resources or crash.
>>
>> More here: https://www.sqlite.org/c3ref/initialize.html
> You're right:
>
> "All open database connections must be closed and all other SQLite resources 
> must be deallocated prior to invoking sqlite3_shutdown()."
>
> Hmm.  So there's no neat way to shut down SQLite if you've lost track of your 
> context.  Is there a way of asking SQLite for a list of connections ?

I don't think there is.

Dan.

[sqlite] Creating & Dropping memory databases

[Dan Kennedy]

On 03/08/2016 09:14 AM, Simon Slavin wrote:
> On 8 Mar 2016, at 2:10am, Stephen Chrzanowski  wrote:
>
>> Out of curiosity, from a memory management standpoint, if I create a memory
>> database, populate simple tables and such, to what falls the responsibility
>> with cleaning up the memory used?
>>
>> Will SQLite handle :memory: databases internally, flushing out the tables
>> from memory?
>>
>> Does my application have to handle dropping the tables before actually
>> closing the connection?
> You can manually DROP the table.
>
> If the table still exists when you close the connection SQLite will DROP the 
> table for you.
>
> If you call sqlite3_shutdown(void) while SQLite still has connections open it 
> will close the connections for you.

I don't think it does that. sqlite3_shutdown() is for embedded platforms 
to shutdown sub-systems initialized by sqlite3_initialize(). Calling it 
with open connections will usually either leak resources or crash.

More here: https://www.sqlite.org/c3ref/initialize.html

[sqlite] Changing the default page_size in 3.12.0

[Dan Kennedy]

On 03/04/2016 11:03 PM, Dominique Devienne wrote:
> On Fri, Mar 4, 2016 at 4:48 PM, Richard Hipp  wrote:
>
>>  https://www.sqlite.org/draft/releaselog/3_12_0.html
>
> - from 2000 to -2000
> + from 2000 to 500
>
> [OT] The PRAGMA defer_foreign_keys=ON statement now also disables RESTRICT
> actions on foreign key.
>
> I've confused by that Richard. Does that mean handling of the RESTRICT is
> also postponed to commit-time,
> or that RESTRICT is not honored in deferred FK mode (and thus violating
> referential integrity). --DD
Technically SQLite does the latter, but I don't think that opens up any 
new opportunities to violate referential integrity.

But as far as I can tell "the RESTRICT is also postponed to commit-time" 
and "RESTRICT is not honored" are indistinguishable from the users point 
of view. See the notes on RESTRICT here:

   http://sqlite.org/foreignkeys.html#fk_actions

Dan.

[sqlite] Porting Sqlite to Embedded Platform

[Dan Kennedy]

On 03/03/2016 10:20 PM, Nitin Bhosale wrote:
> Hi
>
> We were attempting to use test_onefile.c based approach 
> (http://www.sqlite.org/src/doc/trunk/src/test_onefile.c). This is for 
> embedded where filesystem is not available.
> This code works and we can create, insert, select on the database created 
> using this test_onefile.c.
>
> However we want to save the db to flash and saw that we should be 
> implementing below functions but didn't find any relevant information about 
> it.
>
> **   mediaRead();- Read a 512 byte block from the file.
> **   mediaWrite();   - Write a 512 byte block to the file.
> **   mediaSync();- Tell the media hardware to sync.
>
> I have few questions about it. If you answer them it would be really valuable 
> information for us.
> 1. Signature for these functions with meaning of the argument 2. After 
> writing the above functions to behave as expected, how to integrate it with 
> rest of the code?
> 3. As per our understanding we have to just write above 3 functions and we 
> are done. Is this understanding correct?

That is what it says. But it looks like the code is out of sync with the 
comments. I think you'll need to update fsRead(), fsWrite() and fsSync() 
to work with the media instead of a file-system file. And fsOpen() to 
initialize it.

Dan.

[sqlite] [SqlLite Support] Crash when query data from databse

[Dan Kennedy]

On 03/01/2016 08:01 AM, Nathan Wei wrote:
>
> Hi, All
>   
>
> Currently we are facing a crash when we query data from database where the 
> data contains some emoji char from iOS. The main log is listed below, please 
> refer to the attachment for detailed log.

I think that's the problem fixed here:

http://sqlite.org/android/info/20f8872529890bbf

Upgrading to the latest code from http://sqlite.org/android/ should fix 
things.

Dan.




>
> 5-13 19:43:39.407  12875-13552/com.ringcentral.android A/art? 
> art/runtime/check_jni.cc:65] JNI DETECTED ERROR IN APPLICATION: input is not 
> valid Modified UTF-8: illegal start byte 0xf0
> 05-13 19:43:39.407  12875-13552/com.ringcentral.android A/art? 
> art/runtime/check_jni.cc:65] string: 'Hchd?'
> 05-13 19:43:39.407  12875-13552/com.ringcentral.android A/art? 
> art/runtime/check_jni.cc:65] in call to NewStringUTF
> 05-13 19:43:39.407  12875-13552/com.ringcentral.android A/art? 
> art/runtime/check_jni.cc:65] from long 
> org.sqlite.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(int, 
> int, android.database.CursorWindow, int, int, boolean)
>
> This can be reproduced on Sumsung Galaxy S5/S6 with android version 5.0, 
> Sumsung Note 3 with android version 4.4.1 and etc.
>
>
> Would you provide some thoughts on how to deal with this problem? Your help 
> is highly appreciated.
>
>
>
>
> Warmest Regards
> Nathan Wei
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug in the SQL parser

[Dan Kennedy]

On 02/27/2016 12:49 AM, Jo?o Ramos wrote:
> I'm using SQLite 3.8.10.2 and the following query illustrates the problem:
>
> WITH
> tA(id, name) AS
> (
>  SELECT 1, "a" UNION ALL SELECT 2, "b"
> ),
> tB(name) AS
> (
>  SELECT "a" UNION ALL SELECT "b"
> )
> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>
>
> There is no _id_ column in the tB table, yet the statement doesn't produce
> any error and in fact will return the ids of table tA. This doesn't seem
> correct to me.
>
>

Cannot reproduce this problem here:

dan at darkstar:~/work/sqlite/bld$ ./sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> WITH tA(id, name) AS (
...>   SELECT 1, "a" UNION ALL SELECT 2, "b"
...> ),
...> tB(name) AS (
...>   SELECT "a" UNION ALL SELECT "b"
...> )
...> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
Error: no such column: tB.id


If you were using "oid", "rowid" or "_rowid_" instead of "id", then it 
might look like it was returning the values from table tA. All views and 
CTEs in SQLite have such columns for historical reasons, but the 
contents of them is both undefined and unstable. Sometimes it's a 
sequence of integers starting at 1.

Dan.

[sqlite] Crash when running complex FT5 queries

[Dan Kennedy]

On 02/25/2016 03:04 AM, Gergely Lukacsy (glukacsy) wrote:
> Hi,
>
>
>
> We run into a reproducible crash on FTS5 search when searching something 
> complex such as:
>
>
>
> "(word1* word2* word3*) OR word1* OR word2* OR word3*"
>
>
>
> It might be that we are doing something wrong, but any insight is 
> appreciated. The actual crashing call is a sqlite3_step/1 call:
>
>
>
> while (SQLITE_ROW == stepResult)
>
>{
>
>  stepResult = sqlite3_step(statement); // BOOM
>
>
>
>  if (stepResult == SQLITE_ROW)
>
>  {
>
> ...
>
>  }
>
> }
>
>
>
> Regards,
>
> Gergely
>
>
>
>()Unknown
>
>   SparkWindows.exe!sqlite3_column_name16?()C
>
>   SparkWindows.exe!sqlite3_column_name16?()C
>
>   SparkWindows.exe!sqlite3_column_name16?()C
>
>   SparkWindows.exe!sqlite3_column_name16?()C
>
>   SparkWindows.exe!sqlite3_create_function16?() C
>
>   SparkWindows.exe!sqlite3CodecGetKey?()C
>
>   SparkWindows.exe!sqlite3CodecGetKey?()C
>
>   SparkWindows.exe!sqlite3_step?()   C
>
>   SparkWindows.exe!sqlite3_column_name16?()C
>
>   SparkWindows.exe!sqlite3_column_name16?()C
>
>   SparkWindows.exe!sqlite3_result_subtype?()   C
>
>   SparkWindows.exe!sqlite3CodecGetKey?()C
>
>   SparkWindows.exe!sqlite3CodecGetKey?()C
>
>   SparkWindows.exe!sqlite3_step?()   C


Thanks for reporting this. I don't think the stack trace above is valid 
though. Can you retry with a debugging build?

Also, does the same query on the same database crash if you use the 
shell tool to execute it?

Dan.

[sqlite] Why skip invoking busy handler whilepBt->inTransaction!=TRANS_NONE

[Dan Kennedy]

On 02/25/2016 09:53 AM, sanhua.zh wrote:
> So can this be understood as, if I run my code in WAL, I can invoke busy 
> handler even it in TRAN_READ?


No. The busy-handler is not invoked when trying to upgrade from a read 
to a write transaction. Regardless of journal mode.

Dan.


>
>
> 
> ???:Dan Kennedydanielk1977 at gmail.com
> ???:sqlite-userssqlite-users at mailinglists.sqlite.org
> :2016?2?24?(??)?23:52
> ??:Re: [sqlite] Why skip invoking busy handler 
> whilepBt-inTransaction!=TRANS_NONE
>
>
> On 02/24/2016 08:32 PM, sanhua.zh wrote:  In the source code of SQLite, 
> btree.c, sqlite3BtreeBeginTrans function,  The codedo {  /* Call 
> lockBtree() until either pBt-pPage1 is populated or  ** lockBtree() returns 
> something other than SQLITE_OK. lockBtree()  ** may return SQLITE_OK but 
> leave pBt-pPage1 set to 0 if after  ** reading page 1 it discovers that the 
> page-size of the database  ** file is not pBt-pageSize. In this case 
> lockBtree() will update  ** pBt-pageSize to the page-size of the file on 
> disk.  */  while( pBt-pPage1==0 SQLITE_OK==(rc = lockBtree(pBt)) );if( 
> rc==SQLITE_OK wrflag ){  if( (pBt-btsFlags BTS_READ_ONLY)!=0 ){  rc = 
> SQLITE_READONLY;  }else{  rc = 
> sqlite3PagerBegin(pBt-pPager,wrflag1,sqlite3TempInMemory(p-db));  if( 
> rc==SQLITE_OK ){  rc = newDatabase(pBt);  }  }  }   if( rc!=SQLITE_OK ){  
> unlockBtreeIfUnused(pBt);  }  }while( (rc0xFF)==SQLITE_BUSY 
> pBt-inTransaction==TRANS_NONE  btreeInvokeBusyHandler(pBt) );  You can 
> see pBt-inTransaction==TRANS_NONE is one of the condition that invoke busy 
> handler.  There is a simple way to simulate a situation that does not invoke 
> busy handler:  1. begin a transaction without ?IMMEDIATE? and ?EXCLUSIVE?  2. 
> run a read operation, like ?SELECT?. This will let pBt-inTransaction be 
> TRANS_READ  3. run a write operation, which will invoke 
> sqlite3BtreeBeginTrans again. And if it becomes SQLITE_BUSY, then 
> btreeInvokeBusyHandler will be skiped and no retry will happen.So it?s 
> the question I confused. Why SQLite skip invoking busy handler while it's in 
> TRANS (either read or write) ? Assuming you're not using wal-mode, it's 
> because the two processes will be waiting for each other. The transaction 
> opened in step 1 cannot be committed until the read-only transaction started 
> in step 2 has ended. So if you did invoke the busy-handler in step 3, the two 
> processes would each be waiting for the other to give up. Not much point to 
> that. In wal-mode it's a little different. The transaction opened in step 1 
> could be committed, but attempting to open the write-transaction in step 3 
> following that would fail with SQLITE_BUSY_SNAPSHOT. Dan.  
> ___  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
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why skip invoking busy handler while pBt->inTransaction!=TRANS_NONE

[Dan Kennedy]

On 02/24/2016 08:32 PM, sanhua.zh wrote:
> In the source code of SQLite, btree.c, sqlite3BtreeBeginTrans function,
> The code
>
>
>   do {
>/* Call lockBtree() until either pBt-pPage1 is populated or
>** lockBtree() returns something other than SQLITE_OK. lockBtree()
>** may return SQLITE_OK but leave pBt-pPage1 set to 0 if after
>** reading page 1 it discovers that the page-size of the database
>** file is not pBt-pageSize. In this case lockBtree() will update
>** pBt-pageSize to the page-size of the file on disk.
>*/
>while( pBt-pPage1==0  SQLITE_OK==(rc = lockBtree(pBt)) );
>
>
>if( rc==SQLITE_OK  wrflag ){
> if( (pBt-btsFlags  BTS_READ_ONLY)!=0 ){
>  rc = SQLITE_READONLY;
> }else{
>  rc = sqlite3PagerBegin(pBt-pPager,wrflag1,sqlite3TempInMemory(p-db));
>  if( rc==SQLITE_OK ){
>   rc = newDatabase(pBt);
>  }
> }
>}
>
>if( rc!=SQLITE_OK ){
> unlockBtreeIfUnused(pBt);
>}
>   }while( (rc0xFF)==SQLITE_BUSY  pBt-inTransaction==TRANS_NONE
>   btreeInvokeBusyHandler(pBt) );
>
>
>
>
> You can see pBt-inTransaction==TRANS_NONE is one of the condition that invoke 
> busy handler.
> There is a simple way to simulate a situation that does not invoke busy 
> handler:
> 1. begin a transaction without ?IMMEDIATE? and ?EXCLUSIVE?
> 2. run a read operation, like ?SELECT?. This will let pBt-inTransaction be 
> TRANS_READ
> 3. run a write operation, which will invoke sqlite3BtreeBeginTrans again. And 
> if it becomes SQLITE_BUSY, then btreeInvokeBusyHandler will be skiped and no 
> retry will happen.
>
>
> So it?s the question I confused. Why SQLite skip invoking busy handler while 
> it's in TRANS (either read or write) ?

Assuming you're not using wal-mode, it's because the two processes will 
be waiting for each other.

The transaction opened in step 1 cannot be committed until the read-only 
transaction started in step 2 has ended. So if you did invoke the 
busy-handler in step 3, the two processes would each be waiting for the 
other to give up. Not much point to that.

In wal-mode it's a little different. The transaction opened in step 1 
could be committed, but attempting to open the write-transaction in step 
3 following that would fail with SQLITE_BUSY_SNAPSHOT.


Dan.



> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite-jdbc Bug - database table is locked

[Dan Kennedy]

On 02/24/2016 09:49 AM, Pankaj Bisen wrote:
> Hello Everyone,
>
> I have two unrelated tables table_A and table_B in my SQLite DB. Trying to
> drop table_B while a resultset is open on table_A throws
> "java.sql.SQLException: database table is locked".

That's an SQLITE_LOCKED error. Here:

https://www.sqlite.org/mark/rescode.html?DROP%20TABLE*reader#locked

Dan.



> Following simple code will illustrate the Bug clearly.
> public class Class1 {
>  public static void main(String[] args) throws Exception {
>  Class.forName("org.sqlite.JDBC");
>  Connection c = DriverManager.getConnection("jdbc:sqlite:" +
> "D:\\test.db");
>  Statement stmt1 = c.createStatement();
>  stmt1.executeUpdate("create table table_A (col1 text)");
>  stmt1.executeUpdate("insert into table_A values ('FIRST')");
>  stmt1.executeUpdate("create table table_B (col1 text)");
>  PreparedStatement ps = c.prepareStatement("select * from table_A");
>  ResultSet rs = ps.executeQuery();
>  //While the resultset is open on table_A try to
>  //drop table_B. It gives error that database table is locked
>  //Which makes no sense because there is no relation between
>  //table_A and table_B
>  stmt1.executeUpdate("drop table if exists table_B");
>  rs.close();
>  stmt1.close();
>  c.close();
>  }
> }
> *Some other details*
>
> 1. My Java version is 1.8.0_72.
> 2. I have tried this with sqlite-jdbc-3.8.11.2.jar as well as
> sqlite-jdbc-3.7.2.jar. But the result is same.
> 3. Following are some of the other things that I tried which made no
> difference.
>1. Setting auto-commit on or off
>2. Searched bug-list and googled but all hits are for "database is
>locked" but nothing for "database *table* is locked"
>3. Using try-catch-finally to properly close all resources in case of
>exception.
>4. Closing the connection and reopening it before "Preparedstatement
>ps ...".
>5. Tried to use another connection to drop the table_B. This
>expectedly gave "database is locked" error.
> 4. My actual scenario is that I have meta-data in one table based on
> which I need to drop other tables. So I need to drop other tables while
> processing resultset from meta-data table.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined SQL functions

[Dan Kennedy]

On 02/23/2016 07:36 PM, E.Pasma wrote:
> 22 feb 2016, Dan Kennedy:
>
>> On 02/23/2016 01:33 AM, E.Pasma wrote:
>>>
>>> I reproduced the memory leak and added a test in the sql script.
>>> An alternative fix, instead of adding the missing break, is:
>>>
>>>   case SQLITE_TEXT:
>>>   case SQLITE_BLOB:
>>> pval->n=sqlite3_value_bytes(arg);
>>> if (!pval->n) {
>>>   pval->z="";
>>> } else {
>>>   pval->z=sqlite3_malloc(pval->n);
>>>   assert (pval->z); /* TODO: SQLITE_NOMEM*/
>>> memcpy(pval->z,sqlite3_value_blob(arg),pval->n);
>>> }
>>> break;
>>>
>>> Thus sqlite3_value_blob is used to get both text or blob value (like 
>>> in sqlite3.c at line ~93615 in routine  attachFunc).
>>>
>>> If no response I opt for the alternative fix and place it at
>>> http://h1972688.stratoserver.net/sqlite_mprint/160223
>>
>>
>> Suggest testing with text values and a utf-16 database.
>>
>> Dan.
>
> Thanks, testing with a utf-16 database does not yield any differences. 
> I inserted some 2-byte character and checked that it comes out 
> unchanged from mprint. Hope this will do.

Very good.

I think it's working because the call to sqlite3_value_bytes() is 
transforming the utf-16 text in the sqlite3_value to utf-8. And then 
value_blob() just returns whatever encoding the sqlite3_value has stored 
- turns out that's utf-8 by the time it's called.

Dan.



>
> sqlite> .version
> SQLite 3.11.0 2016-02-12 00:13:38 
> b5d771991686bf86a679b7dff9f16301a5029c8b
> sqlite> pragma encoding='utf-16';
> sqlite> pragma encoding;
> UTF-16be
> sqlite> create table t (a);
> sqlite> insert into t values ('abc');
> sqlite> insert into t values ('d'||char(1000)||'f');
> sqlite> select typeof(a), length(a), length(cast (a as blob)), a from t;
> text|3|6|abc
> text|3|6|d?f
> sqlite> .load ttt_mprint
> sqlite> select * from t where a=mprint(a);
> bytes: 3, memcmp: 0
> abc
> bytes: 4, memcmp: 0
> d?f
>
> ttt_mprint is compiled to compare sqlite3_value_blob and 
> sqlite3_value_text and print the outcome of memcmp. The relevant part is:
>
> case SQLITE_TEXT: /* use sqlite3_value_blob */
> case SQLITE_BLOB: /* use sqlite3_value_blob */
>   pval->n=sqlite3_value_bytes(arg);
>   if (!pval->n) {
> pval->z="";
> z2=""; //testing
>   } else {
> pval->z=sqlite3_malloc(pval->n);
> z2=sqlite3_malloc(pval->n); //testing
> memcpy(pval->z,sqlite3_value_blob(arg),pval->n);
> assert (pval->z); /* TODO: SQLITE_NOMEM*/
> memcpy(z2,sqlite3_value_text(arg),pval->n); //testing
>   }
>   printf("bytes: %d, memcmp: %d \n",
> pval->n,
> memcmp(pval->z, z2, pval->n)); //testing
>   break;
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] User-defined SQL functions

[Dan Kennedy]

On 02/23/2016 01:33 AM, E.Pasma wrote:
>
> I reproduced the memory leak and added a test in the sql script.
> An alternative fix, instead of adding the missing break, is:
>
>case SQLITE_TEXT:
>case SQLITE_BLOB:
>  pval->n=sqlite3_value_bytes(arg);
>  if (!pval->n) {
>pval->z="";
>  } else {
>pval->z=sqlite3_malloc(pval->n);
>assert (pval->z); /* TODO: SQLITE_NOMEM*/
>memcpy(pval->z,sqlite3_value_blob(arg),pval->n);
>  }
>  break;
>
> Thus sqlite3_value_blob is used to get both text or blob value (like 
> in sqlite3.c at line ~93615 in routine  attachFunc).
>
> If no response I opt for the alternative fix and place it at
> http://h1972688.stratoserver.net/sqlite_mprint/160223


Suggest testing with text values and a utf-16 database.

Dan.

[sqlite] FTS4 inquiry (non-printable characters, column delimiters)

[Dan Kennedy]

On 02/22/2016 10:27 AM, Ephraim Stevens wrote:
> Greetings All,
>
> I use the Full Text Search facilities extensively. The feature is intuitive
> and works beautifully and super fast even with 100GB+ databases. I must
> applaud the SQLITE dev team on such amazing work.
>
> I have two questions with regards to FTS.
>
> 1) I'm going out on a limb here:
>
> As some of you may know, there is a undocumented, and not officially
> supported feature/bug where you can specify your own delimiters using the
> "simple" tokenizer:
>
>
> http://article.gmane.org/gmane.comp.db.sqlite.general/74199
>
>
>
> for example to create an table where tokens are split by an exclamation
> point and carat, one would execute:
>
> create virtual table TEST using FTS4(title, body, tokenize=simple "" "!^");
>
> Note the first argument is ignored, the second argument is where the
> delimiters should be specified in quotes.
>
> This is very, very convenient and I've used it extensively without issue (I
> hope the feature never gets removed).
>
> The question here is, how can one specify non printable characters within
> the sqlite3 command shell. Often, delimiters are non-printable characters
> such as NUL (0x00) or SOH (0x01) or even the newline character.  If I
> wanted to specify either one of those in my delimiter string parameter
> above, how could it successfully be done?
>
>

0x00 is likely impossible, but for other ASCII range characters you can 
probably just insert the value into the SQL string, printable or not.


>
>
>
> 2)  The colon ':'  is used in the FTS engine as a column specifier:
>
> select * from FTS_TABLE where COL:foo MATCH "test123".   This means show me
> any rows that have test123 in the COL column only (and not any other
> column).
>
> Is there a way to adjust/change this column delimiter during table setup or
> is this a hard-coded value?  I ask because sometimes, I need to be able to
> perform searches with the colon character as part of a string.  For
> example, when searching on NETWORK MAC addresses. At the same time, I don't
> want to lose the ability to utilize column specifiers.

I think quoted strings are passed to the tokenizer without 
interpretation. So if your tokenizer considers ":" to be a token 
character, you might be able to do:

   ... MATCH '"01:23:45:67:89:AB"' ...

or similar.

Dan.

[sqlite] FTS design document

[Dan Kennedy]

On 02/21/2016 03:54 PM, Charles Leifer wrote:
> Is anyone aware of a design doc for any of the FTS implementations? Looking
> for something a bit more technical than the docs. If not, where in the
> source would you recommend starting? Thanks!

There is no such document unfortunately.

The FTS5 code is cleaner than FTS3/4. I guess to decode it, start by 
understanding SQLite's virtual table interface, then look at fts5Int.h 
to figure out how the code fits together, then the on-disk format 
documented near the top of fts5_index.c.

Dan.



> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

[Dan Kennedy]

On 02/17/2016 08:47 PM, Paul wrote:
> Let's say I have a following database structure:
>
> CREATE TABLE properties
> (
>   name  TEXT NOT NULL,
>   value TEXT,
>   PRIMARY KEY(name)
> ) WITHOUT ROWID;
>
> CREATE TABLE foo
> (
>   idTEXT NOT NULL,
>   PRIMARY KEY(id)
> );
>
> CREATE TRIGGER foo_inserted
>AFTER INSERT ON foo
>   BEGIN
> INSERT OR IGNORE INTO properties(name, value) VALUES('foo_inserts', 0);
> UPDATE properties SET value = value + 1 WHERE name = 'foo_inserts';
>   END;
>
> With a clean database I perform set of queries:
>
> INSERT OR REPLACE INTO foo(id) VALUES(1);
> INSERT OR REPLACE INTO foo(id) VALUES(2);
> INSERT OR REPLACE INTO foo(id) VALUES(3);
>
...
> I've made different test cases and came to a conclusion that 'OR IGNORE' 
> clause inside
> a query within a body of trigger suddenly works as if it was 'OR REPLACE'.

The ON CONFLICT clause of the outer statement overrides the ON CONFLICT 
clause of the statement within the trigger:

https://www.sqlite.org/mark/lang_createtrigger.html?However+if+an*used+instead

(scroll the page down a bit to see the highlighted statement)

Dan.

[sqlite] Correlated subquery throwing an error

[Dan Kennedy]

On 02/15/2016 02:56 PM, Clemens Ladisch wrote:
> Gary Briggs wrote:
>>> SELECT
>>>a.id AS a_id,
>>>(SELECT b.id
>>> FROM foo AS b
>>> WHERE b.id!=a.id
>>>   AND distance(a.x,a.y,b.x,b.y)<=25
>>> ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
>>> FROM foo AS a
>> This is the bit that doesn't seem to work; having
>> distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is
>> what appears to cause the error that it can't find a.x, from the outer query.
> I don't know why correlated subqueries cannot use values from the outer
> query in the ORDER BY or LIMIT clauses; this does not look as if it were
> by design.

ORDER BY and GROUP BY cannot refer to columns from the outer select by 
design:

   http://www.sqlite.org/src/artifact/9f7ce3a3c087a?ln=1252-1253

I think this is an SQL standard thing. It's what Postgres does too. 
Which is probably why SQLite is this way.

LIMIT and OFFSET expressions cannot refer to any columns, correlated or 
otherwise.

Dan.

[sqlite] query Benchmark

[Dan Kennedy]

On 02/12/2016 03:47 PM, Michele Pradella wrote:
> Hi all, is there a way to make a benchmark of queries to check which 
> version is faster? I'm using sqlite shell, the question is about on 
> how to make repetitive tests in the same conditions (for example I 
> need to totally disable cache to avoid different results the second 
> time query is executed).
> I try to explain better: the same query re-arranged in two different 
> queries to check which is the faster, executed on the same DB, how can 
> I do speed check of those queries avoiding interaction between two 
> queries(example cache)?

One way to look at performance of an SQLite query is to say that it is 
influenced by three factors: the amount of IO, the number and size of 
malloc() calls made and the CPU consumed by the library to run the query.

Adding the -stats option to the shell tool command line causes it to 
output some helpful information after running each query. Including page 
cache hits and misses. And some stuff about malloc() usage.

We usually use the valgrind tool callgrind or cachegrind to repeatably 
measure CPU load here.

Dan.

[sqlite] Recover corrupt database

[Dan Kennedy]

On 01/27/2016 08:47 PM, J Decker wrote:
> I was wondering if the backup api (sqlite3_backup_init, et al. ) will
> help to recover a corrupt DB?  All the other mechanisms are an export
> and re-import into a new DB...
>
> The database exists in a encrypted database that it's really difficult
> to get the keys outside of the program and I don't look forward to
> doing something that iterates through sqlite_master to get all the
> tables do all the selects and re-insert... so I browsed the API and
> found the _backup_ stuff.. but; I'm starting to think that it will
> just be a faithful page replication and the result will still be
> corrupted.

Most likely, yes.

As you have surmised, the backup API does a page-by-page copy.

Dan.

[sqlite] Documentation Typo in FTS Example

[Dan Kennedy]

On 12/30/2015 03:57 PM, Casey Rodarmor wrote:
>  From https://sqlite.org/fts3.html:
>
> CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c, d);
> CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);
>
> INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
> INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
>
>
> The two inserts will fail, since t2 has 5 columns but 4 values were
> supplied.

Thanks for this. Now fixed in source control.

Dan.

[sqlite] The Lock-Byte Page

[Dan Kennedy]

On 12/28/2015 05:08 AM, Olivier Mascia wrote:
> Hello,
>
> I'm referring to paragraph 1.3 of https://www.sqlite.org/fileformat2.html 
> about the Lock-Byte page.
>
>  From what I read, I understand those 2^9 bytes at offset 2^30, should they 
> exist, are set aside, untouched by SQLite nor the built-in unix and win32 
> VFS, but third-party VFS implementations might.
>
> What I don't really get straight is what file-locking related mechanism would 
> have a use for those bytes, knowing they wouldn't even exists unless the 
> database size is 2^30 bytes or more?  Or should I understand that for 
> whatever purpose they could be used for, only their offset is useful (even 
> when those bytes do not exists in the file)?

It's because (at least historically - may have changed?) win32 does not 
support advisory locks. So if you take an EXCLUSIVE lock on a range of 
bytes no other process can read them. This is different from Unix, where 
all locks are advisory - one process locking a range of bytes does not 
prevent another from reading them, only from taking a conflicting lock.

For this reason we need a range of bytes that are never read by SQLite 
to take locks on (otherwise we couldn't have readers running 
concurrently with the writer). The reason the selected range is out at 
2^30 instead of, say, at the start of the file, is to avoid forcing 
really small databases to be larger than they would otherwise have to be.

It doesn't matter that database files are usually less than 2^30 bytes 
in size. All the (main?) platforms support locking regions of a file 
that have not yet been populated.

Dan.

[sqlite] Malloc getting segmentation fault in sqlite

[Dan Kennedy]

On 12/24/2015 05:02 PM, santosh dasimanth wrote:
> Hi All,
> I am working on Sqlite in multi threaded environment on ARM v7 platform.
>
> I am facing problems with malloc() function returning segmentation fault.
> The problem is not frequent but out of 100 times am getting this once.
>
> The backtrace is pasted below.
>
> (gdb) bt
> #0  0x4038eb18 in malloc () from /lib/libc.so.0
> #1  0x401e0758 in sqlite3MallocRaw () from /pfrm2.0/lib/libsqlite3.so.0
> #2  0x401e08a8 in sqlite3Malloc () from /pfrm2.0/lib/libsqlite3.so.0
> #3  0x401e6254 in sqlite3VdbeCreate () from /pfrm2.0/lib/libsqlite3.so.0
> #4  0x401d99cc in sqlite3GetVdbe () from /pfrm2.0/lib/libsqlite3.so.0
> #5  0x401bd780 in sqlite3FinishCoding () from /pfrm2.0/lib/libsqlite3.so.0
> #6  0x401d2464 in sqlite3Parser () from /pfrm2.0/lib/libsqlite3.so.0
> #7  0x401dd664 in sqlite3RunParser () from /pfrm2.0/lib/libsqlite3.so.0
> #8  0x401d650c in sqlite3Prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #9  0x401d69a4 in sqlite3_prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #10 0x401ed5c0 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #11 0x401d5dbc in sqlite3InitCallback () from /pfrm2.0/lib/libsqlite3.so.0
> #12 0x401ed6f8 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #13 0x401d6184 in ?? () from /pfrm2.0/lib/libsqlite3.so.0
>
> The traces are pointing to different functions when I hit the issue at
> times.
> Please let me know if anyone of people faced this problem before with
> sqlite.

You have a corrupted heap in your application. Usually this is caused by 
calling free() or similar on a pointer that you should not have, but can 
also be due to large buffer overwrites and so on.

If possible, run your application under valgrind - either on the ARM 
platform or on a workstation. It will very likely tell you what is going 
wrong.

   http://valgrind.org/

Dan.

[sqlite] Index on computed value?

[Dan Kennedy]

On 12/16/2015 03:17 PM, Deon Brewis wrote:
> Is it possible to have an index on a computer value?
>
>
> E.g. I have a 40 byte value in one of my columns. I only want an index over 
> the first 4 bytes of it.
>
>
> However, I don't really want to repeat those 4 bytes inside another column on 
> the main table.
>
>
> Is there any way to accomplish that?

Something like

   CREATE TABLE t1(x BLOB);
   CREATE INDEX i1 ON t1( substr(x, 1, 4) );

   https://www.sqlite.org/expridx.html

Dan.

[sqlite] about attach database

[Dan Kennedy]

On 12/16/2015 12:51 PM, ??? wrote:
>  After testing the Sqlite3_open and ATTACH DATABASE,
>  I found that the attach database is slower than sqlite3_open.
>  there is attachment after the mail which includ the speed 
> information ( millisecond ).

Hi,

This mailing list strips attachments, so you'll need to include the 
information inline.

One possible explanation: When you run an ATTACH statement, SQLite opens 
the new database file and reads the schema from the sqlite_master table. 
Whereas sqlite3_open() just opens the db file (reading the schema is 
deferred until it is first required in this case).

So an apples/apples comparison might be to open/ATTACH the database and 
then run a simple query that forces SQLite to read the database schema 
if it has not already - say "SELECT * FROM sqlite_master".

Dan.

[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

[Dan Kennedy]

On 12/13/2015 08:05 PM, ajm at zator.com wrote:
>>  Mensaje original 
>> De: Dan Kennedy 
>> Para:  sqlite-users at mailinglists.sqlite.org
>> Fecha:  Fri, 11 Dec 2015 22:54:45 +0700
>> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 
>> toFTS5modules(revisited)
>>
>>>>> 1a.- Delete the previous table.
>>>>> DROP TABLE IF EXIST ftsm
>>>>>
>>>>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 
>>>>> gives an erroro in 1a:  "database disk image is malformed".
>>>>>
>>>>> Note that in previous attemps I believed that the problem was into try to 
>>>>> delete a ftsm table build with the previous modules, but the error happen 
>>>>> when trying delete a table build with the FTS5 module.
>>>>>
>>>>> ...
>>>>>>> Some clues?
>>>>>> Not really sure why it might fail there. Can you post the entire
>>>>>> database schema (results of "SELECT * FROM sqlite_master" or the output
>>>>>> of the .schema shell tool command)?
>>>>>>
>>>>> Dan:
>>>>>
>>>>> Here is the schema:
>>>> That all looks Ok.
>>>>
>>>> If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm"
>>>> command does it report an error as well?
>>>>
>>> Dan:
>>>
>>> Surprisingly, the shell does not complain when using the same query, and 
>>> indeed, drop the table.
>>>
>>> Yeah, I also believe that the problem is in my roof, although that code has 
>>> been behaving Ok from ages with the FTS3/4 modules (only changes some 
>>> directives in other places of the code).
>>>
>>> Any way, when running again the code when the table has been previously 
>>> erased (with the shell), the routine works without complaint (executes the 
>>> query correctly), and despite the warning, the rest of the app behaves 
>>> correctly and the queries related with the search works fine.
>>>
>>> When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, 
>>> but sqlite3_step() ends with error 11.
>> Are you able to compile a debugging build of SQLite and set a breakpoint
>> in sqlite3CorruptError()?
>>
>> Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS
>> statement (pretty good chance), the stack trace will tell us more about
>> the form of corruption SQLite thinks it has found.
>>
> Dan:
>
> I managed a breakpoint int the requested function, and can tell that it has 
> been called twice inside the sqlite3_step() function before it returns.
>
> Here you have the call's stack:
>
> First call of sqlite3CorrupError()  lineno == 56209
>
> sqlite3CorruptError(int lineno) Line 133961
> decodeFlags(MemPage * pPage, int flagByte) Line 56209
> btreeInitPage(MemPage * pPage) Line 56251
> getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, 
> BtCursor * pCur, int bReadOnly) Line 56495
> clearDatabasePage(BtShared * pBt, unsigned int pgno, int freePageFlag, int * 
> pnChange) Line 62907
> sqlite3BtreeClearTable(Btree * p, int iTable, int * pnChange) Line 62970
> btreeDropTable(Btree * p, unsigned int iTable, int * piMoved) Line 63028
> sqlite3BtreeDropTable(Btree * p, int iTable, int * piMoved) Line 63111
> sqlite3VdbeExec(Vdbe * p) Line 77954
> sqlite3Step(Vdbe * p) Line 71546
> sqlite3_step(sqlite3_stmt * pStmt) Line 71608
> sqlite3_exec(sqlite3 * db, const char * zSql, int (void *, int, char * *, 
> char * *) * xCallback, void * pArg, char * * pzErrMsg) Line 103955
> fts5ExecPrintf(sqlite3 * db, char * * pzErr, const char * zFormat, ...) Line 
> 180863
> sqlite3Fts5DropAll(Fts5Config * pConfig) Line 180876
> fts5DestroyMethod(sqlite3_vtab * pVtab) Line 178532
> sqlite3VtabCallDestroy(sqlite3 * db, int iDb, const char * zTab) Line 117587
> sqlite3VdbeExec(Vdbe * p) Line 79084
> sqlite3Step(Vdbe * p) Line 71546
> sqlite3_step(sqlite3_stmt * pStmt) Line 71608
>
> Secon call of sqlite3CorrupError() lineno == 56251
>
> sqlite3CorruptError(int lineno) Line 133961
> btreeInitPage(MemPage * pPage) Line 56251
> getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, 
> BtCursor * pCur, int bReadOnly) Line 56495
> ...
> Rest the same...
>
> Note that the line numbers can be slightly greater than the ones in the 
> original file (SQLite 3.9.1) because the inclusion of some comments.
>
> As I can reproduce the problem as needed, In case of interest, I can try to 
> provide any intermediate value inside the stack.

So that looks like database corruption, except we don't think the 
database is actually corrupt as the DROP TABLE statement did not fail 
when run in the shell tool. So perhaps heap-corruption has caused the 
in-memory cache to become corrupt.

Can you run the whole thing under valgrind?

Dan.

[sqlite] Problem when upgrading from FTS3/4 to FTS5modules(revisited)

[Dan Kennedy]

On 12/11/2015 08:22 PM, ajm at zator.com wrote:
>>  Mensaje original 
>> De: Dan Kennedy 
>> Para:  sqlite-users at mailinglists.sqlite.org
>> Fecha:  Fri, 11 Dec 2015 15:28:33 +0700
>> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 to 
>> FTS5modules(revisited)
>>
>>> 1a.- Delete the previous table.
>>> DROP TABLE IF EXIST ftsm
>>>
>>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives 
>>> an erroro in 1a:  "database disk image is malformed".
>>>
>>> Note that in previous attemps I believed that the problem was into try to 
>>> delete a ftsm table build with the previous modules, but the error happen 
>>> when trying delete a table build with the FTS5 module.
>>>
>>> ...
>>>>> Some clues?
>>>> Not really sure why it might fail there. Can you post the entire
>>>> database schema (results of "SELECT * FROM sqlite_master" or the output
>>>> of the .schema shell tool command)?
>>>>
>>> Dan:
>>>
>>> Here is the schema:
>> That all looks Ok.
>>
>> If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm"
>> command does it report an error as well?
>>
> Dan:
>
> Surprisingly, the shell does not complain when using the same query, and 
> indeed, drop the table.
>
> Yeah, I also believe that the problem is in my roof, although that code has 
> been behaving Ok from ages with the FTS3/4 modules (only changes some 
> directives in other places of the code).
>
> Any way, when running again the code when the table has been previously 
> erased (with the shell), the routine works without complaint (executes the 
> query correctly), and despite the warning, the rest of the app behaves 
> correctly and the queries related with the search works fine.
>
> When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, 
> but sqlite3_step() ends with error 11.

Are you able to compile a debugging build of SQLite and set a breakpoint 
in sqlite3CorruptError()?

Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS 
statement (pretty good chance), the stack trace will tell us more about 
the form of corruption SQLite thinks it has found.

Thanks,
Dan.




>
> When the table is already deleted, sqlite3_prepare_v2() ends Ok, and 
> sqlite3_step() ends with 101.
>
> Frankly, because the routine is the same in both times, and there are not 
> variables to bind, really I don't know where to look. Anyway thanks for 
> your's attention.
>
> Cheers!
>
> --
> Adolfo J. Millan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules(revisited)

[Dan Kennedy]

> 1a.- Delete the previous table.
> DROP TABLE IF EXIST ftsm
>
> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an 
> erroro in 1a:  "database disk image is malformed".
>
> Note that in previous attemps I believed that the problem was into try to 
> delete a ftsm table build with the previous modules, but the error happen 
> when trying delete a table build with the FTS5 module.
>
> ...
>>> Some clues?
>> Not really sure why it might fail there. Can you post the entire
>> database schema (results of "SELECT * FROM sqlite_master" or the output
>> of the .schema shell tool command)?
>>
> Dan:
>
> Here is the schema:

That all looks Ok.

If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm" 
command does it report an error as well?

Thanks,
Dan.







>
> CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 
> remove_diacri
> tics 0',columnsize=0);
>
> And here the entire database schema as produced by the shell:
>
> sqlite> SELECT * FROM sqlite_master;
> table|usr|usr|2|CREATE TABLE usr (ky INTEGER PRIMARY KEY,id CHARACTER 
> UNIQUE,lev
>   INTEGER,pwd TEXT)
> index|sqlite_autoindex_usr_1|usr|3|
> table|block|block|4|CREATE TABLE block (Stat INTEGER,User INTEGER,Page 
> TEXT,Text
>   INTEGER)
> table|FreqUse|FreqUse|5|CREATE TABLE FreqUse (Stat INTEGER,User INTEGER,Page 
> TEX
> T,Text INTEGER)
> table|blb|blb|6|CREATE TABLE blb (Id INTEGER PRIMARY KEY,Nm INTEGER)
> table|atm|atm|7|CREATE TABLE atm (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl 
> INTEGER,D
> c REAL,Dm REAL,St INTEGER)
> table|coco|coco|8|CREATE TABLE coco (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl 
> INTEGE
> R,Dc REAL,Dm REAL,St INTEGER)
> table|lnk|lnk|9|CREATE TABLE lnk (So INTEGER NOT NULL,Ta INTEGER NOT NULL,Cl 
> INT
> EGER,Tpt INTEGER,UNIQUE 
> (So,Ta,Cl),CHECK(typeof(So)='integer'),CHECK(typeof(Ta)=
> 'integer'),CHECK((typeof(Cl)='integer') OR (typeof(Cl)='null')))
> index|sqlite_autoindex_lnk_1|lnk|10|
> table|prm|prm|11|CREATE TABLE prm(ref INTEGER, val INTEGER, own INTEGER, 
> UNIQUE(
> ref, own))
> index|sqlite_autoindex_prm_1|prm|12|
> table|email|email|13|CREATE TABLE email (Id INTEGER PRIMARY KEY, Tit INTEGER, 
> No
> m INTEGER, Org INTEGER,eHnm INTEGER, ePort INTEGER, eUnm INTEGER, ePsw 
> INTEGER,
> eScon INTEGER, eDel INTEGER,sHnm INTEGER, sPort INTEGER, sUnm INTEGER, sPsw 
> INTE
> GER, sScon INTEGER,Enam INTEGER, Rnam INTEGER, Unam INTEGER, Onam INTEGER, 
> iucs
> INTEGER, sec1 INTEGER, sec2 INTEGER, sec3 INTEGER, sec4 INTEGER,Cl INTEGER, 
> St I
> NTEGER, aux1 INTEGER, aux2 INTEGER, aux3 INTEGER, aux4 INTEGER, aux5 INTEGER, 
> au
> x6 INTEGER, aux7 INTEGER)
> view|AgVtHolder|AgVtHolder|0|CREATE VIEW AgVtHolder AS SELECT id FROM atm 
> WHERE
> id IN(SELECT so FROM lnk L WHERE L.ta=73 AND L.cl=47)
> view|AgVtIDt|AgVtIDt|0|CREATE VIEW AgVtIDt AS SELECT L.ta AS 'Hd', C.nm AS 
> 'Dt'
> FROM atm C, lnk L WHERE C.cl=17 AND C.id IN (SELECT L.so FROM lnk L WHERE 
> L.cl=4
> 8 AND L.ta IN(SELECT id FROM AgVtHolder)) AND L.so=C.id
> view|AgVtPre|AgVtPre|0|CREATE VIEW AgVtPre AS SELECT L.ta AS 'Hd', CAST(Nm AS 
> IN
> T) AS 'Pr' FROM atm C, lnk L WHERE C.cl=17 AND C.id IN(SELECT so FROM lnk L 
> WHER
> E L.cl=49 AND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk 
> L W
> HERE L.ta=73 AND L.cl=47))) AND L.So=C.id
> view|AgVtos|AgVtos|0|CREATE VIEW AgVtos AS SELECT D.Hd AS 'Hd', D.Dt AS 'Dt', 
> P.
> Pr AS 'Pr' FROM AgVtIDt D, AgVtPre P WHERE P.Hd=D.Hd
> view|AgPdHolder|AgPdHolder|0|CREATE VIEW AgPdHolder AS SELECT id FROM atm 
> WHERE
> id IN(SELECT So FROM lnk L WHERE L.ta=75 AND L.cl=53)
> view|AgPdIDt|AgPdIDt|0|CREATE VIEW AgPdIDt AS SELECT L.ta AS 'Hd', C.Nm AS 
> 'Dt'
> FROM atm C, lnk L WHERE C.Cl=18 AND C.id IN (SELECT L.so FROM lnk L WHERE 
> L.cl=5
> 4 AND L.ta IN(SELECT id FROM AgPdHolder)) AND L.so=C.id
> view|AgEfHolder|AgEfHolder|0|CREATE VIEW AgEfHolder AS SELECT id FROM atm 
> WHERE
> id IN(SELECT So FROM lnk L WHERE L.ta=77 AND L.cl=59)
> view|AgEfIDt|AgEfIDt|0|CREATE VIEW AgEfIDt AS SELECT L.ta AS 'Hd', C.Nm AS 
> 'Dt'
> FROM atm C, lnk L WHERE C.Cl=19 AND C.id IN (SELECT L.So FROM lnk L WHERE 
> L.cl=6
> 0 AND L.ta IN(SELECT id FROM AgEfHolder)) AND L.So=C.id
> view|AgEfKlv|AgEfKlv|0|CREATE VIEW AgEfKlv AS SELECT L.ta AS 'Hd', C.Nm AS 
> 'Kl'
> FROM atm C, lnk L WHERE C.cl=19 AND C.id IN(SELECT so FROM lnk L WHERE 
> L.cl=61 A
> ND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L WHERE 
> L.ta=7
> 7 AND L.cl=59))) AND L.so=C.id
> view|AgEfemer|AgEfemer|0|CREATE VIEW AgEfemer AS SELECT D.Hd AS 'Hd', D.Dt AS 
> 'D
> t', P.Kl AS 'Kl' FROM AgEfIDt D, AgEfKlv P WHERE P.Hd=D.Hd
> table|ftsm|ftsm|0|CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter 
> unico
> de61 remove_diacritics 0',columnsize=0)
> table|ftsm_data|ftsm_data|11332|CREATE TABLE 'ftsm_data'(id INTEGER PRIMARY 
> KEY,
>   block BLOB)
> table|ftsm_idx|ftsm_idx|11333|CREATE TABLE 'ftsm_idx'(segid, term, pgno, 
> PRIMARY
>   KEY(segid, term)) WITHOUT ROWID
> 

[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules (revisited)

[Dan Kennedy]

On 12/10/2015 05:15 AM, ajm at zator.com wrote:
> Hi list:
>
> In a C++ Windows app that uses SQLite v. 3.9.1 and behaves well, I try change 
> the search engine from FTS3/4 modules to FTS5, by means off:
>
> 1. Define the directive
> #define SQLITE_ENABLE_FTS5 1
>
> 2.-  Create the table:
> CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 
> remove_diacritics 0',columnsize=0)
>
> 3.- Populate the table:
> INSERT INTO ftsm (row,nm) SELECT id,nm FROM atm WHERE(..)
>
> After that, the app and the search engine works as espected.
>
> To update the ftsm table after several inserts and deletes, I try to follow 
> this steps
>
> 1a.- Delete the previous table.
> DROP TABLE IF EXIST ftsm
>
> 2a.- Create table (as above)
>
> 3a.- Populate table (as above).
>
> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an 
> erroro in 1a:  "database disk image is malformed".
>
> Note that in previous attemps I believed that the problem was into try to 
> delete a ftsm table build with the previous modules, but the error happen 
> when trying delete a table build with the FTS5 module.
>
> I managed to drop the ftsm table by means of create a new dbase; create the 
> same tables (except ftsm); populate the tables, and replacing the previous 
> dbase with the new one. But obviously, this method is primitive; time 
> consuming, and has problems when the dbase is in use.
>
> Some clues?

Not really sure why it might fail there. Can you post the entire 
database schema (results of "SELECT * FROM sqlite_master" or the output 
of the .schema shell tool command)?

Thanks,
Dan.

[sqlite] sqlite3_errmsg and wide char

[Dan Kennedy]

On 11/27/2015 12:13 PM, Igor Korot wrote:
> Hi,
> Is there any way to have "sqlite3_errmsg" function return a wide char string?
> Or do a conversion in a portable way?

Will sqlite3_errmsg16() work for you?

   https://www.sqlite.org/c3ref/errcode.html

Dan.

[sqlite] FTS5 prefix index documentation may be incorrect

[Dan Kennedy]

On 11/25/2015 05:11 AM, Charles Leifer wrote:
> The FTS5 prefix index documentation[1] seems to not be working. I've tried
> with SQLite 3.9.0 and 3.10.0 (2015-11-06) and both show the same error
> messages.

Thanks for reporting this. Now fixed here:

   http://sqlite.org/src/info/11eb8e877e2ba859

Dan.


>
> Examples:
>
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3');
> Error: malformed prefix=... directive
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);
> Error: multiple prefix=... directives
>
> What does appear to work is using a comma:
>
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
> sqlite> .schema ft
> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
>
> 1: https://sqlite.org/fts5.html#section_4_2
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE vs. OSX mmap == inevitable catalog corruption?

[Dan Kennedy]

On 11/18/2015 03:37 PM, T?r?k Edwin wrote:
> On 11/17/2015 12:11 AM, Deon Brewis wrote:
>> We found that using SQLITE memory mapped I/O (mmap_size > 0) in OSX isn't 
>> usable in any way shape or form. It will inevitably lead to catalog 
>> corruption if you hard-reboot OSX, even without the database or application 
>> open.
>>
> I tried to turn on mmap I/O in SQLite 3.9.1 in our application on CentOS 7 
> and Debian Jessie, but after 24-48h of continous writes to the DB I always 
> get a corruption:
> SQLite result 0xb: database corruption at line 76915 of [767c1727fe]
> SQLite result 0xb: statement aborts at 10: [DELETE FROM revision_blocks WHERE 
> revision_id=:revision_id]
> Query "DELETE FROM revision_blocks WHERE revision_id=:revision_id" failed: 
> (code 0xb: database disk image is malformed) database disk image is malformed
>
> After this 'pragma integrity check' says:
> row 90814 missing from index sqlite_autoindex_revision_ops_1
> wrong # of entries in index sqlite_autoindex_revision_ops_1
> row 1046646 missing from index idx_revmap
> row 1046646 missing from index sqlite_autoindex_revision_blocks_1
> wrong # of entries in index idx_revmap
> wrong # of entries in index sqlite_autoindex_revision_blocks_1
>
> There are not reboots involved, just multiple processes accessing a WAL DB. 
> Without mmap I/O I've never seen corrupted DBs in our application.

As of yesterday, SQLite uses a read-only mapping in mmap mode. The db 
file is written using plain old write(), just as in non-mmap mode:

   http://sqlite.org/src/info/67c5d3c646c8198c

It would be interesting to know if this clears the problem in your 
environment.

Dan.

[sqlite] Delta Compression in RBU

[Dan Kennedy]

On 11/13/2015 08:06 AM, Philip Bennefall wrote:
> Something I forgot in my prior post; I found the delta creation code 
> in sqldiff.c so my question really concerns the combined delta code 
> found in RBU and sqldiff.c (both creating and applying deltas).


The versions of the delta creation and application code checked in to 
the sqlite source project are public domain. The original authors of the 
code re-licensed it.

Dan.

[sqlite] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Dan Kennedy]

On 11/11/2015 01:19 AM, Florian Weimer wrote:
> * Dan Kennedy:
>
>> On 10/18/2015 10:27 PM, Florian Weimer wrote:
>>> My first surprise was SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE (see
>>> ?SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE?).  I have found another
>>> source of such snapshot failures with WAL-mode databases.
>>>
>>> I like to pre-compile my DML statements before starting transactions,
>>> mainly for the implied syntax check.  (But perhaps there are also
>>> performance benefits, too?I haven't checked.)
>>>
>>> Here is what I did (if my tracing is correct).  Unless mentioned
>>> otherwise, the operations succeed with SQLITE_OK.
>>>
>>> Open the database.
>>> Compile the UPDATE statement.
>>> Compile the ROLLBACK statement (used below).
>>> Start a BEGIN DEFERRED transaction (with a fresh statement).
>>> Reset the UPDATE statement.
>>> Step the UPDATE statement.
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>> Reset/step/reset the pre-compiled ROLLBACK statement.
>>> Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>> Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>>> Reset the UPDATE statement.
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>>
>>> After the last failure, iterating through the list of stattements does
>>> not show that the UPDATE statement is busy.
>>
>> The SQLITE_BUSY_SNAPSHOT returned by the final call to sqlite3_reset()
>> on the UPDATE is not a new error - it has just saved the error code
>> from the error that occurred during the sqlite3_step() call in the
>> previous transaction.
> D'oh!  I mistakenly assumed using the sqlite3_prepare_v2() function
> would avoid that.  Thanks.
>
>> So technically the error did not occur within
>> the BEGIN IMMEDIATE transaction.
> Right.
>
> I have changed my wrapper for sqlite3_reset() to ignore the error
> value and removed the compensation code from my transaction monitor.
> The tests that previously failed occasionally still succeed.
>
>>> I don't know how risky pre-compiling UPDATE statement is.  For SELECT
>>> statements, it is problematic because they can lead to persistent
>>> transaction failures with SQLITE_BUSY_SNAPSHOT because the
>>> pre-compiled and partitial executed statement is busy and will block
>>> creating a new transaction.  I wonder if this can happen with UPDATE
>>> statements as well.
>> I don't quite understand this. Preparing an SQL statement may read the
>> database (in order to read the schema). But calling sqlite3_prepare()
>> doesn't leave the statement in a "partially executed" state.
> My concern is about sqlite3_step(UPDATE) without a following
> sqlite3_reset(UPDATE).  Perhaps I should change my wrapper to
> unconditionally call sqlite3_reset() after DML-related sqlite3_step(),
> whether the stepping operation succeeded or not.

For a DML statement, I usually just do:

   sqlite3_step(pStmt);
   rc = sqlite3_reset(pStmt);
   if( rc!=SQLITE_OK ){
 /* Handle error */
   }

I don't see why you would want to call step() but not reset() for a DML 
operation. And since reset() always returns either the same or a more 
specific error code, there isn't too much reason to ever catch the 
return value of step(). IMO.

Dan.

[sqlite] Database corruption using zipvfs on android 64 bits (x86_64)

[Dan Kennedy]

On 11/06/2015 11:57 PM, Alexandre Mainville wrote:
> Hi,
>
> I am experiencing database corruption using zipvfs on android 64
> bits (x86_64).
> The same program executing on 32 bits runs without problems.
>
> The program creates a new database on every run and does a series of
> inserts and updates (always the same series of operations).
>
> Both programs produce the same SQLITE_DEBUG trace up until the point the 64
> bit version outputs:
> zipvfs database is corrupt. Line 1100 of [9d6c1880fb]
> because of a zero size payload in a slot header.
>
> I am waiting on a device to test behavior with arm64-v8a.
>
> I am using sqlite 3.8.8.3 and zipvfs 3.8.8.
>
> If anyone has experienced the same or has some insight on this, it would be
> greatly appreciated.


Are you able to run this on a workstation? If so, try running the 
program under valgrind ( http://valgrind.org ).

One way to debug this might be to run the zipvfs integrity-check (i.e. 
ZIPVFS_CTRL_INTEGRITY_CHECK) after each transaction. If you can figure 
out which transaction is introducing the corruption and take a copy of 
the database before and after it is executed, we might be able to figure 
out what is going on based on that and the log below.

Another would be to capture the statements executed using an 
sqlite3_trace() callback, then try to use them to reproduce the 
corruption using the shell tool. Even if that doesn't produce the 
corruption, it will be worth running such a script under valgrind to 
search for uninitialized variable problems etc.

I can't see any bugfixes in the changelog since 3.8.8 to do with corrupt 
databases, but it might be worth upgrading to the latest zipvfs and 
sqlite to check if the problem is still present anyway.

Regards,
Dan.

[sqlite] FTS5 rank configuration option

[Dan Kennedy]

On 11/06/2015 02:26 PM, chromedout64 at yahoo.com wrote:
> The documentation says that a persistent rank can be set with
> INSERT INTO ft(ft, rank) VALUES('rank', 'bm25(10.0, 5.0)');
> However, I can only get it to work on the same database connection as the 
> query. Subsequent database connections seem to use the standard default 
> ranking. Is this by design?

Thanks for reporting this. Might be fixed here:

   http://sqlite.org/src/info/33e6606f5e497e81

Dan.




> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

[Dan Kennedy]

On 11/06/2015 12:36 PM, chromedout64 at yahoo.com wrote:
> I noticed that this update is not included in the official 3.9.2 release. How 
> is it determined when an update such as this one gets included in a release? 
> Thanks for any info.

Most likely as part of 3.10.0.

You can always download the SQLite trunk, either via fossil or via a 
link like:

   http://www.sqlite.org/src/tarball/SQLite-trunk.tgz?uuid=trunk

Then run [./configure && make amalgamation-tarball] to build an autoconf 
package.

Regards,
Dan.






>   
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 explicitly set delimiter

[Dan Kennedy]

On 11/02/2015 01:55 AM, chromedout64 at yahoo.com wrote:
> Thanks, I figured that this might be the case. What is the best way to 
> specify all characters except whitespace as part of a CREATE VIRTUAL TABLE 
> statement? Should you simply list each of the literal ascii characters such 
> as tokenchars '!@#$%' and so on.

I think you'll have to do the above, yes.

Dan.

[sqlite] FTS5 explicitly set delimiter

[Dan Kennedy]

On 11/01/2015 06:39 PM, chromedout64 at yahoo.com wrote:
>   Is there an easy way to set an FTS5 table so that the only 
> delimiter/separator is a space and everything else, including all 
> punctuation, is a token? Some searching revealed that there is an 
> undocumented feature as a part of FTS3/4 that allows the actual delimiter to 
> be specified as part of table creation, but I'm not sure if this exists for 
> FTS5.

You could use the tokenchars option of the unicode61 or ascii tokenizers 
to specify that all characters except whitespace are token characters:

   https://www.sqlite.org/fts5.html#section_4_3_1

Dan.

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

[Dan Kennedy]

On 10/27/2015 05:06 PM, chromedout64 at yahoo.com wrote:
> Using the example from the documentation at https://www.sqlite.org/fts5.html
> I thought this worked in a previous version.
>
> CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
> CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', 
> content_rowid='a');
> CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
>INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
> END;
> CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
>INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, 
> old.c);
> END;
> CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
>INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, 
> old.c);
>INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
> END;
>
> REPLACE INTO tbl VALUES(1,'foo','bar');

I think this may have worked with an older version of FTS5 that did not 
support REPLACE.

The trouble is that because the top-level statement uses OR REPLACE 
conflict handling, so do the INSERT statements fired by the triggers. 
And OR REPLACE conflict handling was broken for external content tables.

Fts5 is now updated on trunk so that OR REPLACE is ignored for 
contentless and external content tables:

   http://sqlite.org/src/info/a85c2a4758c27e8d

So this sort of thing should work again.

Dan.

[sqlite] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Dan Kennedy]

On 10/18/2015 10:27 PM, Florian Weimer wrote:
> My first surprise was SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE (see
> ?SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE?).  I have found another
> source of such snapshot failures with WAL-mode databases.
>
> I like to pre-compile my DML statements before starting transactions,
> mainly for the implied syntax check.  (But perhaps there are also
> performance benefits, too?I haven't checked.)
>
> Here is what I did (if my tracing is correct).  Unless mentioned
> otherwise, the operations succeed with SQLITE_OK.
>
>Open the database.
>Compile the UPDATE statement.
>Compile the ROLLBACK statement (used below).
>Start a BEGIN DEFERRED transaction (with a fresh statement).
>Reset the UPDATE statement.
>Step the UPDATE statement.
> -> this fails with SQLITE_BUSY_SNAPSHOT
>Reset/step/reset the pre-compiled ROLLBACK statement.
>Start a BEGIN IMMEDIATE transaction (with a fresh statement).
> -> this fails with SQLITE_BUSY_SNAPSHOT
>Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>Reset the UPDATE statement.
> -> this fails with SQLITE_BUSY_SNAPSHOT
>
> After the last failure, iterating through the list of stattements does
> not show that the UPDATE statement is busy.


The SQLITE_BUSY_SNAPSHOT returned by the final call to sqlite3_reset() 
on the UPDATE is not a new error - it has just saved the error code from 
the error that occurred during the sqlite3_step() call in the previous 
transaction. So technically the error did not occur within the BEGIN 
IMMEDIATE transaction.


>
> This seems to contradict this piece of the documentation:
>
> | If X starts a transaction that will initially only read but X knows
> | it will eventually want to write and does not want to be troubled
> | with possible SQLITE_BUSY_SNAPSHOT errors that arise because another
> | connection jumped ahead of it in line, then X can issue BEGIN
> | IMMEDIATE to start its transaction instead of just an ordinary
> | BEGIN. The BEGIN IMMEDIATE command goes ahead and starts a write
> | transaction, and thus blocks all other writers. If the BEGIN
> | IMMEDIATE operation succeeds, then no subsequent operations in that
> | transaction will ever fail with an SQLITE_BUSY error.
>
> 
>
> This happens with version 3.8.11.1 on Fedora 22.
>
> I don't know how risky pre-compiling UPDATE statement is.  For SELECT
> statements, it is problematic because they can lead to persistent
> transaction failures with SQLITE_BUSY_SNAPSHOT because the
> pre-compiled and partitial executed statement is busy and will block
> creating a new transaction.  I wonder if this can happen with UPDATE
> statements as well.

I don't quite understand this. Preparing an SQL statement may read the 
database (in order to read the schema). But calling sqlite3_prepare() 
doesn't leave the statement in a "partially executed" state.

So if you do:

   sqlite3_prepare(UPDATE);
   BEGIN;
 sqlite3_prepare(SELECT);
 sqlite3_step(UPDATE);

then I guess you might get an SQLITE_BUSY_SNAPSHOT error (if the 
prepare() call actually needed to read the database), but if you do:

   sqlite3_prepare(SELECT);
   sqlite3_prepare(UPDATE);
   BEGIN;
 sqlite3_step();

then I don't think you can get an SQLITE_BUSY_SNAPSHOT error.

Dan.

[sqlite] Compilation fails under Linux (Ubuntu) when FTS5 is enabled

[Dan Kennedy]

On 10/18/2015 02:21 AM, tonyp at acm.org wrote:
> I'm trying to compile the shell using the amalgamation (three files 
> involved: shell.c sqlite3.c sqlite.h).
> This is a process I've done a zillion times before without problems.
> The only new thing I did was add the -DSQLITE_ENABLE_FTS5 to command 
> line.
>
> (Adding the -lm switch did not help.)

That's quite surprising. Please post the full command line and the 
resulting output.

Thanks,
Dan.



>
> -Original Message- From: Dan Kennedy
> Sent: Saturday, October 17, 2015 4:35 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Compilation fails under Linux (Ubuntu) when FTS5 
> is enabled
>
> On 10/17/2015 06:47 PM, tonyp at acm.org wrote:
>> What the subject says and I get this error:
>>
>> /tmp/cco8QBGJ.o: In function `fts5Bm25GetData':
>> sqlite3.c:(.text+0xb521b): undefined reference to `log'
>>
>> (using the latest 3.9.1 release)
>
> You'll need to add "-lm" to the command line.
>
> Was this using one of the configure scripts? Or a hand-made makefile?
>
> Dan.
>
>
>>
>> Any ideas?
>> Thanks.
>> ___
>> 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
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compilation fails under Linux (Ubuntu) when FTS5 is enabled

[Dan Kennedy]

On 10/17/2015 06:47 PM, tonyp at acm.org wrote:
> What the subject says and I get this error:
>
> /tmp/cco8QBGJ.o: In function `fts5Bm25GetData':
> sqlite3.c:(.text+0xb521b): undefined reference to `log'
>
> (using the latest 3.9.1 release)

You'll need to add "-lm" to the command line.

Was this using one of the configure scripts? Or a hand-made makefile?

Dan.


>
> Any ideas?
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] some thing wrong under android.

[Dan Kennedy]

On 10/16/2015 03:22 PM, ???_Ren? wrote:
> my code like below:
> --
>
>
> const char* sql =
> "begin;"
> "create table if not exists personal("
> "user_id integer primary key autoincrement,"
> "type int not null,"
> "user_name char(100) unique not null,"
> "password char(200),"
> "nick_name char(100),"
> "image blob(204800),"
> "round int,"
> "win int,"
> "kill int,"
> "death int,"
> "coin int,"
> "diamond int,"
> "vip int,"
> "level int,"
> "experience int,"
> "finished_story int,"
> "last_online_date int"
> ");"
> "create table if not exists fragment("
> "id integer primary key autoincrement,"
> "user_id int not null references personal(user_id),"
> "type int not null,"
> "amount int,"
> "finished_times int"
> ");"
> "create unique index if not exists fragment_index on fragment(user_id asc, 
> type asc);"
> "commit;"
> ;
>
>
> char* errmsg = 0;
> int ret = sqlite3_exec(m_db, sql, 0, 0, );
> sqlite3_free(errmsg);
>
>
>
>
> --
> under ios, it work nice.
> but under android it don't work.
>
>
> i have debug this error, and find the rules are:
> 1).when i delete that statement, it work nice:
> "create unique index if not exists fragment_index on fragment(user_id asc, 
> type asc);"
>
>
> 2).when i delete these statements, it work nice too.
> "begin;"
> "commit;"
>
>
> so, i think it don't support create index in a transaction under android.
>
>
> do you have any suggest for me?

You may need to ensure that SQLite stores temp files in memory. Either 
by building with -DSQLITE_TEMP_STORE=3 or by executing "PRAGMA 
temp_store=memory" after opening the db connection.

https://www.sqlite.org/tempfiles.html#tempstore

Dan.




>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fts5aux?

[Dan Kennedy]

On 10/15/2015 03:35 AM, Philip Bennefall wrote:
> Hi all,
>
> Are there any plans in fts5 to add support for something equivalent to 
> fts4aux? This would be of great use to me.

There is, but it's not actually finished or documented yet. It still 
does a linear scan of the entire FTS index for every query. To create 
the table:

   CREATE VIRTUAL TABLE vvv USING fts5vocab(, );

where  may be either "row" or "column".

Code is here: http://www.sqlite.org/src/artifact/85ebf2e93089c

Dan.

[sqlite] compiling sqlite3 with FTS5 support

[Dan Kennedy]

On 10/09/2015 10:18 AM, chromedout64 at yahoo.com wrote:
> I noticed there was talk in the other thread of including FTS5 support in the 
> amalgamation. That would be great and it seems like that might solve my 
> problem. Is that correct?
>
> Also, I noticed what may be a couple typos in the FTS5 documentation at 
> https://www.sqlite.org/fts5.html.
> In the external content table section, the example states:
> CREATE TABLE fts USING fts5(a, c, content=tbl, content_rowid=d);
> I think it should be CREATE VIRTUAL TABLE instead of CREATE TABLE.
>
> Also, in the overview section, the text says:
> The expression to the right of the MATCH operator must be the nameof the FTS5 
> table. The expression on the left must be a text value specifyingthe term to 
> search for.I think the right and left's are backwards.

Thanks for these. Now fixed.

Dan.

[sqlite] Problem with sqlite3_db_filename

[Dan Kennedy]

On 10/08/2015 03:51 AM, Bart Smissaert wrote:
> As I understand it this should produce a filepointer to the filepath of the
> attached database, given the database handle of file the other database was
> attached to and the database name of the attached database. I checked all
> the return values and also did a select involving tables in both
> databases and all goes fine, so I can be sure that the other database is
> attached OK.
> All I get from sqlite3_db_filename is zero, so no valid file pointer. No
> error messages though.
>
> I am accessing sqlite3.dll (Windows 7) via a std_call dll as I am working
> in VBA here.
>
> Any suggestions what could be the problem?
>
> I am running 3.8.11.1
>

The program below works here.

I'm seeing full paths for databases "main" and "aux", and a zero-length 
nul-terminated string for "next" (the in-memory database).

Dan

-



#include 
#include 
#include 

int main(int argc, char **argv){
   int rc;
   sqlite3 *db;

   rc = sqlite3_open("test.db", );
   if( rc!=SQLITE_OK ){
 fprintf(stderr, "sqlite3_open: %s\n", sqlite3_errmsg(db));
 exit(1);
   }

   rc = sqlite3_exec(db, "ATTACH 'other.db' AS 'aux'", 0, 0, 0);
   if( rc!=SQLITE_OK ){
 fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
 exit(1);
   }

   rc = sqlite3_exec(db, "ATTACH ':memory:' AS 'next'", 0, 0, 0);
   if( rc!=SQLITE_OK ){
 fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
 exit(1);
   }

   printf("main  db is: %s\n", sqlite3_db_filename(db, "main"));
   printf("aux   db is: %s\n", sqlite3_db_filename(db, "aux"));
   printf("next  db is: %s\n", sqlite3_db_filename(db, "next"));

   return 0;
}

[sqlite] sqlite-users Digest, Vol 94, Issue 4

[Dan Kennedy]

On 10/06/2015 02:45 AM, Andrew Cunningham wrote:
>> Ok. My first guess is that you are missing the "suggested index" on the
>> child table. Without this, if there is a foreign key violation in the
>> db, each insert on the parent table will cause a linear scan of the
>> entire child table. With the index, it's just a simple seek.
>>
>> https://www.sqlite.org/foreignkeys.html#fk_indexes
>>
>> If you're unsure, open your database using the command line tool and
>> dump the schema using the ".schema" command. Post that here and someone
>> should be able to tell you if you're missing an index or not.
>>
>
> OK.
>
> D:\ODB\odb-examples-2.4.0\relationship>D:\ODB\sqlite3.exe test.db
> SQLite version 3.8.11.1 2015-07-29 20:00:57
> Enter ".help" for usage hints.
> sqlite> .schema
> CREATE TABLE "relation_employer" (
>"name" TEXT NOT NULL PRIMARY KEY);
> CREATE TABLE "relation_project" (
>"name" TEXT NOT NULL PRIMARY KEY);
> CREATE TABLE "relation_employee" (
>"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>"first" TEXT NOT NULL,
>"last" TEXT NOT NULL,
>"employer" TEXT NOT NULL,
>CONSTRAINT "employer_fk"
>  FOREIGN KEY ("employer")
>  REFERENCES "relation_employer" ("name")
>  DEFERRABLE INITIALLY DEFERRED);
> CREATE TABLE "relation_employee_projects" (
>"object_id" INTEGER NOT NULL,
>"value" TEXT NOT NULL,
>CONSTRAINT "object_id_fk"
>  FOREIGN KEY ("object_id")
>  REFERENCES "relation_employee" ("id")
>  ON DELETE CASCADE,
>CONSTRAINT "value_fk"
>  FOREIGN KEY ("value")
>  REFERENCES "relation_project" ("name")
>  DEFERRABLE INITIALLY DEFERRED);
> CREATE INDEX "relation_employee_projects_object_id_i"
>ON "relation_employee_projects" ("object_id");

I think you need indexes on the child key columns that don't already 
have them. i.e.

   CREATE INDEX relation_employee_projects_value_i ON 
relation_employee_projects_value(value);
   CREATE INDEX relation_employee_employer_i ON relation_employee(employer);

Dan.

[sqlite] incorrect R-tree documentation

[Dan Kennedy]

On 10/03/2015 02:04 AM, Clemens Ladisch wrote:
> Hi,
>
> the R-tree documentation says:
> | Attempts to insert something other than an integer into the first
> | column, or something other than a numeric value into the other
> | columns, will result in an error.
>
> This is not actually true:
>
>> create virtual table t using rtree(id, x1, x2);
>> insert into t values(null, null, null);
>> insert into t values('xxx', 'xxx', x'1234');
>> select * from t;
> 1|0.0|0.0
> 0|0.0|0.0

Thanks for pointing this out. Docs now updated here:

   http://sqlite.org/docsrc/info/56eab0136ce41732

Dan.

[sqlite] (no subject)

[Dan Kennedy]

On 10/03/2015 03:19 AM, Andrew Cunningham wrote:
>
>
> SQLite uses a counter to enforce deferred foreign keys. Each time an
> operation violates a constraint it increments that counter.
>
> If the counter is greater than 0, then for every operation that might
> potentially correct the violation (which means virtually every
> operation
> on any table that is the parent or child in an FK constraint) it does
> extra seeks to see if it really does. i.e. each time you insert
> into the
> parent table, it seeks within the child table to see if the insert
> fixes
> a foreign key violation. And if it does, the counter is decremented.
>
> So we know there is overhead, but I'm quite surprised that it is 1000x
> slower.
>
> How large is the database? So large that these extra seeks could be
> performing real IO (i.e. having to go all the way to the disk for
> every
> seek, not just finding the data in the OS cache)?
>
>
> OK, that kinds of makes sense in explaining where the problem comes 
> from. I really do see an exponential growth in execution time 
> depending on how many INSERTS are made after the first FK violation 
> occurs.
>
> I have a very simple example that , for example, takes 0.2s with no FK 
> violations and 25s ( about  100x slower) when a single FK violation is 
> forced before I start doing the "mass creation".
>
> This is a small database, creating 500  parent objects, each with 50 
> child objects.
> If I change (double) the number of objects to 1000 the time changes to 
> 0.4s and 100s. It looks like an N^2 problem. So you can see that very 
> quickly my factor of 1000x is very easy to reach..
>
>
> When FK checking is in DEFERRED mode, the implication is that no FK 
> checking is made until the COMMIT.
>
> That's very useful, as often it is impossible to make the 
> modifications done in the correct order to satisfy all the 
> FK=IMMEDIATE constraints, even though the end result is a 
> "referentially correct" database.
>
> So what you are saying above makes DEFERRED mode a lot less useful.
>
> DISCLAIMER: I am use ODB ( the awesome C++/ORM tool) to do these tests.
>
>
>


Hi,

Ok. My first guess is that you are missing the "suggested index" on the 
child table. Without this, if there is a foreign key violation in the 
db, each insert on the parent table will cause a linear scan of the 
entire child table. With the index, it's just a simple seek.

https://www.sqlite.org/foreignkeys.html#fk_indexes

If you're unsure, open your database using the command line tool and 
dump the schema using the ".schema" command. Post that here and someone 
should be able to tell you if you're missing an index or not.

Dan.

[sqlite] SQLite slows by a factor of 1000 after foreign key violation

[Dan Kennedy]

On 10/01/2015 10:32 PM, Andrew Cunningham wrote:
> I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY
> violation occurs, SQLite will continue to run, but performance slows down
> by about 1000x. Breaking into the code shows that it seems to be
> continually re-reading the database. When I finally get to COMMIT , an
> exception is thrown, but this can take hours , literally as I have a single
> global transactions.
>
> Of course the solution is to debug the problem by setting the mode to
> IMMEDIATE, detecting the issue at the source, then returning to DEFERRED.
>
> But the '1000x slowdown' behavior is an interesting quirk I don't really
> understand.
>


SQLite uses a counter to enforce deferred foreign keys. Each time an 
operation violates a constraint it increments that counter.

If the counter is greater than 0, then for every operation that might 
potentially correct the violation (which means virtually every operation 
on any table that is the parent or child in an FK constraint) it does 
extra seeks to see if it really does. i.e. each time you insert into the 
parent table, it seeks within the child table to see if the insert fixes 
a foreign key violation. And if it does, the counter is decremented.

So we know there is overhead, but I'm quite surprised that it is 1000x 
slower.

How large is the database? So large that these extra seeks could be 
performing real IO (i.e. having to go all the way to the disk for every 
seek, not just finding the data in the OS cache)?

Thanks,
Dan.

[sqlite] FTS5 returns "corrupt" plus trailing zero

[Dan Kennedy]

On 09/21/2015 05:14 PM, Ralf Junker wrote:
> My implementation of
>
>   http://www.sqlite.org/src/artifact/400384798349d658?ln=94-96
>
> returns "corrupt" plus a trailing zero, that is 8 characters in total.
>
> Maybe this line
>
>   http://www.sqlite.org/src/artifact/4fdbc0a321e3a1d7?ln=5364
>
> should be corrected to
>
>   sqlite3Fts5BufferSet(, , 7, (const u8*)"corrupt");
>
> so that the number of characters passed matches the length of "corrupt".

Finally noticed this. Thanks.

   http://www.sqlite.org/src/info/3a9f076250d9559d

Dan.

[sqlite] FTS5 stopwords

[Dan Kennedy]

On 09/14/2015 09:13 PM, Abilio Marques wrote:
> ?Hi,
>
> I know I'm a newcomer into the SQLite project, but I'm excited about what
> FTS5 has to offer. To me it seems simple and powerful, and has some really
> nice ideas.
>
> Is it possible for me to contribute on the module, or is it too late for
> that?
>
> I would like to mention two new ideas I would offer to introduce. First, a
> customizable list of stopwords:
>
> https://en.wikipedia.org/wiki/Stop_words
> ?
> (I didn't find anything similar to that in the documentation, am I missing
> something?)
>
> I know I can add it via a custom tokenizer, but wouldn't it be useful to
> have it straight out of the box?

Hi,

I think such a thing would be implemented using the custom tokenizer API 
even if it were shipped as part of FTS5. As a "wrapper tokenizer" 
similar to the built-in porter tokenizer perhaps.

If we had code for a stop-words implementation that seemed like it would 
work for everybody and any licensing issues could be worked out then 
there's no reason something like that couldn't be made part of FTS5.

Dan.

[sqlite] FTS5 stopwords

[Dan Kennedy]

On 09/14/2015 09:13 PM, Abilio Marques wrote:
> ?Hi,
>
> I know I'm a newcomer into the SQLite project, but I'm excited about what
> FTS5 has to offer. To me it seems simple and powerful, and has some really
> nice ideas.
>
> Is it possible for me to contribute on the module, or is it too late for
> that?
>
> I would like to mention two new ideas I would offer to introduce. First, a
> customizable list of stopwords:
>
> https://en.wikipedia.org/wiki/Stop_words
> ?
> (I didn't find anything similar to that in the documentation, am I missing
> something?)
>
> I know I can add it via a custom tokenizer, but wouldn't it be useful to
> have it straight out of the box?
>
>
> Also, I would like to mention the usefulness of some statistics to create
> more advanced ranking formulas. Things like: the Longest Common Subsequence
> between query and document, number of unique matched keywords, etc. These
> and other values are really useful in applications where bm25 is not
> suitable or enough.

Hi,

 From an FTS5 custom auxiliary function, there are two ways to find the 
token offset of every phrase match in the current document:

The xInstCount()/xInst() allows random access to an array of matches - 
i.e. give me the phrase number, column and token offset of the Nth match:

   https://www.sqlite.org/draft/fts5.html#xInstCount

And xPhraseFirst()/xPhraseNext() allow the user to iterate through the 
matches for a specific query phrase within the current document:

   https://www.sqlite.org/draft/fts5.html#xPhraseFirst

xPhraseFirst/xPhraseNext is faster, but xInstCount/xInst can be easier 
to use.

It should be possible to build the sorts of things you're talking about 
on top of one of those, no? The example matchinfo() code contains code 
to determine the longest common subsequence here:

   http://www.sqlite.org/src/artifact/e96be827aa8f5?ln=259-281

Feedback from anyone who actually tries to use this API much appreciated.

Dan.



>
> I come from using an engine called Sphinx Search (used on huge things like
> Craigslist), which offers such factors. Using them, they have defined
> rankers that mix bm25 with proximity, and some other they call
> SPH_RANK_SPH04, which includes a weighting boost for the result appearing
> at the beginning of the text field, and a bigger boost if its an exact
> match:
>
> http://sphinxsearch.com/docs/latest/builtin-rankers.html
>
> The formulas (in sphinx higher is better) for them are:
> http://sphinxsearch.com/docs/latest/formulas-for-builtin-rankers.html
>
> And the list of supported factor is:
> http://sphinxsearch.com/docs/latest/ranking-factors.html.
>
> Of course having all of them would be overkill, but if you find them
> interesting, we can get the most useful ones, allowing people to build
> rankers to their own needs.
>
>
> ?Once again, you people are the experts and know if such ideas are feasible
> and where is the right place to include them, so please tell me your
> opinions.
>
>   ?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 documentation typo

[Dan Kennedy]

On 09/14/2015 06:31 PM, Abilio Marques wrote:
> While reading the documentation draft for FTS5, I spotted this on
> section 4.3.3 .
> I believe the first example is missing quotation marks around the word 
> 'porter':
>
> *-- Two ways to create an FTS5 table that uses the porter tokenizer to
> -- stem the output of the default tokenizer (unicode61). *
> CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = porter);
> CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter unicode61');

Hi,

I think it's Ok as is. An "option value" can be eitehr an FTS5 bareword 
or string literal. See the 4th paragraph here:

   https://www.sqlite.org/fts5.html#section_4

Dan.

[sqlite] How stable is FTS5 considered?

[Dan Kennedy]

On 09/14/2015 01:45 AM, Abilio Marques wrote:
> Hi,
>
>
>
> I've been into this mailing list for a month now, and I think I've heard
> FTS5 mentioned a couple of times. Back when I first saw it, I remember it
> to be labeled with something close to beta or preliminary.
>
>
>
> Long story short, I've previously worked with a dedicated search engine
> called Sphinx Search. One of the things people love about it, is it's
> ability to be linked to Snowball (http://snowball.tartarus.org), which is a
> project created by Dr. Martin Porter. This code includes stemmers in
> several other languages (Spanish, French, Portuguese, Italian, German,
> Dutch, Swedish, Norwegian, Danish, Russian, Finnish and even an improved
> English version), which would be an upgrade over the present FTS5 condition:
>
>
>
> "The porter stemmer algorithm is designed for use with English language
> terms only - using it with other languages may or may not improve search
> utility."
>
>
>
> I'm thinking about a possible approach to get Snowball working with SQLite.
> I believe an extension is the way to go, as Snowball is published under the
> BSD license (and so I guess it cannot be mixed with public domain code).
>
>
>
> But I have no experience mixing BSD and public domain, so anyone with more
> information can shed a light on that matter?
>
>
>
> Second, and the most important question for me is, can I consider FTS5
> stable enough to start working on the extension?

I think so.

The custom tokenizer API changed just recently in order to support synonyms:

   http://www.sqlite.org/src/info/0b7e4ab8abde3ae3

but I don't expect it to change again. The updated API is described here:

   http://sqlite.org/draft/fts5.html#section_7_1

For example code, see the built-in tokenizers:

   http://www.sqlite.org/src/artifact/f380f46f341af9c9

Dan.

[sqlite] SQLITE_READONLY_ROLLBACK due to expected race?

[Dan Kennedy]

On 09/09/2015 08:48 PM, Matthew Flatt wrote:
> The documentation for SQLITE_READONLY_ROLLBACK suggests that it will
> only happen as a result of a previous crash or power failure, where a
> hot journal is left behind. I'm seeing that error without those events
> and with a small number of concurrent readers and writers.
>
> In the implementation of SQLite, comments in hasHotJournal() mention
> the possibility of a false positive (referencing ticket #3883, which I
> cannot find) and how it will be handled in the playback mechanism after
> obtaining an exclusive lock. The check for a read-only connection after
> hasHotJournal() is called, however, happens (and must happen?) before
> that exclusive lock is acquired. So, it seems like the race-provoked
> false positive could trigger a false SQLITE_READONLY_ROLLBACK error.
>
> If that's right, then for my application's purposes, it works to detect
> SQLITE_READONLY_ROLLBACK and just try again. I'd like to make sure I'm
> not missing some other problem in my system, though.


I think your analysis is correct.

If you open a database that has a journal file with a read-only connect, 
stop it here using the debugger:

   http://www.sqlite.org/src/artifact/4784012f80b21?ln=4875

delete the journal file and then let the process continue, you do indeed 
get an SQLITE_READONLY_ROLLBACK error.

Dan.



>
> Thanks!
> Matthew
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS: Escaping MATCH expressions

[Dan Kennedy]

On 09/09/2015 07:56 PM, Lohmann, Niels, Dr. (CQTN) wrote:
> Hi there,
>   
> I have a question regarding the expressions that are valid after MATCH: Is 
> there a way to escape a string str such that I can safely bind it to variable 
> @var in a statement like "SELECT * FROM myFtsTable WHERE myFtsTable MATCH 
> @var;"?
>   
> In particular, I encountered error messages with strings like "TEST.*" or 
> "TEST'*".

I don't think there is a foolproof way to do that with FTS4. Enclosing 
the text in double-quotes might help, but then there is no way to escape 
embedded double quotes.

In FTS5 you can enclose tokens in double quotes and escape embeded quote 
characters in the usual SQL way (by doubling them). i.e.

   ... MATCH '"TEST.*"'

Or for {TEST"*}:

   ... MATCH '"TEST""*"'

Dan.




>   
> All the best
> Niels
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Dan Kennedy]

On 09/06/2015 09:23 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> I've discovered a potential bug in handling of SQLite database
>> files on gvfs mounted network shares.
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.

The other problem is that it's 2.8.17. I think the most recent bugfix on 
the 2.* line was in 2007. 2.8.17 was the last release, and that was in 
December 2005.

Dan.

[sqlite] SQLite3 trunk error with old database with fts3/4

[Dan Kennedy]

On 09/04/2015 10:13 PM, Domingo Alvarez Duarte wrote:
> Hello again !
>
> I looked at the documentaion again and realized that I was alread calling
> sqlite3_close_v2 the I commented out all of the sqlite3_next_stmt cleanup and
> no segfaults so far, I'll see if memory is released on a long running process
> to certify that everything is fine.

The trouble with sqlite3_close_v2() is that after you call it you can't 
safely pass the db handle to sqlite3_next_stmt() - the data structure 
may have already been freed.

Dan.



>
> Thanks a lot for your help !
>
>
>
>>   Fri Sep 04 2015 4:57:57 pm CEST CEST from "Domingo Alvarez Duarte"
>>   Subject: Re: [sqlite] SQLite3 trunk error 
>> with
>> old database with fts3/4
>>
>>   Hello !
>>
>> I did something similar to your sugestion (sqlite3_next_stmt(db, NULL))
>> and
>> it still segfaults.
>>
>> What you mention about fts3/4 having prepared statemtns that and somehow
>> I'm
>> doing a double free a good point.
>>
>> And will be sad to not be able to use sqlite3_next_stmt(db, NULL) to
>> finalize
>> any open preapred statemnt, because it's very handy when using "exceptions"
>> and having a single point to do the cleanup.
>>
>> Can somehow sqlite3_prepare somehow have any extra parameter to indicated
>> that we are using it from an extension and somehow sqlite3_next_stmt detect
>> it and skip it ?
>>
>> Or any way to safely have a central point to do a cleanup ?
>>
>> Cheers !
>>
>>   
>>> Fri Sep 04 2015 4:44:02 pm CEST CEST from "Dan Kennedy"
>>>  Subject: Re: [sqlite] SQLite3 trunk error with 
>>> old
>>> database with fts3/4
>>>
>>> On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
>>>
>>>   
>>>> Hello again !
>>>>
>>>> On mac os x some time ago I was getting segfaults here and tought that it
>>>> was
>>>> caused by the way os x manage memory but it doesn't seem correct.
>>>>
>>>> The error happens on this code that is called before call sqlite3_close:
>>>>
>>>> sqlite3 *db = sdb->db;
>>>> sqlite3_stmt* statement = NULL;
>>>> int count = 0;
>>>> while ((statement = sqlite3_next_stmt(db, statement)))
>>>> {
>>>> //do no close statements because garbage collector will
>>>> do it
>>>> //on MacOSX we get segfaults finalizing statements here
>>>> printf("sq_sqlite3_close_release:stmt:%s\n",
>>>> sqlite3_sql(statement));
>>>> sqlite3_finalize(statement);
>>>> count++;
>>>> }
>>>> if (count) return sq_throwerror(v, _SC("closing database with
>>>> %d statements not closed."), count);
>>>>
>>>>
>>>   Hi,
>>>
>>> Two problems:
>>>
>>> After you have finalized a statement handle, it may not be passed to
>>> sqlite3_next_stmt(). Change the while() line to:
>>>
>>> while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...
>>>
>>> Another reason not to do this before calling sqlite3_close() is that the
>>> FTS module may be managing some of these statement handles. So if you
>>> finalize() them before sqlite3_close() is called, then when the FTS
>>> module is shut down as part of the eventual sqlite3_close() call, it may
>>> pass the same statement handle pointers to sqlite3_finalize() - similar
>>> to a double-free of any other object or memory allocation. SQLite
>>> includes checks to try to return SQLITE_MISUSE instead of crashing when
>>> this happens, but they only work some of the time - this scenario can
>>> still cause crashes or heap corruption.
>>>
>>> A workaround is to call sqlite3_close() on the db, then do the above
>>> only if it returns SQLITE_BUSY. This works because, even though it
>>> fails, the first sqlite3_close() shuts down the FTS module -
>>> guaranteeing that it is no longer holding pointers to statement handles.
>>>
>>> Even better is not to leak statement handle pointers. The
>>> sqlite3_next_stmt() API should really only be used to help track down
>>> leaks, not to do cleanup.
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>   
>>>>   
>>>>> Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"

[sqlite] SQLite3 trunk error with old database with fts3/4

[Dan Kennedy]

On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
> Hello again !
>
> On mac os x some time ago I was getting segfaults here and tought that it was
> caused by the way os x manage memory but it doesn't seem correct.
>
> The error happens on this code that is called before call sqlite3_close:
>
>  sqlite3 *db = sdb->db;
>  sqlite3_stmt* statement = NULL;
>  int count = 0;
>  while ((statement = sqlite3_next_stmt(db, statement)))
>  {
>  //do no close statements because garbage collector will
> do it
>  //on MacOSX we get segfaults finalizing statements here
>  printf("sq_sqlite3_close_release:stmt:%s\n",
> sqlite3_sql(statement));
>  sqlite3_finalize(statement);
>  count++;
>  }
>  if (count) return sq_throwerror(v, _SC("closing database with
> %d statements not closed."), count);

Hi,

Two problems:

After you have finalized a statement handle, it may not be passed to 
sqlite3_next_stmt(). Change the while() line to:

   while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...

Another reason not to do this before calling sqlite3_close() is that the 
FTS module may be managing some of these statement handles. So if you 
finalize() them before sqlite3_close() is called, then when the FTS 
module is shut down as part of the eventual sqlite3_close() call, it may 
pass the same statement handle pointers to sqlite3_finalize() - similar 
to a double-free of any other object or memory allocation. SQLite 
includes checks to try to return SQLITE_MISUSE instead of crashing when 
this happens, but they only work some of the time - this scenario can 
still cause crashes or heap corruption.

A workaround is to call sqlite3_close() on the db, then do the above 
only if it returns SQLITE_BUSY. This works because, even though it 
fails, the first sqlite3_close() shuts down the FTS module - 
guaranteeing that it is no longer holding pointers to statement handles.

Even better is not to leak statement handle pointers. The 
sqlite3_next_stmt() API should really only be used to help track down 
leaks, not to do cleanup.

Dan.








> 
>>   Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
>>   Subject: Re: [sqlite] SQLite3 trunk error 
>> with
>> old database with fts3/4
>>
>>   Hello !
>>
>> I'm not sure where the problem is but this code worked without any problem
>> with previous sqlite3.
>>
>> Here is a backtrace of a segfault using gdb (the line numbers will not
>> match
>> standard sqlite3.c because I have some custom extensions):
>>
>> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>>
>> Program received signal SIGSEGV, Segmentation fault.
>> [Switching to Thread 0x73c70700 (LWP 22336)]
>> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>>  pDef=0x)
>>  at sqlite3.c:66869
>> 66869  if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
>> ){
>> (gdb) bt
>> #0  0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>>  pDef=0x)
>>  at sqlite3.c:66869
>> #1  0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
>>  p4type=-1431655766, p4=0x7fffe4181588)
>>  at sqlite3.c:66884
>> #2  0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
>>  aOp=0x7fffe40df508, nOp=)
>>  at sqlite3.c:66933
>> #3  0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
>>  p=0x7fffe408ac88) at sqlite3.c:68920
>> #4  0x00000047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
>>  at sqlite3.c:68941
>> #5  0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
>>  at sqlite3.c:68861
>> #6  0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
>>  at sqlite3.c:70500
>>
>>
>>   
>>> Fri Sep 04 2015 4:05:12 pm CEST CEST from "Dan Kennedy"
>>>  Subject: Re: [sqlite] SQLite3 trunk error with 
>>> old
>>> database with fts3/4
>>>
>>> On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote:
>>>
>>>   
>>>> Hello !
>>>>
>>>> After fix the index issues using an old sqlite3 executable (the trunk
>>>> refuse
>>>> to work on indexes created with single quotes on field names) I'm getting
>>>> ocasionaly memory errors when using fts3/4 searches, see error below:
>>>>
>>>> free(): corrupted unsorted chunks: 0x7fa3a01073a0
>>>>
>>>>
>>>   Is this error on the trunk or w