<?xml version="1.0"?>
<!--

 Copyright (c) 2006, 2010, Oracle and/or its affiliates. All rights reserved. 

NAME
    kuatcol.xsl
DESCRIPTION
    Convert column diffs in sxml TABLE diff document to ALTER_XML document

MODIFIED        MM/DD/YY
    lbarton     10/30/09 - bug 8796742: translatable error messages
    abodge      11/24/08 - Handle COL_NUM diff
    rapayne     09/15/08 - bug 7393make CUSTOMER_AREA diffs NOT_ALTERABLE.
    lbarton     07/10/08 - bug 5709159: SQL_LIST_ITEM subelements
    lbarton     03/06/08 - xmltype col properties
    lbarton     11/20/06 - remove ENABLE VALIDATE
    lbarton     09/22/05 - Initial version
 -->
<xsl:stylesheet version="1.0" xmlns:sxml="http://xmlns.oracle.com/ku" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <!-- Top level imports -->
 <xsl:import href="kuacomm.xsl"/>
 <xsl:import href="kualob.xsl"/>
 <xsl:import href="kuscnstd.xsl"/>
 <!-- Templates -->
 <xsl:template name="AlterColumns">
  <xsl:param name="ParentNode" select="''"/>
  <!-- *******************************************************************
Template: AlterColumns
Parameters:
 ParentNode - Parent node of SCHEMA, NAME
******************************************************************** -->
  <!-- add columns -->
  <xsl:call-template name="AddColumns">
   <xsl:with-param name="ParentNode" select="$ParentNode"/>
   <xsl:with-param name="ColListNode" select="$ParentNode/sxml:RELATIONAL_TABLE/sxml:COL_LIST"/>
   <xsl:with-param name="ColProperties" select="$ParentNode/sxml:RELATIONAL_TABLE/sxml:TABLE_PROPERTIES/sxml:COLUMN_PROPERTIES"/>
  </xsl:call-template>
  <!-- drop columns -->
  <xsl:if test="$DROP_MISSING_COLUMNS!=0">
   <xsl:call-template name="DropColumns">
    <xsl:with-param name="ParentNode" select="$ParentNode"/>
    <xsl:with-param name="ColListNode" select="$ParentNode/sxml:RELATIONAL_TABLE/sxml:COL_LIST"/>
   </xsl:call-template>
  </xsl:if>
  <!-- modify columns -->
  <xsl:call-template name="ModifyColumns">
   <xsl:with-param name="ParentNode" select="$ParentNode"/>
   <xsl:with-param name="ColListNode" select="$ParentNode/sxml:RELATIONAL_TABLE/sxml:COL_LIST"/>
   <xsl:with-param name="ColProperties" select="$ParentNode/sxml:RELATIONAL_TABLE/sxml:TABLE_PROPERTIES/sxml:COLUMN_PROPERTIES"/>
  </xsl:call-template>
  <!-- column properties -->
  <xsl:choose>
   <xsl:when test="$ParentNode/sxml:RELATIONAL_TABLE">
    <xsl:call-template name="ColProperties">
     <xsl:with-param name="ParentNode" select="$ParentNode"/>
     <xsl:with-param name="ColListNode" select="$ParentNode/sxml:RELATIONAL_TABLE/sxml:COL_LIST"/>
     <xsl:with-param name="ColProperties" select="$ParentNode/sxml:RELATIONAL_TABLE/sxml:TABLE_PROPERTIES/sxml:COLUMN_PROPERTIES"/>
    </xsl:call-template>
   </xsl:when>
   <xsl:when test="$ParentNode/sxml:OBJECT_TABLE">
    <xsl:call-template name="ColProperties">
     <xsl:with-param name="ParentNode" select="$ParentNode"/>
     <xsl:with-param name="ColListNode" select="$ParentNode/sxml:OBJECT_TABLE/sxml:COL_LIST"/>
     <xsl:with-param name="ColProperties" select="$ParentNode/sxml:OBJECT_TABLE/sxml:TABLE_PROPERTIES/sxml:COLUMN_PROPERTIES"/>
    </xsl:call-template>
   </xsl:when>
  </xsl:choose>
 </xsl:template>
 <xsl:template name="AddColumns">
  <xsl:param name="ParentNode" select="''"/>
  <xsl:param name="ColListNode" select="''"/>
  <xsl:param name="ColProperties" select="''"/>
  <!-- *******************************************************************
Template: AddColumns
Parameters:
 ParentNode - Parent node of SCHEMA, NAME
 ColListNode - COL_LIST
 ColProperties - TABLE_PROPERTIES/COLUMN_PROPERTIES
******************************************************************** -->
  <xsl:for-each select="$ColListNode/sxml:COL_LIST_ITEM[@src='2']">
   <xsl:element name="ALTER_LIST_ITEM">
    <xsl:call-template name="AddColumn">
     <xsl:with-param name="ParentNode" select="$ParentNode"/>
     <xsl:with-param name="ColNode" select="."/>
     <xsl:with-param name="ColProperties" select="$ColProperties"/>
     <xsl:with-param name="Action">PARSE</xsl:with-param>
    </xsl:call-template>
    <xsl:call-template name="AddColumn">
     <xsl:with-param name="ParentNode" select="$ParentNode"/>
     <xsl:with-param name="ColNode" select="."/>
     <xsl:with-param name="ColProperties" select="$ColProperties"/>
     <xsl:with-param name="Action">SQL</xsl:with-param>
    </xsl:call-template>
   </xsl:element>
  </xsl:for-each>
 </xsl:template>
 <xsl:template name="AddColumn">
  <xsl:param name="ParentNode" select="''"/>
  <xsl:param name="ColNode" select="''"/>
  <xsl:param name="ColProperties" select="''"/>
  <xsl:param name="Action" select="''"/>
  <!-- *******************************************************************
Template: AddColumn
Parameters:
 ParentNode - Parent node of SCHEMA, NAME
 ColNode - COL_LIST_ITEM
 ColProperties - TABLE_PROPERTIES/COLUMN_PROPERTIES
 Action = "PARSE" or "SQL"
******************************************************************** -->
  <xsl:choose>
   <xsl:when test="$Action='PARSE' and ($PRS_NAME=1 or 
                                        $PRS_CLAUSE_TYPE=1 or
                                        $PRS_COLUMN_ATTRIBUTE=1 or
                                        $PRS_XPATH=1)">
    <xsl:element name="PARSE_LIST">
     <xsl:call-template name="AddXPathParseItem">
      <xsl:with-param name="Node" select="$ColNode"/>
     </xsl:call-template>
     <xsl:call-template name="AddParseItem">
      <xsl:with-param name="ParseIt" select="$PRS_NAME"/>
      <xsl:with-param name="Item">NAME</xsl:with-param>
      <xsl:with-param name="Value1" select="sxml:NAME"/>
     </xsl:call-template>
     <xsl:call-template name="AddParseItem">
      <xsl:with-param name="ParseIt" select="$PRS_CLAUSE_TYPE"/>
      <xsl:with-param name="Item">CLAUSE_TYPE</xsl:with-param>
      <xsl:with-param name="Value1">ADD_COLUMN</xsl:with-param>
     </xsl:call-template>
     <xsl:call-template name="AddParseItem">
      <xsl:with-param name="ParseIt" select="$PRS_COLUMN_ATTRIBUTE"/>
      <xsl:with-param name="Item">COLUMN_ATTRIBUTE</xsl:with-param>
      <xsl:with-param name="Value1">
       <xsl:if test="sxml:NOT_NULL">NOT_NULL</xsl:if>
      </xsl:with-param>
      <xsl:with-param name="Value2">
       <xsl:if test="sxml:DEFAULT">DEFAULT</xsl:if>
      </xsl:with-param>
     </xsl:call-template>
    </xsl:element>
   </xsl:when>
   <xsl:when test="$Action='SQL'">
    <xsl:element name="SQL_LIST">
     <xsl:element name="SQL_LIST_ITEM">
      <xsl:element name="TEXT">
       <xsl:text>ALTER TABLE </xsl:text>
       <xsl:call-template name="SchemaName">
        <xsl:with-param name="ParentNode" select="$ParentNode"/>
       </xsl:call-template>
       <xsl:text> ADD (</xsl:text>
       <xsl:call-template name="SourceName">
        <xsl:with-param name="NameNode" select="$ColNode/sxml:NAME"/>
       </xsl:call-template>
       <xsl:text> </xsl:text>
       <xsl:call-template name="Datatype"/>
       <xsl:apply-templates select="$ColNode/sxml:DEFAULT"/>
       <xsl:apply-templates select="$ColNode/sxml:NOT_NULL"/>
       <xsl:apply-templates select="$ColNode/sxml:ENCRYPT"/>
       <xsl:apply-templates select="$ColNode/sxml:VIRTUAL"/>
       <xsl:text>)</xsl:text>
       <xsl:choose>
        <xsl:when test="$ColNode/sxml:DATATYPE='CLOB' or $ColNode/sxml:DATATYPE='BLOB' or $ColNode/sxml:DATATYPE='NCLOB'">
         <xsl:call-template name="LobColProperties">
          <xsl:with-param name="ColListItem" select="$ColProperties/sxml:COL_LIST/sxml:COL_LIST_ITEM[sxml:NAME=$ColNode/sxml:NAME]"/>
          <xsl:with-param name="DataType" select="$ColNode/sxml:DATATYPE"/>
         </xsl:call-template>
        </xsl:when>
        <xsl:when test="$ColNode/sxml:DATATYPE='UDT'">
         <xsl:call-template name="SubstitutableColProperties">
          <xsl:with-param name="ColListItem" select="$ColProperties/sxml:COL_LIST/sxml:COL_LIST_ITEM[sxml:NAME=$ColNode/sxml:NAME]"/>
         </xsl:call-template>
        </xsl:when>
        <xsl:when test="$ColNode/sxml:DATATYPE='NESTED_TABLE'">
         <xsl:call-template name="NestedTableColProperties">
          <xsl:with-param name="ColNameNode" select="$ColNode/sxml:NAME"/>
          <xsl:with-param name="ColProperties" select="$ColProperties"/>
         </xsl:call-template>
        </xsl:when>
        <xsl:when test="$ColNode/sxml:DATATYPE='VARRAY'">
         <xsl:call-template name="VarrayColProperties">
          <xsl:with-param name="ColNameNode" select="$ColNode/sxml:NAME"/>
          <xsl:with-param name="ColProperties" select="$ColProperties"/>
         </xsl:call-template>
        </xsl:when>
        <xsl:when test="$ColNode/sxml:DATATYPE='XMLTYPE'">
         <xsl:call-template name="XMLTypeColProperties">
          <xsl:with-param name="ColNameNode" select="$ColNode/sxml:NAME"/>
          <xsl:with-param name="ColProperties" select="$ColProperties"/>
         </xsl:call-template>
        </xsl:when>
       </xsl:choose>
      </xsl:element>
     </xsl:element>
    </xsl:element>
   </xsl:when>
  </xsl:choose>
 </xsl:template>
 <xsl:template name="DropColumns">
  <xsl:param name="ParentNode" select="''"/>
  <xsl:param name="ColListNode" select="''"/>
  <!-- *******************************************************************
Template: DropColumns
Parameters:
 ParentNode - Parent node of SCHEMA, NAME
 ColListNode - COL_LIST
******************************************************************** -->
  <xsl:for-each select="$ColListNode/sxml:COL_LIST_ITEM[@src='1']">
   <xsl:element name="ALTER_LIST_ITEM">
    <xsl:call-template name="DropColumn">
     <xsl:with-param name="ParentNode" select="$ParentNode"/>
     <xsl:with-param name="ColNode" select="."/>
     <xsl:with-param name="Action">PARSE</xsl:with-param>
    </xsl:call-template>
    <xsl:call-template name="DropColumn">
     <xsl:with-param name="ParentNode" select="$ParentNode"/>
     <xsl:with-param name="ColNode" select="."/>
     <xsl:with-param name="Action">SQL</xsl:with-param>
    </xsl:call-template>
   </xsl:element>
  </xsl:for-each>
 </xsl:template>
 <xsl:template name="DropColumn">
  <xsl:param name="ParentNode" select="''"/>
  <xsl:param name="ColNode" select="''"/>
  <xsl:param name="Action" select="''"/>
  <!-- *******************************************************************
Template: DropColumn
Parameters:
 ParentNode - Parent node of SCHEMA, NAME
 ColNode - COL_LIST_ITEM
 Action = "PARSE" or "SQL"
******************************************************************** -->
  <xsl:choose>
   <xsl:when test="$Action='PARSE' and ($PRS_NAME=1 or 
                                        $PRS_CLAUSE_TYPE=1 or
                                        $PRS_XPATH=1)">
    <xsl:element name="PARSE_LIST">
     <xsl:call-template name="AddXPathParseItem">
      <xsl:with-param name="Node" select="$ColNode"/>
     </xsl:call-template>
     <xsl:call-template name="AddParseItem">
      <xsl:with-param name="ParseIt" select="$PRS_NAME"/>
      <xsl:with-param name="Item">NAME</xsl:with-param>
      <xsl:with-param name="Value1" select="sxml:NAME"/>
     </xsl:call-template>
     <xsl:call-template name="AddParseItem">
      <xsl:with-param name="ParseIt" select="$PRS_CLAUSE_TYPE"/>
      <xsl:with-param name="Item">CLAUSE_TYPE</xsl:with-param>
      <xsl:with-param name="Value1">DROP_COLUMN</xsl:with-param>
     </xsl:call-template>
    </xsl:element>
   </xsl:when>
   <xsl:when test="$Action='SQL'">
    <xsl:element name="SQL_LIST">
     <xsl:element name="SQL_LIST_ITEM">
      <xsl:element name="TEXT">
       <xsl:text>ALTER TABLE </xsl:text>
       <xsl:call-template name="SchemaName">
        <xsl:with-param name="ParentNode" select="$ParentNode"/>
       </xsl:call-template>
       <xsl:text> DROP (</xsl:text>
       <xsl:call-template name="SourceName">
        <xsl:with-param name="NameNode" select="sxml:NAME"/>
       </xsl:call-template>
       <xsl:text>)</xsl:text>
      </xsl:element>
     </xsl:element>
    </xsl:element>
   </xsl:when>
  </xsl:choose>
 </xsl:template>
 <xsl:template name="ModifyColumns">
  <xsl:param name="ParentNode" select="''"/>
  <xsl:param name="ColListNode" select="''"/>
  <xsl:param name="ColProperties" select="''"/>
  <!-- *******************************************************************
Template: ModifyColumns
Parameters:
 ParentNode - Parent node of SCHEMA, NAME
 ColListNode - COL_LIST
 ColProperties - TABLE_PROPERTIES/COLUMN_PROPERTIES
******************************************************************** -->
  <xsl:for-each select="$ColListNode/sxml:COL_LIST_ITEM">
   <xsl:choose>
    <!-- skip nodes with a src attribute: handled by Add or Drop -->
    <xsl:when test="./@src"/>
    <xsl:otherwise>
     <!-- NOT_ALTERABLE cases -->
     <!-- FLAGS present: this column is used as base column for a virtual column or 
           a functional index expression and is potentially NOT alterable. 
           If there are any col_list_item nodes that require modification
           then simply issue the not_alterable parse item. 
          Note: NOT_NULL diffs are alterable
      -->
     <xsl:if test="(sxml:FLAGS/sxml:FLAGS_ITEM = 'IN_VIRTUAL_COL_EXPRESSION' or
                         sxml:FLAGS/sxml:FLAGS_ITEM = 'IN_FUNCTIONAL_INDEX') and
                (sxml:DATATYPE/@value1 or
                 sxml:LENGTH/@value1 or
                 sxml:CHAR_SEMANTICS/@value1 or
                 sxml:PRECISION/@value1 or sxml:PRECISION/@src or
                 sxml:SCALE/@value1       or  sxml:SCALE/@src or
                 sxml:SORT/@value1         or sxml:SORT/@src or
                 sxml:VIRTUAL/@value1   or sxml:VIRTUAL/@src or
                 sxml:HIDDEN/@value1      or sxml:HIDDEN/@src or
                 sxml:ENCRYPT/@value1  or sxml:ENCRYPT/@src)">
      <xsl:variable name="Subst">
       <xsl:call-template name="QuotedName">
        <xsl:with-param name="NameNode" select="./sxml:NAME"/>
       </xsl:call-template>
      </xsl:variable>
      <xsl:call-template name="CommonNotAlterable">
       <xsl:with-param name="XpathNode" select="./sxml:FLAGS"/>
       <xsl:with-param name="NameNode" select="./sxml:NAME"/>
       <xsl:with-param name="Reason" select="./sxml:FLAGS/sxml:FLAGS_ITEM[position()=last()]"/>
       <xsl:with-param name="Message" select="$MSG_VIRTUAL_COL_EXPRESSION"/>
       <xsl:with-param name="Subst" select="$Subst"/>
      </xsl:call-template>
     </xsl:if>
     <!-- cannot alter a column to drop its default value -->
     <xsl:if test="sxml:DEFAULT/@src='1'">
      <xsl:call-template name="CommonNotAlterable">
       <xsl:with-param name="XpathNode" select="./sxml:DEFAULT"/>
       <xsl:with-param name="NameNode" select="./sxml:NAME"/>
       <xsl:with-param name="Reason">NO_DEFAULT</xsl:with-param>
       <xsl:with-param name="Message" select="$MSG_NO_DEFAULT"/>
      </xsl:call-template>
     </xsl:if>
     <!-- cannot make a virtual column real or vice versa -->
     <xsl:if test="sxml:VIRTUAL[@src]">
      <xsl:call-template name="CommonNotAlterable">
       <xsl:with-param name="XpathNode" select="./sxml:VIRTUAL"/>
       <xsl:with-param name="NameNode" select="./sxml:NAME"/>
       <xsl:with-param name="Reason">VIRTUAL_REAL_CHANGE</xsl:with-param>
       <xsl:with-param name="Message" select="$MSG_VIRTUAL_REAL_CHANGE"/>
      </xsl:call-template>
     </xsl:if>
     <!-- cannot change a long column to anything other than lob -->
     <xsl:if test="sxml:DATATYPE/@value1='LONG' and (sxml:DATATYPE!='CLOB' and sxml:DATATYPE!='NCLOB')">
      <xsl:call-template name="CommonNotAlterable">
       <xsl:with-param name="XpathNode" select="./sxml:DATATYPE"/>
       <xsl:with-param name="NameNode" select="./sxml:NAME"/>
       <xsl:with-param name="Reason">CHANGE_LONG_DATATYPE</xsl:with-param>
       <xsl:with-param name="Message" select="$MSG_CHANGE_LONG_DATATYPE"/>
      </xsl:call-template>
     </xsl:if>
     <!-- cannot change the datatype of a lob column -->
     <xsl:if test="sxml:DATATYPE/@value1='CLOB' or
                     sxml:DATATYPE/@value1='NCLOB' or
                     sxml:DATATYPE/@value1='BLOB'">
      <xsl:call-template name="CommonNotAlterable">
       <xsl:with-param name="XpathNode" select="./sxml:DATATYPE"/>
       <xsl:with-param name="NameNode" select="./sxml:NAME"/>
       <xsl:with-param name="Reason">CHANGE_LOB_DATATYPE</xsl:with-param>
       <xsl:with-param name="Message" select="$MSG_CHANGE_LOB_DATATYPE"/>
      </xsl:call-template>
     </xsl:if>
     <!-- cannot change customer area (no error message needed) -->
     <xsl:if test="sxml:CUSTOMER_AREA/@value1 or sxml:CUSTOMER_AREA/@src">
      <xsl:call-template name="CommonNotAlterable">
       <xsl:with-param name="XpathNode" select="./sxml:CUSTOMER_AREA"/>
       <xsl:with-param name="NameNode" select="./sxml:NAME"/>
       <xsl:with-param name="Reason">CUSTOMER_AREA</xsl:with-param>
      </xsl:call-template>
     </xsl:if>
     <!-- cannot change column position (CM only; no error message needed) -->
     <xsl:if test="sxml:COL_NUM/@value1">
      <xsl:call-template name="CommonNotAlterable">
       <xsl:with-param name="XpathNode" select="./sxml:COL_NUM"/>
       <xsl:with-param name="NameNode" select="./sxml:NAME"/>
       <xsl:with-param name="Reason">COLUMN_POSITION</xsl:with-param>
      </xsl:call-template>
     </xsl:if>
     <!-- modify if necessary and possible -->
     <xsl:if test="(sxml:NOT_NULL/@src or sxml:NOT_NULL/*[@src] or sxml:NOT_NULL/*[@value1]) or
                  ( ( (not(sxml:FLAGS/sxml:FLAGS_ITEM = 'IN_VIRTUAL_COL_EXPRESSION') and
                         not(sxml:FLAGS/sxml:FLAGS_ITEM = 'IN_FUNCTIONAL_INDEX')) and
                 ((sxml:DATATYPE/@value1 or sxml:LENGTH/@value1
                    or sxml:PRECISION/@value1 or sxml:PRECISION/@src
                    or sxml:SCALE/@value1 or sxml:SCALE/@src
                    or sxml:DEFAULT/@value1  or sxml:DEFAULT/@src='2'
                    or sxml:VIRTUAL/@value1) and
                not((sxml:DATATYPE/@value1='LONG' and (sxml:DATATYPE!='CLOB' and sxml:DATATYPE!='NCLOB')) or
                     sxml:DATATYPE/@value1='CLOB' or
                     sxml:DATATYPE/@value1='NCLOB' or
                     sxml:DATATYPE/@value1='BLOB') )))">
      <xsl:element name="ALTER_LIST_ITEM">
       <xsl:call-template name="ModifyColumn">
        <xsl:with-param name="ParentNode" select="$ParentNode"/>
        <xsl:with-param name="ColNode" select="."/>
        <xsl:with-param name="ColProperties" select="$ColProperties"/>
        <xsl:with-param name="Action">PARSE</xsl:with-param>
       </xsl:call-template>
       <xsl:call-template name="ModifyColumn">
        <xsl:with-param name="ParentNode" select="$ParentNode"/>
        <xsl:with-param name="ColNode" select="."/>
        <xsl:with-param name="ColProperties" select="$ColProperties"/>
        <xsl:with-param name="Action">SQL</xsl:with-param>
       </xsl:call-template>
      </xsl:element>
     </xsl:if>
    </xsl:otherwise>
   </xsl:choose>
  </xsl:for-each>
 </xsl:template>
 <xsl:template name="ModifyColumn">
  <xsl:param name="ParentNode" select="''"/>
  <xsl:param name="ColNode" select="''"/>
  <xsl:param name="ColProperties" select="''"/>
  <xsl:param name="Action" select="''"/>
  <!-- *******************************************************************
Template: ModifyColumn
Description:  We are here because we need to alter one or more of the 
    table attributes. There are a couple of conditions which are worth
    explaining:
    - in general if this col is referenced in a virtual col/functional index expr
      then we would NOT be called. However, there is one exception: sql does
      allow NOT_NULL modifications even when this above condition is met.
      Consequently we need to handle diffs which we do not want to issue alter
     stmt for.
     
Parameters:
 ParentNode - Parent node of SCHEMA, NAME
 ColNode - COL_LIST_ITEM
 ColProperties - TABLE_PROPERTIES/COLUMN_PROPERTIES
 Action = "PARSE" or "SQL"
******************************************************************** -->
  <xsl:choose>
   <xsl:when test="$Action='PARSE' and ($PRS_NAME=1 or 
                                        $PRS_CLAUSE_TYPE=1 or
                                        $PRS_COLUMN_ATTRIBUTE=1 or
                                        $PRS_XPATH=1)">
    <xsl:element name="PARSE_LIST">
     <xsl:call-template name="AddXPathParseItem">
      <xsl:with-param name="Node" select="$ColNode"/>
     </xsl:call-template>
     <xsl:call-template name="AddParseItem">
      <xsl:with-param name="ParseIt" select="$PRS_NAME"/>
      <xsl:with-param name="Item">NAME</xsl:with-param>
      <xsl:with-param name="Value1" select="sxml:NAME"/>
     </xsl:call-template>
     <xsl:call-template name="AddParseItem">
      <xsl:with-param name="ParseIt" select="$PRS_CLAUSE_TYPE"/>
      <xsl:with-param name="Item">CLAUSE_TYPE</xsl:with-param>
      <xsl:with-param name="Value1">MODIFY_COLUMN</xsl:with-param>
     </xsl:call-template>
     <xsl:call-template name="AddParseItem">
      <xsl:with-param name="ParseIt" select="$PRS_COLUMN_ATTRIBUTE"/>
      <xsl:with-param name="Item">COLUMN_ATTRIBUTE</xsl:with-param>
      <xsl:with-param name="Value1">
       <xsl:if test="(sxml:DEFAULT[@src] or sxml:DEFAULT[@value1]) and
                           not(sxml:FLAGS)">
        <xsl:choose>
         <xsl:when test="sxml:DEFAULT/@value1">DEFAULT</xsl:when>
         <xsl:when test="sxml:DEFAULT/@src='2'">DEFAULT</xsl:when>
        </xsl:choose>
       </xsl:if>
      </xsl:with-param>
      <xsl:with-param name="Value2">
       <xsl:if test="sxml:NOT_NULL[@src]">
        <xsl:choose>
         <xsl:when test="sxml:NOT_NULL/@src='1'">NULL</xsl:when>
         <xsl:when test="sxml:NOT_NULL/@src='2'">NOT_NULL</xsl:when>
        </xsl:choose>
       </xsl:if>
      </xsl:with-param>
      <xsl:with-param name="Value3">
       <xsl:if test="sxml:LENGTH[@value1]  and not(sxml:FLAGS)">
        <xsl:choose>
         <xsl:when test="sxml:LENGTH/@value1 &gt; sxml:LENGTH">SIZE_DECREASE</xsl:when>
         <xsl:when test="sxml:LENGTH/@value1 &lt; sxml:LENGTH">SIZE_INCREASE</xsl:when>
        </xsl:choose>
       </xsl:if>
      </xsl:with-param>
      <xsl:with-param name="Value4">
       <xsl:if test="sxml:DATATYPE[@value1]  and not(sxml:FLAGS)">DATA_TYPE</xsl:if>
      </xsl:with-param>
      <xsl:with-param name="Value5">
       <xsl:if test="sxml:NAME[@value1]">RENAME_COLUMN</xsl:if>
      </xsl:with-param>
     </xsl:call-template>
    </xsl:element>
   </xsl:when>
   <xsl:when test="$Action='SQL'">
    <xsl:element name="SQL_LIST">
     <xsl:if test="(not(sxml:FLAGS)) and 
            ((sxml:DATATYPE[@value1] or sxml:LENGTH[@value1]
                    or sxml:PRECISION[@value1] or sxml:PRECISION[@src]
                    or sxml:SCALE[@value1] or sxml:SCALE[@src]
                    or sxml:DEFAULT[@value1]  or sxml:DEFAULT[@src]
                    or sxml:VIRTUAL[@value1]) and
                not((sxml:DATATYPE/@value1='LONG' and (sxml:DATATYPE!='CLOB' and sxml:DATATYPE!='NCLOB')) or
                     sxml:DATATYPE/@value1='CLOB' or
                     sxml:DATATYPE/@value1='NCLOB' or
                     sxml:DATATYPE/@value1='BLOB'))">
      <xsl:element name="SQL_LIST_ITEM">
       <xsl:element name="TEXT">
        <xsl:text>ALTER TABLE </xsl:text>
        <xsl:call-template name="SchemaName">
         <xsl:with-param name="ParentNode" select="$ParentNode"/>
        </xsl:call-template>
        <xsl:text> MODIFY (</xsl:text>
        <xsl:call-template name="SourceName">
         <xsl:with-param name="NameNode" select="sxml:NAME"/>
        </xsl:call-template>
        <xsl:text> </xsl:text>
        <xsl:call-template name="Datatype"/>
        <xsl:choose>
         <xsl:when test="sxml:DEFAULT/@src='1'"/>
         <xsl:otherwise>
          <xsl:apply-templates select="sxml:DEFAULT"/>
         </xsl:otherwise>
        </xsl:choose>
        <xsl:if test="sxml:VIRTUAL/@value1">
         <xsl:apply-templates select="sxml:VIRTUAL"/>
        </xsl:if>
        <xsl:apply-templates select="sxml:ENCRYPT"/>
        <xsl:text>)</xsl:text>
        <xsl:if test="$ColNode/sxml:DATATYPE='CLOB' or $ColNode/sxml:DATATYPE='BLOB' or $ColNode/sxml:DATATYPE='NCLOB'">
         <xsl:call-template name="LobColProperties">
          <xsl:with-param name="ColListItem" select="$ColProperties/sxml:COL_LIST/sxml:COL_LIST_ITEM[sxml:NAME=$ColNode/sxml:NAME]"/>
          <xsl:with-param name="DataType" select="$ColNode/sxml:DATATYPE"/>
         </xsl:call-template>
        </xsl:if>
       </xsl:element>
      </xsl:element>
     </xsl:if>
     <!-- NOT NULL constraints: in general, the strategy for constraints is
          to drop the constraint and recreate it with a new definition.
          Two optimizations are possible:
          - If the constraints are identical except for their names,
            we can do ALTER TABLE tab RENAME CONSTRAINT name1 TO name2
          - If the constraints have the same name and definition, and
            only differ in their state (DISABLE, NOVALIDATE, etc.), we can do
            ALTER TABLE tab MODIFY CONSTRAINT name new-state
       -->
     <xsl:if test="sxml:NOT_NULL[@src] or sxml:NOT_NULL/*[@src] or sxml:NOT_NULL/*[@value1]">
      <xsl:choose>
       <xsl:when test="not (sxml:NOT_NULL[@src]) and not(sxml:NOT_NULL/*[@src]) and sxml:NOT_NULL/sxml:NAME[@value1]">
        <!-- rename the constraint -->
        <xsl:element name="SQL_LIST_ITEM">
         <xsl:element name="TEXT">
          <xsl:text>ALTER TABLE </xsl:text>
          <xsl:call-template name="SchemaName">
           <xsl:with-param name="ParentNode" select="$ParentNode"/>
          </xsl:call-template>
          <xsl:text> RENAME CONSTRAINT </xsl:text>
          <xsl:call-template name="SourceName">
           <xsl:with-param name="NameNode" select="sxml:NOT_NULL/sxml:NAME"/>
          </xsl:call-template>
          <xsl:text> TO </xsl:text>
          <xsl:call-template name="QuotedName">
           <xsl:with-param name="NameNode" select="sxml:NOT_NULL/sxml:NAME"/>
          </xsl:call-template>
         </xsl:element>
        </xsl:element>
       </xsl:when>
       <xsl:when test="not (sxml:NOT_NULL[@src]) and sxml:NOT_NULL/sxml:NAME and not(sxml:NOT_NULL/NAME[@src] or sxml:NOT_NULL/sxml:NAME[@value1]) and (sxml:NOT_NULL/sxml:DISABLE[@src] or sxml:NOT_NULL/sxml:NOVALIDATE[@src])">
        <!-- modify the constraint state -->
        <xsl:element name="SQL_LIST_ITEM">
         <xsl:element name="TEXT">
          <xsl:text>ALTER TABLE </xsl:text>
          <xsl:call-template name="SchemaName">
           <xsl:with-param name="ParentNode" select="$ParentNode"/>
          </xsl:call-template>
          <xsl:text> MODIFY CONSTRAINT </xsl:text>
          <xsl:call-template name="QuotedName">
           <xsl:with-param name="NameNode" select="sxml:NOT_NULL/sxml:NAME"/>
          </xsl:call-template>
          <xsl:call-template name="ConstraintState2">
           <xsl:with-param name="ParentNode" select="sxml:NOT_NULL"/>
          </xsl:call-template>
         </xsl:element>
        </xsl:element>
       </xsl:when>
       <xsl:otherwise>
        <!-- drop/recreate the constraint -->
        <!-- drop it if it exists in doc 1 -->
        <xsl:if test="not(sxml:NOT_NULL/@src='2')">
         <xsl:element name="SQL_LIST_ITEM">
          <xsl:element name="TEXT">
           <xsl:text>ALTER TABLE </xsl:text>
           <xsl:call-template name="SchemaName">
            <xsl:with-param name="ParentNode" select="$ParentNode"/>
           </xsl:call-template>
           <xsl:text> MODIFY (</xsl:text>
           <xsl:call-template name="SourceName">
            <xsl:with-param name="NameNode" select="sxml:NAME"/>
           </xsl:call-template>
           <xsl:text> NULL)</xsl:text>
          </xsl:element>
         </xsl:element>
        </xsl:if>
        <!-- add the constraint if it exists in doc 2-->
        <xsl:if test="not(sxml:NOT_NULL/@src='1')">
         <xsl:element name="SQL_LIST_ITEM">
          <xsl:element name="TEXT">
           <xsl:text>ALTER TABLE </xsl:text>
           <xsl:call-template name="SchemaName">
            <xsl:with-param name="ParentNode" select="$ParentNode"/>
           </xsl:call-template>
           <xsl:text> MODIFY (</xsl:text>
           <xsl:call-template name="SourceName">
            <xsl:with-param name="NameNode" select="sxml:NAME"/>
           </xsl:call-template>
           <!-- constraint name, definition and state -->
           <xsl:apply-templates select="sxml:NOT_NULL"/>
           <xsl:text>)</xsl:text>
          </xsl:element>
         </xsl:element>
        </xsl:if>
       </xsl:otherwise>
      </xsl:choose>
     </xsl:if>
     <xsl:if test="sxml:NAME/@value1">
      <xsl:element name="SQL_LIST_ITEM">
       <xsl:element name="TEXT">
        <xsl:text>ALTER TABLE </xsl:text>
        <xsl:call-template name="SchemaName">
         <xsl:with-param name="ParentNode" select="$ParentNode"/>
        </xsl:call-template>
        <xsl:text> RENAME COLUMN </xsl:text>
        <xsl:call-template name="QuotedName">
         <xsl:with-param name="NameNode" select="sxml:NAME/@value1"/>
        </xsl:call-template>
        <xsl:text> TO </xsl:text>
        <xsl:call-template name="QuotedName">
         <xsl:with-param name="NameNode" select="sxml:NAME"/>
        </xsl:call-template>
       </xsl:element>
      </xsl:element>
     </xsl:if>
    </xsl:element>
   </xsl:when>
  </xsl:choose>
 </xsl:template>
 <xsl:template name="ColProperties">
  <xsl:param name="ParentNode" select="''"/>
  <xsl:param name="ColListNode" select="''"/>
  <xsl:param name="ColProperties" select="''"/>
  <!-- *******************************************************************
Template: ColProperties
Parameters:
 ParentNode - Parent node of SCHEMA, NAME
 ColListNode - COL_LIST
 ColProperties - TABLE_PROPERTIES/COLUMN_PROPERTIES
******************************************************************** -->
  <xsl:for-each select="$ColProperties/sxml:COL_LIST/sxml:COL_LIST_ITEM">
   <xsl:variable name="CurName" select="sxml:NAME"/>
   <xsl:choose>
    <!-- Only change properties for existing columns,
        i.e., those without a 'src' attribute in COL_LIST/COL_LIST_ENTRY -->
    <xsl:when test="$ColListNode/sxml:COL_LIST_ITEM[sxml:NAME=$CurName]/@src"/>
    <xsl:otherwise>
     <xsl:choose>
      <xsl:when test="(sxml:DATATYPE='CLOB' or
                       sxml:DATATYPE='BLOB' or
                       sxml:DATATYPE='NCLOB') and
                       sxml:LOB_PROPERTIES//*[@value1]">
       <xsl:element name="ALTER_LIST_ITEM">
        <xsl:call-template name="ModifyLob">
         <xsl:with-param name="TableNode" select="$ParentNode"/>
         <xsl:with-param name="NameNode" select="sxml:NAME"/>
         <xsl:with-param name="ColProperties" select="$ColProperties"/>
         <xsl:with-param name="LobProperties" select="$ColProperties/sxml:COL_LIST/sxml:COL_LIST_ITEM[sxml:NAME=$CurName]/sxml:LOB_PROPERTIES"/>
         <xsl:with-param name="Action">PARSE</xsl:with-param>
        </xsl:call-template>
        <xsl:call-template name="ModifyLob">
         <xsl:with-param name="TableNode" select="$ParentNode"/>
         <xsl:with-param name="NameNode" select="sxml:NAME"/>
         <xsl:with-param name="ColProperties" select="$ColProperties"/>
         <xsl:with-param name="LobProperties" select="$ColProperties/sxml:COL_LIST/sxml:COL_LIST_ITEM[sxml:NAME=$CurName]/sxml:LOB_PROPERTIES"/>
         <xsl:with-param name="Action">SQL</xsl:with-param>
        </xsl:call-template>
       </xsl:element>
      </xsl:when>
      <xsl:when test="sxml:DATATYPE='UDT' and (.//*[@value1] or .//*[@src] or @src='2')">
       <xsl:call-template name="DoModifyUDT">
        <xsl:with-param name="TableNode" select="$ParentNode"/>
        <xsl:with-param name="NameNode" select="sxml:NAME"/>
        <xsl:with-param name="ColProperties" select="$ColProperties"/>
       </xsl:call-template>
      </xsl:when>
      <xsl:when test="sxml:DATATYPE='NESTED_TABLE' and (.//*[@value1] or .//*[@src] or @src='2')">
       <xsl:call-template name="ModifyNestedTable">
        <xsl:with-param name="TableNode" select="$ParentNode"/>
        <xsl:with-param name="NameNode" select="sxml:NAME"/>
        <xsl:with-param name="ColProperties" select="$ColProperties"/>
       </xsl:call-template>
      </xsl:when>
      <xsl:when test="sxml:DATATYPE='XMLTYPE' and (.//*[@value1] or .//*[@src] or @src='2')">
       <xsl:call-template name="ModifyXMLType">
        <xsl:with-param name="TableNode" select="$ParentNode"/>
        <xsl:with-param name="NameNode" select="sxml:NAME"/>
        <xsl:with-param name="ColProperties" select="$ColProperties"/>
       </xsl:call-template>
      </xsl:when>
     </xsl:choose>
    </xsl:otherwise>
   </xsl:choose>
  </xsl:for-each>
 </xsl:template>
</xsl:stylesheet>
