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.
108 This producer will generate
109 DDL with or without quotes if L<quote_table_names> and/or
110 L<quote_field_names> are true.
112 Quotes will be forced and names capitalised if C<quote_table_names==0> and/or C<quote_field_names==0>
113 for the following reserved keywords:
115 ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR CHECK
116 CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL
117 DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT
118 FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT
119 INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK
120 LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT
121 NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE
122 PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM
123 ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM
124 SYSDATE TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE
125 VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH
130 use vars qw[ $VERSION $DEBUG $WARN ];
132 $DEBUG = 0 unless defined $DEBUG;
134 use SQL::Translator::Schema::Constants;
135 use SQL::Translator::Utils qw(header_comment);
147 mediumint => 'number',
148 smallint => 'number',
151 varchar => 'varchar2',
154 mediumblob => 'blob',
156 tinytext => 'varchar2',
159 mediumtext => 'clob',
172 'double precision' => 'number',
174 bigserial => 'number',
177 'character varying' => 'varchar2',
179 interval => 'number',
190 macaddr => 'varchar2',
192 'bit varying' => 'number',
198 varchar2 => 'varchar2',
203 # Oracle reserved words from:
204 # http://technet.oracle.com/docs/products/oracle8i/doc_library/\
205 # 817_doc/server.817/a85397/ap_keywd.htm
207 my %ora_reserved = map { $_, 1 } qw(
208 ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT
210 CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT
211 DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP
212 ELSE EXCLUSIVE EXISTS
216 IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT
217 INTEGER INTERSECT INTO IS
219 MAXEXTENTS MINUS MLSLABEL MODE MODIFY
220 NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER
221 OF OFFLINE ON ONLINE OPTION OR ORDER
222 PCTFREE PRIOR PRIVILEGES PUBLIC
223 RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS
224 SELECT SESSION SET SHARE SIZE SMALLINT START
225 SUCCESSFUL SYNONYM SYSDATE
226 TABLE THEN TO TRIGGER
227 UID UNION UNIQUE UPDATE USER
228 VALIDATE VALUES VARCHAR VARCHAR2 VIEW
233 # Oracle 8/9 max size of data types from:
234 # http://www.ss64.com/orasyntax/datatypes.html
240 number => [ 38, 127 ],
242 varchar => 4000, # only synonym for varchar2
246 my $max_id_length = 30;
247 my %used_identifiers = ();
251 # Quote used to escape table, field, sequence and trigger names
252 my $quote_char = '"';
255 # -------------------------------------------------------------------
257 my $translator = shift;
258 $DEBUG = $translator->debug;
259 $WARN = $translator->show_warnings || 0;
260 my $no_comments = $translator->no_comments;
261 my $add_drop_table = $translator->add_drop_table;
262 my $schema = $translator->schema;
263 $quote_char = $translator->producer_args->{'quote_char'} ||= '"';
264 $name_sep = $translator->producer_args->{'name_sep'} ||= '.';
265 my $delay_constraints = $translator->producer_args->{delay_constraints};
266 my ($output, $create, @table_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs);
268 $create .= header_comment unless ($no_comments);
269 my $qt = $quote_char if $translator->quote_table_names;
270 my $qf = $quote_char if $translator->quote_field_names;
272 if ( $translator->parser_type =~ /mysql/i ) {
274 "-- We assume that default NLS_DATE_FORMAT has been changed\n".
275 "-- but we set it here anyway to be self-consistent.\n"
279 "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';\n\n";
282 for my $table ( $schema->get_tables ) {
283 my ( $table_def, $fk_def, $trigger_def, $index_def, $constraint_def ) = create_table(
286 add_drop_table => $add_drop_table,
287 show_warnings => $WARN,
288 no_comments => $no_comments,
289 delay_constraints => $delay_constraints,
290 quote_table_names => $qt,
291 quote_field_names => $qf,
294 push @table_defs, @$table_def;
295 push @fk_defs, @$fk_def;
296 push @trigger_defs, @$trigger_def;
297 push @index_defs, @$index_def;
298 push @constraint_defs, @$constraint_def;
302 foreach my $view ( $schema->get_views ) {
303 my ( $view_def ) = create_view(
306 add_drop_view => $add_drop_table,
309 push @view_defs, @$view_def;
313 return defined $create ? $create : (), @table_defs, @view_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs;
316 $create .= join (";\n\n", @table_defs, @view_defs, @fk_defs, @index_defs, @constraint_defs);
318 # If wantarray is not set we have to add "/" in this statement
319 # DBI->do() needs them omitted
320 # triggers may NOT end with a semicolon
321 $create .= join "/\n\n", @trigger_defs;
329 my ($table, $options) = @_;
330 my $qt = $options->{quote_table_names};
331 my $qf = $options->{quote_field_names};
332 my $table_name = $table->name;
333 my $table_name_q = quote($table_name,$qt);
337 my (@create, @field_defs, @constraint_defs, @fk_defs, @trigger_defs);
339 push @create, "--\n-- Table: $table_name\n--" unless $options->{no_comments};
340 push @create, qq[DROP TABLE $table_name_q CASCADE CONSTRAINTS] if $options->{add_drop_table};
342 my ( %field_name_scope, @field_comments );
343 for my $field ( $table->get_fields ) {
344 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
345 create_field($field, $options, \%field_name_scope);
346 push @create, @$field_create if ref $field_create;
347 push @field_defs, @$field_defs if ref $field_defs;
348 push @trigger_defs, @$trigger_defs if ref $trigger_defs;
349 push @field_comments, @$field_comments if ref $field_comments;
356 for my $opt ( $table->options ) {
357 if ( ref $opt eq 'HASH' ) {
358 my ( $key, $value ) = each %$opt;
359 if ( ref $value eq 'ARRAY' ) {
360 push @table_options, "$key\n(\n". join ("\n",
361 map { " $_->[0]\t$_->[1]" }
366 elsif ( !defined $value ) {
367 push @table_options, $key;
370 push @table_options, "$key $value";
378 for my $c ( $table->get_constraints ) {
379 my $name = $c->name || '';
380 my @fields = map { quote($_,$qf) } $c->fields;
381 my @rfields = quote($c->reference_fields,$qf);
382 next if !@fields && $c->type ne CHECK_C;
384 if ( $c->type eq PRIMARY_KEY ) {
385 # create a name if delay_constraints
386 $name ||= mk_name( $table_name, 'pk' )
387 if $options->{delay_constraints};
388 push @constraint_defs, ($name ? "CONSTRAINT $name " : '') .
389 'PRIMARY KEY (' . join( ', ', @fields ) . ')';
391 elsif ( $c->type eq UNIQUE ) {
392 # Don't create UNIQUE constraints identical to the primary key
393 if ( my $pk = $table->primary_key ) {
394 my $u_fields = join(":", @fields);
395 my $pk_fields = join(":", $pk->fields);
396 next if $u_fields eq $pk_fields;
399 # Force prepend of table_name as ORACLE doesn't allow duplicate
400 # CONSTRAINT names even for different tables (ORA-02264)
401 $name = "${table_name}_$name" unless $name =~ /^$table_name/;
403 $name = mk_name( $table_name, 'u' );
405 $name = quote($name, $qf);
407 for my $f ( $c->fields ) {
408 my $field_def = $table->get_field( $f ) or next;
409 my $dtype = $translate{ $field_def->data_type } or next;
410 if ( $WARN && $dtype =~ /clob/i ) {
411 warn "Oracle will not allow UNIQUE constraints on " .
412 "CLOB field '" . $field_def->table->name . '.' .
413 $field_def->name . ".'\n"
417 push @constraint_defs, "CONSTRAINT $name UNIQUE " .
418 '(' . join( ', ', @fields ) . ')';
420 elsif ( $c->type eq CHECK_C ) {
421 $name ||= mk_name( $name || $table_name, 'ck' );
422 my $expression = $c->expression || '';
423 push @constraint_defs, "CONSTRAINT $name CHECK ($expression)";
425 elsif ( $c->type eq FOREIGN_KEY ) {
426 $name = mk_name( join('_', $table_name, $c->fields). '_fk' );
427 $name = quote($name, $qf);
428 my $def = "CONSTRAINT $name FOREIGN KEY ";
431 $def .= '(' . join( ', ', @fields ) . ')';
434 my $ref_table = quote($c->reference_table,$qt);
436 $def .= " REFERENCES $ref_table";
439 $def .= ' (' . join( ', ', @rfields ) . ')';
442 if ( $c->match_type ) {
444 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
447 if ( $c->on_delete ) {
448 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
451 # disabled by plu 2007-12-29 - doesn't exist for oracle
452 #if ( $c->on_update ) {
453 # $def .= ' ON UPDATE '.join( ' ', $c->on_update );
456 push @fk_defs, sprintf("ALTER TABLE %s ADD %s", $table_name_q, $def);
464 for my $index ( $table->get_indices ) {
465 my $index_name = $index->name || '';
466 my $index_type = $index->type || NORMAL;
467 my @fields = map { quote($_, $qf) } $index->fields;
471 for my $opt ( $index->options ) {
472 if ( ref $opt eq 'HASH' ) {
473 my ( $key, $value ) = each %$opt;
474 if ( ref $value eq 'ARRAY' ) {
475 push @table_options, "$key\n(\n". join ("\n",
476 map { " $_->[0]\t$_->[1]" }
481 elsif ( !defined $value ) {
482 push @index_options, $key;
485 push @index_options, "$key $value";
489 my $index_options = @index_options
490 ? "\n".join("\n", @index_options) : '';
492 if ( $index_type eq PRIMARY_KEY ) {
493 $index_name = $index_name ? mk_name( $index_name )
494 : mk_name( $table_name, 'pk' );
495 $index_name = quote($index_name, $qf);
496 push @field_defs, 'CONSTRAINT '.$index_name.' PRIMARY KEY '.
497 '(' . join( ', ', @fields ) . ')';
499 elsif ( $index_type eq NORMAL ) {
500 $index_name = $index_name ? mk_name( $index_name )
501 : mk_name( $table_name, $index_name || 'i' );
502 $index_name = quote($index_name, $qf);
504 "CREATE INDEX $index_name on ".quote($table_name,$qt)." (".
505 join( ', ', @fields ).
508 elsif ( $index_type eq UNIQUE ) {
509 $index_name = $index_name ? mk_name( $index_name )
510 : mk_name( $table_name, $index_name || 'i' );
511 $index_name = quote($index_name, $qf);
513 "CREATE UNIQUE INDEX $index_name on $table_name (".
514 join( ', ', @fields ).
518 warn "Unknown index type ($index_type) on table $table_name.\n"
523 if ( my @table_comments = $table->comments ) {
524 for my $comment ( @table_comments ) {
525 next unless $comment;
526 $comment =~ s/'/''/g;
527 push @field_comments, "COMMENT ON TABLE $table_name_q is\n '".
528 $comment . "'" unless $options->{no_comments}
533 my $table_options = @table_options
534 ? "\n".join("\n", @table_options) : '';
535 push @create, "CREATE TABLE $table_name_q (\n" .
536 join( ",\n", map { " $_" } @field_defs,
537 ($options->{delay_constraints} ? () : @constraint_defs) ) .
540 @constraint_defs = map { "ALTER TABLE $table_name_q ADD $_" }
545 warn "Truncated " . keys( %truncated ) . " names:\n";
546 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
550 return \@create, \@fk_defs, \@trigger_defs, \@index_defs, ($options->{delay_constraints} ? \@constraint_defs : []);
554 my ($from_field, $to_field, $options) = @_;
556 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
557 create_field($to_field, $options, {});
560 if ($to_field->is_nullable && !$from_field->is_nullable) {
561 die 'Cannot remove NOT NULL from table field';
562 } elsif (!$from_field->is_nullable && !$to_field->is_nullable) {
563 @$field_defs = map { s/ NOT NULL//; $_} @$field_defs;
566 my $table_name = $to_field->table->name;
568 return 'ALTER TABLE '.$table_name.' MODIFY ( '.join('', @$field_defs).' )';
572 my ($new_field, $options) = @_;
574 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
575 create_field($new_field, $options, {});
577 my $table_name = $new_field->table->name;
579 my $out = sprintf('ALTER TABLE %s ADD ( %s )',
581 join('', @$field_defs));
586 my ($field, $options, $field_name_scope) = @_;
587 my $qf = $options->{quote_field_names};
588 my $qt = $options->{quote_table_names};
590 my (@create, @field_defs, @trigger_defs, @field_comments);
592 my $table_name = $field->table->name;
593 my $table_name_q = quote($table_name, $qt);
598 my $field_name = mk_name(
599 $field->name, '', $field_name_scope, 1
601 my $field_name_q = quote($field_name, $qf);
602 my $field_def = quote($field_name, $qf);
603 $field->name( $field_name );
609 my $data_type = lc $field->data_type;
610 my @size = $field->size;
611 my %extra = $field->extra;
612 my $list = $extra{'list'} || [];
613 # \todo deal with embedded quotes
614 my $commalist = join( ', ', map { qq['$_'] } @$list );
616 if ( $data_type eq 'enum' ) {
617 $check = "CHECK ($field_name_q IN ($commalist))";
618 $data_type = 'varchar2';
620 elsif ( $data_type eq 'set' ) {
621 # XXX add a CHECK constraint maybe
622 # (trickier and slower, than enum :)
623 $data_type = 'varchar2';
626 $data_type = defined $translate{ $data_type } ?
627 $translate{ $data_type } :
629 $data_type ||= 'varchar2';
632 # ensure size is not bigger than max size oracle allows for data type
633 if ( defined $max_size{$data_type} ) {
634 for ( my $i = 0 ; $i < scalar @size ; $i++ ) {
636 ref( $max_size{$data_type} ) eq 'ARRAY'
637 ? $max_size{$data_type}->[$i]
638 : $max_size{$data_type};
639 $size[$i] = $max if $size[$i] > $max;
644 # Fixes ORA-02329: column of datatype LOB cannot be
645 # unique or a primary key
647 if ( $data_type eq 'clob' && $field->is_primary_key ) {
648 $data_type = 'varchar2';
650 warn "CLOB cannot be a primary key, changing to VARCHAR2\n"
654 if ( $data_type eq 'clob' && $field->is_unique ) {
655 $data_type = 'varchar2';
657 warn "CLOB cannot be a unique key, changing to VARCHAR2\n"
662 # Fixes ORA-00907: missing right parenthesis
664 if ( $data_type =~ /(date|clob)/i ) {
668 $field_def .= " $data_type";
669 if ( defined $size[0] && $size[0] > 0 ) {
670 $field_def .= '(' . join( ',', @size ) . ')';
676 my $default = $field->default_value;
677 if ( defined $default ) {
679 # Wherein we try to catch a string being used as
680 # a default value for a numerical field. If "true/false,"
681 # then sub "1/0," otherwise just test the truthity of the
682 # argument and use that (naive?).
684 if (ref $default and defined $$default) {
685 $default = $$default;
686 } elsif (ref $default) {
689 $data_type =~ /^number$/i &&
690 $default !~ /^-?\d+$/ &&
693 if ( $default =~ /^true$/i ) {
695 } elsif ( $default =~ /^false$/i ) {
698 $default = $default ? "'1'" : "'0'";
701 $data_type =~ /date/ && (
702 $default eq 'current_timestamp'
707 $default = 'SYSDATE';
709 $default = $default =~ m/null/i ? 'NULL' : "'$default'"
712 $field_def .= " DEFAULT $default",
716 # Not null constraint
718 unless ( $field->is_nullable ) {
719 $field_def .= ' NOT NULL';
722 $field_def .= " $check" if $check;
727 if ( $field->is_auto_increment ) {
728 my $base_name = $table_name . "_". $field_name;
729 my $seq_name = quote(mk_name( $base_name, 'sq' ),$qt);
730 my $trigger_name = quote(mk_name( $base_name, 'ai' ),$qt);
732 push @create, qq[DROP SEQUENCE $seq_name] if $options->{add_drop_table};
733 push @create, "CREATE SEQUENCE $seq_name";
735 "CREATE OR REPLACE TRIGGER $trigger_name\n" .
736 "BEFORE INSERT ON $table_name_q\n" .
737 "FOR EACH ROW WHEN (\n" .
738 " new.$field_name_q IS NULL".
739 " OR new.$field_name_q = 0\n".
742 " SELECT $seq_name.nextval\n" .
743 " INTO :new." . $field_name_q."\n" .
747 push @trigger_defs, $trigger;
750 if ( lc $field->data_type eq 'timestamp' ) {
751 my $base_name = $table_name . "_". $field_name;
752 my $trig_name = quote(mk_name( $base_name, 'ts' ), $qt);
754 "CREATE OR REPLACE TRIGGER $trig_name\n".
755 "BEFORE INSERT OR UPDATE ON $table_name_q\n".
756 "FOR EACH ROW WHEN (new.$field_name_q IS NULL)\n".
758 " SELECT sysdate INTO :new.$field_name_q FROM dual;\n".
761 push @trigger_defs, $trigger;
764 push @field_defs, $field_def;
766 if ( my $comment = $field->comments ) {
767 $comment =~ s/'/''/g;
768 push @field_comments,
769 "COMMENT ON COLUMN $table_name.$field_name is\n '" .
770 $comment . "';" unless $options->{no_comments};
773 return \@create, \@field_defs, \@trigger_defs, \@field_comments;
779 my ($view, $options) = @_;
780 my $qt = $options->{quote_table_names};
781 my $qf = $options->{quote_field_names};
782 my $view_name = quote($view->name,$qt);
785 push @create, qq[DROP VIEW $view_name]
786 if $options->{add_drop_view};
788 push @create, sprintf("CREATE VIEW %s AS\n%s",
795 # -------------------------------------------------------------------
797 my $basename = shift || '';
798 my $type = shift || '';
799 $type = '' if $type =~ /^\d/;
800 my $scope = shift || '';
801 my $critical = shift || '';
802 my $basename_orig = $basename;
804 ? $max_id_length - (length($type) + 1)
806 $basename = substr( $basename, 0, $max_name )
807 if length( $basename ) > $max_name;
808 my $name = $type ? "${type}_$basename" : $basename;
810 if ( $basename ne $basename_orig and $critical ) {
811 my $show_type = $type ? "+'$type'" : "";
812 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
813 "character limit to make '$name'\n" if $WARN;
814 $truncated{ $basename_orig } = $name;
817 $scope ||= \%global_names;
818 if ( my $prev = $scope->{ $name } ) {
819 my $name_orig = $name;
820 substr($name, $max_id_length - 2) = ""
821 if length( $name ) >= $max_id_length - 1;
822 $name .= sprintf( "%02d", $prev++ );
824 warn "The name '$name_orig' has been changed to ",
825 "'$name' to make it unique.\n" if $WARN;
827 $scope->{ $name_orig }++;
836 # -------------------------------------------------------------------
841 } elsif ($ora_reserved { uc $name }) {
842 # convert to upper case to be consistent with oracle
843 # when no quotes are being used
845 "$quote_char$name$quote_char";
852 # -------------------------------------------------------------------
853 # All bad art is the result of good intentions.
855 # -------------------------------------------------------------------
861 Mad props to Tim Bunce for much of the logic stolen from his "mysql2ora"
866 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
867 Alexander Hartmaier E<lt>abraxxa@cpan.orgE<gt>,
868 Fabien Wernli E<lt>faxmodem@cpan.orgE<gt>.
872 SQL::Translator, DDL::Oracle, mysql2ora.