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 ] )
117 # -------------------------------------------------------------------
119 my $translator = shift;
120 $DEBUG = $translator->debug;
121 $WARN = $translator->show_warnings;
122 my $no_comments = $translator->no_comments;
123 my $add_drop_table = $translator->add_drop_table;
124 my $schema = $translator->schema;
127 $output .= header_comment unless ($no_comments);
129 for my $table ( $schema->get_tables ) {
130 my $table_name = $table->name or next;
131 $table_name = mk_name( $table_name, '', undef, 1 );
132 my $table_name_ur = unreserve($table_name) || '';
134 my ( @comments, @field_defs, @index_defs, @constraint_defs );
136 push @comments, "--\n-- Table: $table_name_ur\n--" unless $no_comments;
138 push @comments, map { "-- $_" } $table->comments;
143 my %field_name_scope;
144 for my $field ( $table->get_fields ) {
145 my $field_name = mk_name(
146 $field->name, '', \%field_name_scope, undef,1
148 my $field_name_ur = unreserve( $field_name, $table_name );
149 my $field_def = qq["$field_name_ur"];
150 $field_def =~ s/\"//g;
151 if ( $field_def =~ /identity/ ){
152 $field_def =~ s/identity/pidentity/;
158 my $data_type = lc $field->data_type;
159 my $orig_data_type = $data_type;
160 my %extra = $field->extra;
161 my $list = $extra{'list'} || [];
162 # \todo deal with embedded quotes
163 my $commalist = join( ', ', map { qq['$_'] } @$list );
166 if ( $data_type eq 'enum' ) {
167 my $check_name = mk_name(
168 $table_name.'_'.$field_name, 'chk' ,undef, 1
170 push @constraint_defs,
171 "CONSTRAINT $check_name CHECK ($field_name IN ($commalist))";
172 $data_type .= 'character varying';
174 elsif ( $data_type eq 'set' ) {
175 $data_type .= 'character varying';
177 elsif ( $field->is_auto_increment ) {
178 $field_def .= ' IDENTITY';
181 if ( defined $translate{ $data_type } ) {
182 $data_type = $translate{ $data_type };
185 warn "Unknown datatype: $data_type ",
186 "($table_name.$field_name)\n" if $WARN;
190 my $size = $field->size;
192 if ( $data_type =~ /numeric/ ) {
195 elsif ( $orig_data_type eq 'text' ) {
196 #interpret text fields as long varchars
200 $data_type eq 'varchar' &&
201 $orig_data_type eq 'boolean'
205 elsif ( $data_type eq 'varchar' ) {
210 $field_def .= " $data_type";
211 $field_def .= "($size)" if $size;
216 my $default = $field->default_value;
217 if ( defined $default ) {
218 $field_def .= sprintf( ' DEFAULT %s',
219 ( $field->is_auto_increment && $seq_name )
220 ? qq[nextval('"$seq_name"'::text)] :
221 ( $default =~ m/null/i ) ? 'NULL' : "'$default'"
226 # Not null constraint
228 unless ( $field->is_nullable ) {
229 $field_def .= ' NOT NULL';
232 $field_def .= ' NULL' if $data_type ne 'bit';
235 push @field_defs, $field_def;
239 # Constraint Declarations
241 my @constraint_decs = ();
243 for my $constraint ( $table->get_constraints ) {
244 my $name = $constraint->name || '';
245 my $type = $constraint->type || NORMAL;
246 my @fields = map { unreserve( $_, $table_name ) }
248 my @rfields = map { unreserve( $_, $table_name ) }
249 $constraint->reference_fields;
252 if ( $type eq PRIMARY_KEY ) {
253 $name ||= mk_name( $table_name, 'pk', undef,1 );
254 push @constraint_defs,
255 "CONSTRAINT $name PRIMARY KEY ".
256 '(' . join( ', ', @fields ) . ')';
258 elsif ( $type eq FOREIGN_KEY ) {
259 $name ||= mk_name( $table_name, 'fk', undef,1 );
260 push @constraint_defs,
261 "CONSTRAINT $name FOREIGN KEY".
262 ' (' . join( ', ', @fields ) . ') REFERENCES '.
263 $constraint->reference_table.
264 ' (' . join( ', ', @rfields ) . ')';
266 elsif ( $type eq UNIQUE ) {
269 $name || ++$c_name_default,undef, 1
271 push @constraint_defs,
272 "CONSTRAINT $name UNIQUE " .
273 '(' . join( ', ', @fields ) . ')';
280 for my $index ( $table->get_indices ) {
282 'CREATE INDEX ' . $index->name .
284 join( ', ', $index->fields ) . ");";
287 my $create_statement;
288 $create_statement = qq[DROP TABLE $table_name_ur;\n]
290 $create_statement .= qq[CREATE TABLE $table_name_ur (\n].
292 map { " $_" } @field_defs, @constraint_defs
297 $output .= join( "\n\n",
305 foreach my $view ( $schema->get_views ) {
306 my (@comments, $view_name);
308 $view_name = $view->name();
309 push @comments, "--\n-- View: $view_name\n--" unless $no_comments;
311 # text of view is already a 'create view' statement so no need
312 # to do anything fancy.
314 $output .= join("\n\n",
321 foreach my $procedure ( $schema->get_procedures ) {
322 my (@comments, $procedure_name);
324 $procedure_name = $procedure->name();
326 "--\n-- Procedure: $procedure_name\n--" unless $no_comments;
328 # text of procedure already has the 'create procedure' stuff
329 # so there is no need to do anything fancy. However, we should
330 # think about doing fancy stuff with granting permissions and
333 $output .= join("\n\n",
341 warn "Truncated " . keys( %truncated ) . " names:\n";
342 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
346 warn "Encounted " . keys( %unreserve ) .
347 " unsafe names in schema (reserved or invalid):\n";
348 warn "\t" . join( "\n\t", sort keys %unreserve ) . "\n";
355 # -------------------------------------------------------------------
357 my $basename = shift || '';
358 my $type = shift || '';
359 my $scope = shift || '';
360 my $critical = shift || '';
361 my $basename_orig = $basename;
363 ? $max_id_length - (length($type) + 1)
365 $basename = substr( $basename, 0, $max_name )
366 if length( $basename ) > $max_name;
367 my $name = $type ? "${type}_$basename" : $basename;
369 if ( $basename ne $basename_orig and $critical ) {
370 my $show_type = $type ? "+'$type'" : "";
371 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
372 "character limit to make '$name'\n" if $WARN;
373 $truncated{ $basename_orig } = $name;
376 $scope ||= \%global_names;
377 if ( my $prev = $scope->{ $name } ) {
378 my $name_orig = $name;
379 $name .= sprintf( "%02d", ++$prev );
380 substr($name, $max_id_length - 3) = "00"
381 if length( $name ) > $max_id_length;
383 warn "The name '$name_orig' has been changed to ",
384 "'$name' to make it unique.\n" if $WARN;
386 $scope->{ $name_orig }++;
388 $name = substr( $name, 0, $max_id_length )
389 if ((length( $name ) > $max_id_length) && $critical);
394 # -------------------------------------------------------------------
396 my $name = shift || '';
397 my $schema_obj_name = shift || '';
398 my ( $suffix ) = ( $name =~ s/(\W.*)$// ) ? $1 : '';
400 # also trap fields that don't begin with a letter
401 return $name if !$reserved{ uc $name } && $name =~ /^[a-z]/i;
403 if ( $schema_obj_name ) {
404 ++$unreserve{"$schema_obj_name.$name"};
407 ++$unreserve{"$name (table name)"};
410 my $unreserve = sprintf '%s_', $name;
411 return $unreserve.$suffix;
416 # -------------------------------------------------------------------
426 Sam Angiuoli E<lt>angiuoli@users.sourceforge.netE<gt>,
427 Paul Harrington E<lt>harringp@deshaw.comE<gt>,
428 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.