3 * Program to check a Bacula database for consistency and to
6 * Kern E. Sibbald, August 2002
12 Copyright (C) 2000, 2001, 2002 Kern Sibbald and John Walker
14 This program is free software; you can redistribute it and/or
15 modify it under the terms of the GNU General Public License as
16 published by the Free Software Foundation; either version 2 of
17 the License, or (at your option) any later version.
19 This program is distributed in the hope that it will be useful,
20 but WITHOUT ANY WARRANTY; without even the implied warranty of
21 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
22 General Public License for more details.
24 You should have received a copy of the GNU General Public
25 License along with this program; if not, write to the Free
26 Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
32 #include "cats/cats.h"
34 typedef struct s_id_ctx {
35 uint32_t *Id; /* ids to be modified */
36 int num_ids; /* ids stored */
37 int max_ids; /* size of array */
38 int num_del; /* number deleted */
39 int tot_ids; /* total to process */
42 typedef struct s_name_ctx {
43 char **name; /* list of names */
44 int num_ids; /* ids stored */
45 int max_ids; /* size of array */
46 int num_del; /* number deleted */
47 int tot_ids; /* total to process */
52 /* Global variables */
53 static int fix = FALSE;
54 static int interactive = FALSE;
55 static int verbose = FALSE;
57 static ID_LIST id_list;
58 static NAME_LIST name_list;
59 static char buf[2000];
61 #define MAX_ID_LIST_LEN 1000000
63 /* Forward referenced functions */
64 static int make_id_list(char *query, ID_LIST *id_list);
65 static int delete_id_list(char *query, ID_LIST *id_list);
66 static int make_name_list(char *query, NAME_LIST *name_list);
67 static void print_name_list(NAME_LIST *name_list);
68 static void free_name_list(NAME_LIST *name_list);
69 static void eliminate_duplicate_filenames();
70 static void eliminate_duplicate_paths();
71 static void eliminate_orphaned_jobmedia_records();
72 static void eliminate_orphaned_file_records();
75 static void prtit(void *ctx, char *msg)
84 "Usage: dbcheck [-d debug_level] <working-directory> <bacula-databse> <user> <password>\n"
85 " -dnn set debug level to nn\n"
86 " -f fix inconsistencies\n"
87 " -i interactive mode\n"
88 " -? print this message\n\n");
92 int main (int argc, char *argv[])
95 char *user, *password, *db_name;
97 my_name_is(argc, argv, "dbcheck");
98 init_msg(NULL, NULL); /* setup message handler */
100 memset(&id_list, 0, sizeof(id_list));
101 memset(&name_list, 0, sizeof(name_list));
104 while ((ch = getopt(argc, argv, "d:fi?")) != -1) {
106 case 'd': /* debug level */
107 debug_level = atoi(optarg);
108 if (debug_level <= 0)
112 case 'f': /* fix inconsistencies */
116 case 'i': /* interactive */
130 Pmsg0(0, _("Wrong number of arguments.\n"));
135 Pmsg0(0, _("Working directory not supplied.\n"));
139 /* This is needed by SQLite to find the db */
140 working_directory = argv[0];
148 } else if (argc == 3) {
151 } else if (argc == 4) {
158 db = db_init_database(db_name, user, password);
159 if (!db_open_database(db)) {
160 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
163 eliminate_duplicate_filenames();
165 eliminate_duplicate_paths();
167 eliminate_orphaned_jobmedia_records();
169 eliminate_orphaned_file_records();
171 db_close_database(db);
179 * Called here with each id to be added to the list
181 static int id_list_handler(void *ctx, int num_fields, char **row)
183 ID_LIST *lst = (ID_LIST *)ctx;
185 if (lst->num_ids == MAX_ID_LIST_LEN) {
188 if (lst->num_ids == lst->max_ids) {
189 if (lst->max_ids == 0) {
191 lst->Id = (uint32_t *)malloc(sizeof(uint32_t) * lst->max_ids);
193 lst->max_ids = (lst->max_ids * 3) / 2;
194 lst->Id = (uint32_t *)brealloc(lst->Id, sizeof(uint32_t) * lst->max_ids);
197 lst->Id[lst->num_ids++] = (uint32_t)strtod(row[0], NULL);
202 * Construct record id list
204 static int make_id_list(char *query, ID_LIST *id_list)
206 id_list->num_ids = 0;
207 id_list->num_del = 0;
208 id_list->tot_ids = 0;
210 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
211 printf("%s", db_strerror(db));
218 * Delete all entries in the list
220 static int delete_id_list(char *query, ID_LIST *id_list)
224 for (i=0; i < id_list->num_ids; i++) {
225 sprintf(buf, query, id_list->Id[i]);
226 db_sql_query(db, buf, NULL, NULL);
232 * Called here with each name to be added to the list
234 static int name_list_handler(void *ctx, int num_fields, char **row)
236 NAME_LIST *name = (NAME_LIST *)ctx;
238 if (name->num_ids == MAX_ID_LIST_LEN) {
241 if (name->num_ids == name->max_ids) {
242 if (name->max_ids == 0) {
243 name->max_ids = 1000;
244 name->name = (char **)malloc(sizeof(char *) * name->max_ids);
246 name->max_ids = (name->max_ids * 3) / 2;
247 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
250 name->name[name->num_ids++] = bstrdup(row[0]);
256 * Construct name list
258 static int make_name_list(char *query, NAME_LIST *name_list)
260 name_list->num_ids = 0;
261 name_list->num_del = 0;
262 name_list->tot_ids = 0;
264 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
265 printf("%s", db_strerror(db));
273 * Print names in the list
275 static void print_name_list(NAME_LIST *name_list)
279 for (i=0; i < name_list->num_ids; i++) {
280 printf("%s\n", name_list->name[i]);
286 * Free names in the list
288 static void free_name_list(NAME_LIST *name_list)
292 for (i=0; i < name_list->num_ids; i++) {
293 free(name_list->name[i]);
295 name_list->num_ids = 0;
298 static void eliminate_duplicate_filenames()
302 printf("Checking for duplicate Filename entries.\n");
304 /* Make list of duplicated names */
305 query = "SELECT Name FROM Filename "
306 "GROUP BY Name HAVING COUNT(FilenameId) > 1";
307 if (!make_name_list(query, &name_list)) {
310 printf("Found %d duplicate Filename records.\n", name_list.num_ids);
312 print_name_list(&name_list);
315 /* Loop through list of duplicate names */
316 for (int i=0; i<name_list.num_ids; i++) {
317 /* Get all the Ids of each name */
318 sprintf(buf, "SELECT FilenameId FROM Filename WHERE Name='%s'",
320 if (!make_id_list(buf, &id_list)) {
323 /* Force all records to use the first id then delete the other ids */
324 for (int j=1; j<id_list.num_ids; j++) {
325 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
326 id_list.Id[0], id_list.Id[j]);
327 db_sql_query(db, buf, NULL, NULL);
328 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
330 db_sql_query(db, buf, NULL, NULL);
334 free_name_list(&name_list);
337 static void eliminate_duplicate_paths()
341 printf("Checking for duplicate Path entries.\n");
343 /* Make list of duplicated names */
344 query = "SELECT Path FROM Path "
345 "GROUP BY Path HAVING COUNT(PathId) > 1";
346 if (!make_name_list(query, &name_list)) {
349 printf("Found %d duplicate Path records.\n", name_list.num_ids);
351 print_name_list(&name_list);
354 /* Loop through list of duplicate names */
355 for (int i=0; i<name_list.num_ids; i++) {
356 /* Get all the Ids of each name */
357 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'",
359 if (!make_id_list(buf, &id_list)) {
362 /* Force all records to use the first id then delete the other ids */
363 for (int j=1; j<id_list.num_ids; j++) {
364 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
365 id_list.Id[0], id_list.Id[j]);
366 db_sql_query(db, buf, NULL, NULL);
367 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
369 db_sql_query(db, buf, NULL, NULL);
373 free_name_list(&name_list);
376 static void eliminate_orphaned_jobmedia_records()
380 printf("Checking for orphaned JobMedia entries.\n");
381 query = "SELECT JobMedia.JobId,Job FROM JobMedia LEFT OUTER JOIN Job ON"
382 " (Job.JobId=JobMediaId) GROUP BY MediaId HAVING Job IS NULL";
383 if (!make_id_list(query, &id_list)) {
386 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
388 if (fix && id_list.num_ids > 0) {
389 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
390 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
394 static void eliminate_orphaned_file_records()
398 printf("Checking for orphaned File entries.\n");
399 query = "SELECT FileId,Job FROM File LEFT OUTER JOIN Job ON"
400 " (Job.JobId=File.JobId) GROUP BY FileId HAVING Job IS NULL";
401 if (!make_id_list(query, &id_list)) {
404 printf("Found %d orphaned File records.\n", id_list.num_ids);
406 if (fix && id_list.num_ids > 0) {
407 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
408 delete_id_list("DELETE FROM File WHERE FileIdId=%u", &id_list);