I wasn't aware SQLite's PRNG was not like most others. Good to know. I guess one could insert their own random() function if you need repeatability (which is actually a major point of most random number generators). I don't see the seeding exposed in SQLite so you can restart from a given point.
This makes it impossible to generate test cases using this that can be repeated, at least using SQLite's functions. As for Enrico's problem...could you explain a bit more about what you mean by synchronizing your databases? Row ids are considered to be an internal number so why do they matter across databases? It would seem that you shouldn't need it at all. How/why are they synchronizing and why prevent rowid collisions rather than data collisions? Here's another way to do what you want. On your first insert into your database just update the rowid using random. Then each subsequent insert will be 1-up from that. So, in your trigger try this: SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a int); sqlite> create trigger tr after insert on t ...> when (select count(a) from t) = 1 ...> begin ...> update t set rowid=random() where rowid=1; ...> end; sqlite> insert into t values(1); sqlite> insert into t values(2); sqlite> select rowid,a from t; 4193218855921046132|1 4193218855921046133|2 The odds of the starting value being within +/- 1000 of a given 64-bit random number is 2000*(1/2^64 ) or about 1e-16 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 Enrico Thierbach [e...@open-lab.org] Sent: Friday, March 04, 2011 7:18 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Adjusting strategy for ROWIDs 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
