GNOME.org
 

[libgda] Completed GdaSqlBuilder

commit 26206b5cc86e48c6d4c1b9d9905babdc1cadb10e
Author: Vivien Malerba <malerba gnome-db org>
Date:   Mon Oct 12 18:49:16 2009 +0200

    Completed GdaSqlBuilder
    
    added:
    * gda_sql_builder_add_case() and gda_sql_builder_add_case_v()
      to create expressions as CASE ... END
    * gda_sql_builder_add_sub_select() to create expressions having a
      sub SELECT
    * gda_sql_builder_compound_add_sub_select() and gda_sql_builder_compound_set_type()
      to handle COMPOUND select statements

 doc/C/libgda-sections.txt         |    7 +
 doc/C/tmpl/gda-sql-builder.sgml   |   59 +++++++
 doc/C/tmpl/gda-sql-statement.sgml |   12 ++
 libgda/gda-sql-builder.c          |  304 ++++++++++++++++++++++++++++++++++---
 libgda/gda-sql-builder.h          |   53 ++++---
 libgda/libgda.symbols             |    5 +
 samples/SqlBuilder/README         |    5 +
 samples/SqlBuilder/example.c      |  109 +++++++++++++
 8 files changed, 510 insertions(+), 44 deletions(-)
---
diff --git a/doc/C/libgda-sections.txt b/doc/C/libgda-sections.txt
index fb09b83..577cf54 100644
--- a/doc/C/libgda-sections.txt
+++ b/doc/C/libgda-sections.txt
@@ -1189,6 +1189,7 @@ gda_sql_statement_update_take_condition
 gda_sql_statement_update_take_set_value
 <SUBSECTION>
 GdaSqlStatementCompound
+GdaSqlStatementCompoundType
 gda_sql_statement_compound_set_type
 gda_sql_statement_compound_take_stmt
 <SUBSECTION>
@@ -1670,6 +1671,9 @@ gda_sql_builder_add_id
 gda_sql_builder_add_expr
 gda_sql_builder_add_expr_value
 gda_sql_builder_add_param
+gda_sql_builder_add_case
+gda_sql_builder_add_case_v
+gda_sql_builder_add_sub_select
 <SUBSECTION>
 gda_sql_builder_add_cond
 gda_sql_builder_add_cond_v
@@ -1679,6 +1683,9 @@ 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>
+gda_sql_builder_compound_add_sub_select
+gda_sql_builder_compound_set_type
 <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 ef5a296..1f90278 100644
--- a/doc/C/tmpl/gda-sql-builder.sgml
+++ b/doc/C/tmpl/gda-sql-builder.sgml
@@ -178,6 +178,46 @@ gda_sql_builder_add_cond2 (b, 5, GDA_SQL_OPERATOR_TYPE_AND, 3, 4); // whole expr
 @Returns: 
 
 
+<!-- ##### FUNCTION gda_sql_builder_add_case ##### -->
+<para>
+
+</para>
+
+ builder: 
+ id: 
+ test_expr: 
+ else_expr: 
+ Varargs: 
+ Returns: 
+
+
+<!-- ##### FUNCTION gda_sql_builder_add_case_v ##### -->
+<para>
+
+</para>
+
+ builder: 
+ id: 
+ test_expr: 
+ else_expr: 
+ when_array: 
+ then_array: 
+ args_size: 
+ Returns: 
+
+
+<!-- ##### FUNCTION gda_sql_builder_add_sub_select ##### -->
+<para>
+
+</para>
+
+ builder: 
+ id: 
+ sqlst: 
+ steal: 
+ Returns: 
+
+
 <!-- ##### FUNCTION gda_sql_builder_add_cond ##### -->
 <para>
 
@@ -261,3 +301,22 @@ gda_sql_builder_add_cond2 (b, 5, GDA_SQL_OPERATOR_TYPE_AND, 3, 4); // whole expr
 @collation_name: 
 
 
+<!-- ##### FUNCTION gda_sql_builder_compound_add_sub_select ##### -->
+<para>
+
+</para>
+
+ builder: 
+ sqlst: 
+ steal: 
+
+
+<!-- ##### FUNCTION gda_sql_builder_compound_set_type ##### -->
+<para>
+
+</para>
+
+ builder: 
+ compound_type: 
+
+
diff --git a/doc/C/tmpl/gda-sql-statement.sgml b/doc/C/tmpl/gda-sql-statement.sgml
index 86f6393..1eac851 100644
--- a/doc/C/tmpl/gda-sql-statement.sgml
+++ b/doc/C/tmpl/gda-sql-statement.sgml
@@ -619,6 +619,18 @@ Specifies the type of functions passed to gda_sql_any_part_foreach().
 @_gda_reserved1: 
 @_gda_reserved2: 
 
+<!-- ##### ENUM GdaSqlStatementCompoundType ##### -->
+<para>
+
+</para>
+
+ GDA_SQL_STATEMENT_COMPOUND_UNION: 
+ GDA_SQL_STATEMENT_COMPOUND_UNION_ALL: 
+ GDA_SQL_STATEMENT_COMPOUND_INTERSECT: 
+ GDA_SQL_STATEMENT_COMPOUND_INTERSECT_ALL: 
+ GDA_SQL_STATEMENT_COMPOUND_EXCEPT: 
+ GDA_SQL_STATEMENT_COMPOUND_EXCEPT_ALL: 
+
 <!-- ##### FUNCTION gda_sql_statement_compound_set_type ##### -->
 <para>
 
diff --git a/libgda/gda-sql-builder.c b/libgda/gda-sql-builder.c
index 941c53b..f434a59 100644
--- a/libgda/gda-sql-builder.c
+++ b/libgda/gda-sql-builder.c
@@ -241,11 +241,14 @@ gda_sql_builder_set_property (GObject *object,
 			if ((stmt_type != GDA_SQL_STATEMENT_SELECT) &&
 			    (stmt_type != GDA_SQL_STATEMENT_UPDATE) &&
 			    (stmt_type != GDA_SQL_STATEMENT_INSERT) &&
-			    (stmt_type != GDA_SQL_STATEMENT_DELETE)) {
+			    (stmt_type != GDA_SQL_STATEMENT_DELETE) &&
+			    (stmt_type != GDA_SQL_STATEMENT_COMPOUND)) {
 				g_critical ("Unsupported statement type: %d", stmt_type);
 				return;
 			}
 			builder->priv->main_stmt = gda_sql_statement_new (stmt_type);
+			if (stmt_type == GDA_SQL_STATEMENT_COMPOUND)
+				gda_sql_builder_compound_set_type (builder, GDA_SQL_STATEMENT_COMPOUND_UNION);
 			break;
 		}
 	}
@@ -489,12 +492,17 @@ gda_sql_builder_set_where (GdaSqlBuilder *builder, guint cond_id)
  * gda_sql_builder_add_field
  * @builder: a #GdaSqlBuilder object
  * @field_id: the ID of the field's name or definition
- * @value_id: the ID of the value to set the field to
+ * @value_id: the ID of the value to set the field to, or %0
  *
  * Valid only for: INSERT, UPDATE, SELECT statements
  *
- * For INSERT and UPDATE: specifies that the field named @field_name will be set to the value identified by @value_id.
+ * For UPDATE: specifies that the field represented by @field_id will be set to the value identified by @value_id.
  * For SELECT: add a selected item to the statement, and if @value_id is not %0, then use it as an alias
+ * For INSERT: if @field_id represents an SQL identifier (obtained using gda_sql_builder_add_id()): then if
+ *             @value_id is not %0 then specifies that the field represented by @field_id will be set to the
+ *             value identified by @value_id, otherwise just specifies a named field to be given a value.
+ *             If @field_id represents a sub SELECT (obtained using gda_sql_builder_add_sub_select()), then
+ *             this method call defines the sub SELECT from which values to insert are taken.
  *
  * Since: 4.2
  */
@@ -516,11 +524,11 @@ gda_sql_builder_add_field (GdaSqlBuilder *builder, guint field_id, guint value_i
 	switch (builder->priv->main_stmt->stmt_type) {
 	case GDA_SQL_STATEMENT_UPDATE:
 	case GDA_SQL_STATEMENT_INSERT:
-		if (!value_part)
-			return;
-		if (!field_expr->value || (G_VALUE_TYPE (field_expr->value) !=  G_TYPE_STRING)) {
-			g_warning (_("Wrong field format"));
-			return;
+		if (!field_expr->select) {
+			if (!field_expr->value || (G_VALUE_TYPE (field_expr->value) !=  G_TYPE_STRING)) {
+				g_warning (_("Wrong field format"));
+				return;
+			}
 		}
 		break;
 	case GDA_SQL_STATEMENT_SELECT:
@@ -543,18 +551,34 @@ gda_sql_builder_add_field (GdaSqlBuilder *builder, guint field_id, guint value_i
 	}
 	case GDA_SQL_STATEMENT_INSERT:{
 		GdaSqlStatementInsert *ins = (GdaSqlStatementInsert*) builder->priv->main_stmt->contents;
-		GdaSqlField *field = gda_sql_field_new (GDA_SQL_ANY_PART (ins));
-		field->field_name = g_value_dup_string (field_expr->value);
-
-		ins->fields_list = g_slist_append (ins->fields_list, field);
 		
-		if (! ins->values_list)
-			ins->values_list = g_slist_append (NULL,
-							   g_slist_append (NULL,
-							   use_part (value_part, GDA_SQL_ANY_PART (ins))));
-		else
-			ins->values_list->data = g_slist_append ((GSList*) ins->values_list->data, 
-								 use_part (value_part, GDA_SQL_ANY_PART (ins)));
+		if (field_expr->select) {
+			switch (GDA_SQL_ANY_PART (field_expr->select)->type) {
+			case GDA_SQL_STATEMENT_SELECT:
+				ins->select = _gda_sql_statement_select_copy (field_expr->select);
+				break;
+			case GDA_SQL_STATEMENT_COMPOUND:
+				ins->select = _gda_sql_statement_compound_copy (field_expr->select);
+				break;
+			default:
+				g_assert_not_reached ();
+			}
+		}
+		else {
+			GdaSqlField *field = gda_sql_field_new (GDA_SQL_ANY_PART (ins));
+			field->field_name = g_value_dup_string (field_expr->value);
+			
+			ins->fields_list = g_slist_append (ins->fields_list, field);
+			if (value_part) {
+				if (! ins->values_list)
+					ins->values_list = g_slist_append (NULL,
+									   g_slist_append (NULL,
+									   use_part (value_part, GDA_SQL_ANY_PART (ins))));
+				else
+					ins->values_list->data = g_slist_append ((GSList*) ins->values_list->data, 
+										 use_part (value_part, GDA_SQL_ANY_PART (ins)));
+			}
+		}
 		break;
 	}
 	case GDA_SQL_STATEMENT_SELECT: {
@@ -674,6 +698,8 @@ gda_sql_builder_add_expr (GdaSqlBuilder *builder, guint id, GdaDataHandler *dh,
 		g_value_set_string ((v = gda_value_new (G_TYPE_STRING)), va_arg (ap, gchar*));
 	else if (type == G_TYPE_INT)
 		g_value_set_int ((v = gda_value_new (G_TYPE_INT)), va_arg (ap, gint));
+	else if (type == G_TYPE_FLOAT)
+		g_value_set_float ((v = gda_value_new (G_TYPE_FLOAT)), va_arg (ap, double));
 	else
 		g_warning (_("Could not convert value to type '%s'"), g_type_name (type));
 	va_end (ap);
@@ -921,8 +947,8 @@ gda_sql_builder_select_add_target (GdaSqlBuilder *builder, guint id, guint table
 	BuildTarget *btarget;
 	GdaSqlStatementSelect *sel = (GdaSqlStatementSelect*) builder->priv->main_stmt->contents;
 	btarget = g_new0 (BuildTarget, 1);
-        GDA_SQL_ANY_PART(btarget)->type = GDA_SQL_ANY_SQL_SELECT_TARGET;
-        GDA_SQL_ANY_PART(btarget)->parent = GDA_SQL_ANY_PART (sel->from);
+        GDA_SQL_ANY_PART (btarget)->type = GDA_SQL_ANY_SQL_SELECT_TARGET;
+        GDA_SQL_ANY_PART (btarget)->parent = GDA_SQL_ANY_PART (sel->from);
 	if (id)
 		btarget->part_id = id;
 	else
@@ -1008,8 +1034,8 @@ gda_sql_builder_select_join_targets (GdaSqlBuilder *builder, guint id,
 	GdaSqlSelectJoin *join;
 
 	bjoin = g_new0 (BuilderJoin, 1);
-	GDA_SQL_ANY_PART(bjoin)->type = GDA_SQL_ANY_SQL_SELECT_JOIN;
-        GDA_SQL_ANY_PART(bjoin)->parent = GDA_SQL_ANY_PART (sel->from);
+	GDA_SQL_ANY_PART (bjoin)->type = GDA_SQL_ANY_SQL_SELECT_JOIN;
+        GDA_SQL_ANY_PART (bjoin)->parent = GDA_SQL_ANY_PART (sel->from);
 	if (id)
 		bjoin->part_id = id;
 	else
@@ -1205,3 +1231,235 @@ gda_sql_builder_add_function_v (GdaSqlBuilder *builder, guint id, const gchar *f
 
 	return add_part (builder, id, (GdaSqlAnyPart *) expr);
 }
+
+/**
+ * gda_sql_builder_add_sub_select
+ * @builder: a #GdaSqlBuilder object
+ * @id: the requested ID, or 0 if to be determined by @builder
+ * @sqlst: a pointer to a #GdaSqlStatement, which has to be a SELECT or compound SELECT
+ * @steal: if %TRUE, then @sqlst will be "stolen" by @b and should not be used anymore
+ *
+ * Adds an expression which is a subselect.
+ *
+ * Returns: the ID of the new expression, or 0 if there was an error
+ *
+ * Since: 4.2
+ */
+guint
+gda_sql_builder_add_sub_select (GdaSqlBuilder *builder, guint id, GdaSqlStatement *sqlst, gboolean steal)
+{
+	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 (sqlst, 0);
+	g_return_val_if_fail ((sqlst->stmt_type == GDA_SQL_STATEMENT_SELECT) ||
+			      (sqlst->stmt_type == GDA_SQL_STATEMENT_COMPOUND), 0);
+	
+	GdaSqlExpr *expr;
+	expr = gda_sql_expr_new (NULL);
+	if (steal) {
+		expr->select = sqlst->contents;
+		sqlst->contents = NULL;
+		gda_sql_statement_free (sqlst);
+	}
+	else {
+		switch (sqlst->stmt_type) {
+		case GDA_SQL_STATEMENT_SELECT:
+			expr->select = _gda_sql_statement_select_copy (sqlst->contents);
+			break;
+		case GDA_SQL_STATEMENT_COMPOUND:
+			expr->select = _gda_sql_statement_compound_copy (sqlst->contents);
+			break;
+		default:
+			g_assert_not_reached ();
+		}
+	}
+	GDA_SQL_ANY_PART (expr->select)->parent = GDA_SQL_ANY_PART (expr);
+
+	return add_part (builder, id, (GdaSqlAnyPart *) expr);
+}
+
+/**
+ * gda_sql_builder_compound_set_type
+ * @builder: a #GdaSqlBuilder object
+ * @compound_type: a type of compound
+ *
+ * Changes the type of compound which @builder is making, for a COMPOUND statement
+ *
+ * Since: 4.2
+ */
+void
+gda_sql_builder_compound_set_type (GdaSqlBuilder *builder, GdaSqlStatementCompoundType compound_type)
+{
+	GdaSqlStatementCompound *cstmt;
+	g_return_if_fail (GDA_IS_SQL_BUILDER (builder));
+	g_return_if_fail (builder->priv->main_stmt);
+	if (builder->priv->main_stmt->stmt_type != GDA_SQL_STATEMENT_COMPOUND) {
+		g_warning (_("Wrong statement type"));
+		return;
+	}
+
+	cstmt = (GdaSqlStatementCompound*) builder->priv->main_stmt->contents;
+	cstmt->compound_type = compound_type;
+}
+
+/**
+ * gda_sql_builder_compound_add_sub_select
+ * @builder: a #GdaSqlBuilder object
+ * @sqlst: a pointer to a #GdaSqlStatement, which has to be a SELECT or compound SELECT
+ * @steal: if %TRUE, then @sqlst will be "stolen" by @b and should not be used anymore
+ *
+ * Add a sub select to a COMPOUND statement
+ * 
+ * Since: 4.2
+ */
+void
+gda_sql_builder_compound_add_sub_select (GdaSqlBuilder *builder, GdaSqlStatement *sqlst, gboolean steal)
+{
+	GdaSqlStatementCompound *cstmt;
+	GdaSqlStatement *sub;
+
+	g_return_if_fail (GDA_IS_SQL_BUILDER (builder));
+	g_return_if_fail (builder->priv->main_stmt);
+	if (builder->priv->main_stmt->stmt_type != GDA_SQL_STATEMENT_COMPOUND) {
+		g_warning (_("Wrong statement type"));
+		return;
+	}
+	g_return_if_fail (sqlst);
+	g_return_if_fail ((sqlst->stmt_type == GDA_SQL_STATEMENT_SELECT) ||
+			  (sqlst->stmt_type == GDA_SQL_STATEMENT_COMPOUND));
+
+	cstmt = (GdaSqlStatementCompound*) builder->priv->main_stmt->contents;
+	if (steal)
+		sub = sqlst;
+	else
+		sub = gda_sql_statement_copy (sqlst);
+
+	cstmt->stmt_list = g_slist_append (cstmt->stmt_list, sub);
+}
+
+/**
+ * gda_sql_builder_add_case
+ * @builder: a #GdaSqlBuilder object
+ * @id: the requested ID, or 0 if to be determined by @builder
+ * @test_expr: the expression ID representing the test of the CASE, or %0
+ * @else_expr: the expression ID representing the ELSE expression, or %0
+ * @...: a list, terminated by a %0, of (WHEN expression ID, THEN expression ID) representing
+ *       all the test cases
+ *
+ * Creates a new CASE ... WHEN ... THEN ... ELSE ... END expression.
+ *
+ * Returns: the ID of the new expression, or 0 if there was an error
+ *
+ * Since: 4.2
+ */
+guint
+gda_sql_builder_add_case (GdaSqlBuilder *builder, guint id,
+			  guint test_expr, guint else_expr, ...)
+{
+	g_return_val_if_fail (GDA_IS_SQL_BUILDER (builder), 0);
+	g_return_val_if_fail (builder->priv->main_stmt, 0);
+
+	SqlPart *ptest, *pelse;
+	ptest = get_part (builder, test_expr, GDA_SQL_ANY_EXPR);
+	pelse = get_part (builder, else_expr, GDA_SQL_ANY_EXPR);
+	
+	GdaSqlExpr *expr;
+	expr = gda_sql_expr_new (NULL);
+
+	expr->case_s = gda_sql_case_new (GDA_SQL_ANY_PART (expr));
+	if (ptest)
+		expr->case_s->base_expr = (GdaSqlExpr*) use_part (ptest, GDA_SQL_ANY_PART (expr->case_s));
+	if (pelse)
+		expr->case_s->else_expr = (GdaSqlExpr*) use_part (pelse, GDA_SQL_ANY_PART (expr->case_s));
+	
+	va_list ap;
+	guint id1;
+	va_start (ap, else_expr);
+	for (id1 = va_arg (ap, guint); id1; id1 = va_arg (ap, guint)) {
+		guint id2;
+		SqlPart *pwhen, *pthen;
+		id2 = va_arg (ap, guint);
+		if (!id2)
+			goto cleanups;
+		pwhen = get_part (builder, id1, GDA_SQL_ANY_EXPR);
+		if (!pwhen)
+			goto cleanups;
+		pthen = get_part (builder, id2, GDA_SQL_ANY_EXPR);
+		if (!pthen)
+			goto cleanups;
+		expr->case_s->when_expr_list = g_slist_prepend (expr->case_s->when_expr_list,
+								use_part (pwhen, GDA_SQL_ANY_PART (expr->case_s)));
+		expr->case_s->then_expr_list = g_slist_prepend (expr->case_s->then_expr_list,
+								use_part (pthen, GDA_SQL_ANY_PART (expr->case_s)));
+	}
+	va_end (ap);
+	expr->case_s->when_expr_list = g_slist_reverse (expr->case_s->when_expr_list);
+	expr->case_s->then_expr_list = g_slist_reverse (expr->case_s->then_expr_list);
+	return add_part (builder, id, (GdaSqlAnyPart *) expr);
+	
+ cleanups:
+	gda_sql_expr_free (expr);
+	return 0;
+}
+
+/**
+ * gda_sql_builder_add_case_v
+ * @builder: a #GdaSqlBuilder object
+ * @id: the requested ID, or 0 if to be determined by @builder
+ * @test_expr: the expression ID representing the test of the CASE, or %0
+ * @else_expr: the expression ID representing the ELSE expression, or %0
+ * @when_array: an array containing each WHEN expression ID, having at least @args_size elements
+ * @then_array: an array containing each THEN expression ID, having at least @args_size elements
+ * @args_size: the size of @when_array and @then_array
+ *
+ * Creates a new CASE ... WHEN ... THEN ... ELSE ... END expression. The WHEN expression and the THEN
+ * expression IDs are taken from the @when_array and @then_array at the same index, for each index inferior to
+ * @args_size.
+ *
+ * Returns: the ID of the new expression, or 0 if there was an error
+ *
+ * Since: 4.2
+ */
+guint
+gda_sql_builder_add_case_v (GdaSqlBuilder *builder, guint id,
+			    guint test_expr, guint else_expr,
+			    const guint *when_array, const guint *then_array, gint args_size)
+{
+	g_return_val_if_fail (GDA_IS_SQL_BUILDER (builder), 0);
+	g_return_val_if_fail (builder->priv->main_stmt, 0);
+
+	SqlPart *ptest, *pelse;
+	ptest = get_part (builder, test_expr, GDA_SQL_ANY_EXPR);
+	pelse = get_part (builder, else_expr, GDA_SQL_ANY_EXPR);
+	
+	GdaSqlExpr *expr;
+	expr = gda_sql_expr_new (NULL);
+
+	expr->case_s = gda_sql_case_new (GDA_SQL_ANY_PART (expr));
+	if (ptest)
+		expr->case_s->base_expr = (GdaSqlExpr*) use_part (ptest, GDA_SQL_ANY_PART (expr->case_s));
+	if (pelse)
+		expr->case_s->else_expr = (GdaSqlExpr*) use_part (pelse, GDA_SQL_ANY_PART (expr->case_s));
+	
+	gint i;
+	for (i = 0; i < args_size; i++) {
+		SqlPart *pwhen, *pthen;
+		pwhen = get_part (builder, when_array[i], GDA_SQL_ANY_EXPR);
+		if (!pwhen)
+			goto cleanups;
+		pthen = get_part (builder, then_array[i], GDA_SQL_ANY_EXPR);
+		if (!pthen)
+			goto cleanups;
+		expr->case_s->when_expr_list = g_slist_prepend (expr->case_s->when_expr_list,
+								use_part (pwhen, GDA_SQL_ANY_PART (expr->case_s)));
+		expr->case_s->then_expr_list = g_slist_prepend (expr->case_s->then_expr_list,
+								use_part (pthen, GDA_SQL_ANY_PART (expr->case_s)));
+	}
+	expr->case_s->when_expr_list = g_slist_reverse (expr->case_s->when_expr_list);
+	expr->case_s->then_expr_list = g_slist_reverse (expr->case_s->then_expr_list);
+	return add_part (builder, id, (GdaSqlAnyPart *) expr);
+	
+ cleanups:
+	gda_sql_expr_free (expr);
+	return 0;
+}
diff --git a/libgda/gda-sql-builder.h b/libgda/gda-sql-builder.h
index fae267e..e5139b9 100644
--- a/libgda/gda-sql-builder.h
+++ b/libgda/gda-sql-builder.h
@@ -57,6 +57,7 @@ struct _GdaSqlBuilderClass
 {
 	GObjectClass              parent_class;
 
+	/*< private >*/
 	/* Padding for future expansion */
 	void (*_gda_reserved1) (void);
 	void (*_gda_reserved2) (void);
@@ -70,30 +71,26 @@ GdaStatement     *gda_sql_builder_get_statement (GdaSqlBuilder *builder, GError
 GdaSqlStatement  *gda_sql_builder_get_sql_statement (GdaSqlBuilder *builder, gboolean copy_it);
 
 /* Expression API */
-guint              gda_sql_builder_add_id (GdaSqlBuilder *builder, guint id, const gchar *string);
-guint              gda_sql_builder_add_expr (GdaSqlBuilder *builder, guint id, GdaDataHandler *dh, GType type, ...);
-guint              gda_sql_builder_add_expr_value (GdaSqlBuilder *builder, guint id, GdaDataHandler *dh, GValue* value);
-guint              gda_sql_builder_add_param (GdaSqlBuilder *builder, guint id, const gchar *param_name, GType type, gboolean nullok);
-
-guint              gda_sql_builder_add_cond (GdaSqlBuilder *builder, guint id, GdaSqlOperatorType op,
-					 guint op1, guint op2, guint op3);
-guint              gda_sql_builder_add_cond_v (GdaSqlBuilder *builder, guint id, GdaSqlOperatorType op,
-					   const guint *op_ids, gint op_ids_size);
+guint             gda_sql_builder_add_id (GdaSqlBuilder *builder, guint id, const gchar *string);
+guint             gda_sql_builder_add_expr (GdaSqlBuilder *builder, guint id, GdaDataHandler *dh, GType type, ...);
+guint             gda_sql_builder_add_expr_value (GdaSqlBuilder *builder, guint id, GdaDataHandler *dh, GValue* value);
+guint             gda_sql_builder_add_param (GdaSqlBuilder *builder, guint id, const gchar *param_name, GType type, gboolean nullok);
+
+guint             gda_sql_builder_add_cond (GdaSqlBuilder *builder, guint id, GdaSqlOperatorType op,
+					    guint op1, guint op2, guint op3);
+guint             gda_sql_builder_add_cond_v (GdaSqlBuilder *builder, guint id, GdaSqlOperatorType op,
+					      const guint *op_ids, gint op_ids_size);
 guint             gda_sql_builder_add_function (GdaSqlBuilder *builder, guint id, const gchar *func_name, ...);
 guint             gda_sql_builder_add_function_v (GdaSqlBuilder *builder, guint id, const gchar *func_name,
 						  const guint *args, gint args_size);
-
-
-/* SELECT Statement API */
-guint              gda_sql_builder_select_add_target (GdaSqlBuilder *builder, guint id,
-						      guint table_id, const gchar *alias);
-guint              gda_sql_builder_select_join_targets (GdaSqlBuilder *builder, guint id,
-							guint left_target_id, guint right_target_id,
-							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);
+guint             gda_sql_builder_add_sub_select (GdaSqlBuilder *builder, guint id, GdaSqlStatement *sqlst,
+						  gboolean steal);
+guint             gda_sql_builder_add_case (GdaSqlBuilder *builder, guint id,
+					    guint test_expr, guint else_expr, ...);
+guint             gda_sql_builder_add_case_v (GdaSqlBuilder *builder, guint id,
+					      guint test_expr, guint else_expr,
+					      const guint *when_array, const guint *then_array, gint args_size);
+					    
 
 /* General Statement API */
 void              gda_sql_builder_set_table (GdaSqlBuilder *builder, const gchar *table_name);
@@ -101,6 +98,20 @@ void              gda_sql_builder_set_where (GdaSqlBuilder *builder, guint cond_
 
 void              gda_sql_builder_add_field (GdaSqlBuilder *builder, guint field_id, guint value_id);
 
+/* SELECT Statement API */
+guint             gda_sql_builder_select_add_target (GdaSqlBuilder *builder, guint id,
+						     guint table_id, const gchar *alias);
+guint             gda_sql_builder_select_join_targets (GdaSqlBuilder *builder, guint id,
+						       guint left_target_id, guint right_target_id,
+						       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);
+
+/* COMPOUND SELECT Statement API */
+void              gda_sql_builder_compound_set_type (GdaSqlBuilder *builder, GdaSqlStatementCompoundType compound_type);
+void              gda_sql_builder_compound_add_sub_select (GdaSqlBuilder *builder, GdaSqlStatement *sqlst, gboolean steal);
 
 G_END_DECLS
 
diff --git a/libgda/libgda.symbols b/libgda/libgda.symbols
index 053b9ad..de4e514 100644
--- a/libgda/libgda.symbols
+++ b/libgda/libgda.symbols
@@ -563,6 +563,8 @@
 	gda_sql_any_part_check_structure
 	gda_sql_any_part_foreach
 	gda_sql_any_part_type_get_type
+	gda_sql_builder_add_case
+	gda_sql_builder_add_case_v
 	gda_sql_builder_add_cond
 	gda_sql_builder_add_cond_v
 	gda_sql_builder_add_expr
@@ -572,8 +574,11 @@
 	gda_sql_builder_add_function_v
 	gda_sql_builder_add_id
 	gda_sql_builder_add_param
+	gda_sql_builder_compound_add_sub_select
+	gda_sql_builder_compound_set_type
 	gda_sql_builder_get_statement
 	gda_sql_builder_get_sql_statement
+	gda_sql_builder_add_sub_select
 	gda_sql_builder_get_type
 	gda_sql_builder_join_add_field
 	gda_sql_builder_new
diff --git a/samples/SqlBuilder/README b/samples/SqlBuilder/README
index 7e332c2..da52319 100644
--- a/samples/SqlBuilder/README
+++ b/samples/SqlBuilder/README
@@ -33,6 +33,11 @@ SQL: SELECT myfunc (a, 5, 'Joe') FROM mytable
 SQL: SELECT myfunc (a, 5, 'Joe'), MAX (myfunc (a, 5, 'Joe'), b, 10) FROM mytable
 SQL: UPDATE "select" SET "date"='2009-05-27' WHERE id = 14
 SQL: SELECT date (a, 5, 'Joe') FROM "date", MyTable
+SQL: SELECT name FROM master WHERE id IN (SELECT id FROM subdata)
+SQL: INSERT INTO customers (e, f, g) SELECT id, name, location FROM subdate
+SQL: SELECT id, name FROM subdata1 UNION SELECT ident, lastname FROM subdata2
+SQL: SELECT CASE WHEN price < 1.200000 THEN 2 ELSE 1 END FROM data
+SQL: SELECT CASE tag WHEN 'Alpha' THEN 1 WHEN 'Bravo' THEN 2 WHEN 'Charlie' THEN 3 END FROM data
 
 and for example_cnc:
 SQL: INSERT INTO "customers" ("e", "f", "g") VALUES (##p1::string, 15, 'joe')
diff --git a/samples/SqlBuilder/example.c b/samples/SqlBuilder/example.c
index b2425d2..be2dac2 100644
--- a/samples/SqlBuilder/example.c
+++ b/samples/SqlBuilder/example.c
@@ -146,7 +146,116 @@ main (int argc, char *argv[])
 				      0);
 	gda_sql_builder_add_field (b, 1, 0);
 	render_as_sql (b);
+	g_object_unref (b);
+
+	/* Subselect: SELECT name FROM master WHERE id IN (SELECT id FROM subdata) */
+	GdaSqlStatement *sub;
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "id"), 0);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_add_id (b, 0, "subdata"),
+					   NULL);
+	sub = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "name"), 0);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_add_id (b, 0, "master"),
+					   NULL);
+	gda_sql_builder_add_id (b, 1, "id");
+	gda_sql_builder_add_sub_select (b, 2, sub, TRUE);
+	gda_sql_builder_add_cond (b, 3, GDA_SQL_OPERATOR_TYPE_IN, 1, 2, 0);
+	gda_sql_builder_set_where (b, 3);
+	render_as_sql (b);
+	g_object_unref (b);
 	
+	/* Subselect in INSERT: INSERT INTO customers (e, f, g) SELECT id, name, location FROM subdate */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "id"), 0);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "name"), 0);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "location"), 0);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_add_id (b, 0, "subdate"),
+					   NULL);
+	sub = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
+
+	gda_sql_builder_set_table (b, "customers");
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "e"), 0);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "f"), 0);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "g"), 0);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_sub_select (b, 0, sub, TRUE), 0);
+	
+	render_as_sql (b);
+	g_object_unref (b);
+
+
+	/* compound: SELECT id, name FROM subdata1 UNION SELECT ident, lastname FROM subdata2 */
+	GdaSqlStatement *sub1, *sub2;
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "id"), 0);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "name"), 0);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_add_id (b, 0, "subdata1"),
+					   NULL);
+	sub1 = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "ident"), 0);
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "lastname"), 0);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_add_id (b, 0, "subdata2"),
+					   NULL);
+	sub2 = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_COMPOUND);
+	gda_sql_builder_compound_add_sub_select (b, sub1, TRUE);
+	gda_sql_builder_compound_add_sub_select (b, sub2, TRUE);
+	render_as_sql (b);
+	g_object_unref (b);
+
+	/* SELECT CASE WHEN price < 1.200000 THEN 2 ELSE 1 END FROM data */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_cond (b, 1, GDA_SQL_OPERATOR_TYPE_LT,
+				  gda_sql_builder_add_id (b, 0, "price"),
+				  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_FLOAT, 1.2), 0);
+	
+	gda_sql_builder_add_case (b, 10, 
+				  0,
+				  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 1),
+				  1, gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 2),
+				  0);
+	gda_sql_builder_add_field (b, 10, 0);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_add_id (b, 0, "data"),
+					   NULL);
+	render_as_sql (b);
+	g_object_unref (b);
+
+	/* SELECT CASE tag WHEN 'Alpha' THEN 1 WHEN 'Bravo' THEN 2 WHEN 'Charlie' THEN 3 ELSE 0 END FROM data */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_case (b, 10, 
+				  gda_sql_builder_add_id (b, 0, "tag"),
+				  0,
+				  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_STRING, "Alpha"),
+				  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 1),
+				  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_STRING, "Bravo"),
+				  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 2),
+				  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_STRING, "Charlie"),
+				  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 3),
+				  0);
+	gda_sql_builder_add_field (b, 10, 0);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_add_id (b, 0, "data"),
+					   NULL);
+	render_as_sql (b);
+	g_object_unref (b);
+
 	return 0;
 }
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]