Change sochild view to use type registration pattern.

diff --git a/enyo-client/database/source/manifest.js b/enyo-client/database/source/manifest.js
index a986adc..7c4f568 100644 (file)
--- a/enyo-client/database/source/manifest.js
+++ b/enyo-client/database/source/manifest.js
@@ -10,6 +10,7 @@
     "xt/trigger_functions/owner_record_did_change.sql",
     "xt/trigger_functions/sales_order_did_change.sql",
     "xt/trigger_functions/sharetype_did_change.sql",
+    "xt/trigger_functions/sordtype_did_change.sql",
     "xt/trigger_functions/taxtype_record_did_change.sql",
     "xt/trigger_functions/usr_did_change.sql",
     "xt/trigger_functions/usrpref_did_change.sql",
@@ -164,7 +165,6 @@
     "xt/views/cmiteminfo.sql",
     "xt/views/cntctinfo.sql",
     "xt/views/coheadinfo.sql",
-    "xt/views/sochild.sql",
     "xt/views/coiteminfo.sql",
     "xt/views/crmacct_users.sql",
     "xt/views/crmacctaddr.sql",

diff --git a/enyo-client/database/source/xt/tables/sordtype.sql b/enyo-client/database/source/xt/tables/sordtype.sql
index 76335e6..e4af351 100644 (file)
--- a/enyo-client/database/source/xt/tables/sordtype.sql
+++ b/enyo-client/database/source/xt/tables/sordtype.sql
@@ -1,11 +1,39 @@
--- table definition
+drop view if exists xt.sochild cascade;
 
-select xt.create_table('sordtype', 'xt');
-select xt.add_column('sordtype','sordtype_id', 'serial', 'primary key', 'xt');
-select xt.add_column('sordtype','sordtype_tblname', 'text', '', 'xt');
-select xt.add_column('sordtype','sordtype_code', 'text', '', 'xt');
+-- placeholder view
+create view xt.sochild as
+select
 
-comment on table xt.sordtype is 'Sales Order Child Type Map';
+  null::integer as sochild_id,
+  null::text as sochild_type,
+  null::uuid as sochild_uuid,
+  null::text as sochild_key,
+  null::text as sochild_number,
+  null::character(1) as sochild_status,
+  null::date as sochild_duedate,
+  null::numeric(18,6) as sochild_qty;
+
+select dropIfExists('TRIGGER', 'sordtype_did_change', 'xt');
 
+-- Flush the current table. We rebuild it on each run.
+delete from xt.sordtype;
 
+-- table definition
+select xt.create_table('sordtype', 'xt');
+select xt.add_column('sordtype','sordtype_id', 'serial', 'primary key', 'xt', 'The sordtype primary key.');
+select xt.add_column('sordtype','sordtype_nsname', 'text', 'not null', 'xt', 'sordtype child table namespace schema');
+select xt.add_column('sordtype','sordtype_tblname', 'text', 'not null', 'xt', 'sordtype child table name');
+select xt.add_column('sordtype','sordtype_code', 'text', '', 'xt', 'sordtype code');
+select xt.add_column('sordtype','sordtype_col_sochild_id', 'text', 'not null', 'xt', 'sordtype child table sochild_id column name');
+select xt.add_column('sordtype','sordtype_col_sochild_uuid', 'text', 'not null', 'xt', 'sordtype child table sochild_uuid column name');
+select xt.add_column('sordtype','sordtype_col_sochild_key', 'text', 'not null', 'xt', 'sordtype child table sochild_key column name');
+select xt.add_column('sordtype','sordtype_col_sochild_number', 'text', 'not null', 'xt', 'sordtype child table sochild_number column name');
+select xt.add_column('sordtype','sordtype_col_sochild_status', 'text', 'not null', 'xt', 'sordtype child table sochild_status column name');
+select xt.add_column('sordtype','sordtype_col_sochild_duedate', 'text', 'not null', 'xt', 'sordtype child table sochild_duedate column name');
+select xt.add_column('sordtype','sordtype_col_sochild_qty', 'text', 'not null', 'xt', 'sordtype child table sochild_qty column name');
+select xt.add_column('sordtype','sordtype_joins', 'text', '', 'xt', 'sordtype child table optional joins column name');
+
+comment on table xt.sordtype is 'Sales Order Child Type Map';
 
+-- create trigger
+create trigger sordtype_did_change after insert or update or delete on xt.sordtype for each row execute procedure xt.sordtype_did_change();

diff --git a/enyo-client/database/source/xt/trigger_functions/sordtype_did_change.sql b/enyo-client/database/source/xt/trigger_functions/sordtype_did_change.sql
new file mode 100644 (file)
index 0000000..858c978
--- /dev/null
+++ b/enyo-client/database/source/xt/trigger_functions/sordtype_did_change.sql
@@ -0,0 +1,45 @@
+create or replace function xt.sordtype_did_change() returns trigger as $$
+/* Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+   See www.xm.ple.com/CPAL for the full text of the software license. */
+
+return (function () {
+
+  var sql,
+    rows,
+    ary = [];
+
+  rows = plv8.execute("select * from xt.sordtype");
+
+  if (rows.length) {
+
+    rows.forEach(function (row) {
+      sql = "select " +
+        row.sordtype_col_sochild_id + " as sochild_id, " +
+        "''" + row.sordtype_code + "''::text" + " as sochild_type, " +
+        row.sordtype_col_sochild_uuid + " as sochild_uuid, " +
+        row.sordtype_col_sochild_key + " as sochild_key, " +
+        row.sordtype_col_sochild_number + " as sochild_number, " +
+        row.sordtype_col_sochild_status + " as sochild_status, " +
+        row.sordtype_col_sochild_duedate + " as sochild_duedate, " +
+        row.sordtype_col_sochild_qty + " as sochild_qty " +
+        "from " + row.sordtype_nsname + "." + row.sordtype_tblname + " ";
+
+      if (row.sordtype_joins) {
+        sql = sql + row.sordtype_joins;
+      }
+
+      plv8.elog(WARNING, "sql: ", sql);
+      ary.push(sql);
+    });
+
+    sql = "select xt.create_view('xt.sochild','" + ary.join(" union all ") + "', true)";
+    plv8.elog(WARNING, "sql2: ", sql);
+    plv8.execute(sql);
+
+  }
+
+  return NEW;
+
+}());
+
+$$ language plv8;

diff --git a/enyo-client/database/source/xt/views/sochild.sql b/enyo-client/database/source/xt/views/sochild.sql
deleted file mode 100644 (file)
index d37bfca..0000000
--- a/enyo-client/database/source/xt/views/sochild.sql
+++ /dev/null
@@ -1,53 +0,0 @@
--- This thing would need to be re-written as a self generating
--- union query like 'xt.act' and 'xt.ordhead' to be truly extensible.
-
-select xt.create_view('xt.sochild', $$
-
-  -- PURCHASE REQUEST
-  select
-    pr_id as sochild_id,
-    sordtype_code as sochild_type,
-    pr.obj_uuid as sochild_uuid,
-    pr.obj_uuid::text as sochild_key,
-    pr_number::text || '-' || pr_subnumber as sochild_number,
-    pr_status as sochild_status,
-    pr_duedate as sochild_duedate,
-    pr_qtyreq as sochild_qty
-  from pr
-    join pg_class c on pr.tableoid = c.oid
-    join xt.sordtype on sordtype_tblname=relname
-
-  union all
-
-  -- PURCHASE ORDER LINE
-  select
-    poitem_id as sochild_id,
-    sordtype_code as sochild_type,
-    poitem.obj_uuid as sochild_uuid,
-    pohead_number as sochild_key,
-    pohead_number || '-' || poitem_linenumber::text as sochild_number,
-    poitem_status as sochild_status,
-    poitem_duedate as sochild_duedate,
-    poitem_qty_ordered as sochild_qty
-  from poitem
-    join pohead on pohead_id=poitem_pohead_id
-    join pg_class c on poitem.tableoid = c.oid
-    join xt.sordtype on sordtype_tblname=relname
-
-  union all
-
-  -- WORK ORDER
-  select
-    wo_id,
-    sordtype_code,
-    obj_uuid as ord_uuid,
-    obj_uuid::text as sochild_key,
-    formatwonumber(wo_id),
-    wo_status as sochild_status,
-    wo_duedate as sochild_duedate,
-    wo_qtyord as sochild_qty
-  from wo
-    join pg_class c on wo.tableoid = c.oid
-    join xt.sordtype on sordtype_tblname=relname
-
-$$);