testtables.sql

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(128)                NOT NULL DEFAULT '',
    firstname    VARCHAR(128)                NOT NULL DEFAULT '',
    pathFileName VARCHAR(128)                NOT NULL DEFAULT '',
    gender       ENUM ('', 'male', 'female') NOT NULL DEFAULT 'male',
    groups       SET ('', 'a', 'b', 'c')     NOT NULL DEFAULT '',
    birthday     DATE                        NOT NULL DEFAULT '0000-00-00',
    noteId       BIGINT                      NOT NULL DEFAULT 0,
    modified     TIMESTAMP                   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created      DATETIME                    NOT NULL DEFAULT '0000-00-00 00:00:00'
);

#
# Create Demo
INSERT INTO Person (id, name, firstname, gender, groups)
VALUES (NULL, 'Doe', 'John', 'male', 'c'),
       (NULL, 'Smith', 'Jane', 'female', 'a,c');


DROP TABLE IF EXISTS PersFunction;
CREATE TABLE PersFunction
(
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    personId BIGINT             DEFAULT '0',
    type     ENUM ('Student', 'Assistant', 'Professor', 'Administration'),
    start    DATE      NOT NULL DEFAULT '0000-00-00',
    end      DATE      NOT NULL DEFAULT '0000-00-00',
    note     VARCHAR(255),
    modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created  DATETIME  NOT NULL DEFAULT '0000-00-00 00:00:00'
);

# ---------------------------
# Form: plain
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (3, 'formplain', 'Form: Plain', '', 'Form', 'always', 'always', 'plain', '', '');

# FormEditor: FormElements
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs,
                          value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (300, 3, 'id', 'id', 'readonly', 'text', 'all', 'native', 100, 0, 11, '', '', '', '', '', '', 0, '', ''),
       (310, 3, 'name', 'Name', 'show', 'text', 'all', 'native', 120, 0, 255, '', '', '', '', '', '', 0, '', '');

# Form: table
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (4, 'formtable', 'Form: Table', '', 'Form', 'always', 'always', 'table', '', '');

# FormEditor: FormElements
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs,
                          value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (400, 4, 'id', 'id', 'readonly', 'text', 'all', 'native', 100, 0, 11, '', '', '', '', '', '', 0, '', ''),
       (410, 4, 'name', 'Name', 'show', 'text', 'all', 'native', 120, 0, 255, '', '', '', '', '', '', 0, '', '');


# Form: Person
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (5, 'person', 'Person {{SELECT ": ", firstName, " ", name, " (", id, ")" FROM Person WHERE id = {{r:S0}}}}',
        'Please secure the form',
        'Person', 'always', 'always', 'bootstrap', '', '');

# FormEditor: FormElements person
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs,
                          value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (500, 5, 'name', 'Name', 'show', 'text', 'all', 'native', 10, 0, 255, '', '', '', '', '', '', 0, '', ''),
       (501, 5, 'firstname', 'Firstname', 'show', 'text', 'all', 'native', 20, 0, 255, '', '', '', '', '', '', 0, '',
        ''),
       (502, 5, 'birthday', 'Birthday', 'show', 'date', 'all', 'native', 30, 0, 255, '', '', '', '', '', '', 0, '', ''),
       (506, 5, 'gender', 'Sex', 'show', 'radio', 'alnumx', 'native', 40, 0, 0, '', '', '', '', '',
        'itemList=female,male',
        0, '', ''),
       (503, 5, 'datumZeit', 'Datum & Zeit', 'show', 'datetime', 'alnumx', 'native', 50, 0, 0, '', '', '', '', '', '',
        0,
        '', ''),
       (504, 5, 'zeit', 'Zeit', 'show', 'time', 'alnumx', 'native', 60, 0, 0, '', '', '', '', '', '', 0, '', ''),
       (505, 5, 'pathFileName', 'Picture', 'show', 'upload', 'allbut', 'native', 70, 0, 0, '', '', '', '', '',
        'fileDestination={{SELECT ''fileadmin/user/pictures/'', p.name, ''-{{_filename}}'' FROM Person AS p WHERE p.id={{r}} }}',
        0, '', ''),

       (506, 5, '', 'Address', 'show', 'subrecord', 'all', 'native', 100, 0, 0, '', '', '',
        '{{!SELECT a.id, a.street, a.city, a.country FROM Address AS a WHERE a.personId={{r:S0}} }}',
        '', 'form=address\ndetail=id:personId', 0, 'new,edit,delete', '');


# Form: Address
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (6, 'address',
        'Person {{SELECT ": ", p.firstName, " ", p.name, " (", id, ")" FROM Person AS p WHERE p.id = {{personId:S0}}}}',
        '',
        'Address', 'always', 'always', 'bootstrap', '', '');

# FormEditor: FormElements address
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs,
                          value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (600, 6, 'street', 'Street', 'show', 'text', 'all', 'native', 10, 0, 255, '', '', '', '', '', '', 0, '', ''),
       (601, 6, 'city', 'City', 'show', 'text', 'all', 'native', 20, 0, 255, '', '', '', '', '', '', 0, '', ''),
       (602, 6, 'country', 'Country', 'show', 'select', 'all', 'native', 30, 0, 255, '', '', '', '', '', '', 0, '', '');


# ----------------------------------------------------------------------
#
DROP TABLE IF EXISTS Address;
CREATE TABLE Address
(
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    personId BIGINT                                               NOT NULL DEFAULT 0,
    street   VARCHAR(128)                                         NOT NULL DEFAULT '',
    city     VARCHAR(128)                                         NOT NULL DEFAULT '',
    country  ENUM ('Switzerland', 'Austria', 'France', 'Germany') NOT NULL,
    grIdTyp  BIGINT                                               NOT NULL DEFAULT 0,
    modified TIMESTAMP                                            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created  DATETIME                                             NOT NULL DEFAULT '0000-00-00 00:00:00'
);

INSERT INTO Address (personId, street, city, country)
VALUES (1, 'Side Street', 'Zurich', 'Switzerland'),
       (1, 'Park Street', 'Wien', 'Austria'),
       (1, 'Winter Street', 'Paris', 'France'),
       (2, 'Summer Street', 'Berlin', 'Germany');

#------------------------------------------------------------------------
#
DROP TABLE IF EXISTS Note;
CREATE TABLE Note
(
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    note     VARCHAR(128) NOT NULL DEFAULT '',
    xId      BIGINT       NOT NULL DEFAULT 0,
    grIdTyp  BIGINT       NOT NULL DEFAULT 0,
    modified TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created  DATETIME     NOT NULL DEFAULT '0000-00-00 00:00:00'
);