001/* 002 * Copyright 2010 Anyware Services 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016package org.ametys.plugins.forms.table; 017 018import java.sql.DatabaseMetaData; 019import java.sql.PreparedStatement; 020import java.sql.SQLException; 021import java.sql.Types; 022 023import org.apache.commons.lang.StringUtils; 024 025import org.ametys.core.datasource.ConnectionHelper; 026import org.ametys.core.datasource.dbtype.SQLDatabaseTypeExtensionPoint; 027import org.ametys.plugins.forms.table.FormTableManager.DbColumn; 028 029/** 030 * Database abstraction helper. 031 */ 032public final class DbTypeHelper 033{ 034 private DbTypeHelper() 035 { 036 // Hides the default constructor. 037 } 038 039 /** 040 * Normalize a name (table name, column name, and so on) according to the database type. 041 * @param dbType the database type. 042 * @param str the string to normalize. 043 * @return the normalized string. 044 */ 045 public static String normalizeName(String dbType, String str) 046 { 047 switch (dbType) 048 { 049 case ConnectionHelper.DATABASE_ORACLE: 050 return StringUtils.replace(str, "-", "_"); 051 default: 052 return str; 053 } 054 } 055 056 /** 057 * Filter a name (table name, column name, and so on) according to the database type 058 * so that it can be passed to {@link DatabaseMetaData} methods, such as getTables and getColumns. 059 * @param dbType the database type. 060 * @param str the string to filter. 061 * @return the filtered string. 062 */ 063 public static String filterName(String dbType, String str) 064 { 065 switch (dbType) 066 { 067 case ConnectionHelper.DATABASE_ORACLE: 068 return str.toUpperCase(); 069 default: 070 return str; 071 } 072 } 073 074 /** 075 * Get the varchar type identifier corresponding to the database type. 076 * @param dbType the database type. 077 * @return the real varchar type identifier (to be included in CREATE statement). 078 */ 079 public static String getVarcharType(String dbType) 080 { 081 return "VARCHAR(255)"; 082 } 083 084 /** 085 * Get the text type identifier corresponding to the database type. 086 * @param dbType the database type. 087 * @return the real text type identifier (to be included in CREATE statement). 088 */ 089 public static String getTextType(String dbType) 090 { 091 switch (dbType) 092 { 093 case ConnectionHelper.DATABASE_DERBY: 094 return "LONG VARCHAR"; 095 case ConnectionHelper.DATABASE_ORACLE: 096 return "CLOB"; 097 default: 098 return "TEXT"; 099 } 100 } 101 102 /** 103 * Get the boolean type identifier corresponding to the database type. 104 * @param dbType the database type. 105 * @return the real boolean type identifier (to be included in CREATE statement). 106 */ 107 public static String getBooleanType(String dbType) 108 { 109 switch (dbType) 110 { 111 case ConnectionHelper.DATABASE_DERBY: 112 case ConnectionHelper.DATABASE_POSTGRES: 113 return "SMALLINT"; 114 case ConnectionHelper.DATABASE_ORACLE: 115 return "NUMBER(1)"; 116 default: 117 return "INT(1)"; 118 } 119 } 120 121 /** 122 * Get the binary type identifier corresponding to the database type. 123 * @param dbType the database type. 124 * @return the real binary type identifier (to be included in CREATE statement). 125 */ 126 public static String getBinaryType(String dbType) 127 { 128 switch (dbType) 129 { 130 case ConnectionHelper.DATABASE_MYSQL: 131 return "LONGBLOB"; 132 case ConnectionHelper.DATABASE_POSTGRES: 133 return "BYTEA"; 134 default: 135 return "BLOB"; 136 } 137 } 138 139 /** 140 * Get the integer type identifier corresponding to the database type. 141 * @param dbType the database type. 142 * @return the integer type identifier (to be included in CREATE statement). 143 */ 144 public static String getIntegerType(String dbType) 145 { 146 return "INTEGER"; 147 } 148 149 /** 150 * Get the date and time type identifier corresponding to the database type. 151 * @param dbType the database type. 152 * @return the real date and time type identifier (to be included in CREATE statement). 153 */ 154 public static String getDateTimeType(String dbType) 155 { 156 return "TIMESTAMP"; 157 } 158 159 /** 160 * Get the "identity" type (int, integer, number, ...) 161 * @param dbType the database type. 162 * @return the "identity" type 163 */ 164 public static String getIdentityType (String dbType) 165 { 166 switch (dbType) 167 { 168 case ConnectionHelper.DATABASE_ORACLE: 169 return "number NOT NULL"; 170 case ConnectionHelper.DATABASE_MYSQL: 171 case ConnectionHelper.DATABASE_DERBY: 172 return "int NOT NULL"; 173 default: 174 return ""; 175 } 176 } 177 /** 178 * Get the "identity" string marker (AUTO_INCREMENT, SERIAL...) 179 * @param dbType the database type. 180 * @return the "identity" string marker. 181 */ 182 public static String getIdentityMarker(String dbType) 183 { 184 switch (dbType) 185 { 186 case ConnectionHelper.DATABASE_MYSQL: 187 return "AUTO_INCREMENT"; 188 case ConnectionHelper.DATABASE_DERBY: 189 return "GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)"; 190 case ConnectionHelper.DATABASE_POSTGRES: 191 return "SERIAL"; 192 default: 193 return ""; 194 } 195 } 196 197 /** 198 * Get the "identity" value (NULL, sequence next val call, ...) 199 * @param dbType the database type. 200 * @return the "identity" value. 201 */ 202 public static String getIdentityValue(String dbType) 203 { 204 return "NULL"; 205 } 206 207 /** 208 * Get the "now" function (NOW(), CURDATE, ...) 209 * @param dbType the database type. 210 * @return the "now" function. 211 */ 212 public static String getCurrentDateFunction(String dbType) 213 { 214 switch (dbType) 215 { 216 case ConnectionHelper.DATABASE_MYSQL: 217 return "NOW()"; 218 case ConnectionHelper.DATABASE_ORACLE: 219 return "CURDATE"; 220 case ConnectionHelper.DATABASE_POSTGRES: 221 return "CURRENT_TIMESTAMP"; 222 case ConnectionHelper.DATABASE_DERBY: 223 default: 224 return ""; 225 } 226 } 227 228 /** 229 * Test if we have to include the identity column in INSERTs. 230 * @param dbType the database type. 231 * @return true if the identity is to be included in INSERT statements, false otherwise. 232 */ 233 public static boolean insertIdentity(String dbType) 234 { 235 switch (dbType) 236 { 237 case ConnectionHelper.DATABASE_DERBY: 238 case ConnectionHelper.DATABASE_POSTGRES: 239 return false; 240 default: 241 return true; 242 } 243 } 244 245 /** 246 * Set the identity value into the statement. 247 * @param stmt the prepared statement. 248 * @param index the column index. 249 * @param dbType the database type. 250 * @throws SQLException if a SQL exception occurs. 251 */ 252 public static void setIdentity(PreparedStatement stmt, int index, String dbType) throws SQLException 253 { 254 switch (dbType) 255 { 256 case ConnectionHelper.DATABASE_MYSQL: 257 stmt.setNull(index, Types.INTEGER); 258 break; 259 case ConnectionHelper.DATABASE_DERBY: 260 break; 261 case ConnectionHelper.DATABASE_POSTGRES: 262 stmt.setNull(index, Types.INTEGER); 263 break; 264 case ConnectionHelper.DATABASE_ORACLE: 265 return; 266 default: 267 stmt.setNull(index, Types.INTEGER); 268 break; 269 270 } 271 } 272 273 /** 274 * Get a SQL statement to rename a column. 275 * @param tableName the table name. 276 * @param column the column to rename. 277 * @param newName the new column name. 278 * @param dbType the database type. 279 * @param sqlDatabaseTypeExtensionPoint SQLDatabaseTypeExtensionPoint 280 * @return the SQL rename query. 281 */ 282 public static String getRenameColumnStatement(String tableName, DbColumn column, String newName, String dbType, SQLDatabaseTypeExtensionPoint sqlDatabaseTypeExtensionPoint) 283 { 284 return getRenameColumnStatement(tableName, column.getName(), newName, column.getColumnTypeIdentifier(), dbType, sqlDatabaseTypeExtensionPoint); 285 } 286 287 /** 288 * Get a SQL statement to rename a column. 289 * @param tableName the table name. 290 * @param columnName the current column name. 291 * @param newName the new column name. 292 * @param columnType the full column type string (i.e TEXT, VARCHAR(255), INT(1), and so on). 293 * @param dbType the database type. 294 * @param sqlDatabaseTypeExtensionPoint SQLDatabaseTypeExtensionPoint 295 * @return the SQL rename query. 296 */ 297 public static String getRenameColumnStatement(String tableName, String columnName, String newName, String columnType, String dbType, SQLDatabaseTypeExtensionPoint sqlDatabaseTypeExtensionPoint) 298 { 299 StringBuilder buff = new StringBuilder(); 300 301 String escapedTableName = sqlDatabaseTypeExtensionPoint.languageEscapeTableName(dbType, tableName); 302 String escapedColumnName = sqlDatabaseTypeExtensionPoint.languageEscapeTableName(dbType, columnName); 303 String escapedNewName = sqlDatabaseTypeExtensionPoint.languageEscapeTableName(dbType, newName); 304 305 switch (dbType) 306 { 307 case ConnectionHelper.DATABASE_MYSQL: 308 buff.append("ALTER TABLE ").append(escapedTableName); 309 buff.append(" CHANGE ").append(escapedColumnName); 310 buff.append(" ").append(escapedNewName).append(" "); 311 buff.append(columnType); 312 buff.append(" DEFAULT NULL"); 313 break; 314 case ConnectionHelper.DATABASE_DERBY: 315 buff.append("RENAME COLUMN ").append(escapedTableName).append(".").append(escapedColumnName); 316 buff.append(" TO ").append(escapedNewName); 317 break; 318 case ConnectionHelper.DATABASE_ORACLE: 319 case ConnectionHelper.DATABASE_POSTGRES: 320 default: 321 buff.append("ALTER TABLE ").append(escapedTableName); 322 buff.append(" RENAME COLUMN ").append(escapedColumnName); 323 buff.append(" TO ").append(escapedNewName); 324 break; 325 } 326 327 return buff.toString(); 328 } 329 330}