Actualize Oracle SQL generator and Oracle datatypes

2 files changed, 280 insertions, 94 deletions

diff --git a/db/oracle/datatypes.xml b/db/oracle/datatypes.xml
index 45c803e..732d9eb 100644
--- a/db/oracle/datatypes.xml
+++ b/db/oracle/datatypes.xml
@@ -1,30 +1,29 @@
 <?xml version="1.0"?>
 <datatypes db="oracle">
 	<group label="Number" color="rgb(238,238,170)">
-		<type label="NUMERIC" length="0" sql="DECIMAL" re="INT" quote="" />
-		<type label="NUMBER" length="0" sql="DECIMAL" re="INT" quote="" />
-		<type label="Decimal" length="1" sql="DECIMAL" re="DEC" quote="" />
-		<type label="Single precision" length="0" sql="FLOAT" quote="" />
-		<type label="Double precision" length="0" sql="DOUBLE" re="DOUBLE" quote="" />
+		<type label="INTEGER" length="0" sql="INTEGER" re="INT" quote="" />
+		<type label="NUMBER" length="1" sql="NUMBER" re="INT" quote="" />
 	</group>
 
 	<group label="Character" color="rgb(255,200,200)">
-		<type label="Char" length="1" sql="CHAR" quote="'"/>
+		<type label="CHAR" length="1" sql="CHAR" quote="'"/>
+		<type label="VARCHAR2" length="1" sql="VARCHAR2"  quote="'"/>
+		<type label="CLOB" length="0" sql="CLOB" re="TEXT" quote="'"/>
 		<type label="NCHAR" length="1" sql="NCHAR" quote="'"/>
-		<type label="Varchar2" length="1" sql="VARCHAR2"  quote="'"/>
-		<type label="CLOB" length="0" sql="MEDIUMTEXT" re="TEXT" quote="'"/>
-		<type label="Binary" length="1" sql="BINARY" quote="'"/>
-		<type label="Varbinary" length="1" sql="VARBINARY" quote="'"/>
-		<type label="BLOB" length="0" sql="BLOB" re="BLOB" quote="'"/>
+		<type label="NVARCHAR2" length="1" sql="NVARCHAR2"  quote="'"/>
+		<type label="NCLOB" length="0" sql="NCLOB" re="TEXT" quote="'"/>
 	</group>
 
 	<group label="Date &amp; Time" color="rgb(200,255,200)">
-		<type label="Date" length="0" sql="DATE" quote="'" />
-		<type label="Timestamp" length="0" sql="TIMESTAMP" quote="'" />
+		<type label="DATE" length="0" sql="DATE" quote="" />
+		<type label="TIMESTAMP" length="1" sql="TIMESTAMP" quote="" />
 	</group>
 	
-	<group label="Miscellaneous" color="rgb(200,200,255)">
-		<type label="ENUM" length="1" sql="ENUM" quote="" />
-		<type label="SET" length="1" sql="SET" quote="" />
+	<group label="Other" color="rgb(200,200,255)">
+		<type label="RAW" length="1" sql="RAW" quote="'"/>
+		<type label="BLOB" length="0" sql="BLOB" re="BLOB" quote="'"/>
+		<type label="DECIMAL" length="1" sql="DECIMAL" re="DEC" quote="" />
+		<type label="FLOAT" length="1" sql="FLOAT" quote="" />
+		<type label="DOUBLE" length="0" sql="DOUBLE PRECISION" quote="" />
 	</group>
 </datatypes>
diff --git a/db/oracle/output.xsl b/db/oracle/output.xsl
index 0217256..308f7cc 100644
--- a/db/oracle/output.xsl
+++ b/db/oracle/output.xsl
@@ -1,85 +1,200 @@
 <?xml version="1.0" ?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
+
+<!-- Define which symbol to use for quoting identifiers, may be empty -->
+<xsl:variable name="quote">"</xsl:variable>
+
+<!-- Predefine usefull global variables -->
+<xsl:variable name="apos" >'</xsl:variable>
+<xsl:variable name="crlf" ><xsl:text>
+</xsl:text></xsl:variable>
+<!--                                       |"MAXIMUM_ORACLE_COLUMN_NAME_LEN"| -->
+<xsl:variable name="padding_name"><xsl:text>                                </xsl:text></xsl:variable>
+<!--                                       |VARCHAR2(4000 CHAR) |             -->
+<xsl:variable name="padding_type"><xsl:text>                    </xsl:text></xsl:variable>
+<xsl:variable name="smallcase" select="'abcdefghijklmnopqrstuvwxyz'" />
+<xsl:variable name="uppercase" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'" />
+
 <xsl:output method="text"/>
 
+<!-- Quotes oracle identifier, if required (if it contains non-uppercase letters)-->
+<xsl:template name="ora_ident">
+	<xsl:param name="ident"/>
+	<xsl:choose>
+		<xsl:when test="translate( $ident, $smallcase, $uppercase ) = $ident">
+			<xsl:value-of select="$ident" />
+		</xsl:when>
+		<xsl:otherwise>
+			<xsl:value-of select="concat( $quote, $ident, $quote )"/>
+		</xsl:otherwise>
+	</xsl:choose>
+</xsl:template>
+
+<!-- Constructs FK constraint name from 2 table names, fitted into 30 symbols per identifier limitation -->
+<xsl:template name="ora_fk_constraint_name">
+	<xsl:param name="tbl_fr"/>
+	<xsl:param name="tbl_to"/>
+	<xsl:call-template name="ora_ident">
+		<xsl:with-param name="ident" select="concat( 'FK_',  substring( $tbl_fr, 1 + string-length( $tbl_fr ) - ( 30 - 4 ) div 2 )
+		                                           , '_',    substring( $tbl_to, 1 + string-length( $tbl_to ) - ( 30 - 4 ) div 2 ) )" />
+	</xsl:call-template>
+</xsl:template>
+
 <xsl:template name="replace-substring">
-      <xsl:param name="value" />
-      <xsl:param name="from" />
-      <xsl:param name="to" />
-      <xsl:choose>
-         <xsl:when test="contains($value,$from)">
-            <xsl:value-of select="substring-before($value,$from)" />
-            <xsl:value-of select="$to" />
-            <xsl:call-template name="replace-substring">
-               <xsl:with-param name="value" select="substring-after($value,$from)" />
-               <xsl:with-param name="from" select="$from" />
-               <xsl:with-param name="to" select="$to" />
-            </xsl:call-template>
-         </xsl:when>
-         <xsl:otherwise>
-            <xsl:value-of select="$value" />
-         </xsl:otherwise>
-      </xsl:choose>
+	<xsl:param name="value" />
+	<xsl:param name="from" />
+	<xsl:param name="to" />
+	<xsl:choose>
+		<xsl:when test="contains($value,$from)">
+			<xsl:value-of select="substring-before($value,$from)" />
+			<xsl:value-of select="$to" />
+			<xsl:call-template name="replace-substring">
+				<xsl:with-param name="value" select="substring-after($value,$from)" />
+				<xsl:with-param name="from" select="$from" />
+				<xsl:with-param name="to" select="$to" />
+			</xsl:call-template>
+		</xsl:when>
+		<xsl:otherwise>
+			<xsl:value-of select="$value" />
+		</xsl:otherwise>
+	</xsl:choose>
 </xsl:template>
 
+
 <xsl:template match="/sql">
 
+<!-- Generate commented DROPs for same objects that later would be created. This is usefull when re-creating DB schema. -->
+	<xsl:if test="0 &lt; count( table )">
+		<xsl:value-of select="concat( $crlf, '/*' )"/>
+		<xsl:for-each select="table">
+			<xsl:variable name="tbl_fr" select="@name" />
+			<xsl:for-each select="row">
+				<xsl:for-each select="relation">
+					<xsl:variable name="tbl_to" select="@table" />
+					<xsl:value-of select="concat( $crlf, 'ALTER TABLE ', ' DROP CONSTRAINT ' )"/>
+					<xsl:call-template name="ora_ident">
+						<xsl:with-param name="ident" select="$tbl_fr" />
+					</xsl:call-template>
+					<xsl:value-of select="concat( ' DROP CONSTRAINT ', '' )"/>
+					<xsl:call-template name="ora_fk_constraint_name">
+						<xsl:with-param name="tbl_fr" select="$tbl_fr" />
+						<xsl:with-param name="tbl_to" select="$tbl_to" />
+					</xsl:call-template>
+					<xsl:value-of select="concat( ';', '' )"/>
+				</xsl:for-each>
+			</xsl:for-each>
+		</xsl:for-each>
+
+		<xsl:for-each select="table">
+			<xsl:variable name="table" select="@name" />
+			<xsl:value-of select="concat( $crlf, 'DROP TABLE ' )" />
+			<xsl:call-template name="ora_ident">
+				<xsl:with-param name="ident" select="$table" />
+			</xsl:call-template>
+			<xsl:value-of select="concat( ' PURGE;', '' )" />
+			<xsl:if test="0 &lt; count( row[@autoincrement = 1] )">
+				<xsl:for-each select="row[@autoincrement = 1]">
+					<xsl:value-of select="concat( $crlf, 'DROP SEQUENCE ' )"/>
+					<xsl:call-template name="ora_ident">
+						<xsl:with-param name="ident" select="concat( 'SQ_', $table )" />
+					</xsl:call-template>
+					<xsl:value-of select="concat( ';', '' )"/>
+				</xsl:for-each>
+			</xsl:if>
+		</xsl:for-each>
+
+		<xsl:value-of select="concat( $crlf, '-- */' )"/>
+	</xsl:if>
+
 <!-- tables -->
 	<xsl:for-each select="table">
 		<xsl:variable name="table" select="@name" />
-		<xsl:text>CREATE TABLE `</xsl:text>
-		<xsl:value-of select="@name" />
-		<xsl:text>` (
-</xsl:text>
+		<xsl:value-of select="concat( $crlf, '' )" />
+		<xsl:value-of select="concat( $crlf, '-------------------------------------------------------------------------------' )" />
+		<xsl:value-of select="concat( $crlf, '--            ', $table )" />
+		<xsl:value-of select="concat( $crlf, '-------------------------------------------------------------------------------' )" />
+		<xsl:value-of select="concat( $crlf, '' )" />
+		<xsl:value-of select="concat( $crlf, 'CREATE TABLE ' )" />
+		<xsl:call-template name="ora_ident">
+			<xsl:with-param name="ident" select="$table" />
+		</xsl:call-template>
+		<xsl:value-of select="concat( ' (', '' )" />
 		<xsl:for-each select="row">
-			<xsl:text>`</xsl:text>
-			<xsl:value-of select="@name" />
-			<xsl:text>` </xsl:text>
+			<xsl:if test="position()=1">
+				<xsl:value-of select="concat( $crlf, '    ' )" />
+			</xsl:if>
+			<xsl:if test="not (position()=1)">
+				<xsl:value-of select="concat( $crlf, '  , ' )" />
+			</xsl:if>
+			<xsl:call-template name="ora_ident">
+				<xsl:with-param name="ident" select="@name" />
+			</xsl:call-template>
 
-			<xsl:value-of select="datatype" />
-			<xsl:text> </xsl:text>
+			<xsl:value-of select="concat( substring( $padding_name, 1, string-length( $padding_name ) - string-length( @name ) )
+			                            , datatype )" />
 			
+			<xsl:if test="default and not ( default = 'NULL' )">
+				<xsl:value-of select="concat( substring( $padding_type, 1, string-length( $padding_type ) - string-length( datatype ) )
+				                            , 'DEFAULT ', default )" />
+			</xsl:if>
+
 			<xsl:if test="@null = 0">
-				<xsl:text>NOT NULL </xsl:text>
-			</xsl:if> 
-
-			<!-- needs to be fixed using sequences
-			<xsl:if test="@autoincrement = 1">
-				<xsl:text>AUTO_INCREMENT </xsl:text>
-			</xsl:if> 
-			-->
-
-			<xsl:if test="default">
-				<xsl:text>DEFAULT </xsl:text>
-				<xsl:value-of select="default" />
-				<xsl:text> </xsl:text>
+				<xsl:value-of select="concat( substring( $padding_type, 1, string-length( $padding_type ) - string-length( datatype ) )
+				                            , 'NOT NULL' )" />
 			</xsl:if>
 
-			<xsl:if test="not (position()=last())">
-				<xsl:text>,
-</xsl:text>
-			</xsl:if> 
 		</xsl:for-each>
 		
 <!-- keys -->
 		<xsl:for-each select="key">
-			<xsl:text>,
-</xsl:text>
+			<xsl:value-of select="concat( $crlf, '  , CONSTRAINT ' )" />
+			<xsl:if test="@name">
+				<!-- if KEY constraint name was specified - use it -->
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="@name" />
+				</xsl:call-template>
+			</xsl:if>
 			<xsl:choose>
-				<xsl:when test="@type = 'PRIMARY'">PRIMARY KEY (</xsl:when>
-				<xsl:when test="@type = 'FULLTEXT'">FULLTEXT KEY (</xsl:when>
-				<xsl:when test="@type = 'UNIQUE'">UNIQUE KEY (</xsl:when>
-				<xsl:otherwise>KEY (</xsl:otherwise>
+				<xsl:when test="@type = 'PRIMARY'" >
+					<xsl:if test="@name = ''">
+						<!-- if PK KEY constraint name was NOT specified - invent it -->
+						<xsl:call-template name="ora_ident">
+							<xsl:with-param name="ident" select="concat( 'PK_', ../@name )" />
+						</xsl:call-template>
+					</xsl:if>
+					<xsl:value-of select="concat( ' PRIMARY KEY', '' )" />
+				</xsl:when>
+				<xsl:when test="@type = 'UNIQUE'" >
+					<xsl:if test="@name = ''">
+						<!-- if QU KEY constraint name was NOT specified - invent it -->
+						<xsl:call-template name="ora_ident">
+							<xsl:with-param name="ident" select="concat( 'UQ_', position() )" />
+						</xsl:call-template>
+					</xsl:if>
+					<xsl:value-of select="concat( ' UNIQUE', '' )" />
+				</xsl:when>
+				<xsl:otherwise>
+						<!-- if other? KEY constraint name was NOT specified - invent it -->
+					<xsl:if test="@name = ''">
+						<xsl:call-template name="ora_ident">
+							<xsl:with-param name="ident" select="concat( 'KK_', position() )" />
+						</xsl:call-template>
+					</xsl:if>
+					<xsl:value-of select="concat( ' ??',  @type, '??' )" />
+				</xsl:otherwise>
 			</xsl:choose>
-			
+
+			<!-- comma -separated list of columns -->
+			<xsl:text> ( </xsl:text>
 			<xsl:for-each select="part">
-				<xsl:text>`</xsl:text><xsl:value-of select="." /><xsl:text>`</xsl:text>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="." />
+				</xsl:call-template>
 				<xsl:if test="not (position() = last())">
 					<xsl:text>, </xsl:text>
 				</xsl:if>
 			</xsl:for-each>
-			<xsl:text>)</xsl:text>
-			
+			<xsl:text> )</xsl:text>
 		</xsl:for-each>
 		
 		<xsl:text>
@@ -87,54 +202,126 @@
 </xsl:text>
 
 		<xsl:if test="comment">
-			<xsl:text>COMMENT ON TABLE `</xsl:text>
-			<xsl:value-of select="@name" />
-			<xsl:text>` IS '</xsl:text>
+			<xsl:value-of select="concat( $crlf, 'COMMENT ON TABLE  ' )"/>
+			<xsl:call-template name="ora_ident">
+				<xsl:with-param name="ident" select="@name" />
+			</xsl:call-template>
+			<xsl:value-of select="concat( $padding_name, ' IS ', $apos )"/>
 			<xsl:call-template name="replace-substring">
 				<xsl:with-param name="value" select="comment" />
 				<xsl:with-param name="from" select='"&apos;"' />
 				<xsl:with-param name="to" select='"&apos;&apos;"' />
 			</xsl:call-template>
-			<xsl:text>';
-</xsl:text>
+			<xsl:value-of select="concat( $apos, ';' )"/>
 		</xsl:if>
 		
 		<xsl:for-each select="row">
 			<xsl:if test="comment">
-				<xsl:text>COMMENT ON COLUMN `</xsl:text>
-				<xsl:value-of select="$table" />
-				<xsl:text>`.`</xsl:text>
-				<xsl:value-of select="@name" />
-				<xsl:text>` IS '</xsl:text>
+				<xsl:value-of select="concat( $crlf, 'COMMENT ON COLUMN ' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="$table" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( '.', '' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="@name" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( substring( $padding_name, 1, string-length( $padding_name ) - string-length( @name ) )
+				                            , 'IS ', $apos )"/>
 				<xsl:call-template name="replace-substring">
 					<xsl:with-param name="value" select="comment" />
 					<xsl:with-param name="from" select='"&apos;"' />
 					<xsl:with-param name="to" select='"&apos;&apos;"' />
 				</xsl:call-template>
-				<xsl:text>';
-</xsl:text>
+				<xsl:value-of select="concat( $apos, ';' )"/>
 			</xsl:if>
 		</xsl:for-each>
 		
+		<xsl:if test="0 &lt; count( row[@autoincrement = 1] )">
+			<xsl:value-of select="concat( $crlf, '' )"/>
+<!-- create auto increment sequence -->
+			<xsl:for-each select="row[@autoincrement = 1]">
+				<xsl:value-of select="concat( $crlf, 'CREATE SEQUENCE ' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="concat( 'SQ_', $table )" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( ';', '' )"/>
+			</xsl:for-each>
+
+<!-- create auto increment trigger -->
+			<xsl:value-of select="concat( $crlf, '' )"/>
+			<xsl:value-of select="concat( $crlf, 'CREATE OR REPLACE TRIGGER ' )"/>
+			<xsl:call-template name="ora_ident">
+				<xsl:with-param name="ident" select="concat( 'TG_', $table, '_BI' )" />
+			</xsl:call-template>
+			<xsl:value-of select="concat( $crlf, '    BEFORE INSERT ON ' )"/>
+			<xsl:call-template name="ora_ident">
+				<xsl:with-param name="ident" select="$table" />
+			</xsl:call-template>
+			<xsl:value-of select="concat( $crlf, '    FOR EACH ROW' )"/>
+			<xsl:value-of select="concat( $crlf, 'BEGIN' )"/>
+			<xsl:for-each select="row[@autoincrement = 1]">
+				<xsl:value-of select="concat( $crlf, '    if :NEW.' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="@name" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( ' is NULL then', '' )"/>
+				<xsl:value-of select="concat( $crlf, '        :NEW.' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="@name" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( ' := ', '' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="concat( 'SQ_', $table )" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( '.nextVal;', '' )"/>
+				<xsl:value-of select="concat( $crlf, '    end if;' )"/>
+			</xsl:for-each>
+			<xsl:value-of select="concat( $crlf, 'END;' )"/>
+			<xsl:value-of select="concat( $crlf, '/' )"/>
+			<xsl:value-of select="concat( $crlf, '' )"/>
+			<xsl:value-of select="concat( $crlf, 'SHOW ERRORS;' )"/>
+		</xsl:if>
+
 	</xsl:for-each>
 
-<!-- fk -->
-	<xsl:for-each select="table">
+<!-- Generate all FKs in the end - when all tables are present -->
+	<xsl:if test="0 &lt; count( table/row/relation )">
+		<xsl:value-of select="concat( $crlf, '' )" />
+		<xsl:value-of select="concat( $crlf, '-------------------------------------------------------------------------------' )" />
+		<xsl:value-of select="concat( $crlf, '' )" />
+	</xsl:if>
+	<xsl:for-each select="table/row/relation/../.."> <!--  loop through tables which have relations -->
+		<xsl:variable name="tbl_fr" select="@name" />
+<!-- This is a straight-forward algorithm: each <row> produces one ALTER TABLE statement -->
 		<xsl:for-each select="row">
 			<xsl:for-each select="relation">
-				<xsl:text>ALTER TABLE `</xsl:text>
-				<xsl:value-of select="../../@name" />
-				<xsl:text>` ADD FOREIGN KEY (</xsl:text>
-				<xsl:value-of select="../@name" />
-				<xsl:text>) REFERENCES `</xsl:text>
-				<xsl:value-of select="@table" />
-				<xsl:text>` (`</xsl:text>
-				<xsl:value-of select="@row" />
-				<xsl:text>`);
-</xsl:text>
+				<xsl:variable name="tbl_to" select="@table" />
+				<xsl:value-of select="concat( $crlf, 'ALTER TABLE ' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="$tbl_fr" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( ' ADD CONSTRAINT ', '' )"/>
+				<xsl:call-template name="ora_fk_constraint_name">
+					<xsl:with-param name="tbl_fr" select="$tbl_fr" />
+					<xsl:with-param name="tbl_to" select="$tbl_to" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( ' FOREIGN KEY ( ', '' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="../@name" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( ' ) REFERENCES ', '' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="$tbl_to" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( ' ( ', '' )"/>
+				<xsl:call-template name="ora_ident">
+					<xsl:with-param name="ident" select="@row" />
+				</xsl:call-template>
+				<xsl:value-of select="concat( ' );', '' )"/>
 			</xsl:for-each>
 		</xsl:for-each>
+<!-- @TODO: While it seems that XML DB model would contain several <row>-s for FK constraints which use compound keys: one <row> per column -->
 	</xsl:for-each>
 
 </xsl:template>
-</xsl:stylesheet>
\ No newline at end of file
+</xsl:stylesheet>