Persy_Jackson, PostgreSQL

Persy_Jackson

CREATE TABLE Camp(
    Camp_id Serial  PRIMARY KEY, 
    Camp_name VARCHAR(30) NOT NULL, 
    origin  VARCHAR(30) NOT NULL
    );
    
create table Gods(
    God_id SERIAL PRIMARY KEY, 
    god_name varchar(30) not null, 
    origin varchar(30) not null, 
    camp_id integer not null, 
    god_side varchar(30) CHECK (god_side IN ('good', 'evil', 'neutral')) , 
    FOREIGN KEY(camp_id) REFERENCES Camp(camp_id) ON DELETE CASCADE
    );

create table Threat(
    threat_id SERIAL PRIMARY KEY,
    threat text NOT NULL
    );
    
create table Search1(
    Search_id SERIAL PRIMARY KEY, 
    Search_name VARCHAR(30) NOT NULL, 
    task text NOT NULL, 
    threat_id INTEGER NOT NULL, 
    FOREIGN KEY(threat_id) REFERENCES Threat(threat_id) ON DELETE CASCADE
    );
    
create table Allies(
    allie_id SERIAL PRIMARY KEY, 
    allie_name VARCHAR(30) NOT NULL, 
    powers VARCHAR(30) NOT NULL, 
    search_id INTEGER NOT NULL,
    FOREIGN KEY(search_id) REFERENCES Search1(search_id) ON DELETE CASCADE
    );

create table Halfbloods(
    hb_id SERIAL PRIMARY KEY, 
    name VARCHAR(30) NOT NULL, 
    side VARCHAR(30) CHECK (side IN('good', 'evil', 'neutral')), 
    powers VARCHAR(40) NOT NULL, 
    camp_id INTEGER NOT NULL, 
    search_id INTEGER NOT NULL, 
    parent_id INTEGER NOT NULL, 
    allie_id INTEGER NOT NULL, 
    FOREIGN KEY(search_id) REFERENCES Search1(search_id) ON DELETE CASCADE, 
    FOREIGN KEY(camp_id) REFERENCES Camp(camp_id) ON DELETE CASCADE, 
    FOREIGN KEY(parent_id) REFERENCES Gods(god_id) ON DELETE CASCADE, 
    FOREIGN KEY(allie_id) REFERENCES Allies(allie_id) ON DELETE CASCADE
    );

create table Fog(
    fog_id SERIAL PRIMARY KEY, 
    purpose text NOT NULL, 
    working_time INTEGER NOT NULL
    );

create table Artifact(
    art_id SERIAL PRIMARY KEY, 
    name VARCHAR(30) NOT NULL, 
    fog_id INTEGER NOT NULL, 
    god_id INTEGER NOT NULL, 
    FOREIGN KEY(fog_id) REFERENCES Fog(fog_id) ON DELETE CASCADE, 
    FOREIGN KEY(god_id) REFERENCES Gods(god_id) ON DELETE CASCADE
    );

create table Myths(
    myth_id SERIAL PRIMARY KEY, 
    myth_text TEXT NOT NULL, 
    search_id INTEGER NOT NULL,
    FOREIGN KEY(search_id) REFERENCES Search1(search_id) ON DELETE CASCADE
    );

create table Magic_place(
    place_id SERIAL PRIMARY KEY, 
    place_name VARCHAR(30) NOT NULL, 
    history TEXT NOT NULL, 
    fog_id INTEGER NOT NULL, 
    FOREIGN KEY(fog_id) REFERENCES Fog(fog_id) ON DELETE CASCADE
    );

create table Monsters(
    monster_id SERIAL PRIMARY KEY, 
    monster_name VARCHAR(30) NOT NULL, 
    powers TEXT NOT NULL
    );

create table Kill(
    monster_id INTEGER NOT NULL, 
    hb_id INTEGER NOT NULL, 
    FOREIGN KEY(monster_id) REFERENCES Monsters(monster_id) ON DELETE CASCADE, 
    FOREIGN KEY(hb_id) REFERENCES Halfbloods(hb_id) ON DELETE CASCADE
    );

create table Use(
    hb_id INTEGER NOT NULL, 
    art_id INTEGER NOT NULL, 
    FOREIGN KEY(hb_id) REFERENCES Halfbloods(hb_id) ON DELETE CASCADE, 
    FOREIGN KEY(art_id) REFERENCES Artifact(art_id) ON DELETE CASCADE
    );

create table Goes_through(
    search_id INTEGER NOT NULL, 
    place_id INTEGER NOT NULL, 
    FOREIGN KEY(search_id) REFERENCES Search1(search_id) ON DELETE CASCADE, 
    FOREIGN KEY(place_id) REFERENCES Magic_place(place_id) ON DELETE CASCADE
    );

create table Give_information (
    god_id INTEGER NOT NULL, 
    myth_id INTEGER NOT NULL, 
    FOREIGN KEY(god_id) REFERENCES Gods(god_id) ON DELETE CASCADE, 
    FOREIGN KEY(myth_id) REFERENCES Myths(myth_id) ON DELETE CASCADE
    );

create table Give(
    god_id INTEGER NOT NULL, 
    search_id INTEGER NOT NULL, 
    FOREIGN KEY(god_id) REFERENCES Gods(god_id) ON DELETE CASCADE, 
    FOREIGN KEY(search_id) REFERENCES Search1(search_id) ON DELETE CASCADE
    );

create table Connected_to(
    god_id INTEGER NOT NULL, 
    threat_id INTEGER NOT NULL, 
    FOREIGN KEY(god_id) REFERENCES Gods(god_id), 
    FOREIGN KEY(threat_id) REFERENCES Threat(threat_id)
    );

insert into Camp(Camp_name, origin) Values('Camp Haflblood', 'Greece');

insert into Gods(god_name, origin, camp_id, god_side) Values('Zeus', 'Greece', 1, 'good');
insert into Gods(god_name, origin, camp_id, god_side) values('Hades', 'Greece', 1, 'neutral');
insert into Gods(god_name, origin, camp_id, god_side) values('Athene', 'Greece', 1, 'good');
insert into Gods(god_name, origin, camp_id, god_side) values('Poseidon', 'Greece', 1, 'good');
insert into Gods(god_name, origin, camp_id, god_side) values('Ares', 'Greece', 1, 'evil');
insert into Gods(god_name, origin, camp_id, god_side) values('Aphrodite', 'Greece', 1, 'good');
insert into Gods(god_name, origin, camp_id, god_side) values(' Hephaestus ', 'Greece', 1, 'good');

insert into Threat(threat) values('Kronos. War with gods');
insert into Threat(threat) values('The lightning thief');

insert into Connected_to(god_id, threat_id) values(1, 1);
insert into Connected_to(god_id, threat_id) values(2, 1);
insert into Connected_to(god_id, threat_id) values(4, 1);
insert into Connected_to(god_id, threat_id) values(2, 1);
insert into Connected_to(god_id, threat_id) values(2, 2);

insert into Search1(search_name, task, threat_id) values('Find lightnings', 'Find stolen lightnings and go to Hades', 2);
insert into Search1(search_name, task, threat_id) values('Info search about Kronos', 'Find information about Kronos’ army', 1);

insert into Allies(allie_name, powers, search_id) values('Loopy', 'strength', 2);
insert into Allies(allie_name, powers, search_id) values('Grouver', 'speed, strength', 1);

insert into Halfbloods(name, side, powers, camp_id, search_id, parent_id, allie_id) values('Percy Jackson', 'good', 'water control, reaction', 1, 1, 4, 1);
insert into Halfbloods(name, side, powers, camp_id, search_id, parent_id, allie_id) values('Annabeth Chase' , 'good', 'rational thinking, intelligence', 1, 1, 3, 1);
insert into Halfbloods(name, side, powers, camp_id, search_id, parent_id, allie_id) values('Mike Chan' , 'good', 'combat skills, reaction', 1, 2, 5, 2);
insert into Halfbloods(name, side, powers, camp_id, search_id, parent_id, allie_id) values('July Black' , 'good', 'beaty, reaction, persuasion', 1, 2, 6, 2);
insert into Halfbloods(name, side, powers, camp_id, search_id, parent_id, allie_id) values('Arthur Bowen' , 'good', 'fire control, mechanism control', 1, 2, 7, 2);

insert into Fog(purpose, working_time) values('hide place', 24);
insert into Fog(purpose, working_time) values('hide objects', 48);

insert into Artifact(name, fog_id, god_id) values('Anaklusmos', 2, 4);
insert into Artifact(name, fog_id, god_id) values('Zeus lightnings', 2, 1);

insert into Magic_place(place_name, history, fog_id) values('Hades', 'Greek underworld. People, monsters and halfbloods are sent here when they die', 1);

insert into Myths(myth_text, search_id) values('When Zeus was born, however, Rhea hid him in Crete and tricked Cronus into swallowing a stone instead. Zeus grew up, forced Cronus to disgorge his brothers and sisters, waged war on Cronus, and was victorious. After his defeat by Zeus, Cronus became a prisoner in Tartarus', 2);

insert into Monsters(monster_name, powers) values('Gorgon', 'Make from people stones');
insert into Monsters(monster_name, powers) values('Hydra', 'Creates new heads, shots with fire');

insert into Kill(monster_id, hb_id) values (1, 3);
insert into Kill(monster_id, hb_id) values (1, 4);
insert into Kill(monster_id, hb_id) values (2, 5);

insert into Use(hb_id, art_id) values(1, 1);

insert into Goes_through(search_id, place_id) values(1, 1);

insert into Give_information(god_id, myth_id) values(1, 1);

insert into Give(god_id, search_id) values(3, 2);
insert into Give(god_id, search_id) values(2, 1);

-- Сколько полукровок находится в поиске 
CREATE FUNCTION hb_in_search (name_of_search VARCHAR(30))
RETURNS INTEGER AS $$
DECLARE
i integer;
BEGIN 
    SELECT COUNT(hb_id) FROM halfbloods into i INNER JOIN search1 ON halfbloods.search_id = search1.search_id 
    AND search1.search_name = name_of_search;
RETURN i;
END;
$$ LANGUAGE plpgsql;

SELECT hb_in_search('Find lightnings') AS HALFBLOODS_IN_SEARCH;

-- Вывод богов по их стороне
CREATE FUNCTION gods_side (side VARCHAR(30))
RETURNS SETOF VARCHAR(30) AS $$
    SELECT god_name FROM Gods WHERE gods.god_side = side;
$$ LANGUAGE sql;

SELECT gods_side('good') as good_gods;

-- Количество убитых полукровок
CREATE FUNCTION dead_hb ()
RETURNS INTEGER AS $$
DECLARE
i INTEGER;
BEGIN
    SELECT COUNT(hb_id) FROM kill into i;
    RETURN i;
END;
$$ LANGUAGE plpgsql;

SELECT dead_hb() as dead_halfbloods;

-- Смена стороны полукровки
CREATE FUNCTION side_changing (new_side VARCHAR(30), hb_name VARCHAR(30))
RETURNS void AS $$
BEGIN
    if new_side != 'good' and new_side != 'evil' and new_side != 'neutral' then
        raise exception 'Некорректное название стороны';
    else 
        begin
            UPDATE halfbloods SET side = new_side WHERE name = hb_name;
        end;
    end if;
    Return;
END;
$$ LANGUAGE plpgsql;

-- добавление нового монстра или изменение данных существующего
CREATE FUNCTION new_monster (integer, varchar(30), text) 
RETURNS void AS $$
DECLARE vns character(6);
BEGIN
if $2 is null   then
   raise exception 'Некорректное имя монстра';
else
   begin
      if $3 is null   then
         raise exception 'Некорректные силы монстра';
      else
         begin 
            if $1 is null   then
              INSERT INTO monsters (monster_name, powers) VALUES ($2, $3);
            else
              UPDATE monsters SET monster_name=$2, powers=$3 where monster_id=$1;
            end if;
            Return;
         end;
      end if;
   end;
end if;
exception when integrity_constraint_violation then
  raise exception 'Нарушение ограничений целостности';
END;
$$ LANGUAGE plpgsql;

--SELECT new_monster(null, 'Minotavr', 'power');
--SELECT * FROM monsters;

--триггер для удаления угрозы
CREATE FUNCTION threat_del() 
RETURNS trigger AS $$
BEGIN
    if (SELECT COUNT(*) FROM connected_to WHERE OLD.threat_id = connected_to.threat_id) > 0
    then DELETE from connected_to where OLD.threat_id = connected_to.threat_id;
    end if;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER threat_bef_del
BEFORE DELETE ON threat FOR EACH ROW
EXECUTE PROCEDURE threat_del();

--DELETE FROM threat WHERE threat_id = 2;

--SELECT * FROM threat;
--SELECT * FROM connected_to;

--триггер для автозаполения
CREATE FUNCTION camp_trigger()
RETURNS trigger AS $$
BEGIN
    if NEW.camp_name is null then
        NEW.camp_name = 'Camp_'||to_char(NEW.camp_id, '9');
    end if;
    if NEW.origin is null then
        NEW.origin = 'This is the camp origin';
    end if;
    return NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER camp_ins
BEFORE INSERT ON camp FOR EACH ROW
EXECUTE PROCEDURE camp_trigger();

--INSERT INTO camp values(2, null, null);

--индексы
CREATE INDEX hb_id_index ON halfbloods (hb_id);
CREATE INDEX god_index ON gods ((origin || ' ' || god_side));


run \| edit \| history \| help	0