3 * Program to check a Bacula database for consistency and to
6 * Kern E. Sibbald, August 2002
12 Copyright (C) 2000-2003 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();
78 static int yes_no(char *prompt);
84 "Usage: dbcheck [-d debug_level] <working-directory> <bacula-databse> <user> <password>\n"
86 " -dnn set debug level to nn\n"
87 " -f fix inconsistencies\n"
89 " -? print this message\n\n");
93 int main (int argc, char *argv[])
96 char *user, *password, *db_name;
98 my_name_is(argc, argv, "dbcheck");
99 init_msg(NULL, NULL); /* setup message handler */
101 memset(&id_list, 0, sizeof(id_list));
102 memset(&name_list, 0, sizeof(name_list));
105 while ((ch = getopt(argc, argv, "bd:fv?")) != -1) {
107 case 'b': /* batch */
111 case 'd': /* debug level */
112 debug_level = atoi(optarg);
113 if (debug_level <= 0)
117 case 'f': /* fix inconsistencies */
135 Pmsg0(0, _("Wrong number of arguments.\n"));
140 Pmsg0(0, _("Working directory not supplied.\n"));
144 /* This is needed by SQLite to find the db */
145 working_directory = argv[0];
153 } else if (argc == 3) {
156 } else if (argc == 4) {
163 db = db_init_database(NULL, db_name, user, password);
164 if (!db_open_database(db)) {
165 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
169 eliminate_duplicate_filenames();
170 eliminate_duplicate_paths();
171 eliminate_orphaned_jobmedia_records();
172 eliminate_orphaned_file_records();
173 eliminate_orphaned_path_records();
174 eliminate_orphaned_filename_records();
175 eliminate_orphaned_fileset_records();
177 do_interactive_mode();
180 db_close_database(db);
186 static void do_interactive_mode()
191 printf("Hello, this is the database check/correct program.\n\
192 Modify database is %s. Verbose is %s.\n\
193 Please select the fuction you want to perform.\n",
194 fix?"On":"Off", verbose?"On":"Off");
199 1) Toggle modify database flag\n\
200 2) Toggle verbose flag\n\
201 3) Eliminate duplicate Filename records\n\
202 4) Eliminate duplicate Path records\n\
203 5) Eliminate orphaned Jobmedia records\n\
204 6) Eliminate orphaned File records\n\
205 7) Eliminate orphaned Path records\n\
206 8) Eliminate orphaned Filename records\n\
207 9) Eliminate orphaned FileSet records\n\
212 1) Toggle modify database flag\n\
213 2) Toggle verbose flag\n\
214 3) Check for duplicate Filename records\n\
215 4) Check for duplicate Path records\n\
216 5) Check for orphaned Jobmedia records\n\
217 6) Check for orphaned File records\n\
218 7) Check for orphaned Path records\n\
219 8) Check for orphaned Filename records\n\
220 9) Check for orphaned FileSet records\n\
225 cmd = get_cmd(_("Select function number: "));
227 int item = atoi(cmd);
231 printf(_("Database will %sbe modified.\n"), fix?"":_("NOT "));
234 verbose = verbose?0:1;
235 printf(_("Verbose is %s\n"), verbose?_("On"):_("Off"));
238 eliminate_duplicate_filenames();
241 eliminate_duplicate_paths();
244 eliminate_orphaned_jobmedia_records();
247 eliminate_orphaned_file_records();
250 eliminate_orphaned_path_records();
253 eliminate_orphaned_filename_records();
256 eliminate_orphaned_fileset_records();
259 eliminate_duplicate_filenames();
260 eliminate_duplicate_paths();
261 eliminate_orphaned_jobmedia_records();
262 eliminate_orphaned_file_records();
263 eliminate_orphaned_path_records();
264 eliminate_orphaned_filename_records();
265 eliminate_orphaned_fileset_records();
275 static int print_name_handler(void *ctx, int num_fields, char **row)
278 printf("%s\n", row[0]);
283 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
285 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
286 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
290 static int print_file_handler(void *ctx, int num_fields, char **row)
292 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
293 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
297 static int print_fileset_handler(void *ctx, int num_fields, char **row)
299 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
300 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
309 * Called here with each id to be added to the list
311 static int id_list_handler(void *ctx, int num_fields, char **row)
313 ID_LIST *lst = (ID_LIST *)ctx;
315 if (lst->num_ids == MAX_ID_LIST_LEN) {
318 if (lst->num_ids == lst->max_ids) {
319 if (lst->max_ids == 0) {
321 lst->Id = (uint32_t *)bmalloc(sizeof(uint32_t) * lst->max_ids);
323 lst->max_ids = (lst->max_ids * 3) / 2;
324 lst->Id = (uint32_t *)brealloc(lst->Id, sizeof(uint32_t) * lst->max_ids);
327 lst->Id[lst->num_ids++] = (uint32_t)strtod(row[0], NULL);
332 * Construct record id list
334 static int make_id_list(char *query, ID_LIST *id_list)
336 id_list->num_ids = 0;
337 id_list->num_del = 0;
338 id_list->tot_ids = 0;
340 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
341 printf("%s", db_strerror(db));
348 * Delete all entries in the list
350 static int delete_id_list(char *query, ID_LIST *id_list)
354 for (i=0; i < id_list->num_ids; i++) {
355 sprintf(buf, query, id_list->Id[i]);
357 printf("Deleting: %s\n", buf);
359 db_sql_query(db, buf, NULL, NULL);
365 * Called here with each name to be added to the list
367 static int name_list_handler(void *ctx, int num_fields, char **row)
369 NAME_LIST *name = (NAME_LIST *)ctx;
371 if (name->num_ids == MAX_ID_LIST_LEN) {
374 if (name->num_ids == name->max_ids) {
375 if (name->max_ids == 0) {
376 name->max_ids = 1000;
377 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
379 name->max_ids = (name->max_ids * 3) / 2;
380 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
383 name->name[name->num_ids++] = bstrdup(row[0]);
389 * Construct name list
391 static int make_name_list(char *query, NAME_LIST *name_list)
393 name_list->num_ids = 0;
394 name_list->num_del = 0;
395 name_list->tot_ids = 0;
397 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
398 printf("%s", db_strerror(db));
405 * Print names in the list
407 static void print_name_list(NAME_LIST *name_list)
411 for (i=0; i < name_list->num_ids; i++) {
412 printf("%s\n", name_list->name[i]);
418 * Free names in the list
420 static void free_name_list(NAME_LIST *name_list)
424 for (i=0; i < name_list->num_ids; i++) {
425 free(name_list->name[i]);
427 name_list->num_ids = 0;
430 static void eliminate_duplicate_filenames()
435 printf("Checking for duplicate Filename entries.\n");
437 /* Make list of duplicated names */
438 query = "SELECT Name,count(Name) as Count FROM Filename GROUP BY Name "
441 if (!make_name_list(query, &name_list)) {
444 printf("Found %d duplicate Filename records.\n", name_list.num_ids);
445 if (verbose && yes_no("Print the list? (yes/no): ")) {
446 print_name_list(&name_list);
449 /* Loop through list of duplicate names */
450 for (int i=0; i<name_list.num_ids; i++) {
451 /* Get all the Ids of each name */
452 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
453 sprintf(buf, "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
455 printf("Doing: %s\n", name_list.name[i]);
457 if (!make_id_list(buf, &id_list)) {
460 /* Force all records to use the first id then delete the other ids */
461 for (int j=1; j<id_list.num_ids; j++) {
462 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
463 id_list.Id[0], id_list.Id[j]);
464 db_sql_query(db, buf, NULL, NULL);
465 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
467 db_sql_query(db, buf, NULL, NULL);
471 free_name_list(&name_list);
474 static void eliminate_duplicate_paths()
479 printf(_("Checking for duplicate Path entries.\n"));
481 /* Make list of duplicated names */
483 query = "SELECT Path,count(Path) as Count FROM Path "
484 "GROUP BY Path HAVING Count > 1";
486 if (!make_name_list(query, &name_list)) {
489 printf("Found %d duplicate Path records.\n", name_list.num_ids);
490 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
491 print_name_list(&name_list);
494 /* Loop through list of duplicate names */
495 for (int i=0; i<name_list.num_ids; i++) {
496 /* Get all the Ids of each name */
497 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
498 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
500 printf("Doing: %s\n", name_list.name[i]);
502 if (!make_id_list(buf, &id_list)) {
505 /* Force all records to use the first id then delete the other ids */
506 for (int j=1; j<id_list.num_ids; j++) {
507 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
508 id_list.Id[0], id_list.Id[j]);
509 db_sql_query(db, buf, NULL, NULL);
510 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
512 db_sql_query(db, buf, NULL, NULL);
516 free_name_list(&name_list);
519 static void eliminate_orphaned_jobmedia_records()
523 printf("Checking for orphaned JobMedia entries.\n");
524 query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
525 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
526 "WHERE Job.JobId IS NULL";
527 if (!make_id_list(query, &id_list)) {
530 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
531 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
533 for (i=0; i < id_list.num_ids; i++) {
535 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
536 "WHERE JobMedia.JobMediaId=%u AND Media.MediaId=JobMedia.MediaId", id_list.Id[i]);
537 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
538 printf("%s\n", db_strerror(db));
543 if (fix && id_list.num_ids > 0) {
544 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
545 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
549 static void eliminate_orphaned_file_records()
553 printf("Checking for orphaned File entries. This may take some time!\n");
554 query = "SELECT File.FileId,Job.JobId FROM File "
555 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
556 "WHERE Job.JobId IS NULL";
557 if (!make_id_list(query, &id_list)) {
560 printf("Found %d orphaned File records.\n", id_list.num_ids);
561 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
563 for (i=0; i < id_list.num_ids; i++) {
565 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
566 "WHERE File.FileId=%u AND File.FilenameId=Filename.FilenameId", id_list.Id[i]);
567 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
568 printf("%s\n", db_strerror(db));
573 if (fix && id_list.num_ids > 0) {
574 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
575 delete_id_list("DELETE FROM File WHERE FileId=%u", &id_list);
579 static void eliminate_orphaned_path_records()
583 printf("Checking for orphaned Path entries. This may take some time!\n");
584 query = "SELECT Path.PathId,File.PathId FROM Path "
585 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
586 "HAVING File.PathId IS NULL";
587 if (!make_id_list(query, &id_list)) {
590 printf("Found %d orphaned Path records.\n", id_list.num_ids);
591 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
593 for (i=0; i < id_list.num_ids; i++) {
594 sprintf(buf, "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
595 db_sql_query(db, buf, print_name_handler, NULL);
599 if (fix && id_list.num_ids > 0) {
600 printf("Deleting %d orphaned Path records.\n", id_list.num_ids);
601 delete_id_list("DELETE FROM Path WHERE PathId=%u", &id_list);
605 static void eliminate_orphaned_filename_records()
609 printf("Checking for orphaned Filename entries. This may take some time!\n");
610 query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
611 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
612 "WHERE File.FilenameId IS NULL";
614 if (!make_id_list(query, &id_list)) {
617 printf("Found %d orphaned Filename records.\n", id_list.num_ids);
618 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
620 for (i=0; i < id_list.num_ids; i++) {
621 sprintf(buf, "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
622 db_sql_query(db, buf, print_name_handler, NULL);
626 if (fix && id_list.num_ids > 0) {
627 printf("Deleting %d orphaned Filename records.\n", id_list.num_ids);
628 delete_id_list("DELETE FROM Filename WHERE FilenameId=%u", &id_list);
632 static void eliminate_orphaned_fileset_records()
636 printf("Checking for orphaned FileSet entries. This takes some time!\n");
637 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
638 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
639 "WHERE Job.FileSetId IS NULL";
640 if (!make_id_list(query, &id_list)) {
643 printf("Found %d orphaned FileSet records.\n", id_list.num_ids);
644 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
646 for (i=0; i < id_list.num_ids; i++) {
647 sprintf(buf, "SELECT FileSetId,FileSet,MD5 FROM FileSet "
648 "WHERE FileSetId=%u", id_list.Id[i]);
649 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
650 printf("%s\n", db_strerror(db));
655 if (fix && id_list.num_ids > 0) {
656 printf("Deleting %d orphaned FileSet records.\n", id_list.num_ids);
657 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%u", &id_list);
663 * Gen next input command from the terminal
665 static char *get_cmd(char *prompt)
667 static char cmd[1000];
669 printf("%s", prompt);
670 if (fgets(cmd, sizeof(cmd), stdin) == NULL)
673 strip_trailing_junk(cmd);
677 static int yes_no(char *prompt)
680 cmd = get_cmd(prompt);
681 return strcasecmp(cmd, "yes") == 0;