Hi guys! Here goes some update of the work I am doing in the database world in planner. WARNING: The code possible attached to this email is broken and only useful to developers working in planner database stuff. Don't try to use it now if you don't plan to help in database stuff. Will break your planner system and possible, destroy your gold data. 1. Now, when you load a project from the database: 1.1. If the tables doesn't exist they are created using the last version planner can find for database schemas. 1.2. If tables exist and planner find and upgrade path for tables version, planner will execute in database the planner SQL upgrade code. 2. My next problems now are: 2.1 Where to store the planner version for tables? I think the best thing is to have a unique version for all the tables in the planner database, maybe with a global planner table with data about planner (now we will only have there the planner version). 2.2 If there isn't a database, create it using the login and password user give us in the load/save database dialog. I have tried to create solid code in detecting sql files, checking version and this kind of details but you know, we need to test it a lot and find a point when more checks and time invest will be a waste of time. Cheers -- Alvaro
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 4 Jul 2004 09:29:46 -0000
@@ -291,6 +291,163 @@ 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)
+{
+ GDir* dir;
+ const gchar *name;
+ gboolean upgradable=FALSE;
+ gboolean create_db=FALSE;
+ gchar *max_version_database;
+ gchar *max_version_upgrade;
+ gchar *upgrade_file = NULL;
+ gchar *database_file = NULL;
+
+ max_version_database = g_strdup ("0.0");
+ max_version_upgrade = g_strdup ("0.0");
+
+ /* 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[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);
+ }
+ }
+ } else {
+ g_warning ("Incorrect upgrade file name: %s", sql_file);
+ }
+ }
+ /* Complete database */
+ 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);
+ }
+
+ create_db = TRUE;
+ version = g_strdup (versionv[0]);
+ g_message ("Version: %s", version);
+ g_free (version);
+
+ } else {
+ g_warning ("File with no version: %s", sql_file);
+ }
+ if (versionv) {
+ g_strfreev(versionv);
+ }
+ if (namev) {
+ g_strfreev(namev);
+ }
+ g_free (sql_file);
+ }
+
+ if (upgradable) {
+ GdaDataModel *res;
+ gchar *contents;
+
+ g_message ("Upgrading database with: %s", upgrade_file);
+ g_file_get_contents (upgrade_file, &contents, NULL, NULL);
+ res = sql_execute_query (conn, contents);
+ g_free (contents);
+ g_free (upgrade_file);
+ return TRUE;
+ }
+ else if (create_db) {
+ GdaDataModel *res;
+
+ /* Check if tables exist */
+ res = sql_execute_query (conn, "SELECT proj_id FROM project");
+ if (res == NULL) {
+ gchar *contents;
+
+ g_warning ("No tables in database. Creating them!");
+ g_message ("Creating new database with: %s", database_file);
+ g_file_get_contents (database_file, &contents, NULL, NULL);
+ res = sql_execute_query (conn, contents);
+ g_free (contents);
+ }
+ /* Check if upgrade fail (database file newer but not upgrade path)*/
+ else {
+ g_message ("Database already exists");
+ g_object_unref (res);
+ }
+ g_free (database_file);
+ return TRUE;
+ }
+
+ return FALSE;
+}
+
/**
* Display a list with projects and let the user select one. Returns the project
* id of the selected one.
@@ -341,13 +498,20 @@ sql_plugin_retrieve_project_id (PlannerP
return -1;
}
+ /* First thing is to check if database table exists and if not, create them */
+ if (!check_database_tables (conn)) {
+ str = g_strdup_printf (_("Test to tables in database '%s' failed."), database);
+ show_error_dialog (plugin, str);
+ g_free (str);
+ return -1;
+ }
+
res = sql_execute_query (conn, "BEGIN");
if (res == NULL) {
g_warning ("BEGIN command failed.");
return -1;
}
g_object_unref (res);
- res = NULL;
res = sql_execute_query (conn,
"DECLARE mycursor CURSOR FOR SELECT proj_id, name,"
@@ -703,6 +867,7 @@ sql_plugin_save (BonoboUIComponent *comp
gchar *login = NULL;
gchar *password = NULL;
gchar *uri = NULL;
+ const gchar *uri_plan = NULL;
GError *error = NULL;
project = planner_window_get_project (plugin->main_window);
@@ -721,15 +886,37 @@ sql_plugin_save (BonoboUIComponent *comp
* 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