[gnome-db] patch : gda-postgres remove-row.

[Bas Driessen <bas driessen xobas com>]

Hello,

I am (re)visiting the record-set implementation again for the Postgres provider, since I want to start using that now. Let's start with the non-existing "remove-row" method.

Attached a patch for the "remove-row" method for data models for the Postgres provider. It will update both the data model as the underlying database. Some comments:

- Only works if there is a unique key available (similar like the existing "update-row" method). This is probably correct, but I am not sure if Postgres has a concept of a "row-id" that we can use. 

- Postgres provider uses the hash version of a Data Model. This means that only information requested will be in the Data Model. If a row is requested from the data model that has not been loaded yet, the information will come from the Postgres cursor (PGresult). Because of this concept, it is pretty much impossible to physically remove a row from the data model, since the numbering of the data model will then no longer be in sync with the Postgres cursor (data can not be removed from the Postgres cursor unless triggering the SQL again which can be time consuming). Therefore I came up with the concept of placing the text "NULL" in all fields of the row deleted from the data model and setting the row id (gda_row_set_id) to "R" (for Removed).  This way deleted rows can easily be identified without destroying the current structure. Perhaps we need a setting "visible" for rows in a data model? The only thing that is debatable then is the number of rows in a data model. It should return with the number of rows minus deleted ones, but if we do that then chances are not all rows are processed that should be processed. Better to skip row by testing on the id (gda_row_get_id). A 2nd type of row return perhaps? Suggestions?

Please let me know if this implementation is acceptable. If not, let's discuss / advise how it otherwise should work.

Also any tips/advise how to deliver patches (ie  which diff command from where) to this list are appreciated.

Thanks,
Bas.







--- providers/postgres/gda-postgres-recordset.c.orig	2005-01-06 19:19:12.380528899 +1000
+++ providers/postgres/gda-postgres-recordset.c	2005-01-06 21:19:35.531393199 +1000
@@ -57,6 +57,7 @@
 static gint gda_postgres_recordset_get_n_rows 		      (GdaDataModel *model);
 static const GdaRow *gda_postgres_recordset_get_row 	      (GdaDataModel *model, gint rownum);
 static const GdaRow *gda_postgres_recordset_append_row        (GdaDataModel *model, const GList *values);
+static gboolean gda_postgres_recordset_remove_row 	      (GdaDataModel *model, const GdaRow *row);
 static gboolean gda_postgres_recordset_update_row 	      (GdaDataModel *model, const GdaRow *row);
 
 static GObjectClass *parent_class = NULL;
@@ -91,6 +92,7 @@
 	model_class->get_value_at = gda_postgres_recordset_get_value_at;
 	model_class->get_row = gda_postgres_recordset_get_row;
 	model_class->append_row = gda_postgres_recordset_append_row;
+	model_class->remove_row = gda_postgres_recordset_remove_row;
 	model_class->update_row = gda_postgres_recordset_update_row;
 }
 
@@ -317,6 +319,113 @@
 }
 
 static gboolean
+gda_postgres_recordset_remove_row (GdaDataModel *model, const GdaRow *row)
+{
+	GdaPostgresRecordset *recset = (GdaPostgresRecordset *) model;
+	GdaPostgresRecordsetPrivate *priv_data;
+	gint colnum, uk, rm_rownum, i;
+	PGresult *pg_res, *pg_rm_res;
+	gchar *query, *query_where, *tmp;
+	GdaPostgresConnectionData *cnc_priv_data;
+	PGconn *pg_conn;
+	gboolean status = FALSE;
+	GdaRow *rm_row;
+	GdaValue *value;
+
+	g_return_val_if_fail (GDA_IS_POSTGRES_RECORDSET (recset), FALSE);
+	g_return_val_if_fail (recset->priv != NULL, FALSE);
+	g_return_val_if_fail (row != NULL, FALSE);
+
+	priv_data = recset->priv;
+	pg_res = priv_data->pg_res;
+	cnc_priv_data = g_object_get_data (G_OBJECT (priv_data->cnc),
+					   OBJECT_DATA_POSTGRES_HANDLE);
+	pg_conn = cnc_priv_data->pconn;
+
+	/* checks if the given row belongs to the given model */
+	if (gda_row_get_model ((GdaRow *) row) != model) {
+		gda_connection_add_error_string (priv_data->cnc,
+						_("Given row doesn't belong to the model."));
+		return FALSE;
+	}
+
+	/* checks if the table name has been guessed */
+	if (priv_data->table_name == NULL) {
+		gda_connection_add_error_string (priv_data->cnc,
+						_("Table name could not be guessed."));
+		return FALSE;
+	}
+
+	query_where = g_strdup ("WHERE TRUE ");
+
+	for (colnum = uk = 0;
+	     colnum != gda_data_model_get_n_columns (model);
+	     colnum++)
+	{
+		GdaFieldAttributes *attrs = gda_data_model_describe_column (model, colnum);
+		const gchar *column_name = PQfname (pg_res, colnum);
+		gchar *curval = gda_value_stringify (gda_row_get_value ((GdaRow *) row, colnum));
+
+		/* unique column: we will use it as an index */
+		if (gda_field_attributes_get_primary_key (attrs) ||
+		    gda_field_attributes_get_unique_key (attrs))
+		{
+			/* fills the 'where' part of the update command */
+			tmp = g_strdup_printf ("AND %s = '%s' ",
+					       column_name,
+					       curval);
+			query_where = g_strconcat (query_where, tmp, NULL);
+			g_free (tmp);
+			uk++;
+		}
+
+		g_free (curval);
+		gda_field_attributes_free (attrs);
+	}
+
+	if (uk == 0) {
+		gda_connection_add_error_string (priv_data->cnc,
+						_("Model doesn't have at least one unique key."));
+	}
+	else {
+		/* build the delete command */
+		query = g_strdup_printf ("DELETE FROM %s %s",
+					 priv_data->table_name,
+					 query_where);
+
+		/* remove the row */
+		pg_rm_res = PQexec (pg_conn, query);
+		g_free (query);
+
+		if (pg_rm_res != NULL) {
+			/* removal ok! */
+			if (PQresultStatus (pg_rm_res) == PGRES_COMMAND_OK)
+				status = TRUE;
+			else
+				gda_connection_add_error (priv_data->cnc,
+					 		  gda_postgres_make_error (pg_conn, pg_rm_res));
+			PQclear (pg_rm_res);
+		}
+		else
+			gda_connection_add_error (priv_data->cnc,
+						  gda_postgres_make_error (pg_conn, NULL));
+	}
+
+	g_free (query_where);
+
+        /* remove from data model by removing the data from the row and unsetting row id */
+	if (status == TRUE) {
+		for (i=0; i<gda_data_model_get_n_columns (model); i++) {
+			value = (GdaValue *)gda_row_get_value((GdaRow *)row, i);
+			gda_value_set_string (value, "NULL");
+		}
+		gda_row_set_id ((GdaRow *)row, "R");
+	}
+
+        return status;
+}
+
+static gboolean
 gda_postgres_recordset_update_row (GdaDataModel *model, const GdaRow *row)
 {
 	GdaPostgresRecordset *recset = (GdaPostgresRecordset *) model;