1<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 2 xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 3 xmlns:wb-string-util="workbench.util.StringUtil"> 4 5<xsl:template name="create-table"> 6 <xsl:variable name="squote"><xsl:text>'</xsl:text></xsl:variable> 7 <xsl:variable name="dsquote"><xsl:text>''</xsl:text></xsl:variable> 8 9 <xsl:variable name="table-name" select="table-name"/> 10 <xsl:variable name="pk-col-count"> 11 <xsl:value-of select="count(column-def[primary-key='true'])"/> 12 </xsl:variable> 13 14 <xsl:element name="createTable"> 15 <xsl:attribute name="tableName"><xsl:value-of select="$table-name"/></xsl:attribute> 16 <xsl:if test="string-length($schema.owner) > 0"> 17 <xsl:attribute name="schemaName"> 18 <xsl:value-of select="$schema.owner"/> 19 </xsl:attribute> 20 </xsl:if> 21 <xsl:if test="string-length($tablespace.table) > 0"> 22 <xsl:attribute name="tablespace"> 23 <xsl:value-of select="$tablespace.table"/> 24 </xsl:attribute> 25 </xsl:if> 26 27 <xsl:if test="string-length(comment) > 0"> 28 <xsl:attribute name="remarks"> 29 <xsl:call-template name="_replace_text"> 30 <xsl:with-param name="text" select="comment"/> 31 <xsl:with-param name="replace" select="$squote"/> 32 <xsl:with-param name="by" select="$dsquote"/> 33 </xsl:call-template> 34 </xsl:attribute> 35 </xsl:if> 36 37 <!-- find PK name --> 38 <xsl:variable name="pk-name"> 39 <xsl:value-of select="index-def[primary-key='true']/name"/> 40 </xsl:variable> 41 42 <xsl:for-each select="column-def"> 43 <xsl:sort select="dbms-position"/> 44 45 <xsl:variable name="column-name" select="@name"/> 46 47 <xsl:variable name="data-type"> 48 <xsl:if test="$useJdbcTypes = 'true'"> 49 <xsl:call-template name="write-data-type"> 50 <xsl:with-param name="type-id" select="java-sql-type"/> 51 <xsl:with-param name="dbms-type" select="dbms-data-type"/> 52 <xsl:with-param name="precision" select="dbms-data-size"/> 53 <xsl:with-param name="scale" select="dbms-data-digits"/> 54 </xsl:call-template> 55 </xsl:if> 56 57 <xsl:if test="$useJdbcTypes = 'false'"> 58 <xsl:value-of select="dbms-data-type"/> 59 </xsl:if> 60 </xsl:variable> 61 62 <xsl:element name="column"> 63 <xsl:attribute name="name"><xsl:value-of select="$column-name"/></xsl:attribute> 64 <xsl:attribute name="type"><xsl:value-of select="$data-type"/></xsl:attribute> 65 66 <xsl:variable name="pk-flag" select="primary-key"/> 67 <xsl:variable name="nullable" select="nullable"/> 68 <xsl:variable name="type-id" select="java-sql-type"/> 69 70 <xsl:if test="string-length(default-value) > 0"> 71 72 <!-- defaults for character columns go into a different attribute than other values --> 73 <xsl:variable name="character-types"> 74 <xsl:value-of select="'12;-9;-15;1;'"/> 75 </xsl:variable> 76 <xsl:if test="contains($character-types, concat($type-id,';'))"> 77 <xsl:attribute name="defaultValue"> 78 <xsl:value-of select="wb-string-util:trimQuotes(default-value)"/> 79 </xsl:attribute> 80 </xsl:if> 81 82 <xsl:variable name="numeric-types"> 83 <xsl:value-of select="'-5;3;4;5;6;7;8;'"/> 84 </xsl:variable> 85 <xsl:if test="contains($numeric-types, concat($type-id,';'))"> 86 <xsl:attribute name="defaultValueNumeric"> 87 <xsl:value-of select="translate(default-value, ' *?[]()ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', '')"/> 88 </xsl:attribute> 89 </xsl:if> 90 91 <xsl:variable name="boolean-types"> 92 <xsl:value-of select="'16;-7;'"/> 93 </xsl:variable> 94 <xsl:if test="contains($boolean-types, concat($type-id,';'))"> 95 <xsl:attribute name="defaultValueBoolean"> 96 <xsl:value-of select="default-value"/> 97 </xsl:attribute> 98 </xsl:if> 99 </xsl:if> 100 101 <!-- only write remarks if they are defined --> 102 <xsl:if test="string-length(comment) > 0"> 103 <xsl:attribute name="remarks"> 104 <xsl:call-template name="_replace_text"> 105 <xsl:with-param name="text" select="comment"/> 106 <xsl:with-param name="replace" select="$squote"/> 107 <xsl:with-param name="by" select="$dsquote"/> 108 </xsl:call-template> 109 </xsl:attribute> 110 </xsl:if> 111 112 <xsl:if test="$useJdbcTypes = 'true'"> 113 <xsl:if test="auto-increment = 'true'"> 114 <xsl:attribute name="autoIncrement"> 115 <xsl:value-of select="'true'"/> 116 </xsl:attribute> 117 </xsl:if> 118 </xsl:if> 119 120 <xsl:if test="($pk-flag = 'true' and $pk-col-count = 1) or $nullable = 'false'"> 121 <xsl:element name="constraints"> 122 123 <xsl:if test="$nullable = 'false'"> 124 <xsl:attribute name="nullable"> 125 <xsl:value-of select="$nullable"/> 126 </xsl:attribute> 127 </xsl:if> 128 129 <xsl:if test="$pk-flag = 'true' and $pk-col-count = 1"> 130 <xsl:attribute name="primaryKey"> 131 <xsl:value-of select="'true'"/> 132 </xsl:attribute> 133 <xsl:attribute name="primaryKeyName"> 134 <xsl:value-of select="$pk-name"/> 135 </xsl:attribute> 136 </xsl:if> 137 </xsl:element> 138 </xsl:if> 139 140 </xsl:element> 141 142 </xsl:for-each> <!-- columns --> 143 144 </xsl:element> 145 146 <!-- 147 now process all index definitions for this table 148 --> 149 <xsl:for-each select="index-def"> 150 <!-- There is no way to create an Oracle DOMAIN index with Liquibase --> 151 <xsl:if test="type != 'DOMAIN'"> 152 <xsl:call-template name="create-index"> 153 <xsl:with-param name="table-name" select="$table-name"/> 154 <xsl:with-param name="pk-col-count" select="$pk-col-count"/> 155 </xsl:call-template> 156 </xsl:if> 157 </xsl:for-each> <!-- index-def --> 158 159 <xsl:for-each select="table-constraints/constraint-definition[@type='check']"> 160 <xsl:variable name="condition"> 161 <xsl:value-of select="normalize-space(.)"/> 162 </xsl:variable> 163 <xsl:element name="sql">ALTER TABLE <xsl:value-of select="$table-name"/> ADD CONSTRAINT <xsl:value-of select="@name"/> CHECK <xsl:value-of select="normalize-space(.)"/></xsl:element> 164 </xsl:for-each> 165 166</xsl:template> 167 168<xsl:template name="create-index"> 169 <xsl:param name="table-name"/> 170 <xsl:param name="pk-col-count"/> 171 172 <xsl:variable name="index-name"> 173 <xsl:value-of select="name"/> 174 </xsl:variable> 175 176 <xsl:variable name="unique-flag"> 177 <xsl:value-of select="unique"/> 178 </xsl:variable> 179 180 <!-- 181 Primary keys with a single column are already defined in the table itself 182 so we only need to take care of those with more than one column. 183 184 Oracle supports (non-unique) index definitions that have more columns than the PK 185 to be used for enforcing the PK. No addPrimaryKey should be generated for those indexes. 186 187 Only indexes that are marked as PK and have the same number of columns as the PK itself should be considered 188 as the index for the PK. 189 190 Checking for PK indexes ensures that a unique constraint is not added twice: 191 once for the PK columns and once for the unique index that is present 192 --> 193 <xsl:if test="primary-key='true' and count(column-list/column) > 1 and count(column-list/column) = $pk-col-count"> 194 <xsl:variable name="pk-columns"> 195 <xsl:for-each select="column-list/column"> 196 <xsl:value-of select="@name"/> 197 <xsl:if test="position() < last()"><xsl:text>,</xsl:text></xsl:if> 198 </xsl:for-each> 199 </xsl:variable> 200 201 <xsl:element name="addPrimaryKey"> 202 <xsl:attribute name="tableName"><xsl:value-of select="$table-name"/></xsl:attribute> 203 <xsl:attribute name="columnNames"><xsl:value-of select="$pk-columns"/></xsl:attribute> 204 <xsl:attribute name="constraintName"><xsl:value-of select="$index-name"/></xsl:attribute> 205 </xsl:element> 206 </xsl:if> 207 208 <xsl:if test="primary-key='false' or count(column-list/column) != $pk-col-count"> 209 <xsl:element name="createIndex"> 210 <xsl:attribute name="indexName"><xsl:value-of select="$index-name"/></xsl:attribute> 211 <xsl:attribute name="tableName"><xsl:value-of select="$table-name"/></xsl:attribute> 212 <xsl:attribute name="unique"><xsl:value-of select="$unique-flag"/></xsl:attribute> 213 <xsl:if test="string-length($schema.owner) > 0"> 214 <xsl:attribute name="schemaName"> 215 <xsl:value-of select="$schema.owner"/> 216 </xsl:attribute> 217 </xsl:if> 218 <xsl:if test="string-length($tablespace.index) > 0"> 219 <xsl:attribute name="tablespace"> 220 <xsl:value-of select="$tablespace.index"/> 221 </xsl:attribute> 222 </xsl:if> 223 224 <xsl:for-each select="column-list/column"> 225 <xsl:element name="column"> 226 <xsl:attribute name="name"> 227 <xsl:value-of select="@name"/> 228 </xsl:attribute> 229 </xsl:element> 230 </xsl:for-each> <!-- index columns --> 231 </xsl:element> 232 </xsl:if> 233</xsl:template> 234 235<xsl:template name="add-fk"> 236 <xsl:param name="tablename"/> 237 238 <xsl:variable name="fk-name" select="constraint-name"/> 239 <xsl:variable name="referenced-table" select="references/table-name"/> 240 241 <xsl:variable name="base-columns"> 242 <xsl:for-each select="source-columns/column"> 243 <xsl:copy-of select="."/> 244 <xsl:if test="position() < last()"><xsl:text>,</xsl:text></xsl:if> 245 </xsl:for-each> 246 </xsl:variable> 247 248 <xsl:variable name="referenced-columns"> 249 <xsl:for-each select="referenced-columns/column"> 250 <xsl:copy-of select="."/> 251 <xsl:if test="position() < last()"><xsl:text>,</xsl:text></xsl:if> 252 </xsl:for-each> 253 </xsl:variable> 254 255 <xsl:variable name="update-rule" select="update-rule/text()"/> 256 <xsl:variable name="delete-rule" select="delete-rule/text()"/> 257 <xsl:variable name="deferrable-value" select="deferrable/@jdbcValue"/> 258 259 <xsl:element name="addForeignKeyConstraint"> 260 <xsl:attribute name="constraintName"><xsl:value-of select="$fk-name"/></xsl:attribute> 261 <xsl:attribute name="baseTableName"><xsl:value-of select="$tablename"/></xsl:attribute> 262 <xsl:attribute name="baseColumnNames"><xsl:value-of select="$base-columns"/></xsl:attribute> 263 <xsl:attribute name="referencedTableName"><xsl:value-of select="$referenced-table"/></xsl:attribute> 264 <xsl:attribute name="referencedColumnNames"><xsl:value-of select="$referenced-columns"/></xsl:attribute> 265 266 <xsl:if test="$delete-rule != 'NO ACTION' and $delete-rule != 'RESTRICT'"> 267 <xsl:attribute name="onDelete"> 268 <xsl:value-of select="$delete-rule"/> 269 </xsl:attribute> 270 </xsl:if> 271 <xsl:if test="$update-rule != 'NO ACTION' and $update-rule != 'RESTRICT'"> 272 <xsl:attribute name="onUpdate"> 273 <xsl:value-of select="$update-rule"/> 274 </xsl:attribute> 275 </xsl:if> 276 <!-- constant values for the deferrability: 277 7 = not deferrable 278 6 = initially immediate 279 5 = initially deferred 280 --> 281 <xsl:if test="$deferrable-value = 5 or $deferrable-value = 6"> 282 <xsl:attribute name="deferrable"> 283 <xsl:value-of select="'true'"/> 284 </xsl:attribute> 285 <xsl:if test="$deferrable-value = 5"> 286 <xsl:attribute name="initiallyDeferred"> 287 <xsl:value-of select="'true'"/> 288 </xsl:attribute> 289 </xsl:if> 290 <xsl:if test="$deferrable-value = 6"> 291 <xsl:attribute name="initiallyDeferred"> 292 <xsl:value-of select="'false'"/> 293 </xsl:attribute> 294 </xsl:if> 295 </xsl:if> 296 </xsl:element> 297</xsl:template> 298 299<xsl:template match="sequence-def"> 300 <xsl:variable name="seq-name" select="@name"/> 301 <xsl:variable name="max-value" select="sequence-properties/property[@name='MAX_VALUE']/@value"/> 302 303 <xsl:element name="createSequence"> 304 <xsl:attribute name="sequenceName"><xsl:value-of select="$seq-name"/></xsl:attribute> 305 <xsl:if test="string-length($schema.owner) > 0"> 306 <xsl:attribute name="schemaName"> 307 <xsl:value-of select="$schema.owner"/> 308 </xsl:attribute> 309 </xsl:if> 310 <xsl:if test="string-length(sequence-properties/property[@name='INCREMENT']/@value) > 0"> 311 <xsl:attribute name="incrementBy"> 312 <xsl:value-of select="sequence-properties/property[@name='INCREMENT']/@value"/> 313 </xsl:attribute> 314 </xsl:if> 315 <xsl:if test="string-length(sequence-properties/property[@name='CYCLE']/@value) > 0"> 316 <xsl:attribute name="cycle"> 317 <xsl:value-of select="sequence-properties/property[@name='CYCLE']/@value"/> 318 </xsl:attribute> 319 </xsl:if> 320 <xsl:if test="string-length(sequence-properties/property[@name='MIN_VALUE']/@value) > 0"> 321 <xsl:attribute name="minValue"> 322 <xsl:value-of select="sequence-properties/property[@name='MIN_VALUE']/@value"/> 323 </xsl:attribute> 324 </xsl:if> 325 <xsl:if test="string-length($max-value) > 0 and $max-value != '9223372036854775807' and substring($max-value, 1, 27) != '999999999999999999999999999'"> 326 <xsl:attribute name="maxValue"> 327 <xsl:value-of select="sequence-properties/property[@name='MAX_VALUE']/@value"/> 328 </xsl:attribute> 329 </xsl:if> 330 <xsl:if test="$useOrderedSequence = 'true'"> 331 <xsl:if test="string-length(sequence-properties/property[@name='ORDERED']/@value) > 0"> 332 <xsl:attribute name="ordered"> 333 <xsl:value-of select="sequence-properties/property[@name='ORDERED']/@value"/> 334 </xsl:attribute> 335 </xsl:if> 336 </xsl:if> 337 </xsl:element> 338 <xsl:if test="string-length(sequence-properties/property[@name='OWNED_BY']/@value) > 0"> 339 <xsl:element name="sql"> 340 <xsl:attribute name="dbms">postgresql</xsl:attribute> 341 <xsl:text>ALTER SEQUENCE </xsl:text><xsl:value-of select="$seq-name"/><xsl:text> OWNED BY </xsl:text><xsl:value-of select="sequence-properties/property[@name='OWNED_BY']/@value"/><xsl:text>;</xsl:text> 342 </xsl:element> 343 </xsl:if> 344</xsl:template> 345 346<!-- 347 Map jdbc data types (from java.sql.Types) to a proper data type 348 using scale and precision where approriate. 349--> 350<xsl:template name="write-data-type"> 351 <xsl:param name="type-id"/> 352 <xsl:param name="dbms-type"/> 353 <xsl:param name="precision"/> 354 <xsl:param name="scale"/> 355 <xsl:choose> 356 <xsl:when test="$type-id = 2005 or ($type-id = 12 and $precision = 2147483647)"> 357 <xsl:text>CLOB</xsl:text> 358 </xsl:when> 359 <xsl:when test="$type-id = 2011"> 360 <xsl:text>NCLOB</xsl:text> 361 </xsl:when> 362 <xsl:when test="$type-id = 2004 or $type-id = -2"> 363 <xsl:text>BLOB</xsl:text> 364 </xsl:when> 365 <xsl:when test="$type-id = -3"> 366 <xsl:text>VARBINARY</xsl:text> 367 </xsl:when> 368 <xsl:when test="$type-id = -4"> 369 <xsl:text>LONGVARBINARY</xsl:text> 370 </xsl:when> 371 <xsl:when test="$type-id = -1"> 372 <xsl:text>LONGVARCHAR</xsl:text> 373 </xsl:when> 374 <xsl:when test="$type-id = 93"> 375 <xsl:text>TIMESTAMP</xsl:text> 376 </xsl:when> 377 <xsl:when test="$type-id = 92"> 378 <xsl:text>TIME</xsl:text> 379 </xsl:when> 380 <xsl:when test="$type-id = 91"> 381 <xsl:text>DATE</xsl:text> 382 </xsl:when> 383 <xsl:when test="$type-id = 1"> 384 <xsl:text>CHAR(</xsl:text><xsl:value-of select="$precision"/><xsl:text>)</xsl:text> 385 </xsl:when> 386 <xsl:when test="$type-id = -15"> 387 <xsl:text>NCHAR(</xsl:text><xsl:value-of select="$precision"/><xsl:text>)</xsl:text> 388 </xsl:when> 389 <xsl:when test="$type-id = 4"> 390 <xsl:text>INTEGER</xsl:text> 391 </xsl:when> 392 <xsl:when test="$type-id = -5"> 393 <xsl:text>BIGINT</xsl:text> 394 </xsl:when> 395 <xsl:when test="$type-id = 5"> 396 <xsl:text>SMALLINT</xsl:text> 397 </xsl:when> 398 <xsl:when test="$type-id = -6"> 399 <xsl:text>TINYINT</xsl:text> 400 </xsl:when> 401 <xsl:when test="$type-id = 8"> 402 <xsl:text>DOUBLE</xsl:text> 403 </xsl:when> 404 <xsl:when test="$type-id = 7"> 405 <xsl:text>REAL</xsl:text> 406 </xsl:when> 407 <xsl:when test="$type-id = 6"> 408 <xsl:text>FLOAT</xsl:text> 409 </xsl:when> 410 <xsl:when test="$type-id = 16"> 411 <xsl:text>BOOLEAN</xsl:text> 412 </xsl:when> 413 <xsl:when test="$type-id = -7"> 414 <xsl:text>BIT</xsl:text> 415 </xsl:when> 416 <xsl:when test="$type-id = 3 or $type-id = 2"> 417 <xsl:if test="$scale > 0"> 418 <xsl:text>DECIMAL(</xsl:text><xsl:value-of select="$precision"/><xsl:text>,</xsl:text><xsl:value-of select="$scale"/><xsl:text>)</xsl:text> 419 </xsl:if> 420 <xsl:if test="$scale = 0 and $precision > 10"> 421 <xsl:text>BIGINT</xsl:text> 422 </xsl:if> 423 <xsl:if test="$scale = 0 and $precision <= 10"> 424 <xsl:text>INTEGER</xsl:text> 425 </xsl:if> 426 </xsl:when> 427 <xsl:when test="$type-id = 12"> 428 <xsl:text>VARCHAR(</xsl:text><xsl:value-of select="$precision"/><xsl:text>)</xsl:text> 429 </xsl:when> 430 <xsl:when test="$type-id = -9"> 431 <xsl:text>NVARCHAR(</xsl:text><xsl:value-of select="$precision"/><xsl:text>)</xsl:text> 432 </xsl:when> 433 <xsl:when test="$mapXMLToClob = 'true' and $type-id = 1111 and ($dbms-type='XML' or $dbms-type = 'XMLTYPE')"> 434 <xsl:text>CLOB</xsl:text> 435 </xsl:when> 436 <xsl:when test="$mapXMLToClob = 'false' and $type-id = 1111 and ($dbms-type='XML' or $dbms-type = 'XMLTYPE')"> 437 <xsl:value-of select="$dbms-type"/> 438 </xsl:when> 439 <xsl:otherwise> 440 <xsl:text>$dbms-type</xsl:text> 441 </xsl:otherwise> 442 </xsl:choose> 443</xsl:template> 444 445<xsl:template name="_replace_text"> 446 <xsl:param name="text"/> 447 <xsl:param name="replace"/> 448 <xsl:param name="by"/> 449 <xsl:choose> 450 <xsl:when test="contains($text, $replace)"> 451 <xsl:value-of select="substring-before($text, $replace)"/> 452 <xsl:copy-of select="$by"/> 453 <xsl:call-template name="_replace_text"> 454 <xsl:with-param name="text" select="substring-after($text, $replace)"/> 455 <xsl:with-param name="replace" select="$replace"/> 456 <xsl:with-param name="by" select="$by"/> 457 </xsl:call-template> 458 </xsl:when> 459 <xsl:otherwise> 460 <xsl:value-of select="$text"/> 461 </xsl:otherwise> 462 </xsl:choose> 463</xsl:template> 464 465</xsl:stylesheet>