Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem imu111.sql
Rem
Rem DESCRIPTION
Rem run as ORDSYS
Rem Internal upgrade script for Oracle Multimedia from 11.1
Rem
Rem !!!!! IMPORTANT !!!!!
Rem SEE imu111m.sql - Dicom repos data migration
Rem
Rem NOTES
Rem In 11.1 the dicom repository is installed in the ORDSYS schema.
Rem The repository in future releases is installed in ORDData schema.
Rem The user-defined documents in the ORDSYS DICOM repository need
Rem to be migrated to the ORDDATA Dicom repository.
Rem SEE imu111m.sql - Dicom repos data migration
Rem
Rem
--
-- create ORDDATA user
--
declare
l_ord_tbs varchar2(30);
ex exception;
-- ignore error ORA-01920, happens if the script is re-run
-- ORA-01920: user name 'ORDDATA' conflicts with another user or role name
pragma exception_init(ex, -1920);
begin
-- create orddata user and assign privs
-- create ORDData user in the same tablespace ORDSYS
select default_tablespace into l_ord_tbs
from sys.dba_users where username='ORDSYS';
begin
execute immediate
'create user orddata identified by orddata default tablespace '
|| l_ord_tbs;
EXCEPTION
-- ORA-01920: user name 'ORDDATA' conflicts with another user
WHEN ex THEN
NULL;
end;
execute immediate
'alter user orddata account lock password expire';
end;
/
--
-- call imxreg.sql to register new schemas
-- for stored tag list document
-- http://xmlns.oracle.com/ord/dicom/attributeTag_1_0(ordcmstl.xsd)
-- Manifest schema
-- http://xmlns.oracle.com/ord/dicom/manifest_1_0(ordcmmft.xsd)
-- The other schemas which are already registered do not change because
-- Oracle XML DB raises an exception if the schema_url being registered
-- already exists. imxreg.sql handles this exception and continues.
-- To change existing schemas use inplaceEvolve or copyEvolve
--
Rem
Rem Invoke imxreg.sql to register new XML Schemas if XDB is installed
Rem
COLUMN file_name NEW_VALUE comp_file NOPRINT;
SELECT sys.dbms_registry.script('XDB','@imxreg.sql') AS file_name FROM DUAL;
@&comp_file
--
-- Perform in place evolve for the IPTC metadata schema.
-- relax minOccurs from 1 to 0 for recordVersion element
-- Relaxed schema is kept during downgrade as old behavior
-- produces compatible documents.
--
declare
xmlDiff sys.xmltype;
begin
xmlDiff := xmltype('
0
');
xdb.dbms_xmlschema.inPlaceEvolve('http://xmlns.oracle.com/ord/meta/iptc', xmlDiff, 1);
end;
/
--
-- Perform in place evolve for the EXIF metadata schema.
-- relax minOccurs from 1 to 0 for GPSVersionID element
-- Relaxed schema is kept during downgrade as old behavior
-- produces compatible documents.
--
declare
xmlDiff sys.xmltype;
begin
xmlDiff := xmltype('
0
');
xdb.dbms_xmlschema.inPlaceEvolve('http://xmlns.oracle.com/ord/meta/exif', xmlDiff, 1);
end;
/
--
-- Perform in place evolve for the DICOM metadata ordcmmddt.xsd,
-- ordcmrdt.xsd and ordcmdts.xsd schema.
-- relax minOccurs from 1 to 0 for DATASET_T element and add optional
-- number attribute to the ATTR_GRP_T.
-- remove the pattern restriction on VALUE_LOCATOR_T and
-- VALUE_LOCATOR_MACRO_T.
-- Relaxed schema is kept during downgrade as old behavior
-- produces compatible documents.
--
declare
xmlDiff sys.xmltype;
xml_number sys.xmltype;
begin
xmlDiff := xmltype('
DICOM VR type SeQuence.
Note that item number can be explicitly encoded in XML.
Number counts from 1 up.
Each item is a DATASET_T type, which may contain
any combination of DICOM attributes.
0
Attribute group type (ATTR_GRP_T) is used by all DICOM attribute
definitions. It defines XML attributes that are used by all DICOM
attribute types. The "tag" attribute
defines DICOM attributes in little-endian encoding.
The "definer" attribute specifies the organization that has
created the attribute. By default, all DICOM standard attributes have
the definer name "DICOM". The "name" attribute
specifies the canonical attribute name as defined by the data
dictionary. For example, in an XML metadata schema definition,
you can choose a tag PATIENT_DATE_OF_BIRTH or "DOB"
for DICOM attribute (0010,0030), but its name attribute should
match that of the DICOM standard: "Patient''s
Birth Date". The "number" attribute is an optional
attribute to indicate the ordering of a multivalued attributes.
Number counts from 1 up.
The "truncated" attribute takes a Boolean value.
If it is true, it indicates that the original length of the
DICOM attribute exceeds the maximum length allowed for this XML
value;therefore, it is truncated in XML. When this attribute is true,
xsi:nill="true" for this attribute.
Optionally, the "rawValue" attribute can be used to store
values that do not conform to the DICOM standard. The associated
attribute "byteOrderLE" specifies the byte order
of the byte stream for the "rawValue" attribute.
"offset" and "length" are Oracle-reserved
attributes.
');
-- We only do the schema evolution if the schema has not been evolved.
-- In the case upgrade/downgrade/upgrade, the last upgrade won't evolve
-- the schema again
xml_number := null;
select extract(t.schema,
'/xs:schema/xs:attributeGroup/xs:attribute[@name="number"]',
'xmlns="http://xmlns.oracle.com/ord/dicom/mddatatype_1_0" ' ||
'xmlns:xs="http://www.w3.org/2001/XMLSchema" ' ||
'xmlns:xdb="http://xmlns.oracle.com/xdb"')
into xml_number from sys.all_xml_schemas t
where schema_url='http://xmlns.oracle.com/ord/dicom/mddatatype_1_0';
if (xml_number is null) then
-- evolve the datatype schema used by the default metadata schema
xdb.dbms_xmlschema.inPlaceEvolve(
'http://xmlns.oracle.com/ord/dicom/mddatatype_1_0',
xmlDiff,
xdb.dbms_xmlschema.inplace_evolve);
end if;
-- We only do the schema evolution if the schema has not been evolved.
-- In the case upgrade/downgrade/upgrade, the last upgrade won't evolve
-- the schema again
xml_number := null;
select extract(t.schema,
'/xs:schema/xs:attributeGroup/xs:attribute[@name="number"]',
'xmlns="http://xmlns.oracle.com/ord/dicom/datatype_1_0" ' ||
'xmlns:xs="http://www.w3.org/2001/XMLSchema" ' ||
'xmlns:xdb="http://xmlns.oracle.com/xdb"')
into xml_number from sys.all_xml_schemas t
where schema_url='http://xmlns.oracle.com/ord/dicom/datatype_1_0';
if (xml_number is null) then
-- evolve the datatype schema used by the repository document
xdb.dbms_xmlschema.inPlaceEvolve(
'http://xmlns.oracle.com/ord/dicom/datatype_1_0',
xmlDiff,
xdb.dbms_xmlschema.inplace_evolve);
end if;
-- We only do the schema evolution if the schema has not been evolved.
-- In the case upgrade/downgrade/upgrade, the last upgrade won't evolve
-- the schema again
xml_number := null;
select extract(t.schema,
'/xs:schema/xs:attributeGroup/xs:attribute[@name="number"]',
'xmlns="http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0" ' ||
'xmlns:xs="http://www.w3.org/2001/XMLSchema" ' ||
'xmlns:xdb="http://xmlns.oracle.com/xdb"')
into xml_number from sys.all_xml_schemas t
where schema_url='http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0';
if (xml_number is null) then
-- evolve the datatype schema provided for user application. There is
-- no reference in Oracle repository documents or metadata documents.
xdb.dbms_xmlschema.inPlaceEvolve(
'http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0',
xmlDiff,
xdb.dbms_xmlschema.inplace_evolve);
end if;
end;
/
--
-- Validate obsolete, invalid older versions of our objects.
-- There is no other way to validate them. (bug 7700436)
--
begin
UPDATE sys.obj$ SET status = 1
WHERE type#=13 AND subname LIKE '%$VSN\_%' ESCAPE '\' AND status=6
AND owner# in (SELECT u.user# from sys.user$ u
WHERE u.name in ('ORDSYS', 'ORDPLUGINS',
'SI_INFORMTN_SCHEMA', 'ORDDATA'));
COMMIT;
exception
when others then
-- continue regardless
null;
end;
/
--
-- revoke unneeded SYS privs from ORDSYS, ORDPLUGINS, SI_INFORMTN_SCHEMA
-- comes before imprv.sql
declare
cmd VARCHAR2(100);
begin
for rec in (select grantee, privilege
from sys.dba_sys_privs
where grantee in ('ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA')
and privilege not in ('UNLIMITED TABLESPACE'))
loop
begin
cmd := 'revoke ' || rec.privilege || ' from ' || rec.grantee;
execute immediate cmd;
-- ignore errors
exception
when others then null;
end;
end loop;
execute immediate 'revoke UNLIMITED TABLESPACE from ordplugins';
-- ignore errors
exception
when others then null;
end;
/
--
-- revoke unneeded roles from ORDSYS, ORDPLUGINS, SI_INFORMTN_SCHEMA
-- comes before imprv.sql
declare
cmd VARCHAR2(100);
begin
for rec in (select grantee, granted_role
from sys.dba_role_privs
where grantee in ('ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA'))
loop
-- this role is needed
if( (rec.grantee = 'ORDSYS') and (rec.granted_role = 'JAVAUSERPRIV') ) then
continue;
end if;
begin
cmd := 'revoke ' || rec.granted_role || ' from ' || rec.grantee;
execute immediate cmd;
-- ignore errors
exception
when others then null;
end;
end loop;
end;
/
--
-- revoke grant with grant option privs
--
declare
revoke_cmd VARCHAR2(200);
grant_cmd VARCHAR2(200);
begin
for rec in (select distinct a.owner, b.object_name, a.privilege,
b.object_type, a.grantee
from sys.dba_tab_privs a,
sys.dba_objects b
where a.grantee='PUBLIC'
and a.grantable='YES'
and (a.owner in ('ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA') or
a.grantor in ('ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA')
)
and a.table_name=b.object_name
-- filter out the types created for XML schema registration
and ( (b.object_type <> 'TYPE')
or ((b.object_type = 'TYPE') and
(b.object_name not like 'ORD\_DICOM\_HEADER%' escape '\') and
((b.object_name like 'ORD%') or
(b.object_name like 'SI\_%' escape '\'))
)
)
)
loop
begin
revoke_cmd := 'revoke ' || rec.privilege || ' on ' || rec.owner ||
'.' || rec.object_name || ' from ' || rec.grantee || ' force' ;
grant_cmd := 'grant ' || rec.privilege || ' on ' || rec.owner ||
'.' || rec.object_name || ' to ' || rec.grantee;
execute immediate revoke_cmd;
-- We don't want to grant if revoke fails. Can no longer think of a
-- case where revoke might fail now that we're using force.
begin
-- Revoke again because some grants were issued twice - once as
-- grantor sys and once as grantor ordsys. A second revoke is
-- needed to clean up the second grant. Handle exception so we
-- do go on to regrant if this second revoke fails
execute immediate revoke_cmd;
exception
when others then null;
end;
execute immediate grant_cmd;
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
end loop;
end;
/
-- revoke execute to public from ORDIMERRORCODES
declare
cmd VARCHAR2(200);
begin
cmd := 'revoke execute on ordsys.ordimerrorcodes from public force';
execute immediate cmd;
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
/
--
-- Miscellaneous revokes for problems found in Fengting's upgrade
-- verification tests
--
begin
execute immediate
'revoke execute on ordsys.ordimageindexstats from public force';
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
/
--
-- The following grants were granted with grant option at one point. They
-- are not needed. Revoking.
--
begin
execute immediate 'revoke execute on sys.dbms_registry from ordsys';
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
/
begin
execute immediate 'revoke select on sys.obj$ from ordsys';
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
/
begin
execute immediate 'revoke select on sys.user$ from ordsys';
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
/
begin
execute immediate 'revoke select on sys.dba_views from ordsys';
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
/
begin
execute immediate 'revoke select on sys.dba_tables from ordsys';
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
/
begin
execute immediate 'revoke select on sys.dba_object_tables from ordsys';
-- We want to continue regardless of errors so ignore errors.
exception
when others then null;
end;
/
--
-- drop 11.1 DICOM views from ORDSYS
-- The public synonyms have the same name as the views
-- The view name in schema takes precedence over public synonym with same name
-- This causes a problem if the view definition has changed between 11.1( ORDSYS)
-- and 11.2(ORDDATA).
-- Drop public synonym to public views
begin
for cur in (select 'orddcm_documents' syn_name from dual
union
select 'orddcm_document_types' sys_name from dual
union
select 'orddcm_document_refs' sys_name from dual
union
select 'orddcm_conformance_vld_msgs' sys_name from dual
union
select 'orddcm_constraint_names' sys_name from dual) loop
begin
EXECUTE IMMEDIATE ' drop public synonym ' || cur.syn_name;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
end loop;
end;
/
--
-- drop 11.1 dicom views in ORDSYS
--
BEGIN
for cur in ( select view_name from sys.dba_views
where owner='ORDSYS' and view_name like 'ORDDCM_%') loop
begin
EXECUTE IMMEDIATE ' DROP VIEW ORDSYS.' || cur.view_name;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
end loop;
END;
/
-- grant privs
@@imprv.sql
-- create DICOM tables in ORDDATA schema
@@ordcrtbl.plb
--create DICOM views in ORDDATA schema
@@ordcrvw.plb
-- Leave data , tables in ordsys unchanged for downgrade purposes.
-- ordsys might contain user documents.
-- Drop obsolete validate_ordim_with_details procedure. This should have been
-- dropped in 10.2 to 11.1 upgrade, but wasn't done there. We'll do it here
-- to catch all upgrades to 11.2.
begin
execute immediate 'DROP procedure ORDSYS.validate_ordim_with_details';
end;
/
-- add watermark methods
begin
execute immediate 'ALTER TYPE ORDSYS.ORDImage
ADD MEMBER PROCEDURE applyWatermark(SELF IN OUT NOCOPY ORDSYS.ORDImage,
added_text IN VARCHAR2,
dest IN OUT NOCOPY ORDSYS.ORDImage,
logging OUT VARCHAR2,
watermark_properties IN ORDSYS.ORD_STR_LIST default null)
CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDImage
ADD MEMBER PROCEDURE applyWatermark(SELF IN OUT NOCOPY ORDSYS.ORDImage,
added_image IN OUT NOCOPY ORDSYS.ORDImage,
dest IN OUT NOCOPY ORDSYS.ORDImage,
logging OUT VARCHAR2,
watermark_properties IN ORDSYS.ORD_STR_LIST default null)
CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDImage
ADD STATIC PROCEDURE applyWatermark(imageBlob IN BLOB,
added_text IN VARCHAR2,
dest IN OUT NOCOPY BLOB,
logging OUT VARCHAR2,
watermark_properties IN ordsys.ord_str_list default null)
CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDImage
ADD STATIC PROCEDURE applyWatermark(imageBlob IN BLOB,
added_image IN BLOB,
dest IN OUT NOCOPY BLOB,
logging OUT VARCHAR2,
watermark_properties IN ordsys.ord_str_list default null)
CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDImage
ADD STATIC PROCEDURE applyWatermark(imageBfile IN OUT NOCOPY BFILE,
added_text IN VARCHAR2,
dest IN OUT NOCOPY BLOB,
logging OUT VARCHAR2,
watermark_properties IN ordsys.ord_str_list default null)
CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDImage
ADD STATIC PROCEDURE applyWatermark(imageBfile IN OUT NOCOPY BFILE,
added_image IN OUT NOCOPY BFILE,
dest IN OUT NOCOPY BLOB,
logging OUT VARCHAR2,
watermark_properties IN ordsys.ord_str_list default null)
CASCADE';
end;
/
--
-- Add user-defined object constructors
--
begin
execute immediate 'ALTER TYPE ORDSYS.ORDImage
ADD CONSTRUCTOR FUNCTION ORDImage(
SELF IN OUT NOCOPY ORDSYS.ORDImage,
data IN BLOB,
setProperties IN INTEGER DEFAULT 0)
RETURN SELF AS RESULT CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDImage
ADD CONSTRUCTOR FUNCTION ORDImage(
SELF IN OUT NOCOPY ORDSYS.ORDImage,
source_type IN VARCHAR2 DEFAULT ''LOCAL'',
source_location IN VARCHAR2 DEFAULT NULL,
source_name IN VARCHAR2 DEFAULT NULL,
setProperties IN INTEGER DEFAULT 0)
RETURN SELF AS RESULT CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDAudio
ADD CONSTRUCTOR FUNCTION ORDAudio(
SELF IN OUT NOCOPY ORDSYS.ORDAudio,
data IN BLOB,
setProperties IN INTEGER DEFAULT 0)
RETURN SELF AS RESULT CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDAudio
ADD CONSTRUCTOR FUNCTION ORDAudio(
SELF IN OUT NOCOPY ORDSYS.ORDAudio,
source_type IN VARCHAR2 DEFAULT ''LOCAL'',
source_location IN VARCHAR2 DEFAULT NULL,
source_name IN VARCHAR2 DEFAULT NULL,
setProperties IN INTEGER DEFAULT 0)
RETURN SELF AS RESULT CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDVideo
ADD CONSTRUCTOR FUNCTION ORDVideo(
SELF IN OUT NOCOPY ORDSYS.ORDVideo,
data IN BLOB,
setProperties IN INTEGER DEFAULT 0)
RETURN SELF AS RESULT CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDVideo
ADD CONSTRUCTOR FUNCTION ORDVideo(
SELF IN OUT NOCOPY ORDSYS.ORDVideo,
source_type IN VARCHAR2 DEFAULT ''LOCAL'',
source_location IN VARCHAR2 DEFAULT NULL,
source_name IN VARCHAR2 DEFAULT NULL,
setProperties IN INTEGER DEFAULT 0)
RETURN SELF AS RESULT CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDDoc
ADD CONSTRUCTOR FUNCTION ORDDoc(
SELF IN OUT NOCOPY ORDSYS.ORDDoc,
data IN BLOB,
setProperties IN INTEGER DEFAULT 0)
RETURN SELF AS RESULT CASCADE';
end;
/
begin
execute immediate 'ALTER TYPE ORDSYS.ORDDoc
ADD CONSTRUCTOR FUNCTION ORDDoc(
SELF IN OUT NOCOPY ORDSYS.ORDDoc,
source_type IN VARCHAR2 DEFAULT ''LOCAL'',
source_location IN VARCHAR2 DEFAULT NULL,
source_name IN VARCHAR2 DEFAULT NULL,
setProperties IN INTEGER DEFAULT 0)
RETURN SELF AS RESULT CASCADE';
end;
/
--
-- Drop Deprecated Image Indexing and any user index of type ORDIMAGEINDEX
--
--
-- First drop user indexes of type ORDSYS.ORDIMAGINDEX
--
declare
cursor imgidx_curs is select index_name, owner from sys.dba_indexes
where index_type = 'DOMAIN' and ityp_name = 'ORDIMAGEINDEX';
index_name VARCHAR2(30);
index_owner VARCHAR2(30);
idxcurs integer;
begin
idxcurs := sys.dbms_sql.open_cursor;
open imgidx_curs;
loop
begin
fetch imgidx_curs into index_name, index_owner;
exit when imgidx_curs%NOTFOUND;
-- Note: these drop statements are executed immediately upon parse
-- thus the class name needs to be specified, but no execute is
-- required
--
-- drop the domain index
sys.dbms_sql.parse(idxcurs, 'drop index ' ||
sys.dbms_assert.enquote_name(index_owner) || '.' ||
sys.dbms_assert.enquote_name(index_name) || ' force',
sys.dbms_sql.native);
-- drop associated table
sys.dbms_sql.parse(idxcurs, 'drop table ' ||
sys.dbms_assert.enquote_name(index_owner) || '.' ||
sys.dbms_assert.enquote_name(index_name) || '_FT$',
sys.dbms_sql.native);
exception when others then
raise;
end;
end loop;
close imgidx_curs;
sys.dbms_sql.close_cursor(idxcurs);
end;
/
begin
execute immediate 'drop indextype ordsys.ORDIMAGEINDEX';
exception when others then
null;
end;
/
begin
execute immediate 'drop operator ordsys.IMGScore';
exception when others then
null;
end;
/
begin
execute immediate 'drop operator ordsys.IMGSimilar';
exception when others then
null;
end;
/
begin
execute immediate 'drop function ordsys.ScoreFunc';
exception when others then
null;
end;
/
begin
execute immediate 'drop function ordsys.SimilarFunc';
exception when others then
null;
end;
/
begin
execute immediate 'drop type ordsys.ORDIMGIDXMethods';
exception when others then
null;
end;
/
begin
execute immediate 'drop type ordsys.ORDImageIndexStats';
exception when others then
null;
end;
/
Rem This should be at the end of the file
alter session set current_schema="ORDSYS";