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.sql.Connection;
015import java.sql.Date;
016import java.sql.DriverManager;
017import java.sql.Statement;
018import java.sql.ResultSet;
019import java.sql.Savepoint;
020import java.sql.SQLException;
021
022import static java.util.logging.Logger.getAnonymousLogger;
023import java.util.TimeZone;
024
025import org.postgresql.pljava.annotation.Function;
026import org.postgresql.pljava.annotation.SQLAction;
027
028/**
029 * Some tests of pre-JSR 310 date/time/timestamp conversions.
030 *<p>
031 * For now, just {@code java.sql.Date}, thanks to issue #199.
032 */
033@SQLAction(provides="language java_tzset", install={
034    "SELECT sqlj.alias_java_language('java_tzset', true)"
035}, remove={
036    "DROP LANGUAGE java_tzset"
037})
038
039@SQLAction(
040    requires="issue199", install={
041    "SELECT javatest.issue199()"
042})
043public class PreJSR310
044{
045    private static final String TZPRAGUE = "Europe/Prague";
046
047    static
048    {
049        TimeZone oldZone = TimeZone.getDefault();
050        TimeZone tzPrague = TimeZone.getTimeZone(TZPRAGUE);
051
052        try
053        {
054            TimeZone.setDefault(tzPrague);
055        }
056        finally
057        {
058            TimeZone.setDefault(oldZone);
059        }
060    }
061
062    /**
063     * Test for a regression in PG date to/from java.sql.Date conversion
064     * identified in issue #199.
065     *<p>
066     * Checks that two months of consecutive dates in October/November 2018
067     * are converted correctly in the Europe/Prague timezone. The actual issue
068     * was by no means limited to that timezone, but this test reproducibly
069     * detects it.
070     *<p>
071     * This function is defined in the 'alias' language {@code java_tzset}, for
072     * which there is an entry in the default {@code pljava.policy} granting
073     * permission to adjust the time zone, which is temporarily done here.
074     */
075    @Function(
076        schema="javatest", language="java_tzset",
077        requires="language java_tzset", provides="issue199"
078    )
079    public static void issue199() throws SQLException
080    {
081        TimeZone oldZone = TimeZone.getDefault();
082        TimeZone tzPrague = TimeZone.getTimeZone(TZPRAGUE);
083        Connection c = DriverManager.getConnection("jdbc:default:connection");
084        Statement s = c.createStatement();
085        Savepoint svpt = c.setSavepoint();
086        boolean ok = true;
087        try
088        {
089            TimeZone.setDefault(tzPrague);
090            s.execute("SET LOCAL TIME ZONE '" + TZPRAGUE + "'");
091
092            ResultSet rs = s.executeQuery(
093                "SELECT" +
094                "  d, to_char(d, 'YYYY-MM-DD')" +
095                " FROM" +
096                "  generate_series(0, 60) AS s(i)," +
097                "  LATERAL (SELECT date '2018-10-01' + i) AS t(d)");
098            while ( rs.next() )
099            {
100                Date dd = rs.getDate(1);
101                String ds = rs.getString(2);
102                if ( ! ds.equals(dd.toString()) )
103                    ok = false;
104            }
105        }
106        finally
107        {
108            TimeZone.setDefault(oldZone);
109            c.rollback(svpt); // restore prior PG timezone
110            s.close();
111            c.close();
112        }
113
114        if ( ok )
115            getAnonymousLogger().info("issue 199 test ok");
116        else
117            getAnonymousLogger().warning("issue 199 test not ok");
118    }
119}