ConsistencyCheck


1   /*
2   * Licensed to the Apache Software Foundation (ASF) under one or more
3   *  contributor license agreements.  The ASF licenses this file to You
4   * under the Apache License, Version 2.0 (the "License"); you may not
5   * use this file except in compliance with the License.
6   * You may obtain a copy of the License at
7   *
8   *     http://www.apache.org/licenses/LICENSE-2.0
9   *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.  For additional information regarding
15  * copyright in this work, please see the NOTICE file in the top level
16  * directory of this distribution.
17  */
18  
19  package org.apache.roller.business.utils;
20  
21  import java.io.FileInputStream  ;
22  import java.sql.*;
23  import java.util.*;
24  
25  /**
26   * Roller database consistency checker.<br />
27   * Don't run this unless you know what you are doing!</br >
28   * 
29   * <p>Configuration:<br />
30   * 
31   * Program looks in current directory for db.properties file with database
32   * connection properties driverClassName and connectionUrl. 
33   * 
34   * Program expects JDBC driver jar to be on classpath.</p>
35   * 
36   * <p>Usage:<br />
37   * 
38   * java -cp ./WEB-INF/lib/rollerbeans.jar org.apache.roller.business.utils.ConsistencyCheck<br />
39   * 
40   * <br />Options:<br />
41   * -v Verbose<br />
42   * -purge Delete orphans</p>
43   */
44  public class ConsistencyCheck 
45  {
46      /** 
47       * Consistency checker, find and optionally delete orphans. 
48       */
49      public static void main(String  [] args) throws Exception  
50      {
51          Properties props = new Properties();
52          props.load(new FileInputStream  ("rollerdb.properties"));
53          Connection con = createConnection(props,"");
54          
55          boolean delete = false;
56          boolean verbose = false;
57          if (args.length > 0) 
58          {
59              if ("-purge".equals(args[0])) 
60              {
61                  delete = true;
62              }
63              else if ("-v".equals(args[0]))
64              {
65                  verbose = true;
66              }
67          }        
68          
69          findAndDeleteOrphans(con, delete, verbose);
70      }
71      
72      /** 
73       * Create connection based on properties:<br/>
74       * - driverClassName<br/>
75       * - connectionUrl<br/>
76       * - userName<br/>
77       * - password<br/>
78       */
79      public static Connection createConnection(Properties props, String   prefix) 
80          throws Exception  
81      {
82          Connection con = null;
83          if (prefix == null) 
84          {
85              prefix = "";
86          }
87          String   driverClassName = props.getProperty(prefix+"driverClassName");
88          String   connectionUrl = props.getProperty(prefix+"connectionUrl");
89          String   userName = props.getProperty(prefix+"userName");
90          String   password = props.getProperty(prefix+"password");
91          
92          Class.forName(driverClassName);
93          if (userName != null && password != null)
94          {
95             con = DriverManager.getConnection(connectionUrl, userName, password);
96          }
97          else
98          {
99             con = DriverManager.getConnection(connectionUrl);
100         }
101         return con;
102     }
103     
104     /** Find and optionally delete all safely deletable orphans. */
105     public static void findAndDeleteOrphans(Connection con, boolean delete, boolean verbose) 
106         throws SQLException
107     {
108         // websites with bad user?
109         findOrphans(con, "website", "userid", "rolleruser", delete, verbose);
110         
111         // userroles with bad user?
112         findOrphans(con, "userrole", "userid", "rolleruser", delete, verbose);
113         
114         // folders with bad website?
115         findOrphans(con, "folder", "websiteid", "website", delete, verbose);
116         
117         // bookmarks with bad folder?
118         findOrphans(con, "bookmark", "folderid", "folder", delete, verbose);
119         
120         // weblogcategories with bad website?
121         findOrphans(con, "weblogcategory", "websiteid", "website", delete, verbose);
122         
123         // weblogcategoryassocs with bad category?
124         findOrphans(con, "weblogcategoryassoc", "categoryid", "weblogcategory", delete, verbose);
125         
126         // weblog entries with bad website?
127         findOrphans(con, "weblogentry", "websiteid", "website", delete, verbose);
128                 
129         // comments with bad weblogentry?
130         findOrphans(con, "comment", "entryid", "weblogentry", delete, verbose);
131         
132         // Referers with bad website?
133         findOrphans(con, "referer", "websiteid", "website", delete, verbose);
134         
135         // Referers with bad website?
136         findOrphans(con, "referer", "entryid", "weblogentry", delete, verbose);              
137         
138         if (delete)
139         {
140             correctWeblogEntries(con);
141             correctWebsites(con);
142             correctFolderTrees(con, delete);
143         }
144     }
145     
146     /**
147      * @param con
148      * @param delete
149      */
150     private static void correctFolderTrees(Connection con, boolean delete) throws SQLException
151     {
152         PreparedStatement rootStatement = con.prepareStatement(
153             "select a.id from folder as f, folderassoc as a where "+
154             "f.websiteid=? and f.id=a.folderid and "+
155             "a.relation='PARENT' and a.ancestorid is null");
156         PreparedStatement childrenStatement = con.prepareStatement(
157             "select id from folderassoc where ancestorid=?");
158 
159         // loop through all websites
160         Statement websitesStatement = con.createStatement();
161         ResultSet websitesResultSet = 
162             websitesStatement.executeQuery("select id from website");
163         while (websitesResultSet.next()) 
164         {
165             String   websiteId = websitesResultSet.getString(1);
166             //debug("Website "+websiteId);
167             
168             // find root folder(s)
169             List rootIds = new LinkedList();
170             rootStatement.clearParameters();
171             rootStatement.setString(1, websiteId);
172             ResultSet rootResultSet = rootStatement.executeQuery();
173             while (rootResultSet.next())
174             {
175                 rootIds.add(rootResultSet.getString(1));
176             }
177             if (rootIds.size() > 1) 
178             {
179                 // too many roots, need to figure out which are bogus
180                 Iterator rootIter = rootIds.iterator();
181                 while (rootIter.hasNext())
182                 {
183                     String   rootId = (String  )rootIter.next();
184                     childrenStatement.clearParameters();
185                     childrenStatement.setString(1, rootId);
186                     ResultSet childrenResultSet = childrenStatement.executeQuery();
187                     List childIds = new LinkedList();
188                     while (childrenResultSet.next()) 
189                     {
190                         childIds.add(childrenResultSet.getString(1));
191                     }
192                     if (childIds.size() == 0)
193                     {
194                         debug("Folder "+rootId+" in website "+websiteId+"is a bogus root folder!");
195                     }
196                 }
197             }
198             else if (rootIds.size() == 0)
199             {
200                 debug("Website "+websiteId+" has no root folder!");
201             }
202         }
203     }
204 
205     private static void correctWeblogEntries(Connection con) throws SQLException
206     {
207         List entries = findOrphans(con, "weblogentry", "categoryid", "weblogcategory", false, false);
208         Iterator entryIter = entries.iterator();
209         while (entryIter.hasNext())
210         {
211             String   entryid = (String  ) entryIter.next();
212             Statement websiteSt = con.createStatement();
213             ResultSet websiteRs = websiteSt.executeQuery(
214                 "select websiteid from weblogentry where id="+entryid);
215             websiteRs.first();
216             String   websiteid = websiteRs.getString(0);
217             
218             String   rootid = getRootCategoryId(con, websiteid);
219             Statement st = con.createStatement();
220             st.executeUpdate("update weblogentry set categoryid='"+rootid+"' "
221                            +" where id='"+entryid+"'");
222         }   
223     }
224     
225     public static void correctWebsites(Connection con) throws SQLException
226     {
227         List websites = findOrphans(con, "website", "defaultcatid", "weblogcategory", false, false);
228         Iterator websiteIter = websites.iterator();
229         while (websiteIter.hasNext())
230         {
231             String   websiteid = (String  ) websiteIter.next();
232             String   rootid = getRootCategoryId(con, websiteid);
233             Statement st = con.createStatement();
234             st.executeUpdate("update website set defaultcatid='"+rootid+"' "
235                     +" where id='"+websiteid+"'");
236         }
237     
238         websites = findOrphans(con, "website", "bloggercatid", "weblogcategory", false, false);
239         websiteIter = websites.iterator();
240         while (websiteIter.hasNext())
241         {
242             String   websiteid = (String  ) websiteIter.next();
243             String   rootid = getRootCategoryId(con, websiteid);
244             Statement st = con.createStatement();
245             st.executeUpdate("update website set bloggercatid='"+rootid+"' "
246                             +"where id='"+websiteid+"'");
247         }
248     }
249     
250     public static String   getRootCategoryId(Connection con, String   websiteid)
251         throws SQLException
252     {
253         Statement st = con.createStatement();
254         String   query = 
255             "select c.id from weblogcategory as c, weblogcategoryassoc as a "
256             +"where a.categoryid=c.id and a.ancestorid is null "
257             +"and c.websiteid ='"+websiteid+"'";
258         //System.out.println(query);
259         ResultSet rs = st.executeQuery(query);        
260         rs.next();
261         return rs.getString(1);
262     }
263     
264     /** Find orphans, records in a manytable that refer to a onetable that 
265      * no longer exists.
266      * @param con       Database connection to be used.
267      * @param manytable Name of the manytable.
268      * @param fkname    Name of the foreign key field in the manytable.
269      * @param onetable  Name of the onetable.
270      * @param delete    True if orphans in manytable are to be deleted.
271      * @return          List of orphans found (will be empty if delete is true.
272      * @throws SQLException
273      */
274     public static List findOrphans(
275         Connection con, String   manytable, String   fkname, String   onetable, boolean delete, boolean verbose) 
276         throws SQLException
277     {
278         List orphans = new LinkedList();
279         
280         Statement stall = con.createStatement();
281         ResultSet rsall = stall.executeQuery(
282             "select id,"+fkname+" as fk from "+manytable);
283         while (rsall.next()) 
284         {
285             String   id = rsall.getString("id");
286             String   fk = rsall.getString("fk");
287             if (fk != null)
288             {
289                 Statement stone = con.createStatement();
290                 ResultSet rsone = stone.executeQuery(
291                     "select id from "+onetable+" where id='"+fk+"' limit 1");
292                 if (!rsone.next()) 
293                 {
294                     orphans.add(id);  
295                     System.out.println("   Found orphan in "+manytable+" id="+id); 
296                 }                
297             }
298         } 
299         
300         if (!delete)
301         {
302             debug("Orphans found in "+manytable+" = "+orphans.size());
303             if (verbose)
304             {
305                 Iterator iter = orphans.iterator();
306                 while (iter.hasNext())
307                 {
308                     String   id = (String  ) iter.next();
309                     debug("   "+manytable+" id="+id);
310                 }
311             }
312         }
313         else
314         {
315             debug("Deleting orphans found in "+manytable+" count = "+orphans.size());            
316             Iterator iter = orphans.iterator();
317             while (iter.hasNext())
318             {
319                 String   id = (String  ) iter.next();
320                 Statement stdel = con.createStatement();
321                 stdel.executeUpdate("delete from "+manytable+" where id='"+id+"'");
322             }
323             orphans = new LinkedList();
324         }
325         return orphans;
326     }
327     
328     private static void debug(String   msg) 
329     {
330         System.out.println("DEBUG: "+msg);   
331     }
332 }
333
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags