Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\WEB-INF\xsls\kuconstr.xsl
<?xml version="1.0"?> <!-- NAME kuconstr.xsl DESCRIPTION XSLT stylesheet for XML => DDL conversion of muapi_constraint_t ADTs NOTES Do NOT modify this file under any circumstance. If you wish to use this stylesheet with an external XML/XSL parser, first make a copy then reverse the comments on any xsl:import statements appearing below. MODIFIED MM/DD/YY lbarton 11/02/05 - Bug 4715313: reformat files for use with XMLSpy emagrath 02/10/04 - Transportable OID/SETID constraints/indexes emagrath 06/06/03 - Correct index info for constraints emagrath 05/20/03 - Revamp constraint processing htseng 02/11/03 - fix bug 2795373 handle constraint NULL value lbarton 08/02/02 - transportable export htseng 05/29/02 - add supplemental log support. emagrath 01/31/02 - Complete support for REF constraints dgagne 06/28/01 - fix bug 1757298 lbarton 01/22/01 - bugfix: foreign key constr on objtab refs lbarton 01/12/01 - fix comment lbarton 01/10/01 - nested table referential constraints lbarton 10/13/00 - bugfixes: not null/foreign key on adt/ref, etc. gclaborn 11/03/00 - change name lbarton 10/05/00 - more bugfixes lbarton 09/25/00 - bugfix: temporary tables lbarton 06/23/00 - RELY lbarton 06/12/00 - SEGMENT_ATTRIBUTES support lbarton 05/17/00 - Params for new API lbarton 03/17/00 - Multinested collections --> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!-- Parameters: TAB_CONSTRAINT: 1 = this is a table constraint other = this is a column constraint TabProperty: value of TABLE_T/PROPERTY ConstraintNode: the CON node ColListNode: the table column list 'FLAGS' has the following definition in sql.bsq: defer number, /* 0x01 constraint is deferrable */ /* 0x02 constraint is deferred */ /* 0x04 constraint has been system validated */ /* 0x08 constraint name is system generated */ /* 0x10 constraint is BAD, depends on current century */ /* 0x20, optimizer should RELY on this constraint */ --> <xsl:template name="DoConstraint"> <xsl:param name="TAB_CONSTRAINT">0</xsl:param> <xsl:param name="Property">0</xsl:param> <xsl:param name="ConstraintNode" select="''"/> <xsl:param name="ColListNode" select="''"/> <!-- if not system-generated name, emit constraint name --> <xsl:choose> <xsl:when test="CONTYPE='12'"> <xsl:text> SUPPLEMENTAL LOG GROUP "</xsl:text> <xsl:value-of select="NAME"/> <xsl:text>"</xsl:text> </xsl:when> <xsl:otherwise> <xsl:choose> <xsl:when test="(FLAGS mod 16)>=8"/> <xsl:otherwise> <xsl:text> CONSTRAINT "</xsl:text> <xsl:value-of select="NAME"/> <xsl:text>"</xsl:text> </xsl:otherwise> </xsl:choose> </xsl:otherwise> </xsl:choose> <xsl:choose> <xsl:when test="CONTYPE='1'"> <xsl:text> CHECK (</xsl:text> <xsl:value-of select="CONDITION"/> <xsl:text>)</xsl:text> <xsl:call-template name="DoFlags"/> <xsl:apply-templates select="ENABLED"/> </xsl:when> <xsl:when test="CONTYPE='12'"> <xsl:text> (</xsl:text> <xsl:call-template name="DoConstraintColList"> <xsl:with-param name="ConstraintColList" select="COL_LIST"/> </xsl:call-template> <xsl:text>)</xsl:text> <xsl:call-template name="DoFlags"/> </xsl:when> <xsl:when test="CONTYPE='2'"> <xsl:text> PRIMARY KEY</xsl:text> <xsl:call-template name="DoPrimaryUnique"> <xsl:with-param name="Property" select="$Property"/> </xsl:call-template> </xsl:when> <xsl:when test="CONTYPE='3'"> <xsl:text> UNIQUE</xsl:text> <xsl:call-template name="DoPrimaryUnique"> <xsl:with-param name="Property" select="$Property"/> </xsl:call-template> </xsl:when> <xsl:when test="CONTYPE='4'"> <!-- Referential constraints --> <xsl:if test="$TAB_CONSTRAINT=1"> <xsl:text> FOREIGN KEY (</xsl:text> <xsl:choose> <!-- A referential constraint on a REF whose referenced object table has an OID based on a PRIMARY KEY may specify multiple source columns (one for each 'exploded' col/attr of the potentially compound Primary key). The source col/attr names were 'corrected' to specify the actual REF item when the metadata was collected, however only 1 source column must be used in the constraint. Example: create type type1 as object (a1 number, a2 number); create table table1 of type1 (primary key(a1, a2)) object identifier primary key; create table table2 (c1 ref type1, foreign key (c1) references table1); Foreign key source columns generated: c1.a1, c1.a2 each having column property 0x00200000 (2097152) attribute column of a user-defined ref. Corrected columns names stored, respectively: c1, c1 --> <xsl:when test="(SRC_COL_LIST/SRC_COL_LIST_ITEM/COL/PROPERTY mod 4194304)>=2097152"> <xsl:call-template name="ColNameOrAttr"> <xsl:with-param name="ColItem" select="SRC_COL_LIST/SRC_COL_LIST_ITEM/COL"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="DoConstraintColList"> <xsl:with-param name="ConstraintColList" select="SRC_COL_LIST"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> <xsl:text>)</xsl:text> </xsl:if> <!-- only for Table constraints --> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <xsl:text> REFERENCES </xsl:text> <xsl:apply-templates select="SCHEMA_OBJ"/> <!-- A referential constraint on a REF whose referenced object table has an OID based on a PRIMARY KEY may specify multiple target columns (one for each col/attr of the potentially compound Primary key - see above example). Do not emit the target col/attr list if the ref attribute property exists for the [first] source col (unneeded since it references the Primary key). --> <xsl:if test="(SRC_COL_LIST/SRC_COL_LIST_ITEM/COL/PROPERTY mod 4194304)<2097152"> <xsl:text>(</xsl:text> <xsl:call-template name="DoConstraintColList"> <xsl:with-param name="ConstraintColList" select="TGT_COL_LIST"/> </xsl:call-template> <xsl:text>)</xsl:text> </xsl:if> <xsl:apply-templates select="REFACT"/> <xsl:call-template name="DoFlags"/> <xsl:apply-templates select="ENABLED"/> </xsl:when> <!-- Referential constraints --> <xsl:when test="CONTYPE='7' or CONTYPE='11'"> <xsl:text> NOT NULL</xsl:text> <xsl:call-template name="DoFlags"/> <xsl:apply-templates select="ENABLED"/> </xsl:when> <xsl:when test="CONTYPE='14'"> <xsl:text> SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS</xsl:text> </xsl:when> <xsl:when test="CONTYPE='15'"> <xsl:text> SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS</xsl:text> </xsl:when> <xsl:when test="CONTYPE='16'"> <xsl:text> SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS</xsl:text> </xsl:when> <xsl:when test="CONTYPE='17'"> <xsl:text> SUPPLEMENTAL LOG DATA (ALL) COLUMNS</xsl:text> </xsl:when> </xsl:choose> </xsl:template> <xsl:template name="DoFlags"> <xsl:if test="(FLAGS mod 2)=1"> <xsl:text> DEFERRABLE</xsl:text> </xsl:if> <xsl:if test="(FLAGS mod 4)>=2"> <xsl:text> INITIALLY DEFERRED</xsl:text> </xsl:if> <xsl:if test="(FLAGS mod 64)>=32"> <xsl:text> RELY</xsl:text> </xsl:if> <xsl:if test="(FLAGS mod 128)>=64"> <xsl:text> ALWAYS</xsl:text> </xsl:if> </xsl:template> <!-- Common processing for UNIQUE and PRIMARY KEY constraints --> <xsl:template name="DoPrimaryUnique"> <xsl:param name="Property">0</xsl:param> <xsl:text> (</xsl:text> <xsl:call-template name="DoConstraintColList"> <xsl:with-param name="ConstraintColList" select="COL_LIST"/> </xsl:call-template> <xsl:text>)</xsl:text> <xsl:call-template name="DoFlags"/> <!-- Idx, for other than temp idx w/ sys gend name or IOT Primary Key --> <xsl:if test="IND and not (((IND/BASE_OBJ/FLAGS mod 4)>=2 and starts-with(IND/SCHEMA_OBJ/NAME,'SYS_C')) or (($Property mod 128)>=64 and CONTYPE='2'))"> <xsl:choose> <!-- Non sys gend name and (pkoid or unnamed constr idx marked sys gend) --> <xsl:when test="not(starts-with(IND/SCHEMA_OBJ/NAME,'SYS_C')) and ((($Property mod 8192)>=4096 and CONTYPE='2') or (starts-with(NAME,'SYS_C') and (IND/SCHEMA_OBJ/FLAGS mod 8)>=4))"> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!-- Add full CREATE INDEX clause --> <xsl:text> USING INDEX (</xsl:text> <xsl:apply-templates select="IND"> <xsl:with-param name="ConstraintIndex">1</xsl:with-param> </xsl:apply-templates> <xsl:text>) </xsl:text> </xsl:when> <xsl:otherwise> <!-- Segment attributes requested and not a temp idx --> <xsl:if test="$SEGMENT_ATTRIBUTES=1 and 2>(IND/BASE_OBJ/FLAGS mod 4)"> <xsl:if test="$PRETTY=1"> <xsl:text>
 </xsl:text> </xsl:if> <!-- Add index attributes if being emitted --> <xsl:text> USING INDEX </xsl:text> <xsl:apply-templates select="IND"> <xsl:with-param name="ConstraintIndex">2</xsl:with-param> </xsl:apply-templates> </xsl:if> </xsl:otherwise> </xsl:choose> </xsl:if> <xsl:apply-templates select="ENABLED"/> </xsl:template> <!-- (Sigh) Doing our own col list processing --> <xsl:template name="DoConstraintColList"> <xsl:param name="ConstraintColList" select="''"/> <xsl:for-each select="$ConstraintColList/COL_LIST_ITEM | $ConstraintColList/SRC_COL_LIST_ITEM | $ConstraintColList/TGT_COL_LIST_ITEM"> <xsl:call-template name="ColNameOrAttr"> <xsl:with-param name="ColItem" select="COL"/> </xsl:call-template> <!-- Add Any Supplemental Log Group NO LOG clause --> <xsl:if test="(SPARE1 mod 2)=1"> NO LOG</xsl:if> <!-- Put out a ',' when not the last column --> <xsl:if test="not(position()=last())"> <xsl:text>, </xsl:text> </xsl:if> </xsl:for-each> </xsl:template> <xsl:template match="REFACT"> <xsl:text> ON DELETE </xsl:text> <xsl:choose> <xsl:when test=".='1'">CASCADE</xsl:when> <xsl:when test=".='2'">SET NULL</xsl:when> <xsl:otherwise>SET DEFAULT</xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template match="ENABLED"> <xsl:choose> <!-- process [en/dis]able syntax --> <xsl:when test=".='0'"> <xsl:text> DISABLE</xsl:text> <!-- novalidate is default for disable, so check for validate only --> <xsl:if test="(../FLAGS mod 8) >= 4"> <xsl:text> VALIDATE</xsl:text> </xsl:if> </xsl:when> <xsl:otherwise> <xsl:text> ENABLE</xsl:text> <!-- validate is default for enable, so check for novalidate only --> <xsl:choose> <xsl:when test="../FLAGS"> <xsl:if test="(../FLAGS mod 8) < 4"> <xsl:text> NOVALIDATE</xsl:text> </xsl:if> </xsl:when> <xsl:otherwise> <!-- this is NULL value --> <xsl:text> NOVALIDATE</xsl:text> </xsl:otherwise> </xsl:choose> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- HIDDEN_ID (OID or SETID) constraints/indexes. Only arrive here for TRANSPORTABLE=1 when some TABLE_T/CON1_LIST/$ConstraintNode[OID_OR_SETID] != 0. CREATE of an object table, and/or a table with nested columns will cause RDBMS to generate a unique constraint on the OID and/or each nested table's SETID respectively, which when enabled at the end of the RDBMS Create Table procedure, will create a UNIQUE index for that constraint. The constraint and index have system generated names, and the index will be flagged as having been created by a constraint. For an object table, any OIDINDEX clause information in the Create will be associated with the OID constraint and used during the implicit creation of the Unique OID index at the end of Create Table. For all Export modes, the OID and SETID Constraint (CONSTRAINT_T) and Index (INDEX_T) Objects are filtered out. The only place the HIDDEN_ID constraint info appears is in the table (TABLE_T) object. Currently, for all Import modes other than Transportable, these Table object constraints are ignored, as the constraints and associated indexes will be implictly re-created on Import (with any OIDINDEX clause information used for the OID index). For transportable mode, RDBMS creates but generally does not enable these constraints during CREATE TABLE (see bug 2347596 which now causes the OID constraint to be enabled). This leaves disabled constraints as well as the UNIQUE index segments without corresponding index metadata. OIDINDEX clause information associated with the disabled OID constraint will never be used (see above for bug 2347596). For transportable mode, Import must generate and execute DDL to determine the OID/SETID constraint name in the target metadata, and enable those constraints. The DDL is generated from constraint and index information in the [nested] Table Objects, providing explicit index storage info via the USING INDEX clause to reuse the existing index segments (any any OIDINDEX infor is then ignored). The DDL is executed after the Create Table for each object table and each table with nested table columns. Note that re-enabling an enabled constraint does not present any problems. Unfortunately RDBMS does Not guarantee that the constraint is enabled or exists, that the index exists, that both have the same name, that the index is marked as having been created by a constraint, that either have not been redefined as named objects, that the index is unique, or that the constraint has been enabled/disabled with the 'current' defaults (such as [NO]VALIDATE), leading to the following: Not supported: Disabled constraints, with or without a retained index (can leave 'detached' index segments with no index metadata). Dropped constraints, with or without a retained index (same problem with 'detached' index segments). Non Unique indexes (manually created or via DEFERRABLE constraints). Named constraints and/or named indexes (non system gen'd names). Clauses - NOVALIDATE (Unique index generation is only guaranteed to occur with ENABLE VALIDATE), DROP/KEEP INDEX, DEFERRABLE, INITIALLY, RELY, EXCEPTIONS. --> <!-- Parameters: SchemaObjParent: TABLE_T node (for SCHEMA_OBJ child) ConstraintNode: CON1 child node with OID_OR_SETID != 0 --> <xsl:template name="DoHiddenConstraint"> <xsl:param name="SchemaObjParent" select="''"/> <xsl:param name="ConstraintNode" select="''"/> <xsl:call-template name="DoParse"> <xsl:with-param name="Verb">ALTER</xsl:with-param> <xsl:with-param name="ObjectType" select="$SchemaObjParent/SCHEMA_OBJ/TYPE_NAME"/> <xsl:with-param name="NameNode" select="$SchemaObjParent/SCHEMA_OBJ/NAME"/> </xsl:call-template> <xsl:text>DECLARE</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> CON_NAME VARCHAR2(30);</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>BEGIN</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <!-- Get target constr. name for owner/table/int. col. num of OID/SETID --> <xsl:text> EXECUTE IMMEDIATE</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> 'SELECT CH.CONSTR_NAME </xsl:text> <xsl:text>FROM SYS.KU$_FIND_HIDDEN_CONS_VIEW CH ' ||</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> 'WHERE CH.OWNER_NAME = :1 AND </xsl:text> <xsl:text>CH.TABLE_NAME = :2 AND ' ||</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> 'CH.INTCOL_NUM = :3'</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> INTO CON_NAME</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> USING '</xsl:text> <xsl:value-of select="$SchemaObjParent/SCHEMA_OBJ/OWNER_NAME"/> <xsl:text>', '</xsl:text> <xsl:value-of select="$SchemaObjParent/SCHEMA_OBJ/NAME"/> <xsl:text>', </xsl:text> <xsl:value-of select="$ConstraintNode/COL_LIST/COL_LIST_ITEM/INTCOL_NUM"/> <xsl:text>;</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> EXECUTE IMMEDIATE</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> 'ALTER </xsl:text> <xsl:value-of select="$SchemaObjParent/SCHEMA_OBJ/TYPE_NAME"/> <xsl:text> </xsl:text> <xsl:apply-templates select="$SchemaObjParent/SCHEMA_OBJ"/> <xsl:text>' ||</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> 'ENABLE </xsl:text> <xsl:text>CONSTRAINT "' || CON_NAME || '" ' ||</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text> 'USING INDEX </xsl:text> <xsl:apply-templates select="$ConstraintNode/IND"> <xsl:with-param name="ConstraintIndex">2</xsl:with-param> </xsl:apply-templates> <xsl:text>';</xsl:text> <xsl:if test="$PRETTY=1"> <xsl:text>
</xsl:text> </xsl:if> <xsl:text>END;</xsl:text> <xsl:call-template name="DoTerminator"> <xsl:with-param name="Text"/> </xsl:call-template> <xsl:text>
</xsl:text> </xsl:template> </xsl:stylesheet>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de