001: /**
002: * Licensed under the GNU LESSER GENERAL PUBLIC LICENSE, version 2.1, dated February 1999.
003: *
004: * This program is free software; you can redistribute it and/or modify
005: * it under the terms of the latest version of the GNU Lesser General
006: * Public License as published by the Free Software Foundation;
007: *
008: * This program is distributed in the hope that it will be useful,
009: * but WITHOUT ANY WARRANTY; without even the implied warranty of
010: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
011: * GNU Lesser General Public License for more details.
012: *
013: * You should have received a copy of the GNU Lesser General Public License
014: * along with this program (LICENSE.txt); if not, write to the Free Software
015: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
016: */package org.jamwiki.db;
017:
018: import java.sql.Connection;
019: import java.util.Vector;
020: import org.jamwiki.Environment;
021: import org.jamwiki.WikiBase;
022: import org.jamwiki.model.Role;
023: import org.jamwiki.model.WikiGroup;
024: import org.jamwiki.model.WikiUser;
025: import org.jamwiki.utils.WikiLogger;
026:
027: /**
028: * This class simply contains utility methods for upgrading database schemas
029: * (if needed) between JAMWiki versions. In general upgrade methods will only
030: * be maintained for a few versions and then deleted - for example, JAMWiki version 10.0.0
031: * does not need to keep the upgrade methods from JAMWiki 0.0.1 around.
032: */
033: public class DatabaseUpgrades {
034:
035: private static final WikiLogger logger = WikiLogger
036: .getLogger(DatabaseUpgrades.class.getName());
037:
038: /**
039: *
040: */
041: private DatabaseUpgrades() {
042: }
043:
044: /**
045: *
046: */
047: public static WikiUser getWikiUser(String username)
048: throws Exception {
049: // prior to JAMWiki 0.5.0 the remember_key column did not exist. once
050: // the ability to upgrade to JAMWiki 0.5.0 is removed this code can be
051: // replaced with the method (below) that has been commented out.
052: // user = WikiBase.getDataHandler().lookupWikiUser(username, password, false);
053: Connection conn = null;
054: try {
055: conn = DatabaseConnection.getConnection();
056: AnsiQueryHandler queryHandler = new AnsiQueryHandler();
057: WikiResultSet rs = queryHandler.lookupWikiUser(username,
058: conn);
059: if (rs.size() == 0) {
060: return null;
061: }
062: int userId = rs.getInt(AnsiDataHandler.DATA_WIKI_USER_ID);
063: String sql = "select * from jam_wiki_user where wiki_user_id = ? ";
064: WikiPreparedStatement stmt = new WikiPreparedStatement(sql);
065: stmt.setInt(1, userId);
066: rs = stmt.executeQuery();
067: WikiUser user = new WikiUser(username);
068: user
069: .setUserId(rs
070: .getInt(AnsiDataHandler.DATA_WIKI_USER_ID));
071: user.setDisplayName(rs.getString("display_name"));
072: user.setCreateDate(rs.getTimestamp("create_date"));
073: user.setLastLoginDate(rs.getTimestamp("last_login_date"));
074: user.setCreateIpAddress(rs.getString("create_ip_address"));
075: user.setLastLoginIpAddress(rs
076: .getString("last_login_ip_address"));
077: return user;
078: } catch (Exception e) {
079: DatabaseConnection.handleErrors(conn);
080: throw e;
081: } finally {
082: DatabaseConnection.closeConnection(conn);
083: }
084: }
085:
086: /**
087: *
088: */
089: public static Vector upgrade042(Vector messages) throws Exception {
090: Connection conn = null;
091: try {
092: conn = DatabaseConnection.getConnection();
093: conn.setAutoCommit(false);
094: // drop topic_content column
095: String sql = "alter table jam_topic drop column topic_content ";
096: DatabaseConnection.executeUpdate(sql, conn);
097: messages.add("Dropped topic_content column from jam_topic");
098: // add current_version_id column
099: if (Environment.getValue(Environment.PROP_DB_TYPE).equals(
100: WikiBase.DATA_HANDLER_ORACLE)) {
101: sql = "alter table jam_topic add (current_version_id INTEGER) ";
102: } else {
103: sql = "alter table jam_topic add column current_version_id INTEGER ";
104: }
105: DatabaseConnection.executeUpdate(sql, conn);
106: messages
107: .add("Added current_version_id column to jam_topic");
108: // add current_version_id constraint
109: DatabaseConnection
110: .executeUpdate(
111: AnsiQueryHandler.STATEMENT_CREATE_TOPIC_CURRENT_VERSION_CONSTRAINT,
112: conn);
113: messages
114: .add("Added jam_f_topic_topicv constraint to jam_topic");
115: // update jam_topic records
116: DatabaseConnection
117: .executeUpdate(
118: AnsiQueryHandler.STATEMENT_UPDATE_TOPIC_CURRENT_VERSIONS,
119: conn);
120: messages
121: .add("Added current_version_id values for jam_topic records");
122: // create the jam_watchlist table
123: DatabaseConnection.executeUpdate(
124: AnsiQueryHandler.STATEMENT_CREATE_WATCHLIST_TABLE,
125: conn);
126: messages.add("Created watchlist table");
127: conn.commit();
128: } catch (Exception e) {
129: DatabaseConnection.handleErrors(conn);
130: DatabaseConnection
131: .executeUpdate(AnsiQueryHandler.STATEMENT_DROP_WATCHLIST_TABLE);
132: throw e;
133: } finally {
134: DatabaseConnection.closeConnection(conn);
135: }
136: return messages;
137: }
138:
139: /**
140: *
141: */
142: public static Vector upgrade050(Vector messages) throws Exception {
143: Connection conn = null;
144: try {
145: conn = DatabaseConnection.getConnection();
146: conn.setAutoCommit(false);
147: // add remember_key to jam_wiki_user
148: String sql = "";
149: if (Environment.getValue(Environment.PROP_DB_TYPE).equals(
150: WikiBase.DATA_HANDLER_ORACLE)) {
151: sql = "alter table jam_wiki_user add (remember_key VARCHAR(100)) ";
152: } else {
153: sql = "alter table jam_wiki_user add column remember_key VARCHAR(100) ";
154: }
155: DatabaseConnection.executeUpdate(sql, conn);
156: messages.add("Added remember_key column to jam_wiki_user");
157: // populate remember_key column
158: sql = "update jam_wiki_user set remember_key = (select encoded_password from jam_wiki_user_info where jam_wiki_user.wiki_user_id = jam_wiki_user_info.wiki_user_id) ";
159: DatabaseConnection.executeUpdate(sql, conn);
160: messages.add("Populated the remember_key column with data");
161: // set column not null
162: if (Environment.getValue(Environment.PROP_DB_TYPE).equals(
163: WikiBase.DATA_HANDLER_MYSQL)) {
164: sql = "alter table jam_wiki_user MODIFY COLUMN remember_key VARCHAR(100) NOT NULL ";
165: } else if (Environment.getValue(Environment.PROP_DB_TYPE)
166: .equals(WikiBase.DATA_HANDLER_MSSQL)) {
167: sql = "alter table jam_wiki_user ALTER COLUMN remember_key VARCHAR(100) NOT NULL ";
168: } else if (Environment.getValue(Environment.PROP_DB_TYPE)
169: .equals(WikiBase.DATA_HANDLER_ORACLE)) {
170: sql = "alter table jam_wiki_user modify (remember_key VARCHAR(100) NOT NULL) ";
171: } else {
172: sql = "alter table jam_wiki_user ALTER COLUMN remember_key SET NOT NULL ";
173: }
174: DatabaseConnection.executeUpdate(sql, conn);
175: messages.add("remember_key column set to NOT NULL");
176: // add default_locale column
177: if (Environment.getValue(Environment.PROP_DB_TYPE).equals(
178: WikiBase.DATA_HANDLER_ORACLE)) {
179: sql = "alter table jam_wiki_user add (default_locale VARCHAR(8)) ";
180: } else {
181: sql = "alter table jam_wiki_user add column default_locale VARCHAR(8) ";
182: }
183: DatabaseConnection.executeUpdate(sql, conn);
184: messages
185: .add("Added default_locale column to jam_wiki_user");
186: conn.commit();
187: } catch (Exception e) {
188: DatabaseConnection.handleErrors(conn);
189: throw e;
190: } finally {
191: DatabaseConnection.closeConnection(conn);
192: }
193: return messages;
194: }
195:
196: /**
197: *
198: */
199: public static Vector upgrade060(Vector messages) throws Exception {
200: Connection conn = null;
201: try {
202: conn = DatabaseConnection.getConnection();
203: conn.setAutoCommit(false);
204: // create jam_group table
205: DatabaseConnection
206: .executeUpdate(
207: AnsiQueryHandler.STATEMENT_CREATE_GROUP_TABLE,
208: conn);
209: messages.add("Added jam_group table");
210: // create jam_role table
211: DatabaseConnection.executeUpdate(
212: AnsiQueryHandler.STATEMENT_CREATE_ROLE_TABLE, conn);
213: messages.add("Added jam_role table");
214: // create jam_role_map table
215: DatabaseConnection.executeUpdate(
216: AnsiQueryHandler.STATEMENT_CREATE_ROLE_MAP_TABLE,
217: conn);
218: messages.add("Added jam_role_map table");
219: // setup basic roles
220: WikiDatabase.setupRoles(conn);
221: messages.add("Added basic wiki roles.");
222: // setup basic groups
223: WikiDatabase.setupGroups(conn);
224: messages.add("Added basic wiki groups.");
225: // convert old-style admins to new
226: String sql = null;
227: // assign admins all permissions during upgrades just to be safe. for
228: // new installs it is sufficient just to give them the basics
229: Role[] adminRoles = { Role.ROLE_ADMIN,
230: Role.ROLE_EDIT_EXISTING, Role.ROLE_EDIT_NEW,
231: Role.ROLE_MOVE, Role.ROLE_SYSADMIN,
232: Role.ROLE_TRANSLATE, Role.ROLE_UPLOAD,
233: Role.ROLE_VIEW };
234: for (int i = 0; i < adminRoles.length; i++) {
235: Role adminRole = adminRoles[i];
236: sql = "insert into jam_role_map ( "
237: + " role_name, wiki_user_id " + ") "
238: + "select '" + adminRole.getAuthority()
239: + "', wiki_user_id "
240: + "from jam_wiki_user where is_admin = 1 ";
241: DatabaseConnection.executeUpdate(sql, conn);
242: }
243: if (Environment
244: .getBooleanValue(Environment.PROP_TOPIC_FORCE_USERNAME)) {
245: sql = "delete from jam_role_map "
246: + "where role_name = ? "
247: + "and group_id = (select group_id from jam_group where group_name = ?) ";
248: WikiPreparedStatement stmt = new WikiPreparedStatement(
249: sql);
250: stmt.setString(1, Role.ROLE_EDIT_EXISTING
251: .getAuthority());
252: stmt.setString(2, WikiGroup.GROUP_ANONYMOUS);
253: stmt.executeUpdate(conn);
254: stmt = new WikiPreparedStatement(sql);
255: stmt.setString(1, Role.ROLE_EDIT_NEW.getAuthority());
256: stmt.setString(2, WikiGroup.GROUP_ANONYMOUS);
257: stmt.executeUpdate(conn);
258: }
259: if (!Environment
260: .getBooleanValue(Environment.PROP_TOPIC_NON_ADMIN_TOPIC_MOVE)) {
261: sql = "delete from jam_role_map "
262: + "where role_name = ? "
263: + "and group_id = (select group_id from jam_group where group_name = ?) ";
264: WikiPreparedStatement stmt = new WikiPreparedStatement(
265: sql);
266: stmt.setString(1, Role.ROLE_MOVE.getAuthority());
267: stmt.setString(2, WikiGroup.GROUP_REGISTERED_USER);
268: stmt.executeUpdate(conn);
269: }
270: sql = "alter table jam_wiki_user drop column is_admin ";
271: DatabaseConnection.executeUpdate(sql, conn);
272: messages
273: .add("Converted admin users to new role structure.");
274: conn.commit();
275: } catch (Exception e) {
276: DatabaseConnection.handleErrors(conn);
277: try {
278: DatabaseConnection
279: .executeUpdate(AnsiQueryHandler.STATEMENT_DROP_ROLE_MAP_TABLE);
280: } catch (Exception ex) {
281: }
282: try {
283: DatabaseConnection
284: .executeUpdate(AnsiQueryHandler.STATEMENT_DROP_ROLE_TABLE);
285: } catch (Exception ex) {
286: }
287: try {
288: DatabaseConnection
289: .executeUpdate(AnsiQueryHandler.STATEMENT_DROP_GROUP_TABLE);
290: } catch (Exception ex) {
291: }
292: throw e;
293: } finally {
294: DatabaseConnection.closeConnection(conn);
295: }
296: return messages;
297: }
298:
299: /**
300: *
301: */
302: public static Vector upgrade061(Vector messages) throws Exception {
303: Connection conn = null;
304: try {
305: String sql = null;
306: conn = DatabaseConnection.getConnection();
307: conn.setAutoCommit(false);
308: // delete ROLE_DELETE
309: sql = "delete from jam_role_map where role_name = 'ROLE_DELETE'";
310: DatabaseConnection.executeUpdate(sql, conn);
311: sql = "delete from jam_role where role_name = 'ROLE_DELETE'";
312: DatabaseConnection.executeUpdate(sql, conn);
313: messages.add("Removed ROLE_DELETE");
314: conn.commit();
315: } catch (Exception e) {
316: DatabaseConnection.handleErrors(conn);
317: throw e;
318: } finally {
319: DatabaseConnection.closeConnection(conn);
320: }
321: return messages;
322: }
323:
324: /**
325: *
326: */
327: public static Vector upgrade063(Vector messages) throws Exception {
328: Connection conn = null;
329: try {
330: String sql = null;
331: conn = DatabaseConnection.getConnection();
332: conn.setAutoCommit(false);
333: // increase the size of ip address columns
334: String dbType = Environment
335: .getValue(Environment.PROP_DB_TYPE);
336: if (dbType.equals(WikiBase.DATA_HANDLER_DB2)
337: || dbType.equals(WikiBase.DATA_HANDLER_DB2400)) {
338: sql = "alter table jam_topic_version alter column wiki_user_ip_address set data type varchar(39) ";
339: DatabaseConnection.executeUpdate(sql, conn);
340: sql = "alter table jam_file_version alter column wiki_user_ip_address set data type varchar(39) ";
341: DatabaseConnection.executeUpdate(sql, conn);
342: sql = "alter table jam_wiki_user alter column create_ip_address set data type varchar(39) ";
343: DatabaseConnection.executeUpdate(sql, conn);
344: sql = "alter table jam_wiki_user alter column last_login_ip_address set data type varchar(39) ";
345: DatabaseConnection.executeUpdate(sql, conn);
346: } else if (dbType.equals(WikiBase.DATA_HANDLER_MYSQL)
347: || dbType.equals(WikiBase.DATA_HANDLER_ORACLE)) {
348: sql = "alter table jam_topic_version modify wiki_user_ip_address varchar(39) not null ";
349: DatabaseConnection.executeUpdate(sql, conn);
350: sql = "alter table jam_file_version modify wiki_user_ip_address varchar(39) not null ";
351: DatabaseConnection.executeUpdate(sql, conn);
352: sql = "alter table jam_wiki_user modify create_ip_address varchar(39) not null ";
353: DatabaseConnection.executeUpdate(sql, conn);
354: sql = "alter table jam_wiki_user modify last_login_ip_address varchar(39) not null ";
355: DatabaseConnection.executeUpdate(sql, conn);
356: } else if (dbType.equals(WikiBase.DATA_HANDLER_POSTGRES)) {
357: sql = "alter table jam_topic_version alter column wiki_user_ip_address type varchar(39) ";
358: DatabaseConnection.executeUpdate(sql, conn);
359: sql = "alter table jam_file_version alter column wiki_user_ip_address type varchar(39) ";
360: DatabaseConnection.executeUpdate(sql, conn);
361: sql = "alter table jam_wiki_user alter column create_ip_address type varchar(39) ";
362: DatabaseConnection.executeUpdate(sql, conn);
363: sql = "alter table jam_wiki_user alter column last_login_ip_address type varchar(39) ";
364: DatabaseConnection.executeUpdate(sql, conn);
365: } else {
366: sql = "alter table jam_topic_version alter column wiki_user_ip_address varchar(39) not null ";
367: DatabaseConnection.executeUpdate(sql, conn);
368: sql = "alter table jam_file_version alter column wiki_user_ip_address varchar(39) not null ";
369: DatabaseConnection.executeUpdate(sql, conn);
370: sql = "alter table jam_wiki_user alter column create_ip_address varchar(39) not null ";
371: DatabaseConnection.executeUpdate(sql, conn);
372: sql = "alter table jam_wiki_user alter column last_login_ip_address varchar(39) not null ";
373: DatabaseConnection.executeUpdate(sql, conn);
374: }
375: messages
376: .add("Increased IP address field sizes to support IPv6");
377: conn.commit();
378: } catch (Exception e) {
379: DatabaseConnection.handleErrors(conn);
380: messages
381: .add("Unable to modify database schema to support IPv6. Please see UPGRADE.txt for further details on this optional modification."
382: + e.getMessage());
383: // do not throw this error and halt the upgrade process - changing the column size
384: // is not required for systems that have already been successfully installed, it
385: // is simply being done to keep new installs consistent with existing installs.
386: logger
387: .info(
388: "Failure while updating database for IPv6 support. See UPGRADE.txt for instructions on how to manually complete this optional step.",
389: e);
390: } finally {
391: DatabaseConnection.closeConnection(conn);
392: }
393: return messages;
394: }
395: }
|