40 VALUES ('keywords', %d, 1); |
40 VALUES ('keywords', %d, 1); |
41 """ %(id,)) |
41 """ %(id,)) |
42 self.crs.execute(""" |
42 self.crs.execute(""" |
43 INSERT INTO db_sequence(seq_name, userid, nextid) |
43 INSERT INTO db_sequence(seq_name, userid, nextid) |
44 VALUES ('bookmarks', %d, 0); |
44 VALUES ('bookmarks', %d, 0); |
|
45 """ %(id,)) |
|
46 self.crs.execute(""" |
|
47 INSERT INTO folders(id, keyword, parent, userid) |
|
48 VALUES (0, 0, -1, %d); |
|
49 """ %(id,)) |
|
50 self.crs.execute(""" |
|
51 INSERT INTO db_sequence(seq_name, userid, nextid) |
|
52 VALUES ('folders', %d, 1); |
45 """ %(id,)) |
53 """ %(id,)) |
46 self.crs.execute(""" |
54 self.crs.execute(""" |
47 INSERT INTO preferences(userid) |
55 INSERT INTO preferences(userid) |
48 VALUES (%d); |
56 VALUES (%d); |
49 """ %(id,)) |
57 """ %(id,)) |
259 for kw in except_kw: |
267 for kw in except_kw: |
260 qry = qry + ("EXCEPT " + basic_qry % (kw, self.userid)) |
268 qry = qry + ("EXCEPT " + basic_qry % (kw, self.userid)) |
261 qry = qry + ';' |
269 qry = qry + ';' |
262 self.crs.execute(qry) |
270 self.crs.execute(qry) |
263 return self.crs.fetchall() |
271 return self.crs.fetchall() |
|
272 def get_folder_keyword(self, id): |
|
273 self.crs.execute(""" |
|
274 SELECT k.id, k.name |
|
275 FROM keywords k, folders f |
|
276 WHERE |
|
277 f.userid = %d AND f.id = %d |
|
278 AND k.userid = f.userid |
|
279 AND k.id = f.keyword; |
|
280 """ %(self.userid, id)) |
|
281 return self.crs.fetchone() |
|
282 def get_subfolders(self, id): |
|
283 self.crs.execute(""" |
|
284 SELECT f.id, k.id, k.name |
|
285 FROM folders f, keywords k |
|
286 WHERE f.userid = %d |
|
287 AND f.parent = %d |
|
288 AND k.userid = f.userid |
|
289 AND k.id = f.keyword |
|
290 ORDER BY k.name; |
|
291 """ %(self.userid, id)) |
|
292 res = self.crs.fetchall() |
|
293 return map(lambda e: { |
|
294 'id' : e[0], |
|
295 'name' : e[2], |
|
296 'keyword' : e[1] }, |
|
297 res) |
|
298 def get_folder_parents_and_self(self, id): |
|
299 """Return self and parents in descending order.""" |
|
300 if id < 0: |
|
301 return [] |
|
302 self.crs.execute(""" |
|
303 SELECT f.id, k.id, k.name, f.parent |
|
304 FROM folders f, keywords k |
|
305 WHERE |
|
306 f.id = %d AND f.userid = %d |
|
307 AND k.userid = f.userid |
|
308 AND k.id = f.keyword; |
|
309 """ %(id, self.userid)) |
|
310 cur = self.crs.fetchone() |
|
311 res = [{ 'id': cur[0], |
|
312 'name' : cur[2], |
|
313 'keyword': cur[1] }] |
|
314 return res + self.get_folder_parents_and_self(cur[3]) |
|
315 def add_folder(self, keyword, parent): |
|
316 id = self.get_next_id('folders') |
|
317 self.crs.execute(""" |
|
318 INSERT INTO folders(userid, id, keyword, parent) |
|
319 VALUES (%d, %d, %d, %d); |
|
320 """ % (self.userid, id, keyword, parent)) |
|
321 self.cnx.commit() |
|
322 def remove_folders(self, id, commit = 1): |
|
323 if id == 0: |
|
324 raise "Can't erase base folder!" |
|
325 subs = self.get_subfolders(self, id) |
|
326 for sub in subs: |
|
327 self.remove_folder(sub[0], 0) |
|
328 self.crs.execute(""" |
|
329 DELETE FROM folders |
|
330 WHERE id = %d AND userid = %d; |
|
331 """ % (id, self.userid)) |
|
332 if commit: |
|
333 self.cnx.commit() |
264 def get_next_id(self, seq_name): |
334 def get_next_id(self, seq_name): |
265 if seq_name == 'users': |
335 if seq_name == 'users': |
266 userid = -1 |
336 userid = -1 |
267 else: |
337 else: |
268 userid = self.userid |
338 userid = self.userid |