3 from pysqlite2 import dbapi2 as sqlite
6 class DBExcept(Exception):
10 """Database access for storing persistent data."""
12 def __init__(self, db):
20 self.conn.text_factory = str
22 def _loadDB(self, db):
24 self.conn = sqlite.connect(database=db, detect_types=sqlite.PARSE_DECLTYPES)
27 raise DBExcept, "Couldn't open DB", traceback.format_exc()
29 def _createNewDB(self, db):
30 self.conn = sqlite.connect(database=db)
31 c = self.conn.cursor()
32 c.execute("CREATE TABLE kv (key TEXT, value TEXT, time TIMESTAMP, PRIMARY KEY (key, value))")
33 c.execute("CREATE INDEX kv_key ON kv(key)")
34 c.execute("CREATE INDEX kv_timestamp ON kv(time)")
35 c.execute("CREATE TABLE nodes (id TEXT PRIMARY KEY, host TEXT, port NUMBER)")
36 c.execute("CREATE TABLE self (num NUMBER PRIMARY KEY, id TEXT)")
39 def getSelfNode(self):
40 c = self.conn.cursor()
41 c.execute('SELECT id FROM self WHERE num = 0')
43 return c.fetchone()[0]
47 def saveSelfNode(self, id):
48 c = self.conn.cursor()
49 c.execute("INSERT OR REPLACE INTO self VALUES (0, ?)", (id,))
52 def dumpRoutingTable(self, buckets):
54 save routing table nodes to the database
56 c = self.conn.cursor()
57 c.execute("DELETE FROM nodes WHERE id NOT NULL")
58 for bucket in buckets:
60 c.execute("INSERT INTO nodes VALUES (?, ?, ?)", (node.id, node.host, node.port))
63 def getRoutingTable(self):
65 load routing table nodes from database
66 it's usually a good idea to call refreshTable(force=1) after loading the table
68 c = self.conn.cursor()
69 c.execute("SELECT * FROM nodes")
72 def retrieveValues(self, key):
73 c = self.conn.cursor()
74 c.execute("SELECT value FROM kv WHERE key = ?", (key,))
82 def storeValue(self, key, value):
83 """Store or update a key and value."""
85 c = self.conn.cursor()
86 c.execute("INSERT OR REPLACE INTO kv VALUES (?, ?, ?)", (key, value, t))
89 def expireValues(self, expireTime):
90 """Expire older values than expireTime."""
91 t = "%0.6f" % expireTime
92 c = self.conn.cursor()
93 c.execute("DELETE FROM kv WHERE time < ?", (t, ))