Hi guys! After reading the Lincoln and Jani emails, and coding and testing 2 hours, here goes a new patch with the database version for all planner projects in a separate table. With this approach, we can grow the table to meet Liconln hosting needs and also, Jani ideas. But for the moment, I want to close a first working version in which we can continue working. The patch is very similar to the last one but: - Checks for database version and store database version after creating or upgrading tables. - More checks for the sql files: if upgrade and new database files don't finish with the same database version, a warning in console is displayed and the process fails. So, everytime we change the database between planner versions, we need upgrade files and also, database files with the complete description of tables and relations. - Removed asking the user if she wants to create the tables. She have said that she want the new database so ... sure she will want also the tables ;-) With the patch I attach the new sql files. Hmmm, I think the email will be queued because size :( Cheers -- Alvaro El sáb, 31-07-2004 a las 09:23, Alvaro del Castillo escribió: > Hi! > > The database create/upgrade patch only misses actually a way to know the > version for planner database. Thinking about how we can save in database > the version (needed for the upgrade system), I finally think that the > best thing we can do is to store it in "property" table as a "text" > property. > > property records are related to projects in table "project_to_property" > so it could be clear that if a property isn't in project_to_property, it > is a global property for all planner projects, like the database > version. > > But we can't do it that way because properties types are related to > projects. So if we want to use a property to store the database version, > we need a pseudo-project in which to store the global properties for > planner. I am not sure how "hackish" it sounds to you. For me yes, it is > a bit hackish but the other solution will be to create a separate table: > > CREATE TABLE property_global ( > prop_id serial, > prop_name text NOT NULL, > value text, > PRIMARY KEY (prop_id) > ); > > Then we can't have different properties types as in properties for > projects but you know, in this table for the moment we will have only 1 > record and I can't think now about other things we ca store here. > > I think I will follow the new table approach but, what do you think? > > http://cvs.gnome.org/viewcvs/planner/data/sql/database-0.11.sql?rev=1.1&view=auto (database tables) > > Cheers > > -- Alvaro > > > > ______________________________________________________________________ > _______________________________________________ > Planner-dev mailing list > Planner-dev lists imendio com > http://lists.imendio.com/mailman/listinfo/planner-dev
Index: data/sql/Makefile.am
===================================================================
RCS file: /cvs/gnome/planner/data/sql/Makefile.am,v
retrieving revision 1.2
diff -u -b -B -p -r1.2 Makefile.am
--- data/sql/Makefile.am 30 Jun 2004 10:47:32 -0000 1.2
+++ data/sql/Makefile.am 31 Jul 2004 09:24:59 -0000
@@ -1,8 +1,10 @@
sqldir = $(datadir)/planner/sql
sql_DATA = \
+ database-0.13.sql \
database-0.11.sql \
database.sql \
+ upgrade-0.11-0.13.sql \
upgrade-0.6.x-0.11.sql
EXTRA_DIST = $(sql_DATA)
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 31 Jul 2004 09:25:00 -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 31 Jul 2004 09:25:02 -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);
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 31 Jul 2004 09:25:02 -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 31 Jul 2004 09:25:03 -0000
@@ -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,27 @@ 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);
+
+ if (list == NULL) {
+ return _("No errors reported.");
+ }
+
+ 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 +316,373 @@ 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,
+ PlannerPlugin *plugin)
+{
+ GtkWindow *window;
+ 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;
+ gchar *database_version = VERSION;
+ 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);
+
+ window = GTK_WINDOW (plugin->main_window);
+
+ /* Try to get the database version */
+ res = sql_execute_query (conn, "SELECT value FROM property_global WHERE prop_name='database_version'");
+ if (res == NULL) {
+ create_tables = TRUE;
+ } else {
+ create_tables = FALSE;
+ database_version = get_string (res, 0, 0);
+ g_message ("Database version : %s", database_version);
+ if (database_version == NULL) {
+ database_version = VERSION;
+ }
+ g_object_unref (res);
+ }
+
+ /* 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_filename (SQL_DIR, name, NULL);
+
+ if (strncmp (name + strlen (name) - 4, ".sql", 4) != 0) {
+ g_warning ("Strange file 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], database_version)) {
+ upgradable = TRUE;
+ 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_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);
+ }
+
+ /* With each database change we need the new complete database description
+ and the upgrades files from different versions */
+ if ((is_newer_version (max_version_upgrade, max_version_database) ||
+ is_newer_version (max_version_database, max_version_upgrade))
+ &&
+ (upgradable || create_tables)) {
+ g_warning ("Database file version %s (%s) is different from upgrade file version %s (%s)",
+ max_version_database,
+ database_file,
+ max_version_upgrade,
+ upgrade_file);
+ retval = FALSE;
+ upgradable = FALSE;
+ can_create_tables = FALSE;
+ }
+
+ if (!upgradable && !create_tables) {
+ retval = TRUE;
+ }
+ else if (upgradable && !create_tables) {
+ gchar *contents;
+
+ dialog = gtk_message_dialog_new (window,
+ GTK_DIALOG_DESTROY_WITH_PARENT,
+ GTK_MESSAGE_QUESTION,
+ GTK_BUTTONS_NONE,
+ _("Database %s need to be upgraded "
+ "from version %s to version %s."),
+ database_name, database_version,
+ max_version_upgrade);
+ gtk_dialog_add_buttons ((GtkDialog *) dialog,
+ GTK_STOCK_CANCEL, GTK_RESPONSE_CANCEL,
+ _("Upgrade"), GTK_RESPONSE_YES,
+ NULL);
+
+ 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 (window,
+ GTK_DIALOG_DESTROY_WITH_PARENT,
+ GTK_MESSAGE_WARNING,
+ GTK_BUTTONS_CLOSE,
+ _("Could not upgrade database %s.\n Upgrade file used: %s."
+ "\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 {
+ gchar *query;
+
+ sql_execute_query (conn, "DELETE * FROM property_global WHERE prop_name='database_version'");
+ query = g_strdup_printf ("INSERT INTO property_global (prop_name, value) VALUES ('database_version','%s')", max_version_upgrade);
+
+ sql_execute_query (conn, query);
+ g_free (query);
+ g_free (res);
+ retval = TRUE;
+ }
+ } 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;
+
+ 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 (window,
+ 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 {
+ gchar *query;
+
+ query = g_strdup_printf ("INSERT INTO property_global (prop_name, value) VALUES ('database_version','%s')", max_version_database);
+
+ sql_execute_query (conn, query);
+ g_free (query);
+ 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,
+ PlannerPlugin *plugin)
+{
+ GtkWidget *dialog;
+ GtkWindow *window;
+ 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;
+
+ window = GTK_WINDOW (plugin->main_window);
+
+ /* 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 (conn == NULL) {
+ 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 (window,
+ GTK_DIALOG_DESTROY_WITH_PARENT,
+ GTK_MESSAGE_QUESTION,
+ GTK_BUTTONS_YES_NO,
+ _("Database %s is not setup for Planner. "
+ "Do you want to do that?"),
+ 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 definitions in sql file.*/
+ 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 (conn == NULL) {
+ if (!create_database (dsn_name, db_name, plugin)) {
+ 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, plugin)) {
+ 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 +698,6 @@ sql_plugin_retrieve_project_id (PlannerP
GdaConnection *conn;
GdaDataModel *res;
GdaClient *client;
- gchar *str;
GladeXML *gui;
GtkWidget *dialog;
GtkWidget *treeview;
@@ -331,13 +722,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 +734,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 +1079,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 +1090,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 +1108,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);
-- $Id: database-0.11.sql,v 1.1 2004/06/30 10:07:41 alvaro Exp $
-- Planner Database Schema
-- Daniel Lundin <daniel codefactory se>
-- Richard Hult <richard imendio com>
-- Copyright 2003 CodeFactory AB
--
-- Project
--
CREATE TABLE project (
proj_id serial,
name text NOT NULL,
company text,
manager text,
proj_start date NOT NULL DEFAULT CURRENT_TIMESTAMP,
cal_id integer,
phase text,
default_group_id integer,
revision integer,
last_user text NOT NULL DEFAULT (user),
PRIMARY KEY (proj_id)
);
GRANT select,insert,update,delete ON project TO GROUP planner;
GRANT select,update ON project_proj_id_seq TO GROUP planner;
--
-- Phases
--
CREATE TABLE phase (
phase_id serial,
proj_id integer,
name text NOT NULL,
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (phase_id)
);
GRANT select,insert,update,delete ON phase TO GROUP planner;
GRANT select,update ON phase_phase_id_seq TO GROUP planner;
--
-- Day Types
--
CREATE TABLE daytype (
dtype_id serial,
proj_id integer,
name text,
descr text,
is_work boolean NOT NULL DEFAULT FALSE,
is_nonwork boolean NOT NULL DEFAULT FALSE,
UNIQUE (proj_id, name),
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (dtype_id)
);
GRANT select,insert,update,delete ON daytype TO GROUP planner;
GRANT select,update ON daytype_dtype_id_seq TO GROUP planner;
--
-- Calendar
--
CREATE TABLE calendar (
cal_id serial,
proj_id integer,
parent_cid integer,
name text,
day_mon integer DEFAULT NULL,
day_tue integer DEFAULT NULL,
day_wed integer DEFAULT NULL,
day_thu integer DEFAULT NULL,
day_fri integer DEFAULT NULL,
day_sat integer DEFAULT NULL,
day_sun integer DEFAULT NULL,
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (day_mon) REFERENCES daytype (dtype_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (day_tue) REFERENCES daytype (dtype_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (day_wed) REFERENCES daytype (dtype_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (day_thu) REFERENCES daytype (dtype_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (day_fri) REFERENCES daytype (dtype_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (day_sat) REFERENCES daytype (dtype_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (day_sun) REFERENCES daytype (dtype_id)
ON DELETE SET DEFAULT ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (parent_cid) REFERENCES calendar (cal_id)
ON DELETE SET NULL ON UPDATE CASCADE,
PRIMARY KEY (cal_id)
);
GRANT select,insert,update,delete ON calendar TO GROUP planner;
GRANT select,update ON calendar_cal_id_seq TO GROUP planner;
ALTER TABLE project ADD CONSTRAINT project_cal_id
FOREIGN KEY (cal_id) REFERENCES calendar (cal_id)
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- Day
--
CREATE TABLE day (
day_id serial,
cal_id integer,
dtype_id integer,
date date,
FOREIGN KEY (dtype_id) REFERENCES daytype (dtype_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (cal_id) REFERENCES calendar (cal_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (day_id)
);
GRANT select,insert,update,delete ON day TO GROUP planner;
--
-- Day (working) Interval
--
CREATE TABLE day_interval (
cal_id integer,
dtype_id integer,
start_time time with time zone,
end_time time with time zone,
FOREIGN KEY (dtype_id) REFERENCES daytype (dtype_id)
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
FOREIGN KEY (cal_id) REFERENCES calendar (cal_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (dtype_id, cal_id, start_time, end_time)
);
GRANT select,insert,update,delete ON day_interval TO GROUP planner;
--
-- Task
--
CREATE TABLE task (
task_id serial,
parent_id integer,
proj_id integer,
name text NOT NULL,
note text,
start timestamp with time zone,
finish timestamp with time zone,
work integer DEFAULT 0,
duration integer DEFAULT 0,
percent_complete integer DEFAULT 0,
priority integer DEFAULT 0,
is_milestone boolean NOT NULL DEFAULT FALSE,
is_fixed_work boolean NOT NULL DEFAULT TRUE,
constraint_type text NOT NULL DEFAULT 'ASAP',
constraint_time timestamp with time zone,
CHECK (constraint_type = 'ASAP' OR constraint_type = 'MSO' OR constraint_type = 'FNLT' OR constraint_type = 'SNET'),
CHECK (percent_complete > -1 AND percent_complete < 101),
CHECK (priority > -1 AND priority < 10000),
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (parent_id) REFERENCES task (task_id)
ON DELETE SET NULL ON UPDATE CASCADE,
PRIMARY KEY (task_id)
);
GRANT select,insert,update,delete ON task TO GROUP planner;
GRANT select,update ON task_task_id_seq TO GROUP planner;
-- FIXME: Add triggers to handle different types of tasks/milestones
--
-- Predecessor (tasks)
--
CREATE TABLE predecessor (
task_id integer NOT NULL,
pred_task_id integer NOT NULL,
pred_id serial,
type text NOT NULL DEFAULT 'FS',
lag integer DEFAULT 0,
CHECK (type = 'FS' OR type = 'FF' OR type = 'SS' OR type = 'SF'),
UNIQUE (pred_id),
FOREIGN KEY (task_id) REFERENCES task (task_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (pred_task_id) REFERENCES task (task_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (task_id, pred_task_id)
);
GRANT select,insert,update,delete ON predecessor TO GROUP planner;
GRANT select,update ON predecessor_pred_id_seq TO GROUP planner;
--
-- Property types
--
CREATE TABLE property_type (
proptype_id serial,
proj_id integer,
name text NOT NULL,
label text NOT NULL,
type text NOT NULL DEFAULT 'text',
owner text NOT NULL DEFAULT 'project',
descr text,
CHECK (type = 'date' OR type = 'duration' OR type = 'float'
OR type = 'int' OR type = 'text' OR type = 'text-list'
OR type = 'cost'),
CHECK (owner = 'project' OR owner = 'task' OR owner = 'resource'),
UNIQUE (proj_id, name),
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (proptype_id)
);
GRANT select,insert,update,delete ON property_type TO GROUP planner;
GRANT select,update ON property_type_proptype_id_seq TO GROUP planner;
--
-- Properties
--
CREATE TABLE property (
prop_id serial,
proptype_id integer NOT NULL,
value text,
FOREIGN KEY (proptype_id) REFERENCES property_type (proptype_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (prop_id)
);
GRANT select,insert,update,delete ON property TO GROUP planner;
GRANT select,update ON property_prop_id_seq TO GROUP planner;
--
-- Project properties
--
CREATE TABLE project_to_property (
proj_id integer,
prop_id integer,
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (prop_id) REFERENCES property (prop_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (proj_id, prop_id)
);
GRANT select,insert,update,delete ON project_to_property TO GROUP planner;
--
-- Task properties
--
CREATE TABLE task_to_property (
prop_id integer,
task_id integer,
FOREIGN KEY (task_id) REFERENCES task (task_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (prop_id) REFERENCES property (prop_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (task_id, prop_id)
);
GRANT select,insert,update,delete ON task_to_property TO GROUP planner;
--
-- Resource Group
--
CREATE TABLE resource_group (
group_id serial,
proj_id integer,
name text NOT NULL,
admin_name text,
admin_phone text,
admin_email text,
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (group_id)
);
GRANT select,insert,update,delete ON resource_group TO GROUP planner;
GRANT select,update ON resource_group_group_id_seq TO GROUP planner;
--
-- Resource
--
CREATE TABLE resource (
res_id serial,
proj_id integer,
group_id integer,
name text,
short_name text,
email text,
note text,
is_worker boolean NOT NULL DEFAULT TRUE,
units real NOT NULL DEFAULT 1.0,
std_rate real NOT NULL DEFAULT 0.0,
ovt_rate real NOT NULL DEFAULT 0.0,
cal_id integer,
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (group_id) REFERENCES resource_group (group_id)
ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (cal_id) REFERENCES calendar (cal_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (res_id)
);
GRANT select,insert,update,delete ON resource TO GROUP planner;
GRANT select,update ON resource_res_id_seq TO GROUP planner;
--
-- Resource properties
--
CREATE TABLE resource_to_property (
prop_id integer,
res_id integer,
FOREIGN KEY (res_id) REFERENCES resource (res_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (prop_id) REFERENCES property (prop_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (res_id, prop_id)
);
GRANT select,insert,update,delete ON resource_to_property TO GROUP planner;
--
-- Allocations (of resources)
--
CREATE TABLE allocation (
task_id integer,
res_id integer,
units real NOT NULL DEFAULT 1.0,
FOREIGN KEY (res_id) REFERENCES resource (res_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (task_id) REFERENCES task (task_id)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (res_id, task_id)
);
GRANT select,insert,update,delete ON allocation TO GROUP planner;
--
-- Global planner properties
--
CREATE TABLE property_global (
prop_id serial,
prop_name text NOT NULL,
value text,
PRIMARY KEY (prop_id)
);
-- Planner Database Schema update
-- Alvaro del Castillo <acs barrapunto com>
--
-- New table to store global properties for Planner
-- Actually (0.13) it is used to store the database version
CREATE TABLE property_global (
prop_id serial,
prop_name text NOT NULL,
value text,
PRIMARY KEY (prop_id)
);Attachment:
signature.asc
Description: Esta parte del mensaje =?ISO-8859-1?Q?est=E1?= firmada digitalmente