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 current database server version.
1732 * If the optional precision argument is passed, return the first prec
1733 * fields of the full version number.
1736 * var x = getPgVersion(1), // '9'
1737 * xy = getPgVersion(2), // '9.1'
1738 * xyz = getPgVersion(3), // '9.1.3'
1739 * all = getPgVersion(); // '9.1.3'
1741 * @param {Number} proc - optional precision
1742 * @returns {String} X[.Y[.Z]]
1744 getPgVersion: function (prec) {
1745 var q = plv8.execute("select setting from pg_settings " +
1746 "where name='server_version';"),
1749 if (typeof prec === 'number') {
1750 ret = ret.split(".").slice(0,prec).join(".");
1756 * Get the oid for a given table name.
1758 * @param {String} table name
1761 getTableOid: function (table) {
1762 var tableName = this.getTableFromNamespacedTable(table).toLowerCase(), /* be generous */
1763 namespace = this.getNamespaceFromNamespacedTable(table),
1765 sql = "select pg_class.oid::integer as oid " +
1766 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1767 "where relname = $1 and nspname = $2";
1770 XT.debug('getTableOid sql =', sql);
1771 XT.debug('getTableOid values =', [tableName, namespace]);
1773 ret = plv8.execute(sql, [tableName, namespace])[0].oid - 0;
1775 // TODO - Handle not found error.
1781 * Get the primary key id for an object based on a passed in natural key.
1783 * @param {Object} Orm
1784 * @param {String} Natural key value
1786 getId: function (orm, value) {
1787 var ncol = XT.Orm.naturalKey(orm, true),
1788 pcol = XT.Orm.primaryKey(orm, true),
1793 if (orm.table.indexOf(".") > 0) {
1794 namespace = orm.table.beforeDot();
1795 table = orm.table.afterDot();
1796 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1797 sql = XT.format(query, [pcol, namespace, table, ncol]);
1799 query = "select %1$I as id from %2$I where %3$I = $1";
1800 sql = XT.format(query, [pcol, orm.table, ncol]);
1804 XT.debug('getId sql =', sql);
1805 XT.debug('getId values =', [value]);
1808 ret = plv8.execute(sql, [value]);
1813 throw new handleError("Primary Key not found on " + orm.table +
1814 " where " + ncol + " = " + value, 400);
1818 getNamespaceFromNamespacedTable: function (fullName) {
1819 return fullName.indexOf(".") > 0 ? fullName.beforeDot() : "public";
1822 getTableFromNamespacedTable: function (fullName) {
1823 return fullName.indexOf(".") > 0 ? fullName.afterDot() : fullName;
1826 getPgTypeFromOrmType: function (schema, table, column) {
1827 var sql = "select data_type from information_schema.columns " +
1829 "and table_schema = $1 " +
1830 "and table_name = $2 " +
1831 "and column_name = $3;",
1833 values = [schema, table, column];
1836 XT.debug('getPgTypeFromOrmType sql =', sql);
1837 XT.debug('getPgTypeFromOrmType values =', values);
1840 pgType = plv8.execute(sql, values);
1841 pgType = pgType && pgType[0] ? pgType[0].data_type : false;
1847 * Get the natural key id for an object based on a passed in primary key.
1849 * @param {Object} Orm
1850 * @param {Number|String} Primary key value
1851 * @param {Boolean} safe Return the original value instead of erroring if no match is found
1853 getNaturalId: function (orm, value, safe) {
1854 var ncol = XT.Orm.naturalKey(orm, true),
1855 pcol = XT.Orm.primaryKey(orm, true),
1860 if (orm.table.indexOf(".") > 0) {
1861 namespace = orm.table.beforeDot();
1862 table = orm.table.afterDot();
1863 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1864 sql = XT.format(query, [ncol, namespace, table, pcol]);
1866 query = "select %1$I as id from %2$I where %3$I = $1";
1867 sql = XT.format(query, [ncol, orm.table, pcol]);
1871 XT.debug('getNaturalId sql =', sql);
1872 XT.debug('getNaturalId values =', [value]);
1875 ret = plv8.execute(sql, [value]);
1882 throw new handleError("Natural Key Not Found: " + orm.nameSpace + "." + orm.type, 400);
1887 * Returns the current version of a record.
1889 * @param {Object} Orm
1890 * @param {Number|String} Record id
1892 getVersion: function (orm, id) {
1893 if (!orm.lockable) { return; }
1896 oid = this.getTableOid(orm.lockTable || orm.table),
1898 sql = 'select ver_etag from xt.ver where ver_table_oid = $1 and ver_record_id = $2;';
1901 XT.debug('getVersion sql = ', sql);
1902 XT.debug('getVersion values = ', [oid, id]);
1904 res = plv8.execute(sql, [oid, id]);
1905 etag = res.length ? res[0].ver_etag : false;
1908 etag = XT.generateUUID();
1909 sql = 'insert into xt.ver (ver_table_oid, ver_record_id, ver_etag) values ($1, $2, $3::uuid);';
1910 // TODO - Handle insert error.
1913 XT.debug('getVersion insert sql = ', sql);
1914 XT.debug('getVersion insert values = ', [oid, id, etag]);
1916 plv8.execute(sql, [oid, id, etag]);
1923 * Fetch an array of records from the database.
1925 * @param {Object} Options
1926 * @param {String} [dataHash.nameSpace] Namespace. Required.
1927 * @param {String} [dataHash.type] Type. Required.
1928 * @param {Array} [dataHash.parameters] Parameters
1929 * @param {Array} [dataHash.orderBy] Order by - optional
1930 * @param {Number} [dataHash.rowLimit] Row limit - optional
1931 * @param {Number} [dataHash.rowOffset] Row offset - optional
1934 fetch: function (options) {
1935 var nameSpace = options.nameSpace,
1936 type = options.type,
1937 query = options.query || {},
1938 encryptionKey = options.encryptionKey,
1939 orderBy = query.orderBy,
1940 orm = this.fetchOrm(nameSpace, type),
1943 parameters = query.parameters,
1944 clause = this.buildClause(nameSpace, type, parameters, orderBy),
1946 pkey = XT.Orm.primaryKey(orm),
1947 pkeyColumn = XT.Orm.primaryKey(orm, true),
1948 nkey = XT.Orm.naturalKey(orm),
1949 limit = query.rowLimit ? XT.format('limit %1$L', [query.rowLimit]) : '',
1950 offset = query.rowOffset ? XT.format('offset %1$L', [query.rowOffset]) : '',
1953 nameSpace: nameSpace,
1963 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};',
1964 sql2 = 'select * from %1$I.%2$I where %3$I in ({ids}) {orderBy}';
1966 /* Validate - don't bother running the query if the user has no privileges. */
1967 if (!this.checkPrivileges(nameSpace, type)) { return []; }
1969 tableNamespace = this.getNamespaceFromNamespacedTable(orm.table);
1970 table = this.getTableFromNamespacedTable(orm.table);
1973 /* Just get the count of rows that match the conditions */
1974 sqlCount = 'select count(distinct t1.%3$I) as count from %1$I.%2$I t1 {joins} where {conditions};';
1975 sqlCount = XT.format(sqlCount, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1976 sqlCount = sqlCount.replace('{joins}', clause.joins)
1977 .replace('{conditions}', clause.conditions);
1980 XT.debug('fetch sqlCount = ', sqlCount);
1981 XT.debug('fetch values = ', clause.parameters);
1984 ret.data = plv8.execute(sqlCount, clause.parameters);
1988 /* Because we query views of views, you can get inconsistent results */
1989 /* when doing limit and offest queries without an order by. Add a default. */
1990 if (limit && offset && (!orderBy || !orderBy.length) && !clause.orderByColumns) {
1991 /* We only want this on sql1, not sql2's clause.orderBy. */
1992 clause.orderByColumns = XT.format('order by t1.%1$I', [pkeyColumn]);
1995 /* Query the model. */
1996 sql1 = XT.format(sql1, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1997 sql1 = sql1.replace('{joins}', clause.joins)
1998 .replace('{conditions}', clause.conditions)
1999 .replace(/{groupBy}/g, clause.groupByColumns)
2000 .replace(/{orderBy}/g, clause.orderByColumns)
2001 .replace('{limit}', limit)
2002 .replace('{offset}', offset);
2005 XT.debug('fetch sql1 = ', sql1);
2006 XT.debug('fetch values = ', clause.parameters);
2009 /* First query for matching ids, then get entire result set. */
2010 /* This improves performance over a direct query on the view due */
2011 /* to the way sorting is handled by the query optimizer */
2012 qry = plv8.execute(sql1, clause.parameters) || [];
2013 if (!qry.length) { return [] };
2014 qry.forEach(function (row) {
2016 idParams.push("$" + counter);
2021 sql_etags = "select ver_etag as etag, ver_record_id as id " +
2023 "where ver_table_oid = ( " +
2024 "select pg_class.oid::integer as oid " +
2025 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
2026 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
2027 "where nspname = %1$L and relname = %2$L " +
2029 "and ver_record_id in ({ids})";
2030 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
2031 sql_etags = sql_etags.replace('{ids}', idParams.join());
2034 XT.debug('fetch sql_etags = ', sql_etags);
2035 XT.debug('fetch etags_values = ', JSON.stringify(ids));
2037 etags = plv8.execute(sql_etags, ids) || {};
2041 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize(), pkey]);
2042 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
2043 .replace('{ids}', idParams.join());
2046 XT.debug('fetch sql2 = ', sql2);
2047 XT.debug('fetch values = ', JSON.stringify(ids));
2049 ret.data = plv8.execute(sql2, ids) || [];
2051 for (var i = 0; i < ret.data.length; i++) {
2052 ret.data[i] = this.decrypt(nameSpace, type, ret.data[i], encryptionKey);
2055 /* Add etags to result in pkey->etag format. */
2056 for (var j = 0; j < etags.length; j++) {
2057 if (etags[j].id === ret.data[i][pkey]) {
2058 ret.etags[ret.data[i][nkey]] = etags[j].etag;
2064 this.sanitize(nameSpace, type, ret.data, options);
2070 Fetch a metric value.
2072 @param {String} Metric name
2073 @param {String} Return type 'text', 'boolean' or 'number' (default 'text')
2075 fetchMetric: function (name, type) {
2076 var fn = 'fetchmetrictext';
2077 if (type === 'boolean') {
2078 fn = 'fetchmetricbool';
2079 } else if (type === 'number') {
2080 fn = 'fetchmetricvalue';
2082 return plv8.execute("select " + fn + "($1) as resp", [name])[0].resp;
2086 * Retreives a record from the database. If the user does not have appropriate privileges an
2087 * error will be thrown unless the `silentError` option is passed.
2089 * If `context` is passed as an option then a record will only be returned if it exists in the context (parent)
2090 * record which itself must be accessible by the effective user.
2092 * @param {Object} options
2093 * @param {String} [options.nameSpace] Namespace. Required.
2094 * @param {String} [options.type] Type. Required.
2095 * @param {Number} [options.id] Record id. Required.
2096 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
2097 * @param {String} [options.encryptionKey] Encryption key
2098 * @param {Boolean} [options.silentError=false] Silence errors
2099 * @param {Object} [options.context] Context
2100 * @param {String} [options.context.nameSpace] Context namespace.
2101 * @param {String} [options.context.type] The type of context object.
2102 * @param {String} [options.context.value] The value of the context's primary key.
2103 * @param {String} [options.context.relation] The name of the attribute on the type to which this record is related.
2106 retrieveRecord: function (options) {
2107 options = options ? options : {};
2108 options.obtainLock = false;
2110 var id = options.id,
2111 nameSpace = options.nameSpace,
2112 type = options.type,
2113 map = this.fetchOrm(nameSpace, type),
2114 context = options.context,
2115 encryptionKey = options.encryptionKey,
2117 lockTable = map.lockTable || map.table,
2118 nkey = XT.Orm.naturalKey(map),
2120 pkey = XT.Orm.primaryKey(map),
2122 nameSpace: nameSpace,
2129 throw new Error('No key found for {nameSpace}.{type}'
2130 .replace("{nameSpace}", nameSpace)
2131 .replace("{type}", type));
2134 /* If this object uses a natural key, go get the primary key id. */
2136 id = this.getId(map, id);
2142 /* Context means search for this record inside another. */
2144 context.nameSpace = context.nameSpace || context.recordType.beforeDot();
2145 context.type = context.type || context.recordType.afterDot()
2146 context.map = this.fetchOrm(context.nameSpace, context.type);
2147 context.prop = XT.Orm.getProperty(context.map, context.relation);
2148 context.pertinentExtension = XT.Orm.getProperty(context.map, context.relation, true);
2149 context.underlyingTable = context.pertinentExtension.table,
2150 context.underlyingNameSpace = this.getNamespaceFromNamespacedTable(context.underlyingTable);
2151 context.underlyingType = this.getTableFromNamespacedTable(context.underlyingTable);
2152 context.fkey = context.prop.toMany.inverse;
2153 context.fkeyColumn = context.prop.toMany.column;
2154 context.pkey = XT.Orm.naturalKey(context.map) || XT.Orm.primaryKey(context.map);
2155 params.attribute = context.pkey;
2156 params.value = context.value;
2158 join = 'join %1$I.%2$I on (%1$I.%2$I.%3$I = %4$I.%5$I)';
2159 join = XT.format(join, [
2160 context.underlyingNameSpace,
2161 context.underlyingType,
2168 /* Validate - don't bother running the query if the user has no privileges. */
2169 if(!options.superUser && !context && !this.checkPrivileges(nameSpace, type)) {
2170 if (options.silentError) {
2173 throw new handleError("Unauthorized", 401);
2177 ret.etag = this.getVersion(map, id);
2179 /* Obtain lock if required. */
2181 ret.lock = this.tryLock(lockTable, id, options);
2185 sql = 'select %1$I.* from %2$I.%1$I {join} where %1$I.%3$I = $1;';
2186 sql = sql.replace(/{join}/, join);
2187 sql = XT.format(sql, [type.decamelize(), nameSpace.decamelize(), pkey]);
2189 /* Query the map. */
2191 XT.debug('retrieveRecord sql = ', sql);
2192 XT.debug('retrieveRecord values = ', [id]);
2194 ret.data = plv8.execute(sql, [id])[0] || {};
2197 /* Check privileges again, this time against record specific criteria where applicable. */
2198 if(!options.superUser && !this.checkPrivileges(nameSpace, type, ret.data)) {
2199 if (options.silentError) {
2202 throw new handleError("Unauthorized", 401);
2205 /* Decrypt result where applicable. */
2206 ret.data = this.decrypt(nameSpace, type, ret.data, encryptionKey);
2209 this.sanitize(nameSpace, type, ret.data, options);
2211 /* Return the results. */
2216 * Remove unprivileged attributes, primary and foreign keys from the data.
2217 * Only removes the primary key if a natural key has been specified in the ORM.
2218 * Also format for printing using XT.format functions if printFormat=true'
2220 * @param {String} Namespace
2221 * @param {String} Type
2222 * @param {Object|Array} Data
2223 * @param {Object} Options
2224 * @param {Boolean} [options.includeKeys=false] Do not remove primary and foreign keys.
2225 * @param {Boolean} [options.superUser=false] Do not remove unprivileged attributes.
2226 * @param {Boolean} [options.printFormat=true] Format for printing.
2228 sanitize: function (nameSpace, type, data, options) {
2229 options = options || {};
2230 if (options.includeKeys && options.superUser) { return; }
2231 if (XT.typeOf(data) !== "array") { data = [data]; }
2232 var orm = this.fetchOrm(nameSpace, type),
2233 pkey = XT.Orm.primaryKey(orm),
2234 nkey = XT.Orm.naturalKey(orm),
2235 props = orm.properties,
2236 attrPriv = orm.privileges && orm.privileges.attribute ?
2237 orm.privileges.attribute : false,
2238 inclKeys = options.includeKeys,
2239 superUser = options.superUser,
2240 printFormat = options.printFormat,
2251 check = function (p) {
2252 return p.name === itemAttr;
2255 for (var c = 0; c < data.length; c++) {
2258 /* Remove primary key if applicable */
2259 if (!inclKeys && nkey && nkey !== pkey) { delete item[pkey]; }
2261 for (itemAttr in item) {
2262 if (!item.hasOwnProperty(itemAttr)) {
2265 filteredProps = orm.properties.filter(check);
2267 if (filteredProps.length === 0 && orm.extensions.length > 0) {
2268 /* Try to get the orm prop from an extension if it's not in the core*/
2269 orm.extensions.forEach(function (ext) {
2270 if (filteredProps.length === 0) {
2271 filteredProps = ext.properties.filter(check);
2276 /* Remove attributes not found in the ORM */
2277 if (filteredProps.length === 0) {
2278 delete item[itemAttr];
2280 prop = filteredProps[0];
2283 /* Remove unprivileged attribute if applicable */
2284 if (!superUser && attrPriv && attrPriv[prop.name] &&
2285 (attrPriv[prop.name].view !== undefined) &&
2286 !this.checkPrivilege(attrPriv[prop.name].view)) {
2287 delete item[prop.name];
2290 /* Format for printing if printFormat and not an object */
2291 if (printFormat && !prop.toOne && !prop.toMany) {
2292 switch(prop.attr.type) {
2295 preOffsetDate = item[itemAttr];
2296 offsetDate = preOffsetDate &&
2297 new Date(preOffsetDate.valueOf() + 60000 * preOffsetDate.getTimezoneOffset());
2298 item[itemAttr] = XT.formatDate(offsetDate).formatdate;
2301 item[itemAttr] = XT.formatCost(item[itemAttr]).formatcost.toString();
2304 item[itemAttr] = XT.formatNumeric(item[itemAttr], "").formatnumeric.toString();
2307 item[itemAttr] = XT.formatMoney(item[itemAttr]).formatmoney.toString();
2310 item[itemAttr] = XT.formatSalesPrice(item[itemAttr]).formatsalesprice.toString();
2312 case "PurchasePrice":
2313 item[itemAttr] = XT.formatPurchPrice(item[itemAttr]).formatpurchprice.toString();
2315 case "ExtendedPrice":
2316 item[itemAttr] = XT.formatExtPrice(item[itemAttr]).formatextprice.toString();
2319 item[itemAttr] = XT.formatQty(item[itemAttr]).formatqty.toString();
2322 item[itemAttr] = XT.formatQtyPer(item[itemAttr]).formatqtyper.toString();
2324 case "UnitRatioScale":
2325 item[itemAttr] = XT.formatRatio(item[itemAttr]).formatratio.toString();
2328 item[itemAttr] = XT.formatPrcnt(item[itemAttr]).formatprcnt.toString();
2331 item[itemAttr] = XT.formatWeight(item[itemAttr]).formatweight.toString();
2334 item[itemAttr] = (item[itemAttr] || "").toString();
2338 /* Handle composite types */
2339 if (prop.toOne && prop.toOne.isNested && item[prop.name]) {
2340 this.sanitize(nameSpace, prop.toOne.type, item[prop.name], options);
2341 } else if (prop.toMany && prop.toMany.isNested && item[prop.name]) {
2342 for (var n = 0; n < item[prop.name].length; n++) {
2343 val = item[prop.name][n];
2345 /* Remove foreign key if applicable */
2346 if (!inclKeys) { delete val[prop.toMany.inverse]; }
2347 this.sanitize(nameSpace, prop.toMany.type, val, options);
2355 * Returns a array of key value pairs of metric settings that correspond with an array of passed keys.
2357 * @param {Array} array of metric names
2360 retrieveMetrics: function (keys) {
2365 sql = 'select metric_name as setting, metric_value as value '
2367 + 'where metric_name in ({literals})';
2369 for (var i = 0; i < keys.length; i++) {
2370 literals[i] = "%" + (i + 1) + "$L";
2373 sql = sql.replace(/{literals}/, literals.join(','));
2374 sql = XT.format(sql, keys)
2377 XT.debug('retrieveMetrics sql = ', sql);
2379 qry = plv8.execute(sql);
2381 /* Recast where applicable. */
2382 for (var i = 0; i < qry.length; i++) {
2383 prop = qry[i].setting;
2384 if(qry[i].value === 't') { ret[prop] = true; }
2385 else if(qry[i].value === 'f') { ret[prop] = false }
2386 else if(!isNaN(qry[i].value)) { ret[prop] = qry[i].value - 0; }
2387 else { ret[prop] = qry[i].value; }
2390 /* Make sure there is a result at all times */
2391 keys.forEach(function (key) {
2392 if (ret[key] === undefined) { ret[key] = null; }
2399 * Creates and returns a lock for a given table. Defaults to a time based lock of 30 seconds
2400 * unless aternate timeout option or process id (pid) is passed. If a pid is passed, the lock
2401 * is considered infinite as long as the pid is valid. If a previous lock key is passed and it is
2402 * valid, a new lock will be granted.
2404 * @param {String | Number} Table name or oid
2405 * @param {Number} Record id
2406 * @param {Object} Options
2407 * @param {Number} [options.timeout=30]
2408 * @param {Number} [options.pid] Process id
2409 * @param {Number} [options.key] Key
2410 * @param {Boolean} [options.obtainLock=true] If false, only checks for existing lock
2412 tryLock: function (table, id, options) {
2413 options = options ? options : {};
2415 var deleteSql = "delete from xt.lock where lock_id = $1;",
2416 timeout = options.timeout || 30,
2417 expires = new Date(),
2419 insertSqlExp = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_expires) " +
2420 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2421 insertSqlPid = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_pid) " +
2422 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2427 pgver = 0 + XT.Data.getPgVersion(2),
2428 pid = options.pid || null,
2429 pidcol = (pgver < 9.2) ? "procpid" : "pid",
2430 pidSql = "select usename, {pidcol} " +
2431 "from pg_stat_activity " +
2432 "where datname=current_database() " +
2433 " and usename=$1 " +
2434 " and procpid=$2;".replace("{pidcol}", pidcol),
2436 selectSql = "select * " +
2438 "where lock_table_oid = $1 " +
2439 " and lock_record_id = $2;",
2440 username = XT.username;
2442 /* If passed a table name, look up the oid. */
2443 oid = typeof table === "string" ? this.getTableOid(table) : table;
2445 if (DEBUG) XT.debug("Trying lock table", [oid, id]);
2447 /* See if there are existing lock(s) for this record. */
2449 XT.debug('tryLock sql = ', selectSql);
2450 XT.debug('tryLock values = ', [oid, id]);
2452 query = plv8.execute(selectSql, [oid, id]);
2454 /* Validate result */
2455 if (query.length > 0) {
2456 while (query.length) {
2457 lock = query.shift();
2459 /* See if we are confirming our own lock. */
2460 if (options.key && options.key === lock.lock_id) {
2461 /* Go on and we'll get a new lock. */
2463 /* Make sure if they are pid locks users is still connected. */
2464 } else if (lock.lock_pid) {
2466 XT.debug('tryLock sql = ', pidSql);
2467 XT.debug('tryLock values = ', [lock.lock_username, lock.lock_pid]);
2469 pcheck = plv8.execute(pidSql, [lock.lock_username, lock.lock_pid]);
2470 if (pcheck.length) { break; } /* valid lock */
2472 lockExp = new Date(lock.lock_expires);
2473 if (DEBUG) { XT.debug("Lock found", [lockExp > expires, lockExp, expires]); }
2474 if (lockExp > expires) { break; } /* valid lock */
2477 /* Delete invalid or expired lock. */
2479 XT.debug('tryLock sql = ', deleteSql);
2480 XT.debug('tryLock values = ', [lock.lock_id]);
2482 plv8.execute(deleteSql, [lock.lock_id]);
2487 if (DEBUG) XT.debug("Lock found", lock.lock_username);
2490 username: lock.lock_username,
2491 effective: lock.lock_effective
2496 if (options.obtainLock === false) { return; }
2498 if (DEBUG) { XT.debug("Creating lock."); }
2499 if (DEBUG) { XT.debug('tryLock sql = ', insertSqlPid); }
2502 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, pid]); }
2503 lock = plv8.execute(insertSqlPid, [oid, id, username, pid])[0];
2505 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2506 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, expires]); }
2507 lock = plv8.execute(insertSqlExp, [oid, id, username, expires])[0];
2510 if (DEBUG) { XT.debug("Lock returned is", lock.lock_id); }
2514 effective: lock.lock_effective,
2520 * Release a lock. Pass either options with a key, or table, id and username.
2522 * @param {Object} Options: key or table and id
2524 releaseLock: function (options) {
2526 sqlKey = 'delete from xt.lock where lock_id = $1;',
2527 sqlUsr = 'delete from xt.lock where lock_table_oid = $1 and lock_record_id = $2 and lock_username = $3;',
2528 username = XT.username;
2532 XT.debug('releaseLock sql = ', sqlKey);
2533 XT.debug('releaseLock values = ', [options.key]);
2535 plv8.execute(sqlKey, [options.key]);
2537 oid = typeof options.table === "string" ? this.getTableOid(options.table) : options.table;
2540 XT.debug('releaseLock sql = ', sqlUsr);
2541 XT.debug('releaseLock values = ', [oid, options.id, username]);
2543 plv8.execute(sqlUsr, [oid, options.id, username]);
2550 * Renew a lock. Defaults to rewing the lock for 30 seconds.
2552 * @param {Number} Key
2553 * @params {Object} Options: timeout
2554 * @returns {Date} New expiration or false.
2556 renewLock: function (key, options) {
2557 var expires = new Date(),
2559 selectSql = "select * from xt.lock where lock_id = $1;",
2560 timeout = options && options.timeout ? options.timeout : 30,
2561 updateSql = "update xt.lock set lock_expires = $1 where lock_id = $2;";
2563 if (typeof key !== "number") { return false; }
2564 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2567 XT.debug('renewLock sql = ', selectSql);
2568 XT.debug('renewLock values = ', [key]);
2570 query = plv8.execute(selectSql, [key]);
2574 XT.debug('renewLock sql = ', updateSql);
2575 XT.debug('renewLock values = ', [expires, key]);
2577 plv8.execute(updateSql, [expires, key]);