2 from datetime import datetime, timedelta
3 from pysqlite2 import dbapi2 as sqlite
4 from binascii import a2b_base64, b2a_base64
8 from twisted.trial import unittest
10 assert sqlite.version_info >= (2, 1)
12 class DBExcept(Exception):
16 """Dummy class to convert all hashes to base64 for storing in the DB."""
18 sqlite.register_adapter(khash, b2a_base64)
19 sqlite.register_converter("KHASH", a2b_base64)
20 sqlite.register_converter("khash", a2b_base64)
21 sqlite.enable_callback_tracebacks(True)
24 """Database access for storing persistent data."""
26 def __init__(self, db):
34 self.conn.text_factory = str
35 self.conn.row_factory = sqlite.Row
37 def _loadDB(self, db):
39 self.conn = sqlite.connect(database=db, detect_types=sqlite.PARSE_DECLTYPES)
42 raise DBExcept, "Couldn't open DB", traceback.format_exc()
44 def _createNewDB(self, db):
45 self.conn = sqlite.connect(database=db, detect_types=sqlite.PARSE_DECLTYPES)
46 c = self.conn.cursor()
47 c.execute("CREATE TABLE files (path TEXT PRIMARY KEY, hash KHASH, urldir INTEGER, dirlength INTEGER, size NUMBER, mtime NUMBER, refreshed TIMESTAMP)")
48 c.execute("CREATE INDEX files_urldir ON files(urldir)")
49 c.execute("CREATE INDEX files_refreshed ON files(refreshed)")
50 c.execute("CREATE TABLE dirs (urldir INTEGER PRIMARY KEY AUTOINCREMENT, path TEXT)")
51 c.execute("CREATE INDEX dirs_path ON dirs(path)")
55 def _removeChanged(self, path, row):
63 res = (row['size'] == stat.st_size and row['mtime'] == stat.st_mtime)
65 c = self.conn.cursor()
66 c.execute("DELETE FROM files WHERE path = ?", (path, ))
71 def storeFile(self, path, hash, directory):
72 """Store or update a file in the database."""
73 path = os.path.abspath(path)
74 directory = os.path.abspath(directory)
75 assert path.startswith(directory)
77 c = self.conn.cursor()
78 c.execute("SELECT dirs.urldir AS urldir, dirs.path AS directory FROM dirs JOIN files USING (urldir) WHERE files.path = ?", (path, ))
80 if row and directory == row['directory']:
81 c.execute("UPDATE files SET hash = ?, size = ?, mtime = ?, refreshed = ?",
82 (khash(hash), stat.st_size, stat.st_mtime, datetime.now()))
85 urldir, newdir = self.findDirectory(directory)
86 c.execute("INSERT OR REPLACE INTO files VALUES(?, ?, ?, ?, ?, ?, ?)",
87 (path, khash(hash), urldir, len(directory), stat.st_size, stat.st_mtime, datetime.now()))
92 def getFile(self, path):
93 """Get a file from the database.
95 If it has changed or is missing, it is removed from the database.
97 @return: dictionary of info for the file, False if changed, or
98 None if not in database or missing
100 path = os.path.abspath(path)
101 c = self.conn.cursor()
102 c.execute("SELECT hash, urldir, dirlength, size, mtime FROM files WHERE path = ?", (path, ))
104 res = self._removeChanged(path, row)
107 res['hash'] = row['hash']
108 res['urlpath'] = '/~' + str(row['urldir']) + path[row['dirlength']:]
112 def isUnchanged(self, path):
113 """Check if a file in the file system has changed.
115 If it has changed, it is removed from the table.
117 @return: True if unchanged, False if changed, None if not in database
119 path = os.path.abspath(path)
120 c = self.conn.cursor()
121 c.execute("SELECT size, mtime FROM files WHERE path = ?", (path, ))
123 return self._removeChanged(path, row)
125 def refreshFile(self, path):
126 """Refresh the publishing time of a file.
128 If it has changed or is missing, it is removed from the table.
130 @return: True if unchanged, False if changed, None if not in database
132 path = os.path.abspath(path)
133 c = self.conn.cursor()
134 c.execute("SELECT size, mtime FROM files WHERE path = ?", (path, ))
136 res = self._removeChanged(path, row)
138 c.execute("UPDATE files SET refreshed = ? WHERE path = ?", (datetime.now(), path))
141 def expiredFiles(self, expireAfter):
142 """Find files that need refreshing after expireAfter seconds.
144 Also removes any entries from the table that no longer exist.
146 @return: dictionary with keys the hashes, values a list of url paths
148 t = datetime.now() - timedelta(seconds=expireAfter)
149 c = self.conn.cursor()
150 c.execute("SELECT path, hash, urldir, dirlength, size, mtime FROM files WHERE refreshed < ?", (t, ))
154 res = self._removeChanged(row['path'], row)
156 expired.setdefault(row['hash'], []).append('/~' + str(row['urldir']) + row['path'][row['dirlength']:])
161 def removeUntrackedFiles(self, dirs):
162 """Find files that are no longer tracked and so should be removed.
164 Also removes the entries from the table.
166 @return: list of files that were removed
168 assert len(dirs) >= 1
172 newdirs.append(os.path.abspath(dir) + os.sep + '*')
173 sql += " path NOT GLOB ? AND"
176 c = self.conn.cursor()
177 c.execute("SELECT path FROM files " + sql, newdirs)
181 removed.append(row['path'])
185 c.execute("DELETE FROM files " + sql, newdirs)
189 def findDirectory(self, directory):
190 """Store or update a directory in the database.
192 @return: the index of the url directory, and whether it is new or not
194 directory = os.path.abspath(directory)
195 c = self.conn.cursor()
196 c.execute("SELECT min(urldir) AS urldir FROM dirs WHERE path = ?", (directory, ))
200 return row['urldir'], False
202 # Not found, need to add a new one
203 c = self.conn.cursor()
204 c.execute("INSERT INTO dirs (path) VALUES (?)", (directory, ))
210 def getAllDirectories(self):
211 """Get all the current directories avaliable."""
212 c = self.conn.cursor()
213 c.execute("SELECT urldir, path FROM dirs")
217 dirs['~' + str(row['urldir'])] = row['path']
222 def reconcileDirectories(self):
223 """Remove any unneeded directories by checking which are used by files."""
224 c = self.conn.cursor()
225 c.execute('DELETE FROM dirs WHERE urldir NOT IN (SELECT DISTINCT urldir FROM files)')
227 return bool(c.rowcount)
232 class TestDB(unittest.TestCase):
233 """Tests for the khashmir database."""
236 db = '/tmp/khashmir.db'
237 path = '/tmp/khashmir.test'
238 hash = '\xca\xec\xb8\x0c\x00\xe7\x07\xf8~])\x8f\x9d\xe5_B\xff\x1a\xc4!'
240 urlpath = '/~1/khashmir.test'
241 dirs = ['/tmp/apt-dht/top1', '/tmp/apt-dht/top2/sub1', '/tmp/apt-dht/top2/sub2/']
244 f = open(self.path, 'w')
247 os.utime(self.path, None)
248 self.store = DB(self.db)
249 self.store.storeFile(self.path, self.hash, self.directory)
251 def test_getFile(self):
252 res = self.store.getFile(self.path)
254 self.failUnlessEqual(res['hash'], self.hash)
255 self.failUnlessEqual(res['urlpath'], self.urlpath)
257 def test_getAllDirectories(self):
258 res = self.store.getAllDirectories()
260 self.failUnlessEqual(len(res.keys()), 1)
261 self.failUnlessEqual(res.keys()[0], '~1')
262 self.failUnlessEqual(res['~1'], os.path.abspath(self.directory))
264 def test_isUnchanged(self):
265 res = self.store.isUnchanged(self.path)
268 os.utime(self.path, None)
269 res = self.store.isUnchanged(self.path)
270 self.failUnless(res == False)
272 res = self.store.isUnchanged(self.path)
273 self.failUnless(res == None)
275 def test_expiry(self):
276 res = self.store.expiredFiles(1)
277 self.failUnlessEqual(len(res.keys()), 0)
279 res = self.store.expiredFiles(1)
280 self.failUnlessEqual(len(res.keys()), 1)
281 self.failUnlessEqual(res.keys()[0], self.hash)
282 self.failUnlessEqual(len(res[self.hash]), 1)
283 self.failUnlessEqual(res[self.hash][0], self.urlpath)
284 res = self.store.refreshFile(self.path)
286 res = self.store.expiredFiles(1)
287 self.failUnlessEqual(len(res.keys()), 0)
289 def build_dirs(self):
290 for dir in self.dirs:
291 path = os.path.join(dir, self.path[1:])
292 os.makedirs(os.path.dirname(path))
297 self.store.storeFile(path, self.hash, dir)
299 def test_removeUntracked(self):
301 res = self.store.removeUntrackedFiles(self.dirs)
302 self.failUnlessEqual(len(res), 1, 'Got removed paths: %r' % res)
303 self.failUnlessEqual(res[0], self.path, 'Got removed paths: %r' % res)
304 res = self.store.removeUntrackedFiles(self.dirs)
305 self.failUnlessEqual(len(res), 0, 'Got removed paths: %r' % res)
306 res = self.store.removeUntrackedFiles(self.dirs[1:])
307 self.failUnlessEqual(len(res), 1, 'Got removed paths: %r' % res)
308 self.failUnlessEqual(res[0], os.path.join(self.dirs[0], self.path[1:]), 'Got removed paths: %r' % res)
309 res = self.store.removeUntrackedFiles(self.dirs[:1])
310 self.failUnlessEqual(len(res), 2, 'Got removed paths: %r' % res)
311 self.failUnlessIn(os.path.join(self.dirs[1], self.path[1:]), res, 'Got removed paths: %r' % res)
312 self.failUnlessIn(os.path.join(self.dirs[2], self.path[1:]), res, 'Got removed paths: %r' % res)
314 def test_reconcileDirectories(self):
316 res = self.store.getAllDirectories()
318 self.failUnlessEqual(len(res.keys()), 4)
319 res = self.store.reconcileDirectories()
320 self.failUnlessEqual(res, False)
321 res = self.store.getAllDirectories()
323 self.failUnlessEqual(len(res.keys()), 4)
324 res = self.store.removeUntrackedFiles(self.dirs)
325 res = self.store.reconcileDirectories()
326 self.failUnlessEqual(res, True)
327 res = self.store.getAllDirectories()
329 self.failUnlessEqual(len(res.keys()), 3)
330 res = self.store.removeUntrackedFiles(self.dirs[:1])
331 res = self.store.reconcileDirectories()
332 self.failUnlessEqual(res, True)
333 res = self.store.getAllDirectories()
335 self.failUnlessEqual(len(res.keys()), 1)
336 res = self.store.removeUntrackedFiles(['/what'])
337 res = self.store.reconcileDirectories()
338 self.failUnlessEqual(res, True)
339 res = self.store.getAllDirectories()
340 self.failUnlessEqual(len(res.keys()), 0)
343 for root, dirs, files in os.walk('/tmp/apt-dht', topdown=False):
345 os.remove(os.path.join(root, name))
347 os.rmdir(os.path.join(root, name))