CREATE TABLE `def_package` (
`Package` VARCHAR(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Archetype` VARCHAR(100) DEFAULT NULL,
`Name` VARCHAR(255) DEFAULT NULL,
`Description` MEDIUMTEXT DEFAULT NULL,
`Notes` MEDIUMTEXT DEFAULT NULL,
`DefaultLogo` VARCHAR(512) DEFAULT NULL,
`LogoData` LONGTEXT DEFAULT NULL,
`DefaultPallete` VARCHAR(255) DEFAULT NULL,
`DefaultHome` VARCHAR(255) DEFAULT NULL,
`DefaultTemplate` VARCHAR(255) DEFAULT NULL,
`DefaultStylesheet` VARCHAR(255) DEFAULT NULL,
`DefaultEmail` VARCHAR(255) DEFAULT NULL,
`DefaultSMSConnection` VARCHAR(255) DEFAULT NULL,
`DefaultDiscordConnection` VARCHAR(255)
DEFAULT NULL,
`UseSession` CHAR(1) DEFAULT 'N',
`SessionValid` INT DEFAULT 24,
`AllowMultipleDevice` CHAR(1) DEFAULT 'Y',
`UseOTP` CHAR(1) DEFAULT NULL,
`OTPMethod` VARCHAR(50) DEFAULT NULL,
`UseGoogle` CHAR(1) DEFAULT NULL,
`UseFacebook` CHAR(1) DEFAULT NULL,
`UseBiometric` CHAR(1) DEFAULT NULL,
`UseAuthenticator` CHAR(1) DEFAULT NULL,
`SSLCert` VARCHAR(512) DEFAULT NULL,
`SSLKey` VARCHAR(512) DEFAULT NULL,
`OneSignalKey` VARCHAR(255) DEFAULT NULL,
`GoogleAnalytics` MEDIUMTEXT DEFAULT NULL,
`Emoji` VARCHAR(255) DEFAULT NULL,
`Signature` MEDIUMTEXT DEFAULT NULL,
`AgentLogo` VARCHAR(512) DEFAULT NULL,
`ContactList` MEDIUMTEXT DEFAULT NULL,
`Version` VARCHAR(50) DEFAULT '1',
`RaciSimulation` TEXT DEFAULT NULL,
`RaciProduction` TEXT DEFAULT NULL,
`RaciOutcome` TEXT DEFAULT NULL,
`RaciDevelopment` TEXT DEFAULT NULL,
PRIMARY KEY (`Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_colour_palette` (
`Palette_name` varchar(255) NOT NULL,
`package` varchar(255) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`primary` varchar(64) DEFAULT NULL,
`secondary` varchar(64) DEFAULT NULL,
`accent` varchar(64) DEFAULT NULL,
`danger` varchar(64) DEFAULT NULL,
`success` varchar(64) DEFAULT NULL,
`info` varchar(64) DEFAULT NULL,
`warning` varchar(64) DEFAULT NULL,
`colour_1` varchar(64) DEFAULT NULL,
`colour_2` varchar(64) DEFAULT NULL,
`colour_3` varchar(64) DEFAULT NULL,
`colour_4` varchar(64) DEFAULT NULL,
`colour_5` varchar(64) DEFAULT NULL,
`colour_6` varchar(64) DEFAULT NULL,
`colour_7` varchar(64) DEFAULT NULL,
`colour_8` varchar(64) DEFAULT NULL,
`colour_9` varchar(64) DEFAULT NULL,
`colour_10` varchar(64) DEFAULT NULL,
`light` varchar(64) DEFAULT NULL,
`dark` varchar(64) DEFAULT NULL,
`background` varchar(64) DEFAULT NULL,
`text` varchar(64) DEFAULT NULL,
`Gray-100` varchar(64) DEFAULT NULL,
`Gray-200` varchar(64) DEFAULT NULL,
`Gray-300` varchar(64) DEFAULT NULL,
`Gray-400` varchar(64) DEFAULT NULL,
`Gray-500` varchar(64) DEFAULT NULL,
`Gray-600` varchar(64) DEFAULT NULL,
`Gray-700` varchar(64) DEFAULT NULL,
`Gray-800` varchar(64) DEFAULT NULL,
`Gray-900` varchar(64) DEFAULT NULL,
`Blue` varchar(64) DEFAULT NULL,
`Indigo` varchar(64) DEFAULT NULL,
`Green` varchar(64) DEFAULT NULL,
`Purple` varchar(64) DEFAULT NULL,
`Pink` varchar(64) DEFAULT NULL,
`Red` varchar(64) DEFAULT NULL,
`Orange` varchar(64) DEFAULT NULL,
`Yellow` varchar(64) DEFAULT NULL,
`Cyan` varchar(64) DEFAULT NULL,
`Teal` varchar(64) DEFAULT NULL,
`Body-Color` varchar(64) DEFAULT NULL,
`Body-Bg` varchar(64) DEFAULT NULL,
`Font` varchar(255) DEFAULT NULL,
`FontUrl` varchar(512) DEFAULT NULL,
`active` char(1) DEFAULT NULL,
`delta_e_threshold` float DEFAULT NULL,
`wcag_level` varchar(10) DEFAULT NULL,
`is_default` char(1) DEFAULT NULL,
`SetName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Palette_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_company` (
`CompanyCode` varchar(255) DEFAULT NULL,
`CompanyName` varchar(255) NOT NULL DEFAULT '',
`Address` varchar(255) DEFAULT NULL,
`City` varchar(100) DEFAULT NULL,
`State` varchar(100) DEFAULT NULL,
`Zip` varchar(20) DEFAULT NULL,
`Phone` varchar(50) DEFAULT NULL,
`Email` varchar(255) DEFAULT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`CompanyName`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_constants` (
`ConstantName` varchar(255) NOT NULL DEFAULT '',
`Value` text DEFAULT NULL,
`Description` text DEFAULT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`ConstantName`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_remoteconnections` (
`remote` char(255) NOT NULL,
`Block` char(100) NOT NULL DEFAULT 'ADHOC',
`Package` char(255) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`company` char(255) NOT NULL DEFAULT 'TECHNOCORE',
`productionCredits` char(255) DEFAULT NULL,
`uat_remoteip` char(255) DEFAULT NULL,
`imapserver` char(255) DEFAULT NULL,
`imapport` char(255) DEFAULT NULL,
`PoolMaxSize` int(11) DEFAULT 25,
`Reachable` char(1) DEFAULT NULL,
`LastLogin` datetime DEFAULT NULL,
`smtpport` char(10) DEFAULT NULL,
`remoteurllast` char(255) DEFAULT NULL,
`VendorId` char(255) DEFAULT NULL,
`uat_remotedatabase` char(255) DEFAULT NULL,
`Infrastructure` char(1) DEFAULT NULL,
`remoteurl_last` char(255) DEFAULT NULL,
`CampaignId` char(255) DEFAULT NULL,
`basedir` char(255) DEFAULT NULL,
`popserver` char(255) DEFAULT NULL,
`Servicemethod` char(255) DEFAULT NULL,
`serviceid` char(10) DEFAULT NULL,
`remoteinitsql` mediumtext DEFAULT NULL,
`remotedir` char(255) DEFAULT NULL,
`username` char(255) DEFAULT NULL,
`remoteurl` char(255) DEFAULT NULL,
`description` char(255) DEFAULT NULL,
`Notes` longtext DEFAULT NULL,
`uat_remotepassword` char(255) DEFAULT NULL,
`remotetype` char(50) DEFAULT 'MYSQL',
`remoteip` char(255) DEFAULT NULL,
`Localdir` char(255) DEFAULT NULL,
`remotepassword` varchar(500) DEFAULT NULL,
`PoolBurstSize` int(11) DEFAULT 50,
`smtpserver` char(255) DEFAULT NULL,
`uat_remoteurl` char(255) DEFAULT NULL,
`uat_username` char(255) DEFAULT NULL,
`Subblock` char(255) DEFAULT NULL,
`remotedatabase` char(255) DEFAULT NULL,
`sshport` char(10) DEFAULT '22',
`Mount` char(255) DEFAULT NULL,
PRIMARY KEY (`remote`, `Block`),
KEY `INDEX` (`company`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_setting` (
`Name` char(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Type` char(255) DEFAULT 'String',
`Context` char(255) NOT NULL DEFAULT 'General',
`Value` char(255) DEFAULT NULL,
PRIMARY KEY (`Name`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_tables` (
`Tablename` char(255) NOT NULL,
`Remoteconnection` char(255) NOT NULL,
`Block` char(100) NOT NULL DEFAULT 'ADHOC',
`Package` char(100) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Company` char(255) DEFAULT NULL,
`CreateTableQuery` blob DEFAULT NULL,
`Description` char(255) DEFAULT NULL,
`Subblock` char(255) DEFAULT NULL,
PRIMARY KEY (`Tablename`, `Remoteconnection`, `Block`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_tableviews` (
`Viewname` char(255) NOT NULL,
`Remoteconnection` char(255) NOT NULL,
`Block` char(100) NOT NULL DEFAULT 'ADHOC',
`Package` char(100) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Description` char(255) DEFAULT NULL,
`CreateViewQuery` blob DEFAULT NULL,
PRIMARY KEY (`Viewname`, `Remoteconnection`, `Block`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_factoryversion` (
`Type` char(50) NOT NULL DEFAULT '',
`Name` char(255) NOT NULL DEFAULT '',
`Version` char(50) NOT NULL DEFAULT '',
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`LastScan` datetime DEFAULT NULL,
`Infrastructure` char(1) DEFAULT '',
`DocString` mediumtext DEFAULT NULL,
`Description` mediumtext DEFAULT NULL,
`LoadError` mediumtext DEFAULT NULL,
PRIMARY KEY (`Type`, `Name`, `Version`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_template` (
`templatename` char(255) NOT NULL,
`templatepart` char(255) NOT NULL,
`Block` char(100) NOT NULL DEFAULT 'ADHOC',
`Package` char(100) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Company` char(255) NOT NULL DEFAULT 'TECHNOCORE',
`templatetext` text DEFAULT NULL,
`templatedescription` varchar(8000) DEFAULT NULL,
PRIMARY KEY (`templatename`, `templatepart`, `Block`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_parameter` (
`Parameter` char(255) NOT NULL,
`Block` char(100) NOT NULL DEFAULT 'ADHOC',
`Package` char(100) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Context` char(255) DEFAULT NULL,
`ConfigGroup` char(255) DEFAULT '',
`Countrycode` char(25) NOT NULL DEFAULT '',
`Description` char(255) DEFAULT NULL,
`Value` mediumtext DEFAULT NULL,
PRIMARY KEY (`Parameter`, `Block`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_substrate` (
`Type` char(50) NOT NULL DEFAULT '',
`Name` char(255) NOT NULL DEFAULT '',
`Version` char(50) NOT NULL DEFAULT '',
`Package` char(50) DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Infrastructure` char(1) DEFAULT '',
`LastScan` datetime DEFAULT NULL,
`Description` mediumtext DEFAULT NULL,
PRIMARY KEY (`Type`, `Name`, `Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_queues` (
`Queuecode` CHAR(255) NOT NULL,
`Strategy_id` CHAR(25) NOT NULL,
`Block` CHAR(36) NOT NULL DEFAULT 'ADHOC',
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`RiskLevel` INT DEFAULT NULL,
`Description` CHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY
(`Queuecode`, `Strategy_id`,
`Block`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
CREATE TABLE IF NOT EXISTS `def_queues_queries` (
`Queuecode` CHAR(255) NOT NULL,
`Strategy_id` CHAR(25) NOT NULL,
`Block` CHAR(36) NOT NULL DEFAULT 'ADHOC',
`Package` VARCHAR(255) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`RiskLevel` INT DEFAULT NULL,
`Description` CHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY
(`Queuecode`, `Strategy_id`,
`Block`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation}
¶ add_column_sql_template
ALTER TABLE {table_name} ADD COLUMN {column_name} {column_definition}
SELECT COUNT(*)
FROM def_form
WHERE formname = '{form_name}'
AND Package = '{package}'
SELECT COUNT(*)
FROM def_formfields
WHERE FormName = '{form_name}'
AND Package = '{package}'
AND FormField = '{form_field}'
¶ get_constant_value_by_name_and_package
SELECT Value
FROM def_constants
WHERE {constant_column} = '{constant_name}'
AND Package IN ('{package}')
LIMIT 1
¶ get_ssl_cert_and_key_by_package
SELECT COALESCE(SSLCert, ''), COALESCE(SSLKey, '')
FROM def_package
WHERE Package = '{package}' = 1
LIMIT 1
¶ update_supervisor_processes_by_service_and_package
UPDATE def_supervisor
SET processes = '{num_workers}'
WHERE Service = '{service}' AND Package = '{package}'
¶ get_supervisor_processes_by_service_and_package
SELECT processes
FROM def_supervisor
WHERE ServiceCode LIKE '{service}'
AND COALESCE(Package,'') IN ('', '{package}')
ORDER BY Package desc
INSERT INTO def_supervisor (ServiceCode, Package, Processes, Note)
VALUES (
'{service}',
'{package}',
'10',
'Workers for Docker or FastAPI deployment'
)
SELECT username, remotepassword
FROM Def_RemoteConnections
WHERE remote = '{remote}'
AND ifnull(Package,'ADHOC') in ('ADHOC','SYSTEM','CORE','{package}')
AND remotetype like '{remote_type}'
RENAME TABLE remoteconnections TO def_Remoteconnections
SELECT remoteurl, username, remotepassword
FROM Def_RemoteConnections
WHERE remote = '{remote}'
AND ifnull(package,'ADHOC') in ('ADHOC','SYSTEM','CORE','{package}')
AND remotetype like '{remote_type}'
UPDATE Def_RemoteConnections
SET remotepassword = '{password}'
WHERE remote = '{remote}'
AND ifnull(package,'ADHOC') in ('ADHOC','SYSTEM','CORE','{package}')
AND remotetype like '{remote_type}'
¶ get_table_column_types
SELECT COLUMN_NAME, data_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = '{table}'
SELECT DATABASE()
SHOW PROCESSLIST
INSERT IGNORE INTO mem_connectioninfo
(ConnectionId, Context, Status, OpenTime)
VALUES (@ConnectionId, 'RECON', 'OPEN', @OpenTime)
UPDATE mem_connectioninfo
SET OpenTime = '{OpenTime}',
remote_connectionName = '{RemoteIp}'
WHERE ConnectionId = '{Id}'
SELECT ConnectionId FROM mem_connectioninfo
DELETE FROM mem_connectioninfo
WHERE ConnectionId = '{ConnectionId}'
UPDATE mem_connectioninfo AS M
LEFT JOIN Internet_resolve I
ON M.remote_connectionName = I.ClientHost
SET ProgramCmd = COALESCE(I.Name, 'Unknown')
WHERE M.ProgramCmd = ''
SELECT emoji
FROM def_package
WHERE Package = '{package}'
LIMIT 1
SELECT COALESCE(Signature, '')
FROM def_package
WHERE Package = '{package}'
LIMIT 1
SELECT COALESCE(DefaultLogo, '')
FROM def_package
WHERE Package = '{package}'
LIMIT 1
SELECT COALESCE(DefaultPallete, '')
FROM def_package
WHERE Package = '{package}'
LIMIT 1
- version_map:
{'develop': 'meridian', 'main': 'axion', 'master': 'axion', 'helix': 'helix'}
- version_default:
prism
- email_signature
- logo_img_tag