optimize and test db_reposition_respace()

diff --git a/db.php b/db.php
index 2dccfbc..5a9ef89 100644 (file)
--- a/db.php
+++ b/db.php
@@ -466,10 +466,22 @@ function db_reposition_respace($table, $field, $where = '') {
                return;
        }
        $inc = floor(DB_ORD_MAX / ($c + 1));
-       $cur = $inc;
-       foreach($ids as $id) {
-               db_update($table, $field, $cur, 'where id=%i', $id);
-               $cur += $inc;
+       $ord = $inc;
+       $count = count($ids);
+       for ($i = 0; $i < $count; $i += 1000) {
+               $values = [];
+               $j_max = min($count, $i + 1000);
+               for ($j = $i; $j < $j_max; ++$j) {
+                       $id = $ids[$j];
+                       $values[] = "($id,$ord)";
+                       $ord += $inc;
+               }
+               $sql =
+                       "insert into $table (id,$field) values "
+                       . implode(',', $values)
+                       . " on duplicate key update $field=VALUES($field)"
+               ;
+               db_send_query($sql);
        }
 }
 

diff --git a/test/db_reposition.php b/test/db_reposition.php
new file mode 100644 (file)
index 0000000..299974d
--- /dev/null
+++ b/test/db_reposition.php
@@ -0,0 +1,93 @@
+<?php
+
+# This program is in the public domain within the United States. Additionally,
+# we waive copyright and related rights in the work worldwide through the CC0
+# 1.0 Universal public domain dedication, which can be found at
+# http://creativecommons.org/publicdomain/zero/1.0/
+
+
+# to run this test:
+#
+# option 1: with wfpl-cms
+#
+#     set up wfpl-cms
+#
+#     copy/rename this file to test_db_reposition.php in your main dir
+#
+#     visit http://yourdomain.tld/test_db_reposition
+#
+#     refresh to test more times
+#
+#     drop the "test_db_reposition" table when you're done
+#
+# option 2:
+#
+#     require inc/wfpl/db.php
+#     function message($msg) { print "$msg\n"; }
+#     db_connect(...)
+#     test_db_reposition_main()
+
+
+function test_db_reposition_main () {
+       db_send_query(
+               'create table if not exists test_db_reposition ('
+               . ' id int unique auto_increment,'
+               . ' ord int(11) not null default 0'
+               . ')'
+       );
+       db_delete('test_db_reposition');
+       $ids = [];
+       for ($i = 0; $i < 10; ++$i) {
+               db_insert('test_db_reposition', 'ord', $i + 1);
+               $ids[] = db_auto_id();
+       }
+
+       $max_tests = 2000;
+       for ($tests = 0; $tests < $max_tests; ++$tests) {
+               # decide which one to move, and to where
+               $to = $from = rand(0, 9);
+               while ($to === $from) {
+                       if ($tests < $max_tests / 3) {
+                               # full range (respace rare)
+                               $to = rand(0, 10);
+                       } elseif ($tests < $max_tests / 3 * 2) {
+                               # never place first (respaces)
+                               $to = rand(1, 10);
+                       } else {
+                               # never place last (respaces)
+                               $to = rand(0, 9);
+                       }
+               }
+
+               #message("from: $from (id " . $ids[$from] . "), to: $to");
+
+               # move it in the db
+               $new_ord = db_reposition(
+                       'test_db_reposition', # table
+                       $ids[$from], # id of row to move
+                       $to, # position to move it to (0 for first, 1 for 2nd, etc)
+               );
+               db_update('test_db_reposition', 'ord', $new_ord, 'where id=%i', $ids[$from]);
+
+               # move it in our cache
+               $moving = array_splice($ids, $from, 1);
+               array_splice(
+                       $ids, # array to operate on
+                       ($to > $from) ? ($to - 1) : ($to), # adjust if moving into the part we moved in the first splice
+                       0, # don't delete anything
+                       $moving # insert these
+               );
+
+               # check if it matches
+               if (($tests % 10) === 9) {
+                       $db_ids = db_get_column('test_db_reposition', 'id', 'order by ord');
+                       for ($i = 0; $i < 10; ++$i) {
+                               if ($ids[$i] != $db_ids[$i]) {
+                                       message('fail');
+                                       return;
+                               }
+                       }
+               }
+       }
+       message("pass! $max_tests repositions checked every 10");
+}