1 package SQL::Translator::Producer::DB2;
3 # -------------------------------------------------------------------
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-2009 SQLFairy Authors
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
24 SQL::Translator::Producer::DB2 - DB2 SQL producer
30 my $t = SQL::Translator->new( parser => '...', producer => 'DB2' );
31 print $translator->translate( $file );
35 Creates an SQL DDL suitable for DB2.
41 use vars qw[ $DEBUG $WARN ];
42 $DEBUG = 0 unless defined $DEBUG;
44 use SQL::Translator::Schema::Constants;
45 use SQL::Translator::Utils qw(header_comment);
48 # http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0006844.htm
50 # This is a terrible WTDI, each Parser should parse down to some standard set
51 # of SQL data types, with field->extra entries being used to convert back to
52 # weird types like "polygon" if needed (IMO anyway)
61 mediumint => 'integer',
62 tinyint => 'smallint',
66 longblob => 'long varchar for bit data',
67 tinytext => 'varchar',
69 longtext => 'varchar',
70 mediumtext => 'varchar',
74 datetime => 'timestamp',
81 'double precision' => 'double',
83 bigserial => 'integer',
86 'character varying' => 'varchar',
88 interval => 'integer',
89 boolean => 'smallint',
101 'bit varying' => 'number',
107 varchar2 => 'varchar',
112 my %db2_reserved = map { $_ => 1} qw/
113 ADD DETERMINISTIC LEAVE RESTART
114 AFTER DISALLOW LEFT RESTRICT
115 ALIAS DISCONNECT LIKE RESULT
116 ALL DISTINCT LINKTYPE RESULT_SET_LOCATOR
117 ALLOCATE DO LOCAL RETURN
118 ALLOW DOUBLE LOCALE RETURNS
119 ALTER DROP LOCATOR REVOKE
120 AND DSNHATTR LOCATORS RIGHT
121 ANY DSSIZE LOCK ROLLBACK
122 APPLICATION DYNAMIC LOCKMAX ROUTINE
124 ASSOCIATE EDITPROC LONG ROWS
125 ASUTIME ELSE LOOP RRN
126 AUDIT ELSEIF MAXVALUE RUN
127 AUTHORIZATION ENCODING MICROSECOND SAVEPOINT
128 AUX END MICROSECONDS SCHEMA
129 AUXILIARY END-EXEC MINUTE SCRATCHPAD
130 BEFORE END-EXEC1 MINUTES SECOND
131 BEGIN ERASE MINVALUE SECONDS
132 BETWEEN ESCAPE MODE SECQTY
133 BINARY EXCEPT MODIFIES SECURITY
134 BUFFERPOOL EXCEPTION MONTH SELECT
135 BY EXCLUDING MONTHS SENSITIVE
136 CACHE EXECUTE NEW SET
137 CALL EXISTS NEW_TABLE SIGNAL
138 CALLED EXIT NO SIMPLE
139 CAPTURE EXTERNAL NOCACHE SOME
140 CARDINALITY FENCED NOCYCLE SOURCE
141 CASCADED FETCH NODENAME SPECIFIC
142 CASE FIELDPROC NODENUMBER SQL
143 CAST FILE NOMAXVALUE SQLID
144 CCSID FINAL NOMINVALUE STANDARD
145 CHAR FOR NOORDER START
146 CHARACTER FOREIGN NOT STATIC
148 CLOSE FROM NULLS STOGROUP
149 CLUSTER FULL NUMPARTS STORES
150 COLLECTION FUNCTION OBID STYLE
151 COLLID GENERAL OF SUBPAGES
152 COLUMN GENERATED OLD SUBSTRING
153 COMMENT GET OLD_TABLE SYNONYM
154 COMMIT GLOBAL ON SYSFUN
155 CONCAT GO OPEN SYSIBM
156 CONDITION GOTO OPTIMIZATION SYSPROC
157 CONNECT GRANT OPTIMIZE SYSTEM
158 CONNECTION GRAPHIC OPTION TABLE
159 CONSTRAINT GROUP OR TABLESPACE
160 CONTAINS HANDLER ORDER THEN
161 CONTINUE HAVING OUT TO
162 COUNT HOLD OUTER TRANSACTION
163 COUNT_BIG HOUR OVERRIDING TRIGGER
164 CREATE HOURS PACKAGE TRIM
165 CROSS IDENTITY PARAMETER TYPE
167 CURRENT_DATE IMMEDIATE PARTITION UNION
168 CURRENT_LC_CTYPE IN PATH UNIQUE
169 CURRENT_PATH INCLUDING PIECESIZE UNTIL
170 CURRENT_SERVER INCREMENT PLAN UPDATE
171 CURRENT_TIME INDEX POSITION USAGE
172 CURRENT_TIMESTAMP INDICATOR PRECISION USER
173 CURRENT_TIMEZONE INHERIT PREPARE USING
174 CURRENT_USER INNER PRIMARY VALIDPROC
175 CURSOR INOUT PRIQTY VALUES
176 CYCLE INSENSITIVE PRIVILEGES VARIABLE
177 DATA INSERT PROCEDURE VARIANT
178 DATABASE INTEGRITY PROGRAM VCAT
180 DAYS IS QUERYNO VOLUMES
181 DB2GENERAL ISOBID READ WHEN
182 DB2GENRL ISOLATION READS WHERE
183 DB2SQL ITERATE RECOVERY WHILE
184 DBINFO JAR REFERENCES WITH
185 DECLARE JAVA REFERENCING WLM
186 DEFAULT JOIN RELEASE WRITE
187 DEFAULTS KEY RENAME YEAR
188 DEFINITION LABEL REPEAT YEARS
189 DELETE LANGUAGE RESET
190 DESCRIPTOR LC_CTYPE RESIGNAL
193 #------------------------------------------------------------------------------
197 my ($translator) = @_;
198 $DEBUG = $translator->debug;
199 $WARN = $translator->show_warnings;
200 my $no_comments = $translator->no_comments;
201 my $add_drop_table = $translator->add_drop_table;
202 my $schema = $translator->schema;
206 $output .= header_comment unless($no_comments);
207 my (@table_defs, @fks, @index_defs);
208 foreach my $table ($schema->get_tables)
210 push @table_defs, 'DROP TABLE ' . $table->name . ";" if $add_drop_table;
211 my ($table_def, $fks) = create_table($table, {
212 no_comments => $no_comments});
213 push @table_defs, $table_def;
216 foreach my $index ($table->get_indices)
218 push @index_defs, create_index($index);
223 foreach my $view ( $schema->get_views )
225 push @view_defs, create_view($view);
228 foreach my $trigger ( $schema->get_triggers )
230 push @trigger_defs, create_trigger($trigger);
233 return wantarray ? (@table_defs, @fks, @index_defs, @view_defs, @trigger_defs) :
234 $output . join("\n\n", @table_defs, @fks, @index_defs, @view_defs, @trigger_defs) . "\n";
241 my ($name, $type, $length) = @_;
244 if(length($name) > $length) ## Maximum table name length is 18
246 warn "Table name $name is longer than $length characters, truncated" if $WARN;
247 # if(grep {$_ eq substr($name, 0, $length) }
248 # values(%{$objnames{$type}}))
250 # die "Got multiple matching table names when truncated";
252 # $objnames{$type}{$name} = substr($name, 0,$length);
253 # $newname = $objnames{$type}{$name};
256 if($db2_reserved{uc($newname)})
258 warn "$newname is a reserved word in DB2!" if $WARN;
261 # return sprintf("%-*s", $length-5, $newname);
268 my ($table, $options) = @_;
270 my $table_name = check_name($table->name, 'tables', 128);
271 # this limit is 18 in older DB2s ! (<= 8)
273 my (@field_defs, @comments);
274 push @comments, "--\n-- Table: $table_name\n--" unless $options->{no_comments};
275 foreach my $field ($table->get_fields)
277 push @field_defs, create_field($field);
279 my (@con_defs, @fks);
280 foreach my $con ($table->get_constraints)
282 my ($cdefs, $fks) = create_constraint($con);
283 push @con_defs, @$cdefs;
287 my $tablespace = $table->extra()->{'TABLESPACE'} || '';
288 my $table_def = "CREATE TABLE $table_name (\n";
289 $table_def .= join (",\n", map { " $_" } @field_defs, @con_defs);
291 $table_def .= $tablespace ? "IN $tablespace;" : ';';
293 return $table_def, \@fks;
300 my $field_name = check_name($field->name, 'fields', 30);
302 # print Dumper(\%dt_translate);
303 # print $field->data_type, " ", $dt_translate{lc($field->data_type)}, "\n";
304 my $data_type = uc($dt_translate{lc($field->data_type)} || $field->data_type);
305 my $size = $field->size();
307 my $field_def = "$field_name $data_type";
308 $field_def .= $field->is_auto_increment ?
309 ' GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)' : '';
310 $field_def .= $data_type =~ /(CHAR|CLOB)/i ? "(${size})" : '';
311 $field_def .= !$field->is_nullable ? ' NOT NULL':'';
312 # $field_def .= $field->is_primary_key ? ' PRIMARY KEY':'';
313 $field_def .= !defined $field->default_value ? '' :
314 $field->default_value =~ /current( |_)timestamp/i ||
315 $field->default_value =~ /\Qnow()\E/i ?
316 ' DEFAULT CURRENT TIMESTAMP' : defined $field->default_value ?
317 (" DEFAULT " . ($data_type =~ /(INT|DOUBLE)/i ?
318 $field->default_value : "'" . $field->default_value . "'")
328 my $out = sprintf('CREATE %sINDEX %s ON %s ( %s );',
329 $index->type() =~ /^UNIQUE$/i ? 'UNIQUE' : '',
332 join(', ', $index->fields) );
337 sub create_constraint
339 my ($constraint) = @_;
341 my (@con_defs, @fks);
343 my $ctype = $constraint->type =~ /^PRIMARY(_|\s)KEY$/i ? 'PRIMARY KEY' :
344 $constraint->type =~ /^UNIQUE$/i ? 'UNIQUE' :
345 $constraint->type =~ /^CHECK_C$/i ? 'CHECK' :
346 $constraint->type =~ /^FOREIGN(_|\s)KEY$/i ? 'FOREIGN KEY' : '';
348 my $expr = $constraint->type =~ /^CHECK_C$/i ? $constraint->expression :
350 my $ref = $constraint->type =~ /^FOREIGN(_|\s)KEY$/i ? ('REFERENCES ' . $constraint->reference_table . '(' . join(', ', $constraint->reference_fields) . ')') : '';
351 my $update = $constraint->on_update ? $constraint->on_update : '';
352 my $delete = $constraint->on_delete ? $constraint->on_delete : '';
354 my $out = join(' ', grep { $_ }
355 $constraint->name ? ('CONSTRAINT ' . $constraint->name) : '',
357 '(' . join (', ', $constraint->fields) . ')',
358 $expr ? $expr : $ref,
361 if ($constraint->type eq FOREIGN_KEY) {
362 my $table_name = $constraint->table->name;
363 $out = "ALTER TABLE $table_name ADD $out;";
367 push @con_defs, $out;
370 return \@con_defs, \@fks;
378 my $out = sprintf("CREATE VIEW %s AS\n%s;",
388 # create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
390 my $out = sprintf('CREATE TRIGGER %s %s %s ON %s %s %s MODE DB2SQL %s',
392 $trigger->perform_action_when || 'AFTER',
393 $trigger->database_event =~ /update_on/i ?
394 ('UPDATE OF '. join(', ', $trigger->fields)) :
395 $trigger->database_event || 'UPDATE',
396 $trigger->table->name,
397 $trigger->extra->{reference} || 'REFERENCING OLD AS oldrow NEW AS newrow',
398 $trigger->extra->{granularity} || 'FOR EACH ROW',
407 my ($from_field, $to_field) = @_;
409 my $data_type = uc($dt_translate{lc($to_field->data_type)} || $to_field->data_type);
411 my $size = $to_field->size();
412 $data_type .= $data_type =~ /CHAR/i ? "(${size})" : '';
414 # DB2 will only allow changing of varchar/vargraphic datatypes
415 # to extend their lengths. Or changing of text types to other
416 # texttypes, and numeric types to larger numeric types. (v8)
417 # We can also drop/add keys, checks and constraints, but not
420 my $out = sprintf('ALTER TABLE %s ALTER %s SET DATATYPE %s',
421 $to_field->table->name,
429 my ($new_field) = @_;
431 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
432 $new_field->table->name,
433 create_field($new_field));