/*
* Copyright 2014 BitPOS Pty Ltd.
* Copyright 2014 Andreas Schildbach
* Copyright 2014 Kalpesh Parmar
*
* 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 org.bitcoinj.store;
import org.bitcoinj.core.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.annotation.Nullable;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
/**
* <p>A full pruned block store using the Postgres database engine. As an added bonus an address index is calculated,
* so you can use {@link #calculateBalanceForAddress(org.bitcoinj.core.Address)} to quickly look up
* the quantity of bitcoins controlled by that address.</p>
*/
public class PostgresFullPrunedBlockStore extends DatabaseFullPrunedBlockStore {
private static final Logger log = LoggerFactory.getLogger(PostgresFullPrunedBlockStore.class);
private static final String POSTGRES_DUPLICATE_KEY_ERROR_CODE = "23505";
private static final String DATABASE_DRIVER_CLASS = "org.postgresql.Driver";
private static final String DATABASE_CONNECTION_URL_PREFIX = "jdbc:postgresql://";
// create table SQL
private static final String CREATE_SETTINGS_TABLE = "CREATE TABLE settings (\n" +
" name character varying(32) NOT NULL,\n" +
" value bytea,\n" +
" CONSTRAINT setting_pk PRIMARY KEY (name)\n" +
")\n";
private static final String CREATE_HEADERS_TABLE = "CREATE TABLE headers (\n" +
" hash bytea NOT NULL,\n" +
" chainwork bytea NOT NULL,\n" +
" height integer NOT NULL,\n" +
" header bytea NOT NULL,\n" +
" wasundoable boolean NOT NULL,\n" +
" CONSTRAINT headers_pk PRIMARY KEY (hash)\n" +
")\n";
private static final String CREATE_UNDOABLE_TABLE = "CREATE TABLE undoableblocks (\n" +
" hash bytea NOT NULL,\n" +
" height integer NOT NULL,\n" +
" txoutchanges bytea,\n" +
" transactions bytea,\n" +
" CONSTRAINT undoableblocks_pk PRIMARY KEY (hash)\n" +
")\n";
private static final String CREATE_OPEN_OUTPUT_TABLE = "CREATE TABLE openoutputs (\n" +
" hash bytea NOT NULL,\n" +
" index integer NOT NULL,\n" +
" height integer NOT NULL,\n" +
" value bigint NOT NULL,\n" +
" scriptbytes bytea NOT NULL,\n" +
" toaddress character varying(35),\n" +
" addresstargetable smallint,\n" +
" coinbase boolean,\n" +
" CONSTRAINT openoutputs_pk PRIMARY KEY (hash,index)\n" +
")\n";
// Some indexes to speed up inserts
private static final String CREATE_OUTPUTS_ADDRESS_MULTI_INDEX = "CREATE INDEX openoutputs_hash_index_num_height_toaddress_idx ON openoutputs USING btree (hash, index, height, toaddress)";
private static final String CREATE_OUTPUTS_TOADDRESS_INDEX = "CREATE INDEX openoutputs_toaddress_idx ON openoutputs USING btree (toaddress)";
private static final String CREATE_OUTPUTS_ADDRESSTARGETABLE_INDEX = "CREATE INDEX openoutputs_addresstargetable_idx ON openoutputs USING btree (addresstargetable)";
private static final String CREATE_OUTPUTS_HASH_INDEX = "CREATE INDEX openoutputs_hash_idx ON openoutputs USING btree (hash)";
private static final String CREATE_UNDOABLE_TABLE_INDEX = "CREATE INDEX undoableblocks_height_idx ON undoableBlocks USING btree (height)";
private static final String SELECT_UNDOABLEBLOCKS_EXISTS_SQL = "select 1 from undoableblocks where hash = ?";
/**
* Creates a new PostgresFullPrunedBlockStore.
*
* @param params A copy of the NetworkParameters used
* @param fullStoreDepth The number of blocks of history stored in full (something like 1000 is pretty safe)
* @param hostname The hostname of the database to connect to
* @param dbName The database to connect to
* @param username The database username
* @param password The password to the database
* @throws BlockStoreException if the database fails to open for any reason
*/
public PostgresFullPrunedBlockStore(NetworkParameters params, int fullStoreDepth, String hostname, String dbName,
String username, String password) throws BlockStoreException {
super(params, DATABASE_CONNECTION_URL_PREFIX + hostname + "/" + dbName, fullStoreDepth, username, password, null);
}
/**
* <p>Create a new PostgresFullPrunedBlockStore, storing the tables in the schema specified. You may want to
* specify a schema to avoid name collisions, or just to keep the database better organized. The schema is not
* required, and if one is not provided than the default schema for the username will be used. See
* <a href="http://www.postgres.org/docs/9.3/static/ddl-schemas.html">the postgres schema docs</a> for more on
* schemas.</p>
*
* @param params A copy of the NetworkParameters used.
* @param fullStoreDepth The number of blocks of history stored in full (something like 1000 is pretty safe).
* @param hostname The hostname of the database to connect to.
* @param dbName The database to connect to.
* @param username The database username.
* @param password The password to the database.
* @param schemaName The name of the schema to put the tables in. May be null if no schema is being used.
* @throws BlockStoreException If the database fails to open for any reason.
*/
public PostgresFullPrunedBlockStore(NetworkParameters params, int fullStoreDepth, String hostname, String dbName,
String username, String password, @Nullable String schemaName) throws BlockStoreException {
super(params, DATABASE_CONNECTION_URL_PREFIX + hostname + "/" + dbName, fullStoreDepth, username, password, schemaName);
}
@Override
protected String getDuplicateKeyErrorCode() {
return POSTGRES_DUPLICATE_KEY_ERROR_CODE;
}
@Override
protected List<String> getCreateTablesSQL() {
List<String> sqlStatements = new ArrayList<String>();
sqlStatements.add(CREATE_SETTINGS_TABLE);
sqlStatements.add(CREATE_HEADERS_TABLE);
sqlStatements.add(CREATE_UNDOABLE_TABLE);
sqlStatements.add(CREATE_OPEN_OUTPUT_TABLE);
return sqlStatements;
}
@Override
protected List<String> getCreateIndexesSQL() {
List<String> sqlStatements = new ArrayList<String>();
sqlStatements.add(CREATE_UNDOABLE_TABLE_INDEX);
sqlStatements.add(CREATE_OUTPUTS_ADDRESS_MULTI_INDEX);
sqlStatements.add(CREATE_OUTPUTS_ADDRESSTARGETABLE_INDEX);
sqlStatements.add(CREATE_OUTPUTS_HASH_INDEX);
sqlStatements.add(CREATE_OUTPUTS_TOADDRESS_INDEX);
return sqlStatements;
}
@Override
protected List<String> getCreateSchemeSQL() {
List<String> sqlStatements = new ArrayList<String>();
sqlStatements.add("CREATE SCHEMA IF NOT EXISTS " + schemaName);
sqlStatements.add("set search_path to '" + schemaName +"'");
return sqlStatements;
}
@Override
protected String getDatabaseDriverClass() {
return DATABASE_DRIVER_CLASS;
}
@Override
public void put(StoredBlock storedBlock, StoredUndoableBlock undoableBlock) throws BlockStoreException {
maybeConnect();
// We skip the first 4 bytes because (on mainnet) the minimum target has 4 0-bytes
byte[] hashBytes = new byte[28];
System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 4, hashBytes, 0, 28);
int height = storedBlock.getHeight();
byte[] transactions = null;
byte[] txOutChanges = null;
try {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
if (undoableBlock.getTxOutChanges() != null) {
undoableBlock.getTxOutChanges().serializeToStream(bos);
txOutChanges = bos.toByteArray();
} else {
int numTxn = undoableBlock.getTransactions().size();
bos.write(0xFF & numTxn);
bos.write(0xFF & (numTxn >> 8));
bos.write(0xFF & (numTxn >> 16));
bos.write(0xFF & (numTxn >> 24));
for (Transaction tx : undoableBlock.getTransactions())
tx.bitcoinSerialize(bos);
transactions = bos.toByteArray();
}
bos.close();
} catch (IOException e) {
throw new BlockStoreException(e);
}
try {
if (log.isDebugEnabled())
log.debug("Looking for undoable block with hash: " + Utils.HEX.encode(hashBytes));
PreparedStatement findS = conn.get().prepareStatement(SELECT_UNDOABLEBLOCKS_EXISTS_SQL);
findS.setBytes(1, hashBytes);
ResultSet rs = findS.executeQuery();
if (rs.next())
{
// We already have this output, update it.
findS.close();
// Postgres insert-or-updates are very complex (and finnicky). This level of transaction isolation
// seems to work for bitcoinj
PreparedStatement s =
conn.get().prepareStatement(getUpdateUndoableBlocksSQL());
s.setBytes(3, hashBytes);
if (log.isDebugEnabled())
log.debug("Updating undoable block with hash: " + Utils.HEX.encode(hashBytes));
if (transactions == null) {
s.setBytes(1, txOutChanges);
s.setNull(2, Types.BINARY);
} else {
s.setNull(1, Types.BINARY);
s.setBytes(2, transactions);
}
s.executeUpdate();
s.close();
return;
}
PreparedStatement s =
conn.get().prepareStatement(getInsertUndoableBlocksSQL());
s.setBytes(1, hashBytes);
s.setInt(2, height);
if (log.isDebugEnabled())
log.debug("Inserting undoable block with hash: " + Utils.HEX.encode(hashBytes) + " at height " + height);
if (transactions == null) {
s.setBytes(3, txOutChanges);
s.setNull(4, Types.BINARY);
} else {
s.setNull(3, Types.BINARY);
s.setBytes(4, transactions);
}
s.executeUpdate();
s.close();
try {
putUpdateStoredBlock(storedBlock, true);
} catch (SQLException e) {
throw new BlockStoreException(e);
}
} catch (SQLException e) {
if (!e.getSQLState().equals(POSTGRES_DUPLICATE_KEY_ERROR_CODE))
throw new BlockStoreException(e);
}
}
}