Rem Rem $Header: sdo/admin/sdordfctx.sql /st_sdo_11.2.0/5 2011/05/23 03:19:20 sdas Exp $ Rem Rem sdordftx.sql Rem Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdordftx.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem sdas 05/20/11 - tracing using ODCIenv Rem sdas 05/12/11 - extend the batchExtractRdf interface Rem spsundar 05/09/11 - rename sem_contains_score to sem_contains_count Rem sdas 04/29/11 - mix of batch and regular policies Rem sdas 04/25/11 - batch rebuild with multiple base-policies Rem sdas 04/23/11 - continue batch interface: use two Rem index_merge/index_load calls for GLOBAL index Rem sdas 04/19/11 - batch interface implementation Rem spsundar 04/11/11 - add new methods to interface for rdfctx_extractor Rem sdas 02/01/11 - SDO version check should look for 11 or higher Rem sdas 12/30/10 - pass mapping table names to build_index_partition Rem spsundar 12/07/10 - add support for local semantic index Rem sdas 08/11/10 - XbranchMerge sdas_bug-9937565 from st_sdo_11.2.0 Rem sdas 07/14/10 - allow loadMethod spec in prllidx_create type spec Rem matperry 06/30/10 - move creation of object types for parallel index Rem form prvtrdfctx to sdordfctx Rem ayalaman 06/09/09 - stylesheet for web-service information extractor Rem matperry 05/26/09 - remove WHENEVER SQLERROR EXIT Rem ayalaman 05/20/09 - add methods set_extractor_param to rdfctx Rem ayalaman 05/07/09 - gate nlp integration Rem ayalaman 04/14/09 - handling 3rd party license key Rem ayalaman 03/19/09 - rename select operator Rem ayalaman 03/10/09 - ctx_entity extractor Rem ayalaman 02/27/09 - user ontologies and rule bases Rem ayalaman 02/09/09 - domain index implementation Rem ayalaman 01/13/09 - RDF text search Rem ayalaman 01/13/09 - Created Rem --WHENEVER SQLERROR EXIT; declare rc NUMBER; version VARCHAR2(100); ver_num NUMBER; begin select count(*) into rc from all_objects where owner = 'MDSYS' and object_name = 'SDO_RDF'; version := dbms_registry.version('SDO'); ver_num := to_number(substr(version,1,instr(version,'.')-1)); if (ver_num < 11 or rc = 0) then raise_application_error(-20000, 'RDF Component not installed:'|| 'RDF Component for 11.1 or above must be installed.'); end if; end; / create role rdfctx_admin; WHENEVER SQLERROR CONTINUE; grant execute on DBMS_NETWORK_ACL_ADMIN to mdsys; BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl ( acl => 'mdsys_rdfctx.xml', description => 'ACL for web service access from MDSYS', principal => 'MDSYS', is_grant => TRUE, privilege => 'connect', start_date => SYSTIMESTAMP, end_date => NULL); COMMIT; exception when others then if (SQLCODE != -31003) then raise; end if; END; / alter session set current_schema = MDSYS; /*****************************************************************************/ /*** Text extractor for semantic searches ***/ /*****************************************************************************/ create or replace type rdfctx_extractor authid current_user as object ( extr_type VARCHAR2(32), member procedure startDriver, member function getDescription return VARCHAR2, -- 'RDF/XML','RDF/XML-ESC', etc member function rdfReturnType return VARCHAR2, -- In some cases, the rdf/xml text has escape sequences for the -- XML tags which makes them invalid XML. Hence a generic CLOB type is -- is used. member function extractRdf(document CLOB, docId VARCHAR2) return CLOB, member procedure closeDriver ) not instantiable not final / show errors; -- add a new method to extractor type that returns the value for a specified attribute -- including: -- DBUSER: the DB user on the extractor side who is going to connect to the database -- to read from the documents from the docTable and -- write extracted info (in rdfReturnType format) to the extr_info_table -- -- BATCH_SUPPORT: 'YES' or 'NO' -- -- more to follow... alter type rdfctx_extractor add member function getContext(attribute VARCHAR2) return VARCHAR2; -- parameters (such as quality of extraction) will be passed alter type rdfctx_extractor add member function extractRdf(document CLOB, docId VARCHAR2, params VARCHAR2, options VARCHAR2 default NULL) return CLOB; -- for batch extraction, we pass -- a cursor for reading the documents -- a table_name for holding data extracted from the docs -- params (such as quality of extraction) that will be passed on uninterpreted alter type rdfctx_extractor add member function batchExtractRdf(docCursor SYS_REFCURSOR, extracted_info_table VARCHAR2, params VARCHAR2, partition_name VARCHAR2 default NULL, docId VARCHAR2 default NULL, preferences SYS.XMLType default NULL, options VARCHAR2 default NULL) return CLOB; grant execute on rdfctx_extractor to public; /*****************************************************************************/ /*** Text extractor that is implemented as a web service ***/ /*****************************************************************************/ create or replace type rdfctx_ws_extractor under rdfctx_extractor ( ws_end_point VARCHAR2(1024), ws_soap_act VARCHAR2(1024), ws_envelope CLOB, ws_xsltrans sys.XMLType, overriding member function getDescription return VARCHAR2, overriding member function rdfReturnType return VARCHAR2, -- sets the proxy server for the current instance -- overriding member procedure startDriver, -- post the http request to the web service end-point and -- returns the response -- overriding member function extractRdf(document CLOB, docId VARCHAR2) return CLOB, -- closes the driver -- overriding member procedure closeDriver ) not instantiable not final / show errors; grant execute on rdfctx_ws_extractor to public; /*****************************************************************************/ /*** SEM_RDFCTX : Package for configuring Semantic search on Text ***/ /*****************************************************************************/ create or replace package sem_rdfctx authid current_user as procedure create_policy ( policy_name VARCHAR2, extractor mdsys.rdfctx_extractor, preferences sys.XMLType default null); procedure create_policy ( policy_name VARCHAR2, base_policy VARCHAR2); procedure create_policy ( policy_name VARCHAR2, base_policy VARCHAR2, user_models mdsys.rdf_models, rulebases mdsys.rdf_rulebases default null); procedure create_policy ( policy_name VARCHAR2, base_policy VARCHAR2, user_models mdsys.rdf_models, user_entailments mdsys.rdf_models, rulebases mdsys.rdf_rulebases default null); procedure drop_policy ( policy_name VARCHAR2); procedure set_default_policy ( index_name VARCHAR2, policy_name VARCHAR2); procedure add_dependent_policy ( index_name VARCHAR2, policy_name VARCHAR2, partition_name VARCHAR2 default NULL); function extract_rdfxml ( doc CLOB, ext_type mdsys.rdfctx_extractor) return CLOB; procedure maintain_triples ( index_name VARCHAR2, where_clause VARCHAR2, rdfxml_content sys.XMLType, policy_name VARCHAR2 default NULL, action VARCHAR2 default 'ADD'); procedure set_extractor_param ( param_key VARCHAR2, param_value VARCHAR2, param_desc VARCHAR2); end sem_rdfctx; / show errors; create public synonym sem_rdfctx for mdsys.sem_rdfctx; grant execute on sem_rdfctx to public; /*****************************************************************************/ /*** CALAIS Extractor : web service extractor ***/ /*****************************************************************************/ create or replace type calais_extractor under rdfctx_ws_extractor ( constructor function calais_extractor ( extr_params sys.XMLType default null) return self as result, constructor function calais_extractor ( ws_end_point VARCHAR2, ws_soap_act VARCHAR2, extr_params sys.XMLType default null) return self as result, overriding member function extractRdf(document CLOB, docId VARCHAR2) return CLOB ) / grant execute on calais_extractor to public; /*****************************************************************************/ /*** CTX_ENTITY_EXTRACTOR : Oracle Context extractor ***/ /*****************************************************************************/ create or replace type ctx_entity_extractor under rdfctx_extractor ( epl_policy VARCHAR2(30), xsl_trans sys.XMLtype, constructor function ctx_entity_extractor ( epl_policy VARCHAR2, base_url VARCHAR2 default null) return self as result, constructor function ctx_entity_extractor ( epl_policy VARCHAR2, xsl_trans sys.xmltype) return self as result, overriding member function getDescription return VARCHAR2, overriding member function rdfReturnType return VARCHAR2, overriding member function extractRdf(document CLOB, docId VARCHAR2) return CLOB, overriding member function extractRdf(document CLOB, docId VARCHAR2, params VARCHAR2, options VARCHAR2 default NULL) return CLOB ) / grant execute on ctx_entity_extractor to public; /*****************************************************************************/ /*** GATE NLP Extractor : extractor using GATE NLP ***/ /*****************************************************************************/ create or replace type gatenlp_extractor under mdsys.rdfctx_extractor ( gate_host VARCHAR2(100), gate_port NUMBER, xsl_trans sys.XMLtype, constructor function gatenlp_extractor (base_url VARCHAR2 default null) return self as result, constructor function gatenlp_extractor (xsl_trans sys.xmltype) return self as result, overriding member function getDescription return VARCHAR2, overriding member function extractRdf (document CLOB, docId VARCHAR2) return CLOB ); / grant execute on gatenlp_extractor to public; /*****************************************************************************/ /*** Types for parallel index creation ***/ /*****************************************************************************/ create or replace type doublenumlist as table of sys.odcinumberlist; / create or replace type fn_extarr is varray(5) of mdsys.rdfctx_extractor; / create or replace type fn_xmlpref is varray(5) of sys.xmltype; / create or replace type prllidx_create authid current_user as object ( c1 number, static function index_merge( idxschema IN varchar2, idxname IN varchar2, moderr IN sys.odcinumberlist, flags IN number, c1 IN SYS_REFCURSOR) return sys.odcinumberlist parallel_enable (partition c1 by any), static function index_load( idxschema IN varchar2, idxname IN varchar2, filedir varchar2, filterpl varchar2, basepol sys.ODCIVarchar2List, extrParams sys.ODCIVarchar2List, ctxpref fn_xmlpref, extarr fn_extarr, modarr sys.ODCINumberList, polrids sys.ODCIvarchar2List, loadMethod varchar2, stagtab_name varchar2, docUri2Rid_name varchar2, docVid2Rid_name varchar2, is_batchpol sys.ODCINumberList, flags number, cur SYS_REFCURSOR) return doublenumlist parallel_enable (partition cur by any) pipelined, static function build_index_partition( idxinfo sys.ODCIIndexInfo, l_modarr sys.ODCINumberList, filedir VARCHAR2, filterpl varchar2, l_basepol sys.ODCIVarchar2List, l_extrParams sys.ODCIVarchar2List, l_ctxpref fn_xmlpref, t_extarr fn_extarr, l_polrids sys.ODCIVarchar2List, loadMethod varchar2, stagtab_name varchar2, docUri2Rid_name varchar2, docVid2Rid_name varchar2, xinfotab_name varchar2, flags number) return number, static function build_index_batch_extr( idxinfo sys.ODCIIndexInfo , l_modarr sys.ODCINumberList , filedir VARCHAR2 , filterpl varchar2 , l_basepol sys.ODCIVarchar2List , l_batchpol sys.ODCINumberList , l_extrParams sys.ODCIVarchar2List , l_ctxpref fn_xmlpref , t_extarr fn_extarr , l_polrids sys.ODCIVarchar2List , l_errcnt IN OUT sys.ODCINumberList , l_docUriPfx VARCHAR2 , l_docUriShortPfx VARCHAR2 , loadMethod varchar2 , stagtab_name varchar2 , l_stagtab_suffixes sys.ODCIvarchar2List , docUri2Rid_name varchar2 , docVid2Rid_name varchar2 , xinfotab_name varchar2 , l_sel_basetab VARCHAR2 , flags number ) RETURN NUMBER ); / show errors; grant execute on prllidx_create to public; @@prvtrdfctx.plb /*****************************************************************************/ /*** Indextype and Operators ***/ /*****************************************************************************/ create or replace operator sem_contains binding (VARCHAR2, VARCHAR2) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (VARCHAR2, VARCHAR2, VARCHAR2) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (VARCHAR2, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (CLOB, VARCHAR2) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (CLOB, VARCHAR2, mdsys.RDF_ALIASES) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (CLOB, VARCHAR2, VARCHAR2) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (CLOB, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (VARCHAR2, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains, (CLOB, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains; grant execute on sem_contains to public; create or replace public synonym sem_contains for mdsys.sem_contains; create or replace operator sem_contains_select binding (NUMBER) return CLOB ANCILLARY TO mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) USING mdsys.sem_rdfctx_oper.sem_rdfctx_select; grant execute on sem_contains_select to public; create or replace public synonym sem_contains_select for mdsys.sem_contains_select; create or replace operator sem_contains_count binding (NUMBER) return NUMBER ANCILLARY TO mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) USING mdsys.sem_rdfctx_oper.sem_rdfctx_count; grant execute on sem_contains_count to public; create or replace public synonym sem_contains_count for mdsys.sem_contains_count; create indextype SemContext for SEM_CONTAINS(VARCHAR2, VARCHAR2), SEM_CONTAINS(VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), SEM_CONTAINS(VARCHAR2, VARCHAR2, VARCHAR2), SEM_CONTAINS(VARCHAR2, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), SEM_CONTAINS(VARCHAR2, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES), SEM_CONTAINS(CLOB, VARCHAR2), SEM_CONTAINS(CLOB, VARCHAR2, mdsys.RDF_ALIASES), SEM_CONTAINS(CLOB, VARCHAR2, VARCHAR2), SEM_CONTAINS(CLOB, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), SEM_CONTAINS(CLOB, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) using SemContextIndexMethods with local range partition; grant execute on SemContext to public; begin insert into mdsys.rdf_parameter (namespace, attribute, value, description) values ('COMPONENT','RDFCTX','INSTALLED','Semantic (Text) Search component'); exception when dup_val_on_index then null; end; / commit;