Fixed Primary Key of identity table. Was (address) and know is (address, user_id). Added 'virtual' user_id (const string + address) as a side effect, in case user_id isn't given and no user have been created with same address.
authorEdouard Tisserant
Sat, 30 Apr 2016 17:59:16 +0200
changeset 55932811e6a231c
parent 558 71a650d71006
child 560 61966cd2d7a7
Fixed Primary Key of identity table. Was (address) and know is (address, user_id). Added 'virtual' user_id (const string + address) as a side effect, in case user_id isn't given and no user have been created with same address.
src/keymanagement.c
src/pEpEngine.c
src/pEpEngine.h
src/pEp_internal.h
     1.1 --- a/src/keymanagement.c	Thu Apr 28 22:07:10 2016 +0200
     1.2 +++ b/src/keymanagement.c	Sat Apr 30 17:59:16 2016 +0200
     1.3 @@ -70,7 +70,43 @@
     1.4      if (!(session && identity && !EMPTYSTR(identity->address)))
     1.5          return PEP_ILLEGAL_VALUE;
     1.6  
     1.7 -    status = get_identity(session, identity->address, &stored_identity);
     1.8 +    int _no_user_id = EMPTYSTR(identity->user_id);
     1.9 +
    1.10 +    if (_no_user_id)
    1.11 +    {
    1.12 +        free(identity->user_id);
    1.13 +
    1.14 +        status = get_best_user(session,
    1.15 +                              identity->address,
    1.16 +                              &identity->user_id);
    1.17 +
    1.18 +        // Default user_id, aka Virtual user_id
    1.19 +        if (status == PEP_CANNOT_FIND_IDENTITY)
    1.20 +        {
    1.21 +            identity->user_id = calloc(1, identity->address_size + 5);
    1.22 +            if (!identity->user_id)
    1.23 +            {
    1.24 +                return PEP_OUT_OF_MEMORY;
    1.25 +            }
    1.26 +            snprintf(identity->user_id, identity->address_size + 5,
    1.27 +                     "TOFU_%s", identity->address);
    1.28 +        }
    1.29 +        else if (status != PEP_STATUS_OK)
    1.30 +        {
    1.31 +            return status;
    1.32 +        }
    1.33 +        
    1.34 +        if(identity->user_id)
    1.35 +        {
    1.36 +            identity->user_id_size = strlen(identity->user_id);
    1.37 +        }
    1.38 +    }
    1.39 +    
    1.40 +    status = get_identity(session,
    1.41 +                          identity->address,
    1.42 +                          identity->user_id,
    1.43 +                          &stored_identity);
    1.44 +    
    1.45      assert(status != PEP_OUT_OF_MEMORY);
    1.46      if (status == PEP_OUT_OF_MEMORY)
    1.47          return PEP_OUT_OF_MEMORY;
    1.48 @@ -82,15 +118,6 @@
    1.49          if (status == PEP_OUT_OF_MEMORY)
    1.50              return PEP_OUT_OF_MEMORY;
    1.51  
    1.52 -        if (EMPTYSTR(identity->user_id)) {
    1.53 -            free(identity->user_id);
    1.54 -            identity->user_id = strndup(stored_identity->user_id, stored_identity->user_id_size);
    1.55 -            assert(identity->user_id);
    1.56 -            if (identity->user_id == NULL)
    1.57 -                return PEP_OUT_OF_MEMORY;
    1.58 -            identity->user_id_size = stored_identity->user_id_size;
    1.59 -        }
    1.60 -
    1.61          if (EMPTYSTR(identity->username)) {
    1.62              free(identity->username);
    1.63              identity->username = strndup(stored_identity->username, stored_identity->username_size);
    1.64 @@ -218,10 +245,15 @@
    1.65              identity->username_size = 9;
    1.66          }
    1.67  
    1.68 -        status = set_identity(session, identity);
    1.69 -        assert(status == PEP_STATUS_OK);
    1.70 -        if (status != PEP_STATUS_OK) {
    1.71 -            return status;
    1.72 +        // Identity doesn't get stored if is was just about checking existing
    1.73 +        // user by address (i.e. no user id but already stored)
    1.74 +        if (!(_no_user_id && stored_identity))
    1.75 +        {
    1.76 +            status = set_identity(session, identity);
    1.77 +            assert(status == PEP_STATUS_OK);
    1.78 +            if (status != PEP_STATUS_OK) {
    1.79 +                return status;
    1.80 +            }
    1.81          }
    1.82      }
    1.83  
     2.1 --- a/src/pEpEngine.c	Thu Apr 28 22:07:10 2016 +0200
     2.2 +++ b/src/pEpEngine.c	Sat Apr 30 17:59:16 2016 +0200
     2.3 @@ -13,6 +13,7 @@
     2.4      static const char *sql_log;
     2.5      static const char *sql_trustword;
     2.6      static const char *sql_get_identity;
     2.7 +    static const char *sql_get_best_user;
     2.8      static const char *sql_set_person;
     2.9      static const char *sql_set_pgp_keypair;
    2.10      static const char *sql_set_identity;
    2.11 @@ -140,14 +141,15 @@
    2.12                  "   comment text\n"
    2.13                  ");\n"
    2.14                  "create table if not exists identity (\n"
    2.15 -                "   address text primary key,\n"
    2.16 +                "   address text,\n"
    2.17                  "   user_id text\n"
    2.18                  "       references person (id)\n"
    2.19                  "       on delete cascade,\n"
    2.20                  "   main_key_id text\n"
    2.21                  "       references pgp_keypair (fpr)\n"
    2.22                  "       on delete set null,\n"
    2.23 -                "   comment text\n"
    2.24 +                "   comment text,\n"
    2.25 +                "   primary key (address, user_id)\n"
    2.26                  ");\n"
    2.27                  "create table if not exists trust (\n"
    2.28                  "   user_id text not null\n"
    2.29 @@ -186,13 +188,21 @@
    2.30          sql_log = "insert into log (title, entity, description, comment)"
    2.31                    "values (?1, ?2, ?3, ?4);";
    2.32  
    2.33 -        sql_get_identity =    "select fpr, identity.user_id, username, comm_type, lang"
    2.34 +        sql_get_identity =    "select fpr, username, comm_type, lang"
    2.35                              "   from identity"
    2.36                              "   join person on id = identity.user_id"
    2.37                              "   join pgp_keypair on fpr = identity.main_key_id"
    2.38                              "   join trust on id = trust.user_id"
    2.39                              "       and pgp_keypair_fpr = identity.main_key_id"
    2.40 -                            "   where address = ?1 ;";
    2.41 +                            "   where address = ?1 and identity.user_id = ?2;";
    2.42 +
    2.43 +        sql_get_best_user = "select identity.user_id"
    2.44 +                            "   from identity"
    2.45 +                            "   join trust on trust.user_id = identity.user_id"
    2.46 +                            "       and pgp_keypair_fpr = identity.main_key_id"
    2.47 +                            "   where address = ?1"
    2.48 +                            "   order by comm_type DESC, identity.rowid DESC"
    2.49 +                            "   limit 1;";
    2.50  
    2.51          sql_trustword = "select id, word from wordlist where lang = lower(?1) "
    2.52                         "and id = ?2 ;";
    2.53 @@ -201,21 +211,21 @@
    2.54                           "values (?1, ?2, ?3) ;";
    2.55  
    2.56          sql_set_pgp_keypair = "insert or replace into pgp_keypair (fpr) "
    2.57 -                              "values (?1) ;";
    2.58 +                              "values (upper(replace(?1,' ',''))) ;";
    2.59  
    2.60          sql_set_identity = "insert or replace into identity (address, main_key_id, "
    2.61 -                           "user_id) values (?1, ?2, ?3) ;";
    2.62 +                           "user_id) values (?1, upper(replace(?2,' ','')), ?3) ;";
    2.63  
    2.64          sql_set_trust = "insert or replace into trust (user_id, pgp_keypair_fpr, comm_type) "
    2.65 -                        "values (?1, ?2, ?3) ;";
    2.66 +                        "values (?1, upper(replace(?2,' ','')), ?3) ;";
    2.67  
    2.68 -        sql_get_trust = "select user_id, comm_type from trust where user_id = ?1 "
    2.69 -                        "and pgp_keypair_fpr = ?2 ;";
    2.70 +        sql_get_trust = "select comm_type from trust where user_id = ?1 "
    2.71 +                        "and pgp_keypair_fpr = upper(replace(?2,' ','')) ;";
    2.72  
    2.73 -        sql_least_trust = "select min(comm_type) from trust where pgp_keypair_fpr = ?1 ;";
    2.74 +        sql_least_trust = "select min(comm_type) from trust where pgp_keypair_fpr = upper(replace(?1,' ','')) ;";
    2.75  
    2.76          sql_mark_as_compromized = "update trust not indexed set comm_type = 15"
    2.77 -                                  " where pgp_keypair_fpr = ?1 ;";
    2.78 +                                  " where pgp_keypair_fpr = upper(replace(?1,' ','')) ;";
    2.79  
    2.80          sql_crashdump = "select timestamp, title, entity, description, comment"
    2.81                          " from log order by timestamp desc limit ?1 ;";
    2.82 @@ -226,13 +236,13 @@
    2.83  
    2.84          // blacklist
    2.85  
    2.86 -        sql_blacklist_add = "insert or replace into blacklist_keys (fpr) values (?1) ;"
    2.87 -                            "delete from identity where main_key_id = ?1 ;"
    2.88 -                            "delete from pgp_keypair where fpr = ?1 ;";
    2.89 +        sql_blacklist_add = "insert or replace into blacklist_keys (fpr) values (upper(replace(?1,' ',''))) ;"
    2.90 +                            "delete from identity where main_key_id = upper(replace(?1,' ','')) ;"
    2.91 +                            "delete from pgp_keypair where fpr = upper(replace(?1,' ','')) ;";
    2.92  
    2.93 -        sql_blacklist_delete = "delete from blacklist_keys where fpr = ?1 ;";
    2.94 +        sql_blacklist_delete = "delete from blacklist_keys where fpr = upper(replace(?1,' ','')) ;";
    2.95  
    2.96 -        sql_blacklist_is_listed = "select count(*) from blacklist_keys where fpr = ?1 ;";
    2.97 +        sql_blacklist_is_listed = "select count(*) from blacklist_keys where fpr = upper(replace(?1,' ','')) ;";
    2.98  
    2.99          sql_blacklist_retrieve = "select * from blacklist_keys ;";
   2.100      }
   2.101 @@ -249,6 +259,10 @@
   2.102              (int)strlen(sql_get_identity), &_session->get_identity, NULL);
   2.103      assert(int_result == SQLITE_OK);
   2.104  
   2.105 +    int_result = sqlite3_prepare_v2(_session->db, sql_get_best_user,
   2.106 +            (int)strlen(sql_get_best_user), &_session->get_best_user, NULL);
   2.107 +    assert(int_result == SQLITE_OK);
   2.108 +
   2.109      int_result = sqlite3_prepare_v2(_session->db, sql_set_person,
   2.110              (int)strlen(sql_set_person), &_session->set_person, NULL);
   2.111      assert(int_result == SQLITE_OK);
   2.112 @@ -360,6 +374,8 @@
   2.113                  sqlite3_finalize(session->trustword);
   2.114              if (session->get_identity)
   2.115                  sqlite3_finalize(session->get_identity);
   2.116 +            if (session->get_best_user)
   2.117 +                sqlite3_finalize(session->get_best_user);
   2.118              if (session->set_person)
   2.119                  sqlite3_finalize(session->set_person);
   2.120              if (session->set_pgp_keypair)
   2.121 @@ -671,7 +687,9 @@
   2.122  }
   2.123  
   2.124  DYNAMIC_API PEP_STATUS get_identity(
   2.125 -        PEP_SESSION session, const char *address,
   2.126 +        PEP_SESSION session,
   2.127 +        const char *address,
   2.128 +        const char *user_id,
   2.129          pEp_identity **identity
   2.130      )
   2.131  {
   2.132 @@ -689,6 +707,7 @@
   2.133  
   2.134      sqlite3_reset(session->get_identity);
   2.135      sqlite3_bind_text(session->get_identity, 1, address, -1, SQLITE_STATIC);
   2.136 +    sqlite3_bind_text(session->get_identity, 2, user_id, -1, SQLITE_STATIC);
   2.137  
   2.138      result = sqlite3_step(session->get_identity);
   2.139      switch (result) {
   2.140 @@ -696,15 +715,15 @@
   2.141          _identity = new_identity(
   2.142                  address,
   2.143                  (const char *) sqlite3_column_text(session->get_identity, 0),
   2.144 -                (const char *) sqlite3_column_text(session->get_identity, 1),
   2.145 -                (const char *) sqlite3_column_text(session->get_identity, 2)
   2.146 +                user_id,
   2.147 +                (const char *) sqlite3_column_text(session->get_identity, 1)
   2.148                  );
   2.149          assert(_identity);
   2.150          if (_identity == NULL)
   2.151              return PEP_OUT_OF_MEMORY;
   2.152  
   2.153 -        _identity->comm_type = (PEP_comm_type) sqlite3_column_int(session->get_identity, 3);
   2.154 -        _lang = (const char *) sqlite3_column_text(session->get_identity, 4);
   2.155 +        _identity->comm_type = (PEP_comm_type) sqlite3_column_int(session->get_identity, 2);
   2.156 +        _lang = (const char *) sqlite3_column_text(session->get_identity, 3);
   2.157          if (_lang && _lang[0]) {
   2.158              assert(_lang[0] >= 'a' && _lang[0] <= 'z');
   2.159              assert(_lang[1] >= 'a' && _lang[1] <= 'z');
   2.160 @@ -724,6 +743,41 @@
   2.161      return status;
   2.162  }
   2.163  
   2.164 +DYNAMIC_API PEP_STATUS get_best_user(
   2.165 +       PEP_SESSION session,
   2.166 +       const char *address,
   2.167 +       char **user_id
   2.168 +    )
   2.169 +{
   2.170 +    PEP_STATUS status = PEP_STATUS_OK;
   2.171 +    int result;
   2.172 +    
   2.173 +    assert(session);
   2.174 +    assert(address);
   2.175 +    assert(user_id);
   2.176 +    
   2.177 +    *user_id = NULL;
   2.178 +    
   2.179 +    if (!(session && address && user_id))
   2.180 +        return PEP_ILLEGAL_VALUE;
   2.181 +    
   2.182 +    sqlite3_reset(session->get_best_user);
   2.183 +    sqlite3_bind_text(session->get_best_user, 1, address, -1, SQLITE_STATIC);
   2.184 +    
   2.185 +    result = sqlite3_step(session->get_best_user);
   2.186 +    switch (result) {
   2.187 +        case SQLITE_ROW: {
   2.188 +            *user_id = strdup((const char *)sqlite3_column_text(session->get_best_user, 0));
   2.189 +            break;
   2.190 +        }
   2.191 +        default:
   2.192 +            status = PEP_CANNOT_FIND_IDENTITY;
   2.193 +    }
   2.194 +    
   2.195 +    sqlite3_reset(session->get_best_user);
   2.196 +    return status;
   2.197 +}
   2.198 +
   2.199  DYNAMIC_API PEP_STATUS set_identity(
   2.200          PEP_SESSION session, const pEp_identity *identity
   2.201      )
   2.202 @@ -868,16 +922,7 @@
   2.203      result = sqlite3_step(session->get_trust);
   2.204      switch (result) {
   2.205      case SQLITE_ROW: {
   2.206 -        const char * user_id = (const char *) sqlite3_column_text(session->get_trust, 0);
   2.207 -        int comm_type = (PEP_comm_type) sqlite3_column_int(session->get_trust, 1);
   2.208 -
   2.209 -        if (strcmp(user_id, identity->user_id) != 0) {
   2.210 -            free(identity->user_id);
   2.211 -            identity->user_id = strdup(user_id);
   2.212 -            assert(identity->user_id);
   2.213 -            if (identity->user_id == NULL)
   2.214 -                return PEP_OUT_OF_MEMORY;
   2.215 -        }
   2.216 +        int comm_type = (PEP_comm_type) sqlite3_column_int(session->get_trust, 0);
   2.217          identity->comm_type = comm_type;
   2.218          break;
   2.219      }
     3.1 --- a/src/pEpEngine.h	Thu Apr 28 22:07:10 2016 +0200
     3.2 +++ b/src/pEpEngine.h	Sat Apr 30 17:59:16 2016 +0200
     3.3 @@ -443,10 +443,32 @@
     3.4  //        more
     3.5  
     3.6  DYNAMIC_API PEP_STATUS get_identity(
     3.7 -        PEP_SESSION session, const char *address,
     3.8 +        PEP_SESSION session,
     3.9 +        const char *address,
    3.10 +        const char *user_id,
    3.11          pEp_identity **identity
    3.12      );
    3.13  
    3.14 +// get_best_user() - get best user_id candidate for a given address
    3.15 +//
    3.16 +//	parameters:
    3.17 +//		session (in)		session handle
    3.18 +//		address (in)		C string with communication address, UTF-8 encoded
    3.19 +//		user_id (out)		pointer to user_id string
    3.20 +//
    3.21 +//	caveat:
    3.22 +//	    the address string is being copied; the original string remains in the
    3.23 +//	    ownership of the caller
    3.24 +//		the resulting user_id string goes to the ownership of the
    3.25 +//		caller and has to be freed with free() when not in use any
    3.26 +//		more
    3.27 +    
    3.28 +DYNAMIC_API PEP_STATUS get_best_user(
    3.29 +        PEP_SESSION session,
    3.30 +        const char *address,
    3.31 +        char **user_id
    3.32 +    );
    3.33 +    
    3.34  
    3.35  // set_identity() - set identity information
    3.36  //
     4.1 --- a/src/pEp_internal.h	Thu Apr 28 22:07:10 2016 +0200
     4.2 +++ b/src/pEp_internal.h	Sat Apr 30 17:59:16 2016 +0200
     4.3 @@ -88,6 +88,7 @@
     4.4      sqlite3_stmt *log;
     4.5      sqlite3_stmt *trustword;
     4.6      sqlite3_stmt *get_identity;
     4.7 +    sqlite3_stmt *get_best_user;
     4.8      sqlite3_stmt *set_person;
     4.9      sqlite3_stmt *set_pgp_keypair;
    4.10      sqlite3_stmt *set_identity;