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