src/test.ml

   1
   2 open OUnit
   3 open RA
   4 open Sql.Type
   5 open Stmt
   6
   7 let named s = (Some s,(0,0))
   8 let param = (None,(0,0))
   9 let p name t = (named name, t)
  10
  11 let cmp_params p1 p2 =
  12   try
  13     List.for_all2 (fun ((name1,pos1),t1) ((name2,pos2),t2) ->
  14       name1 = name2 && t1 = t2 && pos1 = (0,0) && snd pos2 > fst pos2)
  15     p1 p2
  16   with
  17     _ -> false
  18
  19 let tt ?msg sql ?kind schema params =
  20   let msg = Option.default sql msg in
  21   match Main.parse_one (sql,[]) with
  22   | None -> assert_failure "Failed to parse"
  23   | Some stmt ->
  24       assert_equal ~msg ~printer:RA.Schema.to_string schema stmt.schema;
  25       assert_equal ~msg ~cmp:cmp_params ~printer:Stmt.params_to_string params stmt.params;
  26       match kind with
  27       | Some k -> assert_equal ~msg ~printer:Show.show<Stmt.kind> k stmt.kind
  28       | None -> ()
  29
  30 let wrong sql =
  31   ("Expected error in : " ^ sql) @? (try ignore (Main.parse_one_exn (sql,[])); false with _ -> true)
  32
  33 (* let wrong sql = assert_equal None (Main.parse_one (sql,[])) *)
  34
  35 let test () =
  36   tt "CREATE TABLE test (id INT, str TEXT, name TEXT)" [] [];
  37   tt "SELECT str FROM test WHERE id=?"
  38      [attr "str" Text]
  39      [param, Int];
  40   tt "SELECT x,y+? AS z FROM (SELECT id AS y,CONCAT(str,name) AS x FROM test WHERE id=@id*2) ORDER BY x,x+z LIMIT @lim"
  41      [attr "x" Text; attr "z" Int]
  42      [param,Int; named "id", Int; named "lim",Int; ];
  43   tt "select test.name,other.name as other_name from test, test as other where test.id=other.id + @delta"
  44      [attr "name" Text; attr "other_name" Text]
  45      [named "delta", Int];
  46   tt "select test.name from test where test.id + @x = ? or test.id - @x = ?"
  47      [attr "name" Text;]
  48      [named "x", Int; param, Int; named "x", Int; param, Int;];
  49   tt "insert into test values"
  50      []
  51      [p "id" Int; p "str" Text; p "name" Text];
  52   tt "insert into test (str,name) values"
  53      []
  54      [p "str" Text; p "name" Text];
  55   tt "insert into test values (2,'hello' || ' world',@name)"
  56      []
  57      [p "name" Text];
  58   tt "insert or replace into test values (2,?,?)" [] [param,Text; param,Text;];
  59   tt "replace into test values (2,?,?)" [] [param,Text; param,Text;];
  60   tt "select str, case when id > @id then name when id < @id then 'qqq' else @def end as q from test"
  61     [attr "str" Text; attr "q" Any] (* FIXME `Func *)
  62     [p "id" Int; p "id" Int; p "def" Any];
  63   wrong "insert into test values (1,2)";
  64   wrong "insert into test (str,name) values (1,'str','name')";
  65   (* check precedence of boolean and arithmetic operators *)
  66   tt "select str from test where id>=@id and id-@x<@id"
  67     [attr "str" Text;]
  68     [p "id" Int; p "x" Int; p "id" Int];
  69   ()
  70
  71 let test2 () =
  72   tt "CREATE TABLE test2 (id INT, str TEXT)" [] [];
  73   tt    "update test, (select * from test2) as x set str = x.str where test.id=x.id" [] [];
  74   tt    "update test, (select * from test2) as x set name = x.str where test.id=x.id" [] [];
  75   tt    "update test, (select * from test2) as x set test.str = x.str where test.id=x.id" [] [];
  76   wrong "update test, (select * from test2) as x set test.name = x.name where test.id=x.id";
  77   wrong "update test, (select * from test2) as x set test.str = str where test.id=x.id";
  78   ()
  79
  80 let test3 () =
  81   tt "SELECT id FROM test WHERE str IN ( SELECT str FROM test2 )" [attr "id" Int] [];
  82   todo "tuples";
  83   (* from http://stackoverflow.com/questions/1063866/sql-portability-gotchas/1063946#1063946 *)
  84   tt "SELECT id FROM test WHERE (id, str) IN ( SELECT id, str FROM test2)" [attr "id" Int] [];
  85   ()
  86
  87 let test4  () =
  88   let a = [attr "" Int] in
  89   tt "CREATE TABLE test4 (x INT, y INT)" [] [];
  90   tt "select max(*) from test4" a [] ~kind:(Select `One);
  91   tt "select max(x) as q from test4" [attr "q" Int] [] ~kind:(Select `One);
  92   tt "select max(x,y) from test4" a [] ~kind:(Select `Nat);
  93   tt "select max(x,y) from test4 limit 1" a [] ~kind:(Select `Zero_one);
  94   tt "select max(x,y) from test4 limit 2" a [] ~kind:(Select `Nat);
  95   tt "select 1+2 from test4" a [] ~kind:(Select `Zero_one);
  96   tt "select least(10+unix_timestamp(),random()), concat('test',upper('qqqq')) from test"
  97     [attr "" Int; attr "" Text] [] ~kind:(Select `Zero_one);
  98   tt "select greatest(10,x) from test4" a [] ~kind:(Select `Nat);
  99   tt "select 1+2 from test4 where x=y" a [] ~kind:(Select `Nat);
 100   tt "select max(x) as q from test4 where y = x + @n" [attr "q" Int] [named "n", Int] ~kind:(Select `One);
 101   todo "single row";
 102   tt "select coalesce(max(x),0) as q from test4 where y = x + @n" [attr "q" Int] [named "n", Int] ~kind:(Select `One);
 103   ()
 104
 105 let test_parsing () =
 106   tt "CREATE TABLE test5_1 (x INT NOT NULL, y INT DEFAULT -1) ENGINE=MEMORY" [] [];
 107   tt "SELECT 2+3, 2+-3, -10 FROM test5_1" [attr "" Int; attr "" Int; attr "" Int] [];
 108   ()
 109
 110 (*
 111   see MySQL 5.4 refman -- 12.2.8.1. JOIN Syntax
 112   see SQL:2008 -- 7.7 <joined table>
 113 *)
 114 let test_join_result_cols () =
 115   Tables.reset ();
 116   let ints = List.map (fun name -> attr name Int) in
 117   tt "CREATE TABLE t1 (i INT, j INT)" [] [];
 118   tt "CREATE TABLE t2 (k INT, j INT)" [] [];
 119   tt "SELECT * FROM t1 JOIN t2 ON i=t1.j" (ints ["i";"j";"k";"j"]) [];
 120   tt ~msg:"NATURAL JOIN"
 121     "SELECT * FROM t1 NATURAL JOIN t2" (ints ["j";"i";"k"]) [];
 122   tt ~msg:"JOIN USING"
 123     "SELECT * FROM t1 JOIN t2 USING (j)" (ints ["j";"i";"k"]) [];
 124   tt ~msg:"NATURAL JOIN with common column in WHERE"
 125     "SELECT * FROM t1 NATURAL JOIN t2 WHERE j > @x"
 126     (ints ["j";"i";"k"])
 127     [named "x",Int];
 128   tt ~msg:"NATURAL JOIN with common column qualified in WHERE"
 129     "SELECT * FROM t1 NATURAL JOIN t2 WHERE t2.j > @x"
 130     (ints ["j";"i";"k"])
 131     [named "x",Int];
 132   ()
 133
 134 let test_misc () =
 135   let test =
 136     let printer = Show.show<int list> in
 137     fun x y z -> assert_equal ~printer (RA.Schema.natural_ x y) z
 138   in
 139   test [1;2;3;4] [1;2;5;6] [1;2;3;4;5;6];
 140   test [1;2;3;4] [4;3;2;1] [1;2;3;4];
 141   test [1;2;3;4] [5;4;3;7;5;7] [3;4;1;2;5;7;5;7];
 142   test [1;2;3;4] [5;2;2] [2;1;3;4;5]; (* ?! *)
 143   ()
 144
 145 let test_enum () =
 146   tt "CREATE TABLE test6 (x enum('true','false') COLLATE utf8_bin NOT NULL, y INT DEFAULT 0) ENGINE=MyISAM DEFAULT CHARSET=utf8" [] [];
 147   tt "SELECT * FROM test6" [attr "x" Text; attr "y" Int] [];
 148   tt "SELECT x, y+10 FROM test6" [attr "x" Text; attr "" Int] [];
 149   ()
 150
 151 let run () =
 152   let tests =
 153   [
 154     "simple" >:: test;
 155     "multi-table UPDATE" >:: test2;
 156     "gotchas" >:: test3;
 157     "single-row SELECT" >:: test4;
 158     "parsing" >:: test_parsing;
 159     "JOIN result columns" >:: test_join_result_cols;
 160     "misc" >:: test_misc;
 161     "enum" >:: test_enum;
 162   ]
 163   in
 164   let test_suite = "main" >::: tests in
 165   ignore (run_test_tt test_suite)