test.ml

   1
   2 open OUnit
   3 open RA
   4 open Sql.Type
   5 open Stmt
   6
   7 let tt ?msg stmt scheme params =
   8   let print_scheme = RA.Scheme.to_string in
   9   let print_params = Stmt.params_to_string in
  10   let (s1,p1,_) =
  11     try
  12       Parser.parse_stmt stmt
  13     with
  14     | _ -> assert_failure "tt failed"
  15   in
  16   assert_equal ?msg ~printer:print_scheme scheme s1;
  17   assert_equal ?msg ~printer:print_params params p1
  18
  19 let test () =
  20   tt "CREATE TABLE test (id INT, str TEXT, name TEXT)"
  21      []
  22      [];
  23   tt "SELECT str FROM test WHERE id=?"
  24      [attr "str" Text]
  25      [Next,Some Int];
  26   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"
  27      [attr "x" Text; attr "z" Int]
  28      [Next,Some Int; Named "id", Some Int; Named "lim",Some Int; ];
  29   tt "select test.name,other.name as other_name from test, test as other where test.id=other.id + @delta"
  30      [attr "name" Text; attr "other_name" Text]
  31      [Named "delta", Some Int];
  32   tt "select test.name from test where test.id = @x + ? or test.id = @x - ?"
  33      [attr "name" Text;]
  34      [Named "x", Some Int; Next, Some Int; Named "x", Some Int; Next, Some Int;];
  35   ()
  36
  37 (*
  38   see MySQL 5.4 refman -- 12.2.8.1. JOIN Syntax
  39   see SQL:2008 -- 7.7 <joined table>
  40 *)
  41 let test_join_result_cols () =
  42   Tables.reset ();
  43   let ints = List.map (fun name -> attr name Int) in
  44   tt "CREATE TABLE t1 (i INT, j INT)" [] [];
  45   tt "CREATE TABLE t2 (k INT, j INT)" [] [];
  46   tt "SELECT * FROM t1 JOIN t2 ON i=t1.j" (ints ["i";"j";"k";"j"]) [];
  47   tt ~msg:"NATURAL JOIN"
  48     "SELECT * FROM t1 NATURAL JOIN t2" (ints ["j";"i";"k"]) [];
  49   tt ~msg:"JOIN USING"
  50     "SELECT * FROM t1 JOIN t2 USING (j)" (ints ["j";"i";"k"]) [];
  51   tt ~msg:"NATURAL JOIN with common column in WHERE"
  52     "SELECT * FROM t1 NATURAL JOIN t2 WHERE j > @x"
  53     (ints ["j";"i";"k"])
  54     [Named "x",Some Int];
  55   tt ~msg:"NATURAL JOIN with common column qualified in WHERE"
  56     "SELECT * FROM t1 NATURAL JOIN t2 WHERE t2.j > @x"
  57     (ints ["j";"i";"k"])
  58     [Named "x",Some Int];
  59   ()
  60
  61 let test_misc () =
  62   let test =
  63     let printer = Show.show<int list> in
  64     fun x y z -> assert_equal ~printer (RA.Scheme.natural x y) z
  65   in
  66   test [1;2;3;4] [1;2;5;6] [1;2;3;4;5;6];
  67   test [1;2;3;4] [4;3;2;1] [1;2;3;4];
  68   test [1;2;3;4] [5;4;3;7;5;7] [3;4;1;2;5;7;5;7];
  69   test [1;2;3;4] [5;2;2] [2;1;3;4;5]; (* ?! *)
  70   ()
  71
  72 let run () =
  73   let tests =
  74   [
  75     "simple" >:: test;
  76     "JOIN result columns" >:: test_join_result_cols;
  77     "misc" >:: test_misc;
  78   ]
  79   in
  80   let test_suite = "main" >::: tests in
  81   ignore (run_test_tt test_suite)