2 from datetime import datetime, timedelta
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."""
84 c = self.conn.cursor()
85 c.execute("INSERT OR REPLACE INTO kv VALUES (?, ?, ?)", (key, value, datetime.now()))
88 def expireValues(self, expireAfter):
89 """Expire older values after expireAfter seconds."""
90 t = datetime.now() - timedelta(seconds=expireAfter)
91 c = self.conn.cursor()
92 c.execute("DELETE FROM kv WHERE time < ?", (t, ))