JNI-21 moved initialization of global sql manipulation statements from init() code to global initializer JNI-21
authorEdouard Tisserant <edouard@pep-project.org>
Fri, 30 Dec 2016 02:13:13 +0100
branchJNI-21
changeset 151480218e86f9c2
parent 1513 e7f7e42385b5
child 1519 61d4a1a904a4
JNI-21 moved initialization of global sql manipulation statements from init() code to global initializer
src/pEpEngine.c
     1.1 --- a/src/pEpEngine.c	Tue Dec 27 21:13:41 2016 +0100
     1.2 +++ b/src/pEpEngine.c	Fri Dec 30 02:13:13 2016 +0100
     1.3 @@ -10,6 +10,186 @@
     1.4  
     1.5  static int init_count = -1;
     1.6  
     1.7 +// sql manipulation statements
     1.8 +static const char *sql_log = 
     1.9 +    "insert into log (title, entity, description, comment)"
    1.10 +     "values (?1, ?2, ?3, ?4);";
    1.11 +
    1.12 +static const char *sql_trustword = 
    1.13 +    "select id, word from wordlist where lang = lower(?1) "
    1.14 +    "and id = ?2 ;";
    1.15 +
    1.16 +static const char *sql_get_identity =  
    1.17 +    "select fpr, username, comm_type, lang,"
    1.18 +    "   identity.flags | pgp_keypair.flags"
    1.19 +    "   from identity"
    1.20 +    "   join person on id = identity.user_id"
    1.21 +    "   join pgp_keypair on fpr = identity.main_key_id"
    1.22 +    "   join trust on id = trust.user_id"
    1.23 +    "       and pgp_keypair_fpr = identity.main_key_id"
    1.24 +    "   where address = ?1 and identity.user_id = ?2;";
    1.25 +
    1.26 +// Set person, but if already exist, only update.
    1.27 +// if main_key_id already set, don't touch.
    1.28 +static const char *sql_set_person = 
    1.29 +    "insert or replace into person (id, username, lang, main_key_id, device_group)"
    1.30 +    "  values (?1, ?2, ?3,"
    1.31 +    "    (select coalesce((select main_key_id from person "
    1.32 +    "      where id = ?1), upper(replace(?4,' ','')))),"
    1.33 +    "    (select device_group from person where id = ?1)) ;";
    1.34 +
    1.35 +static const char *sql_set_device_group = 
    1.36 +    "update person set device_group = ?1 "
    1.37 +    "where id = '" PEP_OWN_USERID "';";
    1.38 +
    1.39 +static const char *sql_get_device_group = 
    1.40 +    "select device_group from person "
    1.41 +    "where id = '" PEP_OWN_USERID "';";
    1.42 +
    1.43 +static const char *sql_set_pgp_keypair = 
    1.44 +    "insert or replace into pgp_keypair (fpr) "
    1.45 +    "values (upper(replace(?1,' ',''))) ;";
    1.46 +
    1.47 +static const char *sql_set_identity = 
    1.48 +    "insert or replace into identity ("
    1.49 +    " address, main_key_id, "
    1.50 +    " user_id, flags"
    1.51 +    ") values ("
    1.52 +    " ?1,"
    1.53 +    " upper(replace(?2,' ','')),"
    1.54 +    " ?3,"
    1.55 +    // " (select"
    1.56 +    // "   coalesce("
    1.57 +    // "    (select flags from identity"
    1.58 +    // "     where address = ?1 and"
    1.59 +    // "           user_id = ?3),"
    1.60 +    // "    0)"
    1.61 +    // " ) | (?4 & 255)"
    1.62 +    /* set_identity ignores previous flags, and doesn't filter machine flags */
    1.63 +    " ?4"
    1.64 +    ");";
    1.65 +        
    1.66 +static const char *sql_set_identity_flags = 
    1.67 +    "update identity set flags = "
    1.68 +    "    ((?1 & 255) | (select flags from identity"
    1.69 +    "                   where address = ?2 and user_id = ?3)) "
    1.70 +    "where address = ?2 and user_id = ?3 ;";
    1.71 +
    1.72 +static const char *sql_unset_identity_flags = 
    1.73 +    "update identity set flags = "
    1.74 +    "    ( ~(?1 & 255) & (select flags from identity"
    1.75 +    "                   where address = ?2 and user_id = ?3)) "
    1.76 +    "where address = ?2 and user_id = ?3 ;";
    1.77 +
    1.78 +static const char *sql_set_trust =
    1.79 +    "insert or replace into trust (user_id, pgp_keypair_fpr, comm_type) "
    1.80 +    "values (?1, upper(replace(?2,' ','')), ?3) ;";
    1.81 +
    1.82 +static const char *sql_get_trust = 
    1.83 +    "select comm_type from trust where user_id = ?1 "
    1.84 +    "and pgp_keypair_fpr = upper(replace(?2,' ','')) ;";
    1.85 +
    1.86 +static const char *sql_greater_trust_keys = 
    1.87 +    "select pgp_keypair_fpr from trust"
    1.88 +    "  where user_id = ?1"
    1.89 +    "    and comm_type > ?2"
    1.90 +    "    and (select count(*) from identity"
    1.91 +    "           where user_id = identity.user_id"
    1.92 +    "             and pgp_keypair_fpr = identity.main_key_id"
    1.93 +    "        ) = 0;";
    1.94 +
    1.95 +static const char *sql_least_trust = 
    1.96 +    "select min(comm_type) from trust where pgp_keypair_fpr = upper(replace(?1,' ','')) ;";
    1.97 +
    1.98 +static const char *sql_mark_as_compromized = 
    1.99 +    "update trust not indexed set comm_type = 15"
   1.100 +    " where pgp_keypair_fpr = upper(replace(?1,' ','')) ;";
   1.101 +
   1.102 +static const char *sql_crashdump = 
   1.103 +    "select timestamp, title, entity, description, comment"
   1.104 +    " from log order by timestamp desc limit ?1 ;";
   1.105 +
   1.106 +static const char *sql_languagelist = 
   1.107 +    "select i18n_language.lang, name, phrase" 
   1.108 +    " from i18n_language join i18n_token using (lang) where i18n_token.id = 1000;" ;
   1.109 +
   1.110 +static const char *sql_i18n_token = 
   1.111 +    "select phrase from i18n_token where lang = lower(?1) and id = ?2 ;";
   1.112 +
   1.113 +
   1.114 +// blacklist
   1.115 +static const char *sql_blacklist_add = 
   1.116 +    "insert or replace into blacklist_keys (fpr) values (upper(replace(?1,' ',''))) ;"
   1.117 +    "delete from identity where main_key_id = upper(replace(?1,' ','')) ;"
   1.118 +    "delete from pgp_keypair where fpr = upper(replace(?1,' ','')) ;";
   1.119 +
   1.120 +static const char *sql_blacklist_delete =
   1.121 +    "delete from blacklist_keys where fpr = upper(replace(?1,' ','')) ;";
   1.122 +
   1.123 +static const char *sql_blacklist_is_listed = 
   1.124 +    "select count(*) from blacklist_keys where fpr = upper(replace(?1,' ','')) ;";
   1.125 +
   1.126 +static const char *sql_blacklist_retrieve = 
   1.127 +    "select * from blacklist_keys ;";
   1.128 +                
   1.129 +
   1.130 +// Own keys
   1.131 +static const char *sql_own_key_is_listed = 
   1.132 +    "select count(*) from ("
   1.133 +    " select main_key_id from person "
   1.134 +    "   where main_key_id = upper(replace(?1,' ',''))"
   1.135 +    "    and id = '" PEP_OWN_USERID "' "
   1.136 +    " union "
   1.137 +    "  select main_key_id from identity "
   1.138 +    "   where main_key_id = upper(replace(?1,' ',''))"
   1.139 +    "    and user_id = '" PEP_OWN_USERID "' );";
   1.140 +
   1.141 +static const char *sql_own_identities_retrieve =  
   1.142 +    "select address, fpr, username, "
   1.143 +    "   lang, identity.flags | pgp_keypair.flags"
   1.144 +    "   from identity"
   1.145 +    "   join person on id = identity.user_id"
   1.146 +    "   join pgp_keypair on fpr = identity.main_key_id"
   1.147 +    "   join trust on id = trust.user_id"
   1.148 +    "       and pgp_keypair_fpr = identity.main_key_id"
   1.149 +    "   where identity.user_id = '" PEP_OWN_USERID "'"
   1.150 +    "       and (identity.flags & ?1) = 0;";
   1.151 +        
   1.152 +static const char *sql_own_keys_retrieve =  
   1.153 +    "select fpr from own_keys"
   1.154 +    "   natural join identity"
   1.155 +    "   where (identity.flags & ?1) = 0;";
   1.156 +
   1.157 +static const char *sql_set_own_key = 
   1.158 +    "insert or replace into own_keys (address, user_id, fpr)"
   1.159 +    " values (?1, '" PEP_OWN_USERID "', upper(replace(?2,' ','')));";
   1.160 +
   1.161 +
   1.162 +// Sequence
   1.163 +static const char *sql_sequence_value1 = 
   1.164 +    "insert or replace into sequences (name, value, own) "
   1.165 +    "values (?1, "
   1.166 +    "(select coalesce((select value + 1 from sequences "
   1.167 +    "where name = ?1), 1 )), ?2) ; ";
   1.168 +
   1.169 +static const char *sql_sequence_value2 = 
   1.170 +    "select value, own from sequences where name = ?1 ;";
   1.171 +
   1.172 +static const char *sql_sequence_value3 = 
   1.173 +    "update sequences set value = ?2, own = ?3 where name = ?1 ;";
   1.174 +        
   1.175 +// Revocation tracking
   1.176 +static const char *sql_set_revoked =
   1.177 +    "insert or replace into revoked_keys ("
   1.178 +    "    revoked_fpr, replacement_fpr, revocation_date) "
   1.179 +    "values (upper(replace(?1,' ','')),"
   1.180 +    "        upper(replace(?2,' ','')),"
   1.181 +    "        ?3) ;";
   1.182 +        
   1.183 +static const char *sql_get_revoked = 
   1.184 +    "select revoked_fpr, revocation_date from revoked_keys"
   1.185 +    "    where replacement_fpr = upper(replace(?1,' ','')) ;";
   1.186 +
   1.187  static int user_version(void *_version, int count, char **text, char **name)
   1.188  {
   1.189      assert(_version);
   1.190 @@ -27,45 +207,6 @@
   1.191  {
   1.192      PEP_STATUS status = PEP_STATUS_OK;
   1.193      int int_result;
   1.194 -    static const char *sql_log;
   1.195 -    static const char *sql_trustword;
   1.196 -    static const char *sql_get_identity;
   1.197 -    static const char *sql_set_person;
   1.198 -    static const char *sql_set_device_group;
   1.199 -    static const char *sql_get_device_group;
   1.200 -    static const char *sql_set_pgp_keypair;
   1.201 -    static const char *sql_set_identity;
   1.202 -    static const char *sql_set_identity_flags;
   1.203 -    static const char *sql_unset_identity_flags;
   1.204 -    static const char *sql_set_trust;
   1.205 -    static const char *sql_get_trust;
   1.206 -    static const char *sql_greater_trust_keys;
   1.207 -    static const char *sql_least_trust;
   1.208 -    static const char *sql_mark_as_compromized;
   1.209 -    static const char *sql_crashdump;
   1.210 -    static const char *sql_languagelist;
   1.211 -    static const char *sql_i18n_token;
   1.212 -
   1.213 -    // blacklist
   1.214 -    static const char *sql_blacklist_add;
   1.215 -    static const char *sql_blacklist_delete;
   1.216 -    static const char *sql_blacklist_is_listed;
   1.217 -    static const char *sql_blacklist_retrieve;
   1.218 -    
   1.219 -    // Own keys
   1.220 -    static const char *sql_own_key_is_listed;
   1.221 -    static const char *sql_own_identities_retrieve;
   1.222 -    static const char *sql_own_keys_retrieve;
   1.223 -    static const char *sql_set_own_key;
   1.224 -
   1.225 -    // Sequence
   1.226 -    static const char *sql_sequence_value1;
   1.227 -    static const char *sql_sequence_value2;
   1.228 -    static const char *sql_sequence_value3;
   1.229 -
   1.230 -    // Revocation tracking
   1.231 -    static const char *sql_set_revoked;
   1.232 -    static const char *sql_get_revoked;
   1.233      
   1.234      bool in_first = false;
   1.235  
   1.236 @@ -321,150 +462,6 @@
   1.237              assert(int_result == SQLITE_OK);
   1.238          }
   1.239  
   1.240 -        sql_log = "insert into log (title, entity, description, comment)"
   1.241 -                  "values (?1, ?2, ?3, ?4);";
   1.242 -
   1.243 -        sql_get_identity =  "select fpr, username, comm_type, lang,"
   1.244 -                            "   identity.flags | pgp_keypair.flags"
   1.245 -                            "   from identity"
   1.246 -                            "   join person on id = identity.user_id"
   1.247 -                            "   join pgp_keypair on fpr = identity.main_key_id"
   1.248 -                            "   join trust on id = trust.user_id"
   1.249 -                            "       and pgp_keypair_fpr = identity.main_key_id"
   1.250 -                            "   where address = ?1 and identity.user_id = ?2;";
   1.251 -
   1.252 -        sql_trustword = "select id, word from wordlist where lang = lower(?1) "
   1.253 -                       "and id = ?2 ;";
   1.254 -
   1.255 -        // Set person, but if already exist, only update.
   1.256 -        // if main_key_id already set, don't touch.
   1.257 -        sql_set_person = "insert or replace into person (id, username, lang, main_key_id, device_group)"
   1.258 -                         "  values (?1, ?2, ?3,"
   1.259 -                         "    (select coalesce((select main_key_id from person "
   1.260 -                         "      where id = ?1), upper(replace(?4,' ','')))),"
   1.261 -                         "    (select device_group from person where id = ?1)) ;";
   1.262 -
   1.263 -        sql_set_device_group = "update person set device_group = ?1 "
   1.264 -                               "where id = '" PEP_OWN_USERID "';";
   1.265 -
   1.266 -        sql_get_device_group = "select device_group from person "
   1.267 -                               "where id = '" PEP_OWN_USERID "';";
   1.268 -
   1.269 -        sql_set_pgp_keypair = "insert or replace into pgp_keypair (fpr) "
   1.270 -                              "values (upper(replace(?1,' ',''))) ;";
   1.271 -
   1.272 -        sql_set_identity = "insert or replace into identity ("
   1.273 -                           " address, main_key_id, "
   1.274 -                           " user_id, flags"
   1.275 -                           ") values ("
   1.276 -                           " ?1,"
   1.277 -                           " upper(replace(?2,' ','')),"
   1.278 -                           " ?3,"
   1.279 -                           // " (select"
   1.280 -                           // "   coalesce("
   1.281 -                           // "    (select flags from identity"
   1.282 -                           // "     where address = ?1 and"
   1.283 -                           // "           user_id = ?3),"
   1.284 -                           // "    0)"
   1.285 -                           // " ) | (?4 & 255)"
   1.286 -                           /* set_identity ignores previous flags, and doesn't filter machine flags */
   1.287 -                           " ?4"
   1.288 -                           ");";
   1.289 -        
   1.290 -        sql_set_identity_flags = "update identity set flags = "
   1.291 -                                 "    ((?1 & 255) | (select flags from identity"
   1.292 -                                 "                   where address = ?2 and user_id = ?3)) "
   1.293 -                                 "where address = ?2 and user_id = ?3 ;";
   1.294 -
   1.295 -        sql_unset_identity_flags = 
   1.296 -                                 "update identity set flags = "
   1.297 -                                 "    ( ~(?1 & 255) & (select flags from identity"
   1.298 -                                 "                   where address = ?2 and user_id = ?3)) "
   1.299 -                                 "where address = ?2 and user_id = ?3 ;";
   1.300 -
   1.301 -        sql_set_trust = "insert or replace into trust (user_id, pgp_keypair_fpr, comm_type) "
   1.302 -                        "values (?1, upper(replace(?2,' ','')), ?3) ;";
   1.303 -
   1.304 -        sql_get_trust = "select comm_type from trust where user_id = ?1 "
   1.305 -                        "and pgp_keypair_fpr = upper(replace(?2,' ','')) ;";
   1.306 -
   1.307 -        sql_greater_trust_keys = "select pgp_keypair_fpr from trust"
   1.308 -                                 "  where user_id = ?1"
   1.309 -                                 "    and comm_type > ?2"
   1.310 -                                 "    and (select count(*) from identity"
   1.311 -                                 "           where user_id = identity.user_id"
   1.312 -                                 "             and pgp_keypair_fpr = identity.main_key_id"
   1.313 -                                 "        ) = 0;";
   1.314 -
   1.315 -        sql_least_trust = "select min(comm_type) from trust where pgp_keypair_fpr = upper(replace(?1,' ','')) ;";
   1.316 -
   1.317 -        sql_mark_as_compromized = "update trust not indexed set comm_type = 15"
   1.318 -                                  " where pgp_keypair_fpr = upper(replace(?1,' ','')) ;";
   1.319 -
   1.320 -        sql_crashdump = "select timestamp, title, entity, description, comment"
   1.321 -                        " from log order by timestamp desc limit ?1 ;";
   1.322 -
   1.323 -        sql_languagelist = "select i18n_language.lang, name, phrase from i18n_language join i18n_token using (lang) where i18n_token.id = 1000;" ;
   1.324 -
   1.325 -        sql_i18n_token = "select phrase from i18n_token where lang = lower(?1) and id = ?2 ;";
   1.326 -
   1.327 -        // blacklist
   1.328 -
   1.329 -        sql_blacklist_add = "insert or replace into blacklist_keys (fpr) values (upper(replace(?1,' ',''))) ;"
   1.330 -                            "delete from identity where main_key_id = upper(replace(?1,' ','')) ;"
   1.331 -                            "delete from pgp_keypair where fpr = upper(replace(?1,' ','')) ;";
   1.332 -
   1.333 -        sql_blacklist_delete = "delete from blacklist_keys where fpr = upper(replace(?1,' ','')) ;";
   1.334 -
   1.335 -        sql_blacklist_is_listed = "select count(*) from blacklist_keys where fpr = upper(replace(?1,' ','')) ;";
   1.336 -
   1.337 -        sql_blacklist_retrieve = "select * from blacklist_keys ;";
   1.338 -                
   1.339 -        // Own keys
   1.340 -        
   1.341 -        sql_own_key_is_listed = "select count(*) from ("
   1.342 -                                " select main_key_id from person "
   1.343 -                                "   where main_key_id = upper(replace(?1,' ',''))"
   1.344 -                                "    and id = '" PEP_OWN_USERID "' "
   1.345 -                                " union "
   1.346 -                                "  select main_key_id from identity "
   1.347 -                                "   where main_key_id = upper(replace(?1,' ',''))"
   1.348 -                                "    and user_id = '" PEP_OWN_USERID "' );";
   1.349 -
   1.350 -        sql_own_identities_retrieve =  
   1.351 -                            "select address, fpr, username, "
   1.352 -                            "   lang, identity.flags | pgp_keypair.flags"
   1.353 -                            "   from identity"
   1.354 -                            "   join person on id = identity.user_id"
   1.355 -                            "   join pgp_keypair on fpr = identity.main_key_id"
   1.356 -                            "   join trust on id = trust.user_id"
   1.357 -                            "       and pgp_keypair_fpr = identity.main_key_id"
   1.358 -                            "   where identity.user_id = '" PEP_OWN_USERID "'"
   1.359 -                            "       and (identity.flags & ?1) = 0;";
   1.360 -        
   1.361 -        sql_own_keys_retrieve =  
   1.362 -                            "select fpr from own_keys"
   1.363 -                            "   natural join identity"
   1.364 -                            "   where (identity.flags & ?1) = 0;";
   1.365 -
   1.366 -        sql_set_own_key = "insert or replace into own_keys (address, user_id, fpr)"
   1.367 -                          " values (?1, '" PEP_OWN_USERID "', upper(replace(?2,' ','')));";
   1.368 -
   1.369 -        sql_sequence_value1 = "insert or replace into sequences (name, value, own) "
   1.370 -                              "values (?1, "
   1.371 -                              "(select coalesce((select value + 1 from sequences "
   1.372 -                              "where name = ?1), 1 )), ?2) ; ";
   1.373 -        sql_sequence_value2 = "select value, own from sequences where name = ?1 ;";
   1.374 -        sql_sequence_value3 = "update sequences set value = ?2, own = ?3 where name = ?1 ;";
   1.375 -        
   1.376 -        sql_set_revoked =     "insert or replace into revoked_keys ("
   1.377 -                              "    revoked_fpr, replacement_fpr, revocation_date) "
   1.378 -                              "values (upper(replace(?1,' ','')),"
   1.379 -                              "        upper(replace(?2,' ','')),"
   1.380 -                              "        ?3) ;";
   1.381 -        
   1.382 -        sql_get_revoked =     "select revoked_fpr, revocation_date from revoked_keys"
   1.383 -                              "    where replacement_fpr = upper(replace(?1,' ','')) ;";
   1.384      }
   1.385  
   1.386      int_result = sqlite3_prepare_v2(_session->db, sql_log,