| Commit | Line | Data |
|---|
| d0c2b8e9 |
1 | use MooseX::Declare; |
| 2 | role SQL::Translator::Grammar::PostgreSQL { |
| 3 | # ------------------------------------------------------------------- |
| 4 | # Copyright (C) 2002-2009 SQLFairy Authors |
| 5 | # |
| 6 | # This program is free software; you can redistribute it and/or |
| 7 | # modify it under the terms of the GNU General Public License as |
| 8 | # published by the Free Software Foundation; version 2. |
| 9 | # |
| 10 | # This program is distributed in the hope that it will be useful, but |
| 11 | # WITHOUT ANY WARRANTY; without even the implied warranty of |
| 12 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
| 13 | # General Public License for more details. |
| 14 | # |
| 15 | # You should have received a copy of the GNU General Public License |
| 16 | # along with this program; if not, write to the Free Software |
| 17 | # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA |
| 18 | # 02111-1307 USA |
| 19 | # ------------------------------------------------------------------- |
| 20 | |
| 21 | method _build_grammar { |
| 22 | return q! |
| 23 | { my ( %tables, @views, $table_order, $field_order, @table_comments) } |
| 24 | |
| 25 | # |
| 26 | # The "eofile" rule makes the parser fail if any "statement" rule |
| 27 | # fails. Otherwise, the first successful match by a "statement" |
| 28 | # won't cause the failure needed to know that the parse, as a whole, |
| 29 | # failed. -ky |
| 30 | # |
| 31 | startrule : statement(s) eofile { { tables => \%tables, views => \@views } } |
| 32 | |
| 33 | eofile : /^\Z/ |
| 34 | |
| 35 | |
| 36 | statement : create |
| 37 | | comment_on_table |
| 38 | | comment_on_column |
| 39 | | comment_on_other |
| 40 | | comment |
| 41 | | alter |
| 42 | | grant |
| 43 | | revoke |
| 44 | | drop |
| 45 | | insert |
| 46 | | connect |
| 47 | | update |
| 48 | | set |
| 49 | | select |
| 50 | | copy |
| 51 | | readin_symbol |
| 52 | | <error> |
| 53 | |
| 54 | connect : /^\s*\\\connect.*\n/ |
| 55 | |
| 56 | set : /set/i /[^;]*/ ';' |
| 57 | |
| 58 | revoke : /revoke/i WORD(s /,/) /on/i TABLE(?) table_id /from/i name_with_opt_quotes(s /,/) ';' |
| 59 | { |
| 60 | my $table_info = $item{'table_id'}; |
| 61 | my $schema_name = $table_info->{'schema_name'}; |
| 62 | my $table_name = $table_info->{'table_name'}; |
| 63 | push @{ $tables{ $table_name }{'permissions'} }, { |
| 64 | type => 'revoke', |
| 65 | actions => $item[2], |
| 66 | users => $item[7], |
| 67 | } |
| 68 | } |
| 69 | |
| 70 | revoke : /revoke/i WORD(s /,/) /on/i SCHEMA(?) schema_name /from/i name_with_opt_quotes(s /,/) ';' |
| 71 | { 1 } |
| 72 | |
| 73 | grant : /grant/i WORD(s /,/) /on/i TABLE(?) table_id /to/i name_with_opt_quotes(s /,/) ';' |
| 74 | { |
| 75 | my $table_info = $item{'table_id'}; |
| 76 | my $schema_name = $table_info->{'schema_name'}; |
| 77 | my $table_name = $table_info->{'table_name'}; |
| 78 | push @{ $tables{ $table_name }{'permissions'} }, { |
| 79 | type => 'grant', |
| 80 | actions => $item[2], |
| 81 | users => $item[7], |
| 82 | } |
| 83 | } |
| 84 | |
| 85 | grant : /grant/i WORD(s /,/) /on/i SCHEMA(?) schema_name /to/i name_with_opt_quotes(s /,/) ';' |
| 86 | { 1 } |
| 87 | |
| 88 | drop : /drop/i /[^;]*/ ';' |
| 89 | |
| 90 | string : |
| 91 | /'(\\.|''|[^\\\'])*'/ |
| 92 | |
| 93 | nonstring : /[^;\'"]+/ |
| 94 | |
| 95 | statement_body : string | nonstring |
| 96 | |
| 97 | insert : /insert/i statement_body(s?) ';' |
| 98 | |
| 99 | update : /update/i statement_body(s?) ';' |
| 100 | |
| 101 | # |
| 102 | # Create table. |
| 103 | # |
| 104 | create : CREATE temporary(?) TABLE table_id '(' create_definition(s? /,/) ')' table_option(s?) ';' |
| 105 | { |
| 106 | my $table_info = $item{'table_id'}; |
| 107 | my $schema_name = $table_info->{'schema_name'}; |
| 108 | my $table_name = $table_info->{'table_name'}; |
| 109 | $tables{ $table_name }{'order'} = ++$table_order; |
| 110 | $tables{ $table_name }{'schema_name'} = $schema_name; |
| 111 | $tables{ $table_name }{'table_name'} = $table_name; |
| 112 | |
| 113 | $tables{ $table_name }{'temporary'} = $item[2][0]; |
| 114 | |
| 115 | if ( @table_comments ) { |
| 116 | $tables{ $table_name }{'comments'} = [ @table_comments ]; |
| 117 | @table_comments = (); |
| 118 | } |
| 119 | |
| 120 | my @constraints; |
| 121 | for my $definition ( @{ $item[6] } ) { |
| 122 | if ( $definition->{'supertype'} eq 'field' ) { |
| 123 | my $field_name = $definition->{'name'}; |
| 124 | $tables{ $table_name }{'fields'}{ $field_name } = |
| 125 | { %$definition, order => $field_order++ }; |
| 126 | |
| 127 | for my $constraint ( @{ $definition->{'constraints'} || [] } ) { |
| 128 | $constraint->{'fields'} = [ $field_name ]; |
| 129 | push @{ $tables{ $table_name }{'constraints'} }, |
| 130 | $constraint; |
| 131 | } |
| 132 | } |
| 133 | elsif ( $definition->{'supertype'} eq 'constraint' ) { |
| 134 | push @{ $tables{ $table_name }{'constraints'} }, $definition; |
| 135 | } |
| 136 | elsif ( $definition->{'supertype'} eq 'index' ) { |
| 137 | push @{ $tables{ $table_name }{'indices'} }, $definition; |
| 138 | } |
| 139 | } |
| 140 | |
| 141 | for my $option ( @{ $item[8] } ) { |
| 142 | $tables{ $table_name }{'table_options(s?)'}{ $option->{'type'} } = |
| 143 | $option; |
| 144 | } |
| 145 | |
| 146 | 1; |
| 147 | } |
| 148 | |
| 149 | create : CREATE unique(?) /(index|key)/i index_name /on/i table_id using_method(?) '(' field_name(s /,/) ')' where_predicate(?) ';' |
| 150 | { |
| 151 | my $table_info = $item{'table_id'}; |
| 152 | my $schema_name = $table_info->{'schema_name'}; |
| 153 | my $table_name = $table_info->{'table_name'}; |
| 154 | push @{ $tables{ $table_name }{'indices'} }, |
| 155 | { |
| 156 | name => $item{'index_name'}, |
| 157 | supertype => $item{'unique'}[0] ? 'constraint' : 'index', |
| 158 | type => $item{'unique'}[0] ? 'unique' : 'normal', |
| 159 | fields => $item[9], |
| 160 | method => $item{'using_method'}[0], |
| 161 | } |
| 162 | ; |
| 163 | } |
| 164 | |
| 165 | create : CREATE or_replace(?) temporary(?) VIEW view_id view_fields(?) /AS/i view_target ';' |
| 166 | { |
| 167 | push @views, { |
| 168 | schema_name => $item{view_id}{schema_name}, |
| 169 | view_name => $item{view_id}{view_name}, |
| 170 | sql => $item{view_target}, |
| 171 | fields => $item[6], |
| 172 | is_temporary => $item[3][0], |
| 173 | } |
| 174 | } |
| 175 | |
| 176 | # |
| 177 | # Create anything else (e.g., domain, etc.) |
| 178 | # |
| 179 | create : CREATE WORD /[^;]+/ ';' |
| 180 | { @table_comments = (); } |
| 181 | |
| 182 | using_method : /using/i WORD { $item[2] } |
| 183 | |
| 184 | where_predicate : /where/i /[^;]+/ |
| 185 | |
| 186 | create_definition : field |
| 187 | | table_constraint |
| 188 | | <error> |
| 189 | |
| 190 | comment : /^\s*(?:#|-{2})(.*)\n/ |
| 191 | { |
| 192 | my $comment = $item[1]; |
| 193 | $comment =~ s/^\s*(#|-*)\s*//; |
| 194 | $comment =~ s/\s*$//; |
| 195 | $return = $comment; |
| 196 | push @table_comments, $comment; |
| 197 | } |
| 198 | |
| 199 | comment_on_table : /comment/i /on/i /table/i table_id /is/i comment_phrase ';' |
| 200 | { |
| 201 | my $table_info = $item{'table_id'}; |
| 202 | my $schema_name = $table_info->{'schema_name'}; |
| 203 | my $table_name = $table_info->{'table_name'}; |
| 204 | push @{ $tables{ $table_name }{'comments'} }, $item{'comment_phrase'}; |
| 205 | } |
| 206 | |
| 207 | comment_on_column : /comment/i /on/i /column/i column_name /is/i comment_phrase ';' |
| 208 | { |
| 209 | my $table_name = $item[4]->{'table'}; |
| 210 | my $field_name = $item[4]->{'field'}; |
| 211 | if ($tables{ $table_name }{'fields'}{ $field_name } ) { |
| 212 | push @{ $tables{ $table_name }{'fields'}{ $field_name }{'comments'} }, |
| 213 | $item{'comment_phrase'}; |
| 214 | } |
| 215 | else { |
| 216 | die "No such column as $table_name.$field_name"; |
| 217 | } |
| 218 | } |
| 219 | |
| 220 | comment_on_other : /comment/i /on/i /\w+/ /\w+/ /is/i comment_phrase ';' |
| 221 | { |
| 222 | push(@table_comments, $item{'comment_phrase'}); |
| 223 | } |
| 224 | |
| 225 | # [added by cjm 20041019] |
| 226 | # [TODO: other comment-on types] |
| 227 | # for now we just have a general mechanism for handling other |
| 228 | # kinds of comments than table/column; I'm not sure of the best |
| 229 | # way to incorporate these into the datamodel |
| 230 | # |
| 231 | # this is the exhaustive list of types of comment: |
| 232 | #COMMENT ON DATABASE my_database IS 'Development Database'; |
| 233 | #COMMENT ON INDEX my_index IS 'Enforces uniqueness on employee id'; |
| 234 | #COMMENT ON RULE my_rule IS 'Logs UPDATES of employee records'; |
| 235 | #COMMENT ON SEQUENCE my_sequence IS 'Used to generate primary keys'; |
| 236 | #COMMENT ON TABLE my_table IS 'Employee Information'; |
| 237 | #COMMENT ON TYPE my_type IS 'Complex Number support'; |
| 238 | #COMMENT ON VIEW my_view IS 'View of departmental costs'; |
| 239 | #COMMENT ON COLUMN my_table.my_field IS 'Employee ID number'; |
| 240 | #COMMENT ON TRIGGER my_trigger ON my_table IS 'Used for R.I.'; |
| 241 | # |
| 242 | # this is tested by test 08 |
| 243 | |
| 244 | column_name : NAME '.' NAME |
| 245 | { $return = { table => $item[1], field => $item[3] } } |
| 246 | |
| 247 | comment_phrase : /null/i |
| 248 | { $return = 'NULL' } |
| 249 | |
| 250 | comment_phrase : /'/ comment_phrase_unquoted(s) /'/ |
| 251 | { my $phrase = join(' ', @{ $item[2] }); |
| 252 | $return = $phrase} |
| 253 | |
| 254 | # [cjm TODO: double-single quotes in a comment_phrase] |
| 255 | comment_phrase_unquoted : /[^\']*/ |
| 256 | { $return = $item[1] } |
| 257 | |
| 258 | |
| 259 | xxxcomment_phrase : /'.*?'|NULL/ |
| 260 | { |
| 261 | my $val = $item[1] || ''; |
| 262 | $val =~ s/^'|'$//g; |
| 263 | $return = $val; |
| 264 | } |
| 265 | |
| 266 | field : field_comment(s?) field_name data_type field_meta(s?) field_comment(s?) |
| 267 | { |
| 268 | my ( $default, @constraints, $is_pk ); |
| 269 | my $is_nullable = 1; |
| 270 | for my $meta ( @{ $item[4] } ) { |
| 271 | if ( $meta->{'type'} eq 'default' ) { |
| 272 | $default = $meta; |
| 273 | next; |
| 274 | } |
| 275 | elsif ( $meta->{'type'} eq 'not_null' ) { |
| 276 | $is_nullable = 0; |
| 277 | } |
| 278 | elsif ( $meta->{'type'} eq 'primary_key' ) { |
| 279 | $is_pk = 1; |
| 280 | } |
| 281 | |
| 282 | push @constraints, $meta if $meta->{'supertype'} eq 'constraint'; |
| 283 | } |
| 284 | |
| 285 | my @comments = ( @{ $item[1] }, @{ $item[5] } ); |
| 286 | |
| 287 | $return = { |
| 288 | supertype => 'field', |
| 289 | name => $item{'field_name'}, |
| 290 | data_type => $item{'data_type'}{'type'}, |
| 291 | size => $item{'data_type'}{'size'}, |
| 292 | is_nullable => $is_nullable, |
| 293 | default => $default->{'value'}, |
| 294 | constraints => [ @constraints ], |
| 295 | comments => [ @comments ], |
| 296 | is_primary_key => $is_pk || 0, |
| 297 | is_auto_increment => $item{'data_type'}{'is_auto_increment'}, |
| 298 | } |
| 299 | } |
| 300 | | <error> |
| 301 | |
| 302 | field_comment : /^\s*(?:#|-{2})(.*)\n/ |
| 303 | { |
| 304 | my $comment = $item[1]; |
| 305 | $comment =~ s/^\s*(#|-*)\s*//; |
| 306 | $comment =~ s/\s*$//; |
| 307 | $return = $comment; |
| 308 | } |
| 309 | |
| 310 | field_meta : default_val |
| 311 | | column_constraint |
| 312 | |
| 313 | view_fields : '(' field_name(s /,/) ')' |
| 314 | { $return = join (',', @{$item[2]} ) } |
| 315 | |
| 316 | column_constraint : constraint_name(?) column_constraint_type deferrable(?) deferred(?) |
| 317 | { |
| 318 | my $desc = $item{'column_constraint_type'}; |
| 319 | my $type = $desc->{'type'}; |
| 320 | my $fields = $desc->{'fields'} || []; |
| 321 | my $expression = $desc->{'expression'} || ''; |
| 322 | |
| 323 | $return = { |
| 324 | supertype => 'constraint', |
| 325 | name => $item{'constraint_name'}[0] || '', |
| 326 | type => $type, |
| 327 | expression => $type eq 'check' ? $expression : '', |
| 328 | deferrable => $item{'deferrable'}, |
| 329 | deferred => $item{'deferred'}, |
| 330 | reference_table => $desc->{'reference_table'}, |
| 331 | reference_fields => $desc->{'reference_fields'}, |
| 332 | match_type => $desc->{'match_type'}, |
| 333 | on_delete => $desc->{'on_delete'} || $desc->{'on_delete_do'}, |
| 334 | on_update => $desc->{'on_update'} || $desc->{'on_update_do'}, |
| 335 | } |
| 336 | } |
| 337 | |
| 338 | constraint_name : /constraint/i name_with_opt_quotes { $item[2] } |
| 339 | |
| 340 | column_constraint_type : /not null/i { $return = { type => 'not_null' } } |
| 341 | | |
| 342 | /null/i |
| 343 | { $return = { type => 'null' } } |
| 344 | | |
| 345 | /unique/i |
| 346 | { $return = { type => 'unique' } } |
| 347 | | |
| 348 | /primary key/i |
| 349 | { $return = { type => 'primary_key' } } |
| 350 | | |
| 351 | /check/i '(' /[^)]+/ ')' |
| 352 | { $return = { type => 'check', expression => $item[3] } } |
| 353 | | |
| 354 | /references/i table_id parens_word_list(?) match_type(?) key_action(s?) |
| 355 | { |
| 356 | my $table_info = $item{'table_id'}; |
| 357 | my $schema_name = $table_info->{'schema_name'}; |
| 358 | my $table_name = $table_info->{'table_name'}; |
| 359 | my ( $on_delete, $on_update ); |
| 360 | for my $action ( @{ $item[5] || [] } ) { |
| 361 | $on_delete = $action->{'action'} if $action->{'type'} eq 'delete'; |
| 362 | $on_update = $action->{'action'} if $action->{'type'} eq 'update'; |
| 363 | } |
| 364 | |
| 365 | $return = { |
| 366 | type => 'foreign_key', |
| 367 | reference_table => $table_name, |
| 368 | reference_fields => $item[3][0], |
| 369 | match_type => $item[4][0], |
| 370 | on_delete => $on_delete, |
| 371 | on_update => $on_update, |
| 372 | } |
| 373 | } |
| 374 | |
| 375 | table_id : schema_qualification(?) name_with_opt_quotes { |
| 376 | $return = { schema_name => $item[1][0], table_name => $item[2] } |
| 377 | } |
| 378 | |
| 379 | view_id : schema_qualification(?) name_with_opt_quotes { |
| 380 | $return = { schema_name => $item[1][0], view_name => $item[2] } |
| 381 | } |
| 382 | |
| 383 | view_target : /select|with/i /[^;]+/ { |
| 384 | $return = "$item[1] $item[2]"; |
| 385 | } |
| 386 | |
| 387 | # SELECT views _may_ support outer parens, and we used to produce |
| 388 | # such sql, although non-standard. Use ugly lookeahead to parse |
| 389 | view_target : '(' /select/i / [^;]+ (?= \) ) /x ')' { |
| 390 | $return = "$item[2] $item[3]" |
| 391 | } |
| 392 | |
| 393 | view_target_spec : |
| 394 | |
| 395 | schema_qualification : name_with_opt_quotes '.' |
| 396 | |
| 397 | schema_name : name_with_opt_quotes |
| 398 | |
| 399 | field_name : name_with_opt_quotes |
| 400 | |
| 401 | name_with_opt_quotes : double_quote(?) NAME double_quote(?) { $item[2] } |
| 402 | |
| 403 | double_quote: /"/ |
| 404 | |
| 405 | index_name : name_with_opt_quotes |
| 406 | |
| 407 | data_type : pg_data_type parens_value_list(?) |
| 408 | { |
| 409 | my $data_type = $item[1]; |
| 410 | |
| 411 | # |
| 412 | # We can deduce some sizes from the data type's name. |
| 413 | # |
| 414 | if ( my $size = $item[2][0] ) { |
| 415 | $data_type->{'size'} = $size; |
| 416 | } |
| 417 | |
| 418 | $return = $data_type; |
| 419 | } |
| 420 | |
| 421 | pg_data_type : |
| 422 | /(bigint|int8)/i |
| 423 | { |
| 424 | $return = { |
| 425 | type => 'integer', |
| 426 | size => 20, |
| 427 | }; |
| 428 | } |
| 429 | | |
| 430 | /(smallint|int2)/i |
| 431 | { |
| 432 | $return = { |
| 433 | type => 'integer', |
| 434 | size => 5, |
| 435 | }; |
| 436 | } |
| 437 | | |
| 438 | /interval/i |
| 439 | { |
| 440 | $return = { type => 'interval' }; |
| 441 | } |
| 442 | | |
| 443 | /(integer|int4?)/i # interval must come before this |
| 444 | { |
| 445 | $return = { |
| 446 | type => 'integer', |
| 447 | size => 10, |
| 448 | }; |
| 449 | } |
| 450 | | |
| 451 | /(real|float4)/i |
| 452 | { |
| 453 | $return = { |
| 454 | type => 'real', |
| 455 | size => 10, |
| 456 | }; |
| 457 | } |
| 458 | | |
| 459 | /(double precision|float8?)/i |
| 460 | { |
| 461 | $return = { |
| 462 | type => 'float', |
| 463 | size => 20, |
| 464 | }; |
| 465 | } |
| 466 | | |
| 467 | /(bigserial|serial8)/i |
| 468 | { |
| 469 | $return = { |
| 470 | type => 'integer', |
| 471 | size => 20, |
| 472 | is_auto_increment => 1, |
| 473 | }; |
| 474 | } |
| 475 | | |
| 476 | /serial4?/i |
| 477 | { |
| 478 | $return = { |
| 479 | type => 'integer', |
| 480 | size => 11, |
| 481 | is_auto_increment => 1, |
| 482 | }; |
| 483 | } |
| 484 | | |
| 485 | /(bit varying|varbit)/i |
| 486 | { |
| 487 | $return = { type => 'varbit' }; |
| 488 | } |
| 489 | | |
| 490 | /character varying/i |
| 491 | { |
| 492 | $return = { type => 'varchar' }; |
| 493 | } |
| 494 | | |
| 495 | /char(acter)?/i |
| 496 | { |
| 497 | $return = { type => 'char' }; |
| 498 | } |
| 499 | | |
| 500 | /bool(ean)?/i |
| 501 | { |
| 502 | $return = { type => 'boolean' }; |
| 503 | } |
| 504 | | |
| 505 | /bytea/i |
| 506 | { |
| 507 | $return = { type => 'bytea' }; |
| 508 | } |
| 509 | | |
| 510 | /(timestamptz|timestamp)(?:\(\d\))?( with(out)? time zone)?/i |
| 511 | { |
| 512 | $return = { type => 'timestamp' }; |
| 513 | } |
| 514 | | |
| 515 | /text/i |
| 516 | { |
| 517 | $return = { |
| 518 | type => 'text', |
| 519 | size => 64_000, |
| 520 | }; |
| 521 | } |
| 522 | | |
| 523 | /(bit|box|cidr|circle|date|inet|line|lseg|macaddr|money|numeric|decimal|path|point|polygon|timetz|time|varchar)/i |
| 524 | { |
| 525 | $return = { type => $item[1] }; |
| 526 | } |
| 527 | |
| 528 | parens_value_list : '(' VALUE(s /,/) ')' |
| 529 | { $item[2] } |
| 530 | |
| 531 | |
| 532 | parens_word_list : '(' name_with_opt_quotes(s /,/) ')' |
| 533 | { $item[2] } |
| 534 | |
| 535 | field_size : '(' num_range ')' { $item{'num_range'} } |
| 536 | |
| 537 | num_range : DIGITS ',' DIGITS |
| 538 | { $return = $item[1].','.$item[3] } |
| 539 | | DIGITS |
| 540 | { $return = $item[1] } |
| 541 | |
| 542 | table_constraint : comment(s?) constraint_name(?) table_constraint_type deferrable(?) deferred(?) comment(s?) |
| 543 | { |
| 544 | my $desc = $item{'table_constraint_type'}; |
| 545 | my $type = $desc->{'type'}; |
| 546 | my $fields = $desc->{'fields'}; |
| 547 | my $expression = $desc->{'expression'}; |
| 548 | my @comments = ( @{ $item[1] }, @{ $item[-1] } ); |
| 549 | |
| 550 | $return = { |
| 551 | name => $item[2][0] || '', |
| 552 | supertype => 'constraint', |
| 553 | type => $type, |
| 554 | fields => $type ne 'check' ? $fields : [], |
| 555 | expression => $type eq 'check' ? $expression : '', |
| 556 | deferrable => $item{'deferrable'}, |
| 557 | deferred => $item{'deferred'}, |
| 558 | reference_table => $desc->{'reference_table'}, |
| 559 | reference_fields => $desc->{'reference_fields'}, |
| 560 | match_type => $desc->{'match_type'}[0], |
| 561 | on_delete => $desc->{'on_delete'} || $desc->{'on_delete_do'}, |
| 562 | on_update => $desc->{'on_update'} || $desc->{'on_update_do'}, |
| 563 | comments => [ @comments ], |
| 564 | } |
| 565 | } |
| 566 | |
| 567 | table_constraint_type : /primary key/i '(' name_with_opt_quotes(s /,/) ')' |
| 568 | { |
| 569 | $return = { |
| 570 | type => 'primary_key', |
| 571 | fields => $item[3], |
| 572 | } |
| 573 | } |
| 574 | | |
| 575 | /unique/i '(' name_with_opt_quotes(s /,/) ')' |
| 576 | { |
| 577 | $return = { |
| 578 | type => 'unique', |
| 579 | fields => $item[3], |
| 580 | } |
| 581 | } |
| 582 | | |
| 583 | /check/i '(' /[^)]+/ ')' |
| 584 | { |
| 585 | $return = { |
| 586 | type => 'check', |
| 587 | expression => $item[3], |
| 588 | } |
| 589 | } |
| 590 | | |
| 591 | /foreign key/i '(' name_with_opt_quotes(s /,/) ')' /references/i table_id parens_word_list(?) match_type(?) key_action(s?) |
| 592 | { |
| 593 | my ( $on_delete, $on_update ); |
| 594 | for my $action ( @{ $item[9] || [] } ) { |
| 595 | $on_delete = $action->{'action'} if $action->{'type'} eq 'delete'; |
| 596 | $on_update = $action->{'action'} if $action->{'type'} eq 'update'; |
| 597 | } |
| 598 | |
| 599 | $return = { |
| 600 | supertype => 'constraint', |
| 601 | type => 'foreign_key', |
| 602 | fields => $item[3], |
| 603 | reference_table => $item[6]->{'table_name'}, |
| 604 | reference_fields => $item[7][0], |
| 605 | match_type => $item[8][0], |
| 606 | on_delete => $on_delete || '', |
| 607 | on_update => $on_update || '', |
| 608 | } |
| 609 | } |
| 610 | |
| 611 | deferrable : not(?) /deferrable/i |
| 612 | { |
| 613 | $return = ( $item[1] =~ /not/i ) ? 0 : 1; |
| 614 | } |
| 615 | |
| 616 | deferred : /initially/i /(deferred|immediate)/i { $item[2] } |
| 617 | |
| 618 | match_type : /match full/i { 'match_full' } |
| 619 | | |
| 620 | /match partial/i { 'match_partial' } |
| 621 | |
| 622 | key_action : key_delete |
| 623 | | |
| 624 | key_update |
| 625 | |
| 626 | key_delete : /on delete/i key_mutation |
| 627 | { |
| 628 | $return = { |
| 629 | type => 'delete', |
| 630 | action => $item[2], |
| 631 | }; |
| 632 | } |
| 633 | |
| 634 | key_update : /on update/i key_mutation |
| 635 | { |
| 636 | $return = { |
| 637 | type => 'update', |
| 638 | action => $item[2], |
| 639 | }; |
| 640 | } |
| 641 | |
| 642 | key_mutation : /no action/i { $return = 'no_action' } |
| 643 | | |
| 644 | /restrict/i { $return = 'restrict' } |
| 645 | | |
| 646 | /cascade/i { $return = 'cascade' } |
| 647 | | |
| 648 | /set null/i { $return = 'set null' } |
| 649 | | |
| 650 | /set default/i { $return = 'set default' } |
| 651 | |
| 652 | alter : alter_table table_id add_column field ';' |
| 653 | { |
| 654 | my $field_def = $item[4]; |
| 655 | $tables{ $item[2]->{'table_name'} }{'fields'}{ $field_def->{'name'} } = { |
| 656 | %$field_def, order => $field_order++ |
| 657 | }; |
| 658 | 1; |
| 659 | } |
| 660 | |
| 661 | alter : alter_table table_id ADD table_constraint ';' |
| 662 | { |
| 663 | my $table_name = $item[2]->{'table_name'}; |
| 664 | my $constraint = $item[4]; |
| 665 | push @{ $tables{ $table_name }{'constraints'} }, $constraint; |
| 666 | 1; |
| 667 | } |
| 668 | |
| 669 | alter : alter_table table_id drop_column NAME restrict_or_cascade(?) ';' |
| 670 | { |
| 671 | $tables{ $item[2]->{'table_name'} }{'fields'}{ $item[4] }{'drop'} = 1; |
| 672 | 1; |
| 673 | } |
| 674 | |
| 675 | alter : alter_table table_id alter_column NAME alter_default_val ';' |
| 676 | { |
| 677 | $tables{ $item[2]->{'table_name'} }{'fields'}{ $item[4] }{'default'} = |
| 678 | $item[5]->{'value'}; |
| 679 | 1; |
| 680 | } |
| 681 | |
| 682 | # |
| 683 | # These will just parse for now but won't affect the structure. - ky |
| 684 | # |
| 685 | alter : alter_table table_id /rename/i /to/i NAME ';' |
| 686 | { 1 } |
| 687 | |
| 688 | alter : alter_table table_id alter_column NAME SET /statistics/i INTEGER ';' |
| 689 | { 1 } |
| 690 | |
| 691 | alter : alter_table table_id alter_column NAME SET /storage/i storage_type ';' |
| 692 | { 1 } |
| 693 | |
| 694 | alter : alter_table table_id rename_column NAME /to/i NAME ';' |
| 695 | { 1 } |
| 696 | |
| 697 | alter : alter_table table_id DROP /constraint/i NAME restrict_or_cascade ';' |
| 698 | { 1 } |
| 699 | |
| 700 | alter : alter_table table_id /owner/i /to/i NAME ';' |
| 701 | { 1 } |
| 702 | |
| 703 | alter : alter_sequence NAME /owned/i /by/i column_name ';' |
| 704 | { 1 } |
| 705 | |
| 706 | storage_type : /(plain|external|extended|main)/i |
| 707 | |
| 708 | temporary : /temp(orary)?\\b/i |
| 709 | { |
| 710 | 1; |
| 711 | } |
| 712 | |
| 713 | or_replace : /or replace/i |
| 714 | |
| 715 | alter_default_val : SET default_val |
| 716 | { |
| 717 | $return = { value => $item[2]->{'value'} } |
| 718 | } |
| 719 | | DROP DEFAULT |
| 720 | { |
| 721 | $return = { value => undef } |
| 722 | } |
| 723 | |
| 724 | # |
| 725 | # This is a little tricky to get right, at least WRT to making the |
| 726 | # tests pass. The problem is that the constraints are stored just as |
| 727 | # a list (no name access), and the tests expect the constraints in a |
| 728 | # particular order. I'm going to leave the rule but disable the code |
| 729 | # for now. - ky |
| 730 | # |
| 731 | alter : alter_table table_id alter_column NAME alter_nullable ';' |
| 732 | { |
| 733 | # my $table_name = $item[2]->{'table_name'}; |
| 734 | # my $field_name = $item[4]; |
| 735 | # my $is_nullable = $item[5]->{'is_nullable'}; |
| 736 | # |
| 737 | # $tables{ $table_name }{'fields'}{ $field_name }{'is_nullable'} = |
| 738 | # $is_nullable; |
| 739 | # |
| 740 | # if ( $is_nullable ) { |
| 741 | # 1; |
| 742 | # push @{ $tables{ $table_name }{'constraints'} }, { |
| 743 | # type => 'not_null', |
| 744 | # fields => [ $field_name ], |
| 745 | # }; |
| 746 | # } |
| 747 | # else { |
| 748 | # for my $i ( |
| 749 | # 0 .. $#{ $tables{ $table_name }{'constraints'} || [] } |
| 750 | # ) { |
| 751 | # my $c = $tables{ $table_name }{'constraints'}[ $i ] or next; |
| 752 | # my $fields = join( '', @{ $c->{'fields'} || [] } ) or next; |
| 753 | # if ( $c->{'type'} eq 'not_null' && $fields eq $field_name ) { |
| 754 | # delete $tables{ $table_name }{'constraints'}[ $i ]; |
| 755 | # last; |
| 756 | # } |
| 757 | # } |
| 758 | # } |
| 759 | |
| 760 | 1; |
| 761 | } |
| 762 | |
| 763 | alter_nullable : SET not_null |
| 764 | { |
| 765 | $return = { is_nullable => 0 } |
| 766 | } |
| 767 | | DROP not_null |
| 768 | { |
| 769 | $return = { is_nullable => 1 } |
| 770 | } |
| 771 | |
| 772 | not_null : /not/i /null/i |
| 773 | |
| 774 | not : /not/i |
| 775 | |
| 776 | add_column : ADD COLUMN(?) |
| 777 | |
| 778 | alter_table : ALTER TABLE ONLY(?) |
| 779 | |
| 780 | alter_sequence : ALTER SEQUENCE |
| 781 | |
| 782 | drop_column : DROP COLUMN(?) |
| 783 | |
| 784 | alter_column : ALTER COLUMN(?) |
| 785 | |
| 786 | rename_column : /rename/i COLUMN(?) |
| 787 | |
| 788 | restrict_or_cascade : /restrict/i | |
| 789 | /cascade/i |
| 790 | |
| 791 | # Handle functions that can be called |
| 792 | select : SELECT select_function ';' |
| 793 | { 1 } |
| 794 | |
| 795 | # Read the setval function but don't do anything with it because this parser |
| 796 | # isn't handling sequences |
| 797 | select_function : schema_qualification(?) /setval/i '(' VALUE /,/ VALUE /,/ /(true|false)/i ')' |
| 798 | { 1 } |
| 799 | |
| 800 | # Skipping all COPY commands |
| 801 | copy : COPY WORD /[^;]+/ ';' { 1 } |
| 802 | { 1 } |
| 803 | |
| 804 | # The "\." allows reading in from STDIN but this isn't needed for schema |
| 805 | # creation, so it is skipped. |
| 806 | readin_symbol : '\.' |
| 807 | {1} |
| 808 | |
| 809 | # |
| 810 | # End basically useless stuff. - ky |
| 811 | # |
| 812 | |
| 813 | create_table : CREATE TABLE |
| 814 | |
| 815 | create_index : CREATE /index/i |
| 816 | |
| 817 | default_val : DEFAULT /(\d+|'[^']*'|\w+\(.*\))|\w+/ |
| 818 | { |
| 819 | my $val = defined $item[2] ? $item[2] : ''; |
| 820 | $val =~ s/^'|'$//g; |
| 821 | $return = { |
| 822 | supertype => 'constraint', |
| 823 | type => 'default', |
| 824 | value => $val, |
| 825 | } |
| 826 | } |
| 827 | | /null/i |
| 828 | { |
| 829 | $return = { |
| 830 | supertype => 'constraint', |
| 831 | type => 'default', |
| 832 | value => 'NULL', |
| 833 | } |
| 834 | } |
| 835 | |
| 836 | name_with_opt_paren : NAME parens_value_list(s?) |
| 837 | { $item[2][0] ? "$item[1]($item[2][0][0])" : $item[1] } |
| 838 | |
| 839 | unique : /unique/i { 1 } |
| 840 | |
| 841 | key : /key/i | /index/i |
| 842 | |
| 843 | table_option : /inherits/i '(' name_with_opt_quotes(s /,/) ')' |
| 844 | { |
| 845 | $return = { type => 'inherits', table_name => $item[3] } |
| 846 | } |
| 847 | | |
| 848 | /with(out)? oids/i |
| 849 | { |
| 850 | $return = { type => $item[1] =~ /out/i ? 'without_oids' : 'with_oids' } |
| 851 | } |
| 852 | |
| 853 | ADD : /add/i |
| 854 | |
| 855 | ALTER : /alter/i |
| 856 | |
| 857 | CREATE : /create/i |
| 858 | |
| 859 | ONLY : /only/i |
| 860 | |
| 861 | DEFAULT : /default/i |
| 862 | |
| 863 | DROP : /drop/i |
| 864 | |
| 865 | COLUMN : /column/i |
| 866 | |
| 867 | TABLE : /table/i |
| 868 | |
| 869 | VIEW : /view/i |
| 870 | |
| 871 | SCHEMA : /schema/i |
| 872 | |
| 873 | SEMICOLON : /\s*;\n?/ |
| 874 | |
| 875 | SEQUENCE : /sequence/i |
| 876 | |
| 877 | SELECT : /select/i |
| 878 | |
| 879 | COPY : /copy/i |
| 880 | |
| 881 | INTEGER : /\d+/ |
| 882 | |
| 883 | WORD : /\w+/ |
| 884 | |
| 885 | DIGITS : /\d+/ |
| 886 | |
| 887 | COMMA : ',' |
| 888 | |
| 889 | SET : /set/i |
| 890 | |
| 891 | NAME : "`" /\w+/ "`" |
| 892 | { $item[2] } |
| 893 | | /\w+/ |
| 894 | { $item[1] } |
| 895 | | /[\$\w]+/ |
| 896 | { $item[1] } |
| 897 | |
| 898 | VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/ |
| 899 | { $item[1] } |
| 900 | | /'.*?'/ # XXX doesn't handle embedded quotes |
| 901 | { $item[1] } |
| 902 | | /null/i |
| 903 | { 'NULL' } |
| 904 | !; |
| 905 | } |
| 906 | } |
projects / dbsrgits/SQL-Translator-2.0-ish.git / blame