## HEADER PROTOCOL

### insert header (set_link)

```
insert into headers (hash, flags, height, mtp, parent_id) values (...);
```

### Mark as candidate (push_candidate)


```
update headers set candidate = true where id = ...;
```
or

```update headers set candidate = true where hash = ...;
```

With libbitcoin database we set_link and then push_candidate

With SQL we can do upserts as below. This reduces the two calls to the
database into a single call.

```
insert into headers (hash, flags, height, mtp, parent_id) values (...)
       on conflict (hash) do update set candidate = true;
```

## BLOCK PROTOCOL

### insert block (set_link)

insert into headers (hash, flags, height, mtp, parent_id) values (...);

### save inputs for block (populate)

We have to save the input and the relationships to lookup
prevouts. Therefore we put them in a transaction and make sure this is
atomic and the lookups can scale to required.

```
BEGIN;
-- save the returned id in application variable
insert into outputs (output_index, value, script) values (0, 100, "dup verify") returning id;

-- save the returned id in application variable
insert into inputs (input_index, sequence, script) values (0, 1, "checksig") returning id;

-- use the ids received above
insert into prevouts (input_id, output_id) values(...);
COMMIT;
```

## confirm block (push_confirm)

We can use both ID and hash as the situation requires. We have an index on both.

In comparison to mmap store, we don't need store confirm and valid
status in separate append only logs. Here we run with the option to
update in place counting on the sql dbms to manage the updates.

If this is a bottleneck, we can optimise this by moving the confirm
and valid metadata to a separate `headers_state` table tracking this
metadata.

```
update headers set confirmed = true where id = ...;
```

```
update headers set confirmed = true where hash = ...;
```

# validate

## Get attributes

`get_height`, 
`is_candidate`, 
`is_confirmed_block`,
`is_confirmed_tx`, `is_confirmed_input`, `is_confirmed_output`

These are select on `id` - we also provide the same efficiency for lookup by `hash`.

## Get spenders for an output

`to_spenders` is used by 
`is_spent_output`, `is_spent_prevout` and can be queried by point or primary key.

We query the transactions_outputs table to get the output. Using the
schema from sqlite-schema.sql, we can query the output as:

```
select i.* from transactions tx, transactions_outputs txo, prevouts p, inputs i
where tx.hash = <point.hash> and
	  txo.transaction_id = tx.id and
	  txo.output_index = <point.index> and
	  p.output_id = txo.output_id and
	  i.id = p.input_id;
```

Expected analysis:
1. Index scan `transactions_hash_ix`. This is only a million entries,
   so it should require 50MB of RAM.
2. Index scan `prevouts_output_ix`. This is will be 2b (outputs) * avg
   number of outputs. Say 4b rows.
3. Index scan on `inputs_pk`.
4. Join the results of the above index scans.

The problem here is the index scan on `prevouts_output_ix`. This will
be a ~4b entries. I have seen postgresql handle 2b rows hash index
fitting under 50GB. This index will be close 100GB, definitely won't
be performant on user laptops.

Input: `is_mature`, `is_spent`, `is_strong`, `locked_input`

# Confirm

This goes back to the get spenders problem. We need to verify the
spenders are not spent.

`block_confirmable`
`set_strong`
`set_unstrong`