Create Key Image Table

1. Install PostgreSQL if you have not already done so.

2. Set the password for the postgres user via the following commands.

   • sudo su postgres
   • psql
   • \password postgres

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

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

   Note: Some old transaction rings in Monero have repeated ring members. The distinct_ring_indices only stores the distinct ring member indices.

4. Run the following command in the scripts/monero directory to create the xmr_keyimages table.

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

   NOTE: Alternatively, you can create the xmr_keyimages table by running the script called keyimage_table_creation.sh in the scripts/monero directory. You can run the command source keyimage_table_creation.sh and enter the Postgres user password when prompted.

5. You can check that the table was successfully created by running the \dt command in the psql shell.

   • Run the following commands to enter the psql shell.

     sudo su postgres
     psql
     

   • Run the \dt command in the shell. The output should look like the following.

     postgres=# \dt
                 List of relations
      Schema |     Name      | Type  |  Owner   
     --------+---------------+-------+----------
      public | xmr_keyimages | table | postgres
     (1 row)
     
     

   • The xmr_keyimages table will be initially empty.

     postgres=# SELECT * FROM xmr_keyimages;
      image | id | ring_amount | ring_indices | distinct_ring_indices | block_height 
     -------+----+-------------+--------------+-----------------------+--------------
     (0 rows)
     

6. Run the Monero CLI client in offline mode using the following command. In this mode, the client will not download new blocks.

   ./monerod --offline
   

7. Run the populate_keyimage_table.py script that is located in the scripts/monero directory.

   python3 populate_keyimage_table.py
   

   This script will query the Monero client and populate the xmr_keyimages table with non-coinbase transactions from block height 0 to block height 2,530,000. The latter block was mined on January 4, 2022. This script can take more than a day to finish running.

   WARNING: Before running this script, don't forget to change the postgres user password in the script to the password you have set. It needs to be changed in the argument to psycopg2.connect().

8. Once the populate_keyimage_table.py script finishes running, you can stop the monerod client by pressing Ctrl-D in the CLI window.