/*
* Copyright 2014 http://Bither.net
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package net.bither.db;
import net.bither.ApplicationInstanceManager;
import net.bither.bitherj.BitherjSettings;
import net.bither.bitherj.core.AddressManager;
import net.bither.bitherj.core.In;
import net.bither.bitherj.core.Out;
import net.bither.bitherj.core.Tx;
import net.bither.bitherj.db.AbstractDb;
import net.bither.bitherj.db.ITxProvider;
import net.bither.bitherj.exception.AddressFormatException;
import net.bither.bitherj.utils.Base58;
import net.bither.bitherj.utils.Sha256Hash;
import net.bither.bitherj.utils.Utils;
import net.bither.utils.LogUtil;
import net.bither.utils.StringUtil;
import net.bither.utils.SystemUtil;
import java.sql.*;
import java.util.*;
public class TxProvider implements ITxProvider {
String txInsertSql = "insert into txs " +
"(tx_hash,tx_ver,tx_locktime,tx_time,block_no,source)" +
" values (?,?,?,?,?,?) ";
String inInsertSql = "insert into ins " +
"(tx_hash,in_sn,prev_tx_hash,prev_out_sn,in_signature,in_sequence)" +
" values (?,?,?,?,?,?) ";
String outInsertSql = "insert into outs " +
"(tx_hash,out_sn,out_script,out_value,out_status,out_address,hd_account_id)" +
" values (?,?,?,?,?,?,?) ";
private static TxProvider txProvider = new TxProvider(ApplicationInstanceManager.txDBHelper);
public static TxProvider getInstance() {
return txProvider;
}
private TxDBHelper mDb;
public TxProvider(TxDBHelper db) {
this.mDb = db;
}
public List<Tx> getTxAndDetailByAddress(String address) {
List<Tx> txItemList = new ArrayList<Tx>();
HashMap<Sha256Hash, Tx> txDict = new HashMap<Sha256Hash, Tx>();
try {
String sql = "select b.* from addresses_txs a, txs b where a.tx_hash=b.tx_hash and a.address=? order by b.block_no ";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{address});
ResultSet c = statement.executeQuery();
while (c.next()) {
Tx txItem = TxHelper.applyCursor(c);
txItem.setIns(new ArrayList<In>());
txItem.setOuts(new ArrayList<Out>());
txItemList.add(txItem);
txDict.put(new Sha256Hash(txItem.getTxHash()), txItem);
}
c.close();
statement.close();
sql = "select b.* from addresses_txs a, ins b where a.tx_hash=b.tx_hash and a.address=? order by b.tx_hash ,b.in_sn";
statement = this.mDb.getPreparedStatement(sql, new String[]{address});
c = statement.executeQuery();
while (c.next()) {
In inItem = TxHelper.applyCursorIn(c);
Tx tx = txDict.get(new Sha256Hash(inItem.getTxHash()));
if (tx != null)
tx.getIns().add(inItem);
}
c.close();
statement.close();
sql = "select b.* from addresses_txs a, outs b where a.tx_hash=b.tx_hash and a.address=? order by b.tx_hash,b.out_sn";
statement = this.mDb.getPreparedStatement(sql, new String[]{address});
c = statement.executeQuery();
while (c.next()) {
Out out = TxHelper.applyCursorOut(c);
Tx tx = txDict.get(new Sha256Hash(out.getTxHash()));
if (tx != null)
tx.getOuts().add(out);
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return txItemList;
}
@Override
public List<Tx> getTxAndDetailByAddress(String address, int page) {
List<Tx> txItemList = new ArrayList<Tx>();
HashMap<Sha256Hash, Tx> txDict = new HashMap<Sha256Hash, Tx>();
try {
String sql = "select b.* from addresses_txs a, txs b" +
" where a.tx_hash=b.tx_hash and a.address=? order by ifnull(b.block_no,4294967295) desc limit ?,? ";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{
address, Integer.toString((page - 1) * BitherjSettings.TX_PAGE_SIZE), Integer.toString(BitherjSettings.TX_PAGE_SIZE)
});
ResultSet c = statement.executeQuery();
while (c.next()) {
Tx txItem = TxHelper.applyCursor(c);
txItem.setIns(new ArrayList<In>());
txItem.setOuts(new ArrayList<Out>());
txItemList.add(txItem);
txDict.put(new Sha256Hash(txItem.getTxHash()), txItem);
}
c.close();
statement.close();
addInForTxDetail(address, txDict);
addOutForTxDetail(address, txDict);
} catch (SQLException e) {
e.printStackTrace();
} catch (AddressFormatException e) {
e.printStackTrace();
}
return txItemList;
}
private void addInForTxDetail(String address, HashMap<Sha256Hash, Tx> txDict) throws AddressFormatException, SQLException {
String sql = "select b.* from addresses_txs a, ins b where a.tx_hash=b.tx_hash and a.address=? "
+ "order by b.tx_hash ,b.in_sn";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{address});
ResultSet c = statement.executeQuery();
while (c.next()) {
In inItem = TxHelper.applyCursorIn(c);
Tx tx = txDict.get(new Sha256Hash(inItem.getTxHash()));
if (tx != null) {
tx.getIns().add(inItem);
}
}
c.close();
statement.close();
}
private void addOutForTxDetail(String address, HashMap<Sha256Hash, Tx> txDict) throws AddressFormatException, SQLException {
String sql = "select b.* from addresses_txs a, outs b where a.tx_hash=b.tx_hash and a.address=? "
+ "order by b.tx_hash,b.out_sn";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{address});
ResultSet c = statement.executeQuery();
while (c.next()) {
Out out = TxHelper.applyCursorOut(c);
Tx tx = txDict.get(new Sha256Hash(out.getTxHash()));
if (tx != null) {
tx.getOuts().add(out);
}
}
c.close();
statement.close();
}
public List<Tx> getPublishedTxs() {
List<Tx> txItemList = new ArrayList<Tx>();
HashMap<Sha256Hash, Tx> txDict = new HashMap<Sha256Hash, Tx>();
String sql = "select * from txs where block_no is null or block_no =?";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Integer.toString(Tx.TX_UNCONFIRMED)});
ResultSet c = statement.executeQuery();
while (c.next()) {
Tx txItem = TxHelper.applyCursor(c);
txItem.setIns(new ArrayList<In>());
txItem.setOuts(new ArrayList<Out>());
txItemList.add(txItem);
txDict.put(new Sha256Hash(txItem.getTxHash()), txItem);
}
c.close();
statement.close();
sql = "select b.* from txs a, ins b where a.tx_hash=b.tx_hash and ( a.block_no is null or a.block_no =?) order by b.tx_hash ,b.in_sn";
statement = this.mDb.getPreparedStatement(sql, new String[]{Integer.toString(Tx.TX_UNCONFIRMED)});
c = statement.executeQuery();
while (c.next()) {
In inItem = TxHelper.applyCursorIn(c);
Tx tx = txDict.get(new Sha256Hash(inItem.getTxHash()));
tx.getIns().add(inItem);
}
c.close();
statement.close();
sql = "select b.* from txs a, outs b where a.tx_hash=b.tx_hash and ( a.block_no is null or a.block_no = ? )order by b.tx_hash,b.out_sn";
statement = this.mDb.getPreparedStatement(sql, new String[]{Integer.toString(Tx.TX_UNCONFIRMED)});
c = statement.executeQuery();
while (c.next()) {
Out out = TxHelper.applyCursorOut(c);
Tx tx = txDict.get(new Sha256Hash(out.getTxHash()));
tx.getOuts().add(out);
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return txItemList;
}
public List<In> getRelatedIn(String address) {
List<In> list = new ArrayList<In>();
String sql = "select ins.* from ins,addresses_txs " +
"where ins.tx_hash=addresses_txs.tx_hash and addresses_txs.address=?";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{address});
ResultSet rs = statement.executeQuery();
while (rs.next()) {
list.add(TxHelper.applyCursorIn(rs));
}
rs.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public Tx getTxDetailByTxHash(byte[] txHash) {
Tx txItem = null;
String txHashStr = Base58.encode(txHash);
String sql = "select * from txs where tx_hash=?";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{txHashStr});
ResultSet c = statement.executeQuery();
if (c.next()) {
txItem = TxHelper.applyCursor(c);
}
if (txItem != null) {
TxHelper.addInsAndOuts(this.mDb, txItem);
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return txItem;
}
@Override
public long sentFromAddress(byte[] txHash, String address) {
String sql = "select sum(o.out_value) out_value from ins i,outs o where" +
" i.tx_hash=? and o.tx_hash=i.prev_tx_hash and i.prev_out_sn=o.out_sn and o.out_address=?";
long sum = 0;
try {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Base58.encode(txHash),
address});
ResultSet cursor = statement.executeQuery();
if (cursor.next()) {
int idColumn = cursor.findColumn(AbstractDb.OutsColumns.OUT_VALUE);
if (idColumn != -1) {
sum = cursor.getLong(idColumn);
}
}
cursor.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return sum;
}
public boolean isExist(byte[] txHash) {
boolean result = false;
try {
String sql = "select count(0) cnt from txs where tx_hash=?";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Base58.encode(txHash)});
ResultSet c = statement.executeQuery();
if (c.next()) {
int columnIndex = c.findColumn("cnt");
if (columnIndex != -1) {
result = c.getInt(columnIndex) > 0;
}
}
c.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public void add(final Tx txItem) {
try {
this.mDb.getConn().setAutoCommit(false);
addTxToDb(this.mDb.getConn(), txItem);
this.mDb.getConn().commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void addTxs(List<Tx> txItems) {
try {
Connection connection = this.mDb.getConn();
connection.setAutoCommit(false);
for (Tx txItem : txItems) {
addTxToDb(connection, txItem);
}
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
private void addTxToDb(Connection conn, Tx txItem) throws SQLException {
HashSet<String> addressSet = AbstractDb.hdAccountProvider.
getBelongAccountAddresses(txItem.getOutAddressList());
for (Out out : txItem.getOuts()) {
if (addressSet.contains(out.getOutAddress())) {
out.setHDAccountId(AddressManager.getInstance().getHdAccount().getHdSeedId());
}
}
insertTx(conn, txItem);
List<AddressTx> addressesTxsRels = new ArrayList<AddressTx>();
List<AddressTx> temp = insertIn(conn, txItem);
if (temp != null && temp.size() > 0) {
addressesTxsRels.addAll(temp);
}
temp = insertOut(conn, txItem);
if (temp != null && temp.size() > 0) {
addressesTxsRels.addAll(temp);
}
PreparedStatement statement;
for (AddressTx addressTx : addressesTxsRels) {
String sql = "insert or ignore into addresses_txs(address, tx_hash) values(?,?)";
statement = conn.prepareStatement(sql);
statement.setString(1, addressTx.getAddress());
statement.setString(2, addressTx.getTxHash());
statement.executeUpdate();
statement.close();
}
}
private void insertTx(Connection conn, Tx txItem) throws SQLException {
String existSql = "select count(0) cnt from txs where tx_hash=?";
PreparedStatement preparedStatement = conn.prepareStatement(existSql);
preparedStatement.setString(1, Base58.encode(txItem.getTxHash()));
ResultSet c = preparedStatement.executeQuery();
int cnt = 0;
if (c.next()) {
int idColumn = c.findColumn("cnt");
if (idColumn != -1) {
cnt = c.getInt(idColumn);
}
}
c.close();
preparedStatement.close();
if (cnt == 0) {
String blockNoString = null;
if (txItem.getBlockNo() != Tx.TX_UNCONFIRMED) {
blockNoString = Integer.toString(txItem.getBlockNo());
}
preparedStatement = conn.prepareStatement(txInsertSql);
preparedStatement.setString(1, Base58.encode(txItem.getTxHash()));
preparedStatement.setLong(2, txItem.getTxVer());
preparedStatement.setLong(3, txItem.getTxLockTime());
preparedStatement.setLong(4, txItem.getTxTime());
preparedStatement.setString(5, blockNoString);
preparedStatement.setInt(6, txItem.getSource());
preparedStatement.executeUpdate();
preparedStatement.close();
}
}
private List<AddressTx> insertOut(Connection conn, Tx txItem) throws SQLException {
ResultSet c;
String sql;
PreparedStatement preparedStatement;
List<AddressTx> addressTxes = new ArrayList<AddressTx>();
for (Out outItem : txItem.getOuts()) {
String existSql = "select count(0) cnt from outs where tx_hash=? and out_sn=?";
preparedStatement = conn.prepareStatement(existSql);
preparedStatement.setString(1, Base58.encode(outItem.getTxHash()));
preparedStatement.setString(2, Integer.toString(outItem.getOutSn()));
c = preparedStatement.executeQuery();
int cnt = 0;
if (c.next()) {
int idColumn = c.findColumn("cnt");
if (idColumn != -1) {
cnt = c.getInt(idColumn);
}
}
c.close();
preparedStatement.close();
if (cnt == 0) {
String outAddress = null;
if (!Utils.isEmpty(outItem.getOutAddress())) {
outAddress = outItem.getOutAddress();
}
preparedStatement = conn.prepareStatement(outInsertSql);
preparedStatement.setString(1, Base58.encode(outItem.getTxHash()));
preparedStatement.setInt(2, outItem.getOutSn());
preparedStatement.setString(3, Base58.encode(outItem.getOutScript()));
preparedStatement.setLong(4, outItem.getOutValue());
preparedStatement.setInt(5, outItem.getOutStatus().getValue());
preparedStatement.setString(6, outAddress);
preparedStatement.setInt(7, outItem.getHDAccountId());
preparedStatement.executeUpdate();
preparedStatement.close();
} else {
if (outItem.getHDAccountId() > -1) {
preparedStatement = conn.prepareStatement("update outs set hd_account_id=? where tx_hash=? and out_sn=?");
preparedStatement.setString(1, Integer.toString(outItem.getHDAccountId()));
preparedStatement.setString(2, Base58.encode(txItem.getTxHash()));
preparedStatement.setString(3, Integer.toString(outItem.getOutSn()));
preparedStatement.executeUpdate();
preparedStatement.close();
}
}
if (!Utils.isEmpty(outItem.getOutAddress())) {
addressTxes.add(new AddressTx(outItem.getOutAddress(), Base58.encode(txItem.getTxHash())));
}
sql = "select tx_hash from ins where prev_tx_hash=? and prev_out_sn=?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, Base58.encode(txItem.getTxHash()));
preparedStatement.setString(2, Integer.toString(outItem.getOutSn()));
c = preparedStatement.executeQuery();
boolean isSpentByExistTx = false;
if (c.next()) {
int idColumn = c.findColumn("tx_hash");
if (idColumn != -1) {
addressTxes.add(new AddressTx(outItem.getOutAddress(), c.getString(idColumn)));
}
isSpentByExistTx = true;
}
c.close();
preparedStatement.close();
if (isSpentByExistTx) {
sql = "update outs set out_status=? where tx_hash=? and out_sn=?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, Integer.toString(Out.OutStatus.spent.getValue()));
preparedStatement.setString(2, Base58.encode(txItem.getTxHash()));
preparedStatement.setString(3, Integer.toString(outItem.getOutSn()));
preparedStatement.executeUpdate();
preparedStatement.close();
}
}
return addressTxes;
}
private List<AddressTx> insertIn(Connection conn, Tx txItem) throws SQLException {
ResultSet c;
String sql;
PreparedStatement preparedStatement;
List<AddressTx> addressTxes = new ArrayList<AddressTx>();
for (In inItem : txItem.getIns()) {
String existSql = "select count(0) cnt from ins where tx_hash=? and in_sn=?";
preparedStatement = conn.prepareStatement(existSql);
preparedStatement.setString(1, Base58.encode(inItem.getTxHash()));
preparedStatement.setString(2, Integer.toString(inItem.getInSn()));
c = preparedStatement.executeQuery();
int cnt = 0;
if (c.next()) {
int idColumn = c.findColumn("cnt");
if (idColumn != -1) {
cnt = c.getInt(idColumn);
}
}
c.close();
preparedStatement.close();
if (cnt == 0) {
String signatureString = null;
if (inItem.getInSignature() != null) {
signatureString = Base58.encode(inItem.getInSignature());
}
preparedStatement = conn.prepareStatement(inInsertSql);
preparedStatement.setString(1, Base58.encode(inItem.getTxHash()));
preparedStatement.setInt(2, inItem.getInSn());
preparedStatement.setString(3, Base58.encode(inItem.getPrevTxHash()));
preparedStatement.setInt(4, inItem.getPrevOutSn());
preparedStatement.setString(5, signatureString);
preparedStatement.setLong(6, inItem.getInSequence());
preparedStatement.executeUpdate();
preparedStatement.close();
}
sql = "select out_address from outs where tx_hash=? and out_sn=?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, Base58.encode(inItem.getPrevTxHash()));
preparedStatement.setString(2, Integer.toString(inItem.getPrevOutSn()));
c = preparedStatement.executeQuery();
while (c.next()) {
int idColumn = c.findColumn("out_address");
if (idColumn != -1) {
addressTxes.add(new AddressTx(c.getString(idColumn), Base58.encode(txItem.getTxHash())));
}
}
c.close();
preparedStatement.close();
sql = "update outs set out_status=? where tx_hash=? and out_sn=?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, Integer.toString(Out.OutStatus.spent.getValue()));
preparedStatement.setString(2, Base58.encode(inItem.getPrevTxHash()));
preparedStatement.setString(3, Integer.toString(inItem.getPrevOutSn()));
preparedStatement.executeUpdate();
preparedStatement.close();
}
return addressTxes;
}
public void remove(byte[] txHash) {
String txHashStr = Base58.encode(txHash);
List<String> txHashes = new ArrayList<String>();
final List<String> needRemoveTxHashes = new ArrayList<String>();
txHashes.add(txHashStr);
while (txHashes.size() > 0) {
String thisHash = txHashes.get(0);
txHashes.remove(0);
needRemoveTxHashes.add(thisHash);
List<String> temp = getRelayTx(thisHash);
txHashes.addAll(temp);
}
try {
this.mDb.getConn().setAutoCommit(false);
for (String str : needRemoveTxHashes) {
removeSingleTx(this.mDb.getConn(), str);
}
this.mDb.getConn().commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
private void removeSingleTx(Connection conn, String tx) throws SQLException {
Statement stmt = conn.createStatement();
String deleteTx = "delete from txs where tx_hash='" + tx + "'";
String deleteIn = "delete from ins where tx_hash='" + tx + "'";
String deleteOut = "delete from outs where tx_hash='" + tx + "'";
String deleteAddressesTx = "delete from addresses_txs where tx_hash='" + tx + "'";
String inSql = "select prev_tx_hash,prev_out_sn from ins where tx_hash='" + tx + "'";
String existOtherIn = "select count(0) cnt from ins where prev_tx_hash=? and prev_out_sn=?";
String updatePrevOut = "update outs set out_status=? where tx_hash=? and out_sn=?";
ResultSet c = stmt.executeQuery(inSql);
List<Object[]> needUpdateOuts = new ArrayList<Object[]>();
while (c.next()) {
int idColumn = c.findColumn(AbstractDb.InsColumns.PREV_TX_HASH);
String prevTxHash = null;
int prevOutSn = 0;
if (idColumn != -1) {
prevTxHash = c.getString(idColumn);
}
idColumn = c.findColumn(AbstractDb.InsColumns.PREV_OUT_SN);
if (idColumn != -1) {
prevOutSn = c.getInt(idColumn);
}
needUpdateOuts.add(new Object[]{prevTxHash, prevOutSn});
}
c.close();
stmt.executeUpdate(deleteAddressesTx);
stmt.executeUpdate(deleteOut);
stmt.executeUpdate(deleteIn);
stmt.executeUpdate(deleteTx);
for (Object[] array : needUpdateOuts) {
PreparedStatement statement = this.mDb.getPreparedStatement(existOtherIn, new String[]{array[0].toString(), array[1].toString()});
c = statement.executeQuery();
while (c.next()) {
int columnIndex = c.findColumn("cnt");
if (columnIndex != -1 && c.getInt(columnIndex) == 0) {
stmt.executeUpdate(updatePrevOut, new String[]{
Integer.toString(Out.OutStatus.unspent.getValue()), array[0].toString(), array[1].toString()});
}
}
c.close();
statement.close();
}
stmt.close();
}
private List<String> getRelayTx(String txHash) {
List<String> relayTxHashes = new ArrayList<String>();
try {
String relayTx = "select distinct tx_hash from ins where prev_tx_hash=?";
PreparedStatement statement = this.mDb.getPreparedStatement(relayTx, new String[]{txHash});
ResultSet c = statement.executeQuery();
while (c.next()) {
relayTxHashes.add(c.getString(0));
}
c.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return relayTxHashes;
}
public boolean isAddress(String address, Tx txItem) {
boolean result = false;
String sql = "select count(0) cnt from ins a, txs b where a.tx_hash=b.tx_hash and" +
" b.block_no is not null and a.prev_tx_hash=? and a.prev_out_sn=?";
ResultSet c;
try {
for (In inItem : txItem.getIns()) {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Base58.encode(inItem.getPrevTxHash()), Integer.toString(inItem.getPrevOutSn())});
c = statement.executeQuery();
if (c.next()) {
int columnIndex = c.findColumn("cnt");
if (columnIndex != -1 && c.getInt(columnIndex) > 0) {
c.close();
return false;
}
}
c.close();
statement.close();
}
String addressSql = "select count(0) cnt from addresses_txs where tx_hash=? and address=?";
PreparedStatement statement = this.mDb.getPreparedStatement(addressSql, new String[]{Base58.encode(txItem.getTxHash()), address});
c = statement.executeQuery();
int count = 0;
if (c.next()) {
int columnIndex = c.findColumn("cnt");
if (columnIndex != -1) {
count = c.getInt(columnIndex);
}
}
c.close();
statement.close();
if (count > 0) {
return true;
}
String outsCountSql = "select count(0) cnt from outs where tx_hash=? and out_sn=? and out_address=?";
for (In inItem : txItem.getIns()) {
statement = this.mDb.getPreparedStatement(outsCountSql, new String[]{Base58.encode(inItem.getPrevTxHash())
, Integer.toString(inItem.getPrevOutSn()), address});
c = statement.executeQuery();
count = 0;
int columnIndex = c.findColumn("cnt");
if (c.next()) {
if (columnIndex != -1) {
count = c.getInt(columnIndex);
}
}
c.close();
statement.close();
if (count > 0) {
return true;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public void confirmTx(final int blockNo, final List<byte[]> txHashes) {
if (blockNo == Tx.TX_UNCONFIRMED || txHashes == null) {
return;
}
final String sql = "update txs set block_no=%d where tx_hash='%s'";
final String existSql = "select count(0) cnt from txs where block_no=" + Integer.toString(blockNo) + " and tx_hash='%s'";
final String doubleSpendSql = "select a.tx_hash from ins a, ins b where a.prev_tx_hash=b.prev_tx_hash " +
"and a.prev_out_sn=b.prev_out_sn and a.tx_hash<>b.tx_hash and b.tx_hash='%s'";
final String blockTimeSql = "select block_time from blocks where block_no=%d";
final String updateTxTimeThatMoreThanBlockTime = "update txs set tx_time=%d where block_no=%d and tx_time>%d";
try {
ResultSet c;
Statement stmt = this.mDb.getConn().createStatement();
for (byte[] txHash : txHashes) {
c = stmt.executeQuery(String.format(Locale.US, existSql, Base58.encode(txHash)));
if (c.next()) {
int columnIndex = c.findColumn("cnt");
int cnt = 0;
if (columnIndex != -1) {
cnt = c.getInt(columnIndex);
}
c.close();
if (cnt > 0) {
continue;
}
} else {
c.close();
}
String updateSql = Utils.format(sql, blockNo, Base58.encode(txHash));
stmt.execute(updateSql);
c = stmt.executeQuery(Utils.format(doubleSpendSql, Base58.encode(txHash)));
List<String> txHashes1 = new ArrayList<String>();
while (c.next()) {
int idColumn = c.findColumn("tx_hash");
if (idColumn != -1) {
txHashes1.add(c.getString(idColumn));
}
}
c.close();
List<String> needRemoveTxHashes = new ArrayList<String>();
while (txHashes1.size() > 0) {
String thisHash = txHashes1.get(0);
txHashes1.remove(0);
needRemoveTxHashes.add(thisHash);
List<String> temp = getRelayTx(thisHash);
txHashes1.addAll(temp);
}
this.mDb.getConn().setAutoCommit(false);
for (String each : needRemoveTxHashes) {
removeSingleTx(this.mDb.getConn(), each);
}
this.mDb.getConn().commit();
}
c = stmt.executeQuery(Utils.format(blockTimeSql, blockNo));
if (c.next())
{
int idColumn = c.findColumn("block_time");
if (idColumn != -1) {
int blockTime = c.getInt(idColumn);
c.close();
String sqlTemp = Utils.format(updateTxTimeThatMoreThanBlockTime, blockTime, blockNo, blockTime);
stmt.executeUpdate(sqlTemp);
}
} else {
c.close();
}
this.mDb.getConn().commit();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void unConfirmTxByBlockNo(int blockNo) {
String sql = "update txs set block_no=null where block_no>=" + blockNo;
mDb.executeUpdate(sql, null);
}
public List<Tx> getUnspendTxWithAddress(String address) {
String unspendOutSql = "select a.*,b.tx_ver,b.tx_locktime,b.tx_time,b.block_no,b.source,ifnull(b.block_no,0)*a.out_value coin_depth " +
"from outs a,txs b where a.tx_hash=b.tx_hash" +
" and a.out_address=? and a.out_status=?";
List<Tx> txItemList = new ArrayList<Tx>();
try {
PreparedStatement statement = this.mDb.getPreparedStatement(unspendOutSql, new String[]{address, Integer.toString(Out.OutStatus.unspent.getValue())});
ResultSet c = statement.executeQuery();
while (c.next()) {
int idColumn = c.findColumn("coin_depth");
Tx txItem = TxHelper.applyCursor(c);
Out outItem = TxHelper.applyCursorOut(c);
if (idColumn != -1) {
outItem.setCoinDepth(c.getLong(idColumn));
}
outItem.setTx(txItem);
txItem.setOuts(new ArrayList<Out>());
txItem.getOuts().add(outItem);
txItemList.add(txItem);
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return txItemList;
}
public List<Out> getUnspendOutWithAddress(String address) {
List<Out> outItems = new ArrayList<Out>();
String unspendOutSql = "select a.* from outs a,txs b where a.tx_hash=b.tx_hash " +
"and b.block_no is null and a.out_address=? and a.out_status=?";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(unspendOutSql, new String[]{address, Integer.toString(Out.OutStatus.unspent.getValue())});
ResultSet c = statement.executeQuery();
while (c.next()) {
outItems.add(TxHelper.applyCursorOut(c));
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return outItems;
}
@Override
public long getConfirmedBalanceWithAddress(String address) {
long sum = 0;
try {
String unspendOutSql = "select ifnull(sum(a.out_value),0) sum from outs a,txs b where a.tx_hash=b.tx_hash " +
" and a.out_address=? and a.out_status=? and b.block_no is not null";
PreparedStatement statement = this.mDb.getPreparedStatement(unspendOutSql,
new String[]{address, Integer.toString(Out.OutStatus.unspent.getValue())});
ResultSet c = statement.executeQuery();
if (c.next()) {
int idColumn = c.findColumn("sum");
if (idColumn != -1) {
sum = c.getLong(idColumn);
}
}
c.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return sum;
}
@Override
public List<Tx> getUnconfirmedTxWithAddress(String address) {
List<Tx> txList = new ArrayList<Tx>();
HashMap<Sha256Hash, Tx> txDict = new HashMap<Sha256Hash, Tx>();
try {
String sql = "select b.* from addresses_txs a, txs b " +
"where a.tx_hash=b.tx_hash and a.address=? and b.block_no is null " +
"order by b.block_no desc";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{address});
ResultSet c = statement.executeQuery();
while (c.next()) {
Tx txItem = TxHelper.applyCursor(c);
txItem.setIns(new ArrayList<In>());
txItem.setOuts(new ArrayList<Out>());
txList.add(txItem);
txDict.put(new Sha256Hash(txItem.getTxHash()), txItem);
}
c.close();
statement.close();
sql = "select b.tx_hash,b.in_sn,b.prev_tx_hash,b.prev_out_sn,b.in_signature,b.in_sequence" +
" from addresses_txs a, ins b, txs c " +
" where a.tx_hash=b.tx_hash and b.tx_hash=c.tx_hash and c.block_no is null and a.address=? "
+ " order by b.tx_hash ,b.in_sn";
statement = this.mDb.getPreparedStatement(sql, new String[]{address});
c = statement.executeQuery();
while (c.next()) {
In inItem = TxHelper.applyCursorIn(c);
Tx tx = txDict.get(new Sha256Hash(inItem.getTxHash()));
if (tx != null) {
tx.getIns().add(inItem);
}
}
c.close();
statement.close();
sql = "select b.tx_hash,b.out_sn,b.out_value,b.out_address,b.out_script,b.out_status " +
"from addresses_txs a, outs b, txs c " +
"where a.tx_hash=b.tx_hash and b.tx_hash=c.tx_hash and c.block_no is null and a.address=? "
+ "order by b.tx_hash,b.out_sn";
statement = this.mDb.getPreparedStatement(sql, new String[]{address});
c = statement.executeQuery();
while (c.next()) {
Out out = TxHelper.applyCursorOut(c);
Tx tx = txDict.get(new Sha256Hash(out.getTxHash()));
if (tx != null) {
tx.getOuts().add(out);
}
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return txList;
}
public List<Out> getUnSpendOutCanSpendWithAddress(String address) {
List<Out> outItems = new ArrayList<Out>();
String confirmedOutSql = "select a.*,b.block_no*a.out_value coin_depth from outs a,txs b" +
" where a.tx_hash=b.tx_hash and b.block_no is not null and a.out_address=? and a.out_status=?";
String selfOutSql = "select a.* from outs a,txs b where a.tx_hash=b.tx_hash and b.block_no" +
" is null and a.out_address=? and a.out_status=? and b.source>=1";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(confirmedOutSql, new String[]{address, Integer.toString(Out.OutStatus.unspent.getValue())});
ResultSet c = statement.executeQuery();
while (c.next()) {
Out outItem = TxHelper.applyCursorOut(c);
int idColumn = c.findColumn("coin_depth");
if (idColumn != -1) {
outItem.setCoinDepth(c.getLong(idColumn));
}
outItems.add(outItem);
}
c.close();
statement.close();
statement = this.mDb.getPreparedStatement(selfOutSql, new String[]{address, Integer.toString(Out.OutStatus.unspent.getValue())});
c = statement.executeQuery();
while (c.next()) {
outItems.add(TxHelper.applyCursorOut(c));
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return outItems;
}
public List<Out> getUnSpendOutButNotConfirmWithAddress(String address) {
List<Out> outItems = new ArrayList<Out>();
String selfOutSql = "select a.* from outs a,txs b where a.tx_hash=b.tx_hash and b.block_no" +
" is null and a.out_address=? and a.out_status=? and b.source=0";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(selfOutSql, new String[]{address, Integer.toString(Out.OutStatus.unspent.getValue())});
ResultSet c = statement.executeQuery();
while (c.next()) {
outItems.add(TxHelper.applyCursorOut(c));
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return outItems;
}
public int txCount(String address) {
int result = 0;
try {
String sql = "select count(*) cnt from addresses_txs where address=?";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{address});
ResultSet c = statement.executeQuery();
if (c.next()) {
int idColumn = c.findColumn("cnt");
if (idColumn != -1) {
result = c.getInt(idColumn);
}
}
c.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
public long totalReceive(String address) {
long result = 0;
try {
String sql = "select sum(aa.receive-ifnull(bb.send,0)) sum" +
" from (select a.tx_hash,sum(a.out_value) receive " +
" from outs a where a.out_address=?" +
" group by a.tx_hash) aa LEFT OUTER JOIN " +
" (select b.tx_hash,sum(a.out_value) send" +
" from outs a, ins b" +
" where a.tx_hash=b.prev_tx_hash and a.out_sn=b.prev_out_sn and a.out_address=?" +
" group by b.tx_hash) bb on aa.tx_hash=bb.tx_hash " +
" where aa.receive>ifnull(bb.send, 0)";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{address, address});
ResultSet c = statement.executeQuery();
if (c.next()) {
int idColumn = c.findColumn("sum");
if (idColumn != -1) {
result = c.getLong(idColumn);
}
}
c.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public void txSentBySelfHasSaw(byte[] txHash) {
String sql = "update txs set source=source+1 where tx_hash=? and source>=1";
mDb.executeUpdate(sql, new String[]{Base58.encode(txHash)});
}
public List<Out> getOuts() {
List<Out> outItemList = new ArrayList<Out>();
String sql = "select * from outs ";
try {
PreparedStatement preparedStatement = this.mDb.getPreparedStatement(sql, null);
ResultSet c = preparedStatement.executeQuery();
while (c.next()) {
outItemList.add(TxHelper.applyCursorOut(c));
}
c.close();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (AddressFormatException e) {
e.printStackTrace();
}
return outItemList;
}
public List<Tx> getRecentlyTxsByAddress(String address, int greateThanBlockNo, int limit) {
List<Tx> txItemList = new ArrayList<Tx>();
String sql = "select b.* from addresses_txs a, txs b where a.tx_hash=b.tx_hash and a.address=? " +
"and ((b.block_no is null) or (b.block_no is not null and b.block_no>?)) " +
"order by ifnull(b.block_no,4294967295) desc, b.tx_time desc " +
"limit ? ";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{address, Integer.toString(greateThanBlockNo), Integer.toString(limit)});
ResultSet c = statement.executeQuery();
while (c.next()) {
Tx txItem = TxHelper.applyCursor(c);
txItemList.add(txItem);
}
for (Tx item : txItemList) {
TxHelper.addInsAndOuts(this.mDb, item);
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return txItemList;
}
public List<Long> txInValues(byte[] txHash) {
List<Long> inValues = new ArrayList<Long>();
try {
String sql = "select b.out_value " +
"from ins a left outer join outs b on a.prev_tx_hash=b.tx_hash and a.prev_out_sn=b.out_sn " +
"where a.tx_hash=?";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Base58.encode(txHash)});
ResultSet c = statement.executeQuery();
while (c.next()) {
int idColumn = c.findColumn("out_value");
if (idColumn != -1) {
inValues.add(c.getLong(idColumn));
} else {
inValues.add(null);
}
}
c.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return inValues;
}
public HashMap<Sha256Hash, Tx> getTxDependencies(Tx txItem) {
HashMap<Sha256Hash, Tx> result = new HashMap<Sha256Hash, Tx>();
try {
for (In inItem : txItem.getIns()) {
Tx tx;
String txHashStr = Base58.encode(inItem.getTxHash());
String sql = "select * from txs where tx_hash=?";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{txHashStr});
ResultSet c = statement.executeQuery();
if (c.next()) {
tx = TxHelper.applyCursor(c);
c.close();
statement.close();
} else {
c.close();
statement.close();
continue;
}
TxHelper.addInsAndOuts(this.mDb, tx);
result.put(new Sha256Hash(tx.getTxHash()), tx);
}
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public boolean isTxDoubleSpendWithConfirmedTx(Tx tx) {
String sql = "select count(0) cnt from ins a, txs b where a.tx_hash=b.tx_hash and" +
" b.block_no is not null and a.prev_tx_hash=? and a.prev_out_sn=?";
ResultSet rs;
try {
for (In inItem : tx.getIns()) {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Base58.encode(inItem.getPrevTxHash()), Integer.toString(inItem.getPrevOutSn())});
rs = statement.executeQuery();
if (rs.next()) {
int columnIndex = rs.findColumn("cnt");
if (columnIndex != -1 && rs.getInt(columnIndex) > 0) {
rs.close();
statement.close();
return true;
}
}
rs.close();
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public List<String> getInAddresses(Tx tx) {
List<String> result = new ArrayList<String>();
String sql = "select out_address from outs where tx_hash=? and out_sn=?";
ResultSet c;
try {
for (In inItem : tx.getIns()) {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Base58.encode(inItem.getPrevTxHash()), Integer.toString(inItem.getPrevOutSn())});
c = statement.executeQuery();
if (c.next()) {
int column = c.findColumn("out_address");
if (column != -1) {
result.add(c.getString(column));
}
}
c.close();
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public void completeInSignature(final List<In> ins) {
try {
this.mDb.getConn().setAutoCommit(false);
String sql = "update ins set in_signature=? where tx_hash=? and in_sn=? and ifnull(in_signature,'')=''";
for (In in : ins) {
PreparedStatement preparedStatement = this.mDb.getConn().prepareStatement(sql);
preparedStatement.setString(1, Base58.encode(in.getInSignature()));
preparedStatement.setString(2, Base58.encode(in.getTxHash()));
preparedStatement.setInt(3, in.getInSn());
preparedStatement.executeUpdate();
preparedStatement.close();
}
this.mDb.getConn().commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public int needCompleteInSignature(String address) {
int result = 0;
String sql = "select max(txs.block_no) as block_no from outs,ins,txs where outs.out_address='" + address +
"' and ins.prev_tx_hash=outs.tx_hash and ins.prev_out_sn=outs.out_sn " +
" and ifnull(ins.in_signature,'')='' and txs.tx_hash=ins.tx_hash";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, null);
ResultSet c = statement.executeQuery();
if (c.next()) {
int index = c.findColumn("block_no");
if (index != -1) {
result = c.getInt(index);
}
}
c.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public List<Out> getUnSpentOuts() {
List<Out> outItemList = new ArrayList<Out>();
String sql = "select * from outs where out_status=0";
try {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, null);
ResultSet c = statement.executeQuery();
while (c.next()) {
outItemList.add(TxHelper.applyCursorOut(c));
}
c.close();
statement.close();
} catch (AddressFormatException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return outItemList;
}
public boolean isAddressContainsTx(String address, Tx txItem) {
boolean result = false;
try {
String sql = "select count(0) cnt from ins a, txs b where a.tx_hash=b.tx_hash and" +
" b.block_no is not null and a.prev_tx_hash=? and a.prev_out_sn=?";
ResultSet c;
for (In inItem : txItem.getIns()) {
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{Base58.encode(inItem.getPrevTxHash()), Integer.toString(inItem.getPrevOutSn())});
c = statement.executeQuery();
if (c.next()) {
int columnIndex = c.findColumn("cnt");
if (columnIndex != -1 && c.getInt(columnIndex) > 0) {
c.close();
statement.close();
return false;
}
}
c.close();
statement.close();
}
sql = "select count(0) cnt from addresses_txs where tx_hash=? and address=?";
PreparedStatement statement = this.mDb.getPreparedStatement(sql, new String[]{
Base58.encode(txItem.getTxHash()), address
});
c = statement.executeQuery();
int count = 0;
if (c.next()) {
int columnIndex = c.findColumn("cnt");
if (columnIndex != -1) {
count = c.getInt(columnIndex);
}
}
c.close();
statement.close();
if (count > 0) {
return true;
}
sql = "select count(0) cnt from outs where tx_hash=? and out_sn=? and out_address=?";
for (In inItem : txItem.getIns()) {
statement = this.mDb.getPreparedStatement(sql, new String[]{Base58.encode(inItem.getPrevTxHash())
, Integer.toString(inItem.getPrevOutSn()), address});
c = statement.executeQuery();
count = 0;
if (c.next()) {
int columnIndex = c.findColumn("cnt");
if (columnIndex != -1) {
count = c.getInt(columnIndex);
}
}
c.close();
statement.close();
if (count > 0) {
return true;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public void clearAllTx() {
this.mDb.rebuildTx();
}
private static class AddressTx {
private String address;
private String txHash;
public AddressTx(String address, String txHash) {
this.address = address;
this.txHash = txHash;
}
public String getTxHash() {
return txHash;
}
public void setTxHash(String txHash) {
this.txHash = txHash;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
}