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(NULL, 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(NULL, 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 (name_list.num_ids && 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);
454 if (!make_id_list(buf, &id_list)) {
458 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
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 if (!make_id_list(buf, &id_list)) {
504 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
506 /* Force all records to use the first id then delete the other ids */
507 for (int j=1; j<id_list.num_ids; j++) {
508 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
509 id_list.Id[0], id_list.Id[j]);
510 db_sql_query(db, buf, NULL, NULL);
511 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
513 db_sql_query(db, buf, NULL, NULL);
517 free_name_list(&name_list);
520 static void eliminate_orphaned_jobmedia_records()
524 printf("Checking for orphaned JobMedia entries.\n");
525 query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
526 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
527 "WHERE Job.JobId IS NULL";
528 if (!make_id_list(query, &id_list)) {
531 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
532 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
534 for (i=0; i < id_list.num_ids; i++) {
536 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
537 "WHERE JobMedia.JobMediaId=%u AND Media.MediaId=JobMedia.MediaId", id_list.Id[i]);
538 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
539 printf("%s\n", db_strerror(db));
544 if (fix && id_list.num_ids > 0) {
545 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
546 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
550 static void eliminate_orphaned_file_records()
554 printf("Checking for orphaned File entries. This may take some time!\n");
555 query = "SELECT File.FileId,Job.JobId FROM File "
556 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
557 "WHERE Job.JobId IS NULL";
558 if (!make_id_list(query, &id_list)) {
561 printf("Found %d orphaned File records.\n", id_list.num_ids);
562 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
564 for (i=0; i < id_list.num_ids; i++) {
566 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
567 "WHERE File.FileId=%u AND File.FilenameId=Filename.FilenameId", id_list.Id[i]);
568 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
569 printf("%s\n", db_strerror(db));
574 if (fix && id_list.num_ids > 0) {
575 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
576 delete_id_list("DELETE FROM File WHERE FileId=%u", &id_list);
580 static void eliminate_orphaned_path_records()
584 printf("Checking for orphaned Path entries. This may take some time!\n");
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);
592 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
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 query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
612 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
613 "WHERE File.FilenameId IS NULL";
615 if (!make_id_list(query, &id_list)) {
618 printf("Found %d orphaned Filename records.\n", id_list.num_ids);
619 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
621 for (i=0; i < id_list.num_ids; i++) {
622 sprintf(buf, "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
623 db_sql_query(db, buf, print_name_handler, NULL);
627 if (fix && id_list.num_ids > 0) {
628 printf("Deleting %d orphaned Filename records.\n", id_list.num_ids);
629 delete_id_list("DELETE FROM Filename WHERE FilenameId=%u", &id_list);
633 static void eliminate_orphaned_fileset_records()
637 printf("Checking for orphaned FileSet entries. This takes some time!\n");
638 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
639 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
640 "WHERE Job.FileSetId IS NULL";
641 if (!make_id_list(query, &id_list)) {
644 printf("Found %d orphaned FileSet records.\n", id_list.num_ids);
645 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
647 for (i=0; i < id_list.num_ids; i++) {
648 sprintf(buf, "SELECT FileSetId,FileSet,MD5 FROM FileSet "
649 "WHERE FileSetId=%u", id_list.Id[i]);
650 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
651 printf("%s\n", db_strerror(db));
656 if (fix && id_list.num_ids > 0) {
657 printf("Deleting %d orphaned FileSet records.\n", id_list.num_ids);
658 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%u", &id_list);
664 * Gen next input command from the terminal
666 static char *get_cmd(char *prompt)
668 static char cmd[1000];
670 printf("%s", prompt);
671 if (fgets(cmd, sizeof(cmd), stdin) == NULL)
674 strip_trailing_junk(cmd);
678 static int yes_no(char *prompt)
681 cmd = get_cmd(prompt);
682 return strcasecmp(cmd, "yes") == 0;