Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Tim Streater]

On 21 Dec 2019, at 21:42, Michael Walker (barrucadu)  
wrote:

> I'm not sure the attachment to my first email got through ...

Correct. The list strips them.



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

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Shawn Wagner]

The mailing list strips attachments, btw.

Anyways, looking at that, yeah, they're all text values:

sqlite> SELECT bookIsbn, typeof(bookIsbn) FROM books WHERE bookAuthor LIKE
'%Ambrose%';
bookIsbntypeof(bookIsbn)
--  
0486280381  text
9781496030  text
9781496030  text

There are other problems with the database too:

sqlite> PRAGMA integrity_check;
integrity_check
---
row 649 missing from index sqlite_autoindex_books_1
row 659 missing from index sqlite_autoindex_books_1
row 665 missing from index sqlite_autoindex_books_1
row 667 missing from index sqlite_autoindex_books_1
row 674 missing from index sqlite_autoindex_books_1
row 676 missing from index sqlite_autoindex_books_1

I'd start going through https://www.sqlite.org/howtocorrupt.html and trying
to figure out if anything there might have happened.


On Sat, Dec 21, 2019 at 1:43 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:

> Hi Shawn,
>
> Thanks for your response.  Though that doesn't seem to be the case:
>
> sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
> "9781496030825";
> 9781496030825|text
> sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
> "9780099477310";
> 9780099477310|text
>
> The column is a VARCHAR:
>
> CREATE TABLE `books` (
>`bookIsbn` VARCHAR NOT NULL PRIMARY KEY,
>`bookTitle` VARCHAR NOT NULL,
>`bookSubtitle` VARCHAR NOT NULL,
>`bookCover` VARCHAR NULL,
>`bookVolume` VARCHAR NOT NULL,
>`bookFascicle` VARCHAR NOT NULL,
>`bookVoltitle` VARCHAR NOT NULL,
>`bookAuthor` VARCHAR NOT NULL,
>`bookTranslator` VARCHAR NULL,
>`bookEditor` VARCHAR NULL,
>`bookSorting` VARCHAR NULL,
>`bookRead` BOOLEAN NOT NULL,
>`bookLastRead` TIMESTAMP NULL,
>`bookNowReading` BOOLEAN NOT NULL,
>`bookLocation` VARCHAR NOT NULL,
>`bookBorrower` VARCHAR NOT NULL,
>`bookCategoryCode` VARCHAR NOT NULL,
>FOREIGN KEY(`bookCategoryCode`) REFERENCES
> `book_categories`(`categoryCode`)
> );
>
> I'm not sure the attachment to my first email got through, so here's the
> database:
> https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite
>
>
> ‐‐‐ Original Message ‐‐‐
> On Saturday, 21 December 2019 21:37, Shawn Wagner 
> wrote:
>
> > Without seeing your table definition, this is just a guess, but maybe the
> > duplicate keys are stored as different types, with the primary key column
> > having an affinity that doesn't force one particular storage class:
> >
> > sqlite> CREATE TABLE test(id PRIMARY KEY);
> > sqlite> INSERT INTO test VALUES('12345');
> > sqlite> INSERT INTO test VALUES(12345);
> > sqlite> SELECT id, typeof(id) FROM test;
> > id typeof(id)
> >
> > 12345 text
> > 12345 integer
> > sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
> > id typeof(id)
> >
> > 12345 text
> >
> > On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
> > m...@barrucadu.co.uk> wrote:
> >
> > > Hi,
> > > I've somehow ended up with a table which contains two records for the
> same
> > > primary key - well actually I've got two primary keys like that, so I
> have
> > > four records with two primary keys between them.
> > > I've been unable to reproduce this from a clean database, so I attach
> my
> > > database file to this email.
> > > Here are some oddities:
> > >
> > > $ sqlite3 bookdb.sqlite
> > > SQLite version 3.28.0 2019-04-16 19:49:53
> > > Enter ".help" for usage hints.
> > > sqlite> select * from books where bookIsbn = "9781496030825";
> > > 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> > > Ambrose0||0|London||F
> > > sqlite> select * from books where bookIsbn = "9780099477310";
> > > 9780099477310|Catch-22||9780099477310.jpgHeller,
> > > Joseph0||0|London||F
> > > sqlite> .output books_issue
> > > sqlite> .dump books
> > > sqlite> .quit
> > >
> > > $ grep "9781496030825" < books_issue
> > > INSERT INTO books VALUES('9781496030825','Can Such Things
> > > Be?','','9781496030825.jpg','','','','Bierce,
> > > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > > INSERT INTO books VALUES('9781496030825','Can Such Things
> > > Be?','','9781496030825.jpg','','','','Bierce,
> > > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > >
> > > $ grep "9780099477310" < books_issue
> > > INSERT INTO books
> > >
>  VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > > INSERT INTO books
> > >
>  VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > >
> > > $ sqlite3 bookdb.sqlite
> > > SQLite version 3.28.0 2019-04-16 19:49:53
> > > Enter ".help" for usage 

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Michael Walker (barrucadu)]

Hi Shawn,

Thanks for your response.  Though that doesn't seem to be the case:

sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn = 
"9781496030825";
9781496030825|text
sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn = 
"9780099477310";
9780099477310|text

The column is a VARCHAR:

CREATE TABLE `books` (
   `bookIsbn` VARCHAR NOT NULL PRIMARY KEY,
   `bookTitle` VARCHAR NOT NULL,
   `bookSubtitle` VARCHAR NOT NULL,
   `bookCover` VARCHAR NULL,
   `bookVolume` VARCHAR NOT NULL,
   `bookFascicle` VARCHAR NOT NULL,
   `bookVoltitle` VARCHAR NOT NULL,
   `bookAuthor` VARCHAR NOT NULL,
   `bookTranslator` VARCHAR NULL,
   `bookEditor` VARCHAR NULL,
   `bookSorting` VARCHAR NULL,
   `bookRead` BOOLEAN NOT NULL,
   `bookLastRead` TIMESTAMP NULL,
   `bookNowReading` BOOLEAN NOT NULL,
   `bookLocation` VARCHAR NOT NULL,
   `bookBorrower` VARCHAR NOT NULL,
   `bookCategoryCode` VARCHAR NOT NULL,
   FOREIGN KEY(`bookCategoryCode`) REFERENCES 
`book_categories`(`categoryCode`)
);

I'm not sure the attachment to my first email got through, so here's the 
database: 
https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite


‐‐‐ Original Message ‐‐‐
On Saturday, 21 December 2019 21:37, Shawn Wagner  
wrote:

> Without seeing your table definition, this is just a guess, but maybe the
> duplicate keys are stored as different types, with the primary key column
> having an affinity that doesn't force one particular storage class:
>
> sqlite> CREATE TABLE test(id PRIMARY KEY);
> sqlite> INSERT INTO test VALUES('12345');
> sqlite> INSERT INTO test VALUES(12345);
> sqlite> SELECT id, typeof(id) FROM test;
> id typeof(id)
>
> 12345 text
> 12345 integer
> sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
> id typeof(id)
>
> 12345 text
>
> On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
> m...@barrucadu.co.uk> wrote:
>
> > Hi,
> > I've somehow ended up with a table which contains two records for the same
> > primary key - well actually I've got two primary keys like that, so I have
> > four records with two primary keys between them.
> > I've been unable to reproduce this from a clean database, so I attach my
> > database file to this email.
> > Here are some oddities:
> >
> > $ sqlite3 bookdb.sqlite
> > SQLite version 3.28.0 2019-04-16 19:49:53
> > Enter ".help" for usage hints.
> > sqlite> select * from books where bookIsbn = "9781496030825";
> > 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> > Ambrose0||0|London||F
> > sqlite> select * from books where bookIsbn = "9780099477310";
> > 9780099477310|Catch-22||9780099477310.jpgHeller,
> > Joseph0||0|London||F
> > sqlite> .output books_issue
> > sqlite> .dump books
> > sqlite> .quit
> >
> > $ grep "9781496030825" < books_issue
> > INSERT INTO books VALUES('9781496030825','Can Such Things
> > Be?','','9781496030825.jpg','','','','Bierce,
> > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > INSERT INTO books VALUES('9781496030825','Can Such Things
> > Be?','','9781496030825.jpg','','','','Bierce,
> > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> >
> > $ grep "9780099477310" < books_issue
> > INSERT INTO books
> > 
> > VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > INSERT INTO books
> > 
> > VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> >
> > $ sqlite3 bookdb.sqlite
> > SQLite version 3.28.0 2019-04-16 19:49:53
> > Enter ".help" for usage hints.
> > sqlite> drop table books;
> > sqlite>
> >
> > $ sqlite3 bookdb.sqlite < books_issue
> > Error: near line 697: UNIQUE constraint failed: books.bookIsbn
> > Error: near line 698: UNIQUE constraint failed: books.bookIsbn
> >
> >
> > Updating either affected record results in the second copy in the .dump
> > output being updated, the first copy has the original state.
> > The table has always had a primary key constraint, so I'm not sure how
> > it's ended up in its current state. However, even if there were not a
> > primary key constraint, there do seem to be two very real bugs here: SELECT
> > gives different results to .dump, and .dump is producing output which can't
> > be restored.
> > I'm not sure if you'll be able to make anything of this, as I say I
> > haven't been able to reproduce it from a blank database, but I figure
> > you'll be better at debugging this than me.
> > Thanks
> > --
> > Michael Walker (http://www.barrucadu.co.uk)
> >
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> sqlite-users mailing list
> 

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Shawn Wagner]

Without seeing your table definition, this is just a guess, but maybe the
duplicate keys are stored as different types, with the primary key column
having an affinity that doesn't force one particular storage class:

sqlite> CREATE TABLE test(id PRIMARY KEY);
sqlite> INSERT INTO test VALUES('12345');
sqlite> INSERT INTO test VALUES(12345);
sqlite> SELECT id, typeof(id) FROM test;
id  typeof(id)
--  --
12345   text
12345   integer
sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
id  typeof(id)
--  --
12345   text



On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:

> Hi,
>
> I've somehow ended up with a table which contains two records for the same
> primary key - well actually I've got two primary keys like that, so I have
> four records with two primary keys between them.
>
> I've been unable to reproduce this from a clean database, so I attach my
> database file to this email.
>
> Here are some oddities:
>
> ```
> $ sqlite3 bookdb.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> select * from books where bookIsbn = "9781496030825";
> 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> Ambrose0||0|London||F
> sqlite> select * from books where bookIsbn = "9780099477310";
> 9780099477310|Catch-22||9780099477310.jpgHeller,
> Joseph0||0|London||F
> sqlite> .output books_issue
> sqlite> .dump books
> sqlite> .quit
>
> $ grep "9781496030825" < books_issue
> INSERT INTO books VALUES('9781496030825','Can Such Things
> Be?','','9781496030825.jpg','','','','Bierce,
> Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> INSERT INTO books VALUES('9781496030825','Can Such Things
> Be?','','9781496030825.jpg','','','','Bierce,
> Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
>
> $ grep "9780099477310" < books_issue
> INSERT INTO books
> VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> INSERT INTO books
> VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
>
> $ sqlite3 bookdb.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> drop table books;
> sqlite>
>
> $ sqlite3 bookdb.sqlite < books_issue
> Error: near line 697: UNIQUE constraint failed: books.bookIsbn
> Error: near line 698: UNIQUE constraint failed: books.bookIsbn
> ```
>
> Updating either affected record results in the second copy in the .dump
> output being updated, the first copy has the original state.
>
> The table has always had a primary key constraint, so I'm not sure how
> it's ended up in its current state.  However, even if there were not a
> primary key constraint, there do seem to be two very real bugs here: SELECT
> gives different results to .dump, and .dump is producing output which can't
> be restored.
>
> I'm not sure if you'll be able to make anything of this, as I say I
> haven't been able to reproduce it from a blank database, but I figure
> you'll be better at debugging this than me.
>
> Thanks
>
> --
> Michael Walker (http://www.barrucadu.co.uk)
> ___
> 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

[sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Michael Walker (barrucadu)]

Hi,

I've somehow ended up with a table which contains two records for the same 
primary key - well actually I've got two primary keys like that, so I have four 
records with two primary keys between them.

I've been unable to reproduce this from a clean database, so I attach my 
database file to this email.

Here are some oddities:

```
$ sqlite3 bookdb.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> select * from books where bookIsbn = "9781496030825";
9781496030825|Can Such Things Be?||9781496030825.jpgBierce, 
Ambrose0||0|London||F
sqlite> select * from books where bookIsbn = "9780099477310";
9780099477310|Catch-22||9780099477310.jpgHeller, Joseph0||0|London||F
sqlite> .output books_issue
sqlite> .dump books
sqlite> .quit

$ grep "9781496030825" < books_issue
INSERT INTO books VALUES('9781496030825','Can Such Things 
Be?','','9781496030825.jpg','','','','Bierce, 
Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
INSERT INTO books VALUES('9781496030825','Can Such Things 
Be?','','9781496030825.jpg','','','','Bierce, 
Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');

$ grep "9780099477310" < books_issue
INSERT INTO books 
VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller, 
Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
INSERT INTO books 
VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller, 
Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');

$ sqlite3 bookdb.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> drop table books;
sqlite>

$ sqlite3 bookdb.sqlite < books_issue
Error: near line 697: UNIQUE constraint failed: books.bookIsbn
Error: near line 698: UNIQUE constraint failed: books.bookIsbn
```

Updating either affected record results in the second copy in the .dump output 
being updated, the first copy has the original state.

The table has always had a primary key constraint, so I'm not sure how it's 
ended up in its current state.  However, even if there were not a primary key 
constraint, there do seem to be two very real bugs here: SELECT gives different 
results to .dump, and .dump is producing output which can't be restored.

I'm not sure if you'll be able to make anything of this, as I say I haven't 
been able to reproduce it from a blank database, but I figure you'll be better 
at debugging this than me.

Thanks

--
Michael Walker (http://www.barrucadu.co.uk)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling SQLite without the database storage?

[Jens Alfke]

This may sound crazy, but is there a way to compile SQLite without its B-tree 
and table code? This would be for a use case with _only_ virtual tables — i.e. 
SQLite being used as a SQL query engine on top of a different data store*. It 
would be nice not to drag in too much unused code.

—Jens

* Yes, I happen to be experimenting with LMDB...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sanitising user input for FTS5 MATCH parameter

[Dan Kennedy]

On 20/12/62 22:03, test user wrote:

Hello,

I have a search box on a website that uses FTS5/MATCH.

MATCH seems to take its own custom language for matching.

1. Is it safe to just pass the users query to MATCH ? via the SQLite bind
FFI?


Users could specify a query that uses excessive resources. In 
particular, prefix searches for very common prefixes on large databases 
can use a lot of memory. I think it's otherwise safe though.



- This would give them full access to the FTS5 matching language.

2. If not, how should I be sanitising user input?

- E.g. How can I transform a string of words and text into a query? What
characters should I be removing or escaping? How can I prevent them using
the FTS5 keywords "AND" "OR" etc?
It really depends on what you want to allow. And how you want the query 
interpreted. If you want all input to be treated as a single phrase, 
enclose it in double-quotes, doubling any embedded " characters SQL 
style. Or, if you wanted the input treated as a list of terms separated 
by implicit AND, split the input on whitespace and then enclose each 
term in double-quotes. Details here:


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

Dan.






Thanks
___
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 URI mode=ro

[Keith Medcalf]

On Saturday, 21 December, 2019 03:27, Thomas Kurz  
wrote:

>I have a problem when opening a read-only database, which is a WAL-mode
>database.

>When trying to open it in read-only mode, i.e. using
>file:test.sqlite?mode=ro, SHM and WAL file are created. That's
>unpleasant, but the actual problem is the two files don't get deleted
>when closing the database.

>Steps to reproduce (OS=Windows, SQLite=3.30.1):

>.open test.sqlite
>create table test (a integer primary key autoincrement);
>insert into test (a) values (null);
>select * from test;
>.quit

>Now set the read-only attribute to test.sqlite, and continue:

>.open file:test.sqlite?mode=ro
>select * from test;   <-- creates wal and shm
>.quit

>Result: SHM and WAL files are kept.

A WAL database can only be opened as Read-Only if the WAL and SHM files exist.  
Therefore, when a WAL database is opened in read-only mode they must be created 
if they do not exist (and an error will ensue if they neither exist nor can be 
created).  However, when you close such a database, you (the royal you) have 
said that it is to be read-only.  If it is read-only why would you (the royal 
you) expect that the files would be deleted, since they must exist in order to 
open the file?

The three files (the database file plus the WAL and SHM file) can now be 
written to "read only media" such as a CD, and they will be able to be opened 
and closed as read-only without problems.  If the database was opened on CD 
media and and attempt was made to "delete" the SHM and WAL files, then that 
attempt would fail and there would be complaints that SQLite was attempting to 
"delete things from a read-only database".

This behaviour is almost certainly by design.  In fact, I do believe that I 
have managed to read documentation regarding it somewhere, but that somewhere 
does not yield specificity beyond "in the documentation on the sqlite.org 
website" at the moment.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] problem with URI mode=ro

[Simon Slavin]

On 21 Dec 2019, at 11:12am, Thomas Kurz  wrote:

>> Do the same thing again without the mode=ro
>> Do the files get deleted this time ?
> 
> No, this shows the same behavior, but in this case, it's actually what I'd 
> expect.

Your problem would seem to be related to all WAL operations, not just read-only 
status.

Section 4 of  states

" Usually, the WAL file is deleted automatically when the last connection to 
the database closes. However, if the last process to have the database open 
exits without cleanly shutting down the database connection, or if the 
SQLITE_FCNTL_PERSIST_WAL file control is used, then the WAL file might be 
retained on disk after all connections to the database have been closed. "

Section 2.3 of  states

" The shared-memory file has the same lifetime as its associated WAL file. The 
shared-memory file is created when the WAL file is created and is deleted when 
the WAL file is deleted. "

It would seem that the sqlite command-line tool should implement this 
correctly, if anything does.  I would say that he problem is not related to 
read-only status.

I checked your problem on my own setup:

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.sqlite
sqlite> PRAGMA journal_mode;
delete
sqlite> PRAGMA journal_mode=WAL;
wal
sqlite> PRAGMA journal_mode;
wal
sqlite> create table test (a integer primary key autoincrement);
sqlite> insert into test (a) values (123);
sqlite> select * from test;
123
sqlite> .quit

And got the same behaviour: the +wal and +shm files were not deleted. This 
would appear to contradict the documentation.  But I can't think of a way to 
check the state of SQLITE_FCNTL_PERSIST_WAL within the command-line tool.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing a statement cache

[Roger Binns]

On 16/12/2019 13:38, carsten.muencheberg wrote:
> I am working on a generic cache for prepared statements 

I really wish SQLite had a statement cache behind the scenes, so that
devs don't have to keep re-implementing a statement cache.  I would be
delighted to delete the statement cache code in APSW.

> The cache is a simple map from an SQL string to a statement pointer.

Note that you will need SQLite to parse the input string to get the SQL
string.  For example your API could be called with "select 3; select 4;"
and would need to be broken in the middle into two statements.  You need
to get all this stuff right.

I used the same map approach which requires you keeping an in-use flag
for the statement pointer.  This is because you may be supplied the same
SQL twice without the first being released yet.  My implementation only
has one statement per SQL text meaning additional executions of the same
SQL do not use the cache.

There are multiple copies of the SQL text too.  One copy will be in
whatever calls you, you need a copy to use for the key in the map, and
then SQLite internally keeps a third copy.  You can avoid that third
copy by using the v1 prepare method and handling SQLITE_SCHEMA yourself.
 It would be so much better if SQLite had the cache internally.

> 1. When to call sqlite3_reset()? It looks like the safest and easiest
> approach is to call sqlite3_reset() immediately after retrieving a
> statement from the cache.

Do so immediately when you are finished with the statement (eg about to
put it back in the cache).  That will release all the locks etc, as well
as free memory - eg if a binding is a long string or blob.

> Is there any disadvantage in regards to
> concurrency or performance in keeping dozens or hundreds of statements
> alive in a non reset state e.g. SELECT statements which have not stepped
> over all rows?

You will have considerably more memory allocated, in addition to held
locks etc.  Note that cleanup is going to happen.  You could do it all
at the very end, or I prefer to do it as soon as possible to keep the
footprint more compact.  The cache is supposed to be transparent.

> 2. When to call sqlite3_clear_bindings()?

Same thing - the sooner the better.

> but calling
> sqlite3_clear_bindings() can be a safeguard against accidentally
> executing a statement with old values?

If the cache is transparent then you must do so to avoid very hard to
diagnose bugs.

> 3. When to clear the cache?

My implementation has the developer specify the number of entries in the
cache (default 100).  In addition to the mapping between SQL text and a
statement, there is a linked list between the statements tracking least
recently used.  This is a fairly complex combined data structure, and
another reason SQLite should do it (one place to get right).

> I read that in some cases statements are automatically recompiled 

This is not relevant to a statement cache, and if you use the currently
documented APIs it is something you do not need to know or care about ever.

Behind the scenes each statement is transformed into byte code which is
what SQLite runs to perform a query.  This is necessary because you get
a result row at a time, so SQLite has to be able to suspend and resume
execution. https://sqlite.org/vdbe.html

For example the vdbe will mention column 3, and if the table schema has
changed, the same named column could now be column 4.  In the olden
days, if you tried to execute the vdbe SQLite would detect it was out of
date, return an error code (SQLITE_SCHEMA) and the developer would have
to reprepare the statement.  Now SQLite keeps a copy of the SQL text and
does the reprepare internally and transparently.

> 4. Other ideas, comments?

Keep asking the SQLite team to make an internal SQLite statement cache.
 I'd be happy to call different APIs even.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sanitising user input for FTS5 MATCH parameter

[test user]

Yeh but it doesn’t explicitly say anything about how secure it is to pass a
string directly to “match”.

Other scalar values that are passed to a query via binding are safe as they
are just data saved to the DB file.

But as the MATCH x string contains a language it is going to be interpreted
or compiled which is why I was asking how safe that would be.



On Fri, 20 Dec 2019 at 16:05, Jose Isaias Cabrera 
wrote:

>
> test user, on Friday, December 20, 2019 10:03 AM, wrote...
> >
> > Hello,
> >
> > I have a search box on a website that uses FTS5/MATCH.
> >
> > MATCH seems to take its own custom language for matching.
> >
> > 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind
> > FFI?
> >
> > - This would give them full access to the FTS5 matching language.
> >
> > 2. If not, how should I be sanitising user input?
> >
> > - E.g. How can I transform a string of words and text into a query? What
> > characters should I be removing or escaping? How can I prevent them using
> > the FTS5 keywords "AND" "OR" etc?
>
> Have you taken a look at the FTS5 site[1]?  It has lots of information
> there that may be helpful.
>
> josé
>
> [1] https://www.sqlite.org/fts5.html
>
> ___
> 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 URI mode=ro

[Thomas Kurz]

> Do the same thing again without the mode=ro
> Do the files get deleted this time ?

No, this shows the same behavior, but in this case, it's actually what I'd 
expect.

> Does the program have enough privs over the database file's folder ?

Yeah, sure, actually the file's on a FAT32 drive.

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

Re: [sqlite] problem with URI mode=ro

[Simon Slavin]

On 21 Dec 2019, at 10:27am, Thomas Kurz  wrote:

> Result: SHM and WAL files are kept.

Do the same thing again without the mode=ro

Do the files get deleted this time ?

Does the program have enough privs over the database file's folder ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem with URI mode=ro

[Thomas Kurz]

I have a problem when opening a read-only database, which is a WAL-mode 
database.

When trying to open it in read-only mode, i.e. using file:test.sqlite?mode=ro, 
SHM and WAL file are created. That's unpleasant, but the actual problem is the 
two files don't get deleted when closing the database.

Steps to reproduce (OS=Windows, SQLite=3.30.1):

.open test.sqlite
create table test (a integer primary key autoincrement);
insert into test (a) values (null);
select * from test;
.quit

Now set the read-only attribute to test.sqlite, and continue:

.open file:test.sqlite?mode=ro
select * from test;   <-- creates wal and shm
.quit

Result: SHM and WAL files are kept.

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