---------------------------------------------------------
-- Before usage, please query replace the following    --
-- constants to fit your database environment:	       --
-- * M4OLD: tablespace containing the original M4 data --
-- * M4NEW: tablespace for the transformed data	       --
--                                                     --
-- Then you should run the M4 installer script for the --
-- new  tablespace.   This is  necessary in  order to  --
-- create  and fill the  system tables  not touched by --
-- this converter script.                              --
--                                                     --
-- Finally run this script. 
---------------------------------------------------------

-- Tables to be created by the installer script:
-- * OP_CONSTR_T
-- * OP_COND_T
-- * OP_ASSERT_T

-- Remaining problems:
-- * STEP_T and some other tables need a NAME (NOT NULL!).
--   This and all other needs for additional information are
--   currently ignored!
-- * No constraints are generated by the converter.

DROP TABLE M4NEW.COL_DATATYPE_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.OP_TYPE_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.COLUMNSET_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.CONCEPT_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.CONCEPTISA_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.PROJECTION_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.MCFEATURE_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.COLUMN_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.KEYHEAD_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.KEYMEMBER_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.CSSTATIST_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.COLSTATIST1_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.COLSTATIST2_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.VALUE_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.ROLERESTRICTION_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.CASE_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.CASEINPUT_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.CASEATTRIB_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.OPERATOR_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.PARAMETER_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.STEP_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.STEPSEQUENCE_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.RELATION_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.RELATIONISA_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.BASEATTRIB_T CASCADE CONSTRAINTS;
DROP TABLE M4NEW.DOCU_T CASCADE CONSTRAINTS;

-------------------------------------------------------------------------------

CREATE TABLE M4NEW.COL_DATATYPE_T as SELECT * FROM M4OLD.COL_DATATYPE_T;
CREATE TABLE M4NEW.OP_TYPE_T as SELECT * FROM M4OLD.OP_TYPE_T;
CREATE TABLE M4NEW.COLUMNSET_T as SELECT * FROM M4OLD.COLUMNSET_T;

CREATE TABLE M4NEW.CONCEPT_T as (
  SELECT CON_ID, CON_NAME, CON_TYPE, CON_SUBCONRESTR, 'YES' CON_VALID
  FROM M4OLD.CONCEPT_T
);

CREATE TABLE M4NEW.CONCEPTISA_T as SELECT * FROM M4OLD.CONCEPTISA_T;
CREATE TABLE M4NEW.PROJECTION_T as SELECT * FROM M4OLD.PROJECTION_T;

CREATE TABLE M4NEW.MCFEATURE_T as (
  SELECT MCF_ID, MCF_NAME, MCF_CONID, 'YES' MCF_VALID
  FROM M4OLD.MCFEATURE_T
);

CREATE TABLE M4NEW.COLUMN_T as SELECT * FROM M4OLD.COLUMN_T;
CREATE TABLE M4NEW.KEYHEAD_T as SELECT * FROM M4OLD.KEYHEAD_T;
CREATE TABLE M4NEW.KEYMEMBER_T as SELECT * FROM M4OLD.KEYMEMBER_T;
CREATE TABLE M4NEW.CSSTATIST_T as SELECT * FROM M4OLD.CSSTATIST_T;

CREATE TABLE M4NEW.COLSTATIST1_T as (
  SELECT COLST1_ID, COLST1_COLID, COLST1_UNIQUE, COLST1_MISSING,
  COLST1_MIN, COLST1_MAX, COLST1_AVG, COLST1_STDDEV, COLST1_VARIANCE, 
  12 COLST1_MEDIAN, 11 COLST1_MODAL FROM M4OLD.COLSTATIST1_T
);

CREATE TABLE M4NEW.COLSTATIST2_T as SELECT * FROM M4OLD.COLSTATIST2_T;
CREATE TABLE M4NEW.VALUE_T as SELECT * FROM M4OLD.VALUE_T;
CREATE TABLE M4NEW.ROLERESTRICTION_T as SELECT * FROM M4OLD.ROLERESTRICTION_T;

CREATE TABLE M4NEW.CASE_T as (
  SELECT CA_ID, CA_NAME, CA_MODE, CA_POPULATION, CA_OUTPUT, 'YES' CA_VALID
  FROM M4OLD.CASE_T
);

CREATE TABLE M4NEW.CASEINPUT_T as SELECT * FROM M4OLD.CASEINPUT_T;
CREATE TABLE M4NEW.CASEATTRIB_T as SELECT * FROM M4OLD.CASEATTRIB_T;

-- CREATE TABLE M4NEW.OPERATOR_T as SELECT * FROM M4OLD.OPERATOR_T;
CREATE TABLE M4NEW.OPERATOR_T AS (
  SELECT DISTINCT
    OPN_ID OP_ID, OPN_TYPE OP_NAME, OP_LOOP, OP_MULTI, OP_MANUAL, OP_REALIZE
  FROM M4OLD.OPERATOR_T, M4OLD.OP_NAME_T
  WHERE ( OP_OPNID = OPN_ID )
);
--

-- CREATE TABLE M4NEW.PARAMETER_T as SELECT * FROM M4OLD.PARAMETER_T;
CREATE TABLE M4NEW.PARAMETER_T AS (
  SELECT PAR_ID, PAR_NAME, PAR_OBJID, PAR_OBJTYPE,
         OP_OPNID PAR_OPID, PAR_TYPE, PAR_NR, ST_ID PAR_STID,
         PAR_STLOOPNR
  FROM M4OLD.PARAMETER_T, M4OLD.OPERATOR_T, M4OLD.STEP_T 
  WHERE ( (PAR_OPID = OP_ID) AND (ST_OPID = OP_ID) )
);
--

CREATE TABLE M4NEW.STEP_T as (
  SELECT ST_ID, ST_CAID, ST_NR, ST_OPID, ST_LOOPNR,
         ST_MULTISTEPCOND, 'YES' ST_VALID, OP_NAME ST_NAME
  FROM M4OLD.STEP_T, M4OLD.OPERATOR_T
  WHERE ( ST_OPID = OP_ID)
);

-- ALTER TABLE M4NEW.STEP_T ADD ST_NAME VARCHAR2(100);  
ALTER TABLE M4NEW.STEP_T ADD ST_CHID NUMBER;

CREATE TABLE M4NEW.STEPSEQUENCE_T as SELECT * FROM M4OLD.STEPSEQUENCE_T;

CREATE TABLE M4NEW.RELATION_T as (
  SELECT REL_ID, REL_NAME, REL_FROMCONID, REL_TOCONID,
         REL_FROMKID, REL_TOKID, REL_CSID, REL_SUBRELRESTR,
         'YES' REL_VALID
  FROM M4OLD.RELATION_T
);

CREATE TABLE M4NEW.RELATIONISA_T as SELECT * FROM M4OLD.RELATIONISA_T;

CREATE TABLE M4NEW.BASEATTRIB_T as (
  SELECT BA_ID, BA_NAME, BA_CONDTID, BA_RELEVANCE, BA_ATTRIBTYPE,
         BA_MCFID, 'YES' BA_VALID FROM M4OLD.BASEATTRIB_T
);

CREATE TABLE M4NEW.DOCU_T as SELECT * FROM M4OLD.DOCU_T;

--------------------

DROP TABLE M4NEW.CONCEPT_CASE_T;
CREATE TABLE M4NEW.CONCEPT_CASE_T AS (
  SELECT M4NEW.ALL_SQ.NEXTVAL CC_ID, CONCEPT_T.CON_ID CC_CONID, CASE_T.CA_ID CC_CASEID
  FROM M4NEW.STEP_T, M4NEW.CASE_T, M4NEW.PARAMETER_T, M4NEW.CONCEPT_T
  WHERE (
    (STEP_T.ST_ID = CASE_T.CA_ID) AND
    (PARAMETER_T.PAR_STID = STEP_T.ST_ID) AND
    (PARAMETER_T.PAR_OBJTYPE = 'CON') AND
    (PARAMETER_T.PAR_OBJID = CONCEPT_T.CON_ID)
  )
);

DROP TABLE M4NEW.BA_CONCEPT_T;
CREATE TABLE M4NEW.BA_CONCEPT_T AS (
  SELECT M4NEW.ALL_SQ.NEXTVAL BC_ID, BA_ID BC_BAID, CON_ID BC_CONID
  FROM M4OLD.BASEATTRIB_T, M4OLD.CONCEPT_T
  WHERE (BA_CONID = CON_ID)
);

DROP TABLE BA_COLUMN_T;
CREATE TABLE BA_COLUMN_T AS (
  SELECT M4NEW.ALL_SQ.NEXTVAL BAC_ID, BA_ID BAC_BAID, COL_ID BAC_COLID
  FROM M4OLD.COLUMN_T, M4OLD.BASEATTRIB_T
  WHERE (BA_COLID = COL_ID)
);

DROP TABLE COLUMNSET_T;
CREATE TABLE M4NEW.COLUMNSET_T as (
  SELECT
    CS_ID, CS_SCHEMA, CS_NAME, CS_FILE, CS_USER, CS_CONNECT,
    CS_TYPE, CS_SQL, CON_ID CS_CONID
  FROM M4OLD.COLUMNSET_T, M4OLD.CONCEPT_T
  WHERE CON_CSID = CS_ID
);

ALTER TABLE M4NEW.COLUMNSET_T ADD CS_MSBRANCH VARCHAR2(1000);  

COMMIT;
exit
