3 import sqlite ## find this at http://pysqlite.sourceforge.net/
6 class DBExcept(Exception):
10 """Database access for storing persistent data."""
12 def __init__(self, db):
21 def _loadDB(self, db):
23 self.store = sqlite.connect(db=db)
24 #self.store.autocommit = 0
27 raise DBExcept, "Couldn't open DB", traceback.format_exc()
29 def _createNewDB(self, db):
30 self.store = sqlite.connect(db=db)
32 create table kv (key binary, value binary, time timestamp, primary key (key, value));
33 create index kv_key on kv(key);
34 create index kv_timestamp on kv(time);
36 create table nodes (id binary primary key, host text, port number);
38 create table self (num number primary key, id binary);
40 c = self.store.cursor()
44 def getSelfNode(self):
45 c = self.store.cursor()
46 c.execute('select id from self where num = 0;')
48 return c.fetchone()[0]
52 def saveSelfNode(self, id):
53 c = self.store.cursor()
54 c.execute('delete from self where num = 0;')
55 c.execute("insert into self values (0, %s);", sqlite.encode(id))
58 def dumpRoutingTable(self, buckets):
60 save routing table nodes to the database
62 c = self.store.cursor()
63 c.execute("delete from nodes where id not NULL;")
64 for bucket in buckets:
66 c.execute("insert into nodes values (%s, %s, %s);", (sqlite.encode(node.id), node.host, node.port))
69 def getRoutingTable(self):
71 load routing table nodes from database
72 it's usually a good idea to call refreshTable(force=1) after loading the table
74 c = self.store.cursor()
75 c.execute("select * from nodes;")
78 def retrieveValues(self, key):
79 c = self.store.cursor()
80 c.execute("select value from kv where key = %s;", sqlite.encode(key))
88 def storeValue(self, key, value):
89 """Store or update a key and value."""
91 c = self.store.cursor()
93 c.execute("insert into kv values (%s, %s, %s);", (sqlite.encode(key), sqlite.encode(value), t))
94 except sqlite.IntegrityError, reason:
95 # update last insert time
96 c.execute("update kv set time = %s where key = %s and value = %s;", (t, sqlite.encode(key), sqlite.encode(value)))
99 def expireValues(self, expireTime):
100 """Expire older values than expireTime."""
101 t = "%0.6f" % expireTime
102 c = self.store.cursor()
103 s = "delete from kv where time < '%s';" % t