-DO $$
+do $$
/* 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. */
+ See www.xtuple.com/CPAL for the full text of the software license. */
- plv8.execute("select xt.js_init()");
- plv8.execute("alter table xt.orm disable trigger orm_did_change");
+ plv8.execute("select xt.js_init();");
+ plv8.execute("alter table xt.orm disable trigger orm_did_change;");
plv8.execute("delete from xt.orm where orm_json ~ '\"isSystem\":true';");
- plv8.execute("alter table xt.orm enable trigger orm_did_change");
+ plv8.execute("alter table xt.orm enable trigger orm_did_change;");
$$ language plv8;
-DO $$
+do $$
/* 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. */
-
- var sql,
- result,
- viewname,
- schemaname,
- i;
-
- sql = "select schemaname, viewname from pg_views where schemaname in ('xm','sys', 'xt');"
- result = plv8.execute(sql);
- for (i = 0; i < result.length; i++) {
- viewname = result[i].viewname;
- schemaname = result[i].schemaname;
- plv8.execute('drop view if exists ' + schemaname + '.' + viewname + ' cascade;');
- }
-
-$$ language plv8;
+declare
+ sqlstring text;
+begin
+ select string_agg('drop view ' || t.oid::regclass || ';', ' ') into sqlstring
+ from pg_class t
+ join pg_namespace n on n.oid = relnamespace
+ where relkind = 'v'
+ and nspname in ('xm','sys', 'xt');
+ if length(sqlstring) > 0 then
+ execute sqlstring;
+ end if;
+end
+$$;
create or replace function xt.pg_advisory_unlock(oid integer, id integer) returns boolean as $$
+ var sql = "select pg_catalog.pg_advisory_unlock($1, $2) as result;";
+ return plv8.execute(sql, [oid, id])[0].result;
+ /* temporary: let qt client use advisory locks but web client use xt.lock */
+
var pid = plv8.execute("select pg_backend_pid() as pid;")[0].pid,
username = plv8.execute("select geteffectivextuser() as username;")[0].username,
sql = "select * from xt.lock where lock_table_oid = $1 and lock_record_id = $2 and lock_username = $3 and lock_pid = $4;",
create or replace function xt.trylock(oid integer, id integer) returns boolean as $$
+ var sql = "select public.trylock($1, $2) as result;";
+ return plv8.execute(sql, [oid, id])[0].result;
+ /* temporary: let qt client use advisory locks but web client use xt.lock */
+
var pid = plv8.execute("select pg_backend_pid() as pid;")[0].pid,
data = Object.create(XT.Data),
lock = data.tryLock(oid, id, {pid: pid});
"xt/views/share_users_shipto.sql",
"xt/views/share_users_shipto_cntct.sql",
"xt/views/share_users_shipto_addr.sql",
+ "xt/views/share_users_shipto_cust.sql",
"xt/tables/sharetype.sql"
]
}
'username'
);
+-- Customer that a Ship To is on CRM Account's users.
+delete from xt.sharetype where sharetype_tblname = 'share_users_shipto_cust';
+insert into xt.sharetype (
+ sharetype_nsname,
+ sharetype_tblname,
+ sharetype_col_obj_uuid,
+ sharetype_col_username
+) values (
+ 'xt',
+ 'share_users_shipto_cust',
+ 'obj_uuid',
+ 'username'
+);
+
-- Sales Order CRM Account's users.
delete from xt.sharetype where sharetype_tblname = 'share_users_cohead';
insert into xt.sharetype (
--- /dev/null
+/*
+ * This view lists all postgres usernames that are associated with a CRM
+ * Account that owns a resource. That associaiton is either the main user
+ * account, owner's user account, customer's sale rep's user account or
+ * a shared access that has been specifically granted.
+ *
+ * This view can be used to determine which users have personal privilege
+ * access to a Customer that a Ship To is on based on what CRM Account the
+ * Ship To belongs to.
+ */
+
+select xt.create_view('xt.share_users_shipto_cust', $$
+
+ -- Customer that is for a Ship To CRM Account's users.
+ SELECT
+ shipto_cust_crmacct_id.obj_uuid::uuid AS obj_uuid,
+ username::text AS username
+ FROM (
+ SELECT
+ custinfo.obj_uuid,
+ crmacct.crmacct_id
+ FROM shiptoinfo
+ LEFT JOIN crmacct ON crmacct_cust_id = shipto_cust_id
+ LEFT JOIN custinfo ON cust_id = shipto_cust_id
+ ) shipto_cust_crmacct_id
+ LEFT JOIN xt.crmacct_users USING (crmacct_id)
+ WHERE 1=1
+ AND username IS NOT NULL
+ AND obj_uuid IS NOT NULL;
+
+$$, false);
-- 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
- PERFORM pg_advisory_unlock(datid::integer, procpid)
+ IF (compareversion('9.2.0') <= 0)
+ THEN
+ PERFORM pg_advisory_unlock(datid::integer, pid)
FROM pg_stat_activity
- WHERE(procpid = pg_backend_pid());
+ WHERE(pid = pg_backend_pid());
+ ELSE
+ PERFORM pg_advisory_unlock(datid::integer, procpid)
+ FROM pg_stat_activity
+ WHERE(procpid = pg_backend_pid());
+ END IF;
RETURN 0;
END;
return ret;
},
+ /**
+ * Get the current database server version.
+ * If the optional precision argument is passed, return the first prec
+ * fields of the full version number.
+ *
+ * @example
+ * var x = getPgVersion(1), // '9'
+ * xy = getPgVersion(2), // '9.1'
+ * xyz = getPgVersion(3), // '9.1.3'
+ * all = getPgVersion(); // '9.1.3'
+ *
+ * @param {Number} proc - optional precision
+ * @returns {String} X[.Y[.Z]]
+ */
+ getPgVersion: function (prec) {
+ var q = plv8.execute("select setting from pg_settings " +
+ "where name='server_version';"),
+ ret;
+ ret = q[0].setting;
+ if (typeof prec === 'number') {
+ ret = ret.split(".").slice(0,prec).join(".");
+ }
+ return ret;
+ },
+
/**
* Get the oid for a given table name.
*
lockExp,
oid,
pcheck,
+ pgver = 0 + XT.Data.getPgVersion(2),
pid = options.pid || null,
- pidSql = "select usename, procpid " +
+ pidcol = (pgver < 9.2) ? "procpid" : "pid",
+ pidSql = "select usename, {pidcol} " +
"from pg_stat_activity " +
"where datname=current_database() " +
" and usename=$1 " +
- " and procpid=$2;",
+ " and {pidcol}=$2;",
query,
selectSql = "select * " +
"from xt.lock " +
" and lock_record_id = $2;",
username = XT.username;
+ pidSql = pidSql.replace(/{pidcol}/g, pidcol);
+
/* If passed a table name, look up the oid. */
oid = typeof table === "string" ? this.getTableOid(table) : table;
sudo add-apt-repository -y "deb http://apt.postgresql.org/pub/repos/apt/ ${DEBDIST}-pgdg main"
sudo wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get -qq update 2>&1 | tee -a $LOG_FILE
- sudo apt-get -q -y install curl build-essential libssl-dev postgresql-9.1 postgresql-server-dev-9.1 postgresql-contrib-9.1 postgresql-9.1-plv8 2>&1 | tee -a $LOG_FILE
+ sudo apt-get -q -y install curl build-essential libssl-dev \
+ postgresql-${PG_VERSION} postgresql-server-dev-${PG_VERSION} \
+ postgresql-contrib-${PG_VERSION} postgresql-${PG_VERSION}-plv8 2>&1 \
+ | tee -a $LOG_FILE
if [ ! -d "/usr/local/nvm" ]; then
sudo rm -f /usr/local/bin/nvm
sudo nvm alias xtuple $NODE_VERSION
# use latest npm
- npm install -fg npm@1.4.25
+ sudo npm install -fg npm@1.4.25
# npm no longer supports its self-signed certificates
log "telling npm to use known registrars..."
npm config set ca ""
+ sudo chown -R $USER $HOME/.npm
log "installing npm modules..."
npm install --unsafe-perm 2>&1 | tee -a $LOG_FILE
return 1
fi
- PGDIR=/etc/postgresql/9.1/main
+ PGDIR=/etc/postgresql/${PG_VERSION}/main
log "copying configs..."
sudo cp $PGDIR/postgresql.conf $PGDIR/postgresql.conf.default
return memo + script;
}, "");
- // Without this, when we delegate to exec psql the err var will not be set even
- // on the case of error.
- allSql = "\\set ON_ERROR_STOP TRUE;\n" + allSql;
+ // Without this, psql runs all input and returns success even if errors occurred
+ allSql = "\\set ON_ERROR_STOP TRUE\n" + allSql;
winston.info("Applying build to database " + spec.database);
credsClone.database = spec.database;
-<package id = "distribution-install-460"
- version = "4.7.0Beta"
+<package id = "distribution-install-470"
+ version = "4.7.0"
developer = "xTuple"
descrip = "load PostBooks resources"
updater = "2.2.4" >
<prerequisite type = "query"
name = "Checking for too-new xTuple ERP database version" >
- <query>SELECT NOT fetchMetricText('ServerVersion') >= '4.7.0';</query>
- <message>This package may not be applied to a database newer than 4.7.0Beta.
+ <query>SELECT NOT fetchMetricText('ServerVersion') >= '4.8.0';</query>
+ <message>This package may not be applied to a database newer than 4.7.0.
</message>
</prerequisite>
-<package id = "distribution-upgrade-460"
- version = "4.7.0Beta"
+<package id = "distribution-upgrade-470"
+ version = "4.7.0"
developer = "xTuple"
descrip = "load PostBooks resources"
updater = "2.2.4" >
<prerequisite type = "query"
name = "Checking for too-new xTuple ERP database version" >
- <query>SELECT NOT fetchMetricText('ServerVersion') >= '4.7.0';</query>
- <message>This package may not be applied to a database newer than 4.7.0Beta.
+ <query>SELECT NOT fetchMetricText('ServerVersion') >= '4.8.0';</query>
+ <message>This package may not be applied to a database newer than 4.7.0.
</message>
</prerequisite>
-<package id = "postbooks-upgrade-460"
- version = "4.7.0Beta"
+<package id = "postbooks-upgrade-470"
+ version = "4.7.0"
developer = "xTuple"
descrip = "load PostBooks resources"
updater = "2.2.4" >
<prerequisite type = "query"
name = "Checking for too-new xTuple ERP database version" >
- <query>SELECT NOT fetchMetricText('ServerVersion') >= '4.7.0';</query>
- <message>This package may not be applied to a database newer than 4.7.0Beta.
+ <query>SELECT NOT fetchMetricText('ServerVersion') >= '4.8.0';</query>
+ <message>This package may not be applied to a database newer than 4.7.0.
</message>
</prerequisite>
-<package id = "manufacturing-install-460"
- version = "4.7.0Beta"
+<package id = "manufacturing-install-470"
+ version = "4.7.0"
developer = "xTuple"
descrip = "load PostBooks resources"
updater = "2.2.4" >
<prerequisite type = "query"
name = "Checking for too-new xTuple ERP database version" >
- <query>SELECT NOT fetchMetricText('ServerVersion') >= '4.7.0';</query>
- <message>This package may not be applied to a database newer than 4.7.0Beta.
+ <query>SELECT NOT fetchMetricText('ServerVersion') >= '4.8.0';</query>
+ <message>This package may not be applied to a database newer than 4.7.0.
</message>
</prerequisite>
-<package id = "manufacturing-upgrade-460"
- version = "4.7.0Beta"
+<package id = "manufacturing-upgrade-470"
+ version = "4.7.0"
developer = "xTuple"
descrip = "load PostBooks resources"
updater = "2.2.4" >
<prerequisite type = "query"
name = "Checking for too-new xTuple ERP database version" >
-<query>SELECT NOT fetchMetricText('ServerVersion') >= '4.7.0';</query>
- <message>This package may not be applied to a database newer than 4.7.0Beta.
+<query>SELECT NOT fetchMetricText('ServerVersion') >= '4.8.0';</query>
+ <message>This package may not be applied to a database newer than 4.7.0.
</message>
</prerequisite>