syncupgrade098xto1000.java

这个weblogging 设计得比较精巧

                destInsert.setInt(priority_num, srcSet.getInt(priority_num));
                destInsert.setString(image_num, srcSet.getString(image_num));
                destInsert.setString(feedurl_num, srcSet.getString(feedurl_num));
                destInsert.executeUpdate();
            }
            else if (parentExists)
            {
                debug("Updating bookmark id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(id_num, id);
                destUpdate.setString(folderid_num, srcSet.getString(folderid_num));
                destUpdate.setString(name_num, srcSet.getString(name_num));
                destUpdate.setString(description_num, srcSet.getString(name_num));
                destUpdate.setString(url_num, srcSet.getString(url_num));
                destUpdate.setString(weight_num, srcSet.getString(weight_num));
                destUpdate.setString(priority_num, srcSet.getString(priority_num));
                destUpdate.setString(image_num, srcSet.getString(image_num));
                destUpdate.setString(feedurl_num, srcSet.getString(feedurl_num));
                destUpdate.setString(10, srcSet.getString(id_num));
                destUpdate.executeUpdate();
            }                
            else 
            {
                info("Not copying bookmark id="+id);
            }
        }     
        purgeDeleted(srccon,destcon,"bookmark");
    }
    private void syncWeblogentryTable(Connection srccon, Connection destcon) throws Exception
    {
        info("--- syncWeblogentryTable ---");
        
        String columns = "id,anchor,title,text,pubtime,updatetime,websiteid,categoryid,publishentry";
        int id_num=1;
        int anchor_num=2;
        int title_num=3;
        int text_num=4;
        int pubtime_num=5;
        int updatetime_num=6;
        int websiteid_num=7;
        int categoryid_num=8;
        int publishentry_num=9;
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from weblogentry where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into weblogentry "+"("+columns+") "+"values (?,?,?,?,?,?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update weblogentry set id=?,anchor=?,title=?,text=?,pubtime=?,"
            +"updatetime=?,websiteid=?,categoryid=?,publishentry=?"
            +" where id=?");
        
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from website where id=?");        
        PreparedStatement categoryExistsStmt = destcon.prepareStatement(
            "select id from weblogcategory where id=?");        

        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from weblogentry");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(id_num);
            String websiteid = srcSet.getString(websiteid_num);
            String categoryid = srcSet.getString(categoryid_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();
            
            categoryExistsStmt.clearParameters();
            categoryExistsStmt.setString(id_num, categoryid);
            ResultSet categorySet = categoryExistsStmt.executeQuery();
            boolean categoryExists = categorySet.first();
            
            if (!destSet.first() && parentExists && categoryExists)
            {
                debug("Inserting weblogentry id="+id);
                destInsert.clearParameters();
                destInsert.setString(id_num, id);
                destInsert.setString(anchor_num, srcSet.getString(anchor_num));
                destInsert.setString(title_num, srcSet.getString(title_num));
                destInsert.setString(text_num, srcSet.getString(text_num));
                destInsert.setTimestamp(pubtime_num, srcSet.getTimestamp(pubtime_num));
                destInsert.setTimestamp(updatetime_num, srcSet.getTimestamp(updatetime_num));
                destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
                destInsert.setString(categoryid_num, srcSet.getString(categoryid_num));
                destInsert.setBoolean(publishentry_num, srcSet.getBoolean(publishentry_num));
                destInsert.executeUpdate();
            }
            else if (parentExists && categoryExists)
            {
                debug("Updating weblogentry id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(id_num, id);
                destUpdate.setString(anchor_num, srcSet.getString(anchor_num));
                destUpdate.setString(title_num, srcSet.getString(title_num));
                destUpdate.setString(text_num, srcSet.getString(text_num));
                destUpdate.setTimestamp(pubtime_num, srcSet.getTimestamp(pubtime_num));
                destUpdate.setTimestamp(updatetime_num, srcSet.getTimestamp(updatetime_num));
                destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
                destUpdate.setString(categoryid_num, srcSet.getString(categoryid_num));
                destUpdate.setBoolean(publishentry_num, srcSet.getBoolean(publishentry_num));
                destUpdate.setString(10, srcSet.getString(id_num));
                destUpdate.executeUpdate();
            }                
            else 
            {
                info("Not copying weblogentry id="+id);
            }
        }     
        purgeDeleted(srccon,destcon,"weblogentry");
    }
    private void syncCommentTable(Connection srccon, Connection destcon) throws Exception
    {
        info("--- syncCommentTable ---");
        
        String columns = "id,entryid,name,email,url,content,posttime";
        int id_num=1;
        int entryid_num=2;
        int name_num=3;
        int email_num=4;
        int url_num=5;
        int content_num=6;
        int posttime_num=7;
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from comment where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into comment "+"("+columns+") "+"values (?,?,?,?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update comment set id=?,entryid=?,name=?,email=?,url=?,content=?,posttime=?"
            +" where id=?");
        
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from weblogentry where id=?");        

        Statement srcStmt = srccon.createStatement();
        ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from comment");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(id_num);
            String entryid = srcSet.getString(entryid_num);
            
            destExistsStmt.clearParameters();
            destExistsStmt.setString(id_num, id);
            ResultSet destSet = destExistsStmt.executeQuery();
            
            parentExistsStmt.clearParameters();
            parentExistsStmt.setString(id_num, entryid);
            ResultSet parentSet = parentExistsStmt.executeQuery();
            boolean parentExists = parentSet.first();
            
            if (!destSet.first() && parentExists)
            {
                debug("Inserting comment id="+id);
                destInsert.clearParameters();
                destInsert.setString(id_num, id);
                destInsert.setString(entryid_num, srcSet.getString(entryid_num));
                destInsert.setString(name_num, srcSet.getString(name_num));
                destInsert.setString(email_num, srcSet.getString(email_num));
                destInsert.setString(url_num, srcSet.getString(url_num));
                destInsert.setString(content_num, srcSet.getString(content_num));
                destInsert.setTimestamp(posttime_num, srcSet.getTimestamp(posttime_num));
                destInsert.executeUpdate();
            }
            else if (parentExists)
            {
                debug("Updating comment id="+id);
                destUpdate.clearParameters();
                destUpdate.setString(id_num, id);
                destUpdate.setString(entryid_num, srcSet.getString(entryid_num));
                destUpdate.setString(name_num, srcSet.getString(name_num));
                destUpdate.setString(email_num, srcSet.getString(email_num));
                destUpdate.setString(url_num, srcSet.getString(url_num));
                destUpdate.setString(content_num, srcSet.getString(content_num));
                destUpdate.setTimestamp(posttime_num, srcSet.getTimestamp(posttime_num));
                destUpdate.setString(8, srcSet.getString(id_num));
                destUpdate.executeUpdate();
            }                
            else 
            {
                info("Not copying comment id="+id);
            }
        }     
        purgeDeleted(srccon,destcon,"comment");
    }
    private void syncRefererTable(Connection srccon, Connection destcon) throws Exception
    {
        info("--- syncRefererTable ---");
        
        String columns = "id,websiteid,entryid,datestr,refurl,refpermalink,"
            +"reftime,requrl,title,excerpt,dayhits,totalhits,visible,duplicate";
        int id_num = 1;
        int websiteid_num = 2;
        int entryid_num = 3;
        int datestr_num = 4;
        int refurl_num = 5;
        int refpermalink_num = 6;        
        int reftime_num = 7;
        int requrl_num = 8;
        int title_num = 9;
        int excerpt_num = 10;
        int dayhits_num = 11;
        int totalhits_num = 12;
        int visible_num = 13;
        int duplicate_num = 14;
        
        PreparedStatement destExistsStmt = destcon.prepareStatement(
            "select id from referer where id=?");
        PreparedStatement destInsert = destcon.prepareStatement(
            "insert into referer "+"("+columns+") "
            +"values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?)");
        PreparedStatement destUpdate = destcon.prepareStatement(
            "update referer set id=?,websiteid=?,entryid=?,datestr=?,refurl=?,"
            +"refpermalink=?,reftime=?,requrl=?,title=?,excerpt=?,dayhits=?,"
            +"totalhits=?,visible=?,duplicate=?"
            +" where id=?");
        
        PreparedStatement parentExistsStmt = destcon.prepareStatement(
            "select id from website where id=?");        
        
        Statement srcStmt = srccon.createStatement();
        // only sync those with excerpts
        ResultSet srcSet = srcStmt.executeQuery(
                "select "+columns+" from referer where excerpt is not null");
        while (srcSet.next()) 
        {
            String id = srcSet.getString(id_num);
            String entryid = srcSet.getString(entryid_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() || websiteid == null;

            if (!destSet.first() && parentExists)
            {
                debug("Inserting referer id="+id);
                destInsert.clearParameters();
                
                destInsert.setString(id_num, id);
                destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
                destInsert.setString(entryid_num, srcSet.getString(entryid_num));
                destInsert.setString(datestr_num, srcSet.getString(datestr_num));
                destInsert.setString(refurl_num, srcSet.getString(refurl_num));
                
                destInsert.setString(refpermalink_num, srcSet.getString(refpermalink_num));
                destInsert.setString(reftime_num, srcSet.getString(reftime_num));
                destInsert.setString(requrl_num, srcSet.getString(requrl_num));
                destInsert.setString(title_num, srcSet.getString(title_num));
                destInsert.setString(excerpt_num, srcSet.getString(excerpt_num));
                
                destInsert.setString(dayhits_num, srcSet.getString(dayhits_num));
                destInsert.setString(totalhits_num, srcSet.getString(totalhits_num));
                destInsert.setString(visible_num, srcSet.getString(visible_num));
                destInsert.setString(duplicate_num, srcSet.getString(duplicate_num));
                
                destInsert.executeUpdate();
            }
            else if (parentExists)
            {
                debug("Updating referer id="+id);
                destUpdate.clearParameters();
                
                destUpdate.setString(id_num, id);
                destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
                destUpdate.setString(entryid_num, srcSet.getString(entryid_num));
                destUpdate.setString(datestr_num, srcSet.getString(datestr_num));
                destUpdate.setString(refurl_num, srcSet.getString(refurl_num));
                
                destUpdate.setString(refpermalink_num, srcSet.getString(refpermalink_num));
                destUpdate.setString(reftime_num, srcSet.getString(reftime_num));
                destUpdate.setString(requrl_num, srcSet.getString(requrl_num));
                destUpdate.setString(title_num, srcSet.getString(title_num));
                destUpdate.setString(excerpt_num, srcSet.getString(excerpt_num));
                
                destUpdate.setString(dayhits_num, srcSet.getString(dayhits_num));
                destUpdate.setString(totalhits_num, srcSet.getString(totalhits_num));
                destUpdate.setString(visible_num, srcSet.getString(visible_num));
                destUpdate.setString(duplicate_num, srcSet.getString(duplicate_num));
                
                destUpdate.setString(15, srcSet.getString(id_num));
                destUpdate.executeUpdate();
            }                
        }     
        purgeDeleted(srccon,destcon,"referer");
    }
    /** for now... just for testing */
    public static void main(String[] args) throws Exception 
    {
        if (args.length > 0) 
        {
            if ("-debug".equals(args[0])) 
            {
                debug = true;
            }
        }        
        Properties props = new Properties();
        props.load(new FileInputStream("rollerdb.properties"));
        Connection destcon = ConsistencyCheck.createConnection(props,"");
        Connection srccon = ConsistencyCheck.createConnection(props,"src.");
        
        new SyncUpgrade098Xto1000().syncUpgrade(srccon, destcon);
    }
}