// This file is under GNU General Public License 3.0

 // see LICENSE.txt

 #include "pEp_internal.h"

 #include "dynamic_api.h"

 #include "cryptotech.h"

 #include "transport.h"

 #include "blacklist.h"

 #include <time.h>

 #include <stdlib.h>

 #define _PEP_SQLITE_DEBUG 0

 #if _PEP_SQLITE_DEBUG

 #include <sqlite3.h>

 #endif

 static volatile int init_count = -1;

 // sql overloaded functions - modified from sqlite3.c

 static void _sql_lower(sqlite3_context* ctx, int argc, sqlite3_value** argv) {

     char *z1;

     const char *z2;

     int i, n;

     z2 = (char*)sqlite3_value_text(argv[0]);

     n = sqlite3_value_bytes(argv[0]);

     /* Verify that the call to _bytes() does not invalidate the _text() pointer */

     assert( z2==(char*)sqlite3_value_text(argv[0]) );

     if( z2 ){

         z1 = (char*)sqlite3_malloc(n+1);

         if( z1 ){

             for(i=0; i<n; i++){

                 char c = z2[i];

                 char c_mod = c | 0x20;

                 if (c_mod < 0x61 || c_mod > 0x7a)

                     c_mod = c;

                 z1[i] = c_mod;

             }

             z1[n] = '\0';

             sqlite3_result_text(ctx, z1, n, sqlite3_free);

         }

     }

 }

 #if _PEP_SQLITE_DEBUG

 int sql_trace_callback (unsigned trace_constant, 

                         void* context_ptr,

                         void* P,

                         void* X) {

     switch (trace_constant) {

         case SQLITE_TRACE_STMT:

             fprintf(stderr, "SQL_DEBUG: STMT - ");

             const char* X_str = (const char*) X;

             if (!EMPTYSTR(X_str) && X_str[0] == '-' && X_str[1] == '-')

                 fprintf(stderr, "%s\n", X_str);

             else

                 fprintf(stderr, "%s\n", sqlite3_expanded_sql((sqlite3_stmt*)P));

             break;

         case SQLITE_TRACE_ROW:

             fprintf(stderr, "SQL_DEBUG: ROW - ");

             fprintf(stderr, "%s\n", sqlite3_expanded_sql((sqlite3_stmt*)P));

             break;            

         case SQLITE_TRACE_CLOSE:

             fprintf(stderr, "SQL_DEBUG: CLOSE - ");

             break;

         default:

             break;

     }

     return 0;

 }

 #endif

 // sql manipulation statements

 static const char *sql_log = 

     "insert into log (title, entity, description, comment)"

      "values (?1, ?2, ?3, ?4);";

 static const char *sql_trustword = 

     "select id, word from wordlist where lang = lower(?1) "

     "and id = ?2 ;";

 static const char *sql_get_identity =  

     "select fpr, username, comm_type, lang,"

     "   identity.flags | pgp_keypair.flags,"

     "   is_own"

     "   from identity"

     "   join person on id = identity.user_id"

     "   join pgp_keypair on fpr = identity.main_key_id"

     "   join trust on id = trust.user_id"

     "       and pgp_keypair_fpr = identity.main_key_id"    

     "   where (case when (address = ?1) then (1)"

     "               when (lower(address) = lower(?1)) then (1)"

     "               when (replace(lower(address),'.','') = replace(lower(?1),'.','')) then (1)"

     "               else 0"

     "          end) = 1"

     "   and identity.user_id = ?2" 

     "   order by is_own desc, "

     "   timestamp desc; ";

 static const char *sql_get_identity_without_trust_check =  

     "select identity.main_key_id, username, lang,"

     "   identity.flags, is_own"

     "   from identity"

     "   join person on id = identity.user_id"

     "   where (case when (address = ?1) then (1)"

     "               when (lower(address) = lower(?1)) then (1)"

     "               when (replace(lower(address),'.','') = replace(lower(?1),'.','')) then (1)"

     "               else 0"

     "          end) = 1"

     "   and identity.user_id = ?2 "

     "   order by is_own desc, "

     "   timestamp desc; ";

 static const char *sql_get_identities_by_address =  

     "select user_id, identity.main_key_id, username, lang,"

     "   identity.flags, is_own"

     "   from identity"

     "   join person on id = identity.user_id"

     "   where (case when (address = ?1) then (1)"

     "               when (lower(address) = lower(?1)) then (1)"

     "               when (replace(lower(address),'.','') = replace(lower(?1),'.','')) then (1)"

     "               else 0"

     "          end) = 1 "

     "   order by is_own desc, "

     "   timestamp desc; ";

 static const char *sql_replace_identities_fpr =  

     "update identity"

     "   set main_key_id = ?1 "

     "   where main_key_id = ?2 ;";

 static const char *sql_remove_fpr_as_default =

     "update person set main_key_id = NULL where main_key_id = ?1 ;"

     "update identity set main_key_id = NULL where main_key_id = ?1 ;";

 // Set person, but if already exist, only update.

 // if main_key_id already set, don't touch.

 static const char *sql_set_person = 

      "insert into person (id, username, lang, main_key_id, device_group)"

      "  values (?1, ?2, ?3, ?4, "

      "          (select device_group from person where id = ?1)) ;";

 static const char *sql_update_person = 

     "update person "

     "   set username = ?2, "

     "       lang = ?3, "

     "       main_key_id =  "

     "           (select coalesce( "

     "               (select main_key_id from person where id = ?1), " 

     "                upper(replace(?4,' ','')))),"         

     "       device_group = "

     "           (select device_group from person where id = ?1)"

     "   where id = ?1 ;";

 static const char *sql_set_as_pep_user =

     "update person set is_pep_user = 1 "

     "   where id = ?1 ; ";

 static const char *sql_is_pep_user =

     "select is_pep_user from person "

     "   where id = ?1 ; ";

 static const char* sql_exists_person = 

     "select count(*) from person "

     "   where id = ?1 ;";

 static const char *sql_set_device_group = 

     "update person set device_group = ?1 "

     "   where id = ?2;";

 // This will cascade to identity and trust

 static const char* sql_replace_userid =

     "update person set id = ?1 " 

     "   where id = ?2;";

 static const char *sql_replace_main_user_fpr =  

     "update person "

     "   set main_key_id = ?1 "

     "   where id = ?2 ;";

 static const char *sql_get_main_user_fpr =  

     "select main_key_id from person"

     "   where id = ?1 ;";

 static const char *sql_refresh_userid_default_key =

     "update person "

     "   set main_key_id = "

     "       (select identity.main_key_id from identity "

     "           join trust on trust.user_id = identity.user_id "

     "               and trust.pgp_keypair_fpr = identity.main_key_id "

     "           join person on identity.user_id = identity.user_id "

     "       where identity.user_id = ?1 "

     "       order by trust.comm_type desc "

     "       limit 1) "

     "where id = ?1 ; ";

 static const char *sql_get_device_group = 

     "select device_group from person "

     "where id = ?1;";

 static const char *sql_set_pgp_keypair = 

     "insert or ignore into pgp_keypair (fpr) "

     "values (upper(replace(?1,' ',''))) ;";

 static const char* sql_exists_identity_entry = 

     "select count(*) from identity "

     "   where (case when (address = ?1) then (1)"

     "               when (lower(address) = lower(?1)) then (1)"

     "               when (replace(lower(address),'.','') = replace(lower(?1),'.','')) then (1)"

     "               else 0"

     "          end) = 1"

     "    and user_id = ?2;";

 static const char *sql_set_identity_entry = 

     "insert into identity ("

     "       address, main_key_id, "

     "       user_id, flags, is_own"

     "   ) values ("

     "       ?1,"

     "       upper(replace(?2,' ','')),"

     "       ?3,"

     "       ?4,"

     "       ?5"

     "   );";

 static const char* sql_update_identity_entry =    

     "update identity "

     "   set main_key_id = upper(replace(?2,' ','')), "

     "       flags = ?4, " 

     "       is_own = ?5 "

     "   where (case when (address = ?1) then (1)"

     "               when (lower(address) = lower(?1)) then (1)"

     "               when (replace(lower(address),'.','') = replace(lower(?1),'.','')) then (1) "

     "               else 0 "

     "          end) = 1 "

     "          and user_id = ?3 ;";

     // " (select"

     // "   coalesce("

     // "    (select flags from identity"

     // "     where address = ?1 and"

     // "           user_id = ?3),"

     // "    0)"

     // " ) | (?4 & 255)"

     /* set_identity ignores previous flags, and doesn't filter machine flags */

 static const char *sql_set_identity_flags = 

     "update identity set flags = "

     "    ((?1 & 255) | (select flags from identity"

     "                    where (case when (address = ?2) then (1)"

     "                                when (lower(address) = lower(?2)) then (1)"

     "                                when (replace(lower(address),'.','') = replace(lower(?2),'.','')) then (1)"

     "                                else 0 "    

     "                           end) = 1 "

     "                           and user_id = ?3)) "

     "   where (case when (address = ?2) then (1)"

     "               when (lower(address) = lower(?2)) then (1)"

     "               when (replace(lower(address),'.','') = replace(lower(?2),'.','')) then (1)"

     "               else 0"

     "          end) = 1"

     "          and user_id = ?3 ;";

 static const char *sql_unset_identity_flags = 

     "update identity set flags = "

     "    ( ~(?1 & 255) & (select flags from identity"

     "                    where (case when (address = ?2) then (1)"

     "                                when (lower(address) = lower(?2)) then (1)"

     "                                when (replace(lower(address),'.','') = replace(lower(?2),'.','')) then (1)"

     "                                else 0 "    

     "                           end) = 1 "

     "                           and user_id = ?3)) "

     "   where (case when (address = ?2) then (1)"

     "               when (lower(address) = lower(?2)) then (1)"

     "               when (replace(lower(address),'.','') = replace(lower(?2),'.','')) then (1)"

     "               else 0"

     "          end) = 1"

     "          and user_id = ?3 ;";

 static const char *sql_set_trust =

     "insert into trust (user_id, pgp_keypair_fpr, comm_type) "

     "values (?1, upper(replace(?2,' ','')), ?3) ;";

 static const char *sql_update_trust =

     "update trust set comm_type = ?3 " 

     "   where user_id = ?1 and pgp_keypair_fpr = upper(replace(?2,' ',''));";

 static const char *sql_update_trust_to_pep =

     "update trust set comm_type = comm_type + 71 "

     "   where (user_id = ?1 "

     "          and (case when (comm_type = 56) then (1) "

     "                    when (comm_type = 184) then (1) "

     "                    else 0"

     "               end) = 1); ";

 static const char* sql_exists_trust_entry = 

     "select count(*) from trust "

     "   where user_id = ?1 and pgp_keypair_fpr = upper(replace(?2,' ',''));";

 static const char *sql_update_trust_for_fpr =

     "update trust "

     "set comm_type = ?1 "

     "where pgp_keypair_fpr = upper(replace(?2,' ','')) ;";

 static const char *sql_get_trust = 

     "select comm_type from trust where user_id = ?1 "

     "and pgp_keypair_fpr = upper(replace(?2,' ','')) ;";

 static const char *sql_least_trust = 

     "select min(comm_type) from trust where"

     " pgp_keypair_fpr = upper(replace(?1,' ',''))"

     " and comm_type != 0;"; // ignores PEP_ct_unknown

     // returns PEP_ct_unknown only when no known trust is recorded

 static const char *sql_mark_as_compromised = 

     "update trust not indexed set comm_type = 15"

     " where pgp_keypair_fpr = upper(replace(?1,' ','')) ;";

 static const char *sql_crashdump = 

     "select timestamp, title, entity, description, comment"

     " from log order by timestamp desc limit ?1 ;";

 static const char *sql_languagelist = 

     "select i18n_language.lang, name, phrase" 

     " from i18n_language join i18n_token using (lang) where i18n_token.id = 1000;" ;

 static const char *sql_i18n_token = 

     "select phrase from i18n_token where lang = lower(?1) and id = ?2 ;";

 // blacklist

 static const char *sql_blacklist_add = 

     "insert or ignore into blacklist_keys (fpr) values (upper(replace(?1,' ',''))) ;"

     "delete from identity where main_key_id = upper(replace(?1,' ','')) ;"

     "delete from pgp_keypair where fpr = upper(replace(?1,' ','')) ;";

 static const char *sql_blacklist_delete =

     "delete from blacklist_keys where fpr = upper(replace(?1,' ','')) ;";

 static const char *sql_blacklist_is_listed = 

     "select count(*) from blacklist_keys where fpr = upper(replace(?1,' ','')) ;";

 static const char *sql_blacklist_retrieve = 

     "select * from blacklist_keys ;";

 // Own keys

 // We only care if it's 0 or non-zero

 static const char *sql_own_key_is_listed = 

     "select count(*) from ("

     "   select pgp_keypair_fpr from trust"

     "      join identity on trust.user_id = identity.user_id"

     "      where pgp_keypair_fpr = upper(replace(?1,' ',''))"

     "           and identity.is_own = 1"

     ");";

 static const char *sql_own_identities_retrieve =  

     "select address, fpr, username, identity.user_id, "

     "   lang, identity.flags | pgp_keypair.flags"

     "   from identity"

     "   join person on id = identity.user_id"

     "   join pgp_keypair on fpr = identity.main_key_id"

     "   join trust on id = trust.user_id"

     "       and pgp_keypair_fpr = identity.main_key_id"

     "   where identity.is_own = 1"

     "       and (identity.flags & ?1) = 0;";

 static const char *sql_own_keys_retrieve = 

     "select pgp_keypair_fpr from trust"

     "   join identity on trust.user_id = identity.user_id"

     "   where identity.is_own = 1";

 static const char* sql_get_user_default_key =

     "select main_key_id from person" 

     "   where id = ?1;";

 static const char* sql_get_default_own_userid =

     "select id from person"

     "   join identity on id = identity.user_id"

     "   where identity.is_own = 1";

 // Sequence

 static const char *sql_sequence_value1 = 

     "insert or replace into sequences (name, value, own) "

     "values (?1, "

     "       (select coalesce((select value + 1 from sequences "

     "           where name = ?1), 1 )), "

     "       (select coalesce((select own or ?2 from sequences "

     "           where name = ?1), ?2))) ; ";

 static const char *sql_sequence_value2 = 

     "select value, own from sequences where name = ?1 ;";

 static const char *sql_sequence_value3 = 

     "insert or replace into sequences (name, value, own) "

     "values (?1, "

     "        ?2, "

     "       (select coalesce((select own or ?3 from sequences "

     "           where name = ?1), ?3))) ; ";

 // Revocation tracking

 static const char *sql_set_revoked =

     "insert or replace into revoked_keys ("

     "    revoked_fpr, replacement_fpr, revocation_date) "

     "values (upper(replace(?1,' ','')),"

     "        upper(replace(?2,' ','')),"

     "        ?3) ;";

 static const char *sql_get_revoked = 

     "select revoked_fpr, revocation_date from revoked_keys"

     "    where replacement_fpr = upper(replace(?1,' ','')) ;";

 static const char *sql_get_userid_alias_default =

     "select default_id from alternate_user_id "

     "   where alternate_id = ?1 ; ";

 // Revocation tracking

 static const char *sql_add_mistrusted_key =

     "insert or replace into mistrusted_keys (fpr) "

     "   values (upper(replace(?1,' ',''))) ;";

 static const char *sql_delete_mistrusted_key = 

     "delete from mistrusted_keys where fpr = upper(replace(?1,' ','')) ;";

 static const char *sql_is_mistrusted_key = 

     "select count(*) from mistrusted_keys where fpr = upper(replace(?1,' ','')) ;";

 static const char *sql_add_userid_alias =

     "insert or replace into alternate_user_id (alternate_id, default_id) "

     "values (?2, ?1) ;";

 static int user_version(void *_version, int count, char **text, char **name)

 {

     assert(_version);

     assert(count == 1);

     assert(text && text[0]);

     if (!(_version && count == 1 && text && text[0]))

         return -1;

     int *version = (int *) _version;

     *version = atoi(text[0]);

     return 0;

 }

 static int table_contains_column(PEP_SESSION session, const char* table_name,

                                                       const char* col_name) {

     if (!session || !table_name || !col_name)

         return -1;

     // Table names can't be SQL parameters, so we do it this way.

     // these two must be the same number.

     char sql_buf[500];

     const size_t max_q_len = 500;

     size_t t_size, c_size, q_size;

     const char* q1 = "SELECT "; // 7

     const char* q2 = " from "; // 6

     const char* q3 = ";";       // 1

     q_size = 14;

     t_size = strlen(table_name);

     c_size = strlen(col_name);

     size_t query_len = q_size + c_size + t_size + 1;

     if (query_len > max_q_len)

         return -1;

     strlcpy(sql_buf, q1, max_q_len);

     strlcat(sql_buf, col_name, max_q_len);

     strlcat(sql_buf, q2, max_q_len);

     strlcat(sql_buf, table_name, max_q_len);

     strlcat(sql_buf, q3, max_q_len);

     sqlite3_stmt *stmt; 

     sqlite3_prepare_v2(session->db, sql_buf, -1, &stmt, NULL);

     int retval = 0;

     int rc = sqlite3_step(stmt);  

     if (rc == SQLITE_DONE || rc == SQLITE_OK || rc == SQLITE_ROW) {

         retval = 1;

     }

     sqlite3_finalize(stmt);      

     return retval;

 }

 void errorLogCallback(void *pArg, int iErrCode, const char *zMsg){

   fprintf(stderr, "(%d) %s\n", iErrCode, zMsg);

 }

 #ifdef USE_GPG

 PEP_STATUS pgp_import_ultimately_trusted_keypairs(PEP_SESSION session);

 #endif // USE_GPG

 DYNAMIC_API PEP_STATUS init(PEP_SESSION *session)

 {

     PEP_STATUS status = PEP_STATUS_OK;

     int int_result;

     bool in_first = false;

     bool very_first = false;

     assert(sqlite3_threadsafe());

     if (!sqlite3_threadsafe())

         return PEP_INIT_SQLITE3_WITHOUT_MUTEX;

     // a little race condition - but still a race condition

     // mitigated by calling caveat (see documentation)

     // this increment is made atomic IN THE ADAPTERS by

     // guarding the call to init with the appropriate mutex.

     int _count = ++init_count;

     if (_count == 0)

         in_first = true;

     // Race condition mitigated by calling caveat starts here :

     // If another call to init() preempts right now, then preemptive call

     // will have in_first false, will not create SQL tables, and following

     // calls relying on those tables will fail.

     //

     // Therefore, as above, adapters MUST guard init() with a mutex.

     // 

     // Therefore, first session

     // is to be created and last session to be deleted alone, and not

     // concurently to other sessions creation or deletion.

     // We expect adapters to enforce this either by implicitely creating a

     // client session, or by using synchronization primitive to protect

     // creation/deletion of first/last session from the app.

     assert(session);

     if (session == NULL)

         return PEP_ILLEGAL_VALUE;

     *session = NULL;

     pEpSession *_session = calloc(1, sizeof(pEpSession));

     assert(_session);

     if (_session == NULL)

         goto enomem;

     _session->version = PEP_ENGINE_VERSION;

 #ifdef DEBUG_ERRORSTACK

     _session->errorstack = new_stringlist("init()");

 #endif

     assert(LOCAL_DB);

     if (LOCAL_DB == NULL) {

         status = PEP_INIT_CANNOT_OPEN_DB;

         goto pep_error;

     }

 #if _PEP_SQLITE_DEBUG    

     sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, NULL);

 #endif

     int_result = sqlite3_open_v2(

             LOCAL_DB,

             &_session->db,

             SQLITE_OPEN_READWRITE

                 | SQLITE_OPEN_CREATE

                 | SQLITE_OPEN_FULLMUTEX

                 | SQLITE_OPEN_PRIVATECACHE,

             NULL 

         );

     if (int_result != SQLITE_OK) {

         status = PEP_INIT_CANNOT_OPEN_DB;

         goto pep_error;

     }

     int_result = sqlite3_exec(

             _session->db,

             "PRAGMA locking_mode=NORMAL;\n"

             "PRAGMA journal_mode=WAL;\n",

             NULL,

             NULL,

             NULL

         );

     sqlite3_busy_timeout(_session->db, BUSY_WAIT_TIME);

 #if _PEP_SQLITE_DEBUG

     sqlite3_trace_v2(_session->db, 

         SQLITE_TRACE_STMT | SQLITE_TRACE_ROW | SQLITE_TRACE_CLOSE,

         sql_trace_callback,

         NULL);

 #endif

     assert(SYSTEM_DB);

     if (SYSTEM_DB == NULL) {

         status = PEP_INIT_CANNOT_OPEN_SYSTEM_DB;

         goto pep_error;

     }

     int_result = sqlite3_open_v2(

             SYSTEM_DB, &_session->system_db,

             SQLITE_OPEN_READONLY

                 | SQLITE_OPEN_FULLMUTEX

                 | SQLITE_OPEN_SHAREDCACHE,

             NULL

         );

     if (int_result != SQLITE_OK) {

         status = PEP_INIT_CANNOT_OPEN_SYSTEM_DB;

         goto pep_error;

     }

     sqlite3_busy_timeout(_session->system_db, 1000);

 // increment this when patching DDL

 #define _DDL_USER_VERSION "8"

     if (in_first) {

         int_result = sqlite3_exec(

             _session->db,

                 "create table if not exists version_info (\n"

                 "   id integer primary key,\n"

                 "   timestamp integer default (datetime('now')),\n"

                 "   version text,\n"

                 "   comment text\n"

                 ");\n",

                 NULL,

                 NULL,

                 NULL

         );

         int_result = sqlite3_exec(

             _session->db,

                 "PRAGMA application_id = 0x23423423;\n"

                 "create table if not exists log (\n"

                 "   timestamp integer default (datetime('now')),\n"

                 "   title text not null,\n"

                 "   description text,\n"

                 "   entity text not null,\n"

                 "   comment text\n"

                 ");\n"

                 "create index if not exists log_timestamp on log (\n"

                 "   timestamp\n"

                 ");\n"

                 "create table if not exists pgp_keypair (\n"

                 "   fpr text primary key,\n"

                 "   created integer,\n"

                 "   expires integer,\n"

                 "   comment text,\n"

                 "   flags integer default 0\n"

                 ");\n"

                 "create index if not exists pgp_keypair_expires on pgp_keypair (\n"

                 "   expires\n"

                 ");\n"

                 "create table if not exists person (\n"

                 "   id text primary key,\n"

                 "   username text not null,\n"

                 "   main_key_id text\n"

                 "       references pgp_keypair (fpr)\n"

                 "       on delete set null,\n"

                 "   lang text,\n"

                 "   comment text,\n"

                 "   device_group text,\n"

                 "   is_pep_user integer default 0\n"

                 ");\n"

                 "create table if not exists identity (\n"

                 "   address text,\n"

                 "   user_id text\n"

                 "       references person (id)\n"

                 "       on delete cascade on update cascade,\n"

                 "   main_key_id text\n"

                 "       references pgp_keypair (fpr)\n"

                 "       on delete set null,\n"

                 "   comment text,\n"

                 "   flags integer default 0,\n"

                 "   is_own integer default 0,\n"

                 "   timestamp integer default (datetime('now')),\n"

                 "   primary key (address, user_id)\n"

                 ");\n"

                 "create table if not exists trust (\n"

                 "   user_id text not null\n"

                 "       references person (id)\n"

                 "       on delete cascade on update cascade,\n"

                 "   pgp_keypair_fpr text not null\n"

                 "       references pgp_keypair (fpr)\n"

                 "       on delete cascade,\n"

                 "   comm_type integer not null,\n"

                 "   comment text,\n"

                 "   primary key (user_id, pgp_keypair_fpr)\n"

                 ");\n"

                 // blacklist

                 "create table if not exists blacklist_keys (\n"

                 "   fpr text primary key\n"

                 ");\n"

                 // sequences

                 "create table if not exists sequences(\n"

                 "   name text primary key,\n"

                 "   value integer default 0,\n"

                 "   own integer default 0\n"

                 ");\n"

                 "create table if not exists revoked_keys (\n"

                 "   revoked_fpr text primary key,\n"

                 "   replacement_fpr text not null\n"

                 "       references pgp_keypair (fpr)\n"

                 "       on delete cascade,\n"

                 "   revocation_date integer\n"

                 ");\n"

                 // user id aliases

                 "create table if not exists alternate_user_id (\n"

                 "    default_id text references person (id)\n"

                 "       on delete cascade on update cascade,\n"

                 "    alternate_id text primary key\n"

                 ");\n"

                 // mistrusted keys

                 "create table if not exists mistrusted_keys (\n"

                 "    fpr text primary key\n"

                 ");\n"

                 ,

             NULL,

             NULL,

             NULL

         );

         assert(int_result == SQLITE_OK);

         int version;

         int_result = sqlite3_exec(

             _session->db,

             "pragma user_version;",

             user_version,

             &version,

             NULL

         );

         assert(int_result == SQLITE_OK);

         void (*xFunc_lower)(sqlite3_context*,int,sqlite3_value**) = &_sql_lower;

         int_result = sqlite3_create_function_v2(

             _session->db,

             "lower",

             1,

             SQLITE_UTF8 | SQLITE_DETERMINISTIC,

             NULL,

             xFunc_lower,

             NULL,

             NULL,

             NULL);

         assert(int_result == SQLITE_OK);

         int_result = sqlite3_exec(

             _session->db,

             "pragma foreign_keys=ON;\n",

             NULL,

             NULL,

             NULL

         );

         assert(int_result == SQLITE_OK);

         // Sometimes the user_version wasn't set correctly. Check to see if this

         // is really necessary...

         if (version == 1) {

             bool version_changed = true;

             if (table_contains_column(_session, "identity", "timestamp") > 0) {

                 version = 8;

             }            

             if (table_contains_column(_session, "person", "is_pep_user") > 0) {

                 version = 7;

             }            

             else if (table_contains_column(_session, "identity", "is_own") > 0) {

                 version = 6;

             }

             else if (table_contains_column(_session, "sequences", "own") > 0) {

                 version = 3;

             }

             else if (table_contains_column(_session, "pgp_keypair", "flags") > 0) {

                 version = 2;

             }

             else {

                 version_changed = false;

             }

             if (version_changed) {

                 // set it in the DB, finally. Yeesh.

                 char verbuf[21]; // enough digits for a max-sized 64 bit int, cmon. 

                 sprintf(verbuf,"%d",version);

                 size_t query_size = strlen(verbuf) + 25;

                 char* query = calloc(query_size, 1);

                 strlcpy(query, "pragma user_version = ", query_size);

                 strlcat(query, verbuf, query_size);

                 strlcat(query, ";", query_size);

                 int_result = sqlite3_exec(

                     _session->db,

                     query,

                     user_version,

                     &version,

                     NULL

                 );

                 free(query);

             }

         }

         if(version != 0) { 

             // Version has been already set

             // Early mistake : version 0 shouldn't have existed.

             // Numbering should have started at 1 to detect newly created DB.

             // Version 0 DBs are not anymore compatible.

             // // Was version 0 compat code.

             // if (version < 1) {

             //     int_result = sqlite3_exec(

             //         _session->db,

             //         "alter table identity\n"

             //         "   add column flags integer default 0;\n",

             //         NULL,

             //         NULL,

             //         NULL

             //     );

             //     assert(int_result == SQLITE_OK);

             // }

             if (version < 2) {

                 int_result = sqlite3_exec(

                     _session->db,

                     "alter table pgp_keypair\n"

                     "   add column flags integer default 0;\n"

                     "alter table person\n"

                     "   add column device_group text;\n",

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);

             }

             if (version < 3) {

                 int_result = sqlite3_exec(

                     _session->db,

                     "alter table sequences\n"

                     "   add column own integer default 0;\n",

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);

             }

             if (version < 5) {

                 int_result = sqlite3_exec(

                     _session->db,

                     "delete from pgp_keypair where fpr = '';",

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);

                 int_result = sqlite3_exec(

                     _session->db,

                     "delete from trust where pgp_keypair_fpr = '';",

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);

             }

             if (version < 6) {

                 int_result = sqlite3_exec(

                     _session->db,

                     "alter table identity\n"

                     "   add column is_own integer default 0;\n",

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);                

                 int_result = sqlite3_exec(

                     _session->db,

                     "update identity\n"

                     "   set is_own = 1\n"

                     "   where (user_id = '" PEP_OWN_USERID "');\n",

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);    

                 // Turns out that just adding "on update cascade" in

                 // sqlite is a PITA. We need to be able to cascade

                 // person->id replacements (for temp ids like "TOFU_")

                 // so here we go...

                 int_result = sqlite3_exec(

                     _session->db,

                     "PRAGMA foreign_keys=off;\n"

                     "BEGIN TRANSACTION;\n"

                     "ALTER TABLE identity RENAME TO _identity_old;\n"

                     "create table identity (\n"

                     "   address text,\n"

                     "   user_id text\n"

                     "       references person (id)\n"

                     "       on delete cascade on update cascade,\n"

                     "   main_key_id text\n"

                     "       references pgp_keypair (fpr)\n"

                     "       on delete set null,\n"

                     "   comment text,\n"

                     "   flags integer default 0,\n"

                     "   is_own integer default 0,\n"

                     "   primary key (address, user_id)\n"

                     ");\n"

                     "INSERT INTO identity SELECT * FROM _identity_old;\n"

                     "DROP TABLE _identity_old;\n"

                     "ALTER TABLE trust RENAME TO _trust_old;\n"

                     "create table trust (\n"

                     "   user_id text not null\n"

                     "       references person (id)\n"

                     "       on delete cascade on update cascade,\n"

                     "   pgp_keypair_fpr text not null\n"

                     "       references pgp_keypair (fpr)\n"

                     "       on delete cascade,\n"

                     "   comm_type integer not null,\n"

                     "   comment text,\n"

                     "   primary key (user_id, pgp_keypair_fpr)\n"

                     ");\n"

                     "INSERT INTO trust SELECT * FROM _trust_old;\n"

                     "DROP TABLE _trust_old;\n"

                     "COMMIT;\n"

                     "\n"

                     "PRAGMA foreign_keys=on;\n"

                     "create table if not exists alternate_user_id (\n"

                     "    default_id text references person (id)\n"

                     "       on delete cascade on update cascade,\n"

                     "    alternate_id text primary key\n"

                     ");\n"

                     ,

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);    

             }

             if (version < 7) {

                 int_result = sqlite3_exec(

                     _session->db,

                     "alter table person\n"

                     "   add column is_pep_user integer default 0;\n",

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);

                 int_result = sqlite3_exec(

                     _session->db,

                     "update person\n"

                     "   set is_pep_user = 1\n"

                     "   where id = "

                     "       (select distinct id from person "

                     "               join trust on id = user_id "

                     "               where (case when (comm_type = 127) then (id) "

                     "                           when (comm_type = 255) then (id) "

                     "                           else 0"

                     "                      end) = id );\n",

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);

                 int_result = sqlite3_exec(

                     _session->db,

                     "create table if not exists mistrusted_keys (\n"

                     "    fpr text primary key\n"

                     ");\n",            

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);    

             }

             if (version < 8) {

                 int_result = sqlite3_exec(

                     _session->db,

                     "PRAGMA foreign_keys=off;\n"

                     "BEGIN TRANSACTION;\n"

                     "ALTER TABLE identity RENAME TO _identity_old;\n"

                     "create table identity (\n"

                     "   address text,\n"

                     "   user_id text\n"

                     "       references person (id)\n"

                     "       on delete cascade on update cascade,\n"

                     "   main_key_id text\n"

                     "       references pgp_keypair (fpr)\n"

                     "       on delete set null,\n"

                     "   comment text,\n"

                     "   flags integer default 0,\n"

                     "   is_own integer default 0,\n"

                     "   timestamp integer default (datetime('now')),\n"

                     "   primary key (address, user_id)\n"

                     ");\n"

                     "INSERT INTO identity (address, user_id, main_key_id, "

                     "                      comment, flags, is_own) "

                     "   SELECT _identity_old.address, _identity_old.user_id, "

                     "          _identity_old.main_key_id, _identity_old.comment, "

                     "          _identity_old.flags, _identity_old.is_own "

                     "   FROM _identity_old "

                     "   WHERE 1;\n"

                     "DROP TABLE _identity_old;\n"

                     "COMMIT;\n"

                     "\n"

                     "PRAGMA foreign_keys=on;\n"

                     ,

                     NULL,

                     NULL,

                     NULL

                 );

                 assert(int_result == SQLITE_OK);    

             }

         }        

         else { 

             // Version from DB was 0, it means this is initial setup.

             // DB has just been created, and all tables are empty.

             very_first = true;

         }

         if (version < atoi(_DDL_USER_VERSION)) {

             int_result = sqlite3_exec(

                 _session->db,

                 "pragma user_version = "_DDL_USER_VERSION";\n"

                 "insert or replace into version_info (id, version)"

                     "values (1, '" PEP_ENGINE_VERSION "');",

                 NULL,

                 NULL,

                 NULL

             );

             assert(int_result == SQLITE_OK);

         }

         // We need to init a few globals for message id that we'd rather not

         // calculate more than once.

         _init_globals();

     }

     int_result = sqlite3_prepare_v2(_session->db, sql_log,

             (int)strlen(sql_log), &_session->log, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->system_db, sql_trustword,

             (int)strlen(sql_trustword), &_session->trustword, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_identity,

             (int)strlen(sql_get_identity), &_session->get_identity, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_identity_without_trust_check,

             (int)strlen(sql_get_identity_without_trust_check), 

             &_session->get_identity_without_trust_check, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_identities_by_address,

             (int)strlen(sql_get_identities_by_address), 

             &_session->get_identities_by_address, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_user_default_key,

             (int)strlen(sql_get_user_default_key), &_session->get_user_default_key, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_default_own_userid,

             (int)strlen(sql_get_default_own_userid), &_session->get_default_own_userid, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_userid_alias_default,

             (int)strlen(sql_get_userid_alias_default), &_session->get_userid_alias_default, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_add_userid_alias,

             (int)strlen(sql_add_userid_alias), &_session->add_userid_alias, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_replace_userid,

             (int)strlen(sql_replace_userid), &_session->replace_userid, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_replace_main_user_fpr,

             (int)strlen(sql_replace_main_user_fpr), &_session->replace_main_user_fpr, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_main_user_fpr,

             (int)strlen(sql_get_main_user_fpr), &_session->get_main_user_fpr, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_refresh_userid_default_key,

             (int)strlen(sql_refresh_userid_default_key), &_session->refresh_userid_default_key, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_replace_identities_fpr,

             (int)strlen(sql_replace_identities_fpr), 

             &_session->replace_identities_fpr, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_remove_fpr_as_default,

             (int)strlen(sql_remove_fpr_as_default), 

             &_session->remove_fpr_as_default, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_set_person,

             (int)strlen(sql_set_person), &_session->set_person, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_update_person,

             (int)strlen(sql_update_person), &_session->update_person, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_exists_person,

             (int)strlen(sql_exists_person), &_session->exists_person, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_set_as_pep_user,

             (int)strlen(sql_set_as_pep_user), &_session->set_as_pep_user, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_is_pep_user,

             (int)strlen(sql_is_pep_user), &_session->is_pep_user, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_set_device_group,

             (int)strlen(sql_set_device_group), &_session->set_device_group, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_device_group,

             (int)strlen(sql_get_device_group), &_session->get_device_group, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_set_pgp_keypair,

             (int)strlen(sql_set_pgp_keypair), &_session->set_pgp_keypair,

             NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_set_identity_entry,

             (int)strlen(sql_set_identity_entry), &_session->set_identity_entry, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_update_identity_entry,

             (int)strlen(sql_update_identity_entry), &_session->update_identity_entry, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_exists_identity_entry,

             (int)strlen(sql_exists_identity_entry), &_session->exists_identity_entry, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_set_identity_flags,

             (int)strlen(sql_set_identity_flags), &_session->set_identity_flags,

             NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_unset_identity_flags,

             (int)strlen(sql_unset_identity_flags), &_session->unset_identity_flags,

             NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_set_trust,

             (int)strlen(sql_set_trust), &_session->set_trust, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_update_trust,

             (int)strlen(sql_update_trust), &_session->update_trust, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_update_trust_to_pep,

             (int)strlen(sql_update_trust_to_pep), &_session->update_trust_to_pep, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_exists_trust_entry,

                  (int)strlen(sql_exists_trust_entry), &_session->exists_trust_entry, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_update_trust_for_fpr,

             (int)strlen(sql_update_trust_for_fpr), &_session->update_trust_for_fpr, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_trust,

             (int)strlen(sql_get_trust), &_session->get_trust, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_least_trust,

             (int)strlen(sql_least_trust), &_session->least_trust, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_mark_as_compromised,

             (int)strlen(sql_mark_as_compromised), &_session->mark_compromised,

             NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_crashdump,

             (int)strlen(sql_crashdump), &_session->crashdump, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->system_db, sql_languagelist,

             (int)strlen(sql_languagelist), &_session->languagelist, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->system_db, sql_i18n_token,

             (int)strlen(sql_i18n_token), &_session->i18n_token, NULL);

     assert(int_result == SQLITE_OK);

     // blacklist

     int_result = sqlite3_prepare_v2(_session->db, sql_blacklist_add,

             (int)strlen(sql_blacklist_add), &_session->blacklist_add, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_blacklist_delete,

             (int)strlen(sql_blacklist_delete), &_session->blacklist_delete,

             NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_blacklist_is_listed,

             (int)strlen(sql_blacklist_is_listed),

             &_session->blacklist_is_listed, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_blacklist_retrieve,

             (int)strlen(sql_blacklist_retrieve), &_session->blacklist_retrieve,

             NULL);

     assert(int_result == SQLITE_OK);

     // Own keys

     int_result = sqlite3_prepare_v2(_session->db, sql_own_key_is_listed,

             (int)strlen(sql_own_key_is_listed), &_session->own_key_is_listed,

             NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_own_identities_retrieve,

             (int)strlen(sql_own_identities_retrieve),

             &_session->own_identities_retrieve, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_own_keys_retrieve,

             (int)strlen(sql_own_keys_retrieve),

             &_session->own_keys_retrieve, NULL);

     assert(int_result == SQLITE_OK);

     // int_result = sqlite3_prepare_v2(_session->db, sql_set_own_key,

     //         (int)strlen(sql_set_own_key),

     //         &_session->set_own_key, NULL);

     // assert(int_result == SQLITE_OK);

     // Sequence

     int_result = sqlite3_prepare_v2(_session->db, sql_sequence_value1,

             (int)strlen(sql_sequence_value1), &_session->sequence_value1,

             NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_sequence_value2,

             (int)strlen(sql_sequence_value2), &_session->sequence_value2,

             NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_sequence_value3,

             (int)strlen(sql_sequence_value3), &_session->sequence_value3,

             NULL);

     assert(int_result == SQLITE_OK);

     // Revocation tracking

     int_result = sqlite3_prepare_v2(_session->db, sql_set_revoked,

             (int)strlen(sql_set_revoked), &_session->set_revoked, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_get_revoked,

             (int)strlen(sql_get_revoked), &_session->get_revoked, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_add_mistrusted_key,

             (int)strlen(sql_add_mistrusted_key), &_session->add_mistrusted_key, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_delete_mistrusted_key,

             (int)strlen(sql_delete_mistrusted_key), &_session->delete_mistrusted_key, NULL);

     assert(int_result == SQLITE_OK);

     int_result = sqlite3_prepare_v2(_session->db, sql_is_mistrusted_key,

             (int)strlen(sql_is_mistrusted_key), &_session->is_mistrusted_key, NULL);

     assert(int_result == SQLITE_OK);

     status = init_cryptotech(_session, in_first);

     if (status != PEP_STATUS_OK)

         goto pep_error;

     status = init_transport_system(_session, in_first);

     if (status != PEP_STATUS_OK)

         goto pep_error;

     status = log_event(_session, "init", "pEp " PEP_ENGINE_VERSION, NULL, NULL);

     if (status != PEP_STATUS_OK)

         goto pep_error;

     // runtime config

     if (very_first)

     {

 #ifdef USE_GPG

         // On first run, all private keys already present in PGP keyring 

         // are taken as own in order to seamlessly integrate with

         // pre-existing GPG setup.

         // Note: earlier fears about danger because of DB reinitialisation should

         // be a non-issue here, as we ONLY take the ultimately trusted keys now.

         // Thus, unless the user has assigned ultimate trust through PGP, there is

         // no chance of automatically imported pEp keys from a previous run making

         // their way into PEP trusted status without explicit action (Bare imported

         // private keys have an 'unknown' trust designation in PGP).

         // We don't really worry about the status here.

         status = pgp_import_ultimately_trusted_keypairs(_session);        

 #endif // USE_GPG

     }

     // sync_session set to own session by default

     // sync_session is then never null on a valid session

     _session->sync_session = _session;

     *session = _session;

     // Note: Following statement is NOT for any cryptographic/secure functionality; it is

     //       ONLY used for some randomness in generated outer message ID, which are

     //       required by the RFC to be globally unique!

     srand((unsigned int) time(NULL));

     return PEP_STATUS_OK;

 enomem:

     status = PEP_OUT_OF_MEMORY;

 pep_error:

     release(_session);

     return status;

 }

 DYNAMIC_API void release(PEP_SESSION session)

 {

     bool out_last = false;

     int _count = --init_count;

     assert(_count >= -1);

     assert(session);

     if (!((_count >= -1) && session))

         return;

     // a small race condition but still a race condition

     // mitigated by calling caveat (see documentation)

     // (release() is to be guarded by a mutex by the caller)

     if (_count == -1)

         out_last = true;

     if (session) {

         if (session->db) {

             if (session->log)

                 sqlite3_finalize(session->log);

             if (session->trustword)

                 sqlite3_finalize(session->trustword);

             if (session->get_identity)

                 sqlite3_finalize(session->get_identity);

             if (session->get_identity_without_trust_check)

                 sqlite3_finalize(session->get_identity_without_trust_check);

             if (session->get_identities_by_address)

                 sqlite3_finalize(session->get_identities_by_address);            

             if (session->get_user_default_key)

                 sqlite3_finalize(session->get_user_default_key);    

             if (session->get_default_own_userid)

                 sqlite3_finalize(session->get_default_own_userid);

             if (session->get_userid_alias_default)

                 sqlite3_finalize(session->get_userid_alias_default);

             if (session->add_userid_alias)

                 sqlite3_finalize(session->add_userid_alias);

             if (session->replace_identities_fpr)

                 sqlite3_finalize(session->replace_identities_fpr);        

             if (session->remove_fpr_as_default)

                 sqlite3_finalize(session->remove_fpr_as_default);            

             if (session->set_person)

                 sqlite3_finalize(session->set_person);

             if (session->set_as_pep_user)

                 sqlite3_finalize(session->set_as_pep_user);

             if (session->is_pep_user)

                 sqlite3_finalize(session->is_pep_user);

             if (session->exists_person)

                 sqlite3_finalize(session->exists_person);                        

             if (session->set_device_group)

                 sqlite3_finalize(session->set_device_group);

             if (session->get_device_group)

                 sqlite3_finalize(session->get_device_group);

             if (session->set_pgp_keypair)

                 sqlite3_finalize(session->set_pgp_keypair);

             if (session->exists_identity_entry)

                 sqlite3_finalize(session->exists_identity_entry);                

             if (session->set_identity_entry)

                 sqlite3_finalize(session->set_identity_entry);

             if (session->update_identity_entry)

                 sqlite3_finalize(session->update_identity_entry);    

             if (session->set_identity_flags)

                 sqlite3_finalize(session->set_identity_flags);

             if (session->unset_identity_flags)

                 sqlite3_finalize(session->unset_identity_flags);

             if (session->exists_trust_entry)

                 sqlite3_finalize(session->exists_trust_entry);                                

             if (session->set_trust)

                 sqlite3_finalize(session->set_trust);

             if (session->update_trust)

                 sqlite3_finalize(session->update_trust);

             if (session->update_trust_to_pep)

                 sqlite3_finalize(session->update_trust_to_pep);                                                

             if (session->update_trust_for_fpr)

                 sqlite3_finalize(session->update_trust_for_fpr);

             if (session->get_trust)

                 sqlite3_finalize(session->get_trust);

             if (session->least_trust)

                 sqlite3_finalize(session->least_trust);

             if (session->mark_compromised)

                 sqlite3_finalize(session->mark_compromised);

             if (session->crashdump)

                 sqlite3_finalize(session->crashdump);

             if (session->languagelist)

                 sqlite3_finalize(session->languagelist);

             if (session->i18n_token)

                 sqlite3_finalize(session->i18n_token);

             if (session->replace_userid)

                 sqlite3_finalize(session->replace_userid);

             if (session->replace_main_user_fpr)

                 sqlite3_finalize(session->replace_main_user_fpr);                

             if (session->get_main_user_fpr)

                 sqlite3_finalize(session->get_main_user_fpr);

             if (session->refresh_userid_default_key)

                 sqlite3_finalize(session->refresh_userid_default_key);

             if (session->blacklist_add)

                 sqlite3_finalize(session->blacklist_add);

             if (session->blacklist_delete)

                 sqlite3_finalize(session->blacklist_delete);

             if (session->blacklist_is_listed)

                 sqlite3_finalize(session->blacklist_is_listed);

             if (session->blacklist_retrieve)

                 sqlite3_finalize(session->blacklist_retrieve);

             if (session->own_key_is_listed)

                 sqlite3_finalize(session->own_key_is_listed);

             if (session->own_identities_retrieve)

                 sqlite3_finalize(session->own_identities_retrieve);

             if (session->own_keys_retrieve)

                 sqlite3_finalize(session->own_keys_retrieve);

             // if (session->set_own_key)

             //     sqlite3_finalize(session->set_own_key);

             if (session->sequence_value1)

                 sqlite3_finalize(session->sequence_value1);

             if (session->sequence_value2)

                 sqlite3_finalize(session->sequence_value2);

             if (session->sequence_value3)

                 sqlite3_finalize(session->sequence_value3);

             if (session->set_revoked)

                 sqlite3_finalize(session->set_revoked);

             if (session->get_revoked)

                 sqlite3_finalize(session->get_revoked);

             if (session->add_mistrusted_key)

                 sqlite3_finalize(session->add_mistrusted_key);

             if (session->delete_mistrusted_key)

                 sqlite3_finalize(session->delete_mistrusted_key);

             if (session->is_mistrusted_key)

                 sqlite3_finalize(session->is_mistrusted_key);

             if (session->db) {

                 sqlite3_exec(        

                     session->db,

                     "PRAGMA optimize;\n",

                     NULL,

                     NULL,

                     NULL

                 );

                 sqlite3_close_v2(session->db);

             }

             if (session->system_db)

                 sqlite3_close_v2(session->system_db);

         }

         release_transport_system(session, out_last);

         release_cryptotech(session, out_last);

 #ifdef DEBUG_ERRORSTACK

         free_stringlist(session->errorstack);

 #endif

         free(session);

     }

 }

 DYNAMIC_API void config_passive_mode(PEP_SESSION session, bool enable)

 {

     assert(session);

     session->passive_mode = enable;

 }

 DYNAMIC_API void config_unencrypted_subject(PEP_SESSION session, bool enable)

 {

     assert(session);

     session->unencrypted_subject = enable;

 }

 DYNAMIC_API void config_keep_sync_msg(PEP_SESSION session, bool enable)

 {

     assert(session);

     session->keep_sync_msg = enable;

 }

 DYNAMIC_API void config_service_log(PEP_SESSION session, bool enable)

 {

     assert(session);

     session->service_log = enable;

 }

 DYNAMIC_API PEP_STATUS log_event(

         PEP_SESSION session,

         const char *title,

         const char *entity,

         const char *description,

         const char *comment

     )

 {

 //    PEP_STATUS status = PEP_STATUS_OK;

     // int result;

     // 

     // assert(session);

     // assert(title);

     // assert(entity);

     // 

     // if (!(session && title && entity))

     //     return PEP_ILLEGAL_VALUE;

     // 

     // sqlite3_reset(session->log);

     // sqlite3_bind_text(session->log, 1, title, -1, SQLITE_STATIC);

     // sqlite3_bind_text(session->log, 2, entity, -1, SQLITE_STATIC);

     // if (description)

     //     sqlite3_bind_text(session->log, 3, description, -1, SQLITE_STATIC);

     // else

     //     sqlite3_bind_null(session->log, 3);

     // if (comment)

     //     sqlite3_bind_text(session->log, 4, comment, -1, SQLITE_STATIC);

     // else

     //     sqlite3_bind_null(session->log, 4);

     // result = sqlite3_step(session->log);

     // sqlite3_reset(session->log);

     // 

     return PEP_STATUS_OK; // We ignore errors for this function.

 }

 DYNAMIC_API PEP_STATUS log_service(

         PEP_SESSION session,

         const char *title,

         const char *entity,

         const char *description,

         const char *comment

     )

 {

     assert(session);

     if (!session)

         return PEP_ILLEGAL_VALUE;

     if (session->service_log)

         return log_event(session, title, entity, description, comment);

     else

         return PEP_STATUS_OK;

 }

 DYNAMIC_API PEP_STATUS trustword(

             PEP_SESSION session, uint16_t value, const char *lang,

             char **word, size_t *wsize

         )

 {

     PEP_STATUS status = PEP_STATUS_OK;

     assert(session);

     assert(word);

     assert(wsize);

     if (!(session && word && wsize))

         return PEP_ILLEGAL_VALUE;

     *word = NULL;

     *wsize = 0;

     if (lang == NULL)

         lang = "en";

     assert((lang[0] >= 'A' && lang[0] <= 'Z')

             || (lang[0] >= 'a' && lang[0] <= 'z'));

     assert((lang[1] >= 'A' && lang[1] <= 'Z')

             || (lang[1] >= 'a' && lang[1] <= 'z'));

     assert(lang[2] == 0);

     sqlite3_reset(session->trustword);

     sqlite3_bind_text(session->trustword, 1, lang, -1, SQLITE_STATIC);

     sqlite3_bind_int(session->trustword, 2, value);

     const int result = sqlite3_step(session->trustword);

     if (result == SQLITE_ROW) {

         *word = strdup((const char *) sqlite3_column_text(session->trustword,

                     1));

         if (*word)

             *wsize = sqlite3_column_bytes(session->trustword, 1);

         else

             status = PEP_OUT_OF_MEMORY;

     } else

         status = PEP_TRUSTWORD_NOT_FOUND;

     sqlite3_reset(session->trustword);

     return status;

 }

 DYNAMIC_API PEP_STATUS trustwords(

         PEP_SESSION session, const char *fingerprint, const char *lang,

         char **words, size_t *wsize, int max_words

     )

 {

     const char *source = fingerprint;

     assert(session);

     assert(fingerprint);

     assert(words);

     assert(wsize);

     assert(max_words >= 0);

     if (!(session && fingerprint && words && wsize && max_words >= 0))

         return PEP_ILLEGAL_VALUE;

     *words = NULL;

     *wsize = 0;

     char *buffer = calloc(1, MAX_TRUSTWORDS_SPACE);

     assert(buffer);

     if (buffer == NULL)

         return PEP_OUT_OF_MEMORY;

     char *dest = buffer;

     const size_t fsize = strlen(fingerprint);

     if (!lang || !lang[0])

         lang = "en";

     assert((lang[0] >= 'A' && lang[0] <= 'Z')

             || (lang[0] >= 'a' && lang[0] <= 'z'));

     assert((lang[1] >= 'A' && lang[1] <= 'Z')

             || (lang[1] >= 'a' && lang[1] <= 'z'));

     assert(lang[2] == 0);

     int n_words = 0;

     while (source < fingerprint + fsize) {

         PEP_STATUS _status;

         uint16_t value;

         char *word = NULL;

         size_t _wsize = 0;

         int j;

         for (value=0, j=0; j < 4 && source < fingerprint + fsize; ) {

             if (*source >= 'a' && *source <= 'f')

                 value += (*source - 'a' + 10) << (3 - j++) * 4;

             else if (*source >= 'A' && *source <= 'F')

                 value += (*source - 'A' + 10) << (3 - j++) * 4;

             else if (*source >= '0' && *source <= '9')

                 value += (*source - '0') << (3 - j++) * 4;

             source++;

         }

         _status = trustword(session, value, lang, &word, &_wsize);

         if (_status == PEP_OUT_OF_MEMORY) {

             free(buffer);

             return PEP_OUT_OF_MEMORY;

         }

         if (word == NULL) {

             free(buffer);

             return PEP_TRUSTWORD_NOT_FOUND;

         }

         if (dest + _wsize < buffer + MAX_TRUSTWORDS_SPACE - 1) {

             strncpy(dest, word, _wsize);

             free(word);

             dest += _wsize;

         }

         else {

             free(word);

             break; // buffer full

         }

         if (source < fingerprint + fsize

                 && dest + _wsize < buffer + MAX_TRUSTWORDS_SPACE - 1)

             *dest++ = ' ';

         ++n_words;

         if (max_words && n_words >= max_words)

             break;

     }

     *words = buffer;

     *wsize = dest - buffer;

     return PEP_STATUS_OK;

 }

 pEp_identity *new_identity(

         const char *address, const char *fpr, const char *user_id,

         const char *username

     )

 {

     pEp_identity *result = calloc(1, sizeof(pEp_identity));

     assert(result);

     if (result) {

         if (address) {

             result->address = strdup(address);

             assert(result->address);

             if (result->address == NULL) {

                 free(result);

                 return NULL;

             }

         }

         if (fpr) {

             result->fpr = strdup(fpr);

             assert(result->fpr);

             if (result->fpr == NULL) {

                 free_identity(result);

                 return NULL;

             }

         }

         if (user_id) {

             result->user_id = strdup(user_id);

             assert(result->user_id);

             if (result->user_id == NULL) {

                 free_identity(result);

                 return NULL;

             }

         }

         if (username) {

             result->username = strdup(username);

             assert(result->username);

             if (result->username == NULL) {

                 free_identity(result);

                 return NULL;

             }

         }

     }

     return result;

 }

 pEp_identity *identity_dup(const pEp_identity *src)

 {

     assert(src);

     pEp_identity *dup = new_identity(src->address, src->fpr, src->user_id,

             src->username);

     assert(dup);

     if (dup == NULL)

         return NULL;

     dup->comm_type = src->comm_type;

     dup->lang[0] = src->lang[0];

     dup->lang[1] = src->lang[1];

     dup->lang[2] = 0;

     dup->flags = src->flags;

     dup->me = src->me;

     return dup;

 }

 void free_identity(pEp_identity *identity)

 {

     if (identity) {

         free(identity->address);

         free(identity->fpr);

         free(identity->user_id);

         free(identity->username);

         free(identity);

     }

 }

 DYNAMIC_API PEP_STATUS get_default_own_userid(

         PEP_SESSION session, 

         char** userid

     )

 {

     assert(session);

     assert(userid);

     if (!session || !userid)

         return PEP_ILLEGAL_VALUE;

     PEP_STATUS status = PEP_STATUS_OK;

     char* retval = NULL;

     sqlite3_reset(session->get_default_own_userid);

     const int result = sqlite3_step(session->get_default_own_userid);

     const char* id;

     switch (result) {

         case SQLITE_ROW:

             id = (const char *) sqlite3_column_text(session->get_default_own_userid, 0);

             if (!id) {

                 // Shouldn't happen.

                 status = PEP_UNKNOWN_ERROR;

             }

             else {

                 retval = strdup(id);

                 if (!retval)

                     status = PEP_OUT_OF_MEMORY;

             }

             break;

         default:

             // Technically true, given how we find it, but FIXME we need a more descriptive error

             status = PEP_CANNOT_FIND_IDENTITY;

             *userid = NULL;

     }

     *userid = retval;

     sqlite3_reset(session->get_default_own_userid);

     return status;

 }

 DYNAMIC_API PEP_STATUS get_userid_alias_default(

         PEP_SESSION session, 

         const char* alias_id,

         char** default_id) {

     assert(session);

     assert(alias_id);

     assert(alias_id[0]);

     assert(default_id);

     if (!(session && alias_id && alias_id[0] && default_id))

         return PEP_ILLEGAL_VALUE;

     PEP_STATUS status = PEP_STATUS_OK;

     char* retval = NULL;

     sqlite3_reset(session->get_userid_alias_default);

     sqlite3_bind_text(session->get_userid_alias_default, 1, alias_id, -1, SQLITE_STATIC);

     const char* tempid;

     const int result = sqlite3_step(session->get_userid_alias_default);

     switch (result) {

     case SQLITE_ROW:

         tempid = (const char *) sqlite3_column_text(session->get_userid_alias_default, 0);

         if (tempid) {

             retval = strdup(tempid);

             assert(retval);

             if (retval == NULL)

                 return PEP_OUT_OF_MEMORY;

         }

         *default_id = retval;

         break;

     default:

         status = PEP_CANNOT_FIND_ALIAS;

         *default_id = NULL;

     }

     sqlite3_reset(session->get_userid_alias_default);

     return status;            

 }

 DYNAMIC_API PEP_STATUS set_userid_alias (

         PEP_SESSION session, 

         const char* default_id,

         const char* alias_id) {

     int result;

     assert(session);

     assert(default_id);

     assert(alias_id);

     if (!(session && default_id && alias_id && 

           default_id[0] != '\0' && alias_id[0] != '\0'))

         return PEP_ILLEGAL_VALUE;

     sqlite3_exec(session->db, "BEGIN TRANSACTION ;", NULL, NULL, NULL);

     sqlite3_reset(session->add_userid_alias);

     sqlite3_bind_text(session->add_userid_alias, 1, default_id, -1,

             SQLITE_STATIC);

     sqlite3_bind_text(session->add_userid_alias, 2, alias_id, -1,

             SQLITE_STATIC);

     result = sqlite3_step(session->add_userid_alias);

     sqlite3_reset(session->add_userid_alias);

     if (result != SQLITE_DONE) {

         sqlite3_exec(session->db, "ROLLBACK ;", NULL, NULL, NULL);        

         return PEP_CANNOT_SET_ALIAS;

     }

     sqlite3_exec(session->db, "COMMIT ;", NULL, NULL, NULL);

     return PEP_STATUS_OK;

 }

 DYNAMIC_API PEP_STATUS get_identity(

         PEP_SESSION session,

         const char *address,

         const char *user_id,

         pEp_identity **identity

     )

 {

     PEP_STATUS status = PEP_STATUS_OK;

     static pEp_identity *_identity;

     assert(session);

     assert(address);

     assert(address[0]);

     assert(identity);

     if (!(session && address && address[0] && identity))

         return PEP_ILLEGAL_VALUE;

     *identity = NULL;

     sqlite3_reset(session->get_identity);

     sqlite3_bind_text(session->get_identity, 1, address, -1, SQLITE_STATIC);

     sqlite3_bind_text(session->get_identity, 2, user_id, -1, SQLITE_STATIC);

     const int result = sqlite3_step(session->get_identity);

     switch (result) {

     case SQLITE_ROW:

         _identity = new_identity(

                 address,

                 (const char *) sqlite3_column_text(session->get_identity, 0),

                 user_id,

                 (const char *) sqlite3_column_text(session->get_identity, 1)

                 );

         assert(_identity);

         if (_identity == NULL) {

             sqlite3_reset(session->get_identity);

             return PEP_OUT_OF_MEMORY;

         }

         _identity->comm_type = (PEP_comm_type)

             sqlite3_column_int(session->get_identity, 2);

         const char* const _lang = (const char *)

             sqlite3_column_text(session->get_identity, 3);

         if (_lang && _lang[0]) {

             assert(_lang[0] >= 'a' && _lang[0] <= 'z');

             assert(_lang[1] >= 'a' && _lang[1] <= 'z');

             assert(_lang[2] == 0);

             _identity->lang[0] = _lang[0];

             _identity->lang[1] = _lang[1];

             _identity->lang[2] = 0;

         }

         _identity->flags = (unsigned int)

             sqlite3_column_int(session->get_identity, 4);

         _identity->me = (unsigned int)

             sqlite3_column_int(session->get_identity, 5);

         *identity = _identity;

         break;

     default:

         sqlite3_reset(session->get_identity);

         status = PEP_CANNOT_FIND_IDENTITY;

         *identity = NULL;

     }

     sqlite3_reset(session->get_identity);

     return status;

 }

 PEP_STATUS get_identity_without_trust_check(

         PEP_SESSION session,

         const char *address,

         const char *user_id,

         pEp_identity **identity

     )

 {

     PEP_STATUS status = PEP_STATUS_OK;

     static pEp_identity *_identity;

     assert(session);

     assert(address);

     assert(address[0]);

     assert(identity);

     if (!(session && address && address[0] && identity))

         return PEP_ILLEGAL_VALUE;

     *identity = NULL;

     sqlite3_reset(session->get_identity_without_trust_check);

     sqlite3_bind_text(session->get_identity_without_trust_check, 1, address, -1, SQLITE_STATIC);

     sqlite3_bind_text(session->get_identity_without_trust_check, 2, user_id, -1, SQLITE_STATIC);

     const int result = sqlite3_step(session->get_identity_without_trust_check);

     switch (result) {

     case SQLITE_ROW:

         _identity = new_identity(

                 address,

                 (const char *) sqlite3_column_text(session->get_identity_without_trust_check, 0),

                 user_id,

                 (const char *) sqlite3_column_text(session->get_identity_without_trust_check, 1)

                 );

         assert(_identity);

         if (_identity == NULL) {

             sqlite3_reset(session->get_identity_without_trust_check);

             return PEP_OUT_OF_MEMORY;

         }

         _identity->comm_type = PEP_ct_unknown;

         const char* const _lang = (const char *)

             sqlite3_column_text(session->get_identity_without_trust_check, 2);

         if (_lang && _lang[0]) {

             assert(_lang[0] >= 'a' && _lang[0] <= 'z');

             assert(_lang[1] >= 'a' && _lang[1] <= 'z');

             assert(_lang[2] == 0);

             _identity->lang[0] = _lang[0];

             _identity->lang[1] = _lang[1];

             _identity->lang[2] = 0;

         }

         _identity->flags = (unsigned int)

             sqlite3_column_int(session->get_identity_without_trust_check, 3);

         _identity->me = (unsigned int)

             sqlite3_column_int(session->get_identity_without_trust_check, 4);

         *identity = _identity;

         break;

     default:

         status = PEP_CANNOT_FIND_IDENTITY;

         *identity = NULL;

     }

     sqlite3_reset(session->get_identity_without_trust_check);

     return status;

 }

 PEP_STATUS get_identities_by_address(

         PEP_SESSION session,

         const char *address,

         identity_list** id_list

     )

 {

     pEp_identity* ident;

     assert(session);

     assert(address);

     assert(address[0]);

     assert(id_list);

     if (!(session && address && address[0] && id_list))

         return PEP_ILLEGAL_VALUE;

     *id_list = NULL;

     identity_list* ident_list = NULL;

     sqlite3_reset(session->get_identities_by_address);

     sqlite3_bind_text(session->get_identities_by_address, 1, address, -1, SQLITE_STATIC);

     int result;

     while ((result = sqlite3_step(session->get_identities_by_address)) == SQLITE_ROW) {

         //"select user_id, main_key_id, username, comm_type, lang,"

         //"   identity.flags, is_own"

         ident = new_identity(

                 address,

                 (const char *) sqlite3_column_text(session->get_identities_by_address, 1),

                 (const char *) sqlite3_column_text(session->get_identities_by_address, 0),

                 (const char *) sqlite3_column_text(session->get_identities_by_address, 2)

                 );

         assert(ident);

         if (ident == NULL) {

             sqlite3_reset(session->get_identities_by_address);

             return PEP_OUT_OF_MEMORY;

         }

         ident->comm_type = PEP_ct_unknown;

         const char* const _lang = (const char *)

             sqlite3_column_text(session->get_identities_by_address, 3);

         if (_lang && _lang[0]) {

             assert(_lang[0] >= 'a' && _lang[0] <= 'z');

             assert(_lang[1] >= 'a' && _lang[1] <= 'z');

             assert(_lang[2] == 0);

             ident->lang[0] = _lang[0];

             ident->lang[1] = _lang[1];

             ident->lang[2] = 0;

         }

         ident->flags = (unsigned int)

             sqlite3_column_int(session->get_identities_by_address, 4);

         ident->me = (unsigned int)

             sqlite3_column_int(session->get_identities_by_address, 5);

         if (ident_list)

             identity_list_add(ident_list, ident);

         else

             ident_list = new_identity_list(ident);

     }

     sqlite3_reset(session->get_identities_by_address);

     *id_list = ident_list;

     if (!ident_list)

         return PEP_CANNOT_FIND_IDENTITY;

     return PEP_STATUS_OK;

 }

 PEP_STATUS exists_identity_entry(PEP_SESSION session, pEp_identity* identity,

                                  bool* exists) {

     assert(session);

     assert(identity);

     assert(!EMPTYSTR(identity->user_id));        

     assert(!EMPTYSTR(identity->address));

     if (!session || !exists || EMPTYSTR(identity->user_id) || EMPTYSTR(identity->address))

         return PEP_ILLEGAL_VALUE;

     *exists = false;

     PEP_STATUS status = PEP_STATUS_OK;

     sqlite3_reset(session->exists_identity_entry);

     sqlite3_bind_text(session->exists_identity_entry, 1, identity->address, -1,

                       SQLITE_STATIC);

     sqlite3_bind_text(session->exists_identity_entry, 2, identity->user_id, -1,

                       SQLITE_STATIC);

     int result = sqlite3_step(session->exists_identity_entry);

     switch (result) {

         case SQLITE_ROW: {

             // yeah yeah, I know, we could be lazy here, but it looks bad.

             *exists = (sqlite3_column_int(session->exists_identity_entry, 0) != 0);

             break;

         }

         default: 

             status = PEP_UNKNOWN_ERROR;

     }

     sqlite3_reset(session->exists_identity_entry);

     return status;

 }

 PEP_STATUS exists_trust_entry(PEP_SESSION session, pEp_identity* identity,

                               bool* exists) {

     assert(session);

     assert(identity);

     assert(!EMPTYSTR(identity->user_id));        

     assert(!EMPTYSTR(identity->fpr));

     if (!session || !exists || EMPTYSTR(identity->user_id) || EMPTYSTR(identity->fpr))

         return PEP_ILLEGAL_VALUE;

     *exists = false;

     PEP_STATUS status = PEP_STATUS_OK;

     sqlite3_reset(session->exists_trust_entry);

     sqlite3_bind_text(session->exists_trust_entry, 1, identity->user_id, -1,

                       SQLITE_STATIC);

     sqlite3_bind_text(session->exists_trust_entry, 2, identity->fpr, -1,

                       SQLITE_STATIC);

     int result = sqlite3_step(session->exists_trust_entry);

     switch (result) {

         case SQLITE_ROW: {

             // yeah yeah, I know, we could be lazy here, but it looks bad.

             *exists = (sqlite3_column_int(session->exists_trust_entry, 0) != 0);

             break;

         }

         default:

             status = PEP_UNKNOWN_ERROR;

     }

     sqlite3_reset(session->exists_trust_entry);

     return status;

 }

 // FIXME: We can rollback in set_identity on the return status,

 // so we should probably do that.

 PEP_STATUS set_pgp_keypair(PEP_SESSION session, const char* fpr) {

     if (!session || EMPTYSTR(fpr))

         return PEP_ILLEGAL_VALUE;

     int result;

     sqlite3_reset(session->set_pgp_keypair);

     sqlite3_bind_text(session->set_pgp_keypair, 1, fpr, -1,

             SQLITE_STATIC);

     result = sqlite3_step(session->set_pgp_keypair);

     sqlite3_reset(session->set_pgp_keypair);

     if (result != SQLITE_DONE) {

         return PEP_CANNOT_SET_PGP_KEYPAIR;

     }

     return PEP_STATUS_OK;

 }

 static PEP_STATUS _set_or_update_trust(PEP_SESSION session,

                                        pEp_identity* identity,

                                        sqlite3_stmt* set_or_update) {

     assert(session);

     assert(identity);

     assert(identity->user_id);

     assert(identity->fpr);

     if (!session || !identity || EMPTYSTR(identity->user_id) || EMPTYSTR(identity->fpr))

         return PEP_ILLEGAL_VALUE;

     int result;

     sqlite3_reset(set_or_update);

     sqlite3_bind_text(set_or_update, 1, identity->user_id, -1,

             SQLITE_STATIC);

     sqlite3_bind_text(set_or_update, 2, identity->fpr, -1,

             SQLITE_STATIC);

     sqlite3_bind_int(set_or_update, 3, identity->comm_type);

     result = sqlite3_step(set_or_update);

     assert(result == SQLITE_DONE);

     sqlite3_reset(set_or_update);

     if (result != SQLITE_DONE)

         return PEP_CANNOT_SET_TRUST;

     return PEP_STATUS_OK;

 }

 static PEP_STATUS _set_or_update_identity_entry(PEP_SESSION session,

                                                 pEp_identity* identity,

                                                 sqlite3_stmt* set_or_update) {

     assert(session);

     assert(identity);

     assert(set_or_update);

     if (!session || !identity || !identity->user_id || !identity->address)

         return PEP_ILLEGAL_VALUE;

     sqlite3_reset(set_or_update);

     sqlite3_bind_text(set_or_update, 1, identity->address, -1,

             SQLITE_STATIC);

     sqlite3_bind_text(set_or_update, 2, identity->fpr, -1,

             SQLITE_STATIC);

     sqlite3_bind_text(set_or_update, 3, identity->user_id, -1,

             SQLITE_STATIC);

     sqlite3_bind_int(set_or_update, 4, identity->flags);

     sqlite3_bind_int(set_or_update, 5, identity->me);

     int result = sqlite3_step(set_or_update);

     sqlite3_reset(set_or_update);

     if (result != SQLITE_DONE)

         return PEP_CANNOT_SET_IDENTITY;

     return PEP_STATUS_OK;

 }

 static PEP_STATUS _set_or_update_person(PEP_SESSION session, 

                                         pEp_identity* identity,

                                         sqlite3_stmt* set_or_update) {

     assert(session);

     assert(identity);

     assert(set_or_update);

     if (!session || !identity || !identity->user_id || !identity->username)

         return PEP_ILLEGAL_VALUE;

     sqlite3_reset(set_or_update);

     sqlite3_bind_text(set_or_update, 1, identity->user_id, -1,

             SQLITE_STATIC);

     sqlite3_bind_text(set_or_update, 2, identity->username, -1,

             SQLITE_STATIC);

     if (identity->lang[0])

         sqlite3_bind_text(set_or_update, 3, identity->lang, 2,

                 SQLITE_STATIC);

     else

         sqlite3_bind_null(set_or_update, 3);

     sqlite3_bind_text(set_or_update, 4, identity->fpr, -1,

                       SQLITE_STATIC);

     int result = sqlite3_step(set_or_update);

     sqlite3_reset(set_or_update);

     if (result != SQLITE_DONE)

         return PEP_CANNOT_SET_PERSON;

     return PEP_STATUS_OK;                                         

 }

 PEP_STATUS set_or_update_with_identity(PEP_SESSION session,

                                        pEp_identity* identity,

                                        PEP_STATUS (* set_function)(PEP_SESSION, pEp_identity*, sqlite3_stmt*),

                                        PEP_STATUS (* exists_function)(PEP_SESSION, pEp_identity*, bool*),                                       

                                        sqlite3_stmt* update_query,

                                        sqlite3_stmt* set_query,

                                        bool guard_transaction) {

     if (guard_transaction) {

         sqlite3_exec(session->db, "BEGIN TRANSACTION ;", NULL, NULL, NULL);

     }

     bool exists = false;

     PEP_STATUS status = exists_function(session, identity, &exists);

     if (status == PEP_STATUS_OK) {

         if (exists) {

             status = set_function(session, identity, update_query);

         }

         else {

             status = set_function(session, identity, set_query);                                              

         }                    

     }   

     if (guard_transaction) {        

         if (status != PEP_STATUS_OK)

             sqlite3_exec(session->db, "ROLLBACK ;", NULL, NULL, NULL);

         else 

             sqlite3_exec(session->db, "COMMIT ;", NULL, NULL, NULL);

     }                      

     return status;

 }

 PEP_STATUS _set_trust_internal(PEP_SESSION session, pEp_identity* identity,

                                bool guard_transaction) {

     return set_or_update_with_identity(session, identity,

                                        _set_or_update_trust,

                                         exists_trust_entry,

                                         session->update_trust,

                                         session->set_trust,

                                         guard_transaction);

 }

 // This is the TOP-LEVEL function. If you're calling from set_identity,

 // you can't use this one.

 PEP_STATUS set_trust(PEP_SESSION session, pEp_identity* identity) {

     PEP_STATUS status = PEP_STATUS_OK;

     status = _set_trust_internal(session, identity, true);

     if (status == PEP_STATUS_OK) {

         if ((identity->comm_type | PEP_ct_confirmed) == PEP_ct_pEp)

             status = set_as_pep_user(session, identity);

     }

     return status;

 }

 PEP_STATUS set_person(PEP_SESSION session, pEp_identity* identity,

                       bool guard_transaction) {

     return set_or_update_with_identity(session, identity,

                                        _set_or_update_person,

                                        exists_person,

                                        session->update_person,

                                        session->set_person,

                                        guard_transaction);

 }

 PEP_STATUS set_identity_entry(PEP_SESSION session, pEp_identity* identity,

                               bool guard_transaction) {

     return set_or_update_with_identity(session, identity,

                                        _set_or_update_identity_entry,

                                        exists_identity_entry,

                                        session->update_identity_entry,

                                        session->set_identity_entry,

                                        guard_transaction);

 }

 // This will NOT call set_as_pep_user; you have to do that separately.

 DYNAMIC_API PEP_STATUS set_identity(

         PEP_SESSION session, const pEp_identity *identity

     )

 {

     int result;

     assert(session);

     assert(identity);

     assert(identity->address);

     assert(identity->user_id);

     assert(identity->username);

     if (!(session && identity && identity->address &&

                 identity->user_id && identity->username))

         return PEP_ILLEGAL_VALUE;

     PEP_STATUS status = PEP_STATUS_OK;

     bool has_fpr = (!EMPTYSTR(identity->fpr));

     sqlite3_exec(session->db, "BEGIN TRANSACTION ;", NULL, NULL, NULL);

     if (identity->lang[0]) {

         assert(identity->lang[0] >= 'a' && identity->lang[0] <= 'z');

         assert(identity->lang[1] >= 'a' && identity->lang[1] <= 'z');

         assert(identity->lang[2] == 0);

     }

     if (has_fpr) {

         sqlite3_reset(session->set_pgp_keypair);

         sqlite3_bind_text(session->set_pgp_keypair, 1, identity->fpr, -1,

                 SQLITE_STATIC);

         result = sqlite3_step(session->set_pgp_keypair);

         sqlite3_reset(session->set_pgp_keypair);

         if (result != SQLITE_DONE) {

             sqlite3_exec(session->db, "ROLLBACK ;", NULL, NULL, NULL);

             return PEP_CANNOT_SET_PGP_KEYPAIR;

         }

     }

     // We do this because there are checks in set_person for

     // aliases, which modify the identity object on return.

     pEp_identity* ident_copy = identity_dup(identity); 

     if (!ident_copy)

         return PEP_OUT_OF_MEMORY;

     status = set_person(session, ident_copy, false);

     if (status != PEP_STATUS_OK) {

         sqlite3_exec(session->db, "ROLLBACK ;", NULL, NULL, NULL);

         goto pep_free;

     }

     status = set_identity_entry(session, ident_copy, false);

     if (status != PEP_STATUS_OK) {

         sqlite3_exec(session->db, "ROLLBACK ;", NULL, NULL, NULL);

         goto pep_free;

     }

     if (has_fpr) {

         status = _set_trust_internal(session, ident_copy, false);

         if (status != PEP_STATUS_OK) {

             sqlite3_exec(session->db, "ROLLBACK ;", NULL, NULL, NULL);

             goto pep_free;

         }

     }

     result = sqlite3_exec(session->db, "COMMIT ;", NULL, NULL, NULL);

     if (result == SQLITE_OK)

         status = PEP_STATUS_OK;

     else

         status = PEP_COMMIT_FAILED;

 pep_free:

     free_identity(ident_copy);

     return status;

 }

 PEP_STATUS update_pep_user_trust_vals(PEP_SESSION session,

                                       pEp_identity* user) {

     if (!user->user_id)

         return PEP_ILLEGAL_VALUE;

     sqlite3_reset(session->update_trust_to_pep);

     sqlite3_bind_text(session->update_trust_to_pep, 1, user->user_id, -1,

             SQLITE_STATIC);

     int result = sqlite3_step(session->update_trust_to_pep);

     sqlite3_reset(session->update_trust_to_pep);

     if (result != SQLITE_DONE)

         return PEP_CANNOT_SET_TRUST;

     return PEP_STATUS_OK;

 }

 // This ONLY sets the user flag. Must be called outside of a transaction.

 PEP_STATUS set_as_pep_user(PEP_SESSION session, pEp_identity* user) {

     assert(session);

     assert(user);

     assert(!EMPTYSTR(user->user_id));

     if (!session || !user || EMPTYSTR(user->user_id))

         return PEP_ILLEGAL_VALUE;

     PEP_STATUS status = PEP_STATUS_OK;

     bool person_exists = false;

     status = exists_person(session, user, &person_exists);

     if (status != PEP_STATUS_OK)

         return status;

     if (!person_exists)

         status = set_person(session, user, true);

     // Ok, let's set it.

     sqlite3_reset(session->set_as_pep_user);

     sqlite3_bind_text(session->set_as_pep_user, 1, user->user_id, -1,

             SQLITE_STATIC);

     int result = sqlite3_step(session->set_as_pep_user);

     sqlite3_reset(session->set_as_pep_user);

     if (result != SQLITE_DONE)

         return PEP_CANNOT_SET_PERSON;

     status = update_pep_user_trust_vals(session, user);

     return status;

 }

 PEP_STATUS exists_person(PEP_SESSION session, pEp_identity* identity,

                          bool* exists) {            

     // const char* user_id,

     //                      char** default_id, bool* exists) {

     assert(session);

     assert(exists);

     assert(identity);

     assert(!EMPTYSTR(identity->user_id));

     if (!session || !exists || !identity || EMPTYSTR(identity->user_id))

         return PEP_ILLEGAL_VALUE;

     *exists = false;

     const char* user_id = identity->user_id;

     char* alias_default = NULL;

     PEP_STATUS status = get_userid_alias_default(session, user_id, &alias_default);

     if (status == PEP_CANNOT_FIND_ALIAS || EMPTYSTR(alias_default)) {

         sqlite3_reset(session->exists_person);

         sqlite3_bind_text(session->exists_person, 1, user_id, -1,

                 SQLITE_STATIC);

         int result = sqlite3_step(session->exists_person);

         switch (result) {

             case SQLITE_ROW: {

                 // yeah yeah, I know, we could be lazy here, but it looks bad.

                 *exists = (sqlite3_column_int(session->exists_person, 0) != 0);

                 status = PEP_STATUS_OK;

                 break;

             }

             default:

                 sqlite3_reset(session->exists_person);

                 return PEP_UNKNOWN_ERROR;

         }

         sqlite3_reset(session->exists_person);

     }

     else if (status == PEP_STATUS_OK) {

         *exists = true; // thank you, delete on cascade!

         // FIXME: Should we correct the userid default here? I think we should.

         free(identity->user_id);

         identity->user_id = alias_default; // ownership transfer

     }

     else

         free(alias_default);

     return status;

 }

 DYNAMIC_API PEP_STATUS is_pep_user(PEP_SESSION session, pEp_identity *identity, bool* is_pep)

 {

     assert(session);

     assert(is_pep);

     assert(identity);

     assert(!EMPTYSTR(identity->user_id));

     if (!session || !is_pep || !identity || EMPTYSTR(identity->user_id))

         return PEP_ILLEGAL_VALUE;

     *is_pep = false;

     const char* user_id = identity->user_id;

     if (!session || EMPTYSTR(user_id))

         return PEP_ILLEGAL_VALUE;

     char* alias_default = NULL;

     PEP_STATUS status = get_userid_alias_default(session, user_id, &alias_default);

     if (status == PEP_CANNOT_FIND_ALIAS || EMPTYSTR(alias_default)) {

         free(alias_default);

         alias_default = strdup(user_id);

     }

     sqlite3_reset(session->is_pep_user);

     sqlite3_bind_text(session->is_pep_user, 1, user_id, -1,

             SQLITE_STATIC);

     int result = sqlite3_step(session->is_pep_user);

     switch (result) {

         case SQLITE_ROW: {

             // yeah yeah, I know, we could be lazy here, but it looks bad.

             *is_pep = (sqlite3_column_int(session->is_pep_user, 0) != 0);

             break;

         }

         default:

             sqlite3_reset(session->is_pep_user);

             free(alias_default);

             return PEP_CANNOT_FIND_PERSON;

     }

     sqlite3_reset(session->is_pep_user);

     return PEP_STATUS_OK;

 }

 PEP_STATUS remove_fpr_as_default(PEP_SESSION session, 

                                  const char* fpr) 

 {

     assert(fpr);

     if (!session || !fpr)

         return PEP_ILLEGAL_VALUE;

     sqlite3_reset(session->remove_fpr_as_default);

     sqlite3_bind_text(session->remove_fpr_as_default, 1, fpr, -1,

                       SQLITE_STATIC);

     int result = sqlite3_step(session->remove_fpr_as_default);

     sqlite3_reset(session->remove_fpr_as_default);

     if (result != SQLITE_DONE)

         return PEP_CANNOT_SET_IDENTITY; // misleading - could also be person

     return PEP_STATUS_OK;

 }

 PEP_STATUS replace_identities_fpr(PEP_SESSION session, 

                                  const char* old_fpr, 

                                  const char* new_fpr) 

 {

     assert(old_fpr);

     assert(new_fpr);

     if (!old_fpr || !new_fpr)

         return PEP_ILLEGAL_VALUE;

     sqlite3_reset(session->replace_identities_fpr);

     sqlite3_bind_text(session->replace_identities_fpr, 1, new_fpr, -1,

                       SQLITE_STATIC);

     sqlite3_bind_text(session->replace_identities_fpr, 2, old_fpr, -1,

                       SQLITE_STATIC);

     int result = sqlite3_step(session->replace_identities_fpr);

     sqlite3_reset(session->replace_identities_fpr);

     if (result != SQLITE_DONE)

         return PEP_CANNOT_SET_IDENTITY;

     return PEP_STATUS_OK;

 }

 PEP_STATUS update_trust_for_fpr(PEP_SESSION session, 

                                 const char* fpr, 

                                 PEP_comm_type comm_type)

 {