Rem  Copyright (c) Oracle Corporation 2015. All Rights Reserved.
Rem
Rem    NAME
Rem      ords_schema_mapping.sql
Rem
Rem    DESCRIPTION
Rem      Enable APEX Workspaces to be looked up via ORDS_PUBLIC_USER.
Rem      Do not invoke this script directly.  If you are doing a manual installation, use ords_manual_install.sql
Rem
Rem    Arguments:
Rem
Rem    NOTES
Rem      Assumes user with SYSDBA privilege is connected.
Rem
Rem    REQUIREMENTS
Rem      - Oracle Database Release 11.1 or later
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    cdivilly    09/28/2015  Created.
Rem    cdivilly    11/25/2015  Patch WWV_FLOW_POOL_CONFIG view if necessary
Rem

set define off
set verify off
set termout on
whenever sqlerror exit sql.sqlcode

prompt INFO: Configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS schemas

alter session set current_schema = SYS;

declare
 l_apex_listener_exists number := 0;
 l_pool_config_exists   number := 0;
 l_apex_schema          varchar2(255) := null;
 l_apex_view            varchar2(255) := null;
 l_pool_config_updated  number := 0;
 l_current_release  varchar2(100) := '<unknown>';
begin
    -- determine if/what version of APEX is installed
    for c1 in ( select version, schema
                  from sys.dba_registry
                 where comp_id = 'APEX' )
    loop
        l_current_release := c1.version;
        l_apex_schema := c1.schema;
    end loop;
    
    -- check apex_listener user has been created
   select count(*) into l_apex_listener_exists from dba_users where
        username = 'APEX_LISTENER';
        
   -- check for APEX versions that need patching     
   if ( substr(l_current_release,1,5) in ('4.2.6','5.0.0','5.0.1','5.0.2')) and (l_apex_listener_exists = 1) then
    -- APEX is installed and is a version that may need patching, check if POOL_CONFIG view exists (>= APEX 4.2)

    select count(*) into l_pool_config_exists from dba_synonyms where owner = 'APEX_LISTENER' and synonym_name = 'POOL_CONFIG';

    if ( l_pool_config_exists = 1 ) then
      -- figure out the view backing the pool_config synonym
      select table_name into l_apex_view from dba_synonyms where owner = 'APEX_LISTENER' and synonym_name = 'POOL_CONFIG';

      -- check if the view has the patched columns (we just check for one of them)
      select count(*) into l_pool_config_updated from dba_tab_cols where table_name = l_apex_view and owner = l_apex_schema and column_name = 'IDENTITY_DOMAIN';

      if ( l_pool_config_updated = 0 ) then
      -- The WWV_FLOW_POOL_CONFIG view needs to be updated to add the additional columns ORDS requires
      -- below is a copy and paste from the relevant APEX patch: (21537995)
      execute immediate 'create or replace view ' || l_apex_schema || '.wwv_flow_pool_config
as
select null                                  pool_name,
       c.short_name                          workspace_identifier,
       case when c.host_prefix is null
            then lower(c.path_prefix)
            else lower(c.host_prefix) end    as uri,
       case when c.host_prefix is null
            then ''BASE_PATH''
            else ''BASE_URL'' end              as type,
       coalesce (
           c.created_on,
           date''2015-01-01'' )                updated,
       c.provisioning_company_id             tenant_id,
       c.cloud_group_name                    identity_domain           
from   wwv_flow_companies c
where  c.provisioning_company_id not in (0,10,11,12)
and    c.account_status in (''AVAILABLE'', ''ASSIGNED'')
and    exists (  select 1
                  from wwv_flow_platform_prefs
                 where name  = ''RESTFUL_SERVICES_ENABLED''
                   and value = ''Y'')      
';

      end if;
      -- grant ORDS_METADATA select on underlying view
      for i in ( select username
                   from sys.dba_users
                  where username in ('ORDS_METADATA'))
      loop
          execute immediate 'grant select on ' || l_apex_schema || '.wwv_flow_pool_config to '||i.username ||' with grant option';
      end loop;

      -- create the ORDS_METADATA view over APEX_??????.WWV_FLOW_POOL_CONFIG
      execute immediate 'create or replace view ORDS_METADATA.apex_pool_config as select * from APEX_LISTENER.pool_config';
    end if;
  end if;
  if ( l_pool_config_exists <> 1 ) then
    -- If APEX is not installed then we need to stub out the APEX_POOL_CONFIG view
    -- Note that this will cause an issue if APEX is installed *after* ORDS, as ORDS will be using this stubbed
    -- out view instead of the real APEX view. We need code in ORDS to detect this situation and instruct user on how to resolve
    execute immediate 'create or replace view ORDS_METADATA.apex_pool_config as select rpad(''BASE_PATH'',9) type, rpad(''dummmy'',255) uri, rpad(''dummy'',30) workspace_identifier, 1 tenant_id, rpad(''none'',255) identity_domain, systimestamp updated from dual where 1 <> 1';
  end if;
  execute immediate 'grant select on ORDS_METADATA.apex_pool_config to ords_public_user';
end;
/
-- Create a view that joins APEX_POOL_CONFIG and POOL_CONFIG, indicating the type of mapping
create or replace view ORDS_METADATA.unified_pool_config as
SELECT
  CASE
   WHEN o.workspace_identifier is not null and a.workspace_identifier is not null THEN 'BOTH'
   WHEN o.workspace_identifier is null and a.workspace_identifier is not null THEN 'APEX'
   WHEN a.workspace_identifier is null and o.workspace_identifier is not null THEN 'ORDS'
  END schema_type,
  NULL pool_name,
  NVL(o.workspace_identifier,a.workspace_identifier) workspace_identifier,
  NVL(o.type,a.type) type,
  NVL(o.uri,a.uri) uri,
  NVL(o.updated,a.updated) updated,
  a.tenant_id,
  o.enc_key,
  o.mac_key
FROM ORDS_METADATA.pool_config o
FULL JOIN ORDS_METADATA.apex_pool_config a
ON o.workspace_identifier = a.workspace_identifier
/
grant select on ORDS_METADATA.unified_pool_config to ords_public_user
/
prompt INFO: Completed configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS Schemas

alter session set current_schema = ORDS_METADATA;
