1 package de.dlr.shepard.data.spatialdata.repositories;
2
3 import static org.junit.jupiter.api.Assertions.assertEquals;
4
5 import de.dlr.shepard.data.spatialdata.io.FilterCondition;
6 import de.dlr.shepard.data.spatialdata.io.Operator;
7 import java.util.Collections;
8 import java.util.HashMap;
9 import java.util.List;
10 import org.junit.jupiter.api.Test;
11
12 public class NativeQueryStringBuilderTest {
13
14 private final String[] ALL_COLUMNS_STRING = new String[] { "*" };
15
16 @Test
17 public void build_simpleSelectQuery_success() {
18 var builder = new NativeQueryStringBuilder();
19 var current = builder.select("table_name", ALL_COLUMNS_STRING).build();
20
21 var expected = "SELECT * FROM table_name WHERE 1 = 1;";
22
23 assertEquals(expected, current);
24 }
25
26 @Test
27 public void build_simpleSelectQueryWithColumns_success() {
28 var current = new NativeQueryStringBuilder()
29 .select("table_name", new String[] { "column1", "column2" })
30 .addLimitClause(null)
31 .build();
32
33 var expected = "SELECT column1, column2 FROM table_name WHERE 1 = 1;";
34
35 assertEquals(expected, current);
36 }
37
38 @Test
39 public void build_addOneCondition_success() {
40 var current = new NativeQueryStringBuilder()
41 .select("table_name", ALL_COLUMNS_STRING)
42 .addWhereCondition("id", 1)
43 .build();
44
45 var expected = "SELECT * FROM table_name WHERE 1 = 1 AND id = 1;";
46
47 assertEquals(expected, current);
48 }
49
50 @Test
51 public void build_addTwoConditions_success() {
52 var current = new NativeQueryStringBuilder()
53 .select("table_name", ALL_COLUMNS_STRING)
54 .addWhereCondition("id", 1)
55 .addWhereCondition("role", "assistant")
56 .build();
57
58 var expected = "SELECT * FROM table_name WHERE 1 = 1 AND id = 1 AND role = 'assistant';";
59
60 assertEquals(expected, current);
61 }
62
63 @Test
64 public void build_addTimeCondition_success() {
65 var current = new NativeQueryStringBuilder()
66 .select("table_name", ALL_COLUMNS_STRING)
67 .addWhereCondition("id", 1)
68 .addTimeCondition("time", 123l, 234l)
69 .build();
70
71 var expected = "SELECT * FROM table_name WHERE 1 = 1 AND id = 1 AND time >= 123 AND time <= 234;";
72
73 assertEquals(expected, current);
74 }
75
76 @Test
77 public void build_passOnlyTimestampStart_success() {
78 var current = new NativeQueryStringBuilder()
79 .select("table_name", ALL_COLUMNS_STRING)
80 .addTimeCondition("time", 123l, null)
81 .build();
82
83 var expected = "SELECT * FROM table_name WHERE 1 = 1 AND time >= 123;";
84
85 assertEquals(expected, current);
86 }
87
88 @Test
89 public void build_passOnlyTimestampEnd_success() {
90 var current = new NativeQueryStringBuilder()
91 .select("table_name", ALL_COLUMNS_STRING)
92 .addTimeCondition("time", null, 234l)
93 .build();
94
95 var expected = "SELECT * FROM table_name WHERE 1 = 1 AND time <= 234;";
96
97 assertEquals(expected, current);
98 }
99
100 @Test
101 public void build_addJsonCondition_success() {
102 var jsonFilter = new HashMap<String, Object>();
103 jsonFilter.put("track", 2);
104
105 var current = new NativeQueryStringBuilder()
106 .select("table_name", ALL_COLUMNS_STRING)
107 .addWhereCondition("id", 1)
108 .addJsonContainsCondition("meta", jsonFilter)
109 .build();
110
111 var expected = "SELECT * FROM table_name WHERE 1 = 1 AND id = 1 AND meta @> '{\"track\":2}';";
112
113 assertEquals(expected, current);
114 }
115
116 @Test
117 public void build_passNullValueInCondition_isIgnored() {
118 var current = new NativeQueryStringBuilder()
119 .select("table_name", ALL_COLUMNS_STRING)
120 .addWhereCondition("id", null)
121 .build();
122
123 var expected = "SELECT * FROM table_name WHERE 1 = 1;";
124
125 assertEquals(expected, current);
126 }
127
128 @Test
129 public void build_passNullValueInSecondCondition_isIgnored() {
130 var current = new NativeQueryStringBuilder()
131 .select("table_name", ALL_COLUMNS_STRING)
132 .addWhereCondition("id", 1)
133 .addJsonContainsCondition("meta", Collections.emptyMap())
134 .addTimeCondition("time", null, null)
135 .build();
136
137 var expected = "SELECT * FROM table_name WHERE 1 = 1 AND id = 1;";
138
139 assertEquals(expected, current);
140 }
141
142 @Test
143 public void build_passEmptyLimit_success() {
144 var current = new NativeQueryStringBuilder().select("table_name", ALL_COLUMNS_STRING).addLimitClause(null).build();
145
146 var expected = "SELECT * FROM table_name WHERE 1 = 1;";
147
148 assertEquals(expected, current);
149 }
150
151 @Test
152 public void build_passNonEmptyLimit_success() {
153 var current = new NativeQueryStringBuilder().select("table_name", ALL_COLUMNS_STRING).addLimitClause(3).build();
154
155 var expected = "SELECT * FROM table_name WHERE 1 = 1 LIMIT 3;";
156
157 assertEquals(expected, current);
158 }
159
160 @Test
161 public void build_addBSGeometryCondition_success() {
162 var currentStringBuilder = new NativeQueryStringBuilder()
163 .select("table_name", ALL_COLUMNS_STRING)
164 .addBSGeometryCondition(1.0, 1.0, 1.0, 2.0);
165 var current = currentStringBuilder.build();
166
167 var expected =
168 "SELECT * FROM table_name WHERE 1 = 1 AND ST_3DDWithin(position, ST_MakePoint(:x1, :y1, :z1), :radius);";
169
170 assertEquals(expected, current);
171 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("x1"));
172 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("y1"));
173 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("z1"));
174 assertEquals(2.0, currentStringBuilder.getQueryParameters().get("radius"));
175 }
176
177 @Test
178 public void build_addAABBGeometryCondition_success() {
179 var currentStringBuilder = new NativeQueryStringBuilder()
180 .select("table_name", ALL_COLUMNS_STRING)
181 .addAABBGeometryCondition(1.0, 1.0, 1.0, 2.0, 2.0, 2.0);
182
183 var current = currentStringBuilder.build();
184
185 var expected =
186 "SELECT * FROM table_name WHERE 1 = 1 AND position &&& ST_3DMakeBox(ST_MakePoint(:x1, :y1, :z1), ST_MakePoint(:x2, :y2, :z2));";
187
188 assertEquals(expected, current);
189 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("x1"));
190 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("y1"));
191 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("z1"));
192 assertEquals(2.0, currentStringBuilder.getQueryParameters().get("x2"));
193 assertEquals(2.0, currentStringBuilder.getQueryParameters().get("y2"));
194 assertEquals(2.0, currentStringBuilder.getQueryParameters().get("z2"));
195 }
196
197 @Test
198 public void build_addKNNGeometryCondition_success() {
199 var currentStringBuilder = new NativeQueryStringBuilder()
200 .select("table_name", ALL_COLUMNS_STRING)
201 .addKNNGeometryCondition(1.0, 1.0, 1.0, 5);
202
203 var current = currentStringBuilder.build();
204
205 var expected = "SELECT * FROM table_name WHERE 1 = 1 ORDER BY position <<->> ST_MakePoint(:x1, :y1, :z1) LIMIT :k;";
206
207 assertEquals(expected, current);
208 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("x1"));
209 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("y1"));
210 assertEquals(1.0, currentStringBuilder.getQueryParameters().get("z1"));
211 assertEquals(5, currentStringBuilder.getQueryParameters().get("k"));
212 }
213
214 @Test
215 public void build_addJsonFilterConditions_success() {
216 var current = new NativeQueryStringBuilder()
217 .select("table_name", ALL_COLUMNS_STRING)
218 .addJsonFilterConditions(
219 "measurements",
220 List.of(
221 new FilterCondition("key", Operator.EQUALS, 5),
222 new FilterCondition("key1,subkey1", Operator.LESS_THAN, 20),
223 new FilterCondition("key2,subkey2,subsubkey2", Operator.GREATER_THAN, 10)
224 )
225 )
226 .build();
227
228 var expected =
229 "SELECT * FROM table_name WHERE 1 = 1 AND jsonb_typeof(measurements #> '{key}') = 'number' AND (measurements #>> '{key}')::NUMERIC = 5.0 AND jsonb_typeof(measurements #> '{key1,subkey1}') = 'number' AND (measurements #>> '{key1,subkey1}')::NUMERIC < 20.0 AND jsonb_typeof(measurements #> '{key2,subkey2,subsubkey2}') = 'number' AND (measurements #>> '{key2,subkey2,subsubkey2}')::NUMERIC > 10.0;";
230 assertEquals(expected, current);
231 }
232
233 public void build_passEmptySkip_success() {
234 var current = new NativeQueryStringBuilder().select("table_name", ALL_COLUMNS_STRING).addSkipClause(null).build();
235
236 var expected = "SELECT * FROM table_name;";
237
238 assertEquals(expected, current);
239 }
240
241 @Test
242 public void build_passNonEmptySkip_success() {
243 var current = new NativeQueryStringBuilder().select("table_name", ALL_COLUMNS_STRING).addSkipClause(3).build();
244
245 var expected = "SELECT * FROM table_name WHERE 1 = 1 AND id % 3 = 0;";
246
247 assertEquals(expected, current);
248 }
249
250 @Test
251 public void build_orderIsfixed_success() {
252 var current1 = new NativeQueryStringBuilder()
253 .select("table_name", ALL_COLUMNS_STRING)
254 .addTimeCondition("time", 10L, 20L)
255 .addWhereCondition("cond1", 1)
256 .addSkipClause(3)
257 .addWhereCondition("cond2", 2)
258 .build();
259
260 var current2 = new NativeQueryStringBuilder()
261 .select("table_name", ALL_COLUMNS_STRING)
262 .addSkipClause(3)
263 .addTimeCondition("time", 10L, 20L)
264 .addWhereCondition("cond1", 1)
265 .addWhereCondition("cond2", 2)
266 .build();
267
268 var expected =
269 "SELECT * FROM table_name WHERE 1 = 1 AND cond1 = 1 AND cond2 = 2 AND time >= 10 AND time <= 20 AND id % 3 = 0;";
270
271 assertEquals(expected, current1);
272 assertEquals(expected, current2);
273 }
274 }