[libgda/LIBGDA_4.0] Bugs fixed in GdaDataSelect, new example using GdaDataSelect

[Vivien Malerba <vivien src gnome org>]

commit 3e91e89d30b3a6d0265856279156857ef85206aa
Author: Vivien Malerba <malerba gnome-db org>
Date:   Wed Jun 24 15:20:01 2009 +0200

    Bugs fixed in GdaDataSelect, new example using GdaDataSelect
    
    * fixed 2 bugs in GdaDataSelect regarding internal rows handling
    * added a new WritableSelect example to show how to use the GdaDataSelect
      object

 Makefile.am                      |    5 +-
 libgda/gda-data-select.c         |   14 ++-
 samples/README                   |    2 +
 samples/WritableSelect/README    |  100 ++++++++++++++++++++
 samples/WritableSelect/example.c |  192 ++++++++++++++++++++++++++++++++++++++
 5 files changed, 306 insertions(+), 7 deletions(-)
---
diff --git a/Makefile.am b/Makefile.am
index 6a136ee..dda7d57 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -62,7 +62,10 @@ example_files = \
 	samples/XSLT/data.xml \
 	samples/XSLT/data2.xml \
 	samples/XSLT/test.xsl \
-	samples/XSLT/transform.c
+	samples/XSLT/transform.c \
+	samples/WritableSelect/README \
+	samples/WritableSelect/example.c \
+	samples/WritableSelect/Makefile
 
 EXTRA_DIST = \
 	COPYING \
diff --git a/libgda/gda-data-select.c b/libgda/gda-data-select.c
index 4075ca0..9786d1e 100644
--- a/libgda/gda-data-select.c
+++ b/libgda/gda-data-select.c
@@ -1674,8 +1674,7 @@ gda_data_select_get_value_at (GdaDataModel *model, gint col, gint row, GError **
 			GdaDataModel *tmpmodel;
 			if (!dstmt->select || !dstmt->params) {
 				g_set_error (error, GDA_DATA_MODEL_ERROR, GDA_DATA_MODEL_ACCESS_ERROR,
-					      "%s", _("Unable to retreive data after modifications, no further modification will be allowed"));
-				imodel->priv->modif_internals->safely_locked = TRUE;
+					      "%s", _("Unable to retreive data after modifications"));
 				return NULL;
 			}
 			tmpmodel = gda_connection_statement_execute_select (imodel->priv->cnc, 
@@ -1760,7 +1759,8 @@ gda_data_select_get_attributes_at (GdaDataModel *model, gint col, gint row)
 	imodel = (GdaDataSelect *) model;
 	g_return_val_if_fail (imodel->priv, 0);
 	
-	if (imodel->priv->modif_internals->safely_locked || !imodel->priv->modif_internals->modif_stmts [UPD_QUERY])
+	if (imodel->priv->modif_internals->safely_locked ||
+	    !imodel->priv->modif_internals->modif_stmts [UPD_QUERY])
 		flags = GDA_VALUE_ATTR_NO_MODIF;
 	GdaColumn *gdacol = g_slist_nth_data (imodel->priv->columns, col);
 	if (gdacol) {
@@ -2398,7 +2398,7 @@ vector_set_value_at (GdaDataSelect *imodel, BVector *bv, GdaDataModelIter *iter,
 				}
 			}
 			else {
-				cvalue = gda_data_model_get_value_at ((GdaDataModel*) imodel, i, int_row, error);
+				cvalue = gda_data_model_get_value_at ((GdaDataModel*) imodel, i, row, error);
 				if (!cvalue)
 					return FALSE;
 			}
@@ -2759,6 +2759,8 @@ gda_data_select_append_values (GdaDataModel *model, const GList *values, GError
 
 	/* compute added row's number */
 	row = imodel->advertized_nrows;
+	if (imodel->priv->del_rows)
+		row -= imodel->priv->del_rows->len;
 	imodel->advertized_nrows++;
 	int_row = external_to_internal_row (imodel, row, error);
 	imodel->advertized_nrows--;
@@ -2858,7 +2860,8 @@ gda_data_select_append_values (GdaDataModel *model, const GList *values, GError
 
 	GdaSet *last_insert;
 	if (gda_connection_statement_execute_non_select (imodel->priv->cnc, stmt,
-							 imodel->priv->modif_internals->modif_set, &last_insert, error) == -1)
+							 imodel->priv->modif_internals->modif_set,
+							 &last_insert, error) == -1)
 		return -1;
 
 	/* mark that this row has been modified */
@@ -2907,7 +2910,6 @@ gda_data_select_append_values (GdaDataModel *model, const GList *values, GError
 #ifdef GDA_DEBUG_NO
 	dump_d (imodel);
 #endif
-
 	imodel->advertized_nrows++;
 	gda_data_model_row_inserted ((GdaDataModel *) imodel, row);
 
diff --git a/samples/README b/samples/README
index 5379d78..f58ca09 100644
--- a/samples/README
+++ b/samples/README
@@ -17,5 +17,7 @@ Currently the contents are:
 * in DDL/: example using the GdaServerOperation object to perform a CREATE TABLE query
 * in SqlParserConsole/: simple console to test how the GdaSqlParser object parses strings
 * in MetaStore/: simple example to show how to use and update a GdaMetaStore
+* in WritableSelect/: example to show how to modify a GdaDataModel returned after
+  the execution of a SELECT statement
 
 Good luck and happy hacking!
diff --git a/samples/WritableSelect/README b/samples/WritableSelect/README
new file mode 100644
index 0000000..6e370a9
--- /dev/null
+++ b/samples/WritableSelect/README
@@ -0,0 +1,100 @@
+Writing to a data model created by executing a SELECT
+=====================================================
+
+Description:
+------------
+
+The example in this directory illustrate how to write to a data model created 
+when executing a SELECT statement. It opens a connection to the SalesTest DSN,
+obtains all the data in the "customers" table, and then modify the contents
+of the returned data model (after each modification, the data model's contents
+is printed and the actual data in the "customers" is also printed).
+
+Note that for this to work, the meta data associated to the "customers" table must
+be up to date, which is why gda_connection_update_meta_store() is called before
+modifying the data model.
+
+Compiling and running:
+----------------------
+
+To compile (make sure Libgda is installed prior to this):
+> make
+
+and to run:
+> ./example
+
+The result should be similar to:
+** Data model is:
+id | name           
+---+----------------
+ 2 | Ed Lamton      
+ 3 | Lew Bonito     
+ 4 | Mark Lawrencep 
+ 9 | Greg Popoff    
+10 | Vladimir Zirkov
+(5 rows)
+
+Computed UPDATE: UPDATE customers SET id=##+0::int, name=##+1::string WHERE id = ##-0::int
+Computed DELETE: DELETE FROM customers WHERE id = ##-0::int
+Computed INSERT: INSERT INTO customers (id, name) VALUES (##+0::int, ##+1::string)
+
+
+** Removing row 0
+** Data model is now:
+id | name           
+---+----------------
+ 3 | Lew Bonito     
+ 4 | Mark Lawrencep 
+ 9 | Greg Popoff    
+10 | Vladimir Zirkov
+(4 rows)
+** Table's contents is now:
+id | name           
+---+----------------
+ 3 | Lew Bonito     
+ 4 | Mark Lawrencep 
+ 9 | Greg Popoff    
+10 | Vladimir Zirkov
+(4 rows)
+
+
+** Adding a row
+** Data model is now:
+id | name           
+---+----------------
+ 3 | Lew Bonito     
+ 4 | Mark Lawrencep 
+ 9 | Greg Popoff    
+10 | Vladimir Zirkov
+11 | Hiro           
+(5 rows)
+** Table's contents is now:
+id | name           
+---+----------------
+ 3 | Lew Bonito     
+ 4 | Mark Lawrencep 
+ 9 | Greg Popoff    
+10 | Vladimir Zirkov
+11 | Hiro           
+(5 rows)
+
+
+** Modifying row 2
+** Data model is now:
+id | name           
+---+----------------
+ 3 | Lew Bonito     
+ 4 | Mark Lawrencep 
+ 9 | Tom            
+10 | Vladimir Zirkov
+11 | Hiro           
+(5 rows)
+** Table's contents is now:
+id | name           
+---+----------------
+ 3 | Lew Bonito     
+ 4 | Mark Lawrencep 
+ 9 | Tom            
+10 | Vladimir Zirkov
+11 | Hiro           
+(5 rows)
diff --git a/samples/WritableSelect/example.c b/samples/WritableSelect/example.c
new file mode 100644
index 0000000..1c0d97b
--- /dev/null
+++ b/samples/WritableSelect/example.c
@@ -0,0 +1,192 @@
+#include <libgda/libgda.h>
+#include <sql-parser/gda-sql-parser.h>
+
+GdaSqlParser *parser;
+
+GdaConnection *open_connection (void);
+void display_customers (GdaConnection *cnc);
+void run_sql_non_select (GdaConnection *cnc, const gchar *sql);
+
+int
+main (int argc, char *argv[])
+{
+        gda_init ();
+
+        GdaConnection *cnc;
+	GError *error = NULL;
+	GdaStatement *stmt;
+	GdaDataModel *model;
+	gchar *str;
+	GValue *name;
+
+	/* open connection */
+	cnc = open_connection ();
+
+	/* begin transaction */
+	if (! gda_connection_begin_transaction (cnc, NULL, GDA_TRANSACTION_ISOLATION_UNKNOWN,
+						&error)) {
+		g_print ("Could not begin transaction: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+	}
+	
+	/* execute SELECT */
+	stmt = gda_sql_parser_parse_string (parser, "SELECT id, name FROM customers ORDER BY id", NULL, NULL);
+	g_assert (stmt);
+	model = gda_connection_statement_execute_select (cnc, stmt, NULL, &error);
+	g_object_unref (stmt);
+	if (!model) {
+		g_print ("Could not execute SELECT statement: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+	}
+
+	g_print ("** Data model is:\n");
+	gda_data_model_dump (model, stdout);
+
+	/*
+	 * make sure the mete data is up to date
+	 */
+	g_print ("Computing meta data, this may take a while; in real applications, this should be cached to avoid waiting\n");
+	if (! gda_connection_update_meta_store (cnc, NULL, &error)) {
+		g_print ("Could not fetch meta data: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+	}
+
+	/*
+	 * Make the data model compute the modification statements which
+	 * will actually be executed when the data model is modified
+	 */
+	if (! gda_data_select_compute_modification_statements (GDA_DATA_SELECT (model), &error)) {
+		g_print ("Could not compute modification statements: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+	}
+
+	g_object_get (G_OBJECT (model), "update-stmt", &stmt, NULL);
+	str = gda_statement_to_sql (stmt, NULL, NULL);
+	g_print ("Computed UPDATE: %s\n", str);
+	g_free (str);
+	g_object_unref (stmt);
+	g_object_get (G_OBJECT (model), "delete-stmt", &stmt, NULL);
+	str = gda_statement_to_sql (stmt, NULL, NULL);
+	g_print ("Computed DELETE: %s\n", str);
+	g_free (str);
+	g_object_unref (stmt);
+	g_object_get (G_OBJECT (model), "insert-stmt", &stmt, NULL);
+	str = gda_statement_to_sql (stmt, NULL, NULL);
+	g_print ("Computed INSERT: %s\n", str);
+	g_free (str);
+	g_object_unref (stmt);
+
+	/*
+	 * remove row 4 (5th row)
+	 */
+	g_print ("\n\n** Removing row 0\n");
+	if (! gda_data_model_remove_row (model, 0, &error)) {
+		g_print ("Could not remove row 1: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+	}
+	g_print ("** Data model is now:\n");
+	gda_data_model_dump (model, stdout);
+	g_print ("** Table's contents is now:\n");
+	display_customers (cnc);
+
+	/*
+	 * add a row: the row's values is a list of GValue pointers 
+	 * (or NULL pointers where the default value should be inserted
+	 */
+	GList *list;
+	g_print ("\n\n** Adding a row\n");
+	list = g_list_append (NULL, NULL); 
+	g_value_set_string ((name = gda_value_new (G_TYPE_STRING)), "Hiro");
+	list = g_list_append (list, name);
+	if (! gda_data_model_append_values (model, list, &error)) {
+		g_print ("Could add a row: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+	}
+	gda_value_free (name);
+	g_list_free (list);
+	g_print ("** Data model is now:\n");
+	gda_data_model_dump (model, stdout);
+	g_print ("** Table's contents is now:\n");
+	display_customers (cnc);
+
+	/*
+	 * alter row 2
+	 */
+	g_print ("\n\n** Modifying row 2\n");
+	g_value_set_string ((name = gda_value_new (G_TYPE_STRING)), "Tom");
+	if (! gda_data_model_set_value_at (model, 1, 2, name, &error)) {
+		g_print ("Could not modify row 3: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+	}
+	gda_value_free (name);
+	g_print ("** Data model is now:\n");
+	gda_data_model_dump (model, stdout);
+	g_print ("** Table's contents is now:\n");
+	display_customers (cnc);
+
+	/* rollback transaction */
+	gda_connection_rollback_transaction (cnc, NULL, NULL);
+
+        gda_connection_close (cnc);
+
+        return 0;
+}
+
+/*
+ * Open a connection to the example.db file
+ */
+GdaConnection *
+open_connection ()
+{
+        GdaConnection *cnc;
+        GError *error = NULL;
+
+	/* open connection */
+        cnc = gda_connection_open_from_dsn ("SalesTest", NULL,
+					    GDA_CONNECTION_OPTIONS_NONE,
+					    &error);
+        if (!cnc) {
+                g_print ("Could not open connection: %s\n",
+                         error && error->message ? error->message : "No detail");
+                exit (1);
+        }
+
+	/* create an SQL parser */
+	parser = gda_connection_create_parser (cnc);
+	if (!parser) /* @cnc doe snot provide its own parser => use default one */
+		parser = gda_sql_parser_new ();
+	/* attach the parser object to the connection */
+	g_object_set_data_full (G_OBJECT (cnc), "parser", parser, g_object_unref);
+
+        return cnc;
+}
+
+/* 
+ * display the contents of the 'products' table 
+ */
+void
+display_customers (GdaConnection *cnc)
+{
+	GdaDataModel *data_model;
+	GdaSqlParser *parser;
+	GdaStatement *stmt;
+	gchar *sql = "SELECT id, name FROM customers ORDER BY id";
+	GError *error = NULL;
+
+	parser = g_object_get_data (G_OBJECT (cnc), "parser");
+	stmt = gda_sql_parser_parse_string (parser, sql, NULL, NULL);
+	data_model = gda_connection_statement_execute_select (cnc, stmt, NULL, &error);
+	g_object_unref (stmt);
+        if (!data_model) 
+                g_error ("Could not get the contents of the 'products' table: %s\n",
+                         error && error->message ? error->message : "No detail");
+	gda_data_model_dump (data_model, stdout);
+	g_object_unref (data_model);
+}