|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
use yii\db\Migration; |
|
4
|
|
|
|
|
5
|
|
|
class m160902_204642_dds_init extends Migration |
|
6
|
|
|
{ |
|
7
|
|
|
public function up() |
|
8
|
|
|
{ |
|
9
|
|
|
$tableOptions = null; |
|
|
|
|
|
|
10
|
|
|
if ($this->db->driverName === 'mysql') { |
|
11
|
|
|
// http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci |
|
12
|
|
|
$tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB'; |
|
13
|
|
|
} |
|
14
|
|
|
$connection = \Yii::$app->getDb(); |
|
15
|
|
|
|
|
16
|
|
|
// create the various tables for dds |
|
17
|
|
|
$sql=<<<EOQ |
|
18
|
|
|
-- -------------------------------------------------------- |
|
19
|
|
|
|
|
20
|
|
|
-- |
|
21
|
|
|
-- Table structure for table `dds_storage` |
|
22
|
|
|
-- |
|
23
|
|
|
|
|
24
|
|
|
DROP TABLE IF EXISTS `dds_storage`; |
|
25
|
|
|
CREATE TABLE `dds_storage` ( |
|
26
|
|
|
`storage_ref` char(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'unique reference to the storage type within the code', |
|
27
|
|
|
`label` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'User facing label for the storage type', |
|
28
|
|
|
`type` enum('INTEGER_TINY','INTEGER_SHORT','INTEGER','INTEGER_LONG','FLOAT','DOUBLE','DATE','DATETIME','TEXT_SHORT','TEXT','TEXT_LONG','BINARY_SHORT','BINARY','BINARY_LONG','TIME','CHAR','UUID') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'the storage type', |
|
29
|
|
|
`description` varchar(8000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'User facing description of what the type will take', |
|
30
|
|
|
PRIMARY KEY (`storage_ref`) |
|
31
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Defines the storage mechanisms for a particular data type'; |
|
32
|
|
|
|
|
33
|
|
|
-- -------------------------------------------------------- |
|
34
|
|
|
|
|
35
|
|
|
-- |
|
36
|
|
|
-- Table structure for table `dds_class` |
|
37
|
|
|
-- |
|
38
|
|
|
|
|
39
|
|
|
DROP TABLE IF EXISTS `dds_class`; |
|
40
|
|
|
CREATE TABLE `dds_class` ( |
|
41
|
|
|
`class_type` char(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of data class this is e.g. ''Blog''', |
|
42
|
|
|
`label` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The user facing label for the class', |
|
43
|
|
|
`description` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'A description of the class for the end user', |
|
44
|
|
|
`module` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The neon module (e.g. cms) that the data belongs with if required', |
|
45
|
|
|
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not the class has been soft deleted', |
|
46
|
|
|
`change_log` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not a change log is maintained for this class', |
|
47
|
|
|
`count_total` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'A count of the number of objects of this type', |
|
48
|
|
|
`count_deleted` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'A count of the number of deleted objects of this type', |
|
49
|
|
|
`count_deleted` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'A count of the number of deleted objects of this type', |
|
50
|
|
|
`definition` text COLLATE utf8mb4_unicode_ci COMMENT 'A definition for use by other modules, but not understood by DDS', |
|
51
|
|
|
UNIQUE KEY `classRef` (`class_type`) |
|
52
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Holds the basic information about the dynamic data class'; |
|
53
|
|
|
|
|
54
|
|
|
-- -------------------------------------------------------- |
|
55
|
|
|
|
|
56
|
|
|
-- |
|
57
|
|
|
-- Table structure for table `dds_data_type` |
|
58
|
|
|
-- |
|
59
|
|
|
|
|
60
|
|
|
DROP TABLE IF EXISTS `dds_data_type`; |
|
61
|
|
|
CREATE TABLE `dds_data_type` ( |
|
62
|
|
|
`data_type_ref` char(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'a reference for the data type used in code', |
|
63
|
|
|
`label` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'User facing label for the data type (e.g. fixed choice)', |
|
64
|
|
|
`description` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'A description of the data type', |
|
65
|
|
|
`definition` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Any definitions inc constraints that the UI should know about as JSON', |
|
66
|
|
|
`storage_ref` char(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'the type of database storage type used to define this type', |
|
67
|
|
|
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not this data type is deleted', |
|
68
|
|
|
UNIQUE KEY `data_type_ref` (`data_type_ref`), |
|
69
|
|
|
KEY `storage_type` (`storage_ref`) USING BTREE, |
|
70
|
|
|
CONSTRAINT `dds_data_type_storage_ref` FOREIGN KEY (`storage_ref`) REFERENCES `dds_storage` (`storage_ref`) ON UPDATE CASCADE |
|
71
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Definition of the type of user facing object the class member can be (e.g. a choice)'; |
|
72
|
|
|
|
|
73
|
|
|
-- -------------------------------------------------------- |
|
74
|
|
|
|
|
75
|
|
|
-- |
|
76
|
|
|
-- Table structure for table `dds_link` |
|
77
|
|
|
-- |
|
78
|
|
|
|
|
79
|
|
|
DROP TABLE IF EXISTS `dds_link`; |
|
80
|
|
|
CREATE TABLE `dds_link` ( |
|
81
|
|
|
`from_id` char(22) COLLATE latin1_general_cs NOT NULL COMMENT 'The UUID64 of the from object', |
|
82
|
|
|
`from_member` char(50) COLLATE latin1_general_cs NOT NULL COMMENT 'The member ref within from object', |
|
83
|
|
|
`to_id` char(22) COLLATE latin1_general_cs NOT NULL COMMENT 'The UUID64 of the to object', |
|
84
|
|
|
UNIQUE KEY `linky` (`from_id`,`from_member`,`to_id`), |
|
85
|
|
|
KEY `from_id` (`from_id`), |
|
86
|
|
|
KEY `to_id` (`to_id`) |
|
87
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs; |
|
88
|
|
|
|
|
89
|
|
|
-- -------------------------------------------------------- |
|
90
|
|
|
|
|
91
|
|
|
-- |
|
92
|
|
|
-- Table structure for table `dds_member` |
|
93
|
|
|
-- |
|
94
|
|
|
|
|
95
|
|
|
DROP TABLE IF EXISTS `dds_member`; |
|
96
|
|
|
CREATE TABLE `dds_member` ( |
|
97
|
|
|
`class_type` char(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The class reference', |
|
98
|
|
|
`member_ref` char(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'A reference for the member used within the code generation', |
|
99
|
|
|
`data_type_ref` char(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The reference to the data type', |
|
100
|
|
|
`label` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a user facing label for the member', |
|
101
|
|
|
`description` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'A user facing description of the member', |
|
102
|
|
|
`definition` text COLLATE utf8mb4_unicode_ci COMMENT 'A definition for use by other modules, but not understood by DDS', |
|
103
|
|
|
`choices` text COLLATE utf8mb4_unicode_ci COMMENT 'If the member is of data type choice, the set of available choices', |
|
104
|
|
|
`link_class` char(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'For links, the linked classtype.', |
|
105
|
|
|
`map_field` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'If true, then this is field data used in defining maps', |
|
106
|
|
|
`created` datetime NOT NULL COMMENT 'When the member was first added', |
|
107
|
|
|
`updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'When the member was last updated', |
|
108
|
|
|
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'whether or not this field has been soft deleted', |
|
109
|
|
|
UNIQUE KEY `class_member` (`class_type`,`member_ref`) USING BTREE, |
|
110
|
|
|
KEY `data_type` (`data_type_ref`) USING BTREE, |
|
111
|
|
|
KEY `member_ref` (`member_ref`), |
|
112
|
|
|
CONSTRAINT `dds_member_class_ref` FOREIGN KEY (`class_type`) REFERENCES `dds_class` (`class_type`) ON UPDATE CASCADE, |
|
113
|
|
|
CONSTRAINT `dds_member_data_type_ref` FOREIGN KEY (`data_type_ref`) REFERENCES `dds_data_type` (`data_type_ref`) ON UPDATE CASCADE |
|
114
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Defines a particular member of a data class'; |
|
115
|
|
|
|
|
116
|
|
|
-- -------------------------------------------------------- |
|
117
|
|
|
-- |
|
118
|
|
|
-- Table structure for table `dds_object` |
|
119
|
|
|
-- |
|
120
|
|
|
|
|
121
|
|
|
DROP TABLE IF EXISTS `dds_object`; |
|
122
|
|
|
CREATE TABLE `dds_object` ( |
|
123
|
|
|
`_uuid` char(22) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL COMMENT 'A universally unique identifier for this object', |
|
124
|
|
|
`_class_type` char(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The class this object is an instance of', |
|
125
|
|
|
`_deleted` tinyint(1) DEFAULT '0' COMMENT 'whether or not the object has been deleted', |
|
126
|
|
|
`_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'when this was created', |
|
127
|
|
|
`_updated` timestamp NULL DEFAULT NULL COMMENT 'when this was updated', |
|
128
|
|
|
PRIMARY KEY (`_uuid`), |
|
129
|
|
|
KEY `class_ref` (`_class_type`), |
|
130
|
|
|
CONSTRAINT `dds_object_class_type` FOREIGN KEY (`_class_type`) REFERENCES `dds_class` (`class_type`) ON UPDATE CASCADE |
|
131
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='An instance of a particular data type'; |
|
132
|
|
|
|
|
133
|
|
|
-- -------------------------------------------------------- |
|
134
|
|
|
-- |
|
135
|
|
|
-- Table structure for table `dds_change_log` |
|
136
|
|
|
-- |
|
137
|
|
|
|
|
138
|
|
|
DROP TABLE IF EXISTS `dds_change_log`; |
|
139
|
|
|
CREATE TABLE `dds_change_log` ( |
|
140
|
|
|
`log_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Autoincremented id to ensure entries made within the same second can be distinguished', |
|
141
|
|
|
`log_uuid` char(22) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL DEFAULT '' COMMENT 'Log unique identifier', |
|
142
|
|
|
`module` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT 'daedalus' COMMENT 'The module managing the object - default to daedalus', |
|
143
|
|
|
`object_uuid` char(22) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL COMMENT 'The object that was changed', |
|
144
|
|
|
`associated_objects` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'List of associated object uuids', |
|
145
|
|
|
`class_type` char(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'The class the object is an instance of', |
|
146
|
|
|
`change_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'A key defining the type of change e.g. EDIT', |
|
147
|
|
|
`description` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'A short user friendly description of the change', |
|
148
|
|
|
`who` char(22) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT '' COMMENT 'Who made the changes if known', |
|
149
|
|
|
`when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the changes were made', |
|
150
|
|
|
`before` longblob COMMENT 'What the changed fields values were before the change', |
|
151
|
|
|
`after` longblob COMMENT 'What the changed fields values were after the change', |
|
152
|
|
|
PRIMARY KEY (`log_id`), |
|
153
|
|
|
KEY `log_uuid` (`log_uuid`), |
|
154
|
|
|
KEY `object_uuid` (`object_uuid`), |
|
155
|
|
|
KEY `class_type` (`class_type`), |
|
156
|
|
|
KEY `change_key` (`change_key`) |
|
157
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Log of changes made to selected Daedalus tables'; |
|
158
|
|
|
EOQ; |
|
159
|
|
|
$command = $connection->createCommand($sql); |
|
160
|
|
|
$command->execute(); |
|
161
|
|
|
|
|
162
|
|
|
$sql=<<<EOQ |
|
163
|
|
|
-- |
|
164
|
|
|
-- Dumping data for table `dds_storage` |
|
165
|
|
|
-- |
|
166
|
|
|
|
|
167
|
|
|
INSERT INTO `dds_storage` (`storage_ref`, `label`, `type`, `description`) |
|
168
|
|
|
VALUES |
|
169
|
|
|
('binary', 'Normal Binary', 'BINARY', 'For normal binaries such as images up to 16MB in size'), |
|
170
|
|
|
('binarylong', 'Very Large Binary', 'BINARY_LONG', 'For storing binary data such as documents up to 4GB in size'), |
|
171
|
|
|
('binaryshort', 'Small Binary', 'BINARY_SHORT', 'For small binaries up to 16KB in size (e.g thumbnails)'), |
|
172
|
|
|
('char', 'Fixed Char', 'CHAR', 'For char of a fixed length (set by data type) up to 150 characters'), |
|
173
|
|
|
('date', 'Date', 'DATE', 'For storing a date (no time associated) from the years 1000 to 9999'), |
|
174
|
|
|
('datetime', 'Date and Time', 'DATETIME', 'For storing date and time from the years 1000 to 9999'), |
|
175
|
|
|
('float', 'Float', 'FLOAT', 'For floating numbers (decimal with limited but large precision) up to about 1 x 10^±38'), |
|
176
|
|
|
('floatdouble', 'Double Float', 'DOUBLE', 'A double precision float up to 1 x 10^±308'), |
|
177
|
|
|
('integer', 'Integer', 'INTEGER', 'For integers up to ±2,147,483,648'), |
|
178
|
|
|
('integerlong', 'Large Integer', 'INTEGER_LONG', 'For integers up to 9 quintillion (9 with 18 zeros after it). '), |
|
179
|
|
|
('integershort', 'Short Integer', 'INTEGER_SHORT', 'For integers up to ±8192'), |
|
180
|
|
|
('integertiny', 'Tiny Integer', 'INTEGER_TINY', 'For numbers up to ±128'), |
|
181
|
|
|
('text', 'Text', 'TEXT', 'For text up to about twenty thousand characters (about two to four thousand average English words)'), |
|
182
|
|
|
('textlong', 'Long Text', 'TEXT_LONG', 'For text up to about five million characters (about half to one million average English words). '), |
|
183
|
|
|
('textshort', 'Short Text', 'TEXT_SHORT', 'For text up to 150 characters (about 15 to 30 words)'), |
|
184
|
|
|
('time', 'Time', 'TIME', 'For storing time only'), |
|
185
|
|
|
('uuid', 'Universally Unique Id', 'UUID', 'For storing UUIDs (which are stored as case sensitive chars of fixed length)'); |
|
186
|
|
|
|
|
187
|
|
|
-- |
|
188
|
|
|
-- Dumping data for table `dds_data_type` |
|
189
|
|
|
-- |
|
190
|
|
|
|
|
191
|
|
|
INSERT INTO `dds_data_type` (`data_type_ref`, `label`, `description`, `definition`, `storage_ref`, `deleted`) |
|
192
|
|
|
VALUES |
|
193
|
|
|
('binary', 'Normal Binary', 'For normal binaries such as images up to 16MB in size', '', 'binary', 0), |
|
194
|
|
|
('binarylong', 'Very Large Binary', 'For storing binary data such as documents up to 4GB in size', '', 'binarylong', 0), |
|
195
|
|
|
('binaryshort', 'Small Binary', 'For small binaries up to 16KB in size (e.g thumbnails)', '', 'binaryshort', 0), |
|
196
|
|
|
('boolean', 'Boolean', 'For true | false value', '{\"size\":1}', 'integertiny', 0), |
|
197
|
|
|
('choice', 'Choice', 'For the key of a key:value choice. The mapping is stored elsewhere', '{max:150}', 'textshort', 0), |
|
198
|
|
|
('choice_multiple', 'Multiple Choice', 'For storing multiple choice keys', '{\"size\":10000}', 'text', 0), |
|
199
|
|
|
('date', 'Date', 'For storing a date (no time associated) from the years 1000 to 9999', '', 'date', 0), |
|
200
|
|
|
('datetime', 'Date and Time', 'For storing date and time from the years 1000 to 9999', '', 'datetime', 0), |
|
201
|
|
|
('document', 'Document', 'For storing documents up to 4GB in size', '', 'binarylong', 0), |
|
202
|
|
|
('email', 'Email', 'For emails up to 150 characters', '{max:150}', 'textshort', 0), |
|
203
|
|
|
('file_ref', 'File Reference', 'A UUID64 reference for a file', '{\"size\":22}', 'uuid', 0), |
|
204
|
|
|
('file_ref_multi', 'Multiple File References', 'Multiple files stored against member. Data is stored in dds_link not the ddt_xxx table.', '{\"size\":10}', 'char', 0), |
|
205
|
|
|
('float', 'Float', 'For floating numbers (decimal with limited but large precision) up to about 1 x 10^±38', '', 'float', 0), |
|
206
|
|
|
('floatdouble', 'Double Float', 'A double precision float up to 1 x 10^±308', '', 'floatdouble', 0), |
|
207
|
|
|
('html', 'HTML', 'For HTML up to about a million characters', '{\"size\":1000000}', 'textlong', 0), |
|
208
|
|
|
('image', 'Image File', 'For storing images up to 16MB in size', '', 'binary', 0), |
|
209
|
|
|
('image_ref', 'Image Reference', 'A UUID64 reference for an image', '{\"size\":22}', 'uuid', 0), |
|
210
|
|
|
('integer', 'Integer', 'For integers up to ±2,147,483,648', '', 'integer', 0), |
|
211
|
|
|
('integerlong', 'Large Integer', 'For integers up to 9 quintillion (9 with 18 zeros after it).', '', 'integerlong', 0), |
|
212
|
|
|
('integershort', 'Short Integer', 'For integers up to ±8192', '{\"max\":8192}', 'integershort', 0), |
|
213
|
|
|
('integertiny', 'Tiny Integer', 'For numbers up to ±128', '{\"max\":128}', 'integertiny', 0), |
|
214
|
|
|
('json', 'JSON', 'For JSON up to about a million characters ', '{max:1000000}', 'textlong', 0), |
|
215
|
|
|
('link_multi', 'Link - Many to Many', 'Single or many to many link placeholder. Can be used in joins. Data is stored in dds_link not the ddt_xxx table.', '{\"size\":10}', 'char', 0), |
|
216
|
|
|
('link_uni', 'Link - One to One', 'An object reference (uuid64) linking one object to another', '{max:22}', 'uuid', 0), |
|
217
|
|
|
('link_uni_object', 'Link Object - One to One', 'An object reference (uuid64) linking one object to any object across multiple daedalus table.', '{max:22}', 'uuid', 0), |
|
218
|
|
|
('markdown', 'Markdown', 'For markdown files of up to about a million characters ', '{max:1000000}', 'textlong', 0), |
|
219
|
|
|
('mime', 'MIME Type', 'For storing mime types', '{\"size\":256}', 'text', 0), |
|
220
|
|
|
('text', 'Text', 'For text up to about twenty thousand characters (about two to four thousand average English words)', '{\"size\":20000}', 'text', 0), |
|
221
|
|
|
('textlong', 'Long Text', 'For text up to about five million characters (about half to one million average English words).', '{\"size\":5000000}', 'textlong', 0), |
|
222
|
|
|
('textshort', 'Short Text', 'For text up to 150 characters (about 15 to 30 words)', '{\"size\":150}', 'textshort', 0), |
|
223
|
|
|
('time', 'Time', 'For storing a time only field', '', 'time', 0), |
|
224
|
|
|
('url', 'URL', 'URL up to 150 characters', '{\"size\":150}', 'textshort', 0), |
|
225
|
|
|
('uuid', 'UUID', 'For Universally Unique Identifiers', '{\"size\":36}', 'uuid', 0), |
|
226
|
|
|
('uuid64', 'UUID64', 'For Universally Unique Identifiers in base 64', '{\"size\":22}', 'uuid', 0); |
|
227
|
|
|
|
|
228
|
|
|
|
|
229
|
|
|
EOQ; |
|
230
|
|
|
$command = $connection->createCommand($sql); |
|
231
|
|
|
$command->execute(); |
|
232
|
|
|
} |
|
233
|
|
|
|
|
234
|
|
|
public function down() |
|
235
|
|
|
{ |
|
236
|
|
|
# deletion order is important because of foreign key constraints |
|
237
|
|
|
$this->dropTable('dds_change_log'); |
|
238
|
|
|
$this->dropTable('dds_object'); |
|
239
|
|
|
$this->dropTable('dds_member'); |
|
240
|
|
|
$this->dropTable('dds_link'); |
|
241
|
|
|
$this->dropTable('dds_class'); |
|
242
|
|
|
$this->dropTable('dds_data_type'); |
|
243
|
|
|
$this->dropTable('dds_storage'); |
|
244
|
|
|
} |
|
245
|
|
|
} |
|
246
|
|
|
|
Loading history...