dbfix - fix typo and close another possible inconsistency

This patch fixes a typo in one request producing a SQL error.
It also close an issue where param_values could point to
inexistant param_names after the deduplications of those.
It does this by rewriting the param_values to point to the
highest id of the param_names of the same name.
The same operation is performed on the tags.

Signed-off-by: Brice Figureau <brice-puppet@daysofwonder.com>

1 files changed, 37 insertions, 3 deletions

diff --git a/ext/dbfix.sql b/ext/dbfix.sql
index d22914adf..960369117 100644
--- a/ext/dbfix.sql
+++ b/ext/dbfix.sql
@@ -35,6 +35,23 @@ FROM param_values AS bad_rows
     good_rows.resource_id = bad_rows.resource_id AND 
     good_rows.max_id <> bad_rows.id;
 
+-- rewrite param_values that points to duplicated param_names
+-- to point to the highest param_name id.
+UPDATE 
+  param_values v 
+  INNER JOIN
+  param_names n
+  ON n.id = v.param_name_id
+  INNER JOIN
+  (
+    SELECT name, MAX(id) as max_id
+    FROM param_names
+    GROUP BY name
+    HAVING count(*) > 1
+  ) nmax ON n.name = nmax.name
+SET
+  v.param_name_id = nmax.max_id;
+
 -- Remove duplicate param_names, and keep the latest one
 DELETE bad_rows.*
 FROM param_names AS bad_rows
@@ -48,12 +65,12 @@ FROM param_names AS bad_rows
     good_rows.name = bad_rows.name AND
     good_rows.max_id <> bad_rows.id;
 
--- Remove duplicate resource_tags, and keep the latest one
+-- Remove duplicate resource_tags, and keep the highest one
 DELETE bad_rows.*
 FROM resource_tags AS bad_rows
   INNER JOIN (
     SELECT resource_id,puppet_tag_id, MAX(id) as max_id
-    FROM param_names
+    FROM resource_tags
     GROUP BY resource_id,puppet_tag_id
     HAVING count(*) > 1
   ) AS good_rows 
@@ -62,7 +79,24 @@ FROM resource_tags AS bad_rows
     good_rows.puppet_tag_id = bad_rows.puppet_tag_id AND
     good_rows.max_id <> bad_rows.id;
 
--- Remove duplicate puppet_tags, and keep the latest one
+-- rewrite resource_tags that points to duplicated puppet_tags
+-- to point to the highest puppet_tags id.
+UPDATE 
+  resource_tags v 
+  INNER JOIN
+  puppet_tags n
+  ON n.id = v.puppet_tag_id
+  INNER JOIN
+  (
+    SELECT name, MAX(id) as max_id
+    FROM puppet_tags
+    GROUP BY name
+    HAVING count(*) > 1
+  ) nmax ON n.name = nmax.name
+SET
+  v.puppet_tag_id = nmax.max_id;
+
+-- Remove duplicate puppet_tags, and keep the highest one
 DELETE bad_rows.*
 FROM puppet_tags AS bad_rows
   INNER JOIN (