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 */
133 Pmsg0(0, _("Wrong number of arguments.\n"));
138 Pmsg0(0, _("Working directory not supplied.\n"));
142 /* This is needed by SQLite to find the db */
143 working_directory = argv[0];
151 } else if (argc == 3) {
154 } else if (argc == 4) {
161 db = db_init_database(NULL, db_name, user, password, NULL, 0, NULL);
162 if (!db_open_database(NULL, db)) {
163 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
167 eliminate_duplicate_filenames();
168 eliminate_duplicate_paths();
169 eliminate_orphaned_jobmedia_records();
170 eliminate_orphaned_file_records();
171 eliminate_orphaned_path_records();
172 eliminate_orphaned_filename_records();
173 eliminate_orphaned_fileset_records();
175 do_interactive_mode();
178 db_close_database(NULL, db);
184 static void do_interactive_mode()
189 printf("Hello, this is the database check/correct program.\n\
190 Modify database is %s. Verbose is %s.\n\
191 Please select the fuction you want to perform.\n",
192 fix?"On":"Off", verbose?"On":"Off");
197 1) Toggle modify database flag\n\
198 2) Toggle verbose flag\n\
199 3) Eliminate duplicate Filename records\n\
200 4) Eliminate duplicate Path records\n\
201 5) Eliminate orphaned Jobmedia records\n\
202 6) Eliminate orphaned File records\n\
203 7) Eliminate orphaned Path records\n\
204 8) Eliminate orphaned Filename records\n\
205 9) Eliminate orphaned FileSet records\n\
210 1) Toggle modify database flag\n\
211 2) Toggle verbose flag\n\
212 3) Check for duplicate Filename records\n\
213 4) Check for duplicate Path records\n\
214 5) Check for orphaned Jobmedia records\n\
215 6) Check for orphaned File records\n\
216 7) Check for orphaned Path records\n\
217 8) Check for orphaned Filename records\n\
218 9) Check for orphaned FileSet records\n\
223 cmd = get_cmd(_("Select function number: "));
225 int item = atoi(cmd);
229 printf(_("Database will %sbe modified.\n"), fix?"":_("NOT "));
232 verbose = verbose?0:1;
233 printf(_("Verbose is %s\n"), verbose?_("On"):_("Off"));
236 eliminate_duplicate_filenames();
239 eliminate_duplicate_paths();
242 eliminate_orphaned_jobmedia_records();
245 eliminate_orphaned_file_records();
248 eliminate_orphaned_path_records();
251 eliminate_orphaned_filename_records();
254 eliminate_orphaned_fileset_records();
257 eliminate_duplicate_filenames();
258 eliminate_duplicate_paths();
259 eliminate_orphaned_jobmedia_records();
260 eliminate_orphaned_file_records();
261 eliminate_orphaned_path_records();
262 eliminate_orphaned_filename_records();
263 eliminate_orphaned_fileset_records();
273 static int print_name_handler(void *ctx, int num_fields, char **row)
276 printf("%s\n", row[0]);
281 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
283 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
284 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
288 static int print_file_handler(void *ctx, int num_fields, char **row)
290 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
291 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
295 static int print_fileset_handler(void *ctx, int num_fields, char **row)
297 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
298 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
307 * Called here with each id to be added to the list
309 static int id_list_handler(void *ctx, int num_fields, char **row)
311 ID_LIST *lst = (ID_LIST *)ctx;
313 if (lst->num_ids == MAX_ID_LIST_LEN) {
316 if (lst->num_ids == lst->max_ids) {
317 if (lst->max_ids == 0) {
319 lst->Id = (uint32_t *)bmalloc(sizeof(uint32_t) * lst->max_ids);
321 lst->max_ids = (lst->max_ids * 3) / 2;
322 lst->Id = (uint32_t *)brealloc(lst->Id, sizeof(uint32_t) * lst->max_ids);
325 lst->Id[lst->num_ids++] = (uint32_t)strtod(row[0], NULL);
330 * Construct record id list
332 static int make_id_list(char *query, ID_LIST *id_list)
334 id_list->num_ids = 0;
335 id_list->num_del = 0;
336 id_list->tot_ids = 0;
338 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
339 printf("%s", db_strerror(db));
346 * Delete all entries in the list
348 static int delete_id_list(char *query, ID_LIST *id_list)
350 for (int i=0; i < id_list->num_ids; i++) {
351 sprintf(buf, query, id_list->Id[i]);
353 printf("Deleting: %s\n", buf);
355 db_sql_query(db, buf, NULL, NULL);
361 * Called here with each name to be added to the list
363 static int name_list_handler(void *ctx, int num_fields, char **row)
365 NAME_LIST *name = (NAME_LIST *)ctx;
367 if (name->num_ids == MAX_ID_LIST_LEN) {
370 if (name->num_ids == name->max_ids) {
371 if (name->max_ids == 0) {
372 name->max_ids = 1000;
373 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
375 name->max_ids = (name->max_ids * 3) / 2;
376 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
379 name->name[name->num_ids++] = bstrdup(row[0]);
385 * Construct name list
387 static int make_name_list(char *query, NAME_LIST *name_list)
389 name_list->num_ids = 0;
390 name_list->num_del = 0;
391 name_list->tot_ids = 0;
393 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
394 printf("%s", db_strerror(db));
401 * Print names in the list
403 static void print_name_list(NAME_LIST *name_list)
405 for (int i=0; i < name_list->num_ids; i++) {
406 printf("%s\n", name_list->name[i]);
412 * Free names in the list
414 static void free_name_list(NAME_LIST *name_list)
416 for (int i=0; i < name_list->num_ids; i++) {
417 free(name_list->name[i]);
419 name_list->num_ids = 0;
422 static void eliminate_duplicate_filenames()
427 printf("Checking for duplicate Filename entries.\n");
429 /* Make list of duplicated names */
430 query = "SELECT Name,count(Name) as Count FROM Filename GROUP BY Name "
433 if (!make_name_list(query, &name_list)) {
436 printf("Found %d duplicate Filename records.\n", name_list.num_ids);
437 if (name_list.num_ids && verbose && yes_no("Print the list? (yes/no): ")) {
438 print_name_list(&name_list);
441 /* Loop through list of duplicate names */
442 for (int i=0; i<name_list.num_ids; i++) {
443 /* Get all the Ids of each name */
444 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
445 sprintf(buf, "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
446 if (!make_id_list(buf, &id_list)) {
450 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
452 /* Force all records to use the first id then delete the other ids */
453 for (int j=1; j<id_list.num_ids; j++) {
454 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
455 id_list.Id[0], id_list.Id[j]);
456 db_sql_query(db, buf, NULL, NULL);
457 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
459 db_sql_query(db, buf, NULL, NULL);
463 free_name_list(&name_list);
466 static void eliminate_duplicate_paths()
471 printf(_("Checking for duplicate Path entries.\n"));
473 /* Make list of duplicated names */
475 query = "SELECT Path,count(Path) as Count FROM Path "
476 "GROUP BY Path HAVING Count > 1";
478 if (!make_name_list(query, &name_list)) {
481 printf("Found %d duplicate Path records.\n", name_list.num_ids);
482 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
483 print_name_list(&name_list);
486 /* Loop through list of duplicate names */
487 for (int i=0; i<name_list.num_ids; i++) {
488 /* Get all the Ids of each name */
489 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
490 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
492 if (!make_id_list(buf, &id_list)) {
496 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
498 /* Force all records to use the first id then delete the other ids */
499 for (int j=1; j<id_list.num_ids; j++) {
500 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
501 id_list.Id[0], id_list.Id[j]);
502 db_sql_query(db, buf, NULL, NULL);
503 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
505 db_sql_query(db, buf, NULL, NULL);
509 free_name_list(&name_list);
512 static void eliminate_orphaned_jobmedia_records()
516 printf("Checking for orphaned JobMedia entries.\n");
517 query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
518 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
519 "WHERE Job.JobId IS NULL";
520 if (!make_id_list(query, &id_list)) {
523 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
524 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
525 for (int i=0; i < id_list.num_ids; i++) {
527 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
528 "WHERE JobMedia.JobMediaId=%u AND Media.MediaId=JobMedia.MediaId", id_list.Id[i]);
529 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
530 printf("%s\n", db_strerror(db));
535 if (fix && id_list.num_ids > 0) {
536 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
537 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
541 static void eliminate_orphaned_file_records()
545 printf("Checking for orphaned File entries. This may take some time!\n");
546 query = "SELECT File.FileId,Job.JobId FROM File "
547 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
548 "WHERE Job.JobId IS NULL";
549 if (!make_id_list(query, &id_list)) {
552 printf("Found %d orphaned File records.\n", id_list.num_ids);
553 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
554 for (int i=0; i < id_list.num_ids; i++) {
556 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
557 "WHERE File.FileId=%u AND File.FilenameId=Filename.FilenameId", id_list.Id[i]);
558 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
559 printf("%s\n", db_strerror(db));
564 if (fix && id_list.num_ids > 0) {
565 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
566 delete_id_list("DELETE FROM File WHERE FileId=%u", &id_list);
570 static void eliminate_orphaned_path_records()
574 printf("Checking for orphaned Path entries. This may take some time!\n");
575 query = "SELECT Path.PathId,File.PathId FROM Path "
576 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
577 "GROUP BY Path.PathId HAVING File.PathId IS NULL";
578 if (!make_id_list(query, &id_list)) {
581 printf("Found %d orphaned Path records.\n", id_list.num_ids);
582 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
583 for (int i=0; i < id_list.num_ids; i++) {
584 sprintf(buf, "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
585 db_sql_query(db, buf, print_name_handler, NULL);
589 if (fix && id_list.num_ids > 0) {
590 printf("Deleting %d orphaned Path records.\n", id_list.num_ids);
591 delete_id_list("DELETE FROM Path WHERE PathId=%u", &id_list);
595 static void eliminate_orphaned_filename_records()
599 printf("Checking for orphaned Filename entries. This may take some time!\n");
600 query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
601 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
602 "WHERE File.FilenameId IS NULL";
604 if (!make_id_list(query, &id_list)) {
607 printf("Found %d orphaned Filename records.\n", id_list.num_ids);
608 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
609 for (int i=0; i < id_list.num_ids; i++) {
610 sprintf(buf, "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
611 db_sql_query(db, buf, print_name_handler, NULL);
615 if (fix && id_list.num_ids > 0) {
616 printf("Deleting %d orphaned Filename records.\n", id_list.num_ids);
617 delete_id_list("DELETE FROM Filename WHERE FilenameId=%u", &id_list);
621 static void eliminate_orphaned_fileset_records()
625 printf("Checking for orphaned FileSet entries. This takes some time!\n");
626 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
627 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
628 "WHERE Job.FileSetId IS NULL";
629 if (!make_id_list(query, &id_list)) {
632 printf("Found %d orphaned FileSet records.\n", id_list.num_ids);
633 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
634 for (int i=0; i < id_list.num_ids; i++) {
635 sprintf(buf, "SELECT FileSetId,FileSet,MD5 FROM FileSet "
636 "WHERE FileSetId=%u", id_list.Id[i]);
637 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
638 printf("%s\n", db_strerror(db));
643 if (fix && id_list.num_ids > 0) {
644 printf("Deleting %d orphaned FileSet records.\n", id_list.num_ids);
645 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%u", &id_list);
651 * Gen next input command from the terminal
653 static char *get_cmd(char *prompt)
655 static char cmd[1000];
657 printf("%s", prompt);
658 if (fgets(cmd, sizeof(cmd), stdin) == NULL)
661 strip_trailing_junk(cmd);
665 static int yes_no(char *prompt)
668 cmd = get_cmd(prompt);
669 return strcasecmp(cmd, "yes") == 0;