--- 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)