Re: [sqlite] AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

[Keith Medcalf]

On Thursday, 2 February, 2017 18:56, Richard Hipp  wrote:


> The behavior is correct.
 
> I have adjusted the documentation to try to avoid ambiguity.  See
> https://www.sqlite.org/docsrc/info/f6e2eab4e71644b1 for the
> documentation update.

The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before been inserted in that same table.

would perhaps something like:

The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before been inserted, or currently exists, in that same table.

be more accurate since newrowid = max(1, largestinserted, max(rowid)) + 1 ?

> On 2/2/17, Keith Medcalf  wrote:
> >
> > sqlite> create table x (key integer primary key, value text);
> > sqlite> insert into x values (null, 'test');
> > sqlite> update x set key=1 where value='test';
> > sqlite> select * from x;
> > 1|test
> > sqlite> delete from x;
> > sqlite> insert into x values (null, 'again');
> > sqlite> select * from x;
> > 1|again
> > sqlite> drop table x;
> > sqlite> create table x (key integer primary key autoincrement, value
> text);
> > sqlite> insert into x values (null, 'test');
> > sqlite> update x set key=1 where value='test';
> > sqlite> select * from x;
> > 1|test
> > sqlite> delete from x;
> > sqlite> insert into x values (null, 'again');
> > sqlite> select * from x;
> > 2|again
> > sqlite> drop table x;
> > sqlite> create table x (key integer primary key autoincrement, value
> text);
> > sqlite> insert into x values (null, 'test');
> > sqlite> update x set key=1 where value='test';
> > sqlite> select * from x;
> > 1|test
> > sqlite> insert into x values (null, 'again');
> > sqlite> select * from x;
> > 1|test
> > 10001|again
> > sqlite>
> >
> > Question is:  when the rowid alias is declared autoincrement, Should the
> > "update" update the hi-level mark for the key field?
> >
> > ---
> > Life should not be a journey to the grave with the intention of arriving
> > safely in a pretty and well preserved body, but rather to skid in
> broadside
> > in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> > proclaiming "Wow! What a Ride!"
> >  -- Hunter S. Thompson
> >
> >
> >
> >
> > ___
> > 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] AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

[Richard Hipp]

The behavior is correct.

I have adjusted the documentation to try to avoid ambiguity.  See
https://www.sqlite.org/docsrc/info/f6e2eab4e71644b1 for the
documentation update.

On 2/2/17, Keith Medcalf  wrote:
>
> sqlite> create table x (key integer primary key, value text);
> sqlite> insert into x values (null, 'test');
> sqlite> update x set key=1 where value='test';
> sqlite> select * from x;
> 1|test
> sqlite> delete from x;
> sqlite> insert into x values (null, 'again');
> sqlite> select * from x;
> 1|again
> sqlite> drop table x;
> sqlite> create table x (key integer primary key autoincrement, value text);
> sqlite> insert into x values (null, 'test');
> sqlite> update x set key=1 where value='test';
> sqlite> select * from x;
> 1|test
> sqlite> delete from x;
> sqlite> insert into x values (null, 'again');
> sqlite> select * from x;
> 2|again
> sqlite> drop table x;
> sqlite> create table x (key integer primary key autoincrement, value text);
> sqlite> insert into x values (null, 'test');
> sqlite> update x set key=1 where value='test';
> sqlite> select * from x;
> 1|test
> sqlite> insert into x values (null, 'again');
> sqlite> select * from x;
> 1|test
> 10001|again
> sqlite>
>
> Question is:  when the rowid alias is declared autoincrement, Should the
> "update" update the hi-level mark for the key field?
>
> ---
> Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in broadside
> in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
> ___
> 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] AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

[Keith Medcalf]

sqlite> create table x (key integer primary key, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=1 where value='test';
sqlite> select * from x;
1|test
sqlite> delete from x;
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
1|again
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=1 where value='test';
sqlite> select * from x;
1|test
sqlite> delete from x;
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
2|again
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=1 where value='test';
sqlite> select * from x;
1|test
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
1|test
10001|again
sqlite>

Question is:  when the rowid alias is declared autoincrement, Should the 
"update" update the hi-level mark for the key field?

---
Life should not be a journey to the grave with the intention of arriving safely 
in a pretty and well preserved body, but rather to skid in broadside in a cloud 
of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! 
What a Ride!"
 -- Hunter S. Thompson




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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Keith Medcalf]

This has been the case since Microsoft stole SQL Server from Sybase (where it 
is/was the case as well).

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of James K. Lowden
> Sent: Thursday, 3 November, 2016 19:50
> To: SQLite mailing list
> Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE
> 
> On Tue, 1 Nov 2016 11:01:24 +
> Simon Slavin <slav...@bigfraud.org> wrote:
> 
> > attempts to change a value in that column using UPDATE always
> > generate an error.  I didn't know that.  I looked it up.  Apparently
> > Microsoft's SQLSERVER blocks it
> 
> Blocks but does not prevent.
> https://msdn.microsoft.com/en-us/library/ms188059.aspx
> 
> --jkl
> ___
> 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] Autoincrement sequence not updated by UPDATE

[James K. Lowden]

On Tue, 1 Nov 2016 11:01:24 +
Simon Slavin  wrote:

> attempts to change a value in that column using UPDATE always
> generate an error.  I didn't know that.  I looked it up.  Apparently
> Microsoft's SQLSERVER blocks it

Blocks but does not prevent.
https://msdn.microsoft.com/en-us/library/ms188059.aspx

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[David Raymond]

sqlite> create table tbl1
   ...> (
   ...>   id integer primary key autoincrement,
   ...>   someOtherfield,
   ...>   yetAnotherField
   ...> );

sqlite> create trigger trg_imInChargeAndSayNoAutoincrementUpdates
   ...> before update of id on tbl1
   ...> begin
   ...>   select raise(abort, 'Bad dog, no biscuit.');
   ...> end;

sqlite> insert into tbl1 (someOtherField, yetAnotherField) values (1, 2);

sqlite> select * from tbl1;
id|someField|someOtherField
1|1|2

sqlite> update tbl1 set id = 2 where id = 1;
Error: Bad dog, no biscuit.






sqlite> create table tbl2
   ...> (
   ...>   id integer primary key autoincrement,
   ...>   someOtherField,
   ...>   yetAnotherField
   ...> );

sqlite> create trigger trg_imInChargeAndWantToUpdateMyAutoincrementFields
   ...> after update of id on tbl2
   ...> when new.id > (select seq from sqlite_sequence where name = 'tbl2')
   ...> begin
   ...>   update sqlite_sequence
   ...>   set seq = new.id where name = 'tbl2';
   ...> end;

sqlite> insert into tbl2 (someOtherField, yetAnotherField) values (1, 2);

sqlite> select * from tbl2;
id|someOtherField|yetAnotherField
1|1|2

sqlite> select * from sqlite_sequence;
name|seq
tbl1|1
tbl2|1

sqlite> update tbl2 set id = 5 where id = 1;

sqlite> select * from tbl2;
id|someOtherField|yetAnotherField
5|1|2

sqlite> select * from sqlite_sequence;
name|seq
tbl1|1
tbl2|5
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

Interesting. I am using an old version, 5.5.34

The database engine may also make a difference. I was using MyISAM. INNODB may 
be different.

A quick scan of the MySQL documentation suggests INNODB is different. For 
example, it doesn't
store the next value in the database.


If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle 
in the InnoDB data dictionary contains a special counter called the 
auto-increment counter that is used in assigning new values for the column. 
This counter is stored only in main memory, not on disk.

To initialize an auto-increment counter after a server restart, InnoDB executes 
the equivalent of the following statement on the first insert into a table 
containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

Simon's requested table is likely to get quite complicated :^)

I suspect all you can really say about auto increment is that it will create a 
unique number.


Andy


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tony Papadimitriou
Sent: Tue 01 November 2016 13:09
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE

Well, I got different results (so maybe it's version related).  I tried it 
on MySQL v5.7.16-log:
++--+
| id | v|
++--+
|  2 | two  |
| 10 | one  |
++--+
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
++---+
| id | v |
++---+
|  3 | three |
| 10 | one   |
| 30 | two   |
++---+

-Original Message- 
From: Simon Slavin
Sent: Tuesday, November 01, 2016 1:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:44am, Andy Ling <andy.l...@s-a-m.com> wrote:

> It remembers..

Ah, neat.  Thanks for the testing.  And the "show create table" command you 
used makes it clear that the engine keeps a record for the table. 
Apparently a single value for the table's primary key rather than a value 
for each "INTEGER PRIMARY KEY AUTOINCREMENT" column.

I'd be interested in the equivalents for progresql and Oracle, if anyone is 
set up to find out.  Maybe we could work up a table like the one in

<https://www.sqlite.org/nulls.html>

Simon.
___
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
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Tony Papadimitriou]

Well, I got different results (so maybe it's version related).  I tried it 
on MySQL v5.7.16-log:

++--+
| id | v|
++--+
|  2 | two  |
| 10 | one  |
++--+
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
++---+
| id | v |
++---+
|  3 | three |
| 10 | one   |
| 30 | two   |
++---+

-Original Message- 
From: Simon Slavin

Sent: Tuesday, November 01, 2016 1:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:44am, Andy Ling <andy.l...@s-a-m.com> wrote:


It remembers..


Ah, neat.  Thanks for the testing.  And the "show create table" command you 
used makes it clear that the engine keeps a record for the table. 
Apparently a single value for the table's primary key rather than a value 
for each "INTEGER PRIMARY KEY AUTOINCREMENT" column.


I'd be interested in the equivalents for progresql and Oracle, if anyone is 
set up to find out.  Maybe we could work up a table like the one in


<https://www.sqlite.org/nulls.html>

Simon.
___
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] Autoincrement sequence not updated by UPDATE

[Andy Ling]

>> It remembers..

>Ah, neat.  Thanks for the testing.

As a slight aside. It also never resets the value. We had a problem where the 
number of inserts
had incremented the AUTO INCREMENT value to MAXINT (it took a few years). It 
then stops.
We fixed it by changing id to a BIGINT, but you can also reset the auto 
increment value via SQL.

Regards

Andy
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

On 1 Nov 2016, at 11:44am, Andy Ling  wrote:

> It remembers..

Ah, neat.  Thanks for the testing.  And the "show create table" command you 
used makes it clear that the engine keeps a record for the table.  Apparently a 
single value for the table's primary key rather than a value for each "INTEGER 
PRIMARY KEY AUTOINCREMENT" column.

I'd be interested in the equivalents for progresql and Oracle, if anyone is set 
up to find out.  Maybe we could work up a table like the one in



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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

It remembers..

mysql> create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY 
KEY(id)) ;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> update tt set id=10 where v="one";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into tt (v) VALUES("two");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 11 | two  |
++--+
2 rows in set (0.00 sec)

mysql> update tt set id=40 where v="two";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
2 rows in set (0.00 sec)

mysql> update tt set id=30 where v="two";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into tt (v) VALUES("three");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++---+
| id | v |
++---+
| 10 | one   |
| 30 | two   |
| 41 | three |
++---+
3 rows in set (0.00 sec)

After doing this fiddling, show create table shows the auto increment value

mysql> show create table tt;
+---+-
| Table | Create Table
+---+-
| tt| CREATE TABLE `tt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 |
+---+-
1 row in set (0.00 sec)





-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tue 01 November 2016 11:42
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:14am, Andy Ling <andy.l...@s-a-m.com> wrote:

> MySQL lets you fiddle.

I don't have MySQL.  To satisfy my curiosity, could you try this:

create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ;
insert into tt (v) VALUES("one");
update tt set id=10 where v="one";
insert into tt (v) VALUES("two");
select * from tt;

You got this far already (though I changed the details a little).

Now add this:

update tt set id=40 where v="two";
select * from tt;
update tt set id=30 where v="two";
insert into tt (v) VALUES("three");
select * from tt;

I'm trying to find out whether it remembers that there once was a value that 
was 40, even if the highest value present when you do the insert is 30.  My 
guess is that it'll assign an id of 41 to row three, but I might be wrong.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[R Smith]

On 2016/11/01 1:01 PM, Simon Slavin wrote:

On 1 Nov 2016, at 10:45am, R Smith  wrote:


  D - Horrible if you up some key value significantly and then update it back 
down, because there is no way the Autoinc value should *EVER* be able/allowed 
to come back down. It's a one-way street.

This is an additional argument for not allowing UPDATE to change an "INTEGER PRIMARY 
KEY AUTOINCREMENT" value.


Well, either that, or an argument for not letting UPDATE dictate the 
next incremental key value. While I think this argument carries equal 
merit both ways (perhaps leaning more towards your suggestion), the 
consequence of disallowing a column to be updated is too great a 
disadvantage in my opinion (Even if it didn't break backwards 
compatibility).




Which, according to GB, is what some other SQL engines do: attempts to change a 
value in that column using UPDATE always generate an error.  I didn't know 
that.  I looked it up.  Apparently Microsoft's SQLSERVER blocks it, but I was 
unable to find anything mentioning how any of the other big SQL engines handles 
it.


Indeed, though MSSQL maintains what it calls "Identity" columns, which 
(IIUC) is like Autoincrement Primary Key (but need not be the official 
PK) that cannot be Updated. I don't know enough of it as I tend to avoid 
it when using MSSQL. MySQL definitely allows updates to Autoinc columns, 
and even updates the next key. Not sure about PostGres, but it is 
clearly not a blanket assumption that can be made. Seems everybody has 
their own take, which is ok, it just emphasizes what I tried to point 
out previously - This AUTOINC thing is a luxury item, not a mandated 
one, and one best avoided when you can roll your own.


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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

On 1 Nov 2016, at 11:14am, Andy Ling  wrote:

> MySQL lets you fiddle.

I don't have MySQL.  To satisfy my curiosity, could you try this:

create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ;
insert into tt (v) VALUES("one");
update tt set id=10 where v="one";
insert into tt (v) VALUES("two");
select * from tt;

You got this far already (though I changed the details a little).

Now add this:

update tt set id=40 where v="two";
select * from tt;
update tt set id=30 where v="two";
insert into tt (v) VALUES("three");
select * from tt;

I'm trying to find out whether it remembers that there once was a value that 
was 40, even if the highest value present when you do the insert is 30.  My 
guess is that it'll assign an id of 41 to row three, but I might be wrong.

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

> Which, according to GB, is what some other SQL engines do: attempts to change 
> a value
>  in that column using UPDATE always generate an error.  I didn't know that.  
> I looked it up.
>   Apparently Microsoft's SQLSERVER blocks it, but I was unable to find 
> anything mentioning
>  how any of the other big SQL engines handles it.

MySQL lets you fiddle.

mysql> create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY 
KEY(id)) ;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
|  1 | one  |
++--+
1 row in set (0.00 sec)

mysql> update tt set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
++--+
1 row in set (0.00 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 11 | one  |
++--+
2 rows in set (0.00 sec)
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Richard Damon]

On 11/1/16 7:01 AM, Simon Slavin wrote:

On 1 Nov 2016, at 10:45am, R Smith  wrote:


  D - Horrible if you up some key value significantly and then update it back 
down, because there is no way the Autoinc value should *EVER* be able/allowed 
to come back down. It's a one-way street.

This is an additional argument for not allowing UPDATE to change an "INTEGER PRIMARY 
KEY AUTOINCREMENT" value.

Which, according to GB, is what some other SQL engines do: attempts to change a 
value in that column using UPDATE always generate an error.  I didn't know 
that.  I looked it up.  Apparently Microsoft's SQLSERVER blocks it, but I was 
unable to find anything mentioning how any of the other big SQL engines handles 
it.

Simon.
MySQL sets the increment to max+1 ever used, and uses the auto=increment 
value any time you try to set the field to 0 (other values get written 
at provided, assuming they are unique). This can be handy when you 
create the table schema and then restore data that was saved from a 
previous version of the table, The restored data has values for the 
field, which you want to be used, and may not use every number as some 
records might have been deleted. If it strictly just used the 
auto-increment value then any use of that as a foreign key in some other 
table gets broken.


--
Richard Damon

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

On 1 Nov 2016, at 10:45am, R Smith  wrote:

>  D - Horrible if you up some key value significantly and then update it back 
> down, because there is no way the Autoinc value should *EVER* be able/allowed 
> to come back down. It's a one-way street.

This is an additional argument for not allowing UPDATE to change an "INTEGER 
PRIMARY KEY AUTOINCREMENT" value.

Which, according to GB, is what some other SQL engines do: attempts to change a 
value in that column using UPDATE always generate an error.  I didn't know 
that.  I looked it up.  Apparently Microsoft's SQLSERVER blocks it, but I was 
unable to find anything mentioning how any of the other big SQL engines handles 
it.

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[R Smith]

On 2016/11/01 11:52 AM, Simon Slavin wrote:


Unfortunately the response is going to be along the lines of "We can't do this for 
compatibility reasons because there might be a program out there that does it.".


And rightly so...

There is no reason to use Autoincrement other to be sure newly added 
items (in consecutive inserts) get an automatic next number as an 
integer primary key that was never used before in an insert. I have been 
vocal on here about how that should not really be left up to the DB 
engine, you should really assign your own numbering, but, for easiness 
sake, using the DB is fine.


Then, someone starts expecting miracles from the mechanism, which it may 
provide by virtue of doing some fun things at a cost, and then someone 
starts wanting it to "Lock down" the field because now the fun things 
shouldn't always happen because


I for one have many systems which update the Autoinc key, such as 
inserting a new value in between two others and the like. It would break 
bad if it locks up. I also do not wish the autoinc value to update 
during updates, because I may alter all of them in a single transaction 
and then back again after some shuffling and the like, at the end of 
which the Autoinc next value should still be what it was before the 
updates - not to mention saving valuable CPU cycles. On the occasion 
that I DO need it to update, amending the sequence table that keeps the 
next autoinc value is a simple thing to do.


Moral of the story: Don't use Autoincrement, and if you /have/ to - 
control it well and test it well, and rest assured that updates won't 
affect the next key value - unless you want to, in which case you can 
simply pop the MAX(...)+1 into the autoinc keys control table 
(sqlite_sequence).


An alternative would be to have UPDATE actually write the sequence 
table, and then issue another update to it if you /didn't/ want the next 
key to change - but that is:

  A - Contrary to how it currently works (backwards compatibility etc.),
  B - Not a huge audience for this method (14+ years of not a single 
such a request),

  C - Heavier on CPU cycles, and
  D - Horrible if you up some key value significantly and then update 
it back down, because there is no way the Autoinc value should *EVER* be 
able/allowed to come back down. It's a one-way street.



cheers,
Ryan

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

On 1 Nov 2016, at 6:56am, GB  wrote:

> And that is why several SQL Engines prevent AUTOINCREMENT columns from being 
> UPDATEd.

I think that would be a good fix in SQLite's case.  If you're relying on SQLite 
to generate these unique numbers for you then you shouldn't then change your 
mind and try to correct one of its attempts.

Unfortunately the response is going to be along the lines of "We can't do this 
for compatibility reasons because there might be a program out there that does 
it.".  Roll on SQLite 4 in which this argument cannot apply.

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Niall O'Reilly]

On 28 Oct 2016, at 12:47, Simon Slavin wrote:

It guess it comes down to what one wants from "INTEGER PRIMARY KEY 
AUTOINCREMENT". If the requirement is only-ever-increasing then this 
is a bug.


  The behaviour described at https://sqlite.org/autoinc.html seems to 
match this requirement:


  If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a 
slightly different
  ROWID selection algorithm is used. The ROWID chosen for the new row 
is at least one
  larger than the largest ROWID that has ever before existed in that 
same table.


  It looks to me as if there is a bug, and I'ld prefer to see the 
behaviour reported by

  the OP as buggy, rather than what is documented.

  Best regards,
  Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[GB]

And that is why several SQL Engines prevent AUTOINCREMENT columns from 
being UPDATEd.


I think it should be fixed in the Docs, not in Code.


Richard Hipp schrieb am 31.10.2016 um 18:56:


Note that the implied purpose of AUTOINCREMENT is to generate a unique
and immutable identifier.  Running an UPDATE on an immutable
identifier breaks the contract.  Even though this contract was not
previously stated in the documentation, apparently most people
understood it because this issue has never come up before in 14 years
of heavy use.

Thanks for pointing out the problem.


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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Don V Nielsen]

> It guess it comes down to what one wants from "INTEGER PRIMARY KEY
AUTOINCREMENT"

What I would want, ...expect, is that a primary key autoincrement column
would be left completely alone. And if was altered, it was altered on
accident. I always thought "integer primary key" was synonymous with
__rowid__.  What application would want to mess with that?



On Mon, Oct 31, 2016 at 12:56 PM, Richard Hipp  wrote:

> On 10/27/16, Adam Goldman  wrote:
> > I expected the test case below to print 5679, but it prints 1235
> > instead. I tested under a few versions including 3.15.0. It's a bit of a
> > corner case and I worked around it in my application, but I guess it's a
> > bug.
> >
> > CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
> > INSERT INTO foo (bar) VALUES(1234);
> > UPDATE foo SET bar=5678;
> > DELETE FROM foo;
> > INSERT INTO foo DEFAULT VALUES;
> > SELECT * FROM foo;
>
> Yes, this is a discrepancy between the implementation and the
> documentation.  But the implementation of AUTOINCREMENT has *never*
> before modified the content of the sqlite_sequence table on an UPDATE,
> since the AUTOINCREMENT feature was first introduced in 2002 (SQLite
> version 2.5.2). For 14 years, AUTOINCREMENT has always worked as it
> does in 3.15.0.  If we "fix" the implementation now, we run a risk of
> breaking some of the millions of applications that use AUTOINCREMENT.
> For that reason, we have tentatively decided to update the
> documentation rather than the code.
>
> Note that the implied purpose of AUTOINCREMENT is to generate a unique
> and immutable identifier.  Running an UPDATE on an immutable
> identifier breaks the contract.  Even though this contract was not
> previously stated in the documentation, apparently most people
> understood it because this issue has never come up before in 14 years
> of heavy use.
>
> Thanks for pointing out the problem.
> --
> 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] Autoincrement sequence not updated by UPDATE

[Richard Hipp]

On 10/27/16, Adam Goldman  wrote:
> I expected the test case below to print 5679, but it prints 1235
> instead. I tested under a few versions including 3.15.0. It's a bit of a
> corner case and I worked around it in my application, but I guess it's a
> bug.
>
> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
> INSERT INTO foo (bar) VALUES(1234);
> UPDATE foo SET bar=5678;
> DELETE FROM foo;
> INSERT INTO foo DEFAULT VALUES;
> SELECT * FROM foo;

Yes, this is a discrepancy between the implementation and the
documentation.  But the implementation of AUTOINCREMENT has *never*
before modified the content of the sqlite_sequence table on an UPDATE,
since the AUTOINCREMENT feature was first introduced in 2002 (SQLite
version 2.5.2). For 14 years, AUTOINCREMENT has always worked as it
does in 3.15.0.  If we "fix" the implementation now, we run a risk of
breaking some of the millions of applications that use AUTOINCREMENT.
For that reason, we have tentatively decided to update the
documentation rather than the code.

Note that the implied purpose of AUTOINCREMENT is to generate a unique
and immutable identifier.  Running an UPDATE on an immutable
identifier breaks the contract.  Even though this contract was not
previously stated in the documentation, apparently most people
understood it because this issue has never come up before in 14 years
of heavy use.

Thanks for pointing out the problem.
-- 
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] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
sqlite> INSERT INTO foo (bar) VALUES(1234);
sqlite> SELECT * FROM foo;
1234
sqlite> UPDATE foo SET bar=5678;
sqlite> SELECT * FROM foo;
5678
sqlite> DELETE FROM foo;
sqlite> SELECT * FROM foo;
sqlite> INSERT INTO foo DEFAULT VALUES;
sqlite> SELECT * FROM foo;
1235
sqlite> 

Suggests that in 3.14.0 the autoincrement number isn't changed by UPDATE.

It guess it comes down to what one wants from "INTEGER PRIMARY KEY 
AUTOINCREMENT". If the requirement is only-ever-increasing then this is a bug.

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Stephen Chrzanowski]

Works here;

SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
sqlite> INSERT INTO foo (bar) VALUES(1234);
sqlite> SELECT * FROM foo;
1234
sqlite> UPDATE foo SET bar=5678;
sqlite> SELECT * FROM foo;
5678
sqlite> DELETE FROM foo;
sqlite> SELECT * FROM foo;
sqlite> INSERT INTO foo DEFAULT VALUES;
sqlite> SELECT * FROM foo;

And in one transaction;
sqlite> begin;
sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
sqlite> INSERT INTO foo (bar) VALUES(1234);
sqlite> SELECT * FROM foo;
1234
sqlite> UPDATE foo SET bar=5678;
sqlite> SELECT * FROM foo;
5678
sqlite> DELETE FROM foo;
sqlite> SELECT * FROM foo;
sqlite> INSERT INTO foo DEFAULT VALUES;
sqlite> SELECT * FROM foo;
1235
sqlite> commit;


On Fri, Oct 28, 2016 at 2:46 AM, Radovan Antloga 
wrote:

> After line:
> UPDATE foo SET bar=5678;
>
> put this sql command:
> COMMIT;
>
> If you execute all statements in one sql
> (except last), they are executed in one transaction.
>
> Regards
> Radovan
>
>
> Adam Goldman je 27.10.2016 ob 11:52 napisal:
>
> Hi,
>>
>> I expected the test case below to print 5679, but it prints 1235
>> instead. I tested under a few versions including 3.15.0. It's a bit of a
>> corner case and I worked around it in my application, but I guess it's a
>> bug.
>>
>> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
>> INSERT INTO foo (bar) VALUES(1234);
>> UPDATE foo SET bar=5678;
>> DELETE FROM foo;
>> INSERT INTO foo DEFAULT VALUES;
>> SELECT * FROM foo;
>>
>> -- Adam
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Radovan Antloga]

After line:
UPDATE foo SET bar=5678;

put this sql command:
COMMIT;

If you execute all statements in one sql
(except last), they are executed in one transaction.

Regards
Radovan


Adam Goldman je 27.10.2016 ob 11:52 napisal:

Hi,

I expected the test case below to print 5679, but it prints 1235
instead. I tested under a few versions including 3.15.0. It's a bit of a
corner case and I worked around it in my application, but I guess it's a
bug.

CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO foo (bar) VALUES(1234);
UPDATE foo SET bar=5678;
DELETE FROM foo;
INSERT INTO foo DEFAULT VALUES;
SELECT * FROM foo;

-- Adam
___
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] Autoincrement sequence not updated by UPDATE

[Adam Goldman]

Hi,

I expected the test case below to print 5679, but it prints 1235 
instead. I tested under a few versions including 3.15.0. It's a bit of a 
corner case and I worked around it in my application, but I guess it's a 
bug.

CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO foo (bar) VALUES(1234);
UPDATE foo SET bar=5678;
DELETE FROM foo;
INSERT INTO foo DEFAULT VALUES;
SELECT * FROM foo;

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

[sqlite] autoincrement

[Igor Korot]

Hi,

On Wed, Apr 13, 2016 at 1:54 PM, Igor Korot  wrote:
> Hi, Peter,
>
> On Wed, Apr 13, 2016 at 1:16 PM, Peter Aronson  wrote:
>> There is one limitation to this approach, however.  The entry for an 
>> autoincrement column in the sqlite_sequence table isn't made until the first 
>> row is inserted into the table.  If you are also using the C interface, you 
>> can identify autoincrement columns using sqlite3_table_column_metadata.
>
> I will test that when I come home from work.

Yes, that didn't work.

Can you tell me how to use sqlite3_table_column_metadata?

Thank you.

>
> Thank you.
>
>>
>> Peter
>>
>> On Wednesday, April 13, 2016 9:40 AM, Igor Korot  
>> wrote:
>>
>>
>>
>>  Hi, Kees,
>>
>> On Wed, Apr 13, 2016 at 12:15 PM, Kees Nuyt  wrote:
>>> On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
>>>  wrote:
>>>
 Hi,,

On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
> Yes, you can get the create statement from sqlite_master table

 I was kind of hoping for a simpler solution so that not to
 parse "CREATE TABLE" statement...

 Well, I guess I will have to.
>>>
>>> With AUTOINCREMENT, the last autoincremented primary key value
>>> is tracked in a table called sqlite_sequence.
>>>
>>> $ sqlite3 test2.db
>>> SQLite version 3.8.12 2015-10-07 00:35:18
>>> Enter ".help" for usage hints.
>>> sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
>>> sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
>>> tx TEXT);
>>> sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
>>> sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
>>> sqlite> SELECT name FROM sqlite_master WHERE type='table';
>>> t1
>>> t2
>>> sqlite_sequence
>>> sqlite> SELECT * FROM sqlite_sequence;
>>> t2|2
>>> sqlite>
>>
>> So I can actually query sqlite_sequence table with the table name in WHERE
>> clause and it should give me the field that is set to be autoincrement.
>>
>> For the reference: https://www.sqlite.org/fileformat2.html (paragraph 2.6.2).
>>
>> Thank you.
>>
>> P.S.: There is a small typo on the page/paragraph I referenced:
>>
>> [quote]
>> The schema for the sqlite_sequence table is:
>>
>> CREATE TABLE sqlite_sequence(name,seq);
>>
>> There is a single row in the sqlite_sequence table for each ordinary
>> table that uses AUTOINCREMENT. The name of the table (as it appears in
>> sqlite_master.name) is in the sqlite_sequence.main field and the
>> largest INTEGER PRIMARY KEY ever used by that table is in the
>> sqlite_sequence.seq field.
>> [/quote]
>>
>> That should be changed to:
>>
>> [changes}
>> The name of the table (as it appears in sqlite_master.name) is in the
>> sqlite_sequence._name_ field
>> [/changes]
>>
>> Could someone please correct that? The changes are between the underscores.
>>
>> Thank you.
>>
>>>
>>> Hope this helps
>>>
>>>
>
> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
>> Hi,
>> Is it possible to get whether the column is set to autoincrement or not?
>>
>> PRAGMA table_info() does not give such info...
>>
>> Thank you.
>>>
>>> --
>>> Regards,
>>> Kees Nuyt
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoincrement

[R Smith]

On 2016/04/13 4:58 PM, Igor Korot wrote:
>   Hi,,
>
> On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
>> Yes, you can get the create statement from sqlite_master table
> I was kind of hoping for a simpler solution so that not to parse "CREATE 
> TABLE"
> statement...
>
> Well, I guess I will have to.
>
> Thank you.

Hi Igor,

Note that SQLite will auto increment PK fields whether AUTOINCREMENT is 
present or not, it just ensures non-repeats and guaranteed increments 
when present.

Either way, this following query will give the column in any table that 
is AUTOINCREMENTed (if any), just replace 'AutoIncTable' with your table 
name (or bind it).

WITH WS(WhSpc) AS (
 SELECT char(9)||char(10)||char(13)||char(32) -- White space chars
), AI(idxStart, idxAuto, sql) AS (
 SELECT instr(sql,'('), instr(upper(sql),' AUTOINCREMENT'), sql
   FROM "sqlite_master" WHERE type = 'table' AND tbl_name =
'AutoIncTable' -- < Replace your table here
), PRS(idxNextComma, remainText) AS (
 SELECT 1, ','||trim(substr(sql,idxStart+1,idxAuto-idxStart),WhSpc)
   FROM AI,WS
  WHERE idxStart>1 AND idxAuto>idxStart
   UNION ALL
 SELECT instr(substr(trim(remainText,WhSpc),
idxNextComma+1),','), substr(trim(remainText,WhSpc), idxNextComma+1)
   FROM PRS,WS
  WHERE idxNextComma > 0
), WRD(idxSpace, remText) AS (
 SELECT 999, remainText FROM PRS WHERE idxNextComma = 0
   UNION ALL
 SELECT instr(trim(substr(remText, 1, idxSpace)),' '),
trim(substr(remText, 1, idxSpace)) FROM WRD WHERE idxSpace > 0
)
SELECT trim(remText,'['' "`]') AS AutoInc_Col FROM WRD WHERE
idxSPace = 0



Note 1 - You CAN break it by adding /* comments */  in between the 
column name and the AUTOINCREMENT keyword in your table definition, and 
then have the comment contain the said keyword or commas, but this 
should not ever be the case in a normal table, less so if you control 
the schema.

Note 2 - Parsing out comments first would make it foolproof, but I will 
leave that exercise for if you really need it and/or fancy actually 
using this method.

Note 3 - It's not crazy efficient, but usually the amount of tables in a 
schema is not that large.


Cheers,
Ryan

[sqlite] autoincrement

[Peter Aronson]

The documentation for sqlite3_table_column_metadata C function can be 
found here; https://www.sqlite.org/c3ref/table_column_metadata.html.   
You just call it in turn on each column in a table (you can get the 
column names for a table by using Pragma table_info) and check the value 
of the 9th argument.

Peter

On 4/13/2016 7:58 PM, Igor Korot wrote:
> Hi,
>
> On Wed, Apr 13, 2016 at 1:54 PM, Igor Korot  wrote:
>> Hi, Peter,
>>
>> On Wed, Apr 13, 2016 at 1:16 PM, Peter Aronson  wrote:
>>> There is one limitation to this approach, however.  The entry for an 
>>> autoincrement column in the sqlite_sequence table isn't made until the 
>>> first row is inserted into the table.  If you are also using the C 
>>> interface, you can identify autoincrement columns using 
>>> sqlite3_table_column_metadata.
>> I will test that when I come home from work.
> Yes, that didn't work.
>
> Can you tell me how to use sqlite3_table_column_metadata?
>
> Thank you.
>
>> Thank you.
>>
>>> Peter
>>>
>>>  On Wednesday, April 13, 2016 9:40 AM, Igor Korot >> gmail.com> wrote:
>>>
>>>
>>>
>>>   Hi, Kees,
>>>
>>> On Wed, Apr 13, 2016 at 12:15 PM, Kees Nuyt  wrote:
 On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
  wrote:

> Hi,,
>
> On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
>> Yes, you can get the create statement from sqlite_master table
> I was kind of hoping for a simpler solution so that not to
> parse "CREATE TABLE" statement...
>
> Well, I guess I will have to.
 With AUTOINCREMENT, the last autoincremented primary key value
 is tracked in a table called sqlite_sequence.

 $ sqlite3 test2.db
 SQLite version 3.8.12 2015-10-07 00:35:18
 Enter ".help" for usage hints.
 sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
 sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
 tx TEXT);
 sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
 sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
 sqlite> SELECT name FROM sqlite_master WHERE type='table';
 t1
 t2
 sqlite_sequence
 sqlite> SELECT * FROM sqlite_sequence;
 t2|2
 sqlite>
>>> So I can actually query sqlite_sequence table with the table name in WHERE
>>> clause and it should give me the field that is set to be autoincrement.
>>>
>>> For the reference: https://www.sqlite.org/fileformat2.html (paragraph 
>>> 2.6.2).
>>>
>>> Thank you.
>>>
>>> P.S.: There is a small typo on the page/paragraph I referenced:
>>>
>>> [quote]
>>> The schema for the sqlite_sequence table is:
>>>
>>> CREATE TABLE sqlite_sequence(name,seq);
>>>
>>> There is a single row in the sqlite_sequence table for each ordinary
>>> table that uses AUTOINCREMENT. The name of the table (as it appears in
>>> sqlite_master.name) is in the sqlite_sequence.main field and the
>>> largest INTEGER PRIMARY KEY ever used by that table is in the
>>> sqlite_sequence.seq field.
>>> [/quote]
>>>
>>> That should be changed to:
>>>
>>> [changes}
>>> The name of the table (as it appears in sqlite_master.name) is in the
>>> sqlite_sequence._name_ field
>>> [/changes]
>>>
>>> Could someone please correct that? The changes are between the underscores.
>>>
>>> Thank you.
>>>
 Hope this helps


>> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  
>> wrote:
>>> Hi,
>>> Is it possible to get whether the column is set to autoincrement or not?
>>>
>>> PRAGMA table_info() does not give such info...
>>>
>>> Thank you.
 --
 Regards,
 Kees Nuyt
 ___
 sqlite-users mailing list
 sqlite-users at mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] autoincrement

[Kees Nuyt]

On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
 wrote:

> Hi,,
>
>On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
>> Yes, you can get the create statement from sqlite_master table
>
> I was kind of hoping for a simpler solution so that not to
> parse "CREATE TABLE" statement...
>
> Well, I guess I will have to.

With AUTOINCREMENT, the last autoincremented primary key value
is tracked in a table called sqlite_sequence.

$ sqlite3 test2.db
SQLite version 3.8.12 2015-10-07 00:35:18
Enter ".help" for usage hints.
sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
tx TEXT);
sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
sqlite> SELECT name FROM sqlite_master WHERE type='table';
t1
t2
sqlite_sequence
sqlite> SELECT * FROM sqlite_sequence;
t2|2
sqlite>

Hope this helps


>>
>> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
>>> Hi,
>>> Is it possible to get whether the column is set to autoincrement or not?
>>>
>>> PRAGMA table_info() does not give such info...
>>>
>>> Thank you.

-- 
Regards,
Kees Nuyt

[sqlite] autoincrement

[Peter Aronson]

There is one limitation to this approach, however. ?The entry for an 
autoincrement column in the sqlite_sequence table isn't made until the first 
row is inserted into the table. ?If you are also using the C interface, you can 
identify autoincrement columns using?sqlite3_table_column_metadata.

Peter 

On Wednesday, April 13, 2016 9:40 AM, Igor Korot  
wrote:



 Hi, Kees,

On Wed, Apr 13, 2016 at 12:15 PM, Kees Nuyt  wrote:
> On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
>  wrote:
>
>> Hi,,
>>
>>On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
>>> Yes, you can get the create statement from sqlite_master table
>>
>> I was kind of hoping for a simpler solution so that not to
>> parse "CREATE TABLE" statement...
>>
>> Well, I guess I will have to.
>
> With AUTOINCREMENT, the last autoincremented primary key value
> is tracked in a table called sqlite_sequence.
>
> $ sqlite3 test2.db
> SQLite version 3.8.12 2015-10-07 00:35:18
> Enter ".help" for usage hints.
> sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
> sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
> tx TEXT);
> sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
> sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
> sqlite> SELECT name FROM sqlite_master WHERE type='table';
> t1
> t2
> sqlite_sequence
> sqlite> SELECT * FROM sqlite_sequence;
> t2|2
> sqlite>

So I can actually query sqlite_sequence table with the table name in WHERE
clause and it should give me the field that is set to be autoincrement.

For the reference: https://www.sqlite.org/fileformat2.html (paragraph 2.6.2).

Thank you.

P.S.: There is a small typo on the page/paragraph I referenced:

[quote]
The schema for the sqlite_sequence table is:

CREATE TABLE sqlite_sequence(name,seq);

There is a single row in the sqlite_sequence table for each ordinary
table that uses AUTOINCREMENT. The name of the table (as it appears in
sqlite_master.name) is in the sqlite_sequence.main field and the
largest INTEGER PRIMARY KEY ever used by that table is in the
sqlite_sequence.seq field.
[/quote]

That should be changed to:

[changes}
The name of the table (as it appears in sqlite_master.name) is in the
sqlite_sequence._name_ field
[/changes]

Could someone please correct that? The changes are between the underscores.

Thank you.

>
> Hope this helps
>
>
>>>
>>> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
 Hi,
 Is it possible to get whether the column is set to autoincrement or not?

 PRAGMA table_info() does not give such info...

 Thank you.
>
> --
> Regards,
> Kees Nuyt
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoincrement

[Igor Korot]

Hi, Peter,

On Wed, Apr 13, 2016 at 1:16 PM, Peter Aronson  wrote:
> There is one limitation to this approach, however.  The entry for an 
> autoincrement column in the sqlite_sequence table isn't made until the first 
> row is inserted into the table.  If you are also using the C interface, you 
> can identify autoincrement columns using sqlite3_table_column_metadata.

I will test that when I come home from work.

Thank you.

>
> Peter
>
> On Wednesday, April 13, 2016 9:40 AM, Igor Korot  
> wrote:
>
>
>
>  Hi, Kees,
>
> On Wed, Apr 13, 2016 at 12:15 PM, Kees Nuyt  wrote:
>> On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
>>  wrote:
>>
>>> Hi,,
>>>
>>>On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
 Yes, you can get the create statement from sqlite_master table
>>>
>>> I was kind of hoping for a simpler solution so that not to
>>> parse "CREATE TABLE" statement...
>>>
>>> Well, I guess I will have to.
>>
>> With AUTOINCREMENT, the last autoincremented primary key value
>> is tracked in a table called sqlite_sequence.
>>
>> $ sqlite3 test2.db
>> SQLite version 3.8.12 2015-10-07 00:35:18
>> Enter ".help" for usage hints.
>> sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
>> sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
>> tx TEXT);
>> sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
>> sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
>> sqlite> SELECT name FROM sqlite_master WHERE type='table';
>> t1
>> t2
>> sqlite_sequence
>> sqlite> SELECT * FROM sqlite_sequence;
>> t2|2
>> sqlite>
>
> So I can actually query sqlite_sequence table with the table name in WHERE
> clause and it should give me the field that is set to be autoincrement.
>
> For the reference: https://www.sqlite.org/fileformat2.html (paragraph 2.6.2).
>
> Thank you.
>
> P.S.: There is a small typo on the page/paragraph I referenced:
>
> [quote]
> The schema for the sqlite_sequence table is:
>
> CREATE TABLE sqlite_sequence(name,seq);
>
> There is a single row in the sqlite_sequence table for each ordinary
> table that uses AUTOINCREMENT. The name of the table (as it appears in
> sqlite_master.name) is in the sqlite_sequence.main field and the
> largest INTEGER PRIMARY KEY ever used by that table is in the
> sqlite_sequence.seq field.
> [/quote]
>
> That should be changed to:
>
> [changes}
> The name of the table (as it appears in sqlite_master.name) is in the
> sqlite_sequence._name_ field
> [/changes]
>
> Could someone please correct that? The changes are between the underscores.
>
> Thank you.
>
>>
>> Hope this helps
>>
>>

 On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
> Hi,
> Is it possible to get whether the column is set to autoincrement or not?
>
> PRAGMA table_info() does not give such info...
>
> Thank you.
>>
>> --
>> Regards,
>> Kees Nuyt
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoincrement

[Igor Korot]

Hi, Kees,

On Wed, Apr 13, 2016 at 12:15 PM, Kees Nuyt  wrote:
> On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
>  wrote:
>
>> Hi,,
>>
>>On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
>>> Yes, you can get the create statement from sqlite_master table
>>
>> I was kind of hoping for a simpler solution so that not to
>> parse "CREATE TABLE" statement...
>>
>> Well, I guess I will have to.
>
> With AUTOINCREMENT, the last autoincremented primary key value
> is tracked in a table called sqlite_sequence.
>
> $ sqlite3 test2.db
> SQLite version 3.8.12 2015-10-07 00:35:18
> Enter ".help" for usage hints.
> sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
> sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
> tx TEXT);
> sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
> sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
> sqlite> SELECT name FROM sqlite_master WHERE type='table';
> t1
> t2
> sqlite_sequence
> sqlite> SELECT * FROM sqlite_sequence;
> t2|2
> sqlite>

So I can actually query sqlite_sequence table with the table name in WHERE
clause and it should give me the field that is set to be autoincrement.

For the reference: https://www.sqlite.org/fileformat2.html (paragraph 2.6.2).

Thank you.

P.S.: There is a small typo on the page/paragraph I referenced:

[quote]
The schema for the sqlite_sequence table is:

CREATE TABLE sqlite_sequence(name,seq);

There is a single row in the sqlite_sequence table for each ordinary
table that uses AUTOINCREMENT. The name of the table (as it appears in
sqlite_master.name) is in the sqlite_sequence.main field and the
largest INTEGER PRIMARY KEY ever used by that table is in the
sqlite_sequence.seq field.
[/quote]

That should be changed to:

[changes}
The name of the table (as it appears in sqlite_master.name) is in the
sqlite_sequence._name_ field
[/changes]

Could someone please correct that? The changes are between the underscores.

Thank you.

>
> Hope this helps
>
>
>>>
>>> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
 Hi,
 Is it possible to get whether the column is set to autoincrement or not?

 PRAGMA table_info() does not give such info...

 Thank you.
>
> --
> Regards,
> Kees Nuyt
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoincrement

[Igor Korot]

 Hi,,

On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
> Yes, you can get the create statement from sqlite_master table

I was kind of hoping for a simpler solution so that not to parse "CREATE TABLE"
statement...

Well, I guess I will have to.

Thank you.

>
> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
>> Hi,
>> Is it possible to get whether the column is set to autoincrement or not?
>>
>> PRAGMA table_info() does not give such info...
>>
>> Thank you.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoincrement

[J Decker]

Yes, you can get the create statement from sqlite_master table

On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
> Hi,
> Is it possible to get whether the column is set to autoincrement or not?
>
> PRAGMA table_info() does not give such info...
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] autoincrement

[Igor Korot]

Hi,
Is it possible to get whether the column is set to autoincrement or not?

PRAGMA table_info() does not give such info...

Thank you.

[sqlite] autoincrement field

[Levente Kovacs]

On Thu, 27 Aug 2015 23:40:15 +0200
Jean-Christophe Deschamps  wrote:

> http://www.sqlite.org/c3ref/last_insert_rowid.html is what you need. 

Yes, thanks a lot!

Lev

[sqlite] autoincrement field

[Jean-Christophe Deschamps]

At 23:25 27/08/2015, you wrote:
 >---
>I have a table structure like this:
>
>CREATE TABLE padstack (
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> pin_number INTEGER,
> name TEXT
>);
>
>Is there any way to get the 'id' of newly inserted row? My insert of 
>course
>not contains the 'id' field.
 >---

http://www.sqlite.org/c3ref/last_insert_rowid.html is what you need. 

[sqlite] autoincrement field

[Levente Kovacs]

I have a table structure like this:

CREATE TABLE padstack (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pin_number INTEGER,
name TEXT
);

Is there any way to get the 'id' of newly inserted row? My insert of course
not contains the 'id' field.

Thanks,
Lev

[sqlite] autoincrement field

[Richard Hipp]

On 8/27/15, Levente Kovacs  wrote:
> On Thu, 27 Aug 2015 23:40:15 +0200
> Jean-Christophe Deschamps  wrote:
>
>> http://www.sqlite.org/c3ref/last_insert_rowid.html is what you need.
>
> Yes, thanks a lot!

You should probably also read the documentation on AUTOINCREMENT
(https://www.sqlite.org/autoinc.html) since it probably does not do
what you think it does.


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


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] autoincrement field

[Igor Tandetnik]

On 8/27/2015 5:25 PM, Levente Kovacs wrote:
> Is there any way to get the 'id' of newly inserted row?

http://www.sqlite.org/c3ref/last_insert_rowid.html
http://www.sqlite.org/lang_corefunc.html#last_insert_rowid

-- 
Igor Tandetnik

Re: [sqlite] Autoincrement with rollback

[Koen Van Exem]

Hi Darren,

Thanks for explaining the internals.

I already assumed by my 2 small experiments
that this was the case but it's nice to have it confirmed.

With kind regards,
Koen



2014-11-11 12:05 GMT+01:00 Darren Duncan :

> On 2014-11-11 2:41 AM, Koen Van Exem wrote:
>
>> I find it a bit confusing because when you create
>> a PRIMARY KEY AUTOINCREMENT then a
>> table named sqlite_sequence is created.
>>
>> According to the SQL (2003) standard multiple sessions are
>> guaranteed to allocate distinct sequence values.
>> (even when rollbacks are involved)
>>
>
> See, this is the source of your confusion.  I will explain.
>
> 1.  Conceptually a sequence generator is just a database table with a
> single row and single column whose value is the integer.  When the
> generator produces the next value, it is like these 2 statements being done
> as an atomic unit: "update seqgentbl set theint = theint + 1" and "select
> theint from seqgentbl".
>
> 2.  The semantics that the SQL standard defines, and is commonplace with
> other SQL DBMSs, is that the aforementioned read+update of seqgentbl
> happens in its own autonomous database transaction that commits
> immediately, and serially prior to the main transaction that called upon
> the sequence generator.  This is why in those cases a rollback of the main
> transaction doesn't rollback the sequence generator, because semantically
> that happened prior to the current transaction and successfully committed.
>
> 3.  SQLite is different such that its read_update of seqgentbl happens
> within the current main transaction rather than a separate one, and
> therefore its actions rollback like anything else.
>
> So SQLite is purposefully being different than the SQL standard.  Partly
> this is because supporting the standard means having to support multiple
> concurrent transactions trying to write the database, in contrast to what
> SQLite actually does which is only supporting one writing transaction at a
> time.
>
> If you want to use SQLite like the SQL standard, then invoke the sequence
> generator first in its own transaction and remember the value, then use
> that remembered value in your main transaction that you explicitly do
> afterwards.
>
> Do you understand what's going on now?
>
> -- Darren Duncan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Koen Van Exem
+32 3301 3301
+32 498 51
k...@allors.com
http://www.allors.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[Darren Duncan]

On 2014-11-11 2:41 AM, Koen Van Exem wrote:

I find it a bit confusing because when you create
a PRIMARY KEY AUTOINCREMENT then a
table named sqlite_sequence is created.

According to the SQL (2003) standard multiple sessions are
guaranteed to allocate distinct sequence values.
(even when rollbacks are involved)


See, this is the source of your confusion.  I will explain.

1.  Conceptually a sequence generator is just a database table with a single row 
and single column whose value is the integer.  When the generator produces the 
next value, it is like these 2 statements being done as an atomic unit: "update 
seqgentbl set theint = theint + 1" and "select theint from seqgentbl".


2.  The semantics that the SQL standard defines, and is commonplace with other 
SQL DBMSs, is that the aforementioned read+update of seqgentbl happens in its 
own autonomous database transaction that commits immediately, and serially prior 
to the main transaction that called upon the sequence generator.  This is why in 
those cases a rollback of the main transaction doesn't rollback the sequence 
generator, because semantically that happened prior to the current transaction 
and successfully committed.


3.  SQLite is different such that its read_update of seqgentbl happens within 
the current main transaction rather than a separate one, and therefore its 
actions rollback like anything else.


So SQLite is purposefully being different than the SQL standard.  Partly this is 
because supporting the standard means having to support multiple concurrent 
transactions trying to write the database, in contrast to what SQLite actually 
does which is only supporting one writing transaction at a time.


If you want to use SQLite like the SQL standard, then invoke the sequence 
generator first in its own transaction and remember the value, then use that 
remembered value in your main transaction that you explicitly do afterwards.


Do you understand what's going on now?

-- Darren Duncan

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

Re: [sqlite] Autoincrement with rollback

[Koen Van Exem]

I find it a bit confusing because when you create
a PRIMARY KEY AUTOINCREMENT then a
table named sqlite_sequence is created.

According to the SQL (2003) standard multiple sessions are
guaranteed to allocate distinct sequence values.
(even when rollbacks are involved)


2014-11-11 11:14 GMT+01:00 Andy Ling :

> > --- Begin ---
> >
> > sqlite> drop table if exists demo;
> > sqlite> create table demo (id integer primary key autoincrement, value
> > text);
> > sqlite> begin transaction;
> > sqlite> insert into demo (value) VALUES ('value');
> > sqlite> select last_insert_rowid();
> > 1
> > sqlite> delete from demo where id = 1;
> > sqlite> rollback;
> > sqlite> begin transaction;
> > sqlite> insert into demo (value) VALUES ('value');
> > sqlite> select last_insert_rowid();
> > 1
> >
> > --- End ---
> >
> > Using a delete statement before the rollback
> > doesn't help.
> >
> > I really want sqlite to only issue an id once.
> >
>
> Surely the whole point of rollback is that it puts the database back to the
> state it was in before the transaction started. So everything should be
> just as if no inserts (or deletes or anything else)had happened, including
> any changes to autoincrement values.
>
> Regards
>
> Andy Ling
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Koen Van Exem
+32 3301 3301
+32 498 51
k...@allors.com
http://www.allors.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[J Decker]

delete it and commit it...
rollback is 'undo' and if anything was differen't it wouldn't be a very
good undo.
assign the key yourself?  on failure keep incrementing?
if it's supposed to have been inserted and deleted... then rollback is not
the correct solution.

On Tue, Nov 11, 2014 at 2:04 AM, Koen Van Exem  wrote:

> --- Begin ---
>
> sqlite> drop table if exists demo;
> sqlite> create table demo (id integer primary key autoincrement, value
> text);
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
> sqlite> delete from demo where id = 1;
> sqlite> rollback;
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
>
> --- End ---
>
> Using a delete statement before the rollback
> doesn't help.
>
> I really want sqlite to only issue an id once.
>
> Is this possible?
>
> Thanks,
> Koen
>
>
> 2014-11-11 10:35 GMT+01:00 Clemens Ladisch :
>
> > Koen Van Exem wrote:
> > > Is it a bug or feature that the autoincrement
> > > value is being reused when a rollback is issued?
> > >
> > > The documentation on https://www.sqlite.org/autoinc.html is a bit
> > unclear
> > >
> > > ... it says it prevents reuse of ROWIDs from previously deleted rows.
> >
> > Only a DELETE statement results in "deleted rows".  When an INSERT is
> > rolled back, the database is in a state as if the INSERT never happened.
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Koen Van Exem
> +32 3301 3301
> +32 498 51
> k...@allors.com
> http://www.allors.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] Autoincrement with rollback

[Andy Ling]

> --- Begin ---
> 
> sqlite> drop table if exists demo;
> sqlite> create table demo (id integer primary key autoincrement, value
> text);
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
> sqlite> delete from demo where id = 1;
> sqlite> rollback;
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
> 
> --- End ---
> 
> Using a delete statement before the rollback
> doesn't help.
> 
> I really want sqlite to only issue an id once.
> 

Surely the whole point of rollback is that it puts the database back to the
state it was in before the transaction started. So everything should be
just as if no inserts (or deletes or anything else)had happened, including
any changes to autoincrement values.

Regards

Andy Ling

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

Re: [sqlite] Autoincrement with rollback

[Koen Van Exem]

--- Begin ---

sqlite> drop table if exists demo;
sqlite> create table demo (id integer primary key autoincrement, value
text);
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1
sqlite> delete from demo where id = 1;
sqlite> rollback;
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1

--- End ---

Using a delete statement before the rollback
doesn't help.

I really want sqlite to only issue an id once.

Is this possible?

Thanks,
Koen


2014-11-11 10:35 GMT+01:00 Clemens Ladisch :

> Koen Van Exem wrote:
> > Is it a bug or feature that the autoincrement
> > value is being reused when a rollback is issued?
> >
> > The documentation on https://www.sqlite.org/autoinc.html is a bit
> unclear
> >
> > ... it says it prevents reuse of ROWIDs from previously deleted rows.
>
> Only a DELETE statement results in "deleted rows".  When an INSERT is
> rolled back, the database is in a state as if the INSERT never happened.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Koen Van Exem
+32 3301 3301
+32 498 51
k...@allors.com
http://www.allors.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement with rollback

[Clemens Ladisch]

Koen Van Exem wrote:
> Is it a bug or feature that the autoincrement
> value is being reused when a rollback is issued?
>
> The documentation on https://www.sqlite.org/autoinc.html is a bit unclear
>
> ... it says it prevents reuse of ROWIDs from previously deleted rows.

Only a DELETE statement results in "deleted rows".  When an INSERT is
rolled back, the database is in a state as if the INSERT never happened.


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

[sqlite] Autoincrement with rollback

[Koen Van Exem]

Hi,

Is it a bug or feature that the autoincrement
value is being reused when a rollback is issued?


--- Begin ---

sqlite> drop table if exists demo;
sqlite> create table demo (id integer primary key autoincrement, value
text);
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1
sqlite> rollback;
sqlite> begin transaction;
sqlite> insert into demo (value) VALUES ('value');
sqlite> select last_insert_rowid();
1

--- End ---

The documentation on https://www.sqlite.org/autoinc.html is a bit unclear

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 the one more than the
largest ROWID currently in use. This is true regardless of whether or not
the AUTOINCREMENT keyword is used.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that
changes the automatic ROWID assignment algorithm to prevent the reuse of
ROWIDs over the lifetime of the database. In other words, the purpose of
AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted
rows.

In bullet 1 it says that it takes a value higher than the largest ROWID.
In bullet 2 it says it prevents reuse of ROWIDs from previously deleted
rows.

Can someone please clarify?

Thanks,
Koen

-- 
Koen Van Exem
+32 2335 2335
k...@allors.com
http://www.allors.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] AUTOINCREMENT BIGINT

[Michele Pradella]

Ok understand thanks

Il 07/11/2014 14.40, Richard Hipp ha scritto:

On Fri, Nov 7, 2014 at 8:26 AM, Michele Pradella 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] AUTOINCREMENT BIGINT

[Richard Hipp]

On Fri, Nov 7, 2014 at 8:26 AM, Michele Pradella  wrote:

>
> Is there a way to sue AUTOINCREMENT with BIGINT? what's the reason for
> this check?
>

No.  Furthermore, AUTOINCREMENT probably does not do what you think it
does.  Please read the details at https://www.sqlite.org/autoinc.html
-- 
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] AUTOINCREMENT BIGINT

[Clemens Ladisch]

Michele Pradella wrote:
> I have a question about data type BIGINT

BIGINT is not a data type.

> from docs (http://www.sqlite.org/datatype3.html)

This page says the data types are NULL, INTEGER, REAL, TEXT, and BLOB.

> I understand that INTEGER and BIGINT results in the same affinity

Yes.

> so datatypes are same, is it correct?

No.  SQLite uses dynamic typing; the data type of a value is whatever
type was used when inserting this value (and as modified by the
affinity).

> if I create a table with a field "Id BIGINT PRIMARY KEY AUTOINCREMENT"
> I got the error "AUTOINCREMENT is only allowed on an INTEGER PRIMARY
> KEY"

The documentation 
says:
| A PRIMARY KEY column only becomes an integer primary key if the
| declared type name is exactly "INTEGER". Other integer type names like
| "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the
| primary key column to behave as an ordinary table column with integer
| affinity and a unique index, not as an alias for the rowid.

> Is there a way to sue AUTOINCREMENT with BIGINT?

No.  Neither is there a way to use AUTOINCREMENT with BIGINT.

> what's the reason for this check?

Backwards compatibility.


Please note that with an INTEGER PRIMARY KEY column, it is likely that
you do not need AUTOINCREMENT: .


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

[sqlite] AUTOINCREMENT BIGINT

[Michele Pradella]

Hi all, I have a question about data type BIGINT: from docs 
(http://www.sqlite.org/datatype3.html) I understand that INTEGER and 
BIGINT results in the same affinity (INTEGER), so datatypes are same, is 
it correct?
Unfortunately if I create a table with a field "Id BIGINT PRIMARY KEY 
AUTOINCREMENT" I got the error "AUTOINCREMENT is only allowed on an 
INTEGER PRIMARY KEY"

from those lines:
#ifndef SQLITE_OMIT_AUTOINCREMENT
sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
   "INTEGER PRIMARY KEY");
#endif

Is there a way to sue AUTOINCREMENT with BIGINT? what's the reason for 
this check?


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

Re: [sqlite] AUTOINCREMENT

[RSmith]

On 2014/07/27 12:07, michael walsley wrote:

Why doesn't AUTOINCREMENT work like rowid, in that, with rowid if the value
on insert is not null it doesn't get a new rowid, it just accepts the value
as in the insert?

  


Im trying to develop a win phone app in that data downloaded from the server
needs to insert as it comes, but rows added on the phone need to use
AUTOINCREMENT, in which case the id is null and the db has to get the next
rowid?


Not sure what happens in your app, but it is likely the fault of something else... AUTOINCREMENT is merely a suggestion what to do 
with a value should it not be supplied, with rowid it is implicit, but in either case you can supply the value yourself, so long as 
you specify a valid one (meaning no duplicate, etc.).


So if you have this...

CREATE TABLE AutoTest (
 "ID" INTEGER PRIMARY KEY AUTOINCREMENT,
 "Name" TEXT
);

INSERT INTO AutoTest (Name) VALUES
('This will have ID 1'),
('And this must be ID 2');

INSERT INTO AutoTest (ID, Name) VALUES
(4, 'This should be at ID 4'),
(6, 'and this at ID 6');

INSERT INTO AutoTest (ID, Name) VALUES
(3, 'This should now be "inserted" at ID 3'),
(5, 'and this at ID 5');

SELECT * FROM AutoTest;
ID, Name
1, This will have ID 1
2, And this must be ID 2
3, This should now be "inserted" at ID 3
4, This should be at ID 4
5, and this at ID 5
6, and this at ID 6

See? No Problem.

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

Re: [sqlite] AUTOINCREMENT

[Clemens Ladisch]

On 07/27/2014 12:07 PM, michael walsley wrote:
> Why doesn't AUTOINCREMENT work like rowid, in that, with rowid if the value
> on insert is not null it doesn't get a new rowid, it just accepts the value
> as in the insert?

sqlite> create table t(x integer primary key autoincrement);
sqlite> insert into t(x) values(42);
sqlite> select * from t;
42

What is your app actually doing?


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

[sqlite] AUTOINCREMENT

[michael walsley]

Why doesn't AUTOINCREMENT work like rowid, in that, with rowid if the value
on insert is not null it doesn't get a new rowid, it just accepts the value
as in the insert?

 

Im trying to develop a win phone app in that data downloaded from the server
needs to insert as it comes, but rows added on the phone need to use
AUTOINCREMENT, in which case the id is null and the db has to get the next
rowid?

 

One can do something similar on sql server by setting identity_insert on.

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

Re: [sqlite] autoincrement and primary key

[Roman Fleysher]

Thank you, INTEGER -> INT solved the problem. According to manual, this will 
make search slower, but I give data integrity more weight for now.

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [i...@tandetnik.org]
Sent: Monday, May 20, 2013 4:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] autoincrement and primary key

On 5/20/2013 4:17 PM, Roman Fleysher wrote:
> I would like to use INTEGER PRIMARY KEY, but I would like to disable its 
> implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the 
> column, I would like uniqueness to be enforced, but if NULL is supplied, I 
> would like the operation to fail instead of advancing key to a new integer.

Just make the column "INT PRIMARY KEY NOT NULL" (note INT rather than
INTEGER). This way, it is not an alias for ROWID but a column in its own
right, and doesn't get assigned a value automatically.
--
Igor Tandetnik

___
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] autoincrement and primary key

[Igor Tandetnik]

On 5/20/2013 4:17 PM, Roman Fleysher wrote:

I would like to use INTEGER PRIMARY KEY, but I would like to disable its 
implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the 
column, I would like uniqueness to be enforced, but if NULL is supplied, I 
would like the operation to fail instead of advancing key to a new integer.


Just make the column "INT PRIMARY KEY NOT NULL" (note INT rather than 
INTEGER). This way, it is not an alias for ROWID but a column in its own 
right, and doesn't get assigned a value automatically.

--
Igor Tandetnik

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

Re: [sqlite] autoincrement and primary key

[Jean-Christophe Deschamps]

I would like to use INTEGER PRIMARY KEY, but I would like to disable 
its implicit AUTOINCREMENT feature. Namely, if INSERT specifies value 
of the column, I would like uniqueness to be enforced, but if NULL is 
supplied, I would like the operation to fail instead of advancing key 
to a new integer.


Switching declared type from INTEGER to INT should do what you want:

CREATE TABLE qqq (
  id INT PRIMARY KEY
); 


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

[sqlite] autoincrement and primary key

[Roman Fleysher]

Dear SQLiters,

I would like to use INTEGER PRIMARY KEY, but I would like to disable its 
implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the 
column, I would like uniqueness to be enforced, but if NULL is supplied, I 
would like the operation to fail instead of advancing key to a new integer. 
Here is my failed attempt:

CREATE TABLE qqq (
  id INTEGER PRIMARY KEY   
);

CREATE TRIGGER qqqIdTrigger BEFORE INSERT ON qqq WHEN new.id IS NULL
  BEGIN 
select RAISE (FAIL, roma);
  END;

INSERT INTO qqq (id) VALUES (20);
INSERT INTO qqq (id) VALUES (NULL);

I tried typeof(new.id)='null' in the WHEN clause, both triggers register, but 
do not raise fail. Triggering when id is too high (WHEN new.id >20) works. I 
thought that when id being inserted is NULL it is autoincremented before 
trigger is called. But then, it must trigger the too-high version when passing 
threshold --- it did not.

I could replace INTEGER primary key by TEXT primary key and auto increment will 
go away. How can I keep integer?

Thank you,

Roman


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

Re: [sqlite] Autoincrement failure

[Black, Michael (IS)]

What happens if you make it an in-memory database?  If that works that will 
help narrow the problem to the MMC.



And can you run it on your development machine too and see what happens?



And I think we asked before but can you make a small sample program that causes 
this for you?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Monday, August 22, 2011 10:17 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

SELECT rowid FROM (mytable) WHERE (mystuff)

returns

37 identical rows(!)  where Rowid = 1



Alessandro




From:
a.azzol...@custom.it
To:
<sqlite-users@sqlite.org>
Date:
22/08/2011 16.26
Subject:
Re: [sqlite] Autoincrement failure



sqlite> .dump sqlite_sequence
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite>


Any idea?

Thanks
Alessandro



From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 16.11
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:

> Hallo,
>
> Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> Every new record seems to be added with rowid=1 overwriting existing
> info...
>
> Any idea about the causes of this issue
> and about extracting lost data (if present)?
>

Please run from the sqlite3.exe shell:

 .dump sqlite_sequence

Tell us what you see.


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



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


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


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

Re: [sqlite] Autoincrement failure

[Simon Slavin]

On 24 Aug 2011, at 1:13pm, a.azzol...@custom.it wrote:

> On my laptop integrity_check fail
> 
>> PRAGMA integrity_check
>> returns
>> 
>> *** in database main ***
>> rowid 0 missing from index JournalDateIndex
>> rowid 0 missing from index sqlite_autoindex_Journal_1
>> wrong # of entries in index JournalDateIndex
>> wrong # of entries in index sqlite_autoindex_Journal_1
> 
> but I can continue to Insert new rows without any error.

It does not matter.  Once integrity_check fails, anything can go wrong: records 
can be lost, new data can magically appear.  What you need to do is find out 
which operation is causing your integrity_check to fail.

Start with a new database.  Create your table and indexes.  Put some data in 
it.  After each command run integrity_check.  Find out which command is the 
first one that makes integrity_check fail.

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

Re: [sqlite] Autoincrement failure

[A . Azzolini]

On my laptop integrity_check fail

> PRAGMA integrity_check
> returns
>
> *** in database main ***
> rowid 0 missing from index JournalDateIndex
> rowid 0 missing from index sqlite_autoindex_Journal_1
> wrong # of entries in index JournalDateIndex
> wrong # of entries in index sqlite_autoindex_Journal_1

but I can continue to Insert new rows without any error.
Any new row has rowid = 1. Autoincrement algorithm does not work.

I'm looking for make my DB file more 'strong' and safe against this issue

Alessandro



From:
Simon Slavin <slav...@bigfraud.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 18.44
Subject:
Re: [sqlite] Autoincrement failure




On 23 Aug 2011, at 4:28pm, a.azzol...@custom.it wrote:

> I cannot run a shell sqlite3 on my embedded system. I will explain it 
> using your example.

At some point your database file is becoming corrupt.

> It happen that sometime autoincrement begin to fail
> and new records overwrite the one at rowid =1
> 
> 1|1|2|3|one|two|three  replaced by 1|4|2|3|one|two|three
> 2|2|2|3|one|two|three
> 3|3|2|3|one|two|three

Can you stop at this point, copy the resulting database file back onto 
your desktop computer, and run

PRAGMA integrity_check()

on it using the shell tool ?

If it passes the integrity_check there, can you then execute the next 
INSERT on it, then do the same test ?

Simon.
___
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] Autoincrement failure

[Simon Slavin]

On 23 Aug 2011, at 4:28pm, a.azzol...@custom.it wrote:

> I cannot run a shell sqlite3 on my embedded system. I will explain it 
> using your example.

At some point your database file is becoming corrupt.

> It happen that sometime autoincrement begin to fail
> and new records overwrite the one at rowid =1
> 
> 1|1|2|3|one|two|three  replaced by 1|4|2|3|one|two|three
> 2|2|2|3|one|two|three
> 3|3|2|3|one|two|three

Can you stop at this point, copy the resulting database file back onto your 
desktop computer, and run

PRAGMA integrity_check()

on it using the shell tool ?

If it passes the integrity_check there, can you then execute the next INSERT on 
it, then do the same test ?

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

Re: [sqlite] Autoincrement failure

[Black, Michael (IS)]

Dont' know if this is related but are you keeping the database open all the 
time or closing and reopening?



If reopening does this only occur after opening again?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 10:28 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

Sorry,
I cannot run a shell sqlite3 on my embedded system. I will explain it
using your example.

It happen that sometime autoincrement begin to fail
and new records overwrite the one at rowid =1

1|1|2|3|one|two|three  replaced by 1|4|2|3|one|two|three
2|2|2|3|one|two|three
3|3|2|3|one|two|three

if I add another record >insert into journal
values(5,2,3,'one','two','three');
it overwrite last one as follow

1|4|2|3|one|two|three  replaced by 1|5|2|3|one|two|three
2|2|2|3|one|two|three
3|3|2|3|one|two|three

and by  "select rowid, * from journal where rowid = 1"
returns three times the same values with rowid = 1

1|5|2|3|one|two|three
1|5|2|3|one|two|three
1|5|2|3|one|two|three

It appear that SQLite "keep in mind" the number of operations but the
index for extracting info does not work fine.

Thanks for any comment

Alessandro


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

Re: [sqlite] Autoincrement failure

[A . Azzolini]

Sorry,
I cannot run a shell sqlite3 on my embedded system. I will explain it 
using your example.

It happen that sometime autoincrement begin to fail
and new records overwrite the one at rowid =1

1|1|2|3|one|two|three  replaced by 1|4|2|3|one|two|three
2|2|2|3|one|two|three
3|3|2|3|one|two|three

if I add another record >insert into journal 
values(5,2,3,'one','two','three');
it overwrite last one as follow

1|4|2|3|one|two|three  replaced by 1|5|2|3|one|two|three
2|2|2|3|one|two|three
3|3|2|3|one|two|three

and by  "select rowid, * from journal where rowid = 1" 
returns three times the same values with rowid = 1

1|5|2|3|one|two|three
1|5|2|3|one|two|three
1|5|2|3|one|two|three

It appear that SQLite "keep in mind" the number of operations but the 
index for extracting info does not work fine.

Thanks for any comment

Alessandro



From:
"Black, Michael (IS)" <michael.bla...@ngc.com>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 17.07
Subject:
Re: [sqlite] Autoincrement failure



Your insert looks OK...can you run sqlite3 on your embedded system?



Try this and see what you get...I ran this on an MMC I had and it worked 
OK.



SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE Journal (ClosureNum INTEGER, TicketNum INTEGER, 
ItemNum
   ...> INTEGER, Date DATE, Time TIME, Item BLOB, PRIMARY KEY(ClosureNum,
   ...> TicketNum, ItemNum));
sqlite> insert into journal values(1,2,3,'one','two','three');
sqlite> select * from journal;
1|2|3|one|two|three
sqlite> insert into journal values(2,2,3,'one','two','three');
sqlite> select rowid,* from journal;
1|1|2|3|one|two|three
2|2|2|3|one|two|three
sqlite> insert into journal values(3,2,3,'one','two','three');
sqlite> select rowid,* from journal;
1|1|2|3|one|two|three
2|2|2|3|one|two|three
3|3|2|3|one|two|three



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 8:55 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

table definition:

"CREATE TABLE Journal (ClosureNum INTEGER, TicketNum INTEGER, ItemNum
INTEGER, Date DATE, Time TIME, Item BLOB, PRIMARY KEY(ClosureNum,
TicketNum, ItemNum))"

example insert (sorry for cut from my code...):

sprintf(zSql, "INSERT INTO Journal VALUES(%i, %i, %i, '%s', '%s', '%s')",
nv.dgfe_db_pntr->triad.closure_num+1, nv.dgfe_db_pntr->triad.ticket_num,
nv.dgfe_db_pntr->triad.item_num, nv.dgfe_db_pntr->date,
nv.dgfe_db_pntr->time, description);

Rgds,
Alessandro



From:
"Black, Michael (IS)" <michael.bla...@ngc.com>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 15.50
Subject:
Re: [sqlite] Autoincrement failure



Could you please show us your table definition and an example insert
statement that your generating?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 8:35 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

I'm using SQLite on embedded system (ARM processor)

and DB file is on MMC memory.

Normally INSERT operations of new records works fine. Perhaps,
autoincrement failure is due to electrical pbm and is present only on few
devices
but I'm looking for a way to protect DB file against this pbm.

This failure is a DB corruption invisible after simple INSERT (it returns
'no_error') and it's very dangerous (data seems correctly saved)

Compiler is "armcc".

My table has three primary key, autoincrement active and other description
column like

(rowid = 1) 1 1 1  mydescr1 mydescr2 mydescr3
(rowid = 2) 1 1 2  mydescr1 mydescr2 mydescr3
(rowid = 3) 1 1 3  mydescr1 mydescr2 mydescr3
(rowid = 4) 1 1 4  mydescr1 mydescr2 mydescr3
(rowid = 5) 1 2 1  mydescr1 mydescr2 mydescr3
(rowid = 6) 1 2 2  mydescr1 mydescr2 mydescr3
(rowid = 7) 1 2 3  mydescr1 mydescr2 mydescr3
(rowid = 8) 1 2 4  mydescr1 mydescr2 mydescr3
(rowid = 9) 1 3 1  mydescr1 mydescr2 mydescr3
(rowid =10) 1 3 2  mydescr1 mydescr2 mydescr3
(rowid =11) 1 3 3  mydescr1 mydescr2 mydescr3
(rowid =12) 2 1 1  mydescr1 mydescr2 mydescr3
...
etc

Corruption is visible when first row is overwrited with three index
different from 1,1,1
After that, any new record will be inserted automatically at rowid=1

How can I avoid this corruption? Or How can protect my DB against this
kind of corruption?

Thanks for any help
Alessandro

Re: [sqlite] Autoincrement failure

[Black, Michael (IS)]

Your insert looks OK...can you run sqlite3 on your embedded system?



Try this and see what you get...I ran this on an MMC I had and it worked OK.



SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE Journal (ClosureNum INTEGER, TicketNum INTEGER, ItemNum
   ...> INTEGER, Date DATE, Time TIME, Item BLOB, PRIMARY KEY(ClosureNum,
   ...> TicketNum, ItemNum));
sqlite> insert into journal values(1,2,3,'one','two','three');
sqlite> select * from journal;
1|2|3|one|two|three
sqlite> insert into journal values(2,2,3,'one','two','three');
sqlite> select rowid,* from journal;
1|1|2|3|one|two|three
2|2|2|3|one|two|three
sqlite> insert into journal values(3,2,3,'one','two','three');
sqlite> select rowid,* from journal;
1|1|2|3|one|two|three
2|2|2|3|one|two|three
3|3|2|3|one|two|three



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 8:55 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

table definition:

"CREATE TABLE Journal (ClosureNum INTEGER, TicketNum INTEGER, ItemNum
INTEGER, Date DATE, Time TIME, Item BLOB, PRIMARY KEY(ClosureNum,
TicketNum, ItemNum))"

example insert (sorry for cut from my code...):

sprintf(zSql, "INSERT INTO Journal VALUES(%i, %i, %i, '%s', '%s', '%s')",
nv.dgfe_db_pntr->triad.closure_num+1, nv.dgfe_db_pntr->triad.ticket_num,
nv.dgfe_db_pntr->triad.item_num, nv.dgfe_db_pntr->date,
nv.dgfe_db_pntr->time, description);

Rgds,
Alessandro



From:
"Black, Michael (IS)" <michael.bla...@ngc.com>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 15.50
Subject:
Re: [sqlite] Autoincrement failure



Could you please show us your table definition and an example insert
statement that your generating?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 8:35 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

I'm using SQLite on embedded system (ARM processor)

and DB file is on MMC memory.

Normally INSERT operations of new records works fine. Perhaps,
autoincrement failure is due to electrical pbm and is present only on few
devices
but I'm looking for a way to protect DB file against this pbm.

This failure is a DB corruption invisible after simple INSERT (it returns
'no_error') and it's very dangerous (data seems correctly saved)

Compiler is "armcc".

My table has three primary key, autoincrement active and other description
column like

(rowid = 1) 1 1 1  mydescr1 mydescr2 mydescr3
(rowid = 2) 1 1 2  mydescr1 mydescr2 mydescr3
(rowid = 3) 1 1 3  mydescr1 mydescr2 mydescr3
(rowid = 4) 1 1 4  mydescr1 mydescr2 mydescr3
(rowid = 5) 1 2 1  mydescr1 mydescr2 mydescr3
(rowid = 6) 1 2 2  mydescr1 mydescr2 mydescr3
(rowid = 7) 1 2 3  mydescr1 mydescr2 mydescr3
(rowid = 8) 1 2 4  mydescr1 mydescr2 mydescr3
(rowid = 9) 1 3 1  mydescr1 mydescr2 mydescr3
(rowid =10) 1 3 2  mydescr1 mydescr2 mydescr3
(rowid =11) 1 3 3  mydescr1 mydescr2 mydescr3
(rowid =12) 2 1 1  mydescr1 mydescr2 mydescr3
...
etc

Corruption is visible when first row is overwrited with three index
different from 1,1,1
After that, any new record will be inserted automatically at rowid=1

How can I avoid this corruption? Or How can protect my DB against this
kind of corruption?

Thanks for any help
Alessandro




From:
"Black, Michael (IS)" <michael.bla...@ngc.com>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 14.10
Subject:
Re: [sqlite] Autoincrement failure



Give the recent notice of a bug in gcc-4.1 what compiler are you using and
how are you compiling?



And can you reproduce this with a small example table?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 7:01 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

Reindex procedure returns 'no_error'

but rowid=1 is still present 37 times with same values


How can I avoid to fall in this bug?
It's very dangerous because any new INSERT operation terminate
successfully
but previous data has beeen overwrited with new one (on rowid =1)

Many thanks,
Alessandro





From:
Richar

Re: [sqlite] Autoincrement failure

[A . Azzolini]

table definition:

"CREATE TABLE Journal (ClosureNum INTEGER, TicketNum INTEGER, ItemNum 
INTEGER, Date DATE, Time TIME, Item BLOB, PRIMARY KEY(ClosureNum, 
TicketNum, ItemNum))"

example insert (sorry for cut from my code...):

sprintf(zSql, "INSERT INTO Journal VALUES(%i, %i, %i, '%s', '%s', '%s')", 
nv.dgfe_db_pntr->triad.closure_num+1, nv.dgfe_db_pntr->triad.ticket_num, 
nv.dgfe_db_pntr->triad.item_num, nv.dgfe_db_pntr->date, 
nv.dgfe_db_pntr->time, description);

Rgds,
Alessandro



From:
"Black, Michael (IS)" <michael.bla...@ngc.com>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 15.50
Subject:
Re: [sqlite] Autoincrement failure



Could you please show us your table definition and an example insert 
statement that your generating?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 8:35 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

I'm using SQLite on embedded system (ARM processor)

and DB file is on MMC memory.

Normally INSERT operations of new records works fine. Perhaps,
autoincrement failure is due to electrical pbm and is present only on few
devices
but I'm looking for a way to protect DB file against this pbm.

This failure is a DB corruption invisible after simple INSERT (it returns
'no_error') and it's very dangerous (data seems correctly saved)

Compiler is "armcc".

My table has three primary key, autoincrement active and other description
column like

(rowid = 1) 1 1 1  mydescr1 mydescr2 mydescr3
(rowid = 2) 1 1 2  mydescr1 mydescr2 mydescr3
(rowid = 3) 1 1 3  mydescr1 mydescr2 mydescr3
(rowid = 4) 1 1 4  mydescr1 mydescr2 mydescr3
(rowid = 5) 1 2 1  mydescr1 mydescr2 mydescr3
(rowid = 6) 1 2 2  mydescr1 mydescr2 mydescr3
(rowid = 7) 1 2 3  mydescr1 mydescr2 mydescr3
(rowid = 8) 1 2 4  mydescr1 mydescr2 mydescr3
(rowid = 9) 1 3 1  mydescr1 mydescr2 mydescr3
(rowid =10) 1 3 2  mydescr1 mydescr2 mydescr3
(rowid =11) 1 3 3  mydescr1 mydescr2 mydescr3
(rowid =12) 2 1 1  mydescr1 mydescr2 mydescr3
...
etc

Corruption is visible when first row is overwrited with three index
different from 1,1,1
After that, any new record will be inserted automatically at rowid=1

How can I avoid this corruption? Or How can protect my DB against this
kind of corruption?

Thanks for any help
Alessandro




From:
"Black, Michael (IS)" <michael.bla...@ngc.com>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 14.10
Subject:
Re: [sqlite] Autoincrement failure



Give the recent notice of a bug in gcc-4.1 what compiler are you using and
how are you compiling?



And can you reproduce this with a small example table?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 7:01 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

Reindex procedure returns 'no_error'

but rowid=1 is still present 37 times with same values


How can I avoid to fall in this bug?
It's very dangerous because any new INSERT operation terminate
successfully
but previous data has beeen overwrited with new one (on rowid =1)

Many thanks,
Alessandro





From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 17.40
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 11:25 AM, <a.azzol...@custom.it> wrote:

> schema 3
>
> PRAGMA integrity_check
> returns
>
> *** in database main ***
> rowid 0 missing from index JournalDateIndex
> rowid 0 missing from index sqlite_autoindex_Journal_1
> wrong # of entries in index JournalDateIndex
> wrong # of entries in index sqlite_autoindex_Journal_1
>

Try running "REINDEX" and see if that clears up the problem.


>
>
>
>
>
> From:
> Richard Hipp <d...@sqlite.org>
> To:
> General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 17.21
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote:
>
> >  SELECT rowid FROM (mytable) WHERE (mystuff)
> >
> > returns
> >
> > 37 identical rows(!)  where Rowid = 1
> >
>
> What is your schema?
>
> If you run "PRAGMA integrity_check"?
>
>
> >
> >
> >
> > Alessandro
>

Re: [sqlite] Autoincrement failure

[Simon Slavin]

On 23 Aug 2011, at 2:35pm, a.azzol...@custom.it wrote:

> Normally INSERT operations of new records works fine. Perhaps, 
> autoincrement failure is due to electrical pbm and is present only on few 
> devices
> but I'm looking for a way to protect DB file against this pbm.
> 
> This failure is a DB corruption invisible after simple INSERT (it returns 
> 'no_error') and it's very dangerous (data seems correctly saved)

Although the INSERT is returning 'no_error' could you please run

PRAGMA integrity_check()

after each operation and see when it starts reporting problems ?

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

Re: [sqlite] Autoincrement failure

[Black, Michael (IS)]

Could you please show us your table definition and an example insert statement 
that your generating?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 8:35 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

I'm using SQLite on embedded system (ARM processor)

and DB file is on MMC memory.

Normally INSERT operations of new records works fine. Perhaps,
autoincrement failure is due to electrical pbm and is present only on few
devices
but I'm looking for a way to protect DB file against this pbm.

This failure is a DB corruption invisible after simple INSERT (it returns
'no_error') and it's very dangerous (data seems correctly saved)

Compiler is "armcc".

My table has three primary key, autoincrement active and other description
column like

(rowid = 1) 1 1 1  mydescr1 mydescr2 mydescr3
(rowid = 2) 1 1 2  mydescr1 mydescr2 mydescr3
(rowid = 3) 1 1 3  mydescr1 mydescr2 mydescr3
(rowid = 4) 1 1 4  mydescr1 mydescr2 mydescr3
(rowid = 5) 1 2 1  mydescr1 mydescr2 mydescr3
(rowid = 6) 1 2 2  mydescr1 mydescr2 mydescr3
(rowid = 7) 1 2 3  mydescr1 mydescr2 mydescr3
(rowid = 8) 1 2 4  mydescr1 mydescr2 mydescr3
(rowid = 9) 1 3 1  mydescr1 mydescr2 mydescr3
(rowid =10) 1 3 2  mydescr1 mydescr2 mydescr3
(rowid =11) 1 3 3  mydescr1 mydescr2 mydescr3
(rowid =12) 2 1 1  mydescr1 mydescr2 mydescr3
...
etc

Corruption is visible when first row is overwrited with three index
different from 1,1,1
After that, any new record will be inserted automatically at rowid=1

How can I avoid this corruption? Or How can protect my DB against this
kind of corruption?

Thanks for any help
Alessandro




From:
"Black, Michael (IS)" <michael.bla...@ngc.com>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 14.10
Subject:
Re: [sqlite] Autoincrement failure



Give the recent notice of a bug in gcc-4.1 what compiler are you using and
how are you compiling?



And can you reproduce this with a small example table?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 7:01 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

Reindex procedure returns 'no_error'

but rowid=1 is still present 37 times with same values


How can I avoid to fall in this bug?
It's very dangerous because any new INSERT operation terminate
successfully
but previous data has beeen overwrited with new one (on rowid =1)

Many thanks,
Alessandro





From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 17.40
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 11:25 AM, <a.azzol...@custom.it> wrote:

> schema 3
>
> PRAGMA integrity_check
> returns
>
> *** in database main ***
> rowid 0 missing from index JournalDateIndex
> rowid 0 missing from index sqlite_autoindex_Journal_1
> wrong # of entries in index JournalDateIndex
> wrong # of entries in index sqlite_autoindex_Journal_1
>

Try running "REINDEX" and see if that clears up the problem.


>
>
>
>
>
> From:
> Richard Hipp <d...@sqlite.org>
> To:
> General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 17.21
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote:
>
> >  SELECT rowid FROM (mytable) WHERE (mystuff)
> >
> > returns
> >
> > 37 identical rows(!)  where Rowid = 1
> >
>
> What is your schema?
>
> If you run "PRAGMA integrity_check"?
>
>
> >
> >
> >
> > Alessandro
> >
> >
> >
> >
> > From:
> > a.azzol...@custom.it
> > To:
> > <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.26
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > sqlite> .dump sqlite_sequence
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > COMMIT;
> > sqlite>
> >
> >
> > Any idea?
> >
> > Thanks
> > Alessandro
> >
> >
> >
> > From:
> > Richard Hipp <d...@sqlite.org>
> > To:
> > General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > Date:
> > 22/08/20

Re: [sqlite] Autoincrement failure

[A . Azzolini]

creation statement is

"CREATE TABLE Journal (ClosureNum INTEGER, TicketNum INTEGER, ItemNum 
INTEGER, Date DATE, Time TIME, Item BLOB, PRIMARY KEY(ClosureNum, 
TicketNum, ItemNum))"

Autoincrement seems to work fine

Thanks for your support

Alessandro




From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 15.42
Subject:
Re: [sqlite] Autoincrement failure



On Tue, Aug 23, 2011 at 9:35 AM, <a.azzol...@custom.it> wrote:

> IMy table has three primary key, autoincrement active and other 
description
> column like
>


The AUTOINCREMENT feature of SQLite only works if there is a single 
INTEGER
PRIMARY KEY.

Please send us the text of the CREATE TABLE statement for your table so 
that
we can better advise you.


-- 
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] Autoincrement failure

[Richard Hipp]

On Tue, Aug 23, 2011 at 9:35 AM,  wrote:

> IMy table has three primary key, autoincrement active and other description
> column like
>


The AUTOINCREMENT feature of SQLite only works if there is a single INTEGER
PRIMARY KEY.

Please send us the text of the CREATE TABLE statement for your table so that
we can better advise you.


-- 
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] Autoincrement failure

[A . Azzolini]

I'm using SQLite on embedded system (ARM processor)

and DB file is on MMC memory.

Normally INSERT operations of new records works fine. Perhaps, 
autoincrement failure is due to electrical pbm and is present only on few 
devices
but I'm looking for a way to protect DB file against this pbm.

This failure is a DB corruption invisible after simple INSERT (it returns 
'no_error') and it's very dangerous (data seems correctly saved)

Compiler is "armcc".

My table has three primary key, autoincrement active and other description 
column like

(rowid = 1) 1 1 1  mydescr1 mydescr2 mydescr3
(rowid = 2) 1 1 2  mydescr1 mydescr2 mydescr3
(rowid = 3) 1 1 3  mydescr1 mydescr2 mydescr3
(rowid = 4) 1 1 4  mydescr1 mydescr2 mydescr3
(rowid = 5) 1 2 1  mydescr1 mydescr2 mydescr3
(rowid = 6) 1 2 2  mydescr1 mydescr2 mydescr3
(rowid = 7) 1 2 3  mydescr1 mydescr2 mydescr3
(rowid = 8) 1 2 4  mydescr1 mydescr2 mydescr3
(rowid = 9) 1 3 1  mydescr1 mydescr2 mydescr3
(rowid =10) 1 3 2  mydescr1 mydescr2 mydescr3
(rowid =11) 1 3 3  mydescr1 mydescr2 mydescr3
(rowid =12) 2 1 1  mydescr1 mydescr2 mydescr3
...
etc

Corruption is visible when first row is overwrited with three index 
different from 1,1,1
After that, any new record will be inserted automatically at rowid=1

How can I avoid this corruption? Or How can protect my DB against this 
kind of corruption?

Thanks for any help
Alessandro 




From:
"Black, Michael (IS)" <michael.bla...@ngc.com>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
23/08/2011 14.10
Subject:
Re: [sqlite] Autoincrement failure



Give the recent notice of a bug in gcc-4.1 what compiler are you using and 
how are you compiling?



And can you reproduce this with a small example table?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 7:01 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

Reindex procedure returns 'no_error'

but rowid=1 is still present 37 times with same values


How can I avoid to fall in this bug?
It's very dangerous because any new INSERT operation terminate
successfully
but previous data has beeen overwrited with new one (on rowid =1)

Many thanks,
Alessandro





From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 17.40
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 11:25 AM, <a.azzol...@custom.it> wrote:

> schema 3
>
> PRAGMA integrity_check
> returns
>
> *** in database main ***
> rowid 0 missing from index JournalDateIndex
> rowid 0 missing from index sqlite_autoindex_Journal_1
> wrong # of entries in index JournalDateIndex
> wrong # of entries in index sqlite_autoindex_Journal_1
>

Try running "REINDEX" and see if that clears up the problem.


>
>
>
>
>
> From:
> Richard Hipp <d...@sqlite.org>
> To:
> General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 17.21
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote:
>
> >  SELECT rowid FROM (mytable) WHERE (mystuff)
> >
> > returns
> >
> > 37 identical rows(!)  where Rowid = 1
> >
>
> What is your schema?
>
> If you run "PRAGMA integrity_check"?
>
>
> >
> >
> >
> > Alessandro
> >
> >
> >
> >
> > From:
> > a.azzol...@custom.it
> > To:
> > <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.26
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > sqlite> .dump sqlite_sequence
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > COMMIT;
> > sqlite>
> >
> >
> > Any idea?
> >
> > Thanks
> > Alessandro
> >
> >
> >
> > From:
> > Richard Hipp <d...@sqlite.org>
> > To:
> > General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.11
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:
> >
> > > Hallo,
> > >
> > > Have you ever seen a SQLite3 DB file with autoincrement algoritm
> broken?
> > > Every new record seems to be added with rowid=1 overwriting existing
> > > info...
> > >
> > > Any idea about the causes of this i

Re: [sqlite] Autoincrement failure

[Black, Michael (IS)]

Give the recent notice of a bug in gcc-4.1 what compiler are you using and how 
are you compiling?



And can you reproduce this with a small example table?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of a.azzol...@custom.it [a.azzol...@custom.it]
Sent: Tuesday, August 23, 2011 7:01 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Autoincrement failure

Reindex procedure returns 'no_error'

but rowid=1 is still present 37 times with same values


How can I avoid to fall in this bug?
It's very dangerous because any new INSERT operation terminate
successfully
but previous data has beeen overwrited with new one (on rowid =1)

Many thanks,
Alessandro





From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 17.40
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 11:25 AM, <a.azzol...@custom.it> wrote:

> schema 3
>
> PRAGMA integrity_check
> returns
>
> *** in database main ***
> rowid 0 missing from index JournalDateIndex
> rowid 0 missing from index sqlite_autoindex_Journal_1
> wrong # of entries in index JournalDateIndex
> wrong # of entries in index sqlite_autoindex_Journal_1
>

Try running "REINDEX" and see if that clears up the problem.


>
>
>
>
>
> From:
> Richard Hipp <d...@sqlite.org>
> To:
> General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 17.21
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote:
>
> >  SELECT rowid FROM (mytable) WHERE (mystuff)
> >
> > returns
> >
> > 37 identical rows(!)  where Rowid = 1
> >
>
> What is your schema?
>
> If you run "PRAGMA integrity_check"?
>
>
> >
> >
> >
> > Alessandro
> >
> >
> >
> >
> > From:
> > a.azzol...@custom.it
> > To:
> > <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.26
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > sqlite> .dump sqlite_sequence
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > COMMIT;
> > sqlite>
> >
> >
> > Any idea?
> >
> > Thanks
> > Alessandro
> >
> >
> >
> > From:
> > Richard Hipp <d...@sqlite.org>
> > To:
> > General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.11
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:
> >
> > > Hallo,
> > >
> > > Have you ever seen a SQLite3 DB file with autoincrement algoritm
> broken?
> > > Every new record seems to be added with rowid=1 overwriting existing
> > > info...
> > >
> > > Any idea about the causes of this issue
> > > and about extracting lost data (if present)?
> > >
> >
> > Please run from the sqlite3.exe shell:
> >
> > .dump sqlite_sequence
> >
> > Tell us what you see.
> >
> >
> > >
> > > Many thanks
> > > Alessandro
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> 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
>



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


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

Re: [sqlite] Autoincrement failure

[A . Azzolini]

Reindex procedure returns 'no_error'

but rowid=1 is still present 37 times with same values


How can I avoid to fall in this bug?
It's very dangerous because any new INSERT operation terminate 
successfully
but previous data has beeen overwrited with new one (on rowid =1)

Many thanks,
Alessandro
 




From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 17.40
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 11:25 AM, <a.azzol...@custom.it> wrote:

> schema 3
>
> PRAGMA integrity_check
> returns
>
> *** in database main ***
> rowid 0 missing from index JournalDateIndex
> rowid 0 missing from index sqlite_autoindex_Journal_1
> wrong # of entries in index JournalDateIndex
> wrong # of entries in index sqlite_autoindex_Journal_1
>

Try running "REINDEX" and see if that clears up the problem.


>
>
>
>
>
> From:
> Richard Hipp <d...@sqlite.org>
> To:
> General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 17.21
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote:
>
> >  SELECT rowid FROM (mytable) WHERE (mystuff)
> >
> > returns
> >
> > 37 identical rows(!)  where Rowid = 1
> >
>
> What is your schema?
>
> If you run "PRAGMA integrity_check"?
>
>
> >
> >
> >
> > Alessandro
> >
> >
> >
> >
> > From:
> > a.azzol...@custom.it
> > To:
> > <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.26
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > sqlite> .dump sqlite_sequence
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > COMMIT;
> > sqlite>
> >
> >
> > Any idea?
> >
> > Thanks
> > Alessandro
> >
> >
> >
> > From:
> > Richard Hipp <d...@sqlite.org>
> > To:
> > General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.11
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:
> >
> > > Hallo,
> > >
> > > Have you ever seen a SQLite3 DB file with autoincrement algoritm
> broken?
> > > Every new record seems to be added with rowid=1 overwriting existing
> > > info...
> > >
> > > Any idea about the causes of this issue
> > > and about extracting lost data (if present)?
> > >
> >
> > Please run from the sqlite3.exe shell:
> >
> > .dump sqlite_sequence
> >
> > Tell us what you see.
> >
> >
> > >
> > > Many thanks
> > > Alessandro
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> 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
>



-- 
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] Autoincrement failure

[Richard Hipp]

On Mon, Aug 22, 2011 at 11:25 AM, <a.azzol...@custom.it> wrote:

> schema 3
>
> PRAGMA integrity_check
> returns
>
> *** in database main ***
> rowid 0 missing from index JournalDateIndex
> rowid 0 missing from index sqlite_autoindex_Journal_1
> wrong # of entries in index JournalDateIndex
> wrong # of entries in index sqlite_autoindex_Journal_1
>

Try running "REINDEX" and see if that clears up the problem.


>
>
>
>
>
> From:
> Richard Hipp <d...@sqlite.org>
> To:
> General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 17.21
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote:
>
> >  SELECT rowid FROM (mytable) WHERE (mystuff)
> >
> > returns
> >
> > 37 identical rows(!)  where Rowid = 1
> >
>
> What is your schema?
>
> If you run "PRAGMA integrity_check"?
>
>
> >
> >
> >
> > Alessandro
> >
> >
> >
> >
> > From:
> > a.azzol...@custom.it
> > To:
> > <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.26
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > sqlite> .dump sqlite_sequence
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > COMMIT;
> > sqlite>
> >
> >
> > Any idea?
> >
> > Thanks
> > Alessandro
> >
> >
> >
> > From:
> > Richard Hipp <d...@sqlite.org>
> > To:
> > General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > Date:
> > 22/08/2011 16.11
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:
> >
> > > Hallo,
> > >
> > > Have you ever seen a SQLite3 DB file with autoincrement algoritm
> broken?
> > > Every new record seems to be added with rowid=1 overwriting existing
> > > info...
> > >
> > > Any idea about the causes of this issue
> > > and about extracting lost data (if present)?
> > >
> >
> > Please run from the sqlite3.exe shell:
> >
> > .dump sqlite_sequence
> >
> > Tell us what you see.
> >
> >
> > >
> > > Many thanks
> > > Alessandro
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> 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
>



-- 
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] Autoincrement failure

[A . Azzolini]

schema 3

PRAGMA integrity_check
returns

*** in database main ***
rowid 0 missing from index JournalDateIndex
rowid 0 missing from index sqlite_autoindex_Journal_1
wrong # of entries in index JournalDateIndex
wrong # of entries in index sqlite_autoindex_Journal_1





From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 17.21
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote:

>  SELECT rowid FROM (mytable) WHERE (mystuff)
>
> returns
>
> 37 identical rows(!)  where Rowid = 1
>

What is your schema?

If you run "PRAGMA integrity_check"?


>
>
>
> Alessandro
>
>
>
>
> From:
> a.azzol...@custom.it
> To:
> <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 16.26
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> sqlite> .dump sqlite_sequence
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
> sqlite>
>
>
> Any idea?
>
> Thanks
> Alessandro
>
>
>
> From:
> Richard Hipp <d...@sqlite.org>
> To:
> General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 16.11
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:
>
> > Hallo,
> >
> > Have you ever seen a SQLite3 DB file with autoincrement algoritm 
broken?
> > Every new record seems to be added with rowid=1 overwriting existing
> > info...
> >
> > Any idea about the causes of this issue
> > and about extracting lost data (if present)?
> >
>
> Please run from the sqlite3.exe shell:
>
> .dump sqlite_sequence
>
> Tell us what you see.
>
>
> >
> > Many thanks
> > Alessandro
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Autoincrement failure

[Richard Hipp]

On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote:

>  SELECT rowid FROM (mytable) WHERE (mystuff)
>
> returns
>
> 37 identical rows(!)  where Rowid = 1
>

What is your schema?

If you run "PRAGMA integrity_check"?


>
>
>
> Alessandro
>
>
>
>
> From:
> a.azzol...@custom.it
> To:
> <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 16.26
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> sqlite> .dump sqlite_sequence
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
> sqlite>
>
>
> Any idea?
>
> Thanks
> Alessandro
>
>
>
> From:
> Richard Hipp <d...@sqlite.org>
> To:
> General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Date:
> 22/08/2011 16.11
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:
>
> > Hallo,
> >
> > Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> > Every new record seems to be added with rowid=1 overwriting existing
> > info...
> >
> > Any idea about the causes of this issue
> > and about extracting lost data (if present)?
> >
>
> Please run from the sqlite3.exe shell:
>
> .dump sqlite_sequence
>
> Tell us what you see.
>
>
> >
> > Many thanks
> > Alessandro
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Autoincrement failure

[A . Azzolini]

 SELECT rowid FROM (mytable) WHERE (mystuff) 

returns

37 identical rows(!)  where Rowid = 1



Alessandro




From:
a.azzol...@custom.it
To:
<sqlite-users@sqlite.org>
Date:
22/08/2011 16.26
Subject:
Re: [sqlite] Autoincrement failure



sqlite> .dump sqlite_sequence 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> 


Any idea?

Thanks
Alessandro



From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 16.11
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:

> Hallo,
>
> Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> Every new record seems to be added with rowid=1 overwriting existing
> info...
>
> Any idea about the causes of this issue
> and about extracting lost data (if present)?
>

Please run from the sqlite3.exe shell:

 .dump sqlite_sequence

Tell us what you see.


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



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


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


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

Re: [sqlite] Autoincrement failure

[A . Azzolini]

sqlite> .dump sqlite_sequence 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> 


Any idea?

Thanks
Alessandro



From:
Richard Hipp <d...@sqlite.org>
To:
General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date:
22/08/2011 16.11
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote:

> Hallo,
>
> Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> Every new record seems to be added with rowid=1 overwriting existing
> info...
>
> Any idea about the causes of this issue
> and about extracting lost data (if present)?
>

Please run from the sqlite3.exe shell:

 .dump sqlite_sequence

Tell us what you see.


>
> Many thanks
> Alessandro
> ___
> 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] Autoincrement failure

[Richard Hipp]

On Mon, Aug 22, 2011 at 9:56 AM,  wrote:

> Hallo,
>
> Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> Every new record seems to be added with rowid=1 overwriting existing
> info...
>
> Any idea about the causes of this issue
> and about extracting lost data (if present)?
>

Please run from the sqlite3.exe shell:

 .dump sqlite_sequence

Tell us what you see.


>
> Many thanks
> Alessandro
> ___
> 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] Autoincrement failure

[A . Azzolini]

Hallo,

Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
Every new record seems to be added with rowid=1 overwriting existing 
info...

Any idea about the causes of this issue
and about extracting lost data (if present)?

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

Re: [sqlite] AUTOINCREMENT documentation.

[Marcin Walkowiak - Work (local #2)]

> 
> Not exactly,
> in monotonically increasing sequence next element is always smaller than
> current.
> 

I mean larger :-)

Sorry,
KoD

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

Re: [sqlite] AUTOINCREMENT documentation.

[Marcin Walkowiak - Work (local #2)]

Troeger, Thomas (ext) wrote:
> Hello,
> 
> Sorry if this has been asked before, I couldn't find any reference to it
> in the list archives. I've found a small bug in the documentation, I
> wanted to mention it since I think it should be changed accordingly.
> 
> In http://www.sqlite.org/autoinc.html the documentation says:
> 
> """
> The normal ROWID selection algorithm described above will generate
> monotonically increasing unique ROWIDs ...
> ...
> The AUTOINCREMENT Keyword
> ...
> Note that "monotonically increasing" does not imply that the ROWID
> always increases by exactly one. One is the usual increment. ...
> """
> 
> AFAIK, in a monotonically increasing sequence a value is larger *or
> equal* to the previous value, while in a *strictly* monotonically
> increasing sequence the next value is always larger (see
> http://en.wikipedia.org/wiki/Monotonic_function). This is maybe
> nitpicking, but I think the documentation should be fixed.
> 
> Regards,
> T.
> 
> P.S.: I hope this is the right place to ask such things, I couldn't find
> any documentation improvement link, else I would comment on some
> spelling errors as well ;-)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

Not exactly,
in monotonically increasing sequence next element is always smaller than
current.

Sequence where consecutive elements can be equal is called monotonically
nondecreasing sequence.

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

[sqlite] AUTOINCREMENT documentation.

[Troeger, Thomas (ext)]

Hello,

Sorry if this has been asked before, I couldn't find any reference to it
in the list archives. I've found a small bug in the documentation, I
wanted to mention it since I think it should be changed accordingly.

In http://www.sqlite.org/autoinc.html the documentation says:

"""
The normal ROWID selection algorithm described above will generate
monotonically increasing unique ROWIDs ...
...
The AUTOINCREMENT Keyword
...
Note that "monotonically increasing" does not imply that the ROWID
always increases by exactly one. One is the usual increment. ...
"""

AFAIK, in a monotonically increasing sequence a value is larger *or
equal* to the previous value, while in a *strictly* monotonically
increasing sequence the next value is always larger (see
http://en.wikipedia.org/wiki/Monotonic_function). This is maybe
nitpicking, but I think the documentation should be fixed.

Regards,
T.

P.S.: I hope this is the right place to ask such things, I couldn't find
any documentation improvement link, else I would comment on some
spelling errors as well ;-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] AUTOINCREMENT detection

[D. Richard Hipp]

On Jun 16, 2008, at 4:42 AM, Csaba wrote:

> Hi, I didn't get a response to my first post to this group
> and I didn't get a copy, so perhaps it didn't go through...
>
> Is there any way to detect, based strictly on querying
> the structure of a table/database whether there is an
> AUTOINCREMENT set?  That is to say, without
> analyzing the original SQL creation statement, and
> without inserting a new element into the database.
>
> How is the difference between these two detectable?
> CREATE Table myTab ('AUTOINCREMENT col' INTEGER AUTOINCREMENT  
> PRIMARY KEY)
> vs.
> CREATE Table myTab ('AUTOINCREMENT col' INTEGER PRIMARY KEY)
>

http://www.sqlite.org/c3ref/table_column_metadata.html

D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] AUTOINCREMENT detection

[Csaba]

Hi, I didn't get a response to my first post to this group
and I didn't get a copy, so perhaps it didn't go through...

Is there any way to detect, based strictly on querying
the structure of a table/database whether there is an
AUTOINCREMENT set?  That is to say, without
analyzing the original SQL creation statement, and
without inserting a new element into the database.

How is the difference between these two detectable?
CREATE Table myTab ('AUTOINCREMENT col' INTEGER AUTOINCREMENT PRIMARY KEY)
vs.
CREATE Table myTab ('AUTOINCREMENT col' INTEGER PRIMARY KEY)

I have not found a difference upon looking at:
SELECT * FROM sqlite_master WHERE type='table' AND name='myTab';
PRAGMA table_info('myTab');

The following is empty:
PRAGMA index_list('myTab');
so index_info does not seem relevant either.

Thanks,
Csaba Gabor from Vienna
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] autoincrement and fts2?

[Joe Wilson]

--- Scott Hess <[EMAIL PROTECTED]> wrote:
> S, as far as I can tell, this behaviour changed in October, with
> http://www.sqlite.org/cvstrac/chngview?cn=3470 .  Which is before fts2
> even existed!  So fts2 has been broken in this way essentially
> forever.  *sigh*.  [I'm not entirely clear why that change introduced
> this difference, but it did, so be it.]

I'm confused by what behavior you mean... 
VACUUM has changed implicit rowids long before that patch:

SQLite version 3.2.0 (~ March 2005)
Enter ".help" for instructions
sqlite> CREATE TABLE t(a);
sqlite> INSERT INTO "t" VALUES('one');
sqlite> INSERT INTO "t" VALUES('two');
sqlite> INSERT INTO "t" VALUES('three');
sqlite> select rowid, * from t;
1|one
2|two
3|three
sqlite> delete from t where a = 'one';
sqlite> select rowid, * from t;
2|two
3|three
sqlite> vacuum;
sqlite> select rowid, * from t;
1|two
2|three

> Anyhow, moving on...  anyone have opinions on how to handle this?  The
> patches to fix fts1/2 are simple, but mean that older code will break
> if it tries to read the database created by the patched version.
> Older code is already broken, but only if you use vacuum.  My
> inclination is to add code to fts2 and fts1 to upgrade tables, and put
> a prominent disclaimer somewhere.

If you upgrade the tables in a new fts1/fts2 library, you cannot return
to using the old code. This may surprise people when they revert to a 
previous version to test. I think there's no good way out of this except 
to rename the fts1/fts2 modules to something else.

Other alternatives:

* Lobby drh to retain implicit rowids across a VACUUM.
  That way you can keep the old code without modifications.

* Make VACUUM a no-op if FTS is installed (either at compile time
  or run-time).

I'm not saying these alternatives are good or likely to happen.


   

Sick sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

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

Re: [sqlite] autoincrement and fts2?

[Scott Hess]

S, as far as I can tell, this behaviour changed in October, with
http://www.sqlite.org/cvstrac/chngview?cn=3470 .  Which is before fts2
even existed!  So fts2 has been broken in this way essentially
forever.  *sigh*.  [I'm not entirely clear why that change introduced
this difference, but it did, so be it.]

Anyhow, moving on...  anyone have opinions on how to handle this?  The
patches to fix fts1/2 are simple, but mean that older code will break
if it tries to read the database created by the patched version.
Older code is already broken, but only if you use vacuum.  My
inclination is to add code to fts2 and fts1 to upgrade tables, and put
a prominent disclaimer somewhere.

-scott


On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:

[Forwarding gist of an offline conversation with Joe.]

Looks about like what my patch looks like.  Needs to additionally
handle %_segments.rowid (same problem, but you need to insert more
than 16 docs to see it).

I'm also tossing in some test cases.  My patch should be ready this
afternoon.  I'm not going to check it in, yet, because there's the
question of backwards/forwards compatibility to be addressed.

-scott


On 7/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> Scott, I've attached a possible patch to the ticket.
> It seems to work, but I may have missed some something.
> Tell me what you think.
>
> --- Scott Hess <[EMAIL PROTECTED]> wrote:
> > I've updated the bug with an example of how this breaks fts tables
> > (fts1 or fts2).  I'm thinking on the problem.
> > http://www.sqlite.org/cvstrac/tktview?tn=2510
> >
> > Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
> > break the table if you've done any deletions.
> >
> > I'll try to add more constraints to the summary today,
> >
> > -scott
> >
> >
> > On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> > > WTH!  Wow, this is a very unexpected change.  I must have not been
> > > paying attention at some point.
> > >
> > > -scott
> > >
> > >
> > > On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
> > > >
> > > > >>The standard way to have non-TEXT information associated with rows in
> > > > >>an fts table would be a separate table which joins with the fts table
> > > > >>on rowid.
> > > > >
> > > > >I have not tested this, but if the FTS2 rowid is the standard SQLite 
rowid, I believe that
> > it will be affected by VACUUM change of rowids recently reported on this 
list? If so, could this
> > be fixed?
> > > >
> > > > VACUUM does modify FTS2 rowids. Here is the test:
> > > >
> > > >   drop table if exists a;
> > > >
> > > >   create virtual table a using fts2 (t);
> > > >
> > > >   insert into a (t) values ('one');
> > > >   insert into a (t) values ('two');
> > > >   insert into a (t) values ('three');
> > > >
> > > >   select rowid, * from a;
> > > >
> > > >   delete from a where t = 'two';
> > > >   vacuum;
> > > >
> > > >   select rowid, * from a;
> > > >
> > > > Unfortunately there is no workaround since table a is auto-generated by 
the FTS2 module.
> > Created ticket #2510.
> > > >
> > > >
> > > > 
-
> > > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > > 
-
> > > >
> > > >
> > >
> >
> > 
-
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > 
-
> >
> >
>
>
>
>
> 

> Looking for a deal? Find great prices on flights and hotels with Yahoo! 
FareChase.
> http://farechase.yahoo.com/
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



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

Re: [sqlite] autoincrement and fts2?

[Scott Hess]

[Forwarding gist of an offline conversation with Joe.]

Looks about like what my patch looks like.  Needs to additionally
handle %_segments.rowid (same problem, but you need to insert more
than 16 docs to see it).

I'm also tossing in some test cases.  My patch should be ready this
afternoon.  I'm not going to check it in, yet, because there's the
question of backwards/forwards compatibility to be addressed.

-scott


On 7/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote:

Scott, I've attached a possible patch to the ticket.
It seems to work, but I may have missed some something.
Tell me what you think.

--- Scott Hess <[EMAIL PROTECTED]> wrote:
> I've updated the bug with an example of how this breaks fts tables
> (fts1 or fts2).  I'm thinking on the problem.
> http://www.sqlite.org/cvstrac/tktview?tn=2510
>
> Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
> break the table if you've done any deletions.
>
> I'll try to add more constraints to the summary today,
>
> -scott
>
>
> On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> > WTH!  Wow, this is a very unexpected change.  I must have not been
> > paying attention at some point.
> >
> > -scott
> >
> >
> > On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
> > >
> > > >>The standard way to have non-TEXT information associated with rows in
> > > >>an fts table would be a separate table which joins with the fts table
> > > >>on rowid.
> > > >
> > > >I have not tested this, but if the FTS2 rowid is the standard SQLite 
rowid, I believe that
> it will be affected by VACUUM change of rowids recently reported on this 
list? If so, could this
> be fixed?
> > >
> > > VACUUM does modify FTS2 rowids. Here is the test:
> > >
> > >   drop table if exists a;
> > >
> > >   create virtual table a using fts2 (t);
> > >
> > >   insert into a (t) values ('one');
> > >   insert into a (t) values ('two');
> > >   insert into a (t) values ('three');
> > >
> > >   select rowid, * from a;
> > >
> > >   delete from a where t = 'two';
> > >   vacuum;
> > >
> > >   select rowid, * from a;
> > >
> > > Unfortunately there is no workaround since table a is auto-generated by 
the FTS2 module.
> Created ticket #2510.
> > >
> > >
> > > 
-
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > 
-
> > >
> > >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>





Looking for a deal? Find great prices on flights and hotels with Yahoo! 
FareChase.
http://farechase.yahoo.com/

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




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

Re: [sqlite] autoincrement and fts2?

[Scott Hess]

You can, and I'm working on a patch to do this to see how it might look.

There's the question of how to handle existing tables.

-scott


On 7/17/07, Chris Wedgwood <[EMAIL PROTECTED]> wrote:

On Tue, Jul 17, 2007 at 09:37:43AM -0700, Scott Hess wrote:

> Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
> break the table if you've done any deletions.

Can you not not have the fts[12] code explicitly mark the rowid
columns as integer primary key in the schema?  Then vacuum will leave
them alone.

  sqlite> create table t2 ( oid integer primary key, c1 text );
  sqlite> insert into t2(c1) values ('one');
  sqlite> insert into t2(c1) values ('two');
  sqlite> select oid,c1 from t2;
  1|one
  2|two
  sqlite> delete from t2 where oid=1;
  sqlite> vacuum;
  sqlite> select oid,c1 from t2;
  2|two

I opened a bug about this subtle difference a while ago (I think it's
fine, it should just be documented).



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

Re: [sqlite] autoincrement and fts2?

[Joe Wilson]

Scott, I've attached a possible patch to the ticket.
It seems to work, but I may have missed some something.
Tell me what you think.

--- Scott Hess <[EMAIL PROTECTED]> wrote:
> I've updated the bug with an example of how this breaks fts tables
> (fts1 or fts2).  I'm thinking on the problem.
> http://www.sqlite.org/cvstrac/tktview?tn=2510
> 
> Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
> break the table if you've done any deletions.
> 
> I'll try to add more constraints to the summary today,
> 
> -scott
> 
> 
> On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> > WTH!  Wow, this is a very unexpected change.  I must have not been
> > paying attention at some point.
> >
> > -scott
> >
> >
> > On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
> > >
> > > >>The standard way to have non-TEXT information associated with rows in
> > > >>an fts table would be a separate table which joins with the fts table
> > > >>on rowid.
> > > >
> > > >I have not tested this, but if the FTS2 rowid is the standard SQLite 
> > > >rowid, I believe that
> it will be affected by VACUUM change of rowids recently reported on this 
> list? If so, could this
> be fixed?
> > >
> > > VACUUM does modify FTS2 rowids. Here is the test:
> > >
> > >   drop table if exists a;
> > >
> > >   create virtual table a using fts2 (t);
> > >
> > >   insert into a (t) values ('one');
> > >   insert into a (t) values ('two');
> > >   insert into a (t) values ('three');
> > >
> > >   select rowid, * from a;
> > >
> > >   delete from a where t = 'two';
> > >   vacuum;
> > >
> > >   select rowid, * from a;
> > >
> > > Unfortunately there is no workaround since table a is auto-generated by 
> > > the FTS2 module.
> Created ticket #2510.
> > >
> > >
> > > -
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > -
> > >
> > >
> >
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 



   

Looking for a deal? Find great prices on flights and hotels with Yahoo! 
FareChase.
http://farechase.yahoo.com/

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

Re: [sqlite] autoincrement and fts2?

[Scott Hess]

I've updated the bug with an example of how this breaks fts tables
(fts1 or fts2).  I'm thinking on the problem.
http://www.sqlite.org/cvstrac/tktview?tn=2510

Summary: In sqlite 3.4, running vacuum with fts2 or fts1 tables can
break the table if you've done any deletions.

I'll try to add more constraints to the summary today,

-scott


On 7/17/07, Scott Hess <[EMAIL PROTECTED]> wrote:

WTH!  Wow, this is a very unexpected change.  I must have not been
paying attention at some point.

-scott


On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:
>
> >>The standard way to have non-TEXT information associated with rows in
> >>an fts table would be a separate table which joins with the fts table
> >>on rowid.
> >
> >I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, 
I believe that it will be affected by VACUUM change of rowids recently reported on 
this list? If so, could this be fixed?
>
> VACUUM does modify FTS2 rowids. Here is the test:
>
>   drop table if exists a;
>
>   create virtual table a using fts2 (t);
>
>   insert into a (t) values ('one');
>   insert into a (t) values ('two');
>   insert into a (t) values ('three');
>
>   select rowid, * from a;
>
>   delete from a where t = 'two';
>   vacuum;
>
>   select rowid, * from a;
>
> Unfortunately there is no workaround since table a is auto-generated by the 
FTS2 module. Created ticket #2510.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



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

Re: [sqlite] autoincrement and fts2?

[Scott Hess]

WTH!  Wow, this is a very unexpected change.  I must have not been
paying attention at some point.

-scott


On 7/17/07, Ralf Junker <[EMAIL PROTECTED]> wrote:


>>The standard way to have non-TEXT information associated with rows in
>>an fts table would be a separate table which joins with the fts table
>>on rowid.
>
>I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I 
believe that it will be affected by VACUUM change of rowids recently reported on 
this list? If so, could this be fixed?

VACUUM does modify FTS2 rowids. Here is the test:

  drop table if exists a;

  create virtual table a using fts2 (t);

  insert into a (t) values ('one');
  insert into a (t) values ('two');
  insert into a (t) values ('three');

  select rowid, * from a;

  delete from a where t = 'two';
  vacuum;

  select rowid, * from a;

Unfortunately there is no workaround since table a is auto-generated by the 
FTS2 module. Created ticket #2510.


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




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

Re: [sqlite] autoincrement and fts2?

[Ralf Junker]

>>The standard way to have non-TEXT information associated with rows in
>>an fts table would be a separate table which joins with the fts table
>>on rowid. 
>
>I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I 
>believe that it will be affected by VACUUM change of rowids recently reported 
>on this list? If so, could this be fixed?

VACUUM does modify FTS2 rowids. Here is the test:

  drop table if exists a;
  
  create virtual table a using fts2 (t);
  
  insert into a (t) values ('one');
  insert into a (t) values ('two');
  insert into a (t) values ('three');
  
  select rowid, * from a;
  
  delete from a where t = 'two';
  vacuum;
  
  select rowid, * from a;

Unfortunately there is no workaround since table a is auto-generated by the 
FTS2 module. Created ticket #2510. 


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

Re: [sqlite] autoincrement and fts2?

[Ralf Junker]

>The rowid is the standard SQLite rowid, so it does provide an INTEGER
>PRIMARY KEY AUTOINCREMENT column.
>
>The standard way to have non-TEXT information associated with rows in
>an fts table would be a separate table which joins with the fts table
>on rowid. 

I have not tested this, but if the FTS2 rowid is the standard SQLite rowid, I 
believe that it will be affected by VACUUM change of rowids recently reported 
on this list? If so, could this be fixed?

Ralf 


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

Re: [sqlite] autoincrement and fts2?

[Scott Hess]

On 7/16/07, Adam Megacz <[EMAIL PROTECTED]> wrote:

Is there any way to use a INTEGER PRIMARY KEY AUTOINCREMENT on a table
that has FTS2?  Specifying it in the obvious manner looks like it
works, but the column just ends up with nulls in it.


In fts tables all columns other than rowid are of type TEXT.  It
doesn't matter what you put in the type, they will be of type TEXT.
The rowid is the standard SQLite rowid, so it does provide an INTEGER
PRIMARY KEY AUTOINCREMENT column.

The standard way to have non-TEXT information associated with rows in
an fts table would be a separate table which joins with the fts table
on rowid.

-scott

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

[sqlite] autoincrement and fts2?

[Adam Megacz]

Is there any way to use a INTEGER PRIMARY KEY AUTOINCREMENT on a table
that has FTS2?  Specifying it in the obvious manner looks like it
works, but the column just ends up with nulls in it.

  - a

-- 
PGP/GPG: 5C9F F366 C9CF 2145 E770  B1B8 EFB1 462D A146 C380

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

[sqlite] Autoincrement step size

[mikpol]

Hi Everyone,

I would like to be able to change the step size of an AUTOINCREMENT rowid,
so that it is incremented by some integer other than 1. The purpose is for
database replication, for which I need rows of a table in two different
databases to have non-overlapping rowid ranges. I have tried to do this with
a trigger which changes sqlite_sequence system table after each update as
shown below, but it did not work:


CREATE TABLE test (id integer primary key autoincrement, stuff text);
INSERT into test (stuff) values ('one');

SELECT * from sqlite_sequence;
nameseq   
--  --
test1 

CREATE TRIGGER test_trigger AFTER INSERT ON test FOR EACH ROW BEGIN update
sqlite_sequence set seq=seq+1 where name='test'; END;

INSERT into test (stuff) values ('two');
INSERT into test (stuff) values ('three');

SELECT * from test;
id  stuff 
--  --
1   one   
2   two   
3   three  

SELECT * from sqlite_sequence;
nameseq   
--  --
test3 



I've also tried to set an update trigger on sqlite_sequence table, but got
an error: "SQL error: cannot create trigger on system table". Could you
please tell me why above doesn't work? Also, if anyone has any suggestions
on how to change the step size of an AUTOINCREMENT rowid in a table, I would
very much appreciate to hear your opinion.

Thanks in advance.
-- 
View this message in context: 
http://www.nabble.com/Autoincrement-step-size-tf2925868.html#a8179183
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] autoincrement and integer primary key

[Mario Frasca]

[EMAIL PROTECTED] wrote:


In the FAQ's on the web site it indicated that when the primary key is
autoincrement, the data type is a signed 64 bit number.  Has this been your
experience?
 

never noticed...  not in Python with the sqlite modules I have...  and 
no difference whether I use the 'autoincrement' keyword or not, as long 
as we're talking about 'integer primary key' / oid...


Python 2.3.5 (#2, Oct 16 2006, 19:19:48)
{also tried with no difference:
Python 2.4.1 (#2, Oct 18 2006, 20:58:01)
}
[GCC 3.3.5 (Debian 1:3.3.5-13)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from pysqlite2 import dbapi2 as sqlite
>>> db = sqlite.connect(':memory:')
>>> cr = db.cursor()
>>> cr.execute('create table test(pk integer primary key autoincrement, 
v string)')

>>> cr.execute("insert into test (v) values ('test')")
>>> cr.execute("select * from test")
>>> cr.fetchone()
(1, u'test')
>>> cr.execute("select * from test")
>>> [type(i) for i in cr.fetchone()]
[, ]
>>> type(1L)

>>>

Python 2.4.4 (#2, Oct 20 2006, 00:57:46)
[GCC 4.1.2 20061007 (prerelease) (Debian 4.1.1-16)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite
>>> db = sqlite.connect(':memory:')
>>> cr = db.cursor()
>>> cr.execute('create table test(pk integer primary key, v string)')
>>> cr.execute("insert into test (v) values ('test')")
>>> cr.execute("select * from test")
>>> cr.fetchone()
(1, 'test')
>>> cr.execute("select * from test")
>>> [type(i) for i in cr.fetchone()]
[, ]
>>>

in short: no, it's not my experience.

MF

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