lib/SQL/Translator/Producer/DB2.pm

   1 package SQL::Translator::Producer::DB2;
   2
   3 # -------------------------------------------------------------------
   4 # Copyright (C) 2002-2009 SQLFairy Authors
   5 #
   6 # This program is free software; you can redistribute it and/or
   7 # modify it under the terms of the GNU General Public License as
   8 # published by the Free Software Foundation; version 2.
   9 #
  10 # This program is distributed in the hope that it will be useful, but
  11 # WITHOUT ANY WARRANTY; without even the implied warranty of
  12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  13 # General Public License for more details.
  14 #
  15 # You should have received a copy of the GNU General Public License
  16 # along with this program; if not, write to the Free Software
  17 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
  18 # 02111-1307  USA
  19 # -------------------------------------------------------------------
  20 =head1 NAME
  21
  22 SQL::Translator::Producer::DB2 - DB2 SQL producer
  23
  24 =head1 SYNOPSIS
  25
  26   use SQL::Translator;
  27
  28   my $t = SQL::Translator->new( parser => '...', producer => 'DB2' );
  29   print $translator->translate( $file );
  30
  31 =head1 DESCRIPTION
  32
  33 Creates an SQL DDL suitable for DB2.
  34
  35 =cut
  36
  37 use warnings;
  38 use strict;
  39 use vars qw[ $VERSION $DEBUG $WARN ];
  40 $VERSION = '1.99';
  41 $DEBUG   = 0 unless defined $DEBUG;
  42
  43 use SQL::Translator::Schema::Constants;
  44 use SQL::Translator::Utils qw(header_comment);
  45
  46
  47 # http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0006844.htm
  48
  49 # This is a terrible WTDI, each Parser should parse down to some standard set
  50 # of SQL data types, with field->extra entries being used to convert back to
  51 # weird types like "polygon" if needed (IMO anyway)
  52
  53 my %dt_translate;
  54 BEGIN {
  55   %dt_translate = (
  56     #
  57     # MySQL types
  58     #
  59     int        => 'integer',
  60     mediumint  => 'integer',
  61     tinyint    => 'smallint',
  62     char       => 'char',
  63     tinyblob   => 'blob',
  64     mediumblob => 'blob',
  65     longblob   => 'long varchar for bit data',
  66     tinytext   => 'varchar',
  67     text       => 'varchar',
  68     longtext   => 'varchar',
  69     mediumtext => 'varchar',
  70     enum       => 'varchar',
  71     set        => 'varchar',
  72     date       => 'date',
  73     datetime   => 'timestamp',
  74     time       => 'time',
  75     year       => 'date',
  76
  77     #
  78     # PostgreSQL types
  79     #
  80     'double precision'  => 'double',
  81     serial              => 'integer',
  82     bigserial           => 'integer',
  83     money               => 'double',
  84     character           => 'char',
  85     'character varying' => 'varchar',
  86     bytea               => 'BLOB',
  87     interval            => 'integer',
  88     boolean             => 'smallint',
  89     point               => 'integer',
  90     line                => 'integer',
  91     lseg                => 'integer',
  92     box                 => 'integer',
  93     path                => 'integer',
  94     polygon             => 'integer',
  95     circle              => 'integer',
  96     cidr                => 'integer',
  97     inet                => 'varchar',
  98     macaddr             => 'varchar',
  99     bit                 => 'number',
 100     'bit varying'       => 'number',
 101
 102     #
 103     # DB types
 104     #
 105     number              => 'integer',
 106     varchar2            => 'varchar',
 107     long                => 'clob',
 108 );
 109 }
 110
 111 my %db2_reserved = map { $_ => 1} qw/
 112 ADD                DETERMINISTIC  LEAVE         RESTART
 113 AFTER              DISALLOW       LEFT          RESTRICT
 114 ALIAS              DISCONNECT     LIKE          RESULT
 115 ALL                DISTINCT       LINKTYPE      RESULT_SET_LOCATOR
 116 ALLOCATE           DO             LOCAL         RETURN
 117 ALLOW              DOUBLE         LOCALE        RETURNS
 118 ALTER              DROP           LOCATOR       REVOKE
 119 AND                DSNHATTR       LOCATORS      RIGHT
 120 ANY                DSSIZE         LOCK          ROLLBACK
 121 APPLICATION        DYNAMIC        LOCKMAX       ROUTINE
 122 AS                 EACH           LOCKSIZE      ROW
 123 ASSOCIATE          EDITPROC       LONG          ROWS
 124 ASUTIME            ELSE           LOOP          RRN
 125 AUDIT              ELSEIF         MAXVALUE      RUN
 126 AUTHORIZATION      ENCODING       MICROSECOND   SAVEPOINT
 127 AUX                END            MICROSECONDS  SCHEMA
 128 AUXILIARY          END-EXEC       MINUTE        SCRATCHPAD
 129 BEFORE             END-EXEC1      MINUTES       SECOND
 130 BEGIN              ERASE          MINVALUE      SECONDS
 131 BETWEEN            ESCAPE         MODE          SECQTY
 132 BINARY             EXCEPT         MODIFIES      SECURITY
 133 BUFFERPOOL         EXCEPTION      MONTH         SELECT
 134 BY                 EXCLUDING      MONTHS        SENSITIVE
 135 CACHE              EXECUTE        NEW           SET
 136 CALL               EXISTS         NEW_TABLE     SIGNAL
 137 CALLED             EXIT           NO            SIMPLE
 138 CAPTURE            EXTERNAL       NOCACHE       SOME
 139 CARDINALITY        FENCED         NOCYCLE       SOURCE
 140 CASCADED           FETCH          NODENAME      SPECIFIC
 141 CASE               FIELDPROC      NODENUMBER    SQL
 142 CAST               FILE           NOMAXVALUE    SQLID
 143 CCSID              FINAL          NOMINVALUE    STANDARD
 144 CHAR               FOR            NOORDER       START
 145 CHARACTER          FOREIGN        NOT           STATIC
 146 CHECK              FREE           NULL          STAY
 147 CLOSE              FROM           NULLS         STOGROUP
 148 CLUSTER            FULL           NUMPARTS      STORES
 149 COLLECTION         FUNCTION       OBID          STYLE
 150 COLLID             GENERAL        OF            SUBPAGES
 151 COLUMN             GENERATED      OLD           SUBSTRING
 152 COMMENT            GET            OLD_TABLE     SYNONYM
 153 COMMIT             GLOBAL         ON            SYSFUN
 154 CONCAT             GO             OPEN          SYSIBM
 155 CONDITION          GOTO           OPTIMIZATION  SYSPROC
 156 CONNECT            GRANT          OPTIMIZE      SYSTEM
 157 CONNECTION         GRAPHIC        OPTION        TABLE
 158 CONSTRAINT         GROUP          OR            TABLESPACE
 159 CONTAINS           HANDLER        ORDER         THEN
 160 CONTINUE           HAVING         OUT           TO
 161 COUNT              HOLD           OUTER         TRANSACTION
 162 COUNT_BIG          HOUR           OVERRIDING    TRIGGER
 163 CREATE             HOURS          PACKAGE       TRIM
 164 CROSS              IDENTITY       PARAMETER     TYPE
 165 CURRENT            IF             PART          UNDO
 166 CURRENT_DATE       IMMEDIATE      PARTITION     UNION
 167 CURRENT_LC_CTYPE   IN             PATH          UNIQUE
 168 CURRENT_PATH       INCLUDING      PIECESIZE     UNTIL
 169 CURRENT_SERVER     INCREMENT      PLAN          UPDATE
 170 CURRENT_TIME       INDEX          POSITION      USAGE
 171 CURRENT_TIMESTAMP  INDICATOR      PRECISION     USER
 172 CURRENT_TIMEZONE   INHERIT        PREPARE       USING
 173 CURRENT_USER       INNER          PRIMARY       VALIDPROC
 174 CURSOR             INOUT          PRIQTY        VALUES
 175 CYCLE              INSENSITIVE    PRIVILEGES    VARIABLE
 176 DATA               INSERT         PROCEDURE     VARIANT
 177 DATABASE           INTEGRITY      PROGRAM       VCAT
 178 DAY                INTO           PSID          VIEW
 179 DAYS               IS             QUERYNO       VOLUMES
 180 DB2GENERAL         ISOBID         READ          WHEN
 181 DB2GENRL           ISOLATION      READS         WHERE
 182 DB2SQL             ITERATE        RECOVERY      WHILE
 183 DBINFO             JAR            REFERENCES    WITH
 184 DECLARE            JAVA           REFERENCING   WLM
 185 DEFAULT            JOIN           RELEASE       WRITE
 186 DEFAULTS           KEY            RENAME        YEAR
 187 DEFINITION         LABEL          REPEAT        YEARS
 188 DELETE             LANGUAGE       RESET
 189 DESCRIPTOR         LC_CTYPE       RESIGNAL
 190 /;
 191
 192 #------------------------------------------------------------------------------
 193
 194 sub produce
 195 {
 196     my ($translator) = @_;
 197     $DEBUG             = $translator->debug;
 198     $WARN              = $translator->show_warnings;
 199     my $no_comments    = $translator->no_comments;
 200     my $add_drop_table = $translator->add_drop_table;
 201     my $schema         = $translator->schema;
 202     my $output         = '';
 203     my $indent         = '    ';
 204
 205     $output .= header_comment unless($no_comments);
 206     my (@table_defs, @fks, @index_defs);
 207     foreach my $table ($schema->get_tables)
 208     {
 209         push @table_defs, 'DROP TABLE ' . $table->name . ";" if $add_drop_table;
 210         my ($table_def, $fks) = create_table($table, {
 211             no_comments => $no_comments});
 212         push @table_defs, $table_def;
 213         push @fks, @$fks;
 214
 215         foreach my $index ($table->get_indices)
 216         {
 217             push @index_defs, create_index($index);
 218         }
 219
 220     }
 221     my (@view_defs);
 222     foreach my $view ( $schema->get_views )
 223     {
 224         push @view_defs, create_view($view);
 225     }
 226     my (@trigger_defs);
 227     foreach my $trigger ( $schema->get_triggers )
 228     {
 229         push @trigger_defs, create_trigger($trigger);
 230     }
 231
 232     return wantarray ? (@table_defs, @fks, @index_defs, @view_defs, @trigger_defs) :
 233         $output . join("\n\n", @table_defs, @fks, @index_defs, @view_defs, @trigger_defs) . "\n";
 234 }
 235
 236 { my %objnames;
 237
 238     sub check_name
 239     {
 240         my ($name, $type, $length) = @_;
 241
 242         my $newname = $name;
 243         if(length($name) > $length)   ## Maximum table name length is 18
 244         {
 245             warn "Table name $name is longer than $length characters, truncated" if $WARN;
 246 #             if(grep {$_ eq substr($name, 0, $length) }
 247 #                               values(%{$objnames{$type}}))
 248 #             {
 249 #                 die "Got multiple matching table names when truncated";
 250 #             }
 251 #             $objnames{$type}{$name} = substr($name, 0,$length);
 252 #             $newname = $objnames{$type}{$name};
 253         }
 254
 255         if($db2_reserved{uc($newname)})
 256         {
 257             warn "$newname is a reserved word in DB2!" if $WARN;
 258         }
 259
 260 #        return sprintf("%-*s", $length-5, $newname);
 261         return $newname;
 262     }
 263 }
 264
 265 sub create_table
 266 {
 267     my ($table, $options) = @_;
 268
 269     my $table_name = check_name($table->name, 'tables', 128);
 270     # this limit is 18 in older DB2s ! (<= 8)
 271
 272     my (@field_defs, @comments);
 273     push @comments, "--\n-- Table: $table_name\n--" unless $options->{no_comments};
 274     foreach my $field ($table->get_fields)
 275     {
 276         push @field_defs, create_field($field);
 277     }
 278     my (@con_defs, @fks);
 279     foreach my $con ($table->get_constraints)
 280     {
 281         my ($cdefs, $fks) = create_constraint($con);
 282         push @con_defs, @$cdefs;
 283         push @fks, @$fks;
 284     }
 285
 286     my $tablespace = $table->extra()->{'TABLESPACE'} || '';
 287     my $table_def = "CREATE TABLE $table_name (\n";
 288     $table_def .= join (",\n", map { "  $_" } @field_defs, @con_defs);
 289     $table_def .= "\n)";
 290     $table_def .= $tablespace ? "IN $tablespace;" : ';';
 291
 292     return $table_def, \@fks;
 293 }
 294
 295 sub create_field
 296 {
 297     my ($field) = @_;
 298
 299     my $field_name = check_name($field->name, 'fields', 30);
 300 #    use Data::Dumper;
 301 #    print Dumper(\%dt_translate);
 302 #    print $field->data_type, " ", $dt_translate{lc($field->data_type)}, "\n";
 303     my $data_type = uc($dt_translate{lc($field->data_type)} || $field->data_type);
 304     my $size = $field->size();
 305
 306     my $field_def = "$field_name $data_type";
 307     $field_def .= $field->is_auto_increment ?
 308         ' GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)' : '';
 309     $field_def .= $data_type =~ /(CHAR|CLOB)/i ? "(${size})" : '';
 310     $field_def .= !$field->is_nullable ? ' NOT NULL':'';
 311 #            $field_def .= $field->is_primary_key ? ' PRIMARY KEY':'';
 312     $field_def .= !defined $field->default_value ? '' :
 313         $field->default_value =~ /current( |_)timestamp/i ||
 314         $field->default_value =~ /\Qnow()\E/i ?
 315         ' DEFAULT CURRENT TIMESTAMP' : defined $field->default_value ?
 316         (" DEFAULT " . ($data_type =~ /(INT|DOUBLE)/i ?
 317                         $field->default_value : "'" . $field->default_value . "'")
 318          ) : '';
 319
 320     return $field_def;
 321 }
 322
 323 sub create_index
 324 {
 325     my ($index) = @_;
 326
 327     my $out = sprintf('CREATE %sINDEX %s ON %s ( %s );',
 328                       $index->type() =~ /^UNIQUE$/i ? 'UNIQUE' : '',
 329                       $index->name,
 330                       $index->table->name,
 331                       join(', ', $index->fields) );
 332
 333     return $out;
 334 }
 335
 336 sub create_constraint
 337 {
 338     my ($constraint) = @_;
 339
 340     my (@con_defs, @fks);
 341
 342     my $ctype =  $constraint->type =~ /^PRIMARY(_|\s)KEY$/i ? 'PRIMARY KEY' :
 343                  $constraint->type =~ /^UNIQUE$/i      ? 'UNIQUE' :
 344                  $constraint->type =~ /^CHECK_C$/i     ? 'CHECK' :
 345                  $constraint->type =~ /^FOREIGN(_|\s)KEY$/i ? 'FOREIGN KEY' : '';
 346
 347     my $expr = $constraint->type =~ /^CHECK_C$/i ? $constraint->expression :
 348         '';
 349     my $ref = $constraint->type =~ /^FOREIGN(_|\s)KEY$/i ? ('REFERENCES ' . $constraint->reference_table . '(' . join(', ', $constraint->reference_fields) . ')') : '';
 350     my $update = $constraint->on_update ? $constraint->on_update : '';
 351     my $delete = $constraint->on_delete ? $constraint->on_delete : '';
 352
 353     my $out = join(' ', grep { $_ }
 354                       $constraint->name ? ('CONSTRAINT ' . $constraint->name) : '',
 355                       $ctype,
 356                       '(' . join (', ', $constraint->fields) . ')',
 357                       $expr ? $expr : $ref,
 358                       $update,
 359                       $delete);
 360     if ($constraint->type eq FOREIGN_KEY) {
 361         my $table_name = $constraint->table->name;
 362         $out = "ALTER TABLE $table_name ADD $out;";
 363         push @fks, $out;
 364     }
 365     else {
 366         push @con_defs, $out;
 367     }
 368
 369     return \@con_defs, \@fks;
 370
 371 }
 372
 373 sub create_view
 374 {
 375     my ($view) = @_;
 376
 377     my $out = sprintf("CREATE VIEW %s AS\n%s;",
 378                       $view->name,
 379                       $view->sql);
 380
 381     return $out;
 382 }
 383
 384 sub create_trigger
 385 {
 386     my ($trigger) = @_;
 387 # create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
 388
 389     my $out = sprintf('CREATE TRIGGER %s %s %s ON %s %s %s MODE DB2SQL %s',
 390                       $trigger->name,
 391                       $trigger->perform_action_when || 'AFTER',
 392                       $trigger->database_event =~ /update_on/i ?
 393                         ('UPDATE OF '. join(', ', $trigger->fields)) :
 394                         $trigger->database_event || 'UPDATE',
 395                       $trigger->table->name,
 396                       $trigger->extra->{reference} || 'REFERENCING OLD AS oldrow NEW AS newrow',
 397                       $trigger->extra->{granularity} || 'FOR EACH ROW',
 398                       $trigger->action );
 399
 400     return $out;
 401
 402 }
 403
 404 sub alter_field
 405 {
 406     my ($from_field, $to_field) = @_;
 407
 408     my $data_type = uc($dt_translate{lc($to_field->data_type)} || $to_field->data_type);
 409
 410     my $size = $to_field->size();
 411     $data_type .= $data_type =~ /CHAR/i ? "(${size})" : '';
 412
 413     # DB2 will only allow changing of varchar/vargraphic datatypes
 414     # to extend their lengths. Or changing of text types to other
 415     # texttypes, and numeric types to larger numeric types. (v8)
 416     # We can also drop/add keys, checks and constraints, but not
 417     # columns !?
 418
 419     my $out = sprintf('ALTER TABLE %s ALTER %s SET DATATYPE %s',
 420                       $to_field->table->name,
 421                       $to_field->name,
 422                       $data_type);
 423
 424 }
 425
 426 sub add_field
 427 {
 428     my ($new_field) = @_;
 429
 430     my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
 431                       $new_field->table->name,
 432                       create_field($new_field));
 433
 434     return $out;
 435 }
 436
 437 sub drop_field
 438 {
 439     my ($field) = @_;
 440
 441     return '';
 442 }
 443 1;