2 Bacula® - The Network Backup Solution
4 Copyright (C) 2002-2008 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version two of the GNU General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
30 * Program to check a Bacula database for consistency and to
33 * Kern E. Sibbald, August 2002
40 #include "cats/cats.h"
41 #include "lib/runscript.h"
42 #include "dird/dird_conf.h"
44 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
47 int generate_daemon_event(JCR *jcr, const char *event)
50 typedef struct s_id_ctx {
51 int64_t *Id; /* ids to be modified */
52 int num_ids; /* ids stored */
53 int max_ids; /* size of array */
54 int num_del; /* number deleted */
55 int tot_ids; /* total to process */
58 typedef struct s_name_ctx {
59 char **name; /* list of names */
60 int num_ids; /* ids stored */
61 int max_ids; /* size of array */
62 int num_del; /* number deleted */
63 int tot_ids; /* total to process */
68 /* Global variables */
69 static bool fix = false;
70 static bool batch = false;
72 static ID_LIST id_list;
73 static NAME_LIST name_list;
74 static char buf[20000];
75 static bool quit = false;
76 static CONFIG *config;
78 #define MAX_ID_LIST_LEN 10000000
80 /* Forward referenced functions */
81 static int make_id_list(const char *query, ID_LIST *id_list);
82 static int delete_id_list(const char *query, ID_LIST *id_list);
83 static int make_name_list(const char *query, NAME_LIST *name_list);
84 static void print_name_list(NAME_LIST *name_list);
85 static void free_name_list(NAME_LIST *name_list);
86 static char *get_cmd(const char *prompt);
87 static void eliminate_duplicate_filenames();
88 static void eliminate_duplicate_paths();
89 static void eliminate_orphaned_jobmedia_records();
90 static void eliminate_orphaned_file_records();
91 static void eliminate_orphaned_path_records();
92 static void eliminate_orphaned_filename_records();
93 static void eliminate_orphaned_fileset_records();
94 static void eliminate_orphaned_client_records();
95 static void eliminate_orphaned_job_records();
96 static void eliminate_admin_records();
97 static void eliminate_restore_records();
98 static void repair_bad_paths();
99 static void repair_bad_filenames();
100 static void do_interactive_mode();
101 static bool yes_no(const char *prompt);
107 "Usage: dbcheck [-c config] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
109 " -C catalog name in the director conf file\n"
110 " -c Director conf filename\n"
111 " -d <nn> set debug level to <nn>\n"
112 " -dt print timestamp in debug output\n"
113 " -f fix inconsistencies\n"
115 " -? print this message\n\n");
119 int main (int argc, char *argv[])
122 const char *user, *password, *db_name, *dbhost;
124 char *configfile = NULL;
125 char *catalogname = NULL;
128 setlocale(LC_ALL, "");
129 bindtextdomain("bacula", LOCALEDIR);
130 textdomain("bacula");
132 my_name_is(argc, argv, "dbcheck");
133 init_msg(NULL, NULL); /* setup message handler */
135 memset(&id_list, 0, sizeof(id_list));
136 memset(&name_list, 0, sizeof(name_list));
139 while ((ch = getopt(argc, argv, "bc:C:d:fv?")) != -1) {
141 case 'b': /* batch */
145 case 'C': /* CatalogName */
146 catalogname = optarg;
149 case 'c': /* configfile */
153 case 'd': /* debug level */
154 if (*optarg == 't') {
155 dbg_timestamp = true;
157 debug_level = atoi(optarg);
158 if (debug_level <= 0) {
164 case 'f': /* fix inconsistencies */
186 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
188 config = new_config_parser();
189 parse_dir_config(config, configfile, M_ERROR_TERM);
191 foreach_res(catalog, R_CATALOG) {
192 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
195 } else if (!catalogname) { // stop on first if no catalogname is given
203 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
205 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
211 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
214 Pmsg0(0, _("Error no Director resource defined.\n"));
217 set_working_directory(director->working_directory);
218 db_name = catalog->db_name;
219 user = catalog->db_user;
220 password = catalog->db_password;
221 dbhost = catalog->db_address;
222 if (dbhost && dbhost[0] == 0) {
225 dbport = catalog->db_port;
229 Pmsg0(0, _("Wrong number of arguments.\n"));
234 Pmsg0(0, _("Working directory not supplied.\n"));
238 /* This is needed by SQLite to find the db */
239 working_directory = argv[0];
248 } else if (argc == 3) {
251 } else if (argc == 4) {
255 } else if (argc == 5) {
260 } else if (argc == 6) {
266 dbport = strtol(argv[5], &endptr, 10);
267 if (*endptr != '\0') {
268 Pmsg0(0, _("Database port must be a numeric value.\n"));
270 } else if (errno == ERANGE) {
271 Pmsg0(0, _("Database port must be a int value.\n"));
278 db = db_init_database(NULL, db_name, user, password, dbhost, dbport, NULL, 0);
279 if (!db_open_database(NULL, db)) {
280 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
286 repair_bad_filenames();
287 eliminate_duplicate_filenames();
288 eliminate_duplicate_paths();
289 eliminate_orphaned_jobmedia_records();
290 eliminate_orphaned_file_records();
291 eliminate_orphaned_path_records();
292 eliminate_orphaned_filename_records();
293 eliminate_orphaned_fileset_records();
294 eliminate_orphaned_client_records();
295 eliminate_orphaned_job_records();
296 eliminate_admin_records();
297 eliminate_restore_records();
299 do_interactive_mode();
302 db_close_database(NULL, db);
308 static void do_interactive_mode()
312 printf(_("Hello, this is the database check/correct program.\n"));
314 printf(_("Modify database is on."));
316 printf(_("Modify database is off."));
318 printf(_(" Verbose is on.\n"));
320 printf(_(" Verbose is off.\n"));
322 printf(_("Please select the fuction you want to perform.\n"));
327 " 1) Toggle modify database flag\n"
328 " 2) Toggle verbose flag\n"
329 " 3) Repair bad Filename records\n"
330 " 4) Repair bad Path records\n"
331 " 5) Eliminate duplicate Filename records\n"
332 " 6) Eliminate duplicate Path records\n"
333 " 7) Eliminate orphaned Jobmedia records\n"
334 " 8) Eliminate orphaned File records\n"
335 " 9) Eliminate orphaned Path records\n"
336 " 10) Eliminate orphaned Filename records\n"
337 " 11) Eliminate orphaned FileSet records\n"
338 " 12) Eliminate orphaned Client records\n"
339 " 13) Eliminate orphaned Job records\n"
340 " 14) Eliminate all Admin records\n"
341 " 15) Eliminate all Restore records\n"
346 " 1) Toggle modify database flag\n"
347 " 2) Toggle verbose flag\n"
348 " 3) Check for bad Filename records\n"
349 " 4) Check for bad Path records\n"
350 " 5) Check for duplicate Filename records\n"
351 " 6) Check for duplicate Path records\n"
352 " 7) Check for orphaned Jobmedia records\n"
353 " 8) Check for orphaned File records\n"
354 " 9) Check for orphaned Path records\n"
355 " 10) Check for orphaned Filename records\n"
356 " 11) Check for orphaned FileSet records\n"
357 " 12) Check for orphaned Client records\n"
358 " 13) Check for orphaned Job records\n"
359 " 14) Check for all Admin records\n"
360 " 15) Check for all Restore records\n"
365 cmd = get_cmd(_("Select function number: "));
367 int item = atoi(cmd);
372 printf(_("Database will be modified.\n"));
374 printf(_("Database will NOT be modified.\n"));
377 verbose = verbose?0:1;
379 printf(_(" Verbose is on.\n"));
381 printf(_(" Verbose is off.\n"));
384 repair_bad_filenames();
390 eliminate_duplicate_filenames();
393 eliminate_duplicate_paths();
396 eliminate_orphaned_jobmedia_records();
399 eliminate_orphaned_file_records();
402 eliminate_orphaned_path_records();
405 eliminate_orphaned_filename_records();
408 eliminate_orphaned_fileset_records();
411 eliminate_orphaned_client_records();
414 eliminate_orphaned_job_records();
417 eliminate_admin_records();
420 eliminate_restore_records();
423 repair_bad_filenames();
425 eliminate_duplicate_filenames();
426 eliminate_duplicate_paths();
427 eliminate_orphaned_jobmedia_records();
428 eliminate_orphaned_file_records();
429 eliminate_orphaned_path_records();
430 eliminate_orphaned_filename_records();
431 eliminate_orphaned_fileset_records();
432 eliminate_orphaned_client_records();
433 eliminate_orphaned_job_records();
434 eliminate_admin_records();
435 eliminate_restore_records();
445 static int print_name_handler(void *ctx, int num_fields, char **row)
448 printf("%s\n", row[0]);
453 static int get_name_handler(void *ctx, int num_fields, char **row)
455 POOLMEM *buf = (POOLMEM *)ctx;
457 pm_strcpy(&buf, row[0]);
462 static int print_job_handler(void *ctx, int num_fields, char **row)
464 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
465 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
470 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
472 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
473 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
477 static int print_file_handler(void *ctx, int num_fields, char **row)
479 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
480 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
484 static int print_fileset_handler(void *ctx, int num_fields, char **row)
486 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
487 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
491 static int print_client_handler(void *ctx, int num_fields, char **row)
493 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
494 NPRT(row[0]), NPRT(row[1]));
500 * Called here with each id to be added to the list
502 static int id_list_handler(void *ctx, int num_fields, char **row)
504 ID_LIST *lst = (ID_LIST *)ctx;
506 if (lst->num_ids == MAX_ID_LIST_LEN) {
509 if (lst->num_ids == lst->max_ids) {
510 if (lst->max_ids == 0) {
511 lst->max_ids = 10000;
512 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
514 lst->max_ids = (lst->max_ids * 3) / 2;
515 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
518 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
523 * Construct record id list
525 static int make_id_list(const char *query, ID_LIST *id_list)
527 id_list->num_ids = 0;
528 id_list->num_del = 0;
529 id_list->tot_ids = 0;
531 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
532 printf("%s", db_strerror(db));
539 * Delete all entries in the list
541 static int delete_id_list(const char *query, ID_LIST *id_list)
544 for (int i=0; i < id_list->num_ids; i++) {
545 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
547 printf(_("Deleting: %s\n"), buf);
549 db_sql_query(db, buf, NULL, NULL);
555 * Called here with each name to be added to the list
557 static int name_list_handler(void *ctx, int num_fields, char **row)
559 NAME_LIST *name = (NAME_LIST *)ctx;
561 if (name->num_ids == MAX_ID_LIST_LEN) {
564 if (name->num_ids == name->max_ids) {
565 if (name->max_ids == 0) {
566 name->max_ids = 10000;
567 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
569 name->max_ids = (name->max_ids * 3) / 2;
570 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
573 name->name[name->num_ids++] = bstrdup(row[0]);
579 * Construct name list
581 static int make_name_list(const char *query, NAME_LIST *name_list)
583 name_list->num_ids = 0;
584 name_list->num_del = 0;
585 name_list->tot_ids = 0;
587 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
588 printf("%s", db_strerror(db));
595 * Print names in the list
597 static void print_name_list(NAME_LIST *name_list)
599 for (int i=0; i < name_list->num_ids; i++) {
600 printf("%s\n", name_list->name[i]);
606 * Free names in the list
608 static void free_name_list(NAME_LIST *name_list)
610 for (int i=0; i < name_list->num_ids; i++) {
611 free(name_list->name[i]);
613 name_list->num_ids = 0;
616 static void eliminate_duplicate_filenames()
621 printf(_("Checking for duplicate Filename entries.\n"));
623 /* Make list of duplicated names */
624 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
625 "HAVING count(Name) > 1";
627 if (!make_name_list(query, &name_list)) {
630 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
631 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
632 print_name_list(&name_list);
638 /* Loop through list of duplicate names */
639 for (int i=0; i<name_list.num_ids; i++) {
640 /* Get all the Ids of each name */
641 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
642 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
646 if (!make_id_list(buf, &id_list)) {
650 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
652 /* Force all records to use the first id then delete the other ids */
653 for (int j=1; j<id_list.num_ids; j++) {
654 char ed1[50], ed2[50];
655 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
656 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
660 db_sql_query(db, buf, NULL, NULL);
661 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
666 db_sql_query(db, buf, NULL, NULL);
670 free_name_list(&name_list);
673 static void eliminate_duplicate_paths()
678 printf(_("Checking for duplicate Path entries.\n"));
680 /* Make list of duplicated names */
682 query = "SELECT Path, count(Path) as Count FROM Path "
683 "GROUP BY Path HAVING count(Path) > 1";
685 if (!make_name_list(query, &name_list)) {
688 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
689 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
690 print_name_list(&name_list);
696 /* Loop through list of duplicate names */
697 for (int i=0; i<name_list.num_ids; i++) {
698 /* Get all the Ids of each name */
699 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
700 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
704 if (!make_id_list(buf, &id_list)) {
708 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
710 /* Force all records to use the first id then delete the other ids */
711 for (int j=1; j<id_list.num_ids; j++) {
712 char ed1[50], ed2[50];
713 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
714 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
718 db_sql_query(db, buf, NULL, NULL);
719 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
723 db_sql_query(db, buf, NULL, NULL);
727 free_name_list(&name_list);
730 static void eliminate_orphaned_jobmedia_records()
732 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
733 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
734 "WHERE Job.JobId IS NULL LIMIT 300000";
736 printf(_("Checking for orphaned JobMedia entries.\n"));
737 if (!make_id_list(query, &id_list)) {
740 /* Loop doing 300000 at a time */
741 while (id_list.num_ids != 0) {
742 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
743 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
744 for (int i=0; i < id_list.num_ids; i++) {
746 bsnprintf(buf, sizeof(buf),
747 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
748 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
749 edit_int64(id_list.Id[i], ed1));
750 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
751 printf("%s\n", db_strerror(db));
759 if (fix && id_list.num_ids > 0) {
760 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
761 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
763 break; /* get out if not updating db */
765 if (!make_id_list(query, &id_list)) {
771 static void eliminate_orphaned_file_records()
773 const char *query = "SELECT File.FileId,Job.JobId FROM File "
774 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
775 "WHERE Job.JobId IS NULL LIMIT 300000";
777 printf(_("Checking for orphaned File entries. This may take some time!\n"));
779 printf("%s\n", query);
781 if (!make_id_list(query, &id_list)) {
784 /* Loop doing 300000 at a time */
785 while (id_list.num_ids != 0) {
786 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
787 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
788 for (int i=0; i < id_list.num_ids; i++) {
790 bsnprintf(buf, sizeof(buf),
791 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
792 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
793 edit_int64(id_list.Id[i], ed1));
794 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
795 printf("%s\n", db_strerror(db));
802 if (fix && id_list.num_ids > 0) {
803 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
804 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
806 break; /* get out if not updating db */
808 if (!make_id_list(query, &id_list)) {
814 static void eliminate_orphaned_path_records()
816 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
817 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
818 "WHERE File.PathId IS NULL LIMIT 300000";
820 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
822 printf("%s\n", query);
824 if (!make_id_list(query, &id_list)) {
827 /* Loop doing 300000 at a time */
828 while (id_list.num_ids != 0) {
829 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
830 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
831 for (int i=0; i < id_list.num_ids; i++) {
833 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
834 edit_int64(id_list.Id[i], ed1));
835 db_sql_query(db, buf, print_name_handler, NULL);
841 if (fix && id_list.num_ids > 0) {
842 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
843 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
845 break; /* get out if not updating db */
847 if (!make_id_list(query, &id_list)) {
853 static void eliminate_orphaned_filename_records()
855 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
856 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
857 "WHERE File.FilenameId IS NULL LIMIT 300000";
859 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
861 printf("%s\n", query);
863 if (!make_id_list(query, &id_list)) {
866 /* Loop doing 300000 at a time */
867 while (id_list.num_ids != 0) {
868 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
869 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
870 for (int i=0; i < id_list.num_ids; i++) {
872 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
873 edit_int64(id_list.Id[i], ed1));
874 db_sql_query(db, buf, print_name_handler, NULL);
880 if (fix && id_list.num_ids > 0) {
881 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
882 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
884 break; /* get out if not updating db */
886 if (!make_id_list(query, &id_list)) {
892 static void eliminate_orphaned_fileset_records()
896 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
897 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
898 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
899 "WHERE Job.FileSetId IS NULL";
901 printf("%s\n", query);
903 if (!make_id_list(query, &id_list)) {
906 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
907 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
908 for (int i=0; i < id_list.num_ids; i++) {
910 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
911 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
912 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
913 printf("%s\n", db_strerror(db));
920 if (fix && id_list.num_ids > 0) {
921 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
922 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
926 static void eliminate_orphaned_client_records()
930 printf(_("Checking for orphaned Client entries.\n"));
932 * Wiffle through Client for every Client
933 * joining with the Job table including every Client even if
934 * there is not a match in Job (left outer join), then
935 * filter out only those where no Job points to a Client
936 * i.e. Job.Client is NULL
938 query = "SELECT Client.ClientId,Client.Name FROM Client "
939 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
940 "WHERE Job.ClientId IS NULL";
942 printf("%s\n", query);
944 if (!make_id_list(query, &id_list)) {
947 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
948 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
949 for (int i=0; i < id_list.num_ids; i++) {
951 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
952 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
953 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
954 printf("%s\n", db_strerror(db));
961 if (fix && id_list.num_ids > 0) {
962 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
963 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
967 static void eliminate_orphaned_job_records()
971 printf(_("Checking for orphaned Job entries.\n"));
973 * Wiffle through Job for every Job
974 * joining with the Client table including every Job even if
975 * there is not a match in Client (left outer join), then
976 * filter out only those where no Client exists
977 * i.e. Client.Name is NULL
979 query = "SELECT Job.JobId,Job.Name FROM Job "
980 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
981 "WHERE Client.Name IS NULL";
983 printf("%s\n", query);
985 if (!make_id_list(query, &id_list)) {
988 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
989 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
990 for (int i=0; i < id_list.num_ids; i++) {
992 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
993 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
994 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
995 printf("%s\n", db_strerror(db));
1002 if (fix && id_list.num_ids > 0) {
1003 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1004 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1005 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1006 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1007 printf(_("Deleting Log records of orphaned Job records.\n"));
1008 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1013 static void eliminate_admin_records()
1017 printf(_("Checking for Admin Job entries.\n"));
1018 query = "SELECT Job.JobId FROM Job "
1019 "WHERE Job.Type='D'";
1021 printf("%s\n", query);
1023 if (!make_id_list(query, &id_list)) {
1026 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1027 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1028 for (int i=0; i < id_list.num_ids; i++) {
1030 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1031 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1032 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1033 printf("%s\n", db_strerror(db));
1040 if (fix && id_list.num_ids > 0) {
1041 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1042 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1046 static void eliminate_restore_records()
1050 printf(_("Checking for Restore Job entries.\n"));
1051 query = "SELECT Job.JobId FROM Job "
1052 "WHERE Job.Type='R'";
1054 printf("%s\n", query);
1056 if (!make_id_list(query, &id_list)) {
1059 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1060 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1061 for (int i=0; i < id_list.num_ids; i++) {
1063 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1064 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1065 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1066 printf("%s\n", db_strerror(db));
1073 if (fix && id_list.num_ids > 0) {
1074 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1075 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1082 static void repair_bad_filenames()
1087 printf(_("Checking for Filenames with a trailing slash\n"));
1088 query = "SELECT FilenameId,Name from Filename "
1089 "WHERE Name LIKE '%/'";
1091 printf("%s\n", query);
1093 if (!make_id_list(query, &id_list)) {
1096 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1097 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1098 for (i=0; i < id_list.num_ids; i++) {
1100 bsnprintf(buf, sizeof(buf),
1101 "SELECT Name FROM Filename WHERE FilenameId=%s",
1102 edit_int64(id_list.Id[i], ed1));
1103 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1104 printf("%s\n", db_strerror(db));
1111 if (fix && id_list.num_ids > 0) {
1112 POOLMEM *name = get_pool_memory(PM_FNAME);
1113 char esc_name[5000];
1114 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1115 for (i=0; i < id_list.num_ids; i++) {
1118 bsnprintf(buf, sizeof(buf),
1119 "SELECT Name FROM Filename WHERE FilenameId=%s",
1120 edit_int64(id_list.Id[i], ed1));
1121 if (!db_sql_query(db, buf, get_name_handler, name)) {
1122 printf("%s\n", db_strerror(db));
1124 /* Strip trailing slash(es) */
1125 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1133 db_escape_string(NULL, db, esc_name, name, len);
1135 bsnprintf(buf, sizeof(buf),
1136 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1137 esc_name, edit_int64(id_list.Id[i], ed1));
1139 printf("%s\n", buf);
1141 db_sql_query(db, buf, NULL, NULL);
1146 static void repair_bad_paths()
1151 printf(_("Checking for Paths without a trailing slash\n"));
1152 query = "SELECT PathId,Path from Path "
1153 "WHERE Path NOT LIKE '%/'";
1155 printf("%s\n", query);
1157 if (!make_id_list(query, &id_list)) {
1160 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1161 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1162 for (i=0; i < id_list.num_ids; i++) {
1164 bsnprintf(buf, sizeof(buf),
1165 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1166 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1167 printf("%s\n", db_strerror(db));
1174 if (fix && id_list.num_ids > 0) {
1175 POOLMEM *name = get_pool_memory(PM_FNAME);
1176 char esc_name[5000];
1177 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1178 for (i=0; i < id_list.num_ids; i++) {
1181 bsnprintf(buf, sizeof(buf),
1182 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1183 if (!db_sql_query(db, buf, get_name_handler, name)) {
1184 printf("%s\n", db_strerror(db));
1186 /* Strip trailing blanks */
1187 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1190 /* Add trailing slash */
1191 len = pm_strcat(&name, "/");
1192 db_escape_string(NULL, db, esc_name, name, len);
1193 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1194 esc_name, edit_int64(id_list.Id[i], ed1));
1196 printf("%s\n", buf);
1198 db_sql_query(db, buf, NULL, NULL);
1205 * Gen next input command from the terminal
1207 static char *get_cmd(const char *prompt)
1209 static char cmd[1000];
1211 printf("%s", prompt);
1212 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1217 strip_trailing_junk(cmd);
1221 static bool yes_no(const char *prompt)
1224 cmd = get_cmd(prompt);
1229 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1232 bool python_set_prog(JCR*, char const*) { return false; }