/*! ******************************************************************************
*
* 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 static org.junit.Assert.assertTrue;
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 InfiniDbDatabaseMetaTest extends MySQLDatabaseMetaTest {
InfiniDbDatabaseMeta nativeMeta, odbcMeta;
@Before
public void setupBefore() {
nativeMeta = new InfiniDbDatabaseMeta();
nativeMeta.setAccessType( DatabaseMeta.TYPE_ACCESS_NATIVE );
odbcMeta = new InfiniDbDatabaseMeta();
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( 3306, nativeMeta.getDefaultDatabasePort() );
assertEquals( -1, odbcMeta.getDefaultDatabasePort() );
assertTrue( nativeMeta.supportsAutoInc() );
assertEquals( 1, nativeMeta.getNotFoundTK( true ) );
assertEquals( 0, nativeMeta.getNotFoundTK( false ) );
assertEquals( "org.gjt.mm.mysql.Driver", nativeMeta.getDriverClass() );
assertEquals( "sun.jdbc.odbc.JdbcOdbcDriver", odbcMeta.getDriverClass() );
assertEquals( "jdbc:odbc:FOO", odbcMeta.getURL( "IGNORED", "IGNORED", "FOO" ) );
assertEquals( "jdbc:mysql://FOO:BAR/WIBBLE", nativeMeta.getURL( "FOO", "BAR", "WIBBLE" ) );
assertEquals( "jdbc:mysql://FOO/WIBBLE", nativeMeta.getURL( "FOO", "", "WIBBLE" ) );
assertEquals( "&", nativeMeta.getExtraOptionSeparator() );
assertEquals( "?", nativeMeta.getExtraOptionIndicator() );
assertFalse( nativeMeta.supportsTransactions() );
assertFalse( nativeMeta.supportsBitmapIndex() );
assertTrue( nativeMeta.supportsViews() );
assertFalse( nativeMeta.supportsSynonyms() );
assertArrayEquals( new String[] { "ADD", "ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC", "ASENSITIVE", "BEFORE", "BETWEEN",
"BIGINT", "BINARY", "BLOB", "BOTH", "BY", "CALL", "CASCADE", "CASE", "CHANGE", "CHAR", "CHARACTER", "CHECK",
"COLLATE", "COLUMN", "CONDITION", "CONNECTION", "CONSTRAINT", "CONTINUE", "CONVERT", "CREATE", "CROSS",
"CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DATABASE", "DATABASES",
"DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE", "DAY_SECOND", "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DELAYED",
"DELETE", "DESC", "DESCRIBE", "DETERMINISTIC", "DISTINCT", "DISTINCTROW", "DIV", "DOUBLE", "DROP", "DUAL", "EACH",
"ELSE", "ELSEIF", "ENCLOSED", "ESCAPED", "EXISTS", "EXIT", "EXPLAIN", "FALSE", "FETCH", "FLOAT", "FOR", "FORCE",
"FOREIGN", "FROM", "FULLTEXT", "GOTO", "GRANT", "GROUP", "HAVING", "HIGH_PRIORITY", "HOUR_MICROSECOND",
"HOUR_MINUTE", "HOUR_SECOND", "IF", "IGNORE", "IN", "INDEX", "INFILE", "INNER", "INOUT", "INSENSITIVE", "INSERT",
"INT", "INTEGER", "INTERVAL", "INTO", "IS", "ITERATE", "JOIN", "KEY", "KEYS", "KILL", "LEADING", "LEAVE", "LEFT",
"LIKE", "LIMIT", "LINES", "LOAD", "LOCALTIME", "LOCALTIMESTAMP", "LOCATE", "LOCK", "LONG", "LONGBLOB", "LONGTEXT",
"LOOP", "LOW_PRIORITY", "MATCH", "MEDIUMBLOB", "MEDIUMINT", "MEDIUMTEXT", "MIDDLEINT", "MINUTE_MICROSECOND",
"MINUTE_SECOND", "MOD", "MODIFIES", "NATURAL", "NOT", "NO_WRITE_TO_BINLOG", "NULL", "NUMERIC", "ON", "OPTIMIZE",
"OPTION", "OPTIONALLY", "OR", "ORDER", "OUT", "OUTER", "OUTFILE", "POSITION", "PRECISION", "PRIMARY", "PROCEDURE",
"PURGE", "READ", "READS", "REAL", "REFERENCES", "REGEXP", "RENAME", "REPEAT", "REPLACE", "REQUIRE", "RESTRICT",
"RETURN", "REVOKE", "RIGHT", "RLIKE", "SCHEMA", "SCHEMAS", "SECOND_MICROSECOND", "SELECT", "SENSITIVE",
"SEPARATOR", "SET", "SHOW", "SMALLINT", "SONAME", "SPATIAL", "SPECIFIC", "SQL", "SQLEXCEPTION", "SQLSTATE",
"SQLWARNING", "SQL_BIG_RESULT", "SQL_CALC_FOUND_ROWS", "SQL_SMALL_RESULT", "SSL", "STARTING", "STRAIGHT_JOIN",
"TABLE", "TERMINATED", "THEN", "TINYBLOB", "TINYINT", "TINYTEXT", "TO", "TRAILING", "TRIGGER", "TRUE", "UNDO",
"UNION", "UNIQUE", "UNLOCK", "UNSIGNED", "UPDATE", "USAGE", "USE", "USING", "UTC_DATE", "UTC_TIME",
"UTC_TIMESTAMP", "VALUES", "VARBINARY", "VARCHAR", "VARCHARACTER", "VARYING", "WHEN", "WHERE", "WHILE", "WITH",
"WRITE", "XOR", "YEAR_MONTH", "ZEROFILL" }, nativeMeta.getReservedWords() );
assertEquals( "`", nativeMeta.getStartQuote() );
assertEquals( "`", nativeMeta.getEndQuote() );
assertTrue( nativeMeta.needsToLockAllTables() );
assertEquals( "http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html", nativeMeta.getExtraOptionsHelpText() );
assertArrayEquals( new String[] { "mysql-connector-java-3.1.14-bin.jar" }, nativeMeta.getUsedLibraries() ); // this is way wrong
assertTrue( nativeMeta.isSystemTable( "sysTest" ) );
assertTrue( nativeMeta.isSystemTable( "dtproperties" ) );
assertFalse( nativeMeta.isSystemTable( "SysTest" ) );
assertFalse( nativeMeta.isSystemTable( "dTproperties" ) );
assertFalse( nativeMeta.isSystemTable( "Testsys" ) );
assertTrue( nativeMeta.isMySQLVariant() );
assertFalse( nativeMeta.releaseSavepoint() );
assertTrue( nativeMeta.supportsErrorHandlingOnBatchUpdates() );
assertFalse( nativeMeta.isRequiringTransactionsOnQueries() );
assertTrue( nativeMeta.supportsRepository() );
}
@Test
public void testSQLStatements() {
assertEquals( " LIMIT 15", nativeMeta.getLimitClause( 15 ) );
assertEquals( "SELECT * FROM FOO LIMIT 0", nativeMeta.getSQLQueryFields( "FOO" ) );
assertEquals( "SELECT * FROM FOO LIMIT 0", nativeMeta.getSQLTableExists( "FOO" ) );
assertEquals( "SELECT FOO FROM BAR LIMIT 0", nativeMeta.getSQLQueryColumnFields( "FOO", "BAR" ) );
assertEquals( "ALTER TABLE FOO ADD BAR DATETIME",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaDate( "BAR" ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR DATETIME",
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 INT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 0, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR INT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 5, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR DOUBLE",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 10, 3 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR DOUBLE",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 10, 3 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR DECIMAL(21, 4)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 21, 4 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR MEDIUMTEXT",
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 DOUBLE",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", 10, -7 ), "", false, "", false ) ); // Bug here - invalid SQL
assertEquals( "ALTER TABLE FOO ADD BAR DECIMAL(22, 7)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 22, 7 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR DOUBLE",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaNumber( "BAR", -10, 7 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR DOUBLE",
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 BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaInteger( "BAR" ), "BAR", true, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY",
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 TINYTEXT",
nativeMeta.getModifyColumnStatement( "FOO", new ValueMetaString( "BAR" ), "", false, "", true ) );
odbcMeta.setSupportsBooleanDataType( true ); // some subclass of the MSSQL meta probably ...
assertEquals( "ALTER TABLE FOO ADD BAR BOOLEAN",
odbcMeta.getAddColumnStatement( "FOO", new ValueMetaBoolean( "BAR" ), "", false, "", false ) );
odbcMeta.setSupportsBooleanDataType( false );
assertEquals( "ALTER TABLE FOO ADD BAR INT",
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 BIGINT NOT NULL PRIMARY KEY",
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 DECIMAL(22)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBigNumber( "BAR", 22, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR CHAR(1)",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaString( "BAR", 1, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR LONGTEXT",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaString( "BAR", 16777250, 0 ), "", false, "", false ) );
assertEquals( "ALTER TABLE FOO ADD BAR LONGBLOB",
nativeMeta.getAddColumnStatement( "FOO", new ValueMetaBinary( "BAR", 16777250, 0 ), "", false, "", false ) );
assertEquals( "LOCK TABLES FOO WRITE, BAR WRITE;" + lineSep,
nativeMeta.getSQLLockTables( new String[] { "FOO", "BAR" } ) );
assertEquals( "UNLOCK TABLES", nativeMeta.getSQLUnlockTables( new String[] { } ) );
assertEquals( "insert into FOO(FOOKEY, FOOVERSION) values (1, 1)", nativeMeta.getSQLInsertAutoIncUnknownDimensionRow( "FOO", "FOOKEY", "FOOVERSION" ) );
}
}