001: /* Copyright 2004 The JA-SIG Collaborative. All rights reserved.
002: * See license distributed with this file and
003: * available online at http://www.uportal.org/license.html
004: */
005:
006: package org.jasig.portal.tools;
007:
008: import java.sql.Connection;
009: import java.sql.PreparedStatement;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.sql.Statement;
013:
014: import org.apache.commons.logging.Log;
015: import org.apache.commons.logging.LogFactory;
016: import org.jasig.portal.RDBMServices;
017: import org.springframework.dao.DataAccessException;
018:
019: /**
020: * Adjusts stored layouts in 2.1 database to work in 2.2+.
021: * @author Susan Bramhall, susan.bramhall@yale.edu
022: * @version $Revision: 35664 $
023: */
024: public class DbConvert21 {
025:
026: private static final Log log = LogFactory.getLog(DbConvert21.class);
027:
028: public static void main(String[] args) {
029:
030: Statement stmt = null;
031: PreparedStatement stmtTest = null;
032: PreparedStatement testStructStmt = null;
033: Statement modifyStmt = null;
034: ResultSet rset = null;
035: ResultSet rsetTest = null;
036: Connection con = null;
037: int updateCount = 0;
038:
039: // the query to select all the layouts that need to be adjusted
040: String query = "select uul.USER_ID, uul.LAYOUT_ID, max(uls.struct_id)+100 as new_struct_id, "
041: + "init_struct_id as new_child_id from up_layout_struct uls, up_user_layout uul "
042: + "where uls.user_id=uul.user_id and uls.layout_id=uul.layout_id "
043: + "group by uul.user_id, uul.layout_id, init_struct_id";
044:
045: String testQuery = "select count(*) as ct from up_layout_struct where type='root' and user_id= ? and layout_id=?";
046: String testNextStructId = "SELECT NEXT_STRUCT_ID FROM UP_USER where user_id=? ";
047:
048: try {
049: RDBMServices.setGetDatasourceFromJndi(false); /*don't try jndi when not in web app */
050: con = RDBMServices.getConnection();
051:
052: if (RDBMServices.getDbMetaData().supportsTransactions())
053: con.setAutoCommit(false);
054:
055: // Create the JDBC statement
056: stmt = con.createStatement();
057:
058: // change stylesheet URIs to classpath reference for resource manager
059: try {
060: Statement ssModifyStmt = con.createStatement();
061: rset = stmt
062: .executeQuery("SELECT SS_ID, SS_URI, SS_DESCRIPTION_URI FROM UP_SS_STRUCT ");
063: String newSsUri, ssUri, ssDescUri, updateSsUri;
064:
065: while (rset.next()) {
066: int ssId = rset.getInt(1);
067: ssUri = rset.getString(2);
068: if (ssUri.startsWith("stylesheets/")) {
069: newSsUri = ssUri.substring("stylesheets/"
070: .length());
071: updateSsUri = "UPDATE UP_SS_STRUCT set SS_URI = '"
072: + newSsUri
073: + "' "
074: + "where SS_ID = "
075: + ssId;
076: ssModifyStmt.execute(updateSsUri);
077: log.debug("DbConvert21 update: " + updateSsUri);
078: }
079: ssDescUri = rset.getString(3);
080: if (ssDescUri.startsWith("stylesheets/")) {
081: newSsUri = ssDescUri.substring("stylesheets/"
082: .length());
083: updateSsUri = "UPDATE UP_SS_STRUCT set SS_DESCRIPTION_URI = '"
084: + newSsUri
085: + "' "
086: + "where SS_ID = "
087: + ssId;
088: ssModifyStmt.execute(updateSsUri);
089: log.debug("DbConvert21 update: " + updateSsUri);
090: }
091: }
092: rset = stmt
093: .executeQuery("SELECT SS_ID, SS_URI, SS_DESCRIPTION_URI FROM UP_SS_THEME ");
094: while (rset.next()) {
095: int ssId = rset.getInt(1);
096: ssUri = rset.getString(2);
097: if (ssUri.startsWith("stylesheets/")) {
098: newSsUri = ssUri.substring("stylesheets/"
099: .length());
100: updateSsUri = "UPDATE UP_SS_THEME set SS_URI = '"
101: + newSsUri
102: + "' "
103: + "where SS_ID = "
104: + ssId;
105: ssModifyStmt.execute(updateSsUri);
106: log.debug("DbConvert21 update: " + updateSsUri);
107: }
108: ssDescUri = rset.getString(3);
109: if (ssDescUri.startsWith("stylesheets/")) {
110: newSsUri = ssDescUri.substring("stylesheets/"
111: .length());
112: updateSsUri = "UPDATE UP_SS_THEME set SS_DESCRIPTION_URI = '"
113: + newSsUri
114: + "' "
115: + "where SS_ID = "
116: + ssId;
117: ssModifyStmt.execute(updateSsUri);
118: log.debug("DbConvert21 update: " + updateSsUri);
119: }
120:
121: }
122: } catch (SQLException se) {
123: System.err.println("Error updating stylesheet Uri");
124: se.printStackTrace();
125: } finally {
126: if (rset != null)
127: rset.close();
128: System.out.println("stylesheet references updated.");
129: }
130:
131: // update layouts to add new folder
132: try {
133: rset = stmt.executeQuery(query);
134: try {
135: // Create statements for modifications
136: // for updating the layout
137: modifyStmt = con.createStatement();
138: // to test if already modfied
139: stmtTest = con.prepareStatement(testQuery);
140: // to test if need to increment next struct id for user
141: testStructStmt = con
142: .prepareStatement(testNextStructId);
143:
144: // loop through returned results
145: while (rset.next()) {
146: int user_id = rset.getInt("USER_ID");
147: int layout_id = rset.getInt("LAYOUT_ID");
148: int new_struct_id = rset
149: .getInt("new_struct_id");
150: int new_child_id = rset.getInt("new_child_id");
151:
152: stmtTest.clearParameters();
153: stmtTest.setInt(1, user_id);
154: stmtTest.setInt(2, layout_id);
155: rsetTest = stmtTest.executeQuery();
156: if (rsetTest.next()
157: && rsetTest.getInt("ct") > 0) {
158: System.err
159: .println("DbConvert: root folder already exists. USER_ID "
160: + user_id
161: + ", LAYOUT_ID "
162: + layout_id + " ignored");
163: } else {
164: String insertString = "INSERT INTO UP_LAYOUT_STRUCT ( USER_ID, LAYOUT_ID, STRUCT_ID, "
165: + "NEXT_STRUCT_ID, CHLD_STRUCT_ID, NAME, TYPE, IMMUTABLE, UNREMOVABLE) VALUES ("
166: + user_id
167: + ", "
168: + layout_id
169: + ", "
170: + new_struct_id
171: + ", null, "
172: + new_child_id
173: + ", 'Root Folder', 'root', 'N', 'Y')";
174: modifyStmt.execute(insertString);
175: // DEBUG
176: log.debug("DbConvert inserted: "
177: + insertString);
178:
179: String updateString = "UPDATE UP_USER_LAYOUT set INIT_STRUCT_ID="
180: + new_struct_id
181: + " where user_id="
182: + user_id
183: + " and layout_id="
184: + layout_id;
185: modifyStmt.execute(updateString);
186: log.debug("DbConvert updated layout: "
187: + updateString);
188:
189: testStructStmt.clearParameters();
190: testStructStmt.setInt(1, user_id);
191: ResultSet testNext = testStructStmt
192: .executeQuery();
193: int newNext = new_struct_id + 1;
194: if (testNext.next()
195: && testNext.getInt(1) <= newNext) {
196: updateString = "UPDATE UP_USER set NEXT_STRUCT_ID = "
197: + newNext
198: + " where user_id="
199: + user_id;
200: modifyStmt.execute(updateString);
201: log
202: .debug("DbConvert updated next struct id : "
203: + updateString);
204: }
205:
206: log.debug("DbConvert updated: "
207: + updateString);
208: updateCount++;
209: }
210: }
211:
212: if (RDBMServices.getDbMetaData()
213: .supportsTransactions())
214: con.commit();
215:
216: } finally {
217: stmt.close();
218: modifyStmt.close();
219: if (stmtTest != null)
220: stmtTest.close();
221: }
222: } catch (Exception e) {
223: System.err
224: .println("Error attempting to update layouts.");
225: e.printStackTrace();
226: } finally {
227: rset.close();
228: if (rsetTest != null)
229: rsetTest.close();
230: }
231:
232: } catch (DataAccessException dae) {
233: // we know this was thrown by RDBMServices.getConnection().
234: System.err.println("Unable to get a database connection");
235: return;
236: } catch (Exception e) {
237: e.printStackTrace();
238: } finally {
239: try {
240: con.commit();
241: RDBMServices.releaseConnection(con);
242: } catch (Exception e) {
243: }
244: }
245:
246: System.out.println("DbConvert21 updated " + updateCount
247: + " user layouts");
248: return;
249:
250: }//end main
251:
252: }
|