1 package SQL::Translator::Parser::SQLite;
5 SQL::Translator::Parser::SQLite - parser for SQLite
10 use SQL::Translator::Parser::SQLite;
12 my $translator = SQL::Translator->new;
13 $translator->parser("SQL::Translator::Parser::SQLite");
17 This is a grammar for parsing CREATE statements for SQLite as
20 http://www.sqlite.org/lang.html
25 CREATE [TEMP | TEMPORARY] [UNIQUE] INDEX index-name
26 ON [database-name .] table-name ( column-name [, column-name]* )
27 [ ON CONFLICT conflict-algorithm ]
35 CREATE [TEMP | TEMPORARY] TABLE table-name (
36 column-def [, column-def]*
41 CREATE [TEMP | TEMPORARY] TABLE table-name AS select-statement
44 name [type] [[CONSTRAINT name] column-constraint]*
49 typename ( number , number )
52 NOT NULL [ conflict-clause ] |
53 PRIMARY KEY [sort-order] [ conflict-clause ] |
54 UNIQUE [ conflict-clause ] |
55 CHECK ( expr ) [ conflict-clause ] |
59 PRIMARY KEY ( name [, name]* ) [ conflict-clause ]|
60 UNIQUE ( name [, name]* ) [ conflict-clause ] |
61 CHECK ( expr ) [ conflict-clause ]
64 ON CONFLICT conflict-algorithm
69 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]
70 database-event ON [database-name .] table-name
74 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
75 database-event ON [database-name .] view-name
85 [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ]
87 trigger-step ; [ trigger-step ; ]*
91 update-statement | insert-statement |
92 delete-statement | select-statement
97 CREATE [TEMP | TEMPORARY] VIEW view-name AS select-statement
102 ON CONFLICT conflict-algorithm
104 conflict-algorithm ::=
105 ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
110 expr binary-op expr |
115 table-name . column-name |
116 database-name . table-name . column-name |
118 function-name ( expr-list | * ) |
122 expr [NOT] BETWEEN expr AND expr |
123 expr [NOT] IN ( value-list ) |
124 expr [NOT] IN ( select-statement ) |
125 ( select-statement ) |
126 CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END
129 LIKE | GLOB | NOT LIKE | NOT GLOB
136 our $VERSION = '1.60';
139 $DEBUG = 0 unless defined $DEBUG;
142 use SQL::Translator::Utils qw/ddl_parser_instance/;
144 use base qw(Exporter);
145 our @EXPORT_OK = qw(parse);
147 our $GRAMMAR = <<'END_OF_GRAMMAR';
150 my ( %tables, $table_order, @table_comments, @views, @triggers );
154 my @up_to_N_lines = split (/\n/, $_[1], $max_lines + 1);
155 die sprintf ("Unable to parse line %d:\n%s\n",
157 join "\n", (map { "'$_'" } @up_to_N_lines[0..$max_lines - 1 ]), @up_to_N_lines > $max_lines ? '...' : ()
164 # The "eofile" rule makes the parser fail if any "statement" rule
165 # fails. Otherwise, the first successful match by a "statement"
166 # won't cause the failure needed to know that the parse, as a whole,
169 startrule : statement(s) eofile {
173 triggers => \@triggers,
179 statement : begin_transaction
184 | /^\Z/ | { _err ($thisline, $text) }
186 begin_transaction : /begin/i TRANSACTION(?) SEMICOLON
188 commit : /commit/i SEMICOLON
190 drop : /drop/i (tbl_drop | view_drop | trg_drop) SEMICOLON
192 tbl_drop: TABLE <commit> table_name
194 view_drop: VIEW if_exists(?) view_name
196 trg_drop: TRIGGER if_exists(?) trigger_name
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 NAME 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: comment(s?) NAME type(?) column_constraint_def(s?)
266 supertype => 'column',
268 data_type => $item[3][0]->{'type'},
269 size => $item[3][0]->{'size'},
275 constraints => $item[4],
276 comments => $item[1],
280 for my $c ( @{ $item[4] } ) {
281 if ( $c->{'type'} eq 'not_null' ) {
282 $column->{'is_nullable'} = 0;
284 elsif ( $c->{'type'} eq 'primary_key' ) {
285 $column->{'is_primary_key'} = 1;
287 elsif ( $c->{'type'} eq 'unique' ) {
288 $column->{'is_unique'} = 1;
290 elsif ( $c->{'type'} eq 'check' ) {
291 $column->{'check'} = $c->{'expression'};
293 elsif ( $c->{'type'} eq 'default' ) {
294 $column->{'default'} = $c->{'value'};
296 elsif ( $c->{'type'} eq 'autoincrement' ) {
297 $column->{'is_auto_inc'} = 1;
304 type : WORD parens_value_list(?)
312 column_constraint_def : CONSTRAINT constraint_name column_constraint
322 column_constraint : NOT_NULL conflict_clause(?)
329 PRIMARY_KEY sort_order(?) conflict_clause(?)
332 type => 'primary_key',
333 sort_order => $item[2][0],
334 on_conflict => $item[2][0],
338 UNIQUE conflict_clause(?)
342 on_conflict => $item[2][0],
346 CHECK_C '(' expr ')' conflict_clause(?)
350 expression => $item[3],
351 on_conflict => $item[5][0],
363 REFERENCES ref_def cascade_def(?)
366 type => 'foreign_key',
367 reference_table => $item[2]{'reference_table'},
368 reference_fields => $item[2]{'reference_fields'},
369 on_delete => $item[3][0]{'on_delete'},
370 on_update => $item[3][0]{'on_update'},
377 type => 'autoincrement',
381 constraint_def : comment(s?) CONSTRAINT constraint_name table_constraint
384 comments => $item[1],
390 comment(s?) table_constraint
393 comments => $item[1],
398 table_constraint : PRIMARY_KEY parens_field_list conflict_clause(?)
401 supertype => 'constraint',
402 type => 'primary_key',
404 on_conflict => $item[3][0],
408 UNIQUE parens_field_list conflict_clause(?)
411 supertype => 'constraint',
414 on_conflict => $item[3][0],
418 CHECK_C '(' expr ')' conflict_clause(?)
421 supertype => 'constraint',
423 expression => $item[3],
424 on_conflict => $item[5][0],
428 FOREIGN_KEY parens_field_list REFERENCES ref_def cascade_def(?)
431 supertype => 'constraint',
432 type => 'foreign_key',
434 reference_table => $item[4]{'reference_table'},
435 reference_fields => $item[4]{'reference_fields'},
436 on_delete => $item[5][0]{'on_delete'},
437 on_update => $item[5][0]{'on_update'},
441 ref_def : table_name parens_field_list
442 { $return = { reference_table => $item[1]{name}, reference_fields => $item[2] } }
444 cascade_def : cascade_update_def cascade_delete_def(?)
445 { $return = { on_update => $item[1], on_delete => $item[2][0] } }
447 cascade_delete_def cascade_update_def(?)
448 { $return = { on_delete => $item[1], on_update => $item[2][0] } }
450 cascade_delete_def : /on\s+delete\s+(set null|set default|cascade|restrict|no action)/i
453 cascade_update_def : /on\s+update\s+(set null|set default|cascade|restrict|no action)/i
456 table_name : qualified_name
458 qualified_name : NAME
459 { $return = { name => $item[1] } }
461 qualified_name : /(\w+)\.(\w+)/
462 { $return = { db_name => $1, name => $2 } }
466 constraint_name : NAME
468 conflict_clause : /on conflict/i conflict_algorigthm
470 conflict_algorigthm : /(rollback|abort|fail|ignore|replace)/i
472 parens_field_list : '(' column_list ')'
475 column_list : field_name(s /,/)
477 parens_value_list : '(' VALUE(s /,/) ')'
480 expr : /[^)]* \( [^)]+ \) [^)]*/x # parens, balanced one deep
483 sort_order : /(ASC|DESC)/i
488 create : CREATE TEMPORARY(?) TRIGGER NAME before_or_after(?) database_event ON table_name trigger_action SEMICOLON
490 my $table_name = $item[8]->{'name'};
493 is_temporary => $item[2][0] ? 1 : 0,
496 db_events => [ $item[6] ],
498 on_table => $table_name,
502 create : CREATE TEMPORARY(?) TRIGGER NAME instead_of database_event ON view_name trigger_action
504 my $table_name = $item[8]->{'name'};
507 is_temporary => $item[2][0] ? 1 : 0,
510 db_events => [ $item[6] ],
512 on_table => $table_name,
516 database_event : /(delete|insert|update)/i
518 database_event : /update of/i column_list
520 trigger_action : for_each(?) when(?) BEGIN_C trigger_step(s) END_C
523 for_each => $item[1][0],
529 for_each : /FOR EACH ROW/i
531 when : WHEN expr { $item[2] }
536 nonstring : /[^;\'"]+/
538 statement_body : string | nonstring
540 trigger_step : /(select|delete|insert|update)/i statement_body(s?) SEMICOLON
542 $return = join( ' ', $item[1], join ' ', @{ $item[2] || [] } )
545 before_or_after : /(before|after)/i { $return = lc $1 }
547 instead_of : /instead of/i
549 if_exists : /if exists/i
551 view_name : qualified_name
553 trigger_name : qualified_name
558 create : CREATE TEMPORARY(?) VIEW view_name AS select_statement
561 name => $item[4]->{'name'},
563 is_temporary => $item[2][0] ? 1 : 0,
567 select_statement : SELECT /[^;]+/ SEMICOLON
569 $return = join( ' ', $item[1], $item[2] );
579 TRANSACTION: /transaction/i
583 TEMPORARY : /temp(orary)?/i { 1 }
589 NOT_NULL : /not null/i
591 PRIMARY_KEY : /primary key/i
593 FOREIGN_KEY : /foreign key/i
613 REFERENCES : /references/i
615 CONSTRAINT : /constraint/i
617 AUTOINCREMENT : /autoincrement/i
619 UNIQUE : /unique/i { 1 }
627 DQSTRING : '"' <skip: ''> /((?:[^"]|"")+)/ '"'
628 { ($return = $item[3]) =~ s/""/"/g }
630 SQSTRING : "'" <skip: ''> /((?:[^']|'')*)/ "'"
631 { ($return = $item[3]) =~ s/''/'/g }
633 VALUE : /[-+]?\d*\.?\d+(?:[eE]\d+)?/
638 | /CURRENT_TIMESTAMP/i
639 { 'CURRENT_TIMESTAMP' }
645 my ( $translator, $data ) = @_;
647 # Enable warnings within the Parse::RecDescent module.
648 local $::RD_ERRORS = 1 unless defined $::RD_ERRORS; # Make sure the parser dies when it encounters an error
649 local $::RD_WARN = 1 unless defined $::RD_WARN; # Enable warnings. This will warn on unused rules &c.
650 local $::RD_HINT = 1 unless defined $::RD_HINT; # Give out hints to help fix problems.
652 local $::RD_TRACE = $translator->trace ? 1 : undef;
653 local $DEBUG = $translator->debug;
655 my $parser = ddl_parser_instance('SQLite');
657 my $result = $parser->startrule($data);
658 return $translator->error( "Parse failed." ) unless defined $result;
659 warn Dumper( $result ) if $DEBUG;
661 my $schema = $translator->schema;
664 sort { $a->[0] <=> $b->[0] }
665 map { [ $result->{'tables'}{ $_ }->{'order'}, $_ ] }
666 keys %{ $result->{'tables'} };
668 for my $table_name ( @tables ) {
669 my $tdata = $result->{'tables'}{ $table_name };
670 my $table = $schema->add_table(
671 name => $tdata->{'name'},
672 ) or die $schema->error;
674 $table->comments( $tdata->{'comments'} );
676 for my $fdata ( @{ $tdata->{'fields'} } ) {
677 my $field = $table->add_field(
678 name => $fdata->{'name'},
679 data_type => $fdata->{'data_type'},
680 size => $fdata->{'size'},
681 default_value => $fdata->{'default'},
682 is_auto_increment => $fdata->{'is_auto_inc'},
683 is_nullable => $fdata->{'is_nullable'},
684 comments => $fdata->{'comments'},
685 ) or die $table->error;
687 $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
689 for my $cdata ( @{ $fdata->{'constraints'} } ) {
690 next unless $cdata->{'type'} eq 'foreign_key';
691 $cdata->{'fields'} ||= [ $field->name ];
692 push @{ $tdata->{'constraints'} }, $cdata;
696 for my $idata ( @{ $tdata->{'indices'} || [] } ) {
697 my $index = $table->add_index(
698 name => $idata->{'name'},
699 type => uc ($idata->{'type'}||''),
700 fields => $idata->{'fields'},
701 ) or die $table->error;
704 for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
705 my $constraint = $table->add_constraint(
706 name => $cdata->{'name'},
707 type => $cdata->{'type'},
708 fields => $cdata->{'fields'},
709 reference_table => $cdata->{'reference_table'},
710 reference_fields => $cdata->{'reference_fields'},
711 match_type => $cdata->{'match_type'} || '',
712 on_delete => $cdata->{'on_delete'}
713 || $cdata->{'on_delete_do'},
714 on_update => $cdata->{'on_update'}
715 || $cdata->{'on_update_do'},
716 ) or die $table->error;
720 for my $def ( @{ $result->{'views'} || [] } ) {
721 my $view = $schema->add_view(
722 name => $def->{'name'},
723 sql => $def->{'sql'},
727 for my $def ( @{ $result->{'triggers'} || [] } ) {
728 my $view = $schema->add_trigger(
729 name => $def->{'name'},
730 perform_action_when => $def->{'when'},
731 database_events => $def->{'db_events'},
732 action => $def->{'action'},
733 on_table => $def->{'on_table'},
734 scope => 'row', # SQLite only supports row triggers
743 # -------------------------------------------------------------------
744 # All wholesome food is caught without a net or a trap.
746 # -------------------------------------------------------------------
752 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
756 perl(1), Parse::RecDescent, SQL::Translator::Schema.