[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Producer / DB2.pm

package SQL::Translator::Producer::DB2;

# -------------------------------------------------------------------
# $Id: DB2.pm,v 1.1 2005-09-18 20:06:31 schiffbruechige Exp $
# -------------------------------------------------------------------
# Copyright (C) 2002-4 SQLFairy Authors
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; version 2.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
# 02111-1307  USA
# -------------------------------------------------------------------

=head1 NAME

SQL::Translator::Producer::DB2 - DB2 SQL producer

=head1 SYNOPSIS

  use SQL::Translator;

  my $t = SQL::Translator->new( parser => '...', producer => 'DB2' );
  print $translator->translate( $file );

=head1 DESCRIPTION

Creates an SQL DDL suitable for DB.

=cut

use strict;
use vars qw[ $VERSION $DEBUG $WARN ];
$VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/;
$DEBUG   = 0 unless defined $DEBUG;

use SQL::Translator::Schema::Constants;
use SQL::Translator::Utils qw(header_comment);


# http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0006844.htm

# This is a terrible WTDI, each Parser should parse down to some standard set
# of SQL data types, with field->extra entries being used to convert back to
# weird types like "polygon" if needed (IMO anyway)

my %translate  = (
    #
    # MySQL types
    #
    int        => 'integer',
    mediumint  => 'integer',
    tinyint    => 'smallint',
    char       => 'char',
    tinyblob   => 'blob',
    mediumblob => 'blob',
    longblob   => 'long varchar for bit data',
    tinytext   => 'varchar',
    text       => 'clob',
    longtext   => 'clob',
    mediumtext => 'clob',
    enum       => 'varchar',
    set        => 'varchar',
    date       => 'date',
    datetime   => 'timestamp',
    time       => 'time',
    year       => 'date',

    #
    # PostgreSQL types
    #
    'double precision'  => 'double',
    serial              => 'integer',
    bigserial           => 'integer',
    money               => 'double',
    character           => 'char',
    'character varying' => 'varchar',
    bytea               => 'BLOB',
    interval            => 'integer',
    boolean             => 'smallint',
    point               => 'integer',
    line                => 'integer',
    lseg                => 'integer',
    box                 => 'integer',
    path                => 'integer',
    polygon             => 'integer',
    circle              => 'integer',
    cidr                => 'integer',
    inet                => 'varchar',
    macaddr             => 'varchar',
    bit                 => 'number',
    'bit varying'       => 'number',

    #
    # DB types
    #
    number              => 'integer',
    varchar2            => 'varchar',
    long                => 'clob',
);

my %db2_reserved = map { $_ => 1} qw/
ADD                DETERMINISTIC  LEAVE         RESTART
AFTER              DISALLOW       LEFT          RESTRICT
ALIAS              DISCONNECT     LIKE          RESULT
ALL                DISTINCT       LINKTYPE      RESULT_SET_LOCATOR
ALLOCATE           DO             LOCAL         RETURN
ALLOW              DOUBLE         LOCALE        RETURNS
ALTER              DROP           LOCATOR       REVOKE
AND                DSNHATTR       LOCATORS      RIGHT
ANY                DSSIZE         LOCK          ROLLBACK
APPLICATION        DYNAMIC        LOCKMAX       ROUTINE
AS                 EACH           LOCKSIZE      ROW
ASSOCIATE          EDITPROC       LONG          ROWS
ASUTIME            ELSE           LOOP          RRN
AUDIT              ELSEIF         MAXVALUE      RUN
AUTHORIZATION      ENCODING       MICROSECOND   SAVEPOINT
AUX                END            MICROSECONDS  SCHEMA
AUXILIARY          END-EXEC       MINUTE        SCRATCHPAD
BEFORE             END-EXEC1      MINUTES       SECOND
BEGIN              ERASE          MINVALUE      SECONDS
BETWEEN            ESCAPE         MODE          SECQTY
BINARY             EXCEPT         MODIFIES      SECURITY
BUFFERPOOL         EXCEPTION      MONTH         SELECT
BY                 EXCLUDING      MONTHS        SENSITIVE
CACHE              EXECUTE        NEW           SET
CALL               EXISTS         NEW_TABLE     SIGNAL
CALLED             EXIT           NO            SIMPLE
CAPTURE            EXTERNAL       NOCACHE       SOME
CARDINALITY        FENCED         NOCYCLE       SOURCE
CASCADED           FETCH          NODENAME      SPECIFIC
CASE               FIELDPROC      NODENUMBER    SQL
CAST               FILE           NOMAXVALUE    SQLID
CCSID              FINAL          NOMINVALUE    STANDARD
CHAR               FOR            NOORDER       START
CHARACTER          FOREIGN        NOT           STATIC
CHECK              FREE           NULL          STAY
CLOSE              FROM           NULLS         STOGROUP
CLUSTER            FULL           NUMPARTS      STORES
COLLECTION         FUNCTION       OBID          STYLE
COLLID             GENERAL        OF            SUBPAGES
COLUMN             GENERATED      OLD           SUBSTRING
COMMENT            GET            OLD_TABLE     SYNONYM
COMMIT             GLOBAL         ON            SYSFUN
CONCAT             GO             OPEN          SYSIBM
CONDITION          GOTO           OPTIMIZATION  SYSPROC
CONNECT            GRANT          OPTIMIZE      SYSTEM
CONNECTION         GRAPHIC        OPTION        TABLE
CONSTRAINT         GROUP          OR            TABLESPACE
CONTAINS           HANDLER        ORDER         THEN
CONTINUE           HAVING         OUT           TO
COUNT              HOLD           OUTER         TRANSACTION
COUNT_BIG          HOUR           OVERRIDING    TRIGGER
CREATE             HOURS          PACKAGE       TRIM
CROSS              IDENTITY       PARAMETER     TYPE
CURRENT            IF             PART          UNDO
CURRENT_DATE       IMMEDIATE      PARTITION     UNION
CURRENT_LC_CTYPE   IN             PATH          UNIQUE
CURRENT_PATH       INCLUDING      PIECESIZE     UNTIL
CURRENT_SERVER     INCREMENT      PLAN          UPDATE
CURRENT_TIME       INDEX          POSITION      USAGE
CURRENT_TIMESTAMP  INDICATOR      PRECISION     USER
CURRENT_TIMEZONE   INHERIT        PREPARE       USING
CURRENT_USER       INNER          PRIMARY       VALIDPROC
CURSOR             INOUT          PRIQTY        VALUES
CYCLE              INSENSITIVE    PRIVILEGES    VARIABLE
DATA               INSERT         PROCEDURE     VARIANT
DATABASE           INTEGRITY      PROGRAM       VCAT
DAY                INTO           PSID          VIEW
DAYS               IS             QUERYNO       VOLUMES
DB2GENERAL         ISOBID         READ          WHEN
DB2GENRL           ISOLATION      READS         WHERE
DB2SQL             ITERATE        RECOVERY      WHILE
DBINFO             JAR            REFERENCES    WITH
DECLARE            JAVA           REFERENCING   WLM
DEFAULT            JOIN           RELEASE       WRITE
DEFAULTS           KEY            RENAME        YEAR
DEFINITION         LABEL          REPEAT        YEARS
DELETE             LANGUAGE       RESET
DESCRIPTOR         LC_CTYPE       RESIGNAL 
/;

#------------------------------------------------------------------------------

sub produce
{
    my ($translator) = @_;
    $DEBUG             = $translator->debug;
    $WARN              = $translator->show_warnings;
    my $no_comments    = $translator->no_comments;
    my $add_drop_table = $translator->add_drop_table;
    my $schema         = $translator->schema;
    my $output         = '';
    my $indent         = '    ';

    $output .= header_comment unless($no_comments);
    my (@table_defs);
    foreach my $table ($schema->get_tables)
    {
        my $table_name = check_name($table->name, 'tables', 18);

        my (@field_defs, @comments);
        push @comments, "--\n-- Table: $table_name\n--" unless $no_comments;
        foreach my $field ($table->get_fields)
        {
            my $field_name = check_name($field->name, 'fields', 30);
            my $data_type = uc($translate{lc($field->data_type)} || $field->data_type);
            my $size = $field->size();

            my $field_def = "$field_name $data_type";
            $field_def .= $field->is_auto_increment ? 
                ' GENERATED BY DEFAULT AS IDENTITY' : '';
            $field_def .= $data_type =~ /CHAR/i ? "(${size})" : '';
            $field_def .= !$field->is_nullable ? ' NOT NULL':'';
            $field_def .= $field->is_primary_key ? ' PRIMARY KEY':'';
            $field_def .= $field->default_value ? ' DEFAULT ' .  $field->default_value : '';

            push @field_defs, "${indent}${field_def}";
        }
        

        my $tablespace = $table->extra()->{'TABLESPACE'} || '';
        my $table_def = "CREATE TABLE $table_name (\n";
        $table_def .= join (",\n", @field_defs);
        $table_def .= "\n)";
        $table_def .= $tablespace ? "IN $tablespace;" : ';';
        
        push @table_defs, "DROP TABLE $table_name;\n" if $add_drop_table;
        push @table_defs, $table_def;
    }   

    $output .= join("\n\n", @table_defs);
}

{ my %objnames;

    sub check_name
    {
        my ($name, $type, $length) = @_;

        my $newname = $name;
        if(length($name) > $length)   ## Maximum table name length is 18
        {
            warn "Table name $name is longer than $length characters, truncated" if $WARN;
            if(grep {$_ eq substr($name, 0, $length) } 
                              values(%{$objnames{$type}}))
            {
                die "Got multiple matching table names when truncated";
            }
            $objnames{$type}{$name} = substr($name, 0,$length);
            $newname = $objnames{$type}{$name};
        }

        if($db2_reserved{uc($newname)})
        {
            warn "$newname is a reserved word in DB2!" if $WARN;
        }

        return sprintf("%-*s", $length-5, $newname);
    }
}

1;
Commit	Line	Data
04bc93b6	1	package SQL::Translator::Producer::DB2;
	2
	3	# -------------------------------------------------------------------
	4	# $Id: DB2.pm,v 1.1 2005-09-18 20:06:31 schiffbruechige Exp $
	5	# -------------------------------------------------------------------
	6	# Copyright (C) 2002-4 SQLFairy Authors
	7	#
	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.
	11	#
	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.
	16	#
	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
	20	# 02111-1307 USA
	21	# -------------------------------------------------------------------
	22
	23	=head1 NAME
	24
	25	SQL::Translator::Producer::DB2 - DB2 SQL producer
	26
	27	=head1 SYNOPSIS
	28
	29	use SQL::Translator;
	30
	31	my $t = SQL::Translator->new( parser => '...', producer => 'DB2' );
	32	print $translator->translate( $file );
	33
	34	=head1 DESCRIPTION
	35
	36	Creates an SQL DDL suitable for DB.
	37
	38	=cut
	39
	40	use strict;
	41	use vars qw[ $VERSION $DEBUG $WARN ];
	42	$VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/;
	43	$DEBUG = 0 unless defined $DEBUG;
	44
	45	use SQL::Translator::Schema::Constants;
	46	use SQL::Translator::Utils qw(header_comment);
	47
	48
	49	# http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0006844.htm
	50
	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)
	54
	55	my %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 => 'clob',
68	longtext => 'clob',
69	mediumtext => 'clob',
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	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
121	AS EACH LOCKSIZE ROW
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
145	CHECK FREE NULL STAY
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
164	CURRENT IF PART UNDO
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
177	DAY INTO PSID VIEW
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
189	/;
190
191	#------------------------------------------------------------------------------
192
193	sub produce
194	{
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;
201	my $output = '';
202	my $indent = ' ';
203
204	$output .= header_comment unless($no_comments);
205	my (@table_defs);
206	foreach my $table ($schema->get_tables)
207	{
208	my $table_name = check_name($table->name, 'tables', 18);
209
210	my (@field_defs, @comments);
211	push @comments, "--\n-- Table: $table_name\n--" unless $no_comments;
212	foreach my $field ($table->get_fields)
213	{
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();
217
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 : '';
225
226	push @field_defs, "${indent}${field_def}";
227	}
228
229
230	my $tablespace = $table->extra()->{'TABLESPACE'} \|\| '';
231	my $table_def = "CREATE TABLE $table_name (\n";
232	$table_def .= join (",\n", @field_defs);
233	$table_def .= "\n)";
234	$table_def .= $tablespace ? "IN $tablespace;" : ';';
235
236	push @table_defs, "DROP TABLE $table_name;\n" if $add_drop_table;
237	push @table_defs, $table_def;
238	}
239
240	$output .= join("\n\n", @table_defs);
241	}
242
243	{ my %objnames;
244
245	sub check_name
246	{
247	my ($name, $type, $length) = @_;
248
249	my $newname = $name;
250	if(length($name) > $length) ## Maximum table name length is 18
251	{
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}}))
255	{
256	die "Got multiple matching table names when truncated";
257	}
258	$objnames{$type}{$name} = substr($name, 0,$length);
259	$newname = $objnames{$type}{$name};
260	}
261
262	if($db2_reserved{uc($newname)})
263	{
264	warn "$newname is a reserved word in DB2!" if $WARN;
265	}
266
267	return sprintf("%-*s", $length-5, $newname);
268	}
269	}
270
271	1;