# 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',
`fileStats` 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`),
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 DEFAULT '0',
`feIdContainer` INT(11) NOT NULL DEFAULT '0',
`dynamicUpdate` ENUM ('yes', 'no') NOT NULL DEFAULT 'no',
`encryption` ENUM ('yes', 'no') NOT NULL DEFAULT 'no',
`encryptionMethod` ENUM ('Default', 'AES-128','AES-256') NOT NULL DEFAULT 'Default',
`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', 'single tick') 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 DEFAULT '0',
`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;
# 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(2048) NOT NULL DEFAULT '',
`attach` VARCHAR(4096) 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 DEFAULT '0',
`formName` VARCHAR(255) NOT NULL,
`recordId` INT(11) NOT NULL DEFAULT '0',
`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;
# AutoCRON
CREATE TABLE IF NOT EXISTS `Cron`
(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`grId` INT(11) NOT NULL DEFAULT '0',
`xId` INT(11) NOT NULL DEFAULT '0',
`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;
CREATE TABLE IF NOT EXISTS `Split`
(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`tableName` VARCHAR(255) NOT NULL,
`xId` INT(11) NOT NULL DEFAULT '0',
`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;
# Used to save tablesorter.js column selection settings. Free to use for other settings as well.
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 DEFAULT '0',
`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;