· 5 years ago · Feb 24, 2020, 06:50 AM
1
2
3-- Drop All
4SET FOREIGN_KEY_CHECKS=0;
5
6DROP TABLE IF EXISTS TestCaseStep;
7DROP TABLE IF EXISTS TestCaseNote;
8DROP TABLE IF EXISTS FuseObjectProperty;
9DROP TABLE IF EXISTS TestCase;
10DROP TABLE IF EXISTS FuseObjectPropertyType;
11DROP TABLE IF EXISTS FuseObjectBusinessKey;
12DROP TABLE IF EXISTS FuseObject;
13DROP TABLE IF EXISTS Destination;
14DROP TABLE IF EXISTS UserInfo;
15
16SET FOREIGN_KEY_CHECKS=1;
17
18
19-- Create All
20
21-- Generated from file in/test.omc version author by brian as at 2020-02-02 15:42:18.639417
22CREATE TABLE UserInfo (
23 ID int auto_increment NOT NULL COMMENT 'Row Identifier'
24, ObjID int NOT NULL COMMENT 'Object Identifier'
25, Ident varchar(45) NOT NULL COMMENT 'the unique identity of the user'
26, UserName varchar(128) NOT NULL COMMENT 'the user"s full name'
27, EffectiveFrom datetime NOT NULL COMMENT 'From when is this version valid?'
28, EffectiveTo datetime NULL COMMENT 'Until when is this version valid?'
29, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
30, CreatedByID int NOT NULL COMMENT ''
31, CreatedDateTime datetime NOT NULL COMMENT ''
32, ModifiedByID int NOT NULL COMMENT ''
33, ModifiedDateTime datetime NOT NULL COMMENT ''
34, RevisionNumber int NOT NULL COMMENT ''
35
36-- Constraints
37
38, CONSTRAINT PK_UserInfo
39 PRIMARY KEY (ID)
40, CONSTRAINT BK_UserInfo_Ident
41 UNIQUE KEY (Ident)
42, CONSTRAINT BK_UserInfo_ObjIDEffectiveFrom
43 UNIQUE KEY (ObjID, EffectiveFrom)
44) COMMENT ' The list of users allowed in the tenant';
45
46GRANT SELECT,INSERT,UPDATE ON TABLE UserInfo TO `root`@`localhost`;
47-- Generated from file in/meta.omc version author by brian as at 2020-02-02 15:42:18.639417
48CREATE TABLE Destination (
49 ID int auto_increment NOT NULL COMMENT 'Row Identifier'
50, ObjID int NOT NULL COMMENT 'Object Identifier'
51, Ident varchar(45) NOT NULL COMMENT 'Name of the Destination (FF4, Tenant, ...?)'
52, EffectiveFrom datetime NOT NULL COMMENT 'From when is this version valid?'
53, EffectiveTo datetime NULL COMMENT 'Until when is this version valid?'
54, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
55, CreatedByID int NOT NULL COMMENT ''
56, CreatedDateTime datetime NOT NULL COMMENT ''
57, ModifiedByID int NOT NULL COMMENT ''
58, ModifiedDateTime datetime NOT NULL COMMENT ''
59, RevisionNumber int NOT NULL COMMENT ''
60
61-- Constraints
62
63, CONSTRAINT PK_Destination
64 PRIMARY KEY (ID)
65, CONSTRAINT FK_Destination_CreatedBy
66 FOREIGN KEY (CreatedByID)
67 REFERENCES UserInfo (ID)
68, CONSTRAINT FK_Destination_ModifiedBy
69 FOREIGN KEY (ModifiedByID)
70 REFERENCES UserInfo (ID)
71, CONSTRAINT BK_Destination_ObjIDEffectiveFrom
72 UNIQUE KEY (ObjID, EffectiveFrom)
73) COMMENT ' Blah';
74
75GRANT SELECT,INSERT,UPDATE ON TABLE Destination TO `root`@`localhost`;
76-- Generated from file in/meta.omc version author by brian as at 2020-02-02 15:42:18.639417
77CREATE TABLE FuseObject (
78 ID int auto_increment NOT NULL COMMENT 'Row Identifier'
79, ObjID int NOT NULL COMMENT 'Object Identifier'
80, SQLIdent varchar(45) NOT NULL COMMENT 'Name of Object in SQL'
81, JSONIdent varchar(45) NOT NULL COMMENT 'Name of Object in JSON'
82, DestinationID int NULL COMMENT 'Which database should this go to.'
83, Purpose varchar(1024) NULL COMMENT 'Brief description of object"s purpose'
84, IsIDed bool NOT NULL COMMENT 'Does the object have an ID?'
85, IsTimephased bool NOT NULL COMMENT 'Is the object time phased?'
86, IsMetaed bool NOT NULL COMMENT 'Does the object have meta data?'
87, IsAPI bool NOT NULL COMMENT 'Is this object part of the API?'
88, ParentFuseObjectID int NULL COMMENT 'Which Object is this a child of, if any?'
89, PrimaryKeyList varchar(1024) NOT NULL COMMENT 'Which properties make up the primary key?'
90, SecureAreaPath varchar(1024) NULL COMMENT 'What is the path to the list of Areas on this object, if any?'
91, SecureDepartmentPath varchar(1024) NULL COMMENT 'What is the path to the list of Departments on this object, if any?'
92, EffectiveFrom datetime NOT NULL COMMENT 'From when is this version valid?'
93, EffectiveTo datetime NULL COMMENT 'Until when is this version valid?'
94, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
95, CreatedByID int NOT NULL COMMENT ''
96, CreatedDateTime datetime NOT NULL COMMENT ''
97, ModifiedByID int NOT NULL COMMENT ''
98, ModifiedDateTime datetime NOT NULL COMMENT ''
99, RevisionNumber int NOT NULL COMMENT ''
100
101-- Constraints
102
103, CONSTRAINT PK_FuseObject
104 PRIMARY KEY (ID)
105, CONSTRAINT FK_FuseObject_Destination
106 FOREIGN KEY (DestinationID)
107 REFERENCES Destination (ID)
108, CONSTRAINT FK_FuseObject_FuseObject
109 FOREIGN KEY (ParentFuseObjectID)
110 REFERENCES FuseObject (ID)
111, CONSTRAINT FK_FuseObject_CreatedBy
112 FOREIGN KEY (CreatedByID)
113 REFERENCES UserInfo (ID)
114, CONSTRAINT FK_FuseObject_ModifiedBy
115 FOREIGN KEY (ModifiedByID)
116 REFERENCES UserInfo (ID)
117, CONSTRAINT BK_FuseObject_ObjIDEffectiveFrom
118 UNIQUE KEY (ObjID, EffectiveFrom)
119) COMMENT ' Blah';
120
121GRANT SELECT,INSERT,UPDATE ON TABLE FuseObject TO `root`@`localhost`;
122-- Generated from file in/meta.omc version author by brian as at 2020-02-02 15:42:18.639417
123CREATE TABLE FuseObjectBusinessKey (
124 ID int auto_increment NOT NULL COMMENT 'Row Identifier'
125, ObjID int NOT NULL COMMENT 'Object Identifier'
126, ParentFuseObjectID int NOT NULL COMMENT 'What is the owning FuseObject?'
127, Ident varchar(45) NOT NULL COMMENT ''
128, PropertyList varchar(1024) NOT NULL COMMENT ''
129, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
130, CreatedByID int NOT NULL COMMENT ''
131, CreatedDateTime datetime NOT NULL COMMENT ''
132, ModifiedByID int NOT NULL COMMENT ''
133, ModifiedDateTime datetime NOT NULL COMMENT ''
134, RevisionNumber int NOT NULL COMMENT ''
135, EffectiveFrom datetime NOT NULL COMMENT 'From when is this version valid?'
136, EffectiveTo datetime NULL COMMENT 'Until when is this version valid?'
137
138-- Constraints
139
140, CONSTRAINT PK_FuseObjectBusinessKey
141 PRIMARY KEY (ID)
142, CONSTRAINT FK_FuseObjectBusinessKey_FuseObject
143 FOREIGN KEY (ParentFuseObjectID)
144 REFERENCES FuseObject (ID)
145, CONSTRAINT FK_FuseObjectBusinessKey_CreatedBy
146 FOREIGN KEY (CreatedByID)
147 REFERENCES UserInfo (ID)
148, CONSTRAINT FK_FuseObjectBusinessKey_ModifiedBy
149 FOREIGN KEY (ModifiedByID)
150 REFERENCES UserInfo (ID)
151, CONSTRAINT BK_FuseObjectBusinessKey_ObjIDEffectiveFrom
152 UNIQUE KEY (ObjID, EffectiveFrom)
153) COMMENT ' Which business keys are there? [ list for FuseObject ]';
154
155GRANT SELECT,INSERT,UPDATE ON TABLE FuseObjectBusinessKey TO `root`@`localhost`;
156-- Generated from file in/meta.omc version author by brian as at 2020-02-02 15:42:18.639417
157CREATE TABLE FuseObjectPropertyType (
158 ID int auto_increment NOT NULL COMMENT 'Row Identifier'
159, ObjID int NOT NULL COMMENT 'Object Identifier'
160, Ident varchar(45) NOT NULL COMMENT 'OMC Name'
161, SQLTypeName varchar(45) NOT NULL COMMENT 'Type in SQL'
162, JSONTypeName varchar(45) NOT NULL COMMENT 'Type in JSON'
163, IsMeta bool NOT NULL COMMENT 'Is this type always meta?'
164, EffectiveFrom datetime NOT NULL COMMENT 'From when is this version valid?'
165, EffectiveTo datetime NULL COMMENT 'Until when is this version valid?'
166, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
167, CreatedByID int NOT NULL COMMENT ''
168, CreatedDateTime datetime NOT NULL COMMENT ''
169, ModifiedByID int NOT NULL COMMENT ''
170, ModifiedDateTime datetime NOT NULL COMMENT ''
171, RevisionNumber int NOT NULL COMMENT ''
172
173-- Constraints
174
175, CONSTRAINT PK_FuseObjectPropertyType
176 PRIMARY KEY (ID)
177, CONSTRAINT FK_FuseObjectPropertyType_CreatedBy
178 FOREIGN KEY (CreatedByID)
179 REFERENCES UserInfo (ID)
180, CONSTRAINT FK_FuseObjectPropertyType_ModifiedBy
181 FOREIGN KEY (ModifiedByID)
182 REFERENCES UserInfo (ID)
183, CONSTRAINT BK_FuseObjectPropertyType_ObjIDEffectiveFrom
184 UNIQUE KEY (ObjID, EffectiveFrom)
185) COMMENT ' Blah';
186
187GRANT SELECT,INSERT,UPDATE ON TABLE FuseObjectPropertyType TO `root`@`localhost`;
188-- Generated from file version author by brian as at 2020-02-02 15:42:18.639417
189CREATE TABLE TestCase (
190 ID int auto_increment NOT NULL COMMENT 'Row Identifier'
191, ObjID int NOT NULL COMMENT 'Object Identifier'
192, Ident varchar(45) NOT NULL COMMENT ''
193, TestName varchar(45) NOT NULL COMMENT ''
194, EffectiveFrom datetime NOT NULL COMMENT 'From when is this version valid?'
195, EffectiveTo datetime NULL COMMENT 'Until when is this version valid?'
196, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
197, CreatedByID int NOT NULL COMMENT ''
198, CreatedDateTime datetime NOT NULL COMMENT ''
199, ModifiedByID int NOT NULL COMMENT ''
200, ModifiedDateTime datetime NOT NULL COMMENT ''
201, RevisionNumber int NOT NULL COMMENT ''
202
203-- Constraints
204
205, CONSTRAINT PK_TestCase
206 PRIMARY KEY (ID)
207, CONSTRAINT FK_TestCase_CreatedBy
208 FOREIGN KEY (CreatedByID)
209 REFERENCES UserInfo (ID)
210, CONSTRAINT FK_TestCase_ModifiedBy
211 FOREIGN KEY (ModifiedByID)
212 REFERENCES UserInfo (ID)
213, CONSTRAINT BK_TestCase_ObjIDEffectiveFrom
214 UNIQUE KEY (ObjID, EffectiveFrom)
215) COMMENT ' For testing stored proc generation.';
216
217GRANT SELECT,INSERT,UPDATE ON TABLE TestCase TO `root`@`localhost`;
218-- Generated from file in/meta.omc version author by brian as at 2020-02-02 15:42:18.639417
219CREATE TABLE FuseObjectProperty (
220 ParentFuseObjectID int NOT NULL COMMENT 'What is the owning FuseObject?'
221, SQLIdent varchar(45) NOT NULL COMMENT 'Name of Property in SQL'
222, JSONIdent varchar(45) NOT NULL COMMENT 'Name of Property in JSON'
223, TypeID int NOT NULL COMMENT 'Type of Property'
224, PropertyLength int NULL COMMENT 'For Strings only'
225, PropertyRank int NOT NULL COMMENT 'Where should this property be listed?'
226, Comment varchar(1024) NULL COMMENT 'x'
227, IsOptional bool NOT NULL COMMENT 'Is this property optional?'
228, IsMeta bool NOT NULL COMMENT 'Is this property meta (ie, not part of the API)'
229, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
230, CreatedByID int NOT NULL COMMENT ''
231, CreatedDateTime datetime NOT NULL COMMENT ''
232, ModifiedByID int NOT NULL COMMENT ''
233, ModifiedDateTime datetime NOT NULL COMMENT ''
234, RevisionNumber int NOT NULL COMMENT ''
235
236-- Constraints
237
238, CONSTRAINT FK_FuseObjectProperty_FuseObject
239 FOREIGN KEY (ParentFuseObjectID)
240 REFERENCES FuseObject (ID)
241, CONSTRAINT FK_FuseObjectProperty_FuseObjectPropertyType
242 FOREIGN KEY (TypeID)
243 REFERENCES FuseObjectPropertyType (ID)
244, CONSTRAINT FK_FuseObjectProperty_CreatedBy
245 FOREIGN KEY (CreatedByID)
246 REFERENCES UserInfo (ID)
247, CONSTRAINT FK_FuseObjectProperty_ModifiedBy
248 FOREIGN KEY (ModifiedByID)
249 REFERENCES UserInfo (ID)
250) COMMENT ' [ list for FuseObject ]';
251
252GRANT SELECT,INSERT,UPDATE ON TABLE FuseObjectProperty TO `root`@`localhost`;
253-- Generated from file in/test.omc version author by brian as at 2020-02-02 15:42:18.639417
254CREATE TABLE TestCaseNote (
255 ParentTestCaseID int NOT NULL COMMENT 'What is the owning TestCase?'
256, ID int auto_increment NOT NULL COMMENT ''
257, NoteText varchar(1024) NOT NULL COMMENT ''
258, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
259, CreatedByID int NOT NULL COMMENT ''
260, CreatedDateTime datetime NOT NULL COMMENT ''
261, ModifiedByID int NOT NULL COMMENT ''
262, ModifiedDateTime datetime NOT NULL COMMENT ''
263, RevisionNumber int NOT NULL COMMENT ''
264
265-- Constraints
266
267, CONSTRAINT FK_TestCaseNote_TestCase
268 FOREIGN KEY (ParentTestCaseID)
269 REFERENCES TestCase (ID)
270, CONSTRAINT PK_TestCaseNote
271 PRIMARY KEY (ID)
272, CONSTRAINT FK_TestCaseNote_CreatedBy
273 FOREIGN KEY (CreatedByID)
274 REFERENCES UserInfo (ID)
275, CONSTRAINT FK_TestCaseNote_ModifiedBy
276 FOREIGN KEY (ModifiedByID)
277 REFERENCES UserInfo (ID)
278) COMMENT ' [ list for TestCase ]';
279
280GRANT SELECT,INSERT,UPDATE ON TABLE TestCaseNote TO `root`@`localhost`;
281-- Generated from file in/test.omc version author by brian as at 2020-02-02 15:42:18.639417
282CREATE TABLE TestCaseStep (
283 ParentTestCaseID int NOT NULL COMMENT 'What is the owning TestCase?'
284, ID int auto_increment NOT NULL COMMENT ''
285, StepName varchar(45) NOT NULL COMMENT ''
286, StepNumber int NOT NULL COMMENT ''
287, IsDeleted bool NOT NULL COMMENT 'Is this row considered to be deleted?'
288, CreatedByID int NOT NULL COMMENT ''
289, CreatedDateTime datetime NOT NULL COMMENT ''
290, ModifiedByID int NOT NULL COMMENT ''
291, ModifiedDateTime datetime NOT NULL COMMENT ''
292, RevisionNumber int NOT NULL COMMENT ''
293
294-- Constraints
295
296, CONSTRAINT FK_TestCaseStep_TestCase
297 FOREIGN KEY (ParentTestCaseID)
298 REFERENCES TestCase (ID)
299, CONSTRAINT PK_TestCaseStep
300 PRIMARY KEY (ID)
301, CONSTRAINT FK_TestCaseStep_CreatedBy
302 FOREIGN KEY (CreatedByID)
303 REFERENCES UserInfo (ID)
304, CONSTRAINT FK_TestCaseStep_ModifiedBy
305 FOREIGN KEY (ModifiedByID)
306 REFERENCES UserInfo (ID)
307) COMMENT ' [ list for TestCase ]';
308
309GRANT SELECT,INSERT,UPDATE ON TABLE TestCaseStep TO `root`@`localhost`;