218 self.cnx.commit() |
218 self.cnx.commit() |
219 def add_keywords(self, add_id, into_id): |
219 def add_keywords(self, add_id, into_id): |
220 self.crs.execute(""" |
220 self.crs.execute(""" |
221 INSERT INTO bookmarks_keywords (userid, |
221 INSERT INTO bookmarks_keywords (userid, |
222 bookmark, keyword) |
222 bookmark, keyword) |
223 SELECT (%d, %d, bookmark) |
223 SELECT %d, bookmark, %d |
|
224 FROM bookmarks_keywords |
224 WHERE keyword = %d |
225 WHERE keyword = %d |
225 AND userid = %d |
226 AND userid = %d |
226 AND bookmark NOT IN |
227 AND bookmark NOT IN |
227 (SELECT bookmark FROM bookmarks_keywords |
228 (SELECT bookmark FROM bookmarks_keywords |
228 WHERE userid = %d AND keyword = %d ) |
229 WHERE userid = %d AND keyword = %d ) |
266 self.crs.execute(qry) |
267 self.crs.execute(qry) |
267 res = self.crs.fetchall() |
268 res = self.crs.fetchall() |
268 if not res: |
269 if not res: |
269 res = [[0,0]] |
270 res = [[0,0]] |
270 return res |
271 return res |
271 def select_bookmarks(self, has_kw = [0], except_kw = []): |
272 def _bookmarks_selection(self, has_kw = [0], except_kw = []): |
272 """ |
273 """ |
273 Return a list of id bookmarks who has /has_kw/ set but not /except_kw/. |
274 Return a bookmarks selection query used in at least two function. |
274 """ |
275 """ |
275 basic_qry = """ |
276 basic_qry = """ |
276 SELECT bk.bookmark FROM bookmarks_keywords bk |
277 SELECT bk.bookmark FROM bookmarks_keywords bk |
277 WHERE bk.keyword = %d |
278 WHERE bk.keyword = %d |
278 AND bk.userid = %d |
279 AND bk.userid = %d |
279 """ |
280 """ |
280 |
281 |
281 qry = basic_qry % (has_kw[0], self.userid) |
282 qry = basic_qry % (has_kw[0], self.userid) |
282 for kw in has_kw[1:]: |
283 for kw in has_kw[1:]: |
283 qry = qry + ("INTERSECT " + basic_qry % (kw, self.userid)) |
284 qry += ("INTERSECT " + basic_qry % (kw, self.userid)) |
284 for kw in except_kw: |
285 for kw in except_kw: |
285 qry = qry + ("EXCEPT " + basic_qry % (kw, self.userid)) |
286 qry += ("EXCEPT " + basic_qry % (kw, self.userid)) |
286 qry = qry + ';' |
287 return qry |
|
288 def select_bookmarks(self, has_kw = [0], except_kw = []): |
|
289 """ |
|
290 Return a list of id bookmarks who has /has_kw/ set but not /except_kw/. |
|
291 """ |
|
292 qry = self._bookmarks_selection(has_kw, except_kw) + ';' |
287 self.crs.execute(qry) |
293 self.crs.execute(qry) |
288 return self.crs.fetchall() |
294 return self.crs.fetchall() |
|
295 def get_bookmarks_count(self, has_kw = [0], except_kw = []): |
|
296 """ |
|
297 Return a count of bookmarks which would have been return on |
|
298 select_bookmarks qry. |
|
299 """ |
|
300 qry = """ |
|
301 SELECT COUNT(id) FROM bookmarks |
|
302 WHERE userid = %d AND id IN """ % self.userid |
|
303 qry += "(" + self._bookmarks_selection(has_kw, except_kw) + ");" |
|
304 self.crs.execute(qry) |
|
305 result = self.crs.fetchone() |
|
306 if result: |
|
307 return result[0] |
|
308 else: |
|
309 return 0 |
289 def get_folder_keyword(self, id): |
310 def get_folder_keyword(self, id): |
290 self.crs.execute(""" |
311 self.crs.execute(""" |
291 SELECT k.id, k.name |
312 SELECT k.id, k.name |
292 FROM keywords k, folders f |
313 FROM keywords k, folders f |
293 WHERE |
314 WHERE |