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.postgresql; 029 030import org.opencms.setup.CmsSetupDBWrapper; 031import org.opencms.setup.CmsSetupDb; 032 033import java.io.IOException; 034import java.sql.SQLException; 035import java.util.HashMap; 036import java.util.Map; 037 038/** 039 * PostgreSQL implementation to update the project ids to uuids.<p> 040 * 041 * @since 7.0.2 042 */ 043public class CmsUpdateDBProjectId extends org.opencms.setup.db.update6to7.CmsUpdateDBProjectId { 044 045 /** Constant for the sql primary key of the CMS_PROJECTRESOURCES table.<p> */ 046 private static final String COLUMN_PROJECT_ID_RESOURCE_PATH = "PROJECT_ID,RESOURCE_PATH"; 047 048 /** Constant for the SQL query properties.<p> */ 049 private static final String QUERY_PROPERTY_FILE = "cms_projectid_queries.properties"; 050 051 /** Constant for the replacement in the sql query. */ 052 private static final String REPLACEMENT_TABLEINDEX_SPACE = "${indexTablespace}"; 053 054 /** 055 * Constructor.<p> 056 * 057 * @throws IOException if the sql queries properties file could not be read 058 */ 059 public CmsUpdateDBProjectId() 060 throws IOException { 061 062 super(); 063 loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE); 064 } 065 066 /** 067 * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#addPrimaryKey(org.opencms.setup.CmsSetupDb, java.lang.String, java.lang.String) 068 */ 069 @Override 070 protected void addPrimaryKey(CmsSetupDb dbCon, String tablename, String primaryKey) throws SQLException { 071 072 String indexTablespace = m_poolData.get("indexTablespace"); 073 074 System.out.println(new Exception().getStackTrace()[0].toString()); 075 if (dbCon.hasTableOrColumn(tablename, null)) { 076 String query = readQuery(QUERY_ADD_PRIMARY_KEY); 077 Map<String, String> replacer = new HashMap<String, String>(); 078 replacer.put(REPLACEMENT_TABLENAME, tablename); 079 replacer.put(REPLACEMENT_PRIMARY_KEY, primaryKey); 080 replacer.put(REPLACEMENT_TABLEINDEX_SPACE, indexTablespace); 081 dbCon.updateSqlStatement(query, replacer, null); 082 } else { 083 System.out.println("table " + tablename + " does not exists"); 084 } 085 } 086 087 /** 088 * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#addUUIDColumnToTable(org.opencms.setup.CmsSetupDb, java.lang.String, java.lang.String) 089 */ 090 @Override 091 protected void addUUIDColumnToTable(CmsSetupDb dbCon, String tablename, String column) throws SQLException { 092 093 System.out.println(new Exception().getStackTrace()[0].toString()); 094 if (!dbCon.hasTableOrColumn(tablename, column)) { 095 String query = readQuery(QUERY_ADD_TEMP_UUID_COLUMN); // Get the query 096 // if the table is not one of the ONLINE or OFFLINE resources add the new column in the first position 097 if (!RESOURCES_TABLES_LIST.contains(tablename)) { 098 //query += " FIRST"; 099 } 100 Map<String, String> replacer = new HashMap<String, String>(); // Build the replacements 101 replacer.put(REPLACEMENT_TABLENAME, tablename); 102 replacer.put(REPLACEMENT_COLUMN, column); 103 dbCon.updateSqlStatement(query, replacer, null); // execute the query 104 } else { 105 System.out.println("column " + column + " in table " + tablename + " already exists"); 106 } 107 } 108 109 /** 110 * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#checkColumnTypeProjectId(int) 111 */ 112 @Override 113 protected boolean checkColumnTypeProjectId(int type) { 114 115 return type == java.sql.Types.INTEGER; 116 } 117 118 /** 119 * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#createHistProjectsTable(org.opencms.setup.CmsSetupDb) 120 */ 121 @Override 122 protected void createHistProjectsTable(CmsSetupDb dbCon) throws SQLException { 123 124 System.out.println(new Exception().getStackTrace()[0].toString()); 125 if (!dbCon.hasTableOrColumn(HISTORY_PROJECTS_TABLE, null)) { 126 String createStatement = readQuery(QUERY_CREATE_HISTORY_PROJECTS_TABLE); 127 128 String indexTablespace = m_poolData.get("indexTablespace"); 129 Map<String, String> replacer = new HashMap<String, String>(); 130 replacer.put(REPLACEMENT_TABLEINDEX_SPACE, indexTablespace); 131 132 dbCon.updateSqlStatement(createStatement, replacer, null); 133 transferDataToHistoryTable(dbCon); 134 } else { 135 System.out.println("table " + HISTORY_PROJECTS_TABLE + " already exists"); 136 } 137 } 138 139 /** 140 * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#createTempTable(org.opencms.setup.CmsSetupDb) 141 */ 142 @Override 143 protected void createTempTable(CmsSetupDb dbCon) throws SQLException { 144 145 System.out.println(new Exception().getStackTrace()[0].toString()); 146 if (!dbCon.hasTableOrColumn(TEMPORARY_TABLE_NAME, null)) { 147 String createStatement = readQuery(QUERY_CREATE_TEMP_TABLE_UUIDS); 148 149 String indexTablespace = m_poolData.get("indexTablespace"); 150 Map<String, String> replacer = new HashMap<String, String>(); 151 replacer.put(REPLACEMENT_TABLEINDEX_SPACE, indexTablespace); 152 153 dbCon.updateSqlStatement(createStatement, replacer, null); 154 } else { 155 System.out.println("table " + TEMPORARY_TABLE_NAME + " already exists"); 156 } 157 } 158 159 /** 160 * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#getColumnProjectIdResourcePath() 161 */ 162 @Override 163 protected String getColumnProjectIdResourcePath() { 164 165 return COLUMN_PROJECT_ID_RESOURCE_PATH; 166 } 167 168 /** 169 * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#needsUpdating(org.opencms.setup.CmsSetupDb, java.lang.String) 170 */ 171 @Override 172 protected boolean needsUpdating(CmsSetupDb dbCon, String tablename) throws SQLException { 173 174 System.out.println(new Exception().getStackTrace()[0].toString()); 175 boolean result = true; 176 177 String query = readQuery(QUERY_DESCRIBE_TABLE); 178 Map<String, String> replacer = new HashMap<String, String>(); 179 replacer.put(REPLACEMENT_TABLENAME, tablename); 180 CmsSetupDBWrapper db = null; 181 try { 182 db = dbCon.executeSqlStatement(query, replacer); 183 184 while (db.getResultSet().next()) { 185 String fieldname = db.getResultSet().getString("COLUMN_NAME"); 186 if (fieldname.equals(COLUMN_PROJECT_ID) || fieldname.equals(COLUMN_PROJECT_LASTMODIFIED)) { 187 try { 188 String fieldtype = db.getResultSet().getString("DATA_TYPE"); 189 // If the type is varchar then no update needs to be done. 190 if (fieldtype.indexOf("VARCHAR") > -1) { 191 return false; 192 } 193 } catch (SQLException e) { 194 result = true; 195 } 196 } 197 } 198 } finally { 199 if (db != null) { 200 db.close(); 201 } 202 } 203 204 return result; 205 } 206}