Sorry to rain on your parade, but " The sqlite_sequence table does not track ROWID changes associated with UPDATE statement, only INSERT statements.", so if you change the (auto-generated) rowid, your "row age to row id" relation no longer holds. Same goes for updating the sqlite_sequence entry associated with the table.
Indeed, if you immediately update the sqlite_sequence entry for your INTEGER PRIMARY KEY to MAXINT64, you will achieve "random" rowids. BTW: Consider the following transaction to implement LRU via rowids: BEGIN -- retrieve the rowid for the target row (store as rowid in calling program) SELECT rowid,... FROM mytable WHERE ...; -- retrieve the sequence value for the table (store as seq in calling program) SELECT seq+1 FROM SQLITE_SEQUENCE WHERE name='mytable'; -- update values and set new rowid UPDATE mytable SET rowid=?seq, ... WHERE rowid=?rowid; -- update sequence value UPDATE SQLITE_SEQUENCE SET seq=?seq WHERE name='mytable'; COMMIT; Yes, this will probably cause foreign keys to break or require execssive work (ON UPDATE CASCADE). -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Eduardo Gesendet: Montag, 27. November 2017 10:28 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug] On Thu, 23 Nov 2017 17:35:08 -0700 "Keith Medcalf" <kmedc...@dessus.com> escribió: > > That is correct. You cannot spell "INTEGER PRIMARY KEY", which is an > explicit alias for the rowid, as anything other than that one > particular correct spelling. > > Personally, I have never found a use for the AUTOINCREMENT option. > Why is it being used? If you declare a INTEGER PRIMARY KEY alone, sqlite could reuse rowid number from deleted rows. Using AUTOINCREMENT forces sqlite to use a rowid bigger than the last one inserted/updated(1), so you can use it to know if one row is older or newer than others, without autoincrement no. You can do similar behavior with a trigger, for example for TEXT columns or automatically with DATE current time. (1) You can change the integer primary key to whatever value you want. > The fact that there's a Highway to Hell but only a Stairway to Heaven > says a lot about anticipated traffic volume. .... See you there then? ;) -- Eduardo <eduardo.mor...@mobelservices.com> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
