1 select xt.install_js('XT','Schema','xtuple', $$
8 * The XT.Schema class includes all functions necessary to return a JSON-Schema
9 * (http://tools.ietf.org/html/draft-zyp-json-schema-03) for the ORMs.
14 XT.Schema.isDispatchable = true;
17 * Return a JSON-Schema property object that MAY include type, format, required
18 * minimum/maximum number, minLength/maxLength string based on a column's
19 * PostgreSQL information_schema.columns record.
21 * @param {String} An ORM's "table" property formated like "schema.table" or "table".
22 * @param {String} An ORM's properties' "column" attibute formatted like "column_name".
25 XT.Schema.columnInfo = function(ormSchemaTable, ormColumns) {
39 /* Get the schema and table from the ORM table property. */
40 schemaTable = ormSchemaTable.split(".");
41 if (!schemaTable[1]) {
43 table = schemaTable[0];
45 schema = schemaTable[0];
46 table = schemaTable[1];
49 /* Check if this is a function and not a table. */
50 if (table.indexOf("(") !== -1) {
51 /* Extract just the function name from table. */
52 func = table.substring(0,table.indexOf("("));
54 /* Look up the "RETURNS SETOF" type. */
58 'from information_schema.routines ' +
60 'and specific_schema = $1 ' +
61 'and routine_name = $2; ';
63 funcRes = plv8.execute(funcSql, [schema, func]);
65 if (funcRes.length === 1) {
66 /* Name of the schema that the return data type of the function is defined in. */
67 schema = funcRes[0].type_udt_schema;
68 /* Name of the return data type of the function. */
69 table = funcRes[0].type_udt_name;
73 /* Query to get column's PostgreSQL datatype and other schema info. */
77 'character_maximum_length, ' +
78 'is_nullable, ' + /* Pull in required from NOT NULL in database. */
80 "WHEN column_default ILIKE 'nextval%' THEN 'AUTO_INCREMENT' " +
82 'END AS column_default, ' + /* Used to determine if integer is really an AUTO_INCREMENT serial. */
83 'col_description( ' + /* Pull in column comments from db. */
89 'and relname = information_schema.columns.table_name ' +
90 'and relnamespace = (select oid from pg_namespace where nspname = information_schema.columns.table_schema) ' +
94 'from information_schema.columns ' +
96 'and table_schema = $1 ' +
97 'and table_name = $2 ' +
98 'and column_name in (';
100 /* Build column_name in (...) string. */
101 for (var i = 0; i < ormColumns.length; i++) {
103 colNames = colNames + "'" + ormColumns[i] + "'";
105 colNames = colNames + ", '" + ormColumns[i] + "'";
109 /* TODO - $3 in sql doesn't work for column_name in (...). */
110 sql = sql + colNames + ")";
112 res = plv8.execute(sql, [schema, table]);
118 for (var i = 0; i < res.length; i++) {
119 ret[res[i].column_name] = {};
121 /* Set "description" if column "comment" is not null. */
122 if (res[i].comment) {
123 ret[res[i].column_name].description = res[i].comment;
126 /* Set "required" if column is not "is_nullable". */
127 if (res[i].is_nullable === "NO") {
128 /* Check if this is a fetchable number. */
129 fetchSql = 'select orderseq_id from orderseq where orderseq_table = $1 and orderseq_numcol = $2;';
130 fetchRes = plv8.execute(fetchSql, [table, res[i].column_name]);
132 /* Set to required when this is not a fetchable number. If it is fetchable, see xt.post(). */
133 if (!fetchRes.length) {
134 ret[res[i].column_name].required = true;
138 /* Map PostgreSQL datatype to JSON-Schema type and format. */
139 /* https://developers.google.com/discovery/v1/type-format */
140 /* type: http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.1 */
141 /* format: http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.23 */
142 /* min max: http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.9 */
143 /* lenght: http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.17 */
144 /* required: http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.7 */
145 switch (res[i].data_type) {
147 ret[res[i].column_name].type = "array";
150 /* http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL */
151 if (res[i].is_nullable === "NO" && res[i].column_default === "AUTO_INCREMENT") {
152 ret[res[i].column_name].type = "string";
153 ret[res[i].column_name].format = "uint64";
154 ret[res[i].column_name].minimum = "1";
155 ret[res[i].column_name].maximum = "9223372036854775807";
157 ret[res[i].column_name].type = "string";
158 ret[res[i].column_name].format = "int64";
159 ret[res[i].column_name].minimum = "-9223372036854775808";
160 ret[res[i].column_name].maximum = "9223372036854775807";
164 ret[res[i].column_name].type = "string";
165 ret[res[i].column_name].format = "uint64";
166 ret[res[i].column_name].minimum = "1";
167 ret[res[i].column_name].maximum = "9223372036854775807";
170 ret[res[i].column_name].type = "boolean";
173 ret[res[i].column_name].type = "string";
177 ret[res[i].column_name].type = "string";
178 ret[res[i].column_name].minLength = res[i].character_maximum_length ? res[i].character_maximum_length : null;
179 ret[res[i].column_name].maxLength = res[i].character_maximum_length ? res[i].character_maximum_length : null;
181 case "character varying":
183 ret[res[i].column_name].type = "string";
184 ret[res[i].column_name].maxLength = res[i].character_maximum_length ? res[i].character_maximum_length : null;
187 ret[res[i].column_name].type = "string";
188 ret[res[i].column_name].format = "date";
193 ret[res[i].column_name].type = "number";
194 ret[res[i].column_name].format = "float";
196 case "double precision":
197 ret[res[i].column_name].type = "number";
198 ret[res[i].column_name].format = "double";
201 /* http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL */
202 if (res[i].is_nullable === "NO" && res[i].column_default === "AUTO_INCREMENT") {
203 ret[res[i].column_name].type = "integer";
204 ret[res[i].column_name].format = "uint32";
205 ret[res[i].column_name].minimum = "1";
206 ret[res[i].column_name].maximum = "2147483647";
208 ret[res[i].column_name].type = "integer";
209 ret[res[i].column_name].format = "int32";
210 ret[res[i].column_name].minimum = "-2147483648";
211 ret[res[i].column_name].maximum = "2147483647";
215 ret[res[i].column_name].type = "number";
216 ret[res[i].column_name].format = "float";
217 ret[res[i].column_name].minimum = "-92233720368547758.08";
218 ret[res[i].column_name].maximum = "92233720368547758.07";
221 ret[res[i].column_name].type = "string";
224 ret[res[i].column_name].type = "integer";
225 ret[res[i].column_name].format = "uint32";
226 ret[res[i].column_name].minimum = "1";
227 ret[res[i].column_name].maximum = "2147483647";
230 ret[res[i].column_name].type = "integer";
231 ret[res[i].column_name].format = "int32";
232 ret[res[i].column_name].minimum = "-32768";
233 ret[res[i].column_name].maximum = "32767";
236 ret[res[i].column_name].type = "string";
239 case "time without time zone":
240 ret[res[i].column_name].type = "string";
241 ret[res[i].column_name].format = "time";
244 case "timestamp without time zone":
245 ret[res[i].column_name].type = "string";
246 ret[res[i].column_name].format = "date-time";
248 case "time with time zone":
250 case "timestamp with time zone":
251 ret[res[i].column_name].type = "string";
252 ret[res[i].column_name].format = "date-time";
256 ret[res[i].column_name].type = "string";
259 ret[res[i].column_name].type = "string";
260 ret[res[i].column_name].format = "uuid";
263 throw new Error("Unsupported datatype format. No known conversion from PostgreSQL to JSON-Schema.");
267 /* return the results */
272 * Return a JSON-Schema for an ORM to be used for an API Discovery Service
273 * resource's "properties".
275 * @param {Object} An ORM object or a basic one with just orm.nameSpace and orm.type.
278 XT.Schema.getProperties = function(orm) {
279 /* Load ORM if this function was called with just orm.nameSpace and orm.type. */
280 orm = orm.properties ? orm : XT.Orm.fetch(orm.nameSpace, orm.type, {"silentError": true});
281 if (!orm || !orm.properties) {
285 var attrPriv = orm.privileges && orm.privileges.attribute ? orm.privileges.attribute : false,
289 nkey = XT.Orm.naturalKey(orm),
290 pkey = XT.Orm.primaryKey(orm),
292 ret = {"id": orm.type, "type": "object"},
296 schemaColumnInfo = {},
297 schemaTable = orm.table;
299 if (orm.extensions.length > 0) {
300 /* Loop through the ORM extensions and add their properties into main properties. */
301 for (var i = 0; i < orm.extensions.length; i++) {
302 for (var j = 0; j < orm.extensions[i].properties.length; j++) {
303 var propLength = orm.properties.length;
305 orm.properties[propLength] = orm.extensions[i].properties[j];
307 /* Set extTable property to be used to get extension table column properties. */
308 if (orm.extensions[i].table !== schemaTable) {
309 orm.properties[propLength].extTable = orm.extensions[i].table;
315 /* Loop through the ORM properties and get the columns. */
316 for (var i = 0; i < orm.properties.length; i++) {
317 prop = orm.properties[i];
320 /* Skip primaryKey if there is a natualKey that's a different property. */
321 if (nkey && orm.properties[i].name === pkey && orm.properties[i].name !== nkey) {
325 /* Skip this property if it has a attr.value. Those are just used for relation associaiton queries. */
326 if (orm.properties[i].attr && orm.properties[i].attr.value) {
330 /* Skip this property if its ORM column priv is set to false. */
331 /* TODO: Not checking for superuser here. This is more to just check if the column is set to false. */
332 if (attrPriv && attrPriv[orm.properties[i].name] &&
333 (attrPriv[orm.properties[i].name].view !== undefined) &&
334 !XT.Data.checkPrivilege(attrPriv[orm.properties[i].name].view)) {
338 if (!ret.properties) {
339 /* Initialize properties. */
343 /* Add title and description properties. */
344 /* For readability only, title should be first, therefore a redundant if. */
345 if ((orm.properties[i].attr && orm.properties[i].attr.column) ||
346 (orm.properties[i].toOne) ||
347 (orm.properties[i].toMany)) {
349 /* Initialize named properties. */
350 ret.properties[orm.properties[i].name] = {};
351 ret.properties[orm.properties[i].name].title = orm.properties[i].name.humanize();
356 * By definition a derived property is not backed by a column, so use the
357 * 'name' property to stand in for the column.
359 if (attr && attr.derived) {
360 attr.column = prop.name;
364 if (orm.properties[i].attr && orm.properties[i].attr.column) {
365 if (orm.properties[i].extTable) {
366 /* Build ext object to be used to get extension table column properties. */
367 if (!ext[orm.properties[i].extTable]) {
368 ext[orm.properties[i].extTable] = [];
370 ext[orm.properties[i].extTable].push(orm.properties[i].attr.column);
372 columns.push(orm.properties[i].attr.column);
375 /* Add required override based off of ORM's property. */
376 if (orm.properties[i].attr.required) {
377 /* Check if this is a fetchable number. */
378 if (orm.orderSequence && orm.properties[i].name === nkey && nkey !== pkey) {
379 /* Do not set this property to required. See xt.post() which will */
380 /* use XM.Model.fetchNumber() for it. */
382 ret.properties[orm.properties[i].name].required = true;
386 /* Add key flag. This isn't part of JSON-Schema, but very useful for URIs. */
387 /* example.com/resource/{key} */
388 /* JSON-Schema allows for additional custom properites like this. */
389 if (nkey && orm.properties[i].name === nkey) {
390 ret.properties[orm.properties[i].name].isKey = true;
391 } else if (!nkey && orm.properties[i].name === pkey) {
392 ret.properties[orm.properties[i].name].isKey = true;
396 else if (orm.properties[i].toOne) {
397 if (orm.properties[i].toOne.isNested) {
398 ret.properties[orm.properties[i].name].type = "object";
399 ret.properties[orm.properties[i].name]["$ref"] = orm.properties[i].toOne.type;
402 relatedORM = XT.Orm.fetch(orm.nameSpace, orm.properties[i].toOne.type, {"silentError": true});
403 relatedKey = XT.Orm.naturalKey(relatedORM) || XT.Orm.primaryKey(relatedORM);
404 relatedKeyProp = XT.Orm.getProperty(relatedORM, relatedKey);
406 if (relatedKeyProp && relatedKeyProp.attr && relatedKeyProp.attr.type) {
407 ret.properties[orm.properties[i].name].type = relatedKeyProp.attr.type;
409 plv8.elog(ERROR, "No attr.type found for ORM: " + orm.properties[i].toOne.type);
412 /* This is an array of related keys, not a full object. */
413 /* Make the $ref to the relation's natural key. */
414 /* Using JSON-Schema $ref paths like this: */
415 /* http://json-schema.org/latest/json-schema-validation.html#rfc.section.5.5.7.2 */
416 /* See also: http://www.sitepen.com/blog/2008/06/17/json-referencing-in-dojo/ */
417 ret.properties[orm.properties[i].name]["$ref"] = orm.properties[i].toOne.type + "/" + relatedKey;
420 /* Add required override based off of ORM's property. */
421 if (orm.properties[i].toOne.required) {
422 ret.properties[orm.properties[i].name].required = true;
425 /* toMany property */
426 else if (orm.properties[i].toMany) {
427 /* Add required override based off of ORM's property. */
428 if (orm.properties[i].toMany.required) {
429 ret.properties[orm.properties[i].name].required = true;
432 if (orm.properties[i].toMany.isNested) {
433 ret.properties[orm.properties[i].name].type = "object";
434 ret.properties[orm.properties[i].name].items = {"$ref": orm.properties[i].toMany.type};
436 /* TODO: Assuming "XM" here... */
437 relatedORM = XT.Orm.fetch("XM", orm.properties[i].toMany.type, {"silentError": true});
438 relatedKey = XT.Orm.naturalKey(relatedORM) || XT.Orm.primaryKey(relatedORM);
440 ret.properties[orm.properties[i].name].type = "array";
442 /* This is an array of related keys, not a full object. */
443 /* Make the $ref to the relation's natural key. */
444 /* Using JSON-Schema $ref paths like this: */
445 /* http://json-schema.org/latest/json-schema-validation.html#rfc.section.5.5.7.2 */
446 /* See also: http://www.sitepen.com/blog/2008/06/17/json-referencing-in-dojo/ */
447 ret.properties[orm.properties[i].name].items = {
448 "$ref": orm.properties[i].toMany.type + "/" + relatedKey
454 /* You broke it. We should not be here. */
455 throw new Error("Invalid ORM property. Unable to generate JSON-Schema from this ORM.");
459 /* Assign column attributes. */
460 schemaColumnInfo = XT.Schema.columnInfo(schemaTable, columns);
462 /* Add in extension table column properties. */
463 for (var tableName in ext) {
464 schemaColumnInfo = XT.extend(schemaColumnInfo, XT.Schema.columnInfo(tableName, ext[tableName]));
467 for (var i = 0; i < orm.properties.length; i++) {
468 /* Basic properties only. */
469 if (orm.properties[i].attr && orm.properties[i].attr.column) {
470 /* Loop through the returned schemaColumnInfo attributes and add them. */
471 for (var attrname in schemaColumnInfo[orm.properties[i].attr.column]) {
472 if (!ret.properties[orm.properties[i].name]) {
473 /* This can happen if the same column name is errantly referenced in different properties */
474 throw new Error("Cannot get property " + orm.properties[i].name + " on ORM " + orm.nameSpace + "." + orm.type);
476 ret.properties[orm.properties[i].name][attrname] = schemaColumnInfo[orm.properties[i].attr.column][attrname];
481 /* If this ORM has no column properties, we have an empty object, return false. */
482 if (!ret.properties || !Object.keys(ret.properties).length > 0) {
486 /* return the results */
491 * Return an array of requiredAttributes or columns that can not be NULL for an ORM.
493 * @param {Object} An ORM object or a basic one with just orm.nameSpace and orm.type.
496 XT.Schema.getRequiredAttributes = function(orm) {
497 /* Load ORM if this function was called with just orm.nameSpace and orm.type. */
498 orm = orm.properties ? orm : XT.Orm.fetch(orm.nameSpace, orm.type);
500 var schemaTable = orm.table,
502 schemaColumnInfo = {},
505 if (!orm.properties) {
509 /* Loop through the ORM properties and get the columns. */
510 for (var i = 0; i < orm.properties.length; i++) {
513 if (orm.properties[i].attr && orm.properties[i].attr.column) {
514 columns.push(orm.properties[i].attr.column);
516 /* Add required override based off of ORM's property. */
517 if (orm.properties[i].attr.required) {
518 ret.push(orm.properties[i].name);
522 else if (orm.properties[i].toOne) {
523 /* Add required override based off of ORM's property. */
524 if (orm.properties[i].toOne.required) {
525 ret.push(orm.properties[i].name);
528 /* toMany property */
529 else if (orm.properties[i].toMany) {
530 /* Add required override based off of ORM's property. */
531 if (orm.properties[i].toMany.required) {
532 ret.push(orm.properties[i].name);
537 /* You broke it. We should not be here. */
538 throw new Error("Invalid ORM property. Unable to generate requiredAttributes from this ORM.");
542 /* Get required from the returned schemaColumnInfo properties. */
543 schemaColumnInfo = XT.Schema.columnInfo(schemaTable, columns);
545 for (var i = 0; i < orm.properties.length; i++) {
546 /* Basic properties only. */
547 if (orm.properties[i].attr && orm.properties[i].attr.column) {
548 /* Get required from the returned schemaColumnInfo attributes. */
549 if (schemaColumnInfo[orm.properties[i].attr.column].required) {
550 ret.push(orm.properties[i].name);
555 /* If this ORM has no column properties, we have an empty object, return false. */
556 if (!ret.length > 0) {
560 /* return the results */