init.sql

drop view IF EXISTS vCamionNonSature;
drop table IF EXISTS public.transport;
drop table IF EXISTS public.evenement;
drop type IF EXISTS type_evenement;
drop table IF EXISTS public.capteur;
drop view IF EXISTS vCommunication;
drop table IF EXISTS public.communication;
drop type IF EXISTS type_communication;
drop view IF EXISTS vVehicule;
drop view IF EXISTS vVoiture;
drop table IF EXISTS public.voiture;
drop type IF EXISTS type_voiture_speciale;
drop view IF EXISTS vMoto;
drop table IF EXISTS public.moto;
drop view IF EXISTS vCamion;
drop table IF EXISTS public.camion;
drop view IF EXISTS vInfrastructure;
drop table IF EXISTS public.infrastructure;
drop table IF EXISTS public.noeud;
drop table IF EXISTS public.position;
drop table IF EXISTS public.commune;

-- Table: public.commune

-- DROP TABLE public.commune;

CREATE TABLE public.commune
(
  code_postal integer NOT NULL,
  nom varchar,
  PRIMARY KEY (code_postal)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.commune
  OWNER TO postgres;

-- Table: public.position

-- DROP TABLE public.position;

CREATE TABLE public.position
(
  longitude double precision NOT NULL,
  latitude double precision NOT NULL,
  fk_code_postal integer,
  PRIMARY KEY (longitude, latitude),
  FOREIGN KEY (fk_code_postal) REFERENCES public.commune (code_postal) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.position
  OWNER TO postgres;

-- Table: public.noeud

-- DROP TABLE public.noeud;

CREATE TABLE public.noeud
(
  id integer NOT NULL,
  fk_longitude double precision,
  fk_latitude double precision,
  PRIMARY KEY (id),
  FOREIGN KEY (fk_longitude, fk_latitude) REFERENCES public.position (longitude, latitude) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.noeud
  OWNER TO postgres;

-- Table: public.infrastructure

-- DROP TABLE public.infrastructure;

CREATE TABLE public.infrastructure
(
  id_i integer UNIQUE NOT NULL,
  fk_noeud integer,
  PRIMARY KEY (fk_noeud),
  FOREIGN KEY (fk_noeud) REFERENCES public.noeud (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION

)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.infrastructure
  OWNER TO postgres;

CREATE VIEW vInfrastructure AS
  SELECT *
  FROM public.infrastructure i
  JOIN public.noeud n
  ON i.fk_noeud=n.id;

-- Table: public.camion

-- DROP TABLE public.camion;

CREATE TABLE public.camion
(
  fk_noeud integer,
  numero_immatriculation integer UNIQUE,
  marque varchar,
  modele varchar,
  annee_production integer,
  capacite_maximale integer,
  PRIMARY KEY (Fk_noeud),
  FOREIGN KEY (Fk_noeud) REFERENCES public.noeud (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION

)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.camion
  OWNER TO postgres;

CREATE VIEW vCamion AS
  SELECT *
  FROM public.camion c
  JOIN public.noeud n
  ON c.fk_noeud=n.id;

-- Table: public.moto

-- DROP TABLE public.moto;

CREATE TABLE public.moto
(
  fk_noeud integer,
  numero_immatriculation integer UNIQUE,
  marque varchar,
  modele varchar,
  annee_production integer,
  capacite_motoe integer,
  PRIMARY KEY (fk_noeud),
  FOREIGN KEY (fk_noeud) REFERENCES public.noeud (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.moto
  OWNER TO postgres;

CREATE VIEW vMoto AS
  SELECT *
  FROM public.moto m
  JOIN public.noeud n
  ON m.fk_noeud=n.id;

-- Table: public.voiture

-- DROP TABLE public.voiture

CREATE TYPE type_voiture_speciale AS enum('ordinaire','voiture_SAMU','voiture_policier','voiture pompier');
CREATE TABLE public.voiture
(
  fk_noeud integer,
  numero_immatriculation integer UNIQUE,
  marque varchar,
  modele varchar,
  annee_production integer,
  type_voiture type_voiture_speciale NOT NULL,
  PRIMARY KEY (fk_noeud),
  FOREIGN KEY (fk_noeud) REFERENCES public.noeud (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.voiture
  OWNER TO postgres;

CREATE VIEW vVoiture AS
  SELECT *
  FROM public.voiture v
  JOIN public.noeud n
  ON v.fk_noeud=n.id;

-- View: vVehicule

CREATE VIEW vVehicule AS
  SELECT fk_noeud FROM vVoiture
  UNION
  SELECT fk_noeud FROM vMoto
  UNION
  SELECT fk_noeud FROM vCamion;

-- Table: public.communication

-- DROP TABLE public.communication;

CREATE TYPE type_communication AS enum('un', 'plusieur');
CREATE TABLE public.communication
(
  id_c integer NOT NULL,
  fk_noeud_emetteur integer NOT NULL,
  fk_noeud_recepteur integer NOT NULL,
  stamp date,
  type_communication type_communication NOT NULL,
  PRIMARY KEY (id_c),
  FOREIGN KEY (fk_noeud_emetteur) REFERENCES public.noeud (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  FOREIGN KEY (fk_noeud_recepteur) REFERENCES public.noeud (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT communication_check_diff CHECK (fk_noeud_emetteur <> fk_noeud_recepteur)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.communication
  OWNER TO postgres;

CREATE VIEW vCommunication AS
  SELECT *,
    CASE WHEN fk_noeud_emetteur IN (SELECT fk_noeud FROM vVehicule) THEN 'un'
    ELSE 'vrai'
    END as "vrai_type"
  from public.communication;

-- Table: public.capteur

-- DROP TABLE public.capteur;

CREATE TABLE public.capteur
(
  fk_noeud integer,
  numero_serie varchar NOT NULL,
  modele varchar NOT NULL,
  PRIMARY KEY (fk_noeud,numero_serie),
  FOREIGN KEY (fk_noeud) REFERENCES public.noeud (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.capteur
  OWNER TO postgres;


-- Table: public.evenement

-- DROP TABLE public.evenement

CREATE TYPE type_evenement AS enum('accident','alert_meteo','detection_materiel','traveau_routiers');
CREATE TABLE public.evenement
(
  id_e integer NOT NULL,
  stamp date,
  contenu varchar NOT NULL,
  fk_com integer,
  fk_capteur_noeud integer NOT NULL,
  fk_capteur_serie varchar NOT NULL,
  gravité integer,
  nombre_véhicules integer,
  types_véhicules varchar,
  temperature double precision,
  type type_evenement,
  PRIMARY KEY (contenu,fk_com),
  FOREIGN KEY (fk_com) REFERENCES public.communication (id_c) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  FOREIGN KEY (fk_capteur_noeud,fk_capteur_serie) REFERENCES public.capteur (fk_noeud,numero_serie) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.evenement
  OWNER TO postgres;

-- Table: public.transport

-- DROP TABLE public.transport;

CREATE TABLE public.transport
(
  fk_voiture integer,
  fk_camion integer,
  PRIMARY KEY (fk_voiture,fk_camion),
  FOREIGN KEY (fk_camion) REFERENCES public.camion (fk_noeud) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  FOREIGN KEY (fk_voiture) REFERENCES public.voiture (fk_noeud) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.transport
  OWNER TO postgres;


CREATE VIEW vCamionNonSature AS
  SELECT c.fk_noeud, count(*)
  FROM public.camion c
  LEFT JOIN public.transport t
  ON c.fk_noeud=t.fk_camion
  GROUP BY c.fk_noeud
  HAVING count(*) <= c.capacite_maximale;


insert into public.commune (code_postal ,nom )  values (60200, 'compiegne');
insert into public.commune (code_postal ,nom )  values (60100, 'creil');
insert into public.commune (code_postal ,nom )  values (60300, 'senlis');
insert into public.commune (code_postal ,nom )  values (60000, 'beauvais');

insert into public.position (longitude,latitude,fk_code_postal)  values ( 2.8261 , 49.4179,60200);
insert into public.position (longitude,latitude,fk_code_postal)  values ( 2.0833, 49.4333,60000);
insert into public.position (longitude,latitude,fk_code_postal)  values ( 2.4833, 49.2667,60100);
insert into public.position (longitude,latitude,fk_code_postal)  values ( 2.5833, 49.2,60300);

insert into public.noeud (id, fk_longitude ,fk_latitude )values (1, 2.8261 , 49.4179);
insert into public.noeud (id, fk_longitude ,fk_latitude )values (2, 2.0833, 49.4333);
insert into public.noeud (id, fk_longitude ,fk_latitude )values (3, 2.4833, 49.2667);
insert into public.noeud (id, fk_longitude ,fk_latitude )values (4, 2.5833, 49.2);
insert into public.noeud (id, fk_longitude ,fk_latitude )values (5, 2.5833, 49.2);
insert into public.noeud (id, fk_longitude ,fk_latitude )values (6, 2.5833, 49.2);

insert into public.infrastructure (id_i ,fk_noeud )  values (1, 1);
insert into public.infrastructure (id_i ,fk_noeud )  values (2, 3);

insert into public.camion (fk_noeud, numero_immatriculation, marque, modele,annee_production, capacite_maximale)
		   values (2,10000,'marque a','modele a',1970,1);

insert into public.moto (fk_noeud, numero_immatriculation, marque, modele,annee_production, capacite_motoe)
		   values (4,10001,'marque b','modele b',1974,2);

insert into public.voiture (fk_noeud, numero_immatriculation, marque, modele,annee_production, type_voiture)
		   values (5,10002,'marque c','modele c',1954,'ordinaire');
insert into public.voiture (fk_noeud, numero_immatriculation, marque, modele,annee_production, type_voiture)
		   values (6,10003,'marque c','modele c',2004,'ordinaire');

insert into public.communication (id_c, fk_noeud_emetteur, fk_noeud_recepteur, stamp, type_communication)
		   values (1,1,3,'2001-09-28','un');
insert into public.communication (id_c, fk_noeud_emetteur, fk_noeud_recepteur, stamp, type_communication)
		   values (2,3,1,'2001-09-28','plusieur');
insert into public.communication (id_c, fk_noeud_emetteur, fk_noeud_recepteur, stamp, type_communication)
		   values (3,3,5,'2001-09-28','plusieur');
insert into public.communication (id_c, fk_noeud_emetteur, fk_noeud_recepteur, stamp, type_communication)
		   values (4,5,4,'2002-09-28','un');

insert into public.capteur (fk_noeud, numero_serie, modele) values (1,'serie 1','model 1');
insert into public.capteur (fk_noeud, numero_serie, modele) values (1,'serie 2','model 1');
insert into public.capteur (fk_noeud, numero_serie, modele) values (3,'serie 1','model 1');
insert into public.capteur (fk_noeud, numero_serie, modele) values (2,'serie 3','model 2');

insert into public.evenement (id_e, stamp, contenu, fk_com, fk_capteur_noeud, fk_capteur_serie, gravité, nombre_véhicules, types_véhicules, temperature, type)
		   values (1,'2001-09-28','warning',1,1,'serie 1',null,null,null,-20,'alert_meteo');
insert into public.evenement (id_e, stamp, contenu, fk_com, fk_capteur_noeud, fk_capteur_serie, gravité, nombre_véhicules, types_véhicules, temperature, type)
		   values (2,'2001-09-28','accident',2,3,'serie 1',10,2,'ordinaire',null,'accident');

insert into public.transport (fk_voiture, fk_camion) values (5,2);
insert into public.transport (fk_voiture, fk_camion) values (6,2);