|
1
2 package org.roller.business.utils;
3
4 import java.io.FileInputStream  ;
5 import java.sql.*;
6 import java.util.*;
7
8
27 public class ConsistencyCheck
28 {
29
32 public static void main(String [] args) throws Exception
33 {
34 Properties props = new Properties();
35 props.load(new FileInputStream ("rollerdb.properties"));
36 Connection con = createConnection(props,"");
37
38 boolean delete = false;
39 boolean verbose = false;
40 if (args.length > 0)
41 {
42 if ("-purge".equals(args[0]))
43 {
44 delete = true;
45 }
46 else if ("-v".equals(args[0]))
47 {
48 verbose = true;
49 }
50 }
51
52 findAndDeleteOrphans(con, delete, verbose);
53 }
54
55
62 public static Connection createConnection(Properties props, String prefix)
63 throws Exception
64 {
65 Connection con = null;
66 if (prefix == null)
67 {
68 prefix = "";
69 }
70 String driverClassName = props.getProperty(prefix+"driverClassName");
71 String connectionUrl = props.getProperty(prefix+"connectionUrl");
72 String userName = props.getProperty(prefix+"userName");
73 String password = props.getProperty(prefix+"password");
74
75 Class.forName(driverClassName);
76 if (userName != null && password != null)
77 {
78 con = DriverManager.getConnection(connectionUrl, userName, password);
79 }
80 else
81 {
82 con = DriverManager.getConnection(connectionUrl);
83 }
84 return con;
85 }
86
87
88 public static void findAndDeleteOrphans(Connection con, boolean delete, boolean verbose)
89 throws SQLException
90 {
91 findOrphans(con, "website", "userid", "rolleruser", delete, verbose);
93
94 findOrphans(con, "userrole", "userid", "rolleruser", delete, verbose);
96
97 findOrphans(con, "folder", "websiteid", "website", delete, verbose);
99
100 findOrphans(con, "bookmark", "folderid", "folder", delete, verbose);
102
103 findOrphans(con, "weblogcategory", "websiteid", "website", delete, verbose);
105
106 findOrphans(con, "weblogcategoryassoc", "categoryid", "weblogcategory", delete, verbose);
108
109 findOrphans(con, "weblogentry", "websiteid", "website", delete, verbose);
111
112 findOrphans(con, "comment", "entryid", "weblogentry", delete, verbose);
114
115 findOrphans(con, "referer", "websiteid", "website", delete, verbose);
117
118 findOrphans(con, "referer", "entryid", "weblogentry", delete, verbose);
120
121 if (delete)
122 {
123 correctWeblogEntries(con);
124 correctWebsites(con);
125 correctFolderTrees(con, delete);
126 }
127 }
128
129
133 private static void correctFolderTrees(Connection con, boolean delete) throws SQLException
134 {
135 PreparedStatement rootStatement = con.prepareStatement(
136 "select a.id from folder as f, folderassoc as a where "+
137 "f.websiteid=? and f.id=a.folderid and "+
138 "a.relation='PARENT' and a.ancestorid is null");
139 PreparedStatement childrenStatement = con.prepareStatement(
140 "select id from folderassoc where ancestorid=?");
141
142 Statement websitesStatement = con.createStatement();
144 ResultSet websitesResultSet =
145 websitesStatement.executeQuery("select id from website");
146 while (websitesResultSet.next())
147 {
148 String websiteId = websitesResultSet.getString(1);
149
151 List rootIds = new LinkedList();
153 rootStatement.clearParameters();
154 rootStatement.setString(1, websiteId);
155 ResultSet rootResultSet = rootStatement.executeQuery();
156 while (rootResultSet.next())
157 {
158 rootIds.add(rootResultSet.getString(1));
159 }
160 if (rootIds.size() > 1)
161 {
162 Iterator rootIter = rootIds.iterator();
164 while (rootIter.hasNext())
165 {
166 String rootId = (String )rootIter.next();
167 childrenStatement.clearParameters();
168 childrenStatement.setString(1, rootId);
169 ResultSet childrenResultSet = childrenStatement.executeQuery();
170 List childIds = new LinkedList();
171 while (childrenResultSet.next())
172 {
173 childIds.add(childrenResultSet.getString(1));
174 }
175 if (childIds.size() == 0)
176 {
177 debug("Folder "+rootId+" in website "+websiteId+"is a bogus root folder!");
178 }
179 }
180 }
181 else if (rootIds.size() == 0)
182 {
183 debug("Website "+websiteId+" has no root folder!");
184 }
185 }
186 }
187
188 private static void correctWeblogEntries(Connection con) throws SQLException
189 {
190 List entries = findOrphans(con, "weblogentry", "categoryid", "weblogcategory", false, false);
191 Iterator entryIter = entries.iterator();
192 while (entryIter.hasNext())
193 {
194 String entryid = (String ) entryIter.next();
195 Statement websiteSt = con.createStatement();
196 ResultSet websiteRs = websiteSt.executeQuery(
197 "select websiteid from weblogentry where id="+entryid);
198 websiteRs.first();
199 String websiteid = websiteRs.getString(0);
200
201 String rootid = getRootCategoryId(con, websiteid);
202 Statement st = con.createStatement();
203 st.executeUpdate("update weblogentry set categoryid='"+rootid+"' "
204 +" where id='"+entryid+"'");
205 }
206 }
207
208 public static void correctWebsites(Connection con) throws SQLException
209 {
210 List websites = findOrphans(con, "website", "defaultcatid", "weblogcategory", false, false);
211 Iterator websiteIter = websites.iterator();
212 while (websiteIter.hasNext())
213 {
214 String websiteid = (String ) websiteIter.next();
215 String rootid = getRootCategoryId(con, websiteid);
216 Statement st = con.createStatement();
217 st.executeUpdate("update website set defaultcatid='"+rootid+"' "
218 +" where id='"+websiteid+"'");
219 }
220
221 websites = findOrphans(con, "website", "bloggercatid", "weblogcategory", false, false);
222 websiteIter = websites.iterator();
223 while (websiteIter.hasNext())
224 {
225 String websiteid = (String ) websiteIter.next();
226 String rootid = getRootCategoryId(con, websiteid);
227 Statement st = con.createStatement();
228 st.executeUpdate("update website set bloggercatid='"+rootid+"' "
229 +"where id='"+websiteid+"'");
230 }
231 }
232
233 public static String getRootCategoryId(Connection con, String websiteid)
234 throws SQLException
235 {
236 Statement st = con.createStatement();
237 String query =
238 "select c.id from weblogcategory as c, weblogcategoryassoc as a "
239 +"where a.categoryid=c.id and a.ancestorid is null "
240 +"and c.websiteid ='"+websiteid+"'";
241 ResultSet rs = st.executeQuery(query);
243 rs.next();
244 return rs.getString(1);
245 }
246
247
257 public static List findOrphans(
258 Connection con, String manytable, String fkname, String onetable, boolean delete, boolean verbose)
259 throws SQLException
260 {
261 List orphans = new LinkedList();
262
263 Statement stall = con.createStatement();
264 ResultSet rsall = stall.executeQuery(
265 "select id,"+fkname+" as fk from "+manytable);
266 while (rsall.next())
267 {
268 String id = rsall.getString("id");
269 String fk = rsall.getString("fk");
270 if (fk != null)
271 {
272 Statement stone = con.createStatement();
273 ResultSet rsone = stone.executeQuery(
274 "select id from "+onetable+" where id='"+fk+"' limit 1");
275 if (!rsone.next())
276 {
277 orphans.add(id);
278 System.out.println(" Found orphan in "+manytable+" id="+id);
279 }
280 }
281 }
282
283 if (!delete)
284 {
285 debug("Orphans found in "+manytable+" = "+orphans.size());
286 if (verbose)
287 {
288 Iterator iter = orphans.iterator();
289 while (iter.hasNext())
290 {
291 String id = (String ) iter.next();
292 debug(" "+manytable+" id="+id);
293 }
294 }
295 }
296 else
297 {
298 debug("Deleting orphans found in "+manytable+" count = "+orphans.size());
299 Iterator iter = orphans.iterator();
300 while (iter.hasNext())
301 {
302 String id = (String ) iter.next();
303 Statement stdel = con.createStatement();
304 stdel.executeUpdate("delete from "+manytable+" where id='"+id+"'");
305 }
306 orphans = new LinkedList();
307 }
308 return orphans;
309 }
310
311 private static void debug(String msg)
312 {
313 System.out.println("DEBUG: "+msg);
314 }
315 }
316 |
Popular Tags |
Java API By Example, From Geeks To Geeks.