| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ViewsTest |
| |
| 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.Connection; |
| import java.sql.DatabaseMetaData; |
| import java.sql.PreparedStatement; |
| 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; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| public final class ViewsTest extends BaseJDBCTestCase { |
| |
| /** |
| * Public constructor required for running test as standalone JUnit. |
| */ |
| public ViewsTest(String name) |
| { |
| super(name); |
| } |
| |
| public static Test suite() |
| { |
| BaseTestSuite suite = new BaseTestSuite("views Test"); |
| suite.addTest(TestConfiguration.embeddedSuite(ViewsTest.class)); |
| return new CleanDatabaseTestSetup(suite); |
| } |
| |
| //DERBY-6185(Query against view with |
| // "where name LIKE 'Col1' ESCAPE '\' " failed) |
| //Problem was that we clone binary comparison operator node during the |
| // optimize phase but we did not copy the entire state of the original |
| // node |
| public void test_DERBY6185() throws Exception |
| { |
| ResultSet rs = null; |
| |
| Statement st = createStatement(); |
| PreparedStatement ps; |
| |
| // create 2 tables and a view |
| st.executeUpdate( |
| "create table mytbl1 (name clob(10K))"); |
| st.executeUpdate("insert into mytbl1 "+ |
| "values ('Col1'),('Col2')"); |
| st.executeUpdate( |
| "create table mytbl2 (name clob(10K))"); |
| st.executeUpdate("insert into mytbl2 "+ |
| "values ('Col1'),('Col2')"); |
| st.executeUpdate( |
| "create view myview (name) as select t1.name from " + |
| "mytbl1 t1 union all select t2.name from mytbl2 t2"); |
| |
| //test base table's CLOB and LIKE clause with Statement |
| rs = st.executeQuery("select name from mytbl1 " + |
| "where name LIKE 'Col1'"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Col1"} |
| }); |
| |
| //test base table's CLOB and LIKE clause with PreparedStatement |
| ps = prepareStatement("select name from mytbl1 " + |
| "where name LIKE ?"); |
| ps.setString(1, "Col1"); |
| rs = ps.executeQuery(); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Col1"} |
| }); |
| |
| //test view's CLOB and LIKE clause with Statement |
| rs = st.executeQuery("select name from myview " + |
| "where name LIKE 'Col1'"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Col1"}, |
| {"Col1"} |
| }); |
| |
| //test view's CLOB and LIKE clause with PreparedStatement |
| ps = prepareStatement("select name from myview " + |
| "where name LIKE ?"); |
| ps.setString(1, "Col1"); |
| rs = ps.executeQuery(); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Col1"}, |
| {"Col1"} |
| }); |
| |
| //test UNION's CLOB and LIKE clause with Statement |
| rs = st.executeQuery("select name from " + |
| "(select name from mytbl1 t1 union all " + |
| "select t2.name from mytbl2 t2) " + |
| "as s where name like 'Col1'"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Col1"}, |
| {"Col1"} |
| }); |
| |
| //test UNION's CLOB and LIKE clause with PreparedStatement |
| ps = prepareStatement("select name from " + |
| "(select name from mytbl1 t1 union all " + |
| "select t2.name from mytbl2 t2) " + |
| "as s where name like ?"); |
| ps.setString(1, "Col1"); |
| rs = ps.executeQuery(); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Col1"}, |
| {"Col1"} |
| }); |
| } |
| |
| public void test_views() throws Exception |
| { |
| ResultSet rs = null; |
| |
| Statement st = createStatement(); |
| |
| String [][] expRS; |
| String [] expColNames; |
| |
| |
| // 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. tests for views set |
| // autocommit off |
| |
| getConnection().setAutoCommit(false); |
| |
| // create some tables |
| |
| st.executeUpdate( |
| "create table t1(i int, s smallint, f float, dp " |
| + "double precision)"); |
| |
| st.executeUpdate( |
| " create table t2(i int, s smallint, f float, dp " |
| + "double precision)"); |
| |
| st.executeUpdate( |
| " create table insert_test (f float)"); |
| |
| // create some views |
| |
| st.executeUpdate( |
| "create view sv1 (s_was_i, dp_was_s, i_was_f, " |
| + "f_was_dp) as select * from t1"); |
| |
| st.executeUpdate( |
| " create view sv2 as select * from t1"); |
| |
| st.executeUpdate( |
| " create view sv3 as select dp, f from t1 where i = s"); |
| |
| st.executeUpdate( |
| " create view sv4(i) as values 1, 2, 3"); |
| |
| st.executeUpdate( |
| " create view sv5 (c1) as select * from sv4"); |
| |
| st.executeUpdate( |
| " create view cv1 (t1_i, t2_s, t1_f, t2_dp) as " |
| + "select t1.i, t2.s, t1.f, t2.dp from t1, t2 where " |
| + "t1.i between t2.s and t2.i"); |
| |
| st.executeUpdate( |
| " create view cv2 as select * from sv1, sv3 where dp " |
| + "= f_was_dp"); |
| |
| st.executeUpdate( |
| " create view cv3(i,s,f,dp) as select i, s, f, dp " |
| + "from sv2 union select dp_was_s, s_was_i, f_was_dp, " |
| + "i_was_f from sv1"); |
| |
| st.executeUpdate( |
| " create view cv4 (distinct_i) as select distinct i from t1"); |
| |
| st.executeUpdate( |
| " create view cv5(i,s) as select * from (select i, s " |
| + "from cv3 where i = s) xyz"); |
| |
| st.executeUpdate( |
| " create view cv6 (c1, c2) as select a.c1 as x, b.c1 " |
| + "as y from sv5 a, sv5 b where a.c1 <> b.c1"); |
| |
| st.executeUpdate( |
| " create view cv7 as select t.i, v.c1 from t1 t, cv6 " |
| + "v where t.i = v.c1"); |
| |
| st.executeUpdate( |
| " create view cv8(col1, col2) as select 'Column 1', " |
| + "'Value = ' || cast(c1 as char(5)) from cv7 where 1 " |
| + "in (select i from sv5)"); |
| |
| // populate the tables |
| |
| st.executeUpdate( |
| "insert into t1 values (1, 1, 1.0, 1.0)"); |
| |
| st.executeUpdate( |
| " insert into t1 values (1, 2, 3.0, 4.0)"); |
| |
| st.executeUpdate( |
| " insert into t1 values (8, 7, 6.0, 5.0)"); |
| |
| st.executeUpdate( |
| " insert into t2 values (1, 1, 1.0, 1.0)"); |
| |
| st.executeUpdate( |
| " insert into t2 values (1, 2, 3.0, 4.0)"); |
| |
| st.executeUpdate( |
| " insert into t2 values (8, 7, 6.0, 5.0)"); |
| |
| // negative tests view with a parameter |
| |
| assertStatementError("42X98", st, |
| "create view vneg as select * from t1 where i = ?"); |
| |
| // drop view on table |
| |
| assertStatementError("X0Y16", st, |
| "drop view t1"); |
| |
| // drop table on view |
| |
| assertStatementError("42Y62", st, |
| "drop table sv1"); |
| |
| // views and tables share same name space |
| |
| assertStatementError("X0Y32", st, |
| "create view sv1(i) as values 1"); |
| |
| assertStatementError("X0Y32", st, |
| " create table sv1 (c1 int)"); |
| |
| assertStatementError("X0Y32", st, |
| " create view t1(i) as values 1"); |
| |
| // drop non-existant view |
| |
| assertStatementError("X0X05", st, |
| "drop view notexists"); |
| |
| // duplicate column name in view's column list |
| |
| assertStatementError("42Y13", st, |
| "create view shouldntwork (c1, c2, c1) as select i, " |
| + "s, f from t1"); |
| |
| // # of columns in view's column list does not match that |
| // in view definition |
| |
| assertStatementError("42X56", st, |
| "create view shouldntwork (c1, c2, c3) as select i, s from t1"); |
| |
| assertStatementError("42X56", st, |
| " create view shouldntwork (c1, c2, c3) as select i, " |
| + "s, f, dp from t1"); |
| |
| // try to drop a table out from under a view |
| |
| assertStatementError(new String[] {"X0Y23","X0Y23","X0Y23","X0Y23","X0Y23","X0Y23", |
| "X0Y23","X0Y23","X0Y23","X0Y23"},st, |
| "drop table t1"); |
| |
| assertStatementError("X0Y23", st, |
| " drop table t2"); |
| |
| // try to drop a view out from under another view |
| |
| assertStatementError(new String[] {"X0Y23","X0Y23","X0Y23"}, st, |
| "drop view sv1"); |
| |
| assertStatementError("X0Y23", st, |
| " drop view sv3"); |
| |
| // try to drop a view out from under a cursor |
| |
| PreparedStatement ps_c1 = prepareStatement( |
| "select * from cv8"); |
| |
| ResultSet c1 = ps_c1.executeQuery(); |
| |
| assertStatementError("X0X95", st, |
| " drop view cv8"); |
| |
| assertStatementErrorUnordered( |
| new String[] {"X0Y23","X0Y23","X0Y23","X0X95"}, st, |
| " drop view sv5"); |
| |
| assertStatementErrorUnordered( |
| new String[] {"X0Y23","X0Y23","X0Y23","X0Y23","X0X95"}, st, |
| " drop view sv4"); |
| |
| c1.close(); |
| ps_c1.close(); |
| |
| // view updateability (No views are currently updateable) |
| |
| assertStatementError("42Y24", st, |
| "insert into sv1 values 1"); |
| |
| assertStatementError("42Y24", st, |
| " delete from sv1"); |
| |
| assertStatementError("42Y24", st, |
| " update sv1 set s_was_i = 0"); |
| |
| try { |
| prepareStatement( |
| "select * from sv1 for update of s_was_i"); |
| fail("statement ps_c2 should not have succeeded"); |
| } catch (SQLException se) { |
| assertSQLState("42Y90",se); |
| } |
| |
| // create index on a view |
| |
| assertStatementError("42Y62", st, |
| "create index i1 on sv2(i)"); |
| |
| // positive tests |
| |
| rs = st.executeQuery( |
| "select * from sv1"); |
| |
| expColNames = new String [] {"S_WAS_I", "DP_WAS_S", "I_WAS_F", "F_WAS_DP"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1", "1.0", "1.0"}, |
| {"1", "2", "3.0", "4.0"}, |
| {"8", "7", "6.0", "5.0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from sv2"); |
| |
| expColNames = new String [] {"I", "S", "F", "DP"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1", "1.0", "1.0"}, |
| {"1", "2", "3.0", "4.0"}, |
| {"8", "7", "6.0", "5.0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from sv3"); |
| |
| expColNames = new String [] {"DP", "F"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1.0", "1.0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from sv4"); |
| |
| expColNames = new String [] {"I"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1"}, |
| {"2"}, |
| {"3"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from sv5"); |
| |
| expColNames = new String [] {"C1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1"}, |
| {"2"}, |
| {"3"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from cv1"); |
| |
| expColNames = new String [] {"T1_I", "T2_S", "T1_F", "T2_DP"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1", "1.0", "1.0"}, |
| {"1", "1", "3.0", "1.0"}, |
| {"8", "7", "6.0", "5.0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from cv2"); |
| |
| expColNames = new String [] {"S_WAS_I", "DP_WAS_S", "I_WAS_F", "F_WAS_DP", "DP", "F"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1", "1.0", "1.0", "1.0", "1.0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from cv3"); |
| |
| expColNames = new String [] {"I", "S", "F", "DP"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1", "1.0", "1.0"}, |
| {"1", "2", "3.0", "4.0"}, |
| {"2", "1", "4.0", "3.0"}, |
| {"7", "8", "5.0", "6.0"}, |
| {"8", "7", "6.0", "5.0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from cv4 order by 1"); |
| |
| expColNames = new String [] {"DISTINCT_I"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1"}, |
| {"8"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from cv5"); |
| |
| expColNames = new String [] {"I", "S"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from cv6"); |
| |
| expColNames = new String [] {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "2"}, |
| {"1", "3"}, |
| {"2", "1"}, |
| {"2", "3"}, |
| {"3", "1"}, |
| {"3", "2"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from cv7"); |
| |
| expColNames = new String [] {"I", "C1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1"}, |
| {"1", "1"}, |
| {"1", "1"}, |
| {"1", "1"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from cv8"); |
| |
| expColNames = new String [] {"COL1", "COL2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"Column 1", "Value = 1"}, |
| {"Column 1", "Value = 1"}, |
| {"Column 1", "Value = 1"}, |
| {"Column 1", "Value = 1"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from (select * from cv3) x order by 1,2"); |
| |
| expColNames = new String [] {"I", "S", "F", "DP"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1", "1.0", "1.0"}, |
| {"1", "2", "3.0", "4.0"}, |
| {"2", "1", "4.0", "3.0"}, |
| {"7", "8", "5.0", "6.0"}, |
| {"8", "7", "6.0", "5.0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from (select * from cv4) x order by 1"); |
| |
| expColNames = new String [] {"DISTINCT_I"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1"}, |
| {"8"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select * from (select * from cv5) x"); |
| |
| expColNames = new String [] {"I", "S"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // verify that we can create and drop indexes on |
| // underlying tables |
| |
| st.executeUpdate( |
| "create index i on t1(i)"); |
| |
| st.executeUpdate( |
| " drop index i"); |
| |
| // verify the consistency of the indexes on the system |
| // catalogs |
| |
| rs = st.executeQuery( |
| "select tablename, " |
| + "SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from " |
| + "sys.systables where CAST(tabletype AS CHAR(1)) = " |
| + "'S' and CAST(tablename AS VARCHAR(128)) != 'SYSDUMMY1' order by tablename"); |
| |
| expColNames = new String [] {"TABLENAME", "2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"SYSALIASES", "1"}, |
| {"SYSCHECKS", "1"}, |
| {"SYSCOLPERMS", "1"}, |
| {"SYSCOLUMNS", "1"}, |
| {"SYSCONGLOMERATES", "1"}, |
| {"SYSCONSTRAINTS", "1"}, |
| {"SYSDEPENDS", "1"}, |
| {"SYSFILES", "1"}, |
| {"SYSFOREIGNKEYS", "1"}, |
| {"SYSKEYS", "1"}, |
| {"SYSPERMS", "1"}, |
| {"SYSROLES", "1"}, |
| {"SYSROUTINEPERMS", "1"}, |
| {"SYSSCHEMAS", "1"}, |
| {"SYSSEQUENCES", "1"}, |
| {"SYSSTATEMENTS", "1"}, |
| {"SYSSTATISTICS", "1"}, |
| {"SYSTABLEPERMS", "1"}, |
| {"SYSTABLES", "1"}, |
| {"SYSTRIGGERS", "1"}, |
| {"SYSUSERS", "1"}, |
| {"SYSVIEWS", "1"}, |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // test inserts from a view |
| |
| st.executeUpdate( |
| "insert into insert_test select * from sv5"); |
| |
| rs = st.executeQuery( |
| " select * from insert_test"); |
| |
| expColNames = new String [] {"F"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1.0"}, |
| {"2.0"}, |
| {"3.0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // drop the views |
| |
| st.executeUpdate( |
| "drop view cv8"); |
| |
| st.executeUpdate( |
| " drop view cv7"); |
| |
| st.executeUpdate( |
| " drop view cv6"); |
| |
| st.executeUpdate( |
| " drop view cv5"); |
| |
| st.executeUpdate( |
| " drop view cv4"); |
| |
| st.executeUpdate( |
| " drop view cv3"); |
| |
| st.executeUpdate( |
| " drop view cv2"); |
| |
| st.executeUpdate( |
| " drop view cv1"); |
| |
| st.executeUpdate( |
| " drop view sv5"); |
| |
| st.executeUpdate( |
| " drop view sv4"); |
| |
| st.executeUpdate( |
| " drop view sv3"); |
| |
| st.executeUpdate( |
| " drop view sv2"); |
| |
| st.executeUpdate( |
| " drop view sv1"); |
| |
| // drop the tables |
| |
| st.executeUpdate( |
| "drop table t1"); |
| |
| st.executeUpdate( |
| " drop table t2"); |
| |
| st.executeUpdate( |
| " drop table insert_test"); |
| |
| // verify the consistency of the indexes on the system |
| // catalogs |
| |
| rs = st.executeQuery( |
| "select tablename, " |
| + "SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from " |
| + "sys.systables where CAST(tabletype as CHAR(1)) = " |
| + "'S' and CAST(tablename as VARCHAR(128)) != 'SYSDUMMY1' order by tablename"); |
| |
| expColNames = new String [] {"TABLENAME", "2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"SYSALIASES", "1"}, |
| {"SYSCHECKS", "1"}, |
| {"SYSCOLPERMS", "1"}, |
| {"SYSCOLUMNS", "1"}, |
| {"SYSCONGLOMERATES", "1"}, |
| {"SYSCONSTRAINTS", "1"}, |
| {"SYSDEPENDS", "1"}, |
| {"SYSFILES", "1"}, |
| {"SYSFOREIGNKEYS", "1"}, |
| {"SYSKEYS", "1"}, |
| {"SYSPERMS", "1"}, |
| {"SYSROLES", "1"}, |
| {"SYSROUTINEPERMS", "1"}, |
| {"SYSSCHEMAS", "1"}, |
| {"SYSSEQUENCES", "1"}, |
| {"SYSSTATEMENTS", "1"}, |
| {"SYSSTATISTICS", "1"}, |
| {"SYSTABLEPERMS", "1"}, |
| {"SYSTABLES", "1"}, |
| {"SYSTRIGGERS", "1"}, |
| {"SYSUSERS", "1"}, |
| {"SYSVIEWS", "1"}, |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // bug 2745 |
| |
| st.executeUpdate( |
| "CREATE TABLE orgtable ( name VARCHAR(255), " |
| + "supervisorname VARCHAR(255), jobtitle VARCHAR(255) )"); |
| |
| st.executeUpdate( |
| " CREATE VIEW orgview AS SELECT name, " |
| + "supervisorname, jobtitle FROM orgtable"); |
| |
| rs = st.executeQuery( |
| " SELECT name,jobtitle FROM orgview WHERE name IN " |
| + "(SELECT supervisorname FROM orgview WHERE name LIKE " |
| + "'WYATT%')"); |
| |
| expColNames = new String [] {"NAME", "JOBTITLE"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| JDBC.assertDrainResults(rs, 0); |
| |
| st.executeUpdate( |
| " drop view orgview"); |
| |
| st.executeUpdate( |
| " drop table orgtable"); |
| |
| // reset autocommit |
| |
| getConnection().setAutoCommit(true); |
| |
| // DERBY-1304 view not getting dropped The second drop |
| // view statement fails before the patch |
| |
| st.executeUpdate( |
| "CREATE SCHEMA TEST_SCHEMA"); |
| |
| st.executeUpdate( |
| " CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR)"); |
| |
| st.executeUpdate( |
| " CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN " |
| + "AS VIEW_COLUMN FROM TEST_SCHEMA.T1"); |
| |
| st.executeUpdate( |
| " DROP VIEW TEST_SCHEMA.V1"); |
| |
| st.executeUpdate( |
| " DROP TABLE TEST_SCHEMA.T1"); |
| |
| st.executeUpdate( |
| " DROP SCHEMA TEST_SCHEMA RESTRICT"); |
| |
| // reiterate |
| |
| st.executeUpdate( |
| "CREATE SCHEMA TEST_SCHEMA"); |
| |
| st.executeUpdate( |
| " CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR)"); |
| |
| st.executeUpdate( |
| " CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN " |
| + "AS VIEW_COLUMN FROM TEST_SCHEMA.T1"); |
| |
| st.executeUpdate( |
| " DROP VIEW TEST_SCHEMA.V1"); |
| |
| st.executeUpdate( |
| " DROP TABLE TEST_SCHEMA.T1"); |
| |
| st.executeUpdate( |
| " DROP SCHEMA TEST_SCHEMA RESTRICT"); |
| |
| // DERBY-2018 expect error |
| |
| assertStatementError("42X07", st, |
| "CREATE VIEW v1(c1) AS VALUES NULL"); |
| |
| // DERBY-681 |
| |
| st.executeUpdate( |
| "create table o (name varchar(20), ord int)"); |
| |
| st.executeUpdate( |
| " create table a (ord int, amount int)"); |
| |
| st.executeUpdate( |
| " create view v1 (vx, vy) as select name, sum(ord) " |
| + "from o where ord > 0 group by name, ord"); |
| |
| st.executeUpdate( |
| " create view v2 (vx, vy) as select name, sum(ord) " |
| + "from o where ord > 0 group by name, ord having ord " |
| + "<= ANY (select ord from a)"); |
| |
| st.executeUpdate( |
| " drop view v2"); |
| |
| st.executeUpdate( |
| " drop view v1"); |
| |
| st.executeUpdate( |
| " drop table a"); |
| |
| st.executeUpdate( |
| " drop table o"); |
| |
| |
| |
| getConnection().rollback(); |
| st.close(); |
| } |
| |
| /** |
| * DERBY-3270 Test that we can select from a view in another schema if the |
| * default schema does not exist. |
| * |
| * @throws SQLException |
| */ |
| public void testSelectViewFromOtherSchemaWithNoDefaultSchema() |
| throws SQLException { |
| Connection conn = openDefaultConnection("joe","joepass"); |
| Statement st = conn.createStatement(); |
| st.execute("create table mytable(a int)"); |
| st.execute("insert into mytable values (99)"); |
| st.execute("create view myview as select * from mytable"); |
| st.close(); |
| conn.close(); |
| Connection conn2 = openDefaultConnection("bill","billpass"); |
| Statement st2 = conn2.createStatement(); |
| ResultSet rs = st2.executeQuery("SELECT * FROM JOE.MYVIEW"); |
| JDBC.assertFullResultSet(rs,new String[][] {{"99"}}); |
| st2.executeUpdate("drop view joe.myview"); |
| st2.executeUpdate("drop table joe.mytable"); |
| st2.close(); |
| conn2.close(); |
| } |
| |
| /** |
| * Make sure DatabaseMetaData.getColumns is correct when we have a view |
| * created when there is an expression in the select list. |
| * Also check the ResultSetMetaData |
| * @throws SQLException |
| */ |
| public void testViewMetaDataWithGeneratedColumnsDerby4230() throws SQLException { |
| Statement s = createStatement(); |
| s.executeUpdate("create table A (id integer, data varchar(20), data2 integer)"); |
| s.executeUpdate("insert into A values (3, 'G', 5), (23, 'G', 4), (5, 'F', 1), (2, 'H', 4), (1, 'F', 5)"); |
| //DERBY-4230. Make sure DatabaseMetaData.getColumns does not include generated columns. |
| // You need an expression in the select list. |
| s.executeUpdate("create view V (data, num) as select data, data2 + 2 from A group by data, data2"); |
| DatabaseMetaData dmd = getConnection().getMetaData(); |
| ResultSet columns = dmd.getColumns(null, null, "V", null); |
| String[][] expectedDBMetaRows = new String[][] {{"","APP","V","DATA","12","VARCHAR","20",null,null,null,"1","",null,null,null |
| ,"40","1","YES",null,null,null,null,"NO","NO",null}, |
| {"","APP","V","NUM","4","INTEGER","10",null,"0","10","1","",null,null,null,null,"2","YES",null,null,null,null,"NO","NO",null}}; |
| JDBC.assertFullResultSet(columns,expectedDBMetaRows); |
| // Make sure ResultSetMetaData is right when selecting from view. |
| // This wasn't a problem with DERBY-4230, but checking for good measure. |
| ResultSet rs = s.executeQuery("SELECT * FROM V"); |
| JDBC.assertColumnNames(rs, new String[] {"DATA","NUM"}); |
| JDBC.assertColumnTypes(rs, new int[] {java.sql.Types.VARCHAR, java.sql.Types.INTEGER}); |
| JDBC.assertNullability(rs,new boolean[] {true,true}); |
| // Finally check the results. |
| String [][] expectedRows = new String[][] {{"F","3"}, |
| {"F","7"}, |
| {"G","6"}, |
| {"G","7"}, |
| {"H","6"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| s.executeUpdate("DROP VIEW V"); |
| s.executeUpdate("DROP TABLE A"); |
| } |
| |
| |
| /** |
| * DERBY-3478 |
| * Make sure column names are correct when we select from a view and also |
| * give a table correation name with derived column list. |
| * |
| * E.g. SELECT * FROM V1 X(A,B) |
| * |
| * @throws SQLException |
| */ |
| public void testViewMetaDataWithCorrelationNameAndDerivedColumnList_3478() |
| throws SQLException |
| { |
| Statement s = createStatement(); |
| s.executeUpdate("create table t1 (i int, j int)"); |
| s.executeUpdate("insert into t1 values (1, 1), (1, -1), " + |
| " (2, 2), (3, -3), (4, 4)"); |
| |
| s.executeUpdate("create view v1 as select j, i from t1"); |
| s.executeUpdate("create view v2 (x,y,z) as select j, i, i+j from t1"); |
| |
| DatabaseMetaData dmd = getConnection().getMetaData(); |
| ResultSet columns = dmd.getColumns(null, null, "V1", null); |
| |
| String[][] expectedDBMetaRows = new String[][] |
| {{"","APP","V1","J","4","INTEGER","10",null,"0","10","1","", |
| null,null,null,null,"1","YES",null,null,null,null,"NO","NO",null}, |
| {"","APP","V1","I","4","INTEGER","10",null,"0","10","1","", |
| null,null,null,null,"2","YES",null,null,null,null,"NO","NO",null}}; |
| |
| JDBC.assertFullResultSet(columns,expectedDBMetaRows); |
| |
| expectedDBMetaRows = new String[][] |
| {{"","APP","V2","X","4","INTEGER","10",null,"0","10","1","", |
| null,null,null,null,"1","YES",null,null,null,null,"NO","NO",null}, |
| {"","APP","V2","Y","4","INTEGER","10",null,"0","10","1","", |
| null,null,null,null,"2","YES",null,null,null,null,"NO","NO",null}, |
| {"","APP","V2","Z","4","INTEGER","10",null,"0","10","1","", |
| null,null,null,null,"3","YES",null,null,null,null,"NO","NO",null}}; |
| |
| columns = dmd.getColumns(null, null, "V2", null); |
| JDBC.assertFullResultSet(columns,expectedDBMetaRows); |
| |
| // Make sure ResultSetMetaData is right when selecting from view. This |
| // exposes DERBY-3478 if not fixed. |
| |
| ResultSet rs = s.executeQuery("select * from v1 x(a,b)"); |
| JDBC.assertColumnNames(rs, new String[] {"A","B"}); |
| JDBC.assertColumnTypes(rs, new int[] {java.sql.Types.INTEGER, |
| java.sql.Types.INTEGER}); |
| JDBC.assertNullability(rs,new boolean[] {true,true}); |
| |
| // Check the results. |
| String [][] expectedRows = new String[][] |
| {{"1","1"}, |
| {"-1","1"}, |
| {"2","2"}, |
| {"-3","3"}, |
| {"4","4"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| rs = s.executeQuery("select * from v2 as x(a,b,d)"); |
| JDBC.assertColumnNames(rs, new String[] {"A","B","D"}); |
| JDBC.assertColumnTypes(rs, new int[] {java.sql.Types.INTEGER, |
| java.sql.Types.INTEGER, |
| java.sql.Types.INTEGER}); |
| JDBC.assertNullability(rs,new boolean[] {true,true, true}); |
| |
| // Check the results. |
| expectedRows = new String[][] |
| {{"1","1","2"}, |
| {"-1","1","0"}, |
| {"2","2","4"}, |
| {"-3","3","0"}, |
| {"4","4","8"}}; |
| JDBC.assertFullResultSet(rs, expectedRows); |
| |
| s.executeUpdate("drop view v1"); |
| s.executeUpdate("drop view v2"); |
| s.executeUpdate("drop table t1"); |
| } |
| } |
