From d1fa845dd36ff279285e7429fcddc5960dbdf44b Mon Sep 17 00:00:00 2001 From: Roman Vorobets Date: Mon, 20 Jul 2015 22:34:34 +0800 Subject: [PATCH] sql: parse Mysql INTERVALs --- lib/sql_lexer.mll | 23 ++++++- lib/sql_parser.mly | 16 ++++- test/interval.sql | 22 +++++++ test/out/interval.xml | 168 ++++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 226 insertions(+), 3 deletions(-) create mode 100644 test/interval.sql create mode 100644 test/out/interval.xml diff --git a/lib/sql_lexer.mll b/lib/sql_lexer.mll index 65c0424..cad94c2 100644 --- a/lib/sql_lexer.mll +++ b/lib/sql_lexer.mll @@ -121,6 +121,27 @@ let keywords = "action", ACTION; "no", NO; "is", IS; + "interval", INTERVAL; + "microsecond", MICROSECOND; + "second", SECOND; + "minute", MINUTE; + "hour", HOUR; + "day", DAY; + "week", WEEK; + "month", MONTH; + "quarter", QUARTER; + "year", YEAR; + "second_microsecond", SECOND_MICROSECOND; + "minute_microsecond", MINUTE_MICROSECOND; + "minute_second", MINUTE_SECOND; + "hour_microsecond", HOUR_MICROSECOND; + "hour_second", HOUR_SECOND; + "hour_minute", HOUR_MINUTE; + "day_microsecond", DAY_MICROSECOND; + "day_second", DAY_SECOND; + "day_minute", DAY_MINUTE; + "day_hour", DAY_HOUR; + "year_month", YEAR_MONTH; ] in (* more *) let all token l = k := !k @ List.map (fun x -> x,token) l in all (FUNCTION (T.Int,true)) ["max"; "min"; "count";"sum";"avg"]; @@ -151,7 +172,7 @@ let keywords = all T_BLOB ["blob";"varbinary";"tinyblob";"mediumblob";"longblob"]; all T_TEXT ["text";"char";"varchar";"tinytext";"mediumtext";"longtext"]; all T_TEXT ["varchar2"]; (* oracle *) - all T_DATETIME ["datetime";"year";]; + all T_DATETIME ["datetime"]; !k (* diff --git a/lib/sql_parser.mly b/lib/sql_parser.mly index c6c450d..190d24d 100644 --- a/lib/sql_parser.mly +++ b/lib/sql_parser.mly @@ -37,7 +37,12 @@ DATETIME_FUNC DATE TIME TIMESTAMP ALTER ADD COLUMN CASCADE RESTRICT DROP GLOBAL LOCAL VALUE REFERENCES CHECK CONSTRAINT IGNORED AFTER INDEX FULLTEXT FIRST CASE WHEN THEN ELSE END CHANGE MODIFY DELAYED ENUM FOR SHARE MODE LOCK - OF WITH NOWAIT ACTION NO IS + OF WITH NOWAIT ACTION NO IS INTERVAL +%token MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR + SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND + HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE + DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR + YEAR_MONTH %token NUM_DIV_OP NUM_BIT_OP NUM_EQ_OP NUM_CMP_OP PLUS MINUS %token T_INTEGER T_BLOB T_TEXT T_FLOAT T_BOOLEAN T_DATETIME @@ -275,6 +280,7 @@ expr: { Fun ((Any,false),(list_filter_map identity [Some e1; Some e2; e3]),`None) } | unary_op expr { $2 } | MINUS expr %prec UNARY_MINUS { $2 } + | INTERVAL expr interval_unit { $2 } | LPAREN expr RPAREN { $2 } | attr_name { Column $1 } | v=literal_value | v=datetime_value { v } @@ -332,13 +338,19 @@ unary_op: EXCL { } | TILDE { } | NOT { } +interval_unit: MICROSECOND | SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR + | SECOND_MICROSECOND | MINUTE_MICROSECOND | MINUTE_SECOND + | HOUR_MICROSECOND | HOUR_SECOND | HOUR_MINUTE + | DAY_MICROSECOND | DAY_SECOND | DAY_MINUTE | DAY_HOUR + | YEAR_MONTH { } + sql_type_flavor: T_INTEGER UNSIGNED? ZEROFILL? { Int } | binary { Blob } | NATIONAL? text VARYING? charset? collate? { Text } | ENUM sequence(TEXT) charset? collate? { Text } | T_FLOAT PRECISION? { Float } | T_BOOLEAN { Bool } - | T_DATETIME | DATE | TIME | TIMESTAMP { Datetime } + | T_DATETIME | YEAR | DATE | TIME | TIMESTAMP { Datetime } binary: T_BLOB | BINARY | BINARY VARYING { } text: T_TEXT | T_TEXT LPAREN INTEGER RPAREN | CHARACTER { } diff --git a/test/interval.sql b/test/interval.sql new file mode 100644 index 0000000..c4ef3ca --- /dev/null +++ b/test/interval.sql @@ -0,0 +1,22 @@ +CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY AUTO_INCREMENT, stamp TIMESTAMP, `interval` INTEGER); + +SELECT * FROM events WHERE stamp + INTERVAL `interval` MICROSECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` SECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` MINUTE < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` HOUR < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` DAY < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` WEEK < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` MONTH < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` QUARTER < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` YEAR < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` SECOND_MICROSECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` MINUTE_MICROSECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` MINUTE_SECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` HOUR_MICROSECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` HOUR_SECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` HOUR_MINUTE < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` DAY_MICROSECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` DAY_SECOND < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` DAY_MINUTE < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` DAY_HOUR < NOW(); +SELECT * FROM events WHERE stamp + INTERVAL `interval` YEAR_MONTH < NOW(); diff --git a/test/out/interval.xml b/test/out/interval.xml new file mode 100644 index 0000000..8bf411b --- /dev/null +++ b/test/out/interval.xml @@ -0,0 +1,168 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + -- 2.11.4.GIT