/*! ******************************************************************************
*
* Pentaho Data Integration
*
* Copyright (C) 2002-2017 by Pentaho : http://www.pentaho.com
*
*******************************************************************************
*
* Licensed 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.pentaho.di.core.database;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.mockito.Mockito.spy;
import static org.mockito.Mockito.when;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.mockito.Mockito;
import org.mockito.invocation.InvocationOnMock;
import org.mockito.stubbing.Answer;
import org.pentaho.di.core.Const;
import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.exception.KettleDatabaseException;
import org.pentaho.di.core.logging.LoggingObjectInterface;
import org.pentaho.di.core.logging.LoggingObjectType;
import org.pentaho.di.core.logging.SimpleLoggingObject;
import org.pentaho.di.core.row.RowMeta;
import org.pentaho.di.core.row.RowMetaInterface;
import org.pentaho.di.core.row.ValueMetaInterface;
import org.pentaho.di.core.row.value.ValueMetaInteger;
import org.pentaho.di.trans.TransMeta;
/**
* Try to test database functionality using a hypersonic database. This is just a small fraction of the functionality,
* but could already trap a few problems.
*
* @author Sven Boden
*/
public class DatabaseTest {
private static final int COUNT_OF_STEPS_WITH_DATABASE = 10;
private LoggingObjectInterface log = new SimpleLoggingObject( "junit", LoggingObjectType.GENERAL, null );
DatabaseMeta databaseMysqlMeta = new DatabaseMeta( "junit_db", "Mysql", "JDBC", null, "stub:stub", null, null, null );
private RowMetaInterface params = Mockito.mock( RowMetaInterface.class );
private Object[] data = new Object[] {};
private PreparedStatement ps;
@BeforeClass
public static void setUpBeforeClass() throws Exception {
KettleEnvironment.init();
}
@Before
public void before() throws SQLException {
ps = Mockito.mock( PreparedStatement.class );
Mockito.when( ps.getMaxRows() ).thenReturn( 13 );
}
/**
* Test that mysql fetch size is not set if fetch size more than
* max rows ResultSet can return.
*
* @throws KettleDatabaseException
* @throws SQLException
*/
@Test
public void testOpenQueryFetchSizeNotSet() throws KettleDatabaseException, SQLException {
Database db = Mockito.spy( new MockDatabase( log, databaseMysqlMeta, 5 ) );
try {
db.openQuery( ps, params, data );
} catch ( KettleDatabaseException e ) {
// it is OK since we do not using real connection
}
Mockito.verify( ps, Mockito.times( 0 ) ).setFetchSize( Mockito.anyInt() );
}
/**
* Test that non-mysql databases can set fetch size.
*
* @throws SQLException
*/
@Test
public void testOpenQueryFetchSizeSet() throws SQLException {
DatabaseMeta emptyMeta = new DatabaseMeta();
Database db = Mockito.spy( new MockDatabase( log, emptyMeta, 5 ) );
try {
db.openQuery( ps, params, data );
} catch ( KettleDatabaseException e ) {
// it is OK since we do not using real connection
}
Mockito.verify( ps, Mockito.times( 1 ) ).setFetchSize( Mockito.anyInt() );
}
/**
* Test that if for mysql variant if max rows more than fetch size
* we do set fetch size
* @throws SQLException
*/
@Test
public void testOpenQuerySetMySqlFetchSize() throws SQLException {
Database db = Mockito.spy( new MockDatabase( log, databaseMysqlMeta, 5 ) );
PreparedStatement ps = Mockito.mock( PreparedStatement.class );
Mockito.when( ps.getMaxRows() ).thenReturn( Const.FETCH_SIZE + 1 );
try {
db.openQuery( ps, params, data );
} catch ( KettleDatabaseException e ) {
// it is OK since we do not using real connection
}
Mockito.verify( ps, Mockito.times( 1 ) ).setFetchSize( Mockito.anyInt() );
}
@Test
public void testDatabaseCasing() throws Exception {
String tableName = "mIxCaSiNG";
Database db = setupDatabase();
db.connect();
RowMetaInterface rm = new RowMeta();
ValueMetaInterface[] valuesMeta =
{ new ValueMetaInteger( "ID" ),
new ValueMetaInteger( "DLR_CD" ), };
for ( int i = 0; i < valuesMeta.length; i++ ) {
valuesMeta[i].setLength( 8 );
valuesMeta[i].setPrecision( 0 );
rm.addValueMeta( valuesMeta[i] );
}
String createStatement = db.getCreateTableStatement( tableName, rm, null, false, null, true );
db.execStatement( createStatement );
// Make sure that the tablename is of mixed case
assertFalse( tableName.equals( tableName.toLowerCase() ) );
assertTrue( db.checkTableExists( tableName ) );
assertEquals( false, db.checkTableExists( "unknown" ) );
// We're testing here whether tables names are case insensitive.
// If this would fail, it can either be a problem with PDI or
// be a problem with a new H2 JDBC driver.
assertTrue( db.checkTableExists( tableName.toLowerCase() ) );
db.disconnect();
}
@Test
public void testQuoting() throws Exception {
Database database = setupDatabase();
DatabaseMeta dbInfo = database.getDatabaseMeta();
database.connect();
assertNull( dbInfo.quoteField( null ) );
assertEquals( "table1", dbInfo.quoteField( "table1" ) );
assertEquals( "\"table 1\"", dbInfo.quoteField( "table 1" ) );
assertEquals( "\"table-1\"", dbInfo.quoteField( "table-1" ) );
assertEquals( "\"table+1\"", dbInfo.quoteField( "table+1" ) );
assertEquals( "\"table.1\"", dbInfo.quoteField( "table.1" ) );
assertNull( dbInfo.getQuotedSchemaTableCombination( null, null ) );
assertEquals( "table1", dbInfo.getQuotedSchemaTableCombination( null, "table1" ) );
assertEquals( "\"table 1\"", dbInfo.getQuotedSchemaTableCombination( null, "table 1" ) );
assertEquals( "\"table-1\"", dbInfo.getQuotedSchemaTableCombination( null, "table-1" ) );
assertEquals( "\"table+1\"", dbInfo.getQuotedSchemaTableCombination( null, "table+1" ) );
assertEquals( "\"table.1\"", dbInfo.getQuotedSchemaTableCombination( null, "table.1" ) );
assertEquals( "\"schema1\".\"null\"", dbInfo.getQuotedSchemaTableCombination( "schema1", null ) );
assertEquals( "\"schema1\".\"table1\"", dbInfo.getQuotedSchemaTableCombination( "schema1", "table1" ) );
// These 2 are maybe dodgy, but current behaviour
assertEquals( "\"schema 1\".\"table 1\"", dbInfo.getQuotedSchemaTableCombination( "schema 1", "table 1" ) );
assertEquals( "\"schema1\".\"table1\"", dbInfo.getQuotedSchemaTableCombination( "schema1", "\"table1\"" ) );
database.disconnect();
}
@Test
public void testEquals() throws Exception {
Database db1 = setupDatabase();
Database db2 = setupDatabase2();
assertFalse( db1.equals( db2 ) );
}
@Test
public void testBatchCommit() throws Exception {
String tableName = "CommitTest";
Database db = setupDatabase();
db.connect();
RowMetaInterface rm = new RowMeta();
ValueMetaInterface[] valuesMeta =
{ new ValueMetaInteger( "ID" ), new ValueMetaInteger( "VALUE" ), };
for ( int i = 0; i < valuesMeta.length; i++ ) {
valuesMeta[i].setLength( 8 );
valuesMeta[i].setPrecision( 0 );
rm.addValueMeta( valuesMeta[i] );
}
String createStatement = db.getCreateTableStatement( tableName, rm, null, false, null, true );
db.execStatement( createStatement );
int insertSize = 3;
db.setCommit( insertSize - 1 );
fillDbInBatch( tableName, db, insertSize );
db.truncateTable( tableName );
insertSize = 3;
db.setCommit( insertSize );
fillDbInBatch( tableName, db, insertSize );
db.disconnect();
}
@Test
public void testNonPooledAndPooledNormalConnect() throws Exception {
ExecutorService executorService = Executors.newFixedThreadPool( COUNT_OF_STEPS_WITH_DATABASE );
List<Callable<Connection>> tasks = new ArrayList<Callable<Connection>>();
for ( int i = 0; i < COUNT_OF_STEPS_WITH_DATABASE; i++ ) {
tasks.add(
new Callable<Connection>() {
@Override
public Connection call() throws Exception {
Database db = setupDatabase();
db.normalConnect( null );
return db.getConnection();
}
}
);
tasks.add(
new Callable<Connection>() {
@Override
public Connection call() throws Exception {
Database db2 = setupPoolingDatabaseWOConnect();
db2.normalConnect( null );
return db2.getConnection();
}
}
);
}
List<Future<Connection>> futures = executorService.invokeAll( tasks );
for ( Future<Connection> future : futures ) {
assertNotNull( future.get() );
}
}
public Database setupPoolingDatabaseWOConnect() throws Exception {
// Create a new transformation...
TransMeta transMeta = new TransMeta();
transMeta.setName( "transname" );
DatabaseMeta dbInfo = new DatabaseMeta(
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"
+ "<connection>\n"
+ "<name>db_pool</name>\n"
+ "<server>127.0.0.1</server>\n"
+ "<type>H2</type>\n"
+ "<access>Native</access>\n"
+ "<database>mem:db</database>\n"
+ "<port></port>\n"
+ "<username>sa</username>\n"
+ "<password></password>\n"
+ "<attributes>\n"
+ "<attribute><code>INITIAL_POOL_SIZE</code><attribute>5</attribute></attribute>\n"
+ "<attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>\n"
+ "<attribute><code>MAXIMUM_POOL_SIZE</code><attribute>10</attribute></attribute>\n"
+ "<attribute><code>USE_POOLING</code><attribute>Y</attribute></attribute>\n"
+ "</attributes>\n"
+ "</connection>" );
// Add the database connections
transMeta.addDatabase( dbInfo );
Database database = new Database( transMeta, dbInfo );
return database;
}
public Database setupDatabase() throws Exception {
// Create a new transformation...
TransMeta transMeta = new TransMeta();
transMeta.setName( "transname" );
// Add the database connections
DatabaseMeta databaseMeta = new DatabaseMeta(
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
+ "<connection>"
+ "<name>db</name>"
+ "<server>127.0.0.1</server>"
+ "<type>H2</type>"
+ "<access>Native</access>"
+ "<database>mem:db</database>"
+ "<port></port>"
+ "<username>sa</username>"
+ "<password></password>"
+ "</connection>" );
transMeta.addDatabase( databaseMeta );
Database database = new Database( transMeta, databaseMeta );
return database;
}
public Database setupDatabase2() throws Exception {
// Create a new transformation...
TransMeta transMeta = new TransMeta();
transMeta.setName( "transname" );
// Add the database connections
DatabaseMeta databaseMeta = new DatabaseMeta(
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
+ "<connection>"
+ "<name>db2</name>"
+ "<server>10.12.4.1</server>"
+ "<type>H2</type>"
+ "<access>Native</access>"
+ "<database>mem:db2</database>"
+ "<port></port>"
+ "<username>sa</username>"
+ "<password></password>"
+ "</connection>" );
transMeta.addDatabase( databaseMeta );
Database database = new Database( transMeta, databaseMeta );
return database;
}
private void fillDbInBatch( String tableName, Database db, int insertSize ) throws SQLException,
KettleDatabaseException {
String insert = "INSERT INTO " + tableName + " VALUES( ?, ? )";
PreparedStatement ps = db.getConnection().prepareStatement( insert );
final PreparedStatement psMocked = spy( ps );
// Need mock to check executing with empty batch
// some jdbc drivers verify batch is not empty some throw sqlexception
// or in really neglected cases NullPointerException
when( psMocked.executeBatch() ).thenAnswer( new Answer<Object>() {
@Override
public Object answer( InvocationOnMock invocation ) throws Throwable {
int[] succ = (int[]) invocation.callRealMethod();
if ( succ.length == 0 ) {
throw new SQLException( "Batch is empty" );
}
return succ;
}
} );
for ( int i = 1; i <= insertSize; i++ ) {
psMocked.setInt( 1, i );
psMocked.setInt( 2, i );
db.insertRow( psMocked, true, true );
}
db.emptyAndCommit( psMocked, true );
}
class MockDatabase extends Database {
DatabaseMetaData md = Mockito.mock( DatabaseMetaData.class );
int version;
public MockDatabase( LoggingObjectInterface parentObject, DatabaseMeta databaseMeta, int version ) {
super( parentObject, databaseMeta );
this.version = version;
Mockito.when( md.getDriverMajorVersion() ).thenReturn( version );
}
@Override
public DatabaseMetaData getDatabaseMetaData() throws KettleDatabaseException {
return md;
}
}
}