1 select xt.install_js('XT','Data','xtuple', $$
8 * The XT.Data class includes all functions necessary to process data source requests against the database.
9 * It should be instantiated as an object against which its funtion calls are made. This class enforces privilege
10 * control and as such is not and should not be dispatchable.
20 CREATED_STATE: 'create',
22 UPDATED_STATE: 'update',
23 DELETED_STATE: 'delete',
26 * Build a SQL `where` clause based on privileges for name space and type,
27 * and conditions and parameters passed.
31 * @param {String} Name space
32 * @param {String} Type
33 * @param {Array} Parameters - optional
36 buildClauseOptimized: function (nameSpace, type, parameters, orderBy) {
37 parameters = parameters || [];
40 arrayIdentifiers = [],
47 groupByColumnParams = [],
51 orderByColumnList = [],
55 orderByIdentifiers = [],
56 orderByColumnIdentifiers = [],
58 orderByColumnParams = [],
60 orm = this.fetchOrm(nameSpace, type),
68 privileges = orm.privileges,
76 /* Handle privileges. */
77 if (orm.isNestedOnly) { plv8.elog(ERROR, 'Access Denied'); }
81 (!this.checkPrivilege(privileges.all.read) &&
82 !this.checkPrivilege(privileges.all.update)))
84 privileges.personal &&
85 (this.checkPrivilege(privileges.personal.read) ||
86 this.checkPrivilege(privileges.personal.update))
90 attribute: privileges.personal.properties,
92 isUsernamePrivFilter: true,
97 /* Support the short cut wherein the client asks for a filter on a toOne with a
98 string. Technically they should use "theAttr.theAttrNaturalKey", but if they
99 don't, massage the inputs as if they did */
100 parameters.map(function (parameter) {
101 var attributeIsString = typeof parameter.attribute === 'string';
102 attributes = attributeIsString ? [parameter.attribute] : parameter.attribute;
104 attributes.map(function (attribute) {
105 var prop = XT.Orm.getProperty(orm, attribute),
106 propName = prop.name,
111 if ((prop.toOne || prop.toMany) && attribute.indexOf('.') < 0) {
112 /* Someone is querying on a toOne without using a path */
113 /* TODO: even if there's a path x.y, it's possible that it's still not
114 correct because the correct path maybe is x.y.naturalKeyOfY */
115 if (prop.toOne && prop.toOne.type) {
116 childOrm = that.fetchOrm(nameSpace, prop.toOne.type);
117 } else if (prop.toMany && prop.toMany.type) {
118 childOrm = that.fetchOrm(nameSpace, prop.toMany.type);
120 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + prop.name);
122 naturalKey = XT.Orm.naturalKey(childOrm);
123 if (attributeIsString) {
124 /* add the natural key to the end of the requested attribute */
125 parameter.attribute = attribute + "." + naturalKey;
127 /* swap out the attribute in the array for the one with the prepended natural key */
128 index = parameter.attribute.indexOf(attribute);
129 parameter.attribute.splice(index, 1);
130 parameter.attribute.splice(index, 0, attribute + "." + naturalKey);
136 /* Handle parameters. */
137 if (parameters.length) {
138 for (var i = 0; i < parameters.length; i++) {
140 param = parameters[i];
141 op = param.operator || '=';
161 for (var c = 0; c < param.value.length; c++) {
162 ret.parameters.push(param.value[c]);
163 param.value[c] = '$' + count;
169 for (var c = 0; c < param.value.length; c++) {
170 ret.parameters.push(param.value[c]);
171 param.value[c] = '$' + count;
176 plv8.elog(ERROR, 'Invalid operator: ' + op);
179 /* Handle characteristics. This is very specific to xTuple,
180 and highly dependant on certain table structures and naming conventions,
181 but otherwise way too much work to refactor in an abstract manner right now. */
182 if (param.isCharacteristic) {
185 param.value = ' ARRAY[' + param.value.join(',') + ']';
188 /* Booleans are stored as strings. */
189 if (param.value === true) {
191 } else if (param.value === false) {
195 /* Yeah, it depends on a property called 'characteristics'... */
196 prop = XT.Orm.getProperty(orm, 'characteristics');
198 /* Build the characteristics query clause. */
199 identifiers.push(XT.Orm.primaryKey(orm, true));
200 identifiers.push(prop.toMany.inverse);
201 identifiers.push(orm.nameSpace.toLowerCase());
202 identifiers.push(prop.toMany.type.decamelize());
203 identifiers.push(param.attribute);
204 identifiers.push(param.value);
206 charSql = '%' + (identifiers.length - 5) + '$I in (' +
207 ' select %' + (identifiers.length - 4) + '$I '+
208 ' from %' + (identifiers.length - 3) + '$I.%' + (identifiers.length - 2) + '$I ' +
209 ' join char on (char_name = characteristic)' +
211 /* Note: Not using $i for these. L = literal here. These is not identifiers. */
212 ' and char_name = %' + (identifiers.length - 1) + '$L ' +
213 ' and value ' + op + ' %' + (identifiers.length) + '$L ' +
216 clauses.push(charSql);
218 /* Array comparisons handle another way. e.g. %1$I !<@ ARRAY[$1,$2] */
219 } else if (op === '<@' || op === '!<@') {
220 /* Handle paths if applicable. */
221 if (param.attribute.indexOf('.') > -1) {
222 parts = param.attribute.split('.');
223 childOrm = this.fetchOrm(nameSpace, type);
225 pcount = params.length - 1;
227 for (var n = 0; n < parts.length; n++) {
228 /* Validate attribute. */
229 prop = XT.Orm.getProperty(childOrm, parts[n]);
231 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
235 if (n === parts.length - 1) {
236 identifiers.push("jt" + (joins.length - 1));
237 identifiers.push(prop.attr.column);
238 pgType = this.getPgTypeFromOrmType(
239 this.getNamespaceFromNamespacedTable(childOrm.table),
240 this.getTableFromNamespacedTable(childOrm.table),
243 pgType = pgType ? "::" + pgType + "[]" : '';
244 params[pcount] += "%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I";
245 params[pcount] += ' ' + op + ' ARRAY[' + param.value.join(',') + ']' + pgType;
247 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
248 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
249 joinIdentifiers.push(
250 this.getNamespaceFromNamespacedTable(childOrm.table),
251 this.getTableFromNamespacedTable(childOrm.table),
252 sourceTableAlias, prop.toOne.column,
253 XT.Orm.primaryKey(childOrm, true));
254 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
255 + "$I jt" + joins.length + " on %"
256 + (joinIdentifiers.length - 2) + "$I.%"
257 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
261 prop = XT.Orm.getProperty(orm, param.attribute);
262 pertinentExtension = XT.Orm.getProperty(orm, param.attribute, true);
263 if(pertinentExtension.isChild || pertinentExtension.isExtension) {
264 /* We'll need to join this orm extension */
265 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
266 joinIdentifiers.push(
267 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
268 this.getTableFromNamespacedTable(pertinentExtension.table),
269 fromKeyProp.attr.column,
270 pertinentExtension.relations[0].column);
271 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
272 + "$I jt" + joins.length + " on t1.%"
273 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
276 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute);
279 identifiers.push(pertinentExtension.isChild || pertinentExtension.isExtension ?
280 "jt" + (joins.length - 1) :
282 identifiers.push(prop.attr.column);
283 pgType = this.getPgTypeFromOrmType(
284 this.getNamespaceFromNamespacedTable(orm.table),
285 this.getTableFromNamespacedTable(orm.table),
288 pgType = pgType ? "::" + pgType + "[]" : '';
289 params.push("%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I " + op + ' ARRAY[' + param.value.join(',') + ']' + pgType);
290 pcount = params.length - 1;
292 clauses.push(params[pcount]);
294 /* Everything else handle another. */
296 if (XT.typeOf(param.attribute) !== 'array') {
297 param.attribute = [param.attribute];
300 for (var c = 0; c < param.attribute.length; c++) {
301 /* Handle paths if applicable. */
302 if (param.attribute[c].indexOf('.') > -1) {
303 parts = param.attribute[c].split('.');
304 childOrm = this.fetchOrm(nameSpace, type);
306 pcount = params.length - 1;
309 /* Check if last part is an Array. */
310 for (var m = 0; m < parts.length; m++) {
311 /* Validate attribute. */
312 prop = XT.Orm.getProperty(childOrm, parts[m]);
314 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[m]);
317 if (m < parts.length - 1) {
318 if (prop.toOne && prop.toOne.type) {
319 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
320 } else if (prop.toMany && prop.toMany.type) {
321 childOrm = this.fetchOrm(nameSpace, prop.toMany.type);
323 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + prop.name);
325 } else if (prop.attr && prop.attr.type === 'Array') {
326 /* The last property in the path is an array. */
328 params[pcount] = '$' + count;
332 /* Reset the childOrm to parent. */
333 childOrm = this.fetchOrm(nameSpace, type);
335 for (var n = 0; n < parts.length; n++) {
336 /* Validate attribute. */
337 prop = XT.Orm.getProperty(childOrm, parts[n]);
339 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
342 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
343 if (param.isUsernamePrivFilter && isArray) {
344 identifiers.push(prop.attr.column);
345 arrayIdentifiers.push(identifiers.length);
347 if (n < parts.length - 1) {
348 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
351 /* Build path, e.g. table_name.column_name */
352 if (n === parts.length - 1) {
353 identifiers.push("jt" + (joins.length - 1));
354 identifiers.push(prop.attr.column);
355 params[pcount] += "%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I";
357 params[pcount] = "lower(" + params[pcount] + ")";
360 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
361 if (prop.toOne && prop.toOne.type) {
362 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
363 joinIdentifiers.push(
364 this.getNamespaceFromNamespacedTable(childOrm.table),
365 this.getTableFromNamespacedTable(childOrm.table),
366 sourceTableAlias, prop.toOne.column,
367 XT.Orm.primaryKey(childOrm, true)
369 } else if (prop.toMany && prop.toMany.type) {
370 childOrm = this.fetchOrm(nameSpace, prop.toMany.type);
371 joinIdentifiers.push(
372 this.getNamespaceFromNamespacedTable(childOrm.table),
373 this.getTableFromNamespacedTable(childOrm.table),
374 sourceTableAlias, prop.toMany.column,
375 XT.Orm.primaryKey(childOrm, true)
378 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
379 + "$I jt" + joins.length + " on %"
380 + (joinIdentifiers.length - 2) + "$I.%"
381 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
386 /* Validate attribute. */
387 prop = XT.Orm.getProperty(orm, param.attribute[c]);
388 pertinentExtension = XT.Orm.getProperty(orm, param.attribute[c], true);
389 if(pertinentExtension.isChild || pertinentExtension.isExtension) {
390 /* We'll need to join this orm extension */
391 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
392 joinIdentifiers.push(
393 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
394 this.getTableFromNamespacedTable(pertinentExtension.table),
395 fromKeyProp.attr.column,
396 pertinentExtension.relations[0].column);
397 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
398 + "$I jt" + joins.length + " on t1.%"
399 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
402 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute[c]);
405 identifiers.push(pertinentExtension.isChild || pertinentExtension.isExtension ?
406 "jt" + (joins.length - 1) :
408 identifiers.push(prop.attr.column);
410 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
411 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested) ||
412 (prop.attr && prop.attr.type === 'Array'))) {
414 params.push('$' + count);
415 pcount = params.length - 1;
416 arrayIdentifiers.push(identifiers.length);
418 params.push("%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I");
419 pcount = params.length - 1;
423 /* Add persional privs array search. */
424 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested)
425 || (prop.attr && prop.attr.type === 'Array') || isArray)) {
427 /* XXX: this bit of code has not been touched by the optimization refactor */
428 /* e.g. 'admin' = ANY (usernames_array) */
430 params[pcount] += ' ' + op + ' ANY (';
432 /* Build path. e.g. ((%1$I).%2$I).%3$I */
433 for (var f =0; f < arrayIdentifiers.length; f++) {
434 arrayParams += '%' + arrayIdentifiers[f] + '$I';
435 if (f < arrayIdentifiers.length - 1) {
436 arrayParams = "(" + arrayParams + ").";
439 params[pcount] += arrayParams + ')';
441 /* Add optional is null clause. */
442 } else if (parameters[i].includeNull) {
443 /* e.g. %1$I = $1 or %1$I is null */
444 params[pcount] = params[pcount] + " " + op + ' $' + count + ' or ' + params[pcount] + ' is null';
447 params[pcount] += " " + op + ' $' + count;
450 orClause.push(params[pcount]);
453 /* If more than one clause we'll get: (%1$I = $1 or %1$I = $2 or %1$I = $3) */
454 clauses.push('(' + orClause.join(' or ') + ')');
456 ret.parameters.push(param.value);
461 ret.conditions = (clauses.length ? '(' + XT.format(clauses.join(' and '), identifiers) + ')' : ret.conditions) || true;
463 /* Massage orderBy with quoted identifiers. */
464 /* We need to support the xm case for sql2 and the xt/public (column) optimized case for sql1 */
465 /* In practice we build the two lists independently of one another */
467 for (var i = 0; i < orderBy.length; i++) {
468 /* Handle path case. */
469 if (orderBy[i].attribute.indexOf('.') > -1) {
470 parts = orderBy[i].attribute.split('.');
472 orderByParams.push("");
473 orderByColumnParams.push("");
474 groupByColumnParams.push("");
475 pcount = orderByParams.length - 1;
477 for (var n = 0; n < parts.length; n++) {
478 prop = XT.Orm.getProperty(orm, parts[n]);
480 plv8.elog(ERROR, 'Attribute not found in map: ' + parts[n]);
482 orderByIdentifiers.push(parts[n]);
483 orderByParams[pcount] += "%" + orderByIdentifiers.length + "$I";
485 if (n === parts.length - 1) {
486 orderByColumnIdentifiers.push("jt" + (joins.length - 1));
487 orderByColumnIdentifiers.push(prop.attr.column);
488 orderByColumnParams[pcount] += "%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I"
489 groupByColumnParams[pcount] += "%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I"
491 orderByParams[pcount] = "(" + orderByParams[pcount] + ").";
492 orm = this.fetchOrm(nameSpace, prop.toOne.type);
493 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
494 joinIdentifiers.push(
495 this.getNamespaceFromNamespacedTable(orm.table),
496 this.getTableFromNamespacedTable(orm.table),
497 sourceTableAlias, prop.toOne.column,
498 XT.Orm.primaryKey(orm, true));
499 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
500 + "$I jt" + joins.length + " on %"
501 + (joinIdentifiers.length - 2) + "$I.%"
502 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
508 prop = XT.Orm.getProperty(orm, orderBy[i].attribute);
510 plv8.elog(ERROR, 'Attribute not found in map: ' + orderBy[i].attribute);
512 orderByIdentifiers.push(orderBy[i].attribute);
513 orderByColumnIdentifiers.push("t1");
515 We might need to look at toOne if the client is asking for a toOne without specifying
516 the path. Unfortunately, if they do specify the path, then sql2 will fail. So this does
517 work, although we're really sorting by the primary key of the toOne, whereas the
518 user probably wants us to sort by the natural key TODO
520 orderByColumnIdentifiers.push(prop.attr ? prop.attr.column : prop.toOne.column);
521 orderByParams.push("%" + orderByIdentifiers.length + "$I");
522 orderByColumnParams.push("%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I");
523 groupByColumnParams.push("%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I");
524 pcount = orderByParams.length - 1;
527 if (orderBy[i].isEmpty) {
528 orderByParams[pcount] = "length(" + orderByParams[pcount] + ")=0";
529 orderByColumnParams[pcount] = "length(" + orderByColumnParams[pcount] + ")=0";
531 if (orderBy[i].descending) {
532 orderByParams[pcount] += " desc";
533 orderByColumnParams[pcount] += " desc";
536 orderByList.push(orderByParams[pcount])
537 orderByColumnList.push(orderByColumnParams[pcount])
541 ret.orderBy = orderByList.length ? XT.format('order by ' + orderByList.join(','), orderByIdentifiers) : '';
542 ret.orderByColumns = orderByColumnList.length ? XT.format('order by ' + orderByColumnList.join(','), orderByColumnIdentifiers) : '';
543 ret.groupByColumns = groupByColumnParams.length ? XT.format(', ' + groupByColumnParams.join(','), orderByColumnIdentifiers) : '';
544 ret.joins = joins.length ? XT.format(joins.join(' '), joinIdentifiers) : '';
550 * Queries whether the current user has been granted the privilege passed.
552 * @param {String} privilege
555 checkPrivilege: function (privilege) {
562 if (typeof privilege === 'string') {
563 if (!this._granted) { this._granted = {}; }
564 if (!this._granted[XT.username]) { this._granted[XT.username] = {}; }
565 if (this._granted[XT.username][privilege] !== undefined) { return this._granted[XT.username][privilege]; }
567 /* The privilege name is allowed to be a set of space-delimited privileges */
568 /* If a user has any of the applicable privileges then they get access */
569 privArray = privilege.split(" ");
570 sql = 'select coalesce(usrpriv_priv_id, grppriv_priv_id, -1) > 0 as granted ' +
572 'left join usrpriv on (priv_id=usrpriv_priv_id) and (usrpriv_username=$1) ' +
574 ' select distinct grppriv_priv_id ' +
576 ' join usrgrp on (grppriv_grp_id=usrgrp_grp_id) and (usrgrp_username=$1) ' +
577 ' ) grppriv on (grppriv_priv_id=priv_id) ' +
578 'where priv_name = $2';
580 for (var i = 1; i < privArray.length; i++) {
581 sql = sql + ' or priv_name = $' + (i + 2);
583 sql = sql + "order by granted desc limit 1;";
585 /* Cleverness: the query parameters are just the priv array with the username tacked on front. */
586 privArray.unshift(XT.username);
589 XT.debug('checkPrivilege sql =', sql);
590 XT.debug('checkPrivilege values =', privArray);
592 res = plv8.execute(sql, privArray);
593 ret = res.length ? res[0].granted : false;
596 this._granted[XT.username][privilege] = ret;
600 XT.debug('Privilege check for "' + XT.username + '" on "' + privilege + '" returns ' + ret);
607 * Validate whether user has read access to data. If a record is passed, check personal privileges of
610 * @param {String} name space
611 * @param {String} type name
612 * @param {Object} record - optional
613 * @param {Boolean} is top level, default is true
616 checkPrivileges: function (nameSpace, type, record, isTopLevel) {
617 isTopLevel = isTopLevel !== false ? true : false;
618 var action = record && record.dataState === this.CREATED_STATE ? 'create' :
619 record && record.dataState === this.DELETED_STATE ? 'delete' :
620 record && record.dataState === this.UPDATED_STATE ? 'update' : 'read',
621 committing = record ? record.dataState !== this.READ_STATE : false,
623 isGrantedPersonal = false,
624 map = this.fetchOrm(nameSpace, type),
625 privileges = map.privileges,
629 /* If there is no ORM, this isn't a table data type so no check required. */
632 XT.debug('orm type is ->', map.type);
633 XT.debug('orm is ->', map);
636 if (!map) { return true; }
638 /* Can not access 'nested only' records directly. */
640 XT.debug('is top level ->', isTopLevel);
641 XT.debug('is nested ->', map.isNestedOnly);
643 if (isTopLevel && map.isNestedOnly) { return false; }
645 /* Check privileges - first do we have access to anything? */
647 if (DEBUG) { XT.debug('privileges found', privileges); }
649 if (DEBUG) { XT.debug('is committing'); }
651 /* Check if user has 'all' read privileges. */
652 isGrantedAll = privileges.all ? this.checkPrivilege(privileges.all[action]) : false;
654 /* Otherwise check for 'personal' read privileges. */
656 isGrantedPersonal = privileges.personal ?
657 this.checkPrivilege(privileges.personal[action]) : false;
660 if (DEBUG) { XT.debug('is NOT committing'); }
662 /* Check if user has 'all' read privileges. */
663 isGrantedAll = privileges.all ?
664 this.checkPrivilege(privileges.all.read) ||
665 this.checkPrivilege(privileges.all.update) : false;
667 /* Otherwise check for 'personal' read privileges. */
669 isGrantedPersonal = privileges.personal ?
670 this.checkPrivilege(privileges.personal.read) ||
671 this.checkPrivilege(privileges.personal.update) : false;
676 /* If we're checknig an actual record and only have personal privileges, */
677 /* see if the record allows access. */
678 if (record && !isGrantedAll && isGrantedPersonal && action !== "create") {
679 if (DEBUG) { XT.debug('checking record level personal privileges'); }
682 /* Shared checker function that checks 'personal' properties for access rights. */
683 checkPersonal = function (record) {
686 props = privileges.personal.properties,
687 get = function (obj, target) {
690 parts = target.split("."),
693 for (var idx = 0; idx < parts.length; idx++) {
695 ret = ret ? ret[part] : obj[part];
696 if (ret === null || ret === undefined) {
704 while (!isGranted && i < props.length) {
706 personalUser = get(record, prop);
708 if (personalUser instanceof Array) {
709 for (var userIdx = 0; userIdx < personalUser.length; userIdx++) {
710 if (personalUser[userIdx].toLowerCase() === XT.username) {
714 } else if (personalUser) {
715 isGranted = personalUser.toLowerCase() === XT.username;
724 /* If committing we need to ensure the record in its previous state is editable by this user. */
725 if (committing && (action === 'update' || action === 'delete')) {
726 pkey = XT.Orm.naturalKey(map) || XT.Orm.primaryKey(map);
727 old = this.retrieveRecord({
728 nameSpace: nameSpace,
734 isGrantedPersonal = checkPersonal(old.data);
736 /* Otherwise check personal privileges on the record passed. */
737 } else if (action === 'read') {
738 isGrantedPersonal = checkPersonal(record);
743 XT.debug('is granted all ->', isGrantedAll);
744 XT.debug('is granted personal ->', isGrantedPersonal);
747 return isGrantedAll || isGrantedPersonal;
751 * Commit array columns with their own statements
753 * @param {Object} Orm
754 * @param {Object} Record
756 commitArrays: function (orm, record, encryptionKey) {
757 var pkey = XT.Orm.primaryKey(orm),
766 resolveKey = function (col) {
769 /* First search properties */
770 var ary = orm.properties.filter(function (prop) {
771 return prop.attr && prop.attr.column === col;
778 /* If not found must be extension, search relations */
779 if (orm.extensions.length) {
780 orm.extensions.forEach(function (ext) {
782 ary = ext.relations.filter(function (prop) {
783 return prop.column === col;
787 attr = ary[0].inverse;
793 if (attr) { return attr };
795 /* If still not found, we have a structural problem */
796 throw new Error("Can not resolve primary id on toMany relation");
799 for (prop in record) {
800 ormp = XT.Orm.getProperty(orm, prop);
802 /* If the property is an array of objects they must be records so commit them. */
803 if (ormp.toMany && ormp.toMany.isNested) {
804 fkey = ormp.toMany.inverse;
805 values = record[prop];
807 for (var i = 0; i < values.length; i++) {
810 /* Populate the parent key into the foreign key field if it's absent. */
812 columnToKey = ormp.toMany.column;
813 propToKey = columnToKey ? resolveKey(columnToKey) : pkey;
814 if (!record[propToKey]) {
815 /* If there's no data, we have a structural problem */
816 throw new Error("Can not resolve foreign key on toMany relation " + ormp.name);
818 val[fkey] = record[propToKey];
822 nameSpace: orm.nameSpace,
823 type: ormp.toMany.type,
825 encryptionKey: encryptionKey
833 * Commit metrics that have changed to the database.
835 * @param {Object} metrics
838 commitMetrics: function (metrics) {
840 sql = 'select setMetric($1,$2)',
843 for (key in metrics) {
844 value = metrics[key];
845 if (typeof value === 'boolean') {
846 value = value ? 't' : 'f';
847 } else if (typeof value === 'number') {
848 value = value.toString();
852 XT.debug('commitMetrics sql =', sql);
853 XT.debug('commitMetrics values =', [key, value]);
855 plv8.execute(sql, [key, value]);
862 * Commit a record to the database. The record must conform to the object hiearchy as defined by the
863 * record's `ORM` definition. Each object in the tree must include state information on a reserved property
864 * called `dataState`. Valid values are `create`, `update` and `delete`. Objects with other dataState values including
865 * `undefined` will be ignored. State values can be added using `XT.jsonpatch.updateState(obj, state)`.
867 * @seealso XT.jsonpatch.updateState
868 * @param {Object} Options
869 * @param {String} [options.nameSpace] Namespace. Required.
870 * @param {String} [options.type] Type. Required.
871 * @param {Object} [options.data] The data payload to be processed. Required
872 * @param {Number} [options.etag] Record version for optimistic locking.
873 * @param {Object} [options.lock] Lock information for pessemistic locking.
874 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
875 * @param {String} [options.encryptionKey] Encryption key.
877 commitRecord: function (options) {
878 var data = options.data,
879 dataState = data ? data.dataState : false,
880 hasAccess = options.superUser ||
881 this.checkPrivileges(options.nameSpace, options.type, data, false);
883 if (!hasAccess) { throw new Error("Access Denied."); }
886 case (this.CREATED_STATE):
887 this.createRecord(options);
889 case (this.UPDATED_STATE):
890 this.updateRecord(options);
892 case (this.DELETED_STATE):
893 this.deleteRecord(options);
898 * Commit insert to the database
900 * @param {Object} Options
901 * @param {String} [options.nameSpace] Namespace. Required.
902 * @param {String} [options.type] Type. Required.
903 * @param {Object} [options.data] The data payload to be processed. Required.
904 * @param {String} [options.encryptionKey] Encryption key.
906 createRecord: function (options) {
907 var data = options.data,
908 encryptionKey = options.encryptionKey,
910 orm = this.fetchOrm(options.nameSpace, options.type),
911 sql = this.prepareInsert(orm, data, null, encryptionKey),
912 pkey = XT.Orm.primaryKey(orm),
915 /* Handle extensions on the same table. */
916 for (var i = 0; i < orm.extensions.length; i++) {
917 if (orm.extensions[i].table === orm.table) {
918 sql = this.prepareInsert(orm.extensions[i], data, sql, encryptionKey);
922 /* Commit the base record. */
924 XT.debug('createRecord sql =', sql.statement);
925 XT.debug('createRecord values =', sql.values);
929 rec = plv8.execute(sql.statement, sql.values);
930 /* Make sure the primary key is populated */
932 data[pkey] = rec[0].id;
934 /* Make sure the obj_uuid is populated, if applicable */
935 if (!data.obj_uuid && rec[0] && rec[0].obj_uuid) {
936 data.uuid = rec[0].obj_uuid;
940 /* Handle extensions on other tables. */
941 for (var i = 0; i < orm.extensions.length; i++) {
942 if (orm.extensions[i].table !== orm.table &&
943 !orm.extensions[i].isChild) {
944 sql = this.prepareInsert(orm.extensions[i], data, null, encryptionKey);
947 XT.debug('createRecord sql =', sql.statement);
948 XT.debug('createRecord values =', sql.values);
952 plv8.execute(sql.statement, sql.values);
957 /* Okay, now lets handle arrays. */
958 this.commitArrays(orm, data, encryptionKey);
962 * Use an orm object and a record and build an insert statement. It
963 * returns an object with a table name string, columns array, expressions
964 * array and insert statement string that can be executed.
966 * The optional params object includes objects columns, expressions
967 * that can be cumulatively added to the result.
969 * @params {Object} Orm
970 * @params {Object} Record
971 * @params {Object} Params - optional
972 * @params {String} Encryption Key
975 prepareInsert: function (orm, record, params, encryptionKey) {
988 pkey = XT.Orm.primaryKey(orm),
991 sql = "select nextval($1) as id",
997 isValidSql = params && params.statement ? true : false,
1000 params = params || {
1007 params.table = orm.table;
1008 count = params.values.length + 1;
1010 /* If no primary key, then create one. */
1011 if (!record[pkey] && orm.idSequenceName) {
1013 XT.debug('prepareInsert sql =', sql);
1014 XT.debug('prepareInsert values =', [orm.idSequenceName]);
1016 record[pkey] = plv8.execute(sql, [orm.idSequenceName])[0].id;
1019 /* If extension handle key. */
1020 if (orm.relations) {
1021 for (var i = 0; i < orm.relations.length; i++) {
1022 column = orm.relations[i].column;
1023 if (!params.identifiers.contains(column)) {
1024 params.columns.push("%" + count + "$I");
1025 params.values.push(record[orm.relations[i].inverse]);
1026 params.expressions.push('$' + count);
1027 params.identifiers.push(orm.relations[i].column);
1033 /* Build up the content for insert of this record. */
1034 for (var i = 0; i < orm.properties.length; i++) {
1035 ormp = orm.properties[i];
1038 if (ormp.toMany && ormp.toMany.column === 'obj_uuid') {
1039 params.parentUuid = true;
1042 attr = ormp.attr ? ormp.attr : ormp.toOne ? ormp.toOne : ormp.toMany;
1044 iorm = ormp.toOne ? this.fetchOrm(orm.nameSpace, ormp.toOne.type) : false,
1045 nkey = iorm ? XT.Orm.naturalKey(iorm, true) : false;
1046 val = ormp.toOne && record[prop] instanceof Object ?
1047 record[prop][nkey || ormp.toOne.inverse || 'id'] : record[prop];
1050 * Ignore derived fields for insert/update
1052 if (attr.derived) continue;
1054 attributePrivileges = orm.privileges &&
1055 orm.privileges.attribute &&
1056 orm.privileges.attribute[prop];
1058 if(!attributePrivileges || attributePrivileges.create === undefined) {
1060 } else if (typeof attributePrivileges.create === 'string') {
1061 canEdit = this.checkPrivilege(attributePrivileges.create);
1063 canEdit = attributePrivileges.create; /* if it's true or false */
1066 /* Handle fixed values. */
1067 if (attr.value !== undefined) {
1068 params.columns.push("%" + count + "$I");
1069 params.expressions.push('$' + count);
1070 params.values.push(attr.value);
1071 params.identifiers.push(attr.column);
1075 /* Handle passed values. */
1076 } else if (canEdit && val !== undefined && val !== null && !ormp.toMany) {
1077 if (attr.isEncrypted) {
1078 if (encryptionKey) {
1079 encryptQuery = "select encrypt(setbytea(%1$L), setbytea(%2$L), %3$L)";
1080 encryptSql = XT.format(encryptQuery, [record[prop], encryptionKey, 'bf']);
1081 val = record[prop] ? plv8.execute(encryptSql)[0].encrypt : null;
1082 params.columns.push("%" + count + "$I");
1083 params.values.push(val);
1084 params.identifiers.push(attr.column);
1085 params.expressions.push("$" + count);
1089 throw new Error("No encryption key provided.");
1092 if (ormp.toOne && nkey) {
1093 if (iorm.table.indexOf(".") > 0) {
1094 toOneQuery = "select %1$I from %2$I.%3$I where %4$I = $" + count;
1095 toOneSql = XT.format(toOneQuery, [
1096 XT.Orm.primaryKey(iorm, true),
1097 iorm.table.beforeDot(),
1098 iorm.table.afterDot(),
1102 toOneQuery = "select %1$I from %2$I where %3$I = $" + count;
1103 toOneSql = XT.format(toOneQuery, [
1104 XT.Orm.primaryKey(iorm, true),
1109 exp = "(" + toOneSql + ")";
1110 params.expressions.push(exp);
1112 params.expressions.push('$' + count);
1115 params.columns.push("%" + count + "$I");
1116 params.values.push(val);
1117 params.identifiers.push(attr.column);
1121 /* Handle null value if applicable. */
1122 } else if (canEdit && val === undefined || val === null) {
1123 if (attr.nullValue) {
1124 params.columns.push("%" + count + "$I");
1125 params.values.push(attr.nullValue);
1126 params.identifiers.push(attr.column);
1127 params.expressions.push('$' + count);
1130 } else if (attr.required) {
1131 plv8.elog(ERROR, "Attribute " + ormp.name + " is required.");
1140 /* Build the insert statement */
1141 columns = params.columns.join(', ');
1142 columns = XT.format(columns, params.identifiers);
1143 expressions = params.expressions.join(', ');
1144 expressions = XT.format(expressions, params.identifiers);
1146 if (params.table.indexOf(".") > 0) {
1147 namespace = params.table.beforeDot();
1148 table = params.table.afterDot();
1149 query = 'insert into %1$I.%2$I (' + columns + ') values (' + expressions + ')';
1150 params.statement = XT.format(query, [namespace, table]);
1152 query = 'insert into %1$I (' + columns + ') values (' + expressions + ')';
1153 params.statement = XT.format(query, [params.table]);
1156 /* If we can get the primary key column we want to return that
1157 for cases where it is determined behind the scenes */
1158 if (!record[pkey] && !params.primaryKey) {
1159 params.primaryKey = XT.Orm.primaryKey(orm, true);
1162 if (params.primaryKey && params.parentUuid) {
1163 params.statement = params.statement + ' returning ' + params.primaryKey + ' as id, obj_uuid';
1164 } else if (params.parentUuid) {
1165 params.statement = params.statement + ' returning obj_uuid';
1166 } else if (params.primaryKey) {
1167 params.statement = params.statement + ' returning ' + params.primaryKey + ' as id';
1171 XT.debug('prepareInsert statement =', params.statement);
1172 XT.debug('prepareInsert values =', params.values);
1179 * Commit update to the database
1181 * @param {Object} Options
1182 * @param {String} [options.nameSpace] Namespace. Required.
1183 * @param {String} [options.type] Type. Required.
1184 * @param {Object} [options.data] The data payload to be processed. Required.
1185 * @param {Number} [options.etag] Record version for optimistic locking.
1186 * @param {Object} [options.lock] Lock information for pessemistic locking.
1187 * @param {String} [options.encryptionKey] Encryption key.
1189 updateRecord: function (options) {
1190 var data = options.data,
1191 encryptionKey = options.encryptionKey,
1192 orm = this.fetchOrm(options.nameSpace, options.type),
1193 pkey = XT.Orm.primaryKey(orm),
1196 etag = this.getVersion(orm, id),
1201 lockKey = options.lock && options.lock.key ? options.lock.key : false,
1202 lockTable = orm.lockTable || orm.table,
1204 sql = this.prepareUpdate(orm, data, null, encryptionKey);
1206 /* Test for optimistic lock. */
1207 if (!XT.disableLocks && etag && options.etag !== etag) {
1208 // TODO - Improve error handling.
1209 plv8.elog(ERROR, "The version being updated is not current.");
1211 /* Test for pessimistic lock. */
1213 lock = this.tryLock(lockTable, id, {key: lockKey});
1215 // TODO - Improve error handling.
1216 plv8.elog(ERROR, "Can not obtain a lock on the record.");
1220 /* Okay, now lets handle arrays. */
1221 this.commitArrays(orm, data, encryptionKey);
1223 /* Handle extensions on the same table. */
1224 for (var i = 0; i < orm.extensions.length; i++) {
1225 if (orm.extensions[i].table === orm.table) {
1226 sql = this.prepareUpdate(orm.extensions[i], data, sql, encryptionKey);
1230 sql.values.push(id);
1232 /* Commit the base record. */
1234 XT.debug('updateRecord sql =', sql.statement);
1235 XT.debug('updateRecord values =', sql.values);
1237 plv8.execute(sql.statement, sql.values);
1239 /* Handle extensions on other tables. */
1240 for (var i = 0; i < orm.extensions.length; i++) {
1241 ext = orm.extensions[i];
1242 if (ext.table !== orm.table &&
1245 /* Determine whether to insert or update. */
1246 if (ext.table.indexOf(".") > 0) {
1247 iORuQuery = "select %1$I from %2$I.%3$I where %1$I = $1;";
1248 iORuSql = XT.format(iORuQuery, [
1249 ext.relations[0].column,
1250 ext.table.beforeDot(),
1251 ext.table.afterDot()
1254 iORuQuery = "select %1$I from %2$I where %1$I = $1;";
1255 iORuSql = XT.format(iORuQuery, [ext.relations[0].column, ext.table]);
1259 XT.debug('updateRecord sql =', iORuSql);
1260 XT.debug('updateRecord values =', [data[pkey]]);
1262 rows = plv8.execute(iORuSql, [data[pkey]]);
1265 sql = this.prepareUpdate(ext, data, null, encryptionKey);
1266 sql.values.push(id);
1268 sql = this.prepareInsert(ext, data, null, encryptionKey);
1272 XT.debug('updateRecord sql =', sql.statement);
1273 XT.debug('updateRecord values =', sql.values);
1276 if (sql.statement) {
1277 plv8.execute(sql.statement, sql.values);
1282 /* Release any lock. */
1284 this.releaseLock({table: lockTable, id: id});
1289 * Use an orm object and a record and build an update statement. It
1290 * returns an object with a table name string, expressions array and
1291 * insert statement string that can be executed.
1293 * The optional params object includes objects columns, expressions
1294 * that can be cumulatively added to the result.
1296 * @params {Object} Orm
1297 * @params {Object} Record
1298 * @params {Object} Params - optional
1301 prepareUpdate: function (orm, record, params, encryptionKey) {
1303 attributePrivileges,
1326 params = params || {
1332 params.table = orm.table;
1333 count = params.values.length + 1;
1335 if (orm.relations) {
1337 pkey = orm.relations[0].inverse;
1338 columnKey = orm.relations[0].column;
1341 pkey = XT.Orm.primaryKey(orm);
1342 columnKey = XT.Orm.primaryKey(orm, true);
1345 /* Build up the content for update of this record. */
1346 for (var i = 0; i < orm.properties.length; i++) {
1347 ormp = orm.properties[i];
1349 attr = ormp.attr ? ormp.attr : ormp.toOne ? ormp.toOne : ormp.toMany;
1351 iorm = ormp.toOne ? this.fetchOrm(orm.nameSpace, ormp.toOne.type) : false;
1352 nkey = iorm ? XT.Orm.naturalKey(iorm, true) : false;
1353 val = ormp.toOne && record[prop] instanceof Object ?
1354 record[prop][nkey || ormp.toOne.inverse || 'id'] : record[prop],
1356 attributePrivileges = orm.privileges &&
1357 orm.privileges.attribute &&
1358 orm.privileges.attribute[prop];
1361 * Ignore derived fields for insert/update
1363 if (attr.derived) continue;
1365 if(!attributePrivileges || attributePrivileges.update === undefined) {
1367 } else if (typeof attributePrivileges.update === 'string') {
1368 canEdit = this.checkPrivilege(attributePrivileges.update);
1370 canEdit = attributePrivileges.update; /* if it's true or false */
1373 if (canEdit && val !== undefined && !ormp.toMany) {
1375 /* Handle encryption if applicable. */
1376 if (attr.isEncrypted) {
1377 if (encryptionKey) {
1378 encryptQuery = "select encrypt(setbytea(%1$L), setbytea(%2$L), %3$L)";
1379 encryptSql = XT.format(encryptQuery, [val, encryptionKey, 'bf']);
1380 val = record[prop] ? plv8.execute(encryptSql)[0].encrypt : null;
1381 params.values.push(val);
1382 params.identifiers.push(attr.column);
1383 params.expressions.push("%" + count + "$I = $" + count);
1387 // TODO - Improve error handling.
1388 throw new Error("No encryption key provided.");
1390 } else if (ormp.name !== pkey) {
1392 if (attr.required) {
1393 plv8.elog(ERROR, "Attribute " + ormp.name + " is required.");
1395 params.values.push(attr.nullValue || null);
1396 params.expressions.push("%" + count + "$I = $" + count);
1398 } else if (ormp.toOne && nkey) {
1399 if (iorm.table.indexOf(".") > 0) {
1400 toOneQuery = "select %1$I from %2$I.%3$I where %4$I = $" + count;
1401 toOneSql = XT.format(toOneQuery, [
1402 XT.Orm.primaryKey(iorm, true),
1403 iorm.table.beforeDot(),
1404 iorm.table.afterDot(),
1408 toOneQuery = "select %1$I from %2$I where %3$I = $" + count;
1409 toOneSql = XT.format(toOneQuery, [
1410 XT.Orm.primaryKey(iorm, true),
1416 exp = "%" + count + "$I = (" + toOneSql + ")";
1417 params.values.push(val);
1418 params.expressions.push(exp);
1420 params.values.push(val);
1421 params.expressions.push("%" + count + "$I = $" + count);
1423 params.identifiers.push(attr.column);
1430 /* Build the update statement */
1431 expressions = params.expressions.join(', ');
1432 expressions = XT.format(expressions, params.identifiers);
1434 // do not send an invalid sql statement
1435 if (!isValidSql) { return params; }
1437 if (params.table.indexOf(".") > 0) {
1438 namespace = params.table.beforeDot();
1439 table = params.table.afterDot();
1440 query = 'update %1$I.%2$I set ' + expressions + ' where %3$I = $' + count + ';';
1441 params.statement = XT.format(query, [namespace, table, columnKey]);
1443 query = 'update %1$I set ' + expressions + ' where %2$I = $' + count + ';';
1444 params.statement = XT.format(query, [params.table, columnKey]);
1448 XT.debug('prepareUpdate statement =', params.statement);
1449 XT.debug('prepareUpdate values =', params.values);
1456 * Commit deletion to the database
1458 * @param {Object} Options
1459 * @param {String} [options.nameSpace] Namespace. Required.
1460 * @param {String} [options.type] Type. Required.
1461 * @param {Object} [options.data] The data payload to be processed. Required.
1462 * @param {Number} [options.etag] Optional record id version for optimistic locking.
1463 * If set and version does not match, delete will fail.
1464 * @param {Number} [options.lock] Lock information for pessemistic locking.
1466 deleteRecord: function (options) {
1467 var data = options.data,
1468 orm = this.fetchOrm(options.nameSpace, options.type, {silentError: true}),
1476 lockKey = options.lock && options.lock.key ? options.lock.key : false,
1486 /* Set variables or return false with message. */
1488 throw new handleError("Not Found", 404);
1491 pkey = XT.Orm.primaryKey(orm);
1492 nkey = XT.Orm.naturalKey(orm);
1493 lockTable = orm.lockTable || orm.table;
1494 if (!pkey && !nkey) {
1495 throw new handleError("Not Found", 404);
1498 id = nkey ? this.getId(orm, data[nkey]) : data[pkey];
1500 throw new handleError("Not Found", 404);
1503 /* Test for optional optimistic lock. */
1504 etag = this.getVersion(orm, id);
1505 if (etag && options.etag && etag !== options.etag) {
1506 throw new handleError("Precondition Required", 428);
1509 /* Test for pessemistic lock. */
1511 lock = this.tryLock(lockTable, id, {key: lockKey});
1513 throw new handleError("Conflict", 409);
1517 /* Delete children first. */
1518 for (prop in data) {
1519 ormp = XT.Orm.getProperty(orm, prop);
1521 /* If the property is an array of objects they must be records so delete them. */
1522 if (ormp.toMany && ormp.toMany.isNested) {
1523 values = data[prop];
1524 for (var i = 0; i < values.length; i++) {
1526 nameSpace: options.nameSpace,
1527 type: ormp.toMany.type,
1534 /* Next delete from extension tables. */
1535 for (var i = 0; i < orm.extensions.length; i++) {
1536 ext = orm.extensions[i];
1537 if (ext.table !== orm.table &&
1539 columnKey = ext.relations[0].column;
1540 nameKey = ext.relations[0].inverse;
1542 if (ext.table.indexOf(".") > 0) {
1543 namespace = ext.table.beforeDot();
1544 table = ext.table.afterDot();
1545 query = 'delete from %1$I.%2$I where %3$I = $1';
1546 sql = XT.format(query, [namespace, table, columnKey]);
1548 query = 'delete from %1$I where %2$I = $1';
1549 sql = XT.format(query, [ext.table, columnKey]);
1553 XT.debug('deleteRecord sql =', sql);
1554 XT.debug('deleteRecord values =', [id]);
1556 plv8.execute(sql, [id]);
1560 /* Now delete the top. */
1561 nameKey = XT.Orm.primaryKey(orm);
1562 columnKey = XT.Orm.primaryKey(orm, true);
1564 if (orm.table.indexOf(".") > 0) {
1565 namespace = orm.table.beforeDot();
1566 table = orm.table.afterDot();
1567 query = 'delete from %1$I.%2$I where %3$I = $1';
1568 sql = XT.format(query, [namespace, table, columnKey]);
1570 query = 'delete from %1$I where %2$I = $1';
1571 sql = XT.format(query, [orm.table, columnKey]);
1574 /* Commit the record.*/
1576 XT.debug('deleteRecord sql =', sql);
1577 XT.debug('deleteRecord values =', [id]);
1579 plv8.execute(sql, [id]);
1581 /* Release any lock. */
1583 this.releaseLock({table: lockTable, id: id});
1588 * Decrypts properties where applicable.
1590 * @param {String} name space
1591 * @param {String} type
1592 * @param {Object} record
1593 * @param {Object} encryption key
1596 decrypt: function (nameSpace, type, record, encryptionKey) {
1599 hexToAlpha = function (hex) {
1601 for (i = 2; i < hex.length; i += 2) {
1602 str += String.fromCharCode(parseInt(hex.substr(i, 2), 16));
1606 orm = this.fetchOrm(nameSpace, type);
1608 for (prop in record) {
1609 var ormp = XT.Orm.getProperty(orm, prop.camelize());
1611 /* Decrypt property if applicable. */
1612 if (ormp && ormp.attr && ormp.attr.isEncrypted) {
1613 if (encryptionKey) {
1614 sql = "select formatbytea(decrypt($1, setbytea($2), 'bf')) as result";
1615 // TODO - Handle not found error.
1617 if (DEBUG && false) {
1618 XT.debug('decrypt prop =', prop);
1619 XT.debug('decrypt sql =', sql);
1620 XT.debug('decrypt values =', [record[prop], encryptionKey]);
1622 result = plv8.execute(sql, [record[prop], encryptionKey])[0].result;
1623 /* we SOMETIMES need to translate from hex here */
1624 if(typeof result === 'string' && result.substring(0, 2) === '\\x') {
1625 result = result ? hexToAlpha(result) : result;
1627 /* in the special case of encrypted credit card numbers, we don't give the
1628 user the full decrypted number EVEN IF they have the encryption key */
1629 if(ormp.attr.isEncrypted === "credit_card_number" && result && result.length >= 4) {
1630 record[prop] = "************" + result.substring(result.length - 4);
1632 record[prop] = result;
1635 record[prop] = '**********';
1638 /* Check recursively. */
1639 } else if (ormp.toOne && ormp.toOne.isNested) {
1640 that.decrypt(nameSpace, ormp.toOne.type, record[prop], encryptionKey);
1642 } else if (ormp.toMany && ormp.toMany.isNested) {
1643 record[prop].map(function (subdata) {
1644 that.decrypt(nameSpace, ormp.toMany.type, subdata, encryptionKey);
1653 Fetches the ORM. Caches the result in this data object, where it can be used
1654 for this request but will be conveniently forgotten between requests.
1656 fetchOrm: function (nameSpace, type) {
1659 recordType = nameSpace + '.'+ type;
1665 res = this._maps.findProperty('recordType', recordType);
1669 ret = XT.Orm.fetch(nameSpace, type);
1671 /* cache the result so we don't requery needlessly */
1672 this._maps.push({ "recordType": recordType, "map": ret});
1678 * Get the oid for a given table name.
1680 * @param {String} table name
1683 getTableOid: function (table) {
1684 var tableName = this.getTableFromNamespacedTable(table).toLowerCase(), /* be generous */
1685 namespace = this.getNamespaceFromNamespacedTable(table),
1687 sql = "select pg_class.oid::integer as oid " +
1688 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1689 "where relname = $1 and nspname = $2";
1692 XT.debug('getTableOid sql =', sql);
1693 XT.debug('getTableOid values =', [tableName, namespace]);
1695 ret = plv8.execute(sql, [tableName, namespace])[0].oid - 0;
1697 // TODO - Handle not found error.
1703 * Get the primary key id for an object based on a passed in natural key.
1705 * @param {Object} Orm
1706 * @param {String} Natural key value
1708 getId: function (orm, value) {
1709 var ncol = XT.Orm.naturalKey(orm, true),
1710 pcol = XT.Orm.primaryKey(orm, true),
1715 if (orm.table.indexOf(".") > 0) {
1716 namespace = orm.table.beforeDot();
1717 table = orm.table.afterDot();
1718 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1719 sql = XT.format(query, [pcol, namespace, table, ncol]);
1721 query = "select %1$I as id from %2$I where %3$I = $1";
1722 sql = XT.format(query, [pcol, orm.table, ncol]);
1726 XT.debug('getId sql =', sql);
1727 XT.debug('getId values =', [value]);
1730 ret = plv8.execute(sql, [value]);
1735 throw new handleError("Primary Key not found on " + orm.table +
1736 " where " + ncol + " = " + value, 400);
1740 getNamespaceFromNamespacedTable: function (fullName) {
1741 return fullName.indexOf(".") > 0 ? fullName.beforeDot() : "public";
1744 getTableFromNamespacedTable: function (fullName) {
1745 return fullName.indexOf(".") > 0 ? fullName.afterDot() : fullName;
1748 getPgTypeFromOrmType: function (schema, table, column) {
1749 var sql = "select data_type from information_schema.columns " +
1751 "and table_schema = $1 " +
1752 "and table_name = $2 " +
1753 "and column_name = $3;",
1755 values = [schema, table, column];
1758 XT.debug('getPgTypeFromOrmType sql =', sql);
1759 XT.debug('getPgTypeFromOrmType values =', values);
1762 pgType = plv8.execute(sql, values);
1763 pgType = pgType && pgType[0] ? pgType[0].data_type : false;
1769 * Get the natural key id for an object based on a passed in primary key.
1771 * @param {Object} Orm
1772 * @param {Number|String} Primary key value
1773 * @param {Boolean} safe Return the original value instead of erroring if no match is found
1775 getNaturalId: function (orm, value, safe) {
1776 var ncol = XT.Orm.naturalKey(orm, true),
1777 pcol = XT.Orm.primaryKey(orm, true),
1782 if (orm.table.indexOf(".") > 0) {
1783 namespace = orm.table.beforeDot();
1784 table = orm.table.afterDot();
1785 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1786 sql = XT.format(query, [ncol, namespace, table, pcol]);
1788 query = "select %1$I as id from %2$I where %3$I = $1";
1789 sql = XT.format(query, [ncol, orm.table, pcol]);
1793 XT.debug('getNaturalId sql =', sql);
1794 XT.debug('getNaturalId values =', [value]);
1797 ret = plv8.execute(sql, [value]);
1804 throw new handleError("Natural Key Not Found: " + orm.nameSpace + "." + orm.type, 400);
1809 * Returns the current version of a record.
1811 * @param {Object} Orm
1812 * @param {Number|String} Record id
1814 getVersion: function (orm, id) {
1815 if (!orm.lockable) { return; }
1818 oid = this.getTableOid(orm.lockTable || orm.table),
1820 sql = 'select ver_etag from xt.ver where ver_table_oid = $1 and ver_record_id = $2;';
1823 XT.debug('getVersion sql = ', sql);
1824 XT.debug('getVersion values = ', [oid, id]);
1826 res = plv8.execute(sql, [oid, id]);
1827 etag = res.length ? res[0].ver_etag : false;
1830 etag = XT.generateUUID();
1831 sql = 'insert into xt.ver (ver_table_oid, ver_record_id, ver_etag) values ($1, $2, $3::uuid);';
1832 // TODO - Handle insert error.
1835 XT.debug('getVersion insert sql = ', sql);
1836 XT.debug('getVersion insert values = ', [oid, id, etag]);
1838 plv8.execute(sql, [oid, id, etag]);
1845 * Fetch an array of records from the database.
1847 * @param {Object} Options
1848 * @param {String} [dataHash.nameSpace] Namespace. Required.
1849 * @param {String} [dataHash.type] Type. Required.
1850 * @param {Array} [dataHash.parameters] Parameters
1851 * @param {Array} [dataHash.orderBy] Order by - optional
1852 * @param {Number} [dataHash.rowLimit] Row limit - optional
1853 * @param {Number} [dataHash.rowOffset] Row offset - optional
1856 fetch: function (options) {
1857 var nameSpace = options.nameSpace,
1858 type = options.type,
1859 query = options.query || {},
1860 encryptionKey = options.encryptionKey,
1861 orderBy = query.orderBy,
1862 orm = this.fetchOrm(nameSpace, type),
1865 parameters = query.parameters,
1866 clause = this.buildClauseOptimized(nameSpace, type, parameters, orderBy),
1868 pkey = XT.Orm.primaryKey(orm),
1869 pkeyColumn = XT.Orm.primaryKey(orm, true),
1870 nkey = XT.Orm.naturalKey(orm),
1871 limit = query.rowLimit ? XT.format('limit %1$L', [query.rowLimit]) : '',
1872 offset = query.rowOffset ? XT.format('offset %1$L', [query.rowOffset]) : '',
1875 nameSpace: nameSpace,
1885 sql1 = 'select t1.%3$I as id from %1$I.%2$I t1 {joins} where {conditions} group by t1.%3$I{groupBy} {orderBy} {limit} {offset};',
1886 sql2 = 'select * from %1$I.%2$I where %3$I in ({ids}) {orderBy}';
1888 /* Validate - don't bother running the query if the user has no privileges. */
1889 if (!this.checkPrivileges(nameSpace, type)) { return []; }
1891 tableNamespace = this.getNamespaceFromNamespacedTable(orm.table);
1892 table = this.getTableFromNamespacedTable(orm.table);
1895 /* Just get the count of rows that match the conditions */
1896 sqlCount = 'select count(distinct t1.%3$I) as count from %1$I.%2$I t1 {joins} where {conditions};';
1897 sqlCount = XT.format(sqlCount, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1898 sqlCount = sqlCount.replace('{joins}', clause.joins)
1899 .replace('{conditions}', clause.conditions);
1902 XT.debug('fetch sqlCount = ', sqlCount);
1903 XT.debug('fetch values = ', clause.parameters);
1906 ret.data = plv8.execute(sqlCount, clause.parameters);
1910 /* Query the model. */
1911 sql1 = XT.format(sql1, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1912 sql1 = sql1.replace('{joins}', clause.joins)
1913 .replace('{conditions}', clause.conditions)
1914 .replace(/{groupBy}/g, clause.groupByColumns)
1915 .replace(/{orderBy}/g, clause.orderByColumns)
1916 .replace('{limit}', limit)
1917 .replace('{offset}', offset);
1920 XT.debug('fetch sql1 = ', sql1);
1921 XT.debug('fetch values = ', clause.parameters);
1924 /* First query for matching ids, then get entire result set. */
1925 /* This improves performance over a direct query on the view due */
1926 /* to the way sorting is handled by the query optimizer */
1927 qry = plv8.execute(sql1, clause.parameters) || [];
1928 if (!qry.length) { return [] };
1929 qry.forEach(function (row) {
1931 idParams.push("$" + counter);
1936 sql_etags = "select ver_etag as etag, ver_record_id as id " +
1938 "where ver_table_oid = ( " +
1939 "select pg_class.oid::integer as oid " +
1940 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1941 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
1942 "where nspname = %1$L and relname = %2$L " +
1944 "and ver_record_id in ({ids})";
1945 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
1946 sql_etags = sql_etags.replace('{ids}', idParams.join());
1949 XT.debug('fetch sql_etags = ', sql_etags);
1950 XT.debug('fetch etags_values = ', JSON.stringify(ids));
1952 etags = plv8.execute(sql_etags, ids) || {};
1956 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize(), pkey]);
1957 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
1958 .replace('{ids}', idParams.join());
1961 XT.debug('fetch sql2 = ', sql2);
1962 XT.debug('fetch values = ', JSON.stringify(ids));
1964 ret.data = plv8.execute(sql2, ids) || [];
1966 for (var i = 0; i < ret.data.length; i++) {
1967 ret.data[i] = this.decrypt(nameSpace, type, ret.data[i], encryptionKey);
1970 /* Add etags to result in pkey->etag format. */
1971 for (var j = 0; j < etags.length; j++) {
1972 if (etags[j].id === ret.data[i][pkey]) {
1973 ret.etags[ret.data[i][nkey]] = etags[j].etag;
1979 this.sanitize(nameSpace, type, ret.data, options);
1985 Fetch a metric value.
1987 @param {String} Metric name
1988 @param {String} Return type 'text', 'boolean' or 'number' (default 'text')
1990 fetchMetric: function (name, type) {
1991 var fn = 'fetchmetrictext';
1992 if (type === 'boolean') {
1993 fn = 'fetchmetricbool';
1994 } else if (type === 'number') {
1995 fn = 'fetchmetricvalue';
1997 return plv8.execute("select " + fn + "($1) as resp", [name])[0].resp;
2001 * Retreives a record from the database. If the user does not have appropriate privileges an
2002 * error will be thrown unless the `silentError` option is passed.
2004 * If `context` is passed as an option then a record will only be returned if it exists in the context (parent)
2005 * record which itself must be accessible by the effective user.
2007 * @param {Object} options
2008 * @param {String} [options.nameSpace] Namespace. Required.
2009 * @param {String} [options.type] Type. Required.
2010 * @param {Number} [options.id] Record id. Required.
2011 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
2012 * @param {String} [options.encryptionKey] Encryption key
2013 * @param {Boolean} [options.silentError=false] Silence errors
2014 * @param {Object} [options.context] Context
2015 * @param {String} [options.context.nameSpace] Context namespace.
2016 * @param {String} [options.context.type] The type of context object.
2017 * @param {String} [options.context.value] The value of the context's primary key.
2018 * @param {String} [options.context.relation] The name of the attribute on the type to which this record is related.
2021 retrieveRecord: function (options) {
2022 options = options ? options : {};
2023 options.obtainLock = false;
2025 var id = options.id,
2026 nameSpace = options.nameSpace,
2027 type = options.type,
2028 map = this.fetchOrm(nameSpace, type),
2029 context = options.context,
2030 encryptionKey = options.encryptionKey,
2032 lockTable = map.lockTable || map.table,
2033 nkey = XT.Orm.naturalKey(map),
2035 pkey = XT.Orm.primaryKey(map),
2037 nameSpace: nameSpace,
2044 throw new Error('No key found for {nameSpace}.{type}'
2045 .replace("{nameSpace}", nameSpace)
2046 .replace("{type}", type));
2049 /* If this object uses a natural key, go get the primary key id. */
2051 id = this.getId(map, id);
2057 /* Context means search for this record inside another. */
2059 context.nameSpace = context.nameSpace || context.recordType.beforeDot();
2060 context.type = context.type || context.recordType.afterDot()
2061 context.map = this.fetchOrm(context.nameSpace, context.type);
2062 context.prop = XT.Orm.getProperty(context.map, context.relation);
2063 context.pertinentExtension = XT.Orm.getProperty(context.map, context.relation, true);
2064 context.underlyingTable = context.pertinentExtension.table,
2065 context.underlyingNameSpace = this.getNamespaceFromNamespacedTable(context.underlyingTable);
2066 context.underlyingType = this.getTableFromNamespacedTable(context.underlyingTable);
2067 context.fkey = context.prop.toMany.inverse;
2068 context.fkeyColumn = context.prop.toMany.column;
2069 context.pkey = XT.Orm.naturalKey(context.map) || XT.Orm.primaryKey(context.map);
2070 params.attribute = context.pkey;
2071 params.value = context.value;
2073 join = 'join %1$I.%2$I on (%1$I.%2$I.%3$I = %4$I.%5$I)';
2074 join = XT.format(join, [
2075 context.underlyingNameSpace,
2076 context.underlyingType,
2083 /* Validate - don't bother running the query if the user has no privileges. */
2084 if(!options.superUser && !context && !this.checkPrivileges(nameSpace, type)) {
2085 if (options.silentError) {
2088 throw new handleError("Unauthorized", 401);
2092 ret.etag = this.getVersion(map, id);
2094 /* Obtain lock if required. */
2096 ret.lock = this.tryLock(lockTable, id, options);
2100 sql = 'select %1$I.* from %2$I.%1$I {join} where %1$I.%3$I = $1;';
2101 sql = sql.replace(/{join}/, join);
2102 sql = XT.format(sql, [type.decamelize(), nameSpace.decamelize(), pkey]);
2104 /* Query the map. */
2106 XT.debug('retrieveRecord sql = ', sql);
2107 XT.debug('retrieveRecord values = ', [id]);
2109 ret.data = plv8.execute(sql, [id])[0] || {};
2112 /* Check privileges again, this time against record specific criteria where applicable. */
2113 if(!options.superUser && !this.checkPrivileges(nameSpace, type, ret.data)) {
2114 if (options.silentError) {
2117 throw new handleError("Unauthorized", 401);
2120 /* Decrypt result where applicable. */
2121 ret.data = this.decrypt(nameSpace, type, ret.data, encryptionKey);
2124 this.sanitize(nameSpace, type, ret.data, options);
2126 /* Return the results. */
2131 * Remove unprivileged attributes, primary and foreign keys from the data.
2132 * Only removes the primary key if a natural key has been specified in the ORM.
2133 * Also format for printing using XT.format functions if printFormat=true'
2135 * @param {String} Namespace
2136 * @param {String} Type
2137 * @param {Object|Array} Data
2138 * @param {Object} Options
2139 * @param {Boolean} [options.includeKeys=false] Do not remove primary and foreign keys.
2140 * @param {Boolean} [options.superUser=false] Do not remove unprivileged attributes.
2141 * @param {Boolean} [options.printFormat=true] Format for printing.
2143 sanitize: function (nameSpace, type, data, options) {
2144 options = options || {};
2145 if (options.includeKeys && options.superUser) { return; }
2146 if (XT.typeOf(data) !== "array") { data = [data]; }
2147 var orm = this.fetchOrm(nameSpace, type),
2148 pkey = XT.Orm.primaryKey(orm),
2149 nkey = XT.Orm.naturalKey(orm),
2150 props = orm.properties,
2151 attrPriv = orm.privileges && orm.privileges.attribute ?
2152 orm.privileges.attribute : false,
2153 inclKeys = options.includeKeys,
2154 superUser = options.superUser,
2155 printFormat = options.printFormat,
2166 check = function (p) {
2167 return p.name === itemAttr;
2170 for (var c = 0; c < data.length; c++) {
2173 /* Remove primary key if applicable */
2174 if (!inclKeys && nkey && nkey !== pkey) { delete item[pkey]; }
2176 for (itemAttr in item) {
2177 if (!item.hasOwnProperty(itemAttr)) {
2180 filteredProps = orm.properties.filter(check);
2182 if (filteredProps.length === 0 && orm.extensions.length > 0) {
2183 /* Try to get the orm prop from an extension if it's not in the core*/
2184 orm.extensions.forEach(function (ext) {
2185 if (filteredProps.length === 0) {
2186 filteredProps = ext.properties.filter(check);
2191 /* Remove attributes not found in the ORM */
2192 if (filteredProps.length === 0) {
2193 delete item[itemAttr];
2195 prop = filteredProps[0];
2198 /* Remove unprivileged attribute if applicable */
2199 if (!superUser && attrPriv && attrPriv[prop.name] &&
2200 (attrPriv[prop.name].view !== undefined) &&
2201 !this.checkPrivilege(attrPriv[prop.name].view)) {
2202 delete item[prop.name];
2205 /* Format for printing if printFormat and not an object */
2206 if (printFormat && !prop.toOne && !prop.toMany) {
2207 switch(prop.attr.type) {
2209 preOffsetDate = item[itemAttr];
2210 offsetDate = preOffsetDate &&
2211 new Date(preOffsetDate.valueOf() + 60000 * preOffsetDate.getTimezoneOffset());
2212 item[itemAttr] = XT.formatDate(offsetDate).formatdate;
2215 item[itemAttr] = XT.formatCost(item[itemAttr]).formatcost.toString();
2218 item[itemAttr] = XT.formatNumeric(item[itemAttr], "").formatnumeric.toString();
2221 item[itemAttr] = XT.formatMoney(item[itemAttr]).formatmoney.toString();
2224 item[itemAttr] = XT.formatSalesPrice(item[itemAttr]).formatsalesprice.toString();
2226 case "PurchasePrice":
2227 item[itemAttr] = XT.formatPurchPrice(item[itemAttr]).formatpurchprice.toString();
2229 case "ExtendedPrice":
2230 item[itemAttr] = XT.formatExtPrice(item[itemAttr]).formatextprice.toString();
2233 item[itemAttr] = XT.formatQty(item[itemAttr]).formatqty.toString();
2236 item[itemAttr] = XT.formatQtyPer(item[itemAttr]).formatqtyper.toString();
2238 case "UnitRatioScale":
2239 item[itemAttr] = XT.formatRatio(item[itemAttr]).formatratio.toString();
2242 item[itemAttr] = XT.formatPrcnt(item[itemAttr]).formatprcnt.toString();
2245 item[itemAttr] = XT.formatWeight(item[itemAttr]).formatweight.toString();
2248 item[itemAttr] = (item[itemAttr] || "").toString();
2252 /* Handle composite types */
2253 if (prop.toOne && prop.toOne.isNested && item[prop.name]) {
2254 this.sanitize(nameSpace, prop.toOne.type, item[prop.name], options);
2255 } else if (prop.toMany && prop.toMany.isNested && item[prop.name]) {
2256 for (var n = 0; n < item[prop.name].length; n++) {
2257 val = item[prop.name][n];
2259 /* Remove foreign key if applicable */
2260 if (!inclKeys) { delete val[prop.toMany.inverse]; }
2261 this.sanitize(nameSpace, prop.toMany.type, val, options);
2269 * Returns a array of key value pairs of metric settings that correspond with an array of passed keys.
2271 * @param {Array} array of metric names
2274 retrieveMetrics: function (keys) {
2279 sql = 'select metric_name as setting, metric_value as value '
2281 + 'where metric_name in ({literals})';
2283 for (var i = 0; i < keys.length; i++) {
2284 literals[i] = "%" + (i + 1) + "$L";
2287 sql = sql.replace(/{literals}/, literals.join(','));
2288 sql = XT.format(sql, keys)
2291 XT.debug('retrieveMetrics sql = ', sql);
2293 qry = plv8.execute(sql);
2295 /* Recast where applicable. */
2296 for (var i = 0; i < qry.length; i++) {
2297 prop = qry[i].setting;
2298 if(qry[i].value === 't') { ret[prop] = true; }
2299 else if(qry[i].value === 'f') { ret[prop] = false }
2300 else if(!isNaN(qry[i].value)) { ret[prop] = qry[i].value - 0; }
2301 else { ret[prop] = qry[i].value; }
2304 /* Make sure there is a result at all times */
2305 keys.forEach(function (key) {
2306 if (ret[key] === undefined) { ret[key] = null; }
2313 * Creates and returns a lock for a given table. Defaults to a time based lock of 30 seconds
2314 * unless aternate timeout option or process id (pid) is passed. If a pid is passed, the lock
2315 * is considered infinite as long as the pid is valid. If a previous lock key is passed and it is
2316 * valid, a new lock will be granted.
2318 * @param {String | Number} Table name or oid
2319 * @param {Number} Record id
2320 * @param {Object} Options
2321 * @param {Number} [options.timeout=30]
2322 * @param {Number} [options.pid] Process id
2323 * @param {Number} [options.key] Key
2324 * @param {Boolean} [options.obtainLock=true] If false, only checks for existing lock
2326 tryLock: function (table, id, options) {
2327 options = options ? options : {};
2329 var deleteSql = "delete from xt.lock where lock_id = $1;",
2330 timeout = options.timeout || 30,
2331 expires = new Date(),
2333 insertSqlExp = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_expires) " +
2334 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2335 insertSqlPid = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_pid) " +
2336 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2341 pid = options.pid || null,
2342 pidSql = "select usename, procpid " +
2343 "from pg_stat_activity " +
2344 "where datname=current_database() " +
2345 " and usename=$1 " +
2348 selectSql = "select * " +
2350 "where lock_table_oid = $1 " +
2351 " and lock_record_id = $2;",
2352 username = XT.username;
2354 /* If passed a table name, look up the oid. */
2355 oid = typeof table === "string" ? this.getTableOid(table) : table;
2357 if (DEBUG) XT.debug("Trying lock table", [oid, id]);
2359 /* See if there are existing lock(s) for this record. */
2361 XT.debug('tryLock sql = ', selectSql);
2362 XT.debug('tryLock values = ', [oid, id]);
2364 query = plv8.execute(selectSql, [oid, id]);
2366 /* Validate result */
2367 if (query.length > 0) {
2368 while (query.length) {
2369 lock = query.shift();
2371 /* See if we are confirming our own lock. */
2372 if (options.key && options.key === lock.lock_id) {
2373 /* Go on and we'll get a new lock. */
2375 /* Make sure if they are pid locks users is still connected. */
2376 } else if (lock.lock_pid) {
2378 XT.debug('tryLock sql = ', pidSql);
2379 XT.debug('tryLock values = ', [lock.lock_username, lock.lock_pid]);
2381 pcheck = plv8.execute(pidSql, [lock.lock_username, lock.lock_pid]);
2382 if (pcheck.length) { break; } /* valid lock */
2384 lockExp = new Date(lock.lock_expires);
2385 if (DEBUG) { XT.debug("Lock found", [lockExp > expires, lockExp, expires]); }
2386 if (lockExp > expires) { break; } /* valid lock */
2389 /* Delete invalid or expired lock. */
2391 XT.debug('tryLock sql = ', deleteSql);
2392 XT.debug('tryLock values = ', [lock.lock_id]);
2394 plv8.execute(deleteSql, [lock.lock_id]);
2399 if (DEBUG) XT.debug("Lock found", lock.lock_username);
2402 username: lock.lock_username,
2403 effective: lock.lock_effective
2408 if (options.obtainLock === false) { return; }
2410 if (DEBUG) { XT.debug("Creating lock."); }
2411 if (DEBUG) { XT.debug('tryLock sql = ', insertSqlPid); }
2414 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, pid]); }
2415 lock = plv8.execute(insertSqlPid, [oid, id, username, pid])[0];
2417 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2418 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, expires]); }
2419 lock = plv8.execute(insertSqlExp, [oid, id, username, expires])[0];
2422 if (DEBUG) { XT.debug("Lock returned is", lock.lock_id); }
2426 effective: lock.lock_effective,
2432 * Release a lock. Pass either options with a key, or table, id and username.
2434 * @param {Object} Options: key or table and id
2436 releaseLock: function (options) {
2438 sqlKey = 'delete from xt.lock where lock_id = $1;',
2439 sqlUsr = 'delete from xt.lock where lock_table_oid = $1 and lock_record_id = $2 and lock_username = $3;',
2440 username = XT.username;
2444 XT.debug('releaseLock sql = ', sqlKey);
2445 XT.debug('releaseLock values = ', [options.key]);
2447 plv8.execute(sqlKey, [options.key]);
2449 oid = typeof options.table === "string" ? this.getTableOid(options.table) : options.table;
2452 XT.debug('releaseLock sql = ', sqlUsr);
2453 XT.debug('releaseLock values = ', [oid, options.id, username]);
2455 plv8.execute(sqlUsr, [oid, options.id, username]);
2461 /* This deprecated function is still used by three dispatch functions. We should delete
2462 this as soon as we refactor those, and then rename buildClauseOptimized to buildClause */
2463 buildClause: function (nameSpace, type, parameters, orderBy) {
2464 parameters = parameters || [];
2466 var arrayIdentifiers = [],
2477 orderByIdentifiers = [],
2479 orm = this.fetchOrm(nameSpace, type),
2485 privileges = orm.privileges,
2489 ret.conditions = "";
2490 ret.parameters = [];
2492 /* Handle privileges. */
2493 if (orm.isNestedOnly) { plv8.elog(ERROR, 'Access Denied'); }
2497 (!this.checkPrivilege(privileges.all.read) &&
2498 !this.checkPrivilege(privileges.all.update)))
2500 privileges.personal &&
2501 (this.checkPrivilege(privileges.personal.read) ||
2502 this.checkPrivilege(privileges.personal.update))
2506 attribute: privileges.personal.properties,
2508 isUsernamePrivFilter: true,
2513 /* Handle parameters. */
2514 if (parameters.length) {
2515 for (var i = 0; i < parameters.length; i++) {
2517 param = parameters[i];
2518 op = param.operator || '=';
2538 for (var c = 0; c < param.value.length; c++) {
2539 ret.parameters.push(param.value[c]);
2540 param.value[c] = '$' + count;
2546 for (var c = 0; c < param.value.length; c++) {
2547 ret.parameters.push(param.value[c]);
2548 param.value[c] = '$' + count;
2553 plv8.elog(ERROR, 'Invalid operator: ' + op);
2556 /* Handle characteristics. This is very specific to xTuple,
2557 and highly dependant on certain table structures and naming conventions,
2558 but otherwise way too much work to refactor in an abstract manner right now. */
2559 if (param.isCharacteristic) {
2562 param.value = ' ARRAY[' + param.value.join(',') + ']';
2565 /* Booleans are stored as strings. */
2566 if (param.value === true) {
2568 } else if (param.value === false) {
2572 /* Yeah, it depends on a property called 'characteristics'... */
2573 prop = XT.Orm.getProperty(orm, 'characteristics');
2575 /* Build the characteristics query clause. */
2576 identifiers.push(prop.toMany.inverse);
2577 identifiers.push(orm.nameSpace.toLowerCase());
2578 identifiers.push(prop.toMany.type.decamelize());
2579 identifiers.push(param.attribute);
2580 identifiers.push(param.value);
2582 charSql = 'id in (' +
2583 ' select %' + (identifiers.length - 4) + '$I '+
2584 ' from %' + (identifiers.length - 3) + '$I.%' + (identifiers.length - 2) + '$I ' +
2585 ' join char on (char_name = characteristic)' +
2587 /* Note: Not using $i for these. L = literal here. These is not identifiers. */
2588 ' and char_name = %' + (identifiers.length - 1) + '$L ' +
2589 ' and value ' + op + ' %' + (identifiers.length) + '$L ' +
2592 clauses.push(charSql);
2594 /* Array comparisons handle another way. e.g. %1$I !<@ ARRAY[$1,$2] */
2595 } else if (op === '<@' || op === '!<@') {
2596 /* Handle paths if applicable. */
2597 if (param.attribute.indexOf('.') > -1) {
2598 parts = param.attribute.split('.');
2599 childOrm = this.fetchOrm(nameSpace, type);
2601 pcount = params.length - 1;
2603 for (var n = 0; n < parts.length; n++) {
2604 /* Validate attribute. */
2605 prop = XT.Orm.getProperty(childOrm, parts[n]);
2607 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
2610 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2611 identifiers.push(parts[n]);
2612 params[pcount] += "%" + identifiers.length + "$I";
2613 if (n < parts.length - 1) {
2614 params[pcount] = "(" + params[pcount] + ").";
2615 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2617 params[pcount] += op + ' ARRAY[' + param.value.join(',') + ']';
2621 identifiers.push(param.attribute);
2622 params.push("%" + identifiers.length + "$I " + op + ' ARRAY[' + param.value.join(',') + ']');
2623 pcount = params.length - 1;
2625 clauses.push(params[pcount]);
2627 /* Everything else handle another. */
2629 if (XT.typeOf(param.attribute) !== 'array') {
2630 param.attribute = [param.attribute];
2633 for (var c = 0; c < param.attribute.length; c++) {
2634 /* Handle paths if applicable. */
2635 if (param.attribute[c].indexOf('.') > -1) {
2636 parts = param.attribute[c].split('.');
2637 childOrm = this.fetchOrm(nameSpace, type);
2639 pcount = params.length - 1;
2642 /* Check if last part is an Array. */
2643 for (var m = 0; m < parts.length; m++) {
2644 /* Validate attribute. */
2645 prop = XT.Orm.getProperty(childOrm, parts[m]);
2647 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[m]);
2650 if (m < parts.length - 1) {
2651 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2652 } else if (prop.attr && prop.attr.type === 'Array') {
2653 /* The last property in the path is an array. */
2655 params[pcount] = '$' + count;
2659 /* Reset the childOrm to parent. */
2660 childOrm = this.fetchOrm(nameSpace, type);
2662 for (var n = 0; n < parts.length; n++) {
2663 /* Validate attribute. */
2664 prop = XT.Orm.getProperty(childOrm, parts[n]);
2666 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
2669 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
2670 if (param.isUsernamePrivFilter && isArray) {
2671 identifiers.push(parts[n]);
2672 arrayIdentifiers.push(identifiers.length);
2674 if (n < parts.length - 1) {
2675 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2678 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2679 identifiers.push(parts[n]);
2680 params[pcount] += "%" + identifiers.length + "$I";
2682 if (n < parts.length - 1) {
2683 params[pcount] = "(" + params[pcount] + ").";
2684 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2685 } else if (param.isLower) {
2686 params[pcount] = "lower(" + params[pcount] + ")";
2691 /* Validate attribute. */
2692 prop = XT.Orm.getProperty(orm, param.attribute[c]);
2694 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute[c]);
2697 identifiers.push(param.attribute[c]);
2699 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
2700 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested) ||
2701 (prop.attr && prop.attr.type === 'Array'))) {
2703 params.push('$' + count);
2704 pcount = params.length - 1;
2705 arrayIdentifiers.push(identifiers.length);
2707 params.push("%" + identifiers.length + "$I");
2708 pcount = params.length - 1;
2712 /* Add persional privs array search. */
2713 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested)
2714 || (prop.attr && prop.attr.type === 'Array') || isArray)) {
2716 /* e.g. 'admin' = ANY (usernames_array) */
2718 params[pcount] += ' ' + op + ' ANY (';
2720 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2721 for (var f =0; f < arrayIdentifiers.length; f++) {
2722 arrayParams += '%' + arrayIdentifiers[f] + '$I';
2723 if (f < arrayIdentifiers.length - 1) {
2724 arrayParams = "(" + arrayParams + ").";
2727 params[pcount] += arrayParams + ')';
2729 /* Add optional is null clause. */
2730 } else if (parameters[i].includeNull) {
2731 /* e.g. %1$I = $1 or %1$I is null */
2732 params[pcount] = params[pcount] + " " + op + ' $' + count + ' or ' + params[pcount] + ' is null';
2734 /* e.g. %1$I = $1 */
2735 params[pcount] += " " + op + ' $' + count;
2738 orClause.push(params[pcount]);
2741 /* If more than one clause we'll get: (%1$I = $1 or %1$I = $2 or %1$I = $3) */
2742 clauses.push('(' + orClause.join(' or ') + ')');
2744 ret.parameters.push(param.value);
2749 ret.conditions = (clauses.length ? '(' + XT.format(clauses.join(' and '), identifiers) + ')' : ret.conditions) || true;
2751 /* Massage ordeBy with quoted identifiers. */
2753 for (var i = 0; i < orderBy.length; i++) {
2754 /* Handle path case. */
2755 if (orderBy[i].attribute.indexOf('.') > -1) {
2756 parts = orderBy[i].attribute.split('.');
2758 orderByParams.push("");
2759 pcount = orderByParams.length - 1;
2761 for (var n = 0; n < parts.length; n++) {
2762 prop = XT.Orm.getProperty(orm, parts[n]);
2764 plv8.elog(ERROR, 'Attribute not found in map: ' + parts[n]);
2766 orderByIdentifiers.push(parts[n]);
2767 orderByParams[pcount] += "%" + orderByIdentifiers.length + "$I";
2769 if (n < parts.length - 1) {
2770 orderByParams[pcount] = "(" + orderByParams[pcount] + ").";
2771 orm = this.fetchOrm(nameSpace, prop.toOne.type);
2777 prop = XT.Orm.getProperty(orm, orderBy[i].attribute);
2779 plv8.elog(ERROR, 'Attribute not found in map: ' + orderBy[i].attribute);
2781 orderByIdentifiers.push(orderBy[i].attribute);
2782 orderByParams.push("%" + orderByIdentifiers.length + "$I");
2783 pcount = orderByParams.length - 1;
2786 if (orderBy[i].isEmpty) {
2787 orderByParams[pcount] = "length(" + orderByParams[pcount] + ")=0";
2789 if (orderBy[i].descending) {
2790 orderByParams[pcount] += " desc";
2793 list.push(orderByParams[pcount])
2797 ret.orderBy = list.length ? XT.format('order by ' + list.join(','), orderByIdentifiers) : '';
2802 * Renew a lock. Defaults to rewing the lock for 30 seconds.
2804 * @param {Number} Key
2805 * @params {Object} Options: timeout
2806 * @returns {Date} New expiration or false.
2808 renewLock: function (key, options) {
2809 var expires = new Date(),
2811 selectSql = "select * from xt.lock where lock_id = $1;",
2812 timeout = options && options.timeout ? options.timeout : 30,
2813 updateSql = "update xt.lock set lock_expires = $1 where lock_id = $2;";
2815 if (typeof key !== "number") { return false; }
2816 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2819 XT.debug('renewLock sql = ', selectSql);
2820 XT.debug('renewLock values = ', [key]);
2822 query = plv8.execute(selectSql, [key]);
2826 XT.debug('renewLock sql = ', updateSql);
2827 XT.debug('renewLock values = ', [expires, key]);
2829 plv8.execute(updateSql, [expires, key]);