Create PostgreSQL Tables

We will be using PostgreSQL tables to store the Monero v9 data. We will need three tables that are named as follows.

• xmrv9_keyimages: Contains transaction rings and key images
• xmrv9_outputs: Contains the transaction outputs
• xmr_xmrv9_keyimages: Contains the rows of xmr_keyimages whose key images appear in xmrv9_keyimages

1. There is a file called create_keyimages_table.sql in the scripts/hardforks/monero-v9 directory with the following contents.

   CREATE TABLE xmrv9_keyimages
   (
       image                   VARCHAR(64) NOT NULL,
       id                      SERIAL PRIMARY KEY,
       ring_amount             BIGINT,
       ring_indices            INTEGER[],
       block_height            INTEGER,
       UNIQUE(image)
   )
   

2. Run the following command in the scripts/hardforks/monero-v9 directory to create the xmrv9_keyimages table.

   psql -U postgres -h 127.0.0.1 -W -f create_keyimages_table.sql
   

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

   CREATE TABLE xmrv9_outputs
   (
       image                   VARCHAR(64) NOT NULL,
       id                      SERIAL PRIMARY KEY,
       ring_amount             BIGINT,
       ring_indices            INTEGER[],
       block_height            INTEGER,
       UNIQUE(image)
   )
   

4. Run the following command in the scripts/hardforks/monero-v9 directory to create the xmrv9_outputs table.

   psql -U postgres -h 127.0.0.1 -W -f create_outputs_table.sql
   

   NOTE: Alternatively, you can create the xmrv9_keyimages and xmrv9_outputs tables by running the script called sql_table_creation.sh in the scripts/hardforks/monero-v9 directory. You can run the command source sql_table_creation.sh and enter the Postgres user password when prompted.

5. Run the populate_xmrv9_tables.py script that is located in the scripts/hardforks/monero-v9 directory.

   python3 populate_xmrv9_tables.py
   

   This script will query the Monero v9 client and populate the xmrv9_keyimages and xmrv9_outputs tables with non-coinbase transactions from block height 1,788,000 to block height 1,788,072. The Monero v9 blockchain diverged from the main Monero chain at block height 1,788,000.

6. Once the populate_xmrv9_tables.py script finishes running, you can stop the Monero v9 client by pressing Ctrl-D in the CLI window.

7. Create the xmr_xmrv9_keyimages table using the following steps. It will contain the subset of xmr_keyimages that corresponds to key images which have appeared both on the main Monero chain and the Monero v9 chain.

   • Run the following commands to enter the psql shell.

     sudo su postgres
     psql
     

   • Run the following query.

     CREATE TABLE xmr_xmrv9_keyimages AS (SELECT xmrk.* FROM xmr_keyimages xmrk INNER JOIN xmrv9_keyimages xmrv9k ON xmrk.image=xmrv9k.image);