/*! ******************************************************************************
*
* Pentaho Data Integration
*
* Copyright (C) 2002-2016 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.assertArrayEquals;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import java.util.Properties;
import org.junit.Before;
import org.junit.Test;
import org.pentaho.di.core.row.value.ValueMetaBigNumber;
import org.pentaho.di.core.row.value.ValueMetaBinary;
import org.pentaho.di.core.row.value.ValueMetaBoolean;
import org.pentaho.di.core.row.value.ValueMetaDate;
import org.pentaho.di.core.row.value.ValueMetaInteger;
import org.pentaho.di.core.row.value.ValueMetaInternetAddress;
import org.pentaho.di.core.row.value.ValueMetaNumber;
import org.pentaho.di.core.row.value.ValueMetaString;
import org.pentaho.di.core.row.value.ValueMetaTimestamp;
public class GenericDatabaseMetaTest {
GenericDatabaseMeta nativeMeta, odbcMeta;
@Before
public void setupBefore() {
nativeMeta = new GenericDatabaseMeta();
nativeMeta.setAccessType( DatabaseMeta.TYPE_ACCESS_NATIVE );
odbcMeta = new GenericDatabaseMeta();
odbcMeta.setAccessType( DatabaseMeta.TYPE_ACCESS_ODBC );
}
@Test
public void testSettings() throws Exception {
assertArrayEquals( new int[] { DatabaseMeta.TYPE_ACCESS_NATIVE, DatabaseMeta.TYPE_ACCESS_ODBC, DatabaseMeta.TYPE_ACCESS_JNDI },
nativeMeta.getAccessTypeList() );
assertEquals( 1, nativeMeta.getNotFoundTK( true ) );
assertEquals( 0, nativeMeta.getNotFoundTK( false ) );
Properties attrs = new Properties();
attrs.put( GenericDatabaseMeta.ATRRIBUTE_CUSTOM_DRIVER_CLASS, "foo.bar.wibble" );
attrs.put( GenericDatabaseMeta.ATRRIBUTE_CUSTOM_URL, "jdbc:foo:bar://foodb" );
nativeMeta.setAttributes( attrs );
assertEquals( "foo.bar.wibble", nativeMeta.getDriverClass() );
assertEquals( "sun.jdbc.odbc.JdbcOdbcDriver", odbcMeta.getDriverClass() );
assertEquals( "jdbc:foo:bar://foodb", nativeMeta.getURL( "NOT", "GOINGTO", "BEUSED" ) );
assertEquals( "jdbc:odbc:FOO", odbcMeta.getURL( "NOT", "USED", "FOO" ) );
assertFalse( nativeMeta.isFetchSizeSupported() );
assertFalse( nativeMeta.supportsBitmapIndex() );
assertFalse( nativeMeta.supportsPreparedStatementMetadataRetrieval() );
assertArrayEquals( new String[] { }, nativeMeta.getUsedLibraries() );
assertFalse( nativeMeta.supportsPreparedStatementMetadataRetrieval() );
}
@Test
public void testSQLStatements() {
assertEquals( "DELETE FROM FOO", nativeMeta.getTruncateTableStatement( "FOO" ) );
assertEquals( "SELECT * FROM FOO", nativeMeta.getSQLQueryFields( "FOO" ) );
assertEquals( "SELECT 1 FROM FOO", nativeMeta.getSQLTableExists( "FOO" ) );
assertEquals( "ALTER TABLE FOO ADD BAR TIMESTAMP",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaDate( "BAR" ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR TIMESTAMP",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaTimestamp( "BAR" ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR CHAR(1)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBoolean( "BAR" ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR BIGINT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 10, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR BIGINT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 10, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR BIGINT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaInteger( "BAR", 10, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR DOUBLE PRECISION",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 0, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR INTEGER",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 5, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR NUMERIC(10, 3)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 10, 3 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR NUMERIC(10, 3)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 10, 3 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR NUMERIC(21, 4)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 21, 4 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR TEXT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaString( "BAR", nativeMeta.getMaxVARCHARLength() + 2, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR VARCHAR(15)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaString( "BAR", 15, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR BIGINT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 10, -7 ), "", false, "", false ) ); // Bug here - invalid SQL
assertEquals( "ALTER TABLE FOO ADD BAR NUMERIC(22, 7)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 22, 7 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR DOUBLE PRECISION",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", -10, 7 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR NUMERIC(5, 7)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 5, 7 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR UNKNOWN",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaInternetAddress( "BAR" ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR BIGSERIAL",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaInteger( "BAR" ), "BAR", true, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR BIGSERIAL",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 26, 8 ), "BAR", true, "", false ) );
String lineSep = System.getProperty( "line.separator" );
assertEquals( "ALTER TABLE FOO DROP BAR" + lineSep,
nativeMeta.getDropColumnStatement( "FOO", new ValueMetaString( "BAR", 15, 0 ), "", false, "", true ) );
assertEquals( "ALTER TABLE FOO MODIFY BAR VARCHAR(15)",
nativeMeta.getModifyColumnStatement( "FOO", new ValueMetaString( "BAR", 15, 0 ), "", false, "", true ) );
assertEquals( "ALTER TABLE FOO MODIFY BAR VARCHAR()",
nativeMeta.getModifyColumnStatement( "FOO", new ValueMetaString( "BAR" ), "", false, "", true ) ); // I think this is a bug ..
assertEquals( "ALTER TABLE FOO ADD BAR SMALLINT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaInteger( "BAR", 4, 0 ), "", true, "", false ) );
// do a boolean check
odbcMeta.setSupportsBooleanDataType( true );
assertEquals( "ALTER TABLE FOO ADD BAR BOOLEAN",
odbcMeta.getAddColumnStatement( "FOO", new ValueMetaBoolean( "BAR" ), "", false, "", false ) );
odbcMeta.setSupportsBooleanDataType( false );
assertEquals( "ALTER TABLE FOO ADD BAR BIGSERIAL",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaInteger( "BAR" ), "BAR", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR BIGINT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 10, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR NUMERIC(22, 0)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 22, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR VARCHAR(1)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaString( "BAR", 1, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR TEXT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaString( "BAR", 16777250, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR UNKNOWN",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBinary( "BAR", 16777250, 0 ), "", false, "", false ) );
assertEquals( "insert into FOO(FOOVERSION) values (1)", nativeMeta.getSQLInsertAutoIncUnknownDimensionRow( "FOO", "FOOKEY", "FOOVERSION" ) );
}
}