er.extensions.jdbc

Class ERXSQLHelper.FrontBaseSQLHelper

java.lang.Object

er.extensions.jdbc.ERXSQLHelper

er.extensions.jdbc.ERXSQLHelper.FrontBaseSQLHelper

Enclosing class:

ERXSQLHelper

public static class ERXSQLHelper.FrontBaseSQLHelper
extends ERXSQLHelper

Nested Class Summary

Nested classes/interfaces inherited from class er.extensions.jdbc.ERXSQLHelper

ERXSQLHelper.ColumnIndex, ERXSQLHelper.CustomTypes, ERXSQLHelper.DB2SQLHelper, ERXSQLHelper.DerbySQLHelper, ERXSQLHelper.EROracleSQLHelper, ERXSQLHelper.FirebirdSQLHelper, ERXSQLHelper.FrontBaseSQLHelper, ERXSQLHelper.H2SQLHelper, ERXSQLHelper.MicrosoftSQLHelper, ERXSQLHelper.MySQLSQLHelper, ERXSQLHelper.NoSQLHelper, ERXSQLHelper.OpenBaseSQLHelper, ERXSQLHelper.OracleSQLHelper, ERXSQLHelper.PostgresqlSQLHelper

Constructor Summary

Constructors

Constructor and Description
FrontBaseSQLHelper()

Method Summary

Modifier and Type	Method and Description
protected Pattern	commentPattern() Returns a pattern than matches lines that start with "--".
String	externalTypeForJDBCType(JDBCAdaptor adaptor, int jdbcType) For BOOLEAN we take 'boolean' as external type.
String	limitExpressionForSQL(EOSQLExpression expression, EOFetchSpecification fetchSpecification, String sql, long start, long end)
protected int	maximumElementPerInClause(EOEntity entity) FrontBase is exceedingly inefficient in processing OR clauses.
void	prepareConnectionForSchemaChange(EOEditingContext ec, EOModel model)
String	quoteColumnName(String columnName)
boolean	reassignExternalTypeForValueTypeOverride(EOAttribute attribute)
void	restoreConnectionSettingsAfterSchemaChange(EOEditingContext ec, EOModel model)
boolean	shouldExecute(String sql)
String	sqlForCreateIndex(String indexName, String tableName, ERXSQLHelper.ColumnIndex... columnIndexes) Returns the SQL expression for creating an index on the given set of columns
String	sqlForCreateUniqueIndex(String indexName, String tableName, ERXSQLHelper.ColumnIndex... columnIndexes) Returns the SQL expression for creating a unique index on the given set of columns
String	sqlForFullTextQuery(ERXFullTextQualifier qualifier, EOSQLExpression expression) Returns the SQL expression for a full text search query.
protected String	sqlForGetNextValFromSequencedNamed(String sequenceName) Returns the SQL required to select the next value from the given sequence.

Methods inherited from class er.extensions.jdbc.ERXSQLHelper

_groupByOrHavingIndex, _orderByIndex, addGroupByClauseToExpression, addGroupByClauseToExpression, addHavingCountClauseToExpression, appendItemToListString, attributesToFetchForEntity, canReliablyPerformDistinctWithSortOrderings, columnIndexesFromColumnNames, columnNamesFromColumnIndexes, commandSeparatorChar, commandSeparatorString, createDependentSchemaSQLForEntities, createIndexSQLForEntities, createIndexSQLForEntities, createSchemaSQLForEntitiesInDatabaseContext, createSchemaSQLForEntitiesInModel, createSchemaSQLForEntitiesInModelAndOptions, createSchemaSQLForEntitiesInModelWithName, createSchemaSQLForEntitiesInModelWithNameAndOptions, createSchemaSQLForEntitiesWithOptions, createSchemaSQLForEntitiesWithOptions, customQueryExpressionHintAsString, defaultOptionDictionary, formatValueForAttribute, getNextValFromSequenceNamed, handleDatabaseException, jdbcTypeForCustomType, migrationTableName, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, newSQLHelper, readFormatForAggregateFunction, readFormatForAggregateFunction, removeSelectFromExpression, rowCountForFetchSpecification, shouldPerformDistinctInMemory, splitSQLStatements, splitSQLStatementsFromFile, splitSQLStatementsFromInputStream, sqlExpressionForFetchSpecification, sqlExpressionForFetchSpecification, sqlForCountDistinct, sqlForCreateIndex, sqlForCreateUniqueIndex, sqlForRegularExpressionQuery, sqlForSubquery, sqlWhereClauseStringForKey, varcharLargeColumnWidth, varcharLargeJDBCType

Methods inherited from class java.lang.Object

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

Constructor Detail

FrontBaseSQLHelper

public FrontBaseSQLHelper()

Method Detail

reassignExternalTypeForValueTypeOverride

public boolean reassignExternalTypeForValueTypeOverride(EOAttribute attribute)

Overrides:

reassignExternalTypeForValueTypeOverride in class ERXSQLHelper

sqlForGetNextValFromSequencedNamed

protected String sqlForGetNextValFromSequencedNamed(String sequenceName)

Description copied from class: ERXSQLHelper

Returns the SQL required to select the next value from the given sequence. This should return a single row with a single column.

Overrides:

sqlForGetNextValFromSequencedNamed in class ERXSQLHelper

Parameters:

sequenceName - the name of the sequence

Returns:

the next sequence value

shouldExecute

public boolean shouldExecute(String sql)

Overrides:

shouldExecute in class ERXSQLHelper

limitExpressionForSQL

public String limitExpressionForSQL(EOSQLExpression expression,
                                    EOFetchSpecification fetchSpecification,
                                    String sql,
                                    long start,
                                    long end)

Overrides:

limitExpressionForSQL in class ERXSQLHelper

sqlForFullTextQuery

public String sqlForFullTextQuery(ERXFullTextQualifier qualifier,
                                  EOSQLExpression expression)

Description copied from class: ERXSQLHelper

Returns the SQL expression for a full text search query.

Overrides:

sqlForFullTextQuery in class ERXSQLHelper

Parameters:

qualifier - the full text qualifier

expression - the EOSQLExpression context

Returns:

a SQL expression

sqlForCreateUniqueIndex

public String sqlForCreateUniqueIndex(String indexName,
                                      String tableName,
                                      ERXSQLHelper.ColumnIndex... columnIndexes)

Description copied from class: ERXSQLHelper

Returns the SQL expression for creating a unique index on the given set of columns

Overrides:

sqlForCreateUniqueIndex in class ERXSQLHelper

Parameters:

indexName - the name of the index to create

tableName - the name of the containing table

columnIndexes - the list of columns to index on

Returns:

a SQL expression

sqlForCreateIndex

public String sqlForCreateIndex(String indexName,
                                String tableName,
                                ERXSQLHelper.ColumnIndex... columnIndexes)

Description copied from class: ERXSQLHelper

Returns the SQL expression for creating an index on the given set of columns

Overrides:

sqlForCreateIndex in class ERXSQLHelper

Parameters:

indexName - the name of the index to create

tableName - the name of the containing table

columnIndexes - the list of columns to index on

Returns:

a SQL expression

prepareConnectionForSchemaChange

public void prepareConnectionForSchemaChange(EOEditingContext ec,
                                             EOModel model)

Overrides:

prepareConnectionForSchemaChange in class ERXSQLHelper

restoreConnectionSettingsAfterSchemaChange

public void restoreConnectionSettingsAfterSchemaChange(EOEditingContext ec,
                                                       EOModel model)

Overrides:

restoreConnectionSettingsAfterSchemaChange in class ERXSQLHelper

commentPattern

protected Pattern commentPattern()

Returns a pattern than matches lines that start with "--".

Overrides:

commentPattern in class ERXSQLHelper

Returns:

regex pattern that indicates this line is an SQL comment

quoteColumnName

public String quoteColumnName(String columnName)

Overrides:

quoteColumnName in class ERXSQLHelper

maximumElementPerInClause

protected int maximumElementPerInClause(EOEntity entity)

FrontBase is exceedingly inefficient in processing OR clauses. A query like this:

SELECT * FROM "Foo" t0 WHERE ( t0."oid" IN (431, 437, ...) OR t0."oid" IN (1479, 1480, 1481,...)...

Completely KILLS FrontBase (30+ seconds of 100%+ CPU usage). The same query rendered as:

SELECT * FROM "Foo" t0 WHERE t0."oid" IN (431, 437, ...) UNION SELECT * FROM "Foo" t0 WHERE t0."oid" IN (1479, 1480, 1481, ...)...

executes in less than a tenth of the time with less high CPU load. Collapse all the ORs and INs into one and it is faster still. This has been tested with over 17,000 elements, so 15,000 seemed like a safe maximum. I don't know what the actual theoretical maximum is.

But... It looks to like the query optimizer will choose to NOT use an index if the number of elements in the IN gets close to, or exceeds, the number of rows (as in the case of a select based on FK with a large number of keys that don't match any rows). In this case it seems to fall back to table scanning (or something dreadfully slow). This only seems to have an impact when the number of elements in the IN is greater than 1,000. For larger sizes, the correct number for this method to return seems to depend on the number of rows in the tables. 1/5th of the table size may be a good place to start looking for the upper bound.

Overrides:

maximumElementPerInClause in class ERXSQLHelper

Parameters:

entity - EOEntity that can be used to fine-tune the result

Returns:

database specific limit, or or most efficient number, of elements in an IN clause in a statement

See Also:

ERXSQLHelper.maximumElementPerInClause(EOEntity)

externalTypeForJDBCType

public String externalTypeForJDBCType(JDBCAdaptor adaptor,
                                      int jdbcType)

For BOOLEAN we take 'boolean' as external type. For any other case, we pass it up to the default impl.

Overrides:

externalTypeForJDBCType in class ERXSQLHelper

Parameters:

adaptor - the adaptor to retrieve an external type for

jdbcType - the JDBC type number

Returns:

a guess at the external type name to use