Schema - dfryan/dreamfactory GitHub Wiki

Manage Schema

Schema allows a developer to create tables in DreamFactory utilizing the Local SQL DB service.

Schema DataTypes

Data Type	Description
ID/Primary Key	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.
String	Defines a string field (i.e. varchar or char), defaults to a length of 255, but can be set using the "length" property. Optional properties: "allow_null".
Integer	Defines an integer field. Use "length" to set the displayable length of the integer, i.e. int(11) in MySQL. Optional properties: "allow_null".
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: "allow_null".
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: "allow_null".
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: "allow_null".
Blob	Defines a large binary string field (i.e. MySQL's blob or MSSQL's varbinary[max]), defaults to the largest length binary string allowed by the underlying database. Optional properties: "allow_null".
Float	Defines a standard float 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: "allow_null".
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: "allow_null.
Datetime	A datetime field. Optional properties: "allow_null".
Date	A date field. Optional properties: "allow_null".
Time	A time field. Optional properties: "allow_null".

Import Schema

The other option for importing data, is to import schema from JSON data. Below is an example of a valid JSON file for creating schema. To import JSON data:

1. Access the Admin Console by clicking the gear icon in the upper right.
2. Select Schema from the left-side menu.
3. Click the Import JSON Schema button.
4. Next, paste your JSON schema into the input box and click the Validate button.
5. To import your JSON file, click the Create Table(s) button.

{
  "Contacts": {
    "name": "Contacts",
    "fields": [
      {
        "name": "contactId",
        "type": "int",
        "autoIncrement": true,
        "header": "ID",
        "width": 50,
        "editor": {
          "xtype": "hiddenfield"
        }
      },
      {
        "name": "firstName",
        "size": 64,
        "header": "First Name",
        "required": true,
        "editor": {
          "xtype": "textfield",
          "anchor": "100%"
        }
      },
      {
        "name": "lastName",
        "size": 64,
        "header": "Last Name",
        "required": true,
        "editor": {
          "xtype": "textfield",
          "anchor": "100%"
        }
      },
      {
        "name": "imageUrl",
        "header": "Image URL",
        "size": 1024,
        "editor": {
          "xtype": "textfield",
          "anchor": "100%"
        }
      },
      {
        "name": "twitter",
        "header": "Twitter",
        "size": 1024,
        "editor": {
          "xtype": "textfield",
          "anchor": "100%"
        }
      },
      {
        "name": "skype",
        "header": "Skype",
        "size": 1024,
        "editor": {
          "xtype": "textfield",
          "anchor": "100%"
        }
      },
      {
        "name": "notes",
        "size": 1024,
        "header": "Notes",
        "autoExpand": true,
        "editor": {
          "xtype": "textarea",
          "anchor": "100%"
        }
      }
    ]
  }
}

Manually Create Schema

A developer can manually enter the fields.

To enter fields manually:

1. Access the Admin Console by clicking the gear icon in the upper-right tool bar.
2. Select Schema from the left navigation menu.
3. Click the Create New Table button.
4. Enter the table name you would like to create. You are now ready to create fields for your DB.
5. Enter a file name, then select Field type and length, then click Add Field.
6. Once all of your fields have been entered, click the Save button.

If you would like to add data manually, select Data on the left navigation menu, then select your table by name.