GNOME.org
 

[libgda] Added support in GdaSqlBuilder for DISTINCT and LIMIT clauses

commit c7499c9e8492c186c8fd51928870d34e3d3eead4
Author: Vivien Malerba <malerba gnome-db org>
Date:   Sun Feb 28 15:10:17 2010 +0100

    Added support in GdaSqlBuilder for DISTINCT and LIMIT clauses

 doc/C/libgda-sections.txt       |    2 +
 doc/C/tmpl/gda-sql-builder.sgml |   20 ++++++++
 libgda/gda-sql-builder.c        |  104 +++++++++++++++++++++++++++++++++++++++
 libgda/gda-sql-builder.h        |    6 ++-
 libgda/libgda.symbols           |    2 +
 tests/test-sql-builder.c        |   74 ++++++++++++++++++++++++++--
 6 files changed, 202 insertions(+), 6 deletions(-)
---
diff --git a/doc/C/libgda-sections.txt b/doc/C/libgda-sections.txt
index bc06c49..7bcc174 100644
--- a/doc/C/libgda-sections.txt
+++ b/doc/C/libgda-sections.txt
@@ -1692,6 +1692,8 @@ gda_sql_builder_select_add_field
 gda_sql_builder_select_join_targets
 gda_sql_builder_join_add_field
 gda_sql_builder_select_order_by
+gda_sql_builder_select_set_distinct
+gda_sql_builder_select_set_limit
 <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 938eb4e..d124696 100644
--- a/doc/C/tmpl/gda-sql-builder.sgml
+++ b/doc/C/tmpl/gda-sql-builder.sgml
@@ -347,6 +347,26 @@ gda_sql_builder_add_cond2 (b, 5, GDA_SQL_OPERATOR_TYPE_AND, 3, 4); // whole expr
 @collation_name: 
 
 
+<!-- ##### FUNCTION gda_sql_builder_select_set_distinct ##### -->
+<para>
+
+</para>
+
+ builder: 
+ distinct: 
+ expr_id: 
+
+
+<!-- ##### FUNCTION gda_sql_builder_select_set_limit ##### -->
+<para>
+
+</para>
+
+ builder: 
+ limit_count_expr_id: 
+ limit_offest_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 4bf5427..a59c3b4 100644
--- a/libgda/gda-sql-builder.c
+++ b/libgda/gda-sql-builder.c
@@ -1307,6 +1307,110 @@ gda_sql_builder_select_order_by (GdaSqlBuilder *builder, guint expr_id,
 }
 
 /**
+ * gda_sql_builder_select_set_distinct
+ * @builder: a #GdaSqlBuilder object
+ * @distinct: set to %TRUE to have the DISTINCT requirement
+ * @expr_id: the ID of the DISTINCT ON expression, or %0 if no expression is to be used. It is ignored
+ *           if @distinct is %FALSE.
+ *
+ * Defines (if @distinct is %TRUE) or removes (if @distinct is %FALSE) a DISTINCT clause
+ * for a SELECT statement.
+ *
+ * If @distinct is %TRUE, then the ID of an expression can be specified as the @expr_id argument:
+ * if not %0, this is the expression used to apply the DISTINCT clause on (the resuting SQL
+ * will then usually be "... DISTINCT ON &lt;expression&gt;...").
+ *
+ * Since: 4.2
+ */
+void
+gda_sql_builder_select_set_distinct (GdaSqlBuilder *builder, gboolean distinct, guint expr_id)
+{
+	GdaSqlStatementSelect *sel;
+	SqlPart *part = NULL;
+
+	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;
+	}
+
+	if (expr_id) {
+		part = get_part (builder, expr_id, GDA_SQL_ANY_EXPR);
+		if (!part)
+			return;
+	}
+
+	sel = (GdaSqlStatementSelect*) builder->priv->main_stmt->contents;
+	if (sel->distinct_expr) {
+		gda_sql_expr_free (sel->distinct_expr);
+		sel->distinct_expr = NULL;
+	}
+
+	if (distinct && part)
+		sel->distinct_expr = (GdaSqlExpr*) use_part (part, GDA_SQL_ANY_PART (sel));
+	sel->distinct = distinct;
+}
+
+/**
+ * gda_sql_builder_select_set_limit
+ * @builder: a #GdaSqlBuilder object
+ * @limit_count_expr_id: the ID of the LIMIT expression, or %0
+ * @limit_offset_expr_id: the ID of the OFFSET expression, or %0
+ *
+ * If @limit_count_expr_id is not %0, defines the maximum number of rows in the #GdaDataModel
+ * resulting from the execution of the built statement. In this case, the offset from which the
+ * rows must be collected can be defined by the @limit_offset_expr_id expression if not %0 (note that
+ * this feature may not be supported by all the database providers).
+ *
+ * If @limit_count_expr_id is %0, then removes any LIMIT which may have been imposed by a previous
+ * call to this method.
+ *
+ * Since: 4.2
+ */
+void
+gda_sql_builder_select_set_limit (GdaSqlBuilder *builder,
+				  guint limit_count_expr_id, guint limit_offset_expr_id)
+{
+	GdaSqlStatementSelect *sel;
+	SqlPart *part1 = NULL, *part2 = NULL;
+
+	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;
+	}
+
+	if (limit_count_expr_id) {
+		part1 = get_part (builder, limit_count_expr_id, GDA_SQL_ANY_EXPR);
+		if (!part1)
+			return;
+	}
+	if (limit_offset_expr_id) {
+		part2 = get_part (builder, limit_offset_expr_id, GDA_SQL_ANY_EXPR);
+		if (!part2)
+			return;
+	}
+
+	sel = (GdaSqlStatementSelect*) builder->priv->main_stmt->contents;
+
+	if (sel->limit_count) {
+		gda_sql_expr_free (sel->limit_count);
+		sel->limit_count = NULL;
+	}
+	if (sel->limit_offset) {
+		gda_sql_expr_free (sel->limit_offset);
+		sel->limit_offset = NULL;
+	}
+	if (part1)
+		sel->limit_count = (GdaSqlExpr*) use_part (part1, GDA_SQL_ANY_PART (sel));
+	if (part2)
+		sel->limit_offset = (GdaSqlExpr*) use_part (part2, GDA_SQL_ANY_PART (sel));
+}
+
+
+/**
  * gda_sql_builder_add_function
  * @builder: a #GdaSqlBuilder object
  * @id: the requested ID, or 0 if to be determined by @builder
diff --git a/libgda/gda-sql-builder.h b/libgda/gda-sql-builder.h
index d3a2ad0..f8f7dd3 100644
--- a/libgda/gda-sql-builder.h
+++ b/libgda/gda-sql-builder.h
@@ -1,6 +1,6 @@
 /* gda-sql-builder.h
  *
- * Copyright (C) 2009 Vivien Malerba
+ * Copyright (C) 2009 - 2010 Vivien Malerba
  *
  * This Library is free software; you can redistribute it and/or
  * modify it under the terms of the GNU Library General Public License as
@@ -115,6 +115,10 @@ guint             gda_sql_builder_select_join_targets (GdaSqlBuilder *builder, g
 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);
+void              gda_sql_builder_select_set_distinct (GdaSqlBuilder *builder,
+						       gboolean distinct, guint expr_id);
+void              gda_sql_builder_select_set_limit (GdaSqlBuilder *builder,
+						    guint limit_count_expr_id, guint limit_offest_expr_id);
 
 /* COMPOUND SELECT Statement API */
 void              gda_sql_builder_compound_set_type (GdaSqlBuilder *builder, GdaSqlStatementCompoundType compound_type);
diff --git a/libgda/libgda.symbols b/libgda/libgda.symbols
index 21dc7bc..65c919d 100644
--- a/libgda/libgda.symbols
+++ b/libgda/libgda.symbols
@@ -600,6 +600,8 @@
 	gda_sql_builder_select_add_target_id
 	gda_sql_builder_select_join_targets
 	gda_sql_builder_select_order_by
+	gda_sql_builder_select_set_distinct
+	gda_sql_builder_select_set_limit
 	gda_sql_builder_set_table
 	gda_sql_builder_set_where
 	gda_sql_case_copy
diff --git a/tests/test-sql-builder.c b/tests/test-sql-builder.c
index b95abee..92c9be8 100644
--- a/tests/test-sql-builder.c
+++ b/tests/test-sql-builder.c
@@ -40,6 +40,8 @@ static GdaSqlStatement *build6 (void);
 static GdaSqlStatement *build7 (void);
 static GdaSqlStatement *build8 (void);
 static GdaSqlStatement *build9 (void);
+static GdaSqlStatement *build10 (void);
+static GdaSqlStatement *build11 (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\"}]}}}"},
@@ -51,7 +53,9 @@ ATest tests[] = {
 	{"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}}}}}}}}"},
 	{"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\":\"in
 t\",\"is_param\":true,\"nullok\":false}}}}}}}}"},
 	{"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}}}}}}"},
-	{"build8", build9, "{\"sql\":null,\"stmt_type\":\"INSERT\",\"contents\":{\"table\":\"mytable\",\"fields\":[\"session\",\"name\"],\"values\":[[{\"value\":\"NULL\"},{\"value\":\"NULL\"}]]}}"}
+	{"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\"}}}"}
 };
 
 int
@@ -429,14 +433,74 @@ build9 (void)
 	gda_sql_builder_add_field_value (builder, "name", value);
 	gda_value_free (value);
 
+	stmt = gda_sql_builder_get_sql_statement (builder, FALSE);
+	g_object_unref (builder);
+	
+	return stmt;
+}
+
+/*
+ * SELECT DISTINCT fav_id, rank FROM mytable LIMIT 5
+ */
+static GdaSqlStatement *
+build10 (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, "fav_id"), 0);
+	gda_sql_builder_add_field_id (b, gda_sql_builder_add_id (b, 0, "rank"), 0);
+
+	gda_sql_builder_select_add_target_id (b, 0,
+					   gda_sql_builder_add_id (b, 0, "mytable"),
+					   NULL);
+	gda_sql_builder_select_set_distinct (b, TRUE, 0);
+	gda_sql_builder_select_set_limit (b, gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 5), 0);
+
 	{
 		GdaStatement *st;
-		st = gda_sql_builder_get_statement (builder, FALSE);
+		st = gda_sql_builder_get_statement (b, FALSE);
 		g_print ("[%s]\n", gda_statement_to_sql (st, NULL, NULL));
 		g_object_unref (st);
 	}
-	stmt = gda_sql_builder_get_sql_statement (builder, FALSE);
-	g_object_unref (builder);
-	
+
+	stmt = gda_sql_builder_get_sql_statement (b, FALSE);
+	g_object_unref (b);
+	return stmt;
+}
+
+/*
+ * SELECT DISTINCT ON (rank) fav_id, rank FROM mytable LIMIT 5 OFFSET 2
+ */
+static GdaSqlStatement *
+build11 (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, "fav_id"), 0);
+	gda_sql_builder_add_field_id (b, gda_sql_builder_add_id (b, 0, "rank"), 0);
+
+	gda_sql_builder_select_add_target_id (b, 0,
+					   gda_sql_builder_add_id (b, 0, "mytable"),
+					   NULL);
+	gda_sql_builder_select_set_distinct (b, TRUE,
+					     gda_sql_builder_add_id (b, 0, "rank"));
+	gda_sql_builder_select_set_limit (b, gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 5),
+					  gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 2));
+
+#ifdef DEBUG_NO
+	{
+		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;
 }
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]