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 rh...@apache.org on 2006/09/22 17:16:01 UTC
svn commit: r448961 [4/4] - in /db/derby/code/branches/10.2/java: demo/
drda/org/apache/derby/impl/drda/ engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/harness/
testing/org/apache/derbyTesting/functionTests/mast...
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out Fri Sep 22 08:15:58 2006
@@ -408,4 +408,375 @@
0 rows inserted/updated/deleted
ij> drop table table1;
0 rows inserted/updated/deleted
+ij> -- DERBY-1784
+create schema test1;
+0 rows inserted/updated/deleted
+ij> create schema test2;
+0 rows inserted/updated/deleted
+ij> create table test1.t1 ( id bigint not null );
+0 rows inserted/updated/deleted
+ij> insert into test1.t1 values 1;
+1 row inserted/updated/deleted
+ij> create synonym test2.t1 for test1.t1;
+0 rows inserted/updated/deleted
+ij> set schema test1;
+0 rows inserted/updated/deleted
+ij> select t1.id from t1;
+ID
+--------------------
+1
+ij> set schema test2;
+0 rows inserted/updated/deleted
+ij> select id from t1;
+ID
+--------------------
+1
+ij> select id from test2.t1;
+ID
+--------------------
+1
+ij> select t1.id from t1;
+ID
+--------------------
+1
+ij> select t1.id from test2.t1;
+ID
+--------------------
+1
+ij> select test2.t1.id from t1;
+ID
+--------------------
+1
+ij> select test2.t1.id from test2.t1;
+ID
+--------------------
+1
+ij> drop synonym t1;
+0 rows inserted/updated/deleted
+ij> drop table test1.t1;
+0 rows inserted/updated/deleted
+ij> set schema app;
+0 rows inserted/updated/deleted
+ij> create table A (id integer);
+0 rows inserted/updated/deleted
+ij> insert into A values 29;
+1 row inserted/updated/deleted
+ij> create synonym B for A;
+0 rows inserted/updated/deleted
+ij> select a.id from a;
+ID
+-----------
+29
+ij> select b.id from b;
+ID
+-----------
+29
+ij> select b.id from b as b;
+ID
+-----------
+29
+ij> select b.id from (select b.id from b) as b;
+ID
+-----------
+29
+ij> select b.id from (select b.id from b as b) as b;
+ID
+-----------
+29
+ij> drop synonym B;
+0 rows inserted/updated/deleted
+ij> drop table A;
+0 rows inserted/updated/deleted
+ij> create table t1 (i int, j int);
+0 rows inserted/updated/deleted
+ij> create view v1 as select * from t1;
+0 rows inserted/updated/deleted
+ij> insert into t1 values (1, 10);
+1 row inserted/updated/deleted
+ij> create synonym s1 for t1;
+0 rows inserted/updated/deleted
+ij> create synonym sv1 for v1;
+0 rows inserted/updated/deleted
+ij> -- should fail
+select t1.i from s1;
+ERROR 42X04: Column 'T1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.I' is not a column in the target table.
+ij> select v1.i from sv1;
+ERROR 42X04: Column 'V1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'V1.I' is not a column in the target table.
+ij> select sv1.i from sv1 as w1;
+ERROR 42X04: Column 'SV1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SV1.I' is not a column in the target table.
+ij> select s1.j from s1 where s1.k = 1;
+ERROR 42X04: Column 'S1.K' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S1.K' is not a column in the target table.
+ij> select s1.j from s1 where w1.i = 1;
+ERROR 42X04: Column 'W1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'W1.I' is not a column in the target table.
+ij> select * from s1 where w1.i = 1;
+ERROR 42X04: Column 'W1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'W1.I' is not a column in the target table.
+ij> select s1.j from s1 as w1 where w1.i = 1;
+ERROR 42X04: Column 'S1.J' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S1.J' is not a column in the target table.
+ij> select w1.j from s1 as w1 where s1.i = 1;
+ERROR 42X04: Column 'S1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S1.I' is not a column in the target table.
+ij> select s1.j from s1 where t1.i = 1;
+ERROR 42X04: Column 'T1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.I' is not a column in the target table.
+ij> select s1.j from s1 group by t1.j;
+ERROR 42X04: Column 'T1.J' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.J' is not a column in the target table.
+ij> select s1.j from s1 group by s1.j having t1.j > 0;
+ERROR 42X04: Column 'T1.J' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.J' is not a column in the target table.
+ij> insert into s1 (t1.i) values 100;
+ERROR 42X55: Table name 'T1' should be the same as 'S1'.
+ij> update s1 set t1.i=1;
+ERROR 42X55: Table name 'T1' should be the same as 'S1'.
+ij> delete from s1 where t1.i=100;
+ERROR 42X04: Column 'T1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.I' is not a column in the target table.
+ij> -- ok
+select s1.i from s1;
+I
+-----------
+1
+ij> select s1.i from s1 as s1;
+I
+-----------
+1
+ij> select s1.i from s1 where i = 1;
+I
+-----------
+1
+ij> select s1.i from s1 where s1.i = 1;
+I
+-----------
+1
+ij> select s1.i from s1 as s1 where i = 1;
+I
+-----------
+1
+ij> select w1.i from s1 as w1 where w1.i = 1;
+I
+-----------
+1
+ij> select sv1.i from sv1;
+I
+-----------
+1
+ij> select sv1.i from sv1 as sv1;
+I
+-----------
+1
+ij> select sv1.i from sv1 where i = 1;
+I
+-----------
+1
+ij> select sv1.i from sv1 where sv1.i = 1;
+I
+-----------
+1
+ij> select sv1.i from sv1 as sv1 where i = 1;
+I
+-----------
+1
+ij> select wv1.i from sv1 as wv1 where wv1.i = 1;
+I
+-----------
+1
+ij> select s1.i, s1.i from s1;
+I |I
+-----------------------
+1 |1
+ij> select sv1.i, sv1.i from sv1;
+I |I
+-----------------------
+1 |1
+ij> select * from s1;
+I |J
+-----------------------
+1 |10
+ij> select * from s1 where i = 1;
+I |J
+-----------------------
+1 |10
+ij> select * from s1 where s1.i = 1;
+I |J
+-----------------------
+1 |10
+ij> select * from s1 as s1;
+I |J
+-----------------------
+1 |10
+ij> select * from s1 as w1;
+I |J
+-----------------------
+1 |10
+ij> select * from sv1;
+I |J
+-----------------------
+1 |10
+ij> select * from sv1 as sv1;
+I |J
+-----------------------
+1 |10
+ij> select * from sv1 as w1;
+I |J
+-----------------------
+1 |10
+ij> select * from sv1 where i = 1;
+I |J
+-----------------------
+1 |10
+ij> select * from sv1 where sv1.i = 1;
+I |J
+-----------------------
+1 |10
+ij> select s1.i from (select s1.i from s1) as s1;
+I
+-----------
+1
+ij> select sv1.i from (select sv1.i from sv1) as sv1;
+I
+-----------
+1
+ij> create table t2 (i int, j int);
+0 rows inserted/updated/deleted
+ij> insert into t2 values (1, 100), (1, 100), (2, 200);
+3 rows inserted/updated/deleted
+ij> create view v2 as select * from t2;
+0 rows inserted/updated/deleted
+ij> create synonym s2 for t2;
+0 rows inserted/updated/deleted
+ij> create synonym sv2 for v2;
+0 rows inserted/updated/deleted
+ij> select s2.j from s2 group by s2.j order by s2.j;
+J
+-----------
+100
+200
+ij> select s2.j from s2 group by s2.j having s2.j > 100 order by s2.j;
+J
+-----------
+200
+ij> select s1.i, s1.j from (select s1.i, s2.j from s1,s2 where s1.i=s2.i) as s1;
+I |J
+-----------------------
+1 |100
+1 |100
+ij> select sv2.j from sv2 group by sv2.j order by sv2.j;
+J
+-----------
+100
+200
+ij> select sv2.j from sv2 group by sv2.j having sv2.j > 100 order by sv2.j;
+J
+-----------
+200
+ij> select sv1.i, sv1.j from (select sv1.i, sv2.j from sv1,sv2 where sv1.i=sv2.i) as sv1;
+I |J
+-----------------------
+1 |100
+1 |100
+ij> select max(s2.i) from s2;
+1
+-----------
+2
+ij> select max(sv2.i) from sv2;
+1
+-----------
+2
+ij> select * from s1 inner join s2 on (s1.i = s2.i);
+I |J |I |J
+-----------------------------------------------
+1 |10 |1 |100
+1 |10 |1 |100
+ij> select * from sv1 inner join sv2 on (sv1.i = sv2.i);
+I |J |I |J
+-----------------------------------------------
+1 |10 |1 |100
+1 |10 |1 |100
+ij> select s1.* from s1;
+I |J
+-----------------------
+1 |10
+ij> select sv1.* from sv1;
+I |J
+-----------------------
+1 |10
+ij> create table t3 (i int, j int);
+0 rows inserted/updated/deleted
+ij> insert into t3 values (10, 0), (11, 0), (12, 0);
+3 rows inserted/updated/deleted
+ij> create synonym s3 for t3;
+0 rows inserted/updated/deleted
+ij> insert into s1 (s1.i, s1.j) values (2, 20);
+1 row inserted/updated/deleted
+ij> insert into app.s1 (s1.i, s1.j) values (3, 30);
+1 row inserted/updated/deleted
+ij> insert into app.s1 (app.s1.i, s1.j) values (4, 40);
+1 row inserted/updated/deleted
+ij> insert into app.s1 (app.s1.i, app.s1.j) values (5, 50);
+1 row inserted/updated/deleted
+ij> update s1 set s1.j = 1;
+5 rows inserted/updated/deleted
+ij> update app.s1 set s1.j = 2;
+5 rows inserted/updated/deleted
+ij> update app.s1 set app.s1.j = 3;
+5 rows inserted/updated/deleted
+ij> update s1 set s1.j = 4 where s1.i = 3;
+1 row inserted/updated/deleted
+ij> update app.s1 set app.s1.j = 5 where app.s1.i = 4;
+1 row inserted/updated/deleted
+ij> delete from s1 where s1.i = 4;
+1 row inserted/updated/deleted
+ij> delete from app.s1 where app.s1.i = 5;
+1 row inserted/updated/deleted
+ij> update app.s1 set s1.j = s1.i, s1.i = s1.j;
+3 rows inserted/updated/deleted
+ij> select * from s1;
+I |J
+-----------------------
+3 |1
+3 |2
+4 |3
+ij> update app.s1 set s1.j = s1.i, s1.i = s1.j;
+3 rows inserted/updated/deleted
+ij> select * from s1;
+I |J
+-----------------------
+1 |3
+2 |3
+3 |4
+ij> delete from s1;
+3 rows inserted/updated/deleted
+ij> -- should fail
+insert into s1 (s1.i) select s1.i from s3;
+ERROR 42X04: Column 'S1.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S1.I' is not a column in the target table.
+ij> -- ok
+insert into s1 (s1.i) select s3.i from s3;
+3 rows inserted/updated/deleted
+ij> insert into s1 select * from s3;
+3 rows inserted/updated/deleted
+ij> select * from s1;
+I |J
+-----------------------
+10 |NULL
+11 |NULL
+12 |NULL
+10 |0
+11 |0
+12 |0
+ij> -- clean up
+drop synonym s3;
+0 rows inserted/updated/deleted
+ij> drop synonym sv2;
+0 rows inserted/updated/deleted
+ij> drop synonym s2;
+0 rows inserted/updated/deleted
+ij> drop synonym s1;
+0 rows inserted/updated/deleted
+ij> drop synonym sv1;
+0 rows inserted/updated/deleted
+ij> drop view v2;
+0 rows inserted/updated/deleted
+ij> drop view v1;
+0 rows inserted/updated/deleted
+ij> drop table t3;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> drop table t1;
+0 rows inserted/updated/deleted
ij>
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Fri Sep 22 08:15:58 2006
@@ -132,6 +132,7 @@
lang/subquery2.sql
lang/subqueryFlattening.sql
lang/supersimple.sql
+lang/synonym.sql
lang/syscat.sql
lang/tempRestrictions.sql
lang/timestampArith.java
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/derbynet/testProperties.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/derbynet/testProperties.java?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/derbynet/testProperties.java (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/derbynet/testProperties.java Fri Sep 22 08:15:58 2006
@@ -56,6 +56,15 @@
private static jvm jvm;
private static Vector vCmd;
private static BufferedOutputStream bos = null;
+
+ /**
+ * For each new exec process done as part of this test, set
+ * timeout for ProcessStreamResult after which the thread that
+ * handles the streams for the process exits. Timeout is in minutes.
+ * Note: timeout handling will only come into effect when
+ * ProcessStreamResult#Wait() is called
+ */
+ private static String timeoutMinutes = "2";
//Command to start server specifying system properties without values
private static String[] startServerCmd =
@@ -115,8 +124,8 @@
* Execute the given command and optionally wait and dump the results to standard out
*
* @param args command and arguments
- * @param wait true =wait for completion and dump output, false don't wait and
- * ignore the output.
+ * @param wait true =wait for either completion or timeout time and dump output,
+ * false don't wait and ignore the output.
* @exception Exception
*/
@@ -138,13 +147,16 @@
}
// Start a process to run the command
Process pr = execCmd(args);
- prout = new ProcessStreamResult(pr.getInputStream(), _bos, null);
- prerr = new ProcessStreamResult(pr.getErrorStream(), _bos, null);
+
+ // Note, the timeout handling will only come into effect when we make
+ // the Wait() call on ProcessStreamResult.
+ prout = new ProcessStreamResult(pr.getInputStream(), _bos, timeoutMinutes);
+ prerr = new ProcessStreamResult(pr.getErrorStream(), _bos, timeoutMinutes);
if (!wait)
return;
- // wait until all the results have been processed
+ // wait until all the results have been processed or if we timed out
prout.Wait();
prerr.Wait();
_bos.flush();
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource.java?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource.java (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource.java Fri Sep 22 08:15:58 2006
@@ -78,10 +78,6 @@
// Only embedded supports SimpleDataSource (JSR169).
// These tests are exempted from other frameworks
private boolean testSimpleDataSource = TestUtil.isEmbeddedFramework();
-
- // DERBY-1326 - Network server may abandon sessions when Derby system is shutdown
- // and this causes intermittent hangs in the client
- private static boolean hangAfterSystemShutdown = TestUtil.isDerbyNetClientFramework();
/**
* A hashtable of opened connections. This is used when checking to
@@ -279,14 +275,10 @@
testPoolReset("XADataSource", dsx.getXAConnection());
-
- // DERBY-1326 - hang in client after Derby system shutdown
- if(! hangAfterSystemShutdown) {
- try {
- TestUtil.getConnection("","shutdown=true");
- } catch (SQLException sqle) {
- JDBCDisplayUtil.ShowSQLException(System.out, sqle);
- }
+ try {
+ TestUtil.getConnection("","shutdown=true");
+ } catch (SQLException sqle) {
+ JDBCDisplayUtil.ShowSQLException(System.out, sqle);
}
dmc = ij.startJBMS();
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/declareGlobalTempTableJava.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/declareGlobalTempTableJava.java?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/declareGlobalTempTableJava.java (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/declareGlobalTempTableJava.java Fri Sep 22 08:15:58 2006
@@ -68,6 +68,10 @@
con1.setAutoCommit(false);
con2.setAutoCommit(false);
+ /* Test this before other tests because this test requires
+ that session schema has not been created yet */
+ passed = testDERBY1706(con1, s) && passed;
+
/* Test various schema and grammar related cases */
passed = testSchemaNameAndGrammar(con1, s) && passed;
@@ -88,6 +92,49 @@
System.out.println("PASS");
System.out.println("Test declaredGlobalTempTable finished");
+ }
+
+ /**
+ * Test switching to session schema (it doesn't yet exist because
+ * no create schema session has been issued yet) & then try to create
+ * first persistent object in it. This used to cause null pointer
+ * exception (DERBY-1706).
+ *
+ * @param conn The Connection
+ * @param s A Statement on the Connection
+ *
+ * @return true if it succeeds, false if it doesn't
+ *
+ * @exception SQLException Thrown if some unexpected error happens
+ */
+
+ static boolean testDERBY1706(Connection con1, Statement s)
+ throws SQLException {
+ boolean passed = true;
+
+ try
+ {
+ System.out.print("TEST-DERBY1706 : Create a persistent object");
+ System.out.print(" in SESSION schema w/o first creating the");
+ System.out.println(" schema");
+
+ s.executeUpdate("set schema SESSION");
+ s.executeUpdate("create table DERBY1706(c11 int)");
+ s.executeUpdate("drop table DERBY1706");
+ s.executeUpdate("set schema APP");
+ s.executeUpdate("drop schema SESSION restrict");
+
+ con1.commit();
+ System.out.println("TEST-DERBY1706 PASSED");
+ } catch (Throwable e)
+ {
+ System.out.println("Unexpected message: " + e.getMessage());
+ con1.rollback();
+ passed = false; //we shouldn't have reached here. Set passed to false to indicate failure
+ System.out.println("TEST-DERBY1706 FAILED");
+ }
+
+ return passed;
}
/**
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql Fri Sep 22 08:15:58 2006
@@ -1924,3 +1924,27 @@
update mamta2.ttt1 set i = 888;
commit;
autocommit on;
+
+-- Simple test case for DERBY-1589. The problem here involves dependency
+-- management between the FOREIGN KEY clause in the CREATE TABLE statement
+-- and the underlying table that the FK refers to. The statement must
+-- declare a dependency on the referenced table so that changes to the table
+-- cause invalidation of the statement's compiled plan. The test case below
+-- sets up such a situation by dropping the referenced table and recreating
+-- it and then re-issuing a statement with identical text to one which
+-- was issued earlier.
+
+set connection mamta1;
+create table d1589t11ConstraintTest (c111 int not null, c112 int not null, primary key (c111, c112));
+grant references on d1589t11ConstraintTest to mamta3;
+set connection mamta3;
+drop table d1589t31ConstraintTest;
+create table d1589t31ConstraintTest (c311 int, c312 int, foreign key(c311, c312) references mamta1.d1589t11ConstraintTest);
+drop table d1589t31ConstraintTest;
+set connection mamta1;
+drop table d1589t11ConstraintTest;
+create table d1589t11ConstraintTest (c111 int not null, c112 int not null, primary key (c111, c112));
+grant references(c111) on d1589t11ConstraintTest to mamta3;
+grant references(c112) on d1589t11ConstraintTest to PUBLIC;
+set connection mamta3;
+create table d1589t31ConstraintTest (c311 int, c312 int, foreign key(c311, c312) references mamta1.d1589t11ConstraintTest);
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql Fri Sep 22 08:15:58 2006
@@ -1,7 +1,12 @@
+-- -------------------------------------------------------------------
+-- GRANT and REVOKE test Part 2
+-- -------------------------------------------------------------------
connect 'grantRevokeDDL2;create=true' user 'user1' as user1;
-connect 'grantRevokeDDL2;create=true' user 'user2' as user2;
-connect 'grantRevokeDDL2;create=true' user 'user3' as user3;
-
+connect 'grantRevokeDDL2' user 'user2' as user2;
+connect 'grantRevokeDDL2' user 'user3' as user3;
+connect 'grantRevokeDDL2' user 'user4' as user4;
+connect 'grantRevokeDDL2' user 'user5' as user5;
+
-- DERBY-1729
-- test grant and revoke in Java stored procedure with triggers.
-- Java stored procedure that contains grant or revoke statement
@@ -174,4 +179,1030 @@
-- should fail
select * from user1.t1 where i = 1;
+set connection user1;
+drop table t2;
+drop table t1;
+
+-- -------------------------------------------------------------------
+-- table privileges (tp)
+-- -------------------------------------------------------------------
+set connection user1;
+create table t1 (c1 int primary key not null, c2 varchar(10));
+create table t2 (c1 int primary key not null, c2 varchar(10), c3 int);
+create index idx1 on t1(c2);
+insert into t1 values (1, 'a'), (2, 'b'), (3, 'c');
+insert into t2 values (1, 'Yip', 10);
+select * from t1;
+CREATE FUNCTION F_ABS1(P1 INT)
+ RETURNS INT NO SQL
+ RETURNS NULL ON NULL INPUT
+ EXTERNAL NAME 'java.lang.Math.abs'
+ LANGUAGE JAVA PARAMETER STYLE JAVA;
+values f_abs1(-5);
+-- grant on a non-existing table, expect error
+grant select on table t0 to user2;
+-- revoke on a non-existing table, expect error
+revoke select on table t0 from user2;
+-- grant more than one table, expect error
+grant select on t0, t1 to user2;
+-- revoke more than one table, expect error
+revoke select on t0, t1 from user2;
+-- revoking privilege that has not been granted, expect warning
+revoke select,insert,update,delete,trigger,references on t1 from user2;
+-- syntax errors, expect errors
+grant select on t1 from user2;
+revoke select on t1 to user2;
+-- redundant but ok
+grant select, select on t1 to user2;
+revoke select, select on t1 from user2;
+
+-- switch to user2
+set connection user2;
+-- test SELECT privilege, expect error
+select * from user1.t1;
+-- test INSERT privilege, expect error
+insert into user1.t1(c1) values 4;
+-- test UPDATE privilege, expect error
+update user1.t1 set c1=10;
+-- test DELETE privilege, expect error
+delete from user1.t1;
+-- test REFERENCES privilege, expect error
+create table t2 (c1 int primary key not null, c2 int references user1.t1);
+-- test TRIGGER privilege, expect error
+create trigger trigger1 after update on user1.t1 for each statement mode db2sql values integer('123');
+-- try to DROP user1.idx1 index, expect error
+drop index user1.idx1;
+-- try to DROP user1.t1 table, expect error
+drop table user1.t1;
+-- non privileged user try to grant privileges on user1.t1, expect error
+grant select,insert,delete,update,references,trigger on user1.t1 to user2;
+-- try to grant privileges for public on user1.t1, expect error
+grant select,insert,delete,update,references,trigger on user1.t1 to public;
+-- try to grant all privileges for user2 on user1.t1, expect error
+grant ALL PRIVILEGES on user1.t1 to user2;
+-- try to grant all privileges on user1.t1 to public, expect error
+grant ALL PRIVILEGES on user1.t1 to public;
+-- try to revoke user1 from table user1.t1, expect error
+revoke select,insert,delete,update,references,trigger on user1.t1 from user1;
+-- try to revoke all privileges from user1 on table user1.t1, expect error
+revoke ALL PRIVILEGES on user1.t1 from user1;
+-- try to revoke execute on a non-existing function on user1.t1, expect error
+revoke execute on function user1.f1 from user1 restrict;
+
+create table t2 (c1 int);
+-- try revoking yourself from user2.t2, expect error
+revoke select on t2 from user2;
+-- try granting yourself again on user2.t2, expect error. Why?
+grant select on t2 to user2;
+-- try granting yourself multiple times, expect error. Why?
+grant insert on t2 to user2,user2,user2;
+
+-- try to execute user1.F_ABS1, expect error
+values user1.F_ABS1(-9);
+
+set connection user1;
+select * from sys.systableperms;
+select * from sys.syscolperms;
+select * from sys.sysroutineperms;
+
+grant select,update on table t1 to user2, user3;
+grant execute on function F_ABS1 to user2;
+select * from sys.systableperms;
+select * from sys.syscolperms;
+select * from sys.sysroutineperms;
+
+set connection user2;
+-- try to select from t1, ok
+select * from user1.t1;
+-- try to insert from t1, expect error
+insert into user1.t1 values (5, 'e');
+-- ok
+values user1.F_ABS1(-8);
+-- ok
+update user1.t1 set c2 = 'user2';
+
+set connection user1;
+-- add a column to t1, user2 should still be able to select
+alter table t1 add column c3 varchar(10);
+set connection user2;
+-- ok
+select * from user1.t1;
+-- error
+insert into user1.t1 values (2, 'abc', 'ABC');
+-- ok
+update user1.t1 set c3 = 'XYZ';
+
+set connection user3;
+-- try to select from t1, ok
+select * from user1.t1;
+-- user3 does not have permission to execute, expect error
+values user1.F_ABS1(-8);
+-- ok
+update user1.t1 set c2 = 'user3';
+
+set connection user1;
+-- expect warnings
+revoke update(c2) on t1 from user3;
+revoke select(c2) on t1 from user3;
+
+set connection user2;
+-- ok
+update user1.t1 set c2 = 'user2';
+
+set connection user3;
+-- revoking part of table privilege raises warning, so ok
+update user1.t1 set c2 = 'user3';
+-- same as above
+select * from user1.t1;
+-- same as above
+select c2 from user1.t1;
+
+set connection user1;
+grant select, update on t1 to PUBLIC;
+set connection user3;
+-- ok, use PUBLIC
+select * from user1.t1;
+-- ok, use PUBLIC
+update user1.t1 set c2 = 'user3';
+
+set connection user1;
+grant select on t1 to user3;
+-- revoke select from PUBLIC
+revoke select on t1 from PUBLIC;
+set connection user3;
+-- ok, privileged
+select * from user1.t1;
+-- ok, use PUBLIC
+update user1.t1 set c2 = 'user3';
+set connection user1;
+revoke select, update on t1 from user3;
+revoke update on t1 from PUBLIC;
+set connection user3;
+-- expect error
+select * from user1.t1;
+-- expect error
+update user1.t1 set c2 = 'user3';
+
+set connection user1;
+declare global temporary table SESSION.t1(c1 int) not logged;
+-- expect error
+grant select on session.t1 to user2;
+revoke select on session.t1 from user2;
+
+-- -------------------------------------------------------------------
+-- column privileges
+-- -------------------------------------------------------------------
+set connection user1;
+create table t3 (c1 int, c2 varchar(10), c3 int);
+create table t4 (c1 int, c2 varchar(10), c3 int);
+-- grant table select privilege then revoke partially
+grant select, update on t3 to user2;
+-- expect warning
+revoke select(c1) on t3 from user2;
+revoke update(c2) on t3 from user2;
+set connection user2;
+select * from user1.t3;
+
+set connection user1;
+grant select (c2, c3), update (c2), insert on t4 to user2;
+set connection user2;
+-- expect error
+select * from user1.t4;
+-- expect error
+select c1 from user1.t4;
+-- ok
+select c2, c3 from user1.t4;
+-- expect error
+update user1.t4 set c1=10, c3=100;
+-- ok
+update user1.t4 set c2='XYZ';
+set connection user1;
+
+-- DERBY-1847
+-- alter table t4 add column c4 int;
+-- set connection user2;
+-- expect error
+-- select c4 from user1.t4;
+-- ok
+-- select c2 from user1.t4;
+
+set connection user1;
+-- revoke all columns
+revoke select, update on t4 from user2;
+set connection user2;
+-- expect error
+select c2 from user1.t4;
+-- expect error
+update user1.t4 set c2='ABC';
+
+-- -------------------------------------------------------------------
+-- schemas
+-- -------------------------------------------------------------------
+set connection user2;
+-- expect error
+create table myschema.t5 (i int);
+-- ok
+create table user2.t5 (i int);
+
+-- expect error
+CREATE SCHEMA w3 AUTHORIZATION user2;
+create table w3.t1 (i int);
+
+-- expect error, already exists
+CREATE SCHEMA AUTHORIZATION user2;
+
+-- expect error
+CREATE SCHEMA myschema;
+
+-- expect error
+CREATE SCHEMA user2;
+
+set connection user1;
+-- ok
+CREATE SCHEMA w3 AUTHORIZATION user2;
+CREATE SCHEMA AUTHORIZATION user6;
+CREATE SCHEMA myschema;
+
+-- -------------------------------------------------------------------
+-- views
+-- -------------------------------------------------------------------
+set connection user1;
+create view sv1 as select * from sys.systables;
+set connection user2;
+-- expect error
+select tablename from user1.sv1;
+set connection user1;
+grant select on sv1 to user2;
+set connection user2;
+-- ok
+select tablename from user1.sv1;
+
+set connection user1;
+create table ta (i int);
+insert into ta values 1,2,3;
+create view sva as select * from ta;
+create table tb (j int);
+insert into tb values 2,3,4;
+create view svb as select * from tb;
+grant select on sva to user2;
+set connection user2;
+-- expect error
+create view svc (i) as select * from user1.sva union select * from user1.svb;
+set connection user1;
+grant select on svb to user2;
+set connection user2;
+-- ok
+create view svc (i) as select * from user1.sva union select * from user1.svb;
+select * from svc;
+
+-- DERBY-1715, DERBY-1631
+--set connection user1;
+--create table t01 (i int);
+--insert into t01 values 1;
+--grant select on t01 to user2;
+--set connection user2;
+--select * from user1.t01;
+--create view v01 as select * from user1.t01;
+--create view v02 as select * from user2.v01;
+--create view v03 as select * from user2.v02;
+--set connection user1;
+--revoke select on t01 from user2;
+--set connection user2;
+--select * from user1.t01;
+--select * from user2.v01;
+--select * from user2.v02;
+--select * from user2.v03;
+--drop view user2.v01;
+--drop view user2.v02;
+--drop view user3.v03;
+
+-- grant all privileges then create the view
+set connection user1;
+create table t01ap (i int);
+insert into t01ap values 1;
+grant all privileges on t01ap to user2;
+set connection user2;
+-- ok
+create view v02ap as select * from user1.t01ap;
+-- ok
+select * from v02ap;
+
+-- expect error, don't have with grant option
+grant select on user2.v02ap to user3;
+set connection user3;
+-- expect error
+create view v03ap as select * from user2.v02ap;
+select * from v03ap;
+-- expect error
+grant all privileges on v03ap to user4;
+set connection user4;
+-- expect error
+create view v04ap as select * from user3.v03ap;
+select * from v04ap;
+-- expect error
+grant select on v04ap to user2;
+
+set connection user2;
+select * from user4.v04ap;
+
+set connection user4;
+-- expect error
+revoke select on v04ap from user2;
+
+set connection user2;
+-- expect error
+select * from user4.v04ap;
+
+-- -------------------------------------------------------------------
+-- references and constraints
+-- -------------------------------------------------------------------
+set connection user1;
+drop table user1.rt1;
+drop table user2.rt2;
+create table rt1 (c1 int not null primary key, c2 int not null);
+insert into rt1 values (1, 10);
+insert into rt1 values (2, 20);
+set connection user2;
+-- expect error
+create table rt2 (c1 int primary key not null, c2 int not null, c3 int not null, constraint rt2fk foreign key(c1) references user1.rt1);
+set connection user1;
+grant references on rt1 to user2;
+set connection user2;
+-- ok
+create table rt2 (c1 int primary key not null, c2 int not null, c3 int not null, constraint rt2fk foreign key(c2) references user1.rt1);
+insert into rt2 values (1,1,1);
+-- expect error
+insert into rt2 values (3,3,3);
+set connection user1;
+revoke references on rt1 from user2;
+set connection user2;
+-- ok, fk constraint got dropped by revoke
+insert into rt2 values (3,3,3);
+select * from rt2;
+-- expect errors
+create table rt3 (c1 int primary key not null, c2 int not null, c3 int not null, constraint rt3fk foreign key(c1) references user1.rt1);
+
+-- test PUBLIC
+-- DERBY-1857
+--set connection user1;
+--drop table user3.rt3;
+--drop table user2.rt2;
+--drop table user1.rt1;
+--create table rt1 (c1 int primary key not null, c2 int not null unique, c3 int not null);
+--insert into rt1 values (1,1,1);
+--insert into rt1 values (2,2,2);
+--insert into rt1 values (3,3,3);
+--grant references(c2, c1) on rt1 to PUBLIC;
+--set connection user2;
+--create table rt2 (c1 int primary key not null, constraint rt2fk foreign key(c1) references user1.rt1(c2) );
+--insert into rt2 values (1), (2);
+--set connection user3;
+--create table rt3 (c1 int primary key not null, constraint rt3fk foreign key(c1) references user1.rt1(c2) );
+--insert into rt3 values (1), (2);
+--set connection user1;
+--revoke references(c1) on rt1 from PUBLIC;
+--set connection user2;
+-- expect constraint error
+--insert into rt2 values (4);
+--set connection user3;
+-- expect constraint error
+--insert into rt3 values (4);
+
+-- test user privilege and PUBLIC
+set connection user1;
+drop table user3.rt3;
+drop table user2.rt2;
+drop table user1.rt1;
+create table rt1 (c1 int primary key not null, c2 int);
+insert into rt1 values (1,1), (2,2);
+grant references on rt1 to PUBLIC, user2, user3;
+set connection user2;
+create table rt2 (c1 int primary key not null, constraint rt2fk foreign key(c1) references user1.rt1);
+insert into rt2 values (1), (2);
+set connection user3;
+create table rt3 (c1 int primary key not null, constraint rt3fk foreign key(c1) references user1.rt1);
+insert into rt3 values (1), (2);
+set connection user1;
+-- ok, use the privilege granted to user2
+revoke references on rt1 from PUBLIC;
+-- ok, user3 got no privileges, so rt3fk should get dropped.
+revoke references on rt1 from user3;
+set connection user2;
+-- expect error, FK enforced.
+insert into rt2 values (3);
+set connection user3;
+-- ok
+insert into rt3 values (3);
+
+-- test multiple FKs
+-- DERBY-1589?
+--set connection user1;
+--drop table user3.rt3;
+--drop table user2.rt2;
+--drop table user1.rt1;
+--create table rt1 (c1 int primary key not null, c2 int);
+--insert into rt1 values (1,1), (2,2);
+--grant references on rt1 to PUBLIC, user2, user3;
+--set connection user2;
+-- XJ001 occurred at create table rt2...
+--create table rt2 (c1 int primary key not null, constraint rt2fk foreign key(c1) references user1.rt1);
+--insert into rt2 values (1), (2);
+--grant references on rt2 to PUBLIC, user3;
+--set connection user3;
+--create table rt3 (c1 int primary key not null, constraint rt3fk1 foreign key(c1) references user1.rt1,
+-- constraint rt3fk2 foreign key(c1) references user1.rt2);
+--insert into rt3 values (1), (2);
+
+--set connection user1;
+-- rt3fk1 should get dropped.
+--revoke references on rt1 from PUBLIC;
+--revoke references on rt1 from user3;
+
+--set connection user2;
+--revoke references on rt2 from PUBLIC;
+
+-- expect error
+--insert into rt2 values (3);
+--set connection user3;
+-- expect error, use user3 references privilege, rt3fk2 still in effect
+--insert into rt3 values (3);
+--set connection user2;
+--revoke references on rt2 from user3;
+--set connection user3;
+-- ok, rt3fk2 should be dropped.
+--insert into rt3 values (3);
+
+-- -------------------------------------------------------------------
+-- routines and standard builtins
+-- -------------------------------------------------------------------
+set connection user1;
+CREATE FUNCTION F_ABS2(P1 INT)
+ RETURNS INT NO SQL
+ RETURNS NULL ON NULL INPUT
+ EXTERNAL NAME 'java.lang.Math.abs'
+ LANGUAGE JAVA PARAMETER STYLE JAVA;
+
+-- syntax error
+grant execute on F_ABS2 to user2;
+-- F_ABS2 is not a procedure, expect errors
+grant execute on procedure F_ABS2 to user2;
+
+set connection user2;
+-- expect errors
+values user1.F_ABS1(10) + user1.F_ABS2(-10);
+set connection user1;
+-- ok
+grant execute on function F_ABS2 to user2;
+set connection user2;
+-- ok
+values user1.F_ABS1(10) + user1.F_ABS2(-10);
+
+-- expect errors
+revoke execute on function ABS from user2 restrict;
+revoke execute on function AVG from user2 restrict;
+revoke execute on function LENGTH from user2 restrict;
+
+set connection user1;
+-- ok
+revoke execute on function F_ABS2 from user2 restrict;
+revoke execute on function F_ABS1 from user2 restrict;
+
+set connection user2;
+-- expect error
+values user1.F_ABS1(10) + user1.F_ABS2(-10);
+
+set connection user1;
+-- ok
+grant execute on function F_ABS1 to PUBLIC;
+grant execute on function F_ABS2 to PUBLIC;
+
+set connection user2;
+-- ok
+values user1.F_ABS1(10) + user1.F_ABS2(-10);
+
+-- -------------------------------------------------------------------
+-- system tables
+-- -------------------------------------------------------------------
+set connection user1;
+-- not allowed. expect errors, sanity check
+grant ALL PRIVILEGES on sys.sysaliases to user2;
+grant ALL PRIVILEGES on sys.syschecks to user2;
+grant ALL PRIVILEGES on sys.syscolperms to user2;
+grant ALL PRIVILEGES on sys.syscolumns to user2;
+grant ALL PRIVILEGES on sys.sysconglomerates to user2;
+grant ALL PRIVILEGES on sys.sysconstraints to user2;
+grant ALL PRIVILEGES on sys.sysdepends to user2;
+grant ALL PRIVILEGES on sys.sysfiles to user2;
+grant ALL PRIVILEGES on sys.sysforeignkeys to user2;
+grant ALL PRIVILEGES on sys.syskeys to user2;
+grant ALL PRIVILEGES on sys.sysroutineperms to user2;
+grant ALL PRIVILEGES on sys.sysschemas to user2;
+grant ALL PRIVILEGES on sys.sysstatistics to user2;
+grant ALL PRIVILEGES on sys.sysstatements to user2;
+grant ALL PRIVILEGES on sys.systableperms to user2;
+grant ALL PRIVILEGES on sys.systables to user2;
+grant ALL PRIVILEGES on sys.systriggers to user2;
+grant ALL PRIVILEGES on sys.sysviews to user2;
+grant ALL PRIVILEGES on syscs_diag.lock_table to user2;
+
+grant select on sys.sysaliases to user2, public;
+grant select on sys.syschecks to user2, public;
+grant select on sys.syscolperms to user2, public;
+grant select on sys.syscolumns to user2, public;
+grant select on sys.sysconglomerates to user2, public;
+grant select on sys.sysconstraints to user2, public;
+grant select on sys.sysdepends to user2, public;
+grant select on sys.sysfiles to user2, public;
+grant select on sys.sysforeignkeys to user2, public;
+grant select on sys.syskeys to user2, public;
+grant select on sys.sysroutineperms to user2, public;
+grant select on sys.sysschemas to user2, public;
+grant select on sys.sysstatistics to user2, public;
+grant select on sys.sysstatements to user2, public;
+grant select on sys.systableperms to user2, public;
+grant select on sys.systables to user2, public;
+grant select on sys.systriggers to user2, public;
+grant select on sys.sysviews to user2, public;
+grant select on syscs_diag.lock_table to user2, public;
+
+revoke ALL PRIVILEGES on sys.sysaliases from user2;
+revoke ALL PRIVILEGES on sys.syschecks from user2;
+revoke ALL PRIVILEGES on sys.syscolperms from user2;
+revoke ALL PRIVILEGES on sys.syscolumns from user2;
+revoke ALL PRIVILEGES on sys.sysconglomerates from user2;
+revoke ALL PRIVILEGES on sys.sysconstraints from user2;
+revoke ALL PRIVILEGES on sys.sysdepends from user2;
+revoke ALL PRIVILEGES on sys.sysfiles from user2;
+revoke ALL PRIVILEGES on sys.sysforeignkeys from user2;
+revoke ALL PRIVILEGES on sys.syskeys from user2;
+revoke ALL PRIVILEGES on sys.sysroutineperms from user2;
+revoke ALL PRIVILEGES on sys.sysschemas from user2;
+revoke ALL PRIVILEGES on sys.sysstatistics from user2;
+revoke ALL PRIVILEGES on sys.sysstatements from user2;
+revoke ALL PRIVILEGES on sys.systableperms from user2;
+revoke ALL PRIVILEGES on sys.systables from user2;
+revoke ALL PRIVILEGES on sys.systriggers from user2;
+revoke ALL PRIVILEGES on sys.sysviews from user2;
+revoke ALL PRIVILEGES on syscs_diag.lock_table from user2;
+
+revoke select on sys.sysaliases from user2, public;
+revoke select on sys.syschecks from user2, public;
+revoke select on sys.syscolperms from user2, public;
+revoke select on sys.syscolumns from user2, public;
+revoke select on sys.sysconglomerates from user2, public;
+revoke select on sys.sysconstraints from user2, public;
+revoke select on sys.sysdepends from user2, public;
+revoke select on sys.sysfiles from user2, public;
+revoke select on sys.sysforeignkeys from user2, public;
+revoke select on sys.syskeys from user2, public;
+revoke select on sys.sysroutineperms from user2, public;
+revoke select on sys.sysschemas from user2, public;
+revoke select on sys.sysstatistics from user2, public;
+revoke select on sys.sysstatements from user2, public;
+revoke select on sys.systableperms from user2, public;
+revoke select on sys.systables from user2, public;
+revoke select on sys.systriggers from user2, public;
+revoke select on sys.sysviews from user2, public;
+revoke select on syscs_diag.lock_table from user2, public;
+
+-- -------------------------------------------------------------------
+-- built-in functions and procedures and routines
+-- -------------------------------------------------------------------
+
+set connection user3;
+-- test sqlj, only db owner have privileges by default
+-- expect errors
+CALL SQLJ.INSTALL_JAR ('bogus.jar','user2.bogus',0);
+CALL SQLJ.REPLACE_JAR ('bogus1.jar', 'user2.bogus');
+CALL SQLJ.REMOVE_JAR ('user2.bogus', 0);
+
+-- test backup routines, only db owner have privileges by default
+-- expect errors
+CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('backup1');
+CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE('backup3', 1);
+CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT('backup4', 1);
+
+-- test admin routines, only db owner have privileges by default
+CALL SYSCS_UTIL.SYSCS_FREEZE_DATABASE();
+CALL SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE();
+CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
+CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE();
+
+-- test statistical routines, available for everyone by default
+set connection user1;
+-- ok
+CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
+CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
+
+-- ok
+set connection user3;
+CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
+CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
+
+-- test import/export, only db owner have privileges by default
+create table TABLEIMP1 (i int);
+create table TABLEEXP1 (i int);
+insert into TABLEEXP1 values 1,2,3,4,5;
+CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('USER3', 'TABLEEXP1', 'myfile.del', null, null, null);
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('USER3', 'TABLEIMP1', 'myfile.del', null, null, null, 0);
+CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from user3.TABLEEXP1','myfile.del', null, null, null);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA ('USER3', 'TABLEIMP1', null, '1,3,4', 'myfile.del', null, null, null,0);
+
+-- test property handling routines, only db owner have privileges by default
+-- expect errors
+CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ('derby.locks.deadlockTimeout', '10');
+VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.locks.deadlockTimeout');
+
+-- test compress routines, everyone have privilege as long as the user owns the schema
+-- ok
+CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('USER3', 'TABLEEXP1', 1);
+call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('USER3', 'TABLEEXP1', 1, 1, 1);
+
+-- test check table routines, only db owner have privilege by default
+VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('USER3', 'TABLEEXP1');
+
+-- -------------------------------------------------------------------
+-- synonyms
+-- -------------------------------------------------------------------
+set connection user1;
+create synonym s1 for user1.t1;
+create index ii1 on user1.t1(c2);
+
+-- not supported yet, expect errors
+grant select on s1 to user2;
+grant insert on s1 to user2;
+revoke select on s1 from user2;
+revoke insert on s1 from user2;
+
+set connection user2;
+-- expect errors
+drop synonym user1.s1;
+drop index user1.ii1;
+
+-- -------------------------------------------------------------------
+-- transactions and lock table stmt
+-- -------------------------------------------------------------------
+set connection user1;
+create table t1000 (i int);
+autocommit off;
+grant select on t1000 to user2;
+set connection user2;
+select * from user1.t1000;
+set connection user1;
+commit;
+
+set connection user2;
+-- ok
+select * from user1.t1000;
+set connection user1;
+revoke select on t1000 from user2;
+set connection user2;
+select * from user1.t1000;
+set connection user1;
+commit;
+set connection user2;
+select * from user1.t1000;
+autocommit off;
+-- should fail
+lock table user1.t1000 in share mode;
+-- should fail
+lock table user1.t1000 in exclusive mode;
+commit;
+autocommit on;
+
+set connection user1;
+grant select on t1000 to user2;
+rollback;
+set connection user2;
+select * from user1.t1000;
+
+set connection user1;
+grant select on t1000 to user2;
+commit;
+revoke select on t1000 from user2;
+rollback;
+set connection user2;
+select * from user1.t1000;
+set connection user1;
+autocommit on;
+drop table t1000;
+
+set connection user1;
+create table t1000 (c varchar(1));
+insert into t1000 values 'a', 'b', 'c';
+grant select on t1000 to user3;
+set connection user2;
+create table t1001 (i int);
+insert into t1001 values 1;
+set connection user1;
+select * from user2.t1001;
+insert into user2.t1001 values 2;
+update user2.t1001 set i = 888;
+drop table user1.t1000;
+drop table user2.t1001;
+commit;
+autocommit on;
+
+-- -------------------------------------------------------------------
+-- cursors
+-- -------------------------------------------------------------------
+-- DERBY-1716
+--set connection user1;
+--drop table t1001;
+--create table t1001 (c varchar(1));
+--insert into t1001 values 'a', 'b', 'c';
+--grant select on t1001 to user3;
+--set connection user3;
+--autocommit off;
+--GET CURSOR crs1 AS 'select * from user1.t1001';
+--next crs1;
+--set connection user1;
+-- revoke select privilege while user3 still have an open cursor
+--revoke select on t1001 from user3;
+--set connection user3;
+--next crs1;
+--next crs1;
+--close crs1;
+--autocommit on;
+-- -------------------------------------------------------------------
+-- rename table
+-- -------------------------------------------------------------------
+set connection user1;
+drop table user1.rta;
+drop table user2.rtb;
+create table rta (i int);
+grant select on rta to user2;
+set connection user2;
+select * from user1.rta;
+set connection user1;
+rename table rta to rtb;
+set connection user1;
+-- expect error
+select * from user1.rta;
+-- ok
+select * from user1.rtb;
+set connection user2;
+-- expect error
+select * from user1.rta;
+-- ok
+select * from user1.rtb;
+-- -------------------------------------------------------------------
+-- DB owner power =)
+-- -------------------------------------------------------------------
+set connection user2;
+create table ttt1 (i int);
+insert into ttt1 values 1;
+set connection user3;
+create table ttt1 (i int);
+insert into ttt1 values 10;
+set connection user1;
+-- the following actions are ok
+select * from user2.ttt1;
+insert into user2.ttt1 values 2;
+update user2.ttt1 set i = 888;
+delete from user2.ttt1;
+drop table user2.ttt1;
+select * from user3.ttt1;
+insert into user3.ttt1 values 20;
+update user3.ttt1 set i = 999;
+delete from user3.ttt1;
+drop table user3.ttt1;
+
+set connection user4;
+create table ttt1 (i int);
+set connection user1;
+drop table user4.ttt1;
+
+-- set connection user2;
+-- DERBY-1858
+-- expect error
+-- drop schema user4 restrict;
+
+set connection user1;
+-- ok
+drop schema user4 restrict;
+
+-- -------------------------------------------------------------------
+-- Statement preparation
+-- -------------------------------------------------------------------
+set connection user1;
+create table ttt2 (i int);
+insert into ttt2 values 8;
+
+set connection user2;
+-- prepare statement, ok
+prepare p1 as 'select * from user1.ttt2';
+-- expect error
+execute p1;
+remove p1;
+
+set connection user1;
+grant select on ttt2 to user2;
+set connection user2;
+-- prepare statement, ok
+prepare p1 as 'select * from user1.ttt2';
+-- ok
+execute p1;
+set connection user1;
+revoke select on ttt2 from user2;
+set connection user2;
+-- expect error
+execute p1;
+remove p1;
+
+-- -------------------------------------------------------------------
+-- Misc
+-- -------------------------------------------------------------------
+set connection user2;
+create table tshared0 (i int);
+-- db owner tries to revoke select access from user2
+set connection user1;
+-- expect error
+revoke select on user2.tshared0 from user2;
+set connection user2;
+select * from user2.tshared0;
+
+set connection user2;
+create table tshared1 (i int);
+grant select, insert, delete, update on tshared1 to user3, user4, user5;
+set connection user3;
+create table tshared1 (i int);
+grant select, insert, delete, update on tshared1 to user2, user4, user5;
+set connection user2;
+insert into user3.tshared1 values 1,2,3;
+update user3.tshared1 set i = 888;
+select * from user3.tshared1;
+delete from user3.tshared1;
+insert into user3.tshared1 values 1,2,3;
+set connection user3;
+insert into user2.tshared1 values 3,2,1;
+update user2.tshared1 set i = 999;
+select * from user2.tshared1;
+delete from user2.tshared1;
+insert into user2.tshared1 values 3,2,1;
+set connection user1;
+update user2.tshared1 set i = 1000;
+update user3.tshared1 set i = 1001;
+delete from user2.tshared1;
+delete from user3.tshared1;
+insert into user2.tshared1 values 0,1,2,3;
+insert into user3.tshared1 values 4,3,2,1;
+set connection user4;
+select * from user2.tshared1;
+select * from user3.tshared1;
+create view vshared1 as select * from user2.tshared1 union select * from user3.tshared1;
+create view vshared2 as select * from user2.tshared1 intersect select * from user3.tshared1;
+create view vshared3 as select * from user2.tshared1 except select * from user3.tshared1;
+create view vshared4(i) as select * from user3.tshared1 union values 0;
+insert into user2.tshared1 select * from user3.tshared1;
+select * from vshared1;
+select * from vshared2;
+select * from vshared3;
+select * from vshared4;
+-- expect errors
+grant select on vshared1 to user5;
+grant select on vshared2 to user5;
+grant select on vshared3 to user5;
+grant select on vshared4 to user5;
+set connection user5;
+select * from user4.vshared1;
+select * from user4.vshared2;
+select * from user4.vshared3;
+select * from user4.vshared4;
+set connection user1;
+
+-- -------------------------------------------------------------------
+-- triggers
+-- -------------------------------------------------------------------
+set connection user1;
+
+-- expect error
+create trigger tt0a after insert on t1 for each statement mode db2sql grant select on t1 to user2;
+-- expect error
+create trigger tt0b after insert on t1 for each statement mode db2sql revoke select on t1 from user2;
+
+-- same schema in trigger action
+drop table t6;
+create table t6 (c1 int not null primary key, c2 int);
+grant trigger on t6 to user2;
+set connection user2;
+drop table t7;
+create table t7 (c1 int, c2 int, c3 int);
+insert into t7 values (1,1,1);
+create trigger tt1 after insert on user1.t6 for each statement mode db2sql update user2.t7 set c2 = 888;
+create trigger tt2 after insert on user1.t6 for each statement mode db2sql insert into user2.t7 values (2,2,2);
+
+set connection user1;
+insert into t6 values (1, 10);
+select * from user2.t7;
+
+-- different schema in trigger action
+-- this testcase is causing NPE - DERBY-1583
+set connection user1;
+drop table t8;
+drop table t9;
+create table t8 (c1 int not null primary key, c2 int);
+create table t9 (c1 int, c2 int, c3 int);
+insert into user1.t8 values (1,1);
+insert into user1.t9 values (10,10,10);
+grant trigger on t8 to user2;
+grant update(c2, c1), insert on t9 to user2;
+set connection user2;
+create trigger tt3 after insert on user1.t8 for each statement mode db2sql update user1.t9 set c2 = 888;
+create trigger tt4 after insert on user1.t8 for each statement mode db2sql insert into user1.t9 values (2,2,2);
+set connection user1;
+-- expect error
+insert into user1.t8 values (1, 10);
+-- ok
+insert into user1.t8 values (2, 20);
+select * from user1.t9;
+
+-- grant all privileges then create trigger, then revoke the trigger privilege
+drop table t10;
+drop table t11;
+create table t10 (i int, j int);
+insert into t10 values (1,1), (2,2);
+create table t11 (i int);
+grant all privileges on t10 to user2;
+grant all privileges on t11 to user2;
+set connection user2;
+-- ok
+create trigger tt5 after update on user1.t10 for each statement mode db2sql insert into user1.t11 values 1;
+create trigger tt6 after update of i on user1.t10 for each statement mode db2sql insert into user1.t11 values 2;
+create trigger tt7 after update of j on user1.t10 for each statement mode db2sql insert into user1.t11 values 3;
+
+update user1.t10 set i=10;
+select * from user1.t10;
+select * from user1.t11;
+
+set connection user1;
+-- triggers get dropped
+revoke trigger on t10 from user2;
+set connection user2;
+
+update user1.t10 set i=20;
+select * from user1.t10;
+select * from user1.t11;
+
+set connection user1;
+grant trigger on t10 to user2;
+
+set connection user2;
+create trigger tt8 after update of j on user1.t10 for each statement mode db2sql delete from user1.t11;
+
+update user1.t10 set j=100;
+select * from user1.t10;
+select * from user1.t11;
+delete from user1.t10;
+delete from user1.t11;
+
+-- test trigger, view and function combo
+set connection user1;
+drop function F_ABS1;
+CREATE FUNCTION F_ABS1(P1 INT)
+ RETURNS INT NO SQL
+ RETURNS NULL ON NULL INPUT
+ EXTERNAL NAME 'java.lang.Math.abs'
+ LANGUAGE JAVA PARAMETER STYLE JAVA;
+
+grant execute on function F_ABS1 to user5;
+grant trigger,insert,update,delete,select on t10 to user5;
+grant trigger,insert,update,delete,select on t11 to user5;
+drop view v;
+create view v(i) as values 888;
+grant select on v to user5;
+
+set connection user5;
+create trigger tt9 after insert on user1.t10 for each statement mode db2sql insert into user1.t11 values (user1.F_ABS1(-5));
+create trigger tt10 after insert on user1.t10 for each statement mode db2sql insert into user1.t11 select * from user1.v;
+
+insert into user1.t10 values (1,1);
+select * from user1.t10;
+select * from user1.t11;
+
+-- Related to DERBY-1631
+-- cannot revoke execution on F_ABS1 due to X0Y25 (object dependencies)
+--set connection user1;
+--revoke execute on function F_ABS1 from user5 restrict;
+
+--set connection user5;
+--insert into user1.t10 values (2,2);
+--select * from user1.t10;
+--select * from user1.t11;
+
+--set connection user1;
+--revoke select on v from user5;
+
+--set connection user5;
+--insert into user1.t10 values (3,3);
+--select * from user1.t10;
+--select * from user1.t11;
+--set connection user1;
+--drop view v;
set connection user1;
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2_app.properties
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2_app.properties?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2_app.properties (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2_app.properties Fri Sep 22 08:15:58 2006
@@ -4,4 +4,8 @@
derby.database.sqlAuthorization=true
useextdirs=true
+# we want wait timeouts to be quick
+derby.locks.deadlockTimeout=5
+derby.locks.waitTimeout=2
+
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql Fri Sep 22 08:15:58 2006
@@ -107,6 +107,10 @@
for update of c2, c1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+select * from t1 --derby-properties constraint = null
+;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
-- constraint which includes columns in for update of list
select * from t1 --derby-properties constraint = cons1
for update;
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatePushdown.sql Fri Sep 22 08:15:58 2006
@@ -107,6 +107,12 @@
(select c1, c, c2, 28 from tc) xx1
union select 'i','j','j',i from t2;
+create view vz4 (z1, z2, z3, z4) as
+ select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from
+ (select c1, c, c2, 28 from tc) xx1
+ union select 'i','j','j',i from t2
+ union select c1, c2, c3, c from tc;
+
-- Both sides of predicate reference aggregates.
select x1.c1 from
(select count(*) from t1 union select count(*) from t2) x1 (c1),
@@ -266,6 +272,16 @@
(select distinct j from t2 union select j from t1) x2 (c2)
where x1.z4 = x2.c2;
+-- Same as previous query but with a different nested
+-- view (vz4) that has double-nested unions in it.
+-- This is a test case for DERBY-1777.
+select x1.z4, x2.c2 from
+ (select z1, z4, z3 from vz4
+ union select '1', i+1, '3' from t1
+ ) x1 (z1, z4, z3),
+ (select distinct j from t2 union select j from t1) x2 (c2)
+where x1.z4 = x2.c2;
+
-- Queries with Select->Union->Select chains having differently-
-- ordered result column lists with some non-column reference
-- expressions. In all of these queries we specify LEFT join
@@ -379,6 +395,7 @@
drop view vz;
drop view vz2;
drop view vz3;
+drop view vz4;
drop table tc;
-- Now bump up the size of tables T3 and T4 to the point where
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql?view=diff&rev=448961&r1=448960&r2=448961
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql Fri Sep 22 08:15:58 2006
@@ -258,3 +258,141 @@
drop view view1;
drop table table1;
+-- DERBY-1784
+create schema test1;
+create schema test2;
+create table test1.t1 ( id bigint not null );
+insert into test1.t1 values 1;
+create synonym test2.t1 for test1.t1;
+set schema test1;
+select t1.id from t1;
+set schema test2;
+select id from t1;
+select id from test2.t1;
+select t1.id from t1;
+select t1.id from test2.t1;
+select test2.t1.id from t1;
+select test2.t1.id from test2.t1;
+drop synonym t1;
+drop table test1.t1;
+
+set schema app;
+create table A (id integer);
+insert into A values 29;
+create synonym B for A;
+select a.id from a;
+select b.id from b;
+select b.id from b as b;
+select b.id from (select b.id from b) as b;
+select b.id from (select b.id from b as b) as b;
+drop synonym B;
+drop table A;
+
+create table t1 (i int, j int);
+create view v1 as select * from t1;
+insert into t1 values (1, 10);
+create synonym s1 for t1;
+create synonym sv1 for v1;
+-- should fail
+select t1.i from s1;
+select v1.i from sv1;
+select sv1.i from sv1 as w1;
+select s1.j from s1 where s1.k = 1;
+select s1.j from s1 where w1.i = 1;
+select * from s1 where w1.i = 1;
+select s1.j from s1 as w1 where w1.i = 1;
+select w1.j from s1 as w1 where s1.i = 1;
+select s1.j from s1 where t1.i = 1;
+select s1.j from s1 group by t1.j;
+select s1.j from s1 group by s1.j having t1.j > 0;
+insert into s1 (t1.i) values 100;
+update s1 set t1.i=1;
+delete from s1 where t1.i=100;
+
+-- ok
+select s1.i from s1;
+select s1.i from s1 as s1;
+select s1.i from s1 where i = 1;
+select s1.i from s1 where s1.i = 1;
+select s1.i from s1 as s1 where i = 1;
+select w1.i from s1 as w1 where w1.i = 1;
+select sv1.i from sv1;
+select sv1.i from sv1 as sv1;
+select sv1.i from sv1 where i = 1;
+select sv1.i from sv1 where sv1.i = 1;
+select sv1.i from sv1 as sv1 where i = 1;
+select wv1.i from sv1 as wv1 where wv1.i = 1;
+
+select s1.i, s1.i from s1;
+select sv1.i, sv1.i from sv1;
+select * from s1;
+select * from s1 where i = 1;
+select * from s1 where s1.i = 1;
+select * from s1 as s1;
+select * from s1 as w1;
+select * from sv1;
+select * from sv1 as sv1;
+select * from sv1 as w1;
+select * from sv1 where i = 1;
+select * from sv1 where sv1.i = 1;
+select s1.i from (select s1.i from s1) as s1;
+select sv1.i from (select sv1.i from sv1) as sv1;
+
+create table t2 (i int, j int);
+insert into t2 values (1, 100), (1, 100), (2, 200);
+create view v2 as select * from t2;
+create synonym s2 for t2;
+create synonym sv2 for v2;
+select s2.j from s2 group by s2.j order by s2.j;
+select s2.j from s2 group by s2.j having s2.j > 100 order by s2.j;
+select s1.i, s1.j from (select s1.i, s2.j from s1,s2 where s1.i=s2.i) as s1;
+select sv2.j from sv2 group by sv2.j order by sv2.j;
+select sv2.j from sv2 group by sv2.j having sv2.j > 100 order by sv2.j;
+select sv1.i, sv1.j from (select sv1.i, sv2.j from sv1,sv2 where sv1.i=sv2.i) as sv1;
+select max(s2.i) from s2;
+select max(sv2.i) from sv2;
+select * from s1 inner join s2 on (s1.i = s2.i);
+select * from sv1 inner join sv2 on (sv1.i = sv2.i);
+select s1.* from s1;
+select sv1.* from sv1;
+
+create table t3 (i int, j int);
+insert into t3 values (10, 0), (11, 0), (12, 0);
+create synonym s3 for t3;
+insert into s1 (s1.i, s1.j) values (2, 20);
+insert into app.s1 (s1.i, s1.j) values (3, 30);
+insert into app.s1 (app.s1.i, s1.j) values (4, 40);
+insert into app.s1 (app.s1.i, app.s1.j) values (5, 50);
+update s1 set s1.j = 1;
+update app.s1 set s1.j = 2;
+update app.s1 set app.s1.j = 3;
+update s1 set s1.j = 4 where s1.i = 3;
+update app.s1 set app.s1.j = 5 where app.s1.i = 4;
+delete from s1 where s1.i = 4;
+delete from app.s1 where app.s1.i = 5;
+update app.s1 set s1.j = s1.i, s1.i = s1.j;
+select * from s1;
+update app.s1 set s1.j = s1.i, s1.i = s1.j;
+select * from s1;
+delete from s1;
+
+-- should fail
+insert into s1 (s1.i) select s1.i from s3;
+
+-- ok
+insert into s1 (s1.i) select s3.i from s3;
+insert into s1 select * from s3;
+select * from s1;
+
+-- clean up
+drop synonym s3;
+drop synonym sv2;
+drop synonym s2;
+drop synonym s1;
+drop synonym sv1;
+drop view v2;
+drop view v1;
+drop table t3;
+drop table t2;
+drop table t1;
+