AnsiStandard (POEM 0.7.9 API)

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.

Constructor Summary

Constructors
Constructor and Description

AnsiStandard()

Constructors
Constructor and Description
`AnsiStandard()`

Method Summary

All Methods Instance Methods Concrete Methods
Modifier and Type	Method and Description
`String`	`alterColumnAddCommentSQL(Column<?> column, String comment)` TODO test on something which actually uses this
`String`	`alterColumnNotNullableSQL(String tableName, Column<?> column)` Return the SQL snippet to alter a column to not nullable.
`String`	`alterTableAddCommentSQL(Table<?> table, String comment)` TODO test on something which actually uses this
`String`	`booleanTrueExpression(Column<Boolean> booleanColumn)` Accommodate lack of boolean types in underlying DBMS.
`boolean`	`canBeIndexed(Column<?> column)` MSSQL cannot index a TEXT column.
`boolean`	`canDropColumns()` Whether this DBMS can drop columns.
`<S,O> PoemType<O>`	`canRepresent(PoemType<S> storage, PoemType<O> 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 columnsMetaData)` 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 `PreparedStatement`s.
`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)` Accommodate 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.
`String`	`getFixedPtSqlDefinition(int scale, int precision)` Accommodate 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()` Accommodate Long / Bigint deviants.
`String`	`getPrimaryKeyDefinition(String fieldName)` Return the PRIMARY KEY definition string for this dbms.
`String`	`getQuotedName(String name)` Accommodate different quoting strategies.
`String`	`getQuotedValue(SQLType<?> sqlType, String value)` Accommodate 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)` Accommodate 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`	`preparedStatementPlaceholder(PoemType<?> type)` Accommodate casting in placeholders.
`String`	`selectLimit(String querySelection, int limit)` Accommodate different limiting syntax.
`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)` Accommodate 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
equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

- Constructor Detail
  - AnsiStandard
```
public AnsiStandard()
```
- Method Detail
  - unloadDriver
```
public void unloadDriver()
```
    Description copied from interface: Dbms
    
    Used in tests to allow multiple dbmsen to be loaded and unloaded.
    
    Specified by:
    
    unloadDriver in interface Dbms
    
    See Also:
    
    Dbms.unloadDriver()
  - getSchema
```
public String getSchema()
```
    Description copied from interface: Dbms
    
    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
```
    Description copied from interface: Dbms
    
    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()
  - 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.
    
    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)
```
    Accommodate casting in placeholders.
    
    Specified by:
    
    preparedStatementPlaceholder in interface Dbms
    
    Parameters:
    
    type - the PoemType
    
    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
  - 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
```
    Accommodate 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()
```
    Accommodate 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
```
    Accommodate 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
```
    Accommodate 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)
```
    Description copied from interface: Dbms
    
    Accommodate 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 <S,O> PoemType<O> canRepresent(PoemType<S> storage,
                                      PoemType<O> type)
```
    Description copied from interface: Dbms
    
    Enable one PoemType to represent another, for example a bit to represent a boolean.
    
    Specified by:
    
    canRepresent in interface Dbms
    
    Parameters:
    
    storage - the container
    
    type - the type to store
    
    Returns:
    
    the PoemType to use
    
    See Also:
    
    Dbms.canRepresent(org.melati.poem.PoemType, org.melati.poem.PoemType)
  - defaultPoemTypeOfColumnMetaData
```
public SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet columnsMetaData)
                                               throws SQLException
```
    Description copied from interface: Dbms
    
    The simplest POEM type corresponding to a JDBC description from the database.
    
    Specified by:
    
    defaultPoemTypeOfColumnMetaData in interface Dbms
    
    Parameters:
    
    columnsMetaData - 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)
```
    Accommodate 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)
```
    Description copied from interface: Dbms
    
    Accommodate 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
  - 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 corresponding 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. Accommodate 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. Accommodate 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)
```
    Description copied from interface: Dbms
    
    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
  - selectLimit
```
public String selectLimit(String querySelection,
                          int limit)
```
    Accommodate 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<Boolean> booleanColumn)
```
    Description copied from interface: Dbms
    
    Accommodate lack of boolean types in underlying DBMS.
    
    Specified by:
    
    booleanTrueExpression in interface Dbms
    
    Parameters:
    
    booleanColumn - the column which should be a boolean
    
    Returns:
    
    an expression 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)
```
    Description copied from interface: Dbms
    
    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 = ?
  - alterColumnAddCommentSQL
```
public String alterColumnAddCommentSQL(Column<?> column,
                                       String comment)
```
    TODO test on something which actually uses this
    
    Specified by:
    
    alterColumnAddCommentSQL in interface Dbms
    
    Parameters:
    
    column - the target to add a remark to
    
    comment - the remark to add
    
    Returns:
    
    an update SQL command or null
  - alterTableAddCommentSQL
```
public String alterTableAddCommentSQL(Table<?> table,
                                      String comment)
```
    TODO test on something which actually uses this
    
    Specified by:
    
    alterTableAddCommentSQL in interface Dbms
    
    Parameters:
    
    table - the target to add a remark to
    
    comment - the remark to add
    
    Returns:
    
    an update SQL command or null

Class AnsiStandard

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Constructor Detail

AnsiStandard

Method Detail

unloadDriver

getSchema

shutdown

canDropColumns

canStoreBlobs

getConnection

preparedStatementPlaceholder

createTableSql

createTableTypeQualifierSql

createTableOptionsSql

tableInitialisationSql

getSqlDefinition

getStringSqlDefinition

getLongSqlDefinition

getBinarySqlDefinition

getFixedPtSqlDefinition

sqlBooleanValueOfRaw

canRepresent

defaultPoemTypeOfColumnMetaData

exceptionForUpdate

exceptionForUpdate

getQuotedName

getQuotedValue

getJdbcMetadataName

unreservedName

melatiName

getIndexLength

canBeIndexed

givesCapabilitySQL

caseInsensitiveRegExpSQL

toString

getForeignKeyDefinition

getPrimaryKeyDefinition

alterColumnNotNullableSQL

selectLimit

booleanTrueExpression

getSqlDefaultValue

alterColumnAddCommentSQL

alterTableAddCommentSQL