On 04.03.2011, at 22:21, Drake Wilson wrote: > Quoth Enrico Thierbach <e...@open-lab.org>, on 2011-03-04 22:11:07 +0100: >> If I insert a record into a table with a primary key column id, >> Sqlite assigns a ROWID as outlined here >> http://www.sqlite.org/autoinc.html. However, I would like to assign >> a totally random rowid. > > Why do you want to do this? In particular, why would it not work to > randomize the values from the application side during inserts? > > Random numbers collide faster than you might expect if you're not > familiar with the birthday paradox; normally, truly-random values that > are expected to have no collisions are generated using entropy from > the environment and are at least 128 bits long. I suspect this is not > what you want. If you just want them to be "random-looking" then it > may be more convenient to relate the underlying ID and the exterior ID > through a suitable permutation of the 64-bit integer space. If > neither of those is true, you're probably looking at probing several > times to avoid collisions, and that's not something the stock "pick a > new row ID" mechanism handles AFAIK. >
Hi drake, thanks for your answer. I am trying to build a solution where two or more databases can synchronize with each other. As in my problem area the databases are pretty small (probably less than a 1000 rows), the collision probability given roughly 64 bit of randomness would be about 1000 / 2^32. This is less than 10^-6, which is good enough for me. However, I am aware that there are better ways to generate IDs that are guaranteed to be different in different databases. I do have a working solution for synching my databases (with randomly generated ROWIDs). The problem I face is that I cannot get the rowid of a newly created record to pass thru back into the application using last_insert_row_id. To explicitely generate an ID in the application, however, is not a suitable solution for me. This must work out of the box (i.e. on a database) with any application that uses this database. Therefore I am somewhat limited to what SQLite can give me via SQL (or probably by extending SQLite via its C interface). /eno _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users