ENGINE-538: fixed sqlite >= 3.25 forced errors and included code to repair damaged DBs from this external lib upgrade. Harumph. DDL version upgraded. sync
authorKrista 'DarthMama' Bennett <krista@pep.foundation>
Mon, 08 Apr 2019 10:19:29 +0200
branchsync
changeset 3463d8f0a39103fe
parent 3462 652172b1b367
child 3465 9008577e8f67
ENGINE-538: fixed sqlite >= 3.25 forced errors and included code to repair damaged DBs from this external lib upgrade. Harumph. DDL version upgraded.
src/pEpEngine.c
     1.1 --- a/src/pEpEngine.c	Sun Apr 07 23:37:54 2019 +0200
     1.2 +++ b/src/pEpEngine.c	Mon Apr 08 10:19:29 2019 +0200
     1.3 @@ -594,6 +594,184 @@
     1.4      return retval;
     1.5  }
     1.6  
     1.7 +PEP_STATUS repair_altered_tables(PEP_SESSION session) {
     1.8 +    PEP_STATUS status = PEP_STATUS_OK;
     1.9 +    
    1.10 +    const unsigned int _PEP_MAX_AFFECTED = 5;
    1.11 +    char** table_names = calloc(_PEP_MAX_AFFECTED, sizeof(char*));
    1.12 +    if (!table_names)
    1.13 +        return PEP_OUT_OF_MEMORY;
    1.14 +
    1.15 +    const char* sql_query = "select tbl_name from sqlite_master WHERE sql LIKE '%REFERENCES%' AND sql LIKE '%_old%';";
    1.16 +    sqlite3_stmt *stmt; 
    1.17 +    sqlite3_prepare_v2(session->db, sql_query, -1, &stmt, NULL);
    1.18 +    int i = 0;
    1.19 +    int int_result = 0;
    1.20 +    while ((int_result = sqlite3_step(stmt)) == SQLITE_ROW && i < _PEP_MAX_AFFECTED) {
    1.21 +        table_names[i++] = strdup((const char*)(sqlite3_column_text(stmt, 0)));
    1.22 +    }
    1.23 +    
    1.24 +    sqlite3_finalize(stmt);      
    1.25 +
    1.26 +    if ((int_result != SQLITE_DONE && int_result != SQLITE_OK) || i > (_PEP_MAX_AFFECTED + 1)) {
    1.27 +        status = PEP_UNKNOWN_DB_ERROR;
    1.28 +        goto pEp_free;
    1.29 +    }
    1.30 +        
    1.31 +    for (i = 0; i < _PEP_MAX_AFFECTED; i++) {
    1.32 +        const char* table_name = table_names[i];
    1.33 +        if (!table_name)
    1.34 +            break;
    1.35 +            
    1.36 +        if (strcmp(table_name, "identity") == 0) {
    1.37 +            int_result = sqlite3_exec(session->db,
    1.38 +                "PRAGMA foreign_keys=off;\n"
    1.39 +                "BEGIN TRANSACTION;\n"
    1.40 +                "create table _identity_new (\n"
    1.41 +                "   address text,\n"
    1.42 +                "   user_id text\n"
    1.43 +                "       references person (id)\n"
    1.44 +                "       on delete cascade on update cascade,\n"
    1.45 +                "   main_key_id text\n"
    1.46 +                "       references pgp_keypair (fpr)\n"
    1.47 +                "       on delete set null,\n"
    1.48 +                "   comment text,\n"
    1.49 +                "   flags integer default 0,\n"
    1.50 +                "   is_own integer default 0,\n"
    1.51 +                "   timestamp integer default (datetime('now')),\n"
    1.52 +                "   primary key (address, user_id)\n"
    1.53 +                ");\n"
    1.54 +                "INSERT INTO _identity_new SELECT * from identity;\n"
    1.55 +                "DROP TABLE identity;\n"
    1.56 +                "ALTER TABLE _identity_new RENAME TO identity;\n"
    1.57 +                "COMMIT;\n"
    1.58 +                "PRAGMA foreign_keys=on;"
    1.59 +                ,
    1.60 +                NULL,
    1.61 +                NULL,
    1.62 +                NULL
    1.63 +            );
    1.64 +            assert(int_result == PEP_STATUS_OK);
    1.65 +        }
    1.66 +        else if (strcmp(table_name, "trust") == 0) {
    1.67 +            int_result = sqlite3_exec(session->db,
    1.68 +                "PRAGMA foreign_keys=off;\n"
    1.69 +                "BEGIN TRANSACTION;\n"
    1.70 +                "create table _trust_new (\n"
    1.71 +                "   user_id text not null\n"
    1.72 +                "       references person (id)\n"
    1.73 +                "       on delete cascade on update cascade,\n"
    1.74 +                "   pgp_keypair_fpr text not null\n"
    1.75 +                "       references pgp_keypair (fpr)\n"
    1.76 +                "       on delete cascade,\n"
    1.77 +                "   comm_type integer not null,\n"
    1.78 +                "   comment text,\n"
    1.79 +                "   primary key (user_id, pgp_keypair_fpr)\n"                
    1.80 +                ");\n"
    1.81 +                "INSERT INTO _trust_new SELECT * from trust;\n"
    1.82 +                "DROP TABLE trust;\n"
    1.83 +                "ALTER TABLE _trust_new RENAME TO trust;\n"
    1.84 +                "COMMIT;\n"
    1.85 +                "PRAGMA foreign_keys=on;"
    1.86 +                ,
    1.87 +                NULL,
    1.88 +                NULL,
    1.89 +                NULL
    1.90 +            );             
    1.91 +            assert(int_result == PEP_STATUS_OK);                       
    1.92 +        }
    1.93 +        else if (strcmp(table_name, "alternate_user_id") == 0) {
    1.94 +            int_result = sqlite3_exec(session->db,
    1.95 +                "PRAGMA foreign_keys=off;\n"
    1.96 +                "BEGIN TRANSACTION;\n"
    1.97 +                "create table _alternate_user_id_new (\n"
    1.98 +                "    default_id text references person (id)\n"
    1.99 +                "       on delete cascade on update cascade,\n"
   1.100 +                "    alternate_id text primary key\n"
   1.101 +                ");\n"
   1.102 +                "INSERT INTO _alternate_user_id_new SELECT * from alternate_user_id;\n"
   1.103 +                "DROP TABLE alternate_user_id;\n"
   1.104 +                "ALTER TABLE _alternate_user_id_new RENAME TO alternate_user_id;\n"
   1.105 +                "COMMIT;\n"
   1.106 +                "PRAGMA foreign_keys=on;"                
   1.107 +                ,
   1.108 +                NULL,
   1.109 +                NULL,
   1.110 +                NULL
   1.111 +            );
   1.112 +            assert(int_result == PEP_STATUS_OK);
   1.113 +        }
   1.114 +        else if (strcmp(table_name, "revocation_contact_list") == 0) {
   1.115 +            int_result = sqlite3_exec(session->db,
   1.116 +                "PRAGMA foreign_keys=off;\n"
   1.117 +                "BEGIN TRANSACTION;\n"
   1.118 +                "create table _revocation_contact_list_new (\n"
   1.119 +                "   fpr text not null references pgp_keypair (fpr)\n"
   1.120 +                "       on delete cascade,\n"
   1.121 +                "   contact_id text not null references person (id)\n"
   1.122 +                "       on delete cascade on update cascade,\n"
   1.123 +                "   timestamp integer default (datetime('now')),\n"
   1.124 +                "   PRIMARY KEY(fpr, contact_id)\n"            
   1.125 +                ");\n"
   1.126 +                "INSERT INTO _revocation_contact_list_new SELECT * from revocation_contact_list;\n"
   1.127 +                "DROP TABLE revocation_contact_list;\n"
   1.128 +                "ALTER TABLE _revocation_contact_list_new RENAME TO revocation_contact_list;\n"
   1.129 +                "COMMIT;\n"
   1.130 +                "PRAGMA foreign_keys=on;"                
   1.131 +                ,
   1.132 +                NULL,
   1.133 +                NULL,
   1.134 +                NULL
   1.135 +            );      
   1.136 +            assert(int_result == PEP_STATUS_OK);                              
   1.137 +        }
   1.138 +        else if (strcmp(table_name, "social_graph")) {
   1.139 +            int_result = sqlite3_exec(session->db,
   1.140 +                "PRAGMA foreign_keys=off;\n"
   1.141 +                "BEGIN TRANSACTION;\n"
   1.142 +                "create table _social_new (\n"
   1.143 +                "    own_userid text,\n"
   1.144 +                "    own_address text,\n"
   1.145 +                "    contact_userid text,\n"
   1.146 +                "    CONSTRAINT fk_own_identity\n"
   1.147 +                "       FOREIGN KEY(own_address, own_userid)\n" 
   1.148 +                "       REFERENCES identity(address, user_id)\n"
   1.149 +                "       ON DELETE CASCADE ON UPDATE CASCADE\n"
   1.150 +                ");\n"
   1.151 +                "INSERT INTO _social_graph_new SELECT * from social_graph;\n"
   1.152 +                "DROP TABLE social_graph;\n"
   1.153 +                "ALTER TABLE _social_graph_new RENAME TO social_graph;\n"
   1.154 +                "COMMIT;\n"
   1.155 +                "PRAGMA foreign_keys=on;"                
   1.156 +                ,
   1.157 +                NULL,
   1.158 +                NULL,
   1.159 +                NULL
   1.160 +            );
   1.161 +            assert(int_result == PEP_STATUS_OK);                                    
   1.162 +        }        
   1.163 +    }
   1.164 +    
   1.165 +    int_result = sqlite3_exec(
   1.166 +        session->db,
   1.167 +        "PRAGMA foreign_key_check;\n"
   1.168 +        ,
   1.169 +        NULL,
   1.170 +        NULL,
   1.171 +        NULL
   1.172 +    );
   1.173 +    assert(int_result == SQLITE_OK);
   1.174 +
   1.175 +pEp_free:
   1.176 +    for (i = 0; i < _PEP_MAX_AFFECTED; i++) {
   1.177 +        if (table_names[i])
   1.178 +            free(table_names[i]);
   1.179 +        else
   1.180 +            break;
   1.181 +    }
   1.182 +    free(table_names);
   1.183 +    return status;
   1.184 +}
   1.185  void errorLogCallback(void *pArg, int iErrCode, const char *zMsg){
   1.186    fprintf(stderr, "(%d) %s\n", iErrCode, zMsg);
   1.187  }
   1.188 @@ -726,7 +904,7 @@
   1.189      sqlite3_busy_timeout(_session->system_db, 1000);
   1.190  
   1.191  // increment this when patching DDL
   1.192 -#define _DDL_USER_VERSION "10"
   1.193 +#define _DDL_USER_VERSION "11"
   1.194  
   1.195      if (in_first) {
   1.196  
   1.197 @@ -1140,9 +1318,9 @@
   1.198                      ");\n"
   1.199                      "INSERT INTO _identity_new (address, user_id, main_key_id, "
   1.200                      "                      comment, flags, is_own) "
   1.201 -                    "   SELECT _identity_old.address, _identity_old.user_id, "
   1.202 -                    "          _identity_old.main_key_id, _identity_old.comment, "
   1.203 -                    "          _identity_old.flags, _identity_old.is_own "
   1.204 +                    "   SELECT identity.address, identity.user_id, "
   1.205 +                    "          identity.main_key_id, identity.comment, "
   1.206 +                    "          identity.flags, identity.is_own "
   1.207                      "   FROM identity "
   1.208                      "   WHERE 1;\n"
   1.209                      "DROP TABLE identity;\n"
   1.210 @@ -1239,6 +1417,12 @@
   1.211                  );
   1.212                  assert(int_result == SQLITE_OK);
   1.213              }
   1.214 +            if (version < 11) {
   1.215 +                status = repair_altered_tables(_session);
   1.216 +                assert(status == PEP_STATUS_OK);
   1.217 +                if (status != PEP_STATUS_OK)
   1.218 +                    return status;
   1.219 +            }
   1.220          }        
   1.221          else { 
   1.222              // Version from DB was 0, it means this is initial setup.