Database Creation
Имплементациски детали
Во овој проект, преку DDL наредби се креирани сите потребни табели за системот за управување со такси апликација, како и нивните меѓусебни релации.
Во скриптите се опфатени:
- примарни клучеви (PRIMARY KEY)
- надворешни клучеви (FOREIGN KEY)
- ограничувања за интегритет (CHECK, UNIQUE, NOT NULL)
- правила за ажурирање и бришење (ON DELETE, ON UPDATE)
Со овие DDL наредби се обезбедува конзистентност, интегритет и правилна организација на податоците во базата.
DDL Скрипти
CREATE TABLE Status (
status_id SERIAL PRIMARY KEY,
status_tip VARCHAR(255) NOT NULL,
status_text VARCHAR(255) NOT NULL
);
CREATE TABLE Vehicles_model (
Vehicles_model_id SERIAL PRIMARY KEY,
Model VARCHAR(255) NOT NULL
);
CREATE TABLE Locations (
location_id SERIAL PRIMARY KEY,
latitude NUMERIC(9,6) NOT NULL,
longitude NUMERIC(9,6) NOT NULL,
grad VARCHAR(255) NOT NULL,
naselba VARCHAR(255) NOT NULL,
ulica VARCHAR(255) NOT NULL,
broj VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL
);
CREATE TABLE Users (
User_id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
username VARCHAR(255) NOT NULL UNIQUE,
phone VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
created_at TIMESTAMP NOT NULL,
status_id INT NOT NULL,
CONSTRAINT chk_users_phone CHECK (phone ~ '^[0-9+]+$'),
CONSTRAINT chk_users_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'),
CONSTRAINT FK_Users_Status
FOREIGN KEY (status_id) REFERENCES Status(status_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE TABLE Drivers (
driver_id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
phone VARCHAR(255) NOT NULL UNIQUE,
license_number VARCHAR(255) NOT NULL UNIQUE,
registration_date DATE NOT NULL,
rating NUMERIC(3,2) NOT NULL,
status_id INT NOT NULL,
CONSTRAINT chk_drivers_phone CHECK (phone ~ '^[0-9+]+$'),
CONSTRAINT chk_drivers_rating CHECK (rating BETWEEN 0 AND 5),
CONSTRAINT FK_Drivers_Status
FOREIGN KEY (status_id) REFERENCES Status(status_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE TABLE Admins (
admins_id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
username VARCHAR(255) NOT NULL UNIQUE,
phone VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
created_at TIMESTAMP NOT NULL,
status_id INT NOT NULL,
CONSTRAINT chk_admins_phone CHECK (phone ~ '^[0-9+]+$'),
CONSTRAINT chk_admins_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'),
CONSTRAINT FK_Admins_Status
FOREIGN KEY (status_id) REFERENCES Status(status_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE TABLE Vehicle_types (
vehicle_type_id SERIAL PRIMARY KEY,
type_name VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL,
Vehicles_model_id INT NOT NULL,
CONSTRAINT FK_Vehicle_types_Model
FOREIGN KEY (Vehicles_model_id)
REFERENCES Vehicles_model(Vehicles_model_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE TABLE Vehicle_ownership (
Vehicle_ownership_id SERIAL PRIMARY KEY,
Plate VARCHAR(255) NOT NULL UNIQUE,
Traffic_permit VARCHAR(255) NOT NULL UNIQUE,
Color VARCHAR(255) NOT NULL,
year INT NOT NULL,
vehicle_type_id INT NOT NULL,
CONSTRAINT chk_vehicle_year CHECK (year >= 1900),
CONSTRAINT FK_Vehicle_ownership_Type
FOREIGN KEY (vehicle_type_id)
REFERENCES Vehicle_types(vehicle_type_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE TABLE Pricing_rules (
tariff_id SERIAL PRIMARY KEY,
base_price NUMERIC(10,2) NOT NULL,
price_per_km NUMERIC(10,2) NOT NULL,
vehicle_type_id INT NOT NULL,
CONSTRAINT chk_price CHECK (base_price >= 0 AND price_per_km >= 0),
CONSTRAINT FK_Pricing_rules_Vehicle_types
FOREIGN KEY (vehicle_type_id)
REFERENCES Vehicle_types(vehicle_type_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE TABLE Drivers_Vehicle_ownership (
driver_id INT NOT NULL,
Vehicle_ownership_id INT NOT NULL,
PRIMARY KEY (driver_id, Vehicle_ownership_id),
CONSTRAINT FK_DVO_Drivers
FOREIGN KEY (driver_id)
REFERENCES Drivers(driver_id)
ON DELETE CASCADE,
CONSTRAINT FK_DVO_Vehicles
FOREIGN KEY (Vehicle_ownership_id)
REFERENCES Vehicle_ownership(Vehicle_ownership_id)
ON DELETE CASCADE
);
CREATE TABLE Active_drivers (
driver_id INT PRIMARY KEY,
current_location_id INT NOT NULL,
is_available BOOLEAN NOT NULL,
working_start TIMESTAMP NOT NULL,
working_end TIMESTAMP NOT NULL,
vehicle_ownership_id INT NOT NULL,
CONSTRAINT FK_Active_drivers_Drivers
FOREIGN KEY (driver_id)
REFERENCES Drivers(driver_id)
ON DELETE CASCADE,
CONSTRAINT FK_Active_drivers_Locations
FOREIGN KEY (current_location_id)
REFERENCES Locations(location_id)
ON DELETE RESTRICT,
CONSTRAINT FK_Active_drivers_vehicle_ownership_id
FOREIGN KEY (vehicle_ownership_id)
REFERENCES Vehicle_ownership(Vehicle_ownership_id)
ON DELETE RESTRICT
);
CREATE TABLE Rides (
ride_id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
driver_id INT NOT NULL,
vehicle_ownership_id INT NOT NULL,
pickup_location_id INT NOT NULL,
dropoff_location_id INT NOT NULL,
status_id INT NOT NULL,
request_time TIMESTAMP NOT NULL,
pickup_time TIMESTAMP NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL,
distance_km NUMERIC(6,2) NOT NULL,
final_price NUMERIC(10,2) NOT NULL,
discount_percentage NUMERIC(5,2) NOT NULL,
CONSTRAINT chk_distance CHECK (distance_km >= 0),
CONSTRAINT chk_discount CHECK (discount_percentage BETWEEN 0 AND 100),
CONSTRAINT FK_Rides_Users
FOREIGN KEY (user_id) REFERENCES Users(User_id) ON DELETE RESTRICT,
CONSTRAINT FK_Rides_vehicle_ownership_id
FOREIGN KEY (vehicle_ownership_id) REFERENCES Vehicle_ownership(Vehicle_ownership_id) ON DELETE RESTRICT,
CONSTRAINT FK_Rides_Drivers
FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE RESTRICT,
CONSTRAINT FK_Rides_Pickup_Location
FOREIGN KEY (pickup_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT,
CONSTRAINT FK_Rides_Dropoff_Location
FOREIGN KEY (dropoff_location_id) REFERENCES Locations(location_id) ON DELETE RESTRICT,
CONSTRAINT FK_Rides_Status
FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT
);
CREATE TABLE Payment_methods(
payment_method_id SERIAL PRIMARY KEY,
payment_tip VARCHAR(255) NOT NULL
);
CREATE TABLE Payments (
payment_id SERIAL PRIMARY KEY,
amount NUMERIC(10,2) NOT NULL,
payment_method_id INT NOT NULL,
payment_time TIMESTAMP NOT NULL,
ride_id INT NOT NULL,
status_id INT NOT NULL,
CONSTRAINT chk_amount CHECK (amount >= 0),
CONSTRAINT FK_Payments_Rides
FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,
CONSTRAINT FK_Payments_Status
FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT,
CONSTRAINT FK_Payments_Method
FOREIGN KEY (payment_method_id) REFERENCES Payment_methods(payment_method_id) ON DELETE RESTRICT
);
CREATE TABLE Ratings (
rating_id SERIAL PRIMARY KEY,
score INT NOT NULL,
comment VARCHAR(255),
created_at TIMESTAMP NOT NULL,
ride_id INT NOT NULL,
User_id INT NOT NULL,
driver_id INT NOT NULL,
CONSTRAINT chk_score CHECK (score BETWEEN 1 AND 5),
CONSTRAINT FK_Ratings_Rides
FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,
CONSTRAINT FK_Ratings_Users
FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE,
CONSTRAINT FK_Ratings_Drivers
FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id) ON DELETE CASCADE
);
CREATE TABLE Cancellations (
cancellation_id SERIAL PRIMARY KEY,
cancelled_by_type VARCHAR(255) NOT NULL,
cancelled_by_id INT NOT NULL,
reason VARCHAR(255) NOT NULL,
cancellation_fee NUMERIC(10,2) NOT NULL,
created_at TIMESTAMP NOT NULL,
ride_id INT NOT NULL,
CONSTRAINT chk_fee CHECK (cancellation_fee >= 0),
CONSTRAINT chk_cancelled_by_type CHECK (cancelled_by_type IN ('USER', 'DRIVER', 'ADMIN')),
CONSTRAINT FK_Cancellations_Rides
FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE
);
CREATE TABLE Messages(
message_id SERIAL PRIMARY KEY,
message_text VARCHAR(255) NOT NULL
);
CREATE TABLE Notifications (
notification_id SERIAL PRIMARY KEY,
message_id INT NOT NULL,
sent_time TIMESTAMP NOT NULL,
User_id INT NOT NULL,
ride_id INT NOT NULL,
status_id INT NOT NULL,
CONSTRAINT FK_Notifications_Users
FOREIGN KEY (User_id) REFERENCES Users(User_id) ON DELETE CASCADE,
CONSTRAINT FK_Notifications_Rides
FOREIGN KEY (ride_id) REFERENCES Rides(ride_id) ON DELETE CASCADE,
CONSTRAINT FK_Notifications_Status
FOREIGN KEY (status_id) REFERENCES Status(status_id) ON DELETE RESTRICT,
CONSTRAINT FK_Notifications_Messages
FOREIGN KEY (message_id) REFERENCES Messages(message_id) ON DELETE CASCADE
);
DML Имплементација
DML скриптите се користат за внесување, генерирање и управување со податоците во базата на податоци. Во овој проект, преку DML наредби се пополнуваат табелите со иницијални и тест податоци потребни за функционирање и симулација на ride-sharing системот.
Во скриптите се опфатени:
- внесување на почетни податоци (INSERT INTO)
- генерирање на големи количини тест податоци
- користење на SELECT, CASE, WITH и JOIN изрази за автоматско креирање податоци
- симулација на реални сценарија за возења, плаќања, откажувања и нотификации
- batch обработка и оптимизација за работа со милионски datasets
DML Скрипти
INSERT INTO Status (status_tip, status_text) VALUES
('driver', 'active'),
('driver', 'inactive');
INSERT INTO Status (status_tip, status_text) VALUES
('user', 'active'),
('user', 'inactive'),
('user', 'blocked'),
('user', 'deleted');
INSERT INTO Status (status_tip, status_text) VALUES
('admin', 'active'),
('admin', 'inactive'),
('admin', 'blocked');
INSERT INTO Status (status_tip, status_text) VALUES
('ride', 'requested'),
('ride', 'accepted'),
('ride', 'in_progress'),
('ride', 'completed'),
('ride', 'cancelled');
INSERT INTO Status (status_tip, status_text) VALUES
('payment', 'pending'),
('payment', 'completed'),
('payment', 'failed'),
('payment', 'refunded');
INSERT INTO Payment_methods (payment_tip) VALUES
('CASH'),
('CARD');
INSERT INTO Messages (message_text) VALUES
('Vaseto baranje e uspesno isprateno'),
('Vozacot go prifati vaseto baranje'),
('Vozacot go odbi vaseto baranje'),
('Vozacot pristigna na lokacijata za podiganje'),
('Vozenjeto e otkazano od strana na vozacot'),
('Vozenjeto e otkazano od vasa strana'),
('Plakjanjeto e uspesno'),
('Plakjanjeto ne uspea'),
('Nemate dostapni vozaci vo momentot');
INSERT INTO Vehicles_model (Model) VALUES
('Volkswagen'),
('Skoda'),
('Toyota'),
('Opel'),
('Mercedes-Benz'),
('BMW'),
('Audi'),
('Hyundai'),
('Kia'),
('Renault');
INSERT INTO Vehicle_types (type_name, description, Vehicles_model_id)
SELECT
m.model_name,
CASE
WHEN m.model_name IN ('Golf','Octavia','Corolla','Astra','Polo','Fabia','Yaris','Corsa','Elantra','Clio',
'i30','Rio','Megane','Passat','Superb','Camry','Insignia','Tucson','Ceed','Captur')
THEN 'Economy class'
ELSE 'Luxury class'
END as description,
m.brand_id
FROM (
SELECT vm.Vehicles_model_id as brand_id, unnest(models) as model_name
FROM Vehicles_model vm
JOIN (VALUES
('Volkswagen', ARRAY['Golf','Passat','Tiguan','Polo','Arteon']),
('Skoda', ARRAY['Octavia','Superb','Kodiaq','Fabia','Karoq']),
('Toyota', ARRAY['Corolla','Yaris','RAV4','Camry','C-HR']),
('Opel', ARRAY['Astra','Insignia','Corsa','Mokka','Grandland']),
('Mercedes-Benz', ARRAY['E-Class','C-Class','S-Class','GLE','CLA']),
('BMW', ARRAY['3 Series','5 Series','X5','X3','1 Series']),
('Audi', ARRAY['A4','A6','Q5','A3','Q7']),
('Hyundai', ARRAY['Elantra','Tucson','Santa Fe','i30','Kona']),
('Kia', ARRAY['Sportage','Ceed','Rio','Sorento','Niro']),
('Renault', ARRAY['Clio','Megane','Captur','Kadjar','Talisman'])
) AS t(brand, models) ON vm.Model = t.brand
) m;
INSERT INTO Pricing_rules (base_price, price_per_km, vehicle_type_id)
SELECT
CASE
WHEN description = 'Economy class' THEN 80.00
ELSE 100.00
END as base_price,
CASE
WHEN description = 'Economy class' THEN 40.00
ELSE 60.00
END as price_per_km,
vehicle_type_id
FROM Vehicle_types;
INSERT INTO Locations (latitude, longitude, grad, naselba, ulica, broj, created_at)
WITH street_data AS (
SELECT * FROM (VALUES
('Centar', 42.1322, 21.7144, ARRAY[
'11 Oktomvri', 'Goce Delchev', 'Tane Georgiev', 'Gorce Petrov', 'Plostad Marsal Tito',
'Done Bozhinov', 'Ljubo Atanasov', 'Ivo Lola Ribar', 'Leninova', 'Narodna Revolucija',
'Svetozar Markovic', 'Krste Misirkov', 'Jane Sandanski', 'Dragan Stoparevic', 'Bratstvo Edinstvo',
'Marsal Tito', 'JNA', 'Stiv Naumov', 'Guro Pucar Stari', 'Hristijan Karpos'
]),
('Zelen Rid', 42.1400, 21.7000, ARRAY[
'Nikola Tesla', 'Zheleznichka', 'Vidoe Smilevski Bato', 'Vasil Glavinov', 'Sremska',
'Kozjak', 'Osogovska', 'Shar Planina', 'Belasica', 'Ruen',
'Prilepska', 'Bitolska', 'Ohridska', 'Strumicka', 'Vardar',
'Pelister', 'Skopska', 'Tetovska', 'Gevgeliska', 'Veleska'
]),
('Goce Delchev', 42.1250, 21.7250, ARRAY[
'Treta Makedonska Udarna Brigada', 'Vera Kotorka', 'Boro Mikic', 'Filip Vtori', 'Srbo Tomovik',
'Zikica Jovanovic Spanac', 'Boro Menkov', 'Milan Zecar', 'Dositej Obradovic', 'Kuzman Josifovski Pitu',
'Tomaki Dimitrovski', 'Todor Velkov', 'Vasko Karangelevski', 'Niksicka', 'Pero Nakov',
'Bajram Shabani', 'Mite Bogoevski', 'Slavko Janevski', 'Gjorce Petrov', 'Kiro Fetak'
]),
('Pero Chicho', 42.1150, 21.7300, ARRAY[
'Pero Chicho', '11 Noemvri', 'Boro Mendkov', 'Josip Pancic', 'Banatska',
'Moravska', 'Drinska', 'Sredorek', 'Vojvodina', 'Sumadinska',
'Toplicki Odred', 'Zajecarska', 'Niska', 'Vranjska', 'Leskovacka',
'Sremska', 'Prizrenska', 'Dakovicka', 'Pecka', 'Kosovska'
]),
('Karposh', 42.1200, 21.7500, ARRAY[
'Oktomvriska Revolucija', 'Srecko Puzalka', 'Mito Hadzivasilev Jasmin', 'Sava Kovacevic', 'Karposova',
'Metodija Andonov Cento', 'ASNOM', 'Dame Gruev', 'Pitu Guli', 'Partizanska',
'Kiro Burnaz', 'Pere Tosev', 'Gjorce Petrov', 'Hristijan Todorovski', 'Miroslav Krleza',
'Blagoja Stevkovski', 'Kosta Racin', 'Koco Racin', 'Mirce Acev', 'Vasil Antevski'
]),
('Bedinje', 42.1500, 21.7100, ARRAY[
'Srbo Tomovikj', 'Zhivko Tomovski', 'Guro Djakovic', 'Karaorman', 'Njegoseva',
'Ivan Milutinovic', 'Mojsa Pijade', 'Zheleznichka', 'Revolucionerna', 'Gjorce Petrov',
'Cede Filipovski', 'Bratstvo', 'Edinstvo', 'Prvomajska', 'Sloboda',
'Proleterska', 'Makedonska', 'Ilindenska', 'Vidoe Smilevski', 'Josip Pancic'
]),
('Ajducka Cheshma', 42.1480, 21.6950, ARRAY[
'Bajram Shabani', 'Prilepska', 'Blagoja Stevkovski', 'Romanovska', 'Kumanovska',
'Debarska', 'Krusevska', 'Dojranska', 'Prespanska', 'Gevgeliska',
'Negotinska', 'Kavadarska', 'Radoviska', 'Berovska', 'Delcevska',
'Vinicka', 'Kratovska', 'Zletovska', 'Probistipska', 'Svetinikolska'
])
) AS t(naselba, lat_base, lon_base, ulici)
),
expanded AS (
SELECT
naselba,
lat_base,
lon_base,
unnest(ulici) as ulica
FROM street_data
),
numbered AS (
SELECT
naselba,
lat_base,
lon_base,
ulica,
row_number() OVER (PARTITION BY naselba ORDER BY ulica) as street_num
FROM expanded
)
SELECT
(lat_base
+ (street_num * 0.0008)
+ (n.num * 0.000045)
+ (random() * 0.00005 - 0.000025)
)::NUMERIC(9,6) AS latitude,
(lon_base
+ (street_num * 0.0010)
+ (n.num * 0.000060)
+ (random() * 0.00005 - 0.000025)
)::NUMERIC(9,6) AS longitude,
'Kumanovo' AS grad,
nd.naselba AS naselba,
nd.ulica AS ulica,
n.num::VARCHAR AS broj,
(NOW() - interval '5 years' - (random() * interval '365 days'))::TIMESTAMP AS created_at
FROM numbered nd
CROSS JOIN generate_series(1, 100) AS n(num);
INSERT INTO Users (
name,
username,
phone,
email,
password_hash,
created_at,
status_id
)
WITH data_pools AS (
SELECT
ARRAY['Marija','Ana','Ivana','Elena','Sara','Katerina','Jana','Mila','Teodora','Kristina','Sofija','Jovana','Tamara','Simona','Biljana','Vesna','Diana','Lidija','Monika','Angela','Ema','Tijana','Marina','Sonja','Nina','Anita','Valentina','Dragana','Ivona','Aleksandra','Natasha','Katarina','Emilija','Andrea','Gabriela','Tea','Danica','Zvezdana','Slobodanka','Gordana','Zorica','Ljubica','Rada','Mirjana','Despina','Kalina','Dafina','Hristina','Anastasija','Snezhana','Zlatka','Stojanka','Stana','Milka','Roksana','Romina','Karolina','Alina','Elina','Selina','Adelina','Darija','Dorotea','Nela','Neda','Zana','Hana','Ines','Iva','Irina','Arina','Lara','Tara','Dara','Kira','Lila','Tina','Jasmina','Bilja','Vanja','Maja','Bojana','Milena','Stefanija','Viktorija','Tatjana','Slavica','Ruzica'] AS f_names,
ARRAY['Petar','Marko','Nikola','Stefan','Daniel','Goran','Bojan','Andrej','Filip','Viktor','Aleksandar','Dejan','Zoran','Ilija','Dimitar','Kiril','Hristijan','Vladimir','Vasko','Riste','Martin','Igor','Kristijan','Nenad','Damjan','Darko','Oliver','Simeon','Luka','Emil','Alen','Stojan','Metodija','Vlatko','Boris','Davor','Radoslav','Kosta','Mladen','Mile','Petre','Gjorgi','Trajan','Jovan','Ivan','Spas','Sasho','Zlatko','Bojcho','Risto','Bogdan','Tome','Kole','Vangel','Naum','Gligor','Stanko','Rade','Lazo','Arsen','Milko','Pavle','Dragan','Zdravko','Ilko','Vuk','Dame','Gorancho','Stevo','Miro'] AS m_names,
ARRAY['Arsova','Dimitrova','Stojanova','Petrovska','Ilievska','Trajkova','Georgieva','Kostova','Maneva','Ristova','Popova','Jovanova','Nikolova','Mitrevska','Ivanova','Sokolova','Markova','Spasova','Angelova','Vasilevska','Pavlova','Gruevska','Nikolovska','Filipova','Stefanova','Andonova','Zafirova','Koleva','Stamenova','Bojkova','Todorova','Petkova','Hristova','Kitanova','Ilieva','Ruseva','Dimovska','Miloshevska','Petreska','Arnaudova','Georgievska','Markoska','Velkova','Stojkovska','Kirovska','Zlateva','Milevska','Naceva','Spiridonova','Lazarevska','Doneva','Atanasova','Bogdanova','Simeonova','Rangelova','Gligorova','Tasevska','Pancheva','Jankova','Bogoeva'] AS f_surnames,
ARRAY['Arsov','Dimitrov','Stojanov','Petrovski','Iliev','Trajkov','Georgiev','Kostov','Manev','Ristov','Popov','Jovanov','Nikolov','Mitrev','Ivanov','Sokolov','Markov','Spasov','Angelov','Vasilev','Pavlov','Gruev','Nikolovski','Filipov','Stefanov','Andonov','Zafirov','Kolev','Stamenov','Bojkov','Todorov','Petkov','Hristov','Kitanov','Rusev','Dimovski','Miloshevski','Petreski','Arnaudov','Georgievski','Markoski','Velkov','Stojkovski','Kirovski','Zlatev','Milevski','Nacev','Spiridonov','Lazarevski','Donev','Atanasov','Bogdanov','Simeonov','Rangelov','Gligorov','Tasev','Panchev','Jankov','Bogoev','Karov','Trifunov','Gavrilov','Savov','Ilkov','Vukov','Zdravkov','Stojanovski','Danev'] AS m_surnames
)
SELECT
p.fname || ' ' || p.lname AS name,
LOWER(p.fname || '.' || p.lname || i) AS username,
'+3897' || (1000000 + i) AS phone,
LOWER(p.fname || '.' || p.lname || i || '@gmail.com') AS email,
md5(random()::text) AS password_hash,
NOW() - (random() * interval '3 years') AS created_at,
(SELECT status_id FROM Status WHERE status_tip = 'user' AND status_text = 'active' LIMIT 1) AS status_id
FROM generate_series(1, 70000) i
CROSS JOIN data_pools d
CROSS JOIN LATERAL (
SELECT
CASE WHEN i % 2 = 0
THEN d.f_names[ (i % cardinality(d.f_names)) + 1]
ELSE d.m_names[ (i % cardinality(d.m_names)) + 1]
END AS fname,
CASE WHEN i % 2 = 0
THEN d.f_surnames[(i % cardinality(d.f_surnames)) + 1]
ELSE d.m_surnames[(i % cardinality(d.m_surnames)) + 1]
END AS lname
) p;
INSERT INTO Drivers (name, phone, license_number, registration_date, rating, status_id)
WITH data_pools AS (
SELECT
ARRAY['Petar','Marko','Nikola','Stefan','Daniel','Goran','Bojan','Andrej','Filip','Viktor',
'Aleksandar','Dejan','Zoran','Ilija','Dimitar','Kiril','Hristijan','Vladimir','Vasko','Riste',
'Martin','Igor','Damjan','Darko','Oliver','Luka','Emil','Stojan','Vlatko','Boris',
'Radoslav','Kosta','Mladen','Mile','Petre','Gjorgi','Jovan','Ivan','Sasho','Zlatko',
'Bogdan','Tome','Naum','Gligor','Stanko','Rade','Lazo','Arsen','Pavle','Dragan'] AS m_names,
ARRAY['Arsov','Dimitrov','Stojanov','Petrovski','Iliev','Trajkov','Georgiev','Kostov','Manev',
'Ristov','Popov','Jovanov','Nikolov','Mitrev','Ivanov','Sokolov','Markov','Spasov','Angelov',
'Vasilev','Pavlov','Gruev','Nikolovski','Filipov','Stefanov','Andonov','Zafirov','Kolev',
'Stamenov','Bojkov','Todorov','Petkov','Hristov','Kitanov','Rusev','Dimovski','Miloshevski',
'Petreski','Arnaudov','Georgievski','Markoski','Velkov','Stojkovski','Kirovski','Zlatev',
'Milevski','Spiridonov','Lazarevski','Atanasov','Bogdanov'] AS m_surnames
)
SELECT
p.fname || ' ' || p.lname AS name,
'+3897' || (2000000 + i) AS phone,
CHR(65 + (i % 26)) || LPAD((floor(random() * 9000000) + 1000000)::int::text, 7, '0') AS license_number,
(NOW() - interval '3 years' - (random() * interval '2 years'))::DATE AS registration_date,
ROUND((3.5 + random() * 1.5)::numeric, 2) AS rating,
(SELECT status_id FROM Status WHERE status_tip = 'driver' AND status_text = 'active' LIMIT 1) AS status_id
FROM generate_series(1, 200) i
CROSS JOIN data_pools d
CROSS JOIN LATERAL (
SELECT
d.m_names[(i % cardinality(d.m_names)) + 1] AS fname,
d.m_surnames[(i % cardinality(d.m_surnames)) + 1] AS lname
) p;
INSERT INTO Admins (name, username, phone, email, password_hash, created_at, status_id)
VALUES
('Aleksandar Dimitrov', 'admin.aleksandar', '+38971100001', 'aleksandar.dimitrov@taxikumanovo.mk', md5(random()::text), NOW() - interval '4 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)),
('Marija Petrovska', 'admin.marija', '+38971100002', 'marija.petrovska@taxikumanovo.mk', md5(random()::text), NOW() - interval '3 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)),
('Nikola Trajkov', 'admin.nikola', '+38971100003', 'nikola.trajkov@taxikumanovo.mk', md5(random()::text), NOW() - interval '3 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)),
('Elena Georgieva', 'admin.elena', '+38971100004', 'elena.georgieva@taxikumanovo.mk', md5(random()::text), NOW() - interval '2 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)),
('Stefan Iliev', 'admin.stefan', '+38971100005', 'stefan.iliev@taxikumanovo.mk', md5(random()::text), NOW() - interval '2 years', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1)),
('Ana Nikolova', 'admin.ana', '+38971100006', 'ana.nikolova@taxikumanovo.mk', md5(random()::text), NOW() - interval '1 year', (SELECT status_id FROM Status WHERE status_tip = 'admin' AND status_text = 'active' LIMIT 1));
INSERT INTO Vehicle_ownership (Plate, Traffic_permit, Color, year, vehicle_type_id)
WITH colors AS (
SELECT ARRAY['Crna','Bela','Siva','Srebrena','Crvena','Sina','Zelena','Beza','Kafena','Temnosina'] AS cols
)
SELECT
-- Табличка: 2 букви + 3 цифри + 2 букви, пр: SK123AB
'KU' ||
LPAD((100 + i)::text, 4, '0') ||
CHR(65 + ((i * 5) % 26)) || CHR(65 + ((i * 7) % 26)) AS Plate,
-- Сообраќајна: KU + 6 цифри
CHR(65 + (i % 26)) || CHR(65 + ((i * 3) % 26)) || LPAD((100000 + i)::text, 6, '0') AS Traffic_permit,
-- Боја
c.cols[(i % cardinality(c.cols)) + 1] AS Color,
-- Година: возилата се од 2010 до 2023
2010 + (i % 14) AS year,
-- vehicle_type_id: рандом од 1 до 50
(1 + (i % 50)) AS vehicle_type_id
FROM generate_series(1, 300) i
CROSS JOIN colors c;
INSERT INTO Active_drivers (driver_id, current_location_id, is_available, working_start, working_end,vehicle_ownership_id)
SELECT
d.driver_id,
-- Random location_id од постоечките локации
(1 + (d.driver_id % 14000)) AS current_location_id,
-- 70% се available, 30% се зафатени
CASE WHEN d.driver_id % 10 < 7 THEN true ELSE false END AS is_available,
-- Работната смена почнала пред 1-8 часа
(NOW() - (random() * interval '8 hours'))::TIMESTAMP AS working_start,
-- Работната смена завршува за 2-10 часа
(NOW() + (random() * interval '10 hours'))::TIMESTAMP AS working_end,
-- Да се знае кое авто моментлано го вози
CASE
WHEN d.driver_id < 100
THEN d.driver_id + (random(0,1) * 200)
ELSE d.driver_id
END AS vehicle_ownership_id
FROM Drivers d
-- Земаме само 150 од 200 возачи (75%)
WHERE d.driver_id % 4 != 0
LIMIT 150;
INSERT INTO Drivers_Vehicle_ownership (driver_id, Vehicle_ownership_id)
-- Sekoj vozac dobiva svoe primарно vozilo (1:1)
SELECT
d.driver_id,
-- Voziloto se mapira deterministicki: vozac 1→vozilo 1, vozac 2→vozilo 2 ...
d.driver_id AS Vehicle_ownership_id
FROM Drivers d
WHERE d.driver_id <= 200 -- site 200 vozaci
UNION ALL
-- Prvite 100 vozaci dobivaat i vtoro vozilo (od 201 do 300)
SELECT
d.driver_id,
d.driver_id + 200 AS Vehicle_ownership_id
FROM Drivers d
WHERE d.driver_id <= 100;
--Rides
DO $$
DECLARE
batch_size INT := 1000000;
total_rows INT := 10000000;
batches INT := total_rows / batch_size;
b INT;
max_user INT;
max_driver INT;
max_location INT;
sid_requested INT;
sid_accepted INT;
sid_in_progress INT;
sid_completed INT;
sid_cancelled INT;
BEGIN
SELECT MAX(user_id) INTO max_user FROM Users;
SELECT MAX(driver_id) INTO max_driver FROM Drivers;
SELECT MAX(location_id) INTO max_location FROM Locations;
SELECT status_id INTO sid_requested FROM Status WHERE status_tip='ride' AND status_text='requested' LIMIT 1;
SELECT status_id INTO sid_accepted FROM Status WHERE status_tip='ride' AND status_text='accepted' LIMIT 1;
SELECT status_id INTO sid_in_progress FROM Status WHERE status_tip='ride' AND status_text='in_progress' LIMIT 1;
SELECT status_id INTO sid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1;
SELECT status_id INTO sid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1;
RAISE NOTICE 'max_user=%, max_driver=%, max_location=%', max_user, max_driver, max_location;
FOR b IN 1..batches LOOP
INSERT INTO Rides (
user_id,
driver_id,
pickup_location_id,
dropoff_location_id,
status_id,
request_time,
pickup_time,
start_time,
end_time,
distance_km,
final_price,
discount_percentage,
vehicle_ownership_id
)
WITH
-- --------------------------------------------------------
-- CTE 1: Realna cena po vozac spored tipot na voziloto
-- Economy: base=80, per_km=40
-- Luxury: base=100, per_km=60
-- Ako ima 2 vozila → go zemame luksoznoto (MAX cena)
-- --------------------------------------------------------
driver_pricing AS (
SELECT
dvo.driver_id,
MAX(pr.base_price) AS base_price,
MAX(pr.price_per_km) AS price_per_km,
MAX(vt.description) AS vehicle_class
FROM Drivers_Vehicle_ownership dvo
JOIN Vehicle_ownership vo ON dvo.Vehicle_ownership_id = vo.Vehicle_ownership_id
JOIN Vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id
JOIN Pricing_rules pr ON vt.vehicle_type_id = pr.vehicle_type_id
GROUP BY dvo.driver_id
),
-- --------------------------------------------------------
-- CTE 2: Base pool so site ogranichuvanja primeneti
-- --------------------------------------------------------
base_pool AS (
SELECT
s.i,
-- USER / DRIVER (realni, razlichni)
1 + (s.i % max_user) AS user_id,
1 + ((s.i + floor(random() * 200000)+ 37)::INTEGER % max_driver) AS driver_id,
-- LOKACII
1 + (s.i + floor(random() * 200000))::INTEGER % max_location AS pickup_location_id,
1 + ((s.i + floor(random() * 200000)+ (14000 / 2))::INTEGER % max_location) AS dropoff_location_id,
-- STATUS
CASE
WHEN (s.i % 20) = 0 THEN sid_requested
WHEN (s.i % 20) IN (1, 2) THEN sid_accepted
WHEN (s.i % 20) IN (3, 4) THEN sid_in_progress
WHEN (s.i % 20) IN (18, 19) THEN sid_cancelled
ELSE sid_completed
END AS status_id,
-- BASE TIME: random den (poslednite 2 godini), cas 06:00-23:00
date_trunc('day', now() - (floor(random() * 730) * interval '1 day'))
+ (21600 + floor(random() * 61200)) * interval '1 second'
AS request_time,
-- DISTANCA: 0.5 .. 10.0 km (OGRANICHENO NA MAX 10km)
ROUND((0.5 + random() * 9.5)::numeric, 2) AS distance_km
FROM generate_series(
(b - 1) * batch_size + 1,
b * batch_size
) s(i)
),
-- --------------------------------------------------------
-- CTE 3: Presmetaj popust i vreminja vrz osnova na distanca
-- --------------------------------------------------------
enriched AS (
SELECT
bp.*,
-- POPUST:
-- >= 9km → 10%
-- < 9km → 0%
CASE
WHEN bp.distance_km >= 9 THEN 10.0
ELSE 0.0
END AS discount_pct,
-- VREMINJA (site od ista request_time baza):
-- pickup = request + 2..10 min
bp.request_time
+ (2 + floor(random() * 9)) * interval '1 minute'
AS pickup_time,
-- start = request + 3..13 min (sekogash posle pickup)
bp.request_time
+ (3 + floor(random() * 11)) * interval '1 minute'
AS start_time,
-- end = start + 5..15 min (OGRANICHENO: min 5, max 15)
bp.request_time
+ (3 + floor(random() * 11)) * interval '1 minute' -- ista start baza
+ (5 + floor(random() * 11)) * interval '1 minute' -- + 5..15 min
AS end_time
FROM base_pool bp
)
-- --------------------------------------------------------
-- FINALEN SELECT: JOIN so realni ceni od vozacovoto vozilo
-- --------------------------------------------------------
SELECT
en.user_id,
en.driver_id,
en.pickup_location_id,
en.dropoff_location_id,
en.status_id,
en.request_time,
en.pickup_time,
en.start_time,
en.end_time,
en.distance_km,
-- CENA spored tipot na voziloto:
-- Economy → (80 + km * 40) * (1 - popust%)
-- Luxury → (100 + km * 60) * (1 - popust%)
-- Ako vozacot nema vozilo → fallback na Economy cena
ROUND(
(COALESCE(dp.base_price, 80) + en.distance_km * COALESCE(dp.price_per_km, 40))
* (1.0 - en.discount_pct / 100.0)
::numeric, 2) AS final_price,
en.discount_pct AS discount_percentage,
CASE
WHEN en.driver_id < 100
THEN en.driver_id + (random(0,1) * 200)
ELSE
en.driver_id
END AS vehicle_ownership_id
FROM enriched en
LEFT JOIN driver_pricing dp ON en.driver_id = dp.driver_id;
RAISE NOTICE 'Batch % / % zavrshen — % redovi vkupno', b, batches, b * batch_size;
END LOOP;
update rides set pickup_location_id = 1 + (Rides.ride_id + floor(random() * 200000))::INTEGER% max_location
where dropoff_location_id=pickup_location_id;
RAISE NOTICE '*** GOTOVO: 10 000 000 redovi vo Rides ***';
END $$;
--Payments
DO $$
DECLARE
batch_size INT := 1000000;
total_rows INT := 10000000;
batches INT := total_rows / batch_size;
b INT;
-- Payment method IDs
pm_cash INT;
pm_card INT;
-- Payment status IDs
sid_pending INT;
sid_completed INT;
sid_failed INT;
sid_refunded INT;
-- Ride status IDs (za da odredime payment status)
rid_completed INT;
rid_cancelled INT;
BEGIN
-- Payment methods
SELECT payment_method_id INTO pm_cash FROM Payment_methods WHERE payment_tip = 'CASH' LIMIT 1;
SELECT payment_method_id INTO pm_card FROM Payment_methods WHERE payment_tip = 'CARD' LIMIT 1;
-- Payment statusi
SELECT status_id INTO sid_pending FROM Status WHERE status_tip='payment' AND status_text='pending' LIMIT 1;
SELECT status_id INTO sid_completed FROM Status WHERE status_tip='payment' AND status_text='completed' LIMIT 1;
SELECT status_id INTO sid_failed FROM Status WHERE status_tip='payment' AND status_text='failed' LIMIT 1;
SELECT status_id INTO sid_refunded FROM Status WHERE status_tip='payment' AND status_text='refunded' LIMIT 1;
-- Ride statusi
SELECT status_id INTO rid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1;
SELECT status_id INTO rid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1;
RAISE NOTICE 'pm_cash=%, pm_card=%', pm_cash, pm_card;
RAISE NOTICE 'payment statusi: pending=%, completed=%, failed=%, refunded=%',
sid_pending, sid_completed, sid_failed, sid_refunded;
FOR b IN 1..batches LOOP
INSERT INTO Payments (
amount,
payment_method_id,
payment_time,
ride_id,
status_id
)
SELECT
-- Amount = tocnata cena od ridot
r.final_price AS amount,
-- Payment method: 60% CASH, 40% CARD
CASE
WHEN (r.ride_id % 10) < 6 THEN pm_cash
ELSE pm_card
END AS payment_method_id,
-- Payment time = end_time + 1..5 min
r.end_time + (1 + floor(random() * 5)) * interval '1 minute'
AS payment_time,
r.ride_id AS ride_id,
-- Payment status spored status na ridot:
-- completed → completed
-- cancelled → 50% failed, 50% refunded
-- site drugi → pending
CASE
WHEN r.status_id = rid_completed THEN sid_completed
WHEN r.status_id = rid_cancelled THEN
CASE WHEN r.ride_id % 2 = 0 THEN sid_failed
ELSE sid_refunded
END
ELSE sid_pending
END AS status_id
FROM Rides r
WHERE r.ride_id BETWEEN (b - 1) * batch_size + 1
AND b * batch_size;
RAISE NOTICE 'Batch % / % zavrshen — % payments vkupno', b, batches, b * batch_size;
END LOOP;
RAISE NOTICE '*** GOTOVO: 10 000 000 redovi vo Payments ***';
END $$;
INSERT INTO Cancellations (
cancelled_by_type,
cancelled_by_id,
reason,
cancellation_fee,
created_at,
ride_id
)
WITH
-- Ride status za cancelled
cancelled_status AS (
SELECT status_id FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1
),
-- Realna base_price po vozac (Economy=80, Luxury=100)
driver_pricing AS (
SELECT
dvo.driver_id,
MAX(pr.base_price) AS base_price
FROM Drivers_Vehicle_ownership dvo
JOIN Vehicle_ownership vo ON dvo.Vehicle_ownership_id = vo.Vehicle_ownership_id
JOIN Vehicle_types vt ON vo.vehicle_type_id = vt.vehicle_type_id
JOIN Pricing_rules pr ON vt.vehicle_type_id = pr.vehicle_type_id
GROUP BY dvo.driver_id
),
-- Samo cancelled rides + row_number za ramномерна distribucija
cancelled_rides AS (
SELECT
r.ride_id,
r.user_id,
r.driver_id,
r.request_time,
COALESCE(dp.base_price, 80) AS base_price,
-- row_number() garantira 0..9 ramномерно bez zavisnost od ride_id vrednostite
(row_number() OVER (ORDER BY r.ride_id) - 1) % 10 AS rn
FROM Rides r
JOIN cancelled_status cs ON r.status_id = cs.status_id
LEFT JOIN driver_pricing dp ON r.driver_id = dp.driver_id
)
SELECT
-- cancelled_by_type: 50% USER (0-4), 30% DRIVER (5-7), 20% ADMIN (8-9)
CASE
WHEN rn IN (0,1,2,3,4) THEN 'USER'
WHEN rn IN (5,6,7) THEN 'DRIVER'
ELSE 'ADMIN'
END AS cancelled_by_type,
-- cancelled_by_id: realen ID
CASE
WHEN rn IN (0,1,2,3,4) THEN user_id
WHEN rn IN (5,6,7) THEN driver_id
ELSE 1 + (ride_id % 6)
END AS cancelled_by_id,
-- Reason spored tipot
CASE
WHEN rn IN (0,1,2,3,4) THEN
CASE (ride_id % 3)
WHEN 0 THEN 'Променив планови и повеќе не ми треба превоз'
WHEN 1 THEN 'Чекањето беше предолго'
ELSE 'Внесов погрешна локација за подигање'
END
WHEN rn IN (5,6,7) THEN
CASE (ride_id % 3)
WHEN 0 THEN 'Не можам да ја пронајдам точната локација на патникот'
WHEN 1 THEN 'Патникот не се појави на местото за подигање'
ELSE 'Технички проблем со возилото'
END
ELSE
CASE (ride_id % 3)
WHEN 0 THEN 'Вожњата е откажана поради сомнителна активност'
WHEN 1 THEN 'Откажано поради дупликат барање'
ELSE 'Откажано поради системска грешка'
END
END AS reason,
-- Fee: samo USER plaka, Driver i Admin = 0
CASE
WHEN rn IN (0,1,2,3,4) THEN base_price
ELSE 0.00
END AS cancellation_fee,
-- created_at = request_time + 1..30 min
request_time + (1 + floor(random() * 30)) * interval '1 minute'
AS created_at,
ride_id
FROM cancelled_rides;
DO $$
DECLARE
batch_size INT := 500000;
b INT;
max_ride INT;
batches INT;
mid_1 INT; mid_2 INT; mid_3 INT; mid_4 INT;
mid_5 INT; mid_6 INT; mid_7 INT; mid_8 INT; mid_9 INT;
sid_notif_sent INT;
sid_notif_fail INT;
rid_completed INT;
rid_cancelled INT;
rid_in_progress INT;
rid_accepted INT;
rid_requested INT;
pay_completed INT;
pay_failed INT;
BEGIN
-- Message IDs
SELECT message_id INTO mid_1 FROM Messages WHERE message_text LIKE 'Vaseto baranje e uspesno%' LIMIT 1;
SELECT message_id INTO mid_2 FROM Messages WHERE message_text LIKE 'Vozacot go prifati%' LIMIT 1;
SELECT message_id INTO mid_3 FROM Messages WHERE message_text LIKE 'Vozacot go odbi%' LIMIT 1;
SELECT message_id INTO mid_4 FROM Messages WHERE message_text LIKE 'Vozacot pristigna%' LIMIT 1;
SELECT message_id INTO mid_5 FROM Messages WHERE message_text LIKE 'Vozenjeto e otkazano od strana na vozac%' LIMIT 1;
SELECT message_id INTO mid_6 FROM Messages WHERE message_text LIKE 'Vozenjeto e otkazano od vasa%' LIMIT 1;
SELECT message_id INTO mid_7 FROM Messages WHERE message_text LIKE 'Plakjanjeto e uspesno%' LIMIT 1;
SELECT message_id INTO mid_8 FROM Messages WHERE message_text LIKE 'Plakjanjeto ne uspea%' LIMIT 1;
SELECT message_id INTO mid_9 FROM Messages WHERE message_text LIKE 'Nemate dostapni%' LIMIT 1;
-- Notification statusi (koristime ride status kako proxy za sent/fail)
SELECT status_id INTO sid_notif_sent FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1;
SELECT status_id INTO sid_notif_fail FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1;
-- Ride statusi
SELECT status_id INTO rid_completed FROM Status WHERE status_tip='ride' AND status_text='completed' LIMIT 1;
SELECT status_id INTO rid_cancelled FROM Status WHERE status_tip='ride' AND status_text='cancelled' LIMIT 1;
SELECT status_id INTO rid_in_progress FROM Status WHERE status_tip='ride' AND status_text='in_progress' LIMIT 1;
SELECT status_id INTO rid_accepted FROM Status WHERE status_tip='ride' AND status_text='accepted' LIMIT 1;
SELECT status_id INTO rid_requested FROM Status WHERE status_tip='ride' AND status_text='requested' LIMIT 1;
-- Payment statusi
SELECT status_id INTO pay_completed FROM Status WHERE status_tip='payment' AND status_text='completed' LIMIT 1;
SELECT status_id INTO pay_failed FROM Status WHERE status_tip='payment' AND status_text='failed' LIMIT 1;
SELECT MAX(ride_id) INTO max_ride FROM Rides;
batches := CEIL(max_ride::numeric / batch_size);
RAISE NOTICE 'Pocnuvame so % batches po % rides', batches, batch_size;
-- ===========================================================
-- FLOW 1: COMPLETED rides
-- Poraki: 1→2→4→7
-- Vreminja: request→pickup→start→end+2min
-- ===========================================================
FOR b IN 1..batches LOOP
INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id)
SELECT * FROM (
-- Poraka 1: Baranjeto e uspesno isprateno → request_time
SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_completed
UNION ALL
-- Poraka 2: Vozacot go prifati → pickup_time
SELECT mid_2, r.pickup_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_completed
UNION ALL
-- Poraka 4: Vozacot pristigna → start_time
SELECT mid_4, r.start_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_completed
UNION ALL
-- Poraka 7: Plakjanjeto uspesno → end_time + 2..5 min
SELECT mid_7,
r.end_time + (2 + floor(random()*4)) * interval '1 minute',
r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
JOIN Payments p ON r.ride_id = p.ride_id AND p.status_id = pay_completed
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_completed
) flows;
RAISE NOTICE '[COMPLETED] Batch % / %', b, batches;
END LOOP;
-- ===========================================================
-- FLOW 2: CANCELLED BY USER
-- Poraki: 1→6→8
-- Vreminja: request→request+1..10min→end+2min
-- ===========================================================
FOR b IN 1..batches LOOP
INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id)
SELECT * FROM (
-- Poraka 1: Baranjeto isprateno → request_time
SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER'
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_cancelled
UNION ALL
-- Poraka 6: Otkazano od vasa strana → request_time + 1..10 min
SELECT mid_6,
r.request_time + (1 + floor(random()*10)) * interval '1 minute',
r.user_id, r.ride_id, sid_notif_fail
FROM Rides r
JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER'
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_cancelled
UNION ALL
-- Poraka 8: Plakjanjeto ne uspea → end_time + 2..5 min
SELECT mid_8,
r.end_time + (2 + floor(random()*4)) * interval '1 minute',
r.user_id, r.ride_id, sid_notif_fail
FROM Rides r
JOIN Payments p ON r.ride_id = p.ride_id AND p.status_id = pay_failed
JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'USER'
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_cancelled
) flows;
RAISE NOTICE '[CANCELLED USER] Batch % / %', b, batches;
END LOOP;
-- ===========================================================
-- FLOW 3: CANCELLED BY DRIVER
-- Poraki: 1→2→5
-- Vreminja: request→pickup→request+5..15min
-- ===========================================================
FOR b IN 1..batches LOOP
INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id)
SELECT * FROM (
-- Poraka 1: Baranjeto isprateno → request_time
SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER'
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_cancelled
UNION ALL
-- Poraka 2: Vozacot prifati → pickup_time
SELECT mid_2, r.pickup_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER'
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_cancelled
UNION ALL
-- Poraka 5: Otkazano od vozac → request_time + 5..15 min
SELECT mid_5,
r.request_time + (5 + floor(random()*11)) * interval '1 minute',
r.user_id, r.ride_id, sid_notif_fail
FROM Rides r
JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'DRIVER'
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_cancelled
) flows;
RAISE NOTICE '[CANCELLED DRIVER] Batch % / %', b, batches;
END LOOP;
-- ===========================================================
-- FLOW 4: CANCELLED BY ADMIN
-- Poraki: 1→9
-- Vreminja: request→request (momentalno)
-- ===========================================================
FOR b IN 1..batches LOOP
INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id)
SELECT * FROM (
-- Poraka 1: Baranjeto isprateno → request_time
SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'ADMIN'
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_cancelled
UNION ALL
-- Poraka 9: Nema dostapni vozaci → request_time (vednas)
SELECT mid_9, r.request_time, r.user_id, r.ride_id, sid_notif_fail
FROM Rides r
JOIN Cancellations c ON r.ride_id = c.ride_id AND c.cancelled_by_type = 'ADMIN'
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_cancelled
) flows;
RAISE NOTICE '[CANCELLED ADMIN] Batch % / %', b, batches;
END LOOP;
-- ===========================================================
-- FLOW 5: ACCEPTED / IN_PROGRESS rides
-- Poraki: 1→2→4
-- Voznjata e vo tek, nema kraj i plakjanje uste
-- ===========================================================
FOR b IN 1..batches LOOP
INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id)
SELECT * FROM (
-- Poraka 1: Baranjeto isprateno → request_time
SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id IN (rid_accepted, rid_in_progress)
UNION ALL
-- Poraka 2: Vozacot prifati → pickup_time
SELECT mid_2, r.pickup_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id IN (rid_accepted, rid_in_progress)
UNION ALL
-- Poraka 4: Vozacot pristigna → start_time
SELECT mid_4, r.start_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id IN (rid_accepted, rid_in_progress)
) flows;
RAISE NOTICE '[ACCEPTED/IN_PROGRESS] Batch % / %', b, batches;
END LOOP;
-- ===========================================================
-- FLOW 6: REQUESTED rides
-- Poraka: samo 1
-- Barano no vozac uste ne odgovoril
-- ===========================================================
FOR b IN 1..batches LOOP
INSERT INTO Notifications (message_id, sent_time, user_id, ride_id, status_id)
-- Poraka 1: Baranjeto isprateno → request_time
SELECT mid_1, r.request_time, r.user_id, r.ride_id, sid_notif_sent
FROM Rides r
WHERE r.ride_id BETWEEN (b-1)*batch_size+1 AND b*batch_size
AND r.status_id = rid_requested;
RAISE NOTICE '[REQUESTED] Batch % / %', b, batches;
END LOOP;
RAISE NOTICE '*** GOTOVO: SITE NOTIFICATIONS SE GENERIRANI ***';
END $$;
Last modified
3 days ago
Last modified on 05/22/26 15:29:58
Note:
See TracWiki
for help on using the wiki.
