formEditor.sql 16.4 KB
Newer Older
1
2
DROP TABLE IF EXISTS `Form`;
CREATE TABLE IF NOT EXISTS `Form` (
3
4
5
6
7
  `id`                       INT(11)                                                   NOT NULL  AUTO_INCREMENT,
  `name`                     VARCHAR(255)                                              NOT NULL  DEFAULT '',
  `title`                    VARCHAR(255)                                              NOT NULL  DEFAULT '',
  `noteInternal`             TEXT                                                      NOT NULL,
  `tableName`                VARCHAR(255)                                              NOT NULL  DEFAULT '',
8

9

10
11
  `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',
12
  `render`                   ENUM('plain', 'table', 'bootstrap')                       NOT NULL  DEFAULT 'plain',
13
  `showButton` SET('new', 'delete') NOT NULL  DEFAULT 'new,delete',
14
15
16
17
  `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 '',
18

19
  `forwardMode`              ENUM('client', 'no', 'page')                              NOT NULL  DEFAULT 'client',
20
  `forwardPage`              VARCHAR(255)                                              NOT NULL  DEFAULT '',
21

22
23
24
  `bsLabelColumns`           VARCHAR(255)                                              NOT NULL  DEFAULT '',
  `bsInputColumns`           VARCHAR(255)                                              NOT NULL  DEFAULT '',
  `bsNoteColumns`            VARCHAR(255)                                              NOT NULL  DEFAULT '',
25

26
  `parameter`                TEXT                                                      NOT NULL,
27

28
29
  `deleted`                  ENUM('yes', 'no')                                         NOT NULL  DEFAULT 'no',
  `modified`                 TIMESTAMP                                                 NOT NULL  DEFAULT CURRENT_TIMESTAMP,
30
  `created`                  DATETIME                                                  NOT NULL  DEFAULT '0000-00-00 00:00:00',
31

32
33
34
35
36
37
38
39
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `name_deleted` (`name`, `deleted`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  AUTO_INCREMENT = 0;

40
41
42
43
44
45
46
47
48
49
#--
#-- Triggers `Form`
#--
#DROP TRIGGER IF EXISTS `on_Form_update_modified`;
#DELIMITER //
#CREATE TRIGGER `on_Form_update_modified` BEFORE UPDATE ON `Form`
#FOR EACH ROW SET NEW.modified =
#current_timestamp()
#//
#DELIMITER ;
50
51
52
53
54
55

# ----------------------------------------
# FormElement

DROP TABLE IF EXISTS `FormElement`;
CREATE TABLE IF NOT EXISTS `FormElement` (
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
  `id`              INT(11)                                                  NOT NULL  AUTO_INCREMENT,
  `formId`          INT(11)                                                  NOT NULL,
  `feIdContainer`   INT(11)                                                  NOT NULL  DEFAULT '0',

  `enabled`         ENUM('yes', 'no')                                        NOT NULL  DEFAULT 'yes',

  `name`            VARCHAR(255)                                             NOT NULL  DEFAULT '',
  `label`           VARCHAR(255)                                             NOT NULL  DEFAULT '',

  `mode`            ENUM('show', 'readonly', 'required', 'lock', 'disabled') NOT NULL  DEFAULT 'show',
  `class`           ENUM('native', 'action', 'container')                    NOT NULL  DEFAULT 'native',
  `type`            ENUM('checkbox', 'dateJQW', 'datetimeJQW', 'gridJQW', 'hidden', 'text',
                         'note', 'password', 'radio', 'select', 'subrecord', 'upload', 'fieldset', 'pill',
                         'before_load', 'before_save', 'before_insert', 'before_update', 'before_delete', 'after_load',
                         'after_save', 'after_insert', 'after_update', 'after_delete', 'feGroup',
                         'sendmail')                                         NOT NULL  DEFAULT 'text',
  `subrecordOption` SET('edit', 'delete', 'new')                             NOT NULL  DEFAULT '',
  `checkType`       ENUM('', 'min|max', 'pattern', 'number', 'email')        NOT NULL  DEFAULT '',
  `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 '',
  `note`            TEXT                                                     NOT NULL,
  `tooltip`         VARCHAR(255)                                             NOT NULL  DEFAULT '',
  `placeholder`     VARCHAR(255)                                             NOT NULL  DEFAULT '',

  `value`           TEXT                                                     NOT NULL,
  `sql1`            TEXT                                                     NOT NULL,
  `sql2`            TEXT                                                     NOT NULL,
  `parameter`       TEXT                                                     NOT NULL,
  `clientJs`        TEXT                                                     NOT NULL,

  `feGroup`         VARCHAR(255)                                             NOT NULL  DEFAULT '',
  `deleted`         ENUM('yes', 'no')                                        NOT NULL  DEFAULT 'no',
  `modified`        TIMESTAMP                                                NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  `created`         DATETIME                                                 NOT NULL  DEFAULT '0000-00-00 00:00:00',
97
98
99
100
101
102
103
104
105

  PRIMARY KEY (`id`),
  KEY `formId` (`formId`),
  KEY `formId_class_enabled_deleted` (`formId`, `class`, `enabled`, `deleted`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  AUTO_INCREMENT = 0;

106
107
108
109
110
111
112
113
114
115
#--
#-- Triggers `FormElement`
#--
#DROP TRIGGER IF EXISTS `on_FormEelement_update_modified`;
#DELIMITER //
#CREATE TRIGGER `on_FormEelement_update_modified` BEFORE UPDATE ON `FormElement`
#FOR EACH ROW SET NEW.modified =
#current_timestamp()
#//
#DELIMITER ;
116

117
118

#
119
# FormEditor: Form
120
INSERT INTO Form (name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter) VALUES
121
  ('form', 'Form Editor: {{SELECT id, " / ", title FROM Form WHERE id = {{recordId:S0}}}}', 'Please secure the form',
122
   'Form', 'always', 'always', 'bootstrap', '', 'maxVisiblePill=3\nclass=container-fluid');
123

124
# FormEditor: FormElements
125
INSERT INTO FormElement (formId, name, label, mode, type, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption)
126
VALUES
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
  (1, 'basic', 'Basic', 'show', 'pill', 'container', 10, 0, 0, '', '', '', '', '', '', 0, ''),
  (1, 'permission', 'Permission', 'show', 'pill', 'container', 20, 0, 0, '', '', '', '', '', '', 0, ''),
  (1, 'various', 'Various', 'show', 'pill', 'container', 30, 0, 0, '', '', '', '', '', '', 0, ''),
  (1, 'multi', 'Multi', 'show', 'pill', 'container', 40, 0, 0, '', '', '', '', '', '', 0, ''),
  (1, 'formelement', 'Formelement', 'show', 'pill', 'container', 50, 0, 0, '', '', '', '', '', '', 0, ''),

  (1, 'id', 'id', 'readonly', 'text', 'native', 100, 10, 11, '', '', '', '', '', '', 1, ''),
  (1, 'name', 'Name', 'show', 'text', 'native', 120, 40, 255, '', '', '', '', '', 'autofocus', 1, ''),
  (1, 'title', 'Title', 'show', 'text', 'native', 130, 40, 255, '', '', '', '', '', '', 1, ''),
  (1, 'noteInternal', 'Note', 'show', 'text', 'native', 140, '40,3', 0, '', '', '', '', '', '', 1, ''),
  (1, 'tableName', 'Table', 'required', 'select', 'native', 150, 0, 0, '', '', '', '{{!SHOW tables}}', '', 'emptyItemAtStart', 1, ''),

  (1, 'permitNew', 'Permit New', 'show', 'radio', 'native', 160, 0, 0, '', '', '', '', '', '', 2, ''),
  (1, 'permitEdit', 'Permit Edit', 'show', 'radio', 'native', 170, 0, 0, '', '', '', '', '', '', 2, ''),
  (1, 'render', 'Render', 'show', 'radio', 'native', 190, 0, 0, '', '', '', '', '', '', 2, ''),
  (1, 'showButton', 'Show button', 'show', 'checkbox', 'native', 200, 0, 0, '', '', '', '', '', 'checkBoxMode = multi\norientation=vertical', 2, ''),

  (1, 'forwardMode', 'Forward', 'show', 'radio', 'native', 260, 0, 0, '', '', '', '', '', '', 3, ''),
  (1, 'forwardPage', 'Forward Page', 'show', 'text', 'native', 270, 40, 255, '', '', '', '', '', '', 3, ''),
  (1, 'parameter', 'Parameter', 'show', 'text', 'native', 275, '40,3', 0, '', '', '', '', '', '', 3, ''),

  (1, 'bsLabelColumns', 'BS Label Columns', 'show', 'text', 'native', 280, 40, 250, '', '', '', '', '', '', 3, ''),
  (1, 'bsInputColumns', 'BS Input Columns', 'show', 'text', 'native', 290, 40, 250, '', '', '', '', '', '', 3, ''),
  (1, 'bsNoteColumns', 'BS Note Columns', 'show', 'text', 'native', 300, 40, 250, '', '', '', '', '', '', 3, ''),

  (1, 'deleted', 'Deleted', 'show', 'checkbox', 'native', 405, 0, 0, '', '', '', '', '', '', 3, ''),
  (1, 'modified', 'Modified', 'readonly', 'text', 'native', 410, 40, 20, '', '', '', '', '', '', 3, ''),
  (1, 'created', 'Created', 'readonly', 'text', 'native', 420, 40, 20, '', '', '', '', '', '', 3, ''),

  (1, 'multi', 'Multi', 'show', 'fieldset', 'native', 210, 0, 0, '', '', '', '', '', '', 4, ''),
  (1, 'multiMode', 'Multi Mode', 'show', 'radio', 'native', 220, 0, 0, '', '', '', '', '', '', 4, ''),
  (1, 'multiSql', 'Multi SQL', 'show', 'text', 'native', 230, '40,3', 0, '', '', '', '', '', '', 4, ''),
  (1, 'multiDetailForm', 'Multi Detail Form', 'show', 'text', 'native', 240, 40, 255, '', '', '', '', '', '', 4, ''),
160
  (1, 'multiDetailFormParameter', 'Multi Detail Form Parameter', 'show', 'text', 'native', 250, 40, 255, '', '', '', '',
161
   '', '', 4, ''),
162

163
  (1, '', 'FormElements', 'show', 'subrecord', 'native', 500, 0, 0, '', '', '',
164
   '{{!SELECT * FROM FormElement WHERE formId={{id:R0}}}}',
165
   '', 'form=formElement\ndetail=id:formId', 5, 'new,edit,delete');
166
167
168
169

#
# FormEditor: FormElement
INSERT INTO Form (name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter) VALUES
170
  ('formElement', 'Form Element Editor : {{SELECT id, " / ", title FROM Form WHERE id = {{recordId:S0}}}}',
171
   'Please secure the form',
172
   'FormElement', 'always', 'always', 'bootstrap', '', 'maxVisiblePill=5');
173
174

# FormEditor: FormElements
175
INSERT INTO FormElement (id, formId, name, label, mode, type, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption)
176
177
VALUES

178
179
180
181
182
183
184
185
  (100, 2, 'basic', 'Basic', 'show', 'pill', 'container', 10, 0, 0, '', '', '', '', '', '', 0, ''),
  (101, 2, 'check_order', 'Check & Order', 'show', 'pill', 'container', 20, 0, 0, '', '', '', '', '', '', 0, ''),
  (102, 2, 'layout', 'Layout', 'show', 'pill', 'container', 20, 0, 0, '', '', '', '', '', '', 0, ''),
  (103, 2, 'value', 'Value', 'show', 'pill', 'container', 20, 0, 0, '', '', '', '', '', '', 0, ''),
  (104, 2, 'info', 'Info', 'show', 'pill', 'container', 20, 0, 0, '', '', '', '', '', '', 0, '');

INSERT INTO FormElement (formId, name, label, mode, type, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption)
VALUES
186

187
188
189
  (2, 'id', 'id', 'readonly', 'text', 'native', 100, 10, 11, '', '', '', '', '', '', 100, ''),
  (2, 'formId', 'formId', 'readonly', 'text', 'native', 110, 40, 255, '', '', '', '', '', '', 100, ''),
  (2, 'feIdContainer', 'Container', 'show', 'select', 'native', 120, 0, 0, '', '', '',
190
   '{{!SELECT fe.id, CONCAT(fe.class, " / ", fe.label) FROM FormElement As fe WHERE fe.formId={{formId}} AND fe.class="container" ORDER BY fe.ord }}',
191
192
193
194
195
196
197
   '', 'emptyItemAtStart', 100, ''),
  (2, 'enabled', 'Enabled', 'show', 'checkbox', 'native', 130, 0, 0, '', '', '', '', '', '', 100, ''),
  (2, 'name', 'Name', 'show', 'text', 'native', 140, 40, 255, '', '', '', '', '', '', 100, ''),
  (2, 'label', 'Label', 'show', 'text', 'native', 150, 40, 255, '', '', '', '', '', '', 100, ''),
  (2, 'mode', 'Mode', 'show', 'select', 'native', 160, 0, 255, '', '', '', '', '', '', 100, ''),
  (2, 'class', 'Class', 'show', 'select', 'native', 170, 0, 255, '', '', '', '', '', '', 100, ''),
  (2, 'type', 'Type', 'show', 'select', 'native', 180, 0, 255, '', '', '', '', '', '', 100, ''),
198
  (2, 'subrecordOption', 'Subrecord Option', 'show', 'checkbox', 'native', 190, 0, 0, '', '', '', '', '', '', 100, ''),
199
200
201
202
203
204
  (2, 'checkType', 'Check Type', 'show', 'select', 'native', 200, 0, 255, '', '', '', '', '', '', 101, ''),
  (2, 'checkPattern', 'Check Pattern', 'show', 'text', 'native', 210, 40, 255, '', '', '', '', '', '', 101, ''),
  (2, 'onChange', 'JS onChange', 'show', 'text', 'native', 220, 40, 255, '', '', '', '', '', '', 101, ''),
  (2, 'ord', 'Order', 'show', 'text', 'native', 230, 40, 255, '', '', '', '', '', '', 101, ''),
  (2, 'tabindex', 'tabindex', 'show', 'text', 'native', 240, 40, 255, '', '', '', '', '', '', 101, ''),
  (2, 'size', 'Size', 'show', 'text', 'native', 250, 40, 255, '', '', '', '', '', '', 102, ''),
205
  (2, 'maxLength', 'Maxlength', 'show', 'text', 'native', 260, 40, 255, '', '', '', '', '', '', 102, ''),
206
207
208
209
210
211
212
213
214
215
216
  (2, 'note', 'note', 'show', 'text', 'native', 270, 40, 255, '', '', '', '', '', '', 102, ''),
  (2, 'tooltip', 'Tooltip', 'show', 'text', 'native', 280, 40, 255, '', '', '', '', '', '', 102, ''),
  (2, 'placeholder', 'Placeholder', 'show', 'text', 'native', 290, 40, 255, '', '', '', '', '', '', 102, ''),
  (2, 'value', 'value', 'show', 'text', 'native', 300, 40, 255, '', '', '', '', '', '', 102, ''),
  (2, 'sql1', 'sql1', 'show', 'text', 'native', 310, '40,4', 255, '', '', '', '', '', '', 103, ''),
  (2, 'parameter', 'Parameter', 'show', 'text', 'native', 320, '40,4', 255, '', '', '', '', '', '', 103, ''),
  (2, 'clientJs', 'ClientJS', 'show', 'text', 'native', 330, 40, 255, '', '', '', '', '', '', 103, ''),
  (2, 'feGroup', 'feGroup', 'show', 'text', 'native', 340, 40, 255, '', '', '', '', '', '', 104, ''),
  (2, 'deleted', 'Deleted', 'show', 'checkbox', 'native', 350, 0, 0, '', '', '', '', '', '', 104, ''),
  (2, 'modified', 'Modified', 'readonly', 'text', 'native', 360, 40, 20, '', '', '', '', '', '', 104, ''),
  (2, 'created', 'Created', 'readonly', 'text', 'native', 370, 40, 20, '', '', '', '', '', '', 104, '');
217
218
219
220
221
222

# FormEditor: Small
INSERT INTO Form (name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter) VALUES
  ('person', 'Person {{SELECT ": ", firstName, " ", name, " (", id, ")" FROM Person WHERE id = {{recordId:S0}}}}',
   'Please secure the form',
   'Person', 'always', 'always', 'bootstrap', '', '');
223

224
# FormEditor: FormElements
225
INSERT INTO FormElement (id, formId, name, label, mode, type, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption)
226
VALUES
227

228
229
  (200, 3, 'name', 'Name', 'show', 'text', 'native', 10, 50, 255, '', '', '', '', '', '', 0, ''),
  (201, 3, 'firstname', 'Firstname', 'show', 'text', 'native', 10, 50, 255, '', '', '', '', '', '', 0, '');
230
231
232
233
234
235
236
237
238

# ----------------------------------------------------------------------
#

# Form: plain
INSERT INTO Form (name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter) VALUES
  ('formplain', 'Form: Plain', '', 'Form', 'always', 'always', 'plain', '', '');

# FormEditor: FormElements
239
INSERT INTO FormElement (id, formId, name, label, mode, type, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption)
240
VALUES
241
242
  (300, 4, 'id', 'id', 'readonly', 'text', 'native', 100, 10, 11, '', '', '', '', '', '', 0, ''),
  (310, 4, 'name', 'Name', 'show', 'text', 'native', 120, 40, 255, '', '', '', '', '', '', 0, '');
243
244
245
246
247
248

# Form: table
INSERT INTO Form (name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter) VALUES
  ('formtable', 'Form: Table', '', 'Form', 'always', 'always', 'table', '', '');

# FormEditor: FormElements
249
INSERT INTO FormElement (id, formId, name, label, mode, type, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption)
250
VALUES
251
252
  (400, 5, 'id', 'id', 'readonly', 'text', 'native', 100, 10, 11, '', '', '', '', '', '', 0, ''),
  (410, 5, 'name', 'Name', 'show', 'text', 'native', 120, 40, 255, '', '', '', '', '', '', 0, '');