test for UPDATE SELECT
[sqlgg.git] / test.ml
blob28193a8bbe4b306c39a86fd5498b2b40667f1837
2 open OUnit
3 open RA
4 open Sql.Type
5 open Stmt
6 open ListMore
8 let named s = (Some s,(0,0))
9 let param = (None,(0,0))
10 let p name t = (named name, t)
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
20 let tt ?msg sql schema params =
21 match Main.parse_one (sql,[]) with
22 | None -> assert_failure ("Failed to parse " ^ (Option.default "" msg))
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
27 let wrong sql =
28 ("Expected error in : " ^ sql) @? (try ignore (Main.parse_one_exn (sql,[])); false with exn -> true)
30 (* let wrong sql = assert_equal None (Main.parse_one (sql,[])) *)
32 let test () =
33 tt "CREATE TABLE test (id INT, str TEXT, name TEXT)" [] [];
34 tt "CREATE TABLE test2 (id INT, str TEXT)" [] [];
35 tt "SELECT str FROM test WHERE id=?"
36 [attr "str" Text]
37 [param, Int];
38 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"
39 [attr "x" Text; attr "z" Int]
40 [param,Int; named "id", Int; named "lim",Int; ];
41 tt "select test.name,other.name as other_name from test, test as other where test.id=other.id + @delta"
42 [attr "name" Text; attr "other_name" Text]
43 [named "delta", Int];
44 tt "select test.name from test where test.id + @x = ? or test.id - @x = ?"
45 [attr "name" Text;]
46 [named "x", Int; param, Int; named "x", Int; param, Int;];
47 tt "insert into test values"
49 [p "id" Int; p "str" Text; p "name" Text];
50 tt "insert into test (str,name) values"
52 [p "str" Text; p "name" Text];
53 tt "insert into test values (2,'hello' || ' world',@name)"
55 [p "name" Text];
56 wrong "insert into test values (1,2)";
57 wrong "insert into test (str,name) values (1,'str','name')";
58 tt "update test, (select * from test2) as x set test.name = x.name where test.id=x.id" [] [];
62 see MySQL 5.4 refman -- 12.2.8.1. JOIN Syntax
63 see SQL:2008 -- 7.7 <joined table>
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",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",Int];
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
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]; (* ?! *)
96 let run () =
97 let tests =
99 "simple" >:: test;
100 "JOIN result columns" >:: test_join_result_cols;
101 "misc" >:: test_misc;
104 let test_suite = "main" >::: tests in
105 ignore (run_test_tt test_suite)