1 #!/usr/bin/python |
1 #!/usr/bin/python |
2 |
2 |
3 import pgdb |
3 import pgdb |
4 import config |
4 import config |
5 from bkmark import Bookmark |
5 from bkmark import Bookmark |
|
6 import os |
6 |
7 |
7 def sql_quote(str): |
8 def sql_quote(str): |
8 return pgdb._quote(str) |
9 return pgdb._quote(str) |
9 |
10 |
10 class MyDbConnexion: |
11 class MyDbConnexion: |
11 def __init__(self): |
12 def __init__(self, name): |
12 self.cnx = pgdb.connect(database = config.database, |
13 self.cnx = pgdb.connect(database = config.database, |
13 host = config.host+':%d'%config.port, |
14 host = config.host+':%d'%config.port, |
14 user = config.user, password = config.passwd) |
15 user = config.user, password = config.passwd) |
15 self.crs = self.cnx.cursor(); |
16 self.crs = self.cnx.cursor(); |
|
17 self.userid = self.get_user_id(name) |
|
18 if self.userid == None: |
|
19 self.userid = self.create_user(name) |
|
20 else: |
|
21 self.userid = self.userid[0] |
|
22 def get_user_id(self, name): |
|
23 self.crs.execute(""" |
|
24 SELECT id FROM users |
|
25 WHERE name = %s; |
|
26 """ % (sql_quote(name),)) |
|
27 return self.crs.fetchone() |
|
28 def create_user(self, name): |
|
29 id = self.get_next_id('users') |
|
30 self.crs.execute(""" |
|
31 INSERT INTO users(id, name) |
|
32 VALUES (%d, %s); |
|
33 """ % (id, sql_quote(name))) |
|
34 self.crs.execute(""" |
|
35 INSERT INTO keywords(id, userid, name) |
|
36 VALUES (0, %d, '--'); |
|
37 """ %(id,)) |
|
38 self.crs.execute(""" |
|
39 INSERT INTO db_sequence(seq_name, userid, nextid) |
|
40 VALUES ('keywords', %d, 1); |
|
41 """ %(id,)) |
|
42 self.crs.execute(""" |
|
43 INSERT INTO db_sequence(seq_name, userid, nextid) |
|
44 VALUES ('bookmarks', %d, 0); |
|
45 """ %(id,)) |
|
46 self.cnx.commit() |
|
47 return id |
|
48 def delete_user(self, userid): |
|
49 self.crs_execute(""" |
|
50 DELETE FROM users WHERE id = %d; |
|
51 DELETE FROM bookmarks_keywords WHERE userid = %d; |
|
52 DELETE FROM bookmarks WHERE userid = %d; |
|
53 DELETE FROM keywords WHERE userid = %d; |
|
54 DELETE FROM db_sequence WHERE userid = %d; |
|
55 """ % (userid,userid,userid,userid,userid)) |
16 def add_bookmark(self, bk): |
56 def add_bookmark(self, bk): |
17 id = self.get_next_id('bookmarks') |
57 id = self.get_next_id('bookmarks') |
18 self.crs.execute(""" |
58 self.crs.execute(""" |
19 INSERT INTO bookmarks(id, url, name, ldesc, added) |
59 INSERT INTO bookmarks(id, userid, url, name, ldesc, added) |
20 VALUES (%d, %s, %s, %s, %s); |
60 VALUES (%d, %d, %s, %s, %s, %s); |
21 INSERT INTO bookmarks_keywords(bookmark, keyword) |
61 INSERT INTO bookmarks_keywords(bookmark, keyword, userid) |
22 VALUES (%d, %d); |
62 VALUES (%d, 0, %d); |
23 """ % (id, sql_quote(bk.url), |
63 """ % (id, self.userid, sql_quote(bk.url), |
24 sql_quote(bk.name), sql_quote(bk.desc), |
64 sql_quote(bk.name), sql_quote(bk.desc), |
25 sql_quote(bk.added), id, 0)) |
65 sql_quote(bk.added), |
|
66 id, self.userid)) |
26 self.cnx.commit() |
67 self.cnx.commit() |
27 return id |
68 return id |
28 def get_bookmarks(self, ids): |
69 def get_bookmarks(self, ids): |
29 qry = """ |
70 qry = """ |
30 SELECT id, url, name, ldesc, added |
71 SELECT id, url, name, ldesc, added |
31 FROM bookmarks |
72 FROM bookmarks |
32 WHERE id = %d |
73 WHERE userid = %d AND (id = %d |
33 """ % (ids.pop(),) |
74 """ % (self.userid, ids.pop(),) |
34 for id in ids: |
75 for id in ids: |
35 qry = qry + " OR id = %d" % (id,) |
76 qry = qry + " OR id = %d" % (id,) |
36 qry = qry + ";" |
77 qry = qry + ");" |
37 self.crs.execute(qry) |
78 self.crs.execute(qry) |
38 bks = [] |
79 bks = [] |
39 list = self.crs.fetchall() |
80 list = self.crs.fetchall() |
40 for res in list: |
81 for res in list: |
41 bk = Bookmark() |
82 bk = Bookmark() |
49 def update_bookmark(self, bk): |
90 def update_bookmark(self, bk): |
50 self.crs.execute(""" |
91 self.crs.execute(""" |
51 UPDATE bookmarks |
92 UPDATE bookmarks |
52 SET url = %s, name = %s, |
93 SET url = %s, name = %s, |
53 ldesc = %s, added = %s |
94 ldesc = %s, added = %s |
54 WHERE id = %d; |
95 WHERE userid = %d AND id = %d; |
55 """ % (sql_quote(bk.url), sql_quote(bk.name), |
96 """ % (sql_quote(bk.url), sql_quote(bk.name), |
56 sql_quote(bk.desc), |
97 sql_quote(bk.desc), |
57 sql_quote(bk.added), bk.id)) |
98 sql_quote(bk.added), self.userid, bk.id)) |
58 self.cnx.commit() |
99 self.cnx.commit() |
59 def update_keywords(self, id, keywords): |
100 def update_keywords(self, id, keywords): |
60 self.crs.execute(""" |
101 self.crs.execute(""" |
61 DELETE FROM bookmarks_keywords |
102 DELETE FROM bookmarks_keywords |
62 WHERE bookmark = %d AND |
103 WHERE userid = %d AND bookmark = %d AND |
63 keyword <> 0;""" % (id,)) |
104 keyword <> 0;""" % (self.userid, id)) |
64 for kw in keywords: |
105 for kw in keywords: |
65 if kw == 0: continue |
106 if kw == 0: continue |
66 self.crs.execute(""" |
107 self.crs.execute(""" |
67 INSERT INTO |
108 INSERT INTO |
68 bookmarks_keywords(bookmark, keyword) |
109 bookmarks_keywords(bookmark, keyword, userid) |
69 VALUES (%d, %d);""" % (id, kw)) |
110 VALUES (%d, %d, %d);""" % (id, kw, self.userid)) |
70 self.cnx.commit() |
111 self.cnx.commit() |
71 def remove_bookmark(self, id): |
112 def remove_bookmark(self, id): |
72 self.crs.execute(""" |
113 self.crs.execute(""" |
73 DELETE FROM bookmarks_keywords |
114 DELETE FROM bookmarks_keywords |
74 WHERE bookmark = %d; |
115 WHERE userid = %d AND bookmark = %d; |
75 DELETE FROM bookmarks |
116 DELETE FROM bookmarks |
76 WHERE id = %d; |
117 WHERE userid = %d AND id = %d; |
77 """ % (id, id)) |
118 """ % (self.userid, id, userid, id)) |
78 self.cnx.commit() |
119 self.cnx.commit() |
79 def add_keyword(self, name): |
120 def add_keyword(self, name): |
80 id = self.get_next_id('keywords') |
121 id = self.get_next_id('keywords') |
81 self.crs.execute(""" |
122 self.crs.execute(""" |
82 INSERT INTO keywords(id, name) |
123 INSERT INTO keywords(id, userid, name) |
83 VALUES (%d, %s); |
124 VALUES (%d, %d, %s); |
84 """ % (id, sql_quote(name))) |
125 """ % (id, self.userid, sql_quote(name))) |
85 self.cnx.commit() |
126 self.cnx.commit() |
86 return id |
127 return id |
87 def get_keyword(self, id): |
128 def get_keyword(self, id): |
88 self.crs.execute(""" |
129 self.crs.execute(""" |
89 SELECT name FROM keywords |
130 SELECT name FROM keywords |
90 WHERE id = %d; |
131 WHERE userid = %d AND id = %d; |
91 """ % (id,)) |
132 """ % (self.userid, id)) |
92 return self.crs.fetchone()[0] |
133 return self.crs.fetchone()[0] |
93 def get_keywords(self, bk_id): |
134 def get_keywords(self, bk_id): |
94 self.crs.execute(""" |
135 self.crs.execute(""" |
95 SELECT id, name FROM keywords, bookmarks_keywords |
136 SELECT id, name FROM keywords, bookmarks_keywords |
96 WHERE keywords.id = bookmarks_keywords.keyword |
137 WHERE bookmarks_keywords.userid = %d |
97 AND bookmarks_keywords.bookmark = %d |
138 AND bookmarks_keywords.bookmark = %d |
|
139 AND keywords.id = bookmarks_keywords.keyword |
98 ORDER BY id; |
140 ORDER BY id; |
99 """ % (bk_id,)) |
141 """ % (self.userid, bk_id)) |
100 return self.crs.fetchall() |
142 return self.crs.fetchall() |
101 def update_keyword(self, id, name): |
143 def update_keyword(self, id, name): |
102 self.crs.execute(""" |
144 self.crs.execute(""" |
103 UPDATE keywords |
145 UPDATE keywords |
104 SET name = %s |
146 SET name = %s |
105 WHERE id = %d; |
147 WHERE userid = %d AND id = %d; |
106 """ % (sql_quote(name), id)) |
148 """ % (sql_quote(name), self.userid, id)) |
107 self.cnx.commit() |
149 self.cnx.commit() |
108 def remove_keyword(self, id): |
150 def remove_keyword(self, id): |
109 if id == 0: |
151 if id == 0: |
110 raise "Can't remove default keyword!" |
152 raise "Can't remove default keyword!" |
111 self.crs.execute(""" |
153 self.crs.execute(""" |
112 DELETE FROM bookmarks_keywords |
154 DELETE FROM bookmarks_keywords |
113 WHERE keyword = %d; |
155 WHERE keyword = %d AND userid = %d; |
114 DELETE FROM keywords |
156 DELETE FROM keywords |
115 WHERE id = %d; |
157 WHERE id = %d AND userid = %d; |
116 """ % (id, id)) |
158 """ % (id, self.userid, id, self.userid)) |
117 self.cnx.commit() |
159 self.cnx.commit() |
118 def merge_keywords(self, merge_id, into_id): |
160 def merge_keywords(self, merge_id, into_id): |
119 self.crs.execute(""" |
161 self.crs.execute(""" |
120 UPDATE bookmarks_keywords |
162 UPDATE bookmarks_keywords |
121 SET keyword = %d |
163 SET keyword = %d |
122 WHERE keyword = %d |
164 WHERE keyword = %d |
|
165 userid = %d |
123 AND NOT ( bookmark IN |
166 AND NOT ( bookmark IN |
124 (SELECT bookmark FROM bookmarks_keywords |
167 (SELECT bookmark FROM bookmarks_keywords |
125 WHERE keyword = %d )) |
168 WHERE userid = %d AND keyword = %d )) |
126 ; |
169 ; |
127 """ % (into_id, merge_id, merge_id)) |
170 """ % (into_id, merge_id, self.userid, |
|
171 self.userid, merge_id)) |
128 self.remove_keyword(merge_id) |
172 self.remove_keyword(merge_id) |
129 def get_all_keywords(self): |
173 def get_all_keywords(self): |
130 """Return a list of triplets [id, keyword, count].""" |
174 """Return a list of doublets [id, keyword, count].""" |
131 self.crs.execute("""SELECT k.id, k.name, COUNT(bk.keyword) |
175 self.crs.execute("""SELECT k.id, k.name |
132 FROM keywords k, bookmarks_keywords bk |
176 FROM keywords k |
133 WHERE k.id = bk.keyword |
177 WHERE userid = %d |
134 GROUP BY k.id, k.name |
178 ORDER BY k.id;""" % (self.userid,)) |
135 ORDER BY k.id;""") |
179 res = self.crs.fetchall() |
136 return self.crs.fetchall() |
180 return res |
|
181 def get_keywords_count(self): |
|
182 """Return a list of doublets [id, count]. |
|
183 Does with a count of zero are ignored.""" |
|
184 self.crs.execute("""SELECT bk.keyword, COUNT(bk.keyword) |
|
185 FROM bookmarks_keywords bk |
|
186 WHERE bk.userid = %d |
|
187 GROUP BY bk.keyword |
|
188 ORDER BY bk.keyword;""" % (self.userid,)) |
|
189 res = self.crs.fetchall() |
|
190 if not res: |
|
191 res = [[0,0]] |
|
192 return res |
137 def select_bookmarks(self, has_kw = [0], except_kw = []): |
193 def select_bookmarks(self, has_kw = [0], except_kw = []): |
138 """ |
194 """ |
139 Return a list of id bookmarks who has /has_kw/ set but not /except_kw/. |
195 Return a list of id bookmarks who has /has_kw/ set but not /except_kw/. |
140 """ |
196 """ |
141 basic_qry = """ |
197 basic_qry = """ |
142 SELECT id FROM bookmarks, bookmarks_keywords |
198 SELECT id FROM bookmarks, bookmarks_keywords |
143 WHERE bookmarks.id = bookmarks_keywords.bookmark |
199 WHERE bookmarks.id = bookmarks_keywords.bookmark |
144 AND bookmarks_keywords.keyword = %d |
200 AND bookmarks_keywords.keyword = %d |
|
201 AND bookmarks_keywords.userid = %d |
145 """ |
202 """ |
146 |
203 |
147 qry = basic_qry % has_kw[0] |
204 qry = basic_qry % (has_kw[0], self.userid) |
148 for kw in has_kw[1:]: |
205 for kw in has_kw[1:]: |
149 qry = qry + ("INTERSECT " + basic_qry % kw) |
206 qry = qry + ("INTERSECT " + basic_qry % (kw, self.userid)) |
150 for kw in except_kw: |
207 for kw in except_kw: |
151 qry = qry + ("EXCEPT " + basic_qry % kw) |
208 qry = qry + ("EXCEPT " + basic_qry % (kw, self.userid)) |
152 qry = qry + ';' |
209 qry = qry + ';' |
153 self.crs.execute(qry) |
210 self.crs.execute(qry) |
154 return self.crs.fetchall() |
211 return self.crs.fetchall() |
155 def get_next_id(self, seq_name): |
212 def get_next_id(self, seq_name): |
|
213 if seq_name == 'users': |
|
214 userid = -1 |
|
215 else: |
|
216 userid = self.userid |
156 self.crs.execute(""" |
217 self.crs.execute(""" |
157 SELECT nextid FROM db_sequence WHERE |
218 SELECT nextid FROM db_sequence WHERE |
158 db_sequence.seq_name = '%s' FOR UPDATE; |
219 db_sequence.seq_name = '%s' |
159 """ % (seq_name,)) |
220 AND db_sequence.userid = %d |
|
221 FOR UPDATE; |
|
222 """ % (seq_name,userid)) |
160 id = self.crs.fetchone()[0] |
223 id = self.crs.fetchone()[0] |
161 self.crs.execute(""" |
224 self.crs.execute(""" |
162 UPDATE db_sequence SET nextid = %d |
225 UPDATE db_sequence SET nextid = %d |
163 WHERE seq_name = '%s'; |
226 WHERE seq_name = '%s' |
164 """ % (id+1, seq_name)) |
227 AND userid = %d; |
165 return id |
228 """ % (id+1, seq_name, userid)) |
166 |
229 return id |
167 def connect(): |
230 |
168 return MyDbConnexion() |
231 def connect(username): |
|
232 return MyDbConnexion(username) |