Java tutorial
package com.tesora.dve.db.mysql; /* * #%L * Tesora Inc. * Database Virtualization Engine * %% * Copyright (C) 2011 - 2014 Tesora Inc. * %% * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License, version 3, * as published by the Free Software Foundation. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Affero General Public License for more details. * * You should have received a copy of the GNU Affero General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. * #L% */ import java.nio.charset.Charset; import java.sql.ParameterMetaData; import java.sql.SQLException; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import com.tesora.dve.charset.NativeCharSetCatalog; import com.tesora.dve.charset.NativeCollationCatalog; import com.tesora.dve.common.DBType; import com.tesora.dve.common.catalog.User; import com.tesora.dve.common.catalog.UserColumn; import com.tesora.dve.db.DBNative; import com.tesora.dve.db.Emitter; import com.tesora.dve.db.NativeType; import com.tesora.dve.db.mysql.MysqlNativeType.MysqlType; import com.tesora.dve.db.mysql.portal.protocol.MSPAuthenticateV10MessageMessage; import com.tesora.dve.errmap.AvailableErrors; import com.tesora.dve.errmap.ErrorInfo; import com.tesora.dve.exceptions.PEException; import com.tesora.dve.resultset.ColumnMetadata; import com.tesora.dve.server.connectionmanager.SSConnection; import com.tesora.dve.server.global.HostService; import com.tesora.dve.server.messaging.SQLCommand; import com.tesora.dve.singleton.Singletons; import com.tesora.dve.sql.SchemaException; import com.tesora.dve.sql.schema.ForeignKeyAction; import com.tesora.dve.sql.schema.types.Type; import com.tesora.dve.variables.VariableStoreSource; public class MysqlNative extends DBNative { private static final long serialVersionUID = 1L; private static final Logger logger = Logger.getLogger(MysqlNative.class); private final NativeCharSetCatalog supportedCharsets; private final NativeCollationCatalog supportedCollations; public MysqlNative(NativeCharSetCatalog charSetCat, NativeCollationCatalog nativeColCat, DBType dbType) throws PEException { this.supportedCharsets = charSetCat; this.supportedCollations = nativeColCat; super.setDbType(dbType); setIdentifierQuoteChar(MysqlNativeConstants.MYSQL_IDENTIFIER_QUOTE_CHAR); setLiteralQuoteChar(MysqlNativeConstants.MYSQL_LITERAL_QUOTE_CHAR); MysqlNativeTypeCatalog types = new MysqlNativeTypeCatalog(); setTypeCatalog(types); setEmitter(new MysqlEmitter()); setResultHandler(new MysqlNativeResultHandler()); setCharsetEncoding(MysqlNativeConstants.MYSQL_CHARSET_ENCODING); } @Override public NativeCharSetCatalog getSupportedCharSets() { return supportedCharsets; } @Override public NativeCollationCatalog getSupportedCollations() { return supportedCollations; } public MysqlNativeType getNativeTypeFromMyFieldType(MyFieldType mft, int flags, long maxLen) throws PEException { final MysqlNativeTypeCatalog typeCatalog = (MysqlNativeTypeCatalog) Singletons.require(DBNative.class) .getTypeCatalog(); return typeCatalog.findType(mft, flags, maxLen, true); } @Override public String getColumnDefForQuery(UserColumn uc) throws PEException { StringBuilder columnDef = new StringBuilder(); columnDef.append(getNameForQuery(uc)).append(" ").append(getDataTypeForQuery(uc, true)); return columnDef.toString(); } private String getDataTypeForQuery(UserColumn uc, boolean extras) throws PEException { MysqlNativeType typeInfo = (MysqlNativeType) findType(uc.getTypeName()); String typeName = typeInfo.getTypeName(); // DAS - HACK - we need to remove the "ALT" from the 3 BLOB types if (typeName.startsWith("alt")) { typeName = typeName.substring(3); // remove the "ALT" } StringBuilder sb = new StringBuilder(typeName); if (MysqlType.ENUM.equals(typeInfo.getMysqlType())) { // with enum, use the full type, including values sb = new StringBuilder(uc.getTypeName()); } else if (!typeInfo.getSupportsPrecision()) { // type requires neither precision nor scale, so we're done } else if (typeInfo.getSupportsPrecision() && !typeInfo.getSupportsScale()) { // type has precision String precision = Long.toString(uc.getSize()); if (uc.getSize() == 0) { // the uc has a size of 0 which means the user probably specified it like integer instead of integer(n) // grab the default precision from the type info instead precision = Long.toString(typeInfo.getPrecision()); } sb.append('(').append(precision).append(')'); } else { // type has precision and scale MysqlType mysqlType = typeInfo.getMysqlType(); // don't output precision and scale if precision is 0 if (uc.getPrecision() > 0) { // handle special case for MYSQL - DOUBLE with no (p,s) comes back as (22,31) which is invalid if ((MysqlType.DOUBLE.equals(mysqlType) || MysqlType.DOUBLE_PRECISION.equals(mysqlType) || MysqlType.FLOAT.equals(mysqlType)) && uc.getScale() == 31) { // do nothing } else { sb.append('(').append(uc.getPrecision()).append(',').append(uc.getScale()).append(')'); } } else { // special case for decimal and numeric if 0 is specified // for precision // if ((MysqlType.DECIMAL.equals(mysqlType) || MysqlType.NUMERIC.equals(mysqlType)) if ((MysqlType.DECIMAL.equals(mysqlType)) && uc.getScale() == 0) { if (uc.getSize() > 0) { // can specify the size without scale - make sure it isn't larger than max for create table (see PE-1232) sb.append('(').append(uc.getSize()).append(",0)"); } else { // figure out the default size sb.append('(').append(typeInfo.getPrecision()).append(",0)"); } } } } if (uc.getESUniverse() != null) sb.append("(").append(uc.getESUniverse()).append(")"); if (extras && typeInfo.isStringType()) { if (uc.getCharset() != null && !StringUtils.equalsIgnoreCase(MysqlNativeConstants.DB_CHAR_SET, uc.getCharset())) { sb.append(" CHARACTER SET ").append(uc.getCharset()); } if (uc.getCollation() != null) { sb.append(" COLLATE ").append(uc.getCollation()); } } // add any type modifiers to the end if (uc.isUnsigned()) sb.append(" unsigned"); if (uc.isZerofill()) sb.append(" zerofill"); return sb.toString(); } @Override public String getDataTypeForQuery(UserColumn uc) throws PEException { return getDataTypeForQuery(uc, false); } /* @Override public ColumnMetadata getResultSetColumnInfo(ResultSetMetaData rsmd, ProjectionInfo projection, int colIdx) throws SQLException { // TODO this doesn't handle default values String nativeTypeName = NativeType.fixName(rsmd.getColumnTypeName(colIdx)); ColumnMetadata cm = new ColumnMetadata(); if (projection != null) { ColumnInfo ci = projection.getColumnInfo(colIdx); cm.setName(ci.getName()); cm.setAliasName(ci.getAlias()); cm.setDbName(ci.getDatabaseName()); cm.setTableName(ci.getTableName()); if (ci.isSet(ColumnAttribute.AUTO_INCREMENT)) cm.setAutoGenerated(Boolean.TRUE); if (ci.isSet(ColumnAttribute.NULLABLE)) cm.setNullable(Boolean.TRUE); if (ci.isSet(ColumnAttribute.KEY_PART)) { if (ci.isSet(ColumnAttribute.PRIMARY_KEY_PART)) cm.primaryKey(); else if (ci.isSet(ColumnAttribute.UNIQUE_PART)) cm.uniqueKey(); else cm.nonUniqueKey(); } } else { cm.setName(rsmd.getColumnName(colIdx)); cm.setAliasName(rsmd.getColumnLabel(colIdx)); } cm.setDataType(rsmd.getColumnType(colIdx)); // for NativeTypeName we need to handle UNSIGNED. The // getColumnTypeName() method returns // UNSIGNED as part of the type name instead of a modifier to the type cm.setNativeTypeName(nativeTypeName); if (!rsmd.isSigned(colIdx) && nativeTypeName.contains(" " + MysqlNativeType.MODIFIER_UNSIGNED)) { cm.setNativeTypeName(nativeTypeName.replace(" " + MysqlNativeType.MODIFIER_UNSIGNED, "")); cm.setNativeTypeModifiers(MysqlNativeType.MODIFIER_UNSIGNED); } if (rsmd.getColumnType(colIdx) == Types.LONGVARCHAR) { // Mysql JDBC seems to return native type "VARCHAR" when the type is "TEXT" // or we could read the manual: // http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-type-conversions.html int prec = rsmd.getPrecision(colIdx); if (prec > 16777215) cm.setNativeTypeName(MysqlType.LONGTEXT.toString()); else if (prec > 65535) cm.setNativeTypeName(MysqlType.MEDIUMTEXT.toString()); else if (prec > 255) cm.setNativeTypeName(MysqlType.TEXT.toString()); else cm.setNativeTypeName(MysqlType.TINYTEXT.toString()); // Precision and size will be set here even though they are the same as // all other types. We may need to alter these based on Charset at some point cm.setPrecision(rsmd.getPrecision(colIdx)); cm.setSize(rsmd.getColumnDisplaySize(colIdx)); } else { cm.setPrecision(rsmd.getPrecision(colIdx)); cm.setSize(rsmd.getColumnDisplaySize(colIdx)); } cm.setScale(rsmd.getScale(colIdx)); return cm; } */ @Override public ColumnMetadata getParameterColumnInfo(ParameterMetaData pmd, int colIdx) throws SQLException { ColumnMetadata out = new ColumnMetadata(); out.setDataType(pmd.getParameterType(colIdx)); out.setPrecision(pmd.getPrecision(colIdx)); out.setScale(pmd.getScale(colIdx)); if (ParameterMetaData.parameterNullable == pmd.isNullable(colIdx)) out.setNullable(Boolean.TRUE); out.setTypeName(NativeType.fixName(pmd.getParameterTypeName(colIdx))); return out; } @Override public UserColumn updateUserColumn(UserColumn iuc, Type schemaType) { UserColumn uc = (iuc == null ? new UserColumn() : iuc); schemaType.persistTypeName(uc); uc.setDataType(schemaType.getDataType()); if (schemaType.hasSize()) { uc.setSize(schemaType.getSize()); if (schemaType.hasPrecisionAndScale()) { uc.setPrecision(schemaType.getPrecision()); uc.setScale(schemaType.getScale()); } else if (uc.getId() != 0) { uc.setPrecision(0); uc.setScale(0); } } else { uc.setSize(0); uc.setPrecision(0); uc.setScale(0); } schemaType.addColumnTypeModifiers(uc); return uc; } @Override public Emitter getEmitter() { return new MysqlEmitter(); } @Override public SQLCommand getDropDatabaseStmt(final VariableStoreSource vs, String databaseName) { return new SQLCommand(vs, buildDropDatabaseStmt(databaseName)); } @Override public SQLCommand getCreateDatabaseStmt(final VariableStoreSource vs, String databaseName, boolean ine, String defaultCharSet, String defaultCollation) { return new SQLCommand(vs, buildCreateDatabaseStmt(databaseName, ine, defaultCharSet, defaultCollation)); } @Override public SQLCommand getAlterDatabaseStmt(final VariableStoreSource vs, String databaseName, String defaultCharSet, String defaultCollation) { return new SQLCommand(vs, buildAlterDatabaseStmt(databaseName, defaultCharSet, defaultCollation)); } @Override public SQLCommand getCreateUserCommand(final VariableStoreSource vs, User user) { return new SQLCommand(vs, buildCreateUserCommand(user)); } @Override public SQLCommand getGrantPriviledgesCommand(final VariableStoreSource vs, String userDeclaration, String databaseName) { return new SQLCommand(vs, buildGrantPriviledgesCommand(userDeclaration, databaseName)); } @Override public SQLCommand getDropDatabaseStmt(final Charset connectionCharset, String databaseName) { return new SQLCommand(connectionCharset, buildDropDatabaseStmt(databaseName)); } @Override public SQLCommand getCreateDatabaseStmt(final Charset connectionCharset, String databaseName, boolean ine, String defaultCharSet, String defaultCollation) { return new SQLCommand(connectionCharset, buildCreateDatabaseStmt(databaseName, ine, defaultCharSet, defaultCollation)); } @Override public SQLCommand getAlterDatabaseStmt(final Charset connectionCharset, String databaseName, String defaultCharSet, String defaultCollation) { return new SQLCommand(connectionCharset, buildAlterDatabaseStmt(databaseName, defaultCharSet, defaultCollation)); } @Override public SQLCommand getCreateUserCommand(final Charset connectionCharset, User user) { return new SQLCommand(connectionCharset, buildCreateUserCommand(user)); } @Override public SQLCommand getGrantPriviledgesCommand(final Charset connectionCharset, String userDeclaration, String databaseName) { return new SQLCommand(connectionCharset, buildGrantPriviledgesCommand(userDeclaration, databaseName)); } private String buildDropDatabaseStmt(String databaseName) { return "DROP DATABASE IF EXISTS " + quoteIdentifier(databaseName); } private String buildCreateDatabaseStmt(String databaseName, boolean ine, String defaultCharSet, String defaultCollation) { final StringBuilder command = new StringBuilder("CREATE DATABASE "); if (ine) { command.append("IF NOT EXISTS "); } command.append(quoteIdentifier(databaseName)); command.append(" DEFAULT CHARACTER SET = ").append(defaultCharSet); command.append(" DEFAULT COLLATE = ").append(defaultCollation); return command.toString(); } private String buildAlterDatabaseStmt(String databaseName, String defaultCharSet, String defaultCollation) { final StringBuilder command = new StringBuilder("ALTER DATABASE "); command.append(quoteIdentifier(databaseName)); command.append(" DEFAULT CHARACTER SET = ").append(defaultCharSet); command.append(" DEFAULT COLLATE = ").append(defaultCollation); return command.toString(); } private String buildCreateUserCommand(User user) { // switch to doing a grant all - lets create be idempotent return buildGrantPriviledgesCommand(getUserDeclaration(user, true), "*"); } private String buildGrantPriviledgesCommand(String userDeclaration, String databaseName) { return "GRANT ALL PRIVILEGES ON " + databaseName + ".* TO " + userDeclaration; } @Override public String getUserDeclaration(User user, boolean pword) { StringBuffer buf = new StringBuffer(); buf.append("'").append(user.getName()).append("'@'").append(user.getAccessSpec()).append("'"); // NOPMD by doug on 30/11/12 3:23 PM if (pword && user.getPlaintextPassword() != null) buf.append(" IDENTIFIED BY '").append(user.getPlaintextPassword()).append("'"); // NOPMD by doug on 30/11/12 3:23 PM return buf.toString(); } @Override public String getEmptyCatalogName() { return "mysql"; } @Override public String getPasswordForAuth(User user, SSConnection ssConn) throws Exception { return MSPAuthenticateV10MessageMessage.computeSecurePasswordString(user.getPlaintextPassword(), ssConn.getHandshake().getSalt()); } @Override public String getSessionVariableConfigName() { return "com/tesora/dve/db/mysql/mysqlSessionConfig.xml"; } @Override public String getStatusVariableConfigName() { return "com/tesora/dve/db/mysql/mysqlStatusConfig.xml"; } @Override public String getSetSessionVariableStatement(String assignmentClause) { return "SET SESSION " + assignmentClause; } @Override public String getSetAutocommitStatement(String value) { return "SET autocommit = " + value; } @Override public void assertValidCharacterSet(String value) throws PEException { if (!getSupportedCharSets().isCompatibleCharacterSet(value)) { throw new SchemaException(new ErrorInfo(AvailableErrors.UNKNOWN_CHARACTER_SET, value)); } } @Override public void assertValidCollation(String value) throws PEException { if (!getSupportedCollations().isCompatibleCollation(value)) { throw new SchemaException(new ErrorInfo(AvailableErrors.UNKNOWN_COLLATION, value)); } } @Override public String getDefaultServerCharacterSet() { return "utf8"; } @Override public String getDefaultServerCollation() { return "utf8_general_ci"; } @Override public String getDefaultServerBinaryCollation() { return "utf8_bin"; } @Override public boolean beginImpliesCommit() { return true; } @Override public boolean ddlImpliesCommit() { return true; } @Override public boolean exceptionAbortsTxn(PEException e) { // return e.hasCause(MySQLTransactionRollbackException.class); return false; } @Override public int convertTransactionIsolationLevel(String in) throws PEException { if (in == null) throw new PEException("Missing isolation level"); MySQLTransactionIsolation isol = MySQLTransactionIsolation.find(in); if (isol == null) throw new PEException("Unknown isolation level '" + in + "'"); return isol.getJdbcConstant(); } // use the mysql names - the ones you'd see in the show variables listing @Override public String convertTransactionIsolationLevel(int level) throws PEException { MySQLTransactionIsolation isol = MySQLTransactionIsolation.find(level); if (isol == null) throw new PEException("Unknown transaction isolation level value: " + level); return isol.getExternalName(); } @Override public String getTableRenameStatement(String existingTableName, String newTableName) { return "ALTER TABLE " + quoteIdentifier(existingTableName) + " RENAME " + quoteIdentifier(newTableName); } @Override public int getMaxAliasNameLen() { return 64; } @Override public void convertColumnMetadataToUserColumn(ColumnMetadata cm, UserColumn uc) throws PEException { super.convertColumnMetadataToUserColumn(cm, uc); NativeType nt = this.findType(uc.getTypeName()); if (nt.isNumericType()) { if (uc.getPrecision() > nt.getMaxPrecision()) { uc.setPrecision((int) nt.getMaxPrecision()); } if (uc.getSize() > nt.getMaxPrecision()) { uc.setSize((int) nt.getMaxPrecision()); } } else { // A non-numeric type with scale/precision may be returned. // @see coallesce(VARCHAR, VARCHAR) // Clear the properties. uc.setPrecision(0); uc.setScale(0); } } @Override public int getMaxNumColsInIndex() { return 16; } @Override public ForeignKeyAction getDefaultOnDeleteAction() { return ForeignKeyAction.RESTRICT; } @Override public ForeignKeyAction getDefaultOnUpdateAction() { return ForeignKeyAction.RESTRICT; } @Override public long getMaxTableCommentLength() { return 2048; } @Override public long getMaxTableFieldCommentLength() { return 1024; } }