From 2c7511485bb59ae87eb7ef857678c1f46cb5fea4 Mon Sep 17 00:00:00 2001
From: Gil Moskowitz <gmoskowitz@xtuple.com>
Date: Fri, 26 Sep 2014 16:13:50 -0400
Subject: [PATCH] copy updater behavior to avoid report name/grade collisions.
 we might need duplicate this for metasql.

---
 scripts/lib/util/convert_specialized.js | 43 +++++++++++++------------
 1 file changed, 23 insertions(+), 20 deletions(-)

diff --git a/scripts/lib/util/convert_specialized.js b/scripts/lib/util/convert_specialized.js
index 3b89903ef..c952b8a18 100644
--- a/scripts/lib/util/convert_specialized.js
+++ b/scripts/lib/util/convert_specialized.js
@@ -50,8 +50,7 @@ regexp:true, undef:true, strict:true, trailing:true, white:true */
       grade = "0",
       tableName = defaultSchema ? defaultSchema + ".pkgreport" : "report",
       description,
-      updateSql,
-      insertSql;
+      upsertSql;
 
     if (lines[3].indexOf(" <name>") !== 0 ||
         lines[4].indexOf(" <description>") !== 0) {
@@ -66,24 +65,28 @@ regexp:true, undef:true, strict:true, trailing:true, white:true */
       grade = grade.substring(0, grade.indexOf("<"));
     }
 
-    insertSql = "insert into " + tableName + " (report_name, report_descrip, " +
-      "report_source, report_loaddate, report_grade) select " +
-      "'" + name + "'," +
-      "$$" + description + "$$," +
-      "$$" + content + "$$," +
-      "now(), " + grade +
-      " where not exists (select c.report_id from " + tableName + " c " +
-      "where report_name = '" + name +
-      "' and report_grade = " + grade + ");";
-
-    updateSql = "update " + tableName + " set " +
-      " report_descrip = $$" + description +
-      "$$, report_source = $$" + content +
-      "$$, report_loaddate = now() " +
-      "where report_name = '" + name +
-      "' and report_grade = " + grade + ";";
-
-    return insertSql + updateSql;
+    upsertSql = "do language plpgsql $do$" +
+                "declare _id integer := null;" +
+                " begin" +
+                "  update " + tableName + " set " +
+                "    report_descrip = $$" + description + "$$," +
+                "    report_source = $$" + content + "$$," +
+                "    report_loaddate = now() " +
+                "   where report_name = '" + name + "'" +
+                "    and report_grade = " + grade + " returning report_id into _id;" +
+                "  if _id is null then " +
+                "    insert into " + tableName + " (report_name, report_descrip," +
+                "        report_source, report_loaddate, report_grade)" +
+                "      select '" + name + "', $$" + description + "$$," +
+                "        $$" + content + "$$, now(), min(sequence_value)" +
+                "        from sequence" +
+                "       where sequence_value >= " + grade + "" +
+                "         and sequence_value not in (" +
+                "        select c.report_grade from report c where report_name = '" + name + "'" +
+                "       );" +
+                "  end if;" +
+                " end $do$;";
+    return upsertSql;
   };
 
   var convertFromScript = function (content, filename, defaultSchema) {
-- 
2.39.2