src/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, 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 ?kind schema params =
  21   let msg = Option.default sql msg in
  22   match Main.parse_one (sql,[]) with
  23   | None -> assert_failure "Failed to parse"
  24   | Some stmt ->
  25       assert_equal ~msg ~printer:RA.Schema.to_string schema stmt.schema;
  26       assert_equal ~msg ~cmp:cmp_params ~printer:Stmt.params_to_string params stmt.params;
  27       match kind with
  28       | Some k -> assert_equal ~msg ~printer:Show.show<Stmt.kind> k stmt.kind
  29       | None -> ()
  30
  31 let wrong sql =
  32   ("Expected error in : " ^ sql) @? (try ignore (Main.parse_one_exn (sql,[])); false with _ -> true)
  33
  34 (* let wrong sql = assert_equal None (Main.parse_one (sql,[])) *)
  35
  36 let test () =
  37   tt "CREATE TABLE test (id INT, str TEXT, name TEXT)" [] [];
  38   tt "SELECT str FROM test WHERE id=?"
  39      [attr "str" Text]
  40      [param, Int];
  41   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"
  42      [attr "x" Text; attr "z" Int]
  43      [param,Int; named "id", Int; named "lim",Int; ];
  44   tt "select test.name,other.name as other_name from test, test as other where test.id=other.id + @delta"
  45      [attr "name" Text; attr "other_name" Text]
  46      [named "delta", Int];
  47   tt "select test.name from test where test.id + @x = ? or test.id - @x = ?"
  48      [attr "name" Text;]
  49      [named "x", Int; param, Int; named "x", Int; param, Int;];
  50   tt "insert into test values"
  51      []
  52      [p "id" Int; p "str" Text; p "name" Text];
  53   tt "insert into test (str,name) values"
  54      []
  55      [p "str" Text; p "name" Text];
  56   tt "insert into test values (2,'hello' || ' world',@name)"
  57      []
  58      [p "name" Text];
  59   tt "insert or replace into test values (2,?,?)" [] [param,Text; param,Text;];
  60   tt "replace into test values (2,?,?)" [] [param,Text; param,Text;];
  61   tt "select str, case when id > @id then name when id < @id then 'qqq' else @def end as q from test"
  62     [attr "str" Text; attr "q" Any] (* FIXME `Func *)
  63     [p "id" Int; p "id" Int; p "def" Any];
  64   wrong "insert into test values (1,2)";
  65   wrong "insert into test (str,name) values (1,'str','name')";
  66   (* check precedence of boolean and arithmetic operators *)
  67   tt "select str from test where id>=@id and id-@x<@id"
  68     [attr "str" Text;]
  69     [p "id" Int; p "x" Int; p "id" Int];
  70   ()
  71
  72 let test2 () =
  73   tt "CREATE TABLE test2 (id INT, str TEXT)" [] [];
  74   tt    "update test, (select * from test2) as x set str = x.str where test.id=x.id" [] [];
  75   tt    "update test, (select * from test2) as x set name = x.str where test.id=x.id" [] [];
  76   tt    "update test, (select * from test2) as x set test.str = x.str where test.id=x.id" [] [];
  77   wrong "update test, (select * from test2) as x set test.name = x.name where test.id=x.id";
  78   wrong "update test, (select * from test2) as x set test.str = str where test.id=x.id";
  79   ()
  80
  81 let test3 () =
  82   tt "SELECT id FROM test WHERE str IN ( SELECT str FROM test2 )" [attr "id" Int] [];
  83   todo "tuples";
  84   (* from http://stackoverflow.com/questions/1063866/sql-portability-gotchas/1063946#1063946 *)
  85   tt "SELECT id FROM test WHERE (id, str) IN ( SELECT id, str FROM test2)" [attr "id" Int] [];
  86   ()
  87
  88 let test4  () =
  89   let a = [attr "" Int] in
  90   tt "CREATE TABLE test4 (x INT, y INT)" [] [];
  91   tt "select max(*) from test4" a [] ~kind:(Select true);
  92   tt "select max(x) as q from test4" [attr "q" Int] [] ~kind:(Select true);
  93   tt "select max(x,y) from test4" a [] ~kind:(Select false);
  94   tt "select max(x,y) from test4 limit 1" a [] ~kind:(Select true);
  95   tt "select max(x,y) from test4 limit 2" a [] ~kind:(Select false);
  96   tt "select 1+2 from test4" a [] ~kind:(Select true);
  97   tt "select least(10+unix_timestamp(),random()), concat('test',upper('qqqq')) from test"
  98     [attr "" Int; attr "" Text] [] ~kind:(Select true);
  99   tt "select greatest(10,x) from test4" a [] ~kind:(Select false);
 100   tt "select 1+2 from test4 where x=y" a [] ~kind:(Select false);
 101   tt "select max(x) as q from test4 where y = x + @n" [attr "q" Int] [named "n", Int] ~kind:(Select true);
 102   todo "single row";
 103   tt "select coalesce(max(x),0) as q from test4 where y = x + @n" [attr "q" Int] [named "n", Int] ~kind:(Select true);
 104   ()
 105
 106 let test_parsing () =
 107   tt "CREATE TABLE test5_1 (x INT NOT NULL, y INT DEFAULT -1) ENGINE=MEMORY" [] [];
 108   tt "SELECT 2+3, 2+-3, -10 FROM test5_1" [attr "" Int; attr "" Int; attr "" Int] [];
 109   ()
 110
 111 (*
 112   see MySQL 5.4 refman -- 12.2.8.1. JOIN Syntax
 113   see SQL:2008 -- 7.7 <joined table>
 114 *)
 115 let test_join_result_cols () =
 116   Tables.reset ();
 117   let ints = List.map (fun name -> attr name Int) in
 118   tt "CREATE TABLE t1 (i INT, j INT)" [] [];
 119   tt "CREATE TABLE t2 (k INT, j INT)" [] [];
 120   tt "SELECT * FROM t1 JOIN t2 ON i=t1.j" (ints ["i";"j";"k";"j"]) [];
 121   tt ~msg:"NATURAL JOIN"
 122     "SELECT * FROM t1 NATURAL JOIN t2" (ints ["j";"i";"k"]) [];
 123   tt ~msg:"JOIN USING"
 124     "SELECT * FROM t1 JOIN t2 USING (j)" (ints ["j";"i";"k"]) [];
 125   tt ~msg:"NATURAL JOIN with common column in WHERE"
 126     "SELECT * FROM t1 NATURAL JOIN t2 WHERE j > @x"
 127     (ints ["j";"i";"k"])
 128     [named "x",Int];
 129   tt ~msg:"NATURAL JOIN with common column qualified in WHERE"
 130     "SELECT * FROM t1 NATURAL JOIN t2 WHERE t2.j > @x"
 131     (ints ["j";"i";"k"])
 132     [named "x",Int];
 133   ()
 134
 135 let test_misc () =
 136   let test =
 137     let printer = Show.show<int list> in
 138     fun x y z -> assert_equal ~printer (RA.Schema.natural_ x y) z
 139   in
 140   test [1;2;3;4] [1;2;5;6] [1;2;3;4;5;6];
 141   test [1;2;3;4] [4;3;2;1] [1;2;3;4];
 142   test [1;2;3;4] [5;4;3;7;5;7] [3;4;1;2;5;7;5;7];
 143   test [1;2;3;4] [5;2;2] [2;1;3;4;5]; (* ?! *)
 144   ()
 145
 146 let test_enum () =
 147   tt "CREATE TABLE test6 (x enum('true','false') COLLATE utf8_bin NOT NULL, y INT DEFAULT 0) ENGINE=MyISAM DEFAULT CHARSET=utf8" [] [];
 148   tt "SELECT * FROM test6" [attr "x" Text; attr "y" Int] [];
 149   tt "SELECT x, y+10 FROM test6" [attr "x" Text; attr "" Int] [];
 150   ()
 151
 152 let run () =
 153   let tests =
 154   [
 155     "simple" >:: test;
 156     "multi-table UPDATE" >:: test2;
 157     "gotchas" >:: test3;
 158     "single-row SELECT" >:: test4;
 159     "parsing" >:: test_parsing;
 160     "JOIN result columns" >:: test_join_result_cols;
 161     "misc" >:: test_misc;
 162     "enum" >:: test_enum;
 163   ]
 164   in
 165   let test_suite = "main" >::: tests in
 166   ignore (run_test_tt test_suite)