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