org.melati.poem.dbms
Class MySQL

java.lang.Object
  org.melati.poem.dbms.AnsiStandard
      org.melati.poem.dbms.MySQL

All Implemented Interfaces:

Dbms

public class MySQL
extends AnsiStandard

A Driver for MySQL. See http://www.mysql.com. Notes

1. Use JDBC URL of type jdbc:mysql://[host][:port]/dbname[?param=value[...]] ie. the simpliest one has 3 slashes: jdbc:mysql:///melatitest
2. Don't use asterix * for password, leave it empty (end of line), as:

     org.Melati.LogicalDatabase.melatitest.pass=
    

   or use explicit username and password and

      GRANT ALL PRIVILEGES ON dbname
     TO username@localhost IDENTIFIED BY 'password';
    

3. If you want to use double quotes to delimit table and column names then start MySQL in ANSI mode and modify getQuotedName(String name).
4. Start MySQL with transactioned tables as default. InnoDB is stable, BDB nearly stable. getConnection now returns a Connection with autocommit turned off through JDBC. BDB tables of MySQL-Max 3.23.49 don't support full transactions - they lock whole table instead, until commit/rollback is called. According to MySQL 4.0.2-alpha doc, interface between MySQL and BDB tables is still improved. As I tested MySQL-Max 3.23.49, InnoDB has correct transactions, however database size must be specified & reserved in advance in one file, that is share by all InnoDB tables. Set in /etc/my.cnf by line like: innodb_data_file_path=ibdata1:30M

     run
     safe_mysqld --user=mysql --ansi --default-table-type=InnoDB
    

   After it created and initialised dB file /var/lib/mysql/ibdata1 of 30MB, it creates 2 own log files /var/lib/mysql/ib_logfile0 and ib_logfile1, both of size 5MB.
   The table type is currently hardcoded in getCreateTableOptions.
5. boolean type works (both applications melatitest and contacts). Because MySQL returns metainfo about BOOL as TINYINT.

Nested Class Summary

static class	MySQL.BlobPoemType Translates a MySQL Blob into a Poem IntegerPoemType.
static class	MySQL.MySQLBooleanPoemType Translates a MySQL Boolean into a Poem BooleanType.
static class	MySQL.MySQLStringPoemType Translates a MySQL String into a Poem StringPoemType.

Field Summary

static int	indexSize Size of indexes.
static int	mysqlTextSize Size of MySQL text fields.

Fields inherited from class org.melati.poem.dbms.AnsiStandard

schema

Constructor Summary

MySQL()
Constructor - sets driver.

Method Summary

String	alterColumnNotNullableSQL(String tableName, Column column) Return the SQL snippet to alter a column to not nullable.
PoemType<?>	canRepresent(PoemType<?> storage, PoemType<?> type) Enable one PoemType to represent another, for example a bit to represent a boolean.
String	caseInsensitiveRegExpSQL(String term1, String term2) This is the Postgresql syntax.
String	createTableOptionsSql() Accomodate MySQL table creation options.
SQLPoemType	defaultPoemTypeOfColumnMetaData(ResultSet md) The simplest POEM type corresponding to a JDBC description from the database.
SQLPoemException	exceptionForUpdate(Table table, String sql, boolean insert, SQLException e) An exception appropriate for expressing what really went wrong during a write to the db.
String	getBinarySqlDefinition(int size) Ignores size.
String	getIndexLength(Column column) MySQL requires TEXT and BLOB field indices to have an explicit length, 30 should be fine.
String	getQuotedName(String name) Accomodate different quoting strategies.
String	getSqlDefinition(String sqlTypeName) Retrieve an SQL type keyword used by the DBMS for the given Melati type name.
String	getStringSqlDefinition(int size) Accomodate String / Text distinction.
String	givesCapabilitySQL(Integer userTroid, String capabilityExpr) MySQL had no EXISTS keyword, from 4.1 onwards it does.
String	melatiName(String name) Reverse the mapping in unreservedName.
String	unreservedName(String name) A pair of functions for getting around keywords which make your JDBC driver barf, as 'group' does for MySQL.

Methods inherited from class org.melati.poem.dbms.AnsiStandard

booleanTrueExpression, canBeIndexed, canDropColumns, canStoreBlobs, createTableSql, createTableTypeQualifierSql, exceptionForUpdate, getConnection, getDriverClassName, getDriverLoaded, getFixedPtSqlDefinition, getForeignKeyDefinition, getJdbcMetadataName, getLongSqlDefinition, getPrimaryKeyDefinition, getQuotedValue, getSchema, getSqlDefaultValue, loadDriver, preparedStatementPlaceholder, selectLimit, setDriverClassName, setDriverLoaded, shutdown, sqlBooleanValueOfRaw, tableInitialisationSql, toString, unloadDriver

Methods inherited from class java.lang.Object

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

Field Detail

indexSize

public static final int indexSize

Size of indexes.

See Also:

Constant Field Values

mysqlTextSize

public static final int mysqlTextSize

Size of MySQL text fields.

See Also:

Constant Field Values

Constructor Detail

MySQL

public MySQL()

Constructor - sets driver.

Method Detail

createTableOptionsSql

public String createTableOptionsSql()

Accomodate MySQL table creation options.

Specified by:

createTableOptionsSql in interface Dbms

Overrides:

createTableOptionsSql in class AnsiStandard

Returns:

DMBS specific table creation options or empty String

See Also:

AnsiStandard.createTableOptionsSql()

getSqlDefinition

public String getSqlDefinition(String sqlTypeName)

Retrieve an SQL type keyword used by the DBMS for the given Melati type name.

Specified by:

getSqlDefinition in interface Dbms

Overrides:

getSqlDefinition in class AnsiStandard

Parameters:

sqlTypeName - the Melati internal type name

Returns:

this dbms specific type keyword

See Also:

Dbms.getSqlDefinition(java.lang.String)

getStringSqlDefinition

public String getStringSqlDefinition(int size)
                              throws SQLException

Accomodate String / Text distinction.

Specified by:

getStringSqlDefinition in interface Dbms

Overrides:

getStringSqlDefinition in class AnsiStandard

Parameters:

size - the string length (-1 means no limit)

Returns:

the SQL definition for a string of this size

Throws:

SQLException

See Also:

AnsiStandard.getStringSqlDefinition(int)

getBinarySqlDefinition

public String getBinarySqlDefinition(int size)

Ignores size. Accomodate different treatment of different sized binary data.

Specified by:

getBinarySqlDefinition in interface Dbms

Overrides:

getBinarySqlDefinition in class AnsiStandard

Parameters:

size - how big the field is

Returns:

the keyword to use

See Also:

AnsiStandard.getBinarySqlDefinition(int)

getQuotedName

public String getQuotedName(String name)

Accomodate different quoting strategies.

Specified by:

getQuotedName in interface Dbms

Overrides:

getQuotedName in class AnsiStandard

Parameters:

name - the unquoted name

Returns:

the name quoted (or not) appropriate for this Dbms

See Also:

AnsiStandard.getQuotedName(java.lang.String)

canRepresent

public PoemType<?> canRepresent(PoemType<?> storage,
                                PoemType<?> type)

Enable one PoemType to represent another, for example a bit to represent a boolean.

Specified by:

canRepresent in interface Dbms

Overrides:

canRepresent in class AnsiStandard

Parameters:

storage - the POEM native type

type - the current type

Returns:

the PoemType to use

See Also:

AnsiStandard.canRepresent(org.melati.poem.PoemType, org.melati.poem.PoemType)

defaultPoemTypeOfColumnMetaData

public SQLPoemType defaultPoemTypeOfColumnMetaData(ResultSet md)
                                            throws SQLException

The simplest POEM type corresponding to a JDBC description from the database.

Specified by:

defaultPoemTypeOfColumnMetaData in interface Dbms

Overrides:

defaultPoemTypeOfColumnMetaData in class AnsiStandard

Parameters:

md - the JDBC metadata

Returns:

the PoemType to use

Throws:

SQLException - potentially

See Also:

defaultPoemTypeOfColumnMetaData(java.sql.ResultSet)

exceptionForUpdate

public SQLPoemException exceptionForUpdate(Table table,
                                           String sql,
                                           boolean insert,
                                           SQLException e)

An exception appropriate for expressing what really went wrong during a write to the db. This gives the opportunity to try to interpret the getMessage text returned by the underlying driver, so that a more friendly error page can be put together for the user. Canonically, this is used to separate out "duplicate key" errors from more serious problems.

Specified by:

exceptionForUpdate in interface Dbms

Overrides:

exceptionForUpdate in class AnsiStandard

Parameters:

table - The table on which the update was affected

sql - The operation attempted, or possibly null

insert - Whether the operation was an INSERT as opposed to an UPDATE

e - The raw SQL exception: the routine is meant to try to interpret e.getMessage if it can

Returns:

an appropriate exception

See Also:

AnsiStandard.exceptionForUpdate(org.melati.poem.Table, java.lang.String, boolean, java.sql.SQLException)

unreservedName

public String unreservedName(String name)

A pair of functions for getting around keywords which make your JDBC driver barf, as 'group' does for MySQL. Translate special names to non special ones.

Specified by:

unreservedName in interface Dbms

Overrides:

unreservedName in class AnsiStandard

Parameters:

name - the field or table name

Returns:

the name translated if necessary

See Also:

AnsiStandard.unreservedName(java.lang.String)

melatiName

public String melatiName(String name)

Reverse the mapping in unreservedName.

Specified by:

melatiName in interface Dbms

Overrides:

melatiName in class AnsiStandard

Parameters:

name - an SQL name

Returns:

the coresponding name to use within Melati

See Also:

AnsiStandard.melatiName(java.lang.String)

getIndexLength

public String getIndexLength(Column column)

MySQL requires TEXT and BLOB field indices to have an explicit length, 30 should be fine.

Specified by:

getIndexLength in interface Dbms

Overrides:

getIndexLength in class AnsiStandard

Parameters:

column - the POEM Column we are dealing with

Returns:

a snippet of sql to insert into an SQL statement.

See Also:

Dbms.getIndexLength(org.melati.poem.Column), getIndexLength(org.melati.poem.Column)

givesCapabilitySQL

public String givesCapabilitySQL(Integer userTroid,
                                 String capabilityExpr)

MySQL had no EXISTS keyword, from 4.1 onwards it does. NOTE There is a bootstrap problem here, we need to use the unchecked troid, otherwise we get a stack overflow. SQL string to get a Capability.

Specified by:

givesCapabilitySQL in interface Dbms

Overrides:

givesCapabilitySQL in class AnsiStandard

Parameters:

userTroid - the troid of the User to use in the query

capabilityExpr - the capability troid we need

Returns:

the SQL query to use

See Also:

AnsiStandard.givesCapabilitySQL(java.lang.Integer, java.lang.String)

caseInsensitiveRegExpSQL

public String caseInsensitiveRegExpSQL(String term1,
                                       String term2)

This is the Postgresql syntax. Accomodate the variety of ways of ignoring case.

Specified by:

caseInsensitiveRegExpSQL in interface Dbms

Overrides:

caseInsensitiveRegExpSQL in class AnsiStandard

Parameters:

term1 - the term to find in

term2 - the quoted term to find

Returns:

the SQL query to use

See Also:

caseInsensitiveRegExpSQL(java.lang.String, java.lang.String)

alterColumnNotNullableSQL

public String alterColumnNotNullableSQL(String tableName,
                                        Column column)

Return the SQL snippet to alter a column to not nullable.

Specified by:

alterColumnNotNullableSQL in interface Dbms

Overrides:

alterColumnNotNullableSQL in class AnsiStandard

Returns:

SQL snippet to set a column not nullable

See Also:

alterColumnNotNullableSQL(java.lang.String, org.melati.poem.Column)