@Test public void testCheckIndexExists() throws Exception { String expectedSQL = "select i.name table_name, c.name column_name from sysindexes i, sysindexkeys k, syscolumns c where i.name = 'FOO' AND i.id = k.id AND i.id = c.id AND k.colid = c.colid "; Database db = Mockito.mock( Database.class ); RowMetaInterface rm = Mockito.mock( RowMetaInterface.class ); ResultSet rs = Mockito.mock( ResultSet.class ); DatabaseMeta dm = Mockito.mock( DatabaseMeta.class ); Mockito.when( dm.getQuotedSchemaTableCombination( "", "FOO" ) ).thenReturn( "FOO" ); Mockito.when( rs.next() ).thenReturn( rowCnt < 2 ); Mockito.when( db.openQuery( expectedSQL ) ).thenReturn( rs ); Mockito.when( db.getReturnRowMeta() ).thenReturn( rm ); Mockito.when( rm.getString( row1, "column_name", "" ) ).thenReturn( "ROW1COL2" ); Mockito.when( rm.getString( row2, "column_name", "" ) ).thenReturn( "ROW2COL2" ); Mockito.when( db.getRow( rs ) ).thenAnswer( new Answer<Object[]>() { @Override public Object[] answer( InvocationOnMock invocation ) throws Throwable { rowCnt++; if ( rowCnt == 1 ) { return row1; } else if ( rowCnt == 2 ) { return row2; } else { return null; } } } ); Mockito.when( db.getDatabaseMeta() ).thenReturn( dm ); assertTrue( nativeMeta.checkIndexExists( db, "", "FOO", new String[] { "ROW1COL2", "ROW2COL2" } ) ); assertFalse( nativeMeta.checkIndexExists( db, "", "FOO", new String[] { "ROW2COL2", "NOTTHERE" } ) ); assertFalse( nativeMeta.checkIndexExists( db, "", "FOO", new String[] { "NOTTHERE", "ROW1COL2" } ) ); }
@Override public boolean checkIndexExists( Database database, String schemaName, String tableName, String[] idx_fields ) throws KettleDatabaseException { String tablename = database.getDatabaseMeta().getQuotedSchemaTableCombination( schemaName, tableName ); boolean[] exists = new boolean[idx_fields.length]; for ( int i = 0; i < exists.length; i++ ) { exists[i] = false; } try { StringBuilder sql = new StringBuilder( 128 ); sql.append( "select i.name table_name, c.name column_name " ); sql.append( "from sysindexes i, sysindexkeys k, syscolumns c " ); sql.append( "where i.name = '" + tablename + "' " ); sql.append( "AND i.id = k.id " ); sql.append( "AND i.id = c.id " ); sql.append( "AND k.colid = c.colid " ); ResultSet res = null; try { res = database.openQuery( sql.toString() ); if ( res != null ) { Object[] row = database.getRow( res ); while ( row != null ) { String column = database.getReturnRowMeta().getString( row, "column_name", "" ); int idx = Const.indexOfString( column, idx_fields ); if ( idx >= 0 ) { exists[idx] = true; } row = database.getRow( res ); } } else { return false; } } finally { if ( res != null ) { database.closeQuery( res ); } } boolean all = true; for ( int i = 0; i < exists.length && all; i++ ) { if ( !exists[i] ) { all = false; } } return all; } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to determine if indexes exists on table [" + tablename + "]", e ); } }
MSSQLServerDatabaseMeta extends BaseDatabaseMeta implements DatabaseInterface { @Override public boolean checkIndexExists( Database database, String schemaName, String tableName, String[] idx_fields ) throws KettleDatabaseException { String tablename = database.getDatabaseMeta().getQuotedSchemaTableCombination( schemaName, tableName ); boolean[] exists = new boolean[idx_fields.length]; for ( int i = 0; i < exists.length; i++ ) { exists[i] = false; } try { StringBuilder sql = new StringBuilder( 128 ); sql.append( "select i.name table_name, c.name column_name " ); sql.append( "from sysindexes i, sysindexkeys k, syscolumns c " ); sql.append( "where i.name = '" + tablename + "' " ); sql.append( "AND i.id = k.id " ); sql.append( "AND i.id = c.id " ); sql.append( "AND k.colid = c.colid " ); ResultSet res = null; try { res = database.openQuery( sql.toString() ); if ( res != null ) { Object[] row = database.getRow( res ); while ( row != null ) { String column = database.getReturnRowMeta().getString( row, "column_name", "" ); int idx = Const.indexOfString( column, idx_fields ); if ( idx >= 0 ) { exists[idx] = true; } row = database.getRow( res ); } } else { return false; } } finally { if ( res != null ) { database.closeQuery( res ); } } boolean all = true; for ( int i = 0; i < exists.length && all; i++ ) { if ( !exists[i] ) { all = false; } } return all; } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to determine if indexes exists on table [" + tablename + "]", e ); } } }
MSSQLServerDatabaseMeta extends BaseDatabaseMeta implements DatabaseInterface { @Override public boolean checkIndexExists( Database database, String schemaName, String tableName, String[] idx_fields ) throws KettleDatabaseException { String tablename = database.getDatabaseMeta().getQuotedSchemaTableCombination( schemaName, tableName ); boolean[] exists = new boolean[idx_fields.length]; for ( int i = 0; i < exists.length; i++ ) { exists[i] = false; } try { StringBuilder sql = new StringBuilder( 128 ); sql.append( "select i.name table_name, c.name column_name " ); sql.append( "from sysindexes i, sysindexkeys k, syscolumns c " ); sql.append( "where i.name = '" + tablename + "' " ); sql.append( "AND i.id = k.id " ); sql.append( "AND i.id = c.id " ); sql.append( "AND k.colid = c.colid " ); ResultSet res = null; try { res = database.openQuery( sql.toString() ); if ( res != null ) { Object[] row = database.getRow( res ); while ( row != null ) { String column = database.getReturnRowMeta().getString( row, "column_name", "" ); int idx = Const.indexOfString( column, idx_fields ); if ( idx >= 0 ) { exists[idx] = true; } row = database.getRow( res ); } } else { return false; } } finally { if ( res != null ) { database.closeQuery( res ); } } boolean all = true; for ( int i = 0; i < exists.length && all; i++ ) { if ( !exists[i] ) { all = false; } } return all; } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to determine if indexes exists on table [" + tablename + "]", e ); } }  }
MSSQLServerDatabaseMeta extends BaseDatabaseMeta implements DatabaseInterface { @Override public boolean checkIndexExists( Database database, String schemaName, String tableName, String[] idx_fields ) throws KettleDatabaseException { String tablename = database.getDatabaseMeta().getQuotedSchemaTableCombination( schemaName, tableName ); boolean[] exists = new boolean[idx_fields.length]; for ( int i = 0; i < exists.length; i++ ) { exists[i] = false; } try { StringBuilder sql = new StringBuilder( 128 ); sql.append( "select i.name table_name, c.name column_name " ); sql.append( "from sysindexes i, sysindexkeys k, syscolumns c " ); sql.append( "where i.name = '" + tablename + "' " ); sql.append( "AND i.id = k.id " ); sql.append( "AND i.id = c.id " ); sql.append( "AND k.colid = c.colid " ); ResultSet res = null; try { res = database.openQuery( sql.toString() ); if ( res != null ) { Object[] row = database.getRow( res ); while ( row != null ) { String column = database.getReturnRowMeta().getString( row, "column_name", "" ); int idx = Const.indexOfString( column, idx_fields ); if ( idx >= 0 ) { exists[idx] = true; } row = database.getRow( res ); } } else { return false; } } finally { if ( res != null ) { database.closeQuery( res ); } } boolean all = true; for ( int i = 0; i < exists.length && all; i++ ) { if ( !exists[i] ) { all = false; } } return all; } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to determine if indexes exists on table [" + tablename + "]", e ); } }  @Override boolean supportsCatalogs(); @Override int[] getAccessTypeList(); @Override int getDefaultDatabasePort(); @Override String getDriverClass(); @Override String getURL( String hostname, String port, String databaseName ); @Override String getSchemaTableCombination( String schema_name, String table_part ); @Override boolean supportsBitmapIndex(); @Override boolean supportsSynonyms(); @Override String getSQLQueryFields( String tableName ); @Override String getSQLTableExists( String tablename ); @Override String getSQLColumnExists( String columnname, String tablename ); String getSQLQueryColumnFields( String columnname, String tableName ); @Override String getSQLLockTables( String[] tableNames ); @Override String getAddColumnStatement( String tablename, ValueMetaInterface v, String tk, boolean use_autoinc,
    String pk, boolean semicolon ); @Override String getModifyColumnStatement( String tablename, ValueMetaInterface v, String tk, boolean use_autoinc,
    String pk, boolean semicolon ); @Override String getDropColumnStatement( String tablename, ValueMetaInterface v, String tk, boolean use_autoinc,
    String pk, boolean semicolon ); @Override String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean use_autoinc,
    boolean add_fieldname, boolean add_cr ); String getSQLListOfProcedures( String schemaName ); @Override String[] getReservedWords(); @Override String[] getUsedLibraries(); @Override String getExtraOptionsHelpText(); @Override boolean checkIndexExists( Database database, String schemaName, String tableName, String[] idx_fields ); @Override String getSQLListOfSchemas(); @Override boolean supportsSchemas(); @Override String getSQLInsertAutoIncUnknownDimensionRow( String schemaTable, String keyField, String versionField ); @Override String getSQLNextSequenceValue( String sequenceName ); @Override String getSQLCurrentSequenceValue( String sequenceName ); @Override String getSQLSequenceExists( String sequenceName ); @Override boolean supportsSequences(); @Override boolean supportsSequenceNoMaxValueOption(); @Override String getSQLListOfSequences(); @Override String quoteSQLString( String string ); @Override Long getNextBatchIdUsingLockTables( DatabaseMeta dbm, Database ldb, String schemaName, String tableName,
    String fieldName ); @Override boolean useSafePoints(); @Override boolean supportsErrorHandlingOnBatchUpdates(); @Override int getMaxVARCHARLength(); }
MSSQLServerDatabaseMeta extends BaseDatabaseMeta implements DatabaseInterface { @Override public boolean checkIndexExists( Database database, String schemaName, String tableName, String[] idx_fields ) throws KettleDatabaseException { String tablename = database.getDatabaseMeta().getQuotedSchemaTableCombination( schemaName, tableName ); boolean[] exists = new boolean[idx_fields.length]; for ( int i = 0; i < exists.length; i++ ) { exists[i] = false; } try { StringBuilder sql = new StringBuilder( 128 ); sql.append( "select i.name table_name, c.name column_name " ); sql.append( "from sysindexes i, sysindexkeys k, syscolumns c " ); sql.append( "where i.name = '" + tablename + "' " ); sql.append( "AND i.id = k.id " ); sql.append( "AND i.id = c.id " ); sql.append( "AND k.colid = c.colid " ); ResultSet res = null; try { res = database.openQuery( sql.toString() ); if ( res != null ) { Object[] row = database.getRow( res ); while ( row != null ) { String column = database.getReturnRowMeta().getString( row, "column_name", "" ); int idx = Const.indexOfString( column, idx_fields ); if ( idx >= 0 ) { exists[idx] = true; } row = database.getRow( res ); } } else { return false; } } finally { if ( res != null ) { database.closeQuery( res ); } } boolean all = true; for ( int i = 0; i < exists.length && all; i++ ) { if ( !exists[i] ) { all = false; } } return all; } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to determine if indexes exists on table [" + tablename + "]", e ); } }  @Override boolean supportsCatalogs(); @Override int[] getAccessTypeList(); @Override int getDefaultDatabasePort(); @Override String getDriverClass(); @Override String getURL( String hostname, String port, String databaseName ); @Override String getSchemaTableCombination( String schema_name, String table_part ); @Override boolean supportsBitmapIndex(); @Override boolean supportsSynonyms(); @Override String getSQLQueryFields( String tableName ); @Override String getSQLTableExists( String tablename ); @Override String getSQLColumnExists( String columnname, String tablename ); String getSQLQueryColumnFields( String columnname, String tableName ); @Override String getSQLLockTables( String[] tableNames ); @Override String getAddColumnStatement( String tablename, ValueMetaInterface v, String tk, boolean use_autoinc,
    String pk, boolean semicolon ); @Override String getModifyColumnStatement( String tablename, ValueMetaInterface v, String tk, boolean use_autoinc,
    String pk, boolean semicolon ); @Override String getDropColumnStatement( String tablename, ValueMetaInterface v, String tk, boolean use_autoinc,
    String pk, boolean semicolon ); @Override String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean use_autoinc,
    boolean add_fieldname, boolean add_cr ); String getSQLListOfProcedures( String schemaName ); @Override String[] getReservedWords(); @Override String[] getUsedLibraries(); @Override String getExtraOptionsHelpText(); @Override boolean checkIndexExists( Database database, String schemaName, String tableName, String[] idx_fields ); @Override String getSQLListOfSchemas(); @Override boolean supportsSchemas(); @Override String getSQLInsertAutoIncUnknownDimensionRow( String schemaTable, String keyField, String versionField ); @Override String getSQLNextSequenceValue( String sequenceName ); @Override String getSQLCurrentSequenceValue( String sequenceName ); @Override String getSQLSequenceExists( String sequenceName ); @Override boolean supportsSequences(); @Override boolean supportsSequenceNoMaxValueOption(); @Override String getSQLListOfSequences(); @Override String quoteSQLString( String string ); @Override Long getNextBatchIdUsingLockTables( DatabaseMeta dbm, Database ldb, String schemaName, String tableName,
    String fieldName ); @Override boolean useSafePoints(); @Override boolean supportsErrorHandlingOnBatchUpdates(); @Override int getMaxVARCHARLength();  }