/**********************************************************************
Copyright (c) 2003 Mike Martin (TJDO) and others. All rights reserved.
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.
Contributors:
2003 Andy Jefferson - added getCreateTableStatement() method and commented
2004 Andy Jefferson - fixed convert expression
...
**********************************************************************/
package org.jpox.store.rdbms.adapter;
import java.math.BigInteger;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.jpox.store.mapped.DatastoreContainerObject;
import org.jpox.store.mapped.DatastoreIdentifier;
import org.jpox.store.mapped.IdentifierFactory;
import org.jpox.store.mapped.expression.BooleanExpression;
import org.jpox.store.mapped.expression.Literal;
import org.jpox.store.mapped.expression.LogicSetExpression;
import org.jpox.store.mapped.expression.NumericExpression;
import org.jpox.store.mapped.expression.QueryExpression;
import org.jpox.store.mapped.expression.ScalarExpression;
import org.jpox.store.mapped.expression.StringExpression;
import org.jpox.store.mapped.expression.TableExprAsJoins;
import org.jpox.store.rdbms.Column;
import org.jpox.store.rdbms.columninfo.ColumnInfo;
import org.jpox.store.rdbms.columninfo.MySQLColumnInfo;
import org.jpox.store.rdbms.key.PrimaryKey;
import org.jpox.store.rdbms.table.Table;
import org.jpox.store.rdbms.table.TableImpl;
import org.jpox.store.rdbms.typeinfo.MySQLTypeInfo;
import org.jpox.store.rdbms.typeinfo.TypeInfo;
/**
* Provides methods for adapting SQL language elements to the MySQL database.
* Overrides some methods in DatabaseAdapter where MySQL behaviour differs.
*
* @version $Revision: 1.51 $
*/
public class MySQLAdapter extends DatabaseAdapter
{
/**
* A string containing the list of MySQL keywords that are not also SQL/92
* <i>reserved words</i>, separated by commas.
* This list is normally obtained dynamically from the driver using
* DatabaseMetaData.getSQLKeywords(), but MySQL drivers are known to return
* an incomplete list.
* <p>
* This list was produced based on the reserved word list in the MySQL
* Manual (Version 4.0.10-gamma) at
* http://www.mysql.com/doc/en/Reserved_words.html.
*/
public static final String NONSQL92_RESERVED_WORDS =
"ANALYZE,AUTO_INCREMENT,BDB,BERKELEYDB,BIGINT,BINARY,BLOB,BTREE," +
"CHANGE,COLUMNS,DATABASE,DATABASES,DAY_HOUR,DAY_MINUTE,DAY_SECOND," +
"DELAYED,DISTINCTROW,DIV,ENCLOSED,ERRORS,ESCAPED,EXPLAIN,FIELDS," +
"FORCE,FULLTEXT,FUNCTION,GEOMETRY,HASH,HELP,HIGH_PRIORITY," +
"HOUR_MINUTE,HOUR_SECOND,IF,IGNORE,INDEX,INFILE,INNODB,KEYS,KILL," +
"LIMIT,LINES,LOAD,LOCALTIME,LOCALTIMESTAMP,LOCK,LONG,LONGBLOB," +
"LONGTEXT,LOW_PRIORITY,MASTER_SERVER_ID,MEDIUMBLOB,MEDIUMINT," +
"MEDIUMTEXT,MIDDLEINT,MINUTE_SECOND,MOD,MRG_MYISAM,OPTIMIZE," +
"OPTIONALLY,OUTFILE,PURGE,REGEXP,RENAME,REPLACE,REQUIRE,RETURNS," +
"RLIKE,RTREE,SHOW,SONAME,SPATIAL,SQL_BIG_RESULT,SQL_CALC_FOUND_ROWS," +
"SQL_SMALL_RESULT,SSL,STARTING,STRAIGHT_JOIN,STRIPED,TABLES," +
"TERMINATED,TINYBLOB,TINYINT,TINYTEXT,TYPES,UNLOCK,UNSIGNED,USE," +
"USER_RESOURCES,VARBINARY,VARCHARACTER,WARNINGS,XOR,YEAR_MONTH," +
"ZEROFILL";
/**
* Constructor.
* Overridden so we can add on our own list of NON SQL92 reserved words
* which is returned incorrectly with the JDBC driver.
* @param metadata MetaData for the DB
**/
public MySQLAdapter(DatabaseMetaData metadata)
{
super(metadata);
reservedKeywords.addAll(parseKeywordList(NONSQL92_RESERVED_WORDS));
// Add on any missing JDBC types
TypeInfo ti = new MySQLTypeInfo("MEDIUMBLOB",
(short)Types.BLOB,
2147483647,
null,
null,
null,
1,
false,
(short)1,
false,
false,
false,
"MEDIUMBLOB",
(short)0,
(short)0,
0);
addTypeInfo((short)Types.BLOB, ti, true);
ti = new MySQLTypeInfo("MEDIUMTEXT",
(short)Types.CLOB,
2147483647,
null,
null,
null,
1,
true,
(short)1,
false,
false,
false,
"MEDIUMTEXT",
(short)0,
(short)0,
0);
addTypeInfo((short)Types.CLOB, ti, true);
}
public String getVendorID()
{
return "mysql";
}
/**
* A factory for ColumnInfo objects. This method should always be used
* instead of directly constructing ColumnInfo objects in order to give the
* DatabaseAdapter an opportunity to modify and/or correct the metadata
* obtained from the JDBC driver.
*
* The column information object is constructed from the current row of the
* given result set. The {@link ResultSet} object passed must have been
* obtained from a call to DatabaseMetaData.getColumns().
*
* <p>The constructor only retrieves the values from the current row; the
* caller is required to advance to the next row with {@link ResultSet#next}
*
* @param rs The result set returned from DatabaseMetaData.getColumns().
* @return The column info
*/
public ColumnInfo newColumnInfo(ResultSet rs)
{
if (this.driverMajorVersion <= 3)
{
// driver 3.1.13 has some issues with BLOB, CLOB
return new MySQLColumnInfo(rs);
}
//while driver 5.x does not
return super.newColumnInfo(rs);
}
/**
* Factory for TypeInfo objects.
* @param rs The ResultSet from DatabaseMetaData.getTypeInfo().
* @return A TypeInfo object.
**/
public TypeInfo newTypeInfo(ResultSet rs)
{
TypeInfo ti=new MySQLTypeInfo(rs);
// Exclude BINARY and VARBINARY since these equate to CHAR(M) BINARY
// and VARCHAR(M) BINARY respectively, which aren't true binary
// types (e.g. trailing space characters are stripped).
if (ti.typeName.equalsIgnoreCase("binary") ||
ti.typeName.equalsIgnoreCase("varbinary"))
{
return null;
}
return ti;
}
// ------------------------------- Schema Methods ------------------------------------
/**
* MySQL, when using AUTO_INCREMENT, requires the primary key specified
* in the CREATE TABLE, so we do nothing here.
* @param pk An object describing the primary key.
* @param factory Identifier factory
* @return The PK statement
*/
public String getAddPrimaryKeyStatement(PrimaryKey pk, IdentifierFactory factory)
{
return null;
}
/**
* Whether this datastore supports ALTER TABLE DROP constraints
* @return whether we support ALTER TABLE DROP constraints
**/
public boolean supportsAlterTableDropConstraint()
{
return false;
}
/**
* Whether this datastore supports ALTER TABLE DROP FOREIGN KEY constraint.
* @return whether we support ALTER TABLE DROP FOREIGN KEY constraints
**/
public boolean supportsAlterTableDropForeignKeyConstraint()
{
if (datastoreMajorVersion < 4 ||
(datastoreMajorVersion == 4 && datastoreMinorVersion == 0 &&
datastoreRevisionVersion < 13))
{
return false;
}
else
{
// MySQL version 4.0.13 started supporting this syntax
return true;
}
}
/**
* Whether this datastore supports deferred constraints.
* @return whether we support deferred constraints.
**/
public boolean supportsDeferredConstraints()
{
return false;
}
/**
* Whether the datastore supports specification of the primary key in
* CREATE TABLE statements.
* @return Whetehr it allows "PRIMARY KEY ..."
*/
public boolean supportsPrimaryKeyInCreateStatements()
{
return true;
}
/**
* Whether any DEFAULT tag will be before any NULL/NOT NULL in the column options.
* @return Whether to put DEFAULT before NULL
*/
public boolean supportsDefaultBeforeNullInColumnOptions()
{
// Put NULL before DEFAULT
return false;
}
/**
* Method to return the CREATE TABLE statement.
* Versions before 5 need INNODB table type selecting for them.
* It seems, MySQL >= 5 still needs innodb in order to support transactions.
* @param table The table
* @param columns The columns in the table
* @param props Properties for controlling the table creation
* @return The creation statement
**/
public String getCreateTableStatement(TableImpl table, Column[] columns, Properties props)
{
StringBuffer createStmt = new StringBuffer(super.getCreateTableStatement(table, columns, props));
String engineType = "INNODB";
if (props != null)
{
// Check for specification of the "engine"
if (props.containsKey("mysql-engine-type"))
{
engineType = props.getProperty("mysql-engine-type");
}
}
boolean engineKeywordPresent = false;
if (datastoreMajorVersion >= 5 ||
(datastoreMajorVersion == 4 && datastoreMinorVersion >= 1 && datastoreRevisionVersion >= 2) ||
(datastoreMajorVersion == 4 && datastoreMinorVersion == 0 && datastoreRevisionVersion >= 18))
{
// "ENGINE=" was introduced in 4.1.2 and 4.0.18 (http://dev.mysql.com/doc/refman/4.1/en/create-table.html)
engineKeywordPresent = true;
}
if (engineKeywordPresent)
{
createStmt.append(" ENGINE=" + engineType);
}
else
{
createStmt.append(" TYPE=" + engineType);
}
return createStmt.toString();
}
/**
* Method to return the DROP TABLE statement.
* Override the default omitting the CASCADE part since MySQL doesn't
* support that.
* @param table The table
* @return The drop statement
**/
public String getDropTableStatement(DatastoreContainerObject table)
{
return "DROP TABLE " + table.toString();
}
/**
* Whether the RDBMS supports use of EXISTS syntax.
* @return Whether EXISTS is supported.
*/
public boolean supportsExistsSyntax()
{
if (datastoreMajorVersion < 5)
{
if (datastoreMajorVersion < 4 || datastoreMinorVersion < 1)
{
// Support starts at MySQL 4.1
return false;
}
}
return true;
}
/**
* Whether the RDBMS supports use of UNION syntax.
* @return Whether UNION is supported.
*/
public boolean supportsUnionSyntax()
{
if (datastoreMajorVersion < 4)
{
return false;
}
return true;
}
/**
* Accessor for whether setting a BLOB value allows use of PreparedStatement.setString()
* @return Whether setString is allowed.
*/
public boolean supportsSettingBlobUsingSetString()
{
// Need setString with MySQL since we are providing the BLOB mapping directly anyway
return true;
}
/**
* Accessor for whether setting a CLOB value allows use of PreparedStatement.setString()
* @return Whether setString is allowed.
*/
public boolean supportsSettingClobUsingSetString()
{
// Need setString with MySQL since we are providing the CLOB mapping directly anyway
return true;
}
/**
* Whether to create indexes before foreign keys.
* @return Whether to create indexes before foreign keys
**/
public boolean createIndexesBeforeForeignKeys()
{
return true;
}
/**
* Accessor for the SQL statement to add a column to a table.
* @param table The table
* @param col The column
* @return The SQL necessary to add the column
*/
public String getAddColumnStatement(DatastoreContainerObject table, Column col)
{
return "ALTER TABLE " + table.toString() + " ADD COLUMN " + col.getSQLDefinition();
}
// ------------------------------- Identity Methods ------------------------------------
/**
* Whether we support autoincrementing fields.
* @return whether we support autoincrementing fields.
**/
public boolean supportsIdentityFields()
{
return true;
}
/**
* Accessor for the auto-increment sql statement for this datastore.
* @param table Name of the table that the autoincrement is for
* @param columnName Name of the column that the autoincrement is for
* @return The statement for getting the latest auto-increment key
**/
public String getAutoIncrementStmt(Table table, String columnName)
{
return "SELECT LAST_INSERT_ID()";
}
/**
* Accessor for the auto-increment keyword for generating DDLs (CREATE TABLEs...).
* @return The keyword for a column using auto-increment
**/
public String getAutoIncrementKeyword()
{
return "AUTO_INCREMENT";
}
/**
* The function to creates a unique value of type uniqueidentifier.
* MySQL generates 36-character hex uuids.
* @return The function. e.g. "SELECT uuid()"
**/
public String getSelectNewUUIDStmt()
{
return "SELECT uuid()";
}
// ----------------------------------- Query Methods ------------------------------------
/**
* Accessor for whether the SQL extensions CUBE, ROLLUP are supported.
* @return Whether the SQL extensions CUBE, ROLLUP are supported.
*/
public boolean supportsAnalysisMethods()
{
// MySQL actually supports "WITH ROLLUP" instead of putting it in GROUP BY.
// It doesnt support CUBE seemingly
return false;
}
/**
* Method to return the SQL to append to the WHERE clause of a SELECT statement to handle
* restriction of ranges using the LIMUT keyword.
* @param offset The offset to return from
* @param count The number of items to return
* @return The SQL to append to allow for ranges using LIMIT.
*/
public String getRangeByLimitWhereClause(long offset, long count)
{
if (offset >= 0 && count > 0)
{
return " LIMIT " + offset + "," + count;
}
else if (offset <= 0 && count > 0)
{
return " LIMIT " + count;
}
else if (offset >= 0 && count < 0)
{
// MySQL doesnt allow just offset so use Long.MAX_VALUE as count
return " LIMIT " + offset + "," + Long.MAX_VALUE;
}
else
{
return "";
}
}
/**
* Return a new TableExpression appropriate to MySQL. MySQL does not
* support the TableExprAsSubjoins so instead we use TableExprAsJoins.
* @param qs The QueryStatement to add the expression to
* @param table The table in the expression
* @param rangeVar range variable to assign to the expression.
* @return The expression.
**/
public LogicSetExpression newTableExpression(QueryExpression qs, DatastoreContainerObject table, DatastoreIdentifier rangeVar)
{
return new TableExprAsJoins(qs, table, rangeVar);
}
/**
* <p>
* If only one operand expression is of type String, then string conversion
* is performed on the other operand to produce a string at run time. The
* result is a reference to a String object (newly created, unless the
* expression is a compile-time constant expression (�15.28))that is the
* concatenation of the two operand strings. The characters of the left-hand
* operand precede the characters of the right-hand operand in the newly
* created string. If an operand of type String is null, then the string
* "null" is used instead of that operand. "null" is used instead of that
* operand.
* </p>
* <p>
* Concatenates two or more character or binary strings, columns, or a
* combination of strings and column names into one expression (a string
* operator).
* </p>
* @param operand1 the left expression
* @param operand2 the right expression
* @return The Expression for concatenation
*/
public ScalarExpression concatOperator(ScalarExpression operand1, ScalarExpression operand2)
{
ArrayList args = new ArrayList();
args.add(operand1);
args.add(operand2);
return new StringExpression("CONCAT", args);
}
/**
* Method to handle the starts with operation.
* @param source The expression with the searched string
* @param str The expression for the search string
* @return The expression.
**/
public BooleanExpression startsWithMethod(ScalarExpression source, ScalarExpression str)
{
ScalarExpression integerLiteral = getMapping(BigInteger.class, source).newLiteral(source.getQueryExpression(), BigInteger.ONE);
ArrayList args = new ArrayList();
args.add(str);
args.add(source);
return new BooleanExpression(new StringExpression("LOCATE", args),ScalarExpression.OP_EQ,integerLiteral);
}
/**
* Whether this datastore supports locking using SELECT ... FOR UPDATE.
* @return whether we support locking using SELECT ... FOR UPDATE.
**/
public boolean supportsLockWithSelectForUpdate()
{
return true;
}
/**
* The character for escaping patterns.
* @return Escape character(s)
**/
public String getEscapePatternExpression()
{
return "ESCAPE '\\\\'";
}
/**
* A String conversion that converts a numeric expression to string.
* If the expr argument represents a Literal value, converts to a Literal string.
* In SQL, it should compile something like:
* <p>
* <blockquote>
* <pre>
* CAST(999999 AS VARCHAR(4000))
* </pre>
* </blockquote>
* </p>
* @param expr The NumericExpression
* @return the StringExpression
*/
public StringExpression toStringExpression(NumericExpression expr)
{
if (expr instanceof Literal)
{
return super.toStringExpression(expr);
}
List args = new ArrayList();
args.add(expr);
List types = new ArrayList();
types.add("CHAR(4000)");
return new StringExpression("CAST", args, types);
}
/**
* Generates a expression that represents the cartesian product of two sets: <code>X</code> and <code>Y</code>.
* Actually, <code>X</code> is not generated to the expression.
* @param Y right hand set
* @return the cartesion product expression. if the cartesian product expression is "X x Y", the returned expression
* is " x Y". Note that the left hand set was not introduced to the expression
**/
public String cartersianProduct(LogicSetExpression Y)
{
StringBuffer sb = new StringBuffer();
sb.append(" CROSS JOIN ");
sb.append(Y.toString());
return sb.toString();
}
}