1 package SQL::Translator::Producer::Sybase;
5 SQL::Translator::Producer::Sybase - Sybase producer for SQL::Translator
11 my $t = SQL::Translator->new( parser => '...', producer => 'Sybase' );
16 This module will produce text output of the schema suitable for Sybase.
21 use vars qw[ $DEBUG $WARN $VERSION ];
23 $DEBUG = 1 unless defined $DEBUG;
26 use SQL::Translator::Schema::Constants;
27 use SQL::Translator::Utils qw(debug header_comment);
38 varchar2 => 'varchar',
39 timestamp => 'datetime',
41 real => 'double precision',
44 tinyint => 'smallint',
45 float => 'double precision',
52 my %reserved = map { $_, 1 } qw[
53 ALL ANALYSE ANALYZE AND ANY AS ASC
55 CASE CAST CHECK COLLATE COLUMN CONSTRAINT CROSS
56 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
57 DEFAULT DEFERRABLE DESC DISTINCT DO
59 FALSE FOR FOREIGN FREEZE FROM FULL
61 ILIKE IN INITIALLY INNER INTERSECT INTO IS ISNULL
62 JOIN LEADING LEFT LIKE LIMIT
63 NATURAL NEW NOT NOTNULL NULL
64 OFF OFFSET OLD ON ONLY OR ORDER OUTER OVERLAPS
65 PRIMARY PUBLIC REFERENCES RIGHT
66 SELECT SESSION_USER SOME TABLE THEN TO TRAILING TRUE
67 UNION UNIQUE USER USING VERBOSE WHEN WHERE
70 my $max_id_length = 30;
71 my %used_identifiers = ();
78 =head1 Sybase Create Table Syntax
80 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
81 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
82 | table_constraint } [, ... ]
84 [ INHERITS ( parent_table [, ... ] ) ]
85 [ WITH OIDS | WITHOUT OIDS ]
87 where column_constraint is:
89 [ CONSTRAINT constraint_name ]
90 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
92 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
93 [ ON DELETE action ] [ ON UPDATE action ] }
94 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
96 and table_constraint is:
98 [ CONSTRAINT constraint_name ]
99 { UNIQUE ( column_name [, ... ] ) |
100 PRIMARY KEY ( column_name [, ... ] ) |
101 CHECK ( expression ) |
102 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
103 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
104 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
106 =head1 Create Index Syntax
108 CREATE [ UNIQUE ] INDEX index_name ON table
109 [ USING acc_method ] ( column [ ops_name ] [, ...] )
111 CREATE [ UNIQUE ] INDEX index_name ON table
112 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
118 my $translator = shift;
119 $DEBUG = $translator->debug;
120 $WARN = $translator->show_warnings;
121 my $no_comments = $translator->no_comments;
122 my $add_drop_table = $translator->add_drop_table;
123 my $schema = $translator->schema;
126 $output .= header_comment unless ($no_comments);
128 for my $table ( $schema->get_tables ) {
129 my $table_name = $table->name or next;
130 $table_name = mk_name( $table_name, '', undef, 1 );
131 my $table_name_ur = unreserve($table_name) || '';
133 my ( @comments, @field_defs, @index_defs, @constraint_defs );
135 push @comments, "--\n-- Table: $table_name_ur\n--" unless $no_comments;
137 push @comments, map { "-- $_" } $table->comments;
142 my %field_name_scope;
143 for my $field ( $table->get_fields ) {
144 my $field_name = mk_name(
145 $field->name, '', \%field_name_scope, undef,1
147 my $field_name_ur = unreserve( $field_name, $table_name );
148 my $field_def = qq["$field_name_ur"];
149 $field_def =~ s/\"//g;
150 if ( $field_def =~ /identity/ ){
151 $field_def =~ s/identity/pidentity/;
157 my $data_type = lc $field->data_type;
158 my $orig_data_type = $data_type;
159 my %extra = $field->extra;
160 my $list = $extra{'list'} || [];
161 # \todo deal with embedded quotes
162 my $commalist = join( ', ', map { qq['$_'] } @$list );
165 if ( $data_type eq 'enum' ) {
166 my $check_name = mk_name(
167 $table_name.'_'.$field_name, 'chk' ,undef, 1
169 push @constraint_defs,
170 "CONSTRAINT $check_name CHECK ($field_name IN ($commalist))";
171 $data_type .= 'character varying';
173 elsif ( $data_type eq 'set' ) {
174 $data_type .= 'character varying';
176 elsif ( $field->is_auto_increment ) {
177 $field_def .= ' IDENTITY';
180 if ( defined $translate{ $data_type } ) {
181 $data_type = $translate{ $data_type };
184 warn "Unknown datatype: $data_type ",
185 "($table_name.$field_name)\n" if $WARN;
189 my $size = $field->size;
191 if ( $data_type =~ /numeric/ ) {
194 elsif ( $orig_data_type eq 'text' ) {
195 #interpret text fields as long varchars
199 $data_type eq 'varchar' &&
200 $orig_data_type eq 'boolean'
204 elsif ( $data_type eq 'varchar' ) {
209 $field_def .= " $data_type";
210 $field_def .= "($size)" if $size;
215 my $default = $field->default_value;
216 if ( defined $default ) {
217 $field_def .= sprintf( ' DEFAULT %s',
218 ( $field->is_auto_increment && $seq_name )
219 ? qq[nextval('"$seq_name"'::text)] :
220 ( $default =~ m/null/i ) ? 'NULL' : "'$default'"
225 # Not null constraint
227 unless ( $field->is_nullable ) {
228 $field_def .= ' NOT NULL';
231 $field_def .= ' NULL' if $data_type ne 'bit';
234 push @field_defs, $field_def;
238 # Constraint Declarations
240 my @constraint_decs = ();
242 for my $constraint ( $table->get_constraints ) {
243 my $name = $constraint->name || '';
244 my $type = $constraint->type || NORMAL;
245 my @fields = map { unreserve( $_, $table_name ) }
247 my @rfields = map { unreserve( $_, $table_name ) }
248 $constraint->reference_fields;
251 if ( $type eq PRIMARY_KEY ) {
252 $name ||= mk_name( $table_name, 'pk', undef,1 );
253 push @constraint_defs,
254 "CONSTRAINT $name PRIMARY KEY ".
255 '(' . join( ', ', @fields ) . ')';
257 elsif ( $type eq FOREIGN_KEY ) {
258 $name ||= mk_name( $table_name, 'fk', undef,1 );
259 push @constraint_defs,
260 "CONSTRAINT $name FOREIGN KEY".
261 ' (' . join( ', ', @fields ) . ') REFERENCES '.
262 $constraint->reference_table.
263 ' (' . join( ', ', @rfields ) . ')';
265 elsif ( $type eq UNIQUE ) {
268 $name || ++$c_name_default,undef, 1
270 push @constraint_defs,
271 "CONSTRAINT $name UNIQUE " .
272 '(' . join( ', ', @fields ) . ')';
279 for my $index ( $table->get_indices ) {
281 'CREATE INDEX ' . $index->name .
283 join( ', ', $index->fields ) . ");";
286 my $create_statement;
287 $create_statement = qq[DROP TABLE $table_name_ur;\n]
289 $create_statement .= qq[CREATE TABLE $table_name_ur (\n].
291 map { " $_" } @field_defs, @constraint_defs
296 $output .= join( "\n\n",
304 foreach my $view ( $schema->get_views ) {
305 my (@comments, $view_name);
307 $view_name = $view->name();
308 push @comments, "--\n-- View: $view_name\n--" unless $no_comments;
310 # text of view is already a 'create view' statement so no need
311 # to do anything fancy.
313 $output .= join("\n\n",
320 foreach my $procedure ( $schema->get_procedures ) {
321 my (@comments, $procedure_name);
323 $procedure_name = $procedure->name();
325 "--\n-- Procedure: $procedure_name\n--" unless $no_comments;
327 # text of procedure already has the 'create procedure' stuff
328 # so there is no need to do anything fancy. However, we should
329 # think about doing fancy stuff with granting permissions and
332 $output .= join("\n\n",
340 warn "Truncated " . keys( %truncated ) . " names:\n";
341 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
345 warn "Encounted " . keys( %unreserve ) .
346 " unsafe names in schema (reserved or invalid):\n";
347 warn "\t" . join( "\n\t", sort keys %unreserve ) . "\n";
355 my $basename = shift || '';
356 my $type = shift || '';
357 my $scope = shift || '';
358 my $critical = shift || '';
359 my $basename_orig = $basename;
361 ? $max_id_length - (length($type) + 1)
363 $basename = substr( $basename, 0, $max_name )
364 if length( $basename ) > $max_name;
365 my $name = $type ? "${type}_$basename" : $basename;
367 if ( $basename ne $basename_orig and $critical ) {
368 my $show_type = $type ? "+'$type'" : "";
369 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
370 "character limit to make '$name'\n" if $WARN;
371 $truncated{ $basename_orig } = $name;
374 $scope ||= \%global_names;
375 if ( my $prev = $scope->{ $name } ) {
376 my $name_orig = $name;
377 $name .= sprintf( "%02d", ++$prev );
378 substr($name, $max_id_length - 3) = "00"
379 if length( $name ) > $max_id_length;
381 warn "The name '$name_orig' has been changed to ",
382 "'$name' to make it unique.\n" if $WARN;
384 $scope->{ $name_orig }++;
386 $name = substr( $name, 0, $max_id_length )
387 if ((length( $name ) > $max_id_length) && $critical);
393 my $name = shift || '';
394 my $schema_obj_name = shift || '';
395 my ( $suffix ) = ( $name =~ s/(\W.*)$// ) ? $1 : '';
397 # also trap fields that don't begin with a letter
398 return $name if !$reserved{ uc $name } && $name =~ /^[a-z]/i;
400 if ( $schema_obj_name ) {
401 ++$unreserve{"$schema_obj_name.$name"};
404 ++$unreserve{"$name (table name)"};
407 my $unreserve = sprintf '%s_', $name;
408 return $unreserve.$suffix;
421 Sam Angiuoli E<lt>angiuoli@users.sourceforge.netE<gt>,
422 Paul Harrington E<lt>harringp@deshaw.comE<gt>,
423 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.