AdvancedTopics: partitioning.sql

-- explain plan za analiza
EXPLAIN (ANALYZE, BUFFERS)
SELECT * from Zapisnik
WHERE datum BETWEEN '2024-03-01' AND '2024-03-31';


----dodavanje na datum vo zavisnite tabeli


ALTER TABLE Stavka_Zapisnik DISABLE TRIGGER USER;

ALTER TABLE Stavka_Zapisnik ADD COLUMN datum_zapisnik date;
UPDATE Stavka_Zapisnik sz
SET datum_zapisnik = z.datum
FROM Zapisnik z
WHERE sz.id_na_zapisnik = z.id_na_zapisnik;

ALTER TABLE Stavka_Zapisnik ENABLE TRIGGER USER;

ALTER TABLE Uplata ADD COLUMN datum_zapisnik date;
UPDATE Uplata u
SET datum_zapisnik = z.datum
FROM Zapisnik z
WHERE u.id_zapisnik = z.id_na_zapisnik;




---drop na constraint za foreign key

ALTER TABLE Stavka_Zapisnik DROP CONSTRAINT IF EXISTS stavka_zapisnik_id_na_zapisnik_fkey;
ALTER TABLE Uplata          DROP CONSTRAINT IF EXISTS uplata_id_zapisnik_fkey;


--preimenuvanje na zapisnik
ALTER TABLE Zapisnik RENAME TO Zapisnik_old;


-- ---- particionirana Zapisnik so kompoziten kluc(id_na_zapisnik, datum)
CREATE TABLE Zapisnik (
    id_na_zapisnik     int,
    vreme              time,
    datum              date NOT NULL,
    lokacija           varchar(100),
    Potpis             boolean DEFAULT false,
    id_slucaj          int,
    status_zapisnik    varchar(20) DEFAULT 'Otvoren' CHECK (status_zapisnik IN ('Otvoren', 'Zatvoren')),
    EMBG_Prekrsuvach   char(13),
    Vozilo_Broj_Sasija varchar(17),
    EMBG_Policaec      char(13),
    PRIMARY KEY (id_na_zapisnik, datum),
    FOREIGN KEY (id_slucaj)          REFERENCES Slucaj(id_slucaj)      ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (EMBG_Prekrsuvach)   REFERENCES Gragjanin(EMBG)        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (Vozilo_Broj_Sasija) REFERENCES Vozilo(broj_na_sasija) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (EMBG_Policaec)      REFERENCES Policaec(EMBG_P)       ON UPDATE CASCADE ON DELETE RESTRICT
) PARTITION BY RANGE (datum);



CREATE TABLE Zapisnik_2021 PARTITION OF Zapisnik FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
CREATE TABLE Zapisnik_2022 PARTITION OF Zapisnik FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE Zapisnik_2023 PARTITION OF Zapisnik FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE Zapisnik_2024 PARTITION OF Zapisnik FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE Zapisnik_2025 PARTITION OF Zapisnik FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE Zapisnik_2026 PARTITION OF Zapisnik FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE Zapisnik_default PARTITION OF Zapisnik DEFAULT;

----migracija na podatocite

INSERT INTO Zapisnik (id_na_zapisnik, vreme, datum, lokacija, Potpis,
                      id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec)
SELECT id_na_zapisnik, vreme, datum, lokacija, Potpis,
       id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec
FROM Zapisnik_old;

ANALYZE Zapisnik;

---- вrakanje na avtomatskata numeracija na id_na_zapisnik
ALTER TABLE Zapisnik ALTER COLUMN id_na_zapisnik SET DEFAULT nextval('zapisnik_id_na_zapisnik_seq');
ALTER SEQUENCE zapisnik_id_na_zapisnik_seq OWNED BY Zapisnik.id_na_zapisnik;
SELECT setval('zapisnik_id_na_zapisnik_seq', (SELECT COALESCE(MAX(id_na_zapisnik), 1) FROM Zapisnik));



---- Vraten integritetot
ALTER TABLE Stavka_Zapisnik
    ADD CONSTRAINT stavka_zapisnik_zapisnik_fk
    FOREIGN KEY (id_na_zapisnik, datum_zapisnik)
    REFERENCES Zapisnik (id_na_zapisnik, datum)
    ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE Uplata
    ADD CONSTRAINT uplata_zapisnik_fk
    FOREIGN KEY (id_zapisnik, datum_zapisnik)
    REFERENCES Zapisnik (id_na_zapisnik, datum)
    ON UPDATE CASCADE ON DELETE RESTRICT;




-- ---- Raspredelba po particii 
SELECT tableoid::regclass AS particija, COUNT(*) AS redovi
FROM Zapisnik GROUP BY 1 ORDER BY 1;

----Funkcija za avtomatsko kreiranje na particii za ponatamosni godini

CREATE OR REPLACE PROCEDURE avtomatiziraj_particija(p_godina int)
LANGUAGE plpgsql
AS $$
BEGIN

EXECUTE format(
'CREATE TABLE IF NOT EXISTS Zapisnik_%s PARTITION OF Zapisnik FOR VALUES FROM (%L) TO (%L);',
p_godina,
p_godina::text || '-01-01',
(p_godina + 1)::text || '-01-01'
);
RAISE NOTICE 'Партицијата за % година е успешно креирана.', p_godina;
END;
$$;
--povik na funkcijata
CALL avtomatiziraj_particija(2027);