Create PostgreSQL Tables

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

• xmo_keyimages: Contains transaction rings and key images
• xmo_outputs: Contains the transaction outputs
• xmr_xmo_keyimages: Contains the rows of xmr_keyimages whose key images appear in xmo_keyimages

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

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

   CREATE TABLE xmo_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-original directory to create the xmo_outputs table.

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

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

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

   python3 populate_xmo_tables.py
   

   This script will query the Monero Original client and populate the xmo_keyimages and xmo_outputs tables with non-coinbase transactions from block height 1,546,000 to block height 1,784,681. The Monero Original blockchain diverged from the main Monero chain at block height 1,546,000.

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

7. Create the xmr_xmo_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 Original chain.

   • Run the following commands to enter the psql shell.

     sudo su postgres
     psql
     

   • Run the following query.

     CREATE TABLE xmr_xmo_keyimages AS (SELECT xmrk.* FROM xmr_keyimages xmrk INNER JOIN xmo_keyimages xmok ON xmrk.image=xmok.image);