<?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>&#xa;	 </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)&lt;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>&#xa; </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>&#xa; </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) &lt; 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>&#xa;</xsl:text>
  </xsl:if>
  <xsl:text> CON_NAME VARCHAR2(30);</xsl:text>
  <xsl:if test="$PRETTY=1">
   <xsl:text>&#xa;</xsl:text>
  </xsl:if>
  <xsl:text>BEGIN</xsl:text>
  <xsl:if test="$PRETTY=1">
   <xsl:text>&#xa;</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>&#xa;</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>&#xa;</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>&#xa;</xsl:text>
  </xsl:if>
  <xsl:text>        'CH.INTCOL_NUM = :3'</xsl:text>
  <xsl:if test="$PRETTY=1">
   <xsl:text>&#xa;</xsl:text>
  </xsl:if>
  <xsl:text>  INTO CON_NAME</xsl:text>
  <xsl:if test="$PRETTY=1">
   <xsl:text>&#xa;</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>&#xa;</xsl:text>
  </xsl:if>
  <xsl:text> EXECUTE IMMEDIATE</xsl:text>
  <xsl:if test="$PRETTY=1">
   <xsl:text>&#xa;</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>&#xa;</xsl:text>
  </xsl:if>
  <xsl:text>  'ENABLE </xsl:text>
  <xsl:text>CONSTRAINT "' || CON_NAME || '" ' ||</xsl:text>
  <xsl:if test="$PRETTY=1">
   <xsl:text>&#xa;</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>&#xa;</xsl:text>
  </xsl:if>
  <xsl:text>END;</xsl:text>
  <xsl:call-template name="DoTerminator">
   <xsl:with-param name="Text"/>
  </xsl:call-template>
  <xsl:text>&#xa;</xsl:text>
 </xsl:template>
</xsl:stylesheet>
