ELEMENT table (description?, (grant* | primarykey | foreign* | trigger* |
                 constraint* | declaration | index)*)
  ATTRIBUTE
       name        string
       title       string
       readonly    bool
       inherits    string
  

Attributes

Description

A table is a set of column definition plus properties, which all columns of the table have in common like: triggers and indexes.

Implementation of inheritance

Table-Inheritance means the following: a source table FooBar extends the structure and data of a target table Bar with Foo elements. All rows in FooBar have any columns defined in Bar + all columns of FooBar. Bar remains unchanged for that. If the structure of Bar changes, the structure of FooBar changes as well. Each element of FooBar may also be interpreted as an element of Bar, but not vice versa.
Technically spoken: the primary key of the source table is a foreign key that points to the primary key of the source table.
This is unlike PostgreSQL where you may define multiple-inheritance with n parent tables - with all consequences and issues linked to such behavior.

  ELEMENT grant EMPTY
  ATTRIBUTE
       role        string
       user        string
       level       integer
       select      bool
       insert      bool
       update      bool
       delete      bool
       grant       bool
  

Attributes

Description

The rights management provides 3 layers, each of which is optional in this document. An implementation must however implement at least one of these options.

User groups

like Sales, Human Resources

User roles

like Project Manager

Security level

an integer of 0 through 100

In analogy to databases, there is also a grant option, which allows users to temporarily grant any right they own in person, to any other user. So a manager may grant (and later revoke) all his rights to an assistant while he is on vacation.

Beispiele

You may decide that every manager of Human Resources, who has at least a security level of 50 may create a new employee and view salaries, but that it requires a manager of HR with security level 80 to update them.

You may even skip any of the levels to perhaps allow anybody to view a catalog form, who has at least a security level of 1, or grant access for any member of the sales department to sales data.

You may precisely choose what each member may or may not do: select (view), insert (create), update (edit), delete.

Note that you may have multiple grant elements to define several alternatives. For example, users may either be a member of group sales OR a have the role of a company manager to view and edit sales information.

Implementation

If no grant element is present, the element is supposed to be public. This means, no security checking is done. If at least 1 grant element exists, no user is allowed to carry out any operation unless there is an explicit grant that allows him to do so.

The Yana Framework implements a profile system on top of all that as well. Application profiles may define different subsidiaries inside your company. For example, Europe or Asia. If implementations should support the management of multiple independent instances on the same installation, it is recommended to implement comparable systematics.

  ELEMENT primarykey (#PCDATA)
  

Description

The primary key is the name of a single column inside the table that has unique values and will be used to identify each row within the table.

Implementation

The software must check if the specified column exists. If it doesn't, it must throw an error.
Each table must have exactly 1 primary key. Compound primary keys are not supported.

  ELEMENT declaration (array | bool | date | enum | file | float | html |
                       image | inet | integer | list | mail | password |
                       reference | set | string | text | time | timestamp |
                       url)*
  

Description

The declaration element is a simple container that may contain a number of column definitions.

  ELEMENT trigger (#PCDATA)
  ATTRIBUTE
       name        string
       dbms        string
       on          string
       insert      bool
       update      bool
       delete      bool
  

Attributes

Description

Triggers are a database feature that allows to execute code on certain database events. They fire on insert, update, or delete statements, or a combination of those. Triggers are fired either before, after or instead of a statement. When triggered, the given code is executed by the database.

The implementation however depends heavily on the chosen DBMS. Not all DBMS support all features. For example: not all DBMS support a trigger to be executed "instead" of a statement, thus replacing it.

Note that a trigger implies a user defined function. Some DBMS require thus that the function is explicitly created and only the name of the function must be specified for the trigger. Other DBMS allow that you specify the function body along with the trigger.

Implementation

If a trigger uses a functionality which is not supported by the chosen DBMS, the DBMS will throw an error when trying to create the trigger. The implementation itself does not need to check that.

Triggers, that use the DBMS-type "generic", must be emulated. In that case the XDDL-compliant software must execute the code instead of the database. The implementation itself may define the required syntax of the code. In such case it is recommended, that the attribute code for such a trigger is limited to be a valid callback (function- or method names). The Yana Framework requires the code attribute of emulated triggers to be a valid callback of a user-defined PHP-function. See the manual of the database API for more details on that topic.

  ELEMENT constraint (#PCDATA)
  ATTRIBUTE
       name        string
       dbms        string
  

Attributes

Description

A constraint is a boolean expression that must evaluate to true at all times for the row to be valid. The database should ensure that. For databases that don't have that feature, the DBMS-type "generic" may be used to simulate this.

Implementation

If a constraint uses the DBMS-type "generic", and the XDDL-implementation can't ensure, that the database supports that feature, it must validate the constraint instead of the database. The implementation itself may define the required syntax of the code. On simulation column-level constraints may be validated equivalent to table-level constraints. The Yana Framework uses PHP as language for the definition of generic constraints. It provides an associative array $ROW, that contains copies of the values of the current row. The keys of this array are the lowercased column names.

  ELEMENT bool (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       notnull         bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Bool may have two values: true and false.

Implementation

Boolean values are not supported by all DBMS but may be easily simulated using integer types.

Presentation

Input fields of type boolean are presented as checkboxes on edit. When viewing the column, a graphic indicates the state of the field.

  ELEMENT default (#PCDATA)
  ATTRIBUTE
       dbms            string
  

Attributes

Description

In newly inserted rows, columns are set to the default value automatically, if no other input is provided.

Implementation

The type of the value depends on the type of column. The physical default value also depends on the DBMS.
A good example is data type Boolean, which is natively supported by PostgreSQL and thus the physical default value would be true or false. For MySQL it is stored as TinyInt with the default values 1 or 0.
However the DBMS-independent (generic) default values would be true or false. The implementation must convert these values automatically.

  ELEMENT integer (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       autoincrement   bool
       unsigned        bool
       fixed           bool
       length          integer
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Integer may contain any whole number, which can be displayed by the database and programming language.

Implementation

The upper and lower boundaries for integer values depend on the type of system. In general: on 32-bit systems the displayable numbers are in the range [-2^31, +2^31]. For 64-bit systems numbers may be bigger [-2^63, +2^63]. But only if all your software supports 64-bit integer values.

Note! 64-bit and 32-bit applications may be incompatible. Especially when using a 64-bit database server with a 32-bit application or vice versa. Be warned that a number overflow or underflow may occur when converting a large 64-bit to a small 32-bit number. Note that this applies to dates and times as well!

Presentation

Strings and numbers are displayed as input fields when edited. If the column is editable, the content is displayed as text.

  ELEMENT float (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       unsigned        bool
       length          integer
       precision       integer
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Float may contain any number, which can be displayed by the database and programming language. These may either be whole, or floating point, or fixed point numbers.

Implementation

These may either be whole, or floating point, or fixed point numbers. Note that, for floating point values, "maximum" means the maximum number of digits including fraction. Thus you will loose precision for very large and very small numbers. For fixed point numbers the maximum and minimum numbers are reduced by the number of digits reserved for displaying the fraction.

Note! 64-bit and 32-bit applications may be incompatible. Especially when using a 64-bit database server with a 32-bit application or vice versa. Be warned that a number overflow or underflow may occur when converting a large 64-bit to a small 32-bit number. Note that this applies to dates and times as well!

Presentation

Strings and numbers are displayed as input fields when edited. If the column is editable, the content is displayed as text.

  ELEMENT string (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       length          integer
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type String contain a single line of text.

Implementation

Note that string values must not contain line breaks. These are the characters \f, \r and \n. For security reasons values must not contain the character \#0.

Presentation

Strings and numbers are displayed as input fields when edited. If the column is editable, the content is displayed as text.

  ELEMENT mail (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       length          integer
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Mail may contain any valid e-mail address.

Implementation

Mails are implemented as string values. The syntax of e-mail addresses is specified inRFC 822. For PHP implementations it is recommended to use the following code for validation: filter_var($email, FILTER_VALIDATE_EMAIL) === true. Other implementations may use the following regular expression: [\w\d-_\.]{1,}\@[\w\d-_\.]{2,}\.[\w\d-_\.]{2,}.

Presentation

On edit the column is presented as input field. This is equal to the presentation of strings.

Values of type "mail" should automatically be encoded when shown in a browser, to make data theft more difficult. This applies to all displayed e-mail addresses. The Yana Framework utilizes a filter within the presentation layer to solve this issue. A manual intervention is not necessary.

  ELEMENT url (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       length          integer
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type URL are equivalent to type String, except that every input is checked to be a syntactically correct URL.

Implementation

URLs are implemented as string values. The syntax of URIs is specified in RFC 3986. For PHP implementations it is recommended to use the following code for validation: filter_var($url, FILTER_VALIDATE_URL) === true.

Presentation

Strings and numbers are displayed as input fields when edited. If the column is editable, the content is displayed as text.

  ELEMENT password (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

A column of type Password is used to store encrypted password information.

Implementation

Passwords are hash strings. The values must be calculated from the input using a , like MD5. Passwords must not be stored as clear text.

Presentation

Passwords are not shown as text, but as an input element of type "password".

  ELEMENT inet (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Inet offer the possibility to automatically store the IP address of the visitor.

Implementation

Inet columns are implemented as string values. The datatype must support IPv4 and IPv6. The syntax of IPv6 is specified in RFC 2460.

For PHP implementations it is recommended to use the following code for validation: filter_var($inet, FILTER_VALIDATE_IP) === true. Other implementations may use the following regular expressions:

IPv4:

\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}

IPv6:

[a-f0-9]{1,4}:[a-f0-9]{1,4}:[a-f0-9]{1,4}:[a-f0-9]{1,4}:[a-f0-9]{1,4}:[a-f0-9]{1,4}:[a-f0-9]{1,4}:[a-f0-9]{1,4}

Presentation

As a rule, columns of this type should not be editable. If they are, they use an input box for editing.

  ELEMENT text (description?, grant*, constraint*)
  ATTRIBUTE
       name            string
       length          integer
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Text may contain multiple lines of text. They are unbounded in length.

Implementation

Note that the length may be limited for technical reasons. The physical data type depends on the chosen DBMS. Also the DBMS may disallow you to create an index on a text column, or might create a special full text index with specific properties.

For security reasons values must not contain the character \#0.

Any HTML special characters must be masked. Additionally the implementation may define input and output filters. E.g. these may be used to add emot-icons and/or prevent spam and flooding.

For example, the Yana Framework implements several filters to prevent obvious vandalism.

Presentation

Multi-line texts use textarea fields for editing. If the column is not editable, it's contents are shown as text. If a text is too long, scrollbars are shown (CSS: "overflow: auto").

  ELEMENT html (description?, grant*, constraint*)
  ATTRIBUTE
       name            string
       length          integer
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type HTML may contain multiple lines of hypertext in XHTML format.

Implementation

You should keep in mind that hypertext may contain tags and entities. Determining the true length of such a text may be tricky.

No files (like images) may be attached to or embedded in a HTML value of the column. But the column may contain a tag that refers to a file stored elsewhere.

For security reasons values must not contain the character \#0.

Additionally the implementation may define input and output filters. E.g. to prevent XSS-attacks and vandalism.

Presentation

To edit HTML columns, the implementation may present an inline HTML editor. The properties and behavior of this editor are not specified. For output, HTML columns must be shown as interpreted hypertext according to the XHTML 1.0 standard or a successor of this standard (e.g. HTML 5).

  ELEMENT date (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Date are used to store a date without time or timezone.

Implementation

The physical data type depends on the chosen DBMS. For DBMS which do not support such a type, it may be simulated using an integer.

Presentation

When editing select boxes may be shown to ease the input.

Values are presented as text. The presentation may depend on the chosen language.

  ELEMENT time (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Time are used to store dates with time and timezone.

Implementation

The physical data type depends on the chosen DBMS. For DBMS which do not support such a type, it may be simulated using an integer.

Presentation

When editing select boxes may be shown to ease the input.

Values are presented as text. The presentation may depend on the chosen language and timezone.

  ELEMENT timestamp (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Timestamp are used to store dates with time in UTC.

This type is identical to Time, except for the technical implementation.

Implementation

The physical data type depends on the chosen DBMS. Usually it is stored and returned as an integer.

Note that there may be issues with negative timestamps on some OS, like older Win32 systems.

Be warned not to mix the value 0 (1.1.1970) with NULL (undefined).

Presentation

When editing select boxes may be shown to ease the input.

Values are presented as text. The presentation may depend on the chosen language and timezone.

  ELEMENT enum (description?, grant*, constraint*, default*, option+)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

The data type Enum is an enumeration type. The list of valid values can be defined using option elements.

Note that the default value (if provided) must be a valid enumeration option.

Implementation

Enumerations are stored as strings. The value is equivalent to the value of the chosen option.

Presentation

When editing such column, a select box is shown. Alternatively radio buttons may be used. The elements equal to the option elements of the tag.

  ELEMENT option (#PCDATA)
  ATTRIBUTE
       value        string
  

Attributes

Description

An option represents one valid element of an enumeration. It has a textual description (pcdata) and a value. Only the value of the target column is stored in the database. The description (pcdata) is only shown in UI (instead of the value).

The description represents the option as a human readable text. Note that this may also be a language token, which is to be translated to the selected language.

If an invalid or undefined enumeration item is found in the database, the UI must show the stored value without conversion. The implementation may additionally throw an error message.

  ELEMENT set (description?, grant*, constraint*, default*, option+)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

The data type Set is equivalent to the type Enum, but permits the selection of multiple values.

Note that the default value (if provided) must be a valid enumeration option.

Implementation

The physical data type depends on the chosen DBMS. Some DBMS have native support. Where available, the native type should be used. Otherwise the data may be stored as a comma-separated string. The values are equivalent to the values of the chosen options.

Presentation

When editing such column, a list of check boxes is presented. Alternatively a select box may be used, that allows the selection of multiple options. In both cases the values and labels equal the option elements of the tag.

  ELEMENT list (description?, grant*, constraint*)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

A column of type List is a numeric array of strings.

Implementation

PostgreSQL has native support for arrays. For other DBMS this feature must be simulated. It may be implemented by storing the values as a serialized or comma-seperated string. The string should be deserialized when loaded and returned as an array.

Presentation

On edit lists are presented as lists of input fields. Additionally a control element is shown to remove or add new entries.

If the element is not editable, then the elements are enumerated as a list.

The displayed list items may be numerated.

  ELEMENT array (description?, grant*, constraint*)
  ATTRIBUTE
       name            string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

A column of type Array is a (possibly multidimensional) array of strings.

Implementation

For most DBMS this feature must be simulated. It may be implemented by storing the values as a serialized or comma-separated string. The string should be deserialized when loaded and returned as an array. In that case it is recommended to encode the string using JSON.

Presentation

On edit arrays are presented as key-value pairs. Additionally a control element is shown to remove or add new entries.

If the element is not editable, then the elements are enumerated as a multidimensional list. Keys and values are optically separated from each other. The presentation may be implementation as a foldable tree menu.

  ELEMENT file (description?, grant*, constraint*)
  ATTRIBUTE
       name            string
       notnull         bool
       maxsize         integer
       readonly        bool
       title           string
  

Attributes

Description

The data type "file" is used to save files ("binary large objects").

Implementation

The files should remain in the file system for better performance. In order to save disk space, a compression (like GZip) may be used. This compression also ensures that files stores on the server are not executable and a potential attacker can't misuse such upload fields to store malicious code on the server.

For security reasons the file should be stored in a place, where it is not directly accessible for a client. When you download the file should be unpacked automatically, so the user no disadvantages from the experiences compression and decompression not installed. To provide a smaller download size, the file may automatically be send as compressed data stream, if the user's browser supports this feature. The browser unpacks the file independently. A manual intervention is not necessary.

Presentation

On edit, an upload field is shown to upload a new file, and a button to download the current one. The implementation may offer a preview for files, whose Mime-type is known.

  ELEMENT image (description?, grant*, constraint*)
  ATTRIBUTE
       name            string
       notnull         bool
       width           integer
       height          integer
       ratio           bool
       background      string
       maxsize         integer
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Image are files (binary large objects). These must be graphics of a supported type and should be displayable on the user interface as an image.

Implementation

The graphics file must automatically be checked and converted during upload. If the given file is not a valid graphic, the implementation must throw an error message. When the image is resized, the implementation must respect the attributes "width", "height", "ratio" and "background". The dimensions of the image are adapted to the given height and width. If the attribute "ratio" is set to "no" and width and height are given, the image dimensions are asymmetrically stretched to the exact given values. Otherwise the width and/or height is changed, but the aspect ratio of the image is kept intact. If these changes result in a part of the canvas being empty, then this part is to be filled with the given background color. The background color is given by the attribute "background". If no background color is defined, the implementation may chose a color.

Note: not all image files are suited to be shown in a browser. For example, many browsers (and other programs) may have problems viewing images that use a CMYK color palette.

For reasons of performance, image files should be stored outside the database. Compressing bitmap graphics usually doesn't have any benefits and should be avoided.

A list of valid image formats and/or rules for their interpretation are not specified in this document. The treatment of vector graphics is not specified in this document.

Presentation

Columns of the data type Image are presented as a thumbnail image plus an upload field to insert or replace the stored graphic. When clicking the thumbnail the complete graphic should be shown. For the creation of the thumbnail, the implementation should also respect the attributes ratio and background.

  ELEMENT reference (description?, grant*, constraint*, default*)
  ATTRIBUTE
       name            string
       table           string
       column          string
       label           string
       notnull         bool
       unique          bool
       readonly        bool
       title           string
  

Attributes

Description

Columns of type Reference are used to represent foreign keys. The real type of the column depends on the type of the target column.

Implementation

A reference itself does not automatically imply a foreign key constraint.

Only the value of the target column is stored in the database. The physical type and properties of the column are thus inherited from those of the target column. If the physical type of the target column changes, the physical type of the reference column must change too. If the target column has no suitable type, the implementation must throw an error.

Presentation

When editing such column, a select box is shown. The options are filled with the entries of the referenced table. The labels are taken form the column "label" and the values are taken from the target "column" of the target table.

  ELEMENT foreign (key+)
  ATTRIBUTE
       name            string
       table           string
       match           string
       ondelete        string
       onupdate        string
       deferrable      bool
  

Attributes

Description

Foreign-key constraints are meant to ensure referential integrity between tables. This feature is not supported by all DBMS. The implementation my emulate that feature.

Each foreign-key consists at least of a source and a target. Note that the types of the source columns of a foreign-key depend on the types of the target columns. Columns containing a foreign-key should thus be defined as type "reference". If so, the implementation must determine the correct type automatically.

The attributes "ondelete" and "onupdate" define, how the DBMS should react when an reference is updated.

no-action

The reference in the child table may not be set to a value, that has no corresponding row in the parent table.

restrict

The value of a key in the parent table may not be changed, if at least one reference to it still exists.

cascade

If the key in the parent table is deleted or updated, all references to it in the child table are also updated or deleted.

set-null

If the key in the parent table is updated, the value of the references will be set to NULL.

set-default

If the key in the parent table is updated, the value of the references will be reset to the default value.

The attribute "match" defines, how the DBMS should evaluate the given rules for referential integrity. This applies to compound foreign-keys including multiple columns only.

full

All columns must match

partial

At least one column must match

simple

Any column that has a value must match (some columns may be null)

Implementation

The attribute "deferrable" is not supported by some DBMS. Note that this feature may not be emulated. Various DBMS have different approaches to circumvent this problem. For example, temporarily deactivating constraints. See your manual for details.

The attribute "match" is not supported by some DBMS. This applies to compound foreign-keys including multiple columns only. This feature is rarely used and should be avoided for portable database applications for reasons of compatibility.

Some values of the attributes "onupdate" and "ondelete" are not supported by some DBMS. These may be simulated using triggers.

  ELEMENT key EMPTY
  ATTRIBUTE
       name           string
       column         string
  

Attributes

Description

A column reference including a source and target column. If a foreign-key constraint defines more then one column reference, it is a so-called "compound" foreign-key. Note that compound keys are more complicated to handle and are not supported by all DBMS. Compound keys should be avoided where possible.

  ELEMENT index (description?, column*)
  ATTRIBUTE
       name           string
       unique         bool
       clustered      bool
       title          string
  

Attributes

Description

Indexes are meant to improve the performance of select-statements. An index is a sorted list of column values. Scanning an index is usually faster than scanning a whole table. However: as creating and maintaining an index causes some overhead in calculation time, insert- and update-statements will thus be slower when using an index.

Implementation

Note: it is not required to explicitly define an unique-constraint on a primary key. Primary keys implicitly have an unique-constraint.

Important! Even if an unique index exists, the column is not reported to have an unique-constraint. It is recommended to avoid unique indexes, whenever it is possible to express the same using a constraint. Note that an unique constraint may not be defined using multiple columns. In that case you should use an unique index.

Clustered indexes apply to MSSQL only. A clustered index means, that the DBS should try to store values, which are close to each other in the index, close to each other in the tablespace, so that they fit inside the same memory page, when retrieving data from a table.
Typically a clustered index is created on the primary key (which is the default), or on another column which is used for sorting the table. Each table may only have one clustered index. If the attribute "clustered" of an index is to be set to "yes", the implementation must check, if the table has another clustered index already. If this is the case, the attribute "clustered" of this index must be set to "no". The implementation may throw an error message, if two clustered indexes are found within one table.

  ELEMENT column EMPTY
  ATTRIBUTE
       name           string
       sorting        string
       length         string
  

Attributes

Description

The column list of an index specifies which columns of a table are indexed and how these values are stored.

Implementation

The length attribute is used for performance optimization and is only supported by MySQL. Other DBMS don't support this argument. Note, that the attribute "length" may not be greater than the length of the source column.
The implementation may automatically decide if a full-text index is to be created, if the DBMS supports that feature.