db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject 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/
Date Thu, 09 Jun 2005 05:53:19 GMT
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;
+



Mime
View raw message