Mercurial > pub > Impl
annotate Lib/IMPL/SQL/Schema/MySQL/Formatter.pm @ 393:69a1f1508696
minor security refactoring
| author | cin |
|---|---|
| date | Fri, 14 Feb 2014 16:41:12 +0400 |
| parents | 2f06250bab5f |
| children |
| rev | line source |
|---|---|
| 271 | 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', | |
|
283
2f06250bab5f
*IMPL::SQL::MySQL fixed issues with foreign keys and drop table
sergey
parents:
272
diff
changeset
|
16 TraitsCreateTable => '-IMPL::SQL::Schema::Traits::CreateTable', |
| 271 | 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 | |
| 272 | 439 if ( my $rule = $constraint->{onDelete} ) { |
| 440 $rule = uc($rule); | |
| 271 | 441 grep $_ eq $rule, @fkRules |
| 442 or die Exception->new( "Invalid onDelete rule specified", | |
| 443 $constraint->{name}, $rule ); | |
| 444 | |
| 445 push @parts, 'ON DELETE', $rule; | |
| 446 } | |
| 447 | |
| 272 | 448 if ( my $rule = $constraint->{onUpdate} ) { |
| 449 $rule = uc($rule); | |
| 271 | 450 grep $_ eq $rule, @fkRules |
| 451 or die Exception->new( "Invalid onUpdate rule specified", | |
| 452 $constraint->{name}, $rule ); | |
| 453 | |
| 454 push @parts, 'ON UPDATE', $rule; | |
| 455 } | |
| 456 | |
| 457 } | |
| 458 else { | |
| 459 if ( $constraint->constraintClass eq PrimaryKey ) { | |
| 460 push @parts, 'PRIMARY KEY'; | |
| 461 | |
| 462 } | |
| 463 elsif ( $constraint->constraintClass eq UniqueIndex ) { | |
| 464 push @parts, 'UNIQUE', $self->quote_names( $constraint->{name} ); | |
| 465 } | |
| 466 elsif ( $constraint->constraintClass eq Index ) { | |
| 467 push @parts, 'INDEX', $self->quote_names( $constraint->{name} ); | |
| 468 } | |
| 469 else { | |
| 470 die Exception->new( 'Invalid constraint type', | |
| 471 $constraint->constraintClass ); | |
| 472 } | |
| 473 | |
| 474 push @parts, | |
| 475 '(', | |
| 476 join( ', ', $self->quote_names( @{ $constraint->{columns} || [] } ) ), | |
| 477 ')'; | |
| 478 } | |
| 479 | |
| 480 | |
| 481 return join ' ', @parts; | |
| 482 } | |
| 483 | |
| 484 sub FormatAlterTableAddConstraint { | |
| 485 my ( $self, $op ) = @_; | |
| 486 | |
| 487 return join(' ', | |
| 488 'ALTER TABLE', | |
| 489 $self->quote_names( $op->tableName ), | |
| 490 'ADD', | |
| 491 $self->FormatConstraint($op->constraint), | |
| 492 ';' | |
| 493 ); | |
| 494 } | |
| 495 | |
| 496 sub FormatAlterTableDropConstraint { | |
| 497 my ( $self, $op, $constraintType ) = @_; | |
| 498 | |
| 499 my @parts = ( 'ALTER TABLE', $self->quote_names( $op->tableName ), 'DROP' ); | |
| 500 | |
| 501 if ( $constraintType eq PrimaryKey ) { | |
| 502 push @parts, 'PRIMARY KEY'; | |
| 503 } | |
| 504 elsif ( $constraintType eq ForeignKey ) { | |
| 505 push @parts, 'FOREIGN KEY', $self->quote_names( $op->constraintName ); | |
| 506 } | |
| 507 elsif ( $constraintType eq UniqueIndex or $constraintType eq Index ) { | |
| 508 push @parts, 'INDEX', $self->quote_names( $op->constraintName ); | |
| 509 } | |
| 510 else { | |
| 511 die Exception->new( | |
| 512 'Invalid constraint type', $op->tableName, | |
| 513 $op->constraintName, $constraintType | |
| 514 ); | |
| 515 } | |
| 516 | |
| 517 push @parts, ';'; | |
| 518 | |
| 519 return join ' ', @parts; | |
| 520 } | |
| 521 | |
| 522 sub Format { | |
| 523 my $self = shift; | |
| 524 my ($op) = @_; | |
| 525 | |
| 526 my $formatter = $TRAITS_FORMATS{ref $op} | |
| 527 or die OpException->new("Don't know how to format the specified operation", $op); | |
| 528 | |
| 529 $self->$formatter(@_); | |
| 530 } | |
| 531 | |
| 532 sub _Column2Traits { | |
| 533 my ( $self, $column, %options ) = @_; | |
| 534 | |
| 535 return new IMPL::SQL::Schema::Traits::Column( | |
| 536 $column->name, | |
| 537 $column->type, | |
| 538 isNullable => $column->isNullable, | |
| 539 defaultValue => $column->defaultValue, | |
| 540 tag => $column->tag, | |
| 541 %options | |
| 542 ); | |
| 543 } | |
| 544 | |
| 545 1; | |
| 546 | |
| 547 __END__ | |
| 548 | |
| 549 =pod | |
| 550 | |
| 551 =head1 NAME | |
| 552 | |
| 553 C<IMPL::SQL::Traits::MysqlFormatter> - преобразует операции над схемой в C<SQL> | |
| 554 выражения. | |
| 555 | |
| 556 =head1 DESCRIPTION | |
| 557 | |
| 558 Используется для форматирования операций изменения схемы БД. Осуществляет | |
| 559 правильное экранирование имен, форматирование значений, имен типов данных. | |
| 560 | |
| 561 =cut |