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