1 package SQL::Translator::Producer::Oracle;
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 # -------------------------------------------------------------------
23 SQL::Translator::Producer::Oracle - Oracle SQL producer
29 my $t = SQL::Translator->new( parser => '...', producer => 'Oracle' );
30 print $translator->translate( $file );
34 Creates an SQL DDL suitable for Oracle.
40 =item delay_constraints
42 This option remove the primary key and other key constraints from the
43 CREATE TABLE statement and adds ALTER TABLEs at the end with it.
45 =item quote_field_names
47 Controls whether quotes are being used around column names in generated DDL.
49 =item quote_table_names
51 Controls whether quotes are being used around table, sequence and trigger names in
58 =head2 Autoincremental primary keys
60 This producer uses sequences and triggers to autoincrement primary key
61 columns, if necessary. SQLPlus and DBI expect a slightly different syntax
62 of CREATE TRIGGER statement. You might have noticed that this
63 producer returns a scalar containing all statements concatenated by
64 newlines or an array of single statements depending on the context
65 (scalar, array) it has been called in.
67 SQLPlus expects following trigger syntax:
69 CREATE OR REPLACE TRIGGER ai_person_id
70 BEFORE INSERT ON person
72 new.id IS NULL OR new.id = 0
75 SELECT sq_person_id.nextval
81 Whereas if you want to create the same trigger using L<DBI/do>, you need
82 to omit the last slash:
84 my $dbh = DBI->connect('dbi:Oracle:mysid', 'scott', 'tiger');
86 CREATE OR REPLACE TRIGGER ai_person_id
87 BEFORE INSERT ON person
89 new.id IS NULL OR new.id = 0
92 SELECT sq_person_id.nextval
98 If you call this producer in array context, we expect you want to process
99 the returned array of statements using L<DBI> like
100 L<DBIx::Class::Schema/deploy> does.
102 To get this working we removed the slash in those statements in version
103 0.09002 of L<SQL::Translator> when called in array context. In scalar
104 context the slash will be still there to ensure compatibility with SQLPlus.
109 use vars qw[ $VERSION $DEBUG $WARN ];
111 $DEBUG = 0 unless defined $DEBUG;
113 use SQL::Translator::Schema::Constants;
114 use SQL::Translator::Utils qw(header_comment);
126 mediumint => 'number',
127 smallint => 'number',
130 varchar => 'varchar2',
133 mediumblob => 'blob',
135 tinytext => 'varchar2',
136 text => [ 'varchar2', 4000 ],
138 mediumtext => 'clob',
151 'double precision' => 'number',
153 bigserial => 'number',
156 'character varying' => 'varchar2',
158 interval => 'number',
169 macaddr => 'varchar2',
171 'bit varying' => 'number',
177 varchar2 => 'varchar2',
182 # Oracle 8/9 max size of data types from:
183 # http://www.ss64.com/orasyntax/datatypes.html
189 number => [ 38, 127 ],
191 varchar => 4000, # only synonym for varchar2
195 my $max_id_length = 30;
196 my %used_identifiers = ();
200 # Quote used to escape table, field, sequence and trigger names
201 my $quote_char = '"';
203 # -------------------------------------------------------------------
205 my $translator = shift;
206 $DEBUG = $translator->debug;
207 $WARN = $translator->show_warnings || 0;
208 my $no_comments = $translator->no_comments;
209 my $add_drop_table = $translator->add_drop_table;
210 my $schema = $translator->schema;
211 my $oracle_version = $translator->producer_args->{oracle_version} || 0;
212 my $delay_constraints = $translator->producer_args->{delay_constraints};
213 my ($output, $create, @table_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs);
215 $create .= header_comment unless ($no_comments);
216 my $qt = 1 if $translator->quote_table_names;
217 my $qf = 1 if $translator->quote_field_names;
219 if ( $translator->parser_type =~ /mysql/i ) {
221 "-- We assume that default NLS_DATE_FORMAT has been changed\n".
222 "-- but we set it here anyway to be self-consistent.\n"
226 "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';\n\n";
229 for my $table ( $schema->get_tables ) {
230 my ( $table_def, $fk_def, $trigger_def, $index_def, $constraint_def ) = create_table(
233 add_drop_table => $add_drop_table,
234 show_warnings => $WARN,
235 no_comments => $no_comments,
236 delay_constraints => $delay_constraints,
237 quote_table_names => $qt,
238 quote_field_names => $qf,
241 push @table_defs, @$table_def;
242 push @fk_defs, @$fk_def;
243 push @trigger_defs, @$trigger_def;
244 push @index_defs, @$index_def;
245 push @constraint_defs, @$constraint_def;
249 foreach my $view ( $schema->get_views ) {
250 my ( $view_def ) = create_view(
253 add_drop_view => $add_drop_table,
254 quote_table_names => $qt,
257 push @view_defs, @$view_def;
261 return defined $create ? $create : (), @table_defs, @view_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs;
264 $create .= join (";\n\n", @table_defs, @view_defs, @fk_defs, @index_defs, @constraint_defs);
266 # If wantarray is not set we have to add "/" in this statement
267 # DBI->do() needs them omitted
268 # triggers may NOT end with a semicolon
269 $create .= join "/\n\n", @trigger_defs;
277 my ($table, $options) = @_;
278 my $qt = $options->{quote_table_names};
279 my $qf = $options->{quote_field_names};
280 my $table_name = $table->name;
281 my $table_name_q = quote($table_name,$qt);
285 my (@create, @field_defs, @constraint_defs, @fk_defs, @trigger_defs);
287 push @create, "--\n-- Table: $table_name\n--" unless $options->{no_comments};
288 push @create, qq[DROP TABLE $table_name_q CASCADE CONSTRAINTS] if $options->{add_drop_table};
290 my ( %field_name_scope, @field_comments );
291 for my $field ( $table->get_fields ) {
292 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
293 create_field($field, $options, \%field_name_scope);
294 push @create, @$field_create if ref $field_create;
295 push @field_defs, @$field_defs if ref $field_defs;
296 push @trigger_defs, @$trigger_defs if ref $trigger_defs;
297 push @field_comments, @$field_comments if ref $field_comments;
304 for my $opt ( $table->options ) {
305 if ( ref $opt eq 'HASH' ) {
306 my ( $key, $value ) = each %$opt;
307 if ( ref $value eq 'ARRAY' ) {
308 push @table_options, "$key\n(\n". join ("\n",
309 map { " $_->[0]\t$_->[1]" }
314 elsif ( !defined $value ) {
315 push @table_options, $key;
318 push @table_options, "$key $value";
326 for my $c ( $table->get_constraints ) {
327 my $name = $c->name || '';
328 my @fields = map { quote($_,$qf) } $c->fields;
329 my @rfields = quote($c->reference_fields,$qf);
330 next if !@fields && $c->type ne CHECK_C;
332 if ( $c->type eq PRIMARY_KEY ) {
333 # create a name if delay_constraints
334 $name ||= mk_name( $table_name, 'pk' )
335 if $options->{delay_constraints};
336 $name = quote($name,$qf);
337 push @constraint_defs, ($name ? "CONSTRAINT $name " : '') .
338 'PRIMARY KEY (' . join( ', ', @fields ) . ')';
340 elsif ( $c->type eq UNIQUE ) {
341 # Don't create UNIQUE constraints identical to the primary key
342 if ( my $pk = $table->primary_key ) {
343 my $u_fields = join(":", @fields);
344 my $pk_fields = join(":", $pk->fields);
345 next if $u_fields eq $pk_fields;
349 # Force prepend of table_name as ORACLE doesn't allow duplicate
350 # CONSTRAINT names even for different tables (ORA-02264)
351 $name = "${table_name}_$name" unless $name =~ /^$table_name/;
354 $name = mk_name( $table_name, 'u' );
357 $name = quote($name, $qf);
359 for my $f ( $c->fields ) {
360 my $field_def = $table->get_field( $f ) or next;
361 my $dtype = $translate{ ref $field_def->data_type eq "ARRAY" ? $field_def->data_type->[0] : $field_def->data_type} or next;
362 if ( $WARN && $dtype =~ /clob/i ) {
363 warn "Oracle will not allow UNIQUE constraints on " .
364 "CLOB field '" . $field_def->table->name . '.' .
365 $field_def->name . ".'\n"
369 push @constraint_defs, "CONSTRAINT $name UNIQUE " .
370 '(' . join( ', ', @fields ) . ')';
372 elsif ( $c->type eq CHECK_C ) {
373 $name ||= mk_name( $name || $table_name, 'ck' );
374 $name = quote($name, $qf);
375 my $expression = $c->expression || '';
376 push @constraint_defs, "CONSTRAINT $name CHECK ($expression)";
378 elsif ( $c->type eq FOREIGN_KEY ) {
379 $name = mk_name( join('_', $table_name, $c->fields). '_fk' );
380 $name = quote($name, $qf);
381 my $def = "CONSTRAINT $name FOREIGN KEY ";
384 $def .= '(' . join( ', ', @fields ) . ')';
387 my $ref_table = quote($c->reference_table,$qt);
389 $def .= " REFERENCES $ref_table";
392 $def .= ' (' . join( ', ', @rfields ) . ')';
395 if ( $c->match_type ) {
397 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
400 if ( $c->on_delete ) {
401 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
404 # disabled by plu 2007-12-29 - doesn't exist for oracle
405 #if ( $c->on_update ) {
406 # $def .= ' ON UPDATE '.join( ' ', $c->on_update );
409 push @fk_defs, sprintf("ALTER TABLE %s ADD %s", $table_name_q, $def);
417 for my $index ( $table->get_indices ) {
418 my $index_name = $index->name || '';
419 my $index_type = $index->type || NORMAL;
420 my @fields = map { quote($_, $qf) } $index->fields;
424 for my $opt ( $index->options ) {
425 if ( ref $opt eq 'HASH' ) {
426 my ( $key, $value ) = each %$opt;
427 if ( ref $value eq 'ARRAY' ) {
428 push @table_options, "$key\n(\n". join ("\n",
429 map { " $_->[0]\t$_->[1]" }
434 elsif ( !defined $value ) {
435 push @index_options, $key;
438 push @index_options, "$key $value";
442 my $index_options = @index_options
443 ? "\n".join("\n", @index_options) : '';
445 if ( $index_type eq PRIMARY_KEY ) {
446 $index_name = $index_name ? mk_name( $index_name )
447 : mk_name( $table_name, 'pk' );
448 $index_name = quote($index_name, $qf);
449 push @field_defs, 'CONSTRAINT '.$index_name.' PRIMARY KEY '.
450 '(' . join( ', ', @fields ) . ')';
452 elsif ( $index_type eq NORMAL ) {
453 $index_name = $index_name ? mk_name( $index_name )
454 : mk_name( $table_name, $index_name || 'i' );
455 $index_name = quote($index_name, $qf);
457 "CREATE INDEX $index_name on $table_name_q (".
458 join( ', ', @fields ).
461 elsif ( $index_type eq UNIQUE ) {
462 $index_name = $index_name ? mk_name( $index_name )
463 : mk_name( $table_name, $index_name || 'i' );
464 $index_name = quote($index_name, $qf);
466 "CREATE UNIQUE INDEX $index_name on $table_name_q (".
467 join( ', ', @fields ).
471 warn "Unknown index type ($index_type) on table $table_name.\n"
476 if ( my @table_comments = $table->comments ) {
477 for my $comment ( @table_comments ) {
478 next unless $comment;
479 $comment =~ s/'/''/g;
480 push @field_comments, "COMMENT ON TABLE $table_name_q is\n '".
481 $comment . "'" unless $options->{no_comments}
486 my $table_options = @table_options
487 ? "\n".join("\n", @table_options) : '';
488 push @create, "CREATE TABLE $table_name_q (\n" .
489 join( ",\n", map { " $_" } @field_defs,
490 ($options->{delay_constraints} ? () : @constraint_defs) ) .
493 @constraint_defs = map { "ALTER TABLE $table_name_q ADD $_" }
498 warn "Truncated " . keys( %truncated ) . " names:\n";
499 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
503 return \@create, \@fk_defs, \@trigger_defs, \@index_defs, ($options->{delay_constraints} ? \@constraint_defs : []);
507 my ($from_field, $to_field, $options) = @_;
509 my $qt = $options->{quote_table_names};
510 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
511 create_field($to_field, $options, {});
514 if ($to_field->is_nullable && !$from_field->is_nullable) {
515 die 'Cannot remove NOT NULL from table field';
516 } elsif (!$from_field->is_nullable && !$to_field->is_nullable) {
517 @$field_defs = map { s/ NOT NULL//; $_} @$field_defs;
520 my $table_name = quote($to_field->table->name,$qt);
522 return 'ALTER TABLE '.$table_name.' MODIFY ( '.join('', @$field_defs).' )';
526 my ($new_field, $options) = @_;
528 my $qt = $options->{quote_table_names};
529 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
530 create_field($new_field, $options, {});
532 my $table_name = quote($new_field->table->name,$qt);
534 my $out = sprintf('ALTER TABLE %s ADD ( %s )',
536 join('', @$field_defs));
541 my ($field, $options, $field_name_scope) = @_;
542 my $qf = $options->{quote_field_names};
543 my $qt = $options->{quote_table_names};
545 my (@create, @field_defs, @trigger_defs, @field_comments);
547 my $table_name = $field->table->name;
548 my $table_name_q = quote($table_name, $qt);
553 my $field_name = mk_name(
554 $field->name, '', $field_name_scope, 1
556 my $field_name_q = quote($field_name, $qf);
557 my $field_def = quote($field_name, $qf);
558 $field->name( $field_name );
564 my $data_type = lc $field->data_type;
565 my @size = $field->size;
566 my %extra = $field->extra;
567 my $list = $extra{'list'} || [];
568 # \todo deal with embedded quotes
569 my $commalist = join( ', ', map { qq['$_'] } @$list );
571 if ( $data_type eq 'enum' ) {
572 $check = "CHECK ($field_name_q IN ($commalist))";
573 $data_type = 'varchar2';
575 elsif ( $data_type eq 'set' ) {
576 # XXX add a CHECK constraint maybe
577 # (trickier and slower, than enum :)
578 $data_type = 'varchar2';
581 if (defined $translate{ $data_type }) {
582 if (ref $translate{ $data_type } eq "ARRAY") {
583 ($data_type,$size[0]) = @{$translate{ $data_type }};
585 $data_type = $translate{ $data_type };
588 $data_type ||= 'varchar2';
591 # ensure size is not bigger than max size oracle allows for data type
592 if ( defined $max_size{$data_type} ) {
593 for ( my $i = 0 ; $i < scalar @size ; $i++ ) {
595 ref( $max_size{$data_type} ) eq 'ARRAY'
596 ? $max_size{$data_type}->[$i]
597 : $max_size{$data_type};
598 $size[$i] = $max if $size[$i] > $max;
603 # Fixes ORA-02329: column of datatype LOB cannot be
604 # unique or a primary key
606 if ( $data_type eq 'clob' && $field->is_primary_key ) {
607 $data_type = 'varchar2';
609 warn "CLOB cannot be a primary key, changing to VARCHAR2\n"
613 if ( $data_type eq 'clob' && $field->is_unique ) {
614 $data_type = 'varchar2';
616 warn "CLOB cannot be a unique key, changing to VARCHAR2\n"
621 # Fixes ORA-00907: missing right parenthesis
623 if ( $data_type =~ /(date|clob)/i ) {
627 $field_def .= " $data_type";
628 if ( defined $size[0] && $size[0] > 0 ) {
629 $field_def .= '(' . join( ',', @size ) . ')';
635 my $default = $field->default_value;
636 if ( defined $default ) {
638 # Wherein we try to catch a string being used as
639 # a default value for a numerical field. If "true/false,"
640 # then sub "1/0," otherwise just test the truthity of the
641 # argument and use that (naive?).
643 if (ref $default and defined $$default) {
644 $default = $$default;
645 } elsif (ref $default) {
648 $data_type =~ /^number$/i &&
649 $default !~ /^-?\d+$/ &&
652 if ( $default =~ /^true$/i ) {
654 } elsif ( $default =~ /^false$/i ) {
657 $default = $default ? "'1'" : "'0'";
660 $data_type =~ /date/ && (
661 $default eq 'current_timestamp'
666 $default = 'SYSDATE';
668 $default = $default =~ m/null/i ? 'NULL' : "'$default'"
671 $field_def .= " DEFAULT $default",
675 # Not null constraint
677 unless ( $field->is_nullable ) {
678 $field_def .= ' NOT NULL';
681 $field_def .= " $check" if $check;
686 if ( $field->is_auto_increment ) {
687 my $base_name = $table_name . "_". $field_name;
688 my $seq_name = quote(mk_name( $base_name, 'sq' ),$qt);
689 my $trigger_name = quote(mk_name( $base_name, 'ai' ),$qt);
691 push @create, qq[DROP SEQUENCE $seq_name] if $options->{add_drop_table};
692 push @create, "CREATE SEQUENCE $seq_name";
694 "CREATE OR REPLACE TRIGGER $trigger_name\n" .
695 "BEFORE INSERT ON $table_name_q\n" .
696 "FOR EACH ROW WHEN (\n" .
697 " new.$field_name_q IS NULL".
698 " OR new.$field_name_q = 0\n".
701 " SELECT $seq_name.nextval\n" .
702 " INTO :new." . $field_name_q."\n" .
706 push @trigger_defs, $trigger;
709 if ( lc $field->data_type eq 'timestamp' ) {
710 my $base_name = $table_name . "_". $field_name;
711 my $trig_name = quote(mk_name( $base_name, 'ts' ), $qt);
713 "CREATE OR REPLACE TRIGGER $trig_name\n".
714 "BEFORE INSERT OR UPDATE ON $table_name_q\n".
715 "FOR EACH ROW WHEN (new.$field_name_q IS NULL)\n".
717 " SELECT sysdate INTO :new.$field_name_q FROM dual;\n".
720 push @trigger_defs, $trigger;
723 push @field_defs, $field_def;
725 if ( my $comment = $field->comments ) {
726 $comment =~ s/'/''/g;
727 push @field_comments,
728 "COMMENT ON COLUMN $table_name_q.$field_name_q is\n '" .
729 $comment . "';" unless $options->{no_comments};
732 return \@create, \@field_defs, \@trigger_defs, \@field_comments;
738 my ($view, $options) = @_;
739 my $qt = $options->{quote_table_names};
740 my $view_name = quote($view->name,$qt);
743 push @create, qq[DROP VIEW $view_name]
744 if $options->{add_drop_view};
746 push @create, sprintf("CREATE VIEW %s AS\n%s",
753 # -------------------------------------------------------------------
755 my $basename = shift || '';
756 my $type = shift || '';
757 $type = '' if $type =~ /^\d/;
758 my $scope = shift || '';
759 my $critical = shift || '';
760 my $basename_orig = $basename;
762 ? $max_id_length - (length($type) + 1)
764 $basename = substr( $basename, 0, $max_name )
765 if length( $basename ) > $max_name;
766 my $name = $type ? "${type}_$basename" : $basename;
768 if ( $basename ne $basename_orig and $critical ) {
769 my $show_type = $type ? "+'$type'" : "";
770 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
771 "character limit to make '$name'\n" if $WARN;
772 $truncated{ $basename_orig } = $name;
775 $scope ||= \%global_names;
776 if ( my $prev = $scope->{ $name } ) {
777 my $name_orig = $name;
778 substr($name, $max_id_length - 2) = ""
779 if length( $name ) >= $max_id_length - 1;
780 $name .= sprintf( "%02d", $prev++ );
782 warn "The name '$name_orig' has been changed to ",
783 "'$name' to make it unique.\n" if $WARN;
785 $scope->{ $name_orig }++;
794 # -------------------------------------------------------------------
797 $q && $name ? "$quote_char$name$quote_char" : $name;
801 # -------------------------------------------------------------------
802 # All bad art is the result of good intentions.
804 # -------------------------------------------------------------------
810 Mad props to Tim Bunce for much of the logic stolen from his "mysql2ora"
815 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
816 Alexander Hartmaier E<lt>abraxxa@cpan.orgE<gt>,
817 Fabien Wernli E<lt>faxmodem@cpan.orgE<gt>.
821 SQL::Translator, DDL::Oracle, mysql2ora.