1 package SQL::Translator::Producer::Oracle;
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 # -------------------------------------------------------------------
25 SQL::Translator::Producer::Oracle - Oracle SQL producer
31 my $t = SQL::Translator->new( parser => '...', producer => 'Oracle' );
32 print $translator->translate( $file );
36 Creates an SQL DDL suitable for Oracle.
42 =item delay_constraints
44 This option remove the primary key and other key constraints from the
45 CREATE TABLE statement and adds ALTER TABLEs at the end with it.
51 =head2 Autoincremental primary keys
53 This producer uses sequences and triggers to autoincrement primary key
54 columns, if necessary. SQLPlus and DBI expect a slightly different syntax
55 of CREATE TRIGGER statement. You might have noticed that this
56 producer returns a scalar containing all statements concatenated by
57 newlines or an array of single statements depending on the context
58 (scalar, array) it has been called in.
60 SQLPlus expects following trigger syntax:
62 CREATE OR REPLACE TRIGGER ai_person_id
63 BEFORE INSERT ON person
65 new.id IS NULL OR new.id = 0
68 SELECT sq_person_id.nextval
74 Whereas if you want to create the same trigger using L<DBI/do>, you need
75 to omit the last slash:
77 my $dbh = DBI->connect('dbi:Oracle:mysid', 'scott', 'tiger');
79 CREATE OR REPLACE TRIGGER ai_person_id
80 BEFORE INSERT ON person
82 new.id IS NULL OR new.id = 0
85 SELECT sq_person_id.nextval
91 If you call this producer in array context, we expect you want to process
92 the returned array of statements using L<DBI> like
93 L<DBIx::Class::Schema/deploy> does.
95 To get this working we removed the slash in those statements in version
96 0.09002 of L<SQL::Translator> when called in array context. In scalar
97 context the slash will be still there to ensure compatibility with SQLPlus.
102 use vars qw[ $DEBUG $WARN ];
103 $DEBUG = 0 unless defined $DEBUG;
105 use SQL::Translator::Schema::Constants;
106 use SQL::Translator::Utils qw(header_comment);
118 mediumint => 'number',
119 smallint => 'number',
122 varchar => 'varchar2',
125 mediumblob => 'blob',
127 tinytext => 'varchar2',
130 mediumtext => 'clob',
143 'double precision' => 'number',
145 bigserial => 'number',
148 'character varying' => 'varchar2',
150 interval => 'number',
161 macaddr => 'varchar2',
163 'bit varying' => 'number',
169 varchar2 => 'varchar2',
174 # Oracle reserved words from:
175 # http://technet.oracle.com/docs/products/oracle8i/doc_library/\
176 # 817_doc/server.817/a85397/ap_keywd.htm
178 my %ora_reserved = map { $_, 1 } qw(
179 ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT
181 CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT
182 DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP
183 ELSE EXCLUSIVE EXISTS
187 IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT
188 INTEGER INTERSECT INTO IS
190 MAXEXTENTS MINUS MLSLABEL MODE MODIFY
191 NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER
192 OF OFFLINE ON ONLINE OPTION OR ORDER
193 PCTFREE PRIOR PRIVILEGES PUBLIC
194 RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS
195 SELECT SESSION SET SHARE SIZE SMALLINT START
196 SUCCESSFUL SYNONYM SYSDATE
197 TABLE THEN TO TRIGGER
198 UID UNION UNIQUE UPDATE USER
199 VALIDATE VALUES VARCHAR VARCHAR2 VIEW
204 # Oracle 8/9 max size of data types from:
205 # http://www.ss64.com/orasyntax/datatypes.html
211 number => [ 38, 127 ],
213 varchar => 4000, # only synonym for varchar2
217 my $max_id_length = 30;
218 my %used_identifiers = ();
223 # -------------------------------------------------------------------
225 my $translator = shift;
226 $DEBUG = $translator->debug;
227 $WARN = $translator->show_warnings || 0;
228 my $no_comments = $translator->no_comments;
229 my $add_drop_table = $translator->add_drop_table;
230 my $schema = $translator->schema;
231 my $delay_constraints = $translator->producer_args->{delay_constraints};
232 my ($output, $create, @table_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs);
234 $create .= header_comment unless ($no_comments);
236 if ( $translator->parser_type =~ /mysql/i ) {
238 "-- We assume that default NLS_DATE_FORMAT has been changed\n".
239 "-- but we set it here anyway to be self-consistent.\n"
243 "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';\n\n";
246 for my $table ( $schema->get_tables ) {
247 my ( $table_def, $fk_def, $trigger_def, $index_def, $constraint_def ) = create_table(
250 add_drop_table => $add_drop_table,
251 show_warnings => $WARN,
252 no_comments => $no_comments,
253 delay_constraints => $delay_constraints,
254 wantarray => wantarray ? 1 : 0,
257 push @table_defs, @$table_def;
258 push @fk_defs, @$fk_def;
259 push @trigger_defs, @$trigger_def;
260 push @index_defs, @$index_def;
261 push @constraint_defs, @$constraint_def;
265 foreach my $view ( $schema->get_views ) {
266 push @view_defs, create_view($view);
270 return defined $create ? $create : (), @table_defs, @view_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs;
273 $create .= join ('', map { $_ ? "$_;\n\n" : () } @table_defs, @view_defs, @fk_defs, @index_defs, @constraint_defs);
274 # triggers may NOT end with a semicolon
275 $create .= join "\n\n", @trigger_defs;
281 my ($table, $options) = @_;
282 my $table_name = $table->name;
286 my (@create, @field_defs, @constraint_defs, @fk_defs, @trigger_defs);
288 my $table_name_ur = unreserve($table_name) or next;
290 push @create, "--\n-- Table: $table_name_ur\n--" unless $options->{no_comments};
291 push @create, qq[DROP TABLE $table_name_ur CASCADE CONSTRAINTS] if $options->{add_drop_table};
293 my ( %field_name_scope, @field_comments );
294 for my $field ( $table->get_fields ) {
295 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
296 create_field($field, $options, \%field_name_scope);
297 push @create, @$field_create if ref $field_create;
298 push @field_defs, @$field_defs if ref $field_defs;
299 push @trigger_defs, @$trigger_defs if ref $trigger_defs;
300 push @field_comments, @$field_comments if ref $field_comments;
307 for my $opt ( $table->options ) {
308 if ( ref $opt eq 'HASH' ) {
309 my ( $key, $value ) = each %$opt;
310 if ( ref $value eq 'ARRAY' ) {
311 push @table_options, "$key\n(\n". join ("\n",
312 map { " $_->[0]\t$_->[1]" }
317 elsif ( !defined $value ) {
318 push @table_options, $key;
321 push @table_options, "$key $value";
329 for my $c ( $table->get_constraints ) {
330 my $name = $c->name || '';
331 my @fields = map { unreserve( $_, $table_name ) } $c->fields;
332 my @rfields = map { unreserve( $_, $table_name ) }
333 $c->reference_fields;
334 next if !@fields && $c->type ne CHECK_C;
336 if ( $c->type eq PRIMARY_KEY ) {
337 # create a name if delay_constraints
338 $name ||= mk_name( $table_name, 'pk' )
339 if $options->{delay_constraints};
340 push @constraint_defs, ($name ? "CONSTRAINT $name " : '') .
341 'PRIMARY KEY (' . join( ', ', @fields ) . ')';
343 elsif ( $c->type eq UNIQUE ) {
344 # Don't create UNIQUE constraints identical to the primary key
345 if ( my $pk = $table->primary_key ) {
346 my $u_fields = join(":", @fields);
347 my $pk_fields = join(":", $pk->fields);
348 next if $u_fields eq $pk_fields;
351 $name ||= mk_name( $name || $table_name, 'u' );
353 for my $f ( $c->fields ) {
354 my $field_def = $table->get_field( $f ) or next;
355 my $dtype = $translate{ $field_def->data_type } or next;
356 if ( $WARN && $dtype =~ /clob/i ) {
357 warn "Oracle will not allow UNIQUE constraints on " .
358 "CLOB field '" . $field_def->table->name . '.' .
359 $field_def->name . ".'\n"
363 push @constraint_defs, "CONSTRAINT $name UNIQUE " .
364 '(' . join( ', ', @fields ) . ')';
366 elsif ( $c->type eq CHECK_C ) {
367 $name ||= mk_name( $name || $table_name, 'ck' );
368 my $expression = $c->expression || '';
369 push @constraint_defs, "CONSTRAINT $name CHECK ($expression)";
371 elsif ( $c->type eq FOREIGN_KEY ) {
372 $name = mk_name( join('_', $table_name, $c->fields). '_fk' );
373 my $def = "CONSTRAINT $name FOREIGN KEY ";
376 $def .= '(' . join( ', ', @fields ) . ')';
379 my $ref_table = unreserve($c->reference_table);
381 $def .= " REFERENCES $ref_table";
384 $def .= ' (' . join( ', ', @rfields ) . ')';
387 if ( $c->match_type ) {
389 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
392 if ( $c->on_delete ) {
393 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
396 # disabled by plu 2007-12-29 - doesn't exist for oracle
397 #if ( $c->on_update ) {
398 # $def .= ' ON UPDATE '.join( ' ', $c->on_update );
401 push @fk_defs, sprintf("ALTER TABLE %s ADD %s", $table_name_ur, $def);
409 for my $index ( $table->get_indices ) {
410 my $index_name = $index->name || '';
411 my $index_type = $index->type || NORMAL;
412 my @fields = map { unreserve( $_, $table_name ) }
417 for my $opt ( $index->options ) {
418 if ( ref $opt eq 'HASH' ) {
419 my ( $key, $value ) = each %$opt;
420 if ( ref $value eq 'ARRAY' ) {
421 push @table_options, "$key\n(\n". join ("\n",
422 map { " $_->[0]\t$_->[1]" }
427 elsif ( !defined $value ) {
428 push @index_options, $key;
431 push @index_options, "$key $value";
435 my $index_options = @index_options
436 ? "\n".join("\n", @index_options) : '';
438 if ( $index_type eq PRIMARY_KEY ) {
439 $index_name = $index_name ? mk_name( $index_name )
440 : mk_name( $table_name, 'pk' );
441 push @field_defs, 'CONSTRAINT '.$index_name.' PRIMARY KEY '.
442 '(' . join( ', ', @fields ) . ')';
444 elsif ( $index_type eq NORMAL ) {
445 $index_name = $index_name ? mk_name( $index_name )
446 : mk_name( $table_name, $index_name || 'i' );
448 "CREATE INDEX $index_name on $table_name_ur (".
449 join( ', ', @fields ).
452 elsif ( $index_type eq UNIQUE ) {
453 $index_name = $index_name ? mk_name( $index_name )
454 : mk_name( $table_name, $index_name || 'i' );
456 "CREATE UNIQUE INDEX $index_name on $table_name_ur (".
457 join( ', ', @fields ).
461 warn "Unknown index type ($index_type) on table $table_name.\n"
466 if ( my @table_comments = $table->comments ) {
467 for my $comment ( @table_comments ) {
468 next unless $comment;
469 $comment =~ s/'/''/g;
470 push @field_comments, "COMMENT ON TABLE $table_name_ur is\n '".
471 $comment . "'" unless $options->{no_comments}
476 my $table_options = @table_options
477 ? "\n".join("\n", @table_options) : '';
478 push @create, "CREATE TABLE $table_name_ur (\n" .
479 join( ",\n", map { " $_" } @field_defs,
480 ($options->{delay_constraints} ? () : @constraint_defs) ) .
483 @constraint_defs = map { 'ALTER TABLE '.$table_name_ur.' ADD '.$_ }
488 warn "Truncated " . keys( %truncated ) . " names:\n";
489 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
493 warn "Encounted " . keys( %unreserve ) .
494 " unsafe names in schema (reserved or invalid):\n";
495 warn "\t" . join( "\n\t", sort keys %unreserve ) . "\n";
499 return \@create, \@fk_defs, \@trigger_defs, \@index_defs, ($options->{delay_constraints} ? \@constraint_defs : []);
503 my ($from_field, $to_field, $options) = @_;
505 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
506 create_field($to_field, $options, {});
509 if ($to_field->is_nullable && !$from_field->is_nullable) {
510 die 'Cannot remove NOT NULL from table field';
511 } elsif (!$from_field->is_nullable && !$to_field->is_nullable) {
512 @$field_defs = map { s/ NOT NULL//; $_} @$field_defs;
515 my $table_name = $to_field->table->name;
516 my $table_name_ur = unreserve( $table_name );
518 return 'ALTER TABLE '.$table_name_ur.' MODIFY ( '.join('', @$field_defs).' )';
522 my ($new_field, $options) = @_;
524 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
525 create_field($new_field, $options, {});
527 my $table_name = $new_field->table->name;
528 my $table_name_ur = unreserve( $table_name );
530 my $out = sprintf('ALTER TABLE %s ADD ( %s )',
532 join('', @$field_defs));
537 my ($field, $options, $field_name_scope) = @_;
539 my (@create, @field_defs, @trigger_defs, @field_comments);
541 my $table_name = $field->table->name;
542 my $table_name_ur = unreserve( $table_name );
547 my $field_name = mk_name(
548 $field->name, '', $field_name_scope, 1
551 my $field_name_ur = unreserve( $field_name, $table_name );
552 my $field_def = $field_name_ur;
553 $field->name( $field_name_ur );
559 my $data_type = lc $field->data_type;
560 my @size = $field->size;
561 my %extra = $field->extra;
562 my $list = $extra{'list'} || [];
563 # \todo deal with embedded quotes
564 my $commalist = join( ', ', map { qq['$_'] } @$list );
566 if ( $data_type eq 'enum' ) {
567 $check = "CHECK ($field_name_ur IN ($commalist))";
568 $data_type = 'varchar2';
570 elsif ( $data_type eq 'set' ) {
571 # XXX add a CHECK constraint maybe
572 # (trickier and slower, than enum :)
573 $data_type = 'varchar2';
576 $data_type = defined $translate{ $data_type } ?
577 $translate{ $data_type } :
579 $data_type ||= 'varchar2';
582 # ensure size is not bigger than max size oracle allows for data type
583 if ( defined $max_size{$data_type} ) {
584 for ( my $i = 0 ; $i < scalar @size ; $i++ ) {
586 ref( $max_size{$data_type} ) eq 'ARRAY'
587 ? $max_size{$data_type}->[$i]
588 : $max_size{$data_type};
589 $size[$i] = $max if $size[$i] > $max;
594 # Fixes ORA-02329: column of datatype LOB cannot be
595 # unique or a primary key
597 if ( $data_type eq 'clob' && $field->is_primary_key ) {
598 $data_type = 'varchar2';
600 warn "CLOB cannot be a primary key, changing to VARCHAR2\n"
604 if ( $data_type eq 'clob' && $field->is_unique ) {
605 $data_type = 'varchar2';
607 warn "CLOB cannot be a unique key, changing to VARCHAR2\n"
612 # Fixes ORA-00907: missing right parenthesis
614 if ( $data_type =~ /(date|clob)/i ) {
618 $field_def .= " $data_type";
619 if ( defined $size[0] && $size[0] > 0 ) {
620 $field_def .= '(' . join( ', ', @size ) . ')';
626 my $default = $field->default_value;
627 if ( defined $default ) {
629 # Wherein we try to catch a string being used as
630 # a default value for a numerical field. If "true/false,"
631 # then sub "1/0," otherwise just test the truthity of the
632 # argument and use that (naive?).
634 if (ref $default and defined $$default) {
635 $default = $$default;
636 } elsif (ref $default) {
639 $data_type =~ /^number$/i &&
640 $default !~ /^-?\d+$/ &&
643 if ( $default =~ /^true$/i ) {
645 } elsif ( $default =~ /^false$/i ) {
648 $default = $default ? "'1'" : "'0'";
651 $data_type =~ /date/ && (
652 $default eq 'current_timestamp'
657 $default = 'SYSDATE';
659 $default = $default =~ m/null/i ? 'NULL' : "'$default'"
662 $field_def .= " DEFAULT $default",
666 # Not null constraint
668 unless ( $field->is_nullable ) {
669 $field_def .= ' NOT NULL';
672 $field_def .= " $check" if $check;
677 if ( $field->is_auto_increment ) {
678 my $base_name = $table_name_ur . "_". $field_name;
679 my $seq_name = mk_name( $base_name, 'sq' );
680 my $trigger_name = mk_name( $base_name, 'ai' );
682 push @create, qq[DROP SEQUENCE $seq_name] if $options->{add_drop_table};
683 push @create, "CREATE SEQUENCE $seq_name";
685 "CREATE OR REPLACE TRIGGER $trigger_name\n" .
686 "BEFORE INSERT ON $table_name_ur\n" .
687 "FOR EACH ROW WHEN (\n" .
688 " new.$field_name_ur IS NULL".
689 " OR new.$field_name_ur = 0\n".
692 " SELECT $seq_name.nextval\n" .
693 " INTO :new." . $field->name."\n" .
698 # If wantarray is set we have to omit the last "/" in this statement so it
699 # can be executed by DBI->do() directly.
701 $trigger .= "/" unless $options->{wantarray};
703 push @trigger_defs, $trigger;
706 if ( lc $field->data_type eq 'timestamp' ) {
707 my $base_name = $table_name_ur . "_". $field_name_ur;
708 my $trig_name = mk_name( $base_name, 'ts' );
710 "CREATE OR REPLACE TRIGGER $trig_name\n".
711 "BEFORE INSERT OR UPDATE ON $table_name_ur\n".
712 "FOR EACH ROW WHEN (new.$field_name_ur IS NULL)\n".
714 " SELECT sysdate INTO :new.$field_name_ur FROM dual;\n".
718 # If wantarray is set we have to omit the last "/" in this statement so it
719 # can be executed by DBI->do() directly.
721 $trigger .= "/" unless $options->{wantarray};
723 push @trigger_defs, $trigger;
726 push @field_defs, $field_def;
728 if ( my $comment = $field->comments ) {
729 $comment =~ s/'/''/g;
730 push @field_comments,
731 "COMMENT ON COLUMN $table_name_ur.$field_name_ur is\n '" .
732 $comment . "';" unless $options->{no_comments};
735 return \@create, \@field_defs, \@trigger_defs, \@field_comments;
743 my $out = sprintf("CREATE VIEW %s AS\n%s",
750 # -------------------------------------------------------------------
752 my $basename = shift || '';
753 my $type = shift || '';
754 $type = '' if $type =~ /^\d/;
755 my $scope = shift || '';
756 my $critical = shift || '';
757 my $basename_orig = $basename;
759 ? $max_id_length - (length($type) + 1)
761 $basename = substr( $basename, 0, $max_name )
762 if length( $basename ) > $max_name;
763 my $name = $type ? "${type}_$basename" : $basename;
765 if ( $basename ne $basename_orig and $critical ) {
766 my $show_type = $type ? "+'$type'" : "";
767 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
768 "character limit to make '$name'\n" if $WARN;
769 $truncated{ $basename_orig } = $name;
772 $scope ||= \%global_names;
773 if ( my $prev = $scope->{ $name } ) {
774 my $name_orig = $name;
775 substr($name, $max_id_length - 2) = ""
776 if length( $name ) >= $max_id_length - 1;
777 $name .= sprintf( "%02d", $prev++ );
779 warn "The name '$name_orig' has been changed to ",
780 "'$name' to make it unique.\n" if $WARN;
782 $scope->{ $name_orig }++;
789 # -------------------------------------------------------------------
791 my $name = shift || '';
792 my $schema_obj_name = shift || '';
794 my ( $suffix ) = ( $name =~ s/(\W.*)$// ) ? $1 : '';
796 # also trap fields that don't begin with a letter
797 return $name if !$ora_reserved{ uc $name } && $name =~ /^[a-z]/i;
799 if ( $schema_obj_name ) {
800 ++$unreserve{"$schema_obj_name.$name"};
803 ++$unreserve{"$name (table name)"};
806 my $unreserve = sprintf '%s_', $name;
807 return $unreserve.$suffix;
812 # -------------------------------------------------------------------
813 # All bad art is the result of good intentions.
815 # -------------------------------------------------------------------
821 Mad props to Tim Bunce for much of the logic stolen from his "mysql2ora"
826 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
830 SQL::Translator, DDL::Oracle, mysql2ora.