From e92f9aa27907392833f8a03a5302229838f90e95 Mon Sep 17 00:00:00 2001
Message-Id: <e92f9aa27907392833f8a03a5302229838f90e95.1715969658.git.mdw@distorted.org.uk>
From: Mark Wooding <mdw@chiark.greenend.org.uk>
Date: Sun, 24 May 2015 19:33:21 +0100
Subject: [PATCH] catacomb/pwsafe.py: Add a backend based on SQLite.
Organization: Straylight/Edgeware

From: Mark Wooding <mdw@distorted.org.uk>

---
 catacomb/pwsafe.py | 123 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 123 insertions(+)

diff --git a/catacomb/pwsafe.py b/catacomb/pwsafe.py
index 52bd6ba..4b4b994 100644
--- a/catacomb/pwsafe.py
+++ b/catacomb/pwsafe.py
@@ -541,6 +541,129 @@ else:
         if k.startswith('$'): yield k[1:], me._db[k]
         k = me._db.nextkey(k)
 
+try: import sqlite3 as _Q
+except ImportError: pass
+else:
+  class SQLiteStorageBackend (StorageBackend):
+    """
+    I represent a password database stored in SQLite.
+
+    Metadata and password items are stored in separate tables, so there's no
+    conflict.  Some additional metadata is stored in the `meta' table, with
+    names beginning with `$' so as not to conflict with clients:
+
+    $version            The schema version of the table.
+    """
+
+    NAME = 'sqlite'
+    VERSION = 0
+
+    def _open(me, file, writep):
+      try:
+        me._db = _Q.connect(file)
+        ver = me._query_scalar(
+          "SELECT value FROM meta WHERE name = '$version'",
+          "version check")
+      except (_Q.DatabaseError, _Q.OperationalError), e:
+        raise StorageBackendRefusal, e
+      if ver is None: raise ValueError, 'database broken (missing $version)'
+      elif ver < me.VERSION: me._upgrade(ver)
+      elif ver > me.VERSION:
+        raise ValueError, 'unknown database schema version (%d > %d)' % \
+            (ver, me.VERSION)
+
+    def _create(me, file):
+      fd = _OS.open(file, _OS.O_WRONLY | _OS.O_CREAT | _OS.O_EXCL, 0600)
+      _OS.close(fd)
+      try:
+        me._db = _Q.connect(file)
+        c = me._db.cursor()
+        c.execute("""
+          CREATE TABLE meta (
+                  name TEXT PRIMARY KEY NOT NULL,
+                  value BLOB NOT NULL);
+        """)
+        c.execute("""
+          CREATE TABLE passwd (
+                  label BLOB PRIMARY KEY NOT NULL,
+                  payload BLOB NOT NULL);
+        """)
+        c.execute("""
+          INSERT INTO meta (name, value) VALUES ('$version', ?);
+        """, [me.VERSION])
+      except:
+        try: _OS.unlink(file)
+        except OSError: pass
+        raise
+
+    def _upgrade(me, ver):
+      """Upgrade the database from schema version VER."""
+      assert False, 'how embarrassing'
+
+    def _close(me, abruptp):
+      if not abruptp: me._db.commit()
+      me._db.close()
+      me._db = None
+
+    def _fetch_scalar(me, c, what, default = None):
+      try: row = next(c)
+      except StopIteration: val = default
+      else: val, = row
+      try: row = next(c)
+      except StopIteration: pass
+      else: raise ValueError, 'multiple matching records for %s' % what
+      return val
+
+    def _query_scalar(me, query, what, default = None, args = []):
+      c = me._db.cursor()
+      c.execute(query, args)
+      return me._fetch_scalar(c, what, default)
+
+    def _get_meta(me, name, default):
+      v = me._query_scalar("SELECT value FROM meta WHERE name = ?",
+                           "metadata item `%s'" % name,
+                           default = default, args = [name])
+      if v is default: return v
+      else: return str(v)
+
+    def _put_meta(me, name, value):
+      c = me._db.cursor()
+      c.execute("INSERT OR REPLACE INTO meta (name, value) VALUES (?, ?)",
+                [name, buffer(value)])
+
+    def _del_meta(me, name):
+      c = me._db.cursor()
+      c.execute("DELETE FROM meta WHERE name = ?", [name])
+      if not c.rowcount: raise KeyError, name
+
+    def _iter_meta(me):
+      c = me._db.cursor()
+      c.execute("SELECT name, value FROM meta WHERE name NOT LIKE '$%'")
+      for k, v in c: yield k, str(v)
+
+    def _get_passwd(me, label):
+      pld = me._query_scalar("SELECT payload FROM passwd WHERE label = ?",
+                             "password", default = None,
+                             args = [buffer(label)])
+      if pld is None: raise KeyError, label
+      return str(pld)
+
+    def _put_passwd(me, label, payload):
+      c = me._db.cursor()
+      c.execute("INSERT OR REPLACE INTO passwd (label, payload) "
+                "VALUES (?, ?)",
+                [buffer(label), buffer(payload)])
+
+    def _del_passwd(me, label):
+      c = me._db.cursor()
+      c.execute("DELETE FROM passwd WHERE label = ?", [label])
+      if not c.rowcount: raise KeyError, label
+
+    def _iter_passwds(me):
+      c = me._db.cursor()
+      c.execute("SELECT label, payload FROM passwd")
+      for k, v in c: yield str(k), str(v)
+
 class PlainTextBackend (StorageBackend):
   """
   I'm a utility base class for storage backends which use plain text files.
-- 
[mdw]