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

Reply via email to