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 ba...@apache.org on 2005/06/09 07:53:19 UTC
svn commit: r189716 - in /incubator/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/depend/
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/compile/
engine/org/apache/derby/impl/sql/depend/
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/DerbyNet/
testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bandaram
Date: Wed Jun 8 22:53:18 2005
New Revision: 189716
URL: http://svn.apache.org/viewcvs?rev=189716&view=rev
Log:
Add dependency checks for synonyms. DML statements depend on all synonyms referenced in the chain.
Submitted by Satheesh Bandaram (satheesh@sourcery.org)
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/synonym.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/synonym.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java Wed Jun 8 22:53:18 2005
@@ -309,6 +309,7 @@
public static final int RENAME_INDEX = 41;
public static final int TRUNCATE_TABLE = 42;
+ public static final int DROP_SYNONYM = 43;
/**
* Extensions to this interface may use action codes > MAX_ACTION_CODE without fear of
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java Wed Jun 8 22:53:18 2005
@@ -872,6 +872,7 @@
case DependencyManager.DROP_TABLE:
case DependencyManager.DROP_VIEW:
case DependencyManager.DROP_METHOD_ALIAS:
+ case DependencyManager.DROP_SYNONYM:
case DependencyManager.ALTER_TABLE:
case DependencyManager.RENAME:
case DependencyManager.RENAME_INDEX:
@@ -949,6 +950,7 @@
case DependencyManager.DROP_INDEX:
case DependencyManager.DROP_VIEW:
case DependencyManager.DROP_METHOD_ALIAS:
+ case DependencyManager.DROP_SYNONYM:
case DependencyManager.ALTER_TABLE:
case DependencyManager.RENAME:
case DependencyManager.RENAME_INDEX:
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java Wed Jun 8 22:53:18 2005
@@ -644,6 +644,7 @@
** dropped before our table is dropped).
*/
case DependencyManager.DROP_TABLE:
+ case DependencyManager.DROP_SYNONYM:
case DependencyManager.DROP_SPS:
case DependencyManager.RENAME:
DependencyManager dm = getDataDictionary().getDependencyManager();
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Wed Jun 8 22:53:18 2005
@@ -1489,6 +1489,7 @@
String nextSynonymTable = tabName.getTableName();
String nextSynonymSchema = tabName.getSchemaName();
boolean found = false;
+ CompilerContext cc = getCompilerContext();
// Circular synonym references should have been detected at the DDL time, so
// the following loop shouldn't loop forever.
@@ -1502,6 +1503,9 @@
nextSynonymTable, AliasInfo.ALIAS_NAME_SPACE_SYNONYM_AS_CHAR);
if (nextAD == null)
break;
+
+ /* Query is dependent on the AliasDescriptor */
+ cc.createDependency(nextAD);
found = true;
SynonymAliasInfo info = ((SynonymAliasInfo)nextAD.getAliasInfo());
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java Wed Jun 8 22:53:18 2005
@@ -845,6 +845,9 @@
case TRUNCATE_TABLE:
return "TRUNCATE TABLE";
+ case DROP_SYNONYM:
+ return "DROP SYNONYM";
+
default:
if (SanityManager.DEBUG)
{
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropAliasConstantAction.java Wed Jun 8 22:53:18 2005
@@ -149,12 +149,17 @@
case AliasInfo.ALIAS_TYPE_FUNCTION_AS_CHAR:
invalidationType = DependencyManager.DROP_METHOD_ALIAS;
break;
+
+ case AliasInfo.ALIAS_TYPE_SYNONYM_AS_CHAR:
+ invalidationType = DependencyManager.DROP_SYNONYM;
+ break;
}
dm.invalidateFor(ad, invalidationType, lcc);
if (ad.getAliasType() == AliasInfo.ALIAS_TYPE_SYNONYM_AS_CHAR)
{
+ // Drop the entry from SYSTABLES as well.
DataDescriptorGenerator ddg = dd.getDataDescriptorGenerator();
TableDescriptor td = ddg.newTableDescriptor(aliasName, sd,
TableDescriptor.SYNONYM_TYPE, TableDescriptor.DEFAULT_LOCK_GRANULARITY);
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/synonym.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/synonym.out?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/synonym.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/synonym.out Wed Jun 8 22:53:18 2005
@@ -200,10 +200,156 @@
5 |5
ij> drop table table2;
0 rows inserted/updated/deleted
-ij> -- TODO: Add more tests here
------ drop and recreate schema test
------ More negative tests once dependency checking is added
-drop view view1;
+ij> -- Try referential constraints. Synonyms should not be allowed there.
+create table primaryTab (i int not null primary key, j int, c char(10));
+0 rows inserted/updated/deleted
+ij> create synonym synPrimary for primaryTab;
+0 rows inserted/updated/deleted
+ij> -- Should fail
+create table foreignTab(i int, j int references synPrimary(i));
+ERROR X0Y46: Constraint 'xxxxGENERATED-IDxxxx' is invalid: referenced table SYNPRIMARY does not exist.
+ij> create table foreignTab(i int, j int references primaryTab(i));
+0 rows inserted/updated/deleted
+ij> drop table foreignTab;
+0 rows inserted/updated/deleted
+ij> drop table primaryTab;
+0 rows inserted/updated/deleted
+ij> drop synonym synPrimary;
+0 rows inserted/updated/deleted
+ij> -- Positive test case with three levels of synonym chaining
+create schema synonymSchema;
+0 rows inserted/updated/deleted
+ij> create synonym synonymSchema.mySynonym1 for APP.table1;
+0 rows inserted/updated/deleted
+ij> create synonym APP.mySynonym2 for "SYNONYMSCHEMA"."MYSYNONYM1";
+0 rows inserted/updated/deleted
+ij> create synonym mySynonym for mySynonym2;
+0 rows inserted/updated/deleted
+ij> select * from table1;
+I |J
+-----
+6 |1
+2 |2
+5 |5
+ij> select * from mySynonym;
+I |J
+-----
+6 |1
+2 |2
+5 |5
+ij> insert into mySynonym values (6,6);
+1 row inserted/updated/deleted
+ij> insert into mySynonym select * from mySynonym where i<2;
+0 rows inserted/updated/deleted
+ij> select * from mySynonym;
+I |J
+-----
+6 |1
+2 |2
+5 |5
+6 |6
+ij> update mySynonym set j=5;
+4 rows inserted/updated/deleted
+ij> update mySynonym set j=4 where i=5;
+1 row inserted/updated/deleted
+ij> delete from mySynonym where j=6;
+0 rows inserted/updated/deleted
+ij> select * from mySynonym;
+I |J
+-----
+6 |5
+2 |5
+5 |4
+6 |5
+ij> select * from table1;
+I |J
+-----
+6 |5
+2 |5
+5 |4
+6 |5
+ij> -- cursor on mySynonym
+get cursor c1 as 'select * from mySynonym';
+ij> next c1;
+I |J
+-----
+6 |5
+ij> next c1;
+I |J
+-----
+2 |5
+ij> next c1;
+I |J
+-----
+5 |4
+ij> close c1;
+ij> -- More negative tests to check dependencies
+select * from mySynonym;
+I |J
+-----
+6 |5
+2 |5
+5 |4
+6 |5
+ij> drop synonym mySynonym;
+0 rows inserted/updated/deleted
+ij> -- Previously compiled cached statement should get invalidated
+select * from mySynonym;
+ERROR 42X05: Table 'MYSYNONYM' does not exist.
+ij> create synonym mySyn for table1;
+0 rows inserted/updated/deleted
+ij> create view v1 as select * from mySyn;
+0 rows inserted/updated/deleted
+ij> create view v2 as select * from v1;
+0 rows inserted/updated/deleted
+ij> -- Drop synonym should fail since it is used in two views.
+drop synonym mySyn;
+ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V1' is dependent on that object. SQLSTATE: X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V2' is dependent on that object.
+ij> drop view v2;
+0 rows inserted/updated/deleted
+ij> -- fail still
+drop synonym mySyn;
+ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V1' is dependent on that object.
+ij> drop view v1;
+0 rows inserted/updated/deleted
+ij> -- should pass
+drop synonym mySyn;
+0 rows inserted/updated/deleted
+ij> -- drop and recreate schema test
+create schema testSchema;
+0 rows inserted/updated/deleted
+ij> create synonym multiSchema for testSchema.testtab;
+0 rows inserted/updated/deleted
+WARNING 01522: The newly defined synonym 'MULTISCHEMA' resolved to the object 'TESTSCHEMA.TESTTAB' which is currently undefined. :
+ij> select * from multiSchema;
+ERROR 42X05: Table 'TESTSCHEMA.TESTTAB' does not exist.
+ij> create table testSchema.testtab(i int, c char(10));
+0 rows inserted/updated/deleted
+ij> insert into testSchema.testtab values (1, 'synonym');
+1 row inserted/updated/deleted
+ij> select * from multiSchema;
+I |C
+-----
+1 |synonym
+ij> drop table testSchema.testtab;
+0 rows inserted/updated/deleted
+ij> drop schema testSchema restrict;
+0 rows inserted/updated/deleted
+ij> create schema testSchema;
+0 rows inserted/updated/deleted
+ij> create table testSchema.testtab(j int, c1 char(10), c2 char(20));
+0 rows inserted/updated/deleted
+ij> insert into testSchema.testtab values (1, 'synonym', 'test');
+1 row inserted/updated/deleted
+ij> select * from multiSchema;
+J |C1 |C2
+-----
+1 |synonym |test
+ij> drop synonym multiSchema;
+0 rows inserted/updated/deleted
+ij> drop table testSchema.testtab;
+0 rows inserted/updated/deleted
+ij> drop view view1;
0 rows inserted/updated/deleted
ij> drop table table1;
0 rows inserted/updated/deleted
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/synonym.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/synonym.out?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/synonym.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/synonym.out Wed Jun 8 22:53:18 2005
@@ -200,10 +200,156 @@
5 |5
ij> drop table table2;
0 rows inserted/updated/deleted
-ij> -- TODO: Add more tests here
------ drop and recreate schema test
------ More negative tests once dependency checking is added
-drop view view1;
+ij> -- Try referential constraints. Synonyms should not be allowed there.
+create table primaryTab (i int not null primary key, j int, c char(10));
+0 rows inserted/updated/deleted
+ij> create synonym synPrimary for primaryTab;
+0 rows inserted/updated/deleted
+ij> -- Should fail
+create table foreignTab(i int, j int references synPrimary(i));
+ERROR X0Y46: Constraint 'xxxxGENERATED-IDxxxx' is invalid: referenced table SYNPRIMARY does not exist.
+ij> create table foreignTab(i int, j int references primaryTab(i));
+0 rows inserted/updated/deleted
+ij> drop table foreignTab;
+0 rows inserted/updated/deleted
+ij> drop table primaryTab;
+0 rows inserted/updated/deleted
+ij> drop synonym synPrimary;
+0 rows inserted/updated/deleted
+ij> -- Positive test case with three levels of synonym chaining
+create schema synonymSchema;
+0 rows inserted/updated/deleted
+ij> create synonym synonymSchema.mySynonym1 for APP.table1;
+0 rows inserted/updated/deleted
+ij> create synonym APP.mySynonym2 for "SYNONYMSCHEMA"."MYSYNONYM1";
+0 rows inserted/updated/deleted
+ij> create synonym mySynonym for mySynonym2;
+0 rows inserted/updated/deleted
+ij> select * from table1;
+I |J
+-----
+6 |1
+2 |2
+5 |5
+ij> select * from mySynonym;
+I |J
+-----
+6 |1
+2 |2
+5 |5
+ij> insert into mySynonym values (6,6);
+1 row inserted/updated/deleted
+ij> insert into mySynonym select * from mySynonym where i<2;
+0 rows inserted/updated/deleted
+ij> select * from mySynonym;
+I |J
+-----
+6 |1
+2 |2
+5 |5
+6 |6
+ij> update mySynonym set j=5;
+4 rows inserted/updated/deleted
+ij> update mySynonym set j=4 where i=5;
+1 row inserted/updated/deleted
+ij> delete from mySynonym where j=6;
+0 rows inserted/updated/deleted
+ij> select * from mySynonym;
+I |J
+-----
+6 |5
+2 |5
+5 |4
+6 |5
+ij> select * from table1;
+I |J
+-----
+6 |5
+2 |5
+5 |4
+6 |5
+ij> -- cursor on mySynonym
+get cursor c1 as 'select * from mySynonym';
+ij> next c1;
+I |J
+-----
+6 |5
+ij> next c1;
+I |J
+-----
+2 |5
+ij> next c1;
+I |J
+-----
+5 |4
+ij> close c1;
+ij> -- More negative tests to check dependencies
+select * from mySynonym;
+I |J
+-----
+6 |5
+2 |5
+5 |4
+6 |5
+ij> drop synonym mySynonym;
+0 rows inserted/updated/deleted
+ij> -- Previously compiled cached statement should get invalidated
+select * from mySynonym;
+ERROR 42X05: Table 'MYSYNONYM' does not exist.
+ij> create synonym mySyn for table1;
+0 rows inserted/updated/deleted
+ij> create view v1 as select * from mySyn;
+0 rows inserted/updated/deleted
+ij> create view v2 as select * from v1;
+0 rows inserted/updated/deleted
+ij> -- Drop synonym should fail since it is used in two views.
+drop synonym mySyn;
+ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V1' is dependent on that object. SQLSTATE: X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V2' is dependent on that object.
+ij> drop view v2;
+0 rows inserted/updated/deleted
+ij> -- fail still
+drop synonym mySyn;
+ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V1' is dependent on that object.
+ij> drop view v1;
+0 rows inserted/updated/deleted
+ij> -- should pass
+drop synonym mySyn;
+0 rows inserted/updated/deleted
+ij> -- drop and recreate schema test
+create schema testSchema;
+0 rows inserted/updated/deleted
+ij> create synonym multiSchema for testSchema.testtab;
+0 rows inserted/updated/deleted
+WARNING 01522: The newly defined synonym 'MULTISCHEMA' resolved to the object 'TESTSCHEMA.TESTTAB' which is currently undefined. :
+ij> select * from multiSchema;
+ERROR 42X05: Table 'TESTSCHEMA.TESTTAB' does not exist.
+ij> create table testSchema.testtab(i int, c char(10));
+0 rows inserted/updated/deleted
+ij> insert into testSchema.testtab values (1, 'synonym');
+1 row inserted/updated/deleted
+ij> select * from multiSchema;
+I |C
+-----
+1 |synonym
+ij> drop table testSchema.testtab;
+0 rows inserted/updated/deleted
+ij> drop schema testSchema restrict;
+0 rows inserted/updated/deleted
+ij> create schema testSchema;
+0 rows inserted/updated/deleted
+ij> create table testSchema.testtab(j int, c1 char(10), c2 char(20));
+0 rows inserted/updated/deleted
+ij> insert into testSchema.testtab values (1, 'synonym', 'test');
+1 row inserted/updated/deleted
+ij> select * from multiSchema;
+J |C1 |C2
+-----
+1 |synonym |test
+ij> drop synonym multiSchema;
+0 rows inserted/updated/deleted
+ij> drop table testSchema.testtab;
+0 rows inserted/updated/deleted
+ij> drop view view1;
0 rows inserted/updated/deleted
ij> drop table table1;
0 rows inserted/updated/deleted
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out Wed Jun 8 22:53:18 2005
@@ -203,10 +203,157 @@
5 |5
ij> drop table table2;
0 rows inserted/updated/deleted
-ij> -- TODO: Add more tests here
--- drop and recreate schema test
--- More negative tests once dependency checking is added
-drop view view1;
+ij> -- Try referential constraints. Synonyms should not be allowed there.
+create table primaryTab (i int not null primary key, j int, c char(10));
+0 rows inserted/updated/deleted
+ij> create synonym synPrimary for primaryTab;
+0 rows inserted/updated/deleted
+ij> -- Should fail
+create table foreignTab(i int, j int references synPrimary(i));
+ERROR X0Y46: Constraint 'xxxxGENERATED-IDxxxx' is invalid: referenced table SYNPRIMARY does not exist.
+ij> create table foreignTab(i int, j int references primaryTab(i));
+0 rows inserted/updated/deleted
+ij> drop table foreignTab;
+0 rows inserted/updated/deleted
+ij> drop table primaryTab;
+0 rows inserted/updated/deleted
+ij> drop synonym synPrimary;
+0 rows inserted/updated/deleted
+ij> -- Positive test case with three levels of synonym chaining
+create schema synonymSchema;
+0 rows inserted/updated/deleted
+ij> create synonym synonymSchema.mySynonym1 for APP.table1;
+0 rows inserted/updated/deleted
+ij> create synonym APP.mySynonym2 for "SYNONYMSCHEMA"."MYSYNONYM1";
+0 rows inserted/updated/deleted
+ij> create synonym mySynonym for mySynonym2;
+0 rows inserted/updated/deleted
+ij> select * from table1;
+I |J
+-----------------------
+6 |1
+2 |2
+5 |5
+ij> select * from mySynonym;
+I |J
+-----------------------
+6 |1
+2 |2
+5 |5
+ij> insert into mySynonym values (6,6);
+1 row inserted/updated/deleted
+ij> insert into mySynonym select * from mySynonym where i<2;
+0 rows inserted/updated/deleted
+ij> select * from mySynonym;
+I |J
+-----------------------
+6 |1
+2 |2
+5 |5
+6 |6
+ij> update mySynonym set j=5;
+4 rows inserted/updated/deleted
+ij> update mySynonym set j=4 where i=5;
+1 row inserted/updated/deleted
+ij> delete from mySynonym where j=6;
+0 rows inserted/updated/deleted
+ij> select * from mySynonym;
+I |J
+-----------------------
+6 |5
+2 |5
+5 |4
+6 |5
+ij> select * from table1;
+I |J
+-----------------------
+6 |5
+2 |5
+5 |4
+6 |5
+ij> -- cursor on mySynonym
+get cursor c1 as 'select * from mySynonym';
+ij> next c1;
+I |J
+-----------------------
+6 |5
+ij> next c1;
+I |J
+-----------------------
+2 |5
+ij> next c1;
+I |J
+-----------------------
+5 |4
+ij> close c1;
+ij> -- More negative tests to check dependencies
+select * from mySynonym;
+I |J
+-----------------------
+6 |5
+2 |5
+5 |4
+6 |5
+ij> drop synonym mySynonym;
+0 rows inserted/updated/deleted
+ij> -- Previously compiled cached statement should get invalidated
+select * from mySynonym;
+ERROR 42X05: Table 'MYSYNONYM' does not exist.
+ij> create synonym mySyn for table1;
+0 rows inserted/updated/deleted
+ij> create view v1 as select * from mySyn;
+0 rows inserted/updated/deleted
+ij> create view v2 as select * from v1;
+0 rows inserted/updated/deleted
+ij> -- Drop synonym should fail since it is used in two views.
+drop synonym mySyn;
+ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V1' is dependent on that object.
+ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V2' is dependent on that object.
+ij> drop view v2;
+0 rows inserted/updated/deleted
+ij> -- fail still
+drop synonym mySyn;
+ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V1' is dependent on that object.
+ij> drop view v1;
+0 rows inserted/updated/deleted
+ij> -- should pass
+drop synonym mySyn;
+0 rows inserted/updated/deleted
+ij> -- drop and recreate schema test
+create schema testSchema;
+0 rows inserted/updated/deleted
+ij> create synonym multiSchema for testSchema.testtab;
+0 rows inserted/updated/deleted
+WARNING 01522: The newly defined synonym 'MULTISCHEMA' resolved to the object 'TESTSCHEMA.TESTTAB' which is currently undefined.
+ij> select * from multiSchema;
+ERROR 42X05: Table 'TESTSCHEMA.TESTTAB' does not exist.
+ij> create table testSchema.testtab(i int, c char(10));
+0 rows inserted/updated/deleted
+ij> insert into testSchema.testtab values (1, 'synonym');
+1 row inserted/updated/deleted
+ij> select * from multiSchema;
+I |C
+----------------------
+1 |synonym
+ij> drop table testSchema.testtab;
+0 rows inserted/updated/deleted
+ij> drop schema testSchema restrict;
+0 rows inserted/updated/deleted
+ij> create schema testSchema;
+0 rows inserted/updated/deleted
+ij> create table testSchema.testtab(j int, c1 char(10), c2 char(20));
+0 rows inserted/updated/deleted
+ij> insert into testSchema.testtab values (1, 'synonym', 'test');
+1 row inserted/updated/deleted
+ij> select * from multiSchema;
+J |C1 |C2
+-------------------------------------------
+1 |synonym |test
+ij> drop synonym multiSchema;
+0 rows inserted/updated/deleted
+ij> drop table testSchema.testtab;
+0 rows inserted/updated/deleted
+ij> drop view view1;
0 rows inserted/updated/deleted
ij> drop table table1;
0 rows inserted/updated/deleted
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql?rev=189716&r1=189715&r2=189716&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql Wed Jun 8 22:53:18 2005
@@ -124,9 +124,105 @@
drop table table2;
--- TODO: Add more tests here
+-- Try referential constraints. Synonyms should not be allowed there.
+
+create table primaryTab (i int not null primary key, j int, c char(10));
+
+create synonym synPrimary for primaryTab;
+
+-- Should fail
+create table foreignTab(i int, j int references synPrimary(i));
+
+create table foreignTab(i int, j int references primaryTab(i));
+
+drop table foreignTab;
+drop table primaryTab;
+drop synonym synPrimary;
+
+-- Positive test case with three levels of synonym chaining
+
+create schema synonymSchema;
+
+create synonym synonymSchema.mySynonym1 for APP.table1;
+create synonym APP.mySynonym2 for "SYNONYMSCHEMA"."MYSYNONYM1";
+create synonym mySynonym for mySynonym2;
+
+
+select * from table1;
+select * from mySynonym;
+
+insert into mySynonym values (6,6);
+insert into mySynonym select * from mySynonym where i<2;
+
+select * from mySynonym;
+
+update mySynonym set j=5;
+
+update mySynonym set j=4 where i=5;
+
+delete from mySynonym where j=6;
+
+select * from mySynonym;
+select * from table1;
+
+-- cursor on mySynonym
+get cursor c1 as 'select * from mySynonym';
+
+next c1;
+next c1;
+next c1;
+
+close c1;
+
+-- More negative tests to check dependencies
+select * from mySynonym;
+drop synonym mySynonym;
+
+-- Previously compiled cached statement should get invalidated
+select * from mySynonym;
+
+create synonym mySyn for table1;
+create view v1 as select * from mySyn;
+create view v2 as select * from v1;
+
+-- Drop synonym should fail since it is used in two views.
+drop synonym mySyn;
+
+drop view v2;
+
+-- fail still
+drop synonym mySyn;
+
+drop view v1;
+
+-- should pass
+drop synonym mySyn;
+
-- drop and recreate schema test
--- More negative tests once dependency checking is added
+create schema testSchema;
+
+create synonym multiSchema for testSchema.testtab;
+
+select * from multiSchema;
+
+create table testSchema.testtab(i int, c char(10));
+insert into testSchema.testtab values (1, 'synonym');
+
+select * from multiSchema;
+
+drop table testSchema.testtab;
+drop schema testSchema restrict;
+
+create schema testSchema;
+
+create table testSchema.testtab(j int, c1 char(10), c2 char(20));
+insert into testSchema.testtab values (1, 'synonym', 'test');
+
+select * from multiSchema;
+
+drop synonym multiSchema;
+drop table testSchema.testtab;
drop view view1;
drop table table1;
+