Data Types

This is a comprehensive list of all supported data types, their parameters and the related MySQL-types.

Text

type: text

A single text line, no further parameters. Related MySQL-types:

  • CHAR
  • VARCHAR (recommended)
  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

Multiline

type: multiline

A multi text line allowing linebreaks, no further parameters. Related MySQL-types:

  • CHAR
  • VARCHAR
  • TINYTEXT
  • TEXT (recommended)
  • MEDIUMTEXT
  • LONGTEXT

If the field is shown in the list view and the value is longer than 27 characters, the rest is cut and replaced with three dots. The full text is still available as tooltip in the list view though. Example with 50 characters:

“Lorem ipsum dolor sit amet, consetetur sadipscing”

Would be shown in the list view as:

“Lorem ipsum dolor sit amet,…”

WYSIWYM

type: multiline

A visual WYSIWYM (what you see is what you mean) editor producing HTML. Related MySQL-types:

  • CHAR
  • VARCHAR
  • TINYTEXT
  • TEXT (recommended)
  • MEDIUMTEXT
  • LONGTEXT

Pay attention that the database type can store all content. Else, the HTML might be just cut and broken.

Url

type: url

A single text line representing an URL, no further parameters. Related MySQL-types:

  • CHAR
  • VARCHAR (recommended)
  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

The only difference to the type “text” is that url fields are clickable in the list and show view. They are shortened in the list view to their base name in order to save space. A value of:

http://www.foo.com/bar.txt

would lead to:

http://www.foo.com/bar.txt

on click and is shortened to “bar.txt” in the list.

Integer

type: integer

An integer, no further parameters. Related MySQL-types:

  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • INT (recommended)
  • BIGINT

Float

type: float
floatStep: 0.1

An float. Related MySQL-types:

  • FLOAT (recommended)
  • DECIMAL
  • DOUBLE (recommended)
  • REAL

The parameter “floatStep” is to set the step size in the form field.

Boolean

type: boolean

A boolean value, either true or false, no further parameters. Related MySQL-type:

  • TINYINT

Saved as 0 (false) or 1 (true).

Date

type: date

A date value without time, no further parameters. Related MySQL-types:

  • DATE
  • DATETIME (recommended)
  • TIMESTAMP

Datetime

type: datetime

A date value with time, no further parameters. Related MySQL-type:

  • DATETIME (recommended)
  • TIMESTAMP

Set

type: set
items: [red, green, blue]

A fixed set of elements to be chosen from, stored as text. Related MySQL-types:

  • CHAR
  • VARCHAR (recommended)
  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

In this example, the user has the choice between the three colors “red”, “green” and “blue”.

Reference

type: reference
reference:
  nameField: otherName
  entity: otherEntity
  hideId: false

This is the 1-side of a one-to-many relation. Related MySQL-type:

  • INT

In order to display a proper selection UI and represent the the value from the other table, a few more fields are needed. Those are the nameField describing which field to use from the other table to display the selected value and last, the referenced entity.

The nameField is optional. If it is not given, only the id of the referenced entity is shown.

The optional parameter hideId allows to display the reference without its id, if set to true.

Think about a book in a library. The library is stored in the table “library” and has a field “name”. A book belongs to a library, so it has an integer field “library” referencing ids of libraries. Here is the needed yml for this book-library relationship:

library:
    table: lib
    label: Library
    fields:
        name:
            type: text
book:
    table: book
    label: Book
    fields:
        title:
            type: text
        author:
            type: text
        library:
            type: reference
            reference:
              nameField: name
              entity: library

Show Children

If you want to show the children (books in this case) on the details page of the parent (library), you can activate it via the childrenLabelFields:

library:
    table: lib
    label: Library
    childrenLabelFields:
        book: title
    fields:
        name:
            type: text
book:
    table: book
    label: Book
    fields:
        title:
            type: text
        author:
            type: text
        library:
            type: reference
            reference:
              nameField: name
              entity: library

On a details page of a library, all of its books are now displayed by their title field. If a library had more children and their label fields are not defined, it falls back to the id field.

Cascading Children Deletion

The default setup is, that referenced entities can’t be deleted until their children are deleted. In this case, a library can’t be deleted until all of its books are gone. You can force children deletion by using the deleteCascade setting like this:

library:
    table: lib
    label: Library
    childrenLabelFields:
        book: title
    deleteCascade: true
    fields:
        name:
            type: text
book:
    table: book
    label: Book
    fields:
        title:
            type: text
        author:
            type: text
        library:
            type: reference
            reference:
              nameField: name
              entity: library

MySQL Foreign Key Hint

Don’t forget to set the MySQL foreign key.

ALTER TABLE `book`
ADD CONSTRAINT `book_ibfk_1` FOREIGN KEY (`library`) REFERENCES `lib` (`id`);

Many

libraryBook:
    type: many
    many:
        entity: book
        nameField: title
        thisField: library
        thatField: book
        hideId: false

A many-to-many relation. For MySQL, the field key is the name of the cross table. So the sample above translates to this structure:

CREATE TABLE `libraryBook` (
  `library` int(11) NOT NULL,
  `book` int(11) NOT NULL,
  KEY `library` (`library`),
  KEY `book` (`book`),
  CONSTRAINT `librarybook_ibfk_1` FOREIGN KEY (`library`) REFERENCES `library` (`id`),
  CONSTRAINT `librarybook_ibfk_2` FOREIGN KEY (`book`) REFERENCES `book` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The fields of the many key have the following meaning:

  • entity: the other entity
  • nameField: the representing field on the other entity for button labels, selection etc.; this key is optional, if it is not given, only the id will be shown
  • thisField: the field of the cross table referencing the entity with the many field
  • thatField: the field of the cross table referencing the other entity named with the entity field of the many key
  • hideId (optional) allows to display the reference without its id, if set to true.

Think about a library having many books and a book being in many libraries. The library is stored in the table “library” and has a field “name”. Here is the needed yml for this book-library many-to-many relationship:

library:
    table: lib
    label: Library
    fields:
        name:
            type: text
        libraryBook:
            type: many
            many:
                entity: book
                nameField: title
                thisField: library
                thatField: book
book:
    table: book
    label: Book
    fields:
        title:
            type: text
        author:
            type: text

Attention: In the list view, it is not possible to sort by many fields as it doesn’t make that much sense.

File

CRUDlex supports the handling of files. They get uploaded with the create or edit form, can be viewed, removed and replaced.

To have an image field for our library, you would declare it like this:

library:
    table: lib
    label: Library
    fields:
        image:
            type: file
            path: uploads

The images are stored in the filesystem relative to your index.php within the subfolder you give with the path parameter.

If you edit an entity with a file and re-upload it or if you delete the file or if you delete the entity, the current implementation is defensive and doesn’t physically delete the files.

See the File Handling chapter for more details.

Related MySQL-types:

  • CHAR
  • VARCHAR (recommended)
  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

Fixed

type: fixed
value: abc

Fills the db always with the defined, fixed value, not editable. Related MySQL-types:

  • CHAR
  • VARCHAR (recommended)
  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT
  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • INT
  • BIGINT