001/*
002 * Copyright (c) 2018-2022 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 org.postgresql.pljava.annotation.Function;
015import org.postgresql.pljava.annotation.SQLAction;
016
017import org.postgresql.pljava.example.annotation.ConditionalDDR; // for javadoc
018
019/**
020 * Exercise new mappings between date/time types and java.time classes
021 * (JDBC 4.2 change 21).
022 *<p>
023 * Defines a method {@link #javaSpecificationGE javaSpecificationGE} that may be
024 * of use for other examples.
025 *<p>
026 * Relies on PostgreSQL-version-specific implementor tags set up in the
027 * {@link ConditionalDDR} example.
028 */
029@SQLAction(
030    implementor="postgresql_ge_90300",requires="TypeRoundTripper.roundTrip",
031    install={
032    " SELECT" +
033    "  CASE WHEN every(orig = roundtripped)" +
034    "  THEN javatest.logmessage('INFO', 'java.time.LocalDate passes')" +
035    "  ELSE javatest.logmessage('WARNING', 'java.time.LocalDate fails')" +
036    "  END" +
037    " FROM" +
038    "  (VALUES" +
039    "   (date 'infinity')," +
040    "   (date '2017-08-21')," +
041    "   (date '1970-03-07')," +
042    "   (date '1919-05-29')," +
043    "   (date '-infinity')" +
044    "  ) AS p(orig)," +
045    "  javatest.roundtrip(p, 'java.time.LocalDate')" +
046    "  AS r(roundtripped date)",
047
048    " SELECT" +
049    "  CASE WHEN every(orig = roundtripped)" +
050    "  THEN javatest.logmessage('INFO', 'java.time.LocalTime passes')" +
051    "  ELSE javatest.logmessage('WARNING', 'java.time.LocalTime fails')" +
052    "  END" +
053    " FROM" +
054    "  (VALUES" +
055    "   (current_time::time)," +
056    "   ('00:00:00')," +
057    "   ('24:00:00')" +
058    "  ) AS p(orig)," +
059    "  javatest.roundtrip(p, 'java.time.LocalTime')" +
060    "  AS r(roundtripped time)",
061
062    " SELECT" +
063    "  CASE WHEN every(orig = roundtripped)" +
064    "  THEN javatest.logmessage('INFO', 'java.time.OffsetTime passes')" +
065    "  ELSE javatest.logmessage('WARNING', 'java.time.OffsetTime fails')" +
066    "  END" +
067    " FROM" +
068    "  (VALUES" +
069    "   (current_time::timetz)," +
070    "   ('00:00:00')," +
071    "   ('24:00:00')" +
072    "  ) AS p(orig)," +
073    "  javatest.roundtrip(p, 'java.time.OffsetTime')" +
074    "  AS r(roundtripped timetz)",
075
076    " SELECT" +
077    "  CASE WHEN every(orig = roundtripped)" +
078    "  THEN javatest.logmessage('INFO', 'java.time.LocalDateTime passes')" +
079    "  ELSE javatest.logmessage('WARNING','java.time.LocalDateTime fails')"+
080    "  END" +
081    " FROM" +
082    "  (SELECT 'on' = current_setting('integer_datetimes')) AS ck(idt)," +
083    "  LATERAL (" +
084    "   SELECT" +
085    "    value" +
086    "   FROM" +
087    "    (VALUES" +
088    "     (true, timestamp '2017-08-21 18:25:29.900005')," +
089    "     (true, timestamp '1970-03-07 17:37:49.300009')," +
090    "     (true, timestamp '1919-05-29 13:08:33.600001')," +
091    "     (idt,  timestamp  'infinity')," +
092    "     (idt,  timestamp '-infinity')" +
093    "    ) AS vs(cond, value)" +
094    "   WHERE cond" +
095    "  ) AS p(orig)," +
096    "  javatest.roundtrip(p, 'java.time.LocalDateTime')" +
097    "  AS r(roundtripped timestamp)",
098
099    " SELECT" +
100    "  CASE WHEN every(orig = roundtripped)" +
101    "  THEN javatest.logmessage('INFO', 'java.time.OffsetDateTime passes')"+
102    "  ELSE javatest.logmessage(" +
103    "         'WARNING','java.time.OffsetDateTime fails')"+
104    "  END" +
105    " FROM" +
106    "  (SELECT 'on' = current_setting('integer_datetimes')) AS ck(idt)," +
107    "  LATERAL (" +
108    "   SELECT" +
109    "    value" +
110    "   FROM" +
111    "    (VALUES" +
112    "     (true, timestamptz '2017-08-21 18:25:29.900005Z')," +
113    "     (true, timestamptz '1970-03-07 17:37:49.300009Z')," +
114    "     (true, timestamptz '1919-05-29 13:08:33.600001Z')," +
115    "     (idt,  timestamptz  'infinity')," +
116    "     (idt,  timestamptz '-infinity')" +
117    "    ) AS vs(cond, value)" +
118    "   WHERE cond" +
119    "  ) AS p(orig)," +
120    "  javatest.roundtrip(p, 'java.time.OffsetDateTime')" +
121    "  AS r(roundtripped timestamptz)",
122
123    " SELECT" +
124    "  CASE WHEN every(orig = roundtripped)" +
125    "  THEN javatest.logmessage('INFO', 'OffsetTime as stmt param passes')"+
126    "  ELSE javatest.logmessage(" +
127    "         'WARNING','java.time.OffsetTime as stmt param fails')"+
128    "  END" +
129    " FROM" +
130    "  (SELECT current_time::timetz) AS p(orig)," +
131    "  javatest.roundtrip(p, 'java.time.OffsetTime', true)" +
132    "  AS r(roundtripped timetz)"
133})
134public class JDBC42_21
135{
136    /**
137     * Return true if running under a Java specification version at least as
138     * recent as the argument ('1.6', '1.7', '1.8', '9', '10', '11', ...).
139     */
140    @Function(schema="javatest", provides="javaSpecificationGE")
141    public static boolean javaSpecificationGE(String want)
142    {
143        String got = System.getProperty("java.specification.version");
144        if ( want.startsWith("1.") )
145            want = want.substring(2);
146        if ( got.startsWith("1.") )
147            got = got.substring(2);
148        return 0 <= Integer.valueOf(got).compareTo(Integer.valueOf(want));
149    }
150}