1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
module Filters = ImportAnalyser.Filters
module Chunk = ImportAnalyser.Chunk
(* Remove the externals links in the coonfiguration because the queries are
already complicated enough without *)
let conf = { ConfLoader.conf with externals = [] }
let path' =
Expression_builder.path ImportDataTypes.Path.{ alias = None; column = 1 }
(** Build a simple expression expressing a group. This expression will trigger
the creation of a CTE in the query. *)
let group_expression =
Expression_builder.(equal (max path' [ path' ] [ path' ]) integer_one)
(** Basic test ensuring we create nothing when we have nothing to create :) *)
let empty_predicates () =
let chunk_links = Chunk.create () in
let chunk_predicates = Filters.generate_sql ~conf [] chunk_links in
Alcotest.(check @@ pair Test_migration.chunk Test_migration.chunk)
"No predicates"
(Chunk.create (), Chunk.create ())
(chunk_links, chunk_predicates)
(** Simple numeric filter
The path is identified as pointing to a numeric column, and the associated
query will replace null with 0. *)
let simple_filter () =
let filter =
ImporterSyntax.CTE.of_filters Expression_builder.[ equal integer_one path' ]
in
let chunk_links = Chunk.create () in
let chunk_predicates = Filters.generate_sql ~conf filter chunk_links in
let expected_predicates = Chunk.create () in
Chunk.add_string expected_predicates " WHERE (1=COALESCE('source'.'col_1',0))";
Alcotest.(check @@ pair Test_migration.chunk Test_migration.chunk)
"Simple predicate"
(expected_predicates, Chunk.create ())
(chunk_links, chunk_predicates)
(** Combined filter
The path is identified as pointing to a literal column, andn the associated
query will replace null with empty string. *)
let multiple_filters () =
let filter =
ImporterSyntax.CTE.of_filters
Expression_builder.[ integer_one; equal path' literal_zero ]
in
let chunk_links = Chunk.create () in
let chunk_predicates = Filters.generate_sql ~conf filter chunk_links in
let expected_predicates = Chunk.create () in
(* The predicates can be executed in reverse order, but it’s not an issue
because they all are applied at the same time in the projection *)
Chunk.add_string expected_predicates
" WHERE (COALESCE('source'.'col_1','')=?)\nAND (1)";
Alcotest.(check @@ pair Test_migration.chunk Test_migration.chunk)
"Combined predicate"
(expected_predicates, Chunk.create ())
(chunk_links, chunk_predicates)
(** Create a simple configuration with a group expression and no other filters
*)
let group_filter () =
let filter = ImporterSyntax.CTE.of_filters [ group_expression ] in
let chunk_links = Chunk.create () in
let chunk_predicates = Filters.generate_sql ~conf filter chunk_links in
let expected_predicates = Chunk.create () in
Chunk.add_string expected_predicates
"\n\
INNER JOIN 'filter0' ON filter0.id = source.id\n\
WHERE filter0.group_function";
let expected_links = Chunk.create () in
Chunk.add_string expected_links
"WITH filter0 AS (SELECT source.id, LAST_VALUE('source'.'col_1') OVER \
(PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN \
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)=1 AS group_function\n\
FROM 'source' AS 'source')\n";
Alcotest.(check @@ pair Test_migration.chunk Test_migration.chunk)
"Predicate with a group function"
(expected_predicates, expected_links)
(chunk_links, chunk_predicates)
let expression_with_group () =
let filter =
ImporterSyntax.CTE.of_filters
Expression_builder.[ equal integer_one path'; group_expression ]
in
let chunk_links = Chunk.create () in
let chunk_predicates = Filters.generate_sql ~conf filter chunk_links in
let expected_predicates = Chunk.create () in
Chunk.add_string expected_predicates
"\n\
INNER JOIN 'filter0' ON filter0.id = source.id\n\
WHERE filter0.group_function";
let expected_links = Chunk.create () in
Chunk.add_string expected_links
"WITH filter0 AS (SELECT source.id, LAST_VALUE('source'.'col_1') OVER \
(PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN \
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)=1 AS group_function\n\
FROM 'source' AS 'source' WHERE (1=COALESCE('source'.'col_1',0)))\n";
Alcotest.(check @@ pair Test_migration.chunk Test_migration.chunk)
"The predicate expression is inside of the CTE"
(expected_predicates, expected_links)
(chunk_links, chunk_predicates)
(** The last filter is given after the group. The predicate shall not be
included inside the CTE but in the main query. *)
let group_with_expression () =
let filter =
ImporterSyntax.CTE.of_filters
Expression_builder.[ group_expression; equal integer_one path' ]
in
let chunk_links = Chunk.create () in
let chunk_predicates = Filters.generate_sql ~conf filter chunk_links in
let expected_predicates = Chunk.create () in
Chunk.add_string expected_predicates
"\n\
INNER JOIN 'filter0' ON filter0.id = source.id\n\
WHERE (1=COALESCE('source'.'col_1',0)) AND filter0.group_function";
let expected_links = Chunk.create () in
Chunk.add_string expected_links
"WITH filter0 AS (SELECT source.id, LAST_VALUE('source'.'col_1') OVER \
(PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN \
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)=1 AS group_function\n\
FROM 'source' AS 'source')\n";
Alcotest.(check @@ pair Test_migration.chunk Test_migration.chunk)
"The predicate expression is out of the CTE"
(expected_predicates, expected_links)
(chunk_links, chunk_predicates)
(** Test the configuration with two group, each one generating it’s own CTE in
the query *)
let group_with_group () =
let filter =
ImporterSyntax.CTE.of_filters [ group_expression; group_expression ]
in
let chunk_links = Chunk.create () in
let chunk_predicates = Filters.generate_sql ~conf filter chunk_links in
let expected_predicates = Chunk.create () in
Chunk.add_string expected_predicates
"\n\
INNER JOIN 'filter1' ON filter1.id = source.id\n\
WHERE filter1.group_function";
let expected_links = Chunk.create () in
Chunk.add_string expected_links
"WITH filter0 AS (SELECT source.id, LAST_VALUE('source'.'col_1') OVER \
(PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN \
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)=1 AS group_function\n\
FROM 'source' AS 'source')\n\
, filter1 AS (SELECT source.id, LAST_VALUE('source'.'col_1') OVER \
(PARTITION BY 'source'.'col_1' ORDER BY 'source'.'col_1' RANGE BETWEEN \
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)=1 AS group_function\n\
FROM 'source' AS 'source'\n\
INNER JOIN 'filter0' ON filter0.id = source.id\n\
WHERE filter0.group_function)\n";
Alcotest.(check @@ pair Test_migration.chunk Test_migration.chunk)
"The two CTE shall be generated"
(expected_predicates, expected_links)
(chunk_links, chunk_predicates)
let tests =
Alcotest.
( "filters",
[
test_case "No filters" `Quick empty_predicates;
test_case "Simple filter" `Quick simple_filter;
test_case "Combined filter" `Quick multiple_filters;
test_case "Group filter" `Quick group_filter;
test_case "Expression and Group filter" `Quick expression_with_group;
test_case "Group filter and Expression" `Quick group_with_expression;
test_case "Group and Group" `Quick group_with_group;
] )
|
