#
#
# patch "cmd_db.cc"
#  from [d70d3eb0c456fc31de08cd9f16c487fc02d2e3fe]
#    to [0ff11a2e9a2a988c5dd1398b5c65e80837556068]
# 
# patch "database.cc"
#  from [45a853c398d98224ff998f8769cf4dcbbb0a7d40]
#    to [29ad223f150112f10bbc9b0769208f0cc8d2913a]
# 
# patch "database.hh"
#  from [e3de5162c6654e1355444eb0d6c4d8bd6c0fe63b]
#    to [db606c2e07d1b60a50cb8a95118c5a62141c662f]
# 
# patch "migrate_ancestry.cc"
#  from [c02341580f9e65387807dc8f1ae3874d26e81198]
#    to [60ea5e154f134f633e1405cbd1d312d46a68464c]
# 
# patch "migrate_schema.cc"
#  from [ffd51091a60a67132b1fd88514fd50d04f0e17f8]
#    to [a53016bd522dc676d9df6767369e5d28bf526868]
# 
# patch "schema.sql"
#  from [b933498af43c8221933f2afaa6b2b9c6a527c6e0]
#    to [0137cb35fa501da7ab0017c4a76fb09eeae6013b]
#
============================================================
--- cmd_db.cc	d70d3eb0c456fc31de08cd9f16c487fc02d2e3fe
+++ cmd_db.cc	0ff11a2e9a2a988c5dd1398b5c65e80837556068
@@ -283,6 +283,22 @@ CMD(db_rosterify, "rosterify", "", CMD_R
                                                    app.opts.attrs_to_drop);
 }
 
+CMD(db_migrate_certs_to_key_hashes,
+    "migrate_certs_to_key_hashes", "", CMD_REF(db), "",
+    N_("Converts the database to link certs to keys by hash"),
+    "",
+    options::opts::none)
+{
+  database db(app);
+  key_store keys(app);
+
+  E(args.size() == 0, origin::user,
+    F("no arguments needed"));
+
+  db.ensure_open_for_format_changes();
+  db.check_certs_not_by_hash();
+}
+
 CMD(db_regenerate_caches, "regenerate_caches", "", CMD_REF(db), "",
     N_("Regenerates the caches stored in the database"),
     "",
============================================================
--- database.cc	45a853c398d98224ff998f8769cf4dcbbb0a7d40
+++ database.cc	29ad223f150112f10bbc9b0769208f0cc8d2913a
@@ -514,6 +514,16 @@ void
 }
 
 void
+database::check_certs_not_by_hash()
+{
+  results res;
+  imp->fetch(res, one_col, any_rows,
+             query("SELECT 1 FROM revision_certs LIMIT 1"));
+  E(res.empty(), origin::user,
+    F("this database already has certs linked by key hash"));
+}
+
+void
 database_impl::check_format()
 {
   results res;
@@ -527,6 +537,8 @@ 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"));
+  bool have_certs_by_keyname = !res.empty();
 
 
   if (!have_manifests)
@@ -539,6 +551,12 @@ database_impl::check_format()
         F("database %s lacks some cached data\n"
           "run '%s db regenerate_caches' to restore use of this database")
         % filename % prog_name);
+
+      E(!have_certs_by_keyname, 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"
+          "certs linked to keys by key hashes instead")
+        % filename % prog_name);
     }
   else
     {
============================================================
--- database.hh	e3de5162c6654e1355444eb0d6c4d8bd6c0fe63b
+++ database.hh	db606c2e07d1b60a50cb8a95118c5a62141c662f
@@ -85,6 +85,7 @@ public:
   bool is_dbfile(any_path const & file);
   bool database_specified();
   void check_is_not_rosterified();
+  void check_certs_not_by_hash();
 
   void ensure_open();
   void ensure_open_for_format_changes();
@@ -413,9 +414,12 @@ 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<cert> & certs);
   void get_manifest_certs(cert_name const & name, std::vector<cert> & certs);
+  void get_revision_certs_with_keynames(revision_id const & id,
+                                        std::vector<cert> & certs);
 
   // heights
   void get_rev_height(revision_id const & id,
============================================================
--- migrate_ancestry.cc	c02341580f9e65387807dc8f1ae3874d26e81198
+++ migrate_ancestry.cc	60ea5e154f134f633e1405cbd1d312d46a68464c
@@ -1009,6 +1009,11 @@ regenerate_caches(database & db)
   P(F("finished regenerating cached rosters and heights"));
 }
 
+void
+migrate_certs_to_use_key_hash(database & db)
+{
+}
+
 // Local Variables:
 // mode: C++
 // fill-column: 76
============================================================
--- migrate_schema.cc	ffd51091a60a67132b1fd88514fd50d04f0e17f8
+++ migrate_schema.cc	a53016bd522dc676d9df6767369e5d28bf526868
@@ -701,6 +701,20 @@ char const migrate_to_binary_hashes[] =
   "UPDATE manifest_certs    SET id=unhex(id), hash=unhex(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"
+  "  ( hash not null unique,   -- hash of remaining fields separated by \":\"\n"
+  "    revision_id not null,   -- joins with revisions.id"
+  "    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);"
+  ;
 
 // these must be listed in order so that ones listed earlier override ones
 // listed later
@@ -708,6 +722,7 @@ enum upgrade_regime
   {
     upgrade_changesetify,
     upgrade_rosterify,
+    upgrade_cert_link,
     upgrade_regen_caches,
     upgrade_none,
   };
@@ -718,6 +733,7 @@ dump(enum upgrade_regime const & regime,
     {
     case upgrade_changesetify:  out = "upgrade_changesetify"; break;
     case upgrade_rosterify:     out = "upgrade_rosterify"; break;
+    case upgrade_cert_link:     out = "upgrade_cert_link"; break;
     case upgrade_regen_caches:  out = "upgrade_regen_caches"; break;
     case upgrade_none:          out = "upgrade_none"; break;
     default: out = (FL("upgrade_regime(%d)") % regime).str(); break;
@@ -783,9 +799,12 @@ const migration_event migration_events[]
   { "7ca81b45279403419581d7fde31ed888a80bd34e",
     migrate_to_binary_hashes, 0, upgrade_none },
 
+  { "212dd25a23bfd7bfe030ab910e9d62aa66aa2955",
+    migrate_certs_to_key_hash, 0, upgrade_cert_link },
+
   // The last entry in this table should always be the current
   // schema ID, with 0 for the migrators.
-  { "212dd25a23bfd7bfe030ab910e9d62aa66aa2955", 0, 0, upgrade_none }
+  { "e52bb32ddec599ae6aca885d2c30f2429a77bf6c", 0, 0, upgrade_none }
 };
 const size_t n_migration_events = (sizeof migration_events
                                    / sizeof migration_events[0]);
@@ -1099,6 +1118,11 @@ migrate_sql_schema(sqlite3 * db, key_sto
           % prog_name % command_str);
       }
       break;
+    case upgrade_cert_link:
+      P(F("NOTE: the upgrade changes how certs are linked to the key that\n"
+          "signed them. You should now run '%s db migrate_certs_to_key_hashes'")
+        % prog_name);
+      break;
     case upgrade_regen_caches:
       P(F("NOTE: this upgrade cleared monotone's caches\n"
           "you should now run '%s db regenerate_caches'")
============================================================
--- schema.sql	b933498af43c8221933f2afaa6b2b9c6a527c6e0
+++ schema.sql	0137cb35fa501da7ab0017c4a76fb09eeae6013b
@@ -85,23 +85,23 @@ CREATE TABLE public_keys
  
 CREATE TABLE public_keys
 	(
-	hash not null unique,   -- hash of remaining fields separated by ":"
-	id primary key,         -- key identifier chosen by user
+	id primary key,         -- hash of remaining fields separated by ":"
+	name not null,          -- key identifier chosen by user
 	keydata not null        -- RSA public params
 	);
 
 CREATE TABLE revision_certs
 	(
 	hash not null unique,   -- hash of remaining fields separated by ":"
-	id not null,            -- joins with revisions.id
+	revision_id not null,   -- joins with revisions.id
 	name not null,          -- opaque string chosen by user
 	value not null,         -- opaque blob
-	keypair not null,       -- joins with public_keys.id
+	keypair_id not null,    -- joins with public_keys.id
 	signature not null,     -- RSA/SHA1 signature of "address@hidden:val]"
-	unique(name, value, id, keypair, signature)
+	unique(name, value, revision_id, keypair_id, signature)
 	);
 
-CREATE INDEX revision_certs__id ON revision_certs (id);
+CREATE INDEX revision_certs__revision_id ON revision_certs (revision_id);
 
 CREATE TABLE branch_epochs
 	(
@@ -150,4 +150,16 @@ CREATE TABLE manifest_certs
 	unique(name, id, value, keypair, signature)
 	);
 
+CREATE TABLE revision_certs_by_keyname
+	(
+	hash not null unique,   -- hash of remaining fields separated by ":"
+	id not null,            -- joins with revisions.id
+	name not null,          -- opaque string chosen by user
+	value not null,         -- opaque blob
+	keypair not null,       -- joins with public_keys.id
+	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);
+
 COMMIT;