1 /*************************************************************************
2  *
3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4  *
5  * Copyright 2000, 2010 Oracle and/or its affiliates.
6  *
7  * OpenOffice.org - a multi-platform office productivity suite
8  *
9  * This file is part of OpenOffice.org.
10  *
11  * OpenOffice.org is free software: you can redistribute it and/or modify
12  * it under the terms of the GNU Lesser General Public License version 3
13  * only, as published by the Free Software Foundation.
14  *
15  * OpenOffice.org is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18  * GNU Lesser General Public License version 3 for more details
19  * (a copy is included in the LICENSE file that accompanied this code).
20  *
21  * You should have received a copy of the GNU Lesser General Public License
22  * version 3 along with OpenOffice.org.  If not, see
23  * <http://www.openoffice.org/license.html>
24  * for a copy of the LGPLv3 License.
25  *
26  ************************************************************************/
27 package complex.dbaccess;
28 
29 import com.sun.star.container.ElementExistException;
30 import com.sun.star.lang.IllegalArgumentException;
31 import com.sun.star.lang.WrappedTargetException;
32 import com.sun.star.sdb.CommandType;
33 import com.sun.star.sdbc.SQLException;
34 import connectivity.tools.HsqlColumnDescriptor;
35 import connectivity.tools.HsqlTableDescriptor;
36 import connectivity.tools.RowSet;
37 import com.sun.star.sdbc.XStatement;
38 import com.sun.star.sdbc.XResultSet;
39 
40 // ---------- junit imports -----------------
41 import org.junit.Test;
42 import static org.junit.Assert.*;
43 // ------------------------------------------
44 
45 public class QueryInQuery extends CRMBasedTestCase
46 {
47     private static final String QUERY_PRODUCTS = "query products";
48 
49     // --------------------------------------------------------------------------------------------------------
50     @Override
51     protected void createTestCase()
52     {
53         try
54         {
55             super.createTestCase();
56             m_database.getDatabase().getDataSource().createQuery( QUERY_PRODUCTS,"SELECT * FROM \"products\"");
57         }
58         catch ( Exception e )
59         {
60             e.printStackTrace( System.err );
61             fail( "caught an exception (" + e.getMessage() + ") while creating the test case" );
62         }
63     }
64 
65     // --------------------------------------------------------------------------------------------------------
66     private void verifyEqualRowSetContent( int _outerCommandType, String _outerCommand, int _innerCommandType, String _innerCommand ) throws SQLException
67     {
68         final RowSet outerRowSet = m_database.getDatabase().createRowSet( _outerCommandType, _outerCommand );
69         outerRowSet.execute();
70 
71         final RowSet innerRowSet = m_database.getDatabase().createRowSet( _innerCommandType, _innerCommand );
72         innerRowSet.execute();
73 
74         outerRowSet.last();
75         innerRowSet.last();
76         assertTrue( "wrong record counts", outerRowSet.getRow() == innerRowSet.getRow() );
77 
78         outerRowSet.beforeFirst();
79         innerRowSet.beforeFirst();
80         assertTrue( "wrong column counts", outerRowSet.getColumnCount() == innerRowSet.getColumnCount() );
81 
82         while ( outerRowSet.next() && innerRowSet.next() )
83         {
84             for ( int i=1; i <= outerRowSet.getColumnCount(); ++i )
85             {
86                 assertTrue( "content of column " + i + " of row " + outerRowSet.getRow() + " not identical",
87                     innerRowSet.getString(i).equals( outerRowSet.getString(i) ) );
88             }
89         }
90     }
91 
92     // --------------------------------------------------------------------------------------------------------
93     /** executes a SQL statement simply selecting all columns from a query
94      */
95     @Test
96     public void executeSimpleSelect() throws SQLException
97     {
98         verifyEqualRowSetContent(
99             CommandType.COMMAND, "SELECT * FROM \"query products\"",
100             CommandType.QUERY,QUERY_PRODUCTS);
101     }
102 
103     // --------------------------------------------------------------------------------------------------------
104     /** verifies that aliases for inner queries work as expected
105      */
106     @Test
107     public void executeAliasedSelect() throws SQLException
108     {
109         verifyEqualRowSetContent(
110             CommandType.COMMAND, "SELECT \"PROD\".\"ID\" FROM \"query products\" AS \"PROD\"",
111             CommandType.COMMAND, "SELECT \"ID\" FROM \"products\"" );
112         verifyEqualRowSetContent(
113             CommandType.COMMAND, "SELECT \"PROD\".* FROM \"query products\" AS \"PROD\"",
114             CommandType.QUERY,QUERY_PRODUCTS);
115     }
116 
117     // --------------------------------------------------------------------------------------------------------
118     /** verifies that aliases for inner queries work as expected
119      */
120     @Test
121     public void checkNameCollisions()
122     {
123         // create a query with a name which is used by a table
124         boolean caughtExpected = false;
125         try
126         {
127             m_database.getDatabase().getDataSource().createQuery( "products", "SELECT * FROM \"products\"" );
128         }
129         catch ( WrappedTargetException e ) { caughtExpected = true; }
130         catch ( IllegalArgumentException e ) {}
131         catch ( ElementExistException e ) { caughtExpected = true; }
132         assertTrue( "creating queries with the name of an existing table should not be possible",
133             caughtExpected );
134 
135         // create a table with a name which is used by a query
136         final HsqlTableDescriptor table = new HsqlTableDescriptor( QUERY_PRODUCTS,
137             new HsqlColumnDescriptor[] {
138                 new HsqlColumnDescriptor( "ID", "INTEGER" ),
139                 new HsqlColumnDescriptor( "Name", "VARCHAR(50)" ) } );
140 
141         caughtExpected = false;
142         try
143         {
144             m_database.getDatabase().createTableInSDBCX( table );
145         }
146         catch ( SQLException e ) { caughtExpected = true; }
147         catch ( ElementExistException ex ) { }
148         assertTrue( "creating tables with the name of an existing query should not be possible",
149             caughtExpected );
150     }
151 
152     // --------------------------------------------------------------------------------------------------------
153     @Test
154     public void checkCyclicReferences() throws ElementExistException, WrappedTargetException, IllegalArgumentException
155     {
156         // some queries which create a cycle in the sub query tree
157         m_database.getDatabase().getDataSource().createQuery( "orders level 1", "SELECT * FROM \"orders level 0\"" );
158         m_database.getDatabase().getDataSource().createQuery( "orders level 2", "SELECT * FROM \"orders level 1\"" );
159         m_database.getDatabase().getDataSource().createQuery( "orders level 3", "SELECT * FROM \"orders level 2\"" );
160         m_database.getDatabase().getDataSource().createQuery( "orders level 0", "SELECT * FROM \"orders level 3\"" );
161 
162         final RowSet rowSet = m_database.getDatabase().createRowSet( CommandType.QUERY, "orders level 0" );
163 
164         boolean caughtExpected = false;
165         try { rowSet.execute(); }
166         catch ( SQLException e ) { caughtExpected = ( e.ErrorCode == -com.sun.star.sdb.ErrorCondition.PARSER_CYCLIC_SUB_QUERIES ); }
167 
168         assertTrue( "executing a query with cyclic nested sub queries should fail!", caughtExpected );
169     }
170 
171     // --------------------------------------------------------------------------------------------------------
172     @Test
173     public void checkStatementQiQSupport()
174     {
175         try
176         {
177             final XStatement statement = m_database.getConnection().createStatement();
178             final XResultSet resultSet = statement.executeQuery( "SELECT * FROM \"query products\"" );
179             assertTrue( "Result Set is null", resultSet != null );
180         }
181         catch( SQLException e )
182         {
183             fail( "SDB level statements do not allow for queries in queries" );
184         }
185     }
186 }
187