From: Jason Woofenden <jason@jasonwoof.com>
Date: Tue, 5 Feb 2019 06:31:58 +0000 (-0500)
Subject: optimize and test db_reposition_respace()
X-Git-Url: https://jasonwoof.com/gitweb/?p=wfpl.git;a=commitdiff_plain;h=19e2f5c79c22fd1585ca6569ad25e7ecf06ff7f0

optimize and test db_reposition_respace()
---

diff --git a/db.php b/db.php
index 2dccfbc..5a9ef89 100644
--- 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
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");
+}