| 1 | /** |
| 2 | * Copyright (C) 2006 NetMind Consulting Bt. |
| 3 | * |
| 4 | * This library is free software; you can redistribute it and/or |
| 5 | * modify it under the terms of the GNU Lesser General Public |
| 6 | * License as published by the Free Software Foundation; either |
| 7 | * version 3 of the License, or (at your option) any later version. |
| 8 | * |
| 9 | * This library is distributed in the hope that it will be useful, |
| 10 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 11 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
| 12 | * Lesser General Public License for more details. |
| 13 | * |
| 14 | * You should have received a copy of the GNU Lesser General Public |
| 15 | * License along with this library; if not, write to the Free Software |
| 16 | * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |
| 17 | */ |
| 18 | |
| 19 | package hu.netmind.beankeeper.db.impl; |
| 20 | |
| 21 | import java.sql.Types; |
| 22 | import java.sql.DatabaseMetaData; |
| 23 | import java.sql.ResultSet; |
| 24 | import java.sql.Connection; |
| 25 | import java.sql.PreparedStatement; |
| 26 | import java.sql.SQLException; |
| 27 | import java.util.HashMap; |
| 28 | import java.util.Map; |
| 29 | import java.util.Iterator; |
| 30 | import java.util.List; |
| 31 | import java.io.InputStream; |
| 32 | import java.io.ByteArrayOutputStream; |
| 33 | import java.util.Date; |
| 34 | import java.lang.reflect.Method; |
| 35 | import java.lang.reflect.InvocationTargetException; |
| 36 | import java.math.BigDecimal; |
| 37 | import org.apache.log4j.Logger; |
| 38 | import hu.netmind.beankeeper.parser.*; |
| 39 | import hu.netmind.beankeeper.common.StoreException; |
| 40 | import hu.netmind.beankeeper.db.*; |
| 41 | |
| 42 | /** |
| 43 | * Oracle database implementation. |
| 44 | * Limitations: |
| 45 | * <ul> |
| 46 | * <li>Strings are limited to 1024 characters.</li> |
| 47 | * </ul> |
| 48 | * @author Brautigam Robert |
| 49 | * @version CVS Revision: $Revision$ |
| 50 | */ |
| 51 | public class OracleDatabaseImpl extends GenericDatabase implements Database |
| 52 | { |
| 53 | private static Logger logger = Logger.getLogger(OracleDatabaseImpl.class); |
| 54 | private static Class blobClass = null; |
| 55 | private static Class timestampClass = null; |
| 56 | |
| 57 | public OracleDatabaseImpl() |
| 58 | { |
| 59 | super(); |
| 60 | } |
| 61 | |
| 62 | /** |
| 63 | * Set the limits of the prepared statement if offset is 0. This |
| 64 | * is to avoid using complicated limit+offset inner selects, when |
| 65 | * the first page is queried. |
| 66 | */ |
| 67 | protected void prepareStatement(PreparedStatement pstmt, Limits limits) |
| 68 | throws SQLException |
| 69 | { |
| 70 | if ( (limits == null) || (limits.isEmpty()) ) |
| 71 | return; |
| 72 | if ( (limits.getOffset()==0) && (limits.getLimit()>0) ) |
| 73 | pstmt.setMaxRows((int) limits.getLimit()); |
| 74 | } |
| 75 | |
| 76 | /** |
| 77 | * Get the limit component of statement, if it can be expressed in |
| 78 | * the current database with simple statement part. |
| 79 | * @param limits The limits to apply. |
| 80 | */ |
| 81 | protected String getLimitStatement(String statement,Limits limits, List types) |
| 82 | { |
| 83 | if ( (limits.getLimit() == 0) || (limits.getOffset() == 0) ) |
| 84 | { |
| 85 | if ( limits.getOffset() != 0 ) |
| 86 | { |
| 87 | // No limit, there should be no offset |
| 88 | throw new StoreException("received a limit of 0, but offset was: "+limits.getOffset()); |
| 89 | } |
| 90 | // Nothing |
| 91 | return statement; |
| 92 | } |
| 93 | // Add type of rownum (see below for explanation) |
| 94 | types.add(Long.class); |
| 95 | // Now, oracle does not have limits and offsets, |
| 96 | // so don't look at me, this is the way it's done. |
| 97 | // In a stateless environment anyway. |
| 98 | return "select * from (select sub.*, rownum rnum from ("+statement+ |
| 99 | ") sub where rownum <= "+(limits.getOffset()+limits.getLimit())+ |
| 100 | ") where rnum > "+limits.getOffset(); |
| 101 | } |
| 102 | |
| 103 | /** |
| 104 | * Override to correct type conflicts an unsupported types. |
| 105 | */ |
| 106 | protected String getSQLTypeName(int sqltype) |
| 107 | { |
| 108 | switch ( sqltype ) |
| 109 | { |
| 110 | case Types.LONGVARCHAR: |
| 111 | case Types.VARCHAR: |
| 112 | return "varchar2(1024)"; |
| 113 | case Types.BIGINT: |
| 114 | case Types.INTEGER: |
| 115 | case Types.SMALLINT: |
| 116 | case Types.DECIMAL: |
| 117 | return "number(*,0)"; |
| 118 | case Types.BIT: |
| 119 | case Types.BOOLEAN: |
| 120 | return "number(1,0)"; |
| 121 | case Types.CHAR: |
| 122 | return "char(1)"; |
| 123 | default: |
| 124 | return super.getSQLTypeName(sqltype); |
| 125 | } |
| 126 | } |
| 127 | |
| 128 | /** |
| 129 | * Get the sql type string for a class. |
| 130 | */ |
| 131 | protected int getSQLType(Class type) |
| 132 | { |
| 133 | // Booleans are numbers here |
| 134 | Class booleanClass = boolean.class; |
| 135 | if ( (booleanClass.equals(type)) || (Boolean.class.equals(type)) ) |
| 136 | return Types.INTEGER; |
| 137 | // We don't really want to create BLOB objects, because those are |
| 138 | // complex to handle |
| 139 | if ( byte[].class.equals(type) ) |
| 140 | return Types.LONGVARBINARY; |
| 141 | // Re-use super |
| 142 | return super.getSQLType(type); |
| 143 | } |
| 144 | |
| 145 | /** |
| 146 | * Throw exception when String is longer than Oracle can handle. |
| 147 | */ |
| 148 | protected Object getSQLValue(Object value) |
| 149 | { |
| 150 | if ( (value instanceof String) && ("".equals((String) value)) ) |
| 151 | return new String("$"); // Don't let empty string through |
| 152 | if ( value instanceof String ) |
| 153 | { |
| 154 | // Escape all $ signs |
| 155 | StringBuffer str = new StringBuffer((String) value); |
| 156 | for ( int i=0; i<str.length(); i++ ) |
| 157 | { |
| 158 | if ( str.charAt(i) == '$' ) |
| 159 | { |
| 160 | str.insert(i,"$"); |
| 161 | i++; |
| 162 | } |
| 163 | } |
| 164 | return str.toString(); |
| 165 | } |
| 166 | if ( value instanceof Character ) |
| 167 | return value.toString(); |
| 168 | if ( value instanceof Boolean ) |
| 169 | return new Integer(((Boolean) value).booleanValue()?1:0); |
| 170 | if ( (value instanceof String) && (((String) value).length() > 1024) ) |
| 171 | throw new StoreException("received a string which was too long for Oracle to handle "+ |
| 172 | "(more than 1024 characters), the string started with: "+((String)value).substring(0,100)); |
| 173 | return super.getSQLValue(value); |
| 174 | } |
| 175 | |
| 176 | /** |
| 177 | * Convert incoming value from database into java format. |
| 178 | */ |
| 179 | protected Object getJavaValue(Object value, int type, Class javaType) |
| 180 | { |
| 181 | try |
| 182 | { |
| 183 | if ( value == null ) |
| 184 | return null; |
| 185 | logger.debug("transforming value: "+value+", type: "+type+", java type: "+javaType); |
| 186 | Class booleanClass = boolean.class; |
| 187 | if ( (Boolean.class.equals(javaType)) || (booleanClass.equals(javaType)) ) |
| 188 | return new Boolean( ((Number) value).intValue() > 0 ); |
| 189 | if ( (value instanceof String) && (((String) value).length()==1) |
| 190 | && (((String) value).charAt(0) == '$') ) |
| 191 | return ""; // Deconvert dollar to empty string |
| 192 | if ( value instanceof String ) |
| 193 | { |
| 194 | // Unescape all $ signs |
| 195 | StringBuffer str = new StringBuffer((String) value); |
| 196 | for ( int i=0; i<str.length(); i++ ) |
| 197 | { |
| 198 | if ( str.charAt(i) == '$' ) |
| 199 | { |
| 200 | str.deleteCharAt(i); |
| 201 | i--; |
| 202 | } |
| 203 | } |
| 204 | return str.toString(); |
| 205 | } |
| 206 | if ( blobClass.isAssignableFrom(value.getClass()) ) |
| 207 | { |
| 208 | // Here we must read out the actual values in the blob |
| 209 | InputStream blobStream = (InputStream) internalCall(value,"getBinaryStream"); |
| 210 | ByteArrayOutputStream byteOut = new ByteArrayOutputStream(); |
| 211 | int b = 0; |
| 212 | while ( (b = blobStream.read()) >= 0 ) |
| 213 | byteOut.write( (byte) b ); |
| 214 | blobStream.close(); |
| 215 | byteOut.close(); |
| 216 | return byteOut.toByteArray(); |
| 217 | } |
| 218 | if ( timestampClass.isAssignableFrom(value.getClass()) ) |
| 219 | return (Date) internalCall(value,"timestampValue"); |
| 220 | if ( value instanceof BigDecimal ) |
| 221 | { |
| 222 | if ( javaType.equals(Long.class) || javaType.equals(long.class) ) |
| 223 | return new Long(((BigDecimal) value).longValue()); |
| 224 | if ( javaType.equals(Integer.class) || javaType.equals(int.class) ) |
| 225 | return new Integer(((BigDecimal) value).intValue()); |
| 226 | } |
| 227 | return super.getJavaValue(value,type,javaType); |
| 228 | } catch ( StoreException e ) { |
| 229 | throw e; |
| 230 | } catch ( Exception e ) { |
| 231 | throw new StoreException("conversion error tried to convert: "+value+", of sql type: "+type,e); |
| 232 | } |
| 233 | } |
| 234 | |
| 235 | /** |
| 236 | * This method's purpose is to decouple this code from Oracle libraries, because |
| 237 | * those are not freely available. |
| 238 | */ |
| 239 | private Object internalCall(Object obj, String methodName) |
| 240 | throws NoSuchMethodException, IllegalAccessException, InvocationTargetException |
| 241 | { |
| 242 | return internalCall(obj,methodName,new Class[] {}, new Object[] {}); |
| 243 | } |
| 244 | |
| 245 | /** |
| 246 | * This method's purpose is to decouple this code from Oracle libraries, because |
| 247 | * those are not freely available. |
| 248 | */ |
| 249 | private Object internalCall(Object obj, String methodName, Class[] classes, Object[] params) |
| 250 | throws NoSuchMethodException, IllegalAccessException, InvocationTargetException |
| 251 | { |
| 252 | Method method = obj.getClass().getMethod(methodName, classes); |
| 253 | return method.invoke(obj, params); |
| 254 | } |
| 255 | |
| 256 | /** |
| 257 | * Get the count statement for the given statement. |
| 258 | */ |
| 259 | protected String getCountStatement(String stmt) |
| 260 | { |
| 261 | return "select count(*) from ("+stmt+")"; |
| 262 | } |
| 263 | |
| 264 | /** |
| 265 | * Get the data types of a given table. |
| 266 | * @return A map of names with the sql type number as value. |
| 267 | */ |
| 268 | protected HashMap getTableAttributeTypes(Connection connection, |
| 269 | String tableName) |
| 270 | throws SQLException |
| 271 | { |
| 272 | return super.getTableAttributeTypes(connection,tableName.toUpperCase()); |
| 273 | } |
| 274 | |
| 275 | /** |
| 276 | * Fix custom data types not supported by database. |
| 277 | */ |
| 278 | protected int getTableAttributeType(ResultSet rs) |
| 279 | throws SQLException |
| 280 | { |
| 281 | int columnType = rs.getInt("DATA_TYPE"); |
| 282 | // Recognize boolean type |
| 283 | if ( (columnType == Types.DECIMAL) && (rs.getInt("COLUMN_SIZE")==1) ) |
| 284 | columnType = Types.BOOLEAN; |
| 285 | return columnType; |
| 286 | } |
| 287 | |
| 288 | /** |
| 289 | * Get the table declaration for a select statment. |
| 290 | */ |
| 291 | protected String getTableDeclaration(String tableName, String alias) |
| 292 | { |
| 293 | if ( alias == null ) |
| 294 | return tableName; |
| 295 | else |
| 296 | return tableName+" "+alias; |
| 297 | } |
| 298 | |
| 299 | /** |
| 300 | * Transform functions for hsql. |
| 301 | */ |
| 302 | protected List transformTerms(List terms) |
| 303 | { |
| 304 | for ( int i=0; i<terms.size(); i++ ) |
| 305 | { |
| 306 | Object term = terms.get(i); |
| 307 | if ( term instanceof ReferenceTerm ) |
| 308 | { |
| 309 | Function function = ((ReferenceTerm) term).getFunction(); |
| 310 | if ( (function!=null) && (function instanceof MathematicalPostfixFunction) && |
| 311 | (((MathematicalPostfixFunction)function).getFunction().equals(">>")) ) |
| 312 | { |
| 313 | // Oracle does not support the bitshift operator, so we modify |
| 314 | // the function |
| 315 | MathematicalPostfixFunction mathFunction = (MathematicalPostfixFunction) function; |
| 316 | ((ReferenceTerm)term).setFunction(new PrefixFunction("floor", |
| 317 | new MathematicalPostfixFunction("/",""+ |
| 318 | (1L<<(Long.parseLong(mathFunction.getOperand()))) ))); |
| 319 | } |
| 320 | } |
| 321 | } |
| 322 | return terms; |
| 323 | } |
| 324 | |
| 325 | /** |
| 326 | * Get the statement to add a column to a table. |
| 327 | */ |
| 328 | protected String getAddColumnStatement(String tableName, String columnName, String columnType) |
| 329 | { |
| 330 | return "alter table "+tableName+" add "+columnName+" "+columnType; |
| 331 | } |
| 332 | |
| 333 | /** |
| 334 | * Transform 'ilike' to upper case like. |
| 335 | * @param expr The expression to possibly transform. |
| 336 | * @return A transformed expression. |
| 337 | */ |
| 338 | protected Expression transformExpression(Expression expr) |
| 339 | { |
| 340 | Expression result = new Expression(expr); |
| 341 | result.clear(); |
| 342 | for ( int i=0; i<expr.size(); i++ ) |
| 343 | { |
| 344 | Object item = expr.get(i); |
| 345 | if ( "ilike".equals(item) ) |
| 346 | { |
| 347 | // Here we need to upper() the argument before and after like |
| 348 | Object arg = result.remove(result.size()-1); |
| 349 | result.add("upper("); |
| 350 | result.add(arg); |
| 351 | result.add(")"); |
| 352 | result.add("like"); |
| 353 | result.add("upper("); |
| 354 | result.add(expr.get(i+1)); |
| 355 | result.add(")"); |
| 356 | i++; // We used an argument |
| 357 | } else { |
| 358 | result.add(item); |
| 359 | } |
| 360 | } |
| 361 | // Transform functions too |
| 362 | transformTerms(result); |
| 363 | return result; |
| 364 | } |
| 365 | |
| 366 | /** |
| 367 | * Get an unused index name. |
| 368 | */ |
| 369 | protected String getCreateIndexName(Connection connection, String tableName, |
| 370 | String field) |
| 371 | { |
| 372 | return super.getCreateIndexName(connection,tableName.toUpperCase(),field); |
| 373 | } |
| 374 | |
| 375 | static |
| 376 | { |
| 377 | try |
| 378 | { |
| 379 | blobClass = Class.forName("oracle.sql.BLOB"); |
| 380 | timestampClass = Class.forName("oracle.sql.TIMESTAMP"); |
| 381 | } catch ( Exception e ) { |
| 382 | logger.fatal("could not get necessary oracle classes, oracle database will probably not work",e); |
| 383 | } |
| 384 | } |
| 385 | } |
| 386 | |
| 387 | |
| 388 | |