CREATE TABLE `def_form` (
`Package` char(36) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Block` char(100) NOT NULL DEFAULT 'ADHOC',
`Subblock` char(255) DEFAULT NULL,
`formname` char(255) NOT NULL,
`Active` char(1) NOT NULL DEFAULT 'Y',
`ActionName` char(255) DEFAULT NULL,
`DiscardName` char(255) DEFAULT NULL,
`EventAction` char(255) DEFAULT NULL,
`SubmitName` char(255) DEFAULT 'Submit',
`Action` char(255) DEFAULT NULL,
`Workflowname_discard` char(255) DEFAULT NULL,
`Workflowname` char(255) DEFAULT NULL,
`NotifySqlRemoteconnection` char(255) DEFAULT NULL,
`Template` char(255) DEFAULT NULL,
`Company` char(50) DEFAULT NULL,
`PostSqlRemoteconnection` char(255) DEFAULT NULL,
`Actiondiscard` char(255) DEFAULT NULL,
`isModal` char(1) DEFAULT 'N',
`bloom_type` char(25) DEFAULT 'N',
`Level` int(11) DEFAULT NULL,
`End` datetime(6) DEFAULT NULL,
`Colour` char(255) DEFAULT NULL,
`readsql` text DEFAULT NULL,
`updatesql` text DEFAULT NULL,
`createsql` text DEFAULT NULL,
`RedirectSql` text DEFAULT NULL,
`NotifySql` text DEFAULT NULL,
`Glyph` char(255) DEFAULT NULL,
`FormType` char(255) DEFAULT NULL,
`Formjs` text DEFAULT NULL,
`FormClass` char(255) DEFAULT NULL,
`FlowNotes` mediumtext DEFAULT NULL,
`FormStyle` mediumtext DEFAULT NULL,
`UserGroups` text NOT NULL DEFAULT 'SYSADMIN',
`LayoutType` char(255) DEFAULT NULL,
`FormBanner` char(255) DEFAULT NULL,
`FormTitle` mediumtext DEFAULT NULL,
`FormWidth` int(11) DEFAULT 12,
`FormHeader` mediumtext DEFAULT NULL,
`FormLeft` mediumtext DEFAULT NULL,
`FormWidthLeft` int(11) DEFAULT 6,
`FormRight` mediumtext DEFAULT NULL,
`FormWidthRight` int(11) DEFAULT 6,
`FormUnder` mediumtext DEFAULT NULL,
`FormButtons` mediumtext DEFAULT NULL,
`FormFooter` mediumtext DEFAULT NULL,
`infrastructure` char(1) DEFAULT NULL,
`ACLSql` text DEFAULT NULL,
`logo` char(255) DEFAULT NULL,
`ControllerSql` text DEFAULT NULL,
`SubmitJS` mediumtext DEFAULT NULL,
`PreRemoteconnection` char(255) DEFAULT NULL,
`Controller` char(255) DEFAULT NULL,
`Remoteconnection` char(255) DEFAULT NULL,
`PostSubSaveJS` mediumtext DEFAULT NULL,
`PostSql` text DEFAULT NULL,
`Validatejsmessage` text DEFAULT NULL,
`Start` datetime(6) DEFAULT NULL,
`DiscardJS` mediumtext DEFAULT NULL,
`UseTabs` char(1) DEFAULT 'N',
`ActionParam3` char(255) DEFAULT NULL,
`ActionParam2` char(255) DEFAULT NULL,
`ActionParam1` char(255) DEFAULT NULL,
`ActionJS` mediumtext DEFAULT NULL,
`Groups` text DEFAULT NULL,
`PreSql` text DEFAULT NULL,
`Savediscard` char(1) NOT NULL DEFAULT 'N',
`Companies` text DEFAULT NULL,
`ValidateJS` mediumtext DEFAULT NULL,
`BaseTable` char(255) DEFAULT NULL,
`Layout` text DEFAULT NULL,
`param1name` char(255) DEFAULT NULL,
`param1descripton` char(255) DEFAULT NULL,
`param1sql` mediumtext DEFAULT NULL,
`param2name` char(255) DEFAULT NULL,
`param2description` char(255) DEFAULT NULL,
`param2sql` mediumtext DEFAULT NULL,
`param3name` char(255) DEFAULT NULL,
`param3description` char(255) DEFAULT NULL,
`param3sql` mediumtext DEFAULT NULL,
`param4name` char(255) DEFAULT NULL,
`param4description` char(255) DEFAULT NULL,
`param4sql` mediumtext DEFAULT NULL,
`param6sql` mediumtext DEFAULT NULL,
`param5description` char(255) DEFAULT NULL,
`param5name` char(255) DEFAULT NULL,
`param5sql` mediumtext DEFAULT NULL,
`param7name` char(255) DEFAULT NULL,
`param7sql` mediumtext DEFAULT NULL,
`param6name` char(255) DEFAULT NULL,
`param6description` char(255) DEFAULT NULL,
`param7description` char(255) DEFAULT NULL,
`SubmitClose` char(1) DEFAULT NULL,
`Description` mediumtext DEFAULT NULL,
`Saveforward` char(1) NOT NULL DEFAULT 'Y',
`PreSqlRemoteconnection` char(255) DEFAULT NULL,
`Notes` mediumtext DEFAULT NULL,
`RecordState` char(255) DEFAULT NULL,
`WebhookAction` char(255) DEFAULT NULL,
`AllowCache` char(1) DEFAULT 'N',
`bloomform` char(1) DEFAULT 'Y',
`PdfCss` char(255) DEFAULT NULL,
`ttl` int(11) DEFAULT NULL,
`on_enter_sound` char(255) DEFAULT NULL,
`on_exit_sound` char(255) DEFAULT NULL,
`on_enter_animation` char(255) DEFAULT NULL,
`on_exit_animation` char(255) DEFAULT NULL,
`key_field` char(255) DEFAULT NULL,
`Next_form_sql` varchar(100) DEFAULT NULL,
PRIMARY KEY (`formname`, `Block`, `Package`)
) {collation};
CREATE TABLE `def_formfields` (
`FormName` char(255) NOT NULL,
`Package` char(36) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`FormField` char(255) NOT NULL,
`Sequence` int(11) DEFAULT 0,
`Parent` char(255) DEFAULT '',
`Peer1` char(255) DEFAULT NULL,
`Peer2` char(255) DEFAULT NULL,
`Peer3` char(255) DEFAULT NULL,
`Active` char(1) DEFAULT '',
`Description` varchar(8000) DEFAULT '',
`Glyph` char(255) DEFAULT 'N',
`Note` mediumtext DEFAULT NULL,
`Placeholder` mediumtext DEFAULT NULL,
`Tooltip` mediumtext DEFAULT NULL,
`FieldHeight` char(255) DEFAULT NULL,
`FieldWidth` int(11) DEFAULT 9,
`FieldAlign` char(255) DEFAULT 'LEFT',
`LabelWidth` int(11) DEFAULT 3,
`FieldType` text DEFAULT NULL,
`Fieldoptions` text DEFAULT NULL,
`DisplayType` char(255) DEFAULT '',
`FieldRow` char(255) DEFAULT '',
`Required` char(255) DEFAULT '',
`Readonly` char(255) DEFAULT 'N',
`Hidden` char(255) DEFAULT NULL,
`Default` char(255) DEFAULT '',
`Query` text DEFAULT NULL,
`Remoteconnection` char(255) DEFAULT NULL,
`SecondaryColour` char(255) DEFAULT '',
`PrimaryColour` char(255) DEFAULT '',
`FieldClass` char(255) DEFAULT '',
`InlineJs` mediumtext DEFAULT NULL,
`InlineCss` mediumtext DEFAULT NULL,
`InlineJsMobile` mediumtext DEFAULT NULL,
`Validation` text DEFAULT NULL,
`ValidationMessage` mediumtext DEFAULT NULL,
`Tag` char(255) DEFAULT '',
`Grouping` char(50) DEFAULT '',
`GroupingWidth` int(11) DEFAULT 12,
`GroupingWeight` int(11) DEFAULT 10,
`Example` char(255) DEFAULT NULL,
PRIMARY KEY (`FormField`, `FormName`, `Package`)
) {collation};
CREATE TABLE `def_form_validations` (
`Block` char(100) NOT NULL DEFAULT 'ADHOC',
`Rank` int(11) NOT NULL DEFAULT 1,
`FormName` char(255) NOT NULL DEFAULT '',
`Package` char(36) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Infrastructure` char(1) NOT NULL DEFAULT '',
`Description` char(255) DEFAULT '',
`Notes` mediumtext DEFAULT NULL,
`ValidationSql` longtext DEFAULT NULL,
`RemoteConnection` char(255) NOT NULL DEFAULT '',
`ValidationType` char(255) NOT NULL DEFAULT '',
`ValidationNote` mediumtext NOT NULL,
PRIMARY KEY (`FormName`, `Rank`, `Block`, `Package`)
) {collation};
CREATE TABLE `def_formflow` (
`Package` char(100) NOT NULL DEFAULT 'ADHOC',
`Module` VARCHAR(255) DEFAULT NULL,
`formflow` char(255) NOT NULL,
`Notes` longtext DEFAULT NULL,
`FlowNotes` longtext DEFAULT NULL,
`Description` mediumtext DEFAULT NULL,
`DataModel` varchar(255) DEFAULT NULL,
`progress_report` varchar(255) DEFAULT NULL,
`completion_url` varchar(500) DEFAULT NULL,
PRIMARY KEY (`formflow`, `Package`)
) {collation};
CREATE TABLE `def_formflow_forms` (
`Package` char(100) NOT NULL DEFAULT 'ADHOC',
`Module` VARCHAR(255) DEFAULT NULL,
`formflow` char(255) NOT NULL,
`CancelFormSql` mediumtext DEFAULT NULL,
`Notes` longtext DEFAULT NULL,
`NextFormSql` mediumtext DEFAULT NULL,
`FlowNotes` longtext DEFAULT NULL,
`FlowTitle` char(255) DEFAULT NULL,
`Template` char(255) DEFAULT NULL,
`BackFormSql` mediumtext DEFAULT NULL,
PRIMARY KEY (`formflow`, `Package`)
) {collation};
SELECT * FROM bloom_form_{formname}
WHERE _sys_guid = '{guid}'
OR `{key_field}` = '{guid}'
ORDER BY _Sys_CreateTime DESC
LIMIT 1
SELECT * FROM bloom_form_{formname}
WHERE _sys_formguid = '{form_guid}'
ORDER BY _Sys_CreateTime DESC
LIMIT 1
SELECT * FROM {table_name} LIMIT 1
SELECT * FROM {table_name} WHERE
SELECT FormField
FROM def_formfields
WHERE formname = '{formname}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','ALL','CORE','REFERENCE','SYSTEM')
SELECT COALESCE(`Default`, '')
FROM def_formfields
WHERE formname = '{formname}'
AND FormField = '{field}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','CORE','ALL','SYSTEM')
SELECT COALESCE(`Default`, '')
FROM def_formfields
WHERE formname = '{formname}'
AND FormField = '{field}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','ALL','CORE','REFERENCE','SYSTEM')
SELECT * FROM def_form
WHERE formname = '{formspec}'
AND Package IN ('{package}','{archetype}','CORE','SYSTEM')
SELECT COUNT(*)
FROM def_form_validations
WHERE formname = '{formspec}'
AND Package IN ('{package}','{archetype}','CORE','SYSTEM')
SELECT redirectsql
FROM def_form
WHERE formname = '{formspec}'
AND Package IN ('{package}','{archetype}','CORE','SYSTEM')
SELECT * FROM def_form
WHERE form_name = '{formspec}'
AND Package IN ('{package}','{archetype}','CORE','SYSTEM')
SELECT
COALESCE(on_enter_sound,'') AS OnEnterSound,
COALESCE(on_exit_sound,'') AS OnExitSound,
COALESCE(on_enter_animation,'') AS OnEnterAnimation,
COALESCE(on_exit_animation,'') AS OnExitAnimtion
FROM def_form
WHERE formname = '{formname}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','ALL','CORE','REFERENCE','SYSTEM')
SELECT TRIM(FieldType)
FROM def_formfields
WHERE formname = '{formname}'
AND formfield = '{formfield}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','ALL','CORE','REFERENCE','SYSTEM')
LIMIT 1
SELECT
FormField,
TRIM(FieldType) AS FieldType,
'N' AS Core,
COALESCE(Parent,'') AS Parent,
COALESCE(Hidden,'') AS Hidden
FROM def_formfields F
WHERE formname = '{formname}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','ALL','CORE','REFERENCE','SYSTEM')
ORDER BY IFNULL(Parent,''), Sequence ASC
SELECT FieldType
FROM def_formfields
WHERE form_name = '{formname}'
AND FormField LIKE '{formfield}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','ALL','CORE','REFERENCE','SYSTEM')
SELECT
BaseTable,
COALESCE(Param1Name, ''),
COALESCE(Param2Name, ''),
COALESCE(Param3Name, '')
FROM def_Form
WHERE formname = '{formname}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','ALL','CORE','REFERENCE','SYSTEM')
SELECT TRIM(FieldType)
FROM def_formfields
WHERE formname = '{formname}'
AND FormField = '{field}'
AND COALESCE(Package,'{package}') IN
('{package}','{archetype}','ALL','CORE','REFERENCE','SYSTEM')
LIMIT 1
CREATE TABLE IF NOT EXISTS {table_name}
(
{key_columns_sql},
PRIMARY KEY ({primary_key_sql}) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
ALTER TABLE {table_name} MODIFY COLUMN {column_name} LONGTEXT
CREATE TABLE IF NOT EXISTS {table_name} (
`_sys_Guid` CHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`_sys_CreateTime` DATETIME NOT NULL,
PRIMARY KEY (`_sys_Guid`, `_sys_CreateTime`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
CREATE TABLE IF NOT EXISTS {table_name} (
`loadedtime` datetime NOT NULL,
`keyhash` varchar(64) NOT NULL,
{key_columns_sql},
`keyname` varchar(255) NOT NULL,
`keyvalue` text NULL,
PRIMARY KEY (`keyhash`,`keyname`),
KEY `idx_loadedtime` (`loadedtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC
ALTER TABLE {table_name} ADD KEY `idx_loadedtime` (`loadedtime`)
INSERT IGNORE INTO {table_name}
(loadedtime, `keyhash`, {key_columns}, `keyname`, `keyvalue`)
VALUES
INSERT INTO {table_name}
(
_sys_guid, _sys_createTime, _sys_user_ip,
_sys_user_agent, _sys_user_referer, _sys_submitsql
SELECT 1 FROM {table_name}
WHERE {where_clause}
LIMIT 1
INSERT INTO {table_name} (
VALUES (
UPDATE {table_name} SET
WHERE
SELECT 1 FROM {table_name}
WHERE {key_where}
AND ({verify_conditions})
LIMIT 1
SELECT FormButtons
FROM def_form
WHERE formname = '{formname}'
AND COALESCE(Package, '{package}') IN ('{package}', '{archetype}', 'CORE', 'SYSTEM')
UPDATE def_form
SET layout = '{layout}'
WHERE formname = '{formname}'
AND COALESCE(Package,'{package}') IN ('{package}','{archetype}','CORE','SYSTEM')
¶ show_columns_from_table
SHOW COLUMNS FROM {table_name}
REPLACE INTO def_formfields
(
FormName, Package, FormField, ReadOnly, Description,
FieldType, Fieldwidth, Labelwidth, Glyph, `Default`
)
VALUES
(
'{form_name}', '{package}', '{field_name}', '{read_only}', '{description}',
'STRING', '6', '3', '{glyph}', '{default_value}'
)
¶ select_primary_columns_for_table
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.statistics
WHERE table_schema = '{schema_name}'
AND table_name = '{table_name}'
AND INDEX_NAME = 'PRIMARY'
ORDER BY SEQ_IN_INDEX
REPLACE INTO def_form ({cols})
VALUES ({vals})