176 lines
6.4 KiB
PL/PgSQL
176 lines
6.4 KiB
PL/PgSQL
CREATE SEQUENCE IF NOT EXISTS coordinates_seq START 1;
|
|
CREATE SEQUENCE IF NOT EXISTS location_seq START 1;
|
|
CREATE SEQUENCE IF NOT EXISTS album_seq START 1;
|
|
CREATE SEQUENCE IF NOT EXISTS person_seq START 1;
|
|
CREATE SEQUENCE IF NOT EXISTS music_band_seq START 1;
|
|
CREATE SEQUENCE IF NOT EXISTS users_seq START 1;
|
|
CREATE SEQUENCE IF NOT EXISTS import_operation_seq START 1;
|
|
|
|
CREATE TABLE IF NOT EXISTS coordinates (
|
|
id BIGINT PRIMARY KEY DEFAULT nextval('coordinates_seq'),
|
|
x BIGINT NOT NULL,
|
|
y INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS location (
|
|
id BIGINT PRIMARY KEY DEFAULT nextval('location_seq'),
|
|
x INTEGER NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
z INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS album (
|
|
id BIGINT PRIMARY KEY DEFAULT nextval('album_seq'),
|
|
name VARCHAR(255) NOT NULL CHECK (name <> ''),
|
|
tracks BIGINT NOT NULL CHECK (tracks > 0)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS person (
|
|
id BIGINT PRIMARY KEY DEFAULT nextval('person_seq'),
|
|
name VARCHAR(255) NOT NULL CHECK (name <> ''),
|
|
eye_color VARCHAR(20) NOT NULL CHECK (eye_color IN ('GREEN', 'BLUE', 'BROWN')),
|
|
hair_color VARCHAR(20) CHECK (hair_color IN ('GREEN', 'BLUE', 'BROWN')),
|
|
location_id BIGINT REFERENCES location(id) ON DELETE CASCADE,
|
|
height DOUBLE PRECISION NOT NULL CHECK (height > 0),
|
|
nationality VARCHAR(20) NOT NULL CHECK (nationality IN ('INDIA', 'THAILAND', 'SOUTH_KOREA', 'NORTH_KOREA'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGINT PRIMARY KEY DEFAULT nextval('users_seq'),
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role VARCHAR(20) NOT NULL CHECK (role IN ('USER', 'ADMIN')),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS import_operation (
|
|
id BIGINT PRIMARY KEY DEFAULT nextval('import_operation_seq'),
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
status VARCHAR(20) NOT NULL CHECK (status IN ('SUCCESS', 'FAILED')),
|
|
objects_count INTEGER,
|
|
error_message TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS music_band (
|
|
id BIGINT PRIMARY KEY DEFAULT nextval('music_band_seq'),
|
|
name VARCHAR(255) NOT NULL CHECK (name <> ''),
|
|
coordinates_id BIGINT NOT NULL REFERENCES coordinates(id) ON DELETE CASCADE,
|
|
creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
genre VARCHAR(30) NOT NULL CHECK (genre IN ('ROCK', 'PSYCHEDELIC_CLOUD_RAP', 'JAZZ')),
|
|
number_of_participants BIGINT NOT NULL CHECK (number_of_participants > 0),
|
|
singles_count INTEGER NOT NULL CHECK (singles_count > 0),
|
|
description TEXT,
|
|
best_album_id BIGINT REFERENCES album(id) ON DELETE SET NULL,
|
|
albums_count INTEGER CHECK (albums_count IS NULL OR albums_count > 0),
|
|
establishment_date TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
front_man_id BIGINT REFERENCES person(id) ON DELETE SET NULL,
|
|
created_by BIGINT REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'import_operation' AND column_name = 'file_key'
|
|
) THEN
|
|
ALTER TABLE import_operation ADD COLUMN file_key VARCHAR(500);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'import_operation' AND column_name = 'original_filename'
|
|
) THEN
|
|
ALTER TABLE import_operation ADD COLUMN original_filename VARCHAR(255);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'import_operation' AND column_name = 'file_format'
|
|
) THEN
|
|
ALTER TABLE import_operation ADD COLUMN file_format VARCHAR(20);
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.tables
|
|
WHERE table_name = 'music_band'
|
|
) AND NOT EXISTS (
|
|
SELECT 1 FROM music_band WHERE created_by IS NOT NULL LIMIT 1
|
|
) THEN
|
|
UPDATE music_band
|
|
SET created_by = (SELECT id FROM users WHERE username = 'admin' LIMIT 1)
|
|
WHERE created_by IS NULL;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_music_band_name ON music_band(name);
|
|
CREATE INDEX IF NOT EXISTS idx_music_band_genre ON music_band(genre);
|
|
CREATE INDEX IF NOT EXISTS idx_music_band_participants ON music_band(number_of_participants);
|
|
CREATE INDEX IF NOT EXISTS idx_music_band_establishment_date ON music_band(establishment_date);
|
|
CREATE INDEX IF NOT EXISTS idx_music_band_created_by ON music_band(created_by);
|
|
CREATE INDEX IF NOT EXISTS idx_import_operation_user ON import_operation(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_import_operation_status ON import_operation(status);
|
|
CREATE INDEX IF NOT EXISTS idx_import_operation_created_at ON import_operation(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
|
|
|
CREATE OR REPLACE FUNCTION calculate_average_albums_count()
|
|
RETURNS DOUBLE PRECISION AS $$
|
|
BEGIN
|
|
RETURN (SELECT AVG(albums_count) FROM music_band WHERE albums_count IS NOT NULL);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION find_band_with_max_name()
|
|
RETURNS TABLE(id BIGINT, name VARCHAR(255)) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT mb.id, mb.name
|
|
FROM music_band mb
|
|
WHERE mb.name = (SELECT MAX(mb2.name) FROM music_band mb2)
|
|
LIMIT 1;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION group_bands_by_participants()
|
|
RETURNS TABLE(participants BIGINT, band_count BIGINT) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT mb.number_of_participants, COUNT(*)::BIGINT
|
|
FROM music_band mb
|
|
GROUP BY mb.number_of_participants
|
|
ORDER BY mb.number_of_participants;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION add_single_to_band(band_id BIGINT)
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
UPDATE music_band
|
|
SET singles_count = singles_count + 1
|
|
WHERE id = band_id;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'Band with ID % not found', band_id;
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION remove_participant_from_band(band_id BIGINT)
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
UPDATE music_band
|
|
SET number_of_participants = number_of_participants - 1
|
|
WHERE id = band_id AND number_of_participants > 1;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'Band with ID % not found or cannot remove participant (minimum 1 required)', band_id;
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
INSERT INTO users (username, password_hash, role)
|
|
VALUES ('admin', '$2a$10$SIVWLONFduZBQmozzHmVbO21zvCfXeg649BvXLwbYxL/8EOBGCqSG', 'ADMIN')
|
|
ON CONFLICT (username) DO NOTHING; |