#
#
# patch "database.cc"
#  from [809e62c9dd0adcd5c4d86d4f863bcb29f203ceaa]
#    to [f41522c805852544c3fd396616bc65fc7aad92b5]
# 
# patch "database.hh"
#  from [688a9e65e619589ed8536495986505623dfb2dd1]
#    to [2ea617215f1916f3b4428216b0b6701b26606e22]
# 
# patch "schema_migration.cc"
#  from [49bd56dbd04ea1bd9156aac3c7642af398db4064]
#    to [5d60dd7d8b1755c45a3dafff5e712cacb0325e5d]
# 
# patch "tests/database_check/__driver__.lua"
#  from [41c8c6c0afa3107df66dbe63d23e8c37ce6bb431]
#    to [a22be8cbd007fddbcbb4e1db832574f7e39589c4]
# 
# patch "tests/db_check_(heights)/__driver__.lua"
#  from [1ce1977a7efc19e586cabda16b41b54b8eb1dd8f]
#    to [a25502f8cb60df804f7129fb92cdfb073c0f263a]
# 
# patch "tests/db_check_and_non-serious_errors/__driver__.lua"
#  from [a96021255cd73d0de4101d20c6ef103438fd54c2]
#    to [9d01a189edaa92b99b911cd243204474f16ec4c5]
#
============================================================
--- database.cc	809e62c9dd0adcd5c4d86d4f863bcb29f203ceaa
+++ database.cc	f41522c805852544c3fd396616bc65fc7aad92b5
@@ -2321,7 +2321,7 @@ database::public_key_exists(hexenc<id> c
   results res;
   fetch(res, one_col, any_rows,
         query("SELECT id FROM public_keys WHERE hash = ?")
-        % text(hash()));
+        % blob(decode_hexenc(hash())));
   I((res.size() == 1) || (res.size() == 0));
   if (res.size() == 1)
     return true;
@@ -2349,7 +2349,7 @@ database::get_pubkey(hexenc<id> const & 
   results res;
   fetch(res, 2, one_row,
         query("SELECT id, keydata FROM public_keys WHERE hash = ?")
-        % text(hash()));
+        % blob(decode_hexenc(hash())));
   id = rsa_keypair_id(res[0][0]);
   encode_base64(rsa_pub_key(res[0][1]), pub_encoded);
 }
@@ -2388,7 +2388,7 @@ database::put_key(rsa_keypair_id const &
   rsa_pub_key pub_key;
   decode_base64(pub_encoded, pub_key);
   execute(query("INSERT INTO public_keys VALUES(?, ?, ?)")
-          % text(thash())
+          % blob(decode_hexenc(thash()))
           % text(pub_id())
           % blob(pub_key()));
 
@@ -2786,20 +2786,62 @@ void
 
 // completions
 void
+database::prefix_matching_constraint(std::string const & colname,
+                                     std::string const & prefix,
+                                     query & constraint)
+{
+  L(FL("prefix_matching_constraint for '%s'") % prefix);
+
+  if (prefix.empty())
+    constraint = query("1");
+  else
+    {
+      string lower_bound(prefix);
+      string upper_bound(prefix);
+
+      string::reverse_iterator ity(upper_bound.rbegin());
+      ++(*ity);
+      while ((*ity == 0) && ity != upper_bound.rend())
+        {
+          ++ity;
+          ++(*ity);
+        }
+
+      if (ity == upper_bound.rend())
+        {
+          // no upper bound needed, as the lower bound is
+          // 0xffffff...
+          L(FL("prefix_matcher: only lower bound ('%s')")
+            % encode_hexenc(lower_bound));
+          constraint = query(colname + " > ?")
+                       % blob(lower_bound);
+        }
+      else
+        {
+          L(FL("prefix_matcher: lower bound ('%s') and upper bound ('%s')")
+            % encode_hexenc(lower_bound)
+            % encode_hexenc(upper_bound));
+          constraint = query(colname + " BETWEEN ? AND ?")
+                       % blob(lower_bound)
+                       % blob(upper_bound);
+        }
+    }
+}
+
+void
 database::complete(string const & partial,
                    set<revision_id> & completions)
 {
   results res;
   completions.clear();
+  query constraint;
 
-  string pattern = partial + "*";
-
+  prefix_matching_constraint("id", partial, constraint);
   fetch(res, 1, any_rows,
-        query("SELECT id FROM revisions WHERE id GLOB ?")
-        % text(pattern));
+        query("SELECT id FROM revisions WHERE " + constraint.sql_cmd));
 
   for (size_t i = 0; i < res.size(); ++i)
-    completions.insert(revision_id(res[i][0]));
+    completions.insert(revision_id(encode_hexenc(res[i][0])));
 }
 
 
@@ -2809,24 +2851,22 @@ database::complete(string const & partia
 {
   results res;
   completions.clear();
+  query constraint;
 
-  string pattern = partial + "*";
-
+  prefix_matching_constraint("id", partial, constraint);
   fetch(res, 1, any_rows,
-        query("SELECT id FROM files WHERE id GLOB ?")
-        % text(pattern));
+        query("SELECT id FROM files WHERE " + constraint.sql_cmd));
 
   for (size_t i = 0; i < res.size(); ++i)
-    completions.insert(file_id(res[i][0]));
+    completions.insert(file_id(encode_hexenc(res[i][0])));
 
   res.clear();
 
   fetch(res, 1, any_rows,
-        query("SELECT id FROM file_deltas WHERE id GLOB ?")
-        % text(pattern));
+        query("SELECT id FROM file_deltas WHERE " + constraint.sql_cmd));
 
   for (size_t i = 0; i < res.size(); ++i)
-    completions.insert(file_id(res[i][0]));
+    completions.insert(file_id(encode_hexenc(res[i][0])));
 }
 
 void
@@ -2835,15 +2875,16 @@ database::complete(string const & partia
 {
   results res;
   completions.clear();
+  query constraint;
 
-  string pattern = partial + "*";
-
+  prefix_matching_constraint("hash", partial, constraint);
   fetch(res, 2, any_rows,
-        query("SELECT hash, id FROM public_keys WHERE hash GLOB ?")
-        % text(pattern));
+        query("SELECT hash, id FROM public_keys WHERE "
+              + constraint.sql_cmd));
 
   for (size_t i = 0; i < res.size(); ++i)
-    completions.insert(make_pair(key_id(res[i][0]), utf8(res[i][1])));
+    completions.insert(make_pair(key_id(encode_hexenc(res[i][0])),
+                                 utf8(res[i][1])));
 }
 
 using selectors::selector_type;
@@ -2918,13 +2959,18 @@ void database::complete(selector_type ty
 
           if (i->first == selectors::sel_ident)
             {
-              lim.sql_cmd += "SELECT id FROM revision_certs WHERE id GLOB ?";
-              lim % text(i->second + "*");
+              query constraint;
+              prefix_matching_constraint("id", i->second, constraint);
+              lim.sql_cmd += "SELECT id FROM revision_certs WHERE " +
+                             constraint.sql_cmd;
             }
           else if (i->first == selectors::sel_parent)
             {
-              lim.sql_cmd += "SELECT parent AS id FROM revision_ancestry WHERE child GLOB ?";
-              lim % text(i->second + "*"); 
+              query constraint;
+              prefix_matching_constraint("parent", i->second, constraint);
+              lim.sql_cmd += "SELECT parent AS id "
+                             "FROM revision_ancestry WHERE " +
+                             constraint.sql_cmd;
             }
           else if (i->first == selectors::sel_cert)
             {
@@ -2990,12 +3036,12 @@ void database::complete(selector_type ty
                 {
                   set<revision_id>::const_iterator r = heads.begin();
                   lim.sql_cmd += "?";
-                  lim % text(r->inner()());
+                  lim % blob(decode_hexenc(r->inner()()));
                   r++;
                   while (r != heads.end())
                     {
                       lim.sql_cmd += ", ?";
-                      lim % text(r->inner()());
+                      lim % blob(decode_hexenc(r->inner()()));
                       r++;
                     }
                 }
@@ -3075,13 +3121,10 @@ void database::complete(selector_type ty
   fetch(res, one_col, any_rows, lim);
   for (size_t i = 0; i < res.size(); ++i)
     {
-      if (ty == selectors::sel_ident)
-        completions.insert(res[i][0]);
+      if (ty == selectors::sel_ident || ty == selectors::sel_parent)
+        completions.insert(encode_hexenc(res[i][0]));
       else
-        {
-          data row_decoded(res[i][0]);
-          completions.insert(row_decoded());
-        }
+        completions.insert(res[i][0]);
     }
 }
 
@@ -3097,7 +3140,8 @@ database::get_epochs(map<branch_name, ep
     {
       branch_name decoded(idx(*i, 0));
       I(epochs.find(decoded) == epochs.end());
-      epochs.insert(make_pair(decoded, epoch_data(idx(*i, 1))));
+      epochs.insert(make_pair(decoded,
+                              epoch_data(encode_hexenc(idx(*i, 1)))));
     }
 }
 
@@ -3110,10 +3154,10 @@ database::get_epoch(epoch_id const & eid
   fetch(res, 2, any_rows,
         query("SELECT branch, epoch FROM branch_epochs"
         " WHERE hash = ?")
-        % text(eid.inner()()));
+        % blob(decode_hexenc(eid.inner()())));
   I(res.size() == 1);
   branch = branch_name(idx(idx(res, 0), 0));
-  epo = epoch_data(idx(idx(res, 0), 1));
+  epo = epoch_data(encode_hexenc(idx(idx(res, 0), 1)));
 }
 
 bool
@@ -3122,7 +3166,7 @@ database::epoch_exists(epoch_id const & 
   results res;
   fetch(res, one_col, any_rows,
         query("SELECT hash FROM branch_epochs WHERE hash = ?")
-        % text(eid.inner()()));
+        % blob(decode_hexenc(eid.inner()())));
   I(res.size() == 1 || res.size() == 0);
   return res.size() == 1;
 }
@@ -3134,9 +3178,9 @@ database::set_epoch(branch_name const & 
   epoch_hash_code(branch, epo, eid);
   I(epo.inner()().size() == constants::epochlen);
   execute(query("INSERT OR REPLACE INTO branch_epochs VALUES(?, ?, ?)")
-          % text(eid.inner()())
+          % blob(decode_hexenc(eid.inner()()))
           % blob(branch())
-          % text(epo.inner()()));
+          % blob(decode_hexenc(epo.inner()())));
 }
 
 void
============================================================
--- database.hh	688a9e65e619589ed8536495986505623dfb2dd1
+++ database.hh	2ea617215f1916f3b4428216b0b6701b26606e22
@@ -519,6 +519,10 @@ public:
   // --== Completion ==--
   //
 public:
+  void prefix_matching_constraint(std::string const & colname,
+                                   std::string const & prefix,
+                                   query & constraint);
+
   void complete(std::string const & partial,
                 std::set<revision_id> & completions);
 
============================================================
--- schema_migration.cc	49bd56dbd04ea1bd9156aac3c7642af398db4064
+++ schema_migration.cc	5d60dd7d8b1755c45a3dafff5e712cacb0325e5d
@@ -648,7 +648,9 @@ char const migrate_to_binary_hashes[] =
   "UPDATE public_keys       SET hash=unhex(hash);"
   "UPDATE revision_certs    SET hash=unhex(hash), id=unhex(id);"
 
-  // we altered a comment on this table, thus we need to recreated it
+  // We altered a comment on this table, thus we need to recreated it.
+  // Additionally, this is the only schema change, so that we get another
+  // schema hash to upgrade to.
   "ALTER TABLE branch_epochs RENAME TO tmp;"
   "CREATE TABLE branch_epochs"
 	"  ( hash not null unique,         -- hash of remaining fields separated by \":\"\n"
@@ -657,6 +659,12 @@ char const migrate_to_binary_hashes[] =
 	"  );"
   "INSERT INTO branch_epochs SELECT unhex(hash), branch, unhex(epoch) FROM tmp;"
   "DROP TABLE tmp;"
+
+  // To be able to migrate from pre-roster era, we also need to convert
+  // these deprecated tables
+  "UPDATE manifests         SET id=unhex(id);"
+  "UPDATE manifest_deltas   SET id=unhex(id), base=unhex(base);"
+  "UPDATE manifest_certs    SET id=unhex(id), hash=unhex(hash);"
   ;
 
 // this is a function because it has to refer to the numeric constant
============================================================
--- tests/database_check/__driver__.lua	41c8c6c0afa3107df66dbe63d23e8c37ce6bb431
+++ tests/database_check/__driver__.lua	a22be8cbd007fddbcbb4e1db832574f7e39589c4
@@ -20,7 +20,7 @@ revs[1] = base_revision()
 check(mtn("add", "file1"), 0, false, false)
 commit("test", "add file1")
 revs[1] = base_revision()
-check(raw_mtn("db", "execute", "select id from rosters"), 0, true, false)
+check(raw_mtn("db", "execute", "select hex(id) from rosters"), 0, true, false)
 check(tail("stdout", 1), 0, true)
 rosters[1] = trim(readfile("stdout"))
 
@@ -40,7 +40,7 @@ check(qgrep('database is good', "stderr"
 -- remove file2 from the database invalidating roster2 and roster3
 -- both of which include this file
 
-dbex("delete from files where id='%s'", files[2])
+dbex("delete from files where id=x'%s'", files[2])
 
 check(mtn("db", "check", "--ticker=dot"), 1, false, true)
 check(not qgrep('database is good', "stderr"))
@@ -61,11 +61,11 @@ copy("_MTN/revision", "saved_revision")
 commit("test", "to be removed")
 revs[4] = base_revision()
 copy("_MTN/revision", "saved_revision")
-dbex("delete from revisions where id='%s'", revs[4])
+dbex("delete from revisions where id=x'%s'", revs[4])
 -- revert to the old workspace state
 copy("saved_revision", "_MTN/revision")
 -- remove another file too
-dbex("delete from files where id='%s'", files[3])
+dbex("delete from files where id=x'%s'", files[3])
 
 check(mtn("db", "check", "--ticker=dot"), 1, false, true)
 check(qgrep('2 unreferenced file', "stderr"))
@@ -82,18 +82,18 @@ xdelta_hh = "68d15dc01398c7bb375b1a90fbb
 xdelta_cc = "877cfe29db0f60dec63439857fe78673b9d55346"
 xdelta_hh = "68d15dc01398c7bb375b1a90fbb420bebef1bac7"
 
-dbex("insert into revision_ancestry values('%s', '%s')", xdelta_cc, xdelta_hh)
+dbex("insert into revision_ancestry values(x'%s', x'%s')", xdelta_cc, xdelta_hh)
 check(mtn("db", "check", "--ticker=dot"), 1, false, true)
 check(qgrep('3 mismatched parent', "stderr"))
 check(qgrep('3 mismatched child', "stderr"))
 check(qgrep('3 missing revision', "stderr"))
 
-dbex("delete from roster_deltas where id='%s'", rosters[1])
+dbex("delete from roster_deltas where id=x'%s'", rosters[1])
 check(mtn("db", "check", "--ticker=dot"), 1, false, true)
 -- ROSTER TODO: need check_sane_history equivalent in db check
 --check(grep '3 revisions with bad history' stderr, 0, false, false)
 
-dbex("delete from revisions where id='%s'", revs[1])
+dbex("delete from revisions where id=x'%s'", revs[1])
 check(mtn("db", "check", "--ticker=dot"), 1, false, true)
 check(qgrep('4 missing revision', "stderr"))
 -- ROSTER TODO
@@ -102,7 +102,7 @@ hash = sha1("tosum")
 writefile("tosum", revs[2]..":comment:this is a test:address@hidden:bogus sig")
 hash = sha1("tosum")
 
-dbex("insert into revision_certs values ('%s', '%s', 'comment', 'this is a test', 'address@hidden', 'bogus sig')", hash, revs[2])
+dbex("insert into revision_certs values (x'%s', x'%s', 'comment', 'this is a test', 'address@hidden', 'bogus sig')", hash, revs[2])
 check(mtn("db", "check", "--ticker=dot"), 1, false, true)
 check(qgrep('1 bad sig', "stderr"))
 
============================================================
--- tests/db_check_(heights)/__driver__.lua	1ce1977a7efc19e586cabda16b41b54b8eb1dd8f
+++ tests/db_check_(heights)/__driver__.lua	a25502f8cb60df804f7129fb92cdfb073c0f263a
@@ -13,9 +13,9 @@ check(mtn("db", "check"), 0, false, fals
 check(mtn("db", "check"), 0, false, false)
 
 -- swap the two heights (by swapping their revs)
-check(mtn("db", "execute", "update heights set revision='temp' where revision='" .. revA .. "';"), 0, false, false)
-check(mtn("db", "execute", "update heights set revision='".. revA .. "' where revision='" .. revB .. "';"), 0, false, false)
-check(mtn("db", "execute", "update heights set revision='".. revB .. "' where revision='temp';"), 0, false, false)
+check(mtn("db", "execute", "update heights set revision='temp' where revision=x'" .. revA .. "';"), 0, false, false)
+check(mtn("db", "execute", "update heights set revision=x'".. revA .. "' where revision=x'" .. revB .. "';"), 0, false, false)
+check(mtn("db", "execute", "update heights set revision=x'".. revB .. "' where revision='temp';"), 0, false, false)
 
 -- check
 check(mtn("db", "check"), 1, false, true)
@@ -24,7 +24,7 @@ check(qgrep('serious problems detected',
 check(qgrep('serious problems detected', 'stderr'))
 
 -- delete one of the heights
-check(mtn("db", "execute", "delete from heights where revision='" .. revA .. "';"), 0, false, false)
+check(mtn("db", "execute", "delete from heights where revision=x'" .. revA .. "';"), 0, false, false)
 
 -- check again
 check(mtn("db", "check"), 1, false, true)
@@ -33,7 +33,7 @@ check(qgrep('serious problems detected',
 check(qgrep('serious problems detected', 'stderr'))
 
 -- duplicate the remaining height
-check(mtn("db", "execute", "insert into heights (revision, height) values ('" .. revA .. "', (select height from heights where revision='" .. revB .. "'));"), 0, false, false)
+check(mtn("db", "execute", "insert into heights (revision, height) values (x'" .. revA .. "', (select height from heights where revision=x'" .. revB .. "'));"), 0, false, false)
 
 -- check once more
 check(mtn("db", "check"), 1, false, true)
@@ -42,3 +42,4 @@ check(qgrep('serious problems detected',
 check(qgrep('1 incorrect heights', 'stderr'))
 check(qgrep(revB, 'stderr'))
 check(qgrep('serious problems detected', 'stderr'))
+
============================================================
--- tests/db_check_and_non-serious_errors/__driver__.lua	a96021255cd73d0de4101d20c6ef103438fd54c2
+++ tests/db_check_and_non-serious_errors/__driver__.lua	9d01a189edaa92b99b911cd243204474f16ec4c5
@@ -20,7 +20,7 @@ for a,b in pairs({revisions = "id", revi
 check(mtn("commit", "-m", "goingaway"), 0, false, false)
 del_rev = base_revision()
 for a,b in pairs({revisions = "id", revision_certs = "id", revision_ancestry = "child"}) do
-  local str = string.format("delete from %s where %s = '%s'", a, b, del_rev)
+  local str = string.format("delete from %s where %s = x'%s'", a, b, del_rev)
   check(mtn("db", "execute", str), 0, false, false)
 end