lib/mysql-functions: mysql_update_versions_ordering() new

1 files changed, 41 insertions, 0 deletions

diff --git a/lib/mysql-functions b/lib/mysql-functions
index a4cca4e..2ae5832 100755
--- a/lib/mysql-functions
+++ b/lib/mysql-functions
@@ -1142,3 +1142,44 @@ mysql_query_create_toolchain_order() {
   printf ' SELECT `toolchain_order`.`number`,`toolchain_order`.`pkgbase`'
   printf ' FROM `toolchain_order`;\n'
 }
+
+# mysql_update_versions_ordering
+# calculate the correct ordering of `versions` entries
+mysql_update_versions_ordering() {
+  ( # new shell is intentional
+    tmp_file=$(mktemp "${work_dir}/tmp.mysql-functions.mysql_update_versions_ordering.XXXXXX")
+    trap 'rm "${tmp_file}"' EXIT
+    {
+      printf 'SELECT '
+      printf 'CONCAT('
+        printf '`versions`.`epoch`,":",'
+        printf '`versions`.`version`'
+      printf '),'
+      printf '`versions`.`id`'
+      printf ' FROM `versions`;\n'
+    } | \
+      mysql_run_query | \
+      expand_version 1 | \
+      sort -k1V,1 | \
+      cat -n | \
+      sed '
+        s/^\s*\([0-9]\+\)\s\+\S\+\s\+\([0-9]\+\)$/\1\t\2/
+      ' > \
+      "${tmp_file}"
+    {
+      printf 'CREATE TEMPORARY TABLE `v`('
+        printf '`id` BIGINT,'
+        printf '`order` BIGINT,'
+        printf 'PRIMARY KEY `id`(`id`)'
+      printf ');\n'
+      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `v`(`order`,`id`);\n' \
+        "${tmp_file}"
+      printf 'UPDATE `versions`'
+      printf ' JOIN `v`'
+      printf ' ON `v`.`id`=`versions`.`id`'
+      printf ' SET `versions`.`order`=`v`.`order`;\n'
+      printf 'DROP TEMPORARY TABLE `v`;\n'
+    } | \
+      mysql_run_query
+  )
+}