[gcompris/gcomprixogoo] Better error checking in case the datadase cannot be loaded or created.

[Bruno Coudoin <bcoudoin src gnome org>]

commit c18ee881ea1c5931d2455739a3843944183e8405
Author: Bruno Coudoin <bruno coudoin free fr>
Date:   Sat Jan 30 01:03:29 2010 +0100

    Better error checking in case the datadase cannot be loaded or created.
    
    Before that patch, a failure was exiting the startup sequence without warning the user
    Now, in case we cannot load the database of cannot created it we follow the procedure:
    - move the database under the name gcompris_db.broken
    - recreate it from scratch
    - we failed again, fallback in no database mode.

 src/gcompris/gcompris_db.c |  334 ++++++++++++++++++++++++-------------------
 1 files changed, 186 insertions(+), 148 deletions(-)
---
diff --git a/src/gcompris/gcompris_db.c b/src/gcompris/gcompris_db.c
index 988c969..5826c24 100644
--- a/src/gcompris/gcompris_db.c
+++ b/src/gcompris/gcompris_db.c
@@ -123,190 +123,228 @@ static sqlite3 *gcompris_db=NULL;
      END;"
 
 
-gboolean gc_db_init(gboolean disable_database_)
+static void _create_db()
 {
-
-#ifdef USE_SQLITE
-  disable_database = disable_database_;
-  SUPPORT_OR_RETURN(FALSE);
-  gboolean creation = FALSE;
+  gchar *request;
   char *zErrMsg;
   char **result;
   int rc;
   int nrow;
   int ncolumn;
-  gchar *request;
 
-  GcomprisProperties	*properties = gc_prop_get();
+  /* create all tables needed */
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_USERS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_CLASS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_GROUPS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_USERS_IN_GROUPS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_GROUPS_IN_PROFILES, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_ACTIVITIES_OUT, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_PROFILES, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS_PROFILES_CONF, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_INFO, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,CREATE_TABLE_LOGS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
 
-  if (!g_file_test(properties->database, G_FILE_TEST_EXISTS))
-    creation = TRUE;
+  /* CREATE TRIGGERS */
+  rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_CLASS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_GROUPS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_PROFILES, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_USERS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,TRIGGER_INSERT_USERS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
+  rc = sqlite3_exec(gcompris_db,TRIGGER_UPDATE_USERS, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
 
-#ifndef WIN32
-    /* this stat() does not work on WinXP */
-    /* NEEDS CHECKING IN WINXP */
+  g_message("Database tables created");
 
-  else {
-    /* we have to check this file is not empty,
-       because bug in administration */
-    struct stat buf;
+  request = g_strdup_printf(SET_VERSION(VERSION));
 
-    if (g_stat(properties->database, &buf)!=0)
-      g_error("Can't stat %s", properties->database);
+  rc = sqlite3_get_table(gcompris_db,
+			 request,
+			 &result,
+			 &nrow,
+			 &ncolumn,
+			 &zErrMsg
+			 );
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
 
-    /* if size of file is null, we recreate the tables */
-    if (buf.st_size == 0){
-      creation = TRUE;
-      g_message("Database file is empty! Trying to create table...");
-    }
+  rc = sqlite3_exec(gcompris_db,SET_DEFAULT_PROFILE, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
   }
-#endif
 
-  rc = sqlite3_open(properties->database, &gcompris_db);
-  if( rc ){
-    g_message("Can't open database %s : %s\n", properties->database, sqlite3_errmsg(gcompris_db));
-    sqlite3_close(gcompris_db);
-    exit(1);
+  rc = sqlite3_exec(gcompris_db,ACTIVATE_DEFAULT_PROFILE, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
   }
 
-  g_message("Database %s opened", properties->database);
 
-  if (creation){
-    /* create all tables needed */
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_USERS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_CLASS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_GROUPS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_USERS_IN_GROUPS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_GROUPS_IN_PROFILES, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_ACTIVITIES_OUT, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_PROFILES, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS_PROFILES_CONF, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_INFO, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,CREATE_TABLE_LOGS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
+  request = g_strdup_printf("INSERT INTO class (class_id, name, teacher, wholegroup_id) VALUES ( 1, \"%s\", \"(%s)\", 1);",
+			    _("Unaffected"),
+			    _("Users without a class"));
 
-    /* CREATE TRIGGERS */
-    rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_CLASS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_GROUPS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_PROFILES, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_USERS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,TRIGGER_INSERT_USERS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
-    rc = sqlite3_exec(gcompris_db,TRIGGER_UPDATE_USERS, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
+  rc = sqlite3_exec(gcompris_db, request, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
 
-    g_message("Database tables created");
+  rc = sqlite3_exec(gcompris_db,SET_DEFAULT_GROUP, NULL,  0, &zErrMsg);
+  if( rc!=SQLITE_OK ){
+    g_error("SQL error: %s\n", zErrMsg);
+  }
 
-    request = g_strdup_printf(SET_VERSION(VERSION));
 
-    rc = sqlite3_get_table(gcompris_db,
-			   request,
-			   &result,
-			   &nrow,
-			   &ncolumn,
-			   &zErrMsg
-			   );
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
+  sqlite3_free_table(result);
 
-    rc = sqlite3_exec(gcompris_db,SET_DEFAULT_PROFILE, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
+  g_free(request);
+
+}
+
+static gboolean
+_check_db_integrity()
+{
+  char **result;
+  int nrow;
+  int ncolumn;
+  char *zErrMsg;
+  int rc;
+
+  /* Check the db integrity */
+  rc = sqlite3_get_table(gcompris_db,
+			 PRAGMA_INTEGRITY,
+			 &result,
+			 &nrow,
+			 &ncolumn,
+			 &zErrMsg
+			 );
+  if( rc!=SQLITE_OK )
+    {
+      g_message("SQL error: %s\n", zErrMsg);
+      return FALSE;
     }
 
-    rc = sqlite3_exec(gcompris_db,ACTIVATE_DEFAULT_PROFILE, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
+  if (!(strcmp(result[1],"ok")==0))
+    {
+      g_message("DATABASE integrity check returns %s \n", result[1]);
+      return FALSE;
     }
 
+    sqlite3_free_table(result);
 
-    request = g_strdup_printf("INSERT INTO class (class_id, name, teacher, wholegroup_id) VALUES ( 1, \"%s\", \"(%s)\", 1);",
-			      _("Unaffected"),
-			      _("Users without a class"));
+    return TRUE;
+}
 
-    rc = sqlite3_exec(gcompris_db, request, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
+gboolean gc_db_init(gboolean disable_database_)
+{
 
-    rc = sqlite3_exec(gcompris_db,SET_DEFAULT_GROUP, NULL,  0, &zErrMsg);
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
+#ifdef USE_SQLITE
+  disable_database = disable_database_;
+  SUPPORT_OR_RETURN(FALSE);
+  gboolean creation = FALSE;
+  char *zErrMsg;
+  char **result;
+  int rc;
+  int nrow;
+  int ncolumn;
 
+  GcomprisProperties	*properties = gc_prop_get();
 
-    sqlite3_free_table(result);
+  if (!g_file_test(properties->database, G_FILE_TEST_EXISTS))
+    creation = TRUE;
 
-    g_free(request);
+  rc = sqlite3_open(properties->database, &gcompris_db);
+  if( rc ){
+    g_message("Can't open database %s : %s\n", properties->database,
+	      sqlite3_errmsg(gcompris_db));
+    sqlite3_close(gcompris_db);
+    disable_database = TRUE;
+    return FALSE;
+  }
 
+  g_message("Database %s opened", properties->database);
+
+  if (creation){
+    _create_db();
   } else {
-    /* Check the db integrity */
-    rc = sqlite3_get_table(gcompris_db,
-			   PRAGMA_INTEGRITY,
-			   &result,
-			   &nrow,
-			   &ncolumn,
-			   &zErrMsg
-			   );
-    if( rc!=SQLITE_OK ){
-      g_error("SQL error: %s\n", zErrMsg);
-    }
+    if ( ! _check_db_integrity() )
+      {
+	// We failed to load the database, let's
+	// backup it and re create it.
+	sqlite3_close(gcompris_db);
+	gchar *backup = g_strdup_printf("%s.broken", properties->database);
+	g_rename(properties->database, backup);
+	g_message("Database is broken, it is copyed in %s", backup);
+	g_free(backup);
+
+	rc = sqlite3_open(properties->database, &gcompris_db);
+	if( rc ){
+	  g_message("Can't open database %s : %s\n", properties->database,
+		    sqlite3_errmsg(gcompris_db));
+	  sqlite3_close(gcompris_db);
+	  disable_database = TRUE;
+	  return FALSE;
+	}
+	_create_db();
 
-    if (!(strcmp(result[1],"ok")==0))
-      g_error("DATABASE integrity check returns %s \n", result[1]);
+	if ( ! _check_db_integrity() )
+	  {
+	    disable_database = TRUE;
+	    return FALSE;
+	  }
+      }
 
     g_message("Database Integrity ok");
-    sqlite3_free_table(result);
 
     rc = sqlite3_get_table(gcompris_db,
 			   CHECK_VERSION,