1 #!/usr/bin/python |
1 #!/usr/bin/python |
2 |
2 |
3 import pg |
3 import pg |
|
4 import config |
4 |
5 |
5 host = 'www.tzone.org' |
6 class MyDbConnexion: |
6 user = 'php' |
7 def __init__(self): |
7 passwd = 'crow' |
8 self.cnx = pg.connect(config.database, config.host, |
8 database = 'bookmarker3' |
9 config.port, None, None, config.user, config.passwd) |
9 port = 10021 |
10 def add_bookmark(self, bk): |
|
11 id = self.get_next_id('bookmarks') |
|
12 res = self.cnx.query(""" |
|
13 INSERT INTO bookmarks(id, url, name, ldesc, added) |
|
14 VALUES (%d, '%s', '%s', '%s', '%s'); |
|
15 INSERT INTO bookmarks(bookmark, keyword) |
|
16 VALUES (%d, %d); |
|
17 """ % (id, bk.url, bk.name, bk.desc, bk.added, |
|
18 id, 0)) |
|
19 return id |
|
20 def get_bookmark(self, id): |
|
21 bk = Bookmark() |
|
22 res = self.cnx.query(""" |
|
23 SELECT url, name, ldesc, added |
|
24 FROM bookmarks |
|
25 WHERE id = %d; |
|
26 """ % (id,)) |
|
27 res = res.dictresult()[0] |
|
28 bk.url = res['url'] |
|
29 bk.name = res['name'] |
|
30 bk.desc = res['ldesc'] |
|
31 bk.added = res['added'] |
|
32 bk.id = id |
|
33 return bk |
|
34 def update_bookmark(self, bk): |
|
35 return self.cnx.query(""" |
|
36 UPDATE bookmarks |
|
37 SET url = '%s', name = '%s', |
|
38 ldesc = '%s', added = '%s' |
|
39 WHERE id = %d; |
|
40 """ % (bk.url, bk.name, bk.desc, bk.added, bk.id)) |
|
41 def remove_bookmark(self, id): |
|
42 return self.cnx.query(""" |
|
43 DELETE FROM bookmarks_keywords |
|
44 WHERE bookmark = %d; |
|
45 DELETE FROM bookmarks |
|
46 WHERE id = %d; |
|
47 """ % (id, id)) |
|
48 def add_keyword(self, name): |
|
49 id = self.get_next_id('keywords') |
|
50 res = self.cnx.query(""" |
|
51 INSERT INTO keywords(id, name) |
|
52 VALUES (%d, '%s')' |
|
53 """ % (id, name)) |
|
54 return id |
|
55 def get_keyword(self, id): |
|
56 return self.cnx.query(""" |
|
57 SELECT name FROM keywords |
|
58 WHERE keyword.id = %d; |
|
59 """ % (id,)).getresult()[0][0] |
|
60 def get_keywords(self, bk_id): |
|
61 return self.cnx.query(""" |
|
62 SELECT id, name FROM keywords, bookmarks_keywords |
|
63 WHERE keywords.id = bookmarks_keywords.keyword |
|
64 AND bookmarks_keywords.bookmark = %d; |
|
65 """ % (bk_id,)) |
|
66 def update_keyword(self, id, name): |
|
67 return self.cnx.query(""" |
|
68 UPDATE keywords |
|
69 SET name = '%s' |
|
70 WHERE id = %d; |
|
71 """ % (name, id)) |
|
72 def remove_keyword(self, id): |
|
73 if id == 0: |
|
74 raise "Can't remove default keyword!" |
|
75 return self.cnx.query(""" |
|
76 DELETE FROM bookmarks_keywords |
|
77 WHERE keyword = %d; |
|
78 DELETE FROM keywords |
|
79 WHERE id = %d; |
|
80 """ % (id, id)) |
|
81 def merge_keywords(self, merge_id, into_id): |
|
82 self.cnx.query(""" |
|
83 UPDATE bookmarks_keywords |
|
84 SET keyword = %d |
|
85 WHERE keyword = %d; |
|
86 """ % (into_id, merge_id)) |
|
87 self.remove_keyword(merge_id) |
|
88 def get_all_keywords(self): |
|
89 self.cnx.query("SELECT id, name FROM keywords;" |
|
90 def get_bookmarks(self, has_kw, except_kw): |
|
91 basic_qry = """ |
|
92 SELECT id, name, url FROM bookmarks, bookmarks_keywords |
|
93 WHERE bookmarks.id = bookmarks_keywords.bookmark |
|
94 AND bookmarks_keywords.keyword = %d |
|
95 """ |
|
96 has_kw.reverse() |
|
97 kw = has_kw.pop() |
|
98 qry = basic_qry % kw |
|
99 has_kw.reverse() |
|
100 for kw in has_kw: |
|
101 qry = qry + ("INTERCEPT " + basic_qry % kw) |
|
102 for kw in except_kw: |
|
103 qry = qry + ("EXCEPT " + basic_qry % kw) |
|
104 qry = qry + ';' |
|
105 def get_next_id(self, seq_name): |
|
106 id = self.cnx.query(""" |
|
107 SELECT nextid FROM db_sequence WHERE |
|
108 db_sequence.seq_name = '%s'; |
|
109 """ % (seq_name)).getresult()[0][0] |
|
110 self.cnx.query(""" |
|
111 UPDATE db_sequence SET nextid = %d |
|
112 WHERE seq_name = 'bookmarks'; |
|
113 """ % (id+1,)) |
10 |
114 |
11 def connect(): |
115 def connect(): |
12 return pg.connect(database, host, port, None, None, user, passwd); |
116 return MyDbConnexion() |