1 package SQL::Translator::Producer::DB2;
3 # -------------------------------------------------------------------
4 # $Id: DB2.pm,v 1.1 2005-09-18 20:06:31 schiffbruechige Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-4 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 # -------------------------------------------------------------------
25 SQL::Translator::Producer::DB2 - DB2 SQL producer
31 my $t = SQL::Translator->new( parser => '...', producer => 'DB2' );
32 print $translator->translate( $file );
36 Creates an SQL DDL suitable for DB.
41 use vars qw[ $VERSION $DEBUG $WARN ];
42 $VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/;
43 $DEBUG = 0 unless defined $DEBUG;
45 use SQL::Translator::Schema::Constants;
46 use SQL::Translator::Utils qw(header_comment);
49 # http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0006844.htm
51 # This is a terrible WTDI, each Parser should parse down to some standard set
52 # of SQL data types, with field->extra entries being used to convert back to
53 # weird types like "polygon" if needed (IMO anyway)
60 mediumint => 'integer',
61 tinyint => 'smallint',
65 longblob => 'long varchar for bit data',
66 tinytext => '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',
110 my %db2_reserved = map { $_ => 1} qw/
111 ADD DETERMINISTIC LEAVE RESTART
112 AFTER DISALLOW LEFT RESTRICT
113 ALIAS DISCONNECT LIKE RESULT
114 ALL DISTINCT LINKTYPE RESULT_SET_LOCATOR
115 ALLOCATE DO LOCAL RETURN
116 ALLOW DOUBLE LOCALE RETURNS
117 ALTER DROP LOCATOR REVOKE
118 AND DSNHATTR LOCATORS RIGHT
119 ANY DSSIZE LOCK ROLLBACK
120 APPLICATION DYNAMIC LOCKMAX ROUTINE
122 ASSOCIATE EDITPROC LONG ROWS
123 ASUTIME ELSE LOOP RRN
124 AUDIT ELSEIF MAXVALUE RUN
125 AUTHORIZATION ENCODING MICROSECOND SAVEPOINT
126 AUX END MICROSECONDS SCHEMA
127 AUXILIARY END-EXEC MINUTE SCRATCHPAD
128 BEFORE END-EXEC1 MINUTES SECOND
129 BEGIN ERASE MINVALUE SECONDS
130 BETWEEN ESCAPE MODE SECQTY
131 BINARY EXCEPT MODIFIES SECURITY
132 BUFFERPOOL EXCEPTION MONTH SELECT
133 BY EXCLUDING MONTHS SENSITIVE
134 CACHE EXECUTE NEW SET
135 CALL EXISTS NEW_TABLE SIGNAL
136 CALLED EXIT NO SIMPLE
137 CAPTURE EXTERNAL NOCACHE SOME
138 CARDINALITY FENCED NOCYCLE SOURCE
139 CASCADED FETCH NODENAME SPECIFIC
140 CASE FIELDPROC NODENUMBER SQL
141 CAST FILE NOMAXVALUE SQLID
142 CCSID FINAL NOMINVALUE STANDARD
143 CHAR FOR NOORDER START
144 CHARACTER FOREIGN NOT STATIC
146 CLOSE FROM NULLS STOGROUP
147 CLUSTER FULL NUMPARTS STORES
148 COLLECTION FUNCTION OBID STYLE
149 COLLID GENERAL OF SUBPAGES
150 COLUMN GENERATED OLD SUBSTRING
151 COMMENT GET OLD_TABLE SYNONYM
152 COMMIT GLOBAL ON SYSFUN
153 CONCAT GO OPEN SYSIBM
154 CONDITION GOTO OPTIMIZATION SYSPROC
155 CONNECT GRANT OPTIMIZE SYSTEM
156 CONNECTION GRAPHIC OPTION TABLE
157 CONSTRAINT GROUP OR TABLESPACE
158 CONTAINS HANDLER ORDER THEN
159 CONTINUE HAVING OUT TO
160 COUNT HOLD OUTER TRANSACTION
161 COUNT_BIG HOUR OVERRIDING TRIGGER
162 CREATE HOURS PACKAGE TRIM
163 CROSS IDENTITY PARAMETER TYPE
165 CURRENT_DATE IMMEDIATE PARTITION UNION
166 CURRENT_LC_CTYPE IN PATH UNIQUE
167 CURRENT_PATH INCLUDING PIECESIZE UNTIL
168 CURRENT_SERVER INCREMENT PLAN UPDATE
169 CURRENT_TIME INDEX POSITION USAGE
170 CURRENT_TIMESTAMP INDICATOR PRECISION USER
171 CURRENT_TIMEZONE INHERIT PREPARE USING
172 CURRENT_USER INNER PRIMARY VALIDPROC
173 CURSOR INOUT PRIQTY VALUES
174 CYCLE INSENSITIVE PRIVILEGES VARIABLE
175 DATA INSERT PROCEDURE VARIANT
176 DATABASE INTEGRITY PROGRAM VCAT
178 DAYS IS QUERYNO VOLUMES
179 DB2GENERAL ISOBID READ WHEN
180 DB2GENRL ISOLATION READS WHERE
181 DB2SQL ITERATE RECOVERY WHILE
182 DBINFO JAR REFERENCES WITH
183 DECLARE JAVA REFERENCING WLM
184 DEFAULT JOIN RELEASE WRITE
185 DEFAULTS KEY RENAME YEAR
186 DEFINITION LABEL REPEAT YEARS
187 DELETE LANGUAGE RESET
188 DESCRIPTOR LC_CTYPE RESIGNAL
191 #------------------------------------------------------------------------------
195 my ($translator) = @_;
196 $DEBUG = $translator->debug;
197 $WARN = $translator->show_warnings;
198 my $no_comments = $translator->no_comments;
199 my $add_drop_table = $translator->add_drop_table;
200 my $schema = $translator->schema;
204 $output .= header_comment unless($no_comments);
206 foreach my $table ($schema->get_tables)
208 my $table_name = check_name($table->name, 'tables', 18);
210 my (@field_defs, @comments);
211 push @comments, "--\n-- Table: $table_name\n--" unless $no_comments;
212 foreach my $field ($table->get_fields)
214 my $field_name = check_name($field->name, 'fields', 30);
215 my $data_type = uc($translate{lc($field->data_type)} || $field->data_type);
216 my $size = $field->size();
218 my $field_def = "$field_name $data_type";
219 $field_def .= $field->is_auto_increment ?
220 ' GENERATED BY DEFAULT AS IDENTITY' : '';
221 $field_def .= $data_type =~ /CHAR/i ? "(${size})" : '';
222 $field_def .= !$field->is_nullable ? ' NOT NULL':'';
223 $field_def .= $field->is_primary_key ? ' PRIMARY KEY':'';
224 $field_def .= $field->default_value ? ' DEFAULT ' . $field->default_value : '';
226 push @field_defs, "${indent}${field_def}";
230 my $tablespace = $table->extra()->{'TABLESPACE'} || '';
231 my $table_def = "CREATE TABLE $table_name (\n";
232 $table_def .= join (",\n", @field_defs);
234 $table_def .= $tablespace ? "IN $tablespace;" : ';';
236 push @table_defs, "DROP TABLE $table_name;\n" if $add_drop_table;
237 push @table_defs, $table_def;
240 $output .= join("\n\n", @table_defs);
247 my ($name, $type, $length) = @_;
250 if(length($name) > $length) ## Maximum table name length is 18
252 warn "Table name $name is longer than $length characters, truncated" if $WARN;
253 if(grep {$_ eq substr($name, 0, $length) }
254 values(%{$objnames{$type}}))
256 die "Got multiple matching table names when truncated";
258 $objnames{$type}{$name} = substr($name, 0,$length);
259 $newname = $objnames{$type}{$name};
262 if($db2_reserved{uc($newname)})
264 warn "$newname is a reserved word in DB2!" if $WARN;
267 return sprintf("%-*s", $length-5, $newname);