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
=
14 List.for_all2
(fun ((name1
,pos1
),t1
) ((name2
,pos2
),t2
) ->
15 name1
= name2
&& t1
= t2
&& pos1
= (0,0) && snd pos2
> fst pos2
)
20 let tt ?msg sql schema params
=
21 match Main.parse_one
(sql
,[]) with
22 | None
-> assert_failure
("Failed to parse " ^
(Option.default
"" msg
))
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
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,[])) *)
33 tt "CREATE TABLE test (id INT, str TEXT, name TEXT)" [] [];
34 tt "SELECT str FROM test WHERE id=?"
37 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"
38 [attr
"x" Text
; attr
"z" Int
]
39 [param,Int
; named "id", Int
; named "lim",Int
; ];
40 tt "select test.name,other.name as other_name from test, test as other where test.id=other.id + @delta"
41 [attr
"name" Text
; attr
"other_name" Text
]
43 tt "select test.name from test where test.id + @x = ? or test.id - @x = ?"
45 [named "x", Int
; param, Int
; named "x", Int
; param, Int
;];
46 tt "insert into test values"
48 [p "id" Int
; p "str" Text
; p "name" Text
];
49 tt "insert into test (str,name) values"
51 [p "str" Text
; p "name" Text
];
52 tt "insert into test values (2,'hello' || ' world',@name)"
55 wrong "insert into test values (1,2)";
56 wrong "insert into test (str,name) values (1,'str','name')";
60 tt "CREATE TABLE test2 (id INT, str TEXT)" [] [];
61 tt "update test, (select * from test2) as x set str = x.str where test.id=x.id" [] [];
62 tt "update test, (select * from test2) as x set name = x.str where test.id=x.id" [] [];
63 tt "update test, (select * from test2) as x set test.str = x.str where test.id=x.id" [] [];
64 wrong "update test, (select * from test2) as x set test.name = x.name where test.id=x.id";
65 wrong "update test, (select * from test2) as x set test.str = str where test.id=x.id";
69 tt "SELECT id FROM test WHERE str IN ( SELECT str FROM test2 )" [attr
"id" Int
] [];
71 (* from http://stackoverflow.com/questions/1063866/sql-portability-gotchas/1063946#1063946 *)
72 tt "SELECT id FROM test WHERE (id, str) IN ( SELECT id, str FROM test2)" [attr
"id" Int
] [];
76 see MySQL 5.4 refman -- 12.2.8.1. JOIN Syntax
77 see SQL:2008 -- 7.7 <joined table>
79 let test_join_result_cols () =
81 let ints = List.map
(fun name
-> attr name Int
) in
82 tt "CREATE TABLE t1 (i INT, j INT)" [] [];
83 tt "CREATE TABLE t2 (k INT, j INT)" [] [];
84 tt "SELECT * FROM t1 JOIN t2 ON i=t1.j" (ints ["i";"j";"k";"j"]) [];
85 tt ~msg
:"NATURAL JOIN"
86 "SELECT * FROM t1 NATURAL JOIN t2" (ints ["j";"i";"k"]) [];
88 "SELECT * FROM t1 JOIN t2 USING (j)" (ints ["j";"i";"k"]) [];
89 tt ~msg
:"NATURAL JOIN with common column in WHERE"
90 "SELECT * FROM t1 NATURAL JOIN t2 WHERE j > @x"
93 tt ~msg
:"NATURAL JOIN with common column qualified in WHERE"
94 "SELECT * FROM t1 NATURAL JOIN t2 WHERE t2.j > @x"
101 let printer = Show.show
<int list
> in
102 fun x y z
-> assert_equal ~
printer (RA.Schema.natural_ x y
) z
104 test [1;2;3;4] [1;2;5;6] [1;2;3;4;5;6];
105 test [1;2;3;4] [4;3;2;1] [1;2;3;4];
106 test [1;2;3;4] [5;4;3;7;5;7] [3;4;1;2;5;7;5;7];
107 test [1;2;3;4] [5;2;2] [2;1;3;4;5]; (* ?! *)
114 "multi-table UPDATE" >:: test2;
116 "JOIN result columns" >:: test_join_result_cols;
117 "misc" >:: test_misc;
120 let test_suite = "main" >::: tests in
121 ignore
(run_test_tt
test_suite)