Database create script - Pawlllosss/Radiappka GitHub Wiki

CREATE SCHEMA redaktor;

CREATE SEQUENCE redaktor.piosenka_piosenka_id_seq_1;

CREATE TABLE redaktor.piosenka ( piosenka_id INTEGER NOT NULL DEFAULT nextval('redaktor.piosenka_piosenka_id_seq_1'), tytul VARCHAR NOT NULL, wykonawca VARCHAR NOT NULL, CONSTRAINT piosenka_pk PRIMARY KEY (piosenka_id) );

ALTER SEQUENCE redaktor.piosenka_piosenka_id_seq_1 OWNED BY redaktor.piosenka.piosenka_id;

CREATE SEQUENCE redaktor.studio_studio_id_seq_1;

CREATE TABLE redaktor.studio ( studio_id INTEGER NOT NULL DEFAULT nextval('redaktor.studio_studio_id_seq_1'), nazwa VARCHAR NOT NULL, nr_pokoju INTEGER NOT NULL, CONSTRAINT studio_pk PRIMARY KEY (studio_id) );

ALTER SEQUENCE redaktor.studio_studio_id_seq_1 OWNED BY redaktor.studio.studio_id;

CREATE SEQUENCE redaktor.redaktor_redaktor_id_seq;

CREATE TABLE redaktor.redaktor ( redaktor_id INTEGER NOT NULL DEFAULT nextval('redaktor.redaktor_redaktor_id_seq'), sekcja_id INTEGER, imie VARCHAR NOT NULL, nazwisko VARCHAR NOT NULL, CONSTRAINT redaktor_pk PRIMARY KEY (redaktor_id) );

CREATE SEQUENCE redaktor.audycja_audycja_id_seq;

CREATE TABLE redaktor.audycja ( audycja_id INTEGER NOT NULL DEFAULT nextval('redaktor.audycja_audycja_id_seq'), program_id INTEGER NOT NULL, data_poczatek TIMESTAMP NOT NULL, data_koniec TIMESTAMP NOT NULL, studio_id INTEGER, CONSTRAINT audycja_pk PRIMARY KEY (audycja_id) );

ALTER SEQUENCE redaktor.audycja_audycja_id_seq OWNED BY redaktor.audycja.audycja_id;

CREATE SEQUENCE redaktor.piosenka_odtwarzanie_piosenka_odtwarzanie_id_seq;

CREATE TABLE redaktor.piosenka_odtwarzanie ( piosenka_odtwarzanie_id INTEGER NOT NULL DEFAULT nextval('redaktor.piosenka_odtwarzanie_piosenka_odtwarzanie_id_seq'), czas_odtworzenia TIME NOT NULL, piosenka_id INTEGER NOT NULL, audycja_id INTEGER NOT NULL, CONSTRAINT piosenka_odtwarzanie_pk PRIMARY KEY (piosenka_odtwarzanie_id) );

ALTER SEQUENCE redaktor.piosenka_odtwarzanie_piosenka_odtwarzanie_id_seq OWNED BY redaktor.piosenka_odtwarzanie.piosenka_odtwarzanie_id;

ALTER SEQUENCE redaktor.redaktor_redaktor_id_seq OWNED BY redaktor.redaktor.redaktor_id;

CREATE SEQUENCE redaktor.sekcja_sekcja_id_seq_1;

CREATE TABLE redaktor.sekcja ( sekcja_id INTEGER NOT NULL DEFAULT nextval('redaktor.sekcja_sekcja_id_seq_1'), redaktor_id INTEGER, nazwa VARCHAR NOT NULL, CONSTRAINT sekcja_pk PRIMARY KEY (sekcja_id) );

ALTER SEQUENCE redaktor.sekcja_sekcja_id_seq_1 OWNED BY redaktor.sekcja.sekcja_id;

CREATE SEQUENCE redaktor.program_program_id_seq;

CREATE TABLE redaktor.program ( program_id INTEGER NOT NULL DEFAULT nextval('redaktor.program_program_id_seq'), nazwa VARCHAR NOT NULL, opis VARCHAR NOT NULL, sekcja_id INTEGER, CONSTRAINT program_pk PRIMARY KEY (program_id) );

ALTER SEQUENCE redaktor.program_program_id_seq OWNED BY redaktor.program.program_id;

CREATE TABLE redaktor.redaktor_program ( redaktor_id INTEGER NOT NULL, program_id INTEGER NOT NULL, CONSTRAINT redaktor_program_pk PRIMARY KEY (redaktor_id, program_id) );

ALTER TABLE redaktor.piosenka_odtwarzanie ADD CONSTRAINT piosenka_piosenka_odtwarzanie_fk FOREIGN KEY (piosenka_id) REFERENCES redaktor.piosenka (piosenka_id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE;

ALTER TABLE redaktor.Audycja ADD CONSTRAINT studio_audycja_fk FOREIGN KEY (studio_id) REFERENCES redaktor.Studio (studio_id) ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE;

ALTER TABLE redaktor.redaktor_program ADD CONSTRAINT redaktor_redaktor_program_fk FOREIGN KEY (redaktor_id) REFERENCES redaktor.redaktor (redaktor_id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE;

ALTER TABLE redaktor.sekcja ADD CONSTRAINT redaktor_sekcja_fk FOREIGN KEY (redaktor_id) REFERENCES redaktor.redaktor (redaktor_id) ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE;

ALTER TABLE redaktor.program ADD CONSTRAINT sekcja_program_fk FOREIGN KEY (sekcja_id) REFERENCES redaktor.sekcja (sekcja_id) ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE;

ALTER TABLE redaktor.redaktor ADD CONSTRAINT sekcja_redaktor_fk FOREIGN KEY (sekcja_id) REFERENCES redaktor.sekcja (sekcja_id) ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE;

ALTER TABLE redaktor.Audycja ADD CONSTRAINT program_audycja_fk FOREIGN KEY (program_id) REFERENCES redaktor.program (program_id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE;

ALTER TABLE redaktor.piosenka_odtwarzanie ADD CONSTRAINT audycja_piosenkaodtwarzanie_fk FOREIGN KEY (audycja_id) REFERENCES redaktor.audycja (audycja_id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE;

ALTER TABLE redaktor.redaktor_program ADD CONSTRAINT program_redaktor_program_fk FOREIGN KEY (program_id) REFERENCES redaktor.program (program_id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE;

CREATE OR REPLACE VIEW redaktor.program_redaktor_count AS SELECT pr.program_id, pr.nazwa, COUNT(rp.redaktor_id) as redaktor_count FROM redaktor.program pr LEFT JOIN redaktor.redaktor_program rp USING (program_id) GROUP BY pr.program_id, pr.nazwa;

CREATE OR REPLACE VIEW redaktor.program_przypisany_redaktor AS SELECT pr.program_id, pr.nazwa AS program_nazwa, CONCAT(r.imie, ' ', r.nazwisko) AS imie_nazwisko, r.redaktor_id FROM redaktor.redaktor_program rp JOIN redaktor.redaktor r USING (redaktor_id) JOIN redaktor.program pr USING (program_id);

CREATE OR REPLACE FUNCTION get_audycjas_in_day(day_to_check date) RETURNS SETOF redaktor.audycja AS $$ SELECT * FROM redaktor.audycja WHERE data_poczatek BETWEEN day_to_check AND day_to_check::date + interval '1 day' ORDER BY data_poczatek $$ LANGUAGE SQL;