--gggraham: 25Jun2006. Add WorkspaceID to assoctable for partitioning.
--  This table is transient, but potentially high volume.
--gggraham: 25Jun2006. Add WorkspaceID to AllAssocTable for partitioning.
--  This table is transient, but potentially high volume.
create table AllAssocTable (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  oldid NUMBER(9),
  newid NUMBER(9),
  uoid VARCHAR2(255)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllAssocTable NOLOGGING;

--gggraham: 25Jun2006. Add WorkspaceID to all indexes of AllAssocTable for local partitioning.
create index IDX_ASSOCTABLE_NEWID on AllAssocTable(workspaceID, newid) NOLOGGING COMPRESS tablespace %tndex;
create index IDX_ASSOCTABLE_OLDID on AllAssocTable(workspaceID, oldid) NOLOGGING COMPRESS tablespace %tndex;

create view assoctable (
  workspaceID,
  oldid,
  newid,
  uoid
) as select *
from AllAssocTable
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

create view CMPCopyObject_X (
  snapshotID,
  name,
  classname,
  uoid,
  elementid,
  aggregateParent
)
as
select snapshotID,name,classname,uoid,elementid,owningproject from CMPInstalledModule_X
union all
select snapshotID,name,classname,uoid,elementid,installedmodule from CMPRelation_X
union all
select snapshotID,name,classname,uoid,elementid,owningfolder from FirstClassObject_X
union all
select snapshotID,name,classname,uoid,elementid,firstclassobject from CMPProcessActivity_X
union all
select snapshotID,name,classname,uoid,elementid,operatorowner from CMPMapOperator_X;

create view CMPCopyObject_V (
  notm,
  name,
  classname,
  uoid,
  elementid,
  aggregateParent
)
as
select notm,name,classname,uoid,elementid,owningproject from CMPInstalledModule_V
union all
select notm,name,classname,uoid,elementid,installedmodule from CMPRelation_V
union all
select notm,name,classname,uoid,elementid,owningfolder from FirstClassObject_V
union all
select notm,name,classname,uoid,elementid,firstclassobject from CMPProcessActivity_V
union all
select notm,name,classname,uoid,elementid,operatorowner from CMPMapOperator_V;

create global temporary table CMPElement as select * from CMPElement_X;
create unique index IDX_CMPELEMENT_ELEMID on CMPElement(elementid);

create global temporary table FirstClassObject as select * from FirstClassObject_X;
create unique index IDX_FCO_ELEMID on FirstClassObject(elementid);

create global temporary table SecondClassObject as select * from SecondClassObject_X;
create unique index IDX_SCO_ELEMID on SecondClassObject(elementid);

create global temporary table CMPFunction as select * from CMPFunction_X;
create unique index IDX_CMPFUNCTION_ELEMID on CMPFunction(elementid);

create global temporary table CMPWBProject as select * from CMPWBProject_X;
create unique index IDX_CMPWBPROJECT_ELEMID on CMPWBProject(elementid);

create global temporary table CMPReferencePropertyValue as select * from CMPReferencePropertyValue_X;
create unique index IDX_CMPREFPROPVALUE_ELEMID on CMPReferencePropertyValue(elementid);

create global temporary table CMPPhysicalObject as select * from CMPPhysicalObject_X;
create unique index IDX_CMPPHYSOBJECT_ELEMID on CMPPhysicalObject(elementid);

--gggraham: 25Jun2006. Add WorkspaceID to FCOFOLDERNamespaceStorage
--gggraham: 25Jun2006. Add WorkspaceID to FCOFOLDERNamespaceStorage
create table AllFCOFOLDERNamespaceStorage (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  snapshotID NUMBER(9) default 0,
  isSeed NUMBER(1) default 1,
  active NUMBER(1) default 0,
  parentID NUMBER(9),
  domain VARCHAR2(60),
  name VARCHAR2(200),
  isPhys NUMBER(1),
  elementid NUMBER,
  aggregateParent NUMBER,
  languageISOID varchar(40)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllFCOFOLDERNamespaceStorage NOLOGGING;

--gggraham: 25Jun2006. Add WorkspaceID for all AllFCOFOLDERNamespace indexes, including its primary key.
create unique index IDX_FCONSSTORAGE_ELEMID on allfcofoldernamespacestorage(workspaceID,snapshotid,elementid,isPhys) NOLOGGING COMPRESS tablespace %tndex;
create unique index IDX_FCONSSTORAGE_AGGPARENT on allfcofoldernamespacestorage(workspaceID,snapshotid,aggregateparent,elementid,isPhys) NOLOGGING COMPRESS tablespace %tndex;
create index FCOFOLDERNSSTORAGE_PK on allfcofoldernamespacestorage(workspaceID,snapshotID,parentID,domain,name,isPhys,languageISOID) NOLOGGING COMPRESS tablespace %tndex;

--gggraham: 25Jun2006. Add primary key constraint after index is declare local, partitioned
alter table AllFCOFOLDERNamespaceStorage NOLOGGING
  add constraint FCOFOLDERNSSTORAGE_PK primary key
    (workspaceID,snapshotID,parentID,domain,name,isPhys,languageISOID) ;

create view FCOFOLDERNamespaceStorage (
  workspaceID,
  snapshotID,
  isSeed,
  active,
  parentID,
  domain,
  name,
  isPhys,
  elementid,
  aggregateParent,
  languageISOID
) as select *
from AllFCOFOLDERNamespaceStorage
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);


--gggraham: 25Jun2006. Add WorkspaceID for CMPALLFOLDERFCONAMES_X
create or replace view CMPALLFOLDERFCONAMES_X (
   workspaceID
  ,snapshotID
  ,parentID
  ,domain
  ,name
  ,isPhys
  ,elementid
  ,aggregateParent
  ,languageISOID
)
as select
   workspaceID
  ,snapshotID
  ,parentID
  ,domain
  ,name
  ,isPhys
  ,elementid
  ,aggregateParent
  ,languageISOID
from fcofoldernamespacestorage
where active = 0
  and workspaceid = (select sys_context('owb_workspace','workspaceID') from dual);

create global temporary table MCMSemantics (
  SemanticsID NUMBER,
  LinkType NUMBER,
  fromLinkClass VARCHAR2(255),
  fromLinkOp VARCHAR2(255),
  fromLinkRole VARCHAR2(255),
  toLinkClass VARCHAR2(255),
  toLinkOp VARCHAR2(255),
  toLinkRole VARCHAR(255),
  MatchingStrategy NUMBER default 0
)
ON COMMIT DELETE ROWS;

create index IDX_MCMSEMANTICS_ASSOC on mcmsemantics
  (fromlinkclass,fromlinkrole,tolinkclass,tolinkrole,matchingstrategy);

--gggraham: 25Jun2006. Add WorkspaceID to ChangeLog for partitioning.
--  This table is transient, records deletions, but potentially high volume.
--gggraham: 25Jun2006. Add WorkspaceID to AllChangeLog for partitioning.
--  This table is transient, records deletions, but potentially high volume.
create table AllChangeLog (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  elementid NUMBER(9),
  action NUMBER(1),
  constraint CHANGELOG_PK primary key (workspaceID,elementid))
organization index;
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllChangeLog NOLOGGING;

--gggraham: 25Jun2006. Add WorkspaceID to pctree for partitioning.
create view changeLog (
  workspaceID,
  elementid,
  action
) as select *
from AllChangeLog
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

--gggraham: 25Jun2006. Add WorkspaceID to AllPCTree for partitioning.
create table AllPCTree (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  parentid NUMBER(9),
  childid NUMBER(9),
  parentrole VARCHAR2(100),
  childrole VARCHAR2(100)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllPCTree NOLOGGING;

-- remove the uniques of the index till we figure out why it ic creating dupicate entries.
-- only for owbmeta
-- create index IDX_PCTREE_PARENTID on AllPCTree(workspaceID,parentid) tablespace %tndex NOLOGGING COMPRESS;
-- create unique index IDX_PCTREE_CHILDID on AllPCTree(workspaceID,childid) tablespace %tndex NOLOGGING COMPRESS;
-- create unique index IDX_PCTREE_PARENTID_CHILDID on AllPCTree(workspaceID,parentid,childid) tablespace %tndex NOLOGGING COMPRESS;
-- create unique index IDX_PCTREE_CHILDID_PARENTID on AllPCTree(workspaceID,childid,parentid) tablespace %tndex NOLOGGING COMPRESS;

create index IDX_PCTREE_PARENTID on AllPCTree(workspaceID,parentid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_PCTREE_CHILDID on AllPCTree(workspaceID,childid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_PCTREE_PARENTID_CHILDID on AllPCTree(workspaceID,parentid,childid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_PCTREE_CHILDID_PARENTID on AllPCTree(workspaceID,childid,parentid) tablespace %tndex NOLOGGING COMPRESS;

--gggraham: 25Jun2006. Add WorkspaceID to pctreestorage for partitioning.
create view pctree (
  workspaceID,
  parentid,
  childid,
  parentrole,
  childrole
) as select *
from AllPCTree
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

--gggraham: 25Jun2006. Add WorkspaceID to AllPCTreestorage for partitioning.
create table AllPCTreeStorage (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  snapshotID NUMBER(9),
  compuoid VARCHAR2(255),
  parentid NUMBER(9),
  childid NUMBER(9),
  parentrole VARCHAR2(100),
  childrole VARCHAR2(100)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllPCTreeStorage NOLOGGING;

create index IDX_PCTREESTORAGE_CID on AllPCTreeStorage(workspaceID,childid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_PCTREESTORAGE_COMPUOID on AllPCTreeStorage(workspaceID,compuoid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_PCTREESTORAGE_SNAPID on AllPCTreeStorage(workspaceID,snapshotid) tablespace %tndex NOLOGGING COMPRESS;

create view pctreestorage (
  workspaceID,
  snapshotID,
  compuoid,
  parentid,
  childid,
  parentrole,
  childrole
) as select *
from AllPCTreeStorage
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

create global temporary table pctreedebug (
  parentid NUMBER(9),
  childid NUMBER(9),
  parentrole VARCHAR2(100),
  childrole VARCHAR2(100)
);
create index IDX_PCTREEDBG_PARENTID on pctreedebug(parentid);
create unique index IDX_PCTREEDBG_CHILDID on pctreedebug(childid);


--gggraham: 25Jun2006. Add WorkspaceID to interlink for partitioning.
--gggraham: 25Jun2006. Add WorkspaceID to AllInterLink for partitioning.
create table AllInterLink (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  linkfrom NUMBER(9),
  linkto NUMBER(9),
  linkfromrole VARCHAR2(100),
  linktorole VARCHAR2(100)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllInterLink NOLOGGING;

create index IDX_INTERLINK_LINKFROM on AllInterLink(workspaceID,linkfrom) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_INTERLINK_LINKTO on AllInterLink(workspaceID,linkto) tablespace %tndex NOLOGGING COMPRESS;

--gggraham: 25Jun2006. Add WorkspaceID to interlinkstorage for partitioning.
create view interlink (
  workspaceID,
  linkfrom,
  linkto,
  linkfromrole,
  linktorole
) as select *
from AllInterLink
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

--gggraham: 25Jun2006. Add WorkspaceID to AllInterLinkstorage for partitioning.
create table AllInterLinkStorage (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  snapshotID NUMBER(9),
  compuoid VARCHAR2(255),
  linkfrom NUMBER(9),
  linkto NUMBER(9),
  linkfromrole VARCHAR2(100),
  linktorole VARCHAR2(100)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllInterLinkStorage NOLOGGING;

create index IDX_INTERLINK_S_SNAPID on AllInterLinkStorage(workspaceID,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_INTERLINK_S_LINKFROM on AllInterLinkStorage(workspaceID,linkfrom,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_INTERLINK_S_LINKTO on AllInterLinkStorage(workspaceID,linkto,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_INTERLINK_S_COMPUOID on AllInterLinkStorage(workspaceID,compuoid,snapshotid) tablespace %tndex NOLOGGING COMPRESS;

create view interlinkstorage (
  workspaceID,
  snapshotID,
  compuoid,
  linkfrom,
  linkto,
  linkfromrole,
  linktorole
) as select *
from AllInterLinkStorage
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

create global temporary table interlinkdebug (
  linkfrom NUMBER(9),
  linkto NUMBER(9),
  linkfromrole VARCHAR2(100),
  linktorole VARCHAR2(100)
);

create index IDX_INTERLINKDBG_LINKFROM on interlinkdebug(linkfrom);
create index IDX_INTERLINKDBG_LINKTO on interlinkdebug(linkto);

--gggraham: 25Jun2006. Add WorkspaceID to intralink for partitioning.
--gggraham: 25Jun2006. Add WorkspaceID to AllIntraLink for partitioning.
create table AllIntraLink (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  linkfrom NUMBER(9),
  linkto NUMBER(9),
  linkfromrole VARCHAR2(100),
  linktorole VARCHAR2(100)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllIntraLink NOLOGGING;

BEGIN
--Conditionally use data segment compression on certain tables
--if the Oracle 'compatible' version for the instance permits it.
DECLARE
  prodInf VARCHAR2(512);
  version VARCHAR2(512);
BEGIN
  select product into prodInf from sys.product_component_version where instr(product,'Oracle') != 0;
  select value into version from sys.v_$system_parameter where name ='compatible';
  DBMS_OUTPUT.PUT_LINE('Compatible=' || version);
  IF ((SUBSTR(version, 1, 3) = '10.' OR
       SUBSTR(version, 1, 4) = '9.2.') AND
       INSTR(prodInf, 'Enterprise') != 0) THEN
    execute immediate 'alter table AllPCTree compress ';
    execute immediate 'alter table AllInterLink compress ';
    execute immediate 'alter table AllIntraLink compress ';
  END IF;
END;
END;

create index IDX_INTRALINK_LINKFROM on AllIntraLink(workspaceID,linkfrom) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_INTRALINK_LINKTO on AllIntraLink(workspaceID,linkto) tablespace %tndex NOLOGGING COMPRESS;

--gggraham: 25Jun2006. Add WorkspaceID to intralinkstorage for partitioning.
create view intralink (
  workspaceID,
  linkfrom,
  linkto,
  linkfromrole,
  linktorole
) as select *
from AllIntraLink
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

--gggraham: 25Jun2006. Add WorkspaceID to AllIntraLinkstorage for partitioning.
create table AllIntraLinkStorage (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  snapshotID NUMBER(9),
  compuoid VARCHAR2(255),
  linkfrom NUMBER(9),
  linkto NUMBER(9),
  linkfromrole VARCHAR2(100),
  linktorole VARCHAR2(100)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllIntraLinkStorage NOLOGGING;

create index IDX_INTRALINK_S_SNAPID on AllIntraLinkStorage(workspaceID,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_INTRALINK_S_LINKFROM on AllIntraLinkStorage(workspaceID,linkfrom,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_INTRALINK_S_LINKTO on AllIntraLinkStorage(workspaceID,linkto,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_INTRALINK_S_COMPUOID on AllIntraLinkStorage(workspaceID,compuoid,snapshotid) tablespace %tndex NOLOGGING COMPRESS;

create view intralinkstorage (
  workspaceID,
  snapshotID,
  compuoid,
  linkfrom,
  linkto,
  linkfromrole,
  linktorole
) as select *
from AllIntraLinkStorage
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

create global temporary table intralinkdebug (
  linkfrom NUMBER(9),
  linkto NUMBER(9),
  linkfromrole VARCHAR2(100),
  linktorole VARCHAR2(100)
);

create index IDX_INTRALINKDBG_LINKFROM on intralinkdebug(linkfrom);

--gggraham: 25Jun2006. WorkspaceID not needed for SnapshotLookup, a very low volume table.
create table SnapshotLookup (
    snapshotID NUMBER(9),
    uoid VARCHAR2(32),
    snapshotName VARCHAR2(100),
    creationtimestamp DATE,
    updatetimestamp DATE default null,
    description VARCHAR2(2000),
    username VARCHAR2(100),
    updatedBy VARCHAR2(100),
    isHeavy NUMBER(1),
    snapshotType NUMBER(1)
);
create index IDX_LOOKUPSNAPID on SnapshotLookup(snapshotID) tablespace %tndex;
create index IDX_LOOKUPSNAPNAME on SnapshotLookup(snapshotName) tablespace %tndex;

create global temporary table SnapshotLookupTemp (
    snapshotID NUMBER(9),
    uoid VARCHAR2(32),
    snapshotName VARCHAR2(100),
    creationtimestamp DATE,
    updatetimestamp DATE default null,
    description VARCHAR2(2000),
    username VARCHAR2(100),
    updatedBy VARCHAR2(100),
    isHeavy NUMBER(1),
    snapshotType NUMBER(1)
)
ON COMMIT DELETE ROWS;

--gggraham: 25Jun2006. Add WorkspaceID to temptable for partitioning.
--  This table is transient, but potentially high volume.
--gggraham: 25Jun2006. Add WorkspaceID to AllTempTable for partitioning.
--  This table is transient, but potentially high volume.
create table AllTempTable (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  name VARCHAR2(4000),
  logicalName VARCHAR2(4000),
  classname VARCHAR2(255),
  uoid VARCHAR2(255),
  elementid NUMBER(9),
  rootid NUMBER(9),
  metadatasignature VARCHAR2(255),
  compuoid VARCHAR2(255),
  type NUMBER(9),
  objtype NUMBER(9)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllTempTable NOLOGGING;

create view temptable (
  workspaceID,
  name,
  logicalName,
  classname,
  uoid,
  elementid,
  rootid,
  metadatasignature,
  compuoid,
  type,
  objtype
) as select *
from AllTempTable
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

create global temporary table SourceComponent (
  compareID NUMBER(9),
  diffCol VARCHAR2(500),
  assocCol VARCHAR2(500),
  elementid NUMBER(9),
  uoid VARCHAR2(255),
  role VARCHAR2(255),
  physicalName VARCHAR2(4000),
  logicalName VARCHAR2(4000),
  className VARCHAR2(255),
  metadatasignature VARCHAR2(255),
  objType NUMBER(1),
  associd NUMBER(9),
  assocUOID VARCHAR2(255),
  assocRole VARCHAR2(255),
  assocPhysName VARCHAR2(255),
  assocLogName VARCHAR2(4000),
  assocClassname VARCHAR2(255),
  assocType NUMBER(1)
)
ON COMMIT DELETE ROWS;

create global temporary table TargetComponent (
  compareID NUMBER(9),
  diffCol VARCHAR2(500),
  assocCol VARCHAR2(500),
  elementid NUMBER(9),
  uoid VARCHAR2(255),
  role VARCHAR2(255),
  physicalName VARCHAR2(4000),
  logicalName VARCHAR2(4000),
  className VARCHAR2(255),
  metadatasignature VARCHAR2(255),
  objType NUMBER(1),
  associd NUMBER(9),
  assocUOID VARCHAR2(255),
  assocRole VARCHAR2(255),
  assocPhysName VARCHAR2(255),
  assocLogName VARCHAR2(4000),
  assocClassname VARCHAR2(255),
  assocType NUMBER(1)
)
ON COMMIT DELETE ROWS;

--gggraham: 25Jun2006. Add WorkspaceID to CompareStore for partitioning.
--  This table is transient, but potentially high volume.
--gggraham: 25Jun2006. Add WorkspaceID to AllCompareStore for partitioning.
--  This table is transient, but potentially high volume.
create table AllCompareStore (
  workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
  compareID NUMBER(9),
  diffCol VARCHAR2(500),
  assocCol VARCHAR2(500),
  uoid VARCHAR2(255),
  role VARCHAR2(255),
  physicalName VARCHAR2(4000),
  logicalName VARCHAR2(4000),
  className VARCHAR2(255),
  comparedml NUMBER(1),
  signstate NUMBER(1),
  objType NUMBER(1),
  assocUOID VARCHAR2(255),
  assocRole VARCHAR2(255),
  assocPhysName VARCHAR2(255),
  assocLogName VARCHAR2(4000),
  assocClassName VARCHAR2(255),
  assocState NUMBER(1),
  assocType NUMBER(1),
  otheruoid VARCHAR2(255)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllCompareStore NOLOGGING;

create index IDX_CompareStore_uoid on AllCompareStore(workspaceID,uoid) NOLOGGING COMPRESS tablespace %tndex;
create index IDX_CompareStore_CompareID on AllCompareStore(workspaceID,CompareID) NOLOGGING COMPRESS tablespace %tndex;
create index IDX_CompareStore_assocType on AllCompareStore(workspaceID,assocType) NOLOGGING COMPRESS tablespace %tndex;
create index IDX_CompareStore_diffCol on AllCompareStore(workspaceID,diffCol) NOLOGGING COMPRESS tablespace %tndex;
create index IDX_CompareStore_assocCol on AllCompareStore(workspaceID,assocCol) NOLOGGING COMPRESS tablespace %tndex;

create view CompareStore (
  workspaceID,
  compareID,
  diffCol,
  assocCol,
  uoid,
  role,
  physicalName,
  logicalName,
  className,
  comparedml,
  signstate,
  objType,
  assocUOID,
  assocRole,
  assocPhysName,
  assocLogName,
  assocClassName,
  assocState,
  assocType,
  otheruoid
) as select *
from AllCompareStore
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

create global temporary table CompareStoreTemp (
  compareID NUMBER(9),
  diffCol VARCHAR2(500),
  assocCol VARCHAR2(500),
  uoid VARCHAR2(255),
  role VARCHAR2(255),
  physicalName VARCHAR2(4000),
  logicalName VARCHAR2(4000),
  className VARCHAR2(255),
  comparedml NUMBER(1),
  signstate NUMBER(1),
  objType NUMBER(1),
  assocUOID VARCHAR2(255),
  assocRole VARCHAR2(255),
  assocPhysName VARCHAR2(255),
  assocLogName VARCHAR2(4000),
  assocClassName VARCHAR2(255),
  assocState NUMBER(1),
  assocType NUMBER(1),
  otheruoid VARCHAR2(255)
)
ON COMMIT DELETE ROWS;

--gggraham: 25Jun2006. WorkspaceID not needed for CompareStoreManager, a very low volume table.
create table CompareStoreManager (
  CompUOID VARCHAR2(255),
  srcID NUMBER(9),
  tgtID NUMBER(9),
  CompareID NUMBER(9),
  constraint CompareStoreManager_PK primary key(CompUOID, srcID, tgtID, CompareID)
) organization index overflow tablespace %tndex;

create global temporary table CompareStoreManagerTemp (
  CompUOID VARCHAR2(255),
  srcID NUMBER(9),
  tgtID NUMBER(9),
  CompareID NUMBER(9),
  constraint CompareStoreManager_PK_Temp primary key(CompUOID, srcID, tgtID, CompareID)
)
ON COMMIT DELETE ROWS;

create global temporary table TempFCO (
  elementid NUMBER(9),
  uoid VARCHAR2(500),
  NOTM NUMBER(9),
  type NUMBER(1)
)
ON COMMIT DELETE ROWS;

create index IDX_TEMPFCO_ELEMID on tempfco(elementid);
create index IDX_TEMPFCO_UOID on tempfco(uoid);

create global temporary table CurrTempFCO (
  elementid NUMBER(9),
  uoid VARCHAR2(500),
  NOTM NUMBER(9),
  type NUMBER(1)
);

create unique index IDX_CURRTEMPFCO_ELEMID on currtempfco(elementid);

create global temporary table MCMComponent (
  elementid NUMBER(9),
  uoid VARCHAR2(500),
  cascade NUMBER(9),
  type NUMBER(1)
)
ON COMMIT DELETE ROWS;

create global temporary table hierarchytable (
  rootID NUMBER(9),
  ordering NUMBER(9),
  memberID NUMBER(9),
  constraint hierarchytable_PK primary key (rootID,ordering,memberID)
)
ON COMMIT DELETE ROWS;

--gggraham: 25Jun2006. WorkspaceID not needed for SnapshotStoreTable, a very low volume table.
create table SnapshotStoreTable (
    snapshotID NUMBER(9),
    physicalName VARCHAR2(4000),
    logicalName VARCHAR2(4000),
    classname VARCHAR2(255),
    uoid VARCHAR2(255),
    creationtimestamp DATE,
    description VARCHAR2(2000),
    isHeavy NUMBER(1),
    isCascade NUMBER(1),
    isRoot NUMBER(1),
    userName VARCHAR2(255) default null,
    updatedby VARCHAR2(255) default null,
    updatetimestamp DATE default null,
    constraint SnapshotStoreTable_PK primary key (snapshotID,uoid) using index tablespace %tndex
);

create index IDX_SST_USERNAME on SnapshotStoreTable(userName) tablespace %tndex;
create index IDX_SST_IS_ROOT on SnapshotStoreTable(isRoot) tablespace %tndex;
create index IDX_SST_UOID on SnapshotStoreTable(uoid) tablespace %tndex;
create index IDX_SNAPSTORESNAPID on snapshotstoretable(snapshotID) tablespace %tndex;

--gggraham: 25Jun2006. Add WorkspaceID to AllLWContext for partitioning.
--  This table is transient, but potentially high volume.
create table AllLWContext (
    workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
    snapshotID NUMBER(9),
    compuoid VARCHAR2(255),
    type NUMBER(1),
    elementid NUMBER(9),
    rootid NUMBER(9),
    uoid VARCHAR2(255),
    role VARCHAR2(255),
    physicalName VARCHAR2(4000),
    logicalName VARCHAR2(4000),
    classname VARCHAR2(255),
    metadatasignature VARCHAR2(255),
    objType NUMBER(9),
    associd NUMBER(9),
    assocUOID VARCHAR2(255),
    assocRole VARCHAR2(255),
    assocPhysName VARCHAR2(255),
    assocLogName VARCHAR2(4000),
    assocClassName VARCHAR2(255),
    assocType NUMBER(1),
    diffCol VARCHAR2(500),
    assocCol VARCHAR2(500)
);
--PARTITION BY LIST (workspaceID)
--(PARTITION ws_global  VALUES (1),
-- PARTITION ws_default VALUES (DEFAULT));
alter table AllLWContext NOLOGGING;

create index IDX_LWCONTEXT_DIFFCOL on AllLWContext(workspaceID,diffCol,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_LWCONTEXT_ASSOCCOL on AllLWContext(workspaceID,assocCol,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_LWCONTEXT_ELEMENTID on AllLWContext(workspaceID,elementid,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_LWCONTEXT_ASSOCID on AllLWContext(workspaceID,associd,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_LWCONTEXT_ASSOCTYPE on AllLWContext(workspaceID,assocType,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_LWCONTEXT_SNAPID on AllLWContext(workspaceID,snapshotid) tablespace %tndex NOLOGGING COMPRESS;
create index IDX_LWCONTEXT_UOID_COMPUOID on AllLWContext(workspaceID,uoid,compuoid) tablespace %tndex NOLOGGING COMPRESS;

create view LWContext (
    workspaceID,
    snapshotID,
    compuoid,
    type,
    elementid,
    rootid,
    uoid,
    role,
    physicalName,
    logicalName,
    classname,
    metadatasignature,
    objType,
    associd,
    assocUOID,
    assocRole,
    assocPhysName,
    assocLogName,
    assocClassName,
    assocType,
    diffCol,
    assocCol
) as select *
from AllLWContext
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);

create global temporary table LWContextTemp (
    snapshotID NUMBER(9),
    compuoid VARCHAR2(255),
    type NUMBER(1),
    elementid NUMBER(9),
    rootid NUMBER(9),
    uoid VARCHAR2(255),
    role VARCHAR2(255),
    physicalName VARCHAR2(4000),
    logicalName VARCHAR2(4000),
    classname VARCHAR2(255),
    metadatasignature VARCHAR2(255),
    objType NUMBER(9),
    associd NUMBER(9),
    assocUOID VARCHAR2(255),
    assocRole VARCHAR2(255),
    assocPhysName VARCHAR2(255),
    assocLogName VARCHAR2(4000),
    assocClassName VARCHAR2(255),
    assocType NUMBER(1),
    diffCol VARCHAR2(500),
    assocCol VARCHAR2(500)
)
ON COMMIT DELETE ROWS;

--create index IDX_LWCTXTEMP_ROOTID on lwcontexttemp(rootid);
--create index IDX_LWCTXTEMP_ASSOC on lwcontexttemp(classname,role,assocclassname,assocrole);
create index IDX_LWCTXTEMP_ELEMENTID on lwcontexttemp(elementid);
create index IDX_LWCTXTEMP_SNAPID on lwcontexttemp(snapshotid);

create view lwgraph as
  select snapshotID, diffcol, compuoid, physicalName, logicalName, uoid,
         classname, assocUOID,
         assocPhysName, assocLogName, assocClassName, assocType
  from lwcontext;
create view lwgraphtemp as
  select snapshotID, diffcol, compuoid, physicalName, logicalName, uoid,
         classname, assocUOID,
         assocPhysName, assocLogName, assocClassName, assocType
  from lwcontexttemp;

create view lwcomptree as
  select snapshotID, diffcol, uoid, compuoid from lwcontext where type = 0;

create view lwcomptreetemp as
  select snapshotID, diffcol, uoid, compuoid from lwcontexttemp where type = 0;

create sequence SnapshotSequence increment by 1 start with 1;
create sequence CompareSequence increment by 1 start with 2;

create sequence SnapshotSequenceTemp increment by 1 start with 1;
create sequence CompareSequenceTemp increment by 1 start with 2;

create global temporary table MCMErrors (
  errcode NUMBER(2),
  parameters VARCHAR2(1000)
)
ON COMMIT DELETE ROWS;

create table errors (
  module       VARCHAR2(50),
  seq_number   NUMBER,
  error_number NUMBER,
  error_mesg   VARCHAR2(100),
  error_stack  VARCHAR2(2000),
  call_stack   VARCHAR2(2000),
  timestamp    DATE,
  PRIMARY KEY (module, seq_number)
);

create table call_stacks (
  module        VARCHAR2(50),
  seq_number    NUMBER,
  call_order    NUMBER,
  object_handle VARCHAR2(10),
  line_num      NUMBER,
  object_name   VARCHAR2(80),
  PRIMARY KEY (module, seq_number, call_order),
  FOREIGN KEY (module, seq_number) REFERENCES errors ON DELETE CASCADE
);

create table error_stacks (
  module        VARCHAR2(50),
  seq_number    NUMBER,
  error_order   NUMBER,
  facility      CHAR(3),
  error_number  NUMBER(5),
  error_mesg    VARCHAR2(100),
  PRIMARY KEY (module, seq_number, error_order),
  FOREIGN KEY (module, seq_number) REFERENCES errors ON DELETE CASCADE
);

create sequence error_seq start with 1 increment by 1;
create sequence copy_seq start with -1 increment by -1;