Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[Peter Aronson]

On 6/14/2017 5:42 AM, R Smith wrote:


On 2017/06/14 7:08 AM, Wout Mertens wrote:

Is there a way to specify the starting rowid when using autoincrement?

Or should I insert and then remove a row with the id set to one less than
the desired id?


This is quite easy, but first it is helpful to understand the mechanism 
by which SQLite keeps track of the Primary Key Auto-Inc value.


If you define a primary key that is of type INT, and omit the 
AUTOINCREMENT directive, then you will still have a primary key that 
increments if you don't specify the value directly by virtue of primary 
keys being UNIQUE and requires a value, so it's safe to bet if you 
adding a key without specifying the value for it, you intend for it to 
be automatic.
BUT, the next increment value depends on the DB engine guessing what it 
should be based on existing key values (which can cause re-used keys 
that used to exist for now-deleted items).


If you do define the AUTOINCREMENT directive, then SQLite promises to 
always increment the value by one from the last time a value was 
inserted - whether that value has been deleted or changed etc. - i.e. it 
promises to never re-use a key. It achieves this by keeping a table, 
namely the "sqlite_sequence" system-generated table, with references to 
each table using AUTOINCREMENT and its Key based on the last value used 
for the referred table.


You can simply change the values in this reference table to inform the 
next AUTOINCREMENTed value you would like for the specific table-name.


I'm not sure now if references inside this sqlite_sequence table exists 
the moment you create a table with an AUTOINCREMENT key, or only once 
you insert for the first time, but it is easy to check and handle both 
ways.


Taking all this into account, that is why (as another post suggested) 
the equivalent in SQLite for other SQL DB's:

ALTER TABLE myTable AUTOINCREMENT = 5;

would be something like:
UPDATE sqlite_sequence SET seq = 5 WHERE name = 'myTable';


Good luck!
Ryan

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


It is worth noting that there is no entry for a table with an 
autoincrement column in sqlite_sequence until that table has at least 
one row, so you need to perform an INSERT instead of an UPDATE (I don't 
know if sqlite_sequence has a unique constraint on name, so I don't know 
if REPLACE would work for both cases).


sqlite> create table t1 (c1 integer primary key autoincrement,c2 text);

sqlite> select * from sqlite_sequence;


sqlite> insert into t1 (c2) values ('stuff');

sqlite> select * from sqlite_sequence;

t1|1

sqlite>

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

[sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[wout.mertens]

Thank you so much all! This mailinglist is amazing :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[R Smith]

On 2017/06/14 7:08 AM, Wout Mertens wrote:

Is there a way to specify the starting rowid when using autoincrement?

Or should I insert and then remove a row with the id set to one less than
the desired id?


This is quite easy, but first it is helpful to understand the mechanism 
by which SQLite keeps track of the Primary Key Auto-Inc value.


If you define a primary key that is of type INT, and omit the 
AUTOINCREMENT directive, then you will still have a primary key that 
increments if you don't specify the value directly by virtue of primary 
keys being UNIQUE and requires a value, so it's safe to bet if you 
adding a key without specifying the value for it, you intend for it to 
be automatic.
BUT, the next increment value depends on the DB engine guessing what it 
should be based on existing key values (which can cause re-used keys 
that used to exist for now-deleted items).


If you do define the AUTOINCREMENT directive, then SQLite promises to 
always increment the value by one from the last time a value was 
inserted - whether that value has been deleted or changed etc. - i.e. it 
promises to never re-use a key. It achieves this by keeping a table, 
namely the "sqlite_sequence" system-generated table, with references to 
each table using AUTOINCREMENT and its Key based on the last value used 
for the referred table.


You can simply change the values in this reference table to inform the 
next AUTOINCREMENTed value you would like for the specific table-name.


I'm not sure now if references inside this sqlite_sequence table exists 
the moment you create a table with an AUTOINCREMENT key, or only once 
you insert for the first time, but it is easy to check and handle both ways.


Taking all this into account, that is why (as another post suggested) 
the equivalent in SQLite for other SQL DB's:

ALTER TABLE myTable AUTOINCREMENT = 5;

would be something like:
UPDATE sqlite_sequence SET seq = 5 WHERE name = 'myTable';


Good luck!
Ryan

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

Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[J Decker]

from https://sqlite.org/autoinc.html

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly
given a value, then it will be filled automatically with an unused integer,
usually one more than the largest ROWID currently in use. This is true
regardless of whether or not the AUTOINCREMENT keyword is used.

conversely, if it IS specified then it will be used.

https://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite

suggests that UPDATE SQLITE_SEQUENCE SET seq =  WHERE name = ''
will work to initialize the value before any inserts are done.

On Tue, Jun 13, 2017 at 10:08 PM, Wout Mertens 
wrote:

> Is there a way to specify the starting rowid when using autoincrement?
>
> Or should I insert and then remove a row with the id set to one less than
> the desired id?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

[Wout Mertens]

Is there a way to specify the starting rowid when using autoincrement?

Or should I insert and then remove a row with the id set to one less than
the desired id?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[David Burgess]

> But we have to preserve backwards compatibility - even with bugs
> like this.

​How about a new release? i.e. sqlite4
No backward compatibilty issues.​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Jun 12, 2017 8:26 PM, "Keith Medcalf"  wrote:


Additionally, declaring NOT NULL or NULL is ignored.  CHECK constraints are
honoured.  DEFAULT values are ignored.

so CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000) DEFAULT (-1));
&  CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000));
is CREATE TABLE x(id INTEGER PRIMARY KEY CHECK(id>1000));

This is because an "INTEGER PRIMARY KEY" has a computed default that you
cannot override, and the rowid must always be always NOT NULL ...


Thanks for the info. I'm assuming that the order of constraints is
unimportant so check could come before PK.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Jun 12, 2017 5:43 PM, "Richard Hipp"  wrote:

On 6/13/17, Scott Robison  wrote:
>
> Is it fair to say that the rowid aliasing behavior does not require
> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> that order as the "type") as long as the type is INTEGER and the
> constraint PRIMARY KEY appears somewhere in the column's constraint
> list?

See https://www.sqlite.org/lang_createtable.html#rowid

   CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Columns x *not* a ROWID.  Life is complicated.  Sorry for the mess.
But we have to preserve backwards compatibility - even with bugs like
this.


One,  no problem.

Two, is "INTEGER PRIMARY KEY ASC" the same? I will try it tomorrow.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Keith Medcalf]

Additionally, declaring NOT NULL or NULL is ignored.  CHECK constraints are 
honoured.  DEFAULT values are ignored.

so CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000) DEFAULT (-1));
&  CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000));
is CREATE TABLE x(id INTEGER PRIMARY KEY CHECK(id>1000));

This is because an "INTEGER PRIMARY KEY" has a computed default that you cannot 
override, and the rowid must always be always NOT NULL ...

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Richard Hipp
> Sent: Monday, 12 June, 2017 17:44
> To: SQLite mailing list
> Subject: Re: [sqlite] INTEGER PRIMARY KEY
> 
> On 6/13/17, Scott Robison <sc...@casaderobison.com> wrote:
> >
> > Is it fair to say that the rowid aliasing behavior does not require
> > (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> > that order as the "type") as long as the type is INTEGER and the
> > constraint PRIMARY KEY appears somewhere in the column's constraint
> > list?
> 
> See https://www.sqlite.org/lang_createtable.html#rowid
> 
>CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
> 
> Columns x *not* a ROWID.  Life is complicated.  Sorry for the mess.
> But we have to preserve backwards compatibility - even with bugs like
> this.
> 
> 
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> 
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] INTEGER PRIMARY KEY

[Richard Hipp]

On 6/13/17, Scott Robison  wrote:
>
> Is it fair to say that the rowid aliasing behavior does not require
> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> that order as the "type") as long as the type is INTEGER and the
> constraint PRIMARY KEY appears somewhere in the column's constraint
> list?

See https://www.sqlite.org/lang_createtable.html#rowid

   CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Columns x *not* a ROWID.  Life is complicated.  Sorry for the mess.
But we have to preserve backwards compatibility - even with bugs like
this.


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


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

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Mon, Jun 12, 2017 at 4:20 PM, Simon Slavin  wrote:
>
>
> On 12 Jun 2017, at 11:01pm, Scott Robison  wrote:
>
>> Is it fair to say that the rowid aliasing behavior does not require
>> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
>> that order as the "type") as long as the type is INTEGER and the
>> constraint PRIMARY KEY appears somewhere in the column's constraint
>> list?
>
> FAQ #1 is specifically worded …
>
> 
>
> But you’re being very specific.  I think you’re right but someone who has 
> read the source code might know otherwise.

The question was raised at work so I got curious about it. It makes
sense to me that the rowid aliasing behavior would only be determined
after lexing and parsing the SQL statement, but the documentation
statements of "INTEGER PRIMARY KEY" being necessary led some people
(understandably) down a path thinking you needed those three words in
that order (perhaps).

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

Re: [sqlite] INTEGER PRIMARY KEY

[Simon Slavin]

On 12 Jun 2017, at 11:01pm, Scott Robison  wrote:

> Is it fair to say that the rowid aliasing behavior does not require
> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> that order as the "type") as long as the type is INTEGER and the
> constraint PRIMARY KEY appears somewhere in the column's constraint
> list?

FAQ #1 is specifically worded …



But you’re being very specific.  I think you’re right but someone who has read 
the source code might know otherwise.

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

[sqlite] INTEGER PRIMARY KEY

[Scott Robison]

This is as much out of curiosity as anything. I know that to get the
rowid aliasing behavior for a table one must define the column type as
INTEGER and using the constraint PRIMARY KEY. Something like:

CREATE TABLE A(B INTEGER PRIMARY KEY);

In testing this afternoon I was curious if I could give the constraint a name:

CREATE TABLE A(B INTEGER CONSTRAINT B_PK PRIMARY KEY);

I can, and it is still an alias of the rowid. If I change the type to
INT then it is not an alias. This all makes sense.

If I insert NOT NULL between the type and PK constraint, it still is
an alias (as long as the type is INTEGER).

Is it fair to say that the rowid aliasing behavior does not require
(by design) the incantation "INTEGER PRIMARY KEY" (all three words in
that order as the "type") as long as the type is INTEGER and the
constraint PRIMARY KEY appears somewhere in the column's constraint
list?
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

[Luuk]

On 19-10-2014 17:48, Keith Medcalf wrote:


for a table test(i integer primary key, j integer) the new i (rowid) is as 
follows:

test.i = case when test.i is not null then test.i else case max(test.i) when 
null then 1 else max(test.i)+1 end end


if you add the autoincrement keyword, then the algorithm becomes

test.i = case when test.i is not null then test.i else case when 
sequence[test.i] is null then 1 else sequence[test.i]+1 end end
sequence[test.i] = case when sequence[test.i] is null or test.i > 
sequence[test.i] then test.i else sequence[test.i] end

and the update of sequence[test.i] occurs whether or not the transaction 
commits.





thanks for the clear answer

i could not find it when looking at these links:
https://www.sqlite.org/autoinc.html
http://www.sqlite.org/faq.html

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

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

[Keith Medcalf]

for a table test(i integer primary key, j integer) the new i (rowid) is as 
follows:

test.i = case when test.i is not null then test.i else case max(test.i) when 
null then 1 else max(test.i)+1 end end


if you add the autoincrement keyword, then the algorithm becomes

test.i = case when test.i is not null then test.i else case when 
sequence[test.i] is null then 1 else sequence[test.i]+1 end end
sequence[test.i] = case when sequence[test.i] is null or test.i > 
sequence[test.i] then test.i else sequence[test.i] end

and the update of sequence[test.i] occurs whether or not the transaction 
commits.



>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Luuk
>Sent: Sunday, 19 October, 2014 08:10
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] 'INTEGER PRIMARY KEY' start value
>
>On 19-10-2014 15:27, Baruch Burstein wrote:
>> Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually
>> guaranteed to start from 1? Or at least from a positive number?
>>
>
>no
>
>C:\temp>sqlite3
>SQLite version 3.8.4.3 2014-04-03 16:53:12
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create table test (i integer primary key, j integer);
>sqlite> insert into test values (-10,-10);
>sqlite> insert into test(j) values (123);
>sqlite> select * from test;
>-10|-10
>-9|123
>sqlite>
>
>or did i enter i manually?
>i did with the first query
>but not with the second one
>___
>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] 'INTEGER PRIMARY KEY' start value

[Luuk]

On 19-10-2014 15:27, Baruch Burstein wrote:

Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually
guaranteed to start from 1? Or at least from a positive number?



no

C:\temp>sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (i integer primary key, j integer);
sqlite> insert into test values (-10,-10);
sqlite> insert into test(j) values (123);
sqlite> select * from test;
-10|-10
-9|123
sqlite>

or did i enter i manually?
i did with the first query
but not with the second one
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'INTEGER PRIMARY KEY' start value

[Simon Slavin]

On 19 Oct 2014, at 2:27pm, Baruch Burstein  wrote:

> Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually
> guaranteed to start from 1? Or at least from a positive number?

See the section 'Background' in



for the 'usual' algorithm used for ROWID.  Whatever 'usually' means.  I think 
it means anything that says INTEGER PRIMARY KEY but not INTEGER PRIMARY KEY 
AUTOINCREMENT.

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

[sqlite] 'INTEGER PRIMARY KEY' start value

[Baruch Burstein]

Is the rowid/'INTEGER PRIMARY KEY' field that is not entered manually
guaranteed to start from 1? Or at least from a positive number?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

GB schrieb am 24.08.2011 19:59:

Hi all,

I have a table like this:

CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
CREATE INDEX createIdx on t(createdAt);

SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
current content.

When perfoming a Statement like this:

SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
createdAt BETWEEN '2011-08-01' AND '2011-08-02'

the analyzer always chooses the rowid index which results in a scan over
one million rows. It would have to scan only a few dozen rows if it
chose createIdx instead (which is also a covering index). Looking at the
sqlite_stat2 table shows that there is no data for the rowid index.
Could this be the reason for the suboptimal query plan? The choice works
as expected if itemID is a regular column with an index on it.

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

sorry for replying to myself, but could someone knowledgeable about the 
query planner statistics comment on this? It would be nice to have the 
"best of two worlds" with direct rowid lookups and fair weighted index 
seeks. Are the missing statistics for the rowid tree an omission or 
would adding this have a broader impact on the code?


regards
gerd

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

GB schrieb am 25.08.2011 18:27:


Simon Slavin schrieb am 25.08.2011 02:00:

Had you thought of creating an explicit index on the rowid column, then running 
ANALYZE again ?

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


I tried that, with the same result as before. It still prefers the rowid.

regards
gerd


It seems that my answer was swallowed by the recent server issues so I 
thought I repost it. Sorry for any inconvenience.


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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

Simon Slavin schrieb am 25.08.2011 02:00:
> Had you thought of creating an explicit index on the rowid column, then 
> running ANALYZE again ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

I tried that, with the same result as before. It still prefers the rowid.

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Carlos Rocha]

>
> It seems equally logical to me that one of A or B might be evaluated, and if 
> it were false, then the other might not be evaluated.

I don't think so if efficiency matters. Of course the rule could be to 
evaluated from right to left instead, but it's good to have just one 
rule, and again, it seems logical to me that it should be from left to right

>
> And it would be logical to choose which of A or B to evaluated on a predicted 
> cost and probability of an advantageous false result.

I don't see how predicting cost and probability could help here.
rowID BETWEEN 100 AND 200 are roughly 1M, and createdAt BETWEEN 
'2011-08-01' AND '2011-08-02' could be 10M. In a simple case like this 
it's good to leave the wheel to who knows the db.

>
> but hay.  Who said their could only be one logical approach.
>
>   Alex
>
>
> On 24 Aug 2011, at 20:12, Carlos Rocha wrote:
>
>> Don't know how SQLite should behave in this case, but seems logical to
>> me that A and B would force that A is always evaluated, and B is
>> evaluated only if A is true.
>> I would change the order of the two betweens:
>>
>> SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND
>> '2011-08-02' AND itemID BETWEEN 100 AND 200
>>
>>> Hi all,
>>>
>>> I have a table like this:
>>>
>>> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
>>> CREATE INDEX createIdx on t(createdAt);
>>>
>>> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
>>> current content.
>>>
>>> When perfoming a Statement like this:
>>>
>>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>>>
>>> the analyzer always chooses the rowid index which results in a scan over
>>> one million rows. It would have to scan only a few dozen rows if it
>>> chose createIdx instead (which is also a covering index). Looking at the
>>> sqlite_stat2 table shows that there is no data for the rowid index.
>>> Could this be the reason for the suboptimal query plan? The choice works
>>> as expected if itemID is a regular column with an index on it.
>>>
>>> regards
>>> gerd
>>> ___
>>> 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] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Alex Bowden]

logical?

It seems equally logical to me that one of A or B might be evaluated, and if it 
were false, then the other might not be evaluated.

And it would be logical to choose which of A or B to evaluated on a predicted 
cost and probability of an advantageous false result.

but hay.  Who said their could only be one logical approach.

Alex


On 24 Aug 2011, at 20:12, Carlos Rocha wrote:

> Don't know how SQLite should behave in this case, but seems logical to 
> me that A and B would force that A is always evaluated, and B is 
> evaluated only if A is true.
> I would change the order of the two betweens:
> 
> SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND 
> '2011-08-02' AND itemID BETWEEN 100 AND 200
> 
>> Hi all,
>> 
>> I have a table like this:
>> 
>> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
>> CREATE INDEX createIdx on t(createdAt);
>> 
>> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
>> current content.
>> 
>> When perfoming a Statement like this:
>> 
>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>> 
>> the analyzer always chooses the rowid index which results in a scan over
>> one million rows. It would have to scan only a few dozen rows if it
>> chose createIdx instead (which is also a covering index). Looking at the
>> sqlite_stat2 table shows that there is no data for the rowid index.
>> Could this be the reason for the suboptimal query plan? The choice works
>> as expected if itemID is a regular column with an index on it.
>> 
>> regards
>> gerd
>> ___
>> 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] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Simon Slavin]

On 24 Aug 2011, at 11:18pm, GB wrote:

> Well, that is exactly what the sqlite_stat2 table is meant for. It's 
> information is supposed to make the query planner able to decide upon 
> the usefulness of an index. Unfortunately, histogram information is not 
> collected for the implicit rowid index by the ANALYZE command, so the 
> planner has to rely on some rule of thumb which in turn seems to favour 
> the rowid index.

Had you thought of creating an explicit index on the rowid column, then running 
ANALYZE again ?

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

Simon Slavin schrieb am 24.08.2011 23:33:
> On 24 Aug 2011, at 9:59pm, GB wrote:
>
>> Simon Slavin schrieb am 24.08.2011 22:38:
>>
>>> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND
>>> createdAt>= '2011-08-01' createdAt<= '2011-08-02'
>> Thank you for your thoughts but I already tried this with no different
>> results than before. And according to
>> http://www.sqlite.org/optoverview.html#between_opt this is exactly what
>> happens behind the curtains when SQLite processes BETWEEN statements.
> Just checking.
>
>> What I'm actually looking for is a way to make SQLite create and
>> consider histogram data for rowid lookup the same way as for regular
>> indexes.
> SQLite should be comparing the 'chunkiness' of the itemID and createdAt 
> columns.  It won't know how chunky they are around the specific values 
> specified in that particular SELECT command.  It also won't be able to 
> compare the distance between 1000 and 200, a million values, and 
> '2011-08-01' and '2011-08-02', which is just two values.  The 'two values' 
> would suggest that using an index on that column might be the best way to 
> start, but I don't think the query optimizer can actually tell that.
>
> But I don't know details about how the optimizer uses its information so I 
> can't tell for sure whether it's making a good guess or it could be improved.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Well, that is exactly what the sqlite_stat2 table is meant for. It's 
information is supposed to make the query planner able to decide upon 
the usefulness of an index. Unfortunately, histogram information is not 
collected for the implicit rowid index by the ANALYZE command, so the 
planner has to rely on some rule of thumb which in turn seems to favour 
the rowid index. And if I make the itemID Column a regular (non-INTEGER 
PRIMARY KEY) one, the query plan gets generated as expected using the 
index on createdAt.

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Simon Slavin]

On 24 Aug 2011, at 9:59pm, GB wrote:

> Simon Slavin schrieb am 24.08.2011 22:38:
> 
>> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND
>> createdAt>= '2011-08-01' createdAt<= '2011-08-02'
> 
> Thank you for your thoughts but I already tried this with no different 
> results than before. And according to 
> http://www.sqlite.org/optoverview.html#between_opt this is exactly what 
> happens behind the curtains when SQLite processes BETWEEN statements.

Just checking.

> What I'm actually looking for is a way to make SQLite create and 
> consider histogram data for rowid lookup the same way as for regular 
> indexes.

SQLite should be comparing the 'chunkiness' of the itemID and createdAt 
columns.  It won't know how chunky they are around the specific values 
specified in that particular SELECT command.  It also won't be able to compare 
the distance between 1000 and 200, a million values, and '2011-08-01' 
and '2011-08-02', which is just two values.  The 'two values' would suggest 
that using an index on that column might be the best way to start, but I don't 
think the query optimizer can actually tell that.

But I don't know details about how the optimizer uses its information so I 
can't tell for sure whether it's making a good guess or it could be improved.

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

Simon Slavin schrieb am 24.08.2011 22:38:
> On 24 Aug 2011, at 6:59pm, GB wrote:
>
>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
> Just out of curiosity, try changing both the BETWEEN formulations so it says
>
> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND
> createdAt>= '2011-08-01' createdAt<= '2011-08-02'
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Thank you for your thoughts but I already tried this with no different 
results than before. And according to 
http://www.sqlite.org/optoverview.html#between_opt this is exactly what 
happens behind the curtains when SQLite processes BETWEEN statements. 
What I'm actually looking for is a way to make SQLite create and 
consider histogram data for rowid lookup the same way as for regular 
indexes.

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Simon Slavin]

On 24 Aug 2011, at 6:59pm, GB wrote:

> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND 
> createdAt BETWEEN '2011-08-01' AND '2011-08-02'

Just out of curiosity, try changing both the BETWEEN formulations so it says

SELECT itemID FROM t WHERE itemID >= 100 AND itemID <= 200 AND 
createdAt >= '2011-08-01' createdAt <= '2011-08-02'

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

No, SQLite (as well as most other database systems) does a more 
elaborate evaluation. I breaks the statement apart into subterms and 
tries to determine which one makes the most beneficial use of an index 
so the order of the statement does not matter. See 
http://www.sqlite.org/optoverview.html for details.

regards
gerd

Carlos Rocha schrieb am 24.08.2011 21:12:
> Don't know how SQLite should behave in this case, but seems logical to
> me that A and B would force that A is always evaluated, and B is
> evaluated only if A is true.
> I would change the order of the two betweens:
>
> SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND
> '2011-08-02' AND itemID BETWEEN 100 AND 200
>
>> Hi all,
>>
>> I have a table like this:
>>
>> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
>> CREATE INDEX createIdx on t(createdAt);
>>
>> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
>> current content.
>>
>> When perfoming a Statement like this:
>>
>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>>
>> the analyzer always chooses the rowid index which results in a scan over
>> one million rows. It would have to scan only a few dozen rows if it
>> chose createIdx instead (which is also a covering index). Looking at the
>> sqlite_stat2 table shows that there is no data for the rowid index.
>> Could this be the reason for the suboptimal query plan? The choice works
>> as expected if itemID is a regular column with an index on it.
>>
>> regards
>> gerd
>> ___
>> 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] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

Igor Tandetnik schrieb am 24.08.2011 20:20:
> On 8/24/2011 1:59 PM, GB wrote:
>> When perfoming a Statement like this:
>>
>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>>
>> the analyzer always chooses the rowid index which results in a scan over
>> one million rows. It would have to scan only a few dozen rows if it
>> chose createIdx instead (which is also a covering index). Looking at the
>> sqlite_stat2 table shows that there is no data for the rowid index.
>> Could this be the reason for the suboptimal query plan? The choice works
>> as expected if itemID is a regular column with an index on it.
> I don't know the answer to your question, but if you are interested in a
> workaround, you can write
>
> WHERE +itemID BETWEEN 100 AND 200
>
> The unary plus suppresses the use of index on this column.

I know of this way of forcing SQLite to not use a specific index. 
Forcing a specific index using INDEXED BY might as well be a solution. 
Unfortunately I can't rely on a specific data distribution, that's why I 
chose to set SQLITE_ENABLE_STAT2. I thought it would make the query 
analyzer choose a good plan based on the actual data distribution.

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Carlos Rocha]

Don't know how SQLite should behave in this case, but seems logical to 
me that A and B would force that A is always evaluated, and B is 
evaluated only if A is true.
I would change the order of the two betweens:

SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND 
'2011-08-02' AND itemID BETWEEN 100 AND 200

> Hi all,
>
> I have a table like this:
>
> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
> CREATE INDEX createIdx on t(createdAt);
>
> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
> current content.
>
> When perfoming a Statement like this:
>
> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>
> the analyzer always chooses the rowid index which results in a scan over
> one million rows. It would have to scan only a few dozen rows if it
> chose createIdx instead (which is also a covering index). Looking at the
> sqlite_stat2 table shows that there is no data for the rowid index.
> Could this be the reason for the suboptimal query plan? The choice works
> as expected if itemID is a regular column with an index on it.
>
> regards
> gerd
> ___
> 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] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Igor Tandetnik]

On 8/24/2011 1:59 PM, GB wrote:
> When perfoming a Statement like this:
>
> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>
> the analyzer always chooses the rowid index which results in a scan over
> one million rows. It would have to scan only a few dozen rows if it
> chose createIdx instead (which is also a covering index). Looking at the
> sqlite_stat2 table shows that there is no data for the rowid index.
> Could this be the reason for the suboptimal query plan? The choice works
> as expected if itemID is a regular column with an index on it.

I don't know the answer to your question, but if you are interested in a 
workaround, you can write

WHERE +itemID BETWEEN 100 AND 200

The unary plus suppresses the use of index on this column.
-- 
Igor Tandetnik

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

[sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

Hi all,

I have a table like this:

CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
CREATE INDEX createIdx on t(createdAt);

SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with 
current content.

When perfoming a Statement like this:

SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND 
createdAt BETWEEN '2011-08-01' AND '2011-08-02'

the analyzer always chooses the rowid index which results in a scan over 
one million rows. It would have to scan only a few dozen rows if it 
chose createIdx instead (which is also a covering index). Looking at the 
sqlite_stat2 table shows that there is no data for the rowid index. 
Could this be the reason for the suboptimal query plan? The choice works 
as expected if itemID is a regular column with an index on it.

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

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[Igor Tandetnik]

Pavel Ivanov wrote:
> Could you explain why this scenario doesn't cause infinite call cycle
> of the trigger by itself? Is there some protection in SQLite which
> breaks such cycles?

SQLite doesn't support recursive triggers: a trigger cannot call itself, 
directly or indirectly. SQLite keeps track of the triggers that appear 
in the current chain, and if an operation attempts to fire a trigger 
already present in the chain, the trigger doesn't run the second time.

Igor Tandetnik 



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

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[Dennis Cote]

Oliver Peters wrote:
>
> I want the "normal" user only identify himself by putting his id into
> the field identity and afterwards let the system decide in what field to
> put his id (INSERT = creator, UPDATE = editor). Doing this for every
> record I can show who created it and who was the last editor.
>   
Are the users entering the SQL directly? If not, it still seems to me 
that your application can take the value from the Identity field in your 
UI and assign it to the creator field when inserting a new record. The 
users are seeing a different UI for insertions and update aren't they? 
Your application knows whether it is doing an insert or an update, so it 
can execute the appropriate SQL Statement.

> This task could be accomplished by a combination of INSERT and an AFTER
> INSERT Trigger
>
> /* Code */
>
> INSERT INTO a(code,name,identity)
> VALUES(new."code",new."name",new."identity");
>
> CREATE TRIGGER IF NOT EXISTS test
> AFTER INSERT ON "a"
> BEGIN
> UPDATE a SET creator = identity, identity = NULL; -- NULL to empty it for 
> other possible editors
> END;
>
>   
Based on this I would guess that your update trigger does something like 
this.

BEGIN
UPDATE a SET editor = identity, identity = NULL; -- NULL to empty it for other 
possible editors
END;


What is the purpose of the identity field if it is always NULL after an 
insert or update?
> But because I log every insert/update/delete into a separate table too the 
> combination of INSERT and AFTER INSERT trigger would lead to 2 log records 
> (1. INSERT, 2. UPDATE) - and that's what I'd like to avoid.
>
>   
If you insist on your current approach, you could drop the insert 
trigger and use a field specific update trigger to log the updates done 
by the insert and update triggers. If the log entries are different for 
the two types of changes, then you could use two different triggers, or 
use a select case... conditional to build the appropriate log entry. 

CREATE TRIGGER IF NOT EXISTS log_entry
AFTER UPDATE OF creator, editor ON "a"
BEGIN
  --Insert log record for newly inserted or updated record
  INSERT INTO log ...
END;

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

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[João Eiras]

On , Pavel Ivanov  wrote:

> Hi, Richard!
>
> Could you explain why this scenario doesn't cause infinite call cycle
> of the trigger by itself? Is there some protection in SQLite which
> breaks such cycles?
>

Many dbms forbid recursive trigger calls that modify a table that has been 
previous modified by the same chain of trigger invocations.
For example:
  table A, trigger Y which modifies B
  table B, trigger W which modifies A

DML on A invokes Y which modifies B, and in turn invokes W which tries to 
modify A. This could cause infinite recursion. So it should not be allowed.

The only thing that triggers are allowed to do is to change only the line that 
causes the invocation of the trigger, if it is a FOR EACH ROW trigger, or 
change other tables if that does not cause recursion back to the original one.

I don't know how sqlite handles these situations, but they should be forbidden.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[Oliver Peters]

Am Montag, den 22.06.2009, 17:39 -0600 schrieb Dennis Cote:
> Oliver Peters wrote:
> > sorry: my code wasn't completely what I wanted so here again:
> >
> > CREATE TRIGGER IF NOT EXISTS test
> > BEFORE INSERT ON "a"
> > BEGIN
> > INSERT INTO a(code,name,creator) 
> > VALUES(new."code",new."name",new."identity");
> > SELECT RAISE(IGNORE);
> > END;
> >
> > the difference is that I put new."identity" into the field "creator". This 
> > is the way I chose to differ between creation and altering/updating of a 
> > record (In case of an UPDATE I've another trigger that shoots 
> > new."identity" into another field. The result is a kind of record-logging 
> > the "normal" user should see.
> >
> > On the other hand I've a complete logging that writes every 
> > inserted/updated/deleted record in a special table. If I use a trigger in 
> > your suggested way
> >
> > CREATE TRIGGER IF NOT EXISTS test
> > AFTER INSERT ON "a"
> > BEGIN
> > INSERT INTO a(code,name,identity) 
> > VALUES(new."code",new."name",new."identity");
> > UPDATE a SET creator = identity, identity = NULL;
> > END;
> >
> > I'd get 2 entries per record in the log - I'd like to avoid this. Are there 
> > other possibilities? Maybe I'm only a little balky? ;-)
> >   
> Oliver,
> 
> I didn't follow your description of your problem.
> 
> If you are going to replace the value of the identity field with NULL in 
> your "after insert" trigger, why bother inserting it at all? Why not 
> simply insert the same values into the desired columns?
> 
> Instead of:
> 
> INSERT INTO a(code,name,identity) VALUES('V','abc',1);
> 
> why don't you do this:
> 
> INSERT INTO a(code,name,creator) VALUES('V','abc',1);
> 
> I think you may need to expand on your description of "shooting 
> new.identity into another field" to clarify what you are trying to 
> accomplish.
> 
> Dennis Cote


Hello Dennis

I want the "normal" user only identify himself by putting his id into
the field identity and afterwards let the system decide in what field to
put his id (INSERT = creator, UPDATE = editor). Doing this for every
record I can show who created it and who was the last editor.

This task could be accomplished by a combination of INSERT and an AFTER
INSERT Trigger

/* Code */

INSERT INTO a(code,name,identity)
VALUES(new."code",new."name",new."identity");

CREATE TRIGGER IF NOT EXISTS test
AFTER INSERT ON "a"
BEGIN
UPDATE a SET creator = identity, identity = NULL; -- NULL to empty it for other 
possible editors
END;


But because I log every insert/update/delete into a separate table too the 
combination of INSERT and AFTER INSERT trigger would lead to 2 log records (1. 
INSERT, 2. UPDATE) - and that's what I'd like to avoid.

Greetings
Oliver Peters

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

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[Dennis Cote]

Oliver Peters wrote:
> sorry: my code wasn't completely what I wanted so here again:
>
>   CREATE TRIGGER IF NOT EXISTS test
>   BEFORE INSERT ON "a"
>   BEGIN
>   INSERT INTO a(code,name,creator) 
> VALUES(new."code",new."name",new."identity");
>   SELECT RAISE(IGNORE);
>   END;
>
> the difference is that I put new."identity" into the field "creator". This is 
> the way I chose to differ between creation and altering/updating of a record 
> (In case of an UPDATE I've another trigger that shoots new."identity" into 
> another field. The result is a kind of record-logging the "normal" user 
> should see.
>
> On the other hand I've a complete logging that writes every 
> inserted/updated/deleted record in a special table. If I use a trigger in 
> your suggested way
>
>   CREATE TRIGGER IF NOT EXISTS test
>   AFTER INSERT ON "a"
>   BEGIN
>   INSERT INTO a(code,name,identity) 
> VALUES(new."code",new."name",new."identity");
>   UPDATE a SET creator = identity, identity = NULL;
>   END;
>
> I'd get 2 entries per record in the log - I'd like to avoid this. Are there 
> other possibilities? Maybe I'm only a little balky? ;-)
>   
Oliver,

I didn't follow your description of your problem.

If you are going to replace the value of the identity field with NULL in 
your "after insert" trigger, why bother inserting it at all? Why not 
simply insert the same values into the desired columns?

Instead of:

INSERT INTO a(code,name,identity) VALUES('V','abc',1);

why don't you do this:

INSERT INTO a(code,name,creator) VALUES('V','abc',1);

I think you may need to expand on your description of "shooting 
new.identity into another field" to clarify what you are trying to 
accomplish.

Dennis Cote

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

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[Pavel Ivanov]

Hi, Richard!

Could you explain why this scenario doesn't cause infinite call cycle
of the trigger by itself? Is there some protection in SQLite which
breaks such cycles?

Pavel

On Mon, Jun 22, 2009 at 4:10 PM, D. Richard Hipp wrote:
>
> On Jun 22, 2009, at 3:33 PM, Oliver Peters wrote:
>
>> Hello out there,
>>
>>
>> to my mind I get false entries in sqlite_sequence using this code:
>>
>>
>>       CREATE TABLE IF NOT EXISTS a(
>>               id                  INTEGER      PRIMARY KEY AUTOINCREMENT,
>>               code                VARCHAR      NOT NULL,
>>               name                VARCHAR      NOT NULL,
>>               identity            INTEGER      DEFAULT NULL,
>>               creator             INTEGER      DEFAULT NULL,
>>               timestamp           TIMESTAMP    DEFAULT
>> (datetime('now','localtime'))
>> );
>>
>>
>>       CREATE TRIGGER IF NOT EXISTS test
>>       BEFORE INSERT ON "a"
>>               BEGIN
>>                       INSERT INTO a(code,name,identity)
>> VALUES(new."code",new."name",new."identity");
>>                       SELECT RAISE(IGNORE);
>>               END;
>>
>>
>>       INSERT INTO a(code,name,identity) VALUES('V','abc',1);
>>       INSERT INTO a(code,name,identity) VALUES('S','def',1);
>>
>>
>> As a result I get 2 records in sqlite_sequence:
>>
>> name     seq
>> a            1
>> a            NULL
>
> Please file a bug report and I will fix it when I get a chance.
>
>
>>
>> shouldn't there only 1 record (a   2)?
>>
>> The ids in table a are as expected (1 & 2).
>>
>>
>> __
>> GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
>> Jetzt freischalten unter http://movieflat.web.de
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> D. Richard Hipp
> d...@hwaci.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] integer primary key autoincrement & sqlite_sequence

[D. Richard Hipp]

On Jun 22, 2009, at 3:33 PM, Oliver Peters wrote:

> Hello out there,
>
>
> to my mind I get false entries in sqlite_sequence using this code:
>
>
>   CREATE TABLE IF NOT EXISTS a(
>   id  INTEGER  PRIMARY KEY AUTOINCREMENT,
>   codeVARCHAR  NOT NULL,
>   nameVARCHAR  NOT NULL,
>   identityINTEGER  DEFAULT NULL,
>   creator INTEGER  DEFAULT NULL,
>   timestamp   TIMESTAMPDEFAULT  
> (datetime('now','localtime'))
> );
>
>
>   CREATE TRIGGER IF NOT EXISTS test
>   BEFORE INSERT ON "a"
>   BEGIN
>   INSERT INTO a(code,name,identity)  
> VALUES(new."code",new."name",new."identity");
>   SELECT RAISE(IGNORE);
>   END;
>
>
>   INSERT INTO a(code,name,identity) VALUES('V','abc',1);
>   INSERT INTO a(code,name,identity) VALUES('S','def',1);
>
>
> As a result I get 2 records in sqlite_sequence:
>
> name seq
> a1
> aNULL

Please file a bug report and I will fix it when I get a chance.


>
> shouldn't there only 1 record (a   2)?
>
> The ids in table a are as expected (1 & 2).
>
>
> __
> GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://movieflat.web.de
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[Oliver Peters]

[...]
> 
> The ROWID is not generated until the INSERT statement actually runs.   
> Hence the BEFORE trigger does not have access to it and the BEFORE  
> trigger sees a NULL.  Change the trigger to an AFTER trigger and it  
> will work.
> 
[...]
> 
> D. Richard Hipp
> d...@hwaci.com

Thanks for the unbelievable fast response but

sorry: my code wasn't completely what I wanted so here again:

CREATE TRIGGER IF NOT EXISTS test
BEFORE INSERT ON "a"
BEGIN
INSERT INTO a(code,name,creator) 
VALUES(new."code",new."name",new."identity");
SELECT RAISE(IGNORE);
END;

the difference is that I put new."identity" into the field "creator". This is 
the way I chose to differ between creation and altering/updating of a record 
(In case of an UPDATE I've another trigger that shoots new."identity" into 
another field. The result is a kind of record-logging the "normal" user should 
see.

On the other hand I've a complete logging that writes every 
inserted/updated/deleted record in a special table. If I use a trigger in your 
suggested way

CREATE TRIGGER IF NOT EXISTS test
AFTER INSERT ON "a"
BEGIN
INSERT INTO a(code,name,identity) 
VALUES(new."code",new."name",new."identity");
UPDATE a SET creator = identity, identity = NULL;
END;

I'd get 2 entries per record in the log - I'd like to avoid this. Are there 
other possibilities? Maybe I'm only a little balky? ;-)
__
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de

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

Re: [sqlite] integer primary key autoincrement & sqlite_sequence

[D. Richard Hipp]

On Jun 22, 2009, at 3:33 PM, Oliver Peters wrote:

> Hello out there,
>
>
> to my mind I get false entries in sqlite_sequence using this code:
>
>
>   CREATE TABLE IF NOT EXISTS a(
>   id  INTEGER  PRIMARY KEY AUTOINCREMENT,
>   codeVARCHAR  NOT NULL,
>   nameVARCHAR  NOT NULL,
>   identityINTEGER  DEFAULT NULL,
>   creator INTEGER  DEFAULT NULL,
>   timestamp   TIMESTAMPDEFAULT  
> (datetime('now','localtime'))
> );
>
>
>   CREATE TRIGGER IF NOT EXISTS test
>   BEFORE INSERT ON "a"
>   BEGIN
>   INSERT INTO a(code,name,identity)  
> VALUES(new."code",new."name",new."identity");
>   SELECT RAISE(IGNORE);
>   END;
>
>
>   INSERT INTO a(code,name,identity) VALUES('V','abc',1);
>   INSERT INTO a(code,name,identity) VALUES('S','def',1);
>
>
> As a result I get 2 records in sqlite_sequence:
>
> name seq
> a1
> aNULL
>
> shouldn't there only 1 record (a   2)?

The ROWID is not generated until the INSERT statement actually runs.   
Hence the BEFORE trigger does not have access to it and the BEFORE  
trigger sees a NULL.  Change the trigger to an AFTER trigger and it  
will work.

>
> The ids in table a are as expected (1 & 2).
>
>
> __
> GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://movieflat.web.de
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

[sqlite] integer primary key autoincrement & sqlite_sequence

[Oliver Peters]

Hello out there,


to my mind I get false entries in sqlite_sequence using this code:


CREATE TABLE IF NOT EXISTS a(
id  INTEGER  PRIMARY KEY AUTOINCREMENT,
codeVARCHAR  NOT NULL,
nameVARCHAR  NOT NULL,
identityINTEGER  DEFAULT NULL,
creator INTEGER  DEFAULT NULL,
timestamp   TIMESTAMPDEFAULT 
(datetime('now','localtime'))
);


CREATE TRIGGER IF NOT EXISTS test
BEFORE INSERT ON "a"
BEGIN
INSERT INTO a(code,name,identity) 
VALUES(new."code",new."name",new."identity");
SELECT RAISE(IGNORE);
END;


INSERT INTO a(code,name,identity) VALUES('V','abc',1);
INSERT INTO a(code,name,identity) VALUES('S','def',1);


As a result I get 2 records in sqlite_sequence:

name seq
a1
aNULL

shouldn't there only 1 record (a   2)?

The ids in table a are as expected (1 & 2).


__
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de

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

Re: [sqlite] INTEGER PRIMARY KEY and triggers

[Hugh Gibson]

> I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id  
> fields in my SQLite projects, yet I would like to try some triggers 
> as  well.  Of course, every time I add a trigger that accesses a 
> table  with  these types of id fields, all sorts of odd things 
> happen.

Not to mention the fact that an index made up of 2 or more fields
including the id field won't be used correctly. See
http://www.sqlite.org/cvstrac/tktview?tn=1893

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

Re: [sqlite] INTEGER PRIMARY KEY and Triggers

[Craig Smith]

On Nov 17, 2008, at 12:45 AM, [EMAIL PROTECTED] wrote:

>> I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id
>> fields in my SQLite projects, yet I would like to try some triggers  
>> as
>> well.  Of course, every time I add a trigger that accesses a table
>> with  these types of id fields, all sorts of odd things happen.  I
>> read in the documentation about simply using PRIMARY KEY instead of
>> INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability.
>
> To clarify, do you mean that an automatically generated autoincrement
> value is not available in BEFORE INSERT triggers?
>
> Presently, it is only available in AFTER INSERT triggers.
>
> Dan.

Dan:

Yes, I was using the AFTER INSERT on the trigger.  Please see my  
followup posting in Issue 49, message number 1.  I posted a  
clarification of the odd behavior I am observing.

>
>
>> Does anyone have a solution that both provides AUTOINCREMENT (or a
>> reasonable facsimile) to a PRIMARY KEY field and allows the usage of
>> triggers on those tables?
>>
>> Thank you very much.
>>
>> Craig Smith
>

Craig Smith
[EMAIL PROTECTED]



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

Re: [sqlite] INTEGER PRIMARY KEY and triggers

[Dan]

On Nov 16, 2008, at 2:46 AM, Craig Smith wrote:

> I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id
> fields in my SQLite projects, yet I would like to try some triggers as
> well.  Of course, every time I add a trigger that accesses a table
> with  these types of id fields, all sorts of odd things happen.  I
> read in the documentation about simply using PRIMARY KEY instead of
> INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability.

To clarify, do you mean that an automatically generated autoincrement
value is not available in BEFORE INSERT triggers?

Presently, it is only available in AFTER INSERT triggers.

Dan.

> Does anyone have a solution that both provides AUTOINCREMENT (or a
> reasonable facsimile) to a PRIMARY KEY field and allows the usage of
> triggers on those tables?
>
> Thank you very much.
>
> Craig Smith
>
>
>
> ___
> 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] INTEGER PRIMARY KEY and triggers

[Craig Smith]

I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id  
fields in my SQLite projects, yet I would like to try some triggers as  
well.  Of course, every time I add a trigger that accesses a table  
with  these types of id fields, all sorts of odd things happen.  I  
read in the documentation about simply using PRIMARY KEY instead of  
INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability.

Does anyone have a solution that both provides AUTOINCREMENT (or a  
reasonable facsimile) to a PRIMARY KEY field and allows the usage of  
triggers on those tables?

Thank you very much.

Craig Smith



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

Re: [sqlite] integer primary key and unique index?

[Dennis Cote]

Petite Abeille wrote:
> 
> Would adding an unique index on an integer primary key be of any  
> benefit? Or is it redundant?
> 

It would not help, and would in fact slow down all inserts, deletes, and 
updates for no benefit. It is redundant.

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

[sqlite] integer primary key and unique index?

[Petite Abeille]

Hello,

Would adding an unique index on an integer primary key be of any  
benefit? Or is it redundant?

In "Primary key and index", Ben Carlyle wrote the following:

1 Table = 1 BTree, the BTree holds the data and is ordered by ROWID
1 Table with 1 Index = 2 BTrees, the second referring to rows in the  
first
1 Table with PRIMARY KEY = 1 Table with 1 (unique) Index
1 Table with INTEGER PRIMARY KEY = 1 Table, with its own BTree forming  
its unique index
-- Ben Carlyle, "Primary key and index", 2004
http://osdir.com/ml/db.sqlite.general/2004-02/msg00067.html

Is that an accurate description?

Thanks in advance.

Kind regards,

--
PA.
http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Odekirk, Shawn]

>> Brickl Roland [mailto:[EMAIL PROTECTED] wrote:
>> Integer PrimaryKeys are always autoincrementing. When you don't
>> specify it it uses after (2^63)-1 a random free positiv value.

>> "Odekirk, Shawn" <[EMAIL PROTECTED]> wrote:
>> I will compile this for Windows and see what my results are using a
>> compiler that supports 64 bit integers.

>  "Odekirk, Shawn" <[EMAIL PROTECTED]> wrote:
> Indeed, compiled using Microsoft Visual Studio 2005 it works as
> described.
> So, now to dive into the source and figure out how to make it work
> using my old SCO tools.

Well, that was easy!
A little poking around and I found the SQLITE_32BIT_ROWID preprocessor
symbol.  Simply defining that symbol and recompiling fixed my problem.
I have to say that I am very impressed with how easy it has been to
compile SQLite on different platforms and with how well it seems to
work.  I'm looking around trying to find other projects where I can use
it.

Shawn


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Odekirk, Shawn]

Brickl Roland [mailto:[EMAIL PROTECTED] wrote:
> Integer PrimaryKeys are always autoincrementing. When you don't
> specify it it uses after (2^63)-1 a random free positiv value.

"Odekirk, Shawn" <[EMAIL PROTECTED]> wrote:
> I will compile this for Windows and see what my results are using a
> compiler that supports 64 bit integers.

Indeed, compiled using Microsoft Visual Studio 2005 it works as
described.
So, now to dive into the source and figure out how to make it work using
my old SCO tools.

Thanks,
Shawn


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[drh]

"Odekirk, Shawn" <[EMAIL PROTECTED]> wrote:
> My compiler is old and I don't think it supports a 64 bit data type.
> Maybe this is the root cause of my problem.
> 

Very likely.  

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Odekirk, Shawn]

Thank you for your reply.
I tried adding NOT NULL to my primary key column, but the results are
the same.
My compiler is old and I don't think it supports a 64 bit data type.
Maybe this is the root cause of my problem.

If I create a table like this:
CREATE TABLE rollover (id INTEGER PRIMARY KEY, name TEXT)
or:
CREATE TABLE rollover (id INTEGER PRIMARY KEY NOT NULL, name TEXT)
and I insert a row like this:
INSERT INTO rollover VALUES (2147483647, 'One');
the row has the values:
2147483647, 'One'

If I then insert a row like this:
INSERT INTO rollover VALUES (NULL, 'Two');
the newly inserted row has the following values:
-2147483648, 'Two'

If I then try to insert another row like this:
INSERT INTO rollover VALUES (NULL, 'Three');
I get: "SQL error: PRIMARY KEY must be unique"

I would like the primary key to rollover to 1, instead of to
-2147483648.  Does anyone have any ideas where I should look?

I will compile this for Windows and see what my results are using a
compiler that supports 64 bit integers.

Thanks,
Shawn

-Original Message-
From: Brickl Roland [mailto:[EMAIL PROTECTED] 
Sent: Saturday, October 13, 2007 5:29 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover
Question

Hallo Odekirk Shawn,

SQLite use up to an 64Bit signed Integer for Primary Keys, even on non
64Bit-Systems!
Integer PrimaryKeys are always autoincrementing. When you don't specify
it it uses after (2^63)-1
a random free positiv value. When you write autoincrement for your
create table it never reuse
deleted positiv values and you get an SQLITE_FULL error, thats all.
And please don't forget the not null for your primary key. Without this
you get a little bit
different behavior.

Greats,
Brickl
--- "Odekirk, Shawn" <[EMAIL PROTECTED]> schrieb:

> I am evaluating SQLite for a project I am working on.
> 
> I have a question about the behavior of the INTEGER PRIMARY KEY auto
> increment feature.
> 
> My platform uses 32 bit integers, so the valid values for an unsigned
> integer are 0 - 4294967296 and the valid values for a signed integer
are
> -2147483648 - 2147483647.
> 
> Since the INTEGER PRIMARY KEY data type is a signed integer, the
maximum
> positive value is 2147483648.  If my table already has a row with the
> maximum positive value in the primary key field, and I insert a row
> using NULL as the value of the primary key field, the row is inserted
> and the primary key is assigned the value of -2147483648.  That makes
> sense to me and I have no problem with that.  The problem is that the
> next row I insert generates the error "SQL error: PRIMARY KEY must be
> unique".  I suspect that this is because SQLite tries to use the next
> largest positive value when it increments the primary key field.
> 
> Is there an easy way to cause the INTEGER PRIMARY KEY column to use an
> unsigned integer instead, or to roll over to 0 instead of the most
> negative value for the data type?
> 
> I suspect that in practice I will not run into this issue.  However, I
> would feel better knowing that there is no chance that I will
encounter
> this problem.
> 
>  
> 
> Thanks,
> 
> Shawn
> 
>  
> 
> 



  __  
Yahoo! Clever: Sie haben Fragen? Yahoo! Nutzer antworten Ihnen.
www.yahoo.de/clever



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Brickl Roland]

Hallo Odekirk Shawn,

SQLite use up to an 64Bit signed Integer for Primary Keys, even on non 
64Bit-Systems!
Integer PrimaryKeys are always autoincrementing. When you don't specify it it 
uses after (2^63)-1
a random free positiv value. When you write autoincrement for your create table 
it never reuse
deleted positiv values and you get an SQLITE_FULL error, thats all.
And please don't forget the not null for your primary key. Without this you get 
a little bit
different behavior.

Greats,
Brickl
--- "Odekirk, Shawn" <[EMAIL PROTECTED]> schrieb:

> I am evaluating SQLite for a project I am working on.
> 
> I have a question about the behavior of the INTEGER PRIMARY KEY auto
> increment feature.
> 
> My platform uses 32 bit integers, so the valid values for an unsigned
> integer are 0 - 4294967296 and the valid values for a signed integer are
> -2147483648 - 2147483647.
> 
> Since the INTEGER PRIMARY KEY data type is a signed integer, the maximum
> positive value is 2147483648.  If my table already has a row with the
> maximum positive value in the primary key field, and I insert a row
> using NULL as the value of the primary key field, the row is inserted
> and the primary key is assigned the value of -2147483648.  That makes
> sense to me and I have no problem with that.  The problem is that the
> next row I insert generates the error "SQL error: PRIMARY KEY must be
> unique".  I suspect that this is because SQLite tries to use the next
> largest positive value when it increments the primary key field.
> 
> Is there an easy way to cause the INTEGER PRIMARY KEY column to use an
> unsigned integer instead, or to roll over to 0 instead of the most
> negative value for the data type?
> 
> I suspect that in practice I will not run into this issue.  However, I
> would feel better knowing that there is no chance that I will encounter
> this problem.
> 
>  
> 
> Thanks,
> 
> Shawn
> 
>  
> 
> 



  __  
Yahoo! Clever: Sie haben Fragen? Yahoo! Nutzer antworten Ihnen. 
www.yahoo.de/clever


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question

[Odekirk, Shawn]

I am evaluating SQLite for a project I am working on.

I have a question about the behavior of the INTEGER PRIMARY KEY auto
increment feature.

My platform uses 32 bit integers, so the valid values for an unsigned
integer are 0 - 4294967296 and the valid values for a signed integer are
-2147483648 - 2147483647.

Since the INTEGER PRIMARY KEY data type is a signed integer, the maximum
positive value is 2147483648.  If my table already has a row with the
maximum positive value in the primary key field, and I insert a row
using NULL as the value of the primary key field, the row is inserted
and the primary key is assigned the value of -2147483648.  That makes
sense to me and I have no problem with that.  The problem is that the
next row I insert generates the error "SQL error: PRIMARY KEY must be
unique".  I suspect that this is because SQLite tries to use the next
largest positive value when it increments the primary key field.

Is there an easy way to cause the INTEGER PRIMARY KEY column to use an
unsigned integer instead, or to roll over to 0 instead of the most
negative value for the data type?

I suspect that in practice I will not run into this issue.  However, I
would feel better knowing that there is no chance that I will encounter
this problem.

 

Thanks,

Shawn

 

[sqlite] INTEGER PRIMARY KEY

[Tom Shaw]

Here's a question on INTEGER PRIMARY KEY. I would like use IP 
addresses (converted to an unsigned number to man them monotonically 
increasing) for INTEGER PRIMARY KEY however I can't determine from 
the online docs whether if I supply an unsigned integer in PHP 5:


$uip = sprintf("%u", ip2long($ip));

to sqlite 3. Is this possible or do I have to either use text (yuk) 
or split the ips (yuk)


TIA,

Tom

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] integer primary key initial value

[Michael Hunley]

This may be a totally rooky question, but better safe than sorry
If I declare a table with an INTEGER PRIMARY KEY field s.t. it is an 
auto-increment, do the first INSERT into it and 
call  sqlite_last_insert_rowid(), will I get back a 0 or something 
else?  Will I ever get 0 back (in case it wraps or something)?

thanks.

Michael Hunley
Senior Engineer
PocketPurchase, Inc.