-- 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