|
1 package org.roller.business.utils;
2
3 import java.sql.*;
4 import java.util.*;
5 import java.io.*;
6
7
10 public class SyncUpgrade098Xto1000
11 {
12 private Map rootCategoryIds = new Hashtable(); private static boolean debug = true;
14
15 public void syncUpgrade(Connection srccon, Connection destcon)
16 throws Exception 
17 {
18 rootCategoryIds = buildRootCategoryMap(destcon);
19
20 syncRolleruserTable(srccon,destcon);
21 syncUserroleTable(srccon,destcon);
22 syncWebsiteTable(srccon,destcon);
23 syncWebpageTable(srccon,destcon);
24 syncWeblogcategoryTable(srccon,destcon);
25 syncFolderTable(srccon,destcon);
26 syncBookmarkTable(srccon,destcon);
27 syncWeblogentryTable(srccon,destcon);
28 syncCommentTable(srccon,destcon);
29 syncRefererTable(srccon,destcon);
30
31 ConsistencyCheck.findAndDeleteOrphans(destcon, true, debug);
32 }
33 public Map buildRootCategoryMap(Connection destcon) throws Exception
34 {
35 Hashtable map = new Hashtable();
36 Statement destStmt = destcon.createStatement();
37 ResultSet destSet = destStmt.executeQuery(
38 "select c.websiteid,c.id from "
39 +"weblogcategory as c, weblogcategoryassoc as a "
40 +"where c.id=a.categoryid and a.ancestorid is null");
41 while (destSet.next())
42 {
43 String websiteid = destSet.getString(1);
44 String categoryid = destSet.getString(2);
45 map.put(websiteid, categoryid);
46 }
47 return map;
48 }
49 private void info(String s)
50 {
51 System.out.println(s);
52 }
53 private void debug(String s)
54 {
55 if (debug) System.out.println(s);
56 }
57 private void purgeDeleted(Connection srccon, Connection destcon, String tableName)
58 throws Exception
59 {
60 PreparedStatement destRows = destcon.prepareStatement(
61 "select id from "+tableName);
62 PreparedStatement deleteRow = destcon.prepareStatement(
63 "delete from "+tableName+" where id=?");
64 PreparedStatement srcExists = srccon.prepareStatement(
65 "select id from "+tableName+" where id=?");
66 ResultSet destSet = destRows.executeQuery();
67 while (destSet.next())
68 {
69 String id = destSet.getString(1);
70 srcExists.clearParameters();
71 srcExists.setString(1, id);
72 ResultSet existsSet = srcExists.executeQuery();
73 if (!existsSet.next() && !id.endsWith("R")) {
75 deleteRow.clearParameters();
76 deleteRow.setString(1, id);
77 deleteRow.executeUpdate();
78 info("Deleting from "+tableName+" id="+id);
79 }
80 }
81 }
82 private void purgeAssocs(Connection destcon, String assocTable, String mainTable, String fkeyName)
83 throws Exception
84 {
85 info("--- purgeAssocs --- "+assocTable);
86 PreparedStatement assocRows = destcon.prepareStatement(
87 "select id,"+fkeyName+",ancestorid from "+assocTable);
88 PreparedStatement mainExists = destcon.prepareStatement(
89 "select id from "+mainTable+" where id=?");
90 PreparedStatement deleteMain = destcon.prepareStatement(
91 "delete from "+assocTable+" where "+fkeyName+"=?");
92 PreparedStatement ancestorExists = destcon.prepareStatement(
93 "select id from "+mainTable+" where id=?");
94 PreparedStatement deleteAncestor = destcon.prepareStatement(
95 "delete from "+assocTable+" where ancestorid=?");
96 ResultSet assocSet = assocRows.executeQuery();
97 while (assocSet.next())
98 {
99 String id = assocSet.getString(1);
100 String fkey = assocSet.getString(2);
101 String akey = assocSet.getString(3);
102 mainExists.clearParameters();
103 mainExists.setString(1, fkey);
104 ResultSet existsSet = mainExists.executeQuery();
105 if (!existsSet.next())
106 {
107 deleteMain.clearParameters();
108 deleteMain.setString(1, fkey);
109 deleteMain.executeUpdate();
110 info("Deleting from "+assocTable+" where "+fkeyName+"="+id);
111 }
112 ancestorExists.clearParameters();
113 ancestorExists.setString(1, akey);
114 ResultSet ancestorSet = ancestorExists.executeQuery();
115 if (!ancestorSet.next())
116 {
117 deleteAncestor.clearParameters();
118 deleteAncestor.setString(1, akey);
119 deleteAncestor.executeUpdate();
120 info("Deleting from "+assocTable+" where ancestorid="+id);
121 }
122 }
123 }
124 private void syncRolleruserTable(Connection srccon, Connection destcon)
125 throws Exception
126 {
127 info("--- syncRolleruserTable ---");
128 Set existing = new TreeSet();
129 PreparedStatement destExistsStmt = destcon.prepareStatement(
130 "select id from rolleruser where id=?");
131 PreparedStatement destInsert = destcon.prepareStatement(
132 "insert into rolleruser "
133 +"(id,username,passphrase,fullname,emailaddress,datecreated) "
134 +"values (?,?,?,?,?,?)");
135 PreparedStatement destUpdate = destcon.prepareStatement(
136 "update rolleruser set id=?, username=?, passphrase=?, "
137 +"fullname=?, emailaddress=?, datecreated=? where id=?");
138
139 Statement srcStmt = srccon.createStatement();
140 ResultSet srcSet = srcStmt.executeQuery(
141 "select id,username,passphrase,fullname,emailaddress,datecreated "
142 +"from rolleruser");
143 while (srcSet.next())
144 {
145 String id = srcSet.getString(1);
146 existing.add(id);
147 destExistsStmt.clearParameters();
148 destExistsStmt.setString(1,id);
149 ResultSet destSet = destExistsStmt.executeQuery();
150 if (!destSet.first())
151 {
152 debug("Inserting rolleruser id="+id);
153 destInsert.clearParameters();
154 destInsert.setString(1, srcSet.getString(1));
155 destInsert.setString(2, srcSet.getString(2));
156 destInsert.setString(3, srcSet.getString(3));
157 destInsert.setString(4, srcSet.getString(4));
158 destInsert.setString(5, srcSet.getString(5));
159 destInsert.setDate( 6, srcSet.getDate(6));
160 destInsert.executeUpdate();
161 }
162 else
163 {
164 debug("Updating rolleruser id="+id);
165 destUpdate.clearParameters();
166 destUpdate.setString(1, srcSet.getString(1));
167 destUpdate.setString(2, srcSet.getString(2));
168 destUpdate.setString(3, srcSet.getString(3));
169 destUpdate.setString(4, srcSet.getString(4));
170 destUpdate.setString(5, srcSet.getString(5));
171 destUpdate.setDate( 6, srcSet.getDate(6));
172 destUpdate.setString(7, srcSet.getString(1));
173 destUpdate.executeUpdate();
174 }
175 }
176 purgeDeleted(srccon,destcon,"rolleruser");
177 }
178 private void syncUserroleTable(Connection srccon, Connection destcon)
179 throws Exception
180 {
181 info("--- syncUserroleTable ---");
182
183 PreparedStatement destExistsStmt = destcon.prepareStatement(
184 "select id from userrole where id=?");
185 PreparedStatement destInsert = destcon.prepareStatement(
186 "insert into userrole (id,rolename,username,userid) "
187 +"values (?,?,?,?)");
188 PreparedStatement destUpdate = destcon.prepareStatement(
189 "update userrole set id=?, rolename=?, username=?, userid=? "
190 +"where id=?");
191 PreparedStatement parentExistsStmt = destcon.prepareStatement(
192 "select id from rolleruser where id=?");
193
194 Statement srcStmt = srccon.createStatement();
195 ResultSet srcSet = srcStmt.executeQuery(
196 "select id,rolename,username,userid from userrole");
197 while (srcSet.next())
198 {
199 String id = srcSet.getString(1);
200 String userid = srcSet.getString(4);
201
202 destExistsStmt.clearParameters();
203 destExistsStmt.setString(1, id);
204 ResultSet destSet = destExistsStmt.executeQuery();
205
206 parentExistsStmt.clearParameters();
207 parentExistsStmt.setString(1, userid);
208 ResultSet parentSet = parentExistsStmt.executeQuery();
209
210 boolean parentExists = parentSet.first();
211 if (!destSet.first() && parentExists)
212 {
213 debug("Inserting userrole id="+id);
214 destInsert.clearParameters();
215 destInsert.setString(1, srcSet.getString(1));
216 destInsert.setString(2, srcSet.getString(2));
217 destInsert.setString(3, srcSet.getString(3));
218 destInsert.setString(4, srcSet.getString(4));
219 destInsert.executeUpdate();
220 }
221 else if (parentExists)
222 {
223 debug("Updating userrole id="+id);
224 destUpdate.clearParameters();
225 destUpdate.setString(1, srcSet.getString(1));
226 destUpdate.setString(2, srcSet.getString(2));
227 destUpdate.setString(3, srcSet.getString(3));
228 destUpdate.setString(4, srcSet.getString(4));
229 destUpdate.setString(5, srcSet.getString(1));
230 destUpdate.executeUpdate();
231 }
232 else
233 {
234 info("Not copying userrole id="+id);
235 }
236 }
237 purgeDeleted(srccon,destcon,"userrole");
238 }
239 private void syncWebsiteTable(Connection srccon, Connection destcon)
240 throws Exception
241 {
242 info("--- syncWebsiteTable ---");
243
244 int id_num=1;
245 int name_num=2;
246 int description_num=3;
247 int userid_num=4;
248 int defaultpageid_num=5;
249 int weblogdayid_num=6;
250 int ignorewords_num=7;
251 int enablebloggerapi_num=8;
252 int editorpage_num=9;
253 int bloggercatid_num=10;
254 int allowcomments_num=11;
255
256 PreparedStatement destExistsStmt = destcon.prepareStatement(
257 "select id from website where id=?");
258 PreparedStatement destInsert = destcon.prepareStatement(
259 "insert into website (id,name,description,userid,defaultpageid,"
260 +"weblogdayid,ignorewords,enablebloggerapi,editorpage,"
261 +"bloggercatid,allowcomments,defaultcatid) values (?,?,?,?,?,?,?,?,?,?,?,?)");
262 PreparedStatement destUpdate = destcon.prepareStatement(
263 "update website set id=?,name=?,description=?,userid=?,defaultpageid=?,"
264 +"weblogdayid=?,ignorewords=?,enablebloggerapi=?,editorpage=?,"
265 +"bloggercatid=?,allowcomments=? where id=?");
266 PreparedStatement parentExistsStmt = destcon.prepareStatement(
267 "select id from rolleruser where id=?");
268
269 PreparedStatement insertRootCategory = destcon.prepareStatement(
270 "insert into weblogcategory (id,name,description,websiteid,image) "+
271 "values (?,'root','root',?,NULL)");
272 PreparedStatement insertRootCategoryAssoc = destcon.prepareStatement(
273 "insert into weblogcategoryassoc (id,categoryid,ancestorid,relation)"
274 +" values (?,?,NULL,'PARENT')");
275
276 Statement srcStmt = srccon.createStatement();
277 ResultSet srcSet = srcStmt.executeQuery(
278 "select id,name,description,userid,defaultpageid,weblogdayid,"
279 +"ignorewords,enablebloggerapi,editorpage,bloggercatid,allowcomments"
280 +" from website");
281 while (srcSet.next())
282 {
283 String id = srcSet.getString(id_num);
284 String userid = srcSet.getString(userid_num);
285
286 destExistsStmt.clearParameters();
287 destExistsStmt.setString(id_num, id);
288 ResultSet destSet = destExistsStmt.executeQuery();
289
290 parentExistsStmt.clearParameters();
291 parentExistsStmt.setString(id_num, userid);
292 ResultSet parentSet = parentExistsStmt.executeQuery();
293 boolean parentExists = parentSet.first();
294
295 if (!destSet.first() && parentExists)
296 {
297 debug("Inserting website id="+id);
298 destInsert.clearParameters();
299 destInsert.setString(id_num, srcSet.getString(id_num));
300 destInsert.setString(name_num, srcSet.getString(name_num));
301 destInsert.setString(description_num, srcSet.getString(description_num));
302 destInsert.setString(userid_num, srcSet.getString(userid_num));
303 destInsert.setString(defaultpageid_num, srcSet.getString(defaultpageid_num));
304 destInsert.setString(weblogdayid_num, srcSet.getString(weblogdayid_num));
305 destInsert.setString(ignorewords_num, srcSet.getString(ignorewords_num));
306 destInsert.setBoolean(enablebloggerapi_num, srcSet.getBoolean(enablebloggerapi_num));
307 destInsert.setString(editorpage_num, srcSet.getString(editorpage_num));
308 destInsert.setString(bloggercatid_num, srcSet.getString(bloggercatid_num));
309 destInsert.setBoolean(allowcomments_num, srcSet.getBoolean(allowcomments_num));
310 destInsert.setString(12, id+"R"); destInsert.executeUpdate();
312
313 insertRootCategory.clearParameters();
315 insertRootCategory.setString(1, id+"R");
316 insertRootCategory.setString(2, id);
317 insertRootCategory.executeUpdate();
318 rootCategoryIds.put(id, id+"R"); debug(" Inserting root weblogcategory id="+id+"R");
320
321 insertRootCategoryAssoc.clearParameters();
323 insertRootCategoryAssoc.setString(1, id+"A");
324 insertRootCategoryAssoc.setString(2, id+"R");
325 insertRootCategoryAssoc.executeUpdate();
326 debug(" Inserting root weblogcategoryassoc id="+id+"A");
327 }
328 else if (parentExists)
329 {
330 debug("Updating website id="+id);
331 destUpdate.clearParameters();
332 destUpdate.setString(id_num, srcSet.getString(id_num));
333 destUpdate.setString(name_num, srcSet.getString(name_num));
334 destUpdate.setString(description_num, srcSet.getString(description_num));
335 destUpdate.setString(userid_num, srcSet.getString(userid_num));
336 destUpdate.setString(defaultpageid_num, srcSet.getString(defaultpageid_num));
337 destUpdate.setString(weblogdayid_num, srcSet.getString(weblogdayid_num));
338 destUpdate.setString(ignorewords_num, srcSet.getString(ignorewords_num));
339 destUpdate.setBoolean(enablebloggerapi_num, srcSet.getBoolean(enablebloggerapi_num));
340 destUpdate.setString(editorpage_num, srcSet.getString(editorpage_num));
341 destUpdate.setString(bloggercatid_num, srcSet.getString(bloggercatid_num));
342 destUpdate.setBoolean(allowcomments_num, srcSet.getBoolean(allowcomments_num));
343 destUpdate.setString(12, srcSet.getString(id_num));
344 destUpdate.executeUpdate();
345 }
346 else
347 {
348 info("Not copying website id="+id);
349 }
350 }
351 purgeDeleted(srccon,destcon,"website");
352 }
353 private void syncWebpageTable(Connection srccon, Connection destcon) throws Exception
354 {
355 info("--- syncWebpageTable ---");
356
357 String columns = "id,name,description,link,websiteid,template,updatetime";
358 int id_num=1;
359 int name_num=2;
360 int description_num=3;
361 int link_num=4;
362 int websiteid_num=5;
363 int template_num=6;
364 int updatetime_num=7;
365
366 PreparedStatement destExistsStmt = destcon.prepareStatement(
367 "select id from webpage where id=?");
368 PreparedStatement destInsert = destcon.prepareStatement(
369 "insert into webpage "+"("+columns+") "+"values (?,?,?,?,?,?,?)");
370 PreparedStatement destUpdate = destcon.prepareStatement(
371 "update webpage set id=?,name=?,description=?,link=?,websiteid=?,"
372 +"template=?,updatetime=? where id=?");
373 PreparedStatement parentExistsStmt = destcon.prepareStatement(
374 "select id from website where id=?");
375
376 Statement srcStmt = srccon.createStatement();
377 ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from webpage");
378 while (srcSet.next())
379 {
380 String id = srcSet.getString(id_num);
381 String userid = srcSet.getString(websiteid_num);
382
383 destExistsStmt.clearParameters();
384 destExistsStmt.setString(id_num, id);
385 ResultSet destSet = destExistsStmt.executeQuery();
386
387 parentExistsStmt.clearParameters();
388 parentExistsStmt.setString(id_num, userid);
389 ResultSet parentSet = parentExistsStmt.executeQuery();
390
391 boolean parentExists = parentSet.first();
392 if (!destSet.first() && parentExists)
393 {
394 debug("Inserting webpage id="+id);
395 destInsert.clearParameters();
396 destInsert.setString(id_num, srcSet.getString(id_num));
397 destInsert.setString(name_num, srcSet.getString(name_num));
398 destInsert.setString(description_num, srcSet.getString(description_num));
399 destInsert.setString(link_num, srcSet.getString(link_num));
400 destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
401 destInsert.setString(template_num, srcSet.getString(template_num));
402 destInsert.setTimestamp(updatetime_num, srcSet.getTimestamp(updatetime_num));
403 destInsert.executeUpdate();
404 }
405 else if (parentExists)
406 {
407 debug("Updating webpage id="+id);
408 destUpdate.clearParameters();
409 destUpdate.setString(id_num, srcSet.getString(id_num));
410 destUpdate.setString(name_num, srcSet.getString(name_num));
411 destUpdate.setString(description_num, srcSet.getString(description_num));
412 destUpdate.setString(link_num, srcSet.getString(link_num));
413 destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
414 destUpdate.setString(template_num, srcSet.getString(template_num));
415 destUpdate.setTimestamp(updatetime_num, srcSet.getTimestamp(updatetime_num));
416 destUpdate.setString(8, srcSet.getString(id_num));
417 destUpdate.executeUpdate();
418 }
419 else
420 {
421 info("Not copying webpage id="+id);
422 }
423 }
424 purgeDeleted(srccon,destcon,"webpage");
425 }
426 private void syncWeblogcategoryTable(Connection srccon, Connection destcon) throws Exception
427 {
428 info("--- syncWeblogcategoryTable ---");
429
430 String columns = "id,name,description,websiteid,image";
431 int id_num=1;
432 int name_num=2;
433 int description_num=3;
434 int websiteid_num=4;
435 int image_num=5;
436
437 PreparedStatement destExistsStmt = destcon.prepareStatement(
438 "select id from weblogcategory where id=?");
439 PreparedStatement destInsert = destcon.prepareStatement(
440 "insert into weblogcategory "+"("+columns+") "+"values (?,?,?,?,?)");
441 PreparedStatement destUpdate = destcon.prepareStatement(
442 "update weblogcategory set id=?,name=?,description=?,websiteid=?,"
443 +"image=? where id=?");
444 PreparedStatement parentExistsStmt = destcon.prepareStatement(
445 "select id from website where id=?");
446
447 PreparedStatement assocInsert = destcon.prepareStatement(
448 "insert into weblogcategoryassoc "
449 +"(id,categoryid,ancestorid,relation) "+"values (?,?,?,'PARENT')");
450
451 Statement srcStmt = srccon.createStatement();
452 ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from weblogcategory");
453 while (srcSet.next())
454 {
455 String id = srcSet.getString(id_num);
456 String websiteid = srcSet.getString(websiteid_num);
457
458 destExistsStmt.clearParameters();
459 destExistsStmt.setString(id_num, id);
460 ResultSet destSet = destExistsStmt.executeQuery();
461
462 parentExistsStmt.clearParameters();
463 parentExistsStmt.setString(id_num, websiteid);
464 ResultSet parentSet = parentExistsStmt.executeQuery();
465
466 boolean parentExists = parentSet.first();
467 if (!destSet.first() && parentExists)
468 {
469 debug("Inserting weblogcategory id="+id);
470 destInsert.clearParameters();
471 destInsert.setString(id_num, id);
472 destInsert.setString(name_num, srcSet.getString(name_num));
473 destInsert.setString(description_num, srcSet.getString(description_num));
474 destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
475 destInsert.setString(image_num, srcSet.getString(image_num));
476 destInsert.executeUpdate();
477
478 assocInsert.clearParameters();
480 assocInsert.setString(1, id+"A");
481 assocInsert.setString(2, id);
482 assocInsert.setString(3, (String )rootCategoryIds.get(websiteid));
483 assocInsert.executeUpdate();
484 }
485 else if (parentExists)
486 {
487 debug("Updating weblogcategory id="+id);
488 destUpdate.clearParameters();
489 destUpdate.setString(id_num, id);
490 destUpdate.setString(name_num, srcSet.getString(name_num));
491 destUpdate.setString(description_num, srcSet.getString(description_num));
492 destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
493 destUpdate.setString(image_num, srcSet.getString(image_num));
494 destUpdate.setString(6, srcSet.getString(id_num));
495 destUpdate.executeUpdate();
496 }
497 else
498 {
499 info("Not copying weblogcategory id="+id);
500 }
501 }
502 purgeDeleted(srccon,destcon,"weblogcategory");
503 purgeAssocs(destcon,"weblogcategoryassoc","weblogcategory","categoryid");
504 }
505 private void syncFolderTable(Connection srccon, Connection destcon) throws Exception
506 {
507 info("--- syncFolderTable ---");
508
509 String columns = "id,name,description,parentid,websiteid";
510 int id_num=1;
511 int name_num=2;
512 int description_num=3;
513 int parentid_num=4;
514 int websiteid_num=5;
515
516 PreparedStatement destExistsStmt = destcon.prepareStatement(
517 "select id from folder where id=?");
518 PreparedStatement destInsert = destcon.prepareStatement(
519 "insert into folder "+"("+columns+") "+"values (?,?,?,?,?)");
520 PreparedStatement destUpdate = destcon.prepareStatement(
521 "update folder set id=?,name=?,description=?,parentid=?,websiteid=?"
522 +" where id=?");
523 PreparedStatement parentExistsStmt = destcon.prepareStatement(
524 "select id from website where id=?");
525
526 PreparedStatement assocInsert = destcon.prepareStatement(
527 "insert into folderassoc "
528 +"(id,folderid,ancestorid,relation) "+"values (?,?,?,'PARENT')");
529
530 Statement srcStmt = srccon.createStatement();
531 ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from folder");
532 while (srcSet.next())
533 {
534 String id = srcSet.getString(id_num);
535 String userid = srcSet.getString(websiteid_num);
536
537 destExistsStmt.clearParameters();
538 destExistsStmt.setString(id_num, id);
539 ResultSet destSet = destExistsStmt.executeQuery();
540
541 parentExistsStmt.clearParameters();
542 parentExistsStmt.setString(id_num, userid);
543 ResultSet parentSet = parentExistsStmt.executeQuery();
544
545 boolean parentExists = parentSet.first();
546 if (!destSet.first() && parentExists)
547 {
548 debug("Inserting folder id="+id);
549 destInsert.clearParameters();
550 destInsert.setString(id_num, id);
551 destInsert.setString(name_num, srcSet.getString(name_num));
552 destInsert.setString(description_num, srcSet.getString(description_num));
553 destInsert.setString(parentid_num, srcSet.getString(parentid_num));
554 destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
555 destInsert.executeUpdate();
556
557 assocInsert.clearParameters();
559 assocInsert.setString(1, id+"A");
560 assocInsert.setString(2, id);
561 assocInsert.setString(3, srcSet.getString(parentid_num));
562 assocInsert.executeUpdate();
563 }
564 else if (parentExists)
565 {
566 debug("Updating folder id="+id);
567 destUpdate.clearParameters();
568 destUpdate.setString(id_num, id);
569 destUpdate.setString(name_num, srcSet.getString(name_num));
570 destUpdate.setString(description_num, srcSet.getString(description_num));
571 destUpdate.setString(parentid_num, srcSet.getString(parentid_num));
572 destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
573 destUpdate.setString(6, srcSet.getString(id_num));
574 destUpdate.executeUpdate();
575 }
576 else
577 {
578 info("Not copying folder id="+id);
579 }
580 }
581 purgeDeleted(srccon,destcon,"folder");
582 purgeAssocs(destcon,"folderassoc","folder","folderid");
583 }
584 private void syncBookmarkTable(Connection srccon, Connection destcon) throws Exception
585 {
586 info("--- syncBookmarkTable ---");
587
588 String columns = "id,folderid,name,description,url,weight,priority,image,feedurl";
589 int id_num=1;
590 int folderid_num=2;
591 int name_num=3;
592 int description_num=4;
593 int url_num=5;
594 int weight_num=6;
595 int priority_num=7;
596 int image_num=8;
597 int feedurl_num=9;
598
599 PreparedStatement destExistsStmt = destcon.prepareStatement(
600 "select id from bookmark where id=?");
601 PreparedStatement destInsert = destcon.prepareStatement(
602 "insert into bookmark "+"("+columns+") "+"values (?,?,?,?,?,?,?,?,?)");
603 PreparedStatement destUpdate = destcon.prepareStatement(
604 "update bookmark set id=?,folderid=?,name=?,description=?,url=?,weight=?,priority=?,image=?,feedurl=?"
605 +" where id=?");
606 PreparedStatement parentExistsStmt = destcon.prepareStatement(
607 "select id from folder where id=?");
608
609 Statement srcStmt = srccon.createStatement();
610 ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from bookmark");
611 while (srcSet.next())
612 {
613 String id = srcSet.getString(id_num);
614 String folderid = srcSet.getString(folderid_num);
615
616 destExistsStmt.clearParameters();
617 destExistsStmt.setString(id_num, id);
618 ResultSet destSet = destExistsStmt.executeQuery();
619
620 parentExistsStmt.clearParameters();
621 parentExistsStmt.setString(id_num, folderid);
622 ResultSet parentSet = parentExistsStmt.executeQuery();
623
624 boolean parentExists = parentSet.first();
625 if (!destSet.first() && parentExists)
626 {
627 debug("Inserting bookmark id="+id);
628 destInsert.clearParameters();
629 destInsert.setString(id_num, id);
630 destInsert.setString(folderid_num, srcSet.getString(folderid_num));
631 destInsert.setString(name_num, srcSet.getString(name_num));
632 destInsert.setString(description_num, srcSet.getString(name_num));
633 destInsert.setString(url_num, srcSet.getString(url_num));
634 destInsert.setInt(weight_num, srcSet.getInt(weight_num));
635 destInsert.setInt(priority_num, srcSet.getInt(priority_num));
636 destInsert.setString(image_num, srcSet.getString(image_num));
637 destInsert.setString(feedurl_num, srcSet.getString(feedurl_num));
638 destInsert.executeUpdate();
639 }
640 else if (parentExists)
641 {
642 debug("Updating bookmark id="+id);
643 destUpdate.clearParameters();
644 destUpdate.setString(id_num, id);
645 destUpdate.setString(folderid_num, srcSet.getString(folderid_num));
646 destUpdate.setString(name_num, srcSet.getString(name_num));
647 destUpdate.setString(description_num, srcSet.getString(name_num));
648 destUpdate.setString(url_num, srcSet.getString(url_num));
649 destUpdate.setString(weight_num, srcSet.getString(weight_num));
650 destUpdate.setString(priority_num, srcSet.getString(priority_num));
651 destUpdate.setString(image_num, srcSet.getString(image_num));
652 destUpdate.setString(feedurl_num, srcSet.getString(feedurl_num));
653 destUpdate.setString(10, srcSet.getString(id_num));
654 destUpdate.executeUpdate();
655 }
656 else
657 {
658 info("Not copying bookmark id="+id);
659 }
660 }
661 purgeDeleted(srccon,destcon,"bookmark");
662 }
663 private void syncWeblogentryTable(Connection srccon, Connection destcon) throws Exception
664 {
665 info("--- syncWeblogentryTable ---");
666
667 String columns = "id,anchor,title,text,pubtime,updatetime,websiteid,categoryid,publishentry";
668 int id_num=1;
669 int anchor_num=2;
670 int title_num=3;
671 int text_num=4;
672 int pubtime_num=5;
673 int updatetime_num=6;
674 int websiteid_num=7;
675 int categoryid_num=8;
676 int publishentry_num=9;
677
678 PreparedStatement destExistsStmt = destcon.prepareStatement(
679 "select id from weblogentry where id=?");
680 PreparedStatement destInsert = destcon.prepareStatement(
681 "insert into weblogentry "+"("+columns+") "+"values (?,?,?,?,?,?,?,?,?)");
682 PreparedStatement destUpdate = destcon.prepareStatement(
683 "update weblogentry set id=?,anchor=?,title=?,text=?,pubtime=?,"
684 +"updatetime=?,websiteid=?,categoryid=?,publishentry=?"
685 +" where id=?");
686
687 PreparedStatement parentExistsStmt = destcon.prepareStatement(
688 "select id from website where id=?");
689 PreparedStatement categoryExistsStmt = destcon.prepareStatement(
690 "select id from weblogcategory where id=?");
691
692 Statement srcStmt = srccon.createStatement();
693 ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from weblogentry");
694 while (srcSet.next())
695 {
696 String id = srcSet.getString(id_num);
697 String websiteid = srcSet.getString(websiteid_num);
698 String categoryid = srcSet.getString(categoryid_num);
699
700 destExistsStmt.clearParameters();
701 destExistsStmt.setString(id_num, id);
702 ResultSet destSet = destExistsStmt.executeQuery();
703
704 parentExistsStmt.clearParameters();
705 parentExistsStmt.setString(id_num, websiteid);
706 ResultSet parentSet = parentExistsStmt.executeQuery();
707 boolean parentExists = parentSet.first();
708
709 categoryExistsStmt.clearParameters();
710 categoryExistsStmt.setString(id_num, categoryid);
711 ResultSet categorySet = categoryExistsStmt.executeQuery();
712 boolean categoryExists = categorySet.first();
713
714 if (!destSet.first() && parentExists && categoryExists)
715 {
716 debug("Inserting weblogentry id="+id);
717 destInsert.clearParameters();
718 destInsert.setString(id_num, id);
719 destInsert.setString(anchor_num, srcSet.getString(anchor_num));
720 destInsert.setString(title_num, srcSet.getString(title_num));
721 destInsert.setString(text_num, srcSet.getString(text_num));
722 destInsert.setTimestamp(pubtime_num, srcSet.getTimestamp(pubtime_num));
723 destInsert.setTimestamp(updatetime_num, srcSet.getTimestamp(updatetime_num));
724 destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
725 destInsert.setString(categoryid_num, srcSet.getString(categoryid_num));
726 destInsert.setBoolean(publishentry_num, srcSet.getBoolean(publishentry_num));
727 destInsert.executeUpdate();
728 }
729 else if (parentExists && categoryExists)
730 {
731 debug("Updating weblogentry id="+id);
732 destUpdate.clearParameters();
733 destUpdate.setString(id_num, id);
734 destUpdate.setString(anchor_num, srcSet.getString(anchor_num));
735 destUpdate.setString(title_num, srcSet.getString(title_num));
736 destUpdate.setString(text_num, srcSet.getString(text_num));
737 destUpdate.setTimestamp(pubtime_num, srcSet.getTimestamp(pubtime_num));
738 destUpdate.setTimestamp(updatetime_num, srcSet.getTimestamp(updatetime_num));
739 destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
740 destUpdate.setString(categoryid_num, srcSet.getString(categoryid_num));
741 destUpdate.setBoolean(publishentry_num, srcSet.getBoolean(publishentry_num));
742 destUpdate.setString(10, srcSet.getString(id_num));
743 destUpdate.executeUpdate();
744 }
745 else
746 {
747 info("Not copying weblogentry id="+id);
748 }
749 }
750 purgeDeleted(srccon,destcon,"weblogentry");
751 }
752 private void syncCommentTable(Connection srccon, Connection destcon) throws Exception
753 {
754 info("--- syncCommentTable ---");
755
756 String columns = "id,entryid,name,email,url,content,posttime";
757 int id_num=1;
758 int entryid_num=2;
759 int name_num=3;
760 int email_num=4;
761 int url_num=5;
762 int content_num=6;
763 int posttime_num=7;
764
765 PreparedStatement destExistsStmt = destcon.prepareStatement(
766 "select id from comment where id=?");
767 PreparedStatement destInsert = destcon.prepareStatement(
768 "insert into comment "+"("+columns+") "+"values (?,?,?,?,?,?,?)");
769 PreparedStatement destUpdate = destcon.prepareStatement(
770 "update comment set id=?,entryid=?,name=?,email=?,url=?,content=?,posttime=?"
771 +" where id=?");
772
773 PreparedStatement parentExistsStmt = destcon.prepareStatement(
774 "select id from weblogentry where id=?");
775
776 Statement srcStmt = srccon.createStatement();
777 ResultSet srcSet = srcStmt.executeQuery("select "+columns+" from comment");
778 while (srcSet.next())
779 {
780 String id = srcSet.getString(id_num);
781 String entryid = srcSet.getString(entryid_num);
782
783 destExistsStmt.clearParameters();
784 destExistsStmt.setString(id_num, id);
785 ResultSet destSet = destExistsStmt.executeQuery();
786
787 parentExistsStmt.clearParameters();
788 parentExistsStmt.setString(id_num, entryid);
789 ResultSet parentSet = parentExistsStmt.executeQuery();
790 boolean parentExists = parentSet.first();
791
792 if (!destSet.first() && parentExists)
793 {
794 debug("Inserting comment id="+id);
795 destInsert.clearParameters();
796 destInsert.setString(id_num, id);
797 destInsert.setString(entryid_num, srcSet.getString(entryid_num));
798 destInsert.setString(name_num, srcSet.getString(name_num));
799 destInsert.setString(email_num, srcSet.getString(email_num));
800 destInsert.setString(url_num, srcSet.getString(url_num));
801 destInsert.setString(content_num, srcSet.getString(content_num));
802 destInsert.setTimestamp(posttime_num, srcSet.getTimestamp(posttime_num));
803 destInsert.executeUpdate();
804 }
805 else if (parentExists)
806 {
807 debug("Updating comment id="+id);
808 destUpdate.clearParameters();
809 destUpdate.setString(id_num, id);
810 destUpdate.setString(entryid_num, srcSet.getString(entryid_num));
811 destUpdate.setString(name_num, srcSet.getString(name_num));
812 destUpdate.setString(email_num, srcSet.getString(email_num));
813 destUpdate.setString(url_num, srcSet.getString(url_num));
814 destUpdate.setString(content_num, srcSet.getString(content_num));
815 destUpdate.setTimestamp(posttime_num, srcSet.getTimestamp(posttime_num));
816 destUpdate.setString(8, srcSet.getString(id_num));
817 destUpdate.executeUpdate();
818 }
819 else
820 {
821 info("Not copying comment id="+id);
822 }
823 }
824 purgeDeleted(srccon,destcon,"comment");
825 }
826 private void syncRefererTable(Connection srccon, Connection destcon) throws Exception
827 {
828 info("--- syncRefererTable ---");
829
830 String columns = "id,websiteid,entryid,datestr,refurl,refpermalink,"
831 +"reftime,requrl,title,excerpt,dayhits,totalhits,visible,duplicate";
832 int id_num = 1;
833 int websiteid_num = 2;
834 int entryid_num = 3;
835 int datestr_num = 4;
836 int refurl_num = 5;
837 int refpermalink_num = 6;
838 int reftime_num = 7;
839 int requrl_num = 8;
840 int title_num = 9;
841 int excerpt_num = 10;
842 int dayhits_num = 11;
843 int totalhits_num = 12;
844 int visible_num = 13;
845 int duplicate_num = 14;
846
847 PreparedStatement destExistsStmt = destcon.prepareStatement(
848 "select id from referer where id=?");
849 PreparedStatement destInsert = destcon.prepareStatement(
850 "insert into referer "+"("+columns+") "
851 +"values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?)");
852 PreparedStatement destUpdate = destcon.prepareStatement(
853 "update referer set id=?,websiteid=?,entryid=?,datestr=?,refurl=?,"
854 +"refpermalink=?,reftime=?,requrl=?,title=?,excerpt=?,dayhits=?,"
855 +"totalhits=?,visible=?,duplicate=?"
856 +" where id=?");
857
858 PreparedStatement parentExistsStmt = destcon.prepareStatement(
859 "select id from website where id=?");
860
861 Statement srcStmt = srccon.createStatement();
862 ResultSet srcSet = srcStmt.executeQuery(
864 "select "+columns+" from referer where excerpt is not null");
865 while (srcSet.next())
866 {
867 String id = srcSet.getString(id_num);
868 String entryid = srcSet.getString(entryid_num);
869 String websiteid = srcSet.getString(websiteid_num);
870
871 destExistsStmt.clearParameters();
872 destExistsStmt.setString(id_num, id);
873 ResultSet destSet = destExistsStmt.executeQuery();
874
875 parentExistsStmt.clearParameters();
876 parentExistsStmt.setString(id_num, websiteid);
877 ResultSet parentSet = parentExistsStmt.executeQuery();
878 boolean parentExists = parentSet.first() || websiteid == null;
879
880 if (!destSet.first() && parentExists)
881 {
882 debug("Inserting referer id="+id);
883 destInsert.clearParameters();
884
885 destInsert.setString(id_num, id);
886 destInsert.setString(websiteid_num, srcSet.getString(websiteid_num));
887 destInsert.setString(entryid_num, srcSet.getString(entryid_num));
888 destInsert.setString(datestr_num, srcSet.getString(datestr_num));
889 destInsert.setString(refurl_num, srcSet.getString(refurl_num));
890
891 destInsert.setString(refpermalink_num, srcSet.getString(refpermalink_num));
892 destInsert.setString(reftime_num, srcSet.getString(reftime_num));
893 destInsert.setString(requrl_num, srcSet.getString(requrl_num));
894 destInsert.setString(title_num, srcSet.getString(title_num));
895 destInsert.setString(excerpt_num, srcSet.getString(excerpt_num));
896
897 destInsert.setString(dayhits_num, srcSet.getString(dayhits_num));
898 destInsert.setString(totalhits_num, srcSet.getString(totalhits_num));
899 destInsert.setString(visible_num, srcSet.getString(visible_num));
900 destInsert.setString(duplicate_num, srcSet.getString(duplicate_num));
901
902 destInsert.executeUpdate();
903 }
904 else if (parentExists)
905 {
906 debug("Updating referer id="+id);
907 destUpdate.clearParameters();
908
909 destUpdate.setString(id_num, id);
910 destUpdate.setString(websiteid_num, srcSet.getString(websiteid_num));
911 destUpdate.setString(entryid_num, srcSet.getString(entryid_num));
912 destUpdate.setString(datestr_num, srcSet.getString(datestr_num));
913 destUpdate.setString(refurl_num, srcSet.getString(refurl_num));
914
915 destUpdate.setString(refpermalink_num, srcSet.getString(refpermalink_num));
916 destUpdate.setString(reftime_num, srcSet.getString(reftime_num));
917 destUpdate.setString(requrl_num, srcSet.getString(requrl_num));
918 destUpdate.setString(title_num, srcSet.getString(title_num));
919 destUpdate.setString(excerpt_num, srcSet.getString(excerpt_num));
920
921 destUpdate.setString(dayhits_num, srcSet.getString(dayhits_num));
922 destUpdate.setString(totalhits_num, srcSet.getString(totalhits_num));
923 destUpdate.setString(visible_num, srcSet.getString(visible_num));
924 destUpdate.setString(duplicate_num, srcSet.getString(duplicate_num));
925
926 destUpdate.setString(15, srcSet.getString(id_num));
927 destUpdate.executeUpdate();
928 }
929 }
930 purgeDeleted(srccon,destcon,"referer");
931 }
932
933 public static void main(String [] args) throws Exception
934 {
935 if (args.length > 0)
936 {
937 if ("-debug".equals(args[0]))
938 {
939 debug = true;
940 }
941 }
942 Properties props = new Properties();
943 props.load(new FileInputStream("rollerdb.properties"));
944 Connection destcon = ConsistencyCheck.createConnection(props,"");
945 Connection srccon = ConsistencyCheck.createConnection(props,"src.");
946
947 new SyncUpgrade098Xto1000().syncUpgrade(srccon, destcon);
948 }
949 }
950 |
Popular Tags |
Java API By Example, From Geeks To Geeks.