--
-- Patch DDL de ECL du 21/09/2016 à exécuter depuis le user GRHUM
--
-- Rem : fichier encodé UTF-8

SET SQLBLANK ON
SET DEFINE OFF
SET SERVEROUTPUT ON
--
--
-- Type : DDL
-- Schema : ECL

WHENEVER SQLERROR EXIT SQL.SQLCODE;

create sequence ECL.THEME_CONFERENCE_ID_SEQ
increment by 1
start with 1
 maxvalue 9999999999999999999999999999999999999999999999999999
 minvalue 0
nocycle
 nocache
noorder;

/*==============================================================*/
/* Table : THEME_CONFERENCE                                     */
/*==============================================================*/
create table ECL.THEME_CONFERENCE  (
   ID                   NUMBER(6)                   not null,
   LIBELLE              VARCHAR2(100)               not null,
   THEME_CSS            VARCHAR2(1),
   TEM_VALIDE           NUMBER(1)       DEFAULT 1   not null,
   constraint PK_THEME_CONFERENCE primary key (ID)
);
/
COMMENT ON COLUMN ECL.THEME_CONFERENCE.ID IS 'Clef primaire de la table THEME_CONFERENCE';
COMMENT ON COLUMN ECL.THEME_CONFERENCE.LIBELLE IS 'Le libelle de la demande';
COMMENT ON COLUMN ECL.THEME_CONFERENCE.THEME_CSS IS 'Theme css associe pour affichage (a..z)';
COMMENT ON COLUMN ECL.THEME_CONFERENCE.TEM_VALIDE IS 'Valideite du theme (0/1)';
COMMENT ON TABLE ECL.THEME_CONFERENCE IS 'Demandes possibles';
/
/*==============================================================*/
/* Index : UK_THEME_CONFERENCE                              */
/*==============================================================*/
create unique index ECL.UK_THEME_CONFERENCE on ECL.THEME_CONFERENCE (
   THEME_CSS ASC,
   TEM_VALIDE ASC
);
/
declare
  v_column_exists number := 0;  
BEGIN
  Select count(*) into v_column_exists
    from ALL_TAB_COLS
    where column_name = 'THEME'
    and table_name = 'CONFERENCES'
    and owner = 'ECL';

  if (v_column_exists = 1) then
      execute immediate 'alter table ecl.CONFERENCES drop COLUMN THEME';
  end if;

Select count(*) into v_column_exists
    from ALL_TAB_COLS
    where column_name = 'THEME_ID'
    and table_name = 'CONFERENCES'
    and owner = 'ECL';

  if (v_column_exists = 0) then
      execute immediate 'alter table ecl.CONFERENCES add (theme_id number(6) )';
      execute immediate 'COMMENT ON COLUMN ECL.CONFERENCES.THEME_ID IS ''Cle étrangère vers ecl.theme_conference''';
      execute immediate 'alter table ECL.CONFERENCES add constraint FK_CONF_REF_THEME foreign key (THEME_ID) references ECL.THEME_CONFERENCE (ID)';
  end if;
end;
/
