syncupgrade098xto1000.java

这个weblogging 设计得比较精巧

package org.roller.business.utils;

import java.sql.*;
import java.util.*;
import java.io.*;

/**
 * Synchronize a source 0.9.8.X database with a destination 1.0.0.0 database.
 */
public class SyncUpgrade098Xto1000 
{
    private Map rootCategoryIds = new Hashtable(); // keyed by website id
    private static boolean debug = true;
    
    public void syncUpgrade(Connection srccon, Connection destcon) 
    throws Exception
    {    
        rootCategoryIds = buildRootCategoryMap(destcon);        
        
        syncRolleruserTable(srccon,destcon);
        syncUserroleTable(srccon,destcon);
        syncWebsiteTable(srccon,destcon);
        syncWebpageTable(srccon,destcon);
        syncWeblogcategoryTable(srccon,destcon);        
        syncFolderTable(srccon,destcon);
        syncBookmarkTable(srccon,destcon);
        syncWeblogentryTable(srccon,destcon);
        syncCommentTable(srccon,destcon);
        syncRefererTable(srccon,destcon);
        
        ConsistencyCheck.findAndDeleteOrphans(destcon, true, debug);
    }
    public Map buildRootCategoryMap(Connection destcon) throws Exception 
    {        
        Hashtable map = new Hashtable();
        Statement destStmt = destcon.createStatement();
        ResultSet destSet = destStmt.executeQuery(
           "select c.websiteid,c.id from "
           +"weblogcategory as c, weblogcategoryassoc as a "
           +"where c.id=a.categoryid and a.ancestorid is null");
        while (destSet.next()) 
        {
            String websiteid = destSet.getString(1);
            String categoryid = destSet.getString(2);
            map.put(websiteid, categoryid);
        }
        return map;
    }
    private void info(String s) 
    {
        System.out.println(s);
    }
    private void debug(String s) 
    {
        if (debug) System.out.println(s);
    }
    private void purgeDeleted(Connection srccon, Connection destcon, String tableName) 
    throws Exception
    {
        PreparedStatement destRows = destcon.prepareStatement(
            "select id from "+tableName);
        PreparedStatement deleteRow = destcon.prepareStatement(
            "delete from "+tableName+" where id=?");
        PreparedStatement srcExists = srccon.prepareStatement(
            "select id from "+tableName+" where id=?");
        ResultSet destSet = destRows.executeQuery();
        while (destSet.next())
        {
            String id = destSet.getString(1);
            srcExists.clearParameters();
            srcExists.setString(1, id);
            ResultSet existsSet = srcExists.executeQuery();
            if (!existsSet.next() && !id.endsWith("R")) // kludge alert
            {
                deleteRow.clearParameters();
                deleteRow.setString(1, id);
                deleteRow.executeUpdate();
                info("Deleting from "+tableName+" id="+id);
            }
        }        
    }
    private void purgeAssocs(Connection destcon, String assocTable, String mainTable, String fkeyName) 
    throws Exception
    {
        info("--- purgeAssocs --- "+assocTable);
        PreparedStatement assocRows = destcon.prepareStatement(
            "select id,"+fkeyName+",ancestorid from "+assocTable);
        PreparedStatement mainExists = destcon.prepareStatement(
            "select id from "+mainTable+" where id=?");
        PreparedStatement deleteMain = destcon.prepareStatement(
            "delete from "+assocTable+" where "+fkeyName+"=?");
        PreparedStatement ancestorExists = destcon.prepareStatement(
            "select id from "+mainTable+" where id=?");
        PreparedStatement deleteAncestor = destcon.prepareStatement(
            "delete from "+assocTable+" where ancestorid=?");
        ResultSet assocSet = assocRows.executeQuery();
        while (assocSet.next())
        {
            String id = assocSet.getString(1);
            String fkey = assocSet.getString(2);
            String akey = assocSet.getString(3);
            mainExists.clearParameters();
            mainExists.setString(1, fkey);
            ResultSet existsSet = mainExists.executeQuery();
            if (!existsSet.next())
            {
                deleteMain.clearParameters();
                deleteMain.setString(1, fkey);
                deleteMain.executeUpdate();
                info("Deleting from "+assocTable+" where "+fkeyName+"="+id);
            }
            ancestorExists.clearParameters();
            ancestorExists.setString(1, akey);
            ResultSet ancestorSet = ancestorExists.executeQuery();
            if (!ancestorSet.next())
            {
                deleteAncestor.clearParameters();
                deleteAncestor.setString(1, akey);
                deleteAncestor.executeUpdate();
                info("Deleting from "+assocTable+" where ancestorid="+id);
            }
        }            
    }
    private void syncRolleruserTable(Connection srccon, Connection destcon) 
    throws Exception
    {
        info("--- syncRolleruserTable ---");        
        Set existing = new  TreeSet();         
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from rolleruser where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into rolleruser "
            +"(id,username,passphrase,fullname,emailaddress,datecreated) "
            +"values (?,?,?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update rolleruser set id=?, username=?, passphrase=?, "
            +"fullname=?, emailaddress=?, datecreated=? where id=?");

        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery(
           "select id,username,passphrase,fullname,emailaddress,datecreated "
           +"from rolleruser");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(1);
            existing.add(id);
            destExistsStmt.clearParameters();
            destExistsStmt.setString(1,id);
            ResultSet destSet = destExistsStmt.executeQuery();
            if (!destSet.first())
            {
                debug("Inserting rolleruser id="+id);
                destInsert.clearParameters();
                destInsert.setString(1, srcSet.getString(1));
                destInsert.setString(2, srcSet.getString(2));
                destInsert.setString(3, srcSet.getString(3));
                destInsert.setString(4, srcSet.getString(4));
                destInsert.setString(5, srcSet.getString(5));
                destInsert.setDate(  6, srcSet.getDate(6));
                destInsert.executeUpdate();
            }
            else
            {
                debug("Updating rolleruser id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(1, srcSet.getString(1));
                destUpdate.setString(2, srcSet.getString(2));
                destUpdate.setString(3, srcSet.getString(3));
                destUpdate.setString(4, srcSet.getString(4));
                destUpdate.setString(5, srcSet.getString(5));
                destUpdate.setDate(  6, srcSet.getDate(6));
                destUpdate.setString(7, srcSet.getString(1));
                destUpdate.executeUpdate();
            }                
        } 
        purgeDeleted(srccon,destcon,"rolleruser");
    }
    private void syncUserroleTable(Connection srccon, Connection destcon) 
    throws Exception
    {
        info("--- syncUserroleTable ---");
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from userrole where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into userrole (id,rolename,username,userid) "
            +"values (?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update userrole set id=?, rolename=?, username=?, userid=? "
            +"where id=?");
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from rolleruser where id=?");        

        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery(
           "select id,rolename,username,userid from userrole");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(1);
            String userid = srcSet.getString(4);
            
            destExistsStmt.clearParameters();
            destExistsStmt.setString(1, id);
            ResultSet destSet = destExistsStmt.executeQuery();
            
            parentExistsStmt.clearParameters();
            parentExistsStmt.setString(1, userid);
            ResultSet parentSet = parentExistsStmt.executeQuery();
            
            boolean parentExists = parentSet.first();
            if (!destSet.first() && parentExists)
            {
                debug("Inserting userrole id="+id);
                destInsert.clearParameters();
                destInsert.setString(1, srcSet.getString(1));
                destInsert.setString(2, srcSet.getString(2));
                destInsert.setString(3, srcSet.getString(3));
                destInsert.setString(4, srcSet.getString(4));
                destInsert.executeUpdate();
            }
            else if (parentExists)
            {
                debug("Updating userrole id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(1, srcSet.getString(1));
                destUpdate.setString(2, srcSet.getString(2));
                destUpdate.setString(3, srcSet.getString(3));
                destUpdate.setString(4, srcSet.getString(4));
                destUpdate.setString(5, srcSet.getString(1));
                destUpdate.executeUpdate();
            }                
            else 
            {
                info("Not copying userrole id="+id);
            }
        }     
        purgeDeleted(srccon,destcon,"userrole");
    }
    private void syncWebsiteTable(Connection srccon, Connection destcon) 
    throws Exception
    {
        info("--- syncWebsiteTable ---");
        
        int id_num=1;
        int name_num=2;
        int description_num=3;
        int userid_num=4;
        int defaultpageid_num=5; 
        int weblogdayid_num=6;
        int ignorewords_num=7;    
        int enablebloggerapi_num=8; 
        int editorpage_num=9;    
        int bloggercatid_num=10;   
        int allowcomments_num=11; 
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from website where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into website (id,name,description,userid,defaultpageid,"
            +"weblogdayid,ignorewords,enablebloggerapi,editorpage,"
            +"bloggercatid,allowcomments,defaultcatid) values (?,?,?,?,?,?,?,?,?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
        "update website set id=?,name=?,description=?,userid=?,defaultpageid=?,"
            +"weblogdayid=?,ignorewords=?,enablebloggerapi=?,editorpage=?,"
            +"bloggercatid=?,allowcomments=? where id=?");
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from rolleruser where id=?");
        
        PreparedStatement insertRootCategory = destcon.prepareStatement(
            "insert into weblogcategory (id,name,description,websiteid,image) "+
            "values (?,'root','root',?,NULL)");                        
        PreparedStatement insertRootCategoryAssoc = destcon.prepareStatement(
           "insert into weblogcategoryassoc (id,categoryid,ancestorid,relation)"
            +" values (?,?,NULL,'PARENT')");                        
        
        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery(
            "select id,name,description,userid,defaultpageid,weblogdayid,"
           +"ignorewords,enablebloggerapi,editorpage,bloggercatid,allowcomments"
           +" from website");        
        while (srcSet.next()) 
        {
            String id = srcSet.getString(id_num);
            String userid = srcSet.getString(userid_num);
            
            destExistsStmt.clearParameters();
            destExistsStmt.setString(id_num, id);            
            ResultSet destSet = destExistsStmt.executeQuery();
            
            parentExistsStmt.clearParameters();
            parentExistsStmt.setString(id_num, userid);
            ResultSet parentSet = parentExistsStmt.executeQuery();
            boolean parentExists = parentSet.first();
            
            if (!destSet.first() && parentExists)
            {
                debug("Inserting website id="+id);
                destInsert.clearParameters();
                destInsert.setString(id_num, srcSet.getString(id_num));
                destInsert.setString(name_num, srcSet.getString(name_num));
                destInsert.setString(description_num, srcSet.getString(description_num));
                destInsert.setString(userid_num, srcSet.getString(userid_num));
                destInsert.setString(defaultpageid_num, srcSet.getString(defaultpageid_num));
                destInsert.setString(weblogdayid_num, srcSet.getString(weblogdayid_num));
                destInsert.setString(ignorewords_num, srcSet.getString(ignorewords_num));
                destInsert.setBoolean(enablebloggerapi_num, srcSet.getBoolean(enablebloggerapi_num));
                destInsert.setString(editorpage_num, srcSet.getString(editorpage_num));
                destInsert.setString(bloggercatid_num, srcSet.getString(bloggercatid_num));
                destInsert.setBoolean(allowcomments_num, srcSet.getBoolean(allowcomments_num));
                destInsert.setString(12, id+"R"); // default category
                destInsert.executeUpdate();
                
                // 098 had no root category per website, so create one
                insertRootCategory.clearParameters();
                insertRootCategory.setString(1, id+"R");
                insertRootCategory.setString(2, id);
                insertRootCategory.executeUpdate();