Schema

From DreamFactory
Jump to: navigation, search

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 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

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>]]

where `table_name` and `field_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` 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. 

{
  "resource": [ <table_info> ]
}

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.

<a name="extensions"></a>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
  • 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
  • and #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.

<a name="format"></a>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>",
    "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 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.
  • `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>},
    "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],
    "is_virtual_foreign_key": [true|false],
    "is_foreign_ref_service": [true|false],
    "ref_service_id": "<foreign_key_referenced_service_id>",
    "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": {
        "function": <database_function_syntax>, 
        "type": "[string,integer,double,float]",
        "aggregate": [true,false]
    },
    "value": [
        <value>,
        ...
    ],
    "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.
  • `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 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.
  • `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.
  • `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.
  • `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.
  • `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".
  • `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.
  • `validation`: Array of settings. Optional. See #validations for more info.
  • `db_function`: Object. Optional. See database functions 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".


`<relationship_info>` = 

{
    "name": "<relationship_name>",
    "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],
    "is_foreign_service": [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]",
    "is_foreign_junction_service": [true|false],
    "junction_service": <foreign_service_name>,
    "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](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.
  • `label`: String. Optional. See #labels section.
  • `plural`: String. Optional. See #labels section.
  • `description`: String. Optional. See #labels section.
  • `is_virtual`: Boolean. Optional. Set to true if the relationship is due to arbitrary foreign keys, see 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_id`: Integer. When `is_foreign_service` is true, 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.
  • `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="aliases"></a>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`.

=== <a name="labels"></a>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.

=== <a name="types"></a>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.
  • 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.


<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... 

{
  "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. "[email protected]”. 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.

<a name="virtual_fields"></a>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 name="db_function"></a>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.

Database functions are configured in the following format... 

{
  "db_function":
    {
        "function": "<database_specific_function_call>"
        "type": "[string,integer,double,float]",
        "aggregate": [true,false]
    }
}

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.
   * 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.

<a name="virtual_relationships"></a>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"
}

<a name="examples"></a>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": "virtual",
        "db_function": {
            "function": "MAX(salary)",
            "type": "double",
            "aggregate": true
        }
    }.
    {
        "description": "A virtual field returning the concatenation of two other columns in the table.",
        "name": "full_name",
        "type": "virtual",
        "db_function": {
            "function": "Concat(first_name, ' ', last_name)",
            "type": "string",
            "aggregate": false
        }
    }
}

<a name="operations"></a>Schema Operations

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

<a name="retrieving"></a>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.

<a name="list"></a>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 >GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema/ HTTP/1.1

Response 

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

<a name="multiple-tables"></a>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 > GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema?names=account%2Ccontact HTTP/1.1

Response 

{
  "table": [
    {
      "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"
      ]
    }
  ]
}

<a name="single-table"></a>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 >GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema/account HTTP/1.1

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"
    ]
}

<a name="single-field"></a>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_name>`

Request >GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema/account/name HTTP/1.1

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": []
}

<a name="creating"></a>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 `table` element.

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

Request > POST http://demo.cloud.dreamfactory.com/api/v2/db/_schema HTTP/1.1 

{
  "table": [
    {
      "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 

{
   "table": [
     {
       "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 `table` wrapper, like above.

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

Request > POST http://demo.cloud.dreamfactory.com/api/v2/db/_schema/todo HTTP/1.1 

{
  "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_name>`

Request > POST http://demo.cloud.dreamfactory.com/api/v2/db/_schema/todo/created_date HTTP/1.1 

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

Response 

{
  "name": "created_date"
}

<a name="replacing"></a>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 

{
    "table": [
        {
            "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 

{
    "table": [
        {
            "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_name>`

Request 

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

Response 

{
  "name": "contact"
}

<a name="merging"></a>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 

{
    "table": [
        {
            "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 

{
    "table": [
        {
            "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_name>`

Request 

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

Response 

{
  "name": "contact"
}

<a name="deleting"></a>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_name>`

Retrieved from "https://wiki.dreamfactory.com/index.php?title=DreamFactory/Features/Database/Schema&oldid=31450"