conn / as sysdba

declare
  vcntuser varchar2(100);
  vcnttablespace varchar2(100);
  vpath varchar2(4000);
begin
  select count(*) into vcntuser from dba_users where username='WFS';
  if vcntuser = 0 then
    select count(*) into vcnttablespace from dba_tablespaces where tablespace_name='WFS_DATA';
    if vcnttablespace = 0 then 
      select substr(file_name,1,instr(file_name,'\',-1) -1)
      INTO vpath
      from dba_data_files
      WHERE tablespace_name = 'SYSTEM';
      execute immediate 'alter system set db_create_file_dest='''||vpath||''' scope=both';
      execute immediate 'create tablespace wfs_data datafile size 50m autoextend on';
    end if;
    execute immediate 'create user wfs identified by wfs default tablespace wfs_data';
    execute immediate 'grant connect,resource to wfs';
    
  end if;
end;
/

conn wfs/wfs

CREATE TABLE mdm_ASSET (
	ID 			NUMBER	 		NOT NULL,
	CLASS_ID 	VARCHAR2(1)		NOT NULL,
	TYPE 		VARCHAR2(50)	NOT NULL,
	CREATE_DATE	TIMESTAMP		DEFAULT		systimestamp	NULL,
	UPDATE_DATE	TIMESTAMP		DEFAULT		systimestamp	NULL,
	STATE		NUMBER			DEFAULT		0,
	ROOTASSET_ID		NUMBER			NULL, 
	EXTERNALREVISION	NVARCHAR2(50)   NULL,
	LATESTREVISION 	NUMBER(10)	 	DEFAULT		1		NOT NULL,
	PRIMARY KEY(ID)
);

CREATE TABLE mdm_RELATION (
	ID		 			NUMBER			NOT NULL,
	OWNING_ASSET_ID		NUMBER	 		NOT NULL,
	TARGET_ASSET_ID 	NUMBER					,
	CLASS_ID 			VARCHAR2(50)	NOT NULL,
	DIRECTION			NUMBER(1,0)				,
	CREATE_DATE			TIMESTAMP		DEFAULT 	systimestamp	NULL,
	UPDATE_DATE			TIMESTAMP		DEFAULT 	systimestamp	NULL,
	ADDREVISION 		NUMBER(10)	 	DEFAULT		1				NOT NULL,
	DELETEREVISION 		NUMBER(10)	 	DEFAULT		0,
	PRIMARY KEY(ID)
);

CREATE TABLE mdm_FIELD (
	ID		 			NUMBER			NOT NULL,
	TTVXPATH			NVARCHAR2(500)	NOT NULL, 
	VALUE				NVARCHAR2(2000), 
	LARGEVALUE			CLOB,
	DATEVALUE			DATE,
	INTVALUE			NUMBER,
	FLOATVALUE			NUMBER,
	DATATYPE			NVARCHAR2(50),
	INDICES				NVARCHAR2(100), 
	ROOTASSET_ID		NUMBER			NULL, 
	ASSET_ID			NUMBER			NULL,
	PARSABLE			NUMBER(1,0)		DEFAULT		1		NOT NULL,
	primary key (ID)
);

CREATE TABLE mdm_FIELDREVISION (
	ID		 			NUMBER			NOT NULL,
	TTVXPATH			NVARCHAR2(500)	NOT NULL, 
	VALUE				NVARCHAR2(2000),
	DATEVALUE			DATE,
	INTVALUE			NUMBER,
	FLOATVALUE			NUMBER,
	LARGEVALUE			CLOB,
	DATATYPE			NVARCHAR2(50),
	INDICES				NVARCHAR2(100), 
	ROOTASSET_ID		NUMBER			NULL, 
	ASSET_ID			NUMBER			NULL,
	ADDREVISION 		NUMBER(10)	 	DEFAULT		1		NOT NULL,
	DELETEREVISION 		NUMBER(10)	 	DEFAULT		0,
	REVISIONNUMBER 		NUMBER(10)	 	DEFAULT		1		NOT NULL,
	PARSABLE			NUMBER(1,0)		DEFAULT		1		NOT NULL,
	primary key (ID)
);


CREATE TABLE mdm_ROOTASSETREVISION (
	ID		 			NUMBER			NOT NULL,
	ASSET_ID			NUMBER			NOT NULL,
	EXTERNALREVISION	NVARCHAR2(50)   NULL,
	REVISIONNUMBER 		NUMBER(10)	 	DEFAULT		1		NOT NULL,
	REVISIONSOURCE		VARCHAR2(50)	NULL,
	REVISIONCOMMENT		VARCHAR2(500),
	REVISIONTIME		TIMESTAMP		DEFAULT 	systimestamp   NOT NULL,
	DRAFT				NUMBER(1,0)		DEFAULT		0,
	primary key (ID)
);

ALTER TABLE mdm_RELATION
	ADD ( CONSTRAINT fk_rel_asset_id
	FOREIGN KEY(OWNING_ASSET_ID)
	REFERENCES mdm_ASSET(ID)
);

ALTER TABLE mdm_RELATION
	ADD ( CONSTRAINT fk_rel_asset_target
	FOREIGN KEY(TARGET_ASSET_ID)
	REFERENCES mdm_ASSET(ID)
);


ALTER TABLE mdm_FIELD
	ADD ( CONSTRAINT fk_field_asset_rootassetid
	FOREIGN KEY(ROOTASSET_ID)
	REFERENCES mdm_ASSET(ID)
);


ALTER TABLE mdm_FIELD
	ADD ( CONSTRAINT fk_field_asset_id
	FOREIGN KEY(ASSET_ID)
	REFERENCES mdm_ASSET(ID)
);



ALTER TABLE mdm_ASSET
	ADD ( CONSTRAINT fk_assetroot_asset_id
	FOREIGN KEY(ROOTASSET_ID)
	REFERENCES mdm_ASSET(ID)
);


ALTER TABLE mdm_FIELDREVISION
	ADD ( CONSTRAINT fk_fieldrv_asset_rootassetid
	FOREIGN KEY(ROOTASSET_ID)
	REFERENCES mdm_ASSET(ID)
);


ALTER TABLE mdm_FIELDREVISION
	ADD ( CONSTRAINT fk_fieldrevision_asset_id
	FOREIGN KEY(ASSET_ID)
	REFERENCES mdm_ASSET(ID)
);

ALTER TABLE mdm_ROOTASSETREVISION
	ADD ( CONSTRAINT fk_rootassetrevision_asset_id
	FOREIGN KEY(ASSET_ID)
	REFERENCES mdm_ASSET(ID)
);

ALTER TABLE mdm_ROOTASSETREVISION
	ADD ( CONSTRAINT unique_rootassetrevision
	UNIQUE (ASSET_ID,REVISIONNUMBER)
);

CREATE TABLE mdm_RULESET (
	ID			NUMBER	 		NOT NULL,
	NAME		VARCHAR2(50)	NULL,
	SPEC		VARCHAR2(50)	NULL,
	CREATE_DATE	TIMESTAMP		DEFAULT		systimestamp	NULL,
	UPDATE_DATE	TIMESTAMP		DEFAULT		systimestamp	NULL,
	PRIMARY KEY(ID)
);

CREATE TABLE mdm_RULE (
	ID			NUMBER			NOT NULL,
	XPATH		VARCHAR2(200)	NULL,
	ISREQUIRED	NUMBER(1,0)		NULL,
	RULESET_ID	NUMBER	 		NULL,
	CREATE_DATE	TIMESTAMP		DEFAULT 	systimestamp	NULL,
	UPDATE_DATE	TIMESTAMP		DEFAULT 	systimestamp	NULL,
	PRIMARY KEY(ID)
);

ALTER TABLE mdm_RULE
	ADD ( CONSTRAINT fk_rule_ruleset_id
	FOREIGN KEY(RULESET_ID)
	REFERENCES mdm_RULESET(ID)
);

--Create Indexes

CREATE INDEX mdm_FIELDVAL_IND ON MDM_FIELD("VALUE");

CREATE INDEX mdm_FIELDXPATH_IND ON MDM_FIELD("TTVXPATH");

CREATE INDEX MDM_FIELDASSETID_IND ON MDM_FIELD(ASSET_ID);

CREATE INDEX MDM_FIELD_ROOTASSET_ID_IND ON MDM_FIELD(ROOTASSET_ID);

CREATE INDEX MDM_ASSETCOM_IND ON MDM_ASSET(ID,STATE,ROOTASSET_ID,TYPE,CLASS_ID);

CREATE INDEX MDM_ASSET_ROOTASSET_ID_IND ON MDM_ASSET(ROOTASSET_ID);

CREATE INDEX MDM_RELATION_TAR_ASSET_IND ON MDM_RELATION(TARGET_ASSET_ID);

CREATE INDEX MDM_RELATION_OWN_ASSET_IND ON MDM_RELATION(OWNING_ASSET_ID);

CREATE INDEX FIELDREVISION_ASSET_ID ON MDM_FIELDREVISION(ASSET_ID);

CREATE INDEX MDMFLDREV_VALUEXPATH_IND ON MDM_FIELDREVISION (VALUE,TTVXPATH);

--Create Sequences

CREATE SEQUENCE mdm_ASSET_SEQ INCREMENT BY 1 START WITH 1	NOMAXVALUE;
	
CREATE SEQUENCE mdm_RELATION_SEQ	INCREMENT BY 1	START WITH 1	NOMAXVALUE;

CREATE SEQUENCE mdm_FIELD_SEQ	INCREMENT BY 1	START WITH 1	NOMAXVALUE;

CREATE SEQUENCE mdm_FIELDREVISION_SEQ	INCREMENT BY 1	START WITH 101	NOMAXVALUE;

CREATE SEQUENCE mdm_CFG_SEQ	INCREMENT BY 1	START WITH 1	NOMAXVALUE;
	
CREATE SEQUENCE mdm_CFD_SEQ	INCREMENT BY 1	START WITH 1	NOMAXVALUE;

CREATE SEQUENCE mdm_RULESET_SEQ	INCREMENT BY 1	START WITH 1	NOMAXVALUE;

CREATE SEQUENCE mdm_ROOTASSETREVISION_SEQ	INCREMENT BY 1	START WITH 1	NOMAXVALUE;

CREATE SEQUENCE mdm_RULE_SEQ	INCREMENT BY 1	START WITH 1	NOMAXVALUE;

exit