1 package SQL::Translator::Parser::SQLite;
3 # -------------------------------------------------------------------
4 # $Id: SQLite.pm,v 1.1 2003-10-03 00:20:51 kycl4rk Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2003 Ken Y. Clark <kclark@cpan.org>,
7 # darren chamberlain <darren@cpan.org>,
8 # Chris Mungall <cjm@fruitfly.org>
10 # This program is free software; you can redistribute it and/or
11 # modify it under the terms of the GNU General Public License as
12 # published by the Free Software Foundation; version 2.
14 # This program is distributed in the hope that it will be useful, but
15 # WITHOUT ANY WARRANTY; without even the implied warranty of
16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
17 # General Public License for more details.
19 # You should have received a copy of the GNU General Public License
20 # along with this program; if not, write to the Free Software
21 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
23 # -------------------------------------------------------------------
27 SQL::Translator::Parser::SQLite - parser for SQLite
32 use SQL::Translator::Parser::SQLite;
34 my $translator = SQL::Translator->new;
35 $translator->parser("SQL::Translator::Parser::SQLite");
39 This is a grammar for parsing CREATE statements for SQLite as
42 http://www.sqlite.org/lang.html
47 CREATE [TEMP | TEMPORARY] [UNIQUE] INDEX index-name
48 ON [database-name .] table-name ( column-name [, column-name]* )
49 [ ON CONFLICT conflict-algorithm ]
57 CREATE [TEMP | TEMPORARY] TABLE table-name (
58 column-def [, column-def]*
63 CREATE [TEMP | TEMPORARY] TABLE table-name AS select-statement
66 name [type] [[CONSTRAINT name] column-constraint]*
71 typename ( number , number )
74 NOT NULL [ conflict-clause ] |
75 PRIMARY KEY [sort-order] [ conflict-clause ] |
76 UNIQUE [ conflict-clause ] |
77 CHECK ( expr ) [ conflict-clause ] |
81 PRIMARY KEY ( name [, name]* ) [ conflict-clause ]|
82 UNIQUE ( name [, name]* ) [ conflict-clause ] |
83 CHECK ( expr ) [ conflict-clause ]
86 ON CONFLICT conflict-algorithm
91 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]
92 database-event ON [database-name .] table-name
96 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
97 database-event ON [database-name .] view-name
104 UPDATE OF column-list
107 [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ]
109 trigger-step ; [ trigger-step ; ]*
113 update-statement | insert-statement |
114 delete-statement | select-statemen
119 CREATE [TEMP | TEMPORARY] VIEW view-name AS select-statement
124 ON CONFLICT conflict-algorithm
126 conflict-algorithm ::=
127 ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
132 expr binary-op expr |
137 table-name . column-name |
138 database-name . table-name . column-name |
140 function-name ( expr-list | * ) |
144 expr [NOT] BETWEEN expr AND expr |
145 expr [NOT] IN ( value-list ) |
146 expr [NOT] IN ( select-statement ) |
147 ( select-statement ) |
148 CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END
151 LIKE | GLOB | NOT LIKE | NOT GLOB
156 use vars qw[ $DEBUG $VERSION $GRAMMAR @EXPORT_OK ];
157 $VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/;
158 $DEBUG = 0 unless defined $DEBUG;
161 use Parse::RecDescent;
163 use base qw(Exporter);
165 @EXPORT_OK = qw(parse);
167 # Enable warnings within the Parse::RecDescent module.
168 $::RD_ERRORS = 1; # Make sure the parser dies when it encounters an error
169 $::RD_WARN = 1; # Enable warnings. This will warn on unused rules &c.
170 $::RD_HINT = 1; # Give out hints to help fix problems.
175 my ( %tables, $table_order, @table_comments );
179 # The "eofile" rule makes the parser fail if any "statement" rule
180 # fails. Otherwise, the first successful match by a "statement"
181 # won't cause the failure needed to know that the parse, as a whole,
184 startrule : statement(s) eofile { \%tables }
188 statement : begin_transaction
194 begin_transaction : /begin transaction/i SEMICOLON
196 commit : /commit/i SEMICOLON
198 comment : /^\s*(?:#|-{2}).*\n/
200 my $comment = $item[1];
201 $comment =~ s/^\s*(#|-{2})\s*//;
202 $comment =~ s/\s*$//;
206 comment : /\/\*/ /[^\*]+/ /\*\//
208 my $comment = $item[2];
209 $comment =~ s/^\s*|\s*$//g;
216 create : CREATE TEMPORARY(?) UNIQUE(?) INDEX WORD ON table_name parens_field_list conflict_clause(?) SEMICOLON
218 my $db_name = $item[7]->{'db_name'} || '';
219 my $table_name = $item[7]->{'name'};
224 on_conflict => $item[9][0],
225 is_temporary => $item[2][0] ? 1 : 0,
228 my $is_unique = $item[3][0];
231 $index->{'type'} = 'unique';
232 push @{ $tables{ $table_name }{'constraints'} }, $index;
235 push @{ $tables{ $table_name }{'indices'} }, $index;
242 create : CREATE TEMPORARY(?) TABLE table_name '(' definition(s /,/) ')' SEMICOLON
244 my $db_name = $item[4]->{'db_name'} || '';
245 my $table_name = $item[4]->{'name'};
247 $tables{ $table_name }{'name'} = $table_name;
248 $tables{ $table_name }{'is_temporary'} = $item[2][0] ? 1 : 0;
249 $tables{ $table_name }{'order'} = ++$table_order;
251 for my $def ( @{ $item[6] } ) {
252 if ( $def->{'supertype'} eq 'column' ) {
253 push @{ $tables{ $table_name }{'fields'} }, $def;
255 elsif ( $def->{'supertype'} eq 'constraint' ) {
256 push @{ $tables{ $table_name }{'constraints'} }, $def;
261 definition : constraint_def | column_def
263 column_def: NAME type(?) column_constraint(s?)
266 supertype => 'column',
268 data_type => $item[2][0]->{'type'},
269 size => $item[2][0]->{'size'},
275 constraints => $item[3],
278 for my $c ( @{ $item[3] } ) {
279 if ( $c->{'type'} eq 'not_null' ) {
280 $column->{'is_nullable'} = 0;
282 elsif ( $c->{'type'} eq 'primary_key' ) {
283 $column->{'is_primary_key'} = 1;
285 elsif ( $c->{'type'} eq 'unique' ) {
286 $column->{'is_unique'} = 1;
288 elsif ( $c->{'type'} eq 'check' ) {
289 $column->{'check'} = $c->{'expression'};
291 elsif ( $c->{'type'} eq 'default' ) {
292 $column->{'default'} = $c->{'value'};
299 type : WORD parens_value_list(?)
307 column_constraint : NOT_NULL conflict_clause(?)
314 PRIMARY_KEY sort_order(?) conflict_clause(?)
317 type => 'primary_key',
318 sort_order => $item[2][0],
319 on_conflict => $item[2][0],
323 UNIQUE conflict_clause(?)
327 on_conflict => $item[2][0],
331 CHECK_C '(' expr ')' conflict_clause(?)
335 expression => $item[3],
336 on_conflict => $item[5][0],
348 constraint_def : PRIMARY_KEY parens_field_list conflict_clause(?)
351 supertype => 'constraint',
352 type => 'primary_key',
354 on_conflict => $item[3][0],
358 UNIQUE parens_field_list conflict_clause(?)
361 supertype => 'constraint',
364 on_conflict => $item[3][0],
368 CHECK_C '(' expr ')' conflict_clause(?)
371 supertype => 'constraint',
373 expression => $item[3],
374 on_conflict => $item[5][0],
378 table_name : qualified_name
380 qualified_name : NAME
381 { $return = { name => $item[1] } }
383 qualified_name : /(\w+)\.(\w+)/
384 { $return = { db_name => $1, name => $2 } }
388 conflict_clause : /on conflict/i conflict_algorigthm
390 conflict_algorigthm : /(rollback|abort|fail|ignore|replace)/i
392 parens_field_list : '(' column_list ')'
395 column_list : field_name(s /,/)
397 parens_value_list : '(' VALUE(s /,/) ')'
402 sort_order : /(ASC|DESC)/i
407 create : CREATE TEMPORARY(?) TRIGGER NAME before_or_after(?) database_event ON table_name trigger_action
409 my $table_name = $item[8]->{'name'};
410 push @{ $tables{ $table_name }{'triggers'} }, {
412 is_temporary => $item[2][0] ? 1 : 0,
415 db_event => $item[6],
420 create : CREATE TEMPORARY(?) TRIGGER NAME instead_of database_event ON view_name trigger_action
422 my $table_name = $item[8]->{'name'};
423 push @{ $tables{ $table_name }{'triggers'} }, {
425 is_temporary => $item[2][0] ? 1 : 0,
428 db_event => $item[6],
433 database_event : /(delete|insert|update)/i
435 database_event : /update of/i column_list
437 trigger_action : for_each(?) when(?) BEGIN_C trigger_step(s) END_C
440 for_each => $item[1][0],
446 for_each : /FOR EACH ROW/i | /FOR EACH STATEMENT/i
448 when : WHEN expr { $item[2] }
450 trigger_step : /(select|delete|insert|update)/i /[^;]+/ SEMICOLON
452 $return = join( ' ', $item[1], $item[2] )
455 before_or_after : /(before|after)/i { $return = lc $1 }
457 instead_of : /instead of/i
459 view_name : qualified_name
470 TEMPORARY : /temp(orary)?/i { 1 }
476 NOT_NULL : /not null/i
478 PRIMARY_KEY : /primary key/i
492 UNIQUE : /unique/i { 1 }
496 NAME : /'?(\w+)'?/ { $return = $1 }
498 VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
502 # remove leading/trailing quotes
504 $val =~ s/^['"]|['"]$//g;
512 # -------------------------------------------------------------------
514 my ( $translator, $data ) = @_;
515 my $parser = Parse::RecDescent->new($GRAMMAR);
517 local $::RD_TRACE = $translator->trace ? 1 : undef;
518 local $DEBUG = $translator->debug;
520 unless (defined $parser) {
521 return $translator->error("Error instantiating Parse::RecDescent ".
522 "instance: Bad grammer");
525 my $result = $parser->startrule($data);
526 return $translator->error( "Parse failed." ) unless defined $result;
527 warn Dumper( $result ) if $DEBUG;
529 my $schema = $translator->schema;
531 $result->{ $a }->{'order'} <=> $result->{ $b }->{'order'}
534 for my $table_name ( @tables ) {
535 my $tdata = $result->{ $table_name };
536 my $table = $schema->add_table(
537 name => $tdata->{'name'},
538 ) or die $schema->error;
540 $table->comments( $tdata->{'comments'} );
542 for my $fdata ( @{ $tdata->{'fields'} } ) {
543 my $field = $table->add_field(
544 name => $fdata->{'name'},
545 data_type => $fdata->{'data_type'},
546 size => $fdata->{'size'},
547 default_value => $fdata->{'default'},
548 is_auto_increment => $fdata->{'is_auto_inc'},
549 is_nullable => $fdata->{'is_nullable'},
550 comments => $fdata->{'comments'},
551 ) or die $table->error;
553 $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
555 for my $cdata ( @{ $fdata->{'constraints'} } ) {
556 next unless $cdata->{'type'} eq 'foreign_key';
557 $cdata->{'fields'} ||= [ $field->name ];
558 push @{ $tdata->{'constraints'} }, $cdata;
562 for my $idata ( @{ $tdata->{'indices'} || [] } ) {
563 my $index = $table->add_index(
564 name => $idata->{'name'},
565 type => uc $idata->{'type'},
566 fields => $idata->{'fields'},
567 ) or die $table->error;
570 for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
571 my $constraint = $table->add_constraint(
572 name => $cdata->{'name'},
573 type => $cdata->{'type'},
574 fields => $cdata->{'fields'},
575 reference_table => $cdata->{'reference_table'},
576 reference_fields => $cdata->{'reference_fields'},
577 match_type => $cdata->{'match_type'} || '',
578 on_delete => $cdata->{'on_delete_do'},
579 on_update => $cdata->{'on_update_do'},
580 ) or die $table->error;
589 # -------------------------------------------------------------------
590 # All wholsome food is caught without a net or a trap.
592 # -------------------------------------------------------------------
598 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
602 perl(1), Parse::RecDescent, SQL::Translator::Schema.