Hi guys! After some days working in it, I have a first patch which features: - Creation of database in load/save with confirm dialogs. - Creation of database tables in load/save with confirm dialogs. - Upgrading of database load/save with confirm dialogs and nice WARNINGS I have some points to be solved, like moving some logic from frontend to backend, but currently, all the logic is in the frontend because it is easy to show the user UI will all the data in operations, but I think in the future we will move logic to the backend. Once these patch is applied we can start working in database projects maintanence and implementing a model for all the user access control to databases. I need also to modifiy tables so we can have a global version for all the projects. I am using now the Planner version from to play with it, but we need the planner version which created the user database tables. I think we won't support using different planner versions to share data in a common database in the first release. Cheers -- Alvaro
Index: libplanner/Makefile.am
===================================================================
RCS file: /cvs/gnome/planner/libplanner/Makefile.am,v
retrieving revision 1.7
diff -u -b -B -p -r1.7 Makefile.am
--- libplanner/Makefile.am 2 May 2004 13:30:16 -0000 1.7
+++ libplanner/Makefile.am 11 Jul 2004 16:32:16 -0000
@@ -2,7 +2,8 @@ INCLUDES = \
-I. -I$(top_srcdir) \
$(LIBPLANNER_CFLAGS) $(WARN_CFLAGS) \
-DMRP_STORAGEMODULEDIR=\""$(libdir)/planner/storage-modules"\" \
- -DMRP_FILE_MODULES_DIR=\""$(libdir)/planner/file-modules"\"
+ -DMRP_FILE_MODULES_DIR=\""$(libdir)/planner/file-modules"\" \
+ -DDATADIR=\""$(datadir)"\"
lib_LTLIBRARIES = libplanner-1.la
Index: libplanner/mrp-project.c
===================================================================
RCS file: /cvs/gnome/planner/libplanner/mrp-project.c,v
retrieving revision 1.10
diff -u -b -B -p -r1.10 mrp-project.c
Index: libplanner/mrp-sql.c
===================================================================
RCS file: /cvs/gnome/planner/libplanner/mrp-sql.c,v
retrieving revision 1.8
diff -u -b -B -p -r1.8 mrp-sql.c
--- libplanner/mrp-sql.c 25 Jun 2004 09:59:35 -0000 1.8
+++ libplanner/mrp-sql.c 11 Jul 2004 16:32:27 -0000
@@ -37,7 +37,7 @@
#define REVISION "sql-storage-revision"
/* Struct to keep calendar data before we can build the tree, create the
- * calendars and insert the in the project.
+ * calendars and insert them in the project.
*/
typedef struct {
gint id;
@@ -206,7 +206,7 @@ sql_get_last_error (GdaConnection *conne
error = (GdaError *) g_list_last (list)->data;
- /* Poor user, she won't get localized messages */
+ /* FIXME: Poor user, she won't get localized messages */
error_txt = gda_error_get_description (error);
return error_txt;
@@ -430,7 +430,7 @@ sql_read_project (SQLData *data, gint pr
g_free (query);
if (res == NULL) {
- g_warning ("Couldn't get cursor for project %s.",
+ g_warning ("DECLARE CURSOR command failed (project) %s.",
sql_get_last_error (data->con));
goto out;
}
@@ -604,7 +604,7 @@ sql_read_property_specs (SQLData *data)
if (res == NULL) {
- g_warning ("DECLARE CURSOR command failed (propecty_specs) %s.",
+ g_warning ("DECLARE CURSOR command failed (propecty_type) %s.",
sql_get_last_error (data->con));
goto out;
}
@@ -612,7 +612,7 @@ sql_read_property_specs (SQLData *data)
res = sql_execute_query (data->con, "FETCH ALL in mycursor");
if (res == NULL) {
- g_warning ("FETCH ALL failed for property_specs %s.",
+ g_warning ("FETCH ALL failed for property_type %s.",
sql_get_last_error (data->con));
goto out;
}
@@ -695,9 +695,10 @@ sql_read_property_specs (SQLData *data)
TRUE /* FIXME: user_defined, should
be read from the file */);
- g_hash_table_insert (data->property_type_id_hash, GINT_TO_POINTER (property_type_id), property);
+ g_hash_table_insert (data->property_type_id_hash,
+ GINT_TO_POINTER (property_type_id), property);
} else {
- /* Properties that are already added (e.g. cost). */
+ /* FIXME: Properties that are already added (e.g. cost). */
property = mrp_project_get_property (data->project, name, owner);
g_hash_table_insert (data->property_type_id_hash, GINT_TO_POINTER (property_type_id), property);
}
@@ -2146,6 +2147,7 @@ mrp_sql_load_project (MrpStorageSQL *sto
data = g_new0 (SQLData, 1);
data->project_id = -1;
+ /* data->project_id = project_id; */
data->day_id_hash = g_hash_table_new (NULL, NULL);
data->calendar_id_hash = g_hash_table_new (NULL, NULL);
data->group_id_hash = g_hash_table_new (NULL, NULL);
@@ -2304,6 +2306,9 @@ sql_write_project (MrpStorageSQL *stora
* saving it.
*/
if (project_id != -1) {
+
+ g_message ("Project ID: %d", project_id);
+
/* First check if a project with the given id already exists. */
query = g_strdup_printf ("DECLARE mycursor CURSOR FOR SELECT "
"name, revision, last_user FROM project WHERE proj_id=%d",
@@ -2372,6 +2377,7 @@ sql_write_project (MrpStorageSQL *stora
}
} else {
/* There was no old project. */
+ g_message ("This is a new project ...");
data->revision = 1;
}
Index: libplanner/mrp-storage-sql.c
===================================================================
RCS file: /cvs/gnome/planner/libplanner/mrp-storage-sql.c,v
retrieving revision 1.2
diff -u -b -B -p -r1.2 mrp-storage-sql.c
Index: src/Makefile.am
===================================================================
RCS file: /cvs/gnome/planner/src/Makefile.am,v
retrieving revision 1.16
diff -u -b -B -p -r1.16 Makefile.am
--- src/Makefile.am 21 Jun 2004 20:57:05 -0000 1.16
+++ src/Makefile.am 11 Jul 2004 16:32:29 -0000
@@ -12,6 +12,8 @@ INCLUDES = \
-DGLADEDIR=\""$(datadir)/planner/glade"\" \
-DMRP_VIEWDIR=\""$(libdir)/planner/views"\" \
-DMRP_PLUGINDIR=\""$(libdir)/planner/plugins"\" \
+ -DSQL_DIR=\""$(datadir)/planner/sql"\" \
+ -DVERSION=\""$(VERSION)"\" \
$(GNOMEUI_UNSTABLE)
if HAVE_PYTHON_PLUGIN
Index: src/planner-sql-plugin.c
===================================================================
RCS file: /cvs/gnome/planner/src/planner-sql-plugin.c,v
retrieving revision 1.12
diff -u -b -B -p -r1.12 planner-sql-plugin.c
--- src/planner-sql-plugin.c 25 Jun 2004 09:59:35 -0000 1.12
+++ src/planner-sql-plugin.c 11 Jul 2004 16:32:31 -0000
@@ -6,7 +6,7 @@
* Copyright (C) 2003 Mikael Hallendal <micke imendio com>
* Copyright (C) 2003 Alvaro del Castillo <acs barrapunto com>
*
- * This program is free software; you can redistribute it and/or
+ * This program is free software; You can redistribute it and/or
* modify it under the terms of the GNU General Public License as
* published by the Free Software Foundation; either version 2 of the
* License, or (at your option) any later version.
@@ -77,6 +77,10 @@ static void sql_plugin_save
static GdaDataModel *
sql_execute_query (GdaConnection *con,
gchar *query);
+
+/* FIXME: The same in mrp-sql.c. Create a SQL API in libplanner? */
+static const gchar * sql_get_last_error (GdaConnection *connection);
+
void plugin_init (PlannerPlugin *plugin,
PlannerWindow *main_window);
void plugin_exit (void);
@@ -112,6 +116,23 @@ sql_execute_query (GdaConnection *con, g
return res;
}
+static const gchar *
+sql_get_last_error (GdaConnection *connection)
+{
+ GList *list;
+ GdaError *error;
+ const gchar *error_txt;
+
+ list = (GList *) gda_connection_get_errors (connection);
+
+ error = (GdaError *) g_list_last (list)->data;
+
+ /* FIXME: Poor user, she won't get localized messages */
+ error_txt = gda_error_get_description (error);
+
+ return error_txt;
+}
+
/**
* Helper to get an int.
@@ -291,6 +312,345 @@ row_activated_cb (GtkWidget *tre
gtk_widget_activate (ok_button);
}
+/* Planner versions:
+ 1.x is always lower than 2.x.
+ 0.6 is lower than 0.11
+ If 0.11.90 we don't look ".90".
+*/
+static gboolean
+is_newer_version (const gchar *version_new_txt,
+ const gchar *version_old_txt)
+{
+ guint subversion_old, subversion_new;
+ guint version_old, version_new;
+ gchar **versionv_new, **versionv_old;
+
+ g_return_val_if_fail (version_new_txt != NULL && version_old_txt != NULL, FALSE);
+
+ version_old = g_ascii_strtod (version_old_txt, NULL);
+ version_new = g_ascii_strtod (version_new_txt, NULL);
+
+ if (version_new > version_old) {
+ return TRUE;
+ }
+ else if (version_old > version_new) {
+ return FALSE;
+ }
+
+ /* Need to check subversion */
+ versionv_old = g_strsplit (version_old_txt,".",-1);
+ versionv_new = g_strsplit (version_new_txt,".",-1);
+
+ subversion_old = g_ascii_strtod (versionv_old[1], NULL);
+ subversion_new = g_ascii_strtod (versionv_new[1], NULL);
+
+ g_strfreev(versionv_new);
+ g_strfreev(versionv_old);
+
+ if (subversion_new > subversion_old) {
+ return TRUE;
+ }
+ return FALSE;
+}
+
+static gboolean
+check_database_tables (GdaConnection *conn)
+{
+ GdaDataModel *res;
+ GtkWidget *dialog;
+ gint result;
+ GDir* dir;
+ const gchar *name;
+ gboolean upgradable = FALSE;
+ gboolean create_tables;
+ gboolean can_create_tables = FALSE;
+ gchar *max_version_database;
+ gchar *max_version_upgrade;
+ gchar *upgrade_file = NULL;
+ gchar *database_file = NULL;
+ const gchar *database_name;
+ gboolean retval = FALSE;
+
+ max_version_database = g_strdup ("0.0");
+ max_version_upgrade = g_strdup ("0.0");
+ database_name = gda_connection_get_database (conn);
+
+ /* Check if tables exist */
+ res = sql_execute_query (conn, "SELECT proj_id FROM project");
+ if (res == NULL) {
+ create_tables = TRUE;
+ } else {
+ create_tables = FALSE;
+ g_free (res);
+ }
+
+ g_warning ("Working with tables ...");
+
+ /* Check for tables */
+ dir = g_dir_open (SQL_DIR, 0, NULL);
+ while ((name = g_dir_read_name (dir)) != NULL) {
+ gchar **namev = NULL, **versionv = NULL;
+ gchar *version;
+ gchar *sql_file = g_build_path (G_DIR_SEPARATOR_S,
+ SQL_DIR,
+ name,
+ NULL);
+
+ if (strncmp (name + strlen (name) - 4, ".sql", 4) != 0) {
+ g_warning ("Trash in SQL data Planner directory: %s%s",
+ SQL_DIR, name);
+ continue;
+ }
+
+ /* Find version between "-" and ".sql" */
+ namev = g_strsplit (sql_file,"-",-1);
+ /* Upgrade: 2 versions in file */
+ if (namev[1] && namev[2]) {
+ versionv = g_strsplit (namev[2],".sql",-1);
+ if (is_newer_version (versionv[0], namev[1])) {
+ if (!strcmp (namev[1], VERSION)) {
+ upgradable = TRUE;
+ g_message ("Found upgrade file: %s",
+ sql_file);
+ if (is_newer_version (versionv[0], max_version_upgrade)) {
+ if (upgrade_file) {
+ g_free (upgrade_file);
+ }
+ upgrade_file = g_strdup (sql_file);
+ g_free (max_version_upgrade);
+ max_version_upgrade = g_strdup (versionv[0]);
+ }
+ }
+ } else {
+ g_warning ("Incorrect upgrade file name: %s", sql_file);
+ }
+ }
+ /* Create tables */
+ else if (namev[1]) {
+ versionv = g_strsplit (namev[1],".sql",-1);
+ if (is_newer_version (versionv[0], max_version_database)) {
+ if (database_file) {
+ g_free (database_file);
+ }
+ database_file = g_strdup (sql_file);
+ g_free (max_version_database);
+ max_version_database = g_strdup (versionv[0]);
+ }
+
+ can_create_tables = TRUE;
+ version = g_strdup (versionv[0]);
+ g_message ("Version: %s", version);
+ g_free (version);
+
+ } else {
+ if (!database_file) {
+ database_file = g_strdup (sql_file);
+ }
+ g_warning ("File with no version: %s", sql_file);
+ can_create_tables = TRUE;
+ }
+ if (versionv) {
+ g_strfreev(versionv);
+ }
+ if (namev) {
+ g_strfreev(namev);
+ }
+ g_free (sql_file);
+ }
+
+ if (!upgradable && !create_tables) {
+ retval = TRUE;
+ }
+ else if (upgradable && !create_tables) {
+ gchar *contents;
+
+ dialog = gtk_message_dialog_new (NULL,
+ GTK_DIALOG_DESTROY_WITH_PARENT,
+ GTK_MESSAGE_QUESTION,
+ GTK_BUTTONS_YES_NO,
+ _("Database %s need to be upgraded to version: %s."
+ " Please backup the database before upgrading."
+ " Have you done the backup and want to continue?"),
+ database_name, max_version_upgrade);
+
+ result = gtk_dialog_run (GTK_DIALOG (dialog));
+ gtk_widget_destroy (dialog);
+ if (result == GTK_RESPONSE_YES) {
+ g_file_get_contents (upgrade_file, &contents, NULL, NULL);
+ res = sql_execute_query (conn, contents);
+ g_free (contents);
+ if (res == NULL) {
+ dialog = gtk_message_dialog_new (NULL,
+ GTK_DIALOG_DESTROY_WITH_PARENT,
+ GTK_MESSAGE_WARNING,
+ GTK_BUTTONS_CLOSE,
+ _("Can't create tables in database %s. File %s could be corrupted."
+ "\n\nDatabase error: \n%s"),
+ database_name, upgrade_file,
+ sql_get_last_error (conn));
+
+ gtk_dialog_run (GTK_DIALOG (dialog));
+ gtk_widget_destroy (dialog);
+ retval = FALSE;
+ } else {
+ retval = TRUE;
+ g_free (res);
+ }
+ } else {
+ retval = FALSE;
+ }
+ g_free (upgrade_file);
+ }
+
+ else if (create_tables && !can_create_tables) {
+ g_warning ("Need to create tables but no database file");
+ retval = FALSE;
+ }
+
+ else if (create_tables && can_create_tables) {
+ gchar *contents;
+
+ dialog = gtk_message_dialog_new (NULL,
+ GTK_DIALOG_DESTROY_WITH_PARENT,
+ GTK_MESSAGE_QUESTION,
+ GTK_BUTTONS_YES_NO,
+ _("Tables in database %s doesn't exist. "
+ "Do you want to create them?"),
+ database_name);
+
+ result = gtk_dialog_run (GTK_DIALOG (dialog));
+ gtk_widget_destroy (dialog);
+
+ if (result == GTK_RESPONSE_YES) {
+ g_file_get_contents (database_file, &contents, NULL, NULL);
+ res = sql_execute_query (conn, contents);
+ g_free (contents);
+ if (res == NULL) {
+ dialog = gtk_message_dialog_new (NULL,
+ GTK_DIALOG_DESTROY_WITH_PARENT,
+ GTK_MESSAGE_WARNING,
+ GTK_BUTTONS_CLOSE,
+ _("Can't create tables in database %s"),
+ database_name);
+
+ result = gtk_dialog_run (GTK_DIALOG (dialog));
+ gtk_widget_destroy (dialog);
+ retval = FALSE;
+ } else {
+ g_free (res);
+ retval = TRUE;
+ }
+ }
+ g_free (database_file);
+ }
+
+ g_free (max_version_upgrade);
+ g_free (max_version_database);
+ return retval;
+}
+
+/* Try to create the database */
+static gboolean
+create_database (const gchar *dsn_name,
+ const gchar *db_name)
+{
+ GtkWidget *dialog;
+ guint result;
+ gboolean retval;
+ GdaConnection *conn;
+ GdaClient *client;
+ GdaDataSourceInfo *dsn;
+ gchar *cnc_string_orig;
+ /* FIXME: In postgresql we use template1 as the connection database */
+ gchar *init_database = "template1";
+ gchar *query;
+
+ dsn = gda_config_find_data_source (dsn_name);
+ cnc_string_orig = dsn->cnc_string;
+ retval = FALSE;
+
+ /* Use same data but changing the database */
+ dsn->cnc_string = g_strdup_printf ("DATABASE=%s", init_database);
+ gda_config_save_data_source_info (dsn);
+
+ client = gda_client_new ();
+ conn = gda_client_open_connection (client, dsn_name, NULL, NULL, 0);
+ if (!GDA_IS_CONNECTION (conn)) {
+ g_warning ("Can't connect to database server in order to check/create the database: %s", cnc_string_orig);
+ } else {
+ dialog = gtk_message_dialog_new (NULL,
+ GTK_DIALOG_DESTROY_WITH_PARENT,
+ GTK_MESSAGE_QUESTION,
+ GTK_BUTTONS_YES_NO,
+ _("Database %s doesn't exist. "
+ "Do you want to create it?"),
+ db_name);
+
+ result = gtk_dialog_run (GTK_DIALOG (dialog));
+ gtk_widget_destroy (dialog);
+
+ if (result == GTK_RESPONSE_YES) {
+ query = g_strdup_printf ("CREATE DATABASE %s WITH ENCODING = 'UTF8'",
+ db_name);
+ sql_execute_query (conn, query);
+ g_free (query);
+ /* FIXME: Tables will need the group: dirty relation between
+ code and tables */
+ query = g_strdup_printf ("CREATE GROUP planner WITH USER %s",
+ gda_connection_get_username (conn));
+ sql_execute_query (conn, query);
+ g_free (query);
+ retval = TRUE;
+ } else {
+ retval = FALSE;
+ }
+ gda_connection_close (conn);
+ g_object_unref (client);
+ }
+ g_free (dsn->cnc_string);
+ dsn->cnc_string = cnc_string_orig;
+ gda_config_save_data_source_info (dsn);
+
+ return retval;
+}
+
+/* Test database status: database exists, correct tables, correct version */
+static GdaConnection *
+sql_get_tested_connection (const gchar *dsn_name,
+ const gchar *db_name,
+ GdaClient *client,
+ PlannerPlugin *plugin)
+{
+ GdaConnection *conn;
+ gchar *str;
+
+ conn = gda_client_open_connection (client, dsn_name, NULL, NULL, 0);
+
+ if (!GDA_IS_CONNECTION (conn)) {
+ if (!create_database (dsn_name, db_name)) {
+ str = g_strdup_printf (_("Connection to database '%s' failed."),
+ db_name);
+ show_error_dialog (plugin, str);
+ conn = NULL;
+ } else {
+ conn = gda_client_open_connection (client, dsn_name, NULL, NULL, 0);
+ }
+ }
+
+ if (conn != NULL) {
+ if (!check_database_tables (conn)) {
+ str = g_strdup_printf (_("Test to tables in database '%s' failed."), db_name);
+ show_error_dialog (plugin, str);
+ g_free (str);
+ gda_connection_close (conn);
+ conn = NULL;
+ }
+ }
+
+ /* g_object_unref (client); */
+ return conn;
+}
+
/**
* Display a list with projects and let the user select one. Returns the project
* id of the selected one.
@@ -306,7 +666,6 @@ sql_plugin_retrieve_project_id (PlannerP
GdaConnection *conn;
GdaDataModel *res;
GdaClient *client;
- gchar *str;
GladeXML *gui;
GtkWidget *dialog;
GtkWidget *treeview;
@@ -331,13 +690,9 @@ sql_plugin_retrieve_project_id (PlannerP
g_free (db_txt);
client = gda_client_new ();
+ conn = sql_get_tested_connection (dsn_name, database, client, plugin);
- conn = gda_client_open_connection (client, dsn_name, NULL, NULL, 0);
-
- if (!GDA_IS_CONNECTION (conn)) {
- str = g_strdup_printf (_("Connection to database '%s' failed."), database);
- show_error_dialog (plugin, str);
- g_free (str);
+ if (conn == NULL) {
return -1;
}
@@ -347,7 +702,6 @@ sql_plugin_retrieve_project_id (PlannerP
return -1;
}
g_object_unref (res);
- res = NULL;
res = sql_execute_query (conn,
"DECLARE mycursor CURSOR FOR SELECT proj_id, name,"
@@ -694,6 +1047,8 @@ sql_plugin_save (BonoboUIComponent *comp
gpointer user_data,
const gchar *cname)
{
+ GdaClient *client;
+ GdaConnection *conn;
PlannerPlugin *plugin = user_data;
MrpProject *project;
GObject *object;
@@ -703,7 +1058,11 @@ sql_plugin_save (BonoboUIComponent *comp
gchar *login = NULL;
gchar *password = NULL;
gchar *uri = NULL;
+ const gchar *uri_plan = NULL;
GError *error = NULL;
+ gchar *db_txt;
+ const gchar *dsn_name = "planner-auto";
+ const gchar *provider = "PostgreSQL";
project = planner_window_get_project (plugin->main_window);
@@ -717,19 +1076,56 @@ sql_plugin_save (BonoboUIComponent *comp
return;
}
+ db_txt = g_strdup_printf ("DATABASE=%s",database);
+ gda_config_save_data_source (dsn_name,
+ provider,
+ db_txt,
+ "planner project", login, password);
+ g_free (db_txt);
+ client = gda_client_new ();
+ conn = sql_get_tested_connection (dsn_name, database, client, plugin);
+ if (conn == NULL) {
+ g_object_unref (client);
+ return;
+ }
+ gda_connection_close (conn);
+ g_object_unref (client);
+
/* This code is prepared for getting support for selecting a project to
* save over. Needs finishing though. Pass project id -1 for now (always
* create a new project).
*/
- uri = create_sql_uri (server, port, database, login, password, -1);
+ uri_plan = mrp_project_get_uri (project);
+ /* First time project */
+ if (uri_plan == NULL) {
+ uri = create_sql_uri (server, port, database, login, password, -1);
if (!mrp_project_save_as (project, uri, FALSE, &error)) {
show_error_dialog (plugin, error->message);
g_clear_error (&error);
goto fail;
}
+ g_free (uri);
+ }
+ /* Project was in database */
+ else if (strncmp (uri_plan, "sql://", 6) == 0) {
+ if (!mrp_project_save (project, FALSE, &error)) {
+ show_error_dialog (plugin, error->message);
+ g_clear_error (&error);
+ goto fail;
+ }
+ }
+ /* Project wasn't in database */
+ else {
+ uri = create_sql_uri (server, port, database, login, password, -1);
+ if (!mrp_project_save_as (project, uri, FALSE, &error)) {
+ show_error_dialog (plugin, error->message);
+ g_clear_error (&error);
+ goto fail;
+ }
g_free (uri);
+ }
object = G_OBJECT (plugin->main_window);
Attachment:
signature.asc
Description: Esta parte del mensaje =?ISO-8859-1?Q?est=E1?= firmada digitalmente