formEditor.sql 48.9 KB
Newer Older
1
2
3
4
# 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";

Carsten  Rose's avatar
Carsten Rose committed
5
6
CREATE TABLE IF NOT EXISTS `Form`
(
7
8
    `id`                       INT(11)                                                                                    NOT NULL AUTO_INCREMENT,
    `name`                     VARCHAR(255)                                                                               NOT NULL DEFAULT '',
9
    `title`                    VARCHAR(1023)                                                                              NOT NULL DEFAULT '',
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
    `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 '',

28
    `forwardMode`              ENUM ('auto', 'close', 'no', 'url', 'url-skip-history', 'url-sip', 'url-sip-skip-history') NOT NULL DEFAULT 'auto',
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51


    `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',
    `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`)
52
)
53
54
55
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 1000;
56

57

Carsten  Rose's avatar
Carsten Rose committed
58
59
CREATE TABLE IF NOT EXISTS `FormElement`
(
60
61
62
63
    `id`                 INT(11)                                                                    NOT NULL AUTO_INCREMENT,
    `formId`             INT(11)                                                                    NOT NULL,
    `feIdContainer`      INT(11)                                                                    NOT NULL DEFAULT '0',
    `dynamicUpdate`      ENUM ('yes', 'no')                                                         NOT NULL DEFAULT 'no',
Carsten  Rose's avatar
Carsten Rose committed
64

65
    `enabled`            ENUM ('yes', 'no')                                                         NOT NULL DEFAULT 'yes',
Carsten  Rose's avatar
Carsten Rose committed
66

67
68
    `name`               VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `label`              VARCHAR(1023)                                                              NOT NULL DEFAULT '',
Carsten  Rose's avatar
Carsten Rose committed
69

70
71
72
73
    `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',
Carsten  Rose's avatar
Carsten Rose committed
74
75
76
        'gridJQW', 'text', 'editor', 'annotate', 'time', 'note', 'password', 'radio', 'select',
        'subrecord', 'upload', 'imageCut', 'fieldset', 'pill', 'templateGroup', 'beforeLoad',
        'beforeSave', 'beforeInsert', 'beforeUpdate', 'beforeDelete', 'afterLoad', 'afterSave',
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
        'afterInsert', 'afterUpdate', 'afterDelete', 'sendMail', 'paste')                           NOT NULL DEFAULT 'text',
    `subrecordOption`    SET ('edit', 'delete', 'new')                                              NOT NULL DEFAULT '',
    `encode`             ENUM ('none', 'specialchar')                                               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,
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
    `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`)
121

122
)
123
124
125
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;
126

127

Carsten  Rose's avatar
Carsten Rose committed
128
129
CREATE TABLE IF NOT EXISTS `Dirty`
(
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
    `id`                   INT(11)                                NOT NULL AUTO_INCREMENT,
    `sip`                  VARCHAR(255)                           NOT NULL,
    `tableName`            VARCHAR(255)                           NOT NULL,
    `recordId`             INT(11)                                NOT NULL,
    `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`)
Carsten  Rose's avatar
Carsten Rose committed
148
)
149
150
151
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;
Carsten  Rose's avatar
Carsten Rose committed
152

153
# Delete previous FormElements (if exist) of system forms
Carsten  Rose's avatar
Carsten Rose committed
154
155
156
157
158
DELETE FormElement
FROM FormElement,
     Form
WHERE FIND_IN_SET(Form.name, 'form,formElement,copyForm,cron') > 0
  AND Form.id = FormElement.formId;
159
160

# Delete previous Forms (if exist)
Carsten  Rose's avatar
Carsten Rose committed
161
162
DELETE
FROM Form
Carsten  Rose's avatar
Carsten Rose committed
163
WHERE FIND_IN_SET(Form.name, 'form,formElement,copyForm,cron') > 0;
164
165

#
166
# FormEditor: Form
Carsten  Rose's avatar
Carsten Rose committed
167
INSERT INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter)
168
169
170
VALUES (1, 'form', 'Form Editor: {{SELECT id, " / ", name FROM Form WHERE id = {{r:S0}}}} (DB: {{dbNameQfq:Y}})',
        'FormElement Editor',
        'Form', 'sip', 'sip', 'bootstrap', '', 'maxVisiblePill=5\nclass=container-fluid\ndbIndex={{indexQfq:Y}}');
171

172
# FormEditor: FormElements for 'form'
173
INSERT INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, note, clientJs, value,
174
                         sql1, parameter, feIdContainer, subrecordOption, modeSql, placeholder)
175
176
177
178
179
VALUES (1, 1, 'basic', 'Basic', 'show', 'pill', 'all', 'container', 100, 0, '', '', '', '', '', 0, '', '', ''),
       (2, 1, 'formelement', 'Formelement', 'show', 'pill', 'all', 'container', 200, 0, '', '', '', '', '', 0, '', '',
        ''),
       (3, 1, 'layout', 'Layout', 'show', 'pill', 'all', 'container', 300, 0, '', '', '', '', '', 0, '', '', ''),
       (4, 1, 'access', 'Access', 'show', 'pill', 'all', 'container', 400, 0, '', '', '', '', '', 0, '', '', ''),
180
       (5, 1, 'multi', 'Multi', 'show', 'pill', 'all', 'container', 500, 0, '', '', '', '', '', 0, '', '', '');
181
182
183

# FormEditor: FormElements for 'form'
INSERT INTO FormElement (formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs, value,
Carsten  Rose's avatar
Carsten Rose committed
184
185
                         sql1, parameter, feIdContainer, subrecordOption, modeSql, placeholder, encode, dynamicUpdate,
                         checkPattern)
186
VALUES
187
188
    # Make the form a 'delete form' for records Form/FormElement.
    (1, '', 'Delete FE', 'show', 'beforeDelete', 'all', 'action', 10, 0, 0, '', '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
189
     'sqlAfter={{DELETE FROM FormElement WHERE formId={{id:R}} }}', 0, '', '', '', 'none', 'no', ''),
190
191
192
193
194
195
196
197

    # Check for double form name
    (1, '', 'Check for double form name', 'show', 'beforeSave', 'all', 'action', 20, 0, 0, '', '', '', '',
     'sqlValidate={{!SELECT f.id FROM Form AS f WHERE  f.name!="" AND f.name="{{name:F:alnumx}}" AND f.id!={{id:R0}}  }}\nexpectRecords=0\nmessageFail=There is already another form with the name "{{name:F:alnumx}}".',
     0, '', '', '', 'none', 'no', ''),

    # Basic
    (1, 'name', 'Name', 'required', 'text', 'pattern', 'native', 110, 0, 0,
198
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-name">Info</a>', '', '', '',
199
200
201
     'autofocus\ndata-pattern-error=Allowed characters: alphabet, number or . - +',
     1, '', '', '', 'specialchar', 'no', '[a-zA-Z0-9._+-]+'),
    (1, 'title', 'Title', 'show', 'text', 'all', 'native', 120, '80,1,350', 0,
202
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-title">Info</a>',
203
204
     '', '', '', '', 1, '', '', '', 'none', 'no', ''),
    (1, 'noteInternal', 'Note', 'show', 'text', 'all', 'native', 130, '80,1,350', 0,
205
206
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-note">Info</a>', '', '', '', '', 1, '', '', '',
     'specialchar',
Carsten  Rose's avatar
Carsten Rose committed
207
     'no', ''),
208
    (1, 'tableName', 'Table', 'required', 'select', 'all', 'native', 140, 0, 0,
209
210
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-tablename">Info</a>', '', '',
     '{{[{{indexData:Y}}]!SHOW tables}}',
211
212
213
     'emptyItemAtStart', 1, '', '', '', 'specialchar', 'no', ''),
    (1, 'parameterLanguageA', 'Language: {{formLanguageALabel:YE}}', 'show', 'text', 'all', 'native', 150, '80,1,350',
     0,
214
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multiple-languages">Info</a>', '', '', '', '', 1, '',
215
216
     '{{SELECT IF("{{formLanguageAId:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
    (1, 'parameterLanguageB', 'Language: {{formLanguageBLabel:YE}}', 'show', 'text', 'all', 'native', 160, '80,1,350',
217
     0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multiple-languages">Info</a>', '', '', '', '', 1, '',
218
219
     '{{SELECT IF("{{formLanguageBId:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
    (1, 'parameterLanguageC', 'Language: {{formLanguageCLabel:YE}}', 'show', 'text', 'all', 'native', 170, '80,1,350',
220
     0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multiple-languages">Info</a>', '', '', '', '', 1, '',
221
222
     '{{SELECT IF("{{formLanguageCId:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),
    (1, 'parameterLanguageD', 'Language: {{formLanguageDLabel:YE}}', 'show', 'text', 'all', 'native', 180, '80,1,350',
223
     0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multiple-languages">Info</a>', '', '', '', '', 1, '',
224
225
226
227
228
229
     '{{SELECT IF("{{formLanguageDId:YE}}"="","hidden","show" ) }}', '', 'none', 'no', ''),

    # FormElement
    (1, '', 'FormElements', 'show', 'subrecord', 'all', 'native', 210, 0, 0, '', '', '',
     '{{!SELECT fe.ord AS _ord, IF( fe.enabled="yes", IF( fe.enabled="yes" AND fe.feIdContainer=0 AND !ISNULL(feCX.id) AND fe.class="native", "danger", IF( fe.class="container", "text-info",  IF( fe.class="action", "text-success", ""))), "text-muted") AS _rowClass, IF( fe.enabled="yes", IF(fe.feIdContainer=0 AND !ISNULL(feCX.id) AND fe.class="native", "Please choose a container for this formelement", fe.class), "Disabled") AS _rowTitle, fe.id AS _id, CONCAT(''<span id="qfq-dnd-ord-id-'', fe.id,''">'', fe.ord, ''</span><br><small class="text-muted">'',fe.id, ''</small>'') AS ''Ord|nostrip'', CONCAT( IFNULL( CONCAT( feC.name, " (", fe.feIdContainer, ")"),"")) AS Container, fe.name AS "Name|50", fe.label AS Label, fe.mode AS Mode, fe.class AS Class,	fe.type AS Type, IF(fe.dynamicUpdate="yes", \'<span class="glyphicon glyphicon-random"></span>\',"") AS "Dyn|nostrip" FROM FormElement AS fe LEFT JOIN FormElement AS feC ON feC.id=fe.feIdContainer AND feC.formId=fe.formId LEFT JOIN FormElement AS feCX ON feCX.class="container" AND feCX.enabled="yes" AND feCX.type="pill" AND feCX.formId=fe.formId WHERE fe.formId={{id:R0}} GROUP BY fe.id ORDER BY fe.ord, fe.id}}',
     'form=formElement\ndetail=id:formId\nsubrecordTableClass=table table-hover qfq-subrecord-table qfq-color-grey-2 tablesorter tablesorter-filter tablesorter-column-selector',
Carsten  Rose's avatar
Carsten Rose committed
230
     2, 'new,edit,delete', '', '', 'none', 'no', ''),
231
232
233

    # Layout
    (1, 'showButton', 'Show button', 'show', 'checkbox', 'all', 'native', 220, 0, 5,
234
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#showbutton">Info</a>', '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
235
     'checkBoxMode = multi\norientation=vertical', 3, '', '', '', 'specialchar', 'no', ''),
236
    (1, 'labelAlign', 'Label Align', 'show', 'radio', 'alnumx', 'native', 225, 0, 5,
237
238
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-label-align">Info</a>', '', '', '', 'buttonClass', 3,
     '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
239
     'specialchar', 'no', ''),
240
    (1, 'parameter', 'Parameter', 'show', 'text', 'all', 'native', 230, '80,1,350', 0,
241
242
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-parameter">Info</a>', '', '', '', '', 3, '', '', '',
     'none', 'no',
243
244
     ''),
    (1, 'bsLabelColumns', 'BS Label Columns', 'show', 'text', 'all', 'native', 240, 0, 0,
245
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-layout">Info</a>', '', '', '', '', 3, '', '',
Carsten  Rose's avatar
Carsten Rose committed
246
     '{{bsLabelColumns:Y}}', 'specialchar', 'no', ''),
247
248
249
250
251
252
253
254
    (1, 'bsInputColumns', 'BS Input Columns', 'show', 'text', 'all', 'native', 250, 0, 0, '', '', '', '', '', 3, '', '',
     '{{bsInputColumns:Y}}', 'specialchar', 'no', ''),
    (1, 'bsNoteColumns', 'BS Note Columns', 'show', 'text', 'all', 'native', 260, 0, 0, '', '', '', '', '', 3, '', '',
     '{{bsNoteColumns:Y}}', 'specialchar', 'no', ''),


    # Access
    (1, 'forwardMode', 'Forward', 'show', 'radio', 'all', 'native', 310, 0, 0,
255
256
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-forward">Info</a>', '', '', '',
     'buttonClass=btn-default', 4, '',
Carsten  Rose's avatar
Carsten Rose committed
257
     '', '', 'specialchar', 'no', ''),
258
    (1, 'forwardPage', 'Forward URL / Page', 'show', 'text', 'all', 'native', 320, 0, 0,
259
260
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-forward">Info</a>', '', '', '', '', 4, '', '', '',
     'none', 'no',
Carsten  Rose's avatar
Carsten Rose committed
261
     ''),
262
    (1, 'requiredParameterNew', 'Required Parameter NEW', 'show', 'text', 'all', 'native', 330, 0, 0,
263
264
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#required-parameter-new-edit">Info</a>', '', '', '', '', 4,
     '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
265
     'specialchar', 'no', ''),
266
    (1, 'requiredParameterEdit', 'Required Parameter EDIT', 'show', 'text', 'all', 'native', 340, 0, 0,
267
268
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#required-parameter-new-edit">Info</a>', '', '', '', '', 4,
     '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
269
     'specialchar', 'no', ''),
270
    (1, 'permitNew', 'Permit New', 'show', 'radio', 'all', 'native', 350, 0, 10,
271
272
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-permitnewedit">Info</a>', '', '', '',
     'buttonClass=btn-default',
Carsten  Rose's avatar
Carsten Rose committed
273
     4, '', '', '', 'specialchar', 'no', ''),
274
    (1, 'permitEdit', 'Permit Edit', 'show', 'radio', 'all', 'native', 360, 0, 10,
275
276
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-permitnewedit">Info</a>', '', '', '',
     'buttonClass=btn-default',
Carsten  Rose's avatar
Carsten Rose committed
277
     4, '', '', '', 'specialchar', 'no', ''),
278
279

    (1, 'restMethod', 'Permit REST', 'show', 'checkbox', 'all', 'native', 370, 0, 10,
280
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#rest">Info</a>', '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
281
     'buttonClass=btn-default\nitemList=get,post:insert,put:update,delete', 4, '', '', '', 'specialchar', 'no', ''),
282
283

    (1, 'escapeTypeDefault', 'Escape type default', 'show', 'radio', 'all', 'native', 380, 0, 10,
284
     '<a tabindex="-1" href="{{documentation:Y}}/Variable.html#variable-escape">Info</a>', '', '', '',
285
286
287
     'itemList=c:config,s:single,d:double,l:ldap search,L:ldap value,m:mysql realEscapeString,-:none\nbuttonClass=btn-default',
     4, '', '', '', 'specialchar', 'no', ''),
    (1, 'dirtyMode', 'Record Locking', 'show', 'radio', 'all', 'native', 390, 0, 10,
288
289
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#locking-record">Info</a>', '', '', '',
     'buttonClass=btn-default', 4,
Carsten  Rose's avatar
Carsten Rose committed
290
     '', '', '', 'specialchar', 'no', ''),
291
    (1, 'recordLockTimeoutSeconds', 'Lock timeout (seconds)', 'show', 'text', 'all', 'native', 400, 0, 0,
292
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#locking-record">Info</a>', '',
293
294
295
     '{{SELECT IF("{{recordLockTimeoutSeconds:R0}}"=0,"{{recordLockTimeoutSeconds:Y0}}","{{recordLockTimeoutSeconds:R0}}")}}',
     '', '', 4, '', '', '', 'specialchar', 'no', ''),
    (1, 'primaryKey', 'Primary Key', 'show', 'text', 'all', 'native', 410, 0, 0,
296
     '<a tabindex="-1" href="{{documentation:Y}}/Form.html#form-primary-key">Info</a>', '', '', '', '', 4, '', '', 'id',
297
298
299
300
301
302
     'specialchar', 'no', ''),

    # Multi
    (1, 'multi', 'Multi', 'show', 'fieldset', 'all', 'native', 510, 0, 0, '', '', '', '', '', 5, '', '', '',
     'specialchar', 'no', ''),
    (1, 'multiMode', 'Multi Mode', 'show', 'radio', 'all', 'native', 520, 0, 0, '', '', '', '', 'buttonClass', 5, '',
Carsten  Rose's avatar
Carsten Rose committed
303
     '', '', 'specialchar', 'no', ''),
304
305
306
307
308
309
    (1, 'multiSql', 'Multi SQL', 'show', 'text', 'all', 'native', 530, '80,1,350', 0, '', '', '', '', '', 5, '', '', '',
     'none', 'no', ''),
    (1, 'multiDetailForm', 'Multi Detail Form', 'show', 'text', 'all', 'native', 540, 0, 0, '', '', '', '', '', 5,
     '', '', '', 'specialchar', 'no', ''),
    (1, 'multiDetailFormParameter', 'Multi Detail Form Parameter', 'show', 'text', 'all', 'native', 550, 0, 0, '', '',
     '', '', '', 5, '', '', '', 'none', 'no', '');
310

311
312
313

#
# FormEditor: FormElement
Carsten  Rose's avatar
Carsten Rose committed
314
315
INSERT INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter,
                  requiredParameterNew)
316
VALUES (2, 'formElement',
317
        'Form Element Editor. Form : {{SELECT f.id, " / ",  f.name  FROM Form AS f WHERE f.id = {{formId:SR0}}  }} (DB: {{dbNameQfq:Y}})',
Carsten  Rose's avatar
Carsten Rose committed
318
        'Please secure the form', 'FormElement', 'sip', 'sip', 'bootstrap', '',
319
        'maxVisiblePill=5\nclassBody=qfq-color-blue-1\ndbIndex={{indexQfq:Y}}', 'formId');
320

321
# FormEditor: FormElements for 'formElement'
322
INSERT INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, note, clientJs, value,
323
                         sql1, parameter, feIdContainer, subrecordOption, modeSql)
324
325
326
327
328
VALUES (100, 2, 'basic', 'Basic', 'show', 'pill', 'all', 'container', 10, 0, '', '', '', '', '', 0, '', ''),
       (101, 2, 'check_order', 'Check & Order', 'show', 'pill', 'all', 'container', 290, 0, '', '', '', '', '', 0, '',
        ''),
       (102, 2, 'layout', 'Layout', 'show', 'pill', 'all', 'container', 390, 0, '', '', '', '', '', 0, '', ''),
       (103, 2, 'value', 'Value', 'show', 'pill', 'all', 'container', 490, 0, '', '', '', '', '', 0, '', '');
329

330
INSERT INTO FormElement (formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs, value,
331
332
                         sql1, parameter, feIdContainer, subrecordOption, dynamicUpdate, bsLabelColumns, bsInputColumns,
                         bsNoteColumns, modeSql, placeholder, encode)
333
VALUES (2, 'feIdContainer', 'Container', 'show', 'select', 'all', 'native', 120, 0, 0,
334
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-container">Info</a>', '',
335
336
        '{{feIdContainer:FR:::{{SELECT fe.feIdContainer FROM FormElement AS fe WHERE fe.formId={{formId:SR0}} AND fe.feIdContainer!=0 AND "{{class:FRD0:alnumx}}"!="action" ORDER BY fe.modified DESC LIMIT 1}}}}',
        '{{!SELECT fe.id, CONCAT(fe.type, " / ", fe.name, " (", COUNT(feSub.id), ")" ) FROM FormElement As fe LEFT JOIN FormElement As feSub ON feSub.feIdContainer=fe.id WHERE fe.formId={{formId:SR0}} AND fe.class="container"  AND ("{{class:FRD0:alnumx}}"!="action" OR fe.type="templateGroup") GROUP BY fe.id ORDER BY fe.type, fe.ord, fe.name }}',
Carsten  Rose's avatar
Carsten Rose committed
337
        'emptyItemAtStart', 100, '', 'yes', '', '', '',
338
        '{{SELECT IF(COUNT(fe.id)>0, "show", "hidden") FROM Form AS f LEFT JOIN FormElement AS fe ON f.id=fe.formId AND fe.class="container" WHERE f.id={{formId:S0}} GROUP BY f.id}}',
339
340
        '', 'specialchar'),
       (2, 'enabled', 'Enabled', 'show', 'checkbox', 'all', 'native', 130, 0, 0,
341
342
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '', '', 100, '', 'no',
        '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
343
        '', '', 'specialchar'),
344
       (2, 'dynamicUpdate', 'Dynamic Update', 'show', 'checkbox', 'all', 'native', 135, 0, 0,
345
346
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#dynamic-update">Info</a>', '', '', '', '', 100, '', 'no',
        '', '',
Carsten  Rose's avatar
Carsten Rose committed
347
        '', '', '', 'specialchar'),
348
       (2, 'name', 'Name', 'show', 'text', 'all', 'native', 140, 0, 0,
349
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '',
350
        'typeAheadSql = [{{indexData:Y}}]SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = "{{DB_1_NAME:Y}}" AND table_name = "{{SELECT f.tableName FROM Form AS f WHERE f.id={{formId:S0}}}}" AND COLUMN_NAME LIKE ? ORDER BY COLUMN_NAME\ntypeAheadMinLength = 1\ntypeAheadLimit = 100\ntypeAheadPedantic = 0\n',
351
        100, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', 'no', '', '', '', '', '',
352
        'specialchar'),
353
       (2, 'label', 'Label', 'show', 'text', 'all', 'native', 150, 0, 0,
354
355
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '', '', 100, '', 'no',
        '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
356
        '', '', 'none'),
357
       (2, 'mode', 'Mode', 'show', 'radio', 'all', 'native', 160, 0, 0,
358
359
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '',
        'buttonClass=btn-default', 100,
Carsten  Rose's avatar
Carsten Rose committed
360
        '', 'no', '', '', '', '', '', 'specialchar'),
361
       (2, 'modeSql', 'Mode sql', 'show', 'text', 'all', 'native', 170, '80,1,350', 0,
362
363
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#dynamic-update">Info</a>', '', '', '', '', 100, '', 'no',
        '', '',
Carsten  Rose's avatar
Carsten Rose committed
364
        '', '', '', 'none'),
365
       (2, 'class', 'Class', 'show', 'radio', 'all', 'native', 180, 0, 0,
366
367
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-container">Info</a>', '', '{{class:FSRD0:alnumx}}',
        '',
Carsten  Rose's avatar
Carsten Rose committed
368
        'buttonClass', 100, '', 'yes', '', '', '', '', '', 'none'),
369
370

       (2, 'type', 'Type', 'show', 'select', 'all', 'native', 190, 0, 0,
371
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Native</a>, <a tabindex="-1" href="{{documentation:Y}}/Form.html#class-action">Action</a>, <a tabindex="-1" href="{{documentation:Y}}/Form.html#class-container">Container</a>',
372
373
374
375
        '', '', '',
        'itemList={{SELECT IF( "{{class:FRD0:alnumx}}"="native","checkbox,date,time,datetime,dateJQW,datetimeJQW,extra,gridJQW,text,editor,annotate,imageCut,note,password,radio,select,subrecord,upload", IF("{{class:FRD0:alnumx}}"="action","beforeLoad,beforeSave,beforeInsert,beforeUpdate,beforeDelete,afterLoad,afterSave,afterInsert,afterUpdate,afterDelete,sendMail,paste", "fieldset,pill,templateGroup")  ) }}',
        100, '', 'yes', '', '', '', '', '', 'specialchar'),
       (2, 'subrecordOption', 'Subrecord Option', 'show', 'checkbox', 'all', 'native', 200, 0, 0,
376
377
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#subrecord-option">Info</a>', '', '', '', '', 100, '',
        'yes', '', '',
Carsten  Rose's avatar
Carsten Rose committed
378
379
        '', '{{ SELECT IF("{{type:FRE:alnumx}}"="subrecord" AND "{{class:FRE:alnumx}}"="native", "show", "hidden") }}',
        '', 'specialchar'),
380
       (2, 'parameterLanguageA', 'Language: {{formLanguageALabel:YE}}', 'show', 'text', 'all', 'native', 210,
381
382
        '80,1,350', 0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multi-language-form">Info</a>', '', '', '',
        '', 100,
Carsten  Rose's avatar
Carsten Rose committed
383
        '', 'no', '', '', '', '{{SELECT IF("{{formLanguageAId:YE}}"="","hidden","show" ) }}', '', 'none'),
384
       (2, 'parameterLanguageB', 'Language: {{formLanguageBLabel:YE}}', 'show', 'text', 'all', 'native', 210,
385
386
        '80,1,350', 0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multi-language-form">Info</a>', '', '', '',
        '', 100,
Carsten  Rose's avatar
Carsten Rose committed
387
        '', 'no', '', '', '', '{{SELECT IF("{{formLanguageBId:YE}}"="","hidden","show" ) }}', '', 'none'),
388
389
       (2, 'parameterLanguageC', 'Language: {{formLanguageCLabel:YE}}', 'show', 'text', 'all', 'native', 210,
        '80,1,350',
390
391
        0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multi-language-form">Info</a>', '', '', '', '', 100,
        '', 'no',
Carsten  Rose's avatar
Carsten Rose committed
392
        '', '', '', '{{SELECT IF("{{formLanguageCId:YE}}"="","hidden","show" ) }}', '', 'none'),
393
       (2, 'parameterLanguageD', 'Language: {{formLanguageDLabel:YE}}', 'show', 'text', 'all', 'native', 210,
394
395
        '80,1,350', 0, '<a tabindex="-1" href="{{documentation:Y}}/Form.html#multi-language-form">Info</a>', '', '', '',
        '', 100,
Carsten  Rose's avatar
Carsten Rose committed
396
        '', 'no', '', '', '', '{{SELECT IF("{{formLanguageDId:YE}}"="","hidden","show" ) }}', '', 'none'),
397
398

       (2, 'encode', 'Encode', 'show', 'radio', 'all', 'native', 300, 0, 0,
399
        '<a href="{{documentation:Y}}/Form.html#field-encode">Info</a> {{SELECT IF(''{{encode:FRE:alnumx}}'' = ''specialchar'' AND ''{{checkType:FRE:alnumx}}'' = ''allbut'', "<br><span class=''text-danger''> <span class=''glyphicon glyphicon-warning-sign''></span> <span>Encode ''specialchar'' is not completely compatible with Check Type ''allbut'': certain user input will be converted to its HTML representation (e.g. &amp;quot;), which makes use of a non-supported character (&amp;)</span></span>", '''') }}',
Carsten  Rose's avatar
Carsten Rose committed
400
        '', '', '', 'buttonClass=btn-default', 101, '', 'yes', '', '', '', '', '', 'specialchar'),
Carsten  Rose's avatar
Carsten Rose committed
401
402


403
       (2, 'checkType', 'Check Type', 'show', 'radio', 'all', 'native', 310, 0, 0,
404
405
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-checktype">Info</a>', '', '', '',
        'buttonClass=btn-default',
Carsten  Rose's avatar
Carsten Rose committed
406
        101, '', 'yes', '', '', '', '', '', 'specialchar'),
407
       (2, 'checkPattern', 'Check Pattern', 'show', 'text', 'all', 'native', 320, 0, 0,
408
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-checkpattern">Info</a>, <a tabindex="-1" href="https://regex101.com/">Regex101</a>',
Carsten  Rose's avatar
Carsten Rose committed
409
        '', '', '', '', 101, '', 'yes', '', '', '',
410
411
412
413
        '{{ SELECT IF("{{checkType:FRE:alnumx}}"="pattern" OR "{{checkType:FRE:allbut}}" LIKE "min%", "show", "hidden") }}',
        '', 'none'),
       #(2, 'onChange', 'JS onChange', 'show', 'text', 'all', 'native', 330, 0, 0, '', '', '', '', '', 101, '', 'no', '', '', '', '', '', 'none'),
       (2, 'ord', 'Order', 'show', 'text', 'all', 'native', 340, 0, 0,
414
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-ord">Info</a>', '',
415
416
        '{{SELECT IF({{ord:R0}}=0,  MAX(IFNULL(fe.ord,0))+10,{{ord:R0}})  FROM (SELECT 1) AS a LEFT JOIN FormElement AS fe ON fe.formId={{formId:S0}} GROUP BY fe.formId}}',
        '', '', 101, '', 'no', '', '', '', '', '', 'specialchar'),
417
       #        (2, 'tabindex', 'tabindex', 'show', 'text', 'all', 'native', 350, 0, 0,
418
       #         '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-tabindex">Info</a>', '', '', '', '', 101, '', 'no', '', '',
419
420
       #         '', '', '',
       #         'specialchar'),
421
       (2, 'adminNote', 'Internal Note', 'show', 'text', 'all', 'native', 360, '80,1,350', 0, '', '', '', '', '', 101,
Carsten  Rose's avatar
Carsten Rose committed
422
        '', 'no', '', '', '', '', '', 'specialchar'),
423
424

       (2, 'labelAlign', 'Label Align', 'show', 'radio', 'all', 'native', 400, 0, 0,
425
426
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#class-native">Info</a>', '', '', '',
        'buttonClass=btn-default', 102,
Carsten  Rose's avatar
Carsten Rose committed
427
        '', 'no', '', '', '', '', '', 'specialchar'),
428
       (2, 'size', 'Size', 'show', 'text', 'all', 'native', 405, 0, 0,
429
430
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-size">Info</a>', '', '', '', '', 102, '', 'no', '',
        '', '',
Carsten  Rose's avatar
Carsten Rose committed
431
        '', '', 'specialchar'),
432
       (2, 'bsLabelColumns', 'BS Label Columns', 'show', 'text', 'all', 'native', 410, 0, 0,
433
434
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-bslabelcolumns">Info</a>', '', '', '', '', 102, '',
        'no', '',
435
        '', '', '',
436
437
438
        '{{SELECT IF(f.bsLabelColumns != '''', f.bsLabelColumns, ''{{bsLabelColumns:Y}}'') FROM Form AS f WHERE f.id = {{formId}} }}',
        'specialchar'),
       (2, 'bsInputColumns', 'BS Input Columns', 'show', 'text', 'all', 'native', 420, 0, 0, '', '', '', '', '', 102,
Carsten  Rose's avatar
Carsten Rose committed
439
        '', 'no', '', '', '', '',
440
441
442
        '{{SELECT IF(f.bsInputColumns != '''', f.bsInputColumns, ''{{bsInputColumns:Y}}'') FROM Form AS f WHERE f.id = {{formId}} }}',
        'specialchar'),
       (2, 'bsNoteColumns', 'BS Note Columns', 'show', 'text', 'all', 'native', 430, 0, 0, '', '', '', '', '', 102, '',
Carsten  Rose's avatar
Carsten Rose committed
443
        'no', '', '', '', '',
444
445
446
        '{{SELECT IF(f.bsNoteColumns != '''', f.bsNoteColumns, ''{{bsNoteColumns:Y}}'') FROM Form AS f WHERE f.id = {{formId}} }}',
        'specialchar'),
       (2, 'rowLabelInputNote', 'Label / Input / Note', 'show', 'checkbox', 'alnumx', 'native', 440, 0, 10,
447
448
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-rowlabelinputnote">Info</a>', '', '', '',
        'minWidth=72', 102,
Carsten  Rose's avatar
Carsten Rose committed
449
        '', 'no', '', '', '', '', '', 'specialchar'),
450
       (2, 'maxLength', 'Maxlength', 'show', 'text', 'all', 'native', 450, 0, 0,
451
452
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-maxlength">Info</a>', '', '', '', '', 102, '', 'no',
        '', '',
Carsten  Rose's avatar
Carsten Rose committed
453
        '', '', '', 'specialchar'),
454
       (2, 'note', 'Note', 'show', 'text', 'all', 'native', 460, '80,1,350', 0,
455
456
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-note">Info</a>', '', '', '', '', 102, '', 'no', '',
        '', '',
457
        '', '', 'none'),
458
       (2, 'tooltip', 'Tooltip', 'show', 'text', 'all', 'native', 470, 0, 0,
459
460
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-tooltip">Info</a>', '', '', '', '', 102, '', 'no',
        '', '', '',
Carsten  Rose's avatar
Carsten Rose committed
461
        '', '', 'none'),
462
       (2, 'placeholder', 'Placeholder', 'show', 'text', 'all', 'native', 480, 0, 0,
463
464
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-placeholder">Info</a>', '', '', '', '', 102, '',
        'no', '', '',
Carsten  Rose's avatar
Carsten Rose committed
465
        '', '', '', 'none'),
466

467
       (2, 'value', 'value', 'show', 'text', 'all', 'native', 500, '80,1,350', 0,
468
469
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#field-value">Info</a>', '', '', '', '', 103, '', 'no', '',
        '', '',
Carsten  Rose's avatar
Carsten Rose committed
470
        '', '', 'none'),
471
       (2, 'sql1', 'sql1', 'show', 'text', 'all', 'native', 510, '80,1,350', 0,
472
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#sql1">Info</a><br><br>MariaDB: <a tabindex="-1" href="https://mariadb.com/kb/en/mariadb/select/">Select</a>, <a tabindex="-1" href="https://mariadb.com/kb/en/mariadb/functions-and-operators/">Functions</a>',
473
        '', '', '', '', 103, '', 'no', '', '', '', '', '', 'none'),
474
       (2, 'parameter', 'Parameter', 'show', 'text', 'all', 'native', 520, '80,1,350', 0,
475
476
        '<a tabindex="-1" href="{{documentation:Y}}/Form.html#fe-parameter-attributes">Info</a>', '', '', '', '', 103,
        '', 'no',
Carsten  Rose's avatar
Carsten Rose committed
477
        '', '', '', '', '', 'none');
478

479
480
481
482
483
484
485
INSERT INTO `FormElement` (`id`, `formId`, `feIdContainer`, `dynamicUpdate`, `enabled`, `name`, `label`, `mode`,
                           `modeSql`, `class`, `type`, `subrecordOption`, `encode`, `checkType`, `checkPattern`,
                           `onChange`, `ord`, `tabindex`, `size`, `maxLength`, `bsLabelColumns`, `bsInputColumns`,
                           `bsNoteColumns`, `rowLabelInputNote`, `note`, `adminNote`, `tooltip`, `placeholder`, `value`,
                           `sql1`, `parameter`, `parameterLanguageA`, `parameterLanguageB`, `parameterLanguageC`,
                           `parameterLanguageD`, `clientJs`, `feGroup`, `deleted`)

486
VALUES (NULL, '2', '0', 'no', 'yes', 'Check Name Conflict', '', 'show', '', 'action', 'beforeSave', '', 'specialchar',
Carsten  Rose's avatar
Carsten Rose committed
487
488
        'auto', '', '', '650', '0', '', '', '', '', '', 'row,label,/label,input,/input,note,/note,/row', '', '', '', '',
        '', '',
Carsten  Rose's avatar
Carsten Rose committed
489
        'sqlValidate={{!SELECT fe.id FROM FormElement AS fe WHERE "{{class:F:alnumx}}"=fe.class AND fe.formId={{formId:RF}} AND fe.name!="" AND fe.name="{{name:F:alnumx}}" AND fe.id!={{id:R0}} }}\r\n\r\nexpectRecords=0\r\n\r\nmessageFail=There is already another {{class:F:alnumx}} form element with name "{{name:F:alnumx}}".',
490
        '', '', '', '', '', '', 'no');
491

492
493
494
# ----------------------------------------
# MailLog

495
#DROP TABLE IF EXISTS `MailLog`;
Carsten  Rose's avatar
Carsten Rose committed
496
497
CREATE TABLE IF NOT EXISTS `MailLog`
(
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
    `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(255)  NOT NULL DEFAULT '',
    `attach`   VARCHAR(1024) 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`)
516
)
517
518
519
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;
520

Carsten  Rose's avatar
Carsten Rose committed
521
522
CREATE TABLE IF NOT EXISTS `FormSubmitLog`
(
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
    `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,
    `recordId`  INT(11)     NOT NULL,
    `pageId`    INT         NOT NULL,
    `sessionId` VARCHAR(32) NOT NULL,
    `created`   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX (`feUser`),
    INDEX (`formId`)
538
)
539
540
541
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;
542

543

Carsten  Rose's avatar
Carsten Rose committed
544
545
CREATE TABLE IF NOT EXISTS `Clipboard`
(
546
547
548
549
550
551
552
553
554
    `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`)
Carsten  Rose's avatar
Carsten Rose committed
555
)
556
557
558
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;
Carsten  Rose's avatar
Carsten Rose committed
559
560

# Form: CopyForm
561
INSERT INTO Form (id, name, title, tableName, showButton, forwardMode, forwardPage, parameter)
562
VALUES (3, 'copyForm', 'Copy a form', 'Clipboard', 'close,save', 'url-sip-skip-history',
Carsten  Rose's avatar
Carsten Rose committed
563
        '?id={{pageId:T}}&form=form&r={{formId:P0}}', 'submitButtonText = Copy Form');
Carsten  Rose's avatar
Carsten Rose committed
564
565
566

# FormElements: CopyForm
INSERT INTO FormElement (formId, name, label, type, class, ord, sql1, parameter)
567
568
569
570
VALUES (3, 'idSrc', 'Source Form', 'select', 'native', 10,
        '{{!SELECT f.id, CONCAT(f.name, " / ", f.title) FROM Form AS f ORDER BY f.name}}', ''),
       (3, 'myNewFormName', 'New Form Name', 'text', 'native', 20, '', ''),
       (3, 'clearClipboard', '', 'beforeSave', 'action', 100, '',
571
        'sqlValidate={{!SELECT f.id FROM Form AS f WHERE f.name LIKE "{{myNewFormName:FE:alnumx}}" LIMIT 1}}\nexpectRecords = 0\nmessageFail = There is already a form "{{myNewFormName:FE}}"\nsqlAfter={{DELETE FROM Clipboard WHERE cookie="{{cookieQfq:C0:alnumx}}" }}'),
572
573
574
575
576
577
578
       (3, 'updateClipboardRecord', '', 'afterSave', 'action', 110, '',
        'sqlAfter={{UPDATE Clipboard AS c, Form AS f SET c.cookie="{{cookieQfq:C0:alnumx}}", c.formIdPaste=f.id /* PasteForm */  WHERE c.id={{id:R}} AND f.name="{{form:SE}}" }}'),
       (3, 'formId', '', 'paste', 'action', 200, '{{!SELECT {{id:P}} AS id, "{{myNewFormName:FE:allbut}}" AS name}}',
        'recordDestinationTable=Form'),
       (3, 'formElementId', '', 'paste', 'action', 210,
        '{{!SELECT fe.id AS id, {{formId:P}} AS formId FROM FormElement AS fe WHERE fe.formId={{id:P}} ORDER BY fe.ord}}',
        'recordDestinationTable=FormElement\ntranslateIdColumn=feIdContainer');
Carsten  Rose's avatar
Carsten Rose committed
579

580
# AutoCRON
Carsten  Rose's avatar
Carsten Rose committed
581
582
CREATE TABLE IF NOT EXISTS `Cron`
(
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
    `id`            INT(11)                      NOT NULL AUTO_INCREMENT,
    `grId`          INT(11)                      NOT NULL,
    `xId`           INT(11)                      NOT NULL,
    `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`)
604
)
605
606
607
    ENGINE = InnoDB
    AUTO_INCREMENT = 0
    DEFAULT CHARSET = utf8;
608

609
610
# Form: AutoCron
INSERT INTO Form (id, name, title, tableName, parameter, dirtyMode)
Carsten  Rose's avatar
Carsten Rose committed
611
VALUES (4, 'cron', 'autoCron', 'Cron', 'dbIndex={{indexQfq:Y}}', 'none');
612

613
# FormElements: AutoCron
Carsten  Rose's avatar
Carsten Rose committed
614
615
INSERT INTO FormElement (formId, name, label, mode, modeSql, type, encode, checkType, ord, parameter, size, note,
                         dynamicUpdate, bsLabelColumns, bsInputColumns, bsNoteColumns)
616
617
VALUES (4, 'status', 'Enabled', 'show', '', 'checkbox', 'specialchar', 'alnumx', 10, '', '', '', 'no', '', '', ''),
       (4, 'type', 'Type', 'show', '', 'radio', 'specialchar', 'alnumx', 20, 'buttonClass=btn-default', '', '', 'yes',
Carsten  Rose's avatar
Carsten Rose committed
618
        '', '', ''),
619
620
621
622
623
624
625
626
627
628
       (4, 'nextRun', 'Next run', 'show', '', 'text', 'specialchar', 'alnumx', 30,
        'extraButtonInfo = Cronjob will be started if specified timestamp is over. If timestamp=0: Job will never be started<br>Every time the jobs runs, this timestamp will be increased automatically by "frequency".',
        '', '', 'no', '', '', ''),
       (4, 'frequency', 'Frequency', 'show', '', 'text', 'specialchar', 'alnumx', 40,
        'extraButtonInfo = Repeat AutoCron-job with the specified interval. If empty: no repeating.<br>E.g.: "1 DAY", "15 MINUTE'', "6 MONTH" - used directly in SQL-Function "DATE_ADD(&lt;nextrun&gt;, INTERVAL &lt;frequency&gt;)"',
        '', '', 'no', '', '', ''),
       (4, 'comment', 'Comment', 'show', '', 'text', 'specialchar', 'allbut', 50, '', '', '', 'no', '', '', ''),
       (4, 'sql1', 'Mail', 'show', '{{SELECT IF("{{type:FR:alnumx}}"="mail","show","hidden") }}', 'text', 'none', 'all',
        60,
        'extraButtonInfo = Query: &#123;&#123;!SELECT ... as sendMailTo...&#125;&#125;<br><b>sendMailTo / sendMailCc / sendMailBcc</b>: Separate multiple by comma.<br><b>sendMailFrom</b><br><b>sendMailSubject</b><br><b>sendMailReplyTo</b>: Optional<br><b>sendMailFlagAutoSubmit</b>: Optional. on|off. Default on - if "on", suppresses OoO answers from receivers.<br><b>sendMailGrId</b>: Optional<br><b>sendMailXId</b>: Optional',
629
        '80,1,350', '', 'yes', '', '', ''),
630
       (4, 'content', '{{SELECT IF("{{type:FR:alnumx}}"="mail","Mail body","URL") }}', 'show', '', 'text', 'none',
Carsten  Rose's avatar
Carsten Rose committed
631
        'all', 70, '', '80,1,350',
632
633
634
635
        'Website: URL absolute like "http://..." or relative like "?id=pagealias..."<br>Mail: Static Body or &#123;{SELECT ...&#125;}',
        'yes', '', '', ''),

       (4, 'outputFile', 'Log output to file', 'show', '{{SELECT IF("{{type:FR:alnumx}}"="mail","hidden","show") }}',
Carsten  Rose's avatar
Carsten Rose committed
636
        'text', 'none', 'all', 80, '', '', 'CWD: Site installation directory', 'yes', '', '', ''),
637
638
639
640
       (4, 'outputMode', 'Mode output', 'show', '{{SELECT IF("{{type:FR:alnumx}}"="mail","hidden","show") }}', 'radio',
        'specialchar', 'alnumx', 90, 'buttonClass=btn-default', '', '', 'yes', '', '', ''),
       (4, 'outputPattern', 'Pattern to look for on output', 'show',
        '{{SELECT IF("{{type:FR:alnumx}}"="mail","hidden","show") }}', 'text', 'none', 'all', 100, '', '',
Carsten  Rose's avatar
Carsten Rose committed
641
        'If pattern isn\'t found, return an error.<br>Check <a tabindex="-1" href="https://secure.php.net/manual/en/pcre.pattern.php">pcre</a> / <a tabindex="-1" href="https://regex101.com/">Regex101</a> ',
642
643
644
        'yes', '', '', ''),

       (4, 'lastRun', 'Last run', 'readonly', '', 'text', 'specialchar', 'alnumx', 120, '', '', '', 'no', '', '', ''),
645
       (4, 'lastStatus', 'Laststatus', 'readonly', '', 'text', 'specialchar', 'alnumx', 130, '', '80,1,350', '', 'no',
Carsten  Rose's avatar
Carsten Rose committed
646
        '3', '9', '0'),
647
648
649
       (4, 'inProgress', 'In progress since', 'show', '', 'text', 'specialchar', 'alnumx', 140,
        'extraButtonInfo = Start time of a running job. When job is finished, this will be set back to 0. A new job will only be started, if this is 0. A progress duration >10mins will be treated as an error.',
        '', '', 'no', '', '', '');
Carsten  Rose's avatar
Carsten Rose committed
650
651
652

CREATE TABLE IF NOT EXISTS `Split`
(
653
654
655
656
657
658
659
660
    `id`           INT(11)      NOT NULL AUTO_INCREMENT,
    `tableName`    VARCHAR(255) NOT NULL,
    `xId`          INT(11)      NOT NULL,
    `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`)
661
)
662
663
664
    ENGINE = InnoDB
    AUTO_INCREMENT = 0
    DEFAULT CHARSET = utf8;
665

666
667
CREATE TABLE IF NOT EXISTS `Setting`
(
668
669
670
671
672
673
674
675
676
677
678
679
680
    `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,
    `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
681
) ENGINE = InnoDB
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE `Uniq`
(
    `id`       int(11)  NOT NULL AUTO_INCREMENT,
    `random`   char(32) NOT NULL,
    `expire`   datetime NOT NULL,
    `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `random` (`random`) USING BTREE,
    KEY `expire` (`id`)

) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;