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}