Merge pull request #1877 from gilmoskowitz/i24621_packageModPriv

issue #24621 - allow package content modification during upgrades

diff --git a/foundation-database/public/trigger_functions/pkgcmd.sql b/foundation-database/public/trigger_functions/pkgcmd.sql
index 3905379..be407b8 100644 (file)
--- a/foundation-database/public/trigger_functions/pkgcmd.sql
+++ b/foundation-database/public/trigger_functions/pkgcmd.sql
@@ -45,7 +45,7 @@ CREATE OR REPLACE FUNCTION _pkgcmdalterTrigger() RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 BEGIN
-  IF (pkgMayBeModified(TG_TABLE_SCHEMA)) THEN
+  IF (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
     IF (TG_OP = 'DELETE') THEN
       RETURN OLD;
     ELSE

diff --git a/foundation-database/public/trigger_functions/pkgcmdarg.sql b/foundation-database/public/trigger_functions/pkgcmdarg.sql
index 8eb9cb7..09eaa61 100644 (file)
--- a/foundation-database/public/trigger_functions/pkgcmdarg.sql
+++ b/foundation-database/public/trigger_functions/pkgcmdarg.sql
@@ -27,7 +27,7 @@ CREATE OR REPLACE FUNCTION _pkgcmdargalterTrigger() RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 BEGIN
-  IF (pkgMayBeModified(TG_TABLE_SCHEMA)) THEN
+  IF (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
     IF (TG_OP = 'DELETE') THEN
       RETURN OLD;
     ELSE

diff --git a/foundation-database/public/trigger_functions/pkgimage.sql b/foundation-database/public/trigger_functions/pkgimage.sql
index 1d51cb1..0f8ae1d 100644 (file)
--- a/foundation-database/public/trigger_functions/pkgimage.sql
+++ b/foundation-database/public/trigger_functions/pkgimage.sql
@@ -43,7 +43,7 @@ CREATE OR REPLACE FUNCTION _pkgimagealterTrigger() RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 BEGIN
-  IF (pkgMayBeModified(TG_TABLE_SCHEMA)) THEN
+  IF (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
     IF (TG_OP = 'DELETE') THEN
       RETURN OLD;
     ELSE

diff --git a/foundation-database/public/trigger_functions/pkgmetasql.sql b/foundation-database/public/trigger_functions/pkgmetasql.sql
index 10f7f07..3f9f3c3 100644 (file)
--- a/foundation-database/public/trigger_functions/pkgmetasql.sql
+++ b/foundation-database/public/trigger_functions/pkgmetasql.sql
@@ -52,13 +52,8 @@ $$ LANGUAGE 'plpgsql';
 CREATE OR REPLACE FUNCTION _pkgmetasqlalterTrigger() RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
-DECLARE
-  _isdba        BOOLEAN := false;
-
 BEGIN
-  SELECT rolsuper INTO _isdba FROM pg_roles WHERE (rolname=getEffectiveXtUser());
-
-  IF (pkgMayBeModified(TG_TABLE_SCHEMA)) THEN
+  IF (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
     IF (TG_OP = 'DELETE') THEN
       RETURN OLD;
     ELSE

diff --git a/foundation-database/public/trigger_functions/pkgpriv.sql b/foundation-database/public/trigger_functions/pkgpriv.sql
index edb8f63..b3af003 100644 (file)
--- a/foundation-database/public/trigger_functions/pkgpriv.sql
+++ b/foundation-database/public/trigger_functions/pkgpriv.sql
@@ -47,7 +47,7 @@ CREATE OR REPLACE FUNCTION _pkgprivalterTrigger() RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 BEGIN
-  IF (pkgMayBeModified(TG_TABLE_SCHEMA)) THEN
+  IF (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
     IF (TG_OP = 'DELETE') THEN
       RETURN OLD;
     ELSE

diff --git a/foundation-database/public/trigger_functions/pkgreport.sql b/foundation-database/public/trigger_functions/pkgreport.sql
index 94f9c4b..d32f4f0 100644 (file)
--- a/foundation-database/public/trigger_functions/pkgreport.sql
+++ b/foundation-database/public/trigger_functions/pkgreport.sql
@@ -50,7 +50,7 @@ CREATE OR REPLACE FUNCTION _pkgreportalterTrigger() RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 BEGIN
-  IF (pkgMayBeModified(TG_TABLE_SCHEMA)) THEN
+  IF (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
     IF (TG_OP = 'DELETE') THEN
       RETURN OLD;
     ELSE

diff --git a/foundation-database/public/trigger_functions/pkgscript.sql b/foundation-database/public/trigger_functions/pkgscript.sql
index 954418c..40e2fb4 100644 (file)
--- a/foundation-database/public/trigger_functions/pkgscript.sql
+++ b/foundation-database/public/trigger_functions/pkgscript.sql
@@ -27,7 +27,7 @@ CREATE OR REPLACE FUNCTION _pkgscriptalterTrigger() RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 BEGIN
-  IF (pkgMayBeModified(TG_TABLE_SCHEMA)) THEN
+  IF (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
     IF (TG_OP = 'DELETE') THEN
       RETURN OLD;
     ELSE

diff --git a/foundation-database/public/trigger_functions/pkguiform.sql b/foundation-database/public/trigger_functions/pkguiform.sql
index a5f1a92..dc0e307 100644 (file)
--- a/foundation-database/public/trigger_functions/pkguiform.sql
+++ b/foundation-database/public/trigger_functions/pkguiform.sql
@@ -27,7 +27,7 @@ CREATE OR REPLACE FUNCTION _pkguiformalterTrigger() RETURNS TRIGGER AS $$
 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
 -- See www.xtuple.com/CPAL for the full text of the software license.
 BEGIN
-  IF (pkgMayBeModified(TG_TABLE_SCHEMA)) THEN
+  IF (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
     IF (TG_OP = 'DELETE') THEN
       RETURN OLD;
     ELSE

diff --git a/scripts/lib/util/convert_specialized.js b/scripts/lib/util/convert_specialized.js
index 95b8d52..c952b8a 100644 (file)
--- a/scripts/lib/util/convert_specialized.js
+++ b/scripts/lib/util/convert_specialized.js
@@ -50,10 +50,7 @@ regexp:true, undef:true, strict:true, trailing:true, white:true */
       grade = "0",
       tableName = defaultSchema ? defaultSchema + ".pkgreport" : "report",
       description,
-      disableSql,
-      updateSql,
-      insertSql,
-      enableSql;
+      upsertSql;
 
     if (lines[3].indexOf(" <name>") !== 0 ||
         lines[4].indexOf(" <description>") !== 0) {
@@ -68,40 +65,36 @@ regexp:true, undef:true, strict:true, trailing:true, white:true */
       grade = grade.substring(0, grade.indexOf("<"));
     }
 
-    disableSql = "ALTER TABLE " + tableName + " DISABLE TRIGGER ALL;";
-
-    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 + ";";
-
-    enableSql = "ALTER TABLE " + tableName + " ENABLE TRIGGER ALL;";
-
-    return disableSql + insertSql + updateSql + enableSql;
+    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) {
     var name = path.basename(filename, '.js'),
       tableName = defaultSchema ? defaultSchema + ".pkgscript" : "unknown",
       notes = "", //"xtMfg package",
-      disableSql,
       insertSql,
-      updateSql,
-      enableSql;
-
-    disableSql = "ALTER TABLE " + tableName + " DISABLE TRIGGER ALL;";
+      updateSql;
 
     insertSql = "insert into " + tableName + " (script_name, script_order, script_enabled, " +
       "script_source, script_notes) select " +
@@ -117,21 +110,15 @@ regexp:true, undef:true, strict:true, trailing:true, white:true */
       "$$, script_notes = '" + notes + "' " +
       "where script_name = '" + name + "';";
 
-    enableSql = "ALTER TABLE " + tableName + " ENABLE TRIGGER ALL;";
-
-    return disableSql + insertSql + updateSql + enableSql;
+    return insertSql + updateSql;
   };
 
   var convertFromUiform = function (content, filename, defaultSchema) {
     var name = path.basename(filename, '.ui'),
       tableName = defaultSchema ? defaultSchema + ".pkguiform" : "unknown",
       notes = "", //"xtMfg package",
-      disableSql,
       insertSql,
-      updateSql,
-      enableSql;
-
-    disableSql = "ALTER TABLE " + tableName + " DISABLE TRIGGER ALL;";
+      updateSql;
 
     insertSql = "insert into " + tableName + " (uiform_name, uiform_order, uiform_enabled, " +
       "uiform_source, uiform_notes) select " +
@@ -146,9 +133,7 @@ regexp:true, undef:true, strict:true, trailing:true, white:true */
       "uiform_source = $$" + content + "$$, uiform_notes = '" + notes + "' " +
       "where uiform_name = '" + name + "';";
 
-    enableSql = "ALTER TABLE " + tableName + " ENABLE TRIGGER ALL;";
-
-    return disableSql + insertSql + updateSql + enableSql;
+    return insertSql + updateSql;
   };
 
   exports.conversionMap = {