testtables.sql 7 KB
Newer Older
1
DROP TABLE IF EXISTS Person;
2
CREATE TABLE Person (
3
4
5
6
7
8
9
10
11
12
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(128)               NOT NULL DEFAULT '',
  firstname    VARCHAR(128)               NOT NULL                                                                                                                 DEFAULT '',
  pathFileName VARCHAR(128)               NOT NULL                                                                                                                 DEFAULT '',
  gender       ENUM('', 'male', 'female') NOT NULL DEFAULT 'male',
  groups       SET('', 'a', 'b', 'c')     NOT NULL DEFAULT '',
  birthday     DATE                       NOT NULL DEFAULT '0000-00-00',
  noteId       BIGINT                     NOT NULL DEFAULT 0,
  modified     TIMESTAMP                  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created      DATETIME                   NOT NULL DEFAULT '0000-00-00 00:00:00'
13
14
);

15
#
16
# Create Demo
17
INSERT INTO Person (id, name, firstname, gender, groups) VALUES
18
19
  (NULL, 'Doe', 'John', 'male', 'c'),
  (NULL, 'Smith', 'Jane', 'female', 'a,c');
20
21


22
23
DROP TABLE IF EXISTS PersFunction;
CREATE TABLE PersFunction (
24
  id       BIGINT AUTO_INCREMENT PRIMARY KEY,
25
26
  personId BIGINT,
  type     ENUM('Student', 'Assistant', 'Professor', 'Administration'),
27
28
29
30
31
  start    DATE      NOT NULL DEFAULT '0000-00-00',
  end      DATE      NOT NULL                                                                                                                    DEFAULT '0000-00-00',
  note     VARCHAR(255),
  modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created  DATETIME  NOT NULL DEFAULT '0000-00-00 00:00:00'
32
33
34
35
36
37
38
39
);

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

# FormEditor: FormElements
40
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
41
VALUES
42
43
  (300, 3, 'id', 'id', 'readonly', 'text', 'all', 'native', 100, 0, 11, '', '', '', '', '', '', 0, '', ''),
  (310, 3, 'name', 'Name', 'show', 'text', 'all', 'native', 120, 0, 255, '', '', '', '', '', '', 0, '', '');
44
45
46
47
48
49

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

# FormEditor: FormElements
50
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
51
VALUES
52
53
  (400, 4, 'id', 'id', 'readonly', 'text', 'all', 'native', 100, 0, 11, '', '', '', '', '', '', 0, '', ''),
  (410, 4, 'name', 'Name', 'show', 'text', 'all', 'native', 120, 0, 255, '', '', '', '', '', '', 0, '', '');
54
55
56
57
58
59
60
61


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

62
# FormEditor: FormElements person
63
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
64
65
VALUES

66
67
68
  (500, 5, 'name', 'Name', 'show', 'text', 'all', 'native', 10, 0, 255, '', '', '', '', '', '', 0, '', ''),
  (501, 5, 'firstname', 'Firstname', 'show', 'text', 'all', 'native', 20, 0, 255, '', '', '', '', '', '', 0, '', ''),
  (502, 5, 'birthday', 'Birthday', 'show', 'date', 'all', 'native', 30, 0, 255, '', '', '', '', '', '', 0, '', ''),
69
  (506, 5, 'gender', 'Sex', 'show', 'radio', 'alnumx', 'native', 40, 0, 0, '', '', '', '', '', 'itemList=female,male',
70
   0, '', ''),
71
  (503, 5, 'datumZeit', 'Datum & Zeit', 'show', 'datetime', 'alnumx', 'native', 50, 0, 0, '', '', '', '', '', '', 0,
72
73
   '', ''),
  (504, 5, 'zeit', 'Zeit', 'show', 'time', 'alnumx', 'native', 60, 0, 0, '', '', '', '', '', '', 0, '', ''),
74
75
  (505, 5, 'picture', 'Picture', 'show', 'upload', 'allbut', 'native', 70, 0, 0, '', '', '', '', '',
   'pathFileName={{SELECT ''fileadmin/user/pictures/'', p.name, ''-{{_filename}}'' FROM Person AS p WHERE p.id={{r}} }}',
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
   0, '', ''),

  (506, 5, '', 'Address', 'show', 'subrecord', 'all', 'native', 100, 0, 0, '', '', '',
   '{{!SELECT a.id, a.street, a.city, a.country FROM Address AS a WHERE a.personId={{r:S0}} }}',
   '', 'form=address\ndetail=id:personId', 0, 'new,edit,delete', '');


# Form: Address
REPLACE INTO Form (id, name, title, noteInternal, tableName, permitNew, permitEdit, render, multiSql, parameter) VALUES
  (6, 'address',
   'Person {{SELECT ": ", p.firstName, " ", p.name, " (", id, ")" FROM Person AS p WHERE p.id = {{personId:S0}}}}',
   '',
   'Address', 'always', 'always', 'bootstrap', '', '');

# FormEditor: FormElements address
REPLACE INTO FormElement (id, formId, name, label, mode, type, checkType, class, ord, size, maxLength, note, clientJs, value, sql1, sql2, parameter, feIdContainer, subrecordOption, modeSql)
VALUES

  (600, 6, 'street', 'Street', 'show', 'text', 'all', 'native', 10, 0, 255, '', '', '', '', '', '', 0, '', ''),
  (601, 6, 'city', 'City', 'show', 'text', 'all', 'native', 20, 0, 255, '', '', '', '', '', '', 0, '', ''),
  (602, 6, 'country', 'Country', 'show', 'select', 'all', 'native', 30, 0, 255, '', '', '', '', '', '', 0, '', '');

98
99
100

# ----------------------------------------------------------------------
#
101
102
DROP TABLE IF EXISTS Address;
CREATE TABLE Address (
103
104
105
106
107
108
109
110
  id       BIGINT AUTO_INCREMENT PRIMARY KEY,
  personId BIGINT                                              NOT NULL DEFAULT 0,
  street   VARCHAR(128)                                        NOT NULL DEFAULT '',
  city     VARCHAR(128)                                        NOT NULL DEFAULT '',
  country  ENUM('Switzerland', 'Austria', 'France', 'Germany') NOT NULL,
  grIdTyp  BIGINT                                              NOT NULL DEFAULT 0,
  modified TIMESTAMP                                           NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created  DATETIME                                            NOT NULL DEFAULT '0000-00-00 00:00:00'
111
112
);

113
114
115
116
117
INSERT INTO Address (personId, street, city, country) VALUES
  (1, 'Side Street', 'Zurich', 'Switzerland'),
  (1, 'Park Street', 'Wien', 'Austria'),
  (1, 'Winter Street', 'Paris', 'France'),
  (2, 'Summer Street', 'Berlin', 'Germany');
118

119
120
121
122
123
124
125
126
127
128
129
130
131
132
#------------------------------------------------------------------------
#
DROP TABLE IF EXISTS Note;
CREATE TABLE Note (
  id       BIGINT                AUTO_INCREMENT PRIMARY KEY,
  note     VARCHAR(128) NOT NULL DEFAULT '',
  xId      BIGINT       NOT NULL DEFAULT 0,
  grIdTyp  BIGINT       NOT NULL DEFAULT 0,
  modified TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created  DATETIME     NOT NULL DEFAULT '0000-00-00 00:00:00'
);



133