You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2013/12/18 14:29:52 UTC
svn commit: r1551940 - in /db/derby/code/trunk/java:
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/tools/
tools/org/apache/derby/impl/tools/dblook/
Author: kahatlen
Date: Wed Dec 18 13:29:51 2013
New Revision: 1551940
URL: http://svn.apache.org/r1551940
Log:
DERBY-6387: Incorrect ordering of triggers in dblook
Order the triggers by the CREATIONTIMESTAMP column so that dblook
produces a script that recreates the triggers in the same order as
they were originally created.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_derby6387.sql (with props)
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_app.properties
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_territory_app.properties
db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out?rev=1551940&r1=1551939&r2=1551940&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out Wed Dec 18 13:29:51 2013
@@ -6510,4 +6510,41 @@ File dblook.log was NOT empty. Contents
-- **--> DEBUG: Failed to load jar file <jarFilePath>
java.io.FileNotFoundException: <filePath>
############## End File Contents ################
+-= Start DERBY-6387 test. =-
+Database 'wombat' deleted.
+Creating database 'wombat' from ddl script 'dblook_makeDB_derby6387.sql'
+--
+*******************************************
+Dumping full schema for 'wombat'
+to file 'wombat.sql':
+*******************************************
+File dblook.log was empty.
+File wombat.sql was NOT empty. Contents are:
+############## Begin File Contents ################
+-- ============================
+-- This file was created using Derby's dblook utility.
+-- Timestamp: xxxxxxFILTERED-TIMESTAMPxxxxx
+-- Source database is: <dbPath>
+-- Connection URL is: <dbURL>
+-- appendLogs: false
+-- ----------------------------------------------
+-- DDL Statements for tables
+-- ----------------------------------------------
+CREATE TABLE "APP"."T1" ("X" INTEGER)
+-- ----------------------------------------------
+-- DDL Statements for triggers
+-- ----------------------------------------------
+CREATE TRIGGER "APP"."TR13" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR14" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR15" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR16" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR17" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR18" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR19" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR20" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR21" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR22" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR23" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR24" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+############## End File Contents ################
[ Done. ]
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out?rev=1551940&r1=1551939&r2=1551940&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out Wed Dec 18 13:29:51 2013
@@ -6510,4 +6510,41 @@ File dblook.log was NOT empty. Contents
-- **--> DEBUG: Failed to load jar file <jarFilePath>
java.io.FileNotFoundException: <filePath>
############## End File Contents ################
+-= Start DERBY-6387 test. =-
+Database 'wombat' deleted.
+Creating database 'wombat' from ddl script 'dblook_makeDB_derby6387.sql'
+--
+*******************************************
+Dumping full schema for 'wombat'
+to file 'wombat.sql':
+*******************************************
+File dblook.log was empty.
+File wombat.sql was NOT empty. Contents are:
+############## Begin File Contents ################
+-- ============================
+-- This file was created using Derby's dblook utility.
+-- Timestamp: xxxxxxFILTERED-TIMESTAMPxxxxx
+-- Source database is: <dbPath>
+-- Connection URL is: <dbURL>
+-- appendLogs: false
+-- ----------------------------------------------
+-- DDL Statements for tables
+-- ----------------------------------------------
+CREATE TABLE "APP"."T1" ("X" INTEGER)
+-- ----------------------------------------------
+-- DDL Statements for triggers
+-- ----------------------------------------------
+CREATE TRIGGER "APP"."TR13" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR14" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR15" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR16" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR17" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR18" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR19" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR20" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR21" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR22" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR23" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+CREATE TRIGGER "APP"."TR24" AFTER UPDATE ON "APP"."T1" REFERENCING NEW_TABLE AS NEW FOR EACH STATEMENT select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100)
+############## End File Contents ################
[ Done. ]
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_derby6387.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_derby6387.sql?rev=1551940&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_derby6387.sql (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_derby6387.sql Wed Dec 18 13:29:51 2013
@@ -0,0 +1,76 @@
+--
+-- 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.
+--
+
+--
+-- Script for creating the test database that will be used for testing
+-- that dblook returns triggers in the correct order (which is
+-- important because creation order decides execution order).
+-- Regression test case for DERBY-6387.
+--
+
+create table t1(x int);
+
+-- Create enough triggers to fill at least one page in
+-- SYS.SYSTRIGGERS.
+
+create trigger tr01 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr02 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr03 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr04 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr05 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr06 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr07 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr08 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr09 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr10 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr11 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr12 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr13 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+
+-- Drop some of the triggers to create a hole on the first page of
+-- SYS.SYSTRIGGERS.
+
+drop trigger tr01;
+drop trigger tr02;
+drop trigger tr03;
+drop trigger tr04;
+drop trigger tr05;
+drop trigger tr06;
+drop trigger tr07;
+drop trigger tr08;
+drop trigger tr09;
+drop trigger tr10;
+drop trigger tr11;
+drop trigger tr12;
+call syscs_util.syscs_checkpoint_database();
+
+-- Now fill up the second page of SYS.SYSTRIGGERS. When it's full,
+-- it'll start inserting into the first page, and then the rows in the
+-- table are not in creation order. That's what we need in order to
+-- test that the bug is fixed.
+
+create trigger tr14 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr15 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr16 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr17 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr18 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr19 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr20 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr21 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr22 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr23 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
+create trigger tr24 after update on t1 referencing new table as new for each statement select * from sys.systables natural join sys.sysschemas where exists(select * from new where x > 100);
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_derby6387.sql
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test.java?rev=1551940&r1=1551939&r2=1551940&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test.java Wed Dec 18 13:29:51 2013
@@ -95,6 +95,10 @@ public class dblook_test {
createTestDatabase(dbCreationScript_2);
runMessageCheckTest(testDBName);
+ // Test DERBY-6387 - wrong order of triggers
+ System.out.println("\n-= Start DERBY-6387 test. =-");
+ testDerby6387();
+
} catch (SQLException se) {
System.out.println("FAILED: to complete the test:");
@@ -1713,4 +1717,22 @@ public class dblook_test {
}
+ /**
+ * Regression test case for DERBY-6387. Verify that triggers are returned
+ * in the order in which they were created.
+ */
+ private void testDerby6387() throws Exception {
+ // Create the test database.
+ createTestDatabase("dblook_makeDB_derby6387.sql");
+
+ // Run dblook on it.
+ lookOne(testDBName);
+
+ // Check that the error log was empty.
+ dumpFileToSysOut("dblook.log");
+
+ // Check the dblook output. Before DERBY-6387 was fixed, TR24 was
+ // first in the output. It should be last.
+ dumpFileToSysOut(testDBName + ".sql");
+ }
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_app.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_app.properties?rev=1551940&r1=1551939&r2=1551940&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_app.properties (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_app.properties Wed Dec 18 13:29:51 2013
@@ -14,7 +14,7 @@
# limitations under the License.
ij.user=app
ij.password=apppw
-supportfiles=tests/tools/dblook_makeDB.sql,tests/tools/dblook_makeDB_2.sql,tests/tools/dblook_test.jar
+supportfiles=tests/tools/dblook_makeDB.sql,tests/tools/dblook_makeDB_2.sql,tests/tools/dblook_test.jar,tests/tools/dblook_makeDB_derby6387.sql
#Exclude for J2ME/Foundation - test requires java.sql.DriverManager
runwithfoundation=false
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_territory_app.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_territory_app.properties?rev=1551940&r1=1551939&r2=1551940&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_territory_app.properties (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_test_territory_app.properties Wed Dec 18 13:29:51 2013
@@ -15,7 +15,7 @@
ij.user=app
ij.password=apppw
-supportfiles=tests/tools/dblook_makeDB.sql,tests/tools/dblook_makeDB_2.sql,tests/tools/dblook_test.jar
+supportfiles=tests/tools/dblook_makeDB.sql,tests/tools/dblook_makeDB_2.sql,tests/tools/dblook_test.jar,tests/tools/dblook_makeDB_derby6387.sql
#Exclude for J2ME/Foundation - test requires java.sql.DriverManager
runwithfoundation=false
Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java?rev=1551940&r1=1551939&r2=1551940&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Trigger.java Wed Dec 18 13:29:51 2013
@@ -59,7 +59,8 @@ public class DB_Trigger {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
- "SELECT * FROM SYS.SYSTRIGGERS WHERE STATE != 'D'");
+ "SELECT * FROM SYS.SYSTRIGGERS WHERE STATE != 'D' " +
+ "ORDER BY CREATIONTIMESTAMP");
boolean firstTime = true;
while (rs.next()) {