Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
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
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) xy: 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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
> 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: select a from t1 where not exists (select * from t2 where a = t1.a) order by a; which, when a collation is applied in the order by, applies only to the ordering of the result set and not to the computation of the result set. --- () 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 Nico Williams > Sent: Monday, 06 May, 2013 19:36 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE > > On Mon, May 6, 2013 at 4:22 PM, Keith Medcalf wrote: > > It is difficult to determine what is correct behaviour. I would think > that the "order by" clause applies to the set operation, therefore any > collation being applied has to be applied also to the component sets > before the operation is performed. This implies the current operation is > correct although it may lead to, perhaps, non-intuitive results. > > 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". > > I read this: > > SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; > > to mean "select a from t1 that are not in t2, *then* order that by a > with the nocase collation". > > I don't understand why the COLLATE clause on the ordering term should > be applied transitively to the sub-expressions in the select instead > of only to the result set of the select. It makes no sense, > intuitively, and smells like a bug. But what does the standard say? > > Note that there's a workaround: > > SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2) ORDER BY > a COLLATE nocase; > > Nico > -- > ___ > 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] Suspected bug in SELECT EXCEPT ORDER COLLATE
On 7 May 2013, at 2:35am, Nico Williams 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". > > I read this: > >SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; > > to mean "select a from t1 that are not in t2, *then* order that by a > with the nocase collation". > > I don't understand why the COLLATE clause on the ordering term should > be applied transitively to the sub-expressions in the select instead > of only to the result set of the select. 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 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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On Mon, May 6, 2013 at 4:22 PM, Keith Medcalf wrote: > It is difficult to determine what is correct behaviour. I would think that > the "order by" clause applies to the set operation, therefore any collation > being applied has to be applied also to the component sets before the > operation is performed. This implies the current operation is correct > although it may lead to, perhaps, non-intuitive results. 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". I read this: SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; to mean "select a from t1 that are not in t2, *then* order that by a with the nocase collation". I don't understand why the COLLATE clause on the ordering term should be applied transitively to the sub-expressions in the select instead of only to the result set of the select. It makes no sense, intuitively, and smells like a bug. But what does the standard say? Note that there's a workaround: SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2) ORDER BY a COLLATE nocase; Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update statement
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. --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 Mon, 6 May 2013 20:55:01 +0300 Mikael wrote: > Currently in the absence of a sqlite3_reset_last_insert_rowid(), the > closest you can do to figure out what ID was actually inserted by the > most recent SQLite query is: > > * Lock mutex > * Get sqlite*'s last insert row ID > * Perform the query > * Get number of changed rows > * Get sqlite*'s last insert row ID > * Unlock mutex I don't understand. The process doing the insert presumably knows which data it inserted. Why not turn around and SELECT the ids for the inserted values? No mutex and no guesswork. Your process seems to imply that the the inserted values don't constitute a key, or that you want to compute the row IDs for performance. Those don't strike me as sufficient reason to perturb the API. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
Re: [sqlite] Update statement
On 6 May 2013, at 11:53pm, Simon Slavin wrote: > On 6 May 2013, at 11:48pm, skywind mailing lists > wrote: > >> 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. Okay, could everyone please forget that one ? That was a terrible description. As far as I know my statement is correct. The values used in the calculations are the values read from the row, not the values SQL is assembling to write back to the row. To make up for my poor explanation please accept this demonstration: sqlite> CREATE TABLE x (a,b); sqlite> INSERT INTO x VALUES (1,100); sqlite> SELECT * FROM x; 1|100 sqlite> UPDATE x SET a=b,b=a; sqlite> SELECT * FROM x; 100|1 sqlite> UPDATE x SET b=a,a=b; sqlite> SELECT * FROM x; 1|100 sqlite> UPDATE x SET a=999,b=a; sqlite> SELECT * FROM x; 999|1 sqlite> UPDATE x SET b=a,a=777; sqlite> SELECT * FROM x; 777|999 sqlite> My understanding is that this is part of the SQL standard, though I can't find it spelled out anywhere right now. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update statement
On 6 May 2013, at 11:48pm, skywind mailing lists 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. This is standard in all SQL implementations. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update statement
Hi, 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? Regards, Hartwig ___ 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
Richard,
sqlite> CREATE TABLE t1(a);
sqlite> INSERT INTO t1 VALUES('abc');
sqlite> INSERT INTO t1 VALUES('def');
sqlite> CREATE TABLE t2(a);
sqlite> INSERT INTO t2 VALUES('DEF');
sqlite>
sqlite> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
abc
def
sqlite> SELECT '-';
-
sqlite> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
abc
sqlite> SELECT '-';
-
sqlite> SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
...> ORDER BY a COLLATE nocase;
abc
def
sqlite> select a from t1 where not exists (select * from t2 where a=t1.a);
abc
def
sqlite> select a from t1 where not exists (select * from t2 where a=t1.a) order
by a collate nocase;
abc
def
sqlite> explain query plan SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a
COLLATE nocase;
SELECT {1:0}
FROM {1,*} = t1
ORDERBY {1:0}.COLLATE(nocase)
EXCEPT
SELECT {0:0}
FROM {0,*} = t2
ORDERBY {0:0}.COLLATE(nocase)
END
sele order from deta
-
1 0 0 SCAN TABLE t1 (~100 rows)
1 0 0 USE TEMP B-TREE FOR ORDER BY
2 0 0 SCAN TABLE t2 (~100 rows)
2 0 0 USE TEMP B-TREE FOR ORDER BY
0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
sqlite> explain query plan select a from t1 where not exists (select * from t2
where a=t1.a) order by a collate nocase;
SELECT {0:0}
FROM {0,*} = t1
WHERE NOT(EXISTS(SELECT {1:0}
FROM {1,*} = t2
WHERE EQ({1:0},{0:0})
LIMIT 1
END))
ORDERBY {0:0}.COLLATE(nocase)
END
sele order from deta
-
0 0 0 SCAN TABLE t1 (~50 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 1
1 0 0 SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (a=?)
(~7 rows)
0 0 0 USE TEMP B-TREE FOR ORDER BY
The plans make is pretty clear what is happening. The order by is being bumped
up to the b-tree's used to perform the EXCEPT operation, while on the
semantically equivalent not exists query, it is not.
Try the same queries using the other set operations (union and intersect).
sqlite> select a from t1 union select a from t2 order by a;
DEF
abc
def
sqlite> select a from t1 union select a from t2 order by a collate nocase;
abc
DEF
sqlite> select a from (select a from t1 union select a from t2) order by a
collate nocase;
abc
DEF
def
sqlite> select a from t1 intersect select a from t2 order by a;
sqlite> select a from t1 intersect select a from t2 order by a collate nocase;
def
sqlite> select a from (select a from t1 intersect select a from t2) order by a
collate nocase;
It is difficult to determine what is correct behaviour. I would think that the
"order by" clause applies to the set operation, therefore any collation being
applied has to be applied also to the component sets before the operation is
performed. This implies the current operation is correct although it may lead
to, perhaps, non-intuitive results.
---
() 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 09:01
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
>
> On Mon, May 6, 2013 at 10:56 AM, Simon Slavin
> wrote:
>
> >
> > Unless you include your collation code, it's hard to reproduce your
> > behaviour. Can you please try to find a demonstration with a collation
> > order that's native to SQLite ? Perhaps a three-row example using
> "COLLATE
> > NOCASE" ?
> >
> >
> You are correct, Simon, that Staffan's message was really just a vague
> hint
> of something wrong. But based on that meager hint, I have been able to
> come up with a suspicious-looking test script using the built-in "nocase"
> collation. To wit:
>
> CREATE TABLE t1(a);
> INSERT INTO t1 VALUES('abc');
> INSERT INTO t1 VALUES('def');
> CREATE TABLE t2(a);
> INSERT INTO t2 VALUES('DEF');
>
> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
> SELECT '-';
> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
> SELECT '-';
> SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
> ORDER BY a COLLATE nocase;
>
> --
> 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
Re: [sqlite] Is there a way to select a precision?
You could also store the precision in the datatype and the client can retrieve the column declaration so that it can fiddle with the data before displaying it. This is a client issue, not a database issue. --- () 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 ven...@intouchmi.com > Sent: Monday, 06 May, 2013 06:20 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Is there a way to select a precision? > > You could always store the precision info in another column or two. > > Vance > > on May 06, 2013, Paul van Helden wrote: > > > >> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that > >> SQLite will attempt to store (string) values as integers first and > floats > >> second before giving up and storing strings. > >> > > > >This has nothing to do with my reply and I understand how it works. > > > >> > >> You do realize that there are decimal numbers that have infinite binary > >> expansions? > >> > > > >I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know > >they are the same in SQLite). > > > >> > >> You are also talking presentation (as in formatting) of numeric values > as > >> opposed to representation (as in storing/retrieving). The former is > best > >> handled in the user interface while the latter is the subject of > database > >> engines. > >> > > > >My point was about not storing binary junk - the part of a number that > has > >no meaning because the accuracy of the inputs is limited. When you have a > >generic db manager that can show any table or if you are looking at the > >results of your own SQL statement, it helps to reduce clutter on the > >screen. The data also compresses better. > > > >> > >> Fatihful reproduction of formatting would be possible using TEXT > affinity > >> and calling sqlite3_bind_text. Performing arithmetic with these > "numbers" > >> would however be tricky, slow and would still not guarantee that > calculated > >> values would conform to the desired formatting. > >> > >> Of course, but in most cases we don't need to go this far. My main > point > >is about rounding before binding; my secondary point that scale in a > column > >definition can be desirable to avoid it. > >___ > >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?
2013/5/6 Mikael > Dear Simon, Günter and list, > > The issue goes like this: > > Currently in the absence of a sqlite3_reset_last_insert_rowid(), the > closest you can do to figure out what ID was actually inserted by the most > recent SQLite query is: > > * Lock mutex > > * Get sqlite*'s last insert row ID > > * Perform the query > > * Get number of changed rows > > * Get sqlite*'s last insert row ID > > * Unlock mutex > > Ah, and here you conclude this statement's inserted row ID from: If changed rows is zero or last insert row ID checked before query and after query are the same, then there was no row inserted, so there's no insert it (false/null/etc). Otherwise, use the last insert ID checked last. So this is the test procedure that the following applies to: > But this test leaks crazily much! > > If either of the following holds true, you're in Wild Bugs land! > > * This query was made as an INSERT to one table, and the last query was > made as an INSERT to another table, and both yielded the same ID > > * This query was an UPDATE, so it gave a change count but did not > actually generate a new ID > > > > Probably there's much more cases, though this is enough to show that the > above method is absolutely undesirable and a perpetual source of bugs for > use general method for retrieving a query's insert id. > > And would be resolved by this: > > What do you say about a sqlite3_reset_last_insert_rowid() as to enable > SQLite with this? > > It has a zero performance overhead on present code, and the binary > addition should be in the range 50 bytes. > > > Best regards > Brgds > > 2013/5/6 Hick Gunter > >> I use a separate SQLite connection for each thread to avoid such issues. >> >> When does the "last inserted rowid" become obsolete? >> In your example, do you mean to imply that the last inserted rowid may be >> retrieved once and once only? >> >> Why not just: >> >> (lock mutex) >> (execute query) >> if sqlite3_changes() ** may still be 0, see below >> then >> rowid = sqlite3_last_insert_rowid() >> else >> rowid = undefined >> (unlock mutex) >> >> What about if the query creates more than 1 row (as in INSERT INTO ... >> SELECT or even multiple VALUES tuples)? Why would you want to know only the >> last rowid and not all of them? >> >> If you need to count changes made by triggers and foreign keys you may >> need to: >> >> (lock mutex) >> Before = sqlite_total_changes >> (execute query) >> After = sqlite_total_changes >> If (Before < After) >> ... >> (unlock mutex) >> >> Also note that INSERT OR IGNORE does not change the last inserted rowid. >> >> I also suspect that INSERT OR REPLACE may very well change the last >> inserted rowid (without tallying sqlite3_changes!!! See documentation). >> >> -Ursprüngliche Nachricht- >> Von: Mikael [mailto:mikael.tr...@gmail.com] >> Gesendet: Montag, 06. Mai 2013 14:40 >> An: Richard Hipp; sqlite-users@sqlite.org >> Betreff: [sqlite] How do sqlite3_reset_last_insert_rowid() or functional >> equivalent? >> >> Dear list, >> >> After having made an SQLite statement the ID of a newly inserted row can >> be retrieved with sqlite3_last_insert_rowid . >> >> It'd be a great thing to be able to produce a general mechanism for >> retrieving this value with regard to the most recently performed query only >> right after a query has been made, as programming aimed at getting this >> value lazily won't work as other local code may have made another query to >> the SQLite handle meanwhile. >> >> The most robust way to do this would be through having a >> sqlite3_reset_last_insert_rowid() procedure to invoke right before a query, >> because, sqlite3_last_insert_rowid is only updated on a successful insert. >> >> The code to check if a query is non-readonly can be done using >> sqlite3_stmt_readonly , but then the step from there to check if it's an >> *insert* and not only that but a successful insert, is a huge step and >> possibly the only reliable way to tell this, would be by SQLite telling it, >> and the most straightforward way for it to do this would be through >> sqlite3_last_insert_rowid , so then what about un-problematizing that value >> as to guarantee it won't return any obsolete value, by introducing a >> sqlite3_reset_last_insert_rowid() ? >> >> Please let me know the best practice for solving this particular problem >> - the ability to make a "Query" abstraction atop SQLite, that has its very >> own "ID of row inserted" method, I believe is a reasonable aim. >> >> (I.e., not having such a property but that is not guaranteed to actually >> contain the right thing, depending on very specific circumstances.) >> >> Example: >> >> (mutex for sqlite3 lock) >> sqlite3_reset_last_insert_rowid(sqlite3*); >> (perform SQLite query on sqlite3*) >> sqlite3_last_insert_rowid(sqlite3*) => Guaranteedly returns the rowid of >> any row inserted by the query, or 0 if no insert was done. >> (mutex for sqlite3 unlock) >> [starting here
Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?
Dear Simon, Günter and list, The issue goes like this: Currently in the absence of a sqlite3_reset_last_insert_rowid(), the closest you can do to figure out what ID was actually inserted by the most recent SQLite query is: * Lock mutex * Get sqlite*'s last insert row ID * Perform the query * Get number of changed rows * Get sqlite*'s last insert row ID * Unlock mutex But this test leaks crazily much! If either of the following holds true, you're in Wild Bugs land! * This query was made as an INSERT to one table, and the last query was made as an INSERT to another table, and both yielded the same ID * This query was an UPDATE, so it gave a change count but did not actually generate a new ID Probably there's much more cases, though this is enough to show that the above method is absolutely undesirable and a perpetual source of bugs for use general method for retrieving a query's insert id. What do you say about a sqlite3_reset_last_insert_rowid() as to enable SQLite with this? It has a zero performance overhead on present code, and the binary addition should be in the range 50 bytes. Best regards 2013/5/6 Hick Gunter > I use a separate SQLite connection for each thread to avoid such issues. > > When does the "last inserted rowid" become obsolete? > In your example, do you mean to imply that the last inserted rowid may be > retrieved once and once only? > > Why not just: > > (lock mutex) > (execute query) > if sqlite3_changes() ** may still be 0, see below > then > rowid = sqlite3_last_insert_rowid() > else > rowid = undefined > (unlock mutex) > > What about if the query creates more than 1 row (as in INSERT INTO ... > SELECT or even multiple VALUES tuples)? Why would you want to know only the > last rowid and not all of them? > > If you need to count changes made by triggers and foreign keys you may > need to: > > (lock mutex) > Before = sqlite_total_changes > (execute query) > After = sqlite_total_changes > If (Before < After) > ... > (unlock mutex) > > Also note that INSERT OR IGNORE does not change the last inserted rowid. > > I also suspect that INSERT OR REPLACE may very well change the last > inserted rowid (without tallying sqlite3_changes!!! See documentation). > > -Ursprüngliche Nachricht- > Von: Mikael [mailto:mikael.tr...@gmail.com] > Gesendet: Montag, 06. Mai 2013 14:40 > An: Richard Hipp; sqlite-users@sqlite.org > Betreff: [sqlite] How do sqlite3_reset_last_insert_rowid() or functional > equivalent? > > Dear list, > > After having made an SQLite statement the ID of a newly inserted row can > be retrieved with sqlite3_last_insert_rowid . > > It'd be a great thing to be able to produce a general mechanism for > retrieving this value with regard to the most recently performed query only > right after a query has been made, as programming aimed at getting this > value lazily won't work as other local code may have made another query to > the SQLite handle meanwhile. > > The most robust way to do this would be through having a > sqlite3_reset_last_insert_rowid() procedure to invoke right before a query, > because, sqlite3_last_insert_rowid is only updated on a successful insert. > > The code to check if a query is non-readonly can be done using > sqlite3_stmt_readonly , but then the step from there to check if it's an > *insert* and not only that but a successful insert, is a huge step and > possibly the only reliable way to tell this, would be by SQLite telling it, > and the most straightforward way for it to do this would be through > sqlite3_last_insert_rowid , so then what about un-problematizing that value > as to guarantee it won't return any obsolete value, by introducing a > sqlite3_reset_last_insert_rowid() ? > > Please let me know the best practice for solving this particular problem - > the ability to make a "Query" abstraction atop SQLite, that has its very > own "ID of row inserted" method, I believe is a reasonable aim. > > (I.e., not having such a property but that is not guaranteed to actually > contain the right thing, depending on very specific circumstances.) > > Example: > > (mutex for sqlite3 lock) > sqlite3_reset_last_insert_rowid(sqlite3*); > (perform SQLite query on sqlite3*) > sqlite3_last_insert_rowid(sqlite3*) => Guaranteedly returns the rowid of > any row inserted by the query, or 0 if no insert was done. > (mutex for sqlite3 unlock) > [starting here sqlite3_last_insert_rowid(sqlite3)'s return value is > undefined] > > > > Thanks, > Mikael > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 – 4, A - 1130 Vienna, Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well als
Re: [sqlite] Anyone can decipher this so i can try to figure out how to debug it? Thanks
It's a lousy error message from some application which ay be using sqlite underneath the covers. Sqlite isn't even mentioned in the error so not sure why you think it's involved. It's getting an error on its own rename code and has nothing to do with sqlite. And it's not telling you what the real error is (poor coding). My guess would be either permissions or the target path does not exist. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch Sent: Monday, May 06, 2013 7:00 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Anyone can decipher this so i can try to figure out how to debug it? Thanks Mike wrote: > May 3 16:29:56 syncd: [ERROR] db-api.cpp:3738 rename from '/volume1/@tmp/jUH4Ti' -> '/volume1/@cloudstation/@sync/repo/d/0/V/.Z' How is db-api.cpp related with SQLite? ___ 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 to read log file format into sqlite database?
On Sun, May 05, 2013 at 01:50:41AM -0700, Newbie89 scratched on the wall: > let say is .txt file 99+% of log files are text files. This tells us nothing useful. Is it an Apache log file? Or some other well-known format that someone might have already written a parser for? Does it have a fixed format? Does it have a fixed number of columns? How many columns does it have? How are the columns differentiated? Commas? White-space? Tabs? What are the types of each column? Integers? Text? Dates? How do you want to map columns to database tables? What is the format of the final table? You need to figure this out, not some magic one function library. All of these questions need to be asked before we even get to the import process, yet you provided none of this information. That makes it very difficult/impossible to help, except to help you ask better questions. This is long, but good: http://www.catb.org/esr/faqs/smart-questions.html In my own dealings with log files and SQLite, I usually avoid importing them all together. Importing large files is slow. Most log file searches require scans, due to the type of ad-hoc or summary queries that are typically run against logs. If the file has a well-defined format, I usually just write a virtual table module to scan the log file directly. This is MUCH faster than importing the file, and can be used against "live" log files. Chapter 10 of "Using SQLite" is all about virtual tables: http://shop.oreilly.com/product/9780596521196.do The big example in that chapter is about exposing Apache/httpd format log files the database through a virtual table. Example code can be found here: http://examples.oreilly.com/9780596521196/ -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ 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
Richard's script seems to confirm that something is buggy, so I hold fire
trying to produce something else that only shows the same thing.
Staffan
On Mon, May 6, 2013 at 5:00 PM, Richard Hipp wrote:
> On Mon, May 6, 2013 at 10:56 AM, Simon Slavin
> wrote:
>
> >
> > Unless you include your collation code, it's hard to reproduce your
> > behaviour. Can you please try to find a demonstration with a collation
> > order that's native to SQLite ? Perhaps a three-row example using
> "COLLATE
> > NOCASE" ?
> >
> >
> You are correct, Simon, that Staffan's message was really just a vague hint
> of something wrong. But based on that meager hint, I have been able to
> come up with a suspicious-looking test script using the built-in "nocase"
> collation. To wit:
>
> CREATE TABLE t1(a);
> INSERT INTO t1 VALUES('abc');
> INSERT INTO t1 VALUES('def');
> CREATE TABLE t2(a);
> INSERT INTO t2 VALUES('DEF');
>
> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
> SELECT '-';
> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
> SELECT '-';
> SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
> ORDER BY a COLLATE nocase;
>
> --
> 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
Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
On Mon, May 6, 2013 at 10:56 AM, Simon Slavin wrote:
>
> Unless you include your collation code, it's hard to reproduce your
> behaviour. Can you please try to find a demonstration with a collation
> order that's native to SQLite ? Perhaps a three-row example using "COLLATE
> NOCASE" ?
>
>
You are correct, Simon, that Staffan's message was really just a vague hint
of something wrong. But based on that meager hint, I have been able to
come up with a suspicious-looking test script using the built-in "nocase"
collation. To wit:
CREATE TABLE t1(a);
INSERT INTO t1 VALUES('abc');
INSERT INTO t1 VALUES('def');
CREATE TABLE t2(a);
INSERT INTO t2 VALUES('DEF');
SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
SELECT '-';
SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
SELECT '-';
SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
ORDER BY a COLLATE nocase;
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SEE + CEROD
On 6/5/2013 7:24 PM, Richard Hipp wrote: Yes. SEE and CEROD can be combined to work together. Remember how with CEROD you append some code to the end of the sqlite3.c amalgamation file? SEE works the same way. To use them both, you just append both additions to the amalgamation. Thanks Richard. That makes the decision super simple. Best Regards, Mohit. ___ 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 6 May 2013, at 3:47pm, Staffan Tylen wrote: > I've got a SELECT clause in the following format: > > SELECT a FROM t1 > EXCEPT > SELECT a FROM t2 > ORDER BY a COLLATE myorder; > > All a's in t1 get selected (not expected). > > SELECT a FROM t1 > EXCEPT > SELECT a FROM t2 > ORDER BY a; > > All a's in t1 except the a's in t2 get selected (as expected). > > I haven't experienced any errors using COLLATE myorder in general. > Looks like a bug to me. Unless you include your collation code, it's hard to reproduce your behaviour. Can you please try to find a demonstration with a collation order that's native to SQLite ? Perhaps a three-row example using "COLLATE NOCASE" ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE
I've got a SELECT clause in the following format: SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE myorder; All a's in t1 get selected (not expected). SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; All a's in t1 except the a's in t2 get selected (as expected). I haven't experienced any errors using COLLATE myorder in general. Looks like a bug to me. SQLite 3.7.16.2 Staffan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
On 6 May 2013, at 2:10pm, Simon Slavin wrote: > You can format them after retrieving them from the database, or you can > format them before retrieving them from the database before putting them in > the database, in which case you should consider that you're storing strings, > not numbers. Apologies. That should have read You can format them after retrieving them from the database, or you can format them before putting them in the database, in which case you should consider that you're storing strings, not numbers, and be cautious about using them for calculations, even summing a column. Simon. ___ 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?
I use a separate SQLite connection for each thread to avoid such issues. When does the "last inserted rowid" become obsolete? In your example, do you mean to imply that the last inserted rowid may be retrieved once and once only? Why not just: (lock mutex) (execute query) if sqlite3_changes() ** may still be 0, see below then rowid = sqlite3_last_insert_rowid() else rowid = undefined (unlock mutex) What about if the query creates more than 1 row (as in INSERT INTO ... SELECT or even multiple VALUES tuples)? Why would you want to know only the last rowid and not all of them? If you need to count changes made by triggers and foreign keys you may need to: (lock mutex) Before = sqlite_total_changes (execute query) After = sqlite_total_changes If (Before < After) ... (unlock mutex) Also note that INSERT OR IGNORE does not change the last inserted rowid. I also suspect that INSERT OR REPLACE may very well change the last inserted rowid (without tallying sqlite3_changes!!! See documentation). -Ursprüngliche Nachricht- Von: Mikael [mailto:mikael.tr...@gmail.com] Gesendet: Montag, 06. Mai 2013 14:40 An: Richard Hipp; sqlite-users@sqlite.org Betreff: [sqlite] How do sqlite3_reset_last_insert_rowid() or functional equivalent? Dear list, After having made an SQLite statement the ID of a newly inserted row can be retrieved with sqlite3_last_insert_rowid . It'd be a great thing to be able to produce a general mechanism for retrieving this value with regard to the most recently performed query only right after a query has been made, as programming aimed at getting this value lazily won't work as other local code may have made another query to the SQLite handle meanwhile. The most robust way to do this would be through having a sqlite3_reset_last_insert_rowid() procedure to invoke right before a query, because, sqlite3_last_insert_rowid is only updated on a successful insert. The code to check if a query is non-readonly can be done using sqlite3_stmt_readonly , but then the step from there to check if it's an *insert* and not only that but a successful insert, is a huge step and possibly the only reliable way to tell this, would be by SQLite telling it, and the most straightforward way for it to do this would be through sqlite3_last_insert_rowid , so then what about un-problematizing that value as to guarantee it won't return any obsolete value, by introducing a sqlite3_reset_last_insert_rowid() ? Please let me know the best practice for solving this particular problem - the ability to make a "Query" abstraction atop SQLite, that has its very own "ID of row inserted" method, I believe is a reasonable aim. (I.e., not having such a property but that is not guaranteed to actually contain the right thing, depending on very specific circumstances.) Example: (mutex for sqlite3 lock) sqlite3_reset_last_insert_rowid(sqlite3*); (perform SQLite query on sqlite3*) sqlite3_last_insert_rowid(sqlite3*) => Guaranteedly returns the rowid of any row inserted by the query, or 0 if no insert was done. (mutex for sqlite3 unlock) [starting here sqlite3_last_insert_rowid(sqlite3)'s return value is undefined] Thanks, Mikael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
On 6 May 2013, at 1:14pm, Paul van Helden wrote: > My point was about not storing binary junk - the part of a number that has > no meaning because the accuracy of the inputs is limited. When you have a > generic db manager that can show any table or if you are looking at the > results of your own SQL statement, it helps to reduce clutter on the > screen. You should never be fetching a number from a database and showing it directly on the display. The number needs to be formatted by your software first. Does it need to be justified so a column of numbers lines up ? How should negatives be shown ? Do you want to show the decimal point as a comma or a stop ? Do you use a thousands separator ? Do you need a units symbol ? Formatting numbers for pretty printing is not the job of a database system. The database stores the numbers and recalls them for you. Figuring out how to show them onscreen is the programmer's job. You can format them after retrieving them from the database, or you can format them before retrieving them from the database before putting them in the database, in which case you should consider that you're storing strings, not numbers. > The data also compresses better. Not the way things are done these days. It's extremely rare to do semantic compression of individual values. Almost all compression methods just take the entire file in one go. Simon. ___ 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 functional equivalent?
On 6 May 2013, at 1:39pm, Mikael wrote: > It'd be a great thing to be able to produce a general mechanism for > retrieving this value with regard to the most recently performed query only > right after a query has been made, as programming aimed at getting this > value lazily won't work as other local code may have made another query to > the SQLite handle meanwhile. I believe that if you do both commands before closing a transaction, other threads and processes won't be able to execute anything between them. That's probably your simplest way of coping with the problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
What you are talking about would be feature creep for SQLite. Yes...other databases do respect NUMBER(10,2) on SELECT's. SQLite is "light weight" and does no such magic for you. So it does take an extra step. You'll note that SQLite does provide a GUI for you to play with. If it did it would probably allow you to format columns. This keeps the library small and lightweight. 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 Doesn't work, of course, for generic "table edit" in some GUI. For that you could create a view. sqlite> create view fview as select round(f,2) from stuff; sqlite> select * from fview; -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul van Helden Sent: Monday, May 06, 2013 7:15 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is there a way to select a precision? > A delared type of NUMBER(10,2) has NUMERIC affinity, which means that > SQLite will attempt to store (string) values as integers first and floats > second before giving up and storing strings. > This has nothing to do with my reply and I understand how it works. > > You do realize that there are decimal numbers that have infinite binary > expansions? > I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know they are the same in SQLite). > > You are also talking presentation (as in formatting) of numeric values as > opposed to representation (as in storing/retrieving). The former is best > handled in the user interface while the latter is the subject of database > engines. > My point was about not storing binary junk - the part of a number that has no meaning because the accuracy of the inputs is limited. When you have a generic db manager that can show any table or if you are looking at the results of your own SQL statement, it helps to reduce clutter on the screen. The data also compresses better. > > Fatihful reproduction of formatting would be possible using TEXT affinity > and calling sqlite3_bind_text. Performing arithmetic with these "numbers" > would however be tricky, slow and would still not guarantee that calculated > values would conform to the desired formatting. > > Of course, but in most cases we don't need to go this far. My main point is about rounding before binding; my secondary point that scale in a column definition can be desirable to avoid it. ___ 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] How do sqlite3_reset_last_insert_rowid() or functional equivalent?
Dear list, After having made an SQLite statement the ID of a newly inserted row can be retrieved with sqlite3_last_insert_rowid . It'd be a great thing to be able to produce a general mechanism for retrieving this value with regard to the most recently performed query only right after a query has been made, as programming aimed at getting this value lazily won't work as other local code may have made another query to the SQLite handle meanwhile. The most robust way to do this would be through having a sqlite3_reset_last_insert_rowid() procedure to invoke right before a query, because, sqlite3_last_insert_rowid is only updated on a successful insert. The code to check if a query is non-readonly can be done using sqlite3_stmt_readonly , but then the step from there to check if it's an *insert* and not only that but a successful insert, is a huge step and possibly the only reliable way to tell this, would be by SQLite telling it, and the most straightforward way for it to do this would be through sqlite3_last_insert_rowid , so then what about un-problematizing that value as to guarantee it won't return any obsolete value, by introducing a sqlite3_reset_last_insert_rowid() ? Please let me know the best practice for solving this particular problem - the ability to make a "Query" abstraction atop SQLite, that has its very own "ID of row inserted" method, I believe is a reasonable aim. (I.e., not having such a property but that is not guaranteed to actually contain the right thing, depending on very specific circumstances.) Example: (mutex for sqlite3 lock) sqlite3_reset_last_insert_rowid(sqlite3*); (perform SQLite query on sqlite3*) sqlite3_last_insert_rowid(sqlite3*) => Guaranteedly returns the rowid of any row inserted by the query, or 0 if no insert was done. (mutex for sqlite3 unlock) [starting here sqlite3_last_insert_rowid(sqlite3)'s return value is undefined] Thanks, Mikael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: Random crashes while preparing a statement (Valgrind always complains, reproducing code)
Hi guys,
I found this crash in SQLite. Tested with latest amalgamation (
sqlite-autoconf-307160 ). Please assist.
Thanks,
Jerome
#include
#include
#include
int main()
{
sqlite3_stmt * stmt = NULL;
sqlite3 * db = NULL;
sqlite3_open_v2("test.sqlite", &db, SQLITE_OPEN_READWRITE, NULL);
if(db)
{
printf("Database opened\n");
sqlite3_prepare_v2(db, "UPDATE `Contacts` SET ROWID = ? WHERE
ROWID = ?", -1, &stmt, NULL);
sqlite3_close(db);
}
return 0;
}
--
Simply put create test.sqlite with:
*CREATE TABLE `Contacts`(*
* `Id` INTEGER PRIMARY KEY,*
* `Name` TEXT COLLATE NOCASE,*
* `OfficePhoneNumber` TEXT COLLATE NOCASE,*
* `CellPhoneNumber` TEXT COLLATE NOCASE,*
* `SecondCellPhoneNumber` TEXT COLLATE NOCASE,*
* `PagerNumber` TEXT COLLATE NOCASE,*
* `Email` TEXT COLLATE NOCASE,*
* `Active` INTEGER*
*);*
You'll get this Valgrind output:
==26691== Memcheck, a memory error detector
==26691== Copyright (C) 2002-2012, and GNU GPL'd, by Julian Seward et al.
==26691== Using Valgrind-3.8.1 and LibVEX; rerun with -h for copyright info
==26691== Command: ./a.out
==26691==
Database opened
==26691== *Invalid read of size 8*
==26691==at 0x4EB1896: sqlite3Update (sqlite3.c:101044)
==26691==by 0x4EC1816: yy_reduce (sqlite3.c:111245)
==26691==by 0x4EC3F63: sqlite3Parser (sqlite3.c:112035)
==26691==by 0x4EC4DAD: sqlite3RunParser (sqlite3.c:112872)
==26691==by 0x4EA4B43: sqlite3Prepare (sqlite3.c:94461)
==26691==by 0x4EA4E7F: sqlite3LockAndPrepare (sqlite3.c:94553)
==26691==by 0x4EA5036: sqlite3_prepare_v2 (sqlite3.c:94629)
==26691==by 0x4007E4: main (in /home/jerome/sqlite-autoconf-3071602/a.out)
==26691== Address 0x5906f58 is 0 bytes after a block of size 392 alloc'd
==26691==at 0x4C2C73C: malloc (vg_replace_malloc.c:270)
==26691==by 0x4E4219A: sqlite3MemMalloc (sqlite3.c:15581)
==26691==by 0x4E42BD5: mallocWithAlarm (sqlite3.c:18879)
==26691==by 0x4E42C70: sqlite3Malloc (sqlite3.c:18912)
==26691==by 0x4E4346C: sqlite3DbMallocRaw (sqlite3.c:19248)
==26691==by 0x4E434CF: sqlite3DbRealloc (sqlite3.c:19267)
==26691==by 0x4E8E14E: sqlite3AddColumn (sqlite3.c:81948)
==26691==by 0x4EC04A4: yy_reduce (sqlite3.c:110843)
==26691==by 0x4EC3F63: sqlite3Parser (sqlite3.c:112035)
==26691==by 0x4EC4D17: sqlite3RunParser (sqlite3.c:112860)
==26691==by 0x4EA4B43: sqlite3Prepare (sqlite3.c:94461)
==26691==by 0x4EA4E7F: sqlite3LockAndPrepare (sqlite3.c:94553)
The problematic line is:
*rc = sqlite3AuthCheck(pParse, SQLITE_UPDATE, pTab->zName,
pTab->aCol[j].zName, db->aDb[iDb].zName);*
It looks like an invalid pointer read (4 bytes on 32 bit, 8 bytes on
64) on *pTab->aCol[j].zName*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
You could always store the precision info in another column or two. Vance on May 06, 2013, Paul van Helden wrote: > >> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that >> SQLite will attempt to store (string) values as integers first and floats >> second before giving up and storing strings. >> > >This has nothing to do with my reply and I understand how it works. > >> >> You do realize that there are decimal numbers that have infinite binary >> expansions? >> > >I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know >they are the same in SQLite). > >> >> You are also talking presentation (as in formatting) of numeric values as >> opposed to representation (as in storing/retrieving). The former is best >> handled in the user interface while the latter is the subject of database >> engines. >> > >My point was about not storing binary junk - the part of a number that has >no meaning because the accuracy of the inputs is limited. When you have a >generic db manager that can show any table or if you are looking at the >results of your own SQL statement, it helps to reduce clutter on the >screen. The data also compresses better. > >> >> Fatihful reproduction of formatting would be possible using TEXT affinity >> and calling sqlite3_bind_text. Performing arithmetic with these "numbers" >> would however be tricky, slow and would still not guarantee that calculated >> values would conform to the desired formatting. >> >> Of course, but in most cases we don't need to go this far. My main point >is about rounding before binding; my secondary point that scale in a column >definition can be desirable to avoid it. >___ >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] Is there a way to select a precision?
> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that > SQLite will attempt to store (string) values as integers first and floats > second before giving up and storing strings. > This has nothing to do with my reply and I understand how it works. > > You do realize that there are decimal numbers that have infinite binary > expansions? > I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know they are the same in SQLite). > > You are also talking presentation (as in formatting) of numeric values as > opposed to representation (as in storing/retrieving). The former is best > handled in the user interface while the latter is the subject of database > engines. > My point was about not storing binary junk - the part of a number that has no meaning because the accuracy of the inputs is limited. When you have a generic db manager that can show any table or if you are looking at the results of your own SQL statement, it helps to reduce clutter on the screen. The data also compresses better. > > Fatihful reproduction of formatting would be possible using TEXT affinity > and calling sqlite3_bind_text. Performing arithmetic with these "numbers" > would however be tricky, slow and would still not guarantee that calculated > values would conform to the desired formatting. > > Of course, but in most cases we don't need to go this far. My main point is about rounding before binding; my secondary point that scale in a column definition can be desirable to avoid it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Anyone can decipher this so i can try to figure out how to debug it? Thanks
Mike wrote: > May 3 16:29:56 syncd: [ERROR] db-api.cpp:3738 rename from > '/volume1/@tmp/jUH4Ti' -> '/volume1/@cloudstation/@sync/repo/d/0/V/.Z' How is db-api.cpp related with SQLite? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SEE + CEROD
On Mon, May 6, 2013 at 12:08 AM, Mohit Sindhwani wrote: > Hi Guys, > > We already have a license for CEROD and are now contemplating getting a > license for SEE to use within our products. I notice that both products > are separately provided as amalgamation sqlite3.c files. Is it possible to > use these two together in the same system? Just looking for someone with > experience of this to share whether there are any gotchas that we should be > aware of. > Yes. SEE and CEROD can be combined to work together. Remember how with CEROD you append some code to the end of the sqlite3.c amalgamation file? SEE works the same way. To use them both, you just append both additions to the amalgamation. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
Maybe you should check out http://www.sqlite.org/datatype3.html A delared type of NUMBER(10,2) has NUMERIC affinity, which means that SQLite will attempt to store (string) values as integers first and floats second before giving up and storing strings. You do realize that there are decimal numbers that have infinite binary expansions? You are also talking presentation (as in formatting) of numeric values as opposed to representation (as in storing/retrieving). The former is best handled in the user interface while the latter is the subject of database engines. Fatihful reproduction of formatting would be possible using TEXT affinity and calling sqlite3_bind_text. Performing arithmetic with these "numbers" would however be tricky, slow and would still not guarantee that calculated values would conform to the desired formatting. -Ursprüngliche Nachricht- Von: Paul van Helden [mailto:p...@planetgis.co.za] Gesendet: Montag, 06. Mai 2013 10:05 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Is there a way to select a precision? > > > What do you mean, select precision? The double value you pass to > sqlite3_bind_double() will be used as is. Are you saying you want to > round it first? Then go ahead and do that - I'm not sure what that has > to do with SQLite. > -- > It is an issue with SQLite because the values in NUMBER(10,2) have no effect. Too often I see small values with 15 digits in a table because a double was passed as-is. It is not just about space, it is also about presentation. In engineering we are taught that the number of digits should also tell you the accuracy of the sample, so for example a latitude/longitude obtained from a handheld GPS should be stored with 6 decimal digits (~10cm), the rest is just junk. Since the database does not do this for you, when the programmer knows the accuracy of the sample, he shouldn't be lazy and instead do Round(Longitude*100)/100 before binding. Of course, when the data is presented it should be properly rounded with zeros added at the end or even zeros replacing digits to the left of the decimal (to indicate precision), but my point is you shouldn't store junk digits in the first place. I love it that you don't have to specify TEXT and NUMBER lengths, but would have preferred that SQLite didn't ignore them when specified and that in a NUMBER(p,s) column, the double is stored as an integer internally if p<=18. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to read log file format into sqlite database?
Why don't you try to do yourself and ask the points you stuck? I provided a link which has an example, and there is Sqlite C/C++ Api doc. I don't know the others, but I will not do your job/homework. On 06/05/2013, at 6:02 PM, Newbie89 wrote: > Can you show me a simple tutorial?urgent...please... > Is it the library u create I need to include only can function? > > > > > Fehmi Noyan ISI wrote >> To read txt, use fread() or fgets() . This is the most convenient answer I >> think. >> >> It is up to your programming skills to read the file line by line and >> parse each line according to your needs. >> >> Here is an example >> >> https://github.com/fnoyanisi/sqlite3_capi_extensions >> >> On 05/05/2013, at 6:20 PM, Newbie89 < > >> sh_tan89@ > >> > wrote: >> >>> let say is .txt file >>> >>> >>> >>> -- >>> View this message in context: >>> http://sqlite.1065341.n5.nabble.com/How-to-read-log-file-format-into-sqlite-database-tp68676p68678.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> ___ >>> sqlite-users mailing list > >> sqlite-users@ > >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list > >> sqlite-users@ > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/How-to-read-log-file-format-into-sqlite-database-tp68676p68686.html > Sent from the SQLite mailing list archive at Nabble.com. > ___ > 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 to read log file format into sqlite database?
On Mon, 6 May 2013 01:32:18 -0700 (PDT)
Newbie89 wrote:
> Can you show me a simple tutorial?urgent...please...
> Is it the library u create I need to include only can function?
http://www.cprogramming.com/
You should run this code
while(!understand()){
yourself = Read(SourceCode, Books, Documentation);
yourself = TrytoUse(SourceCode, Snippets);
yourself = Learn(&yourself, C);
}
Where yourself is a pointer to you.
Sorry for being so rude, your questions are not about sqlite, but about C basic
programming.
If someone else has a better answer, it will welcome.
--- ---
Eduardo Morras
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to read log file format into sqlite database?
Can you show me a simple tutorial?urgent...please... Is it the library u create I need to include only can function? Fehmi Noyan ISI wrote > To read txt, use fread() or fgets() . This is the most convenient answer I > think. > > It is up to your programming skills to read the file line by line and > parse each line according to your needs. > > Here is an example > > https://github.com/fnoyanisi/sqlite3_capi_extensions > > On 05/05/2013, at 6:20 PM, Newbie89 < > sh_tan89@ > > wrote: > >> let say is .txt file >> >> >> >> -- >> View this message in context: >> http://sqlite.1065341.n5.nabble.com/How-to-read-log-file-format-into-sqlite-database-tp68676p68678.html >> Sent from the SQLite mailing list archive at Nabble.com. >> ___ >> sqlite-users mailing list >> > sqlite-users@ >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@ > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-read-log-file-format-into-sqlite-database-tp68676p68686.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
> > > What do you mean, select precision? The double value you pass to > sqlite3_bind_double() will be used as is. Are you saying you want to round > it first? Then go ahead and do that - I'm not sure what that has to do with > SQLite. > -- > It is an issue with SQLite because the values in NUMBER(10,2) have no effect. Too often I see small values with 15 digits in a table because a double was passed as-is. It is not just about space, it is also about presentation. In engineering we are taught that the number of digits should also tell you the accuracy of the sample, so for example a latitude/longitude obtained from a handheld GPS should be stored with 6 decimal digits (~10cm), the rest is just junk. Since the database does not do this for you, when the programmer knows the accuracy of the sample, he shouldn't be lazy and instead do Round(Longitude*100)/100 before binding. Of course, when the data is presented it should be properly rounded with zeros added at the end or even zeros replacing digits to the left of the decimal (to indicate precision), but my point is you shouldn't store junk digits in the first place. I love it that you don't have to specify TEXT and NUMBER lengths, but would have preferred that SQLite didn't ignore them when specified and that in a NUMBER(p,s) column, the double is stored as an integer internally if p<=18. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
