io.prestosql.plugin.mysql.MySqlClient Maven / Gradle / Ivy
The newest version!
/*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package io.prestosql.plugin.mysql;
import com.google.common.collect.ImmutableSet;
import com.mysql.jdbc.Statement;
import io.prestosql.plugin.jdbc.BaseJdbcClient;
import io.prestosql.plugin.jdbc.BaseJdbcConfig;
import io.prestosql.plugin.jdbc.ColumnMapping;
import io.prestosql.plugin.jdbc.ConnectionFactory;
import io.prestosql.plugin.jdbc.JdbcColumnHandle;
import io.prestosql.plugin.jdbc.JdbcExpression;
import io.prestosql.plugin.jdbc.JdbcTableHandle;
import io.prestosql.plugin.jdbc.JdbcTypeHandle;
import io.prestosql.plugin.jdbc.PredicatePushdownController;
import io.prestosql.plugin.jdbc.WriteMapping;
import io.prestosql.plugin.jdbc.expression.AggregateFunctionRewriter;
import io.prestosql.plugin.jdbc.expression.AggregateFunctionRule;
import io.prestosql.plugin.jdbc.expression.ImplementAvgDecimal;
import io.prestosql.plugin.jdbc.expression.ImplementAvgFloatingPoint;
import io.prestosql.plugin.jdbc.expression.ImplementCount;
import io.prestosql.plugin.jdbc.expression.ImplementCountAll;
import io.prestosql.plugin.jdbc.expression.ImplementMinMax;
import io.prestosql.plugin.jdbc.expression.ImplementSum;
import io.prestosql.spi.PrestoException;
import io.prestosql.spi.connector.AggregateFunction;
import io.prestosql.spi.connector.ColumnHandle;
import io.prestosql.spi.connector.ConnectorSession;
import io.prestosql.spi.connector.ConnectorTableMetadata;
import io.prestosql.spi.connector.SchemaTableName;
import io.prestosql.spi.type.CharType;
import io.prestosql.spi.type.DecimalType;
import io.prestosql.spi.type.Decimals;
import io.prestosql.spi.type.StandardTypes;
import io.prestosql.spi.type.Type;
import io.prestosql.spi.type.TypeManager;
import io.prestosql.spi.type.TypeSignature;
import io.prestosql.spi.type.VarcharType;
import javax.inject.Inject;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.function.BiFunction;
import static com.google.common.base.Verify.verify;
import static com.google.common.util.concurrent.MoreExecutors.directExecutor;
import static com.mysql.jdbc.SQLError.SQL_STATE_ER_TABLE_EXISTS_ERROR;
import static com.mysql.jdbc.SQLError.SQL_STATE_SYNTAX_ERROR;
import static io.airlift.slice.Slices.utf8Slice;
import static io.prestosql.plugin.base.util.JsonTypeUtil.jsonParse;
import static io.prestosql.plugin.jdbc.DecimalConfig.DecimalMapping.ALLOW_OVERFLOW;
import static io.prestosql.plugin.jdbc.DecimalSessionSessionProperties.getDecimalDefaultScale;
import static io.prestosql.plugin.jdbc.DecimalSessionSessionProperties.getDecimalRounding;
import static io.prestosql.plugin.jdbc.DecimalSessionSessionProperties.getDecimalRoundingMode;
import static io.prestosql.plugin.jdbc.JdbcErrorCode.JDBC_ERROR;
import static io.prestosql.plugin.jdbc.PredicatePushdownController.DISABLE_PUSHDOWN;
import static io.prestosql.plugin.jdbc.PredicatePushdownController.FULL_PUSHDOWN;
import static io.prestosql.plugin.jdbc.PredicatePushdownController.PUSHDOWN_AND_KEEP;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.bigintColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.bigintWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.charWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.dateColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.dateWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.decimalColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.defaultCharColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.doubleColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.doubleWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.integerColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.integerWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.longDecimalWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.realColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.realWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.shortDecimalWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.smallintColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.smallintWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.timestampWriteFunctionUsingSqlTimestamp;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.tinyintColumnMapping;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.tinyintWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.varbinaryReadFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.varbinaryWriteFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.varcharReadFunction;
import static io.prestosql.plugin.jdbc.StandardColumnMappings.varcharWriteFunction;
import static io.prestosql.spi.StandardErrorCode.ALREADY_EXISTS;
import static io.prestosql.spi.StandardErrorCode.NOT_SUPPORTED;
import static io.prestosql.spi.type.BigintType.BIGINT;
import static io.prestosql.spi.type.DateType.DATE;
import static io.prestosql.spi.type.DecimalType.createDecimalType;
import static io.prestosql.spi.type.DoubleType.DOUBLE;
import static io.prestosql.spi.type.IntegerType.INTEGER;
import static io.prestosql.spi.type.RealType.REAL;
import static io.prestosql.spi.type.SmallintType.SMALLINT;
import static io.prestosql.spi.type.TimeWithTimeZoneType.TIME_WITH_TIME_ZONE;
import static io.prestosql.spi.type.TimestampType.TIMESTAMP_MILLIS;
import static io.prestosql.spi.type.TimestampWithTimeZoneType.TIMESTAMP_TZ_MILLIS;
import static io.prestosql.spi.type.TinyintType.TINYINT;
import static io.prestosql.spi.type.VarbinaryType.VARBINARY;
import static io.prestosql.spi.type.VarcharType.createUnboundedVarcharType;
import static io.prestosql.spi.type.VarcharType.createVarcharType;
import static java.lang.Math.max;
import static java.lang.Math.min;
import static java.lang.String.format;
import static java.util.Locale.ENGLISH;
public class MySqlClient
extends BaseJdbcClient
{
private final Type jsonType;
private final AggregateFunctionRewriter aggregateFunctionRewriter;
@Inject
public MySqlClient(BaseJdbcConfig config, ConnectionFactory connectionFactory, TypeManager typeManager)
{
super(config, "`", connectionFactory);
this.jsonType = typeManager.getType(new TypeSignature(StandardTypes.JSON));
JdbcTypeHandle bigintTypeHandle = new JdbcTypeHandle(Types.BIGINT, Optional.of("bigint"), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty());
this.aggregateFunctionRewriter = new AggregateFunctionRewriter(
this::quoted,
ImmutableSet.builder()
.add(new ImplementCountAll(bigintTypeHandle))
.add(new ImplementCount(bigintTypeHandle))
.add(new ImplementMinMax())
.add(new ImplementSum(MySqlClient::toTypeHandle))
.add(new ImplementAvgFloatingPoint())
.add(new ImplementAvgDecimal())
.add(new ImplementAvgBigint())
.build());
}
@Override
public Optional implementAggregation(ConnectorSession session, AggregateFunction aggregate, Map assignments)
{
// TODO support complex ConnectorExpressions
return aggregateFunctionRewriter.rewrite(session, aggregate, assignments);
}
private static Optional toTypeHandle(DecimalType decimalType)
{
return Optional.of(new JdbcTypeHandle(Types.NUMERIC, Optional.of("decimal"), Optional.of(decimalType.getPrecision()), Optional.of(decimalType.getScale()), Optional.empty(), Optional.empty()));
}
@Override
protected Collection listSchemas(Connection connection)
{
// for MySQL, we need to list catalogs instead of schemas
try (ResultSet resultSet = connection.getMetaData().getCatalogs()) {
ImmutableSet.Builder schemaNames = ImmutableSet.builder();
while (resultSet.next()) {
String schemaName = resultSet.getString("TABLE_CAT");
// skip internal schemas
if (filterSchema(schemaName)) {
schemaNames.add(schemaName);
}
}
return schemaNames.build();
}
catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
protected boolean filterSchema(String schemaName)
{
if (schemaName.equalsIgnoreCase("mysql")) {
return false;
}
return super.filterSchema(schemaName);
}
@Override
public void abortReadConnection(Connection connection)
throws SQLException
{
// Abort connection before closing. Without this, the MySQL driver
// attempts to drain the connection by reading all the results.
connection.abort(directExecutor());
}
@Override
public PreparedStatement getPreparedStatement(Connection connection, String sql)
throws SQLException
{
PreparedStatement statement = connection.prepareStatement(sql);
if (statement.isWrapperFor(Statement.class)) {
statement.unwrap(Statement.class).enableStreamingResults();
}
return statement;
}
@Override
protected ResultSet getTables(Connection connection, Optional schemaName, Optional tableName)
throws SQLException
{
// MySQL maps their "database" to SQL catalogs and does not have schemas
DatabaseMetaData metadata = connection.getMetaData();
return metadata.getTables(
schemaName.orElse(null),
null,
escapeNamePattern(tableName, metadata.getSearchStringEscape()).orElse(null),
new String[] {"TABLE", "VIEW"});
}
@Override
protected String getTableSchemaName(ResultSet resultSet)
throws SQLException
{
// MySQL uses catalogs instead of schemas
return resultSet.getString("TABLE_CAT");
}
@Override
public Optional toPrestoType(ConnectorSession session, Connection connection, JdbcTypeHandle typeHandle)
{
String jdbcTypeName = typeHandle.getJdbcTypeName()
.orElseThrow(() -> new PrestoException(JDBC_ERROR, "Type name is missing: " + typeHandle));
Optional mapping = getForcedMappingToVarchar(typeHandle);
if (mapping.isPresent()) {
return mapping;
}
Optional unsignedMapping = getUnsignedMapping(typeHandle);
if (unsignedMapping.isPresent()) {
return unsignedMapping;
}
if (jdbcTypeName.equalsIgnoreCase("json")) {
return Optional.of(jsonColumnMapping());
}
switch (typeHandle.getJdbcType()) {
case Types.TINYINT:
return Optional.of(tinyintColumnMapping());
case Types.SMALLINT:
return Optional.of(smallintColumnMapping());
case Types.INTEGER:
return Optional.of(integerColumnMapping());
case Types.BIGINT:
return Optional.of(bigintColumnMapping());
case Types.REAL:
return Optional.of(realColumnMapping());
case Types.DOUBLE:
return Optional.of(doubleColumnMapping());
case Types.DECIMAL:
int decimalDigits = typeHandle.getDecimalDigits().orElseThrow(() -> new IllegalStateException("decimal digits not present"));
int precision = typeHandle.getRequiredColumnSize();
if (getDecimalRounding(session) == ALLOW_OVERFLOW && precision > Decimals.MAX_PRECISION) {
int scale = min(decimalDigits, getDecimalDefaultScale(session));
return Optional.of(decimalColumnMapping(createDecimalType(Decimals.MAX_PRECISION, scale), getDecimalRoundingMode(session)));
}
// TODO does mysql support negative scale?
precision = precision + max(-decimalDigits, 0); // Map decimal(p, -s) (negative scale) to decimal(p+s, 0).
if (precision > Decimals.MAX_PRECISION) {
break;
}
return Optional.of(decimalColumnMapping(createDecimalType(precision, max(decimalDigits, 0))));
case Types.CHAR:
return Optional.of(defaultCharColumnMapping(typeHandle.getRequiredColumnSize()));
// TODO not all these type constants are necessarily used by the JDBC driver
case Types.VARCHAR:
case Types.NVARCHAR:
case Types.LONGVARCHAR:
case Types.LONGNVARCHAR:
int varcharLength = typeHandle.getRequiredColumnSize();
VarcharType varcharType = (varcharLength <= VarcharType.MAX_LENGTH) ? createVarcharType(varcharLength) : createUnboundedVarcharType();
// Remote database can be case insensitive.
PredicatePushdownController predicatePushdownController = PUSHDOWN_AND_KEEP;
return Optional.of(ColumnMapping.sliceMapping(varcharType, varcharReadFunction(varcharType), varcharWriteFunction(), predicatePushdownController));
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
return Optional.of(ColumnMapping.sliceMapping(VARBINARY, varbinaryReadFunction(), varbinaryWriteFunction(), FULL_PUSHDOWN));
case Types.DATE:
return Optional.of(dateColumnMapping());
}
// TODO add explicit mappings
return legacyToPrestoType(session, connection, typeHandle);
}
@Override
public WriteMapping toWriteMapping(ConnectorSession session, Type type)
{
if (type == TINYINT) {
return WriteMapping.longMapping("tinyint", tinyintWriteFunction());
}
if (type == SMALLINT) {
return WriteMapping.longMapping("smallint", smallintWriteFunction());
}
if (type == INTEGER) {
return WriteMapping.longMapping("integer", integerWriteFunction());
}
if (type == BIGINT) {
return WriteMapping.longMapping("bigint", bigintWriteFunction());
}
if (type == REAL) {
return WriteMapping.longMapping("float", realWriteFunction());
}
if (type == DOUBLE) {
return WriteMapping.doubleMapping("double precision", doubleWriteFunction());
}
if (type instanceof DecimalType) {
DecimalType decimalType = (DecimalType) type;
String dataType = format("decimal(%s, %s)", decimalType.getPrecision(), decimalType.getScale());
if (decimalType.isShort()) {
return WriteMapping.longMapping(dataType, shortDecimalWriteFunction(decimalType));
}
return WriteMapping.sliceMapping(dataType, longDecimalWriteFunction(decimalType));
}
if (type == DATE) {
return WriteMapping.longMapping("date", dateWriteFunction());
}
if (TIME_WITH_TIME_ZONE.equals(type) || TIMESTAMP_TZ_MILLIS.equals(type)) {
throw new PrestoException(NOT_SUPPORTED, "Unsupported column type: " + type.getDisplayName());
}
if (TIMESTAMP_MILLIS.equals(type)) {
// TODO use `timestampWriteFunction`
return WriteMapping.longMapping("datetime", timestampWriteFunctionUsingSqlTimestamp(TIMESTAMP_MILLIS));
}
if (VARBINARY.equals(type)) {
return WriteMapping.sliceMapping("mediumblob", varbinaryWriteFunction());
}
if (type instanceof CharType) {
return WriteMapping.sliceMapping("char(" + ((CharType) type).getLength() + ")", charWriteFunction());
}
if (type instanceof VarcharType) {
VarcharType varcharType = (VarcharType) type;
String dataType;
if (varcharType.isUnbounded()) {
dataType = "longtext";
}
else if (varcharType.getBoundedLength() <= 255) {
dataType = "tinytext";
}
else if (varcharType.getBoundedLength() <= 65535) {
dataType = "text";
}
else if (varcharType.getBoundedLength() <= 16777215) {
dataType = "mediumtext";
}
else {
dataType = "longtext";
}
return WriteMapping.sliceMapping(dataType, varcharWriteFunction());
}
if (type.equals(jsonType)) {
return WriteMapping.sliceMapping("json", varcharWriteFunction());
}
return legacyToWriteMapping(session, type);
}
@Override
public void createTable(ConnectorSession session, ConnectorTableMetadata tableMetadata)
{
try {
createTable(session, tableMetadata, tableMetadata.getTable().getTableName());
}
catch (SQLException e) {
boolean exists = SQL_STATE_ER_TABLE_EXISTS_ERROR.equals(e.getSQLState());
throw new PrestoException(exists ? ALREADY_EXISTS : JDBC_ERROR, e);
}
}
@Override
public void renameColumn(ConnectorSession session, JdbcTableHandle handle, JdbcColumnHandle jdbcColumn, String newColumnName)
{
try (Connection connection = connectionFactory.openConnection(session)) {
DatabaseMetaData metadata = connection.getMetaData();
if (metadata.storesUpperCaseIdentifiers()) {
newColumnName = newColumnName.toUpperCase(ENGLISH);
}
String sql = format(
"ALTER TABLE %s RENAME COLUMN %s TO %s",
quoted(handle.getCatalogName(), handle.getSchemaName(), handle.getTableName()),
quoted(jdbcColumn.getColumnName()),
quoted(newColumnName));
execute(connection, sql);
}
catch (SQLException e) {
// MySQL versions earlier than 8 do not support the above RENAME COLUMN syntax
if (SQL_STATE_SYNTAX_ERROR.equals(e.getSQLState())) {
throw new PrestoException(NOT_SUPPORTED, format("Rename column not supported in catalog: '%s'", handle.getCatalogName()), e);
}
throw new PrestoException(JDBC_ERROR, e);
}
}
@Override
protected void copyTableSchema(Connection connection, String catalogName, String schemaName, String tableName, String newTableName, List columnNames)
{
String tableCopyFormat = "CREATE TABLE %s AS SELECT * FROM %s WHERE 0 = 1";
if (isGtidMode(connection)) {
tableCopyFormat = "CREATE TABLE %s LIKE %s";
}
String sql = format(
tableCopyFormat,
quoted(catalogName, schemaName, newTableName),
quoted(catalogName, schemaName, tableName));
execute(connection, sql);
}
@Override
public void renameTable(ConnectorSession session, JdbcTableHandle handle, SchemaTableName newTableName)
{
// MySQL doesn't support specifying the catalog name in a rename. By setting the
// catalogName parameter to null, it will be omitted in the ALTER TABLE statement.
verify(handle.getSchemaName() == null);
renameTable(session, null, handle.getCatalogName(), handle.getTableName(), newTableName);
}
@Override
protected Optional> limitFunction()
{
return Optional.of((sql, limit) -> sql + " LIMIT " + limit);
}
@Override
public boolean isLimitGuaranteed(ConnectorSession session)
{
return true;
}
private ColumnMapping jsonColumnMapping()
{
return ColumnMapping.sliceMapping(
jsonType,
(resultSet, columnIndex) -> jsonParse(utf8Slice(resultSet.getString(columnIndex))),
varcharWriteFunction(),
DISABLE_PUSHDOWN);
}
private static boolean isGtidMode(Connection connection)
{
try (java.sql.Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SHOW VARIABLES LIKE 'gtid_mode'")) {
if (resultSet.next()) {
return !resultSet.getString("Value").equalsIgnoreCase("OFF");
}
return false;
}
catch (SQLException e) {
throw new PrestoException(JDBC_ERROR, e);
}
}
private static Optional getUnsignedMapping(JdbcTypeHandle typeHandle)
{
if (typeHandle.getJdbcTypeName().isEmpty()) {
return Optional.empty();
}
String typeName = typeHandle.getJdbcTypeName().get();
if (typeName.equalsIgnoreCase("tinyint unsigned")) {
return Optional.of(smallintColumnMapping());
}
if (typeName.equalsIgnoreCase("smallint unsigned")) {
return Optional.of(integerColumnMapping());
}
if (typeName.equalsIgnoreCase("int unsigned")) {
return Optional.of(bigintColumnMapping());
}
if (typeName.equalsIgnoreCase("bigint unsigned")) {
return Optional.of(decimalColumnMapping(createDecimalType(20)));
}
return Optional.empty();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy