Mercurial > pub > Impl

comparison Lib/IMPL/SQL/Schema/MySQL/Formatter.pm @ 271:56364d0c4b4f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
+IMPL::SQL::Schema::MySQL: added basic support for MySQL
author cin
date Mon, 28 Jan 2013 02:43:14 +0400
parents
children 47db27ed5b43
comparison
equal deleted inserted replaced
270:3f59fd828d5f 271:56364d0c4b4f
1 package IMPL::SQL::Schema::MySQL::Formatter;
2 use strict;
3
4 use IMPL::lang qw(is);
5 use IMPL::require {
6 Exception => 'IMPL::Exception',
7 OpException => '-IMPL::InvalidOperationException',
8 ArgException => '-IMPL::InvalidArgumentException',
9 PrimaryKey => '-IMPL::SQL::Schema::Constraint::PrimaryKey',
10 UniqueIndex => '-IMPL::SQL::Schema::Constraint::Unique',
11 Index => '-IMPL::SQL::Schema::Constraint::Index',
12 ForeignKey => '-IMPL::SQL::Schema::Constraint::ForeignKey',
13 CharType => '-IMPL::SQL::Schema::MySQL::CharType',
14 EnumType => '-IMPL::SQL::Schema::MySQL::EnumType',
15 TraitsDropTable => '-IMPL::SQL::Schema::Traits::DropTable',
16 TraitsCreateTable => '-IMPL::SQL::Schema::Traits::CreateTable',
17 TraitsAlterTableDropConstraint => '-IMPL::SQL::Schema::Traits::AlterTableDropConstraint',
18 TraitsAlterTableAddConstraint => '-IMPL::SQL::Schema::Traits::AlterTableAddConstraint',
19 TraitsAlterTableDropColumn => '-IMPL::SQL::Schema::Traits::AlterTableDropColumn',
20 TraitsAlterTableAddColumn => '-IMPL::SQL::Schema::Traits::AlterTableAddColumn',
21 TraitsAlterTableChangeColumn => '-IMPL::SQL::Schema::Traits::AlterTableChangeColumn'
22 };
23
24 our %TRAITS_FORMATS = (
25 TraitsDropTable, 'FormatDropTable',
26 TraitsCreateTable, 'FormatCreateTable',
27 TraitsAlterTableDropConstraint, 'FormatAlterTableDropConstraint',
28 TraitsAlterTableAddConstraint, 'FormatAlterTableAddConstraint',
29 TraitsAlterTableDropColumn, 'FormatAlterTableDropColumn',
30 TraitsAlterTableAddColumn, 'FormatAlterTableAddColumn',
31 TraitsAlterTableChangeColumn, 'FormatAlterTableChangeColumn'
32 );
33
34 sub quote {
35 my $self = shift;
36
37 if (wantarray) {
38 return map { my $str = $_; $str =~ s/'/''/g; "'$str'"; } @_;
39 }
40 else {
41 return join '', map { my $str = $_; $str =~ s/'/''/g; "'$str'"; } @_;
42 }
43 }
44
45 sub quote_names {
46 my $self = shift;
47
48 if (wantarray) {
49 return map { my $str = $_; $str =~ s/`/``/g; "`$str`"; } @_;
50 }
51 else {
52 return join '', map { my $str = $_; $str =~ s/`/``/g; "`$str`"; } @_;
53 }
54 }
55
56 sub formatTypeNameInteger {
57 my ( $self, $type ) = @_;
58
59 return
60 $type->name
61 . ( $type->maxLength ? '(' . $type->maxLength . ')' : '' )
62 . ( $type->unsigned ? ' UNSIGNED' : '' )
63 . ( $type->zerofill ? ' ZEROFILL' : '' );
64 }
65
66 sub formatTypeNameReal {
67 my ( $self, $type ) = @_;
68
69 return $type->name
70 . ( $type->maxLength
71 ? '(' . $type->maxLength . ', ' . $type->scale . ')'
72 : '' )
73 . ( $type->unsigned ? ' UNSIGNED' : '' )
74 . ( $type->zerofill ? ' ZEROFILL' : '' );
75 }
76
77 sub formatTypeNameNumeric {
78 my ( $self, $type ) = @_;
79 $type->maxLength
80 or die ArgException->new(
81 type => 'The length and precission must be specified',
82 $type->name
83 );
84 return $type->name
85 . ( $type->maxLength
86 ? '(' . $type->maxLength . ', ' . $type->scale . ')'
87 : '' )
88 . ( $type->unsigned ? ' UNSIGNED' : '' )
89 . ( $type->zerofill ? ' ZEROFILL' : '' );
90 }
91
92 sub formatTypeName {
93 my ( $self, $type ) = @_;
94 return $type->name;
95 }
96
97 sub formatTypeNameChar {
98 my ( $self, $type ) = @_;
99
100 return ($type->name . '('
101 . $type->MaxLength . ')'
102 . ( is( $type, CharType ) ? $type->encoding : '' ) );
103 }
104
105 sub formatTypeNameVarChar {
106 my ( $self, $type ) = @_;
107
108 return ($type->name . '('
109 . $type->maxLength . ')'
110 . ( is( $type, CharType ) ? $type->encoding : '' ) );
111 }
112
113 sub formatTypeNameEnum {
114 my ( $self, $type ) = @_;
115
116 die ArgException->new( type => 'Invalid enum type' )
117 unless is( $type, EnumType );
118 return ($type->name . '('
119 . join( ',', map { $self->quote($_) } $type->enumValues )
120 . ')' );
121 }
122
123 sub formatStringValue {
124 my ( $self, $value ) = @_;
125
126 if ( ref $value eq 'SCALAR' ) {
127 return $$value;
128 }
129 else {
130 return $self->quote($value);
131 }
132 }
133
134 sub formatNumberValue {
135 my ( $self, $value ) = @_;
136
137 if ( ref $value eq 'SCALAR' ) {
138 return $$value;
139 }
140 else {
141 $value =~ /^((\+|-)\s*)?\d+(\.\d+)?(e(\+|-)?\d+)?$/
142 or die ArgException->new(
143 value => 'The specified value isn\'t a valid number',
144 $value
145 );
146 return $value;
147 }
148 }
149
150 our %TYPES_FORMATS = (
151 TINYINT => {
152 formatType => \&formatTypeNameInteger,
153 formatValue => \&formatNumberValue
154 },
155 SMALLINT => {
156 formatType => \&formatTypeNameInteger,
157 formatValue => \&formatNumberValue
158 },
159 MEDIUMINT => {
160 formatType => \&formatTypeNameInteger,
161 formatValue => \&formatNumberValue
162 },
163 INT => {
164 formatType => \&formatTypeNameInteger,
165 formatValue => \&formatNumberValue
166 },
167 INTEGER => {
168 formatType => \&formatTypeNameInteger,
169 formatValue => \&formatNumberValue
170 },
171 BIGINT => {
172 formatType => \&formatTypeNameInteger,
173 formatValue => \&formatNumberValue
174 },
175 REAL => {
176 formatType => \&formatTypeNameReal,
177 formatValue => \&formatNumberValue
178 },
179 DOUBLE => {
180 formatType => \&formatTypeNameReal,
181 formatValue => \&formatNumberValue
182 },
183 FLOAT => {
184 formatType => \&formatTypeNameReal,
185 formatValue => \&formatNumberValue
186 },
187 DECIMAL => {
188 formatType => \&formatTypeNameNumeric,
189 formatValue => \&formatNumberValue
190 },
191 NUMERIC => {
192 formatType => \&formatTypeNameNumeric,
193 formatValue => \&formatNumberValue
194 },
195 DATE => {
196 formatType => \&formatTypeName,
197 formatValue => \&formatStringValue
198 },
199 TIME => {
200 formatType => \&formatTypeName,
201 formatValue => \&formatStringValue
202 },
203 TIMESTAMP => {
204 formatType => \&formatTypeName,
205 formatValue => \&formatStringValue
206 },
207 DATETIME => {
208 formatType => \&formatTypeName,
209 formatValue => \&formatStringValue
210 },
211 CHAR => {
212 formatType => \&formatTypeNameChar,
213 formatValue => \&formatStringValue
214 },
215 VARCHAR => {
216 formatType => \&formatTypeNameVarChar,
217 formatValue => \&formatStringValue
218 },
219 TINYBLOB => {
220 formatType => \&formatTypeName,
221 formatValue => \&formatStringValue
222 },
223 BLOB => {
224 formatType => \&formatTypeName,
225 formatValue => \&formatStringValue
226 },
227 MEDIUMBLOB => {
228 formatType => \&formatTypeName,
229 formatValue => \&formatStringValue
230 },
231 LONGBLOB => {
232 formatType => \&formatTypeName,
233 formatValue => \&formatStringValue
234 },
235 TINYTEXT => {
236 formatType => \&formatTypeName,
237 formatValue => \&formatStringValue
238 },
239 TEXT => {
240 formatType => \&formatTypeName,
241 formatValue => \&formatStringValue
242 },
243 MEDIUMTEXT => {
244 formatType => \&formatTypeName,
245 formatValue => \&formatStringValue
246 },
247 LONGTEXT => {
248 formatType => \&formatTypeName,
249 formatValue => \&formatStringValue
250 },
251 ENUM => {
252 formatType => \&formatTypeNameEnum,
253 formatValue => \&formatStringValue
254 },
255 SET => {
256 formatType => \&formatTypeNameEnum,
257 formatValue => \&formatStringValue
258 }
259 );
260
261 sub FormatTypeName {
262 my ( $self, $type ) = @_;
263
264 my $fn = $TYPES_FORMATS{ $type->name }{formatType}
265 or die ArgException->new( type => "The specified type is unknown",
266 $type->name );
267
268 return $self->$fn($type);
269 }
270
271 sub FormatValue {
272 my ( $self, $value, $type ) = @_;
273
274 my $fn = $TYPES_FORMATS{ $type->name }{formatValue}
275 or die ArgException->new( type => "The specified type is unknown",
276 $type->name );
277
278 return $self->$fn( $value, $type );
279 }
280
281 sub FormatColumn {
282 my ( $self, $column ) = @_;
283
284 my @parts = (
285 $self->quote_names( $column->{name} ),
286 $self->FormatTypeName( $column->{type} ),
287 $column->{isNullable} ? 'NULL' : 'NOT NULL'
288 );
289
290 push @parts, $self->FormatValue( $column->{defaultValue}, $column->{type} )
291 if $column->{defaultValue};
292
293 push @parts, 'AUTO_INCREMENT'
294 if $column->{tag} and $column->{tag}->{auto_increment};
295
296 return join ' ', @parts;
297 }
298
299 sub FormatCreateTable {
300 my ( $self, $op ) = @_;
301
302 my $table = $op->table;
303
304 my @lines;
305 my @body;
306
307 push @lines, "CREATE TABLE " . $self->quote_names($table->{name}) . "(";
308
309 push @body, map { " " . $self->FormatColumn($_) } @{ $table->{columns} }
310 if $table->{columns};
311
312 push @body, map { " " . $self->FormatConstraint($_) } @{ $table->{constraints} }
313 if $table->{constraints};
314
315 push @lines, join(",\n", @body);
316
317 push @lines, ");";
318
319 return join "\n", @lines;
320 }
321
322 sub FormatDropTable {
323 my ( $self, $op ) = @_;
324
325 return join ' ', 'DROP TABLE', $self->quote_names( $op->tableName ), ';';
326 }
327
328 sub FormatRenameTable {
329 my ( $self, $op ) = @_;
330
331 return join ' ',
332 'ALTER TABLE',
333 $self->quote_names( $op->tableName ),
334 'RENAME TO',
335 $self->quote_names( $op->tableNewName ),
336 ';';
337 }
338
339 sub FormatAlterTableAddColumn {
340 my ( $self, $op, $schema ) = @_;
341
342 my @parts = (
343 'ALTER TABLE',$self->quote_names($op->tableName), 'ADD COLUMN',
344 $self->FormatColumn( $op->column )
345 );
346
347 if ( defined $op->position ) {
348
349 # mysql supports column reordering
350 # the new location is specified relative to the previous column
351 # to determine the name of the previous column we need to ask the schema
352
353 my $table = $schema->GetTable( $op->tableName );
354
355 if ( $op->position == 0 ) {
356 push @parts, 'FIRST';
357 }
358 else {
359 push @parts, 'AFTER';
360
361 my $prevColumn = $table->GetColumnAt( $op->position - 1 );
362 push @parts, $self->quote_names( $prevColumn->{name} );
363 }
364 }
365
366 push @parts, ';';
367
368 return join ' ', @parts;
369 }
370
371 sub FormatAlterTableDropColumn {
372 my ( $self, $op ) = @_;
373
374 return join ' ',
375 'ALTER TABLE',
376 $self->quote_names( $op->tableName ),
377 'DROP COLUMN',
378 $self->quote_names( $op->columnName ),
379 ';';
380 }
381
382 sub FormatAlterTableChangeColumn {
383 my ( $self, $op, $schema ) = @_;
384
385 my $table = $schema->GetTable( $op->tableName );
386 my $column = $table->GetColumn( $op->columnName );
387
388 my @parts = (
389 'ALTER TABLE',
390 $self->quote_names( $op->tableName ),
391 'MODIFY COLUMN',
392 $self->quote_names( $op->columnName ),
393 $self->FormatColumn( $self->_Column2Traits($column) )
394 );
395
396 if ( defined $op->position ) {
397
398 # mysql supports column reordering
399 # the new location is specified relative to the previous column
400 # to determine the name of the previous column we need to ask the schema
401
402 if ( $op->position == 0 ) {
403 push @parts, 'FIRST';
404 }
405 else {
406 push @parts, 'AFTER';
407
408 my $prevColumn = $table->GetColumnAt( $op->position - 1 );
409 push @parts, $self->quote_names( $prevColumn->{name} );
410 }
411 }
412
413 push @parts, ';';
414 return join ' ', @parts;
415 }
416
417 sub FormatConstraint {
418 my ($self,$constraint) = @_;
419
420 my @fkRules =
421 ( 'RESTRICT', 'CASCADE', 'SET NULL', 'SET DEFAULT', 'NO ACTION' );
422
423 my @parts;
424
425 if ( $constraint->constraintClass eq ForeignKey ) {
426 push @parts,
427 'CONSTRAINT',
428 $self->quote_names( $constraint->{name} ),
429 'FOREIGN KEY',
430 $self->quote_names( $constraint->{name} ),
431 '(',
432 join( ', ', $self->quote_names( @{ $constraint->{columns} || [] } ) ),
433 ')',
434 'REFERENCES', $self->quote_names( $constraint->{foreignTable} ), '(',
435 join( ', ',
436 $self->quote_names( @{ $constraint->{foreignColumns} || [] } ) ),
437 ')';
438
439 if ( my $rule = uc( $constraint->{onDelete} ) ) {
440 grep $_ eq $rule, @fkRules
441 or die Exception->new( "Invalid onDelete rule specified",
442 $constraint->{name}, $rule );
443
444 push @parts, 'ON DELETE', $rule;
445 }
446
447 if ( my $rule = uc( $constraint->{onUpdate} ) ) {
448 grep $_ eq $rule, @fkRules
449 or die Exception->new( "Invalid onUpdate rule specified",
450 $constraint->{name}, $rule );
451
452 push @parts, 'ON UPDATE', $rule;
453 }
454
455 }
456 else {
457 if ( $constraint->constraintClass eq PrimaryKey ) {
458 push @parts, 'PRIMARY KEY';
459
460 }
461 elsif ( $constraint->constraintClass eq UniqueIndex ) {
462 push @parts, 'UNIQUE', $self->quote_names( $constraint->{name} );
463 }
464 elsif ( $constraint->constraintClass eq Index ) {
465 push @parts, 'INDEX', $self->quote_names( $constraint->{name} );
466 }
467 else {
468 die Exception->new( 'Invalid constraint type',
469 $constraint->constraintClass );
470 }
471
472 push @parts,
473 '(',
474 join( ', ', $self->quote_names( @{ $constraint->{columns} || [] } ) ),
475 ')';
476 }
477
478
479 return join ' ', @parts;
480 }
481
482 sub FormatAlterTableAddConstraint {
483 my ( $self, $op ) = @_;
484
485 return join(' ',
486 'ALTER TABLE',
487 $self->quote_names( $op->tableName ),
488 'ADD',
489 $self->FormatConstraint($op->constraint),
490 ';'
491 );
492 }
493
494 sub FormatAlterTableDropConstraint {
495 my ( $self, $op, $constraintType ) = @_;
496
497 my @parts = ( 'ALTER TABLE', $self->quote_names( $op->tableName ), 'DROP' );
498
499 if ( $constraintType eq PrimaryKey ) {
500 push @parts, 'PRIMARY KEY';
501 }
502 elsif ( $constraintType eq ForeignKey ) {
503 push @parts, 'FOREIGN KEY', $self->quote_names( $op->constraintName );
504 }
505 elsif ( $constraintType eq UniqueIndex or $constraintType eq Index ) {
506 push @parts, 'INDEX', $self->quote_names( $op->constraintName );
507 }
508 else {
509 die Exception->new(
510 'Invalid constraint type', $op->tableName,
511 $op->constraintName, $constraintType
512 );
513 }
514
515 push @parts, ';';
516
517 return join ' ', @parts;
518 }
519
520 sub Format {
521 my $self = shift;
522 my ($op) = @_;
523
524 my $formatter = $TRAITS_FORMATS{ref $op}
525 or die OpException->new("Don't know how to format the specified operation", $op);
526
527 $self->$formatter(@_);
528 }
529
530 sub _Column2Traits {
531 my ( $self, $column, %options ) = @_;
532
533 return new IMPL::SQL::Schema::Traits::Column(
534 $column->name,
535 $column->type,
536 isNullable => $column->isNullable,
537 defaultValue => $column->defaultValue,
538 tag => $column->tag,
539 %options
540 );
541 }
542
543 1;
544
545 __END__
546
547 =pod
548
549 =head1 NAME
550
551 C<IMPL::SQL::Traits::MysqlFormatter> - преобразует операции над схемой в C<SQL>
552 выражения.
553
554 =head1 DESCRIPTION
555
556 Используется для форматирования операций изменения схемы БД. Осуществляет
557 правильное экранирование имен, форматирование значений, имен типов данных.
558
559 =cut