test.ml

   1
   2 open OUnit
   3 open RA
   4 open Sql.Type
   5 open Stmt
   6 open ListMore
   7
   8 let named s = (Some s,(0,0))
   9 let param = (None,(0,0))
  10 let p name t = (named name, Some t)
  11
  12 let cmp_params p1 p2 =
  13   try
  14     List.for_all2 (fun ((name1,pos1),t1) ((name2,pos2),t2) ->
  15       name1 = name2 && t1 = t2 && pos1 = (0,0) && snd pos2 > fst pos2)
  16     p1 p2
  17   with
  18     _ -> false
  19
  20 let tt ?msg sql schema params =
  21   let print_schema = RA.Schema.to_string in
  22   let print_params = Stmt.params_to_string in
  23   let stmt = Main.parse_one_exn (sql,[]) in
  24   assert_equal ?msg ~printer:print_schema schema stmt.schema;
  25   assert_equal ?msg ~cmp:cmp_params ~printer:print_params params stmt.params
  26
  27 let wrong sql =
  28   ("Expected error in : " ^ sql) @? (try ignore (Main.parse_one_exn (sql,[])); false with exn -> true)
  29
  30 (* let wrong sql = assert_equal None (Main.parse_one (sql,[])) *)
  31
  32 let test () =
  33   tt "CREATE TABLE test (id INT, str TEXT, name TEXT)"
  34      []
  35      [];
  36   tt "SELECT str FROM test WHERE id=?"
  37      [attr "str" Text]
  38      [param,Some Int];
  39   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"
  40      [attr "x" Text; attr "z" Int]
  41      [param,Some Int; named "id", Some Int; named "lim",Some Int; ];
  42   tt "select test.name,other.name as other_name from test, test as other where test.id=other.id + @delta"
  43      [attr "name" Text; attr "other_name" Text]
  44      [named "delta", Some Int];
  45   tt "select test.name from test where test.id + @x = ? or test.id - @x = ?"
  46      [attr "name" Text;]
  47      [named "x", Some Int; param, Some Int; named "x", Some Int; param, Some Int;];
  48   tt "insert into test values"
  49      []
  50      [p "id" Int; p "str" Text; p "name" Text];
  51   tt "insert into test (str,name) values"
  52      []
  53      [p "str" Text; p "name" Text];
  54   tt "insert into test values (2,'hello' || ' world',@name)"
  55      []
  56      [p "name" Text];
  57   wrong "insert into test values (1,2)";
  58   wrong "insert into test (str,name) values (1,'str','name')";
  59   ()
  60
  61 (*
  62   see MySQL 5.4 refman -- 12.2.8.1. JOIN Syntax
  63   see SQL:2008 -- 7.7 <joined table>
  64 *)
  65 let test_join_result_cols () =
  66   Tables.reset ();
  67   let ints = List.map (fun name -> attr name Int) in
  68   tt "CREATE TABLE t1 (i INT, j INT)" [] [];
  69   tt "CREATE TABLE t2 (k INT, j INT)" [] [];
  70   tt "SELECT * FROM t1 JOIN t2 ON i=t1.j" (ints ["i";"j";"k";"j"]) [];
  71   tt ~msg:"NATURAL JOIN"
  72     "SELECT * FROM t1 NATURAL JOIN t2" (ints ["j";"i";"k"]) [];
  73   tt ~msg:"JOIN USING"
  74     "SELECT * FROM t1 JOIN t2 USING (j)" (ints ["j";"i";"k"]) [];
  75   tt ~msg:"NATURAL JOIN with common column in WHERE"
  76     "SELECT * FROM t1 NATURAL JOIN t2 WHERE j > @x"
  77     (ints ["j";"i";"k"])
  78     [named "x",Some Int];
  79   tt ~msg:"NATURAL JOIN with common column qualified in WHERE"
  80     "SELECT * FROM t1 NATURAL JOIN t2 WHERE t2.j > @x"
  81     (ints ["j";"i";"k"])
  82     [named "x",Some Int];
  83   ()
  84
  85 let test_misc () =
  86   let test =
  87     let printer = Show.show<int list> in
  88     fun x y z -> assert_equal ~printer (RA.Schema.natural_ x y) z
  89   in
  90   test [1;2;3;4] [1;2;5;6] [1;2;3;4;5;6];
  91   test [1;2;3;4] [4;3;2;1] [1;2;3;4];
  92   test [1;2;3;4] [5;4;3;7;5;7] [3;4;1;2;5;7;5;7];
  93   test [1;2;3;4] [5;2;2] [2;1;3;4;5]; (* ?! *)
  94   ()
  95
  96 let run () =
  97   let tests =
  98   [
  99     "simple" >:: test;
 100     "JOIN result columns" >:: test_join_result_cols;
 101     "misc" >:: test_misc;
 102   ]
 103   in
 104   let test_suite = "main" >::: tests in
 105   ignore (run_test_tt test_suite)