· 10 years ago · Sep 27, 2016, 01:12 PM
1USE SYS;
2DROP DATABASE prototype_DB_2_DINAMIC;
3
4
5CREATE DATABASE prototype_DB_2_DINAMIC;
6use prototype_DB_2_DINAMIC;
7
8
9-- DROP TABLE IF EXISTS `Entity`;
10CREATE TABLE `Entity` (
11 `entID` int(11) NOT NULL AUTO_INCREMENT,
12 `entName` varchar(30) NOT NULL,
13 PRIMARY KEY (`entID`)
14);
15
16
17-- DROP TABLE IF EXISTS `EntityInstance`;
18CREATE TABLE `EntityInstance` (
19 `entInsID` int(11) NOT NULL AUTO_INCREMENT,
20 -- `entInsName` varchar(30) NOT NULL,
21 `entInsEntity`int(11) not null,
22 PRIMARY KEY (`entInsID`),
23 CONSTRAINT `EntityInstance_ibfk_1` FOREIGN KEY (`entInsEntity`) REFERENCES `Entity` (`entID`)
24);
25
26
27CREATE TABLE `Fsm` (
28 `fsmID` int(11) NOT NULL AUTO_INCREMENT,
29 `fsmName` varchar(20) NOT NULL,
30 `fsmEntityID`int(11) not null,
31 PRIMARY KEY (`fsmID`),
32 CONSTRAINT `Fsm_ibfk_3` FOREIGN KEY (`fsmEntityID`) REFERENCES `Entity` (`entID`)
33);
34
35
36CREATE TABLE `State` (
37 `staID` int(11) NOT NULL AUTO_INCREMENT,
38 `staName` varchar(20) NOT NULL,
39 `staFsmID` int(11) NOT NULL,
40 PRIMARY KEY (`staID`),
41 KEY `staFsmID` (`staFsmID`),
42 CONSTRAINT `state_ibfk_1` FOREIGN KEY (`staFsmID`) REFERENCES `FSM` (`fsmID`)
43);
44
45/*
46CREATE TABLE `finitestatemachinepath` (
47 `fsmPathID` int(11) NOT NULL AUTO_INCREMENT,
48 `fsmPathFsmID` int(11) NOT NULL,
49 `fsmPathFrom` int(11) NOT NULL,
50 `fsmPathTo` int(11) NOT NULL,
51 `fsmPathAction` varchar(45) NOT NULL DEFAULT 'coise',
52 PRIMARY KEY (`fsmPathID`),
53 KEY `fsmPathFsmID` (`fsmPathFsmID`),
54 KEY `fsmPathFrom` (`fsmPathFrom`),
55 KEY `fsmPathTo` (`fsmPathTo`),
56 CONSTRAINT `finitestatemachinepath_ibfk_1` FOREIGN KEY (`fsmPathFsmID`) REFERENCES `finitestatemachine` (`fsmID`),
57 CONSTRAINT `finitestatemachinepath_ibfk_2` FOREIGN KEY (`fsmPathFrom`) REFERENCES `state` (`staID`),
58 CONSTRAINT `finitestatemachinepath_ibfk_3` FOREIGN KEY (`fsmPathTo`) REFERENCES `state` (`staID`)
59);
60*/
61
62CREATE TABLE `FsmInstance`(
63 `fsmInsID` int(11) NOT NULL AUTO_INCREMENT,
64 `fsmInsName` varchar(20) NOT NULL,
65 `fsmInsFsmID` int(11) NOT NULL,
66 `fsmInsStateID`int(11) NOT NULl,
67 `fsmInsEntityInstanceID` int(11) not null,
68
69 PRIMARY KEY (`fsmInsID`),
70-- KEY `fsmInsFsmID` (`fsmInsFsmID`),
71-- KEY `fsmInsStateID`(`fsmInsStateID`),
72 -- KEY `fsmInsEntity`(`fsmInsEntity`),
73 CONSTRAINT `FsmInstance_ibfk_1` FOREIGN KEY (`fsmInsFsmID`) REFERENCES `Fsm` (`fsmID`),
74 CONSTRAINT `FsmInstance_ibfk_2` FOREIGN KEY (`fsmInsStateID`) REFERENCES `State` (`staID`),
75 CONSTRAINT `FsmInstance_ibfk_3` FOREIGN KEY (`fsmInsEntityInstanceID`) REFERENCES `EntityInstance` (`entInsID`)
76);
77
78
79CREATE TABLE `AttributeType`(
80 `attTypeID` int(11) NOT NULL AUTO_INCREMENT,
81 `attTypeName` varchar(20) NOT NULL,
82
83 PRIMARY KEY (`attTypeID`)
84 -- CONSTRAINT `Attribute_ibfk_1` FOREIGN KEY (`attTypeID`) REFERENCES `.......` (`....`),
85);
86
87CREATE TABLE `Attribute`(
88 `attID` int(11) NOT NULL AUTO_INCREMENT,
89 `attName` varchar(20) NOT NULL,
90 `attTypeID` int(11) NOT NULL, /* other table */
91
92 PRIMARY KEY (`attID`),
93 CONSTRAINT `Attribute_ibfk_1` FOREIGN KEY (`attTypeID`) REFERENCES `AttributeType` (`attTypeID`)
94);
95
96
97CREATE TABLE `AttributeEntity`(
98 `attEntID` int(11) NOT NULL,
99 `attEntAttributeID` int(11) NOT NULL,
100
101 PRIMARY KEY (`attEntID`, `attEntAttributeID`),
102 CONSTRAINT `AttributeEntity_ibfk_1` FOREIGN KEY (`attEntID`) REFERENCES `Entity` (`entID`),
103 CONSTRAINT `AttributeEntity_ibfk_2` FOREIGN KEY (`attEntAttributeID`) REFERENCES `Attribute` (`attID`)
104);
105
106
107CREATE TABLE `AttributeFsm`(
108 `attFsmID` int(11) NOT NULL AUTO_INCREMENT,
109 `attFsmFsmID` int(11) NOT NULL,
110 `attFsmAttributeID` int(11) NOT NULL,
111
112 PRIMARY KEY (`attFsmID`),
113 CONSTRAINT `AttributeFsm_ibfk_1` FOREIGN KEY (`attFsmFsmID`) REFERENCES `Fsm` (`fsmID`),
114 CONSTRAINT `AttributeFsm_ibfk_2` FOREIGN KEY (`attFsmAttributeID`) REFERENCES `Attribute` (`attID`)
115);
116
117
118
119
120
121
122INSERT INTO `Entity` (`entName`) VALUES ('Keynote');
123INSERT INTO `Entity` (`entName`) VALUES ('Deadlines');
124INSERT INTO `EntityInstance` (`entInsEntity`) VALUES (1);
125INSERT INTO `EntityInstance` (`entInsEntity`) VALUES (2);
126
127
128INSERT INTO `AttributeType` (`attTypeName`) VALUES ('Text');
129INSERT INTO `AttributeType` (`attTypeName`) VALUES ('Bool');
130INSERT INTO `AttributeType` (`attTypeName`) VALUES ('Date');
131
132/*
133atributos da entidade - keynotes
134*/
135INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Name', 1);
136INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Country', 1);
137INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Affiliation', 1);
138
139/*
140atributos da fsm - keynotes
141*/
142INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Visible', 2);
143INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Canceled', 2);
144INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Presented', 2);
145
146/*
147atributos da entidade - deadlines
148*/
149INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Date', 3);
150
151/*
152atributos da fsm - deadlines
153*/
154INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Visible', 2);
155INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Canceled', 2);
156INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Extended', 2);
157INSERT INTO `Attribute` (`attName`, `attTypeID`) VALUES ('Expired', 2);
158
159
160INSERT INTO `Fsm` (`fsmName`, `fsmEntityID`) VALUES ('Keynote', 1);
161INSERT INTO `Fsm` (`fsmName`, `fsmEntityID`) VALUES ('Deadlines', 2);
162
163
164INSERT INTO `State` (`staName`, `staFsmID`) VALUES ('Invited' , 1);
165INSERT INTO `State` (`staName`, `staFsmID`) VALUES ('Visible' , 1);
166INSERT INTO `State` (`staName`, `staFsmID`) VALUES ('Canceled' , 1);
167INSERT INTO `State` (`staName`, `staFsmID`) VALUES ('Presented' , 1);
168INSERT INTO `State` (`staName`, `staFsmID`) VALUES ('Canceled Hidden', 1);
169
170
171/*
172Associar atributos à fsm -> keynotes
173*/
174INSERT INTO `AttributeFsm` (`attFsmFsmID`, `attFsmAttributeID`) VALUES (1, 4);
175INSERT INTO `AttributeFsm` (`attFsmFsmID`, `attFsmAttributeID`) VALUES (1, 5);
176INSERT INTO `AttributeFsm` (`attFsmFsmID`, `attFsmAttributeID`) VALUES (1, 6);
177
178/*
179Associar atributos à entidade -> keynotes
180*/
181INSERT INTO `AttributeEntity` (`attEntID`, `attEntAttributeID`) VALUES (1, 1);
182INSERT INTO `AttributeEntity` (`attEntID`, `attEntAttributeID`) VALUES (1, 2);
183INSERT INTO `AttributeEntity` (`attEntID`, `attEntAttributeID`) VALUES (1, 3);
184
185
186/*
187Associar atributos à entidade -> Deadline
188*/
189INSERT INTO `AttributeEntity` (`attEntID`, `attEntAttributeID`) VALUES (2, 7);
190
191/*
192Associar atributos à fsm -> Deadline
193*/
194INSERT INTO `AttributeFsm` (`attFsmFsmID`, `attFsmAttributeID`) VALUES (2, 8);
195INSERT INTO `AttributeFsm` (`attFsmFsmID`, `attFsmAttributeID`) VALUES (2, 9);
196INSERT INTO `AttributeFsm` (`attFsmFsmID`, `attFsmAttributeID`) VALUES (2, 10);
197INSERT INTO `AttributeFsm` (`attFsmFsmID`, `attFsmAttributeID`) VALUES (2, 11);
198
199
200
201
202-- todos os atributos de uma entidade, juntamente com os atributos da sua fsm
203
204/*
205-- estavel but old
206SELECT entName, attName, attTypeName, attFsmFsmID
207FROM `AttributeFsm`
208JOIN `Attribute` a ON attFsmAttributeID = attID
209JOIN `AttributeType` aType ON a.attTypeID = aType.attTypeID
210JOIN `Fsm` ON attFsmFsmID = fsmID
211JOIN `Entity` ON fsmEntityID = entID
212UNION
213SELECT entName, attName, attTypeName, attEntID
214FROM `AttributeEntity`
215JOIN `Attribute` a ON attEntAttributeID = attID
216JOIN `AttributeType` aType ON a.attTypeID = aType.attTypeID
217JOIN `Entity` ON attEntID = entID;
218*/
219
220
221
222select entID, entName, attName, attTypeName, attFsmFsmID
223from (
224SELECT entID, entName, attName, attTypeName, attFsmFsmID
225FROM `AttributeFsm`
226JOIN `Attribute` a ON attFsmAttributeID = attID
227JOIN `AttributeType` aType ON a.attTypeID = aType.attTypeID
228JOIN `Fsm` ON attFsmFsmID = fsmID
229JOIN `Entity` ON fsmEntityID = entID
230UNION
231SELECT entID, entName, attName, attTypeName, attEntID
232FROM `AttributeEntity`
233JOIN `Attribute` a ON attEntAttributeID = attID
234JOIN `AttributeType` aType ON a.attTypeID = aType.attTypeID
235JOIN `Entity` ON attEntID = entID
236) t order by entID;
237
238
239
240/*
241SELECT entName, attName, attTypeName, attEntID
242FROM `AttributeEntity`
243JOIN `Attribute` a ON attEntAttributeID = attID
244JOIN `AttributeType` aType ON a.attTypeID = aType.attTypeID
245JOIN `Entity` ON fsmEntityID = entID;
246*/
247
248
249/*
250 SELECT * FROM `Entity`;
251SELECT * FROM `EntityInstance`;
252 SELECT * FROM `AttributeType`;
253 SELECT * FROM `Attribute`;
254 SELECT * FROM `Fsm`;
255 SELECT * FROM `State`;
256 SELECT * FROM `AttributeFsm`;
257*/