From 12ac971429417f0acdf23288359da26f6ab1798d Mon Sep 17 00:00:00 2001 From: Andrea Zagli Date: Mon, 6 Jun 2011 13:37:56 +0200 Subject: [PATCH] Function "add all fields and tables" completed. Added checking if fields exist. Added removing of tables and fields that doesn't exist anymore in db. --- data/audit-gui/gui/audit-gui.gui | 2 +- src/datasources.c | 251 +++++++++++++++++++++---------- 2 files changed, 169 insertions(+), 84 deletions(-) diff --git a/data/audit-gui/gui/audit-gui.gui b/data/audit-gui/gui/audit-gui.gui index af5329b..f582081 100644 --- a/data/audit-gui/gui/audit-gui.gui +++ b/data/audit-gui/gui/audit-gui.gui @@ -70,7 +70,7 @@ True - Add _all tables + Add _all tables and fields True diff --git a/src/datasources.c b/src/datasources.c index fb7d570..831e5c3 100644 --- a/src/datasources.c +++ b/src/datasources.c @@ -17,6 +17,8 @@ * */ +#include + #include #include "datasources.h" @@ -243,6 +245,9 @@ datasources_add_all_tables (Datasources *datasources) gboolean is_key; guint id_field; + gchar *ids_tables_saved; + gchar *ids_fields_saved; + GdaMetaContext mcontext = {"_columns", 1, NULL, NULL}; g_return_if_fail (IS_DATASOURCES (datasources)); @@ -293,10 +298,10 @@ datasources_add_all_tables (Datasources *datasources) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - "Unable to update the metastore: %s", + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to update the metastore: %s", error != NULL && error->message != NULL ? error->message : "no details"); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); @@ -319,21 +324,24 @@ datasources_add_all_tables (Datasources *datasources) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - priv->error_update_metastore); + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + priv->error_update_metastore); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); return; } + ids_tables_saved = g_strdup (""); + ids_fields_saved = g_strdup (""); + /* updating tables */ error = NULL; dm_tables = gda_connection_get_meta_store_data (priv->gdacon, - GDA_CONNECTION_META_TABLES, - &error, - 0); + GDA_CONNECTION_META_TABLES, + &error, + 0); if (dm_tables != NULL && error == NULL) { gdouble fract; @@ -366,11 +374,11 @@ datasources_add_all_tables (Datasources *datasources) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - "Unable to get tables list.\n%s", - (error != NULL && error->message != NULL ? error->message : "No details.")); + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get tables list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); break; @@ -388,18 +396,18 @@ datasources_add_all_tables (Datasources *datasources) new_id = 0; sql = g_strdup_printf ("SELECT COALESCE (MAX (id), 0) FROM %stables", - priv->commons->prefix); + priv->commons->prefix); error = NULL; stmt = gda_sql_parser_parse_string (priv->commons->gdaparser, sql, NULL, &error); if (stmt == NULL || error != NULL) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - "Unable to get tables list.\n%s", - (error != NULL && error->message != NULL ? error->message : "No details.")); + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get tables list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); break; @@ -417,23 +425,23 @@ datasources_add_all_tables (Datasources *datasources) new_id++; sql = g_strdup_printf ("INSERT INTO %stables" - " (id, name, id_datasources)" - " VALUES (%d, '%s', %d)", - priv->commons->prefix, - new_id, - table_name, - id_ds); + " (id, name, id_datasources)" + " VALUES (%d, '%s', %d)", + priv->commons->prefix, + new_id, + table_name, + id_ds); error = NULL; stmt = gda_sql_parser_parse_string (priv->commons->gdaparser, sql, NULL, &error); if (stmt == NULL || error != NULL) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - "Unable to get tables list.\n%s", - (error != NULL && error->message != NULL ? error->message : "No details.")); + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get tables list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); break; @@ -447,11 +455,11 @@ datasources_add_all_tables (Datasources *datasources) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - "Unable to get tables list.\n%s", - (error != NULL && error->message != NULL ? error->message : "No details.")); + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get tables list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); break; @@ -461,11 +469,11 @@ datasources_add_all_tables (Datasources *datasources) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - "Unable to get tables list.\n%s", - (error != NULL && error->message != NULL ? error->message : "No details.")); + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get tables list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); break; @@ -478,6 +486,8 @@ datasources_add_all_tables (Datasources *datasources) g_object_unref (stmt); g_object_unref (dm); + ids_tables_saved = g_strconcat (ids_tables_saved, g_strdup_printf ("%d,", new_id), NULL); + /* updating fields */ error = NULL; mcontext.column_names = g_new (gchar *, 1); @@ -485,16 +495,16 @@ datasources_add_all_tables (Datasources *datasources) mcontext.column_values = g_new (GValue *, 1); mcontext.column_values[0] = gda_value_new (G_TYPE_STRING); g_value_take_string (mcontext.column_values[0], - gda_sql_identifier_quote (table_name, priv->gdacon, NULL, FALSE, FALSE)); + gda_sql_identifier_quote (table_name, priv->gdacon, NULL, FALSE, FALSE)); if (!gda_connection_update_meta_store (priv->gdacon, &mcontext, &error)) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - "Unable to get fields list.\n%s", - (error != NULL && error->message != NULL ? error->message : "No details.")); + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get fields list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); break; @@ -504,10 +514,10 @@ datasources_add_all_tables (Datasources *datasources) gval = gda_value_new (G_TYPE_STRING); g_value_take_string (gval, table_name); dm_fields = gda_connection_get_meta_store_data (priv->gdacon, - GDA_CONNECTION_META_FIELDS, - &error, - 1, - "name", gval); + GDA_CONNECTION_META_FIELDS, + &error, + 1, + "name", gval); if (dm_fields != NULL) { rows_fields = gda_data_model_get_n_rows (dm_fields); @@ -515,34 +525,79 @@ datasources_add_all_tables (Datasources *datasources) { field_name = gda_value_stringify (gda_data_model_get_value_at (dm_fields, 0, row_fields, NULL)); + /* check if already exists */ + sql = g_strdup_printf ("SELECT id FROM %sfields WHERE id_tables = %d AND name = '%s'", + priv->commons->prefix, + new_id, + field_name); + error = NULL; + stmt = gda_sql_parser_parse_string (priv->commons->gdaparser, sql, NULL, &error); + if (stmt == NULL || error != NULL) + { + gtk_widget_hide_all (dialog); + dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get tables list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); + gtk_dialog_run (GTK_DIALOG (dialog)); + gtk_widget_destroy (dialog); + break; + } + + g_free (sql); + + error = NULL; + dm = gda_connection_statement_execute_select (priv->commons->gdacon, stmt, NULL, &error); + g_object_unref (stmt); + if (dm != NULL && error == NULL && gda_data_model_get_n_rows (dm) > 0) + { + ids_fields_saved = g_strconcat (ids_fields_saved, g_strdup_printf ("%d,", g_value_get_int (gda_data_model_get_value_at (dm, 0, 0, NULL))), NULL); + } + else if (error != NULL) + { + gtk_widget_hide_all (dialog); + dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get tables list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); + gtk_dialog_run (GTK_DIALOG (dialog)); + gtk_widget_destroy (dialog); + break; + } + else + { /* find if field is the primary key */ sql = g_strdup_printf ("SELECT column_name" - " FROM _key_column_usage AS c" - " INNER JOIN _table_constraints AS t" - " ON c.table_catalog = t.table_catalog" - " AND c.table_schema = t.table_schema" - " AND c.table_name = t.table_name" - " WHERE t.table_name = '%s'" - " AND t.constraint_type = 'PRIMARY KEY'" - " AND c.column_name = '%s'", - table_name, - field_name); + " FROM _key_column_usage AS c" + " INNER JOIN _table_constraints AS t" + " ON c.table_catalog = t.table_catalog" + " AND c.table_schema = t.table_schema" + " AND c.table_name = t.table_name" + " WHERE t.table_name = '%s'" + " AND t.constraint_type = 'PRIMARY KEY'" + " AND c.column_name = '%s'", + table_name, + field_name); error = NULL; dm = gda_meta_store_extract (gda_connection_get_meta_store (priv->gdacon), - sql, - &error, - NULL); + sql, + &error, + NULL); if (dm == NULL || error != NULL) { g_warning ("Unable to get the primary key: %s.", - error != NULL && error->message != NULL ? error->message : "no details"); + error != NULL && error->message != NULL ? error->message : "no details"); } is_key = (dm != NULL && gda_data_model_get_n_rows (dm) > 0); id_field = 0; error = NULL; sql = g_strdup_printf ("SELECT COALESCE (MAX (id), 0) FROM %sfields", - priv->commons->prefix); + priv->commons->prefix); stmt = gda_sql_parser_parse_string (priv->commons->gdaparser, sql, NULL, NULL); g_free (sql); dm = gda_connection_statement_execute_select (priv->commons->gdacon, stmt, NULL, &error); @@ -554,7 +609,7 @@ datasources_add_all_tables (Datasources *datasources) else if (error != NULL) { g_warning ("Error getting new id field: %s", - (error != NULL && error->message != NULL ? error->message : "No details.")); + error != NULL && error->message != NULL ? error->message : "No details."); } id_field++; @@ -562,13 +617,13 @@ datasources_add_all_tables (Datasources *datasources) error = NULL; sql = g_strdup_printf ("INSERT INTO %sfields" - " (id, id_tables, name, is_key)" - " VALUES (%d, %d, '%s', %s)", - priv->commons->prefix, - id_field, - new_id, - field_name, - is_key ? "TRUE" : "FALSE"); + " (id, id_tables, name, is_key)" + " VALUES (%d, %d, '%s', %s)", + priv->commons->prefix, + id_field, + new_id, + field_name, + is_key ? "TRUE" : "FALSE"); stmt = gda_sql_parser_parse_string (priv->commons->gdaparser, sql, NULL, NULL); g_free (sql); gda_connection_statement_execute_non_select (priv->commons->gdacon, stmt, NULL, NULL, &error); @@ -576,7 +631,10 @@ datasources_add_all_tables (Datasources *datasources) if (error != NULL) { g_warning ("Error saving new field: %s", - (error->message != NULL ? error->message : "No details.")); + error->message != NULL ? error->message : "No details."); + } + + ids_fields_saved = g_strconcat (ids_fields_saved, g_strdup_printf ("%d,", id_field), NULL); } } } @@ -588,17 +646,44 @@ datasources_add_all_tables (Datasources *datasources) { gtk_widget_hide_all (dialog); dialog = gtk_message_dialog_new (GTK_WINDOW (gtk_builder_get_object (priv->commons->gtkbuilder, "w_main")), - GTK_DIALOG_DESTROY_WITH_PARENT, - GTK_MESSAGE_WARNING, - GTK_BUTTONS_OK, - "Unable to get tables list.\n%s", - (error != NULL && error->message != NULL ? error->message : "No details.")); + GTK_DIALOG_DESTROY_WITH_PARENT, + GTK_MESSAGE_WARNING, + GTK_BUTTONS_OK, + "Unable to get tables list.\n%s", + error != NULL && error->message != NULL ? error->message : "No details."); gtk_dialog_run (GTK_DIALOG (dialog)); gtk_widget_destroy (dialog); return; } - + + /* removing tables that aren't still in db */ + if (g_strcmp0 (ids_tables_saved, "") != 0) + { + ids_tables_saved[strlen (ids_tables_saved) - 1] = '\0'; + sql = g_strdup_printf ("DELETE FROM %stables WHERE id NOT IN (%s)", + priv->commons->prefix, + ids_tables_saved); + stmt = gda_sql_parser_parse_string (priv->commons->gdaparser, sql, NULL, NULL); + g_free (sql); + gda_connection_statement_execute_non_select (priv->commons->gdacon, stmt, NULL, NULL, &error); + g_object_unref (stmt); + } + + /* removing fields that aren't still in db */ + if (g_strcmp0 (ids_fields_saved, "") != 0) + { + ids_fields_saved[strlen (ids_fields_saved) - 1] = '\0'; + sql = g_strdup_printf ("DELETE FROM %sfields WHERE id NOT IN (%s)", + priv->commons->prefix, + ids_fields_saved); + stmt = gda_sql_parser_parse_string (priv->commons->gdaparser, sql, NULL, NULL); + g_free (sql); + gda_connection_statement_execute_non_select (priv->commons->gdacon, stmt, NULL, NULL, &error); + g_object_unref (stmt); + } + gtk_widget_hide_all (dialog); + datasources_load (datasources); } else -- 2.49.0