diff -r 8c201fa3bdc7 -r 345ee7421989 lib/my_db.py --- a/lib/my_db.py Fri Sep 26 11:31:09 2003 -0400 +++ b/lib/my_db.py Fri Sep 26 18:18:33 2003 -0400 @@ -3,37 +3,78 @@ import pgdb import config from bkmark import Bookmark +import os def sql_quote(str): return pgdb._quote(str) class MyDbConnexion: - def __init__(self): + def __init__(self, name): self.cnx = pgdb.connect(database = config.database, host = config.host+':%d'%config.port, user = config.user, password = config.passwd) self.crs = self.cnx.cursor(); + self.userid = self.get_user_id(name) + if self.userid == None: + self.userid = self.create_user(name) + else: + self.userid = self.userid[0] + def get_user_id(self, name): + self.crs.execute(""" + SELECT id FROM users + WHERE name = %s; + """ % (sql_quote(name),)) + return self.crs.fetchone() + def create_user(self, name): + id = self.get_next_id('users') + self.crs.execute(""" + INSERT INTO users(id, name) + VALUES (%d, %s); + """ % (id, sql_quote(name))) + self.crs.execute(""" + INSERT INTO keywords(id, userid, name) + VALUES (0, %d, '--'); + """ %(id,)) + self.crs.execute(""" + INSERT INTO db_sequence(seq_name, userid, nextid) + VALUES ('keywords', %d, 1); + """ %(id,)) + self.crs.execute(""" + INSERT INTO db_sequence(seq_name, userid, nextid) + VALUES ('bookmarks', %d, 0); + """ %(id,)) + self.cnx.commit() + return id + def delete_user(self, userid): + self.crs_execute(""" + DELETE FROM users WHERE id = %d; + DELETE FROM bookmarks_keywords WHERE userid = %d; + DELETE FROM bookmarks WHERE userid = %d; + DELETE FROM keywords WHERE userid = %d; + DELETE FROM db_sequence WHERE userid = %d; + """ % (userid,userid,userid,userid,userid)) def add_bookmark(self, bk): id = self.get_next_id('bookmarks') self.crs.execute(""" - INSERT INTO bookmarks(id, url, name, ldesc, added) - VALUES (%d, %s, %s, %s, %s); - INSERT INTO bookmarks_keywords(bookmark, keyword) - VALUES (%d, %d); - """ % (id, sql_quote(bk.url), + INSERT INTO bookmarks(id, userid, url, name, ldesc, added) + VALUES (%d, %d, %s, %s, %s, %s); + INSERT INTO bookmarks_keywords(bookmark, keyword, userid) + VALUES (%d, 0, %d); + """ % (id, self.userid, sql_quote(bk.url), sql_quote(bk.name), sql_quote(bk.desc), - sql_quote(bk.added), id, 0)) + sql_quote(bk.added), + id, self.userid)) self.cnx.commit() return id def get_bookmarks(self, ids): qry = """ SELECT id, url, name, ldesc, added FROM bookmarks - WHERE id = %d - """ % (ids.pop(),) + WHERE userid = %d AND (id = %d + """ % (self.userid, ids.pop(),) for id in ids: qry = qry + " OR id = %d" % (id,) - qry = qry + ";" + qry = qry + ");" self.crs.execute(qry) bks = [] list = self.crs.fetchall() @@ -51,89 +92,104 @@ UPDATE bookmarks SET url = %s, name = %s, ldesc = %s, added = %s - WHERE id = %d; + WHERE userid = %d AND id = %d; """ % (sql_quote(bk.url), sql_quote(bk.name), sql_quote(bk.desc), - sql_quote(bk.added), bk.id)) + sql_quote(bk.added), self.userid, bk.id)) self.cnx.commit() def update_keywords(self, id, keywords): self.crs.execute(""" DELETE FROM bookmarks_keywords - WHERE bookmark = %d AND - keyword <> 0;""" % (id,)) + WHERE userid = %d AND bookmark = %d AND + keyword <> 0;""" % (self.userid, id)) for kw in keywords: if kw == 0: continue self.crs.execute(""" INSERT INTO - bookmarks_keywords(bookmark, keyword) - VALUES (%d, %d);""" % (id, kw)) + bookmarks_keywords(bookmark, keyword, userid) + VALUES (%d, %d, %d);""" % (id, kw, self.userid)) self.cnx.commit() def remove_bookmark(self, id): self.crs.execute(""" DELETE FROM bookmarks_keywords - WHERE bookmark = %d; + WHERE userid = %d AND bookmark = %d; DELETE FROM bookmarks - WHERE id = %d; - """ % (id, id)) + WHERE userid = %d AND id = %d; + """ % (self.userid, id, userid, id)) self.cnx.commit() def add_keyword(self, name): id = self.get_next_id('keywords') self.crs.execute(""" - INSERT INTO keywords(id, name) - VALUES (%d, %s); - """ % (id, sql_quote(name))) + INSERT INTO keywords(id, userid, name) + VALUES (%d, %d, %s); + """ % (id, self.userid, sql_quote(name))) self.cnx.commit() return id def get_keyword(self, id): self.crs.execute(""" SELECT name FROM keywords - WHERE id = %d; - """ % (id,)) + WHERE userid = %d AND id = %d; + """ % (self.userid, id)) return self.crs.fetchone()[0] def get_keywords(self, bk_id): self.crs.execute(""" SELECT id, name FROM keywords, bookmarks_keywords - WHERE keywords.id = bookmarks_keywords.keyword + WHERE bookmarks_keywords.userid = %d AND bookmarks_keywords.bookmark = %d + AND keywords.id = bookmarks_keywords.keyword ORDER BY id; - """ % (bk_id,)) + """ % (self.userid, bk_id)) return self.crs.fetchall() def update_keyword(self, id, name): self.crs.execute(""" UPDATE keywords SET name = %s - WHERE id = %d; - """ % (sql_quote(name), id)) + WHERE userid = %d AND id = %d; + """ % (sql_quote(name), self.userid, id)) self.cnx.commit() def remove_keyword(self, id): if id == 0: raise "Can't remove default keyword!" self.crs.execute(""" DELETE FROM bookmarks_keywords - WHERE keyword = %d; + WHERE keyword = %d AND userid = %d; DELETE FROM keywords - WHERE id = %d; - """ % (id, id)) + WHERE id = %d AND userid = %d; + """ % (id, self.userid, id, self.userid)) self.cnx.commit() def merge_keywords(self, merge_id, into_id): self.crs.execute(""" UPDATE bookmarks_keywords SET keyword = %d WHERE keyword = %d + userid = %d AND NOT ( bookmark IN (SELECT bookmark FROM bookmarks_keywords - WHERE keyword = %d )) + WHERE userid = %d AND keyword = %d )) ; - """ % (into_id, merge_id, merge_id)) + """ % (into_id, merge_id, self.userid, + self.userid, merge_id)) self.remove_keyword(merge_id) def get_all_keywords(self): - """Return a list of triplets [id, keyword, count].""" - self.crs.execute("""SELECT k.id, k.name, COUNT(bk.keyword) - FROM keywords k, bookmarks_keywords bk - WHERE k.id = bk.keyword - GROUP BY k.id, k.name - ORDER BY k.id;""") - return self.crs.fetchall() + """Return a list of doublets [id, keyword, count].""" + self.crs.execute("""SELECT k.id, k.name + FROM keywords k + WHERE userid = %d + ORDER BY k.id;""" % (self.userid,)) + res = self.crs.fetchall() + return res + def get_keywords_count(self): + """Return a list of doublets [id, count]. +Does with a count of zero are ignored.""" + self.crs.execute("""SELECT bk.keyword, COUNT(bk.keyword) + FROM bookmarks_keywords bk + WHERE bk.userid = %d + GROUP BY bk.keyword + ORDER BY bk.keyword;""" % (self.userid,)) + res = self.crs.fetchall() + if not res: + res = [[0,0]] + return res def select_bookmarks(self, has_kw = [0], except_kw = []): """ Return a list of id bookmarks who has /has_kw/ set but not /except_kw/. @@ -142,27 +198,35 @@ SELECT id FROM bookmarks, bookmarks_keywords WHERE bookmarks.id = bookmarks_keywords.bookmark AND bookmarks_keywords.keyword = %d + AND bookmarks_keywords.userid = %d """ - qry = basic_qry % has_kw[0] + qry = basic_qry % (has_kw[0], self.userid) for kw in has_kw[1:]: - qry = qry + ("INTERSECT " + basic_qry % kw) + qry = qry + ("INTERSECT " + basic_qry % (kw, self.userid)) for kw in except_kw: - qry = qry + ("EXCEPT " + basic_qry % kw) + qry = qry + ("EXCEPT " + basic_qry % (kw, self.userid)) qry = qry + ';' self.crs.execute(qry) return self.crs.fetchall() def get_next_id(self, seq_name): + if seq_name == 'users': + userid = -1 + else: + userid = self.userid self.crs.execute(""" SELECT nextid FROM db_sequence WHERE - db_sequence.seq_name = '%s' FOR UPDATE; - """ % (seq_name,)) + db_sequence.seq_name = '%s' + AND db_sequence.userid = %d + FOR UPDATE; + """ % (seq_name,userid)) id = self.crs.fetchone()[0] self.crs.execute(""" UPDATE db_sequence SET nextid = %d - WHERE seq_name = '%s'; - """ % (id+1, seq_name)) + WHERE seq_name = '%s' + AND userid = %d; + """ % (id+1, seq_name, userid)) return id -def connect(): - return MyDbConnexion() +def connect(username): + return MyDbConnexion(username)