Playing with the Management Database

It is not completely trivial to get a copy of the database from running the engine alone. One way of doing that is from the test suite, after disabling the line

success = nftw((engine_home + "/.").c_str(), util_delete_filepath, 100, FTW_DEPTH);

in test/src/Engine.cc . At this point it is possible to copy some .db files; however some test cases will generate database for older user versions; it is much better to extract a management database from a running engine.

Volker suggests using the Python adapter instead:

[luca@moore ~/pep-src/pep-python-adapter/test]$ HOME=$PWD python3 sync_test.py 

This generates management databases under the Phone/ and Laptop/ subdirectories:

[luca@moore ~/pep-src/pep-python-adapter/test]$ ls Phone/.pEp/
total 2440
   4 keys.db     4 management.db    32 keys.db-shm    32 management.db-shm  1212 keys.db-wal  1156 management.db-wal

The management database can be opened and played with interactively using sqlite3. When analyzing performance it can be enlightening to look at the output of EXPLAIN QUERY PLAN SELECT … and also at EXPLAIN SELECT … which prints a readable dump of the executed bytecode.

Of course before looking at query plans one should not forget to fill the database (query plans may be different with almost empty tables) and run

ANALYZE;

(and VACUUM does not hurt either.) The database can be filled with random data generated by some crude script. Example:

#!/usr/bin/python3

import random

letters='abcdefghijklmnopqrstuvwxyz'
hex_digits='0123456789abcdef'

row_no = 10000

def random_string (characters, length):
    res = ''
    for i in range (length):
        res += characters [random.randint (0, len (characters) - 1)]
    return res

def random_name ():
    return random_string (letters, 8)

def random_key_id ():
    return random_string (hex_digits, 16)

for i in range (row_no):
    name = random_name ()
    userid = name + '@foo.bar'
    key = random_key_id ()
    print ("""
INSERT INTO pgp_keypair
   (fpr, created, expires, comment)
VALUES
   ('%s', NULL, NULL, NULL);""" % (key))
    print ("""
INSERT INTO trust
   (user_id, pgp_keypair_fpr, comm_type)
VALUES
   ('%s', '%s', 1);""" % (userid, key))
    print ("""
INSERT INTO person
   (id, username, main_key_id, lang, comment, is_pEp_user)
VALUES
   ('%s', '%s', '%s', 'en', NULL, 1);""" % (userid, name, key))
    print ("""
INSERT INTO identity
   (address, user_id, main_key_id)
VALUES
   ('%s', '%s', '%s');""" % (userid, userid, key))
    print ()

The generated SQL script can be kept and reused, for reproducibility’s sake.