001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one or more
003: * contributor license agreements. The ASF licenses this file to You
004: * under the Apache License, Version 2.0 (the "License"); you may not
005: * use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License. For additional information regarding
015: * copyright in this work, please see the NOTICE file in the top level
016: * directory of this distribution.
017: */
018:
019: package org.apache.roller.business.utils;
020:
021: import java.io.FileInputStream;
022: import java.sql.*;
023: import java.util.*;
024:
025: /**
026: * Roller database consistency checker.<br />
027: * Don't run this unless you know what you are doing!</br >
028: *
029: * <p>Configuration:<br />
030: *
031: * Program looks in current directory for db.properties file with database
032: * connection properties driverClassName and connectionUrl.
033: *
034: * Program expects JDBC driver jar to be on classpath.</p>
035: *
036: * <p>Usage:<br />
037: *
038: * java -cp ./WEB-INF/lib/rollerbeans.jar org.apache.roller.business.utils.ConsistencyCheck<br />
039: *
040: * <br />Options:<br />
041: * -v Verbose<br />
042: * -purge Delete orphans</p>
043: */
044: public class ConsistencyCheck {
045: /**
046: * Consistency checker, find and optionally delete orphans.
047: */
048: public static void main(String[] args) throws Exception {
049: Properties props = new Properties();
050: props.load(new FileInputStream("rollerdb.properties"));
051: Connection con = createConnection(props, "");
052:
053: boolean delete = false;
054: boolean verbose = false;
055: if (args.length > 0) {
056: if ("-purge".equals(args[0])) {
057: delete = true;
058: } else if ("-v".equals(args[0])) {
059: verbose = true;
060: }
061: }
062:
063: findAndDeleteOrphans(con, delete, verbose);
064: }
065:
066: /**
067: * Create connection based on properties:<br/>
068: * - driverClassName<br/>
069: * - connectionUrl<br/>
070: * - userName<br/>
071: * - password<br/>
072: */
073: public static Connection createConnection(Properties props,
074: String prefix) throws Exception {
075: Connection con = null;
076: if (prefix == null) {
077: prefix = "";
078: }
079: String driverClassName = props.getProperty(prefix
080: + "driverClassName");
081: String connectionUrl = props.getProperty(prefix
082: + "connectionUrl");
083: String userName = props.getProperty(prefix + "userName");
084: String password = props.getProperty(prefix + "password");
085:
086: Class.forName(driverClassName);
087: if (userName != null && password != null) {
088: con = DriverManager.getConnection(connectionUrl, userName,
089: password);
090: } else {
091: con = DriverManager.getConnection(connectionUrl);
092: }
093: return con;
094: }
095:
096: /** Find and optionally delete all safely deletable orphans. */
097: public static void findAndDeleteOrphans(Connection con,
098: boolean delete, boolean verbose) throws SQLException {
099: // websites with bad user?
100: findOrphans(con, "website", "userid", "rolleruser", delete,
101: verbose);
102:
103: // userroles with bad user?
104: findOrphans(con, "userrole", "userid", "rolleruser", delete,
105: verbose);
106:
107: // folders with bad website?
108: findOrphans(con, "folder", "websiteid", "website", delete,
109: verbose);
110:
111: // bookmarks with bad folder?
112: findOrphans(con, "bookmark", "folderid", "folder", delete,
113: verbose);
114:
115: // weblogcategories with bad website?
116: findOrphans(con, "weblogcategory", "websiteid", "website",
117: delete, verbose);
118:
119: // weblogcategoryassocs with bad category?
120: findOrphans(con, "weblogcategoryassoc", "categoryid",
121: "weblogcategory", delete, verbose);
122:
123: // weblog entries with bad website?
124: findOrphans(con, "weblogentry", "websiteid", "website", delete,
125: verbose);
126:
127: // comments with bad weblogentry?
128: findOrphans(con, "comment", "entryid", "weblogentry", delete,
129: verbose);
130:
131: // Referers with bad website?
132: findOrphans(con, "referer", "websiteid", "website", delete,
133: verbose);
134:
135: // Referers with bad website?
136: findOrphans(con, "referer", "entryid", "weblogentry", delete,
137: verbose);
138:
139: if (delete) {
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,
151: boolean delete) throws SQLException {
152: PreparedStatement rootStatement = con
153: .prepareStatement("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
157: .prepareStatement("select id from folderassoc where ancestorid=?");
158:
159: // loop through all websites
160: Statement websitesStatement = con.createStatement();
161: ResultSet websitesResultSet = websitesStatement
162: .executeQuery("select id from website");
163: while (websitesResultSet.next()) {
164: String websiteId = websitesResultSet.getString(1);
165: //debug("Website "+websiteId);
166:
167: // find root folder(s)
168: List rootIds = new LinkedList();
169: rootStatement.clearParameters();
170: rootStatement.setString(1, websiteId);
171: ResultSet rootResultSet = rootStatement.executeQuery();
172: while (rootResultSet.next()) {
173: rootIds.add(rootResultSet.getString(1));
174: }
175: if (rootIds.size() > 1) {
176: // too many roots, need to figure out which are bogus
177: Iterator rootIter = rootIds.iterator();
178: while (rootIter.hasNext()) {
179: String rootId = (String) rootIter.next();
180: childrenStatement.clearParameters();
181: childrenStatement.setString(1, rootId);
182: ResultSet childrenResultSet = childrenStatement
183: .executeQuery();
184: List childIds = new LinkedList();
185: while (childrenResultSet.next()) {
186: childIds.add(childrenResultSet.getString(1));
187: }
188: if (childIds.size() == 0) {
189: debug("Folder " + rootId + " in website "
190: + websiteId + "is a bogus root folder!");
191: }
192: }
193: } else if (rootIds.size() == 0) {
194: debug("Website " + websiteId + " has no root folder!");
195: }
196: }
197: }
198:
199: private static void correctWeblogEntries(Connection con)
200: throws SQLException {
201: List entries = findOrphans(con, "weblogentry", "categoryid",
202: "weblogcategory", false, false);
203: Iterator entryIter = entries.iterator();
204: while (entryIter.hasNext()) {
205: String entryid = (String) entryIter.next();
206: Statement websiteSt = con.createStatement();
207: ResultSet websiteRs = websiteSt
208: .executeQuery("select websiteid from weblogentry where id="
209: + entryid);
210: websiteRs.first();
211: String websiteid = websiteRs.getString(0);
212:
213: String rootid = getRootCategoryId(con, websiteid);
214: Statement st = con.createStatement();
215: st.executeUpdate("update weblogentry set categoryid='"
216: + rootid + "' " + " where id='" + entryid + "'");
217: }
218: }
219:
220: public static void correctWebsites(Connection con)
221: throws SQLException {
222: List websites = findOrphans(con, "website", "defaultcatid",
223: "weblogcategory", false, false);
224: Iterator websiteIter = websites.iterator();
225: while (websiteIter.hasNext()) {
226: String websiteid = (String) websiteIter.next();
227: String rootid = getRootCategoryId(con, websiteid);
228: Statement st = con.createStatement();
229: st.executeUpdate("update website set defaultcatid='"
230: + rootid + "' " + " where id='" + websiteid + "'");
231: }
232:
233: websites = findOrphans(con, "website", "bloggercatid",
234: "weblogcategory", false, false);
235: websiteIter = websites.iterator();
236: while (websiteIter.hasNext()) {
237: String websiteid = (String) websiteIter.next();
238: String rootid = getRootCategoryId(con, websiteid);
239: Statement st = con.createStatement();
240: st.executeUpdate("update website set bloggercatid='"
241: + rootid + "' " + "where id='" + websiteid + "'");
242: }
243: }
244:
245: public static String getRootCategoryId(Connection con,
246: String websiteid) throws SQLException {
247: Statement st = con.createStatement();
248: String query = "select c.id from weblogcategory as c, weblogcategoryassoc as a "
249: + "where a.categoryid=c.id and a.ancestorid is null "
250: + "and c.websiteid ='" + websiteid + "'";
251: //System.out.println(query);
252: ResultSet rs = st.executeQuery(query);
253: rs.next();
254: return rs.getString(1);
255: }
256:
257: /** Find orphans, records in a manytable that refer to a onetable that
258: * no longer exists.
259: * @param con Database connection to be used.
260: * @param manytable Name of the manytable.
261: * @param fkname Name of the foreign key field in the manytable.
262: * @param onetable Name of the onetable.
263: * @param delete True if orphans in manytable are to be deleted.
264: * @return List of orphans found (will be empty if delete is true.
265: * @throws SQLException
266: */
267: public static List findOrphans(Connection con, String manytable,
268: String fkname, String onetable, boolean delete,
269: boolean verbose) throws SQLException {
270: List orphans = new LinkedList();
271:
272: Statement stall = con.createStatement();
273: ResultSet rsall = stall.executeQuery("select id," + fkname
274: + " as fk from " + manytable);
275: while (rsall.next()) {
276: String id = rsall.getString("id");
277: String fk = rsall.getString("fk");
278: if (fk != null) {
279: Statement stone = con.createStatement();
280: ResultSet rsone = stone.executeQuery("select id from "
281: + onetable + " where id='" + fk + "' limit 1");
282: if (!rsone.next()) {
283: orphans.add(id);
284: System.out.println(" Found orphan in "
285: + manytable + " id=" + id);
286: }
287: }
288: }
289:
290: if (!delete) {
291: debug("Orphans found in " + manytable + " = "
292: + orphans.size());
293: if (verbose) {
294: Iterator iter = orphans.iterator();
295: while (iter.hasNext()) {
296: String id = (String) iter.next();
297: debug(" " + manytable + " id=" + id);
298: }
299: }
300: } else {
301: debug("Deleting orphans found in " + manytable
302: + " count = " + orphans.size());
303: Iterator iter = orphans.iterator();
304: while (iter.hasNext()) {
305: String id = (String) iter.next();
306: Statement stdel = con.createStatement();
307: stdel.executeUpdate("delete from " + manytable
308: + " where id='" + id + "'");
309: }
310: orphans = new LinkedList();
311: }
312: return orphans;
313: }
314:
315: private static void debug(String msg) {
316: System.out.println("DEBUG: " + msg);
317: }
318: }
|