DatabaseCreation: dml.sql

File dml.sql, 79.9 KB (added by 231018, 30 hours ago)

dml skripta za popolnuvanje na tebeli

Line 
1-- ===================== ZONA =====================
2insert into Zona (ime)
3select
4 (array[
5 'centralna zona',
6 'rezidentska zona',
7 'uchilishna zona',
8 'bolnichka zona',
9 'industriska zona',
10 'komercijalna zona',
11 'rekreativna zona',
12 'avtopat zona',
13 'peshachka zona',
14 'turistichka zona'
15 ])[floor(random()*10 + 1)]
16from generate_series(1, 3000);
17
18update Zona
19set dozvolena_brzina =
20 case
21 when ime like 'uchilishna zona%' then 30
22 when ime like 'bolnichka zona%' then 30
23 when ime like 'peshachka zona%' then 20
24 when ime like 'rezidentska zona%' then 40
25 when ime like 'rekreativna zona%' then 30
26 when ime like 'centralna zona%' then 50
27 when ime like 'komercijalna zona%' then 50
28 when ime like 'turistichka zona%' then 40
29 when ime like 'industriska zona%' then 60
30 when ime like 'avtopat zona%' then 80
31 end
32where 1 = 1;
33
34update Zona
35set opstina =
36 case
37 when ime like 'avtopat zona%' then
38 (array [
39 'skopje','kumanovo','tetovo','veles','negotino',
40 'gevgelija','bitola','prilep','shtip','strumica',
41 'gostivar','ohrid','kochani','kavadarci'
42 ])[floor(random() * 13 + 1)]
43
44 when ime like 'centralna zona%' then
45 (array [
46 'centar','aerodrom','karpos','kisela voda','gjorce petrov',
47 'chair','butel','gazi baba','saraj','shuto orizari',
48 'ilinden','petrovec','sopishte','studenichani',
49 'skopje','kumanovo','tetovo','bitola','ohrid','prilep',
50 'strumica','veles','kavadarci','negotino','gostivar',
51 'struga','gevgelija','kocani','vinica','shtip'
52 ])[floor(random() * 30 + 1)]
53
54 when ime like 'rezidentska zona%' then
55 (array [
56 'aerodrom','karpos','kisela voda','gjorce petrov',
57 'butel','gazi baba','chair','shuto orizari',
58 'kumanovo','tetovo','bitola','prilep','veles',
59 'strumica','kochani','shtip','gostivar','kavadarci'
60 ])[floor(random() * 17 + 1)]
61
62 when ime like 'uchilishna zona%' then
63 (array [
64 'centar','aerodrom','karpos','kisela voda','gjorce petrov',
65 'chair','butel','gazi baba',
66 'kumanovo','tetovo','bitola','ohrid','prilep',
67 'strumica','veles','gostivar','struga','kochani','shtip','kavadarci'
68 ])[floor(random() * 19 + 1)]
69
70 when ime like 'bolnichka zona%' then
71 (array [
72 'centar','karpos','aerodrom',
73 'skopje','bitola','tetovo','kumanovo',
74 'ohrid','prilep','strumica','veles','shtip','kavadarci'
75 ])[floor(random() * 12 + 1)]
76
77 when ime like 'industriska zona%' then
78 (array [
79 'gazi baba','ilinden','petrovec',
80 'kumanovo','veles','kavadarci','negotino',
81 'prilep','bitola','tetovo','kochani','shtip','strumica'
82 ])[floor(random() * 13 + 1)]
83
84 when ime like 'komercijalna zona%' then
85 (array [
86 'centar','aerodrom','karpos','chair','gazi baba',
87 'skopje','kumanovo','tetovo','bitola','ohrid',
88 'prilep','strumica','veles','gostivar','struga',
89 'gevgelija','kochani','shtip','kavadarci'
90 ])[floor(random() * 18 + 1)]
91
92 when ime like 'rekreativna zona%' then
93 (array [
94 'karpos','gjorce petrov','saraj','sopishte',
95 'ohrid','struga','tetovo','gostivar',
96 'bitola','prilep','gevgelija','mavrovo','kavadarci'
97 ])[floor(random() * 12 + 1)]
98
99 when ime like 'peshachka zona%' then
100 (array [
101 'centar','aerodrom','karpos','chair',
102 'skopje','ohrid','bitola','tetovo',
103 'prilep','struga','kumanovo','shtip','kavadarci'
104 ])[floor(random() * 12 + 1)]
105
106 when ime like 'turistichka zona%' then
107 (array [
108 'ohrid','struga','gevgelija','bitola',
109 'krushevo','mavrovo','dojran','berovo',
110 'prilep','tetovo','skopje','kavadarci'
111 ])[floor(random() * 11 + 1)]
112 end
113where 1 = 1;
114
115insert into Zona (ime)
116select (array [
117 'centralna zona',
118 'rezidentska zona',
119 'uchilishna zona',
120 'bolnichka zona',
121 'industriska zona',
122 'komercijalna zona',
123 'rekreativna zona',
124 'avtopat zona',
125 'peshachka zona',
126 'turistichka zona'
127 ])[floor(random() * 10 + 1)]
128from generate_series(1, 3000);
129
130
131-- ===================== LOKACIJA =====================
132insert into Lokacija (zona_id, ulica, latitude, longitude)
133select
134 z.zona_id,
135 case
136 when z.opstina = 'centar' then
137 (array[
138 'dimitrie chupovski',
139 'makedonija',
140 'partizanski odredi',
141 'mitropolit teodosij gologanov',
142 'goce delchev'
143 ])[floor(random()*5 + 1)]
144
145 when z.opstina = 'aerodrom' then
146 (array[
147 'jane sandanski',
148 'srbija',
149 'vasil karangeleski',
150 'treta makedonska brigada',
151 'video smilevski bato'
152 ])[floor(random()*5 + 1)]
153
154 when z.opstina = 'karpos' then
155 (array[
156 'partizanski odredi',
157 'ilindenska',
158 'nikola tesla',
159 'moskovska',
160 'kozle'
161 ])[floor(random()*5 + 1)]
162
163 when z.opstina = 'kisela voda' then
164 (array[
165 'boris trajkovski',
166 'prvomajska',
167 'naroden front',
168 'hristo tatarchev',
169 'anton popov'
170 ])[floor(random()*5 + 1)]
171
172 when z.opstina = 'gjorce petrov' then
173 (array[
174 'gjorche petrov',
175 'partizanski odredi',
176 'makedonska vojska',
177 'luka gerov',
178 '4 juli'
179 ])[floor(random()*5 + 1)]
180
181 when z.opstina = 'chair' then
182 (array[
183 'krste misirkov',
184 'cvetan dimov',
185 'braka cvetanovi',
186 'kemal seyd',
187 'dizhonska'
188 ])[floor(random()*5 + 1)]
189
190 when z.opstina = 'butel' then
191 (array[
192 'butelska',
193 'boca ivanova',
194 'ferid murad',
195 'kemal seyd',
196 'radishanska'
197 ])[floor(random()*5 + 1)]
198
199 when z.opstina = 'gazi baba' then
200 (array[
201 'aleksandar makedonski',
202 'pero nakov',
203 'finlandska',
204 '16 makedonska brigada',
205 'blagoja stefkovski'
206 ])[floor(random()*5 + 1)]
207
208 when z.opstina = 'saraj' then
209 (array[
210 'saraj',
211 'lokalna glavna',
212 '1',
213 '2',
214 '3'
215 ])[floor(random()*5 + 1)]
216
217 when z.opstina = 'shuto orizari' then
218 (array[
219 'indira gandhi',
220 'nov zhivot',
221 'shuto orizari',
222 'bosna i hercegovina',
223 'washingtonska'
224 ])[floor(random()*5 + 1)]
225
226 when z.opstina = 'ilinden' then
227 (array[
228 'glavna',
229 'industriska',
230 '8',
231 'marshal tito',
232 '9'
233 ])[floor(random()*5 + 1)]
234
235 when z.opstina = 'petrovec' then
236 (array[
237 'glavna',
238 'marshal tito',
239 'petrovec',
240 '1',
241 '2'
242 ])[floor(random()*5 + 1)]
243
244 when z.opstina = 'sopishte' then
245 (array[
246 'sopishte',
247 'glavna',
248 'sonce',
249 '1',
250 '2'
251 ])[floor(random()*5 + 1)]
252
253 when z.opstina = 'studenichani' then
254 (array[
255 'glavna',
256 'studenichani',
257 '1',
258 '2',
259 '3'
260 ])[floor(random()*5 + 1)]
261
262 when z.opstina = 'skopje' then
263 (array[
264 'partizanski odredi',
265 'jane sandanski',
266 'goce delchev',
267 'boris trajkovski',
268 'krste misirkov'
269 ])[floor(random()*5 + 1)]
270
271 when z.opstina = 'kumanovo' then
272 (array[
273 'oktomvriska revolucija',
274 'treta makedonska udarna brigada',
275 '11 oktomvri',
276 'done bozhinov',
277 'pero chakar'
278 ])[floor(random()*5 + 1)]
279
280 when z.opstina = 'tetovo' then
281 (array[
282 'ilirija',
283 'blagoja toska',
284 'marshal tito',
285 'vidoe smilevski bato',
286 'goce delchev'
287 ])[floor(random()*5 + 1)]
288
289 when z.opstina = 'bitola' then
290 (array[
291 'shirok sokak',
292 'partizanska',
293 'marsal tito',
294 '4 noemvri',
295 '8 septemvri'
296 ])[floor(random()*5 + 1)]
297
298 when z.opstina = 'ohrid' then
299 (array[
300 'kej makedonija',
301 'partizanska',
302 'turistichka',
303 'dejan vojvoda',
304 'dimitar vlahov'
305 ])[floor(random()*5 + 1)]
306
307 when z.opstina = 'prilep' then
308 (array[
309 'goce delchev',
310 'partizanska',
311 'aleksa shantij',
312 'borsa',
313 '11 oktomvri'
314 ])[floor(random()*5 + 1)]
315
316 when z.opstina = 'strumica' then
317 (array[
318 'marshal tito',
319 'goce delchev',
320 'leninova',
321 '24 oktomvri',
322 'braka miladinovi'
323 ])[floor(random()*5 + 1)]
324
325 when z.opstina = 'veles' then
326 (array[
327 'blagoj gjorev',
328 '8 septemvri',
329 'marshal tito',
330 'dimitar vlahov',
331 'kocho racin'
332 ])[floor(random()*5 + 1)]
333
334 when z.opstina = 'kavadarci' then
335 (array[
336 '7 septemvri',
337 'strasho pindzur',
338 'marshal tito',
339 'disanska',
340 'edvard kardelj'
341 ])[floor(random()*5 + 1)]
342
343 when z.opstina = 'negotino' then
344 (array[
345 'marshal tito',
346 'partizanska',
347 'industriska',
348 'goce delchev',
349 '11 oktomvri'
350 ])[floor(random()*5 + 1)]
351
352 when z.opstina = 'gostivar' then
353 (array[
354 'braka gjinoski',
355 'ilindenska',
356 'marshal tito',
357 'sedec',
358 'goce delchev'
359 ])[floor(random()*5 + 1)]
360
361 when z.opstina = 'struga' then
362 (array[
363 'marshal tito',
364 'proleterski brigadi',
365 'kej 8 noemvri',
366 'vlado maleski',
367 'partizanska'
368 ])[floor(random()*5 + 1)]
369
370 when z.opstina = 'gevgelija' then
371 (array[
372 'marshal tito',
373 '7 noemvri',
374 'industriska',
375 'goce delchev',
376 'partizanska'
377 ])[floor(random()*5 + 1)]
378
379 when z.opstina = 'kocani' then
380 (array[
381 'dimitar vlahov',
382 'marshal tito',
383 'strasho erbenov',
384 'goce delchev',
385 'kej na revolucijata'
386 ])[floor(random()*5 + 1)]
387
388 when z.opstina = 'vinica' then
389 (array[
390 'marshal tito',
391 'partizanska',
392 'ilindenska',
393 'goce delchev',
394 '8 septemvri'
395 ])[floor(random()*5 + 1)]
396
397 else 'glavna'
398 end as ulica,
399
400 round((41.2 + random() * 1.2)::numeric, 6) as latitude,
401 round((20.4 + random() * 2.4)::numeric, 6) as longitude
402
403from Zona z;
404
405insert into Lokacija (zona_id, ulica, latitude, longitude)
406select z.zona_id,
407 case
408 when z.opstina = 'centar' then
409 (array [
410 'dimitrie chupovski',
411 'makedonija',
412 'partizanski odredi',
413 'mitropolit teodosij gologanov',
414 'goce delchev'
415 ])[floor(random() * 5 + 1)]
416
417 when z.opstina = 'aerodrom' then
418 (array [
419 'jane sandanski',
420 'srbija',
421 'vasil karangeleski',
422 'treta makedonska brigada',
423 'video smilevski bato'
424 ])[floor(random() * 5 + 1)]
425
426 when z.opstina = 'karpos' then
427 (array [
428 'partizanski odredi',
429 'ilindenska',
430 'nikola tesla',
431 'moskovska',
432 'kozle'
433 ])[floor(random() * 5 + 1)]
434
435 when z.opstina = 'kisela voda' then
436 (array [
437 'boris trajkovski',
438 'prvomajska',
439 'naroden front',
440 'hristo tatarchev',
441 'anton popov'
442 ])[floor(random() * 5 + 1)]
443
444 when z.opstina = 'gjorce petrov' then
445 (array [
446 'gjorche petrov',
447 'partizanski odredi',
448 'makedonska vojska',
449 'luka gerov',
450 '4 juli'
451 ])[floor(random() * 5 + 1)]
452
453 when z.opstina = 'chair' then
454 (array [
455 'krste misirkov',
456 'cvetan dimov',
457 'braka cvetanovi',
458 'kemal seyd',
459 'dizhonska'
460 ])[floor(random() * 5 + 1)]
461
462 when z.opstina = 'butel' then
463 (array [
464 'butelska',
465 'boca ivanova',
466 'ferid murad',
467 'kemal seyd',
468 'radishanska'
469 ])[floor(random() * 5 + 1)]
470
471 when z.opstina = 'gazi baba' then
472 (array [
473 'aleksandar makedonski',
474 'pero nakov',
475 'finlandska',
476 '16 makedonska brigada',
477 'blagoja stefkovski'
478 ])[floor(random() * 5 + 1)]
479
480 when z.opstina = 'saraj' then
481 (array [
482 'saraj',
483 'lokalna glavna',
484 '1',
485 '2',
486 '3'
487 ])[floor(random() * 5 + 1)]
488
489 when z.opstina = 'shuto orizari' then
490 (array [
491 'indira gandhi',
492 'nov zhivot',
493 'shuto orizari',
494 'bosna i hercegovina',
495 'washingtonska'
496 ])[floor(random() * 5 + 1)]
497
498 when z.opstina = 'ilinden' then
499 (array [
500 'glavna',
501 'industriska',
502 '8',
503 'marshal tito',
504 '9'
505 ])[floor(random() * 5 + 1)]
506
507 when z.opstina = 'petrovec' then
508 (array [
509 'glavna',
510 'marshal tito',
511 'petrovec',
512 '1',
513 '2'
514 ])[floor(random() * 5 + 1)]
515
516 when z.opstina = 'sopishte' then
517 (array [
518 'sopishte',
519 'glavna',
520 'sonce',
521 '1',
522 '2'
523 ])[floor(random() * 5 + 1)]
524
525 when z.opstina = 'studenichani' then
526 (array [
527 'glavna',
528 'studenichani',
529 '1',
530 '2',
531 '3'
532 ])[floor(random() * 5 + 1)]
533
534 when z.opstina = 'skopje' then
535 (array [
536 'partizanski odredi',
537 'jane sandanski',
538 'goce delchev',
539 'boris trajkovski',
540 'krste misirkov'
541 ])[floor(random() * 5 + 1)]
542
543 when z.opstina = 'kumanovo' then
544 (array [
545 'oktomvriska revolucija',
546 'treta makedonska udarna brigada',
547 '11 oktomvri',
548 'done bozhinov',
549 'pero chakar'
550 ])[floor(random() * 5 + 1)]
551
552 when z.opstina = 'tetovo' then
553 (array [
554 'ilirija',
555 'blagoja toska',
556 'marshal tito',
557 'vidoe smilevski bato',
558 'goce delchev'
559 ])[floor(random() * 5 + 1)]
560
561 when z.opstina = 'bitola' then
562 (array [
563 'shirok sokak',
564 'partizanska',
565 'marsal tito',
566 '4 noemvri',
567 '8 septemvri'
568 ])[floor(random() * 5 + 1)]
569
570 when z.opstina = 'ohrid' then
571 (array [
572 'kej makedonija',
573 'partizanska',
574 'turistichka',
575 'dejan vojvoda',
576 'dimitar vlahov'
577 ])[floor(random() * 5 + 1)]
578
579 when z.opstina = 'prilep' then
580 (array [
581 'goce delchev',
582 'partizanska',
583 'aleksa shantij',
584 'borsa',
585 '11 oktomvri'
586 ])[floor(random() * 5 + 1)]
587
588 when z.opstina = 'strumica' then
589 (array [
590 'marshal tito',
591 'goce delchev',
592 'leninova',
593 '24 oktomvri',
594 'braka miladinovi'
595 ])[floor(random() * 5 + 1)]
596
597 when z.opstina = 'veles' then
598 (array [
599 'blagoj gjorev',
600 '8 septemvri',
601 'marshal tito',
602 'dimitar vlahov',
603 'kocho racin'
604 ])[floor(random() * 5 + 1)]
605
606 when z.opstina = 'kavadarci' then
607 (array [
608 '7 septemvri',
609 'strasho pindzur',
610 'marshal tito',
611 'disanska',
612 'edvard kardelj'
613 ])[floor(random() * 5 + 1)]
614
615 when z.opstina = 'negotino' then
616 (array [
617 'marshal tito',
618 'partizanska',
619 'industriska',
620 'goce delchev',
621 '11 oktomvri'
622 ])[floor(random() * 5 + 1)]
623
624 when z.opstina = 'gostivar' then
625 (array [
626 'braka gjinoski',
627 'ilindenska',
628 'marshal tito',
629 'sedec',
630 'goce delchev'
631 ])[floor(random() * 5 + 1)]
632
633 when z.opstina = 'struga' then
634 (array [
635 'marshal tito',
636 'proleterski brigadi',
637 'kej 8 noemvri',
638 'vlado maleski',
639 'partizanska'
640 ])[floor(random() * 5 + 1)]
641
642 when z.opstina = 'gevgelija' then
643 (array [
644 'marshal tito',
645 '7 noemvri',
646 'industriska',
647 'goce delchev',
648 'partizanska'
649 ])[floor(random() * 5 + 1)]
650
651 when z.opstina = 'kocani' then
652 (array [
653 'dimitar vlahov',
654 'marshal tito',
655 'strasho erbenov',
656 'goce delchev',
657 'kej na revolucijata'
658 ])[floor(random() * 5 + 1)]
659
660 when z.opstina = 'vinica' then
661 (array [
662 'marshal tito',
663 'partizanska',
664 'ilindenska',
665 'goce delchev',
666 '8 septemvri'
667 ])[floor(random() * 5 + 1)]
668
669 else 'glavna'
670 end as ulica,
671
672 round((41.2 + random() * 1.2)::numeric, 6) as latitude,
673 round((20.4 + random() * 2.4)::numeric, 6) as longitude
674
675from Zona z;
676
677
678-- ===================== TIPKAMERA =====================
679insert into TipKamera (ime, opis, max_opseg, multifunkcionalna)
680values ('speed camera', 'kamera za detekcija na prekoracena brzina', 300, false),
681 ('red light camera', 'kamera za detekcija na pominuvanje na crveno svetlo', 150, false),
682 ('surveillance camera', 'kamera za opshto video nadgleduvanje na raskrsnici i javni povrshini', 200, false),
683 ('anpr camera', 'kamera za avtomatsko prepoznavanje na registraciski tablichki', 100, true),
684 ('multifunction traffic camera', 'kamera shto kombinira brzina, tablichki i opsto nadgleduvanje', 350, true);
685
686
687-- ===================== KAMERA =====================
688insert into Kamera (datum_instalacija, status, lokacija_id, tip_kamera_id)
689select current_date - ((random() * 1500)::int) as datum_instalacija,
690
691 case
692 when random() < 0.90 then 'aktivna'
693 else 'neaktivna'
694 end as status,
695
696 l.lokacija_id,
697
698 case
699 when z.ime like 'avtopat zona%' then 1
700 when z.ime like 'uchilishna zona%' then
701 case
702 when random() < 0.7 then 1
703 else 3
704 end
705 when z.ime like 'peshachka zona%' then 3
706 when z.ime like 'centralna zona%' then
707 case
708 when random() < 0.6 then 3
709 else 2
710 end
711 when z.ime like 'komercijalna zona%' then 3
712 when z.ime like 'industriska zona%' then 3
713 when z.ime like 'bolnichka zona%' then 3
714 when z.ime like 'rezidentska zona%' then 3
715 when z.ime like 'rekreativna zona%' then 3
716 when z.ime like 'turistichka zona%' then 3
717 end as tip_kamera_id
718
719from Lokacija l
720 join Zona z on l.zona_id = z.zona_id;
721
722
723-- ===================== GRAGJANIN =====================
724ALTER TABLE Gragjanin
725ADD COLUMN pol CHAR(1);
726
727ALTER TABLE Gragjanin
728ADD CONSTRAINT CHK_Gragjanin_Pol
729CHECK (pol IN ('M', 'F'));
730
731WITH female_data AS (
732
733 SELECT
734 embg,
735
736ARRAY[
737'Ана','Марија','Јована','Тамара','Елена','Александра','Ивана','Сара','Мила','Теодора',
738'Софија','Хана','Нина','Мартина','Ангела','Кристина','Тијана','Леона','Катерина','Симона',
739'Бојана','Даниела','Моника','Лара','Илина','Ирена','Јасмина','Маја','Михаела','Анастасија',
740'Емилија','Викторија','Лидија','Теа','Фатиме','Емина','Аделина','Анита','Анкица','Антонија',
741'Билјана','Бисера','Благица','Борјана','Вера','Вероника','Весна','Виолета','Гордана','Дара',
742'Даринка','Деница','Десислава','Димитра','Дина','Драгица','Есма','Жаклина','Жанета','Живка',
743'Злата','Зорица','Зорка','Ивона','Искра','Јана','Јелена','Јорданка','Калина','Кети',
744'Клара','Лила','Лилјана','Лина','Марина','Марта','Матеја','Меланија','Мирјана','Наде',
745'Надежда','Оливера','Павлинка','Петра','Радмила','Радослава','Рената','Роза','Росица','Сашка',
746'Светлана','Силвана','Славица','Стефанија','Сузана','Тања','Фани','Флора','Христина','Агнеса',
747'Адела','Адријана','Алма','Амела','Аница','Анѓела','Арбена','Асја','Бадија','Бети',
748'Бисерка','Бранка','Валентина','Васка','Василија','Ведрана','Вилма','Габи',
749'Галина','Галена','Глорија','Деа','Дивна','Добрилка','Доротеа','Ева','Евгенија',
750'Едита','Екатерина','Елисавета','Елма','Елмира','Емина','Ерика','Ермира',
751'Жана','Здравка','Златка','Ива','Ина','Илина','Инес','Ирма','Јагода','Јулија',
752'Камелија','Каролина','Касија','Клара','Кристина','Круна','Лана','Лејла','Ленче','Леонида',
753'Ливија','Љубица','Магдалена','Мариана','Марика','Мелиса','Мерита','Милена','Милка','Мирела',
754'Наташа','Невена','Нела','Нермина','Олга','Орхидеја','Паола','Пепа','Перса',
755'Рајна','Рамона','Ребека','Рина','Сабина','Сања','Селма','Силвија','Снежана','Соња',
756'Спомена','Стаменка','Тамина','Татјана','Тереза','Тина','Убавка','Фиданка','Хелена','Цвета',
757'Цветанка','Џенита','Шпреса','Аурора','Блажа','Ведрана','Габијела',
758'Елвира','Жанин','Инеса','Луна','Мирна','Нора','Офелија',
759'Роксана','Сандра','Сузи','Фемија','Хатиџе','Цеца','Џулија','Шејла','Симона' , 'Миланка' ,'Оља' ,'Бојана' , 'Миа' ,'Делфина' ,'Гордана' ,'Христина' , 'Мариа' ,'Марија' ,'Нена' ,'Иларија' , 'Тамара' , 'Дона' , 'Мила' ,'Љубка' , 'Леона'
760]
761 AS female_names,
762
763ARRAY[
764'Иванова','Петрова','Стојанова','Николова','Јованова','Георгиева','Димитрова','Ангелова','Костова','Тодорова',
765'Ристова','Спасова','Милева','Трајкова','Илиева','Попова','Стефанова','Павлова','Маркова','Кирева',
766'Атанасова','Богданова','Величкова','Митрева','Цветкова','Христова','Лазарова','Георгиевска','Колева','Димкова',
767'Младеновска','Наумова','Панова','Радева','Савева','Симонова','Тасевска','Узунова','Филиповска','Чолакова',
768'Шопова','Арсова','Бајрамов','Блажевска','Василева','Гаврилова','Давидова','Ефтимова','Живкова','Златева','Јакимова','Караџова','Личеноска','Малиновска','Неделкова','Огненовска','Пандевска','Рангелова','Серафимова',
769'Темелкова','Ќосевска','Апостолова','Бошковска','Велјаноска','Глигоровска','Деспотовска','Жупанова','Захариева',
770'Иваноска','Јанкуловска','Караманова','Лозановска','Маневска','Наковска','Орданоска','Пешевска','Ристовска','Соколовска',
771'Тренчевска','Угриновска','Фиданоска','Цековска','Чупеска','Шуманова','Абдулаи','Адеми','Алиу','Бајрами',
772'Беќири','Бериша','Весели','Даути','Имери','Исмаили','Красниќи','Мустафа','Османи','Реџепи',
773'Салиу','Сулејмани','Таири','Фејзула','Хасани','Шаќири','Адемовска','Бајрамовска','Бектеши','Билалова',
774'Велиу','Гаши','Даути','Емини','Зеќири','Ибраими','Јусуфи','Кадриу','Лимани','Мемети',
775'Нухиу','Османова','Пајазити','Рамадани','Селими','Тахири','Усеини','Фетаи','Халили','Џафери',
776'Шабани','Андоновска','Бошњакова','Вељковска','Грујовска','Доневска','Евтимова','Жежовска','Зографска','Игњатова',
777'Јосифовска','Китаноска','Луковска','Мицевска','Несторова','Петрушевска','Ризова','Славевска','Томовска','Урошевска',
778'Фрчковска','Цветаноска','Чадиковска','Шутевска','Алексова','Бабунска','Вангеловска','Глигоровска','Дамјановска','Еленовска',
779'Живановска','Здравковска','Илиевска','Јовановска','Караџовска','Лазаревска','Митановска','Николовска','Оровчанец','Павлеска',
780'Ристеска','Стојческа','Тодоровска','Цветковска','Чуповска','Асанова','Јанчева', 'Пецуровски', 'Данчевска'
781] AS female_surnames,
782
783 abs(hashtext(embg)::bigint) AS h
784
785 FROM gragjanin
786
787 -- FEMALE
788 WHERE substring(embg from 10 for 3)::int >= 500
789)
790
791UPDATE gragjanin g
792SET
793 ime =
794 fd.female_names[
795 (fd.h % array_length(fd.female_names, 1)) + 1
796 ],
797
798 prezime =
799 fd.female_surnames[
800 ((fd.h / 17) % array_length(fd.female_surnames, 1)) + 1
801 ]
802
803FROM female_data fd
804WHERE g.embg = fd.embg;
805
806WITH male_data AS (
807 SELECT
808 embg,
809ARRAY[
810'Александар','Андреј','Антонио','Бојан','Виктор','Владимир','Горан','Давид','Дејан','Димитар',
811'Драган','Емил','Иван','Игор','Јован','Кристијан','Лазар','Леон','Марко','Мартин',
812'Никола','Огнен','Павле','Петар','Стефан','Трајан','Филип','Христијан','Али','Самир',
813'Аце','Благој','Васко','Глигор','Дане','Ернест','Жарко','Зоран','Иле','Јордан',
814'Кирил','Љупчо','Миле','Наум','Орце','Пеце','Раде','Сашо','Тони','Урош',
815'Филипче','Цане','Чеде','Џељал','Аднан','Арбен','Бесим','Влатко','Гоце',
816'Горанче','Дамјан','Димче','Елвир','Живко','Здравко','Ибрахим','Јане','Кочо','Круме',
817'Лазе','Марио','Ненад','Орхан','Перо','Ристо','Слободан','Томе','Ќамил','Фарук',
818'Цветан','Џеват','Шенол','Андон','Бобан','Ване','Гаврил','Драги','Енес','Златко','Илија','Јовица','Костадин','Лукас','Мирко','Никче','Оливер','Панче',
819'Роберт','Симеон','Тихомир','Ќире','Филипе','Цветко','Чедомир','Џанер','Шефкет','Алекс',
820'Алек','Ангел','Бојче','Венко','Глигор','Доне','Елтон','Жељко','Зоки','Игорче',
821'Јоце','Кире','Љубе','Митко','Никодин','Омер','Павел','Ранко','Славе','Трајче',
822'Цветанче','Џевдет','Шакир','Агим','Бајрам','Веби','Гзим','Јусуф','Кадри','Љатиф','Мемет','Неџат','Осман',
823'Рамадан','Сеад','Таир','Урим','Фадил','Хасан','Џевад','Шабан','Ацо','Бранко',
824'Викентиј','Глигориј','Доне','Ефтим','Жарко','Зоки','Илчо','Јованче','Кирче',
825'Милан','Огнен','Петар','Ратко','Станко','Трајко','Киро','Фоте','Цане',
826'Чедомир','Џоле','Андреа','Бојанче','Велко','Горанчо','Димо','Ермал',
827'Златан','Јусуф','Кире','Лазар','Мирослав','Николче','Ордан','Ристе', 'Ристо', 'Дарио', 'Саше','Томислав','Ќемал','Филипчо','Цанко',
828'Валон','Гани','Дритон','Ервин','Зоранчо','Илми','Јахја','Кастриот',
829'Борис','Видое','Гавро','Дончо','Евгениј','Жаре','Злате','Ице',
830'Јаким','Калин','Лазо','Мартин','Петре','Раде','Сотир','Трајче',
831'Урош','Цветко','Џино','Шенко','Ариф','Бакир','Вебијан','Гафур',
832'Ване','Горан','Диме','Елдар','Желимир','Зоран','Јордан','Кирил','Лука',
833'Милан','Огнен','Петко','Радован','Славко','Тихо','Кирил', 'Димитар', 'Ненад', 'Давид', 'Стефан', 'Саве', 'Влатко', 'Дарко', 'Иван', 'Игор', 'Јован', 'Спасе', 'Тони', 'Адам', 'Никола', 'Матеј', 'Марко', 'Ѓорѓи', 'Филип', 'Борјан', 'Максим', 'Милан', 'Симон', 'Давор', 'Леонид', 'Бошко', 'Костадин', 'Живко', 'Панче', 'Андреј', 'Бобан'
834 ]
835
836 AS male_names,
837
838 ARRAY[
839'Иванов','Петров','Стојанов','Николов','Јованов','Георгиев','Димитров','Ангелов','Костов','Тодоров',
840'Ристов','Спасов','Милев','Трајков','Илиев','Попов','Стефанов','Павлов','Марков','Кирев',
841'Атанасов','Богданов','Величков','Митрев','Цветков','Христов','Лазаров','Георгиевски','Колев','Димков',
842'Младеновски','Наумов','Панов','Радев','Савев','Симонов','Тасевски','Узунов','Филиповски','Чолаков',
843'Шопов','Арсов','Бајрамов','Блажевски','Василев','Гаврилов','Давидов','Ефтимов','Живков','Златев',
844'Исаев','Јакимов','Караџов','Личеноски','Малиновски','Неделков','Огненовски','Пандевски','Рангелов','Серафимов',
845'Темелков','Ќосевски','Апостолов','Бошковски','Велјановски','Глигоровски','Деспотовски','Елезовски','Жупанов','Захариев',
846'Иваноски','Јанкуловски','Караманов','Лозановски','Маневски','Наковски','Орданоски','Пешевски','Ристовски','Соколовски',
847'Тренчевски','Угриновски','Фиданоски','Цековски','Чупески','Шуманов','Абдулаи','Адеми','Алиу','Бајрами',
848'Беќири','Бериша','Весели','Даути','Османи','Реџепи',
849'Рамадани','Селими','Тахири','Усеини','Бошњаков',
850'Вељковски','Грујовски','Доневски','Евтимов','Жежовски','Зографски','Игњатов','Јосифовски','Китаноски','Луковски',
851'Мицевски','Несторов','Петрушевски','Ризов','Славевски','Томовски','Урошевски','Фрчковски','Цветаноски','Чадиковски',
852'Шутевски','Алексов','Бабунски','Вангеловски','Дамјановски','Еленовски','Живановски','Здравковски','Илиевски','Јовановски',
853'Караџовски','Лазаревски','Митановски','Николовски','Павлески','Ристески','Стојчески','Тодоровски','Фотевски','Цветковски',
854'Чуповски','Шумански','Асанов','Бејтула','Велков','Георгиевски','Деспотски','Ефтимовски','Живковски','Златков',
855'Илиоски','Јакимоски','Калинов','Лазароски','Милошевски','Никодинов','Орданов','Петков','Радески','Станков',
856'Трајчески','Урошев','Филипов','Цанев','Јанчев',
857'Реџепов',
858'Сабри','Тефик','Улви','Фатон','Хамди','Шкелзен','Алексиев','Борисов','Видоев','Гавров',
859'Дончев','Евгениев', 'Јорданов','Калиновски','Лазов','Мартинов','Неделчев','Петрески','Сотиров'
860] AS male_surnames,
861
862 abs(hashtext(embg)::bigint) AS h
863
864 FROM gragjanin
865
866 -- mashki EMBG
867 WHERE substring(embg from 10 for 3)::int < 500
868)
869
870UPDATE gragjanin g
871SET
872 ime =
873 md.male_names[
874 (md.h % array_length(md.male_names, 1)) + 1
875 ],
876
877 prezime =
878 md.male_surnames[
879 ((md.h / 17) % array_length(md.male_surnames, 1)) + 1
880 ]
881
882FROM male_data md
883WHERE g.embg = md.embg;
884
885
886-- ===================== SOPSTVENIK =====================
887INSERT INTO Sopstvenik (embg)
888SELECT embg
889FROM Gragjanin
890ORDER BY random()
891LIMIT 1200350;
892
893
894-- ===================== VOZACKA =====================
895INSERT INTO Vozacka
896(vozacki_broj, datum_izdavanje, datum_istekuvanje, status, embg)
897SELECT
898 'MK-' || LPAD(row_number() OVER ()::TEXT, 7, '0') AS vozacki_broj,
899 datum_izdavanje,
900 datum_istekuvanje,
901
902 CASE
903 WHEN rn <= 0.93 THEN 'aktivna'
904 WHEN rn <= 0.96 THEN 'istecena'
905 ELSE 'suspendirana'
906 END AS status,
907
908 embg
909
910FROM
911(
912 SELECT
913 embg,
914 datum_izdavanje,
915
916 datum_izdavanje +
917 CASE
918 WHEN random() < 0.5 THEN INTERVAL '5 years'
919 ELSE INTERVAL '10 years'
920 END AS datum_istekuvanje,
921
922 random() AS rn
923
924 FROM
925 (
926 SELECT
927 s.embg,
928
929 (
930 (g.datum_ragjanje + INTERVAL '18 years')::DATE
931 +
932 FLOOR(
933 random() *
934 (
935 CURRENT_DATE
936 - (g.datum_ragjanje + INTERVAL '18 years')::DATE
937 )
938 )::INT
939 ) AS datum_izdavanje
940
941 FROM Sopstvenik s
942 JOIN Gragjanin g
943 ON s.embg = g.embg
944
945 WHERE
946 g.datum_ragjanje <= CURRENT_DATE - INTERVAL '18 years'
947
948 ORDER BY random()
949 LIMIT 900000
950 ) x
951) y;
952
953
954-- ===================== KATEGORIJA =====================
955INSERT INTO Kategorija (ime, opis)
956VALUES
957('AM', 'Kategorija za upravuvanje so mopedi, skuteri i lesni motorni vozila so pomala zafatnina i ogranicena maksimalna brzina. Ovaa kategorija najchesto se koristi za gradski prevoz i pochetno iskustvo vo soobrakjajot.'),
958
959('A1', 'Kategorija za upravuvanje so lesni motocikli do 125 cm3 i odredena maksimalna mokjnost. Nameneta e za vozachi koi pocnuvaat so upravuvanje motocikli so pomala sila i polesna kontrola.'),
960
961('A2', 'Kategorija za motocikli so sredna mokjnost i pogolema zafatnina, nameneta za vozachi so prethodno iskustvo i podgotvenost za upravuvanje posilni motorni vozila.'),
962
963('A', 'Kategorija za upravuvanje so site tipovi motocikli bez ogranicuvanje na mokjnost ili zafatnina. Ovaa kategorija ovozmozhuva upravuvanje so sportski i profesionalni motocikli.'),
964
965('B1', 'Kategorija za polesni chetiricikli i kompaktni motorni vozila nameneti za gradska upotreba i prevoz na mal broj patnici.'),
966
967('B', 'Najchesto koristena kategorija koja ovozmozhuva upravuvanje so patnichki avtomobili do odredena maksimalna masa i vozila nameneti za sekojdneven privaten ili sluzhben prevoz.'),
968
969('BE', 'Kategorija koja ovozmozhuva upravuvanje so vozila od kategorija B vo kombinacija so prikolka ili dopolnitelno prikacheno vozilo so pogolema masa.'),
970
971('C1', 'Kategorija za upravuvanje so polesni tovarni vozila i kamioni so sredna nosivost, nameneti za lokalen i regionalen transport na roba.'),
972
973('C1E', 'Kategorija za polesni tovarni vozila od C1 vo kombinacija so prikolka, najchesto koristena vo komercijalen i distributiven transport.'),
974
975('C', 'Kategorija za upravuvanje so teski tovarni vozila i kamioni nameneti za profesionalen transport na roba na podolgi relacii.'),
976
977('CE', 'Kategorija za teski tovarni vozila vo kombinacija so golemi prikolki i shleperi, nameneta za profesionalni vozachi vo megjunaroden i industriski transport.'),
978
979('D1', 'Kategorija za upravuvanje so pomali avtobusi i kombinja nameneti za prevoz na pomal broj patnici vo lokalen ili privaten transport.'),
980
981('D1E', 'Kategorija za pomali avtobusi vo kombinacija so prikolka ili dopolnitelna oprema za transport.'),
982
983('D', 'Kategorija za upravuvanje so avtobusi i drugi vozila nameneti za javen i organiziran prevoz na pogolem broj patnici.'),
984
985('DE', 'Kategorija za avtobusi so golema prikolka ili dopolnitelen transporten kapacitet, najchesto koristena vo profesionalen transport.'),
986
987('F', 'Kategorija za upravuvanje so traktori, zemjodelski mashini i drugi rabotni vozila koi se koristat vo zemjodelstvo i industrija.'),
988
989('G', 'Kategorija za specijalni rabotni mashini, gradezhna mehanizacija i terenski vozila nameneti za profesionalna upotreba vo posebni uslovi.');
990
991
992-- ===================== VOZACKA_KATEGORIJA =====================
993INSERT INTO Vozacka_Kategorija (vozacka_id, kategorija_id)
994-- сите имаат B
995SELECT v.vozacka_id, k.kategorija_id
996FROM Vozacka v
997 JOIN Kategorija k ON k.ime = 'B'
998
999UNION
1000
1001-- 25% имаат AM
1002SELECT v.vozacka_id, k.kategorija_id
1003FROM Vozacka v
1004 JOIN Kategorija k ON k.ime = 'AM'
1005WHERE random() < 0.25
1006
1007UNION
1008
1009-- 8% имаат A1
1010SELECT v.vozacka_id, k.kategorija_id
1011FROM Vozacka v
1012 JOIN Sopstvenik s ON v.embg = s.embg
1013 JOIN Gragjanin g ON s.embg = g.embg
1014 JOIN Kategorija k ON k.ime = 'A1'
1015WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 16
1016 AND random() < 0.08
1017
1018UNION
1019
1020-- 5% имаат A2
1021SELECT v.vozacka_id, k.kategorija_id
1022FROM Vozacka v
1023 JOIN Sopstvenik s ON v.embg = s.embg
1024 JOIN Gragjanin g ON s.embg = g.embg
1025 JOIN Kategorija k ON k.ime = 'A2'
1026WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 18
1027 AND random() < 0.05
1028
1029UNION
1030
1031-- 4% имаат A
1032SELECT v.vozacka_id, k.kategorija_id
1033FROM Vozacka v
1034 JOIN Sopstvenik s ON v.embg = s.embg
1035 JOIN Gragjanin g ON s.embg = g.embg
1036 JOIN Kategorija k ON k.ime = 'A'
1037WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 24
1038 AND random() < 0.04
1039
1040UNION
1041
1042-- 10% имаат BE
1043SELECT v.vozacka_id, k.kategorija_id
1044FROM Vozacka v
1045 JOIN Kategorija k ON k.ime = 'BE'
1046WHERE random() < 0.10
1047
1048UNION
1049
1050-- 6% имаат C
1051SELECT v.vozacka_id, k.kategorija_id
1052FROM Vozacka v
1053 JOIN Sopstvenik s ON v.embg = s.embg
1054 JOIN Gragjanin g ON s.embg = g.embg
1055 JOIN Kategorija k ON k.ime = 'C'
1056WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 21
1057 AND random() < 0.06
1058
1059UNION
1060
1061-- 2% имаат D
1062SELECT v.vozacka_id, k.kategorija_id
1063FROM Vozacka v
1064 JOIN Sopstvenik s ON v.embg = s.embg
1065 JOIN Gragjanin g ON s.embg = g.embg
1066 JOIN Kategorija k ON k.ime = 'D'
1067WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 24
1068 AND random() < 0.02
1069
1070UNION
1071
1072-- 4% имаат F
1073SELECT v.vozacka_id, k.kategorija_id
1074FROM Vozacka v
1075 JOIN Kategorija k ON k.ime = 'F'
1076WHERE random() < 0.04
1077
1078UNION
1079
1080-- 3% имаат G
1081SELECT v.vozacka_id, k.kategorija_id
1082FROM Vozacka v
1083 JOIN Kategorija k ON k.ime = 'G'
1084WHERE random() < 0.03;
1085
1086
1087-- ===================== TIP =====================
1088INSERT INTO Tip (ime)
1089VALUES
1090('Sedan'),
1091('Hecbek'),
1092('Karavan'),
1093('Kupe'),
1094('Kabriolet'),
1095('SUV'),
1096('Krosover'),
1097('Pikap'),
1098('Miniven'),
1099('Kombi'),
1100('Van'),
1101('Limuzina'),
1102('Sportski avtomobil'),
1103('Elektrichen avtomobil'),
1104('Hibriden avtomobil'),
1105('Gradski avtomobil'),
1106('Terensko vozilo'),
1107('Kamion'),
1108('Shleper'),
1109('Avtobus'),
1110('Minibus'),
1111('Motocikl'),
1112('Skuter'),
1113('Moped'),
1114('Traktor'),
1115('Rabotna mashina'),
1116('Policisko vozilo'),
1117('Ambulantno vozilo'),
1118('Protivpozharno vozilo');
1119
1120
1121-- ===================== VOZILO =====================
1122ALTER TABLE vozilo
1123ADD COLUMN registarska_oznaka VARCHAR(20);
1124
1125ALTER TABLE vozilo
1126ADD CONSTRAINT UQ_Vozilo_Registarska
1127UNIQUE (registarska_oznaka);
1128
1129ALTER TABLE vozilo
1130ADD CONSTRAINT CHK_Vozilo_Registarska
1131CHECK (
1132 registarska_oznaka IS NULL
1133 OR TRIM(registarska_oznaka) <> ''
1134);
1135
1136ALTER TABLE vozilo
1137ADD COLUMN boja VARCHAR(50);
1138
1139ALTER TABLE vozilo
1140ADD CONSTRAINT CHK_Vozilo_Boja
1141CHECK (
1142 boja IS NULL
1143 OR TRIM(boja) <> ''
1144);
1145
1146INSERT INTO vozilo
1147(marka, broj_sasija, model, registarska_oznaka, boja, tip_id)
1148
1149WITH params AS (
1150 SELECT 1400000 AS broj_vozila
1151),
1152
1153generated AS (
1154 SELECT
1155 gs AS rn,
1156 random() AS r
1157 FROM params, generate_series(1, params.broj_vozila) gs
1158),
1159
1160chosen_tip AS (
1161 SELECT
1162 rn,
1163 CASE
1164 WHEN r < 0.18 THEN 'Hecbek'
1165 WHEN r < 0.34 THEN 'Sedan'
1166 WHEN r < 0.47 THEN 'SUV'
1167 WHEN r < 0.57 THEN 'Krosover'
1168 WHEN r < 0.64 THEN 'Karavan'
1169 WHEN r < 0.70 THEN 'Gradski avtomobil'
1170 WHEN r < 0.76 THEN 'Kombi'
1171 WHEN r < 0.81 THEN 'Van'
1172 WHEN r < 0.86 THEN 'Dostavno vozilo'
1173 WHEN r < 0.90 THEN 'Kamion'
1174 WHEN r < 0.925 THEN 'Shleper'
1175 WHEN r < 0.945 THEN 'Motocikl'
1176 WHEN r < 0.960 THEN 'Skuter'
1177 WHEN r < 0.972 THEN 'Avtobus'
1178 WHEN r < 0.982 THEN 'Minibus'
1179 WHEN r < 0.988 THEN 'Policisko vozilo'
1180 WHEN r < 0.993 THEN 'Ambulantno vozilo'
1181 WHEN r < 0.996 THEN 'Protivpozharno vozilo'
1182 WHEN r < 0.998 THEN 'Diplomatsko vozilo'
1183 ELSE 'Rabotna mashina'
1184 END AS tip_ime
1185 FROM generated
1186),
1187
1188with_tip AS (
1189 SELECT
1190 ct.rn,
1191 ct.tip_ime,
1192 t.tip_id
1193 FROM chosen_tip ct
1194 JOIN tip t ON t.ime = ct.tip_ime
1195),
1196
1197vehicle_data AS (
1198 SELECT
1199 wt.rn,
1200 wt.tip_ime,
1201 wt.tip_id,
1202 ROW_NUMBER() OVER (PARTITION BY wt.tip_ime ORDER BY wt.rn) AS type_seq,
1203
1204 CASE
1205 WHEN wt.tip_ime IN ('Hecbek','Sedan','SUV','Krosover','Karavan','Gradski avtomobil') THEN
1206 (ARRAY[
1207 'Volkswagen|Golf',
1208 'Volkswagen|Passat',
1209 'Opel|Astra',
1210 'Opel|Corsa',
1211 'Toyota|Corolla',
1212 'Toyota|Yaris',
1213 'Ford|Focus',
1214 'Renault|Clio',
1215 'Peugeot|308',
1216 'Skoda|Octavia',
1217 'Hyundai|Tucson',
1218 'Kia|Sportage',
1219 'Dacia|Duster',
1220 'Audi|A4',
1221 'BMW|320'
1222 ])[(floor(random()*15)+1)::int]
1223
1224 WHEN wt.tip_ime IN ('Kombi','Van','Dostavno vozilo') THEN
1225 (ARRAY[
1226 'Volkswagen|Transporter',
1227 'Ford|Transit',
1228 'Mercedes-Benz|Sprinter',
1229 'Fiat|Ducato',
1230 'Renault|Master',
1231 'Mercedes-Benz|Vito'
1232 ])[(floor(random()*6)+1)::int]
1233
1234 WHEN wt.tip_ime IN ('Kamion','Shleper') THEN
1235 (ARRAY[
1236 'MAN|TGX',
1237 'Mercedes-Benz|Actros',
1238 'Volvo|FH',
1239 'Scania|R Series',
1240 'DAF|XF',
1241 'Iveco|Stralis'
1242 ])[(floor(random()*6)+1)::int]
1243
1244 WHEN wt.tip_ime IN ('Avtobus','Minibus') THEN
1245 (ARRAY[
1246 'Mercedes-Benz|Tourismo',
1247 'Setra|S415',
1248 'MAN|Lion City',
1249 'Iveco|Daily Bus',
1250 'Isuzu|Novo',
1251 'Neoplan|Cityliner'
1252 ])[(floor(random()*6)+1)::int]
1253
1254 WHEN wt.tip_ime IN ('Motocikl','Skuter') THEN
1255 (ARRAY[
1256 'Yamaha|MT-07',
1257 'Honda|CBR',
1258 'Suzuki|Burgman',
1259 'Kawasaki|Ninja',
1260 'Piaggio|Liberty',
1261 'Vespa|LX'
1262 ])[(floor(random()*6)+1)::int]
1263
1264 WHEN wt.tip_ime = 'Policisko vozilo' THEN
1265 (ARRAY[
1266 'Skoda|Octavia Police',
1267 'Volkswagen|Passat Police',
1268 'Ford|Transit Police',
1269 'BMW|X5 Police'
1270 ])[(floor(random()*4)+1)::int]
1271
1272 WHEN wt.tip_ime = 'Ambulantno vozilo' THEN
1273 (ARRAY[
1274 'Mercedes-Benz|Sprinter Ambulance',
1275 'Volkswagen|Crafter Ambulance',
1276 'Fiat|Ducato Ambulance'
1277 ])[(floor(random()*3)+1)::int]
1278
1279 WHEN wt.tip_ime = 'Protivpozharno vozilo' THEN
1280 (ARRAY[
1281 'MAN|Fire Truck',
1282 'Mercedes-Benz|Atego Fire',
1283 'Iveco|Eurocargo Fire'
1284 ])[(floor(random()*3)+1)::int]
1285
1286 WHEN wt.tip_ime = 'Diplomatsko vozilo' THEN
1287 (ARRAY[
1288 'Mercedes-Benz|E-Class Diplomatic',
1289 'BMW|5 Series Diplomatic',
1290 'Audi|A6 Diplomatic'
1291 ])[(floor(random()*3)+1)::int]
1292
1293 ELSE
1294 (ARRAY[
1295 'Caterpillar|Excavator',
1296 'JCB|Backhoe Loader',
1297 'Komatsu|Bulldozer',
1298 'Liebherr|Crane'
1299 ])[(floor(random()*4)+1)::int]
1300 END AS marka_model
1301 FROM with_tip wt
1302)
1303
1304SELECT
1305 split_part(marka_model, '|', 1) AS marka,
1306
1307 (10000000000000000 + rn)::BIGINT AS broj_sasija,
1308
1309 split_part(marka_model, '|', 2) AS model,
1310
1311 CASE
1312 WHEN tip_ime = 'Diplomatsko vozilo' THEN
1313 LPAD(((type_seq - 1) % 90 + 10)::TEXT, 2, '0')
1314 || '-CD-' ||
1315 LPAD(type_seq::TEXT, 5, '0')
1316
1317 WHEN tip_ime = 'Policisko vozilo' THEN
1318 'POL-' || LPAD(type_seq::TEXT, 7, '0')
1319
1320 WHEN tip_ime = 'Ambulantno vozilo' THEN
1321 'AMB-' || LPAD(type_seq::TEXT, 7, '0')
1322
1323 WHEN tip_ime = 'Protivpozharno vozilo' THEN
1324 'FIR-' || LPAD(type_seq::TEXT, 7, '0')
1325
1326 ELSE
1327 (ARRAY[
1328 'SK','BT','KU','TE','GV','OH','VE','ST','PR','PP','SR','KI','KO','KA',
1329 'BE','DE','NE','RE','SN','VI','VV','DB','DK','MB','KR','PS','VA','DH',
1330 'KS','PE','GE','KP','RA','SU'
1331 ])[((rn - 1) % 34 + 1)]
1332 ||
1333 LPAD((((rn - 1) / 34) % 10000)::TEXT, 4, '0')
1334 ||
1335 (ARRAY['A','B','C','E','H','J','K','M','N','P','R','S','T','V','Z'])
1336 [(((rn - 1) / (34 * 10000)) % 15 + 1)]
1337 ||
1338 (ARRAY['A','B','C','E','H','J','K','M','N','P','R','S','T','V','Z'])
1339 [(((rn - 1) / (34 * 10000 * 15)) % 15 + 1)]
1340 END AS registarska_oznaka,
1341
1342 (ARRAY['Bela','Crna','Siva','Srebrna','Crvena','Sina','Zelena','Kafeava','Zolta'])
1343 [(floor(random()*9)+1)::int] AS boja,
1344
1345 tip_id
1346FROM vehicle_data;
1347
1348ALTER TABLE Vozilo
1349ADD CONSTRAINT UQ_Vozilo_BrojSasija UNIQUE (broj_sasija);
1350
1351
1352-- ===================== KORISNIK =====================
1353ALTER TABLE Korisnik
1354ALTER COLUMN embg TYPE VARCHAR(13);
1355
1356INSERT INTO Korisnik (mail, hashed_password, embg)
1357SELECT
1358 LOWER(
1359 CASE
1360 WHEN rn = 1 THEN latin_ime || '.' || latin_prezime
1361 WHEN rn = 2 THEN latin_ime || '_' || latin_prezime
1362 WHEN rn = 3 THEN latin_prezime || '.' || latin_ime
1363 WHEN rn = 4 THEN latin_prezime || '_' || latin_ime
1364 ELSE latin_ime || '.' || latin_prezime || rn::TEXT
1365 END
1366 || '@' ||
1367 CASE (floor(random() * 8))::INT
1368 WHEN 0 THEN 'gmail.com'
1369 WHEN 1 THEN 'yahoo.com'
1370 WHEN 2 THEN 'hotmail.com'
1371 WHEN 3 THEN 'outlook.com'
1372 WHEN 4 THEN 'icloud.com'
1373 WHEN 5 THEN 'live.com'
1374 WHEN 6 THEN 'aol.com'
1375 ELSE 'mail.com'
1376 END
1377 ) AS mail,
1378
1379 (
1380 CHR(65 + FLOOR(random() * 26)::INT)
1381 || SUBSTRING(md5(random()::TEXT) FROM 1 FOR 6)
1382 || FLOOR(random() * 90 + 10)::TEXT
1383 || CASE (floor(random() * 6))::INT
1384 WHEN 0 THEN '!'
1385 WHEN 1 THEN '@'
1386 WHEN 2 THEN '#'
1387 WHEN 3 THEN '$'
1388 WHEN 4 THEN '%'
1389 ELSE '&'
1390 END
1391 ) AS hashed_password,
1392
1393 embg
1394FROM
1395(
1396 SELECT
1397 x.*,
1398 ROW_NUMBER() OVER (
1399 PARTITION BY latin_ime, latin_prezime
1400 ORDER BY embg
1401 ) AS rn
1402 FROM
1403 (
1404 SELECT
1405 g.embg,
1406
1407 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
1408 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
1409 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
1410 REPLACE(LOWER(g.ime),
1411 'а','a'),'б','b'),'в','v'),'г','g'),'д','d'),'ѓ','gj'),
1412 'е','e'),'ж','zh'),'з','z'),'ѕ','dz'),'и','i'),'ј','j'),
1413 'к','k'),'л','l'),'љ','lj'),'м','m'),'н','n'),'њ','nj'),
1414 'о','o'),'п','p'),'р','r'),'с','s'),'т','t'),'ќ','kj'),
1415 'у','u'),'ф','f'),'х','h'),'ц','c'),'ч','ch'),'џ','dj'),'ш','sh') AS latin_ime,
1416
1417 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
1418 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
1419 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
1420 REPLACE(LOWER(g.prezime),
1421 'а','a'),'б','b'),'в','v'),'г','g'),'д','d'),'ѓ','gj'),
1422 'е','e'),'ж','zh'),'з','z'),'ѕ','dz'),'и','i'),'ј','j'),
1423 'к','k'),'л','l'),'љ','lj'),'м','m'),'н','n'),'њ','nj'),
1424 'о','o'),'п','p'),'р','r'),'с','s'),'т','t'),'ќ','kj'),
1425 'у','u'),'ф','f'),'х','h'),'ц','c'),'ч','ch'),'џ','dj'),'ш','sh') AS latin_prezime
1426
1427 FROM Gragjanin g
1428 WHERE AGE(CURRENT_DATE, g.datum_ragjanje) >= INTERVAL '18 years'
1429 ) x
1430) t
1431
1432ON CONFLICT (mail) DO NOTHING;
1433
1434
1435-- ===================== ADMINISTRATOR =====================
1436ALTER TABLE Administrator
1437ADD CONSTRAINT UQ_Administrator_BrojNaLicenca UNIQUE (broj_na_licenca);
1438
1439INSERT INTO Administrator
1440(
1441 datum_vrabotuvanje,
1442 broj_na_licenca,
1443 oddel,
1444 korisnik_id
1445)
1446SELECT
1447 CURRENT_DATE - ((random() * 3650)::int) AS datum_vrabotuvanje,
1448
1449 100000 + ROW_NUMBER() OVER (ORDER BY k.korisnik_id) AS broj_na_licenca,
1450
1451 CASE (random() * 4)::int
1452 WHEN 0 THEN 'Traffic Monitoring'
1453 WHEN 1 THEN 'Camera Control'
1454 WHEN 2 THEN 'Violation Review'
1455 WHEN 3 THEN 'Penalty Management'
1456 ELSE 'System Administration'
1457 END AS oddel,
1458
1459
1460 k.korisnik_id
1461FROM Korisnik k
1462ORDER BY random()
1463LIMIT (
1464 SELECT GREATEST(1, COUNT(*) * 2 / 100)
1465 FROM Korisnik
1466);
1467
1468WITH numbered AS
1469(
1470 SELECT
1471 administrator_id,
1472 ROW_NUMBER() OVER (ORDER BY administrator_id) AS rn
1473 FROM Administrator
1474)
1475
1476UPDATE Administrator a
1477SET oddel =
1478 CASE ((n.rn - 1) % 5)
1479 WHEN 0 THEN 'Traffic Monitoring'
1480 WHEN 1 THEN 'Camera Control'
1481 WHEN 2 THEN 'Violation Review'
1482 WHEN 3 THEN 'Penalty Management'
1483 ELSE 'System Administration'
1484 END
1485FROM numbered n
1486WHERE a.administrator_id = n.administrator_id;
1487
1488
1489-- ===================== ULOGA =====================
1490INSERT INTO Uloga (ime)
1491VALUES
1492 ('Administrator'),
1493 ('Korisnik');
1494
1495
1496-- ===================== KORISNIK_ULOGA =====================
1497INSERT INTO Korisnik_Uloga (korisnik_id, uloga_id)
1498
1499SELECT
1500 k.korisnik_id,
1501
1502 CASE
1503 WHEN random() < 0.20 THEN
1504 (SELECT uloga_id
1505 FROM Uloga
1506 WHERE lower(ime) = 'administrator')
1507
1508 ELSE
1509 (SELECT uloga_id
1510 FROM Uloga
1511 WHERE lower(ime) = 'korisnik')
1512 END
1513
1514FROM Korisnik k
1515
1516ON CONFLICT DO NOTHING;
1517
1518
1519-- ===================== MREZHNAKONEKCIJA =====================
1520INSERT INTO MrezhnaKonekcija (ime, opis)
1521VALUES
1522 ('Ethernet', 'Zicna LAN konekcija za stabilen prenos na podatoci'),
1523 ('Fiber Optic', 'Opticka mreza so visok bandwidth za HD video streaming'),
1524 ('4G LTE', 'Mobilna mreza za kameri na oddalecheni lokacii'),
1525 ('5G', 'Brza mobilna mreza za real-time video nadzor'),
1526 ('WiFi', 'Bezicna lokalna mreza za komunikacija na uredite'),
1527 ('VPN', 'Bezbedna kriptirana mrezna konekcija'),
1528 ('Wireless Bridge', 'Bezicno povrzuvanje pomegju dve lokacii'),
1529 ('Satellite', 'Satelitska konekcija za ruralni i oddalecheni oblasti'),
1530 ('Municipal LAN', 'Opshtinska lokalna mreza za javni sistemi'),
1531 ('Police Secure Network', 'Interna policiska bezbedna mreza'),
1532 ('Traffic Control Network', 'Mreza za kontrola i monitoring na soobrakaj'),
1533 ('Camera Backbone Fiber', 'Centralna opticka infrastruktura za kameri'),
1534 ('Hybrid Network', 'Kombinacija od fiber i mobilna mreza'),
1535 ('Emergency Response VPN', 'VPN mreza za итни sluzhbi'),
1536 ('Smart City Grid', 'Integrirana mreza za smart city infrastruktura'),
1537 ('Public Transport Network', 'Mreza za monitoring na javniot prevoz'),
1538 ('Tunnel Monitoring Network', 'Specijalizirana mreza za tunelski nadzor'),
1539 ('Bridge Surveillance Link', 'Mrezna konekcija za monitoring na mostovi'),
1540 ('Parking Monitoring LAN', 'Lokalna mreza za parking sistemi'),
1541 ('AI Video Analytics Fiber', 'Opticka mreza za AI analiza na video'),
1542 ('Red Light Camera LTE', 'LTE mreza za kameri na semafori'),
1543 ('Highway Surveillance Wireless', 'Bezicna mreza za avtopatski kameri'),
1544 ('Border Security Network', 'Mreza za granichna bezbednost'),
1545 ('Drone Communication Link', 'Mreza za komunikacija so dronovi'),
1546 ('Environmental Sensor Network', 'Mreza za senzori i kameri'),
1547 ('Facial Recognition Network', 'Mreza za AI sistemi za prepoznavanje'),
1548 ('Real-Time Monitoring Fiber', 'Opticka mreza za monitoring vo realno vreme'),
1549 ('Backup LTE Connection', 'Rezervna mobilna konekcija pri prekin'),
1550 ('Secure Government LAN', 'Bezbedna mreza za drzhavni sistemi'),
1551 ('Integrated Surveillance Network', 'Centralizirana mreza za video nadzor');
1552
1553
1554-- ===================== MREZHNAKONEKCIJAKAMERA =====================
1555INSERT INTO MrezhnaKonekcijaKamera (mrezna_konekcija_id, kamera_id)
1556SELECT
1557 mk.mrezna_konekcija_id,
1558 k.kamera_id
1559FROM Kamera k
1560JOIN LATERAL (
1561 SELECT mrezna_konekcija_id
1562 FROM MrezhnaKonekcija
1563 WHERE ime =
1564 CASE
1565 WHEN k.kamera_id % 100 < 30 THEN 'Fiber Optic'
1566 WHEN k.kamera_id % 100 < 50 THEN '4G LTE'
1567 WHEN k.kamera_id % 100 < 65 THEN '5G'
1568 WHEN k.kamera_id % 100 < 78 THEN 'Ethernet'
1569 WHEN k.kamera_id % 100 < 88 THEN 'VPN'
1570 WHEN k.kamera_id % 100 < 94 THEN 'WiFi'
1571 WHEN k.kamera_id % 100 < 98 THEN 'Wireless Bridge'
1572 ELSE 'Satellite'
1573 END
1574 LIMIT 1
1575) mk ON true
1576ON CONFLICT DO NOTHING;
1577
1578INSERT INTO MrezhnaKonekcijaKamera (mrezna_konekcija_id, kamera_id)
1579SELECT
1580 mk.mrezna_konekcija_id,
1581 k.kamera_id
1582FROM Kamera k
1583JOIN LATERAL (
1584 SELECT mrezna_konekcija_id
1585 FROM MrezhnaKonekcija
1586 WHERE ime =
1587 CASE
1588 WHEN k.kamera_id % 100 < 40 THEN 'Backup LTE Connection'
1589 WHEN k.kamera_id % 100 < 70 THEN 'VPN'
1590 WHEN k.kamera_id % 100 < 90 THEN 'Police Secure Network'
1591 ELSE 'Satellite'
1592 END
1593 LIMIT 1
1594) mk ON true
1595WHERE k.kamera_id % 10 IN (0, 3, 7)
1596ON CONFLICT DO NOTHING;
1597
1598
1599-- ===================== SNIMKA =====================
1600ALTER TABLE Snimka
1601ADD COLUMN arhivirana BOOLEAN DEFAULT FALSE;
1602
1603ALTER TABLE Snimka
1604ADD COLUMN datum_arhiviranje DATE;
1605
1606ALTER TABLE Snimka
1607ADD CONSTRAINT CHK_Snimka_Arhiviranje
1608CHECK (
1609 datum_arhiviranje IS NULL
1610 OR datum_arhiviranje >= datum
1611);
1612
1613INSERT INTO Snimka
1614(
1615 datum,
1616 url_adresa,
1617 arhivirana,
1618 datum_arhiviranje,
1619 kamera_id
1620)
1621WITH kameri AS (
1622 SELECT
1623 kamera_id,
1624 datum_instalacija,
1625 ROW_NUMBER() OVER (ORDER BY kamera_id) AS rn,
1626 COUNT(*) OVER () AS total_kameri
1627 FROM Kamera
1628 WHERE datum_instalacija IS NOT NULL
1629),
1630generated AS (
1631 SELECT
1632 gs AS unique_id,
1633 k.kamera_id,
1634 k.datum_instalacija,
1635
1636 (
1637 k.datum_instalacija
1638 + ((random() * GREATEST(1, CURRENT_DATE - k.datum_instalacija))::int)
1639 )::date AS datum,
1640
1641 CASE
1642 WHEN random() < 0.35 THEN TRUE
1643 ELSE FALSE
1644 END AS arhivirana
1645
1646 FROM generate_series(1, 5000000) gs
1647 JOIN kameri k
1648 ON k.rn = ((gs - 1) % k.total_kameri) + 1
1649)
1650SELECT
1651 datum,
1652
1653 'https://safecity.mk/snimki/kamera_' || kamera_id ||
1654 '/datum_' || TO_CHAR(datum, 'YYYY_MM_DD') ||
1655 '/snimka_' || unique_id ||
1656 '_' || md5(kamera_id::text || '_' || unique_id::text || '_' || datum::text) ||
1657 '.mp4' AS url_adresa,
1658
1659 arhivirana,
1660
1661 CASE
1662 WHEN arhivirana = TRUE
1663 THEN LEAST(CURRENT_DATE, datum + ((random() * 60)::int))
1664 ELSE NULL
1665 END AS datum_arhiviranje,
1666
1667 kamera_id
1668FROM generated;
1669
1670
1671-- ===================== SLIKA =====================
1672INSERT INTO Slika
1673(
1674 url,
1675 format,
1676 golemina,
1677 datum_kreiranje,
1678 snimka_id
1679)
1680SELECT
1681 'https://safecity.mk/sliki/snimka_' || x.snimka_id ||
1682 '/slika_' || x.broj_slika ||
1683 '_' || md5(x.snimka_id::text || '_' || x.broj_slika::text || '_' || x.datum::text) ||
1684 '.' || x.format AS url,
1685
1686 x.format,
1687 x.golemina,
1688 x.datum AS datum_kreiranje,
1689 x.snimka_id
1690FROM
1691(
1692 SELECT
1693 s.snimka_id,
1694 s.datum,
1695 gs AS broj_slika,
1696
1697 CASE ((s.snimka_id % 5))
1698 WHEN 0 THEN 'jpg'
1699 WHEN 1 THEN 'jpeg'
1700 WHEN 2 THEN 'png'
1701 WHEN 3 THEN 'bmp'
1702 ELSE 'gif'
1703 END AS format,
1704
1705 100000 + (random() * 4900000)::int AS golemina
1706
1707 FROM Snimka s
1708 JOIN Kamera k
1709 ON s.kamera_id = k.kamera_id
1710 CROSS JOIN generate_series(1, 2) gs
1711 WHERE s.datum >= k.datum_instalacija
1712) x;
1713
1714
1715-- ===================== PREGLEDSNIMKA =====================
1716INSERT INTO PregledSnimka
1717(
1718 administrator_id,
1719 snimka_id,
1720 status
1721)
1722SELECT
1723 a.administrator_id,
1724 s.snimka_id,
1725 CASE
1726 WHEN random() < 0.15 THEN 0
1727 WHEN random() < 0.75 THEN 1
1728 ELSE 2
1729 END AS status
1730FROM
1731(
1732 SELECT
1733 snimka_id,
1734 ROW_NUMBER() OVER (ORDER BY snimka_id) AS rn
1735 FROM Snimka
1736) s
1737JOIN
1738(
1739 SELECT
1740 administrator_id,
1741 ROW_NUMBER() OVER (ORDER BY administrator_id) AS rn,
1742 COUNT(*) OVER () AS total_admins
1743 FROM Administrator
1744) a
1745ON ((s.rn - 1) % a.total_admins) + 1 = a.rn
1746ON CONFLICT (administrator_id, snimka_id) DO NOTHING;
1747
1748
1749-- ===================== TIPPREKRSOK =====================
1750INSERT INTO TipPrekrsok (ime, iznos)
1751VALUES
1752('Prekoracena brzina do 10 km/h', 50),
1753('Prekoracena brzina od 10 do 30 km/h', 150),
1754('Prekoracena brzina nad 30 km/h', 300),
1755('Ekstremno prekoracena brzina nad 50 km/h', 600),
1756('Pominuvanje na crveno svetlo', 250),
1757('Pominuvanje na zholto svetlo pri zabrana', 120),
1758('Nepropisno parkiranje', 80),
1759('Parkiranje na invalidsko mesto', 250),
1760('Parkiranje na peshachki premin', 180),
1761('Parkiranje na avtobuska stanica', 200),
1762('Koristenje mobilen telefon pri vozenje', 120),
1763('Pisuvanje poraki pri vozenje', 180),
1764('Nekoristenje sigurnosen pojas', 100),
1765('Nekoristenje kaciga na motocikl', 150),
1766('Vozenje bez vozacka dozvola', 500),
1767('Vozenje so nesoodvetna kategorija', 400),
1768('Vozenje so istechena registracija', 200),
1769('Vozenje bez registracija', 500),
1770('Vozenje pod dejstvo na alkohol', 600),
1771('Vozenje pod dejstvo na narkotici', 800),
1772('Nepochtuvanje prvenstvo na minuvanje', 180),
1773('Nepropisno preticanje', 220),
1774('Vozenje vo zabraneta nasoka', 300),
1775('Vozenje vo zabraneta zona', 250),
1776('Nepochtuvanje peshachki premin', 200),
1777('Zagrozuvanje na peshaci', 350),
1778('Nepropisno svrtuvanje', 100),
1779('Nepropisno prestrojuvanje', 120),
1780('Prevoz na pogolem broj patnici od dozvolenoto', 140),
1781('Neispravni svetla', 70),
1782('Neispravni stop svetla', 80),
1783('Neispravni migavci', 60),
1784('Neispravni sopirachki', 350),
1785('Tehnichki neispravno vozilo', 400),
1786('Prekomerna buchava od vozilo', 90),
1787('Nelegalni temni stakla', 130),
1788('Vozenje bez osiguruvanje', 250),
1789('Koristenje falsifikuvani registraciski oznaki', 1000),
1790('Neprijavena soobrakjajna nesrekja', 500),
1791('Nepropisno dvizhenje vo zholta lenta', 150),
1792('Nepochtuvanje policiski naredbi', 700),
1793('Beganje od policija', 1200),
1794('Nepropisno dvizhenje na motocikl', 130),
1795('Nepropisno transportiranje tovar', 260),
1796('Nepropisno vlechenje prikolka', 170),
1797('Preoptovareno tovarno vozilo', 450),
1798('Vozenje bez tablichki', 500),
1799('Prekrshuvanje policiski chas', 300),
1800('Driftanje i opasno vozenje', 700),
1801('Nepropisno vrtenje polukruzhno', 120),
1802('Blokiranje raskrsnica', 100),
1803('Nepropisno koristenje sirena', 50),
1804('Nepropisno koristenje rotacioni svetla', 900),
1805('Koristenje vozilo bez tehnichki pregled', 250),
1806('Nepropisno dvizhenje vo kruzhen tek', 110);
1807
1808
1809-- ===================== SOPSTVENIK_VOZILO =====================
1810ALTER TABLE Sopstvenik_Vozilo
1811DROP CONSTRAINT FK_SV_Sopstvenik;
1812
1813ALTER TABLE Sopstvenik_Vozilo
1814ALTER COLUMN embg TYPE CHAR(13)
1815USING embg::CHAR(13);
1816
1817ALTER TABLE Sopstvenik_Vozilo
1818ADD CONSTRAINT FK_SV_Sopstvenik
1819FOREIGN KEY (embg)
1820REFERENCES Sopstvenik(embg)
1821ON DELETE CASCADE
1822ON UPDATE CASCADE;
1823
1824INSERT INTO Sopstvenik_Vozilo (embg, vozilo_id)
1825
1826SELECT
1827 s.embg,
1828 v.vozilo_id
1829
1830FROM
1831(
1832 SELECT
1833 embg,
1834 row_number() OVER (ORDER BY random()) AS rn
1835 FROM Sopstvenik
1836) s
1837
1838JOIN
1839(
1840 SELECT
1841 vozilo_id,
1842 row_number() OVER (ORDER BY random()) AS rn
1843 FROM Vozilo
1844) v
1845
1846ON s.rn = v.rn
1847
1848ON CONFLICT DO NOTHING;
1849
1850
1851-- ===================== REGISTRACIJA =====================
1852INSERT INTO registracija
1853(broj, mesto, datum, datum_istekuvanje, sopstvenikembg, vozilovozilo_id)
1854WITH valid_owners AS (
1855 SELECT s.embg, ROW_NUMBER() OVER (ORDER BY s.embg) AS owner_rn
1856 FROM sopstvenik s
1857 JOIN gragjanin g ON g.embg = s.embg
1858 WHERE AGE(CURRENT_DATE, g.datum_ragjanje) >= INTERVAL '18 years'
1859),
1860owner_count AS (
1861 SELECT COUNT(*) AS total_owners FROM valid_owners
1862),
1863valid_vehicles AS (
1864 SELECT v.vozilo_id, v.registarska_oznaka, t.ime AS tip_ime,
1865 ROW_NUMBER() OVER (ORDER BY v.vozilo_id) AS vehicle_rn,
1866 random() AS r
1867 FROM vozilo v
1868 JOIN tip t ON t.tip_id = v.tip_id
1869 WHERE t.ime NOT IN ('Policisko vozilo','Ambulantno vozilo','Protivpozharno vozilo','Diplomatsko vozilo')
1870),
1871vehicle_registration_count AS (
1872 SELECT vv.*,
1873 CASE
1874 WHEN r < 0.50 THEN (4 + floor(random() * 7))::int
1875 WHEN r < 0.75 THEN 2
1876 WHEN r < 0.85 THEN 3
1877 ELSE 1
1878 END AS registration_count
1879 FROM valid_vehicles vv
1880),
1881expanded AS (
1882 SELECT vrc.vozilo_id, vrc.registarska_oznaka, vrc.vehicle_rn, vrc.registration_count, gs.reg_num
1883 FROM vehicle_registration_count vrc
1884 CROSS JOIN LATERAL generate_series(1, vrc.registration_count) gs(reg_num)
1885),
1886registrations AS (
1887 SELECT e.*,
1888 (CURRENT_DATE - ((e.registration_count - e.reg_num) * 365) - ((random() * 120)::int))::date AS datum_registracija,
1889 CASE
1890 WHEN random() < 0.70 THEN ((e.vehicle_rn * 13) % oc.total_owners) + 1
1891 ELSE ((e.vehicle_rn * 13 + e.reg_num * 97) % oc.total_owners) + 1
1892 END AS owner_pick
1893 FROM expanded e
1894 CROSS JOIN owner_count oc
1895),
1896with_owner AS (
1897 SELECT r.*, vo.embg
1898 FROM registrations r
1899 JOIN valid_owners vo ON vo.owner_rn = r.owner_pick
1900)
1901SELECT
1902 ROW_NUMBER() OVER () AS broj,
1903 CASE LEFT(registarska_oznaka, 2)
1904 WHEN 'SK' THEN 1 WHEN 'BT' THEN 2 WHEN 'KU' THEN 3 WHEN 'TE' THEN 4
1905 WHEN 'GV' THEN 5 WHEN 'OH' THEN 6 WHEN 'VE' THEN 7 WHEN 'ST' THEN 8
1906 WHEN 'PR' THEN 9 WHEN 'PP' THEN 10 WHEN 'SR' THEN 11 WHEN 'KI' THEN 12
1907 WHEN 'KO' THEN 13 WHEN 'KA' THEN 14
1908 ELSE ((vehicle_rn - 1) % 34) + 1
1909 END AS mesto,
1910 datum_registracija AS datum,
1911 (datum_registracija + INTERVAL '1 year')::date AS datum_istekuvanje,
1912 embg AS sopstvenikembg,
1913 vozilo_id AS vozilovozilo_id
1914FROM with_owner;
1915
1916
1917-- ===================== KAZNA =====================
1918ALTER TABLE Kazna
1919ADD COLUMN IF NOT EXISTS iznos_za_plakanje INTEGER;
1920
1921ALTER TABLE Kazna
1922ADD CONSTRAINT CHK_Kazna_Iznos
1923CHECK (iznos_za_plakanje IS NULL OR iznos_za_plakanje > 0);
1924
1925
1926-- ===================== NOTIFIKACIJA =====================
1927INSERT INTO Notifikacija
1928(
1929 sodrzina,
1930 slika_id,
1931 korisnik_id
1932)
1933SELECT
1934 CASE (random() * 7)::int
1935 WHEN 0 THEN
1936 'Ве известуваме дека е евидентирано надминување на дозволената брзина за 5 km/h.Ова известување претставува опомена и не е казна'
1937 WHEN 1 THEN
1938 'Ве известуваме дека е евидентирано надминување на дозволената брзина за 10 km/h.'
1939 WHEN 2 THEN
1940 'Ве известуваме дека е евидентирано надминување на дозволената брзина за 15 km/h.'
1941 WHEN 3 THEN
1942 'Ве известуваме дека е евидентирано користење мобилен телефон за време на управување со возило.'
1943 WHEN 4 THEN
1944 'Ве известуваме дека е евидентирано поминување на црвено светло.'
1945 WHEN 5 THEN
1946 'Ве известуваме дека е евидентирано непропуштање пешаци на пешачки премин.'
1947 END AS sodrzina,
1948
1949 s.slika_id,
1950
1951 k.korisnik_id
1952
1953FROM
1954(
1955 SELECT
1956 slika_id,
1957 ROW_NUMBER() OVER (ORDER BY slika_id) AS rn
1958 FROM Slika
1959) s
1960JOIN
1961(
1962 SELECT
1963 korisnik_id,
1964 ROW_NUMBER() OVER (ORDER BY korisnik_id) AS rn,
1965 COUNT(*) OVER () AS total_korisnici
1966 FROM Korisnik
1967) k
1968ON ((s.rn - 1) % k.total_korisnici) + 1 = k.rn;
1969
1970
1971-- ===================== PREKRSOK =====================
1972INSERT INTO Prekrsok
1973(
1974 opis,
1975 vreme,
1976 status,
1977 datum,
1978 tip_prekrsok_id,
1979 kamera_id,
1980 kazna_id
1981)
1982WITH
1983target AS (
1984 SELECT 5000000::bigint AS total_needed
1985),
1986current_count AS (
1987 SELECT COUNT(*)::bigint AS current_total
1988 FROM Prekrsok
1989),
1990to_insert AS (
1991 SELECT GREATEST(0, t.total_needed - c.current_total) AS rows_to_insert
1992 FROM target t
1993 CROSS JOIN current_count c
1994),
1995kazni AS (
1996 SELECT
1997 kazna_id,
1998 datum,
1999 status,
2000 ROW_NUMBER() OVER (ORDER BY kazna_id) AS rn,
2001 COUNT(*) OVER () AS total
2002 FROM Kazna
2003),
2004tipovi AS (
2005 SELECT
2006 tip_prekrsok_id,
2007 ime,
2008 ROW_NUMBER() OVER (ORDER BY tip_prekrsok_id) AS rn,
2009 COUNT(*) OVER () AS total
2010 FROM TipPrekrsok
2011),
2012kameri AS (
2013 SELECT
2014 kamera_id,
2015 datum_instalacija,
2016 ROW_NUMBER() OVER (ORDER BY kamera_id) AS rn,
2017 COUNT(*) OVER () AS total
2018 FROM Kamera
2019),
2020broevi AS (
2021 SELECT g.gs
2022 FROM to_insert ti
2023 CROSS JOIN generate_series(1, ti.rows_to_insert) AS g(gs)
2024)
2025SELECT
2026 'Evidentiran prekrsok od tip: ' || t.ime AS opis,
2027
2028 make_time(
2029 (6 + (b.gs % 17))::int,
2030 (b.gs % 60)::int,
2031 ((b.gs * 7) % 60)::int
2032 ) AS vreme,
2033
2034 k.status,
2035
2036 CASE
2037 WHEN k.datum < km.datum_instalacija THEN km.datum_instalacija
2038 ELSE k.datum
2039 END AS datum,
2040
2041 t.tip_prekrsok_id,
2042 km.kamera_id,
2043 k.kazna_id
2044FROM broevi b
2045JOIN kazni k
2046 ON k.rn = ((b.gs - 1) % k.total) + 1
2047JOIN tipovi t
2048 ON t.rn = ((b.gs - 1) % t.total) + 1
2049JOIN kameri km
2050 ON km.rn = ((b.gs - 1) % km.total) + 1;
2051
2052ALTER TABLE Prekrsok
2053ADD COLUMN detektirana_brzina INTEGER;
2054
2055UPDATE Prekrsok
2056SET detektirana_brzina = 40 + floor(random() * 100)::int
2057WHERE detektirana_brzina IS NULL;
2058
2059ALTER TABLE Prekrsok
2060ADD CONSTRAINT CHK_Prekrsok_Brzina
2061CHECK (detektirana_brzina IS NULL OR detektirana_brzina > 0);
2062
2063
2064-- ===================== PLAKANJE =====================
2065INSERT INTO Plakanje
2066(
2067 metod,
2068 datum,
2069 kazna_id
2070)
2071SELECT
2072 CASE (k.kazna_id % 3)
2073 WHEN 0 THEN 1
2074 WHEN 1 THEN 2
2075 ELSE 3
2076 END AS metod,
2077
2078 LEAST(
2079 CURRENT_DATE,
2080 COALESCE(k.datum, CURRENT_DATE) + ((k.kazna_id % 365)::int)
2081 ) AS datum,
2082
2083 k.kazna_id
2084FROM Kazna k
2085WHERE k.kazna_id <= 10000000
2086 AND NOT EXISTS (
2087 SELECT 1
2088 FROM Plakanje p
2089 WHERE p.kazna_id = k.kazna_id
2090 )
2091ORDER BY k.kazna_id
2092LIMIT 800000;
2093
2094
2095-- ===================== NOTIFIKACIJA_KAZNA =====================
2096INSERT INTO Notifikacija_Kazna
2097(
2098 notifikacija_id,
2099 kazna_id
2100)
2101SELECT
2102 n.notifikacija_id,
2103 k.kazna_id
2104FROM
2105(
2106 SELECT
2107 notifikacija_id,
2108 ROW_NUMBER() OVER (ORDER BY notifikacija_id) AS rn
2109 FROM Notifikacija
2110) n
2111JOIN
2112(
2113 SELECT
2114 kazna_id,
2115 ROW_NUMBER() OVER (ORDER BY kazna_id) AS rn
2116 FROM Kazna
2117) k
2118ON n.rn = k.rn
2119WHERE NOT EXISTS
2120(
2121 SELECT 1
2122 FROM Notifikacija_Kazna nk
2123 WHERE nk.notifikacija_id = n.notifikacija_id
2124 AND nk.kazna_id = k.kazna_id
2125);
2126
2127WITH novi_notifikacii AS
2128(
2129 SELECT
2130 notifikacija_id,
2131 ROW_NUMBER() OVER (ORDER BY notifikacija_id) AS rn
2132 FROM Notifikacija
2133),
2134stari_redovi AS
2135(
2136 SELECT
2137 nk.notifikacija_id AS old_notifikacija_id,
2138 nk.kazna_id,
2139 ROW_NUMBER() OVER (ORDER BY nk.kazna_id) AS rn
2140 FROM Notifikacija_Kazna nk
2141)
2142UPDATE Notifikacija_Kazna nk
2143SET notifikacija_id = nn.notifikacija_id
2144FROM novi_notifikacii nn
2145JOIN stari_redovi sr
2146 ON nn.rn = sr.rn
2147WHERE nk.notifikacija_id = sr.old_notifikacija_id
2148 AND nk.kazna_id = sr.kazna_id;
2149
2150
2151-- ===================== ZALBA =====================
2152INSERT INTO Zalba
2153(
2154 sodrzina,
2155 datum_na_podnesuvanje,
2156 status,
2157 administrator_id,
2158 korisnik_id,
2159 prekrsok_id
2160)
2161SELECT
2162 CASE (p.prekrsok_id % 6)
2163 WHEN 0 THEN 'Казната е погрешно евидентирана од камерата.'
2164 WHEN 1 THEN 'Возилото не било управувано од мене во тој момент.'
2165 WHEN 2 THEN 'Семафорот не функционирал правилно.'
2166 WHEN 3 THEN 'Износот на казната не е точен.'
2167 WHEN 4 THEN 'Барам дополнителна проверка на доказите.'
2168 ELSE 'Во записот за прекршокот има неточни информации.'
2169 END AS sodrzina,
2170
2171 LEAST(
2172 CURRENT_DATE,
2173 kz.datum + ((p.prekrsok_id % 15) + 1)::int
2174 ) AS datum_na_podnesuvanje,
2175
2176 CASE (p.prekrsok_id % 10)
2177 WHEN 0 THEN 'Одобрена'
2178 WHEN 1 THEN 'Одобрена'
2179 WHEN 2 THEN 'Во постапка'
2180 WHEN 3 THEN 'Во постапка'
2181 ELSE 'Одбиена'
2182 END AS status,
2183
2184 a.administrator_id,
2185
2186 kz.korisnik_id,
2187
2188 p.prekrsok_id
2189
2190FROM Prekrsok p
2191JOIN Kazna kz
2192 ON kz.kazna_id = p.kazna_id
2193
2194JOIN LATERAL (
2195 SELECT ad.administrator_id
2196 FROM Administrator ad
2197 ORDER BY ((ad.administrator_id + p.prekrsok_id) % 100000)
2198 LIMIT 1
2199) a ON true
2200
2201WHERE kz.korisnik_id IS NOT NULL
2202 AND NOT EXISTS (
2203 SELECT 1
2204 FROM Zalba z
2205 WHERE z.prekrsok_id = p.prekrsok_id
2206 )
2207
2208ORDER BY p.prekrsok_id
2209LIMIT 200000;
2210
2211WITH
2212admin_ids AS (
2213 SELECT array_agg(administrator_id) AS ids
2214 FROM administrator
2215),
2216korisnik_ids AS (
2217 SELECT array_agg(korisnik_id) AS ids
2218 FROM korisnik
2219),
2220prekrsok_ids AS (
2221 SELECT array_agg(prekrsok_id) AS ids
2222 FROM prekrsok
2223)
2224
2225INSERT INTO zalba (
2226 sodrzina,
2227 datum_na_podnesuvanje,
2228 status,
2229 administrator_id,
2230 korisnik_id,
2231 prekrsok_id
2232)
2233
2234SELECT
2235 CASE floor(random() * 6)::int
2236 WHEN 0 THEN 'Neosnovana zalba za prekrsok'
2237 WHEN 1 THEN 'Baranje za namaluvanje na kazna'
2238 WHEN 2 THEN 'Prigovor za pogresna identifikacija'
2239 WHEN 3 THEN 'Zalba za netocni podatoci'
2240 WHEN 4 THEN 'Baranje za povtorno razgleduvanje'
2241 ELSE 'Podnesena e oficijalna zalba'
2242 END,
2243
2244 CURRENT_DATE - ((random() * 1000)::int),
2245
2246 CASE floor(random() * 4)::int
2247 WHEN 0 THEN 'podnesena'
2248 WHEN 1 THEN 'vo_postapka'
2249 WHEN 2 THEN 'prifatena'
2250 ELSE 'odbijena'
2251 END,
2252
2253 admin_ids.ids[
2254 floor(random() * array_length(admin_ids.ids, 1) + 1)::int
2255 ],
2256
2257 korisnik_ids.ids[
2258 floor(random() * array_length(korisnik_ids.ids, 1) + 1)::int
2259 ],
2260
2261 prekrsok_ids.ids[
2262 floor(random() * array_length(prekrsok_ids.ids, 1) + 1)::int
2263 ]
2264
2265FROM generate_series(1, 7000000)
2266
2267CROSS JOIN admin_ids
2268CROSS JOIN korisnik_ids
2269CROSS JOIN prekrsok_ids;
2270
2271
2272-- ===================== PREKRSOK_VOZILO =====================
2273INSERT INTO Prekrsok_Vozilo (prekrsok_id, vozilo_id)
2274SELECT p.prekrsok_id, v.vozilo_id
2275FROM Prekrsok p
2276CROSS JOIN LATERAL (
2277 SELECT vozilo_id
2278 FROM Vozilo
2279 ORDER BY RANDOM()
2280 LIMIT 1
2281) v
2282ON CONFLICT (prekrsok_id, vozilo_id) DO NOTHING;
2283
2284
2285-- ===================== PREKRSOK_STORITEL =====================
2286ALTER TABLE Prekrsok_Storitel
2287ALTER COLUMN storitel_embg TYPE VARCHAR(13);
2288
2289INSERT INTO Prekrsok_Storitel (prekrsok_id, storitel_embg)
2290SELECT
2291 p.prekrsok_id,
2292 g.embg
2293FROM
2294(
2295 SELECT
2296 prekrsok_id,
2297 ROW_NUMBER() OVER (ORDER BY random()) AS rn
2298 FROM Prekrsok
2299) p
2300JOIN
2301(
2302 SELECT
2303 embg,
2304 ROW_NUMBER() OVER (ORDER BY random()) AS rn,
2305 COUNT(*) OVER () AS total
2306 FROM Gragjanin
2307) g
2308ON g.rn = ((p.rn - 1) % g.total) + 1
2309ON CONFLICT (prekrsok_id, storitel_embg) DO NOTHING;