formEditor.sql

# Before MariaDB 10.2.1, 'text' column could not have a 'default' value.
# To not be forced to specify each text column on INSERT() statements, switch off strict checking:
SET sql_mode = "NO_ENGINE_SUBSTITUTION";

CREATE TABLE IF NOT EXISTS `Form`
(
    `id`                       INT(11)                                                                                    NOT NULL AUTO_INCREMENT,
    `name`                     VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `title`                    VARCHAR(1023)                                                                              NOT NULL DEFAULT '',
    `noteInternal`             TEXT                                                                                       NOT NULL,
    `tableName`                VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `primaryKey`               VARCHAR(255)                                                                               NOT NULL DEFAULT '',

    `permitNew`                ENUM ('sip', 'logged_in', 'logged_out', 'always', 'never')                                 NOT NULL DEFAULT 'sip',
    `permitEdit`               ENUM ('sip', 'logged_in', 'logged_out', 'always', 'never')                                 NOT NULL DEFAULT 'sip',
    `restMethod`               SET ('get', 'post', 'put', 'delete')                                                       NOT NULL DEFAULT '',
    `escapeTypeDefault`        VARCHAR(32)                                                                                NOT NULL DEFAULT 'c',
    `render`                   ENUM ('bootstrap', 'table', 'plain')                                                       NOT NULL DEFAULT 'bootstrap',
    `requiredParameterNew`     VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `requiredParameterEdit`    VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `dirtyMode`                ENUM ('exclusive', 'advisory', 'none')                                                     NOT NULL DEFAULT 'exclusive',
    `showButton`               SET ('new', 'delete', 'close', 'save')                                                     NOT NULL DEFAULT 'new,delete,close,save',
    `multiMode`                ENUM ('none', 'horizontal', 'vertical')                                                    NOT NULL DEFAULT 'none',
    `multiSql`                 TEXT                                                                                       NOT NULL,
    `multiDetailForm`          VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `multiDetailFormParameter` VARCHAR(255)                                                                               NOT NULL DEFAULT '',

    `forwardMode`              ENUM ('auto', 'close', 'no', 'url', 'url-skip-history', 'url-sip', 'url-sip-skip-history') NOT NULL DEFAULT 'auto',


    `forwardPage`              VARCHAR(511)                                                                               NOT NULL DEFAULT '',

    `labelAlign`               ENUM ('default', 'left', 'center', 'right')                                                NOT NULL DEFAULT 'default',
    `bsLabelColumns`           VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `bsInputColumns`           VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `bsNoteColumns`            VARCHAR(255)                                                                               NOT NULL DEFAULT '',

    `parameter`                TEXT                                                                                       NOT NULL,
    `parameterLanguageA`       TEXT                                                                                       NOT NULL,
    `parameterLanguageB`       TEXT                                                                                       NOT NULL,
    `parameterLanguageC`       TEXT                                                                                       NOT NULL,
    `parameterLanguageD`       TEXT                                                                                       NOT NULL,
    `recordLockTimeoutSeconds` INT(11)                                                                                    NOT NULL DEFAULT 900,

    `deleted`                  ENUM ('yes', 'no')                                                                         NOT NULL DEFAULT 'no',
    `modified`                 DATETIME                                                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`                  DATETIME                                                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `name` (`name`),
    KEY `name_deleted` (`name`, `deleted`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 1000;


CREATE TABLE IF NOT EXISTS `FormElement`
(
    `id`                INT(11)                                                                    NOT NULL AUTO_INCREMENT,
    `formId`            INT(11)                                                                    NOT NULL,
    `feIdContainer`     INT(11)                                                                    NOT NULL DEFAULT '0',
    `dynamicUpdate`     ENUM ('yes', 'no')                                                         NOT NULL DEFAULT 'no',

    `enabled`           ENUM ('yes', 'no')                                                         NOT NULL DEFAULT 'yes',

    `name`              VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `label`             VARCHAR(1023)                                                              NOT NULL DEFAULT '',

    `mode`              ENUM ('show', 'required', 'readonly', 'hidden')                            NOT NULL DEFAULT 'show',
    `modeSql`           TEXT                                                                       NOT NULL,
    `class`             ENUM ('native', 'action', 'container')                                     NOT NULL DEFAULT 'native',
    `type`              ENUM ('checkbox', 'date', 'datetime', 'dateJQW', 'datetimeJQW', 'extra',
        'gridJQW', 'text', 'editor', 'annotate', 'time', 'note', 'password', 'radio', 'select',
        'subrecord', 'upload', 'imageCut', 'fieldset', 'pill', 'templateGroup', 'beforeLoad',
        'beforeSave', 'beforeInsert', 'beforeUpdate', 'beforeDelete', 'afterLoad', 'afterSave',
        'afterInsert', 'afterUpdate', 'afterDelete', 'sendMail', 'paste')                          NOT NULL DEFAULT 'text',
    `subrecordOption`   SET ('edit', 'delete', 'new')                                              NOT NULL DEFAULT '',
    `encode`            ENUM ('none', 'specialchar')                                               NOT NULL DEFAULT 'specialchar',
    `checkType`         ENUM ('auto', 'alnumx', 'digit', 'numerical', 'email', 'pattern', 'allbut',
        'all')                                                                                     NOT NULL DEFAULT 'auto',
    `checkPattern`      VARCHAR(255)                                                               NOT NULL DEFAULT '',

    `onChange`          VARCHAR(255)                                                               NOT NULL DEFAULT '',

    `ord`               INT(11)                                                                    NOT NULL DEFAULT '0',
    `tabindex`          INT(11)                                                                    NOT NULL DEFAULT '0',

    `size`              VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `maxLength`         VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `labelAlign`        ENUM ('default', 'left', 'center', 'right')                                NOT NULL DEFAULT 'default',
    `bsLabelColumns`    VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `bsInputColumns`    VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `bsNoteColumns`     VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `rowLabelInputNote` SET ('row', 'label', '/label', 'input', '/input', 'note', '/note', '/row') NOT NULL DEFAULT 'row,label,/label,input,/input,note,/note,/row',
    `note`              TEXT                                                                       NOT NULL,
    `adminNote`         TEXT                                                                       NOT NULL,
    `tooltip`            VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `placeholder`        VARCHAR(2048)                                                              NOT NULL DEFAULT '',

    `value`              TEXT                                                                       NOT NULL,
    `sql1`               TEXT                                                                       NOT NULL,
    `parameter`          TEXT                                                                       NOT NULL,
    `parameterLanguageA` TEXT                                                                       NOT NULL,
    `parameterLanguageB` TEXT                                                                       NOT NULL,
    `parameterLanguageC` TEXT                                                                       NOT NULL,
    `parameterLanguageD` TEXT                                                                       NOT NULL,
    `clientJs`           TEXT                                                                       NOT NULL,

    `feGroup`            VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `deleted`            ENUM ('yes', 'no')                                                         NOT NULL DEFAULT 'no',
    `modified`           DATETIME                                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`            DATETIME                                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `formId` (`formId`),
    KEY `formId_class_enabled_deleted` (`formId`, `class`, `enabled`, `deleted`),
    KEY `feIdContainer` (`feIdContainer`),
    KEY `ord` (`ord`),
    KEY `feGroup` (`feGroup`)

)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;


CREATE TABLE IF NOT EXISTS `Dirty`
(
    `id`                   INT(11)                                NOT NULL AUTO_INCREMENT,
    `sip`                  VARCHAR(255)                           NOT NULL,
    `tableName`            VARCHAR(255)                           NOT NULL,
    `recordId`             INT(11)                                NOT NULL,
    `expire`               DATETIME                               NOT NULL,
    `recordHashMd5`        CHAR(32)                               NOT NULL,
    `tabUniqId`            CHAR(32)                               NOT NULL,
    `feUser`               VARCHAR(255)                           NOT NULL,
    `qfqUserSessionCookie` VARCHAR(255)                           NOT NULL,
    `dirtyMode`            ENUM ('exclusive', 'advisory', 'none') NOT NULL DEFAULT 'exclusive',
    `remoteAddress`        VARCHAR(45)                            NOT NULL,
    `modified`             DATETIME                               NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`              DATETIME                               NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `sip` (`sip`),
    KEY `tableName` (`tableName`),
    KEY `recordId` (`recordId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;

# Delete previous FormElements (if exist) of system forms
DELETE FormElement
FROM FormElement,
     Form
WHERE FIND_IN_SET(Form.name, 'form,formElement,copyForm,cron') > 0
  AND Form.id = FormElement.formId;

# Delete previous Forms (if exist)
DELETE
FROM Form
WHERE FIND_IN_SET(Form.name, 'form,formElement,copyForm,cron') > 0;

#
# FormEditor: Form
INSERT INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
VALUES (1, 'form', 'Form Editor: {{SELECT id, " / ", name FROM Form WHERE id = {{r:S0}}}} (DB: {{dbNameQfq:Y}})',
        'FormElement Editor',
        'Form', 'sip', 'sip', 'bootstrap', '', 'maxVisiblePill=5\nclass=container-fluid\ndbIndex={{indexQfq:Y}}');

# FormEditor: FormElements for 'form'
INSERT INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, note, clientJs, value,
                         sql1, parameter, feIdContainer, subrecordOption, modeSql, placeholder)
VALUES (1, 1, 'basic', 'Basic', 'show', 'pill', 'all', 'container', 100, 0, '', '', '', '', '', 0, '', '', ''),
       (2, 1, 'formelement', 'Formelement', 'show', 'pill', 'all', 'container', 200, 0, '', '', '', '', '', 0, '', '',
        ''),
       (3, 1, 'layout', 'Layout', 'show', 'pill', 'all', 'container', 300, 0, '', '', '', '', '', 0, '', '', ''),
       (4, 1, 'access', 'Access', 'show', 'pill', 'all', 'container', 400, 0, '', '', '', '', '', 0, '', '', ''),
       (5, 1, 'multi', 'Multi', 'show', 'pill', 'all', 'container', 500, 0, '', '', '', '', '', 0, '', '', '');

# FormEditor: FormElements for 'form'
INSERT INTO FormElement (formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs, value,
                         sql1, parameter, feIdContainer, subrecordOption, modeSql, placeholder, encode, dynamicUpdate,
                         checkPattern)
VALUES
    # Make the form a 'delete form' for records Form/FormElement.
    (1, '', 'Delete FE', 'show', 'beforeDelete', 'all', 'action', 10, 0, 0, '', '', '', '',
     'sqlAfter={{DELETE FROM FormElement WHERE formId={{id:R}} }}', 0, '', '', '', 'none', 'no', ''),

    # Check for double form name
    (1, '', 'Check for double form name', 'show', 'beforeSave', 'all', 'action', 20, 0, 0, '', '', '', '',
     'sqlValidate={{!SELECT f.id FROM Form AS f WHERE  f.name!="" AND f.name="{{name:F:alnumx}}" AND f.id!={{id:R0}}  }}\nexpectRecords=0\nmessageFail=There is already another form with the name "{{name:F:alnumx}}".',
     0, '', '', '', 'none', 'no', ''),

    # Basic
    (1, 'name', 'Name', 'required', 'text', 'pattern', 'native', 110, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-name">Info</a>', '', '', '',
     'autofocus\ndata-pattern-error=Allowed characters: alphabet, number or . - +',
     1, '', '', '', 'specialchar', 'no', '[a-zA-Z0-9._+-]+'),
    (1, 'title', 'Title', 'show', 'text', 'all', 'native', 120, '80,1,350', 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-title">Info</a>',
     '', '', '', '', 1, '', '', '', 'none', 'no', ''),
    (1, 'noteInternal', 'Note', 'show', 'text', 'all', 'native', 130, '80,1,350', 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-note">Info</a>', '', '', '', '', 1, '', '', '',
     'specialchar',
     'no', ''),
    (1, 'tableName', 'Table', 'required', 'select', 'all', 'native', 140, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-tablename">Info</a>', '', '',
     '{{[{{indexData:Y}}]!SHOW tables}}',
     'emptyItemAtStart', 1, '', '', '', 'specialchar', 'no', ''),
    (1, 'parameterLanguageA', 'Language: {{formLanguageALabel:YE}}', 'show', 'text', 'all', 'native', 150, '80,1,350',
     0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multiple-languages">Info</a>', '', '', '', '', 1, '',
     '{{SELECT IF("{{formLanguageAId:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
    (1, 'parameterLanguageB', 'Language: {{formLanguageBLabel:YE}}', 'show', 'text', 'all', 'native', 160, '80,1,350',
     0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multiple-languages">Info</a>', '', '', '', '', 1, '',
     '{{SELECT IF("{{formLanguageBId:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
    (1, 'parameterLanguageC', 'Language: {{formLanguageCLabel:YE}}', 'show', 'text', 'all', 'native', 170, '80,1,350',
     0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multiple-languages">Info</a>', '', '', '', '', 1, '',
     '{{SELECT IF("{{formLanguageCId:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
    (1, 'parameterLanguageD', 'Language: {{formLanguageDLabel:YE}}', 'show', 'text', 'all', 'native', 180, '80,1,350',
     0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multiple-languages">Info</a>', '', '', '', '', 1, '',
     '{{SELECT IF("{{formLanguageDId:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),

    # FormElement
    (1, '', 'FormElements', 'show', 'subrecord', 'all', 'native', 210, 0, 0, '', '', '',
     '{{!SELECT fe.ord AS _ord, IF( fe.enabled="yes", IF( fe.enabled="yes" AND fe.feIdContainer=0 AND !ISNULL(feCX.id) AND fe.class="native", "danger", IF( fe.class="container", "text-info",  IF( fe.class="action", "text-success", ""))), "text-muted") AS _rowClass, IF( fe.enabled="yes", IF(fe.feIdContainer=0 AND !ISNULL(feCX.id) AND fe.class="native", "Please choose a container for this formelement", fe.class), "Disabled") AS _rowTitle, fe.id AS _id, CONCAT(''<span id="qfq-dnd-ord-id-'', fe.id,''">'', fe.ord, ''</span><br><small class="text-muted">'',fe.id, ''</small>'') AS ''Ord|nostrip'', CONCAT( IFNULL( CONCAT( feC.name, " (", fe.feIdContainer, ")"),"")) AS Container, fe.name AS "Name|50", fe.label AS Label, fe.mode AS Mode, fe.class AS Class,	fe.type AS Type, IF(fe.dynamicUpdate="yes", \'<span class="glyphicon glyphicon-random"></span>\',"") AS "Dyn|nostrip" FROM FormElement AS fe LEFT JOIN FormElement AS feC ON feC.id=fe.feIdContainer AND feC.formId=fe.formId LEFT JOIN FormElement AS feCX ON feCX.class="container" AND feCX.enabled="yes" AND feCX.type="pill" AND feCX.formId=fe.formId WHERE fe.formId={{id:R0}} GROUP BY fe.id ORDER BY fe.ord, fe.id}}',
     'form=formElement\ndetail=id:formId\nsubrecordTableClass=table table-hover qfq-subrecord-table qfq-color-grey-2 tablesorter tablesorter-filter tablesorter-column-selector',
     2, 'new,edit,delete', '', '', 'none', 'no', ''),

    # Layout
    (1, 'showButton', 'Show button', 'show', 'checkbox', 'all', 'native', 220, 0, 5,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#showbutton">Info</a>', '', '', '',
     'checkBoxMode = multi\norientation=vertical', 3, '', '', '', 'specialchar', 'no', ''),
    (1, 'labelAlign', 'Label Align', 'show', 'radio', 'alnumx', 'native', 225, 0, 5,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-label-align">Info</a>', '', '', '', 'buttonClass', 3,
     '', '', '',
     'specialchar', 'no', ''),
    (1, 'parameter', 'Parameter', 'show', 'text', 'all', 'native', 230, '80,1,350', 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-parameter">Info</a>', '', '', '', '', 3, '', '', '',
     'none', 'no',
     ''),
    (1, 'bsLabelColumns', 'BS Label Columns', 'show', 'text', 'all', 'native', 240, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-layout">Info</a>', '', '', '', '', 3, '', '',
     '{{bsLabelColumns:Y}}', 'specialchar', 'no', ''),
    (1, 'bsInputColumns', 'BS Input Columns', 'show', 'text', 'all', 'native', 250, 0, 0, '', '', '', '', '', 3, '', '',
     '{{bsInputColumns:Y}}', 'specialchar', 'no', ''),
    (1, 'bsNoteColumns', 'BS Note Columns', 'show', 'text', 'all', 'native', 260, 0, 0, '', '', '', '', '', 3, '', '',
     '{{bsNoteColumns:Y}}', 'specialchar', 'no', ''),


    # Access
    (1, 'forwardMode', 'Forward', 'show', 'radio', 'all', 'native', 310, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-forward">Info</a>', '', '', '',
     'buttonClass=btn-default', 4, '',
     '', '', 'specialchar', 'no', ''),
    (1, 'forwardPage', 'Forward URL / Page', 'show', 'text', 'all', 'native', 320, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-forward">Info</a>', '', '', '', '', 4, '', '', '',
     'none', 'no',
     ''),
    (1, 'requiredParameterNew', 'Required Parameter NEW', 'show', 'text', 'all', 'native', 330, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#required-parameter-new-edit">Info</a>', '', '', '', '', 4,
     '', '', '',
     'specialchar', 'no', ''),
    (1, 'requiredParameterEdit', 'Required Parameter EDIT', 'show', 'text', 'all', 'native', 340, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#required-parameter-new-edit">Info</a>', '', '', '', '', 4,
     '', '', '',
     'specialchar', 'no', ''),
    (1, 'permitNew', 'Permit New', 'show', 'radio', 'all', 'native', 350, 0, 10,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-permitnewedit">Info</a>', '', '', '',
     'buttonClass=btn-default',
     4, '', '', '', 'specialchar', 'no', ''),
    (1, 'permitEdit', 'Permit Edit', 'show', 'radio', 'all', 'native', 360, 0, 10,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-permitnewedit">Info</a>', '', '', '',
     'buttonClass=btn-default',
     4, '', '', '', 'specialchar', 'no', ''),

    (1, 'restMethod', 'Permit REST', 'show', 'checkbox', 'all', 'native', 370, 0, 10,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#rest">Info</a>', '', '', '',
     'buttonClass=btn-default\nitemList=get,post:insert,put:update,delete', 4, '', '', '', 'specialchar', 'no', ''),

    (1, 'escapeTypeDefault', 'Escape type default', 'show', 'radio', 'all', 'native', 380, 0, 10,
     '<a tabindex="-1" href="{{documentation:Y}}/Variable.html#variable-escape">Info</a>', '', '', '',
     'itemList=c:config,s:single,d:double,l:ldap search,L:ldap value,m:mysql realEscapeString,-:none\nbuttonClass=btn-default',
     4, '', '', '', 'specialchar', 'no', ''),
    (1, 'dirtyMode', 'Record Locking', 'show', 'radio', 'all', 'native', 390, 0, 10,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#locking-record">Info</a>', '', '', '',
     'buttonClass=btn-default', 4,
     '', '', '', 'specialchar', 'no', ''),
    (1, 'recordLockTimeoutSeconds', 'Lock timeout (seconds)', 'show', 'text', 'all', 'native', 400, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#locking-record">Info</a>', '',
     '{{SELECT IF("{{recordLockTimeoutSeconds:R0}}"=0,"{{recordLockTimeoutSeconds:Y0}}","{{recordLockTimeoutSeconds:R0}}")}}',
     '', '', 4, '', '', '', 'specialchar', 'no', ''),
    (1, 'primaryKey', 'Primary Key', 'show', 'text', 'all', 'native', 410, 0, 0,
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-primary-key">Info</a>', '', '', '', '', 4, '', '', 'id',
     'specialchar', 'no', ''),

    # Multi
    (1, 'multi', 'Multi', 'show', 'fieldset', 'all', 'native', 510, 0, 0, '', '', '', '', '', 5, '', '', '',
     'specialchar', 'no', ''),
    (1, 'multiMode', 'Multi Mode', 'show', 'radio', 'all', 'native', 520, 0, 0, '', '', '', '', 'buttonClass', 5, '',
     '', '', 'specialchar', 'no', ''),
    (1, 'multiSql', 'Multi SQL', 'show', 'text', 'all', 'native', 530, '80,1,350', 0, '', '', '', '', '', 5, '', '', '',
     'none', 'no', ''),
    (1, 'multiDetailForm', 'Multi Detail Form', 'show', 'text', 'all', 'native', 540, 0, 0, '', '', '', '', '', 5,
     '', '', '', 'specialchar', 'no', ''),
    (1, 'multiDetailFormParameter', 'Multi Detail Form Parameter', 'show', 'text', 'all', 'native', 550, 0, 0, '', '',
     '', '', '', 5, '', '', '', 'none', 'no', '');


#
# FormEditor: FormElement
INSERT INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter,
                  requiredParameterNew)
VALUES (2, 'formElement',
        'Form Element Editor. Form : {{SELECT f.id, " / ",  f.name  FROM Form AS f WHERE f.id = {{formId:SR0}}  }} (DB: {{dbNameQfq:Y}})',
        'Please secure the form', 'FormElement', 'sip', 'sip', 'bootstrap', '',
        'maxVisiblePill=5\nclassBody=qfq-color-blue-1\ndbIndex={{indexQfq:Y}}', 'formId');

# FormEditor: FormElements for 'formElement'
INSERT INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, note, clientJs, value,
                         sql1, parameter, feIdContainer, subrecordOption, modeSql)
VALUES (100, 2, 'basic', 'Basic', 'show', 'pill', 'all', 'container', 10, 0, '', '', '', '', '', 0, '', ''),
       (101, 2, 'check_order', 'Check & Order', 'show', 'pill', 'all', 'container', 290, 0, '', '', '', '', '', 0, '',
        ''),
       (102, 2, 'layout', 'Layout', 'show', 'pill', 'all', 'container', 390, 0, '', '', '', '', '', 0, '', ''),
       (103, 2, 'value', 'Value', 'show', 'pill', 'all', 'container', 490, 0, '', '', '', '', '', 0, '', '');

INSERT INTO FormElement (formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs, value,
                         sql1, parameter, feIdContainer, subrecordOption, dynamicUpdate, bsLabelColumns, bsInputColumns,
                         bsNoteColumns, modeSql, placeholder, encode)
VALUES (2, 'feIdContainer', 'Container', 'show', 'select', 'all', 'native', 120, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-container">Info</a>', '',
        '{{feIdContainer:FR:::{{SELECT fe.feIdContainer FROM FormElement AS fe WHERE fe.formId={{formId:SR0}} AND fe.feIdContainer!=0 AND "{{class:FRD0:alnumx}}"!="action" ORDER BY fe.modified DESC LIMIT 1}}}}',
        '{{!SELECT fe.id, CONCAT(fe.type, " / ", fe.name, " (", COUNT(feSub.id), ")" ) FROM FormElement As fe LEFT JOIN FormElement As feSub ON feSub.feIdContainer=fe.id WHERE fe.formId={{formId:SR0}} AND fe.class="container"  AND ("{{class:FRD0:alnumx}}"!="action" OR fe.type="templateGroup") GROUP BY fe.id ORDER BY fe.type, fe.ord, fe.name }}',
        'emptyItemAtStart', 100, '', 'yes', '', '', '',
        '{{SELECT IF(COUNT(fe.id)>0, "show", "hidden") FROM Form AS f LEFT JOIN FormElement AS fe ON f.id=fe.formId AND fe.class="container" WHERE f.id={{formId:S0}} GROUP BY f.id}}',
        '', 'specialchar'),
       (2, 'enabled', 'Enabled', 'show', 'checkbox', 'all', 'native', 130, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '', '', 100, '', 'no',
        '', '', '',
        '', '', 'specialchar'),
       (2, 'dynamicUpdate', 'Dynamic Update', 'show', 'checkbox', 'all', 'native', 135, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#dynamic-update">Info</a>', '', '', '', '', 100, '', 'no',
        '', '',
        '', '', '', 'specialchar'),
       (2, 'name', 'Name', 'show', 'text', 'all', 'native', 140, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '',
        'typeAheadSql = [{{indexData:Y}}]SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = "{{DB_1_NAME:Y}}" AND table_name = "{{SELECT f.tableName FROM Form AS f WHERE f.id={{formId:S0}}}}" AND COLUMN_NAME LIKE ? ORDER BY COLUMN_NAME\ntypeAheadMinLength = 1\ntypeAheadLimit = 100\ntypeAheadPedantic = 0\n',
        100, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', 'no', '', '', '', '', '',
        'specialchar'),
       (2, 'label', 'Label', 'show', 'text', 'all', 'native', 150, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '', '', 100, '', 'no',
        '', '', '',
        '', '', 'none'),
       (2, 'mode', 'Mode', 'show', 'radio', 'all', 'native', 160, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '',
        'buttonClass=btn-default', 100,
        '', 'no', '', '', '', '', '', 'specialchar'),
       (2, 'modeSql', 'Mode sql', 'show', 'text', 'all', 'native', 170, '80,1,350', 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#dynamic-update">Info</a>', '', '', '', '', 100, '', 'no',
        '', '',
        '', '', '', 'none'),
       (2, 'class', 'Class', 'show', 'radio', 'all', 'native', 180, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-container">Info</a>', '', '{{class:FSRD0:alnumx}}',
        '',
        'buttonClass', 100, '', 'yes', '', '', '', '', '', 'none'),

       (2, 'type', 'Type', 'show', 'select', 'all', 'native', 190, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Native</a>, <a tabindex="-1" href="{{documentation:Y}}/Form.html#class-action">Action</a>, <a tabindex="-1" href="{{documentation:Y}}/Form.html#class-container">Container</a>',
        '', '', '',
        'itemList={{SELECT IF( "{{class:FRD0:alnumx}}"="native","checkbox,date,time,datetime,dateJQW,datetimeJQW,extra,gridJQW,text,editor,annotate,imageCut,note,password,radio,select,subrecord,upload", IF("{{class:FRD0:alnumx}}"="action","beforeLoad,beforeSave,beforeInsert,beforeUpdate,beforeDelete,afterLoad,afterSave,afterInsert,afterUpdate,afterDelete,sendMail,paste", "fieldset,pill,templateGroup")  ) }}',
        100, '', 'yes', '', '', '', '', '', 'specialchar'),
       (2, 'subrecordOption', 'Subrecord Option', 'show', 'checkbox', 'all', 'native', 200, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#subrecord-option">Info</a>', '', '', '', '', 100, '',
        'yes', '', '',
        '', '{{ SELECT IF("{{type:FRE:alnumx}}"="subrecord" AND "{{class:FRE:alnumx}}"="native", "show", "hidden") }}',
        '', 'specialchar'),
       (2, 'parameterLanguageA', 'Language: {{formLanguageALabel:YE}}', 'show', 'text', 'all', 'native', 210,
        '80,1,350', 0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multi-language-form">Info</a>', '', '', '',
        '', 100,
        '', 'no', '', '', '', '{{SELECT IF("{{formLanguageAId:YE}}"="","hidden","show" ) }}', '', 'none'),
       (2, 'parameterLanguageB', 'Language: {{formLanguageBLabel:YE}}', 'show', 'text', 'all', 'native', 210,
        '80,1,350', 0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multi-language-form">Info</a>', '', '', '',
        '', 100,
        '', 'no', '', '', '', '{{SELECT IF("{{formLanguageBId:YE}}"="","hidden","show" ) }}', '', 'none'),
       (2, 'parameterLanguageC', 'Language: {{formLanguageCLabel:YE}}', 'show', 'text', 'all', 'native', 210,
        '80,1,350',
        0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multi-language-form">Info</a>', '', '', '', '', 100,
        '', 'no',
        '', '', '', '{{SELECT IF("{{formLanguageCId:YE}}"="","hidden","show" ) }}', '', 'none'),
       (2, 'parameterLanguageD', 'Language: {{formLanguageDLabel:YE}}', 'show', 'text', 'all', 'native', 210,
        '80,1,350', 0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multi-language-form">Info</a>', '', '', '',
        '', 100,
        '', 'no', '', '', '', '{{SELECT IF("{{formLanguageDId:YE}}"="","hidden","show" ) }}', '', 'none'),

       (2, 'encode', 'Encode', 'show', 'radio', 'all', 'native', 300, 0, 0,
        '<a href="{{documentation:Y}}/Form.html#field-encode">Info</a> {{SELECT IF(''{{encode:FRE:alnumx}}'' = ''specialchar'' AND ''{{checkType:FRE:alnumx}}'' = ''allbut'', "<br><span class=''text-danger''> <span class=''glyphicon glyphicon-warning-sign''></span> <span>Encode ''specialchar'' is not completely compatible with Check Type ''allbut'': certain user input will be converted to its HTML representation (e.g. &amp;quot;), which makes use of a non-supported character (&amp;)</span></span>", '''') }}',
        '', '', '', 'buttonClass=btn-default', 101, '', 'yes', '', '', '', '', '', 'specialchar'),


       (2, 'checkType', 'Check Type', 'show', 'radio', 'all', 'native', 310, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-checktype">Info</a>', '', '', '',
        'buttonClass=btn-default',
        101, '', 'yes', '', '', '', '', '', 'specialchar'),
       (2, 'checkPattern', 'Check Pattern', 'show', 'text', 'all', 'native', 320, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-checkpattern">Info</a>, <a tabindex="-1" href="https://regex101.com/">Regex101</a>',
        '', '', '', '', 101, '', 'yes', '', '', '',
        '{{ SELECT IF("{{checkType:FRE:alnumx}}"="pattern" OR "{{checkType:FRE:allbut}}" LIKE "min%", "show", "hidden") }}',
        '', 'none'),
       #(2, 'onChange', 'JS onChange', 'show', 'text', 'all', 'native', 330, 0, 0, '', '', '', '', '', 101, '', 'no', '', '', '', '', '', 'none'),
       (2, 'ord', 'Order', 'show', 'text', 'all', 'native', 340, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-ord">Info</a>', '',
        '{{SELECT IF({{ord:R0}}=0,  MAX(IFNULL(fe.ord,0))+10,{{ord:R0}})  FROM (SELECT 1) AS a LEFT JOIN FormElement AS fe ON fe.formId={{formId:S0}} GROUP BY fe.formId}}',
        '', '', 101, '', 'no', '', '', '', '', '', 'specialchar'),
       #        (2, 'tabindex', 'tabindex', 'show', 'text', 'all', 'native', 350, 0, 0,
       #         '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-tabindex">Info</a>', '', '', '', '', 101, '', 'no', '', '',
       #         '', '', '',
       #         'specialchar'),
       (2, 'adminNote', 'Internal Note', 'show', 'text', 'all', 'native', 360, '80,1,350', 0, '', '', '', '', '', 101,
        '', 'no', '', '', '', '', '', 'specialchar'),

       (2, 'labelAlign', 'Label Align', 'show', 'radio', 'all', 'native', 400, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '',
        'buttonClass=btn-default', 102,
        '', 'no', '', '', '', '', '', 'specialchar'),
       (2, 'size', 'Size', 'show', 'text', 'all', 'native', 405, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-size">Info</a>', '', '', '', '', 102, '', 'no', '',
        '', '',
        '', '', 'specialchar'),
       (2, 'bsLabelColumns', 'BS Label Columns', 'show', 'text', 'all', 'native', 410, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-bslabelcolumns">Info</a>', '', '', '', '', 102, '',
        'no', '',
        '', '', '',
        '{{SELECT IF(f.bsLabelColumns != '''', f.bsLabelColumns, ''{{bsLabelColumns:Y}}'') FROM Form AS f WHERE f.id = {{formId}} }}',
        'specialchar'),
       (2, 'bsInputColumns', 'BS Input Columns', 'show', 'text', 'all', 'native', 420, 0, 0, '', '', '', '', '', 102,
        '', 'no', '', '', '', '',
        '{{SELECT IF(f.bsInputColumns != '''', f.bsInputColumns, ''{{bsInputColumns:Y}}'') FROM Form AS f WHERE f.id = {{formId}} }}',
        'specialchar'),
       (2, 'bsNoteColumns', 'BS Note Columns', 'show', 'text', 'all', 'native', 430, 0, 0, '', '', '', '', '', 102, '',
        'no', '', '', '', '',
        '{{SELECT IF(f.bsNoteColumns != '''', f.bsNoteColumns, ''{{bsNoteColumns:Y}}'') FROM Form AS f WHERE f.id = {{formId}} }}',
        'specialchar'),
       (2, 'rowLabelInputNote', 'Label / Input / Note', 'show', 'checkbox', 'alnumx', 'native', 440, 0, 10,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-rowlabelinputnote">Info</a>', '', '', '',
        'minWidth=72', 102,
        '', 'no', '', '', '', '', '', 'specialchar'),
       (2, 'maxLength', 'Maxlength', 'show', 'text', 'all', 'native', 450, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-maxlength">Info</a>', '', '', '', '', 102, '', 'no',
        '', '',
        '', '', '', 'specialchar'),
       (2, 'note', 'Note', 'show', 'text', 'all', 'native', 460, '80,1,350', 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-note">Info</a>', '', '', '', '', 102, '', 'no', '',
        '', '',
        '', '', 'none'),
       (2, 'tooltip', 'Tooltip', 'show', 'text', 'all', 'native', 470, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-tooltip">Info</a>', '', '', '', '', 102, '', 'no',
        '', '', '',
        '', '', 'none'),
       (2, 'placeholder', 'Placeholder', 'show', 'text', 'all', 'native', 480, 0, 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-placeholder">Info</a>', '', '', '', '', 102, '',
        'no', '', '',
        '', '', '', 'none'),

       (2, 'value', 'value', 'show', 'text', 'all', 'native', 500, '80,1,350', 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-value">Info</a>', '', '', '', '', 103, '', 'no', '',
        '', '',
        '', '', 'none'),
       (2, 'sql1', 'sql1', 'show', 'text', 'all', 'native', 510, '80,1,350', 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#sql1">Info</a><br><br>MariaDB: <a tabindex="-1" href="https://mariadb.com/kb/en/mariadb/select/">Select</a>, <a tabindex="-1" href="https://mariadb.com/kb/en/mariadb/functions-and-operators/">Functions</a>',
        '', '', '', '', 103, '', 'no', '', '', '', '', '', 'none'),
       (2, 'parameter', 'Parameter', 'show', 'text', 'all', 'native', 520, '80,1,350', 0,
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#fe-parameter-attributes">Info</a>', '', '', '', '', 103,
        '', 'no',
        '', '', '', '', '', 'none');

INSERT INTO `FormElement` (`id`, `formId`, `feIdContainer`, `dynamicUpdate`, `enabled`, `name`, `label`, `mode`,
                           `modeSql`, `class`, `type`, `subrecordOption`, `encode`, `checkType`, `checkPattern`,
                           `onChange`, `ord`, `tabindex`, `size`, `maxLength`, `bsLabelColumns`, `bsInputColumns`,
                           `bsNoteColumns`, `rowLabelInputNote`, `note`, `adminNote`, `tooltip`, `placeholder`, `value`,
                           `sql1`, `parameter`, `parameterLanguageA`, `parameterLanguageB`, `parameterLanguageC`,
                           `parameterLanguageD`, `clientJs`, `feGroup`, `deleted`)

VALUES (NULL, '2', '0', 'no', 'yes', 'Check Name Conflict', '', 'show', '', 'action', 'beforeSave', '', 'specialchar',
        'auto', '', '', '650', '0', '', '', '', '', '', 'row,label,/label,input,/input,note,/note,/row', '', '', '', '',
        '', '',
        'sqlValidate={{!SELECT fe.id FROM FormElement AS fe WHERE "{{class:F:alnumx}}"=fe.class AND fe.formId={{formId:RF}} AND fe.name!="" AND fe.name="{{name:F:alnumx}}" AND fe.id!={{id:R0}} }}\r\n\r\nexpectRecords=0\r\n\r\nmessageFail=There is already another {{class:F:alnumx}} form element with name "{{name:F:alnumx}}".',
        '', '', '', '', '', '', 'no');

# ----------------------------------------
# MailLog

#DROP TABLE IF EXISTS `MailLog`;
CREATE TABLE IF NOT EXISTS `MailLog`
(
    `id`       INT(11)       NOT NULL AUTO_INCREMENT,
    `grId`     INT(11)       NOT NULL DEFAULT '0',
    `xId`      INT(11)       NOT NULL DEFAULT '0',
    `xId2`     INT(11)       NOT NULL DEFAULT '0',
    `xId3`     INT(11)       NOT NULL DEFAULT '0',
    `receiver` TEXT          NOT NULL,
    `cc`       TEXT          NOT NULL,
    `bcc`      TEXT          NOT NULL,
    `sender`   VARCHAR(255)  NOT NULL DEFAULT '',
    `subject`  VARCHAR(255)  NOT NULL DEFAULT '',
    `body`     TEXT          NOT NULL,
    `header`   VARCHAR(255)  NOT NULL DEFAULT '',
    `attach`   VARCHAR(1024) NOT NULL DEFAULT '',
    `src`      VARCHAR(255)  NOT NULL DEFAULT '',
    `modified` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;

CREATE TABLE IF NOT EXISTS `FormSubmitLog`
(
    `id`        INT(11)     NOT NULL AUTO_INCREMENT,
    `formData`  TEXT        NOT NULL,
    `sipData`   TEXT        NOT NULL,
    `clientIp`  VARCHAR(64) NOT NULL,
    `feUser`    VARCHAR(64) NOT NULL,
    `userAgent` TEXT        NOT NULL,
    `formId`    INT(11)     NOT NULL,
    `recordId`  INT(11)     NOT NULL,
    `pageId`    INT         NOT NULL,
    `sessionId` VARCHAR(32) NOT NULL,
    `created`   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX (`feUser`),
    INDEX (`formId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;


CREATE TABLE IF NOT EXISTS `Clipboard`
(
    `id`          INT(11)      NOT NULL AUTO_INCREMENT,
    `cookie`      VARCHAR(255) NOT NULL DEFAULT '',
    `formIdPaste` INT(11)      NOT NULL DEFAULT '0',
    `idSrc`       INT(11)      NOT NULL DEFAULT '0',
    `xId`         INT(11)      NOT NULL DEFAULT '0',
    `modified`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;

# Form: CopyForm
INSERT INTO Form (id, name, title, tableName, showButton, forwardMode, forwardPage, parameter)
VALUES (3, 'copyForm', 'Copy a form', 'Clipboard', 'close,save', 'url-sip-skip-history',
        '?id={{pageId:T}}&form=form&r={{formId:P0}}', 'submitButtonText = Copy Form');

# FormElements: CopyForm
INSERT INTO FormElement (formId, name, label, type, class, ord, sql1, parameter)
VALUES (3, 'idSrc', 'Source Form', 'select', 'native', 10,
        '{{!SELECT f.id, CONCAT(f.name, " / ", f.title) FROM Form AS f ORDER BY f.name}}', ''),
       (3, 'myNewFormName', 'New Form Name', 'text', 'native', 20, '', ''),
       (3, 'clearClipboard', '', 'beforeSave', 'action', 100, '',
        'sqlValidate={{!SELECT f.id FROM Form AS f WHERE f.name LIKE "{{myNewFormName:FE:alnumx}}" LIMIT 1}}\nexpectRecords = 0\nmessageFail = There is already a form "{{myNewFormName:FE}}"\nsqlAfter={{DELETE FROM Clipboard WHERE cookie="{{cookieQfq:C0:alnumx}}" }}'),
       (3, 'updateClipboardRecord', '', 'afterSave', 'action', 110, '',
        'sqlAfter={{UPDATE Clipboard AS c, Form AS f SET c.cookie="{{cookieQfq:C0:alnumx}}", c.formIdPaste=f.id /* PasteForm */  WHERE c.id={{id:R}} AND f.name="{{form:SE}}" }}'),
       (3, 'formId', '', 'paste', 'action', 200, '{{!SELECT {{id:P}} AS id, "{{myNewFormName:FE:allbut}}" AS name}}',
        'recordDestinationTable=Form'),
       (3, 'formElementId', '', 'paste', 'action', 210,
        '{{!SELECT fe.id AS id, {{formId:P}} AS formId FROM FormElement AS fe WHERE fe.formId={{id:P}} ORDER BY fe.ord}}',
        'recordDestinationTable=FormElement\ntranslateIdColumn=feIdContainer');

# AutoCRON
CREATE TABLE IF NOT EXISTS `Cron`
(
    `id`            INT(11)                      NOT NULL AUTO_INCREMENT,
    `grId`          INT(11)                      NOT NULL,
    `xId`           INT(11)                      NOT NULL,
    `type`          ENUM ('mail', 'website')     NOT NULL DEFAULT 'website',
    `lastRun`       DATETIME                     NOT NULL DEFAULT '0000-00-00 00:00:00',
    `lastStatus`    TEXT                         NOT NULL,
    `nextRun`       DATETIME                     NOT NULL DEFAULT '0000-00-00 00:00:00',
    `frequency`     VARCHAR(32)                  NOT NULL,
    `inProgress`    DATETIME                     NOT NULL DEFAULT '0000-00-00 00:00:00',
    `status`        ENUM ('enable', 'disable')   NOT NULL DEFAULT 'enable',
    `sql1`          TEXT                         NOT NULL,
    `content`       TEXT                         NOT NULL,
    `comment`       TEXT                         NOT NULL,
    `outputFile`    VARCHAR(255)                 NOT NULL,
    `outputMode`    ENUM ('overwrite', 'append') NOT NULL DEFAULT 'append',
    `outputPattern` VARCHAR(255)                 NOT NULL,
    `autoGenerated` ENUM ('yes', 'no')           NOT NULL DEFAULT 'no',
    `modified`      DATETIME                     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`       DATETIME                     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    AUTO_INCREMENT = 0
    DEFAULT CHARSET = utf8;

# Form: AutoCron
INSERT INTO Form (id, name, title, tableName, parameter, dirtyMode)
VALUES (4, 'cron', 'autoCron', 'Cron', 'dbIndex={{indexQfq:Y}}', 'none');

# FormElements: AutoCron
INSERT INTO FormElement (formId, name, label, mode, modeSql, type, encode, checkType, ord, parameter, size, note,
                         dynamicUpdate, bsLabelColumns, bsInputColumns, bsNoteColumns)
VALUES (4, 'status', 'Enabled', 'show', '', 'checkbox', 'specialchar', 'alnumx', 10, '', '', '', 'no', '', '', ''),
       (4, 'type', 'Type', 'show', '', 'radio', 'specialchar', 'alnumx', 20, 'buttonClass=btn-default', '', '', 'yes',
        '', '', ''),
       (4, 'nextRun', 'Next run', 'show', '', 'text', 'specialchar', 'alnumx', 30,
        'extraButtonInfo = Cronjob will be started if specified timestamp is over. If timestamp=0: Job will never be started<br>Every time the jobs runs, this timestamp will be increased automatically by "frequency".',
        '', '', 'no', '', '', ''),
       (4, 'frequency', 'Frequency', 'show', '', 'text', 'specialchar', 'alnumx', 40,
        'extraButtonInfo = Repeat AutoCron-job with the specified interval. If empty: no repeating.<br>E.g.: "1 DAY", "15 MINUTE'', "6 MONTH" - used directly in SQL-Function "DATE_ADD(&lt;nextrun&gt;, INTERVAL &lt;frequency&gt;)"',
        '', '', 'no', '', '', ''),
       (4, 'comment', 'Comment', 'show', '', 'text', 'specialchar', 'allbut', 50, '', '', '', 'no', '', '', ''),
       (4, 'sql1', 'Mail', 'show', '{{SELECT IF("{{type:FR:alnumx}}"="mail","show","hidden") }}', 'text', 'none', 'all',
        60,
        'extraButtonInfo = Query: &#123;&#123;!SELECT ... as sendMailTo...&#125;&#125;<br><b>sendMailTo / sendMailCc / sendMailBcc</b>: Separate multiple by comma.<br><b>sendMailFrom</b><br><b>sendMailSubject</b><br><b>sendMailReplyTo</b>: Optional<br><b>sendMailFlagAutoSubmit</b>: Optional. on|off. Default on - if "on", suppresses OoO answers from receivers.<br><b>sendMailGrId</b>: Optional<br><b>sendMailXId</b>: Optional',
        '80,1,350', '', 'yes', '', '', ''),
       (4, 'content', '{{SELECT IF("{{type:FR:alnumx}}"="mail","Mail body","URL") }}', 'show', '', 'text', 'none',
        'all', 70, '', '80,1,350',
        'Website: URL absolute like "http://..." or relative like "?id=pagealias..."<br>Mail: Static Body or &#123;{SELECT ...&#125;}',
        'yes', '', '', ''),

       (4, 'outputFile', 'Log output to file', 'show', '{{SELECT IF("{{type:FR:alnumx}}"="mail","hidden","show") }}',
        'text', 'none', 'all', 80, '', '', 'CWD: Site installation directory', 'yes', '', '', ''),
       (4, 'outputMode', 'Mode output', 'show', '{{SELECT IF("{{type:FR:alnumx}}"="mail","hidden","show") }}', 'radio',
        'specialchar', 'alnumx', 90, 'buttonClass=btn-default', '', '', 'yes', '', '', ''),
       (4, 'outputPattern', 'Pattern to look for on output', 'show',
        '{{SELECT IF("{{type:FR:alnumx}}"="mail","hidden","show") }}', 'text', 'none', 'all', 100, '', '',
        'If pattern isn\'t found, return an error.<br>Check <a tabindex="-1" href="https://secure.php.net/manual/en/pcre.pattern.php">pcre</a> / <a tabindex="-1" href="https://regex101.com/">Regex101</a> ',
        'yes', '', '', ''),

       (4, 'lastRun', 'Last run', 'readonly', '', 'text', 'specialchar', 'alnumx', 120, '', '', '', 'no', '', '', ''),
       (4, 'lastStatus', 'Laststatus', 'readonly', '', 'text', 'specialchar', 'alnumx', 130, '', '80,1,350', '', 'no',
        '3', '9', '0'),
       (4, 'inProgress', 'In progress since', 'show', '', 'text', 'specialchar', 'alnumx', 140,
        'extraButtonInfo = Start time of a running job. When job is finished, this will be set back to 0. A new job will only be started, if this is 0. A progress duration >10mins will be treated as an error.',
        '', '', 'no', '', '', '');

CREATE TABLE IF NOT EXISTS `Split`
(
    `id`           INT(11)      NOT NULL AUTO_INCREMENT,
    `tableName`    VARCHAR(255) NOT NULL,
    `xId`          INT(11)      NOT NULL,
    `pathFileName` VARCHAR(255) NOT NULL,
    `modified`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    AUTO_INCREMENT = 0
    DEFAULT CHARSET = utf8;

CREATE TABLE IF NOT EXISTS `Setting`
(
    `id`       INT(11)                  NOT NULL AUTO_INCREMENT,
    `type`     ENUM ('', 'tablesorter') NOT NULL,
    `readonly` ENUM ('yes', 'no')       NOT NULL DEFAULT 'no' COMMENT 'Settings can''t be modified.',
    `name`     VARCHAR(64)              NOT NULL,
    `public`   TINYINT(1)               NOT NULL,
    `feUser`   VARCHAR(32)              NOT NULL COMMENT 'In case there is no logged in user, take QFQ cookie.',
    `tableId`  VARCHAR(64)              NOT NULL,
    `view`     TEXT                     NOT NULL,
    `modified` DATETIME                 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`  DATETIME                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `name` (`name`),
    KEY `typeFeUserUidTableIdPublic` (`type`, `feUser`, `tableId`, `public`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;