Allow passing an arrayref to SQLT->filename
[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Parser / SQLite.pm
CommitLineData
72aa2647 1package SQL::Translator::Parser::SQLite;
2
72aa2647 3=head1 NAME
4
5SQL::Translator::Parser::SQLite - parser for SQLite
6
7=head1 SYNOPSIS
8
9 use SQL::Translator;
10 use SQL::Translator::Parser::SQLite;
11
12 my $translator = SQL::Translator->new;
13 $translator->parser("SQL::Translator::Parser::SQLite");
14
15=head1 DESCRIPTION
16
ea93df61 17This is a grammar for parsing CREATE statements for SQLite as
72aa2647 18described here:
19
20 http://www.sqlite.org/lang.html
21
22CREATE INDEX
23
24sql-statement ::=
ea93df61 25 CREATE [TEMP | TEMPORARY] [UNIQUE] INDEX index-name
72aa2647 26 ON [database-name .] table-name ( column-name [, column-name]* )
27 [ ON CONFLICT conflict-algorithm ]
28
29column-name ::=
30 name [ ASC | DESC ]
31
32CREATE TABLE
33
34sql-command ::=
35 CREATE [TEMP | TEMPORARY] TABLE table-name (
36 column-def [, column-def]*
37 [, constraint]*
38 )
39
40sql-command ::=
41 CREATE [TEMP | TEMPORARY] TABLE table-name AS select-statement
42
43column-def ::=
44 name [type] [[CONSTRAINT name] column-constraint]*
45
46type ::=
47 typename |
48 typename ( number ) |
49 typename ( number , number )
50
51column-constraint ::=
52 NOT NULL [ conflict-clause ] |
53 PRIMARY KEY [sort-order] [ conflict-clause ] |
54 UNIQUE [ conflict-clause ] |
55 CHECK ( expr ) [ conflict-clause ] |
56 DEFAULT value
57
58constraint ::=
59 PRIMARY KEY ( name [, name]* ) [ conflict-clause ]|
60 UNIQUE ( name [, name]* ) [ conflict-clause ] |
61 CHECK ( expr ) [ conflict-clause ]
62
63conflict-clause ::=
64 ON CONFLICT conflict-algorithm
65
66CREATE TRIGGER
67
68sql-statement ::=
69 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]
70 database-event ON [database-name .] table-name
71 trigger-action
72
73sql-statement ::=
74 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
75 database-event ON [database-name .] view-name
76 trigger-action
77
78database-event ::=
ea93df61 79 DELETE |
80 INSERT |
81 UPDATE |
72aa2647 82 UPDATE OF column-list
83
84trigger-action ::=
ea93df61 85 [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ]
86 BEGIN
72aa2647 87 trigger-step ; [ trigger-step ; ]*
88 END
89
90trigger-step ::=
ea93df61 91 update-statement | insert-statement |
51af147e 92 delete-statement | select-statement
72aa2647 93
94CREATE VIEW
95
96sql-command ::=
97 CREATE [TEMP | TEMPORARY] VIEW view-name AS select-statement
98
99ON CONFLICT clause
100
101 conflict-clause ::=
102 ON CONFLICT conflict-algorithm
103
104 conflict-algorithm ::=
105 ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
106
107expression
108
109expr ::=
110 expr binary-op expr |
111 expr like-op expr |
112 unary-op expr |
113 ( expr ) |
114 column-name |
115 table-name . column-name |
116 database-name . table-name . column-name |
117 literal-value |
118 function-name ( expr-list | * ) |
119 expr (+) |
120 expr ISNULL |
121 expr NOTNULL |
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
127
128like-op::=
129 LIKE | GLOB | NOT LIKE | NOT GLOB
130
131=cut
132
133use strict;
f27f9229 134use warnings;
bdf60588 135
0c04c5a2 136our $VERSION = '1.59';
bdf60588 137
138our $DEBUG;
72aa2647 139$DEBUG = 0 unless defined $DEBUG;
140
141use Data::Dumper;
bdf60588 142use SQL::Translator::Utils qw/ddl_parser_instance/;
72aa2647 143
bdf60588 144use base qw(Exporter);
145our @EXPORT_OK = qw(parse);
72aa2647 146
bdf60588 147our $GRAMMAR = <<'END_OF_GRAMMAR';
72aa2647 148
ea93df61 149{
70698e1c 150 my ( %tables, $table_order, @table_comments, @views, @triggers );
95044c79 151
152 sub _err {
153 my $max_lines = 5;
154 my @up_to_N_lines = split (/\n/, $_[1], $max_lines + 1);
155 die sprintf ("Unable to parse line %d:\n%s\n",
156 $_[0],
157 join "\n", (map { "'$_'" } @up_to_N_lines[0..$max_lines - 1 ]), @up_to_N_lines > $max_lines ? '...' : ()
158 );
159 }
160
72aa2647 161}
162
163#
164# The "eofile" rule makes the parser fail if any "statement" rule
ea93df61 165# fails. Otherwise, the first successful match by a "statement"
72aa2647 166# won't cause the failure needed to know that the parse, as a whole,
167# failed. -ky
168#
ea93df61 169startrule : statement(s) eofile {
8c12c406 170 $return = {
ea93df61 171 tables => \%tables,
70698e1c 172 views => \@views,
173 triggers => \@triggers,
174 }
175}
72aa2647 176
177eofile : /^\Z/
178
179statement : begin_transaction
180 | commit
ac9c4e2e 181 | drop
72aa2647 182 | comment
183 | create
95044c79 184 | /^\Z/ | { _err ($thisline, $text) }
72aa2647 185
f9c96971 186begin_transaction : /begin/i TRANSACTION(?) SEMICOLON
72aa2647 187
188commit : /commit/i SEMICOLON
189
ae602737 190drop : /drop/i (tbl_drop | view_drop | trg_drop) SEMICOLON
191
192tbl_drop: TABLE <commit> table_name
193
194view_drop: VIEW if_exists(?) view_name
195
196trg_drop: TRIGGER if_exists(?) trigger_name
ac9c4e2e 197
72aa2647 198comment : /^\s*(?:#|-{2}).*\n/
199 {
200 my $comment = $item[1];
201 $comment =~ s/^\s*(#|-{2})\s*//;
202 $comment =~ s/\s*$//;
203 $return = $comment;
204 }
205
ea93df61 206comment : /\/\*/ /[^\*]+/ /\*\//
72aa2647 207 {
208 my $comment = $item[2];
209 $comment =~ s/^\s*|\s*$//g;
210 $return = $comment;
211 }
212
213#
214# Create Index
215#
6ceb5f1c 216create : CREATE TEMPORARY(?) UNIQUE(?) INDEX NAME ON table_name parens_field_list conflict_clause(?) SEMICOLON
72aa2647 217 {
218 my $db_name = $item[7]->{'db_name'} || '';
219 my $table_name = $item[7]->{'name'};
220
ea93df61 221 my $index = {
72aa2647 222 name => $item[5],
223 fields => $item[8],
224 on_conflict => $item[9][0],
225 is_temporary => $item[2][0] ? 1 : 0,
226 };
227
228 my $is_unique = $item[3][0];
229
230 if ( $is_unique ) {
231 $index->{'type'} = 'unique';
232 push @{ $tables{ $table_name }{'constraints'} }, $index;
233 }
234 else {
235 push @{ $tables{ $table_name }{'indices'} }, $index;
236 }
237 }
238
239#
240# Create Table
241#
242create : CREATE TEMPORARY(?) TABLE table_name '(' definition(s /,/) ')' SEMICOLON
243 {
244 my $db_name = $item[4]->{'db_name'} || '';
245 my $table_name = $item[4]->{'name'};
246
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;
250
251 for my $def ( @{ $item[6] } ) {
252 if ( $def->{'supertype'} eq 'column' ) {
253 push @{ $tables{ $table_name }{'fields'} }, $def;
254 }
255 elsif ( $def->{'supertype'} eq 'constraint' ) {
256 push @{ $tables{ $table_name }{'constraints'} }, $def;
257 }
258 }
259 }
260
ea93df61 261definition : constraint_def | column_def
72aa2647 262
7179cb09 263column_def: comment(s?) NAME type(?) column_constraint_def(s?)
72aa2647 264 {
265 my $column = {
266 supertype => 'column',
ae602737 267 name => $item[2],
268 data_type => $item[3][0]->{'type'},
269 size => $item[3][0]->{'size'},
72aa2647 270 is_nullable => 1,
271 is_primary_key => 0,
272 is_unique => 0,
273 check => '',
274 default => undef,
ae602737 275 constraints => $item[4],
276 comments => $item[1],
72aa2647 277 };
278
ae602737 279
280 for my $c ( @{ $item[4] } ) {
72aa2647 281 if ( $c->{'type'} eq 'not_null' ) {
282 $column->{'is_nullable'} = 0;
283 }
284 elsif ( $c->{'type'} eq 'primary_key' ) {
285 $column->{'is_primary_key'} = 1;
286 }
287 elsif ( $c->{'type'} eq 'unique' ) {
288 $column->{'is_unique'} = 1;
289 }
290 elsif ( $c->{'type'} eq 'check' ) {
291 $column->{'check'} = $c->{'expression'};
292 }
293 elsif ( $c->{'type'} eq 'default' ) {
294 $column->{'default'} = $c->{'value'};
295 }
6ceb5f1c 296 elsif ( $c->{'type'} eq 'autoincrement' ) {
297 $column->{'is_auto_inc'} = 1;
298 }
72aa2647 299 }
300
301 $column;
302 }
303
304type : WORD parens_value_list(?)
305 {
306 $return = {
307 type => $item[1],
308 size => $item[2][0],
309 }
310 }
311
7179cb09 312column_constraint_def : CONSTRAINT constraint_name column_constraint
313 {
314 $return = {
315 name => $item[2],
316 %{ $item[3] },
317 }
318 }
319 |
320 column_constraint
321
72aa2647 322column_constraint : NOT_NULL conflict_clause(?)
323 {
324 $return = {
325 type => 'not_null',
326 }
327 }
328 |
329 PRIMARY_KEY sort_order(?) conflict_clause(?)
330 {
331 $return = {
332 type => 'primary_key',
333 sort_order => $item[2][0],
ea93df61 334 on_conflict => $item[2][0],
72aa2647 335 }
336 }
337 |
338 UNIQUE conflict_clause(?)
339 {
340 $return = {
341 type => 'unique',
ea93df61 342 on_conflict => $item[2][0],
72aa2647 343 }
344 }
345 |
346 CHECK_C '(' expr ')' conflict_clause(?)
347 {
348 $return = {
349 type => 'check',
350 expression => $item[3],
ea93df61 351 on_conflict => $item[5][0],
72aa2647 352 }
353 }
354 |
355 DEFAULT VALUE
356 {
357 $return = {
358 type => 'default',
359 value => $item[2],
360 }
361 }
08e01b5c 362 |
ea4a3ecc 363 REFERENCES ref_def cascade_def(?)
08e01b5c 364 {
365 $return = {
366 type => 'foreign_key',
367 reference_table => $item[2]{'reference_table'},
368 reference_fields => $item[2]{'reference_fields'},
ea4a3ecc 369 on_delete => $item[3][0]{'on_delete'},
370 on_update => $item[3][0]{'on_update'},
08e01b5c 371 }
372 }
6ceb5f1c 373 |
374 AUTOINCREMENT
375 {
376 $return = {
377 type => 'autoincrement',
378 }
379 }
72aa2647 380
7179cb09 381constraint_def : comment(s?) CONSTRAINT constraint_name table_constraint
382 {
383 $return = {
384 comments => $item[1],
385 name => $item[3],
386 %{ $item[4] },
387 }
388 }
389 |
390 comment(s?) table_constraint
391 {
392 $return = {
393 comments => $item[1],
394 %{ $item[2] },
395 }
396 }
397
398table_constraint : PRIMARY_KEY parens_field_list conflict_clause(?)
72aa2647 399 {
400 $return = {
401 supertype => 'constraint',
402 type => 'primary_key',
403 fields => $item[2],
404 on_conflict => $item[3][0],
405 }
406 }
407 |
408 UNIQUE parens_field_list conflict_clause(?)
409 {
410 $return = {
411 supertype => 'constraint',
412 type => 'unique',
413 fields => $item[2],
414 on_conflict => $item[3][0],
415 }
416 }
417 |
418 CHECK_C '(' expr ')' conflict_clause(?)
419 {
420 $return = {
421 supertype => 'constraint',
422 type => 'check',
423 expression => $item[3],
424 on_conflict => $item[5][0],
425 }
426 }
0dbd2362 427 |
ea4a3ecc 428 FOREIGN_KEY parens_field_list REFERENCES ref_def cascade_def(?)
0dbd2362 429 {
430 $return = {
431 supertype => 'constraint',
432 type => 'foreign_key',
433 fields => $item[2],
434 reference_table => $item[4]{'reference_table'},
435 reference_fields => $item[4]{'reference_fields'},
ea4a3ecc 436 on_delete => $item[5][0]{'on_delete'},
437 on_update => $item[5][0]{'on_update'},
0dbd2362 438 }
439 }
72aa2647 440
d8cf2279 441ref_def : table_name parens_field_list
442 { $return = { reference_table => $item[1]{name}, reference_fields => $item[2] } }
08e01b5c 443
ea4a3ecc 444cascade_def : cascade_update_def cascade_delete_def(?)
445 { $return = { on_update => $item[1], on_delete => $item[2][0] } }
446 |
447 cascade_delete_def cascade_update_def(?)
448 { $return = { on_delete => $item[1], on_update => $item[2][0] } }
449
450cascade_delete_def : /on\s+delete\s+(\w+)/i
451 { $return = $1}
452
453cascade_update_def : /on\s+update\s+(\w+)/i
454 { $return = $1}
455
72aa2647 456table_name : qualified_name
ea93df61 457
458qualified_name : NAME
72aa2647 459 { $return = { name => $item[1] } }
460
ea93df61 461qualified_name : /(\w+)\.(\w+)/
72aa2647 462 { $return = { db_name => $1, name => $2 } }
463
464field_name : NAME
465
7179cb09 466constraint_name : NAME
467
72aa2647 468conflict_clause : /on conflict/i conflict_algorigthm
469
470conflict_algorigthm : /(rollback|abort|fail|ignore|replace)/i
471
472parens_field_list : '(' column_list ')'
473 { $item[2] }
474
475column_list : field_name(s /,/)
476
477parens_value_list : '(' VALUE(s /,/) ')'
478 { $item[2] }
479
480expr : /[^)]+/
481
482sort_order : /(ASC|DESC)/i
483
484#
485# Create Trigger
486
2661d702 487create : CREATE TEMPORARY(?) TRIGGER NAME before_or_after(?) database_event ON table_name trigger_action SEMICOLON
72aa2647 488 {
489 my $table_name = $item[8]->{'name'};
70698e1c 490 push @triggers, {
72aa2647 491 name => $item[4],
492 is_temporary => $item[2][0] ? 1 : 0,
493 when => $item[5][0],
494 instead_of => 0,
d0fcb05d 495 db_events => [ $item[6] ],
72aa2647 496 action => $item[9],
da2f5992 497 on_table => $table_name,
72aa2647 498 }
499 }
500
501create : CREATE TEMPORARY(?) TRIGGER NAME instead_of database_event ON view_name trigger_action
502 {
503 my $table_name = $item[8]->{'name'};
70698e1c 504 push @triggers, {
72aa2647 505 name => $item[4],
506 is_temporary => $item[2][0] ? 1 : 0,
507 when => undef,
508 instead_of => 1,
d0fcb05d 509 db_events => [ $item[6] ],
72aa2647 510 action => $item[9],
da2f5992 511 on_table => $table_name,
72aa2647 512 }
513 }
514
515database_event : /(delete|insert|update)/i
516
517database_event : /update of/i column_list
518
519trigger_action : for_each(?) when(?) BEGIN_C trigger_step(s) END_C
520 {
521 $return = {
522 for_each => $item[1][0],
523 when => $item[2][0],
524 steps => $item[4],
525 }
526 }
527
d0fcb05d 528for_each : /FOR EACH ROW/i
72aa2647 529
530when : WHEN expr { $item[2] }
531
9bf756df 532string :
bdf60588 533 /'(\.|''|[^\\'])*'/
9bf756df 534
535nonstring : /[^;\'"]+/
536
9644fab3 537statement_body : string | nonstring
9bf756df 538
9644fab3 539trigger_step : /(select|delete|insert|update)/i statement_body(s?) SEMICOLON
72aa2647 540 {
67a1819d 541 $return = join( ' ', $item[1], join ' ', @{ $item[2] || [] } )
ea93df61 542 }
72aa2647 543
544before_or_after : /(before|after)/i { $return = lc $1 }
545
546instead_of : /instead of/i
547
ae602737 548if_exists : /if exists/i
549
72aa2647 550view_name : qualified_name
551
ae602737 552trigger_name : qualified_name
553
72aa2647 554#
70698e1c 555# Create View
556#
ea93df61 557create : CREATE TEMPORARY(?) VIEW view_name AS select_statement
70698e1c 558 {
559 push @views, {
560 name => $item[4]->{'name'},
ea93df61 561 sql => $item[6],
70698e1c 562 is_temporary => $item[2][0] ? 1 : 0,
563 }
564 }
565
566select_statement : SELECT /[^;]+/ SEMICOLON
567 {
568 $return = join( ' ', $item[1], $item[2] );
569 }
570
571#
72aa2647 572# Tokens
573#
574BEGIN_C : /begin/i
575
576END_C : /end/i
577
f9c96971 578TRANSACTION: /transaction/i
579
72aa2647 580CREATE : /create/i
581
582TEMPORARY : /temp(orary)?/i { 1 }
583
584TABLE : /table/i
585
586INDEX : /index/i
587
588NOT_NULL : /not null/i
589
590PRIMARY_KEY : /primary key/i
591
0dbd2362 592FOREIGN_KEY : /foreign key/i
593
72aa2647 594CHECK_C : /check/i
595
596DEFAULT : /default/i
597
598TRIGGER : /trigger/i
599
70698e1c 600VIEW : /view/i
601
602SELECT : /select/i
603
72aa2647 604ON : /on/i
605
70698e1c 606AS : /as/i
607
72aa2647 608WORD : /\w+/
609
610WHEN : /when/i
611
08e01b5c 612REFERENCES : /references/i
613
7179cb09 614CONSTRAINT : /constraint/i
615
6ceb5f1c 616AUTOINCREMENT : /autoincrement/i
617
72aa2647 618UNIQUE : /unique/i { 1 }
619
620SEMICOLON : ';'
621
6ceb5f1c 622NAME : /["']?(\w+)["']?/ { $return = $1 }
72aa2647 623
70698e1c 624VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
72aa2647 625 { $item[1] }
ea93df61 626 | /'.*?'/
627 {
628 # remove leading/trailing quotes
72aa2647 629 my $val = $item[1];
630 $val =~ s/^['"]|['"]$//g;
631 $return = $val;
632 }
633 | /NULL/
634 { 'NULL' }
ac9c4e2e 635 | /CURRENT_TIMESTAMP/i
636 { 'CURRENT_TIMESTAMP' }
72aa2647 637
bdf60588 638END_OF_GRAMMAR
639
72aa2647 640
72aa2647 641sub parse {
642 my ( $translator, $data ) = @_;
bdf60588 643
644 # Enable warnings within the Parse::RecDescent module.
645 local $::RD_ERRORS = 1 unless defined $::RD_ERRORS; # Make sure the parser dies when it encounters an error
646 local $::RD_WARN = 1 unless defined $::RD_WARN; # Enable warnings. This will warn on unused rules &c.
647 local $::RD_HINT = 1 unless defined $::RD_HINT; # Give out hints to help fix problems.
72aa2647 648
649 local $::RD_TRACE = $translator->trace ? 1 : undef;
650 local $DEBUG = $translator->debug;
651
bdf60588 652 my $parser = ddl_parser_instance('SQLite');
72aa2647 653
654 my $result = $parser->startrule($data);
655 return $translator->error( "Parse failed." ) unless defined $result;
656 warn Dumper( $result ) if $DEBUG;
657
658 my $schema = $translator->schema;
ea93df61 659 my @tables =
b727fc08 660 map { $_->[1] }
ea93df61 661 sort { $a->[0] <=> $b->[0] }
b727fc08 662 map { [ $result->{'tables'}{ $_ }->{'order'}, $_ ] }
663 keys %{ $result->{'tables'} };
72aa2647 664
665 for my $table_name ( @tables ) {
70698e1c 666 my $tdata = $result->{'tables'}{ $table_name };
ea93df61 667 my $table = $schema->add_table(
72aa2647 668 name => $tdata->{'name'},
669 ) or die $schema->error;
670
671 $table->comments( $tdata->{'comments'} );
672
673 for my $fdata ( @{ $tdata->{'fields'} } ) {
674 my $field = $table->add_field(
675 name => $fdata->{'name'},
676 data_type => $fdata->{'data_type'},
677 size => $fdata->{'size'},
678 default_value => $fdata->{'default'},
679 is_auto_increment => $fdata->{'is_auto_inc'},
680 is_nullable => $fdata->{'is_nullable'},
681 comments => $fdata->{'comments'},
682 ) or die $table->error;
683
684 $table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
685
686 for my $cdata ( @{ $fdata->{'constraints'} } ) {
687 next unless $cdata->{'type'} eq 'foreign_key';
688 $cdata->{'fields'} ||= [ $field->name ];
689 push @{ $tdata->{'constraints'} }, $cdata;
690 }
691 }
692
693 for my $idata ( @{ $tdata->{'indices'} || [] } ) {
694 my $index = $table->add_index(
695 name => $idata->{'name'},
56b9e6a5 696 type => uc ($idata->{'type'}||''),
72aa2647 697 fields => $idata->{'fields'},
698 ) or die $table->error;
699 }
700
701 for my $cdata ( @{ $tdata->{'constraints'} || [] } ) {
702 my $constraint = $table->add_constraint(
703 name => $cdata->{'name'},
704 type => $cdata->{'type'},
705 fields => $cdata->{'fields'},
706 reference_table => $cdata->{'reference_table'},
707 reference_fields => $cdata->{'reference_fields'},
708 match_type => $cdata->{'match_type'} || '',
ea93df61 709 on_delete => $cdata->{'on_delete'}
08e01b5c 710 || $cdata->{'on_delete_do'},
ea93df61 711 on_update => $cdata->{'on_update'}
08e01b5c 712 || $cdata->{'on_update_do'},
72aa2647 713 ) or die $table->error;
714 }
715 }
716
70698e1c 717 for my $def ( @{ $result->{'views'} || [] } ) {
718 my $view = $schema->add_view(
719 name => $def->{'name'},
720 sql => $def->{'sql'},
721 );
722 }
723
724 for my $def ( @{ $result->{'triggers'} || [] } ) {
725 my $view = $schema->add_trigger(
726 name => $def->{'name'},
727 perform_action_when => $def->{'when'},
d0fcb05d 728 database_events => $def->{'db_events'},
70698e1c 729 action => $def->{'action'},
da2f5992 730 on_table => $def->{'on_table'},
70698e1c 731 );
732 }
733
72aa2647 734 return 1;
735}
736
7371;
738
739# -------------------------------------------------------------------
740# All wholsome food is caught without a net or a trap.
741# William Blake
742# -------------------------------------------------------------------
743
744=pod
745
746=head1 AUTHOR
747
08e01b5c 748Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
72aa2647 749
750=head1 SEE ALSO
751
752perl(1), Parse::RecDescent, SQL::Translator::Schema.
753
754=cut