public StoredTransactionOutput getTransactionOutput(Sha256Hash hash, long index)
     throws BlockStoreException {
   maybeConnect();
   PreparedStatement s = null;
   try {
     s =
         conn.get()
             .prepareStatement(
                 "SELECT height, value, scriptBytes FROM openOutputs "
                     + "WHERE hash = ? AND index = ?");
     s.setBytes(1, hash.getBytes());
     // index is actually an unsigned int
     s.setInt(2, (int) index);
     ResultSet results = s.executeQuery();
     if (!results.next()) {
       return null;
     }
     // Parse it.
     int height = results.getInt(1);
     BigInteger value = new BigInteger(results.getBytes(2));
     // Tell the StoredTransactionOutput that we are a coinbase, as that is encoded in height
     StoredTransactionOutput txout =
         new StoredTransactionOutput(hash, index, value, height, true, results.getBytes(3));
     return txout;
   } catch (SQLException ex) {
     throw new BlockStoreException(ex);
   } finally {
     if (s != null)
       try {
         s.close();
       } catch (SQLException e) {
         throw new BlockStoreException("Failed to close PreparedStatement");
       }
   }
 }
  private void initFromDatabase() throws SQLException, BlockStoreException {
    Statement s = conn.get().createStatement();
    ResultSet rs;

    rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'");
    if (!rs.next()) {
      throw new BlockStoreException("corrupt Postgres block store - no chain head pointer");
    }
    Sha256Hash hash = new Sha256Hash(rs.getBytes(1));
    rs.close();
    this.chainHeadBlock = get(hash);
    this.chainHeadHash = hash;
    if (this.chainHeadBlock == null) {
      throw new BlockStoreException("corrupt Postgres block store - head block not found");
    }

    rs =
        s.executeQuery(
            "SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'");
    if (!rs.next()) {
      throw new BlockStoreException(
          "corrupt Postgres block store - no verified chain head pointer");
    }
    hash = new Sha256Hash(rs.getBytes(1));
    rs.close();
    s.close();
    this.verifiedChainHeadBlock = get(hash);
    this.verifiedChainHeadHash = hash;
    if (this.verifiedChainHeadBlock == null) {
      throw new BlockStoreException("corrupt Postgres block store - verified head block not found");
    }
  }
  /**
   * Calculate the balance for a coinbase, to-address, or p2sh address.
   *
   * @param address The address to calculate the balance of
   * @return The balance of the address supplied. If the address has not been seen, or there are no
   *     outputs open for this address, the return value is 0
   * @throws BlockStoreException
   */
  public BigInteger calculateBalanceForAddress(Address address) throws BlockStoreException {
    maybeConnect();
    PreparedStatement s = null;

    try {
      s =
          conn.get()
              .prepareStatement(
                  "select sum(('x'||lpad(substr(value::text, 3, 50),16,'0'))::bit(64)::bigint) "
                      + "from openoutputs where toaddress = ?");
      s.setString(1, address.toString());
      ResultSet rs = s.executeQuery();
      if (rs.next()) {
        return BigInteger.valueOf(rs.getLong(1));
      } else {
        throw new BlockStoreException("Failed to execute balance lookup");
      }

    } catch (SQLException ex) {
      throw new BlockStoreException(ex);
    } finally {
      if (s != null)
        try {
          s.close();
        } catch (SQLException e) {
          throw new BlockStoreException("Could not close statement");
        }
    }
  }
 private boolean tableExists(String table) throws SQLException {
   Statement s = conn.get().createStatement();
   try {
     ResultSet results = s.executeQuery("SELECT * FROM " + table + " WHERE 1 = 2");
     results.close();
     return true;
   } catch (SQLException ex) {
     return false;
   } finally {
     s.close();
   }
 }
 public boolean hasUnspentOutputs(Sha256Hash hash, int numOutputs) throws BlockStoreException {
   maybeConnect();
   PreparedStatement s = null;
   try {
     s = conn.get().prepareStatement("SELECT COUNT(*) FROM openOutputs WHERE hash = ?");
     s.setBytes(1, hash.getBytes());
     ResultSet results = s.executeQuery();
     if (!results.next()) {
       throw new BlockStoreException("Got no results from a COUNT(*) query");
     }
     int count = results.getInt(1);
     return count != 0;
   } catch (SQLException ex) {
     throw new BlockStoreException(ex);
   } finally {
     if (s != null)
       try {
         s.close();
       } catch (SQLException e) {
         throw new BlockStoreException("Failed to close PreparedStatement");
       }
   }
 }
  public StoredBlock get(Sha256Hash hash, boolean wasUndoableOnly) throws BlockStoreException {
    // Optimize for chain head
    if (chainHeadHash != null && chainHeadHash.equals(hash)) return chainHeadBlock;
    if (verifiedChainHeadHash != null && verifiedChainHeadHash.equals(hash))
      return verifiedChainHeadBlock;
    maybeConnect();
    PreparedStatement s = null;
    try {
      s =
          conn.get()
              .prepareStatement(
                  "SELECT chainWork, height, header, wasUndoable FROM headers WHERE hash = ?");
      // We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes
      byte[] hashBytes = new byte[28];
      System.arraycopy(hash.getBytes(), 3, hashBytes, 0, 28);
      s.setBytes(1, hashBytes);
      ResultSet results = s.executeQuery();
      if (!results.next()) {
        return null;
      }
      // Parse it.

      if (wasUndoableOnly && !results.getBoolean(4)) return null;

      BigInteger chainWork = new BigInteger(results.getBytes(1));
      int height = results.getInt(2);
      Block b = new Block(params, results.getBytes(3));
      b.verifyHeader();
      StoredBlock stored = new StoredBlock(b, chainWork, height);
      return stored;
    } catch (SQLException ex) {
      throw new BlockStoreException(ex);
    } catch (ProtocolException e) {
      // Corrupted database.
      throw new BlockStoreException(e);
    } catch (VerificationException e) {
      // Should not be able to happen unless the database contains bad
      // blocks.
      throw new BlockStoreException(e);
    } finally {
      if (s != null)
        try {
          s.close();
        } catch (SQLException e) {
          throw new BlockStoreException("Failed to close PreparedStatement");
        }
    }
  }
  public StoredUndoableBlock getUndoBlock(Sha256Hash hash) throws BlockStoreException {
    maybeConnect();
    PreparedStatement s = null;
    try {
      s =
          conn.get()
              .prepareStatement(
                  "SELECT txOutChanges, transactions FROM undoableBlocks WHERE hash = ?");
      // We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes

      byte[] hashBytes = new byte[28];
      System.arraycopy(hash.getBytes(), 3, hashBytes, 0, 28);
      s.setBytes(1, hashBytes);
      ResultSet results = s.executeQuery();
      if (!results.next()) {
        return null;
      }
      // Parse it.
      byte[] txOutChanges = results.getBytes(1);
      byte[] transactions = results.getBytes(2);
      StoredUndoableBlock block;
      if (txOutChanges == null) {
        int offset = 0;
        int numTxn =
            ((transactions[offset++] & 0xFF) << 0)
                | ((transactions[offset++] & 0xFF) << 8)
                | ((transactions[offset++] & 0xFF) << 16)
                | ((transactions[offset++] & 0xFF) << 24);
        List<Transaction> transactionList = new LinkedList<Transaction>();
        for (int i = 0; i < numTxn; i++) {
          Transaction tx = new Transaction(params, transactions, offset);
          transactionList.add(tx);
          offset += tx.getMessageSize();
        }
        block = new StoredUndoableBlock(hash, transactionList);
      } else {
        TransactionOutputChanges outChangesObject =
            new TransactionOutputChanges(new ByteArrayInputStream(txOutChanges));
        block = new StoredUndoableBlock(hash, outChangesObject);
      }
      return block;
    } catch (SQLException ex) {
      throw new BlockStoreException(ex);
    } catch (NullPointerException e) {
      // Corrupted database.
      throw new BlockStoreException(e);
    } catch (ClassCastException e) {
      // Corrupted database.
      throw new BlockStoreException(e);
    } catch (ProtocolException e) {
      // Corrupted database.
      throw new BlockStoreException(e);
    } catch (IOException e) {
      // Corrupted database.
      throw new BlockStoreException(e);
    } finally {
      if (s != null)
        try {
          s.close();
        } catch (SQLException e) {
          throw new BlockStoreException("Failed to close PreparedStatement");
        }
    }
  }
  public void put(StoredBlock storedBlock, StoredUndoableBlock undoableBlock)
      throws BlockStoreException {
    maybeConnect();
    // We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes
    byte[] hashBytes = new byte[28];
    System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 3, 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((int) (0xFF & (numTxn >> 0)));
        bos.write((int) (0xFF & (numTxn >> 8)));
        bos.write((int) (0xFF & (numTxn >> 16)));
        bos.write((int) (0xFF & (numTxn >> 24)));
        for (Transaction tx : undoableBlock.getTransactions()) tx.rimbitSerialize(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.bytesToHexString(hashBytes));

      PreparedStatement findS =
          conn.get().prepareStatement("select 1 from undoableBlocks where hash = ?");
      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 rimbitj
        PreparedStatement s =
            conn.get()
                .prepareStatement(
                    "UPDATE undoableBlocks SET txOutChanges=?, transactions=?" + " WHERE hash = ?");
        s.setBytes(3, hashBytes);

        if (log.isDebugEnabled())
          log.debug("Updating undoable block with hash: " + Utils.bytesToHexString(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(
                  "INSERT INTO undoableBlocks(hash, height, txOutChanges, transactions)"
                      + " VALUES(?, ?, ?, ?)");
      s.setBytes(1, hashBytes);
      s.setInt(2, height);

      if (log.isDebugEnabled())
        log.debug(
            "Inserting undoable block with hash: "
                + Utils.bytesToHexString(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);
    }
  }
  /**
   * Dumps information about the size of actual data in the database to standard output The only
   * truly useless data counted is printed in the form "N in id indexes" This does not take database
   * indexes into account
   */
  public void dumpSizes() throws SQLException, BlockStoreException {
    maybeConnect();
    Statement s = conn.get().createStatement();
    long size = 0;
    long totalSize = 0;
    int count = 0;
    ResultSet rs = s.executeQuery("SELECT name, value FROM settings");
    while (rs.next()) {
      size += rs.getString(1).length();
      size += rs.getBytes(2).length;
      count++;
    }
    rs.close();
    System.out.printf(
        "Settings size: %d, count: %d, average size: %f%n", size, count, (double) size / count);

    totalSize += size;
    size = 0;
    count = 0;
    rs = s.executeQuery("SELECT chainWork, header FROM headers");
    while (rs.next()) {
      size += 28; // hash
      size += rs.getBytes(1).length;
      size += 4; // height
      size += rs.getBytes(2).length;
      count++;
    }
    rs.close();
    System.out.printf(
        "Headers size: %d, count: %d, average size: %f%n", size, count, (double) size / count);

    totalSize += size;
    size = 0;
    count = 0;
    rs = s.executeQuery("SELECT txOutChanges, transactions FROM undoableBlocks");
    while (rs.next()) {
      size += 28; // hash
      size += 4; // height
      byte[] txOutChanges = rs.getBytes(1);
      byte[] transactions = rs.getBytes(2);
      if (txOutChanges == null) size += transactions.length;
      else size += txOutChanges.length;
      // size += the space to represent NULL
      count++;
    }
    rs.close();
    System.out.printf(
        "Undoable Blocks size: %d, count: %d, average size: %f%n",
        size, count, (double) size / count);

    totalSize += size;
    size = 0;
    count = 0;
    long scriptSize = 0;
    rs = s.executeQuery("SELECT value, scriptBytes FROM openOutputs");
    while (rs.next()) {
      size += 32; // hash
      size += 4; // index
      size += 4; // height
      size += rs.getBytes(1).length;
      size += rs.getBytes(2).length;
      scriptSize += rs.getBytes(2).length;
      count++;
    }
    rs.close();
    System.out.printf(
        "Open Outputs size: %d, count: %d, average size: %f, average script size: %f (%d in id indexes)%n",
        size, count, (double) size / count, (double) scriptSize / count, count * 8);

    totalSize += size;
    System.out.println("Total Size: " + totalSize);

    s.close();
  }