patch - support for multi-dimensional arrays and NULL values - Mailing list pgsql-jdbc

Hello Kris and hello group,
in the attachment you can find AbstractJdbc2Array class with two fixed
features: null value as element of an array and... multi-dimensional
arrays support. There are two things related with this two changes:
- all arrays are objects arrays (which means that e.g. boolean[] is
represented as Boolean[])
- when multi-dimensional array is processed then Object[] is returned by
getArray() - it's because we can't declare in Java an array of unknown
dimension (so if PostgreSQL returns char[][] then jdbc will return
Object[], that contains String[] elements)

Sorry that I didn't send patch file, but I didn't have time to install
cvs client (I'm using svn in everyday work).

In case of questions or any other discussion write me an email.

Best regards,
Marek Lewczuk



/*-------------------------------------------------------------------------
*
* Copyright (c) 2004-2005, PostgreSQL Global Development Group
*
* IDENTIFICATION
*   $PostgreSQL: pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Array.java,v 1.18 2005/12/04 21:40:33 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresql.jdbc2;

import org.postgresql.core.*;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import org.postgresql.util.GT;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Map;
import java.util.Vector;
import java.util.GregorianCalendar;

/**
 * Array is used collect one column of query result data.
 *
 * <p>Read a field of type Array into either a natively-typed
 * Java array object or a ResultSet.  Accessor methods provide
 * the ability to capture array slices.
 *
 * <p>Other than the constructor all methods are direct implementations
 * of those specified for java.sql.Array.  Please refer to the javadoc
 * for java.sql.Array for detailed descriptions of the functionality
 * and parameters of the methods of this class.
 *
 * @see ResultSet#getArray
 */


public class AbstractJdbc2Array
{
    private BaseConnection conn = null;
    private Field field = null;
    private BaseResultSet rs;
    private int idx = 0;
    private String rawString = null;

    /**
     * Create a new Array
     *
     * @param conn a database connection
     * @param idx 1-based index of the query field to load into this Array
     * @param field the Field descriptor for the field to load into this Array
     * @param rs the ResultSet from which to get the data for this Array
     */
    public AbstractJdbc2Array(BaseConnection conn, int idx, Field field, BaseResultSet rs )
    throws SQLException
    {
        this.conn = conn;
        this.field = field;
        this.rs = rs;
        this.idx = idx;
        this.rawString = rs.getFixedString(idx);
    }

    public Object getArray() throws SQLException
    {
        return getArrayImpl( 1, 0, null );
    }

    public Object getArray(long index, int count) throws SQLException
    {
        return getArrayImpl( index, count, null );
    }

    public Object getArrayImpl(Map map) throws SQLException
    {
        return getArrayImpl( 1, 0, map );
    }

    public Object getArrayImpl(long index, int count, Map map) throws SQLException
    {
        if ( map != null && !map.isEmpty()) // For now maps aren't supported.
            throw org.postgresql.Driver.notImplemented(this.getClass(), "getArrayImpl(long,int,Map)");

        if (index < 1)
            throw new PSQLException(GT.tr("The array index is out of range: {0}", new Long(index)),
PSQLState.DATA_ERROR);
        Object retVal = null;

        ArrayList array = new ArrayList();

        /* Check if the String is also not an empty array
              * otherwise there will be an exception thrown below
              * in the ResultSet.toX with an empty string.
              * -- Doug Fields <dfields-pg-jdbc@pexicom.com> Feb 20, 2002 */

        if ( rawString != null && !rawString.equals("{}") )
        {
            char[] chars = rawString.toCharArray();
            StringBuffer sbuf = new StringBuffer();
            boolean insideString = false;
            boolean wasInsideString = false; // needed for checking if NULL value occured
            Vector dims = new Vector(); // array dimension arrays
            ArrayList curArray = array; // currently processed array

            /**
             * Starting with 8.0 non-standard (beginning index
             * isn't 1) bounds the dimensions are returned in the
             * data formatted like so "[0:3]={0,1,2,3,4}".
             * Older versions simply do not return the bounds.
             *
             * Right now we ignore these bounds, but we could
             * consider allowing these index values to be used
             * even though the JDBC spec says 1 is the first
             * index.  I'm not sure what a client would like
             * to see, so we just retain the old behavior.
             */
            int startOffset = 0;
            if (chars[0] == '[')
            {
                while (chars[startOffset] != '=')
                {
                    startOffset++;
                }
                startOffset++; // skip =
            }

            for ( int i = startOffset; i < chars.length; i++ )
            {
                if ( chars[i] == '\\' )
                    //escape character that we need to skip
                    i++;
                else if (!insideString && chars[i] == '{' )
                {
                    if (dims.size() == 0) dims.add(array);
                    else {
                        ArrayList a = new ArrayList();
                        ((ArrayList) dims.lastElement()).add(a);
                        dims.add(a);
                    }
                    curArray = (ArrayList) dims.lastElement();
                    sbuf = new StringBuffer();
                    continue;
                }
                else if (chars[i] == '"')
                {
                    insideString = !insideString;
                    wasInsideString = true;
                    continue;
                }
                else if (!insideString && (chars[i] == ',' || chars[i] == '}') ||
                         i == chars.length - 1)
                {
                    if ( chars[i] != '"' && chars[i] != '}' && chars[i] != ',' && sbuf != null)
                        sbuf.append(chars[i]);
                    String b = sbuf == null ? null : sbuf.toString();
                    if (b != null) curArray.add(wasInsideString == false && b.equals("NULL") ? null : b);
                    wasInsideString = false;
                    sbuf = new StringBuffer();
                    if (chars[i] == '}') {
                        dims.remove(dims.size() - 1);
                        if (dims.size() > 0) curArray = (ArrayList) dims.lastElement();
                        sbuf = null;
                    }
                    continue;
                }
                if (sbuf != null) sbuf.append( chars[i] );
            }
        }

        // convert
        if ( count == 0 )
            count = array.size();
        index--;
        if ( index + count > array.size() )
            throw new PSQLException(GT.tr("The array index is out of range: {0}, number of elements: {1}.", new
Object[]{newLong(index + count), new Long(array.size())}), PSQLState.DATA_ERROR); 
        return buildArray(array, (int) index, count);
    }

    /**
     * Convert ArrayList to array.
     */
    private Object[] buildArray (ArrayList _input, int index, int count) throws SQLException {
        int i = 0;
        if (count == -1) count = _input.size();
        Object[] retVal = null;
        boolean multi = false;
        for (int z = 0; z < count; z++) {
            Object x = _input.get(z);
            if (x == null) continue;
            else if (x instanceof ArrayList) multi = true;
            break;
        }
        switch ( getBaseType() )
        {
        case Types.BIT:
            retVal = multi ? new Object[count] : new Boolean[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toBoolean((String)v); 
            }
            break;
        case Types.SMALLINT:
        case Types.INTEGER:
            retVal = multi ? new Object[count] : new Integer[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toInt((String)v); 
            }
            break;
        case Types.BIGINT:
            retVal = multi ? new Object[count] : new Long[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toLong((String)v); 
            }
            break;
        case Types.NUMERIC:
            retVal = multi ? new Object[count] : new BigDecimal[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toBigDecimal((String)v, -1); 
            }
            break;
        case Types.REAL:
            retVal = multi ? new Object[count] : new Float[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toFloat((String)v); 
            }
            break;
        case Types.DOUBLE:
            retVal = multi ? new Object[count] : new Double[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
AbstractJdbc2ResultSet.toDouble((String)v); 
            }
            break;
        case Types.CHAR:
        case Types.VARCHAR:
            retVal = multi ? new Object[count] : new String[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) : v;
            }
            break;
        case Types.DATE:
            retVal = multi ? new Object[count] : new java.sql.Date[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
conn.getTimestampUtils().toDate(null,(String) v); 
            }
            break;
        case Types.TIME:
            retVal = multi ? new Object[count] : new java.sql.Time[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
conn.getTimestampUtils().toTime(null,(String) v); 
            }
            break;
        case Types.TIMESTAMP:
            retVal = multi ? new Object[count] : new java.sql.Timestamp[count];
            for ( ; count > 0; count-- ) {
                Object v = _input.get(index++);
                retVal[i++] = multi && v != null ? buildArray((ArrayList) v, 0, -1) :
conn.getTimestampUtils().toTimestamp(null,(String) v); 
            }
            break;

            // Other datatypes not currently supported.  If you are really using other types ask
            // yourself if an array of non-trivial data types is really good database design.
        default:
            if (conn.getLogger().logDebug())
                conn.getLogger().debug("getArrayImpl(long,int,Map) with "+getBaseTypeName());
            throw org.postgresql.Driver.notImplemented(this.getClass(), "getArrayImpl(long,int,Map)");
        }

        return retVal;

    }

    public int getBaseType() throws SQLException
    {
        return conn.getSQLType(getBaseTypeName());
    }

    public String getBaseTypeName() throws SQLException
    {
        String fType = conn.getPGType(field.getOID());
        if ( fType.charAt(0) == '_' )
            fType = fType.substring(1);
        return fType;
    }

    public java.sql.ResultSet getResultSet() throws SQLException
    {
        return getResultSetImpl( 1, 0, null );
    }

    public java.sql.ResultSet getResultSet(long index, int count) throws SQLException
    {
        return getResultSetImpl( index, count, null );
    }

    public java.sql.ResultSet getResultSetImpl(Map map) throws SQLException
    {
        return getResultSetImpl( 1, 0, map );
    }

    private void fillIntegerResultSet(long index, int[] intArray, Vector rows) throws SQLException
    {
        for ( int i = 0; i < intArray.length; i++ )
        {
            byte[][] tuple = new byte[2][0];
            tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
            tuple[1] = conn.encodeString( Integer.toString(intArray[i]) ); // Value
            rows.addElement(tuple);
        }
    }

    private void fillStringResultSet(long index, String[] strArray, Vector rows) throws SQLException
    {
        for ( int i = 0; i < strArray.length; i++ )
        {
            byte[][] tuple = new byte[2][0];
            tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
            tuple[1] = conn.encodeString( strArray[i] ); // Value
            rows.addElement(tuple);
        }
    }

    public java.sql.ResultSet getResultSetImpl(long index, int count, java.util.Map map) throws SQLException
    {
        Object array = getArrayImpl( index, count, map );
        Vector rows = new Vector();
        Field[] fields = new Field[2];
        fields[0] = new Field("INDEX", Oid.INT2);
        switch ( getBaseType() )
        {
        case Types.BIT:
            boolean[] booleanArray = (boolean[]) array;
            fields[1] = new Field("VALUE", Oid.BOOL);
            for ( int i = 0; i < booleanArray.length; i++ )
            {
                byte[][] tuple = new byte[2][0];
                tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
                tuple[1] = conn.encodeString( (booleanArray[i] ? "YES" : "NO") ); // Value
                rows.addElement(tuple);
            }
            break;
        case Types.SMALLINT:
            fields[1] = new Field("VALUE", Oid.INT2);
            fillIntegerResultSet(index, (int[])array, rows);
            break;
        case Types.INTEGER:
            fields[1] = new Field("VALUE", Oid.INT4);
            fillIntegerResultSet(index, (int[])array, rows);
            break;
        case Types.BIGINT:
            long[] longArray = (long[]) array;
            fields[1] = new Field("VALUE", Oid.INT8);
            for ( int i = 0; i < longArray.length; i++ )
            {
                byte[][] tuple = new byte[2][0];
                tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
                tuple[1] = conn.encodeString( Long.toString(longArray[i]) ); // Value
                rows.addElement(tuple);
            }
            break;
        case Types.NUMERIC:
            BigDecimal[] bdArray = (BigDecimal[]) array;
            fields[1] = new Field("VALUE", Oid.NUMERIC);
            for ( int i = 0; i < bdArray.length; i++ )
            {
                byte[][] tuple = new byte[2][0];
                tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
                tuple[1] = conn.encodeString( bdArray[i].toString() ); // Value
                rows.addElement(tuple);
            }
            break;
        case Types.REAL:
            float[] floatArray = (float[]) array;
            fields[1] = new Field("VALUE", Oid.FLOAT4);
            for ( int i = 0; i < floatArray.length; i++ )
            {
                byte[][] tuple = new byte[2][0];
                tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
                tuple[1] = conn.encodeString( Float.toString(floatArray[i]) ); // Value
                rows.addElement(tuple);
            }
            break;
        case Types.DOUBLE:
            double[] doubleArray = (double[]) array;
            fields[1] = new Field("VALUE", Oid.FLOAT8);
            for ( int i = 0; i < doubleArray.length; i++ )
            {
                byte[][] tuple = new byte[2][0];
                tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
                tuple[1] = conn.encodeString( Double.toString(doubleArray[i]) ); // Value
                rows.addElement(tuple);
            }
            break;
        case Types.CHAR:
            fields[1] = new Field("VALUE", Oid.BPCHAR);
            fillStringResultSet(index, (String[])array, rows);
            break;
        case Types.VARCHAR:
            fields[1] = new Field("VALUE", Oid.VARCHAR);
            fillStringResultSet(index, (String[])array, rows);
            break;
        case Types.DATE:
            java.sql.Date[] dateArray = (java.sql.Date[]) array;
            fields[1] = new Field("VALUE", Oid.DATE);
            for ( int i = 0; i < dateArray.length; i++ )
            {
                byte[][] tuple = new byte[2][0];
                tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
                tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, dateArray[i]) ); // Value
                rows.addElement(tuple);
            }
            break;
        case Types.TIME:
            java.sql.Time[] timeArray = (java.sql.Time[]) array;
            fields[1] = new Field("VALUE", Oid.TIME);
            for ( int i = 0; i < timeArray.length; i++ )
            {
                byte[][] tuple = new byte[2][0];
                tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
                tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, timeArray[i]) ); // Value
                rows.addElement(tuple);
            }
            break;
        case Types.TIMESTAMP:
            java.sql.Timestamp[] timestampArray = (java.sql.Timestamp[]) array;
            fields[1] = new Field("VALUE", Oid.TIMESTAMPTZ);
            for ( int i = 0; i < timestampArray.length; i++ )
            {
                byte[][] tuple = new byte[2][0];
                tuple[0] = conn.encodeString( Integer.toString((int)index + i) ); // Index
                tuple[1] = conn.encodeString( conn.getTimestampUtils().toString(null, timestampArray[i]) ); // Value
                rows.addElement(tuple);
            }
            break;

            // Other datatypes not currently supported.  If you are really using other types ask
            // yourself if an array of non-trivial data types is really good database design.
        default:
            if (conn.getLogger().logDebug())
                conn.getLogger().debug("getResultSetImpl(long,int,Map) with "+getBaseTypeName());
            throw org.postgresql.Driver.notImplemented(this.getClass(), "getResultSetImpl(long,int,Map)");
        }
        BaseStatement stat = (BaseStatement) conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
        return (ResultSet) stat.createDriverResultSet(fields, rows);
    }

    public String toString()
    {
        return rawString;
    }
}