001: /*
002: * File : $Source: /usr/local/cvs/opencms/src-setup/org/opencms/setup/update6to7/generic/CmsUpdateDBProjectId.java,v $
003: * Date : $Date: 2008-02-27 12:05:35 $
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.file.CmsProject;
035: import org.opencms.security.CmsOrganizationalUnit;
036: import org.opencms.setup.CmsSetupDBWrapper;
037: import org.opencms.setup.CmsSetupDb;
038: import org.opencms.setup.update6to7.A_CmsUpdateDBPart;
039: import org.opencms.util.CmsUUID;
040:
041: import java.io.IOException;
042: import java.sql.Date;
043: import java.sql.ResultSetMetaData;
044: import java.sql.SQLException;
045: import java.util.ArrayList;
046: import java.util.Arrays;
047: import java.util.Collections;
048: import java.util.HashMap;
049: import java.util.Iterator;
050: import java.util.List;
051: import java.util.Map;
052:
053: /**
054: * This class updates the project ids from integer values to CmsUUIDs in all existing database tables.<p>
055: *
056: * It creates new UUIDs for each existing project and stores it into a temporary table.<p>
057: *
058: * For each table using a project id a new column for the UUID is added and the according data is transferred.<p>
059: * After that the original indexes and the column for the project id index is dropped and the new column with the
060: * project uuid becomes the primary key.<p>
061: *
062: * @author Roland Metzler
063: *
064: * @version $Revision: 1.2 $
065: *
066: * @since 7.0.0
067: */
068: public class CmsUpdateDBProjectId extends A_CmsUpdateDBPart {
069:
070: /** Constant for the sql column PROJECT_ID.<p> */
071: protected static final String COLUMN_PROJECT_ID = "PROJECT_ID";
072:
073: /** Constant for the sql query to use the column PROJECT_LASTMODIFIED.<p> */
074: protected static final String COLUMN_PROJECT_LASTMODIFIED = "PROJECT_LASTMODIFIED";
075:
076: /** Constant for the sql column PROJECT_UUID.<p> */
077: protected static final String COLUMN_PROJECT_UUID = "PROJECT_UUID";
078:
079: /** Constant for the sql column TEMP_PROJECT_UUID.<p> */
080: protected static final String COLUMN_TEMP_PROJECT_UUID = "TEMP_PROJECT_UUID";
081:
082: /** Constant for the table name of the CMS_HISTORY_PROJECTS table.<p> */
083: protected static final String HISTORY_PROJECTS_TABLE = "CMS_HISTORY_PROJECTS";
084:
085: /** Constant for the sql query to add a new primary key.<p> */
086: protected static final String QUERY_ADD_PRIMARY_KEY = "Q_ADD_PRIMARY_KEY";
087:
088: /** Constant for the sql query to add a new column to the table.<p> */
089: protected static final String QUERY_ADD_TEMP_UUID_COLUMN = "Q_ADD_COLUMN";
090:
091: /** Constant for the sql query to create the new CMS_HISTORY_PROJECTS table.<p> */
092: protected static final String QUERY_CREATE_HISTORY_PROJECTS_TABLE = "Q_CREATE_HISTORY_PROJECTS_TABLE";
093:
094: /** Constant for the sql query to create the temporary table.<p> */
095: protected static final String QUERY_CREATE_TEMP_TABLE_UUIDS = "Q_CREATE_TEMPORARY_TABLE_UUIDS";
096:
097: /** Constant for the sql query to describe the given table.<p> */
098: protected static final String QUERY_DESCRIBE_TABLE = "Q_DESCRIBE_TABLE";
099:
100: /** Constant for the sql query to read max publish tag.<p> */
101: protected static final String QUERY_READ_MAX_PUBTAG = "Q_READ_MAX_PUBTAG";
102:
103: /** Constant for the replacement in the SQL query for the columnname.<p> */
104: protected static final String REPLACEMENT_COLUMN = "${column}";
105:
106: /** Constant for the replacement in the SQL query for the new columnname.<p> */
107: protected static final String REPLACEMENT_NEW_COLUMN = "${newcolumn}";
108:
109: /** Constant for the replacement in the SQL query for old id to update.<p> */
110: protected static final String REPLACEMENT_OLDID = "${oldid}";
111:
112: /** Constant for the replacement in the SQL query for the primary key.<p> */
113: protected static final String REPLACEMENT_PRIMARY_KEY = "${primarykeycolumn}";
114:
115: /** Constant for the replacement in the SQL query for the tablename.<p> */
116: protected static final String REPLACEMENT_TABLENAME = "${tablename}";
117:
118: /** Array of the online and offline resources tables.<p> */
119: protected static final String[] RESOURCE_TABLES = {
120: "CMS_OFFLINE_RESOURCES", "CMS_ONLINE_RESOURCES" };
121:
122: /** Arraylist for the online and offline resources tables that shall be updated.<p> */
123: protected static final List RESOURCES_TABLES_LIST = Collections
124: .unmodifiableList(Arrays.asList(RESOURCE_TABLES));
125:
126: /** Array of the tables that are to be updated.<p> */
127: protected static final String[] TABLES = { "CMS_OFFLINE_RESOURCES",
128: "CMS_ONLINE_RESOURCES", "CMS_PROJECTRESOURCES",
129: "CMS_PROJECTS" };
130:
131: /** Arraylist for the tables that shall be updated.<p> */
132: protected static final List TABLES_LIST = Collections
133: .unmodifiableList(Arrays.asList(TABLES));
134:
135: /** Constant for the temporary UUID column in the tables.<p> */
136: protected static final String TEMP_UUID_COLUMN = "TEMP_PROJECT_UUID";
137:
138: /** Constant for the name of temporary table containing the project ids and uuids.<p> */
139: protected static final String TEMPORARY_TABLE_NAME = "TEMP_PROJECT_UUIDS";
140:
141: /** Constant for the sql primary key of the CMS_PROJECTRESOURCES table.<p> */
142: private static final String COLUMN_PROJECT_ID_RESOURCE_PATH = "PROJECT_ID,RESOURCE_PATH(255)";
143:
144: /** Constant for the sql query to drop a given column.<p> */
145: private static final String QUERY_DROP_COLUMN = "Q_DROP_COLUMN";
146:
147: /** Constant for the sql query to get the project ids.<p> */
148: private static final String QUERY_GET_PROJECT_IDS = "Q_SELECT_PROJECT_IDS";
149:
150: /** Constant for the sql query to get the uuids and project ids.<p> */
151: private static final String QUERY_GET_UUIDS = "Q_SELECT_UUIDS";
152:
153: /** Constant for the sql query to insert the data into the CMS_HISTORY_PROJECTS table.<p> */
154: private static final String QUERY_INSERT_CMS_HISTORY_TABLE = "Q_INSERT_CMS_HISTORY_TABLE";
155:
156: /** Constant for the sql query to insert a pair of values to the temp table.<p> */
157: private static final String QUERY_INSERT_UUIDS = "Q_INSERT_UUIDS_TEMP_TABLE";
158:
159: /** Constant for the SQL query properties.<p> */
160: private static final String QUERY_PROPERTY_FILE = "generic/cms_projectid_queries.properties";
161:
162: /** Constant for the sql query to read the id of the administrators group.<p> */
163: private static final String QUERY_READ_ADMIN_GROUP = "Q_READ_ADMIN_GROUP";
164:
165: /** Constant for the sql query to read the id of the admin user.<p> */
166: private static final String QUERY_READ_ADMIN_USER = "Q_READ_ADMIN_USER";
167:
168: /** Constant for the sql query to add a rename a column in the table.<p> */
169: private static final String QUERY_RENAME_COLUMN = "Q_RENAME_COLUMN";
170:
171: /** Constant for the sql query to count the hsitorical projects.<p> */
172: private static final String QUERY_SELECT_COUNT_HISTORY_TABLE = "Q_SELECT_COUNT_HISTORY_TABLE";
173:
174: /** Constant for the sql query to select the data from the CMS_BACKUP_PROJECTS table.<p> */
175: private static final String QUERY_SELECT_DATA_FROM_BACKUP_PROJECTS = "Q_SELECT_DATA_FROM_BACKUP_PROJECTS";
176:
177: /** Constant for the sql query to transfer the new uuids to the temporary column.<p> */
178: private static final String QUERY_TRANSFER_UUID = "Q_TRANSFER_UUID";
179:
180: /** Constant for the sql query to repair lost project ids.<p> */
181: private static final String QUERY_UPDATE_NULL_PROJECTID = "Q_UPDATE_NULL_PROJECTID";
182:
183: /**
184: * Constructor.<p>
185: *
186: * @throws IOException if the query properties cannot be read
187: */
188: public CmsUpdateDBProjectId() throws IOException {
189:
190: super ();
191: loadQueryProperties(QUERY_PROPERTIES_PREFIX
192: + QUERY_PROPERTY_FILE);
193: }
194:
195: /**
196: * Adds a new primary key to the given table.<p>
197: *
198: * @param dbCon the db connection interface
199: * @param tablename the table to add the primary key to
200: * @param primaryKey the new primary key
201: *
202: * @throws SQLException if something goes wrong
203: */
204: protected void addPrimaryKey(CmsSetupDb dbCon, String tablename,
205: String primaryKey) throws SQLException {
206:
207: System.out.println(new Exception().getStackTrace()[0]
208: .toString());
209: if (dbCon.hasTableOrColumn(tablename, null)) {
210: String query = readQuery(QUERY_ADD_PRIMARY_KEY);
211: Map replacer = new HashMap();
212: replacer.put(REPLACEMENT_TABLENAME, tablename);
213: replacer.put(REPLACEMENT_PRIMARY_KEY, primaryKey);
214: dbCon.updateSqlStatement(query, replacer, null);
215: } else {
216: System.out.println("table " + tablename
217: + " does not exists");
218: }
219: }
220:
221: /**
222: * Adds the new column for the uuids to a table.<p>
223: *
224: * @param dbCon the db connection interface
225: * @param tablename the table to add the column to
226: * @param column the new colum to add
227: *
228: * @throws SQLException if something goes wrong
229: */
230: protected void addUUIDColumnToTable(CmsSetupDb dbCon,
231: String tablename, String column) throws SQLException {
232:
233: System.out.println(new Exception().getStackTrace()[0]
234: .toString());
235: if (!dbCon.hasTableOrColumn(tablename, column)) {
236: String query = readQuery(QUERY_ADD_TEMP_UUID_COLUMN); // Get the query
237: // if the table is not one of the ONLINE or OFFLINE resources add the new column in the first position
238: if (!RESOURCES_TABLES_LIST.contains(tablename)) {
239: query += " FIRST";
240: }
241: Map replacer = new HashMap(); // Build the replacements
242: replacer.put(REPLACEMENT_TABLENAME, tablename);
243: replacer.put(REPLACEMENT_COLUMN, column);
244: dbCon.updateSqlStatement(query, replacer, null); // execute the query
245: } else {
246: System.out.println("column " + column + " in table "
247: + tablename + " already exists");
248: }
249: }
250:
251: /**
252: * Check if the column type of the project id is incorrect.<p>
253: *
254: * @param type the type of the column from the meta data
255: *
256: * @return true if the type is incorrect
257: */
258: protected boolean checkColumnTypeProjectId(int type) {
259:
260: return type == java.sql.Types.INTEGER;
261: }
262:
263: /**
264: * Creates the CMS_HISTORY_PROJECTS table if it does not exist yet.<p>
265: *
266: * @param dbCon the db connection interface
267: *
268: * @throws SQLException if soemthing goes wrong
269: */
270: protected void createHistProjectsTable(CmsSetupDb dbCon)
271: throws SQLException {
272:
273: System.out.println(new Exception().getStackTrace()[0]
274: .toString());
275: if (!dbCon.hasTableOrColumn(HISTORY_PROJECTS_TABLE, null)) {
276: String createStatement = readQuery(QUERY_CREATE_HISTORY_PROJECTS_TABLE);
277: dbCon.updateSqlStatement(createStatement, null, null);
278: transferDataToHistoryTable(dbCon);
279: } else {
280: System.out.println("table " + HISTORY_PROJECTS_TABLE
281: + " already exists");
282: }
283: }
284:
285: /**
286: * Creates the temp table for project ids if it does not exist yet.<p>
287: *
288: * @param dbCon the db connection interface
289: *
290: * @throws SQLException if soemthing goes wrong
291: */
292: protected void createTempTable(CmsSetupDb dbCon)
293: throws SQLException {
294:
295: System.out.println(new Exception().getStackTrace()[0]
296: .toString());
297: if (!dbCon.hasTableOrColumn(TEMPORARY_TABLE_NAME, null)) {
298: String createStatement = readQuery(QUERY_CREATE_TEMP_TABLE_UUIDS);
299: dbCon.updateSqlStatement(createStatement, null, null);
300: } else {
301: System.out.println("table " + TEMPORARY_TABLE_NAME
302: + " already exists");
303: }
304: }
305:
306: /**
307: * Returns the columns for the primary key of the project resources table.<p>
308: *
309: * @return the columns for the primary key of the project resources table
310: */
311: protected String getColumnProjectIdResourcePath() {
312:
313: return COLUMN_PROJECT_ID_RESOURCE_PATH;
314: }
315:
316: /**
317: * @see org.opencms.setup.update6to7.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
318: */
319: protected void internalExecute(CmsSetupDb dbCon)
320: throws SQLException {
321:
322: System.out.println(new Exception().getStackTrace()[0]
323: .toString());
324:
325: generateUUIDs(dbCon);
326:
327: createHistProjectsTable(dbCon);
328:
329: Map uuids = getUUIDs(dbCon); // Get the UUIDS
330:
331: /*
332: * Add the temporary column for the new UUIDs and fill it with data
333: */
334: for (Iterator it = TABLES_LIST.iterator(); it.hasNext();) {
335: String tablename = (String) it.next();
336:
337: if (needsUpdating(dbCon, tablename)) {
338: addUUIDColumnToTable(dbCon, tablename, TEMP_UUID_COLUMN);
339: boolean isInResourcesList = RESOURCES_TABLES_LIST
340: .contains(tablename);
341: // Add the new uuids
342: Iterator entries = uuids.entrySet().iterator();
343: while (entries.hasNext()) {
344: Map.Entry entry = (Map.Entry) entries.next();
345: if (entry.getKey() != null
346: && entry.getValue() != null) {
347: if (isInResourcesList) {
348: fillUUIDSColumn(dbCon, tablename,
349: TEMP_UUID_COLUMN, (String) entry
350: .getValue(),
351: COLUMN_PROJECT_LASTMODIFIED,
352: (String) entry.getKey());
353: } else {
354: fillUUIDSColumn(dbCon, tablename,
355: TEMP_UUID_COLUMN, (String) entry
356: .getValue(),
357: COLUMN_PROJECT_ID, (String) entry
358: .getKey());
359: }
360: }
361: }
362:
363: /*
364: * In this phase the primary keys or indexes are dropped and the old columns containing the
365: * old project ids are dropped. After that the temporary columns are renamed and the new
366: * indexes and primary keys are added.
367: */
368: if (isInResourcesList) {
369: // fix lost project ids
370: Map replacer = Collections.singletonMap(
371: "${tablename}", tablename);
372: List params = Collections.singletonList(CmsUUID
373: .getNullUUID().toString());
374: String query = readQuery(QUERY_UPDATE_NULL_PROJECTID);
375: dbCon.updateSqlStatement(query, replacer, params);
376:
377: // Drop the column PROJECT_LASTMODIFIED
378: dropColumn(dbCon, tablename,
379: COLUMN_PROJECT_LASTMODIFIED);
380: // rename the column TEMP_PROJECT_UUID to PROJECT_LASTMODIFIED
381: renameColumn(dbCon, tablename,
382: COLUMN_TEMP_PROJECT_UUID,
383: COLUMN_PROJECT_LASTMODIFIED);
384: } else {
385: // drop the columns
386: dropColumn(dbCon, tablename, COLUMN_PROJECT_ID);
387:
388: // rename the column TEMP_PROJECT_UUID to PROJECT_ID
389: renameColumn(dbCon, tablename,
390: COLUMN_TEMP_PROJECT_UUID, COLUMN_PROJECT_ID);
391:
392: // add the new primary key
393: if (tablename.equals("CMS_PROJECTRESOURCES")) {
394: addPrimaryKey(dbCon, tablename,
395: getColumnProjectIdResourcePath());
396: }
397: if (tablename.equals("CMS_PROJECTS")) {
398: addPrimaryKey(dbCon, tablename,
399: COLUMN_PROJECT_ID);
400: }
401: }
402: } else {
403: System.out.println("table " + tablename
404: + " does not need to be updated");
405: }
406: }
407:
408: CmsSetupDBWrapper db = null;
409: boolean update = false;
410: try {
411: db = dbCon.executeSqlStatement(
412: readQuery(QUERY_SELECT_COUNT_HISTORY_TABLE), null);
413:
414: if (db.getResultSet().next()) {
415: if (db.getResultSet().getInt("COUNT") <= 0) {
416: update = true;
417: }
418: }
419: } finally {
420: if (db != null) {
421: db.close();
422: }
423: }
424: if (update) {
425: System.out.println("table " + HISTORY_PROJECTS_TABLE
426: + " has no content, create a dummy entry");
427:
428: CmsUUID userId = CmsUUID.getNullUUID();
429: try {
430: db = dbCon.executeSqlStatement(
431: readQuery(QUERY_READ_ADMIN_USER), null);
432: if (db.getResultSet().next()) {
433: userId = new CmsUUID(db.getResultSet().getString(1));
434: }
435: } finally {
436: if (db != null) {
437: db.close();
438: }
439: }
440: CmsUUID groupId = CmsUUID.getNullUUID();
441: try {
442: db = dbCon.executeSqlStatement(
443: readQuery(QUERY_READ_ADMIN_GROUP), null);
444: if (db.getResultSet().next()) {
445: groupId = new CmsUUID(db.getResultSet()
446: .getString(1));
447: }
448: } finally {
449: if (db != null) {
450: db.close();
451: }
452: }
453: // read publish tag
454: int pubTag = 1;
455: String query = readQuery(QUERY_READ_MAX_PUBTAG);
456: try {
457: db = dbCon.executeSqlStatement(query, null);
458: if (db.getResultSet().next()) {
459: pubTag = db.getResultSet().getInt(1);
460: }
461: } finally {
462: if (db != null) {
463: db.close();
464: }
465: }
466:
467: List params = new ArrayList();
468: params.add(new CmsUUID().toString());
469: params.add("updateWizardDummyProject");
470: params.add("dummy project just for having an entry");
471: params.add(new Integer(1));
472: params.add(userId.toString());
473: params.add(groupId.toString());
474: params.add(groupId.toString());
475: params.add(new Long(System.currentTimeMillis()));
476: params.add(new Integer(pubTag));
477: params.add(new Long(System.currentTimeMillis()));
478: params.add(userId.toString());
479: params.add(CmsOrganizationalUnit.SEPARATOR);
480:
481: query = readQuery(QUERY_INSERT_CMS_HISTORY_TABLE);
482: dbCon.updateSqlStatement(query, null, params);
483: } else {
484: System.out.println("table " + HISTORY_PROJECTS_TABLE
485: + " has content");
486: }
487: }
488:
489: /**
490: * Checks if the given table needs an update of the uuids.<p>
491: *
492: * @param dbCon the db connection interface
493: * @param tablename the table to check
494: *
495: * @return true if the project ids are not yet updated, false if nothing needs to be done
496: *
497: * @throws SQLException if something goes wrong
498: */
499: protected boolean needsUpdating(CmsSetupDb dbCon, String tablename)
500: throws SQLException {
501:
502: System.out.println(new Exception().getStackTrace()[0]
503: .toString());
504: boolean result = true;
505:
506: String query = readQuery(QUERY_DESCRIBE_TABLE);
507: Map replacer = new HashMap();
508: replacer.put(REPLACEMENT_TABLENAME, tablename);
509: CmsSetupDBWrapper db = null;
510:
511: try {
512: db = dbCon.executeSqlStatement(query, replacer);
513:
514: while (db.getResultSet().next()) {
515: String fieldname = db.getResultSet().getString("Field");
516: if (fieldname.equals(COLUMN_PROJECT_ID)
517: || fieldname
518: .equals(COLUMN_PROJECT_LASTMODIFIED)) {
519: try {
520: String fieldtype = db.getResultSet().getString(
521: "Type");
522: // If the type is varchar then no update needs to be done.
523: if (fieldtype.indexOf("varchar") > 0) {
524: return false;
525: }
526: } catch (SQLException e) {
527: result = true;
528: }
529: }
530: }
531: } finally {
532: if (db != null) {
533: db.close();
534: }
535: }
536: return result;
537: }
538:
539: /**
540: * Transfers the data from the CMS_BACKUP_PROJECTS to the CMS_HISTORY_PROJECTS table.<p>
541: *
542: * The datetime type for the column PROJECT_PUBLISHDATE is converted to the new long value.<p>
543: *
544: * @param dbCon the db connection interface
545: *
546: * @throws SQLException if something goes wrong
547: */
548: protected void transferDataToHistoryTable(CmsSetupDb dbCon)
549: throws SQLException {
550:
551: if (!isKeepHistory()) {
552: return;
553: }
554: System.out.println(new Exception().getStackTrace()[0]
555: .toString());
556: // Get the data from the CMS_BACKUP table
557: String query = readQuery(QUERY_SELECT_DATA_FROM_BACKUP_PROJECTS);
558: CmsSetupDBWrapper db = null;
559: try {
560: db = dbCon.executeSqlStatement(query, null);
561:
562: String insertQuery = readQuery(QUERY_INSERT_CMS_HISTORY_TABLE);
563: while (db.getResultSet().next()) {
564: // Add the values to be inserted into the CMS_HISTORY_PROJECTS table
565: List params = new ArrayList();
566: params.add(db.getResultSet().getString("PROJECT_UUID"));
567: params.add(db.getResultSet().getString("PROJECT_NAME"));
568: params.add(db.getResultSet().getString(
569: "PROJECT_DESCRIPTION"));
570: params.add(new Integer(db.getResultSet().getInt(
571: "PROJECT_TYPE")));
572: params.add(db.getResultSet().getString("USER_ID"));
573: params.add(db.getResultSet().getString("GROUP_ID"));
574: params.add(db.getResultSet().getString(
575: "MANAGERGROUP_ID"));
576: params.add(new Long(db.getResultSet().getLong(
577: "DATE_CREATED")));
578: params.add(new Integer(db.getResultSet().getInt(
579: "PUBLISH_TAG")));
580: Date date = db.getResultSet().getDate(
581: "PROJECT_PUBLISHDATE");
582: params.add(new Long(date.getTime()));
583: params.add(db.getResultSet().getString(
584: "PROJECT_PUBLISHED_BY"));
585: params.add(db.getResultSet().getString("PROJECT_OU"));
586:
587: dbCon.updateSqlStatement(insertQuery, null, params);
588: }
589: } finally {
590: if (db != null) {
591: db.close();
592: }
593: }
594:
595: }
596:
597: /**
598: * Drops the column of the given table.<p>
599: *
600: * @param dbCon the db connection interface
601: * @param tablename the table in which the columns shall be dropped
602: * @param column the column to drop
603: *
604: * @throws SQLException if something goes wrong
605: */
606: private void dropColumn(CmsSetupDb dbCon, String tablename,
607: String column) throws SQLException {
608:
609: System.out.println(new Exception().getStackTrace()[0]
610: .toString());
611: if (dbCon.hasTableOrColumn(tablename, column)) {
612: String query = readQuery(QUERY_DROP_COLUMN);
613: Map replacer = new HashMap();
614: replacer.put(REPLACEMENT_TABLENAME, tablename);
615: replacer.put(REPLACEMENT_COLUMN, column);
616: dbCon.updateSqlStatement(query, replacer, null);
617: } else {
618: System.out.println("column " + column + " in table "
619: + tablename + " does not exist");
620: }
621: }
622:
623: /**
624: * Updates the given table with the new UUID value.<p>
625: *
626: * @param dbCon the db connection interface
627: * @param tablename the table to update
628: * @param column the column to update
629: * @param newvalue the new value to insert
630: * @param oldid the old id to compare the old value to
631: * @param tempValue the old value in the temporary table
632: *
633: * @throws SQLException if something goes wrong
634: */
635: private void fillUUIDSColumn(CmsSetupDb dbCon, String tablename,
636: String column, String newvalue, String oldid,
637: String tempValue) throws SQLException {
638:
639: System.out.println(new Exception().getStackTrace()[0]
640: .toString());
641: if (dbCon.hasTableOrColumn(tablename, column)) {
642: String query = readQuery(QUERY_TRANSFER_UUID);
643: Map replacer = new HashMap();
644: replacer.put(REPLACEMENT_TABLENAME, tablename);
645: replacer.put(REPLACEMENT_COLUMN, column);
646: replacer.put(REPLACEMENT_OLDID, oldid);
647: List params = new ArrayList();
648: params.add(newvalue);
649: params.add(new Integer(tempValue)); // Change type to integer
650:
651: dbCon.updateSqlStatement(query, replacer, params);
652: } else {
653: System.out.println("column " + column + " in table "
654: + tablename + " does not exists");
655: }
656: }
657:
658: /**
659: * Generates the new UUIDs for the project ids.<p>
660: * The new uuids are stored in the temporary table.<p>
661: *
662: * @param dbCon the db connection interface
663: *
664: * @throws SQLException if something goes wrong
665: */
666: private void generateUUIDs(CmsSetupDb dbCon) throws SQLException {
667:
668: System.out.println(new Exception().getStackTrace()[0]
669: .toString());
670: String query = readQuery(QUERY_GET_PROJECT_IDS);
671:
672: CmsSetupDBWrapper db = null;
673: try {
674: db = dbCon.executeSqlStatement(query, null);
675: ResultSetMetaData metaData = db.getResultSet()
676: .getMetaData();
677: // Check the type of the column if it is integer, then create the new uuids
678: int columnType = metaData.getColumnType(1);
679: if (checkColumnTypeProjectId(columnType)) {
680: if (!dbCon.hasTableOrColumn(TEMPORARY_TABLE_NAME, null)) {
681: createTempTable(dbCon);
682:
683: String updateQuery = readQuery(QUERY_INSERT_UUIDS);
684: List params = new ArrayList();
685: // Get the project id and insert it with a new uuid into the temp table
686: boolean hasNullId = false;
687: while (db.getResultSet().next()) {
688: int id = db.getResultSet().getInt("PROJECT_ID");
689: params.add(new Integer(id)); // Add the number
690: CmsUUID uuid = new CmsUUID();
691:
692: // Check for 0 project id
693: if (id == 0) {
694: hasNullId = true;
695: uuid = CmsUUID.getNullUUID();
696: }
697: // Check for the online project
698: if (id == 1) {
699: uuid = CmsProject.ONLINE_PROJECT_ID;
700: }
701: params.add(uuid.toString()); // Add the uuid
702:
703: // Insert the values to the temp table
704: dbCon.updateSqlStatement(updateQuery, null,
705: params);
706:
707: params.clear();
708: }
709:
710: // If no project id with value 0 was found
711: if (!hasNullId) {
712: params.add(new Integer(0));
713: params.add(CmsUUID.getNullUUID().toString());
714: dbCon.updateSqlStatement(updateQuery, null,
715: params);
716: }
717: } else {
718: System.out.println("table " + TEMPORARY_TABLE_NAME
719: + " already exists");
720: }
721: }
722: } finally {
723: if (db != null) {
724: db.close();
725: }
726: }
727: }
728:
729: /**
730: * Gets the UUIDs from the temporary table TEMP_CMS_UUIDS.<p>
731: *
732: * @param dbCon the db connection interface
733: *
734: * @return a map with the old project ids and the new uuids
735: *
736: * @throws SQLException if something goes wrong
737: */
738: private Map getUUIDs(CmsSetupDb dbCon) throws SQLException {
739:
740: System.out.println(new Exception().getStackTrace()[0]
741: .toString());
742: Map result = new HashMap();
743:
744: String query = readQuery(QUERY_GET_UUIDS);
745: CmsSetupDBWrapper db = null;
746: try {
747: db = dbCon.executeSqlStatement(query, null);
748: while (db.getResultSet().next()) {
749: String key = Integer.toString(db.getResultSet().getInt(
750: COLUMN_PROJECT_ID));
751: String value = db.getResultSet().getString(
752: COLUMN_PROJECT_UUID);
753:
754: result.put(key, value);
755: }
756: } finally {
757: if (db != null) {
758: db.close();
759: }
760: }
761: return result;
762: }
763:
764: /**
765: * Renames the column of the given table the new name.<p>
766: *
767: * @param dbCon the db connection interface
768: * @param tablename the table in which the column shall be renamed
769: * @param oldname the old name of the column
770: * @param newname the new name of the column
771: *
772: * @throws SQLException if something goes wrong
773: */
774: private void renameColumn(CmsSetupDb dbCon, String tablename,
775: String oldname, String newname) throws SQLException {
776:
777: System.out.println(new Exception().getStackTrace()[0]
778: .toString());
779: if (dbCon.hasTableOrColumn(tablename, oldname)) {
780: String query = readQuery(QUERY_RENAME_COLUMN);
781: Map replacer = new HashMap();
782: replacer.put(REPLACEMENT_TABLENAME, tablename);
783: replacer.put(REPLACEMENT_COLUMN, oldname);
784: replacer.put(REPLACEMENT_NEW_COLUMN, newname);
785:
786: dbCon.updateSqlStatement(query, replacer, null);
787: } else {
788: System.out.println("column " + oldname + " in table "
789: + tablename + " not found exists");
790: }
791: }
792: }
|