1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
/**
* Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SynonymTest
*
* 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.
*/
package org.apache.derbyTesting.functionTests.tests.lang;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
/**
* Synonym testing using junit
*/
public class SynonymTest extends BaseJDBCTestCase {
/**
* Basic constructor.
*/
public SynonymTest(String name) {
super(name);
}
/**
* Create a suite of tests.
*/
public static Test suite() {
BaseTestSuite suite =
new BaseTestSuite(SynonymTest.class, "SynonymTest");
return new CleanDatabaseTestSetup(suite);
}
/**
* The test makes sure that we correctly throw dependency exception when
* user requests to drop a synonym which has dependent objects on it. Once
* the dependency is removed, we should be able to drop the synonym.
* @throws SQLException
*/
public void testViewDependency() throws SQLException {
Statement stmt = createStatement();
stmt.executeUpdate("create synonym mySyn for sys.systables");
stmt.executeUpdate("create view v1 as select * from mySyn");
stmt.executeUpdate("create view v2 as select * from v1");
// Drop synonym should fail since it is used in two views.
assertStatementError("X0Y23", stmt, "drop synonym mySyn");
stmt.executeUpdate("drop view v2");
// fails still because of view v1's dependency
assertStatementError("X0Y23", stmt, "drop synonym mySyn");
stmt.executeUpdate("drop view v1");
stmt.executeUpdate("drop synonym mySyn");
stmt.close();
}
/**
* DERBY-5244 DatabaseMetaData.getColumns(null, null, tableName, null)
* does not return the columns meta for a SYNONYM. This is because for
* synonyms, we do not add any rows in SYSCOLUMNS. But the metadata query
* for DatabaseMetaData.getColumns() looks at SYSCOLUMNS to get the
* resultset. Views and Tables do not have problems because we do keep
* their columns information in SYSCOLUMNS.
*
* Test DatabaseMetaData.getColumns call on synonyms
*
* This test confirms the behavior noticed in DERBY-5244.
*/
public void testMetaDataCallOnSynonymsDERBY5244()
throws SQLException
{
Statement st = createStatement();
st.executeUpdate("create table t1Derby5422 "+
"( c11 int not null, c12 char(2) )");
//Create a synonym on table t1Derby5422
st.executeUpdate("create synonym s1Derby5422 for t1Derby5422");
st.executeUpdate("create view v1Derby5422 as select * from t1Derby5422");
//Verify that the synonym has been created successfully by
// doing a select from it
ResultSet rs = st.executeQuery("select * from S1DERBY5422");
JDBC.assertEmpty(rs);
DatabaseMetaData dbmd = getConnection().getMetaData();
//Derby can find metadata info for the base table
rs = dbmd.getColumns(null, null, "T1DERBY5422", null);
JDBC.assertDrainResultsHasData(rs);
//Derby can find metadata info for the view
rs = dbmd.getColumns(null, null, "V1DERBY5422", null);
JDBC.assertDrainResultsHasData(rs);
//But Derby does not locate the metadata info for synonym
rs = dbmd.getColumns(null, null, "S1DERBY5422", null);
JDBC.assertEmpty(rs);
}
/**
* Test that synonyms are dereferenced properly for a searched DELETE.
*
* This test verifies that DERBY-4110 is fixed.
*/
public void testSynonymsInSearchedDeleteDERBY4110()
throws SQLException
{
Statement st = createStatement();
st.executeUpdate("create schema test1");
st.executeUpdate("create schema test2");
st.executeUpdate("create table test1.t1 ( id bigint not null )");
st.executeUpdate("insert into test1.t1 values (1),(2)");
st.executeUpdate("create synonym test2.t1 for test1.t1");
st.executeUpdate("create unique index idx4110 on test1.t1 (id)");
st.executeUpdate("set schema test2");
st.executeUpdate("delete from t1 where id = 2"); // DERBY-4110 here
st.executeUpdate("drop synonym test2.t1");
st.executeUpdate("drop table test1.t1");
st.executeUpdate("drop schema test2 restrict");
st.executeUpdate("drop schema test1 restrict");
}
/**
* Verify the fix for DERBY-5168. SynonymAliasInfo.toString() used to
* return a value with incorrect syntax if the synonym referred to a
* table that had a double quote character either in its name or in the
* schema name.
*/
public void testSynonymsForTablesWithDoubleQuotes() throws SQLException {
setAutoCommit(false);
Statement s = createStatement();
s.execute("create schema \"\"\"\"");
s.execute("create table \"\"\"\".\"\"\"\" (x int)");
s.execute("create synonym derby_5168_synonym for \"\"\"\".\"\"\"\"");
// We can exercise SynonymAliasInfo.toString() by reading the ALIASINFO
// column in SYS.SYSALIASES. This assert used to fail before the fix.
JDBC.assertSingleValueResultSet(
s.executeQuery(
"select aliasinfo from sys.sysaliases " +
"where alias = 'DERBY_5168_SYNONYM'"),
"\"\"\"\".\"\"\"\"");
}
}
|
