1 #!/usr/bin/python |
1 #!/usr/bin/python |
2 |
2 |
3 import pg |
3 import pgdb |
4 import config |
4 import config |
|
5 from bkmark import Bookmark |
5 |
6 |
6 class MyDbConnexion: |
7 class MyDbConnexion: |
7 def __init__(self): |
8 def __init__(self): |
8 self.cnx = pg.connect(config.database, config.host, |
9 self.cnx = pgdb.connect(database = config.database, |
9 config.port, None, None, config.user, config.passwd) |
10 host = config.host+':%d'%config.port, |
|
11 user = config.user, password = config.passwd) |
|
12 self.crs = self.cnx.cursor(); |
10 def add_bookmark(self, bk): |
13 def add_bookmark(self, bk): |
11 id = self.get_next_id('bookmarks') |
14 id = self.get_next_id('bookmarks') |
12 res = self.cnx.query(""" |
15 self.crs.execute(""" |
13 INSERT INTO bookmarks(id, url, name, ldesc, added) |
16 INSERT INTO bookmarks(id, url, name, ldesc, added) |
14 VALUES (%d, '%s', '%s', '%s', '%s'); |
17 VALUES (%d, '%s', '%s', '%s', '%s'); |
15 INSERT INTO bookmarks(bookmark, keyword) |
18 INSERT INTO bookmarks_keywords(bookmark, keyword) |
16 VALUES (%d, %d); |
19 VALUES (%d, %d); |
17 """ % (id, bk.url, bk.name, bk.desc, bk.added, |
20 """ % (id, bk.url, bk.name, bk.desc, bk.added, |
18 id, 0)) |
21 id, 0)) |
|
22 self.cnx.commit() |
19 return id |
23 return id |
20 def get_bookmark(self, id): |
24 def get_bookmarks(self, ids): |
21 bk = Bookmark() |
25 qry = """ |
22 res = self.cnx.query(""" |
26 SELECT id, url, name, ldesc, added |
23 SELECT url, name, ldesc, added |
|
24 FROM bookmarks |
27 FROM bookmarks |
25 WHERE id = %d; |
28 WHERE id = %d |
26 """ % (id,)) |
29 """ % (ids.pop(),) |
27 res = res.dictresult()[0] |
30 for id in ids: |
28 bk.url = res['url'] |
31 qry = qry + " OR id = %d" % (id,) |
29 bk.name = res['name'] |
32 qry = qry + ";" |
30 bk.desc = res['ldesc'] |
33 self.crs.execute(qry) |
31 bk.added = res['added'] |
34 bks = [] |
32 bk.id = id |
35 list = self.crs.fetchall() |
33 return bk |
36 for res in list: |
|
37 bk = Bookmark() |
|
38 bk.id = res[0] |
|
39 bk.url = res[1] |
|
40 bk.name = res[2] |
|
41 bk.desc = res[3] |
|
42 bk.added = res[4] |
|
43 bks.append(bk) |
|
44 return bks |
34 def update_bookmark(self, bk): |
45 def update_bookmark(self, bk): |
35 return self.cnx.query(""" |
46 self.crs.execute(""" |
36 UPDATE bookmarks |
47 UPDATE bookmarks |
37 SET url = '%s', name = '%s', |
48 SET url = '%s', name = '%s', |
38 ldesc = '%s', added = '%s' |
49 ldesc = '%s', added = '%s' |
39 WHERE id = %d; |
50 WHERE id = %d; |
40 """ % (bk.url, bk.name, bk.desc, bk.added, bk.id)) |
51 """ % (bk.url, bk.name, bk.desc, bk.added, bk.id)) |
|
52 self.cnx.commit() |
|
53 def update_keywords(self, id, keywords): |
|
54 self.crs.execute(""" |
|
55 DELETE FROM bookmarks_keywords |
|
56 WHERE bookmark = %d AND |
|
57 keyword <> 0;""" % (id,)) |
|
58 for kw in keywords: |
|
59 if kw == 0: continue |
|
60 self.crs.execute(""" |
|
61 INSERT INTO |
|
62 bookmarks_keywords(bookmark, keyword) |
|
63 VALUES (%d, %d);""" % (id, kw)) |
|
64 self.cnx.commit() |
41 def remove_bookmark(self, id): |
65 def remove_bookmark(self, id): |
42 return self.cnx.query(""" |
66 self.crs.execute(""" |
43 DELETE FROM bookmarks_keywords |
67 DELETE FROM bookmarks_keywords |
44 WHERE bookmark = %d; |
68 WHERE bookmark = %d; |
45 DELETE FROM bookmarks |
69 DELETE FROM bookmarks |
46 WHERE id = %d; |
70 WHERE id = %d; |
47 """ % (id, id)) |
71 """ % (id, id)) |
|
72 self.cnx.commit() |
48 def add_keyword(self, name): |
73 def add_keyword(self, name): |
49 id = self.get_next_id('keywords') |
74 id = self.get_next_id('keywords') |
50 res = self.cnx.query(""" |
75 self.crs.execute(""" |
51 INSERT INTO keywords(id, name) |
76 INSERT INTO keywords(id, name) |
52 VALUES (%d, '%s')' |
77 VALUES (%d, '%s')' |
53 """ % (id, name)) |
78 """ % (id, name)) |
|
79 self.cnx.commit() |
54 return id |
80 return id |
55 def get_keyword(self, id): |
81 def get_keyword(self, id): |
56 return self.cnx.query(""" |
82 self.crs.execute(""" |
57 SELECT name FROM keywords |
83 SELECT name FROM keywords |
58 WHERE keyword.id = %d; |
84 WHERE keyword.id = %d; |
59 """ % (id,)).getresult()[0][0] |
85 """ % (id,)) |
|
86 return self.crs.fetchone()[0] |
60 def get_keywords(self, bk_id): |
87 def get_keywords(self, bk_id): |
61 return self.cnx.query(""" |
88 self.crs.execute(""" |
62 SELECT id, name FROM keywords, bookmarks_keywords |
89 SELECT id, name FROM keywords, bookmarks_keywords |
63 WHERE keywords.id = bookmarks_keywords.keyword |
90 WHERE keywords.id = bookmarks_keywords.keyword |
64 AND bookmarks_keywords.bookmark = %d; |
91 AND bookmarks_keywords.bookmark = %d |
|
92 ORDER BY id; |
65 """ % (bk_id,)) |
93 """ % (bk_id,)) |
|
94 return self.crs.fetchall() |
66 def update_keyword(self, id, name): |
95 def update_keyword(self, id, name): |
67 return self.cnx.query(""" |
96 self.crs.execute(""" |
68 UPDATE keywords |
97 UPDATE keywords |
69 SET name = '%s' |
98 SET name = '%s' |
70 WHERE id = %d; |
99 WHERE id = %d; |
71 """ % (name, id)) |
100 """ % (name, id)) |
|
101 self.cnx.commit() |
72 def remove_keyword(self, id): |
102 def remove_keyword(self, id): |
73 if id == 0: |
103 if id == 0: |
74 raise "Can't remove default keyword!" |
104 raise "Can't remove default keyword!" |
75 return self.cnx.query(""" |
105 self.crs.execute(""" |
76 DELETE FROM bookmarks_keywords |
106 DELETE FROM bookmarks_keywords |
77 WHERE keyword = %d; |
107 WHERE keyword = %d; |
78 DELETE FROM keywords |
108 DELETE FROM keywords |
79 WHERE id = %d; |
109 WHERE id = %d; |
80 """ % (id, id)) |
110 """ % (id, id)) |
|
111 self.cnx.commit() |
81 def merge_keywords(self, merge_id, into_id): |
112 def merge_keywords(self, merge_id, into_id): |
82 self.cnx.query(""" |
113 self.crs.execute(""" |
83 UPDATE bookmarks_keywords |
114 UPDATE bookmarks_keywords |
84 SET keyword = %d |
115 SET keyword = %d |
85 WHERE keyword = %d; |
116 WHERE keyword = %d; |
86 """ % (into_id, merge_id)) |
117 """ % (into_id, merge_id)) |
87 self.remove_keyword(merge_id) |
118 self.remove_keyword(merge_id) |
88 def get_all_keywords(self): |
119 def get_all_keywords(self): |
89 self.cnx.query("SELECT id, name FROM keywords;" |
120 """Return a list of pair-elements [id, keyword].""" |
90 def get_bookmarks(self, has_kw, except_kw): |
121 self.crs.execute("SELECT id, name FROM keywords ORDER BY id;") |
|
122 return self.crs.fetchall() |
|
123 def select_bookmarks(self, has_kw = [0], except_kw = []): |
|
124 """ |
|
125 Return a list of id bookmarks who has /has_kw/ set but not /except_kw/. |
|
126 """ |
91 basic_qry = """ |
127 basic_qry = """ |
92 SELECT id, name, url FROM bookmarks, bookmarks_keywords |
128 SELECT id FROM bookmarks, bookmarks_keywords |
93 WHERE bookmarks.id = bookmarks_keywords.bookmark |
129 WHERE bookmarks.id = bookmarks_keywords.bookmark |
94 AND bookmarks_keywords.keyword = %d |
130 AND bookmarks_keywords.keyword = %d |
95 """ |
131 """ |
96 has_kw.reverse() |
132 |
97 kw = has_kw.pop() |
133 qry = basic_qry % has_kw[0] |
98 qry = basic_qry % kw |
134 for kw in has_kw[1:]: |
99 has_kw.reverse() |
135 qry = qry + ("INTERSECT " + basic_qry % kw) |
100 for kw in has_kw: |
|
101 qry = qry + ("INTERCEPT " + basic_qry % kw) |
|
102 for kw in except_kw: |
136 for kw in except_kw: |
103 qry = qry + ("EXCEPT " + basic_qry % kw) |
137 qry = qry + ("EXCEPT " + basic_qry % kw) |
104 qry = qry + ';' |
138 qry = qry + ';' |
|
139 self.crs.execute(qry) |
|
140 return self.crs.fetchall() |
105 def get_next_id(self, seq_name): |
141 def get_next_id(self, seq_name): |
106 id = self.cnx.query(""" |
142 self.crs.execute(""" |
107 SELECT nextid FROM db_sequence WHERE |
143 SELECT nextid FROM db_sequence WHERE |
108 db_sequence.seq_name = '%s'; |
144 db_sequence.seq_name = '%s' FOR UPDATE; |
109 """ % (seq_name)).getresult()[0][0] |
145 """ % (seq_name)) |
110 self.cnx.query(""" |
146 id = self.crs.fetchone()[0] |
|
147 self.crs.execute(""" |
111 UPDATE db_sequence SET nextid = %d |
148 UPDATE db_sequence SET nextid = %d |
112 WHERE seq_name = 'bookmarks'; |
149 WHERE seq_name = 'bookmarks'; |
113 """ % (id+1,)) |
150 """ % (id+1,)) |
|
151 return id |
114 |
152 |
115 def connect(): |
153 def connect(): |
116 return MyDbConnexion() |
154 return MyDbConnexion() |