1 select xt.install_js('XM','Model','xtuple', $$
2 /* Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
3 See www.xm.ple.com/CPAL for the full text of the software license. */
5 if (!XM.Model) { XM.Model = {}; }
7 XM.Model.isDispatchable = true;
9 if (!XM.PrivateModel) { XM.PrivateModel = {}; }
10 XM.PrivateModel.isDispatchable = false;
13 Pass in a record type and get the next id for that type
15 @param {String} record type
18 XM.Model.fetchId = function(recordType) {
19 var nameSpace = recordType.beforeDot(),
20 type = recordType.afterDot(),
21 map = XT.Orm.fetch(nameSpace, type),
22 seq = map.idSequenceName,
23 sql = 'select nextval($1) as result';
25 return seq ? plv8.execute(sql, [seq])[0].result : false;
29 Pass in a record type and get the next id for that type
31 @param {String} record type
34 XM.Model.fetchNumber = function(recordType) {
35 var nameSpace = recordType.beforeDot(),
36 type = recordType.afterDot(),
37 map = XT.Orm.fetch(nameSpace, type),
38 seq = map.orderSequence,
39 sql = 'select fetchNextNumber($1) as result';
41 /** if the order sequence name in orderseq is not found in the ORM
44 return plv8.execute(sql, [seq])[0].result;
46 plv8.elog(ERROR, "orderSequence is not defined in the ORM");
51 Obtain a pessemistic record lock. Defaults to timeout of 30 seconds.
53 @param {String} Namespace
55 @param {String|Number} Id
57 @param {Object} Options: timeout
59 XM.Model.obtainLock = function (nameSpace, type, id, etag, options) {
60 var orm = XT.Orm.fetch(nameSpace, type),
61 data = Object.create(XT.Data),
62 lockTable = orm.lockTable || orm.table,
63 pkey = XT.Orm.primaryKey(orm),
64 nkey = XT.Orm.naturalKey(orm),
68 /* If the model uses a natural key, get the primary key value. */
69 rec = data.retrieveRecord({
76 if (!rec) { return false; }
78 pid = nkey ? data.getId(orm, id) : id;
80 // TODO - Send not found message back.
84 if (!rec || !rec.data) { throw "Record for requested lock not found." }
85 if (rec.etag !== etag) { return false; }
87 return data.tryLock(lockTable, pid);
91 Renew a record lock. Defaults to timeout of 30 seconds.
94 @param {Object} Options: timeout
96 XM.Model.renewLock = function (key, options) {
97 return XT.Data.renewLock(key, options);
101 Release a record lock.
105 XM.Model.releaseLock = function (key) {
106 return XT.Data.releaseLock(key);
110 Release a number back into the sequence pool for a given type.
112 @param {String} record type
113 @param {Number} number
116 XM.Model.releaseNumber = function(recordType, number) {
117 var nameSpace = recordType.beforeDot(),
118 type = recordType.afterDot(),
119 map = XT.Orm.fetch(nameSpace, type),
120 seq = map.orderSequence,
121 sql = 'select releaseNumber($1, $2) as result';
123 return seq ? plv8.execute(sql, [seq, number - 0])[0].result > 0 : false;
127 Return a matching record id for a passed user key and value. If none found returns zero.
129 @param {String} record type
130 @param {String} user key
131 @param {Number} value
134 XM.Model.findExisting = function(recordType, key, value, id) {
135 var nameSpace = recordType.beforeDot(),
136 type = recordType.afterDot(),
137 map = XT.Orm.fetch(nameSpace, type),
138 table = recordType.decamelize(),
139 okey = XT.Orm.naturalKey(map) || XT.Orm.primaryKey(map),
140 sql = 'select "{key}" as id from {table} where "{userKey}"::text=$1::text'
141 .replace(/{key}/, okey)
142 .replace(/{table}/, table)
143 .replace(/{userKey}/, key),
146 sql += " and " + okey + " != $2";
147 if (DEBUG) { XT.debug('XM.Model.findExisting sql = ', sql); }
148 result = plv8.execute(sql, [value, id])[0];
150 if (DEBUG) { XT.debug('XM.Model.findExisting sql = ', sql); }
151 result = plv8.execute(sql, [value])[0];
154 return result ? result.id : 0;
158 Returns a complex query's results.
165 "functionName":"query",
167 "Address", {"query": {"parameters": [{"attribute": "city","operator": "=","value": "Norfolk"}]}}
172 @param {String} recordType to query
173 @param {Object} options: query
176 XM.Model.query = function (recordType, options) {
177 options = options || {};
180 if (recordType && options && options.query) {
181 query.username = XT.username;
182 query.nameSpace = 'XM';
183 query.type = recordType;
184 query.query = options.query;
187 result = XT.Rest.get(query);
191 XM.Model.query.scope = "Model";
192 XM.Model.query.description = "Perform an complex query on a resource. This allows you to use a POST body for the query vs. a long URL.";
193 XM.Model.query.request = {
196 XM.Model.query.parameterOrder = ["recordType", "options"];
197 // For JSON-Schema deff, see:
198 // https://github.com/fge/json-schema-validator/issues/46#issuecomment-14681103
199 XM.Model.query.schema = {
203 title: "Service request attributes",
204 description: "An array of attributes needed to perform a complex query.",
209 description: "The resource to query.",
216 "$ref": "QueryOptions"
229 description: "The query to perform.",
231 "$ref": "QueryOptionsQuery"
239 description: "The query parameters.",
245 "$ref": "QueryOptionsParameters"
252 description: "The query order by.",
258 "$ref": "QueryOptionsOrderBy"
264 description: "The query for paged results.",
268 title: "Max Results",
269 description: "The query limit for total results.",
274 description: "The query offset page token.",
279 description: "Set to true to return only the count of results for this query.",
284 QueryOptionsParameters: {
288 description: "The column name used to construct the query's WHERE clasues.",
294 description: "The operator used to construct the query's WHERE clasues.",
299 description: "The value used to construct the query's WHERE clasues.",
304 QueryOptionsOrderBy: {
308 description: "The column name used to construct the query's ORDER BY.",
314 description: "Set to true so the query's ORDER BY will be DESC.",
322 Format acomplex query's using the REST query structure into an xTuple's query.
323 This is a helper function that reformats the query structure from a
324 rest_query to our XT.Rest structure. This function should be used by reformat
325 any REST API client queriers.
328 XM.Model.restQueryFormat("XM.Address", {"query": [{"city":{"EQUALS":"Norfolk"}}], "orderby": [{"ASC": "line1"}, {"DESC": "line2"}]})
330 @param {Object} options: query
331 @returns {Object} The formated query
333 XM.Model.restQueryFormat = function (recordType, options) {
334 options = options || {};
339 mapOperator = function (op) {
351 BEGINS_WITH: 'BEGINS_WITH'
355 return operators.value[op];
358 /* Convert from rest_query to XM.Model.query structure. */
361 query.parameters = [];
362 for (var i = 0; i < options.query.length; i++) {
363 for (var column in options.query[i]) {
364 for (var op in options.query[i][column]) {
366 param.attribute = column;
367 param.operator = mapOperator(op);
368 param.value = options.query[i][column][op];
369 query.parameters.push(param);
375 /* Convert free text query. */
376 if (recordType && options.q) {
377 /* Get schema and add string columns to search query. */
378 var data = Object.create(XT.Data),
379 nameSpace = recordType.beforeDot(),
380 type = recordType.afterDot(),
381 orm = data.fetchOrm(nameSpace, type),
382 schema = XT.Session.schema(nameSpace.decamelize(), type.decamelize()),
387 for (var c = 0; c < schema[type].columns.length; c++) {
388 if (schema[type].columns[c].category === 'S') {
389 param.attribute.push(schema[type].columns[c].name);
393 if (param.attribute.length) {
394 /* Add all string columns to attribute query. */
395 query.parameters = query.parameters || [];
397 param.operator = 'MATCHES';
399 /* Replace any spaces with regex '.*' so multi-word search works on similar strings. */
400 param.value = options.q.replace(' ', '.*');
401 query.parameters.push(param);
405 if (options.orderby || options.orderBy) {
406 options.orderBy = options.orderby || options.orderBy;
408 for (var o = 0; o < options.orderBy.length; o++) {
409 for (var column in options.orderBy[o]) {
411 order.attribute = column;
412 if (options.orderBy[o][column] === 'DESC') {
413 order.descending = true;
415 query.orderBy.push(order);
420 if (options.rowlimit || options.rowLimit) {
421 options.rowLimit = options.rowlimit || options.rowLimit;
422 query.rowLimit = options.rowLimit;
425 if (options.maxresults || options.maxResults) {
426 options.maxResults = options.maxresults || options.maxResults;
427 query.rowLimit = options.maxResults;
430 if (options.pagetoken || options.pageToken) {
431 options.pageToken = options.pagetoken || options.pageToken;
432 if (query.rowLimit) {
433 query.rowOffset = (options.pageToken || 0) * (query.rowLimit);
435 query.rowOffset = (options.pageToken || 0);
440 query.count = options.count;
448 Returns a complex query's results using the REST query structure. This is a
449 wrapper for XM.Model.query that reformats the query structure from a
450 rest_query to our XT.Rest structure. This dispatch function can be used by
451 a REST API client to query a resource when the query would be too long to
452 pass to the API as a GET URL query.
460 "functionName":"restQuery",
461 "parameters":["Address", {"query": [{"city":{"EQUALS":"Norfolk"}}], "orderby": [{"ASC": "line1"}, {"DESC": "line2"}]}]
465 @param {String} recordType to query
466 @param {Object} options: query
469 XM.Model.restQuery = function (recordType, options) {
470 options = options || {};
471 var formattedOptions = {};
473 /* Convert from rest_query to XM.Model.query structure. */
474 if (recordType && options) {
476 "query": XM.Model.restQueryFormat(recordType, options)
480 result = XM.Model.query(recordType, formattedOptions);
484 XM.Model.restQuery.description = "Perform an complex query on a resource using the REST query structure. This allows you to use a POST body for the query vs. a long URL.";
485 XM.Model.restQuery.request = {
488 XM.Model.restQuery.parameterOrder = ["recordType", "options"];
489 // For JSON-Schema deff, see:
490 // https://github.com/fge/json-schema-validator/issues/46#issuecomment-14681103
491 XM.Model.restQuery.schema = {
495 title: "Service request attributes",
496 description: "An array of attributes needed to perform a complex query.",
501 description: "The resource to query.",
508 "$ref": "RestQueryOptions"
521 description: "The query to perform.",
533 description: "The query order by.",
544 description: "The query for paged results.",
548 title: "Max Results",
549 description: "The query limit for total results.",
554 description: "The query offset page token.",
559 description: "Set to true to return only the count of results for this query.",
567 Used to determine whether a model is used or not.
569 @param {String} Record Type
570 @param {String|Number} Id
571 @param {Array} Array of schema qualified foreign key table names that are exceptions
574 XM.PrivateModel.used = function(recordType, id, exceptions) {
575 exceptions = exceptions || [];
576 var nameSpace = recordType.beforeDot(),
577 type = recordType.afterDot(),
578 map = XT.Orm.fetch(nameSpace, type),
579 data = Object.create(XT.Data),
580 nkey = XT.Orm.naturalKey(map),
581 tableName = map.lockTable || map.table,
582 tableSuffix = tableName.indexOf('.') ? tableName.afterDot() : tableName,
596 id = data.getId(map, id);
598 /* Throw an error here because returning false is a valid use case. */
599 plv8.elog(ERROR, "Can not find primary key.");
603 /* Determine where this record is used by analyzing foreign key linkages */
604 sql = "select pg_namespace.nspname AS schemaname, " +
605 "con.relname AS tablename, " +
607 "conrelid AS class_id " +
608 "from pg_constraint, pg_class f, pg_class con, pg_namespace " +
609 "where confrelid=f.oid " +
610 "and conrelid=con.oid " +
611 "and f.relname = $1 " +
612 "and con.relnamespace=pg_namespace.oid; "
613 fkeys = plv8.execute(sql, [tableSuffix]);
615 /* isNested toMany relationships are irrelevant and should not be counted */
616 /* First boil down our list of isNested toManys from the orm */
617 var toMany = map.properties.filter(function (prop) {
618 return prop.toMany && prop.toMany.isNested;
619 }).map(function (prop) {
620 var toManyType = prop.toMany.type,
621 toManyMap = XT.Orm.fetch(nameSpace, toManyType)
622 toManyTable = toManyMap.lockTable || toManyMap.table,
623 toManyPrefix = toManyTable.indexOf('.') < 0 ? "public" : toManyTable.beforeDot(),
624 toManySuffix = toManyTable.afterDot();
626 return {nameSpace: toManyPrefix, tableName: toManySuffix};
629 if (DEBUG) { XT.debug('XM.Model.used toMany relations are:', JSON.stringify(toMany)); }
631 for (fkIndex = fkeys.length - 1; fkIndex >= 0; fkIndex-=1) {
632 /* loop backwards because we might be deleting elements of the array */
633 fkey = fkeys[fkIndex];
634 toMany.map(function (prop) {
635 if (fkey.schemaname === prop.nameSpace && fkey.tablename === prop.tableName) {
636 fkeys.splice(fkIndex, 1);
641 /* Remove exceptions */
642 fkeys = fkeys.filter(function (key) {
643 var name = key.schemaname + '.' + key.tablename;
644 return !exceptions.contains(name);
647 if (DEBUG) { XT.debug('XM.Model.used keys length:', fkeys.length) }
648 if (DEBUG) { XT.debug('XM.Model.used keys:', JSON.stringify(fkeys)) }
650 for (i = 0; i < fkeys.length; i++) {
653 sql = "select attname " +
654 "from pg_attribute, pg_class " +
655 "where ((attrelid=pg_class.oid) " +
656 " and (pg_class.oid = $1) " +
657 " and (attnum = $2)); ";
659 classId = fkeys[i].class_id;
660 seq = fkeys[i].seq[0];
661 tableName = fkeys[i].schemaname + '.' + fkeys[i].tablename;
662 if (DEBUG) { XT.debug('XM.Model.used vars:', [classId, seq, tableName]) }
663 attr = plv8.execute(sql, [classId, seq])[0].attname;
665 /* See if there are dependencies */
666 sql = 'select * from ' + tableName + ' where ' + attr + ' = $1 limit 1;'
667 uses = plv8.execute(sql, [id]);
668 if (uses.length) { return true; }
675 Return whether a model is referenced by another table.
677 XM.Model.used = function(recordType, id) {
678 return XM.PrivateModel.used(recordType, id);