1 -- This script does the same as dbcheck, but in full SQL in order to be faster
2 -- To run it, exec it like this : psql -U bacula bacula (YOUR username and database)
4 -- It will tell you what it does. At the end you'll have to commit yourself. Check the numbers of altered records before ...
7 -- - SELECT PathId,Path from Path "
8 -- "WHERE Path NOT LIKE '%/'
9 -- - ask for confirmation
10 -- - add a slash, doing one update for each record to be updated ...
13 -- UPDATE Path Set Path=Path || '/' WHERE Path NOT LIKE '%/'; # Should work everywhere
15 --repair_bad_filenames():
16 -- - SELECT FileNameId,Name from FileName
17 -- WHERE Name LIKE '%/'
18 -- - ask for confirmation
19 -- - remove the slash, one update per row ...
22 -- UPDATE FileName Set Name=substr(Name,1,char_length(Name)-1) WHERE Name LIKE '%/'; # Works at least with Pg and Mysql
24 --eliminate_duplicate_filenames():
25 -- - Lists all min filenameids from filename where there are duplicates on name
26 -- - Updates filetable to this entry instead of one of its duplicates
27 -- - Deletes filenameids from filename which are duplicate and not the min filenameids
30 -- CREATE TEMP TABLE t1 AS SELECT Name,min(FileNameId) AS minfilenameid FROM FileName GROUP BY Name HAVING count(*) > 1;
31 -- CREATE TEMP TABLE t2 AS SELECT FileName.Name, FileName.FileNameId, t1.minfilenameid from FileName join t1 ON (FileName.Name=t1.Name) WHERE FileNameId <> minfilenameid;
32 -- UPDATE File SET FileNameId=(SELECT t2.minfilenameid FROM t2 WHERE t2.FileNameId=File.FileNameId) WHERE FileNameId IN (SELECT FileNameId FROM t2);
33 -- DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t2);
37 --eliminate_duplicate_paths():
38 -- Does exactly the same as above ...
41 -- CREATE TEMP TABLE t1 AS SELECT Path,min(PathId) AS minpathid FROM Path GROUP BY Path HAVING count(*) > 1;
42 -- CREATE TEMP TABLE t2 AS SELECT Path.Path, Path.PathId, t1.minpathid from Path join t1 ON (Path.Path=t1.Path) WHERE PathId <> minpathid;
43 -- UPDATE Path SET PathId=(SELECT t2.minpathid FROM t2 WHERE t2.PathId=Path.PathId) WHERE PathId IN (SELECT PathId FROM t2);
44 -- DELETE FROM Path WHERE PathId IN (SELECT PathId FROM t2);
49 --All the orphaned below delete records from a table when they are not referenced anymore in the others...
51 --eliminate_orphaned_jobmedia_records():
53 -- DELETE FROM JobMedia WHERE JobId NOT IN (SELECT JobId FROM Job) OR MediaID NOT IN (SELECT MediaID FROM Media);
55 --eliminate_orphaned_file_records():
57 -- DELETE FROM File WHERE JobId NOT IN (SELECT JobId FROM JOB);
59 --eliminate_orphaned_path_records():
60 --Here, the problem is that File is a big table ... we'd better avoid NOT IN on it ...
62 -- CREATE TEMP TABLE t1 AS
64 -- FROM Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId)
65 -- WHERE File.PathId IS NULL;
66 -- DELETE FROM Path WHERE PathId IN (SELECT PathID FROM t1);
69 --eliminate_orphaned_filename_records():
70 --Here, again, the problem is that File is a big table ... we'd better avoid NOT IN on it ...
72 -- CREATE TEMP TABLE t1 AS
73 -- SELECT FileName.FileNameId
74 -- FROM FileName LEFT OUTER JOIN File ON (FileName.FileNameId=File.FileNameId)
75 -- WHERE File.FileNameId IS NULL;
76 -- DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t1);
79 --eliminate_orphaned_fileset_records():
82 -- DELETE FROM FileSet WHERE FileSetId NOT IN (SELECT DISTINCT FileSetId FROM Job);
84 --eliminate_orphaned_client_records():
86 -- DELETE FROM Client WHERE ClientId NOT IN (SELECT DISTINCT ClientId FROM Job);
88 --eliminate_orphaned_job_records():
90 -- DELETE FROM Job WHERE ClientId NOT IN (SELECT ClientId FROM Client);
92 --eliminate_admin_records():
94 -- DELETE FROM Job WHERE Job.Type='D';
96 --eliminate_restore_records():
98 -- DELETE FROM Job WHERE Job.Type='R';
102 --One script that does it all :
108 SELECT('eliminate_admin_records()');
109 DELETE FROM Job WHERE Job.Type='D';
111 SELECT('eliminate_restore_records()');
112 DELETE FROM Job WHERE Job.Type='R';
114 SELECT('repair_bad_paths()');
115 UPDATE Path Set Path=Path||'/' WHERE Path NOT LIKE '%/' AND Path <> '';
117 SELECT('repair_bad_filenames()');
118 UPDATE FileName Set Name=substr(Name,1,char_length(Name)-1) WHERE Name LIKE '%/';
120 SELECT('eliminate_duplicate_filenames()');
121 CREATE TEMP TABLE t1 AS SELECT Name,min(FileNameId) AS minfilenameid FROM FileName GROUP BY Name HAVING count(*) > 1;
122 CREATE TEMP TABLE t2 AS SELECT FileName.Name, FileName.FileNameId, t1.minfilenameid from FileName join t1 ON (FileName.Name=t1.Name) WHERE FileNameId <> minfilenameid;
123 UPDATE File SET FileNameId=(SELECT t2.minfilenameid FROM t2 WHERE t2.FileNameId=File.FileNameId) WHERE FileNameId IN (SELECT FileNameId FROM t2);
124 DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t2);
128 SELECT('eliminate_duplicate_paths()');
129 CREATE TEMP TABLE t1 AS SELECT Path,min(PathId) AS minpathid FROM Path GROUP BY Path HAVING count(*) > 1;
130 CREATE TEMP TABLE t2 AS SELECT Path.Path, Path.PathId, t1.minpathid from Path join t1 ON (Path.Path=t1.Path) WHERE PathId <> minpathid;
131 UPDATE Path SET PathId=(SELECT t2.minpathid FROM t2 WHERE t2.PathId=Path.PathId) WHERE PathId IN (SELECT PathId FROM t2);
132 DELETE FROM Path WHERE PathId IN (SELECT PathId FROM t2);
136 SELECT('eliminate_orphaned_job_records()');
137 DELETE FROM Job WHERE ClientId NOT IN (SELECT ClientId FROM Client);
139 SELECT('eliminate_orphaned_jobmedia_records()');
140 DELETE FROM JobMedia WHERE JobId NOT IN (SELECT JobId FROM Job) OR MediaID NOT IN (SELECT MediaID FROM Media);
142 SELECT('eliminate_orphaned_file_records()');
143 DELETE FROM File WHERE JobId NOT IN (SELECT JobId FROM JOB);
145 SELECT('eliminate_orphaned_path_records()');
146 CREATE TEMP TABLE t1 AS
148 FROM Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId)
149 WHERE File.PathId IS NULL;
150 DELETE FROM Path WHERE PathId IN (SELECT PathID FROM t1);
153 SELECT('eliminate_orphaned_filename_records()');
154 CREATE TEMP TABLE t1 AS
155 SELECT FileName.FileNameId
156 FROM FileName LEFT OUTER JOIN File ON (FileName.FileNameId=File.FileNameId)
157 WHERE File.FileNameId IS NULL;
158 DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t1);
161 SELECT('eliminate_orphaned_fileset_records()');
162 DELETE FROM FileSet WHERE FileSetId NOT IN (SELECT DISTINCT FileSetId FROM Job);
164 SELECT('eliminate_orphaned_client_records()');
165 DELETE FROM Client WHERE ClientId NOT IN (SELECT DISTINCT ClientId FROM Job);
168 SELECT('Now you should commit,');
169 SELECT('but check that the amount of deleted or updated data is sane...');
170 SELECT('If you''re sure, type ''COMMIT;''');
171 SELECT('THIS SCRIPT IS STILL BETA !');