[svn r1555] Add multiuser support and more templating.
--- a/TODO Fri Sep 26 11:31:09 2003 -0400
+++ b/TODO Fri Sep 26 18:18:33 2003 -0400
@@ -4,8 +4,8 @@
* Add Search capabilities.
* Add better navigation.
* Add default (dynamic?) tree expansion.
-* Add multiuser support.
* Add XBel Import/Export.
* Add support for icons.
* Add modified time.
* Add access/check time.
+* Add user session support.
--- a/add.py Fri Sep 26 11:31:09 2003 -0400
+++ b/add.py Fri Sep 26 18:18:33 2003 -0400
@@ -15,10 +15,12 @@
import my_db
import time
from bkmark import Bookmark
+import os
def main(keywords, title, url):
tmpl = TemplateManager().prepare("add_bk.tmpl")
tproc = TemplateProcessor()
+ tproc.set("pagetitle", os.environ["REMOTE_USER"]+"'s XBELWeb Edit Bookmark")
tproc.set('ctitle', title)
tproc.set('curl', url)
tproc.set('desc', '')
@@ -33,7 +35,7 @@
name = form["ctitle"].value
if form.has_key("curl"):
url = form["curl"].value
- db = my_db.connect()
+ db = my_db.connect(os.environ["REMOTE_USER"])
kw = db.get_all_keywords()[1:]
kw.sort(lambda l,r: cmp(l[1],r[1]))
kw = map(lambda elem: {
--- a/add_bk.tmpl Fri Sep 26 11:31:09 2003 -0400
+++ b/add_bk.tmpl Fri Sep 26 18:18:33 2003 -0400
@@ -1,9 +1,7 @@
<html>
- <head>
- <title>Edit a bookmark</title>
- </head>
+ <TMPL_INCLUDE NAME="headers.tmpl">
<body>
- <h1>Edit a bookmark</h1>
+ <h1><TMPL_VAR pagetitle></h1>
<form action="add_confirm.py" method="post" name="add">
<fieldset class="bookmark">
<input type="hidden" name="id" value="<TMPL_VAR bkid>"/>
@@ -18,7 +16,7 @@
<button type="submit" name="action" value="cancel">Cancel</button>
</fieldset>
<fieldset class="keywords">
- <legend>Mots clés:</legend>
+ <legend>Keywords:</legend>
<p>
<TMPL_LOOP Keywords>
<input type="checkbox" name="kw"
--- a/add_confirm.py Fri Sep 26 11:31:09 2003 -0400
+++ b/add_confirm.py Fri Sep 26 18:18:33 2003 -0400
@@ -19,6 +19,7 @@
from bkmark import Bookmark
from index import load_index
from utils import unique
+import os
def get_bk_from_form(form):
bk = Bookmark()
@@ -52,11 +53,13 @@
kw = get_kw_from_form(form)
kw = map(db.get_keyword, kw)
kw = kw + get_new_kw_from_form(form)
- return unique(kw)
+ kw = unique(kw)
+ return kw
def main(action, bk, kw):
tmpl = TemplateManager().prepare("add_confirm.tmpl")
tproc = TemplateProcessor()
+ tproc.set("pagetitle", os.environ["REMOTE_USER"]+"'s XBELWeb Confirmation")
tproc.set("confirm_delete", action == 'delete')
tproc.set("confirm_update", action == 'update')
tproc.set("action", action)
@@ -68,7 +71,7 @@
if (__name__ == "__main__"):
form = cgi.FieldStorage()
- db = my_db.connect()
+ db = my_db.connect(os.environ["REMOTE_USER"])
id = int(form['id'].value)
action = form['action'].value
if action == 'cancel' or (action == 'delete' and id == -1):
@@ -80,5 +83,6 @@
else:
bk = db.get_bookmarks([id])[0]
(ids, kw) = apply(zip,db.get_keywords(id))
- main(action, bk, kw[1:])
+ kw = kw[1:]
+ main(action, bk, kw)
--- a/add_confirm.tmpl Fri Sep 26 11:31:09 2003 -0400
+++ b/add_confirm.tmpl Fri Sep 26 18:18:33 2003 -0400
@@ -1,12 +1,8 @@
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-15">
- <title>Fabien's XBelWeb</title>
- </head>
+ <TMPL_INCLUDE NAME="headers.tmpl">
<body>
- <h1>Fabien's XBelWeb</h1>
- <h2>Confirmation</h2>
+ <h1><TMPL_VAR pagetitle></h1>
<form name="confirmation" method="post" action="add_result.py">
<fieldset>
<legend>Bookmark</legend>
--- a/add_result.py Fri Sep 26 11:31:09 2003 -0400
+++ b/add_result.py Fri Sep 26 18:18:33 2003 -0400
@@ -17,11 +17,12 @@
import my_db
from add_confirm import get_bk_from_form, get_new_kw_from_form
from index import load_index
+import os
def add_new_keywords(db, newkw):
kw = [0] # Default keyword, so that every bookmarks appears in
# the bookmarks_keywords db.
- (ids, kws, cnt) = map(list,apply(zip, db.get_all_keywords()))
+ (ids, kws) = map(list,apply(zip, db.get_all_keywords()))
for elem in unique(newkw):
if elem in kws:
kw.append(ids[kws.index(elem)])
@@ -33,6 +34,7 @@
def main(bk, kw):
tmpl = TemplateManager().prepare("add_result.tmpl")
tproc = TemplateProcessor()
+ tproc.set("pagetitle", os.environ["REMOTE_USER"]+"'s XBELWeb Result")
tproc.set("url", bk.url)
tproc.set("name", bk.name)
tproc.set("added", bk.added)
@@ -42,7 +44,7 @@
if (__name__ == "__main__"):
form = cgi.FieldStorage()
- db = my_db.connect()
+ db = my_db.connect(os.environ["REMOTE_USER"])
action = form['action'].value
if action == 'cancel':
load_index(db)
--- a/add_result.tmpl Fri Sep 26 11:31:09 2003 -0400
+++ b/add_result.tmpl Fri Sep 26 18:18:33 2003 -0400
@@ -1,11 +1,8 @@
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-15">
- <title>Fabien's XBelWeb</title>
- </head>
+ <TMPL_INCLUDE NAME="headers.tmpl">
<body>
- <h1>Fabien's XBelWeb</h1>
+ <h1><TMPL_VAR pagetitle></h1>
<p>Bookmark successfully added:</p>
<p><a href="<TMPL_VAR url>"><TMPL_VAR name></a><p>
<p><TMPL_VAR desc></p>
@@ -33,4 +30,5 @@
sgml-local-catalogs:nil
sgml-local-ecat-files:nil
End:
+vi: syntax=html
-->
--- a/do_edit_kw.py Fri Sep 26 11:31:09 2003 -0400
+++ b/do_edit_kw.py Fri Sep 26 18:18:33 2003 -0400
@@ -15,18 +15,12 @@
import my_db
import time
from bkmark import Bookmark
+import os
-if (__name__ == "__main__"):
- form = cgi.FieldStorage()
- db = my_db.connect()
- id = int(form['id'].value)
- action = form['action'].value
- kwname = db.get_keyword(id)
- name = form['name'].value
- merge = int(form['keywords'].value)
- mergename = db.get_keyword(merge)
+def main(action, id, kwname, merge, mergename):
tmpl = TemplateManager().prepare("kw_confirm.tmpl")
tproc = TemplateProcessor()
+ tproc.set("pagetitle", os.environ["REMOTE_USER"]+"'s XBELWeb Confirmation")
tproc.set("confirm_delete", action == 'delete')
tproc.set("confirm_merge", action == 'merge')
tproc.set("confirm_update", action == 'update')
@@ -38,3 +32,14 @@
tproc.set("mergename", mergename)
print tproc.process(tmpl)
+if (__name__ == "__main__"):
+ form = cgi.FieldStorage()
+ db = my_db.connect(os.environ["REMOTE_USER"])
+ id = int(form['id'].value)
+ action = form['action'].value
+ kwname = db.get_keyword(id)
+ name = form['name'].value
+ merge = int(form['keywords'].value)
+ mergename = db.get_keyword(merge)
+ main(action, id, kwname, name, merge, mergename)
+
--- a/edit.py Fri Sep 26 11:31:09 2003 -0400
+++ b/edit.py Fri Sep 26 18:18:33 2003 -0400
@@ -15,10 +15,12 @@
import my_db
import time
from bkmark import Bookmark
+import os
def main(keywords, bk):
tmpl = TemplateManager().prepare("add_bk.tmpl")
tproc = TemplateProcessor()
+ tproc.set("pagetitle", os.environ["REMOTE_USER"]+"'s XBELWeb Confirmation")
tproc.set('ctitle', bk.name)
tproc.set('curl', bk.url)
tproc.set('desc', bk.desc)
@@ -29,7 +31,7 @@
if (__name__ == "__main__"):
form = cgi.FieldStorage()
id = int(form["id"].value)
- db = my_db.connect()
+ db = my_db.connect(os.environ["REMOTE_USER"])
bk = db.get_bookmarks([id])[0]
kw = db.get_all_keywords()[1:]
kw.sort(lambda l,r: cmp(l[1],r[1]))
--- a/edit_kw.py Fri Sep 26 11:31:09 2003 -0400
+++ b/edit_kw.py Fri Sep 26 18:18:33 2003 -0400
@@ -14,10 +14,12 @@
from htmltmpl import TemplateManager, TemplateProcessor
import my_db
import time
+import os
def main(keyword, id, keywords):
tmpl = TemplateManager().prepare("edit_kw.tmpl")
tproc = TemplateProcessor()
+ tproc.set("pagetitle", os.environ["REMOTE_USER"]+"'s XBELWeb Edit Keyword")
tproc.set('keyword', keyword)
tproc.set('id', id)
tproc.set('Keywords', keywords)
@@ -26,7 +28,7 @@
if (__name__ == "__main__"):
form = cgi.FieldStorage()
id = int(form["id"].value)
- db = my_db.connect()
+ db = my_db.connect(os.environ["REMOTE_USER"])
name = db.get_keyword(id)
kw = db.get_all_keywords()[1:]
kw.sort(lambda l,r: cmp(l[1],r[1]))
--- a/edit_kw.tmpl Fri Sep 26 11:31:09 2003 -0400
+++ b/edit_kw.tmpl Fri Sep 26 18:18:33 2003 -0400
@@ -1,9 +1,7 @@
<html>
- <head>
- <title>Edit a keyword</title>
- </head>
+ <TMPL_INCLUDE NAME="headers.tmpl">
<body>
- <h1>Edit a keyword</h1>
+ <h1><TMPL_VAR pagetitle></h1>
<form action="do_edit_kw.py" method="post" name="edit">
<input type="hidden" name="id" value="<TMPL_VAR id>"/>
<label>Name: <input type="text" name="name"
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/inc/headers.tmpl Fri Sep 26 18:18:33 2003 -0400
@@ -0,0 +1,6 @@
+<head>
+ <title><TMPL_VAR pagetitle></title>
+ <meta name="generator" content="XBelWeb"/>
+ <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"
+ <link rel="home" href="index.py" title="XBelWeb's Main Page"/>
+</head>
--- a/inc/keywords.tmpl Fri Sep 26 11:31:09 2003 -0400
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,6 +0,0 @@
-<p>
-<TMPL_LOOP Keywords>
- <TMPL_VAR keyword>;
-</TMPL_LOOP>
-</p>
-
--- a/index.py Fri Sep 26 11:31:09 2003 -0400
+++ b/index.py Fri Sep 26 18:18:33 2003 -0400
@@ -26,23 +26,27 @@
kw = [0]
return map(int, kw)
-def set_selection(kw, sel):
+def set_selection(db, sel):
+ allkw = db.get_all_keywords()
+ (kw, cnt) = map(list,apply(zip, db.get_keywords_count()))
res = []
- for key in kw:
+ for key in allkw:
chk = key[0] in sel
+ if key[0] in kw:
+ kcnt = cnt[kw.index(key[0])]
+ else:
+ kcnt = 0
res.append({'id':key[0],
'keyword': key[1],
- 'count' : key[2],
+ 'count' : kcnt,
'checked' : chk})
- res.sort(lambda l,r: cmp(l["keyword"],r["keyword"]))
return res
def get_curl():
return urljoin( 'http://' + environ["HTTP_HOST"] + environ["REQUEST_URI"], 'add.py')
def load_index(db):
- kw = db.get_all_keywords()
- kw = set_selection(kw, [])
+ kw = set_selection(db, [])
total = kw[0]['count']
kw = kw[1:]
exc = map(lambda e: int(e['id']), kw)
@@ -56,7 +60,10 @@
def main(bk, kw, curl, total):
tmpl = TemplateManager().prepare("index.tmpl")
tproc = TemplateProcessor()
+ tproc.set("pagetitle", environ["REMOTE_USER"]+"'s XBELWeb")
+ bk.sort(lambda x,y: cmp(x['name'],y['name']))
tproc.set("Bookmarks", bk)
+ kw.sort(lambda x,y: cmp(x['keyword'],y['keyword']))
tproc.set("Keywords", kw)
tproc.set("curl", curl)
tproc.set("total", total)
@@ -64,17 +71,16 @@
if (__name__ == "__main__"):
form = cgi.FieldStorage()
- db = my_db.connect()
- keywords = db.get_all_keywords()
- total = keywords[0][2]
+ db = my_db.connect(environ["REMOTE_USER"])
+ selection = get_selection(form)
+ keywords = set_selection(db, selection)
+ total = keywords[0]['count']
keywords = keywords[1:]
- selection = get_selection(form)
if selection[0] == 0:
- exc = map(lambda e: int(e[0]), keywords)
+ exc = map(lambda e: int(e['id']), keywords)
bookmarks = db.select_bookmarks([0], exc)
else:
bookmarks = db.select_bookmarks(selection)
- keywords = set_selection(keywords, selection)
if len(bookmarks) > 0:
bookmarks = db.get_bookmarks(map(lambda x: x[0], bookmarks))
bookmarks = map(lambda bk: bk.dict(), bookmarks)
--- a/index.tmpl Fri Sep 26 11:31:09 2003 -0400
+++ b/index.tmpl Fri Sep 26 18:18:33 2003 -0400
@@ -1,11 +1,8 @@
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-15">
- <title>Fabien's XBelWeb</title>
- </head>
+ <TMPL_INCLUDE NAME="headers.tmpl">
<body>
- <h1>Fabien's XBelWeb</h1>
+ <h1><TMPL_VAR pagetitle></h1>
<p>Total bookmarks: <TMPL_VAR total></p>
<form action="index.py" method="get" name="keywords">
<fieldset class="keywords">
@@ -32,7 +29,9 @@
<hr/>
<p>Here the <a
href="javascript:bk1='<TMPL_VAR curl>?curl='+escape(location.href)+'&ctitle='+escape(document.title);bkwin=window.open(bk1,'bkqm','width=620,height=500,scrollbars=1,resizable=1');bkwin.focus();">Quick
- Mark Link</a>.</p>
+ Mark Link</a>. Put it on your bookmarks (using <tt><Right-Mouse
+ Click><tt>Bookmark this link</tt>) and just select it when you
+ want to bookmark your links.</p>
</body>
</html>
<!-- Keep this comment at the end of the file
--- a/kw_confirm.tmpl Fri Sep 26 11:31:09 2003 -0400
+++ b/kw_confirm.tmpl Fri Sep 26 18:18:33 2003 -0400
@@ -1,12 +1,8 @@
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-15">
- <title>Fabien's XBelWeb</title>
- </head>
+ <TMPL_INCLUDE NAME="headers.tmpl">
<body>
- <h1>Fabien's XBelWeb</h1>
- <h2>Confirmation</h2>
+ <h1><TMPL_VAR pagetitle></h1>
<form name="confirmation" method="post" action="kw_result.py">
<input type="hidden" name="id" value="<TMPL_VAR id>"/>
<input type="hidden" name="merge" value="<TMPL_VAR mergeid>"/>
--- a/kw_result.py Fri Sep 26 11:31:09 2003 -0400
+++ b/kw_result.py Fri Sep 26 18:18:33 2003 -0400
@@ -11,10 +11,8 @@
# import cgitb; cgitb.enable()
import cgi
-from htmltmpl import TemplateManager, TemplateProcessor
import my_db
from os import environ
-from urlparse import urljoin
from index import load_index
def do_merge(form, db):
@@ -33,7 +31,7 @@
if (__name__ == "__main__"):
form = cgi.FieldStorage()
- db = my_db.connect()
+ db = my_db.connect(environ["REMOTE_USER"])
action = form['action'].value
if action == 'merge':
do_merge(form, db)
--- a/lib/create_db.sql Fri Sep 26 11:31:09 2003 -0400
+++ b/lib/create_db.sql Fri Sep 26 18:18:33 2003 -0400
@@ -1,29 +1,39 @@
+--
+
+CREATE TABLE users (
+ id int NOT NULL,
+ name varchar(255) DEFAULT '' NOT NULL,
+ PRIMARY KEY (id)
+);
+
+CREATE INDEX index_users_name ON users(name);
+
--
-- Table structure for table 'bookmark'
--
CREATE TABLE bookmarks (
id int NOT NULL,
+ userid int NOT NULL,
url varchar(255) DEFAULT '' NOT NULL,
name varchar(255) DEFAULT '' NOT NULL,
ldesc varchar(255) DEFAULT '' NOT NULL,
added date DEFAULT '1998-01-01' NOT NULL,
- PRIMARY KEY (id)
+ PRIMARY KEY (id, userid)
);
+CREATE INDEX index_bookmarks_userid ON bookmarks(userid);
+
--
-- Table structure for table 'keywords'
--
CREATE TABLE keywords (
id int DEFAULT '0' NOT NULL,
+ userid int DEFAULT '0' NOT NULL,
name varchar(30) DEFAULT '' NOT NULL,
- PRIMARY KEY (id)
+ PRIMARY KEY (id, userid)
);
---
--- Default nil keyword
---
-
-INSERT INTO keywords(id, name) VALUES (0, '--');
+CREATE INDEX index_keywords_name ON keywords(userid, name);
--
-- Cross reference table for linking keywords and bookmarks
@@ -32,19 +42,22 @@
CREATE TABLE bookmarks_keywords (
bookmark int NOT NULL,
keyword int DEFAULT '0' NOT NULL,
- PRIMARY KEY (bookmark, keyword)
+ userid int DEFAULT '0' NOT NULL,
+ PRIMARY KEY (bookmark, keyword, userid)
);
-CREATE INDEX bkkw_kw_idx ON bookmarks_keywords (keyword);
+CREATE INDEX index_bkkw_kwuser ON bookmarks_keywords (keyword, userid);
+CREATE INDEX index_bkkw_user ON bookmarks_keywords (userid);
+CREATE INDEX index_bkkw_bkuser ON bookmarks_keywords (bookmark, userid);
--
-- Table structure for table 'db_sequence'
--
CREATE TABLE db_sequence (
seq_name varchar(30) DEFAULT '' NOT NULL,
+ userid int DEFAULT '0' NOT NULL,
nextid int DEFAULT '0' NOT NULL,
- PRIMARY KEY (seq_name)
+ PRIMARY KEY (seq_name, userid)
);
-INSERT INTO db_sequence (seq_name, nextid) VALUES ('bookmarks', 0);
-INSERT INTO db_sequence (seq_name, nextid) VALUES ('keywords' , 1);
+INSERT INTO db_sequence (seq_name, userid, nextid) VALUES ('users' , -1, 0);
--- 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)