java/testing/org/apache/derbyTesting/functionTests/tests/lang/Derby5866TriggerOrderTest.java - derby - Git at Google

 /*
  * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.Derby5866TriggerOrderTest
  *
  * Licensed to the Apache Software Foundation (ASF) under one or more
  * contributor license agreements.  See the NOTICE file distributed with
  * this work for additional information regarding copyright ownership.
  * The ASF licenses this file to You under the Apache License, Version 2.0
  * (the "License"); you may not use this file except in compliance with
  * the License.  You may obtain a copy of the License at
  *
  *    http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
  * either express or implied. See the License for the specific
  * language governing permissions and limitations under the License.
  */

 package org.apache.derbyTesting.functionTests.tests.lang;

 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.sql.Timestamp;
 import java.util.Calendar;
 import java.util.TimeZone;
 import junit.framework.Test;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
 import org.apache.derbyTesting.junit.JDBC;
 import org.apache.derbyTesting.junit.TestConfiguration;
 import org.apache.derbyTesting.junit.TimeZoneTestSetup;

 /**
  * <p>
  * Test that triggers for a specific event execute in the order in which they
  * were defined. This is a regression test case for DERBY-5866, where triggers
  * were seen to fire in a nondeterministic order if the system clock was too
  * coarse-grained and gave the triggers identical creation time stamps. It
  * also tests that triggers fire in the correct order when the triggers are
  * created in different time zones, or right before or after daylight saving.
  * </p>
  */
 public class Derby5866TriggerOrderTest extends BaseJDBCTestCase {

     private final static TimeZone TIMEZONE =
             TimeZone.getTimeZone("Europe/Oslo");

     private final static String OVERRIDE_TIME_PROP =
             "derby.debug.overrideTriggerCreationTimestamp";

     public Derby5866TriggerOrderTest(String name) {
         super(name);
     }

     public static Test suite() {
         Test test = new CleanDatabaseTestSetup(
             TestConfiguration.embeddedSuite(Derby5866TriggerOrderTest.class));
         return new TimeZoneTestSetup(test, TIMEZONE);
     }

     @Override
     protected void tearDown() throws Exception {
         // Reset the time zone after each test case, since the test case
         // may have altered it.
         TimeZoneTestSetup.setDefault(TIMEZONE);

         // Clear the system property that overrides the trigger creation
         // timestamps.
         removeSystemProperty(OVERRIDE_TIME_PROP);

         super.tearDown();
     }

     /**
      * Test that triggers fire in the correct order if the time zone changes
      * between two CREATE TRIGGER operations in a way that makes it look like
      * the second trigger was created before the first trigger.
      */
     public void testTimeZoneChange() throws SQLException {
         setAutoCommit(false);

         Statement s = createStatement();
         s.execute("create table t1(x int)");
         s.execute("create table t2(x int generated always as identity, "
                 + "y varchar(128))");

         // Create the first trigger while in the GMT time zone.
         s.execute("create trigger tr1 after insert on t1 "
                 + "insert into t2(y) values 'I won! :)'");

         // Travel back in time. Sort of... At least that's how it's perceived
         // until TIMESTAMP WITH TIMEZONE is supported, and SYSTRIGGERS is
         // updated to use it (DERBY-5974).
         TimeZoneTestSetup.setDefault(TimeZone.getTimeZone("GMT-8:00"));
         s.execute("create trigger tr2 after insert on t1 "
                 + "insert into t2(y) values 'I lost... :('");

         // Fire the triggers.
         s.execute("insert into t1 values 1");

         // Check which of the triggers was executed first. It should have been
         // the trigger that was defined first. Before DERBY-5866, they fired
         // in the opposite order.
         JDBC.assertFullResultSet(s.executeQuery("select * from t2 order by x"),
                                  new String[][] {
                                      { "1", "I won! :)" },
                                      { "2", "I lost... :(" },
                                  });
     }

     /**
      * Test that triggers fire in the correct order if the clock shows the
      * same creation time for all the triggers.
      */
     public void testEqualTimestamps() throws SQLException {
         Timestamp now = new Timestamp(System.currentTimeMillis());
         testSpecificTimestamps(now, now, now);
     }

     /**
      * Test that the triggers fire in creation order even if the clock goes
      * backwards.
      */
     public void testReversedTimestamps() throws SQLException {
         long now = System.currentTimeMillis();
         testSpecificTimestamps(new Timestamp(now), new Timestamp(now - 1),
                                new Timestamp(now - 2), new Timestamp(now - 3));
     }

     /**
      * Test that triggers fire in the correct order if they are created around
      * the daylight saving time switchover.
      */
     public void testCrossDaylightSaving() throws SQLException {
         // Use a GMT-based calendar to prevent ambiguities. For example, with
         // a CET-based calendar, it would be ambiguous whether 2014-10-26
         // 02:45:00 means 2014-10-26 02:45:00 CET or 2014-10-26 02:45:00 CEST.
         Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));

         // 15 min before Central European Time switches to DST.
         cal.set(2014, Calendar.MARCH, 30, 0, 45, 0);

         // Test that triggers are ordered correctly if they are created
         // 15 min before switch and 15 min after switch.
         testSpecificTimestamps(new Timestamp(cal.getTimeInMillis()),
                                new Timestamp(cal.getTimeInMillis() + 1800000));

         // 15 min before Central European Time switches from DST.
         cal.clear();
         cal.set(2014, Calendar.OCTOBER, 26, 0, 45, 0);

         // Test that triggers are ordered correctly if they are created
         // 15 min before switch and 15 min after switch.
         testSpecificTimestamps(new Timestamp(cal.getTimeInMillis()),
                                new Timestamp(cal.getTimeInMillis() + 1800000));

         // Last millisecond before switch to DST.
         cal.clear();
         cal.set(2014, Calendar.MARCH, 30, 0, 59, 59);
         cal.set(Calendar.MILLISECOND, 999);
         Timestamp ts = new Timestamp(cal.getTimeInMillis());
         testSpecificTimestamps(ts, ts, ts);

         // Last millisecond before switch from DST.
         cal.clear();
         cal.set(2014, Calendar.OCTOBER, 26, 0, 59, 59);
         cal.set(Calendar.MILLISECOND, 999);
         ts = new Timestamp(cal.getTimeInMillis());
         testSpecificTimestamps(ts, ts, ts);
     }

     /**
      * Test that triggers created before the epoch (Jan 1 1970) fire in the
      * correct order.
      */
     public void testPreEpoch() throws SQLException {
         // 24 hours before the epoch
         Timestamp ts = new Timestamp(-3600L * 24 * 1000);
         testSpecificTimestamps(ts, ts, ts);

         // Test with some non-zero fractions as well.

         ts.setNanos(123000000);
         testSpecificTimestamps(ts, ts, ts);

         ts.setNanos(567000000);
         testSpecificTimestamps(ts, ts, ts);

         ts.setNanos(999000000);
         testSpecificTimestamps(ts, ts, ts);
     }

     /**
      * Helper method that creates triggers with the specified creation
      * timestamps and verifies that they fire in creation order. The creation
      * timestamps can only be overridden in debug builds. When running in a
      * non-debug build, this method will simply create the triggers without
      * overriding the creation timestamps, and verify that they fire in the
      * expected order.
      */
     private void testSpecificTimestamps(Timestamp... timestamps)
             throws SQLException {
         setAutoCommit(false);

         Statement s = createStatement();

         s.execute("create table t1(x int)");
         s.execute("create table t2(x int generated always as identity, y int)");

         // Create the triggers.
         for (int i = 0; i < timestamps.length; i++) {
             overrideTriggerCreationTime(timestamps[i]);
             s.execute("create trigger tr" + (i + 1) + " after insert on t1 "
                     + "insert into t2(y) values " + (i + 1));
         }

         // Fire the triggers.
         s.execute("insert into t1 values 1");

         // Verify that the triggers executed in the correct order.
         ResultSet rs = s.executeQuery("select * from t2 order by x");
         for (int i = 1; i <= timestamps.length; i++) {
             if (rs.next()) {
                 assertEquals("X", i, rs.getInt("X"));
                 assertEquals("Y", i, rs.getInt("Y"));
             } else {
                 fail("Row " + i + " was missing");
             }
         }
         JDBC.assertEmpty(rs);

         // Verify that the CREATIONTIMESTAMP column in SYS.SYSTRIGGERS is
         // monotonically increasing.
         PreparedStatement ps = prepareStatement(
                 "select * from sys.sysschemas natural join sys.systriggers "
                 + "where schemaname = ? and triggername like 'TR%' "
                 + "order by creationtimestamp");
         ps.setString(1, getTestConfiguration().getUserName());
         rs = ps.executeQuery();
         Timestamp prev = null;
         for (int i = 1; i <= timestamps.length; i++) {
             assertTrue(rs.next());
             assertEquals("TR" + i, rs.getString("TRIGGERNAME"));
             Timestamp ts = rs.getTimestamp("CREATIONTIMESTAMP");
             assertNotNull(ts);
             if (prev != null && !prev.before(ts)) {
                 fail(prev + " expected to be before " + ts);
             }
             prev = ts;
         }
         JDBC.assertEmpty(rs);

         rollback();
     }

     /**
      * Set a system property that makes the next CREATE TRIGGER operation
      * use the specified timestamp instead of the current time when
      * constructing the creation timestamp.
      */
     private void overrideTriggerCreationTime(Timestamp ts) {
         setSystemProperty(OVERRIDE_TIME_PROP, String.valueOf(ts.getTime()));
     }
 }
	/*
	* Derby - Class org.apache.derbyTesting.functionTests.tests.lang.Derby5866TriggerOrderTest
	*
	* Licensed to the Apache Software Foundation (ASF) under one or more
	* contributor license agreements. See the NOTICE file distributed with
	* this work for additional information regarding copyright ownership.
	* The ASF licenses this file to You under the Apache License, Version 2.0
	* (the "License"); you may not use this file except in compliance with
	* the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
	* either express or implied. See the License for the specific
	* language governing permissions and limitations under the License.
	*/

	package org.apache.derbyTesting.functionTests.tests.lang;

	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.sql.Timestamp;
	import java.util.Calendar;
	import java.util.TimeZone;
	import junit.framework.Test;
	import org.apache.derbyTesting.junit.BaseJDBCTestCase;
	import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
	import org.apache.derbyTesting.junit.JDBC;
	import org.apache.derbyTesting.junit.TestConfiguration;
	import org.apache.derbyTesting.junit.TimeZoneTestSetup;

	/**
	* <p>
	* Test that triggers for a specific event execute in the order in which they
	* were defined. This is a regression test case for DERBY-5866, where triggers
	* were seen to fire in a nondeterministic order if the system clock was too
	* coarse-grained and gave the triggers identical creation time stamps. It
	* also tests that triggers fire in the correct order when the triggers are
	* created in different time zones, or right before or after daylight saving.
	* </p>
	*/
	public class Derby5866TriggerOrderTest extends BaseJDBCTestCase {

	private final static TimeZone TIMEZONE =
	TimeZone.getTimeZone("Europe/Oslo");

	private final static String OVERRIDE_TIME_PROP =
	"derby.debug.overrideTriggerCreationTimestamp";

	public Derby5866TriggerOrderTest(String name) {
	super(name);
	}

	public static Test suite() {
	Test test = new CleanDatabaseTestSetup(
	TestConfiguration.embeddedSuite(Derby5866TriggerOrderTest.class));
	return new TimeZoneTestSetup(test, TIMEZONE);
	}

	@Override
	protected void tearDown() throws Exception {
	// Reset the time zone after each test case, since the test case
	// may have altered it.
	TimeZoneTestSetup.setDefault(TIMEZONE);

	// Clear the system property that overrides the trigger creation
	// timestamps.
	removeSystemProperty(OVERRIDE_TIME_PROP);

	super.tearDown();
	}

	/**
	* Test that triggers fire in the correct order if the time zone changes
	* between two CREATE TRIGGER operations in a way that makes it look like
	* the second trigger was created before the first trigger.
	*/
	public void testTimeZoneChange() throws SQLException {
	setAutoCommit(false);

	Statement s = createStatement();
	s.execute("create table t1(x int)");
	s.execute("create table t2(x int generated always as identity, "
	+ "y varchar(128))");

	// Create the first trigger while in the GMT time zone.
	s.execute("create trigger tr1 after insert on t1 "
	+ "insert into t2(y) values 'I won! :)'");

	// Travel back in time. Sort of... At least that's how it's perceived
	// until TIMESTAMP WITH TIMEZONE is supported, and SYSTRIGGERS is
	// updated to use it (DERBY-5974).
	TimeZoneTestSetup.setDefault(TimeZone.getTimeZone("GMT-8:00"));
	s.execute("create trigger tr2 after insert on t1 "
	+ "insert into t2(y) values 'I lost... :('");

	// Fire the triggers.
	s.execute("insert into t1 values 1");

	// Check which of the triggers was executed first. It should have been
	// the trigger that was defined first. Before DERBY-5866, they fired
	// in the opposite order.
	JDBC.assertFullResultSet(s.executeQuery("select * from t2 order by x"),
	new String[][] {
	{ "1", "I won! :)" },
	{ "2", "I lost... :(" },
	});
	}

	/**
	* Test that triggers fire in the correct order if the clock shows the
	* same creation time for all the triggers.
	*/
	public void testEqualTimestamps() throws SQLException {
	Timestamp now = new Timestamp(System.currentTimeMillis());
	testSpecificTimestamps(now, now, now);
	}

	/**
	* Test that the triggers fire in creation order even if the clock goes
	* backwards.
	*/
	public void testReversedTimestamps() throws SQLException {
	long now = System.currentTimeMillis();
	testSpecificTimestamps(new Timestamp(now), new Timestamp(now - 1),
	new Timestamp(now - 2), new Timestamp(now - 3));
	}

	/**
	* Test that triggers fire in the correct order if they are created around
	* the daylight saving time switchover.
	*/
	public void testCrossDaylightSaving() throws SQLException {
	// Use a GMT-based calendar to prevent ambiguities. For example, with
	// a CET-based calendar, it would be ambiguous whether 2014-10-26
	// 02:45:00 means 2014-10-26 02:45:00 CET or 2014-10-26 02:45:00 CEST.
	Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));

	// 15 min before Central European Time switches to DST.
	cal.set(2014, Calendar.MARCH, 30, 0, 45, 0);

	// Test that triggers are ordered correctly if they are created
	// 15 min before switch and 15 min after switch.
	testSpecificTimestamps(new Timestamp(cal.getTimeInMillis()),
	new Timestamp(cal.getTimeInMillis() + 1800000));

	// 15 min before Central European Time switches from DST.
	cal.clear();
	cal.set(2014, Calendar.OCTOBER, 26, 0, 45, 0);

	// Test that triggers are ordered correctly if they are created
	// 15 min before switch and 15 min after switch.
	testSpecificTimestamps(new Timestamp(cal.getTimeInMillis()),
	new Timestamp(cal.getTimeInMillis() + 1800000));

	// Last millisecond before switch to DST.
	cal.clear();
	cal.set(2014, Calendar.MARCH, 30, 0, 59, 59);
	cal.set(Calendar.MILLISECOND, 999);
	Timestamp ts = new Timestamp(cal.getTimeInMillis());
	testSpecificTimestamps(ts, ts, ts);

	// Last millisecond before switch from DST.
	cal.clear();
	cal.set(2014, Calendar.OCTOBER, 26, 0, 59, 59);
	cal.set(Calendar.MILLISECOND, 999);
	ts = new Timestamp(cal.getTimeInMillis());
	testSpecificTimestamps(ts, ts, ts);
	}

	/**
	* Test that triggers created before the epoch (Jan 1 1970) fire in the
	* correct order.
	*/
	public void testPreEpoch() throws SQLException {
	// 24 hours before the epoch
	Timestamp ts = new Timestamp(-3600L * 24 * 1000);
	testSpecificTimestamps(ts, ts, ts);

	// Test with some non-zero fractions as well.

	ts.setNanos(123000000);
	testSpecificTimestamps(ts, ts, ts);

	ts.setNanos(567000000);
	testSpecificTimestamps(ts, ts, ts);

	ts.setNanos(999000000);
	testSpecificTimestamps(ts, ts, ts);
	}

	/**
	* Helper method that creates triggers with the specified creation
	* timestamps and verifies that they fire in creation order. The creation
	* timestamps can only be overridden in debug builds. When running in a
	* non-debug build, this method will simply create the triggers without
	* overriding the creation timestamps, and verify that they fire in the
	* expected order.
	*/
	private void testSpecificTimestamps(Timestamp... timestamps)
	throws SQLException {
	setAutoCommit(false);

	Statement s = createStatement();

	s.execute("create table t1(x int)");
	s.execute("create table t2(x int generated always as identity, y int)");

	// Create the triggers.
	for (int i = 0; i < timestamps.length; i++) {
	overrideTriggerCreationTime(timestamps[i]);
	s.execute("create trigger tr" + (i + 1) + " after insert on t1 "
	+ "insert into t2(y) values " + (i + 1));
	}

	// Fire the triggers.
	s.execute("insert into t1 values 1");

	// Verify that the triggers executed in the correct order.
	ResultSet rs = s.executeQuery("select * from t2 order by x");
	for (int i = 1; i <= timestamps.length; i++) {
	if (rs.next()) {
	assertEquals("X", i, rs.getInt("X"));
	assertEquals("Y", i, rs.getInt("Y"));
	} else {
	fail("Row " + i + " was missing");
	}
	}
	JDBC.assertEmpty(rs);

	// Verify that the CREATIONTIMESTAMP column in SYS.SYSTRIGGERS is
	// monotonically increasing.
	PreparedStatement ps = prepareStatement(
	"select * from sys.sysschemas natural join sys.systriggers "
	+ "where schemaname = ? and triggername like 'TR%' "
	+ "order by creationtimestamp");
	ps.setString(1, getTestConfiguration().getUserName());
	rs = ps.executeQuery();
	Timestamp prev = null;
	for (int i = 1; i <= timestamps.length; i++) {
	assertTrue(rs.next());
	assertEquals("TR" + i, rs.getString("TRIGGERNAME"));
	Timestamp ts = rs.getTimestamp("CREATIONTIMESTAMP");
	assertNotNull(ts);
	if (prev != null && !prev.before(ts)) {
	fail(prev + " expected to be before " + ts);
	}
	prev = ts;
	}
	JDBC.assertEmpty(rs);

	rollback();
	}

	/**
	* Set a system property that makes the next CREATE TRIGGER operation
	* use the specified timestamp instead of the current time when
	* constructing the creation timestamp.
	*/
	private void overrideTriggerCreationTime(Timestamp ts) {
	setSystemProperty(OVERRIDE_TIME_PROP, String.valueOf(ts.getTime()));
	}
	}