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 |