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 batch = 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 char *get_cmd(char *prompt);
70 static void eliminate_duplicate_filenames();
71 static void eliminate_duplicate_paths();
72 static void eliminate_orphaned_jobmedia_records();
73 static void eliminate_orphaned_file_records();
74 static void eliminate_orphaned_path_records();
75 static void eliminate_orphaned_filename_records();
76 static void eliminate_orphaned_fileset_records();
77 static void do_interactive_mode();
83 "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"
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, "bd:fv?")) != -1) {
106 case 'b': /* batch */
110 case 'd': /* debug level */
111 debug_level = atoi(optarg);
112 if (debug_level <= 0)
116 case 'f': /* fix inconsistencies */
134 Pmsg0(0, _("Wrong number of arguments.\n"));
139 Pmsg0(0, _("Working directory not supplied.\n"));
143 /* This is needed by SQLite to find the db */
144 working_directory = argv[0];
152 } else if (argc == 3) {
155 } else if (argc == 4) {
162 db = db_init_database(NULL, db_name, user, password);
163 if (!db_open_database(db)) {
164 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
168 eliminate_duplicate_filenames();
169 eliminate_duplicate_paths();
170 eliminate_orphaned_jobmedia_records();
171 eliminate_orphaned_file_records();
172 eliminate_orphaned_path_records();
173 eliminate_orphaned_filename_records();
174 eliminate_orphaned_fileset_records();
176 do_interactive_mode();
179 db_close_database(db);
185 static void do_interactive_mode()
190 printf("Hello, this is the database check/correct program.\n\
191 Please select the fuction you want to perform.\n");
195 1) Toggle modify database flag\n\
196 2) Toggle verbose flag\n\
197 3) Eliminate duplicate Filename records\n\
198 4) Eliminate duplicate Path records\n\
199 5) Eliminate orphaned Jobmedia records\n\
200 6) Eliminate orphaned File records\n\
201 7) Eliminate orphaned Path records\n\
202 8) Eliminate orphaned Filename records\n\
203 9) Eliminate orphaned FileSet records\n\
207 cmd = get_cmd(_("Select function number: "));
209 int item = atoi(cmd);
213 printf(_("Database will %sbe modified.\n"), fix?"":_("NOT "));
216 verbose = verbose?0:1;
217 printf(_("Verbose is %s\n"), verbose?_("On"):_("Off"));
220 eliminate_duplicate_filenames();
223 eliminate_duplicate_paths();
226 eliminate_orphaned_jobmedia_records();
229 eliminate_orphaned_file_records();
232 eliminate_orphaned_path_records();
235 eliminate_orphaned_filename_records();
238 eliminate_orphaned_fileset_records();
241 eliminate_duplicate_filenames();
242 eliminate_duplicate_paths();
243 eliminate_orphaned_jobmedia_records();
244 eliminate_orphaned_file_records();
245 eliminate_orphaned_path_records();
246 eliminate_orphaned_filename_records();
247 eliminate_orphaned_fileset_records();
257 static int print_name_handler(void *ctx, int num_fields, char **row)
260 printf("%s\n", row[0]);
265 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
267 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
268 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
272 static int print_file_handler(void *ctx, int num_fields, char **row)
274 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
275 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
279 static int print_fileset_handler(void *ctx, int num_fields, char **row)
281 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
282 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
291 * Called here with each id to be added to the list
293 static int id_list_handler(void *ctx, int num_fields, char **row)
295 ID_LIST *lst = (ID_LIST *)ctx;
297 if (lst->num_ids == MAX_ID_LIST_LEN) {
300 if (lst->num_ids == lst->max_ids) {
301 if (lst->max_ids == 0) {
303 lst->Id = (uint32_t *)bmalloc(sizeof(uint32_t) * lst->max_ids);
305 lst->max_ids = (lst->max_ids * 3) / 2;
306 lst->Id = (uint32_t *)brealloc(lst->Id, sizeof(uint32_t) * lst->max_ids);
309 lst->Id[lst->num_ids++] = (uint32_t)strtod(row[0], NULL);
314 * Construct record id list
316 static int make_id_list(char *query, ID_LIST *id_list)
318 id_list->num_ids = 0;
319 id_list->num_del = 0;
320 id_list->tot_ids = 0;
322 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
323 printf("%s", db_strerror(db));
330 * Delete all entries in the list
332 static int delete_id_list(char *query, ID_LIST *id_list)
336 for (i=0; i < id_list->num_ids; i++) {
337 sprintf(buf, query, id_list->Id[i]);
339 printf("Deleting: %s\n", buf);
341 db_sql_query(db, buf, NULL, NULL);
347 * Called here with each name to be added to the list
349 static int name_list_handler(void *ctx, int num_fields, char **row)
351 NAME_LIST *name = (NAME_LIST *)ctx;
353 if (name->num_ids == MAX_ID_LIST_LEN) {
356 if (name->num_ids == name->max_ids) {
357 if (name->max_ids == 0) {
358 name->max_ids = 1000;
359 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
361 name->max_ids = (name->max_ids * 3) / 2;
362 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
365 name->name[name->num_ids++] = bstrdup(row[0]);
371 * Construct name list
373 static int make_name_list(char *query, NAME_LIST *name_list)
375 name_list->num_ids = 0;
376 name_list->num_del = 0;
377 name_list->tot_ids = 0;
379 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
380 printf("%s", db_strerror(db));
387 * Print names in the list
389 static void print_name_list(NAME_LIST *name_list)
393 for (i=0; i < name_list->num_ids; i++) {
394 printf("%s\n", name_list->name[i]);
400 * Free names in the list
402 static void free_name_list(NAME_LIST *name_list)
406 for (i=0; i < name_list->num_ids; i++) {
407 free(name_list->name[i]);
409 name_list->num_ids = 0;
412 static void eliminate_duplicate_filenames()
416 printf("Checking for duplicate Filename entries.\n");
418 /* Make list of duplicated names */
419 #ifdef really_needed_for_HAVE_SQLITE
420 query = "SELECT Name FROM (SELECT COUNT(Name) as Count,Name from Filename "
421 "GROUP BY Name) WHERE Count > 1";
424 query = "SELECT Name,count(Name) as Count FROM Filename GROUP BY Name "
427 if (!make_name_list(query, &name_list)) {
430 printf("Found %d duplicate Filename records.\n", name_list.num_ids);
432 print_name_list(&name_list);
435 /* Loop through list of duplicate names */
436 for (int i=0; i<name_list.num_ids; i++) {
437 /* Get all the Ids of each name */
438 sprintf(buf, "SELECT FilenameId FROM Filename WHERE Name='%s'",
440 if (!make_id_list(buf, &id_list)) {
443 /* Force all records to use the first id then delete the other ids */
444 for (int j=1; j<id_list.num_ids; j++) {
445 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
446 id_list.Id[0], id_list.Id[j]);
447 db_sql_query(db, buf, NULL, NULL);
448 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
450 db_sql_query(db, buf, NULL, NULL);
454 free_name_list(&name_list);
457 static void eliminate_duplicate_paths()
461 printf("Checking for duplicate Path entries.\n");
463 /* Make list of duplicated names */
464 #ifdef really_needed_for_HAVE_SQLITE
465 query = "SELECT Path FROM (SELECT COUNT(Path) AS Count,Path FROM Path "
466 "GROUP BY Path) WHERE Count > 1";
469 query = "SELECT Path,count(Path) as Count FROM Path "
470 "GROUP BY Path HAVING Count > 1";
472 if (!make_name_list(query, &name_list)) {
475 printf("Found %d duplicate Path records.\n", name_list.num_ids);
477 print_name_list(&name_list);
480 /* Loop through list of duplicate names */
481 for (int i=0; i<name_list.num_ids; i++) {
482 /* Get all the Ids of each name */
483 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'",
485 if (!make_id_list(buf, &id_list)) {
488 /* Force all records to use the first id then delete the other ids */
489 for (int j=1; j<id_list.num_ids; j++) {
490 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
491 id_list.Id[0], id_list.Id[j]);
492 db_sql_query(db, buf, NULL, NULL);
493 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
495 db_sql_query(db, buf, NULL, NULL);
499 free_name_list(&name_list);
502 static void eliminate_orphaned_jobmedia_records()
506 printf("Checking for orphaned JobMedia entries.\n");
507 #ifdef really_needed_for_HAVE_SQLITE
508 query = "SELECT X FROM (SELECT JobMedia.JobMediaId as X,Job.JobId as Y "
509 "FROM JobMedia LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId)) "
513 query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
514 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
515 "WHERE Job.JobId IS NULL";
516 if (!make_id_list(query, &id_list)) {
519 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
522 for (i=0; i < id_list.num_ids; i++) {
524 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
525 "WHERE JobMedia.JobMediaId=%u AND Media.MediaId=JobMedia.MediaId", id_list.Id[i]);
526 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
527 printf("%s\n", db_strerror(db));
532 if (fix && id_list.num_ids > 0) {
533 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
534 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
538 static void eliminate_orphaned_file_records()
542 printf("Checking for orphaned File entries. This may take some time!\n");
543 #ifdef really_needed_for_HAVE_SQLITE
544 query = "SELECT X FROM (SELECT File.FileId as X,Job.JobId as Y "
545 "FROM File LEFT OUTER JOIN Job ON (File.JobId=Job.JobId)) "
549 query = "SELECT File.FileId,Job.JobId FROM File "
550 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
551 "WHERE Job.JobId IS NULL";
552 if (!make_id_list(query, &id_list)) {
555 printf("Found %d orphaned File records.\n", id_list.num_ids);
558 for (i=0; i < id_list.num_ids; i++) {
560 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
561 "WHERE File.FileId=%u AND File.FilenameId=Filename.FilenameId", id_list.Id[i]);
562 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
563 printf("%s\n", db_strerror(db));
568 if (fix && id_list.num_ids > 0) {
569 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
570 delete_id_list("DELETE FROM File WHERE FileId=%u", &id_list);
574 static void eliminate_orphaned_path_records()
578 printf("Checking for orphaned Path entries. This may take some time!\n");
579 #ifdef really_needed_for_HAVE_SQLITE
580 query = "SELECT X FROM (SELECT Path.PathId as X,File.JobId as Y "
581 "FROM Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId)) "
585 query = "SELECT Path.PathId,File.PathId FROM Path "
586 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
587 "HAVING File.PathId IS NULL";
588 if (!make_id_list(query, &id_list)) {
591 printf("Found %d orphaned Path records.\n", id_list.num_ids);
594 for (i=0; i < id_list.num_ids; i++) {
595 sprintf(buf, "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
596 db_sql_query(db, buf, print_name_handler, NULL);
600 if (fix && id_list.num_ids > 0) {
601 printf("Deleting %d orphaned Path records.\n", id_list.num_ids);
602 delete_id_list("DELETE FROM Path WHERE PathId=%u", &id_list);
606 static void eliminate_orphaned_filename_records()
610 printf("Checking for orphaned Filename entries. This may take some time!\n");
611 #ifdef really_needed_for_HAVE_SQLITE
612 query = "SELECT X FROM (SELECT Filename.FilenameId as X,File.JobId as Y "
613 "FROM Filename LEFT OUTER JOIN File ON "
614 "(Filename.FilenameId=File.FilenameId)) "
618 query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
619 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
620 "WHERE File.FilenameId IS NULL";
622 if (!make_id_list(query, &id_list)) {
625 printf("Found %d orphaned Filename records.\n", id_list.num_ids);
628 for (i=0; i < id_list.num_ids; i++) {
629 sprintf(buf, "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
630 db_sql_query(db, buf, print_name_handler, NULL);
634 if (fix && id_list.num_ids > 0) {
635 printf("Deleting %d orphaned Filename records.\n", id_list.num_ids);
636 delete_id_list("DELETE FROM Filename WHERE FilenameId=%u", &id_list);
640 static void eliminate_orphaned_fileset_records()
644 printf("Checking for orphaned FileSet entries. This takes some time!\n");
645 #ifdef really_needed_for_HAVE_SQLITE
646 query = "SELECT X FROM (SELECT FileSet.FileSetId as X,Job.JobId as Y "
647 "FROM FileSet LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId)) "
651 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
652 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
653 "WHERE Job.FileSetId IS NULL";
654 if (!make_id_list(query, &id_list)) {
657 printf("Found %d orphaned FileSet records.\n", id_list.num_ids);
660 for (i=0; i < id_list.num_ids; i++) {
661 sprintf(buf, "SELECT FileSetId,FileSet,MD5 FROM FileSet "
662 "WHERE FileSetId=%u", id_list.Id[i]);
663 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
664 printf("%s\n", db_strerror(db));
669 if (fix && id_list.num_ids > 0) {
670 printf("Deleting %d orphaned FileSet records.\n", id_list.num_ids);
671 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%u", &id_list);
677 * Gen next input command from the terminal
679 static char *get_cmd(char *prompt)
681 static char cmd[1000];
683 printf("%s", prompt);
684 if (fgets(cmd, sizeof(cmd), stdin) == NULL)
687 strip_trailing_junk(cmd);