GNOME.org
 

[libgda] GdaBrowser: use a combo box to select related data when inserting data

commit 2f1609f8a32bd4b9ff07a7230c839227dff4e6bb
Author: Vivien Malerba <malerba gnome-db org>
Date:   Wed Jul 28 17:37:44 2010 +0200

    GdaBrowser: use a combo box to select related data when inserting data

 tools/browser/help/C/figures/table-insert-data.png |  Bin 0 -> 23450 bytes
 tools/browser/help/C/table-insert-data.page        |   44 +++
 tools/browser/help/Makefile.am                     |    4 +-
 .../schema-browser/schema-browser-perspective.c    |    4 -
 tools/browser/schema-browser/table-info.c          |  288 ++++++++++++++++++--
 5 files changed, 315 insertions(+), 25 deletions(-)
---
diff --git a/tools/browser/help/C/figures/table-insert-data.png b/tools/browser/help/C/figures/table-insert-data.png
new file mode 100644
index 0000000..fc91ff1
Binary files /dev/null and b/tools/browser/help/C/figures/table-insert-data.png differ
diff --git a/tools/browser/help/C/table-insert-data.page b/tools/browser/help/C/table-insert-data.page
new file mode 100644
index 0000000..abd588a
--- /dev/null
+++ b/tools/browser/help/C/table-insert-data.page
@@ -0,0 +1,44 @@
+<page xmlns="http://projectmallard.org/1.0/";
+      type="topic"
+      id="table-insert-data">
+  <info>
+    <title type="sort">1</title>
+    <link type="topic" xref="schema-browser-perspective"/>
+  </info>
+  <title>Inserting data in a table</title>
+  <p>
+    From the <link xref="schema-browser-perspective">schema browser perspective</link>, when a table's
+    properties is displayed in a tab, use the <guiseq><gui>Table</gui><gui>Insert data</gui></guiseq> menu
+    to have a popup appear containing a data entry for each column of the table to insert data into.
+  </p>
+  <p>
+    A popup similar to the following will appear:
+  </p>
+  <figure>
+    <title>Popup to insert data into table</title>
+    <desc>A popup to insert data in a table</desc>
+    <media type="image" mime="image/png" src="figures/table-insert-data.png"/>
+  </figure>
+  <p>
+    The noticeable points here are:
+  </p>
+  <list>
+    <item><p>There is one data entry per column in the table, with the exception (not shown in this
+	figure) of multiple foreign key fields for which a single drop down choice widget is shown;</p></item>
+    <item><p>Each data entry has a small button on the right giving information about the corresponding
+	data entry, and allowing you to reset the data entry's value to the value it had before any
+	modification done, to set it to NULL or to set it to a default value. The button's color has the
+	following signification:</p>
+	<list>
+	  <item><p>Red: means the current value in the data entry is invalid</p></item>
+	  <item><p>Blue: means the current value in the data entry is unused and the column's default value
+	      will be used instead (usefull for example for auto incremented columns)</p></item>
+	  <item><p>Green: means the current value in the data entry is NULL</p></item>
+	  <item><p>Same color as the rest of the UI: means none of the above situations apply</p></item>
+	</list>
+    </item>
+    <item><p>Columns which are foreign keys to other tables are presented as a drop down combo box where
+	you can only choose among the values in the referenced table (hint: to have that values list
+	refresned you should close the popup dialog and reopen it);</p></item>
+  </list>
+</page>
diff --git a/tools/browser/help/Makefile.am b/tools/browser/help/Makefile.am
index 3f307d8..6ba449d 100644
--- a/tools/browser/help/Makefile.am
+++ b/tools/browser/help/Makefile.am
@@ -13,7 +13,8 @@ DOC_FIGURES = \
 	figures/query-exec-persp.png \
 	figures/trans-begin.png \
 	figures/trans-commit.png \
-	figures/trans-rollback.png
+	figures/trans-rollback.png \
+	figures/table-insert-data.png
 
 DOC_PAGES = \
 	data-manager-perspective.page \
@@ -26,6 +27,7 @@ DOC_PAGES = \
 	query-execution-perspective.page \
 	schema-browser-perspective.page \
 	transactions.page \
+	table-insert-data.page \
 	variables.page
 
 DOC_LINGUAS = de es
diff --git a/tools/browser/schema-browser/schema-browser-perspective.c b/tools/browser/schema-browser/schema-browser-perspective.c
index f9570b8..d2c59ba 100644
--- a/tools/browser/schema-browser/schema-browser-perspective.c
+++ b/tools/browser/schema-browser/schema-browser-perspective.c
@@ -307,10 +307,6 @@ static const gchar *ui_actions_info =
         "      </menu>"
         "    </placeholder>"
         "  </menubar>"
-        "  <toolbar name='ToolBar'>"
-        "    <separator/>"
-        "    <toolitem action='NewDiagram'/>"
-        "  </toolbar>"
         "</ui>";
 
 static GtkActionGroup *
diff --git a/tools/browser/schema-browser/table-info.c b/tools/browser/schema-browser/table-info.c
index ddf6693..1a9416f 100644
--- a/tools/browser/schema-browser/table-info.c
+++ b/tools/browser/schema-browser/table-info.c
@@ -39,6 +39,7 @@
 #include <libgda-ui/gdaui-enums.h>
 #include <libgda-ui/gdaui-basic-form.h>
 #include "../common/popup-container.h"
+#include <libgda/gda-data-model-extra.h>
 
 struct _TableInfoPrivate {
 	BrowserConnection *bcnc;
@@ -53,6 +54,8 @@ struct _TableInfoPrivate {
 	GtkWidget *pages; /* notebook to store individual pages */
 
 	GtkWidget *insert_popup;
+	GHashTable *insert_columns_hash; /* key = column index as a pointer, value = GdaHolder in the
+					 * params used in the INSERT statement */
 };
 
 static void table_info_class_init (TableInfoClass *klass);
@@ -127,6 +130,8 @@ table_info_dispose (GObject *object)
 
 	/* free memory */
 	if (tinfo->priv) {
+		if (tinfo->priv->insert_columns_hash)
+			g_hash_table_destroy (tinfo->priv->insert_columns_hash);
 		if (tinfo->priv->insert_popup)
 			gtk_widget_destroy (tinfo->priv->insert_popup);
 		g_free (tinfo->priv->schema);
@@ -266,6 +271,15 @@ meta_changed_cb (BrowserConnection *bcnc, GdaMetaStruct *mstruct, TableInfo *tin
 	GdaMetaDbObject *dbo;
 	GValue *schema_v = NULL, *name_v;
 
+	if (tinfo->priv->insert_columns_hash) {
+		g_hash_table_destroy (tinfo->priv->insert_columns_hash);
+		tinfo->priv->insert_columns_hash = NULL;
+	}
+	if (tinfo->priv->insert_popup) {
+		gtk_widget_destroy (tinfo->priv->insert_popup);
+		tinfo->priv->insert_popup = NULL;
+	}
+
 	g_value_set_string ((schema_v = gda_value_new (G_TYPE_STRING)), tinfo->priv->schema);
 	g_value_set_string ((name_v = gda_value_new (G_TYPE_STRING)), tinfo->priv->table_name);
 	dbo = gda_meta_struct_get_db_object (mstruct, NULL, schema_v, name_v);
@@ -540,8 +554,86 @@ insert_response_cb (GtkWidget *dialog, gint response_id, TableInfo *tinfo)
 					    lerror && lerror->message ? lerror->message : _("No detail"));
 			g_clear_error (&lerror);
 		}
+		gtk_widget_hide (dialog);
+	}
+#ifdef HAVE_GDU
+	else if (response_id == GTK_RESPONSE_HELP) {
+		browser_show_help ((GtkWindow*) gtk_widget_get_toplevel ((GtkWidget*) tinfo),
+				   "table-insert-data");
+	}
+#endif
+	else
+		gtk_widget_hide (dialog);
+}
+
+typedef struct {
+	gint          cols_nb;
+	gint         *fk_cols_array;
+	GdaSet       *insert_params;
+	GHashTable   *chash;
+	GdaStatement *stmt;
+	GdaDataModel *model;
+	gboolean      model_rerunning;
+} FKBindData;
+
+static void
+fk_bind_select_executed_cb (BrowserConnection *bcnc,
+			    guint exec_id,
+			    GObject *out_result,
+			    GdaSet *out_last_inserted_row, GError *error,
+			    FKBindData *fkdata)
+{
+	gint i;
+	GdaDataModel *model;
+	if (! out_result)
+		return;
+
+	if (fkdata->model)
+		g_object_unref (fkdata->model);
+
+	model = GDA_DATA_MODEL (out_result);
+	for (i = 0; i < fkdata->cols_nb; i++) {
+		GdaHolder *h;
+		GdaSetSource *source;
+		h = g_hash_table_lookup (fkdata->chash,
+					 GINT_TO_POINTER (fkdata->fk_cols_array [i] - 1));
+		source = gda_set_get_source (fkdata->insert_params, h);
+		if (source && gda_holder_get_source_model (h, NULL)) {
+			gda_set_replace_source_model (fkdata->insert_params, source,
+						      model);
+			/* break now as gda_set_replace_source_model() does the job of replacing
+			 * the data model for all the holders which share the same data model */
+			break;
+		}
+		else {
+#ifdef GDA_DEBUG_NO
+			if (gda_holder_set_source_model (h, model, i, NULL))
+				g_print ("Bound holder [%s] to column %d for model %p\n", gda_holder_get_id (h), i, model);
+			else
+				g_print ("Could not bind holder [%s] to column %d\n", gda_holder_get_id (h), i);
+#else
+			gda_holder_set_source_model (h, model, i, NULL);
+#endif
+		}
+	}
+	fkdata->model = g_object_ref (out_result);
+	fkdata->model_rerunning = FALSE;
+}
+
+static void
+fkdata_list_free (GSList *fkdata_list)
+{
+	GSList *list;
+	for (list = fkdata_list; list; list = list->next) {
+		FKBindData *fkdata = (FKBindData*) list->data;
+		g_free (fkdata->fk_cols_array);
+		g_object_unref (fkdata->insert_params);
+		g_object_unref (fkdata->stmt);
+		if (fkdata->model)
+			g_object_unref (fkdata->model);
+		g_free (fkdata);
 	}
-	gtk_widget_hide (dialog);
+	g_slist_free (fkdata_list);
 }
 
 static void
@@ -553,6 +645,21 @@ action_insert_cb (GtkAction *action, TableInfo *tinfo)
 
 	if (tinfo->priv->insert_popup) {
 		gtk_widget_show (tinfo->priv->insert_popup);
+		GSList *fkdata_list;
+		
+		for (fkdata_list = g_object_get_data (G_OBJECT (tinfo->priv->insert_popup), "fkdata_list");
+		     fkdata_list; fkdata_list = fkdata_list->next) {
+			FKBindData *fkdata = (FKBindData *) fkdata_list->data;
+			if (fkdata->model && !fkdata->model_rerunning) {
+				if (browser_connection_execute_statement_cb (tinfo->priv->bcnc,
+									     fkdata->stmt, NULL,
+									     GDA_STATEMENT_MODEL_RANDOM_ACCESS,
+									     FALSE,
+									     (BrowserConnectionExecuteCallback) fk_bind_select_executed_cb,
+									     fkdata, NULL) != 0)
+					fkdata->model_rerunning = TRUE;
+			}
+		}
 		return;
 	}
 
@@ -600,10 +707,12 @@ action_insert_cb (GtkAction *action, TableInfo *tinfo)
 	GdaStatement *stmt;
 	stmt = gda_sql_builder_get_statement (b, NULL);
 	g_object_unref (b);
+#ifdef GDA_DEBUG_NO
 	gchar *sql;
 	sql = gda_statement_to_sql (stmt, NULL, NULL);
 	g_print ("[%s]\n", sql);
 	g_free (sql);
+#endif
 
 	/* handle user preferences */
 	GdaSet *params;
@@ -617,47 +726,185 @@ action_insert_cb (GtkAction *action, TableInfo *tinfo)
 		g_object_unref (stmt);
 		return;
 	}
-	for (list = mtable->columns; list; list = list->next) {
+	GSList *fkdata_list = NULL;
+	gint nthcol;
+	if (mtable->fk_list)
+		tinfo->priv->insert_columns_hash = g_hash_table_new_full (NULL, NULL, NULL, g_object_unref);
+
+	for (nthcol = 0, list = mtable->columns; list; nthcol++, list = list->next) {
 		GdaMetaTableColumn *col = (GdaMetaTableColumn*) list->data;
 		gchar *plugin;
+		const GValue *autoinc;
+		GdaHolder *holder;
+
 		plugin = browser_connection_get_table_column_attribute (tinfo->priv->bcnc,
 									mtable,	col,
 									BROWSER_CONNECTION_COLUMN_PLUGIN,
 									NULL);
-		if (!plugin && !col->default_value)
+		holder = gda_set_get_holder (params, col->column_name);
+		if (!holder)
 			continue;
+
+		autoinc = gda_meta_table_column_get_attribute (col, GDA_ATTRIBUTE_AUTO_INCREMENT);
+		if (tinfo->priv->insert_columns_hash)
+			g_hash_table_insert (tinfo->priv->insert_columns_hash, GINT_TO_POINTER (nthcol), g_object_ref (holder));
 		
-		GdaHolder *holder;
-		holder = gda_set_get_holder (params, col->column_name);
-		if (holder) {
-			if (plugin) {
-				GValue *value;
-				value = gda_value_new_from_string (plugin, G_TYPE_STRING);
-				gda_holder_set_attribute_static (holder, GDAUI_ATTRIBUTE_PLUGIN, value);
-				gda_value_free (value);
+		if (!plugin && !col->default_value && !autoinc)
+			continue;
+		if (plugin) {
+			GValue *value;
+			value = gda_value_new_from_string (plugin, G_TYPE_STRING);
+			gda_holder_set_attribute_static (holder, GDAUI_ATTRIBUTE_PLUGIN, value);
+			gda_value_free (value);
+		}
+		
+		if (col->default_value) {
+			GValue *dv;
+			//g_value_set_string ((dv = gda_value_new (G_TYPE_STRING)), col->default_value);
+			dv = gda_value_new_null ();
+			gda_holder_set_default_value (holder, dv);
+			gda_value_free (dv);
+			gda_holder_set_value_to_default (holder);
+
+			gchar *tmp;
+			tmp = g_strdup_printf (_("Default value: '%s'"), col->default_value);
+			g_object_set (holder, "description", tmp, NULL);
+			g_free (tmp);
+		}
+		else if (autoinc) {
+			GValue *dv;
+			g_value_set_string ((dv = gda_value_new (G_TYPE_STRING)), "");
+			gda_holder_set_default_value (holder, dv);
+			gda_value_free (dv);
+			gda_holder_set_value_to_default (holder);
+			g_object_set (holder, "description", _("Default value: auto incremented value"), NULL);
+		}
+
+		g_free (plugin);
+	}
+
+	/* analyse FK list to propose values to choose from */
+	for (list = mtable->fk_list; list; list = list->next) {
+		GdaMetaTableForeignKey *fk = (GdaMetaTableForeignKey*) list->data;
+		if (fk->depend_on == dbo) /* don't link to itself */
+			continue;
+		else if (fk->depend_on->obj_type != GDA_META_DB_TABLE)
+			continue;
+		GdaMetaDbObject *rdbo = (GdaMetaDbObject*) fk->depend_on;
+		GdaDataModel *cmodel;
+		GValue *schema_v, *name_v, *catalog_v;
+		GError *lerror = NULL;
+		
+		g_value_set_string ((catalog_v = gda_value_new (G_TYPE_STRING)), rdbo->obj_catalog);
+		g_value_set_string ((schema_v = gda_value_new (G_TYPE_STRING)), rdbo->obj_schema);
+		g_value_set_string ((name_v = gda_value_new (G_TYPE_STRING)), rdbo->obj_name);
+		dbo = gda_meta_struct_get_db_object (mstruct, NULL, schema_v, name_v);
+
+		cmodel = gda_meta_store_extract (browser_connection_get_meta_store (tinfo->priv->bcnc),
+						"SELECT tc.constraint_name, k.column_name FROM _key_column_usage k INNER JOIN _table_constraints tc ON (k.table_catalog=tc.table_catalog AND k.table_schema=tc.table_schema AND k.table_name=tc.table_name AND k.constraint_name=tc.constraint_name) WHERE tc.constraint_type='UNIQUE' AND k.table_catalog = ##catalog::string AND k.table_schema = ##schema::string AND k.table_name = ##tname::string ORDER by k.ordinal_position", &lerror,
+						"catalog", catalog_v,
+						"schema", schema_v,
+						"tname", name_v, NULL);
+
+		gda_value_free (catalog_v);
+		gda_value_free (schema_v);
+		gda_value_free (name_v);
+
+		GdaSqlBuilder *b = NULL;
+		if (cmodel) {
+			gint nrows, i;
+			nrows = gda_data_model_get_n_rows (cmodel);
+			b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+			gda_sql_builder_select_add_target_id (b,
+							      gda_sql_builder_add_id (b, rdbo->obj_short_name),
+							      NULL);
+			/* add REF PK fields */
+			for (i = 0; i < fk->cols_nb; i++) {
+				gda_sql_builder_select_add_field (b, fk->ref_pk_names_array [i], NULL, NULL);
 			}
 
-			if (col->default_value) {
-				GValue *dv;
-				g_value_set_string ((dv = gda_value_new (G_TYPE_STRING)), col->default_value);
-				gda_holder_set_default_value (holder, dv);
-				gda_value_free (dv);
-				gda_holder_set_value_to_default (holder);
+			/* add UNIQUE fields */
+			for (i = 0; i < nrows; i++) {
+				const GValue *cvalue;
+				cvalue = gda_data_model_get_value_at (cmodel, 1, i, NULL);
+				if (!cvalue || (G_VALUE_TYPE (cvalue) != G_TYPE_STRING))
+					break;
+				gda_sql_builder_select_add_field (b, g_value_get_string (cvalue), NULL, NULL);
+				gda_sql_builder_select_order_by (b,
+								 gda_sql_builder_add_id (b, g_value_get_string (cvalue)),
+								 TRUE, NULL);
 			}
+			if (i < nrows) {
+				/* error  */
+				g_object_unref (b);
+				b = NULL;
+			}
+			/*gda_data_model_dump (cmodel, NULL);*/
+			g_object_unref (cmodel);
+		}
+		else {
+			g_warning ("Can't get list of unique constraints for table '%s': %s",
+				   rdbo->obj_short_name,
+				   lerror && lerror->message ? lerror->message : _("No detail"));
+			g_clear_error (&lerror);
 		}
 
-		g_free (plugin);
+		if (b) {
+			GdaStatement *stmt;
+			stmt = gda_sql_builder_get_statement (b, NULL);
+			if (stmt) {
+#ifdef GDA_DEBUG_NO
+				gchar *sql;
+				sql = gda_statement_to_sql (stmt, NULL, NULL);
+				g_print ("UNIQUE SELECT [%s]\n", sql);
+				g_free (sql);
+#endif
+
+				FKBindData *fkdata;
+				guint eid;
+				fkdata = g_new0 (FKBindData, 1);
+				fkdata->cols_nb = fk->cols_nb;
+				fkdata->fk_cols_array = g_new (gint, fk->cols_nb);
+				memcpy (fkdata->fk_cols_array, fk->fk_cols_array, sizeof (gint) * fk->cols_nb);
+				fkdata->chash = tinfo->priv->insert_columns_hash;
+				fkdata->stmt = stmt;
+				eid = browser_connection_execute_statement_cb (tinfo->priv->bcnc, stmt, NULL,
+									       GDA_STATEMENT_MODEL_RANDOM_ACCESS,
+									       FALSE,
+									       (BrowserConnectionExecuteCallback) fk_bind_select_executed_cb,
+									       fkdata, NULL);
+				if (! eid) {
+					g_free (fkdata->fk_cols_array);
+					g_object_unref (fkdata->stmt);
+					g_free (fkdata);
+				}
+				fkdata->insert_params = g_object_ref (params);
+
+				/* attach the kfdata to @popup to be able to re-run the SELECT
+				 * everytime the window is shown */
+				fkdata_list = g_slist_prepend (fkdata_list, fkdata);
+			}
+
+			g_object_unref (b);
+		}
 	}
 
 	/* create popup */
 	GtkWidget *popup;
 	popup = gtk_dialog_new_with_buttons (_("Values to insert into table"), GTK_WINDOW (bwin),
-					     0, GTK_STOCK_EXECUTE, GTK_RESPONSE_ACCEPT,
+					     0,
+#ifdef HAVE_GDU
+					     GTK_STOCK_HELP, GTK_RESPONSE_HELP,
+#endif
+					     GTK_STOCK_EXECUTE, GTK_RESPONSE_ACCEPT,
 					     GTK_STOCK_CANCEL, GTK_RESPONSE_REJECT,
 					     NULL);
 	tinfo->priv->insert_popup = popup;
 	g_object_set_data_full (G_OBJECT (popup), "stmt", stmt, g_object_unref);
 	g_object_set_data_full (G_OBJECT (popup), "params", params, g_object_unref);
+	if (fkdata_list)
+		g_object_set_data_full (G_OBJECT (popup), "fkdata_list",
+					fkdata_list, (GDestroyNotify) fkdata_list_free);
 
 	g_signal_connect (popup, "close",
 			  G_CALLBACK (gtk_widget_hide), NULL);
@@ -696,11 +943,12 @@ action_insert_cb (GtkAction *action, TableInfo *tinfo)
 	gtk_widget_show_all (popup);
 }
 
+
 static GtkActionEntry ui_actions[] = {
 	{ "Table", NULL, "_Table", NULL, "Table", NULL },
 	{ "AddToFav", STOCK_ADD_BOOKMARK, N_("Add to _Favorites"), NULL, N_("Add table to favorites"),
 	  G_CALLBACK (action_add_to_fav_cb)},
-	{ "ViewContents", GTK_STOCK_EDIT, N_("_Contents"), NULL, N_("View contents"),
+	{ "ViewContents", GTK_STOCK_EDIT, N_("_Contents"), NULL, N_("View table's contents"),
 	  G_CALLBACK (action_view_contents_cb)},
 	{ "InsertData", GTK_STOCK_ADD, N_("_Insert data"), NULL, N_("Insert data into table"),
 	  G_CALLBACK (action_insert_cb)},
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]