|
1 package org.roller.business.utils;
2
3 import org.apache.commons.logging.Log;
4 import org.apache.commons.logging.LogFactory;
5 import org.roller.RollerException;
6
7 import java.sql.Connection  ;
8 import java.sql.PreparedStatement ;
9 import java.sql.ResultSet ;
10 import java.sql.SQLException ;
11 import java.sql.Statement ;
12
13
20 public class UpgradeDatabase
21 {
22 private static Log mLogger =
23 LogFactory.getFactory().getInstance(UpgradeDatabase.class);
24
25 public static void upgradeDatabase(Connection con) throws RollerException
26 {
27 try
29 {
30 Statement versionStatement = con.createStatement();
31 ResultSet versionResultSet =
32 versionStatement.executeQuery("select dbversion from rollerconfig");
33 versionResultSet.next();
34 String dbversion = versionResultSet.getString(1);
35 if (dbversion != null) return;
36
37 } catch(Exception e) {
38 return;
40 }
41
42
43 try
44 {
45
47 PreparedStatement rootCatStatement = con.prepareStatement(
48 "select a.id from weblogcategoryassoc as a, weblogcategory as c "+
49 "where c.websiteid=? and a.categoryid=c.id and a.ancestorid is null and a.relation='PARENT'");
50
51 PreparedStatement rootCatCreateStatement = con.prepareStatement(
52 "insert into weblogcategory (id,name,description,websiteid,image) "+
53 "values (?,'root','root',?,NULL)");
54
55 PreparedStatement updateWebsiteStatement = con.prepareStatement(
56 "update website set bloggercatid=?, defaultcatid=? where id=?");
57
58 PreparedStatement catsStatement = con.prepareStatement(
59 "select id from weblogcategory where websiteid=? and id<>?");
60
61 PreparedStatement assocCreateStatement = con.prepareStatement(
62 "insert into weblogcategoryassoc (id,categoryid,ancestorid,relation) "+
63 "values (?,?,?,'PARENT')");
64
65 PreparedStatement rootFolderStatement = con.prepareStatement(
66 "select id from folder where websiteid=? and parentid is null");
67
68 PreparedStatement foldersStatement = con.prepareStatement(
69 "select id,parentid from folder where websiteid=?");
70
71 PreparedStatement folderAssocCreateStatement = con.prepareStatement(
72 "insert into folderassoc (id,folderid,ancestorid,relation) "+
73 "values (?,?,?,'PARENT')");
74
75 Statement websitesStatement = con.createStatement();
77 ResultSet websitesResultSet =
78 websitesStatement.executeQuery("select id from website");
79 while (websitesResultSet.next())
80 {
81 String websiteId = websitesResultSet.getString(1);
82 mLogger.info("Upgrading website id="+websiteId);
83
84 rootCatStatement.clearParameters();
85 rootCatStatement.setString(1, websiteId);
86 ResultSet rootCatResultSet = rootCatStatement.executeQuery();
87
88
89 if (!rootCatResultSet.first()) {
91 rootCatCreateStatement.clearParameters();
93 rootCatCreateStatement.setString(1, websiteId+"R");
94 rootCatCreateStatement.setString(2, websiteId);
95 rootCatCreateStatement.executeUpdate();
96
97 updateWebsiteStatement.clearParameters();
99 updateWebsiteStatement.setString(1, websiteId+"R");
100 updateWebsiteStatement.setString(2, websiteId+"R");
101 updateWebsiteStatement.setString(3, websiteId);
102 updateWebsiteStatement.executeUpdate();
103
104 assocCreateStatement.clearParameters();
106 assocCreateStatement.setString(1, websiteId+"A0");
107 assocCreateStatement.setString(2, websiteId+"R");
108 assocCreateStatement.setString(3, null);
109 assocCreateStatement.executeUpdate();
110
111 catsStatement.clearParameters();
113 catsStatement.setString(1, websiteId);
114 catsStatement.setString(2, websiteId+"R");
115 ResultSet cats = catsStatement.executeQuery();
116 int count = 1;
117 while (cats.next())
118 {
119 String catid = cats.getString(1);
120 assocCreateStatement.clearParameters();
121 assocCreateStatement.setString(1, websiteId+"A"+count++);
122 assocCreateStatement.setString(2, catid);
123 assocCreateStatement.setString(3, websiteId+"R");
124 assocCreateStatement.executeUpdate();
125 }
126 mLogger.debug(" Created root categories and associations");
127
128 rootFolderStatement.clearParameters();
130 rootFolderStatement.setString(1, websiteId);
131 ResultSet rootFolderResultSet = rootFolderStatement.executeQuery();
132 rootFolderResultSet.next();
133 String rootFolderId = rootFolderResultSet.getString(1);
134
135 foldersStatement.clearParameters();
137 foldersStatement.setString(1, websiteId);
138 ResultSet folders = foldersStatement.executeQuery();
139 while (folders.next())
140 {
141 String id = folders.getString(1);
142 String parentId = folders.getString(2);
143 folderAssocCreateStatement.clearParameters();
144 folderAssocCreateStatement.setString(1, id+"R");
145 folderAssocCreateStatement.setString(2, id);
146 if (parentId == null)
147 {
148 folderAssocCreateStatement.setString(3, null);
149 }
150 else
151 {
152 folderAssocCreateStatement.setString(3, rootFolderId);
153 }
154 folderAssocCreateStatement.executeUpdate();
155 }
156 mLogger.debug(" Created folder associations");
157 }
158 }
159
160 Statement versionUpdateStatement = con.createStatement();
161 versionUpdateStatement.executeUpdate(
162 "update rollerconfig set dbversion='995'");
163 mLogger.info("Database upgrade complete.");
164 }
165 catch (SQLException e)
166 {
167 mLogger.error("ERROR in database upgrade",e);
168 throw new RollerException("ERROR in database upgrade",e);
169 }
170 }
171 }
172 |
Popular Tags |
Java API By Example, From Geeks To Geeks.