001: /*
002: * File : $Source: /usr/local/cvs/opencms/src-setup/org/opencms/setup/update6to7/generic/CmsUpdateDBAlterTables.java,v $
003: * Date : $Date: 2008-02-27 12:05:34 $
004: * Version: $Revision: 1.2 $
005: *
006: * This library is part of OpenCms -
007: * the Open Source Content Management System
008: *
009: * Copyright (c) 2002 - 2008 Alkacon Software GmbH (http://www.alkacon.com)
010: *
011: * This library is free software; you can redistribute it and/or
012: * modify it under the terms of the GNU Lesser General Public
013: * License as published by the Free Software Foundation; either
014: * version 2.1 of the License, or (at your option) any later version.
015: *
016: * This library is distributed in the hope that it will be useful,
017: * but WITHOUT ANY WARRANTY; without even the implied warranty of
018: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
019: * Lesser General Public License for more details.
020: *
021: * For further information about Alkacon Software GmbH, please see the
022: * company website: http://www.alkacon.com
023: *
024: * For further information about OpenCms, please see the
025: * project website: http://www.opencms.org
026: *
027: * You should have received a copy of the GNU Lesser General Public
028: * License along with this library; if not, write to the Free Software
029: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
030: */
031:
032: package org.opencms.setup.update6to7.generic;
033:
034: import org.opencms.setup.CmsSetupDBWrapper;
035: import org.opencms.setup.CmsSetupDb;
036: import org.opencms.setup.update6to7.A_CmsUpdateDBPart;
037:
038: import java.io.IOException;
039: import java.sql.SQLException;
040: import java.util.ArrayList;
041: import java.util.Arrays;
042: import java.util.Collections;
043: import java.util.HashMap;
044: import java.util.Iterator;
045: import java.util.List;
046:
047: /**
048: * This class makes the remaining changes to some tables in order to update them.<p>
049: *
050: * The following tables will be altered
051: *
052: * CMS_ONLINE/OFFLINE_PROPERTYDEF Add the TYPE column
053: * CMS_ONLINE/OFFLINE_RESOURCES Add the columns DATE_CONTENT and RESOURCE_VERSION
054: * CMS_ONLINE/OFFLINE_STRUCTURE Add the column STRUCTURE_VERSION
055: * CMS_PROJECTS Drop the column TASK_ID and change the size for the project name
056: *
057: * @author Roland Metzler
058: *
059: * @version $Revision: 1.2 $
060: *
061: * @since 7.0.0
062: */
063: public class CmsUpdateDBAlterTables extends A_CmsUpdateDBPart {
064:
065: /** Constant array with the queries for the CMS_ONLINE_CONTENTS table.<p> */
066: protected static final String[] CMS_OFFLINE_CONTENTS_QUERIES = { "Q_OFFLINE_CONTENTS_DROP_COLUMN" };
067:
068: /** Constant ArrayList of the queries of the CMS_OFFLINE table.<p> */
069: protected static final List CMS_OFFLINE_CONTENTS_QUERIES_LIST = Collections
070: .unmodifiableList(Arrays
071: .asList(CMS_OFFLINE_CONTENTS_QUERIES));
072:
073: /** Constant array with the ONLINE and OFFLINE PROPERTYDEF tables.<p> */
074: protected static final String[] CMS_PROPERTYDEF = {
075: "CMS_OFFLINE_PROPERTYDEF", "CMS_ONLINE_PROPERTYDEF" };
076:
077: /** Constant ArrayList of the two PROPERTYDEF tables.<p> */
078: protected static final List CMS_PROPERTYDEF_LIST = Collections
079: .unmodifiableList(Arrays.asList(CMS_PROPERTYDEF));
080:
081: /** Constant array with the ONLINE and OFFLINE RESOURCES tables.<p> */
082: protected static final String[] CMS_RESOURCES = {
083: "CMS_OFFLINE_RESOURCES", "CMS_ONLINE_RESOURCES" };
084:
085: /** Constant ArrayList of the two RESOURCES tables.<p> */
086: protected static final List CMS_RESOURCES_LIST = Collections
087: .unmodifiableList(Arrays.asList(CMS_RESOURCES));
088:
089: /** Constant array with the ONLINE and OFFLINE STRUCTURE tables.<p> */
090: protected static final String[] CMS_STRUCTURE = {
091: "CMS_OFFLINE_STRUCTURE", "CMS_ONLINE_STRUCTURE" };
092:
093: /** Constant ArrayList of the two PROPERTYDEF tables.<p> */
094: protected static final List CMS_STRUCTURE_LIST = Collections
095: .unmodifiableList(Arrays.asList(CMS_STRUCTURE));
096:
097: /** Constant for the column CONTENT_ID of the table CMS_OFFLINE_CONTENTS.<p> */
098: protected static final String COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID = "CONTENT_ID";
099:
100: /** Constant for the column PROPERTYDEF_TYPE of the PROPERTYDEF tables.<p> */
101: protected static final String COLUMN_CMS_PROPERTYDEF_TYPE = "PROPERTYDEF_TYPE";
102:
103: /** Constant for the column STRUCTURE_VERSION in the STRUCTURE tables.<p> */
104: protected static final String COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION = "STRUCTURE_VERSION";
105:
106: /** Constant for the column PROJECT_NAME of the CMS_PROJECTS table.<p> */
107: protected static final String COLUMN_PROJECTS_PROJECT_NAME = "PROJECT_NAME";
108:
109: /** Constant for the column TASK_ID of the CMS_PROJECTS table.<p> */
110: protected static final String COLUMN_PROJECTS_TASK_ID = "TASK_ID";
111:
112: /** Constant for the new column DATE_CONTENT of the CMS_RESOURCES tables.<p> */
113: protected static final String COLUMN_RESOURCES_DATE_CONTENT = "DATE_CONTENT";
114:
115: /** Constant for the new column RESOURCE_VERSION of the CMS_RESOURCES tables.<p> */
116: protected static final String COLUMN_RESOURCES_RESOURCE_VERSION = "RESOURCE_VERSION";
117:
118: /** Constant for the sql replacement of the tablename.<p> */
119: protected static final String REPLACEMENT_TABLENAME = "${tablename}";
120:
121: /** Constant for the table name CMS_OFFLINE_CONTENTS.<p> */
122: protected static final String TABLE_CMS_OFFLINE_CONTENTS = "CMS_OFFLINE_CONTENTS";
123:
124: /** Constant for the table name CMS_PROJECTS.<p> */
125: protected static final String TABLE_CMS_PROJECTS = "CMS_PROJECTS";
126:
127: /** Constant for the sql query to change the colum PROJECT_NAME.<p> */
128: private static final String QUERY_CMS_PROJECTS_CHANGE_PROJECT_NAME = "Q_CMS_PROJECTS_CHANGE_PROJECT_NAME_SIZE";
129:
130: /** Constant for the sql query to drop the TASK_ID from the CMS_PROJECTS table.<p> */
131: private static final String QUERY_CMS_PROJECTS_DROP_TASK_ID = "Q_CMS_PROJECTS_DROP_TASK_ID";
132:
133: /** Constant for the sql query to change the colum PROJECT_NAME.<p> */
134: private static final String QUERY_CMS_PROJECTS_UPDATE_PROJECT_FLAGS = "Q_CMS_PROJECTS_UPDATE_PROJECT_FLAGS";
135:
136: /** Constant for the sql query to add the STRUCTURE_VERSION column to the STRUCTURE tables.<p> */
137: private static final String QUERY_CMS_STRUCTURE_ADD_STRUCTURE_VERSION = "Q_CMS_STRUCTURE_ADD_STRUCTURE_VERSION";
138:
139: /** Constant for the SQL query properties.<p> */
140: private static final String QUERY_PROPERTY_FILE = "generic/cms_alter_remaining_queries.properties";
141:
142: /** Constant for the sql query to add the PROPERTYDEF_TYPE to the PROPERTYDEF tables.<p> */
143: private static final String QUERY_PROPERTYDEF_TYPE = "Q_CMS_PROPERTYDEF";
144:
145: /** Constant for the sql query to select the correct structure versions.<p> */
146: private static final String QUERY_SELECT_CMS_STRUCTURE_VERSION = "Q_SELECT_CMS_STRUCTURE_VERSION";
147:
148: /** Constant for the sql query to add the DATE_CONTENT column to the CMS_RESOURCES tables.<p> */
149: private static final String QUERY_UPDATE_RESOURCES_DATE_CONTENT = "Q_UPDATE_RESOURCES_DATE_CONTENT";
150:
151: /** Constant for the sql query to add the RESOURCE_VERSIOn to the CMS_RESOURCES tables.<p> */
152: private static final String QUERY_UPDATE_RESOURCES_RESOURCE_VERSION = "Q_UPDATE_RESOURCES_RESOURCE_VERSION";
153:
154: /** Constant for the sql query to update the structure version in each row.<p> */
155: private static final String QUERY_UPDATE_STRUCTURE_VERSION = "Q_UPDATE_STRUCTURE_VERSION";
156:
157: /**
158: * Default constructor.<p>
159: *
160: * @throws IOException if the default sql queries property file could not be read
161: */
162: public CmsUpdateDBAlterTables() throws IOException {
163:
164: super ();
165: loadQueryProperties(QUERY_PROPERTIES_PREFIX
166: + QUERY_PROPERTY_FILE);
167: }
168:
169: /**
170: * @see org.opencms.setup.update6to7.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
171: */
172: public void internalExecute(CmsSetupDb dbCon) throws SQLException {
173:
174: System.out.println(new Exception().getStackTrace()[0]
175: .toString());
176: // Update the CMS_OFFLINE_CONTENTS table
177: // drop column content_id
178: if (dbCon.hasTableOrColumn(TABLE_CMS_OFFLINE_CONTENTS,
179: COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID)) {
180: for (Iterator it = CMS_OFFLINE_CONTENTS_QUERIES_LIST
181: .iterator(); it.hasNext();) {
182: String query = readQuery((String) it.next());
183: dbCon.updateSqlStatement(query, null, null);
184: }
185: } else {
186: System.out.println("no column "
187: + COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID
188: + " in table " + TABLE_CMS_OFFLINE_CONTENTS);
189: }
190:
191: // Update the CMS_ONLINE/OFFLINE_PROPERTYDEF tables
192: // Add the column PROPERTYDEF_TYPE
193: for (Iterator it = CMS_PROPERTYDEF_LIST.iterator(); it
194: .hasNext();) {
195: String table = (String) it.next();
196: if (!dbCon.hasTableOrColumn(table,
197: COLUMN_CMS_PROPERTYDEF_TYPE)) {
198: String query = readQuery(QUERY_PROPERTYDEF_TYPE);
199: HashMap replacer = new HashMap();
200: replacer.put(REPLACEMENT_TABLENAME, table);
201: dbCon.updateSqlStatement(query, replacer, null);
202: replacer.clear();
203: } else {
204: System.out.println("column "
205: + COLUMN_CMS_PROPERTYDEF_TYPE + " in table "
206: + table + " already exists");
207: }
208: }
209:
210: // Update the ONLINE/OFFLINE_STRUCTURE
211: // Add the STRUCTURE_VERSION
212: for (Iterator it = CMS_STRUCTURE_LIST.iterator(); it.hasNext();) {
213: String table = (String) it.next();
214: // Add the column if needed
215: if (!dbCon.hasTableOrColumn(table,
216: COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION)) {
217: String addColumn = readQuery(QUERY_CMS_STRUCTURE_ADD_STRUCTURE_VERSION);
218: // Add the column
219: HashMap replacer = new HashMap();
220: replacer.put(REPLACEMENT_TABLENAME, table);
221: dbCon.updateSqlStatement(addColumn, replacer, null);
222:
223: // Update the entries of the newly created column
224: String structureVersion = readQuery(QUERY_SELECT_CMS_STRUCTURE_VERSION);
225: CmsSetupDBWrapper db = null;
226: try {
227: db = dbCon.executeSqlStatement(structureVersion,
228: replacer);
229: // update each row
230: while (db.getResultSet().next()) {
231: String updateQuery = readQuery(QUERY_UPDATE_STRUCTURE_VERSION);
232: String structureId = db.getResultSet()
233: .getString("STRUCTURE_ID");
234: int version = db.getResultSet().getInt(
235: "STRUCTURE_VERSION");
236: List params = new ArrayList();
237: params.add(new Integer(version)); // add the version
238: params.add(structureId);
239: dbCon.updateSqlStatement(updateQuery, replacer,
240: params);
241: }
242: } finally {
243: if (db != null) {
244: db.close();
245: }
246: }
247: } else {
248: System.out.println("column "
249: + COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION
250: + " in table " + table + " already exists");
251: }
252: } // end update structure_version
253:
254: // Drop the TASK_ID column from CMS_PROJECTS
255: if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS,
256: COLUMN_PROJECTS_TASK_ID)) {
257: String dropTaskId = readQuery(QUERY_CMS_PROJECTS_DROP_TASK_ID);
258: dbCon.updateSqlStatement(dropTaskId, null, null);
259: } else {
260: System.out.println("no column " + COLUMN_PROJECTS_TASK_ID
261: + " in table " + TABLE_CMS_PROJECTS);
262: }
263:
264: // Change the size of the project names
265: if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS,
266: COLUMN_PROJECTS_PROJECT_NAME)) {
267: String changeProjectName = readQuery(QUERY_CMS_PROJECTS_CHANGE_PROJECT_NAME);
268: dbCon.updateSqlStatement(changeProjectName, null, null);
269: } else {
270: System.out.println("no column "
271: + COLUMN_PROJECTS_PROJECT_NAME + " in table "
272: + TABLE_CMS_PROJECTS);
273: }
274:
275: // Update project flags for temporary projects
276: if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS, null)) {
277: String updateProjectFlags = readQuery(QUERY_CMS_PROJECTS_UPDATE_PROJECT_FLAGS);
278: dbCon.updateSqlStatement(updateProjectFlags, null, null);
279: } else {
280: System.out.println("table " + TABLE_CMS_PROJECTS
281: + " does not exists");
282: }
283:
284: // Update CMS_GROUPS and add the system roles
285:
286: // Update CMS_RESOURCES tables
287: for (Iterator it = CMS_RESOURCES_LIST.iterator(); it.hasNext();) {
288: String table = (String) it.next();
289: HashMap replacer = new HashMap();
290: replacer.put(REPLACEMENT_TABLENAME, table);
291: if (!dbCon.hasTableOrColumn(table,
292: COLUMN_RESOURCES_DATE_CONTENT)) {
293: String addDateContent = readQuery(QUERY_UPDATE_RESOURCES_DATE_CONTENT);
294: // add the DATE_CONTENT column
295: dbCon
296: .updateSqlStatement(addDateContent, replacer,
297: null);
298: } else {
299: System.out.println("column "
300: + COLUMN_RESOURCES_DATE_CONTENT + " in table "
301: + table + " already exists");
302: }
303:
304: if (!dbCon.hasTableOrColumn(table,
305: COLUMN_RESOURCES_RESOURCE_VERSION)) {
306: // add the RESOURCE_VERISION column
307: String addResourceVersion = readQuery(QUERY_UPDATE_RESOURCES_RESOURCE_VERSION);
308: dbCon.updateSqlStatement(addResourceVersion, replacer,
309: null);
310: } else {
311: System.out.println("column "
312: + COLUMN_RESOURCES_RESOURCE_VERSION
313: + " in table " + table + " already exists");
314: }
315: }
316: }
317: }
|