src/pEpEngine.c
branchENGINE-250
changeset 2481 dd1eaa8cc1eb
parent 2480 8799c08f67af
child 2483 7fb3205f2893
     1.1 --- a/src/pEpEngine.c	Wed Feb 07 10:22:23 2018 +0100
     1.2 +++ b/src/pEpEngine.c	Wed Feb 07 16:05:04 2018 +0100
     1.3 @@ -11,7 +11,7 @@
     1.4  #include <stdlib.h>
     1.5  #include <sqlite3.h>
     1.6  
     1.7 -#define _PEP_SQLITE_DEBUG 0
     1.8 +#define _PEP_SQLITE_DEBUG 1
     1.9  
    1.10  static volatile int init_count = -1;
    1.11  
    1.12 @@ -200,8 +200,13 @@
    1.13  
    1.14  static const char* sql_exists_identity_entry = 
    1.15      "select count(*) from identity "
    1.16 -    "   where address = ?1 and user_id = ?2;";
    1.17 -
    1.18 +    "   where (case when (address = ?1) then (1)"
    1.19 +    "               when (lower(address) = lower(?1)) then (1)"
    1.20 +    "               when (replace(lower(address),'.','') = replace(lower(?1),'.','')) then (1)"
    1.21 +    "               else 0"
    1.22 +    "          end) = 1"
    1.23 +    "    and user_id = ?2;";
    1.24 + 
    1.25  static const char *sql_set_identity_entry = 
    1.26      "insert into identity ("
    1.27      "       address, main_key_id, "
    1.28 @@ -219,7 +224,12 @@
    1.29      "   set main_key_id = upper(replace(?2,' ','')), "
    1.30      "       flags = ?4, " 
    1.31      "       is_own = ?5 "
    1.32 -    "   where address = ?1 and user_id = ?3 ;";
    1.33 +    "   where (case when (address = ?1) then (1)"
    1.34 +    "               when (lower(address) = lower(?1)) then (1)"
    1.35 +    "               when (replace(lower(address),'.','') = replace(lower(?1),'.','')) then (1) "
    1.36 +    "               else 0 "
    1.37 +    "          end) = 1 "
    1.38 +    "          and user_id = ?3 ;";
    1.39  
    1.40      // " (select"
    1.41      // "   coalesce("
    1.42 @@ -233,14 +243,34 @@
    1.43  static const char *sql_set_identity_flags = 
    1.44      "update identity set flags = "
    1.45      "    ((?1 & 255) | (select flags from identity"
    1.46 -    "                   where address = ?2 and user_id = ?3)) "
    1.47 -    "where address = ?2 and user_id = ?3 ;";
    1.48 +    "                    where (case when (address = ?2) then (1)"
    1.49 +    "                                when (lower(address) = lower(?2)) then (1)"
    1.50 +    "                                when (replace(lower(address),'.','') = replace(lower(?2),'.','')) then (1)"
    1.51 +    "                                else 0 "    
    1.52 +    "                           end) = 1 "
    1.53 +    "                           and user_id = ?3)) "
    1.54 +    "   where (case when (address = ?2) then (1)"
    1.55 +    "               when (lower(address) = lower(?2)) then (1)"
    1.56 +    "               when (replace(lower(address),'.','') = replace(lower(?2),'.','')) then (1)"
    1.57 +    "               else 0"
    1.58 +    "          end) = 1"
    1.59 +    "          and user_id = ?3 ;";
    1.60  
    1.61  static const char *sql_unset_identity_flags = 
    1.62      "update identity set flags = "
    1.63      "    ( ~(?1 & 255) & (select flags from identity"
    1.64 -    "                   where address = ?2 and user_id = ?3)) "
    1.65 -    "where address = ?2 and user_id = ?3 ;";
    1.66 +    "                    where (case when (address = ?2) then (1)"
    1.67 +    "                                when (lower(address) = lower(?2)) then (1)"
    1.68 +    "                                when (replace(lower(address),'.','') = replace(lower(?2),'.','')) then (1)"
    1.69 +    "                                else 0 "    
    1.70 +    "                           end) = 1 "
    1.71 +    "                           and user_id = ?3)) "
    1.72 +    "   where (case when (address = ?2) then (1)"
    1.73 +    "               when (lower(address) = lower(?2)) then (1)"
    1.74 +    "               when (replace(lower(address),'.','') = replace(lower(?2),'.','')) then (1)"
    1.75 +    "               else 0"
    1.76 +    "          end) = 1"
    1.77 +    "          and user_id = ?3 ;";
    1.78  
    1.79  static const char *sql_set_trust =
    1.80      "insert into trust (user_id, pgp_keypair_fpr, comm_type) "