001/*
002 * Copyright (c) 2018-2023 Tada AB and other contributors, as listed below.
003 *
004 * All rights reserved. This program and the accompanying materials
005 * are made available under the terms of the The BSD 3-Clause License
006 * which accompanies this distribution, and is available at
007 * http://opensource.org/licenses/BSD-3-Clause
008 *
009 * Contributors:
010 *   Chapman Flack
011 */
012package org.postgresql.pljava.example.annotation;
013
014import java.lang.reflect.Field;
015import java.lang.reflect.Modifier;
016import static java.lang.reflect.Modifier.isPublic;
017import static java.lang.reflect.Modifier.isStatic;
018
019import java.lang.reflect.Array;
020
021import java.sql.Connection;
022import static java.sql.DriverManager.getConnection;
023import java.sql.PreparedStatement;
024import java.sql.ResultSet;
025import java.sql.ResultSetMetaData;
026import java.sql.Types;
027import static java.sql.Types.VARCHAR;
028
029import java.sql.SQLException;
030import java.sql.SQLDataException;
031import java.sql.SQLNonTransientException;
032
033import java.util.Arrays;
034
035import org.postgresql.pljava.annotation.Function;
036import org.postgresql.pljava.annotation.SQLAction;
037import org.postgresql.pljava.annotation.SQLType;
038
039/**
040 * A class to simplify testing of PL/Java's mappings between PostgreSQL and
041 * Java/JDBC types.
042 *<p>
043 * Provides one function, {@link #roundTrip roundTrip()}. Its single input
044 * parameter is an unspecified row type, so you can pass it a row that has
045 * exactly one column of any type.
046 *<p>
047 * Its return type is also an unspecified row type, so you need to follow the
048 * function call with a column definition list of up to six columns. Each
049 * requested output column must have its name (case-insensitively) and type
050 * drawn from this table:
051 *<table>
052 *<caption>Items the roundTrip function can return</caption>
053 *<thead>
054 *<tr>
055 *<th>Column name</th><th>Column type</th><th>What is returned</th>
056 *</tr>
057 *</thead>
058 *<tbody>
059 *<tr>
060 *<td>TYPEPG</td><td>any text/varchar</td><td>The PostgreSQL type name</td>
061 *</tr>
062 *<tr>
063 *<td>TYPEJDBC</td><td>any text/varchar</td><td>The JDBC Types constant</td>
064 *</tr>
065 *<tr>
066 *<td>CLASSJDBC</td><td>any text/varchar</td>
067 *<td>Name of the Java class JDBC claims (in metadata) it will instantiate</td>
068 *</tr>
069 *<tr>
070 *<td>CLASS</td><td>any text/varchar</td>
071 *<td>Name of the Java class JDBC did instantiate</td>
072 *</tr>
073 *<tr>
074 *<td>TOSTRING</td><td>any text/varchar</td>
075 *<td>Result of {@code toString()} on the object returned by
076 * {@code ResultSet.getObject()} ({@code Arrays.toString} if it is a primitive
077 * array, {@code Arrays.deepToString} if an array of reference type)</td>
078 *</tr>
079 *<tr>
080 *<td>ROUNDTRIPPED</td><td>same as input column</td>
081 *<td>Result of passing the object returned by {@code ResultSet.getObject()}
082 * directly to {@code ResultSet.updateObject()}</td>
083 *</tr>
084 *</tbody>
085 *</table>
086 *<p>
087 * Serving suggestion:
088 *<pre>
089 *SELECT
090 *  orig = roundtripped AS good, *
091 *FROM
092 *  (VALUES (timestamptz '2017-08-21 18:25:29.900005Z')) AS p(orig),
093 *  roundtrip(p) AS (roundtripped timestamptz);
094 *</pre>
095 */
096@SQLAction(
097    requires = {"TypeRoundTripper.roundTrip", "point mirror type"},
098    install = {
099    " SELECT" +
100    "  CASE WHEN every(orig = roundtripped)" +
101    "  THEN javatest.logmessage('INFO', 'timestamp roundtrip passes')" +
102    "  ELSE javatest.logmessage('WARNING', 'timestamp roundtrip fails')" +
103    "  END" +
104    " FROM" +
105    "  (VALUES" +
106    "   (timestamp '2017-08-21 18:25:29.900005')," +
107    "   (timestamp '1970-03-07 17:37:49.300009')," +
108    "   (timestamp '1919-05-29 13:08:33.600001')" +
109    "  ) AS p(orig)," +
110    "  roundtrip(p) AS (roundtripped timestamp)",
111
112    " SELECT" +
113    "  CASE WHEN every(orig = roundtripped)" +
114    "  THEN javatest.logmessage('INFO', 'timestamptz roundtrip passes')" +
115    "  ELSE javatest.logmessage('WARNING', 'timestamptz roundtrip fails')" +
116    "  END" +
117    " FROM" +
118    "  (VALUES" +
119    "   (timestamptz '2017-08-21 18:25:29.900005Z')," +
120    "   (timestamptz '1970-03-07 17:37:49.300009Z')," +
121    "   (timestamptz '1919-05-29 13:08:33.600001Z')" +
122    "  ) AS p(orig)," +
123    "  roundtrip(p) AS (roundtripped timestamptz)",
124
125    " SELECT" +
126    "  CASE WHEN classjdbc = 'org.postgresql.pljava.example.annotation.Point'" +
127    "  THEN javatest.logmessage('INFO', 'issue192 test passes')" +
128    "  ELSE javatest.logmessage('WARNING', 'issue192 test fails')" +
129    "  END" +
130    " FROM" +
131    "  (VALUES (point '0,0')) AS p," +
132    "  roundtrip(p) AS (classjdbc text)",
133
134    " SELECT" +
135    "  CASE WHEN every(outcome.ok)" +
136    "  THEN javatest.logmessage('INFO',    'boolean[] passes')" +
137    "  ELSE javatest.logmessage('WARNING', 'boolean[] fails')" +
138    "  END" +
139    " FROM" +
140    "  (SELECT '{t,null,f}'::boolean[]) AS p(orig)," +
141    "  (VALUES (''), ('[Ljava.lang.Boolean;'), ('[Z')) as q(rqcls)," +
142    "  roundtrip(p, rqcls) AS (class text, roundtripped boolean[])," +
143    "  LATERAL (SELECT" +
144    "   (rqcls = class OR rqcls = '')" +
145    "   AND roundtripped =" +
146    "   CASE WHEN class LIKE '[_' THEN array_replace(orig, null, false)" +
147    "   ELSE orig END" +
148    "  ) AS outcome(ok)",
149
150    " SELECT" +
151    "  CASE WHEN every(outcome.ok)" +
152    "  THEN javatest.logmessage('INFO',    '\"char\"[] passes')" +
153    "  ELSE javatest.logmessage('WARNING', '\"char\"[] fails')" +
154    "  END" +
155    " FROM" +
156    "  (SELECT '{A,null,B}'::\"char\"[]) AS p(orig)," +
157    "  (VALUES (''), ('[Ljava.lang.Byte;'), ('[B')) as q(rqcls)," +
158    "  roundtrip(p, rqcls) AS (class text, roundtripped \"char\"[])," +
159    "  LATERAL (SELECT" +
160    "   (rqcls = class OR rqcls = '')" +
161    "   AND roundtripped =" +
162    "   CASE WHEN class LIKE '[_' THEN array_replace(orig, null, 0::\"char\")" +
163    "   ELSE orig END" +
164    "  ) AS outcome(ok)",
165
166    " SELECT" +
167    "  CASE WHEN every(outcome.ok)" +
168    "  THEN javatest.logmessage('INFO',    'bytea passes')" +
169    "  ELSE javatest.logmessage('WARNING', 'bytea fails')" +
170    "  END" +
171    " FROM" +
172    "  (SELECT '\\x010203'::bytea) AS p(orig)," +
173    "  (VALUES (''), ('[B')) as q(rqcls)," +
174    "  roundtrip(p, rqcls) AS (class text, roundtripped bytea)," +
175    "  LATERAL (SELECT" +
176    "   (rqcls = class OR rqcls = '')" +
177    "   AND roundtripped = orig" +
178    "  ) AS outcome(ok)",
179
180    " SELECT" +
181    "  CASE WHEN every(outcome.ok)" +
182    "  THEN javatest.logmessage('INFO',    'int2[] passes')" +
183    "  ELSE javatest.logmessage('WARNING', 'int2[] fails')" +
184    "  END" +
185    " FROM" +
186    "  (SELECT '{1,null,3}'::int2[]) AS p(orig)," +
187    "  (VALUES (''), ('[Ljava.lang.Short;'), ('[S')) as q(rqcls)," +
188    "  roundtrip(p, rqcls) AS (class text, roundtripped int2[])," +
189    "  LATERAL (SELECT" +
190    "   (rqcls = class OR rqcls = '')" +
191    "   AND roundtripped =" +
192    "   CASE WHEN class LIKE '[_' THEN array_replace(orig, null, 0::int2)" +
193    "   ELSE orig END" +
194    "  ) AS outcome(ok)",
195
196    " SELECT" +
197    "  CASE WHEN every(outcome.ok)" +
198    "  THEN javatest.logmessage('INFO',    'int4[] passes')" +
199    "  ELSE javatest.logmessage('WARNING', 'int4[] fails')" +
200    "  END" +
201    " FROM" +
202    "  (SELECT '{1,null,3}'::int4[]) AS p(orig)," +
203    "  (VALUES (''), ('[Ljava.lang.Integer;'), ('[I')) as q(rqcls)," +
204    "  roundtrip(p, rqcls) AS (class text, roundtripped int4[])," +
205    "  LATERAL (SELECT" +
206    "   (rqcls = class OR rqcls = '')" +
207    "   AND roundtripped =" +
208    "   CASE WHEN class LIKE '[_' THEN array_replace(orig, null, 0::int4)" +
209    "   ELSE orig END" +
210    "  ) AS outcome(ok)",
211
212    " SELECT" +
213    "  CASE WHEN every(outcome.ok)" +
214    "  THEN javatest.logmessage('INFO',    'int8[] passes')" +
215    "  ELSE javatest.logmessage('WARNING', 'int8[] fails')" +
216    "  END" +
217    " FROM" +
218    "  (SELECT '{1,null,3}'::int8[]) AS p(orig)," +
219    "  (VALUES (''), ('[Ljava.lang.Long;'), ('[J')) as q(rqcls)," +
220    "  roundtrip(p, rqcls) AS (class text, roundtripped int8[])," +
221    "  LATERAL (SELECT" +
222    "   (rqcls = class OR rqcls = '')" +
223    "   AND roundtripped =" +
224    "   CASE WHEN class LIKE '[_' THEN array_replace(orig, null, 0::int8)" +
225    "   ELSE orig END" +
226    "  ) AS outcome(ok)",
227
228    " SELECT" +
229    "  CASE WHEN every(outcome.ok)" +
230    "  THEN javatest.logmessage('INFO',    'float4[] passes')" +
231    "  ELSE javatest.logmessage('WARNING', 'float4[] fails')" +
232    "  END" +
233    " FROM" +
234    "  (SELECT '{1,null,3}'::float4[]) AS p(orig)," +
235    "  (VALUES (''), ('[Ljava.lang.Float;'), ('[F')) as q(rqcls)," +
236    "  roundtrip(p, rqcls) AS (class text, roundtripped float4[])," +
237    "  LATERAL (SELECT" +
238    "   (rqcls = class OR rqcls = '')" +
239    "   AND roundtripped =" +
240    "   CASE WHEN class LIKE '[_' THEN array_replace(orig, null, 0::float4)" +
241    "   ELSE orig END" +
242    "  ) AS outcome(ok)",
243
244    " SELECT" +
245    "  CASE WHEN every(outcome.ok)" +
246    "  THEN javatest.logmessage('INFO',    'float8[] passes')" +
247    "  ELSE javatest.logmessage('WARNING', 'float8[] fails')" +
248    "  END" +
249    " FROM" +
250    "  (SELECT '{1,null,3}'::float8[]) AS p(orig)," +
251    "  (VALUES (''), ('[Ljava.lang.Double;'), ('[D')) as q(rqcls)," +
252    "  roundtrip(p, rqcls) AS (class text, roundtripped float8[])," +
253    "  LATERAL (SELECT" +
254    "   (rqcls = class OR rqcls = '')" +
255    "   AND roundtripped =" +
256    "   CASE WHEN class LIKE '[_' THEN array_replace(orig, null, 0::float8)" +
257    "   ELSE orig END" +
258    "  ) AS outcome(ok)",
259
260    " SELECT" +
261    "  CASE WHEN every(outcome.ok)" +
262    "  THEN javatest.logmessage('INFO',    'text[] passes')" +
263    "  ELSE javatest.logmessage('WARNING', 'text[] fails')" +
264    "  END" +
265    " FROM" +
266    "  (SELECT '{foo,null,bar}'::text[]) AS p(orig)," +
267    "  (VALUES (''), ('[Ljava.lang.String;')) as q(rqcls)," +
268    "  roundtrip(p, rqcls) AS (class text, roundtripped text[])," +
269    "  LATERAL (SELECT" +
270    "   (rqcls = class OR rqcls = '')" +
271    "   AND roundtripped = orig" +
272    "  ) AS outcome(ok)",
273    }
274)
275public class TypeRoundTripper
276{
277    private TypeRoundTripper() { }
278
279    /**
280     * Function accepting one parameter of row type (one column, any type)
281     * and returning a row with up to six columns (use a column definition list
282     * after the function call, choose column names from TYPEPG, TYPEJDBC,
283     * CLASSJDBC, CLASS, TOSTRING, ROUNDTRIPPED where any of the first five
284     * must have text/varchar type, while ROUNDTRIPPED must match the type of
285     * the input column).
286     * @param in The input row value (required to have exactly one column).
287     * @param classname Name of class to be explicitly requested (JDBC 4.1
288     * feature) from {@code getObject}; pass an empty string (the default) to
289     * make no such explicit request. Accepts the form {@code Class.getName}
290     * would produce: canonical names or spelled-out primitives if not an array
291     * type, otherwise prefix left-brackets and primitive letter codes or
292     * {@code L}<em>classname</em>{@code ;}.
293     * @param prepare Whether the object retrieved from {@code in} should be
294     * passed as a parameter to an identity {@code PreparedStatement} and the
295     * result of that be returned. If false (the default), the value from
296     * {@code in} is simply forwarded directly to {@code out}.
297     * @param out The output row (supplied by PL/Java, representing the column
298     * definition list that follows the call of this function in SQL).
299     * @throws SQLException if {@code in} does not have exactly one column, if
300     * {@code out} has more than six, if a requested column name in {@code out}
301     * is not among those recognized, if a column of {@code out} is not of its
302     * required type, or if other stuff goes wrong.
303     */
304    @Function(
305        schema = "javatest",
306        type = "RECORD",
307        provides = "TypeRoundTripper.roundTrip"
308        )
309    public static boolean roundTrip(
310        ResultSet in, @SQLType(defaultValue="") String classname,
311        @SQLType(defaultValue="false") boolean prepare, ResultSet out)
312    throws SQLException
313    {
314        ResultSetMetaData inmd = in.getMetaData();
315        ResultSetMetaData outmd = out.getMetaData();
316
317        Class<?> clazz = null;
318        if ( ! "".equals(classname) )
319            clazz = loadClass(classname);
320
321        if ( 1 != inmd.getColumnCount() )
322            throw new SQLDataException(
323                "in parameter must be a one-column row type", "22000");
324
325        int outcols = outmd.getColumnCount();
326        if ( 6 < outcols )
327            throw new SQLDataException(
328                "result description may have no more than six columns",
329                "22000");
330
331        String inTypePG = inmd.getColumnTypeName(1);
332        int inTypeJDBC = inmd.getColumnType(1);
333        Object val = (null == clazz) ? in.getObject(1) : in.getObject(1, clazz);
334
335        if ( prepare )
336        {
337            Connection c = getConnection("jdbc:default:connection");
338            PreparedStatement ps = c.prepareStatement("SELECT ?");
339            ps.setObject(1, val);
340            ResultSet rs = ps.executeQuery();
341            rs.next();
342            val = (null == clazz) ? rs.getObject(1) : rs.getObject(1, clazz);
343            rs.close();
344            ps.close();
345            c.close();
346        }
347
348        for ( int i = 1; i <= outcols; ++ i )
349        {
350            String what = outmd.getColumnLabel(i);
351
352            if ( "TYPEPG".equalsIgnoreCase(what) )
353            {
354                assertTypeJDBC(outmd, i, VARCHAR);
355                out.updateObject(i, inTypePG);
356            }
357            else if ( "TYPEJDBC".equalsIgnoreCase(what) )
358            {
359                assertTypeJDBC(outmd, i, VARCHAR);
360                out.updateObject(i, typeNameJDBC(inTypeJDBC));
361            }
362            else if ( "CLASSJDBC".equalsIgnoreCase(what) )
363            {
364                assertTypeJDBC(outmd, i, VARCHAR);
365                out.updateObject(i, inmd.getColumnClassName(1));
366            }
367            else if ( "CLASS".equalsIgnoreCase(what) )
368            {
369                assertTypeJDBC(outmd, i, VARCHAR);
370                out.updateObject(i, val.getClass().getName());
371            }
372            else if ( "TOSTRING".equalsIgnoreCase(what) )
373            {
374                assertTypeJDBC(outmd, i, VARCHAR);
375                out.updateObject(i, toString(val));
376            }
377            else if ( "ROUNDTRIPPED".equalsIgnoreCase(what) )
378            {
379                if ( ! inTypePG.equals(outmd.getColumnTypeName(i)) )
380                    throw new SQLDataException(
381                    "Result ROUNDTRIPPED column must have same type as input",
382                        "22000");
383                out.updateObject(i, val);
384            }
385            else
386                throw new SQLDataException(
387                    "Output column label \""+ what + "\" should be one of: " +
388                    "TYPEPG, TYPEJDBC, CLASSJDBC, CLASS, TOSTRING, " +
389                    "ROUNDTRIPPED",
390                    "22000");
391        }
392
393        return true;
394    }
395
396    static void assertTypeJDBC(ResultSetMetaData md, int i, int t)
397    throws SQLException
398    {
399        if ( md.getColumnType(i) != t )
400            throw new SQLDataException(
401                "Result column " + i + " must be of JDBC type " +
402                typeNameJDBC(t));
403    }
404
405    static String typeNameJDBC(int t)
406    {
407        for ( Field f : Types.class.getFields() )
408        {
409            int m = f.getModifiers();
410            if ( isPublic(m) && isStatic(m) && int.class == f.getType() )
411                try
412                {
413                    if ( f.getInt(null) == t )
414                        return f.getName();
415                }
416                catch ( IllegalAccessException e ) { }
417        }
418        return String.valueOf(t);
419    }
420
421    private static Class<?> loadClass(String className)
422    throws SQLException
423    {
424        String noBrackets = className.replaceFirst("^\\[++", "");
425        int ndims = (className.length() - noBrackets.length());
426
427        /*
428         * The naming conventions from Class.getName() could hardly be less
429         * convenient. If *not* an array, it's the same as the canonical name,
430         * with the primitive names spelled out. If it *is* an array, the
431         * primitives get their one-letter codes, and other class names have L
432         * prefix and ; suffix. Condense the two cases here into one offbeat
433         * hybrid form that will be used below.
434         */
435        if ( 0 == ndims )
436            noBrackets =
437                ("L" + noBrackets +
438                ":booleanZ:byteB:shortS:charC:intI:longJ:floatF:doubleD")
439                .replaceFirst(
440                    "^L(\\w++)(?=:)(?:\\w*+:)*\\1(\\w)(?::.*+)?+$|:.++$",
441                    "$2");
442        else
443            noBrackets = noBrackets.replaceFirst(";$", "");
444
445        /*
446         * Invariant: thanks to the above normalization, whether array or not,
447         * noBrackets will now have this form: either the first (and only)
448         * character is one of the primitive character codes, or the first
449         * character is L and the rest is a class name (with no ; at the end).
450         */
451
452        Class<?> c;
453
454        switch ( noBrackets.charAt(0) )
455        {
456        case 'Z': c = boolean.class; break;
457        case 'B': c =    byte.class; break;
458        case 'S': c =   short.class; break;
459        case 'C': c =    char.class; break;
460        case 'I': c =     int.class; break;
461        case 'J': c =    long.class; break;
462        case 'F': c =   float.class; break;
463        case 'D': c =  double.class; break;
464        default:
465            try
466            {
467                noBrackets = noBrackets.substring(1);
468                c = Class.forName(noBrackets);
469            }
470            catch ( ClassNotFoundException e )
471            {
472                throw new SQLNonTransientException(
473                    "No such class: " + noBrackets, "46103", e);
474            }
475        }
476
477        if ( 0 != ndims )
478            c = Array.newInstance(c, new int[ndims]).getClass();
479
480        return c;
481    }
482
483    private static String toString(Object o)
484    {
485        if ( ! o.getClass().isArray() )
486            return o.toString();
487        if (Object[].class.isInstance(o))
488            return Arrays.deepToString(Object[].class.cast(o));
489        if (boolean[].class.isInstance(o))
490            return Arrays.toString(boolean[].class.cast(o));
491        if (byte[].class.isInstance(o))
492            return Arrays.toString(byte[].class.cast(o));
493        if (short[].class.isInstance(o))
494            return Arrays.toString(short[].class.cast(o));
495        if (int[].class.isInstance(o))
496            return Arrays.toString(int[].class.cast(o));
497        if (long[].class.isInstance(o))
498            return Arrays.toString(long[].class.cast(o));
499        if (char[].class.isInstance(o))
500            return Arrays.toString(char[].class.cast(o));
501        if (float[].class.isInstance(o))
502            return Arrays.toString(float[].class.cast(o));
503        if (double[].class.isInstance(o))
504            return Arrays.toString(double[].class.cast(o));
505        return null;
506    }
507}