Schema

Easydb lets users define their own data model using a definition called “user schema”. Easydb also uses internally a schema definition for the base data model (users, groups, pools, etc.) which is called “base schema”.

A schema is composed of several tables, each one representing an object type.

The schema can be provided/retrieved in JSON and XML format. Below is a description of the JSON format.

Attributes

Table definition

Hierarchical objecttypes have a parent of their same type. For instance, an objecttype “location” would be a good candidate for a hierarchical object, since we can have something like:

• Europe
  • Germany
    • Berlin
  • UK

Hierarchical objecttypes have an auto-generated column called “_id_parent” which behaves like a nullable link to the same objecttype (see Object for more details).

If an objecttype has pool_link set to true its objects must be in a pool. The objecttype has an auto-generated column called “_pool” which is a link to a Pool (see Object for more details).

The attribute owned_by is used to mark private tables that are used in link columns (see below).

Column definition

Columns can be regular columns or links. They are classified using the attribute “kind”.

Regular columns:

Represents a field in the objecttype that holds a single value. The column type defines the data type of the field.

The column types are:

• integer: integer
• serial: auto-incremented integer
• decimal.2: decimal number with 2-digit precision
• boolean: boolean (true/false)
• text: text
• text_oneline: text that should be represented in one line
• text_l10n: localized text
• text_l10n_oneline: localized text (one line)
• string: string
• date: date
• date_range: a range of dates (from-to, inclusive)
• date_time: date and time
• eas: an EAS asset
• link: link to another table in the schema. The actual details are defined in the foreign_keys (see above)

The difference between “text” and “string” has to do with /api/search and /api/suggest. Texts are considered as lists of words that can be matched at the beginning of each word. Strings are considered as a block of characters that can be matched at any position. An example of text could be a book title, whereas a good candidate for a string would be a book signature.

The indication “one_line” does not have any consequence on how the data is stored; it is just an indication about the representation in the frontend. Localized columns are columns that have a value for each of the languages defined for the backend (see L10n).

Link columns:

Link columns are repetition groups of a table inside the main table. For example, a “painting” may have one or more “artists”. Additional fields can be specified for the relation (for example: “role” and “comment”). If the kind of the column is “link”, the nested table is private to the main table. If the kind is “reverse_link”, the referenced table is another main table, and can be edited from within this table.

Private tables are marked by the attribute owned_by (see table definition).

Reverse links have to be declared in the referenced table using the attribute reverse_edit (see column definition).

In a typical scenario, like the one above with the painting and the artists, all tables are created at once, with a single schema update. In that case, the nested table does not have an ID, but can be referenced by name. This is what the attribute other_table_name_hint is for.

Primary key

Foreign key

The columns array contains the local columns that compose the foreign key. The columns array inside the referenced_table contains the foreign columns being referenced in the same order.

The valid values for foreign key operations are:

• cascade: propagate operation to referenced tables
• restrict: prevent deletion of a referenced row
• set null: set referencing column to null
• set default: set referencing column to its default value

Unique key

Column reference

The attribute column_name_hint is useful when the ID of the column is not yet known. For example, when creating a new table with a primary key constraint.

auto_column_primary_key, auto_column_uplink, auto_column_parent and normal column references (column_id/column_name_hint) are mutually exclusive.

Table reference

Table and column reference

Omni-directional definitions

It is possible to declare omni-directional relationships between columns for a table. Omni-directional relationships are composed typically of two link columns (but they may be more), so that when a relationship between two objects is established in one direction, it also appears in the other(s). For example, to map a relationship “follows” between two users, you may define something like:

{  // many attributes are ommited
    "tables": [
        {
            "name": "user",
            "columns": [
                { "kind": "column", "name": "id", "type": "serial", "not_null": true },
                { "kind": "link", "other_table_name_hint": "user__follows" }
            ]
        },
        {
            "name": "user__follows",
            "columns": [
                { "kind": "column", "type": "link", "name": "follower" },
                { "kind": "column", "type": "link", "name": "followed" },
                { "kind": "column", "type": "date", "name": "since" }
            ],
            "foreign_keys": [
                // foreign keys to "user"
            ],
            "omni_directional": [
                {
                    "columns": [
                        { "column_name_hint": "follower" },
                        { "column_name_hint": "followed" }
                    ]
                }
            ]
        }
    ]
}

Now you can get user 1 and update it so that it follows user 2. If you get user 2, you will also see the corresponding entry.

Related operations

• /schema: manipulate user schema