ENGINE-171: version which should not evaluate each of the three cases every time, plus overloaded lower() function. This one passes the current tests, meaning it doesn't screw up what already words. ENGINE-171
authorKrista Bennett <krista@pep-project.org>
Tue, 27 Jun 2017 14:35:21 +0200
branchENGINE-171
changeset 18843e00ee967471
parent 1868 a3bf01e38752
child 1885 da4e99a16e99
ENGINE-171: version which should not evaluate each of the three cases every time, plus overloaded lower() function. This one passes the current tests, meaning it doesn't screw up what already words.
src/pEpEngine.c
     1.1 --- a/src/pEpEngine.c	Fri Jun 16 13:52:07 2017 +0200
     1.2 +++ b/src/pEpEngine.c	Tue Jun 27 14:35:21 2017 +0200
     1.3 @@ -10,6 +10,32 @@
     1.4  
     1.5  static int init_count = -1;
     1.6  
     1.7 +// sql overloaded functions - modified from sqlite3.c
     1.8 +static void _sql_lower(sqlite3_context* ctx, int argc, sqlite3_value** argv) {
     1.9 +    char *z1;
    1.10 +    const char *z2;
    1.11 +    int i, n;
    1.12 +    z2 = (char*)sqlite3_value_text(argv[0]);
    1.13 +    n = sqlite3_value_bytes(argv[0]);
    1.14 +    /* Verify that the call to _bytes() does not invalidate the _text() pointer */
    1.15 +    assert( z2==(char*)sqlite3_value_text(argv[0]) );
    1.16 +    if( z2 ){
    1.17 +        z1 = (char*)sqlite3_malloc(n+1);
    1.18 +        if( z1 ){
    1.19 +            for(i=0; i<n; i++){
    1.20 +                char c = z2[i];
    1.21 +                char c_mod = c | 0x20;
    1.22 +                if (c_mod < 0x61 || c_mod > 0x7a)
    1.23 +                    c_mod = c;
    1.24 +                z1[i] = c_mod;
    1.25 +            }
    1.26 +            z1[n] = '\0';
    1.27 +            sqlite3_result_text(ctx, z1, n, sqlite3_free);
    1.28 +        }
    1.29 +    }
    1.30 +}
    1.31 +
    1.32 +
    1.33  // sql manipulation statements
    1.34  static const char *sql_log = 
    1.35      "insert into log (title, entity, description, comment)"
    1.36 @@ -19,6 +45,8 @@
    1.37      "select id, word from wordlist where lang = lower(?1) "
    1.38      "and id = ?2 ;";
    1.39  
    1.40 +    //    where (case when address = "JOEGtestuser@testdomain.org" then (1) else 0 end) = 1
    1.41 +
    1.42  static const char *sql_get_identity =  
    1.43      "select fpr, username, comm_type, lang,"
    1.44      "   identity.flags | pgp_keypair.flags"
    1.45 @@ -26,10 +54,12 @@
    1.46      "   join person on id = identity.user_id"
    1.47      "   join pgp_keypair on fpr = identity.main_key_id"
    1.48      "   join trust on id = trust.user_id"
    1.49 -    "       and pgp_keypair_fpr = identity.main_key_id"
    1.50 -    "   where (address = ?1" 
    1.51 -    "              or (address = ?1 collate nocase)"
    1.52 -    "              or (replace(address,'.','') = replace(?1,'.','')))"
    1.53 +    "       and pgp_keypair_fpr = identity.main_key_id"    
    1.54 +    "   where (case when (address = ?1) then (1)"
    1.55 +    "               when (lower(address) = lower(?1)) then (1)"
    1.56 +    "               when (replace(lower(address),'.','') = replace(lower(?1),'.','')) then (1)"
    1.57 +    "               else 0"
    1.58 +    "          end) = 1"
    1.59      "   and identity.user_id = ?2;";
    1.60  
    1.61  static const char *sql_replace_identities_fpr =  
    1.62 @@ -417,6 +447,21 @@
    1.63              &version,
    1.64              NULL
    1.65          );
    1.66 +
    1.67 +        assert(int_result == SQLITE_OK);
    1.68 +        
    1.69 +        void (*xFunc_lower)(sqlite3_context*,int,sqlite3_value**) = &_sql_lower;
    1.70 +        
    1.71 +        int_result = sqlite3_create_function_v2(
    1.72 +            _session->db,
    1.73 +            "lower",
    1.74 +            1,
    1.75 +            SQLITE_UTF8 | SQLITE_DETERMINISTIC,
    1.76 +            NULL,
    1.77 +            xFunc_lower,
    1.78 +            NULL,
    1.79 +            NULL,
    1.80 +            NULL);
    1.81          assert(int_result == SQLITE_OK);
    1.82  
    1.83          if(version != 0) {