create extension if not exists postgis; 

drop view if exists  v_parent; 
drop view if exists v_child_geo; 
drop view if exists v_child_attrib;

drop table child_geo;
drop table child_attrib;
drop table parent;

create table parent
(
  id uuid NOT NULL, -- Identifiant unique. Lien vers la table ouvrepere
  comment character varying, -- Commentaire sur la sécurité du personnel 
  geom geometry(LineString,2154),
  CONSTRAINT parent_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);


create table child_geo
(
  id uuid NOT NULL, 
  comment character varying,
 fk_parent uuid, -- parent fk id 
  geom geometry(Point, 2154),
  CONSTRAINT child_geo_pkey PRIMARY KEY (id),
  CONSTRAINT child_geo_parent_fk FOREIGN KEY (fk_parent)
      REFERENCES public.parent(id) MATCH SIMPLE
      ON UPDATE SET NULL ON DELETE SET NULL

)
WITH (
  OIDS=FALSE
);


create table child_attrib
(
  id uuid NOT NULL, 
  comment character varying, 
  fk_parent uuid, -- parent fk id 
  CONSTRAINT child_attrib_pkey PRIMARY KEY (id),
  CONSTRAINT child_attrib_parent_fk FOREIGN KEY (fk_parent)
      REFERENCES public.parent(id) MATCH SIMPLE
      ON UPDATE SET NULL ON DELETE SET NULL

)
WITH (
  OIDS=FALSE
);


create view v_parent as 

( SELECT id, comment, geom::geometry(LINESTRING, 2154) geom
  FROM public.parent
);


Create view v_child_geo as (

SELECT id, comment, fk_parent, geom::geometry(POINT, 2154) geom
  FROM public.child_geo
 )
 ;

 Create view v_attrib_geo as (

SELECT id, comment, fk_parent
  FROM public.child_attrib
 )
 ;





