GNOME.org
 

[libgda] GdaSqlBuilder improvements

commit 8b9f0a3998719a4631b9eeb6adbab13627735a55
Author: Vivien Malerba <malerba gnome-db org>
Date:   Mon Jul 13 20:48:44 2009 +0200

    GdaSqlBuilder improvements
    
    * new API to support ORDER BY
    * new API to support complex conditions
    * new test

 doc/C/libgda-4.0-sections.txt   |    3 +
 doc/C/tmpl/gda-sql-builder.sgml |   33 +++
 libgda/gda-sql-builder.c        |  103 ++++++++++
 libgda/gda-sql-builder.h        |    5 +-
 tests/.gitignore                |    1 +
 tests/Makefile.am               |   11 +-
 tests/test-sql-builder.c        |  411 +++++++++++++++++++++++++++++++++++++++
 7 files changed, 564 insertions(+), 3 deletions(-)
---
diff --git a/doc/C/libgda-4.0-sections.txt b/doc/C/libgda-4.0-sections.txt
index 92a43fa..5e4a694 100644
--- a/doc/C/libgda-4.0-sections.txt
+++ b/doc/C/libgda-4.0-sections.txt
@@ -1665,10 +1665,13 @@ gda_sql_builder_expr
 gda_sql_builder_param
 <SUBSECTION>
 gda_sql_builder_cond
+gda_sql_builder_cond_v
+gda_sql_builder_set_where
 <SUBSECTION>
 gda_sql_builder_select_add_target
 gda_sql_builder_select_join_targets
 gda_sql_builder_join_add_field
+gda_sql_builder_select_order_by
 <SUBSECTION Standard>
 GDA_SQL_BUILDER
 GDA_SQL_BUILDER_GET_CLASS
diff --git a/doc/C/tmpl/gda-sql-builder.sgml b/doc/C/tmpl/gda-sql-builder.sgml
index ee9bd37..73c6fa4 100644
--- a/doc/C/tmpl/gda-sql-builder.sgml
+++ b/doc/C/tmpl/gda-sql-builder.sgml
@@ -150,6 +150,28 @@ gda_sql_builder_cond2 (b, 5, GDA_SQL_OPERATOR_TYPE_AND, 3, 4); // whole expressi
 @Returns: 
 
 
+<!-- ##### FUNCTION gda_sql_builder_cond_v ##### -->
+<para>
+
+</para>
+
+ builder: 
+ id: 
+ op: 
+ op_ids: 
+ op_ids_size: 
+ Returns: 
+
+
+<!-- ##### FUNCTION gda_sql_builder_set_where ##### -->
+<para>
+
+</para>
+
+ builder: 
+ cond_id: 
+
+
 <!-- ##### FUNCTION gda_sql_builder_select_add_target ##### -->
 <para>
 
@@ -186,3 +208,14 @@ gda_sql_builder_cond2 (b, 5, GDA_SQL_OPERATOR_TYPE_AND, 3, 4); // whole expressi
 @field_name: 
 
 
+<!-- ##### FUNCTION gda_sql_builder_select_order_by ##### -->
+<para>
+
+</para>
+
+ builder: 
+ expr_id: 
+ asc: 
+ collation_name: 
+
+
diff --git a/libgda/gda-sql-builder.c b/libgda/gda-sql-builder.c
index d2a1d90..24f3d5a 100644
--- a/libgda/gda-sql-builder.c
+++ b/libgda/gda-sql-builder.c
@@ -762,6 +762,69 @@ gda_sql_builder_cond (GdaSqlBuilder *builder, guint id, GdaSqlOperatorType op, g
 	return add_part (builder, id, (GdaSqlAnyPart *) expr);
 }
 
+/**
+ * gda_sql_builder_cond_v
+ * @builder: a #GdaSqlBuilder object
+ * @id: the requested ID, or 0 if to be determined by @builder
+ * @op: type of condition
+ * @op_ids: an array of ID for the arguments (not %0)
+ * @ops_ids_size: size of @ops_ids
+ *
+ * Builds a new expression which reprenents a condition (or operation).
+ *
+ * As a side case, if @ops_ids_size is 1,
+ * then @op is ignored, and the returned ID represents @op_ids[0] (this avoids any problem for example
+ * when @op is GDA_SQL_OPERATOR_TYPE_AND and there is in fact only one operand).
+ *
+ * Returns: the ID of the new expression, or 0 if there was an error
+ *
+ * Since: 4.2
+ */
+guint
+gda_sql_builder_cond_v (GdaSqlBuilder *builder, guint id, GdaSqlOperatorType op,
+			guint *op_ids, gint op_ids_size)
+{
+	gint i;
+	SqlPart **parts;
+
+	g_return_val_if_fail (GDA_IS_SQL_BUILDER (builder), 0);
+	g_return_val_if_fail (builder->priv->main_stmt, 0);
+	g_return_val_if_fail (op_ids, 0);
+	g_return_val_if_fail (op_ids_size > 0, 0);
+
+	parts = g_new (SqlPart *, op_ids_size);
+	for (i = 0; i < op_ids_size; i++) {
+		parts [i] = get_part (builder, op_ids [i], GDA_SQL_ANY_EXPR);
+		if (!parts [i]) {
+			g_free (parts);
+			return 0;
+		}
+	}
+
+	if (op_ids_size == 1) {
+		SqlPart *part = parts [0];
+		g_free (parts);
+		if (id)
+			return add_part (builder, id, use_part (part, NULL));
+		else
+			return op_ids [0]; /* return the same ID as none was specified */
+	}
+	
+	GdaSqlExpr *expr;
+	expr = gda_sql_expr_new (NULL);
+	expr->cond = gda_sql_operation_new (GDA_SQL_ANY_PART (expr));
+	expr->cond->operator_type = op;
+	expr->cond->operands = NULL;
+	for (i = 0; i < op_ids_size; i++)
+		expr->cond->operands = g_slist_append (expr->cond->operands,
+						       use_part (parts [i],
+								 GDA_SQL_ANY_PART (expr->cond)));
+	g_free (parts);
+
+	return add_part (builder, id, (GdaSqlAnyPart *) expr);
+}
+
+
 typedef struct {
 	GdaSqlSelectTarget target; /* inheritance! */
 	guint part_id; /* copied from this part ID */
@@ -951,3 +1014,43 @@ gda_sql_builder_join_add_field (GdaSqlBuilder *builder, guint join_id, const gch
 	field->field_name = g_strdup (field_name);
 	join->use = g_slist_append (join->use, field);
 }
+
+/**
+ * gda_sql_builder_select_order_by
+ * @builder: a #GdaSqlBuiler
+ * @expr_id: the ID of the expression to use during sorting (not %0)
+ * @asc: %TRUE for an ascending sorting
+ * @collation_name: name of the collation to use when sorting, or %NULL
+ *
+ * Adds a new ORDER BY expression to a SELECT statement.
+ * 
+ * Since: 4.2
+ */
+void
+gda_sql_builder_select_order_by (GdaSqlBuilder *builder, guint expr_id,
+				 gboolean asc, const gchar *collation_name)
+{
+	SqlPart *part;
+	GdaSqlStatementSelect *sel;
+	GdaSqlSelectOrder *sorder;
+
+	g_return_if_fail (GDA_IS_SQL_BUILDER (builder));
+	g_return_if_fail (expr_id > 0);
+
+	if (builder->priv->main_stmt->stmt_type != GDA_SQL_STATEMENT_SELECT) {
+		g_warning (_("Wrong statement type"));
+		return;
+	}
+
+	part = get_part (builder, expr_id, GDA_SQL_ANY_EXPR);
+	if (!part)
+		return;
+	sel = (GdaSqlStatementSelect*) builder->priv->main_stmt->contents;
+	
+	sorder = gda_sql_select_order_new (GDA_SQL_ANY_PART (sel));
+	sorder->expr = (GdaSqlExpr*) use_part (part, GDA_SQL_ANY_PART (sorder));
+	sorder->asc = asc;
+	if (collation_name)
+		sorder->collation_name = g_strdup (collation_name);
+	sel->order_by = g_slist_append (sel->order_by, sorder);
+}
diff --git a/libgda/gda-sql-builder.h b/libgda/gda-sql-builder.h
index c3cc362..b5c8df1 100644
--- a/libgda/gda-sql-builder.h
+++ b/libgda/gda-sql-builder.h
@@ -76,6 +76,8 @@ guint              gda_sql_builder_param (GdaSqlBuilder *builder, guint id, cons
 
 guint              gda_sql_builder_cond (GdaSqlBuilder *builder, guint id, GdaSqlOperatorType op,
 					 guint op1, guint op2, guint op3);
+guint              gda_sql_builder_cond_v (GdaSqlBuilder *builder, guint id, GdaSqlOperatorType op,
+					   guint *op_ids, gint op_ids_size);
 
 
 /* SELECT Statement API */
@@ -86,7 +88,8 @@ guint              gda_sql_builder_select_join_targets (GdaSqlBuilder *builder,
 							GdaSqlSelectJoinType join_type,
 							guint join_expr);
 void               gda_sql_builder_join_add_field (GdaSqlBuilder *builder, guint join_id, const gchar *field_name);
-
+void               gda_sql_builder_select_order_by (GdaSqlBuilder *builder, guint expr_id,
+						    gboolean asc, const gchar *collation_name);
 
 /* General Statement API */
 void              gda_sql_builder_set_table (GdaSqlBuilder *builder, const gchar *table_name);
diff --git a/tests/.gitignore b/tests/.gitignore
index bdadec1..bbc7462 100644
--- a/tests/.gitignore
+++ b/tests/.gitignore
@@ -2,3 +2,4 @@ test-bin-converter
 test-ddl-creator
 test-sql-identifier
 test-identifiers-quotes
+test-sql-builder
diff --git a/tests/Makefile.am b/tests/Makefile.am
index 30e475a..526f6b2 100644
--- a/tests/Makefile.am
+++ b/tests/Makefile.am
@@ -1,6 +1,6 @@
 noinst_LTLIBRARIES = libgda-test-4.0.la
-TESTS = test-ddl-creator test-bin-converter test-sql-identifier
-check_PROGRAMS = test-ddl-creator test-bin-converter test-sql-identifier test-identifiers-quotes
+TESTS = test-ddl-creator test-bin-converter test-sql-identifier test-identifiers-quotes test-sql-builder
+check_PROGRAMS = test-ddl-creator test-bin-converter test-sql-identifier test-identifiers-quotes test-sql-builder
 
 
 SUBDIRS = providers parser value-holders meta-store data-models multi-threading
@@ -59,4 +59,11 @@ test_identifiers_quotes_LDADD = \
         $(top_builddir)/libgda/libgda-4.0.la \
         $(LIBGDA_LIBS)
 
+test_sql_builder_SOURCES = \
+        test-sql-builder.c
+
+test_sql_builder_LDADD = \
+        $(top_builddir)/libgda/libgda-4.0.la \
+        $(LIBGDA_LIBS)
+
 EXTRA_DIST = dbstruct.xml
\ No newline at end of file
diff --git a/tests/test-sql-builder.c b/tests/test-sql-builder.c
new file mode 100644
index 0000000..3635920
--- /dev/null
+++ b/tests/test-sql-builder.c
@@ -0,0 +1,411 @@
+/*
+ * Copyright (C) 2009 The GNOME Foundation.
+ *
+ * AUTHORS:
+ *      Vivien Malerba <malerba gnome-db org>
+ *
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or
+ * (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+ */
+
+#include <string.h>
+#include <libgda/libgda.h>
+
+typedef GdaSqlStatement *(*BuilderFunc) (void);
+
+typedef struct {
+	gchar *name;
+	BuilderFunc build_func;
+	gchar *expected_stmt;
+} ATest;
+
+static GdaSqlStatement *build0 (void);
+static GdaSqlStatement *build1 (void);
+static GdaSqlStatement *build2 (void);
+static GdaSqlStatement *build3 (void);
+static GdaSqlStatement *build4 (void);
+static GdaSqlStatement *build5 (void);
+static GdaSqlStatement *build6 (void);
+static GdaSqlStatement *build7 (void);
+static GdaSqlStatement *build8 (void);
+
+ATest tests[] = {
+	{"build0", build0, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"*\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\"}}]}}}"},
+	{"build1", build1, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"contents\"}},{\"expr\":{\"value\":\"descr\"}},{\"expr\":{\"value\":\"rank\"}},{\"expr\":{\"value\":\"name\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\"}}]},\"where\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"session\"},\"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\"},\"operand1\":{\"value\":\"'TABLE'\"}}},\"operand1\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"name\"},\"operand1\":{\"value\":\"'alf'\"}}}}}}}}}"},
+	{"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}}]]}}"},
+	{"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\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"id\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}"},
+	{"build4", build4, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"fav.*\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\"},\"as\":\"fav\"},{\"expr\":{\"value\":\"fav_orders\"},\"as\":\"o\"}],\"joins\":[{\"join_type\":\"LEFT\",\"join_pos\":\"1\",\"on_cond\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"fav.id\"},\"operand1\":{\"value\":\"o.fav_id\"}}}}]},\"where\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"fav.sesion\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"session\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"o.order_key\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"okey\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}},\"order_by\":[{\"expr\":{\"value\":\"o.rank\
 "},\"sort\":\"ASC\"}]}}"},
+	{"build5", build5, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"id\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\"}}]},\"where\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"sesion\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"session\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"contents\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"contents\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}}}"},
+	{"build6", build6, "{\"sql\":null,\"stmt_type\":\"SELECT\",\"contents\":{\"distinct\":\"false\",\"fields\":[{\"expr\":{\"value\":\"fav_id\"}},{\"expr\":{\"value\":\"rank\"}}],\"from\":{\"targets\":[{\"expr\":{\"value\":\"mytable\"}}]},\"where\":{\"operation\":{\"operator\":\"AND\",\"operand0\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"order_key\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"orderkey\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"!=\",\"operand0\":{\"value\":\"fav_id\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"id\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}}}"},
+	{"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\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"id\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand1\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"order_key\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"orderkey\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}},\"operand2\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"rank\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"rank\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}}}"},
+	{"build8", build8, "{\"sql\":null,\"stmt_type\":\"DELETE\",\"contents\":{\"table\":\"mytable\",\"condition\":{\"operation\":{\"operator\":\"=\",\"operand0\":{\"value\":\"id\"},\"operand1\":{\"value\":null,\"param_spec\":{\"name\":\"id\",\"descr\":null,\"type\":\"int\",\"is_param\":true,\"nullok\":false}}}}}}"}
+};
+
+int
+main (int argc, char** argv)
+{
+	gda_init ();
+	gint i, nfailed = 0;
+	for (i = 0; i < G_N_ELEMENTS (tests); i++) {
+		ATest *test = &(tests [i]);
+		GdaSqlStatement *stmt;
+		gboolean fail = FALSE;
+
+
+		g_print ("TEST is: %s\n", test->name);
+
+		stmt = test->build_func ();
+		if (!stmt) {
+			g_print ("Builder function did not create GdaSqlStatement\n");
+			fail = TRUE;
+		}
+		else {
+			gchar *result;
+			result = gda_sql_statement_serialize (stmt);
+			if (!result) {
+				g_print ("Could not serialize GdaSqlStatement\n");
+				fail = TRUE;
+			}
+			else {
+				if (strcmp (result, test->expected_stmt)) {
+					g_print ("Failed:\n\tEXP: %s\n\tGOT: %s\n",
+						 test->expected_stmt, result);
+					fail = TRUE;
+				}
+				g_free (result);
+			}
+			GdaStatement *rstmt;
+			rstmt = g_object_new (GDA_TYPE_STATEMENT, "structure", stmt, NULL);
+			gchar *sql;
+			GError *lerror = NULL;
+			sql = gda_statement_to_sql (rstmt, NULL, &lerror);
+			if (sql) {
+				g_print ("==>%s\n", sql);
+				g_free (sql);
+			}
+			else {
+				g_print ("Can't get SQL: %s\n", lerror && lerror->message ?
+					 lerror->message : "No detail");
+				if (lerror)
+					g_error_free (lerror);
+			}
+			g_object_unref (rstmt);
+			gda_sql_statement_free (stmt);
+		}
+
+		if (fail)
+			nfailed++;
+	}
+
+	g_print ("%d tests executed, ", i);
+	if (nfailed > 0)
+		g_print ("%d failed\n", nfailed);
+	else
+		g_print ("Ok\n");
+	return EXIT_SUCCESS;
+}
+
+/*
+ * SELECT * FROM mytable
+ */
+static GdaSqlStatement *
+build0 (void)
+{
+	GdaSqlBuilder *builder;
+	GdaSqlStatement *stmt;
+
+	builder = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "*"), 0);
+	gda_sql_builder_select_add_target (builder, 0,
+					   gda_sql_builder_literal (builder, 0, "mytable"), NULL);
+	stmt = gda_sql_builder_get_sql_statement (builder, FALSE);
+	g_object_unref (builder);
+	return stmt;
+}
+
+/*
+ * SELECT contents, descr, rank, name FROM mytable WHERE (session = ##session::string) AND ((type = 'TABLE') AND (name = 'alf'))
+ */
+static GdaSqlStatement *
+build1 (void)
+{
+	GdaSqlBuilder *builder;
+	guint op_ids [3];
+	gint index = 0;
+	GdaSqlStatement *stmt;
+
+	memset (op_ids, 0, sizeof (guint) * 3);
+
+	builder = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_select_add_target (builder, 0,
+					   gda_sql_builder_literal (builder, 0, "mytable"), NULL);
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "contents"), 0);
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "descr"), 0);
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "rank"), 0);
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "name"), 0);
+	gda_sql_builder_cond (builder, 1, GDA_SQL_OPERATOR_TYPE_EQ,
+			      gda_sql_builder_literal (builder, 0, "session"),
+			      gda_sql_builder_param (builder, 0, "session", G_TYPE_STRING, FALSE), 0);
+
+	op_ids [index] = gda_sql_builder_cond (builder, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+					       gda_sql_builder_literal (builder, 0, "type"),
+					       gda_sql_builder_expr (builder, 0, NULL, G_TYPE_STRING, "TABLE"), 0);
+	index++;
+
+	op_ids [index] = gda_sql_builder_cond (builder, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+					       gda_sql_builder_literal (builder, 0, "name"),
+					       gda_sql_builder_expr (builder, 0, NULL, G_TYPE_STRING, "alf"), 0);
+	index++;
+
+	gda_sql_builder_cond_v (builder, 2, GDA_SQL_OPERATOR_TYPE_AND, op_ids, index);
+
+	gda_sql_builder_set_where (builder, gda_sql_builder_cond (builder, 0, GDA_SQL_OPERATOR_TYPE_AND, 1, 2, 0));
+
+	stmt = gda_sql_builder_get_sql_statement (builder, FALSE);
+	g_object_unref (builder);
+	return stmt;
+}
+
+/*
+ * INSERT INTO mytable (session, type, name, contents, descr) VALUES (##session::int, ##type::int,
+ *                      ##name::string::null, ##contents::string, ##descr::string::null)
+ */
+static GdaSqlStatement *
+build2 (void)
+{
+	GdaSqlBuilder *builder;
+	GdaSqlStatement *stmt;
+
+	builder = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
+	gda_sql_builder_set_table (builder, "mytable");
+
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "session"),
+				   gda_sql_builder_param (builder, 0, "session", G_TYPE_INT, FALSE));
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "type"),
+				   gda_sql_builder_param (builder, 0, "type", G_TYPE_INT, FALSE));
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "name"),
+				   gda_sql_builder_param (builder, 0, "name", G_TYPE_STRING, TRUE));
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "contents"),
+				   gda_sql_builder_param (builder, 0, "contents", G_TYPE_STRING, FALSE));
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "descr"),
+				   gda_sql_builder_param (builder, 0, "descr", G_TYPE_STRING, TRUE));
+
+	stmt = gda_sql_builder_get_sql_statement (builder, FALSE);
+	g_object_unref (builder);
+	return stmt;
+}
+
+/*
+ * UPDATE mytable set name = ##name::string::null, contents = ##contents::string, descr = ##descr::string::null
+ *                WHERE id = ##id::int
+ */
+static GdaSqlStatement *
+build3 (void)
+{
+	GdaSqlBuilder *builder;
+	GdaSqlStatement *stmt;
+
+	builder = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
+	gda_sql_builder_set_table (builder, "mytable");
+
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "name"),
+				   gda_sql_builder_param (builder, 0, "name", G_TYPE_STRING, TRUE));
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "contents"),
+				   gda_sql_builder_param (builder, 0, "contents", G_TYPE_STRING, FALSE));
+	gda_sql_builder_add_field (builder,
+				   gda_sql_builder_literal (builder, 0, "descr"),
+				   gda_sql_builder_param (builder, 0, "descr", G_TYPE_STRING, TRUE));
+
+	gda_sql_builder_set_where (builder,
+				   gda_sql_builder_cond (builder, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+							 gda_sql_builder_literal (builder, 0, "id"),
+							 gda_sql_builder_param (builder, 0, "id", G_TYPE_INT, FALSE),
+							 0));
+
+	stmt = gda_sql_builder_get_sql_statement (builder, FALSE);
+	g_object_unref (builder);
+	return stmt;
+}
+
+/*
+ * SELECT fav.* FROM mytable fav LEFT JOIN fav_orders o ON (fav.id=o.fav_id)
+ *              WHERE fav.session=##session::int AND o.order_key=##okey::int
+ *              ORDER BY o.rank
+ */
+static GdaSqlStatement *
+build4 (void)
+{
+	GdaSqlBuilder *b;
+	GdaSqlStatement *stmt;
+	guint t1, t2;
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_literal (b, 0, "fav.*"), 0);
+	t1 = gda_sql_builder_select_add_target (b, 0,
+						gda_sql_builder_literal (b, 0, "mytable"),
+						"fav");
+	t2 = gda_sql_builder_select_add_target (b, 0,
+						gda_sql_builder_literal (b, 0, "fav_orders"),
+						"o");
+	gda_sql_builder_select_join_targets (b, 0, t1, t2, GDA_SQL_SELECT_JOIN_LEFT,
+					     gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+								   gda_sql_builder_literal (b, 0, "fav.id"),
+								   gda_sql_builder_literal (b, 0, "o.fav_id"),
+								   0));
+
+	gda_sql_builder_set_where (b,
+	    gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_AND,
+		  gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+					gda_sql_builder_literal (b, 0, "fav.sesion"),
+					gda_sql_builder_param (b, 0, "session", G_TYPE_INT, FALSE), 0),
+		  gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+					gda_sql_builder_literal (b, 0, "o.order_key"),
+					gda_sql_builder_param (b, 0, "okey", G_TYPE_INT, FALSE), 0), 0));
+
+	gda_sql_builder_select_order_by (b,
+					 gda_sql_builder_literal (b, 0, "o.rank"), TRUE, NULL);
+	stmt = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+	return stmt;
+}
+
+/*
+ * SELECT id FROM mytable
+ *              WHERE session=##session::int AND contents=##contents::string
+ */
+static GdaSqlStatement *
+build5 (void)
+{
+	GdaSqlBuilder *b;
+	GdaSqlStatement *stmt;
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_literal (b, 0, "id"), 0);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_literal (b, 0, "mytable"),
+					   NULL);
+
+	gda_sql_builder_set_where (b,
+	    gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_AND,
+		  gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+					gda_sql_builder_literal (b, 0, "sesion"),
+					gda_sql_builder_param (b, 0, "session", G_TYPE_INT, FALSE), 0),
+		  gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+					gda_sql_builder_literal (b, 0, "contents"),
+					gda_sql_builder_param (b, 0, "contents", G_TYPE_INT, FALSE), 0), 0));
+
+	stmt = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+	return stmt;
+}
+
+/*
+ * SELECT fav_id, rank FROM mytable
+ *              WHERE order_key=##orderkey::int AND fav_id!=##id::int
+ */
+static GdaSqlStatement *
+build6 (void)
+{
+	GdaSqlBuilder *b;
+	GdaSqlStatement *stmt;
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (b, gda_sql_builder_literal (b, 0, "fav_id"), 0);
+	gda_sql_builder_add_field (b, gda_sql_builder_literal (b, 0, "rank"), 0);
+
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_literal (b, 0, "mytable"),
+					   NULL);
+	gda_sql_builder_cond (b, 1, GDA_SQL_OPERATOR_TYPE_EQ,
+			      gda_sql_builder_literal (b, 0, "order_key"),
+			      gda_sql_builder_param (b, 0, "orderkey", G_TYPE_INT, FALSE), 0);
+	gda_sql_builder_cond (b, 2, GDA_SQL_OPERATOR_TYPE_DIFF,
+			      gda_sql_builder_literal (b, 0, "fav_id"),
+			      gda_sql_builder_param (b, 0, "id", G_TYPE_INT, FALSE), 0);
+
+	gda_sql_builder_set_where (b, gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_AND, 1, 2, 0));
+
+	stmt = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+	return stmt;
+}
+
+/*
+ * UPDATE mytable SET rank=##newrank WHERE fav_id = ##id::int AND order_key=##orderkey::int AND rank = ##rank::int
+ */
+static GdaSqlStatement *
+build7 (void)
+{
+	GdaSqlBuilder *b;
+	GdaSqlStatement *stmt;
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
+	gda_sql_builder_set_table (b, "mytable");
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_literal (b, 0, "rank"),
+				   gda_sql_builder_param (b, 0, "newrank", G_TYPE_INT, FALSE));
+	gda_sql_builder_cond (b, 1, GDA_SQL_OPERATOR_TYPE_EQ,
+			      gda_sql_builder_literal (b, 0, "fav_id"),
+			      gda_sql_builder_param (b, 0, "id", G_TYPE_INT, FALSE),
+			      0);
+	gda_sql_builder_cond (b, 2, GDA_SQL_OPERATOR_TYPE_EQ,
+			      gda_sql_builder_literal (b, 0, "order_key"),
+			      gda_sql_builder_param (b, 0, "orderkey", G_TYPE_INT, FALSE),
+			      0);
+	gda_sql_builder_cond (b, 3, GDA_SQL_OPERATOR_TYPE_EQ,
+			      gda_sql_builder_literal (b, 0, "rank"),
+			      gda_sql_builder_param (b, 0, "rank", G_TYPE_INT, FALSE),
+			      0);
+	gda_sql_builder_set_where (b, gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_AND, 1, 2, 3));
+
+	stmt = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+	return stmt;
+}
+
+/*
+ * DELETE FROM mytable SET WHERE fav_id = ##id::int
+ */
+static GdaSqlStatement *
+build8 (void)
+{
+	GdaSqlBuilder *b;
+	GdaSqlStatement *stmt;
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_DELETE);
+	gda_sql_builder_set_table (b, "mytable");
+	
+	gda_sql_builder_set_where (b, gda_sql_builder_cond (b, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+							    gda_sql_builder_literal (b, 0, "id"),
+							    gda_sql_builder_param (b, 0, "id", G_TYPE_INT, FALSE),
+							    0));
+	stmt = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+	return stmt;
+}
+
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]