113 self.crs.execute(""" |
113 self.crs.execute(""" |
114 DELETE FROM bookmarks_keywords |
114 DELETE FROM bookmarks_keywords |
115 WHERE userid = %d AND bookmark = %d; |
115 WHERE userid = %d AND bookmark = %d; |
116 DELETE FROM bookmarks |
116 DELETE FROM bookmarks |
117 WHERE userid = %d AND id = %d; |
117 WHERE userid = %d AND id = %d; |
118 """ % (self.userid, id, userid, id)) |
118 """ % (self.userid, id, self.userid, id)) |
119 self.cnx.commit() |
119 self.cnx.commit() |
120 def add_keyword(self, name): |
120 def add_keyword(self, name): |
121 id = self.get_next_id('keywords') |
121 id = self.get_next_id('keywords') |
122 self.crs.execute(""" |
122 self.crs.execute(""" |
123 INSERT INTO keywords(id, userid, name) |
123 INSERT INTO keywords(id, userid, name) |
131 WHERE userid = %d AND id = %d; |
131 WHERE userid = %d AND id = %d; |
132 """ % (self.userid, id)) |
132 """ % (self.userid, id)) |
133 return self.crs.fetchone()[0] |
133 return self.crs.fetchone()[0] |
134 def get_keywords(self, bk_id): |
134 def get_keywords(self, bk_id): |
135 self.crs.execute(""" |
135 self.crs.execute(""" |
136 SELECT id, name FROM keywords, bookmarks_keywords |
136 SELECT k.id, k.name |
137 WHERE bookmarks_keywords.userid = %d |
137 FROM keywords k, bookmarks_keywords bk |
138 AND bookmarks_keywords.bookmark = %d |
138 WHERE bk.userid = %d |
139 AND keywords.id = bookmarks_keywords.keyword |
139 AND k.userid = %d |
140 ORDER BY id; |
140 AND bk.bookmark = %d |
141 """ % (self.userid, bk_id)) |
141 AND k.id = bk.keyword |
|
142 ORDER BY k.id; |
|
143 """ % (self.userid, self.userid, bk_id)) |
142 return self.crs.fetchall() |
144 return self.crs.fetchall() |
143 def update_keyword(self, id, name): |
145 def update_keyword(self, id, name): |
144 self.crs.execute(""" |
146 self.crs.execute(""" |
145 UPDATE keywords |
147 UPDATE keywords |
146 SET name = %s |
148 SET name = %s |
160 def merge_keywords(self, merge_id, into_id): |
162 def merge_keywords(self, merge_id, into_id): |
161 self.crs.execute(""" |
163 self.crs.execute(""" |
162 UPDATE bookmarks_keywords |
164 UPDATE bookmarks_keywords |
163 SET keyword = %d |
165 SET keyword = %d |
164 WHERE keyword = %d |
166 WHERE keyword = %d |
165 userid = %d |
167 AND userid = %d |
166 AND NOT ( bookmark IN |
168 AND bookmark NOT IN |
167 (SELECT bookmark FROM bookmarks_keywords |
169 (SELECT bookmark FROM bookmarks_keywords |
168 WHERE userid = %d AND keyword = %d )) |
170 WHERE userid = %d AND keyword = %d )) |
169 ; |
171 ; |
170 """ % (into_id, merge_id, self.userid, |
172 """ % (into_id, merge_id, self.userid, |
171 self.userid, merge_id)) |
173 self.userid, merge_id)) |
172 self.remove_keyword(merge_id) |
174 self.remove_keyword(merge_id) |
173 def get_all_keywords(self): |
175 def get_all_keywords(self): |
174 """Return a list of doublets [id, keyword, count].""" |
176 """Return a list of doublets [id, keyword, count].""" |
175 self.crs.execute("""SELECT k.id, k.name |
177 self.crs.execute("""SELECT k.id, k.name |
176 FROM keywords k |
178 FROM keywords k |
177 WHERE userid = %d |
179 WHERE k.userid = %d |
178 ORDER BY k.id;""" % (self.userid,)) |
180 ORDER BY k.id;""" % (self.userid,)) |
179 res = self.crs.fetchall() |
181 res = self.crs.fetchall() |
180 return res |
182 return res |
181 def get_keywords_count(self): |
183 def get_keywords_count(self): |
182 """Return a list of doublets [id, count]. |
184 """Return a list of doublets [id, count]. |
193 def select_bookmarks(self, has_kw = [0], except_kw = []): |
195 def select_bookmarks(self, has_kw = [0], except_kw = []): |
194 """ |
196 """ |
195 Return a list of id bookmarks who has /has_kw/ set but not /except_kw/. |
197 Return a list of id bookmarks who has /has_kw/ set but not /except_kw/. |
196 """ |
198 """ |
197 basic_qry = """ |
199 basic_qry = """ |
198 SELECT id FROM bookmarks, bookmarks_keywords |
200 SELECT bk.bookmark FROM bookmarks_keywords bk |
199 WHERE bookmarks.id = bookmarks_keywords.bookmark |
201 WHERE bk.keyword = %d |
200 AND bookmarks_keywords.keyword = %d |
202 AND bk.userid = %d |
201 AND bookmarks_keywords.userid = %d |
|
202 """ |
203 """ |
203 |
204 |
204 qry = basic_qry % (has_kw[0], self.userid) |
205 qry = basic_qry % (has_kw[0], self.userid) |
205 for kw in has_kw[1:]: |
206 for kw in has_kw[1:]: |
206 qry = qry + ("INTERSECT " + basic_qry % (kw, self.userid)) |
207 qry = qry + ("INTERSECT " + basic_qry % (kw, self.userid)) |
213 if seq_name == 'users': |
214 if seq_name == 'users': |
214 userid = -1 |
215 userid = -1 |
215 else: |
216 else: |
216 userid = self.userid |
217 userid = self.userid |
217 self.crs.execute(""" |
218 self.crs.execute(""" |
218 SELECT nextid FROM db_sequence WHERE |
219 SELECT nextid FROM db_sequence |
219 db_sequence.seq_name = '%s' |
220 WHERE seq_name = '%s' |
220 AND db_sequence.userid = %d |
221 AND userid = %d |
221 FOR UPDATE; |
222 FOR UPDATE; |
222 """ % (seq_name,userid)) |
223 """ % (seq_name,userid)) |
223 id = self.crs.fetchone()[0] |
224 id = self.crs.fetchone()[0] |
224 self.crs.execute(""" |
225 self.crs.execute(""" |
225 UPDATE db_sequence SET nextid = %d |
226 UPDATE db_sequence SET nextid = %d |