/**************************************************************
*
* 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 complex.dbaccess;
import com.sun.star.container.ElementExistException;
import com.sun.star.lang.IllegalArgumentException;
import com.sun.star.lang.WrappedTargetException;
import com.sun.star.sdb.CommandType;
import com.sun.star.sdbc.SQLException;
import connectivity.tools.HsqlColumnDescriptor;
import connectivity.tools.HsqlTableDescriptor;
import connectivity.tools.RowSet;
import com.sun.star.sdbc.XStatement;
import com.sun.star.sdbc.XResultSet;
// ---------- junit imports -----------------
import org.junit.Test;
import static org.junit.Assert.*;
// ------------------------------------------
public class QueryInQuery extends CRMBasedTestCase
{
private static final String QUERY_PRODUCTS = "query products";
// --------------------------------------------------------------------------------------------------------
@Override
protected void createTestCase()
{
try
{
super.createTestCase();
m_database.getDatabase().getDataSource().createQuery( QUERY_PRODUCTS,"SELECT * FROM \"products\"");
}
catch ( Exception e )
{
e.printStackTrace( System.err );
fail( "caught an exception (" + e.getMessage() + ") while creating the test case" );
}
}
// --------------------------------------------------------------------------------------------------------
private void verifyEqualRowSetContent( int _outerCommandType, String _outerCommand, int _innerCommandType, String _innerCommand ) throws SQLException
{
final RowSet outerRowSet = m_database.getDatabase().createRowSet( _outerCommandType, _outerCommand );
outerRowSet.execute();
final RowSet innerRowSet = m_database.getDatabase().createRowSet( _innerCommandType, _innerCommand );
innerRowSet.execute();
outerRowSet.last();
innerRowSet.last();
assertTrue( "wrong record counts", outerRowSet.getRow() == innerRowSet.getRow() );
outerRowSet.beforeFirst();
innerRowSet.beforeFirst();
assertTrue( "wrong column counts", outerRowSet.getColumnCount() == innerRowSet.getColumnCount() );
while ( outerRowSet.next() && innerRowSet.next() )
{
for ( int i=1; i <= outerRowSet.getColumnCount(); ++i )
{
assertTrue( "content of column " + i + " of row " + outerRowSet.getRow() + " not identical",
innerRowSet.getString(i).equals( outerRowSet.getString(i) ) );
}
}
}
// --------------------------------------------------------------------------------------------------------
/** executes a SQL statement simply selecting all columns from a query
*/
@Test
public void executeSimpleSelect() throws SQLException
{
verifyEqualRowSetContent(
CommandType.COMMAND, "SELECT * FROM \"query products\"",
CommandType.QUERY,QUERY_PRODUCTS);
}
// --------------------------------------------------------------------------------------------------------
/** verifies that aliases for inner queries work as expected
*/
@Test
public void executeAliasedSelect() throws SQLException
{
verifyEqualRowSetContent(
CommandType.COMMAND, "SELECT \"PROD\".\"ID\" FROM \"query products\" AS \"PROD\"",
CommandType.COMMAND, "SELECT \"ID\" FROM \"products\"" );
verifyEqualRowSetContent(
CommandType.COMMAND, "SELECT \"PROD\".* FROM \"query products\" AS \"PROD\"",
CommandType.QUERY,QUERY_PRODUCTS);
}
// --------------------------------------------------------------------------------------------------------
/** verifies that aliases for inner queries work as expected
*/
@Test
public void checkNameCollisions()
{
// create a query with a name which is used by a table
boolean caughtExpected = false;
try
{
m_database.getDatabase().getDataSource().createQuery( "products", "SELECT * FROM \"products\"" );
}
catch ( WrappedTargetException e ) { caughtExpected = true; }
catch ( IllegalArgumentException e ) {}
catch ( ElementExistException e ) { caughtExpected = true; }
assertTrue( "creating queries with the name of an existing table should not be possible",
caughtExpected );
// create a table with a name which is used by a query
final HsqlTableDescriptor table = new HsqlTableDescriptor( QUERY_PRODUCTS,
new HsqlColumnDescriptor[] {
new HsqlColumnDescriptor( "ID", "INTEGER" ),
new HsqlColumnDescriptor( "Name", "VARCHAR(50)" ) } );
caughtExpected = false;
try
{
m_database.getDatabase().createTableInSDBCX( table );
}
catch ( SQLException e ) { caughtExpected = true; }
catch ( ElementExistException ex ) { }
assertTrue( "creating tables with the name of an existing query should not be possible",
caughtExpected );
}
// --------------------------------------------------------------------------------------------------------
@Test
public void checkCyclicReferences() throws ElementExistException, WrappedTargetException, IllegalArgumentException
{
// some queries which create a cycle in the sub query tree
m_database.getDatabase().getDataSource().createQuery( "orders level 1", "SELECT * FROM \"orders level 0\"" );
m_database.getDatabase().getDataSource().createQuery( "orders level 2", "SELECT * FROM \"orders level 1\"" );
m_database.getDatabase().getDataSource().createQuery( "orders level 3", "SELECT * FROM \"orders level 2\"" );
m_database.getDatabase().getDataSource().createQuery( "orders level 0", "SELECT * FROM \"orders level 3\"" );
final RowSet rowSet = m_database.getDatabase().createRowSet( CommandType.QUERY, "orders level 0" );
boolean caughtExpected = false;
try { rowSet.execute(); }
catch ( SQLException e ) { caughtExpected = ( e.ErrorCode == -com.sun.star.sdb.ErrorCondition.PARSER_CYCLIC_SUB_QUERIES ); }
assertTrue( "executing a query with cyclic nested sub queries should fail!", caughtExpected );
}
// --------------------------------------------------------------------------------------------------------
@Test
public void checkStatementQiQSupport()
{
try
{
final XStatement statement = m_database.getConnection().createStatement();
final XResultSet resultSet = statement.executeQuery( "SELECT * FROM \"query products\"" );
assertTrue( "Result Set is null", resultSet != null );
}
catch( SQLException e )
{
fail( "SDB level statements do not allow for queries in queries" );
}
}
}