Create PostgreSQL Tables

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

• xmrv7_keyimages: Contains transaction rings and key images
• xmrv7_outputs: Contains the transaction outputs
• xmr_xmrv7_keyimages: Contains the rows of xmr_keyimages whose key images appear in xmrv7_keyimages

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

   CREATE TABLE xmrv7_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-v7 directory to create the xmrv7_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-v7 directory with the following contents.

   CREATE TABLE xmrv7_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-v7 directory to create the xmrv7_outputs table.

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

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

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

   python3 populate_xmrv7_tables.py
   

   This script will query the Monero v7 client and populate the xmrv7_keyimages and xmrv7_outputs tables with non-coinbase transactions from block height 1,685,555 to block height 1,685,583. The Monero v7 blockchain diverged from the main Monero chain at block height 1,685,555.

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

7. Create the xmr_xmrv7_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 v7 chain.

   • Run the following commands to enter the psql shell.

     sudo su postgres
     psql
     

   • Run the following query.

     CREATE TABLE xmr_xmrv7_keyimages AS (SELECT xmrk.* FROM xmr_keyimages xmrk INNER JOIN xmrv7_keyimages xmrv7k ON xmrk.image=xmrv7k.image);