1
/* gcompris - gcompris_db.c
3
* Copyright (C) 2000 Bruno Coudoin
5
* This program is free software; you can redistribute it and/or modify
6
* it under the terms of the GNU General Public License as published by
7
* the Free Software Foundation; either version 2 of the License, or
8
* (at your option) any later version.
10
* This program is distributed in the hope that it will be useful,
11
* but WITHOUT ANY WARRANTY; without even the implied warranty of
12
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13
* GNU General Public License for more details.
15
* You should have received a copy of the GNU General Public License
16
* along with this program; if not, write to the Free Software
17
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
27
static sqlite3 *gcompris_db=NULL;
30
#define CREATE_TABLE_USERS \
31
"CREATE TABLE users (user_id INT UNIQUE, login TEXT, lastname TEXT, firstname TEXT, birthdate TEXT, class_id INT ); "
32
#define CREATE_TABLE_CLASS \
33
"CREATE TABLE class (class_id INT UNIQUE, name TEXT, teacher TEXT, wholegroup_id INT ); "
34
#define CREATE_TABLE_GROUPS \
35
"CREATE TABLE groups (group_id INT UNIQUE, name TEXT, class_id INT, description TEXT ); "
36
#define CREATE_TABLE_USERS_IN_GROUPS \
37
"CREATE TABLE list_users_in_groups (user_id INT, group_id INT ); "
38
#define CREATE_TABLE_GROUPS_IN_PROFILES \
39
"CREATE TABLE list_groups_in_profiles (profile_id INT, group_id INT ); "
40
#define CREATE_TABLE_ACTIVITIES_OUT \
41
"CREATE TABLE activities_out (board_id INT, type INT, out_id INT ); "
42
#define CREATE_TABLE_PROFILES \
43
"CREATE TABLE profiles (profile_id INT UNIQUE, name TEXT, profile_directory TEXT, description TEXT); "
44
#define CREATE_TABLE_BOARDS_PROFILES_CONF \
45
"CREATE TABLE board_profile_conf (profile_id INT, board_id INT, conf_key TEXT, conf_value TEXT ); "
46
#define CREATE_TABLE_BOARDS \
47
"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);"
49
#define CREATE_TABLE_INFO \
50
"CREATE TABLE informations (gcompris_version TEXT UNIQUE, init_date TEXTUNIQUE, profile_id INT UNIQUE ); "
52
#define PRAGMA_INTEGRITY \
53
"PRAGMA integrity_check; "
55
/* WARNING: template for g_strdup_printf */
56
#define SET_VERSION(v) \
57
"INSERT INTO informations (gcompris_version) VALUES(\'%s\'); ", v
59
#define CHECK_VERSION \
60
"SELECT gcompris_version FROM informations;"
62
#define SET_DEFAULT_PROFILE \
63
"INSERT INTO profiles (profile_id, name, profile_directory, description) VALUES ( 1, \'Default\', \'Default\', \'Default profil for gcompris\');"
64
#define ACTIVATE_DEFAULT_PROFILE \
65
"UPDATE informations SET profile_id=1;"
67
#define SET_DEFAULT_GROUP \
68
"INSERT INTO groups (group_id, name, class_id, description) VALUES ( 1, \'All\', 1, \'All users\');"
75
#define TRIGGER_DELETE_CLASS \
76
"CREATE TRIGGER delete_class DELETE ON class\
78
DELETE FROM groups WHERE class_id=old.class_id; \
79
UPDATE users SET class_id=1 WHERE class_id=old.class_id; \
82
#define TRIGGER_DELETE_GROUPS \
83
"CREATE TRIGGER delete_groups DELETE ON groups\
85
DELETE FROM list_users_in_groups WHERE group_id=old.group_id; \
86
DELETE FROM list_groups_in_profiles WHERE group_id=old.group_id; \
89
#define TRIGGER_DELETE_PROFILES \
90
"CREATE TRIGGER delete_profiles DELETE ON profiles\
92
DELETE FROM list_groups_in_profiles WHERE profile_id=old.profile_id; \
93
DELETE FROM board_profile_conf WHERE profile_id=old.profile_id; \
96
#define TRIGGER_DELETE_USERS \
97
"CREATE TRIGGER delete_users DELETE ON users\
99
DELETE FROM list_users_in_groups WHERE user_id=old.user_id; \
102
#define TRIGGER_INSERT_USERS \
103
"CREATE TRIGGER insert_users INSERT ON users\
105
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)); \
108
#define TRIGGER_UPDATE_USERS \
109
"CREATE TRIGGER update_wholegroup UPDATE OF class_id ON users\
111
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; \
118
gboolean creation = FALSE;
126
GcomprisProperties *properties = gc_prop_get();
128
if (!g_file_test(properties->database, G_FILE_TEST_EXISTS))
131
/* we have to check this file is not empty,
132
because bug in administration */
135
if (stat(properties->database, &buf)!=0)
136
g_error("Can't stat %s", properties->database);
138
/* if size of file is null, we recreate the tables */
139
if (buf.st_size == 0){
141
g_warning("Database file is empty! Trying to create table...");
145
rc = sqlite3_open(properties->database, &gcompris_db);
147
g_error("Can't open database: %s\n", sqlite3_errmsg(gcompris_db));
148
sqlite3_close(gcompris_db);
152
g_warning("Database %s opened", properties->database);
155
/* create all tables needed */
156
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_USERS, NULL, 0, &zErrMsg);
158
g_error("SQL error: %s\n", zErrMsg);
160
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_CLASS, NULL, 0, &zErrMsg);
162
g_error("SQL error: %s\n", zErrMsg);
164
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_GROUPS, NULL, 0, &zErrMsg);
166
g_error("SQL error: %s\n", zErrMsg);
168
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_USERS_IN_GROUPS, NULL, 0, &zErrMsg);
170
g_error("SQL error: %s\n", zErrMsg);
172
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_GROUPS_IN_PROFILES, NULL, 0, &zErrMsg);
174
g_error("SQL error: %s\n", zErrMsg);
176
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_ACTIVITIES_OUT, NULL, 0, &zErrMsg);
178
g_error("SQL error: %s\n", zErrMsg);
180
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_PROFILES, NULL, 0, &zErrMsg);
182
g_error("SQL error: %s\n", zErrMsg);
184
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS_PROFILES_CONF, NULL, 0, &zErrMsg);
186
g_error("SQL error: %s\n", zErrMsg);
188
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_BOARDS, NULL, 0, &zErrMsg);
190
g_error("SQL error: %s\n", zErrMsg);
192
rc = sqlite3_exec(gcompris_db,CREATE_TABLE_INFO, NULL, 0, &zErrMsg);
194
g_error("SQL error: %s\n", zErrMsg);
197
/* CREATE TRIGGERS */
198
rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_CLASS, NULL, 0, &zErrMsg);
200
g_error("SQL error: %s\n", zErrMsg);
202
rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_GROUPS, NULL, 0, &zErrMsg);
204
g_error("SQL error: %s\n", zErrMsg);
206
rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_PROFILES, NULL, 0, &zErrMsg);
208
g_error("SQL error: %s\n", zErrMsg);
210
rc = sqlite3_exec(gcompris_db,TRIGGER_DELETE_USERS, NULL, 0, &zErrMsg);
212
g_error("SQL error: %s\n", zErrMsg);
214
rc = sqlite3_exec(gcompris_db,TRIGGER_INSERT_USERS, NULL, 0, &zErrMsg);
216
g_error("SQL error: %s\n", zErrMsg);
218
rc = sqlite3_exec(gcompris_db,TRIGGER_UPDATE_USERS, NULL, 0, &zErrMsg);
220
g_error("SQL error: %s\n", zErrMsg);
223
g_warning("Database tables created");
225
request = g_strdup_printf(SET_VERSION(VERSION));
227
rc = sqlite3_get_table(gcompris_db,
235
g_error("SQL error: %s\n", zErrMsg);
238
rc = sqlite3_exec(gcompris_db,SET_DEFAULT_PROFILE, NULL, 0, &zErrMsg);
240
g_error("SQL error: %s\n", zErrMsg);
243
rc = sqlite3_exec(gcompris_db,ACTIVATE_DEFAULT_PROFILE, NULL, 0, &zErrMsg);
245
g_error("SQL error: %s\n", zErrMsg);
249
request = g_strdup_printf("INSERT INTO class (class_id, name, teacher, wholegroup_id) VALUES ( 1, \'%s\', \'(%s)\', 1);",
251
_("Users without a class"));
253
rc = sqlite3_exec(gcompris_db, request, NULL, 0, &zErrMsg);
255
g_error("SQL error: %s\n", zErrMsg);
258
rc = sqlite3_exec(gcompris_db,SET_DEFAULT_GROUP, NULL, 0, &zErrMsg);
260
g_error("SQL error: %s\n", zErrMsg);
264
sqlite3_free_table(result);
269
/* Check the db integrity */
270
rc = sqlite3_get_table(gcompris_db,
278
g_error("SQL error: %s\n", zErrMsg);
280
if (!(strcmp(result[1],"ok")==0))
281
g_error("DATABASE integrity check returns %s \n", result[1]);
282
g_warning("Database Integrity ok");
283
sqlite3_free_table(result);
285
rc = sqlite3_get_table(gcompris_db,
293
g_error("SQL error: %s\n", zErrMsg);
296
if (strcmp(result[1],VERSION)!=0)
297
g_warning("Running GCompris is %s, but databse vrsion is %s", VERSION, result[1]);
298
sqlite3_free_table(result);
310
sqlite3_close(gcompris_db);
311
g_warning("Database closed");
315
#define BOARDS_SET_DATE(date) \
316
"UPDATE informations SET init_date=\'%s\';",date
318
void gc_db_set_date(gchar *date)
329
request = g_strdup_printf(BOARDS_SET_DATE(date));
330
rc = sqlite3_get_table(gcompris_db,
338
g_error("SQL error: %s\n", zErrMsg);
342
sqlite3_free_table(result);
347
#define BOARDS_UPDATE_VERSION(version) \
348
"UPDATE informations SET gcompris_version=\'%s\';",version
350
void gc_db_set_version(gchar *version)
361
request = g_strdup_printf(BOARDS_UPDATE_VERSION(version));
362
rc = sqlite3_get_table(gcompris_db,
370
g_error("SQL error: %s\n", zErrMsg);
374
sqlite3_free_table(result);
379
#define BOARDS_CHECK \
380
"SELECT gcompris_version, init_date FROM informations;"
381
gboolean gc_db_check_boards()
392
rc = sqlite3_get_table(gcompris_db,
400
g_error("SQL error: %s\n", zErrMsg);
403
ret_value = (strcmp(result[2],VERSION)==0) && (result[3] != NULL);
405
sqlite3_free_table(result);
414
#define BOARD_INSERT \
415
"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);"
417
#define MAX_BOARD_ID \
418
"SELECT MAX(board_id) FROM boards;"
420
#define SECTION_ID(s) \
421
"SELECT section_id FROM boards WHERE section=\'%s\';",s
423
#define MAX_SECTION_ID \
424
"SELECT MAX(section_id) FROM boards;"
426
#define CHECK_BOARD(n) \
427
"SELECT board_id FROM boards WHERE name=\'%s\';",n
431
gc_db_board_update(guint *board_id,
441
gchar *mandatory_sound_file,
442
gchar *mandatory_sound_dataset,
461
if (gcompris_db == NULL)
462
g_error("Database is closed !!!");
465
/* board not yet registered */
467
/* assume name is unique */
469
request = g_strdup_printf(CHECK_BOARD(name));
471
rc = sqlite3_get_table(gcompris_db,
480
g_error("SQL error: %s\n", zErrMsg);
486
*board_id = atoi(result[1]);
487
sqlite3_free_table(result);
490
/* get last board_id written */
491
rc = sqlite3_get_table(gcompris_db,
500
g_error("SQL error: %s\n", zErrMsg);
503
if (result[1] == NULL)
506
*board_id = atoi(result[1]) + 1;
508
sqlite3_free_table(result);
514
request = g_strdup_printf(SECTION_ID(section));
516
rc = sqlite3_get_table(gcompris_db,
527
g_error("SQL error: %s\n", zErrMsg);
532
/* get max section_id */
534
rc = sqlite3_get_table(gcompris_db,
544
g_error("SQL error: %s\n", zErrMsg);
547
if (result[1] == NULL){
550
*section_id = atoi(result[1]) + 1;
552
sqlite3_free_table(result);
554
*section_id = atoi(result[1]);
555
sqlite3_free_table(result);
558
request = sqlite3_mprintf( BOARD_INSERT,
569
mandatory_sound_file,
570
mandatory_sound_dataset,
580
rc = sqlite3_get_table(gcompris_db,
589
g_error("SQL error: %s\n", zErrMsg);
592
sqlite3_free_table(result);
594
sqlite3_free(request);
600
#define BOARDS_READ \
601
"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;"
603
GList *gc_menu_load_db(GList *boards_list)
607
GcomprisProperties *properties = gc_prop_get();
609
GList *boards = boards_list;
618
rc = sqlite3_get_table(gcompris_db,
627
g_error("SQL error: %s\n", zErrMsg);
630
/* first ncolumns are columns labels. */
633
while (i < (nrow +1)*ncolumn) {
634
GcomprisBoard *gcomprisBoard = NULL;
636
gcomprisBoard = g_malloc0 (sizeof (GcomprisBoard));
638
gcomprisBoard->plugin=NULL;
639
gcomprisBoard->previous_board=NULL;
640
gcomprisBoard->board_ready=FALSE;
641
gcomprisBoard->canvas=gc_get_canvas();
643
gcomprisBoard->gmodule = NULL;
644
gcomprisBoard->gmodule_file = NULL;
646
/* From DB we have only package_data_dir. */
647
gcomprisBoard->board_dir = g_strdup(properties->package_data_dir);
649
/* Fixed since I use the canvas own pixel_per_unit scheme */
650
gcomprisBoard->width = BOARDWIDTH;
651
gcomprisBoard->height = BOARDHEIGHT;
654
gcomprisBoard->board_id = atoi(result[i++]);
655
gcomprisBoard->name = g_strdup(result[i++]);
656
gcomprisBoard->section_id = atoi(result[i++]);
657
gcomprisBoard->section = g_strdup(result[i++]);
658
gcomprisBoard->author = g_strdup(result[i++]);
659
gcomprisBoard->type = g_strdup(result[i++]);
660
gcomprisBoard->mode = g_strdup(result[i++]);
661
gcomprisBoard->difficulty = g_strdup(result[i++]);
662
gcomprisBoard->icon_name = g_strdup(result[i++]);
663
gcomprisBoard->boarddir = g_strdup(result[i++]);
664
gcomprisBoard->mandatory_sound_file = g_strdup(result[i++]);
665
gcomprisBoard->mandatory_sound_dataset = g_strdup(result[i++]);
666
gcomprisBoard->filename = g_strdup(result[i++]);
667
gcomprisBoard->title = reactivate_newline(gettext(result[i++]));
668
gcomprisBoard->description = reactivate_newline(gettext(result[i++]));
669
gcomprisBoard->prerequisite = reactivate_newline(gettext(result[i++]));
670
gcomprisBoard->goal = reactivate_newline(gettext(result[i++]));
671
gcomprisBoard->manual = reactivate_newline(gettext(result[i++]));
672
gcomprisBoard->credit = reactivate_newline(gettext(result[i++]));
674
boards = g_list_append(boards, gcomprisBoard);
677
sqlite3_free_table(result);
686
GList *gc_db_read_board_from_section(gchar *section)
692
#define BOARD_ID_READ \
693
"SELECT board_id FROM boards;"
695
GList *gc_db_get_board_id(GList *list)
699
GList *board_id_list = list;
708
rc = sqlite3_get_table(gcompris_db,
717
g_error("SQL error: %s\n", zErrMsg);
720
/* first ncolumns are columns labels. */
723
while (i < (nrow +1)*ncolumn) {
724
int *board_id = g_malloc(sizeof(int));
726
*board_id = atoi(result[i++]);
727
board_id_list = g_list_append(board_id_list, board_id);
730
return board_id_list;
737
#define DELETE_BOARD(table, board_id) \
738
"DELETE FROM %s WHERE board_id=%d;", table, board_id
740
void gc_db_remove_board(int board_id)
743
g_warning("Supress board %d from db.", board_id);
753
request = g_strdup_printf(DELETE_BOARD("boards",board_id));
755
rc = sqlite3_get_table(gcompris_db,
764
g_error("SQL error: %s\n", zErrMsg);
771
request = g_strdup_printf(DELETE_BOARD("board_profile_conf",board_id));
773
rc = sqlite3_get_table(gcompris_db,
782
g_error("SQL error: %s\n", zErrMsg);
789
request = g_strdup_printf(DELETE_BOARD("activities_out",board_id));
791
rc = sqlite3_get_table(gcompris_db,
800
g_error("SQL error: %s\n", zErrMsg);
808
#define GET_PROFILE(n) \
809
"SELECT name, profile_directory, description FROM profiles WHERE profile_id=%d;",n
811
#define GET_PROFILE_FROM_NAME(n) \
812
"SELECT profile_id, profile_directory, description FROM profiles WHERE name='%s';",n
814
#define GET_GROUPS_IN_PROFILE(n) \
815
"SELECT group_id FROM list_groups_in_profiles WHERE profile_id=%d;",n
817
#define GET_ACTIVITIES_OUT_OF_PROFILE(n) \
818
"SELECT board_id FROM activities_out WHERE out_id=%d;",n
820
GcomprisProfile *gc_db_get_profile_from_id(gint profile_id)
823
GcomprisProfile *profile = NULL;
835
request = g_strdup_printf(GET_PROFILE(profile_id));
838
rc = sqlite3_get_table(gcompris_db,
847
g_error("SQL error: %s\n", zErrMsg);
851
profile = g_malloc0(sizeof(GcomprisProfile));
853
profile->profile_id = profile_id;
856
profile->name = g_strdup(result[3]);
857
profile->directory = g_strdup(result[4]);
858
profile->description = g_strdup(result[5]);
859
sqlite3_free_table(result);
862
request = g_strdup_printf(GET_GROUPS_IN_PROFILE(profile->profile_id));
864
rc = sqlite3_get_table(gcompris_db,
873
g_error("SQL error: %s\n", zErrMsg);
879
g_warning("No users' groups for profile %s", profile->name);
880
profile->group_ids = NULL;
885
while (i < (nrow +1)*ncolumn) {
886
int *group_id = g_malloc(sizeof(int));
888
*group_id = atoi(result[i++]);
889
ids = g_list_append(ids, group_id);
891
profile->group_ids = ids;
893
sqlite3_free_table(result);
895
request = g_strdup_printf(GET_ACTIVITIES_OUT_OF_PROFILE(profile->profile_id));
896
rc = sqlite3_get_table(gcompris_db,
905
g_error("SQL error: %s\n", zErrMsg);
911
g_warning("No activities for profile %s", profile->name);
912
profile->activities = NULL;
917
while (i < (nrow +1)*ncolumn) {
918
int *board_id = g_malloc(sizeof(int));
920
*board_id = atoi(result[i++]);
921
ids = g_list_append(ids, board_id);
923
profile->activities = ids;
925
sqlite3_free_table(result);
934
/** \brief Given a profile name, return a GcomprisProfile struct
936
* \param profile_name: the profile to retrieve.
938
* \return *GcomprisProfile
941
gc_db_profile_from_name_get(gchar *profile_name)
944
GcomprisProfile *profile = NULL;
954
request = g_strdup_printf(GET_PROFILE_FROM_NAME(profile_name));
956
rc = sqlite3_get_table(gcompris_db,
965
g_error("SQL error: %s\n", zErrMsg);
971
profile_id = atoi(result[3]);
975
profile = gc_db_get_profile_from_id(profile_id);
988
#define GET_ACTIVE_PROFILE_ID \
989
"SELECT profile_id FROM informations;"
991
GcomprisProfile *gc_db_get_profile()
1001
rc = sqlite3_get_table(gcompris_db,
1002
GET_ACTIVE_PROFILE_ID,
1009
if( rc!=SQLITE_OK ){
1010
g_error("SQL error: %s\n", zErrMsg);
1013
profile_id = atoi(result[1]);
1015
sqlite3_free_table(result);
1017
return gc_db_get_profile_from_id(profile_id);
1024
#define USERS_FROM_GROUP(n) \
1025
"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
1027
GList *gc_db_users_from_group_get(gint group_id)
1038
GList *users = NULL;
1040
request = g_strdup_printf(USERS_FROM_GROUP(group_id));
1041
rc = sqlite3_get_table(gcompris_db,
1049
if( rc!=SQLITE_OK ){
1050
g_error("SQL error: %s\n", zErrMsg);
1056
g_warning("No users in the group id %d", group_id);
1059
while (i < (nrow +1)*ncolumn) {
1060
GcomprisUser *user = g_malloc0(sizeof(GcomprisUser));
1062
user->user_id = atoi(result[i++]);
1063
user->login = g_strdup(result[i++]);
1064
user->lastname = g_strdup(result[i++]);
1065
user->firstname = g_strdup(result[i++]);
1066
user->birthdate = g_strdup(result[i++]);
1067
user->class_id = atoi(result[i++]);
1069
users = g_list_append(users, user);
1079
#define USER_FROM_ID(n) \
1080
"SELECT users.login, lastname, firstname, birthdate, class_id FROM users WHERE user_id = %d;",n
1082
GcomprisUser *gc_db_get_user_from_id(gint user_id)
1092
GcomprisUser *user = NULL;
1094
request = g_strdup_printf(USER_FROM_ID(user_id));
1095
rc = sqlite3_get_table(gcompris_db,
1103
if( rc!=SQLITE_OK ){
1104
g_error("SQL error: %s\n", zErrMsg);
1110
g_warning("No user with id %d", user_id);
1114
user = g_malloc0(sizeof(GcomprisUser));
1116
user->user_id = user_id;
1117
user->login = g_strdup(result[i++]);
1118
user->lastname = g_strdup(result[i++]);
1119
user->firstname = g_strdup(result[i++]);
1120
user->birthdate = g_strdup(result[i++]);
1121
user->class_id = atoi(result[i++]);
1131
#define CLASS_FROM_ID(n) \
1132
"SELECT name, teacher, wholegroup_id FROM class WHERE class_id = %d;",n
1134
#define GROUPS_IN_CLASS(n) \
1135
"SELECT group_id FROM groups WHERE class_id = %d;",n
1137
GcomprisClass *gc_db_get_class_from_id(gint class_id)
1148
GcomprisClass *class = NULL;
1150
request = g_strdup_printf(CLASS_FROM_ID(class_id));
1151
rc = sqlite3_get_table(gcompris_db,
1159
if( rc!=SQLITE_OK ){
1160
g_error("SQL error: %s\n", zErrMsg);
1166
g_warning("No class with id %d", class_id);
1172
class = g_malloc0(sizeof(GcomprisClass));
1174
class->class_id = class_id;
1175
class->name = g_strdup(result[i++]);
1176
class->description = g_strdup(result[i++]);
1177
class->wholegroup_id = atoi(result[i++]);
1182
GList *group_ids = NULL;
1184
request = g_strdup_printf(GROUPS_IN_CLASS(class_id));
1186
rc = sqlite3_get_table(gcompris_db,
1194
if( rc!=SQLITE_OK ){
1195
g_error("SQL error: %s\n", zErrMsg);
1201
g_error("No groups found for class id %d: there must be at least one for the whole class with id (%d)",
1202
class_id, class->wholegroup_id);
1208
while (i < (nrow +1)*ncolumn) {
1209
int *group_id = g_malloc(sizeof(int));
1211
*group_id = atoi(result[i++]);
1212
group_ids = g_list_append(group_ids, group_id);
1214
class->group_ids = group_ids;
1224
#define CHECK_CONF \
1225
"SELECT * FROM board_profile_conf WHERE profile_id=%d AND board_id=%d AND conf_key=%Q;"
1227
#define INSERT_KEY \
1228
"INSERT INTO board_profile_conf (profile_id, board_id, conf_key, conf_value) VALUES (%d, %d, %Q, %Q);"
1230
#define UPDATE_KEY \
1231
"UPDATE board_profile_conf SET conf_value=%Q WHERE profile_id=%d AND board_id=%d AND conf_key=%Q;"
1233
void gc_db_set_board_conf(GcomprisProfile *profile,
1234
GcomprisBoard *board,
1246
request = sqlite3_mprintf(CHECK_CONF,
1247
profile->profile_id,
1251
rc = sqlite3_get_table(gcompris_db,
1259
if( rc!=SQLITE_OK ){
1260
g_error("SQL error: %s\n", zErrMsg);
1263
sqlite3_free(request);
1266
request = sqlite3_mprintf(INSERT_KEY,
1267
profile->profile_id,
1272
rc = sqlite3_get_table(gcompris_db,
1280
if( rc!=SQLITE_OK ){
1281
g_error("SQL error: %s\n", zErrMsg);
1284
sqlite3_free(request);
1286
request = sqlite3_mprintf(UPDATE_KEY,
1288
profile->profile_id,
1293
rc = sqlite3_get_table(gcompris_db,
1301
if( rc!=SQLITE_OK ){
1302
g_error("SQL error: %s\n", zErrMsg);
1305
sqlite3_free(request);
1310
#define GET_CONF(p, b) \
1311
"SELECT conf_key, conf_value FROM board_profile_conf WHERE profile_id=%d AND board_id=%d;", p, b
1313
GHashTable *gc_db_conf_with_table_get(int profile_id, int board_id, GHashTable *table )
1315
GHashTable *hash_conf = table;
1326
request = g_strdup_printf(GET_CONF(profile_id,
1329
g_warning ( "Request get_conf : %s", request);
1331
rc = sqlite3_get_table(gcompris_db,
1339
if( rc!=SQLITE_OK ){
1340
g_error("SQL error: %s\n", zErrMsg);
1345
for ( i=ncolumn; i < (nrow +1)*ncolumn; i+=2){
1346
if (strcmp(result[i+1],"NULL")!=0){
1347
/* "NULL" values are ignored */
1348
g_hash_table_replace (hash_conf,
1349
g_strdup(result[i]),
1350
g_strdup(result[i+1]));
1351
g_warning("get_conf: put key %s value %s in the hash",
1357
sqlite3_free_table(result);
1362
GHashTable *gc_db_get_conf(GcomprisProfile *profile, GcomprisBoard *board )
1364
GHashTable *hash_result = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
1367
return gc_db_conf_with_table_get( profile->profile_id, board->board_id, hash_result) ;
1373
GHashTable *gc_db_get_board_conf()
1375
GHashTable *hash_result = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
1377
/* priority order : board + Profile conf, else profile Default (all boards) conf, if not Default profile + board */
1379
/* conf values for default profile and current board */
1380
hash_result = gc_db_conf_with_table_get(1,
1381
gc_board_get_current()->board_id,
1384
/* conf values for profile (board independant) */
1385
if(gc_profile_get_current()) {
1386
hash_result = gc_db_conf_with_table_get(gc_profile_get_current()->profile_id,
1390
/* conf value for current profile and current board */
1391
hash_result = gc_db_conf_with_table_get(gc_profile_get_current()->profile_id,
1392
gc_board_get_current()->board_id,
1399
#define GET_ALL_PROFILES \
1400
"SELECT profile_id, name, profile_directory, description FROM profiles;"
1403
GList *gc_db_profiles_list_get()
1416
GList *profiles_list = NULL;
1426
rc = sqlite3_get_table(gcompris_db,
1434
if( rc!=SQLITE_OK ){
1435
g_error("SQL error: %s\n", zErrMsg);
1442
while (i < (nrow +1)*ncolumn) {
1443
GcomprisProfile *profile = g_malloc0(sizeof(GcomprisProfile));
1445
profile->profile_id = atoi(result[i++]);
1447
profile->name = g_strdup(result[i++]);
1448
profile->directory = g_strdup(result[i++]);
1449
profile->description = g_strdup(result[i++]);
1451
request = g_strdup_printf(GET_GROUPS_IN_PROFILE(profile->profile_id));
1453
rc = sqlite3_get_table(gcompris_db,
1461
if( rc!=SQLITE_OK ){
1462
g_error("SQL error: %s\n", zErrMsg);
1468
g_warning("No users groups for profile %s", profile->name);
1469
profile->group_ids = NULL;
1474
while (i_ < (nrow_ +1)*ncolumn_) {
1475
int *group_id = g_malloc(sizeof(int));
1477
*group_id = atoi(result_[i_++]);
1478
ids_ = g_list_append(ids_, group_id);
1480
profile->group_ids = ids_;
1483
sqlite3_free_table(result_);
1485
request = g_strdup_printf(GET_ACTIVITIES_OUT_OF_PROFILE(profile->profile_id));
1486
rc = sqlite3_get_table(gcompris_db,
1494
if( rc!=SQLITE_OK ){
1495
g_error("SQL error: %s\n", zErrMsg);
1501
g_warning("No activities out for profile %s", profile->name);
1502
profile->activities = NULL;
1507
while (i_ < (nrow_ +1)*ncolumn_) {
1508
int *board_id = g_malloc(sizeof(int));
1510
*board_id = atoi(result_[i_++]);
1511
ids_ = g_list_append(ids_, board_id);
1513
profile->activities = ids_;
1516
sqlite3_free_table(result_);
1517
profiles_list = g_list_append( profiles_list, profile);
1520
sqlite3_free_table(result);
1522
return profiles_list;
1528
#define GROUP_FROM_ID(n) \
1529
"SELECT name, class_id, description FROM groups WHERE group_id=%d;",n
1531
GcomprisGroup *gc_db_get_group_from_id(int group_id)
1543
GcomprisGroup *group = NULL;
1545
request = g_strdup_printf(GROUP_FROM_ID(group_id));
1546
rc = sqlite3_get_table(gcompris_db,
1554
if( rc!=SQLITE_OK ){
1555
g_error("SQL error: %s\n", zErrMsg);
1561
g_warning("No group with id %d", group_id);
1566
group = g_malloc0(sizeof(GcomprisGroup));
1568
group->group_id = group_id;
1569
group->name = g_strdup(result[i++]);
1570
group->class_id = atoi(result[i++]);
1571
group->description = g_strdup(result[i++]);
1574
group->user_ids = gc_db_users_from_group_get(group_id);
1583
#define GET_ALL_GROUPS \
1584
"SELECT group_id, name, class_id, description FROM groups;"
1586
GList *gc_db_get_groups_list()
1589
GList *groups_list = NULL;
1597
GcomprisGroup *group = NULL;
1599
rc = sqlite3_get_table(gcompris_db,
1607
if( rc!=SQLITE_OK ){
1608
g_error("SQL error: %s\n", zErrMsg);
1612
g_warning("No groups !");
1617
while ( i < (nrow +1)*ncolumn) {
1618
group = g_malloc0(sizeof(GcomprisGroup));
1620
group->group_id = atoi(result[i++]);
1621
group->name = g_strdup(result[i++]);
1622
group->class_id = atoi(result[i++]);
1623
group->description = g_strdup(result[i++]);
1625
group->user_ids = gc_db_users_from_group_get(group->group_id);
1627
groups_list = g_list_append(groups_list, group);
1639
#define BOARDS_READ_FROM_ID(n) \
1640
"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
1642
GcomprisBoard *gc_db_get_board_from_id(int board_id)
1646
GcomprisProperties *properties = gc_prop_get();
1656
request = g_strdup_printf(BOARDS_READ_FROM_ID(board_id));
1658
rc = sqlite3_get_table(gcompris_db,
1666
if( rc!=SQLITE_OK ){
1667
g_error("SQL error: %s\n", zErrMsg);
1672
/* first ncolumns are columns labels. */
1675
GcomprisBoard *gcomprisBoard = NULL;
1677
gcomprisBoard = g_malloc0 (sizeof (GcomprisBoard));
1680
gcomprisBoard->plugin=NULL;
1681
gcomprisBoard->previous_board=NULL;
1682
gcomprisBoard->board_ready=FALSE;
1683
gcomprisBoard->canvas=gc_get_canvas();
1685
gcomprisBoard->gmodule = NULL;
1686
gcomprisBoard->gmodule_file = NULL;
1688
/* From DB we have only package_data_dir. */
1689
gcomprisBoard->board_dir = g_strdup_printf(properties->package_data_dir);
1691
/* Fixed since I use the canvas own pixel_per_unit scheme */
1692
gcomprisBoard->width = BOARDWIDTH;
1693
gcomprisBoard->height = BOARDHEIGHT;
1696
gcomprisBoard->board_id = board_id;
1697
gcomprisBoard->name = g_strdup(result[i++]);
1698
gcomprisBoard->section_id = atoi(result[i++]);
1699
gcomprisBoard->section = g_strdup(result[i++]);
1700
gcomprisBoard->author = g_strdup(result[i++]);
1701
gcomprisBoard->type = g_strdup(result[i++]);
1702
gcomprisBoard->mode = g_strdup(result[i++]);
1703
gcomprisBoard->difficulty = g_strdup(result[i++]);
1704
gcomprisBoard->icon_name = g_strdup(result[i++]);
1705
gcomprisBoard->boarddir = g_strdup(result[i++]);
1706
gcomprisBoard->mandatory_sound_file = g_strdup(result[i++]);
1707
gcomprisBoard->mandatory_sound_dataset = g_strdup(result[i++]);
1708
gcomprisBoard->filename = g_strdup(result[i++]);
1709
gcomprisBoard->title = reactivate_newline(gettext(result[i++]));
1710
gcomprisBoard->description = reactivate_newline(gettext(result[i++]));
1711
gcomprisBoard->prerequisite = reactivate_newline(gettext(result[i++]));
1712
gcomprisBoard->goal = reactivate_newline(gettext(result[i++]));
1713
gcomprisBoard->manual = reactivate_newline(gettext(result[i++]));
1714
gcomprisBoard->credit = reactivate_newline(gettext(result[i++]));
1716
sqlite3_free_table(result);
1718
return gcomprisBoard;
1724
#define GET_ALL_USERS \
1725
"SELECT user_id, login, lastname, firstname, birthdate, class_id FROM users;"
1727
GList *gc_db_get_users_list()
1730
GList *users_list = NULL;
1738
GcomprisUser *user = NULL;
1740
rc = sqlite3_get_table(gcompris_db,
1748
if( rc!=SQLITE_OK ){
1749
g_error("SQL error: %s\n", zErrMsg);
1753
g_warning("No users !");
1758
while ( i < (nrow +1)*ncolumn) {
1759
user = g_malloc0(sizeof(GcomprisUser));
1761
user->user_id = atoi(result[i++]);
1762
user->login = g_strdup(result[i++]);
1763
user->firstname = g_strdup(result[i++]);
1764
user->lastname = g_strdup(result[i++]);
1765
user->birthdate = g_strdup(result[i++]);
1766
user->class_id = atoi(result[i++]);
1768
users_list = g_list_append(users_list, user);
1779
#define GET_ALL_CLASSES \
1780
"SELECT class_id, name, teacher, wholegroup_id FROM class;"
1782
GList *gc_db_get_classes_list()
1785
GList *classes_list = NULL;
1793
GcomprisClass *class = NULL;
1795
rc = sqlite3_get_table(gcompris_db,
1803
if( rc!=SQLITE_OK ){
1804
g_error("SQL error: %s\n", zErrMsg);
1808
g_warning("No groups !");
1813
while ( i < (nrow +1)*ncolumn) {
1814
class = g_malloc0(sizeof(GcomprisClass));
1816
class->class_id = atoi(result[i++]);
1817
class->name = g_strdup(result[i++]);
1818
class->description = g_strdup(result[i++]);
1819
class->wholegroup_id = atoi(result[i++]);
1821
classes_list = g_list_append(classes_list, class);
1825
return classes_list;
1832
/* Special request, return true if an activity name is disabled in the profile */
1833
#define DB_IS_ACTIVITY_IN_PROFILE_ID(profile_id, name) \
1834
"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
1836
int gc_db_is_activity_in_profile(GcomprisProfile *profile, char *activity_name)
1846
request = g_strdup_printf(DB_IS_ACTIVITY_IN_PROFILE_ID(profile->profile_id, activity_name));
1848
rc = sqlite3_get_table(gcompris_db,
1858
if( rc!=SQLITE_OK ){
1859
g_error("SQL error: %s\n", zErrMsg);
1863
/* IS IN THE PROFILE */
1867
/* IS NOT IN THE PROFILE */