lib/mysql-functions: mysql_sort_versions new

1 files changed, 59 insertions, 0 deletions

diff --git a/lib/mysql-functions b/lib/mysql-functions
index b6db097..9fe0984 100755
--- a/lib/mysql-functions
+++ b/lib/mysql-functions
@@ -1178,3 +1178,62 @@ mysql_update_versions_ordering() {
       mysql_run_query
   )
 }
+
+# mysql_sort_versions
+# sort the table of versions
+mysql_sort_versions() {
+  ( # new shell is intentional
+    tmp_file=$(
+      mktemp "tmp.mysql_sort_versions.XXXXXXXXXX" --tmpdir
+    )
+    trap 'rm "${tmp_file}"' EXIT
+    {
+      printf 'SELECT '
+      printf '`versions`.`%s`,' \
+        'id' \
+        'epoch' \
+        'version' | \
+        sed 's/,$//'
+      printf ' FROM `versions`;\n'
+    } | \
+      mysql_run_query | \
+      expand_version 3 | \
+      sort -k2n,2 -k3V,3 | \
+      cut -f1 | \
+      cat -n | \
+      awk '{print $1 " " $2}' | \
+      tr ' ' '\t' > \
+      "${tmp_file}"
+    max_order=$(
+      {
+        printf 'SELECT'
+        printf ' GREATEST(%s,' \
+          "$(
+            wc -l < "${tmp_file}"
+          )"
+        printf 'MAX(`versions`.`order`))'
+        printf ' FROM `versions`;\n'
+      } | \
+        mysql_run_query
+    )
+    {
+      printf 'CREATE TEMPORARY TABLE `vs`('
+        printf '`id` BIGINT,'
+        printf '`ord` BIGINT,'
+        printf 'UNIQUE KEY(`id`),'
+        printf 'UNIQUE KEY(`ord`)'
+      printf ');\n'
+      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `vs`(`ord`,`id`);\n' \
+        "${tmp_file}"
+      printf 'UPDATE `versions`'
+      printf ' SET `versions`.`order`=`versions`.`order`+1+%s;\n' \
+        "${max_order}"
+      printf 'COMMIT;\n'
+      printf 'UPDATE `versions`'
+      printf ' JOIN `vs`'
+      printf ' ON `vs`.`id`=`versions`.`id`'
+      printf ' SET `versions`.`order`=`vs`.`ord`;\n'
+    } | \
+      mysql_run_query
+  )
+}