From 3845845b89cb3c1c8a877ca6624d29cccfd9d7c3 Mon Sep 17 00:00:00 2001
From: ygrek <ygrek@autistici.org>
Date: Fri, 15 Feb 2019 15:52:49 +0200
Subject: [PATCH] sql: + SUBSTRING

close #62
---
 lib/sql.ml             |  3 +++
 lib/sql_lexer.mll      |  2 ++
 lib/sql_parser.mly     |  5 ++++-
 test/out/substring.xml | 40 ++++++++++++++++++++++++++++++++++++++++
 test/substring.sql     | 19 +++++++++++++++++++
 5 files changed, 68 insertions(+), 1 deletion(-)
 create mode 100644 test/out/substring.xml
 create mode 100644 test/substring.sql

diff --git a/lib/sql.ml b/lib/sql.ml
index 1f8ae1e..4aef52d 100644
--- a/lib/sql.ml
+++ b/lib/sql.ml
@@ -352,4 +352,7 @@ let () =
   "from_unixtime" |> monomorphic Datetime [Int];
   "from_unixtime" |> monomorphic Text [Int;Text];
   "any_value" |> add 1 (F (Var 0,[Var 0])); (* 'a -> 'a but not aggregate *)
+  "substring" |> monomorphic Text [Text; Int];
+  "substring" |> monomorphic Text [Text; Int; Int];
+  "substring_index" |> monomorphic Text [Text; Text; Int];
   ()
diff --git a/lib/sql_lexer.mll b/lib/sql_lexer.mll
index 661b3ff..a1758ba 100644
--- a/lib/sql_lexer.mll
+++ b/lib/sql_lexer.mll
@@ -151,6 +151,8 @@ let keywords =
    "begin", BEGIN;
    "comment", COMMENT;
    "language", LANGUAGE;
+   "substring", SUBSTRING;
+   "substr", SUBSTRING;
   ] in (* more *)
   let all token l = k := !k @ List.map (fun x -> x,token) l in
   all DATETIME_FUNC ["current_date";"current_timestamp";"current_time";"localtime";"localtimestamp";"now";];
diff --git a/lib/sql_parser.mly b/lib/sql_parser.mly
index 0c54210..546c0ff 100644
--- a/lib/sql_parser.mly
+++ b/lib/sql_parser.mly
@@ -36,7 +36,7 @@
        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 INTERVAL
+       OF WITH NOWAIT ACTION NO IS INTERVAL SUBSTRING
 %token FUNCTION PROCEDURE LANGUAGE RETURNS OUT INOUT BEGIN COMMENT
 %token MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
        SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND
@@ -330,6 +330,9 @@ expr:
     | e1=expr IN table=IDENT { Tables.check table; e1 }
     | LPAREN select=select_stmt RPAREN { Select (select, `AsValue) }
     | PARAM { Param ($1,Any) }
+    | SUBSTRING LPAREN s=expr FROM p=expr FOR n=expr RPAREN
+    | SUBSTRING LPAREN s=expr COMMA p=expr COMMA n=expr RPAREN { Fun (Function.lookup "substring" 3, [s;p;n]) }
+    | SUBSTRING LPAREN s=expr either(FROM,COMMA) p=expr RPAREN { Fun (Function.lookup "substring" 2, [s;p]) }
     | f=IDENT LPAREN p=func_params RPAREN { Fun (Function.lookup f (List.length p), p) }
     | expr IS NOT? NULL { Fun (Ret Bool, [$1]) }
     | e1=expr IS NOT? distinct_from? e2=expr { poly Bool [e1;e2] }
diff --git a/test/out/substring.xml b/test/out/substring.xml
new file mode 100644
index 0000000..cf51712
--- /dev/null
+++ b/test/out/substring.xml
@@ -0,0 +1,40 @@
+<?xml version="1.0"?>
+
+<sqlgg>
+ <stmt name="select_0" sql="SELECT SUBSTRING('Quadratically',5)" kind="select" cardinality="1">
+  <in/>
+  <out>
+   <value name="_0" type="Text"/>
+  </out>
+ </stmt>
+ <stmt name="select_1" sql="SELECT SUBSTRING('foobarbar' FROM 4)" kind="select" cardinality="1">
+  <in/>
+  <out>
+   <value name="_0" type="Text"/>
+  </out>
+ </stmt>
+ <stmt name="select_2" sql="SELECT SUBSTRING('Quadratically',5,6)" kind="select" cardinality="1">
+  <in/>
+  <out>
+   <value name="_0" type="Text"/>
+  </out>
+ </stmt>
+ <stmt name="select_3" sql="SELECT SUBSTRING('Sakila', -3)" kind="select" cardinality="1">
+  <in/>
+  <out>
+   <value name="_0" type="Text"/>
+  </out>
+ </stmt>
+ <stmt name="select_4" sql="SELECT SUBSTRING('Sakila', -5, 3)" kind="select" cardinality="1">
+  <in/>
+  <out>
+   <value name="_0" type="Text"/>
+  </out>
+ </stmt>
+ <stmt name="select_5" sql="SELECT SUBSTRING('Sakila' FROM -4 FOR 2)" kind="select" cardinality="1">
+  <in/>
+  <out>
+   <value name="_0" type="Text"/>
+  </out>
+ </stmt>
+</sqlgg>
diff --git a/test/substring.sql b/test/substring.sql
new file mode 100644
index 0000000..f02c69e
--- /dev/null
+++ b/test/substring.sql
@@ -0,0 +1,19 @@
+-- https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring
+
+SELECT SUBSTRING('Quadratically',5);
+--> 'ratically'
+
+SELECT SUBSTRING('foobarbar' FROM 4);
+--> 'barbar'
+
+SELECT SUBSTRING('Quadratically',5,6);
+--> 'ratica'
+
+SELECT SUBSTRING('Sakila', -3);
+--> 'ila'
+
+SELECT SUBSTRING('Sakila', -5, 3);
+--> 'aki'
+
+SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
+--> 'ki'
-- 
2.11.4.GIT