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.toOne.type) {
118 subChildOrm = that.fetchOrm(nameSpace, currentProp.toOne.type);
119 } else if (currentProp.toMany && currentProp.toMany.type) {
120 subChildOrm = that.fetchOrm(nameSpace, currentProp.toMany.type);
122 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + currentProp.name);
125 if (pathIndex < pathParts.length - 1) {
127 walkPath(pathParts, subChildOrm, pathIndex + 1);
129 /* This is the end of the path. */
130 naturalKey = XT.Orm.naturalKey(subChildOrm);
131 if (currentAttributeIsString) {
132 /* add the natural key to the end of the requested attribute */
133 parameter.attribute = attribute + "." + naturalKey;
135 /* swap out the attribute in the array for the one with the prepended natural key */
136 index = parameter.attribute.indexOf(attribute);
137 parameter.attribute.splice(index, 1);
138 parameter.attribute.splice(index, 0, attribute + "." + naturalKey);
143 if ((prop.toOne || prop.toMany)) {
144 /* Someone is querying on a toOne without using a path */
145 if (prop.toOne && prop.toOne.type) {
146 childOrm = that.fetchOrm(nameSpace, prop.toOne.type);
147 } else if (prop.toMany && prop.toMany.type) {
148 childOrm = that.fetchOrm(nameSpace, prop.toMany.type);
150 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + prop.name);
153 if (attribute.indexOf('.') < 0) {
154 naturalKey = XT.Orm.naturalKey(childOrm);
155 if (attributeIsString) {
156 /* add the natural key to the end of the requested attribute */
157 parameter.attribute = attribute + "." + naturalKey;
159 /* swap out the attribute in the array for the one with the prepended natural key */
160 index = parameter.attribute.indexOf(attribute);
161 parameter.attribute.splice(index, 1);
162 parameter.attribute.splice(index, 0, attribute + "." + naturalKey);
165 /* Even if there's a path x.y, it's possible that it's still not
166 correct because the correct path maybe is x.y.naturalKeyOfY */
167 walkPath(attribute.split("."), orm, 0);
173 /* Handle parameters. */
174 if (parameters.length) {
175 for (var i = 0; i < parameters.length; i++) {
177 param = parameters[i];
178 op = param.operator || '=';
198 for (var c = 0; c < param.value.length; c++) {
199 ret.parameters.push(param.value[c]);
200 param.value[c] = '$' + count;
206 for (var c = 0; c < param.value.length; c++) {
207 ret.parameters.push(param.value[c]);
208 param.value[c] = '$' + count;
213 plv8.elog(ERROR, 'Invalid operator: ' + op);
216 /* Handle characteristics. This is very specific to xTuple,
217 and highly dependant on certain table structures and naming conventions,
218 but otherwise way too much work to refactor in an abstract manner right now. */
219 if (param.isCharacteristic) {
222 param.value = ' ARRAY[' + param.value.join(',') + ']';
225 /* Booleans are stored as strings. */
226 if (param.value === true) {
228 } else if (param.value === false) {
232 /* Yeah, it depends on a property called 'characteristics'... */
233 prop = XT.Orm.getProperty(orm, 'characteristics');
235 /* Build the characteristics query clause. */
236 identifiers.push(XT.Orm.primaryKey(orm, true));
237 identifiers.push(prop.toMany.inverse);
238 identifiers.push(orm.nameSpace.toLowerCase());
239 identifiers.push(prop.toMany.type.decamelize());
240 identifiers.push(param.attribute);
241 identifiers.push(param.value);
243 charSql = '%' + (identifiers.length - 5) + '$I in (' +
244 ' select %' + (identifiers.length - 4) + '$I '+
245 ' from %' + (identifiers.length - 3) + '$I.%' + (identifiers.length - 2) + '$I ' +
246 ' join char on (char_name = characteristic)' +
248 /* Note: Not using $i for these. L = literal here. These is not identifiers. */
249 ' and char_name = %' + (identifiers.length - 1) + '$L ' +
250 ' and value ' + op + ' %' + (identifiers.length) + '$L ' +
253 clauses.push(charSql);
255 /* Array comparisons handle another way. e.g. %1$I !<@ ARRAY[$1,$2] */
256 } else if (op === '<@' || op === '!<@') {
257 /* Handle paths if applicable. */
258 if (param.attribute.indexOf('.') > -1) {
259 parts = param.attribute.split('.');
260 childOrm = this.fetchOrm(nameSpace, type);
262 pcount = params.length - 1;
264 for (var n = 0; n < parts.length; n++) {
265 /* Validate attribute. */
266 prop = XT.Orm.getProperty(childOrm, parts[n]);
268 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
272 if (n === parts.length - 1) {
273 identifiers.push("jt" + (joins.length - 1));
274 identifiers.push(prop.attr.column);
275 pgType = this.getPgTypeFromOrmType(
276 this.getNamespaceFromNamespacedTable(childOrm.table),
277 this.getTableFromNamespacedTable(childOrm.table),
280 pgType = pgType ? "::" + pgType + "[]" : '';
281 params[pcount] += "%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I";
282 params[pcount] += ' ' + op + ' ARRAY[' + param.value.join(',') + ']' + pgType;
284 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
285 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
286 joinIdentifiers.push(
287 this.getNamespaceFromNamespacedTable(childOrm.table),
288 this.getTableFromNamespacedTable(childOrm.table),
289 sourceTableAlias, prop.toOne.column,
290 XT.Orm.primaryKey(childOrm, true));
291 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
292 + "$I jt" + joins.length + " on %"
293 + (joinIdentifiers.length - 2) + "$I.%"
294 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
298 prop = XT.Orm.getProperty(orm, param.attribute);
299 pertinentExtension = XT.Orm.getProperty(orm, param.attribute, true);
300 if(pertinentExtension.isChild || pertinentExtension.isExtension) {
301 /* We'll need to join this orm extension */
302 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
303 joinIdentifiers.push(
304 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
305 this.getTableFromNamespacedTable(pertinentExtension.table),
306 fromKeyProp.attr.column,
307 pertinentExtension.relations[0].column);
308 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
309 + "$I jt" + joins.length + " on t1.%"
310 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
313 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute);
316 identifiers.push(pertinentExtension.isChild || pertinentExtension.isExtension ?
317 "jt" + (joins.length - 1) :
319 identifiers.push(prop.attr.column);
320 pgType = this.getPgTypeFromOrmType(
321 this.getNamespaceFromNamespacedTable(orm.table),
322 this.getTableFromNamespacedTable(orm.table),
325 pgType = pgType ? "::" + pgType + "[]" : '';
326 params.push("%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I " + op + ' ARRAY[' + param.value.join(',') + ']' + pgType);
327 pcount = params.length - 1;
329 clauses.push(params[pcount]);
331 /* Everything else handle another. */
333 if (XT.typeOf(param.attribute) !== 'array') {
334 param.attribute = [param.attribute];
337 for (var c = 0; c < param.attribute.length; c++) {
338 /* Handle paths if applicable. */
339 if (param.attribute[c].indexOf('.') > -1) {
340 parts = param.attribute[c].split('.');
341 childOrm = this.fetchOrm(nameSpace, type);
343 pcount = params.length - 1;
346 /* Check if last part is an Array. */
347 for (var m = 0; m < parts.length; m++) {
348 /* Validate attribute. */
349 prop = XT.Orm.getProperty(childOrm, parts[m]);
351 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[m]);
354 if (m < parts.length - 1) {
355 if (prop.toOne && prop.toOne.type) {
356 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
357 } else if (prop.toMany && prop.toMany.type) {
358 childOrm = this.fetchOrm(nameSpace, prop.toMany.type);
360 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + prop.name);
362 } else if (prop.attr && prop.attr.type === 'Array') {
363 /* The last property in the path is an array. */
365 params[pcount] = '$' + count;
369 /* Reset the childOrm to parent. */
370 childOrm = this.fetchOrm(nameSpace, type);
372 for (var n = 0; n < parts.length; n++) {
373 /* Validate attribute. */
374 prop = XT.Orm.getProperty(childOrm, parts[n]);
376 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
379 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
380 if (param.isUsernamePrivFilter && isArray) {
381 identifiers.push(prop.attr.column);
382 arrayIdentifiers.push(identifiers.length);
384 if (n < parts.length - 1) {
385 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
388 pertinentExtension = XT.Orm.getProperty(childOrm, parts[n], true);
389 var isExtension = pertinentExtension.isChild || pertinentExtension.isExtension;
391 /* We'll need to join this orm extension */
392 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
393 joinIdentifiers.push(
394 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
395 this.getTableFromNamespacedTable(pertinentExtension.table),
396 fromKeyProp.attr.column,
397 pertinentExtension.relations[0].column);
398 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
399 + "$I jt" + joins.length + " on t1.%"
400 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
402 /* Build path, e.g. table_name.column_name */
403 if (n === parts.length - 1) {
404 identifiers.push("jt" + (joins.length - 1));
405 identifiers.push(prop.attr.column);
406 params[pcount] += "%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I";
408 params[pcount] = "lower(" + params[pcount] + ")";
411 sourceTableAlias = n === 0 && !isExtension ? "t1" : "jt" + (joins.length - 1);
412 if (prop.toOne && prop.toOne.type) {
413 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
414 joinIdentifiers.push(
415 this.getNamespaceFromNamespacedTable(childOrm.table),
416 this.getTableFromNamespacedTable(childOrm.table),
417 sourceTableAlias, prop.toOne.column,
418 XT.Orm.primaryKey(childOrm, true)
420 } else if (prop.toMany && prop.toMany.type) {
421 childOrm = this.fetchOrm(nameSpace, prop.toMany.type);
422 joinIdentifiers.push(
423 this.getNamespaceFromNamespacedTable(childOrm.table),
424 this.getTableFromNamespacedTable(childOrm.table),
425 sourceTableAlias, prop.toMany.column,
426 XT.Orm.primaryKey(childOrm, true)
429 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
430 + "$I jt" + joins.length + " on %"
431 + (joinIdentifiers.length - 2) + "$I.%"
432 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
437 /* Validate attribute. */
438 prop = XT.Orm.getProperty(orm, param.attribute[c]);
439 pertinentExtension = XT.Orm.getProperty(orm, param.attribute[c], true);
440 if(pertinentExtension.isChild || pertinentExtension.isExtension) {
441 /* We'll need to join this orm extension */
442 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
443 joinIdentifiers.push(
444 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
445 this.getTableFromNamespacedTable(pertinentExtension.table),
446 fromKeyProp.attr.column,
447 pertinentExtension.relations[0].column);
448 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
449 + "$I jt" + joins.length + " on t1.%"
450 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
453 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute[c]);
456 identifiers.push(pertinentExtension.isChild || pertinentExtension.isExtension ?
457 "jt" + (joins.length - 1) :
459 identifiers.push(prop.attr.column);
461 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
462 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested) ||
463 (prop.attr && prop.attr.type === 'Array'))) {
465 params.push('$' + count);
466 pcount = params.length - 1;
467 arrayIdentifiers.push(identifiers.length);
469 params.push("%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I");
470 pcount = params.length - 1;
474 /* Add persional privs array search. */
475 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested)
476 || (prop.attr && prop.attr.type === 'Array') || isArray)) {
478 /* XXX: this bit of code has not been touched by the optimization refactor */
479 /* e.g. 'admin' = ANY (usernames_array) */
481 params[pcount] += ' ' + op + ' ANY (';
483 /* Build path. e.g. ((%1$I).%2$I).%3$I */
484 for (var f =0; f < arrayIdentifiers.length; f++) {
485 arrayParams += '%' + arrayIdentifiers[f] + '$I';
486 if (f < arrayIdentifiers.length - 1) {
487 arrayParams = "(" + arrayParams + ").";
490 params[pcount] += arrayParams + ')';
492 /* Add optional is null clause. */
493 } else if (parameters[i].includeNull) {
494 /* e.g. %1$I = $1 or %1$I is null */
495 params[pcount] = params[pcount] + " " + op + ' $' + count + ' or ' + params[pcount] + ' is null';
498 params[pcount] += " " + op + ' $' + count;
501 orClause.push(params[pcount]);
504 /* If more than one clause we'll get: (%1$I = $1 or %1$I = $2 or %1$I = $3) */
505 clauses.push('(' + orClause.join(' or ') + ')');
507 ret.parameters.push(param.value);
512 ret.conditions = (clauses.length ? '(' + XT.format(clauses.join(' and '), identifiers) + ')' : ret.conditions) || true;
514 /* Massage orderBy with quoted identifiers. */
515 /* We need to support the xm case for sql2 and the xt/public (column) optimized case for sql1 */
516 /* In practice we build the two lists independently of one another */
518 for (var i = 0; i < orderBy.length; i++) {
519 /* Handle path case. */
520 if (orderBy[i].attribute.indexOf('.') > -1) {
521 parts = orderBy[i].attribute.split('.');
523 orderByParams.push("");
524 orderByColumnParams.push("");
525 groupByColumnParams.push("");
526 pcount = orderByParams.length - 1;
528 for (var n = 0; n < parts.length; n++) {
529 prop = XT.Orm.getProperty(orm, parts[n]);
531 plv8.elog(ERROR, 'Attribute not found in map: ' + parts[n]);
533 orderByIdentifiers.push(parts[n]);
534 orderByParams[pcount] += "%" + orderByIdentifiers.length + "$I";
536 if (n === parts.length - 1) {
537 orderByColumnIdentifiers.push("jt" + (joins.length - 1));
538 orderByColumnIdentifiers.push(prop.attr.column);
539 orderByColumnParams[pcount] += "%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I"
540 groupByColumnParams[pcount] += "%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I"
542 orderByParams[pcount] = "(" + orderByParams[pcount] + ").";
543 orm = this.fetchOrm(nameSpace, prop.toOne.type);
544 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
545 joinIdentifiers.push(
546 this.getNamespaceFromNamespacedTable(orm.table),
547 this.getTableFromNamespacedTable(orm.table),
548 sourceTableAlias, prop.toOne.column,
549 XT.Orm.primaryKey(orm, true));
550 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
551 + "$I jt" + joins.length + " on %"
552 + (joinIdentifiers.length - 2) + "$I.%"
553 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
559 prop = XT.Orm.getProperty(orm, orderBy[i].attribute);
561 plv8.elog(ERROR, 'Attribute not found in map: ' + orderBy[i].attribute);
563 orderByIdentifiers.push(orderBy[i].attribute);
564 orderByColumnIdentifiers.push("t1");
566 We might need to look at toOne if the client is asking for a toOne without specifying
567 the path. Unfortunately, if they do specify the path, then sql2 will fail. So this does
568 work, although we're really sorting by the primary key of the toOne, whereas the
569 user probably wants us to sort by the natural key TODO
571 orderByColumnIdentifiers.push(prop.attr ? prop.attr.column : prop.toOne.column);
572 orderByParams.push("%" + orderByIdentifiers.length + "$I");
573 orderByColumnParams.push("%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I");
574 groupByColumnParams.push("%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I");
575 pcount = orderByParams.length - 1;
578 if (orderBy[i].isEmpty) {
579 orderByParams[pcount] = "length(" + orderByParams[pcount] + ")=0";
580 orderByColumnParams[pcount] = "length(" + orderByColumnParams[pcount] + ")=0";
582 if (orderBy[i].descending) {
583 orderByParams[pcount] += " desc";
584 orderByColumnParams[pcount] += " desc";
587 orderByList.push(orderByParams[pcount])
588 orderByColumnList.push(orderByColumnParams[pcount])
592 ret.orderBy = orderByList.length ? XT.format('order by ' + orderByList.join(','), orderByIdentifiers) : '';
593 ret.orderByColumns = orderByColumnList.length ? XT.format('order by ' + orderByColumnList.join(','), orderByColumnIdentifiers) : '';
594 ret.groupByColumns = groupByColumnParams.length ? XT.format(', ' + groupByColumnParams.join(','), orderByColumnIdentifiers) : '';
595 ret.joins = joins.length ? XT.format(joins.join(' '), joinIdentifiers) : '';
601 * Queries whether the current user has been granted the privilege passed.
603 * @param {String} privilege
606 checkPrivilege: function (privilege) {
613 if (typeof privilege === 'string') {
614 if (!this._granted) { this._granted = {}; }
615 if (!this._granted[XT.username]) { this._granted[XT.username] = {}; }
616 if (this._granted[XT.username][privilege] !== undefined) { return this._granted[XT.username][privilege]; }
618 /* The privilege name is allowed to be a set of space-delimited privileges */
619 /* If a user has any of the applicable privileges then they get access */
620 privArray = privilege.split(" ");
621 sql = 'select coalesce(usrpriv_priv_id, grppriv_priv_id, -1) > 0 as granted ' +
623 'left join usrpriv on (priv_id=usrpriv_priv_id) and (usrpriv_username=$1) ' +
625 ' select distinct grppriv_priv_id ' +
627 ' join usrgrp on (grppriv_grp_id=usrgrp_grp_id) and (usrgrp_username=$1) ' +
628 ' ) grppriv on (grppriv_priv_id=priv_id) ' +
629 'where priv_name = $2';
631 for (var i = 1; i < privArray.length; i++) {
632 sql = sql + ' or priv_name = $' + (i + 2);
634 sql = sql + "order by granted desc limit 1;";
636 /* Cleverness: the query parameters are just the priv array with the username tacked on front. */
637 privArray.unshift(XT.username);
640 XT.debug('checkPrivilege sql =', sql);
641 XT.debug('checkPrivilege values =', privArray);
643 res = plv8.execute(sql, privArray);
644 ret = res.length ? res[0].granted : false;
647 this._granted[XT.username][privilege] = ret;
651 XT.debug('Privilege check for "' + XT.username + '" on "' + privilege + '" returns ' + ret);
658 * Validate whether user has read access to data. If a record is passed, check personal privileges of
661 * @param {String} name space
662 * @param {String} type name
663 * @param {Object} record - optional
664 * @param {Boolean} is top level, default is true
667 checkPrivileges: function (nameSpace, type, record, isTopLevel) {
668 isTopLevel = isTopLevel !== false ? true : false;
669 var action = record && record.dataState === this.CREATED_STATE ? 'create' :
670 record && record.dataState === this.DELETED_STATE ? 'delete' :
671 record && record.dataState === this.UPDATED_STATE ? 'update' : 'read',
672 committing = record ? record.dataState !== this.READ_STATE : false,
674 isGrantedPersonal = false,
675 map = this.fetchOrm(nameSpace, type),
676 privileges = map.privileges,
680 /* If there is no ORM, this isn't a table data type so no check required. */
683 XT.debug('orm type is ->', map.type);
684 XT.debug('orm is ->', map);
687 if (!map) { return true; }
689 /* Can not access 'nested only' records directly. */
691 XT.debug('is top level ->', isTopLevel);
692 XT.debug('is nested ->', map.isNestedOnly);
694 if (isTopLevel && map.isNestedOnly) { return false; }
696 /* Check privileges - first do we have access to anything? */
698 if (DEBUG) { XT.debug('privileges found', privileges); }
700 if (DEBUG) { XT.debug('is committing'); }
702 /* Check if user has 'all' read privileges. */
703 isGrantedAll = privileges.all ? this.checkPrivilege(privileges.all[action]) : false;
705 /* Otherwise check for 'personal' read privileges. */
707 isGrantedPersonal = privileges.personal ?
708 this.checkPrivilege(privileges.personal[action]) : false;
711 if (DEBUG) { XT.debug('is NOT committing'); }
713 /* Check if user has 'all' read privileges. */
714 isGrantedAll = privileges.all ?
715 this.checkPrivilege(privileges.all.read) ||
716 this.checkPrivilege(privileges.all.update) : false;
718 /* Otherwise check for 'personal' read privileges. */
720 isGrantedPersonal = privileges.personal ?
721 this.checkPrivilege(privileges.personal.read) ||
722 this.checkPrivilege(privileges.personal.update) : false;
727 /* If we're checknig an actual record and only have personal privileges, */
728 /* see if the record allows access. */
729 if (record && !isGrantedAll && isGrantedPersonal && action !== "create") {
730 if (DEBUG) { XT.debug('checking record level personal privileges'); }
733 /* Shared checker function that checks 'personal' properties for access rights. */
734 checkPersonal = function (record) {
737 props = privileges.personal.properties,
738 get = function (obj, target) {
741 parts = target.split("."),
744 for (var idx = 0; idx < parts.length; idx++) {
746 ret = ret ? ret[part] : obj[part];
747 if (ret === null || ret === undefined) {
755 while (!isGranted && i < props.length) {
757 personalUser = get(record, prop);
759 if (personalUser instanceof Array) {
760 for (var userIdx = 0; userIdx < personalUser.length; userIdx++) {
761 if (personalUser[userIdx].toLowerCase() === XT.username) {
765 } else if (personalUser) {
766 isGranted = personalUser.toLowerCase() === XT.username;
775 /* If committing we need to ensure the record in its previous state is editable by this user. */
776 if (committing && (action === 'update' || action === 'delete')) {
777 pkey = XT.Orm.naturalKey(map) || XT.Orm.primaryKey(map);
778 old = this.retrieveRecord({
779 nameSpace: nameSpace,
785 isGrantedPersonal = checkPersonal(old.data);
787 /* Otherwise check personal privileges on the record passed. */
788 } else if (action === 'read') {
789 isGrantedPersonal = checkPersonal(record);
794 XT.debug('is granted all ->', isGrantedAll);
795 XT.debug('is granted personal ->', isGrantedPersonal);
798 return isGrantedAll || isGrantedPersonal;
802 * Commit array columns with their own statements
804 * @param {Object} Orm
805 * @param {Object} Record
807 commitArrays: function (orm, record, encryptionKey) {
808 var pkey = XT.Orm.primaryKey(orm),
817 resolveKey = function (col) {
820 /* First search properties */
821 var ary = orm.properties.filter(function (prop) {
822 return prop.attr && prop.attr.column === col;
829 /* If not found must be extension, search relations */
830 if (orm.extensions.length) {
831 orm.extensions.forEach(function (ext) {
833 ary = ext.relations.filter(function (prop) {
834 return prop.column === col;
838 attr = ary[0].inverse;
844 if (attr) { return attr };
846 /* If still not found, we have a structural problem */
847 throw new Error("Can not resolve primary id on toMany relation");
850 for (prop in record) {
851 ormp = XT.Orm.getProperty(orm, prop);
853 /* If the property is an array of objects they must be records so commit them. */
854 if (ormp.toMany && ormp.toMany.isNested) {
855 fkey = ormp.toMany.inverse;
856 values = record[prop];
858 for (var i = 0; i < values.length; i++) {
861 /* Populate the parent key into the foreign key field if it's absent. */
863 columnToKey = ormp.toMany.column;
864 propToKey = columnToKey ? resolveKey(columnToKey) : pkey;
865 if (!record[propToKey]) {
866 /* If there's no data, we have a structural problem */
867 throw new Error("Can not resolve foreign key on toMany relation " + ormp.name);
869 val[fkey] = record[propToKey];
873 nameSpace: orm.nameSpace,
874 type: ormp.toMany.type,
876 encryptionKey: encryptionKey
884 * Commit metrics that have changed to the database.
886 * @param {Object} metrics
889 commitMetrics: function (metrics) {
891 sql = 'select setMetric($1,$2)',
894 for (key in metrics) {
895 value = metrics[key];
896 if (typeof value === 'boolean') {
897 value = value ? 't' : 'f';
898 } else if (typeof value === 'number') {
899 value = value.toString();
903 XT.debug('commitMetrics sql =', sql);
904 XT.debug('commitMetrics values =', [key, value]);
906 plv8.execute(sql, [key, value]);
913 * Commit a record to the database. The record must conform to the object hiearchy as defined by the
914 * record's `ORM` definition. Each object in the tree must include state information on a reserved property
915 * called `dataState`. Valid values are `create`, `update` and `delete`. Objects with other dataState values including
916 * `undefined` will be ignored. State values can be added using `XT.jsonpatch.updateState(obj, state)`.
918 * @seealso XT.jsonpatch.updateState
919 * @param {Object} Options
920 * @param {String} [options.nameSpace] Namespace. Required.
921 * @param {String} [options.type] Type. Required.
922 * @param {Object} [options.data] The data payload to be processed. Required
923 * @param {Number} [options.etag] Record version for optimistic locking.
924 * @param {Object} [options.lock] Lock information for pessemistic locking.
925 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
926 * @param {String} [options.encryptionKey] Encryption key.
928 commitRecord: function (options) {
929 var data = options.data,
930 dataState = data ? data.dataState : false,
931 hasAccess = options.superUser ||
932 this.checkPrivileges(options.nameSpace, options.type, data, false);
934 if (!hasAccess) { throw new Error("Access Denied."); }
937 case (this.CREATED_STATE):
938 this.createRecord(options);
940 case (this.UPDATED_STATE):
941 this.updateRecord(options);
943 case (this.DELETED_STATE):
944 this.deleteRecord(options);
949 * Commit insert to the database
951 * @param {Object} Options
952 * @param {String} [options.nameSpace] Namespace. Required.
953 * @param {String} [options.type] Type. Required.
954 * @param {Object} [options.data] The data payload to be processed. Required.
955 * @param {String} [options.encryptionKey] Encryption key.
957 createRecord: function (options) {
958 var data = options.data,
959 encryptionKey = options.encryptionKey,
961 orm = this.fetchOrm(options.nameSpace, options.type),
962 sql = this.prepareInsert(orm, data, null, encryptionKey),
963 pkey = XT.Orm.primaryKey(orm),
966 /* Handle extensions on the same table. */
967 for (var i = 0; i < orm.extensions.length; i++) {
968 if (orm.extensions[i].table === orm.table) {
969 sql = this.prepareInsert(orm.extensions[i], data, sql, encryptionKey);
973 /* Commit the base record. */
975 XT.debug('createRecord sql =', sql.statement);
976 XT.debug('createRecord values =', sql.values);
980 rec = plv8.execute(sql.statement, sql.values);
981 /* Make sure the primary key is populated */
983 data[pkey] = rec[0].id;
985 /* Make sure the obj_uuid is populated, if applicable */
986 if (!data.obj_uuid && rec[0] && rec[0].obj_uuid) {
987 data.uuid = rec[0].obj_uuid;
991 /* Handle extensions on other tables. */
992 for (var i = 0; i < orm.extensions.length; i++) {
993 if (orm.extensions[i].table !== orm.table &&
994 !orm.extensions[i].isChild) {
995 sql = this.prepareInsert(orm.extensions[i], data, null, encryptionKey);
998 XT.debug('createRecord sql =', sql.statement);
999 XT.debug('createRecord values =', sql.values);
1002 if (sql.statement) {
1003 plv8.execute(sql.statement, sql.values);
1008 /* Okay, now lets handle arrays. */
1009 this.commitArrays(orm, data, encryptionKey);
1013 * Use an orm object and a record and build an insert statement. It
1014 * returns an object with a table name string, columns array, expressions
1015 * array and insert statement string that can be executed.
1017 * The optional params object includes objects columns, expressions
1018 * that can be cumulatively added to the result.
1020 * @params {Object} Orm
1021 * @params {Object} Record
1022 * @params {Object} Params - optional
1023 * @params {String} Encryption Key
1026 prepareInsert: function (orm, record, params, encryptionKey) {
1028 attributePrivileges,
1039 pkey = XT.Orm.primaryKey(orm),
1042 sql = "select nextval($1) as id",
1048 isValidSql = params && params.statement ? true : false,
1051 params = params || {
1058 params.table = orm.table;
1059 count = params.values.length + 1;
1061 /* If no primary key, then create one. */
1062 if (!record[pkey] && orm.idSequenceName) {
1064 XT.debug('prepareInsert sql =', sql);
1065 XT.debug('prepareInsert values =', [orm.idSequenceName]);
1067 record[pkey] = plv8.execute(sql, [orm.idSequenceName])[0].id;
1070 /* If extension handle key. */
1071 if (orm.relations) {
1072 for (var i = 0; i < orm.relations.length; i++) {
1073 column = orm.relations[i].column;
1074 if (!params.identifiers.contains(column)) {
1075 params.columns.push("%" + count + "$I");
1076 params.values.push(record[orm.relations[i].inverse]);
1077 params.expressions.push('$' + count);
1078 params.identifiers.push(orm.relations[i].column);
1084 /* Build up the content for insert of this record. */
1085 for (var i = 0; i < orm.properties.length; i++) {
1086 ormp = orm.properties[i];
1089 if (ormp.toMany && ormp.toMany.column === 'obj_uuid') {
1090 params.parentUuid = true;
1093 attr = ormp.attr ? ormp.attr : ormp.toOne ? ormp.toOne : ormp.toMany;
1095 iorm = ormp.toOne ? this.fetchOrm(orm.nameSpace, ormp.toOne.type) : false,
1096 nkey = iorm ? XT.Orm.naturalKey(iorm, true) : false;
1097 val = ormp.toOne && record[prop] instanceof Object ?
1098 record[prop][nkey || ormp.toOne.inverse || 'id'] : record[prop];
1101 * Ignore derived fields for insert/update
1103 if (attr.derived) continue;
1105 attributePrivileges = orm.privileges &&
1106 orm.privileges.attribute &&
1107 orm.privileges.attribute[prop];
1109 if(!attributePrivileges || attributePrivileges.create === undefined) {
1111 } else if (typeof attributePrivileges.create === 'string') {
1112 canEdit = this.checkPrivilege(attributePrivileges.create);
1114 canEdit = attributePrivileges.create; /* if it's true or false */
1117 /* Handle fixed values. */
1118 if (attr.value !== undefined) {
1119 params.columns.push("%" + count + "$I");
1120 params.expressions.push('$' + count);
1121 params.values.push(attr.value);
1122 params.identifiers.push(attr.column);
1126 /* Handle passed values. */
1127 } else if (canEdit && val !== undefined && val !== null && !ormp.toMany) {
1128 if (attr.isEncrypted) {
1129 if (encryptionKey) {
1130 encryptQuery = "select encrypt(setbytea(%1$L), setbytea(%2$L), %3$L)";
1131 encryptSql = XT.format(encryptQuery, [record[prop], encryptionKey, 'bf']);
1132 val = record[prop] ? plv8.execute(encryptSql)[0].encrypt : null;
1133 params.columns.push("%" + count + "$I");
1134 params.values.push(val);
1135 params.identifiers.push(attr.column);
1136 params.expressions.push("$" + count);
1140 throw new Error("No encryption key provided.");
1143 if (ormp.toOne && nkey) {
1144 if (iorm.table.indexOf(".") > 0) {
1145 toOneQuery = "select %1$I from %2$I.%3$I where %4$I = $" + count;
1146 toOneSql = XT.format(toOneQuery, [
1147 XT.Orm.primaryKey(iorm, true),
1148 iorm.table.beforeDot(),
1149 iorm.table.afterDot(),
1153 toOneQuery = "select %1$I from %2$I where %3$I = $" + count;
1154 toOneSql = XT.format(toOneQuery, [
1155 XT.Orm.primaryKey(iorm, true),
1160 exp = "(" + toOneSql + ")";
1161 params.expressions.push(exp);
1163 params.expressions.push('$' + count);
1166 params.columns.push("%" + count + "$I");
1167 params.values.push(val);
1168 params.identifiers.push(attr.column);
1172 /* Handle null value if applicable. */
1173 } else if (canEdit && val === undefined || val === null) {
1174 if (attr.nullValue) {
1175 params.columns.push("%" + count + "$I");
1176 params.values.push(attr.nullValue);
1177 params.identifiers.push(attr.column);
1178 params.expressions.push('$' + count);
1181 } else if (attr.required) {
1182 plv8.elog(ERROR, "Attribute " + ormp.name + " is required.");
1191 /* Build the insert statement */
1192 columns = params.columns.join(', ');
1193 columns = XT.format(columns, params.identifiers);
1194 expressions = params.expressions.join(', ');
1195 expressions = XT.format(expressions, params.identifiers);
1197 if (params.table.indexOf(".") > 0) {
1198 namespace = params.table.beforeDot();
1199 table = params.table.afterDot();
1200 query = 'insert into %1$I.%2$I (' + columns + ') values (' + expressions + ')';
1201 params.statement = XT.format(query, [namespace, table]);
1203 query = 'insert into %1$I (' + columns + ') values (' + expressions + ')';
1204 params.statement = XT.format(query, [params.table]);
1207 /* If we can get the primary key column we want to return that
1208 for cases where it is determined behind the scenes */
1209 if (!record[pkey] && !params.primaryKey) {
1210 params.primaryKey = XT.Orm.primaryKey(orm, true);
1213 if (params.primaryKey && params.parentUuid) {
1214 params.statement = params.statement + ' returning ' + params.primaryKey + ' as id, obj_uuid';
1215 } else if (params.parentUuid) {
1216 params.statement = params.statement + ' returning obj_uuid';
1217 } else if (params.primaryKey) {
1218 params.statement = params.statement + ' returning ' + params.primaryKey + ' as id';
1222 XT.debug('prepareInsert statement =', params.statement);
1223 XT.debug('prepareInsert values =', params.values);
1230 * Commit update to the database
1232 * @param {Object} Options
1233 * @param {String} [options.nameSpace] Namespace. Required.
1234 * @param {String} [options.type] Type. Required.
1235 * @param {Object} [options.data] The data payload to be processed. Required.
1236 * @param {Number} [options.etag] Record version for optimistic locking.
1237 * @param {Object} [options.lock] Lock information for pessemistic locking.
1238 * @param {String} [options.encryptionKey] Encryption key.
1240 updateRecord: function (options) {
1241 var data = options.data,
1242 encryptionKey = options.encryptionKey,
1243 orm = this.fetchOrm(options.nameSpace, options.type),
1244 pkey = XT.Orm.primaryKey(orm),
1247 etag = this.getVersion(orm, id),
1252 lockKey = options.lock && options.lock.key ? options.lock.key : false,
1253 lockTable = orm.lockTable || orm.table,
1255 sql = this.prepareUpdate(orm, data, null, encryptionKey);
1257 /* Test for optimistic lock. */
1258 if (!XT.disableLocks && etag && options.etag !== etag) {
1259 // TODO - Improve error handling.
1260 plv8.elog(ERROR, "The version being updated is not current.");
1262 /* Test for pessimistic lock. */
1264 lock = this.tryLock(lockTable, id, {key: lockKey});
1266 // TODO - Improve error handling.
1267 plv8.elog(ERROR, "Can not obtain a lock on the record.");
1271 /* Okay, now lets handle arrays. */
1272 this.commitArrays(orm, data, encryptionKey);
1274 /* Handle extensions on the same table. */
1275 for (var i = 0; i < orm.extensions.length; i++) {
1276 if (orm.extensions[i].table === orm.table) {
1277 sql = this.prepareUpdate(orm.extensions[i], data, sql, encryptionKey);
1281 sql.values.push(id);
1283 /* Commit the base record. */
1285 XT.debug('updateRecord sql =', sql.statement);
1286 XT.debug('updateRecord values =', sql.values);
1288 plv8.execute(sql.statement, sql.values);
1290 /* Handle extensions on other tables. */
1291 for (var i = 0; i < orm.extensions.length; i++) {
1292 ext = orm.extensions[i];
1293 if (ext.table !== orm.table &&
1296 /* Determine whether to insert or update. */
1297 if (ext.table.indexOf(".") > 0) {
1298 iORuQuery = "select %1$I from %2$I.%3$I where %1$I = $1;";
1299 iORuSql = XT.format(iORuQuery, [
1300 ext.relations[0].column,
1301 ext.table.beforeDot(),
1302 ext.table.afterDot()
1305 iORuQuery = "select %1$I from %2$I where %1$I = $1;";
1306 iORuSql = XT.format(iORuQuery, [ext.relations[0].column, ext.table]);
1310 XT.debug('updateRecord sql =', iORuSql);
1311 XT.debug('updateRecord values =', [data[pkey]]);
1313 rows = plv8.execute(iORuSql, [data[pkey]]);
1316 sql = this.prepareUpdate(ext, data, null, encryptionKey);
1317 sql.values.push(id);
1319 sql = this.prepareInsert(ext, data, null, encryptionKey);
1323 XT.debug('updateRecord sql =', sql.statement);
1324 XT.debug('updateRecord values =', sql.values);
1327 if (sql.statement) {
1328 plv8.execute(sql.statement, sql.values);
1333 /* Release any lock. */
1335 this.releaseLock({table: lockTable, id: id});
1340 * Use an orm object and a record and build an update statement. It
1341 * returns an object with a table name string, expressions array and
1342 * insert statement string that can be executed.
1344 * The optional params object includes objects columns, expressions
1345 * that can be cumulatively added to the result.
1347 * @params {Object} Orm
1348 * @params {Object} Record
1349 * @params {Object} Params - optional
1352 prepareUpdate: function (orm, record, params, encryptionKey) {
1354 attributePrivileges,
1377 params = params || {
1383 params.table = orm.table;
1384 count = params.values.length + 1;
1386 if (orm.relations) {
1388 pkey = orm.relations[0].inverse;
1389 columnKey = orm.relations[0].column;
1392 pkey = XT.Orm.primaryKey(orm);
1393 columnKey = XT.Orm.primaryKey(orm, true);
1396 /* Build up the content for update of this record. */
1397 for (var i = 0; i < orm.properties.length; i++) {
1398 ormp = orm.properties[i];
1400 attr = ormp.attr ? ormp.attr : ormp.toOne ? ormp.toOne : ormp.toMany;
1402 iorm = ormp.toOne ? this.fetchOrm(orm.nameSpace, ormp.toOne.type) : false;
1403 nkey = iorm ? XT.Orm.naturalKey(iorm, true) : false;
1404 val = ormp.toOne && record[prop] instanceof Object ?
1405 record[prop][nkey || ormp.toOne.inverse || 'id'] : record[prop],
1407 attributePrivileges = orm.privileges &&
1408 orm.privileges.attribute &&
1409 orm.privileges.attribute[prop];
1412 * Ignore derived fields for insert/update
1414 if (attr.derived) continue;
1416 if(!attributePrivileges || attributePrivileges.update === undefined) {
1418 } else if (typeof attributePrivileges.update === 'string') {
1419 canEdit = this.checkPrivilege(attributePrivileges.update);
1421 canEdit = attributePrivileges.update; /* if it's true or false */
1424 if (canEdit && val !== undefined && !ormp.toMany) {
1426 /* Handle encryption if applicable. */
1427 if (attr.isEncrypted) {
1428 if (encryptionKey) {
1429 encryptQuery = "select encrypt(setbytea(%1$L), setbytea(%2$L), %3$L)";
1430 encryptSql = XT.format(encryptQuery, [val, encryptionKey, 'bf']);
1431 val = record[prop] ? plv8.execute(encryptSql)[0].encrypt : null;
1432 params.values.push(val);
1433 params.identifiers.push(attr.column);
1434 params.expressions.push("%" + count + "$I = $" + count);
1438 // TODO - Improve error handling.
1439 throw new Error("No encryption key provided.");
1441 } else if (ormp.name !== pkey) {
1443 if (attr.required) {
1444 plv8.elog(ERROR, "Attribute " + ormp.name + " is required.");
1446 params.values.push(attr.nullValue || null);
1447 params.expressions.push("%" + count + "$I = $" + count);
1449 } else if (ormp.toOne && nkey) {
1450 if (iorm.table.indexOf(".") > 0) {
1451 toOneQuery = "select %1$I from %2$I.%3$I where %4$I = $" + count;
1452 toOneSql = XT.format(toOneQuery, [
1453 XT.Orm.primaryKey(iorm, true),
1454 iorm.table.beforeDot(),
1455 iorm.table.afterDot(),
1459 toOneQuery = "select %1$I from %2$I where %3$I = $" + count;
1460 toOneSql = XT.format(toOneQuery, [
1461 XT.Orm.primaryKey(iorm, true),
1467 exp = "%" + count + "$I = (" + toOneSql + ")";
1468 params.values.push(val);
1469 params.expressions.push(exp);
1471 params.values.push(val);
1472 params.expressions.push("%" + count + "$I = $" + count);
1474 params.identifiers.push(attr.column);
1481 /* Build the update statement */
1482 expressions = params.expressions.join(', ');
1483 expressions = XT.format(expressions, params.identifiers);
1485 // do not send an invalid sql statement
1486 if (!isValidSql) { return params; }
1488 if (params.table.indexOf(".") > 0) {
1489 namespace = params.table.beforeDot();
1490 table = params.table.afterDot();
1491 query = 'update %1$I.%2$I set ' + expressions + ' where %3$I = $' + count + ';';
1492 params.statement = XT.format(query, [namespace, table, columnKey]);
1494 query = 'update %1$I set ' + expressions + ' where %2$I = $' + count + ';';
1495 params.statement = XT.format(query, [params.table, columnKey]);
1499 XT.debug('prepareUpdate statement =', params.statement);
1500 XT.debug('prepareUpdate values =', params.values);
1507 * Commit deletion to the database
1509 * @param {Object} Options
1510 * @param {String} [options.nameSpace] Namespace. Required.
1511 * @param {String} [options.type] Type. Required.
1512 * @param {Object} [options.data] The data payload to be processed. Required.
1513 * @param {Number} [options.etag] Optional record id version for optimistic locking.
1514 * If set and version does not match, delete will fail.
1515 * @param {Number} [options.lock] Lock information for pessemistic locking.
1517 deleteRecord: function (options) {
1518 var data = options.data,
1519 orm = this.fetchOrm(options.nameSpace, options.type, {silentError: true}),
1527 lockKey = options.lock && options.lock.key ? options.lock.key : false,
1537 /* Set variables or return false with message. */
1539 throw new handleError("Not Found", 404);
1542 pkey = XT.Orm.primaryKey(orm);
1543 nkey = XT.Orm.naturalKey(orm);
1544 lockTable = orm.lockTable || orm.table;
1545 if (!pkey && !nkey) {
1546 throw new handleError("Not Found", 404);
1549 id = nkey ? this.getId(orm, data[nkey]) : data[pkey];
1551 throw new handleError("Not Found", 404);
1554 /* Test for optional optimistic lock. */
1555 etag = this.getVersion(orm, id);
1556 if (etag && options.etag && etag !== options.etag) {
1557 throw new handleError("Precondition Required", 428);
1560 /* Test for pessemistic lock. */
1562 lock = this.tryLock(lockTable, id, {key: lockKey});
1564 throw new handleError("Conflict", 409);
1568 /* Delete children first. */
1569 for (prop in data) {
1570 ormp = XT.Orm.getProperty(orm, prop);
1572 /* If the property is an array of objects they must be records so delete them. */
1573 if (ormp.toMany && ormp.toMany.isNested) {
1574 values = data[prop];
1575 for (var i = 0; i < values.length; i++) {
1577 nameSpace: options.nameSpace,
1578 type: ormp.toMany.type,
1585 /* Next delete from extension tables. */
1586 for (var i = 0; i < orm.extensions.length; i++) {
1587 ext = orm.extensions[i];
1588 if (ext.table !== orm.table &&
1590 columnKey = ext.relations[0].column;
1591 nameKey = ext.relations[0].inverse;
1593 if (ext.table.indexOf(".") > 0) {
1594 namespace = ext.table.beforeDot();
1595 table = ext.table.afterDot();
1596 query = 'delete from %1$I.%2$I where %3$I = $1';
1597 sql = XT.format(query, [namespace, table, columnKey]);
1599 query = 'delete from %1$I where %2$I = $1';
1600 sql = XT.format(query, [ext.table, columnKey]);
1604 XT.debug('deleteRecord sql =', sql);
1605 XT.debug('deleteRecord values =', [id]);
1607 plv8.execute(sql, [id]);
1611 /* Now delete the top. */
1612 nameKey = XT.Orm.primaryKey(orm);
1613 columnKey = XT.Orm.primaryKey(orm, true);
1615 if (orm.table.indexOf(".") > 0) {
1616 namespace = orm.table.beforeDot();
1617 table = orm.table.afterDot();
1618 query = 'delete from %1$I.%2$I where %3$I = $1';
1619 sql = XT.format(query, [namespace, table, columnKey]);
1621 query = 'delete from %1$I where %2$I = $1';
1622 sql = XT.format(query, [orm.table, columnKey]);
1625 /* Commit the record.*/
1627 XT.debug('deleteRecord sql =', sql);
1628 XT.debug('deleteRecord values =', [id]);
1630 plv8.execute(sql, [id]);
1632 /* Release any lock. */
1634 this.releaseLock({table: lockTable, id: id});
1639 * Decrypts properties where applicable.
1641 * @param {String} name space
1642 * @param {String} type
1643 * @param {Object} record
1644 * @param {Object} encryption key
1647 decrypt: function (nameSpace, type, record, encryptionKey) {
1650 hexToAlpha = function (hex) {
1652 for (i = 2; i < hex.length; i += 2) {
1653 str += String.fromCharCode(parseInt(hex.substr(i, 2), 16));
1657 orm = this.fetchOrm(nameSpace, type);
1659 for (prop in record) {
1660 var ormp = XT.Orm.getProperty(orm, prop.camelize());
1662 /* Decrypt property if applicable. */
1663 if (ormp && ormp.attr && ormp.attr.isEncrypted) {
1664 if (encryptionKey) {
1665 sql = "select formatbytea(decrypt($1, setbytea($2), 'bf')) as result";
1666 // TODO - Handle not found error.
1668 if (DEBUG && false) {
1669 XT.debug('decrypt prop =', prop);
1670 XT.debug('decrypt sql =', sql);
1671 XT.debug('decrypt values =', [record[prop], encryptionKey]);
1673 result = plv8.execute(sql, [record[prop], encryptionKey])[0].result;
1674 /* we SOMETIMES need to translate from hex here */
1675 if(typeof result === 'string' && result.substring(0, 2) === '\\x') {
1676 result = result ? hexToAlpha(result) : result;
1678 /* in the special case of encrypted credit card numbers, we don't give the
1679 user the full decrypted number EVEN IF they have the encryption key */
1680 if(ormp.attr.isEncrypted === "credit_card_number" && result && result.length >= 4) {
1681 record[prop] = "************" + result.substring(result.length - 4);
1683 record[prop] = result;
1686 record[prop] = '**********';
1689 /* Check recursively. */
1690 } else if (ormp.toOne && ormp.toOne.isNested) {
1691 that.decrypt(nameSpace, ormp.toOne.type, record[prop], encryptionKey);
1693 } else if (ormp.toMany && ormp.toMany.isNested) {
1694 record[prop].map(function (subdata) {
1695 that.decrypt(nameSpace, ormp.toMany.type, subdata, encryptionKey);
1704 Fetches the ORM. Caches the result in this data object, where it can be used
1705 for this request but will be conveniently forgotten between requests.
1707 fetchOrm: function (nameSpace, type) {
1710 recordType = nameSpace + '.'+ type;
1716 res = this._maps.findProperty('recordType', recordType);
1720 ret = XT.Orm.fetch(nameSpace, type);
1722 /* cache the result so we don't requery needlessly */
1723 this._maps.push({ "recordType": recordType, "map": ret});
1729 * Get the oid for a given table name.
1731 * @param {String} table name
1734 getTableOid: function (table) {
1735 var tableName = this.getTableFromNamespacedTable(table).toLowerCase(), /* be generous */
1736 namespace = this.getNamespaceFromNamespacedTable(table),
1738 sql = "select pg_class.oid::integer as oid " +
1739 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1740 "where relname = $1 and nspname = $2";
1743 XT.debug('getTableOid sql =', sql);
1744 XT.debug('getTableOid values =', [tableName, namespace]);
1746 ret = plv8.execute(sql, [tableName, namespace])[0].oid - 0;
1748 // TODO - Handle not found error.
1754 * Get the primary key id for an object based on a passed in natural key.
1756 * @param {Object} Orm
1757 * @param {String} Natural key value
1759 getId: function (orm, value) {
1760 var ncol = XT.Orm.naturalKey(orm, true),
1761 pcol = XT.Orm.primaryKey(orm, true),
1766 if (orm.table.indexOf(".") > 0) {
1767 namespace = orm.table.beforeDot();
1768 table = orm.table.afterDot();
1769 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1770 sql = XT.format(query, [pcol, namespace, table, ncol]);
1772 query = "select %1$I as id from %2$I where %3$I = $1";
1773 sql = XT.format(query, [pcol, orm.table, ncol]);
1777 XT.debug('getId sql =', sql);
1778 XT.debug('getId values =', [value]);
1781 ret = plv8.execute(sql, [value]);
1786 throw new handleError("Primary Key not found on " + orm.table +
1787 " where " + ncol + " = " + value, 400);
1791 getNamespaceFromNamespacedTable: function (fullName) {
1792 return fullName.indexOf(".") > 0 ? fullName.beforeDot() : "public";
1795 getTableFromNamespacedTable: function (fullName) {
1796 return fullName.indexOf(".") > 0 ? fullName.afterDot() : fullName;
1799 getPgTypeFromOrmType: function (schema, table, column) {
1800 var sql = "select data_type from information_schema.columns " +
1802 "and table_schema = $1 " +
1803 "and table_name = $2 " +
1804 "and column_name = $3;",
1806 values = [schema, table, column];
1809 XT.debug('getPgTypeFromOrmType sql =', sql);
1810 XT.debug('getPgTypeFromOrmType values =', values);
1813 pgType = plv8.execute(sql, values);
1814 pgType = pgType && pgType[0] ? pgType[0].data_type : false;
1820 * Get the natural key id for an object based on a passed in primary key.
1822 * @param {Object} Orm
1823 * @param {Number|String} Primary key value
1824 * @param {Boolean} safe Return the original value instead of erroring if no match is found
1826 getNaturalId: function (orm, value, safe) {
1827 var ncol = XT.Orm.naturalKey(orm, true),
1828 pcol = XT.Orm.primaryKey(orm, true),
1833 if (orm.table.indexOf(".") > 0) {
1834 namespace = orm.table.beforeDot();
1835 table = orm.table.afterDot();
1836 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1837 sql = XT.format(query, [ncol, namespace, table, pcol]);
1839 query = "select %1$I as id from %2$I where %3$I = $1";
1840 sql = XT.format(query, [ncol, orm.table, pcol]);
1844 XT.debug('getNaturalId sql =', sql);
1845 XT.debug('getNaturalId values =', [value]);
1848 ret = plv8.execute(sql, [value]);
1855 throw new handleError("Natural Key Not Found: " + orm.nameSpace + "." + orm.type, 400);
1860 * Returns the current version of a record.
1862 * @param {Object} Orm
1863 * @param {Number|String} Record id
1865 getVersion: function (orm, id) {
1866 if (!orm.lockable) { return; }
1869 oid = this.getTableOid(orm.lockTable || orm.table),
1871 sql = 'select ver_etag from xt.ver where ver_table_oid = $1 and ver_record_id = $2;';
1874 XT.debug('getVersion sql = ', sql);
1875 XT.debug('getVersion values = ', [oid, id]);
1877 res = plv8.execute(sql, [oid, id]);
1878 etag = res.length ? res[0].ver_etag : false;
1881 etag = XT.generateUUID();
1882 sql = 'insert into xt.ver (ver_table_oid, ver_record_id, ver_etag) values ($1, $2, $3::uuid);';
1883 // TODO - Handle insert error.
1886 XT.debug('getVersion insert sql = ', sql);
1887 XT.debug('getVersion insert values = ', [oid, id, etag]);
1889 plv8.execute(sql, [oid, id, etag]);
1896 * Fetch an array of records from the database.
1898 * @param {Object} Options
1899 * @param {String} [dataHash.nameSpace] Namespace. Required.
1900 * @param {String} [dataHash.type] Type. Required.
1901 * @param {Array} [dataHash.parameters] Parameters
1902 * @param {Array} [dataHash.orderBy] Order by - optional
1903 * @param {Number} [dataHash.rowLimit] Row limit - optional
1904 * @param {Number} [dataHash.rowOffset] Row offset - optional
1907 fetch: function (options) {
1908 var nameSpace = options.nameSpace,
1909 type = options.type,
1910 query = options.query || {},
1911 encryptionKey = options.encryptionKey,
1912 orderBy = query.orderBy,
1913 orm = this.fetchOrm(nameSpace, type),
1916 parameters = query.parameters,
1917 clause = this.buildClause(nameSpace, type, parameters, orderBy),
1919 pkey = XT.Orm.primaryKey(orm),
1920 pkeyColumn = XT.Orm.primaryKey(orm, true),
1921 nkey = XT.Orm.naturalKey(orm),
1922 limit = query.rowLimit ? XT.format('limit %1$L', [query.rowLimit]) : '',
1923 offset = query.rowOffset ? XT.format('offset %1$L', [query.rowOffset]) : '',
1926 nameSpace: nameSpace,
1936 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};',
1937 sql2 = 'select * from %1$I.%2$I where %3$I in ({ids}) {orderBy}';
1939 /* Validate - don't bother running the query if the user has no privileges. */
1940 if (!this.checkPrivileges(nameSpace, type)) { return []; }
1942 tableNamespace = this.getNamespaceFromNamespacedTable(orm.table);
1943 table = this.getTableFromNamespacedTable(orm.table);
1946 /* Just get the count of rows that match the conditions */
1947 sqlCount = 'select count(distinct t1.%3$I) as count from %1$I.%2$I t1 {joins} where {conditions};';
1948 sqlCount = XT.format(sqlCount, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1949 sqlCount = sqlCount.replace('{joins}', clause.joins)
1950 .replace('{conditions}', clause.conditions);
1953 XT.debug('fetch sqlCount = ', sqlCount);
1954 XT.debug('fetch values = ', clause.parameters);
1957 ret.data = plv8.execute(sqlCount, clause.parameters);
1961 /* Because we query views of views, you can get inconsistent results */
1962 /* when doing limit and offest queries without an order by. Add a default. */
1963 if (limit && offset && (!orderBy || !orderBy.length) && !clause.orderByColumns) {
1964 /* We only want this on sql1, not sql2's clause.orderBy. */
1965 clause.orderByColumns = XT.format('order by t1.%1$I', [pkeyColumn]);
1968 /* Query the model. */
1969 sql1 = XT.format(sql1, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1970 sql1 = sql1.replace('{joins}', clause.joins)
1971 .replace('{conditions}', clause.conditions)
1972 .replace(/{groupBy}/g, clause.groupByColumns)
1973 .replace(/{orderBy}/g, clause.orderByColumns)
1974 .replace('{limit}', limit)
1975 .replace('{offset}', offset);
1978 XT.debug('fetch sql1 = ', sql1);
1979 XT.debug('fetch values = ', clause.parameters);
1982 /* First query for matching ids, then get entire result set. */
1983 /* This improves performance over a direct query on the view due */
1984 /* to the way sorting is handled by the query optimizer */
1985 qry = plv8.execute(sql1, clause.parameters) || [];
1986 if (!qry.length) { return [] };
1987 qry.forEach(function (row) {
1989 idParams.push("$" + counter);
1994 sql_etags = "select ver_etag as etag, ver_record_id as id " +
1996 "where ver_table_oid = ( " +
1997 "select pg_class.oid::integer as oid " +
1998 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1999 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
2000 "where nspname = %1$L and relname = %2$L " +
2002 "and ver_record_id in ({ids})";
2003 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
2004 sql_etags = sql_etags.replace('{ids}', idParams.join());
2007 XT.debug('fetch sql_etags = ', sql_etags);
2008 XT.debug('fetch etags_values = ', JSON.stringify(ids));
2010 etags = plv8.execute(sql_etags, ids) || {};
2014 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize(), pkey]);
2015 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
2016 .replace('{ids}', idParams.join());
2019 XT.debug('fetch sql2 = ', sql2);
2020 XT.debug('fetch values = ', JSON.stringify(ids));
2022 ret.data = plv8.execute(sql2, ids) || [];
2024 for (var i = 0; i < ret.data.length; i++) {
2025 ret.data[i] = this.decrypt(nameSpace, type, ret.data[i], encryptionKey);
2028 /* Add etags to result in pkey->etag format. */
2029 for (var j = 0; j < etags.length; j++) {
2030 if (etags[j].id === ret.data[i][pkey]) {
2031 ret.etags[ret.data[i][nkey]] = etags[j].etag;
2037 this.sanitize(nameSpace, type, ret.data, options);
2043 Fetch a metric value.
2045 @param {String} Metric name
2046 @param {String} Return type 'text', 'boolean' or 'number' (default 'text')
2048 fetchMetric: function (name, type) {
2049 var fn = 'fetchmetrictext';
2050 if (type === 'boolean') {
2051 fn = 'fetchmetricbool';
2052 } else if (type === 'number') {
2053 fn = 'fetchmetricvalue';
2055 return plv8.execute("select " + fn + "($1) as resp", [name])[0].resp;
2059 * Retreives a record from the database. If the user does not have appropriate privileges an
2060 * error will be thrown unless the `silentError` option is passed.
2062 * If `context` is passed as an option then a record will only be returned if it exists in the context (parent)
2063 * record which itself must be accessible by the effective user.
2065 * @param {Object} options
2066 * @param {String} [options.nameSpace] Namespace. Required.
2067 * @param {String} [options.type] Type. Required.
2068 * @param {Number} [options.id] Record id. Required.
2069 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
2070 * @param {String} [options.encryptionKey] Encryption key
2071 * @param {Boolean} [options.silentError=false] Silence errors
2072 * @param {Object} [options.context] Context
2073 * @param {String} [options.context.nameSpace] Context namespace.
2074 * @param {String} [options.context.type] The type of context object.
2075 * @param {String} [options.context.value] The value of the context's primary key.
2076 * @param {String} [options.context.relation] The name of the attribute on the type to which this record is related.
2079 retrieveRecord: function (options) {
2080 options = options ? options : {};
2081 options.obtainLock = false;
2083 var id = options.id,
2084 nameSpace = options.nameSpace,
2085 type = options.type,
2086 map = this.fetchOrm(nameSpace, type),
2087 context = options.context,
2088 encryptionKey = options.encryptionKey,
2090 lockTable = map.lockTable || map.table,
2091 nkey = XT.Orm.naturalKey(map),
2093 pkey = XT.Orm.primaryKey(map),
2095 nameSpace: nameSpace,
2102 throw new Error('No key found for {nameSpace}.{type}'
2103 .replace("{nameSpace}", nameSpace)
2104 .replace("{type}", type));
2107 /* If this object uses a natural key, go get the primary key id. */
2109 id = this.getId(map, id);
2115 /* Context means search for this record inside another. */
2117 context.nameSpace = context.nameSpace || context.recordType.beforeDot();
2118 context.type = context.type || context.recordType.afterDot()
2119 context.map = this.fetchOrm(context.nameSpace, context.type);
2120 context.prop = XT.Orm.getProperty(context.map, context.relation);
2121 context.pertinentExtension = XT.Orm.getProperty(context.map, context.relation, true);
2122 context.underlyingTable = context.pertinentExtension.table,
2123 context.underlyingNameSpace = this.getNamespaceFromNamespacedTable(context.underlyingTable);
2124 context.underlyingType = this.getTableFromNamespacedTable(context.underlyingTable);
2125 context.fkey = context.prop.toMany.inverse;
2126 context.fkeyColumn = context.prop.toMany.column;
2127 context.pkey = XT.Orm.naturalKey(context.map) || XT.Orm.primaryKey(context.map);
2128 params.attribute = context.pkey;
2129 params.value = context.value;
2131 join = 'join %1$I.%2$I on (%1$I.%2$I.%3$I = %4$I.%5$I)';
2132 join = XT.format(join, [
2133 context.underlyingNameSpace,
2134 context.underlyingType,
2141 /* Validate - don't bother running the query if the user has no privileges. */
2142 if(!options.superUser && !context && !this.checkPrivileges(nameSpace, type)) {
2143 if (options.silentError) {
2146 throw new handleError("Unauthorized", 401);
2150 ret.etag = this.getVersion(map, id);
2152 /* Obtain lock if required. */
2154 ret.lock = this.tryLock(lockTable, id, options);
2158 sql = 'select %1$I.* from %2$I.%1$I {join} where %1$I.%3$I = $1;';
2159 sql = sql.replace(/{join}/, join);
2160 sql = XT.format(sql, [type.decamelize(), nameSpace.decamelize(), pkey]);
2162 /* Query the map. */
2164 XT.debug('retrieveRecord sql = ', sql);
2165 XT.debug('retrieveRecord values = ', [id]);
2167 ret.data = plv8.execute(sql, [id])[0] || {};
2170 /* Check privileges again, this time against record specific criteria where applicable. */
2171 if(!options.superUser && !this.checkPrivileges(nameSpace, type, ret.data)) {
2172 if (options.silentError) {
2175 throw new handleError("Unauthorized", 401);
2178 /* Decrypt result where applicable. */
2179 ret.data = this.decrypt(nameSpace, type, ret.data, encryptionKey);
2182 this.sanitize(nameSpace, type, ret.data, options);
2184 /* Return the results. */
2189 * Remove unprivileged attributes, primary and foreign keys from the data.
2190 * Only removes the primary key if a natural key has been specified in the ORM.
2191 * Also format for printing using XT.format functions if printFormat=true'
2193 * @param {String} Namespace
2194 * @param {String} Type
2195 * @param {Object|Array} Data
2196 * @param {Object} Options
2197 * @param {Boolean} [options.includeKeys=false] Do not remove primary and foreign keys.
2198 * @param {Boolean} [options.superUser=false] Do not remove unprivileged attributes.
2199 * @param {Boolean} [options.printFormat=true] Format for printing.
2201 sanitize: function (nameSpace, type, data, options) {
2202 options = options || {};
2203 if (options.includeKeys && options.superUser) { return; }
2204 if (XT.typeOf(data) !== "array") { data = [data]; }
2205 var orm = this.fetchOrm(nameSpace, type),
2206 pkey = XT.Orm.primaryKey(orm),
2207 nkey = XT.Orm.naturalKey(orm),
2208 props = orm.properties,
2209 attrPriv = orm.privileges && orm.privileges.attribute ?
2210 orm.privileges.attribute : false,
2211 inclKeys = options.includeKeys,
2212 superUser = options.superUser,
2213 printFormat = options.printFormat,
2224 check = function (p) {
2225 return p.name === itemAttr;
2228 for (var c = 0; c < data.length; c++) {
2231 /* Remove primary key if applicable */
2232 if (!inclKeys && nkey && nkey !== pkey) { delete item[pkey]; }
2234 for (itemAttr in item) {
2235 if (!item.hasOwnProperty(itemAttr)) {
2238 filteredProps = orm.properties.filter(check);
2240 if (filteredProps.length === 0 && orm.extensions.length > 0) {
2241 /* Try to get the orm prop from an extension if it's not in the core*/
2242 orm.extensions.forEach(function (ext) {
2243 if (filteredProps.length === 0) {
2244 filteredProps = ext.properties.filter(check);
2249 /* Remove attributes not found in the ORM */
2250 if (filteredProps.length === 0) {
2251 delete item[itemAttr];
2253 prop = filteredProps[0];
2256 /* Remove unprivileged attribute if applicable */
2257 if (!superUser && attrPriv && attrPriv[prop.name] &&
2258 (attrPriv[prop.name].view !== undefined) &&
2259 !this.checkPrivilege(attrPriv[prop.name].view)) {
2260 delete item[prop.name];
2263 /* Format for printing if printFormat and not an object */
2264 if (printFormat && !prop.toOne && !prop.toMany) {
2265 switch(prop.attr.type) {
2268 preOffsetDate = item[itemAttr];
2269 offsetDate = preOffsetDate &&
2270 new Date(preOffsetDate.valueOf() + 60000 * preOffsetDate.getTimezoneOffset());
2271 item[itemAttr] = XT.formatDate(offsetDate).formatdate;
2274 item[itemAttr] = XT.formatCost(item[itemAttr]).formatcost.toString();
2277 item[itemAttr] = XT.formatNumeric(item[itemAttr], "").formatnumeric.toString();
2280 item[itemAttr] = XT.formatMoney(item[itemAttr]).formatmoney.toString();
2283 item[itemAttr] = XT.formatSalesPrice(item[itemAttr]).formatsalesprice.toString();
2285 case "PurchasePrice":
2286 item[itemAttr] = XT.formatPurchPrice(item[itemAttr]).formatpurchprice.toString();
2288 case "ExtendedPrice":
2289 item[itemAttr] = XT.formatExtPrice(item[itemAttr]).formatextprice.toString();
2292 item[itemAttr] = XT.formatQty(item[itemAttr]).formatqty.toString();
2295 item[itemAttr] = XT.formatQtyPer(item[itemAttr]).formatqtyper.toString();
2297 case "UnitRatioScale":
2298 item[itemAttr] = XT.formatRatio(item[itemAttr]).formatratio.toString();
2301 item[itemAttr] = XT.formatPrcnt(item[itemAttr]).formatprcnt.toString();
2304 item[itemAttr] = XT.formatWeight(item[itemAttr]).formatweight.toString();
2307 item[itemAttr] = (item[itemAttr] || "").toString();
2311 /* Handle composite types */
2312 if (prop.toOne && prop.toOne.isNested && item[prop.name]) {
2313 this.sanitize(nameSpace, prop.toOne.type, item[prop.name], options);
2314 } else if (prop.toMany && prop.toMany.isNested && item[prop.name]) {
2315 for (var n = 0; n < item[prop.name].length; n++) {
2316 val = item[prop.name][n];
2318 /* Remove foreign key if applicable */
2319 if (!inclKeys) { delete val[prop.toMany.inverse]; }
2320 this.sanitize(nameSpace, prop.toMany.type, val, options);
2328 * Returns a array of key value pairs of metric settings that correspond with an array of passed keys.
2330 * @param {Array} array of metric names
2333 retrieveMetrics: function (keys) {
2338 sql = 'select metric_name as setting, metric_value as value '
2340 + 'where metric_name in ({literals})';
2342 for (var i = 0; i < keys.length; i++) {
2343 literals[i] = "%" + (i + 1) + "$L";
2346 sql = sql.replace(/{literals}/, literals.join(','));
2347 sql = XT.format(sql, keys)
2350 XT.debug('retrieveMetrics sql = ', sql);
2352 qry = plv8.execute(sql);
2354 /* Recast where applicable. */
2355 for (var i = 0; i < qry.length; i++) {
2356 prop = qry[i].setting;
2357 if(qry[i].value === 't') { ret[prop] = true; }
2358 else if(qry[i].value === 'f') { ret[prop] = false }
2359 else if(!isNaN(qry[i].value)) { ret[prop] = qry[i].value - 0; }
2360 else { ret[prop] = qry[i].value; }
2363 /* Make sure there is a result at all times */
2364 keys.forEach(function (key) {
2365 if (ret[key] === undefined) { ret[key] = null; }
2372 * Creates and returns a lock for a given table. Defaults to a time based lock of 30 seconds
2373 * unless aternate timeout option or process id (pid) is passed. If a pid is passed, the lock
2374 * is considered infinite as long as the pid is valid. If a previous lock key is passed and it is
2375 * valid, a new lock will be granted.
2377 * @param {String | Number} Table name or oid
2378 * @param {Number} Record id
2379 * @param {Object} Options
2380 * @param {Number} [options.timeout=30]
2381 * @param {Number} [options.pid] Process id
2382 * @param {Number} [options.key] Key
2383 * @param {Boolean} [options.obtainLock=true] If false, only checks for existing lock
2385 tryLock: function (table, id, options) {
2386 options = options ? options : {};
2388 var deleteSql = "delete from xt.lock where lock_id = $1;",
2389 timeout = options.timeout || 30,
2390 expires = new Date(),
2392 insertSqlExp = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_expires) " +
2393 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2394 insertSqlPid = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_pid) " +
2395 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2400 pid = options.pid || null,
2401 pidSql = "select usename, procpid " +
2402 "from pg_stat_activity " +
2403 "where datname=current_database() " +
2404 " and usename=$1 " +
2407 selectSql = "select * " +
2409 "where lock_table_oid = $1 " +
2410 " and lock_record_id = $2;",
2411 username = XT.username;
2413 /* If passed a table name, look up the oid. */
2414 oid = typeof table === "string" ? this.getTableOid(table) : table;
2416 if (DEBUG) XT.debug("Trying lock table", [oid, id]);
2418 /* See if there are existing lock(s) for this record. */
2420 XT.debug('tryLock sql = ', selectSql);
2421 XT.debug('tryLock values = ', [oid, id]);
2423 query = plv8.execute(selectSql, [oid, id]);
2425 /* Validate result */
2426 if (query.length > 0) {
2427 while (query.length) {
2428 lock = query.shift();
2430 /* See if we are confirming our own lock. */
2431 if (options.key && options.key === lock.lock_id) {
2432 /* Go on and we'll get a new lock. */
2434 /* Make sure if they are pid locks users is still connected. */
2435 } else if (lock.lock_pid) {
2437 XT.debug('tryLock sql = ', pidSql);
2438 XT.debug('tryLock values = ', [lock.lock_username, lock.lock_pid]);
2440 pcheck = plv8.execute(pidSql, [lock.lock_username, lock.lock_pid]);
2441 if (pcheck.length) { break; } /* valid lock */
2443 lockExp = new Date(lock.lock_expires);
2444 if (DEBUG) { XT.debug("Lock found", [lockExp > expires, lockExp, expires]); }
2445 if (lockExp > expires) { break; } /* valid lock */
2448 /* Delete invalid or expired lock. */
2450 XT.debug('tryLock sql = ', deleteSql);
2451 XT.debug('tryLock values = ', [lock.lock_id]);
2453 plv8.execute(deleteSql, [lock.lock_id]);
2458 if (DEBUG) XT.debug("Lock found", lock.lock_username);
2461 username: lock.lock_username,
2462 effective: lock.lock_effective
2467 if (options.obtainLock === false) { return; }
2469 if (DEBUG) { XT.debug("Creating lock."); }
2470 if (DEBUG) { XT.debug('tryLock sql = ', insertSqlPid); }
2473 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, pid]); }
2474 lock = plv8.execute(insertSqlPid, [oid, id, username, pid])[0];
2476 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2477 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, expires]); }
2478 lock = plv8.execute(insertSqlExp, [oid, id, username, expires])[0];
2481 if (DEBUG) { XT.debug("Lock returned is", lock.lock_id); }
2485 effective: lock.lock_effective,
2491 * Release a lock. Pass either options with a key, or table, id and username.
2493 * @param {Object} Options: key or table and id
2495 releaseLock: function (options) {
2497 sqlKey = 'delete from xt.lock where lock_id = $1;',
2498 sqlUsr = 'delete from xt.lock where lock_table_oid = $1 and lock_record_id = $2 and lock_username = $3;',
2499 username = XT.username;
2503 XT.debug('releaseLock sql = ', sqlKey);
2504 XT.debug('releaseLock values = ', [options.key]);
2506 plv8.execute(sqlKey, [options.key]);
2508 oid = typeof options.table === "string" ? this.getTableOid(options.table) : options.table;
2511 XT.debug('releaseLock sql = ', sqlUsr);
2512 XT.debug('releaseLock values = ', [oid, options.id, username]);
2514 plv8.execute(sqlUsr, [oid, options.id, username]);
2521 * Renew a lock. Defaults to rewing the lock for 30 seconds.
2523 * @param {Number} Key
2524 * @params {Object} Options: timeout
2525 * @returns {Date} New expiration or false.
2527 renewLock: function (key, options) {
2528 var expires = new Date(),
2530 selectSql = "select * from xt.lock where lock_id = $1;",
2531 timeout = options && options.timeout ? options.timeout : 30,
2532 updateSql = "update xt.lock set lock_expires = $1 where lock_id = $2;";
2534 if (typeof key !== "number") { return false; }
2535 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2538 XT.debug('renewLock sql = ', selectSql);
2539 XT.debug('renewLock values = ', [key]);
2541 query = plv8.execute(selectSql, [key]);
2545 XT.debug('renewLock sql = ', updateSql);
2546 XT.debug('renewLock values = ', [expires, key]);
2548 plv8.execute(updateSql, [expires, key]);