projects / dbsrgits/SQL-Translator.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
remove commented copyright
[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Producer / Oracle.pm
CommitLineData
16dc9970 1package SQL::Translator::Producer::Oracle;
2
ba096dc4 3=head1 NAME
4
5SQL::Translator::Producer::Oracle - Oracle SQL producer
6
7=head1 SYNOPSIS
8
9 use SQL::Translator;
10
11 my $t = SQL::Translator->new( parser => '...', producer => 'Oracle' );
12 print $translator->translate( $file );
13
14=head1 DESCRIPTION
15
16Creates an SQL DDL suitable for Oracle.
17
662258cd 18=head1 producer_args
19
20=over
21
22=item delay_constraints
23
24This option remove the primary key and other key constraints from the
25CREATE TABLE statement and adds ALTER TABLEs at the end with it.
26
38b019a3 27=item quote_field_names
28
29Controls whether quotes are being used around column names in generated DDL.
30
31=item quote_table_names
32
33Controls whether quotes are being used around table, sequence and trigger names in
34generated DDL.
35
662258cd 36=back
37
2c5e6626 38=head1 NOTES
39
40=head2 Autoincremental primary keys
41
42This producer uses sequences and triggers to autoincrement primary key
43columns, if necessary. SQLPlus and DBI expect a slightly different syntax
44of CREATE TRIGGER statement. You might have noticed that this
45producer returns a scalar containing all statements concatenated by
46newlines or an array of single statements depending on the context
47(scalar, array) it has been called in.
48
49SQLPlus expects following trigger syntax:
50
51 CREATE OR REPLACE TRIGGER ai_person_id
52 BEFORE INSERT ON person
53 FOR EACH ROW WHEN (
54 new.id IS NULL OR new.id = 0
55 )
56 BEGIN
57 SELECT sq_person_id.nextval
58 INTO :new.id
59 FROM dual;
60 END;
61 /
62
63Whereas if you want to create the same trigger using L<DBI/do>, you need
64to omit the last slash:
65
66 my $dbh = DBI->connect('dbi:Oracle:mysid', 'scott', 'tiger');
67 $dbh->do("
68 CREATE OR REPLACE TRIGGER ai_person_id
69 BEFORE INSERT ON person
70 FOR EACH ROW WHEN (
71 new.id IS NULL OR new.id = 0
72 )
73 BEGIN
74 SELECT sq_person_id.nextval
75 INTO :new.id
76 FROM dual;
77 END;
78 ");
79
80If you call this producer in array context, we expect you want to process
81the returned array of statements using L<DBI> like
82L<DBIx::Class::Schema/deploy> does.
83
84To get this working we removed the slash in those statements in version
850.09002 of L<SQL::Translator> when called in array context. In scalar
86context the slash will be still there to ensure compatibility with SQLPlus.
87
ba096dc4 88=cut
89
16dc9970 90use strict;
da06ac74 91use vars qw[ $VERSION $DEBUG $WARN ];
11ad2df9 92$VERSION = '1.59';
d529894e 93$DEBUG = 0 unless defined $DEBUG;
16dc9970 94
57f77285 95use SQL::Translator::Schema::Constants;
5ee19df8 96use SQL::Translator::Utils qw(header_comment);
97
16dc9970 98my %translate = (
d529894e 99 #
100 # MySQL types
101 #
16dc9970 102 bigint => 'number',
07720cf1 103 double => 'float',
16dc9970 104 decimal => 'number',
3c8c1129 105 float => 'float',
16dc9970 106 int => 'number',
25966689 107 integer => 'number',
16dc9970 108 mediumint => 'number',
109 smallint => 'number',
110 tinyint => 'number',
16dc9970 111 char => 'char',
16dc9970 112 varchar => 'varchar2',
1f58ba76 113 tinyblob => 'blob',
114 blob => 'blob',
115 mediumblob => 'blob',
116 longblob => 'blob',
9fc9bfb1 117 tinytext => 'varchar2',
d570aec7 118 text => 'clob',
1f58ba76 119 longtext => 'clob',
120 mediumtext => 'clob',
16dc9970 121 enum => 'varchar2',
122 set => 'varchar2',
16dc9970 123 date => 'date',
124 datetime => 'date',
125 time => 'date',
126 timestamp => 'date',
127 year => 'date',
d529894e 128
129 #
130 # PostgreSQL types
131 #
57f77285 132 numeric => 'number',
133 'double precision' => 'number',
134 serial => 'number',
135 bigserial => 'number',
136 money => 'number',
137 character => 'char',
138 'character varying' => 'varchar2',
139 bytea => 'BLOB',
140 interval => 'number',
141 boolean => 'number',
142 point => 'number',
143 line => 'number',
144 lseg => 'number',
145 box => 'number',
146 path => 'number',
147 polygon => 'number',
148 circle => 'number',
149 cidr => 'number',
150 inet => 'varchar2',
151 macaddr => 'varchar2',
152 bit => 'number',
153 'bit varying' => 'number',
ab8802d7 154
155 #
156 # Oracle types
157 #
158 number => 'number',
159 varchar2 => 'varchar2',
160 long => 'clob',
d529894e 161);
162
163#
e3aac687 164# Oracle 8/9 max size of data types from:
165# http://www.ss64.com/orasyntax/datatypes.html
166#
167my %max_size = (
168 char => 2000,
3c8c1129 169 float => 126,
e3aac687 170 nchar => 2000,
171 nvarchar2 => 4000,
172 number => [ 38, 127 ],
173 raw => 2000,
174 varchar => 4000, # only synonym for varchar2
175 varchar2 => 4000,
176);
177
96844cae 178my $max_id_length = 30;
179my %used_identifiers = ();
d529894e 180my %global_names;
d529894e 181my %truncated;
16dc9970 182
38b019a3 183# Quote used to escape table, field, sequence and trigger names
184my $quote_char = '"';
38b019a3 185
077ebf34 186sub produce {
a1d94525 187 my $translator = shift;
188 $DEBUG = $translator->debug;
e56dabb7 189 $WARN = $translator->show_warnings || 0;
a1d94525 190 my $no_comments = $translator->no_comments;
191 my $add_drop_table = $translator->add_drop_table;
192 my $schema = $translator->schema;
15861005 193 my $oracle_version = $translator->producer_args->{oracle_version} || 0;
65ffb46c 194 my $delay_constraints = $translator->producer_args->{delay_constraints};
195 my ($output, $create, @table_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs);
44fcd0b5 196
e56dabb7 197 $create .= header_comment unless ($no_comments);
7769504d 198 my $qt = 1 if $translator->quote_table_names;
199 my $qf = 1 if $translator->quote_field_names;
077ebf34 200
d529894e 201 if ( $translator->parser_type =~ /mysql/i ) {
ea93df61 202 $create .=
ba3cb849 203 "-- We assume that default NLS_DATE_FORMAT has been changed\n".
204 "-- but we set it here anyway to be self-consistent.\n"
205 unless $no_comments;
206
ea93df61 207 $create .=
d529894e 208 "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';\n\n";
209 }
16dc9970 210
ea93df61 211 for my $table ( $schema->get_tables ) {
535e5c95 212 my ( $table_def, $fk_def, $trigger_def, $index_def, $constraint_def ) = create_table(
e56dabb7 213 $table,
214 {
535e5c95 215 add_drop_table => $add_drop_table,
216 show_warnings => $WARN,
217 no_comments => $no_comments,
218 delay_constraints => $delay_constraints,
7769504d 219 quote_table_names => $qt,
220 quote_field_names => $qf,
e56dabb7 221 }
222 );
223 push @table_defs, @$table_def;
224 push @fk_defs, @$fk_def;
225 push @trigger_defs, @$trigger_def;
041e659f 226 push @index_defs, @$index_def;
65ffb46c 227 push @constraint_defs, @$constraint_def;
e56dabb7 228 }
229
230 my (@view_defs);
231 foreach my $view ( $schema->get_views ) {
64ac5763 232 my ( $view_def ) = create_view(
233 $view,
234 {
235 add_drop_view => $add_drop_table,
7769504d 236 quote_table_names => $qt,
64ac5763 237 }
238 );
239 push @view_defs, @$view_def;
e56dabb7 240 }
241
f92d79aa 242 if (wantarray) {
243 return defined $create ? $create : (), @table_defs, @view_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs;
244 }
245 else {
38b019a3 246 $create .= join (";\n\n", @table_defs, @view_defs, @fk_defs, @index_defs, @constraint_defs);
7769504d 247 $create .= ";\n\n";
64ac5763 248 # If wantarray is not set we have to add "/" in this statement
249 # DBI->do() needs them omitted
f92d79aa 250 # triggers may NOT end with a semicolon
64ac5763 251 $create .= join "/\n\n", @trigger_defs;
252 # for last trigger
253 $create .= "/\n\n";
f92d79aa 254 return $create;
255 }
e56dabb7 256}
257
258sub create_table {
259 my ($table, $options) = @_;
7769504d 260 my $qt = $options->{quote_table_names};
261 my $qf = $options->{quote_field_names};
e56dabb7 262 my $table_name = $table->name;
7769504d 263 my $table_name_q = quote($table_name,$qt);
64ac5763 264
e56dabb7 265 my $item = '';
266 my $drop;
267 my (@create, @field_defs, @constraint_defs, @fk_defs, @trigger_defs);
16dc9970 268
38b019a3 269 push @create, "--\n-- Table: $table_name\n--" unless $options->{no_comments};
270 push @create, qq[DROP TABLE $table_name_q CASCADE CONSTRAINTS] if $options->{add_drop_table};
a7f999aa 271
f6195129 272 my ( %field_name_scope, @field_comments );
57f77285 273 for my $field ( $table->get_fields ) {
017ac5a7 274 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
275 create_field($field, $options, \%field_name_scope);
276 push @create, @$field_create if ref $field_create;
277 push @field_defs, @$field_defs if ref $field_defs;
278 push @trigger_defs, @$trigger_defs if ref $trigger_defs;
279 push @field_comments, @$field_comments if ref $field_comments;
57f77285 280 }
281
282 #
02c2af3b 283 # Table options
284 #
285 my @table_options;
286 for my $opt ( $table->options ) {
287 if ( ref $opt eq 'HASH' ) {
288 my ( $key, $value ) = each %$opt;
289 if ( ref $value eq 'ARRAY' ) {
290 push @table_options, "$key\n(\n". join ("\n",
ea93df61 291 map { " $_->[0]\t$_->[1]" }
02c2af3b 292 map { [ each %$_ ] }
293 @$value
294 )."\n)";
295 }
296 elsif ( !defined $value ) {
297 push @table_options, $key;
298 }
299 else {
300 push @table_options, "$key $value";
301 }
302 }
303 }
304
305 #
57f77285 306 # Table constraints
307 #
57f77285 308 for my $c ( $table->get_constraints ) {
309 my $name = $c->name || '';
38b019a3 310 my @fields = map { quote($_,$qf) } $c->fields;
b307a0db 311 my @rfields = map { quote($_,$qf) } $c->reference_fields;
312
4dfb0380 313 next if !@fields && $c->type ne CHECK_C;
57f77285 314
315 if ( $c->type eq PRIMARY_KEY ) {
d4977f1c 316 # create a name if delay_constraints
317 $name ||= mk_name( $table_name, 'pk' )
318 if $options->{delay_constraints};
7769504d 319 $name = quote($name,$qf);
54e61f1f 320 push @constraint_defs, ($name ? "CONSTRAINT $name " : '') .
7769504d 321 'PRIMARY KEY (' . join( ', ', @fields ) . ')';
57f77285 322 }
323 elsif ( $c->type eq UNIQUE ) {
7769504d 324 # Don't create UNIQUE constraints identical to the primary key
325 if ( my $pk = $table->primary_key ) {
326 my $u_fields = join(":", @fields);
327 my $pk_fields = join(":", $pk->fields);
328 next if $u_fields eq $pk_fields;
329 }
330
331 if ($name) {
332 # Force prepend of table_name as ORACLE doesn't allow duplicate
333 # CONSTRAINT names even for different tables (ORA-02264)
3b9249fb 334 $name = mk_name( "${table_name}_$name", 'u' ) unless $name =~ /^$table_name/;
7769504d 335 }
336 else {
38b019a3 337 $name = mk_name( $table_name, 'u' );
7769504d 338 }
339
340 $name = quote($name, $qf);
6d4ce9b6 341
b0c196d4 342 for my $f ( $c->fields ) {
343 my $field_def = $table->get_field( $f ) or next;
15861005 344 my $dtype = $translate{ ref $field_def->data_type eq "ARRAY" ? $field_def->data_type->[0] : $field_def->data_type} or next;
b0c196d4 345 if ( $WARN && $dtype =~ /clob/i ) {
346 warn "Oracle will not allow UNIQUE constraints on " .
347 "CLOB field '" . $field_def->table->name . '.' .
348 $field_def->name . ".'\n"
349 }
350 }
6d4ce9b6 351
57f77285 352 push @constraint_defs, "CONSTRAINT $name UNIQUE " .
353 '(' . join( ', ', @fields ) . ')';
354 }
4dfb0380 355 elsif ( $c->type eq CHECK_C ) {
54e61f1f 356 $name ||= mk_name( $name || $table_name, 'ck' );
7769504d 357 $name = quote($name, $qf);
4dfb0380 358 my $expression = $c->expression || '';
359 push @constraint_defs, "CONSTRAINT $name CHECK ($expression)";
360 }
57f77285 361 elsif ( $c->type eq FOREIGN_KEY ) {
541d6e24 362 $name = mk_name( join('_', $table_name, $c->fields). '_fk' );
7769504d 363 $name = quote($name, $qf);
0d3badf1 364 my $on_delete = uc ($c->on_delete || '');
365
cd617ba8 366 my $def = "CONSTRAINT $name FOREIGN KEY ";
367
368 if ( @fields ) {
1c899510 369 $def .= '(' . join( ', ', @fields ) . ')';
cd617ba8 370 }
371
38b019a3 372 my $ref_table = quote($c->reference_table,$qt);
1c899510 373
374 $def .= " REFERENCES $ref_table";
57f77285 375
376 if ( @rfields ) {
377 $def .= ' (' . join( ', ', @rfields ) . ')';
378 }
379
380 if ( $c->match_type ) {
ea93df61 381 $def .= ' MATCH ' .
57f77285 382 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
383 }
384
0d3badf1 385 if ( $on_delete && $on_delete ne "RESTRICT") {
5863ad87 386 $def .= ' ON DELETE '.$c->on_delete;
57f77285 387 }
388
541d6e24 389 # disabled by plu 2007-12-29 - doesn't exist for oracle
390 #if ( $c->on_update ) {
5863ad87 391 # $def .= ' ON UPDATE '. $c->on_update;
541d6e24 392 #}
57f77285 393
38b019a3 394 push @fk_defs, sprintf("ALTER TABLE %s ADD %s", $table_name_q, $def);
57f77285 395 }
16dc9970 396 }
397
398 #
399 # Index Declarations
400 #
57f77285 401 my @index_defs = ();
57f77285 402 for my $index ( $table->get_indices ) {
403 my $index_name = $index->name || '';
404 my $index_type = $index->type || NORMAL;
38b019a3 405 my @fields = map { quote($_, $qf) } $index->fields;
44fcd0b5 406 next unless @fields;
16dc9970 407
bdd8e79a 408 my @index_options;
409 for my $opt ( $index->options ) {
410 if ( ref $opt eq 'HASH' ) {
411 my ( $key, $value ) = each %$opt;
412 if ( ref $value eq 'ARRAY' ) {
413 push @table_options, "$key\n(\n". join ("\n",
ea93df61 414 map { " $_->[0]\t$_->[1]" }
bdd8e79a 415 map { [ each %$_ ] }
416 @$value
417 )."\n)";
418 }
419 elsif ( !defined $value ) {
420 push @index_options, $key;
421 }
422 else {
423 push @index_options, "$key $value";
424 }
425 }
426 }
427 my $index_options = @index_options
428 ? "\n".join("\n", @index_options) : '';
429
57f77285 430 if ( $index_type eq PRIMARY_KEY ) {
ea93df61 431 $index_name = $index_name ? mk_name( $index_name )
6d4ce9b6 432 : mk_name( $table_name, 'pk' );
7769504d 433 $index_name = quote($index_name, $qf);
57f77285 434 push @field_defs, 'CONSTRAINT '.$index_name.' PRIMARY KEY '.
16dc9970 435 '(' . join( ', ', @fields ) . ')';
436 }
57f77285 437 elsif ( $index_type eq NORMAL ) {
ea93df61 438 $index_name = $index_name ? mk_name( $index_name )
6d4ce9b6 439 : mk_name( $table_name, $index_name || 'i' );
7769504d 440 $index_name = quote($index_name, $qf);
ea93df61 441 push @index_defs,
1ced2a25 442 "CREATE INDEX $index_name on $table_name_q (".
ea93df61 443 join( ', ', @fields ).
4dec2e49 444 ")$index_options";
16dc9970 445 }
041e659f 446 elsif ( $index_type eq UNIQUE ) {
ea93df61 447 $index_name = $index_name ? mk_name( $index_name )
041e659f 448 : mk_name( $table_name, $index_name || 'i' );
7769504d 449 $index_name = quote($index_name, $qf);
ea93df61 450 push @index_defs,
1ced2a25 451 "CREATE UNIQUE INDEX $index_name on $table_name_q (".
ea93df61 452 join( ', ', @fields ).
453 ")$index_options";
041e659f 454 }
16dc9970 455 else {
96844cae 456 warn "Unknown index type ($index_type) on table $table_name.\n"
457 if $WARN;
16dc9970 458 }
459 }
460
9fc9bfb1 461 if ( my @table_comments = $table->comments ) {
462 for my $comment ( @table_comments ) {
463 next unless $comment;
b89d5635 464 $comment =~ s/'/''/g;
38b019a3 465 push @field_comments, "COMMENT ON TABLE $table_name_q is\n '".
4dec2e49 466 $comment . "'" unless $options->{no_comments}
9fc9bfb1 467 ;
468 }
469 }
470
ea93df61 471 my $table_options = @table_options
02c2af3b 472 ? "\n".join("\n", @table_options) : '';
38b019a3 473 push @create, "CREATE TABLE $table_name_q (\n" .
65ffb46c 474 join( ",\n", map { " $_" } @field_defs,
475 ($options->{delay_constraints} ? () : @constraint_defs) ) .
4dec2e49 476 "\n)$table_options";
65ffb46c 477
38b019a3 478 @constraint_defs = map { "ALTER TABLE $table_name_q ADD $_" }
65ffb46c 479 @constraint_defs;
16dc9970 480
96844cae 481 if ( $WARN ) {
482 if ( %truncated ) {
483 warn "Truncated " . keys( %truncated ) . " names:\n";
484 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
485 }
96844cae 486 }
487
65ffb46c 488 return \@create, \@fk_defs, \@trigger_defs, \@index_defs, ($options->{delay_constraints} ? \@constraint_defs : []);
e56dabb7 489}
490
017ac5a7 491sub alter_field {
492 my ($from_field, $to_field, $options) = @_;
493
7769504d 494 my $qt = $options->{quote_table_names};
017ac5a7 495 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
496 create_field($to_field, $options, {});
497
d888d445 498 # Fix ORA-01442
499 if ($to_field->is_nullable && !$from_field->is_nullable) {
500 die 'Cannot remove NOT NULL from table field';
501 } elsif (!$from_field->is_nullable && !$to_field->is_nullable) {
502 @$field_defs = map { s/ NOT NULL//; $_} @$field_defs;
503 }
504
1ced2a25 505 my $table_name = quote($to_field->table->name,$qt);
017ac5a7 506
38b019a3 507 return 'ALTER TABLE '.$table_name.' MODIFY ( '.join('', @$field_defs).' )';
017ac5a7 508}
509
fe9f1470 510sub add_field {
511 my ($new_field, $options) = @_;
512
7769504d 513 my $qt = $options->{quote_table_names};
fe9f1470 514 my ($field_create, $field_defs, $trigger_defs, $field_comments) =
515 create_field($new_field, $options, {});
516
1ced2a25 517 my $table_name = quote($new_field->table->name,$qt);
fe9f1470 518
519 my $out = sprintf('ALTER TABLE %s ADD ( %s )',
38b019a3 520 $table_name,
fe9f1470 521 join('', @$field_defs));
522 return $out;
523}
524
017ac5a7 525sub create_field {
526 my ($field, $options, $field_name_scope) = @_;
7769504d 527 my $qf = $options->{quote_field_names};
528 my $qt = $options->{quote_table_names};
017ac5a7 529
530 my (@create, @field_defs, @trigger_defs, @field_comments);
531
532 my $table_name = $field->table->name;
38b019a3 533 my $table_name_q = quote($table_name, $qt);
017ac5a7 534
535 #
536 # Field name
537 #
538 my $field_name = mk_name(
539 $field->name, '', $field_name_scope, 1
540 );
7769504d 541 my $field_name_q = quote($field_name, $qf);
38b019a3 542 my $field_def = quote($field_name, $qf);
543 $field->name( $field_name );
017ac5a7 544
545 #
546 # Datatype
547 #
548 my $check;
549 my $data_type = lc $field->data_type;
550 my @size = $field->size;
551 my %extra = $field->extra;
552 my $list = $extra{'list'} || [];
553 # \todo deal with embedded quotes
554 my $commalist = join( ', ', map { qq['$_'] } @$list );
555
556 if ( $data_type eq 'enum' ) {
38b019a3 557 $check = "CHECK ($field_name_q IN ($commalist))";
017ac5a7 558 $data_type = 'varchar2';
559 }
560 elsif ( $data_type eq 'set' ) {
ea93df61 561 # XXX add a CHECK constraint maybe
017ac5a7 562 # (trickier and slower, than enum :)
563 $data_type = 'varchar2';
564 }
565 else {
7769504d 566 if (defined $translate{ $data_type }) {
567 if (ref $translate{ $data_type } eq "ARRAY") {
568 ($data_type,$size[0]) = @{$translate{ $data_type }};
569 } else {
570 $data_type = $translate{ $data_type };
571 }
572 }
15861005 573 $data_type ||= 'varchar2';
017ac5a7 574 }
7769504d 575
e3aac687 576 # ensure size is not bigger than max size oracle allows for data type
577 if ( defined $max_size{$data_type} ) {
578 for ( my $i = 0 ; $i < scalar @size ; $i++ ) {
579 my $max =
580 ref( $max_size{$data_type} ) eq 'ARRAY'
581 ? $max_size{$data_type}->[$i]
582 : $max_size{$data_type};
583 $size[$i] = $max if $size[$i] > $max;
584 }
585 }
017ac5a7 586
587 #
ea93df61 588 # Fixes ORA-02329: column of datatype LOB cannot be
017ac5a7 589 # unique or a primary key
590 #
591 if ( $data_type eq 'clob' && $field->is_primary_key ) {
592 $data_type = 'varchar2';
593 $size[0] = 4000;
594 warn "CLOB cannot be a primary key, changing to VARCHAR2\n"
595 if $WARN;
596 }
597
598 if ( $data_type eq 'clob' && $field->is_unique ) {
599 $data_type = 'varchar2';
600 $size[0] = 4000;
601 warn "CLOB cannot be a unique key, changing to VARCHAR2\n"
602 if $WARN;
603 }
604
605 #
606 # Fixes ORA-00907: missing right parenthesis
607 #
608 if ( $data_type =~ /(date|clob)/i ) {
609 undef @size;
610 }
611
e19efc15 612 #
613 # Fixes ORA-00906: missing right parenthesis
ea93df61 614 # if size is 0 or undefined
e19efc15 615 #
616 for (qw/varchar2/) {
617 if ( $data_type =~ /^($_)$/i ) {
618 $size[0] ||= $max_size{$_};
619 }
620 }
621
017ac5a7 622 $field_def .= " $data_type";
623 if ( defined $size[0] && $size[0] > 0 ) {
9190556b 624 $field_def .= '(' . join( ',', @size ) . ')';
017ac5a7 625 }
626
627 #
628 # Default value
629 #
630 my $default = $field->default_value;
631 if ( defined $default ) {
632 #
ea93df61 633 # Wherein we try to catch a string being used as
017ac5a7 634 # a default value for a numerical field. If "true/false,"
635 # then sub "1/0," otherwise just test the truthity of the
636 # argument and use that (naive?).
637 #
bc8e2aa1 638 if (ref $default and defined $$default) {
639 $default = $$default;
640 } elsif (ref $default) {
641 $default = 'NULL';
ea93df61 642 } elsif (
643 $data_type =~ /^number$/i &&
017ac5a7 644 $default !~ /^-?\d+$/ &&
645 $default !~ m/null/i
646 ) {
647 if ( $default =~ /^true$/i ) {
648 $default = "'1'";
649 } elsif ( $default =~ /^false$/i ) {
650 $default = "'0'";
651 } else {
652 $default = $default ? "'1'" : "'0'";
653 }
ea93df61 654 } elsif (
017ac5a7 655 $data_type =~ /date/ && (
ea93df61 656 $default eq 'current_timestamp'
017ac5a7 657 ||
ea93df61 658 $default eq 'now()'
017ac5a7 659 )
660 ) {
661 $default = 'SYSDATE';
662 } else {
663 $default = $default =~ m/null/i ? 'NULL' : "'$default'"
ea93df61 664 }
017ac5a7 665
666 $field_def .= " DEFAULT $default",
667 }
668
669 #
670 # Not null constraint
671 #
672 unless ( $field->is_nullable ) {
673 $field_def .= ' NOT NULL';
674 }
675
676 $field_def .= " $check" if $check;
677
678 #
679 # Auto_increment
680 #
681 if ( $field->is_auto_increment ) {
38b019a3 682 my $base_name = $table_name . "_". $field_name;
683 my $seq_name = quote(mk_name( $base_name, 'sq' ),$qt);
684 my $trigger_name = quote(mk_name( $base_name, 'ai' ),$qt);
017ac5a7 685
4dec2e49 686 push @create, qq[DROP SEQUENCE $seq_name] if $options->{add_drop_table};
687 push @create, "CREATE SEQUENCE $seq_name";
535e5c95 688 my $trigger =
017ac5a7 689 "CREATE OR REPLACE TRIGGER $trigger_name\n" .
38b019a3 690 "BEFORE INSERT ON $table_name_q\n" .
017ac5a7 691 "FOR EACH ROW WHEN (\n" .
38b019a3 692 " new.$field_name_q IS NULL".
693 " OR new.$field_name_q = 0\n".
017ac5a7 694 ")\n".
695 "BEGIN\n" .
696 " SELECT $seq_name.nextval\n" .
38b019a3 697 " INTO :new." . $field_name_q."\n" .
017ac5a7 698 " FROM dual;\n" .
535e5c95 699 "END;\n";
7769504d 700
535e5c95 701 push @trigger_defs, $trigger;
017ac5a7 702 }
703
704 if ( lc $field->data_type eq 'timestamp' ) {
38b019a3 705 my $base_name = $table_name . "_". $field_name;
706 my $trig_name = quote(mk_name( $base_name, 'ts' ), $qt);
ea93df61 707 my $trigger =
017ac5a7 708 "CREATE OR REPLACE TRIGGER $trig_name\n".
38b019a3 709 "BEFORE INSERT OR UPDATE ON $table_name_q\n".
710 "FOR EACH ROW WHEN (new.$field_name_q IS NULL)\n".
017ac5a7 711 "BEGIN \n".
38b019a3 712 " SELECT sysdate INTO :new.$field_name_q FROM dual;\n".
535e5c95 713 "END;\n";
714
535e5c95 715 push @trigger_defs, $trigger;
017ac5a7 716 }
717
718 push @field_defs, $field_def;
719
720 if ( my $comment = $field->comments ) {
721 $comment =~ s/'/''/g;
ea93df61 722 push @field_comments,
1ced2a25 723 "COMMENT ON COLUMN $table_name_q.$field_name_q is\n '" .
017ac5a7 724 $comment . "';" unless $options->{no_comments};
725 }
726
727 return \@create, \@field_defs, \@trigger_defs, \@field_comments;
728
729}
730
731
e56dabb7 732sub create_view {
64ac5763 733 my ($view, $options) = @_;
7769504d 734 my $qt = $options->{quote_table_names};
38b019a3 735 my $view_name = quote($view->name,$qt);
ea93df61 736
64ac5763 737 my @create;
738 push @create, qq[DROP VIEW $view_name]
739 if $options->{add_drop_view};
e56dabb7 740
64ac5763 741 push @create, sprintf("CREATE VIEW %s AS\n%s",
742 $view_name,
e56dabb7 743 $view->sql);
744
64ac5763 745 return \@create;
16dc9970 746}
747
d529894e 748sub mk_name {
ea93df61 749 my $basename = shift || '';
750 my $type = shift || '';
1c899510 751 $type = '' if $type =~ /^\d/;
ea93df61 752 my $scope = shift || '';
57f77285 753 my $critical = shift || '';
d529894e 754 my $basename_orig = $basename;
ea93df61 755 my $max_name = $type
756 ? $max_id_length - (length($type) + 1)
f5087552 757 : $max_id_length;
ea93df61 758 $basename = substr( $basename, 0, $max_name )
96844cae 759 if length( $basename ) > $max_name;
d529894e 760 my $name = $type ? "${type}_$basename" : $basename;
761
762 if ( $basename ne $basename_orig and $critical ) {
763 my $show_type = $type ? "+'$type'" : "";
764 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
96844cae 765 "character limit to make '$name'\n" if $WARN;
766 $truncated{ $basename_orig } = $name;
d529894e 767 }
768
769 $scope ||= \%global_names;
96844cae 770 if ( my $prev = $scope->{ $name } ) {
771 my $name_orig = $name;
b89d5635 772 substr($name, $max_id_length - 2) = ""
773 if length( $name ) >= $max_id_length - 1;
774 $name .= sprintf( "%02d", $prev++ );
96844cae 775
776 warn "The name '$name_orig' has been changed to ",
777 "'$name' to make it unique.\n" if $WARN;
778
779 $scope->{ $name_orig }++;
780 }
781
782 $scope->{ $name }++;
d529894e 783 return $name;
784}
785
38b019a3 7861;
d529894e 787
38b019a3 788sub quote {
789 my ($name, $q) = @_;
7769504d 790 $q && $name ? "$quote_char$name$quote_char" : $name;
d529894e 791}
792
16dc9970 793
d529894e 794# -------------------------------------------------------------------
16dc9970 795# All bad art is the result of good intentions.
796# Oscar Wilde
d529894e 797# -------------------------------------------------------------------
16dc9970 798
ba096dc4 799=pod
16dc9970 800
d529894e 801=head1 CREDITS
802
ba096dc4 803Mad props to Tim Bunce for much of the logic stolen from his "mysql2ora"
804script.
16dc9970 805
42ef836a 806=head1 AUTHORS
16dc9970 807
42ef836a 808Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
38b019a3 809Alexander Hartmaier E<lt>abraxxa@cpan.orgE<gt>,
810Fabien Wernli E<lt>faxmodem@cpan.orgE<gt>.
16dc9970 811
812=head1 SEE ALSO
813
ba096dc4 814SQL::Translator, DDL::Oracle, mysql2ora.
16dc9970 815
816=cut
DDL Tool (dbsrgits@git.shadowcat.co.uk:SQL-Translator.git)