Re: [sqlite] Ensure a snapshot remains readable

[Gwendal Roué]

Hello Adam,

You may enjoy reading this recent thread, which is exactly about the same
topic:
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-September/086099.html

The crux of your issue is that sqlite3_snapshot_get() is not involved in
transaction management at all. It does not take any lock. It does not
prevent checkpointing. It won't prevent an external connection from writing
and triggering an automatic checkpoint, for example. Checkpoints invalidate
snapshots returned by sqlite3_snapshot_get(), making them unable to be used
with sqlite3_snapshot_open(). This is a given, and I learned in the
previously linked thread that it is unlikely to change.

If you want a read-only long-time access to a given state of the database,
then you need a dedicated transaction. The access will be guaranteed for
the duration of the transaction. Checkpoints won't be able to invalidate
it. It will be super robust.

You can, for example:

1. open a dedicated read-only connection
2. run BEGIN DEFERRED TRANSACTION
3. perform your reads
4. run COMMIT or ROLLBACK at the end of your reads.

This technique actually opens the transaction, "locks" a state of the
database for unlimited future accesses, on the first read. Not on the BEGIN
DEFERRED TRANSACTION statement.

Sometimes this is good enough (think about it for a while). But sometimes
you want to control the exact state of the snapshot. For example, you may
want to take a snapshot after what you call the "next official state
update".

A way to achieve this with the most extreme precision and robustness is the
following:

1. In a "writer" connection, COMMIT the "next official state update". Now
prevent any write in the database until step 4.
2. In the read-only "snapshot" connection, BEGIN DEFERRED TRANSACTION
3. In the read-only "snapshot" connection, perform *any kind of read* in
order to start the transaction for good. SELECT * FROM sqlite_master LIMIT
1 is good enough. Anything goes.
4. Now you can accept further writes in the "writer" connection.
5. And now you can read from the "snapshot" connection and access a
guaranteed "official state" until the end of the "snapshot" transaction.

Hope this helps,
Gwendal Roué
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA table_info fails with "no such tokenizer"

[Gwendal Roué]

Hello,

The fts5 table in your database was created with a custom FTS5 tokenizer
named "TrackerTokenizer" (see
https://www.sqlite.org/fts5.html#custom_tokenizers).

Custom FTS5 tokenizers must be registered in each SQLite connection to the
database file, or you get an error like "no such tokenizer:
TrackerTokenizer".

You thus have two options:

1. Grab information about your Python support for SQLite, and register a
custom tokenizer named "TrackerTokenizer" in your connection. For example,
check https://pypi.org/project/sqlitefts/
2. Ask for a database file that does not use any custom FTS5 tokenizer.

Gwendal Roué

On Sun, Sep 29, 2019 at 12:24 PM Anatoli Babenia 
wrote:

> Python 3.7.4 (default, Jul  9 2019, 16:32:37)
> [GCC 9.1.1 20190503 (Red Hat 9.1.1-1)] on linux
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import sqlite3
> >>> conn = sqlite3.connect("/home/anatoli/.cache/tracker/meta.db")
> >>> cursor = conn.cursor()
> >>> cursor.execute('PRAGMA main.table_info("fts5")')
> Traceback (most recent call last):
>   File "", line 1, in 
> sqlite3.OperationalError: no such tokenizer: TrackerTokenizer
>
>
> The `sqlite3 meta.db .dump` works without errors. The `fts5` table
> referenced in the dump.
>
> INSERT INTO
> sqlite_master(type,name,tbl_name,rootpage,sql)VALUES('table','fts5','fts5',0,'CREATE
> VIRTUAL TABLE fts5 USING fts5(content="fts_view", "nfo:fileName",
> "nco:title", "nfo:genre", "nmm:genre", "mtp:creator",
> "nco:nameFamily", "nco:nameGiven", "nco:nameAdditional",
> "nco:phoneNumber", "nao:prefLabel", "nao:description", "nco:fullname",
> "nco:nickname", "nco:contactGroupName", "nfo:tableOfContents",
> "ncal:comment", "ncal:location", "ncal:summary", "ncal:contact",
> "ncal:description", "nmo:messageSubject", "nmm:albumTitle",
> "nmm:artistName", "nco:department", "nco:role", "nco:note",
> "nie:keyword", "nco:imID", "nco:imNickname", "nco:emailAddress",
> "nmm:category", "nfo:fontFamily", "nco:region", "nco:country",
> "nco:extendedAddress", "nco:streetAddress", "nco:postalcode",
> "nco:locality", "nco:county", "nco:district", "nco:pobox",
> "nie:title", "nie:subject", "nie:plainTextContent", "nie:description",
> "nie:comment", "nid3:title", "nid3:albumTitle", "nid3:contentType",
> tokenize=TrackerTokenizer)');
>
>
> --
> Anatoli Babenia
> ___
> 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] Conflict between snapshots and checkpoints

[Gwendal Roué]

According to
http://dbmsmusings.blogspot.com/2019/06/correctness-anomalies-under.html,
SNAPSHOT ISOLATION is stronger than REPEATABLE READ, in that it prevents
"phantom reads" (
http://dbmsmusings.blogspot.com/2019/05/introduction-to-transaction-isolation.html).
I think SQLite prevents phantom reads, and so we actually get actual
SNAPSHOT ISOLATION (as written in https://www.sqlite.org/isolation.html).

On Mon, Sep 30, 2019 at 10:06 AM Dominique Devienne 
wrote:

> On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf  wrote:
>
> > On Sunday, 29 September, 2019 01:28, Gwendal Roué <
> gwendal.r...@gmail.com>
> > wrote:
> > >Those N reader connections allow concurrent database reads. Those
> "reads"
> > are
> > >generally wrapped in a deferred transaction which provides snapshot
> > >isolation.
> >
> > No, it provides REPEATABLE-READ isolation.  There is no actual "snapshot"
> > taken and no snapshot exists.
>
> You are merely not seeing data written to the WAL transaction log at a
> > point-in-time subsequent to the point in time at which you commenced the
> > "repeatable-read".
> >
>
> I don't see where you are going with this Keith.
> Repeatable-reads "in the past" *are* snapshots IMHO.
>
> The WAL file *does* contain enough information combined with the main
> database file pages,
> to logically *and* physically represent a "snapshot" of the DB at that
> point-in-time. So not calling
> it a "snapshot" is a stretch at the very least. What is a "snapshot"
> according to you, if that's not it?
>
> And also why do you think Richard, who knows a thing or two about
> databases, called these API *snaphot* then?
>
> I'm genuinely curious here. I think I disagree with you, but most time I
> do, I'm wrong, so I'd like to understand, really. --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] Conflict between snapshots and checkpoints

[Gwendal Roué]

Yes, this is exactly as you say. Isolation is very important, and I
carefully make sure that it is guaranteed to users who need it. This has
been working well for a few years now, GRDB.swift is very robust. My recent
focus was on SQLite snapshots, and you and other members of this mailing
list have been very helpful by completing the existing documentation :-)

Gwendal

On Sun, Sep 29, 2019 at 7:41 PM Richard Damon 
wrote:

> On 9/29/19 11:40 AM, Gwendal Roué wrote:
> > Thank you very much Keith.
> >
> > Apologies for my imprecise vocabulary, and the use of the same "snapshot"
> > word with different meanings.
> >
> > I have used the term "snapshot isolation" as used in
> > https://www.sqlite.org/isolation.html; But I'll remember about
> > REPEATABLE-READ isolation.
> >
> > I also thank you very much for confirming that preventing checkpoints
> > allows the WAL markers to remain valid.
> >
> > My goal is to provide a set of concurrency primitives that are useful for
> > GUI app developers. I'm learning a lot about SQLite in the process, of
> > course.
>
> And since you reused the connection (otherwise you just need to keep the
> transaction open and the snapshot was preserved) you don't have isolation.
>
> Quote:
>
>
> No Isolation Between Operations On The Same Database Connection
>
> SQLite provides isolation between operations in separate database
> connections. However, there is no isolation between operations that
> occur within the same database connection.
>
>
>
> --
> Richard Damon
>
> ___
> 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] Conflict between snapshots and checkpoints

[Gwendal Roué]

Thank you very much Keith.

Apologies for my imprecise vocabulary, and the use of the same "snapshot"
word with different meanings.

I have used the term "snapshot isolation" as used in
https://www.sqlite.org/isolation.html; But I'll remember about
REPEATABLE-READ isolation.

I also thank you very much for confirming that preventing checkpoints
allows the WAL markers to remain valid.

My goal is to provide a set of concurrency primitives that are useful for
GUI app developers. I'm learning a lot about SQLite in the process, of
course.

On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf  wrote:

>
> On Sunday, 29 September, 2019 01:28, Gwendal Roué 
> wrote:
>
> >But now I fail to understand the indented use case of sqlite3 snapshots..
> >Why allow to reuse snapshots with several calls to open()? Why do they
> >exist at all, since we can already profit from snapshot isolation with
> >one transaction (at the cost of keeping it open)?
>
> The sqlite3_snapshot_* interfaces allow you to record information about
> transactions maintained by the WAL transaction manager.  The interface DOES
> NOT manage the WAL transaction manager nor does it manage your so-called
> snapshots (which are not snapshots).  It allows you to "get" information
> about a particular point-in-time location in the WAL transaction log and
> "goto" a specific point-in-time in the WAL transaction log, to "free" the
> memory used to record this information, and to "cmp" which of two
> structures point to a later point-in-time in the WAL transaction log.
>
> They do NOT modify or control the WAL transaction system or the WAL file.
> You are responsible for doing this.
>
> https://sqlite.org/wal.html
>
> So, *IF* you want to maintain the availability of a specific point in time
> in the WAL transaction history, they YOU must make sure that YOU do not
> erase that particular point-in-time location by checkpointing the WAL
> transaction log.  You have the capability of doing this by disabling the
> autocheckpoint mechanism and controlling when you do checkpoints.  The WAL
> transaction system neither knows nor cares that you happen to have recorded
> some information about a particular point-in-time offset in the WAL file.
>
> It is sort of like "No Smoking" signs.  The sign does not mean that you
> cannot smoke.  It means that someone posted a sign that says "No Smoking".
> The fact that there is a sign and what it says is an entirely separate fact
> that has no bearing at all on whether or not one may smoke, nor does the
> sign somehow magically prevent smoking, nor will it prevent you from
> catching on fire and smoking as a result thereof.  It is merely a sign that
> has something written on it which someone stuck up on the wall (or whatever
> support structure it is stuck to).  And what is written on the sign is
> entirely independent of any other state of affairs.
>
> >For information, my quest for snapshot protection has met some success (
> >https://github.com/groue/GRDB.swift/pull/625)
>
> This appears to be updating a wrapper interface to SQLite3 so that if you
> happen to have a point-in-time location recorded, then the automatic WAL
> checkpoint system is disabled, plus doing some prevention to protect users
> of the wrapper from modifying (checkpointing) the WAL transaction log if
> they happen to have some recorded "points-in-time" that are dependent on
> not modifying the WAL transaction log.  (ie, things that you should be
> doing yourself so as to not shoot yourself in the foot).
>
> >Given I control the connections to a given database file, I am able to
> >open one writer connection, and, on demand, up to N read-only connections.
>
> >Those N reader connections allow concurrent database reads. Those "reads"
> are
> >generally wrapped in a deferred transaction which provides snapshot
> >isolation.
>
> No, it provides REPEATABLE-READ isolation.  There is no actual "snapshot"
> taken and no snapshot exists.  You are merely not seeing data written to
> the WAL transaction log at a point-in-time subsequent to the point in time
> at which you commenced the "repeatable-read".
>
> The sqlite3_snapshot_* interface merely provides a way to record
> information about this repeatable-read point-in-time position in the WAL
> file so that you can go back to that "repeatable-read" point-in-time
> location sometime in the future IF IT STILL EXISTS in the WAL transaction
> log.  Since YOU are in control of whether or not the WAL file is
> checkpointed, then YOU are in control of whether this point-in-time still
> exists in the WAL transaction log.  If you remove data from the 

Re: [sqlite] Conflict between snapshots and checkpoints

[Gwendal Roué]

Thank you, Richard

But now I fail to understand the indented use case of sqlite3 snapshots..
Why allow to reuse snapshots with several calls to open()? Why do they
exist at all, since we can already profit from snapshot isolation with one
transaction (at the cost of keeping it open)?

For information, my quest for snapshot protection has met some success (
https://github.com/groue/GRDB.swift/pull/625)

Given I control the connections to a given database file, I am able to open
one writer connection, and, on demand, up to N read-only connections. Those
N reader connections allow concurrent database reads. Those "reads" are
generally wrapped in a deferred transaction which provides snapshot
isolation. At the end of this transaction, the reader connection becomes
available for another read.

One can now create a "snapshot". Those snapshots use the same pool of N
readers: snapshot reads are then wrapped in a deferred transaction and
sqlite3_snapshot_open. At the end of this transaction, the reader
connection becomes available for another regular read or for another
snapshot read.

In order to protect unused snapshots, checkpoints are prevented as long as
there exists snapshots, with sqlite3_wal_hook().

I **really** hope this protects snapshots for good. Of course, a
confirmation from knowledgeable people would be appreciated :-)

Gwendal

On Sat, Sep 28, 2019 at 4:06 PM Richard Damon 
wrote:

> On 9/26/19 12:13 PM, Gwendal Roué wrote:
> > Hello,
> >
> > The documentation for sqlite3_snapshot_open() [1] says:
> >
> >> A call to sqlite3_snapshot_open() will fail to open if the specified
> > snapshot has been overwritten by a checkpoint.
> >
> > And indeed I am able to create a snapshot with sqlite3_snapshot_get()
> [2],
> > then run checkpoints with sqlite3_wal_checkpoint_v2() [3], and finally
> have
> > sqlite3_snapshot_open() fail.
> >
> > I have a concern, because this turns automatic checkpointing into a
> mortal
> > danger for all snapshots. As soon as a checkpoint is performed, snapshots
> > are invalidated, and whoever is relying on them has lost the database
> state
> > he's interested into.
> >
> > My question is: is it possible to prevent checkpoints from completing
> > successfully when a snapshot is alive?
> >
> > I know this is possible with a "hand-made checkpoint", made of a distinct
> > connection which has started a deferred transaction and has performed an
> > initial read. Such hand-made checkpoint has SQLITE_CHECKPOINT_RESTART and
> > SQLITE_CHECKPOINT_TRUNCATE fail, and are still able to access their
> > precious database state after a SQLITE_CHECKPOINT_PASSIVE or a
> > SQLITE_CHECKPOINT_FULL.
> >
> > The behavior of those "hand-made checkpoint" matches well the needs of
> > users who want to use WAL without thinking too much about it: they do not
> > disable automatic checkpointing, and are guaranteed with a stable access
> to
> > a given database state as long as they need it.
> >
> > I was wondering if such a behavior is possible with snapshots returned
> from
> > sqlite3_snapshot_get().
> >
> > If it is not, then I humbly suggest that this feature would be added, and
> > am willing to listen to the opinion of SQLite experts on this subject.
> >
> > Regards,
> > Gwendal Roué
> >
> > [1] https://www.sqlite.org/c3ref/snapshot_open.html
> > [2] https://www.sqlite.org/c3ref/snapshot_get.html
> > [3] https://www.sqlite.org/c3ref/wal_checkpoint_v2.html
>
> I think you have a fundamental issue here. Snapshots are tied to a
> Transaction, and as long as that transaction doesn't invalidate that
> snapshot, nothing else can. (This is what you hand-mand checkpoint does
> right). The issue is that if you are reusing the connection, you are
> sharing the Transaction and thus not protecting your snapshot. Once
> someone else using that connection breaks the transaction, then the
> snapshot is no longer protected.
>
> The issue trying to increase the protection on a snapshot is that
> currently sqlite_snapshot_free() isn't defined as protecting the
> snapshot, but is merely a memory management tool, so many applications
> likely don't free all their snapshots before they allow for the
> possibility of them being invalidated by ending the Transaction, thus
> this change would break many existing programs. I suppose it could be
> implemented with a pragma or the like that doesn't allow that
> transaction to be committed or invalidate the snapshot until the
> snapshot is freed.
>
> What that would ultimately do is cause your program to get an error from
> SQLite3 farther down the roa

Re: [sqlite] Conflict between snapshots and checkpoints

[Gwendal Roué]

Richard,

Further experiments with snapshots reveal a behavior I did not expect.

My high-level goal is to *protect snapshots*: if a user of my SQLite
wrapper GRDB.swift takes a snapshot, I want this snapshot to remain valid
(usable without error) for the whole duration of its lifetime. During its
lifetime, a snapshot will open transactions when it is used. But there are
moments when a snapshot exists (has not been freed by
sqlite3_snapshot_free()), but is not currently used by a transaction.

I thus have to carefully manage checkpoints, so that they do not invalidate
existing snapshots, even if they are not currently used in a transaction.

I did attempt at protecting existing snapshots by opening a transaction and
calling sqlite3_snapshot_open with the oldest open snapshot before
calling sqlite3_wal_checkpoint_v2().

But this does not prevent snapshots from invalidation:

1. Create a snapshot with sqlite3_snapshot_get()
2. In connection A, open a deferred transaction, open the snapshot
3. In connection B, run the checkpoint (this fails with SQLITE_BUSY when
checkpoint is RESTART OR TRUNCATE, as expected)
4. Close the transaction in connection A
5. In connection A, open a deferred transaction, open the snapshot, read,
commit: success
6. In connection B, perform a write, commit: success
7. In connection A, open a deferred transaction, open the snapshot:
SQLITE_ERROR (1), "not an error"

Is the error at step 7 expected?

On Fri, Sep 27, 2019 at 12:35 PM Richard Hipp  wrote:

> On 9/26/19, Gwendal Roué  wrote:
> >
> > My question is: is it possible to prevent checkpoints from completing
> > successfully when a snapshot is alive?
> >
>
> That depends on what you mean by "alive"?
>
> An sqlite3_snapshot_get() simply records some numbers in private
> memory of the database connection that called sqlite3_snapshot_get().
> There is no way for another process to know that those numbers have
> been recorded, and hence no way to know that the snapshot exists, and
> no way to prevent a checkpoint from happening.
>
> On the other hand, if you have run sqlite3_snapshot_open() so that
> there is a transaction open on the snapshot, that takes locks on the
> database which prevent checkpoints from running to completion and
> erasing the snapshot.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] The LIKE operator and Swift

[Gwendal Roué]

You can build your pattern in Swift, and use a single parameter:

let queryString = "select name, phone, street, city, state from phone
where name like '?'"
let pattern = "%\(name)%"
if sqlite3_bind_text(stmt, 1, pattern, -1, SQLITE_TRANSIENT) !=
SQLITE_OK {do whatever}

For a more detailed explanation of the reasons why you get an error with
this LIKE query see this dedicated FAQ:
https://github.com/groue/GRDB.swift/blob/master/README.md#sqlite-error-21-wrong-number-of-statement-arguments-with-like-queries

Gwendal Roué

On Thu, Sep 26, 2019 at 3:26 PM Daniel Odom  wrote:

> I am just now getting around to learning Swift and XCode. I am having a
> problem with 'LIKE'. When I do this:
>
> let queryString = "select name, phone, street, city, state from phone
> where name like '%?%'"
>
> And then this: if sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT)
> != SQLITE_OK {do whatever}
>
> I get an error "column index out of range". The rest of the code is
> fine. When I do this:
>
> let queryString = "select name, phone, street, city, state from phone
> where name = ?"
>
> everything works just fine. What am I missing?
>
> ___
> 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] Conflict between snapshots and checkpoints

[Gwendal Roué]

>> My question is: is it possible to prevent checkpoints from completing
>> successfully when a snapshot is alive?
>
> That depends on what you mean by "alive"?

Sorry, I meant "not finalized by sqlite3_snapshot_free".

Your precise answer lifts any doubt: only transactions can prevent
checkpoints from running to completion.

Thank you very much Richard!

Gwendal

On Fri, Sep 27, 2019 at 12:35 PM Richard Hipp  wrote:

> On 9/26/19, Gwendal Roué  wrote:
> >
> > My question is: is it possible to prevent checkpoints from completing
> > successfully when a snapshot is alive?
> >
>
> That depends on what you mean by "alive"?
>
> An sqlite3_snapshot_get() simply records some numbers in private
> memory of the database connection that called sqlite3_snapshot_get().
> There is no way for another process to know that those numbers have
> been recorded, and hence no way to know that the snapshot exists, and
> no way to prevent a checkpoint from happening.
>
> On the other hand, if you have run sqlite3_snapshot_open() so that
> there is a transaction open on the snapshot, that takes locks on the
> database which prevent checkpoints from running to completion and
> erasing the snapshot.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Conflict between snapshots and checkpoints

[Gwendal Roué]

> I have a concern, because this turns automatic checkpointing into a
mortal danger for all snapshots.

This sentence may be false. Automatic snapshots are PASSIVE, and I'm not
sure passive checkpoints invalidate snapshots returned by
sqlite3_snapshot_get().

But i'm not sure, and I would appreciate a confirmation!

Thanks is advance,
Gwendal Roué


On Thu, Sep 26, 2019 at 6:13 PM Gwendal Roué  wrote:

> Hello,
>
> The documentation for sqlite3_snapshot_open() [1] says:
>
> > A call to sqlite3_snapshot_open() will fail to open if the specified
> snapshot has been overwritten by a checkpoint.
>
> And indeed I am able to create a snapshot with sqlite3_snapshot_get() [2],
> then run checkpoints with sqlite3_wal_checkpoint_v2() [3], and finally have
> sqlite3_snapshot_open() fail.
>
> I have a concern, because this turns automatic checkpointing into a mortal
> danger for all snapshots. As soon as a checkpoint is performed, snapshots
> are invalidated, and whoever is relying on them has lost the database state
> he's interested into.
>
> My question is: is it possible to prevent checkpoints from completing
> successfully when a snapshot is alive?
>
> I know this is possible with a "hand-made checkpoint", made of a distinct
> connection which has started a deferred transaction and has performed an
> initial read. Such hand-made checkpoint has SQLITE_CHECKPOINT_RESTART and
> SQLITE_CHECKPOINT_TRUNCATE fail, and are still able to access their
> precious database state after a SQLITE_CHECKPOINT_PASSIVE or a
> SQLITE_CHECKPOINT_FULL.
>
> The behavior of those "hand-made checkpoint" matches well the needs of
> users who want to use WAL without thinking too much about it: they do not
> disable automatic checkpointing, and are guaranteed with a stable access to
> a given database state as long as they need it.
>
> I was wondering if such a behavior is possible with snapshots returned
> from sqlite3_snapshot_get().
>
> If it is not, then I humbly suggest that this feature would be added, and
> am willing to listen to the opinion of SQLite experts on this subject.
>
> Regards,
> Gwendal Roué
>
> [1] https://www.sqlite.org/c3ref/snapshot_open.html
> [2] https://www.sqlite.org/c3ref/snapshot_get.html
> [3] https://www.sqlite.org/c3ref/wal_checkpoint_v2.html
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Conflict between snapshots and checkpoints

[Gwendal Roué]

Hello,

The documentation for sqlite3_snapshot_open() [1] says:

> A call to sqlite3_snapshot_open() will fail to open if the specified
snapshot has been overwritten by a checkpoint.

And indeed I am able to create a snapshot with sqlite3_snapshot_get() [2],
then run checkpoints with sqlite3_wal_checkpoint_v2() [3], and finally have
sqlite3_snapshot_open() fail.

I have a concern, because this turns automatic checkpointing into a mortal
danger for all snapshots. As soon as a checkpoint is performed, snapshots
are invalidated, and whoever is relying on them has lost the database state
he's interested into.

My question is: is it possible to prevent checkpoints from completing
successfully when a snapshot is alive?

I know this is possible with a "hand-made checkpoint", made of a distinct
connection which has started a deferred transaction and has performed an
initial read. Such hand-made checkpoint has SQLITE_CHECKPOINT_RESTART and
SQLITE_CHECKPOINT_TRUNCATE fail, and are still able to access their
precious database state after a SQLITE_CHECKPOINT_PASSIVE or a
SQLITE_CHECKPOINT_FULL.

The behavior of those "hand-made checkpoint" matches well the needs of
users who want to use WAL without thinking too much about it: they do not
disable automatic checkpointing, and are guaranteed with a stable access to
a given database state as long as they need it.

I was wondering if such a behavior is possible with snapshots returned from
sqlite3_snapshot_get().

If it is not, then I humbly suggest that this feature would be added, and
am willing to listen to the opinion of SQLite experts on this subject.

Regards,
Gwendal Roué

[1] https://www.sqlite.org/c3ref/snapshot_open.html
[2] https://www.sqlite.org/c3ref/snapshot_get.html
[3] https://www.sqlite.org/c3ref/wal_checkpoint_v2.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Undo sqlite3_snapshot_open?

[Gwendal Roué]

Woohoo, thanks Dan! I'm going to try this very soon :-)

Gwendal

On Thu, Sep 19, 2019 at 1:18 PM Dan Kennedy  wrote:

>
> On 19/9/62 18:13, Gwendal Roué wrote:
> > Hello,
> >
> > I am looking at the snapshot experimental APIs, and it looks like once a
> > connection has been sent to an "historical snapshot" with
> > sqlite3_snapshot_open (https://www.sqlite.org/c3ref/snapshot_open.html),
> > the connection can never be restored back to regular operations.
> >
> > Is it correct?
>
> I don't think so.
>
> If you end the transaction opened with sqlite3_snapshot_open() (by
> executing a "COMMIT" or "ROLLBACK" and calling sqlite3_reset() or
> sqlite3_finalize() on all active SELECT statements) then open a new
> transaction, the new transaction accesses the latest database snapshot -
> just as if you had never used sqlite3_snapshot_open() with the
> connection at all.
>
> Dan.
>
>
>
> >
> > Thanks is advance,
> > Gwendal Roué
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Undo sqlite3_snapshot_open?

[Gwendal Roué]

Hello,

I am looking at the snapshot experimental APIs, and it looks like once a
connection has been sent to an "historical snapshot" with
sqlite3_snapshot_open (https://www.sqlite.org/c3ref/snapshot_open.html),
the connection can never be restored back to regular operations.

Is it correct?

Thanks is advance,
Gwendal Roué
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

[Gwendal Roué]

I totally agree with your answer. But this wasn't really the question.

> You have hacked around this security feature

I beg you to try to look at my "hacks" with a fresh eye.

The service they provide is a genuine one: be able to run raw SQL requests,
and also to be notified when one has committed changes in the results of
another. I suppose you know that most high-level libraries in GUI platforms
embed such database observation features. This is part of the expected tool
belt these days.

It happens that a security feature has been rerouted for another purpose.
This other purpose sheds a new light on authorizers.

In GRDB, statements are always "authorized": applications want to manage
*their* database, so there is no point restricting access to the database.
There is no need for the security side of SQLite authorizer. There is need
for the statement inspection features provided by SQLite authorizers (what
will be read/written). And prevention of the truncate optimization.

Now that I hope I have better explained where I talk from, I hope you will
read again my previous question.

Thanks in advance,
Gwendal Roué
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

[Gwendal Roué]

For the record, I have successfully refactored my code so that authorizer
is set only once, and the issue has disappeared.

Yet, this authorizer not always returns the same value for the same input.

Precisely speaking, it always return SQLITE_OK, but when asked if it should
allow deletion.

In this case, it may return SQLITE_IGNORE or SQLITE_OK during compilation
(prepare), and SQLITE_IGNORE or SQLITE_OK during execution (step). All four
combinations happen, depending on whether this is a DELETE statement with
truncate optimization enabled or disabled, or a DROP statement.

Do you think this can still be seen as a misuse of the library?

Some insights would be appreciated.

Thanks in advance

On Thursday, August 1, 2019, Richard Hipp  wrote:

> On 8/1/19, Gwendal Roué  wrote:
> >
> > 1. set authorizer
> > 2. compile statement
> > 3. reset authorizer
> > 4. step
> > 5. set authorizer (and do something else)
> > 6. step -> SQLITE_ABORT_ROLLBACK
>
> Please test to see if changing the "0" to a "1" on the line of code
> shown below fixes the problem, and report back.
>
> https://www.sqlite.org/src/artifact/0fac710388?ln=81
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

[Gwendal Roué]

After I have read the documentation of sqlite3ExpirePreparedStatements, I
better see how the GRDB authorizers dance is a misuse of the library.

The goal of GRDB authorizers, which is only to grab information about the
statements before they are executed, is at odds with the fact that SQLite
connections have a single authorizer dedicated to restricting database
accesses. I understand why a change of authorizer invalidates statements.

Conclusion: I should use a single authorizer and never change it.

This issue report remains interesting, because SQLite exhibits inconsistent
behaviors depending of the invalidated statements. But the real fix for
"my" issue is to refactor my use of authorizers.

On Thu, Aug 1, 2019 at 10:47 PM Gwendal Roué  wrote:

> For the context, GRDB uses authorizers as a support for its database
> observation features:
>
> - during the compilation of a read statements in order to know what part
> of the database would be accessed by the statement.
> - during the compilation of other statements in order to know what part of
> the database would be modified, or which transaction/savepoint operation
> would be executed, or if the database schema would change.
> - during the execution of statements for the sole purpose of preventing
> the truncate optimization when the library user has expressed the desire of
> being notified of row deletions.
>
> Joined together, all those pieces of observation allow the library user to
> say that it wants to track a "database region" (sets of tables, columns,
> and rowids), and be notified of any transaction that has committed changes
> to this region. This is insanely useful. With full support for raw SQL.
>
> I'm not sure this is what authorizers were designed for, but... I can't
> live without them now :-)
>
> On Thu, Aug 1, 2019 at 10:26 PM Gwendal Roué 
> wrote:
>
>> Yes, Richard, this fixes the problem! Tested with my local copy of SQLite
>> 3.28.0.
>>
>> On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp  wrote:
>>
>>> On 8/1/19, Gwendal Roué  wrote:
>>> >
>>> > 1. set authorizer
>>> > 2. compile statement
>>> > 3. reset authorizer
>>> > 4. step
>>> > 5. set authorizer (and do something else)
>>> > 6. step -> SQLITE_ABORT_ROLLBACK
>>>
>>> Please test to see if changing the "0" to a "1" on the line of code
>>> shown below fixes the problem, and report back.
>>>
>>> https://www.sqlite.org/src/artifact/0fac710388?ln=81
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> 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] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

[Gwendal Roué]

For the context, GRDB uses authorizers as a support for its database
observation features:

- during the compilation of a read statements in order to know what part of
the database would be accessed by the statement.
- during the compilation of other statements in order to know what part of
the database would be modified, or which transaction/savepoint operation
would be executed, or if the database schema would change.
- during the execution of statements for the sole purpose of preventing the
truncate optimization when the library user has expressed the desire of
being notified of row deletions.

Joined together, all those pieces of observation allow the library user to
say that it wants to track a "database region" (sets of tables, columns,
and rowids), and be notified of any transaction that has committed changes
to this region. This is insanely useful. With full support for raw SQL.

I'm not sure this is what authorizers were designed for, but... I can't
live without them now :-)

On Thu, Aug 1, 2019 at 10:26 PM Gwendal Roué  wrote:

> Yes, Richard, this fixes the problem! Tested with my local copy of SQLite
> 3.28.0.
>
> On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp  wrote:
>
>> On 8/1/19, Gwendal Roué  wrote:
>> >
>> > 1. set authorizer
>> > 2. compile statement
>> > 3. reset authorizer
>> > 4. step
>> > 5. set authorizer (and do something else)
>> > 6. step -> SQLITE_ABORT_ROLLBACK
>>
>> Please test to see if changing the "0" to a "1" on the line of code
>> shown below fixes the problem, and report back.
>>
>> https://www.sqlite.org/src/artifact/0fac710388?ln=81
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> 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] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

[Gwendal Roué]

Yes, Richard, this fixes the problem! Tested with my local copy of SQLite
3.28.0.

On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp  wrote:

> On 8/1/19, Gwendal Roué  wrote:
> >
> > 1. set authorizer
> > 2. compile statement
> > 3. reset authorizer
> > 4. step
> > 5. set authorizer (and do something else)
> > 6. step -> SQLITE_ABORT_ROLLBACK
>
> Please test to see if changing the "0" to a "1" on the line of code
> shown below fixes the problem, and report back.
>
> https://www.sqlite.org/src/artifact/0fac710388?ln=81
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

[Gwendal Roué]

Hello,

This is an issue report.

SQLite from version 3.8.8 to version 3.24.0 exhibits an issue which
prevents the use of sqlite3_set_authorizer during the iteration of a
statement. The issue does not happen with all statements, but only with
some of them.

It basically goes this way:

1. set authorizer
2. compile statement
3. reset authorizer
4. step
5. set authorizer (and do something else)
6. step -> SQLITE_ABORT_ROLLBACK

Please find below a reproducible test case, reduced as much as I could. It
outputs `code = 516`, when it should not.

A piece of information that may help narrowing the trouble: I could only
trigger the error with the provided query, that involve two tables.

Finally, I post this message after investigation for an issue in the GRDB
Swift library: https://github.com/groue/GRDB.swift/issues/583

Thanks for reading,
Gwendal Roué


#include 
#include 

int authorize(void* a,int b,const char* c,const char* d,const char* e,const
char* f) {
return SQLITE_OK;
}

int main() {
sqlite3 *connection;
sqlite3_open_v2(":memory:", , SQLITE_OPEN_READWRITE |
SQLITE_OPEN_NOMUTEX, 0);
sqlite3_extended_result_codes(connection, 1);
sqlite3_exec(connection, "CREATE TABLE user (username TEXT NOT NULL)",
0, 0, 0);
sqlite3_exec(connection, "CREATE TABLE flagUser (username TEXT NOT
NULL)", 0, 0, 0);
sqlite3_exec(connection, "INSERT INTO flagUser (username) VALUES
('User1')", 0, 0, 0);
sqlite3_exec(connection, "INSERT INTO flagUser (username) VALUES
('User2')", 0, 0, 0);

sqlite3_stmt *statement;
sqlite3_set_authorizer(connection, authorize, 0);
sqlite3_prepare_v3(connection, "SELECT * FROM flagUser WHERE (SELECT
COUNT(*) FROM user WHERE username = flagUser.username) = 0", -1, 0,
, 0);
sqlite3_set_authorizer(connection, 0, 0);

int code = sqlite3_step(statement);
printf("code = %i\n", code);
sqlite3_set_authorizer(connection, 0, 0);
code = sqlite3_step(statement);
printf("code = %i\n", code);
sqlite3_finalize(statement);
sqlite3_close_v2(connection);
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Change the update hook from the update hook?

[Gwendal Roué]

For the record, removing the update hook from the update hook works as expected 
in both SQLite 3.19.3 and 3.21, with a connection which is opened with the 
SQLITE_OPEN_NOMUTEX flag, and the guarantee that no two threads use the same 
connection at the same time.

But I'm just not sure if this is a guaranteed behavior?

Gwendal

> Le 14 janv. 2018 à 18:05, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
> 
> Hello,
> 
> Is it valid to change the update hook from the update hook itself?
> 
> The reason for this question is the following: when a notified database 
> change makes it useless to perform further observation, one may want to 
> remove the update hook alltogether. Can it be done from the update hook 
> itself?
> 
> Here is a quick and dirty sample code, for the context: it installs an update 
> hook that tracks any modification to the "players" table, and attempts to 
> remove the update hook as soon as the "players" table is modified:
> 
> typedef struct {
> sqlite3 *conn;
> int players_table_was_modified;
> } info;
> 
> void update_hook(info *info, int change, char const *db, char const 
> *table, sqlite3_int64 rowed) {
> if (strcmp(table, "players") == 0) {
> info->players_table_was_modified = 1;
> 
> // Is it valid?
> sqlite3_update_hook(info->conn, NULL, NULL);
> }
> }
> 
> sqlite3 *conn = ...;
> info info = { conn, 0 };
> sqlite3_update_hook(conn, update_hook, );
> 
> Quoting https://sqlite.org/c3ref/update_hook.html: 
> <https://sqlite.org/c3ref/update_hook.html:>
> 
>> The update hook implementation must not do anything that will modify the 
>> database connection that invoked the update hook. Any actions to modify the 
>> database connection must be deferred until after the completion of the 
>> sqlite3_step() call that triggered the update hook. Note that 
>> sqlite3_prepare_v2() and sqlite3_step() both modify their database 
>> connections for the meaning of "modify" in this paragraph.
> 
> According to this documentation, I'm note sure if sqlite3_update_hook itself 
> modifies the database connection for the meaning of "modify" in the quoted 
> documentation paragraph, and is thus forbidden, or not.
> 
> Can anyone lift this doubt?
> 
> Thanks in advance,
> Gwendal Roué
> 

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

[sqlite] Change the update hook from the update hook?

[Gwendal Roué]

Hello,

Is it valid to change the update hook from the update hook itself?

The reason for this question is the following: when a notified database change 
makes it useless to perform further observation, one may want to remove the 
update hook alltogether. Can it be done from the update hook itself?

Here is a quick and dirty sample code, for the context: it installs an update 
hook that tracks any modification to the "players" table, and attempts to 
remove the update hook as soon as the "players" table is modified:

typedef struct {
sqlite3 *conn;
int players_table_was_modified;
} info;

void update_hook(info *info, int change, char const *db, char const *table, 
sqlite3_int64 rowed) {
if (strcmp(table, "players") == 0) {
info->players_table_was_modified = 1;

// Is it valid?
sqlite3_update_hook(info->conn, NULL, NULL);
}
}

sqlite3 *conn = ...;
info info = { conn, 0 };
sqlite3_update_hook(conn, update_hook, );

Quoting https://sqlite.org/c3ref/update_hook.html:

> The update hook implementation must not do anything that will modify the 
> database connection that invoked the update hook. Any actions to modify the 
> database connection must be deferred until after the completion of the 
> sqlite3_step() call that triggered the update hook. Note that 
> sqlite3_prepare_v2() and sqlite3_step() both modify their database 
> connections for the meaning of "modify" in this paragraph.

According to this documentation, I'm note sure if sqlite3_update_hook itself 
modifies the database connection for the meaning of "modify" in the quoted 
documentation paragraph, and is thus forbidden, or not.

Can anyone lift this doubt?

Thanks in advance,
Gwendal Roué

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

Re: [sqlite] SQLite 3.22.0 coming soon

[Gwendal Roué]

No more glitch with sqlite3_column_name :-)

Thanks Richard,
Gwendal

> Le 10 janv. 2018 à 02:06, Richard Hipp  a écrit :
> 
> All of the minor issues mentioned by prior emails in this thread
> should now be fixed.  Thanks to everybody for proof-reading and
> testing!
> 
> Fresh source code is now available on trunk
> (https://sqlite.org/src/info/trunk) and from the "Prerelease Snapshot"
> link on the https://sqlite.org/download.html page if you would like to
> continue testing.
> 
> We are not yet at "pencils down".  More enhancements may yet go into
> this release before we start our final release testing.  Nevertheless,
> the community testing you have conducted so far has found several real
> issues, and is greatly appreciated.  Please continue the good work!
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQLite 3.22.0 coming soon

[Gwendal Roué]

> Le 9 janv. 2018 à 21:47, Richard Hipp <d...@sqlite.org> a écrit :
> 
> On 1/9/18, Gwendal Roué <gwendal.r...@gmail.com> wrote:
>> 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a,
>> :b`, the sqlite3_column_name used to return the parameter name ("?" or ":a"
>> etc.) for all parameters, the last one included. The latest version returns
>> an empty string for the last parameter.
> 
> I have not been able to reproduce this problem using the command-line
> shell.  I tried the following script:
> 
>  .header on
>  SELECT ?, ?;
>  SELECT :a, :b;
> 
> It shows me the correct column names for all columns.  Do you have a
> reproducible test case that I can work from?

I have Swift code which reliably reproduces the bug :

var conn: OpaquePointer? = nil
sqlite3_open_v2("", , SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, nil)
var stmt: OpaquePointer? = nil
sqlite3_prepare_v3(conn, "SELECT :a, :b", -1, 0, , nil)
sqlite3_column_name(stmt, 0) // ":a"
sqlite3_column_name(stmt, 1) // ""

A manual translation into C gives:

void *conn, *stmt;
sqlite3_open_v2("", , SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, 
NULL);
sqlite3_prepare_v3(conn, "SELECT :a, :b", -1, 0, , NULL);
sqlite3_column_name(stmt, 0); // ":a"
sqlite3_column_name(stmt, 1); // ""

If this is a side effect of the Swift C, we have found another interesting bug, 
but not in SQLite :-)

Gwendal

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

Re: [sqlite] SQLite 3.22.0 coming soon

[Gwendal Roué]

Hello,

I could spot two behavior changes in f6355970 while testing GRDB.swift.

1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a, :b`, 
the sqlite3_column_name used to return the parameter name ("?" or ":a" etc.) 
for all parameters, the last one included. The latest version returns an empty 
string for the last parameter.

2. '^foo' used to be an invalid FTS5 pattern. It is valid in the latest version 
(but I did not check its behavior).

Cheers,
Gwendal Roué

> Le 9 janv. 2018 à 19:46, Richard Hipp <d...@sqlite.org> a écrit :
> 
> Version 3.22.0 will probably be released soon.  Your help in
> beta-testing is appreciated.  Please download the latest "trunk"
> sources (from https://sqlite.org/src/info/trunk) or a pre-release
> snapshot (the top-most link at https://sqlite.org/download.html) and
> verify that you are able to build and use the latest code in your
> applications.  Report any issues to this mailing list, or directly to
> me.
> 
> The latest change summary can be seen at
> https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft
> 3.22.0 webpage is at https://www.sqlite.org/draft
> 
> Detailed log of all check-ins since the last release:
> https://www.sqlite.org/src/timeline?from=release=trunk
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] advice about schema versioning and WAL

[Gwendal Roué]

Thanks Dan for this information!

(For some reason, your reply appears on the mailing list archive, but never 
found the way of my inbox. I'm replying to my own message)

Gwendal

> Dan Kennedy wrote:
> 
> "PRAGMA schema_version" should work the same way in WAL mode. The pragma 
> will read the "database header" from the newest version of page 1 in the 
> wal file if required.
> 
> Dan.
> 
>> Le 28 déc. 2017 à 19:28, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
>> 
>> Hello,
>> 
>> Season's greetings to all SQLite fellows!
>> 
>> 
>> I'm developping a library that would like to keep a "cache" of some 
>> information about the database schema. Such information are the columns of a 
>> table, its primary key, or its indexes. The purpose of this cache is not 
>> really speed, even if it may help, but mainly to avoid cluttering the 
>> sqlite3_trace/sqlite3_trace_v2 hooks with noisy pragmas whenever the library 
>> needs to infer some implicit information from the actual database schema.
>> 
>> This cache has to be invalidated whenever the schema changes. The 
>> Compile-Time Authorization Callback [1] is the perfect tool for the job, 
>> since it allows to identify statements that create, drop, alter tables and 
>> indexes.
>> 
>> Everything is fine and easy when a single connection is used in a 
>> single-threaded way: statements are executed one after the other, and the 
>> management of the schema cache is trivial.
>> 
>> 
>> It's much less trivial with the WAL mode. I focus on a setup which uses a 
>> unique writer connection, and several reader connections. All connections 
>> are used sequentially in their own thread, but readers and writer can run 
>> concurrently in order to take advantage from the WAL mode.
>> 
>> When a read-only connection uses a deferred transaction to enter snapshot 
>> isolation, it doesn't see the changes performed by other transactions. For 
>> example, if a reader acquires snapshot isolation before a table is altered 
>> by the writer, it won't see the alteration until it commits its deferred 
>> transaction. I wish my schema cache would behave the same.
>> 
>> To be precise, I only have two important needs:
>> 
>> 1. A connection's schema cache is correct, which means that it never 
>> contains information that does not match SQLite's genuine view of the 
>> database schema. Being invalidated/empty is correct, if not efficient (the 
>> missing information is then loaded from SQLite).
>> 2. Synchronization points between readers and writers are avoided 
>> (non-blocking access is the whole point of WAL, and I want to avoid locks as 
>> much as possible)
>> 
>> I was hoping that a connection would have a "schema version": an 
>> automatically incremented value that SQLite bumps whenever the schema is 
>> changed. That would have been enough for my use case. Unfortunately, PRAGMA 
>> schema_version reads the database header, and I thus guess that it does not 
>> play well with WAL (I'm not sure). Furthermore, PRAGMA schema_version 
>> clutters the tracing hook.
>> 
>> The most simple solution I have is to invalidate a reader's schema cache 
>> each time it is used. This would unfortunately invalidate the readers' 
>> caches too often, since most real-life uses only alter the schema at 
>> application start-up, which means that the schema is, practically speaking, 
>> stable after this initialisation phase.
>> 
>> Do any of you have any better idea?
>> 
>> Thanks in advance, regards,
>> Gwendal Roué
>> 
>> [1] https://sqlite.org/c3ref/set_authorizer.html
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] advice about schema versioning and WAL

[Gwendal Roué]

Hello,

Season's greetings to all SQLite fellows!


I'm developping a library that would like to keep a "cache" of some information 
about the database schema. Such information are the columns of a table, its 
primary key, or its indexes. The purpose of this cache is not really speed, 
even if it may help, but mainly to avoid cluttering the 
sqlite3_trace/sqlite3_trace_v2 hooks with noisy pragmas whenever the library 
needs to infer some implicit information from the actual database schema.

This cache has to be invalidated whenever the schema changes. The Compile-Time 
Authorization Callback [1] is the perfect tool for the job, since it allows to 
identify statements that create, drop, alter tables and indexes.

Everything is fine and easy when a single connection is used in a 
single-threaded way: statements are executed one after the other, and the 
management of the schema cache is trivial.


It's much less trivial with the WAL mode. I focus on a setup which uses a 
unique writer connection, and several reader connections. All connections are 
used sequentially in their own thread, but readers and writer can run 
concurrently in order to take advantage from the WAL mode.

When a read-only connection uses a deferred transaction to enter snapshot 
isolation, it doesn't see the changes performed by other transactions. For 
example, if a reader acquires snapshot isolation before a table is altered by 
the writer, it won't see the alteration until it commits its deferred 
transaction. I wish my schema cache would behave the same.

To be precise, I only have two important needs:

1. A connection's schema cache is correct, which means that it never contains 
information that does not match SQLite's genuine view of the database schema. 
Being invalidated/empty is correct, if not efficient (the missing information 
is then loaded from SQLite).
2. Synchronization points between readers and writers are avoided (non-blocking 
access is the whole point of WAL, and I want to avoid locks as much as possible)

I was hoping that a connection would have a "schema version": an automatically 
incremented value that SQLite bumps whenever the schema is changed. That would 
have been enough for my use case. Unfortunately, PRAGMA schema_version reads 
the database header, and I thus guess that it does not play well with WAL (I'm 
not sure). Furthermore, PRAGMA schema_version clutters the tracing hook.

The most simple solution I have is to invalidate a reader's schema cache each 
time it is used. This would unfortunately invalidate the readers' caches too 
often, since most real-life uses only alter the schema at application start-up, 
which means that the schema is, practically speaking, stable after this 
initialisation phase.

Do any of you have any better idea?

Thanks in advance, regards,
Gwendal Roué

[1] https://sqlite.org/c3ref/set_authorizer.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the most flexible way to exact the table name from a SQL statement

[Gwendal Roué]

Apologies: I have to amend again my suggestion. The authorizer has to be 
attached to a "real" database that already has a definition for the involved 
tables, if you need to know about insertions, deletions, and updates.

Gwendal

> Le 29 oct. 2017 à 15:37, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
> 
> I should have added that you can check for inserted/deleted/updated tables by 
> looking for more codes than SQLITE_CREATE_TABLE. The provided sample code 
> only checks for table creation.
> 
> Gwendal
> 
>> Le 29 oct. 2017 à 15:28, Gwendal Roué <gwendal.r...@gmail.com 
>> <mailto:gwendal.r...@gmail.com>> a écrit :
>> 
>> Yes, there is a general way.
>> 
>> To know if a statement creates a database table, 
>> 
>> 1. Open a private, in-memory, database connection
>> 2. Register an authorizer with sqlite3_set_authorizer 
>> (https://sqlite.org/c3ref/set_authorizer.html 
>> <https://sqlite.org/c3ref/set_authorizer.html>).
>> 3. Compile the statement with sqlite3_prepare_v2 
>> (https://sqlite.org/c3ref/prepare.html 
>> <https://sqlite.org/c3ref/prepare.html>)
>> 4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE 
>> (https://www.sqlite.org/c3ref/c_alter_table.html 
>> <https://www.sqlite.org/c3ref/c_alter_table.html>).
>> 
>> Find attached a C program that demonstrates the technique.
>> 
>> $ cc -lsqlite3 created_table.c && ./a.out 
>> Create table foo: CREATE TABLE foo(a, b)
>> No table creation: INSERT INTO bar (a) VALUES (1)
>> No table creation: Some invalid SQL
>> 
>> 
>> 
>> Gwendal Roué
>> 
>>> Le 28 oct. 2017 à 14:44, Shane Dev <devshan...@gmail.com 
>>> <mailto:devshan...@gmail.com>> a écrit :
>>> 
>>> Hello,
>>> 
>>> Let's say I have a table containing of SQL statements, for example
>>> 
>>> sqlite> .schema sql
>>> CREATE TABLE sql(statement text);
>>> 
>>> sqlite> select * from sql;
>>> insert into tab1 select 'example text';
>>> update tab2 set col2 = 123 where col2 = 1;
>>> delete from tab3 where col1 = 2;
>>> 
>>> For the first row, I could build a query using instr and substr functions
>>> to extract the first word after INSERT INTO. That would work for most
>>> simple INSERT statements, but it would fail if (for example) the statement
>>> was prepended with a WITH clause which happened to contain the text "INSERT
>>> INTO". Is there more generalized way of achieving this?
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org 
>>> <mailto:sqlite-users@mailinglists.sqlite.org>
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
>>> <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] What is the most flexible way to exact the table name from a SQL statement

[Gwendal Roué]

I should have added that you can check for inserted/deleted/updated tables by 
looking for more codes than SQLITE_CREATE_TABLE. The provided sample code only 
checks for table creation.

Gwendal

> Le 29 oct. 2017 à 15:28, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
> 
> Yes, there is a general way.
> 
> To know if a statement creates a database table, 
> 
> 1. Open a private, in-memory, database connection
> 2. Register an authorizer with sqlite3_set_authorizer 
> (https://sqlite.org/c3ref/set_authorizer.html 
> <https://sqlite.org/c3ref/set_authorizer.html>).
> 3. Compile the statement with sqlite3_prepare_v2 
> (https://sqlite.org/c3ref/prepare.html 
> <https://sqlite.org/c3ref/prepare.html>)
> 4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE 
> (https://www.sqlite.org/c3ref/c_alter_table.html 
> <https://www.sqlite.org/c3ref/c_alter_table.html>).
> 
> Find attached a C program that demonstrates the technique.
> 
> $ cc -lsqlite3 created_table.c && ./a.out 
> Create table foo: CREATE TABLE foo(a, b)
> No table creation: INSERT INTO bar (a) VALUES (1)
> No table creation: Some invalid SQL
> 
> 
> 
> Gwendal Roué
> 
>> Le 28 oct. 2017 à 14:44, Shane Dev <devshan...@gmail.com 
>> <mailto:devshan...@gmail.com>> a écrit :
>> 
>> Hello,
>> 
>> Let's say I have a table containing of SQL statements, for example
>> 
>> sqlite> .schema sql
>> CREATE TABLE sql(statement text);
>> 
>> sqlite> select * from sql;
>> insert into tab1 select 'example text';
>> update tab2 set col2 = 123 where col2 = 1;
>> delete from tab3 where col1 = 2;
>> 
>> For the first row, I could build a query using instr and substr functions
>> to extract the first word after INSERT INTO. That would work for most
>> simple INSERT statements, but it would fail if (for example) the statement
>> was prepended with a WITH clause which happened to contain the text "INSERT
>> INTO". Is there more generalized way of achieving this?
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org 
>> <mailto: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] What is the most flexible way to exact the table name from a SQL statement

[Gwendal Roué]

Yes, there is a general way.

To know if a statement creates a database table, 

1. Open a private, in-memory, database connection
2. Register an authorizer with sqlite3_set_authorizer 
(https://sqlite.org/c3ref/set_authorizer.html 
<https://sqlite.org/c3ref/set_authorizer.html>).
3. Compile the statement with sqlite3_prepare_v2 
(https://sqlite.org/c3ref/prepare.html <https://sqlite.org/c3ref/prepare.html>)
4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE 
(https://www.sqlite.org/c3ref/c_alter_table.html 
<https://www.sqlite.org/c3ref/c_alter_table.html>).

Find attached a C program that demonstrates the technique.

$ cc -lsqlite3 created_table.c && ./a.out 
Create table foo: CREATE TABLE foo(a, b)
No table creation: INSERT INTO bar (a) VALUES (1)
No table creation: Some invalid SQL



Gwendal Roué

> Le 28 oct. 2017 à 14:44, Shane Dev <devshan...@gmail.com> a écrit :
> 
> Hello,
> 
> Let's say I have a table containing of SQL statements, for example
> 
> sqlite> .schema sql
> CREATE TABLE sql(statement text);
> 
> sqlite> select * from sql;
> insert into tab1 select 'example text';
> update tab2 set col2 = 123 where col2 = 1;
> delete from tab3 where col1 = 2;
> 
> For the first row, I could build a query using instr and substr functions
> to extract the first word after INSERT INTO. That would work for most
> simple INSERT statements, but it would fail if (for example) the statement
> was prepended with a WITH clause which happened to contain the text "INSERT
> INTO". Is there more generalized way of achieving this?
> ___
> 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] Binding an order by

[Gwendal Roué]

> Le 5 oct. 2017 à 20:45, Stephen Chrzanowski  a écrit :
> 
> Given the query:
> 
> select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents where
> Resolved=:Resolved order by :OrderBy
> 
> I wanted to bind :OrderBy with field names and conditions based on user
> preferences, but I think the bind converted my order rules into a string
> and ordered my results based on that string somehow.
> 
> Am I looking at a wrapper limitation or is this something SQL won't do and
> I'll have to rely on concatenation?  (Which I'm not bothered by since the
> sort options are hard coded)

Hello Stephen,

SQLite query arguments are for values only: they can't be used as placeholders 
for expressions such as a column name. Reference: 
https://sqlite.org/c3ref/bind_blob.html: 


You're thus not facing a wrapper limitation: you do have to concatenate strings 
in order to build your dynamic SQL query.

Gwendal

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

Re: [sqlite] Is the file I'm about to open an SQLite Database

[Gwendal Roué]

> Le 4 sept. 2017 à 16:46, Phoenix <rwm.phoe...@btinternet.com> a écrit :
> 
> Is there anyway to confirm the file I am about to open, via
> sqlite3_open, is in fact an SQLite Database?

You may have noticed that sqlite3_open doesn't fail if the file is not a 
database.

To check if the file is a valid database, read something. For example: "SELECT 
* FROM sqlite_master LIMIT 1" (I'm sure there are shorter/smarter test access, 
but this one does the job). SQLite will then fail unless the file is actually a 
database.

Gwendal Roué

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

[sqlite] [Announcement] Mix and match Objective-C and Swift for SQLite access

[Gwendal Roué]

Hello SQLite community,

I want to announce the first release of GRDBObjc, a library that allows iOS, 
macOS, and watchOS apps to use SQLite from both Objective-C and Swift.

Both languages share the same database connections, with all the 
multi-threading safety expected by reasonable developers. Objective-C code 
accesses the database through an API that is compatible with FMDB by Gus 
Mueller, the de-facto standard for accessing SQLite from Objective-C. Swift 
code, on the other hand, accesses the database through GRDB, an SQLite wrapper 
that has been designed for Swift.

I hope this library will help developers that maintain Objective-C applications 
enjoy more Swift, at minimal cost.

Links:
- GRDBObjc: https://github.com/groue/GRDBObjc
- GRDB: https://github.com/groue/GRDB.swift

Thanks for reading,
Gwendal Roué

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

Re: [sqlite] Question of Table/Indices common to multiple Databases

[Gwendal Roué]

Hello John,

ATTACH DATABASE may well be the statement that you need: 
https://www.sqlite.org/lang_attach.html 
<https://www.sqlite.org/lang_attach.html>

It lets you use several sqlite files from a single database connection, and 
execute queries across all tables of all attached files.

Gwendal Roué

> Le 31 août 2017 à 19:44, John R. Sowden <jsow...@americansentry.net> a écrit :
> 
> I have been using the xbase language (dbase, foxpro, etc.) for about 36 
> years, writing applicatios for my alarm company, so each database is a 
> separate file for me.  For the last 21 years, I have been using Linux, and 
> have found that sqlite is my best match for Linux database use.
> 
> What I fail to understand is how I set up my files/databases.  I have 
> categories that I write for: accounting, dispatching, service, billing, etc.  
> Some (most) of these use customer data, so when I am writing code for the 
> billing program, and I want to reference the customers, is that a separate 
> file, so I only have 1 customer file to update (the relational model)?  
> Having a customer table, with indices,  in each category's database (file) 
> breaks the relational model.  I have been on this list for about a year and 
> see no reference to this issue.  I am reading now about sqlite in _The 
> Definitive Guide to SQLite_ by Michael Owens, but I'm early in the book.
> 
> Help?
> 
> John
> 
> ___
> 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] Is it safe to use same sqlite connection sequentially between threads ?

[Gwendal Roué]

> Le 16 août 2017 à 08:38, Clemens Ladisch <clem...@ladisch.de> a écrit :
> 
> Gwendal Roué wrote:
>> Serialized accesses from multiple threads is OK when the connection is
>> in the "Multi-thread" or "Serialized" threading modes, but not in the
>> "Single-thread" threading mode.
> 
> <http://www.sqlite.org/threadsafe.html> says:
> | 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
> |is unsafe to use in more than a single thread at once.
> 
> When the accesses are serialized, they are not _at once_.

According to your interpretation, "Single-thread" and "Multi-thread" modes are 
equivalent:

> Multi-thread. In this mode, SQLite can be safely used by multiple threads 
> provided that no single database connection is used simultaneously in two or 
> more threads.

Assuming there *is* a difference, I thus believe you are mistaken. Now 
documentation is ambiguous, I agree. My cautious interpretation says that only 
"Multi-thread" and "Serialized" modes are safe for serialized accesses from 
multiple threads (where "Multi-thread" is enough, and "Serialized" does too 
much).

Finally, we don't know which threading mode the OP is using. We don't even know 
if OP knows about threading modes. This topic may interest him.

Gwendal

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

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Gwendal Roué]

> Le 15 août 2017 à 08:44, Clemens Ladisch <clem...@ladisch.de> a écrit :
> 
> sanhua.zh wrote:
>> All 1. 2. 3. steps are run sequentially, which means that the step 2
>> runs after step 1 finished and step 3 runs after step 2 finished
>> theoretically .
>> Also, I can make sure the memory order between threads.
>> 
>> Then, is it a safe way to use sqlite connection ?
> 
> Yes.
> 
> Multi-threading problems are caused by multiple threads accessing the
> same data at the same time.  If code in multiple threads is serialized,
> it is, for practical purpose, identical to single-threaded code.

Serialized accesses from multiple threads is OK when the connection is in the 
"Multi-thread" or "Serialized" threading modes, but not in the "Single-thread" 
threading mode.

Have a look at https://www.sqlite.org/threadsafe.html for detailed information.

Gwendal Roué

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

Re: [sqlite] Problem with the new pointer-passing interface

[Gwendal Roué]

I agree that it's impossible to enforce "static strings" or "strings that live 
long enough" with the C/C++ type system.

You chose to force key management down the throat of your users, with two 
problems :

- All the potential memory leaks, thread races, etc that may occur if your key 
management has subtle concurrency bugs.
- Making your users think they can build *stuff* on top of this API, *stuff* 
that are explicitly out of scope of the pointer API as described by the design 
rationale of SQLite pointers API. Plus memory problems if your users think they 
can feed your API with arbitrary strings built at runtime.

I think the problem, assuming there is one, is not in the SQLite API.

Gwendal


> Le 4 août 2017 à 08:36, Ulrich Telle  a écrit :
> 
> Gwendal,
> 
>> But... why don't you simply ask your users for a static string as well???
>> C++ makes it trivial to support this requirement of the C API.
> 
> I could do that, of course. But it wouldn't solve the issue. It would push 
> the problem just one level up.
> 
>> // pointerType should be a static string
>> void wxSQLite3Statement::Bind(int paramIndex, void* pointer, char 
>> *pointerType, void(*DeletePointer)(void*))
> 
> That doesn't enforce a static string. The signature would have to be at least:
> 
> void wxSQLite3Statement::Bind(int paramIndex, void* pointer, const char* 
> pointerType, void(*DeletePointer)(void*))
> 
> However, if not called with a string literal, it would still easily fail to 
> work. Simplified example:
> 
> void* ptr = ...;
> char* pType = malloc(10);
> strcpy(pType, "carray");
> // ...
> stmt.Bind(1, ptr, pType, NULL);
> // ...
> free(pType);
> 
> Yes, I know, this is an artificial example. No one would implement it this 
> way in practice. It just demonstrates that even with a method signature 
> asking for a const char* it is not guaranteed to work. It would most probably 
> fail at runtime.
> 
> Additionally, wxWidgets supports various implicit string conversions. So - 
> just as SQLite itself - my wrapper methods can't detect whether a static 
> string or string literal was passed or whether the compiler constructed a 
> temporary string object. And the compiler will not issue error messages, 
> often not even warnings.
> 
> As mentioned in an earlier post, for my wrapper classes I decided to 
> implement the necessary housekeeping. Regarding the use of SQLite and my 
> wrapper classes I just quote a sentence which can be found in all SQLite 
> sources:
> 
> "May you do good and not evil."
> 
> Regards,
> 
> Ulrich
> ___
> 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 the new pointer-passing interface

[Gwendal Roué]

But... why don't you simply ask your users for a static string as well??? C++ 
makes it trivial to support this requirement of the C API.

// pointerType should be a static string
void wxSQLite3Statement::Bind(int paramIndex, void* pointer, char 
*pointerType, void(*DeletePointer)(void*))

Gwendal

> Le 3 août 2017 à 17:30, Ulrich Telle  a écrit :
> 
> Richard,
> 
>> Can you please provide more details on how having a dynamic string for
>> the pointer type would be helpful?  What is it that you are trying to
>> do that string constant will not work?  Please be as specific as
>> possible, so that I might better understand your problem.
> 
> I maintain a C++ wrapper library for SQLite especially for developers of 
> wxWidgets based applications. This wrapper library offers access to almost 
> all SQLite features (that make sense for an end-user application) through the 
> use of C++ classes. That is, the wrapper classes encapsulate all calls to the 
> SQlite C API.
> 
> With the release of SQLite 3.20.0 the new pointer-passing interface was 
> introduced, and I found it quite useful to support extensions like carray. 
> Therefore, I implemented a pointer bind method for the prepared SQL statement 
> classs. This method makes internally a call to function sqlite3_bind_pointer. 
> The signature and implementation of the method looks like this:
> 
> void wxSQLite3Statement::Bind(int paramIndex, void* pointer, const wxString& 
> pointerType, void(*DeletePointer)(void*))
> {
>  CheckStmt();
>  const char* localPointerType = m_stmt->MakePointerTypeCopy(pointerType);
>  int rc = sqlite3_bind_pointer(m_stmt->m_stmt, paramIndex, pointer, 
> localPointerType, DeletePointer);
> }
> 
> The member variable m_stmt is a reference counted reference object to a 
> prepared SQL statement (sqlite3_stmt). This makes it possible to pass around 
> the SQL statement object and to clean up the SQLite data structures when the 
> last reference to the statement is deleted. This reference object now 
> includes a dynamic array holding pointer type string duplicates until the 
> reference object itself goes out of scope.
> 
> However, in my first implementation I converted the pointer type string 
> parameter (wxString object) to a local char* variable. Since this local 
> variable was destroyed after leaving the method, the select on the carray 
> table failed, since the pointer type string was void.
> 
> Now, I create a copy of the pointer type string in a data structure that is 
> kept alive until the SQL statement object is deleted. The carray extension 
> now works flawlessly in the context of my wrapper.
> 
> For a C++ wrapper you could argue that using the SQLite API directly is 
> feasible. However, for SQLite wrappers for other languages like Python or 
> Lua, this might not work out.
> 
> Regards,
> 
> Ulrich
> ___
> 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 the new pointer-passing interface

[Gwendal Roué]

> Le 3 août 2017 à 15:27, Ulrich Telle  a écrit :
> 
> Thanks for the pointer. I have to admit that I referred to 
> http://sqlite.org/c3ref/bind_blob.html 
> .

No offense :-) The SQLite documentation has organically grown, and information 
is often scattered across several pages. It takes time and experience... even 
for brand new features.

Now I lack the expertise to answer your concerns, but am reading this 
conversation with interest.

Gwendal

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

Re: [sqlite] Problem with the new pointer-passing interface

[Gwendal Roué]

> Le 3 août 2017 à 14:27, Peter Da Silva <peter.dasi...@flightaware.com> a 
> écrit :
> 
> On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" 
> <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
> ulrich.te...@gmx.de> wrote:
>> IMHO it would be better if the function sqlite3_bind_pointer would make a 
>> copy of the type string and would thus be independent of the life span of 
>> the type parameter.
> 
> I believe that this was a deliberate choice to deter using temporary strings 
> for type names.

The reference (https://www.sqlite.org/bindptr.html) says:

> Because pointer types must be static strings, and because string values in 
> SQLite are dynamic strings, that means that SQL values cannot be used as a 
> pointer type. This prevents misguided developers from creating a new SQL 
> function that can manufacture pointer values directly from SQL. Such a 
> function, if possible to write, would undermine the security of the 
> pointer-passing APIs. Thus, the requirement that pointer types be static 
> strings helps to prevent misuse of the pointer-passing interfaces.


Gwendal Roué

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

Re: [sqlite] rowid as foreign key

[Gwendal Roué]

> Le 24 juil. 2017 à 20:58, Roman Fleysher <roman.fleys...@einstein.yu.edu> a 
> écrit :
> 
> Dear SQLiters,
> 
> Is it possible to link two tables using rowid, the implicit column? I tried 
> and it did not work, so I presume the answer to my question is "no".

Hello Roman,

The answer is yes. For example :

$ sqlite3 /tmp/db.sqlite
sqlite> CREATE TABLE foo (c);
sqlite> CREATE TABLE bar (c);
sqlite> INSERT INTO foo (c) VALUES ('foo');
sqlite> INSERT INTO bar (c) VALUES ('bar');
sqlite> SELECT foo.rowid, foo.c, bar.rowid, bar.c FROM foo, bar WHERE foo.rowid 
= bar.rowid;
1|foo|1|bar

Gwendal Roué

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Gwendal Roué]

> Le 24 juil. 2017 à 19:02, petern <peter.nichvolo...@gmail.com> a écrit :
> 
> Great.  But, if this is an ultimate replacement for BLOB'ed pointers, these
> new pseudo-null pointers must support SQLITE_STATIC and destructor function
> pointer lifetime disposition for those migrating their code.

You're right that the new APIs make pointer values unsuitable for 
*intermediate* values.

For example, `SELECT free(use(initialize(alloc(123`, while possible, is out 
of scope. One can define such functions, but it's very risky to misuse them, 
leak memory, or double-free.

The new pointer values indeed look *designed* so that the  lifetime of pointed 
values is managed *outside* of any statement execution.

I fail to see how blob'ed pointers did not have the exact same issue. 
SQLITE_STATIC and SQLITE_TRANSIENT manage the lifetime of the blob content, not 
of the content pointed by a blob'ed pointer. Or did I miss something?

Gwendal Roué

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Gwendal Roué]

> Le 24 juil. 2017 à 19:02, petern  a écrit :
> 
> To those posting low information congratulatory notes on this thread, you'd
> better hold off on popping those champagne corks.  The current API already
> contains irreversible additions to solve this problem that fell short.

Congrats can also go to clear documentation, scope, and rationale, even if the 
feature can be discussed :-)

Gwendal

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Gwendal Roué]

> Le 24 juil. 2017 à 13:54, Richard Hipp <d...@sqlite.org> a écrit :
> 
> https://www.sqlite.org/draft/bindptr.html

Thank you very much for this detailed rationale!

Gwendal Roué

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

Re: [sqlite] SQLite 3.20.0 postponed

[Gwendal Roué]

> Le 22 juil. 2017 à 08:14, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
> 
> Still, I feel that static strings are a weird way to define keys. For 
> example, value subtypes in SQLite have the same requirement of needing 
> "unique subtype identifiers", and those subtypes are, today, ints. Not 
> strings compared with strcmp(). Is there anything special with pointer 
> functions that have them require something different than ints for 
> identifying pointers?

Answering my own question: with int keys, it would be very easy to implement 
"interfaces that provide access to pointers of any type the user wants", that 
Richard does not want to support. Static strings indeed are a way to lock the 
API to "narrowly defined purposes".

OK, I'm good :-) Thanks a lot for your explanations, Richard!

Gwendal Roué

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

Re: [sqlite] SQLite 3.20.0 postponed

[Gwendal Roué]

> Le 21 juil. 2017 à 18:50, Richard Hipp <d...@sqlite.org> a écrit :
> 
> On 7/21/17, Gwendal Roué <gwendal.r...@gmail.com> wrote:
>> 
>> First, this strcmp() give a lot of work to languages that wrap SQLite and
>> lack support for "static strings".
> 
> But sqlite3_result_pointer() and sqlite3_bind_pointer() are not
> invoked from those languages.  The _pointer() routines are invoked
> from C, and C does easily support string literals that are static
> strings.
> 
> A C-language wrapper around sqlite3_result_pointer() and
> sqlite3_bind_pointer() that interfaces to the non-static-string
> language can simply insert the required static string.
> 
> We do not want the static string to be a parameter to a generic
> higher-level interface.  That defeats the purpose of the static
> string.  Remember, the string is a "pointer type".  We do not want to
> support interfaces that provide access to pointers of any type the
> user wants.  We are not trying to recreate C++ templates or other
> interfaces that work with arbitrary types.  Each use of _pointer() is
> intended to be used for a single narrowly defined purpose.

If I understand you correctly, no wrapper library in a foreign language should 
ever claim "New! Support for pointer functions introduced in SQLite 3.20.0".

Instead, it could claim: "New! Support for SQLite 3.32.0, including the new 
FTS6 full-text engine". The support for this hypothetical FTS6 engine would 
imply the use of pointer functions by the wrapper, but not by its clients. FTS6 
would have its own "narrowly defined purposes" for function pointers.

I see the point, and the fact that you don't want "to support interfaces that 
provide access to pointers of any type the user wants". I don't want to discuss 
that.

Still, I feel that static strings are a weird way to define keys. For example, 
value subtypes in SQLite have the same requirement of needing "unique subtype 
identifiers", and those subtypes are, today, ints. Not strings compared with 
strcmp(). Is there anything special with pointer functions that have them 
require something different than ints for identifying pointers?

Gwendal Roué

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

Re: [sqlite] SQLite 3.20.0 postponed

[Gwendal Roué]

> Le 21 juil. 2017 à 17:55, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
> 
> First, this strcmp() give a lot of work to languages that wrap SQLite and 
> lack support for "static strings". Building a global \0-terminated buffer 
> that never gets deallocated is not always that easy :-)

For the record, here is the commit that brings support for SQLite 3.20.0 : 
https://github.com/groue/GRDB.swift/commit/0a603f1be3966d478b505373af95d257224ce5b0
 
<https://github.com/groue/GRDB.swift/commit/0a603f1be3966d478b505373af95d257224ce5b0>

The context is custom FTS5 tokenisers for Swift: 
https://github.com/groue/GRDB.swift/blob/v1.2.2/Documentation/FTS5Tokenizers.md 
<https://github.com/groue/GRDB.swift/blob/v1.2.2/Documentation/FTS5Tokenizers.md>

Gwendal

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

Re: [sqlite] SQLite 3.20.0 postponed

[Gwendal Roué]

> Le 17 juil. 2017 à 20:54, Richard Hipp <d...@sqlite.org> a écrit :
> 
> The 3.20.0 release will be delayed.  Some concerns came up over the
> new sqlite3_value_pointer() interface.  Interface chagnes were made
> over the weekend.  But there are still concerns.  So the decision has
> been made to back off and give the current design a few weeks to soak
> before trying to press forward with a release which will commit us to
> a particular design.
> 
> The draft website is still up at https://sqlite.org/draft - note that
> the change log at https://sqlite.org/draft/releaselog/3_20_0.html now
> identifies three (obscure) backwards compatibility breaks.  Your input
> on these changes is requested.

Hello,

When I read the documentation for sqlite3_bind_pointer, I read:

> The T parameter should be a static string

The reason is pretty clear: this T parameter will be used later by 
sqlite3_value_pointer, for a string comparison with strcmp(). It hence has to 
remain is memory forever - and static strings are good at that.

I could test it and make it work reliably in Swift for custom FTS5 tokenisers.

Here is my comment: I wonder if the key comparison with strcmp() is really 
necessary.

First, this strcmp() give a lot of work to languages that wrap SQLite and lack 
support for "static strings". Building a global \0-terminated buffer that never 
gets deallocated is not always that easy :-)

Next, there are techniques for building unique "keys" that hold in a machine 
word, and can simply be compared with ==. For example:

typedef void *sqlite3_pointer_key_t;   // defined in sqlite3.h
sqlite3_pointer_key_t key1 = "my_key"; // nice for debugging
sqlite3_pointer_key_t key2 = // hard core but still valid

Maybe this is considered awful practices - I'm certainly not a C expert.

And this would also force functions that use the new pointer APIs to expose 
those keys in some header (such as FTS5()). You may have chosen the current 
technique precisely because you don't want such API pollution.

What were your rationale behind this choice?

Thanks in advance,
Gwendal Roué

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

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gwendal Roué]

> Le 20 juil. 2017 à 08:45, Clemens Ladisch <clem...@ladisch.de> a écrit :
> 
> Rowan Worth wrote:
>> On 18 July 2017 at 21:43, David Raymond <david.raym...@tomtom.com> wrote:
>>> You can run "begin deferred transaction" then walk away for 3 months
>>> without upsetting anything. If you need the precise timing then why not
>>> just use "begin immediate"?
>> 
>> IMMEDIATE would take a RESERVED lock which is clearly not desired in this
>> case -- the reader only wants a SHARED lock so as to minimise contention
>> with the writer.
> 
> Then to take a SHARED lock, you have to actually read something from the
> database, e.g.:
> 
>  BEGIN DEFERRED TRANSACTION;
>  PRAGMA user_version;

Yes, that's exactly that.

See 
https://github.com/groue/GRDB.swift/blob/2526e0a2f7097acbb71e41fb55baeda9d98c441d/GRDB/Core/DatabasePool.swift#L516-L540
 for an implementation

For a description of the benefits that users can have from such precision 
snapshotting, read: 
https://github.com/groue/GRDB.swift/blob/master/README.md#advanced-databasepool

And on such robust ground, you can build very high-level constructs like 
https://github.com/RxSwiftCommunity/RxGRDB

Gwendal Roué

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

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gwendal Roué]

Gerry, David, you are both right.

The possibility of a bug is thus eliminated for good. And 
https://www.sqlite.org/isolation.html <https://www.sqlite.org/isolation.html> 
is, strictly speaking, accurate, even if it another document is needed to avoid 
any interpretation doubt (http://www.sqlite.org/lang_transaction.html 
<http://www.sqlite.org/lang_transaction.html>).

Fact is, I spent a few days clearing things up, until I could eventually reach 
a correct program despite fuzzy premises ;-) Documentation is hard, and one 
never stops learning SQLite. Thanks for your quick and precise answers!

Gwendal


> Le 18 juil. 2017 à 15:43, David Raymond <david.raym...@tomtom.com> a écrit :
> 
> I think the documentation's good. I think you're missing the whole point of a 
> deferred transaction: that it doesn't start a "transaction" until it needs 
> to. You can run "begin deferred transaction" then walk away for 3 months 
> without upsetting anything. If you need the precise timing then why not just 
> use "begin immediate"?
> 
> http://www.sqlite.org/lang_transaction.html
> 
> A deferred transaction doesn't do anything until it first accesses the file. 
> Once it does then it will lock out any writers.
> 
> "Deferred means that no locks are acquired on the database until the database 
> is first accessed. Thus with a deferred transaction, the BEGIN statement 
> itself does nothing to the filesystem. Locks are not acquired until the first 
> read or write operation. The first read operation against a database creates 
> a SHARED lock and the first write operation creates a RESERVED lock."
> 
> 
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Gwendal Roué
> Sent: Tuesday, July 18, 2017 9:10 AM
> To: SQLite mailing list
> Subject: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper
> 
> Hello all,
> 
> The following sentence in https://www.sqlite.org/isolation.html does not 
> exactly describe the behavior of SQLite (since many versions):
> 
>> In WAL mode, SQLite exhibits "snapshot isolation". When a read transaction 
>> starts, that reader continues to see an unchanging "snapshot" of the 
>> database file as it existed at the moment in time when the read transaction 
>> started. Any write transactions that commit while the read transaction is 
>> active are still invisible to the read transaction, because the reader is 
>> seeing a snapshot of database file from a prior moment in time.
> 
> 
> I'll exhibit the inaccuracy below.
> 
> Meanwhile, "snapshot isolation" is a tremendous feature of SQLite. Not only 
> does it grant any read-only connection that opens a deferred transaction an 
> immutable and consistent view of the database. But when you can guarantee 
> that there is a single writer connection, snapshot isolation allows *precise 
> scheduling*, such as blocking the writer connection until a reader has 
> established snapshot isolation. With such precision, one can exactly control 
> what's available to a reader, while not blocking the writer longer than 
> necessary.
> 
> And this is where the documentation paragraph starts becoming inaccurate. For 
> the simplicity of the argument, I'll execute statements sequentially from two 
> connections W and R. To reproduce, just open two shells, and execute 
> statements in the following order:
> 
> $ sqlite3 /tmp/snapshotisolation.sqlite
> SQLite version 3.16.0 2016-11-04 19:09:39
> W> PRAGMA journal_mode=wal;
> W> CREATE TABLE t(a);
> R> BEGIN DEFERRED TRANSACTION;
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 1
> 
> This is unexpected. After connection R has started a deferred transaction, it 
> should continue to see an "unchanging snapshot of the database file as it 
> existed at the moment in time when the read transaction started". Obviously, 
> this is not the case, since the insertion performed by W is visible from R 
> even though it has been performed *after* R has started its deferred 
> transaction. The "Any write transactions that commit while the read 
> transaction is active are still invisible to the read transaction" is also 
> flat wrong here.
> 
> If we continue, things behave as expected:
> 
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 1
> 
> R does not see the new insertion, which means that it indeed lives in an 
> unchanging snapshot. It just happens that the snapshot was not established 
> when the transaction has started, as documented, but *later*. But when?
> 
> After a few experiments, it looks like the snap

[sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gwendal Roué]

Hello all,

The following sentence in https://www.sqlite.org/isolation.html does not 
exactly describe the behavior of SQLite (since many versions):

> In WAL mode, SQLite exhibits "snapshot isolation". When a read transaction 
> starts, that reader continues to see an unchanging "snapshot" of the database 
> file as it existed at the moment in time when the read transaction started. 
> Any write transactions that commit while the read transaction is active are 
> still invisible to the read transaction, because the reader is seeing a 
> snapshot of database file from a prior moment in time.


I'll exhibit the inaccuracy below.

Meanwhile, "snapshot isolation" is a tremendous feature of SQLite. Not only 
does it grant any read-only connection that opens a deferred transaction an 
immutable and consistent view of the database. But when you can guarantee that 
there is a single writer connection, snapshot isolation allows *precise 
scheduling*, such as blocking the writer connection until a reader has 
established snapshot isolation. With such precision, one can exactly control 
what's available to a reader, while not blocking the writer longer than 
necessary.

And this is where the documentation paragraph starts becoming inaccurate. For 
the simplicity of the argument, I'll execute statements sequentially from two 
connections W and R. To reproduce, just open two shells, and execute statements 
in the following order:

$ sqlite3 /tmp/snapshotisolation.sqlite
SQLite version 3.16.0 2016-11-04 19:09:39
W> PRAGMA journal_mode=wal;
W> CREATE TABLE t(a);
R> BEGIN DEFERRED TRANSACTION;
W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
1

This is unexpected. After connection R has started a deferred transaction, it 
should continue to see an "unchanging snapshot of the database file as it 
existed at the moment in time when the read transaction started". Obviously, 
this is not the case, since the insertion performed by W is visible from R even 
though it has been performed *after* R has started its deferred transaction. 
The "Any write transactions that commit while the read transaction is active 
are still invisible to the read transaction" is also flat wrong here.

If we continue, things behave as expected:

W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
1

R does not see the new insertion, which means that it indeed lives in an 
unchanging snapshot. It just happens that the snapshot was not established when 
the transaction has started, as documented, but *later*. But when?

After a few experiments, it looks like the snapshot is established on the first 
select:

W> PRAGMA journal_mode=wal;
W> CREATE TABLE t(a);
R> BEGIN DEFERRED TRANSACTION;
R> SELECT * FROM sqlite_master LIMIT 1; -- good enough to start the snapshot
W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
0 -- as expected

If now we stop entering commands by hand in the CLI, and start working with 
threads, the *precise scheduling* I mention at the beginning of the mail 
requires to block the writer connection W until the reader connection R has 
started a deferred transaction, and sqlite3_step() has been any executed once 
from any select statement. Only then can the writer connection be released, 
with a absolute control of the content of the reader's snapshot.

I'm not sure the behavior I've just described can be called a bug. The snapshot 
is unchanging indeed. When there are several processes connected to the 
database, a reader can't know whether a change has been performed before its 
deferred transaction has started, or between the start of the deferred 
transaction and its first select statement, and therefore should not care at 
all. With this interpretation, there is nothing wrong in the current behavior 
of SQLite.

However, when an application developper is sure that the database has a single 
writer connection, the question of the content of the snapshot suddenly becomes 
relevant. And the documentation becomes inaccurate.

What do SQLite concurrency masters of this mailing list think? Besides that 
threads are evil, I mean ;-) Shouldn't the documentation be updated?

Cheers to all,
Gwendal
PS: For the record, I've been talking about "precise scheduling" and "absolute 
control of the content of the snapshot" because they are the necessary 
conditions for some database observation features such as reloading fresh 
values from a request as soon as a transaction has modified its content. See 
https://github.com/RxSwiftCommunity/RxGRDB for some high-level implementations 
of such feature.



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

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

[Gwendal Roué]

> Le 28 mai 2017 à 13:24, Bart Smissaert  a écrit :
> 
>> Calling sqlite3_clear_bindings does the same thing as calling
> sqlite3_bind_null for all arguments.
> 
> Yes, I understand that, just thinking about efficiency.

Then I don't know. Your experience will tell.

>> I personnally call sqlite3_reset before sqlite3_clear_bingings with great
> success
> 
> I am doing the same now. Probably no difference there

I suppose so.

>> is there any point *not* checking a result code whenever you are given
> the opportunity to?
> 
> Yes, there is if there is no possible way in that particular situation that
> the result could be other than success.
> If there was a successful sqlite3_step just preceding it could a
> sqlite3_reset possibly be unsuccessful?
> If there was a successful sqlite3_reset just preceding it could a
> sqlite3_clear_bindings possibly be unsuccessful?

The documentation is your reference. If the documentation does not answer your 
questions, then you shouldn't assume anything, and take the only reasonable 
decision: check for errors whenever you can.

Gwendal

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

Re: [sqlite] sqlite3_reset and sqlite3_clear_bindings

[Gwendal Roué]

Hello Bart,

> Le 28 mai 2017 à 13:03, Bart Smissaert  a écrit :
> 
> Using SQLite3 3.19.0 on a Windows machine.
> I have some general questions about sqlite3_reset and
> sqlite3_clear_bindings:
> I am processing data from a 2D variant array (this is VB6).
> 
> 1. I understand that after processing a row (binding all the values in a
> row of that variant array)
> I need to do either sqlite3_clear_bindings or make sure the next row has
> all the values bound
> either with a value (eg with sqlite3_bind_int) or with sqlite3_bind_null.
> Now if I am sure that there are always values to be bound (so I will never
> need sqlite3_bind_null) then I don't need sqlite3_clear_bindings. If there
> are empty array elements however then I could either do sqlite3_bind_null
> or always do sqlite3_clear_bindings after processing a row.
> In general what would be the most efficient approach? I suppose the only
> way to find out is testing, but maybe somebody can shred some light on this.

You are responsible for binding correct values before executing any statement. 
Calling sqlite3_clear_bindings does the same thing as calling sqlite3_bind_null 
for all arguments.

> 2. Is there any difference in the order of doing sqlite3_reset and
> sqlite3_clear_bindings?

I personnally call sqlite3_reset before sqlite3_clear_bingings with great 
success, but I don't know if the order is relevant or not.

> 3. Is there any point in checking the return value of
> sqlite3_clear_bindings, especially if it was
> already preceded by a successful sqlite3_reset?
> 
> 4. Is there any point in checking the return value of sqlite3_reset if
> there was a successful
> sqlite3_bind_XXX preceding it?

3, 4: is there any point *not* checking a result code whenever you are given 
the opportunity to?

Of course you have to check it. The two functions perform a different job, and 
may fail for different reasons. For example, sqlite3_reset() will return an 
error if a previous execution of the statement has returned an error. I'm 
almost sure sqlite3_clear_bindings does not.

Happy SQLite :-)
Gwendal

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

Re: [sqlite] NOT NULL integer primary key

[Gwendal Roué]

> Le 18 mai 2017 à 18:16, Paul Sanderson <sandersonforens...@gmail.com> a écrit 
> :
> 
> Is this a bug?
> 
> Create table test (id integer not null primary key, data text);
> insert into test values (null, 'row1');
> select * from test;
> 1, row1
> 
> I know that if you provide a NULL value to a column define as integer
> primary key that SQLite will provide a rowid, but should the not null
> constraint be obeyed?

Hello Paul,

The constraint is obeyed, since there is no NULL values in the database.

To put it in another way: constraints are properties of the *database content*, 
not of the *operations* on content. They're static, not dynamic.

That's why constraints can be checked with PRAGMA schema.foreign_key_check, 
which tells if the current state of the database content is valid.

That's also why the insert statement above succeeds, as long as the value that 
is eventually inserted in the database is NOT NULL.

Gwendal Roué

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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 11 mai 2017 à 14:29, Richard Hipp <d...@sqlite.org> a écrit :
> 
> On 5/11/17, Gwendal Roué <gwendal.r...@gmail.com> wrote:
> 
>> 1. Existing callbacks that catch SQLITE_READ expect a non-NULL column
>> 
> 
> Very well.  The behavior has been changed so that an SQLITE_READ with
> an empty-string column name, instead of a NULL column name, is invoked
> when a table referenced but not used.  Also, the documentation has
> been updated to clearly state that any of the string arguments to the
> authorizer callback may be NULL.

Many thanks, Richard, for your understanding and SQLite :-)

Gwendal

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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 10 mai 2017 à 18:22, Richard Hipp <d...@sqlite.org> a écrit :
> 
> On 5/10/17, Dominique Devienne <ddevie...@gmail.com> wrote:
>> 
>> We haven't heard from Richard, but I hope we will eventually.
>> 
> 
> No new authorizer codes will be added, since that would present
> compatibility problems for legacy authorizer callbacks.  Instead, the
> fix is to invoke the authorizer callback with SQLITE_READ but with a
> NULL column name for any table that is referenced but for which no
> columns are extracted.
> 
> This change is more likely to be compatible with legacy authorizer
> callbacks.  In particular, the authorizer callback used by Fossil
> (https://www.fossil-scm.org/fossil/artifact/ee53ffbf7?ln=161-221)
> continues to work fine, and with the enhanced SQLITE_READ, no prevents
> users from creating a report using
> 
> SELECT count(*) FROM user
> 
> That returns the number of users, for example.
> 
> The fix is implemented by https://www.sqlite.org/src/timeline?c=92ab1f72 
> <https://www.sqlite.org/src/timeline?c=92ab1f72>

Thanks a lot, Richard!

I would naively expect SQLITE_READ with a NULL column a bigger threat for 
backward compatibility than a new authorizer code, because:

1. Existing callbacks that catch SQLITE_READ expect a non-NULL column

2. Existing callbacks are more likely to implement a blacklist, and use the 
authorizer code in a switch statement that does not do anything in its default 
case. The reason for favouring blacklisting over whitelisting is that the list 
of authorizer codes is not documented as closed, and that the documentation 
shows that the list of authoriser codes has already changed in the past (the 
no-longer used SQLITE_COPY).

The example below (a callback that denies access to the `token` column of the 
`users` table) would be harmed by SQLITE_READ with a NULL column because 
strcmp() isn't supposed to accept NULL input:

int denyTokenAccess(void *pUserData, int code, const char *s1, const char *s2, 
const char *s3, const char *s4) {
switch(code) {
case SQLITE_READ:
if (strcmp(s1, "users") == 0 && strcmp(s2, "token") == 0) {
return SQLITE_DENIED;
} else {
    return SQLITE_OK;
}
default:
return SQLITE_OK
}
}

What do you think?
Gwendal Roué

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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 10 mai 2017 à 15:06, Dominique Devienne <ddevie...@gmail.com> a écrit :
> 
> On Wed, May 10, 2017 at 1:35 PM, Gwendal Roué <gwendal.r...@gmail.com>
> wrote:
> 
>>> Le 9 mai 2017 à 15:41, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
>>>> How are you going to handle TRIGGERs ?
>>> 
>>> That's a very good question.
>> 
>> Very good news: foreign keys and triggers are 100% handled by authorizer
>> callbacks, for free :-D
>> 
> 
> Thanks for confirming. That's what I thought, from previous discussions on
> this list.
> 
> We haven't heard from Richard, but I hope we will eventually.

So do I.

Since my real topic is answering the question "were the results of statement S 
modified by transaction T?", I can imagine how this SQLITE_READ_TABLE proposal 
looks indirect, and potentially misled.

I tried to explain before that this question requires a lot of careful use of 
commit/rollback/update hooks on top of authorizer callbacks in order to provide 
as few false positives as possible.

A better written feature request could remain strictly at the statement level. 
Here is a documentation for a potential sqlite3_stmt_independent() function, 
inspired from the documentation of sqlite3_stmt_readonly:

# Statement Independence Test

int sqlite3_stmt_independent(sqlite3_stmt*, sqlite3_stmt*);

The sqlite3_stmt_independent(S1, S2) returns true (non-zero) if and 
only if the prepared statement S1 makes no changes to the database table and 
columns used by the prepared statement S2. Changes that are taken in account 
are direct changes to the table and columns used by S2, but also indirect 
changes through foreign keys cascades, and indirect changes through triggers.

For example, given the following SQL statement:

SELECT a, b FROM t1;

The following statements are considered independent:

SELECT * FROM t1;
INSERT INTO t2 (...);
UPDATE t1 SET c = c + 1;

The following statements are considered dependent:

DELETE FROM t1;
UPDATE t1 SET a = 1;
INSERT INTO t1 (a, b) VALUES (1, 2);

Note that application-defined SQL functions or virtual tables might 
change the database indirectly as a side effect. For example, if an application 
defines a function "eval()" that calls sqlite3_exec(), then the following SQL 
statement would change the database through side-effects:

SELECT eval('DELETE FROM t1') FROM t2;

But because the SELECT statement does not change the database directly, 
sqlite3_stmt_independent would still return true, regardless of the second 
parameter.


> I very much agree with you the authorizer API should be "complete" and 
> "exhaustive" in what is truly accessed.

That's my current strategy :-)

The core team may prefer the sqlite3_stmt_independent(S1, S2) function above. 
But I'm not sure: currently authorizer callbacks are called during the parsing 
phase: SQLite doesn't internally keep any information about the impact of a 
statement besides its execution plan. An eventual sqlite3_stmt_independent 
function may be difficult to implement, depending on how obsfuscated is the 
information it needs after the statement has been compiled. I don't know the 
inner SQLite guts enough.

With SQLITE_READ_TABLE, we have a way to let the library user gather the needed 
information, with a simple implementation (the only one I could provide with my 
current knowledge of the library).

> A flag could be set to not issue the new calls you propose, for backward 
> compatibility purposes (and also perhaps because both FKs and TRIGGERs are an 
> implementation "detail" of the schema, that not all clients should be aware 
> of, unless explicitly requested by the client, perhaps even checked by the 
> authorizer itself, to complete the circle :)). --DD

I don't know if backward compatibility is an issue here: code that did not 
check for SQLITE_READ_TABLE has no reason to break after SQLITE_READ_TABLE has 
been introduced.

Gwendal Roué

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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 9 mai 2017 à 15:41, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
> 
>>> As a reminder, I intend to use the authorisation system in order to tell if 
>>> a statement has an opportunity to impact on another statement, as a support 
>>> for a general database observation feature.
>> 
>> How are you going to handle TRIGGERs ?  The authoriser analyses each 
>> statement individually.  It will not tell you everything that’s in the 
>> TRIGGER until it decides to execute those statements.
> 
> That's a very good question.

Very good news: foreign keys and triggers are 100% handled by authorizer 
callbacks, for free :-D

How much a boon is SQLite, frankly ???

Look:

CREATE TABLE table1 ( ... );
CREATE TABLE table2 ( ... );
CREATE TRIGGER trigger1 AFTER INSERT ON table2 BEGIN DELETE FROM table1; END;

INSERT INTO table2 (id) VALUES (NULL);
-- SQLITE_INSERT table2 main
-- SQLITE_DELETE table1 main trigger1 :-)

Gwendal Roué

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

Re: [sqlite] foreign key constraint failure

[Gwendal Roué]

There is a way, but it requires some effort:

First let's define a schema that reproduces your error:

CREATE TABLE t1 (
  id INTEGER PRIMARY KEY);
CREATE TABLE t2 (
  id INTEGER PRIMARY KEY,
  id1 INTEGER REFERENCES t1(id) ON DELETE RESTRICT);
INSERT INTO t1 (id) VALUES (123);
INSERT INTO t2 (id, id1) VALUES (456, 123);

-- error: FOREIGN KEY constraint failed
DELETE FROM t1

OK, error is reproduced.

Now you want to know which foreign key has failed:

PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
DELETE FROM t1 -- no error this time
PRAGMA foreign_key_check
-- table:"t2" rowid:456 parent:"t1" fkid:0

This means that row 456 of table t2 has a broken foreign to table t1.

If you want to know which row in t1 can not be deleted:

PRAGMA foreign_key_list(t2)
-- id:0 seq:0 table:"t1" from:"id1" to:"id" on_update:"NO ACTION" 
on_delete:"RESTRICT" match:"NONE"

OK so id1 in table t2 gives the id of the t1 row which can not be deleted:

SELECT id1 FROM t2 WHERE id = 456
-- id1:123

This is row 123 of t1 which can not be deleted.

Make sure to rollback the failed transaction, and restore foreign key checks:

ROLLBACK
PRAGMA foreign_keys = ON

Gwendal Roué

> Le 10 mai 2017 à 06:57, Mark Wagner <m...@google.com> a écrit :
> 
> Is there a way to get sqlite to tell which foreign key constraint is
> causing a failure? Some kind of verbose mode?
> 
> Thanks!
> 
> sqlite> delete from t;
> 
> Error: FOREIGN KEY constraint failed
> 
> sqlite>
> ___
> 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] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 9 mai 2017 à 15:02, Simon Slavin <slav...@bigfraud.org> a écrit :
> 
> On 9 May 2017, at 7:23am, Gwendal Roué <gwendal.r...@gmail.com 
> <mailto:gwendal.r...@gmail.com>> wrote:
> 
>> As a reminder, I intend to use the authorisation system in order to tell if 
>> a statement has an opportunity to impact on another statement, as a support 
>> for a general database observation feature.
> 
> I’ve read your proposed mechanism.  Providing support for all those callbacks 
> looks like it would considerably slow down SQLite.

Observation is an opt-in service. The user decides which queries are observed.

I haven't done any benchmark yet. Still, I care about performance, and GRDB is 
the fastest wrapper in its category: 
https://github.com/groue/GRDB.swift/wiki/Performance.

Database observation is such an awesome service I could pay a little bit for 
it, though. If performance hit is too high (yet to be measured), then the user 
can still send change notifications in another way.

> How are you going to handle TRIGGERs ?  The authoriser analyses each 
> statement individually.  It will not tell you everything that’s in the 
> TRIGGER until it decides to execute those statements.

That's a very good question. I have indeed to think about triggers, and also 
foreign key cascades.

Unless I'm wrong, cascades can be handled through foreign keys introspection. 
Triggers, on the other side...

Well, if triggers can not be handled, then the documentation will have to tell 
it in an explicit way: this does not invalidate the whole idea.

> I don’t think the authorisation system can be efficiently used in this way.  
> If you’re analysing statements speculatively you’ll get more information out 
> of EXPLAIN.

Thanks for the suggestion. Even if EXPLAIN can give enough information, this 
does not invalidate the information provided by authorizer callbacks.

Gwendal

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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 9 mai 2017 à 08:23, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
> 
> As a reminder, I intend to use the authorisation system in order to tell if a 
> statement has an opportunity to impact on another statement, as a support for 
> a general database observation feature.
> 
> Here is the general process:
> 
> 1. During the compilation of a statement S1, authorizer callbacks tell which 
> tables and columns are read by the statement.
> 2. During the compilation of any other statement S2, authorizer callbacks 
> tell which tables and columns are written by the statement.
> 3. If the two sets of tables and columns do not intersect, then S2 can not 
> change the results of S1: exit.
> 4. Until callbacks registered by sqlite3_commit_hook or sqlite3_rollback_hook 
> [2] are invoked, authoriser callbacks allows the app to follow the savepoint 
> stack. This is important for the next step:
> 5. During the execution of S2, sqlite3_update_hook [1] tell if S2 actually 
> performs any change. Those changes are remembered until the transaction 
> commits [2], or the eventual current savepoint is rollbacked (see step 4 
> above).
> 6. After the transaction has been committed, if S2 has performed changes, 
> then S1 is reputed "dirty", and the application is notified that S1 results 
> may have changed.

Let me please rewrite the above process, to make it more clear.

Goal: notify that the last commit may have changed the results of statement S1, 
without any false negative, and a number of false positives that is as low as 
possible.

Let's use `SELECT col1 FROM t1` as S1.


# LEVEL 1: notify after each commit with sqlite3_commit_hook.

Need for improvement: there are many many false positives. Even an empty 
transaction `BEGIN; COMMIT;` triggers a notification.


# LEVEL 2: notify only for statements that write in the columns and tables read 
by S1 (thanks to the authorizer callbacks)

Good: `INSERT INTO t2 ...` and `UPDATE t1 SET col2 = 'foo'` no longer trigger a 
change notification, because authorizer callbacks prove that they have no 
impact of S1.

Need for improvement 1: if S1 uses the COUNT function, then all statements may 
have an impact on it, and we're back to level 1 above (read my previous emails 
about his trouble that SQLITE_READ_TABLE aims at solving)

Need for improvement 2: a statement that may modify S1 may not modify the 
database: for example `DELETE FROM t1 WHERE 0` won't delete a single line, but 
the change notification will be triggered anyway.


# LEVEL 3: notify only for statements that perform database modifications 
(thanks to the database change callbacks)

Good: `DELETE FROM t1 WHERE 0` no longer triggers a change notification, 
because it did not update, delete, or insert a single row.

Need for improvement: a database change callback may be invalidated by a 
rollbacked savepoint.


# LEVEL 4: filter out database changes that are rollbacked by a savepoint 
(thanks to the authorizer callbacks that allow the sqlite3 client to track the 
savepoint stack)

Good: `INSERT INTO t1 ...` no longer triggers a change notification if it is 
rollbacked.


# LEVEL 5: there is no level 5, I think we've gone as far as SQLite can do, and 
this is already quite tremendous.


Again, the problem is with the COUNT function. By I should stop repeating 
myself :-)

Thanks for reading,
Gwendal Roué

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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 9 mai 2017 à 00:21, Simon Slavin <slav...@bigfraud.org> a écrit :
> 
> Hardly anyone uses the authentication system, so far fewer people know the 
> answers.

As a reminder, I intend to use the authorisation system in order to tell if a 
statement has an opportunity to impact on another statement, as a support for a 
general database observation feature.

Here is the general process:

1. During the compilation of a statement S1, authorizer callbacks tell which 
tables and columns are read by the statement.
2. During the compilation of any other statement S2, authorizer callbacks tell 
which tables and columns are written by the statement.
3. If the two sets of tables and columns do not intersect, then S2 can not 
change the results of S1: exit.
4. Until callbacks registered by sqlite3_commit_hook or sqlite3_rollback_hook 
[2] are invoked, authoriser callbacks allows the app to follow the savepoint 
stack. This is important for the next step:
5. During the execution of S2, sqlite3_update_hook [1] tell if S2 actually 
performs any change. Those changes are remembered until the transaction commits 
[2], or the eventual current savepoint is rollbacked (see step 4 above).
6. After the transaction has been committed, if S2 has performed changes, then 
S1 is reputed "dirty", and the application is notified that S1 results may have 
changed.

The process above is able to provide false positives: for example `UPDATE TABLE 
t1 SET a = a` will trigger a notification, even though no change did occur.

What is important is that the process above doesn't miss any potential change.

Because of the current authorizer callbacks for queries like `SELECT COUNT(*) 
FROM t1`, which do not tell anything about t1, the step 3 above has to assume 
that *any* statement has the opportunity to modify the results of this select. 
This yields too many false positives.

My suggestion is there to allow a less paranoid observation of statements that 
use the COUNT function.

It is important to stress that I perfectly know that all those authorizer, 
update, commit, rollback hooks *can not* help observing a database as soon as 
there are several writer connections. But they *can* help as soon as there is a 
single writer connection.

That's exactly why I'm here: the above algorithm is already used by GRDB.swift 
[3], a database library focused on application development that puts all bets 
on SQLite. GRDB has a serious and robust concurrency model [4] which supports a 
single connection to a regular database, or a pool of several connections on a 
WAL database. Both use a single writer connection. Both would be improved with 
my suggestion.

Of course, the core team may prefer not implementing my suggested 
SQLITE_READ_TABLE. But I wish they would react to the above scenario.

Gwendal Roué

[1] https://www.sqlite.org/c3ref/update_hook.html 
<https://www.sqlite.org/c3ref/update_hook.html>
[2] https://sqlite.org/c3ref/commit_hook.html 
<https://sqlite.org/c3ref/commit_hook.html>
[3] http://github.com/groue/GRDB.swift <http://github.com/groue/GRDB.swift>
[4] https://github.com/groue/GRDB.swift/blob/master/README.md#concurrency 
<https://github.com/groue/GRDB.swift/blob/master/README.md#concurrency>


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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 9 mai 2017 à 00:21, Simon Slavin <slav...@bigfraud.org> a écrit :
> 
> 
> On 8 May 2017, at 10:11pm, petern <peter.nichvolo...@gmail.com> wrote:
> 
>> Who is the author of the Authorizer Action Code source?
> 
> Although SQLite is in the public domain, development of it is not typical for 
> an open source project.  Almost everything you download when you download 
> SQLite was written by a development team of three or four people.  
> Contributions from outside that group are rarely (? ever ?) incorporated into 
> the project as source code supplied.  Instead the development group takes 
> suggestions submitted on this list and sometimes decides to write code to 
> implement them.

Thanks for the information: I didn't know that.

> Instead, SQLite provides many hooks and callback opportunities, and people 
> are encouraged to write their own extensions and host them themselves.

Forking SQLite is a very hard path, unfortunately :-)

Gwendal Roué

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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 8 mai 2017 à 23:11, petern <peter.nichvolo...@gmail.com> a écrit :
> 
> Gwendal.  I understand all that.  It's also good that you've confirmed how
> SQLITE_READ is actually queried by the authorizer callback interface.  I
> was wondering about that.  Reading your earlier post, one might get the
> impression that the SQLITE_READ authorizer action was not queried by the
> engine for aggregate table reads for some reason.  Presumably that would be
> a bug.
> 
> My question about your solution is illustrated by looking at the existing
> defines for orthogonal operations.  Consider how SELECT, INSERT, and UPDATE
> are currently defined as below.
> 
> #define SQLITE_INSERT   18   /* Table Name  NULL
> */
> #define SQLITE_SELECT   21   /* NULLNULL
> */
> #define SQLITE_UPDATE   23   /* Table Name  Column Name
> */
> 
> If this interface is logically missing SQLITE_READ_TABLE then shouldn't all
> the orthogonal authorizer action codes in that same dimension also be
> implemented?  Thus, why not also add authorizer action codes for
> SQLITE_WRITE_TABLE, SQLITE_READ_COLUMN, SQLITE_WRITE_COLUMN,
> SQLITE_READ_SCHEMA, and SQLITE_WRITE_SCHEMA?  Why only just
> SQLITE_READ_TABLE?   If SQLITE_READ_TABLE is missing why aren't the others
> also missing?

Because they are not missing: existing authorizer callbacks already provide a 
detailed information for all possible updates. We just miss information about 
selected tables.

> Why is this forum so silent on this question?  Usually there are half a
> dozen responses on the "correct way" to do it.  This time, crickets.

I did propose a patch as a way to show that my proposal doesn't come from thin 
air, but can be implemented.

Yes, I wish the core team would give at least an acknowledgement that something 
could be improved.

Gwendal Roué

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

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

Hello Peter,

It's the generally the responsability of the callback implementor to test or 
not each authorization, depending on her needs. See 
https://sqlite.org/c3ref/set_authorizer.html

-- Allow user to run select statements, and read col1 of t1:
-- SQLITE_SELECT
-- SQLITE_READ t1 col1 main
SELECT col1 FROM t1;

-- Allow user to run select statements, read col1 of t1, and insert in t2:
-- SQLITE_INSERT t2 main
-- SQLITE_SELECT
-- SQLITE_READ t1 col1 main
INSERT INTO t2 SELECT col1 FROM t1;

There are also authorization callbacks for functions:

-- Allow user to run select statements, read col1 of t1, execute count 
function:
-- SQLITE_SELECT
-- SQLITE_FUNCTION max
-- SQLITE_READ t1 col1 main
SELECT MAX(col1) FROM t1

But here is why I'm suggesting a new code SQLITE_READ_TABLE:

-- Allow user to run select statements, and execute count function:
-- SQLITE_SELECT
-- SQLITE_FUNCTION count
SELECT COUNT(*) FROM t1

In the previous query, no one knows that the table t1 is about to be used.

The authorizer callback can not be extended so that it tells everything about a 
function arguments. That's because a function arguments can be too complex to 
fit in the callback arguments:

-- SQLITE_SELECT
-- SQLITE_FUNCTION count
SELECT COUNT(*) FROM t1, t2, t3, t4, t5

-- SQLITE_SELECT
-- SQLITE_FUNCTION count
-- SQLITE_READ t1 col1 main
-- SQLITE_READ t2 col1 main
-- SQLITE_READ t3 col1 main
SELECT COUNT(DISTINCT t1.col1 + t2.col1 + t3.col1) FROM t1, t2, t3

With the newly introduced SQLITE_READ_TABLE code, we have instead:

-- SQLITE_SELECT
-- SQLITE_READ t1 main
-- SQLITE_FUNCTION count
SELECT COUNT(*) FROM t1

And now the client knows that the table t1 is used, and can forbid this access.

Gwendal Roué

> Gwendal.  Your proposal last month for adding column names to the callback 
> parameters seemed more sensible.
> 
> The first question that comes to mind when new callback modes are to being 
> proposed is what else would be missing if the same standard were applied to 
> every possible operation?
> 
> My thought.  A cursory read of the relevant code comments (see below) 
> suggests the author had in mind only precise security control of views and 
> triggers - the ubiquitous 6th parameter mentioned in the comment.  If that's 
> the idea, then one presumably denies everything by default and then handles 
> requests only to a purpose built secure view and trigger layer.
> 
> It would be nice to hear from the author about what they actually had in mind 
> for those who need total iron clad security of every row or aggregate query 
> of any table.  For example, if SQLITE_READ authorization is not being tested, 
> why not?  Is it tested later?  Perhaps the architecture of the authorizer is 
> not self explanatory from the names of the #defines or is described elsewhere.
> 
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 6 mai 2017 à 15:12, Gwendal Roué <gwendal.r...@gmail.com> a écrit :
> 
> Hello,
> 
> This email contains a patch that introduces a new authorizer action code: 
> SQLITE_READ_TABLE.

My patch did not work when the authorizer callback would not return SQLITE_OK.

Please find the fixed patch below:

$ fossil info
project-name: SQLite
repository:   /Users/groue/Documents/git/sqlite/sqlite.fossil
local-root:   /Users/groue/Documents/git/sqlite/
config-db:/Users/groue/.fossil
project-code: 2ab58778c2967968b94284e989e43dc11791f548
checkout: b9a58daca80a815e87e541cb5fff9bc8b93f131d 2017-05-04 11:13:50 UTC
parent:   e24b73820cdca07eee87853fe6dd9f60d76e039e 2017-05-03 19:36:50 UTC
tags: trunk
comment:  Fix a collision of the "B0" identifier name between the termios.h 
header file and the SHA3 implementation in the shell. (user: drh)
check-ins:18701

$ fossil diff
Index: src/select.c
==
--- src/select.c
+++ src/select.c
@@ -4370,10 +4370,15 @@
 }else{
   /* An ordinary table or view name in the FROM clause */
   assert( pFrom->pTab==0 );
   pFrom->pTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom);
   if( pTab==0 ) return WRC_Abort;
+  int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
+  if( sqlite3AuthCheck(pParse, SQLITE_READ_TABLE, pTab->zName, 0, 
db->aDb[iDb].zDbSName) ){
+pFrom->pTab = 0;
+return WRC_Abort;
+  }
   if( pTab->nTabRef>=0x ){
 sqlite3ErrorMsg(pParse, "too many references to \"%s\": max 65535",
pTab->zName);
 pFrom->pTab = 0;
 return WRC_Abort;

Index: src/sqlite.h.in
==
--- src/sqlite.h.in
+++ src/sqlite.h.in
@@ -2824,10 +2824,11 @@
 #define SQLITE_DROP_VTABLE  30   /* Table Name  Module Name */
 #define SQLITE_FUNCTION 31   /* NULLFunction Name   */
 #define SQLITE_SAVEPOINT32   /* Operation   Savepoint Name  */
 #define SQLITE_COPY  0   /* No longer used */
 #define SQLITE_RECURSIVE33   /* NULLNULL*/
+#define SQLITE_READ_TABLE   34   /* Table Name  NULL*/
 
 /*
 ** CAPI3REF: Tracing And Profiling Functions
 ** METHOD: sqlite3
 **

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

[sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

Hello,

This email contains a patch that introduces a new authorizer action code: 
SQLITE_READ_TABLE.

The goal of this new action code is to fill a hole in the current authorization 
API, which does not tell about all tables read by a statement. For example, the 
statement "SELECT COUNT(*) FROM table1" currently invokes the callback twice: 
SQLITE_SELECT, SQLITE_FUNCTION. Nothing is said about table1.

With the provided patch, we add a third invocation of the callback, with the 
new code SQLITE_READ_TABLE. Its 3d parameter is "table1", and the schema name 
is the 5th parameter.

Following the current practice which calls sqlite3AuthCheck() during the 
parsing phase, I have added a call to sqlite3AuthCheck() in the 
selectExpander() function, right after the call to sqlite3LocateTableItem().

Basically, for each table used by the select statement, either it is not found 
by sqlite3LocateTableItem(), either it has to be authorized by the 
authorization callback.

I'm not familiar with the way code and feature requests are handled within the 
SQLite community. If you are interested about this patch, let me know how I can 
help!

Gwendal Roué


$ fossil info
project-name: SQLite
repository:   /Users/groue/Documents/git/sqlite/sqlite.fossil
local-root:   /Users/groue/Documents/git/sqlite/
config-db:/Users/groue/.fossil
project-code: 2ab58778c2967968b94284e989e43dc11791f548
checkout: b9a58daca80a815e87e541cb5fff9bc8b93f131d 2017-05-04 11:13:50 UTC
parent:   e24b73820cdca07eee87853fe6dd9f60d76e039e 2017-05-03 19:36:50 UTC
tags: trunk
comment:  Fix a collision of the "B0" identifier name between the termios.h 
header file and the SHA3 implementation in the shell. (user: drh)
check-ins:18701


$ fossil diff
Index: src/select.c
==
--- src/select.c
+++ src/select.c
@@ -10,10 +10,11 @@
 **
 *
 ** This file contains C code routines that are called by the parser
 ** to handle SELECT statements in SQLite.
 */
+#include 
 #include "sqliteInt.h"
 
 /*
 ** Trace output macros
 */
@@ -4370,10 +4371,14 @@
 }else{
   /* An ordinary table or view name in the FROM clause */
   assert( pFrom->pTab==0 );
   pFrom->pTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom);
   if( pTab==0 ) return WRC_Abort;
+  int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
+  if( sqlite3AuthCheck(pParse, SQLITE_READ_TABLE, pTab->zName, 0, 
db->aDb[iDb].zDbSName) ){
+return WRC_Abort;
+  }
   if( pTab->nTabRef>=0x ){
 sqlite3ErrorMsg(pParse, "too many references to \"%s\": max 65535",
pTab->zName);
 pFrom->pTab = 0;
 return WRC_Abort;

Index: src/sqlite.h.in
==
--- src/sqlite.h.in
+++ src/sqlite.h.in
@@ -2824,10 +2824,11 @@
 #define SQLITE_DROP_VTABLE  30   /* Table Name  Module Name */
 #define SQLITE_FUNCTION 31   /* NULLFunction Name   */
 #define SQLITE_SAVEPOINT32   /* Operation   Savepoint Name  */
 #define SQLITE_COPY  0   /* No longer used */
 #define SQLITE_RECURSIVE33   /* NULLNULL*/
+#define SQLITE_READ_TABLE   34   /* Table Name  NULL*/
 
 /*
 ** CAPI3REF: Tracing And Profiling Functions
 ** METHOD: sqlite3
 **

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

[sqlite] Feature request: please have the sqlite3_set_authorizer callback given the full list of tables and columns used by a statement

[Gwendal Roué]

Hello,

I'm the author of GRDB.swift [1], a Swift wrapper around SQLite which aims, 
among other things, at notifying of transactions that may have an impact on a 
the results of a SELECT statement.

For example, `SELECT a, b FROM table1` is impacted by `DELETE FROM table1`, but 
not by `UPDATE table1 SET c = 1` or `INSERT INTO table2 (...)`.

To achieve this feature, GRDB uses a sqlite3_set_authorizer callback [2]. For 
example, the callback is given SQLITE_READ which tells that `SELECT a, b FROM 
table1` uses the columns a and b from table1. The authorizer callback can also 
been given SQLITE_INSERT, which tells that `INSERT INTO table2 (...)` performs 
an insertion in table2.

Those pieces of information can be compared together, so that one can deduce 
that `INSERT INTO table2 (...)` has no impact on `SELECT a, b FROM table1`, but 
`DELETE FROM table1` has.

Now, enter `COUNT(*)`. The sqlite3_set_authorizer callback is told nearly 
nothing about the `SELECT COUNT(*) FROM table1`. Especially not that table1 is 
used. It is only told that the COUNT function is called throuh SQLITE_FUNCTION. 
That's all. That is more than nothing, because one can deduce from a call to 
the COUNT function that *any* statement can have an impact on `SELECT COUNT(*) 
FROM table1`. For example, `INSERT INTO table2 (...)` will be assumed to have 
an impact on `SELECT COUNT(*) FROM table1`.

Unfortunately, this is less than ideal. I understand the situation: `SELECT 
COUNT(*) FROM table1` does not access values from the table1 table, and thus 
does not need any authorization. But I suggest that sqlite3_set_authorizer is 
so close from giving a full picture of the columns and table read by a 
statement that it's a pity that a simple COUNT(*) is able to ruin the picture.

So here is my feature request: please have the sqlite3_set_authorizer callback 
given the full list of tables and columns used by a statement.

Cheers,
Gwendal Roué
[1] http://github.com/groue/GRDB.swift
[2] https://sqlite.org/c3ref/set_authorizer.html

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

Re: [sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid

[Gwendal Roué]

* Florian Weimer <f...@deneb.enyo.de> wrote:

> * Gwendal Roué:
> 
>> I have found a regression in SQLite 3.17.0. In the following SQL statements:
>> 
>>CREATE VIRTUAL TABLE t1 USING FTS5(content);
>>INSERT INTO t1(content) VALUES ('some text');
>>SELECT last_insert_rowid(); // 10 (wrong)
>>SELECT rowid FROM t1;   // 1
>> 
>> The expected value of the the SQL function last_insert_rowid()
>> function is 1, not 10. Same for the C function
>> sqlite3_last_insert_rowid().
> 
> I think this is a known issue.

I am not sure this is a known issue: I don't find it in the tickets list 
(http://www.sqlite.org/src/reportlist).

> SQLite 3.18 adds a
> sqlite3_set_last_insert_rowid() function and uses it in “the new
> interface in the FTS3, FTS4, and FTS5 extensions to ensure that the
> sqlite3_last_insert_rowid() interface always returns reasonable
> values”.

The pending SQLite 3.18 indeed looks like it addresses this issue!

Gwendal Roué


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

[sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid

[Gwendal Roué]

Hello,

I have found a regression in SQLite 3.17.0. In the following SQL statements:

CREATE VIRTUAL TABLE t1 USING FTS5(content);
INSERT INTO t1(content) VALUES ('some text');
SELECT last_insert_rowid(); // 10 (wrong)
SELECT rowid FROM t1;   // 1

The expected value of the the SQL function last_insert_rowid() function is 1, 
not 10. Same for the C function sqlite3_last_insert_rowid().

This bug was not present in 3.16.2.

This bug is very similar to http://www.sqlite.org/src/tktview?name=13137dccf3, 
which affected FTS3.

Cheers,
Gwendal Roué

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

> Le 8 déc. 2014 à 17:21, Simon Slavin  a écrit :
> 
>> Why not an opt-in way to ask for deferred constraint checking. The key here 
>> is only to allow perfectly legit requests to run. With all the due respect 
>> to sqlite implementors and the wonderful design of sqlite.
> 
> SQL-99 includes a syntax for deferred checking.  We don't need to invent our 
> own syntax with a PRAGMA. However, it is done when the constraint is defined 
> rather than being something one can turn on or off.  So you would need to 
> think out whether you wanted row- or transaction-based checking when you 
> define each constraint in the first place.

Hi Simon,

This topic is fascinating. Googling for SQL-99 deferred checking, I stumbled 
upon this page which shows how deferred index maintenance affects Oracle query 
plan, and performance : 
https://alexanderanokhin.wordpress.com/deferred-index-maintenance/.

I now understand that the strategy for checking index constraints is tied to 
their maintenance.

The `UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 
1` query we are talking about has indeed to perform both. Such an 
innocuous-looking request, and it sends us right into the very guts of 
relational constraints :-)

Gwendal

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

Yes, that would be nice.

For example, sqlite already needs explicit opt-in for some of the relational 
toolkit. I think about "PRAGMA foreign_keys = ON".

Why not an opt-in way to ask for deferred constraint checking. The key here is 
only to allow perfectly legit requests to run. With all the due respect to 
sqlite implementors and the wonderful design of sqlite.

> Le 8 déc. 2014 à 15:55, Nico Williams <n...@cryptonector.com> a écrit :
> 
> Ideally there would be something like DEFERRED foreign key checking
> for uniqueness constraints...  You can get something like that by
> using non-unique indexes (but there would also go your primary keys)
> and then check that there are no duplicates before you COMMIT.  (Doing
> this reliably would require something like transaction triggers, which
> IIRC exists in a "sessions" branch.)
> 
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
>> Le 8 déc. 2014 à 10:55, Gwendal Roué <g...@pierlis.com> a écrit :
>> 
>> Hi,
>> 
>> Unique indexes make some valid update queries fail.
>> 
>> Please find below the SQL queries that lead to the unexpected error:
>> 
>> -- The `books` and `pages` tables implement a book with several pages.
>> -- Page ordering is implemented via the `position` column in the pages table.
>> -- A unique index makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE 
>> CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we 
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position are 
>> not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1;
>> 
>> The query should run without any error, since it does not break the unique 
>> index.
>> 
>> Thank you for considering this issue.
>> 
>> Cheers,
>> Gwendal Roué
>> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

> Le 8 déc. 2014 à 15:18, John McKown  a écrit :
> 
> On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen 
> wrote:
> 
>> I am like you, Gwendal, in that I don't like that behavior in SQLite;
>> however, not liking it doesn't make it a bug.
>> 
> 
> ​On another of my forums, this is called a BAD - Broken, As Designed.​ As
> opposed to the normal WAD - Working As Designed.

Thanks RSmith, Marc and John. I can live with this :-)


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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

> Le 8 déc. 2014 à 14:48, RSmith <rsm...@rsweb.co.za> a écrit :
> 
> 
> On 2014/12/08 11:55, Gwendal Roué wrote:
>> Hi,
>> 
>> Unique indexes make some valid update queries fail.
>> 
>> Please find below the SQL queries that lead to the unexpected error:
>> 
>> -- The `books` and `pages` tables implement a book with several pages.
>> -- Page ordering is implemented via the `position` column in the pages table.
>> -- A unique index makes sure two pages do not share the same position.
>> CREATE TABLE books (
>> id INT PRIMARY KEY
>> )
>> CREATE TABLE pages (
>> book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON 
>> UPDATE CASCADE,
>> position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we 
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position are 
>> not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1;
> 
> NOT a bug...  the moment you SET position to position +1 for the first 
> iteration of the query, it tries to make that entry look like (0,2) and there 
> is of course at this point in time already an entry like (0,2).
> 
> Some engines allow you to defer the constraint checking until the end of the 
> transaction (and you can do this for References, though you are cascading 
> which is fine). In SQLite the check is immediate and will fail for the 
> duplication attempted on the first iteration. The fact that the other record 
> will eventually be changed to no longer cause a fail is irrelevant to the 
> engine in a non-deferred checking.
> 
> Now that we have established it isn't a bug,

I'm new to this mailing list, and I won't try to push my opinion, which is : 
yes this is a bug, and this bug could be fixed without introducing any 
regression (since fixing it would cause failing code to suddenly run, and this 
has never been a compatibility issue).

Thank you all for your support and explanations. The root cause has been found, 
and lies in the constraint checking algorithm of sqlite. I have been able to 
find a work around that is good enough for me.

Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer 
who his not attached to the constraint-checking algorithm fixes it.

Have a nice day,
Gwendal Roué

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

> Le 8 déc. 2014 à 14:39, Simon Slavin <slav...@bigfraud.org> a écrit :
> 
> On 8 Dec 2014, at 1:31pm, Gwendal Roué <g...@pierlis.com> wrote:
> 
>> We share the same conclusion. I even tried to decorate the update query with 
>> "ORDER" clauses, in a foolish attempt to reverse the ordering of row 
>> updates, and circumvent the issue.
> 
> A way to solve this is to use REAL for page numbers instead of INTEGER.  To 
> insert a page between two existing ones, give it a number which is the mean 
> of the two pages you're inserting it between.  Every so often you can run a 
> maintenance routine which renumbers all pages to integers.
> 
> Alternatively, store your pages as a linked list.

Polluting my database schema around such a bug is not an option for me, as long 
as I can find a work around that is good enough and leaves my intent intact. 
The one I chose involves destroying the unique index before running the failing 
update query, and then recreating it.

All I look for is this issue to enter the ticket list of sqlite at 
http://www.sqlite.org/src/reportlist, so that this fantastic embeddable 
database gets better.

Gwendal Roué

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

J T,

I did provide a sequence of queries that reliably reproduce the issue (see 
below, from the first CREATE to the last UPDATE). There is no trigger involved, 
as far as I know. Forgive me but I don't see how I could use your advice.

My work around has been to destroy the unique index, and then re-create it 
after the update. This solution is good enough as my table is not that big, and 
the "pure" code path remains intact, with only two inserted statements that are 
easily described and commented.

Gwendal Roué

> Le 8 déc. 2014 à 14:24, J T <drenho...@aol.com> a écrit :
> 
> Try having your cascade occur before the row is created, updated or deleted.
> 
> http://www.sqlite.org/lang_createtrigger.html
> 
> 
> 
> 
> 
> 
> 
> -Original Message-
> From: Richard Hipp <d...@sqlite.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Mon, Dec 8, 2014 8:14 am
> Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
> fail
> 
> 
> On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué <g...@pierlis.com> wrote:
> 
>> Hi,
>> 
>> Unique indexes make some valid update queries fail.
>> 
>> Please find below the SQL queries that lead to the unexpected error:
>> 
>> -- The `books` and `pages` tables implement a book with several pages.
>> -- Page ordering is implemented via the `position` column in the pages
>> table.
>> -- A unique index makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position
>> are not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
>> 1;
>> 
>> The query should run without any error, since it does not break the unique
>> index.
>> 
> 
> Uniqueness is checked for each row change, not just at the end of the
> transaction.  Hence, uniqueness might fail, depending on the order in which
> the individual rows are updated.
> 
> 
>> 
>> Thank you for considering this issue.
>> 
>> Cheers,
>> Gwendal Roué
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

Thanks J T. Let's give a look.

> Le 8 déc. 2014 à 14:24, J T <drenho...@aol.com> a écrit :
> 
> Try having your cascade occur before the row is created, updated or deleted.
> 
> http://www.sqlite.org/lang_createtrigger.html
> 
> 
> 
> 
> 
> 
> 
> -Original Message-
> From: Richard Hipp <d...@sqlite.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Mon, Dec 8, 2014 8:14 am
> Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
> fail
> 
> 
> On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué <g...@pierlis.com> wrote:
> 
>> Hi,
>> 
>> Unique indexes make some valid update queries fail.
>> 
>> Please find below the SQL queries that lead to the unexpected error:
>> 
>> -- The `books` and `pages` tables implement a book with several pages.
>> -- Page ordering is implemented via the `position` column in the pages
>> table.
>> -- A unique index makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position
>> are not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
>> 1;
>> 
>> The query should run without any error, since it does not break the unique
>> index.
>> 
> 
> Uniqueness is checked for each row change, not just at the end of the
> transaction.  Hence, uniqueness might fail, depending on the order in which
> the individual rows are updated.
> 
> 
>> 
>> Thank you for considering this issue.
>> 
>> Cheers,
>> Gwendal Roué
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

> Le 8 déc. 2014 à 14:14, Richard Hipp <d...@sqlite.org> a écrit :
> 
> On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué <g...@pierlis.com> wrote:
> 
>> Hi,
>> 
>> Unique indexes make some valid update queries fail.
>> 
>> Please find below the SQL queries that lead to the unexpected error:
>> 
>> -- The `books` and `pages` tables implement a book with several pages.
>> -- Page ordering is implemented via the `position` column in the pages
>> table.
>> -- A unique index makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position
>> are not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
>> 1;
>> 
>> The query should run without any error, since it does not break the unique
>> index.
>> 
> 
> Uniqueness is checked for each row change, not just at the end of the
> transaction.  Hence, uniqueness might fail, depending on the order in which
> the individual rows are updated.

Thank you Richard for your answer.

We share the same conclusion. I even tried to decorate the update query with 
"ORDER" clauses, in a foolish attempt to reverse the ordering of row updates, 
and circumvent the issue.

Our analysis describes an implementation detail. Still, this behavior can not 
be considered as normal, and closed as "behaves as expected". I still believe 
that my initial mail is an actual bug report and should be treated as such.

I hope it will find an interested ear. I'm unfortunately not familiar enough 
with the sqlite guts to fix it myself - especially considering the root cause. 
Messing with relational constraints validation is not an easy task.

Regards,
Gwendal Roué
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite bugreport : unique index causes valid updates to fail

[Gwendal Roué]

Hi,

Unique indexes make some valid update queries fail.

Please find below the SQL queries that lead to the unexpected error:

-- The `books` and `pages` tables implement a book with several pages.
-- Page ordering is implemented via the `position` column in the pages table.
-- A unique index makes sure two pages do not share the same position.
CREATE TABLE books (
id INT PRIMARY KEY
)
CREATE TABLE pages (
book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE 
CASCADE,
position INT
)
CREATE UNIQUE INDEX pagination ON pages(book_id, position)

-- Let's populate the tables with a single book and three pages:
INSERT INTO books VALUES (0);
INSERT INTO pages VALUES (0,0);
INSERT INTO pages VALUES (0,1);
INSERT INTO pages VALUES (0,2);

-- We want to insert a page between the pages at positions 0 and 1. So we have
-- to increment the positions of all pages after page 1.
-- Unfortunately, this query yields an error: "columns book_id, position are 
not unique"/

UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1;

The query should run without any error, since it does not break the unique 
index.

Thank you for considering this issue.

Cheers,
Gwendal Roué

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