oraclemodule.java

来自「RESIN 3.2 最新源码」· Java 代码 · 共 2,223 行 · 第 1/5 页

/*
 * Copyright (c) 1998-2008 Caucho Technology -- all rights reserved
 *
 * This file is part of Resin(R) Open Source
 *
 * Each copy or derived work must preserve the copyright notice and this
 * notice unmodified.
 *
 * Resin Open Source is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * Resin Open Source is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, or any warranty
 * of NON-INFRINGEMENT.  See the GNU General Public License for more
 * details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Resin Open Source; if not, write to the
 *
 *   Free Software Foundation, Inc.
 *   59 Temple Place, Suite 330
 *   Boston, MA 02111-1307  USA
 *
 * @author Rodrigo Westrupp
 */

package com.caucho.quercus.lib.db;

import com.caucho.quercus.UnimplementedException;
import com.caucho.quercus.annotation.NotNull;
import com.caucho.quercus.annotation.Optional;
import com.caucho.quercus.annotation.Reference;
import com.caucho.quercus.annotation.ReturnNullAsFalse;
import com.caucho.quercus.env.*;
import com.caucho.quercus.module.AbstractQuercusModule;
import com.caucho.util.L10N;
import com.caucho.util.Log;

import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * Quercus oracle routines.
 *
 * NOTE from php.net:
 *
 * "...
 * These functions allow you to access Oracle 10, Oracle 9, Oracle 8 and Oracle 7
 * databases using the Oracle Call Interface (OCI). They support binding of PHP
 * variables to Oracle placeholders, have full LOB, FILE and ROWID support, and
 * allow you to use user-supplied define variables.
 *
 * Requirements
 *
 * You will need the Oracle client libraries to use this extension.
 * Windows users will need libraries with version at least 10 to use the php_oci8.dll.
 *
 * ..."
 *
 */
public class OracleModule extends AbstractQuercusModule {
  private static final Logger log = Log.open(OracleModule.class);
  private static final L10N L = new L10N(OracleModule.class);

  // WARNING: Do not change order or constant values.
  // They are mapped to oracle types below.
  // See arrayPhpToOracleType.
  public static final int OCI_B_BFILE                    = 0x01;
  public static final int OCI_B_CFILEE                   = 0x02;
  public static final int OCI_B_CLOB                     = 0x03;
  public static final int OCI_B_BLOB                     = 0x04;
  public static final int OCI_B_ROWID                    = 0x05;
  public static final int OCI_B_CURSOR                   = 0x06;
  public static final int OCI_B_NTY                      = 0x07;
  public static final int OCI_B_BIN                      = 0x08;
  public static final int OCI_DTYPE_FILE                 = 0x09;
  public static final int OCI_DTYPE_LOB                  = 0x0A;
  public static final int OCI_DTYPE_ROWID                = 0x0B;
  public static final int OCI_D_FILE                     = 0x0C;
  public static final int OCI_D_LOB                      = 0x0D;
  public static final int OCI_D_ROWID                    = 0x0E;
  public static final int OCI_SYSDATE                    = 0x0F;
  public static final int OCI_TEMP_CLOB                  = 0x10;
  public static final int OCI_TEMP_BLOB                  = 0x11;
  public static final int SQLT_BFILEE                    = 0x12;
  public static final int SQLT_CFILEE                    = 0x13;
  public static final int SQLT_CLOB                      = 0x14;
  public static final int SQLT_BLOB                      = 0x15;
  public static final int SQLT_RDD                       = 0x16;
  public static final int SQLT_NTY                       = 0x17;
  public static final int SQLT_LNG                       = 0x18;
  public static final int SQLT_LBI                       = 0x19;
  public static final int SQLT_BIN                       = 0x1A;
  public static final int SQLT_NUM                       = 0x1B;
  public static final int SQLT_INT                       = 0x1C;
  public static final int SQLT_AFC                       = 0x1D;
  public static final int SQLT_CHR                       = 0x1E;
  public static final int SQLT_VCS                       = 0x1F;
  public static final int SQLT_AVC                       = 0x20;
  public static final int SQLT_STR                       = 0x21;
  public static final int SQLT_LVC                       = 0x22;
  public static final int SQLT_FLT                       = 0x23;
  public static final int SQLT_ODT                       = 0x24;
  public static final int SQLT_BDOUBLE                   = 0x25;
  public static final int SQLT_BFLOAT                    = 0x26;
  public static final int SQLT_RSET                      = 0x27;
  public static final int SQLT_FILE                      = 0x28;
  public static final int SQLT_CFILE                     = 0x29;

  // Reserved for future types and extensions
  // 0x30 - 0x4F

  // OCI Control Constants 0x50 - ...
  public static final int OCI_DEFAULT                    = 0x50;
  public static final int OCI_DESCRIBE_ONLY              = 0x51;
  public static final int OCI_COMMIT_ON_SUCCESS          = 0x52;
  public static final int OCI_EXACT_FETCH                = 0x53;
  public static final int OCI_FETCHSTATEMENT_BY_COLUMN   = 0x54;
  public static final int OCI_FETCHSTATEMENT_BY_ROW      = 0x55;
  public static final int OCI_ASSOC                      = 0x56;
  public static final int OCI_NUM                        = 0x57;
  public static final int OCI_BOTH                       = 0x58;
  public static final int OCI_RETURN_NULLS               = 0x59;
  public static final int OCI_RETURN_LOBS                = 0x5A;
  public static final int OCI_SYSOPER                    = 0x5B;
  public static final int OCI_SYSDBA                     = 0x5C;
  public static final int OCI_LOB_BUFFER_FREE            = 0x5D;
  public static final int OCI_SEEK_SET                   = 0x5E;
  public static final int OCI_SEEK_CUR                   = 0x5F;
  public static final int OCI_SEEK_END                   = 0x6A;


  // Cache class oracle.jdbc.OracleTypes to be used below.
  private static Class classOracleTypes;

  // Map php to oracle type
  private static int arrayPhpToOracleType[];

  static {
    try {
      classOracleTypes = Class.forName("oracle.jdbc.OracleTypes");

      arrayPhpToOracleType = new int[] {
        -1,
        classOracleTypes.getDeclaredField("BFILE").getInt(null), // OCI_B_BFILE
        -1, // OCI_B_CFILEE
        classOracleTypes.getDeclaredField("CLOB").getInt(null), // OCI_B_CLOB
        classOracleTypes.getDeclaredField("BLOB").getInt(null), // OCI_B_BLOB
        classOracleTypes.getDeclaredField("ROWID").getInt(null), // OCI_B_ROWID
        classOracleTypes.getDeclaredField("CURSOR").getInt(null), // OCI_B_CURSOR
        classOracleTypes.getDeclaredField("OTHER").getInt(null), // OCI_B_NTY
        classOracleTypes.getDeclaredField("RAW").getInt(null), // OCI_B_BIN
        -1, // OCI_DTYPE_FILE
        -1, // OCI_DTYPE_LOB
        -1, // OCI_DTYPE_ROWID
        -1, // OCI_D_FILE
        -1, // OCI_D_LOB
        -1, // OCI_D_ROWID
        classOracleTypes.getDeclaredField("TIMESTAMP").getInt(null), // OCI_SYSDATE
        -1, // OCI_TEMP_CLOB
        -1, // OCI_TEMP_BLOB
        classOracleTypes.getDeclaredField("BFILE").getInt(null), // SQLT_BFILEE
        -1, // SQLT_CFILEE
        classOracleTypes.getDeclaredField("CLOB").getInt(null), // SQLT_CLOB
        classOracleTypes.getDeclaredField("BLOB").getInt(null), // SQLT_BLOB
        classOracleTypes.getDeclaredField("ROWID").getInt(null), // SQLT_RDD
        classOracleTypes.getDeclaredField("OTHER").getInt(null), // SQLT_NTY
        classOracleTypes.getDeclaredField("NUMBER").getInt(null), // SQLT_LNG
        classOracleTypes.getDeclaredField("RAW").getInt(null), // SQLT_LBI
        classOracleTypes.getDeclaredField("RAW").getInt(null), // SQLT_BIN
        classOracleTypes.getDeclaredField("NUMBER").getInt(null), // SQLT_NUM
        classOracleTypes.getDeclaredField("INTEGER").getInt(null), // SQLT_INT
        classOracleTypes.getDeclaredField("CHAR").getInt(null), // SQLT_AFC
        classOracleTypes.getDeclaredField("CHAR").getInt(null), // SQLT_CHR
        classOracleTypes.getDeclaredField("VARCHAR").getInt(null), // SQLT_VCS
        classOracleTypes.getDeclaredField("CHAR").getInt(null), // SQLT_AVC
        classOracleTypes.getDeclaredField("VARCHAR").getInt(null), // SQLT_STR
        classOracleTypes.getDeclaredField("LONGVARCHAR").getInt(null), // SQLT_LVC
        classOracleTypes.getDeclaredField("FLOAT").getInt(null), // SQLT_FLT
        classOracleTypes.getDeclaredField("DATE").getInt(null), // SQLT_ODT
        classOracleTypes.getDeclaredField("DOUBLE").getInt(null), // SQLT_BDOUBLE
        classOracleTypes.getDeclaredField("FLOAT").getInt(null), // SQLT_BFLOAT
        classOracleTypes.getDeclaredField("CURSOR").getInt(null), // SQLT_RSET
        classOracleTypes.getDeclaredField("BFILE").getInt(null), // SQLT_FILE
        -1 // SQLT_CFILE
      };
    } catch (Exception e) {
      L.l("Unable to load Oracle types from oracle.jdbc.OracleTypes. Check your Oracle JDBC driver version.");
    }
  }

  public OracleModule()
  {
  }

  /**
   * Returns true for the oracle extension.
   */
  public String []getLoadedExtensions()
  {
    return new String[] { "oci8" };
  }

  /**
   * Binds PHP array to Oracle PL/SQL array by name.
   *
   * oci_bind_array_by_name() binds the PHP array
   * varArray to the Oracle placeholder name, which
   * points to Oracle PL/SQL array. Whether it will
   * be used for input or output will be determined
   * at run-time. The maxTableLength parameter sets
   * the maximum length both for incoming and result
   * arrays. Parameter maxItemLength sets maximum
   * length for array items. If maxItemLength was
   * not specified or equals to -1,
   * oci_bind_array_by_name() will find the longest
   * element in the incoming array and will use it as
   * maximum length for array items. type parameter
   * should be used to set the type of PL/SQL array
   * items. See list of available types below.
   *
   * @param env the PHP executing environment
   * @param stmt the Oracle statement
   * @param name the Oracle placeholder
   * @param varArray the array to be binded
   * @param maxTableLength maximum table length
   * @param maxItemLength maximum item length
   * @param type one of the following types:
   * <br/>
   * SQLT_NUM - for arrays of NUMBER.
   * <br/>
   * SQLT_INT - for arrays of INTEGER
   * (Note: INTEGER it is actually a synonym for
   *  NUMBER(38), but SQLT_NUM type won't work in
   *  this case even though they are synonyms).
   * <br/>
   * SQLT_FLT - for arrays of FLOAT.
   * <br/>
   * SQLT_AFC - for arrays of CHAR.
   * <br/>
   * SQLT_CHR - for arrays of VARCHAR2.
   * <br/>
   * SQLT_VCS - for arrays of VARCHAR.
   * <br/>
   * SQLT_AVC - for arrays of CHARZ.
   * <br/>
   * SQLT_STR - for arrays of STRING.
   * <br/>
   * SQLT_LVC - for arrays of LONG VARCHAR.
   * <br/>
   * SQLT_ODT - for arrays of DATE.
   *
   * @return true on success of false on failure
   */
  public static boolean oci_bind_array_by_name(Env env,
                                               @NotNull OracleStatement stmt,
                                               @NotNull String name,
                                               @NotNull ArrayValue varArray,
                                               @NotNull int maxTableLength,
                                               @Optional("0") int maxItemLength,
                                               @Optional("0") int type)
  {
    try {

      // JDBC underlying connection
      Connection conn = stmt.getJavaConnection();

      // Oracle underlying statement
      PreparedStatement oracleStmt = stmt.getPreparedStatement();

      // Create an oracle.sql.ARRAY object to hold the values
      // oracle.sql.ArrayDescriptor arrayDesc =
      //   oracle.sql.ArrayDescriptor.createDescriptor("number_varray", conn);


      Class clArrayDescriptor = Class.forName("oracle.sql.ArrayDescriptor");

      Method method
        = clArrayDescriptor.getDeclaredMethod("createDescriptor",
                                              new Class[] {String.class, Connection.class});

      Object arrayDesc = method.invoke(clArrayDescriptor,
                                       new Object[] {"NUMBER_VARRAY", conn});

      Value valueArray[] = varArray.valuesToArray(); // int arrayValues[] = {123, 234};

      Object objectArray[] = new Object[5]; // {"aaa", "bbb", "ccc"};
      for (int i=0; i<valueArray.length; i++) {
        Object obj = valueArray[i].toJavaObject();
        objectArray[i] = obj;
      }

      // oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn, arrayValues);

      Class clARRAY = Class.forName("oracle.sql.ARRAY");

      Constructor constructor = clARRAY.getDeclaredConstructor(new Class[] {
        clArrayDescriptor, Connection.class, Object.class});

      Array oracleArray = (Array) constructor.newInstance(new Object[]
        {arrayDesc, conn, objectArray});

      // Bind array
      // ((oracle.jdbc.OraclePreparedStatement)oracleStmt).setARRAY(1, array);

      // cl = Class.forName("oracle.jdbc.OraclePreparedStatement");

      // method = cl.getDeclaredMethod("setARRAY",
      //                              new Class[] {Integer.TYPE, Object[].class});

      if (name == null) {
        return false;
      }

      if (!name.startsWith(":")) {
        name = ":" + name;
      }

      if (name.length() < 2) {
        return false;
      }

      // method.invoke(oracleStmt, new Object[] {name, oracleArray});

      Integer index = stmt.getBindingVariable(name);

      if (index == null)
        return false;

      int i = index.intValue();
      Object object = varArray.toJavaObject();

      if (object instanceof OracleOciCollection) {
        oracleArray = ((OracleOciCollection) object).getCollection();
        oracleStmt.setArray(i, oracleArray);
      } else if (varArray instanceof ArrayValueImpl) {
        // oracleStmt.setObject(i, varArray.getKeyArray());
        // Object objectArray[] = new Object[] {"aaa", "bbb", "ccc"};
        // oracleStmt.setObject(i, objectArray);
        oracleStmt.setArray(i, oracleArray);
      } else {
        oracleStmt.setObject(i, object);
      }

      // drop descriptor ???? 'number_varray' ????

      return true;

    } catch (Exception e) {
      log.log(Level.FINE, e.toString(), e);
      return false;
    }
  }

  /**
   * Binds the PHP variable to the Oracle placeholder
   *
   * @param type one of the following types:
   *
   * SQLT_INT - for integers;
   *
   * SQLT_CHR - for VARCHARs;
   *
   * SQLT_RSET - for cursors, that were created before with oci_new_cursor()
   *
   * OCI_B_BFILE (integer)
   *
   *    Used with oci_bind_by_name() when binding BFILEs.
   *
   * OCI_B_CFILEE (integer)
   *
   *    Used with oci_bind_by_name() when binding CFILEs.
   *
   * OCI_B_CLOB (integer)
   *
   *    Used with oci_bind_by_name() when binding CLOBs.
   *
   * OCI_B_BLOB (integer)
   *
   *    Used with oci_bind_by_name() when binding BLOBs.
   *
   * OCI_B_ROWID (integer)
   *
   *    Used with oci_bind_by_name() when binding ROWIDs.
   *
   * OCI_B_CURSOR (integer)
   *
   *    Used with oci_bind_by_name() when binding cursors,
   *    previously allocated with oci_new_descriptor().
   *
   * OCI_B_NTY (integer)
   *
   *    Used with oci_bind_by_name() when binding named data
   *    types. Note: in PHP < 5.0 it was called OCI_B_SQLT_NTY.
   *
   * OCI_B_BIN (integer)
   *
   * SQLT_FILE (integer)
   *
   * SQLT_BFILEE (integer)
   *
   *    The same as OCI_B_BFILE.
   *
   * SQLT_CFILE (integer)
   *
   * SQLT_CFILEE (integer)
   *
   *    The same as OCI_B_CFILEE.
   *
   * SQLT_CLOB (integer)
   *
   *    The same as OCI_B_CLOB.
   *
   * SQLT_BLOB (integer)
   *
   *    The same as OCI_B_BLOB.
   *
   * SQLT_RDD (integer)
   *
   *    The same as OCI_B_ROWID.
   *
   * SQLT_NTY (integer)
   *
   *    The same as OCI_B_NTY.
   *
   * SQLT_LNG (integer)
   *
   *    Used with oci_bind_by_name() to bind LONG values.
   *
   * SQLT_LBI (integer)
   *
   *    Used with oci_bind_by_name() to bind LONG RAW values.
   *
   * SQLT_BIN (integer)
   *