[libgda] GdaSqlBuilder can now specify HAVING and GROUP BY

[Vivien Malerba <vivien src gnome org>]

commit 4d9ada091853cd7133fba66842f9de34627d8381
Author: Vivien Malerba <malerba gnome-db org>
Date:   Wed Mar 31 17:40:19 2010 +0200

    GdaSqlBuilder can now specify HAVING and GROUP BY

 doc/C/libgda-sections.txt       |    2 +
 doc/C/tmpl/gda-sql-builder.sgml |   18 +++++++++
 libgda/gda-sql-builder.c        |   76 +++++++++++++++++++++++++++++++++++++++
 libgda/gda-sql-builder.h        |    3 ++
 libgda/libgda.symbols           |    2 +
 tests/test-sql-builder.c        |   44 ++++++++++++++++++++++-
 6 files changed, 144 insertions(+), 1 deletions(-)
---
diff --git a/doc/C/libgda-sections.txt b/doc/C/libgda-sections.txt
index e209976..0c6f9a5 100644
--- a/doc/C/libgda-sections.txt
+++ b/doc/C/libgda-sections.txt
@@ -1696,6 +1696,8 @@ gda_sql_builder_join_add_field
 gda_sql_builder_select_order_by
 gda_sql_builder_select_set_distinct
 gda_sql_builder_select_set_limit
+gda_sql_builder_select_set_having
+gda_sql_builder_select_group_by
 <SUBSECTION>
 gda_sql_builder_compound_add_sub_select
 gda_sql_builder_compound_set_type
diff --git a/doc/C/tmpl/gda-sql-builder.sgml b/doc/C/tmpl/gda-sql-builder.sgml
index d124696..b3e678a 100644
--- a/doc/C/tmpl/gda-sql-builder.sgml
+++ b/doc/C/tmpl/gda-sql-builder.sgml
@@ -367,6 +367,24 @@ gda_sql_builder_add_cond2 (b, 5, GDA_SQL_OPERATOR_TYPE_AND, 3, 4); // whole expr
 @limit_offest_expr_id: 
 
 
+<!-- ##### FUNCTION gda_sql_builder_select_set_having ##### -->
+<para>
+
+</para>
+
+ builder: 
+ cond_id: 
+
+
+<!-- ##### FUNCTION gda_sql_builder_select_group_by ##### -->
+<para>
+
+</para>
+
+ builder: 
+ expr_id: 
+
+
 <!-- ##### FUNCTION gda_sql_builder_compound_add_sub_select ##### -->
 <para>
 
diff --git a/libgda/gda-sql-builder.c b/libgda/gda-sql-builder.c
index a59c3b4..1583ff5 100644
--- a/libgda/gda-sql-builder.c
+++ b/libgda/gda-sql-builder.c
@@ -1409,6 +1409,82 @@ gda_sql_builder_select_set_limit (GdaSqlBuilder *builder,
 		sel->limit_offset = (GdaSqlExpr*) use_part (part2, GDA_SQL_ANY_PART (sel));
 }
 
+/**
+ * gda_sql_builder_select_set_having
+ * @builder: a #GdaSqlBuilder object
+ * @cond_id: the ID of the expression to set as HAVING condition, or 0 to unset any previous HAVING condition
+ *
+ * Valid only for: SELECT statements
+ *
+ * Sets the HAVING condition of the statement
+ *
+ * Since: 4.2
+ */
+void
+gda_sql_builder_select_set_having (GdaSqlBuilder *builder, guint cond_id)
+{
+	GdaSqlStatementSelect *sel;
+
+	g_return_if_fail (GDA_IS_SQL_BUILDER (builder));
+
+	if (builder->priv->main_stmt->stmt_type != GDA_SQL_STATEMENT_SELECT) {
+		g_warning (_("Wrong statement type"));
+		return;
+	}
+
+	SqlPart *p = NULL;
+	if (cond_id > 0) {
+		p = get_part (builder, cond_id, GDA_SQL_ANY_EXPR);
+		if (!p)
+			return;
+	}
+
+	sel = (GdaSqlStatementSelect*) builder->priv->main_stmt->contents;
+	if (sel->having_cond)
+		gda_sql_expr_free (sel->having_cond);
+	sel->having_cond = (GdaSqlExpr*) use_part (p, GDA_SQL_ANY_PART (sel));
+}
+
+/**
+ * gda_sql_builder_select_group_by
+ * @builder: a #GdaSqlBuilder object
+ * @expr_id: the ID of the expression to set use in the GROUP BY clause, or 0 to unset any previous GROUP BY clause
+ *
+ * Valid only for: SELECT statements
+ *
+ * Adds the @expr_id expression to the GROUP BY clause's expressions list
+ *
+ * Since: 4.2
+ */
+void
+gda_sql_builder_select_group_by (GdaSqlBuilder *builder, guint expr_id)
+{
+	GdaSqlStatementSelect *sel;
+
+	g_return_if_fail (GDA_IS_SQL_BUILDER (builder));
+
+	if (builder->priv->main_stmt->stmt_type != GDA_SQL_STATEMENT_SELECT) {
+		g_warning (_("Wrong statement type"));
+		return;
+	}
+
+	SqlPart *p = NULL;
+	if (expr_id > 0) {
+		p = get_part (builder, expr_id, GDA_SQL_ANY_EXPR);
+		if (!p)
+			return;
+	}
+
+	sel = (GdaSqlStatementSelect*) builder->priv->main_stmt->contents;
+	if (p)
+		sel->group_by = g_slist_append (sel->group_by,
+						(GdaSqlExpr*) use_part (p, GDA_SQL_ANY_PART (sel)));
+	else if (sel->group_by) {
+		g_slist_foreach (sel->group_by, (GFunc)	gda_sql_expr_free, NULL);
+		g_slist_free (sel->group_by);
+		sel->group_by = NULL;
+	}
+}
 
 /**
  * gda_sql_builder_add_function
diff --git a/libgda/gda-sql-builder.h b/libgda/gda-sql-builder.h
index f8f7dd3..0d17c34 100644
--- a/libgda/gda-sql-builder.h
+++ b/libgda/gda-sql-builder.h
@@ -120,6 +120,9 @@ void              gda_sql_builder_select_set_distinct (GdaSqlBuilder *builder,
 void              gda_sql_builder_select_set_limit (GdaSqlBuilder *builder,
 						    guint limit_count_expr_id, guint limit_offest_expr_id);
 
+void              gda_sql_builder_select_set_having (GdaSqlBuilder *builder, guint cond_id);
+void              gda_sql_builder_select_group_by (GdaSqlBuilder *builder, guint expr_id);
+
 /* 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);
diff --git a/libgda/libgda.symbols b/libgda/libgda.symbols
index fb3f4c6..2ba2b87 100644
--- a/libgda/libgda.symbols
+++ b/libgda/libgda.symbols
@@ -598,9 +598,11 @@
 	gda_sql_builder_select_add_field
 	gda_sql_builder_select_add_target
 	gda_sql_builder_select_add_target_id
+	gda_sql_builder_select_group_by
 	gda_sql_builder_select_join_targets
 	gda_sql_builder_select_order_by
 	gda_sql_builder_select_set_distinct
+	gda_sql_builder_select_set_having
 	gda_sql_builder_select_set_limit
 	gda_sql_builder_set_table
 	gda_sql_builder_set_where
diff --git a/tests/test-sql-builder.c b/tests/test-sql-builder.c
index 92c9be8..f90911b 100644
--- a/tests/test-sql-builder.c
+++ b/tests/test-sql-builder.c
@@ -42,6 +42,7 @@ static GdaSqlStatement *build8 (void);
 static GdaSqlStatement *build9 (void);
 static GdaSqlStatement *build10 (void);
 static GdaSqlStatement *build11 (void);
+static GdaSqlStatement *build12 (void);
 
 ATest tests[] = {
 	{"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\"}]}}}"},
@@ -55,7 +56,8 @@ ATest tests[] = {
 	{"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}}}}}}"},
 	{"build9", build9, "{\"sql\":null,\"stmt_type\":\"INSERT\",\"contents\":{\"table\":\"mytable\",\"fields\":[\"session\",\"name\"],\"values\":[[{\"value\":\"NULL\"},{\"value\":\"NULL\"}]]}}"},
 	{"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\"}}}"},
-	{"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\"}}}"}
+	{"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\"}}}"},
+	{"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\"}}}}}"}
 };
 
 int
@@ -504,3 +506,43 @@ build11 (void)
 	g_object_unref (b);
 	return stmt;
 }
+
+/*
+ * SELECT store_name, sum (sales) FROM stores GROUP BY store_name HAVING sum (sales) > 10
+ */
+static GdaSqlStatement *
+build12 (void)
+{
+	GdaSqlBuilder *b;
+	GdaSqlStatement *stmt;
+
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_add_field_id (b, gda_sql_builder_add_id (b, 0, "store_name"), 0);
+	gda_sql_builder_add_function (b, 1, "sum",
+				      gda_sql_builder_add_id (b, 0, "sales"), 0);
+	gda_sql_builder_add_field_id (b, 1, 0);
+
+	gda_sql_builder_select_add_target_id (b, 0,
+					   gda_sql_builder_add_id (b, 0, "stores"),
+					   NULL);
+	gda_sql_builder_select_group_by (b, gda_sql_builder_add_id (b, 0, "store_name"));
+	gda_sql_builder_select_set_having (b,
+					   gda_sql_builder_add_cond (b, 0, GDA_SQL_OPERATOR_TYPE_GT,
+								     1,
+								     gda_sql_builder_add_expr (b, 0, NULL,
+											       G_TYPE_INT, 10),
+								     0));
+
+#ifdef DEBUG
+	{
+		GdaStatement *st;
+		st = gda_sql_builder_get_statement (b, FALSE);
+		g_print ("[%s]\n", gda_statement_to_sql (st, NULL, NULL));
+		g_object_unref (st);
+	}
+#endif
+
+	stmt = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+	return stmt;
+}