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.push(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 (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 sql = ', sql);
1836 XT.debug('getVersion 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 /* Because we query views of views, you can get inconsistent results */
1911 /* when doing limit and offest queries without an order by. Add a default. */
1912 if (limit && offset && (!orderBy || !orderBy.length) && !clause.orderByColumns) {
1913 /* We only want this on sql1, not sql2's clause.orderBy. */
1914 clause.orderByColumns = XT.format('order by t1.%1$I', [pkeyColumn]);
1917 /* Query the model. */
1918 sql1 = XT.format(sql1, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1919 sql1 = sql1.replace('{joins}', clause.joins)
1920 .replace('{conditions}', clause.conditions)
1921 .replace(/{groupBy}/g, clause.groupByColumns)
1922 .replace(/{orderBy}/g, clause.orderByColumns)
1923 .replace('{limit}', limit)
1924 .replace('{offset}', offset);
1927 XT.debug('fetch sql1 = ', sql1);
1928 XT.debug('fetch values = ', clause.parameters);
1931 /* First query for matching ids, then get entire result set. */
1932 /* This improves performance over a direct query on the view due */
1933 /* to the way sorting is handled by the query optimizer */
1934 qry = plv8.execute(sql1, clause.parameters) || [];
1935 if (!qry.length) { return [] };
1936 qry.forEach(function (row) {
1938 idParams.push("$" + counter);
1943 sql_etags = "select ver_etag as etag, ver_record_id as id " +
1945 "where ver_table_oid = ( " +
1946 "select pg_class.oid::integer as oid " +
1947 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1948 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
1949 "where nspname = %1$L and relname = %2$L " +
1951 "and ver_record_id in ({ids})";
1952 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
1953 sql_etags = sql_etags.replace('{ids}', idParams.join());
1956 XT.debug('fetch sql_etags = ', sql_etags);
1957 XT.debug('fetch etags_values = ', JSON.stringify(ids));
1959 etags = plv8.execute(sql_etags, ids) || {};
1963 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize(), pkey]);
1964 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
1965 .replace('{ids}', idParams.join());
1968 XT.debug('fetch sql2 = ', sql2);
1969 XT.debug('fetch values = ', JSON.stringify(ids));
1971 ret.data = plv8.execute(sql2, ids) || [];
1973 for (var i = 0; i < ret.data.length; i++) {
1974 ret.data[i] = this.decrypt(nameSpace, type, ret.data[i], encryptionKey);
1977 /* Add etags to result in pkey->etag format. */
1978 for (var j = 0; j < etags.length; j++) {
1979 if (etags[j].id === ret.data[i][pkey]) {
1980 ret.etags[ret.data[i][nkey]] = etags[j].etag;
1986 this.sanitize(nameSpace, type, ret.data, options);
1992 Fetch a metric value.
1994 @param {String} Metric name
1995 @param {String} Return type 'text', 'boolean' or 'number' (default 'text')
1997 fetchMetric: function (name, type) {
1998 var fn = 'fetchmetrictext';
1999 if (type === 'boolean') {
2000 fn = 'fetchmetricbool';
2001 } else if (type === 'number') {
2002 fn = 'fetchmetricvalue';
2004 return plv8.execute("select " + fn + "($1) as resp", [name])[0].resp;
2008 * Retreives a record from the database. If the user does not have appropriate privileges an
2009 * error will be thrown unless the `silentError` option is passed.
2011 * If `context` is passed as an option then a record will only be returned if it exists in the context (parent)
2012 * record which itself must be accessible by the effective user.
2014 * @param {Object} options
2015 * @param {String} [options.nameSpace] Namespace. Required.
2016 * @param {String} [options.type] Type. Required.
2017 * @param {Number} [options.id] Record id. Required.
2018 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
2019 * @param {String} [options.encryptionKey] Encryption key
2020 * @param {Boolean} [options.silentError=false] Silence errors
2021 * @param {Object} [options.context] Context
2022 * @param {String} [options.context.nameSpace] Context namespace.
2023 * @param {String} [options.context.type] The type of context object.
2024 * @param {String} [options.context.value] The value of the context's primary key.
2025 * @param {String} [options.context.relation] The name of the attribute on the type to which this record is related.
2028 retrieveRecord: function (options) {
2029 options = options ? options : {};
2030 options.obtainLock = false;
2032 var id = options.id,
2033 nameSpace = options.nameSpace,
2034 type = options.type,
2035 map = this.fetchOrm(nameSpace, type),
2036 context = options.context,
2037 encryptionKey = options.encryptionKey,
2039 lockTable = map.lockTable || map.table,
2040 nkey = XT.Orm.naturalKey(map),
2042 pkey = XT.Orm.primaryKey(map),
2044 nameSpace: nameSpace,
2051 throw new Error('No key found for {nameSpace}.{type}'
2052 .replace("{nameSpace}", nameSpace)
2053 .replace("{type}", type));
2056 /* If this object uses a natural key, go get the primary key id. */
2058 id = this.getId(map, id);
2064 /* Context means search for this record inside another. */
2066 context.nameSpace = context.nameSpace || context.recordType.beforeDot();
2067 context.type = context.type || context.recordType.afterDot()
2068 context.map = this.fetchOrm(context.nameSpace, context.type);
2069 context.prop = XT.Orm.getProperty(context.map, context.relation);
2070 context.pertinentExtension = XT.Orm.getProperty(context.map, context.relation, true);
2071 context.underlyingTable = context.pertinentExtension.table,
2072 context.underlyingNameSpace = this.getNamespaceFromNamespacedTable(context.underlyingTable);
2073 context.underlyingType = this.getTableFromNamespacedTable(context.underlyingTable);
2074 context.fkey = context.prop.toMany.inverse;
2075 context.fkeyColumn = context.prop.toMany.column;
2076 context.pkey = XT.Orm.naturalKey(context.map) || XT.Orm.primaryKey(context.map);
2077 params.attribute = context.pkey;
2078 params.value = context.value;
2080 join = 'join %1$I.%2$I on (%1$I.%2$I.%3$I = %4$I.%5$I)';
2081 join = XT.format(join, [
2082 context.underlyingNameSpace,
2083 context.underlyingType,
2090 /* Validate - don't bother running the query if the user has no privileges. */
2091 if(!options.superUser && !context && !this.checkPrivileges(nameSpace, type)) {
2092 if (options.silentError) {
2095 throw new handleError("Unauthorized", 401);
2099 ret.etag = this.getVersion(map, id);
2101 /* Obtain lock if required. */
2103 ret.lock = this.tryLock(lockTable, id, options);
2107 sql = 'select %1$I.* from %2$I.%1$I {join} where %1$I.%3$I = $1;';
2108 sql = sql.replace(/{join}/, join);
2109 sql = XT.format(sql, [type.decamelize(), nameSpace.decamelize(), pkey]);
2111 /* Query the map. */
2113 XT.debug('retrieveRecord sql = ', sql);
2114 XT.debug('retrieveRecord values = ', [id]);
2116 ret.data = plv8.execute(sql, [id])[0] || {};
2119 /* Check privileges again, this time against record specific criteria where applicable. */
2120 if(!options.superUser && !this.checkPrivileges(nameSpace, type, ret.data)) {
2121 if (options.silentError) {
2124 throw new handleError("Unauthorized", 401);
2127 /* Decrypt result where applicable. */
2128 ret.data = this.decrypt(nameSpace, type, ret.data, encryptionKey);
2131 this.sanitize(nameSpace, type, ret.data, options);
2133 /* Return the results. */
2138 * Remove unprivileged attributes, primary and foreign keys from the data.
2139 * Only removes the primary key if a natural key has been specified in the ORM.
2140 * Also format for printing using XT.format functions if printFormat=true'
2142 * @param {String} Namespace
2143 * @param {String} Type
2144 * @param {Object|Array} Data
2145 * @param {Object} Options
2146 * @param {Boolean} [options.includeKeys=false] Do not remove primary and foreign keys.
2147 * @param {Boolean} [options.superUser=false] Do not remove unprivileged attributes.
2148 * @param {Boolean} [options.printFormat=true] Format for printing.
2150 sanitize: function (nameSpace, type, data, options) {
2151 options = options || {};
2152 if (options.includeKeys && options.superUser) { return; }
2153 if (XT.typeOf(data) !== "array") { data = [data]; }
2154 var orm = this.fetchOrm(nameSpace, type),
2155 pkey = XT.Orm.primaryKey(orm),
2156 nkey = XT.Orm.naturalKey(orm),
2157 props = orm.properties,
2158 attrPriv = orm.privileges && orm.privileges.attribute ?
2159 orm.privileges.attribute : false,
2160 inclKeys = options.includeKeys,
2161 superUser = options.superUser,
2162 printFormat = options.printFormat,
2173 check = function (p) {
2174 return p.name === itemAttr;
2177 for (var c = 0; c < data.length; c++) {
2180 /* Remove primary key if applicable */
2181 if (!inclKeys && nkey && nkey !== pkey) { delete item[pkey]; }
2183 for (itemAttr in item) {
2184 if (!item.hasOwnProperty(itemAttr)) {
2187 filteredProps = orm.properties.filter(check);
2189 if (filteredProps.length === 0 && orm.extensions.length > 0) {
2190 /* Try to get the orm prop from an extension if it's not in the core*/
2191 orm.extensions.forEach(function (ext) {
2192 if (filteredProps.length === 0) {
2193 filteredProps = ext.properties.filter(check);
2198 /* Remove attributes not found in the ORM */
2199 if (filteredProps.length === 0) {
2200 delete item[itemAttr];
2202 prop = filteredProps[0];
2205 /* Remove unprivileged attribute if applicable */
2206 if (!superUser && attrPriv && attrPriv[prop.name] &&
2207 (attrPriv[prop.name].view !== undefined) &&
2208 !this.checkPrivilege(attrPriv[prop.name].view)) {
2209 delete item[prop.name];
2212 /* Format for printing if printFormat and not an object */
2213 if (printFormat && !prop.toOne && !prop.toMany) {
2214 switch(prop.attr.type) {
2216 preOffsetDate = item[itemAttr];
2217 offsetDate = preOffsetDate &&
2218 new Date(preOffsetDate.valueOf() + 60000 * preOffsetDate.getTimezoneOffset());
2219 item[itemAttr] = XT.formatDate(offsetDate).formatdate;
2222 item[itemAttr] = XT.formatCost(item[itemAttr]).formatcost.toString();
2225 item[itemAttr] = XT.formatNumeric(item[itemAttr], "").formatnumeric.toString();
2228 item[itemAttr] = XT.formatMoney(item[itemAttr]).formatmoney.toString();
2231 item[itemAttr] = XT.formatSalesPrice(item[itemAttr]).formatsalesprice.toString();
2233 case "PurchasePrice":
2234 item[itemAttr] = XT.formatPurchPrice(item[itemAttr]).formatpurchprice.toString();
2236 case "ExtendedPrice":
2237 item[itemAttr] = XT.formatExtPrice(item[itemAttr]).formatextprice.toString();
2240 item[itemAttr] = XT.formatQty(item[itemAttr]).formatqty.toString();
2243 item[itemAttr] = XT.formatQtyPer(item[itemAttr]).formatqtyper.toString();
2245 case "UnitRatioScale":
2246 item[itemAttr] = XT.formatRatio(item[itemAttr]).formatratio.toString();
2249 item[itemAttr] = XT.formatPrcnt(item[itemAttr]).formatprcnt.toString();
2252 item[itemAttr] = XT.formatWeight(item[itemAttr]).formatweight.toString();
2255 item[itemAttr] = (item[itemAttr] || "").toString();
2259 /* Handle composite types */
2260 if (prop.toOne && prop.toOne.isNested && item[prop.name]) {
2261 this.sanitize(nameSpace, prop.toOne.type, item[prop.name], options);
2262 } else if (prop.toMany && prop.toMany.isNested && item[prop.name]) {
2263 for (var n = 0; n < item[prop.name].length; n++) {
2264 val = item[prop.name][n];
2266 /* Remove foreign key if applicable */
2267 if (!inclKeys) { delete val[prop.toMany.inverse]; }
2268 this.sanitize(nameSpace, prop.toMany.type, val, options);
2276 * Returns a array of key value pairs of metric settings that correspond with an array of passed keys.
2278 * @param {Array} array of metric names
2281 retrieveMetrics: function (keys) {
2286 sql = 'select metric_name as setting, metric_value as value '
2288 + 'where metric_name in ({literals})';
2290 for (var i = 0; i < keys.length; i++) {
2291 literals[i] = "%" + (i + 1) + "$L";
2294 sql = sql.replace(/{literals}/, literals.join(','));
2295 sql = XT.format(sql, keys)
2298 XT.debug('retrieveMetrics sql = ', sql);
2300 qry = plv8.execute(sql);
2302 /* Recast where applicable. */
2303 for (var i = 0; i < qry.length; i++) {
2304 prop = qry[i].setting;
2305 if(qry[i].value === 't') { ret[prop] = true; }
2306 else if(qry[i].value === 'f') { ret[prop] = false }
2307 else if(!isNaN(qry[i].value)) { ret[prop] = qry[i].value - 0; }
2308 else { ret[prop] = qry[i].value; }
2311 /* Make sure there is a result at all times */
2312 keys.forEach(function (key) {
2313 if (ret[key] === undefined) { ret[key] = null; }
2320 * Creates and returns a lock for a given table. Defaults to a time based lock of 30 seconds
2321 * unless aternate timeout option or process id (pid) is passed. If a pid is passed, the lock
2322 * is considered infinite as long as the pid is valid. If a previous lock key is passed and it is
2323 * valid, a new lock will be granted.
2325 * @param {String | Number} Table name or oid
2326 * @param {Number} Record id
2327 * @param {Object} Options
2328 * @param {Number} [options.timeout=30]
2329 * @param {Number} [options.pid] Process id
2330 * @param {Number} [options.key] Key
2331 * @param {Boolean} [options.obtainLock=true] If false, only checks for existing lock
2333 tryLock: function (table, id, options) {
2334 options = options ? options : {};
2336 var deleteSql = "delete from xt.lock where lock_id = $1;",
2337 timeout = options.timeout || 30,
2338 expires = new Date(),
2340 insertSqlExp = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_expires) " +
2341 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2342 insertSqlPid = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_pid) " +
2343 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2348 pid = options.pid || null,
2349 pidSql = "select usename, procpid " +
2350 "from pg_stat_activity " +
2351 "where datname=current_database() " +
2352 " and usename=$1 " +
2355 selectSql = "select * " +
2357 "where lock_table_oid = $1 " +
2358 " and lock_record_id = $2;",
2359 username = XT.username;
2361 /* If passed a table name, look up the oid. */
2362 oid = typeof table === "string" ? this.getTableOid(table) : table;
2364 if (DEBUG) XT.debug("Trying lock table", [oid, id]);
2366 /* See if there are existing lock(s) for this record. */
2368 XT.debug('tryLock sql = ', selectSql);
2369 XT.debug('tryLock values = ', [oid, id]);
2371 query = plv8.execute(selectSql, [oid, id]);
2373 /* Validate result */
2374 if (query.length > 0) {
2375 while (query.length) {
2376 lock = query.shift();
2378 /* See if we are confirming our own lock. */
2379 if (options.key && options.key === lock.lock_id) {
2380 /* Go on and we'll get a new lock. */
2382 /* Make sure if they are pid locks users is still connected. */
2383 } else if (lock.lock_pid) {
2385 XT.debug('tryLock sql = ', pidSql);
2386 XT.debug('tryLock values = ', [lock.lock_username, lock.lock_pid]);
2388 pcheck = plv8.execute(pidSql, [lock.lock_username, lock.lock_pid]);
2389 if (pcheck.length) { break; } /* valid lock */
2391 lockExp = new Date(lock.lock_expires);
2392 if (DEBUG) { XT.debug("Lock found", [lockExp > expires, lockExp, expires]); }
2393 if (lockExp > expires) { break; } /* valid lock */
2396 /* Delete invalid or expired lock. */
2398 XT.debug('tryLock sql = ', deleteSql);
2399 XT.debug('tryLock values = ', [lock.lock_id]);
2401 plv8.execute(deleteSql, [lock.lock_id]);
2406 if (DEBUG) XT.debug("Lock found", lock.lock_username);
2409 username: lock.lock_username,
2410 effective: lock.lock_effective
2415 if (options.obtainLock === false) { return; }
2417 if (DEBUG) { XT.debug("Creating lock."); }
2418 if (DEBUG) { XT.debug('tryLock sql = ', insertSqlPid); }
2421 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, pid]); }
2422 lock = plv8.execute(insertSqlPid, [oid, id, username, pid])[0];
2424 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2425 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, expires]); }
2426 lock = plv8.execute(insertSqlExp, [oid, id, username, expires])[0];
2429 if (DEBUG) { XT.debug("Lock returned is", lock.lock_id); }
2433 effective: lock.lock_effective,
2439 * Release a lock. Pass either options with a key, or table, id and username.
2441 * @param {Object} Options: key or table and id
2443 releaseLock: function (options) {
2445 sqlKey = 'delete from xt.lock where lock_id = $1;',
2446 sqlUsr = 'delete from xt.lock where lock_table_oid = $1 and lock_record_id = $2 and lock_username = $3;',
2447 username = XT.username;
2451 XT.debug('releaseLock sql = ', sqlKey);
2452 XT.debug('releaseLock values = ', [options.key]);
2454 plv8.execute(sqlKey, [options.key]);
2456 oid = typeof options.table === "string" ? this.getTableOid(options.table) : options.table;
2459 XT.debug('releaseLock sql = ', sqlUsr);
2460 XT.debug('releaseLock values = ', [oid, options.id, username]);
2462 plv8.execute(sqlUsr, [oid, options.id, username]);
2468 /* This deprecated function is still used by three dispatch functions. We should delete
2469 this as soon as we refactor those, and then rename buildClauseOptimized to buildClause */
2470 buildClause: function (nameSpace, type, parameters, orderBy) {
2471 parameters = parameters || [];
2473 var arrayIdentifiers = [],
2484 orderByIdentifiers = [],
2486 orm = this.fetchOrm(nameSpace, type),
2492 privileges = orm.privileges,
2496 ret.conditions = "";
2497 ret.parameters = [];
2499 /* Handle privileges. */
2500 if (orm.isNestedOnly) { plv8.elog(ERROR, 'Access Denied'); }
2504 (!this.checkPrivilege(privileges.all.read) &&
2505 !this.checkPrivilege(privileges.all.update)))
2507 privileges.personal &&
2508 (this.checkPrivilege(privileges.personal.read) ||
2509 this.checkPrivilege(privileges.personal.update))
2513 attribute: privileges.personal.properties,
2515 isUsernamePrivFilter: true,
2520 /* Handle parameters. */
2521 if (parameters.length) {
2522 for (var i = 0; i < parameters.length; i++) {
2524 param = parameters[i];
2525 op = param.operator || '=';
2545 for (var c = 0; c < param.value.length; c++) {
2546 ret.parameters.push(param.value[c]);
2547 param.value[c] = '$' + count;
2553 for (var c = 0; c < param.value.length; c++) {
2554 ret.parameters.push(param.value[c]);
2555 param.value[c] = '$' + count;
2560 plv8.elog(ERROR, 'Invalid operator: ' + op);
2563 /* Handle characteristics. This is very specific to xTuple,
2564 and highly dependant on certain table structures and naming conventions,
2565 but otherwise way too much work to refactor in an abstract manner right now. */
2566 if (param.isCharacteristic) {
2569 param.value = ' ARRAY[' + param.value.join(',') + ']';
2572 /* Booleans are stored as strings. */
2573 if (param.value === true) {
2575 } else if (param.value === false) {
2579 /* Yeah, it depends on a property called 'characteristics'... */
2580 prop = XT.Orm.getProperty(orm, 'characteristics');
2582 /* Build the characteristics query clause. */
2583 identifiers.push(prop.toMany.inverse);
2584 identifiers.push(orm.nameSpace.toLowerCase());
2585 identifiers.push(prop.toMany.type.decamelize());
2586 identifiers.push(param.attribute);
2587 identifiers.push(param.value);
2589 charSql = 'id in (' +
2590 ' select %' + (identifiers.length - 4) + '$I '+
2591 ' from %' + (identifiers.length - 3) + '$I.%' + (identifiers.length - 2) + '$I ' +
2592 ' join char on (char_name = characteristic)' +
2594 /* Note: Not using $i for these. L = literal here. These is not identifiers. */
2595 ' and char_name = %' + (identifiers.length - 1) + '$L ' +
2596 ' and value ' + op + ' %' + (identifiers.length) + '$L ' +
2599 clauses.push(charSql);
2601 /* Array comparisons handle another way. e.g. %1$I !<@ ARRAY[$1,$2] */
2602 } else if (op === '<@' || op === '!<@') {
2603 /* Handle paths if applicable. */
2604 if (param.attribute.indexOf('.') > -1) {
2605 parts = param.attribute.split('.');
2606 childOrm = this.fetchOrm(nameSpace, type);
2608 pcount = params.length - 1;
2610 for (var n = 0; n < parts.length; n++) {
2611 /* Validate attribute. */
2612 prop = XT.Orm.getProperty(childOrm, parts[n]);
2614 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
2617 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2618 identifiers.push(parts[n]);
2619 params[pcount] += "%" + identifiers.length + "$I";
2620 if (n < parts.length - 1) {
2621 params[pcount] = "(" + params[pcount] + ").";
2622 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2624 params[pcount] += op + ' ARRAY[' + param.value.join(',') + ']';
2628 identifiers.push(param.attribute);
2629 params.push("%" + identifiers.length + "$I " + op + ' ARRAY[' + param.value.join(',') + ']');
2630 pcount = params.length - 1;
2632 clauses.push(params[pcount]);
2634 /* Everything else handle another. */
2636 if (XT.typeOf(param.attribute) !== 'array') {
2637 param.attribute = [param.attribute];
2640 for (var c = 0; c < param.attribute.length; c++) {
2641 /* Handle paths if applicable. */
2642 if (param.attribute[c].indexOf('.') > -1) {
2643 parts = param.attribute[c].split('.');
2644 childOrm = this.fetchOrm(nameSpace, type);
2646 pcount = params.length - 1;
2649 /* Check if last part is an Array. */
2650 for (var m = 0; m < parts.length; m++) {
2651 /* Validate attribute. */
2652 prop = XT.Orm.getProperty(childOrm, parts[m]);
2654 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[m]);
2657 if (m < parts.length - 1) {
2658 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2659 } else if (prop.attr && prop.attr.type === 'Array') {
2660 /* The last property in the path is an array. */
2662 params[pcount] = '$' + count;
2666 /* Reset the childOrm to parent. */
2667 childOrm = this.fetchOrm(nameSpace, type);
2669 for (var n = 0; n < parts.length; n++) {
2670 /* Validate attribute. */
2671 prop = XT.Orm.getProperty(childOrm, parts[n]);
2673 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
2676 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
2677 if (param.isUsernamePrivFilter && isArray) {
2678 identifiers.push(parts[n]);
2679 arrayIdentifiers.push(identifiers.length);
2681 if (n < parts.length - 1) {
2682 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2685 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2686 identifiers.push(parts[n]);
2687 params[pcount] += "%" + identifiers.length + "$I";
2689 if (n < parts.length - 1) {
2690 params[pcount] = "(" + params[pcount] + ").";
2691 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2692 } else if (param.isLower) {
2693 params[pcount] = "lower(" + params[pcount] + ")";
2698 /* Validate attribute. */
2699 prop = XT.Orm.getProperty(orm, param.attribute[c]);
2701 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute[c]);
2704 identifiers.push(param.attribute[c]);
2706 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
2707 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested) ||
2708 (prop.attr && prop.attr.type === 'Array'))) {
2710 params.push('$' + count);
2711 pcount = params.length - 1;
2712 arrayIdentifiers.push(identifiers.length);
2714 params.push("%" + identifiers.length + "$I");
2715 pcount = params.length - 1;
2719 /* Add persional privs array search. */
2720 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested)
2721 || (prop.attr && prop.attr.type === 'Array') || isArray)) {
2723 /* e.g. 'admin' = ANY (usernames_array) */
2725 params[pcount] += ' ' + op + ' ANY (';
2727 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2728 for (var f =0; f < arrayIdentifiers.length; f++) {
2729 arrayParams += '%' + arrayIdentifiers[f] + '$I';
2730 if (f < arrayIdentifiers.length - 1) {
2731 arrayParams = "(" + arrayParams + ").";
2734 params[pcount] += arrayParams + ')';
2736 /* Add optional is null clause. */
2737 } else if (parameters[i].includeNull) {
2738 /* e.g. %1$I = $1 or %1$I is null */
2739 params[pcount] = params[pcount] + " " + op + ' $' + count + ' or ' + params[pcount] + ' is null';
2741 /* e.g. %1$I = $1 */
2742 params[pcount] += " " + op + ' $' + count;
2745 orClause.push(params[pcount]);
2748 /* If more than one clause we'll get: (%1$I = $1 or %1$I = $2 or %1$I = $3) */
2749 clauses.push('(' + orClause.join(' or ') + ')');
2751 ret.parameters.push(param.value);
2756 ret.conditions = (clauses.length ? '(' + XT.format(clauses.join(' and '), identifiers) + ')' : ret.conditions) || true;
2758 /* Massage ordeBy with quoted identifiers. */
2760 for (var i = 0; i < orderBy.length; i++) {
2761 /* Handle path case. */
2762 if (orderBy[i].attribute.indexOf('.') > -1) {
2763 parts = orderBy[i].attribute.split('.');
2765 orderByParams.push("");
2766 pcount = orderByParams.length - 1;
2768 for (var n = 0; n < parts.length; n++) {
2769 prop = XT.Orm.getProperty(orm, parts[n]);
2771 plv8.elog(ERROR, 'Attribute not found in map: ' + parts[n]);
2773 orderByIdentifiers.push(parts[n]);
2774 orderByParams[pcount] += "%" + orderByIdentifiers.length + "$I";
2776 if (n < parts.length - 1) {
2777 orderByParams[pcount] = "(" + orderByParams[pcount] + ").";
2778 orm = this.fetchOrm(nameSpace, prop.toOne.type);
2784 prop = XT.Orm.getProperty(orm, orderBy[i].attribute);
2786 plv8.elog(ERROR, 'Attribute not found in map: ' + orderBy[i].attribute);
2788 orderByIdentifiers.push(orderBy[i].attribute);
2789 orderByParams.push("%" + orderByIdentifiers.length + "$I");
2790 pcount = orderByParams.length - 1;
2793 if (orderBy[i].isEmpty) {
2794 orderByParams[pcount] = "length(" + orderByParams[pcount] + ")=0";
2796 if (orderBy[i].descending) {
2797 orderByParams[pcount] += " desc";
2800 list.push(orderByParams[pcount])
2804 ret.orderBy = list.length ? XT.format('order by ' + list.join(','), orderByIdentifiers) : '';
2809 * Renew a lock. Defaults to rewing the lock for 30 seconds.
2811 * @param {Number} Key
2812 * @params {Object} Options: timeout
2813 * @returns {Date} New expiration or false.
2815 renewLock: function (key, options) {
2816 var expires = new Date(),
2818 selectSql = "select * from xt.lock where lock_id = $1;",
2819 timeout = options && options.timeout ? options.timeout : 30,
2820 updateSql = "update xt.lock set lock_expires = $1 where lock_id = $2;";
2822 if (typeof key !== "number") { return false; }
2823 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2826 XT.debug('renewLock sql = ', selectSql);
2827 XT.debug('renewLock values = ', [key]);
2829 query = plv8.execute(selectSql, [key]);
2833 XT.debug('renewLock sql = ', updateSql);
2834 XT.debug('renewLock values = ', [expires, key]);
2836 plv8.execute(updateSql, [expires, key]);