|
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
|
Java API By Example, From Geeks To Geeks.