|
1
|
# Before MariaDB 10.2.1, 'text' column could not have a 'default' value.
|
|
2
|
# To not be forced to specify each text column on INSERT() statements, switch off strict checking:
|
|
3
|
SET sql_mode = "NO_ENGINE_SUBSTITUTION";
|
|
4
|
|
|
5
|
CREATE TABLE IF NOT EXISTS `Form`
|
|
6
|
(
|
|
7
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
8
|
`name` VARCHAR(255) NOT NULL DEFAULT '',
|
|
9
|
`title` VARCHAR(1023) NOT NULL DEFAULT '',
|
|
10
|
`noteInternal` TEXT NOT NULL,
|
|
11
|
`tableName` VARCHAR(255) NOT NULL DEFAULT '',
|
|
12
|
`primaryKey` VARCHAR(255) NOT NULL DEFAULT '',
|
|
13
|
|
|
14
|
`permitNew` ENUM ('sip', 'logged_in', 'logged_out', 'always', 'never') NOT NULL DEFAULT 'sip',
|
|
15
|
`permitEdit` ENUM ('sip', 'logged_in', 'logged_out', 'always', 'never') NOT NULL DEFAULT 'sip',
|
|
16
|
`restMethod` SET ('get', 'post', 'put', 'delete') NOT NULL DEFAULT '',
|
|
17
|
`escapeTypeDefault` VARCHAR(32) NOT NULL DEFAULT 'c',
|
|
18
|
`render` ENUM ('bootstrap', 'table', 'plain') NOT NULL DEFAULT 'bootstrap',
|
|
19
|
`requiredParameterNew` VARCHAR(255) NOT NULL DEFAULT '',
|
|
20
|
`requiredParameterEdit` VARCHAR(255) NOT NULL DEFAULT '',
|
|
21
|
`dirtyMode` ENUM ('exclusive', 'advisory', 'none') NOT NULL DEFAULT 'exclusive',
|
|
22
|
`showButton` SET ('new', 'delete', 'close', 'save') NOT NULL DEFAULT 'new,delete,close,save',
|
|
23
|
`multiMode` ENUM ('none', 'horizontal', 'vertical') NOT NULL DEFAULT 'none',
|
|
24
|
`multiSql` TEXT NOT NULL,
|
|
25
|
`multiDetailForm` VARCHAR(255) NOT NULL DEFAULT '',
|
|
26
|
`multiDetailFormParameter` VARCHAR(255) NOT NULL DEFAULT '',
|
|
27
|
|
|
28
|
`forwardMode` ENUM ('auto', 'close', 'no', 'url', 'url-skip-history', 'url-sip', 'url-sip-skip-history') NOT NULL DEFAULT 'auto',
|
|
29
|
|
|
30
|
|
|
31
|
`forwardPage` VARCHAR(511) NOT NULL DEFAULT '',
|
|
32
|
|
|
33
|
`labelAlign` ENUM ('default', 'left', 'center', 'right') NOT NULL DEFAULT 'default',
|
|
34
|
`bsLabelColumns` VARCHAR(255) NOT NULL DEFAULT '',
|
|
35
|
`bsInputColumns` VARCHAR(255) NOT NULL DEFAULT '',
|
|
36
|
`bsNoteColumns` VARCHAR(255) NOT NULL DEFAULT '',
|
|
37
|
|
|
38
|
`parameter` TEXT NOT NULL,
|
|
39
|
`parameterLanguageA` TEXT NOT NULL,
|
|
40
|
`parameterLanguageB` TEXT NOT NULL,
|
|
41
|
`parameterLanguageC` TEXT NOT NULL,
|
|
42
|
`parameterLanguageD` TEXT NOT NULL,
|
|
43
|
`recordLockTimeoutSeconds` INT(11) NOT NULL DEFAULT 900,
|
|
44
|
|
|
45
|
`deleted` ENUM ('yes', 'no') NOT NULL DEFAULT 'no',
|
|
46
|
`fileStats` VARCHAR(255) NOT NULL DEFAULT '',
|
|
47
|
`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
48
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
49
|
|
|
50
|
PRIMARY KEY (`id`),
|
|
51
|
KEY `name` (`name`),
|
|
52
|
KEY `name_deleted` (`name`, `deleted`)
|
|
53
|
)
|
|
54
|
ENGINE = InnoDB
|
|
55
|
DEFAULT CHARSET = utf8
|
|
56
|
AUTO_INCREMENT = 1000;
|
|
57
|
|
|
58
|
|
|
59
|
CREATE TABLE IF NOT EXISTS `FormElement`
|
|
60
|
(
|
|
61
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
62
|
`formId` INT(11) NOT NULL,
|
|
63
|
`feIdContainer` INT(11) NOT NULL DEFAULT '0',
|
|
64
|
`dynamicUpdate` ENUM ('yes', 'no') NOT NULL DEFAULT 'no',
|
|
65
|
|
|
66
|
`enabled` ENUM ('yes', 'no') NOT NULL DEFAULT 'yes',
|
|
67
|
|
|
68
|
`name` VARCHAR(255) NOT NULL DEFAULT '',
|
|
69
|
`label` VARCHAR(1023) NOT NULL DEFAULT '',
|
|
70
|
|
|
71
|
`mode` ENUM ('show', 'required', 'readonly', 'hidden') NOT NULL DEFAULT 'show',
|
|
72
|
`modeSql` TEXT NOT NULL,
|
|
73
|
`class` ENUM ('native', 'action', 'container') NOT NULL DEFAULT 'native',
|
|
74
|
`type` ENUM ('checkbox', 'date', 'datetime', 'dateJQW', 'datetimeJQW', 'extra',
|
|
75
|
'gridJQW', 'text', 'editor', 'annotate', 'time', 'note', 'password', 'radio', 'select',
|
|
76
|
'subrecord', 'upload', 'imageCut', 'fieldset', 'pill', 'templateGroup', 'beforeLoad',
|
|
77
|
'beforeSave', 'beforeInsert', 'beforeUpdate', 'beforeDelete', 'afterLoad', 'afterSave',
|
|
78
|
'afterInsert', 'afterUpdate', 'afterDelete', 'sendMail', 'paste') NOT NULL DEFAULT 'text',
|
|
79
|
`subrecordOption` SET ('edit', 'delete', 'new') NOT NULL DEFAULT '',
|
|
80
|
`encode` ENUM ('none', 'specialchar') NOT NULL DEFAULT 'specialchar',
|
|
81
|
`checkType` ENUM ('auto', 'alnumx', 'digit', 'numerical', 'email', 'pattern', 'allbut',
|
|
82
|
'all') NOT NULL DEFAULT 'auto',
|
|
83
|
`checkPattern` VARCHAR(255) NOT NULL DEFAULT '',
|
|
84
|
|
|
85
|
`onChange` VARCHAR(255) NOT NULL DEFAULT '',
|
|
86
|
|
|
87
|
`ord` INT(11) NOT NULL DEFAULT '0',
|
|
88
|
`tabindex` INT(11) NOT NULL DEFAULT '0',
|
|
89
|
|
|
90
|
`size` VARCHAR(255) NOT NULL DEFAULT '',
|
|
91
|
`maxLength` VARCHAR(255) NOT NULL DEFAULT '',
|
|
92
|
`labelAlign` ENUM ('default', 'left', 'center', 'right') NOT NULL DEFAULT 'default',
|
|
93
|
`bsLabelColumns` VARCHAR(255) NOT NULL DEFAULT '',
|
|
94
|
`bsInputColumns` VARCHAR(255) NOT NULL DEFAULT '',
|
|
95
|
`bsNoteColumns` VARCHAR(255) NOT NULL DEFAULT '',
|
|
96
|
`rowLabelInputNote` SET ('row', 'label', '/label', 'input', '/input', 'note', '/note', '/row') NOT NULL DEFAULT 'row,label,/label,input,/input,note,/note,/row',
|
|
97
|
`note` TEXT NOT NULL,
|
|
98
|
`adminNote` TEXT NOT NULL,
|
|
99
|
`tooltip` VARCHAR(255) NOT NULL DEFAULT '',
|
|
100
|
`placeholder` VARCHAR(2048) NOT NULL DEFAULT '',
|
|
101
|
|
|
102
|
`value` TEXT NOT NULL,
|
|
103
|
`sql1` TEXT NOT NULL,
|
|
104
|
`parameter` TEXT NOT NULL,
|
|
105
|
`parameterLanguageA` TEXT NOT NULL,
|
|
106
|
`parameterLanguageB` TEXT NOT NULL,
|
|
107
|
`parameterLanguageC` TEXT NOT NULL,
|
|
108
|
`parameterLanguageD` TEXT NOT NULL,
|
|
109
|
`clientJs` TEXT NOT NULL,
|
|
110
|
|
|
111
|
`feGroup` VARCHAR(255) NOT NULL DEFAULT '',
|
|
112
|
`deleted` ENUM ('yes', 'no') NOT NULL DEFAULT 'no',
|
|
113
|
`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
114
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
115
|
|
|
116
|
PRIMARY KEY (`id`),
|
|
117
|
KEY `formId` (`formId`),
|
|
118
|
KEY `formId_class_enabled_deleted` (`formId`, `class`, `enabled`, `deleted`),
|
|
119
|
KEY `feIdContainer` (`feIdContainer`),
|
|
120
|
KEY `ord` (`ord`),
|
|
121
|
KEY `feGroup` (`feGroup`)
|
|
122
|
|
|
123
|
)
|
|
124
|
ENGINE = InnoDB
|
|
125
|
DEFAULT CHARSET = utf8
|
|
126
|
AUTO_INCREMENT = 0;
|
|
127
|
|
|
128
|
|
|
129
|
CREATE TABLE IF NOT EXISTS `Dirty`
|
|
130
|
(
|
|
131
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
132
|
`sip` VARCHAR(255) NOT NULL,
|
|
133
|
`tableName` VARCHAR(255) NOT NULL,
|
|
134
|
`recordId` INT(11) NOT NULL,
|
|
135
|
`expire` DATETIME NOT NULL,
|
|
136
|
`recordHashMd5` CHAR(32) NOT NULL,
|
|
137
|
`tabUniqId` CHAR(32) NOT NULL,
|
|
138
|
`feUser` VARCHAR(255) NOT NULL,
|
|
139
|
`qfqUserSessionCookie` VARCHAR(255) NOT NULL,
|
|
140
|
`dirtyMode` ENUM ('exclusive', 'advisory', 'none') NOT NULL DEFAULT 'exclusive',
|
|
141
|
`remoteAddress` VARCHAR(45) NOT NULL,
|
|
142
|
`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
143
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
144
|
|
|
145
|
PRIMARY KEY (`id`),
|
|
146
|
KEY `sip` (`sip`),
|
|
147
|
KEY `tableName` (`tableName`),
|
|
148
|
KEY `recordId` (`recordId`)
|
|
149
|
)
|
|
150
|
ENGINE = InnoDB
|
|
151
|
DEFAULT CHARSET = utf8
|
|
152
|
AUTO_INCREMENT = 0;
|
|
153
|
|
|
154
|
|
|
155
|
# MailLog
|
|
156
|
CREATE TABLE IF NOT EXISTS `MailLog`
|
|
157
|
(
|
|
158
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
159
|
`grId` INT(11) NOT NULL DEFAULT '0',
|
|
160
|
`xId` INT(11) NOT NULL DEFAULT '0',
|
|
161
|
`xId2` INT(11) NOT NULL DEFAULT '0',
|
|
162
|
`xId3` INT(11) NOT NULL DEFAULT '0',
|
|
163
|
`receiver` TEXT NOT NULL,
|
|
164
|
`cc` TEXT NOT NULL,
|
|
165
|
`bcc` TEXT NOT NULL,
|
|
166
|
`sender` VARCHAR(255) NOT NULL DEFAULT '',
|
|
167
|
`subject` VARCHAR(255) NOT NULL DEFAULT '',
|
|
168
|
`body` TEXT NOT NULL,
|
|
169
|
`header` VARCHAR(2048) NOT NULL DEFAULT '',
|
|
170
|
`attach` VARCHAR(4096) NOT NULL DEFAULT '',
|
|
171
|
`src` VARCHAR(255) NOT NULL DEFAULT '',
|
|
172
|
`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
173
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
174
|
|
|
175
|
PRIMARY KEY (`id`)
|
|
176
|
)
|
|
177
|
ENGINE = InnoDB
|
|
178
|
DEFAULT CHARSET = utf8
|
|
179
|
AUTO_INCREMENT = 0;
|
|
180
|
|
|
181
|
CREATE TABLE IF NOT EXISTS `FormSubmitLog`
|
|
182
|
(
|
|
183
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
184
|
`formData` TEXT NOT NULL,
|
|
185
|
`sipData` TEXT NOT NULL,
|
|
186
|
`clientIp` VARCHAR(64) NOT NULL,
|
|
187
|
`feUser` VARCHAR(64) NOT NULL,
|
|
188
|
`userAgent` TEXT NOT NULL,
|
|
189
|
`formId` INT(11) NOT NULL,
|
|
190
|
`formName` VARCHAR(255) NOT NULL,
|
|
191
|
`recordId` INT(11) NOT NULL,
|
|
192
|
`pageId` INT NOT NULL,
|
|
193
|
`sessionId` VARCHAR(32) NOT NULL,
|
|
194
|
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
195
|
|
|
196
|
PRIMARY KEY (`id`),
|
|
197
|
INDEX (`feUser`),
|
|
198
|
INDEX (`formId`)
|
|
199
|
)
|
|
200
|
ENGINE = InnoDB
|
|
201
|
DEFAULT CHARSET = utf8
|
|
202
|
AUTO_INCREMENT = 0;
|
|
203
|
|
|
204
|
|
|
205
|
CREATE TABLE IF NOT EXISTS `Clipboard`
|
|
206
|
(
|
|
207
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
208
|
`cookie` VARCHAR(255) NOT NULL DEFAULT '',
|
|
209
|
`formIdPaste` INT(11) NOT NULL DEFAULT '0',
|
|
210
|
`idSrc` INT(11) NOT NULL DEFAULT '0',
|
|
211
|
`xId` INT(11) NOT NULL DEFAULT '0',
|
|
212
|
`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
213
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
214
|
|
|
215
|
PRIMARY KEY (`id`)
|
|
216
|
)
|
|
217
|
ENGINE = InnoDB
|
|
218
|
DEFAULT CHARSET = utf8
|
|
219
|
AUTO_INCREMENT = 0;
|
|
220
|
|
|
221
|
|
|
222
|
# AutoCRON
|
|
223
|
CREATE TABLE IF NOT EXISTS `Cron`
|
|
224
|
(
|
|
225
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
226
|
`grId` INT(11) NOT NULL,
|
|
227
|
`xId` INT(11) NOT NULL,
|
|
228
|
`type` ENUM ('mail', 'website') NOT NULL DEFAULT 'website',
|
|
229
|
`lastRun` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
230
|
`lastStatus` TEXT NOT NULL,
|
|
231
|
`nextRun` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
232
|
`frequency` VARCHAR(32) NOT NULL,
|
|
233
|
`inProgress` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
234
|
`status` ENUM ('enable', 'disable') NOT NULL DEFAULT 'enable',
|
|
235
|
`sql1` TEXT NOT NULL,
|
|
236
|
`content` TEXT NOT NULL,
|
|
237
|
`comment` TEXT NOT NULL,
|
|
238
|
`outputFile` VARCHAR(255) NOT NULL,
|
|
239
|
`outputMode` ENUM ('overwrite', 'append') NOT NULL DEFAULT 'append',
|
|
240
|
`outputPattern` VARCHAR(255) NOT NULL,
|
|
241
|
`autoGenerated` ENUM ('yes', 'no') NOT NULL DEFAULT 'no',
|
|
242
|
`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
243
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
244
|
|
|
245
|
PRIMARY KEY (`id`)
|
|
246
|
)
|
|
247
|
ENGINE = InnoDB
|
|
248
|
AUTO_INCREMENT = 0
|
|
249
|
DEFAULT CHARSET = utf8;
|
|
250
|
|
|
251
|
|
|
252
|
CREATE TABLE IF NOT EXISTS `Split`
|
|
253
|
(
|
|
254
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
255
|
`tableName` VARCHAR(255) NOT NULL,
|
|
256
|
`xId` INT(11) NOT NULL,
|
|
257
|
`pathFileName` VARCHAR(255) NOT NULL,
|
|
258
|
`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
259
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
260
|
|
|
261
|
PRIMARY KEY (`id`)
|
|
262
|
)
|
|
263
|
ENGINE = InnoDB
|
|
264
|
AUTO_INCREMENT = 0
|
|
265
|
DEFAULT CHARSET = utf8;
|
|
266
|
|
|
267
|
CREATE TABLE IF NOT EXISTS `Setting`
|
|
268
|
(
|
|
269
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
270
|
`type` ENUM ('', 'tablesorter') NOT NULL,
|
|
271
|
`readonly` ENUM ('yes', 'no') NOT NULL DEFAULT 'no' COMMENT 'Settings can''t be modified.',
|
|
272
|
`name` VARCHAR(64) NOT NULL,
|
|
273
|
`public` TINYINT(1) NOT NULL,
|
|
274
|
`feUser` VARCHAR(32) NOT NULL COMMENT 'In case there is no logged in user, take QFQ cookie.',
|
|
275
|
`tableId` VARCHAR(64) NOT NULL,
|
|
276
|
`view` TEXT NOT NULL,
|
|
277
|
`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
278
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
279
|
PRIMARY KEY (`id`),
|
|
280
|
KEY `name` (`name`),
|
|
281
|
KEY `typeFeUserUidTableIdPublic` (`type`, `feUser`, `tableId`, `public`) USING BTREE
|
|
282
|
) ENGINE = InnoDB
|
|
283
|
DEFAULT CHARSET = utf8mb4;
|
|
284
|
|
|
285
|
|
