/**
* set history in database
*
* @param list list with history data
*/
public void setHistory(T[] array) throws SQLException {
PreparedStatement preparedStatement;
synchronized (lock) {
Database database = null;
try {
// open database
database = new Database(HISTORY_DATABASE_NAME);
// delete old history
preparedStatement = database.prepareStatement("DELETE FROM " + name);
preparedStatement.executeUpdate();
database.commit();
// add new history
switch (direction) {
case ASCENDING:
for (int i = array.length - 1; i >= 0; i--) {
preparedStatement = prepareInsert(database, array[i]);
preparedStatement.executeUpdate();
}
break;
case DESCENDING:
for (int i = 0; i < array.length; i++) {
preparedStatement = prepareInsert(database, array[i]);
preparedStatement.executeUpdate();
}
break;
case SORTED:
Arrays.sort(
array,
new Comparator<T>() {
public int compare(T data0, T data1) {
return dataCompareTo(data0, data1);
}
});
for (int i = 0; i < array.length; i++) {
preparedStatement = prepareInsert(database, array[i]);
preparedStatement.executeUpdate();
}
break;
}
database.commit();
// close database
database.close();
database = null;
} finally {
if (database != null)
try {
database.close();
} catch (SQLException unusedException) {
/* ignored */
}
}
}
}
/**
* set history in database
*
* @param list list with history data
*/
public void setHistory(LinkedList<T> list) throws SQLException {
PreparedStatement preparedStatement;
synchronized (lock) {
Database database = null;
try {
// open database
database = new Database(HISTORY_DATABASE_NAME);
// delete old history
preparedStatement = database.prepareStatement("DELETE FROM " + name);
preparedStatement.executeUpdate();
database.commit();
// add new history
Iterator<T> iterator = null;
switch (direction) {
case ASCENDING:
iterator = list.descendingIterator();
break;
case DESCENDING:
iterator = list.iterator();
break;
case SORTED:
Collections.sort(
list,
new Comparator<T>() {
public int compare(T data0, T data1) {
return dataCompareTo(data0, data1);
}
});
iterator = list.iterator();
break;
}
while (iterator.hasNext()) {
T data = iterator.next();
preparedStatement = prepareInsert(database, data);
preparedStatement.executeUpdate();
}
database.commit();
// close database
database.close();
database = null;
} finally {
if (database != null)
try {
database.close();
} catch (SQLException unusedException) {
/* ignored */
}
}
}
}
/** Fully roll back the current transaction. */
public void rollback() {
checkCommitRollback();
currentTransactionName = null;
boolean needCommit = false;
if (undoLog.size() > 0) {
rollbackTo(null, false);
needCommit = true;
}
if (transaction != null) {
rollbackTo(null, false);
needCommit = true;
// rollback stored the undo operations in the transaction
// committing will end the transaction
transaction.commit();
transaction = null;
}
if (locks.size() > 0 || needCommit) {
database.commit(this);
}
cleanTempTables(false);
if (autoCommitAtTransactionEnd) {
autoCommit = true;
autoCommitAtTransactionEnd = false;
}
endTransaction();
}
/** Fully roll back the current transaction. */
public void rollback() {
checkCommitRollback();
currentTransactionName = null;
boolean needCommit = false;
if (locks.size() > 0 || needCommit) {
database.commit(this);
}
cleanTempTables(false);
if (autoCommitAtTransactionEnd) {
autoCommit = true;
autoCommitAtTransactionEnd = false;
}
endTransaction();
List<SQLException> rollbackExceptions = New.arrayList();
for (Map.Entry<String, Connection> entry : connectionHolder.entrySet()) {
try {
entry.getValue().rollback();
} catch (SQLException ex) {
rollbackExceptions.add(ex);
}
}
if (!rollbackExceptions.isEmpty()) {
throw DbException.convert(rollbackExceptions.get(0));
}
}
/**
* Commit the current transaction. If the statement was not a data definition statement, and if
* there are temporary tables that should be dropped or truncated at commit, this is done as well.
*
* @param ddl if the statement was a data definition statement
*/
public void commit(boolean ddl) {
checkCommitRollback();
currentTransactionName = null;
transactionStart = 0;
if (transaction != null) {
// increment the data mod count, so that other sessions
// see the changes
// TODO should not rely on locking
if (locks.size() > 0) {
for (int i = 0, size = locks.size(); i < size; i++) {
Table t = locks.get(i);
if (t instanceof MVTable) {
((MVTable) t).commit();
}
}
}
transaction.commit();
transaction = null;
}
if (containsUncommitted()) {
// need to commit even if rollback is not possible
// (create/drop table and so on)
database.commit(this);
}
removeTemporaryLobs(true);
if (undoLog.size() > 0) {
// commit the rows when using MVCC
if (database.isMultiVersion()) {
ArrayList<Row> rows = New.arrayList();
synchronized (database) {
while (undoLog.size() > 0) {
UndoLogRecord entry = undoLog.getLast();
entry.commit();
rows.add(entry.getRow());
undoLog.removeLast(false);
}
for (int i = 0, size = rows.size(); i < size; i++) {
Row r = rows.get(i);
r.commit();
}
}
}
undoLog.clear();
}
if (!ddl) {
// do not clean the temp tables if the last command was a
// create/drop
cleanTempTables(false);
if (autoCommitAtTransactionEnd) {
autoCommit = true;
autoCommitAtTransactionEnd = false;
}
}
endTransaction();
}
/**
* Commit the current transaction. If the statement was not a data definition statement, and if
* there are temporary tables that should be dropped or truncated at commit, this is done as well.
*
* @param ddl if the statement was a data definition statement
*/
public void commit(boolean ddl) {
checkCommitRollback();
currentTransactionName = null;
transactionStart = 0;
if (containsUncommitted()) {
// need to commit even if rollback is not possible
// (create/drop table and so on)
database.commit(this);
}
if (undoLog.size() > 0) {
// commit the rows when using MVCC
if (database.isMultiVersion()) {
ArrayList<Row> rows = New.arrayList();
synchronized (database) {
while (undoLog.size() > 0) {
UndoLogRecord entry = undoLog.getLast();
entry.commit();
rows.add(entry.getRow());
undoLog.removeLast(false);
}
for (int i = 0, size = rows.size(); i < size; i++) {
Row r = rows.get(i);
r.commit();
}
}
}
undoLog.clear();
}
if (!ddl) {
// do not clean the temp tables if the last command was a
// create/drop
cleanTempTables(false);
if (autoCommitAtTransactionEnd) {
autoCommit = true;
autoCommitAtTransactionEnd = false;
}
}
if (unlinkLobMap != null && unlinkLobMap.size() > 0) {
// need to flush the transaction log, because we can't unlink lobs if the
// commit record is not written
database.flush();
for (Value v : unlinkLobMap.values()) {
v.unlink();
v.close();
}
unlinkLobMap = null;
}
unlockAll();
}
/** Fully roll back the current transaction. */
public void rollback() {
checkCommitRollback();
currentTransactionName = null;
boolean needCommit = false;
if (undoLog.size() > 0) {
rollbackTo(0, false);
needCommit = true;
}
if (locks.size() > 0 || needCommit) {
database.commit(this);
}
cleanTempTables(false);
unlockAll();
if (autoCommitAtTransactionEnd) {
autoCommit = true;
autoCommitAtTransactionEnd = false;
}
}
private void cleanTempTables(boolean closeSession) {
if (localTempTables != null && localTempTables.size() > 0) {
synchronized (database) {
for (Table table : New.arrayList(localTempTables.values())) {
if (closeSession || table.getOnCommitDrop()) {
modificationId++;
localTempTables.remove(table.getName());
table.removeChildrenAndResources(this);
if (closeSession) {
// need to commit, otherwise recovery might
// ignore the table removal
database.commit(this);
}
} else if (table.getOnCommitTruncate()) {
table.truncate(this);
}
}
}
}
}
@Override
public void init(Database db, int size) throws SQLException {
this.database = db;
transactions = size * 6;
int scale = 2;
accounts = size * 30;
tellers = Math.max(accounts / 10, 1);
branches = Math.max(tellers / 10, 1);
db.start(this, "Init");
db.openConnection();
db.dropTable("BRANCHES");
db.dropTable("TELLERS");
db.dropTable("ACCOUNTS");
db.dropTable("HISTORY");
String[] create = {
"CREATE TABLE BRANCHES(BID INT NOT NULL PRIMARY KEY, "
+ "BBALANCE DECIMAL(15,2), FILLER VARCHAR(88))",
"CREATE TABLE TELLERS(TID INT NOT NULL PRIMARY KEY, "
+ "BID INT, TBALANCE DECIMAL(15,2), FILLER VARCHAR(84))",
"CREATE TABLE ACCOUNTS(AID INT NOT NULL PRIMARY KEY, "
+ "BID INT, ABALANCE DECIMAL(15,2), FILLER VARCHAR(84))",
"CREATE TABLE HISTORY(TID INT, "
+ "BID INT, AID INT, DELTA DECIMAL(15,2), HTIME DATETIME, "
+ "FILLER VARCHAR(40))"
};
for (String sql : create) {
db.update(sql);
}
PreparedStatement prep;
db.setAutoCommit(false);
int commitEvery = 1000;
prep =
db.prepare(
"INSERT INTO BRANCHES(BID, BBALANCE, FILLER) "
+ "VALUES(?, 10000.00, '"
+ FILLER
+ "')");
for (int i = 0; i < branches * scale; i++) {
prep.setInt(1, i);
db.update(prep, "insertBranches");
if (i % commitEvery == 0) {
db.commit();
}
}
db.commit();
prep =
db.prepare(
"INSERT INTO TELLERS(TID, BID, TBALANCE, FILLER) "
+ "VALUES(?, ?, 10000.00, '"
+ FILLER
+ "')");
for (int i = 0; i < tellers * scale; i++) {
prep.setInt(1, i);
prep.setInt(2, i / tellers);
db.update(prep, "insertTellers");
if (i % commitEvery == 0) {
db.commit();
}
}
db.commit();
int len = accounts * scale;
prep =
db.prepare(
"INSERT INTO ACCOUNTS(AID, BID, ABALANCE, FILLER) "
+ "VALUES(?, ?, 10000.00, '"
+ FILLER
+ "')");
for (int i = 0; i < len; i++) {
prep.setInt(1, i);
prep.setInt(2, i / accounts);
db.update(prep, "insertAccounts");
if (i % commitEvery == 0) {
db.commit();
}
}
db.commit();
db.closeConnection();
db.end();
// db.start(this, "Open/Close");
// db.openConnection();
// db.closeConnection();
// db.end();
}
private void processTransactions() throws SQLException {
Random random = database.getRandom();
int branch = random.nextInt(branches);
int teller = random.nextInt(tellers);
PreparedStatement updateAccount =
database.prepare("UPDATE ACCOUNTS SET ABALANCE=ABALANCE+? WHERE AID=?");
PreparedStatement selectBalance = database.prepare("SELECT ABALANCE FROM ACCOUNTS WHERE AID=?");
PreparedStatement updateTeller =
database.prepare("UPDATE TELLERS SET TBALANCE=TBALANCE+? WHERE TID=?");
PreparedStatement updateBranch =
database.prepare("UPDATE BRANCHES SET BBALANCE=BBALANCE+? WHERE BID=?");
PreparedStatement insertHistory =
database.prepare(
"INSERT INTO HISTORY(AID, TID, BID, DELTA, HTIME, FILLER) "
+ "VALUES(?, ?, ?, ?, ?, ?)");
int accountsPerBranch = accounts / branches;
database.setAutoCommit(false);
for (int i = 0; i < transactions; i++) {
int account;
if (random.nextInt(100) < 85) {
account = random.nextInt(accountsPerBranch) + branch * accountsPerBranch;
} else {
account = random.nextInt(accounts);
}
int max = BenchA.DELTA;
// delta: -max .. +max
BigDecimal delta = BigDecimal.valueOf(random.nextInt(max * 2) - max);
long current = System.currentTimeMillis();
updateAccount.setBigDecimal(1, delta);
updateAccount.setInt(2, account);
database.update(updateAccount, "updateAccount");
updateTeller.setBigDecimal(1, delta);
updateTeller.setInt(2, teller);
database.update(updateTeller, "updateTeller");
updateBranch.setBigDecimal(1, delta);
updateBranch.setInt(2, branch);
database.update(updateBranch, "updateBranch");
selectBalance.setInt(1, account);
database.queryReadResult(selectBalance);
insertHistory.setInt(1, account);
insertHistory.setInt(2, teller);
insertHistory.setInt(3, branch);
insertHistory.setBigDecimal(4, delta);
// TODO convert: should be able to convert date to timestamp
// (by using 0 for remaining fields)
// insertHistory.setDate(5, new java.sql.Date(current));
insertHistory.setTimestamp(5, new java.sql.Timestamp(current));
insertHistory.setString(6, BenchA.FILLER);
database.update(insertHistory, "insertHistory");
database.commit();
}
updateAccount.close();
selectBalance.close();
updateTeller.close();
updateBranch.close();
insertHistory.close();
}
/**
* add to history
*
* @param data history data to add
*/
public synchronized void add(T data) throws SQLException {
PreparedStatement preparedStatement;
ResultSet resultSet;
synchronized (lock) {
Database database = null;
try {
database = new Database(HISTORY_DATABASE_NAME);
// check if entry already exists (depending on sort mode)
boolean existsFlag = false;
switch (direction) {
case ASCENDING:
case DESCENDING:
// get most recent entry
preparedStatement =
database.prepareStatement(
"SELECT * FROM " + name + " ORDER BY datetime DESC,id DESC LIMIT 0,1;");
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
// check if equals
T existingData = getResult(resultSet);
existsFlag = (existingData != null) && dataEquals(data, existingData);
}
resultSet.close();
break;
case SORTED:
// check if exists
preparedStatement = database.prepareStatement("SELECT * FROM " + name);
resultSet = preparedStatement.executeQuery();
while (!existsFlag && resultSet.next()) {
T existingData = getResult(resultSet);
existsFlag = (existingData != null) && dataEquals(data, existingData);
}
resultSet.close();
break;
}
if (!existsFlag) {
// add to history
preparedStatement = prepareInsert(database, data);
preparedStatement.executeUpdate();
database.commit();
}
if (maxHistoryLength != HISTORY_LENGTH_INFINTE) {
// shorten history
int id;
do {
preparedStatement =
database.prepareStatement(
"SELECT id FROM " + name + " ORDER BY datetime DESC,id DESC LIMIT ?,1;");
preparedStatement.setInt(1, maxHistoryLength);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
id = resultSet.getInt("id");
} else {
id = -1;
}
resultSet.close();
;
if (id >= 0) {
preparedStatement = prepareDelete(database, id);
preparedStatement.executeUpdate();
database.commit();
}
} while (id >= 0);
}
database.close();
database = null;
} finally {
if (database != null)
try {
database.close();
} catch (SQLException unusedException) {
/* ignored */
}
}
}
}
/**
* open history database
*
* @param name history name
* @param historyList history array
* @param maxHistoryLength max. length of history or HISTORY_LENGTH_INFINTE
*/
public HistoryDatabase(String name, int maxHistoryLength, Directions direction)
throws SQLException {
this.name = name;
this.maxHistoryLength = maxHistoryLength;
this.direction = direction;
// create tables
synchronized (lock) {
Database database = null;
try {
PreparedStatement preparedStatement;
ResultSet resultSet;
// open database
database = new Database(HISTORY_DATABASE_NAME);
// create tables if needed
preparedStatement =
database.prepareStatement(
"CREATE TABLE IF NOT EXISTS meta ( " + " name TEXT, " + " value TEXT " + ");");
preparedStatement.executeUpdate();
preparedStatement = prepareInit(database);
preparedStatement.executeUpdate();
database.commit();
// upgrade tables if needed
try {
// todo
preparedStatement =
database.prepareStatement("ALTER TABLE messages ADD COLUMN historyId INTEGER;");
preparedStatement.executeUpdate();
preparedStatement =
database.prepareStatement("UPDATE messages SET historyId=? WHERE historyId IS NULL;");
// preparedStatement.setInt(1,historyId);
preparedStatement.executeUpdate();
database.commit();
} catch (SQLException unusedException) {
// ignored
}
// init/update meta data
preparedStatement = database.prepareStatement("SELECT value FROM meta");
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
String value = resultSet.getString("value");
resultSet.close();
if (Integer.parseInt(value) != HISTORY_DATABASE_VERSION) {
preparedStatement =
database.prepareStatement("UPDATE meta SET value=? WHERE name='version';");
preparedStatement.setString(1, Integer.toString(HISTORY_DATABASE_VERSION));
preparedStatement.executeUpdate();
database.commit();
}
} else {
resultSet.close();
preparedStatement =
database.prepareStatement("INSERT INTO meta (name,value) VALUES ('version',?);");
preparedStatement.setString(1, Integer.toString(HISTORY_DATABASE_VERSION));
preparedStatement.executeUpdate();
database.commit();
}
// close database
database.close();
database = null;
} finally {
if (database != null)
try {
database.close();
} catch (SQLException unusedException) {
/* ignored */
}
}
}
}
/**
* Commit the current transaction. If the statement was not a data definition statement, and if
* there are temporary tables that should be dropped or truncated at commit, this is done as well.
*
* @param ddl if the statement was a data definition statement
*/
public void commit(boolean ddl) {
checkCommitRollback();
currentTransactionName = null;
transactionStart = 0;
if (containsUncommitted()) {
// need to commit even if rollback is not possible
// (create/drop table and so on)
database.commit(this);
}
if (temporaryLobs != null) {
for (Value v : temporaryLobs) {
if (!v.isLinked()) {
v.close();
}
}
temporaryLobs.clear();
}
if (!ddl) {
// do not clean the temp tables if the last command was a
// create/drop
cleanTempTables(false);
if (autoCommitAtTransactionEnd) {
autoCommit = true;
autoCommitAtTransactionEnd = false;
}
}
endTransaction();
boolean commit = true;
List<SQLException> commitExceptions = New.arrayList();
StringBuilder buf = new StringBuilder();
for (Map.Entry<String, Connection> entry : connectionHolder.entrySet()) {
if (commit) {
try {
entry.getValue().commit();
buf.append("\ncommit shard " + entry.getKey() + " transaction succeed.");
} catch (SQLException ex) {
commit = false;
commitExceptions.add(ex);
buf.append("\ncommit shard " + entry.getKey() + " transaction failure.");
}
} else {
// after unsucessfull commit we must try to rollback
// remaining connections
try {
entry.getValue().rollback();
buf.append("\nrollback shard " + entry.getKey() + " transaction succeed.");
} catch (SQLException ex) {
buf.append("\nrollback shard " + entry.getKey() + " transaction failure.");
}
}
}
if (commitExceptions.isEmpty()) {
trace.debug("commit multiple group transaction succeed. commit track list:{0}", buf);
} else {
trace.error(
commitExceptions.get(0),
"fail to commit multiple group transaction. commit track list:{0}",
buf);
DbException.convert(commitExceptions.get(0));
}
}
