# # # patch "database.cc" # from [f254a5b0227aeba143b95881a09d98e8754ff64d] # to [91deda5cc07e67397154a3a8b640b81434b21e07] # # patch "database.hh" # from [52aca5e21d51c9df75f502cc598035e6f97126b5] # to [6d050136a7c32a94ed5edf6ca00d0a11c6c676a7] # # patch "migrate_schema.cc" # from [f987fd101b23760fee0b5a5cd21ad955e1d6bc1d] # to [8c29f6bc4c04b296ae54eccb75bab127c8a571a3] # # patch "schema.sql" # from [0137cb35fa501da7ab0017c4a76fb09eeae6013b] # to [1b887d06f3a17a9fb3c7141c0842b11e5c589142] # ============================================================ --- database.cc f254a5b0227aeba143b95881a09d98e8754ff64d +++ database.cc 91deda5cc07e67397154a3a8b640b81434b21e07 @@ -531,7 +531,7 @@ database::check_certs_not_by_hash() { results res; imp->fetch(res, one_col, any_rows, - query("SELECT 1 FROM revision_certs LIMIT 1")); + query("SELECT 1 FROM revision_certs_by_keyhash LIMIT 1")); E(res.empty(), origin::user, F("this database already has certs linked by key hash")); } @@ -550,8 +550,10 @@ database_impl::check_format() bool have_rosters = !res.empty(); fetch(res, one_col, any_rows, query("SELECT 1 FROM heights LIMIT 1")); bool have_heights = !res.empty(); - fetch(res, one_col, any_rows, query("SELECT 1 FROM revision_certs_by_keyname LIMIT 1")); + fetch(res, one_col, any_rows, query("SELECT 1 FROM revision_certs LIMIT 1")); bool have_certs_by_keyname = !res.empty(); + fetch(res, one_col, any_rows, query("SELECT 1 FROM revision_certs_by_keyhash LIMIT 1")); + bool have_certs_by_keyhash = !res.empty(); if (!have_manifests) @@ -565,9 +567,9 @@ database_impl::check_format() "run '%s db regenerate_caches' to restore use of this database") % filename % prog_name); - E(!have_certs_by_keyname, origin::no_fault, + E(!have_certs_by_keyname || have_certs_by_keyhash, origin::no_fault, F("database %s has certs linked to keys by key name.\n" - "Please run '%s db migrate_certs_to_key_hashes' to have" + "Please run '%s db migrate_certs_to_key_hashes' to have " "certs linked to keys by key hashes instead") % filename % prog_name); } @@ -950,7 +952,7 @@ database::info(ostream & out, bool analy counts.push_back(imp->count("file_deltas")); counts.push_back(imp->count("revisions")); counts.push_back(imp->count("revision_ancestry")); - counts.push_back(imp->count("revision_certs")); + counts.push_back(imp->count("revision_certs_by_keyhash")); { results res; @@ -988,7 +990,7 @@ database::info(ostream & out, bool analy bytes.push_back(imp->space("revisions", "length(id) + length(data)", total)); bytes.push_back(imp->space("revision_ancestry", "length(parent) + length(child)", total)); - bytes.push_back(imp->space("revision_certs", + bytes.push_back(imp->space("revision_certs_by_keyhash", "length(hash) + length(id) + length(name)" "+ length(value) + length(keypair_id)" "+ length(signature)", total)); @@ -1063,7 +1065,7 @@ database::info(ostream & out, bool analy vector certs; L(FL("fetching revision dates")); - imp->get_certs(date_cert_name, certs, "revision_certs"); + imp->get_certs(date_cert_name, certs, "revision_certs_by_keyhash"); L(FL("analyzing revision dates")); rev_date::iterator d; @@ -2800,7 +2802,7 @@ database::delete_existing_revs_and_certs { imp->execute(query("DELETE FROM revisions")); imp->execute(query("DELETE FROM revision_ancestry")); - imp->execute(query("DELETE FROM revision_certs")); + imp->execute(query("DELETE FROM revision_certs_by_keyhash")); } void @@ -2841,7 +2843,7 @@ database::delete_existing_rev_and_certs( L(FL("Killing revision %s locally") % rid); // Kill the certs, ancestry, and revision. - imp->execute(query("DELETE from revision_certs WHERE revision_id = ?") + imp->execute(query("DELETE from revision_certs_by_keyhash WHERE revision_id = ?") % blob(rid.inner()())); imp->cert_stamper.note_change(); @@ -2862,7 +2864,7 @@ database::delete_branch_named(cert_value database::delete_branch_named(cert_value const & branch) { L(FL("Deleting all references to branch %s") % branch); - imp->execute(query("DELETE FROM revision_certs WHERE name='branch' AND value =?") + imp->execute(query("DELETE FROM revision_certs_by_keyhash WHERE name='branch' AND value =?") % blob(branch())); imp->cert_stamper.note_change(); imp->execute(query("DELETE FROM branch_epochs WHERE branch=?") @@ -2874,7 +2876,7 @@ database::delete_tag_named(cert_value co database::delete_tag_named(cert_value const & tag) { L(FL("Deleting all references to tag %s") % tag); - imp->execute(query("DELETE FROM revision_certs WHERE name='tag' AND value =?") + imp->execute(query("DELETE FROM revision_certs_by_keyhash WHERE name='tag' AND value =?") % blob(tag())); imp->cert_stamper.note_change(); } @@ -3328,7 +3330,7 @@ database::revision_cert_exists(cert cons bool database::revision_cert_exists(cert const & cert) { - return imp->cert_exists(cert, "revision_certs"); + return imp->cert_exists(cert, "revision_certs_by_keyhash"); } bool @@ -3349,7 +3351,7 @@ database::put_revision_cert(cert const & return false; } - imp->put_cert(cert, "revision_certs"); + imp->put_cert(cert, "revision_certs_by_keyhash"); imp->cert_stamper.note_change(); return true; } @@ -3364,7 +3366,7 @@ database::get_revision_cert_nobranch_ind results res; imp->fetch(res, 3, any_rows, query("SELECT hash, revision_id, keypair_id " - "FROM 'revision_certs' WHERE name != 'branch'")); + "FROM revision_certs_by_keyhash WHERE name != 'branch'")); idx.clear(); idx.reserve(res.size()); @@ -3380,7 +3382,7 @@ database::get_revision_certs(vector & certs) { - imp->get_certs(certs, "revision_certs"); + imp->get_certs(certs, "revision_certs_by_keyhash"); return imp->cert_stamper.get_indicator(); } @@ -3388,7 +3390,7 @@ database::get_revision_certs(cert_name c database::get_revision_certs(cert_name const & name, vector & certs) { - imp->get_certs(name, certs, "revision_certs"); + imp->get_certs(name, certs, "revision_certs_by_keyhash"); return imp->cert_stamper.get_indicator(); } @@ -3397,7 +3399,7 @@ database::get_revision_certs(revision_id cert_name const & name, vector & certs) { - imp->get_certs(id.inner(), name, certs, "revision_certs"); + imp->get_certs(id.inner(), name, certs, "revision_certs_by_keyhash"); return imp->cert_stamper.get_indicator(); } @@ -3407,7 +3409,7 @@ database::get_revision_certs(revision_id cert_value const & val, vector & certs) { - imp->get_certs(id.inner(), name, val, certs, "revision_certs"); + imp->get_certs(id.inner(), name, val, certs, "revision_certs_by_keyhash"); return imp->cert_stamper.get_indicator(); } @@ -3418,7 +3420,7 @@ database::get_revisions_with_cert(cert_n { revisions.clear(); results res; - query q("SELECT revision_id FROM revision_certs WHERE name = ? AND value = ?"); + query q("SELECT revision_id FROM revision_certs_by_keyhash WHERE name = ? AND value = ?"); imp->fetch(res, one_col, any_rows, q % text(name()) % blob(val())); for (results::const_iterator i = res.begin(); i != res.end(); ++i) revisions.insert(revision_id((*i)[0], origin::database)); @@ -3430,7 +3432,7 @@ database::get_revision_certs(cert_name c cert_value const & val, vector & certs) { - imp->get_certs(name, val, certs, "revision_certs"); + imp->get_certs(name, val, certs, "revision_certs_by_keyhash"); return imp->cert_stamper.get_indicator(); } @@ -3438,7 +3440,7 @@ database::get_revision_certs(revision_id database::get_revision_certs(revision_id const & id, vector & certs) { - imp->get_certs(id.inner(), certs, "revision_certs"); + imp->get_certs(id.inner(), certs, "revision_certs_by_keyhash"); return imp->cert_stamper.get_indicator(); } @@ -3457,7 +3459,7 @@ database::get_revision_certs(revision_id results res; imp->fetch(res, one_col, any_rows, query("SELECT hash " - "FROM revision_certs " + "FROM revision_certs_by_keyhash " "WHERE revision_id = ?") % blob(ident.inner()())); ids.clear(); @@ -3474,7 +3476,7 @@ database::get_revision_cert(id const & h vector certs; imp->fetch(res, 5, one_row, query("SELECT revision_id, name, value, keypair_id, signature " - "FROM revision_certs " + "FROM revision_certs_by_keyhash " "WHERE hash = ?") % blob(hash())); imp->results_to_certs(res, certs); @@ -3489,7 +3491,7 @@ database::revision_cert_exists(revision_ vector certs; imp->fetch(res, one_col, any_rows, query("SELECT revision_id " - "FROM revision_certs " + "FROM revision_certs_by_keyhash " "WHERE hash = ?") % blob(hash.inner()())); I(res.empty() || res.size() == 1); @@ -3744,7 +3746,7 @@ database::select_cert(string const & cer completions.clear(); imp->fetch(res, 1, any_rows, - query("SELECT DISTINCT revision_id FROM revision_certs WHERE name = ?") + query("SELECT DISTINCT revision_id FROM revision_certs_by_keyhash WHERE name = ?") % text(certname)); for (size_t i = 0; i < res.size(); ++i) @@ -3759,7 +3761,7 @@ database::select_cert(string const & cer completions.clear(); imp->fetch(res, 1, any_rows, - query("SELECT DISTINCT revision_id FROM revision_certs" + query("SELECT DISTINCT revision_id FROM revision_certs_by_keyhash" " WHERE name = ? AND CAST(value AS TEXT) GLOB ?") % text(certname) % text(certvalue)); @@ -3777,7 +3779,7 @@ database::select_author_tag_or_branch(st string pattern = partial + "*"; imp->fetch(res, 1, any_rows, - query("SELECT DISTINCT revision_id FROM revision_certs" + query("SELECT DISTINCT revision_id FROM revision_certs_by_keyhash" " WHERE (name=? OR name=? OR name=?)" " AND CAST(value AS TEXT) GLOB ?") % text(author_cert_name()) % text(tag_cert_name()) @@ -3795,7 +3797,7 @@ database::select_date(string const & dat completions.clear(); query q; - q.sql_cmd = ("SELECT DISTINCT revision_id FROM revision_certs " + q.sql_cmd = ("SELECT DISTINCT revision_id FROM revision_certs_by_keyhash " "WHERE name = ? AND CAST(value AS TEXT) "); q.sql_cmd += comparison; q.sql_cmd += " ?"; @@ -3942,7 +3944,7 @@ database::get_branches(vector & database::get_branches(vector & names) { results res; - query q("SELECT DISTINCT value FROM revision_certs WHERE name = ?"); + query q("SELECT DISTINCT value FROM revision_certs_by_keyhash WHERE name = ?"); string cert_name = "branch"; imp->fetch(res, one_col, any_rows, q % text(cert_name)); for (size_t i = 0; i < res.size(); ++i) @@ -3957,7 +3959,7 @@ database::get_branches(globish const & g vector & names) { results res; - query q("SELECT DISTINCT value FROM revision_certs WHERE name = ?"); + query q("SELECT DISTINCT value FROM revision_certs_by_keyhash WHERE name = ?"); string cert_name = "branch"; imp->fetch(res, one_col, any_rows, q % text(cert_name)); for (size_t i = 0; i < res.size(); ++i) ============================================================ --- database.hh 52aca5e21d51c9df75f502cc598035e6f97126b5 +++ database.hh 6d050136a7c32a94ed5edf6ca00d0a11c6c676a7 @@ -415,7 +415,6 @@ public: // for changesetify, rosterify void delete_existing_revs_and_certs(); void delete_existing_manifests(); - void delete_certs_with_keynames(); void get_manifest_certs(manifest_id const & id, std::vector & certs); void get_manifest_certs(cert_name const & name, std::vector & certs); ============================================================ --- migrate_schema.cc f987fd101b23760fee0b5a5cd21ad955e1d6bc1d +++ migrate_schema.cc 8c29f6bc4c04b296ae54eccb75bab127c8a571a3 @@ -702,18 +702,26 @@ char const migrate_certs_to_key_hash[] = ; char const migrate_certs_to_key_hash[] = - "ALTER TABLE revision_certs RENAME TO revision_certs_by_keyname;" - "ALTER INDEX revision_certs__id RENAME TO revision_certs_by_keyname__id;" - "CREATE TABLE revision_certs_by_keyname" + "ALTER TABLE public_keys rename to public_keys_tmp;\n" + "CREATE TABLE public_keys\n" + " ( id primary key, -- hash of remaining fields separated by \":\"\n" + " name not null, -- key identifier chosen by user\n" + " keydata not null -- RSA public params\n" + " );\n" + "INSERT INTO public_keys (id, name, keydata)" + " select hash, id, keydata from public_keys_tmp;\n" + "DROP TABLE public_keys_tmp;\n" + + "CREATE TABLE revision_certs_by_keyhash\n" " ( hash not null unique, -- hash of remaining fields separated by \":\"\n" - " revision_id not null, -- joins with revisions.id" + " revision_id not null, -- joins with revisions.id\n" " name not null, -- opaque string chosen by user\n" " value not null, -- opaque blob\n" " keypair_id not null, -- joins with public_keys.id\n" " signature not null, -- RSA/SHA1 signature of \"address@hidden:val]\"\n" - " unique(name, value, revision_id, keypair_id, signature)" - " );" - "CREATE INDEX revision_certs__revision_id ON revision_certs (revision_id);" + " unique(name, value, revision_id, keypair_id, signature)\n" + " );\n" + "CREATE INDEX revision_certs_by_keyhash__revision_id ON revision_certs_by_keyhash (revision_id);" ; // these must be listed in order so that ones listed earlier override ones @@ -804,7 +812,7 @@ const migration_event migration_events[] // The last entry in this table should always be the current // schema ID, with 0 for the migrators. - { "e52bb32ddec599ae6aca885d2c30f2429a77bf6c", 0, 0, upgrade_none } + { "f5de2b122a2594d39756517eaf40ca8723b3ea44", 0, 0, upgrade_none } }; const size_t n_migration_events = (sizeof migration_events / sizeof migration_events[0]); ============================================================ --- schema.sql 0137cb35fa501da7ab0017c4a76fb09eeae6013b +++ schema.sql 1b887d06f3a17a9fb3c7141c0842b11e5c589142 @@ -90,7 +90,7 @@ CREATE TABLE public_keys keydata not null -- RSA public params ); -CREATE TABLE revision_certs +CREATE TABLE revision_certs_by_keyhash ( hash not null unique, -- hash of remaining fields separated by ":" revision_id not null, -- joins with revisions.id @@ -101,7 +101,7 @@ CREATE TABLE revision_certs unique(name, value, revision_id, keypair_id, signature) ); -CREATE INDEX revision_certs__revision_id ON revision_certs (revision_id); +CREATE INDEX revision_certs_by_keyhash__revision_id ON revision_certs_by_keyhash (revision_id); CREATE TABLE branch_epochs ( @@ -150,7 +150,7 @@ CREATE TABLE manifest_certs unique(name, id, value, keypair, signature) ); -CREATE TABLE revision_certs_by_keyname +CREATE TABLE revision_certs ( hash not null unique, -- hash of remaining fields separated by ":" id not null, -- joins with revisions.id @@ -160,6 +160,6 @@ CREATE TABLE revision_certs_by_keyname signature not null, -- RSA/SHA1 signature of "address@hidden:val]" unique(name, value, id, keypair, signature) ); -CREATE INDEX revision_certs_by_keyname__id ON revision_certs_by_keyname (id); +CREATE INDEX revision_certs__id ON revision_certs (id); COMMIT;