SQlite 3 negeert autoincrement statements

[divvid]

Bij de overgang van sqlite2 naar sqlite3 liep ik tegen een vervelend probleem aan. Sqlite3 negeert de autoincrement statements in de tabellen:

schema:
CREATE TABLE potm (id integer auto_increment not null, picture_id integer, primary key(id));

In sqlite2 kon ik simpel het volgende doen:
INSERT INTO potm (picture_id) values ('123')
en de id wordt automatisch opgehoogd zoals ook in b.v. mysql

in sqlite3 krijg ik de message: SQL error: potm.id may not be NULL

Geen idee waarom dit gewijzigd is sinds 2->3 Weet iemand een workaround anders dan continu eerst queries uit te voeren wat nou ook al weer de laatste ID was en deze handmatig te verhogen?

[justmental]

Hier staat wat:
http://libdbi-drivers.sourceforge.net/docs/dbd_sqlite3.pdf
onder hoofdstukje 4.2

[divvid]

Yep, gelezen, bedankt, maar dit lost nog steeds niet mijn probleempje op. Autoincrement doet het helemaal niet meer, zie volgende code:

sqlite> create table test(id integer primary_key auto_increment,test varchar(255));
sqlite> select * from test;
sqlite> insert into test (test) values ('test');
sqlite> select * from test;
|test

Je zou verwachten dat ID opgehoogd wordt naar '1', maar niet dus (in sqlite v2 dus wel). omdat de pdo driver voor sqlite

[divvid]

hmmm, lekker hoor, na wat zoek blijkt het wel te werken, maar NIET op geconverteerde databases. De oplossing: alle tabellen opnieuw aanmaken met een 'create table (id INTEGER PRIMARY KEY, overige kolommen) en dan met de hand de dumps weer inlezen. nah, stomme versie gezeur...en owja, niet vergeten expliciet 'insert into x (id,....) values (NULL, .....) toe te passen.

[Soultaker]

Op de SQlite website staat een aparte pagina over dit onderwerp: http://www.sqlite.org/autoinc.html

Hieruit concludeer ik dat de correcte manier om een auto-increment column te declareren, met type "integer primary key autoincrement" is. auto_increment (met underscore!) accepteert de parser wel, maar die lijkt 'ie te negeren.

De vraag is dus hoe je die database geconverteerd hebt. Je hebt een SQL dump gemaakt neem ik aan? Misschien kun je dan een grote find-and-replace actie uithalen om alle "primary_key auto_increment" the vervangen door "primary key autoincrement"; dat lijkt mij voldoende, en dan hoef je de rest van je queries niet aan te passen.

edit:
Trouwens, als je een SQL dump gebruikt, lijkt me dat je juist niet nieuwe id's wil genereren want anders kloppen je referenties niet meer, toch? Of heb je alleen losse tabellen?

[ Voor 13% gewijzigd door Soultaker op 10-01-2007 00:50 ]

[divvid]

Misschien kun je dan een grote find-and-replace actie uithalen om alle "primary_key auto_increment" the vervangen door "primary key autoincrement"; dat lijkt mij voldoende, en dan hoef je de rest van je queries niet aan te passen.

edit:
Trouwens, als je een SQL dump gebruikt, lijkt me dat je juist niet nieuwe id's wil genereren want anders kloppen je referenties niet meer, toch? Of heb je alleen losse tabellen?

dat is exact wat ik uiteindelijk heb gedaan, de nieuwe id's wil ik natuurlijk genereren in de applicatie als er nieuwe data aan de database toegevoegd wordt. De oude id's blijven gewoon bestaan. bij het inlezen van de dump neemt sqlite3 de id waarde over die je in de insert query meegeeft. Zolang die id waardie niet NULL is kan je hem dus ook zelf toewijzen. Of dit bij andere databases ook zo werkt weet ik niet

[Soultaker]

Ah, dat klinkt dan wel goed. De reden dat 'auto_increment' genegeerd werd is trouwens omdat SQLite alle dataspecificaties die 'ie niet kent negeert (uit compatibiliteit met andere SQL databases, die meer datatypen ondersteunen); je kunt ook een kolom "foo bar baz" declareren bijoorbeeld, waarbij "bar baz" gewoon genegeerd wordt.

Bij de sommige andere databases worden auto-increment velden met een los sequence-object geïmplementeerd en moet je na het importeren je sequence nog op de gewente waarde instellen; sqlite zorgt (met triggers, denk ik) automatisch dat je sequence opgehoogt wordt, ook als je handmatig id's opgeeft.

[Verwijderd]

Dus je hebt een SQL dump gemaakt om over te gaan van versie 2 naar 3. Volgens SQLite is dit de oplossing en hoef je dus geen dump te maken.

quote: http://sqlite.org/version3.html

To convert an SQLite 2.8 database into an SQLite 3.0 database, have ready the command-line shells for both version 2.8 and 3.0. Then enter a command like the following:

sqlite OLD.DB .dump | sqlite3 NEW.DB

Verder is het gebruik van de juiste syntax een vereiste, maar dat is logisch

[Soultaker]

@djingelz: probeer dat eens, dan. Het werkt niet, want als je je kolom in SQLite 2 met auto_increment (met underscore) gedeclareerd had (wat wel werkt in SQLite 2) dan komt 'ie ook zo in de dump terecht, en als je 'm dan importeert in SQLite 3 negeert 'ie die kwalificatie, en heb je een kolom zonder autoincrement.

Tenslotte is dat net zo goed een dump maken, alleen sla je 'm niet expliet op tussendoor. Wel handig als het goed gaat, maar als het niet goed gaat is het toch wel handig om 'm eerst even op te slaan zodat je kunt kijken wat er mis gaat.

[ Voor 5% gewijzigd door Soultaker op 10-01-2007 17:37 ]

[Verwijderd]

Je hebt helemaal gelijk . Ik heb nog even in het SQLite2 bestandsformaat gekeken maar ook daar wordt (evenals in versie 3) het create-statement gewoon als platte tekst in opgeslagen. Als zich hier syntax in bevindt die door de SQLite2 parser wordt gepikt maar niet door de SQLite 3 parser dan heb je inderdaad een probleem. Dit heeft zich hier dus ook voorgedaan (ook bij de oplossing die ik zelf aan droeg ) en is de parser van versie 3 strikter geworden.

Soultaker schreef op woensdag 10 januari 2007 @ 17:01:
...

Bij de sommige andere databases worden auto-increment velden met een los sequence-object geïmplementeerd en moet je na het importeren je sequence nog op de gewente waarde instellen; sqlite zorgt (met triggers, denk ik) automatisch dat je sequence opgehoogt wordt, ook als je handmatig id's opgeeft.

Ter info: De enige juiste manier om een autoincrement toe te passen (a.f.a.i.k.) is als een kolom als volgt wordt defenieerd 'INTEGER PRIMARY KEY AUTOINCREMENT'. Wanneer deze constructie wordt toegepast (ook zonder autoincrement) wordt voor deze kolom het ROWID van de rij gebruikt. De ROWID van een rij wordt intern door SQLite gebruikt als identifier in de B+-tree en is een 64-bits integer (in versie 3). Hiervan wordt ook de hoogst toegevoegde waarde tot dan toe bijgehouden. Wanneer er geen autoincrement wordt toegepast en er een rij wordt toegevoegd (met deze kolom NULL), wordt de eerste vrije ROWID genomen. Wanneer er wel een autoincrement wordt toegepast neemt SQLite de waarde na de hoogste waarde tot dan toe toegevoegd.