GNOME.org
 

[libgda/LIBGDA_4.2] Improved statement rewriting for NULL parameters

commit fa05008e840fce0f879f40657bb2ed6b403d2166
Author: Vivien Malerba <malerba gnome-db org>
Date:   Fri Aug 19 15:56:01 2011 +0200

    Improved statement rewriting for NULL parameters

 doc/C/libgda-sections.txt             |    1 +
 libgda/gda-util.c                     |   87 ++++++++++++++++++++++++++++++--
 libgda/gda-util.h                     |    6 ++-
 libgda/libgda.symbols                 |    1 +
 libgda/providers-support/gda-pstmt.h  |    1 +
 libgda/sqlite/gda-sqlite-provider.c   |   57 ++++++++++++++++-----
 tests/parser/check_rewrite_for_null.c |    2 +-
 7 files changed, 132 insertions(+), 23 deletions(-)
---
diff --git a/doc/C/libgda-sections.txt b/doc/C/libgda-sections.txt
index ee085b7..037688b 100644
--- a/doc/C/libgda-sections.txt
+++ b/doc/C/libgda-sections.txt
@@ -1597,6 +1597,7 @@ gda_meta_store_set_reserved_keywords_func
 <SUBSECTION>
 gda_compute_dml_statements
 gda_compute_select_statement_from_update
+gda_rewrite_sql_statement_for_null_parameters
 gda_rewrite_statement_for_null_parameters
 gda_compute_unique_table_row_condition
 gda_compute_unique_table_row_condition_with_cnc
diff --git a/libgda/gda-util.c b/libgda/gda-util.c
index 52a73a3..b68428b 100644
--- a/libgda/gda-util.c
+++ b/libgda/gda-util.c
@@ -1420,7 +1420,7 @@ get_prev_expr (GSList *expr_list, GdaSqlAnyPart *expr)
 }
 
 static gboolean
-null_param_unknown_foreach_func (GdaSqlAnyPart *part, NullData *data , GError **error)
+null_param_unknown_foreach_func (GdaSqlAnyPart *part, NullData *data, GError **error)
 {
 	GdaSqlExpr *expr;
 	if ((part->type != GDA_SQL_ANY_EXPR) || !((GdaSqlExpr*) part)->param_spec)
@@ -1486,28 +1486,36 @@ null_param_unknown_foreach_func (GdaSqlAnyPart *part, NullData *data , GError **
 }
 
 /**
- * gda_rewrite_statement_for_null_parameters:
- * @stmt: (transfer full): a #GdaSqlStatement
+ * gda_rewrite_sql_statement_for_null_parameters: (skip):
+ * @sqlst: (transfer full): a #GdaSqlStatement
  * @params: a #GdaSet to be used as parameters when executing @stmt
+ * @out_modified: (allow-none): a place to store the boolean which tells if @stmt has been modified or not, or %NULL
  * @error: a place to store errors, or %NULL
  *
- * Modifies @stmt to take into account any parameter which might be %NULL: if @stmt contains the
+ * Modifies @sqlst to take into account any parameter which might be %NULL: if @sqlst contains the
  * equivalent of "xxx = &lt;parameter definition&gt;" and if that parameter is in @params and
  * its value is of type GDA_TYPE_NUL, then that part is replaced with "xxx IS NULL". It also
  * handles the "xxx IS NOT NULL" transformation.
  *
+ * If @out_modified is not %NULL, then it will be set to %TRUE if @sqlst has been modified
+ * by this function, and to %FALSE otherwise.
+ *
  * This function is used by provider's implementations to make sure one can use parameters with
  * NULL values in statements without having to rewrite statements, as database usually don't
  * consider that "xxx = NULL" is the same as "xxx IS NULL" when using parameters.
  *
- * Returns: (transfer full): the modified @stmt statement, or %NULL if an error occurred
+ * Returns: (transfer full): the modified @sqlst statement, or %NULL if an error occurred
  *
  * Since: 4.2.9
  */
 GdaSqlStatement *
-gda_rewrite_statement_for_null_parameters (GdaSqlStatement *sqlst, GdaSet *params, GError **error)
+gda_rewrite_sql_statement_for_null_parameters (GdaSqlStatement *sqlst, GdaSet *params,
+					       gboolean *out_modified, GError **error)
 {
+	if (out_modified)
+		*out_modified = FALSE;
 	g_return_val_if_fail (sqlst, sqlst);
+
 	if (!params)
 		return sqlst;
 	GSList *list;
@@ -1532,6 +1540,8 @@ gda_rewrite_statement_for_null_parameters (GdaSqlStatement *sqlst, GdaSet *param
 			gda_sql_statement_free (sqlst);
 			return NULL;
 		}
+		if (out_modified)
+			*out_modified = data.expr_list ? TRUE : FALSE;
 		for (list = data.expr_list; list; list = list->next) {
 			((GdaSqlStatementUnknown*) data.contents)->expressions = 
 				g_slist_remove (((GdaSqlStatementUnknown*) data.contents)->expressions,
@@ -1546,6 +1556,8 @@ gda_rewrite_statement_for_null_parameters (GdaSqlStatement *sqlst, GdaSet *param
 			gda_sql_statement_free (sqlst);
 			return NULL;
 		}
+		if (out_modified)
+			*out_modified = data.expr_list ? TRUE : FALSE;
 		for (list = data.expr_list; list; list = list->next) {
 			GdaSqlOperation *op;
 			op = (GdaSqlOperation*) (((GdaSqlAnyPart*) list->data)->parent);
@@ -1561,6 +1573,69 @@ gda_rewrite_statement_for_null_parameters (GdaSqlStatement *sqlst, GdaSet *param
 	return sqlst;
 }
 
+/**
+ * gda_rewrite_statement_for_null_parameters:
+ * @stmt: (transfer none): a #GdaStatement
+ * @params: a #GdaSet to be used as parameters when executing @stmt
+ * @out_stmt: (transfer full) (allow-none): a place to store the new #GdaStatement, or %NULL
+ * @error: a place to store errors, or %NULL
+ *
+ * Modifies @stmt to take into account any parameter which might be %NULL: if @stmt contains the
+ * equivalent of "xxx = &lt;parameter definition&gt;" and if that parameter is in @params and
+ * its value is of type GDA_TYPE_NUL, then that part is replaced with "xxx IS NULL". It also
+ * handles the "xxx IS NOT NULL" transformation.
+ *
+ * For example the following SELECT:
+ * <programlisting>SELECT * FROM data WHERE id = ##id::int::null AND name = ##name::string</programlisting>
+ * in case the "id" parameter is set to NULL, is converted to:
+ * <programlisting>SELECT * FROM data WHERE id IS NULL AND name = ##name::string</programlisting>
+ *
+ * if @out_stmt is not %NULL, then it will contain:
+ * <itemizedlist>
+ *   <listitem><para>the modified statement if some modifications were required and no error occured (the function returns %TRUE)</para></listitem>
+ *   <listitem><para>%NULL if no modification to @stmt were required and no erro occurred (the function returns %FALSE)</para></listitem>
+ *   <listitem><para>%NULL if an error occured (the function returns %TRUE)</para></listitem>
+ * </itemizedlist>
+ *
+ * This function is used by provider's implementations to make sure one can use parameters with
+ * NULL values in statements without having to rewrite statements, as database usually don't
+ * consider that "xxx = NULL" is the same as "xxx IS NULL" when using parameters.
+ *
+ * Returns: %TRUE if @stmt needs to be transformed to handle NULL parameters, and %FALSE otherwise
+ *
+ * Since: 4.2.9
+ */
+gboolean
+gda_rewrite_statement_for_null_parameters (GdaStatement *stmt, GdaSet *params,
+					   GdaStatement **out_stmt, GError **error)
+{
+	GdaSqlStatement *sqlst;
+	gboolean mod;
+	g_return_val_if_fail (GDA_IS_STATEMENT (stmt), FALSE);
+	g_return_val_if_fail (!params || GDA_IS_SET (params), FALSE);
+
+	if (out_stmt)
+		*out_stmt = NULL;
+	if (!params)
+		return FALSE;
+
+	g_object_get ((GObject*) stmt, "structure", &sqlst, NULL);
+	if (gda_rewrite_sql_statement_for_null_parameters (sqlst, params, &mod, error)) {
+		if (out_stmt) {
+			if (mod) {
+				*out_stmt = g_object_new (GDA_TYPE_STATEMENT, "structure", sqlst, NULL);
+				gda_sql_statement_free (sqlst);
+			}
+		}
+		return mod;
+	}
+	else {
+		/* error => leave *out_stmt to %NULL */
+		return TRUE;
+	}
+}
+
+
 
 static gboolean stmt_rewrite_insert_remove (GdaSqlStatementInsert *ins, GdaSet *params, GError **error);
 static gboolean stmt_rewrite_insert_default_keyword (GdaSqlStatementInsert *ins, GdaSet *params, GError **error);
diff --git a/libgda/gda-util.h b/libgda/gda-util.h
index c905a26..e1ef4ae 100644
--- a/libgda/gda-util.h
+++ b/libgda/gda-util.h
@@ -88,8 +88,10 @@ gboolean         gda_compute_dml_statements (GdaConnection *cnc, GdaStatement *s
 					     GdaStatement **insert_stmt, GdaStatement **update_stmt, GdaStatement **delete_stmt, 
 					     GError **error);
 GdaSqlStatement *gda_compute_select_statement_from_update (GdaStatement *update_stmt, GError **error);
-GdaSqlStatement *gda_rewrite_statement_for_null_parameters (GdaSqlStatement *sqlst, GdaSet *params,
-							    GError **error);
+GdaSqlStatement *gda_rewrite_sql_statement_for_null_parameters (GdaSqlStatement *sqlst, GdaSet *params,
+								gboolean *out_modified, GError **error);
+gboolean         gda_rewrite_statement_for_null_parameters (GdaStatement *stmt, GdaSet *params,
+							    GdaStatement **out_stmt, GError **error);
 
 /*
  * DSN and connection string manipulations
diff --git a/libgda/libgda.symbols b/libgda/libgda.symbols
index 9609890..7f1f235 100644
--- a/libgda/libgda.symbols
+++ b/libgda/libgda.symbols
@@ -527,6 +527,7 @@
 	gda_repetitive_statement_get_type
 	gda_repetitive_statement_new
 	gda_rewrite_statement_for_null_parameters
+	gda_rewrite_sql_statement_for_null_parameters
 	gda_rfc1738_decode
 	gda_rfc1738_encode
 	gda_row_get_length
diff --git a/libgda/providers-support/gda-pstmt.h b/libgda/providers-support/gda-pstmt.h
index f1362ec..211f2c6 100644
--- a/libgda/providers-support/gda-pstmt.h
+++ b/libgda/providers-support/gda-pstmt.h
@@ -48,6 +48,7 @@ struct _GdaPStmt {
 	GSList       *tmpl_columns; /* list of #GdaColumn objects which data models created from this prep. statement
 				     * can copy */
 
+	/*< private >*/
 	/* Padding for future expansion */
 	gpointer         _gda_reserved1;
 	gpointer         _gda_reserved2;
diff --git a/libgda/sqlite/gda-sqlite-provider.c b/libgda/sqlite/gda-sqlite-provider.c
index 250fa88..e7f5b67 100644
--- a/libgda/sqlite/gda-sqlite-provider.c
+++ b/libgda/sqlite/gda-sqlite-provider.c
@@ -2718,7 +2718,14 @@ gda_sqlite_provider_statement_execute (GdaServerProvider *provider, GdaConnectio
 			}
 		}
 		if (!h) {
-			if (! allow_noparam) {
+			if (allow_noparam) {
+				/* bind param to NULL */
+				SQLITE3_CALL (sqlite3_bind_null) (ps->sqlite_stmt, i);
+				empty_rs = TRUE;
+				continue;
+			}
+			else {
+
 				gchar *str;
 				str = g_strdup_printf (_("Missing parameter '%s' to execute query"), pname);
 				event = gda_connection_point_available_event (cnc, GDA_CONNECTION_EVENT_ERROR);
@@ -2728,16 +2735,16 @@ gda_sqlite_provider_statement_execute (GdaServerProvider *provider, GdaConnectio
 				g_free (str);
 				break;
 			}
-			else {
+		}
+
+		if (!gda_holder_is_valid (h)) {
+			if (allow_noparam) {
 				/* bind param to NULL */
 				SQLITE3_CALL (sqlite3_bind_null) (ps->sqlite_stmt, i);
 				empty_rs = TRUE;
 				continue;
 			}
-		}
-
-		if (!gda_holder_is_valid (h)) {
-			if (! allow_noparam) {
+			else {
 				gchar *str;
 				str = g_strdup_printf (_("Parameter '%s' is invalid"), pname);
 				event = gda_connection_point_available_event (cnc, GDA_CONNECTION_EVENT_ERROR);
@@ -2747,12 +2754,6 @@ gda_sqlite_provider_statement_execute (GdaServerProvider *provider, GdaConnectio
 				g_free (str);
 				break;
 			}
-			else {
-				/* bind param to NULL */
-				SQLITE3_CALL (sqlite3_bind_null) (ps->sqlite_stmt, i);
-				empty_rs = TRUE;
-				continue;
-			}
 		}
 		else if (gda_holder_value_is_default (h) && !gda_holder_get_value (h)) {
 			/* create a new GdaStatement to handle all default values and execute it instead */
@@ -2788,8 +2789,36 @@ gda_sqlite_provider_statement_execute (GdaServerProvider *provider, GdaConnectio
 		/*g_print ("BINDING param '%s' to %p\n", pname, h);*/
 		
 		const GValue *value = gda_holder_get_value (h);
-		if (!value || gda_value_is_null (value))
-			SQLITE3_CALL (sqlite3_bind_null) (ps->sqlite_stmt, i);
+		if (!value || gda_value_is_null (value)) {
+			GdaStatement *rstmt;
+			if (! gda_rewrite_statement_for_null_parameters (stmt, params, &rstmt, error))
+				SQLITE3_CALL (sqlite3_bind_null) (ps->sqlite_stmt, i);
+			else if (!rstmt)
+				return NULL;
+			else {
+				GObject *obj;
+				obj = gda_sqlite_provider_statement_execute (provider, cnc,
+									     rstmt, params,
+									     model_usage,
+									     col_types,
+									     last_inserted_row,
+									     task_id, async_cb,
+									     cb_data, error);
+				g_object_unref (rstmt);
+				if (GDA_IS_DATA_SELECT (obj)) {
+					GdaPStmt *pstmt;
+					g_object_get (obj, "prepared-stmt", &pstmt, NULL);
+					if (pstmt) {
+						gda_pstmt_set_gda_statement (pstmt, stmt);
+						g_object_unref (pstmt);
+					}
+				}
+				if (new_ps)
+					g_object_unref (ps);
+				pending_blobs_free_list (blobs_list);
+				return obj;
+			}
+		}
 		else if (G_VALUE_TYPE (value) == G_TYPE_STRING)
 			SQLITE3_CALL (sqlite3_bind_text) (ps->sqlite_stmt, i, 
 							  g_value_get_string (value), -1, SQLITE_TRANSIENT);
diff --git a/tests/parser/check_rewrite_for_null.c b/tests/parser/check_rewrite_for_null.c
index bdc78f6..23a23e2 100644
--- a/tests/parser/check_rewrite_for_null.c
+++ b/tests/parser/check_rewrite_for_null.c
@@ -83,7 +83,7 @@ do_test (ATest *test)
 	GdaSqlStatement *sqlst;
 	g_object_get (stmt, "structure", &sqlst, NULL);
 
-	sqlst = gda_rewrite_statement_for_null_parameters (sqlst, params, &error);
+	sqlst = gda_rewrite_sql_statement_for_null_parameters (sqlst, params, NULL, &error);
 	if (!sqlst) {
 		g_print ("Rewrite error: %s\n", error && error->message ? error->message : "No detail");
 		return FALSE;
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]