1 /**************************************************************
2  *
3  * Licensed to the Apache Software Foundation (ASF) under one
4  * or more contributor license agreements.  See the NOTICE file
5  * distributed with this work for additional information
6  * regarding copyright ownership.  The ASF licenses this file
7  * to you under the Apache License, Version 2.0 (the
8  * "License"); you may not use this file except in compliance
9  * with the License.  You may obtain a copy of the License at
10  *
11  *   http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing,
14  * software distributed under the License is distributed on an
15  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
16  * KIND, either express or implied.  See the License for the
17  * specific language governing permissions and limitations
18  * under the License.
19  *
20  *************************************************************/
21 
22 
23 package complex.dbaccess;
24 
25 import com.sun.star.container.ElementExistException;
26 import com.sun.star.lang.IllegalArgumentException;
27 import com.sun.star.lang.WrappedTargetException;
28 import com.sun.star.sdb.CommandType;
29 import com.sun.star.sdbc.SQLException;
30 import connectivity.tools.HsqlColumnDescriptor;
31 import connectivity.tools.HsqlTableDescriptor;
32 import connectivity.tools.RowSet;
33 import com.sun.star.sdbc.XStatement;
34 import com.sun.star.sdbc.XResultSet;
35 
36 // ---------- junit imports -----------------
37 import org.junit.Test;
38 import static org.junit.Assert.*;
39 // ------------------------------------------
40 
41 public class QueryInQuery extends CRMBasedTestCase
42 {
43     private static final String QUERY_PRODUCTS = "query products";
44 
45     // --------------------------------------------------------------------------------------------------------
46     @Override
createTestCase()47     protected void createTestCase()
48     {
49         try
50         {
51             super.createTestCase();
52             m_database.getDatabase().getDataSource().createQuery( QUERY_PRODUCTS,"SELECT * FROM \"products\"");
53         }
54         catch ( Exception e )
55         {
56             e.printStackTrace( System.err );
57             fail( "caught an exception (" + e.getMessage() + ") while creating the test case" );
58         }
59     }
60 
61     // --------------------------------------------------------------------------------------------------------
verifyEqualRowSetContent( int _outerCommandType, String _outerCommand, int _innerCommandType, String _innerCommand )62     private void verifyEqualRowSetContent( int _outerCommandType, String _outerCommand, int _innerCommandType, String _innerCommand ) throws SQLException
63     {
64         final RowSet outerRowSet = m_database.getDatabase().createRowSet( _outerCommandType, _outerCommand );
65         outerRowSet.execute();
66 
67         final RowSet innerRowSet = m_database.getDatabase().createRowSet( _innerCommandType, _innerCommand );
68         innerRowSet.execute();
69 
70         outerRowSet.last();
71         innerRowSet.last();
72         assertTrue( "wrong record counts", outerRowSet.getRow() == innerRowSet.getRow() );
73 
74         outerRowSet.beforeFirst();
75         innerRowSet.beforeFirst();
76         assertTrue( "wrong column counts", outerRowSet.getColumnCount() == innerRowSet.getColumnCount() );
77 
78         while ( outerRowSet.next() && innerRowSet.next() )
79         {
80             for ( int i=1; i <= outerRowSet.getColumnCount(); ++i )
81             {
82                 assertTrue( "content of column " + i + " of row " + outerRowSet.getRow() + " not identical",
83                     innerRowSet.getString(i).equals( outerRowSet.getString(i) ) );
84             }
85         }
86     }
87 
88     // --------------------------------------------------------------------------------------------------------
89     /** executes a SQL statement simply selecting all columns from a query
90      */
91     @Test
executeSimpleSelect()92     public void executeSimpleSelect() throws SQLException
93     {
94         verifyEqualRowSetContent(
95             CommandType.COMMAND, "SELECT * FROM \"query products\"",
96             CommandType.QUERY,QUERY_PRODUCTS);
97     }
98 
99     // --------------------------------------------------------------------------------------------------------
100     /** verifies that aliases for inner queries work as expected
101      */
102     @Test
executeAliasedSelect()103     public void executeAliasedSelect() throws SQLException
104     {
105         verifyEqualRowSetContent(
106             CommandType.COMMAND, "SELECT \"PROD\".\"ID\" FROM \"query products\" AS \"PROD\"",
107             CommandType.COMMAND, "SELECT \"ID\" FROM \"products\"" );
108         verifyEqualRowSetContent(
109             CommandType.COMMAND, "SELECT \"PROD\".* FROM \"query products\" AS \"PROD\"",
110             CommandType.QUERY,QUERY_PRODUCTS);
111     }
112 
113     // --------------------------------------------------------------------------------------------------------
114     /** verifies that aliases for inner queries work as expected
115      */
116     @Test
checkNameCollisions()117     public void checkNameCollisions()
118     {
119         // create a query with a name which is used by a table
120         boolean caughtExpected = false;
121         try
122         {
123             m_database.getDatabase().getDataSource().createQuery( "products", "SELECT * FROM \"products\"" );
124         }
125         catch ( WrappedTargetException e ) { caughtExpected = true; }
126         catch ( IllegalArgumentException e ) {}
127         catch ( ElementExistException e ) { caughtExpected = true; }
128         assertTrue( "creating queries with the name of an existing table should not be possible",
129             caughtExpected );
130 
131         // create a table with a name which is used by a query
132         final HsqlTableDescriptor table = new HsqlTableDescriptor( QUERY_PRODUCTS,
133             new HsqlColumnDescriptor[] {
134                 new HsqlColumnDescriptor( "ID", "INTEGER" ),
135                 new HsqlColumnDescriptor( "Name", "VARCHAR(50)" ) } );
136 
137         caughtExpected = false;
138         try
139         {
140             m_database.getDatabase().createTableInSDBCX( table );
141         }
142         catch ( SQLException e ) { caughtExpected = true; }
143         catch ( ElementExistException ex ) { }
144         assertTrue( "creating tables with the name of an existing query should not be possible",
145             caughtExpected );
146     }
147 
148     // --------------------------------------------------------------------------------------------------------
149     @Test
checkCyclicReferences()150     public void checkCyclicReferences() throws ElementExistException, WrappedTargetException, IllegalArgumentException
151     {
152         // some queries which create a cycle in the sub query tree
153         m_database.getDatabase().getDataSource().createQuery( "orders level 1", "SELECT * FROM \"orders level 0\"" );
154         m_database.getDatabase().getDataSource().createQuery( "orders level 2", "SELECT * FROM \"orders level 1\"" );
155         m_database.getDatabase().getDataSource().createQuery( "orders level 3", "SELECT * FROM \"orders level 2\"" );
156         m_database.getDatabase().getDataSource().createQuery( "orders level 0", "SELECT * FROM \"orders level 3\"" );
157 
158         final RowSet rowSet = m_database.getDatabase().createRowSet( CommandType.QUERY, "orders level 0" );
159 
160         boolean caughtExpected = false;
161         try { rowSet.execute(); }
162         catch ( SQLException e ) { caughtExpected = ( e.ErrorCode == -com.sun.star.sdb.ErrorCondition.PARSER_CYCLIC_SUB_QUERIES ); }
163 
164         assertTrue( "executing a query with cyclic nested sub queries should fail!", caughtExpected );
165     }
166 
167     // --------------------------------------------------------------------------------------------------------
168     @Test
checkStatementQiQSupport()169     public void checkStatementQiQSupport()
170     {
171         try
172         {
173             final XStatement statement = m_database.getConnection().createStatement();
174             final XResultSet resultSet = statement.executeQuery( "SELECT * FROM \"query products\"" );
175             assertTrue( "Result Set is null", resultSet != null );
176         }
177         catch( SQLException e )
178         {
179             fail( "SDB level statements do not allow for queries in queries" );
180         }
181     }
182 }
183