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+y 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" "SELECT * FROM t1 NATURAL JOIN t2" (ints ["j";"i";"k"]) [];
  48   tt ~msg:"using" "SELECT * FROM t1 JOIN t2 USING (j)" (ints ["j";"i";"k"]) [];
  49   ()
  50
  51 let test_misc () =
  52   let test =
  53     let printer = Show.show<int list> in
  54     fun x y z -> assert_equal ~printer (RA.Scheme.natural x y) z
  55   in
  56   test [1;2;3;4] [1;2;5;6] [1;2;3;4;5;6];
  57   test [1;2;3;4] [4;3;2;1] [1;2;3;4];
  58   test [1;2;3;4] [5;4;3;7;5;7] [3;4;1;2;5;7;5;7];
  59   test [1;2;3;4] [5;2;2] [2;1;3;4;5]; (* ?! *)
  60   ()
  61
  62 let run () =
  63   let tests =
  64   [
  65     "simple" >:: test;
  66     "JOIN result columns" >:: test_join_result_cols;
  67     "misc" >:: test_misc;
  68   ]
  69   in
  70   let test_suite = "main" >::: tests in
  71   ignore (run_test_tt test_suite)