· 8 years ago · Jan 07, 2018, 08:12 PM
1--------------------------------------------------------
2-- File created - niedziela-stycznia-07-2018
3--------------------------------------------------------
4--------------------------------------------------------
5-- DDL for Sequence KLIENCI_ID_KLIENTA_SEQ
6--------------------------------------------------------
7
8 CREATE SEQUENCE "SERWIS"."KLIENCI_ID_KLIENTA_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
9--------------------------------------------------------
10-- DDL for Sequence KOMPUTERY_ID_KOMPUTERA_SEQ
11--------------------------------------------------------
12
13 CREATE SEQUENCE "SERWIS"."KOMPUTERY_ID_KOMPUTERA_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
14--------------------------------------------------------
15-- DDL for Sequence PLATNOSCI_ID_FAKTURY_SEQ
16--------------------------------------------------------
17
18 CREATE SEQUENCE "SERWIS"."PLATNOSCI_ID_FAKTURY_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
19--------------------------------------------------------
20-- DDL for Sequence PRACE_NAPRAWCZE_ID_PRACY_SEQ
21--------------------------------------------------------
22
23 CREATE SEQUENCE "SERWIS"."PRACE_NAPRAWCZE_ID_PRACY_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 81 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
24--------------------------------------------------------
25-- DDL for Sequence PRACOWNICY_ID_PRACOWNIKA_SEQ
26--------------------------------------------------------
27
28 CREATE SEQUENCE "SERWIS"."PRACOWNICY_ID_PRACOWNIKA_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
29--------------------------------------------------------
30-- DDL for Sequence ZAMOWIENIE_NAPRAWY_ID_NAPRAWY_
31--------------------------------------------------------
32
33 CREATE SEQUENCE "SERWIS"."ZAMOWIENIE_NAPRAWY_ID_NAPRAWY_" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
34--------------------------------------------------------
35-- DDL for Table CENNIK
36--------------------------------------------------------
37
38 CREATE TABLE "SERWIS"."CENNIK"
39 ( "ID_USLUGI" NUMBER(10,0),
40 "NAZWA_USLUGI" VARCHAR2(255 CHAR),
41 "CENA" FLOAT(126)
42 ) SEGMENT CREATION IMMEDIATE
43 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
44 NOCOMPRESS LOGGING
45 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
46 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
47 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
48 TABLESPACE "USERS" ;
49--------------------------------------------------------
50-- DDL for Table CZESCI_ZAMIENNE
51--------------------------------------------------------
52
53 CREATE TABLE "SERWIS"."CZESCI_ZAMIENNE"
54 ( "ID_CZESCI" NUMBER(10,0),
55 "ID_KATEGORII" NUMBER(10,0),
56 "ID_PRODUCENTA" NUMBER(10,0),
57 "MODEL" VARCHAR2(255 CHAR),
58 "CENA" FLOAT(126)
59 ) SEGMENT CREATION IMMEDIATE
60 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
61 NOCOMPRESS LOGGING
62 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
63 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
64 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
65 TABLESPACE "USERS" ;
66--------------------------------------------------------
67-- DDL for Table KATEGORIE
68--------------------------------------------------------
69
70 CREATE TABLE "SERWIS"."KATEGORIE"
71 ( "ID_KATEGORII" NUMBER(10,0),
72 "NAZWA_KATEGORII" VARCHAR2(255 CHAR)
73 ) SEGMENT CREATION IMMEDIATE
74 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
75 NOCOMPRESS LOGGING
76 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
77 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
78 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
79 TABLESPACE "USERS" ;
80--------------------------------------------------------
81-- DDL for Table KLIENCI
82--------------------------------------------------------
83
84 CREATE TABLE "SERWIS"."KLIENCI"
85 ( "ID_KLIENTA" NUMBER(10,0),
86 "IMIE" VARCHAR2(255 CHAR),
87 "NAZWISKO" VARCHAR2(255 CHAR),
88 "ADRES" VARCHAR2(255 CHAR),
89 "KOD_POCZTOWY" VARCHAR2(255 CHAR),
90 "MIASTO" VARCHAR2(255 CHAR),
91 "WOJEWODZTWO" VARCHAR2(255 CHAR),
92 "HASLO" VARCHAR2(255 CHAR),
93 "LOGIN" VARCHAR2(255 BYTE),
94 "EMAIL" VARCHAR2(255 BYTE)
95 ) SEGMENT CREATION IMMEDIATE
96 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
97 NOCOMPRESS LOGGING
98 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
99 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
100 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
101 TABLESPACE "USERS" ;
102--------------------------------------------------------
103-- DDL for Table KOMPUTERY
104--------------------------------------------------------
105
106 CREATE TABLE "SERWIS"."KOMPUTERY"
107 ( "ID_KOMPUTERA" NUMBER(10,0),
108 "PRODUCENT" VARCHAR2(255 CHAR),
109 "ROK_PRODUKCJI" NUMBER(10,0),
110 "NR_SERYJNY" VARCHAR2(30 BYTE)
111 ) SEGMENT CREATION IMMEDIATE
112 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
113 NOCOMPRESS LOGGING
114 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
115 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
116 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
117 TABLESPACE "USERS" ;
118--------------------------------------------------------
119-- DDL for Table MAGAZYN
120--------------------------------------------------------
121
122 CREATE TABLE "SERWIS"."MAGAZYN"
123 ( "ID_CZESCI" NUMBER(10,0),
124 "LICZBA_SZTUK" NUMBER(10,0)
125 ) SEGMENT CREATION IMMEDIATE
126 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
127 NOCOMPRESS LOGGING
128 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
129 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
130 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
131 TABLESPACE "USERS" ;
132--------------------------------------------------------
133-- DDL for Table PLATNOSCI
134--------------------------------------------------------
135
136 CREATE TABLE "SERWIS"."PLATNOSCI"
137 ( "ID_FAKTURY" NUMBER(10,0),
138 "ID_NAPRAWY" NUMBER(10,0),
139 "DO_ZAPLATY" FLOAT(126),
140 "ZAPLACONO" FLOAT(126)
141 ) SEGMENT CREATION IMMEDIATE
142 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
143 NOCOMPRESS LOGGING
144 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
145 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
146 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
147 TABLESPACE "USERS" ;
148--------------------------------------------------------
149-- DDL for Table PRACE_NAPRAWCZE
150--------------------------------------------------------
151
152 CREATE TABLE "SERWIS"."PRACE_NAPRAWCZE"
153 ( "ID_PRACY" NUMBER(10,0),
154 "ID_USLUGI" NUMBER(10,0),
155 "ID_NAPRAWY" NUMBER(10,0),
156 "ID_PRACOWNIKA" NUMBER(10,0),
157 "CENA_PRACY" FLOAT(63)
158 ) SEGMENT CREATION IMMEDIATE
159 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
160 NOCOMPRESS LOGGING
161 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
162 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
163 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
164 TABLESPACE "USERS" ;
165--------------------------------------------------------
166-- DDL for Table PRACE_NAPRAWCZE_CZESCI
167--------------------------------------------------------
168
169 CREATE TABLE "SERWIS"."PRACE_NAPRAWCZE_CZESCI"
170 ( "ID_PRACY" NUMBER(10,0),
171 "ID_CZESCI" NUMBER(10,0),
172 "CENA_ZAKUPU" FLOAT(63)
173 ) SEGMENT CREATION IMMEDIATE
174 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
175 NOCOMPRESS LOGGING
176 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
177 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
178 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
179 TABLESPACE "USERS" ;
180--------------------------------------------------------
181-- DDL for Table PRACOWNICY
182--------------------------------------------------------
183
184 CREATE TABLE "SERWIS"."PRACOWNICY"
185 ( "ID_PRACOWNIKA" NUMBER(10,0),
186 "IMIE" VARCHAR2(255 CHAR),
187 "NAZWISKO" VARCHAR2(255 CHAR),
188 "ADRES" VARCHAR2(255 CHAR),
189 "KOD_POCZTOWY" VARCHAR2(255 CHAR),
190 "MIASTO" VARCHAR2(255 CHAR),
191 "WOJEWODZTWO" VARCHAR2(255 CHAR),
192 "NR_TEL" NUMBER(10,0),
193 "NR_UMOWY" NUMBER(10,0),
194 "HASLO" VARCHAR2(255 CHAR),
195 "UPRAWNIENIA_ADMIN" NUMBER(3,0),
196 "LOGIN" VARCHAR2(255 BYTE)
197 ) SEGMENT CREATION IMMEDIATE
198 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
199 NOCOMPRESS LOGGING
200 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
201 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
202 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
203 TABLESPACE "USERS" ;
204--------------------------------------------------------
205-- DDL for Table PRODUCENCI
206--------------------------------------------------------
207
208 CREATE TABLE "SERWIS"."PRODUCENCI"
209 ( "ID_PRODUCENTA" NUMBER(10,0),
210 "NAZWA_PRODUCENTA" VARCHAR2(255 CHAR)
211 ) SEGMENT CREATION IMMEDIATE
212 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
213 NOCOMPRESS LOGGING
214 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
215 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
216 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
217 TABLESPACE "USERS" ;
218--------------------------------------------------------
219-- DDL for Table UMOWY
220--------------------------------------------------------
221
222 CREATE TABLE "SERWIS"."UMOWY"
223 ( "NR_UMOWY" NUMBER(10,0),
224 "DATA_ROZPOCZECIA" DATE,
225 "DATA_ZAKONCZENIA" DATE,
226 "WYPLATA" FLOAT(126),
227 "ID_PRACOWNIKA" NUMBER(10,0)
228 ) SEGMENT CREATION IMMEDIATE
229 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
230 NOCOMPRESS LOGGING
231 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
232 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
233 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
234 TABLESPACE "USERS" ;
235--------------------------------------------------------
236-- DDL for Table ZAMOWIENIE_NAPRAWY
237--------------------------------------------------------
238
239 CREATE TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY"
240 ( "ID_NAPRAWY" NUMBER(10,0),
241 "ID_KLIENTA" NUMBER(10,0),
242 "ID_KOMPUTERA" NUMBER(10,0),
243 "OPIS_USTERKI" VARCHAR2(4000 CHAR),
244 "DATA_ROZPOCZECIA" DATE,
245 "DATA_ZAKONCZENIA" DATE,
246 "STATUS" VARCHAR2(255 BYTE)
247 ) SEGMENT CREATION IMMEDIATE
248 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
249 NOCOMPRESS LOGGING
250 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
251 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
252 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
253 TABLESPACE "USERS" ;
254REM INSERTING into SERWIS.CENNIK
255SET DEFINE OFF;
256Insert into SERWIS.CENNIK (ID_USLUGI,NAZWA_USLUGI,CENA) values ('101','usluga testowa','2');
257Insert into SERWIS.CENNIK (ID_USLUGI,NAZWA_USLUGI,CENA) values ('1','Przyjecie do serwisu','15');
258Insert into SERWIS.CENNIK (ID_USLUGI,NAZWA_USLUGI,CENA) values ('100','wymiana czesci','10');
259Insert into SERWIS.CENNIK (ID_USLUGI,NAZWA_USLUGI,CENA) values ('11','Czyszczenie komputera','15');
260Insert into SERWIS.CENNIK (ID_USLUGI,NAZWA_USLUGI,CENA) values ('12','Wlacz/ Wyl¹cz komputer','5');
261Insert into SERWIS.CENNIK (ID_USLUGI,NAZWA_USLUGI,CENA) values ('2','HARD RESET DYSKU','600');
262REM INSERTING into SERWIS.CZESCI_ZAMIENNE
263SET DEFINE OFF;
264Insert into SERWIS.CZESCI_ZAMIENNE (ID_CZESCI,ID_KATEGORII,ID_PRODUCENTA,MODEL,CENA) values ('3','5','10','4400','50');
265Insert into SERWIS.CZESCI_ZAMIENNE (ID_CZESCI,ID_KATEGORII,ID_PRODUCENTA,MODEL,CENA) values ('1','1','1','model','100');
266Insert into SERWIS.CZESCI_ZAMIENNE (ID_CZESCI,ID_KATEGORII,ID_PRODUCENTA,MODEL,CENA) values ('2','1','1','model2','200');
267Insert into SERWIS.CZESCI_ZAMIENNE (ID_CZESCI,ID_KATEGORII,ID_PRODUCENTA,MODEL,CENA) values ('4','5','7','4600','50');
268Insert into SERWIS.CZESCI_ZAMIENNE (ID_CZESCI,ID_KATEGORII,ID_PRODUCENTA,MODEL,CENA) values ('5','1','9','HD7850','250');
269Insert into SERWIS.CZESCI_ZAMIENNE (ID_CZESCI,ID_KATEGORII,ID_PRODUCENTA,MODEL,CENA) values ('6','6','11','TF-3239DL','50');
270REM INSERTING into SERWIS.KATEGORIE
271SET DEFINE OFF;
272Insert into SERWIS.KATEGORIE (ID_KATEGORII,NAZWA_KATEGORII) values ('5','bateria');
273Insert into SERWIS.KATEGORIE (ID_KATEGORII,NAZWA_KATEGORII) values ('1','karta graficzna');
274Insert into SERWIS.KATEGORIE (ID_KATEGORII,NAZWA_KATEGORII) values ('2','plyta glowna');
275Insert into SERWIS.KATEGORIE (ID_KATEGORII,NAZWA_KATEGORII) values ('3','procesor');
276Insert into SERWIS.KATEGORIE (ID_KATEGORII,NAZWA_KATEGORII) values ('4','RAM');
277Insert into SERWIS.KATEGORIE (ID_KATEGORII,NAZWA_KATEGORII) values ('6','karta sieciowa');
278REM INSERTING into SERWIS.KLIENCI
279SET DEFINE OFF;
280Insert into SERWIS.KLIENCI (ID_KLIENTA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,HASLO,LOGIN,EMAIL) values ('41','mojeimie','mojenazwisko','mojadres','12347','krakow','ma³opolskie','$2y$10$ctQIrOVOkdt1R5FsJHVpY.Gx4qTtaz3s6EVZQTyCiLmBq7XYQz1f2','mojlogin','email@email.com');
281Insert into SERWIS.KLIENCI (ID_KLIENTA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,HASLO,LOGIN,EMAIL) values ('4','Pawel','W??dolny','?????????? 445','????????????????','szcz','dddddfsfs','$2y$10$GiCeEhCcZiKAHXyv1f45BOwkVZ1nfv6I4snoSRjS9.8io/thhQ7xi','kloss','mmm@gmail.com');
282Insert into SERWIS.KLIENCI (ID_KLIENTA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,HASLO,LOGIN,EMAIL) values ('3','klos','lans','asdres 23','34-100','malopolsk','Malopolskie','$2y$10$tBOp/FP27rSs2h3e2veeDurvXuEOTzIiLa6h90Ag5m7En5j3uQTv2','login2','email2@email.com');
283Insert into SERWIS.KLIENCI (ID_KLIENTA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,HASLO,LOGIN,EMAIL) values ('21','aaaaa','aaaaa','aaaaa','00000','aaaaa','aaaaa','$2y$10$5.2EYgzSlJyEvH7jtn9yxe0wmrZrbaFIZFCRTjlI43V7r7sM/6Osu','aaaaa','a@a.pl');
284REM INSERTING into SERWIS.KOMPUTERY
285SET DEFINE OFF;
286Insert into SERWIS.KOMPUTERY (ID_KOMPUTERA,PRODUCENT,ROK_PRODUKCJI,NR_SERYJNY) values ('30','Dell','2014','Inspirion15');
287REM INSERTING into SERWIS.MAGAZYN
288SET DEFINE OFF;
289Insert into SERWIS.MAGAZYN (ID_CZESCI,LICZBA_SZTUK) values ('1','42');
290Insert into SERWIS.MAGAZYN (ID_CZESCI,LICZBA_SZTUK) values ('2','11');
291Insert into SERWIS.MAGAZYN (ID_CZESCI,LICZBA_SZTUK) values ('3','0');
292Insert into SERWIS.MAGAZYN (ID_CZESCI,LICZBA_SZTUK) values ('4','10');
293Insert into SERWIS.MAGAZYN (ID_CZESCI,LICZBA_SZTUK) values ('5','10');
294Insert into SERWIS.MAGAZYN (ID_CZESCI,LICZBA_SZTUK) values ('6','10');
295REM INSERTING into SERWIS.PLATNOSCI
296SET DEFINE OFF;
297Insert into SERWIS.PLATNOSCI (ID_FAKTURY,ID_NAPRAWY,DO_ZAPLATY,ZAPLACONO) values ('1','50','76','0');
298Insert into SERWIS.PLATNOSCI (ID_FAKTURY,ID_NAPRAWY,DO_ZAPLATY,ZAPLACONO) values ('2','51','77','0');
299REM INSERTING into SERWIS.PRACE_NAPRAWCZE
300SET DEFINE OFF;
301Insert into SERWIS.PRACE_NAPRAWCZE (ID_PRACY,ID_USLUGI,ID_NAPRAWY,ID_PRACOWNIKA,CENA_PRACY) values ('63','100','50','2','10');
302Insert into SERWIS.PRACE_NAPRAWCZE (ID_PRACY,ID_USLUGI,ID_NAPRAWY,ID_PRACOWNIKA,CENA_PRACY) values ('64','1','51','2','15');
303Insert into SERWIS.PRACE_NAPRAWCZE (ID_PRACY,ID_USLUGI,ID_NAPRAWY,ID_PRACOWNIKA,CENA_PRACY) values ('65','101','51','2','2');
304Insert into SERWIS.PRACE_NAPRAWCZE (ID_PRACY,ID_USLUGI,ID_NAPRAWY,ID_PRACOWNIKA,CENA_PRACY) values ('66','100','51','2','10');
305Insert into SERWIS.PRACE_NAPRAWCZE (ID_PRACY,ID_USLUGI,ID_NAPRAWY,ID_PRACOWNIKA,CENA_PRACY) values ('61','1','50','2','15');
306Insert into SERWIS.PRACE_NAPRAWCZE (ID_PRACY,ID_USLUGI,ID_NAPRAWY,ID_PRACOWNIKA,CENA_PRACY) values ('62','101','50','2','1');
307REM INSERTING into SERWIS.PRACE_NAPRAWCZE_CZESCI
308SET DEFINE OFF;
309Insert into SERWIS.PRACE_NAPRAWCZE_CZESCI (ID_PRACY,ID_CZESCI,CENA_ZAKUPU) values ('63','3','50');
310Insert into SERWIS.PRACE_NAPRAWCZE_CZESCI (ID_PRACY,ID_CZESCI,CENA_ZAKUPU) values ('66','3','50');
311REM INSERTING into SERWIS.PRACOWNICY
312SET DEFINE OFF;
313Insert into SERWIS.PRACOWNICY (ID_PRACOWNIKA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,NR_TEL,NR_UMOWY,HASLO,UPRAWNIENIA_ADMIN,LOGIN) values ('1','admin','admin','admin','0000','admin','admin','0','1','admin','1','admin');
314Insert into SERWIS.PRACOWNICY (ID_PRACOWNIKA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,NR_TEL,NR_UMOWY,HASLO,UPRAWNIENIA_ADMIN,LOGIN) values ('2','user','user','uuser','00000','user','user','0','2','user','0','user');
315Insert into SERWIS.PRACOWNICY (ID_PRACOWNIKA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,NR_TEL,NR_UMOWY,HASLO,UPRAWNIENIA_ADMIN,LOGIN) values ('3','imie','nazwisko','adres','0','miasto','wojewodztwo','123','3','pass','0','login');
316Insert into SERWIS.PRACOWNICY (ID_PRACOWNIKA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,NR_TEL,NR_UMOWY,HASLO,UPRAWNIENIA_ADMIN,LOGIN) values ('4','aa','bb','aaabb','111','gg','hh','123456','4','aaaa','1','aaaa');
317Insert into SERWIS.PRACOWNICY (ID_PRACOWNIKA,IMIE,NAZWISKO,ADRES,KOD_POCZTOWY,MIASTO,WOJEWODZTWO,NR_TEL,NR_UMOWY,HASLO,UPRAWNIENIA_ADMIN,LOGIN) values ('5','y','y','y','11','y','y','3','5','y','0','y');
318REM INSERTING into SERWIS.PRODUCENCI
319SET DEFINE OFF;
320Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('10','dell');
321Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('1','AMD');
322Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('2','INTEL');
323Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('3','MSI');
324Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('4','Gigabyte');
325Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('5','ASRock');
326Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('6','GoodRam');
327Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('7','Asus');
328Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('8','Corsair');
329Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('9','Sapphire');
330Insert into SERWIS.PRODUCENCI (ID_PRODUCENTA,NAZWA_PRODUCENTA) values ('11','TP-LINK');
331REM INSERTING into SERWIS.UMOWY
332SET DEFINE OFF;
333Insert into SERWIS.UMOWY (NR_UMOWY,DATA_ROZPOCZECIA,DATA_ZAKONCZENIA,WYPLATA,ID_PRACOWNIKA) values ('1',to_date('18/01/04','RR/MM/DD'),to_date('18/01/11','RR/MM/DD'),'1','1');
334Insert into SERWIS.UMOWY (NR_UMOWY,DATA_ROZPOCZECIA,DATA_ZAKONCZENIA,WYPLATA,ID_PRACOWNIKA) values ('2',to_date('18/01/04','RR/MM/DD'),to_date('18/01/11','RR/MM/DD'),'2','2');
335Insert into SERWIS.UMOWY (NR_UMOWY,DATA_ROZPOCZECIA,DATA_ZAKONCZENIA,WYPLATA,ID_PRACOWNIKA) values ('3',to_date('18/01/05','RR/MM/DD'),to_date('18/01/10','RR/MM/DD'),'1000','3');
336Insert into SERWIS.UMOWY (NR_UMOWY,DATA_ROZPOCZECIA,DATA_ZAKONCZENIA,WYPLATA,ID_PRACOWNIKA) values ('4',to_date('18/01/05','RR/MM/DD'),to_date('18/02/05','RR/MM/DD'),'25,5','4');
337Insert into SERWIS.UMOWY (NR_UMOWY,DATA_ROZPOCZECIA,DATA_ZAKONCZENIA,WYPLATA,ID_PRACOWNIKA) values ('5',to_date('01/01/02','RR/MM/DD'),to_date('01/01/01','RR/MM/DD'),'3','5');
338REM INSERTING into SERWIS.ZAMOWIENIE_NAPRAWY
339SET DEFINE OFF;
340Insert into SERWIS.ZAMOWIENIE_NAPRAWY (ID_NAPRAWY,ID_KLIENTA,ID_KOMPUTERA,OPIS_USTERKI,DATA_ROZPOCZECIA,DATA_ZAKONCZENIA,STATUS) values ('50','41','30','Nie dzia³a',to_date('18/01/07','RR/MM/DD'),to_date('18/01/07','RR/MM/DD'),'naprawiony');
341Insert into SERWIS.ZAMOWIENIE_NAPRAWY (ID_NAPRAWY,ID_KLIENTA,ID_KOMPUTERA,OPIS_USTERKI,DATA_ROZPOCZECIA,DATA_ZAKONCZENIA,STATUS) values ('51','41','30','Nie dzia³aa',to_date('18/01/07','RR/MM/DD'),to_date('18/01/07','RR/MM/DD'),'naprawiony');
342--------------------------------------------------------
343-- DDL for Index ID_NAPRAWY_2
344--------------------------------------------------------
345
346 CREATE UNIQUE INDEX "SERWIS"."ID_NAPRAWY_2" ON "SERWIS"."PLATNOSCI" ("ID_NAPRAWY")
347 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
348 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
349 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
350 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
351 TABLESPACE "USERS" ;
352--------------------------------------------------------
353-- DDL for Index PRIMARY
354--------------------------------------------------------
355
356 CREATE UNIQUE INDEX "SERWIS"."PRIMARY" ON "SERWIS"."CENNIK" ("ID_USLUGI")
357 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
358 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
359 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
360 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
361 TABLESPACE "USERS" ;
362--------------------------------------------------------
363-- DDL for Index PRIMARY_1
364--------------------------------------------------------
365
366 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_1" ON "SERWIS"."UMOWY" ("NR_UMOWY")
367 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
368 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
369 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
370 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
371 TABLESPACE "USERS" ;
372--------------------------------------------------------
373-- DDL for Index PRIMARY_10
374--------------------------------------------------------
375
376 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_10" ON "SERWIS"."KOMPUTERY" ("ID_KOMPUTERA")
377 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
378 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
379 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
380 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
381 TABLESPACE "USERS" ;
382--------------------------------------------------------
383-- DDL for Index PRIMARY_11
384--------------------------------------------------------
385
386 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_11" ON "SERWIS"."PLATNOSCI" ("ID_FAKTURY")
387 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
388 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
389 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
390 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
391 TABLESPACE "USERS" ;
392--------------------------------------------------------
393-- DDL for Index PRIMARY_2
394--------------------------------------------------------
395
396 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_2" ON "SERWIS"."PRACE_NAPRAWCZE" ("ID_PRACY")
397 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
398 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
399 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
400 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
401 TABLESPACE "USERS" ;
402--------------------------------------------------------
403-- DDL for Index PRIMARY_3
404--------------------------------------------------------
405
406 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_3" ON "SERWIS"."PRODUCENCI" ("ID_PRODUCENTA")
407 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
408 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
409 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
410 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
411 TABLESPACE "USERS" ;
412--------------------------------------------------------
413-- DDL for Index PRIMARY_4
414--------------------------------------------------------
415
416 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_4" ON "SERWIS"."MAGAZYN" ("ID_CZESCI")
417 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
418 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
419 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
420 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
421 TABLESPACE "USERS" ;
422--------------------------------------------------------
423-- DDL for Index PRIMARY_5
424--------------------------------------------------------
425
426 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_5" ON "SERWIS"."PRACOWNICY" ("ID_PRACOWNIKA")
427 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
428 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
429 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
430 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
431 TABLESPACE "USERS" ;
432--------------------------------------------------------
433-- DDL for Index PRIMARY_6
434--------------------------------------------------------
435
436 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_6" ON "SERWIS"."ZAMOWIENIE_NAPRAWY" ("ID_NAPRAWY")
437 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
438 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
439 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
440 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
441 TABLESPACE "USERS" ;
442--------------------------------------------------------
443-- DDL for Index PRIMARY_7
444--------------------------------------------------------
445
446 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_7" ON "SERWIS"."CZESCI_ZAMIENNE" ("ID_CZESCI")
447 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
448 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
449 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
450 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
451 TABLESPACE "USERS" ;
452--------------------------------------------------------
453-- DDL for Index PRIMARY_8
454--------------------------------------------------------
455
456 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_8" ON "SERWIS"."KATEGORIE" ("ID_KATEGORII")
457 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
458 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
459 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
460 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
461 TABLESPACE "USERS" ;
462--------------------------------------------------------
463-- DDL for Index PRIMARY_9
464--------------------------------------------------------
465
466 CREATE UNIQUE INDEX "SERWIS"."PRIMARY_9" ON "SERWIS"."KLIENCI" ("ID_KLIENTA")
467 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
468 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
469 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
470 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
471 TABLESPACE "USERS" ;
472--------------------------------------------------------
473-- DDL for Index ID_USLUGI
474--------------------------------------------------------
475
476 CREATE INDEX "SERWIS"."ID_USLUGI" ON "SERWIS"."PRACE_NAPRAWCZE" ("ID_USLUGI")
477 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
478 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
479 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
480 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
481 TABLESPACE "USERS" ;
482--------------------------------------------------------
483-- DDL for Index ID_KOMPUTERA
484--------------------------------------------------------
485
486 CREATE INDEX "SERWIS"."ID_KOMPUTERA" ON "SERWIS"."ZAMOWIENIE_NAPRAWY" ("ID_KOMPUTERA")
487 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
488 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
489 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
490 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
491 TABLESPACE "USERS" ;
492--------------------------------------------------------
493-- DDL for Index ID_NAPRAWY
494--------------------------------------------------------
495
496 CREATE INDEX "SERWIS"."ID_NAPRAWY" ON "SERWIS"."PRACE_NAPRAWCZE" ("ID_NAPRAWY")
497 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
498 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
499 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
500 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
501 TABLESPACE "USERS" ;
502--------------------------------------------------------
503-- DDL for Index ID_PRODUCENTA
504--------------------------------------------------------
505
506 CREATE INDEX "SERWIS"."ID_PRODUCENTA" ON "SERWIS"."CZESCI_ZAMIENNE" ("ID_PRODUCENTA")
507 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
508 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
509 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
510 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
511 TABLESPACE "USERS" ;
512--------------------------------------------------------
513-- DDL for Index ID_KATEGORII
514--------------------------------------------------------
515
516 CREATE INDEX "SERWIS"."ID_KATEGORII" ON "SERWIS"."CZESCI_ZAMIENNE" ("ID_KATEGORII")
517 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
518 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
519 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
520 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
521 TABLESPACE "USERS" ;
522--------------------------------------------------------
523-- DDL for Index ID_PRACOWNIKA
524--------------------------------------------------------
525
526 CREATE INDEX "SERWIS"."ID_PRACOWNIKA" ON "SERWIS"."UMOWY" ("ID_PRACOWNIKA")
527 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
528 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
529 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
530 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
531 TABLESPACE "USERS" ;
532--------------------------------------------------------
533-- DDL for Index NR_UMOWY_2_1
534--------------------------------------------------------
535
536 CREATE INDEX "SERWIS"."NR_UMOWY_2_1" ON "SERWIS"."PRACOWNICY" ("NR_UMOWY")
537 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
538 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
539 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
540 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
541 TABLESPACE "USERS" ;
542--------------------------------------------------------
543-- DDL for Index ID_PRACOWNIKA_1
544--------------------------------------------------------
545
546 CREATE INDEX "SERWIS"."ID_PRACOWNIKA_1" ON "SERWIS"."PRACE_NAPRAWCZE" ("ID_PRACOWNIKA")
547 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
548 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
549 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
550 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
551 TABLESPACE "USERS" ;
552--------------------------------------------------------
553-- DDL for Index ID_KLIENTA
554--------------------------------------------------------
555
556 CREATE INDEX "SERWIS"."ID_KLIENTA" ON "SERWIS"."ZAMOWIENIE_NAPRAWY" ("ID_KLIENTA")
557 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
558 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
559 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
560 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
561 TABLESPACE "USERS" ;
562--------------------------------------------------------
563-- DDL for Trigger KLIENCI_ID_KLIENTA_TRG
564--------------------------------------------------------
565
566 CREATE OR REPLACE EDITIONABLE TRIGGER "SERWIS"."KLIENCI_ID_KLIENTA_TRG" BEFORE INSERT ON klienci
567FOR EACH ROW
568DECLARE
569v_newVal NUMBER(12) := 0;
570v_incval NUMBER(12) := 0;
571BEGIN
572 IF INSERTING AND :new.id_klienta IS NULL THEN
573 SELECT klienci_id_klienta_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
574 -- If this is the first time this table have been inserted into (sequence == 1)
575 IF v_newVal = 1 THEN
576 --get the max indentity value from the table
577 SELECT NVL(max(id_klienta),0) INTO v_newVal FROM klienci;
578 v_newVal := v_newVal + 1;
579 --set the sequence to that value
580 LOOP
581 EXIT WHEN v_incval>=v_newVal;
582 SELECT klienci_id_klienta_SEQ.nextval INTO v_incval FROM dual;
583 END LOOP;
584 END IF;
585 --used to emulate LAST_INSERT_ID()
586 --mysql_utilities.identity := v_newVal;
587 -- assign the value from the sequence to emulate the identity column
588 :new.id_klienta := v_newVal;
589 END IF;
590END;
591
592/
593ALTER TRIGGER "SERWIS"."KLIENCI_ID_KLIENTA_TRG" ENABLE;
594--------------------------------------------------------
595-- DDL for Trigger KOMPUTERY_ID_KOMPUTERA_TRG
596--------------------------------------------------------
597
598 CREATE OR REPLACE EDITIONABLE TRIGGER "SERWIS"."KOMPUTERY_ID_KOMPUTERA_TRG" BEFORE INSERT ON komputery
599FOR EACH ROW
600DECLARE
601v_newVal NUMBER(12) := 0;
602v_incval NUMBER(12) := 0;
603BEGIN
604 IF INSERTING AND :new.id_komputera IS NULL THEN
605 SELECT komputery_id_komputera_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
606 -- If this is the first time this table have been inserted into (sequence == 1)
607 IF v_newVal = 1 THEN
608 --get the max indentity value from the table
609 SELECT NVL(max(id_komputera),0) INTO v_newVal FROM komputery;
610 v_newVal := v_newVal + 1;
611 --set the sequence to that value
612 LOOP
613 EXIT WHEN v_incval>=v_newVal;
614 SELECT komputery_id_komputera_SEQ.nextval INTO v_incval FROM dual;
615 END LOOP;
616 END IF;
617 --used to emulate LAST_INSERT_ID()
618 --mysql_utilities.identity := v_newVal;
619 -- assign the value from the sequence to emulate the identity column
620 :new.id_komputera := v_newVal;
621 END IF;
622END;
623
624/
625ALTER TRIGGER "SERWIS"."KOMPUTERY_ID_KOMPUTERA_TRG" ENABLE;
626--------------------------------------------------------
627-- DDL for Trigger PLATNOSCI_ID_FAKTURY_TRG
628--------------------------------------------------------
629
630 CREATE OR REPLACE EDITIONABLE TRIGGER "SERWIS"."PLATNOSCI_ID_FAKTURY_TRG" BEFORE INSERT ON platnosci
631FOR EACH ROW
632DECLARE
633v_newVal NUMBER(12) := 0;
634v_incval NUMBER(12) := 0;
635BEGIN
636 IF INSERTING AND :new.id_faktury IS NULL THEN
637 SELECT platnosci_id_faktury_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
638 -- If this is the first time this table have been inserted into (sequence == 1)
639 IF v_newVal = 1 THEN
640 --get the max indentity value from the table
641 SELECT NVL(max(id_faktury),0) INTO v_newVal FROM platnosci;
642 v_newVal := v_newVal + 1;
643 --set the sequence to that value
644 LOOP
645 EXIT WHEN v_incval>=v_newVal;
646 SELECT platnosci_id_faktury_SEQ.nextval INTO v_incval FROM dual;
647 END LOOP;
648 END IF;
649 --used to emulate LAST_INSERT_ID()
650 --mysql_utilities.identity := v_newVal;
651 -- assign the value from the sequence to emulate the identity column
652 :new.id_faktury := v_newVal;
653 END IF;
654END;
655
656/
657ALTER TRIGGER "SERWIS"."PLATNOSCI_ID_FAKTURY_TRG" ENABLE;
658--------------------------------------------------------
659-- DDL for Trigger PRACE_NAPRAWCZE_ID_PRACY_TRG
660--------------------------------------------------------
661
662 CREATE OR REPLACE EDITIONABLE TRIGGER "SERWIS"."PRACE_NAPRAWCZE_ID_PRACY_TRG" BEFORE INSERT ON prace_naprawcze
663FOR EACH ROW
664DECLARE
665v_newVal NUMBER(12) := 0;
666v_incval NUMBER(12) := 0;
667BEGIN
668 IF INSERTING AND :new.id_pracy IS NULL THEN
669 SELECT prace_naprawcze_id_pracy_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
670 -- If this is the first time this table have been inserted into (sequence == 1)
671 IF v_newVal = 1 THEN
672 --get the max indentity value from the table
673 SELECT NVL(max(id_pracy),0) INTO v_newVal FROM prace_naprawcze;
674 v_newVal := v_newVal + 1;
675 --set the sequence to that value
676 LOOP
677 EXIT WHEN v_incval>=v_newVal;
678 SELECT prace_naprawcze_id_pracy_SEQ.nextval INTO v_incval FROM dual;
679 END LOOP;
680 END IF;
681 --used to emulate LAST_INSERT_ID()
682 --mysql_utilities.identity := v_newVal;
683 -- assign the value from the sequence to emulate the identity column
684 :new.id_pracy := v_newVal;
685 END IF;
686END;
687
688/
689ALTER TRIGGER "SERWIS"."PRACE_NAPRAWCZE_ID_PRACY_TRG" ENABLE;
690--------------------------------------------------------
691-- DDL for Trigger PRACOWNICY_ID_PRACOWNIKA_TRG
692--------------------------------------------------------
693
694 CREATE OR REPLACE EDITIONABLE TRIGGER "SERWIS"."PRACOWNICY_ID_PRACOWNIKA_TRG" BEFORE INSERT ON pracownicy
695FOR EACH ROW
696DECLARE
697v_newVal NUMBER(12) := 0;
698v_incval NUMBER(12) := 0;
699BEGIN
700 IF INSERTING AND :new.id_pracownika IS NULL THEN
701 SELECT pracownicy_id_pracownika_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
702 -- If this is the first time this table have been inserted into (sequence == 1)
703 IF v_newVal = 1 THEN
704 --get the max indentity value from the table
705 SELECT NVL(max(id_pracownika),0) INTO v_newVal FROM pracownicy;
706 v_newVal := v_newVal + 1;
707 --set the sequence to that value
708 LOOP
709 EXIT WHEN v_incval>=v_newVal;
710 SELECT pracownicy_id_pracownika_SEQ.nextval INTO v_incval FROM dual;
711 END LOOP;
712 END IF;
713 --used to emulate LAST_INSERT_ID()
714 --mysql_utilities.identity := v_newVal;
715 -- assign the value from the sequence to emulate the identity column
716 :new.id_pracownika := v_newVal;
717 END IF;
718END;
719
720/
721ALTER TRIGGER "SERWIS"."PRACOWNICY_ID_PRACOWNIKA_TRG" ENABLE;
722--------------------------------------------------------
723-- DDL for Trigger ZAMOWIENIE_NAPRAWY_ID_NAPRAW_1
724--------------------------------------------------------
725
726 CREATE OR REPLACE EDITIONABLE TRIGGER "SERWIS"."ZAMOWIENIE_NAPRAWY_ID_NAPRAW_1" BEFORE INSERT ON zamowienie_naprawy
727FOR EACH ROW
728DECLARE
729v_newVal NUMBER(12) := 0;
730v_incval NUMBER(12) := 0;
731BEGIN
732 IF INSERTING AND :new.id_naprawy IS NULL THEN
733 SELECT zamowienie_naprawy_id_naprawy_.NEXTVAL INTO v_newVal FROM DUAL;
734 -- If this is the first time this table have been inserted into (sequence == 1)
735 IF v_newVal = 1 THEN
736 --get the max indentity value from the table
737 SELECT NVL(max(id_naprawy),0) INTO v_newVal FROM zamowienie_naprawy;
738 v_newVal := v_newVal + 1;
739 --set the sequence to that value
740 LOOP
741 EXIT WHEN v_incval>=v_newVal;
742 SELECT zamowienie_naprawy_id_naprawy_.nextval INTO v_incval FROM dual;
743 END LOOP;
744 END IF;
745 --used to emulate LAST_INSERT_ID()
746 --mysql_utilities.identity := v_newVal;
747 -- assign the value from the sequence to emulate the identity column
748 :new.id_naprawy := v_newVal;
749 END IF;
750END;
751
752/
753ALTER TRIGGER "SERWIS"."ZAMOWIENIE_NAPRAWY_ID_NAPRAW_1" ENABLE;
754--------------------------------------------------------
755-- DDL for Trigger ZAMOWIENIE_NAPRAWY_STATUS_ETRG
756--------------------------------------------------------
757
758 CREATE OR REPLACE EDITIONABLE TRIGGER "SERWIS"."ZAMOWIENIE_NAPRAWY_STATUS_ETRG"
759BEFORE INSERT OR UPDATE ON zamowienie_naprawy
760FOR EACH ROW
761DECLARE
762 v_val zamowienie_naprawy.status%TYPE;
763 v_test zamowienie_naprawy.status%TYPE;
764BEGIN
765 v_val := 'nowy';
766 v_test := TRIM(:new.status);
767 if v_test = '1' OR v_test = 'nowy' THEN
768 v_val := 'nowy';
769 elsif v_test = '2' OR v_test = 'przyjeto' THEN
770 v_val := 'przyjeto';
771 elsif v_test = '3' OR v_test = 'w naprawie' THEN
772 v_val := 'w naprawie';
773 elsif v_test = '4' OR v_test = 'naprawiony' THEN
774 v_val := 'naprawiony';
775 end if; :new.status := v_val;
776END;
777
778/
779ALTER TRIGGER "SERWIS"."ZAMOWIENIE_NAPRAWY_STATUS_ETRG" ENABLE;
780--------------------------------------------------------
781-- DDL for Procedure ADD_ACCOUNT
782--------------------------------------------------------
783set define off;
784
785 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."ADD_ACCOUNT"
786( pFirstName IN varchar2,
787 pLastName IN varchar2,
788 pAddress IN varchar2,
789 pPostCode IN NUMBER,
790 pCity IN varchar2,
791 pState IN varchar2,
792 pPhone IN NUMBER,
793 pAdmAcc IN NUMBER,
794 pContractStart IN DATE,
795 pContractEnd IN DATE,
796 pLogin IN varchar2,
797 pPass IN varchar2,
798 pSalary IN FLOAT
799)
800IS
801vContractNo NUMBER;
802vAccNo NUMBER;
803BEGIN
804
805SELECT (max(NR_UMOWY)+1) INTO vContractNo FROM SERWIS.UMOWY;
806SELECT (max(ID_PRACOWNIKA)+1) INTO vAccNo FROM SERWIS.PRACOWNICY;
807
808
809
810INSERT INTO SERWIS.UMOWY (
811DATA_ROZPOCZECIA, DATA_ZAKONCZENIA, WYPLATA, NR_UMOWY, ID_PRACOWNIKA)
812VALUES (
813pContractStart, pContractEnd, pSalary, vContractNo, vAccNo);
814
815INSERT INTO SERWIS.PRACOWNICY (
816IMIE, NAZWISKO, ADRES, KOD_POCZTOWY, MIASTO, WOJEWODZTWO, NR_TEL,
817LOGIN, HASLO, UPRAWNIENIA_ADMIN, ID_PRACOWNIKA, NR_UMOWY)
818VALUES (
819pFirstName, pLastName, pAddress, pPostCode, pCity, pState, pPhone, pLogin, pPass, pAdmAcc, vAccNo, vContractNo);
820
821commit;
822END;
823
824/
825--------------------------------------------------------
826-- DDL for Procedure ADD_ITEMS
827--------------------------------------------------------
828set define off;
829
830 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."ADD_ITEMS" (
831pPartId NUMBER,
832pPiecesNo NUMBER
833)
834IS
835vPiecesBefore NUMBER;
836vPiecesAfter NUMBER;
837BEGIN
838 SELECT LICZBA_SZTUK INTO vPiecesBefore FROM MAGAZYN WHERE ID_CZESCI = pPartId;
839 vPiecesAfter := vPiecesBefore + pPiecesNo;
840
841 UPDATE MAGAZYN
842 SET LICZBA_SZTUK = vPiecesAfter
843 WHERE ID_CZESCI = pPartId;
844commit;
845END;
846
847/
848--------------------------------------------------------
849-- DDL for Procedure ADD_NEW_ITEM
850--------------------------------------------------------
851set define off;
852
853 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."ADD_NEW_ITEM" (
854pCategory VARCHAR2,
855pProducer VARCHAR2,
856pModel VARCHAR2,
857pPrice NUMBER
858)
859IS
860vId NUMBER;
861vCategoryId NUMBER;
862vProducerId NUMBER;
863BEGIN
864SELECT max(ID_CZESCI)+1 INTO vId FROM CZESCI_ZAMIENNE;
865 BEGIN
866 SELECT ID_KATEGORII INTO vCategoryId FROM KATEGORIE WHERE UPPER(NAZWA_KATEGORII) LIKE UPPER(pCategory); -- znajdz id kategorii
867 EXCEPTION WHEN NO_DATA_FOUND THEN vCategoryId := null;
868 END;
869-- jesli nie ma dodaj
870IF vCategoryId IS NULL THEN
871 SELECT max(ID_KATEGORII)+1 INTO vCategoryId FROM KATEGORIE;
872 INSERT INTO KATEGORIE(ID_KATEGORII, NAZWA_KATEGORII)
873 VALUES (vCategoryId, pCategory);
874END IF;
875 BEGIN
876 SELECT ID_PRODUCENTA INTO vProducerId FROM PRODUCENCI WHERE UPPER(NAZWA_PRODUCENTA) LIKE UPPER(pProducer); -- znajdz id producenta
877 EXCEPTION WHEN NO_DATA_FOUND THEN vProducerId := null;
878 END;
879-- jesli nie ma dodaj
880IF vProducerId IS NULL THEN
881 SELECT max(ID_PRODUCENTA)+1 INTO vProducerId FROM PRODUCENCI;
882 INSERT INTO PRODUCENCI(ID_PRODUCENTA, NAZWA_PRODUCENTA)
883 VALUES (vProducerId, pProducer);
884END IF;
885
886 INSERT INTO CZESCI_ZAMIENNE (ID_CZESCI, ID_KATEGORII, ID_PRODUCENTA, MODEL, CENA)
887 VALUES (vId, vCategoryId, vProducerId, pModel, pPrice);
888
889 INSERT INTO MAGAZYN (ID_CZESCI, LICZBA_SZTUK)
890 VALUES (vId, 0);
891commit;
892END;
893
894/
895--------------------------------------------------------
896-- DDL for Procedure ADD_NEW_SERVICE
897--------------------------------------------------------
898set define off;
899
900 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."ADD_NEW_SERVICE" (
901pName VARCHAR2,
902pPrice FLOAT
903)
904IS
905vId NUMBER;
906BEGIN
907SELECT max(ID_USLUGI)+1 INTO vId FROM CENNIK;
908
909 INSERT INTO CENNIK (ID_USLUGI, NAZWA_USLUGI, CENA)
910 VALUES (vId, pName, pPrice);
911commit;
912END;
913
914/
915--------------------------------------------------------
916-- DDL for Procedure DELETE_ACCOUNT
917--------------------------------------------------------
918set define off;
919
920 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."DELETE_ACCOUNT"
921( pAccId IN NUMBER
922)
923IS
924BEGIN
925 DELETE FROM SERWIS.PRACOWNICY WHERE ID_PRACOWNIKA = pAccId;
926 DELETE FROM SERWIS.UMOWY WHERE ID_PRACOWNIKA = pAccId;
927commit;
928END;
929
930/
931--------------------------------------------------------
932-- DDL for Procedure DISMISS_WORKER
933--------------------------------------------------------
934set define off;
935
936 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."DISMISS_WORKER"
937( pAccId IN NUMBER
938)
939IS
940vDismissDate DATE;
941BEGIN
942 vDismissDate := SYSDATE;
943
944 UPDATE SERWIS.PRACOWNICY
945 SET UPRAWNIENIA_ADMIN = -1
946 WHERE ID_PRACOWNIKA = pAccId;
947
948 UPDATE SERWIS.UMOWY
949 SET DATA_ZAKONCZENIA = vDismissDate
950 WHERE ID_PRACOWNIKA = pAccId;
951commit;
952END;
953
954/
955--------------------------------------------------------
956-- DDL for Procedure DOSERVICE
957--------------------------------------------------------
958set define off;
959
960 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."DOSERVICE" (
961pServiceId NUMBER, -- nr uslugi z cennika
962pRepairId NUMBER, -- nr pracy (ktory sprzet)
963pWorkerId NUMBER -- nr pracownika
964)
965IS
966vWorkNo NUMBER;
967vPrice NUMBER;
968BEGIN
969SELECT MAX(ID_PRACY)+1 INTO vWorkNo FROM PRACE_NAPRAWCZE;
970SELECT CENA INTO vPrice FROM CENNIK WHERE ID_USLUGI = pServiceId;
971INSERT INTO PRACE_NAPRAWCZE (ID_PRACY, ID_USLUGI, ID_NAPRAWY, ID_PRACOWNIKA, CENA_PRACY)
972VALUES (vWorkNo, pServiceId, pRepairId, pWorkerId, vPrice);
973commit;
974END;
975
976/
977--------------------------------------------------------
978-- DDL for Procedure END_REPAIR
979--------------------------------------------------------
980set define off;
981
982 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."END_REPAIR" (
983pWorkNo NUMBER
984)
985IS
986vBillNo NUMBER;
987vToPay NUMBER;
988vToPayServices NUMBER;
989vToPayParts NUMBER;
990BEGIN
991 SELECT max(ID_FAKTURY)+1 INTO vBillNo FROM PLATNOSCI;
992 SELECT SUM(CENA_PRACY) INTO vToPayParts FROM PRACE_NAPRAWCZE WHERE ID_NAPRAWY = pWorkNo;
993 SELECT SUM(CENA_ZAKUPU) INTO vToPayServices FROM PRACE_NAPRAWCZE_CZESCI NATURAL JOIN PRACE_NAPRAWCZE WHERE ID_NAPRAWY = pWorkNo;
994
995 vToPay := vToPayParts + vToPayServices;
996 UPDATE ZAMOWIENIE_NAPRAWY
997 SET STATUS = 'naprawiony',
998 DATA_ZAKONCZENIA = SYSDATE
999 WHERE ID_NAPRAWY = pWorkNo;
1000
1001 INSERT INTO PLATNOSCI(
1002 ID_FAKTURY, ID_NAPRAWY, DO_ZAPLATY, ZAPLACONO)
1003 VALUES (vBillNo, pWorkNo, vToPay, 0);
1004commit;
1005END;
1006
1007/
1008--------------------------------------------------------
1009-- DDL for Procedure EXCHANGEPART
1010--------------------------------------------------------
1011set define off;
1012
1013 CREATE OR REPLACE EDITIONABLE PROCEDURE "SERWIS"."EXCHANGEPART" (
1014pIdNaprawy NUMBER,
1015pIdCzesci NUMBER
1016)
1017IS
1018-- numer pracy napraczej gdy wymieniano czesc (100- kod wymiany czesci)
1019-- nie bierze pod uwagê przypadku, ¿e wpisano us³ugi wymiana czêœci i nie ma vPracyNaprawczej
1020vPracaNaprawcza NUMBER;
1021vPiecesBefore NUMBER;
1022vPiecesAfter NUMBER;
1023vPrice NUMBER;
1024BEGIN
1025SELECT CENA INTO vPrice FROM CZESCI_ZAMIENNE WHERE ID_CZESCI = pIdCzesci;
1026SELECT max(ID_PRACY) INTO vPracaNaprawcza FROM PRACE_NAPRAWCZE WHERE ID_NAPRAWY=pIdNaprawy AND ID_USLUGI = 100;
1027
1028INSERT INTO PRACE_NAPRAWCZE_CZESCI (ID_PRACY, ID_CZESCI, CENA_ZAKUPU)
1029VALUES (vPracaNaprawcza, pIdCzesci, vPrice);
1030
1031SELECT LICZBA_SZTUK INTO vPiecesBefore FROM MAGAZYN WHERE ID_CZESCI = pIdCzesci;
1032vPiecesAfter := vPiecesBefore - 1;
1033
1034 UPDATE MAGAZYN
1035 SET LICZBA_SZTUK = vPiecesAfter
1036 WHERE ID_CZESCI = pIdCzesci;
1037
1038commit;
1039END;
1040
1041/
1042--------------------------------------------------------
1043-- DDL for Function TAKE_WORK
1044--------------------------------------------------------
1045
1046 CREATE OR REPLACE EDITIONABLE FUNCTION "SERWIS"."TAKE_WORK"
1047( pUserId NUMBER,
1048 pReportNo NUMBER -- nr zgloszenia wybrany przez pracownika z tabeli zamowienie_naprawy
1049)
1050RETURN NUMBER
1051IS
1052PRAGMA AUTONOMOUS_TRANSACTION;
1053 vFlag NUMBER;
1054 vWorkNo NUMBER;
1055 vPrice NUMBER;
1056BEGIN
1057 SELECT max(ID_PRACY)+1 INTO vWorkNo FROM PRACE_NAPRAWCZE;
1058 SELECT CENA INTO vPrice FROM CENNIK WHERE ID_USLUGI = 1; -- cena przyjecia do serwisu
1059 SELECT CASE WHEN EXISTS (SELECT * FROM ZAMOWIENIE_NAPRAWY WHERE ID_NAPRAWY = pReportNo AND STATUS = 'nowy') THEN 1 ELSE 0 END INTO vFlag FROM dual;
1060 IF vFlag = 1 THEN
1061 INSERT INTO PRACE_NAPRAWCZE (ID_PRACY, ID_USLUGI, ID_NAPRAWY, ID_PRACOWNIKA, CENA_PRACY)
1062 -- id usugi = 1 - przyjêcie do serwisu
1063 VALUES (vWorkNo, 1, pReportNo, pUserId, vPrice);
1064
1065 UPDATE "SERWIS"."ZAMOWIENIE_NAPRAWY"
1066 SET STATUS = 'w naprawie'
1067 WHERE ID_NAPRAWY = pReportNo;
1068 commit;
1069 ELSE
1070 vFlag := 0;
1071 END IF;
1072
1073RETURN vFlag;
1074END;
1075
1076/
1077--------------------------------------------------------
1078-- Constraints for Table PLATNOSCI
1079--------------------------------------------------------
1080
1081 ALTER TABLE "SERWIS"."PLATNOSCI" MODIFY ("ID_FAKTURY" NOT NULL ENABLE);
1082 ALTER TABLE "SERWIS"."PLATNOSCI" MODIFY ("ID_NAPRAWY" NOT NULL ENABLE);
1083 ALTER TABLE "SERWIS"."PLATNOSCI" ADD CONSTRAINT "PRIMARY_11" PRIMARY KEY ("ID_FAKTURY")
1084 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1085 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1086 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1087 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1088 TABLESPACE "USERS" ENABLE;
1089 ALTER TABLE "SERWIS"."PLATNOSCI" ADD CONSTRAINT "ID_NAPRAWY_2" UNIQUE ("ID_NAPRAWY")
1090 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1091 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1092 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1093 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1094 TABLESPACE "USERS" ENABLE;
1095--------------------------------------------------------
1096-- Constraints for Table PRACOWNICY
1097--------------------------------------------------------
1098
1099 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("ID_PRACOWNIKA" NOT NULL ENABLE);
1100 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("IMIE" NOT NULL ENABLE);
1101 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("NAZWISKO" NOT NULL ENABLE);
1102 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("ADRES" NOT NULL ENABLE);
1103 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("KOD_POCZTOWY" NOT NULL ENABLE);
1104 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("MIASTO" NOT NULL ENABLE);
1105 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("WOJEWODZTWO" NOT NULL ENABLE);
1106 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("NR_TEL" NOT NULL ENABLE);
1107 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("NR_UMOWY" NOT NULL ENABLE);
1108 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("HASLO" NOT NULL ENABLE);
1109 ALTER TABLE "SERWIS"."PRACOWNICY" MODIFY ("UPRAWNIENIA_ADMIN" NOT NULL ENABLE);
1110 ALTER TABLE "SERWIS"."PRACOWNICY" ADD CONSTRAINT "PRIMARY_5" PRIMARY KEY ("ID_PRACOWNIKA")
1111 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1112 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1113 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1114 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1115 TABLESPACE "USERS" ENABLE;
1116--------------------------------------------------------
1117-- Constraints for Table KOMPUTERY
1118--------------------------------------------------------
1119
1120 ALTER TABLE "SERWIS"."KOMPUTERY" MODIFY ("ID_KOMPUTERA" NOT NULL ENABLE);
1121 ALTER TABLE "SERWIS"."KOMPUTERY" ADD CONSTRAINT "PRIMARY_10" PRIMARY KEY ("ID_KOMPUTERA")
1122 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1123 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1124 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1125 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1126 TABLESPACE "USERS" ENABLE;
1127--------------------------------------------------------
1128-- Constraints for Table MAGAZYN
1129--------------------------------------------------------
1130
1131 ALTER TABLE "SERWIS"."MAGAZYN" MODIFY ("ID_CZESCI" NOT NULL ENABLE);
1132 ALTER TABLE "SERWIS"."MAGAZYN" MODIFY ("LICZBA_SZTUK" NOT NULL ENABLE);
1133 ALTER TABLE "SERWIS"."MAGAZYN" ADD CONSTRAINT "PRIMARY_4" PRIMARY KEY ("ID_CZESCI")
1134 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1135 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1136 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1137 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1138 TABLESPACE "USERS" ENABLE;
1139--------------------------------------------------------
1140-- Constraints for Table CENNIK
1141--------------------------------------------------------
1142
1143 ALTER TABLE "SERWIS"."CENNIK" MODIFY ("ID_USLUGI" NOT NULL ENABLE);
1144 ALTER TABLE "SERWIS"."CENNIK" MODIFY ("NAZWA_USLUGI" NOT NULL ENABLE);
1145 ALTER TABLE "SERWIS"."CENNIK" MODIFY ("CENA" NOT NULL ENABLE);
1146 ALTER TABLE "SERWIS"."CENNIK" ADD CONSTRAINT "PRIMARY" PRIMARY KEY ("ID_USLUGI")
1147 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1148 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1149 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1150 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1151 TABLESPACE "USERS" ENABLE;
1152--------------------------------------------------------
1153-- Constraints for Table KATEGORIE
1154--------------------------------------------------------
1155
1156 ALTER TABLE "SERWIS"."KATEGORIE" MODIFY ("ID_KATEGORII" NOT NULL ENABLE);
1157 ALTER TABLE "SERWIS"."KATEGORIE" MODIFY ("NAZWA_KATEGORII" NOT NULL ENABLE);
1158 ALTER TABLE "SERWIS"."KATEGORIE" ADD CONSTRAINT "PRIMARY_8" PRIMARY KEY ("ID_KATEGORII")
1159 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1160 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1161 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1162 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1163 TABLESPACE "USERS" ENABLE;
1164--------------------------------------------------------
1165-- Constraints for Table UMOWY
1166--------------------------------------------------------
1167
1168 ALTER TABLE "SERWIS"."UMOWY" MODIFY ("NR_UMOWY" NOT NULL ENABLE);
1169 ALTER TABLE "SERWIS"."UMOWY" MODIFY ("DATA_ROZPOCZECIA" NOT NULL ENABLE);
1170 ALTER TABLE "SERWIS"."UMOWY" MODIFY ("DATA_ZAKONCZENIA" NOT NULL ENABLE);
1171 ALTER TABLE "SERWIS"."UMOWY" MODIFY ("WYPLATA" NOT NULL ENABLE);
1172 ALTER TABLE "SERWIS"."UMOWY" MODIFY ("ID_PRACOWNIKA" NOT NULL ENABLE);
1173 ALTER TABLE "SERWIS"."UMOWY" ADD CONSTRAINT "PRIMARY_1" PRIMARY KEY ("NR_UMOWY")
1174 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1175 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1176 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1177 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1178 TABLESPACE "USERS" ENABLE;
1179--------------------------------------------------------
1180-- Constraints for Table PRODUCENCI
1181--------------------------------------------------------
1182
1183 ALTER TABLE "SERWIS"."PRODUCENCI" MODIFY ("ID_PRODUCENTA" NOT NULL ENABLE);
1184 ALTER TABLE "SERWIS"."PRODUCENCI" MODIFY ("NAZWA_PRODUCENTA" NOT NULL ENABLE);
1185 ALTER TABLE "SERWIS"."PRODUCENCI" ADD CONSTRAINT "PRIMARY_3" PRIMARY KEY ("ID_PRODUCENTA")
1186 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1187 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1188 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1189 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1190 TABLESPACE "USERS" ENABLE;
1191--------------------------------------------------------
1192-- Constraints for Table ZAMOWIENIE_NAPRAWY
1193--------------------------------------------------------
1194
1195 ALTER TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY" MODIFY ("ID_NAPRAWY" NOT NULL ENABLE);
1196 ALTER TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY" MODIFY ("ID_KLIENTA" NOT NULL ENABLE);
1197 ALTER TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY" MODIFY ("ID_KOMPUTERA" NOT NULL ENABLE);
1198 ALTER TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY" MODIFY ("OPIS_USTERKI" NOT NULL ENABLE);
1199 ALTER TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY" ADD CONSTRAINT "PRIMARY_6" PRIMARY KEY ("ID_NAPRAWY")
1200 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1201 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1202 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1203 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1204 TABLESPACE "USERS" ENABLE;
1205 ALTER TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY" MODIFY ("DATA_ROZPOCZECIA" NOT NULL ENABLE);
1206--------------------------------------------------------
1207-- Constraints for Table KLIENCI
1208--------------------------------------------------------
1209
1210 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("ID_KLIENTA" NOT NULL ENABLE);
1211 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("IMIE" NOT NULL ENABLE);
1212 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("NAZWISKO" NOT NULL ENABLE);
1213 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("ADRES" NOT NULL ENABLE);
1214 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("KOD_POCZTOWY" NOT NULL ENABLE);
1215 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("MIASTO" NOT NULL ENABLE);
1216 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("WOJEWODZTWO" NOT NULL ENABLE);
1217 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("HASLO" NOT NULL ENABLE);
1218 ALTER TABLE "SERWIS"."KLIENCI" ADD CONSTRAINT "PRIMARY_9" PRIMARY KEY ("ID_KLIENTA")
1219 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1220 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1221 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1222 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1223 TABLESPACE "USERS" ENABLE;
1224 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("LOGIN" NOT NULL ENABLE);
1225 ALTER TABLE "SERWIS"."KLIENCI" MODIFY ("EMAIL" NOT NULL ENABLE);
1226--------------------------------------------------------
1227-- Constraints for Table PRACE_NAPRAWCZE
1228--------------------------------------------------------
1229
1230 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE" MODIFY ("ID_PRACY" NOT NULL ENABLE);
1231 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE" MODIFY ("ID_USLUGI" NOT NULL ENABLE);
1232 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE" MODIFY ("ID_NAPRAWY" NOT NULL ENABLE);
1233 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE" ADD CONSTRAINT "PRIMARY_2" PRIMARY KEY ("ID_PRACY")
1234 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1235 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1236 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1237 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1238 TABLESPACE "USERS" ENABLE;
1239--------------------------------------------------------
1240-- Constraints for Table CZESCI_ZAMIENNE
1241--------------------------------------------------------
1242
1243 ALTER TABLE "SERWIS"."CZESCI_ZAMIENNE" MODIFY ("ID_CZESCI" NOT NULL ENABLE);
1244 ALTER TABLE "SERWIS"."CZESCI_ZAMIENNE" MODIFY ("ID_KATEGORII" NOT NULL ENABLE);
1245 ALTER TABLE "SERWIS"."CZESCI_ZAMIENNE" MODIFY ("ID_PRODUCENTA" NOT NULL ENABLE);
1246 ALTER TABLE "SERWIS"."CZESCI_ZAMIENNE" MODIFY ("MODEL" NOT NULL ENABLE);
1247 ALTER TABLE "SERWIS"."CZESCI_ZAMIENNE" MODIFY ("CENA" NOT NULL ENABLE);
1248 ALTER TABLE "SERWIS"."CZESCI_ZAMIENNE" ADD CONSTRAINT "PRIMARY_7" PRIMARY KEY ("ID_CZESCI")
1249 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
1250 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
1251 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
1252 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
1253 TABLESPACE "USERS" ENABLE;
1254--------------------------------------------------------
1255-- Ref Constraints for Table CZESCI_ZAMIENNE
1256--------------------------------------------------------
1257
1258 ALTER TABLE "SERWIS"."CZESCI_ZAMIENNE" ADD CONSTRAINT "CZESCI_ZAMIENNE_IBFK_1" FOREIGN KEY ("ID_KATEGORII")
1259 REFERENCES "SERWIS"."KATEGORIE" ("ID_KATEGORII") ENABLE;
1260 ALTER TABLE "SERWIS"."CZESCI_ZAMIENNE" ADD CONSTRAINT "CZESCI_ZAMIENNE_IBFK_2" FOREIGN KEY ("ID_PRODUCENTA")
1261 REFERENCES "SERWIS"."PRODUCENCI" ("ID_PRODUCENTA") ENABLE;
1262--------------------------------------------------------
1263-- Ref Constraints for Table MAGAZYN
1264--------------------------------------------------------
1265
1266 ALTER TABLE "SERWIS"."MAGAZYN" ADD CONSTRAINT "MAGAZYN_IBFK_1" FOREIGN KEY ("ID_CZESCI")
1267 REFERENCES "SERWIS"."CZESCI_ZAMIENNE" ("ID_CZESCI") ENABLE;
1268--------------------------------------------------------
1269-- Ref Constraints for Table PLATNOSCI
1270--------------------------------------------------------
1271
1272 ALTER TABLE "SERWIS"."PLATNOSCI" ADD CONSTRAINT "PLATNOSCI_IBFK_1" FOREIGN KEY ("ID_NAPRAWY")
1273 REFERENCES "SERWIS"."ZAMOWIENIE_NAPRAWY" ("ID_NAPRAWY") ENABLE;
1274--------------------------------------------------------
1275-- Ref Constraints for Table PRACE_NAPRAWCZE
1276--------------------------------------------------------
1277
1278 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE" ADD CONSTRAINT "PRACE_NAPRAWCZE_IBFK_1" FOREIGN KEY ("ID_PRACOWNIKA")
1279 REFERENCES "SERWIS"."PRACOWNICY" ("ID_PRACOWNIKA") ENABLE;
1280 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE" ADD CONSTRAINT "PRACE_NAPRAWCZE_IBFK_2" FOREIGN KEY ("ID_USLUGI")
1281 REFERENCES "SERWIS"."CENNIK" ("ID_USLUGI") ENABLE;
1282 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE" ADD CONSTRAINT "PRACE_NAPRAWCZE_IBFK_3" FOREIGN KEY ("ID_NAPRAWY")
1283 REFERENCES "SERWIS"."ZAMOWIENIE_NAPRAWY" ("ID_NAPRAWY") ENABLE;
1284--------------------------------------------------------
1285-- Ref Constraints for Table PRACE_NAPRAWCZE_CZESCI
1286--------------------------------------------------------
1287
1288 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE_CZESCI" ADD CONSTRAINT "FK_PRAC_NAPR" FOREIGN KEY ("ID_PRACY")
1289 REFERENCES "SERWIS"."PRACE_NAPRAWCZE" ("ID_PRACY") ENABLE;
1290 ALTER TABLE "SERWIS"."PRACE_NAPRAWCZE_CZESCI" ADD CONSTRAINT "FK_ID_CZESCI" FOREIGN KEY ("ID_CZESCI")
1291 REFERENCES "SERWIS"."CZESCI_ZAMIENNE" ("ID_CZESCI") ENABLE;
1292--------------------------------------------------------
1293-- Ref Constraints for Table PRACOWNICY
1294--------------------------------------------------------
1295
1296 ALTER TABLE "SERWIS"."PRACOWNICY" ADD CONSTRAINT "PRACOWNICY_IBFK_1" FOREIGN KEY ("NR_UMOWY")
1297 REFERENCES "SERWIS"."UMOWY" ("NR_UMOWY") ENABLE;
1298--------------------------------------------------------
1299-- Ref Constraints for Table ZAMOWIENIE_NAPRAWY
1300--------------------------------------------------------
1301
1302 ALTER TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY" ADD CONSTRAINT "ZAMOWIENIE_NAPRAWY_IBFK_1" FOREIGN KEY ("ID_KLIENTA")
1303 REFERENCES "SERWIS"."KLIENCI" ("ID_KLIENTA") ENABLE;
1304 ALTER TABLE "SERWIS"."ZAMOWIENIE_NAPRAWY" ADD CONSTRAINT "ZAMOWIENIE_NAPRAWY_IBFK_2" FOREIGN KEY ("ID_KOMPUTERA")
1305 REFERENCES "SERWIS"."KOMPUTERY" ("ID_KOMPUTERA") ENABLE;