@Override protected boolean isInternalPrimaryKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table, IIndex index) { // Sql Server generates an index "PK__[table name]__[hex number]" StringBuffer pkIndexName = new StringBuffer(); pkIndexName.append("PK__"); pkIndexName.append(table.getName()); pkIndexName.append("__"); return index.getName().toUpperCase().startsWith(pkIndexName.toString().toUpperCase()); }
protected void setPrimaryKeyConstraintName(Connection connection, Table table) throws SQLException { String sql = "select RDB$CONSTRAINT_NAME from RDB$RELATION_CONSTRAINTS where RDB$RELATION_NAME=? and RDB$CONSTRAINT_TYPE='PRIMARY KEY'"; PreparedStatement pstmt = null; try { pstmt = connection.prepareStatement(sql); pstmt.setString(1, table.getName()); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { table.setPrimaryKeyConstraintName(rs.getString(1).trim()); } rs.close(); } finally { JdbcSqlTemplate.close(pstmt); } }
@Override protected boolean isInternalPrimaryKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table, IIndex index) throws SQLException { // We can simply check the sysindexes table where a specific flag is set // for pk indexes StringBuffer query = new StringBuffer(); query.append("SELECT name = si.name FROM dbo.sysindexes si, dbo.sysobjects so WHERE so.name = '"); query.append(table.getName()); query.append("' AND si.name = '"); query.append(index.getName()); query.append("' AND so.id = si.id AND (si.status & 2048) > 0"); Statement stmt = connection.createStatement(); try { ResultSet rs = stmt.executeQuery(query.toString()); boolean result = rs.next(); rs.close(); return result; } finally { stmt.close(); } } }
private boolean tableMatches(DataMetaData dataMetaData, String tableName) { boolean matches = false; if (dataMetaData .getTable() .getName() .equalsIgnoreCase(tableName(tableName))) { matches = true; } return matches; }
@Override protected boolean isInternalPrimaryKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table, IIndex index) throws SQLException { // We can simply check the sysindexes table where a specific flag is set // for pk indexes StringBuffer query = new StringBuffer(); query.append("SELECT name = si.name FROM dbo.sysindexes si, dbo.sysobjects so WHERE so.name = '"); query.append(table.getName()); query.append("' AND si.name = '"); query.append(index.getName()); query.append("' AND so.id = si.id AND (si.status & 2048) > 0"); Statement stmt = connection.createStatement(); try { ResultSet rs = stmt.executeQuery(query.toString()); boolean result = rs.next(); rs.close(); return result; } finally { stmt.close(); } } }
private boolean existsPKWithName(DatabaseMetaDataWrapper metaData, Table table, String name) { try { ResultSet pks = metaData.getPrimaryKeys(table.getName()); boolean found = false; while (pks.next() && !found) { if (name.equals(pks.getString("PK_NAME"))) { found = true; } } pks.close(); return found; } catch (SQLException ex) { throw new DdlException(ex); } }
protected void setPrimaryKeyConstraintName(Connection connection, Table table) throws SQLException { String sql = "select conname from pg_constraint where conrelid in (select oid from pg_class where relname=? and relnamespace in (select oid from pg_namespace where nspname=?)) and contype='p'"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = connection.prepareStatement(sql); pstmt.setString(1, table.getName()); pstmt.setString(2, table.getSchema()); rs = pstmt.executeQuery(); if (rs.next()) { table.setPrimaryKeyConstraintName(rs.getString(1).trim()); } } finally { JdbcSqlTemplate.close(rs); JdbcSqlTemplate.close(pstmt); } }
protected void processError(LoadFilter currentFilter, Table table, Throwable ex) { if (ex instanceof TargetError) { ex = ((TargetError) ex).getTarget(); } String formattedMessage = String.format( "Error executing beanshell script for load filter %s on table %s. The error was: %s", new Object[] { currentFilter != null ? currentFilter.getLoadFilterId() : "N/A", table.getName(), ex.getMessage() }); log.error(formattedMessage); if (currentFilter.isFailOnError()) { throw new SymmetricException(formattedMessage, ex); } }
@Override public boolean beforeWrite( DataContext context, Table table, CsvData data) { if (!table.getName().startsWith(tablePrefix)) { Batch batch = context.getBatch(); String sourceNodeId = batch.getSourceNodeId(); table.setSchema(schemaPrefix != null ? schemaPrefix + sourceNodeId : sourceNodeId); } return true; }
protected Table getDbTable(String tableName) { if (allDbTablesCache == null) { allDbTablesCache = platform.readDatabase(getCatalogToUse(), getSchemaToUse(), null).getTables(); } for (Table table : allDbTablesCache) { if (table.getName().equalsIgnoreCase(tableName)) { return table; } } return null; }
public void createTriggersOnChannelForTables(String channelId, Set<Table> tables, String lastUpdateBy) { for (Table table : tables) { Trigger trigger = new Trigger(); trigger.setChannelId(channelId); trigger.setSourceTableName(table.getName()); trigger.setTriggerId(table.getName()); trigger.setLastUpdateBy(lastUpdateBy); trigger.setLastUpdateTime(new Date()); trigger.setCreateTime(new Date()); saveTrigger(trigger); } }
private boolean containsExactMatchForSourceTableName(Table table, List<Trigger> triggers, boolean ignoreCase) { for (Trigger trigger : triggers) { String sourceCatalogName = trigger.getSourceCatalogName() != null ? trigger.getSourceCatalogName() : platform.getDefaultCatalog(); String sourceSchemaName = trigger.getSourceSchemaName() != null ? trigger.getSourceSchemaName() : platform.getDefaultSchema(); if (trigger.getSourceTableName().equals(table.getName()) && (sourceCatalogName == null || sourceCatalogName.equals(table.getCatalog())) && (sourceSchemaName == null || sourceSchemaName.equals(table.getSchema()))) { return true; } else if (ignoreCase && trigger.getSourceTableName().equalsIgnoreCase(table.getName()) && sourceCatalogName.equalsIgnoreCase(table.getCatalog()) && sourceSchemaName.equalsIgnoreCase(table.getSchema())) { return true; } } return false; }
protected void importTablesFromCsv(InputStream in, String tableName) { Table table = platform.readTableFromDatabase(catalog, schema, tableName); if (table == null) { throw new RuntimeException("Unable to find table"); } CsvTableDataReader reader = new CsvTableDataReader(BinaryEncoding.HEX, table.getCatalog(), table.getSchema(), table.getName(), in); DatabaseWriter writer = new DatabaseWriter(platform, buildDatabaseWriterSettings()); DataProcessor dataProcessor = new DataProcessor(reader, writer, "import"); dataProcessor.process(); }
public boolean start(Table table) { /* * in the case when the target schema or catalog is set then we need to * use the previous schema or catalog to look up the table locally. */ this.currentTable = platform.getTableFromCache(table.getOldCatalog(), table.getOldSchema(), table.getName(), false); this.currentTable = currentTable.copyAndFilterColumns(table.getColumnNames(), table.getPrimaryKeyColumnNames(), true); /* * restore the schema and catalog from the passed in table because they * might have not been originally set, but were set when looking up the table locally */ this.currentTable.setSchema(table.getSchema()); this.currentTable.setCatalog(table.getCatalog()); this.currentTable.setName(table.getName()); return true; }
public void syncTriggers(Table table, boolean force) { boolean ignoreCase = this.parameterService.is(ParameterConstants.DB_METADATA_IGNORE_CASE); /* Re-lookup just in case the table was just altered */ platform.resetCachedTableModel(); table = platform.getTableFromCache(table.getCatalog(), table.getSchema(), table.getName(), true); List<Trigger> triggersForCurrentNode = getTriggersForCurrentNode(); for (Trigger trigger : triggersForCurrentNode) { if (trigger.matches(table, platform.getDefaultCatalog(), platform.getDefaultSchema(), ignoreCase)) { log.info("Synchronizing triggers for {}", table.getFullyQualifiedTableName()); updateOrCreateDatabaseTriggers(trigger, table, null, force, true); log.info("Done synchronizing triggers for {}", table.getFullyQualifiedTableName()); } } }
protected Table toAuditTable(Table table) { Table auditTable = table.copy(); String tableName = table.getName(); if (!FormatUtils.isMixedCase(tableName)) { tableName = tableName.toUpperCase(); } auditTable.setName(String.format("%s_AUDIT", tableName)); Column[] columns = auditTable.getColumns(); auditTable.removeAllColumns(); auditTable.addColumn(new Column(COLUMN_AUDIT_ID, true, Types.BIGINT, 0, 0)); auditTable.addColumn(new Column(COLUMN_AUDIT_TIME, false, Types.TIMESTAMP, 0, 0)); auditTable.addColumn(new Column(COLUMN_AUDIT_EVENT, false, Types.CHAR, 1, 0)); for (Column column : columns) { column.setRequired(false); column.setPrimaryKey(false); column.setAutoIncrement(false); auditTable.addColumn(column); } auditTable.removeAllForeignKeys(); auditTable.removeAllIndices(); engine.getDatabasePlatform().alterCaseToMatchDatabaseDefaultCase(auditTable); return auditTable; }
public TriggerHistory(Table table, Trigger trigger, AbstractTriggerTemplate triggerTemplate, TriggerReBuildReason reason) { this(); this.lastTriggerBuildReason = reason; this.sourceTableName = trigger.isSourceTableNameWildCarded() ? table.getName() : trigger .getSourceTableName(); this.columnNames = Table.getCommaDeliminatedColumns(trigger.orderColumnsForTable(table)); this.sourceSchemaName = trigger.getSourceSchemaName(); this.sourceCatalogName = trigger.getSourceCatalogName(); this.triggerId = trigger.getTriggerId(); this.pkColumnNames = Table.getCommaDeliminatedColumns(trigger.filterExcludedColumns(trigger .getSyncKeysColumnsForTable(table))); this.triggerRowHash = trigger.toHashedValue(); this.triggerTemplateHash = triggerTemplate.toHashedValue(); this.tableHash = table.calculateTableHashcode(); }
public String createCsvPrimaryKeySql(Trigger trigger, TriggerHistory triggerHistory, Table table, Channel channel, String whereClause) { String sql = sqlTemplates.get(INITIAL_LOAD_SQL_TEMPLATE); Column[] columns = table.getPrimaryKeyColumns(); String columnsText = buildColumnString(symmetricDialect.getInitialLoadTableAlias(), symmetricDialect.getInitialLoadTableAlias(), "", columns, DataEventType.INSERT, false, channel, trigger).toString(); sql = FormatUtils.replace("columns", columnsText, sql); sql = FormatUtils.replace("oracleToClob", trigger.isUseCaptureLobs() ? "to_clob('')||" : "", sql); sql = FormatUtils.replace("tableName", SymmetricUtils.quote(symmetricDialect, table.getName()), sql); sql = FormatUtils.replace("schemaName", triggerHistory == null ? getSourceTablePrefix(trigger) : getSourceTablePrefix(triggerHistory), sql); sql = FormatUtils.replace("whereClause", whereClause, sql); sql = FormatUtils.replace( "primaryKeyWhereString", getPrimaryKeyWhereString(symmetricDialect.getInitialLoadTableAlias(), table.hasPrimaryKey() ? table.getPrimaryKeyColumns() : table.getColumns()), sql); return sql; }