Schema

From DreamFactory
Jump to: navigation, search
(Layout Format)
 
(23 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
The DreamFactory database schema resource provides a way of managing the database table layout, usable fields, their storage types and requirements.
 
The DreamFactory database schema resource provides a way of managing the database table layout, usable fields, their storage types and requirements.
By "schema", we mean in its traditional SQL database sense, i.e. a set of properties that define the layout of tables and their fields including relationships between them, not the [schema namespaces](http://msdn.microsoft.com/en-us/library/ms176011.aspx) used by Microsoft and other database vendors. However, we have extended this meaning to also encompass the properties that define tables on NoSQL database, i.e. any table key configuration, etc., some of which are database-type dependent.
+
By "schema", we mean in its traditional SQL database sense, i.e. a set of properties that define the layout of tables and their fields including relationships between them, not the [http://msdn.microsoft.com/en-us/library/ms176011.aspx schema namespaces] used by Microsoft and other database vendors. However, we have extended this meaning to also encompass the properties that define tables on NoSQL database, i.e. any table key configuration, etc., some of which are database-type dependent.
  
##<a name="common"></a>Common Parameters & Formatting
+
== Common Parameters & Formatting ==
All database services have a root-level resource called `_schema` that allows API access to the schema knowable by the database connection provisioned. All calls to this resource take the form of...
+
All database services have a root-level resource called '''''_schema''''' that allows API access to the schema knowable by the database connection provisioned. All calls to this resource take the form of...
  
 
<pre>http[s]://<host_name>/api/v2/<service_name>/_schema/[<table_name>/[<field_name>]]</pre>
 
<pre>http[s]://<host_name>/api/v2/<service_name>/_schema/[<table_name>/[<field_name>]]</pre>
  
where `table_name` and `field_name` are optional and control what level of resource the client is acting on.
+
Starting in release 2.4.0, this API has been expanded to include field level manipulation...
All posted and returned data, when not using the `table_name` path parameter must be an array of table-defining property sets, possibly wrapped with a `resource` element based on system configuration. The wrapper label itself can be optional or modified, see configuration options.
+
<pre>http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>/_field/[<field_name>]</pre>
  
<source lang="javascript">
+
and relationship level manipulation...
 +
<pre>http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>/_related/[<relationship_name>]</pre>
 +
 
 +
where '''''table_name''''', '''''field_name''''' and '''''relationship_name''''' are optional and control what level of resource the client is acting on.
 +
All posted and returned data, when not using the '''''table_name''''', '''''field_name''''' or '''''relationship_name''''' path parameter must be an array of resource-defining property sets (tables, fields, or relationships), possibly wrapped with a '''''resource''''' element based on system configuration. The wrapper label itself can be optional or modified, see configuration options.
 +
 
 +
<syntaxhighlight lang="javascript">
 
{
 
{
   "resource": [ <table_info> ]
+
   "resource": [ <resource_info>, <resource_info>, ... ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
In addition to discovering existing schema and allowing provisioning of schema, DreamFactory adds what we call [Schema Extensions](#extensions) to our blended REST API for databases. The REST API supports a hierarchy of resources to help manage the database schema, centered around the table and field levels. For more detail see the [Schema Operations](#operations) section below.
+
In addition to discovering existing schema and allowing provisioning of schema, DreamFactory adds what we call [[#Schema Extensions|extensions]] to our blended REST API for databases. The REST API supports a hierarchy of resources to help manage the database schema, centered around the table and field levels. For more detail see the [[#Schema Operations|operations]] section below.
  
 
There are no additional parameters or headers required for Database Schema API.
 
There are no additional parameters or headers required for Database Schema API.
  
##<a name="extensions"></a>Extensions
+
== Extensions ==
  
While there are plenty of "standard" elements that you can expect to be present in most databases, particularly SQL-based ones, there are also plenty of vendor-specific types, commands, formats, etc. that make it bothersome, and sometimes difficult for client application development. After dealing with that for several years, we decided to try to make things easier by providing the following extensions.  
+
While there are plenty of "standard" elements that you can expect to be present in most databases, particularly SQL-based ones, there are also plenty of vendor-specific types, commands, formats, etc. that make it bothersome, and sometimes difficult for client application development. After dealing with that for several years, we decided to try to make things easier by providing the following extensions.
  * a client-friendly **JSON** input and output [format](#format)
+
* a client-friendly '''JSON''' input and output [[#Layout Format|layout]]
  * consolidated and simplified data [types](#types)
+
* consolidated and simplified data [[#Simplified Types|types]]
  * table, field and relationship [aliasing](#aliasing) to hide database details
+
* table, field and relationship [[#Aliases|aliasing]] to hide database details
  * server-side [validations](#validations) for helping clients control data sent to the database
+
* server-side [[#Validations|validations]] for helping clients control data sent to the database
  * [virtual fields](#virtual_fields) using database functions
+
* [[#Virtual Fields|Virtual fields]] using database functions
  * and [virtual relationships](#virtual_relationships) between non-related datasets
+
* [[#Virtual Relationships|Virtual relationships]] between non-related datasets
  * table, field and relationship [labels](#labels) for client usage
+
* table, field and relationship [[#Labels|labels]] for client usage
  * field-level client settings for aiding clients in form building
+
* field-level client settings for aiding clients in form building
  
 
If you know your favorite database flavored SQL, or NoSQL, like the back of your hand, we will not hinder you (though you might find some of this useful), but if you are like most application developers, the last thing you want to do is go find your database documentation, load up some workbench environment, and/or beg a friend or a perfect stranger for help just to define a table to store your application's data.
 
If you know your favorite database flavored SQL, or NoSQL, like the back of your hand, we will not hinder you (though you might find some of this useful), but if you are like most application developers, the last thing you want to do is go find your database documentation, load up some workbench environment, and/or beg a friend or a perfect stranger for help just to define a table to store your application's data.
  
###<a name="format"></a>Layout Format
+
=== Layout Format ===
  
 
Below is a JSON schema layout we came up with (pooled from various other platforms and environments over the years) to help define your table configuration, including our extensions, with each element defined below. All elements may not be available for all database, table, or field types.
 
Below is a JSON schema layout we came up with (pooled from various other platforms and environments over the years) to help define your table configuration, including our extensions, with each element defined below. All elements may not be available for all database, table, or field types.
  
`<table_info>` =  
+
'''''<table_info>''''' =
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
     "name": "<table_name>",
 
     "name": "<table_name>",
Line 48: Line 54:
 
     "primary_key": "<primary_key_field_names>",
 
     "primary_key": "<primary_key_field_names>",
 
     "name_field": "<naming_field_name>",
 
     "name_field": "<naming_field_name>",
 +
    "native": [ <platform_native_info>, ... ],
 
     "field": [ <field_info>, ... ],
 
     "field": [ <field_info>, ... ],
 
     "related": [ <relationship_info>, ... ],
 
     "related": [ <relationship_info>, ... ],
Line 54: Line 61:
 
     "raw": "<raw_vendor_specific_table_definition>"
 
     "raw": "<raw_vendor_specific_table_definition>"
 
}
 
}
</source>
+
</syntaxhighlight>
  
* `name`: String. Required. The database vendor compatible table name used to designate a table via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed table names. If [schema namespaces](http://msdn.microsoft.com/en-us/library/ms176011.aspx) apply for tables, then the names must use the `<schema_name>.<table_name>` convention commonly used by those vendors.
+
* '''''name''''': String. Required. The database vendor compatible table name used to designate a table via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed table names. If [http://msdn.microsoft.com/en-us/library/ms176011.aspx schema namespaces] apply for tables, then the names must use the '''''<schema_name>.<table_name>''''' convention commonly used by those vendors.
* `alias`: String. Optional. The user defined table name alias to be used to designate a table via the table records (`_table`) resource. The alias will also be used in Role Service Access on the table records (`_table/<alias>`) assignments. The `name` value above is always used to designate a table via the `_schema` resource.
+
* '''''alias''''': String. Optional. The user defined table name alias to be used to designate a table via the table records ('''''_table''''') resource. The alias will also be used in Role Service Access on the table records ('''''_table/<alias>''''') assignments. The '''''name''''' value above is always used to designate a table via the '''''_schema''''' resource.
* `label`: String. Optional. See [labels](#labels) section.
+
* '''''label''''': String. Optional. See [[#Labels|labels]] section.
* `plural`: String. Optional. See [labels](#labels) section.
+
* '''''plural''''': String. Optional. See [[#Labels|labels]] section.
* `description`: String. Optional. See [labels](#labels) section.
+
* '''''description''''': String. Optional. See [[#Labels|labels]] section.
* `field`: Array. Required for SQL DB service types. A wrapper for an array of field definitions. See below for definition specifics. Each field consists of properties defining that field and its usage. A minimum of one field is required to create a table.
+
* '''''native''''': Array. Optional. Any platform-specific information discovered from the table. Information available varies by platform.
* `related`: Array. Optional. A wrapper for an array of relationship definitions. See below for definition specifics. Currently only supported on SQL DB service types. Each relationship consists of properties defining that relationship and its usage. Relationships can be detected automatically by the application or provisioned by the user, see [virtual relationships](#virtual_relationships).
+
* '''''field''''': Array. Required for SQL DB service types. A wrapper for an array of field definitions. See below for definition specifics. Each field consists of properties defining that field and its usage. A minimum of one field is required to create a table.
* `options`: String. Optional. Request Only. These are database-specific create or alter fragments for things not generally supported otherwise when defining the table or fields, such as defining complicated multi-field keys, or the MySQL engine version, i.e. "ENGINE=InnoDB".
+
* '''''related''''': Array. Optional. A wrapper for an array of relationship definitions. See below for definition specifics. Currently only supported on SQL DB service types. Each relationship consists of properties defining that relationship and its usage. Relationships can be detected automatically by the application or provisioned by the user, see [[#Virtual Relationships|virtual relationships]].
* `access`: Integer. Read-Only. If the `include_access` URL parameter is set to true, then this returns the HTTP verb mask of allowed access.
+
* '''''options''''': String. Optional. Request Only. These are database-specific create or alter fragments for things not generally supported otherwise when defining the table or fields, such as defining complicated multi-field keys, or the MySQL engine version, i.e. "ENGINE=InnoDB".
* `raw`: String. Optional. Request-Only. Allows a pure SQL-compatible definition for a table. If this property is defined, no other properties besides the name, alias, label, and description are looked at. The definition must be supported directly by the underlying database as no translations are done, i.e. "(sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)));".
+
* '''''access''''': Integer. Read-Only. If the '''''include_access''''' URL parameter is set to true, then this returns the HTTP verb mask of allowed access.
 +
* '''''raw''''': String. Optional. Request-Only. Allows a pure SQL-compatible definition for a table. If this property is defined, no other properties besides the name, alias, label, and description are looked at. The definition must be supported directly by the underlying database as no translations are done, i.e. "(sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)));".
  
`<field_info>` =
+
 
<source lang="javascript">
+
'''''<field_info>'''''
 +
<syntaxhighlight lang="javascript">
 
{
 
{
 
     "name": "<field_name>",
 
     "name": "<field_name>",
Line 75: Line 84:
 
     "description": "<field_description>",
 
     "description": "<field_description>",
 
     "client_info": {<user_defined_client_information>},
 
     "client_info": {<user_defined_client_information>},
 +
    "native": {<database_platform_specific_information>},
 
     "type": "<simplified_type>",
 
     "type": "<simplified_type>",
 
     "db_type": "<database_specific_type>",
 
     "db_type": "<database_specific_type>",
Line 90: Line 100:
 
     "is_primary_key": [true|false],
 
     "is_primary_key": [true|false],
 
     "is_foreign_key": [true|false],
 
     "is_foreign_key": [true|false],
    "is_virtual_foreign_key": [true|false],
 
    "is_foreign_ref_service": [true|false],
 
    "ref_service": "<foreign_key_referenced_service>",
 
    "ref_service_id": "<foreign_key_referenced_service_id>",
 
 
     "ref_table": "<foreign_key_referenced_table>",
 
     "ref_table": "<foreign_key_referenced_table>",
 
     "ref_fields": "<foreign_key_referenced_fields>",
 
     "ref_fields": "<foreign_key_referenced_fields>",
Line 102: Line 108:
 
         ...
 
         ...
 
     },
 
     },
     "db_function": {
+
     "db_function": [
        "function": <database_function_syntax>,  
+
        {
        "type": "[string,integer,double,float]",
+
            "use": [SELECT|FILTER|INSERT|UPDATE],
         "aggregate": [true,false]
+
            "function": <database_function_syntax>,
     },
+
            "function_type": "database"
     "value": [
+
         }
 +
     ],
 +
     "picklist": [
 
         <value>,
 
         <value>,
 
         ...
 
         ...
 
     ],
 
     ],
 +
    "is_virtual": [true|false],
 +
    "is_aggregate": [true|false],
 
     "raw": "<raw_vendor_specific_field_definition>"
 
     "raw": "<raw_vendor_specific_field_definition>"
 
}
 
}
</source>
+
</syntaxhighlight>
  
* `name`: String. Required. The database vendor compatible field name used to designate a field via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed field names.
+
* '''''name''''': String. Required. The database vendor compatible field name used to designate a field via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed field names.
* `alias`: String. Optional. The user defined field name alias to be used to designate a field via the table records (`_table`) resource, i.e. in the `fields` URL parameter option or posted or retrieved records. The `name` value above is always used to designate a field via the `_schema` resource.
+
* '''''alias''''': String. Optional. The user defined field name alias to be used to designate a field via the table records ('''''_table''''') resource, i.e. in the '''''fields''''' URL parameter option or posted or retrieved records. The '''''name''''' value above is always used to designate a field via the '''''_schema''''' resource.
* `label`: String. Optional. See [labels](#labels) section.
+
* '''''label''''': String. Optional. See [[#Labels|labels]] section.
* `plural`: String. Optional. See [labels](#labels) section.
+
* '''''plural''''': String. Optional. See [[#Labels|labels]] section.
* `description`: String. Optional. See [labels](#labels) section.
+
* '''''description''''': String. Optional. See [[#Labels|labels]] section.
* `client_info`: JSON String. Optional. User-defined JSON object allows client to define persisted settings for building forms, etc.
+
* '''''client_info''''': JSON String. Optional. User-defined JSON object allows client to define persisted settings for building forms, etc.
* `type`: String. Required if `raw` property is not defined. In server responses, the value for this element is closest derived DreamFactory simplified type, if none apply, then it will be set to "string". In requests to the server, the value can be one of the simplified types (i.e. "string”, see the [simplified types](#types) section for more info), or other types supported directly by the underlying database. If using the latter (db-specific types), make sure you include any additional parameters in the type string, such as length, i.e. "nvarchar(128)", as the `length`, `precision`, `scale`, `fixed_length` and `supports_multibyte` parameters are not considered in this case.  
+
* '''''native''''': JSON String. Optional. Database platform-defined JSON object containing platform specific settings discovered about the field, retrieve and sometimes update settings possible.
* `db_type`: String. Read-Only. In server responses, the value for this element is the type as defined directly by the underlying database.
+
* '''''type''''': String. Required if '''''raw''''' property is not defined. In server responses, the value for this element is closest derived DreamFactory simplified type, if none apply, then it will be set to "string". In requests to the server, the value can be one of the simplified types (i.e. "string”, see the [[#Simplified Types|types]] section for more info), or other types supported directly by the underlying database. If using the latter (db-specific types), make sure you include any additional parameters in the type string, such as length, i.e. "nvarchar(128)", as the '''''length''''', '''''precision''''', '''''scale''''', '''''fixed_length''''' and '''''supports_multibyte''''' parameters are not considered in this case.
* `fixed_length`: Boolean. Optional for `string` type. Set to true to represent string types as fixed length (i.e. ‘char’), false for variable length (i.e. ‘varchar’) which is the default.
+
* '''''db_type''''': String. Read-Only. In server responses, the value for this element is the type as defined directly by the underlying database.
* `supports_multibyte`: Boolean. Optional for `string` type. Set to true to represent string types that support multibyte characters, i.e. "national" in most database vernacular, (i.e. "utf8"); use false for single byte character support only (i.e. ‘ascii’) which is the default.
+
* '''''fixed_length''''': Boolean. Optional for '''''string''''' type. Set to true to represent string types as fixed length (i.e. ‘char’), false for variable length (i.e. ‘varchar’) which is the default.
* `length`: Integer. Optional. Used to define the max length of strings and number fields. For strings, if length is not defined, the default is 255.
+
* '''''supports_multibyte''''': Boolean. Optional for '''''string''''' type. Set to true to represent string types that support multibyte characters, i.e. "national" in most database vernacular, (i.e. "utf8"); use false for single byte character support only (i.e. ‘ascii’) which is the default.
* `precision`: Integer. Optional. Used by the "decimal" and "money", and in some cases the "double" and "float", types. It represents the total length of the number allowed minus the ‘.’, and can be set either by the `length` property or the `precision` property, `length` takes precedence.
+
* '''''length''''': Integer. Optional. Used to define the max length of strings and number fields. For strings, if length is not defined, the default is 255. '''Note:''' '''''length''''' and '''''size''''' in some cases can be used interchangeably.
* `scale`: Integer. Optional. Used by the "decimal" and "money" types, where it defines the number of decimal places to the right of the decimal. Only applies if the `length` or `precision` property is given. If scale is not given, the default is 0, i.e. no decimal places.
+
* '''''precision''''': Integer. Optional. Used by the "decimal" and "money", and in some cases the "double" and "float", types. It represents the total length of the number allowed minus the ‘.’, and can be set either by the '''''length''''' property or the '''''precision''''' property, '''''length''''' takes precedence.
* `default`: Mixed. Optional. Use to define the SQL DEFAULT parameter of a field, supported values are dependent on the "type" designation, but may include null, strings, numbers, and SQL expressions, i.e. "NOW()" for datetime, etc.
+
* '''''scale''''': Integer. Optional. Used by the "decimal" and "money" types, where it defines the number of decimal places to the right of the decimal. Only applies if the '''''length''''' or '''''precision''''' property is given. If scale is not given, the default is 0, i.e. no decimal places.
* `allow_null`: Boolean. Optional. Defines whether or not the NULL value is allowed to be set for the field, ie. false = "NOT NULL". If not defined, the default is allow NULL (true).
+
* '''''default''''': Mixed. Optional. Use to define the SQL DEFAULT parameter of a field, supported values are dependent on the "type" designation, but may include null, strings, numbers, and SQL expressions, i.e. "NOW()" for datetime, etc.
* `auto_increment`: Boolean. Optional. Set to true to allow auto-incrementing of an integer or primary key. If not defined, the default is false.
+
* '''''required''''': Boolean. Read-Only. Defines whether or not a value is required for this field when creating a record, it is set to the equivalent of if required = !(allowNull || defaultValue || autoIncrement).
* `is_primary_key`: Boolean. Optional. Set to true to define non-trivial primary keys, use once per table definition. If not defined, the default is false, except for the "id” type, see below.
+
* '''''allow_null''''': Boolean. Optional. Defines whether or not the NULL value is allowed to be set for the field, ie. false = "NOT NULL". If not defined, the default is allow NULL (true).
* `is_foreign_key`: Boolean. Optional. Set to true to define foreign keys that are not stored as integers or point to multiple primary keys. If not defined, the default is false, except for the "reference" type, see below.
+
* '''''auto_increment''''': Boolean. Optional. Set to true to allow auto-incrementing of an integer or primary key. If not defined, the default is false.
* `is_virtual_foreign_key`: Boolean. Optional. Set to true to define arbitrary foreign keys that can't be stored as constraints in the database, see [virtual relationships](#virtual_relationships).
+
* '''''is_primary_key''''': Boolean. Optional. Set to true to define non-trivial primary keys, use once per table definition. If not defined, the default is false, except for the "id” type, see below.
* `ref_table`: String. Required when `type` is "reference" or when `is_foreign_key` is true. Defines the table name that a reference field points to.
+
* '''''is_foreign_key''''': Boolean. Optional. Set to true to define foreign keys that are not stored as integers or point to multiple primary keys. If not defined, the default is false, except for the "reference" type, see below.
* `ref_fields`: String. Required when `type` is "reference" or when `is_foreign_key` is true. Defines the field(s) of the referenced table defined by `ref_table` property that the field is referencing. If multiple fields are part of the key, separate the fields by a comma.
+
* '''''ref_table''''': String. Required when '''''type''''' is "reference" or when '''''is_foreign_key''''' is true. Defines the table name that a reference field points to.
* `ref_on_update`: String. Optional when `type` is "reference" or when `is_foreign_key` is true. defines the operation to take when the referenced record is updated. May not be supported by all database types, see `ref_on_delete`.
+
* '''''ref_fields''''': String. Required when '''''type''''' is "reference" or when '''''is_foreign_key''''' is true. Defines the field(s) of the referenced table defined by '''''ref_table''''' property that the field is referencing. If multiple fields are part of the key, separate the fields by a comma.
* `ref_on_delete`: String. Optional when `type` is "reference" or when `is_foreign_key` is true. Defines the operation to take when the referenced record is deleted. Options are typically "RESTRICT","CASCADE", "SET NULL", "NO ACTION", but may be database dependent. Default is database dependent.
+
* '''''ref_on_update''''': String. Optional when '''''type''''' is "reference" or when '''''is_foreign_key''''' is true. defines the operation to take when the referenced record is updated. May not be supported by all database types, see '''''ref_on_delete'''''.
* `is_unique`: Boolean. Optional. Set to true to require that each row has a unique value for this field. This is typically implemented as a unique index on the table.
+
* '''''ref_on_delete''''': String. Optional when '''''type''''' is "reference" or when '''''is_foreign_key''''' is true. Defines the operation to take when the referenced record is deleted. Options are typically "RESTRICT","CASCADE", "SET NULL", "NO ACTION", but may be database dependent. Default is database dependent.
* `is_index`: Boolean. Optional. Set to true to set the field as a table index to speed up common searches that use this field. Typically this isn't supported for large fields, like "text".
+
* '''''is_unique''''': Boolean. Optional. Set to true to require that each row has a unique value for this field. This is typically implemented as a unique index on the table.
* `values`: Array of strings or integers. Optional. Designates values allowed for the field defined, type of the field must match the values given, i.e. string for an array of string values. Use the "picklist" and "multi_picklist" validations to restrict changes to this field's value via the API to only these values, otherwise these serve as suggestions to the client.
+
* '''''is_index''''': Boolean. Optional. Set to true to set the field as a table index to speed up common searches that use this field. Typically this isn't supported for large fields, like "text".
* `validation`: Array of settings. Optional. See [validations](#validations) for more info.  
+
* '''''picklist''''': Array of strings or integers. Optional. Designates values allowed for the field defined, type of the field must match the values given, i.e. string for an array of string values. Use the "picklist" and "multi_picklist" validations to restrict changes to this field's value via the API to only these values, otherwise these serve as suggestions to the client.
* `db_function`: Object. Optional. See [database functions](#db_functions) for more info.  
+
* '''''validation''''': Array of settings. Optional. See [[#Validations|validations]] for more info.
* `raw`: String. Optional. Request-Only. Allows a pure SQL-compatible definition for a field. If this property is defined, no other properties besides the name, alias, label, description, and client_info are looked at. SQL types in the definition must be supported directly by the underlying database as no translations are done, i.e. "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY".
+
* '''''db_function''''': Object. Optional. See [[#DB Functions|database functions]] for more info.
 +
* '''''is_virtual''''': Boolean. Optional. Set to true to designate a new field as virtual, see [[#Virtual Fields|virtual fields]]. If not defined, the default is false.
 +
* '''''is_aggregate''''': Boolean. Optional. Applicable only is '''''is_virtual''''' is true. Set to true to designate that this virtual field uses an aggregate function. Aggregate fields are not included in GET requests by default. If not defined, the default is false.
 +
* '''''raw''''': String. Optional. Request-Only. Allows a pure SQL-compatible definition for a field. If this property is defined, no other properties besides the name, alias, label, description, and client_info are looked at. SQL types in the definition must be supported directly by the underlying database as no translations are done, i.e. "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY".
  
`<relationship_info>` =
+
 
<source lang="javascript">
+
'''<relationship_info>'''
 +
<syntaxhighlight lang="javascript">
 
{
 
{
 
     "name": "<relationship_name>",
 
     "name": "<relationship_name>",
 +
    "alias": "<relationship_alias>",
 
     "label": "<user_defined_label>",
 
     "label": "<user_defined_label>",
 
     "description": "<user_defined_description>",
 
     "description": "<user_defined_description>",
 
     "always_fetch": [true|false],
 
     "always_fetch": [true|false],
    "flatten": [true|false],
 
    "flatten_drop_prefix": [true|false],
 
 
     "type": [belongs_to|has_many|many_many],
 
     "type": [belongs_to|has_many|many_many],
 
     "field": "<field_of_this_table>",
 
     "field": "<field_of_this_table>",
 
     "is_virtual": [true|false],
 
     "is_virtual": [true|false],
    "is_foreign_service": [true|false],
 
    "ref_service": <service_name_of_foreign_table>,
 
 
     "ref_service_id": <service_id_of_foreign_table>,
 
     "ref_service_id": <service_id_of_foreign_table>,
 
     "ref_table": "<foreign_table>",
 
     "ref_table": "<foreign_table>",
Line 164: Line 175:
 
     "ref_on_update": "[RESTRICT|CASCADE|SET NULL|NO ACTION]",
 
     "ref_on_update": "[RESTRICT|CASCADE|SET NULL|NO ACTION]",
 
     "ref_on_delete": "[RESTRICT|CASCADE|SET NULL|NO ACTION]",
 
     "ref_on_delete": "[RESTRICT|CASCADE|SET NULL|NO ACTION]",
    "is_foreign_junction_service": [true|false],
 
    "junction_service": <foreign_service_name>,
 
 
     "junction_service_id": <foreign_service_id>,
 
     "junction_service_id": <foreign_service_id>,
 
     "junction_table": "<foreign_table>",
 
     "junction_table": "<foreign_table>",
Line 171: Line 180:
 
     "junction_ref_field": "<field_of_foreign_table>"
 
     "junction_ref_field": "<field_of_foreign_table>"
 
}
 
}
</source>
+
</syntaxhighlight>
 +
 
 +
* '''''name''''': String. Required. The database-type compatible table or field name used to designate a table or field via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed table or field names. If [http://msdn.microsoft.com/en-us/library/ms176011.aspx schema namespaces] are used for tables, then the names must use the '''''<schema_name>.<table_name>''''' convention commonly used by those vendors.
 +
* '''''alias''''': String. Optional. The user defined relation name alias to be used to designate a relation via the table records ('''''_table''''') resource, i.e. in the '''''related''''' URL parameter option or posted or retrieved records. The '''''name''''' value above is always used to designate a relation via the '''''_schema''''' resource.
 +
* '''''label''''': String. Optional. See [[#Labels|labels]] section.
 +
* '''''native''''': JSON String. Optional. Database platform-defined JSON object containing platform specific settings discovered about the relationship.
 +
* '''''description''''': String. Optional. See [[#Labels|labels]] section.
 +
* '''''type''''': String. Required. The type of relationship represented.
 +
* '''''is_virtual''''': Boolean. Optional. Set to true if the relationship is due to arbitrary foreign keys, see [[#Virtual Relationships|virtual relationships]].
 +
* '''''ref_service_id''''': Integer. This defines the service by its id value that a reference points to.
 +
* '''''ref_table''''': String. Defines the table name that a reference field points to.
 +
* '''''ref_fields''''': String. Defines the field(s) of the referenced table defined by '''''ref_table''''' property that the field is referencing. If multiple fields are part of the key, separate the fields by a comma.
 +
* '''''ref_on_update''''': String. Optional when '''''type''''' is "reference" or when '''''is_foreign_key''''' is true. defines the operation to take when the referenced record is updated. May not be supported by all database types, see '''''ref_on_delete'''''.
 +
* '''''ref_on_delete''''': String. Optional when '''''type''''' is "reference" or when '''''is_foreign_key''''' is true. Defines the operation to take when the referenced record is deleted. Options are typically "RESTRICT","CASCADE", "SET NULL", "NO ACTION", but may be database dependent. Default is database dependent.
 +
* '''''junction_service_id''''': Integer. When '''''type''''' is '''many_many''', this defines the service where the junction table exists.
 +
* '''''junction_table''''': String. Defines the name of the junction table.
 +
* '''''junction_field''''': String. Defines the field of the junction table that refers to primary table.
 +
* '''''junction_ref_field''''': String. Defines the field of the junction table that refers to the referenced table defined by '''''ref_table'''''
  
* `name`: String. Required. The database-type compatible table or field name used to designate a table or field via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed table or field names. If [schema namespaces](http://msdn.microsoft.com/en-us/library/ms176011.aspx) are used for tables, then the names must use the `<schema_name>.<table_name>` convention commonly used by those vendors.
+
=== Aliases ===
* `label`: String. Optional. See [labels](#labels) section.
+
Whether it is for security reasons or because you just don't care for the names in the database, DreamFactory allows you to provision and use aliases for the table names, field names and relationship names. The aliases must be used when accessing any of the three elements via the Table (_table) resource. For example if I provision an alias for a table named 'todo' to 'task', when I access the table through the Schema (_schema) resource, the client must use /api/v2/_schema/todo, however to query records, the client must use /api/v2/_table/task. Likewise for any field names used in records or query parameters, like '''''fields''''' or '''''filter'''''.
* `plural`: String. Optional. See [labels](#labels) section.
+
* `description`: String. Optional. See [labels](#labels) section.
+
* `is_virtual`: Boolean. Optional. Set to true if the relationship is due to arbitrary foreign keys, see [virtual relationships](#virtual_relationships).
+
* `is_foreign_service`: Boolean. Optional. Set to true if the arbitrary foreign keys point to a foreign DreamFactory service, i.e. another DB service, see [virtual relationships](#virtual_relationships).
+
* `ref_service`: String. When `is_foreign_service` is true, this defines the service name that a reference points to.
+
* `ref_table`: String. Defines the table name that a reference field points to.
+
* `ref_fields`: String. Defines the field(s) of the referenced table defined by `ref_table` property that the field is referencing. If multiple fields are part of the key, separate the fields by a comma.
+
* `ref_on_update`: String. Optional when `type` is "reference" or when `is_foreign_key` is true. defines the operation to take when the referenced record is updated. May not be supported by all database types, see `ref_on_delete`.
+
* `ref_on_delete`: String. Optional when `type` is "reference" or when `is_foreign_key` is true. Defines the operation to take when the referenced record is deleted. Options are typically "RESTRICT","CASCADE", "SET NULL", "NO ACTION", but may be database dependent. Default is database dependent.
+
* `is_foreign_junction_service`: Boolean. Optional. Set to true if the junction or pivot table is on a foreign DreamFactory service, i.e. another DB service, see [virtual relationships](#virtual_relationships).
+
* `junction_service`: String. When `is_foreign_junction_service` is true, this defines the service name where the junction table exists.
+
* `junction_table`: String. Defines the name of the junction table.
+
* `junction_field`: String. Defines the field of the junction table that refers to primary table.
+
* `junction_ref_field`: String. Defines the field of the junction table that refers to the referenced table defined by `ref_table`
+
  
###<a name="labels"></a>Labels
+
=== Labels ===
 
Labels in singular and plural form, as well as, a description value are tracked in a system table and are relayed to the client along with the schema. They are not used in API calls. They can contain UTF-8 characters, including spaces, but no other whitespace characters for label and plural. These are useful in client applications so that form labels don't have to be hard coded, see our admin applications, like the Data Manager, for example. Labels are available for table,field and relationship names. Plural labels are only available for table names. Labels can be sent and saved on the server along with other properties when creating or updating table schema. When none are specified, labels are auto-generated based on commonly used techniques, i.e. -y becomes -ies, etc.
 
Labels in singular and plural form, as well as, a description value are tracked in a system table and are relayed to the client along with the schema. They are not used in API calls. They can contain UTF-8 characters, including spaces, but no other whitespace characters for label and plural. These are useful in client applications so that form labels don't have to be hard coded, see our admin applications, like the Data Manager, for example. Labels are available for table,field and relationship names. Plural labels are only available for table names. Labels can be sent and saved on the server along with other properties when creating or updating table schema. When none are specified, labels are auto-generated based on commonly used techniques, i.e. -y becomes -ies, etc.
  
###<a name="types"></a>Simplified Types
+
=== Simplified Types ===
 
The supported simple types are defined as follows.
 
The supported simple types are defined as follows.
  
  * **id** or **pk**: defines a typical table identifier, translates to ```"INT NOT NULL AUTO_INCREMENT PRIMARY KEY"```. This type requires no other properties to define the field. It presumes a `type` of int with `allow_null` set to false, the `auto_increment` and `is_primary_key` are set to true. It can only be used once in a table definition.
+
* '''id''' or '''pk''': defines a typical table identifier, translates to <pre>"INT NOT NULL AUTO_INCREMENT PRIMARY KEY"</pre>. This type requires no other properties to define the field. It presumes a '''''type''''' of int with '''''allow_null''''' set to false, the '''''auto_increment''''' and '''''is_primary_key''''' are set to true. It can only be used once in a table definition.
  
  * **reference**: defines a typical foreign key, presumes the `type` of int and requires the `ref_table` and `ref_fields` properties to be defined as well. Optional defining properties are `ref_on_delete`, `ref_on_update`, `allow_null` and `default`. Similar to SQL fragment ```"FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE”```.
+
* '''reference''': defines a typical foreign key, presumes the '''''type''''' of int and requires the '''''ref_table''''' and '''''ref_fields''''' properties to be defined as well. Optional defining properties are '''''ref_on_delete''''', '''''ref_on_update''''', '''''allow_null''''' and '''''default'''''. Similar to SQL fragment <pre>"FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE”</pre>.
  
  * **string**: defines a string field (i.e. varchar or char), length defaults to 255, but can be set using the `length` property. Set the `fixed_length` property to true for fixed length (i.e. char) behavior. Set `supports_multibyte` for multi-byte, national (i.e. nvarchar) behavior. Other optional properties are `allow_null`, `default`, `values`, and `validation`.
+
* '''string''': defines a string field (i.e. varchar or char), length defaults to 255, but can be set using the '''''length''''' property. Set the '''''fixed_length''''' property to true for fixed length (i.e. char) behavior. Set '''''supports_multibyte''''' for multi-byte, national (i.e. nvarchar) behavior. Other optional properties are '''''allow_null''''', '''''default''''', '''''values''''', and '''''validation'''''.
  
  * **text**: defines a large string field (i.e. MySQL's text or MSSQL's varchar[max]), defaults to the largest length string allowed by the underlying database. Optional properties are `allow_null` and `default`.
+
* '''text''': defines a large string field (i.e. MySQL's text or MSSQL's varchar[max]), defaults to the largest length string allowed by the underlying database. Optional properties are '''''allow_null''''' and '''''default'''''.
  
  * **binary**: defines a binary string field (i.e. varbinary or binary), defaults to a length of 255, but can be set using the `length` property. Set the `fixed_length` property to true for fixed length (i.e. binary) behavior. Optional properties are `allow_null` and `default`.
+
* '''binary''': defines a binary string field (i.e. varbinary or binary), defaults to a length of 255, but can be set using the '''''length''''' property. Set the '''''fixed_length''''' property to true for fixed length (i.e. binary) behavior. Optional properties are '''''allow_null''''' and '''''default'''''.
  
  * **boolean**: defines a boolean field, which may be represented by int of length 1, using 0 and 1 values, if a true and false boolean type is not supported by the underlying database. Optional properties are `allow_null` and `default`.
+
* '''boolean''': defines a boolean field, which may be represented by int of length 1, using 0 and 1 values, if a true and false boolean type is not supported by the underlying database. Optional properties are '''''allow_null''''' and '''''default'''''.
  
  * **integer**: defines an integer field. Use `length` to set the displayable length of the integer, i.e. int(11) in MySQL. Optional properties are `allow_null`, `default`, `values`, and `validation`.
+
* '''integer''': defines an integer field. Use '''''length''''' to set the displayable length of the integer, i.e. int(11) in MySQL. Optional properties are '''''allow_null''''', '''''default''''', '''''values''''', and '''''validation'''''.
  
  * **float**: defines a standard single-precision float field. Some databases (i.e. MySQL) allow `length` or `precision` settings. Optional properties are `allow_null` and `default`.
+
* '''float''': defines a standard single-precision float field. Some databases (i.e. MySQL) allow '''''length''''' or '''''precision''''' settings. Optional properties are '''''allow_null''''' and '''''default'''''.
  
  * **double**: defines a standard double-precision float field. Some databases (i.e. MySQL) allow `length` or `precision` settings. Optional properties are `allow_null` and `default`.
+
* '''double''': defines a standard double-precision float field. Some databases (i.e. MySQL) allow '''''length''''' or '''''precision''''' settings. Optional properties are '''''allow_null''''' and '''''default'''''.
  
  * **decimal**: defines a standard decimal field. Use `scale` to set the number of desired decimal places to the right of the decimal point. Use `length` or `precision` to set the total number of digit positions. Optional properties are `allow_null` and `default`.
+
* '''decimal''': defines a standard decimal field. Use '''''scale''''' to set the number of desired decimal places to the right of the decimal point. Use '''''length''''' or '''''precision''''' to set the total number of digit positions. Optional properties are '''''allow_null''''' and '''''default'''''.
  
  * **datetime**: a datetime field. Optional properties are `allow_null`, `default`, `values`, and `validation`.
+
* '''datetime''': a datetime field. Optional properties are '''''allow_null''''', '''''default''''', '''''values''''', and '''''validation'''''.
  
  * **date**: a date field. Optional properties are `allow_null`, `default`, `values`, and `validation`.
+
* '''date''': a date field. Optional properties are '''''allow_null''''', '''''default''''', '''''values''''', and '''''validation'''''.
  
  * **time**: a time field. Optional properties are `allow_null`, `default`, `values`, and `validation`.
+
* '''time''': a time field. Optional properties are '''''allow_null''''', '''''default''''', '''''values''''', and '''''validation'''''.
  
  * **timestamp**: a date and time stamp with timezone awareness where applicable, i.e. MySQL’s `timestamp not null default 0` or MSSQL’s `datetimeoffset`.
+
* '''timestamp''': a date and time stamp with timezone awareness where applicable, i.e. MySQL’s '''''timestamp not null default 0''''' or MSSQL’s '''''datetimeoffset'''''.
  
  * **timestamp_on_create**: a `timestamp` as documented above that will be automatically set on record creation and not updated again unless set by the client via the API. See "api_read_only" validation for keeping this from being set by API.
+
* '''timestamp_on_create''': a '''''timestamp''''' as documented above that will be automatically set on record creation and not updated again unless set by the client via the API. See "api_read_only" validation for keeping this from being set by API.
  
  * **timestamp_on_update**: a `timestamp` as documented above that will be automatically set on record creation and again on every update. See "api_read_only" validation for keeping this from being set by API.
+
* '''timestamp_on_update''': a '''''timestamp''''' as documented above that will be automatically set on record creation and again on every update. See "api_read_only" validation for keeping this from being set by API.
  
  * **user_id**: a reference to the current user, assuming a valid session has been established. On the native database, this is implemented as a reference field pointing to the system's user table, on other databases, it is implemented as an integer. Optional properties are `allow_null` and `default`.
+
* '''user_id''': a reference to the current user, assuming a valid session has been established. On the native database, this is implemented as a reference field pointing to the system's user table, on other databases, it is implemented as an integer. Optional properties are '''''allow_null''''' and '''''default'''''.
  
  * **user_id_on_create**: a `user_id` as documented above that will be automatically set on record creation and not updated again unless set by the client via the API. See "api_read_only" validation for keeping this from being set by API.
+
* '''user_id_on_create''': a '''''user_id''''' as documented above that will be automatically set on record creation and not updated again unless set by the client via the API. See "api_read_only" validation for keeping this from being set by API.
  
  * **user_id_on_update**: a `user_id` as documented above that will be automatically set on record creation and again on every update. See "api_read_only" validation for keeping this from being set by API.
+
* '''user_id_on_update''': a '''''user_id''''' as documented above that will be automatically set on record creation and again on every update. See "api_read_only" validation for keeping this from being set by API.
  
  * **virtual**: defines a field that is not really in the database itself, but is represented by a database function call. The `db_function` parameter must be defined for this field type to be used.
 
  
 
+
=== Validations ===
###<a name="validations"></a>Validations
+
 
Some server-side validations can be defined for each field by setting this property to one or more of the following options in the given format below. Note that additional validation will result in additional processing which may slow response times. Validations are configured in the following format...
 
Some server-side validations can be defined for each field by setting this property to one or more of the following options in the given format below. Note that additional validation will result in additional processing which may slow response times. Validations are configured in the following format...
  
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "validation":
 
   "validation":
Line 253: Line 263:
 
     }
 
     }
 
}
 
}
</source>
+
</syntaxhighlight>
  
where `on_fail` is a configuration option that takes either the value `ignore_field`, dictating if the field should be ignored if it does not pass validation, or a specified error message to overwrite the generic exception thrown. If this configuration is missing, a generic exception will be thrown stating the validation has failed.
+
where '''''on_fail''''' is a configuration option that takes either the value '''''ignore_field''''', dictating if the field should be ignored if it does not pass validation, or a specified error message to overwrite the generic exception thrown. If this configuration is missing, a generic exception will be thrown stating the validation has failed.
  
 
Other validation configuration options are documented below for each validation type.
 
Other validation configuration options are documented below for each validation type.
Line 261: Line 271:
 
Possible validations settings are as follows...
 
Possible validations settings are as follows...
  
  * **picklist** - Supported for string type only. It requires the field value to be set to one of the values listed in the `values` property. Any error is returned if the `values` property is empty or does not contain the value being set. Values are checked only on create and update record API calls; data integrity is kept for existing values even when the picklist value list is modified. Behaves similar to MySQL "enum" type.
+
* '''picklist''' - Supported for string type only. It requires the field value to be set to one of the values listed in the '''''values''''' property. Any error is returned if the '''''values''''' property is empty or does not contain the value being set. Values are checked only on create and update record API calls; data integrity is kept for existing values even when the picklist value list is modified. Behaves similar to MySQL "enum" type.
  
  * **multi_picklist** - similar to picklist but allows multiple values to be selected and stored. Behaves similar to MySQL "set" type.
+
* '''multi_picklist''' - similar to picklist but allows multiple values to be selected and stored. Behaves similar to MySQL "set" type.
    * `delimiter` - Optional. Defaults to comma, ",". Parameter allows setting the delimiter character used to separate each value being set from what is being stored in the field value.
+
** '''''delimiter''''' - Optional. Defaults to comma, ",". Parameter allows setting the delimiter character used to separate each value being set from what is being stored in the field value.
    * `min` - Optional. Integer. Defaults to 1. Number of minimum required selections from the list.
+
** '''''min''''' - Optional. Integer. Defaults to 1. Number of minimum required selections from the list.
    * `max` - Optional. Integer. If set, it designates the maximum required selections from the list.
+
** '''''max''''' - Optional. Integer. If set, it designates the maximum required selections from the list.
  
  * **api_read_only** - sets this field as read only through the API. Use "default” property to set values, i.e. useful for creating timestamps, etc. Supported for all types.
+
* '''api_read_only''' - sets this field as read only through the API. Use "default” property to set values, i.e. useful for creating timestamps, etc. Supported for all types.
  
  * **create_only** - sets this field to only allow values to be set on record creation. Supported for all types.
+
* '''create_only''' - sets this field to only allow values to be set on record creation. Supported for all types.
  
  * **not_null** - validates that the field value to be set is not null. Supported for all types,
+
* '''not_null''' - validates that the field value to be set is not null. Supported for all types,
  
  * **not_empty** - validates that the field value to be set is not empty string. Supported for string and text types. Null is not checked.
+
* '''not_empty''' - validates that the field value to be set is not empty string. Supported for string and text types. Null is not checked.
  
  * **not_zero** - validates that the field value to be set is not zero (0). Supported for integers, decimals and floats. Null is not checked.
+
* '''not_zero''' - validates that the field value to be set is not zero (0). Supported for integers, decimals and floats. Null is not checked.
  
  * **int** - validates that the value to be set is between the min and max integer values designated. Supported for integers.
+
* '''int''' - validates that the value to be set is between the min and max integer values designated. Supported for integers.
    * `range` - Optional. Defines the min and max range allowed for the integer.
+
* '''''range''''' - Optional. Defines the min and max range allowed for the integer.
      * `min` - Optional. If set, it designates the minimum value allowed.
+
** '''''min''''' - Optional. If set, it designates the minimum value allowed.
      * `max` - Optional. If set, it designates the maximum value allowed.
+
** '''''max''''' - Optional. If set, it designates the maximum value allowed.
    * `formats` - Optional. Selects other allowable formats for the integers, "hex" and "octal" currently available.
+
** '''''formats''''' - Optional. Selects other allowable formats for the integers, "hex" and "octal" currently available.
  
  * **float** - validates that the value to be set is a valid float.
+
* '''float''' - validates that the value to be set is a valid float.
    * `decimal` - Optional. Defaults to '.'. Designates the character used as the decimal separator, which is sometimes ','.
+
** '''''decimal''''' - Optional. Defaults to '.'. Designates the character used as the decimal separator, which is sometimes ','.
  
  * **boolean** - validates that the value to be set is one of the generally accepted values for true or false, i.e.  true = 1, "1", "true", "on" and "yes". false = 0, "0", "false", "off", "no", and "".
+
* '''boolean''' - validates that the value to be set is one of the generally accepted values for true or false, i.e.  true = 1, "1", "true", "on" and "yes". false = 0, "0", "false", "off", "no", and "".
  
  * **email** - validates that this field is an email, i.e. "name@company.com”. Supported for string type only. See FILTER_VALIDATE_EMAIL.
+
* '''email''' - validates that this field is an email, i.e. "name@company.com”. Supported for string type only. See FILTER_VALIDATE_EMAIL.
  
  * **url** - validates that this field is a url, i.e. starts with "http(s)://”. Supported for string type only.
+
* '''url''' - validates that this field is a url, i.e. starts with "http(s)://”. Supported for string type only.
    * `sections` - Optional. Array of Strings. Designates required sections of the URL, "path" and "query" currently available.
+
** '''''sections''''' - Optional. Array of Strings. Designates required sections of the URL, "path" and "query" currently available.
  
  * **match** - for strings matching a regular expression designated by `regex`. Supported for string type only.
+
* '''match''' - for strings matching a regular expression designated by '''''regex'''''. Supported for string type only.
    * `regex` - Required. String. Exception thrown if not defined or empty. Due to special characters in regular expressions, the `regex` value must be encoded as base64 before being provisioned.
+
** '''''regex''''' - Required. String. Exception thrown if not defined or empty. Due to special characters in regular expressions, the '''''regex''''' value must be encoded as base64 before being provisioned.
  
###<a name="virtual_fields"></a>Virtual Fields
+
=== Virtual Fields ===
A virtual field is a field defined by using the **virtual** simplified field type that doesn't actually exist in the database but is used in `fields` (i.e. SELECT clause) and `filter` (i.e. WHERE clause) URL parameters. Fields of type **virtual** MUST define the `db_function` parameter below.
+
A virtual field is a field defined by using the '''is_virtual''' field schema designation that doesn't actually exist in the database but is used in '''''fields''''' (i.e. SELECT clause) and '''''filter''''' (i.e. WHERE clause) URL parameters. Virtual fields MUST define the '''''db_function''''' parameter below. Virtual fields included in POST, PUT, or PATCH request are ignored.
  
###<a name="db_function"></a>Database Functions
+
=== Database Functions ===
A database function can be defined for a field, virtual or not, by setting this property to any function supported by the database, i.e aggregate functions like MAX() for SELECT statements. If the db_function is applied to an existing database field, the actual value of the field in the database is not returned, but the function's value is returned instead. For example, if you wanted a certain string field, say "title" to be returned upper-case, regardless of its actual database value, we could add a db_function with `function` set to UPPER(title). Everywhere "title" is used the value will be uppercased.
+
A database function can be defined for a field, virtual or real, by setting this property to any function supported by the database, i.e aggregate functions like MAX() for SELECT statements. If the db_function is applied to an existing database field, the actual value of the field in the database is not returned, but the function's value is returned instead. For example, if you wanted a certain string field, say "title" to be returned upper-case, regardless of its actual database value, we could add a db_function with '''''function''''' set to UPPER(title). Everywhere "title" is used the value will be uppercased.
  
 +
As of 2.4.2, you can now designate a database function to be used for each of the following specific API uses...
 +
* SELECT - Used for GET methods and all internal use of '''fields''' values, i.e. behavior of the SQL SELECT clause.
 +
* FILTER - Used for all '''filter''' uses where the field part of the filter is replaced with the designated function, i.e. behavior of the SQL WHERE clause.
 +
* INSERT - Used for all insert clauses for the field, where the value is replaced with the function call, i.e. behavior of the SQL INSERT request.
 +
* UPDATE - Used for all set clauses for the field, where the value is replaced with the function call, i.e. behavior of the SQL UPDATE request.
 
Database functions are configured in the following format...
 
Database functions are configured in the following format...
  
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
  "db_function":
+
    "db_function": [
    {
+
        {
        "function": "<database_specific_function_call>"
+
            "use": [
        "type": "[string,integer,double,float]",
+
                "SELECT"
         "aggregate": [true,false]
+
            ],
    }
+
            "function": "TO_BASE64(bdata)",
 +
            "function_type": "database"
 +
        },
 +
         {
 +
            "use": [
 +
                "INSERT",
 +
                "UPDATE"
 +
            ],
 +
            "function": "FROM_BASE64({value})",
 +
            "function_type": "database"
 +
        }
 +
    ],
 
}
 
}
</source>
+
</syntaxhighlight>
 +
 
 +
where '''''use''''' is an array of the use cases where this function is to be used, and '''''function''''' is the full raw syntax of the supported function call. ''''''function_type''''' currently only supports "database", meaning the database itself is the one executing the function.
 +
 
 +
'''Note:''' For SELECT and FILTER functions, use the field name, not alias, where the field is supposed to be used. For INSERT and UPDATE functions, use the lookup syntax "{value}" which will perform the function on the value of the field passed in from the payload.
  
 
Database functions are database vendor dependent, but some common functions are listed below...
 
Database functions are database vendor dependent, but some common functions are listed below...
  
  * Aggregate Functions such as AVG, COUNT, MAX, MIN, SUM, etc. Set `aggregate` to true for these, so that API usage of `fields=*` doesn't attempt to return these fields.
+
* Aggregate Functions such as AVG, COUNT, MAX, MIN, SUM, etc. Set '''''is_aggregate''''' to true for these, so that API usage of '''''fields=*''''' doesn't attempt to return these fields.
    * ex. Max(salary) - Returns the maximum value of an existing column in the table named "salary", numerically for numbers or alphabetically for strings.
+
** ex. Max(salary) - Returns the maximum value of an existing column in the table named "salary", numerically for numbers or alphabetically for strings.
  * Scalar Functions such as UCASE, LCASE, MID, LEN
+
* Scalar Functions such as UCASE, LCASE, MID, LEN
    * ex. LEN(address) - Returns the length of an existing value of the column in the table.
+
** ex. LEN(address) - Returns the length of an existing value of the column in the table.
  * Vendor-specific Functions such as SQL Server's CONVERT() or <field_name>.ToString().
+
* Vendor-specific Functions such as SQL Server's CONVERT() or <field_name>.ToString().
  * User-defined Functions if supported by the database vendor.
+
* User-defined Functions if supported by the database vendor.
  
###<a name="virtual_relationships"></a>Virtual Relationships
+
=== Virtual Relationships ===
Relationships in the schema are determined by scanning the foreign key constraints in the database. These relationships are then exposed in the `related` portion of the table schema. A virtual relationship is one whose foreign key constraint isn't discovered in the database but is provisioned by the user. These virtual foreign keys are provisioned in the field schema and stored locally as part of the system tables and applied to the discovered schema and used by table record operations for data retrieval and provisioning.
+
Relationships in the schema are determined by scanning the foreign key constraints in the database. These relationships are then exposed in the '''''related''''' portion of the table schema. A virtual relationship is one whose foreign key constraint isn't discovered in the database but is provisioned by the user. These virtual foreign keys are provisioned in the field schema and stored locally as part of the system tables and applied to the discovered schema and used by table record operations for data retrieval and provisioning.
 +
 
 +
A foreign key as a real database constraint:
 +
<syntaxhighlight lang="javascript">
 +
{
 +
    "name": "account_id",
 +
    "label": "Account Id",
 +
    "type": "reference",
 +
    "ref_table": "account",
 +
    "ref_fields": "id"
 +
}
 +
</syntaxhighlight>
 +
 
 +
A virtual foreign key pointing to the same service, different table:
 +
<syntaxhighlight lang="javascript">
 +
{
 +
    "name": "reports_to_id",
 +
    "label": "Reports To Id",
 +
    "type": "reference",
 +
    "is_virtual_foreign_key": true,
 +
    "ref_table": "contact",
 +
    "ref_fields": "id"
 +
}
 +
</syntaxhighlight>
 +
 
 +
A virtual foreign key pointing to a different service with id '6':
 +
<syntaxhighlight lang="javascript">
 +
{
 +
    "name": "mapped_email",
 +
    "type": "string",
 +
    "is_foreign_key": true,
 +
    "is_virtual_foreign_key": true,
 +
    "is_foreign_service": true,
 +
    "ref_service_id": 6,
 +
    "ref_table": "contact",
 +
    "ref_fields": "id"
 +
}
 +
</syntaxhighlight>
  
###<a name="examples"></a>Example field declarations
+
=== Example Field Declarations ===
Example field schema declarations...
+
  
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
     {
 
     {
Line 382: Line 448:
 
         "default": "",
 
         "default": "",
 
         "allow_null": false,
 
         "allow_null": false,
         "fixed_length": true
+
         "fixed_length": true,
 
         "validation": "match(nnn-nnn-nnnn)"
 
         "validation": "match(nnn-nnn-nnnn)"
 
     },
 
     },
Line 425: Line 491:
 
         "is_virtual_foreign_key": true,
 
         "is_virtual_foreign_key": true,
 
         "is_foreign_service": true,
 
         "is_foreign_service": true,
         "ref_service": "another_db",
+
         "ref_service_id": 6,
 
         "ref_table": "contact",
 
         "ref_table": "contact",
 
         "ref_fields": "id"
 
         "ref_fields": "id"
Line 452: Line 518:
 
         "type": "user_id_on_update",
 
         "type": "user_id_on_update",
 
         "validation": {"api_read_only": {"on_fail": "ignore_field"}}
 
         "validation": {"api_read_only": {"on_fail": "ignore_field"}}
     }.
+
     },
 
     {
 
     {
 
         "description": "An aggregate virtual field, used in grouping statements.",
 
         "description": "An aggregate virtual field, used in grouping statements.",
 
         "name": "max_salary",
 
         "name": "max_salary",
         "type": "virtual",
+
         "type": "double",
         "db_function": {
+
         "is_virtual": true,
            "function": "MAX(salary)",
+
        "is_aggregate": true,
             "type": "double",
+
        "db_function": [
            "aggregate": true
+
             {
        }
+
                "use": [
     }.
+
                    "SELECT"
 +
                ],
 +
                "function": "MAX(salary)",
 +
            }
 +
        ]
 +
     },
 
     {
 
     {
 
         "description": "A virtual field returning the concatenation of two other columns in the table.",
 
         "description": "A virtual field returning the concatenation of two other columns in the table.",
 
         "name": "full_name",
 
         "name": "full_name",
         "type": "virtual",
+
         "type": "string",
         "db_function": {
+
        "is_virtual": true,
             "function": "Concat(first_name, ' ', last_name)",
+
         "db_function": [
             "type": "string",
+
             {
            "aggregate": false
+
                "use": [
         }
+
                    "SELECT"
 +
                ],
 +
                "function": "Concat(first_name, ' ', last_name)",
 +
             }
 +
        ]
 +
    },
 +
    {
 +
        "description": "A real binary field being represented in the API as base64 encoded for transmission in JSON payloads.",
 +
        "name": "bdata",
 +
        "type": "binary",
 +
        "db_type": "blob",
 +
         "allow_null": true,
 +
        "db_function": [
 +
            {
 +
                "use": [
 +
                    "SELECT"
 +
                ],
 +
                "function": "TO_BASE64(bdata)",
 +
                "function_type": "database"
 +
            },
 +
            {
 +
                "use": [
 +
                    "insert",
 +
                    "update"
 +
                ],
 +
                "function": "FROM_BASE64({value})",
 +
                "function_type": "database"
 +
            }
 +
        ]
 
     }
 
     }
 
}
 
}
</source>
+
</syntaxhighlight>
  
##<a name="operations"></a>Schema Operations
+
== Schema Operations ==
 
See the following sections for more detail on CRUD operations for database schema...
 
See the following sections for more detail on CRUD operations for database schema...
  
###<a name="retrieving"></a>Retrieving Schema
+
=== Retrieving Schema ===
 
The following operations are typically available for all DreamFactory Database Services.
 
The following operations are typically available for all DreamFactory Database Services.
 
The examples given below use a single simple identifier field named "id" which is, in this case, an auto-incrementing primary key.
 
The examples given below use a single simple identifier field named "id" which is, in this case, an auto-incrementing primary key.
 
This may not be the case for all tables or all database service types. Refer to the specifics of your database type documented in other pages in this section.
 
This may not be the case for all tables or all database service types. Refer to the specifics of your database type documented in other pages in this section.
  
####<a name="list"></a>List of Tables
+
==== List of Tables ====
 
Description: Return schema data for the database, listing all accessible tables based on role access.
 
Description: Return schema data for the database, listing all accessible tables based on role access.
  
URI: **GET** `http://<server_name>/api/v2/<service_name>/_schema`
+
URI: '''GET''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema</nowiki>
  
 
Request
 
Request
>GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema/ HTTP/1.1
 
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
     "resource": [
 
     "resource": [
Line 517: Line 615:
 
     ]
 
     ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
####<a name="multiple-tables"></a>Multiple Tables By Name
+
==== Multiple Tables By Name ====
 
Description: Retrieve all available properties of one or more tables by name.
 
Description: Retrieve all available properties of one or more tables by name.
  
URI: **GET** `http[s]://<host_name>/api/v2/<service_name>/_schema/?names=<table_names>`
+
URI: '''GET''' <nowiki>http[s]://<host_name>/api/v2/<service_name>/_schema/?names=<table_names></nowiki>
  
 
Request
 
Request
> GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema?names=account%2Ccontact HTTP/1.1
+
 
 +
None
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
   "table": [
+
   "resource": [
 
     {
 
     {
 
       "name": "account",
 
       "name": "account",
Line 581: Line 680:
 
   ]
 
   ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
####<a name="single-table"></a>Single Table
+
==== Single Table ====
 
Description: Return all schema data for a single db table.
 
Description: Return all schema data for a single db table.
  
URI: **GET** `http://<server_name>/api/v2/<service_name>/_schema/<table_name>`
+
URI: '''GET''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name></nowiki>
  
 
Request
 
Request
>GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema/account HTTP/1.1
+
 
 +
None
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
     "name": "account",
 
     "name": "account",
Line 629: Line 729:
 
     ]
 
     ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
####<a name="single-field"></a>Single Field
+
==== Single Field ====
 
Description: Return schema data for a single field of a db table, including the name, label, and defining properties.
 
Description: Return schema data for a single field of a db table, including the name, label, and defining properties.
  
URI: **GET** `http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>`
+
URI: '''GET''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name></nowiki>
 +
 
 +
Prior to 2.4.0:
 +
(Deprecated) URI: '''GET''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name></nowiki>
  
 
Request
 
Request
>GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema/account/name HTTP/1.1
+
 
 +
None
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
     "name": "name",
 
     "name": "name",
Line 662: Line 766:
 
     "values": []
 
     "values": []
 
}
 
}
</source>
+
</syntaxhighlight>
  
###<a name="creating"></a>Creating Schema
+
=== Creating Schema ===
 
The following operations are typically available for creating tables on all DreamFactory Database Services. However, these operations may vary significantly in the properties that are necessary to actually create a table entity for each specific database vendor. For instance, some tables may automatically create the identifier field(s) with no input from the client, like in the case of MongoDB. Be sure to check the specific database service type for any differences documented in other pages in this section.
 
The following operations are typically available for creating tables on all DreamFactory Database Services. However, these operations may vary significantly in the properties that are necessary to actually create a table entity for each specific database vendor. For instance, some tables may automatically create the identifier field(s) with no input from the client, like in the case of MongoDB. Be sure to check the specific database service type for any differences documented in other pages in this section.
  
####Multiple Tables
+
==== Multiple Tables ====
Description: Create one or more tables in the database. Note that the request and response should be an array of table-defining property sets wrapped with a `table` element.
+
Description: Create one or more tables in the database. Note that the request and response should be an array of table-defining property sets wrapped with a '''resource''' element.
  
URI: **POST** `http[s]://<host_name>/api/v2/<service_name>/_schema/`
+
URI: '''POST''' <nowiki>http[s]://<host_name>/api/v2/<service_name>/_schema/</nowiki>
  
 
Request
 
Request
> POST http://demo.cloud.dreamfactory.com/api/v2/db/_schema HTTP/1.1
+
<syntaxhighlight lang="javascript">
 
+
<source lang="javascript">
+
 
{
 
{
   "table": [
+
   "resource": [
 
     {
 
     {
 
       "name": "account",
 
       "name": "account",
Line 706: Line 808:
 
   ]
 
   ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
   "table": [
+
   "resource": [
 
     {
 
     {
 
       "name": "account"
 
       "name": "account"
Line 720: Line 822:
 
   ]
 
   ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
####Single Table
+
==== Single Table ====
Description: Create a single table in the database. Note that the table name is part of the URL and must be properly encoded. Posted data must for a single table and not wrapped with the `table` wrapper, like above.
+
Description: Create a single table in the database. Note that the table name is part of the URL and must be properly encoded. Posted data must for a single table and not wrapped with the '''resource''' wrapper, like above.
  
URI: **POST** `http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>`
+
URI: '''POST''' <nowiki>http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name></nowiki>
  
 
Request
 
Request
> POST http://demo.cloud.dreamfactory.com/api/v2/db/_schema/todo HTTP/1.1
+
<syntaxhighlight lang="javascript">
<source lang="javascript">
+
 
{
 
{
 
   "name": "todo",
 
   "name": "todo",
Line 754: Line 855:
 
   ]
 
   ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "name": "todo"
 
   "name": "todo"
 
}
 
}
</source>
+
</syntaxhighlight>
  
####Adding a Single Field
+
==== Adding a Single Field ====
 
Description: Create a single field in a db table. The table and field name are part of the URL and must be properly encoded. For adding multiple fields at once, see [Updating Schema](Database-Updating-Schema) or [Patching Schema](Database-Patching-Schema).
 
Description: Create a single field in a db table. The table and field name are part of the URL and must be properly encoded. For adding multiple fields at once, see [Updating Schema](Database-Updating-Schema) or [Patching Schema](Database-Patching-Schema).
  
URI: **POST** `http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>`
+
URI: '''POST''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name></nowiki>
  
Request
+
Prior to 2.4.0:
> POST http://demo.cloud.dreamfactory.com/api/v2/db/_schema/todo/created_date HTTP/1.1
+
(Deprecated) URI: '''POST''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name></nowiki>
  
<source lang="javascript">
+
Request
 +
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "name": "created_date",
 
   "name": "created_date",
Line 777: Line 879:
 
   "type": "timestamp_on_create"
 
   "type": "timestamp_on_create"
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "name": "created_date"
 
   "name": "created_date"
 
}
 
}
</source>
+
</syntaxhighlight>
  
###<a name="replacing"></a>Updating or Replacing Schema
+
=== Updating or Replacing Schema ===
The following operations are typically available for all DreamFactory Database Services. Refer to the specifics of your database type documented in other pages in this section.  
+
The following operations are typically available for all DreamFactory Database Services. Refer to the specifics of your database type documented in other pages in this section.
The **PUT** verb on most points of our API is meant to completely replace an existing resource. The same holds for the schema resource. If you wish to just add new fields or modify existing fields, **PATCH** is a simpler and preferred option, as it will not change anything in the schema except what is requested.  
+
The '''PUT''' verb on most points of our API is meant to completely replace an existing resource. The same holds for the schema resource. If you wish to just add new fields or modify existing fields, '''PATCH''' is a simpler and preferred option, as it will not change anything in the schema except what is requested.
Updating a table definition with **PUT** allows you to add or modify existing fields, as well as, delete unnecessary fields, but requires you to post all of the fields that you wish to remain in the schema. Any fields that are left out, will be deleted from the schema.
+
Updating a table definition with '''PUT''' allows you to add or modify existing fields, as well as, delete unnecessary fields, but requires you to post all of the fields that you wish to remain in the schema. Any fields that are left out, will be deleted from the schema.
  
####Multiple Tables
+
==== Multiple Tables ====
Description: Update multiple tables in the database by replacing the schema with updated schema.  
+
Description: Update multiple tables in the database by replacing the schema with updated schema.
  
URI: [**PUT**] `http://<server_name>/api/v2/<service_name>/_schema/`
+
URI: '''PUT''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/</nowiki>
  
 
Request
 
Request
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
     "table": [
+
     "resource": [
 
         {
 
         {
 
             "name": "account",
 
             "name": "account",
Line 826: Line 928:
 
     ]
 
     ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
  
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
     "table": [
+
     "resource": [
 
         {
 
         {
 
             "name": "account"
 
             "name": "account"
Line 841: Line 943:
 
     ]
 
     ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
####Single Table
+
==== Single Table ====
 
Description: Update a table in the database by replacing.
 
Description: Update a table in the database by replacing.
  
URI: [**PUT**] `http://<server_name>/api/v2/<service_name>/_schema/<table_name>`
+
URI: '''PUT''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name></nowiki>
  
 
Request
 
Request
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
     "name": "contact",
 
     "name": "contact",
Line 862: Line 964:
 
     ]
 
     ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "name": "contact"
 
   "name": "contact"
 
}
 
}
</source>
+
</syntaxhighlight>
 +
 
 +
==== Single Field ====
 +
Description: Update a single field in a db table. Here for completeness, this call behaves the same as using '''PATCH''' on a single field. For updating multiple fields at once, use patching single table below.
  
####Single Field
+
URI: '''PUT''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name></nowiki>
Description: Update a single field in a db table. Here for completeness, this call behaves the same as using **PATCH** on a single field. For updating multiple fields at once, use patching single table below.
+
  
URI: [**PUT**] `http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>`
+
Prior to 2.4.0:
 +
(Deprecated) URI: '''PUT''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name></nowiki>
  
 
Request
 
Request
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "label": "My New Label",
 
   "label": "My New Label",
 
   "default": ""
 
   "default": ""
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "name": "contact"
 
   "name": "contact"
 
}
 
}
</source>
+
</syntaxhighlight>
  
###<a name="merging"></a>Patching or Merging Schema
+
=== Patching or Merging Schema ===
 
The following operations are typically available for all DreamFactory Database Services.
 
The following operations are typically available for all DreamFactory Database Services.
 
The examples given below use a single simple identifier field named "id" which is in this case an auto-incrementing primary key, this may not be the case for all tables or all database service types. Refer to the specifics of your database type documented in other pages in this section.
 
The examples given below use a single simple identifier field named "id" which is in this case an auto-incrementing primary key, this may not be the case for all tables or all database service types. Refer to the specifics of your database type documented in other pages in this section.
  
####Multiple Tables
+
==== Multiple Tables ====
 
Description: Update multiple tables in the database by adding new fields or altering existing fields.
 
Description: Update multiple tables in the database by adding new fields or altering existing fields.
  
URI: **PATCH** `http://<server_name>/api/v2/<service_name>/_schema/`
+
URI: '''PATCH''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/</nowiki>
  
 
Request
 
Request
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
     "table": [
+
     "resource": [
 
         {
 
         {
 
             "name": "account",
 
             "name": "account",
Line 930: Line 1,035:
 
     ]
 
     ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
     "table": [
+
     "resource": [
 
         {
 
         {
 
             "name": "account"
 
             "name": "account"
Line 944: Line 1,049:
 
     ]
 
     ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
####Single Table
+
==== Single Table ====
 
Description: Update a table in the database by adding new fields or altering existing fields.
 
Description: Update a table in the database by adding new fields or altering existing fields.
  
URI: **PATCH** `http://<server_name>/api/v2/<service_name>/_schema/<table_name>`
+
URI: '''PATCH''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name></nowiki>
  
 
Request
 
Request
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
     "name": "contact",
 
     "name": "contact",
Line 965: Line 1,070:
 
     ]
 
     ]
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
  
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "name": "contact"
 
   "name": "contact"
 
}
 
}
</source>
+
</syntaxhighlight>
  
####Update Field
+
==== Update Field ====
 
Description: Update a single field in a db table. For updating multiple fields at once, use table format above.
 
Description: Update a single field in a db table. For updating multiple fields at once, use table format above.
  
URI: **PATCH** `http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>`
+
URI: '''PATCH''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name></nowiki>
 +
 
 +
Prior to 2.4.0:
 +
(Deprecated) URI: '''PATCH''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name></nowiki>
  
 
Request
 
Request
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "label": "My New Label",
 
   "label": "My New Label",
 
   "default": ""
 
   "default": ""
 
}
 
}
</source>
+
</syntaxhighlight>
  
 
Response
 
Response
  
<source lang="javascript">
+
<syntaxhighlight lang="javascript">
 
{
 
{
 
   "name": "contact"
 
   "name": "contact"
 
}
 
}
</source>
+
</syntaxhighlight>
  
###<a name="deleting"></a>Deleting Schema
+
=== Deleting Schema ===
 
The following operations are typically available for all DreamFactory Database Services. Refer to the specifics of your database type documented in other pages in this section.
 
The following operations are typically available for all DreamFactory Database Services. Refer to the specifics of your database type documented in other pages in this section.
  
####Single Table
+
==== Single Table ====
 
Description: Delete (aka drop) a single table from the database.
 
Description: Delete (aka drop) a single table from the database.
  
URI: **DELETE** `http://<server_name>/api/v2/<service_name>/_schema/<table_name>`
+
URI: '''DELETE''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name></nowiki>
  
  
####Single Field
+
==== Single Field ====
 
Description: Delete (aka drop) a single field on a db table.
 
Description: Delete (aka drop) a single field on a db table.
  
URI: **DELETE** `http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>`
+
URI: '''DELETE''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name></nowiki>
 +
 
 +
Prior to 2.4.0:
 +
(Deprecated) URI: '''DELETE''' <nowiki>http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name></nowiki>

Latest revision as of 15:57, 13 April 2017

The DreamFactory database schema resource provides a way of managing the database table layout, usable fields, their storage types and requirements. By "schema", we mean in its traditional SQL database sense, i.e. a set of properties that define the layout of tables and their fields including relationships between them, not the schema namespaces used by Microsoft and other database vendors. However, we have extended this meaning to also encompass the properties that define tables on NoSQL database, i.e. any table key configuration, etc., some of which are database-type dependent.

Common Parameters & Formatting

All database services have a root-level resource called _schema that allows API access to the schema knowable by the database connection provisioned. All calls to this resource take the form of...

http[s]://<host_name>/api/v2/<service_name>/_schema/[<table_name>/[<field_name>]]

Starting in release 2.4.0, this API has been expanded to include field level manipulation...

http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>/_field/[<field_name>]

and relationship level manipulation...

http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>/_related/[<relationship_name>]

where table_name, field_name and relationship_name are optional and control what level of resource the client is acting on. All posted and returned data, when not using the table_name, field_name or relationship_name path parameter must be an array of resource-defining property sets (tables, fields, or relationships), possibly wrapped with a resource element based on system configuration. The wrapper label itself can be optional or modified, see configuration options.

{
  "resource": [ <resource_info>, <resource_info>, ... ]
}

In addition to discovering existing schema and allowing provisioning of schema, DreamFactory adds what we call extensions to our blended REST API for databases. The REST API supports a hierarchy of resources to help manage the database schema, centered around the table and field levels. For more detail see the operations section below.

There are no additional parameters or headers required for Database Schema API.

Extensions

While there are plenty of "standard" elements that you can expect to be present in most databases, particularly SQL-based ones, there are also plenty of vendor-specific types, commands, formats, etc. that make it bothersome, and sometimes difficult for client application development. After dealing with that for several years, we decided to try to make things easier by providing the following extensions.

  • a client-friendly JSON input and output layout
  • consolidated and simplified data types
  • table, field and relationship aliasing to hide database details
  • server-side validations for helping clients control data sent to the database
  • Virtual fields using database functions
  • Virtual relationships between non-related datasets
  • table, field and relationship labels for client usage
  • field-level client settings for aiding clients in form building

If you know your favorite database flavored SQL, or NoSQL, like the back of your hand, we will not hinder you (though you might find some of this useful), but if you are like most application developers, the last thing you want to do is go find your database documentation, load up some workbench environment, and/or beg a friend or a perfect stranger for help just to define a table to store your application's data.

Layout Format

Below is a JSON schema layout we came up with (pooled from various other platforms and environments over the years) to help define your table configuration, including our extensions, with each element defined below. All elements may not be available for all database, table, or field types.

<table_info> =

{
    "name": "<table_name>",
    "alias": "<table_alias>",
    "label": "<table_label>",
    "plural": "<table_plural_label>",
    "description": "<table_description>",
    "primary_key": "<primary_key_field_names>",
    "name_field": "<naming_field_name>",
    "native": [ <platform_native_info>, ... ],
    "field": [ <field_info>, ... ],
    "related": [ <relationship_info>, ... ],
    "options": "<SQL_fragments>",
    "access": <verb_mask>,
    "raw": "<raw_vendor_specific_table_definition>"
}
  • name: String. Required. The database vendor compatible table name used to designate a table via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed table names. If schema namespaces apply for tables, then the names must use the <schema_name>.<table_name> convention commonly used by those vendors.
  • alias: String. Optional. The user defined table name alias to be used to designate a table via the table records (_table) resource. The alias will also be used in Role Service Access on the table records (_table/<alias>) assignments. The name value above is always used to designate a table via the _schema resource.
  • label: String. Optional. See labels section.
  • plural: String. Optional. See labels section.
  • description: String. Optional. See labels section.
  • native: Array. Optional. Any platform-specific information discovered from the table. Information available varies by platform.
  • field: Array. Required for SQL DB service types. A wrapper for an array of field definitions. See below for definition specifics. Each field consists of properties defining that field and its usage. A minimum of one field is required to create a table.
  • related: Array. Optional. A wrapper for an array of relationship definitions. See below for definition specifics. Currently only supported on SQL DB service types. Each relationship consists of properties defining that relationship and its usage. Relationships can be detected automatically by the application or provisioned by the user, see virtual relationships.
  • options: String. Optional. Request Only. These are database-specific create or alter fragments for things not generally supported otherwise when defining the table or fields, such as defining complicated multi-field keys, or the MySQL engine version, i.e. "ENGINE=InnoDB".
  • access: Integer. Read-Only. If the include_access URL parameter is set to true, then this returns the HTTP verb mask of allowed access.
  • raw: String. Optional. Request-Only. Allows a pure SQL-compatible definition for a table. If this property is defined, no other properties besides the name, alias, label, and description are looked at. The definition must be supported directly by the underlying database as no translations are done, i.e. "(sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)));".


<field_info>

{
    "name": "<field_name>",
    "alias": "<field_alias>",
    "label": "<field_label>",
    "description": "<field_description>",
    "client_info": {<user_defined_client_information>},
    "native": {<database_platform_specific_information>},
    "type": "<simplified_type>",
    "db_type": "<database_specific_type>",
    "length": <max_length>,
    "precision": <decimal_precision>,
    "scale": <decimal_scale>,
    "default": <default_value_or_expression>,
    "required": [true|false],
    "allow_null": [true|false],
    "fixed_length": [true|false],
    "supports_multibyte": [true|false],
    "auto_increment": [true|false],
    "is_index": [true|false],
    "is_unique": [true|false],
    "is_primary_key": [true|false],
    "is_foreign_key": [true|false],
    "ref_table": "<foreign_key_referenced_table>",
    "ref_fields": "<foreign_key_referenced_fields>",
    "ref_on_update": "[RESTRICT|CASCADE|SET NULL|NO ACTION]",
    "ref_on_delete": "[RESTRICT|CASCADE|SET NULL|NO ACTION]",
    "validation": {
        "<validation_name>" : { <validation_info> },
        ...
    },
    "db_function": [
        {
            "use": [SELECT|FILTER|INSERT|UPDATE],
            "function": <database_function_syntax>,
            "function_type": "database"
        }
    ],
    "picklist": [
        <value>,
        ...
    ],
    "is_virtual": [true|false],
    "is_aggregate": [true|false],
    "raw": "<raw_vendor_specific_field_definition>"
}
  • name: String. Required. The database vendor compatible field name used to designate a field via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed field names.
  • alias: String. Optional. The user defined field name alias to be used to designate a field via the table records (_table) resource, i.e. in the fields URL parameter option or posted or retrieved records. The name value above is always used to designate a field via the _schema resource.
  • label: String. Optional. See labels section.
  • plural: String. Optional. See labels section.
  • description: String. Optional. See labels section.
  • client_info: JSON String. Optional. User-defined JSON object allows client to define persisted settings for building forms, etc.
  • native: JSON String. Optional. Database platform-defined JSON object containing platform specific settings discovered about the field, retrieve and sometimes update settings possible.
  • type: String. Required if raw property is not defined. In server responses, the value for this element is closest derived DreamFactory simplified type, if none apply, then it will be set to "string". In requests to the server, the value can be one of the simplified types (i.e. "string”, see the types section for more info), or other types supported directly by the underlying database. If using the latter (db-specific types), make sure you include any additional parameters in the type string, such as length, i.e. "nvarchar(128)", as the length, precision, scale, fixed_length and supports_multibyte parameters are not considered in this case.
  • db_type: String. Read-Only. In server responses, the value for this element is the type as defined directly by the underlying database.
  • fixed_length: Boolean. Optional for string type. Set to true to represent string types as fixed length (i.e. ‘char’), false for variable length (i.e. ‘varchar’) which is the default.
  • supports_multibyte: Boolean. Optional for string type. Set to true to represent string types that support multibyte characters, i.e. "national" in most database vernacular, (i.e. "utf8"); use false for single byte character support only (i.e. ‘ascii’) which is the default.
  • length: Integer. Optional. Used to define the max length of strings and number fields. For strings, if length is not defined, the default is 255. Note: length and size in some cases can be used interchangeably.
  • precision: Integer. Optional. Used by the "decimal" and "money", and in some cases the "double" and "float", types. It represents the total length of the number allowed minus the ‘.’, and can be set either by the length property or the precision property, length takes precedence.
  • scale: Integer. Optional. Used by the "decimal" and "money" types, where it defines the number of decimal places to the right of the decimal. Only applies if the length or precision property is given. If scale is not given, the default is 0, i.e. no decimal places.
  • default: Mixed. Optional. Use to define the SQL DEFAULT parameter of a field, supported values are dependent on the "type" designation, but may include null, strings, numbers, and SQL expressions, i.e. "NOW()" for datetime, etc.
  • required: Boolean. Read-Only. Defines whether or not a value is required for this field when creating a record, it is set to the equivalent of if required = !(allowNull || defaultValue || autoIncrement).
  • allow_null: Boolean. Optional. Defines whether or not the NULL value is allowed to be set for the field, ie. false = "NOT NULL". If not defined, the default is allow NULL (true).
  • auto_increment: Boolean. Optional. Set to true to allow auto-incrementing of an integer or primary key. If not defined, the default is false.
  • is_primary_key: Boolean. Optional. Set to true to define non-trivial primary keys, use once per table definition. If not defined, the default is false, except for the "id” type, see below.
  • is_foreign_key: Boolean. Optional. Set to true to define foreign keys that are not stored as integers or point to multiple primary keys. If not defined, the default is false, except for the "reference" type, see below.
  • ref_table: String. Required when type is "reference" or when is_foreign_key is true. Defines the table name that a reference field points to.
  • ref_fields: String. Required when type is "reference" or when is_foreign_key is true. Defines the field(s) of the referenced table defined by ref_table property that the field is referencing. If multiple fields are part of the key, separate the fields by a comma.
  • ref_on_update: String. Optional when type is "reference" or when is_foreign_key is true. defines the operation to take when the referenced record is updated. May not be supported by all database types, see ref_on_delete.
  • ref_on_delete: String. Optional when type is "reference" or when is_foreign_key is true. Defines the operation to take when the referenced record is deleted. Options are typically "RESTRICT","CASCADE", "SET NULL", "NO ACTION", but may be database dependent. Default is database dependent.
  • is_unique: Boolean. Optional. Set to true to require that each row has a unique value for this field. This is typically implemented as a unique index on the table.
  • is_index: Boolean. Optional. Set to true to set the field as a table index to speed up common searches that use this field. Typically this isn't supported for large fields, like "text".
  • picklist: Array of strings or integers. Optional. Designates values allowed for the field defined, type of the field must match the values given, i.e. string for an array of string values. Use the "picklist" and "multi_picklist" validations to restrict changes to this field's value via the API to only these values, otherwise these serve as suggestions to the client.
  • validation: Array of settings. Optional. See validations for more info.
  • db_function: Object. Optional. See database functions for more info.
  • is_virtual: Boolean. Optional. Set to true to designate a new field as virtual, see virtual fields. If not defined, the default is false.
  • is_aggregate: Boolean. Optional. Applicable only is is_virtual is true. Set to true to designate that this virtual field uses an aggregate function. Aggregate fields are not included in GET requests by default. If not defined, the default is false.
  • raw: String. Optional. Request-Only. Allows a pure SQL-compatible definition for a field. If this property is defined, no other properties besides the name, alias, label, description, and client_info are looked at. SQL types in the definition must be supported directly by the underlying database as no translations are done, i.e. "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY".


<relationship_info>

{
    "name": "<relationship_name>",
    "alias": "<relationship_alias>",
    "label": "<user_defined_label>",
    "description": "<user_defined_description>",
    "always_fetch": [true|false],
    "type": [belongs_to|has_many|many_many],
    "field": "<field_of_this_table>",
    "is_virtual": [true|false],
    "ref_service_id": <service_id_of_foreign_table>,
    "ref_table": "<foreign_table>",
    "ref_fields": "<field_of_foreign_table>",
    "ref_on_update": "[RESTRICT|CASCADE|SET NULL|NO ACTION]",
    "ref_on_delete": "[RESTRICT|CASCADE|SET NULL|NO ACTION]",
    "junction_service_id": <foreign_service_id>,
    "junction_table": "<foreign_table>",
    "junction_field": "<field_of_foreign_table>",
    "junction_ref_field": "<field_of_foreign_table>"
}
  • name: String. Required. The database-type compatible table or field name used to designate a table or field via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed table or field names. If schema namespaces are used for tables, then the names must use the <schema_name>.<table_name> convention commonly used by those vendors.
  • alias: String. Optional. The user defined relation name alias to be used to designate a relation via the table records (_table) resource, i.e. in the related URL parameter option or posted or retrieved records. The name value above is always used to designate a relation via the _schema resource.
  • label: String. Optional. See labels section.
  • native: JSON String. Optional. Database platform-defined JSON object containing platform specific settings discovered about the relationship.
  • description: String. Optional. See labels section.
  • type: String. Required. The type of relationship represented.
  • is_virtual: Boolean. Optional. Set to true if the relationship is due to arbitrary foreign keys, see virtual relationships.
  • ref_service_id: Integer. This defines the service by its id value that a reference points to.
  • ref_table: String. Defines the table name that a reference field points to.
  • ref_fields: String. Defines the field(s) of the referenced table defined by ref_table property that the field is referencing. If multiple fields are part of the key, separate the fields by a comma.
  • ref_on_update: String. Optional when type is "reference" or when is_foreign_key is true. defines the operation to take when the referenced record is updated. May not be supported by all database types, see ref_on_delete.
  • ref_on_delete: String. Optional when type is "reference" or when is_foreign_key is true. Defines the operation to take when the referenced record is deleted. Options are typically "RESTRICT","CASCADE", "SET NULL", "NO ACTION", but may be database dependent. Default is database dependent.
  • junction_service_id: Integer. When type is many_many, this defines the service where the junction table exists.
  • junction_table: String. Defines the name of the junction table.
  • junction_field: String. Defines the field of the junction table that refers to primary table.
  • junction_ref_field: String. Defines the field of the junction table that refers to the referenced table defined by ref_table

Aliases

Whether it is for security reasons or because you just don't care for the names in the database, DreamFactory allows you to provision and use aliases for the table names, field names and relationship names. The aliases must be used when accessing any of the three elements via the Table (_table) resource. For example if I provision an alias for a table named 'todo' to 'task', when I access the table through the Schema (_schema) resource, the client must use /api/v2/_schema/todo, however to query records, the client must use /api/v2/_table/task. Likewise for any field names used in records or query parameters, like fields or filter.

Labels

Labels in singular and plural form, as well as, a description value are tracked in a system table and are relayed to the client along with the schema. They are not used in API calls. They can contain UTF-8 characters, including spaces, but no other whitespace characters for label and plural. These are useful in client applications so that form labels don't have to be hard coded, see our admin applications, like the Data Manager, for example. Labels are available for table,field and relationship names. Plural labels are only available for table names. Labels can be sent and saved on the server along with other properties when creating or updating table schema. When none are specified, labels are auto-generated based on commonly used techniques, i.e. -y becomes -ies, etc.

Simplified Types

The supported simple types are defined as follows.

  • id or pk: defines a typical table identifier, translates to
    "INT NOT NULL AUTO_INCREMENT PRIMARY KEY"
    . This type requires no other properties to define the field. It presumes a type of int with allow_null set to false, the auto_increment and is_primary_key are set to true. It can only be used once in a table definition.
  • reference: defines a typical foreign key, presumes the type of int and requires the ref_table and ref_fields properties to be defined as well. Optional defining properties are ref_on_delete, ref_on_update, allow_null and default. Similar to SQL fragment
    "FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE”
    .
  • string: defines a string field (i.e. varchar or char), length defaults to 255, but can be set using the length property. Set the fixed_length property to true for fixed length (i.e. char) behavior. Set supports_multibyte for multi-byte, national (i.e. nvarchar) behavior. Other optional properties are allow_null, default, values, and validation.
  • text: defines a large string field (i.e. MySQL's text or MSSQL's varchar[max]), defaults to the largest length string allowed by the underlying database. Optional properties are allow_null and default.
  • binary: defines a binary string field (i.e. varbinary or binary), defaults to a length of 255, but can be set using the length property. Set the fixed_length property to true for fixed length (i.e. binary) behavior. Optional properties are allow_null and default.
  • boolean: defines a boolean field, which may be represented by int of length 1, using 0 and 1 values, if a true and false boolean type is not supported by the underlying database. Optional properties are allow_null and default.
  • integer: defines an integer field. Use length to set the displayable length of the integer, i.e. int(11) in MySQL. Optional properties are allow_null, default, values, and validation.
  • float: defines a standard single-precision float field. Some databases (i.e. MySQL) allow length or precision settings. Optional properties are allow_null and default.
  • double: defines a standard double-precision float field. Some databases (i.e. MySQL) allow length or precision settings. Optional properties are allow_null and default.
  • decimal: defines a standard decimal field. Use scale to set the number of desired decimal places to the right of the decimal point. Use length or precision to set the total number of digit positions. Optional properties are allow_null and default.
  • datetime: a datetime field. Optional properties are allow_null, default, values, and validation.
  • date: a date field. Optional properties are allow_null, default, values, and validation.
  • time: a time field. Optional properties are allow_null, default, values, and validation.
  • timestamp: a date and time stamp with timezone awareness where applicable, i.e. MySQL’s timestamp not null default 0 or MSSQL’s datetimeoffset.
  • timestamp_on_create: a timestamp as documented above that will be automatically set on record creation and not updated again unless set by the client via the API. See "api_read_only" validation for keeping this from being set by API.
  • timestamp_on_update: a timestamp as documented above that will be automatically set on record creation and again on every update. See "api_read_only" validation for keeping this from being set by API.
  • user_id: a reference to the current user, assuming a valid session has been established. On the native database, this is implemented as a reference field pointing to the system's user table, on other databases, it is implemented as an integer. Optional properties are allow_null and default.
  • user_id_on_create: a user_id as documented above that will be automatically set on record creation and not updated again unless set by the client via the API. See "api_read_only" validation for keeping this from being set by API.
  • user_id_on_update: a user_id as documented above that will be automatically set on record creation and again on every update. See "api_read_only" validation for keeping this from being set by API.


Validations

Some server-side validations can be defined for each field by setting this property to one or more of the following options in the given format below. Note that additional validation will result in additional processing which may slow response times. Validations are configured in the following format...

{
  "validation":
    {
      "<validation_name>":
        {
          "on_fail" : "[ignore_field | <error_msg>]"
          <other_config>,
          ...
        },
      ...
    }
}

where on_fail is a configuration option that takes either the value ignore_field, dictating if the field should be ignored if it does not pass validation, or a specified error message to overwrite the generic exception thrown. If this configuration is missing, a generic exception will be thrown stating the validation has failed.

Other validation configuration options are documented below for each validation type.

Possible validations settings are as follows...

  • picklist - Supported for string type only. It requires the field value to be set to one of the values listed in the values property. Any error is returned if the values property is empty or does not contain the value being set. Values are checked only on create and update record API calls; data integrity is kept for existing values even when the picklist value list is modified. Behaves similar to MySQL "enum" type.
  • multi_picklist - similar to picklist but allows multiple values to be selected and stored. Behaves similar to MySQL "set" type.
    • delimiter - Optional. Defaults to comma, ",". Parameter allows setting the delimiter character used to separate each value being set from what is being stored in the field value.
    • min - Optional. Integer. Defaults to 1. Number of minimum required selections from the list.
    • max - Optional. Integer. If set, it designates the maximum required selections from the list.
  • api_read_only - sets this field as read only through the API. Use "default” property to set values, i.e. useful for creating timestamps, etc. Supported for all types.
  • create_only - sets this field to only allow values to be set on record creation. Supported for all types.
  • not_null - validates that the field value to be set is not null. Supported for all types,
  • not_empty - validates that the field value to be set is not empty string. Supported for string and text types. Null is not checked.
  • not_zero - validates that the field value to be set is not zero (0). Supported for integers, decimals and floats. Null is not checked.
  • int - validates that the value to be set is between the min and max integer values designated. Supported for integers.
  • range - Optional. Defines the min and max range allowed for the integer.
    • min - Optional. If set, it designates the minimum value allowed.
    • max - Optional. If set, it designates the maximum value allowed.
    • formats - Optional. Selects other allowable formats for the integers, "hex" and "octal" currently available.
  • float - validates that the value to be set is a valid float.
    • decimal - Optional. Defaults to '.'. Designates the character used as the decimal separator, which is sometimes ','.
  • boolean - validates that the value to be set is one of the generally accepted values for true or false, i.e. true = 1, "1", "true", "on" and "yes". false = 0, "0", "false", "off", "no", and "".
  • email - validates that this field is an email, i.e. "name@company.com”. Supported for string type only. See FILTER_VALIDATE_EMAIL.
  • url - validates that this field is a url, i.e. starts with "http(s)://”. Supported for string type only.
    • sections - Optional. Array of Strings. Designates required sections of the URL, "path" and "query" currently available.
  • match - for strings matching a regular expression designated by regex. Supported for string type only.
    • regex - Required. String. Exception thrown if not defined or empty. Due to special characters in regular expressions, the regex value must be encoded as base64 before being provisioned.

Virtual Fields

A virtual field is a field defined by using the is_virtual field schema designation that doesn't actually exist in the database but is used in fields (i.e. SELECT clause) and filter (i.e. WHERE clause) URL parameters. Virtual fields MUST define the db_function parameter below. Virtual fields included in POST, PUT, or PATCH request are ignored.

Database Functions

A database function can be defined for a field, virtual or real, by setting this property to any function supported by the database, i.e aggregate functions like MAX() for SELECT statements. If the db_function is applied to an existing database field, the actual value of the field in the database is not returned, but the function's value is returned instead. For example, if you wanted a certain string field, say "title" to be returned upper-case, regardless of its actual database value, we could add a db_function with function set to UPPER(title). Everywhere "title" is used the value will be uppercased.

As of 2.4.2, you can now designate a database function to be used for each of the following specific API uses...

  • SELECT - Used for GET methods and all internal use of fields values, i.e. behavior of the SQL SELECT clause.
  • FILTER - Used for all filter uses where the field part of the filter is replaced with the designated function, i.e. behavior of the SQL WHERE clause.
  • INSERT - Used for all insert clauses for the field, where the value is replaced with the function call, i.e. behavior of the SQL INSERT request.
  • UPDATE - Used for all set clauses for the field, where the value is replaced with the function call, i.e. behavior of the SQL UPDATE request.

Database functions are configured in the following format...

{
    "db_function": [
        {
            "use": [
                "SELECT"
            ],
            "function": "TO_BASE64(bdata)",
            "function_type": "database"
        },
        {
            "use": [
                "INSERT",
                "UPDATE"
            ],
            "function": "FROM_BASE64({value})",
            "function_type": "database"
        }
    ],
}

where use is an array of the use cases where this function is to be used, and function is the full raw syntax of the supported function call. 'function_type currently only supports "database", meaning the database itself is the one executing the function.

Note: For SELECT and FILTER functions, use the field name, not alias, where the field is supposed to be used. For INSERT and UPDATE functions, use the lookup syntax "{value}" which will perform the function on the value of the field passed in from the payload.

Database functions are database vendor dependent, but some common functions are listed below...

  • Aggregate Functions such as AVG, COUNT, MAX, MIN, SUM, etc. Set is_aggregate to true for these, so that API usage of fields=* doesn't attempt to return these fields.
    • ex. Max(salary) - Returns the maximum value of an existing column in the table named "salary", numerically for numbers or alphabetically for strings.
  • Scalar Functions such as UCASE, LCASE, MID, LEN
    • ex. LEN(address) - Returns the length of an existing value of the column in the table.
  • Vendor-specific Functions such as SQL Server's CONVERT() or <field_name>.ToString().
  • User-defined Functions if supported by the database vendor.

Virtual Relationships

Relationships in the schema are determined by scanning the foreign key constraints in the database. These relationships are then exposed in the related portion of the table schema. A virtual relationship is one whose foreign key constraint isn't discovered in the database but is provisioned by the user. These virtual foreign keys are provisioned in the field schema and stored locally as part of the system tables and applied to the discovered schema and used by table record operations for data retrieval and provisioning.

A foreign key as a real database constraint:

{
    "name": "account_id",
    "label": "Account Id",
    "type": "reference",
    "ref_table": "account",
    "ref_fields": "id"
}

A virtual foreign key pointing to the same service, different table:

{
    "name": "reports_to_id",
    "label": "Reports To Id",
    "type": "reference",
    "is_virtual_foreign_key": true,
    "ref_table": "contact",
    "ref_fields": "id"
}

A virtual foreign key pointing to a different service with id '6':

{
    "name": "mapped_email",
    "type": "string",
    "is_foreign_key": true,
    "is_virtual_foreign_key": true,
    "is_foreign_service": true,
    "ref_service_id": 6,
    "ref_table": "contact",
    "ref_fields": "id"
}

Example Field Declarations

{
    {
        "name": "id",
        "type": "id"
    },
    {
        "name": "first_name",
        "type": "string",
        "is_index": true
    },
    {
        "name": "last_name",
        "label": "Last Name",
        "type": "string",
        "length": 40,
        "default": "",
        "allow_null": false,
        "is_index": true
    },
    {
        "name": "display_name",
        "label": "Display Name",
        "type": "string",
        "length": 255,
        "allow_null": false,
        "supports_multibyte": true,
        "is_unique": true,
        "validation": {"not_empty": {"on_fail": "Display name must not be empty."}}
    },
    {
        "name": "description",
        "label": "What’s the deal...",
        "type": "text"
    },
    {
        "name": "email",
        "label": "Email",
        "type": "string",
        "length": 320,
        "default": null,
        "allow_null": true,
        "validation": {"email": {"on_fail": "Please enter a valid email address."}}
    },
    {
        "name": "phone",
        "label": "Phone",
        "type": "string",
        "length": 12,
        "default": "",
        "allow_null": false,
        "fixed_length": true,
        "validation": "match(nnn-nnn-nnnn)"
    },
    {
        "name": "Rating",
        "label": "Rating",
        "type": "string",
        "length": 40,
        "values": [ "Unknown","Cold","Warm","Hot" ],
        "default": "Unknown",
        "allow_null": false,
        "validation": {"picklist": {"on_fail": "Please select one of the valid options."}}
    },
    {
        "name": "website",
        "label": "Website",
        "type": "string",
        "validation": {"url": {"on_fail": "Invalid URL given."}}
    },
    {
        "description": "A foreign key as a real database constraint.",
        "name": "account_id",
        "label": "Account Id",
        "type": "reference",
        "ref_table": "account",
        "ref_fields": "id"
    },
    {
        "description": "A virtual foreign key pointing to the same service, different table.",
        "name": "reports_to_id",
        "label": "Reports To Id",
        "type": "reference",
        "is_virtual_foreign_key": true,
        "ref_table": "contact",
        "ref_fields": "id"
    },
    {
        "description": "A virtual foreign key pointing to a different service.",
        "name": "mapped_email",
        "type": "string",
        "is_foreign_key": true,
        "is_virtual_foreign_key": true,
        "is_foreign_service": true,
        "ref_service_id": 6,
        "ref_table": "contact",
        "ref_fields": "id"
    },
    {
        "name": "created_date",
        "label": "Created Date",
        "type": "timestamp_on_create",
        "validation": {"api_read_only": {"on_fail": "ignore_field"}}
    },
    {
        "name": "last_modified_date",
        "label": "Last Modified Date",
        "type": "timestamp_on_update",
        "validation": {"api_read_only": {"on_fail": "ignore_field"}}
    },
    {
        "name": "created_by_id",
        "label": "Created By Id",
        "type": "user_id_on_create",
        "validation": {"api_read_only": {"on_fail": "ignore_field"}}
    },
    {
        "name": "last_modified_by_id",
        "label": "Last Modified By Id",
        "type": "user_id_on_update",
        "validation": {"api_read_only": {"on_fail": "ignore_field"}}
    },
    {
        "description": "An aggregate virtual field, used in grouping statements.",
        "name": "max_salary",
        "type": "double",
        "is_virtual": true,
        "is_aggregate": true,
        "db_function": [
            {
                "use": [
                    "SELECT"
                ],
                "function": "MAX(salary)",
            }
        ]
    },
    {
        "description": "A virtual field returning the concatenation of two other columns in the table.",
        "name": "full_name",
        "type": "string",
        "is_virtual": true,
        "db_function": [
            {
                "use": [
                    "SELECT"
                ],
                "function": "Concat(first_name, ' ', last_name)",
            }
        ]
    },
    {
        "description": "A real binary field being represented in the API as base64 encoded for transmission in JSON payloads.",
        "name": "bdata",
        "type": "binary",
        "db_type": "blob",
        "allow_null": true,
        "db_function": [
            {
                "use": [
                    "SELECT"
                ],
                "function": "TO_BASE64(bdata)",
                "function_type": "database"
            },
            {
                "use": [
                    "insert",
                    "update"
                ],
                "function": "FROM_BASE64({value})",
                "function_type": "database"
            }
        ]
    }
}

Schema Operations

See the following sections for more detail on CRUD operations for database schema...

Retrieving Schema

The following operations are typically available for all DreamFactory Database Services. The examples given below use a single simple identifier field named "id" which is, in this case, an auto-incrementing primary key. This may not be the case for all tables or all database service types. Refer to the specifics of your database type documented in other pages in this section.

List of Tables

Description: Return schema data for the database, listing all accessible tables based on role access.

URI: GET http://<server_name>/api/v2/<service_name>/_schema

Request

Response

{
    "resource": [
        {
            "name": "account",
            "label": "Account",
            "plural": "Accounts",
			"access": [
				"GET",
				...
			]
        },
        {
            "name": "contact",
            "label": "Contact",
            "plural": "Contacts",
			"access": [
				"GET",
				...
			]
        },
		...
    ]
}

Multiple Tables By Name

Description: Retrieve all available properties of one or more tables by name.

URI: GET http[s]://<host_name>/api/v2/<service_name>/_schema/?names=<table_names>

Request

None

Response

{
  "resource": [
    {
      "name": "account",
      "label": "Account",
      "plural": "Accounts",
      "primary_key": "id",
      "name_field": null,
      "field": [
        {
          "name": "id",
          "label": "Id",
          "type": "id",
          "db_type": "int(11)",
          ...
        },
        ...
      ],
      "access": [
        "GET",
        "POST",
        "PUT",
        "PATCH",
        "DELETE"
      ]
    },
    {
      "name": "contact",
      "label": "Contact",
      "plural": "Contacts",
      "primary_key": "id",
      "name_field": null,
      "field": [
        {
          "name": "id",
          "label": "Id",
          "type": "id",
          "db_type": "int(11)",
          ...
        },
        ...
      ],
      "access": [
        "GET",
        "POST",
        "PUT",
        "PATCH",
        "DELETE"
      ]
    }
  ]
}

Single Table

Description: Return all schema data for a single db table.

URI: GET http://<server_name>/api/v2/<service_name>/_schema/<table_name>

Request

None

Response

{
    "name": "account",
    "label": "Account",
    "plural": "Accounts",
    "primary_key": "id",
    "name_field": "",
    "field": [
        {
            "name": "id",
            "label": "Account ID",
            "type": "id",
            "db_type": "int(11)",
            ...
        },
        ...
    ],
    "related": [
        {
            "name": "Contacts_by_AccountId",
            "type": "has_many",
            "ref_table": "Contact",
            "ref_field": "AccountId",
            "field": "Id"
        },
        ...
    ],
    "access": [
        "GET",
        "POST",
        "PUT",
        "PATCH",
        "MERGE",
        "DELETE",
        "ADMIN"
    ]
}

Single Field

Description: Return schema data for a single field of a db table, including the name, label, and defining properties.

URI: GET http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>

Prior to 2.4.0: (Deprecated) URI: GET http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>

Request

None

Response

{
    "name": "name",
    "label": "Name",
    "type": "string",
    "db_type": "varchar(40)",
    "length": 40,
    "precision": 40,
    "scale": 0,
    "default": null,
    "required": true,
    "allow_null": false,
    "fixed_length": false,
    "supports_multibyte": false,
    "auto_increment": false,
    "is_primary_key": false,
    "is_foreign_key": false,
    "ref_table": "",
    "ref_fields": "",
    "validation": "not_empty",
    "values": []
}

Creating Schema

The following operations are typically available for creating tables on all DreamFactory Database Services. However, these operations may vary significantly in the properties that are necessary to actually create a table entity for each specific database vendor. For instance, some tables may automatically create the identifier field(s) with no input from the client, like in the case of MongoDB. Be sure to check the specific database service type for any differences documented in other pages in this section.

Multiple Tables

Description: Create one or more tables in the database. Note that the request and response should be an array of table-defining property sets wrapped with a resource element.

URI: POST http[s]://<host_name>/api/v2/<service_name>/_schema/

Request

{
  "resource": [
    {
      "name": "account",
      "label": "Account",
      "plural": "Accounts",
      "field": [
        {
          "name": "id",
          "label": "Account ID",
          "type": "id"
        },]
    },
    {
      "name": "contact",
      "label": "Contact",
      "plural": "Contacts",
      "field": [
        {
          "name": "id",
          "label": "Contact ID",
          "type": "id"
        },]
    }
  ]
}

Response

{
   "resource": [
     {
       "name": "account"
     },
     {
       "name": "contact"
     }
   ]
}

Single Table

Description: Create a single table in the database. Note that the table name is part of the URL and must be properly encoded. Posted data must for a single table and not wrapped with the resource wrapper, like above.

URI: POST http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>

Request

{
  "name": "todo",
  "label": "Todo",
  "plural": "Todos",
  "field": [
    {
      "name": "id",
      "label": "Todo ID",
      "type": "id"
    },
    {
      "name": "name",
      "label": "Title",
      "type": "string"
    },
    {
      "name": "complete",
      "label": "Completed?",
      "type": "boolean",
      "allow_null": false,
      "default": false
    }
  ]
}

Response

{
  "name": "todo"
}

Adding a Single Field

Description: Create a single field in a db table. The table and field name are part of the URL and must be properly encoded. For adding multiple fields at once, see [Updating Schema](Database-Updating-Schema) or [Patching Schema](Database-Patching-Schema).

URI: POST http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>

Prior to 2.4.0: (Deprecated) URI: POST http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>

Request

{
  "name": "created_date",
  "label": "Created Date",
  "type": "timestamp_on_create"
}

Response

{
  "name": "created_date"
}

Updating or Replacing Schema

The following operations are typically available for all DreamFactory Database Services. Refer to the specifics of your database type documented in other pages in this section. The PUT verb on most points of our API is meant to completely replace an existing resource. The same holds for the schema resource. If you wish to just add new fields or modify existing fields, PATCH is a simpler and preferred option, as it will not change anything in the schema except what is requested. Updating a table definition with PUT allows you to add or modify existing fields, as well as, delete unnecessary fields, but requires you to post all of the fields that you wish to remain in the schema. Any fields that are left out, will be deleted from the schema.

Multiple Tables

Description: Update multiple tables in the database by replacing the schema with updated schema.

URI: PUT http://<server_name>/api/v2/<service_name>/_schema/

Request

{
    "resource": [
        {
            "name": "account",
            "label": "Account",
            "plural": "Accounts",
            "field": [
                {
                    "name": "new_field",
                    "label": "My New Field",
                    "type": "string"
                },]
        },
        {
            "name": "contact",
            "field": [
                {
                    "name": "old_field",
                    "label": "New Type",
                    "type": "integer"
                },]
        }
    ]
}

Response

{
    "resource": [
        {
            "name": "account"
        },
        {
            "name": "contact"
        }
    ]
}

Single Table

Description: Update a table in the database by replacing.

URI: PUT http://<server_name>/api/v2/<service_name>/_schema/<table_name>

Request

{
    "name": "contact",
    "label": "Contact",
    "field": [
        {
            "name": "new_field",
            "label": "My New Field",
            "type": "string"
        },]
}

Response

{
  "name": "contact"
}

Single Field

Description: Update a single field in a db table. Here for completeness, this call behaves the same as using PATCH on a single field. For updating multiple fields at once, use patching single table below.

URI: PUT http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>

Prior to 2.4.0: (Deprecated) URI: PUT http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>

Request

{
  "label": "My New Label",
  "default": ""
}

Response

{
  "name": "contact"
}

Patching or Merging Schema

The following operations are typically available for all DreamFactory Database Services. The examples given below use a single simple identifier field named "id" which is in this case an auto-incrementing primary key, this may not be the case for all tables or all database service types. Refer to the specifics of your database type documented in other pages in this section.

Multiple Tables

Description: Update multiple tables in the database by adding new fields or altering existing fields.

URI: PATCH http://<server_name>/api/v2/<service_name>/_schema/

Request

{
    "resource": [
        {
            "name": "account",
            "label": "Account",
            "plural": "Accounts",
            "field": [
                {
                    "name": "new_field",
                    "label": "My New Field",
                    "type": "string"
                },]
        },
        {
            "name": "contact",
            "field": [
                {
                    "name": "old_field",
                    "label": "New Type",
                    "type": "integer"
                },]
        }
    ]
}

Response

{
    "resource": [
        {
            "name": "account"
        },
        {
            "name": "contact"
        }
    ]
}

Single Table

Description: Update a table in the database by adding new fields or altering existing fields.

URI: PATCH http://<server_name>/api/v2/<service_name>/_schema/<table_name>

Request

{
    "name": "contact",
    "label": "Contact",
    "field": [
        {
            "name": "new_field",
            "label": "My New Field",
            "type": "string"
        },]
}

Response

{
  "name": "contact"
}

Update Field

Description: Update a single field in a db table. For updating multiple fields at once, use table format above.

URI: PATCH http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>

Prior to 2.4.0: (Deprecated) URI: PATCH http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>

Request

{
  "label": "My New Label",
  "default": ""
}

Response

{
  "name": "contact"
}

Deleting Schema

The following operations are typically available for all DreamFactory Database Services. Refer to the specifics of your database type documented in other pages in this section.

Single Table

Description: Delete (aka drop) a single table from the database.

URI: DELETE http://<server_name>/api/v2/<service_name>/_schema/<table_name>


Single Field

Description: Delete (aka drop) a single field on a db table.

URI: DELETE http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>

Prior to 2.4.0: (Deprecated) URI: DELETE http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>