Changes between Version 10 and Version 11 of ddlScript


Ignore:
Timestamp:
09/22/25 23:22:21 (5 weeks ago)
Author:
223270
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript

    v10 v11  
    11{{{
    2 DROP SCHEMA travel_sage CASCADE;
    3 CREATE SCHEMA travel_sage;                   
    4 SET SEARCH_PATH TO travel_sage;             
     2CREATE SCHEMA IF NOT EXISTS travel_sage;
     3SET search_path TO travel_sage;
    54
    6 DROP TABLE IF EXISTS NASTANI CASCADE;
    7 DROP TABLE IF EXISTS PREFERENCI CASCADE;
    8 DROP TABLE IF EXISTS PAKETI CASCADE;
    9 DROP TABLE IF EXISTS RECENZII CASCADE;
    10 DROP TABLE IF EXISTS REZERVACII CASCADE;
    11 DROP TABLE IF EXISTS PREMIUM CASCADE;
    12 DROP TABLE IF EXISTS STANDARD CASCADE;
    13 DROP TABLE IF EXISTS KORISNICI CASCADE;
    14 DROP TABLE IF EXISTS AKTIVNOSTI CASCADE;
    15 DROP TABLE IF EXISTS METEROLOSHKASOSTOJBA CASCADE;
    16 DROP TABLE IF EXISTS DESTINACII CASCADE;
    17 DROP TABLE IF EXISTS TAGOVI CASCADE;
    18 DROP TABLE IF EXISTS destinacii_has_tagovi CASCADE;
    19 DROP TABLE IF EXISTS aktivnosti_has_paketi CASCADE;
    20 DROP TABLE IF EXISTS aktivnosti_has_rezervacii CASCADE;
    21 DROP TABLE IF EXISTS destinacii_has_korisnici CASCADE;
     5DROP TABLE IF EXISTS destination_user CASCADE;
     6DROP TABLE IF EXISTS activity_reservation CASCADE;
     7DROP TABLE IF EXISTS package_reservation CASCADE;
     8DROP TABLE IF EXISTS package_activity CASCADE;
     9DROP TABLE IF EXISTS preference CASCADE;
     10DROP TABLE IF EXISTS destination_tag CASCADE;
     11DROP TABLE IF EXISTS tag CASCADE;
     12DROP TABLE IF EXISTS event CASCADE;
     13DROP TABLE IF EXISTS activity CASCADE;
     14DROP TABLE IF EXISTS package CASCADE;
     15DROP TABLE IF EXISTS meteorological_condition CASCADE;
     16DROP TABLE IF EXISTS review CASCADE;
     17DROP TABLE IF EXISTS reservation CASCADE;
     18DROP TABLE IF EXISTS premium CASCADE;
     19DROP TABLE IF EXISTS standard CASCADE;
     20DROP TABLE IF EXISTS users CASCADE;
     21DROP TABLE IF EXISTS destination CASCADE;
    2222
    23 
    24 CREATE TABLE TAGOVI (
    25     idTag SERIAL PRIMARY KEY,
    26     tagOznaka VARCHAR(255) NOT NULL
     23CREATE TABLE IF NOT EXISTS destination (
     24    id_destination SERIAL PRIMARY KEY,
     25    location_name VARCHAR(255) NOT NULL,
     26    location_desc TEXT,
     27    types_of_places TEXT,   
     28    recommended_season TEXT,   
     29    average_temp NUMERIC(5,2),
     30    latitude DECIMAL(9,6),
     31    longitude DECIMAL(9,6),
     32    country VARCHAR(100),
     33    popularity INTEGER DEFAULT 0,
     34    important_location_name VARCHAR(255),
     35    important_location_description TEXT   
    2736);
    2837
    29 CREATE TABLE DESTINACII (
    30     idDest SERIAL PRIMARY KEY,
    31     imeLokacija VARCHAR(255) NOT NULL,
    32     opisLokacija VARCHAR(255),
    33     tipoviMesta VARCHAR(255) NOT NULL,
    34     preporachanaSezona VARCHAR(255) NOT NULL,
    35     prosechnaTemp DECIMAL(5, 2),
    36     lat DECIMAL(9, 6),   
    37     lon DECIMAL(9, 6),
    38     drzhava VARCHAR(255),
    39     popularnost INT,
    40     ime VARCHAR(255) NOT NULL,
    41     opis VARCHAR(255) NOT NULL,
    42     idTag INT,
    43     CONSTRAINT fk_tag_dest FOREIGN KEY (idTag) REFERENCES TAGOVI(idTag)
     38CREATE TABLE IF NOT EXISTS users (
     39    id_user SERIAL PRIMARY KEY,
     40    first_name VARCHAR(100) NOT NULL,
     41    last_name VARCHAR(100) NOT NULL,
     42    email VARCHAR(255) NOT NULL UNIQUE,
     43    phone_number  VARCHAR(30),
     44    birth_date DATE,
     45    is_premium BOOLEAN NOT NULL DEFAULT FALSE
    4446);
    4547
    46 CREATE TABLE METEROLOSHKASOSTOJBA (
    47     idMeteo SERIAL PRIMARY KEY,
    48     momentTemp DECIMAL(5, 2),
    49     sostojbaVreme VARCHAR(255) NOT NULL,
    50     predupreduvanja VARCHAR(255) NOT NULL,
    51     vlazhnost DECIMAL(5, 2),
    52     veter DECIMAL(5, 2),
    53     mesec INT,
    54     idDest INT,
    55     CONSTRAINT fk_dest_meteo FOREIGN KEY (idDest) REFERENCES DESTINACII(idDest)
     48CREATE TABLE IF NOT EXISTS standard (
     49    id_user INT PRIMARY KEY REFERENCES users(id_user) ON DELETE CASCADE
    5650);
    5751
    58 CREATE TABLE AKTIVNOSTI (
    59     idAktivnost SERIAL PRIMARY KEY,
    60     imeAktivnost VARCHAR(255) NOT NULL,
    61     informacii VARCHAR(255),
    62     kategorija VARCHAR(255) NOT NULL,
    63     iznos INT,
    64     idDest INT,
    65     CONSTRAINT fk_dest_aktivnost FOREIGN KEY (idDest) REFERENCES DESTINACII(idDest)
     52CREATE TABLE IF NOT EXISTS premium (
     53    id_user INT PRIMARY KEY REFERENCES users(id_user) ON DELETE CASCADE,
     54    date_created TIMESTAMP NOT NULL DEFAULT now(),
     55    discount NUMERIC(5,2)
    6656);
    6757
    68 CREATE TABLE KORISNICI (
    69     idKorisnik SERIAL PRIMARY KEY,
    70     ime VARCHAR(255) NOT NULL,
    71     prezime VARCHAR(255) NOT NULL,
    72     ePoshta VARCHAR(255) NOT NULL UNIQUE,
    73     telBr VARCHAR(255) NOT NULL,
    74     datumRagjanje DATE,
    75     idDest INT,
    76     CONSTRAINT fk_dest_korisnik FOREIGN KEY (idDest) REFERENCES DESTINACII(idDest)
     58CREATE TABLE IF NOT EXISTS reservation (
     59    id_reservation SERIAL PRIMARY KEY,
     60    id_user INT NOT NULL REFERENCES users(id_user) ON DELETE CASCADE,
     61    time_point TIMESTAMP NOT NULL DEFAULT now(),
     62    premium_discount_applied BOOLEAN NOT NULL DEFAULT FALSE,
     63    discount_amount NUMERIC(5,2) NOT NULL,
     64    total_price NUMERIC(12,2) NOT NULL
    7765);
    7866
    79 CREATE TABLE STANDARD (
    80     idKorisnik INT,
    81     ime VARCHAR(255) NOT NULL,
    82     prezime VARCHAR(255) NOT NULL,
    83     ePoshta VARCHAR(255) NOT NULL UNIQUE,
    84     telBr VARCHAR(255) NOT NULL,
    85     datumRagjanje DATE,
    86     CONSTRAINT fk_korisnik_standard FOREIGN KEY (idKorisnik) REFERENCES KORISNICI(idKorisnik)
     67CREATE TABLE IF NOT EXISTS review (
     68    id_review SERIAL PRIMARY KEY,
     69    username VARCHAR(255) NOT NULL,
     70    reservation_id INT UNIQUE REFERENCES reservation(id_reservation) ON DELETE CASCADE,
     71    id_destination INT NOT NULL REFERENCES destination(id_destination) ON DELETE CASCADE,
     72    quality SMALLINT NOT NULL CHECK (quality BETWEEN 1 AND 5),
     73    comment TEXT,
     74    review_date TIMESTAMP DEFAULT now(),
     75    vote_count INTEGER DEFAULT 0
    8776);
    8877
    89 CREATE TABLE PREMIUM (
    90     idKorisnik INT,
    91     ime VARCHAR(255) NOT NULL,
    92     prezime VARCHAR(255) NOT NULL,
    93     ePoshta VARCHAR(255) NOT NULL UNIQUE,
    94     telBr VARCHAR(255) NOT NULL,
    95     datumRagjanje DATE,
    96     datumKreiranje DATE,
    97     popust DECIMAL(5, 2),
    98     CONSTRAINT fk_korisnik_premium FOREIGN KEY (idKorisnik) REFERENCES KORISNICI(idKorisnik)
     78CREATE TABLE IF NOT EXISTS meteorological_condition (
     79    id_meteo SERIAL PRIMARY KEY,
     80    id_destination INT NOT NULL REFERENCES destination(id_destination) ON DELETE CASCADE,
     81    current_temp NUMERIC(5,2) NOT NULL,
     82    weather_condition VARCHAR(50) NOT NULL,
     83    warnings VARCHAR(100) NOT NULL,
     84    humidity NUMERIC(5,2),
     85    wind NUMERIC(6,2),
     86    month SMALLINT
    9987);
    10088
    101 CREATE TABLE REZERVACII (
    102     idRezervacija SERIAL PRIMARY KEY,
    103     vremenskaTochka DATE NOT NULL,
    104     vkupnaCena INT NOT NULL,
    105     idAktivnost INT,
    106     idKorisnik INT,
    107     idMeteo INT,
    108     CONSTRAINT fk_aktivnost_rezervacija FOREIGN KEY (idAktivnost) REFERENCES AKTIVNOSTI(idAktivnost),
    109     CONSTRAINT fk_korisnik_rezervacija FOREIGN KEY (idKorisnik) REFERENCES KORISNICI(idKorisnik),
    110     CONSTRAINT fk_meteo_rezervacija FOREIGN KEY (idMeteo) REFERENCES METEROLOSHKASOSTOJBA(idMeteo)
     89CREATE TABLE IF NOT EXISTS package (
     90    id_package SERIAL PRIMARY KEY,
     91    package_name VARCHAR(255) NOT NULL,
     92    id_destination INT NOT NULL REFERENCES destination(id_destination) ON DELETE CASCADE,
     93    price NUMERIC(12,2) NOT NULL,
     94    start_date DATE NOT NULL,
     95    end_date DATE NOT NULL
    11196);
    11297
    113 CREATE TABLE RECENZII (
    114     idRecenzija SERIAL PRIMARY KEY,
    115     korisnichkoIme VARCHAR(255) NOT NULL,
    116     kvalitet INT NOT NULL,
    117     zabeleshka VARCHAR(255),
    118     datumRecenzija DATE,
    119     brGlasovi INT,
    120     idDest INT,
    121     idKorisnik INT,
    122     idRezervacija INT,
    123     CONSTRAINT fk_dest_recenzija FOREIGN KEY (idDest) REFERENCES DESTINACII(idDest),
    124     CONSTRAINT fk_korisnik_recenzija FOREIGN KEY (idKorisnik) REFERENCES KORISNICI(idKorisnik),
    125     CONSTRAINT fk_rezervacija_recenzija FOREIGN KEY (idRezervacija) REFERENCES REZERVACII(idRezervacija)
     98CREATE TABLE IF NOT EXISTS activity (
     99    id_activity SERIAL PRIMARY KEY,
     100    activity_name VARCHAR(255) NOT NULL,
     101    id_destination INT REFERENCES destination(id_destination) ON DELETE CASCADE,
     102    information TEXT,
     103    amount NUMERIC(10,2),
     104    category VARCHAR(100) NOT NULL
    126105);
    127106
    128 CREATE TABLE PAKETI (
    129     idPaket SERIAL PRIMARY KEY,
    130     imePaket VARCHAR(255) NOT NULL,
    131     cena INT NOT NULL,
    132     pochetok TIMESTAMP NOT NULL,
    133     kraj TIMESTAMP NOT NULL,
    134     idDest INT,
    135     idAktivnost INT,
    136     idRezervacija INT,
    137     CONSTRAINT fk_dest_paket FOREIGN KEY (idDest) REFERENCES DESTINACII(idDest),
    138     CONSTRAINT fk_aktivnost_paket FOREIGN KEY (idAktivnost) REFERENCES AKTIVNOSTI(idAktivnost),
    139     CONSTRAINT fk_rezervacija_paket FOREIGN KEY (idRezervacija) REFERENCES REZERVACII(idRezervacija)
     107CREATE TABLE IF NOT EXISTS event (
     108    id_event SERIAL PRIMARY KEY,
     109    event_name VARCHAR(255) NOT NULL,
     110    id_destination INT NOT NULL REFERENCES destination(id_destination) ON DELETE CASCADE,
     111    start_date DATE,
     112    end_date DATE,
     113    details TEXT,
     114    event_type TEXT NOT NULL
    140115);
    141116
    142 CREATE TABLE PREFERENCI (
    143     idPreferenca SERIAL PRIMARY KEY,
    144     tipPreferenca VARCHAR(255) NOT NULL,
    145     prioritet INT,
    146     idKorisnik INT,
    147     CONSTRAINT fk_korisnik_preferenca FOREIGN KEY (idKorisnik) REFERENCES KORISNICI(idKorisnik)
     117CREATE TABLE IF NOT EXISTS tag (
     118    id_tag SERIAL PRIMARY KEY,
     119    tag_name VARCHAR(100) NOT NULL
    148120);
    149121
    150 CREATE TABLE NASTANI (
    151     idNastan SERIAL PRIMARY KEY,
    152     naziv VARCHAR(255) NOT NULL,
    153     vidovi VARCHAR(255) NOT NULL,
    154     pochetenDatum DATE,
    155     kraenDatum DATE,
    156     detali VARCHAR(255),
    157     idDest INT,
    158     CONSTRAINT fk_dest_nastan FOREIGN KEY (idDest) REFERENCES DESTINACII(idDest)
     122CREATE TABLE IF NOT EXISTS destination_tag (
     123    id_destination INT REFERENCES destination(id_destination) ON DELETE CASCADE,
     124    id_tag INT REFERENCES tag(id_tag) ON DELETE CASCADE,
     125    PRIMARY KEY(id_destination, id_tag)
    159126);
    160127
    161 CREATE TABLE destinacii_has_tagovi (
    162   destinacii_idDest INT NOT NULL,
    163   tagovi_idTag INT NOT NULL,
    164   PRIMARY KEY (destinacii_idDest, tagovi_idTag),
    165   FOREIGN KEY (destinacii_idDest) REFERENCES DESTINACII(idDest),
    166   FOREIGN KEY (tagovi_idTag) REFERENCES TAGOVI(idTag)
     128CREATE TABLE IF NOT EXISTS preference (
     129    id_preference SERIAL PRIMARY KEY,
     130    id_user INT NOT NULL REFERENCES users(id_user) ON DELETE CASCADE,
     131    priority INT,
     132    type_preference TEXT NOT NULL
    167133);
    168134
    169 CREATE TABLE aktivnosti_has_paketi (
    170   aktivnosti_idAktivnost INT NOT NULL,
    171   paketi_idPaket INT NOT NULL,
    172   PRIMARY KEY (aktivnosti_idAktivnost, paketi_idPaket),
    173   FOREIGN KEY (aktivnosti_idAktivnost) REFERENCES AKTIVNOSTI(idAktivnost),
    174   FOREIGN KEY (paketi_idPaket) REFERENCES PAKETI(idPaket)
     135CREATE TABLE IF NOT EXISTS package_activity (
     136    id_package INT REFERENCES package(id_package) ON DELETE CASCADE,
     137    id_activity INT REFERENCES activity(id_activity) ON DELETE CASCADE,
     138    PRIMARY KEY(id_package, id_activity)
    175139);
    176140
    177 CREATE TABLE aktivnosti_has_rezervacii (
    178   aktivnosti_idAktivnost INT NOT NULL,
    179   rezervacii_idRezervacija INT NOT NULL,
    180   PRIMARY KEY (aktivnosti_idAktivnost, rezervacii_idRezervacija),
    181   FOREIGN KEY (aktivnosti_idAktivnost) REFERENCES AKTIVNOSTI(idAktivnost),
    182   FOREIGN KEY (rezervacii_idRezervacija) REFERENCES REZERVACII(idRezervacija)
     141CREATE TABLE IF NOT EXISTS package_reservation (
     142    id_package INT REFERENCES package(id_package) ON DELETE CASCADE,
     143    id_reservation INT REFERENCES reservation(id_reservation) ON DELETE CASCADE,
     144    PRIMARY KEY(id_package, id_reservation)
    183145);
    184146
    185 CREATE TABLE destinacii_has_korisnici (
    186   destinacii_idDest INT NOT NULL,
    187   korisnici_idKorisnik INT NOT NULL,
    188   ocena INT NULL,
    189   komentar VARCHAR(100) NULL,
    190   datum DATE NULL,
    191   PRIMARY KEY (destinacii_idDest, korisnici_idKorisnik),
    192   FOREIGN KEY (destinacii_idDest) REFERENCES DESTINACII(idDest),
    193   FOREIGN KEY (korisnici_idKorisnik) REFERENCES KORISNICI(idKorisnik)
     147CREATE TABLE IF NOT EXISTS activity_reservation (
     148    id_activity INT REFERENCES activity(id_activity) ON DELETE CASCADE,
     149    id_reservation INT REFERENCES reservation(id_reservation) ON DELETE CASCADE,
     150    PRIMARY KEY(id_activity, id_reservation)
     151);
     152
     153CREATE TABLE IF NOT EXISTS destination_user (
     154    id_destination INT REFERENCES destination(id_destination) ON DELETE CASCADE,
     155    id_user INT REFERENCES users(id_user) ON DELETE CASCADE,
     156    rating SMALLINT CHECK (rating BETWEEN 1 AND 5),
     157    comment TEXT,
     158    recommendation_date TIMESTAMP DEFAULT now(),
     159    PRIMARY KEY(id_destination, id_user)
    194160);
    195161}}}