Create PostgreSQL Tables

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

• xmv_keyimages: Contains transaction rings and key images
• xmv_outputs: Contains the transaction outputs
• xmr_xmv_keyimages: Contains the rows of xmr_keyimages whose key images appear in xmv_keyimages

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

   CREATE TABLE xmv_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/monerov directory to create the xmv_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/monerov directory with the following contents.

   CREATE TABLE xmv_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/monerov directory to create the xmv_outputs table.

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

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

5. Run the populate_xmv_tables.py script that is located in the scripts/hardforks/monerov directory.

   python3 populate_xmv_tables.py
   

   This script will query the MoneroV client and populate the xmv_keyimages and xmv_outputs tables with non-coinbase transactions from block height 1,564,966 to block height 1,711,290. The MoneroV blockchain diverged from the main Monero chain at block height 1,564,966.

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

7. Create the xmr_xmv_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 MoneroV chain.

   • Run the following commands to enter the psql shell.

     sudo su postgres
     psql
     

   • Run the following query.

     CREATE TABLE xmr_xmv_keyimages AS (SELECT xmrk.* FROM xmr_keyimages xmrk INNER JOIN xmv_keyimages xmvk ON xmrk.image=xmvk.image);