/*
These 5 temp tables used to copy global workspace obj(only cmpelment+namespace) into each workpace
we do not need to copy from CMPSYSClasses since all the info there are static...
by default, the tmp table rows will be deleted on commit.
*/
create global temporary table WS_TMP_CMPFCOClasses as select * from CMPFCOClasses;
create global temporary table WS_TMP_CMPSCOClasses as select * from CMPSCOClasses;
create global temporary table WS_TMP_CMPSCOCfgClasses  as select * from CMPSCOCfgClasses;
create global temporary table WS_TMP_CMPSCOMapClasses as select * from CMPSCOMapClasses;
create global temporary table WS_TMP_CMPSCOPrpClasses as select * from CMPSCOPrpClasses;
create global temporary table WS_TMP_FCOFOLDERNamespace  as select * from FCOFOLDERNamespace;

--Bug 5583003: Copy/Paste requires seeded MCM Association data be copied into each workspace.
--             Not sure which rows (all?) are needed, but at least those rows where the
--             parentid=workspaceid (pctree) or linkfrom=workspaceid (interlink, intralink)
--Bug 6519987: Limit copied MCM Association data to rows immediately owned by workspace.

create global temporary table WS_TMP_AllPCTree as select * from AllPCTree;
create global temporary table WS_TMP_AllInterlink as select * from AllInterlink;
create global temporary table WS_TMP_AllIntralink as select * from AllIntralink;


--fix bug 7417117: introduce a table to hold the prefered workspace choices for each user
-- workspace_user is the user who select the workspace_owner,  workspace_name as his prefered workspace, by default the 1st registered/owned workspace will be his prfered workspace, and end user can change the preferened workspace from Preference graphic user interface

create table workspace_preference 
(
--workspaceid number, 
workspace_owner varchar2(30), 
workspace_name varchar2(200),
workspace_user varchar2(30) unique
);

-- fix bug 8206958: getAnotherConnection will fail if the main connection is single user mode, so will not try raise error if not need it is trying to get another connection
create table ignore_lockerror_set_workspace 
(
  ignore_lockerror number unique

);


--create a view which shows all the assignment

create or replace view workspace_assignment
(
workspace_id,
workspace_name,
business_name,
description,
user_name,
isWorkspaceOwner,
createdBy,
creationTimestamp,
updatedby,
UpdateTimestamp
) as
(
select workspace.elementid ,
       workspace.name,
       workspace.logicalname,
       workspace.description ,
       users.name ,
       users.isWorkspaceOwner,
       users.createdBy ,
       users.CreationTimestamp,
       users.UpdatedBy,
       users.UpdateTimestamp
from cmpworkspace_v workspace, cmpallwbuser_v users
where users.workspaceid = workspace.elementid
 );