Rem
Rem $Header: ctx_src_2/src/dr/admin/d1102030.sql /st_ctx_11.2.0/4 2011/06/15 00:46:52 rkadwe Exp $
Rem
Rem d1200000.sql
Rem
Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      d1200000.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      downgrade from 11.2.0.3 to 11.2.0.2
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rkadwe      06/07/11 - Backport section specific attribute framework
Rem    gauryada    05/31/11 - Backport snippet support for result set interface
Rem    hsarkar     05/22/11 - Date and Pattern Stopclass
Rem    rpalakod    02/08/11 - downgrade from 11.2.0.3
Rem    rpalakod    02/08/11 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

REM ===================================================================
REM Bug 9950719: fuzzy_score has a mininum value of 1
REM ===================================================================

update dr$object_attribute
  set oat_val_min = 0
  where oat_id = 70103;

REM ==================================================================
REM Reverse Change from filter cache
REM ==================================================================
  
delete from dr$object_attribute
  where OAT_ID=90118 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=18
    and OAT_NAME='QUERY_FILTER_CACHE_SIZE';
commit;

drop public synonym ctx_filter_cache_statistics;
drop view ctx_filter_cache_statistics;

REM ------------------------------------------------------------------
REM  drop column spw_pattern dr$stopword
REM ------------------------------------------------------------------

declare
  errnum number;
begin
  execute immediate('alter table dr$stopword drop column spw_pattern');
exception
  when others then
    errnum := SQLCODE;
    if (errnum = -00904) then
      null;
    else
      raise;
    end if;
end;
/
REM ------------------------------------------------------------------
REM ctx_stopwords
REM ------------------------------------------------------------------

create or replace view ctx_stopwords as
select 
  u.name         spw_owner,
  spl_name       spw_stoplist,
  decode(spw_type, 1, 'STOP_CLASS', 2, 'STOP_WORD', 3, 'STOP_THEME', null)
                 spw_type,
  spw_word       spw_word,
  decode(spw_language, 'ALL', null, spw_language)   spw_language,
  spw_lang_dependent   spw_lang_dependent
from dr$stoplist, dr$stopword, sys.user$ u
where spl_id = spw_spl_id
  and spl_owner# = u.user#
/

create or replace public synonym ctx_stopwords for CTXSYS.CTX_STOPWORDS;
grant select on CTX_STOPWORDS to PUBLIC;

REM ------------------------------------------------------------------
REM ctx_user_stopwords
REM ------------------------------------------------------------------

create or replace view CTX_USER_STOPWORDS as
select 
  spl_name       spw_stoplist,
  decode(spw_type, 1, 'STOP_CLASS', 2, 'STOP_WORD', 3, 'STOP_THEME', null)
                 spw_type,
  spw_word       spw_word,
  decode(spw_language, 'ALL', null, spw_language)   spw_language,
  spw_lang_dependent   spw_lang_dependent
from dr$stoplist, dr$stopword
where spl_id = spw_spl_id
  and spl_owner# = userenv('SCHEMAID')
/

create or replace public synonym CTX_USER_STOPWORDS
for CTXSYS.CTX_USER_STOPWORDS;
grant select on CTX_USER_STOPWORDS to PUBLIC;

REM ==================================================================
REM Reverse Changes from Forward Index project
REM ==================================================================

delete from dr$object_attribute
  where OAT_ID=90119 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=19
    and OAT_NAME='FORWARD_INDEX';
delete from dr$object_attribute
  where OAT_ID=90120 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=20
    and OAT_NAME='O_TABLE_CLAUSE';
delete from dr$object_attribute
  where OAT_ID=90121 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=21
    and OAT_NAME='O_INDEX_CLAUSE';
delete from dr$object_attribute
  where OAT_ID=90122 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=22
    and OAT_NAME='SAVE_COPY';
delete from dr$object_attribute
  where OAT_ID=90123 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=23
    and OAT_NAME='D_TABLE_CLAUSE';
delete from dr$object_attribute
  where OAT_ID=90124 and OAT_CLA_ID=9 and OAT_OBJ_ID=1 and OAT_ATT_ID=24
    and OAT_NAME='SAVE_COPY_MAX_SIZE';
commit;

REM ===================================================================
REM Drop configuration column from dr$index
REM ===================================================================
alter table dr$index drop column idx_config_column;


REM ===================================================================
REM Reverse Section specific attributes project changes
REM ===================================================================

REM Migrating back from section specific attributes infrastructure to
REM colon separated infrastructure

declare
  sec_id       number := 0;
  sec_csv      varchar2(500);
  sec_oat_id   number;
  sec_rcnt     number;
  cnt          number := 0;
begin
  for c1 in (select ixv_idx_id, ixv_value, ixv_sub_group, ixv_sub_oat_id
               from dr$index_value, dr$object_attribute
              where ixv_oat_id = oat_id
                and oat_name = 'SECTION_ATTRIBUTE'
           order by ixv_sub_group)
  loop
    if (sec_id <> c1.ixv_sub_group) then
      sec_id := c1.ixv_sub_group;
      select count(*) into sec_rcnt from dr$index_value
        where ixv_idx_id = c1.ixv_idx_id
          and ixv_sub_group = c1.ixv_sub_group;
    end if;

    if (c1.ixv_sub_oat_id = 240107) then
      sec_csv :=  sec_csv || 'T' || c1.ixv_value;
    else
      sec_csv :=  sec_csv || c1.ixv_value ||':';
    end if;
    cnt := cnt + 1;

    if (sec_rcnt = cnt and sec_rcnt <> 0) then
      select ixv_oat_id into sec_oat_id from dr$index_value
        where ixv_idx_id = c1.ixv_idx_id
          and ixv_value = to_char(sec_id);
      insert into dr$index_value values
        (c1.ixv_idx_id, sec_oat_id, rtrim(sec_csv, ':'), 0, 0);
      delete from dr$index_value
        where ixv_idx_id = c1.ixv_idx_id
          and ixv_value = to_char(sec_id);
      delete from dr$index_value
        where ixv_idx_id = c1.ixv_idx_id
          and ixv_sub_group = sec_id;
      update dr$index_object
        set ixo_acnt = ixo_acnt - sec_rcnt
        where ixo_cla_id = 5
          and ixo_idx_id = c1.ixv_idx_id;
      sec_csv := '';
      sec_rcnt := 0;
      cnt := 0;
    end if;

  end loop;
end;
/

alter table dr$class drop constraint drc$cla_key;

drop table dr$section_attribute;

update dr$object_attribute
  set oat_datatype = 'S'
  where oat_cla_id = 5;

delete from dr$object_attribute
  where oat_name = 'SECTION_ATTRIBUTE';

delete from dr$object_attribute
  where oat_cla_id = 24;

delete from dr$object where obj_cla_id = 24;

delete from dr$class where cla_id = 24;