ORDER BY random () with seed in SQLITE

Question

I would like to implement paging for random dialing

Select * from Animals ORDER BY random(SEED) LIMIT 100 OFFSET 50 

I tried to set int on some integer and on some fracture. Does not work

How can I highlight a random number in sqlite?

I use the chance here with an empty voice, because a similar question already exists - Visit SQLite RANDOM () . I just did not get the php solution.

Answer 1

Short answer:

You can not. The SQLite random () function does not support the initial value.

Not so short answer:

Checking SQLite func.c shows that random () is defined without any parameters.

 VFUNCTION(random, 0, 0, 0, randomFunc ), 

.. and this randomFunc () just calls sqlite3_randomness () (again without any explicit initial value) to get a random sizeof (sqlite_int64) byte.

An internal implementation of sqlite3_randomness () (see random.c ) will install the RC4 pseudo-random number generator for the first time used with random initial values ​​obtained from the OS:

  /* Initialize the state of the random number generator once, ** the first time this routine is called. The seed value does ** not need to contain a lot of randomness since we are not ** trying to do secure encryption or anything like that... ** ** [..] */ if( !wsdPrng.isInit ){ [..] sqlite3OsRandomness(sqlite3_vfs_find(0), 256, k); [..] wsdPrng.isInit = 1; } 

Actually, the SQLite unit test functions simply use memcpy () in the sqlite3Prng global structure to save or restore the state of PRNG during test runs.

So, if you do not want to do something strange (for example, create a temporary table of consecutive numbers (1..max (Animals)), shuffle them around and use them to select "random seeds" of RowIds from your animals table) I suppose you were unlucky.

Answer 2

Usually I did not copy the existing answer, but I see that you left a comment asking the author of this answer to explain how it has been working several weeks ago, and there were no explanations. Therefore, I will copy the relevant part and try to explain what is happening. If this explanation is good, go and vote for the original answer.

 $seed = md5(mt_rand()); $prng = ('0.' . str_replace(array('0', 'a', 'b', 'c', 'd', 'e', 'f'), array('7', '3', '1', '5', '9', '8', '4'), $seed )) * 1; $query = 'SELECT id, name FROM table ORDER BY (substr(id * ' . $prng . ', length(id) + 2)'; 

The first two lines are only for creating a sort seed. The result is a decimal number with a lot of decimal places, for example:

 0.54534238371923827955579364758491 

Then sql select uses this number to multiply by the numeric row identifier of each row of the SQLite table. Then the rows are sorted according to the decimal part of the product received. Using fewer decimal places, the sort order will look something like this:

 row id row id * seed sort order 1 0.545342384 545342384 2 1.090684767 090684767 3 1.636027151 636027151 4 2.181369535 181369535 5 2.726711919 726711919 6 3.272054302 272054302 7 3.817396686 817396686 8 4.362739070 362739070 

After sorting, this will be the result:

 row id row id * seed sort order 2 1.090684767 090684767 4 2.181369535 181369535 6 3.272054302 272054302 8 4.362739070 362739070 1 0.545342384 545342384 3 1.636027151 636027151 5 2.726711919 726711919 7 3.817396686 817396686 

In this example, I used only eight lines, so the result is not very random. With more lines, the result will seem more random.

This solution will give you the same order if:

• You use the same seed
• There are no new rows in the table, and not a single row has been deleted from the table.

Answer 3

I don’t know if you want a PHP and iOS solution, but if you are only interested in iOS and not interested in using the built-in sqlite random () function, you can declare a user-defined function, use one that takes the seed parameter in your queries.

 sqlite3_create_function(database, "CUSTOM_RANDOM", 1, SQLITE_UTF8, NULL, &CustomRandomSQLite, NULL, NULL); 

.

 void CustomRandomSQLite(sqlite3_context* context, int argc, sqlite3_value** argv) { if(argc == 1 && sqlite3_value_type(argv[0]) == SQLITE_INTEGER) { const int seed = sqlite3_value_int(argv[0]); const int result = ...; sqlite3_result_int(context, result); } else { sqlite3_result_error(context, "Invalid", 0); } } 

.

 Select * from Animals ORDER BY CUSTOM_RANDOM(SEED) LIMIT 100 OFFSET 50 

Answer 4

I use this for random from seed in my javascript game. I am sure you can easily convert it to sql

 seed: function(max) { if(typeof this._random === 'undefined') this._random = max; // init on first run this._random = (this._random * 9301 + 49297) % 233280; return Math.floor(this._random / (233280.0) * max); }