org.melati.poem.dbms
Class AnsiStandard

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

All Implemented Interfaces:

Dbms

Direct Known Subclasses:

FirstSQL, Hsqldb, Interbase, Mckoi, Mimer, MSAccess, MySQL, Oracle, Postgresql, SQLServer

public class AnsiStandard
extends Object
implements Dbms

An SQL 92 compliant Database Management System.

Should there ever be such a thing then you wouldn't need to extend this, but all DBs used with Melati so far have needed to extend the standard with their own variations.

Field Summary

protected String

schema

Constructor Summary

AnsiStandard()

Method Summary

String	alterColumnNotNullableSQL(String tableName, Column column) Return the SQL snippet to alter a column to not nullable.
String	booleanTrueExpression(Column booleanColumn) Accomodate lack of boolean types.
boolean	canBeIndexed(Column column) MSSQL cannot index a TEXT column.
boolean	canDropColumns() Whether this DBMS can drop columns.
PoemType<?>	canRepresent(PoemType<?> storage, PoemType<?> type) Enable one PoemType to represent another, for example a bit to represent a boolean.
boolean	canStoreBlobs() Whether this DBMS can store binary data.
String	caseInsensitiveRegExpSQL(String term1, String term2) This is the Postgresql syntax.
String	createTableOptionsSql() Accomodate MySQL table creation options.
String	createTableSql(Table table)
String	createTableTypeQualifierSql(Table table) Allow Hsqldb to have a different create table syntax.
SQLPoemType	defaultPoemTypeOfColumnMetaData(ResultSet md) The simplest POEM type corresponding to a JDBC description from the database.
SQLPoemException	exceptionForUpdate(Table table, PreparedStatement ps, boolean insert, SQLException e) Version of previous method for PreparedStatements.
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) Accomodate different treatment of different sized binary data.
Connection	getConnection(String url, String user, String password) The default windows installation of MySQL has autocommit set true, which throws an SQLException when one issues a commit.
protected String	getDriverClassName()
protected boolean	getDriverLoaded()
String	getFixedPtSqlDefinition(int scale, int precision) Accomodate differing Fixed Point notations.
String	getForeignKeyDefinition(String tableName, String fieldName, String targetTableName, String targetTableFieldName, String fixName) If Foreign key definitions are part of field definitions, otherwise blank (silently unsupported).
String	getIndexLength(Column column) MySQL requires a length argument when creating an index on a BLOB or TEXT column.
String	getJdbcMetadataName(String name) Some DBMSen (HSQLDB) use canonical uppercased names in the metadata but not in normal use.
String	getLongSqlDefinition() Accomodate Long / Bigint deviants.
String	getPrimaryKeyDefinition(String fieldName) Return the PRIMARY KEY definition string for this dbms.
String	getQuotedName(String name) Accomodate different quoting strategies.
String	getQuotedValue(SQLType sqlType, String value) Accomodate different quoting strategies for values.
String	getSchema() The db schema name to use, if any.
String	getSqlDefaultValue(SQLType sqlType) Used to set a not null value when creating a non nullable column.
String	getSqlDefinition(String sqlTypeName) Retrieve a 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.
protected void	loadDriver()
String	melatiName(String name) Reverse the mapping in unreservedName.
String	preparedStatementPlaceholder(PoemType<?> type) Accomodate casting in placeholders.
String	selectLimit(String querySelection, int limit) Accomodate different limiting syntax.
protected void	setDriverClassName(String name)
protected void	setDriverLoaded(boolean loaded)
void	shutdown(Connection connection) A no-op for all but hsqldb, where the db needs to be shutdown when the servlet container or jvm is destroyed.
String	sqlBooleanValueOfRaw(Object raw) Accomodate different true and false values.
String	tableInitialisationSql(Table table)
String	toString() A string to represent this DBMS.
void	unloadDriver() Used in tests to allow multiple dbmsen to be loaded and unloaded.
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 java.lang.Object

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

Field Detail

schema

protected String schema

Constructor Detail

AnsiStandard

public AnsiStandard()

Method Detail

setDriverClassName

protected void setDriverClassName(String name)

getDriverClassName

protected String getDriverClassName()

setDriverLoaded

protected void setDriverLoaded(boolean loaded)

unloadDriver

public void unloadDriver()

Used in tests to allow multiple dbmsen to be loaded and unloaded.

Specified by:

unloadDriver in interface Dbms

See Also:

Dbms.unloadDriver()

getDriverLoaded

protected boolean getDriverLoaded()

getSchema

public String getSchema()

The db schema name to use, if any. This is typically the JDBC connection URL User string.

Specified by:

getSchema in interface Dbms

Returns:

the schema to use or null if not required

See Also:

Dbms.getSchema()

shutdown

public void shutdown(Connection connection)
              throws SQLException

A no-op for all but hsqldb, where the db needs to be shutdown when the servlet container or jvm is destroyed.

Specified by:

shutdown in interface Dbms

Throws:

SQLException

See Also:

Dbms.shutdown(java.sql.Connection)

canDropColumns

public boolean canDropColumns()

Whether this DBMS can drop columns.

Specified by:

canDropColumns in interface Dbms

Returns:

true if we can

See Also:

Dbms.canDropColumns()

canStoreBlobs

public boolean canStoreBlobs()

Whether this DBMS can store binary data.

Specified by:

canStoreBlobs in interface Dbms

Returns:

true if we can

See Also:

Dbms.canStoreBlobs()

loadDriver

protected void loadDriver()

getConnection

public Connection getConnection(String url,
                                String user,
                                String password)
                         throws ConnectionFailurePoemException

The default windows installation of MySQL has autocommit set true, which throws an SQLException when one issues a commit. Return a connection.

Specified by:

getConnection in interface Dbms

Parameters:

url - the jdbc URL

user - the user to connect as, may be null

password - the password for user, may be null

Returns:

the connection

Throws:

ConnectionFailurePoemException - is we cannot connect

See Also:

Dbms.getConnection(java.lang.String, java.lang.String, java.lang.String)

preparedStatementPlaceholder

public String preparedStatementPlaceholder(PoemType<?> type)

Accomodate casting in placeholders.

Specified by:

preparedStatementPlaceholder in interface Dbms

Returns:

the place holder

See Also:

Dbms.preparedStatementPlaceholder(org.melati.poem.PoemType)

createTableSql

public String createTableSql(Table table)

Specified by:

createTableSql in interface Dbms

Returns:

The appropriate SQL string to create a table

See Also:

org.melati.poem.dbms.Dbms#createTableSql()

createTableTypeQualifierSql

public String createTableTypeQualifierSql(Table table)

Allow Hsqldb to have a different create table syntax. Should have trailing space if not empty String

Specified by:

createTableTypeQualifierSql in interface Dbms

See Also:

Dbms.createTableTypeQualifierSql(org.melati.poem.Table)

createTableOptionsSql

public String createTableOptionsSql()

Accomodate MySQL table creation options.

Specified by:

createTableOptionsSql in interface Dbms

Returns:

DMBS specific table creation options or empty String

See Also:

Dbms.createTableOptionsSql()

tableInitialisationSql

public String tableInitialisationSql(Table table)

Specified by:

tableInitialisationSql in interface Dbms

Returns:

SQL to be run after creation or null

See Also:

Dbms.tableInitialisationSql(org.melati.poem.Table)

getSqlDefinition

public String getSqlDefinition(String sqlTypeName)

Retrieve a SQL type keyword used by the DBMS for the given Melati type name. Override this in non-Ansi standard dbms to handle variants.

Specified by:

getSqlDefinition in interface Dbms

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

Parameters:

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

Returns:

the SQL definition for a string of this size

Throws:

SQLException

See Also:

Dbms.getStringSqlDefinition(int)

getLongSqlDefinition

public String getLongSqlDefinition()

Accomodate Long / Bigint deviants.

Specified by:

getLongSqlDefinition in interface Dbms

Returns:

the keyword to use.

See Also:

Dbms.getLongSqlDefinition()

getBinarySqlDefinition

public String getBinarySqlDefinition(int size)
                              throws SQLException

Accomodate different treatment of different sized binary data.

Specified by:

getBinarySqlDefinition in interface Dbms

Parameters:

size - how big the field is

Returns:

the keyword to use

Throws:

SQLException

See Also:

Dbms.getBinarySqlDefinition(int)

getFixedPtSqlDefinition

public String getFixedPtSqlDefinition(int scale,
                                      int precision)
                               throws SQLException

Accomodate differing Fixed Point notations.

Specified by:

getFixedPtSqlDefinition in interface Dbms

Parameters:

scale - the number of places to right of decimal point

precision - how many digits in total

Returns:

the keywords to use

Throws:

SQLException - potentially

See Also:

Dbms.getFixedPtSqlDefinition(int, int)

sqlBooleanValueOfRaw

public String sqlBooleanValueOfRaw(Object raw)

Accomodate different true and false values.

Specified by:

sqlBooleanValueOfRaw in interface Dbms

Returns:

the DBMS specific truth and false values

See Also:

Dbms.sqlBooleanValueOfRaw(java.lang.Object)

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

Parameters:

storage - the POEM native type

type - the current type

Returns:

the PoemType to use

See Also:

Dbms.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

Parameters:

md - the JDBC metadata

Returns:

the PoemType to use

Throws:

SQLException - potentially

See Also:

Dbms.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

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:

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

exceptionForUpdate

public SQLPoemException exceptionForUpdate(Table table,
                                           PreparedStatement ps,
                                           boolean insert,
                                           SQLException e)

Version of previous method for PreparedStatements. By default (in the AnsiStandard implementation of Dbms) this simply invokes PreparedStatement.toString() and calls the String version.

Specified by:

exceptionForUpdate in interface Dbms

Parameters:

table - The table on which the update was affected

ps - 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:

Dbms.exceptionForUpdate(org.melati.poem.Table, java.sql.PreparedStatement, boolean, java.sql.SQLException)

getQuotedName

public String getQuotedName(String name)

Accomodate different quoting strategies.

Specified by:

getQuotedName in interface Dbms

Parameters:

name - the unquoted name

Returns:

the name quoted (or not) appropriate for this Dbms

See Also:

Dbms.getQuotedName(java.lang.String)

getQuotedValue

public String getQuotedValue(SQLType sqlType,
                             String value)

Accomodate different quoting strategies for values.

Specified by:

getQuotedValue in interface Dbms

Parameters:

sqlType - the SQLType of the value

value - the value

Returns:

a String quoted appropriately

See Also:

org.melati.poem.dbms.Dbms#getQuotedValue(org.melati.poem.SQLType, java.lang.Object)

getJdbcMetadataName

public String getJdbcMetadataName(String name)

Some DBMSen (HSQLDB) use canonical uppercased names in the metadata but not in normal use. see org.melati.poem.Table#unifyWithDB

Specified by:

getJdbcMetadataName in interface Dbms

Parameters:

name - entity name such as tableinfo

Returns:

the (un)quoted name

See Also:

Dbms.getJdbcMetadataName(java.lang.String)

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

Parameters:

name - the field or table name

Returns:

the name translated if necessary

See Also:

Dbms.unreservedName(java.lang.String), MySQL.unreservedName(java.lang.String), MySQL.melatiName(java.lang.String)

melatiName

public String melatiName(String name)

Reverse the mapping in unreservedName.

Specified by:

melatiName in interface Dbms

Parameters:

name - an SQL name

Returns:

the coresponding name to use within Melati

See Also:

Dbms.melatiName(java.lang.String)

getIndexLength

public String getIndexLength(Column column)

MySQL requires a length argument when creating an index on a BLOB or TEXT column. Accomodate DBMS which require a length for BLOBS.

Specified by:

getIndexLength in interface Dbms

Parameters:

column - the POEM Column we are dealing with

Returns:

SQL length string

See Also:

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

canBeIndexed

public boolean canBeIndexed(Column column)

MSSQL cannot index a TEXT column. But neither can it compare them so we don't use it, we use VARCHAR(255). Whether a Column can have an SQL index applied to it.

Specified by:

canBeIndexed in interface Dbms

Parameters:

column - the POEM Column we are dealing with

Returns:

true if it can, false otherwise.

See Also:

Dbms.canBeIndexed(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

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:

Dbms.givesCapabilitySQL(java.lang.Integer, java.lang.String), MySQL.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

Parameters:

term1 - the term to find in

term2 - the quoted term to find

Returns:

the SQL query to use

See Also:

Dbms.caseInsensitiveRegExpSQL(String, String)

toString

public String toString()

A string to represent this DBMS.

Specified by:

toString in interface Dbms

Overrides:

toString in class Object

Returns:

the class name.

See Also:

Object.toString()

getForeignKeyDefinition

public String getForeignKeyDefinition(String tableName,
                                      String fieldName,
                                      String targetTableName,
                                      String targetTableFieldName,
                                      String fixName)

If Foreign key definitions are part of field definitions, otherwise blank (silently unsupported).

Specified by:

getForeignKeyDefinition in interface Dbms

Parameters:

tableName - the table that this column is in, unquoted

fieldName - often the name of the foreign table, unquoted

targetTableName - the table that this is a foreign key into, unquoted

targetTableFieldName - name of the primary key field of the foreign table, often id, unquoted

fixName - name of the IntegrityFix

Returns:

The definition string

See Also:

Dbms.getForeignKeyDefinition(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)

getPrimaryKeyDefinition

public String getPrimaryKeyDefinition(String fieldName)

Return the PRIMARY KEY definition string for this dbms.

Specified by:

getPrimaryKeyDefinition in interface Dbms

Parameters:

fieldName - the table Troid column, often id, unquoted

Returns:

The definition string

See Also:

{@inheritDoc}, Dbms.getPrimaryKeyDefinition(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

Returns:

SQL snippet to set a column not nullable

See Also:

alterColumnNotNullableSQL(java.lang.String, java.lang.String)

selectLimit

public String selectLimit(String querySelection,
                          int limit)

Accomodate different limiting syntax.

Specified by:

selectLimit in interface Dbms

Parameters:

querySelection - main body of query

limit - number to limit to

Returns:

limited query

See Also:

Dbms.selectLimit(java.lang.String, int)

booleanTrueExpression

public String booleanTrueExpression(Column booleanColumn)

Accomodate lack of boolean types.

Specified by:

booleanTrueExpression in interface Dbms

Returns:

an expresion that evaluates to True ie the column name or column name = 1

See Also:

Dbms.booleanTrueExpression(org.melati.poem.Column)

getSqlDefaultValue

public String getSqlDefaultValue(SQLType sqlType)

Used to set a not null value when creating a non nullable column.

Specified by:

getSqlDefaultValue in interface Dbms

Parameters:

sqlType - the type name

Returns:

a String suitable for substitution in UPDATE table SET field = ?

See Also:

org.melati.poem.dbms.Dbms#getSqlDefaultValue(java.lang.String)