syncupgrade098xto1000.java

这个weblogging 设计得比较精巧

                rootCategoryIds.put(id, id+"R"); // and add it to map
                debug("   Inserting root weblogcategory id="+id+"R");
                
                // Create category assoc to go with root category
                insertRootCategoryAssoc.clearParameters();
                insertRootCategoryAssoc.setString(1, id+"A");
                insertRootCategoryAssoc.setString(2, id+"R");
                insertRootCategoryAssoc.executeUpdate();
                debug("   Inserting root weblogcategoryassoc id="+id+"A");
            }
            else if (parentExists)
            {
                debug("Updating website id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(id_num, srcSet.getString(id_num));
                destUpdate.setString(name_num, srcSet.getString(name_num));
                destUpdate.setString(description_num, srcSet.getString(description_num));
                destUpdate.setString(userid_num, srcSet.getString(userid_num));
                destUpdate.setString(defaultpageid_num, srcSet.getString(defaultpageid_num));
                destUpdate.setString(weblogdayid_num, srcSet.getString(weblogdayid_num));
                destUpdate.setString(ignorewords_num, srcSet.getString(ignorewords_num));
                destUpdate.setBoolean(enablebloggerapi_num, srcSet.getBoolean(enablebloggerapi_num));
                destUpdate.setString(editorpage_num, srcSet.getString(editorpage_num));
                destUpdate.setString(bloggercatid_num, srcSet.getString(bloggercatid_num));
                destUpdate.setBoolean(allowcomments_num, srcSet.getBoolean(allowcomments_num));
                destUpdate.setString(12, srcSet.getString(id_num));
                destUpdate.executeUpdate();
            }   
            else 
            {
                info("Not copying website id="+id);
            }
        }     
        purgeDeleted(srccon,destcon,"website");
    }    
    private void syncWebpageTable(Connection srccon, Connection destcon) throws Exception
    {
        info("--- syncWebpageTable ---");
        
        String columns = "id,name,description,link,websiteid,template,updatetime";
        int id_num=1;
        int name_num=2;
        int description_num=3;
        int link_num=4;
        int websiteid_num=5;
        int template_num=6;
        int updatetime_num=7;
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from webpage where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into webpage "+"("+columns+") "+"values (?,?,?,?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update webpage set id=?,name=?,description=?,link=?,websiteid=?,"
            +"template=?,updatetime=? where id=?");
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from website where id=?");        

        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from webpage");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(id_num);
            String userid = srcSet.getString(websiteid_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 webpage 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(link_num, srcSet.getString(link_num));
                destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
                destInsert.setString(template_num, srcSet.getString(template_num));
                destInsert.setTimestamp(updatetime_num, srcSet.getTimestamp(updatetime_num));
                destInsert.executeUpdate();
            }
            else if (parentExists)
            {
                debug("Updating webpage id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(id_num, srcSet.getString(id_num));
                destUpdate.setString(name_num, srcSet.getString(name_num));
                destUpdate.setString(description_num, srcSet.getString(description_num));
                destUpdate.setString(link_num, srcSet.getString(link_num));
                destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
                destUpdate.setString(template_num, srcSet.getString(template_num));
                destUpdate.setTimestamp(updatetime_num, srcSet.getTimestamp(updatetime_num));
                destUpdate.setString(8, srcSet.getString(id_num));
                destUpdate.executeUpdate();
            }                
            else 
            {
                info("Not copying webpage id="+id);
            }
        }     
        purgeDeleted(srccon,destcon,"webpage");
    }
    private void syncWeblogcategoryTable(Connection srccon, Connection destcon) throws Exception
    {
        info("--- syncWeblogcategoryTable ---");
        
        String columns = "id,name,description,websiteid,image";
        int id_num=1;
        int name_num=2;
        int description_num=3;
        int websiteid_num=4;
        int image_num=5;
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from weblogcategory where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into weblogcategory "+"("+columns+") "+"values (?,?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update weblogcategory set id=?,name=?,description=?,websiteid=?,"
            +"image=? where id=?");
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from website where id=?");        

        PreparedStatement assocInsert = destcon.prepareStatement(
            "insert into weblogcategoryassoc "
            +"(id,categoryid,ancestorid,relation) "+"values (?,?,?,'PARENT')");

        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from weblogcategory");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(id_num);
            String websiteid = srcSet.getString(websiteid_num);
            
            destExistsStmt.clearParameters();
            destExistsStmt.setString(id_num, id);
            ResultSet destSet = destExistsStmt.executeQuery();
            
            parentExistsStmt.clearParameters();
            parentExistsStmt.setString(id_num, websiteid);
            ResultSet parentSet = parentExistsStmt.executeQuery();
            
            boolean parentExists = parentSet.first();
            if (!destSet.first() && parentExists)
            {
                debug("Inserting weblogcategory id="+id);
                destInsert.clearParameters();
                destInsert.setString(id_num, id);
                destInsert.setString(name_num, srcSet.getString(name_num));
                destInsert.setString(description_num, srcSet.getString(description_num));
                destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
                destInsert.setString(image_num, srcSet.getString(image_num));
                destInsert.executeUpdate();
                
                // Create category assoc for new category 
                assocInsert.clearParameters();
                assocInsert.setString(1, id+"A");
                assocInsert.setString(2, id);
                assocInsert.setString(3, (String)rootCategoryIds.get(websiteid));
                assocInsert.executeUpdate();                
            }
            else if (parentExists)
            {
                debug("Updating weblogcategory id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(id_num, id);
                destUpdate.setString(name_num, srcSet.getString(name_num));
                destUpdate.setString(description_num, srcSet.getString(description_num));
                destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
                destUpdate.setString(image_num, srcSet.getString(image_num));
                destUpdate.setString(6, srcSet.getString(id_num));
                destUpdate.executeUpdate();
            }                
            else 
            {
                info("Not copying weblogcategory id="+id);
            }
        }     
        purgeDeleted(srccon,destcon,"weblogcategory");
        purgeAssocs(destcon,"weblogcategoryassoc","weblogcategory","categoryid");
    }
    private void syncFolderTable(Connection srccon, Connection destcon) throws Exception
    {
        info("--- syncFolderTable ---");
        
        String columns = "id,name,description,parentid,websiteid";
        int id_num=1;
        int name_num=2;
        int description_num=3;
        int parentid_num=4;
        int websiteid_num=5;
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from folder where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into folder "+"("+columns+") "+"values (?,?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update folder set id=?,name=?,description=?,parentid=?,websiteid=?"
            +" where id=?");
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from website where id=?");        

        PreparedStatement assocInsert = destcon.prepareStatement(
            "insert into folderassoc "
            +"(id,folderid,ancestorid,relation) "+"values (?,?,?,'PARENT')");

        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from folder");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(id_num);
            String userid = srcSet.getString(websiteid_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 folder id="+id);
                destInsert.clearParameters();
                destInsert.setString(id_num, id);
                destInsert.setString(name_num, srcSet.getString(name_num));
                destInsert.setString(description_num, srcSet.getString(description_num));
                destInsert.setString(parentid_num, srcSet.getString(parentid_num));
                destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
                destInsert.executeUpdate();
                
                // Create folder assoc for new folder 
                assocInsert.clearParameters();
                assocInsert.setString(1, id+"A");
                assocInsert.setString(2, id);
                assocInsert.setString(3, srcSet.getString(parentid_num));
                assocInsert.executeUpdate();                
            }
            else if (parentExists)
            {
                debug("Updating folder id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(id_num, id);
                destUpdate.setString(name_num, srcSet.getString(name_num));
                destUpdate.setString(description_num, srcSet.getString(description_num));
                destUpdate.setString(parentid_num, srcSet.getString(parentid_num));
                destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
                destUpdate.setString(6, srcSet.getString(id_num));
                destUpdate.executeUpdate();
            }                
            else 
            {
                info("Not copying folder id="+id);
            }
        }     
        purgeDeleted(srccon,destcon,"folder");
        purgeAssocs(destcon,"folderassoc","folder","folderid");
    }
    private void syncBookmarkTable(Connection srccon, Connection destcon) throws Exception
    {
        info("--- syncBookmarkTable ---");
        
        String columns = "id,folderid,name,description,url,weight,priority,image,feedurl";
        int id_num=1;
        int folderid_num=2;
        int name_num=3;
        int description_num=4;
        int url_num=5;
        int weight_num=6;
        int priority_num=7;
        int image_num=8;
        int feedurl_num=9;
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from bookmark where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into bookmark "+"("+columns+") "+"values (?,?,?,?,?,?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update bookmark set id=?,folderid=?,name=?,description=?,url=?,weight=?,priority=?,image=?,feedurl=?"
            +" where id=?");
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from folder where id=?");        

        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from bookmark");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(id_num);
            String folderid = srcSet.getString(folderid_num);
            
            destExistsStmt.clearParameters();
            destExistsStmt.setString(id_num, id);
            ResultSet destSet = destExistsStmt.executeQuery();
            
            parentExistsStmt.clearParameters();
            parentExistsStmt.setString(id_num, folderid);
            ResultSet parentSet = parentExistsStmt.executeQuery();
            
            boolean parentExists = parentSet.first();
            if (!destSet.first() && parentExists)
            {
                debug("Inserting bookmark id="+id);
                destInsert.clearParameters();
                destInsert.setString(id_num, id);
                destInsert.setString(folderid_num, srcSet.getString(folderid_num));
                destInsert.setString(name_num, srcSet.getString(name_num));
                destInsert.setString(description_num, srcSet.getString(name_num));
                destInsert.setString(url_num, srcSet.getString(url_num));
                destInsert.setInt(weight_num, srcSet.getInt(weight_num));