1 select xt.install_js('XT','Data','xtuple', $$
8 * The XT.Data class includes all functions necessary to process data source requests against the database.
9 * It should be instantiated as an object against which its funtion calls are made. This class enforces privilege
10 * control and as such is not and should not be dispatchable.
20 CREATED_STATE: 'create',
22 UPDATED_STATE: 'update',
23 DELETED_STATE: 'delete',
26 * Build a SQL `where` clause based on privileges for name space and type,
27 * and conditions and parameters passed.
31 * @param {String} Name space
32 * @param {String} Type
33 * @param {Array} Parameters - optional
36 buildClauseOptimized: function (nameSpace, type, parameters, orderBy) {
37 parameters = parameters || [];
40 arrayIdentifiers = [],
47 groupByColumnParams = [],
51 orderByColumnList = [],
55 orderByIdentifiers = [],
56 orderByColumnIdentifiers = [],
58 orderByColumnParams = [],
60 orm = this.fetchOrm(nameSpace, type),
68 privileges = orm.privileges,
76 /* Handle privileges. */
77 if (orm.isNestedOnly) { plv8.elog(ERROR, 'Access Denied'); }
81 (!this.checkPrivilege(privileges.all.read) &&
82 !this.checkPrivilege(privileges.all.update)))
84 privileges.personal &&
85 (this.checkPrivilege(privileges.personal.read) ||
86 this.checkPrivilege(privileges.personal.update))
90 attribute: privileges.personal.properties,
92 isUsernamePrivFilter: true,
97 /* Support the short cut wherein the client asks for a filter on a toOne with a
98 string. Technically they should use "theAttr.theAttrNaturalKey", but if they
99 don't, massage the inputs as if they did */
100 parameters.map(function (parameter) {
101 var attributeIsString = typeof parameter.attribute === 'string';
102 attributes = attributeIsString ? [parameter.attribute] : parameter.attribute;
104 attributes.map(function (attribute) {
105 var prop = XT.Orm.getProperty(orm, attribute),
106 propName = prop.name,
111 if ((prop.toOne || prop.toMany) && attribute.indexOf('.') < 0) {
112 /* Someone is querying on a toOne without using a path */
113 /* TODO: even if there's a path x.y, it's possible that it's still not
114 correct because the correct path maybe is x.y.naturalKeyOfY */
115 if (prop.toOne && prop.toOne.type) {
116 childOrm = that.fetchOrm(nameSpace, prop.toOne.type);
117 } else if (prop.toMany && prop.toMany.type) {
118 childOrm = that.fetchOrm(nameSpace, prop.toMany.type);
120 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + prop.name);
122 naturalKey = XT.Orm.naturalKey(childOrm);
123 if (attributeIsString) {
124 /* add the natural key to the end of the requested attribute */
125 parameter.attribute = attribute + "." + naturalKey;
127 /* swap out the attribute in the array for the one with the prepended natural key */
128 index = parameter.attribute.indexOf(attribute);
129 parameter.attribute.splice(index, 1);
130 parameter.attribute.splice(index, 0, attribute + "." + naturalKey);
136 /* Handle parameters. */
137 if (parameters.length) {
138 for (var i = 0; i < parameters.length; i++) {
140 param = parameters[i];
141 op = param.operator || '=';
161 for (var c = 0; c < param.value.length; c++) {
162 ret.parameters.push(param.value[c]);
163 param.value[c] = '$' + count;
169 for (var c = 0; c < param.value.length; c++) {
170 ret.parameters.push(param.value[c]);
171 param.value[c] = '$' + count;
176 plv8.elog(ERROR, 'Invalid operator: ' + op);
179 /* Handle characteristics. This is very specific to xTuple,
180 and highly dependant on certain table structures and naming conventions,
181 but otherwise way too much work to refactor in an abstract manner right now. */
182 if (param.isCharacteristic) {
185 param.value = ' ARRAY[' + param.value.join(',') + ']';
188 /* Booleans are stored as strings. */
189 if (param.value === true) {
191 } else if (param.value === false) {
195 /* Yeah, it depends on a property called 'characteristics'... */
196 prop = XT.Orm.getProperty(orm, 'characteristics');
198 /* Build the characteristics query clause. */
199 identifiers.push(XT.Orm.primaryKey(orm, true));
200 identifiers.push(prop.toMany.inverse);
201 identifiers.push(orm.nameSpace.toLowerCase());
202 identifiers.push(prop.toMany.type.decamelize());
203 identifiers.push(param.attribute);
204 identifiers.push(param.value);
206 charSql = '%' + (identifiers.length - 5) + '$I in (' +
207 ' select %' + (identifiers.length - 4) + '$I '+
208 ' from %' + (identifiers.length - 3) + '$I.%' + (identifiers.length - 2) + '$I ' +
209 ' join char on (char_name = characteristic)' +
211 /* Note: Not using $i for these. L = literal here. These is not identifiers. */
212 ' and char_name = %' + (identifiers.length - 1) + '$L ' +
213 ' and value ' + op + ' %' + (identifiers.length) + '$L ' +
216 clauses.push(charSql);
218 /* Array comparisons handle another way. e.g. %1$I !<@ ARRAY[$1,$2] */
219 } else if (op === '<@' || op === '!<@') {
220 /* Handle paths if applicable. */
221 if (param.attribute.indexOf('.') > -1) {
222 parts = param.attribute.split('.');
223 childOrm = this.fetchOrm(nameSpace, type);
225 pcount = params.length - 1;
227 for (var n = 0; n < parts.length; n++) {
228 /* Validate attribute. */
229 prop = XT.Orm.getProperty(childOrm, parts[n]);
231 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
235 if (n === parts.length - 1) {
236 identifiers.push("jt" + (joins.length - 1));
237 identifiers.push(prop.attr.column);
238 pgType = this.getPgTypeFromOrmType(
239 this.getNamespaceFromNamespacedTable(childOrm.table),
240 this.getTableFromNamespacedTable(childOrm.table),
243 pgType = pgType ? "::" + pgType + "[]" : '';
244 params[pcount] += "%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I";
245 params[pcount] += ' ' + op + ' ARRAY[' + param.value.join(',') + ']' + pgType;
247 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
248 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
249 joinIdentifiers.push(
250 this.getNamespaceFromNamespacedTable(childOrm.table),
251 this.getTableFromNamespacedTable(childOrm.table),
252 sourceTableAlias, prop.toOne.column,
253 XT.Orm.primaryKey(childOrm, true));
254 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
255 + "$I jt" + joins.length + " on %"
256 + (joinIdentifiers.length - 2) + "$I.%"
257 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
261 prop = XT.Orm.getProperty(orm, param.attribute);
262 pertinentExtension = XT.Orm.getProperty(orm, param.attribute, true);
263 if(pertinentExtension.isChild || pertinentExtension.isExtension) {
264 /* We'll need to join this orm extension */
265 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
266 joinIdentifiers.push(
267 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
268 this.getTableFromNamespacedTable(pertinentExtension.table),
269 fromKeyProp.attr.column,
270 pertinentExtension.relations[0].column);
271 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
272 + "$I jt" + joins.length + " on t1.%"
273 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
276 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute);
279 identifiers.push(pertinentExtension.isChild || pertinentExtension.isExtension ?
280 "jt" + (joins.length - 1) :
282 identifiers.push(prop.attr.column);
283 pgType = this.getPgTypeFromOrmType(
284 this.getNamespaceFromNamespacedTable(orm.table),
285 this.getTableFromNamespacedTable(orm.table),
288 pgType = pgType ? "::" + pgType + "[]" : '';
289 params.push("%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I " + op + ' ARRAY[' + param.value.join(',') + ']' + pgType);
290 pcount = params.length - 1;
292 clauses.push(params[pcount]);
294 /* Everything else handle another. */
296 if (XT.typeOf(param.attribute) !== 'array') {
297 param.attribute = [param.attribute];
300 for (var c = 0; c < param.attribute.length; c++) {
301 /* Handle paths if applicable. */
302 if (param.attribute[c].indexOf('.') > -1) {
303 parts = param.attribute[c].split('.');
304 childOrm = this.fetchOrm(nameSpace, type);
306 pcount = params.length - 1;
309 /* Check if last part is an Array. */
310 for (var m = 0; m < parts.length; m++) {
311 /* Validate attribute. */
312 prop = XT.Orm.getProperty(childOrm, parts[m]);
314 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[m]);
317 if (m < parts.length - 1) {
318 if (prop.toOne && prop.toOne.type) {
319 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
320 } else if (prop.toMany && prop.toMany.type) {
321 childOrm = this.fetchOrm(nameSpace, prop.toMany.type);
323 plv8.elog(ERROR, "toOne or toMany property is missing it's 'type': " + prop.name);
325 } else if (prop.attr && prop.attr.type === 'Array') {
326 /* The last property in the path is an array. */
328 params[pcount] = '$' + count;
332 /* Reset the childOrm to parent. */
333 childOrm = this.fetchOrm(nameSpace, type);
335 for (var n = 0; n < parts.length; n++) {
336 /* Validate attribute. */
337 prop = XT.Orm.getProperty(childOrm, parts[n]);
339 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
342 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
343 if (param.isUsernamePrivFilter && isArray) {
344 identifiers.push(prop.attr.column);
345 arrayIdentifiers.push(identifiers.length);
347 if (n < parts.length - 1) {
348 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
351 pertinentExtension = XT.Orm.getProperty(childOrm, parts[n], true);
352 var isExtension = pertinentExtension.isChild || pertinentExtension.isExtension;
354 /* We'll need to join this orm extension */
355 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
356 joinIdentifiers.push(
357 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
358 this.getTableFromNamespacedTable(pertinentExtension.table),
359 fromKeyProp.attr.column,
360 pertinentExtension.relations[0].column);
361 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
362 + "$I jt" + joins.length + " on t1.%"
363 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
365 /* Build path, e.g. table_name.column_name */
366 if (n === parts.length - 1) {
367 identifiers.push("jt" + (joins.length - 1));
368 identifiers.push(prop.attr.column);
369 params[pcount] += "%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I";
371 params[pcount] = "lower(" + params[pcount] + ")";
374 sourceTableAlias = n === 0 && !isExtension ? "t1" : "jt" + (joins.length - 1);
375 if (prop.toOne && prop.toOne.type) {
376 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
377 joinIdentifiers.push(
378 this.getNamespaceFromNamespacedTable(childOrm.table),
379 this.getTableFromNamespacedTable(childOrm.table),
380 sourceTableAlias, prop.toOne.column,
381 XT.Orm.primaryKey(childOrm, true)
383 } else if (prop.toMany && prop.toMany.type) {
384 childOrm = this.fetchOrm(nameSpace, prop.toMany.type);
385 joinIdentifiers.push(
386 this.getNamespaceFromNamespacedTable(childOrm.table),
387 this.getTableFromNamespacedTable(childOrm.table),
388 sourceTableAlias, prop.toMany.column,
389 XT.Orm.primaryKey(childOrm, true)
392 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
393 + "$I jt" + joins.length + " on %"
394 + (joinIdentifiers.length - 2) + "$I.%"
395 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
400 /* Validate attribute. */
401 prop = XT.Orm.getProperty(orm, param.attribute[c]);
402 pertinentExtension = XT.Orm.getProperty(orm, param.attribute[c], true);
403 if(pertinentExtension.isChild || pertinentExtension.isExtension) {
404 /* We'll need to join this orm extension */
405 fromKeyProp = XT.Orm.getProperty(orm, pertinentExtension.relations[0].inverse);
406 joinIdentifiers.push(
407 this.getNamespaceFromNamespacedTable(pertinentExtension.table),
408 this.getTableFromNamespacedTable(pertinentExtension.table),
409 fromKeyProp.attr.column,
410 pertinentExtension.relations[0].column);
411 joins.push("left join %" + (joinIdentifiers.length - 3) + "$I.%" + (joinIdentifiers.length - 2)
412 + "$I jt" + joins.length + " on t1.%"
413 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
416 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute[c]);
419 identifiers.push(pertinentExtension.isChild || pertinentExtension.isExtension ?
420 "jt" + (joins.length - 1) :
422 identifiers.push(prop.attr.column);
424 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
425 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested) ||
426 (prop.attr && prop.attr.type === 'Array'))) {
428 params.push('$' + count);
429 pcount = params.length - 1;
430 arrayIdentifiers.push(identifiers.length);
432 params.push("%" + (identifiers.length - 1) + "$I.%" + identifiers.length + "$I");
433 pcount = params.length - 1;
437 /* Add persional privs array search. */
438 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested)
439 || (prop.attr && prop.attr.type === 'Array') || isArray)) {
441 /* XXX: this bit of code has not been touched by the optimization refactor */
442 /* e.g. 'admin' = ANY (usernames_array) */
444 params[pcount] += ' ' + op + ' ANY (';
446 /* Build path. e.g. ((%1$I).%2$I).%3$I */
447 for (var f =0; f < arrayIdentifiers.length; f++) {
448 arrayParams += '%' + arrayIdentifiers[f] + '$I';
449 if (f < arrayIdentifiers.length - 1) {
450 arrayParams = "(" + arrayParams + ").";
453 params[pcount] += arrayParams + ')';
455 /* Add optional is null clause. */
456 } else if (parameters[i].includeNull) {
457 /* e.g. %1$I = $1 or %1$I is null */
458 params[pcount] = params[pcount] + " " + op + ' $' + count + ' or ' + params[pcount] + ' is null';
461 params[pcount] += " " + op + ' $' + count;
464 orClause.push(params[pcount]);
467 /* If more than one clause we'll get: (%1$I = $1 or %1$I = $2 or %1$I = $3) */
468 clauses.push('(' + orClause.join(' or ') + ')');
470 ret.parameters.push(param.value);
475 ret.conditions = (clauses.length ? '(' + XT.format(clauses.join(' and '), identifiers) + ')' : ret.conditions) || true;
477 /* Massage orderBy with quoted identifiers. */
478 /* We need to support the xm case for sql2 and the xt/public (column) optimized case for sql1 */
479 /* In practice we build the two lists independently of one another */
481 for (var i = 0; i < orderBy.length; i++) {
482 /* Handle path case. */
483 if (orderBy[i].attribute.indexOf('.') > -1) {
484 parts = orderBy[i].attribute.split('.');
486 orderByParams.push("");
487 orderByColumnParams.push("");
488 groupByColumnParams.push("");
489 pcount = orderByParams.length - 1;
491 for (var n = 0; n < parts.length; n++) {
492 prop = XT.Orm.getProperty(orm, parts[n]);
494 plv8.elog(ERROR, 'Attribute not found in map: ' + parts[n]);
496 orderByIdentifiers.push(parts[n]);
497 orderByParams[pcount] += "%" + orderByIdentifiers.length + "$I";
499 if (n === parts.length - 1) {
500 orderByColumnIdentifiers.push("jt" + (joins.length - 1));
501 orderByColumnIdentifiers.push(prop.attr.column);
502 orderByColumnParams[pcount] += "%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I"
503 groupByColumnParams[pcount] += "%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I"
505 orderByParams[pcount] = "(" + orderByParams[pcount] + ").";
506 orm = this.fetchOrm(nameSpace, prop.toOne.type);
507 sourceTableAlias = n === 0 ? "t1" : "jt" + (joins.length - 1);
508 joinIdentifiers.push(
509 this.getNamespaceFromNamespacedTable(orm.table),
510 this.getTableFromNamespacedTable(orm.table),
511 sourceTableAlias, prop.toOne.column,
512 XT.Orm.primaryKey(orm, true));
513 joins.push("left join %" + (joinIdentifiers.length - 4) + "$I.%" + (joinIdentifiers.length - 3)
514 + "$I jt" + joins.length + " on %"
515 + (joinIdentifiers.length - 2) + "$I.%"
516 + (joinIdentifiers.length - 1) + "$I = jt" + joins.length + ".%" + joinIdentifiers.length + "$I");
522 prop = XT.Orm.getProperty(orm, orderBy[i].attribute);
524 plv8.elog(ERROR, 'Attribute not found in map: ' + orderBy[i].attribute);
526 orderByIdentifiers.push(orderBy[i].attribute);
527 orderByColumnIdentifiers.push("t1");
529 We might need to look at toOne if the client is asking for a toOne without specifying
530 the path. Unfortunately, if they do specify the path, then sql2 will fail. So this does
531 work, although we're really sorting by the primary key of the toOne, whereas the
532 user probably wants us to sort by the natural key TODO
534 orderByColumnIdentifiers.push(prop.attr ? prop.attr.column : prop.toOne.column);
535 orderByParams.push("%" + orderByIdentifiers.length + "$I");
536 orderByColumnParams.push("%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I");
537 groupByColumnParams.push("%" + (orderByColumnIdentifiers.length - 1) + "$I.%" + orderByColumnIdentifiers.length + "$I");
538 pcount = orderByParams.length - 1;
541 if (orderBy[i].isEmpty) {
542 orderByParams[pcount] = "length(" + orderByParams[pcount] + ")=0";
543 orderByColumnParams[pcount] = "length(" + orderByColumnParams[pcount] + ")=0";
545 if (orderBy[i].descending) {
546 orderByParams[pcount] += " desc";
547 orderByColumnParams[pcount] += " desc";
550 orderByList.push(orderByParams[pcount])
551 orderByColumnList.push(orderByColumnParams[pcount])
555 ret.orderBy = orderByList.length ? XT.format('order by ' + orderByList.join(','), orderByIdentifiers) : '';
556 ret.orderByColumns = orderByColumnList.length ? XT.format('order by ' + orderByColumnList.join(','), orderByColumnIdentifiers) : '';
557 ret.groupByColumns = groupByColumnParams.length ? XT.format(', ' + groupByColumnParams.join(','), orderByColumnIdentifiers) : '';
558 ret.joins = joins.length ? XT.format(joins.join(' '), joinIdentifiers) : '';
564 * Queries whether the current user has been granted the privilege passed.
566 * @param {String} privilege
569 checkPrivilege: function (privilege) {
576 if (typeof privilege === 'string') {
577 if (!this._granted) { this._granted = {}; }
578 if (!this._granted[XT.username]) { this._granted[XT.username] = {}; }
579 if (this._granted[XT.username][privilege] !== undefined) { return this._granted[XT.username][privilege]; }
581 /* The privilege name is allowed to be a set of space-delimited privileges */
582 /* If a user has any of the applicable privileges then they get access */
583 privArray = privilege.split(" ");
584 sql = 'select coalesce(usrpriv_priv_id, grppriv_priv_id, -1) > 0 as granted ' +
586 'left join usrpriv on (priv_id=usrpriv_priv_id) and (usrpriv_username=$1) ' +
588 ' select distinct grppriv_priv_id ' +
590 ' join usrgrp on (grppriv_grp_id=usrgrp_grp_id) and (usrgrp_username=$1) ' +
591 ' ) grppriv on (grppriv_priv_id=priv_id) ' +
592 'where priv_name = $2';
594 for (var i = 1; i < privArray.length; i++) {
595 sql = sql + ' or priv_name = $' + (i + 2);
597 sql = sql + "order by granted desc limit 1;";
599 /* Cleverness: the query parameters are just the priv array with the username tacked on front. */
600 privArray.unshift(XT.username);
603 XT.debug('checkPrivilege sql =', sql);
604 XT.debug('checkPrivilege values =', privArray);
606 res = plv8.execute(sql, privArray);
607 ret = res.length ? res[0].granted : false;
610 this._granted[XT.username][privilege] = ret;
614 XT.debug('Privilege check for "' + XT.username + '" on "' + privilege + '" returns ' + ret);
621 * Validate whether user has read access to data. If a record is passed, check personal privileges of
624 * @param {String} name space
625 * @param {String} type name
626 * @param {Object} record - optional
627 * @param {Boolean} is top level, default is true
630 checkPrivileges: function (nameSpace, type, record, isTopLevel) {
631 isTopLevel = isTopLevel !== false ? true : false;
632 var action = record && record.dataState === this.CREATED_STATE ? 'create' :
633 record && record.dataState === this.DELETED_STATE ? 'delete' :
634 record && record.dataState === this.UPDATED_STATE ? 'update' : 'read',
635 committing = record ? record.dataState !== this.READ_STATE : false,
637 isGrantedPersonal = false,
638 map = this.fetchOrm(nameSpace, type),
639 privileges = map.privileges,
643 /* If there is no ORM, this isn't a table data type so no check required. */
646 XT.debug('orm type is ->', map.type);
647 XT.debug('orm is ->', map);
650 if (!map) { return true; }
652 /* Can not access 'nested only' records directly. */
654 XT.debug('is top level ->', isTopLevel);
655 XT.debug('is nested ->', map.isNestedOnly);
657 if (isTopLevel && map.isNestedOnly) { return false; }
659 /* Check privileges - first do we have access to anything? */
661 if (DEBUG) { XT.debug('privileges found', privileges); }
663 if (DEBUG) { XT.debug('is committing'); }
665 /* Check if user has 'all' read privileges. */
666 isGrantedAll = privileges.all ? this.checkPrivilege(privileges.all[action]) : false;
668 /* Otherwise check for 'personal' read privileges. */
670 isGrantedPersonal = privileges.personal ?
671 this.checkPrivilege(privileges.personal[action]) : false;
674 if (DEBUG) { XT.debug('is NOT committing'); }
676 /* Check if user has 'all' read privileges. */
677 isGrantedAll = privileges.all ?
678 this.checkPrivilege(privileges.all.read) ||
679 this.checkPrivilege(privileges.all.update) : false;
681 /* Otherwise check for 'personal' read privileges. */
683 isGrantedPersonal = privileges.personal ?
684 this.checkPrivilege(privileges.personal.read) ||
685 this.checkPrivilege(privileges.personal.update) : false;
690 /* If we're checknig an actual record and only have personal privileges, */
691 /* see if the record allows access. */
692 if (record && !isGrantedAll && isGrantedPersonal && action !== "create") {
693 if (DEBUG) { XT.debug('checking record level personal privileges'); }
696 /* Shared checker function that checks 'personal' properties for access rights. */
697 checkPersonal = function (record) {
700 props = privileges.personal.properties,
701 get = function (obj, target) {
704 parts = target.split("."),
707 for (var idx = 0; idx < parts.length; idx++) {
709 ret = ret ? ret[part] : obj[part];
710 if (ret === null || ret === undefined) {
718 while (!isGranted && i < props.length) {
720 personalUser = get(record, prop);
722 if (personalUser instanceof Array) {
723 for (var userIdx = 0; userIdx < personalUser.length; userIdx++) {
724 if (personalUser[userIdx].toLowerCase() === XT.username) {
728 } else if (personalUser) {
729 isGranted = personalUser.toLowerCase() === XT.username;
738 /* If committing we need to ensure the record in its previous state is editable by this user. */
739 if (committing && (action === 'update' || action === 'delete')) {
740 pkey = XT.Orm.naturalKey(map) || XT.Orm.primaryKey(map);
741 old = this.retrieveRecord({
742 nameSpace: nameSpace,
748 isGrantedPersonal = checkPersonal(old.data);
750 /* Otherwise check personal privileges on the record passed. */
751 } else if (action === 'read') {
752 isGrantedPersonal = checkPersonal(record);
757 XT.debug('is granted all ->', isGrantedAll);
758 XT.debug('is granted personal ->', isGrantedPersonal);
761 return isGrantedAll || isGrantedPersonal;
765 * Commit array columns with their own statements
767 * @param {Object} Orm
768 * @param {Object} Record
770 commitArrays: function (orm, record, encryptionKey) {
771 var pkey = XT.Orm.primaryKey(orm),
780 resolveKey = function (col) {
783 /* First search properties */
784 var ary = orm.properties.filter(function (prop) {
785 return prop.attr && prop.attr.column === col;
792 /* If not found must be extension, search relations */
793 if (orm.extensions.length) {
794 orm.extensions.forEach(function (ext) {
796 ary = ext.relations.filter(function (prop) {
797 return prop.column === col;
801 attr = ary[0].inverse;
807 if (attr) { return attr };
809 /* If still not found, we have a structural problem */
810 throw new Error("Can not resolve primary id on toMany relation");
813 for (prop in record) {
814 ormp = XT.Orm.getProperty(orm, prop);
816 /* If the property is an array of objects they must be records so commit them. */
817 if (ormp.toMany && ormp.toMany.isNested) {
818 fkey = ormp.toMany.inverse;
819 values = record[prop];
821 for (var i = 0; i < values.length; i++) {
824 /* Populate the parent key into the foreign key field if it's absent. */
826 columnToKey = ormp.toMany.column;
827 propToKey = columnToKey ? resolveKey(columnToKey) : pkey;
828 if (!record[propToKey]) {
829 /* If there's no data, we have a structural problem */
830 throw new Error("Can not resolve foreign key on toMany relation " + ormp.name);
832 val[fkey] = record[propToKey];
836 nameSpace: orm.nameSpace,
837 type: ormp.toMany.type,
839 encryptionKey: encryptionKey
847 * Commit metrics that have changed to the database.
849 * @param {Object} metrics
852 commitMetrics: function (metrics) {
854 sql = 'select setMetric($1,$2)',
857 for (key in metrics) {
858 value = metrics[key];
859 if (typeof value === 'boolean') {
860 value = value ? 't' : 'f';
861 } else if (typeof value === 'number') {
862 value = value.toString();
866 XT.debug('commitMetrics sql =', sql);
867 XT.debug('commitMetrics values =', [key, value]);
869 plv8.execute(sql, [key, value]);
876 * Commit a record to the database. The record must conform to the object hiearchy as defined by the
877 * record's `ORM` definition. Each object in the tree must include state information on a reserved property
878 * called `dataState`. Valid values are `create`, `update` and `delete`. Objects with other dataState values including
879 * `undefined` will be ignored. State values can be added using `XT.jsonpatch.updateState(obj, state)`.
881 * @seealso XT.jsonpatch.updateState
882 * @param {Object} Options
883 * @param {String} [options.nameSpace] Namespace. Required.
884 * @param {String} [options.type] Type. Required.
885 * @param {Object} [options.data] The data payload to be processed. Required
886 * @param {Number} [options.etag] Record version for optimistic locking.
887 * @param {Object} [options.lock] Lock information for pessemistic locking.
888 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
889 * @param {String} [options.encryptionKey] Encryption key.
891 commitRecord: function (options) {
892 var data = options.data,
893 dataState = data ? data.dataState : false,
894 hasAccess = options.superUser ||
895 this.checkPrivileges(options.nameSpace, options.type, data, false);
897 if (!hasAccess) { throw new Error("Access Denied."); }
900 case (this.CREATED_STATE):
901 this.createRecord(options);
903 case (this.UPDATED_STATE):
904 this.updateRecord(options);
906 case (this.DELETED_STATE):
907 this.deleteRecord(options);
912 * Commit insert to the database
914 * @param {Object} Options
915 * @param {String} [options.nameSpace] Namespace. Required.
916 * @param {String} [options.type] Type. Required.
917 * @param {Object} [options.data] The data payload to be processed. Required.
918 * @param {String} [options.encryptionKey] Encryption key.
920 createRecord: function (options) {
921 var data = options.data,
922 encryptionKey = options.encryptionKey,
924 orm = this.fetchOrm(options.nameSpace, options.type),
925 sql = this.prepareInsert(orm, data, null, encryptionKey),
926 pkey = XT.Orm.primaryKey(orm),
929 /* Handle extensions on the same table. */
930 for (var i = 0; i < orm.extensions.length; i++) {
931 if (orm.extensions[i].table === orm.table) {
932 sql = this.prepareInsert(orm.extensions[i], data, sql, encryptionKey);
936 /* Commit the base record. */
938 XT.debug('createRecord sql =', sql.statement);
939 XT.debug('createRecord values =', sql.values);
943 rec = plv8.execute(sql.statement, sql.values);
944 /* Make sure the primary key is populated */
946 data[pkey] = rec[0].id;
948 /* Make sure the obj_uuid is populated, if applicable */
949 if (!data.obj_uuid && rec[0] && rec[0].obj_uuid) {
950 data.uuid = rec[0].obj_uuid;
954 /* Handle extensions on other tables. */
955 for (var i = 0; i < orm.extensions.length; i++) {
956 if (orm.extensions[i].table !== orm.table &&
957 !orm.extensions[i].isChild) {
958 sql = this.prepareInsert(orm.extensions[i], data, null, encryptionKey);
961 XT.debug('createRecord sql =', sql.statement);
962 XT.debug('createRecord values =', sql.values);
966 plv8.execute(sql.statement, sql.values);
971 /* Okay, now lets handle arrays. */
972 this.commitArrays(orm, data, encryptionKey);
976 * Use an orm object and a record and build an insert statement. It
977 * returns an object with a table name string, columns array, expressions
978 * array and insert statement string that can be executed.
980 * The optional params object includes objects columns, expressions
981 * that can be cumulatively added to the result.
983 * @params {Object} Orm
984 * @params {Object} Record
985 * @params {Object} Params - optional
986 * @params {String} Encryption Key
989 prepareInsert: function (orm, record, params, encryptionKey) {
1002 pkey = XT.Orm.primaryKey(orm),
1005 sql = "select nextval($1) as id",
1011 isValidSql = params && params.statement ? true : false,
1014 params = params || {
1021 params.table = orm.table;
1022 count = params.values.length + 1;
1024 /* If no primary key, then create one. */
1025 if (!record[pkey] && orm.idSequenceName) {
1027 XT.debug('prepareInsert sql =', sql);
1028 XT.debug('prepareInsert values =', [orm.idSequenceName]);
1030 record[pkey] = plv8.execute(sql, [orm.idSequenceName])[0].id;
1033 /* If extension handle key. */
1034 if (orm.relations) {
1035 for (var i = 0; i < orm.relations.length; i++) {
1036 column = orm.relations[i].column;
1037 if (!params.identifiers.contains(column)) {
1038 params.columns.push("%" + count + "$I");
1039 params.values.push(record[orm.relations[i].inverse]);
1040 params.expressions.push('$' + count);
1041 params.identifiers.push(orm.relations[i].column);
1047 /* Build up the content for insert of this record. */
1048 for (var i = 0; i < orm.properties.length; i++) {
1049 ormp = orm.properties[i];
1052 if (ormp.toMany && ormp.toMany.column === 'obj_uuid') {
1053 params.parentUuid = true;
1056 attr = ormp.attr ? ormp.attr : ormp.toOne ? ormp.toOne : ormp.toMany;
1058 iorm = ormp.toOne ? this.fetchOrm(orm.nameSpace, ormp.toOne.type) : false,
1059 nkey = iorm ? XT.Orm.naturalKey(iorm, true) : false;
1060 val = ormp.toOne && record[prop] instanceof Object ?
1061 record[prop][nkey || ormp.toOne.inverse || 'id'] : record[prop];
1064 * Ignore derived fields for insert/update
1066 if (attr.derived) continue;
1068 attributePrivileges = orm.privileges &&
1069 orm.privileges.attribute &&
1070 orm.privileges.attribute[prop];
1072 if(!attributePrivileges || attributePrivileges.create === undefined) {
1074 } else if (typeof attributePrivileges.create === 'string') {
1075 canEdit = this.checkPrivilege(attributePrivileges.create);
1077 canEdit = attributePrivileges.create; /* if it's true or false */
1080 /* Handle fixed values. */
1081 if (attr.value !== undefined) {
1082 params.columns.push("%" + count + "$I");
1083 params.expressions.push('$' + count);
1084 params.values.push(attr.value);
1085 params.identifiers.push(attr.column);
1089 /* Handle passed values. */
1090 } else if (canEdit && val !== undefined && val !== null && !ormp.toMany) {
1091 if (attr.isEncrypted) {
1092 if (encryptionKey) {
1093 encryptQuery = "select encrypt(setbytea(%1$L), setbytea(%2$L), %3$L)";
1094 encryptSql = XT.format(encryptQuery, [record[prop], encryptionKey, 'bf']);
1095 val = record[prop] ? plv8.execute(encryptSql)[0].encrypt : null;
1096 params.columns.push("%" + count + "$I");
1097 params.values.push(val);
1098 params.identifiers.push(attr.column);
1099 params.expressions.push("$" + count);
1103 throw new Error("No encryption key provided.");
1106 if (ormp.toOne && nkey) {
1107 if (iorm.table.indexOf(".") > 0) {
1108 toOneQuery = "select %1$I from %2$I.%3$I where %4$I = $" + count;
1109 toOneSql = XT.format(toOneQuery, [
1110 XT.Orm.primaryKey(iorm, true),
1111 iorm.table.beforeDot(),
1112 iorm.table.afterDot(),
1116 toOneQuery = "select %1$I from %2$I where %3$I = $" + count;
1117 toOneSql = XT.format(toOneQuery, [
1118 XT.Orm.primaryKey(iorm, true),
1123 exp = "(" + toOneSql + ")";
1124 params.expressions.push(exp);
1126 params.expressions.push('$' + count);
1129 params.columns.push("%" + count + "$I");
1130 params.values.push(val);
1131 params.identifiers.push(attr.column);
1135 /* Handle null value if applicable. */
1136 } else if (canEdit && val === undefined || val === null) {
1137 if (attr.nullValue) {
1138 params.columns.push("%" + count + "$I");
1139 params.values.push(attr.nullValue);
1140 params.identifiers.push(attr.column);
1141 params.expressions.push('$' + count);
1144 } else if (attr.required) {
1145 plv8.elog(ERROR, "Attribute " + ormp.name + " is required.");
1154 /* Build the insert statement */
1155 columns = params.columns.join(', ');
1156 columns = XT.format(columns, params.identifiers);
1157 expressions = params.expressions.join(', ');
1158 expressions = XT.format(expressions, params.identifiers);
1160 if (params.table.indexOf(".") > 0) {
1161 namespace = params.table.beforeDot();
1162 table = params.table.afterDot();
1163 query = 'insert into %1$I.%2$I (' + columns + ') values (' + expressions + ')';
1164 params.statement = XT.format(query, [namespace, table]);
1166 query = 'insert into %1$I (' + columns + ') values (' + expressions + ')';
1167 params.statement = XT.format(query, [params.table]);
1170 /* If we can get the primary key column we want to return that
1171 for cases where it is determined behind the scenes */
1172 if (!record[pkey] && !params.primaryKey) {
1173 params.primaryKey = XT.Orm.primaryKey(orm, true);
1176 if (params.primaryKey && params.parentUuid) {
1177 params.statement = params.statement + ' returning ' + params.primaryKey + ' as id, obj_uuid';
1178 } else if (params.parentUuid) {
1179 params.statement = params.statement + ' returning obj_uuid';
1180 } else if (params.primaryKey) {
1181 params.statement = params.statement + ' returning ' + params.primaryKey + ' as id';
1185 XT.debug('prepareInsert statement =', params.statement);
1186 XT.debug('prepareInsert values =', params.values);
1193 * Commit update to the database
1195 * @param {Object} Options
1196 * @param {String} [options.nameSpace] Namespace. Required.
1197 * @param {String} [options.type] Type. Required.
1198 * @param {Object} [options.data] The data payload to be processed. Required.
1199 * @param {Number} [options.etag] Record version for optimistic locking.
1200 * @param {Object} [options.lock] Lock information for pessemistic locking.
1201 * @param {String} [options.encryptionKey] Encryption key.
1203 updateRecord: function (options) {
1204 var data = options.data,
1205 encryptionKey = options.encryptionKey,
1206 orm = this.fetchOrm(options.nameSpace, options.type),
1207 pkey = XT.Orm.primaryKey(orm),
1210 etag = this.getVersion(orm, id),
1215 lockKey = options.lock && options.lock.key ? options.lock.key : false,
1216 lockTable = orm.lockTable || orm.table,
1218 sql = this.prepareUpdate(orm, data, null, encryptionKey);
1220 /* Test for optimistic lock. */
1221 if (!XT.disableLocks && etag && options.etag !== etag) {
1222 // TODO - Improve error handling.
1223 plv8.elog(ERROR, "The version being updated is not current.");
1225 /* Test for pessimistic lock. */
1227 lock = this.tryLock(lockTable, id, {key: lockKey});
1229 // TODO - Improve error handling.
1230 plv8.elog(ERROR, "Can not obtain a lock on the record.");
1234 /* Okay, now lets handle arrays. */
1235 this.commitArrays(orm, data, encryptionKey);
1237 /* Handle extensions on the same table. */
1238 for (var i = 0; i < orm.extensions.length; i++) {
1239 if (orm.extensions[i].table === orm.table) {
1240 sql = this.prepareUpdate(orm.extensions[i], data, sql, encryptionKey);
1244 sql.values.push(id);
1246 /* Commit the base record. */
1248 XT.debug('updateRecord sql =', sql.statement);
1249 XT.debug('updateRecord values =', sql.values);
1251 plv8.execute(sql.statement, sql.values);
1253 /* Handle extensions on other tables. */
1254 for (var i = 0; i < orm.extensions.length; i++) {
1255 ext = orm.extensions[i];
1256 if (ext.table !== orm.table &&
1259 /* Determine whether to insert or update. */
1260 if (ext.table.indexOf(".") > 0) {
1261 iORuQuery = "select %1$I from %2$I.%3$I where %1$I = $1;";
1262 iORuSql = XT.format(iORuQuery, [
1263 ext.relations[0].column,
1264 ext.table.beforeDot(),
1265 ext.table.afterDot()
1268 iORuQuery = "select %1$I from %2$I where %1$I = $1;";
1269 iORuSql = XT.format(iORuQuery, [ext.relations[0].column, ext.table]);
1273 XT.debug('updateRecord sql =', iORuSql);
1274 XT.debug('updateRecord values =', [data[pkey]]);
1276 rows = plv8.execute(iORuSql, [data[pkey]]);
1279 sql = this.prepareUpdate(ext, data, null, encryptionKey);
1280 sql.values.push(id);
1282 sql = this.prepareInsert(ext, data, null, encryptionKey);
1286 XT.debug('updateRecord sql =', sql.statement);
1287 XT.debug('updateRecord values =', sql.values);
1290 if (sql.statement) {
1291 plv8.execute(sql.statement, sql.values);
1296 /* Release any lock. */
1298 this.releaseLock({table: lockTable, id: id});
1303 * Use an orm object and a record and build an update statement. It
1304 * returns an object with a table name string, expressions array and
1305 * insert statement string that can be executed.
1307 * The optional params object includes objects columns, expressions
1308 * that can be cumulatively added to the result.
1310 * @params {Object} Orm
1311 * @params {Object} Record
1312 * @params {Object} Params - optional
1315 prepareUpdate: function (orm, record, params, encryptionKey) {
1317 attributePrivileges,
1340 params = params || {
1346 params.table = orm.table;
1347 count = params.values.length + 1;
1349 if (orm.relations) {
1351 pkey = orm.relations[0].inverse;
1352 columnKey = orm.relations[0].column;
1355 pkey = XT.Orm.primaryKey(orm);
1356 columnKey = XT.Orm.primaryKey(orm, true);
1359 /* Build up the content for update of this record. */
1360 for (var i = 0; i < orm.properties.length; i++) {
1361 ormp = orm.properties[i];
1363 attr = ormp.attr ? ormp.attr : ormp.toOne ? ormp.toOne : ormp.toMany;
1365 iorm = ormp.toOne ? this.fetchOrm(orm.nameSpace, ormp.toOne.type) : false;
1366 nkey = iorm ? XT.Orm.naturalKey(iorm, true) : false;
1367 val = ormp.toOne && record[prop] instanceof Object ?
1368 record[prop][nkey || ormp.toOne.inverse || 'id'] : record[prop],
1370 attributePrivileges = orm.privileges &&
1371 orm.privileges.attribute &&
1372 orm.privileges.attribute[prop];
1375 * Ignore derived fields for insert/update
1377 if (attr.derived) continue;
1379 if(!attributePrivileges || attributePrivileges.update === undefined) {
1381 } else if (typeof attributePrivileges.update === 'string') {
1382 canEdit = this.checkPrivilege(attributePrivileges.update);
1384 canEdit = attributePrivileges.update; /* if it's true or false */
1387 if (canEdit && val !== undefined && !ormp.toMany) {
1389 /* Handle encryption if applicable. */
1390 if (attr.isEncrypted) {
1391 if (encryptionKey) {
1392 encryptQuery = "select encrypt(setbytea(%1$L), setbytea(%2$L), %3$L)";
1393 encryptSql = XT.format(encryptQuery, [val, encryptionKey, 'bf']);
1394 val = record[prop] ? plv8.execute(encryptSql)[0].encrypt : null;
1395 params.values.push(val);
1396 params.identifiers.push(attr.column);
1397 params.expressions.push("%" + count + "$I = $" + count);
1401 // TODO - Improve error handling.
1402 throw new Error("No encryption key provided.");
1404 } else if (ormp.name !== pkey) {
1406 if (attr.required) {
1407 plv8.elog(ERROR, "Attribute " + ormp.name + " is required.");
1409 params.values.push(attr.nullValue || null);
1410 params.expressions.push("%" + count + "$I = $" + count);
1412 } else if (ormp.toOne && nkey) {
1413 if (iorm.table.indexOf(".") > 0) {
1414 toOneQuery = "select %1$I from %2$I.%3$I where %4$I = $" + count;
1415 toOneSql = XT.format(toOneQuery, [
1416 XT.Orm.primaryKey(iorm, true),
1417 iorm.table.beforeDot(),
1418 iorm.table.afterDot(),
1422 toOneQuery = "select %1$I from %2$I where %3$I = $" + count;
1423 toOneSql = XT.format(toOneQuery, [
1424 XT.Orm.primaryKey(iorm, true),
1430 exp = "%" + count + "$I = (" + toOneSql + ")";
1431 params.values.push(val);
1432 params.expressions.push(exp);
1434 params.values.push(val);
1435 params.expressions.push("%" + count + "$I = $" + count);
1437 params.identifiers.push(attr.column);
1444 /* Build the update statement */
1445 expressions = params.expressions.join(', ');
1446 expressions = XT.format(expressions, params.identifiers);
1448 // do not send an invalid sql statement
1449 if (!isValidSql) { return params; }
1451 if (params.table.indexOf(".") > 0) {
1452 namespace = params.table.beforeDot();
1453 table = params.table.afterDot();
1454 query = 'update %1$I.%2$I set ' + expressions + ' where %3$I = $' + count + ';';
1455 params.statement = XT.format(query, [namespace, table, columnKey]);
1457 query = 'update %1$I set ' + expressions + ' where %2$I = $' + count + ';';
1458 params.statement = XT.format(query, [params.table, columnKey]);
1462 XT.debug('prepareUpdate statement =', params.statement);
1463 XT.debug('prepareUpdate values =', params.values);
1470 * Commit deletion to the database
1472 * @param {Object} Options
1473 * @param {String} [options.nameSpace] Namespace. Required.
1474 * @param {String} [options.type] Type. Required.
1475 * @param {Object} [options.data] The data payload to be processed. Required.
1476 * @param {Number} [options.etag] Optional record id version for optimistic locking.
1477 * If set and version does not match, delete will fail.
1478 * @param {Number} [options.lock] Lock information for pessemistic locking.
1480 deleteRecord: function (options) {
1481 var data = options.data,
1482 orm = this.fetchOrm(options.nameSpace, options.type, {silentError: true}),
1490 lockKey = options.lock && options.lock.key ? options.lock.key : false,
1500 /* Set variables or return false with message. */
1502 throw new handleError("Not Found", 404);
1505 pkey = XT.Orm.primaryKey(orm);
1506 nkey = XT.Orm.naturalKey(orm);
1507 lockTable = orm.lockTable || orm.table;
1508 if (!pkey && !nkey) {
1509 throw new handleError("Not Found", 404);
1512 id = nkey ? this.getId(orm, data[nkey]) : data[pkey];
1514 throw new handleError("Not Found", 404);
1517 /* Test for optional optimistic lock. */
1518 etag = this.getVersion(orm, id);
1519 if (etag && options.etag && etag !== options.etag) {
1520 throw new handleError("Precondition Required", 428);
1523 /* Test for pessemistic lock. */
1525 lock = this.tryLock(lockTable, id, {key: lockKey});
1527 throw new handleError("Conflict", 409);
1531 /* Delete children first. */
1532 for (prop in data) {
1533 ormp = XT.Orm.getProperty(orm, prop);
1535 /* If the property is an array of objects they must be records so delete them. */
1536 if (ormp.toMany && ormp.toMany.isNested) {
1537 values = data[prop];
1538 for (var i = 0; i < values.length; i++) {
1540 nameSpace: options.nameSpace,
1541 type: ormp.toMany.type,
1548 /* Next delete from extension tables. */
1549 for (var i = 0; i < orm.extensions.length; i++) {
1550 ext = orm.extensions[i];
1551 if (ext.table !== orm.table &&
1553 columnKey = ext.relations[0].column;
1554 nameKey = ext.relations[0].inverse;
1556 if (ext.table.indexOf(".") > 0) {
1557 namespace = ext.table.beforeDot();
1558 table = ext.table.afterDot();
1559 query = 'delete from %1$I.%2$I where %3$I = $1';
1560 sql = XT.format(query, [namespace, table, columnKey]);
1562 query = 'delete from %1$I where %2$I = $1';
1563 sql = XT.format(query, [ext.table, columnKey]);
1567 XT.debug('deleteRecord sql =', sql);
1568 XT.debug('deleteRecord values =', [id]);
1570 plv8.execute(sql, [id]);
1574 /* Now delete the top. */
1575 nameKey = XT.Orm.primaryKey(orm);
1576 columnKey = XT.Orm.primaryKey(orm, true);
1578 if (orm.table.indexOf(".") > 0) {
1579 namespace = orm.table.beforeDot();
1580 table = orm.table.afterDot();
1581 query = 'delete from %1$I.%2$I where %3$I = $1';
1582 sql = XT.format(query, [namespace, table, columnKey]);
1584 query = 'delete from %1$I where %2$I = $1';
1585 sql = XT.format(query, [orm.table, columnKey]);
1588 /* Commit the record.*/
1590 XT.debug('deleteRecord sql =', sql);
1591 XT.debug('deleteRecord values =', [id]);
1593 plv8.execute(sql, [id]);
1595 /* Release any lock. */
1597 this.releaseLock({table: lockTable, id: id});
1602 * Decrypts properties where applicable.
1604 * @param {String} name space
1605 * @param {String} type
1606 * @param {Object} record
1607 * @param {Object} encryption key
1610 decrypt: function (nameSpace, type, record, encryptionKey) {
1613 hexToAlpha = function (hex) {
1615 for (i = 2; i < hex.length; i += 2) {
1616 str += String.fromCharCode(parseInt(hex.substr(i, 2), 16));
1620 orm = this.fetchOrm(nameSpace, type);
1622 for (prop in record) {
1623 var ormp = XT.Orm.getProperty(orm, prop.camelize());
1625 /* Decrypt property if applicable. */
1626 if (ormp && ormp.attr && ormp.attr.isEncrypted) {
1627 if (encryptionKey) {
1628 sql = "select formatbytea(decrypt($1, setbytea($2), 'bf')) as result";
1629 // TODO - Handle not found error.
1631 if (DEBUG && false) {
1632 XT.debug('decrypt prop =', prop);
1633 XT.debug('decrypt sql =', sql);
1634 XT.debug('decrypt values =', [record[prop], encryptionKey]);
1636 result = plv8.execute(sql, [record[prop], encryptionKey])[0].result;
1637 /* we SOMETIMES need to translate from hex here */
1638 if(typeof result === 'string' && result.substring(0, 2) === '\\x') {
1639 result = result ? hexToAlpha(result) : result;
1641 /* in the special case of encrypted credit card numbers, we don't give the
1642 user the full decrypted number EVEN IF they have the encryption key */
1643 if(ormp.attr.isEncrypted === "credit_card_number" && result && result.length >= 4) {
1644 record[prop] = "************" + result.substring(result.length - 4);
1646 record[prop] = result;
1649 record[prop] = '**********';
1652 /* Check recursively. */
1653 } else if (ormp.toOne && ormp.toOne.isNested) {
1654 that.decrypt(nameSpace, ormp.toOne.type, record[prop], encryptionKey);
1656 } else if (ormp.toMany && ormp.toMany.isNested) {
1657 record[prop].map(function (subdata) {
1658 that.decrypt(nameSpace, ormp.toMany.type, subdata, encryptionKey);
1667 Fetches the ORM. Caches the result in this data object, where it can be used
1668 for this request but will be conveniently forgotten between requests.
1670 fetchOrm: function (nameSpace, type) {
1673 recordType = nameSpace + '.'+ type;
1679 res = this._maps.findProperty('recordType', recordType);
1683 ret = XT.Orm.fetch(nameSpace, type);
1685 /* cache the result so we don't requery needlessly */
1686 this._maps.push({ "recordType": recordType, "map": ret});
1692 * Get the oid for a given table name.
1694 * @param {String} table name
1697 getTableOid: function (table) {
1698 var tableName = this.getTableFromNamespacedTable(table).toLowerCase(), /* be generous */
1699 namespace = this.getNamespaceFromNamespacedTable(table),
1701 sql = "select pg_class.oid::integer as oid " +
1702 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1703 "where relname = $1 and nspname = $2";
1706 XT.debug('getTableOid sql =', sql);
1707 XT.debug('getTableOid values =', [tableName, namespace]);
1709 ret = plv8.execute(sql, [tableName, namespace])[0].oid - 0;
1711 // TODO - Handle not found error.
1717 * Get the primary key id for an object based on a passed in natural key.
1719 * @param {Object} Orm
1720 * @param {String} Natural key value
1722 getId: function (orm, value) {
1723 var ncol = XT.Orm.naturalKey(orm, true),
1724 pcol = XT.Orm.primaryKey(orm, true),
1729 if (orm.table.indexOf(".") > 0) {
1730 namespace = orm.table.beforeDot();
1731 table = orm.table.afterDot();
1732 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1733 sql = XT.format(query, [pcol, namespace, table, ncol]);
1735 query = "select %1$I as id from %2$I where %3$I = $1";
1736 sql = XT.format(query, [pcol, orm.table, ncol]);
1740 XT.debug('getId sql =', sql);
1741 XT.debug('getId values =', [value]);
1744 ret = plv8.execute(sql, [value]);
1749 throw new handleError("Primary Key not found on " + orm.table +
1750 " where " + ncol + " = " + value, 400);
1754 getNamespaceFromNamespacedTable: function (fullName) {
1755 return fullName.indexOf(".") > 0 ? fullName.beforeDot() : "public";
1758 getTableFromNamespacedTable: function (fullName) {
1759 return fullName.indexOf(".") > 0 ? fullName.afterDot() : fullName;
1762 getPgTypeFromOrmType: function (schema, table, column) {
1763 var sql = "select data_type from information_schema.columns " +
1765 "and table_schema = $1 " +
1766 "and table_name = $2 " +
1767 "and column_name = $3;",
1769 values = [schema, table, column];
1772 XT.debug('getPgTypeFromOrmType sql =', sql);
1773 XT.debug('getPgTypeFromOrmType values =', values);
1776 pgType = plv8.execute(sql, values);
1777 pgType = pgType && pgType[0] ? pgType[0].data_type : false;
1783 * Get the natural key id for an object based on a passed in primary key.
1785 * @param {Object} Orm
1786 * @param {Number|String} Primary key value
1787 * @param {Boolean} safe Return the original value instead of erroring if no match is found
1789 getNaturalId: function (orm, value, safe) {
1790 var ncol = XT.Orm.naturalKey(orm, true),
1791 pcol = XT.Orm.primaryKey(orm, true),
1796 if (orm.table.indexOf(".") > 0) {
1797 namespace = orm.table.beforeDot();
1798 table = orm.table.afterDot();
1799 query = "select %1$I as id from %2$I.%3$I where %4$I = $1";
1800 sql = XT.format(query, [ncol, namespace, table, pcol]);
1802 query = "select %1$I as id from %2$I where %3$I = $1";
1803 sql = XT.format(query, [ncol, orm.table, pcol]);
1807 XT.debug('getNaturalId sql =', sql);
1808 XT.debug('getNaturalId values =', [value]);
1811 ret = plv8.execute(sql, [value]);
1818 throw new handleError("Natural Key Not Found: " + orm.nameSpace + "." + orm.type, 400);
1823 * Returns the current version of a record.
1825 * @param {Object} Orm
1826 * @param {Number|String} Record id
1828 getVersion: function (orm, id) {
1829 if (!orm.lockable) { return; }
1832 oid = this.getTableOid(orm.lockTable || orm.table),
1834 sql = 'select ver_etag from xt.ver where ver_table_oid = $1 and ver_record_id = $2;';
1837 XT.debug('getVersion sql = ', sql);
1838 XT.debug('getVersion values = ', [oid, id]);
1840 res = plv8.execute(sql, [oid, id]);
1841 etag = res.length ? res[0].ver_etag : false;
1844 etag = XT.generateUUID();
1845 sql = 'insert into xt.ver (ver_table_oid, ver_record_id, ver_etag) values ($1, $2, $3::uuid);';
1846 // TODO - Handle insert error.
1849 XT.debug('getVersion insert sql = ', sql);
1850 XT.debug('getVersion insert values = ', [oid, id, etag]);
1852 plv8.execute(sql, [oid, id, etag]);
1859 * Fetch an array of records from the database.
1861 * @param {Object} Options
1862 * @param {String} [dataHash.nameSpace] Namespace. Required.
1863 * @param {String} [dataHash.type] Type. Required.
1864 * @param {Array} [dataHash.parameters] Parameters
1865 * @param {Array} [dataHash.orderBy] Order by - optional
1866 * @param {Number} [dataHash.rowLimit] Row limit - optional
1867 * @param {Number} [dataHash.rowOffset] Row offset - optional
1870 fetch: function (options) {
1871 var nameSpace = options.nameSpace,
1872 type = options.type,
1873 query = options.query || {},
1874 encryptionKey = options.encryptionKey,
1875 orderBy = query.orderBy,
1876 orm = this.fetchOrm(nameSpace, type),
1879 parameters = query.parameters,
1880 clause = this.buildClauseOptimized(nameSpace, type, parameters, orderBy),
1882 pkey = XT.Orm.primaryKey(orm),
1883 pkeyColumn = XT.Orm.primaryKey(orm, true),
1884 nkey = XT.Orm.naturalKey(orm),
1885 limit = query.rowLimit ? XT.format('limit %1$L', [query.rowLimit]) : '',
1886 offset = query.rowOffset ? XT.format('offset %1$L', [query.rowOffset]) : '',
1889 nameSpace: nameSpace,
1899 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};',
1900 sql2 = 'select * from %1$I.%2$I where %3$I in ({ids}) {orderBy}';
1902 /* Validate - don't bother running the query if the user has no privileges. */
1903 if (!this.checkPrivileges(nameSpace, type)) { return []; }
1905 tableNamespace = this.getNamespaceFromNamespacedTable(orm.table);
1906 table = this.getTableFromNamespacedTable(orm.table);
1909 /* Just get the count of rows that match the conditions */
1910 sqlCount = 'select count(distinct t1.%3$I) as count from %1$I.%2$I t1 {joins} where {conditions};';
1911 sqlCount = XT.format(sqlCount, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1912 sqlCount = sqlCount.replace('{joins}', clause.joins)
1913 .replace('{conditions}', clause.conditions);
1916 XT.debug('fetch sqlCount = ', sqlCount);
1917 XT.debug('fetch values = ', clause.parameters);
1920 ret.data = plv8.execute(sqlCount, clause.parameters);
1924 /* Because we query views of views, you can get inconsistent results */
1925 /* when doing limit and offest queries without an order by. Add a default. */
1926 if (limit && offset && (!orderBy || !orderBy.length) && !clause.orderByColumns) {
1927 /* We only want this on sql1, not sql2's clause.orderBy. */
1928 clause.orderByColumns = XT.format('order by t1.%1$I', [pkeyColumn]);
1931 /* Query the model. */
1932 sql1 = XT.format(sql1, [tableNamespace.decamelize(), table.decamelize(), pkeyColumn]);
1933 sql1 = sql1.replace('{joins}', clause.joins)
1934 .replace('{conditions}', clause.conditions)
1935 .replace(/{groupBy}/g, clause.groupByColumns)
1936 .replace(/{orderBy}/g, clause.orderByColumns)
1937 .replace('{limit}', limit)
1938 .replace('{offset}', offset);
1941 XT.debug('fetch sql1 = ', sql1);
1942 XT.debug('fetch values = ', clause.parameters);
1945 /* First query for matching ids, then get entire result set. */
1946 /* This improves performance over a direct query on the view due */
1947 /* to the way sorting is handled by the query optimizer */
1948 qry = plv8.execute(sql1, clause.parameters) || [];
1949 if (!qry.length) { return [] };
1950 qry.forEach(function (row) {
1952 idParams.push("$" + counter);
1957 sql_etags = "select ver_etag as etag, ver_record_id as id " +
1959 "where ver_table_oid = ( " +
1960 "select pg_class.oid::integer as oid " +
1961 "from pg_class join pg_namespace on relnamespace = pg_namespace.oid " +
1962 /* Note: using $L for quoted literal e.g. 'contact', not an identifier. */
1963 "where nspname = %1$L and relname = %2$L " +
1965 "and ver_record_id in ({ids})";
1966 sql_etags = XT.format(sql_etags, [tableNamespace, table]);
1967 sql_etags = sql_etags.replace('{ids}', idParams.join());
1970 XT.debug('fetch sql_etags = ', sql_etags);
1971 XT.debug('fetch etags_values = ', JSON.stringify(ids));
1973 etags = plv8.execute(sql_etags, ids) || {};
1977 sql2 = XT.format(sql2, [nameSpace.decamelize(), type.decamelize(), pkey]);
1978 sql2 = sql2.replace(/{orderBy}/g, clause.orderBy)
1979 .replace('{ids}', idParams.join());
1982 XT.debug('fetch sql2 = ', sql2);
1983 XT.debug('fetch values = ', JSON.stringify(ids));
1985 ret.data = plv8.execute(sql2, ids) || [];
1987 for (var i = 0; i < ret.data.length; i++) {
1988 ret.data[i] = this.decrypt(nameSpace, type, ret.data[i], encryptionKey);
1991 /* Add etags to result in pkey->etag format. */
1992 for (var j = 0; j < etags.length; j++) {
1993 if (etags[j].id === ret.data[i][pkey]) {
1994 ret.etags[ret.data[i][nkey]] = etags[j].etag;
2000 this.sanitize(nameSpace, type, ret.data, options);
2006 Fetch a metric value.
2008 @param {String} Metric name
2009 @param {String} Return type 'text', 'boolean' or 'number' (default 'text')
2011 fetchMetric: function (name, type) {
2012 var fn = 'fetchmetrictext';
2013 if (type === 'boolean') {
2014 fn = 'fetchmetricbool';
2015 } else if (type === 'number') {
2016 fn = 'fetchmetricvalue';
2018 return plv8.execute("select " + fn + "($1) as resp", [name])[0].resp;
2022 * Retreives a record from the database. If the user does not have appropriate privileges an
2023 * error will be thrown unless the `silentError` option is passed.
2025 * If `context` is passed as an option then a record will only be returned if it exists in the context (parent)
2026 * record which itself must be accessible by the effective user.
2028 * @param {Object} options
2029 * @param {String} [options.nameSpace] Namespace. Required.
2030 * @param {String} [options.type] Type. Required.
2031 * @param {Number} [options.id] Record id. Required.
2032 * @param {Boolean} [options.superUser=false] If true ignore privilege checking.
2033 * @param {String} [options.encryptionKey] Encryption key
2034 * @param {Boolean} [options.silentError=false] Silence errors
2035 * @param {Object} [options.context] Context
2036 * @param {String} [options.context.nameSpace] Context namespace.
2037 * @param {String} [options.context.type] The type of context object.
2038 * @param {String} [options.context.value] The value of the context's primary key.
2039 * @param {String} [options.context.relation] The name of the attribute on the type to which this record is related.
2042 retrieveRecord: function (options) {
2043 options = options ? options : {};
2044 options.obtainLock = false;
2046 var id = options.id,
2047 nameSpace = options.nameSpace,
2048 type = options.type,
2049 map = this.fetchOrm(nameSpace, type),
2050 context = options.context,
2051 encryptionKey = options.encryptionKey,
2053 lockTable = map.lockTable || map.table,
2054 nkey = XT.Orm.naturalKey(map),
2056 pkey = XT.Orm.primaryKey(map),
2058 nameSpace: nameSpace,
2065 throw new Error('No key found for {nameSpace}.{type}'
2066 .replace("{nameSpace}", nameSpace)
2067 .replace("{type}", type));
2070 /* If this object uses a natural key, go get the primary key id. */
2072 id = this.getId(map, id);
2078 /* Context means search for this record inside another. */
2080 context.nameSpace = context.nameSpace || context.recordType.beforeDot();
2081 context.type = context.type || context.recordType.afterDot()
2082 context.map = this.fetchOrm(context.nameSpace, context.type);
2083 context.prop = XT.Orm.getProperty(context.map, context.relation);
2084 context.pertinentExtension = XT.Orm.getProperty(context.map, context.relation, true);
2085 context.underlyingTable = context.pertinentExtension.table,
2086 context.underlyingNameSpace = this.getNamespaceFromNamespacedTable(context.underlyingTable);
2087 context.underlyingType = this.getTableFromNamespacedTable(context.underlyingTable);
2088 context.fkey = context.prop.toMany.inverse;
2089 context.fkeyColumn = context.prop.toMany.column;
2090 context.pkey = XT.Orm.naturalKey(context.map) || XT.Orm.primaryKey(context.map);
2091 params.attribute = context.pkey;
2092 params.value = context.value;
2094 join = 'join %1$I.%2$I on (%1$I.%2$I.%3$I = %4$I.%5$I)';
2095 join = XT.format(join, [
2096 context.underlyingNameSpace,
2097 context.underlyingType,
2104 /* Validate - don't bother running the query if the user has no privileges. */
2105 if(!options.superUser && !context && !this.checkPrivileges(nameSpace, type)) {
2106 if (options.silentError) {
2109 throw new handleError("Unauthorized", 401);
2113 ret.etag = this.getVersion(map, id);
2115 /* Obtain lock if required. */
2117 ret.lock = this.tryLock(lockTable, id, options);
2121 sql = 'select %1$I.* from %2$I.%1$I {join} where %1$I.%3$I = $1;';
2122 sql = sql.replace(/{join}/, join);
2123 sql = XT.format(sql, [type.decamelize(), nameSpace.decamelize(), pkey]);
2125 /* Query the map. */
2127 XT.debug('retrieveRecord sql = ', sql);
2128 XT.debug('retrieveRecord values = ', [id]);
2130 ret.data = plv8.execute(sql, [id])[0] || {};
2133 /* Check privileges again, this time against record specific criteria where applicable. */
2134 if(!options.superUser && !this.checkPrivileges(nameSpace, type, ret.data)) {
2135 if (options.silentError) {
2138 throw new handleError("Unauthorized", 401);
2141 /* Decrypt result where applicable. */
2142 ret.data = this.decrypt(nameSpace, type, ret.data, encryptionKey);
2145 this.sanitize(nameSpace, type, ret.data, options);
2147 /* Return the results. */
2152 * Remove unprivileged attributes, primary and foreign keys from the data.
2153 * Only removes the primary key if a natural key has been specified in the ORM.
2154 * Also format for printing using XT.format functions if printFormat=true'
2156 * @param {String} Namespace
2157 * @param {String} Type
2158 * @param {Object|Array} Data
2159 * @param {Object} Options
2160 * @param {Boolean} [options.includeKeys=false] Do not remove primary and foreign keys.
2161 * @param {Boolean} [options.superUser=false] Do not remove unprivileged attributes.
2162 * @param {Boolean} [options.printFormat=true] Format for printing.
2164 sanitize: function (nameSpace, type, data, options) {
2165 options = options || {};
2166 if (options.includeKeys && options.superUser) { return; }
2167 if (XT.typeOf(data) !== "array") { data = [data]; }
2168 var orm = this.fetchOrm(nameSpace, type),
2169 pkey = XT.Orm.primaryKey(orm),
2170 nkey = XT.Orm.naturalKey(orm),
2171 props = orm.properties,
2172 attrPriv = orm.privileges && orm.privileges.attribute ?
2173 orm.privileges.attribute : false,
2174 inclKeys = options.includeKeys,
2175 superUser = options.superUser,
2176 printFormat = options.printFormat,
2187 check = function (p) {
2188 return p.name === itemAttr;
2191 for (var c = 0; c < data.length; c++) {
2194 /* Remove primary key if applicable */
2195 if (!inclKeys && nkey && nkey !== pkey) { delete item[pkey]; }
2197 for (itemAttr in item) {
2198 if (!item.hasOwnProperty(itemAttr)) {
2201 filteredProps = orm.properties.filter(check);
2203 if (filteredProps.length === 0 && orm.extensions.length > 0) {
2204 /* Try to get the orm prop from an extension if it's not in the core*/
2205 orm.extensions.forEach(function (ext) {
2206 if (filteredProps.length === 0) {
2207 filteredProps = ext.properties.filter(check);
2212 /* Remove attributes not found in the ORM */
2213 if (filteredProps.length === 0) {
2214 delete item[itemAttr];
2216 prop = filteredProps[0];
2219 /* Remove unprivileged attribute if applicable */
2220 if (!superUser && attrPriv && attrPriv[prop.name] &&
2221 (attrPriv[prop.name].view !== undefined) &&
2222 !this.checkPrivilege(attrPriv[prop.name].view)) {
2223 delete item[prop.name];
2226 /* Format for printing if printFormat and not an object */
2227 if (printFormat && !prop.toOne && !prop.toMany) {
2228 switch(prop.attr.type) {
2230 preOffsetDate = item[itemAttr];
2231 offsetDate = preOffsetDate &&
2232 new Date(preOffsetDate.valueOf() + 60000 * preOffsetDate.getTimezoneOffset());
2233 item[itemAttr] = XT.formatDate(offsetDate).formatdate;
2236 item[itemAttr] = XT.formatCost(item[itemAttr]).formatcost.toString();
2239 item[itemAttr] = XT.formatNumeric(item[itemAttr], "").formatnumeric.toString();
2242 item[itemAttr] = XT.formatMoney(item[itemAttr]).formatmoney.toString();
2245 item[itemAttr] = XT.formatSalesPrice(item[itemAttr]).formatsalesprice.toString();
2247 case "PurchasePrice":
2248 item[itemAttr] = XT.formatPurchPrice(item[itemAttr]).formatpurchprice.toString();
2250 case "ExtendedPrice":
2251 item[itemAttr] = XT.formatExtPrice(item[itemAttr]).formatextprice.toString();
2254 item[itemAttr] = XT.formatQty(item[itemAttr]).formatqty.toString();
2257 item[itemAttr] = XT.formatQtyPer(item[itemAttr]).formatqtyper.toString();
2259 case "UnitRatioScale":
2260 item[itemAttr] = XT.formatRatio(item[itemAttr]).formatratio.toString();
2263 item[itemAttr] = XT.formatPrcnt(item[itemAttr]).formatprcnt.toString();
2266 item[itemAttr] = XT.formatWeight(item[itemAttr]).formatweight.toString();
2269 item[itemAttr] = (item[itemAttr] || "").toString();
2273 /* Handle composite types */
2274 if (prop.toOne && prop.toOne.isNested && item[prop.name]) {
2275 this.sanitize(nameSpace, prop.toOne.type, item[prop.name], options);
2276 } else if (prop.toMany && prop.toMany.isNested && item[prop.name]) {
2277 for (var n = 0; n < item[prop.name].length; n++) {
2278 val = item[prop.name][n];
2280 /* Remove foreign key if applicable */
2281 if (!inclKeys) { delete val[prop.toMany.inverse]; }
2282 this.sanitize(nameSpace, prop.toMany.type, val, options);
2290 * Returns a array of key value pairs of metric settings that correspond with an array of passed keys.
2292 * @param {Array} array of metric names
2295 retrieveMetrics: function (keys) {
2300 sql = 'select metric_name as setting, metric_value as value '
2302 + 'where metric_name in ({literals})';
2304 for (var i = 0; i < keys.length; i++) {
2305 literals[i] = "%" + (i + 1) + "$L";
2308 sql = sql.replace(/{literals}/, literals.join(','));
2309 sql = XT.format(sql, keys)
2312 XT.debug('retrieveMetrics sql = ', sql);
2314 qry = plv8.execute(sql);
2316 /* Recast where applicable. */
2317 for (var i = 0; i < qry.length; i++) {
2318 prop = qry[i].setting;
2319 if(qry[i].value === 't') { ret[prop] = true; }
2320 else if(qry[i].value === 'f') { ret[prop] = false }
2321 else if(!isNaN(qry[i].value)) { ret[prop] = qry[i].value - 0; }
2322 else { ret[prop] = qry[i].value; }
2325 /* Make sure there is a result at all times */
2326 keys.forEach(function (key) {
2327 if (ret[key] === undefined) { ret[key] = null; }
2334 * Creates and returns a lock for a given table. Defaults to a time based lock of 30 seconds
2335 * unless aternate timeout option or process id (pid) is passed. If a pid is passed, the lock
2336 * is considered infinite as long as the pid is valid. If a previous lock key is passed and it is
2337 * valid, a new lock will be granted.
2339 * @param {String | Number} Table name or oid
2340 * @param {Number} Record id
2341 * @param {Object} Options
2342 * @param {Number} [options.timeout=30]
2343 * @param {Number} [options.pid] Process id
2344 * @param {Number} [options.key] Key
2345 * @param {Boolean} [options.obtainLock=true] If false, only checks for existing lock
2347 tryLock: function (table, id, options) {
2348 options = options ? options : {};
2350 var deleteSql = "delete from xt.lock where lock_id = $1;",
2351 timeout = options.timeout || 30,
2352 expires = new Date(),
2354 insertSqlExp = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_expires) " +
2355 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2356 insertSqlPid = "insert into xt.lock (lock_table_oid, lock_record_id, lock_username, lock_pid) " +
2357 "values ($1, $2, $3, $4) returning lock_id, lock_effective;",
2362 pid = options.pid || null,
2363 pidSql = "select usename, procpid " +
2364 "from pg_stat_activity " +
2365 "where datname=current_database() " +
2366 " and usename=$1 " +
2369 selectSql = "select * " +
2371 "where lock_table_oid = $1 " +
2372 " and lock_record_id = $2;",
2373 username = XT.username;
2375 /* If passed a table name, look up the oid. */
2376 oid = typeof table === "string" ? this.getTableOid(table) : table;
2378 if (DEBUG) XT.debug("Trying lock table", [oid, id]);
2380 /* See if there are existing lock(s) for this record. */
2382 XT.debug('tryLock sql = ', selectSql);
2383 XT.debug('tryLock values = ', [oid, id]);
2385 query = plv8.execute(selectSql, [oid, id]);
2387 /* Validate result */
2388 if (query.length > 0) {
2389 while (query.length) {
2390 lock = query.shift();
2392 /* See if we are confirming our own lock. */
2393 if (options.key && options.key === lock.lock_id) {
2394 /* Go on and we'll get a new lock. */
2396 /* Make sure if they are pid locks users is still connected. */
2397 } else if (lock.lock_pid) {
2399 XT.debug('tryLock sql = ', pidSql);
2400 XT.debug('tryLock values = ', [lock.lock_username, lock.lock_pid]);
2402 pcheck = plv8.execute(pidSql, [lock.lock_username, lock.lock_pid]);
2403 if (pcheck.length) { break; } /* valid lock */
2405 lockExp = new Date(lock.lock_expires);
2406 if (DEBUG) { XT.debug("Lock found", [lockExp > expires, lockExp, expires]); }
2407 if (lockExp > expires) { break; } /* valid lock */
2410 /* Delete invalid or expired lock. */
2412 XT.debug('tryLock sql = ', deleteSql);
2413 XT.debug('tryLock values = ', [lock.lock_id]);
2415 plv8.execute(deleteSql, [lock.lock_id]);
2420 if (DEBUG) XT.debug("Lock found", lock.lock_username);
2423 username: lock.lock_username,
2424 effective: lock.lock_effective
2429 if (options.obtainLock === false) { return; }
2431 if (DEBUG) { XT.debug("Creating lock."); }
2432 if (DEBUG) { XT.debug('tryLock sql = ', insertSqlPid); }
2435 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, pid]); }
2436 lock = plv8.execute(insertSqlPid, [oid, id, username, pid])[0];
2438 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2439 if (DEBUG) { XT.debug('tryLock values = ', [oid, id, username, expires]); }
2440 lock = plv8.execute(insertSqlExp, [oid, id, username, expires])[0];
2443 if (DEBUG) { XT.debug("Lock returned is", lock.lock_id); }
2447 effective: lock.lock_effective,
2453 * Release a lock. Pass either options with a key, or table, id and username.
2455 * @param {Object} Options: key or table and id
2457 releaseLock: function (options) {
2459 sqlKey = 'delete from xt.lock where lock_id = $1;',
2460 sqlUsr = 'delete from xt.lock where lock_table_oid = $1 and lock_record_id = $2 and lock_username = $3;',
2461 username = XT.username;
2465 XT.debug('releaseLock sql = ', sqlKey);
2466 XT.debug('releaseLock values = ', [options.key]);
2468 plv8.execute(sqlKey, [options.key]);
2470 oid = typeof options.table === "string" ? this.getTableOid(options.table) : options.table;
2473 XT.debug('releaseLock sql = ', sqlUsr);
2474 XT.debug('releaseLock values = ', [oid, options.id, username]);
2476 plv8.execute(sqlUsr, [oid, options.id, username]);
2482 /* This deprecated function is still used by three dispatch functions. We should delete
2483 this as soon as we refactor those, and then rename buildClauseOptimized to buildClause */
2484 buildClause: function (nameSpace, type, parameters, orderBy) {
2485 parameters = parameters || [];
2487 var arrayIdentifiers = [],
2498 orderByIdentifiers = [],
2500 orm = this.fetchOrm(nameSpace, type),
2506 privileges = orm.privileges,
2510 ret.conditions = "";
2511 ret.parameters = [];
2513 /* Handle privileges. */
2514 if (orm.isNestedOnly) { plv8.elog(ERROR, 'Access Denied'); }
2518 (!this.checkPrivilege(privileges.all.read) &&
2519 !this.checkPrivilege(privileges.all.update)))
2521 privileges.personal &&
2522 (this.checkPrivilege(privileges.personal.read) ||
2523 this.checkPrivilege(privileges.personal.update))
2527 attribute: privileges.personal.properties,
2529 isUsernamePrivFilter: true,
2534 /* Handle parameters. */
2535 if (parameters.length) {
2536 for (var i = 0; i < parameters.length; i++) {
2538 param = parameters[i];
2539 op = param.operator || '=';
2559 for (var c = 0; c < param.value.length; c++) {
2560 ret.parameters.push(param.value[c]);
2561 param.value[c] = '$' + count;
2567 for (var c = 0; c < param.value.length; c++) {
2568 ret.parameters.push(param.value[c]);
2569 param.value[c] = '$' + count;
2574 plv8.elog(ERROR, 'Invalid operator: ' + op);
2577 /* Handle characteristics. This is very specific to xTuple,
2578 and highly dependant on certain table structures and naming conventions,
2579 but otherwise way too much work to refactor in an abstract manner right now. */
2580 if (param.isCharacteristic) {
2583 param.value = ' ARRAY[' + param.value.join(',') + ']';
2586 /* Booleans are stored as strings. */
2587 if (param.value === true) {
2589 } else if (param.value === false) {
2593 /* Yeah, it depends on a property called 'characteristics'... */
2594 prop = XT.Orm.getProperty(orm, 'characteristics');
2596 /* Build the characteristics query clause. */
2597 identifiers.push(prop.toMany.inverse);
2598 identifiers.push(orm.nameSpace.toLowerCase());
2599 identifiers.push(prop.toMany.type.decamelize());
2600 identifiers.push(param.attribute);
2601 identifiers.push(param.value);
2603 charSql = 'id in (' +
2604 ' select %' + (identifiers.length - 4) + '$I '+
2605 ' from %' + (identifiers.length - 3) + '$I.%' + (identifiers.length - 2) + '$I ' +
2606 ' join char on (char_name = characteristic)' +
2608 /* Note: Not using $i for these. L = literal here. These is not identifiers. */
2609 ' and char_name = %' + (identifiers.length - 1) + '$L ' +
2610 ' and value ' + op + ' %' + (identifiers.length) + '$L ' +
2613 clauses.push(charSql);
2615 /* Array comparisons handle another way. e.g. %1$I !<@ ARRAY[$1,$2] */
2616 } else if (op === '<@' || op === '!<@') {
2617 /* Handle paths if applicable. */
2618 if (param.attribute.indexOf('.') > -1) {
2619 parts = param.attribute.split('.');
2620 childOrm = this.fetchOrm(nameSpace, type);
2622 pcount = params.length - 1;
2624 for (var n = 0; n < parts.length; n++) {
2625 /* Validate attribute. */
2626 prop = XT.Orm.getProperty(childOrm, parts[n]);
2628 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
2631 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2632 identifiers.push(parts[n]);
2633 params[pcount] += "%" + identifiers.length + "$I";
2634 if (n < parts.length - 1) {
2635 params[pcount] = "(" + params[pcount] + ").";
2636 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2638 params[pcount] += op + ' ARRAY[' + param.value.join(',') + ']';
2642 identifiers.push(param.attribute);
2643 params.push("%" + identifiers.length + "$I " + op + ' ARRAY[' + param.value.join(',') + ']');
2644 pcount = params.length - 1;
2646 clauses.push(params[pcount]);
2648 /* Everything else handle another. */
2650 if (XT.typeOf(param.attribute) !== 'array') {
2651 param.attribute = [param.attribute];
2654 for (var c = 0; c < param.attribute.length; c++) {
2655 /* Handle paths if applicable. */
2656 if (param.attribute[c].indexOf('.') > -1) {
2657 parts = param.attribute[c].split('.');
2658 childOrm = this.fetchOrm(nameSpace, type);
2660 pcount = params.length - 1;
2663 /* Check if last part is an Array. */
2664 for (var m = 0; m < parts.length; m++) {
2665 /* Validate attribute. */
2666 prop = XT.Orm.getProperty(childOrm, parts[m]);
2668 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[m]);
2671 if (m < parts.length - 1) {
2672 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2673 } else if (prop.attr && prop.attr.type === 'Array') {
2674 /* The last property in the path is an array. */
2676 params[pcount] = '$' + count;
2680 /* Reset the childOrm to parent. */
2681 childOrm = this.fetchOrm(nameSpace, type);
2683 for (var n = 0; n < parts.length; n++) {
2684 /* Validate attribute. */
2685 prop = XT.Orm.getProperty(childOrm, parts[n]);
2687 plv8.elog(ERROR, 'Attribute not found in object map: ' + parts[n]);
2690 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
2691 if (param.isUsernamePrivFilter && isArray) {
2692 identifiers.push(parts[n]);
2693 arrayIdentifiers.push(identifiers.length);
2695 if (n < parts.length - 1) {
2696 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2699 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2700 identifiers.push(parts[n]);
2701 params[pcount] += "%" + identifiers.length + "$I";
2703 if (n < parts.length - 1) {
2704 params[pcount] = "(" + params[pcount] + ").";
2705 childOrm = this.fetchOrm(nameSpace, prop.toOne.type);
2706 } else if (param.isLower) {
2707 params[pcount] = "lower(" + params[pcount] + ")";
2712 /* Validate attribute. */
2713 prop = XT.Orm.getProperty(orm, param.attribute[c]);
2715 plv8.elog(ERROR, 'Attribute not found in object map: ' + param.attribute[c]);
2718 identifiers.push(param.attribute[c]);
2720 /* Do a persional privs array search e.g. 'admin' = ANY (usernames_array). */
2721 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested) ||
2722 (prop.attr && prop.attr.type === 'Array'))) {
2724 params.push('$' + count);
2725 pcount = params.length - 1;
2726 arrayIdentifiers.push(identifiers.length);
2728 params.push("%" + identifiers.length + "$I");
2729 pcount = params.length - 1;
2733 /* Add persional privs array search. */
2734 if (param.isUsernamePrivFilter && ((prop.toMany && !prop.isNested)
2735 || (prop.attr && prop.attr.type === 'Array') || isArray)) {
2737 /* e.g. 'admin' = ANY (usernames_array) */
2739 params[pcount] += ' ' + op + ' ANY (';
2741 /* Build path. e.g. ((%1$I).%2$I).%3$I */
2742 for (var f =0; f < arrayIdentifiers.length; f++) {
2743 arrayParams += '%' + arrayIdentifiers[f] + '$I';
2744 if (f < arrayIdentifiers.length - 1) {
2745 arrayParams = "(" + arrayParams + ").";
2748 params[pcount] += arrayParams + ')';
2750 /* Add optional is null clause. */
2751 } else if (parameters[i].includeNull) {
2752 /* e.g. %1$I = $1 or %1$I is null */
2753 params[pcount] = params[pcount] + " " + op + ' $' + count + ' or ' + params[pcount] + ' is null';
2755 /* e.g. %1$I = $1 */
2756 params[pcount] += " " + op + ' $' + count;
2759 orClause.push(params[pcount]);
2762 /* If more than one clause we'll get: (%1$I = $1 or %1$I = $2 or %1$I = $3) */
2763 clauses.push('(' + orClause.join(' or ') + ')');
2765 ret.parameters.push(param.value);
2770 ret.conditions = (clauses.length ? '(' + XT.format(clauses.join(' and '), identifiers) + ')' : ret.conditions) || true;
2772 /* Massage ordeBy with quoted identifiers. */
2774 for (var i = 0; i < orderBy.length; i++) {
2775 /* Handle path case. */
2776 if (orderBy[i].attribute.indexOf('.') > -1) {
2777 parts = orderBy[i].attribute.split('.');
2779 orderByParams.push("");
2780 pcount = orderByParams.length - 1;
2782 for (var n = 0; n < parts.length; n++) {
2783 prop = XT.Orm.getProperty(orm, parts[n]);
2785 plv8.elog(ERROR, 'Attribute not found in map: ' + parts[n]);
2787 orderByIdentifiers.push(parts[n]);
2788 orderByParams[pcount] += "%" + orderByIdentifiers.length + "$I";
2790 if (n < parts.length - 1) {
2791 orderByParams[pcount] = "(" + orderByParams[pcount] + ").";
2792 orm = this.fetchOrm(nameSpace, prop.toOne.type);
2798 prop = XT.Orm.getProperty(orm, orderBy[i].attribute);
2800 plv8.elog(ERROR, 'Attribute not found in map: ' + orderBy[i].attribute);
2802 orderByIdentifiers.push(orderBy[i].attribute);
2803 orderByParams.push("%" + orderByIdentifiers.length + "$I");
2804 pcount = orderByParams.length - 1;
2807 if (orderBy[i].isEmpty) {
2808 orderByParams[pcount] = "length(" + orderByParams[pcount] + ")=0";
2810 if (orderBy[i].descending) {
2811 orderByParams[pcount] += " desc";
2814 list.push(orderByParams[pcount])
2818 ret.orderBy = list.length ? XT.format('order by ' + list.join(','), orderByIdentifiers) : '';
2823 * Renew a lock. Defaults to rewing the lock for 30 seconds.
2825 * @param {Number} Key
2826 * @params {Object} Options: timeout
2827 * @returns {Date} New expiration or false.
2829 renewLock: function (key, options) {
2830 var expires = new Date(),
2832 selectSql = "select * from xt.lock where lock_id = $1;",
2833 timeout = options && options.timeout ? options.timeout : 30,
2834 updateSql = "update xt.lock set lock_expires = $1 where lock_id = $2;";
2836 if (typeof key !== "number") { return false; }
2837 expires = new Date(expires.setSeconds(expires.getSeconds() + timeout));
2840 XT.debug('renewLock sql = ', selectSql);
2841 XT.debug('renewLock values = ', [key]);
2843 query = plv8.execute(selectSql, [key]);
2847 XT.debug('renewLock sql = ', updateSql);
2848 XT.debug('renewLock values = ', [expires, key]);
2850 plv8.execute(updateSql, [expires, key]);