KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > roller > business > utils > ConsistencyCheck


1
2 package org.roller.business.utils;
3
4 import java.io.FileInputStream JavaDoc;
5 import java.sql.*;
6 import java.util.*;
7
8 /**
9  * Roller database consistency checker.<br />
10  * Don't run this unless you know what you are doing!</br >
11  *
12  * <p>Configuration:<br />
13  *
14  * Program looks in current directory for db.properties file with database
15  * connection properties driverClassName and connectionUrl.
16  *
17  * Program expects JDBC driver jar to be on classpath.</p>
18  *
19  * <p>Usage:<br />
20  *
21  * java -cp ./WEB-INF/lib/rollerbeans.jar org.roller.business.utils.ConsistencyCheck<br />
22  *
23  * <br />Options:<br />
24  * -v Verbose<br />
25  * -d Delete orphans</p>
26  */

27 public class ConsistencyCheck
28 {
29     /**
30      * Consistency checker, find and optionally delete orphans.
31      */

32     public static void main(String JavaDoc[] args) throws Exception JavaDoc
33     {
34         Properties props = new Properties();
35         props.load(new FileInputStream JavaDoc("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     /**
56      * Create connection based on properties:<br/>
57      * - driverClassName<br/>
58      * - connectionUrl<br/>
59      * - userName<br/>
60      * - password<br/>
61      */

62     public static Connection createConnection(Properties props, String JavaDoc prefix)
63         throws Exception JavaDoc
64     {
65         Connection con = null;
66         if (prefix == null)
67         {
68             prefix = "";
69         }
70         String JavaDoc driverClassName = props.getProperty(prefix+"driverClassName");
71         String JavaDoc connectionUrl = props.getProperty(prefix+"connectionUrl");
72         String JavaDoc userName = props.getProperty(prefix+"userName");
73         String JavaDoc 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     /** Find and optionally delete all safely deletable orphans. */
88     public static void findAndDeleteOrphans(Connection con, boolean delete, boolean verbose)
89         throws SQLException
90     {
91         // websites with bad user?
92
findOrphans(con, "website", "userid", "rolleruser", delete, verbose);
93         
94         // userroles with bad user?
95
findOrphans(con, "userrole", "userid", "rolleruser", delete, verbose);
96         
97         // folders with bad website?
98
findOrphans(con, "folder", "websiteid", "website", delete, verbose);
99         
100         // bookmarks with bad folder?
101
findOrphans(con, "bookmark", "folderid", "folder", delete, verbose);
102         
103         // weblogcategories with bad website?
104
findOrphans(con, "weblogcategory", "websiteid", "website", delete, verbose);
105         
106         // weblogcategoryassocs with bad category?
107
findOrphans(con, "weblogcategoryassoc", "categoryid", "weblogcategory", delete, verbose);
108         
109         // weblog entries with bad website?
110
findOrphans(con, "weblogentry", "websiteid", "website", delete, verbose);
111                 
112         // comments with bad weblogentry?
113
findOrphans(con, "comment", "entryid", "weblogentry", delete, verbose);
114         
115         // Referers with bad website?
116
findOrphans(con, "referer", "websiteid", "website", delete, verbose);
117         
118         // Referers with bad website?
119
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     /**
130      * @param con
131      * @param delete
132      */

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         // loop through all websites
143
Statement websitesStatement = con.createStatement();
144         ResultSet websitesResultSet =
145             websitesStatement.executeQuery("select id from website");
146         while (websitesResultSet.next())
147         {
148             String JavaDoc websiteId = websitesResultSet.getString(1);
149             //debug("Website "+websiteId);
150

151             // find root folder(s)
152
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                 // too many roots, need to figure out which are bogus
163
Iterator rootIter = rootIds.iterator();
164                 while (rootIter.hasNext())
165                 {
166                     String JavaDoc rootId = (String JavaDoc)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 JavaDoc entryid = (String JavaDoc) 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 JavaDoc websiteid = websiteRs.getString(0);
200             
201             String JavaDoc 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 JavaDoc websiteid = (String JavaDoc) websiteIter.next();
215             String JavaDoc 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 JavaDoc websiteid = (String JavaDoc) websiteIter.next();
226             String JavaDoc 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 JavaDoc getRootCategoryId(Connection con, String JavaDoc websiteid)
234         throws SQLException
235     {
236         Statement st = con.createStatement();
237         String JavaDoc 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         //System.out.println(query);
242
ResultSet rs = st.executeQuery(query);
243         rs.next();
244         return rs.getString(1);
245     }
246     
247     /** Find orphans, records in a manytable that refer to a onetable that
248      * no longer exists.
249      * @param con Database connection to be used.
250      * @param manytable Name of the manytable.
251      * @param fkname Name of the foreign key field in the manytable.
252      * @param onetable Name of the onetable.
253      * @param delete True if orphans in manytable are to be deleted.
254      * @return List of orphans found (will be empty if delete is true.
255      * @throws SQLException
256      */

257     public static List findOrphans(
258         Connection con, String JavaDoc manytable, String JavaDoc fkname, String JavaDoc 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 JavaDoc id = rsall.getString("id");
269             String JavaDoc 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 JavaDoc id = (String JavaDoc) 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 JavaDoc id = (String JavaDoc) 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 JavaDoc msg)
312     {
313         System.out.println("DEBUG: "+msg);
314     }
315 }
316
Popular Tags