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 buildClause: 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 rootAttribute = (attribute.indexOf('.') < 0) ? attribute : attribute.split(".")[0],
106 prop = XT.Orm.getProperty(orm, rootAttribute),
107 propName = prop.name,
111 walkPath = function (pathParts, currentOrm, pathIndex) {
112 var currentAttributeIsString = typeof pathParts[pathIndex] === 'string',
113 currentProp = XT.Orm.getProperty(currentOrm, pathParts[pathIndex]),
117 if ((currentProp.toOne || currentProp.toMany)) {
118 if (currentProp.toOne && currentProp.toOne.type) {
119 subChildOrm = that.fetchOrm(nameSpace, currentProp.toOne.type);
120 } else if (currentProp.toMany && currentProp.toMany.type) {
121 subChildOrm = that.fetchOrm(nameSpace, currentProp.toMany.type);
123 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + currentProp.name);
126 if (pathIndex < pathParts.length - 1) {
128 walkPath(pathParts, subChildOrm, pathIndex + 1);
130 /* This is the end of the path. */
131 naturalKey = XT.Orm.naturalKey(subChildOrm);
132 if (currentAttributeIsString) {
133 /* add the natural key to the end of the requested attribute */
134 parameter.attribute = attribute + "." + naturalKey;
136 /* swap out the attribute in the array for the one with the prepended natural key */
137 index = parameter.attribute.indexOf(attribute);
138 parameter.attribute.splice(index, 1);
139 parameter.attribute.splice(index, 0, attribute + "." + naturalKey);
145 if ((prop.toOne || prop.toMany)) {
146 /* Someone is querying on a toOne without using a path */
147 if (prop.toOne && prop.toOne.type) {
148 childOrm = that.fetchOrm(nameSpace, prop.toOne.type);
149 } else if (prop.toMany && prop.toMany.type) {
150 childOrm = that.fetchOrm(nameSpace, prop.toMany.type);
152 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + prop.name);
155 if (attribute.indexOf('.') < 0) {
156 naturalKey = XT.Orm.naturalKey(childOrm);
157 if (attributeIsString) {
158 /* add the natural key to the end of the requested attribute */
159 parameter.attribute = attribute + "." + naturalKey;
161 /* swap out the attribute in the array for the one with the prepended natural key */
162 index = parameter.attribute.indexOf(attribute);
163 parameter.attribute.splice(index, 1);
164 parameter.attribute.splice(index, 0, attribute + "." + naturalKey);
167 /* Even if there's a path x.y, it's possible that it's still not
168 correct because the correct path maybe is x.y.naturalKeyOfY */
169 walkPath(attribute.split("."), orm, 0);
175 /* Handle parameters. */
176 if (parameters.length) {
177 for (var i = 0; i < parameters.length; i++) {
179 param = parameters[i];
180 op = param.operator || '=';
200 for (var c = 0; c < param.value.length; c++) {
201 ret.parameters.push(param.value[c]);
202 param.value[c] = '$' + count;
208 for (var c = 0; c < param.value.length; c++) {
209 ret.parameters.push(param.value[c]);
210 param.value[c] = '$' + count;
215 plv8.elog(ERROR, 'Invalid operator: ' + op);
218 /* Handle characteristics. This is very specific to xTuple,
219 and highly dependant on certain table structures and naming conventions,
220 but otherwise way too much work to refactor in an abstract manner right now. */
221 if (param.isCharacteristic) {
224 param.value = ' ARRAY[' + param.value.join(',') + ']';
227 /* Booleans are stored as strings. */
228 if (param.value === true) {
230 } else if (param.value === false) {
234 /* Yeah, it depends on a property called 'characteristics'... */
235 prop = XT.Orm.getProperty(orm, 'characteristics');
237 /* Build the characteristics query clause. */
238 identifiers.push(XT.Orm.primaryKey(orm, true));
239 identifiers.push(prop.toMany.inverse);
240 identifiers.push(orm.nameSpace.toLowerCase());
241 identifiers.push(prop.toMany.type.decamelize());
242 identifiers.push(param.attribute);
243 identifiers.push(param.value);
245 charSql = '%' + (identifiers.length - 5) + '$I in (' +
246 ' select %' + (identifiers.length - 4) + '$I '+
247 ' from %' + (identifiers.length - 3) + '$I.%' + (identifiers.length - 2) + '$I ' +
248 ' join char on (char_name = characteristic)' +
250 /* Note: Not using $i for these. L = literal here. These is not identifiers. */
251 ' and char_name = %' + (identifiers.length - 1) + '$L ' +
252 ' and value ' + op + ' %' + (identifiers.length) + '$L ' +
255 clauses.push(charSql);
257 /* Array comparisons handle another way. e.g. %1$I !<@ ARRAY[$1,$2] */
258 } else if (op === '<@' || op === '!<@') {
259 /* Handle paths if applicable. */
260 if (param.attribute.indexOf('.') > -1) {
261 parts = param.attribute.split('.');
262 childOrm = this.fetchOrm(nameSpace, type);
264 pcount = params.length - 1;
266 for (var n = 0; n < parts.length; n++) {
267 /* Validate attribute. */
268 prop = XT.Orm.getProperty(childOrm, parts[n]);
270 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
274 if (n === parts.length - 1) {
275 identifiers.push("jt" + (joins.length - 1));
276 identifiers.push(prop.attr.column);
277 pgType = this.getPgTypeFromOrmType(
278 this.getNamespaceFromNamespacedTable(childOrm.table),
279 this.getTableFromNamespacedTable(childOrm.table),
282 pgType = pgType ? "::" + pgType + "[]" : '';
283 params[pcount] += "%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I";
284 params[pcount] += ' ' + op + ' ARRAY[' + param.value.join(',') + ']' + pgType;
286 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
287 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
288 joinIdentifiers.push(
289 this.getNamespaceFromNamespacedTable(childOrm.table),
290 this.getTableFromNamespacedTable(childOrm.table),
291 sourceTableAlias, prop.toOne.column,
292 XT.Orm.primaryKey(childOrm, true));
293 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
294 + "$I jt" + joins.length + " on %"
295 + (joinIdentifiers.length - 2) + "$I.%"
296 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
300 prop = XT.Orm.getProperty(orm, param.attribute);
301 pertinentExtension = XT.Orm.getProperty(orm, param.attribute, true);
302 if(pertinentExtension.isChild || pertinentExtension.isExtension) {
303 /* We'll need to join this orm extension */
304 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
305 joinIdentifiers.push(
306 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
307 this.getTableFromNamespacedTable(pertinentExtension.table),
308 fromKeyProp.attr.column,
309 pertinentExtension.relations[0].column);
310 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
311 + "$I jt" + joins.length + " on t1.%"
312 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
315 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute);
318 identifiers.push(pertinentExtension.isChild || pertinentExtension.isExtension ?
319 "jt" + (joins.length - 1) :
321 identifiers.push(prop.attr.column);
322 pgType = this.getPgTypeFromOrmType(
323 this.getNamespaceFromNamespacedTable(orm.table),
324 this.getTableFromNamespacedTable(orm.table),
327 pgType = pgType ? "::" + pgType + "[]" : '';
328 params.push("%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I " + op + ' ARRAY[' + param.value.join(',') + ']' + pgType);
329 pcount = params.length - 1;
331 clauses.push(params[pcount]);
333 /* Everything else handle another. */
335 if (XT.typeOf(param.attribute) !== 'array') {
336 param.attribute = [param.attribute];
339 for (var c = 0; c < param.attribute.length; c++) {
340 /* Handle paths if applicable. */
341 if (param.attribute[c].indexOf('.') > -1) {
342 parts = param.attribute[c].split('.');
343 childOrm = this.fetchOrm(nameSpace, type);
345 pcount = params.length - 1;
348 /* Check if last part is an Array. */
349 for (var m = 0; m < parts.length; m++) {
350 /* Validate attribute. */
351 prop = XT.Orm.getProperty(childOrm, parts[m]);
353 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[m]);
356 if (m < parts.length - 1) {
357 if (prop.toOne && prop.toOne.type) {
358 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
359 } else if (prop.toMany && prop.toMany.type) {
360 childOrm = this.fetchOrm(nameSpace, prop.toMany.type);
362 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + prop.name);
364 } else if (prop.attr && prop.attr.type === 'Array') {
365 /* The last property in the path is an array. */
367 params[pcount] = '$' + count;
371 /* Reset the childOrm to parent. */
372 childOrm = this.fetchOrm(nameSpace, type);
374 for (var n = 0; n < parts.length; n++) {
375 /* Validate attribute. */
376 prop = XT.Orm.getProperty(childOrm, parts[n]);
378 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
381 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
382 if (param.isUsernamePrivFilter && isArray) {
383 identifiers.push(prop.attr.column);
384 arrayIdentifiers.push(identifiers.length);
386 if (n < parts.length - 1) {
387 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
390 pertinentExtension = XT.Orm.getProperty(childOrm, parts[n], true);
391 var isExtension = pertinentExtension.isChild || pertinentExtension.isExtension;
393 /* We'll need to join this orm extension */
394 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
395 joinIdentifiers.push(
396 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
397 this.getTableFromNamespacedTable(pertinentExtension.table),
398 fromKeyProp.attr.column,
399 pertinentExtension.relations[0].column);
400 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
401 + "$I jt" + joins.length + " on t1.%"
402 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
404 /* Build path, e.g. table_name.column_name */
405 if (n === parts.length - 1) {
406 identifiers.push("jt" + (joins.length - 1));
407 identifiers.push(prop.attr.column);
408 params[pcount] += "%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I";
410 params[pcount] = "lower(" + params[pcount] + ")";
413 sourceTableAlias = n === 0 && !isExtension ? "t1" : "jt" + (joins.length - 1);
414 if (prop.toOne && prop.toOne.type) {
415 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
416 joinIdentifiers.push(
417 this.getNamespaceFromNamespacedTable(childOrm.table),
418 this.getTableFromNamespacedTable(childOrm.table),
419 sourceTableAlias, prop.toOne.column,
420 XT.Orm.primaryKey(childOrm, true)
422 } else if (prop.toMany && prop.toMany.type) {
423 childOrm = this.fetchOrm(nameSpace, prop.toMany.type);
424 joinIdentifiers.push(
425 this.getNamespaceFromNamespacedTable(childOrm.table),
426 this.getTableFromNamespacedTable(childOrm.table),
427 sourceTableAlias, prop.toMany.column,
428 XT.Orm.primaryKey(childOrm, true)
431 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
432 + "$I jt" + joins.length + " on %"
433 + (joinIdentifiers.length - 2) + "$I.%"
434 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
439 /* Validate attribute. */
440 prop = XT.Orm.getProperty(orm, param.attribute[c]);
441 pertinentExtension = XT.Orm.getProperty(orm, param.attribute[c], true);
442 if(pertinentExtension.isChild || pertinentExtension.isExtension) {
443 /* We'll need to join this orm extension */
444 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
445 joinIdentifiers.push(
446 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
447 this.getTableFromNamespacedTable(pertinentExtension.table),
448 fromKeyProp.attr.column,
449 pertinentExtension.relations[0].column);
450 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
451 + "$I jt" + joins.length + " on t1.%"
452 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
455 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute[c]);
458 identifiers.push(pertinentExtension.isChild || pertinentExtension.isExtension ?
459 "jt" + (joins.length - 1) :
461 identifiers.push(prop.attr.column);
463 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
464 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested) ||
465 (prop.attr && prop.attr.type === 'Array'))) {
467 params.push('$' + count);
468 pcount = params.length - 1;
469 arrayIdentifiers.push(identifiers.length);
471 params.push("%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I");
472 pcount = params.length - 1;
476 /* Add persional privs array search. */
477 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested)
478 || (prop.attr && prop.attr.type === 'Array') || isArray)) {
480 /* XXX: this bit of code has not been touched by the optimization refactor */
481 /* e.g. 'admin' = ANY (usernames_array) */
483 params[pcount] += ' ' + op + ' ANY (';
485 /* Build path. e.g. ((%1$I).%2$I).%3$I */
486 for (var f =0; f < arrayIdentifiers.length; f++) {
487 arrayParams += '%' + arrayIdentifiers[f] + '$I';
488 if (f < arrayIdentifiers.length - 1) {
489 arrayParams = "(" + arrayParams + ").";
492 params[pcount] += arrayParams + ')';
494 /* Add optional is null clause. */
495 } else if (parameters[i].includeNull) {
496 /* e.g. %1$I = $1 or %1$I is null */
497 params[pcount] = params[pcount] + " " + op + ' $' + count + ' or ' + params[pcount] + ' is null';
500 params[pcount] += " " + op + ' $' + count;
503 orClause.push(params[pcount]);
506 /* If more than one clause we'll get: (%1$I = $1 or %1$I = $2 or %1$I = $3) */
507 clauses.push('(' + orClause.join(' or ') + ')');
509 ret.parameters.push(param.value);
514 ret.conditions = (clauses.length ? '(' + XT.format(clauses.join(' and '), identifiers) + ')' : ret.conditions) || true;
516 /* Massage orderBy with quoted identifiers. */
517 /* We need to support the xm case for sql2 and the xt/public (column) optimized case for sql1 */
518 /* In practice we build the two lists independently of one another */
520 for (var i = 0; i < orderBy.length; i++) {
521 /* Handle path case. */
522 if (orderBy[i].attribute.indexOf('.') > -1) {
523 parts = orderBy[i].attribute.split('.');
525 orderByParams.push("");
526 orderByColumnParams.push("");
527 groupByColumnParams.push("");
528 pcount = orderByParams.length - 1;
530 for (var n = 0; n < parts.length; n++) {
531 prop = XT.Orm.getProperty(orm, parts[n]);
533 plv8.elog(ERROR, 'Attribute not found in map: ' + parts[n]);
535 orderByIdentifiers.push(parts[n]);
536 orderByParams[pcount] += "%" + orderByIdentifiers.length + "$I";
538 if (n === parts.length - 1) {
539 orderByColumnIdentifiers.push("jt" + (joins.length - 1));
540 orderByColumnIdentifiers.push(prop.attr.column);
541 orderByColumnParams[pcount] += "%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I"
542 groupByColumnParams[pcount] += "%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I"
544 orderByParams[pcount] = "(" + orderByParams[pcount] + ").";
545 orm = this.fetchOrm(nameSpace, prop.toOne.type);
546 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
547 joinIdentifiers.push(
548 this.getNamespaceFromNamespacedTable(orm.table),
549 this.getTableFromNamespacedTable(orm.table),
550 sourceTableAlias, prop.toOne.column,
551 XT.Orm.primaryKey(orm, true));
552 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
553 + "$I jt" + joins.length + " on %"
554 + (joinIdentifiers.length - 2) + "$I.%"
555 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
561 prop = XT.Orm.getProperty(orm, orderBy[i].attribute);
563 plv8.elog(ERROR, 'Attribute not found in map: ' + orderBy[i].attribute);
565 orderByIdentifiers.push(orderBy[i].attribute);
566 orderByColumnIdentifiers.push("t1");
568 We might need to look at toOne if the client is asking for a toOne without specifying
569 the path. Unfortunately, if they do specify the path, then sql2 will fail. So this does
570 work, although we're really sorting by the primary key of the toOne, whereas the
571 user probably wants us to sort by the natural key TODO
573 orderByColumnIdentifiers.push(prop.attr ? prop.attr.column : prop.toOne.column);
574 orderByParams.push("%" + orderByIdentifiers.length + "$I");
575 orderByColumnParams.push("%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I");
576 groupByColumnParams.push("%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I");
577 pcount = orderByParams.length - 1;
580 if (orderBy[i].isEmpty) {
581 orderByParams[pcount] = "length(" + orderByParams[pcount] + ")=0";
582 orderByColumnParams[pcount] = "length(" + orderByColumnParams[pcount] + ")=0";
584 if (orderBy[i].descending) {
585 orderByParams[pcount] += " desc";
586 orderByColumnParams[pcount] += " desc";
589 orderByList.push(orderByParams[pcount])
590 orderByColumnList.push(orderByColumnParams[pcount])
594 ret.orderBy = orderByList.length ? XT.format('order by ' + orderByList.join(','), orderByIdentifiers) : '';
595 ret.orderByColumns = orderByColumnList.length ? XT.format('order by ' + orderByColumnList.join(','), orderByColumnIdentifiers) : '';
596 ret.groupByColumns = groupByColumnParams.length ? XT.format(', ' + groupByColumnParams.join(','), orderByColumnIdentifiers) : '';
597 ret.joins = joins.length ? XT.format(joins.join(' '), joinIdentifiers) : '';
603 * Queries whether the current user has been granted the privilege passed.
605 * @param {String} privilege
608 checkPrivilege: function (privilege) {
615 if (typeof privilege === 'string') {
616 if (!this._granted) { this._granted = {}; }
617 if (!this._granted[XT.username]) { this._granted[XT.username] = {}; }
618 if (this._granted[XT.username][privilege] !== undefined) { return this._granted[XT.username][privilege]; }
620 /* The privilege name is allowed to be a set of space-delimited privileges */
621 /* If a user has any of the applicable privileges then they get access */
622 privArray = privilege.split(" ");
623 sql = 'select coalesce(usrpriv_priv_id, grppriv_priv_id, -1) > 0 as granted ' +
625 'left join usrpriv on (priv_id=usrpriv_priv_id) and (usrpriv_username=$1) ' +
627 ' select distinct grppriv_priv_id ' +
629 ' join usrgrp on (grppriv_grp_id=usrgrp_grp_id) and (usrgrp_username=$1) ' +
630 ' ) grppriv on (grppriv_priv_id=priv_id) ' +
631 'where priv_name = $2';
633 for (var i = 1; i < privArray.length; i++) {
634 sql = sql + ' or priv_name = $' + (i + 2);
636 sql = sql + "order by granted desc limit 1;";
638 /* Cleverness: the query parameters are just the priv array with the username tacked on front. */
639 privArray.unshift(XT.username);
642 XT.debug('checkPrivilege sql =', sql);
643 XT.debug('checkPrivilege values =', privArray);
645 res = plv8.execute(sql, privArray);
646 ret = res.length ? res[0].granted : false;
649 this._granted[XT.username][privilege] = ret;
653 XT.debug('Privilege check for "' + XT.username + '" on "' + privilege + '" returns ' + ret);
660 * Validate whether user has read access to data. If a record is passed, check personal privileges of
663 * @param {String} name space
664 * @param {String} type name
665 * @param {Object} record - optional
666 * @param {Boolean} is top level, default is true
669 checkPrivileges: function (nameSpace, type, record, isTopLevel) {
670 isTopLevel = isTopLevel !== false ? true : false;
671 var action = record && record.dataState === this.CREATED_STATE ? 'create' :
672 record && record.dataState === this.DELETED_STATE ? 'delete' :
673 record && record.dataState === this.UPDATED_STATE ? 'update' : 'read',
674 committing = record ? record.dataState !== this.READ_STATE : false,
676 isGrantedPersonal = false,
677 map = this.fetchOrm(nameSpace, type),
678 privileges = map.privileges,
682 /* If there is no ORM, this isn't a table data type so no check required. */
685 XT.debug('orm type is ->', map.type);
686 XT.debug('orm is ->', map);
689 if (!map) { return true; }
691 /* Can not access 'nested only' records directly. */
693 XT.debug('is top level ->', isTopLevel);
694 XT.debug('is nested ->', map.isNestedOnly);
696 if (isTopLevel && map.isNestedOnly) { return false; }
698 /* Check privileges - first do we have access to anything? */
700 if (DEBUG) { XT.debug('privileges found', privileges); }
702 if (DEBUG) { XT.debug('is committing'); }
704 /* Check if user has 'all' read privileges. */
705 isGrantedAll = privileges.all ? this.checkPrivilege(privileges.all[action]) : false;
707 /* Otherwise check for 'personal' read privileges. */
709 isGrantedPersonal = privileges.personal ?
710 this.checkPrivilege(privileges.personal[action]) : false;
713 if (DEBUG) { XT.debug('is NOT committing'); }
715 /* Check if user has 'all' read privileges. */
716 isGrantedAll = privileges.all ?
717 this.checkPrivilege(privileges.all.read) ||
718 this.checkPrivilege(privileges.all.update) : false;
720 /* Otherwise check for 'personal' read privileges. */
722 isGrantedPersonal = privileges.personal ?
723 this.checkPrivilege(privileges.personal.read) ||
724 this.checkPrivilege(privileges.personal.update) : false;
729 /* If we're checknig an actual record and only have personal privileges, */
730 /* see if the record allows access. */
731 if (record && !isGrantedAll && isGrantedPersonal && action !== "create") {
732 if (DEBUG) { XT.debug('checking record level personal privileges'); }
735 /* Shared checker function that checks 'personal' properties for access rights. */
736 checkPersonal = function (record) {
739 props = privileges.personal.properties,
740 get = function (obj, target) {
743 parts = target.split("."),
746 for (var idx = 0; idx < parts.length; idx++) {
748 ret = ret ? ret[part] : obj[part];
749 if (ret === null || ret === undefined) {
757 while (!isGranted && i < props.length) {
759 personalUser = get(record, prop);
761 if (personalUser instanceof Array) {
762 for (var userIdx = 0; userIdx < personalUser.length; userIdx++) {
763 if (personalUser[userIdx].toLowerCase() === XT.username) {
767 } else if (personalUser) {
768 isGranted = personalUser.toLowerCase() === XT.username;
777 /* If committing we need to ensure the record in its previous state is editable by this user. */
778 if (committing && (action === 'update' || action === 'delete')) {
779 pkey = XT.Orm.naturalKey(map) || XT.Orm.primaryKey(map);
780 old = this.retrieveRecord({
781 nameSpace: nameSpace,
787 isGrantedPersonal = checkPersonal(old.data);
789 /* Otherwise check personal privileges on the record passed. */
790 } else if (action === 'read') {
791 isGrantedPersonal = checkPersonal(record);
796 XT.debug('is granted all ->', isGrantedAll);
797 XT.debug('is granted personal ->', isGrantedPersonal);
800 return isGrantedAll || isGrantedPersonal;
804 * Commit array columns with their own statements
806 * @param {Object} Orm
807 * @param {Object} Record
809 commitArrays: function (orm, record, encryptionKey) {
810 var pkey = XT.Orm.primaryKey(orm),
819 resolveKey = function (col) {
822 /* First search properties */
823 var ary = orm.properties.filter(function (prop) {
824 return prop.attr && prop.attr.column === col;
831 /* If not found must be extension, search relations */
832 if (orm.extensions.length) {
833 orm.extensions.forEach(function (ext) {
835 ary = ext.relations.filter(function (prop) {
836 return prop.column === col;
840 attr = ary[0].inverse;
846 if (attr) { return attr };
848 /* If still not found, we have a structural problem */
849 throw new Error("Can not resolve primary id on toMany relation");
852 for (prop in record) {
853 ormp = XT.Orm.getProperty(orm, prop);
855 /* If the property is an array of objects they must be records so commit them. */
856 if (ormp.toMany && ormp.toMany.isNested) {
857 fkey = ormp.toMany.inverse;
858 values = record[prop];
860 for (var i = 0; i < values.length; i++) {
863 /* Populate the parent key into the foreign key field if it's absent. */
865 columnToKey = ormp.toMany.column;
866 propToKey = columnToKey ? resolveKey(columnToKey) : pkey;
867 if (!record[propToKey]) {
868 /* If there's no data, we have a structural problem */
869 throw new Error("Can not resolve foreign key on toMany relation " + ormp.name);
871 val[fkey] = record[propToKey];
875 nameSpace: orm.nameSpace,
876 type: ormp.toMany.type,
878 encryptionKey: encryptionKey
886 * Commit metrics that have changed to the database.
888 * @param {Object} metrics
891 commitMetrics: function (metrics) {
893 sql = 'select setMetric($1,$2)',
896 for (key in metrics) {
897 value = metrics[key];
898 if (typeof value === 'boolean') {
899 value = value ? 't' : 'f';
900 } else if (typeof value === 'number') {
901 value = value.toString();
905 XT.debug('commitMetrics sql =', sql);
906 XT.debug('commitMetrics values =', [key, value]);
908 plv8.execute(sql, [key, value]);
915 * Commit a record to the database. The record must conform to the object hiearchy as defined by the
916 * record's `ORM` definition. Each object in the tree must include state information on a reserved property
917 * called `dataState`. Valid values are `create`, `update` and `delete`. Objects with other dataState values including
918 * `undefined` will be ignored. State values can be added using `XT.jsonpatch.updateState(obj, state)`.
920 * @seealso XT.jsonpatch.updateState
921 * @param {Object} Options
922 * @param {String} [options.nameSpace] Namespace. Required.
923 * @param {String} [options.type] Type. Required.
924 * @param {Object} [options.data] The data payload to be processed. Required
925 * @param {Number} [options.etag] Record version for optimistic locking.
926 * @param {Object} [options.lock] Lock information for pessemistic locking.
927 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
928 * @param {String} [options.encryptionKey] Encryption key.
930 commitRecord: function (options) {
931 var data = options.data,
932 dataState = data ? data.dataState : false,
933 hasAccess = options.superUser ||
934 this.checkPrivileges(options.nameSpace, options.type, data, false);
936 if (!hasAccess) { throw new Error("Access Denied."); }
939 case (this.CREATED_STATE):
940 this.createRecord(options);
942 case (this.UPDATED_STATE):
943 this.updateRecord(options);
945 case (this.DELETED_STATE):
946 this.deleteRecord(options);
951 * Commit insert to the database
953 * @param {Object} Options
954 * @param {String} [options.nameSpace] Namespace. Required.
955 * @param {String} [options.type] Type. Required.
956 * @param {Object} [options.data] The data payload to be processed. Required.
957 * @param {String} [options.encryptionKey] Encryption key.
959 createRecord: function (options) {
960 var data = options.data,
961 encryptionKey = options.encryptionKey,
963 orm = this.fetchOrm(options.nameSpace, options.type),
964 sql = this.prepareInsert(orm, data, null, encryptionKey),
965 pkey = XT.Orm.primaryKey(orm),
968 /* Handle extensions on the same table. */
969 for (var i = 0; i < orm.extensions.length; i++) {
970 if (orm.extensions[i].table === orm.table) {
971 sql = this.prepareInsert(orm.extensions[i], data, sql, encryptionKey);
975 /* Commit the base record. */
977 XT.debug('createRecord sql =', sql.statement);
978 XT.debug('createRecord values =', sql.values);
982 rec = plv8.execute(sql.statement, sql.values);
983 /* Make sure the primary key is populated */
985 data[pkey] = rec[0].id;
987 /* Make sure the obj_uuid is populated, if applicable */
988 if (!data.obj_uuid && rec[0] && rec[0].obj_uuid) {
989 data.uuid = rec[0].obj_uuid;
993 /* Handle extensions on other tables. */
994 for (var i = 0; i < orm.extensions.length; i++) {
995 if (orm.extensions[i].table !== orm.table &&
996 !orm.extensions[i].isChild) {
997 sql = this.prepareInsert(orm.extensions[i], data, null, encryptionKey);
1000 XT.debug('createRecord sql =', sql.statement);
1001 XT.debug('createRecord values =', sql.values);
1004 if (sql.statement) {
1005 plv8.execute(sql.statement, sql.values);
1010 /* Okay, now lets handle arrays. */
1011 this.commitArrays(orm, data, encryptionKey);
1015 * Use an orm object and a record and build an insert statement. It
1016 * returns an object with a table name string, columns array, expressions
1017 * array and insert statement string that can be executed.
1019 * The optional params object includes objects columns, expressions
1020 * that can be cumulatively added to the result.
1022 * @params {Object} Orm
1023 * @params {Object} Record
1024 * @params {Object} Params - optional
1025 * @params {String} Encryption Key
1028 prepareInsert: function (orm, record, params, encryptionKey) {
1030 attributePrivileges,
1041 pkey = XT.Orm.primaryKey(orm),
1044 sql = "select nextval($1) as id",
1050 isValidSql = params && params.statement ? true : false,
1053 params = params || {
1060 params.table = orm.table;
1061 count = params.values.length + 1;
1063 /* If no primary key, then create one. */
1064 if (!record[pkey] && orm.idSequenceName) {
1066 XT.debug('prepareInsert sql =', sql);
1067 XT.debug('prepareInsert values =', [orm.idSequenceName]);
1069 record[pkey] = plv8.execute(sql, [orm.idSequenceName])[0].id;
1072 /* If extension handle key. */
1073 if (orm.relations) {
1074 for (var i = 0; i < orm.relations.length; i++) {
1075 column = orm.relations[i].column;
1076 if (!params.identifiers.contains(column)) {
1077 params.columns.push("%" + count + "$I");
1078 params.values.push(record[orm.relations[i].inverse]);
1079 params.expressions.push('$' + count);
1080 params.identifiers.push(orm.relations[i].column);
1086 /* Build up the content for insert of this record. */
1087 for (var i = 0; i < orm.properties.length; i++) {
1088 ormp = orm.properties[i];
1091 if (ormp.toMany && ormp.toMany.column === 'obj_uuid') {
1092 params.parentUuid = true;
1095 attr = ormp.attr ? ormp.attr : ormp.toOne ? ormp.toOne : ormp.toMany;
1097 iorm = ormp.toOne ? this.fetchOrm(orm.nameSpace, ormp.toOne.type) : false,
1098 nkey = iorm ? XT.Orm.naturalKey(iorm, true) : false;
1099 val = ormp.toOne && record[prop] instanceof Object ?
1100 record[prop][nkey || ormp.toOne.inverse || 'id'] : record[prop];
1103 * Ignore derived fields for insert/update
1105 if (attr.derived) continue;
1107 attributePrivileges = orm.privileges &&
1108 orm.privileges.attribute &&
1109 orm.privileges.attribute[prop];
1111 if(!attributePrivileges || attributePrivileges.create === undefined) {
1113 } else if (typeof attributePrivileges.create === 'string') {
1114 canEdit = this.checkPrivilege(attributePrivileges.create);
1116 canEdit = attributePrivileges.create; /* if it's true or false */
1119 /* Handle fixed values. */
1120 if (attr.value !== undefined) {
1121 params.columns.push("%" + count + "$I");
1122 params.expressions.push('$' + count);
1123 params.values.push(attr.value);
1124 params.identifiers.push(attr.column);
1128 /* Handle passed values. */
1129 } else if (canEdit && val !== undefined && val !== null && !ormp.toMany) {
1130 if (attr.isEncrypted) {
1131 if (encryptionKey) {
1132 encryptQuery = "select encrypt(setbytea(%1$L), setbytea(%2$L), %3$L)";
1133 encryptSql = XT.format(encryptQuery, [record[prop], encryptionKey, 'bf']);
1134 val = record[prop] ? plv8.execute(encryptSql)[0].encrypt : null;
1135 params.columns.push("%" + count + "$I");
1136 params.values.push(val);
1137 params.identifiers.push(attr.column);
1138 params.expressions.push("$" + count);
1142 throw new Error("No encryption key provided.");
1145 if (ormp.toOne && nkey) {
1146 if (iorm.table.indexOf(".") > 0) {
1147 toOneQuery = "select %1$I from %2$I.%3$I where %4$I = $" + count;
1148 toOneSql = XT.format(toOneQuery, [
1149 XT.Orm.primaryKey(iorm, true),
1150 iorm.table.beforeDot(),
1151 iorm.table.afterDot(),
1155 toOneQuery = "select %1$I from %2$I where %3$I = $" + count;
1156 toOneSql = XT.format(toOneQuery, [
1157 XT.Orm.primaryKey(iorm, true),
1162 exp = "(" + toOneSql + ")";
1163 params.expressions.push(exp);
1165 params.expressions.push('$' + count);
1168 params.columns.push("%" + count + "$I");
1169 params.values.push(val);
1170 params.identifiers.push(attr.column);
1174 /* Handle null value if applicable. */
1175 } else if (canEdit && val === undefined || val === null) {
1176 if (attr.nullValue) {
1177 params.columns.push("%" + count + "$I");
1178 params.values.push(attr.nullValue);
1179 params.identifiers.push(attr.column);
1180 params.expressions.push('$' + count);
1183 } else if (attr.required) {
1184 plv8.elog(ERROR, "Attribute " + ormp.name + " is required.");
1193 /* Build the insert statement */
1194 columns = params.columns.join(', ');
1195 columns = XT.format(columns, params.identifiers);
1196 expressions = params.expressions.join(', ');
1197 expressions = XT.format(expressions, params.identifiers);
1199 if (params.table.indexOf(".") > 0) {
1200 namespace = params.table.beforeDot();
1201 table = params.table.afterDot();
1202 query = 'insert into %1$I.%2$I (' + columns + ') values (' + expressions + ')';
1203 params.statement = XT.format(query, [namespace, table]);
1205 query = 'insert into %1$I (' + columns + ') values (' + expressions + ')';
1206 params.statement = XT.format(query, [params.table]);
1209 /* If we can get the primary key column we want to return that
1210 for cases where it is determined behind the scenes */
1211 if (!record[pkey] && !params.primaryKey) {
1212 params.primaryKey = XT.Orm.primaryKey(orm, true);
1215 if (params.primaryKey && params.parentUuid) {
1216 params.statement = params.statement + ' returning ' + params.primaryKey + ' as id, obj_uuid';
1217 } else if (params.parentUuid) {
1218 params.statement = params.statement + ' returning obj_uuid';
1219 } else if (params.primaryKey) {
1220 params.statement = params.statement + ' returning ' + params.primaryKey + ' as id';
1224 XT.debug('prepareInsert statement =', params.statement);
1225 XT.debug('prepareInsert values =', params.values);
1232 * Commit update to the database
1234 * @param {Object} Options
1235 * @param {String} [options.nameSpace] Namespace. Required.
1236 * @param {String} [options.type] Type. Required.
1237 * @param {Object} [options.data] The data payload to be processed. Required.
1238 * @param {Number} [options.etag] Record version for optimistic locking.
1239 * @param {Object} [options.lock] Lock information for pessemistic locking.
1240 * @param {String} [options.encryptionKey] Encryption key.
1242 updateRecord: function (options) {
1243 var data = options.data,
1244 encryptionKey = options.encryptionKey,
1245 orm = this.fetchOrm(options.nameSpace, options.type),
1246 pkey = XT.Orm.primaryKey(orm),
1249 etag = this.getVersion(orm, id),
1254 lockKey = options.lock && options.lock.key ? options.lock.key : false,
1255 lockTable = orm.lockTable || orm.table,
1257 sql = this.prepareUpdate(orm, data, null, encryptionKey);
1259 /* Test for optimistic lock. */
1260 if (!XT.disableLocks && etag && options.etag !== etag) {
1261 // TODO - Improve error handling.
1262 plv8.elog(ERROR, "The version being updated is not current.");
1264 /* Test for pessimistic lock. */
1266 lock = this.tryLock(lockTable, id, {key: lockKey});
1268 // TODO - Improve error handling.
1269 plv8.elog(ERROR, "Can not obtain a lock on the record.");
1273 /* Okay, now lets handle arrays. */
1274 this.commitArrays(orm, data, encryptionKey);
1276 /* Handle extensions on the same table. */
1277 for (var i = 0; i < orm.extensions.length; i++) {
1278 if (orm.extensions[i].table === orm.table) {
1279 sql = this.prepareUpdate(orm.extensions[i], data, sql, encryptionKey);
1283 sql.values.push(id);
1285 /* Commit the base record. */
1287 XT.debug('updateRecord sql =', sql.statement);
1288 XT.debug('updateRecord values =', sql.values);
1290 plv8.execute(sql.statement, sql.values);
1292 /* Handle extensions on other tables. */
1293 for (var i = 0; i < orm.extensions.length; i++) {
1294 ext = orm.extensions[i];
1295 if (ext.table !== orm.table &&
1298 /* Determine whether to insert or update. */
1299 if (ext.table.indexOf(".") > 0) {
1300 iORuQuery = "select %1$I from %2$I.%3$I where %1$I = $1;";
1301 iORuSql = XT.format(iORuQuery, [
1302 ext.relations[0].column,
1303 ext.table.beforeDot(),
1304 ext.table.afterDot()
1307 iORuQuery = "select %1$I from %2$I where %1$I = $1;";
1308 iORuSql = XT.format(iORuQuery, [ext.relations[0].column, ext.table]);
1312 XT.debug('updateRecord sql =', iORuSql);
1313 XT.debug('updateRecord values =', [data[pkey]]);
1315 rows = plv8.execute(iORuSql, [data[pkey]]);
1318 sql = this.prepareUpdate(ext, data, null, encryptionKey);
1319 sql.values.push(id);
1321 sql = this.prepareInsert(ext, data, null, encryptionKey);
1325 XT.debug('updateRecord sql =', sql.statement);
1326 XT.debug('updateRecord values =', sql.values);
1329 if (sql.statement) {
1330 plv8.execute(sql.statement, sql.values);
1335 /* Release any lock. */
1337 this.releaseLock({table: lockTable, id: id});
1342 * Use an orm object and a record and build an update statement. It
1343 * returns an object with a table name string, expressions array and
1344 * insert statement string that can be executed.
1346 * The optional params object includes objects columns, expressions
1347 * that can be cumulatively added to the result.
1349 * @params {Object} Orm
1350 * @params {Object} Record
1351 * @params {Object} Params - optional
1354 prepareUpdate: function (orm, record, params, encryptionKey) {
1356 attributePrivileges,
1379 params = params || {
1385 params.table = orm.table;
1386 count = params.values.length + 1;
1388 if (orm.relations) {
1390 pkey = orm.relations[0].inverse;
1391 columnKey = orm.relations[0].column;
1394 pkey = XT.Orm.primaryKey(orm);
1395 columnKey = XT.Orm.primaryKey(orm, true);
1398 /* Build up the content for update of this record. */
1399 for (var i = 0; i < orm.properties.length; i++) {
1400 ormp = orm.properties[i];
1402 attr = ormp.attr ? ormp.attr : ormp.toOne ? ormp.toOne : ormp.toMany;
1404 iorm = ormp.toOne ? this.fetchOrm(orm.nameSpace, ormp.toOne.type) : false;
1405 nkey = iorm ? XT.Orm.naturalKey(iorm, true) : false;
1406 val = ormp.toOne && record[prop] instanceof Object ?
1407 record[prop][nkey || ormp.toOne.inverse || 'id'] : record[prop],
1409 attributePrivileges = orm.privileges &&
1410 orm.privileges.attribute &&
1411 orm.privileges.attribute[prop];
1414 * Ignore derived fields for insert/update
1416 if (attr.derived) continue;
1418 if(!attributePrivileges || attributePrivileges.update === undefined) {
1420 } else if (typeof attributePrivileges.update === 'string') {
1421 canEdit = this.checkPrivilege(attributePrivileges.update);
1423 canEdit = attributePrivileges.update; /* if it's true or false */
1426 if (canEdit && val !== undefined && !ormp.toMany) {
1428 /* Handle encryption if applicable. */
1429 if (attr.isEncrypted) {
1430 if (encryptionKey) {
1431 encryptQuery = "select encrypt(setbytea(%1$L), setbytea(%2$L), %3$L)";
1432 encryptSql = XT.format(encryptQuery, [val, encryptionKey, 'bf']);
1433 val = record[prop] ? plv8.execute(encryptSql)[0].encrypt : null;
1434 params.values.push(val);
1435 params.identifiers.push(attr.column);
1436 params.expressions.push("%" + count + "$I = $" + count);
1440 // TODO - Improve error handling.
1441 throw new Error("No encryption key provided.");
1443 } else if (ormp.name !== pkey) {
1445 if (attr.required) {
1446 plv8.elog(ERROR, "Attribute " + ormp.name + " is required.");
1448 params.values.push(attr.nullValue || null);
1449 params.expressions.push("%" + count + "$I = $" + count);
1451 } else if (ormp.toOne && nkey) {
1452 if (iorm.table.indexOf(".") > 0) {
1453 toOneQuery = "select %1$I from %2$I.%3$I where %4$I = $" + count;
1454 toOneSql = XT.format(toOneQuery, [
1455 XT.Orm.primaryKey(iorm, true),
1456 iorm.table.beforeDot(),
1457 iorm.table.afterDot(),
1461 toOneQuery = "select %1$I from %2$I where %3$I = $" + count;
1462 toOneSql = XT.format(toOneQuery, [
1463 XT.Orm.primaryKey(iorm, true),
1469 exp = "%" + count + "$I = (" + toOneSql + ")";
1470 params.values.push(val);
1471 params.expressions.push(exp);
1473 params.values.push(val);
1474 params.expressions.push("%" + count + "$I = $" + count);
1476 params.identifiers.push(attr.column);
1483 /* Build the update statement */
1484 expressions = params.expressions.join(', ');
1485 expressions = XT.format(expressions, params.identifiers);
1487 // do not send an invalid sql statement
1488 if (!isValidSql) { return params; }
1490 if (params.table.indexOf(".") > 0) {
1491 namespace = params.table.beforeDot();
1492 table = params.table.afterDot();
1493 query = 'update %1$I.%2$I set ' + expressions + ' where %3$I = $' + count + ';';
1494 params.statement = XT.format(query, [namespace, table, columnKey]);
1496 query = 'update %1$I set ' + expressions + ' where %2$I = $' + count + ';';
1497 params.statement = XT.format(query, [params.table, columnKey]);
1501 XT.debug('prepareUpdate statement =', params.statement);
1502 XT.debug('prepareUpdate values =', params.values);
1509 * Commit deletion to the database
1511 * @param {Object} Options
1512 * @param {String} [options.nameSpace] Namespace. Required.
1513 * @param {String} [options.type] Type. Required.
1514 * @param {Object} [options.data] The data payload to be processed. Required.
1515 * @param {Number} [options.etag] Optional record id version for optimistic locking.
1516 * If set and version does not match, delete will fail.
1517 * @param {Number} [options.lock] Lock information for pessemistic locking.
1519 deleteRecord: function (options) {
1520 var data = options.data,
1521 orm = this.fetchOrm(options.nameSpace, options.type, {silentError: true}),
1529 lockKey = options.lock && options.lock.key ? options.lock.key : false,
1539 /* Set variables or return false with message. */
1541 throw new handleError("Not Found", 404);
1544 pkey = XT.Orm.primaryKey(orm);
1545 nkey = XT.Orm.naturalKey(orm);
1546 lockTable = orm.lockTable || orm.table;
1547 if (!pkey && !nkey) {
1548 throw new handleError("Not Found", 404);
1551 id = nkey ? this.getId(orm, data[nkey]) : data[pkey];
1553 throw new handleError("Not Found", 404);
1556 /* Test for optional optimistic lock. */
1557 etag = this.getVersion(orm, id);
1558 if (etag && options.etag && etag !== options.etag) {
1559 throw new handleError("Precondition Required", 428);
1562 /* Test for pessemistic lock. */
1564 lock = this.tryLock(lockTable, id, {key: lockKey});
1566 throw new handleError("Conflict", 409);
1570 /* Delete children first. */
1571 for (prop in data) {
1572 ormp = XT.Orm.getProperty(orm, prop);
1574 /* If the property is an array of objects they must be records so delete them. */
1575 if (ormp.toMany && ormp.toMany.isNested) {
1576 values = data[prop];
1577 for (var i = 0; i < values.length; i++) {
1579 nameSpace: options.nameSpace,
1580 type: ormp.toMany.type,
1587 /* Next delete from extension tables. */
1588 for (var i = 0; i < orm.extensions.length; i++) {
1589 ext = orm.extensions[i];
1590 if (ext.table !== orm.table &&
1592 columnKey = ext.relations[0].column;
1593 nameKey = ext.relations[0].inverse;
1595 if (ext.table.indexOf(".") > 0) {
1596 namespace = ext.table.beforeDot();
1597 table = ext.table.afterDot();
1598 query = 'delete from %1$I.%2$I where %3$I = $1';
1599 sql = XT.format(query, [namespace, table, columnKey]);
1601 query = 'delete from %1$I where %2$I = $1';
1602 sql = XT.format(query, [ext.table, columnKey]);
1606 XT.debug('deleteRecord sql =', sql);
1607 XT.debug('deleteRecord values =', [id]);
1609 plv8.execute(sql, [id]);
1613 /* Now delete the top. */
1614 nameKey = XT.Orm.primaryKey(orm);
1615 columnKey = XT.Orm.primaryKey(orm, true);
1617 if (orm.table.indexOf(".") > 0) {
1618 namespace = orm.table.beforeDot();
1619 table = orm.table.afterDot();
1620 query = 'delete from %1$I.%2$I where %3$I = $1';
1621 sql = XT.format(query, [namespace, table, columnKey]);
1623 query = 'delete from %1$I where %2$I = $1';
1624 sql = XT.format(query, [orm.table, columnKey]);
1627 /* Commit the record.*/
1629 XT.debug('deleteRecord sql =', sql);
1630 XT.debug('deleteRecord values =', [id]);
1632 plv8.execute(sql, [id]);
1634 /* Release any lock. */
1636 this.releaseLock({table: lockTable, id: id});
1641 * Decrypts properties where applicable.
1643 * @param {String} name space
1644 * @param {String} type
1645 * @param {Object} record
1646 * @param {Object} encryption key
1649 decrypt: function (nameSpace, type, record, encryptionKey) {
1652 hexToAlpha = function (hex) {
1654 for (i = 2; i < hex.length; i += 2) {
1655 str += String.fromCharCode(parseInt(hex.substr(i, 2), 16));
1659 orm = this.fetchOrm(nameSpace, type);
1661 for (prop in record) {
1662 var ormp = XT.Orm.getProperty(orm, prop.camelize());
1664 /* Decrypt property if applicable. */
1665 if (ormp && ormp.attr && ormp.attr.isEncrypted) {
1666 if (encryptionKey) {
1667 sql = "select formatbytea(decrypt($1, setbytea($2), 'bf')) as result";
1668 // TODO - Handle not found error.
1670 if (DEBUG && false) {
1671 XT.debug('decrypt prop =', prop);
1672 XT.debug('decrypt sql =', sql);
1673 XT.debug('decrypt values =', [record[prop], encryptionKey]);
1675 result = plv8.execute(sql, [record[prop], encryptionKey])[0].result;
1676 /* we SOMETIMES need to translate from hex here */
1677 if(typeof result === 'string' && result.substring(0, 2) === '\\x') {
1678 result = result ? hexToAlpha(result) : result;
1680 /* in the special case of encrypted credit card numbers, we don't give the
1681 user the full decrypted number EVEN IF they have the encryption key */
1682 if(ormp.attr.isEncrypted === "credit_card_number" && result && result.length >= 4) {
1683 record[prop] = "************" + result.substring(result.length - 4);
1685 record[prop] = result;
1688 record[prop] = '**********';
1691 /* Check recursively. */
1692 } else if (ormp.toOne && ormp.toOne.isNested) {
1693 that.decrypt(nameSpace, ormp.toOne.type, record[prop], encryptionKey);
1695 } else if (ormp.toMany && ormp.toMany.isNested) {
1696 record[prop].map(function (subdata) {
1697 that.decrypt(nameSpace, ormp.toMany.type, subdata, encryptionKey);
1706 Fetches the ORM. Caches the result in this data object, where it can be used
1707 for this request but will be conveniently forgotten between requests.
1709 fetchOrm: function (nameSpace, type) {
1712 recordType = nameSpace + '.'+ type;
1718 res = this._maps.findProperty('recordType', recordType);
1722 ret = XT.Orm.fetch(nameSpace, type);
1724 /* cache the result so we don't requery needlessly */
1725 this._maps.push({ "recordType": recordType, "map": ret});
1731 * Get the oid for a given table name.
1733 * @param {String} table name
1736 getTableOid: function (table) {
1737 var tableName = this.getTableFromNamespacedTable(table).toLowerCase(), /* be generous */
1738 namespace = this.getNamespaceFromNamespacedTable(table),
1740 sql = "select pg_class.oid::integer as oid " +
1741 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1742 "where relname = $1 and nspname = $2";
1745 XT.debug('getTableOid sql =', sql);
1746 XT.debug('getTableOid values =', [tableName, namespace]);
1748 ret = plv8.execute(sql, [tableName, namespace])[0].oid - 0;
1750 // TODO - Handle not found error.
1756 * Get the primary key id for an object based on a passed in natural key.
1758 * @param {Object} Orm
1759 * @param {String} Natural key value
1761 getId: function (orm, value) {
1762 var ncol = XT.Orm.naturalKey(orm, true),
1763 pcol = XT.Orm.primaryKey(orm, true),
1768 if (orm.table.indexOf(".") > 0) {
1769 namespace = orm.table.beforeDot();
1770 table = orm.table.afterDot();
1771 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1772 sql = XT.format(query, [pcol, namespace, table, ncol]);
1774 query = "select %1$I as id from %2$I where %3$I = $1";
1775 sql = XT.format(query, [pcol, orm.table, ncol]);
1779 XT.debug('getId sql =', sql);
1780 XT.debug('getId values =', [value]);
1783 ret = plv8.execute(sql, [value]);
1788 throw new handleError("Primary Key not found on " + orm.table +
1789 " where " + ncol + " = " + value, 400);
1793 getNamespaceFromNamespacedTable: function (fullName) {
1794 return fullName.indexOf(".") > 0 ? fullName.beforeDot() : "public";
1797 getTableFromNamespacedTable: function (fullName) {
1798 return fullName.indexOf(".") > 0 ? fullName.afterDot() : fullName;
1801 getPgTypeFromOrmType: function (schema, table, column) {
1802 var sql = "select data_type from information_schema.columns " +
1804 "and table_schema = $1 " +
1805 "and table_name = $2 " +
1806 "and column_name = $3;",
1808 values = [schema, table, column];
1811 XT.debug('getPgTypeFromOrmType sql =', sql);
1812 XT.debug('getPgTypeFromOrmType values =', values);
1815 pgType = plv8.execute(sql, values);
1816 pgType = pgType && pgType[0] ? pgType[0].data_type : false;
1822 * Get the natural key id for an object based on a passed in primary key.
1824 * @param {Object} Orm
1825 * @param {Number|String} Primary key value
1826 * @param {Boolean} safe Return the original value instead of erroring if no match is found
1828 getNaturalId: function (orm, value, safe) {
1829 var ncol = XT.Orm.naturalKey(orm, true),
1830 pcol = XT.Orm.primaryKey(orm, true),
1835 if (orm.table.indexOf(".") > 0) {
1836 namespace = orm.table.beforeDot();
1837 table = orm.table.afterDot();
1838 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1839 sql = XT.format(query, [ncol, namespace, table, pcol]);
1841 query = "select %1$I as id from %2$I where %3$I = $1";
1842 sql = XT.format(query, [ncol, orm.table, pcol]);
1846 XT.debug('getNaturalId sql =', sql);
1847 XT.debug('getNaturalId values =', [value]);
1850 ret = plv8.execute(sql, [value]);
1857 throw new handleError("Natural Key Not Found: " + orm.nameSpace + "." + orm.type, 400);
1862 * Returns the current version of a record.
1864 * @param {Object} Orm
1865 * @param {Number|String} Record id
1867 getVersion: function (orm, id) {
1868 if (!orm.lockable) { return; }
1871 oid = this.getTableOid(orm.lockTable || orm.table),
1873 sql = 'select ver_etag from xt.ver where ver_table_oid = $1 and ver_record_id = $2;';
1876 XT.debug('getVersion sql = ', sql);
1877 XT.debug('getVersion values = ', [oid, id]);
1879 res = plv8.execute(sql, [oid, id]);
1880 etag = res.length ? res[0].ver_etag : false;
1883 etag = XT.generateUUID();
1884 sql = 'insert into xt.ver (ver_table_oid, ver_record_id, ver_etag) values ($1, $2, $3::uuid);';
1885 // TODO - Handle insert error.
1888 XT.debug('getVersion insert sql = ', sql);
1889 XT.debug('getVersion insert values = ', [oid, id, etag]);
1891 plv8.execute(sql, [oid, id, etag]);
1898 * Fetch an array of records from the database.
1900 * @param {Object} Options
1901 * @param {String} [dataHash.nameSpace] Namespace. Required.
1902 * @param {String} [dataHash.type] Type. Required.
1903 * @param {Array} [dataHash.parameters] Parameters
1904 * @param {Array} [dataHash.orderBy] Order by - optional
1905 * @param {Number} [dataHash.rowLimit] Row limit - optional
1906 * @param {Number} [dataHash.rowOffset] Row offset - optional
1909 fetch: function (options) {
1910 var nameSpace = options.nameSpace,
1911 type = options.type,
1912 query = options.query || {},
1913 encryptionKey = options.encryptionKey,
1914 orderBy = query.orderBy,
1915 orm = this.fetchOrm(nameSpace, type),
1918 parameters = query.parameters,
1919 clause = this.buildClause(nameSpace, type, parameters, orderBy),
1921 pkey = XT.Orm.primaryKey(orm),
1922 pkeyColumn = XT.Orm.primaryKey(orm, true),
1923 nkey = XT.Orm.naturalKey(orm),
1924 limit = query.rowLimit ? XT.format('limit %1$L', [query.rowLimit]) : '',
1925 offset = query.rowOffset ? XT.format('offset %1$L', [query.rowOffset]) : '',
1928 nameSpace: nameSpace,
1938 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};',
1939 sql2 = 'select * from %1$I.%2$I where %3$I in ({ids}) {orderBy}';
1941 /* Validate - don't bother running the query if the user has no privileges. */
1942 if (!this.checkPrivileges(nameSpace, type)) { return []; }
1944 tableNamespace = this.getNamespaceFromNamespacedTable(orm.table);
1945 table = this.getTableFromNamespacedTable(orm.table);
1948 /* Just get the count of rows that match the conditions */
1949 sqlCount = 'select count(distinct t1.%3$I) as count from %1$I.%2$I t1 {joins} where {conditions};';
1950 sqlCount = XT.format(sqlCount, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1951 sqlCount = sqlCount.replace('{joins}', clause.joins)
1952 .replace('{conditions}', clause.conditions);
1955 XT.debug('fetch sqlCount = ', sqlCount);
1956 XT.debug('fetch values = ', clause.parameters);
1959 ret.data = plv8.execute(sqlCount, clause.parameters);
1963 /* Because we query views of views, you can get inconsistent results */
1964 /* when doing limit and offest queries without an order by. Add a default. */
1965 if (limit && offset && (!orderBy || !orderBy.length) && !clause.orderByColumns) {
1966 /* We only want this on sql1, not sql2's clause.orderBy. */
1967 clause.orderByColumns = XT.format('order by t1.%1$I', [pkeyColumn]);
1970 /* Query the model. */
1971 sql1 = XT.format(sql1, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1972 sql1 = sql1.replace('{joins}', clause.joins)
1973 .replace('{conditions}', clause.conditions)
1974 .replace(/{groupBy}/g, clause.groupByColumns)
1975 .replace(/{orderBy}/g, clause.orderByColumns)
1976 .replace('{limit}', limit)
1977 .replace('{offset}', offset);
1980 XT.debug('fetch sql1 = ', sql1);
1981 XT.debug('fetch values = ', clause.parameters);
1984 /* First query for matching ids, then get entire result set. */
1985 /* This improves performance over a direct query on the view due */
1986 /* to the way sorting is handled by the query optimizer */
1987 qry = plv8.execute(sql1, clause.parameters) || [];
1988 if (!qry.length) { return [] };
1989 qry.forEach(function (row) {
1991 idParams.push("$" + counter);
1996 sql_etags = "select ver_etag as etag, ver_record_id as id " +
1998 "where ver_table_oid = ( " +
1999 "select pg_class.oid::integer as oid " +
2000 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
2001 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
2002 "where nspname = %1$L and relname = %2$L " +
2004 "and ver_record_id in ({ids})";
2005 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
2006 sql_etags = sql_etags.replace('{ids}', idParams.join());
2009 XT.debug('fetch sql_etags = ', sql_etags);
2010 XT.debug('fetch etags_values = ', JSON.stringify(ids));
2012 etags = plv8.execute(sql_etags, ids) || {};
2016 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize(), pkey]);
2017 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
2018 .replace('{ids}', idParams.join());
2021 XT.debug('fetch sql2 = ', sql2);
2022 XT.debug('fetch values = ', JSON.stringify(ids));
2024 ret.data = plv8.execute(sql2, ids) || [];
2026 for (var i = 0; i < ret.data.length; i++) {
2027 ret.data[i] = this.decrypt(nameSpace, type, ret.data[i], encryptionKey);
2030 /* Add etags to result in pkey->etag format. */
2031 for (var j = 0; j < etags.length; j++) {
2032 if (etags[j].id === ret.data[i][pkey]) {
2033 ret.etags[ret.data[i][nkey]] = etags[j].etag;
2039 this.sanitize(nameSpace, type, ret.data, options);
2045 Fetch a metric value.
2047 @param {String} Metric name
2048 @param {String} Return type 'text', 'boolean' or 'number' (default 'text')
2050 fetchMetric: function (name, type) {
2051 var fn = 'fetchmetrictext';
2052 if (type === 'boolean') {
2053 fn = 'fetchmetricbool';
2054 } else if (type === 'number') {
2055 fn = 'fetchmetricvalue';
2057 return plv8.execute("select " + fn + "($1) as resp", [name])[0].resp;
2061 * Retreives a record from the database. If the user does not have appropriate privileges an
2062 * error will be thrown unless the `silentError` option is passed.
2064 * If `context` is passed as an option then a record will only be returned if it exists in the context (parent)
2065 * record which itself must be accessible by the effective user.
2067 * @param {Object} options
2068 * @param {String} [options.nameSpace] Namespace. Required.
2069 * @param {String} [options.type] Type. Required.
2070 * @param {Number} [options.id] Record id. Required.
2071 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
2072 * @param {String} [options.encryptionKey] Encryption key
2073 * @param {Boolean} [options.silentError=false] Silence errors
2074 * @param {Object} [options.context] Context
2075 * @param {String} [options.context.nameSpace] Context namespace.
2076 * @param {String} [options.context.type] The type of context object.
2077 * @param {String} [options.context.value] The value of the context's primary key.
2078 * @param {String} [options.context.relation] The name of the attribute on the type to which this record is related.
2081 retrieveRecord: function (options) {
2082 options = options ? options : {};
2083 options.obtainLock = false;
2085 var id = options.id,
2086 nameSpace = options.nameSpace,
2087 type = options.type,
2088 map = this.fetchOrm(nameSpace, type),
2089 context = options.context,
2090 encryptionKey = options.encryptionKey,
2092 lockTable = map.lockTable || map.table,
2093 nkey = XT.Orm.naturalKey(map),
2095 pkey = XT.Orm.primaryKey(map),
2097 nameSpace: nameSpace,
2104 throw new Error('No key found for {nameSpace}.{type}'
2105 .replace("{nameSpace}", nameSpace)
2106 .replace("{type}", type));
2109 /* If this object uses a natural key, go get the primary key id. */
2111 id = this.getId(map, id);
2117 /* Context means search for this record inside another. */
2119 context.nameSpace = context.nameSpace || context.recordType.beforeDot();
2120 context.type = context.type || context.recordType.afterDot()
2121 context.map = this.fetchOrm(context.nameSpace, context.type);
2122 context.prop = XT.Orm.getProperty(context.map, context.relation);
2123 context.pertinentExtension = XT.Orm.getProperty(context.map, context.relation, true);
2124 context.underlyingTable = context.pertinentExtension.table,
2125 context.underlyingNameSpace = this.getNamespaceFromNamespacedTable(context.underlyingTable);
2126 context.underlyingType = this.getTableFromNamespacedTable(context.underlyingTable);
2127 context.fkey = context.prop.toMany.inverse;
2128 context.fkeyColumn = context.prop.toMany.column;
2129 context.pkey = XT.Orm.naturalKey(context.map) || XT.Orm.primaryKey(context.map);
2130 params.attribute = context.pkey;
2131 params.value = context.value;
2133 join = 'join %1$I.%2$I on (%1$I.%2$I.%3$I = %4$I.%5$I)';
2134 join = XT.format(join, [
2135 context.underlyingNameSpace,
2136 context.underlyingType,
2143 /* Validate - don't bother running the query if the user has no privileges. */
2144 if(!options.superUser && !context && !this.checkPrivileges(nameSpace, type)) {
2145 if (options.silentError) {
2148 throw new handleError("Unauthorized", 401);
2152 ret.etag = this.getVersion(map, id);
2154 /* Obtain lock if required. */
2156 ret.lock = this.tryLock(lockTable, id, options);
2160 sql = 'select %1$I.* from %2$I.%1$I {join} where %1$I.%3$I = $1;';
2161 sql = sql.replace(/{join}/, join);
2162 sql = XT.format(sql, [type.decamelize(), nameSpace.decamelize(), pkey]);
2164 /* Query the map. */
2166 XT.debug('retrieveRecord sql = ', sql);
2167 XT.debug('retrieveRecord values = ', [id]);
2169 ret.data = plv8.execute(sql, [id])[0] || {};
2172 /* Check privileges again, this time against record specific criteria where applicable. */
2173 if(!options.superUser && !this.checkPrivileges(nameSpace, type, ret.data)) {
2174 if (options.silentError) {
2177 throw new handleError("Unauthorized", 401);
2180 /* Decrypt result where applicable. */
2181 ret.data = this.decrypt(nameSpace, type, ret.data, encryptionKey);
2184 this.sanitize(nameSpace, type, ret.data, options);
2186 /* Return the results. */
2191 * Remove unprivileged attributes, primary and foreign keys from the data.
2192 * Only removes the primary key if a natural key has been specified in the ORM.
2193 * Also format for printing using XT.format functions if printFormat=true'
2195 * @param {String} Namespace
2196 * @param {String} Type
2197 * @param {Object|Array} Data
2198 * @param {Object} Options
2199 * @param {Boolean} [options.includeKeys=false] Do not remove primary and foreign keys.
2200 * @param {Boolean} [options.superUser=false] Do not remove unprivileged attributes.
2201 * @param {Boolean} [options.printFormat=true] Format for printing.
2203 sanitize: function (nameSpace, type, data, options) {
2204 options = options || {};
2205 if (options.includeKeys && options.superUser) { return; }
2206 if (XT.typeOf(data) !== "array") { data = [data]; }
2207 var orm = this.fetchOrm(nameSpace, type),
2208 pkey = XT.Orm.primaryKey(orm),
2209 nkey = XT.Orm.naturalKey(orm),
2210 props = orm.properties,
2211 attrPriv = orm.privileges && orm.privileges.attribute ?
2212 orm.privileges.attribute : false,
2213 inclKeys = options.includeKeys,
2214 superUser = options.superUser,
2215 printFormat = options.printFormat,
2226 check = function (p) {
2227 return p.name === itemAttr;
2230 for (var c = 0; c < data.length; c++) {
2233 /* Remove primary key if applicable */
2234 if (!inclKeys && nkey && nkey !== pkey) { delete item[pkey]; }
2236 for (itemAttr in item) {
2237 if (!item.hasOwnProperty(itemAttr)) {
2240 filteredProps = orm.properties.filter(check);
2242 if (filteredProps.length === 0 && orm.extensions.length > 0) {
2243 /* Try to get the orm prop from an extension if it's not in the core*/
2244 orm.extensions.forEach(function (ext) {
2245 if (filteredProps.length === 0) {
2246 filteredProps = ext.properties.filter(check);
2251 /* Remove attributes not found in the ORM */
2252 if (filteredProps.length === 0) {
2253 delete item[itemAttr];
2255 prop = filteredProps[0];
2258 /* Remove unprivileged attribute if applicable */
2259 if (!superUser && attrPriv && attrPriv[prop.name] &&
2260 (attrPriv[prop.name].view !== undefined) &&
2261 !this.checkPrivilege(attrPriv[prop.name].view)) {
2262 delete item[prop.name];
2265 /* Format for printing if printFormat and not an object */
2266 if (printFormat && !prop.toOne && !prop.toMany) {
2267 switch(prop.attr.type) {
2270 preOffsetDate = item[itemAttr];
2271 offsetDate = preOffsetDate &&
2272 new Date(preOffsetDate.valueOf() + 60000 * preOffsetDate.getTimezoneOffset());
2273 item[itemAttr] = XT.formatDate(offsetDate).formatdate;
2276 item[itemAttr] = XT.formatCost(item[itemAttr]).formatcost.toString();
2279 item[itemAttr] = XT.formatNumeric(item[itemAttr], "").formatnumeric.toString();
2282 item[itemAttr] = XT.formatMoney(item[itemAttr]).formatmoney.toString();
2285 item[itemAttr] = XT.formatSalesPrice(item[itemAttr]).formatsalesprice.toString();
2287 case "PurchasePrice":
2288 item[itemAttr] = XT.formatPurchPrice(item[itemAttr]).formatpurchprice.toString();
2290 case "ExtendedPrice":
2291 item[itemAttr] = XT.formatExtPrice(item[itemAttr]).formatextprice.toString();
2294 item[itemAttr] = XT.formatQty(item[itemAttr]).formatqty.toString();
2297 item[itemAttr] = XT.formatQtyPer(item[itemAttr]).formatqtyper.toString();
2299 case "UnitRatioScale":
2300 item[itemAttr] = XT.formatRatio(item[itemAttr]).formatratio.toString();
2303 item[itemAttr] = XT.formatPrcnt(item[itemAttr]).formatprcnt.toString();
2306 item[itemAttr] = XT.formatWeight(item[itemAttr]).formatweight.toString();
2309 item[itemAttr] = (item[itemAttr] || "").toString();
2313 /* Handle composite types */
2314 if (prop.toOne && prop.toOne.isNested && item[prop.name]) {
2315 this.sanitize(nameSpace, prop.toOne.type, item[prop.name], options);
2316 } else if (prop.toMany && prop.toMany.isNested && item[prop.name]) {
2317 for (var n = 0; n < item[prop.name].length; n++) {
2318 val = item[prop.name][n];
2320 /* Remove foreign key if applicable */
2321 if (!inclKeys) { delete val[prop.toMany.inverse]; }
2322 this.sanitize(nameSpace, prop.toMany.type, val, options);
2330 * Returns a array of key value pairs of metric settings that correspond with an array of passed keys.
2332 * @param {Array} array of metric names
2335 retrieveMetrics: function (keys) {
2340 sql = 'select metric_name as setting, metric_value as value '
2342 + 'where metric_name in ({literals})';
2344 for (var i = 0; i < keys.length; i++) {
2345 literals[i] = "%" + (i + 1) + "$L";
2348 sql = sql.replace(/{literals}/, literals.join(','));
2349 sql = XT.format(sql, keys)
2352 XT.debug('retrieveMetrics sql = ', sql);
2354 qry = plv8.execute(sql);
2356 /* Recast where applicable. */
2357 for (var i = 0; i < qry.length; i++) {
2358 prop = qry[i].setting;
2359 if(qry[i].value === 't') { ret[prop] = true; }
2360 else if(qry[i].value === 'f') { ret[prop] = false }
2361 else if(!isNaN(qry[i].value)) { ret[prop] = qry[i].value - 0; }
2362 else { ret[prop] = qry[i].value; }
2365 /* Make sure there is a result at all times */
2366 keys.forEach(function (key) {
2367 if (ret[key] === undefined) { ret[key] = null; }
2374 * Creates and returns a lock for a given table. Defaults to a time based lock of 30 seconds
2375 * unless aternate timeout option or process id (pid) is passed. If a pid is passed, the lock
2376 * is considered infinite as long as the pid is valid. If a previous lock key is passed and it is
2377 * valid, a new lock will be granted.
2379 * @param {String | Number} Table name or oid
2380 * @param {Number} Record id
2381 * @param {Object} Options
2382 * @param {Number} [options.timeout=30]
2383 * @param {Number} [options.pid] Process id
2384 * @param {Number} [options.key] Key
2385 * @param {Boolean} [options.obtainLock=true] If false, only checks for existing lock
2387 tryLock: function (table, id, options) {
2388 options = options ? options : {};
2390 var deleteSql = "delete from xt.lock where lock_id = $1;",
2391 timeout = options.timeout || 30,
2392 expires = new Date(),
2394 insertSqlExp = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_expires) " +
2395 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2396 insertSqlPid = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_pid) " +
2397 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2402 pid = options.pid || null,
2403 pidSql = "select usename, procpid " +
2404 "from pg_stat_activity " +
2405 "where datname=current_database() " +
2406 " and usename=$1 " +
2409 selectSql = "select * " +
2411 "where lock_table_oid = $1 " +
2412 " and lock_record_id = $2;",
2413 username = XT.username;
2415 /* If passed a table name, look up the oid. */
2416 oid = typeof table === "string" ? this.getTableOid(table) : table;
2418 if (DEBUG) XT.debug("Trying lock table", [oid, id]);
2420 /* See if there are existing lock(s) for this record. */
2422 XT.debug('tryLock sql = ', selectSql);
2423 XT.debug('tryLock values = ', [oid, id]);
2425 query = plv8.execute(selectSql, [oid, id]);
2427 /* Validate result */
2428 if (query.length > 0) {
2429 while (query.length) {
2430 lock = query.shift();
2432 /* See if we are confirming our own lock. */
2433 if (options.key && options.key === lock.lock_id) {
2434 /* Go on and we'll get a new lock. */
2436 /* Make sure if they are pid locks users is still connected. */
2437 } else if (lock.lock_pid) {
2439 XT.debug('tryLock sql = ', pidSql);
2440 XT.debug('tryLock values = ', [lock.lock_username, lock.lock_pid]);
2442 pcheck = plv8.execute(pidSql, [lock.lock_username, lock.lock_pid]);
2443 if (pcheck.length) { break; } /* valid lock */
2445 lockExp = new Date(lock.lock_expires);
2446 if (DEBUG) { XT.debug("Lock found", [lockExp > expires, lockExp, expires]); }
2447 if (lockExp > expires) { break; } /* valid lock */
2450 /* Delete invalid or expired lock. */
2452 XT.debug('tryLock sql = ', deleteSql);
2453 XT.debug('tryLock values = ', [lock.lock_id]);
2455 plv8.execute(deleteSql, [lock.lock_id]);
2460 if (DEBUG) XT.debug("Lock found", lock.lock_username);
2463 username: lock.lock_username,
2464 effective: lock.lock_effective
2469 if (options.obtainLock === false) { return; }
2471 if (DEBUG) { XT.debug("Creating lock."); }
2472 if (DEBUG) { XT.debug('tryLock sql = ', insertSqlPid); }
2475 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, pid]); }
2476 lock = plv8.execute(insertSqlPid, [oid, id, username, pid])[0];
2478 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2479 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, expires]); }
2480 lock = plv8.execute(insertSqlExp, [oid, id, username, expires])[0];
2483 if (DEBUG) { XT.debug("Lock returned is", lock.lock_id); }
2487 effective: lock.lock_effective,
2493 * Release a lock. Pass either options with a key, or table, id and username.
2495 * @param {Object} Options: key or table and id
2497 releaseLock: function (options) {
2499 sqlKey = 'delete from xt.lock where lock_id = $1;',
2500 sqlUsr = 'delete from xt.lock where lock_table_oid = $1 and lock_record_id = $2 and lock_username = $3;',
2501 username = XT.username;
2505 XT.debug('releaseLock sql = ', sqlKey);
2506 XT.debug('releaseLock values = ', [options.key]);
2508 plv8.execute(sqlKey, [options.key]);
2510 oid = typeof options.table === "string" ? this.getTableOid(options.table) : options.table;
2513 XT.debug('releaseLock sql = ', sqlUsr);
2514 XT.debug('releaseLock values = ', [oid, options.id, username]);
2516 plv8.execute(sqlUsr, [oid, options.id, username]);
2523 * Renew a lock. Defaults to rewing the lock for 30 seconds.
2525 * @param {Number} Key
2526 * @params {Object} Options: timeout
2527 * @returns {Date} New expiration or false.
2529 renewLock: function (key, options) {
2530 var expires = new Date(),
2532 selectSql = "select * from xt.lock where lock_id = $1;",
2533 timeout = options && options.timeout ? options.timeout : 30,
2534 updateSql = "update xt.lock set lock_expires = $1 where lock_id = $2;";
2536 if (typeof key !== "number") { return false; }
2537 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2540 XT.debug('renewLock sql = ', selectSql);
2541 XT.debug('renewLock values = ', [key]);
2543 query = plv8.execute(selectSql, [key]);
2547 XT.debug('renewLock sql = ', updateSql);
2548 XT.debug('renewLock values = ', [expires, key]);
2550 plv8.execute(updateSql, [expires, key]);