ij> -- -- 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. -- -- -- this test shows the dependency system in action; -- autocommit off; ij> create table t(i int); 0 rows inserted/updated/deleted ij> create table s(i int); 0 rows inserted/updated/deleted ij> prepare ins as 'insert into t (i) values (1956)'; ij> prepare ins_s as 'insert into s (i) values (1956)'; ij> prepare sel as 'select i from t'; ij> prepare sel2 as 'select i from (select i from t) a'; ij> prepare sel_s as 'select i from s where i = (select i from t)'; ij> prepare upd as 'update t set i = 666 where i = 1956'; ij> prepare del as 'delete from t where i = 666'; ij> prepare ins_sel as 'insert into t select * from s'; ij> execute ins; 1 row inserted/updated/deleted ij> execute ins_s; 1 row inserted/updated/deleted ij> execute sel; I ----------- 1956 ij> execute sel2; I ----------- 1956 ij> execute sel_s; I ----------- 1956 ij> execute upd; 1 row inserted/updated/deleted ij> execute sel; I ----------- 666 ij> execute del; 1 row inserted/updated/deleted ij> execute sel; I ----------- ij> execute ins_sel; 1 row inserted/updated/deleted ij> execute sel; I ----------- 1956 ij> drop table t; 0 rows inserted/updated/deleted ij> -- these should fail, can't find table execute ins; ERROR 42X05: Table/View 'T' does not exist. ij> execute sel; ERROR 42X05: Table/View 'T' does not exist. ij> execute sel2; ERROR 42X05: Table/View 'T' does not exist. ij> execute upd; ERROR 42X05: Table/View 'T' does not exist. ij> execute del; ERROR 42X05: Table/View 'T' does not exist. ij> execute sel_s; ERROR 42X05: Table/View 'T' does not exist. ij> execute ins_sel; ERROR 42X05: Table/View 'T' does not exist. ij> create table t(i int); 0 rows inserted/updated/deleted ij> -- these should recompile and work, table now found execute ins; 1 row inserted/updated/deleted ij> -- expect one row only execute sel; I ----------- 1956 ij> execute sel2; I ----------- 1956 ij> execute sel_s; I ----------- 1956 ij> execute upd; 1 row inserted/updated/deleted ij> -- test update execute sel; I ----------- 666 ij> execute del; 1 row inserted/updated/deleted ij> -- test delete execute sel; I ----------- ij> execute ins_sel; 1 row inserted/updated/deleted ij> execute sel; I ----------- 1956 ij> rollback; ij> -- these should fail, the table will disappear at the rollback execute ins; ERROR 42X05: Table/View 'T' does not exist. ij> execute sel; ERROR 42X05: Table/View 'T' does not exist. ij> execute sel2; ERROR 42X05: Table/View 'T' does not exist. ij> execute sel_s; ERROR 42X05: Table/View 'S' does not exist. ij> execute upd; ERROR 42X05: Table/View 'T' does not exist. ij> execute del; ERROR 42X05: Table/View 'T' does not exist. ij> -- recreate t again create table t(i int); 0 rows inserted/updated/deleted ij> -- these should recompile and work, table now found execute ins; 1 row inserted/updated/deleted ij> -- open a cursor on t get cursor c1 as 'select * from t'; ij> -- dropping t should fail, due to open cursor drop table t; ERROR X0X95: Operation 'DROP TABLE' cannot be performed on object 'T' because there is an open ResultSet dependent on that object. ij> -- insert should still succeed, since table not dropped execute ins; 1 row inserted/updated/deleted ij> -- close cursor close c1; ij> -- drop table should succeed drop table t; 0 rows inserted/updated/deleted ij> -- verify that invalidate worked this time execute ins; ERROR 42X05: Table/View 'T' does not exist. ij> execute sel; ERROR 42X05: Table/View 'T' does not exist. ij> execute sel2; ERROR 42X05: Table/View 'T' does not exist. ij> execute upd; ERROR 42X05: Table/View 'T' does not exist. ij> execute del; ERROR 42X05: Table/View 'T' does not exist. ij> execute ins_sel; ERROR 42X05: Table/View 'S' does not exist. ij> -- cleanup, roll everything back to the beginning rollback; ij> -- verify that cascading invalidations work create table t1(c1 int); 0 rows inserted/updated/deleted ij> insert into t1 values 1, 2; 2 rows inserted/updated/deleted ij> get cursor c1 as 'select c1 from t1 for update of c1'; ij> -- positioned update dependent on cursor c1 prepare u1 as 'update t1 set c1 = c1 + 1 where current of c1'; ij> next c1; C1 ----------- 1 ij> close c1; ij> execute u1; ERROR 42X30: Cursor 'C1' not found. Verify that autocommit is OFF. ij> -- cleanup, roll everything back to the beginning rollback; ij> -- verify that create index invalidates based on table and -- drop index invalidates based on the index create table t1(c1 int, c2 int); 0 rows inserted/updated/deleted ij> insert into t1 values (1,1), (2, 1), (3,3); 3 rows inserted/updated/deleted ij> create index i1 on t1(c1); 0 rows inserted/updated/deleted ij> get cursor c1 as 'select c1 from t1 where c2 = 1 for update of c1'; ij> next c1; C1 ----------- 1 ij> prepare u1 as 'update t1 set c1 = c1 + 1 '; ij> prepare i1 as 'insert into t1 values (4, 4)'; ij> prepare d1 as 'delete from t1 where c2 = 3'; ij> drop index i1; 0 rows inserted/updated/deleted ij> -- u1 should be recompiled succesfully execute u1; 3 rows inserted/updated/deleted ij> select * from t1; C1 |C2 ----------------------- 2 |1 3 |1 4 |3 ij> -- recreate index i1, this time on c2 create index i1 on t1(c2); 0 rows inserted/updated/deleted ij> next c1; C1 ----------- 3 ij> close c1; ij> -- i1 and d1 should have been invalidated and recompiled execute i1; 1 row inserted/updated/deleted ij> -- check the state of the index select * from t1 where c2 > 0; C1 |C2 ----------------------- 2 |1 3 |1 4 |3 4 |4 ij> execute d1; 1 row inserted/updated/deleted ij> -- check the state of the index select * from t1 where c2 > 0; C1 |C2 ----------------------- 2 |1 3 |1 4 |4 ij> -- cleanup, roll everything back to the beginning rollback; ij> -- DERBY-2202 -- test various DROP statements -- test procedure autocommit off; ij> CREATE SCHEMA datamgmt; 0 rows inserted/updated/deleted ij> CREATE PROCEDURE datamgmt.exit ( IN value INTEGER ) MODIFIES SQL DATA PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'java.lang.System.exit'; 0 rows inserted/updated/deleted ij> DROP PROCEDURE datamgmt.exit; 0 rows inserted/updated/deleted ij> DROP SCHEMA datamgmt RESTRICT; 0 rows inserted/updated/deleted ij> CREATE SCHEMA datamgmt; 0 rows inserted/updated/deleted ij> CREATE PROCEDURE datamgmt.exit ( IN value INTEGER ) MODIFIES SQL DATA PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'java.lang.System.exit'; 0 rows inserted/updated/deleted ij> DROP PROCEDURE datamgmt.exit; 0 rows inserted/updated/deleted ij> DROP SCHEMA datamgmt RESTRICT; 0 rows inserted/updated/deleted ij> autocommit on; ij> CREATE SCHEMA datamgmt; 0 rows inserted/updated/deleted ij> CREATE PROCEDURE datamgmt.exit ( IN value INTEGER ) MODIFIES SQL DATA PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'java.lang.System.exit'; 0 rows inserted/updated/deleted ij> DROP PROCEDURE datamgmt.exit; 0 rows inserted/updated/deleted ij> DROP SCHEMA datamgmt RESTRICT; 0 rows inserted/updated/deleted ij> CREATE SCHEMA datamgmt; 0 rows inserted/updated/deleted ij> CREATE PROCEDURE datamgmt.exit ( IN value INTEGER ) MODIFIES SQL DATA PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'java.lang.System.exit'; 0 rows inserted/updated/deleted ij> DROP PROCEDURE datamgmt.exit; 0 rows inserted/updated/deleted ij> DROP SCHEMA datamgmt RESTRICT; 0 rows inserted/updated/deleted ij> -- test function CREATE SCHEMA datamgmt; 0 rows inserted/updated/deleted ij> CREATE FUNCTION datamgmt.f_abs(P1 INT) RETURNS INT NO SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> DROP FUNCTION datamgmt.f_abs; 0 rows inserted/updated/deleted ij> DROP SCHEMA datamgmt RESTRICT; 0 rows inserted/updated/deleted ij> CREATE SCHEMA datamgmt; 0 rows inserted/updated/deleted ij> CREATE FUNCTION datamgmt.f_abs(P1 INT) RETURNS INT NO SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> DROP FUNCTION datamgmt.f_abs; 0 rows inserted/updated/deleted ij> DROP SCHEMA datamgmt RESTRICT; 0 rows inserted/updated/deleted ij> -- test synonym CREATE SCHEMA datamgmt; 0 rows inserted/updated/deleted ij> CREATE TABLE datamgmt.t1 (c1 int); 0 rows inserted/updated/deleted ij> CREATE SYNONYM datamgmt.s1 for datamgmt.t1; 0 rows inserted/updated/deleted ij> DROP SYNONYM datamgmt.s1; 0 rows inserted/updated/deleted ij> DROP TABLE datamgmt.t1; 0 rows inserted/updated/deleted ij> DROP SCHEMA datamgmt RESTRICT; 0 rows inserted/updated/deleted ij> CREATE SCHEMA datamgmt; 0 rows inserted/updated/deleted ij> CREATE TABLE datamgmt.t1 (c1 int); 0 rows inserted/updated/deleted ij> CREATE SYNONYM datamgmt.s1 for datamgmt.t1; 0 rows inserted/updated/deleted ij> DROP SYNONYM datamgmt.s1; 0 rows inserted/updated/deleted ij> DROP TABLE datamgmt.t1; 0 rows inserted/updated/deleted ij> DROP SCHEMA datamgmt RESTRICT; 0 rows inserted/updated/deleted ij>