Examples with ValueMetaNumber org.pentaho.di.core.row.value.ValueMetaNumber used on opensource projects

Example 61 with ValueMetaNumber

use of org.pentaho.di.core.row.value.ValueMetaNumber 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"));
}

Example 62 with ValueMetaNumber

use of org.pentaho.di.core.row.value.ValueMetaNumber 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));
}

Example 63 with ValueMetaNumber

use of org.pentaho.di.core.row.value.ValueMetaNumber 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"));
}

Example 64 with ValueMetaNumber

use of org.pentaho.di.core.row.value.ValueMetaNumber 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"));
}

Example 65 with ValueMetaNumber

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

the class PostgreSQLDatabaseMetaTest method testSQLStatements.

@Test
public void testSQLStatements() {
    assertEquals("SELECT * FROM FOO limit 1", nativeMeta.getSQLQueryFields("FOO"));
    assertEquals("SELECT * FROM FOO limit 1", nativeMeta.getSQLTableExists("FOO"));
    assertEquals("SELECT FOO FROM BAR limit 1", nativeMeta.getSQLColumnExists("FOO", "BAR"));
    assertEquals("SELECT FOO FROM BAR limit 1", nativeMeta.getSQLQueryColumnFields("FOO", "BAR"));
    assertEquals("SELECT relname AS sequence_name FROM pg_catalog.pg_statio_all_sequences", nativeMeta.getSQLListOfSequences());
    assertEquals("SELECT nextval('FOO')", nativeMeta.getSQLNextSequenceValue("FOO"));
    assertEquals("SELECT currval('FOO')", nativeMeta.getSQLCurrentSequenceValue("FOO"));
    assertEquals("SELECT relname AS sequence_name FROM pg_catalog.pg_statio_all_sequences WHERE relname = 'foo'", nativeMeta.getSQLSequenceExists("FOO"));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR TIMESTAMP", nativeMeta.getAddColumnStatement("FOO", new ValueMetaDate("BAR"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR TIMESTAMP", nativeMeta.getAddColumnStatement("FOO", new ValueMetaTimestamp("BAR"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR CHAR(1)", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBoolean("BAR"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR BIGINT", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 10, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR BIGINT", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBigNumber("BAR", 10, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR BIGINT", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInteger("BAR", 10, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR DOUBLE PRECISION", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 0, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR INTEGER", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 5, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR NUMERIC(13, 3)", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 10, 3), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR NUMERIC(13, 3)", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBigNumber("BAR", 10, 3), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR NUMERIC(25, 4)", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBigNumber("BAR", 21, 4), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR TEXT", nativeMeta.getAddColumnStatement("FOO", new ValueMetaString("BAR", nativeMeta.getMaxVARCHARLength() + 2, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR VARCHAR(15)", nativeMeta.getAddColumnStatement("FOO", new ValueMetaString("BAR", 15, 0), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR BIGINT", // Bug here - invalid SQL
    nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 10, -7), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR NUMERIC(29, 7)", nativeMeta.getAddColumnStatement("FOO", new ValueMetaBigNumber("BAR", 22, 7), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR DOUBLE PRECISION", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", -10, 7), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR NUMERIC(12, 7)", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 5, 7), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR  UNKNOWN", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInternetAddress("BAR"), "", false, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR BIGSERIAL", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInteger("BAR"), "BAR", true, "", false));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR BIGSERIAL", nativeMeta.getAddColumnStatement("FOO", new ValueMetaNumber("BAR", 26, 8), "BAR", true, "", false));
    String lineSep = System.getProperty("line.separator");
    assertEquals("ALTER TABLE FOO DROP COLUMN BAR", nativeMeta.getDropColumnStatement("FOO", new ValueMetaString("BAR", 15, 0), "", false, "", true));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR_KTL VARCHAR(15);" + lineSep + "UPDATE FOO SET BAR_KTL=BAR;" + lineSep + "ALTER TABLE FOO DROP COLUMN BAR;" + lineSep + "ALTER TABLE FOO RENAME BAR_KTL TO BAR;" + lineSep, nativeMeta.getModifyColumnStatement("FOO", new ValueMetaString("BAR", 15, 0), "", false, "", true));
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR_KTL TEXT;" + lineSep + "UPDATE FOO SET BAR_KTL=BAR;" + lineSep + "ALTER TABLE FOO DROP COLUMN BAR;" + lineSep + "ALTER TABLE FOO RENAME BAR_KTL TO BAR;" + lineSep, nativeMeta.getModifyColumnStatement("FOO", new ValueMetaString("BAR"), "", false, "", true));
    // some subclass of the MSSQL meta probably ...
    odbcMeta.setSupportsBooleanDataType(true);
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR BOOLEAN", odbcMeta.getAddColumnStatement("FOO", new ValueMetaBoolean("BAR"), "", false, "", false));
    odbcMeta.setSupportsBooleanDataType(false);
    assertEquals("ALTER TABLE FOO ADD COLUMN BAR SMALLINT", nativeMeta.getAddColumnStatement("FOO", new ValueMetaInteger("BAR", 4, 0), "", true, "", false));
    odbcMeta.setUsername("fOoUsEr");
    assertEquals("select proname " + "from pg_proc, pg_user " + "where pg_user.usesysid = pg_proc.proowner " + "and upper(pg_user.usename) = 'FOOUSER' order by proname", odbcMeta.getSQLListOfProcedures());
    assertEquals("LOCK TABLE FOO , BAR IN ACCESS EXCLUSIVE MODE;" + lineSep, nativeMeta.getSQLLockTables(new String[] { "FOO", "BAR" }));
    assertNull(nativeMeta.getSQLUnlockTables(new String[] { "FOO" }));
}