[gnome-db] gda-postgres: updatable data models

[Laurent Sansonetti <laurent datarescue be>]

Hi all,

I hacked a bit the PostgreSQL provider to make data models updatable
(see the attached patch).

This patch provides implementation for the gda_data_model_update_row(),
as well as information for "unique_key", "primary_key" and "table" for
field attributes (which were needed to build the UPDATE command).

The update process will fail if:
  - the table name from which the model was issued could not be
'guessed' [1]
  - the model does not have at least one unique key
  - all unique keys in the model have been modified
  - the model does not have at least one non-unique key to update

I tested it locally and it seems to work well, with a very simple test
case.  I'm going to create a more complex test case, and send it here as
well, so you will be able to easily test it.

Please tell me what you think ;-)  

-- 
{ "Laurent" => "http://lrz.samika.net"; }

[1]: This is not very easy to determine this.  Currently, the guess
algorithm searches for a 'single' table which owns all column names of
the data model.  By single I mean that if it finds 2 or more tables, it
will fail.  

So it won't work on complex queries which involve several tables in the
same time (like SELECT ... FROM A, B, C), or in very restrictive queries
(SELECT field FROM A) which can confuse the guess algorithm.

Index: gda-postgres-recordset.c
===================================================================
RCS file: /cvs/gnome/libgda/providers/postgres/gda-postgres-recordset.c,v
retrieving revision 1.28
diff -r1.28 gda-postgres-recordset.c
40a41
> 	gchar *table_name;
57a59
> static gboolean gda_postgres_recordset_update_row 	      (GdaDataModel *model, const GdaRow *row);
89a92
> 	model_class->update_row = gda_postgres_recordset_update_row;
152a156
> 	gda_row_set_number (row, rownum);
195c199
< 					 row, row_list);
---
> 					row, row_list);
199a204,324
> static gboolean
> gda_postgres_recordset_update_row (GdaDataModel *model, const GdaRow *row)
> {
> 	GdaPostgresRecordset *recset = (GdaPostgresRecordset *) model;
> 	GdaPostgresRecordsetPrivate *priv_data;
> 	gint colnum, uk, nuk;
> 	PGresult *pg_res, *pg_upd_res;
> 	gchar *query, *query_where, *query_set, *tmp;
> 	GdaPostgresConnectionData *cnc_priv_data;
> 	PGconn *pg_conn;
> 	gboolean status = FALSE;
> 
> 	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 ");
> 	query_set = g_strdup ("SET ");
> 
> 	for (colnum = uk = nuk = 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);
> 		const gchar *newval = gda_value_stringify (gda_row_get_value ((GdaRow *) row, colnum));
> 		const gchar *oldval = PQgetvalue (pg_res, gda_row_get_number ((GdaRow *) row), colnum);
> 
> 		/* unique column: we won't update it, but we will use it as
> 		   an index */
> 		if (gda_field_attributes_get_primary_key (attrs) ||
> 		    gda_field_attributes_get_unique_key (attrs)) 
> 		{
> 			/* checks if it hasn't be modified anyway */
> 			if (oldval == NULL ||
> 	   		    newval == NULL ||
> 			    strcmp (oldval, newval) != 0)
> 			    	continue;
> 
> 			/* fills the 'where' part of the update command */
> 			tmp = g_strdup_printf ("AND %s = '%s' ",
> 					       column_name,
> 					       newval);
> 			query_where = g_strconcat (query_where, tmp, NULL);
> 			g_free (tmp);
> 			uk++;
> 		}
> 		/* non-unique column: update it */
> 		else {
> 			/* fills the 'set' part of the update command */
> 			tmp = g_strdup_printf ("%s = '%s', ", 
> 					       column_name,
> 					       newval);
> 			query_set = g_strconcat (query_set, tmp, NULL);
> 			g_free (tmp);
> 			nuk++;
> 		}
> 
> 		gda_field_attributes_free (attrs);
> 	}
> 
> 	if (uk == 0) {
> 		gda_connection_add_error_string (priv_data->cnc,
> 						_("Model doesn't have at least one non-modified unique key."));
> 	}
> 	else if (nuk == 0) {
> 		gda_connection_add_error_string (priv_data->cnc,
> 						_("Model doesn't have any non-unique values to update."));
> 	}
> 	else {
> 		/* remove the last ',' in the SET part */
> 		tmp = strrchr (query_set, ',');
> 		if (tmp != NULL)
> 			*tmp = ' ';
> 		
> 		/* build the update command */
> 		query = g_strdup_printf ("UPDATE %s ", priv_data->table_name);
> 		query = g_strconcat (query, query_set, query_where, NULL);
> 	
> 		/* update the row */
> 		pg_upd_res = PQexec (pg_conn, query);
> 		g_free (query);
> 	
> 		if (pg_upd_res != NULL) {
> 			/* update ok! */
> 			if (PQresultStatus (pg_upd_res) == PGRES_COMMAND_OK)
> 				status = TRUE;
> 			else
> 				gda_connection_add_error (priv_data->cnc,
> 			        	                  gda_postgres_make_error (pg_conn, pg_res));
> 			PQclear (pg_upd_res);
> 		}
> 		else
> 			gda_connection_add_error (priv_data->cnc,
> 			       	                  gda_postgres_make_error (pg_conn, NULL));
> 	}
> 	
> 	g_free (query_set);
> 	g_free (query_where);
> 	return status;
> }
> 
244a370,448
> /* Try to guess the table name involved in the given data model. 
>  * It can fail on complicated queries, where several tables are
>  * involved in the same time.
>  */
> static gchar *guess_table_name (GdaPostgresRecordset *recset)
> {
> 	GdaPostgresConnectionData *cnc_priv_data;
> 	PGresult *pg_res, *pg_name_res;
> 	PGconn *pg_conn;
> 	gchar *table_name = NULL;
> 	
> 	pg_res = recset->priv->pg_res;
> 	cnc_priv_data = g_object_get_data (G_OBJECT (recset->priv->cnc),
> 					   OBJECT_DATA_POSTGRES_HANDLE);
> 	pg_conn = cnc_priv_data->pconn;
> 
> 	if (PQnfields (pg_res) > 0) {
> 		gchar *query = g_strdup_printf ("SELECT c.relname FROM pg_catalog.pg_class c WHERE c.relkind = 'r'");
> 		gint i;
> 		for (i = 0; i < PQnfields (pg_res); i++) {
> 			const gchar *column_name = PQfname (pg_res, i);
> 			gchar *cond = g_strdup_printf (" AND '%s' IN (SELECT a.attname FROM pg_catalog.pg_attribute a WHERE a.attrelid = c.oid)", column_name);
> 			query = g_strconcat (query, cond, NULL);
> 			g_free (cond);
> 		}
> 		pg_name_res = PQexec (pg_conn, query);
> 		if (pg_name_res != NULL) {
> 			if (PQntuples (pg_name_res) == 1)
> 				table_name = PQgetvalue (pg_name_res, 0, 0);
> 			PQclear (pg_name_res);
> 		}
> 		g_free (query);
> 	}
> 	return table_name != NULL ? g_strdup (table_name) : NULL;
> }
> 
> /* Checks if the given column number of the given data model
>  * has the given constraint.
>  *
>  * contype may be one of the following:
>  *    'f': foreign key
>  *    'p': primary key
>  *    'u': unique key
>  *    etc...
>  */
> static gboolean check_constraint (const GdaDataModel *model,
> 				  const gchar *table_name,
> 				  const gint col,
> 				  const gchar contype)
> {
> 	GdaPostgresRecordset *recset = (GdaPostgresRecordset *) model;
> 	GdaPostgresRecordsetPrivate *priv_data;
> 	GdaPostgresConnectionData *cnc_priv_data;
> 	PGresult *pg_res, *pg_con_res;
> 	gchar *column_name;
> 	gchar *query;
> 	gboolean state = FALSE;
> 
> 	g_return_val_if_fail (table_name != 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);
> 
> 	column_name = PQfname (pg_res, col);
> 	if (column_name != NULL) {
> 		query = g_strdup_printf ("SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = '%s' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = '%c' and c2.conkey[1] = a.attnum and a.attname = '%s'", table_name, contype, column_name);
> 		pg_con_res = PQexec (cnc_priv_data->pconn, query);
> 		if (pg_con_res != NULL) {
> 			state = PQntuples (pg_con_res) == 1;
> 			PQclear (pg_con_res);
> 		}
> 		g_free (query);
> 	}
> 
> 	return state;
> }
> 
253a458
> 	gboolean ispk, isuk;
266c471
< 	if (col >= priv_data->ncolumns){
---
> 	if (col >= priv_data->ncolumns) {
286c491,493
< 	gda_field_attributes_set_defined_size (field_attrs, PQfsize (pg_res, col));
---
> 	gda_field_attributes_set_defined_size (field_attrs,
> 					       PQfsize (pg_res, col));
> 					       
288,289c495,509
< 	gda_field_attributes_set_primary_key (field_attrs, FALSE);
< 	gda_field_attributes_set_unique_key (field_attrs, FALSE);
---
> 
> 	gda_field_attributes_set_table (field_attrs,
> 					priv_data->table_name);
> 
> 	ispk = check_constraint (model,
> 				 priv_data->table_name,
> 				 col, 
> 				 'p');
> 	isuk = check_constraint (model, 
> 				 priv_data->table_name,
> 				 col, 
> 				 'u');
> 	
> 	gda_field_attributes_set_primary_key (field_attrs, ispk);
> 	gda_field_attributes_set_unique_key (field_attrs, isuk);
364c584
< 
---
> 	model->priv->table_name = guess_table_name (model);