formEditor.sql

#DROP TABLE IF EXISTS `Form`;
CREATE TABLE IF NOT EXISTS `Form` (
  `id`                       INT(11)                                                    NOT NULL  AUTO_INCREMENT,
  `name`                     VARCHAR(255)                                               NOT NULL  DEFAULT '',
  `title`                    VARCHAR(255)                                               NOT NULL  DEFAULT '',
  `noteInternal`             TEXT                                                       NOT NULL,
  `tableName`                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',
  `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('client', 'no', 'url', 'url-skip-history', 'url-sip') NOT NULL  DEFAULT 'client',
  `forwardPage`              VARCHAR(255)                                               NOT NULL  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`                 TIMESTAMP                                                  NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created`                  DATETIME                                                   NOT NULL  DEFAULT '0000-00-00 00:00:00',

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


#--
#-- Triggers `Form`
#--
#DROP TRIGGER IF EXISTS `on_Form_update_modified`;
#DELIMITER //
#CREATE TRIGGER `on_Form_update_modified` BEFORE UPDATE ON `Form`
#FOR EACH ROW SET NEW.modified =
#current_timestamp()
#//
#DELIMITER ;

# ----------------------------------------
# FormElement

#DROP TABLE IF EXISTS `FormElement`;
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(255)                                                                                         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', 'time', 'note', 'password', 'radio', 'select', 'subrecord', 'upload', 'annotate',
                            '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('alnumx', 'digit', 'numerical', 'email', 'min|max', 'min|max date', 'pattern', 'allbut', 'all') NOT NULL  DEFAULT 'alnumx',
  `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 '',
  `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`           TIMESTAMP                                                                                            NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created`            DATETIME                                                                                             NOT NULL  DEFAULT '0000-00-00 00:00:00',

  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;

#--
#-- Triggers `FormElement`
#--
#DROP TRIGGER IF EXISTS `on_FormEelement_update_modified`;
#DELIMITER //
#CREATE TRIGGER `on_FormEelement_update_modified` BEFORE UPDATE ON `FormElement`
#FOR EACH ROW SET NEW.modified =
#current_timestamp()
#//
#DELIMITER ;

DROP TABLE IF EXISTS `Dirty`;
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,
  `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`             TIMESTAMP                             NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created`              DATETIME                              NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sip` (`sip`),
  KEY `tableName` (`tableName`),
  KEY `recordId` (`recordId`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  AUTO_INCREMENT = 0;


CREATE TABLE IF NOT EXISTS `Period` (
  `id`       INT(11)      NOT NULL AUTO_INCREMENT,
  `start`    DATETIME     NOT NULL,
  `name`     VARCHAR(255) NOT NULL,
  `modified` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created`  DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `start` (`start`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  AUTO_INCREMENT = 0;

# Delete previous FormElements (if exist)
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}}}}', 'FormElement Editor',
   'Form', 'sip', 'sip', 'bootstrap', '', 'maxVisiblePill=5\nclass=container-fluid');

# 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', 10, 0, '', '', '', '', '', 0, '', '', ''),
  (2, 1, 'access', 'Access', 'show', 'pill', 'all', 'container', 20, 0, '', '', '', '', '', 0, '', '', ''),
  (3, 1, 'various', 'Various', 'show', 'pill', 'all', 'container', 30, 0, '', '', '', '', '', 0, '', '', ''),
  (4, 1, 'multi', 'Multi', 'show', 'pill', 'all', 'container', 40, 0, '', '', '', '', '', 0, '', '', ''),
  (5, 1, 'formelement', 'Formelement', 'show', 'pill', 'all', 'container', 50, 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', 100, 0, 0, '', '', '', '',
   'sqlAfter={{DELETE FROM FormElement WHERE formId={{id:R}} }}', 0, '', '', '', 'none', 'no', ''),
  (1, 'name', 'Name', 'required', 'text', 'pattern', 'native', 120, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-name">Info</a>', '', '', '', 'autofocus',
   1, '', '', '', 'specialchar', 'no', '[a-zA-Z0-9._+-]+'),
  (1, 'title', 'Title', 'show', 'text', 'all', 'native', 130, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-title">Info</a>', '', '', '', '', 1, '', '', '', 'none', 'no', ''),
  (1, 'noteInternal', 'Note', 'show', 'text', 'all', 'native', 140, '40,3', 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-note">Info</a>', '', '', '', '', 1, '', '', '', 'specialchar', 'no', ''),
  (1, 'tableName', 'Table', 'required', 'select', 'all', 'native', 150, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-tablename">Info</a>', '', '', '{{!SHOW tables}}',
   'emptyItemAtStart', 1, '', '', '', 'specialchar', 'no', ''),
  (1, 'parameterLanguageA', 'Language: {{FORM_LANGUAGE_A_LABEL:YE}}', 'show', 'text', 'all', 'native', 160, '60,2', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#multi-language-form">Info</a>', '', '', '', '', 1, '', '{{SELECT IF("{{FORM_LANGUAGE_A_ID:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
  (1, 'parameterLanguageB', 'Language: {{FORM_LANGUAGE_B_LABEL:YE}}', 'show', 'text', 'all', 'native', 160, '60,2', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#multi-language-form">Info</a>', '', '', '', '', 1, '', '{{SELECT IF("{{FORM_LANGUAGE_B_ID:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
  (1, 'parameterLanguageC', 'Language: {{FORM_LANGUAGE_C_LABEL:YE}}', 'show', 'text', 'all', 'native', 160, '60,2', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#multi-language-form">Info</a>', '', '', '', '', 1, '', '{{SELECT IF("{{FORM_LANGUAGE_C_ID:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
  (1, 'parameterLanguageD', 'Language: {{FORM_LANGUAGE_D_LABEL:YE}}', 'show', 'text', 'all', 'native', 160, '60,2', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#multi-language-form">Info</a>', '', '', '', '', 1, '', '{{SELECT IF("{{FORM_LANGUAGE_D_ID:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
  (1, 'requiredParameterNew', 'Required Parameter NEW', 'show', 'text', 'all', 'native', 200, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-requiredParameter">Info</a>', '', '', '', '', 2, '', '', '', 'specialchar', 'no', ''),
  (1, 'requiredParameterEdit', 'Required Parameter EDIT', 'show', 'text', 'all', 'native', 200, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-requiredParameter">Info</a>', '', '', '', '', 2, '', '', '', 'specialchar', 'no', ''),
  (1, 'permitNew', 'Permit New', 'show', 'radio', 'all', 'native', 210, 0, 10, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-permitNewEdit">Info</a>', '', '', '', 'buttonClass=btn-default', 2, '', '', '', 'specialchar', 'no', ''),
  (1, 'permitEdit', 'Permit Edit', 'show', 'radio', 'all', 'native', 220, 0, 10, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-permitNewEdit">Info</a>', '', '', '', 'buttonClass=btn-default', 2, '', '', '', 'specialchar', 'no', ''),
  (1, 'escapeTypeDefault', 'Escape type default', 'show', 'radio', 'all', 'native', 230, 0, 10, '<a href="{{DOCUMENTATION_QFQ:Y}}#variable-escape">Info</a>', '', '', '',
   'itemList=c:config,s:single,d:double,l:ldap search,L:ldap value,m:mysql realEscapeString,-:none\nbuttonClass=btn-default', 2, '', '', '', 'specialchar', 'no', ''),
  (1, 'dirtyMode', 'Record Locking', 'show', 'radio', 'all', 'native', 240, 0, 10, '<a href="{{DOCUMENTATION_QFQ:Y}}#locking-record">Info</a>', '', '', '',
   'buttonClass=btn-default', 2, '', '', '', 'specialchar', 'no', ''),
  (1, 'recordLockTimeoutSeconds', 'Lock timeout (seconds)', 'show', 'text', 'all', 'native', 245, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-requiredParameter">Info</a>', '',
   '{{SELECT IF("{{recordLockTimeoutSeconds:R0}}"=0,"{{RECORD_LOCK_TIMEOUT_SECONDS:Y0}}","{{recordLockTimeoutSeconds:R0}}")}}', '', '', 2, '', '', '', 'specialchar', 'no', ''),
  (1, 'showButton', 'Show button', 'show', 'checkbox', 'all', 'native', 250, 0, 5, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-showButton">Info</a>', '', '', '', 'checkBoxMode = multi\norientation=vertical', 2, '', '', '', 'specialchar', 'no', ''),
  (1, 'forwardMode', 'Forward', 'show', 'radio', 'all', 'native', 300, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-forward">Info</a>', '', '', '', 'buttonClass=btn-default', 3, '', '', '', 'specialchar', 'no', ''),
  (1, 'forwardPage', 'Forward URL / Page', 'show', 'text', 'all', 'native', 310, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-forward">Info</a>', '', '', '', '', 3, '',
   '', '', 'none', 'no', ''),
  (1, 'parameter', 'Parameter', 'show', 'text', 'all', 'native', 320, '40,8', 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-parameter">Info</a>', '', '', '', '', 3, '', '', '', 'none', 'no', ''),
  (1, 'bsLabelColumns', 'BS Label Columns', 'show', 'text', 'all', 'native', 330, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#form-layout">Info</a>', '', '', '', '', 3, '', '', '{{bsLabelColumns:Y}}', 'specialchar', 'no', ''),
  (1, 'bsInputColumns', 'BS Input Columns', 'show', 'text', 'all', 'native', 340, 0, 0, '', '', '', '', '', 3, '', '', '{{bsInputColumns:Y}}', 'specialchar', 'no', ''),
  (1, 'bsNoteColumns', 'BS Note Columns', 'show', 'text', 'all', 'native', 350, 0, 0, '', '', '', '', '', 3, '', '', '{{bsNoteColumns:Y}}', 'specialchar', 'no', ''),
  (1, 'deleted', 'Deleted', 'show', 'checkbox', 'all', 'native', 360, 0, 0, '', '', '', '', '', 3, '', '', '', 'specialchar', 'no', ''),
  (1, 'modified', 'Modified', 'readonly', 'text', 'all', 'native', 370, 0, 20, '', '', '', '', '', 3, '', '', '', 'specialchar', 'no', ''),
  (1, 'created', 'Created', 'readonly', 'text', 'all', 'native', 380, 0, 20, '', '', '', '', '', 3, '', '', '',
   'specialchar', 'no', ''),

  (1, 'multi', 'Multi', 'show', 'fieldset', 'all', 'native', 400, 0, 0, '', '', '', '', '', 4, '', '', '',
   'specialchar', 'no', ''),
  (1, 'multiMode', 'Multi Mode', 'show', 'radio', 'all', 'native', 410, 0, 0, '', '', '', '', '', 4, '', '', '',
   'specialchar', 'no', ''),
  (1, 'multiSql', 'Multi SQL', 'show', 'text', 'all', 'native', 420, '40,3', 0, '', '', '', '', '', 4, '', '', '',
   'none', 'no', ''),
  (1, 'multiDetailForm', 'Multi Detail Form', 'show', 'text', 'all', 'native', 430, 0, 0, '', '', '', '', '', 4,
   '', '', '', 'specialchar', 'no', ''),
  (1, 'multiDetailFormParameter', 'Multi Detail Form Parameter', 'show', 'text', 'all', 'native', 440, 0, 0, '', '',
   '', '', '', 4, '', '', '', 'none', 'no', ''),

  (1, '', 'FormElements', 'show', 'subrecord', 'all', 'native', 500, 0, 0, '', '', '',
   '{{!SELECT 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, CONCAT( IFNULL( CONCAT( feC.name, " (", fe.feIdContainer, ")"),"")) AS Container, fe.name AS "Name|50", fe.label, fe.mode, fe.class,	fe.type, fe.ord, IF(fe.dynamicUpdate="yes", \'<span class="glyphicon glyphicon-random"></span>\',"") AS "dyn|||nostrip", fe.parameter 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.formId=fe.formId WHERE fe.formId={{id:R0}} GROUP BY fe.id ORDER BY fe.class DESC, feC.ord, fe.ord, fe.id}}',
   'form=formElement\ndetail=id:formId', 5, 'new,edit,delete', '', '', '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:S0}}  }}',
   'Please secure the form',
   'FormElement', 'sip', 'sip', 'bootstrap', '', 'maxVisiblePill=5\nclassBody=qfq-color-blue-1', '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', 20, 0, '', '', '', '', '', 0, '',
   ''),
  (102, 2, 'layout', 'Layout', 'show', 'pill', 'all', 'container', 20, 0, '', '', '', '', '', 0, '', ''),
  (103, 2, 'value', 'Value', 'show', 'pill', 'all', 'container', 20, 0, '', '', '', '', '', 0, '', ''),
  (104, 2, 'info', 'Info', 'show', 'pill', 'all', 'container', 20, 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 href="{{DOCUMENTATION_QFQ:Y}}#class-container">Info</a>', '', '',
   '{{!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:S0}} AND fe.class="container" GROUP BY fe.id ORDER BY fe.type, fe.ord, fe.name }}',
   'emptyItemAtStart', 100, '', 'no', '', '', '',
   '{{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 href="{{DOCUMENTATION_QFQ:Y}}#class-native">Info</a>', '', '', '', '', 100, '', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'dynamicUpdate', 'Dynamic Update', 'show', 'checkbox', 'all', 'native', 135, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#dynamic-update">Info</a>',
   '', '', '', '', 100, '', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'name', 'Name', 'show', 'text', 'all', 'native', 140, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#class-native">Info</a>', '', '', '',
   'typeAheadSql = SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = "{{DB_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\n',
   100, '<a href="{{DOCUMENTATION_QFQ:Y}}#class-native">Info</a>', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'label', 'Label', 'show', 'text', 'all', 'native', 150, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#class-native">Info</a>', '', '', '', '', 100, '', 'no', '', '', '', '', '', 'none'),
  (2, 'mode', 'Mode', 'show', 'radio', 'all', 'native', 160, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#class-native">Info</a>', '', '', '', 'buttonClass=btn-default', 100, '', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'modeSql', 'Mode sql', 'show', 'text', 'all', 'native', 170, '70,2', 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#dynamic-update">Info</a>', '', '', '', '', 100, '', 'no', '', '', '', '', '', 'none'),
  (2, 'class', 'Class', 'show', 'select', 'all', 'native', 180, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#class-container">Info</a>', '', '{{class:FSRD0:alnumx}}', '', '', 100, '', 'yes', '', '', '', '', '', 'none'),

  (2, 'type', 'Type', 'show', 'select', 'all', 'native', 190, 0, 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#class-native">native</a>, <a href="{{DOCUMENTATION_QFQ:Y}}#class-action">Action</a>, <a href="{{DOCUMENTATION_QFQ:Y}}#class-container">Container</a>',
   '', '', '',
   'itemList={{SELECT IF( "{{class:FRD0:alnumx}}"="native","checkbox,date,time,datetime,dateJQW,datetimeJQW,extra,gridJQW,text,editor,annotate,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 href="{{DOCUMENTATION_QFQ:Y}}#subrecord-option">Info</a>', '', '', '',
   '', 100, '', 'yes', '', '', '', '{{ SELECT IF("{{type:FRE:alnumx}}"="subrecord" AND "{{class:FRE:alnumx}}"="native", "show", "hidden") }}', '', 'specialchar'),
  (2, 'parameterLanguageA', 'Language: {{FORM_LANGUAGE_A_LABEL:YE}}', 'show', 'text', 'all', 'native', 210, '60,2', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#multi-language-form">Info</a>', '', '', '', '', 100, '', 'no', '', '', '', '{{SELECT IF("{{FORM_LANGUAGE_A_ID:YE}}"="","hidden","show" ) }}', '', 'none'),
  (2, 'parameterLanguageB', 'Language: {{FORM_LANGUAGE_B_LABEL:YE}}', 'show', 'text', 'all', 'native', 210, '60,2', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#multi-language-form">Info</a>', '', '', '', '', 100, '', 'no', '', '', '', '{{SELECT IF("{{FORM_LANGUAGE_B_ID:YE}}"="","hidden","show" ) }}', '', 'none'),
  (2, 'parameterLanguageC', 'Language: {{FORM_LANGUAGE_C_LABEL:YE}}', 'show', 'text', 'all', 'native', 210, '60,2', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#multi-language-form">Info</a>', '', '', '', '', 100, '', 'no', '', '', '', '{{SELECT IF("{{FORM_LANGUAGE_C_ID:YE}}"="","hidden","show" ) }}', '', 'none'),
  (2, 'parameterLanguageD', 'Language: {{FORM_LANGUAGE_D_LABEL:YE}}', 'show', 'text', 'all', 'native', 210, '60,2', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#multi-language-form">Info</a>', '', '', '', '', 100, '', 'no', '', '', '', '{{SELECT IF("{{FORM_LANGUAGE_D_ID:YE}}"="","hidden","show" ) }}', '', 'none'),
  (2, 'encode', 'Encode', 'show', 'radio', 'all', 'native', 300, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-encode">Info</a>', '', '', '', 'buttonClass=btn-default', 101, '', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'checkType', 'Check Type', 'show', 'radio', 'all', 'native', 310, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-checkType">Info</a>', '', '', '', 'buttonClass=btn-default', 101, '', 'yes', '', '', '', '', '', 'specialchar'),
  (2, 'checkPattern', 'Check Pattern', 'show', 'text', 'all', 'native', 320, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-checkType">Info</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 href="{{DOCUMENTATION_QFQ:Y}}#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 href="{{DOCUMENTATION_QFQ:Y}}#field-tabindex">Info</a>', '', '', '', '', 101, '', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'size', 'Size', 'show', 'text', 'all', 'native', 400, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-size">Info</a>', '', '', '', '', 102, '', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'bsLabelColumns', 'BS Label Columns', 'show', 'text', 'all', 'native', 410, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-bsLabelColumns">Info</a>', '', '', '', '', 102, '', 'no', '', '', '', '', '{{bsLabelColumns:Y}}', 'specialchar'),
  (2, 'bsInputColumns', 'BS Input Columns', 'show', 'text', 'all', 'native', 420, 0, 0, '', '', '', '', '', 102, '', 'no', '', '', '', '', '{{bsInputColumns:Y}}', 'specialchar'),
  (2, 'bsNoteColumns', 'BS Note Columns', 'show', 'text', 'all', 'native', 430, 0, 0, '', '', '', '', '', 102, '', 'no', '', '', '', '', '{{bsNoteColumns:Y}}', 'specialchar'),
  (2, 'rowLabelInputNote', 'Label / Input / Note', 'show', 'checkbox', 'alnumx', 'native', 440, 0, 10, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-rowLabelInputNote">Info</a>', '', '', '', '', 102, '', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'maxLength', 'Maxlength', 'show', 'text', 'all', 'native', 450, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-maxLength">Info</a>', '', '', '', '', 102, '', 'no', '', '', '', '', '', 'specialchar'),
  (2, 'note', 'Note', 'show', 'text', 'all', 'native', 460, '40,5', 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-note">Info</a>', '', '', '', '', 102, '', 'no', '', '', '', '', '', 'none'),
  (2, 'tooltip', 'Tooltip', 'show', 'text', 'all', 'native', 470, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-tooltip">Info</a>', '', '', '', '', 102, '', 'no', '', '', '', '', '', 'none'),
  (2, 'placeholder', 'Placeholder', 'show', 'text', 'all', 'native', 480, 0, 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-placeholder">Info</a>', '', '', '', '', 102, '', 'no', '', '', '', '', '', 'none'),
  (2, 'value', 'value', 'show', 'text', 'all', 'native', 500, '40,2', 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#field-value">Info</a>', '', '', '', '', 103, '', 'no', '', '', '', '', '', 'none'),
  (2, 'sql1', 'sql1', 'show', 'text', 'all', 'native', 510, '40,5', 0,
   '<a href="{{DOCUMENTATION_QFQ:Y}}#field-sql1">Info</a>, MariaDB: <a href="https://mariadb.com/kb/en/mariadb/select/">Select</a>, <a href="https://mariadb.com/kb/en/mariadb/functions-and-operators/">Functions</a>',
   '', '', '', '', 103, '', 'no', '', '', '', '', '', 'none'),
  (2, 'parameter', 'Parameter', 'show', 'text', 'all', 'native', 520, '40,8', 0, '<a href="{{DOCUMENTATION_QFQ:Y}}#fe-parameter-attributes">Info</a>',
   '', '', '', '', 103, '', 'no', '', '', '', '', '', 'none'),
  (2, 'adminNote', 'Admin Note', 'show', 'text', 'all', 'native', 600, 0, 0, '', '', '', '', '', 104, '', 'no', '', '',
   '', '', '', 'specialchar'),
  (2, 'feGroup', 'feGroup', 'show', 'text', 'all', 'native', 610, 0, 0, '', '', '', '', '', 104, '', 'no', '', '', '',
   '', '', 'specialchar'),
  (2, 'deleted', 'Deleted', 'show', 'checkbox', 'all', 'native', 620, 0, 0, '', '', '', '', '', 104, '', 'no', '', '',
   '', '', '', 'specialchar'),
  (2, 'modified', 'Modified', 'readonly', 'text', 'all', 'native', 630, 0, 20, '', '', '', '', '', 104, '', 'no',
   '', '', '', '', '', 'specialchar'),
  (2, 'created', 'Created', 'readonly', 'text', 'all', 'native', 640, 0, 20, '', '', '', '', '', 104, '', 'no', '',
   '', '', '', '', 'specialchar');

# ----------------------------------------
# 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,
  `sender`   VARCHAR(255) NOT NULL  DEFAULT '',
  `subject`  VARCHAR(255) NOT NULL  DEFAULT '',
  `body`     TEXT         NOT NULL,
  `header`   VARCHAR(255) NOT NULL  DEFAULT '',
  `attach`   VARCHAR(255) NOT NULL  DEFAULT '',
  `src`      VARCHAR(255) NOT NULL  DEFAULT '',
  `modified` TIMESTAMP    NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  `created`  DATETIME     NOT NULL  DEFAULT '0000-00-00 00:00:00',

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


#--
#-- Triggers `MailLog`
#--
#DROP TRIGGER IF EXISTS `on_MailLog_update_modified`;
#DELIMITER //
#CREATE TRIGGER `on_MailLog_update_modified` BEFORE UPDATE ON `MailLog`
#FOR EACH ROW SET NEW.modified =
#current_timestamp()
#//
#DELIMITER ;

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`    TIMESTAMP    NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created`     DATETIME     NOT NULL  DEFAULT '0000-00-00 00:00:00',

  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', '?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 "{{myName:FE:alnumx}}" LIMIT 1}}\nexpectRecords = 0\nmessageFail = There is already a form with this name\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');


# Default record for table Period
INSERT INTO Period (start, name, created) VALUES (NOW(), 'dummy', NOW());

# AutoCRON
CREATE TABLE IF NOT EXISTS `Cron` (
  `id`         INT(11)                   NOT NULL  AUTO_INCREMENT,
  `grId`       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,
  `modified`   TIMESTAMP                 NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created`    DATETIME                  NOT NULL  DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8;

# Form: Cron
INSERT INTO Form (id, name, title, tableName, parameter)
VALUES
  (4, 'cron', 'cron {{SELECT IF("{{r}}" = "0", "(new)", "(Id: {{r}})") }}', 'Cron', '');

# FormElements: CopyForm
INSERT INTO FormElement (formId, name, label, mode, modeSql, type, encode, checkType, ord, parameter, size, note)
VALUES
  (4, 'status', 'Enabled', 'show', '', 'checkbox', 'specialchar', 'alnumx', 10, '', '', ''),
  (4, 'type', 'Type', 'show', '', 'radio', 'specialchar', 'alnumx', 20, 'buttonClass=btn-default', '', ''),
  (4, 'nextRun', 'Next run', 'show', '', 'text', 'specialchar', 'alnumx', 30, '', '',
   '<p>Cronjob will be started if specified timestamp is over. If timestamp=0: Job will never be started</p>\r\n<p>Every time the jobs runs, this timestamp will be increased automatically by&nbsp;''frequency`.</p>'),
  (4, 'frequency', 'Frequency', 'show', '', 'text', 'specialchar', 'alnumx', 40, '', '',
   '<p>Repeat AutoCron-job with the specified interval. If empty: no repeating.</p>\r\n<p>E.g.: "1 DAY", "15 MINUTE'', "6 MONTH" - used directly in SQL-Function "DATE_ADD(&lt;nextrun&gt;, INTERVAL &lt;frequency&gt;)"&nbsp;</p>'),
  (4, 'comment', 'Comment', 'show', '', 'text', 'specialchar', 'allbut', 50, '', '',
   '<p>Name/Description of cronjob</p>'),
  (4, 'sql1', 'Mail', 'show', '', 'text', 'none', 'all', 60, '', '60,4',
   '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'),
  (4, 'content', 'Content', 'show', '', 'text', 'none', 'all', 70, '', '40,4',
   '<p>Website: URL<br>Mail: Static Body or &#123;{SELECT ...&#125;}</p>'),
  (4, 'lastRun', 'Last run', 'readonly', '', 'text', 'specialchar', 'alnumx', 80, '', '', ''),
  (4, 'lastStatus', 'Laststatus', 'readonly', '', 'text', 'specialchar', 'alnumx', 90, '', '', ''),
  (4, 'inProgress', 'Running', 'show', '', 'text', 'specialchar', 'alnumx', 100, '', '',
   '<p>Starttime of running job. When job is finished, will be set back to 0. A new job will only be started, if this is 0.</p>\r\n<p>&nbsp;</p>');