1 /*
2 * Copyright (C) 2009 - 2012 Vivien Malerba <malerba@gnome-db.org>
3 * Copyright (C) 2010 - 2011 Murray Cumming <murrayc@murrayc.com>
4 *
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the Free Software
17 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 */
19
20 #include <string.h>
21 #include <libgda/libgda.h>
22
23 typedef GdaSqlStatement *(*BuilderFunc) (void);
24
25 typedef struct {
26 gchar *name;
27 BuilderFunc build_func;
28 gchar *expected_stmt;
29 } ATest;
30
31 static GdaSqlStatement *build0 (void);
32 static GdaSqlStatement *build1 (void);
33 static GdaSqlStatement *build2 (void);
34 static GdaSqlStatement *build3 (void);
35 static GdaSqlStatement *build4 (void);
36 static GdaSqlStatement *build5 (void);
37 static GdaSqlStatement *build6 (void);
38 static GdaSqlStatement *build7 (void);
39 static GdaSqlStatement *build8 (void);
40 static GdaSqlStatement *build9 (void);
41 static GdaSqlStatement *build10 (void);
42 static GdaSqlStatement *build11 (void);
43 static GdaSqlStatement *build12 (void);
44 static GdaSqlStatement *build13 (void);
45
46 static gboolean builder_test_target_id (void);
47
48 ATest tests[] = {
49 {"build0", build0, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"*\",\"sqlident\":\"TRUE\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\",\"sqlident\":\"TRUE\"},\"table_name\":\"mytable\"}]}}}"},
50 {"build1", build1, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"contents\",\"sqlident\":\"TRUE\"}},{\"expr\":{\"value\":\"descr\",\"sqlident\":\"TRUE\"}},{\"expr\":{\"value\":\"rank\",\"sqlident\":\"TRUE\"}},{\"expr\":{\"value\":\"name\",\"sqlident\":\"TRUE\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\",\"sqlident\":\"TRUE\"},\"table_name\":\"mytable\"}]},\"where\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"session\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"session\",\"descr\":null,\"type\":\"string\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"type\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":\"'TABLE'\"}}},\"operand1\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"name\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":\"'alf'\"}}}}}}}}}"},
51 {"build2", build2, "{\"sql\":null,\"stmt_type\":\"INSERT\",\"contents\":{\"table\":\"mytable\",\"fields\":[\"session\",\"type\",\"name\",\"contents\",\"descr\"],\"values\":[[{\"value\":null,\"param_spec\":{\"name\":\"session\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}},{\"value\":null,\"param_spec\":{\"name\":\"type\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}},{\"value\":null,\"param_spec\":{\"name\":\"name\",\"descr\":null,\"type\":\"string\",\"is_param\":true,\"nullok\":true}},{\"value\":null,\"param_spec\":{\"name\":\"contents\",\"descr\":null,\"type\":\"string\",\"is_param\":true,\"nullok\":false}},{\"value\":null,\"param_spec\":{\"name\":\"descr\",\"descr\":null,\"type\":\"string\",\"is_param\":true,\"nullok\":true}}]]}}"},
52 {"build3", build3, "{\"sql\":null,\"stmt_type\":\"UPDATE\",\"contents\":{\"table\":\"mytable\",\"fields\":[\"name\",\"contents\",\"descr\"],\"expressions\":[{\"value\":null,\"param_spec\":{\"name\":\"name\",\"descr\":null,\"type\":\"string\",\"is_param\":true,\"nullok\":true}},{\"value\":null,\"param_spec\":{\"name\":\"contents\",\"descr\":null,\"type\":\"string\",\"is_param\":true,\"nullok\":false}},{\"value\":null,\"param_spec\":{\"name\":\"descr\",\"descr\":null,\"type\":\"string\",\"is_param\":true,\"nullok\":true}}],\"condition\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"id\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"id\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}"},
53 {"build4", build4, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"fav.*\",\"sqlident\":\"TRUE\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\",\"sqlident\":\"TRUE\"},\"table_name\":\"mytable\",\"as\":\"fav\"},{\"expr\":{\"value\":\"fav_orders\",\"sqlident\":\"TRUE\"},\"table_name\":\"fav_orders\",\"as\":\"o\"}],\"joins\":[{\"join_type\":\"LEFT\",\"join_pos\":\"1\",\"on_cond\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"fav.id\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":\"o.fav_id\",\"sqlident\":\"TRUE\"}}}}]},\"where\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"fav.sesion\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"session\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"o.order_key\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"okey\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}},\"order_by\":[{\"expr\":{\"value\":\"o.rank\",\"sqlident\":\"TRUE\"},\"sort\":\"ASC\"}]}}"},
54 {"build5", build5, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"id\",\"sqlident\":\"TRUE\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\",\"sqlident\":\"TRUE\"},\"table_name\":\"mytable\"}]},\"where\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"sesion\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"session\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"contents\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"contents\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}}}"},
55 {"build6", build6, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"fav_id\",\"sqlident\":\"TRUE\"}},{\"expr\":{\"value\":\"rank\",\"sqlident\":\"TRUE\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\",\"sqlident\":\"TRUE\"},\"table_name\":\"mytable\"}]},\"where\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"order_key\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"orderkey\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"!=\",\"operand0\":{\"value\":\"fav_id\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"id\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}}}"},
56 {"build7", build7, "{\"sql\":null,\"stmt_type\":\"UPDATE\",\"contents\":{\"table\":\"mytable\",\"fields\":[\"rank\"],\"expressions\":[{\"value\":null,\"param_spec\":{\"name\":\"newrank\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}],\"condition\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"fav_id\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"id\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"order_key\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"orderkey\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand2\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"rank\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"rank\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}}}"},
57 {"build8", build8, "{\"sql\":null,\"stmt_type\":\"DELETE\",\"contents\":{\"table\":\"mytable\",\"condition\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"id\",\"sqlident\":\"TRUE\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"id\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}"},
58 {"build9", build9, "{\"sql\":null,\"stmt_type\":\"INSERT\",\"contents\":{\"table\":\"mytable\",\"fields\":[\"session\",\"name\"],\"values\":[[{\"value\":\"NULL\"},{\"value\":\"NULL\"}]]}}"},
59 {"build10", build10, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"true\",\"fields\":[{\"expr\":{\"value\":\"fav_id\",\"sqlident\":\"TRUE\"}},{\"expr\":{\"value\":\"rank\",\"sqlident\":\"TRUE\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\",\"sqlident\":\"TRUE\"},\"table_name\":\"mytable\"}]},\"limit\":{\"value\":\"5\"}}}"},
60 {"build11", build11, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"true\",\"distinct_on\":{\"value\":\"rank\",\"sqlident\":\"TRUE\"},\"fields\":[{\"expr\":{\"value\":\"fav_id\",\"sqlident\":\"TRUE\"}},{\"expr\":{\"value\":\"rank\",\"sqlident\":\"TRUE\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\",\"sqlident\":\"TRUE\"},\"table_name\":\"mytable\"}]},\"limit\":{\"value\":\"5\"},\"offset\":{\"value\":\"2\"}}}"},
61 {"build12", build12, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"store_name\",\"sqlident\":\"TRUE\"}},{\"expr\":{\"func\":{\"function_name\":\"sum\",\"function_args\":[{\"value\":\"sales\",\"sqlident\":\"TRUE\"}]}}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"stores\",\"sqlident\":\"TRUE\"},\"table_name\":\"stores\"}]},\"group_by\":[{\"value\":\"store_name\",\"sqlident\":\"TRUE\"}],\"having\":{\"operation\":{\"operator\":\">\",\"operand0\":{\"func\":{\"function_name\":\"sum\",\"function_args\":[{\"value\":\"sales\",\"sqlident\":\"TRUE\"}]}},\"operand1\":{\"value\":\"10\"}}}}}"},
62 {"build13", build13, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"'A''string'\"}},{\"expr\":{\"value\":\"234\"}},{\"expr\":{\"value\":\"TRUE\"}},{\"expr\":{\"value\":\"123.456789\"}},{\"expr\":{\"value\":\"1972-05-27\"}},{\"expr\":{\"value\":\"abc'de\\\\\\\\fgh\"}}]}}"}
63 };
64
65 int
main(int argc,char ** argv)66 main (int argc, char** argv)
67 {
68 gda_init ();
69 guint i, nfailed = 0;
70 for (i = 0; i < G_N_ELEMENTS (tests); i++) {
71 ATest *test = &(tests [i]);
72 GdaSqlStatement *stmt;
73 gboolean fail = FALSE;
74
75
76 g_print ("TEST is: %s\n", test->name);
77
78 stmt = test->build_func ();
79 if (!stmt) {
80 g_print ("Builder function did not create GdaSqlStatement\n");
81 fail = TRUE;
82 }
83 else {
84 gchar *result;
85 result = gda_sql_statement_serialize (stmt);
86 if (!result) {
87 g_print ("Could not serialize GdaSqlStatement\n");
88 fail = TRUE;
89 }
90 else {
91 if (strcmp (result, test->expected_stmt)) {
92 g_print ("Failed:\n\tEXP: %s\n\tGOT: %s\n",
93 test->expected_stmt, result);
94 fail = TRUE;
95 }
96 g_free (result);
97 }
98 GdaStatement *rstmt;
99 rstmt = g_object_new (GDA_TYPE_STATEMENT, "structure", stmt, NULL);
100 gchar *sql;
101 GError *lerror = NULL;
102 sql = gda_statement_to_sql (rstmt, NULL, &lerror);
103 if (sql) {
104 g_print ("==>%s\n", sql);
105 g_free (sql);
106 }
107 else {
108 g_print ("Can't get SQL: %s\n", lerror && lerror->message ?
109 lerror->message : "No detail");
110 if (lerror)
111 g_error_free (lerror);
112 }
113 g_object_unref (rstmt);
114 gda_sql_statement_free (stmt);
115 }
116
117 if (fail)
118 nfailed++;
119 }
120
121 if (! builder_test_target_id ())
122 nfailed++;
123
124 g_print ("%d tests executed, ", i);
125 if (nfailed > 0)
126 g_print ("%d failed\n", nfailed);
127 else
128 g_print ("Ok\n");
129 return EXIT_SUCCESS;
130 }
131
132 /*
133 * SELECT * FROM mytable
134 */
135 static GdaSqlStatement *
build0(void)136 build0 (void)
137 {
138 GdaSqlBuilder *builder;
139 GdaSqlStatement *stmt;
140
141 builder = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
142 gda_sql_builder_add_field_value_id (builder,
143 gda_sql_builder_add_id (builder, "*"), 0);
144 gda_sql_builder_select_add_target_id (builder,
145 gda_sql_builder_add_id (builder, "mytable"), NULL);
146 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (builder));
147 g_object_unref (builder);
148 return stmt;
149 }
150
151 /*
152 * SELECT contents, descr, rank, name FROM mytable WHERE (session = ##session::string) AND ((type = 'TABLE') AND (name = 'alf'))
153 */
154 static GdaSqlStatement *
build1(void)155 build1 (void)
156 {
157 GdaSqlBuilder *builder;
158 GdaSqlBuilderId op_ids [3];
159 gint index = 0;
160 GdaSqlStatement *stmt;
161
162 memset (op_ids, 0, sizeof (GdaSqlBuilderId) * 3);
163
164 builder = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
165 gda_sql_builder_select_add_target_id (builder,
166 gda_sql_builder_add_id (builder, "mytable"), NULL);
167 gda_sql_builder_add_field_value_id (builder,
168 gda_sql_builder_add_id (builder, "contents"), 0);
169 gda_sql_builder_add_field_value_id (builder,
170 gda_sql_builder_add_id (builder, "descr"), 0);
171 gda_sql_builder_add_field_value_id (builder,
172 gda_sql_builder_add_id (builder, "rank"), 0);
173 gda_sql_builder_add_field_value_id (builder,
174 gda_sql_builder_add_id (builder, "name"), 0);
175 GdaSqlBuilderId id_cond1 = gda_sql_builder_add_cond (builder, GDA_SQL_OPERATOR_TYPE_EQ,
176 gda_sql_builder_add_id (builder, "session"),
177 gda_sql_builder_add_param (builder, "session", G_TYPE_STRING, FALSE), 0);
178
179 op_ids [index] = gda_sql_builder_add_cond (builder, GDA_SQL_OPERATOR_TYPE_EQ,
180 gda_sql_builder_add_id (builder, "type"),
181 gda_sql_builder_add_expr (builder, NULL, G_TYPE_STRING, "TABLE"), 0);
182 index++;
183
184 op_ids [index] = gda_sql_builder_add_cond (builder, GDA_SQL_OPERATOR_TYPE_EQ,
185 gda_sql_builder_add_id (builder, "name"),
186 gda_sql_builder_add_expr (builder, NULL, G_TYPE_STRING, "alf"), 0);
187 index++;
188
189 GdaSqlBuilderId id_cond2 = gda_sql_builder_add_cond_v (builder, GDA_SQL_OPERATOR_TYPE_AND, op_ids, index);
190
191 gda_sql_builder_set_where (builder, gda_sql_builder_add_cond (builder, GDA_SQL_OPERATOR_TYPE_AND, id_cond1, id_cond2, 0));
192
193 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (builder));
194 g_object_unref (builder);
195 return stmt;
196 }
197
198 /*
199 * INSERT INTO mytable (session, type, name, contents, descr) VALUES (##session::int, ##type::int,
200 * ##name::string::null, ##contents::string, ##descr::string::null)
201 */
202 static GdaSqlStatement *
build2(void)203 build2 (void)
204 {
205 GdaSqlBuilder *builder;
206 GdaSqlStatement *stmt;
207
208 builder = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
209 gda_sql_builder_set_table (builder, "mytable");
210
211 gda_sql_builder_add_field_value_id (builder,
212 gda_sql_builder_add_id (builder, "session"),
213 gda_sql_builder_add_param (builder, "session", G_TYPE_INT, FALSE));
214 gda_sql_builder_add_field_value_id (builder,
215 gda_sql_builder_add_id (builder, "type"),
216 gda_sql_builder_add_param (builder, "type", G_TYPE_INT, FALSE));
217 gda_sql_builder_add_field_value_id (builder,
218 gda_sql_builder_add_id (builder, "name"),
219 gda_sql_builder_add_param (builder, "name", G_TYPE_STRING, TRUE));
220 gda_sql_builder_add_field_value_id (builder,
221 gda_sql_builder_add_id (builder, "contents"),
222 gda_sql_builder_add_param (builder, "contents", G_TYPE_STRING, FALSE));
223 gda_sql_builder_add_field_value_id (builder,
224 gda_sql_builder_add_id (builder, "descr"),
225 gda_sql_builder_add_param (builder, "descr", G_TYPE_STRING, TRUE));
226
227 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (builder));
228 g_object_unref (builder);
229 return stmt;
230 }
231
232 /*
233 * UPDATE mytable set name = ##name::string::null, contents = ##contents::string, descr = ##descr::string::null
234 * WHERE id = ##id::int
235 */
236 static GdaSqlStatement *
build3(void)237 build3 (void)
238 {
239 GdaSqlBuilder *builder;
240 GdaSqlStatement *stmt;
241
242 builder = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
243 gda_sql_builder_set_table (builder, "mytable");
244
245 gda_sql_builder_add_field_value_id (builder,
246 gda_sql_builder_add_id (builder, "name"),
247 gda_sql_builder_add_param (builder, "name", G_TYPE_STRING, TRUE));
248 gda_sql_builder_add_field_value_id (builder,
249 gda_sql_builder_add_id (builder, "contents"),
250 gda_sql_builder_add_param (builder, "contents", G_TYPE_STRING, FALSE));
251 gda_sql_builder_add_field_value_id (builder,
252 gda_sql_builder_add_id (builder, "descr"),
253 gda_sql_builder_add_param (builder, "descr", G_TYPE_STRING, TRUE));
254
255 gda_sql_builder_set_where (builder,
256 gda_sql_builder_add_cond (builder, GDA_SQL_OPERATOR_TYPE_EQ,
257 gda_sql_builder_add_id (builder, "id"),
258 gda_sql_builder_add_param (builder, "id", G_TYPE_INT, FALSE),
259 0));
260
261 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (builder));
262 g_object_unref (builder);
263 return stmt;
264 }
265
266 /*
267 * SELECT fav.* FROM mytable fav LEFT JOIN fav_orders o ON (fav.id=o.fav_id)
268 * WHERE fav.session=##session::int AND o.order_key=##okey::int
269 * ORDER BY o.rank
270 */
271 static GdaSqlStatement *
build4(void)272 build4 (void)
273 {
274 GdaSqlBuilder *b;
275 GdaSqlStatement *stmt;
276 GdaSqlBuilderId t1, t2;
277
278 b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
279 gda_sql_builder_add_field_value_id (b,
280 gda_sql_builder_add_id (b, "fav.*"), 0);
281 t1 = gda_sql_builder_select_add_target_id (b,
282 gda_sql_builder_add_id (b, "mytable"),
283 "fav");
284 t2 = gda_sql_builder_select_add_target_id (b,
285 gda_sql_builder_add_id (b, "fav_orders"),
286 "o");
287 gda_sql_builder_select_join_targets (b, t1, t2, GDA_SQL_SELECT_JOIN_LEFT,
288 gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
289 gda_sql_builder_add_id (b, "fav.id"),
290 gda_sql_builder_add_id (b, "o.fav_id"),
291 0));
292
293 gda_sql_builder_set_where (b,
294 gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_AND,
295 gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
296 gda_sql_builder_add_id (b, "fav.sesion"),
297 gda_sql_builder_add_param (b, "session", G_TYPE_INT, FALSE), 0),
298 gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
299 gda_sql_builder_add_id (b, "o.order_key"),
300 gda_sql_builder_add_param (b, "okey", G_TYPE_INT, FALSE), 0), 0));
301
302 gda_sql_builder_select_order_by (b,
303 gda_sql_builder_add_id (b, "o.rank"), TRUE, NULL);
304 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
305 g_object_unref (b);
306 return stmt;
307 }
308
309 /*
310 * SELECT id FROM mytable
311 * WHERE session=##session::int AND contents=##contents::string
312 */
313 static GdaSqlStatement *
build5(void)314 build5 (void)
315 {
316 GdaSqlBuilder *b;
317 GdaSqlStatement *stmt;
318
319 b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
320 gda_sql_builder_add_field_value_id (b,
321 gda_sql_builder_add_id (b, "id"), 0);
322 gda_sql_builder_select_add_target_id (b,
323 gda_sql_builder_add_id (b, "mytable"),
324 NULL);
325
326 gda_sql_builder_set_where (b,
327 gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_AND,
328 gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
329 gda_sql_builder_add_id (b, "sesion"),
330 gda_sql_builder_add_param (b, "session", G_TYPE_INT, FALSE), 0),
331 gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
332 gda_sql_builder_add_id (b, "contents"),
333 gda_sql_builder_add_param (b, "contents", G_TYPE_INT, FALSE), 0), 0));
334
335 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
336 g_object_unref (b);
337 return stmt;
338 }
339
340 /*
341 * SELECT fav_id, rank FROM mytable
342 * WHERE order_key=##orderkey::int AND fav_id!=##id::int
343 */
344 static GdaSqlStatement *
build6(void)345 build6 (void)
346 {
347 GdaSqlBuilder *b;
348 GdaSqlStatement *stmt;
349
350 b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
351 gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "fav_id"), 0);
352 gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "rank"), 0);
353
354 gda_sql_builder_select_add_target_id (b,
355 gda_sql_builder_add_id (b, "mytable"),
356 NULL);
357 GdaSqlBuilderId id_cond1 = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
358 gda_sql_builder_add_id (b, "order_key"),
359 gda_sql_builder_add_param (b, "orderkey", G_TYPE_INT, FALSE), 0);
360 GdaSqlBuilderId id_cond2 = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_DIFF,
361 gda_sql_builder_add_id (b, "fav_id"),
362 gda_sql_builder_add_param (b, "id", G_TYPE_INT, FALSE), 0);
363
364 gda_sql_builder_set_where (b, gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_AND, id_cond1, id_cond2, 0));
365
366 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
367 g_object_unref (b);
368 return stmt;
369 }
370
371 /*
372 * UPDATE mytable SET rank=##newrank WHERE fav_id = ##id::int AND order_key=##orderkey::int AND rank = ##rank::int
373 */
374 static GdaSqlStatement *
build7(void)375 build7 (void)
376 {
377 GdaSqlBuilder *b;
378 GdaSqlStatement *stmt;
379
380 b = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
381 gda_sql_builder_set_table (b, "mytable");
382 gda_sql_builder_add_field_value_id (b,
383 gda_sql_builder_add_id (b, "rank"),
384 gda_sql_builder_add_param (b, "newrank", G_TYPE_INT, FALSE));
385 GdaSqlBuilderId id_cond1 = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
386 gda_sql_builder_add_id (b, "fav_id"),
387 gda_sql_builder_add_param (b, "id", G_TYPE_INT, FALSE),
388 0);
389 GdaSqlBuilderId id_cond2 = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
390 gda_sql_builder_add_id (b, "order_key"),
391 gda_sql_builder_add_param (b, "orderkey", G_TYPE_INT, FALSE),
392 0);
393 GdaSqlBuilderId id_cond3 = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
394 gda_sql_builder_add_id (b, "rank"),
395 gda_sql_builder_add_param (b, "rank", G_TYPE_INT, FALSE),
396 0);
397 gda_sql_builder_set_where (b, gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_AND, id_cond1, id_cond2, id_cond3));
398
399 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
400 g_object_unref (b);
401 return stmt;
402 }
403
404 /*
405 * DELETE FROM mytable SET WHERE fav_id = ##id::int
406 */
407 static GdaSqlStatement *
build8(void)408 build8 (void)
409 {
410 GdaSqlBuilder *b;
411 GdaSqlStatement *stmt;
412
413 b = gda_sql_builder_new (GDA_SQL_STATEMENT_DELETE);
414 gda_sql_builder_set_table (b, "mytable");
415
416 gda_sql_builder_set_where (b, gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
417 gda_sql_builder_add_id (b, "id"),
418 gda_sql_builder_add_param (b, "id", G_TYPE_INT, FALSE),
419 0));
420 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
421 g_object_unref (b);
422 return stmt;
423 }
424
425 /*
426 * INSERT INTO mytable (session, name) VALUES (NULL, NULL);
427 */
428 static GdaSqlStatement *
build9(void)429 build9 (void)
430 {
431 GdaSqlBuilder *builder;
432 GdaSqlStatement *stmt;
433 GValue *value;
434
435 builder = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
436 gda_sql_builder_set_table (builder, "mytable");
437
438 gda_sql_builder_add_field_value_as_gvalue (builder, "session", NULL);
439 value = gda_value_new_null ();
440 gda_sql_builder_add_field_value_as_gvalue (builder, "name", value);
441 gda_value_free (value);
442
443 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (builder));
444 g_object_unref (builder);
445
446 return stmt;
447 }
448
449 /*
450 * SELECT DISTINCT fav_id, rank FROM mytable LIMIT 5
451 */
452 static GdaSqlStatement *
build10(void)453 build10 (void)
454 {
455 GdaSqlBuilder *b;
456 GdaSqlStatement *stmt;
457
458 b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
459 gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "fav_id"), 0);
460 gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "rank"), 0);
461
462 gda_sql_builder_select_add_target_id (b,
463 gda_sql_builder_add_id (b, "mytable"),
464 NULL);
465 gda_sql_builder_select_set_distinct (b, TRUE, 0);
466 gda_sql_builder_select_set_limit (b, gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 5), 0);
467
468 {
469 GdaStatement *st;
470 st = gda_sql_builder_get_statement (b, FALSE);
471 g_print ("[%s]\n", gda_statement_to_sql (st, NULL, NULL));
472 g_object_unref (st);
473 }
474
475 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
476 g_object_unref (b);
477 return stmt;
478 }
479
480 /*
481 * SELECT DISTINCT ON (rank) fav_id, rank FROM mytable LIMIT 5 OFFSET 2
482 */
483 static GdaSqlStatement *
build11(void)484 build11 (void)
485 {
486 GdaSqlBuilder *b;
487 GdaSqlStatement *stmt;
488
489 b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
490 gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "fav_id"), 0);
491 gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "rank"), 0);
492
493 gda_sql_builder_select_add_target_id (b,
494 gda_sql_builder_add_id (b, "mytable"),
495 NULL);
496 gda_sql_builder_select_set_distinct (b, TRUE,
497 gda_sql_builder_add_id (b, "rank"));
498 gda_sql_builder_select_set_limit (b, gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 5),
499 gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 2));
500
501 #ifdef DEBUG_NO
502 {
503 GdaStatement *st;
504 st = gda_sql_builder_get_statement (b, FALSE);
505 g_print ("[%s]\n", gda_statement_to_sql (st, NULL, NULL));
506 g_object_unref (st);
507 }
508 #endif
509
510 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
511 g_object_unref (b);
512 return stmt;
513 }
514
515 /*
516 * SELECT store_name, sum (sales) FROM stores GROUP BY store_name HAVING sum (sales) > 10
517 */
518 static GdaSqlStatement *
build12(void)519 build12 (void)
520 {
521 GdaSqlBuilder *b;
522 GdaSqlStatement *stmt;
523
524 b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
525 gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "store_name"), 0);
526 const GdaSqlBuilderId id_func = gda_sql_builder_add_function (b, "sum",
527 gda_sql_builder_add_id (b, "sales"), 0);
528 gda_sql_builder_add_field_value_id (b, id_func, 0);
529
530 gda_sql_builder_select_add_target_id (b,
531 gda_sql_builder_add_id (b, "stores"),
532 NULL);
533 gda_sql_builder_select_group_by (b, gda_sql_builder_add_id (b, "store_name"));
534 gda_sql_builder_select_set_having (b,
535 gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_GT,
536 id_func,
537 gda_sql_builder_add_expr (b, NULL,
538 G_TYPE_INT, 10),
539 0));
540
541 #ifdef DEBUG
542 {
543 GdaStatement *st;
544 st = gda_sql_builder_get_statement (b, FALSE);
545 g_print ("[%s]\n", gda_statement_to_sql (st, NULL, NULL));
546 g_object_unref (st);
547 }
548 #endif
549
550 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
551 g_object_unref (b);
552 return stmt;
553 }
554
555 /*
556 *
557 */
558 static gboolean
builder_test_target_id(void)559 builder_test_target_id (void)
560 {
561 GdaSqlBuilder *builder;
562 GdaSqlBuilderId id1, id2;
563 gboolean allok = TRUE;
564
565 builder = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
566 gda_sql_builder_add_field_value_id (builder,
567 gda_sql_builder_add_id (builder, "*"), 0);
568 /* same target with aliases */
569 id1 = gda_sql_builder_select_add_target_id (builder,
570 gda_sql_builder_add_id (builder, "mytable"), "alias");
571 id2 = gda_sql_builder_select_add_target_id (builder,
572 gda_sql_builder_add_id (builder, "mytable"), "alias");
573 if (id1 != id2) {
574 g_print ("identical targets with an alias not recognized as same target.\n");
575 allok = FALSE;
576 }
577
578 id2 = gda_sql_builder_select_add_target_id (builder,
579 gda_sql_builder_add_id (builder, "mytable"), "alias2");
580 if (id1 == id2) {
581 g_print ("identical tables with different alias recognized as same target.\n");
582 allok = FALSE;
583 }
584
585 id2 = gda_sql_builder_select_add_target_id (builder,
586 gda_sql_builder_add_id (builder, "mytable"), NULL);
587 if (id1 == id2) {
588 g_print ("identical tables with no alias recognized as same target.\n");
589 allok = FALSE;
590 }
591
592 id1 = gda_sql_builder_select_add_target_id (builder,
593 gda_sql_builder_add_id (builder, "mytable"), NULL);
594 if (id1 != id2) {
595 g_print ("identical tables with different alias not recognized as same target.\n");
596 allok = FALSE;
597 }
598
599 g_object_unref (builder);
600
601 return allok;
602 }
603
604 /*
605 * SELECT store_name, sum (sales) FROM stores GROUP BY store_name HAVING sum (sales) > 10
606 */
607 static GdaSqlStatement *
build13(void)608 build13 (void)
609 {
610 GdaSqlBuilder *b;
611 GdaSqlStatement *stmt;
612
613 b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
614 gda_sql_builder_add_field_value_id (b,
615 gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "A'string"), 0);
616 gda_sql_builder_add_field_value_id (b,
617 gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 234), 0);
618 gda_sql_builder_add_field_value_id (b,
619 gda_sql_builder_add_expr (b, NULL, G_TYPE_BOOLEAN, TRUE), 0);
620 GdaNumeric *numval;
621 numval = gda_numeric_new ();
622 gda_numeric_set_from_string (numval, "123.456789");
623 gda_sql_builder_add_field_value_id (b,
624 gda_sql_builder_add_expr (b, NULL, GDA_TYPE_NUMERIC, numval), 0);
625 gda_numeric_free (numval);
626
627 GDate *date = g_date_new_dmy (27, G_DATE_MAY, 1972);
628 gda_sql_builder_add_field_value_id (b,
629 gda_sql_builder_add_expr (b, NULL, G_TYPE_DATE, date), 0);
630 g_date_free (date);
631
632 GdaBinary bin = {"abc'de\\fghijklm", 10};
633 gda_sql_builder_add_field_value_id (b,
634 gda_sql_builder_add_expr (b, NULL, GDA_TYPE_BINARY, &bin), 0);
635
636
637 #ifdef DEBUG
638 {
639 GdaStatement *st;
640 st = gda_sql_builder_get_statement (b, FALSE);
641 g_print ("[%s]\n", gda_statement_to_sql (st, NULL, NULL));
642 g_object_unref (st);
643 }
644 #endif
645
646 stmt = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
647 g_object_unref (b);
648 return stmt;
649 }
650