@Override public void showDriverVersion() throws SQLException { super.showDriverVersion(); logger.warn("embulk-input-mysql 0.9.0 upgraded the bundled MySQL Connector/J version from 5.1.34 to 5.1.44 ."); logger.warn("And set useLegacyDatetimeCode=false by default in order to get correct datetime value when the server timezone and the client timezone are different."); logger.warn("Set useLegacyDatetimeCode=true if you need to get datetime value same as older embulk-input-mysql."); } }
public PreparedQuery wrapIncrementalQuery(String rawQuery, JdbcSchema querySchema, List<String> incrementalColumns, List<JsonNode> incrementalValues, boolean useRawQuery) throws SQLException { StringBuilder sb = new StringBuilder(); List<JdbcLiteral> parameters = ImmutableList.of(); if (useRawQuery) { parameters = replacePlaceholder(sb, rawQuery, querySchema, incrementalColumns, incrementalValues); } else { sb.append("SELECT * FROM ("); sb.append(truncateStatementDelimiter(rawQuery)); sb.append(") embulk_incremental_"); if (incrementalValues != null) { sb.append(" WHERE "); parameters = buildIncrementalConditionTo(sb, querySchema, incrementalColumns, incrementalValues); } sb.append(" ORDER BY "); buildIncrementalOrderTo(sb, querySchema, incrementalColumns); } return new PreparedQuery(sb.toString(), parameters); }
public String buildSelectQuery(String tableName, Optional<String> selectExpression, Optional<String> whereCondition, Optional<String> orderByExpression) throws SQLException { StringBuilder sb = new StringBuilder(); sb.append("SELECT "); sb.append(selectExpression.or("*")); sb.append(" FROM ").append(buildTableName(tableName)); if (whereCondition.isPresent()) { sb.append(" WHERE ").append(whereCondition.get()); } if (orderByExpression.isPresent()) { sb.append(" ORDER BY ").append(orderByExpression.get()); } return sb.toString(); }
protected void setSearchPath(String schema) throws SQLException { String sql = "SET search_path TO " + quoteIdentifierString(schema); executeUpdate(sql); }
private void buildIncrementalOrderTo(StringBuilder sb, JdbcSchema querySchema, List<String> incrementalColumns) throws SQLException { boolean first = true; for (String incrementalColumn : incrementalColumns) { if (first) { first = false; } else { sb.append(", "); } int columnIndex = findIncrementalColumnIndex(querySchema, incrementalColumn); // the following column name is case sensitive, // so should use actual column name got by DatabaseMetaData. sb.append(quoteIdentifierString(querySchema.getColumnName(columnIndex))); } }
parameters = buildIncrementalConditionTo(sb, querySchema, incrementalColumns, incrementalValues); sb.append(")"); buildIncrementalOrderTo(sb, querySchema, incrementalColumns); newOrderByExpression = Optional.of(sb.toString()); String newQuery = buildSelectQuery( tableName, selectExpression, newWhereCondition, newOrderByExpression);
int columnIndex = findIncrementalColumnIndex(querySchema, columnName); columnNames.put(columnName, columnIndex); for (Map.Entry<Integer, Integer> columnPosition: generateColumnPositionList(rawQuery, columnNames).entrySet()) { int columnIndex = columnPosition.getValue(); JsonNode value = incrementalValues.get(columnIndex);
try (BatchSelect cursor = con.newSelectCursor(builtQuery, getters, task.getFetchRows(), task.getSocketTimeout())) { while (true) { long rows = fetch(cursor, getters, pageBuilder); con.executeUpdate(task.getAfterSelect().get()); con.connection.commit();
@Override public void close() throws SQLException { // DB2 JDBC Driver requires explicit commit/rollback before closing connection. connection.rollback(); super.close(); }
JdbcInputConnection c = new JdbcInputConnection(con, t.getSchema().orNull()); con = null; return c;
private String getRawQuery(PluginTask task, JdbcInputConnection con) throws SQLException { if (task.getQuery().isPresent()) { if (task.getTable().isPresent() || task.getSelect().isPresent() || task.getWhere().isPresent() || task.getOrderBy().isPresent()) { throw new ConfigException("'table', 'select', 'where' and 'order_by' parameters are unnecessary if 'query' parameter is set."); } else if (task.getUseRawQueryWithIncremental()) { String rawQuery = task.getQuery().get(); for (String columnName : task.getIncrementalColumns()) { if (!rawQuery.contains(":" + columnName)) { throw new ConfigException(String.format("Column \":%s\" doesn't exist in query string", columnName)); } } if (!task.getLastRecord().isPresent()) { throw new ConfigException("'last_record' is required when 'use_raw_query_with_incremental' is set to true"); } if (task.getLastRecord().get().size() != task.getIncrementalColumns().size()) { throw new ConfigException("size of 'last_record' is different from of 'incremental_columns'"); } } else if (!task.getUseRawQueryWithIncremental() && (!task.getIncrementalColumns().isEmpty() || task.getLastRecord().isPresent())) { throw new ConfigException("'incremental_columns' and 'last_record' parameters are not supported if 'query' parameter is set and 'use_raw_query_with_incremental' is set to false."); } return task.getQuery().get(); } else if (task.getTable().isPresent()) { return con.buildSelectQuery(task.getTable().get(), task.getSelect(), task.getWhere(), task.getOrderBy()); } else { throw new ConfigException("'table' or 'query' parameter is required"); } }
List<JdbcLiteral> rightLiterals = new ArrayList<>(); for (int n = 0; n < incrementalColumns.size(); n++) { int columnIndex = findIncrementalColumnIndex(querySchema, incrementalColumns.get(n)); JsonNode value = incrementalValues.get(n); leftColumnNames.add(querySchema.getColumnName(columnIndex)); sb.append(quoteIdentifierString(leftColumnNames.get(i))); sb.append(" = ?"); parameters.add(rightLiterals.get(i)); sb.append(" AND "); sb.append(quoteIdentifierString(leftColumnNames.get(n))); sb.append(" > ?"); parameters.add(rightLiterals.get(n));
@Override public ConfigDiff transaction(ConfigSource config, InputPlugin.Control control) { PluginTask task = config.loadConfig(getTaskClass()); if (task.getIncremental()) { if (task.getOrderBy().isPresent()) { throw new ConfigException("order_by option must not be set if incremental is true"); } } else { if (!task.getIncrementalColumns().isEmpty()) { throw new ConfigException("'incremental: true' must be set if incremental_columns is set"); } } Schema schema; try (JdbcInputConnection con = newConnection(task)) { con.showDriverVersion(); // TODO incremental_columns is not set => get primary key schema = setupTask(con, task); } catch (SQLException ex) { throw Throwables.propagate(ex); } return buildNextConfigDiff(task, control.run(task.dump(), schema, 1)); }