postgresmodule.java

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

      ResultSetMetaData rsMetaData = rs.getMetaData();
      int n = rsMetaData.getColumnCount();
      if (n < assocArray.getSize())
        return null;

      ArrayValueImpl newArray = new ArrayValueImpl();

      // Keep track of column matches: assocArray vs. table columns
      int matches = 0;

      while (rs.next()) {
        // Retrieve the original value to be converted
        String columnName = rs.getString("COLUMN_NAME");
        Value columnNameV = StringValue.create(columnName);
        Value value = assocArray.get(columnNameV);

        // Check for column not passed in
        if (value == UnsetValue.UNSET)
          continue;

        matches++;

        if (value.isNull()) {
          value = StringValue.create("NULL");
          // Add the converted value
          newArray.put(columnNameV, value);
          continue;
        }

        // Retrieve the database column type
        int dataType = rs.getInt("DATA_TYPE");

        // Convert the original value to the database type
        switch (dataType) {
        case Types.BIT:
        case Types.TINYINT:
        case Types.SMALLINT:
        case Types.INTEGER:
        case Types.BIGINT:
          if (value.isLongConvertible()) {
            value = LongValue.create(value.toLong());
          } else {
            StringValue sb = env.createUnicodeBuilder();
            value = sb.append("'").append(value).append("'");
          }
          break;

        case Types.DECIMAL:
        case Types.DOUBLE:
        case Types.FLOAT:
        case Types.NUMERIC:
        case Types.REAL:
          if (value.isDoubleConvertible()) {
            value = DoubleValue.create(value.toDouble());
          } else {
            StringValue sb = env.createUnicodeBuilder();
            value = sb.append("'").append(value).append("'");
          }
          break;

        default:
          StringValue sb = env.createUnicodeBuilder();
          if (value.isNumberConvertible())  {
            value = sb.append(value);
          } else {
            value = sb.append("'").append(value).append("'");
          }
        }

        // Add the converted value
        newArray.put(columnNameV, value);
      }

      rs.close();

      // Check if all columns were consumed. Otherwise, there are
      // wrong column names passed in.
      if (matches < assocArray.getSize()) {
        return null;
      }

      return newArray;

    } catch (Exception ex) {
      log.log(Level.FINE, ex.toString(), ex);
      return null;
    }
  }

  /**
   * Insert records into a table from an array
   */
  public static boolean pg_copy_from(Env env,
                                     @NotNull Postgres conn,
                                     String tableName,
                                     ArrayValue rows,
                                     @Optional("") String delimiter,
                                     @Optional("") String nullAs)
  {
    try {

      // XXX: At the time this was implemented, the JDBC driver
      // did not support SQL COPY operations that could simplify
      // the code below.

      String delimiterRegex;
      if (delimiter.equals("")) {
        delimiter = "\t";
        delimiterRegex = "\\t";
      } else {
        // XXX: even the native php version does not seem to do it very well.
        throw new UnimplementedException("pg_copy_from with non-default delimiter");
      }

      if (nullAs.equals("")) {
        nullAs = "\\N";
      } else {
        // XXX: even the native php version does not seem to do it very well.
        throw new UnimplementedException("pg_copy_from with non-default nullAs");
      }

      ArrayValueImpl array = (ArrayValueImpl) rows;
      int size = array.size();

      String baseInsert = "INSERT INTO " + tableName + " VALUES(";

      StringBuilder sb = new StringBuilder(baseInsert);

      int lenBaseInsert = sb.length();

      for (int i=0; i<size; i++) {
        // Every line has a new-line '\n' character and
        // possibly many NULL values "\\N". Ex:
        // line = "\\N\tNUMBER1col\t\\N\t\\N\tNUMBER2col\tNUMBER3col\tNUMBER4col\t\\N\n";
        String line = array.get(LongValue.create(i)).toString();
        line = line.substring(0, line.length()-1);

        // "INSERT INTO " + tableName + " VALUES("
        sb.setLength(lenBaseInsert);

        // Split columns
        String cols[] = line.split(delimiterRegex);

        int len = cols.length;

        if (len > 0) {

          len--;

          for (int j=0; j<len; j++) {
            if (cols[j].equals(nullAs)) {
              sb.append("NULL, ");
            } else {
              sb.append("'");
              sb.append(cols[j]);
              sb.append("', ");
            }
          }

          if (cols[len].equals(nullAs)) {
            sb.append("NULL)");
          } else {
            sb.append("'");
            sb.append(cols[len]);
            sb.append("')");
          }

          // Insert record
          pg_query(env, conn, sb.toString());
        }
      }

      return true;

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

  /**
   * Copy a table to an array
   */
  @ReturnNullAsFalse
  public static ArrayValue pg_copy_to(Env env,
                                      @NotNull Postgres conn,
                                      String tableName,
                                      @Optional("") String delimiter,
                                      @Optional("") String nullAs)
  {
    try {

      // XXX: At the time this was implemented, the JDBC driver
      // did not support SQL COPY operations that could simplify
      // the code below.

      // XXX: This should be replaced when @Optional("\t") is fixed.
      if (delimiter.equals("")) {
        delimiter = "\t";
      }

      // XXX: This should be replaced when @Optional("\\N") is fixed.
      // Note: according to php.net, it must be \\N, i.e. the
      // two-character sequence: {'\\', 'N'}
      if (nullAs.equals("")) {
        nullAs = "\\N";
      }

      PostgresResult result = pg_query(env, conn, "SELECT * FROM " + tableName);

      ArrayValueImpl newArray = new ArrayValueImpl();

      Object value;

      int curr = 0;

      while ((value = result.fetchArray(env, PGSQL_NUM)) != null) {

        ArrayValueImpl arr = (ArrayValueImpl) value;
        int count = arr.size();

        StringValue sb = env.createUnicodeBuilder();

        LongValue currValue = LongValue.create(curr);

        for (int i=0; i<count; i++) {

          if (sb.length() > 0)
            sb.append(delimiter);

          Value v = newArray.get(currValue);

          Value fieldValue = arr.get(LongValue.create(i));

          if (fieldValue instanceof NullValue) {
            sb.append(nullAs);
          } else {
            sb.append(fieldValue.toString());
          }
        }

        // Every line has a new-line character.
        sb.append("\n");

        newArray.put(currValue, sb);

        curr++;
      }

      return newArray;

    } catch (Exception ex) {
      log.log(Level.FINE, ex.toString(), ex);
      return null;
    }
  }

  /**
   * Get the database name
   */
  @ReturnNullAsFalse
  public static String pg_dbname(Env env,
                                 @Optional Postgres conn)
  {
    try {
      if (conn == null)
        conn = getConnection(env);

      return conn.getDbName();

    } catch (Exception ex) {
      log.log(Level.FINE, ex.toString(), ex);
      return null;
    }
  }

  /**
   * Deletes records
   */
  public static boolean pg_delete(Env env,
                                  @NotNull Postgres conn,
                                  String tableName,
                                  ArrayValue assocArray,
                                  @Optional("-1") int options)
  {
    // From php.net: this function is EXPERIMENTAL.
    // This function is EXPERIMENTAL. The behaviour of this function,
    // the name of this function, and anything else documented about this function
    // may change without notice in a future release of PHP.
    // Use this function at your own risk.

    try {

      if (options > 0) {
        throw new UnimplementedException("pg_delete with options");
      }

      StringBuilder condition = new StringBuilder();

      boolean isFirst = true;

      for (Map.Entry<Value,Value> entry : assocArray.entrySet()) {
        Value k = entry.getKey();
        Value v = entry.getValue();
        if (isFirst) {
          isFirst = false;
        } else {
          condition.append(" AND ");
        }
        condition.append(k.toString());
        condition.append("='");
        condition.append(v.toString());
        condition.append("'");
      }

      StringBuilder query = new StringBuilder();
      query.append("DELETE FROM ");
      query.append(tableName);
      query.append(" WHERE ");
      query.append(condition);

      pg_query(env, conn, query.toString());

      return true;

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

  /**
   * Sync with PostgreSQL backend
   */
  public static boolean pg_end_copy(Env env,
                                    @Optional Postgres conn)
  {
    env.stub("pg_end_copy");

    return false;
  }

  /**
   * Escape a string for insertion into a bytea field
   */
  @ReturnNullAsFalse
  public static StringValue pg_escape_bytea(Env env,
                                            StringValue data)
  {
    if (data.length() == 0)
      return data;

    try {
      Class cl = Class.forName("org.postgresql.util.PGbytea");

      Method method = cl.getDeclaredMethod("toPGString", new Class[] {byte[].class});

      String s = (String) method.invoke(cl, new Object[] { data.toBytes()});

      return Postgres.pgRealEscapeString(env.createString(s));

    } catch (Exception ex) {
      log.log(Level.FINE, ex.toString(), ex);
      return null;
    }
  }

  /**
   * Escape a string for insertion into a bytea field.
   */
  @ReturnNullAsFalse
  public static StringValue pg_escape_bytea(Env env,
                                            @NotNull Postgres conn,
                                            StringValue data)
  {
    return pg_escape_bytea(env, data);
  }

  /**
   * Escape a string for insertion into a text field
   */
  @ReturnNullAsFalse
  public static StringValue pg_escape_string(Env env,
                                             StringValue data)
  {
    try {

      Postgres conn = getConnection(env);

      if (conn == null)
        return null;

      return conn.realEscapeString(data);

    } catch (Exception ex) {
      log.log(Level.FINE, ex.toString(), ex);
      return null;
    }
  }

  /**
   * Sends a request to execute a prepared statement with given parameters,
   * and waits for the result
   */
  @ReturnNullAsFalse
  public static PostgresResult pg_execute(Env env,
                                          @NotNull Postgres conn,
                                          String stmtName,
                                          ArrayValue params)
  {
    try {

      PostgresStatement pstmt = conn.getStatement(stmtName);

      return executeInternal(env, conn, pstmt, params);

    } catch (Exception ex) {
      log.log(Level.FINE, ex.toString(), ex);
      conn.setResultResource(null);
      return null;
    }
  }

  /**
   * Fetches all rows in a particular result column as an array
   */
  @ReturnNullAsFalse
  public static ArrayValue pg_fetch_all_columns(Env env,
                                                @NotNull PostgresResult result,
                                                @Optional("0") int column)
  {
    try {

      ArrayValueImpl newArray = new ArrayValueImpl();

      int curr = 0;

      for (ArrayValue row = result.fetchRow(env);
           row != null;
           row = result.fetchRow(env)) {

        newArray.put(LongValue.create(curr++),
                     row.get(LongValue.create(column)));

      }

      if (newArray.getSize() > 0) {
        return newArray;
      }

    } catch (Exception ex) {
      log.log(Level.FINE, ex.toString(), ex);
    }

    return null;
  }

  /**
   * Fetches all rows from a result as an array
   */
  @ReturnNullAsFalse
  public static ArrayValue pg_fetch_all(Env env,
                                        @NotNull PostgresResult result)
  {
    try {

      ArrayValueImpl newArray = new ArrayValueImpl();

      int curr = 0;

      for (ArrayValue row = result.fetchAssoc(env);
           row != null;
           row = result.fetchAssoc(env)) {

        newArray.put(LongValue.create(curr++), row);

      }

      if (newArray.getSize() > 0) {
        return newArray;
      }

    } catch (Exception ex) {
      log.log(Level.FINE, ex.toString(), ex);
    }

    return null;
  }

  /**
   * Fetch a row as an array
   */
  @ReturnNullAsFalse
  public static ArrayValue pg_fetch_array(Env env,
                                          @NotNull PostgresResult result,
                                          @Optional("-1") Value row,
                                          @Optional("PGSQL_BOTH") int resultType)