// 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 "KeySync_fsm.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) {

    const char *z2;

    int 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 ){

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

        if( z1 ){

            for(int 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 ;";

// FIXME?: problems if we don't have a key for the user - we get nothing

static const char *sql_get_identity =  

    "select fpr, username, comm_type, lang,"

    "   identity.flags | pgp_keypair.flags,"

    "   is_own, pEp_version_major, pEp_version_minor"

    "   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_identities_by_main_key_id =  

    "select address, identity.user_id, username, comm_type, lang,"

    "   identity.flags | pgp_keypair.flags,"

    "   is_own, pEp_version_major, pEp_version_minor"

    "   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.main_key_id = ?1" 

    "   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, pEp_version_major, pEp_version_minor"

    "   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, pEp_version_major, pEp_version_minor"

    "   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_get_identities_by_userid =  

    "select address, fpr, username, comm_type, lang,"

    "   identity.flags | pgp_keypair.flags,"

    "   is_own, pEp_version_major, pEp_version_minor"

    "   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.user_id = ?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)"

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

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,' ',''))))"         

    "   where id = ?1 ;";

// Will cascade.

static const char *sql_delete_person = 

     "delete from person 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 ;";

// This will cascade to identity and trust

static const char* sql_replace_userid =

    "update person set id = ?1 " 

    "   where id = ?2;";

// Hopefully this cascades and removes trust entries...

static const char *sql_delete_key =

    "delete from pgp_keypair "

    "   where fpr = ?1 ; ";

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_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,"

    "       pEp_version_major, pEp_version_minor"

    "   ) values ("

    "       ?1,"

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

    "       ?3,"

    "       ?4,"

    "       ?5,"

    "       ?6,"

    "       ?7"

    "   );";

static const char* sql_update_identity_entry =    

    "update identity "

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

    "       flags = ?4, " 

    "       is_own = ?5, "

    "       pEp_version_major = ?6, "

    "       pEp_version_major = ?7 "    

    "   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 & 65535) | (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 & 65535) & (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_pEp_version =

    "update identity "

    "   set pEp_version_major = ?1, "

    "       pEp_version_minor = ?2 "

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

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

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

    "               else 0 "

    "          end) = 1 "

    "          and user_id = ?4 ;";

static const char *sql_upgrade_pEp_version_by_user_id =

    "update identity "

    "   set pEp_version_major = ?1, "

    "       pEp_version_minor = ?2 "

    "       where user_id = ?3 "

    "           and (case when (pEp_version_major < ?1) then (1)"

    "                     when (pEp_version_major > ?1) then (0)"

    "                     when (pEp_version_minor < ?2) then (1)"

    "                     else 0 "

    "           end) = 1 ;";

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_get_trust_by_userid = 

    "select pgp_keypair_fpr, comm_type from trust where user_id = ?1 ";

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_is_own_address =

    "select count(*) from ("

    "   select address 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 identity.is_own = 1"

    ");";

static const char *sql_own_identities_retrieve =  

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

    "   lang, identity.flags | pgp_keypair.flags, pEp_version_major, pEp_version_minor"

    "   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_all_keys_for_user =

    "select pgp_keypair_fpr from trust"

    "   where user_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) "

    "values (?1, "

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

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

static const char *sql_sequence_value2 = 

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

// 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_replacement_fpr = 

    "select replacement_fpr, revocation_date from revoked_keys"

    "    where revoked_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 const char *sql_add_into_social_graph =

    "insert or replace into social_graph(own_userid, own_address, contact_userid) "

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

static const char *sql_get_own_address_binding_from_contact =

    "select own_address from social_graph where own_userid = ?1 and contact_userid = ?2 ;";

static const char *sql_set_revoke_contact_as_notified =

    "insert or replace into revocation_contact_list(fpr, contact_id) values (?1, ?2) ;";

static const char *sql_get_contacted_ids_from_revoke_fpr =

    "select * from revocation_contact_list where fpr = ?1 ;";

static const char *sql_was_id_for_revoke_contacted = 

    "select count(*) from revocation_contact_list where fpr = ?1 and contact_id = ?2 ;";

// We only need user_id and address, since in the main usage, we'll call update_identity

// on this anyway when sending out messages.

static const char *sql_get_last_contacted =

    "select user_id, address from identity where datetime('now') < datetime(timestamp, '+14 days') ; ";

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;

}

// TODO: refactor and generalise these two functions if possible.

static int db_contains_table(PEP_SESSION session, const char* table_name) {

    if (!session || !table_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, q_size;

    const char* q1 = "SELECT name FROM sqlite_master WHERE type='table' AND name='{"; // 61

    const char* q2 = "}';";       // 3

    q_size = 64;

    t_size = strlen(table_name);

    size_t query_len = q_size + t_size + 1;

    if (query_len > max_q_len)

        return -1;

    strlcpy(sql_buf, q1, max_q_len);

    strlcat(sql_buf, table_name, max_q_len);

    strlcat(sql_buf, q2, 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;

}

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;

}

PEP_STATUS repair_altered_tables(PEP_SESSION session) {

    PEP_STATUS status = PEP_STATUS_OK;

    const unsigned int _PEP_MAX_AFFECTED = 5;

    char** table_names = calloc(_PEP_MAX_AFFECTED, sizeof(char*));

    if (!table_names)

        return PEP_OUT_OF_MEMORY;

    const char* sql_query = "select tbl_name from sqlite_master WHERE sql LIKE '%REFERENCES%' AND sql LIKE '%_old%';";

    sqlite3_stmt *stmt; 

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

    int i = 0;

    int int_result = 0;

    while ((int_result = sqlite3_step(stmt)) == SQLITE_ROW && i < _PEP_MAX_AFFECTED) {

        table_names[i++] = strdup((const char*)(sqlite3_column_text(stmt, 0)));

    }

    sqlite3_finalize(stmt);      

    if ((int_result != SQLITE_DONE && int_result != SQLITE_OK) || i > (_PEP_MAX_AFFECTED + 1)) {

        status = PEP_UNKNOWN_DB_ERROR;

        goto pEp_free;

    }

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

        const char* table_name = table_names[i];

        if (!table_name)

            break;

        if (strcmp(table_name, "identity") == 0) {

            int_result = sqlite3_exec(session->db,

                "PRAGMA foreign_keys=off;\n"

                "BEGIN TRANSACTION;\n"

                "create table _identity_new (\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_new SELECT * from identity;\n"

                "DROP TABLE identity;\n"

                "ALTER TABLE _identity_new RENAME TO identity;\n"

                "COMMIT;\n"

                "PRAGMA foreign_keys=on;"

                ,

                NULL,

                NULL,

                NULL

            );

            assert(int_result == PEP_STATUS_OK);

        }

        else if (strcmp(table_name, "trust") == 0) {

            int_result = sqlite3_exec(session->db,

                "PRAGMA foreign_keys=off;\n"

                "BEGIN TRANSACTION;\n"

                "create table _trust_new (\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_new SELECT * from trust;\n"

                "DROP TABLE trust;\n"

                "ALTER TABLE _trust_new RENAME TO trust;\n"

                "COMMIT;\n"

                "PRAGMA foreign_keys=on;"

                ,

                NULL,

                NULL,

                NULL

            );             

            assert(int_result == PEP_STATUS_OK);                       

        }

        else if (strcmp(table_name, "alternate_user_id") == 0) {

            int_result = sqlite3_exec(session->db,

                "PRAGMA foreign_keys=off;\n"

                "BEGIN TRANSACTION;\n"

                "create table _alternate_user_id_new (\n"

                "    default_id text references person (id)\n"

                "       on delete cascade on update cascade,\n"

                "    alternate_id text primary key\n"

                ");\n"

                "INSERT INTO _alternate_user_id_new SELECT * from alternate_user_id;\n"

                "DROP TABLE alternate_user_id;\n"

                "ALTER TABLE _alternate_user_id_new RENAME TO alternate_user_id;\n"

                "COMMIT;\n"

                "PRAGMA foreign_keys=on;"                

                ,

                NULL,

                NULL,

                NULL

            );

            assert(int_result == PEP_STATUS_OK);

        }

        else if (strcmp(table_name, "revocation_contact_list") == 0) {

            int_result = sqlite3_exec(session->db,

                "PRAGMA foreign_keys=off;\n"

                "BEGIN TRANSACTION;\n"

                "create table _revocation_contact_list_new (\n"

                "   fpr text not null references pgp_keypair (fpr)\n"

                "       on delete cascade,\n"

                "   contact_id text not null references person (id)\n"

                "       on delete cascade on update cascade,\n"

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

                "   PRIMARY KEY(fpr, contact_id)\n"            

                ");\n"

                "INSERT INTO _revocation_contact_list_new SELECT * from revocation_contact_list;\n"

                "DROP TABLE revocation_contact_list;\n"

                "ALTER TABLE _revocation_contact_list_new RENAME TO revocation_contact_list;\n"

                "COMMIT;\n"

                "PRAGMA foreign_keys=on;"                

                ,

                NULL,

                NULL,

                NULL

            );      

            assert(int_result == PEP_STATUS_OK);                              

        }

        else if (strcmp(table_name, "social_graph")) {

            int_result = sqlite3_exec(session->db,

                "PRAGMA foreign_keys=off;\n"

                "BEGIN TRANSACTION;\n"

                "create table _social_new (\n"

                "    own_userid text,\n"

                "    own_address text,\n"

                "    contact_userid text,\n"

                "    CONSTRAINT fk_own_identity\n"

                "       FOREIGN KEY(own_address, own_userid)\n" 

                "       REFERENCES identity(address, user_id)\n"

                "       ON DELETE CASCADE ON UPDATE CASCADE\n"

                ");\n"

                "INSERT INTO _social_graph_new SELECT * from social_graph;\n"

                "DROP TABLE social_graph;\n"

                "ALTER TABLE _social_graph_new RENAME TO social_graph;\n"

                "COMMIT;\n"

                "PRAGMA foreign_keys=on;"                

                ,

                NULL,

                NULL,

                NULL

            );

            assert(int_result == PEP_STATUS_OK);                                    

        }        

    }

    int_result = sqlite3_exec(

        session->db,

        "PRAGMA foreign_key_check;\n"

        ,

        NULL,

        NULL,

        NULL

    );

    assert(int_result == SQLITE_OK);

pEp_free:

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

        if (table_names[i])

            free(table_names[i]);

        else

            break;

    }

    free(table_names);

    return status;

}

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,

        messageToSend_t messageToSend,

        inject_sync_event_t inject_sync_event

    )

{

    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;

    _session->messageToSend = messageToSend;

    _session->inject_sync_event = inject_sync_event;

#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 "11"

    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"