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.CmsSetupDb; 031 032import java.io.IOException; 033import java.sql.SQLException; 034import java.util.HashMap; 035import java.util.Map; 036 037/** 038 * PostgreSQL implementation of the generic Alter Table class.<p> 039 * 040 * @since 7.0.2 041 */ 042public class CmsUpdateDBAlterTables extends org.opencms.setup.db.update6to7.CmsUpdateDBAlterTables { 043 044 /** Constant for the sql query to alter table field.<p> */ 045 private static final String QUERY_ALTER_FIELD = "Q_ALTER_FIELD_TYPE"; 046 047 /** Constant for the sql query to set default value of table field.<p> */ 048 private static final String QUERY_SET_DEFAULT_VALUE = "Q_ALTER_FIELD_SET_DEFAULT"; 049 050 /** Constant for the sql query to drop default value of table field.<p> */ 051 private static final String QUERY_DROP_DEFAULT_VALUE = "Q_ALTER_FIELD_DROP_DEFAULT"; 052 053 /** Constant for the sql query to set not null values of table field.<p> */ 054 private static final String QUERY_SET_NOT_NULL = "Q_ALTER_FIELD_SET_NOT_NULL"; 055 056 /** Constant for the sql query to drop not null constraint of table field.<p> */ 057 private static final String QUERY_DROP_NOT_NULL = "Q_ALTER_FIELD_DROP_NOT_NULL"; 058 059 /** Constant for the sql replacement of the field name.<p> */ 060 private static final String REPLACEMENT_FIELD_NAME = "${fieldname}"; 061 062 /** Constant for the sql replacement of the field type.<p> */ 063 private static final String REPLACEMENT_FIELD_TYPE = "${fieldtype}"; 064 065 /** Constant for the sql replacement of the field default value.<p> */ 066 private static final String REPLACEMENT_FIELD_DEFAULT_VALUE = "${defaultvalue}"; 067 068 /** Constant for the SQL query properties.<p> */ 069 private static final String QUERY_PROPERTY_FILE_ORACLE = "cms_alter_remaining_queries.properties"; 070 071 /** SQL constant. */ 072 private static final String DROP_NOT_NULL = "0"; 073 074 /** SQL constant. */ 075 private static final String SET_NOT_NULL = "1"; 076 077 /** SQL constant. */ 078 private static final String NO_CHANGE = "2"; 079 080 /** SQL constant. */ 081 private static final String DV_NO_CHANGE = null; 082 083 /** SQL constant. */ 084 private static final String DV_DROP = "-- droping the default value --"; 085 086 /** 087 * Array contains differences after upgrade and clean install of OpenCms 7.0.1.<p> 088 * <pre> 089 * [0] - table name 090 * [1] - field name 091 * [3] - database type of the field 092 * [4] - SET/DROP NULL 093 * [5] - DEFAULT VALUE information 094 * </pre> 095 */ 096 private static final String[][] DB_ARRAY = { 097 {"cms_groups", "group_name", "varchar(128)", NO_CHANGE, DV_NO_CHANGE}, 098 {"cms_groups", "group_ou", "", SET_NOT_NULL, DV_NO_CHANGE}, 099 100 {"cms_offline_properties", "property_value", "varchar(2048)", NO_CHANGE, DV_NO_CHANGE}, 101 102 {"cms_offline_propertydef", "propertydef_type", "", NO_CHANGE, DV_DROP}, 103 104 {"cms_offline_resources", "date_content", "", NO_CHANGE, DV_DROP}, 105 {"cms_offline_resources", "project_lastmodified", "", SET_NOT_NULL, DV_NO_CHANGE}, 106 {"cms_offline_resources", "resource_version", "", SET_NOT_NULL, DV_DROP}, 107 108 {"cms_offline_structure", "resource_path", "varchar(1024)", DROP_NOT_NULL, DV_NO_CHANGE}, 109 {"cms_offline_structure", "structure_state", "integer", NO_CHANGE, DV_NO_CHANGE}, 110 {"cms_offline_structure", "date_released", "", SET_NOT_NULL, DV_NO_CHANGE}, 111 {"cms_offline_structure", "date_expired", "", SET_NOT_NULL, DV_NO_CHANGE}, 112 {"cms_offline_structure", "structure_version", "", NO_CHANGE, DV_DROP}, 113 114 {"cms_online_properties", "property_value", "varchar(2048)", NO_CHANGE, DV_NO_CHANGE}, 115 116 {"cms_online_propertydef", "propertydef_type", "", NO_CHANGE, DV_DROP}, 117 118 {"cms_online_resources", "resource_state", "integer", NO_CHANGE, DV_NO_CHANGE}, 119 {"cms_online_resources", "date_content", "", NO_CHANGE, DV_DROP}, 120 {"cms_online_resources", "project_lastmodified", "", SET_NOT_NULL, DV_NO_CHANGE}, 121 {"cms_online_resources", "resource_version", "", NO_CHANGE, DV_DROP}, 122 123 {"cms_online_structure", "resource_path", "varchar(1024)", DROP_NOT_NULL, DV_NO_CHANGE}, 124 {"cms_online_structure", "structure_state", "integer", NO_CHANGE, DV_NO_CHANGE}, 125 {"cms_online_structure", "structure_version", "", NO_CHANGE, DV_DROP}, 126 127 {"cms_projectresources", "resource_path", "varchar(1024)", NO_CHANGE, DV_NO_CHANGE}, 128 129 {"cms_projects", "project_ou", "", SET_NOT_NULL, DV_NO_CHANGE}, 130 131 {"cms_publish_history", "resource_path", "varchar(1024)", NO_CHANGE, DV_NO_CHANGE}, 132 133 {"cms_resource_locks", "resource_path", "", DROP_NOT_NULL, DV_NO_CHANGE}, 134 135 {"cms_staticexport_links", "link_rfs_path", "varchar(1024)", DROP_NOT_NULL, DV_NO_CHANGE}, 136 {"cms_staticexport_links", "link_parameter", "varchar(1024)", NO_CHANGE, DV_NO_CHANGE}, 137 {"cms_staticexport_links", "link_timestamp", "", SET_NOT_NULL, DV_NO_CHANGE}, 138 139 {"cms_users", "user_name", "varchar(128)", NO_CHANGE, DV_NO_CHANGE}, 140 {"cms_users", "user_password", "varchar(64)", NO_CHANGE, DV_NO_CHANGE}, 141 {"cms_users", "user_firstname", "varchar(128)", NO_CHANGE, DV_NO_CHANGE}, 142 {"cms_users", "user_lastname", "varchar(128)", NO_CHANGE, DV_NO_CHANGE}, 143 {"cms_users", "user_email", "varchar(128)", NO_CHANGE, DV_NO_CHANGE}, 144 {"cms_users", "user_ou", "", SET_NOT_NULL, DV_NO_CHANGE}, 145 {"cms_users", "user_datecreated", "", NO_CHANGE, DV_DROP} 146 147 }; 148 149 /** 150 * Constructor.<p> 151 * 152 * @throws IOException if the sql queries properties file could not be read 153 */ 154 public CmsUpdateDBAlterTables() 155 throws IOException { 156 157 super(); 158 loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE_ORACLE); 159 } 160 161 /** 162 * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb) 163 */ 164 @Override 165 protected void internalExecute(CmsSetupDb dbCon) throws SQLException { 166 167 super.internalExecute(dbCon); 168 fixSchema(dbCon); 169 } 170 171 /** 172 * Initializes the replacer.<p> 173 * 174 * @param replacer the replacer 175 * @param tableName the table name 176 * @param fieldName the field name 177 */ 178 private void initReplacer(Map<String, String> replacer, String tableName, String fieldName) { 179 180 replacer.clear(); 181 replacer.put(REPLACEMENT_TABLENAME, tableName); 182 replacer.put(REPLACEMENT_FIELD_NAME, fieldName); 183 } 184 185 /** 186 * Fixes the database schema.<p> 187 * 188 * @param dbCon database connection 189 * 190 * @throws SQLException if something goes wrong changing the schema 191 */ 192 private void fixSchema(CmsSetupDb dbCon) throws SQLException { 193 194 Map<String, String> replacer = new HashMap<String, String>(); 195 String tableName; 196 String fieldName; 197 String fieldType; 198 String nullAcceptInfo; 199 String defaultValue; 200 String query; 201 for (int i = 0; i < DB_ARRAY.length; i++) { 202 tableName = DB_ARRAY[i][0]; 203 fieldName = DB_ARRAY[i][1]; 204 fieldType = DB_ARRAY[i][2]; 205 nullAcceptInfo = DB_ARRAY[i][3]; 206 defaultValue = DB_ARRAY[i][4]; 207 208 if ((fieldType != null) && (fieldType.length() > 0)) { 209 initReplacer(replacer, tableName, fieldName); 210 replacer.put(REPLACEMENT_FIELD_TYPE, fieldType); 211 query = readQuery(QUERY_ALTER_FIELD); 212 dbCon.updateSqlStatement(query, replacer, null); 213 } 214 215 if ((nullAcceptInfo != null) && (!nullAcceptInfo.equals(NO_CHANGE))) { 216 String q; 217 if (nullAcceptInfo.equals(DROP_NOT_NULL)) { 218 q = QUERY_DROP_NOT_NULL; 219 } else { 220 q = QUERY_SET_NOT_NULL; 221 } 222 query = readQuery(q); 223 initReplacer(replacer, tableName, fieldName); 224 dbCon.updateSqlStatement(query, replacer, null); 225 } 226 227 if ((defaultValue != null) && (defaultValue.length() > 0)) { 228 initReplacer(replacer, tableName, fieldName); 229 if (defaultValue.equals(DV_DROP)) { 230 query = readQuery(QUERY_DROP_DEFAULT_VALUE); 231 dbCon.updateSqlStatement(query, replacer, null); 232 } else { 233 query = readQuery(QUERY_SET_DEFAULT_VALUE); 234 replacer.put(REPLACEMENT_FIELD_DEFAULT_VALUE, defaultValue); 235 dbCon.updateSqlStatement(query, replacer, null); 236 } 237 } 238 239 } 240 241 } 242 243}