1 package SQL::Translator::Producer::DB2;
3 # -------------------------------------------------------------------
4 # Copyright (C) 2002-2009 SQLFairy Authors
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.
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.
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
19 # -------------------------------------------------------------------
22 SQL::Translator::Producer::DB2 - DB2 SQL producer
28 my $t = SQL::Translator->new( parser => '...', producer => 'DB2' );
29 print $translator->translate( $file );
33 Creates an SQL DDL suitable for DB2.
39 use vars qw[ $VERSION $DEBUG $WARN ];
41 $DEBUG = 0 unless defined $DEBUG;
43 use SQL::Translator::Schema::Constants;
44 use SQL::Translator::Utils qw(header_comment);
47 # http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0006844.htm
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)
60 mediumint => 'integer',
61 tinyint => 'smallint',
65 longblob => 'long varchar for bit data',
66 tinytext => 'varchar',
68 longtext => 'varchar',
69 mediumtext => 'varchar',
73 datetime => 'timestamp',
80 'double precision' => 'double',
82 bigserial => 'integer',
85 'character varying' => 'varchar',
87 interval => 'integer',
88 boolean => 'smallint',
100 'bit varying' => 'number',
106 varchar2 => 'varchar',
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
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
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
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
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
192 #------------------------------------------------------------------------------
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;
205 $output .= header_comment unless($no_comments);
206 my (@table_defs, @fks, @index_defs);
207 foreach my $table ($schema->get_tables)
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;
215 foreach my $index ($table->get_indices)
217 push @index_defs, create_index($index);
222 foreach my $view ( $schema->get_views )
224 push @view_defs, create_view($view);
227 foreach my $trigger ( $schema->get_triggers )
229 push @trigger_defs, create_trigger($trigger);
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";
240 my ($name, $type, $length) = @_;
243 if(length($name) > $length) ## Maximum table name length is 18
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}}))
249 # die "Got multiple matching table names when truncated";
251 # $objnames{$type}{$name} = substr($name, 0,$length);
252 # $newname = $objnames{$type}{$name};
255 if($db2_reserved{uc($newname)})
257 warn "$newname is a reserved word in DB2!" if $WARN;
260 # return sprintf("%-*s", $length-5, $newname);
267 my ($table, $options) = @_;
269 my $table_name = check_name($table->name, 'tables', 128);
270 # this limit is 18 in older DB2s ! (<= 8)
272 my (@field_defs, @comments);
273 push @comments, "--\n-- Table: $table_name\n--" unless $options->{no_comments};
274 foreach my $field ($table->get_fields)
276 push @field_defs, create_field($field);
278 my (@con_defs, @fks);
279 foreach my $con ($table->get_constraints)
281 my ($cdefs, $fks) = create_constraint($con);
282 push @con_defs, @$cdefs;
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);
290 $table_def .= $tablespace ? "IN $tablespace;" : ';';
292 return $table_def, \@fks;
299 my $field_name = check_name($field->name, 'fields', 30);
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();
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 . "'")
327 my $out = sprintf('CREATE %sINDEX %s ON %s ( %s );',
328 $index->type() =~ /^UNIQUE$/i ? 'UNIQUE' : '',
331 join(', ', $index->fields) );
336 sub create_constraint
338 my ($constraint) = @_;
340 my (@con_defs, @fks);
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' : '';
347 my $expr = $constraint->type =~ /^CHECK_C$/i ? $constraint->expression :
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 : '';
353 my $out = join(' ', grep { $_ }
354 $constraint->name ? ('CONSTRAINT ' . $constraint->name) : '',
356 '(' . join (', ', $constraint->fields) . ')',
357 $expr ? $expr : $ref,
360 if ($constraint->type eq FOREIGN_KEY) {
361 my $table_name = $constraint->table->name;
362 $out = "ALTER TABLE $table_name ADD $out;";
366 push @con_defs, $out;
369 return \@con_defs, \@fks;
377 my $out = sprintf("CREATE VIEW %s AS\n%s;",
387 # create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
389 my $db_events = join ', ', $trigger->database_events;
390 my $out = sprintf('CREATE TRIGGER %s %s %s ON %s %s %s MODE DB2SQL %s',
392 $trigger->perform_action_when || 'AFTER',
393 $db_events =~ /update_on/i ?
394 ('UPDATE OF '. join(', ', $trigger->fields)) :
395 $db_events || '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));