Create Outputs Table

There is a file called create_outputs_table.sql in the scripts/monero directory with the following contents.

CREATE TABLE xmr_outputs
(
    address       VARCHAR(64),
    id            SERIAL PRIMARY KEY,
    amount        BIGINT,
    index         INTEGER,
    UNIQUE(amount, index)
)

Run the following command in the scripts/monero directory to create the xmr_outputs table.
```
psql -U postgres -h 127.0.0.1 -W -f create_outputs_table.sql
```
NOTE: Alternatively, you can create the xmr_outputs table by running the script called output_table_creation.sh in the scripts/monero directory. You can run the command source output_table_creation.sh and enter the Postgres user password when prompted.
Populate the xmr_outputs table using the following steps.
- Run the following commands to enter the psql shell.
```
sudo su postgres
psql
```
- Run ONLY ONE of the following queries. These queries consider transactions upto block height 1541236. This block height was used by Yu et al in their FC 2019 paper.
  - Run the following query to consider all outputs (RingCT and pre-RingCT).
```
INSERT INTO xmr_outputs(amount, index) SELECT ring_amount, UNNEST(ring_indices) FROM xmr_keyimages WHERE block_height <= 1541236 ON CONFLICT(amount, index) DO NOTHING;
```
  - To consider only RingCT outputs, run the following query. The difference from the previous query is the AND ring_amount=0 clause.
```
INSERT INTO xmr_outputs(amount, index) SELECT ring_amount, UNNEST(ring_indices) FROM xmr_keyimages WHERE block_height <= 1541236 AND ring_amount=0 ON CONFLICT(amount, index) DO NOTHING;
```
NOTE: The point of creating the xmr_outputs table is to generate a unique integer index for every output. Then any edge in the CryptoNote transaction graph can be represented as a pair of integers: the key image index and the output index.

CryptoNote Transaction Graph Analysis

Create Outputs Table