package er.extensions.eof;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import org.apache.log4j.Logger;
import com.webobjects.eoaccess.EOAdaptor;
import com.webobjects.eoaccess.EOEntity;
import com.webobjects.eoaccess.EOModel;
import com.webobjects.eoaccess.EOSQLExpression;
import com.webobjects.eoaccess.EOSQLExpressionFactory;
import com.webobjects.eocontrol.EOEditingContext;
import com.webobjects.eocontrol.EOQualifier;
import com.webobjects.foundation.NSForwardException;
import er.extensions.foundation.ERXProperties;
import er.extensions.jdbc.ERXJDBCConnectionBroker;
import er.extensions.jdbc.ERXSQLHelper;
/**
* Simple sequence class. MT safe, but not multi instance safe (this is implemented by subclasses)
* @author ak
*
*/
public class ERXSequence {
protected Logger log;
private final String _name;
protected long _lastValue;
protected long _maxValue;
public ERXSequence(String name) {
_name = name;
log = Logger.getLogger(name);
}
public ERXSequence(String name, long initialValue) {
this(name);
_lastValue = initialValue;
}
public String name() {
return _name;
}
public long nextValue() {
return nextValue(1L);
}
protected long maxValue() {
return _maxValue;
}
public long nextValue(long increment) {
synchronized (this) {
long diff = (lastValue() + increment) - maxValue();
if(diff > 0) {
long nextIncrement = increment();
_maxValue = increasedMaxValue(diff > nextIncrement ? diff : nextIncrement);
}
_lastValue += increment;
return lastValue();
}
}
protected long lastValue() {
return _lastValue;
}
protected long increasedMaxValue(long increment) {
return maxValue() + increment;
}
protected long increment() {
return ERXProperties.longForKeyWithDefault(name() + ".Increment",
ERXProperties.longForKeyWithDefault("er.extensions.ERXSequence.Increment", 10L));
}
/**
* NativeDatabaseSequence uses ERXSQLHelper.getNextValFromSequenceNamed to
* generate a sequence value using your database's native sequence generation scheme. This
* will fail if ERXSQLHelper.getNextValFromSequenceNamed is not implemented for your database.
* This is also currently limited to only incrementing 1 at a time.
*
* @author mschrag
*/
public static class NativeDatabaseSequence extends ERXSequence {
private EOEditingContext _editingContext;
private String _modelName;
public NativeDatabaseSequence(EOEditingContext editingContext, String modelName, String name) {
super(name);
_editingContext=editingContext;
_modelName=modelName;
}
@Override
public long nextValue(long increment) {
if (increment != 1) {
throw new IllegalArgumentException("NativeDatabaseSequence only supports incrementing 1 at a time.");
}
return ERXSQLHelper.newSQLHelper(_editingContext, _modelName).getNextValFromSequenceNamed(_editingContext, _modelName, name()).longValue();
}
@Override
protected long increment() {
return 1L;
}
}
/**
* Multi-instance-safe subclass of ERXSequence. Creates a table
* erx_sequence_table to store the values.
* @author ak
*
*/
public static class DatabaseSequence extends ERXSequence {
private static final String ERX_SEQUENCE_TABLE = EOSQLExpression.sqlStringForString(ERXProperties.stringForKeyWithDefault("er.extensions.ERXSequence.TableName", "erx_sequence_table"));
private static final String VALUE_COLUMN_NAME = EOSQLExpression.sqlStringForString("value_");
private static final String NAME_COLUMN_NAME = EOSQLExpression.sqlStringForString("name_");
private ERXJDBCConnectionBroker _broker;
private EOSQLExpressionFactory _factory;
public DatabaseSequence(EOEditingContext ec, String modelName, String name, long initialValue) {
super(name, initialValue);
EOModel model = ERXEOAccessUtilities.modelGroup(ec).modelNamed(modelName);
_broker = ERXJDBCConnectionBroker.connectionBrokerForModel(model);
_factory = new EOSQLExpressionFactory(EOAdaptor.adaptorWithModel(model));
_lastValue = increasedMaxValue(0);
_maxValue = _lastValue;
}
public DatabaseSequence(EOEditingContext ec, String modelName, String name) {
this(ec, modelName, name, ERXProperties.longForKeyWithDefault(name + ".InitalValue", 100000L));
}
protected ERXJDBCConnectionBroker broker() {
return _broker;
}
protected long selectAndUpdateValue(Connection con, long increment) throws SQLException {
long pk;
EOSQLExpression selectExpression = _factory.expressionForEntity(null);
String columnList = VALUE_COLUMN_NAME;
String tableList = ERX_SEQUENCE_TABLE;
String whereSelector = selectExpression.sqlStringForSelector(EOQualifier.QualifierOperatorEqual, name());
String whereClause = NAME_COLUMN_NAME + " " + whereSelector + " '" + name() + "'";
String lockClause = selectExpression.lockClause();
String selectStatement = selectExpression.assembleSelectStatementWithAttributes(null, true, null, null, "SELECT ", columnList , tableList, whereClause, null, null, lockClause);
ResultSet resultSet = con.createStatement().executeQuery(selectStatement);
boolean hasNext = resultSet.next();
if (hasNext) {
String incrementString = EOSQLExpression.sqlStringForNumber(Long.valueOf(increment));
String updateValueColumn = VALUE_COLUMN_NAME + " = " + VALUE_COLUMN_NAME + " + " + incrementString;
String updateList = updateValueColumn;
pk = resultSet.getLong(VALUE_COLUMN_NAME);
String updateStatement = _factory.expressionForEntity(null).assembleUpdateStatementWithRow(null, null, tableList, updateList, whereClause);
con.createStatement().executeUpdate(updateStatement);
} else {
pk = createRow(con, increment);
}
return pk;
}
protected long createRow(Connection con, long increment) throws SQLException {
String incrementString = EOSQLExpression.sqlStringForNumber(Long.valueOf(increment));
String tableList = ERX_SEQUENCE_TABLE;
String columnList = NAME_COLUMN_NAME + "," + VALUE_COLUMN_NAME;
String valueList = "'" + name() + "'," + incrementString;
String insertStatement = _factory.expressionForEntity(null).assembleInsertStatementWithRow(null, tableList, columnList, valueList);
con.createStatement().executeUpdate(insertStatement);
return 0L;
}
protected void createTable(Connection con) throws SQLException {
String nameColumnClause = NAME_COLUMN_NAME + " VARCHAR(100) NOT NULL";
String valueColumnClause = VALUE_COLUMN_NAME + " INT";
String createTableStatement = "CREATE TABLE " + ERX_SEQUENCE_TABLE + " (" + nameColumnClause + ", " + valueColumnClause + ")";
String alterTableStatement = "ALTER TABLE " + ERX_SEQUENCE_TABLE + " ADD PRIMARY KEY (" + NAME_COLUMN_NAME + ")";// NOT
// DEFERRABLE
// INITIALLY
// IMMEDIATE");
con.createStatement().executeUpdate(createTableStatement);
con.createStatement().executeUpdate(alterTableStatement);
}
@Override
protected long increasedMaxValue(long increment) {
Connection con = broker().getConnection();
try {
try {
if(con.getTransactionIsolation() != 0) {
con.setAutoCommit(false);
con.setReadOnly(false);
}
} catch (SQLException e) {
log.error(e, e);
}
for(int tries = 0; tries < 5; tries++) {
try {
long lastValue = selectAndUpdateValue(con, increment);
if(_lastValue == 0L) {
_lastValue = lastValue;
}
lastValue += increment;
con.commit();
return lastValue;
} catch(SQLException ex) {
if (isCreationError(ex)) {
try {
con.rollback();
createTable(con);
} catch (SQLException ee) {
throw new NSForwardException(ee, "could not create erx_sequence_table");
}
} else {
throw new NSForwardException(ex, "Error fetching sequence: " + name());
}
}
}
} finally {
broker().freeConnection(con);
}
throw new IllegalStateException("Couldn't get sequence: " + name());
}
protected boolean isCreationError(SQLException ex) {
String s = ex.getMessage().toLowerCase();
boolean creationError = false;
String tableNameLastComponent = DatabaseSequence.ERX_SEQUENCE_TABLE.substring(DatabaseSequence.ERX_SEQUENCE_TABLE.lastIndexOf('.')+1);
creationError |= (s.indexOf("error 116") != -1); // frontbase?
creationError |= (s.indexOf(tableNameLastComponent) != -1 && s.indexOf("does not exist") != -1); // postgres ?
creationError |= s.indexOf("ora-00942") != -1; // oracle
creationError |= s.indexOf("doesn't exist") != -1; // mysql
creationError |= (s.indexOf(tableNameLastComponent) != -1 && s.indexOf("not found.") != -1); // sybase
return creationError;
}
}
public static class PrimaryKeySequence extends DatabaseSequence {
private String _entityName;
public PrimaryKeySequence(EOEditingContext ec, String modelName, String entityName) {
super(ec, modelName, entityName + "_pk_seq");
_entityName = entityName;
}
@Override
protected long createRow(Connection con, long increment) throws SQLException {
EOEntity entity = ERXEOAccessUtilities.rootEntityForEntityNamed(_entityName);
String tableName = entity.externalName();
String colName = entity.primaryKeyAttributes().lastObject().columnName();
String sql = "select max(" + colName + ") from " + tableName;
ResultSet resultSet;
resultSet = con.createStatement().executeQuery(sql);
con.commit();
boolean hasNext = resultSet.next();
long v = 0L;
if (hasNext) {
v = resultSet.getLong(1);
v = fixMaxIdValue(v);
/*if (log.isDebugEnabled())
log.debug("received max id from table " + tableName + ", setting value in PK_TABLE to " + v);
if(encodeEntityInPkValue()) {
v = v >> CODE_LENGTH;
}
if(encodeHostInPkValue()) {
v = v >> HOST_CODE_LENGTH;
}*/
}
super.createRow(con, v+increment);
return v;
}
protected long fixMaxIdValue(long v) {
return v;
}
}
private static final Map cache = Collections.synchronizedMap(new HashMap());
public static ERXSequence sequenceWithName(String name) {
return (ERXSequence) cache.get(name);
}
public static ERXSequence createSequenceWithName(String name, long initialValue) {
ERXSequence sequence = new ERXSequence(name, initialValue);
cache.put(name, sequence);
return sequence;
}
public static ERXSequence createDatabaseSequenceWithName(EOEditingContext ec, String modelName, String name) {
ERXSequence sequence = new DatabaseSequence(ec, modelName, name);
cache.put(name, sequence);
return sequence;
}
public static void registerSequenceWithName(ERXSequence sequence, String name) {
cache.put(name, sequence);
}
}