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;
56 static ID_LIST id_list;
57 static NAME_LIST name_list;
58 static char buf[2000];
60 #define MAX_ID_LIST_LEN 1000000
62 /* Forward referenced functions */
63 static int make_id_list(char *query, ID_LIST *id_list);
64 static int delete_id_list(char *query, ID_LIST *id_list);
65 static int make_name_list(char *query, NAME_LIST *name_list);
66 static void print_name_list(NAME_LIST *name_list);
67 static void free_name_list(NAME_LIST *name_list);
68 static char *get_cmd(char *prompt);
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();
73 static void eliminate_orphaned_path_records();
74 static void eliminate_orphaned_filename_records();
75 static void eliminate_orphaned_fileset_records();
76 static void do_interactive_mode();
77 static int yes_no(char *prompt);
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, NULL, 0, NULL);
163 if (!db_open_database(NULL, 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(NULL, db);
185 static void do_interactive_mode()
190 printf("Hello, this is the database check/correct program.\n\
191 Modify database is %s. Verbose is %s.\n\
192 Please select the fuction you want to perform.\n",
193 fix?"On":"Off", verbose?"On":"Off");
198 1) Toggle modify database flag\n\
199 2) Toggle verbose flag\n\
200 3) Eliminate duplicate Filename records\n\
201 4) Eliminate duplicate Path records\n\
202 5) Eliminate orphaned Jobmedia records\n\
203 6) Eliminate orphaned File records\n\
204 7) Eliminate orphaned Path records\n\
205 8) Eliminate orphaned Filename records\n\
206 9) Eliminate orphaned FileSet records\n\
211 1) Toggle modify database flag\n\
212 2) Toggle verbose flag\n\
213 3) Check for duplicate Filename records\n\
214 4) Check for duplicate Path records\n\
215 5) Check for orphaned Jobmedia records\n\
216 6) Check for orphaned File records\n\
217 7) Check for orphaned Path records\n\
218 8) Check for orphaned Filename records\n\
219 9) Check for orphaned FileSet records\n\
224 cmd = get_cmd(_("Select function number: "));
226 int item = atoi(cmd);
230 printf(_("Database will %sbe modified.\n"), fix?"":_("NOT "));
233 verbose = verbose?0:1;
234 printf(_("Verbose is %s\n"), verbose?_("On"):_("Off"));
237 eliminate_duplicate_filenames();
240 eliminate_duplicate_paths();
243 eliminate_orphaned_jobmedia_records();
246 eliminate_orphaned_file_records();
249 eliminate_orphaned_path_records();
252 eliminate_orphaned_filename_records();
255 eliminate_orphaned_fileset_records();
258 eliminate_duplicate_filenames();
259 eliminate_duplicate_paths();
260 eliminate_orphaned_jobmedia_records();
261 eliminate_orphaned_file_records();
262 eliminate_orphaned_path_records();
263 eliminate_orphaned_filename_records();
264 eliminate_orphaned_fileset_records();
274 static int print_name_handler(void *ctx, int num_fields, char **row)
277 printf("%s\n", row[0]);
282 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
284 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
285 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
289 static int print_file_handler(void *ctx, int num_fields, char **row)
291 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
292 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
296 static int print_fileset_handler(void *ctx, int num_fields, char **row)
298 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
299 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
308 * Called here with each id to be added to the list
310 static int id_list_handler(void *ctx, int num_fields, char **row)
312 ID_LIST *lst = (ID_LIST *)ctx;
314 if (lst->num_ids == MAX_ID_LIST_LEN) {
317 if (lst->num_ids == lst->max_ids) {
318 if (lst->max_ids == 0) {
320 lst->Id = (uint32_t *)bmalloc(sizeof(uint32_t) * lst->max_ids);
322 lst->max_ids = (lst->max_ids * 3) / 2;
323 lst->Id = (uint32_t *)brealloc(lst->Id, sizeof(uint32_t) * lst->max_ids);
326 lst->Id[lst->num_ids++] = (uint32_t)strtod(row[0], NULL);
331 * Construct record id list
333 static int make_id_list(char *query, ID_LIST *id_list)
335 id_list->num_ids = 0;
336 id_list->num_del = 0;
337 id_list->tot_ids = 0;
339 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
340 printf("%s", db_strerror(db));
347 * Delete all entries in the list
349 static int delete_id_list(char *query, ID_LIST *id_list)
353 for (i=0; i < id_list->num_ids; i++) {
354 sprintf(buf, query, id_list->Id[i]);
356 printf("Deleting: %s\n", buf);
358 db_sql_query(db, buf, NULL, NULL);
364 * Called here with each name to be added to the list
366 static int name_list_handler(void *ctx, int num_fields, char **row)
368 NAME_LIST *name = (NAME_LIST *)ctx;
370 if (name->num_ids == MAX_ID_LIST_LEN) {
373 if (name->num_ids == name->max_ids) {
374 if (name->max_ids == 0) {
375 name->max_ids = 1000;
376 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
378 name->max_ids = (name->max_ids * 3) / 2;
379 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
382 name->name[name->num_ids++] = bstrdup(row[0]);
388 * Construct name list
390 static int make_name_list(char *query, NAME_LIST *name_list)
392 name_list->num_ids = 0;
393 name_list->num_del = 0;
394 name_list->tot_ids = 0;
396 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
397 printf("%s", db_strerror(db));
404 * Print names in the list
406 static void print_name_list(NAME_LIST *name_list)
410 for (i=0; i < name_list->num_ids; i++) {
411 printf("%s\n", name_list->name[i]);
417 * Free names in the list
419 static void free_name_list(NAME_LIST *name_list)
423 for (i=0; i < name_list->num_ids; i++) {
424 free(name_list->name[i]);
426 name_list->num_ids = 0;
429 static void eliminate_duplicate_filenames()
434 printf("Checking for duplicate Filename entries.\n");
436 /* Make list of duplicated names */
437 query = "SELECT Name,count(Name) as Count FROM Filename GROUP BY Name "
440 if (!make_name_list(query, &name_list)) {
443 printf("Found %d duplicate Filename records.\n", name_list.num_ids);
444 if (name_list.num_ids && verbose && yes_no("Print the list? (yes/no): ")) {
445 print_name_list(&name_list);
448 /* Loop through list of duplicate names */
449 for (int i=0; i<name_list.num_ids; i++) {
450 /* Get all the Ids of each name */
451 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
452 sprintf(buf, "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
453 if (!make_id_list(buf, &id_list)) {
457 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
459 /* Force all records to use the first id then delete the other ids */
460 for (int j=1; j<id_list.num_ids; j++) {
461 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
462 id_list.Id[0], id_list.Id[j]);
463 db_sql_query(db, buf, NULL, NULL);
464 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
466 db_sql_query(db, buf, NULL, NULL);
470 free_name_list(&name_list);
473 static void eliminate_duplicate_paths()
478 printf(_("Checking for duplicate Path entries.\n"));
480 /* Make list of duplicated names */
482 query = "SELECT Path,count(Path) as Count FROM Path "
483 "GROUP BY Path HAVING Count > 1";
485 if (!make_name_list(query, &name_list)) {
488 printf("Found %d duplicate Path records.\n", name_list.num_ids);
489 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
490 print_name_list(&name_list);
493 /* Loop through list of duplicate names */
494 for (int i=0; i<name_list.num_ids; i++) {
495 /* Get all the Ids of each name */
496 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
497 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
499 if (!make_id_list(buf, &id_list)) {
503 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
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;