SET search_path = om5_temp, public, pg_catalog;
-- ce script permet de créer un obj dans un schema différent 

CREATE TABLE om_tables (
    table_name character varying(40) NOT NULL,
    table_schema character varying(40),
    table_type character varying(40),
    comment text,
    parametre text,
    libelle varchar(40),
    affichage integer,
    nombre_colonne integer,
    col1 varchar(40),
    col2 varchar(40),
    col3 varchar(40),
    recherche integer
);


CREATE TABLE om_forms (
    column_name character varying(120) NOT NULL, 
    table_name character varying(40),
    table_schema character varying(40),
    data_type character varying(40),
    character_maximum_length integer,
    is_nullable character varying(5),
    column_default character varying(80),
    comment text,
    parametres text,
    libelle varchar(80),
    libelle_court varchar(40),
    type varchar(40),
    bloc varchar(5),
    position integer,
    calcul varchar(255),
    liste integer,
    position_liste integer
);


CREATE table om_contraintes ( 
	constraint_name character varying(80) NOT NULL, 
	table_name character varying(40),
	column_name character varying(40), 
	foreign_table_name character varying(40), 
    foreign_column_name character varying(40)
);


create table om_vues (
	table_name character varying(80) not null,
	table_schema character varying(80),
	view_definition text
);	


CREATE TABLE om_actions(
  om_actions integer not null,
  libelle character varying(40) not null,
  module character varying(40) not null, 
  parametres jsonb,
  table_name character varying(40)
);

CREATE TABLE om_triggers (
	trigger_name varchar(80) not null,
    trigger_schema varchar(40),
    event_manipulation varchar(40),
    event_object_table varchar(40),
    action_order integer,
    action_condition varchar(80),
    action_statement varchar(255),
    action_orientation varchar(80),
    action_timing varchar(80)
);

CREATE TABLE om_proc(
	proname varchar(80) not null, 
	prosrc  text 
);




-- seq

CREATE SEQUENCE om_actions_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
    
-- pk

ALTER TABLE ONLY om_tables
    ADD CONSTRAINT om_tables_pkey PRIMARY KEY (table_name);
    
ALTER TABLE ONLY om_contraintes
    ADD CONSTRAINT om_contraintes_pkey PRIMARY KEY (constraint_name);

ALTER TABLE ONLY om_forms
    ADD CONSTRAINT om_forms_pkey PRIMARY KEY (column_name);

ALTER TABLE ONLY om_vues
    ADD CONSTRAINT om_vues_pkey PRIMARY KEY (table_name);

ALTER TABLE ONLY om_actions
    ADD CONSTRAINT om_actions_pkey PRIMARY KEY (om_actions);

ALTER TABLE ONLY om_triggers
    ADD CONSTRAINT om_triggers_pkey PRIMARY KEY (trigger_name);

ALTER TABLE ONLY om_proc
    ADD CONSTRAINT om_proc_pkey PRIMARY KEY (proname);
-- fk

ALTER TABLE ONLY om_contraintes
   ADD CONSTRAINT om_contraintes_om_tables_fkey FOREIGN KEY (table_name) REFERENCES om_tables(table_name);

ALTER TABLE ONLY om_forms
   ADD CONSTRAINT om_forms_table_name_fkey FOREIGN KEY (table_name) REFERENCES om_tables(table_name);

ALTER TABLE ONLY om_actions
   ADD CONSTRAINT om_actions_table_name_fkey FOREIGN KEY (table_name) REFERENCES om_tables(table_name);

ALTER TABLE ONLY om_triggers
   ADD CONSTRAINT om_triggers_event_object_table_fkey FOREIGN KEY (event_object_table) REFERENCES om_tables(table_name);

-- edition

ALTER TABLE ONLY om_etat
   ADD CONSTRAINT om_etat_om_tables_fkey FOREIGN KEY (table_name) REFERENCES om_tables(table_name);
   
ALTER TABLE ONLY om_sousetat
   ADD CONSTRAINT om_sousetat_om_tables_fkey FOREIGN KEY (table_name) REFERENCES om_tables(table_name);

ALTER TABLE ONLY om_requete
   ADD CONSTRAINT om_requete_om_tables_fkey FOREIGN KEY (table_name) REFERENCES om_tables(table_name);



