· 7 years ago · Oct 25, 2018, 04:38 PM
1-- ------------------------------------------------------
2-- File created - �ter�-kv�tna-01-2018
3-- ------------------------------------------------------
4-- ------------------------------------------------------
5-- DDL for Sequence DEVICE_PERSON_ADMIN_SEQ
6-- ------------------------------------------------------
7
8 CALL CreateSequence('`DEVICE_PERSON_ADMIN_SEQ`', 1, 1) NOKEEP NOSCALE GLOBAL ;
9-- ------------------------------------------------------
10-- DDL for Sequence DEVICE_PERSON_ADMIN_SEQ1
11-- ------------------------------------------------------
12
13 CALL CreateSequence('`DEVICE_PERSON_ADMIN_SEQ1`', 21, 1) NOKEEP NOSCALE GLOBAL ;
14-- ------------------------------------------------------
15-- DDL for Sequence DEVICE_PERSON_USED_SEQ
16-- ------------------------------------------------------
17
18 CALL CreateSequence('`DEVICE_PERSON_USED_SEQ`', 21, 1) NOKEEP NOSCALE GLOBAL ;
19-- ------------------------------------------------------
20-- DDL for Sequence DEVICE_SEQ
21-- ------------------------------------------------------
22
23 CALL CreateSequence('`DEVICE_SEQ`', 1, 1) NOKEEP NOSCALE GLOBAL ;
24-- ------------------------------------------------------
25-- DDL for Sequence DEVICE_SEQ1
26-- ------------------------------------------------------
27
28 CALL CreateSequence('`DEVICE_SEQ1`', 1, 1) NOKEEP NOSCALE GLOBAL ;
29-- ------------------------------------------------------
30-- DDL for Sequence DEVICE_SEQ2
31-- ------------------------------------------------------
32
33 CALL CreateSequence('`DEVICE_SEQ2`', 21, 1) NOKEEP NOSCALE GLOBAL ;
34-- ------------------------------------------------------
35-- DDL for Sequence DEVICETYPE_SEQ
36-- ------------------------------------------------------
37
38 CALL CreateSequence('`DEVICETYPE_SEQ`', 1, 1) NOKEEP NOSCALE GLOBAL ;
39-- ------------------------------------------------------
40-- DDL for Sequence DEVICETYPE_SEQ1
41-- ------------------------------------------------------
42
43 CALL CreateSequence('`DEVICETYPE_SEQ1`', 21, 1) NOKEEP NOSCALE GLOBAL ;
44-- ------------------------------------------------------
45-- DDL for Sequence PERSON_DEPARTMENT_SEQ
46-- ------------------------------------------------------
47
48 CALL CreateSequence('`PERSON_DEPARTMENT_SEQ`', 21, 1) NOKEEP NOSCALE GLOBAL ;
49-- ------------------------------------------------------
50-- DDL for Sequence REPAIR_SEQ
51-- ------------------------------------------------------
52
53 CALL CreateSequence('`REPAIR_SEQ`', 1, 1) NOKEEP NOSCALE GLOBAL ;
54-- ------------------------------------------------------
55-- DDL for Sequence REPAIR_SEQ1
56-- ------------------------------------------------------
57
58 CALL CreateSequence('`REPAIR_SEQ1`', 41, 1) NOKEEP NOSCALE GLOBAL ;
59-- ------------------------------------------------------
60-- DDL for Sequence TABLE1_SEQ
61-- ------------------------------------------------------
62
63 CALL CreateSequence('`TABLE1_SEQ`', 1, 1) NOKEEP NOSCALE GLOBAL ;
64-- ------------------------------------------------------
65-- DDL for Table DEPARTMENT
66-- ------------------------------------------------------
67
68 CREATE TABLE `DEPARTMENT`
69 ( `NAME` VARCHAR(20)
70 )
71 ;
72-- ------------------------------------------------------
73-- DDL for Table DEVICE
74-- ------------------------------------------------------
75
76 CREATE TABLE `DEVICE`
77 ( `ID` DECIMAL(38,0),
78 `BUYDATE` DATETIME,
79 `SERIALNUMBER` VARCHAR(30),
80 `MANUFACTURER` VARCHAR(64),
81 `TYPE` DECIMAL(38,0),
82 `ROOM` VARCHAR(20),
83 `DEPARTMENT` VARCHAR(20)
84 )
85 ;
86-- ------------------------------------------------------
87-- DDL for Table DEVICE_PERSON_ADMIN
88-- ------------------------------------------------------
89
90 CREATE TABLE `DEVICE_PERSON_ADMIN`
91 ( `ID` DECIMAL(38,0),
92 `DEVICE` DECIMAL(38,0),
93 `PERSON` VARCHAR(20)
94 )
95 ;
96-- ------------------------------------------------------
97-- DDL for Table DEVICE_PERSON_USED
98-- ------------------------------------------------------
99
100 CREATE TABLE `DEVICE_PERSON_USED`
101 ( `ID` DECIMAL(38,0),
102 `DEVICE` DECIMAL(38,0),
103 `PERSON` VARCHAR(20)
104 )
105 ;
106-- ------------------------------------------------------
107-- DDL for Table DEVICETYPE
108-- ------------------------------------------------------
109
110 CREATE TABLE `DEVICETYPE`
111 ( `ID` DECIMAL(38,0),
112 `NAME` VARCHAR(20)
113 )
114 ;
115-- ------------------------------------------------------
116-- DDL for Table MANUFACTURER
117-- ------------------------------------------------------
118
119 CREATE TABLE `MANUFACTURER`
120 ( `NAME` VARCHAR(64),
121 `ADDRESS` VARCHAR(128),
122 `CONTACT` VARCHAR(32)
123 )
124 ;
125-- ------------------------------------------------------
126-- DDL for Table PERSON
127-- ------------------------------------------------------
128
129 CREATE TABLE `PERSON`
130 ( `PID` VARCHAR(20),
131 `NAME` VARCHAR(20),
132 `SURNAME` VARCHAR(20),
133 `MAIL` VARCHAR(32),
134 `ADMIN` TINYINT,
135 `ROOM` VARCHAR(20)
136 )
137 ;
138-- ------------------------------------------------------
139-- DDL for Table PERSON_DEPARTMENT
140-- ------------------------------------------------------
141
142 CREATE TABLE `PERSON_DEPARTMENT`
143 ( `ID` VARCHAR(20),
144 `PERSON` VARCHAR(20),
145 `DEPARTMENT` VARCHAR(20)
146 )
147 ;
148-- ------------------------------------------------------
149-- DDL for Table REPAIR
150-- ------------------------------------------------------
151
152 CREATE TABLE `REPAIR`
153 ( `ID` VARCHAR(20),
154 `REPORTED` DATETIME,
155 `FINISHED` DATETIME,
156 `DEVICE` DECIMAL(38,0),
157 `ADMINISTRATED_BY` VARCHAR(20),
158 `REPORTED_BY` VARCHAR(20)
159 )
160 ;
161-- ------------------------------------------------------
162-- DDL for Table ROOM
163-- ------------------------------------------------------
164
165 CREATE TABLE `ROOM`
166 ( `NAME` VARCHAR(20)
167 )
168 ;
169-- INSERTING into DEPARTMENT
170/* SET DEFINE OFF; */
171Insert into DEPARTMENT (NAME) values ('CVT');
172Insert into DEPARTMENT (NAME) values ('Deanship');
173Insert into DEPARTMENT (NAME) values ('Library');
174-- INSERTING into DEVICE
175/* SET DEFINE OFF; */
176Insert into DEVICE (ID,BUYDATE,SERIALNUMBER,MANUFACTURER,TYPE,ROOM,DEPARTMENT) values ('0',str_to_date('01.01.18','%d.%m.%y'),'xx123','Dell','3','D105','Deanship');
177Insert into DEVICE (ID,BUYDATE,SERIALNUMBER,MANUFACTURER,TYPE,ROOM,DEPARTMENT) values ('1',str_to_date('01.01.18','%d.%m.%y'),'xx122','Dell','3','Library','Library');
178Insert into DEVICE (ID,BUYDATE,SERIALNUMBER,MANUFACTURER,TYPE,ROOM,DEPARTMENT) values ('2',str_to_date('01.01.18','%d.%m.%y'),'xx124','Dell','3','Library','Library');
179Insert into DEVICE (ID,BUYDATE,SERIALNUMBER,MANUFACTURER,TYPE,ROOM,DEPARTMENT) values ('3',str_to_date('01.01.18','%d.%m.%y'),'aa123','Dell','2','Library','Library');
180Insert into DEVICE (ID,BUYDATE,SERIALNUMBER,MANUFACTURER,TYPE,ROOM,DEPARTMENT) values ('4',str_to_date('01.01.18','%d.%m.%y'),'aa124','Dell','2','Library','Library');
181-- INSERTING into DEVICE_PERSON_ADMIN
182/* SET DEFINE OFF; */
183Insert into DEVICE_PERSON_ADMIN (ID,DEVICE,PERSON) values ('1','0','970201/1111');
184Insert into DEVICE_PERSON_ADMIN (ID,DEVICE,PERSON) values ('2','1','970201/1111');
185-- INSERTING into DEVICE_PERSON_USED
186/* SET DEFINE OFF; */
187Insert into DEVICE_PERSON_USED (ID,DEVICE,PERSON) values ('1','0','970101/1111');
188Insert into DEVICE_PERSON_USED (ID,DEVICE,PERSON) values ('2','1','970101/1111');
189-- INSERTING into DEVICETYPE
190/* SET DEFINE OFF; */
191Insert into DEVICETYPE (ID,NAME) values ('1','Notebook');
192Insert into DEVICETYPE (ID,NAME) values ('2','Monitor');
193Insert into DEVICETYPE (ID,NAME) values ('3','PC');
194-- INSERTING into MANUFACTURER
195/* SET DEFINE OFF; */
196Insert into MANUFACTURER (NAME,ADDRESS,CONTACT) values ('Hewlett Packard','Praha
197','123456789');
198Insert into MANUFACTURER (NAME,ADDRESS,CONTACT) values ('Dell','Brno','987654321');
199-- INSERTING into PERSON
200/* SET DEFINE OFF; */
201Insert into PERSON (PID,NAME,SURNAME,MAIL,ADMIN,ROOM) values ('970101/1111','Jan','Novak','jan.novak@vut.cz','0','A203');
202Insert into PERSON (PID,NAME,SURNAME,MAIL,ADMIN,ROOM) values ('970101/1211','Jan','Obrtanec','jan.obrtanec@vut.cz','1','D105');
203Insert into PERSON (PID,NAME,SURNAME,MAIL,ADMIN,ROOM) values ('970201/1111','Frantisek','Datab�z','fra.databaz@vut.cz','1','D105');
204Insert into PERSON (PID,NAME,SURNAME,MAIL,ADMIN,ROOM) values ('970301/1111','Petr','Tumpeta','pet.trumpeta@vut.cz','0','A203');
205-- INSERTING into PERSON_DEPARTMENT
206/* SET DEFINE OFF; */
207Insert into PERSON_DEPARTMENT (ID,PERSON,DEPARTMENT) values ('2','970101/1111','Library');
208Insert into PERSON_DEPARTMENT (ID,PERSON,DEPARTMENT) values ('3','970101/1211','Deanship');
209-- INSERTING into REPAIR
210/* SET DEFINE OFF; */
211Insert into REPAIR (ID,REPORTED,FINISHED,DEVICE,ADMINISTRATED_BY,REPORTED_BY) values ('0',str_to_date('01.02.18','%d.%m.%y'),str_to_date('02.02.18','%d.%m.%y'),'0','970101/1211','970101/1111');
212Insert into REPAIR (ID,REPORTED,FINISHED,DEVICE,ADMINISTRATED_BY,REPORTED_BY) values ('1',str_to_date('01.02.18','%d.%m.%y'),str_to_date('02.02.18','%d.%m.%y'),'1','970101/1211','970101/1111');
213Insert into REPAIR (ID,REPORTED,FINISHED,DEVICE,ADMINISTRATED_BY,REPORTED_BY) values ('21',str_to_date('01.05.18','%d.%m.%y'),null,'1',null,'970101/1111');
214-- INSERTING into ROOM
215/* SET DEFINE OFF; */
216Insert into ROOM (NAME) values ('A203');
217Insert into ROOM (NAME) values ('D105');
218Insert into ROOM (NAME) values ('Library');
219-- ------------------------------------------------------
220-- DDL for Index DEPARTMENT_PK
221-- ------------------------------------------------------
222
223 CREATE UNIQUE INDEX `DEPARTMENT_PK` ON `DEPARTMENT` (`NAME`)
224 ;
225-- ------------------------------------------------------
226-- DDL for Index DEVICE_PERSON_ADMIN_PK
227-- ------------------------------------------------------
228
229 CREATE UNIQUE INDEX `DEVICE_PERSON_ADMIN_PK` ON `DEVICE_PERSON_ADMIN` (`ID`)
230 ;
231-- ------------------------------------------------------
232-- DDL for Index DEVICE_PERSON_USED_PK
233-- ------------------------------------------------------
234
235 CREATE UNIQUE INDEX `DEVICE_PERSON_USED_PK` ON `DEVICE_PERSON_USED` (`ID`)
236 ;
237-- ------------------------------------------------------
238-- DDL for Index DEVICE_PK
239-- ------------------------------------------------------
240
241 CREATE UNIQUE INDEX `DEVICE_PK` ON `DEVICE` (`ID`)
242 ;
243-- ------------------------------------------------------
244-- DDL for Index DEVICETYPE_PK
245-- ------------------------------------------------------
246
247 CREATE UNIQUE INDEX `DEVICETYPE_PK` ON `DEVICETYPE` (`ID`)
248 ;
249-- ------------------------------------------------------
250-- DDL for Index MANUFACTURER_PK
251-- ------------------------------------------------------
252
253 CREATE UNIQUE INDEX `MANUFACTURER_PK` ON `MANUFACTURER` (`NAME`)
254 ;
255-- ------------------------------------------------------
256-- DDL for Index PERSON_DEPARTMENT_PK
257-- ------------------------------------------------------
258
259 CREATE UNIQUE INDEX `PERSON_DEPARTMENT_PK` ON `PERSON_DEPARTMENT` (`ID`)
260 ;
261-- ------------------------------------------------------
262-- DDL for Index PERSON_PK
263-- ------------------------------------------------------
264
265 CREATE UNIQUE INDEX `PERSON_PK` ON `PERSON` (`PID`)
266 ;
267-- ------------------------------------------------------
268-- DDL for Index REPAIR_PK
269-- ------------------------------------------------------
270
271 CREATE UNIQUE INDEX `REPAIR_PK` ON `REPAIR` (`ID`)
272 ;
273-- ------------------------------------------------------
274-- DDL for Index ROOM_PK
275-- ------------------------------------------------------
276
277 CREATE UNIQUE INDEX `ROOM_PK` ON `ROOM` (`NAME`)
278 ;
279-- ------------------------------------------------------
280-- DDL for Trigger DEVICE_PERSON_ADMIN_TRG
281-- ------------------------------------------------------
282
283 CREATE OR REPLACE TRIGGER `DEVICE_PERSON_ADMIN_TRG`
284BEFORE INSERT ON DEVICE_PERSON_ADMIN
285FOR EACH ROW
286BEGIN
287 <<COLUMN_SEQUENCES>>
288 BEGIN
289 IF INSERTING AND :NEW.ID IS NULL THEN
290 SELECT DEVICE_PERSON_ADMIN_SEQ1.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
291 END IF;
292 END; COLUMN_SEQUENCES;
293END;
294
295/
296ALTER TRIGGER `DEVICE_PERSON_ADMIN_TRG` ENABLE;
297-- ------------------------------------------------------
298-- DDL for Trigger DEVICE_PERSON_USED_TRG
299-- ------------------------------------------------------
300
301 CREATE OR REPLACE TRIGGER `DEVICE_PERSON_USED_TRG`
302BEFORE INSERT ON DEVICE_PERSON_USED
303FOR EACH ROW
304BEGIN
305 <<COLUMN_SEQUENCES>>
306 BEGIN
307 IF INSERTING AND :NEW.ID IS NULL THEN
308 SELECT DEVICE_PERSON_USED_SEQ.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
309 END IF;
310 END; COLUMN_SEQUENCES;
311END;
312
313/
314ALTER TRIGGER `DEVICE_PERSON_USED_TRG` ENABLE;
315-- ------------------------------------------------------
316-- DDL for Trigger DEVICE_TRG
317-- ------------------------------------------------------
318
319 CREATE OR REPLACE TRIGGER `DEVICE_TRG`
320BEFORE INSERT ON DEVICE
321FOR EACH ROW
322BEGIN
323 <<COLUMN_SEQUENCES>>
324 BEGIN
325 NULL;
326 END; COLUMN_SEQUENCES;
327END;
328
329/
330ALTER TRIGGER `DEVICE_TRG` ENABLE;
331-- ------------------------------------------------------
332-- DDL for Trigger DEVICE_TRG1
333-- ------------------------------------------------------
334
335 CREATE OR REPLACE TRIGGER `DEVICE_TRG1`
336BEFORE INSERT ON DEVICE
337FOR EACH ROW
338BEGIN
339 <<COLUMN_SEQUENCES>>
340 BEGIN
341 NULL;
342 END; COLUMN_SEQUENCES;
343END;
344
345/
346ALTER TRIGGER `DEVICE_TRG1` ENABLE;
347-- ------------------------------------------------------
348-- DDL for Trigger DEVICE_TRG2
349-- ------------------------------------------------------
350
351 CREATE OR REPLACE TRIGGER `DEVICE_TRG2`
352BEFORE INSERT ON DEVICE
353FOR EACH ROW
354BEGIN
355 <<COLUMN_SEQUENCES>>
356 BEGIN
357 IF INSERTING AND :NEW.ID IS NULL THEN
358 SELECT DEVICE_SEQ2.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
359 END IF;
360 END; COLUMN_SEQUENCES;
361END;
362
363/
364ALTER TRIGGER `DEVICE_TRG2` ENABLE;
365-- ------------------------------------------------------
366-- DDL for Trigger DEVICETYPE_TRG
367-- ------------------------------------------------------
368
369 CREATE OR REPLACE TRIGGER `DEVICETYPE_TRG`
370BEFORE INSERT ON DEVICETYPE
371FOR EACH ROW
372BEGIN
373 <<COLUMN_SEQUENCES>>
374 BEGIN
375 NULL;
376 END; COLUMN_SEQUENCES;
377END;
378
379/
380ALTER TRIGGER `DEVICETYPE_TRG` ENABLE;
381-- ------------------------------------------------------
382-- DDL for Trigger DEVICETYPE_TRG1
383-- ------------------------------------------------------
384
385 CREATE OR REPLACE TRIGGER `DEVICETYPE_TRG1`
386BEFORE INSERT ON DEVICETYPE
387FOR EACH ROW
388BEGIN
389 <<COLUMN_SEQUENCES>>
390 BEGIN
391 IF INSERTING AND :NEW.ID IS NULL THEN
392 SELECT DEVICETYPE_SEQ1.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
393 END IF;
394 END; COLUMN_SEQUENCES;
395END;
396
397/
398ALTER TRIGGER `DEVICETYPE_TRG1` ENABLE;
399-- ------------------------------------------------------
400-- DDL for Trigger INSERT_REPORTING_ADMINISTRATING_PERSON
401-- ------------------------------------------------------
402
403 CREATE OR REPLACE TRIGGER `INSERT_REPORTING_ADMINISTRATING_PERSON`
404BEFORE INSERT OR UPDATE ON "REPAIR"
405FOR EACH ROW
406DECLARE NA_VLOZENIE VARCHAR(20);
407BEGIN
408 IF (:NEW.REPORTED_BY IS NULL) AND (GET_DEVICE_USER_NAME(:NEW.DEVICE) IS NOT NULL) THEN
409 SET :NEW.REPORTED_BY = GET_DEVICE_USER_NAME(:NEW.DEVICE);
410 END IF;
411 IF (:NEW.ADMINISTRATED_BY IS NULL) AND (GET_DEVICE_ADMIN_NAME(:NEW.DEVICE) IS NOT NULL) THEN
412 SET :NEW.ADMINISTRATED_BY = GET_DEVICE_ADMIN_NAME(:NEW.DEVICE);
413 END IF;
414END INSERT_PERSON;
415
416/
417ALTER TRIGGER `INSERT_REPORTING_ADMINISTRATING_PERSON` ENABLE;
418-- ------------------------------------------------------
419-- DDL for Trigger INSERT_REPORTING_PERSON
420-- ------------------------------------------------------
421
422 CREATE OR REPLACE TRIGGER `INSERT_REPORTING_PERSON`
423BEFORE INSERT OR UPDATE ON "REPAIR"
424FOR EACH ROW
425DECLARE NA_VLOZENIE VARCHAR(20);
426BEGIN
427 IF (:NEW.REPORTED_BY IS NULL) AND (GET_DEVICE_USER_NAME(:NEW.DEVICE) IS NOT NULL) THEN
428 SET :NEW.REPORTED_BY = GET_DEVICE_USER_NAME(:NEW.DEVICE);
429 END IF;
430END INSERT_PERSON;
431
432/
433ALTER TRIGGER `INSERT_REPORTING_PERSON` ENABLE;
434-- ------------------------------------------------------
435-- DDL for Trigger PERSON_DEPARTMENT_TRG
436-- ------------------------------------------------------
437
438 CREATE OR REPLACE TRIGGER `PERSON_DEPARTMENT_TRG`
439BEFORE INSERT ON PERSON_DEPARTMENT
440FOR EACH ROW
441BEGIN
442 <<COLUMN_SEQUENCES>>
443 BEGIN
444 IF INSERTING AND :NEW.ID IS NULL THEN
445 SELECT PERSON_DEPARTMENT_SEQ.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
446 END IF;
447 END; COLUMN_SEQUENCES;
448END;
449
450/
451ALTER TRIGGER `PERSON_DEPARTMENT_TRG` ENABLE;
452-- ------------------------------------------------------
453-- DDL for Trigger REPAIR_TRG
454-- ------------------------------------------------------
455
456 CREATE OR REPLACE TRIGGER `REPAIR_TRG`
457BEFORE INSERT ON REPAIR
458FOR EACH ROW
459BEGIN
460 <<COLUMN_SEQUENCES>>
461 BEGIN
462 NULL;
463 END; COLUMN_SEQUENCES;
464END;
465
466/
467ALTER TRIGGER `REPAIR_TRG` ENABLE;
468-- ------------------------------------------------------
469-- DDL for Trigger REPAIR_TRG1
470-- ------------------------------------------------------
471
472 CREATE OR REPLACE TRIGGER `REPAIR_TRG1`
473BEFORE INSERT ON REPAIR
474FOR EACH ROW
475BEGIN
476 <<COLUMN_SEQUENCES>>
477 BEGIN
478 IF INSERTING AND :NEW.ID IS NULL THEN
479 SELECT REPAIR_SEQ1.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
480 END IF;
481 END; COLUMN_SEQUENCES;
482END;
483
484/
485ALTER TRIGGER `REPAIR_TRG1` ENABLE;
486-- ------------------------------------------------------
487-- DDL for Procedure ADMIN_LEAVING
488-- ------------------------------------------------------
489/* set define off; */
490
491
492
493UPDATE DEVICE_PERSON_ADMIN
494SET PERSON = replacing
495WHERE PERSON = leaving;
496CASE WHEN sql%notfound THEN
497 put_line('?�dn� za?�zen� nezm?nilo spr�vce!');
498ELSIF sql%found THEN
499put_line(concat(ifnull(sql%rowcount, ''),' za?�zen� zm?nila spr�vce!'));
500 END;
501//
502
503DELIMITER ;
504
505 IF;
506END ADMIN_LEAVING;
507
508/
509-- ------------------------------------------------------
510-- DDL for Function GET_DEVICE_ADMIN_NAME
511-- ------------------------------------------------------
512
513 DROP FUNCTION IF EXISTS `GET_DEVICE_ADMIN_NAME`;
514
515 DELIMITER //
516
517 CREATE FUNCTION `GET_DEVICE_ADMIN_NAME` ( DEV DOUBLE )
518RETURNS VARCHAR(4000)
519BEGIN DECLARE MENO VARCHAR(20);
520
521 SELECT DISTINCT PERSON
522 INTO MENO
523 FROM DEVICE_PERSON_ADMIN DPA
524 WHERE DPA.DEVICE = DEV;
525 RETURN (MENO);
526END;
527//
528
529DELIMITER ;
530
531 GET_DEVICE_ADMIN_NAME;
532
533/
534-- ------------------------------------------------------
535-- DDL for Function GET_DEVICE_USER_NAME
536-- ------------------------------------------------------
537
538 DROP FUNCTION IF EXISTS `GET_DEVICE_USER_NAME`;
539
540 DELIMITER //
541
542 CREATE FUNCTION `GET_DEVICE_USER_NAME` ( DEV DOUBLE )
543RETURNS VARCHAR(4000)
544BEGIN DECLARE MENO VARCHAR(20);
545
546 SELECT DISTINCT PERSON
547 INTO MENO
548 FROM DEVICE_PERSON_USED DPU
549 WHERE DPU.DEVICE = DEV;
550 RETURN (MENO);
551END;
552//
553
554DELIMITER ;
555
556 GET_DEVICE_USER_NAME;
557
558/
559
560-- Unable to render SYNONYM DDL for object PUBLIC.DBMS_OUTPUT with DBMS_METADATA attempting internal generator.
561CREATE PUBLIC SYNONYM DBMS_OUTPUT FOR SYS.DBMS_OUTPUT
562-- Unable to render SYNONYM DDL for object PUBLIC.DUAL with DBMS_METADATA attempting internal generator.
563CREATE PUBLIC SYNONYM DUAL FOR SYS."DUAL"
564-- ------------------------------------------------------
565-- Constraints for Table DEPARTMENT
566-- ------------------------------------------------------
567
568 ALTER TABLE `DEPARTMENT` MODIFY (`NAME` NOT NULL ENABLE);
569 ALTER TABLE `DEPARTMENT` ADD CONSTRAINT `DEPARTMENT_PK` PRIMARY KEY (`NAME`)
570 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
571 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
572 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
573 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
574 TABLESPACE `USERS` ENABLE;
575-- ------------------------------------------------------
576-- Constraints for Table DEVICE
577-- ------------------------------------------------------
578
579 ALTER TABLE `DEVICE` ADD CONSTRAINT `DEVICE_PK` PRIMARY KEY (`ID`)
580 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
581 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
582 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
583 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
584 TABLESPACE `USERS` ENABLE;
585 ALTER TABLE `DEVICE` MODIFY (`DEPARTMENT` NOT NULL ENABLE);
586 ALTER TABLE `DEVICE` MODIFY (`ID` NOT NULL ENABLE);
587-- ------------------------------------------------------
588-- Constraints for Table DEVICE_PERSON_ADMIN
589-- ------------------------------------------------------
590
591 ALTER TABLE `DEVICE_PERSON_ADMIN` ADD CONSTRAINT `DEVICE_PERSON_ADMIN_PK` PRIMARY KEY (`ID`)
592 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
593 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
594 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
595 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
596 TABLESPACE `USERS` ENABLE;
597 ALTER TABLE `DEVICE_PERSON_ADMIN` MODIFY (`ID` NOT NULL ENABLE);
598 ALTER TABLE `DEVICE_PERSON_ADMIN` MODIFY (`DEVICE` NOT NULL ENABLE);
599 ALTER TABLE `DEVICE_PERSON_ADMIN` MODIFY (`PERSON` NOT NULL ENABLE);
600-- ------------------------------------------------------
601-- Constraints for Table DEVICE_PERSON_USED
602-- ------------------------------------------------------
603
604 ALTER TABLE `DEVICE_PERSON_USED` ADD CONSTRAINT `DEVICE_PERSON_USED_PK` PRIMARY KEY (`ID`)
605 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
606 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
607 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
608 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
609 TABLESPACE `USERS` ENABLE;
610 ALTER TABLE `DEVICE_PERSON_USED` MODIFY (`ID` NOT NULL ENABLE);
611 ALTER TABLE `DEVICE_PERSON_USED` MODIFY (`DEVICE` NOT NULL ENABLE);
612 ALTER TABLE `DEVICE_PERSON_USED` MODIFY (`PERSON` NOT NULL ENABLE);
613-- ------------------------------------------------------
614-- Constraints for Table DEVICETYPE
615-- ------------------------------------------------------
616
617 ALTER TABLE `DEVICETYPE` ADD CONSTRAINT `DEVICETYPE_PK` PRIMARY KEY (`ID`)
618 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
619 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
620 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
621 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
622 TABLESPACE `USERS` ENABLE;
623 ALTER TABLE `DEVICETYPE` MODIFY (`ID` NOT NULL ENABLE);
624 ALTER TABLE `DEVICETYPE` MODIFY (`NAME` NOT NULL ENABLE);
625-- ------------------------------------------------------
626-- Constraints for Table MANUFACTURER
627-- ------------------------------------------------------
628
629 ALTER TABLE `MANUFACTURER` ADD CONSTRAINT `MANUFACTURER_PK` PRIMARY KEY (`NAME`)
630 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
631 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
632 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
633 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
634 TABLESPACE `USERS` ENABLE;
635 ALTER TABLE `MANUFACTURER` MODIFY (`NAME` NOT NULL ENABLE);
636-- ------------------------------------------------------
637-- Constraints for Table PERSON
638-- ------------------------------------------------------
639
640 ALTER TABLE `PERSON` ADD CONSTRAINT `PERSON_MAIL_CONST` CHECK (REGEXP_LIKE(MAIL,'^[0-9a-zA-Z.]+@[0-9a-zA-Z.]+.[0-9a-zA-Z]+$'));
641 ALTER TABLE `PERSON` ADD CONSTRAINT `PERSON_PID_CONST` CHECK (REGEXP_LIKE(PID,'^[0-9]{6}?/[0-9]{4}?$'));
642 ALTER TABLE `PERSON` ADD CONSTRAINT `PERSON_PK` PRIMARY KEY (`PID`)
643 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
644 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
646 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
647 TABLESPACE `USERS` ENABLE;
648 ALTER TABLE `PERSON` MODIFY (`PID` NOT NULL ENABLE);
649-- ------------------------------------------------------
650-- Constraints for Table PERSON_DEPARTMENT
651-- ------------------------------------------------------
652
653 ALTER TABLE `PERSON_DEPARTMENT` ADD CONSTRAINT `PERSON_DEPARTMENT_PK` PRIMARY KEY (`ID`)
654 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
655 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
656 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
657 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
658 TABLESPACE `USERS` ENABLE;
659 ALTER TABLE `PERSON_DEPARTMENT` MODIFY (`ID` NOT NULL ENABLE);
660 ALTER TABLE `PERSON_DEPARTMENT` MODIFY (`PERSON` NOT NULL ENABLE);
661 ALTER TABLE `PERSON_DEPARTMENT` MODIFY (`DEPARTMENT` NOT NULL ENABLE);
662-- ------------------------------------------------------
663-- Constraints for Table REPAIR
664-- ------------------------------------------------------
665
666 ALTER TABLE `REPAIR` ADD CONSTRAINT `REPAIR_PK` PRIMARY KEY (`ID`)
667 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
668 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
669 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
670 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
671 TABLESPACE `USERS` ENABLE;
672 ALTER TABLE `REPAIR` MODIFY (`ID` NOT NULL ENABLE);
673 ALTER TABLE `REPAIR` MODIFY (`DEVICE` NOT NULL ENABLE);
674 ALTER TABLE `REPAIR` MODIFY (`REPORTED` NOT NULL ENABLE);
675 ALTER TABLE `REPAIR` MODIFY (`REPORTED_BY` NOT NULL ENABLE);
676-- ------------------------------------------------------
677-- Constraints for Table ROOM
678-- ------------------------------------------------------
679
680 ALTER TABLE `ROOM` ADD CONSTRAINT `ROOM_PK` PRIMARY KEY (`NAME`)
681 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
682 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
683 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
684 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
685 TABLESPACE `USERS` ENABLE;
686 ALTER TABLE `ROOM` MODIFY (`NAME` NOT NULL ENABLE);
687-- ------------------------------------------------------
688-- Ref Constraints for Table DEVICE
689-- ------------------------------------------------------
690
691 ALTER TABLE `DEVICE` ADD CONSTRAINT `DEVICE_DEPARTMENT_FK` FOREIGN KEY (`DEPARTMENT`)
692 REFERENCES `DEPARTMENT` (`NAME`);
693 ALTER TABLE `DEVICE` ADD CONSTRAINT `DEVICE_DEVICETYPE_FK` FOREIGN KEY (`TYPE`)
694 REFERENCES `DEVICETYPE` (`ID`);
695 ALTER TABLE `DEVICE` ADD CONSTRAINT `DEVICE_MANUFACTURER_FK` FOREIGN KEY (`MANUFACTURER`)
696 REFERENCES `MANUFACTURER` (`NAME`);
697 ALTER TABLE `DEVICE` ADD CONSTRAINT `DEVICE_ROOM` FOREIGN KEY (`ROOM`)
698 REFERENCES `ROOM` (`NAME`);
699-- ------------------------------------------------------
700-- Ref Constraints for Table DEVICE_PERSON_ADMIN
701-- ------------------------------------------------------
702
703 ALTER TABLE `DEVICE_PERSON_ADMIN` ADD CONSTRAINT `DEVICE_PERSON_ADMIN_DEVICE_FK` FOREIGN KEY (`DEVICE`)
704 REFERENCES `DEVICE` (`ID`);
705 ALTER TABLE `DEVICE_PERSON_ADMIN` ADD CONSTRAINT `DEVICE_PERSON_ADMIN_PERSON_FK` FOREIGN KEY (`PERSON`)
706 REFERENCES `PERSON` (`PID`);
707-- ------------------------------------------------------
708-- Ref Constraints for Table DEVICE_PERSON_USED
709-- ------------------------------------------------------
710
711 ALTER TABLE `DEVICE_PERSON_USED` ADD CONSTRAINT `DEVICE_PERSON_USED_DEVICE_FK` FOREIGN KEY (`DEVICE`)
712 REFERENCES `DEVICE` (`ID`);
713 ALTER TABLE `DEVICE_PERSON_USED` ADD CONSTRAINT `DEVICE_PERSON_USED_PERSON_FK` FOREIGN KEY (`PERSON`)
714 REFERENCES `PERSON` (`PID`);
715-- ------------------------------------------------------
716-- Ref Constraints for Table PERSON
717-- ------------------------------------------------------
718
719 ALTER TABLE `PERSON` ADD CONSTRAINT `PERSON_ROOM_FK` FOREIGN KEY (`ROOM`)
720 REFERENCES `ROOM` (`NAME`);
721-- ------------------------------------------------------
722-- Ref Constraints for Table PERSON_DEPARTMENT
723-- ------------------------------------------------------
724
725 ALTER TABLE `PERSON_DEPARTMENT` ADD CONSTRAINT `PERSON_DEPARTMENT_DEPARTMENT_FK` FOREIGN KEY (`DEPARTMENT`)
726 REFERENCES `DEPARTMENT` (`NAME`);
727 ALTER TABLE `PERSON_DEPARTMENT` ADD CONSTRAINT `PERSON_DEPARTMENT_PERSON_FK` FOREIGN KEY (`PERSON`)
728 REFERENCES `PERSON` (`PID`);
729-- ------------------------------------------------------
730-- Ref Constraints for Table REPAIR
731-- ------------------------------------------------------
732
733 ALTER TABLE `REPAIR` ADD CONSTRAINT `REPAIR_DEVICE_FK` FOREIGN KEY (`DEVICE`)
734 REFERENCES `DEVICE` (`ID`);
735 ALTER TABLE `REPAIR` ADD CONSTRAINT `REPAIR_PERSON_FK` FOREIGN KEY (`ADMINISTRATED_BY`)
736 REFERENCES `PERSON` (`PID`);
737 ALTER TABLE `REPAIR` ADD CONSTRAINT `REPAIR_PERSON_REPORT_FK` FOREIGN KEY (`REPORTED_BY`)
738 REFERENCES `PERSON` (`PID`);
739
740
741
742DROP FUNCTION IF EXISTS GET_DEVICE_USER_NAME;
743
744DELIMITER //
745
746CREATE FUNCTION GET_DEVICE_USER_NAME( DEV DOUBLE )
747RETURNS VARCHAR(4000)
748BEGIN DECLARE MENO VARCHAR(20);
749
750 SELECT DISTINCT PERSON
751 INTO MENO
752 FROM DEVICE_PERSON_USED DPU
753 WHERE DPU.DEVICE = DEV;
754 RETURN (MENO);
755END;
756//
757
758DELIMITER ;
759
760
761
762DROP FUNCTION IF EXISTS GET_DEVICE_ADMIN_NAME;
763
764DELIMITER //
765
766CREATE FUNCTION GET_DEVICE_ADMIN_NAME( DEV DOUBLE )
767RETURNS VARCHAR(4000)
768BEGIN DECLARE MENO VARCHAR(20);
769
770 SELECT DISTINCT PERSON
771 INTO MENO
772 FROM DEVICE_PERSON_ADMIN DPA
773 WHERE DPA.DEVICE = DEV;
774 RETURN (MENO);
775END;
776//
777
778DELIMITER ;
779
780
781
782CREATE OR REPLACE TRIGGER INSERT_REPORTING_ADMINISTRATING_PERSON
783BEFORE INSERT OR UPDATE ON "REPAIR"
784FOR EACH ROW
785DECLARE NA_VLOZENIE VARCHAR(20);
786BEGIN
787 IF (:NEW.REPORTED_BY IS NULL) AND (GET_DEVICE_USER_NAME(:NEW.DEVICE) IS NOT NULL) THEN
788 SET :NEW.REPORTED_BY = GET_DEVICE_USER_NAME(:NEW.DEVICE);
789 END IF;
790 IF (:NEW.ADMINISTRATED_BY IS NULL) AND (GET_DEVICE_ADMIN_NAME(:NEW.DEVICE) IS NOT NULL) THEN
791 SET :NEW.ADMINISTRATED_BY = GET_DEVICE_ADMIN_NAME(:NEW.DEVICE);
792 END IF;
793END INSERT_PERSON;
794/
795
796set serveroutput on format wrapped;
797Drop procedure if exists ADMIN_LEAVING;
798
799Delimiter //
800
801Create procedure ADMIN_LEAVING(leaving VARCHAR(4000) /* Use -meta option PERSON.PID%TYPE */,replacing VARCHAR(4000) /* Use -meta option PERSON.PID%TYPE */)sp_lbl:
802
803 BEGIN
804DECLARE leave_nexists tinyint;
805DECLARE replace_nexists tinyint;
806
807select case when not exists (select * from PERSON where PID = LEAVING) then 1 else 0 end into leave_nexists from dual;
808select case when not exists (select * from PERSON where PID = REPLACING) then 1 else 0 end into replace_nexists from dual;
809IF (leave_nexists = 1) or (replace_nexists = 1) or (leaving = replacing) THEN
810put_line('�patn� zadan� parametry!');
811leave sp_lbl;
812END IF;