Package net.sf.basedb.core.dbengine

Class MySQLEngine

java.lang.Object

net.sf.basedb.core.dbengine.AbstractDbEngine

net.sf.basedb.core.dbengine.MySQLEngine

All Implemented Interfaces:

DbEngine

public class MySQLEngine
extends AbstractDbEngine
implements DbEngine

Database engine for My SQL.

Version:

2.0

Author:

nicklas

Last modified

$Date: 2016-02-15 11:58:10 +0100 (må, 15 feb 2016) $

Constructor Summary

Constructors

Constructor	Description
MySQLEngine()	Create MySQLEngine.

Method Summary

Modifier and Type	Method	Description
boolean	analyzeAfterBatchOperation()	Return true.
private void	appendList(StringBuilder sb, Collection<String> items)
boolean	caseInsensitiveComparison()	Returns TRUE.
boolean	dropForeignKeysUsedInIndex()	When dropping an index, must foreign keys that uses the same columns also be dropped (before dropping the index)?
String	getAnalyzeTableSql(String catalog, String schema, String table)	Generate analyze table <catalog>.<table>.
String	getApproximateRowCountSql(String catalog, String schema, String table)	Get an SQL statement that returns an approximate count for the number of rows in the given table.
String	getCaseSensitiveVarchar(int length)	Return varchar(length) CHARACTER SET utf8 COLLATE utf8_bin.
String	getCatalogName(String catalog, String schema)	Generates a catalog name by combining the catalog and schema name (catalog_schema).
String	getCreateForeignKeySql(String catalog, String schema, String table, String name, Set<String> columns, String refTable, Set<String> refColumns)	Generate alter table schema.table add constraint name foreign key (columns) references refTable (refColumns)
String	getCreateIndexSql(String catalog, String schema, String table, String name, Set<String> columns, boolean unique)	Generate alter table <catalog>.<table> add [unique] index <name> (<columns>).
String	getCreatePrimaryKeySql(String catalog, String schema, String table, String name, Set<String> columns)	Generate alter table catalog.table add primary key (columns)
String	getCreateSchemaSql(String catalog, String schema)	Generate create schema <catalog>.
String	getDropForeignKeySql(String catalog, String schema, String table, String name)	Generate alter table <catalog>.<table> drop foreign key <name>
String	getDropIndexSql(String catalog, String schema, String table, String name, boolean unique)	Generate drop index <name> on <catalog>.<table>.
String	getDropPrimaryKey(String catalog, String schema, String table, String name)	Generate alter table catalog.table drop primary key
String	getInsertAutoIncrementSql(PersistentClass pc)	Return a SQL fragment that can be used in an INSERT INTO statement to generate an ID for new rows.
String	getOptimizeTableSql(String catalog, String schema, String table)	Generate optimize table <catalog>.<table>.
String	getQuotedName(String name)	Put quotes (`) around the name.
String	getSchemaName(String catalog, String schema)	Return null.
String	ln(String value)	Return LOG(<value>).
String	makeSafeCreateTable(String sql, String catalog, String schema, String table)	Replace "create table" with "create table if not exists"...
String	rlike(String value, String regexp)	Return <value> RLIKE <regexp>.
boolean	supportColumnAliasInGroupBy()	Returns TRUE.
boolean	supportColumnAliasInHaving()	Returns TRUE.
boolean	supportColumnAliasInOrderBy()	Returns TRUE.
boolean	supportsColumnAliasInWhere()	Returns FALSE.
boolean	useThetaJoin()	Always false.

Methods inherited from class net.sf.basedb.core.dbengine.AbstractDbEngine

abs, castToDate, checkForInvalidNumberOperation, exp, getCreatePartialIndexSql, getMaxParametersInQuery, inspectSchemaGenerationSQL, isValidColumnName, isValidName, isValidTableName, power, selectOrderByColumnsIfDistinct, useSavePointToContinueTransactionFromSqlFailure

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface net.sf.basedb.core.dbengine.DbEngine

abs, castToDate, checkForInvalidNumberOperation, exp, getCreatePartialIndexSql, getCreateTemporaryIdTable, getMaxParametersInQuery, inspectSchemaGenerationSQL, isValidColumnName, isValidTableName, power, selectOrderByColumnsIfDistinct, useSavePointToContinueTransactionFromSqlFailure

Constructor Details

MySQLEngine

public MySQLEngine()

Create MySQLEngine.

Method Details

useThetaJoin

public boolean useThetaJoin()

Always false.

Specified by:

useThetaJoin in interface DbEngine

Overrides:

useThetaJoin in class AbstractDbEngine

Returns:

TRUE for theta join, FALSE for ansi join

getCatalogName

public String getCatalogName(String catalog,
 String schema)

Generates a catalog name by combining the catalog and schema name (catalog_schema).

Specified by:

getCatalogName in interface DbEngine

Overrides:

getCatalogName in class AbstractDbEngine

Parameters:

catalog - The catalog name (always not null)

schema - The schema name (always not null)

Returns:

Should return a non-null value

Since:

3.4

getSchemaName

public String getSchemaName(String catalog,
 String schema)

Return null.

Specified by:

getSchemaName in interface DbEngine

Overrides:

getSchemaName in class AbstractDbEngine

Parameters:

catalog - The catalog name (always not null)

schema - The schema name (always not null)

Returns:

The schema name or null to indicate that schemas are not used

Since:

3.4

getCreateSchemaSql

public String getCreateSchemaSql(String catalog,
 String schema)

Generate create schema <catalog>.

Specified by:

getCreateSchemaSql in interface DbEngine

Since:

3.4

getCreateIndexSql

public String getCreateIndexSql(String catalog,
 String schema,
 String table,
 String name,
 Set<String> columns,
 boolean unique)

Generate alter table <catalog>.<table> add [unique] index <name> (<columns>). The schema parameter is not supported.

Specified by:

getCreateIndexSql in interface DbEngine

Parameters:

catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog

schema - The name of the schema where the table is located, or null if is located in the current schema

table - The name of the table

name - The name of the index to be created

columns - The name of the columns in the index

unique - If the index must contain unique values or not

Returns:

The SQL to execute

dropForeignKeysUsedInIndex

public boolean dropForeignKeysUsedInIndex()

Description copied from interface: DbEngine

When dropping an index, must foreign keys that uses the same columns also be dropped (before dropping the index)? Default is FALSE, but MySQL need TRUE.

Specified by:

dropForeignKeysUsedInIndex in interface DbEngine

Overrides:

dropForeignKeysUsedInIndex in class AbstractDbEngine

Returns:

TRUE

Since:

3.6

getDropIndexSql

public String getDropIndexSql(String catalog,
 String schema,
 String table,
 String name,
 boolean unique)

Generate drop index <name> on <catalog>.<table>. The schema parameter is not supported.

Specified by:

getDropIndexSql in interface DbEngine

Parameters:

catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog

schema - The name of the schema where the table is located, or null if is located in the current schema

table - The name of the table

name - The name of the index

unique - If the index contains unique values or not

Returns:

The SQL to execute

getCreateForeignKeySql

public String getCreateForeignKeySql(String catalog,
 String schema,
 String table,
 String name,
 Set<String> columns,
 String refTable,
 Set<String> refColumns)

Generate alter table schema.table add constraint name foreign key (columns) references refTable (refColumns)

Specified by:

getCreateForeignKeySql in interface DbEngine

Parameters:

catalog - The name of the catalog (database) where the tables are located, or null if it is located in the current catalog

schema - The name of the schema where the tables are located, or null if is located in the current schema

table - The name of the foreign key table

name - The name of the foreign key

columns - The columns that make up the foreign key

refTable - The table that is referenced by the foreign key

refColumns - The columns that are referenced by the foreign key

Returns:

The SQL to create the foreign key

Since:

3.1

getDropForeignKeySql

public String getDropForeignKeySql(String catalog,
 String schema,
 String table,
 String name)

Generate alter table <catalog>.<table> drop foreign key <name>

Specified by:

getDropForeignKeySql in interface DbEngine

Parameters:

catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog

schema - The name of the schema where the table is located, or null if is located in the current schema

table - The name of the table

name - The name of the foreign key

Returns:

The SQL to execute

Since:

3.0

getCreatePrimaryKeySql

public String getCreatePrimaryKeySql(String catalog,
 String schema,
 String table,
 String name,
 Set<String> columns)

Generate alter table catalog.table add primary key (columns)

Specified by:

getCreatePrimaryKeySql in interface DbEngine

Parameters:

catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog

schema - The name of the schema where the table is located, or null if is located in the current schema

table - The name of the table

name - The name of the primary key

columns - The name of the columns in the primary key

Returns:

The SQL to execute

Since:

3.1

getDropPrimaryKey

public String getDropPrimaryKey(String catalog,
 String schema,
 String table,
 String name)

Generate alter table catalog.table drop primary key

Specified by:

getDropPrimaryKey in interface DbEngine

Parameters:

catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog

schema - The name of the schema where the table is located, or null if is located in the current schema

table - The name of the table

name - The name of the primary key

Returns:

The SQL to execute

Since:

3.1

getInsertAutoIncrementSql

public String getInsertAutoIncrementSql(PersistentClass pc)

Description copied from interface: DbEngine

Return a SQL fragment that can be used in an INSERT INTO statement to generate an ID for new rows.

Specified by:

getInsertAutoIncrementSql in interface DbEngine

Parameters:

pc - The persistent class

Returns:

Always "null" (assuming that the ID column is an AUTO_INCREMENT column)

analyzeAfterBatchOperation

public boolean analyzeAfterBatchOperation()

Return true.

Specified by:

analyzeAfterBatchOperation in interface DbEngine

getOptimizeTableSql

public String getOptimizeTableSql(String catalog,
 String schema,
 String table)

Generate optimize table <catalog>.<table>. The schema parameter is not supported.

Specified by:

getOptimizeTableSql in interface DbEngine

Parameters:

catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog

schema - The name of the schema where the table is located, or null if is located in the current schema

table - The name of the table to optimize

Returns:

The SQL to execute

See Also:

• DbEngine.getAnalyzeTableSql(String, String, String)

getAnalyzeTableSql

public String getAnalyzeTableSql(String catalog,
 String schema,
 String table)

Generate analyze table <catalog>.<table>. The schema parameter is not supported.

Specified by:

getAnalyzeTableSql in interface DbEngine

Parameters:

catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog

schema - The name of the schema where the table is located, or null if is located in the current schema

table - The name of the table to analyze

Returns:

The SQL to execute, or null if not supported

See Also:

• DbEngine.getOptimizeTableSql(String, String, String)

makeSafeCreateTable

public String makeSafeCreateTable(String sql,
 String catalog,
 String schema,
 String table)

Replace "create table" with "create table if not exists"...

Specified by:

makeSafeCreateTable in interface DbEngine

Overrides:

makeSafeCreateTable in class AbstractDbEngine

Parameters:

sql - The original SQL

catalog - The name of the catalog (database) where the table is being create, or null if it is located in the current catalog

schema - The name of the schema where the table is being created, or null if is located in the current schema

table - The name of the table that is being created

Returns:

The modified or unmodified SQL statement

Since:

2.6

caseInsensitiveComparison

public boolean caseInsensitiveComparison()

Returns TRUE.

Specified by:

caseInsensitiveComparison in interface DbEngine

Returns:

FALSE if comparisons are case sensitive (ABC != abc), TRUE if they are case insensitive (ABC = abc)

Since:

2.4

supportsColumnAliasInWhere

public boolean supportsColumnAliasInWhere()

Returns FALSE.

Specified by:

supportsColumnAliasInWhere in interface DbEngine

Overrides:

supportsColumnAliasInWhere in class AbstractDbEngine

Returns:

TRUE if column aliases are supported, FALSE otherwise

supportColumnAliasInOrderBy

public boolean supportColumnAliasInOrderBy()

Returns TRUE.

Specified by:

supportColumnAliasInOrderBy in interface DbEngine

Overrides:

supportColumnAliasInOrderBy in class AbstractDbEngine

Returns:

TRUE if column aliases are supported, FALSE otherwise

supportColumnAliasInGroupBy

public boolean supportColumnAliasInGroupBy()

Returns TRUE.

Specified by:

supportColumnAliasInGroupBy in interface DbEngine

Overrides:

supportColumnAliasInGroupBy in class AbstractDbEngine

Returns:

TRUE if column aliases are supported, FALSE otherwise

supportColumnAliasInHaving

public boolean supportColumnAliasInHaving()

Returns TRUE.

Specified by:

supportColumnAliasInHaving in interface DbEngine

Overrides:

supportColumnAliasInHaving in class AbstractDbEngine

Returns:

TRUE if column aliases are supported, FALSE otherwise

ln

public String ln(String value)

Return LOG(<value>).

Specified by:

ln in interface DbEngine

Overrides:

ln in class AbstractDbEngine

Parameters:

value - The value to take the logarithm of

Returns:

The function call that calculates the logartihm

rlike

public String rlike(String value,
 String regexp)

Return <value> RLIKE <regexp>.

Specified by:

rlike in interface DbEngine

Overrides:

rlike in class AbstractDbEngine

Parameters:

value - The left value

regexp - The regular expression as a string

Returns:

The function call/expression that matches the value agains the regular expression

Since:

2.8

getCaseSensitiveVarchar

public String getCaseSensitiveVarchar(int length)

Return varchar(length) CHARACTER SET utf8 COLLATE utf8_bin.

Specified by:

getCaseSensitiveVarchar in interface DbEngine

Overrides:

getCaseSensitiveVarchar in class AbstractDbEngine

Parameters:

length - The maximum length that needs to be stored in the column

Returns:

The SQL snippet that creates the column, or null to let Hibernate use the default SQL

Since:

2.9

getQuotedName

public String getQuotedName(String name)

Put quotes (`) around the name.

Specified by:

getQuotedName in interface DbEngine

Overrides:

getQuotedName in class AbstractDbEngine

Parameters:

name - The name of an object in the database

Returns:

A quoted name

getApproximateRowCountSql

public String getApproximateRowCountSql(String catalog,
 String schema,
 String table)

Description copied from interface: DbEngine

Get an SQL statement that returns an approximate count for the number of rows in the given table. The SQL query should return a single row with a single value.

Specified by:

getApproximateRowCountSql in interface DbEngine

Overrides:

getApproximateRowCountSql in class AbstractDbEngine

Parameters:

catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog

schema - The name of the schema where the table is located, or null if is located in the current schema

table - The name of the table

Returns:

null, since this depends on capabilities of underlying database

appendList

private void appendList(StringBuilder sb,
 Collection<String> items)