Java > Open Source Codes > org > apache > roller > business > utils > UpgradeDatabase


1 /*
2  * Licensed to the Apache Software Foundation (ASF) under one or more
3  * contributor license agreements. The ASF licenses this file to You
4  * under the Apache License, Version 2.0 (the "License"); you may not
5  * use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License. For additional information regarding
15  * copyright in this work, please see the NOTICE file in the top level
16  * directory of this distribution.
17  */
18
19 package org.apache.roller.business.utils;
20
21 import java.sql.Connection ;
22 import java.sql.PreparedStatement ;
23 import java.sql.ResultSet ;
24 import java.sql.SQLException ;
25 import java.sql.Statement ;
26 import java.util.Date ;
27 import org.apache.commons.logging.Log;
28 import org.apache.commons.logging.LogFactory;
29 import org.apache.roller.RollerException;
30 import org.apache.roller.pojos.PermissionsData;
31
32
33 /**
34  * Upgrade Roller Database.
35  */
36 public class UpgradeDatabase {
37     
38     private static Log mLogger = LogFactory.getLog(UpgradeDatabase.class);
39     
40     // the name of the property which holds the dbversion value
41 private static final String DBVERSION_PROP = "roller.database.version";
42     
43     
44     /**
45      * Upgrade database if dbVersion is older than desiredVersion.
46      */
47     public static void upgradeDatabase(Connection con, String desiredVersion)
48             throws RollerException {
49         
50         int myVersion = 0;
51         int dbversion = -1;
52         
53         // NOTE: this assumes a maximum of 3 digits for the version number
54 // so if we get to 10.0 then we'll need to upgrade this
55
56         // strip out non-digits
57 desiredVersion = desiredVersion.replaceAll("\\Q.\\E", "");
58         desiredVersion = desiredVersion.replaceAll("\\D", "");
59         if(desiredVersion.length() > 3)
60             desiredVersion = desiredVersion.substring(0, 3);
61         
62         // parse to an int
63 try {
64             int parsed = Integer.parseInt(desiredVersion);
65             
66             if(parsed < 100)
67                 myVersion = parsed * 10;
68             else
69                 myVersion = parsed;
70         } catch(Exception e) {}
71         
72         
73         // get the current db version
74 try {
75             Statement stmt = con.createStatement();
76             
77             // just check in the roller_properties table
78 ResultSet rs = stmt.executeQuery(
79                     "select value from roller_properties where name = '"+DBVERSION_PROP+"'");
80             
81             if(rs.next()) {
82                 dbversion = Integer.parseInt(rs.getString(1));
83                 
84             } else {
85                 // tough to know if this is an upgrade with no db version :/
86 // however, if roller_properties is not empty then we at least
87 // we have someone upgrading from 1.2.x
88 rs = stmt.executeQuery("select count(*) from roller_properties");
89                 if(rs.next()) {
90                     if(rs.getInt(1) > 0)
91                         dbversion = 120;
92                 }
93             }
94             
95         } catch(Exception e) {
96             // that's strange ... hopefully we didn't need to upgrade
97 mLogger.error("Couldn't lookup current database version", e);
98             return;
99         }
100         
101         mLogger.debug("Database version = "+dbversion);
102         mLogger.debug("Desired version = "+myVersion);
103         
104         if(dbversion < 0) {
105             mLogger.info("New installation found, setting db version to "+myVersion);
106             UpgradeDatabase.setDatabaseVersion(con, myVersion);
107             return;
108         } else if(dbversion >= myVersion) {
109             mLogger.info("Database is current, no upgrade needed");
110             return;
111         }
112         
113         mLogger.info("Database is old, beginning upgrade to version "+myVersion);
114         
115         // iterate through each upgrade as needed
116 // to add to the upgrade sequence simply add a new "if" statement
117 // for whatever version needed and then define a new method upgradeXXX()
118 if(dbversion < 130) {
119             UpgradeDatabase.upgradeTo130(con);
120             dbversion = 130;
121         }
122         if (dbversion < 200) {
123             UpgradeDatabase.upgradeTo200(con);
124             dbversion = 200;
125         }
126         if(dbversion < 210) {
127             UpgradeDatabase.upgradeTo210(con);
128             dbversion = 210;
129         }
130         if(dbversion < 300) {
131             UpgradeDatabase.upgradeTo300(con);
132             dbversion = 300;
133         }
134         
135         // make sure the database version is the exact version
136 // we are upgrading too.
137 UpgradeDatabase.updateDatabaseVersion(con, myVersion);
138     }
139     
140     
141     /**
142      * Upgrade database for Roller 1.3.0
143      */
144     private static void upgradeTo130(Connection con) throws RollerException {
145         try {
146             /*
147              * The new theme management code is going into place and it uses
148              * the old website.themeEditor attribute to store a users theme.
149              *
150              * In pre-1.3 Roller *all* websites are considered to be using a
151              * custom theme, so we need to make sure this is properly defined
152              * by setting the theme on all websites to custom.
153              *
154              * NOTE: If we don't do this then nothing would break, but some users
155              * would be suprised that their template customizations are no longer
156              * in effect because they are using a shared theme instead.
157              */
158             
159             mLogger.info("Doing upgrade to 130 ...");
160             mLogger.info("Ensuring that all website themes are set to custom");
161             
162             PreparedStatement setCustomThemeStmt = con.prepareStatement(
163                     "update website set editortheme = ?");
164             
165             setCustomThemeStmt.setString(1, org.apache.roller.pojos.Theme.CUSTOM);
166             setCustomThemeStmt.executeUpdate();
167             
168             if (!con.getAutoCommit()) con.commit();
169             
170             mLogger.info("Upgrade to 130 complete.");
171             
172         } catch (SQLException e) {
173             mLogger.error("Problem upgrading database to version 130", e);
174             throw new RollerException("Problem upgrading database to version 130", e);
175         }
176         
177         // If someone is upgrading to 1.3.x then we are setting the db version
178 // for the first time. Normally we would just updateDatabaseVersion()
179 UpgradeDatabase.setDatabaseVersion(con, 130);
180     }
181     
182     /**
183      * Upgrade database for Roller 2.0.0
184      */
185     private static void upgradeTo200(Connection con) throws RollerException {
186         try {
187             mLogger.info("Doing upgrade to 200 ...");
188             mLogger.info("Populating roller_user_permissions table");
189             
190             PreparedStatement websitesQuery = con.prepareStatement(
191                     "select w.id as wid, u.id as uid, u.username as uname from "
192                     + "website as w, rolleruser as u where u.id=w.userid");
193             PreparedStatement websiteUpdate = con.prepareStatement(
194                     "update website set handle=? where id=?");
195             PreparedStatement entryUpdate = con.prepareStatement(
196                     "update weblogentry set userid=?, status=?, "
197                     + "pubtime=pubtime, updatetime=updatetime "
198                     + "where publishentry=? and websiteid=?");
199             PreparedStatement permsInsert = con.prepareStatement(
200                     "insert into roller_user_permissions "
201                     + "(id, website_id, user_id, permission_mask, pending) "
202                     + "values (?,?,?,?,?)");
203             
204             // loop through websites, each has a user
205 ResultSet websiteSet = websitesQuery.executeQuery();
206             while (websiteSet.next()) {
207                 String websiteid = websiteSet.getString("wid");
208                 String userid = websiteSet.getString("uid");
209                 String handle = websiteSet.getString("uname");
210                 mLogger.info("Processing website: " + handle);
211                 
212                 // use website user's username as website handle
213 websiteUpdate.clearParameters();
214                 websiteUpdate.setString(1, handle);
215                 websiteUpdate.setString(2, websiteid);
216                 websiteUpdate.executeUpdate();
217                 
218                 // update all of pubished entries to include userid and status
219 entryUpdate.clearParameters();
220                 entryUpdate.setString( 1, userid);
221                 entryUpdate.setString( 2, "PUBLISHED");
222                 entryUpdate.setBoolean(3, true);
223                 entryUpdate.setString( 4, websiteid);
224                 entryUpdate.executeUpdate();
225                 
226                 // update all of draft entries to include userid and status
227 entryUpdate.clearParameters();
228                 entryUpdate.setString( 1, userid);
229                 entryUpdate.setString( 2, "DRAFT");
230                 entryUpdate.setBoolean(3, false);
231                 entryUpdate.setString( 4, websiteid);
232                 entryUpdate.executeUpdate();
233                 
234                 // add permission for user in website
235 permsInsert.clearParameters();
236                 permsInsert.setString( 1, websiteid+"p");
237                 permsInsert.setString( 2, websiteid);
238                 permsInsert.setString( 3, userid);
239                 permsInsert.setShort( 4, PermissionsData.ADMIN);
240                 permsInsert.setBoolean(5, false);
241                 permsInsert.executeUpdate();
242             }
243             
244             if (!con.getAutoCommit()) con.commit();
245             
246             mLogger.info("Upgrade to 200 complete.");
247             
248         } catch (SQLException e) {
249             mLogger.error("Problem upgrading database to version 200", e);
250             throw new RollerException("Problem upgrading database to version 200", e);
251         }
252         
253         UpgradeDatabase.updateDatabaseVersion(con, 200);
254     }
255     
256     
257     /**
258      * Upgrade database for Roller 2.1.0
259      */
260     private static void upgradeTo210(Connection con) throws RollerException {
261         try {
262             /*
263              * For Roller 2.1.0 we are going to standardize some of the
264              * weblog templates and make them less editable. To do this
265              * we need to do a little surgery.
266              *
267              * The goal for this upgrade is to ensure that ALL weblogs now have
268              * the required "Weblog" template as their default template.
269              */
270             
271             mLogger.info("Doing upgrade to 210 ...");
272             mLogger.info("Ensuring that all weblogs use the 'Weblog' template as their default page");
273             
274             // this query will give us all websites that have modified their
275 // default page to link to something other than "Weblog"
276 PreparedStatement selectUpdateWeblogs = con.prepareStatement(
277                     "select website.id,template,website.handle from website,webpage "+
278                     "where webpage.id = website.defaultpageid "+
279                     "and webpage.link != 'Weblog'");
280             
281             PreparedStatement selectWeblogTemplate = con.prepareStatement(
282                     "select id from webpage where websiteid = ? and link = 'Weblog'");
283             
284             PreparedStatement updateWeblogTemplate = con.prepareStatement(
285                     "update webpage set template = ? where id = ?");
286             
287             // insert a new template for a website
288 PreparedStatement insertWeblogTemplate = con.prepareStatement(
289                     "insert into webpage"+
290                     "(id, name, description, link, websiteid, template, updatetime) "+
291                     "values(?,?,?,?,?,?,?)");
292             
293             // update the default page for a website
294 PreparedStatement updateDefaultPage = con.prepareStatement(
295                     "update website set defaultpageid = ? "+
296                     "where id = ?");
297             
298             String description = "This template is used to render the main "+
299                     "page of your weblog.";
300             ResultSet websiteSet = selectUpdateWeblogs.executeQuery();
301             Date now = new Date ();
302             while (websiteSet.next()) {
303                 String websiteid = websiteSet.getString(1);
304                 String template = websiteSet.getString(2);
305                 String handle = websiteSet.getString(3);
306                 mLogger.info("Processing website: " + handle);
307                 
308                 String defaultpageid = null;
309                 
310                 // it's possible that this weblog has a "Weblog" template, but just
311 // isn't using it as their default. if so we need to fix that.
312 selectWeblogTemplate.clearParameters();
313                 selectWeblogTemplate.setString(1, websiteid);
314                 ResultSet weblogPageSet = selectWeblogTemplate.executeQuery();
315                 if(weblogPageSet.next()) {
316                     // this person already has a "Weblog" template, so update it
317 String id = weblogPageSet.getString(1);
318                     
319                     updateWeblogTemplate.clearParameters();
320                     updateWeblogTemplate.setString(1, template);
321                     updateWeblogTemplate.setString(2, id);
322                     updateWeblogTemplate.executeUpdate();
323                     
324                     // make sure and adjust what default page id we want to use
325 defaultpageid = id;
326                 } else {
327                     // no "Weblog" template, so insert a new one
328 insertWeblogTemplate.clearParameters();
329                     insertWeblogTemplate.setString( 1, websiteid+"q");
330                     insertWeblogTemplate.setString( 2, "Weblog");
331                     insertWeblogTemplate.setString( 3, description);
332                     insertWeblogTemplate.setString( 4, "Weblog");
333                     insertWeblogTemplate.setString( 5, websiteid);
334                     insertWeblogTemplate.setString( 6, template);
335                     insertWeblogTemplate.setDate( 7, new java.sql.Date (now.getTime()));
336                     insertWeblogTemplate.executeUpdate();
337                     
338                     // set the new default page id
339 defaultpageid = websiteid+"q";
340                 }
341                 
342                 // update defaultpageid value
343 updateDefaultPage.clearParameters();
344                 updateDefaultPage.setString( 1, defaultpageid);
345                 updateDefaultPage.setString( 2, websiteid);
346                 updateDefaultPage.executeUpdate();
347             }
348             
349             
350             if (!con.getAutoCommit()) con.commit();
351             
352             mLogger.info("Upgrade to 210 complete.");
353             
354         } catch (SQLException e) {
355             mLogger.error("Problem upgrading database to version 210", e);
356             throw new RollerException("Problem upgrading database to version 210", e);
357         }
358         
359         UpgradeDatabase.updateDatabaseVersion(con, 210);
360     }
361     
362     
363     /**
364      * Upgrade database for Roller 3.0.0
365      */
366     private static void upgradeTo300(Connection con) throws RollerException {
367         try {
368             /*
369              * For Roller 3.0.0 we are allowing each weblogentry to track a
370              * locale now so that we can support multi-lingual blogs. As part
371              * of the upgrade process we want to do 2 things ..
372              *
373              * 1. make sure all weblogs have a locale
374              * 2. set the locale on all entries to the locale for the weblog
375              */
376             
377             mLogger.info("Doing upgrade to 300 ...");
378             
379             // get system default language
380 String locale = java.util.Locale.getDefault().getLanguage();
381             
382             mLogger.info("Setting website locale to "+locale+" for websites with no locale");
383             
384             // update all weblogs where locale is "null"
385 PreparedStatement updateNullWeblogLocale = con.prepareStatement(
386                     "update website set locale = ? where locale is NULL");
387             // update all weblogs where locale is empty string ""
388 PreparedStatement updateEmptyWeblogLocale = con.prepareStatement(
389                     "update website set locale = ? where locale = ''");
390             updateNullWeblogLocale.setString( 1, locale);
391             updateEmptyWeblogLocale.setString( 1, locale);
392             updateNullWeblogLocale.executeUpdate();
393             updateEmptyWeblogLocale.executeUpdate();
394
395             
396             mLogger.info("Setting weblogentry locales to website locale");
397             
398             // get all entries and the locale of its website
399 PreparedStatement selectWeblogsLocale = con.prepareStatement(
400                     "select weblogentry.id,website.locale "+
401                     "from weblogentry,website "+
402                     "where weblogentry.websiteid = website.id");
403             
404             // set the locale for an entry
405 PreparedStatement updateWeblogLocale = con.prepareStatement(
406                     "update weblogentry set locale = ? where id = ?");
407             
408             ResultSet websiteSet = selectWeblogsLocale.executeQuery();
409             while (websiteSet.next()) {
410                 String entryid = websiteSet.getString(1);
411                 String entrylocale = websiteSet.getString(2);
412                 
413                 // update entry locale
414 updateWeblogLocale.clearParameters();
415                 updateWeblogLocale.setString( 1, entrylocale);
416                 updateWeblogLocale.setString( 2, entryid);
417                 updateWeblogLocale.executeUpdate();
418             }
419             
420             
421             if (!con.getAutoCommit()) con.commit();
422             
423             mLogger.info("Upgrade to 300 complete.");
424             
425         } catch (SQLException e) {
426             mLogger.error("Problem upgrading database to version 300", e);
427             throw new RollerException("Problem upgrading database to version 300", e);
428         }
429         
430         UpgradeDatabase.updateDatabaseVersion(con, 300);
431     }
432     
433     
434     /**
435      * Insert a new database.version property.
436      *
437      * This should only be called once for new installations
438      */
439     private static void setDatabaseVersion(Connection con, int version)
440             throws RollerException {
441         
442         try {
443             Statement stmt = con.createStatement();
444             stmt.executeUpdate("insert into roller_properties "+
445                     "values('"+DBVERSION_PROP+"', '"+version+"')");
446             
447             mLogger.debug("Set database verstion to "+version);
448         } catch(SQLException se) {
449             throw new RollerException("Error setting database version.", se);
450         }
451     }
452     
453     
454     /**
455      * Update the existing database.version property
456      */
457     private static void updateDatabaseVersion(Connection con, int version)
458             throws RollerException {
459         
460         try {
461             Statement stmt = con.createStatement();
462             stmt.executeUpdate("update roller_properties "+
463                     "set value = '"+version+"'"+
464                     "where name = '"+DBVERSION_PROP+"'");
465             
466             mLogger.debug("Updated database verstion to "+version);
467         } catch(SQLException se) {
468             throw new RollerException("Error setting database version.", se);
469         }
470     }
471 }
472
473

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags