Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?
On Tue, 7 May 2013 18:54:48 +0200
Hick Gunter wrote:
> The problem with checking the ID of the last inserted row is that the
> abstraction for checking it cannot reasonably know that a query is an
> insert;
>
> Essentially doing a string match for "INSERT " in the query executed
> would be a leaky abstraction.
Ah. When you said "leak", I thought you meant memory leak.
I have to tell you I don't have much sympathy for the "problem" you
propose to solve. I don't understand the purpose of an application
that
1. issues a query not knowing whether it's an INSERT, and
2. needs to know the row id of the inserted row
I can tell you for sure that
> "INSERT INTO b (a) VALUES (c)" => the ID of the inserted row
won't work, because more than one row might have been inserted.
More important, though: the problem you're trying to solve is created
by the way you're going about it. Let's look at the SQL.
> CREATE TABLE t1 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d
> TEXT );
That table has no natural key. It has an id column declared as a key,
but the user-provided data -- column d -- is indistinguishably
duplicated. If the user provides "a" three times, and the DBMS generates
meaningless numbers to distinguish them. At most, the user knows he has
three a's, and that the computer -- for reasons of its own -- has
assigned them numbers.
The table also presents the application programmer with problems beyond
those you're seeking to address, e.g.
1. How to delete one of the 'a' rows? Just pick the first one?
2. How to change one of the 'a' rows to 'b'? Pick one?
3. How to limit the number of 'a' rows? A trigger?
Now consider a better design:
CREATE TABLE t1 (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
q INTEGER,
d TEXT,
unique(d)
);
where column q is the count of d, and id remains an auto-generated
number (for your sake, perhaps for efficiency).
To retrieve the id (please observe the use of single quotes):
insert into t1 (q,d) values (1,'a'), (1,'b');
select id, d from t1 where d in ('a', 'b');
You will note that the three questions I posed are also easily
answered.
That is how the problem you propose to solve is conventionally
solved. Of course, it presupposes a natural key and that the
application retains the values it inserted for which it wants the
DBMS-generated ids. In other words, it uses the system according to
its strengths, as it was designed to be used.
In short, the whole problem of discovering the generated row id
disappears with judicious (one might say "correct") use of the DBMS. It
definitely does not warrant an API change.
Orthogonal to the row-id issue is the "what kind of query is it?"
question. It's easy to imagine uses for information from the parser.
A general solution would be an API function to return the tokenized
query; the caller could search the tree for the operators/keywords of
interest.
--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?
On Tue, May 7, 2013 at 7:14 AM, Mikael wrote: > A generalized way of getting if an ID was inserted on the last performed > query and if so which, is of value for instance when inserting a row into a > table with an AUTOINCREMENT key e.g. This can get tricky if you have BEFORE INSERT triggers that insert rows in other tables and then RAISE(IGNORE). Of if the table is a VIEW with an INSTEAD OF INSERT trigger that does something similar. A last rowid function might... return nothing (inserts/updates done in trigger bodies not exposed) or it might show you the last rowid touched by the trigger -- either way it's not what you'd want. If the one INSERT turns into any number of inserts (or updates! or deletes!) other than 1, then what? For me the best thing to do here is to just do your INSERTs and UPDATEs and then SELECT to find the results. If you must then do a SELECT after each INSERT/UPDATE to find out what you got. This works whether there's triggers or not. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On Mon, May 6, 2013 at 9:39 PM, Keith Medcalf wrote: >> I read this: >> >> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; >> >> as saying "select all a from t1 that are not in t2, *then* order that >> by that a". > > No, that is not what it says, actually. The translation of your statement > into SQL Declarative would be: Certainly if you look at SQL books and the SQLite docs you get the impression that the ordering term applies to the final result set. There's no point ordering, say, correlated sub-queries. Some RDBMSes seem to allow it, though I'm not sure what that means if anything outside the context of group_concat()-like aggregate functions -- perhaps it works only as a hint to the query planner that ordering an intermediate result set will net better performance? In any case, SQLite3 only allows ORDER BY at the very end of a select, not in sub-selects. And it applies the ORDER BY to the result set. It seems outrageous to think that a COLLATE clause on ORDER BY should be applied transitively to anything other than ordering the final set unless the result is guaranteed to be the same. At the very least it's weird and surprising. There *is* a point to ordering sets for aggregate function computation, because some aggregate functions are produce different results for the same input set in different orders. E.g., group_concat(). But this is a different story. Outside of such aggregate functions there's no point to ordering anything but the final result set. > select a from t1 where not exists (select * from t2 where a = t1.a) order by > a; You can rewrite EXCEPT that way. That doesn't mean that the first statement is not equivalent to this one; on the contrary, you can rewrite that way because the result is equivalent to the first. And this might well be logical proof that there's bug here! But I'm probably stretching something... Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slowness with FTS Queries and non-FTS tables
On 05/08/2013 01:54 AM, Scott Robison wrote:
On Tue, 30 Apr 2013 23:56:35 -0700 (PDT)
Paul Vercellotti wrote:
We've got some trouble with FTS4 queries taking too long in which
we're looking for a subset of matching records in the FTS table, as
narrowed by a non-FTS table.
{details snipped}
I've recently had to work through a similar issue. My worst case searching
performance wasn't as bad as you're describing but was not "optimal" for
our needs, but I had other problems. One was the size of my full text
index, which could include millions of rows of email message bodies and
various textual header lines. "Pure" inserts into the index weren't too
bad, but anytime the FTS b-tree structures got "too big" and needed to be
merged, sqlite could enter a really slow state allocating tons of memory
during the merge when a single inserted row triggered the behavior. By
"really slow state" I mean in excess of twenty minutes processing after
allocating a single chunk of memory hundreds of megabytes large. When the
full text index grew large enough, it would eventually fail to merge when
it could not allocate a sufficiently large block of data (in excess of 200
megabytes, likely due to a highly fragmented heap; the nature of the
application and the team that has developed it means that the heap is
fragmented, and I can't do much to reduce that fragmentation, even though
otherwise plenty of memory is available, without re-writing significant
other pieces of code; I want to do that eventually, but it is not an option
at the moment).
Another way to go is to use the 'merge=X,Y' and 'automerge=0' commands:
http://www.sqlite.org/fts3.html#mergecmd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update statement
Hi Peter, thanks. But it does not seem to work in my environment. Probably somewhere a bug on my side. Hartwig Am 07.05.2013 um 19:55 schrieb Peter Aronson: > At least for SQLite it appears to be. From the Update doc page > (http://www.sqlite.org/lang_update.html): > > "The modifications made to each row affected by an UPDATE statement are > determined by the list of assignments following the SET keyword. Each > assignment > specifies a column name to the left of the equals sign and a scalar > expression > to the right. For each affected row, the named columns are set to the values > found by evaluating the corresponding scalar expressions. If a single > column-name appears more than once in the list of assignment expressions, all > but the rightmost occurrence is ignored. Columns that do not appear in the > list > of assignments are left unmodified. The scalar expressions may refer to > columns > of the row being updated. In this case all scalar expressions are evaluated > before any assignments are made." > > And in fact I rely on it behaving this way for in one place in my SQLite code > and it seems to be working correctly. > > Peter > > - Original Message >> From: skywind mailing lists >> To: General Discussion of SQLite Database >> Sent: Tue, May 7, 2013 10:46:42 AM >> Subject: Re: [sqlite] Update statement >> >> Hi, >> >> my question is: is it guaranteed that it works? >> >> Regards, >> Hartwig >> >> Am 07.05.2013 um 03:24 schrieb James K. Lowden: >> >>> On Mon, 6 May 2013 23:53:40 +0100 >>> Simon Slavin wrote: >>> > How do I create this kind of update statement? > > UPDATE T SET a=0.5*(a+b), b=0.5*(b-a); > > The RHS should always be used with the values of a and b before the > assignment. > > I think that the result of this kind of statement is undefined, or? No need to worry, it will work the way you want it to work: The row is read. The new values are calculated. The new values are written to the database. >>> ... That was a terrible description. >>> >>> Actually that's not a bad approximation of what happens. Here's a >>> simpler example: >>> >>> sqlite> create table t(a int, b int); >>> sqlite> insert into t values (1,2); >>> sqlite> select * from t; >>> a b >>> -- -- >>> 1 2 >>> sqlite> update t set a=b, b=a; -- Et Voila! >>> sqlite> select * from t; >>> a b >>> -- -- >>> 2 1 >>> >>> There is no "RHS". The syntax and semantics of SQL are its own; they >>> cannot be extrapolated from other languages. >> >> I know but everybody knows what I meant, or? And its a quite brief > description. >> >>> >>> --jkl >>> ___ >>> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?
Hi Günter,
Great, yes that makes sense!
So that solves it.
I was thinking about this and thought there must be some way to do this
already; thank you so much for pointing this out.
This would also open up for picking up several inserted id:s from one query
which is nice.
Thanks,
Mikael
2013/5/7 Hick Gunter
> How about calling sqlite3_update_hook()?
>
> ** **
>
> You will be notified about all UPDATE, INSERT and DELETE operations as
> they occur, with the database, table and rowid affected and in the thread
> executing the statement.
>
> ** **
>
> If you register a well-behaved function (i.e. that calls the previously
> registered function) these hooks may even be layered…
>
> ** **
>
> *Von:* Mikael [mailto:mikael.tr...@gmail.com]
> *Gesendet:* Dienstag, 07. Mai 2013 14:15
> *An:* Richard Hipp; Simon Slavin; Hick Gunter; James K. Lowden; General
> Discussion of SQLite Database
> *Betreff:* Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or
> functionalequivalent?
>
> ** **
>
> Hi,
>
> ** **
>
> Thanks for your feedback, James.
>
> ** **
>
> A generalized way of getting if an ID was inserted on the last performed
> query and if so which, is of value for instance when inserting a row into a
> table with an AUTOINCREMENT key e.g.
>
> ** **
>
> CREATE TABLE t (
>
> id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>
> d TEXT
>
> );
>
> ** **
>
> and then inserting into it, e.g.
>
> ** **
>
> "INSERT INTO t (d) VALUES (\"a\");" => inserted id: 1
>
> "INSERT INTO t (d) VALUES (\"a\");" => inserted id: 2
>
> "INSERT INTO t (d) VALUES (\"b\");" => inserted id: 3
>
> "INSERT INTO t (d) VALUES (\"b\");" => inserted id: 4
>
> "INSERT INTO t (d) VALUES (\"c\");" => inserted id: 5
>
> ** **
>
> This functionality makes a lot of sense in at least certain SQLite
> bindings. In the places where it's not strictly required, it will still
> provide clarity which is valuable in debug, and convenience from being
> dynamic, please see the example use ~15 rows below for this -
>
> ** **
>
> ** **
>
> So what I'm looking for is a general mechanism for deriving the ID
> inserted for a query. E.g.:
>
> ** **
>
> "SELECT a FROM b" => none
>
> ** **
>
> "UPDATE b SET a = c" => none
>
> ** **
>
> "INSERT IGNORE INTO b (a) VALUES (c)" and the ID was already in there =>
> none
>
> ** **
>
> "INSERT IGNORE INTO b (a) VALUES (c)" and the ID was not already in there
> => the ID of the inserted row
>
> ** **
>
> "INSERT INTO b (a) VALUES (c)" => the ID of the inserted row
>
> ** **
>
> ** **
>
> Thanks to this, a SQLite binding can be achieved which
> dynamically/*automatically* detects not only if it was a readonly or
> non-readonly query, but also within the non-readonly query if it was an
> update or insert, and thus a generalized query mechanism can be devised
> that behaves like:
>
> ** **
>
> SELECT query => The query results
>
> UPDATE query => The number of rows updated
>
> INSERT query => The ID of the row inserted, or none (by null/false/0/etc.)
>
>
> ** **
>
> ** **
>
> This is to create a general abstraction for this purpose. Indeed SQLite
> performs queries, well, at the level of query and, the meaningful work you
> do with SQLite is at the granularity of a query, so it appears reasonable
> to me that there would be a mechanism using which you could retrieve the ID
> of the row inserted by a particular query.
>
> ** **
>
> A generalized "rows changed by this query" abstraction can be implemented
> as of today, using sqlite3_changes which returns the number of changed rows
> in the *last performed query*, the error reporting (as to check
> sqlite3_changes was set on the last query) and possibly secondarily
> by stmt_readonly .
>
> ** **
>
> ** **
>
> The problem with checking the ID of the last inserted row is that the
> abstraction for checking it cannot reasonably know that a query is an
> insert;
>
> ** **
>
> Essentially doing a string match for "INSERT " in the query executed would
> be a leaky abstraction.
>
> ** **
>
> Getting a copy of SQLite's SQL parser's data could be something, but then
> - this is a bit beyond my current SQLite skills - aren't there mechanisms
> to make INSERT:s indirectly in SQLite, like through a database-stored
> procedure or alike?
>
> ** **
>
> That would make any abstraction like this leak, because it wouldn't know
> if an INSERT was made during a query, and that knowledge is required as to
> know if sqlite3_last_insert_rowid regards the last inserted ID or not.
>
> ** **
>
> ** **
>
> ** **
>
> And to illustrate how sqlite3_changes and sqlite3_last_insert_rowid does
> not deliver for automatic query ID retrieval:
>
> ** **
>
> Using these two, let's define an attempt at a general mechanism for this,
> in pseudocode:
>
> ** **
>
> var last_rowId_on_last_query = 0;
>
> function query(query) {
>
> lock_mutex();
[sqlite] Who can explain the xCodec API?
I'm trying to implement a encryption VFS, but it turned out it's too tedious and error prone. The main cause is that my simple VFS does not handle locking well (or not at all). This leads to database file corruption when inserting thousands of records (I believe). Then I turned to the commercial SEE (http://www.hwaci.com/sw/sqlite/see.html). But it does not mention how it is implemented. I searched the Internet and it seems that sqlite.c source file has this macro: SQLITE_HAS_CODEC But there is no documentation on this API. I need enlightenment. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update statement
On 7 May 2013, at 8:04pm, Luuk wrote: > http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt > chapter: 13.9 > 6) The s are effectively evaluated before updat- >ing the object row. If a contains a reference >to a column of T, then the reference is to the value of that >column in the object row before any value of the object row is >updated. Thank you Luuk. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update statement
On 07-05-2013 19:46, skywind mailing lists wrote: Hi, my question is: is it guaranteed that it works? Regards, Hartwig http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt chapter: 13.9 6) The s are effectively evaluated before updat- ing the object row. If a contains a reference to a column of T, then the reference is to the value of that column in the object row before any value of the object row is updated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slowness with FTS Queries and non-FTS tables
On Tue, 30 Apr 2013 23:56:35 -0700 (PDT)
Paul Vercellotti wrote:
> We've got some trouble with FTS4 queries taking too long in which
> we're looking for a subset of matching records in the FTS table, as
> narrowed by a non-FTS table.
> {details snipped}
I've recently had to work through a similar issue. My worst case searching
performance wasn't as bad as you're describing but was not "optimal" for
our needs, but I had other problems. One was the size of my full text
index, which could include millions of rows of email message bodies and
various textual header lines. "Pure" inserts into the index weren't too
bad, but anytime the FTS b-tree structures got "too big" and needed to be
merged, sqlite could enter a really slow state allocating tons of memory
during the merge when a single inserted row triggered the behavior. By
"really slow state" I mean in excess of twenty minutes processing after
allocating a single chunk of memory hundreds of megabytes large. When the
full text index grew large enough, it would eventually fail to merge when
it could not allocate a sufficiently large block of data (in excess of 200
megabytes, likely due to a highly fragmented heap; the nature of the
application and the team that has developed it means that the heap is
fragmented, and I can't do much to reduce that fragmentation, even though
otherwise plenty of memory is available, without re-writing significant
other pieces of code; I want to do that eventually, but it is not an option
at the moment).
It occurred to me that because I needed to only do a full text search on a
subset of the full text index (which is not natively possible because of
the nature of the FTS subsystem) that an "ideal" workaround would be to
create a separate full text index for each "sectionID" of my other table.
So instead of just having a single ftstable, I would have "ftstable_1",
"ftstable_2", ... "ftstable_x" where x was my maximum sectionID. I tried
that solution and it unfortunately does not scale. While there is not a
limit on the number of tables you can have (beyond the maximum size of the
database, I assume), adding new tables to a database really gets slow as
the count of tables increases. After several thousand tables have been
added (realizing that a single virtual FTS table results in 3 to 5 physical
shadow tables being created as well), it is visibly slower to continue
adding more tables. I have to assume (though that is dangerous) that
preparing statements to interact with those tables is also going to be
slower, but just the lack of speed creating tables was enough for me to
avoid that solution, so I never tested how fast it was to interact with
that large a set of tables. In my test, I was working with a "schema" that
needed so support approximately 3 sectionIDs, and that number varies
with the exact nature of the users data.
After some more thinking on the subject, I realized that I didn't
necessarily need to have one full text index per sectionID. It would be
sufficient for my needs to split my full text index into individual
buckets. I took my sectionID modulus some number, and used that remainder
as part of the name of an ftstable that only includes a subset of all full
text index data. The right number for you to use is going to depend on the
nature of your data, but for my purposes I used 53. It is a prime number
that tends to avoid any weird clumping of data that would cause grossly
imbalanced full text index buckets, and instead of tens of thousands of
tables in the database, I only wind up with several hundred. This approach
kept the size of the individual full text indexes down to a more manageable
level and gave me the following advantages:
1. Each single full text index is only roughly 2% of the size of the
original monolithic full text index, so inserts that trigger merging
behavior never exhibit the original worst case performance. This means that
I could support far more messages (theoretically) before suffering failure
due to heap fragmentation (probably), but with the current sizes of
datasets that I need to work with there is no problem.
2. Matching search terms in the full text index is now faster. It is still
not optimal because while I only need the matches for a single sectionID, I
will get matches for multiple sections due to the bucket approach. Since
the average query will return about 2% of the result set size as before,
performance is greatly enhanced.
One final note: for our purposes, we wound up moving our full text indexes
out into a separate database. That is not strictly necessary, but it helped
improve other performance issues related to database contention by allowing
one thread to write to the original database that included the sectionID
table portions, and another thread to build the full text index buckets. By
minimizing write contention between the two threads, we were able to insert
more data per second and decrease the time to build the SQLite databases.
That may or may not be us
Re: [sqlite] Update statement
At least for SQLite it appears to be. From the Update doc page (http://www.sqlite.org/lang_update.html): "The modifications made to each row affected by an UPDATE statement are determined by the list of assignments following the SET keyword. Each assignment specifies a column name to the left of the equals sign and a scalar expression to the right. For each affected row, the named columns are set to the values found by evaluating the corresponding scalar expressions. If a single column-name appears more than once in the list of assignment expressions, all but the rightmost occurrence is ignored. Columns that do not appear in the list of assignments are left unmodified. The scalar expressions may refer to columns of the row being updated. In this case all scalar expressions are evaluated before any assignments are made." And in fact I rely on it behaving this way for in one place in my SQLite code and it seems to be working correctly. Peter - Original Message > From: skywind mailing lists > To: General Discussion of SQLite Database > Sent: Tue, May 7, 2013 10:46:42 AM > Subject: Re: [sqlite] Update statement > > Hi, > > my question is: is it guaranteed that it works? > > Regards, > Hartwig > > Am 07.05.2013 um 03:24 schrieb James K. Lowden: > > > On Mon, 6 May 2013 23:53:40 +0100 > > Simon Slavin wrote: > > > >>> How do I create this kind of update statement? > >>> > >>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a); > >>> > >>> The RHS should always be used with the values of a and b before the > >>> assignment. > >>> > >>> I think that the result of this kind of statement is undefined, or? > >> > >> No need to worry, it will work the way you want it to work: > >> > >> The row is read. > >> The new values are calculated. > >> The new values are written to the database. > > ... > >> That was a terrible description. > > > > Actually that's not a bad approximation of what happens. Here's a > > simpler example: > > > > sqlite> create table t(a int, b int); > > sqlite> insert into t values (1,2); > > sqlite> select * from t; > > a b > > -- -- > > 1 2 > > sqlite> update t set a=b, b=a; -- Et Voila! > > sqlite> select * from t; > > a b > > -- -- > > 2 1 > > > > There is no "RHS". The syntax and semantics of SQL are its own; they > > cannot be extrapolated from other languages. > > I know but everybody knows what I meant, or? And its a quite brief description. > > > > > --jkl > > ___ > > 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] Update statement
Hi, my question is: is it guaranteed that it works? Regards, Hartwig Am 07.05.2013 um 03:24 schrieb James K. Lowden: > On Mon, 6 May 2013 23:53:40 +0100 > Simon Slavin wrote: > >>> How do I create this kind of update statement? >>> >>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a); >>> >>> The RHS should always be used with the values of a and b before the >>> assignment. >>> >>> I think that the result of this kind of statement is undefined, or? >> >> No need to worry, it will work the way you want it to work: >> >> The row is read. >> The new values are calculated. >> The new values are written to the database. > ... >> That was a terrible description. > > Actually that's not a bad approximation of what happens. Here's a > simpler example: > > sqlite> create table t(a int, b int); > sqlite> insert into t values (1,2); > sqlite> select * from t; > a b > -- -- > 1 2 > sqlite> update t set a=b, b=a; -- Et Voila! > sqlite> select * from t; > a b > -- -- > 2 1 > > There is no "RHS". The syntax and semantics of SQL are its own; they > cannot be extrapolated from other languages. I know but everybody knows what I meant, or? And its a quite brief description. > > --jkl > ___ > 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] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?
How about calling sqlite3_update_hook()?
You will be notified about all UPDATE, INSERT and DELETE operations as they
occur, with the database, table and rowid affected and in the thread executing
the statement.
If you register a well-behaved function (i.e. that calls the previously
registered function) these hooks may even be layered...
Von: Mikael [mailto:mikael.tr...@gmail.com]
Gesendet: Dienstag, 07. Mai 2013 14:15
An: Richard Hipp; Simon Slavin; Hick Gunter; James K. Lowden; General
Discussion of SQLite Database
Betreff: Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or
functionalequivalent?
Hi,
Thanks for your feedback, James.
A generalized way of getting if an ID was inserted on the last performed query
and if so which, is of value for instance when inserting a row into a table
with an AUTOINCREMENT key e.g.
CREATE TABLE t (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
d TEXT
);
and then inserting into it, e.g.
"INSERT INTO t (d) VALUES (\"a\");" => inserted id: 1
"INSERT INTO t (d) VALUES (\"a\");" => inserted id: 2
"INSERT INTO t (d) VALUES (\"b\");" => inserted id: 3
"INSERT INTO t (d) VALUES (\"b\");" => inserted id: 4
"INSERT INTO t (d) VALUES (\"c\");" => inserted id: 5
This functionality makes a lot of sense in at least certain SQLite bindings. In
the places where it's not strictly required, it will still provide clarity
which is valuable in debug, and convenience from being dynamic, please see the
example use ~15 rows below for this -
So what I'm looking for is a general mechanism for deriving the ID inserted for
a query. E.g.:
"SELECT a FROM b" => none
"UPDATE b SET a = c" => none
"INSERT IGNORE INTO b (a) VALUES (c)" and the ID was already in there => none
"INSERT IGNORE INTO b (a) VALUES (c)" and the ID was not already in there =>
the ID of the inserted row
"INSERT INTO b (a) VALUES (c)" => the ID of the inserted row
Thanks to this, a SQLite binding can be achieved which
dynamically/*automatically* detects not only if it was a readonly or
non-readonly query, but also within the non-readonly query if it was an update
or insert, and thus a generalized query mechanism can be devised that behaves
like:
SELECT query => The query results
UPDATE query => The number of rows updated
INSERT query => The ID of the row inserted, or none (by null/false/0/etc.)
This is to create a general abstraction for this purpose. Indeed SQLite
performs queries, well, at the level of query and, the meaningful work you do
with SQLite is at the granularity of a query, so it appears reasonable to me
that there would be a mechanism using which you could retrieve the ID of the
row inserted by a particular query.
A generalized "rows changed by this query" abstraction can be implemented as of
today, using sqlite3_changes which returns the number of changed rows in the
*last performed query*, the error reporting (as to check sqlite3_changes was
set on the last query) and possibly secondarily by stmt_readonly .
The problem with checking the ID of the last inserted row is that the
abstraction for checking it cannot reasonably know that a query is an insert;
Essentially doing a string match for "INSERT " in the query executed would be a
leaky abstraction.
Getting a copy of SQLite's SQL parser's data could be something, but then -
this is a bit beyond my current SQLite skills - aren't there mechanisms to make
INSERT:s indirectly in SQLite, like through a database-stored procedure or
alike?
That would make any abstraction like this leak, because it wouldn't know if an
INSERT was made during a query, and that knowledge is required as to know if
sqlite3_last_insert_rowid regards the last inserted ID or not.
And to illustrate how sqlite3_changes and sqlite3_last_insert_rowid does not
deliver for automatic query ID retrieval:
Using these two, let's define an attempt at a general mechanism for this, in
pseudocode:
var last_rowId_on_last_query = 0;
function query(query) {
lock_mutex();
sqlite_perform_query(query);
var last_rowId = sqlite_last_rowid();
var id_of_this_row = (last_rowId != last_rowId_on_last_query) ? last_rowId
: null;
last_rowId_on_last_query = last_rowId;
unlock_mutex();
return id_of_this_row;
}
Now, let's set up these two tables
CREATE TABLE t1 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT );
CREATE TABLE t2 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT );
and now make some inserts with it! The use of query() above is implied here.
"INSERT INTO t1 (d) VALUES (\"a\");" => 1- Correct!
"INSERT INTO t1 (d) VALUES (\"a\");" => 2- Correct!
"INSERT INTO t2 (d) VALUES (\"a\");" => 1- Correct!
"INSERT INTO t2 (d) VALUES (\"a\");" => 2- Correct!
"INSERT INTO t2 (d) VALUES (\"a\");" => 3- Correct!
"INSERT INTO t1 (d) VALUES (\"a\");" => null - Error, abstraction leak, ouch!
All of this leakiness would be undone if before a query, the SQLite user could
reset the s
Re: [sqlite] Is there a way to select a precision?
OK...this way then...it's stored correctly and the view should display correctly in a 3rd party app. Correctly gives the 99 cent discount. Should work for any 2 decimal place number. create table mine(a number(10,2)); create view v1 as select a/100.0 as a from mine; create trigger t1 after insert on mine for each row begin update mine set a = cast(round(new.a*100) as integer) where rowid=new.rowid; end; create trigger t2 after update on mine for each row begin update mine set a=cast(round(a*100) as integer) where typeof(a)!='integer' and rowid=new.rowid; end; insert into mine values(1.0/3.0); select * from mine; select a*3 from v1; .99 update mine set a=1/3.0; select a*3 from v1; .99 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Monday, May 06, 2013 8:03 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there a way to select a precision? On Mon, 6 May 2013 07:42:43 -0500 "Michael Black" wrote: > Yes...other databases do respect NUMBER(10,2) on SELECT's. ... > Can you simply use round to do what you want? > CREATE TABLE stuff(f number(10,2)); > INSERT INTO "stuff" VALUES(1.0/3.0); > sqlite> select f from stuff; > 0.333 > sqlite> select round(f,2) from stuff; > 0.33 To be clear, Paul van Helden isn't talking about SELECT. He's talking about INSERT, about not storing more precision that the input actually represents. Apply a 33% discount to a $3.00 purchase. Is the bill $2.00, or $2.01? If you say $2.00, then apply a 33% discount to three $1 purchases in three separate months. I imagine you'd agree the total discount is just 99 cents. Whether or not SQLite ought to support exact decimal types is a separate question; I don't think anyone is saying it should. But it isn't just a matter of presentation. --jkl ___ 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] Security concern with new fts3tokenize virtual table
On 05/07/2013 05:17 PM, Ralf Junker wrote: The new fts3tokenize virtual table calls the fts3_tokenizer() ... However, this call is rejected if, for security reasons, So the problem is that with the authorizer block in place, the fts3tokneize virtual table does not work. One would have to compromise functionality for security. Is there no other way to retrieve the tokenizer besides calling fts3_tokenizer()? Good point. Changed it to avoid fts3_tokenizer(). Thanks. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?
Hi,
Thanks for your feedback, James.
A generalized way of getting if an ID was inserted on the last performed
query and if so which, is of value for instance when inserting a row into a
table with an AUTOINCREMENT key e.g.
CREATE TABLE t (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
d TEXT
);
and then inserting into it, e.g.
"INSERT INTO t (d) VALUES (\"a\");" => inserted id: 1
"INSERT INTO t (d) VALUES (\"a\");" => inserted id: 2
"INSERT INTO t (d) VALUES (\"b\");" => inserted id: 3
"INSERT INTO t (d) VALUES (\"b\");" => inserted id: 4
"INSERT INTO t (d) VALUES (\"c\");" => inserted id: 5
This functionality makes a lot of sense in at least certain SQLite
bindings. In the places where it's not strictly required, it will still
provide clarity which is valuable in debug, and convenience from being
dynamic, please see the example use ~15 rows below for this -
So what I'm looking for is a general mechanism for deriving the ID inserted
for a query. E.g.:
"SELECT a FROM b" => none
"UPDATE b SET a = c" => none
"INSERT IGNORE INTO b (a) VALUES (c)" and the ID was already in there =>
none
"INSERT IGNORE INTO b (a) VALUES (c)" and the ID was not already in there
=> the ID of the inserted row
"INSERT INTO b (a) VALUES (c)" => the ID of the inserted row
Thanks to this, a SQLite binding can be achieved which
dynamically/*automatically* detects not only if it was a readonly or
non-readonly query, but also within the non-readonly query if it was an
update or insert, and thus a generalized query mechanism can be devised
that behaves like:
SELECT query => The query results
UPDATE query => The number of rows updated
INSERT query => The ID of the row inserted, or none (by null/false/0/etc.)
This is to create a general abstraction for this purpose. Indeed SQLite
performs queries, well, at the level of query and, the meaningful work you
do with SQLite is at the granularity of a query, so it appears reasonable
to me that there would be a mechanism using which you could retrieve the ID
of the row inserted by a particular query.
A generalized "rows changed by this query" abstraction can be implemented
as of today, using sqlite3_changes which returns the number of changed rows
in the *last performed query*, the error reporting (as to check
sqlite3_changes was set on the last query) and possibly secondarily
by stmt_readonly .
The problem with checking the ID of the last inserted row is that the
abstraction for checking it cannot reasonably know that a query is an
insert;
Essentially doing a string match for "INSERT " in the query executed would
be a leaky abstraction.
Getting a copy of SQLite's SQL parser's data could be something, but then -
this is a bit beyond my current SQLite skills - aren't there mechanisms to
make INSERT:s indirectly in SQLite, like through a database-stored
procedure or alike?
That would make any abstraction like this leak, because it wouldn't know if
an INSERT was made during a query, and that knowledge is required as to
know if sqlite3_last_insert_rowid regards the last inserted ID or not.
And to illustrate how sqlite3_changes and sqlite3_last_insert_rowid does
not deliver for automatic query ID retrieval:
Using these two, let's define an attempt at a general mechanism for this,
in pseudocode:
var last_rowId_on_last_query = 0;
function query(query) {
lock_mutex();
sqlite_perform_query(query);
var last_rowId = sqlite_last_rowid();
var id_of_this_row = (last_rowId != last_rowId_on_last_query) ?
last_rowId : null;
last_rowId_on_last_query = last_rowId;
unlock_mutex();
return id_of_this_row;
}
Now, let's set up these two tables
CREATE TABLE t1 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT );
CREATE TABLE t2 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT );
and now make some inserts with it! The use of query() above is implied here.
"INSERT INTO t1 (d) VALUES (\"a\");" => 1- Correct!
"INSERT INTO t1 (d) VALUES (\"a\");" => 2- Correct!
"INSERT INTO t2 (d) VALUES (\"a\");" => 1- Correct!
"INSERT INTO t2 (d) VALUES (\"a\");" => 2- Correct!
"INSERT INTO t2 (d) VALUES (\"a\");" => 3- Correct!
"INSERT INTO t1 (d) VALUES (\"a\");" => null - Error, abstraction leak,
ouch!
All of this leakiness would be undone if before a query, the SQLite user
could reset the sqlite3_last_insert_rowid . By that, the user can just
check the sqlite3_last_insert_rowid right after the query, and by that know
that that was .
It may be a bit too simplistic if a query inserted several ID:s as you
pointed out, though at least it's general unlike the present solution, and
can be made to fit any usecase at least I have in reach, as in SQLite any
INSERT operation can be performed one row per query with OK-to-great
performance.
Introduction of a sqlite3_reset_last_insert_rowid would be complimenting
the current sqlite_int64 sqlite3_last_insert_rowid at sqlite3.c row
114023-114028:
/*
** Return the ROWID of the mos
Re: [sqlite] Very important performance regression due to totally different query plan
On Mon, May 6, 2013 at 3:15 PM, Stephane MANKOWSKI wrote: > Hi, > > I am the main developer of Skrooge (application using sqlite) and since my > upgrade to ubuntu 13.04, I have very bad performances due to sqlite. > Have you considered including source code (the sqlite3.c and sqlite3.h files) for the version of SQLite that works for you in our source tree and statically linking it, so that OS upgrades do not cause problems like this? -- 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] Very important performance regression due to totally different query plan
Hi,
I am the main developer of Skrooge (application using sqlite) and since
my upgrade to ubuntu 13.04, I have very bad performances due to sqlite.
Here is my analysis:
All tests are done with the file
http://skrooge.org/files/skrooge_bug_performance.sqlite
*=*
*ON UBUNTU QUANTAL:sqlite version 3.7.13 2012-06-11 02:05:22
f5b5a13f7394dc143aa136f1d4faba6839eaa6dc*
sqlite> SELECT
ABS(TOTAL(f_REALCURRENTAMOUNT))>=400,ABS(TOTAL(f_REALCURRENTAMOUNT))
FROM v_operation_consolidated WHERE d_date!='-00-00' AND
((STRFTIME('%Y-%m',d_date)=STRFTIME('%Y-%m',date('now','start of
month','-1 month';
1|2704.0
CPU Time: user *0.18* sys 0.012000 (*GOOD PERFORMANCES*)
sqlite> SELECT * FROM v_category_display;
...
CPU Time: user 0.812000 sys 0.016000
sqlite> explain query plan SELECT * FROM v_category_display;
sele order from deta
-
1 0 4 SCAN TABLE suboperation USING INDEX
idx_suboperation_category_id (~7341 rows)
1 1 0 SEARCH TABLE operation USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
1 2 1 SEARCH TABLE account USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
1 3 2 SEARCH TABLE bank USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
1 4 3 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
1 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 2
2 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
2 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 3
3 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
3 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 4
4 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
2 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 5
5 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
5 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 6
6 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
6 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 7
7 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
0 0 0 SCAN TABLE category (~78 rows)
0 1 1 SEARCH SUBQUERY 1 AS t USING AUTOMATIC
COVERING INDEX (i_IDCATEGORY=?) (~3 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 8
8 0 1 SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
8 1 0 SEARCH TABLE operation AS o USING INTEGER
PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 9
100 4 SCAN TABLE suboperation USING INDEX
idx_suboperation_category_id (~7341 rows)
101 0 SEARCH TABLE operation USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
102 1 SEARCH TABLE account USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
103 2 SEARCH TABLE bank USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
104 3 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
100 0 EXECUTE CORRELATED SCALAR SUBQUERY 11
110 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
110 0 EXECUTE CORRELATED SCALAR SUBQUERY 12
120 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
120 0 EXECUTE CORRELATED SCALAR SUBQUERY 13
130 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
110 0 EXECUTE CORRELATED SCALAR SUBQUERY 14
140 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
140 0 EXECUTE CORRELATED SCALAR SUBQUERY 15
150 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
150 0 EXECUTE CORRELATED SCALAR SUBQUERY 16
160 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
9 0 0 SCAN TABLE category (~39 rows)
9 1 1 SEARCH SUBQUERY 10 AS t USING AUTOMATIC
COVERING INDEX (i_IDCATEGORY=?) (~3 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 17
170 1 SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
171 0 SEARCH TABLE operation AS o USING I
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
I just wish to confirm that the workaround shown by Nico works fine for me which makes the issue low priority from my point of view. Thanks all. Staffan On Tue, May 7, 2013 at 5:00 AM, Keith Medcalf wrote: > > So you are saying that: > > Select a from t1 except select a from t2 order by a collate nocase; > > Should internally be computed as > > Select a from (select a from t1 except select a from t2) order by a > collate nocase; > > ? > > > > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org > > > > -Original Message- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Richard Hipp > > Sent: Monday, 06 May, 2013 20:46 > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE > > > > On Mon, May 6, 2013 at 10:29 PM, Simon Slavin > > wrote: > > > > > > > > The problem, I think, is that a COLLATE shouldn't change any value > > anyhow, > > > no matter which SELECT the ORDER clause is attached to. The COLLATE > > > modifier is part of the ORDER BY clause. It is there to change the > > ORDER > > > that the values are returned in, not the values themselves. > > > > > > And, indeed, that is exactly what COLLATE is doing. > > > > The problem is this: When SQLite sees the ORDER BY clause on the EXCEPT > > it > > tries to do the EXCEPT using a merge. In other words, it computes two > > subqueries: (SELECT .. FROM x ORDER BY ...) and (SELECT ... FROM y ORDER > > BY ...). Then it looks at the output of these subqueries, row by row. > > > > (1) x > (2) x>y: pop y > > (3) x=y: pop and discard both x and y > > > > You can implement INTERSECT, UNION, and UNION ALL in much the same way, > by > > supplying different actions for each of the above cases. > > > > The above works great (and is very efficient) if the collating sequence > of > > the ORDER BY is the same as the natural collating sequence of the output > > columns. If it isn't, then the above code gives the wrong answer. The > > basic problem is that SQLite is not recognizing that the collating > > sequences are different and is trying to use the algorithm above when it > > it > > shouldn't. > > > > This was an oversight when I first implemented the merging algorithm 5 > > years ago. It didn't occur to me then (and apparently hasn't occurred to > > anybody else in the last 5 years) that the collating sequence in the > ORDER > > BY might be different from the natural collating sequence of the result > > columns. > > > > Unfortunately, the merge algorithm outlined above is the only means > SQLite > > currently has for doing a compound select that contains an ORDER BY. In > > order to fix this, I'm going to have to come up with a whole new > > algorithm, > > just for this case. ON the other hand, since nobody has noticed it in 5 > > years, presumably it doesn't come up that often, so there isn't a huge > > rush > > to get the fix in. So I'm going to take my time and try to come up with > > the minimally disruptive fix. > > > > > > > > > And something like > > > > > > SELECT x EXCEPT y > > > > > > is subtracting one set from another, and in sets the order doesn't > > matter. > > > The problem is something like doing > > > > > > SELECT words FROM dictionary ORDER BY words COLLATE NOCASE > > > > > > and getting all the words back as capital letters. This shouldn't > > happen. > > > > > > Simon. > > > ___ > > > 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
[sqlite] Security concern with new fts3tokenize virtual table
The new fts3tokenize virtual table calls the fts3_tokenizer() SQL function internally to retrieve the pointer to the tokenizer implementation for a FTS table: http://www.sqlite.org/src/artifact/a29f126b9e6c6a6f1021a8f7440bf125e68af1f9?ln=74-100 However, this call is rejected if, for security reasons, the fts3_tokenizer() function is blocked by an authorizer callback: "SECURITY WARNING: If the fts3/4 extension is used in an environment where potentially malicious users may execute arbitrary SQL, they should be prevented from invoking the fts3_tokenizer() function, possibly using the authorization callback." (http://www.sqlite.org/fts3.html#section_8_1). So the problem is that with the authorizer block in place, the fts3tokneize virtual table does not work. One would have to compromise functionality for security. Is there no other way to retrieve the tokenizer besides calling fts3_tokenizer()? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
