-- This is for the migration from Core Cera-2.4 to Cera 2.5
-- ...and for the migration of the modules
--                                      ft, 2001-03-23

--
--
-- New CERA Block LANGUAGES
--
--
create table LANGUAGE       (
       language_id     number(15)    PRIMARY KEY,
       language_name   varchar(250)  UNIQUE NOT NULL
       );
insert into LANGUAGE values ( 0, 'not filled');
insert into LANGUAGE values ( 1, 'English');
insert into LANGUAGE values ( 2, 'German');

create sequence SEQ_LANGUAGE start with 3;

create table LANGUAGES      (
       entry_id             number(15)   NOT NULL constraint LANG_ENTR references ENTRY on delete cascade,
       language_id          number(15)   NOT NULL constraint LANG_LANG references LANGUAGE
       );


--
--
-- add column to table Parameter
--
alter table Parameter   add  data_org_seq       number ( 4);
update      Parameter   set  data_org_seq=0;
alter table Parameter   modify  data_org_seq  NOT NULL;
--
--
-- make all *.*_acronym UNIQUE (except for Topic)
--
alter table Entry     add UNIQUE (Entry_acronym);
alter table Project   add UNIQUE (Project_acronym);
alter table Unit      add UNIQUE (Unit_acronym);
alter table Fees      add UNIQUE (Fees_acronym);
alter table Format    add UNIQUE (Format_acronym);
alter table Access    add UNIQUE (Access_acronym);
--
-- make Topic.topic_name UNIQUE
--
alter table Topic     add UNIQUE (Topic_name);
--
-- in case of constraint violations:
select count(*),topic_acronym from TOPIC
 group by topic_acronym having count(*)>1;
select * from TOPIC where topic_acronym='dunes';
--
--
--
-- ------------------------------------------------------------------
--
--
-- Module DATA_ORGANIZATION  1.2 ==>  1.3
--
--
-- Firstly: providing "*_acronym*31, *_name*250, *_descr*2000" for LOV and 
--     most Entities
--
alter table DATA_ORG    add data_org_acronym   varchar(31)   UNIQUE;
update      DATA_ORG  D set data_org_acronym = (
     select data_org_id ||':'||substr(data_org_descr  ,1,25 )
       from DATA_ORG  where DATA_ORG.data_org_id = D.data_org_id);
alter table DATA_ORG    modify data_org_acronym NOT NULL;

alter table DATA_ORG    add data_org_name      varchar(250)        ;
update      DATA_ORG  D set data_org_name    = (
     select data_org_id ||':'||substr(data_org_descr  ,1,200)
       from DATA_ORG  where DATA_ORG.data_org_id = D.data_org_id);
alter table DATA_ORG    modify data_org_name    NOT NULL;
--
--
alter table SPACE       add space_acronym      varchar(31)   UNIQUE;
update      SPACE     D set space_acronym    = (
     select space_id    ||':'||substr(space_descr     ,1,25 )
       from SPACE     where SPACE   .space_id    = D.space_id   );
alter table SPACE       modify space_acronym    NOT NULL;

alter table SPACE       add space_name         varchar(250)        ;
update      SPACE     D set space_name       = (
     select space_id    ||':'||substr(space_descr     ,1,200)
       from SPACE     where SPACE   .space_id    = D.space_id   );
alter table SPACE       modify space_name    NOT NULL;
--
--
alter table POINT_SET   add point_set_acronym  varchar(31)   UNIQUE;
update      POINT_SET D set point_set_acronym    = (
     select point_set_id    ||':'||substr(point_set_name      ,1,25 )
       from POINT_SET where POINT_SET.point_set_id= D.point_set_id   );
alter table POINT_SET   modify point_set_acronym NOT NULL;

alter table POINT_SET   add point_set_descr    varchar(2000)       ;
update      POINT_SET D set point_set_descr      = (
     select point_set_id    ||':'|| point_set_name
       from POINT_SET where POINT_SET.point_set_id= D.point_set_id   );
alter table POINT_SET   modify point_set_descr   NOT NULL;
--
--
alter table SCALE       add scale_acronym      varchar(31)   UNIQUE;
update      SCALE     D set scale_acronym    = (
     select scale_id    ||':'||substr(scale_name      ,1,25 )
       from SCALE     where SCALE    .scale_id= D.scale_id   );
alter table SCALE       modify scale_acronym NOT NULL;

alter table SCALE       add scale_descr        varchar(2000)       ;
update      SCALE     D set scale_descr      = (
     select scale_id    ||':'|| scale_name
       from SCALE     where SCALE.scale_id   = D.scale_id   );
alter table SCALE       modify scale_descr   NOT NULL;
--
--
alter table TIME        add time_acronym       varchar(31)   UNIQUE;
update      TIME      D set time_acronym    = (
     select time_id    ||':'||substr(time_name      ,1,25 )
       from TIME      where TIME     .time_id= D.time_id   );
alter table TIME        modify time_acronym NOT NULL;

alter table TIME        add time_descr         varchar(2000)       ;
update      TIME      D set time_descr      = (
     select time_id    ||':'|| time_name
       from TIME      where TIME .time_id   = D.time_id   );
alter table TIME        modify time_descr   NOT NULL;
--
--
alter table DIM_TYPE    modify dim_type_name   varchar(250)        ;
--
alter table DIM_TYPE    add dim_type_acronym   varchar(31)   UNIQUE;
update      DIM_TYPE  D set dim_type_acronym    = (
     select dim_type_id    ||':'||substr(dim_type_name      ,1,25 )
       from DIM_TYPE  where DIM_TYPE .dim_type_id= D.dim_type_id   );
alter table DIM_TYPE    modify dim_type_acronym NOT NULL;

alter table DIM_TYPE    add dim_type_descr     varchar(2000)       ;
update      DIM_TYPE  D set dim_type_descr      = (
     select dim_type_id    ||':'|| dim_type_name
       from DIM_TYPE  where DIM_TYPE .dim_type_id= D.dim_type_id   );
alter table DIM_TYPE    modify dim_type_descr   NOT NULL;
--
--
-- if not yet existing:
alter table TIME_CONNECT add sequence_no        number (15) ;
--
--
-- renaming Moment.utc_difference to utc_diff_hours by
--   making a new table "Moment":
--
alter table TIME_CONNECT  drop constraint TCON_MOME;
alter table POINT_CONNECT drop constraint PCON_MOME;
rename MOMENT to YESTERDAY;
create table MOMENT            (
       moment_id               number(15)    PRIMARY KEY          ,
       moment_name             varchar(80)   UNIQUE NOT NULL,
       year                    number(15)    DEFAULT 0   NOT NULL,
       month                   number(2)     DEFAULT 0   NOT NULL,
       day                     number(2)     DEFAULT 0   NOT NULL,
       hour                    number(2)     DEFAULT 0   NOT NULL,
       minute                  number(2)     DEFAULT 0   NOT NULL,
       second                  number(15,9)  DEFAULT 0   NOT NULL,
       utc_diff_hours          number(8,4)   DEFAULT 0   NOT NULL
       );
insert into MOMENT
(select moment_id,moment_name,year,month,day,hour,minute,second,utc_difference
   from YESTERDAY);
alter table TIME_CONNECT  add constraint TCON_MOME foreign key (moment_id) references Moment;
alter table POINT_CONNECT add constraint PCON_MOME foreign key (moment_id) references Moment;
drop table Yesterday;
--
--
--
-- ...finally making   SUBMODULE  PATCHED_DIMENSION  (=Submod. of Module DATA_ORG)
--
create table SCALE_PATCHES (
       scale_id      number(15)  DEFAULT 0  NOT NULL constraint SPAT_SCA1 references SCALE,
       patch_id      number(15)  DEFAULT 0  NOT NULL constraint SPAT_SCA2 references SCALE(scale_id),
       specifier     number(15)  DEFAULT 0  NOT NULL );
create table TIME_PATCHES  (
       time_id       number(15)  DEFAULT 0  NOT NULL constraint TPAT_TIM1 references TIME,
       patch_id      number(15)  DEFAULT 0  NOT NULL constraint TPAT_TIM2 references TIME(time_id),
       specifier     number(15)  DEFAULT 0  NOT NULL );
--
--
The END
