/*-------------------------------------------------------------------------
*
* Copyright (c) 2004-2008, PostgreSQL Global Development Group
*
* IDENTIFICATION
*   $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/ArrayTest.java,v 1.19 2009/03/03 05:33:04 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresql.test.jdbc2;

import org.postgresql.test.TestUtil;
import java.sql.*;
import java.math.BigDecimal;

import org.postgresql.geometric.PGbox;
import org.postgresql.geometric.PGpoint;

import junit.framework.TestCase;

public class ArrayTest extends TestCase
{
    private Connection conn;

    public ArrayTest(String name)
    {
        super(name);
    }

    protected void setUp() throws Exception
    {
        conn = TestUtil.openDB();
        TestUtil.createTable(conn, "arrtest", "intarr int[], decarr decimal(2,1)[], strarr text[]");
    }

    protected void tearDown() throws SQLException
    {
        TestUtil.dropTable(conn, "arrtest");
        TestUtil.closeDB(conn);
    }

    public void testSetNull() throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO arrtest VALUES (?,?,?)");
        pstmt.setNull(1, Types.ARRAY);
        pstmt.setNull(2, Types.ARRAY);
        pstmt.setNull(3, Types.ARRAY);
        pstmt.executeUpdate();

        pstmt.setObject(1, null, Types.ARRAY);
        pstmt.setObject(2, null);
        pstmt.setObject(3, null);
        pstmt.executeUpdate();

        pstmt.setArray(1, null);
        pstmt.setArray(2, null);
        pstmt.setArray(3, null);
        pstmt.executeUpdate();

        pstmt.close();
    }


    public void testRetrieveArrays() throws SQLException {
        Statement stmt = conn.createStatement();

        // you need a lot of backslashes to get a double quote in.
        stmt.executeUpdate("INSERT INTO arrtest VALUES ('{1,2,3}','{3.1,1.4}', '"
                + TestUtil.escapeString(conn, "{abc,f'a,\"fa\\\"b\",def}") + "')");

        ResultSet rs = stmt.executeQuery("SELECT intarr, decarr, strarr FROM arrtest");
        assertTrue(rs.next());

        Array arr = rs.getArray(1);
        assertEquals(Types.INTEGER, arr.getBaseType());
        Integer intarr[] = (Integer[])arr.getArray();
        assertEquals(3, intarr.length);
        assertEquals(1, intarr[0].intValue());
        assertEquals(2, intarr[1].intValue());
        assertEquals(3, intarr[2].intValue());

        arr = rs.getArray(2);
        assertEquals(Types.NUMERIC, arr.getBaseType());
        BigDecimal decarr[] = (BigDecimal[])arr.getArray();
        assertEquals(2, decarr.length);
        assertEquals(new BigDecimal("3.1"), decarr[0]);
        assertEquals(new BigDecimal("1.4"), decarr[1]);

        arr = rs.getArray(3);
        assertEquals(Types.VARCHAR, arr.getBaseType());
        String strarr[] = (String[])arr.getArray(2, 2);
        assertEquals(2, strarr.length);
        assertEquals("f'a", strarr[0]);
        assertEquals("fa\"b", strarr[1]);

        rs.close();
        stmt.close();
    }

    public void testRetrieveResultSets() throws SQLException {
        Statement stmt = conn.createStatement();

        // you need a lot of backslashes to get a double quote in.
        stmt.executeUpdate("INSERT INTO arrtest VALUES ('{1,2,3}','{3.1,1.4}', '" +
                TestUtil.escapeString(conn, "{abc,f'a,\"fa\\\"b\",def}") + "')");

        ResultSet rs = stmt.executeQuery("SELECT intarr, decarr, strarr FROM arrtest");
        assertTrue(rs.next());

        Array arr = rs.getArray(1);
        assertEquals(Types.INTEGER, arr.getBaseType());
        ResultSet arrrs = arr.getResultSet();
        assertTrue(arrrs.next());
        assertEquals(1, arrrs.getInt(1));
        assertEquals(1, arrrs.getInt(2));
        assertTrue(arrrs.next());
        assertEquals(2, arrrs.getInt(1));
        assertEquals(2, arrrs.getInt(2));
        assertTrue(arrrs.next());
        assertEquals(3, arrrs.getInt(1));
        assertEquals(3, arrrs.getInt(2));
        assertTrue(!arrrs.next());
        assertTrue(arrrs.previous());
        assertEquals(3, arrrs.getInt(2));
        arrrs.first();
        assertEquals(1, arrrs.getInt(2));
        arrrs.close();

        arr = rs.getArray(2);
        assertEquals(Types.NUMERIC, arr.getBaseType());
        arrrs = arr.getResultSet();
        assertTrue(arrrs.next());
        assertEquals(new BigDecimal("3.1"), arrrs.getBigDecimal(2));
        assertTrue(arrrs.next());
        assertEquals(new BigDecimal("1.4"), arrrs.getBigDecimal(2));
        arrrs.close();

        arr = rs.getArray(3);
        assertEquals(Types.VARCHAR, arr.getBaseType());
        arrrs = arr.getResultSet(2, 2);
        assertTrue(arrrs.next());
        assertEquals(2, arrrs.getInt(1));
        assertEquals("f'a", arrrs.getString(2));
        assertTrue(arrrs.next());
        assertEquals(3, arrrs.getInt(1));
        assertEquals("fa\"b", arrrs.getString(2));
        assertTrue(!arrrs.next());
        arrrs.close();

        rs.close();
        stmt.close();
    }

    public void testSetArray() throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet arrRS = stmt.executeQuery("SELECT '{1,2,3}'::int4[]");
        assertTrue(arrRS.next());
        Array arr = arrRS.getArray(1);
        arrRS.close();
        stmt.close();

        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO arrtest(intarr) VALUES (?)");
        pstmt.setArray(1, arr);
        pstmt.executeUpdate();

        pstmt.setObject(1, arr, Types.ARRAY);
        pstmt.executeUpdate();

        pstmt.setObject(1, arr);
        pstmt.executeUpdate();

        pstmt.close();

        Statement select = conn.createStatement();
        ResultSet rs = select.executeQuery("SELECT intarr FROM arrtest");
        int resultCount = 0;
        while(rs.next()) {
            resultCount++;
            Array result = rs.getArray(1);
            assertEquals(Types.INTEGER, result.getBaseType());
            assertEquals("int4", result.getBaseTypeName());

            Integer intarr[] = (Integer[])result.getArray();
            assertEquals(3, intarr.length);
            assertEquals(1, intarr[0].intValue());
            assertEquals(2, intarr[1].intValue());
            assertEquals(3, intarr[2].intValue());
        }
        assertEquals(3, resultCount);
    }

    /**
     * Starting with 8.0 non-standard (beginning index isn't 1) bounds
     * the dimensions are returned in the data.  The following should
     * return "[0:3]={0,1,2,3,4}" when queried.  Older versions simply
     * do not return the bounds.
     */
    public void testNonStandardBounds() throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("INSERT INTO arrtest (intarr) VALUES ('{1,2,3}')");
        stmt.executeUpdate("UPDATE arrtest SET intarr[0] = 0");
        ResultSet rs = stmt.executeQuery("SELECT intarr FROM arrtest");
        assertTrue(rs.next());
        Array result = rs.getArray(1);
        Integer intarr[] = (Integer[])result.getArray();
        assertEquals(4, intarr.length);
        for (int i = 0; i < intarr.length; i++)
        {
            assertEquals(i, intarr[i].intValue());
        }
    }

    public void testMultiDimensionalArray() throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT '{{1,2},{3,4}}'::int[]");
        assertTrue(rs.next());
        Array arr = rs.getArray(1);
        Object oa[] = (Object [])arr.getArray();
        assertEquals(2, oa.length);
        Integer i0[] = (Integer []) oa[0];
        assertEquals(2, i0.length);
        assertEquals(1, i0[0].intValue());
        assertEquals(2, i0[1].intValue());
        Integer i1[] = (Integer []) oa[1];
        assertEquals(2, i1.length);
        assertEquals(3, i1[0].intValue());
        assertEquals(4, i1[1].intValue());
        rs.close();
        stmt.close();
    }

    public void testNullValues() throws SQLException {
        if (!TestUtil.haveMinimumServerVersion(conn, "8.2"))
            return;

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT ARRAY[1,NULL,3]");
        assertTrue(rs.next());
        Array arr = rs.getArray(1);
        Integer i[] = (Integer[])arr.getArray();
        assertEquals(3, i.length);
        assertEquals(1, i[0].intValue());
        assertNull(i[1]);
        assertEquals(3, i[2].intValue());
    }

    public void testUnknownArrayType() throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT relacl FROM pg_class WHERE relacl IS NOT NULL LIMIT 1");
        ResultSetMetaData rsmd = rs.getMetaData();
        assertEquals(Types.ARRAY, rsmd.getColumnType(1));

        assertTrue(rs.next());
        Array arr = rs.getArray(1);
        assertEquals("aclitem", arr.getBaseTypeName());

        ResultSet arrRS = arr.getResultSet();
        ResultSetMetaData arrRSMD = arrRS.getMetaData();
        assertEquals("aclitem", arrRSMD.getColumnTypeName(2));
    }

    public void testRecursiveResultSets() throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT '{{1,2},{3,4}}'::int[]");
        assertTrue(rs.next());
        Array arr = rs.getArray(1);

        ResultSet arrRS = arr.getResultSet();
        ResultSetMetaData arrRSMD = arrRS.getMetaData();
        assertEquals(Types.ARRAY, arrRSMD.getColumnType(2));
        assertEquals("_int4", arrRSMD.getColumnTypeName(2));

        assertTrue(arrRS.next());
        assertEquals(1, arrRS.getInt(1));
        Array a1 = arrRS.getArray(2);
        ResultSet a1RS = a1.getResultSet();
        ResultSetMetaData a1RSMD = a1RS.getMetaData();
        assertEquals(Types.INTEGER, a1RSMD.getColumnType(2));
        assertEquals("int4", a1RSMD.getColumnTypeName(2));

        assertTrue(a1RS.next());
        assertEquals(1, a1RS.getInt(2));
        assertTrue(a1RS.next());
        assertEquals(2, a1RS.getInt(2));
        assertTrue(!a1RS.next());
        a1RS.close();

        assertTrue(arrRS.next());
        assertEquals(2, arrRS.getInt(1));
        Array a2 = arrRS.getArray(2);
        ResultSet a2RS = a2.getResultSet();

        assertTrue(a2RS.next());
        assertEquals(3, a2RS.getInt(2));
        assertTrue(a2RS.next());
        assertEquals(4, a2RS.getInt(2));
        assertTrue(!a2RS.next());
        a2RS.close();

        arrRS.close();
        rs.close();
        stmt.close();
    }

    public void testNullString() throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT '{a,NULL}'::text[]");
        assertTrue(rs.next());
        Array arr = rs.getArray(1);

        String s[] = (String [])arr.getArray();
        assertEquals(2, s.length);
        assertEquals("a", s[0]);
        if (TestUtil.haveMinimumServerVersion(conn, "8.2")) {
            assertNull(s[1]);
        } else {
            assertEquals("NULL", s[1]);
        }
    }

    public void testEscaping() throws SQLException
    {
        Statement stmt = conn.createStatement();
        String sql = "SELECT ";
        if (TestUtil.haveMinimumServerVersion(conn, "8.1")) {
            sql += 'E';
        }
        // Uggg.  Three levels of escaping: Java, string literal, array.
        sql += "'{{c\\\\\"d, ''}, {\"\\\\\\\\\",\"''\"}}'::text[]";

        ResultSet rs = stmt.executeQuery(sql);
        assertTrue(rs.next());

        Array arr = rs.getArray(1);
        String s[][] = (String[][])arr.getArray();
        assertEquals("c\"d", s[0][0]);
        assertEquals("'", s[0][1]);
        assertEquals("\\", s[1][0]);
        assertEquals("'", s[1][1]);

        ResultSet arrRS = arr.getResultSet();

        assertTrue(arrRS.next());
        Array a1 = arrRS.getArray(2);
        ResultSet rs1 = a1.getResultSet();
        assertTrue(rs1.next());
        assertEquals("c\"d", rs1.getString(2));
        assertTrue(rs1.next());
        assertEquals("'", rs1.getString(2));
        assertTrue(!rs1.next());

        assertTrue(arrRS.next());
        Array a2 = arrRS.getArray(2);
        ResultSet rs2 = a2.getResultSet();
        assertTrue(rs2.next());
        assertEquals("\\", rs2.getString(2));
        assertTrue(rs2.next());
        assertEquals("'", rs2.getString(2));
        assertTrue(!rs2.next());
    }

    public void testWriteMultiDimensional() throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT '{{1,2},{3,4}}'::int[]");
        assertTrue(rs.next());
        Array arr = rs.getArray(1);
        rs.close();
        stmt.close();

        String sql = "SELECT ?";
        if (TestUtil.isProtocolVersion(conn, 2)) {
            sql = "SELECT ?::int[]";
        }
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setArray(1, arr);
        rs = pstmt.executeQuery();
        assertTrue(rs.next());
        arr = rs.getArray(1);

        Integer i[][] = (Integer[][])arr.getArray();
        assertEquals(1, i[0][0].intValue());
        assertEquals(2, i[0][1].intValue());
        assertEquals(3, i[1][0].intValue());
        assertEquals(4, i[1][1].intValue());
    }

    /*
     * The box data type uses a semicolon as the array element
     * delimiter instead of a comma which pretty much everything
     * else uses.
     */
    public void testNonStandardDelimiter() throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT '{(3,4),(1,2);(7,8),(5,6)}'::box[]");
        assertTrue(rs.next());
        Array arr = rs.getArray(1);

        ResultSet arrRS = arr.getResultSet();

        assertTrue(arrRS.next());
        PGbox box1 = (PGbox)arrRS.getObject(2);
        PGpoint p1 = box1.point[0];
        assertEquals(3, p1.x, 0.001);
        assertEquals(4, p1.y, 0.001);

        assertTrue(arrRS.next());
        PGbox box2 = (PGbox)arrRS.getObject(2);
        PGpoint p2 = box2.point[1];
        assertEquals(5, p2.x, 0.001);
        assertEquals(6, p2.y, 0.001);

        assertTrue(!arrRS.next());
    }

}