Search in sources :

Example 16 with ValueMetaTimestamp

use of org.pentaho.di.core.row.value.ValueMetaTimestamp in project pentaho-kettle by pentaho.

the class Exasol4DatabaseMetaTest method testSQLStatements.

@Test
public void testSQLStatements() {
    assertEquals(" WHERE ROWNUM <= 15", nativeMeta.getLimitClause(15));
    assertEquals("SELECT /*+FIRST_ROWS*/ * FROM FOO WHERE 1=0", nativeMeta.getSQLQueryFields("FOO"));
    assertEquals("SELECT /*+FIRST_ROWS*/ * FROM FOO WHERE 1=0", nativeMeta.getSQLTableExists("FOO"));
    assertEquals("SELECT /*+FIRST_ROWS*/ FOO FROM BAR WHERE 1=0", nativeMeta.getSQLQueryColumnFields("FOO", "BAR"));
    assertEquals("SELECT /*+FIRST_ROWS*/ FOO FROM BAR WHERE 1=0", nativeMeta.getSQLColumnExists("FOO", "BAR"));
    String lineSep = System.getProperty("line.separator");
    assertEquals("ALTER TABLE FOO DROP COLUMN BAR" + lineSep, nativeMeta.getDropColumnStatement("FOO", new ValueMetaString("BAR", 15, 0), "", false, "", true));
    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 BOOLEAN ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBoolean("BAR"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR DECIMAL ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR DECIMAL ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBigNumber("BAR"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR DECIMAL(15) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 15, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR DECIMAL(15) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBigNumber("BAR", 15, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR DECIMAL(15, 5) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 15, 5), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR DECIMAL(15, 5) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBigNumber("BAR", 15, 5), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR INTEGER ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInteger("BAR"), "", 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 VARCHAR(2000000) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaString("BAR", nativeMeta.getMaxVARCHARLength() + 15, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR UNKNOWN ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInternetAddress("BAR"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( BAR BIGINT NOT NULL PRIMARY KEY ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInteger("BAR"), "BAR", false, "", false));
}
Also used : ValueMetaTimestamp(org.pentaho.di.core.row.value.ValueMetaTimestamp) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaNumber(org.pentaho.di.core.row.value.ValueMetaNumber) ValueMetaBigNumber(org.pentaho.di.core.row.value.ValueMetaBigNumber) ValueMetaInternetAddress(org.pentaho.di.core.row.value.ValueMetaInternetAddress) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) Test(org.junit.Test)

Example 17 with ValueMetaTimestamp

use of org.pentaho.di.core.row.value.ValueMetaTimestamp in project pentaho-kettle by pentaho.

the class HypersonicDatabaseMetaTest method testSQLStatements.

@Test
public void testSQLStatements() throws Exception {
    HypersonicDatabaseMeta nativeMeta = new HypersonicDatabaseMeta();
    nativeMeta.setAccessType(DatabaseMeta.TYPE_ACCESS_NATIVE);
    HypersonicDatabaseMeta odbcMeta = new HypersonicDatabaseMeta();
    odbcMeta.setAccessType(DatabaseMeta.TYPE_ACCESS_ODBC);
    assertEquals("TRUNCATE TABLE 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 LONGVARCHAR", 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", // Bug here - invalid SQL
    nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 10, -7), "", false, "", false));
    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 BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0, INCREMENT BY 1) PRIMARY KEY", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInteger("BAR"), "BAR", true, "", false));
    assertEquals("ALTER TABLE FOO ADD BAR BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0, INCREMENT BY 1) 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 ALTER COLUMN BAR VARCHAR(15)", nativeMeta.getModifyColumnStatement("FOO", new ValueMetaString("BAR", 15, 0), "", false, "", true));
    assertEquals("ALTER TABLE FOO ALTER COLUMN BAR VARCHAR()", // I think this is a bug ..
    nativeMeta.getModifyColumnStatement("FOO", new ValueMetaString("BAR"), "", false, "", true));
    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 BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0, INCREMENT BY 1) 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 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 LONGVARCHAR", 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(FOOKEY, FOOVERSION) values (0, 1)", nativeMeta.getSQLInsertAutoIncUnknownDimensionRow("FOO", "FOOKEY", "FOOVERSION"));
}
Also used : ValueMetaTimestamp(org.pentaho.di.core.row.value.ValueMetaTimestamp) ValueMetaBinary(org.pentaho.di.core.row.value.ValueMetaBinary) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaNumber(org.pentaho.di.core.row.value.ValueMetaNumber) ValueMetaBigNumber(org.pentaho.di.core.row.value.ValueMetaBigNumber) ValueMetaInternetAddress(org.pentaho.di.core.row.value.ValueMetaInternetAddress) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) Test(org.junit.Test)

Example 18 with ValueMetaTimestamp

use of org.pentaho.di.core.row.value.ValueMetaTimestamp in project pentaho-kettle by pentaho.

the class MonetDBDatabaseMetaTest method testGetFieldDefinition.

@Test
public void testGetFieldDefinition() {
    assertEquals("FOO TIMESTAMP", nativeMeta.getFieldDefinition(new ValueMetaDate("FOO"), "", "", false, true, false));
    assertEquals("TIMESTAMP", nativeMeta.getFieldDefinition(new ValueMetaTimestamp("FOO"), "", "", false, false, false));
    // Simple hack to prevent duplication of code. Checking the case of supported boolean type
    // both supported and unsupported. Should return BOOLEAN if supported, or CHAR(1) if not.
    String[] typeCk = new String[] { "CHAR(1)", "BOOLEAN", "CHAR(1)" };
    int i = (nativeMeta.supportsBooleanDataType() ? 1 : 0);
    assertEquals(typeCk[i], nativeMeta.getFieldDefinition(new ValueMetaBoolean("FOO"), "", "", false, false, false));
    odbcMeta.setSupportsBooleanDataType(!(odbcMeta.supportsBooleanDataType()));
    assertEquals(typeCk[i + 1], odbcMeta.getFieldDefinition(new ValueMetaBoolean("FOO"), "", "", false, false, false));
    odbcMeta.setSupportsBooleanDataType(!(odbcMeta.supportsBooleanDataType()));
    assertEquals("SERIAL", nativeMeta.getFieldDefinition(new ValueMetaBigNumber("FOO", 8, 0), "", "FOO", true, false, false));
    assertEquals("BIGINT", nativeMeta.getFieldDefinition(new ValueMetaNumber("FOO", 10, 0), "FOO", "", false, false, false));
    assertEquals("BIGINT", nativeMeta.getFieldDefinition(new ValueMetaInteger("FOO", 8, 0), "", "FOO", false, false, false));
    // integer types ( precision == 0 )
    assertEquals("BIGINT", nativeMeta.getFieldDefinition(new ValueMetaInteger("FOO", 8, 0), "", "", false, false, false));
    assertEquals("BIGINT", nativeMeta.getFieldDefinition(new ValueMetaNumber("FOO", 10, 0), "", "", false, false, false));
    assertEquals("DECIMAL(19)", nativeMeta.getFieldDefinition(new ValueMetaBigNumber("FOO", 19, 0), "", "", false, false, false));
    assertEquals("DOUBLE", nativeMeta.getFieldDefinition(new ValueMetaNumber("FOO", 8, 0), "", "", false, false, false));
    // Numerics with precisions
    assertEquals("DECIMAL(19, 5)", nativeMeta.getFieldDefinition(new ValueMetaBigNumber("FOO", 19, 5), "", "", false, false, false));
    assertEquals("DECIMAL(19)", nativeMeta.getFieldDefinition(new ValueMetaBigNumber("FOO", 19, -5), "", "", false, false, false));
    assertEquals("DOUBLE", nativeMeta.getFieldDefinition(new ValueMetaBigNumber("FOO", 11, 5), "", "", false, false, false));
    // String Types
    assertEquals("VARCHAR(10)", nativeMeta.getFieldDefinition(new ValueMetaString("FOO", 10, 0), "", "", false, false, false));
    // This next one is a bug:
    // getMaxVARCHARLength = Integer.MAX_VALUE,
    // if statement for CLOB trips if length > getMaxVARCHARLength()
    // length is of type int - so this could never happen
    assertEquals("VARCHAR()", nativeMeta.getFieldDefinition(new ValueMetaString("FOO", nativeMeta.getMaxVARCHARLength() + 1, 0), "", "", false, false, false));
    assertEquals("VARCHAR()", // should end up with (100) if "safeMode = true"
    nativeMeta.getFieldDefinition(new ValueMetaString("FOO", -2, 0), "", "", false, false, false));
    MonetDBDatabaseMeta.safeModeLocal.set(new Boolean(true));
    assertEquals("VARCHAR(100)", // should end up with (100) if "safeMode = true"
    nativeMeta.getFieldDefinition(new ValueMetaString("FOO", -2, 0), "", "", false, false, false));
    assertEquals(" UNKNOWN", nativeMeta.getFieldDefinition(new ValueMetaInternetAddress("FOO"), "", "", false, false, false));
    assertEquals(" UNKNOWN" + System.getProperty("line.separator"), nativeMeta.getFieldDefinition(new ValueMetaInternetAddress("FOO"), "", "", false, false, true));
}
Also used : ValueMetaTimestamp(org.pentaho.di.core.row.value.ValueMetaTimestamp) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaNumber(org.pentaho.di.core.row.value.ValueMetaNumber) ValueMetaBigNumber(org.pentaho.di.core.row.value.ValueMetaBigNumber) ValueMetaInternetAddress(org.pentaho.di.core.row.value.ValueMetaInternetAddress) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) Test(org.junit.Test)

Example 19 with ValueMetaTimestamp

use of org.pentaho.di.core.row.value.ValueMetaTimestamp in project pentaho-kettle by pentaho.

the class MySQLDatabaseMetaTest method testSQLStatements.

@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", // Bug here - invalid SQL
    nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 10, -7), "", false, "", false));
    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));
    // some subclass of the MSSQL meta probably ...
    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 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"));
}
Also used : ValueMetaTimestamp(org.pentaho.di.core.row.value.ValueMetaTimestamp) ValueMetaBinary(org.pentaho.di.core.row.value.ValueMetaBinary) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaNumber(org.pentaho.di.core.row.value.ValueMetaNumber) ValueMetaBigNumber(org.pentaho.di.core.row.value.ValueMetaBigNumber) ValueMetaInternetAddress(org.pentaho.di.core.row.value.ValueMetaInternetAddress) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) Test(org.junit.Test)

Example 20 with ValueMetaTimestamp

use of org.pentaho.di.core.row.value.ValueMetaTimestamp in project pentaho-kettle by pentaho.

the class OracleDatabaseMetaTest method testOverriddenSQLStatements.

@Test
public void testOverriddenSQLStatements() throws Exception {
    assertEquals(" WHERE ROWNUM <= 5", nativeMeta.getLimitClause(5));
    String reusedFieldsQuery = "SELECT * FROM FOO WHERE 1=0";
    assertEquals(reusedFieldsQuery, nativeMeta.getSQLQueryFields("FOO"));
    assertEquals(reusedFieldsQuery, nativeMeta.getSQLTableExists("FOO"));
    String reusedColumnsQuery = "SELECT FOO FROM BAR WHERE 1=0";
    assertEquals(reusedColumnsQuery, nativeMeta.getSQLQueryColumnFields("FOO", "BAR"));
    assertEquals(reusedColumnsQuery, nativeMeta.getSQLColumnExists("FOO", "BAR"));
    assertEquals("SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'FOO'", nativeMeta.getSQLSequenceExists("FOO"));
    assertEquals("SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'FOO'", nativeMeta.getSQLSequenceExists("foo"));
    assertEquals("SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'BAR' AND SEQUENCE_OWNER = 'FOO'", nativeMeta.getSQLSequenceExists("FOO.BAR"));
    assertEquals("SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'BAR' AND SEQUENCE_OWNER = 'FOO'", nativeMeta.getSQLSequenceExists("foo.bar"));
    assertEquals("SELECT FOO.currval FROM DUAL", nativeMeta.getSQLCurrentSequenceValue("FOO"));
    assertEquals("SELECT FOO.nextval FROM dual", nativeMeta.getSQLNextSequenceValue("FOO"));
    assertEquals("ALTER TABLE FOO ADD ( FOO DATE ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaTimestamp("FOO"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( FOO DATE ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaDate("FOO"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( FOO VARCHAR2(15) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaString("FOO", 15, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( FOO INTEGER ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInteger("FOO", 15, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( FOO NUMBER(15, 10) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBigNumber("FOO", 15, 10), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( FOO NUMBER(15, 10) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("FOO", 15, 10), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( FOO BLOB ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBinary("FOO", 2048, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( FOO CHAR(1) ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBoolean("FOO"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD ( FOO  UNKNOWN ) ", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInternetAddress("FOO"), "", false, "", false));
    String lineSep = System.getProperty("line.separator");
    assertEquals("ALTER TABLE FOO DROP ( BAR ) " + lineSep, nativeMeta.getDropColumnStatement("FOO", new ValueMetaString("BAR"), "", false, "", false));
    String modColStmtExpected = "ALTER TABLE FOO ADD ( BAR_KTL VARCHAR2(2000) ) ;" + lineSep + "UPDATE FOO SET BAR_KTL=BAR;" + lineSep + "ALTER TABLE FOO DROP ( BAR ) " + lineSep + ";" + lineSep + "ALTER TABLE FOO ADD ( BAR VARCHAR2(2000) ) ;" + lineSep + "UPDATE FOO SET BAR=BAR_KTL;" + lineSep + "ALTER TABLE FOO DROP ( BAR_KTL ) " + lineSep;
    assertEquals(modColStmtExpected, nativeMeta.getModifyColumnStatement("FOO", new ValueMetaString("BAR"), "", false, "", false));
    modColStmtExpected = "ALTER TABLE \"FOO\" ADD ( BAR_KTL VARCHAR2(2000) ) ;" + lineSep + "UPDATE \"FOO\" SET BAR_KTL=BAR;" + lineSep + "ALTER TABLE \"FOO\" DROP ( BAR ) " + lineSep + ";" + lineSep + "ALTER TABLE \"FOO\" ADD ( BAR VARCHAR2(2000) ) ;" + lineSep + "UPDATE \"FOO\" SET BAR=BAR_KTL;" + lineSep + "ALTER TABLE \"FOO\" DROP ( BAR_KTL ) " + lineSep;
    assertEquals(modColStmtExpected, nativeMeta.getModifyColumnStatement("\"FOO\"", new ValueMetaString("BAR"), "", false, "", false));
    modColStmtExpected = "ALTER TABLE FOO ADD ( A12345678901234567890123456789_KTL VARCHAR2(2000) ) ;" + lineSep + "UPDATE FOO SET A12345678901234567890123456789_KTL=A1234567890123456789012345678901234567890;" + lineSep + "ALTER TABLE FOO DROP ( A1234567890123456789012345678901234567890 ) " + lineSep + ";" + lineSep + "ALTER TABLE FOO ADD ( A1234567890123456789012345678901234567890 VARCHAR2(2000) ) ;" + lineSep + "UPDATE FOO SET A1234567890123456789012345678901234567890=A12345678901234567890123456789_KTL;" + lineSep + "ALTER TABLE FOO DROP ( A12345678901234567890123456789_KTL ) " + lineSep;
    assertEquals(modColStmtExpected, nativeMeta.getModifyColumnStatement("FOO", new ValueMetaString("A1234567890123456789012345678901234567890"), "", false, "", false));
    String expectedProcSql = "SELECT DISTINCT DECODE(package_name, NULL, '', package_name||'.') || object_name " + "FROM user_arguments " + "ORDER BY 1";
    assertEquals(expectedProcSql, nativeMeta.getSQLListOfProcedures());
    String expectedLockOneItem = "LOCK TABLE FOO IN EXCLUSIVE MODE;" + lineSep;
    assertEquals(expectedLockOneItem, nativeMeta.getSQLLockTables(new String[] { "FOO" }));
    String expectedLockMultiItem = "LOCK TABLE FOO IN EXCLUSIVE MODE;" + lineSep + "LOCK TABLE BAR IN EXCLUSIVE MODE;" + lineSep;
    assertEquals(expectedLockMultiItem, nativeMeta.getSQLLockTables(new String[] { "FOO", "BAR" }));
    // Commit unlocks tables
    assertNull(nativeMeta.getSQLUnlockTables(null));
    assertEquals("SELECT SEQUENCE_NAME FROM all_sequences", nativeMeta.getSQLListOfSequences());
    assertEquals("BEGIN EXECUTE IMMEDIATE 'DROP TABLE FOO'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;", nativeMeta.getDropTableIfExistsStatement("FOO"));
}
Also used : ValueMetaTimestamp(org.pentaho.di.core.row.value.ValueMetaTimestamp) ValueMetaBinary(org.pentaho.di.core.row.value.ValueMetaBinary) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaNumber(org.pentaho.di.core.row.value.ValueMetaNumber) ValueMetaBigNumber(org.pentaho.di.core.row.value.ValueMetaBigNumber) ValueMetaInternetAddress(org.pentaho.di.core.row.value.ValueMetaInternetAddress) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) Test(org.junit.Test)

Aggregations

ValueMetaTimestamp (org.pentaho.di.core.row.value.ValueMetaTimestamp)25 ValueMetaDate (org.pentaho.di.core.row.value.ValueMetaDate)21 ValueMetaString (org.pentaho.di.core.row.value.ValueMetaString)21 Test (org.junit.Test)19 ValueMetaBoolean (org.pentaho.di.core.row.value.ValueMetaBoolean)18 ValueMetaInteger (org.pentaho.di.core.row.value.ValueMetaInteger)18 ValueMetaNumber (org.pentaho.di.core.row.value.ValueMetaNumber)16 ValueMetaBigNumber (org.pentaho.di.core.row.value.ValueMetaBigNumber)15 ValueMetaInternetAddress (org.pentaho.di.core.row.value.ValueMetaInternetAddress)15 ValueMetaBinary (org.pentaho.di.core.row.value.ValueMetaBinary)12 RowMeta (org.pentaho.di.core.row.RowMeta)7 RowMetaInterface (org.pentaho.di.core.row.RowMetaInterface)7 ValueMetaInterface (org.pentaho.di.core.row.ValueMetaInterface)4 ResultSet (java.sql.ResultSet)3 Timestamp (java.sql.Timestamp)3 Date (java.util.Date)3 ByteArrayInputStream (java.io.ByteArrayInputStream)2 ResultSetMetaData (java.sql.ResultSetMetaData)2 SQLException (java.sql.SQLException)2 KettleDatabaseException (org.pentaho.di.core.exception.KettleDatabaseException)2