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',
  `permitUrlParameter`       VARCHAR(255)                                              NOT NULL  DEFAULT '',
  `render`  ENUM('plain', 'table', 'bootstrap') NOT NULL  DEFAULT 'plain',
  `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', 'no', 'page')                                NOT NULL  DEFAULT 'auto',
  `forwardPage`              VARCHAR(255)                                              NOT NULL  DEFAULT '',

  `bsLabelColumns`           VARCHAR(255)                                              NOT NULL  DEFAULT '',
  `bsInputColumns`           VARCHAR(255)                                              NOT NULL  DEFAULT '',
  `bsNoteColumns`            VARCHAR(255)                                              NOT NULL  DEFAULT '',

  `deleted`                  ENUM('yes', 'no')                                         NOT NULL  DEFAULT 'no',
  `modified`                 TIMESTAMP                                                 NOT NULL  DEFAULT 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 = 0;

--
-- 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 ;

#
# FormEditor
INSERT INTO Form (name, title, noteInternal, tableName, permitNew, permitEdit, multiSql) VALUES
  ('form', 'Form Editor: {{SELECT id, " / ", title FROM Form WHERE id = {{recordId:SZ}}}}', 'Please secure the form',
   'Form', 'always', 'always', '');


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

DROP TABLE IF EXISTS `FormElement`;
CREATE TABLE IF NOT EXISTS `FormElement` (
  `id`           INT(11)                                                  NOT NULL                                                                                                                                                                                                                                                                                                                                                      AUTO_INCREMENT,
  `formId`       INT(11)                                                  NOT NULL,
  `groupId`      INT(11)                                                  NOT NULL DEFAULT '0',

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

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

  `mode`         ENUM('show', 'readonly', 'required', 'lock', 'disabled') NOT NULL DEFAULT 'show',
  `class`        ENUM('native', 'action', 'fieldset', 'pill')             NOT NULL DEFAULT 'native',
  `type`         ENUM('checkbox', 'dateJQW', 'datetimeJQW', 'email', 'gridJQW', 'hidden', 'input',
                      'note', 'password', 'radio', 'select', 'subrecord', 'upload',
                      'before_load', 'before_save', 'before_insert', 'before_update', 'before_delete', 'after_load',
                      'after_save', 'after_insert', 'after_update', 'after_delete', 'group',
                      'sendmail')                                         NOT NULL DEFAULT 'input',
  `checkType`    ENUM('', 'min|max', 'pattern', 'number', 'email'),
  `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 '',
  `note`         TEXT                                                     NOT NULL,
  `tooltip`      VARCHAR(255)                                             NOT NULL DEFAULT '',
  `placeholder`  VARCHAR(255)                                             NOT NULL DEFAULT '',
  `clientJs`     TEXT,

  `value`        TEXT                                                     NOT NULL,
  `sql1`         TEXT                                                     NOT NULL,
  `sql2`         TEXT                                                     NOT NULL,
  `parameter`    TEXT                                                     NOT NULL,

  `deleted`      ENUM('yes', 'no')                                        NOT NULL DEFAULT 'no',
  `modified`     TIMESTAMP                                                NOT NULL DEFAULT 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`)
)
  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 ;

# FormEditor: FormElements
INSERT INTO FormElement (formId, name, label, mode, type, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter)
VALUES
  (1, 'id', 'id', 'readonly', 'input', 10, 10, 11, '', '', '', '', '', ''),
  (1, 'name', 'Name', 'show', 'input', 20, 40, 255, '', '', '', '', '', 'autofocus=on'),
  (1, 'title', 'Title', 'show', 'input', 30, 40, 255, '', '', '', '', '', ''),
  (1, 'noteInternal', 'Note', 'show', 'input', 40, '40,3', 0, '', '', '', '', '', ''),
  (1, 'tableName', 'Table', 'show', 'select', 50, 0, 0, '', '', '', '{{!SHOW tables}}', '', ''),

  (1, 'permitNew', 'Permit New', 'show', 'radio', 60, 0, 0, '', '', '', '', '', ''),
  (1, 'permitEdit', 'Permit Edit', 'show', 'radio', 70, 0, 0, '', '', '', '', '', ''),
  (1, 'permitUrlParameter', 'Permit Url Parameter', 'show', 'input', 80, 40, 255, '', '', '', '', '', ''),
  (1, 'render', 'Render', 'show', 'radio', 90, 0, 0, '', '', '', '', '', ''),
  (1, 'multiMode', 'Multi Mode', 'show', 'radio', 100, 0, 0, '', '', '', '', '', ''),
  (1, 'multiSql', 'Multi SQL', 'show', 'input', 110, '40,3', 0, '', '', '', '', '', ''),
  (1, 'multiDetailForm', 'Multi Detail Form', 'show', 'input', 120, 40, 255, '', '', '', '', '', ''),
  (1, 'multiDetailFormParameter', 'Multi Detail Form Parameter', 'show', 'input', 130, 40, 255, '', '', '', '', '', ''),
  (1, 'forwardMode', 'Forward', 'show', 'radio', 140, 0, 0, '', '', '', '', '', ''),
  (1, 'forwardPage', 'Forward Page', 'show', 'input', 150, 40, 255, '', '', '', '', '', ''),
  (1, 'bsLabelColumns', 'BS Label Columns', 'show', 'input', 160, 40, 250, '', '', '', '', '', ''),
  (1, 'bsInputColumns', 'BS Input Columns', 'show', 'input', 160, 40, 250, '', '', '', '', '', ''),
  (1, 'bsNoteColumns', 'BS Note Columns', 'show', 'input', 160, 40, 250, '', '', '', '', '', ''),

  (1, 'deleted', 'Deleted', 'show', 'checkbox', 170, 0, 0, '', '', '', '', '', ''),
  (1, 'modified', 'Modified', 'readonly', 'input', 180, 40, 20, '', '', '', '', '', ''),
  (1, 'created', 'Created', 'readonly', 'input', 180, 40, 20, '', '', '', '', '', ''),

  (1, '', 'FormElements', 'show', 'subrecord', 200, 0, 0, '', '', '', '{{!SELECT * FROM FormElement WHERE formId=1}}',
   '', '');