Package de.fuberlin.wiwiss.d2rq.sql.vendor

Source Code of de.fuberlin.wiwiss.d2rq.sql.vendor.Oracle$OracleCompatibilityTimeZoneDataType

package de.fuberlin.wiwiss.d2rq.sql.vendor;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.TimeZone;
import java.util.regex.Pattern;

import de.fuberlin.wiwiss.d2rq.D2RQException;
import de.fuberlin.wiwiss.d2rq.expr.BooleanToIntegerCaseExpression;
import de.fuberlin.wiwiss.d2rq.expr.Expression;
import de.fuberlin.wiwiss.d2rq.expr.SQLExpression;
import de.fuberlin.wiwiss.d2rq.map.Database;
import de.fuberlin.wiwiss.d2rq.sql.types.DataType;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLApproximateNumeric;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLBinary;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLCharacterString;
import de.fuberlin.wiwiss.d2rq.sql.types.SQLTimestamp;
import de.fuberlin.wiwiss.d2rq.sql.types.UnsupportedDataType;

/**
* This syntax class implements MySQL-compatible SQL syntax.
*
* @author Richard Cyganiak (richard@cyganiak.de)
*/
public class Oracle extends SQL92 {

  public Oracle() {
    super(false);
  }
 
  @Override
  public Expression getRowNumLimitAsExpression(int limit) {
    if (limit == Database.NO_LIMIT) return Expression.TRUE;
    return SQLExpression.create("ROWNUM <= " + limit);
  }

  @Override
  public String getRowNumLimitAsQueryAppendage(int limit) {
    return "";
  }
 
  @Override
  public String quoteBinaryLiteral(String hexString) {
    return quoteStringLiteral(hexString);
  }

  @Override
  public DataType getDataType(int jdbcType, String name, int size) {
   
    // Doesn't support DISTINCT over LOB types
    if (jdbcType == Types.CLOB || "NCLOB".equals(name)) {
      return new SQLCharacterString(this, name, false);
    }
    if (jdbcType == Types.BLOB) {
      return new SQLBinary(this, name, false);
    }
   
    DataType standard = super.getDataType(jdbcType, name, size);
    if (standard != null) return standard;

    // Special handling for TIMESTAMP(x) WITH LOCAL TIME ZONE
    if (name.contains("WITH LOCAL TIME ZONE") || "TIMESTAMPLTZ".equals(name)) {
      return new OracleCompatibilityTimeZoneLocalDataType(this, name);
    }
   
    // Special handling for TIMESTAMP(x) WITH TIME ZONE
    if(name.contains("WITH TIME ZONE") || "TIMESTAMPTZ".equals(name)) {
      return new OracleCompatibilityTimeZoneDataType(this, name);
    }
   
    // Oracle-specific character string types
    if ("VARCHAR2".equals(name) || "NVARCHAR2".equals(name)) {
      return new SQLCharacterString(this, name, true);
    }

    // Oracle-specific floating point types
      if ("BINARY_FLOAT".equals(name) || "BINARY_DOUBLE".equals(name)) {
        return new SQLApproximateNumeric(this, name);
      }
     
    // Oracle binary file pointer
    // TODO: We could at least support reading from BFILE, although querying for them seems hard
      if ("BFILE".equals(name)) {
        return new UnsupportedDataType(jdbcType, name);
      }

      return null;
  }

  @Override
  public void initializeConnection(Connection connection) throws SQLException {
    // Set Oracle date formats
    Statement stmt = connection.createStatement();
    try {
      stmt.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD'");
      stmt.execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS'");
      setSessionTimeZone(connection, getTimeZoneForSession().getID());
    } catch (Exception ex) {
      throw new D2RQException(ex);
    } finally {
      stmt.close();
    }
  }

  /**
   * Sets the session time zone on the connection. We need to call an
   * Oracle-specific method. We use reflection for this so we can compile
   * the code even without the Oracle driver on the classpath. This method
   * does:
   *
   * ((OracleConnection) connection).setSessionTimeZone(timeZoneID);
   */
  private void setSessionTimeZone(Connection connection, String timeZoneID)
      throws Exception {
    Class<?> c = Class.forName("oracle.jdbc.driver.OracleConnection");
    Method setSessionTimeZone = c.getMethod("setSessionTimeZone", String.class);
    setSessionTimeZone.invoke(connection, timeZoneID);
  }
 
  /**
   * A separate method for this just to highlight that it needs to
   * be used in multiple places.
   */
  private static TimeZone getTimeZoneForSession() {
    return TimeZone.getDefault();
  }
 
  /**
  * Oracle doesn't actually support booolean expressions, except in
  * a few places. Turn the boolean into an int using a CASE statement
  */
  public Expression booleanExpressionToSimpleExpression(Expression expression) {
      return new BooleanToIntegerCaseExpression(expression);
  }
 
  @Override
  public boolean isIgnoredTable(String schema, String table) {
    if (Arrays.binarySearch(IGNORED_SCHEMAS, schema) >= 0) return true;
    if (IGNORED_SCHEMAS_PATTERN.matcher(schema).matches()) return true;
    // Skip Oracle system schemas as well as deleted tables in Oracle's Recycling Bin.
    // The latter have names like MYSCHEMA.BIN$FoHqtx6aQ4mBaMQmlTCPTQ==$0
    if (table.startsWith("BIN$")) return true;
    // Skip nested tables with names like SYS_NTr8Sify7K5pLgQBmQXa5h2g==
    // There are issues where the table cannot be found later
    if (table.startsWith("SYS_NT")) return true;
    return false;
  }
  private static final String[] IGNORED_SCHEMAS = {
    "APPQOSSYS", "CACHEADM", "CTXSYS", "DBSNMP", "EXFSYS", "FLOWS_FILES",
    "MDSYS", "OLAPSYS", "ORDDATA", "ORDSYS", "OUTLN", "OWBSYS",
    "SYS", "SYSMAN", "SYSTEM", "TIMESTEN", "WKSYS", "WK_TEST", "WMSYS",
    "XDB", "XDBEXT", "XDBPM"};
  private final static Pattern IGNORED_SCHEMAS_PATTERN = Pattern.compile("(APEX|FLOWS)_\\d{6}");
 
  /**
   * getString() doesn't really work for TIMESTAMP WITH LOCAL TIME ZONE,
   * we have to use getTimestamp() and format the resulting Timestamp object
   * according to the session's time zone.
   *
   * @author Aftab Iqbal
   */
  public static class OracleCompatibilityTimeZoneLocalDataType extends SQLTimestamp {
    public OracleCompatibilityTimeZoneLocalDataType(Vendor syntax, String name) {
      super(syntax, name);
    }
   
    @Override
    public String value(ResultSet resultSet, int column) throws SQLException {
      // Hack for TIMESTAMP WITH LOCAL TIME ZONE data type
      Timestamp timestampValue = resultSet.getTimestamp(column);
      return formatForSessionTimeZone(timestampValue);
    }
   
    /**
     * @param timestamp A timestamp (does not contain time zone information)
     * @return Formatted as xsd:dateTime in the session's time zone
     */
    private static String formatForSessionTimeZone(Timestamp timestamp) {
          Calendar cal = new GregorianCalendar();
          cal.setTime(timestamp);
          DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ");
          df.setTimeZone(getTimeZoneForSession());
          String dateTime = df.format(cal.getTime());
       
          // Adding colon into time zone value (e.g. +03:00)
          // as required by xsd:dateTime. SimpleDateFormat can't do it on its own
          return dateTime.substring(0, dateTime.length()-2) +
              ":" + dateTime.substring(dateTime.length()-2);
      }
  }
 
  public static class OracleCompatibilityTimeZoneDataType extends SQLTimestamp {
    public OracleCompatibilityTimeZoneDataType(Vendor syntax, String name) {
      super(syntax, name);
    }
    @Override
    public String value(ResultSet resultSet, int column) throws SQLException {
      // Hack for Oracle TIMESTAMP WITH TIME ZONE data type
      try {
        return super.value(resultSet, column);
      } catch (SQLException ex) {
        return null;
      }
    }
  }
}
TOP

Related Classes of de.fuberlin.wiwiss.d2rq.sql.vendor.Oracle$OracleCompatibilityTimeZoneDataType

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.