Back to index

gcompris  8.2.2
gcompris_db.c
Go to the documentation of this file.
00001 /* gcompris - gcompris_db.c
00002  *
00003  * Copyright (C) 2000 Bruno Coudoin
00004  *
00005  *   This program is free software; you can redistribute it and/or modify
00006  *   it under the terms of the GNU General Public License as published by
00007  *   the Free Software Foundation; either version 2 of the License, or
00008  *   (at your option) any later version.
00009  *
00010  *   This program is distributed in the hope that it will be useful,
00011  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
00012  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
00013  *   GNU General Public License for more details.
00014  *
00015  *   You should have received a copy of the GNU General Public License
00016  *   along with this program; if not, write to the Free Software
00017  *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
00018  */
00019 
00020 #include <string.h>
00021 
00022 #include "gcompris.h"
00023 #include <sys/stat.h>
00024 
00025 
00026 #ifdef USE_SQLITE
00027 static sqlite3 *gcompris_db=NULL;
00028 #endif
00029 
00030 #define CREATE_TABLE_USERS                                     \
00031   "CREATE TABLE users (user_id INT UNIQUE, login TEXT, lastname TEXT, firstname TEXT, birthdate TEXT, class_id INT ); "
00032 #define CREATE_TABLE_CLASS                                     \
00033   "CREATE TABLE class (class_id INT UNIQUE, name TEXT, teacher TEXT, wholegroup_id INT ); "
00034 #define CREATE_TABLE_GROUPS                                    \
00035   "CREATE TABLE groups (group_id INT UNIQUE, name TEXT, class_id INT, description TEXT ); "
00036 #define CREATE_TABLE_USERS_IN_GROUPS                                  \
00037   "CREATE TABLE list_users_in_groups (user_id INT, group_id INT ); "
00038 #define CREATE_TABLE_GROUPS_IN_PROFILES                               \
00039   "CREATE TABLE list_groups_in_profiles (profile_id INT, group_id INT ); "
00040 #define CREATE_TABLE_ACTIVITIES_OUT                                   \
00041   "CREATE TABLE activities_out (board_id INT, type INT, out_id INT ); "
00042 #define CREATE_TABLE_PROFILES                                         \
00043   "CREATE TABLE profiles (profile_id INT UNIQUE, name TEXT, profile_directory TEXT, description TEXT); "
00044 #define CREATE_TABLE_BOARDS_PROFILES_CONF                      \
00045   "CREATE TABLE board_profile_conf (profile_id INT, board_id INT, conf_key TEXT, conf_value TEXT ); "
00046 #define CREATE_TABLE_BOARDS                                    \
00047   "CREATE TABLE boards (board_id INT UNIQUE, name TEXT, section_id INT, section TEXT, author TEXT, type TEXT, mode TEXT, difficulty INT, icon TEXT, boarddir TEXT, mandatory_sound_file TEXT, mandatory_sound_dataset TEXT, filename TEXT, title TEXT, description TEXT, prerequisite TEXT, goal TEXT, manual TEXT, credit TEXT);"
00048 
00049 #define CREATE_TABLE_INFO                                      \
00050   "CREATE TABLE informations (gcompris_version TEXT UNIQUE, init_date TEXTUNIQUE, profile_id INT UNIQUE ); "
00051 
00052 #define PRAGMA_INTEGRITY                  \
00053   "PRAGMA integrity_check; "
00054 
00055 /* WARNING: template for g_strdup_printf */
00056 #define SET_VERSION(v)                                                \
00057   "INSERT INTO informations (gcompris_version) VALUES(\'%s\'); ", v
00058 
00059 #define CHECK_VERSION                            \
00060   "SELECT gcompris_version FROM informations;"
00061 
00062 #define SET_DEFAULT_PROFILE                                    \
00063   "INSERT INTO profiles (profile_id, name, profile_directory, description) VALUES ( 1, \'Default\', \'Default\', \'Default profil for gcompris\');"
00064 #define ACTIVATE_DEFAULT_PROFILE          \
00065   "UPDATE informations SET profile_id=1;"
00066 
00067 #define SET_DEFAULT_GROUP                                      \
00068   "INSERT INTO groups (group_id, name, class_id, description) VALUES ( 1, \'All\', 1, \'All users\');"
00069 
00070 /*
00071  * TRIGGERS
00072  * --------
00073  */
00074 
00075 #define TRIGGER_DELETE_CLASS                                   \
00076   "CREATE TRIGGER delete_class  DELETE ON class\
00077      BEGIN                                                     \
00078        DELETE FROM groups WHERE class_id=old.class_id;                \
00079        UPDATE users SET class_id=1 WHERE class_id=old.class_id;              \
00080      END;"
00081 
00082 #define TRIGGER_DELETE_GROUPS                                         \
00083   "CREATE TRIGGER delete_groups  DELETE ON groups\
00084      BEGIN                                                     \
00085        DELETE FROM list_users_in_groups WHERE group_id=old.group_id;  \
00086        DELETE FROM list_groups_in_profiles WHERE group_id=old.group_id; \
00087      END;"
00088 
00089 #define TRIGGER_DELETE_PROFILES                                       \
00090   "CREATE TRIGGER delete_profiles DELETE ON profiles\
00091      BEGIN                                                     \
00092        DELETE FROM list_groups_in_profiles WHERE profile_id=old.profile_id; \
00093        DELETE FROM board_profile_conf WHERE profile_id=old.profile_id;       \
00094      END;"
00095 
00096 #define TRIGGER_DELETE_USERS                                          \
00097   "CREATE TRIGGER delete_users DELETE ON users\
00098      BEGIN                                                 \
00099        DELETE FROM list_users_in_groups WHERE user_id=old.user_id; \
00100      END;"
00101 
00102 #define TRIGGER_INSERT_USERS                                          \
00103   "CREATE TRIGGER insert_users INSERT ON users\
00104      BEGIN                                                     \
00105        INSERT INTO list_users_in_groups (user_id, group_id) VALUES (new.user_id, (SELECT wholegroup_id FROM class WHERE class_id=new.class_id)); \
00106      END;"
00107 
00108 #define TRIGGER_UPDATE_USERS                                          \
00109   "CREATE TRIGGER update_wholegroup UPDATE OF class_id ON users\
00110      BEGIN                                                 \
00111        UPDATE list_users_in_groups SET group_id=(SELECT wholegroup_id FROM class WHERE class_id=new.class_id) WHERE user_id=new.user_id; \
00112      END;"
00113 
00114 
00115 int gc_db_init()
00116 {
00117 #ifdef USE_SQLITE
00118   gboolean creation = FALSE;
00119   char *zErrMsg;
00120   char **result;
00121   int rc;
00122   int nrow;
00123   int ncolumn;
00124   gchar *request;
00125 
00126   GcomprisProperties *properties = gc_prop_get();
00127 
00128   if (!g_file_test(properties->database, G_FILE_TEST_EXISTS))
00129     creation = TRUE;
00130   else {
00131     /* we have to check this file is not empty,
00132        because bug in administration */
00133     struct stat buf;
00134 
00135     if (stat(properties->database, &buf)!=0)
00136       g_error("Can't stat %s", properties->database);
00137 
00138     /* if size of file is null, we recreate the tables */
00139     if (buf.st_size == 0){
00140       creation = TRUE;
00141       g_warning("Database file is empty! Trying to create table...");
00142     }
00143   }
00144 
00145   rc = sqlite3_open(properties->database, &gcompris_db);
00146   if( rc ){
00147     g_error("Can't open database: %s\n", sqlite3_errmsg(gcompris_db));
00148     sqlite3_close(gcompris_db);
00149     exit(1);
00150   }
00151 
00152   g_warning("Database %s opened", properties->database);
00153 
00154   if (creation){
00155     /* create all tables needed */
00156     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_USERS, NULL,  0, &zErrMsg);
00157     if( rc!=SQLITE_OK ){
00158       g_error("SQL error: %s\n", zErrMsg);
00159     }
00160     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_CLASS, NULL,  0, &zErrMsg);
00161     if( rc!=SQLITE_OK ){
00162       g_error("SQL error: %s\n", zErrMsg);
00163     }
00164     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_GROUPS, NULL,  0, &zErrMsg);
00165     if( rc!=SQLITE_OK ){
00166       g_error("SQL error: %s\n", zErrMsg);
00167     }
00168     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_USERS_IN_GROUPS, NULL,  0, &zErrMsg);
00169     if( rc!=SQLITE_OK ){
00170       g_error("SQL error: %s\n", zErrMsg);
00171     }
00172     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_GROUPS_IN_PROFILES, NULL,  0, &zErrMsg);
00173     if( rc!=SQLITE_OK ){
00174       g_error("SQL error: %s\n", zErrMsg);
00175     }
00176     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_ACTIVITIES_OUT, NULL,  0, &zErrMsg);
00177     if( rc!=SQLITE_OK ){
00178       g_error("SQL error: %s\n", zErrMsg);
00179     }
00180     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_PROFILES, NULL,  0, &zErrMsg);
00181     if( rc!=SQLITE_OK ){
00182       g_error("SQL error: %s\n", zErrMsg);
00183     }
00184     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS_PROFILES_CONF, NULL,  0, &zErrMsg);
00185     if( rc!=SQLITE_OK ){
00186       g_error("SQL error: %s\n", zErrMsg);
00187     }
00188     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS, NULL,  0, &zErrMsg);
00189     if( rc!=SQLITE_OK ){
00190       g_error("SQL error: %s\n", zErrMsg);
00191     }
00192     rc = sqlite3_exec(gcompris_db,CREATE_TABLE_INFO, NULL,  0, &zErrMsg);
00193     if( rc!=SQLITE_OK ){
00194       g_error("SQL error: %s\n", zErrMsg);
00195     }
00196 
00197     /* CREATE TRIGGERS */
00198     rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_CLASS, NULL,  0, &zErrMsg);
00199     if( rc!=SQLITE_OK ){
00200       g_error("SQL error: %s\n", zErrMsg);
00201     }
00202     rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_GROUPS, NULL,  0, &zErrMsg);
00203     if( rc!=SQLITE_OK ){
00204       g_error("SQL error: %s\n", zErrMsg);
00205     }
00206     rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_PROFILES, NULL,  0, &zErrMsg);
00207     if( rc!=SQLITE_OK ){
00208       g_error("SQL error: %s\n", zErrMsg);
00209     }
00210     rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_USERS, NULL,  0, &zErrMsg);
00211     if( rc!=SQLITE_OK ){
00212       g_error("SQL error: %s\n", zErrMsg);
00213     }
00214     rc = sqlite3_exec(gcompris_db,TRIGGER_INSERT_USERS, NULL,  0, &zErrMsg);
00215     if( rc!=SQLITE_OK ){
00216       g_error("SQL error: %s\n", zErrMsg);
00217     }
00218     rc = sqlite3_exec(gcompris_db,TRIGGER_UPDATE_USERS, NULL,  0, &zErrMsg);
00219     if( rc!=SQLITE_OK ){
00220       g_error("SQL error: %s\n", zErrMsg);
00221     }
00222 
00223     g_warning("Database tables created");
00224 
00225     request = g_strdup_printf(SET_VERSION(VERSION));
00226 
00227     rc = sqlite3_get_table(gcompris_db,
00228                         request,
00229                         &result,
00230                         &nrow,
00231                         &ncolumn,
00232                         &zErrMsg
00233                         );
00234     if( rc!=SQLITE_OK ){
00235       g_error("SQL error: %s\n", zErrMsg);
00236     }
00237 
00238     rc = sqlite3_exec(gcompris_db,SET_DEFAULT_PROFILE, NULL,  0, &zErrMsg);
00239     if( rc!=SQLITE_OK ){
00240       g_error("SQL error: %s\n", zErrMsg);
00241     }
00242 
00243     rc = sqlite3_exec(gcompris_db,ACTIVATE_DEFAULT_PROFILE, NULL,  0, &zErrMsg);
00244     if( rc!=SQLITE_OK ){
00245       g_error("SQL error: %s\n", zErrMsg);
00246     }
00247 
00248 
00249     request = g_strdup_printf("INSERT INTO class (class_id, name, teacher, wholegroup_id) VALUES ( 1, \'%s\', \'(%s)\', 1);",
00250                            _("Unaffected"),
00251                            _("Users without a class"));
00252 
00253     rc = sqlite3_exec(gcompris_db, request, NULL,  0, &zErrMsg);
00254     if( rc!=SQLITE_OK ){
00255       g_error("SQL error: %s\n", zErrMsg);
00256     }
00257 
00258     rc = sqlite3_exec(gcompris_db,SET_DEFAULT_GROUP, NULL,  0, &zErrMsg);
00259     if( rc!=SQLITE_OK ){
00260       g_error("SQL error: %s\n", zErrMsg);
00261     }
00262 
00263 
00264     sqlite3_free_table(result);
00265 
00266     g_free(request);
00267 
00268   } else {
00269     /* Check the db integrity */
00270     rc = sqlite3_get_table(gcompris_db,
00271                         PRAGMA_INTEGRITY,
00272                         &result,
00273                         &nrow,
00274                         &ncolumn,
00275                         &zErrMsg
00276                         );
00277     if( rc!=SQLITE_OK ){
00278       g_error("SQL error: %s\n", zErrMsg);
00279     }
00280     if (!(strcmp(result[1],"ok")==0))
00281       g_error("DATABASE integrity check returns %s \n", result[1]);
00282     g_warning("Database Integrity ok");
00283     sqlite3_free_table(result);
00284 
00285     rc = sqlite3_get_table(gcompris_db,
00286                         CHECK_VERSION,
00287                         &result,
00288                         &nrow,
00289                         &ncolumn,
00290                         &zErrMsg
00291                         );
00292     if( rc!=SQLITE_OK ){
00293       g_error("SQL error: %s\n", zErrMsg);
00294     }
00295 
00296     if (strcmp(result[1],VERSION)!=0)
00297       g_warning("Running GCompris is %s, but databse vrsion is %s", VERSION, result[1]);
00298     sqlite3_free_table(result);
00299   }
00300 
00301   return TRUE;
00302 #else
00303   return FALSE;
00304 #endif
00305 }
00306 
00307 void gc_db_exit()
00308 {
00309 #ifdef USE_SQLITE
00310   sqlite3_close(gcompris_db);
00311   g_warning("Database closed");
00312 #endif
00313 }
00314 
00315 #define BOARDS_SET_DATE(date)                           \
00316   "UPDATE informations SET init_date=\'%s\';",date
00317 
00318 void gc_db_set_date(gchar *date)
00319 {
00320 #ifdef USE_SQLITE
00321 
00322   char *zErrMsg;
00323   char **result;
00324   int rc;
00325   int nrow;
00326   int ncolumn;
00327   gchar *request;
00328 
00329   request = g_strdup_printf(BOARDS_SET_DATE(date));
00330   rc = sqlite3_get_table(gcompris_db,
00331                       request,
00332                       &result,
00333                       &nrow,
00334                       &ncolumn,
00335                       &zErrMsg
00336                       );
00337   if( rc!=SQLITE_OK ){
00338     g_error("SQL error: %s\n", zErrMsg);
00339   }
00340   g_free(request);
00341 
00342   sqlite3_free_table(result);
00343 
00344 #endif
00345 }
00346 
00347 #define BOARDS_UPDATE_VERSION(version)                         \
00348   "UPDATE informations SET gcompris_version=\'%s\';",version
00349 
00350 void gc_db_set_version(gchar *version)
00351 {
00352 #ifdef USE_SQLITE
00353 
00354   char *zErrMsg;
00355   char **result;
00356   int rc;
00357   int nrow;
00358   int ncolumn;
00359   gchar *request;
00360 
00361   request = g_strdup_printf(BOARDS_UPDATE_VERSION(version));
00362   rc = sqlite3_get_table(gcompris_db,
00363                       request,
00364                       &result,
00365                       &nrow,
00366                       &ncolumn,
00367                       &zErrMsg
00368                       );
00369   if( rc!=SQLITE_OK ){
00370     g_error("SQL error: %s\n", zErrMsg);
00371   }
00372   g_free(request);
00373 
00374   sqlite3_free_table(result);
00375 
00376 #endif
00377 }
00378 
00379 #define BOARDS_CHECK                                    \
00380   "SELECT gcompris_version, init_date FROM informations;"
00381 gboolean gc_db_check_boards()
00382 {
00383 #ifdef USE_SQLITE
00384 
00385   char *zErrMsg;
00386   char **result;
00387   int rc;
00388   int nrow;
00389   int ncolumn;
00390   gboolean ret_value;
00391 
00392   rc = sqlite3_get_table(gcompris_db,
00393                       BOARDS_CHECK,
00394                       &result,
00395                       &nrow,
00396                       &ncolumn,
00397                       &zErrMsg
00398                       );
00399   if( rc!=SQLITE_OK ){
00400     g_error("SQL error: %s\n", zErrMsg);
00401   }
00402 
00403   ret_value = (strcmp(result[2],VERSION)==0) && (result[3] != NULL);
00404 
00405   sqlite3_free_table(result);
00406 
00407   return ret_value;
00408 #else
00409   return FALSE;
00410 #endif
00411 }
00412 
00413 
00414 #define BOARD_INSERT                                           \
00415   "INSERT OR REPLACE INTO boards VALUES (%d, %Q, %d, %Q, %Q, %Q, %Q, %d, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q, %Q);"
00416 
00417 #define MAX_BOARD_ID                      \
00418   "SELECT MAX(board_id) FROM boards;"
00419 
00420 #define SECTION_ID(s)                                          \
00421   "SELECT section_id FROM boards WHERE section=\'%s\';",s
00422 
00423 #define MAX_SECTION_ID                           \
00424   "SELECT MAX(section_id) FROM boards;"
00425 
00426 #define CHECK_BOARD(n)                                  \
00427   "SELECT board_id FROM boards WHERE name=\'%s\';",n
00428 
00429 
00430 void
00431 gc_db_board_update(guint *board_id,
00432                  guint *section_id,
00433                  gchar *name,
00434                  gchar *section,
00435                  gchar *author,
00436                  gchar *type,
00437                  gchar *mode,
00438                  int difficulty,
00439                  gchar *icon,
00440                  gchar *boarddir,
00441                  gchar *mandatory_sound_file,
00442                  gchar *mandatory_sound_dataset,
00443                  gchar *filename,
00444                  gchar *title,
00445                  gchar *description,
00446                  gchar *prerequisite,
00447                  gchar *goal,
00448                  gchar *manual,
00449                  gchar *credit
00450                  )
00451 {
00452 #ifdef USE_SQLITE
00453 
00454   char *zErrMsg;
00455   char **result;
00456   int rc;
00457   int nrow;
00458   int ncolumn;
00459   gchar *request;
00460 
00461   if (gcompris_db == NULL)
00462     g_error("Database is closed !!!");
00463 
00464   if (*board_id==0){
00465     /* board not yet registered */
00466 
00467     /* assume name is unique */
00468 
00469     request = g_strdup_printf(CHECK_BOARD(name));
00470 
00471     rc = sqlite3_get_table(gcompris_db,
00472                         request,
00473                         &result,
00474                         &nrow,
00475                         &ncolumn,
00476                         &zErrMsg
00477                         );
00478 
00479     if( rc!=SQLITE_OK ){
00480       g_error("SQL error: %s\n", zErrMsg);
00481     }
00482 
00483     g_free(request);
00484 
00485     if (nrow != 0){
00486       *board_id = atoi(result[1]);
00487       sqlite3_free_table(result);
00488     } else {
00489 
00490       /* get last board_id written */
00491       rc = sqlite3_get_table(gcompris_db,
00492                           MAX_BOARD_ID,
00493                           &result,
00494                           &nrow,
00495                           &ncolumn,
00496                           &zErrMsg
00497                           );
00498 
00499       if( rc!=SQLITE_OK ){
00500        g_error("SQL error: %s\n", zErrMsg);
00501       }
00502 
00503       if (result[1] == NULL)
00504        *board_id = 1;
00505       else
00506        *board_id = atoi(result[1]) + 1;
00507 
00508       sqlite3_free_table(result);
00509 
00510     }
00511   }
00512 
00513   /* get section_id */
00514   request = g_strdup_printf(SECTION_ID(section));
00515 
00516   rc = sqlite3_get_table(gcompris_db,
00517                       request,
00518                       &result,
00519                       &nrow,
00520                       &ncolumn,
00521                       &zErrMsg
00522                       );
00523 
00524   g_free(request);
00525 
00526   if( rc!=SQLITE_OK ){
00527     g_error("SQL error: %s\n", zErrMsg);
00528   }
00529 
00530   if (nrow == 0){
00531 
00532     /* get max section_id */
00533 
00534     rc = sqlite3_get_table(gcompris_db,
00535                         MAX_SECTION_ID,
00536                         &result,
00537                         &nrow,
00538                         &ncolumn,
00539                         &zErrMsg
00540                         );
00541 
00542 
00543     if( rc!=SQLITE_OK ){
00544       g_error("SQL error: %s\n", zErrMsg);
00545     }
00546 
00547     if (result[1] == NULL){
00548       *section_id = 1;
00549     } else {
00550       *section_id = atoi(result[1]) + 1;
00551     }
00552     sqlite3_free_table(result);
00553   } else {
00554     *section_id = atoi(result[1]);
00555     sqlite3_free_table(result);
00556   }
00557 
00558   request = sqlite3_mprintf( BOARD_INSERT,
00559                           *board_id,
00560                           name,
00561                           *section_id,
00562                           section,
00563                           author,
00564                           type,
00565                           mode,
00566                           difficulty,
00567                           icon,
00568                           boarddir,
00569                           mandatory_sound_file,
00570                           mandatory_sound_dataset,
00571                           filename,
00572                           title,
00573                           description,
00574                           prerequisite,
00575                           goal,
00576                           manual,
00577                           credit
00578                           );
00579 
00580   rc = sqlite3_get_table(gcompris_db,
00581                       request,
00582                       &result,
00583                       &nrow,
00584                       &ncolumn,
00585                       &zErrMsg
00586                       );
00587 
00588   if( rc!=SQLITE_OK ){
00589     g_error("SQL error: %s\n", zErrMsg);
00590   }
00591 
00592   sqlite3_free_table(result);
00593 
00594   sqlite3_free(request);
00595 
00596 #endif
00597 }
00598 
00599 
00600 #define BOARDS_READ                                            \
00601   "SELECT board_id ,name, section_id, section, author, type, mode, difficulty, icon, boarddir, mandatory_sound_file, mandatory_sound_dataset, filename, title, description, prerequisite, goal, manual, credit FROM boards;"
00602 
00603 GList *gc_menu_load_db(GList *boards_list)
00604 {
00605 #ifdef USE_SQLITE
00606 
00607   GcomprisProperties *properties = gc_prop_get();
00608 
00609   GList *boards = boards_list;
00610 
00611   char *zErrMsg;
00612   char **result;
00613   int rc;
00614   int nrow;
00615   int ncolumn;
00616   int i;
00617 
00618   rc = sqlite3_get_table(gcompris_db,
00619                       BOARDS_READ,
00620                       &result,
00621                       &nrow,
00622                       &ncolumn,
00623                       &zErrMsg
00624                       );
00625 
00626   if( rc!=SQLITE_OK ){
00627     g_error("SQL error: %s\n", zErrMsg);
00628   }
00629 
00630   /* first ncolumns are columns labels. */
00631   i = ncolumn;
00632 
00633   while (i < (nrow +1)*ncolumn) {
00634     GcomprisBoard *gcomprisBoard = NULL;
00635 
00636     gcomprisBoard = g_malloc0 (sizeof (GcomprisBoard));
00637 
00638     gcomprisBoard->plugin=NULL;
00639     gcomprisBoard->previous_board=NULL;
00640     gcomprisBoard->board_ready=FALSE;
00641     gcomprisBoard->canvas=gc_get_canvas();
00642 
00643     gcomprisBoard->gmodule      = NULL;
00644     gcomprisBoard->gmodule_file = NULL;
00645 
00646     /* From DB we have only package_data_dir. */
00647     gcomprisBoard->board_dir = g_strdup(properties->package_data_dir);
00648 
00649     /* Fixed since I use the canvas own pixel_per_unit scheme */
00650     gcomprisBoard->width  = BOARDWIDTH;
00651     gcomprisBoard->height = BOARDHEIGHT;
00652 
00653 
00654     gcomprisBoard->board_id = atoi(result[i++]);
00655     gcomprisBoard->name = g_strdup(result[i++]);
00656     gcomprisBoard->section_id = atoi(result[i++]);
00657     gcomprisBoard->section = g_strdup(result[i++]);
00658     gcomprisBoard->author = g_strdup(result[i++]);
00659     gcomprisBoard->type = g_strdup(result[i++]);
00660     gcomprisBoard->mode = g_strdup(result[i++]);
00661     gcomprisBoard->difficulty = g_strdup(result[i++]);
00662     gcomprisBoard->icon_name = g_strdup(result[i++]);
00663     gcomprisBoard->boarddir = g_strdup(result[i++]);
00664     gcomprisBoard->mandatory_sound_file = g_strdup(result[i++]);
00665     gcomprisBoard->mandatory_sound_dataset = g_strdup(result[i++]);
00666     gcomprisBoard->filename = g_strdup(result[i++]);
00667     gcomprisBoard->title =  reactivate_newline(gettext(result[i++]));
00668     gcomprisBoard->description  = reactivate_newline(gettext(result[i++]));
00669     gcomprisBoard->prerequisite = reactivate_newline(gettext(result[i++]));
00670     gcomprisBoard->goal = reactivate_newline(gettext(result[i++]));
00671     gcomprisBoard->manual = reactivate_newline(gettext(result[i++]));
00672     gcomprisBoard->credit = reactivate_newline(gettext(result[i++]));
00673 
00674     boards = g_list_append(boards, gcomprisBoard);
00675   }
00676 
00677   sqlite3_free_table(result);
00678 
00679   return boards;
00680 
00681 #else
00682   return NULL;
00683 #endif
00684 }
00685 
00686 GList *gc_db_read_board_from_section(gchar *section)
00687 {
00688   return NULL;
00689 }
00690 
00691 
00692 #define BOARD_ID_READ                            \
00693   "SELECT board_id FROM boards;"
00694 
00695 GList *gc_db_get_board_id(GList *list)
00696 {
00697 #ifdef USE_SQLITE
00698 
00699   GList *board_id_list = list;
00700 
00701   char *zErrMsg;
00702   char **result;
00703   int rc;
00704   int nrow;
00705   int ncolumn;
00706   int i;
00707 
00708   rc = sqlite3_get_table(gcompris_db,
00709                       BOARD_ID_READ,
00710                       &result,
00711                       &nrow,
00712                       &ncolumn,
00713                       &zErrMsg
00714                       );
00715 
00716   if( rc!=SQLITE_OK ){
00717     g_error("SQL error: %s\n", zErrMsg);
00718   }
00719 
00720   /* first ncolumns are columns labels. */
00721   i = ncolumn;
00722 
00723   while (i < (nrow +1)*ncolumn) {
00724     int *board_id = g_malloc(sizeof(int));
00725 
00726     *board_id = atoi(result[i++]);
00727     board_id_list = g_list_append(board_id_list, board_id);
00728   }
00729 
00730   return  board_id_list;
00731 
00732 #else
00733   return list;
00734 #endif
00735 }
00736 
00737 #define DELETE_BOARD(table, board_id)                   \
00738   "DELETE FROM %s WHERE board_id=%d;", table, board_id
00739 
00740 void gc_db_remove_board(int board_id)
00741 {
00742 #ifdef USE_SQLITE
00743   g_warning("Supress board %d from db.", board_id);
00744 
00745   char *zErrMsg;
00746   char **result;
00747   int rc;
00748   int nrow;
00749   int ncolumn;
00750   gchar *request;
00751 
00752   /* get section_id */
00753   request = g_strdup_printf(DELETE_BOARD("boards",board_id));
00754 
00755   rc = sqlite3_get_table(gcompris_db,
00756                       request,
00757                       &result,
00758                       &nrow,
00759                       &ncolumn,
00760                       &zErrMsg
00761                       );
00762 
00763   if( rc!=SQLITE_OK ){
00764     g_error("SQL error: %s\n", zErrMsg);
00765   }
00766 
00767   g_free(request);
00768 
00769 
00770   /* get section_id */
00771   request = g_strdup_printf(DELETE_BOARD("board_profile_conf",board_id));
00772 
00773   rc = sqlite3_get_table(gcompris_db,
00774                       request,
00775                       &result,
00776                       &nrow,
00777                       &ncolumn,
00778                       &zErrMsg
00779                       );
00780 
00781   if( rc!=SQLITE_OK ){
00782     g_error("SQL error: %s\n", zErrMsg);
00783   }
00784 
00785   g_free(request);
00786 
00787 
00788   /* get section_id */
00789   request = g_strdup_printf(DELETE_BOARD("activities_out",board_id));
00790 
00791   rc = sqlite3_get_table(gcompris_db,
00792                       request,
00793                       &result,
00794                       &nrow,
00795                       &ncolumn,
00796                       &zErrMsg
00797                       );
00798 
00799   if( rc!=SQLITE_OK ){
00800     g_error("SQL error: %s\n", zErrMsg);
00801   }
00802 
00803   g_free(request);
00804 #endif
00805 }
00806 
00807 
00808 #define GET_PROFILE(n)                                                \
00809   "SELECT name, profile_directory, description FROM profiles WHERE profile_id=%d;",n
00810 
00811 #define GET_PROFILE_FROM_NAME(n)                               \
00812   "SELECT profile_id, profile_directory, description FROM profiles WHERE name='%s';",n
00813 
00814 #define GET_GROUPS_IN_PROFILE(n)                               \
00815   "SELECT group_id FROM list_groups_in_profiles WHERE profile_id=%d;",n
00816 
00817 #define GET_ACTIVITIES_OUT_OF_PROFILE(n)                \
00818   "SELECT board_id FROM activities_out WHERE out_id=%d;",n
00819 
00820 GcomprisProfile *gc_db_get_profile_from_id(gint profile_id)
00821 {
00822 #ifdef USE_SQLITE
00823   GcomprisProfile *profile = NULL;
00824 
00825   char *zErrMsg;
00826   char **result;
00827   int rc;
00828   int nrow;
00829   int ncolumn;
00830   gchar *request;
00831 
00832   int i;
00833   GList *ids;
00834   /* get section_id */
00835   request = g_strdup_printf(GET_PROFILE(profile_id));
00836 
00837 
00838   rc = sqlite3_get_table(gcompris_db,
00839                       request,
00840                       &result,
00841                       &nrow,
00842                       &ncolumn,
00843                       &zErrMsg
00844                       );
00845 
00846   if( rc!=SQLITE_OK ){
00847     g_error("SQL error: %s\n", zErrMsg);
00848   }
00849 
00850   if (nrow != 0){
00851     profile = g_malloc0(sizeof(GcomprisProfile));
00852 
00853     profile->profile_id = profile_id;
00854 
00855 
00856     profile->name = g_strdup(result[3]);
00857     profile->directory = g_strdup(result[4]);
00858     profile->description = g_strdup(result[5]);
00859     sqlite3_free_table(result);
00860     g_free(request);
00861 
00862     request = g_strdup_printf(GET_GROUPS_IN_PROFILE(profile->profile_id));
00863 
00864     rc = sqlite3_get_table(gcompris_db,
00865                         request,
00866                         &result,
00867                         &nrow,
00868                         &ncolumn,
00869                         &zErrMsg
00870                         );
00871 
00872     if( rc!=SQLITE_OK ){
00873       g_error("SQL error: %s\n", zErrMsg);
00874     }
00875 
00876     g_free(request);
00877 
00878     if (nrow == 0){
00879       g_warning("No users' groups for profile %s", profile->name);
00880       profile->group_ids = NULL;
00881     } else {
00882       ids = NULL;
00883 
00884       i = ncolumn;
00885       while (i < (nrow +1)*ncolumn) {
00886        int *group_id = g_malloc(sizeof(int));
00887 
00888        *group_id = atoi(result[i++]);
00889        ids = g_list_append(ids, group_id);
00890       }
00891       profile->group_ids = ids;
00892     }
00893     sqlite3_free_table(result);
00894 
00895     request = g_strdup_printf(GET_ACTIVITIES_OUT_OF_PROFILE(profile->profile_id));
00896     rc = sqlite3_get_table(gcompris_db,
00897                         request,
00898                         &result,
00899                         &nrow,
00900                         &ncolumn,
00901                         &zErrMsg
00902                         );
00903 
00904     if( rc!=SQLITE_OK ){
00905       g_error("SQL error: %s\n", zErrMsg);
00906     }
00907 
00908     g_free(request);
00909 
00910     if (nrow == 0){
00911       g_warning("No activities for profile %s", profile->name);
00912       profile->activities = NULL;
00913     } else {
00914       ids = NULL;
00915 
00916       i = ncolumn;
00917       while (i < (nrow +1)*ncolumn) {
00918        int *board_id = g_malloc(sizeof(int));
00919 
00920        *board_id = atoi(result[i++]);
00921        ids = g_list_append(ids, board_id);
00922       }
00923       profile->activities = ids;
00924     }
00925     sqlite3_free_table(result);
00926   }
00927 
00928   return profile;
00929 #else
00930   return NULL;
00931 #endif
00932 }
00933 
00940 GcomprisProfile *
00941 gc_db_profile_from_name_get(gchar *profile_name)
00942 {
00943 #ifdef USE_SQLITE
00944   GcomprisProfile *profile = NULL;
00945 
00946   char *zErrMsg;
00947   char **result;
00948   int rc;
00949   int nrow;
00950   int ncolumn;
00951   gchar *request;
00952 
00953   /* get section_id */
00954   request = g_strdup_printf(GET_PROFILE_FROM_NAME(profile_name));
00955 
00956   rc = sqlite3_get_table(gcompris_db,
00957                       request,
00958                       &result,
00959                       &nrow,
00960                       &ncolumn,
00961                       &zErrMsg
00962                       );
00963 
00964   if( rc!=SQLITE_OK ){
00965     g_error("SQL error: %s\n", zErrMsg);
00966   }
00967 
00968   if (nrow != 0){
00969     gint profile_id;
00970 
00971     profile_id  = atoi(result[3]);
00972 
00973     g_free(request);
00974 
00975     profile = gc_db_get_profile_from_id(profile_id);
00976 
00977   }
00978 
00979 
00980   return profile;
00981 #else
00982   return NULL;
00983 #endif
00984 }
00985 
00986 
00987 
00988 #define GET_ACTIVE_PROFILE_ID                    \
00989   "SELECT profile_id FROM informations;"
00990 
00991 GcomprisProfile *gc_db_get_profile()
00992 {
00993 #ifdef USE_SQLITE
00994   char *zErrMsg;
00995   char **result;
00996   int rc;
00997   int nrow;
00998   int ncolumn;
00999   int profile_id;
01000 
01001   rc = sqlite3_get_table(gcompris_db,
01002                       GET_ACTIVE_PROFILE_ID,
01003                       &result,
01004                       &nrow,
01005                       &ncolumn,
01006                       &zErrMsg
01007                       );
01008 
01009   if( rc!=SQLITE_OK ){
01010     g_error("SQL error: %s\n", zErrMsg);
01011   }
01012 
01013   profile_id = atoi(result[1]);
01014 
01015   sqlite3_free_table(result);
01016 
01017   return gc_db_get_profile_from_id(profile_id);
01018 
01019 #else
01020   return NULL;
01021 #endif
01022 }
01023 
01024 #define USERS_FROM_GROUP(n)                                    \
01025   "SELECT users.user_id, users.login, users.lastname, users.firstname, users.birthdate, users.class_id  FROM users, list_users_in_groups WHERE users.user_id = list_users_in_groups.user_id AND list_users_in_groups.group_id = %d;",n
01026 
01027 GList *gc_db_users_from_group_get(gint group_id)
01028 {
01029 #ifdef USE_SQLITE
01030   char *zErrMsg;
01031   char **result;
01032   int rc;
01033   int nrow;
01034   int ncolumn;
01035   gchar *request;
01036 
01037   int i;
01038   GList *users = NULL;
01039 
01040   request = g_strdup_printf(USERS_FROM_GROUP(group_id));
01041   rc = sqlite3_get_table(gcompris_db,
01042                       request,
01043                       &result,
01044                       &nrow,
01045                       &ncolumn,
01046                       &zErrMsg
01047                       );
01048 
01049   if( rc!=SQLITE_OK ){
01050     g_error("SQL error: %s\n", zErrMsg);
01051   }
01052 
01053   g_free(request);
01054 
01055   if (nrow == 0){
01056     g_warning("No users in the group id %d", group_id);
01057   } else {
01058     i = ncolumn;
01059     while (i < (nrow +1)*ncolumn) {
01060       GcomprisUser *user = g_malloc0(sizeof(GcomprisUser));
01061 
01062       user->user_id = atoi(result[i++]);
01063       user->login = g_strdup(result[i++]);
01064       user->lastname = g_strdup(result[i++]);
01065       user->firstname = g_strdup(result[i++]);
01066       user->birthdate = g_strdup(result[i++]);
01067       user->class_id = atoi(result[i++]);
01068 
01069       users = g_list_append(users, user);
01070     }
01071   }
01072 
01073   return users;
01074 #else
01075   return NULL;
01076 #endif
01077 }
01078 
01079 #define USER_FROM_ID(n)                                               \
01080   "SELECT users.login, lastname, firstname, birthdate, class_id  FROM users WHERE user_id = %d;",n
01081 
01082 GcomprisUser *gc_db_get_user_from_id(gint user_id)
01083 {
01084 #ifdef USE_SQLITE
01085   char *zErrMsg;
01086   char **result;
01087   int rc;
01088   int nrow;
01089   int ncolumn;
01090   gchar *request;
01091   int i;
01092   GcomprisUser *user = NULL;
01093 
01094   request = g_strdup_printf(USER_FROM_ID(user_id));
01095   rc = sqlite3_get_table(gcompris_db,
01096                       request,
01097                       &result,
01098                       &nrow,
01099                       &ncolumn,
01100                       &zErrMsg
01101                       );
01102 
01103   if( rc!=SQLITE_OK ){
01104     g_error("SQL error: %s\n", zErrMsg);
01105   }
01106 
01107   g_free(request);
01108 
01109   if (nrow == 0){
01110     g_warning("No user with id  %d", user_id);
01111     return NULL;
01112   } else {
01113     i = ncolumn;
01114     user = g_malloc0(sizeof(GcomprisUser));
01115 
01116     user->user_id = user_id;
01117     user->login = g_strdup(result[i++]);
01118     user->lastname = g_strdup(result[i++]);
01119     user->firstname = g_strdup(result[i++]);
01120     user->birthdate = g_strdup(result[i++]);
01121     user->class_id = atoi(result[i++]);
01122   }
01123 
01124 
01125   return user ;
01126 #else
01127   return NULL;
01128 #endif
01129 }
01130 
01131 #define CLASS_FROM_ID(n)                                       \
01132   "SELECT name, teacher, wholegroup_id  FROM class WHERE class_id = %d;",n
01133 
01134 #define GROUPS_IN_CLASS(n)                       \
01135   "SELECT group_id  FROM groups WHERE class_id = %d;",n
01136 
01137 GcomprisClass *gc_db_get_class_from_id(gint class_id)
01138 {
01139 #ifdef USE_SQLITE
01140   char *zErrMsg;
01141   char **result;
01142   int rc;
01143   int nrow;
01144   int ncolumn;
01145   gchar *request;
01146 
01147   int i;
01148   GcomprisClass *class = NULL;
01149 
01150   request = g_strdup_printf(CLASS_FROM_ID(class_id));
01151   rc = sqlite3_get_table(gcompris_db,
01152                       request,
01153                       &result,
01154                       &nrow,
01155                       &ncolumn,
01156                       &zErrMsg
01157                       );
01158 
01159   if( rc!=SQLITE_OK ){
01160     g_error("SQL error: %s\n", zErrMsg);
01161   }
01162 
01163   g_free(request);
01164 
01165   if (nrow == 0){
01166     g_warning("No class with id %d", class_id);
01167     return NULL;
01168     return NULL;
01169   } else {
01170     i = ncolumn;
01171 
01172     class = g_malloc0(sizeof(GcomprisClass));
01173 
01174     class->class_id = class_id;
01175     class->name = g_strdup(result[i++]);
01176     class->description = g_strdup(result[i++]);
01177     class->wholegroup_id = atoi(result[i++]);
01178   }
01179 
01180   /* Group _ids */
01181 
01182   GList *group_ids = NULL;
01183 
01184   request = g_strdup_printf(GROUPS_IN_CLASS(class_id));
01185 
01186   rc = sqlite3_get_table(gcompris_db,
01187                       request,
01188                       &result,
01189                       &nrow,
01190                       &ncolumn,
01191                       &zErrMsg
01192                       );
01193 
01194   if( rc!=SQLITE_OK ){
01195     g_error("SQL error: %s\n", zErrMsg);
01196   }
01197 
01198   g_free(request);
01199 
01200   if (nrow == 0){
01201     g_error("No groups found for class id %d: there must be at least one for the whole class with id (%d)",
01202            class_id, class->wholegroup_id);
01203     g_free(class);
01204     class = NULL;
01205   } else {
01206 
01207     i = ncolumn;
01208     while (i < (nrow +1)*ncolumn) {
01209       int *group_id = g_malloc(sizeof(int));
01210 
01211       *group_id = atoi(result[i++]);
01212       group_ids = g_list_append(group_ids, group_id);
01213     }
01214     class->group_ids = group_ids;
01215   }
01216 
01217   return class ;
01218 #else
01219   return NULL;
01220 #endif
01221 }
01222 
01223 
01224 #define CHECK_CONF                                             \
01225   "SELECT * FROM board_profile_conf WHERE profile_id=%d AND board_id=%d AND conf_key=%Q;"
01226 
01227 #define INSERT_KEY                                             \
01228   "INSERT INTO board_profile_conf (profile_id, board_id, conf_key, conf_value) VALUES (%d, %d, %Q, %Q);"
01229 
01230 #define UPDATE_KEY                                             \
01231   "UPDATE board_profile_conf SET conf_value=%Q WHERE profile_id=%d AND board_id=%d AND conf_key=%Q;"
01232 
01233 void gc_db_set_board_conf(GcomprisProfile *profile,
01234                        GcomprisBoard  *board,
01235                        gchar *key,
01236                        gchar *value)
01237 {
01238 #ifdef USE_SQLITE
01239   char *zErrMsg;
01240   char **result;
01241   int rc;
01242   int nrow;
01243   int ncolumn;
01244   gchar *request;
01245 
01246   request = sqlite3_mprintf(CHECK_CONF,
01247                          profile->profile_id,
01248                          board->board_id,
01249                          key);
01250 
01251   rc = sqlite3_get_table(gcompris_db,
01252                       request,
01253                       &result,
01254                       &nrow,
01255                       &ncolumn,
01256                       &zErrMsg
01257                       );
01258 
01259   if( rc!=SQLITE_OK ){
01260     g_error("SQL error: %s\n", zErrMsg);
01261   }
01262 
01263   sqlite3_free(request);
01264 
01265   if (nrow == 0){
01266     request = sqlite3_mprintf(INSERT_KEY,
01267                            profile->profile_id,
01268                            board->board_id,
01269                            key,
01270                            value);
01271 
01272     rc = sqlite3_get_table(gcompris_db,
01273                         request,
01274                         &result,
01275                         &nrow,
01276                         &ncolumn,
01277                         &zErrMsg
01278                         );
01279 
01280     if( rc!=SQLITE_OK ){
01281       g_error("SQL error: %s\n", zErrMsg);
01282     }
01283 
01284     sqlite3_free(request);
01285   } else {
01286     request = sqlite3_mprintf(UPDATE_KEY,
01287                            value,
01288                            profile->profile_id,
01289                            board->board_id,
01290                            key
01291                            );
01292 
01293     rc = sqlite3_get_table(gcompris_db,
01294                         request,
01295                         &result,
01296                         &nrow,
01297                         &ncolumn,
01298                         &zErrMsg
01299                         );
01300 
01301     if( rc!=SQLITE_OK ){
01302       g_error("SQL error: %s\n", zErrMsg);
01303     }
01304 
01305     sqlite3_free(request);
01306   }
01307 #endif
01308 }
01309 
01310 #define GET_CONF(p, b)                                                \
01311   "SELECT conf_key, conf_value FROM board_profile_conf WHERE profile_id=%d AND board_id=%d;", p, b
01312 
01313 GHashTable *gc_db_conf_with_table_get(int profile_id, int board_id, GHashTable *table )
01314 {
01315   GHashTable *hash_conf = table;
01316 
01317 #ifdef USE_SQLITE
01318   char *zErrMsg;
01319   char **result;
01320   int rc;
01321   int nrow;
01322   int ncolumn;
01323   gchar *request;
01324   int i;
01325 
01326   request = g_strdup_printf(GET_CONF(profile_id,
01327                                  board_id));
01328 
01329   g_warning ( "Request get_conf : %s", request);
01330 
01331   rc = sqlite3_get_table(gcompris_db,
01332                       request,
01333                       &result,
01334                       &nrow,
01335                       &ncolumn,
01336                       &zErrMsg
01337                       );
01338 
01339   if( rc!=SQLITE_OK ){
01340     g_error("SQL error: %s\n", zErrMsg);
01341   }
01342 
01343   g_free(request);
01344 
01345   for ( i=ncolumn; i < (nrow +1)*ncolumn; i+=2){
01346     if (strcmp(result[i+1],"NULL")!=0){
01347       /* "NULL" values are ignored */
01348       g_hash_table_replace (hash_conf,
01349                          g_strdup(result[i]),
01350                          g_strdup(result[i+1]));
01351       g_warning("get_conf: put key %s value %s in the hash",
01352               result[i],
01353               result[i+1]);
01354     }
01355   }
01356 
01357   sqlite3_free_table(result);
01358 #endif
01359   return hash_conf;
01360 }
01361 
01362 GHashTable *gc_db_get_conf(GcomprisProfile *profile, GcomprisBoard  *board )
01363 {
01364   GHashTable *hash_result = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
01365 
01366 #ifdef USE_SQLITE
01367   return gc_db_conf_with_table_get( profile->profile_id, board->board_id, hash_result) ;
01368 #else
01369   return hash_result;
01370 #endif
01371 }
01372 
01373 GHashTable *gc_db_get_board_conf()
01374 {
01375   GHashTable *hash_result = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
01376 
01377   /* priority order : board + Profile conf, else profile Default (all boards) conf, if not  Default profile + board */
01378 
01379   /* conf values for default profile and current board */
01380   hash_result = gc_db_conf_with_table_get(1,
01381                                      gc_board_get_current()->board_id,
01382                                      hash_result);
01383 
01384   /* conf values for profile (board independant) */
01385   if(gc_profile_get_current()) {
01386     hash_result = gc_db_conf_with_table_get(gc_profile_get_current()->profile_id,
01387                                        -1,
01388                                        hash_result);
01389 
01390     /* conf value for current profile and current board */
01391     hash_result = gc_db_conf_with_table_get(gc_profile_get_current()->profile_id,
01392                                        gc_board_get_current()->board_id,
01393                                        hash_result);
01394   }
01395 
01396   return hash_result;
01397 }
01398 
01399 #define GET_ALL_PROFILES                                       \
01400   "SELECT profile_id, name, profile_directory, description FROM profiles;"
01401 
01402 
01403 GList *gc_db_profiles_list_get()
01404 {
01405 #ifdef USE_SQLITE
01406 
01407 
01408   char *zErrMsg;
01409   char **result;
01410   int rc;
01411   int nrow;
01412   int ncolumn;
01413   gchar *request;
01414 
01415   int i;
01416   GList *profiles_list = NULL;
01417 
01418   char **result_;
01419   int nrow_;
01420   int ncolumn_;
01421 
01422   int i_;
01423   GList *ids_;
01424 
01425 
01426   rc = sqlite3_get_table(gcompris_db,
01427                       GET_ALL_PROFILES,
01428                       &result,
01429                       &nrow,
01430                       &ncolumn,
01431                       &zErrMsg
01432                       );
01433 
01434   if( rc!=SQLITE_OK ){
01435     g_error("SQL error: %s\n", zErrMsg);
01436   }
01437 
01438   if (nrow == 0)
01439     return NULL;
01440 
01441   i = ncolumn;
01442   while (i < (nrow +1)*ncolumn) {
01443     GcomprisProfile *profile = g_malloc0(sizeof(GcomprisProfile));
01444 
01445     profile->profile_id = atoi(result[i++]);
01446 
01447     profile->name = g_strdup(result[i++]);
01448     profile->directory = g_strdup(result[i++]);
01449     profile->description = g_strdup(result[i++]);
01450 
01451     request = g_strdup_printf(GET_GROUPS_IN_PROFILE(profile->profile_id));
01452 
01453     rc = sqlite3_get_table(gcompris_db,
01454                         request,
01455                         &result_,
01456                         &nrow_,
01457                         &ncolumn_,
01458                         &zErrMsg
01459                         );
01460 
01461     if( rc!=SQLITE_OK ){
01462       g_error("SQL error: %s\n", zErrMsg);
01463     }
01464 
01465     g_free(request);
01466 
01467     if (nrow_ == 0){
01468       g_warning("No users groups for profile %s", profile->name);
01469       profile->group_ids = NULL;
01470     } else {
01471       ids_ = NULL;
01472 
01473       i_ = ncolumn_;
01474       while (i_ < (nrow_ +1)*ncolumn_) {
01475        int *group_id = g_malloc(sizeof(int));
01476 
01477        *group_id = atoi(result_[i_++]);
01478        ids_ = g_list_append(ids_, group_id);
01479       }
01480       profile->group_ids = ids_;
01481     }
01482 
01483     sqlite3_free_table(result_);
01484 
01485     request = g_strdup_printf(GET_ACTIVITIES_OUT_OF_PROFILE(profile->profile_id));
01486     rc = sqlite3_get_table(gcompris_db,
01487                         request,
01488                         &result_,
01489                         &nrow_,
01490                         &ncolumn_,
01491                         &zErrMsg
01492                         );
01493 
01494     if( rc!=SQLITE_OK ){
01495       g_error("SQL error: %s\n", zErrMsg);
01496     }
01497 
01498     g_free(request);
01499 
01500     if (nrow_ == 0){
01501       g_warning("No activities out for profile %s", profile->name);
01502       profile->activities = NULL;
01503     } else {
01504       ids_ = NULL;
01505 
01506       i_ = ncolumn_;
01507       while (i_ < (nrow_ +1)*ncolumn_) {
01508        int *board_id = g_malloc(sizeof(int));
01509 
01510        *board_id = atoi(result_[i_++]);
01511        ids_ = g_list_append(ids_, board_id);
01512       }
01513       profile->activities = ids_;
01514     }
01515 
01516     sqlite3_free_table(result_);
01517     profiles_list = g_list_append( profiles_list, profile);
01518   }
01519 
01520   sqlite3_free_table(result);
01521 
01522   return profiles_list;
01523 #else
01524   return NULL;
01525 #endif
01526 }
01527 
01528 #define GROUP_FROM_ID(n)                                       \
01529   "SELECT name, class_id, description FROM groups WHERE group_id=%d;",n
01530 
01531 GcomprisGroup *gc_db_get_group_from_id(int group_id)
01532 {
01533 #ifdef USE_SQLITE
01534 
01535   char *zErrMsg;
01536   char **result;
01537   int rc;
01538   int nrow;
01539   int ncolumn;
01540   gchar *request;
01541 
01542   int i;
01543   GcomprisGroup *group = NULL;
01544 
01545   request = g_strdup_printf(GROUP_FROM_ID(group_id));
01546   rc = sqlite3_get_table(gcompris_db,
01547                       request,
01548                       &result,
01549                       &nrow,
01550                       &ncolumn,
01551                       &zErrMsg
01552                       );
01553 
01554   if( rc!=SQLITE_OK ){
01555     g_error("SQL error: %s\n", zErrMsg);
01556   }
01557 
01558   g_free(request);
01559 
01560   if (nrow == 0){
01561     g_warning("No group with id  %d", group_id);
01562     return NULL;
01563   } else {
01564     i = ncolumn;
01565 
01566     group = g_malloc0(sizeof(GcomprisGroup));
01567 
01568     group->group_id = group_id;
01569     group->name = g_strdup(result[i++]);
01570     group->class_id = atoi(result[i++]);
01571     group->description = g_strdup(result[i++]);
01572   }
01573 
01574   group->user_ids = gc_db_users_from_group_get(group_id);
01575 
01576   return group ;
01577 
01578 #else
01579   return NULL;
01580 #endif
01581 }
01582 
01583 #define GET_ALL_GROUPS                                         \
01584   "SELECT group_id, name, class_id, description FROM groups;"
01585 
01586 GList *gc_db_get_groups_list()
01587 {
01588 #ifdef USE_SQLITE
01589   GList *groups_list = NULL;
01590 
01591   char *zErrMsg;
01592   char **result;
01593   int rc;
01594   int nrow;
01595   int ncolumn;
01596   int i;
01597   GcomprisGroup *group = NULL;
01598 
01599   rc = sqlite3_get_table(gcompris_db,
01600                       GET_ALL_GROUPS,
01601                       &result,
01602                       &nrow,
01603                       &ncolumn,
01604                       &zErrMsg
01605                       );
01606 
01607   if( rc!=SQLITE_OK ){
01608     g_error("SQL error: %s\n", zErrMsg);
01609   }
01610 
01611   if (nrow == 0){
01612     g_warning("No groups !");
01613     return NULL;
01614   } else {
01615     i = ncolumn;
01616 
01617     while ( i < (nrow +1)*ncolumn) {
01618       group = g_malloc0(sizeof(GcomprisGroup));
01619 
01620       group->group_id =  atoi(result[i++]);
01621       group->name = g_strdup(result[i++]);
01622       group->class_id = atoi(result[i++]);
01623       group->description = g_strdup(result[i++]);
01624 
01625       group->user_ids = gc_db_users_from_group_get(group->group_id);
01626 
01627       groups_list = g_list_append(groups_list, group);
01628     }
01629   }
01630 
01631   return groups_list;
01632 
01633 #else
01634   return NULL;
01635 #endif
01636 }
01637 
01638 
01639 #define BOARDS_READ_FROM_ID(n)                                        \
01640   "SELECT name, section_id, section, author, type, mode, difficulty, icon, boarddir, mandatory_sound_file, mandatory_sound_dataset, filename, title, description, prerequisite, goal, manual, credit FROM boards WHERE board_id=%d;",n
01641 
01642 GcomprisBoard *gc_db_get_board_from_id(int board_id)
01643 {
01644 #ifdef USE_SQLITE
01645 
01646   GcomprisProperties *properties = gc_prop_get();
01647 
01648   char *zErrMsg;
01649   char **result;
01650   int rc;
01651   int nrow;
01652   int ncolumn;
01653   int i;
01654   gchar *request;
01655 
01656   request = g_strdup_printf(BOARDS_READ_FROM_ID(board_id));
01657 
01658   rc = sqlite3_get_table(gcompris_db,
01659                       request,
01660                       &result,
01661                       &nrow,
01662                       &ncolumn,
01663                       &zErrMsg
01664                       );
01665 
01666   if( rc!=SQLITE_OK ){
01667     g_error("SQL error: %s\n", zErrMsg);
01668   }
01669 
01670   g_free(request);
01671 
01672   /* first ncolumns are columns labels. */
01673   i = ncolumn;
01674 
01675   GcomprisBoard *gcomprisBoard = NULL;
01676 
01677   gcomprisBoard = g_malloc0 (sizeof (GcomprisBoard));
01678 
01679 
01680   gcomprisBoard->plugin=NULL;
01681   gcomprisBoard->previous_board=NULL;
01682   gcomprisBoard->board_ready=FALSE;
01683   gcomprisBoard->canvas=gc_get_canvas();
01684 
01685   gcomprisBoard->gmodule      = NULL;
01686   gcomprisBoard->gmodule_file = NULL;
01687 
01688   /* From DB we have only package_data_dir. */
01689   gcomprisBoard->board_dir = g_strdup_printf(properties->package_data_dir);
01690 
01691   /* Fixed since I use the canvas own pixel_per_unit scheme */
01692   gcomprisBoard->width  = BOARDWIDTH;
01693   gcomprisBoard->height = BOARDHEIGHT;
01694 
01695 
01696   gcomprisBoard->board_id = board_id;
01697   gcomprisBoard->name = g_strdup(result[i++]);
01698   gcomprisBoard->section_id = atoi(result[i++]);
01699   gcomprisBoard->section = g_strdup(result[i++]);
01700   gcomprisBoard->author = g_strdup(result[i++]);
01701   gcomprisBoard->type = g_strdup(result[i++]);
01702   gcomprisBoard->mode = g_strdup(result[i++]);
01703   gcomprisBoard->difficulty = g_strdup(result[i++]);
01704   gcomprisBoard->icon_name = g_strdup(result[i++]);
01705   gcomprisBoard->boarddir = g_strdup(result[i++]);
01706   gcomprisBoard->mandatory_sound_file = g_strdup(result[i++]);
01707   gcomprisBoard->mandatory_sound_dataset = g_strdup(result[i++]);
01708   gcomprisBoard->filename = g_strdup(result[i++]);
01709   gcomprisBoard->title =  reactivate_newline(gettext(result[i++]));
01710   gcomprisBoard->description  = reactivate_newline(gettext(result[i++]));
01711   gcomprisBoard->prerequisite = reactivate_newline(gettext(result[i++]));
01712   gcomprisBoard->goal = reactivate_newline(gettext(result[i++]));
01713   gcomprisBoard->manual = reactivate_newline(gettext(result[i++]));
01714   gcomprisBoard->credit = reactivate_newline(gettext(result[i++]));
01715 
01716   sqlite3_free_table(result);
01717 
01718   return gcomprisBoard;
01719 #else
01720   return NULL;
01721 #endif
01722 }
01723 
01724 #define GET_ALL_USERS                                                 \
01725   "SELECT user_id, login, lastname, firstname, birthdate, class_id FROM users;"
01726 
01727 GList *gc_db_get_users_list()
01728 {
01729 #ifdef USE_SQLITE
01730   GList *users_list = NULL;
01731 
01732   char *zErrMsg;
01733   char **result;
01734   int rc;
01735   int nrow;
01736   int ncolumn;
01737   int i;
01738   GcomprisUser *user = NULL;
01739 
01740   rc = sqlite3_get_table(gcompris_db,
01741                       GET_ALL_USERS,
01742                       &result,
01743                       &nrow,
01744                       &ncolumn,
01745                       &zErrMsg
01746                       );
01747 
01748   if( rc!=SQLITE_OK ){
01749     g_error("SQL error: %s\n", zErrMsg);
01750   }
01751 
01752   if (nrow == 0){
01753     g_warning("No users !");
01754     return NULL;
01755   } else {
01756     i = ncolumn;
01757 
01758     while ( i < (nrow +1)*ncolumn) {
01759       user = g_malloc0(sizeof(GcomprisUser));
01760 
01761       user->user_id =  atoi(result[i++]);
01762       user->login = g_strdup(result[i++]);
01763       user->firstname = g_strdup(result[i++]);
01764       user->lastname = g_strdup(result[i++]);
01765       user->birthdate = g_strdup(result[i++]);
01766       user->class_id = atoi(result[i++]);
01767 
01768       users_list = g_list_append(users_list, user);
01769     }
01770   }
01771 
01772   return users_list;
01773 
01774 #else
01775   return NULL;
01776 #endif
01777 }
01778 
01779 #define GET_ALL_CLASSES                                        \
01780   "SELECT class_id, name, teacher, wholegroup_id FROM class;"
01781 
01782 GList *gc_db_get_classes_list()
01783 {
01784 #ifdef USE_SQLITE
01785   GList *classes_list = NULL;
01786 
01787   char *zErrMsg;
01788   char **result;
01789   int rc;
01790   int nrow;
01791   int ncolumn;
01792   int i;
01793   GcomprisClass *class = NULL;
01794 
01795   rc = sqlite3_get_table(gcompris_db,
01796                       GET_ALL_CLASSES,
01797                       &result,
01798                       &nrow,
01799                       &ncolumn,
01800                       &zErrMsg
01801                       );
01802 
01803   if( rc!=SQLITE_OK ){
01804     g_error("SQL error: %s\n", zErrMsg);
01805   }
01806 
01807   if (nrow == 0){
01808     g_warning("No groups !");
01809     return NULL;
01810   } else {
01811     i = ncolumn;
01812 
01813     while ( i < (nrow +1)*ncolumn) {
01814       class = g_malloc0(sizeof(GcomprisClass));
01815 
01816       class->class_id =  atoi(result[i++]);
01817       class->name = g_strdup(result[i++]);
01818       class->description = g_strdup(result[i++]);
01819       class->wholegroup_id = atoi(result[i++]);
01820 
01821       classes_list = g_list_append(classes_list, class);
01822     }
01823   }
01824 
01825   return classes_list;
01826 
01827 #else
01828   return NULL;
01829 #endif
01830 }
01831 
01832 /* Special request, return true if an activity name is disabled in the profile */
01833 #define DB_IS_ACTIVITY_IN_PROFILE_ID(profile_id, name)                \
01834   "SELECT activities_out.board_id FROM activities_out, boards WHERE boards.name='%s' AND activities_out.out_id='%d' AND activities_out.board_id=boards.board_id;", name, profile_id
01835 
01836 int gc_db_is_activity_in_profile(GcomprisProfile *profile, char *activity_name)
01837 {
01838 #ifdef USE_SQLITE
01839   char *zErrMsg;
01840   char **result;
01841   int rc;
01842   int nrow;
01843   int ncolumn;
01844   gchar *request;
01845 
01846   request = g_strdup_printf(DB_IS_ACTIVITY_IN_PROFILE_ID(profile->profile_id, activity_name));
01847 
01848   rc = sqlite3_get_table(gcompris_db,
01849                       request,
01850                       &result,
01851                       &nrow,
01852                       &ncolumn,
01853                       &zErrMsg
01854                       );
01855 
01856   g_free(request);
01857 
01858   if( rc!=SQLITE_OK ){
01859     g_error("SQL error: %s\n", zErrMsg);
01860   }
01861 
01862   if (nrow == 0){
01863     /* IS IN THE PROFILE */
01864     return TRUE;
01865   }
01866 
01867   /* IS NOT IN THE PROFILE */
01868   return FALSE;
01869 
01870 #else
01871   return TRUE;
01872 #endif
01873 }