diff --git a/modules/webbasket/lib/webbasket_dblayer.py b/modules/webbasket/lib/webbasket_dblayer.py
index c26f2921e..6d3157919 100644
--- a/modules/webbasket/lib/webbasket_dblayer.py
+++ b/modules/webbasket/lib/webbasket_dblayer.py
@@ -1,2300 +1,2296 @@
# -*- coding: utf-8 -*-
##
## This file is part of Invenio.
## Copyright (C) 2006, 2007, 2008, 2009, 2010, 2011 CERN.
##
## Invenio is free software; you can redistribute it and/or
## modify it under the terms of the GNU General Public License as
## published by the Free Software Foundation; either version 2 of the
## License, or (at your option) any later version.
##
## Invenio is distributed in the hope that it will be useful, but
## WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
## General Public License for more details.
##
## You should have received a copy of the GNU General Public License
## along with Invenio; if not, write to the Free Software Foundation, Inc.,
## 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
""" Database related functions for webbasket module """
__revision__ = "$Id$"
from zlib import decompress
from zlib import compress
from time import localtime
from invenio.textutils import encode_for_xml
from invenio.dbquery import run_sql
from invenio.webcomment import get_reply_order_cache_data
from invenio.webbasket_config import CFG_WEBBASKET_SHARE_LEVELS, \
CFG_WEBBASKET_ACTIONS, \
CFG_WEBBASKET_SHARE_LEVELS_ORDERED, \
CFG_WEBBASKET_MAX_COMMENT_THREAD_DEPTH
from invenio.dateutils import convert_datestruct_to_datetext
from invenio.websession_config import CFG_WEBSESSION_USERGROUP_STATUS
########################### Table of contents ################################
#
# NB. functions preceeded by a star use usergroup table
#
# 1. General functions
# - count_baskets
# - check_user_owns_basket
# - get_max_user_rights_on_basket
#
# 2. Personal baskets
# - get_personal_baskets_info_for_topic
# - get_all_personal_basket_ids_and_names_by_topic
# - get_all_personal_baskets_names
# - get_basket_name
# - is_personal_basket_valid
# - is_topic_valid
# - get_basket_topic
# - get_personal_topics_infos
# - rename_basket
# - rename_topic
# - move_baskets_to_topic
# - delete_basket
# - create_basket
#
# 3. Actions on baskets
# - get_basket_record
# - get_basket_content
# - get_basket_item
# - share_basket_with_group
# - update_rights
# - move_item
# - delete_item
# - add_to_basket
# - get_external_records_by_collection
# - store_external_records
# - store_external_urls
# - store_external_source
# - get_external_colid_and_url
#
# 4. Group baskets
# - get_group_basket_infos
# - get_group_name
# - get_all_group_basket_ids_and_names_by_group
# - (*) get_all_group_baskets_names
# - is_shared_to
#
# 5. External baskets (baskets user has subscribed to)
# - get_external_baskets_infos
# - get_external_basket_info
# - get_all_external_basket_ids_and_names
# - count_external_baskets
# - get_all_external_baskets_names
#
# 6. Public baskets (interface to subscribe to baskets)
# - get_public_basket_infos
# - get_public_basket_info
# - get_basket_general_infos
# - get_basket_owner_id
# - count_public_baskets
# - get_public_baskets_list
# - is_basket_public
# - subscribe
# - unsubscribe
# - is_user_subscribed_to_basket
# - count_subscribers
# - (*) get_groups_subscribing_to_basket
# - get_rights_on_public_basket
#
# 7. Annotating
# - get_notes
# - get_note
# - save_note
# - delete_note
# - note_belongs_to_item_in_basket_p
#
# 8. Usergroup functions
# - (*) get_group_infos
# - count_groups_user_member_of
# - (*) get_groups_user_member_of
#
# 9. auxilliary functions
# - __wash_sql_count
# - __decompress_last
# - create_pseudo_record
# - prettify_url
########################## General functions ##################################
def count_baskets(uid):
"""Return (nb personal baskets, nb group baskets, nb external
baskets) tuple for given user"""
query1 = "SELECT COUNT(id) FROM bskBASKET WHERE id_owner=%s"
res1 = run_sql(query1, (int(uid),))
personal = __wash_sql_count(res1)
query2 = """SELECT count(ugbsk.id_bskbasket)
FROM usergroup_bskBASKET ugbsk LEFT JOIN user_usergroup uug
ON ugbsk.id_usergroup=uug.id_usergroup
WHERE uug.id_user=%s AND uug.user_status!=%s
GROUP BY ugbsk.id_usergroup"""
params = (int(uid), CFG_WEBSESSION_USERGROUP_STATUS['PENDING'])
res2 = run_sql(query2, params)
if len(res2):
groups = reduce(lambda x, y: x + y, map(lambda x: x[0], res2))
else:
groups = 0
external = count_external_baskets(uid)
return (personal, groups, external)
def check_user_owns_baskets(uid, bskids):
""" Return 1 if user is owner of every basket in list bskids"""
if not((type(bskids) is list) or (type(bskids) is tuple)):
bskids = [bskids]
query = """SELECT id_owner FROM bskBASKET WHERE %s GROUP BY id_owner"""
sep = ' OR '
query %= sep.join(['id=%s'] * len(bskids))
res = run_sql(query, tuple(bskids))
if len(res)==1 and int(res[0][0])==uid:
return 1
else:
return 0
def get_max_user_rights_on_basket(uid, bskid):
"""Return the max rights a user has on this basket"""
query_owner = "SELECT count(id_owner) FROM bskBASKET WHERE id_owner=%s and id=%s"
params_owner = (int(uid), int(bskid))
res = run_sql(query_owner, params_owner)
if res and res[0][0]:
# if this user is owner of this baskets he can do anything he wants.
return CFG_WEBBASKET_SHARE_LEVELS['MANAGE']
# not owner => group member ?
query_group_baskets = """
SELECT share_level
FROM user_usergroup AS ug LEFT JOIN usergroup_bskBASKET AS ub
ON ug.id_usergroup=ub.id_usergroup
WHERE ug.id_user=%s AND ub.id_bskBASKET=%s AND NOT(ub.share_level='NO') AND ug.user_status!=%s
"""
params_group_baskets = (int(uid), int(bskid), CFG_WEBSESSION_USERGROUP_STATUS['PENDING'])
res = run_sql(query_group_baskets, params_group_baskets)
group_index = None
if res:
try:
group_index = CFG_WEBBASKET_SHARE_LEVELS_ORDERED.index(res[0][0])
except:
return None
# public basket ?
query_public_baskets = """
SELECT share_level
FROM usergroup_bskBASKET
WHERE id_usergroup=0 AND id_bskBASKET=%s
"""
public_index = None
res = run_sql(query_public_baskets, (int(bskid),))
if res:
try:
public_index = CFG_WEBBASKET_SHARE_LEVELS_ORDERED.index(res[0][0])
except:
return None
if group_index or public_index:
if group_index > public_index:
return CFG_WEBBASKET_SHARE_LEVELS_ORDERED[group_index]
else:
return CFG_WEBBASKET_SHARE_LEVELS_ORDERED[public_index]
return None
########################### Personal baskets ##################################
def get_personal_baskets_info_for_topic(uid, topic):
"""Return information about every basket that belongs to the given user and topic."""
query = """ SELECT bsk.id,
bsk.name,
DATE_FORMAT(bsk.date_modification, '%%Y-%%m-%%d %%H:%%i:%%s'),
bsk.nb_views,
count(rec.id_bibrec_or_bskEXTREC),
DATE_FORMAT(max(rec.date_added), '%%Y-%%m-%%d %%H:%%i:%%s')
FROM user_bskBASKET AS ubsk
JOIN bskBASKET AS bsk
ON bsk.id=ubsk.id_bskBASKET
AND bsk.id_owner=%s
LEFT JOIN bskREC AS rec
ON rec.id_bskBASKET=bsk.id
WHERE ubsk.id_user=%s
AND ubsk.topic=%s
GROUP BY bsk.id
ORDER BY bsk.name"""
params = (uid, uid, topic)
res = run_sql(query, params)
return res
def get_all_personal_basket_ids_and_names_by_topic(uid):
"""For a given user return all their personal baskets
(in tuples: (id, name)) grouped by topic. Note that the
basket tuples have to evaluated to be converted to actual
tuples."""
query = """ SELECT ubsk.topic,
count(bsk.id),
GROUP_CONCAT('(', bsk.id, ', \"', bsk.name, '\")'
ORDER BY bsk.name)
FROM user_bskBASKET AS ubsk
JOIN bskBASKET AS bsk
ON ubsk.id_bskBASKET=bsk.id
AND ubsk.id_user=bsk.id_owner
WHERE bsk.id_owner=%s
GROUP BY ubsk.topic
ORDER BY ubsk.topic"""
params = (uid,)
res = run_sql(query, params)
return res
def get_all_personal_basket_ids_and_names_by_topic_for_add_to_list(uid):
"""For a given user return all their personal baskets
(in tuples: (id, name)) grouped by topic. Note that the
basket tuples have to evaluated to be converted to actual
tuples."""
query = """ SELECT ubsk.topic,
GROUP_CONCAT('(', bsk.id, ', \"', bsk.name, '\")'
ORDER BY bsk.name)
FROM user_bskBASKET AS ubsk
JOIN bskBASKET AS bsk
ON ubsk.id_bskBASKET=bsk.id
AND ubsk.id_user=bsk.id_owner
WHERE bsk.id_owner=%s
GROUP BY ubsk.topic
ORDER BY ubsk.topic"""
params = (uid,)
res = run_sql(query, params)
return res
def get_all_personal_baskets_names(uid):
""" for a given user, returns every basket he is owner of
returns list of tuples: (bskid, bsk_name, topic)
"""
query = """
SELECT bsk.id,
bsk.name,
ubsk.topic
FROM user_bskBASKET ubsk JOIN bskBASKET bsk
ON ubsk.id_bskBASKET=bsk.id
AND ubsk.id_user=bsk.id_owner
WHERE bsk.id_owner=%s
ORDER BY ubsk.topic
"""
params = (int(uid),)
return run_sql(query, params)
def get_basket_name(bskid):
"""return the name of a given basket"""
query = 'SELECT name FROM bskBASKET where id=%s'
res = run_sql(query, (int(bskid), ))
if res:
return res[0][0]
else:
return ''
def is_personal_basket_valid(uid, bskid):
"""Check if the basked (bskid) belongs to user (uid) and is valid."""
query = """ SELECT id
FROM bskBASKET
WHERE id=%s
AND id_owner=%s"""
params = (bskid, uid)
res = run_sql(query, params)
return res
def is_topic_valid(uid, topic):
"""Check if the topic defined by user (uid) exists."""
query = """ SELECT distinct(topic)
FROM user_bskBASKET
WHERE topic=%s
AND id_user=%s"""
params = (topic, uid)
res = run_sql(query, params)
return res
def get_basket_topic(uid, bskid):
"""Return the name of the topic this basket (bskid) belongs to."""
query = """ SELECT topic
FROM user_bskBASKET
WHERE id_bskBASKET=%s
AND id_user=%s"""
params = (bskid,uid)
res = run_sql(query, params)
return res
def get_personal_topics_infos(uid):
"""
Get the list of every topic user has defined,
and the number of baskets in each topic
@param uid: user id (int)
@return: a list of tuples (topic name, nb of baskets)
"""
query = """SELECT topic, count(b.id)
FROM user_bskBASKET ub JOIN bskBASKET b
ON ub.id_bskBASKET=b.id AND
b.id_owner=ub.id_user
WHERE ub.id_user=%s
GROUP BY topic
ORDER BY topic"""
uid = int(uid)
res = run_sql(query, (uid,))
return res
def rename_basket(bskid, new_name):
"""Rename basket to new_name"""
run_sql("UPDATE bskBASKET SET name=%s WHERE id=%s", (new_name, bskid))
def rename_topic(uid, old_topic, new_topic):
"""Rename topic to new_topic """
res = run_sql("UPDATE user_bskBASKET SET topic=%s WHERE id_user=%s AND topic=%s",
(new_topic, uid, old_topic))
return res
def move_baskets_to_topic(uid, bskids, new_topic):
"""Move given baskets to another topic"""
if not((type(bskids) is list) or (type(bskids) is tuple)):
bskids = [bskids]
query = "UPDATE user_bskBASKET SET topic=%s WHERE id_user=%s AND ("
query += ' OR '.join(['id_bskBASKET=%s'] * len(bskids))
query += ")"
params = (new_topic, uid) + tuple(bskids)
res = run_sql(query, params)
return res
def delete_basket(bskid):
"""Delete given basket."""
# TODO: check if any alerts are automaticly adding items to the given basket.
bskid = int(bskid)
query1 = "DELETE FROM bskBASKET WHERE id=%s"
res = run_sql(query1, (bskid,))
query2A = "SELECT id_bibrec_or_bskEXTREC FROM bskREC WHERE id_bskBASKET=%s"
local_and_external_ids = run_sql(query2A, (bskid,))
external_ids = [local_and_external_id[0] for local_and_external_id in \
local_and_external_ids if local_and_external_id[0]<0]
for external_id in external_ids:
delete_item(bskid=bskid, recid=external_id, update_date_modification=False)
query2B = "DELETE FROM bskREC WHERE id_bskBASKET=%s"
run_sql(query2B, (bskid,))
query3 = "DELETE FROM bskRECORDCOMMENT WHERE id_bskBASKET=%s"
run_sql(query3, (bskid,))
query4 = "DELETE FROM user_bskBASKET WHERE id_bskBASKET=%s"
run_sql(query4, (bskid,))
query5 = "DELETE FROM usergroup_bskBASKET WHERE id_bskBASKET=%s"
run_sql(query5, (bskid,))
query6 = "DELETE FROM user_query_basket WHERE id_basket=%s"
run_sql(query6, (bskid,))
return int(res)
def create_basket(uid, basket_name, topic):
"""Create new basket for given user in given topic"""
now = convert_datestruct_to_datetext(localtime())
id_bsk = run_sql("""INSERT INTO bskBASKET (id_owner, name, date_modification)
VALUES (%s, %s, %s)""",
(uid, basket_name, now))
run_sql("""INSERT INTO user_bskBASKET (id_user, id_bskBASKET, topic)
VALUES (%s, %s, %s)""",
(uid, id_bsk, topic))
return id_bsk
def get_all_items_in_user_personal_baskets(uid,
topic="",
format='hb'):
"""For the specified user, return all the items in their personal baskets,
grouped by basket if local or as a list if external.
If topic is set, return only that topic's items."""
if topic:
topic_clause = """AND ubsk.topic=%s"""
params_local = (uid, uid, topic)
params_external = (uid, uid, topic, format)
else:
topic_clause = ""
params_local = (uid, uid)
params_external = (uid, uid, format)
query_local = """
SELECT rec.id_bskBASKET,
bsk.name,
ubsk.topic,
GROUP_CONCAT(rec.id_bibrec_or_bskEXTREC)
FROM bskREC AS rec
JOIN bskBASKET AS bsk
ON bsk.id=rec.id_bskBASKET
AND bsk.id_owner=%%s
JOIN user_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=rec.id_bskBASKET
AND ubsk.id_user=%%s
%s
WHERE rec.id_bibrec_or_bskEXTREC > 0
GROUP BY rec.id_bskBASKET""" % (topic_clause,)
res_local = run_sql(query_local, params_local)
query_external = """
SELECT rec.id_bskBASKET,
bsk.name,
ubsk.topic,
rec.id_bibrec_or_bskEXTREC,
ext.value
FROM bskREC AS rec
JOIN bskBASKET AS bsk
ON bsk.id=rec.id_bskBASKET
AND bsk.id_owner=%%s
JOIN user_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=rec.id_bskBASKET
AND ubsk.id_user=%%s
%s
JOIN bskEXTFMT AS ext
ON ext.id_bskEXTREC=-rec.id_bibrec_or_bskEXTREC
AND ext.format=%%s
WHERE rec.id_bibrec_or_bskEXTREC < 0
ORDER BY rec.id_bskBASKET""" % (topic_clause,)
res_external = run_sql(query_external, params_external)
return (res_local, res_external)
def get_all_items_in_user_personal_baskets_by_matching_notes(uid,
topic="",
p=""):
"""For the specified user, return all the items in their personal baskets
matching their notes' titles and bodies, grouped by basket.
If topic is set, return only that topic's items."""
p = p and '%' + p + '%' or '%'
if topic:
topic_clause = """AND ubsk.topic=%s"""
params = (uid, uid, topic, p, p)
else:
topic_clause = ""
params = (uid, uid, p, p)
query = """ SELECT notes.id_bskBASKET,
bsk.name,
ubsk.topic,
GROUP_CONCAT(DISTINCT(notes.id_bibrec_or_bskEXTREC))
FROM bskRECORDCOMMENT AS notes
JOIN bskBASKET AS bsk
ON bsk.id=notes.id_bskBASKET
AND bsk.id_owner=%%s
JOIN user_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=notes.id_bskBASKET
AND ubsk.id_user=%%s
%s
WHERE notes.title like %%s
OR notes.body like %%s
GROUP BY notes.id_bskBASKET""" % (topic_clause,)
res = run_sql(query, params)
return res
def get_all_user_topics(uid):
"""Return a list of the user's topics."""
query = """ SELECT ubsk.topic
FROM bskBASKET AS bsk
JOIN user_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=bsk.id
AND ubsk.id_user=bsk.id_owner
WHERE bsk.id_owner=%s
GROUP BY ubsk.topic"""
params = (uid,)
res = run_sql(query, params)
return res
########################## Actions on baskets #################################
def get_basket_record(bskid, recid, format='hb'):
"""get record recid in basket bskid
"""
if recid < 0:
rec_table = 'bskEXTREC'
format_table = 'bskEXTFMT'
id_field = 'id_bskEXTREC'
sign = '-'
else:
rec_table = 'bibrec'
format_table = 'bibfmt'
id_field = 'id_bibrec'
sign = ''
query = """
SELECT DATE_FORMAT(record.creation_date, '%%%%Y-%%%%m-%%%%d %%%%H:%%%%i:%%%%s'),
DATE_FORMAT(record.modification_date, '%%%%Y-%%%%m-%%%%d %%%%H:%%%%i:%%%%s'),
DATE_FORMAT(bskREC.date_added, '%%%%Y-%%%%m-%%%%d %%%%H:%%%%i:%%%%s'),
user.nickname,
count(cmt.id_bibrec_or_bskEXTREC),
DATE_FORMAT(max(cmt.date_creation), '%%%%Y-%%%%m-%%%%d %%%%H:%%%%i:%%%%s'),
fmt.value
FROM bskREC LEFT JOIN user
ON bskREC.id_user_who_added_item=user.id
LEFT JOIN bskRECORDCOMMENT cmt
ON bskREC.id_bibrec_or_bskEXTREC=cmt.id_bibrec_or_bskEXTREC
LEFT JOIN %(rec_table)s record
ON (%(sign)sbskREC.id_bibrec_or_bskEXTREC=record.id)
LEFT JOIN %(format_table)s fmt
ON (record.id=fmt.%(id_field)s)
WHERE bskREC.id_bskBASKET=%%s AND
bskREC.id_bibrec_or_bskEXTREC=%%s AND
fmt.format=%%s
GROUP BY bskREC.id_bibrec_or_bskEXTREC
""" % {'rec_table': rec_table,
'sign': sign,
'format_table': format_table,
'id_field':id_field}
params = (int(bskid), int(recid), format)
res = run_sql(query, params)
if res:
return __decompress_last(res[0])
return ()
def get_basket_content(bskid, format='hb'):
"""Get all records for a given basket."""
query = """ SELECT rec.id_bibrec_or_bskEXTREC,
extrec.collection_id,
count(cmt.id_bibrec_or_bskEXTREC),
DATE_FORMAT(max(cmt.date_creation), '%%Y-%%m-%%d %%H:%%i:%%s'),
extern.value as ext_val,
intern.value as int_val,
rec.score
FROM bskREC AS rec
LEFT JOIN bskRECORDCOMMENT AS cmt
ON (rec.id_bibrec_or_bskEXTREC=cmt.id_bibrec_or_bskEXTREC
AND rec.id_bskBASKET=cmt.id_bskBASKET)
LEFT JOIN bskEXTFMT AS extern
ON (-rec.id_bibrec_or_bskEXTREC=extern.id_bskEXTREC
AND extern.format=%s)
LEFT JOIN bibfmt AS intern
ON (rec.id_bibrec_or_bskEXTREC=intern.id_bibrec
AND intern.format=%s)
LEFT JOIN bskEXTREC AS extrec
ON extrec.id=-rec.id_bibrec_or_bskEXTREC
WHERE rec.id_bskBASKET=%s
GROUP BY rec.id_bibrec_or_bskEXTREC
ORDER BY rec.score"""
params = (format, format, int(bskid))
res = run_sql(query, params)
if res:
query2 = "UPDATE bskBASKET SET nb_views=nb_views+1 WHERE id=%s"
run_sql(query2, (int(bskid),))
return res
return ()
def get_basket_item(bskid, recid, format='hb'):
"""Get item (recid) for a given basket."""
query = """ SELECT rec.id_bibrec_or_bskEXTREC,
extrec.collection_id,
count(cmt.id_bibrec_or_bskEXTREC),
DATE_FORMAT(max(cmt.date_creation), '%%Y-%%m-%%d %%H:%%i:%%s'),
extern.value as ext_val,
intern.value as int_val,
rec.score
FROM bskREC rec
LEFT JOIN bskRECORDCOMMENT cmt
ON (rec.id_bibrec_or_bskEXTREC=cmt.id_bibrec_or_bskEXTREC
AND
rec.id_bskBASKET=cmt.id_bskBASKET)
LEFT JOIN bskEXTFMT extern
ON (-rec.id_bibrec_or_bskEXTREC=extern.id_bskEXTREC
AND
extern.format=%s)
LEFT JOIN bibfmt intern
ON (rec.id_bibrec_or_bskEXTREC=intern.id_bibrec
AND
intern.format=%s)
LEFT JOIN bskEXTREC AS extrec
ON extrec.id=-rec.id_bibrec_or_bskEXTREC
WHERE rec.id_bskBASKET=%s
AND rec.id_bibrec_or_bskEXTREC=%s
GROUP BY rec.id_bibrec_or_bskEXTREC
ORDER BY rec.score"""
params = (format, format, bskid, recid)
res = run_sql(query, params)
if res:
queryU = """UPDATE bskBASKET SET nb_views=nb_views+1 WHERE id=%s"""
paramsU = (bskid,)
run_sql(queryU, paramsU)
score = res[0][6]
query_previous = """SELECT id_bibrec_or_bskEXTREC
FROM bskREC
WHERE id_bskBASKET=%s
AND score<%s
ORDER BY score DESC
LIMIT 1"""
params_previous = (bskid, score)
res_previous = run_sql(query_previous, params_previous)
query_next = """SELECT id_bibrec_or_bskEXTREC
FROM bskREC
WHERE id_bskBASKET=%s
AND score>%s
ORDER BY score ASC
LIMIT 1"""
params_next = (bskid, score)
res_next = run_sql(query_next, params_next)
query_index = """ SELECT COUNT(id_bibrec_or_bskEXTREC)
FROM bskREC
WHERE id_bskBASKET=%s
AND score<=%s
ORDER BY score"""
params_index = (bskid, score)
res_index = run_sql(query_index, params_index)
res_index = __wash_sql_count(res_index)
return (res[0], res_previous and res_previous[0][0] or 0, res_next and res_next[0][0] or 0, res_index)
else:
return ()
def share_basket_with_group(bskid, group_id,
share_level=CFG_WEBBASKET_SHARE_LEVELS['READITM']):
""" Share basket bskid with group group_id with given share_level
@param share_level: see CFG_WEBBASKET_SHARE_LEVELS in webbasket_config
"""
now = convert_datestruct_to_datetext(localtime())
run_sql("""REPLACE INTO usergroup_bskBASKET
(id_usergroup, id_bskBASKET, date_shared, share_level)
VALUES (%s,%s,%s,%s)""",
(group_id, bskid, now, str(share_level)))
def update_rights(bskid, group_rights):
"""update rights (permissions) for groups.
@param bskid: basket id
@param group_rights: dictionary of {group id: new rights}
"""
now = convert_datestruct_to_datetext(localtime())
query1 = """REPLACE INTO usergroup_bskBASKET
(id_usergroup, id_bskBASKET, date_shared, share_level)
VALUES """ + \
', '.join(["(%s, %s, %s, %s)"] * len(group_rights.items()))
params = ()
for (group_id, share_level) in group_rights.items():
params += (int(group_id), int(bskid), now, str(share_level))
run_sql(query1, params)
query2 = """DELETE FROM usergroup_bskBASKET WHERE share_level='NO'"""
run_sql(query2)
def move_item(bskid, recid, direction):
"""Change score of an item in a basket"""
bskid = int(bskid)
query1 = """SELECT id_bibrec_or_bskEXTREC,
score
FROM bskREC
WHERE id_bskBASKET=%s
ORDER BY score, date_added"""
items = run_sql(query1, (bskid,))
(recids, scores) = zip(*items)
(recids, scores) = (list(recids), list(scores))
if len(recids) and recid in recids:
current_index = recids.index(recid)
if direction == CFG_WEBBASKET_ACTIONS['UP']:
switch_index = 0
if current_index != 0:
switch_index = current_index -1
else:
switch_index = len(recids) - 1
if current_index != len(recids)-1:
switch_index = current_index + 1
query2 = """UPDATE bskREC
SET score=%s
WHERE id_bskBASKET=%s AND id_bibrec_or_bskEXTREC=%s"""
res1 = run_sql(query2, (scores[switch_index], bskid, recids[current_index]))
res2 = run_sql(query2, (scores[current_index], bskid, recids[switch_index]))
if res1 and res2:
now = convert_datestruct_to_datetext(localtime())
query3 = "UPDATE bskBASKET SET date_modification=%s WHERE id=%s"
params3 = (now, int(bskid))
run_sql(query3, params3)
def delete_item(bskid, recid, update_date_modification=True):
"""Remove item recid from basket bskid"""
if recid < 0:
query0A = "select count(id_bskBASKET) from bskREC where id_bibrec_or_bskEXTREC=%s" % (int(recid))
ncopies = run_sql(query0A)
if ncopies and ncopies[0][0]<=1:
# uncomment the following 5 lines and comment the following 2 to delete cached records
# only for external sources and not for external records
#query0B = "SELECT collection_id FROM bskEXTREC WHERE id=%s" % (-int(recid))
#colid = run_sql(query0B)
#if colid and colid[0][0]==0:
#query0C = "DELETE from bskEXTFMT WHERE id_bskEXTREC=%s" % (-int(recid))
#run_sql(query0C)
# the following two lines delete cached external records. We could keep them if we find
# a way to reuse them in case the external records are added again in the future.
query0D = "DELETE from bskEXTFMT WHERE id_bskEXTREC=%s" % (-int(recid))
run_sql(query0D)
query0E = "DELETE from bskEXTREC WHERE id=%s" % (-int(recid))
run_sql(query0E)
query_notes = "DELETE FROM bskRECORDCOMMENT WHERE id_bskBASKET=%s AND id_bibrec_or_bskEXTREC=%s"
run_sql(query_notes, (bskid, recid,))
query1 = "DELETE from bskREC WHERE id_bskBASKET=%s AND id_bibrec_or_bskEXTREC=%s"
params1 = (int(bskid), int(recid))
res = run_sql(query1, params1)
if update_date_modification and res:
now = convert_datestruct_to_datetext(localtime())
query2 = "UPDATE bskBASKET SET date_modification=%s WHERE id=%s"
params2 = (now, int(bskid))
run_sql(query2, params2)
return res
def add_to_basket(uid,
recids=[],
colid=0,
bskid=0,
es_title="",
es_desc="",
es_url=""):
"""Add items (recids) basket (bskid)."""
if (recids or (colid == -1 and es_title and es_desc and es_url)) and bskid > 0:
query_max_score = """ SELECT MAX(score)
FROM bskREC
WHERE id_bskBASKET=%s"""
params_max_score = (bskid,)
res_max_score = run_sql(query_max_score, params_max_score)
max_score = __wash_sql_count(res_max_score)
if not max_score:
# max_score == None actually means that the basket doesn't exist.
# Maybe we should return 0 and inform the admin?
max_score = 1
if colid > 0:
query_existing = """ SELECT id,
external_id
FROM bskEXTREC
WHERE %s
AND collection_id=%s"""
sep_or = ' OR '
query_existing %= (sep_or.join(['external_id=%s'] * len(recids)), colid)
params_existing = tuple(recids)
res_existing = run_sql(query_existing, params_existing)
existing_recids = [int(external_ids_couple[1]) for external_ids_couple in res_existing]
existing_ids = [int(ids[0]) for ids in res_existing]
new_recids = [recid for recid in recids if int(recid) not in existing_recids]
# sets approach
#existing_recids = [ids[1] for ids in res_existing]
#new_recids = list(set(recids)-set(existing_recids))
if new_recids:
query_new = """ INSERT INTO bskEXTREC (external_id,
collection_id,
creation_date,
modification_date)
VALUES """
now = convert_datestruct_to_datetext(localtime())
records = ["(%s, %s, %s, %s)"] * len(new_recids)
query_new += ', '.join(records)
params_new = ()
for new_recid in new_recids:
params_new += (int(new_recid), colid, now, now)
res_new = run_sql(query_new, params_new)
recids = [-int(recid) for recid in existing_ids]
recids.extend(range(-res_new,-(res_new+len(new_recids)),-1))
else:
recids = [-int(recid) for recid in existing_ids]
elif colid < 0:
query_external = """INSERT INTO bskEXTREC (collection_id,
original_url,
creation_date,
modification_date)
VALUES (%s, %s, %s, %s)"""
now = convert_datestruct_to_datetext(localtime())
params_external = (colid, es_url, now, now)
res_external = run_sql(query_external, params_external)
recids = [-res_external]
store_external_source(res_external, es_title, es_desc, es_url, 'xm')
store_external_source(res_external, es_title, es_desc, es_url, 'hb')
query_insert = """ INSERT IGNORE INTO bskREC
(id_bibrec_or_bskEXTREC,
id_bskBASKET,
id_user_who_added_item,
date_added,
score)
VALUES """
if colid == 0 or (colid > 0 and not new_recids):
now = convert_datestruct_to_datetext(localtime())
records = ["(%s, %s, %s, %s, %s)"] * len(recids)
query_insert += ', '.join(records)
params_insert = ()
i = 1
for recid in recids:
params_insert += (recid, bskid, uid, now, max_score + i)
i += 1
run_sql(query_insert, params_insert)
query_update = """ UPDATE bskBASKET
SET date_modification=%s
WHERE id=%s"""
params_update = (now, bskid)
run_sql(query_update, params_update)
return recids
return 0
def add_to_many_baskets(uid, recids=[], colid=0, bskids=[], es_title="", es_desc="", es_url=""):
"""Add items recids to every basket in bskids list."""
if (len(recids) or colid == -1) and len(bskids):
query1 = """SELECT id_bskBASKET,
max(score)
FROM bskREC
WHERE %s
GROUP BY id_bskBASKET"""
bskids = [bskid for bskid in bskids if int(bskid) >= 0]
sep_or = ' OR '
query1 %= sep_or.join(['id_bskBASKET=%s'] * len(bskids))
bsks = dict.fromkeys(bskids, 0)
params = tuple(bskids)
bsks.update(dict(run_sql(query1, params)))
if colid > 0:
query2A = """SELECT id,
external_id
FROM bskEXTREC
WHERE %s
AND collection_id=%s"""
query2A %= (sep_or.join(['external_id=%s'] * len(recids)), colid)
params2A = tuple(recids)
res2A = run_sql(query2A, params2A)
existing_recids = [int(external_ids_couple[1]) for external_ids_couple in res2A]
existing_ids = [int(ids[0]) for ids in res2A]
new_recids = [recid for recid in recids if int(recid) not in existing_recids]
# sets approach
#existing_recids = [ids[1] for ids in res2A]
#new_recids = list(set(recids)-set(existing_recids))
f = open("/tmp/bsk_db", "w")
f.write(str(recids) + "\n" + str(existing_recids) + "\n" + str(existing_ids) + "\n" + str(new_recids) + "\n")
f.close()
if new_recids:
query2B = """INSERT
INTO bskEXTREC
(external_id,
collection_id,
creation_date,
modification_date)
VALUES """
now = convert_datestruct_to_datetext(localtime())
records = ["(%s, %s, %s, %s)"] * len(new_recids)
query2B += ', '.join(records)
params2B = ()
for new_recid in new_recids:
params2B += (int(new_recid), colid, now, now)
res = run_sql(query2B, params2B)
recids = [-int(recid) for recid in existing_ids]
recids.extend(range(-res,-(res+len(new_recids)),-1))
else:
recids = [-int(recid) for recid in existing_ids]
elif colid < 0:
query2C = """INSERT
INTO bskEXTREC
(collection_id,
original_url,
creation_date,
modification_date)
VALUES (%s, %s, %s, %s)"""
now = convert_datestruct_to_datetext(localtime())
params = (colid, es_url, now, now)
res = run_sql(query2C, params)
recids = [-res]
store_external_source(res, es_title, es_desc, es_url, 'xm')
store_external_source(res, es_title, es_desc, es_url, 'hb')
query2 = """INSERT IGNORE
INTO bskREC
(id_bibrec_or_bskEXTREC,
id_bskBASKET,
id_user_who_added_item,
date_added,
score)
VALUES """
if colid == 0 or (colid > 0 and not new_recids):
now = convert_datestruct_to_datetext(localtime())
records = ["(%s, %s, %s, %s, %s)"] * (len(recids) * len(bsks.items()))
query2 += ', '.join(records)
params = ()
for (bskid, max_score) in bsks.items():
i = 1
for recid in recids:
params += (int(recid), int(bskid), int(uid), now, int(max_score) + i)
i += 1
run_sql(query2, params)
query3 = """UPDATE bskBASKET
SET date_modification=%s
WHERE """
query3 += sep_or.join(["id=%s"] * len(bskids))
params = (now,) + tuple(bskids)
run_sql(query3, params)
return len(bskids)
return 0
def get_external_records_by_collection(recids):
"""Get the selected recids, both local and external, grouped by collection."""
if recids:
query = """ SELECT GROUP_CONCAT(id),
GROUP_CONCAT(external_id),
collection_id
FROM bskEXTREC
WHERE %s
GROUP BY collection_id"""
recids = [-recid for recid in recids]
sep_or = ' OR '
query %= sep_or.join(['id=%s'] * len(recids))
params = tuple(recids)
res = run_sql(query,params)
return res
return 0
def get_external_records(recids, of="hb"):
"""Get formatted external records from the database."""
if recids:
query = """ SELECT rec.collection_id,
fmt.id_bskEXTREC,
fmt.value
FROM bskEXTFMT AS fmt
JOIN bskEXTREC AS rec
ON rec.id=fmt.id_bskEXTREC
WHERE format=%%s
AND ( %s )"""
recids = [-recid for recid in recids]
sep_or = ' OR '
query %= sep_or.join(['id_bskEXTREC=%s'] * len(recids))
params = [of]
params.extend(recids)
params = tuple(params)
res = run_sql(query,params)
return res
return ()
def store_external_records(records, of="hb"):
"""Store formatted external records to the database."""
if records:
query = """INSERT
INTO bskEXTFMT
(id_bskEXTREC,
format,
last_updated,
value)
VALUES """
now = convert_datestruct_to_datetext(localtime())
formatted_records = ["(%s, %s, %s, %s)"] * len(records)
query += ', '.join(formatted_records)
params = ()
for record in records:
params += (record[0], of, now, compress(record[1]))
run_sql(query,params)
def store_external_urls(ids_urls):
"""Store original urls for external records to the database."""
#for id_url in ids_urls.iteritems():
for id_url in ids_urls:
query = """UPDATE
bskEXTREC
SET original_url=%s
WHERE id=%s"""
params = (id_url[1], id_url[0])
run_sql(query,params)
def store_external_source(es_id, es_title, es_desc, es_url, of="hb"):
"""Store formatted external sources to the database."""
if es_id and es_title and es_desc:
query = """INSERT INTO bskEXTFMT
(id_bskEXTREC,
format,
last_updated,
value)
VALUES (%s, %s, %s, %s)"""
now = convert_datestruct_to_datetext(localtime())
value = create_pseudo_record(es_title, es_desc, es_url, of)
params = (es_id, of, now, compress(value))
run_sql(query,params)
def get_external_colid_and_url(recid):
"""Get the collection id and original url for an external record."""
if recid:
query = """SELECT
collection_id,
original_url
FROM bskEXTREC
WHERE id=%s"""
params = (-recid,)
res = run_sql(query,params)
if res:
return res
else:
return 0
############################ Group baskets ####################################
def get_group_baskets_info_for_group(grpid):
"""Return information about every basket that belongs to the given group,
provided the user is its manager or a member of it."""
if not grpid:
return ()
query = """ SELECT bsk.id,
bsk.name,
DATE_FORMAT(bsk.date_modification, '%%Y-%%m-%%d %%H:%%i:%%s'),
bsk.nb_views,
COUNT(rec.id_bibrec_or_bskEXTREC),
DATE_FORMAT(max(rec.date_added), '%%Y-%%m-%%d %%H:%%i:%%s'),
ugbsk.share_level,
bsk.id_owner
FROM usergroup_bskBASKET AS ugbsk
JOIN bskBASKET AS bsk
ON bsk.id=ugbsk.id_bskBASKET
LEFT JOIN bskREC AS rec
ON rec.id_bskBASKET=bsk.id
WHERE ugbsk.id_usergroup=%s
AND ugbsk.share_level!='NO'
GROUP BY bsk.id
ORDER BY bsk.name"""
params = (grpid,)
res = run_sql(query, params)
return res
def get_group_name(gid):
"""Given its id return the group's name."""
query = """ SELECT name
FROM usergroup
WHERE id=%s"""
params = (gid,)
res = run_sql(query, params)
return res
def get_all_group_basket_ids_and_names_by_group(uid):
"""For a given user return all their group baskets
(in tuples: (id, name)) grouped by group. Note that the
basket tuples have to evaluated to be converted to actual
tuples."""
query = """ SELECT ug.id,
ug.name,
count(bsk.id),
GROUP_CONCAT('(', ugbsk.id_bskBASKET, ', \"', bsk.name, '\")'
ORDER BY bsk.name)
FROM usergroup AS ug
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_usergroup=ug.id
JOIN bskBASKET AS bsk
ON ugbsk.id_bskBASKET=bsk.id
JOIN user_usergroup AS uug
ON ug.id=uug.id_usergroup
AND uug.id_user=%s
GROUP BY ug.name
ORDER BY ug.name"""
params = (uid,)
res = run_sql(query, params)
return res
def get_all_group_basket_ids_and_names_by_group_for_add_to_list(uid):
"""For a given user return all their group baskets
(in tuples: (id, name)) grouped by group. Note that the
basket tuples have to evaluated to be converted to actual
tuples."""
query = """ SELECT ug.name,
GROUP_CONCAT('(', ugbsk.id_bskBASKET, ', \"', bsk.name, '\")'
ORDER BY bsk.name)
FROM usergroup AS ug
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_usergroup=ug.id
AND ugbsk.share_level!='NO'
AND ugbsk.share_level!='RI'
AND ugbsk.share_level!='RC'
AND ugbsk.share_level!='AC'
JOIN bskBASKET AS bsk
ON ugbsk.id_bskBASKET=bsk.id
JOIN user_usergroup AS uug
ON ug.id=uug.id_usergroup
AND uug.id_user=%s
GROUP BY ug.name
ORDER BY ug.name"""
params = (uid,)
res = run_sql(query, params)
return res
def get_all_group_baskets_names(uid,
min_rights=CFG_WEBBASKET_SHARE_LEVELS['ADDCMT']):
"""For a given user returns every group baskets in which he can <min_rights>
return a list of tuples: (bskid, bsk_name, group_name)."""
# TODO: This function is no longer used. Delete if necessary.
uid = int(uid)
try:
min_rights_num = CFG_WEBBASKET_SHARE_LEVELS_ORDERED.index(min_rights)
except ValueError:
return ()
groups = get_groups_user_member_of(uid)
if groups:
where_clause = '('
where_clause += " OR ".join(["ugbsk.id_usergroup=%s"] * len(groups))
where_clause += ') AND ('
where_clause += " OR ".join(["ugbsk.share_level=%s"] * len(CFG_WEBBASKET_SHARE_LEVELS_ORDERED[min_rights_num:]))
where_clause += ")"
query = """
SELECT bsk.id,
bsk.name,
ug.name
FROM usergroup ug JOIN usergroup_bskBASKET ugbsk
ON ug.id=ugbsk.id_usergroup
JOIN bskBASKET bsk
ON bsk.id=ugbsk.id_bskBASKET
WHERE %s AND NOT(ugbsk.share_level='NO')
ORDER BY ug.name""" % where_clause
params = tuple([group_id for (group_id, dummy) in groups])
params += tuple(CFG_WEBBASKET_SHARE_LEVELS_ORDERED[min_rights_num:])
return run_sql(query, params)
return ()
def is_shared_to(bskids):
"""For each bskid in bskids get id of one of its group. Used to
make distinction between private basket (no group), 'world' basket
(0) or group basket (any int > 0)
"""
if not((type(bskids) == list) or (type(bskids) == tuple)):
bskids = [bskids]
query = """SELECT b.id,
min(u.id_usergroup)
FROM
bskBASKET b LEFT JOIN usergroup_bskBASKET u
ON (b.id=u.id_bskBASKET) """
if len(bskids) != 0:
query += " WHERE "
query += " OR ".join(['b.id=%s'] * len(bskids))
query += " GROUP BY b.id"
params = tuple(bskids)
res = run_sql(query, params)
if res:
return res
return ()
def get_basket_share_level(bskid):
"""Get the minimum share level of the basket (bskid).
Returns:
None for personal baskets
positive integet for group baskets
0 for public baskets
Will return 0 if the basket is both group and publicly shared."""
query = """ SELECT MIN(ugbsk.id_usergroup)
FROM bskBASKET AS bsk
LEFT JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=bsk.id
WHERE bsk.id=%s
GROUP BY bsk.id"""
params = (bskid,)
res = run_sql(query, params)
return res
def get_all_items_in_user_group_baskets(uid,
group=0,
format='hb'):
"""For the specified user, return all the items in their group baskets,
grouped by basket if local or as a list if external.
If group is set, return only that group's items."""
if group:
group_clause = """AND ubsk.id_usergroup=%s"""
params_local = (group, uid)
params_external = (group, uid, format)
else:
group_clause = ""
params_local = (uid,)
params_external = (uid, format)
query_local = """
SELECT rec.id_bskBASKET,
bsk.name,
uug.id_usergroup,
ug.name,
GROUP_CONCAT(rec.id_bibrec_or_bskEXTREC)
FROM bskREC AS rec
JOIN bskBASKET AS bsk
ON bsk.id=rec.id_bskBASKET
JOIN usergroup_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=rec.id_bskBASKET
%s
JOIN user_usergroup AS uug
ON uug.id_usergroup=ubsk.id_usergroup
AND uug.id_user=%%s
JOIN usergroup AS ug
ON ug.id=uug.id_usergroup
WHERE rec.id_bibrec_or_bskEXTREC > 0
GROUP BY rec.id_bskBASKET""" % (group_clause,)
res_local = run_sql(query_local, params_local)
query_external = """
SELECT rec.id_bskBASKET,
bsk.name,
uug.id_usergroup,
ug.name,
rec.id_bibrec_or_bskEXTREC,
ext.value
FROM bskREC AS rec
JOIN bskBASKET AS bsk
ON bsk.id=rec.id_bskBASKET
JOIN usergroup_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=rec.id_bskBASKET
%s
JOIN user_usergroup AS uug
ON uug.id_usergroup=ubsk.id_usergroup
AND uug.id_user=%%s
JOIN usergroup AS ug
ON ug.id=uug.id_usergroup
JOIN bskEXTFMT AS ext
ON ext.id_bskEXTREC=-rec.id_bibrec_or_bskEXTREC
AND ext.format=%%s
WHERE rec.id_bibrec_or_bskEXTREC < 0
ORDER BY rec.id_bskBASKET""" % (group_clause,)
res_external = run_sql(query_external, params_external)
return (res_local, res_external)
def get_all_items_in_user_group_baskets_by_matching_notes(uid,
group=0,
p=""):
"""For the specified user, return all the items in group personal baskets
matching their notes' titles and bodies, grouped by basket.
If topic is set, return only that topic's items."""
p = p and '%' + p + '%' or '%'
if group:
group_clause = """AND ugbsk.id_usergroup=%s"""
params = (group, uid, p, p)
else:
group_clause = ""
params = (uid, p, p)
query = """ SELECT notes.id_bskBASKET,
bsk.name,
uug.id_usergroup,
ug.name,
GROUP_CONCAT(DISTINCT(notes.id_bibrec_or_bskEXTREC))
FROM bskRECORDCOMMENT AS notes
JOIN bskBASKET AS bsk
ON bsk.id=notes.id_bskBASKET
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=notes.id_bskBASKET
AND ugbsk.share_level IS NOT NULL
AND ugbsk.share_level!='NO'
AND ugbsk.share_level!='RI'
%s
JOIN user_usergroup AS uug
ON uug.id_usergroup=ugbsk.id_usergroup
AND uug.id_user=%%s
JOIN usergroup AS ug
ON ug.id=uug.id_usergroup
WHERE notes.title like %%s
OR notes.body like %%s
GROUP BY notes.id_bskBASKET""" % (group_clause,)
res = run_sql(query, params)
return res
def is_group_basket_valid(uid, bskid):
"""Check if the basked (bskid) belongs to one of the groups the user (uid)
is a member of and is valid."""
query = """ SELECT id
FROM bskBASKET AS bsk
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=bsk.id
JOIN user_usergroup AS uug
ON uug.id_usergroup=ugbsk.id_usergroup
AND uug.id_user=%s
WHERE id=%s"""
params = (uid, bskid)
res = run_sql(query, params)
return res
def is_group_valid(uid, group):
"""Check if the group exists and the user is a member or manager."""
query = """ SELECT id_usergroup
FROM user_usergroup
WHERE id_usergroup=%s
AND id_user=%s"""
params = (group, uid)
res = run_sql(query, params)
return res
def get_all_user_groups(uid):
"""Return a list of the groups the user is a member of or manages."""
query = """ SELECT ug.id,
ug.name
FROM user_usergroup AS uug
JOIN usergroup AS ug
ON ug.id=uug.id_usergroup
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_usergroup=uug.id_usergroup
WHERE uug.id_user=%s
GROUP BY uug.id_usergroup"""
params = (uid,)
res = run_sql(query, params)
return res
########################## External baskets ###################################
def get_external_baskets_infos(uid):
"""Get general informations about every external basket user uid has subscribed to."""
query = """
SELECT bsk.id,
bsk.name,
DATE_FORMAT(bsk.date_modification, '%%Y-%%m-%%d %%H:%%i:%%s'),
bsk.nb_views,
count(rec.id_bibrec_or_bskEXTREC),
DATE_FORMAT(max(rec.date_added), '%%Y-%%m-%%d %%H:%%i:%%s'),
ugbsk.share_level
FROM bskBASKET bsk JOIN user_bskBASKET ubsk
ON (bsk.id=ubsk.id_bskBASKET AND ubsk.id_user=%s)
LEFT JOIN bskREC rec
ON (bsk.id=rec.id_bskBASKET)
LEFT JOIN usergroup_bskBASKET ugbsk
ON (ugbsk.id_bskBASKET=bsk.id AND ugbsk.id_usergroup=0)
WHERE bsk.id_owner!=%s
GROUP BY bsk.id
"""
uid = int(uid)
params = (uid, uid)
res = run_sql(query, params)
if res:
return res
return ()
def get_external_basket_info(bskid):
""""""
query = """ SELECT bsk.id,
bsk.name,
DATE_FORMAT(bsk.date_modification, '%%Y-%%m-%%d %%H:%%i:%%s'),
bsk.nb_views,
count(rec.id_bibrec_or_bskEXTREC),
DATE_FORMAT(max(rec.date_added), '%%Y-%%m-%%d %%H:%%i:%%s'),
ugbsk.share_level
FROM bskBASKET AS bsk
LEFT JOIN bskREC AS rec
ON bsk.id=rec.id_bskBASKET
JOIN usergroup_bskBASKET AS ugbsk
ON bsk.id=ugbsk.id_bskBASKET
AND ugbsk.id_usergroup=0
- JOIN user_bskBASKET AS ubsk
- ON bsk.id_owner!=ubsk.id_user
- AND bsk.id=ubsk.id_bskBASKET
- WHERE id=%s
- GROUP BY bsk.id"""
+ WHERE id=%s"""
params = (bskid,)
res = run_sql(query, params)
return res
def get_all_external_basket_ids_and_names(uid):
"""For a given user return all their external baskets
(in tuples: (id, name, number_of_records))."""
query = """ SELECT bsk.id,
bsk.name,
count(rec.id_bibrec_or_bskEXTREC),
ugbsk.id_usergroup
FROM user_bskBASKET AS ubsk
JOIN bskBASKET AS bsk
ON ubsk.id_bskBASKET=bsk.id
AND ubsk.id_user!=bsk.id_owner
LEFT JOIN bskREC AS rec
ON ubsk.id_bskBASKET=rec.id_bskBASKET
LEFT JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_usergroup=0
AND ugbsk.id_bskBASKET=bsk.id
WHERE ubsk.id_user=%s
GROUP BY bsk.id
ORDER BY bsk.name"""
params = (uid,)
res = run_sql(query, params)
return res
def count_external_baskets(uid):
"""Returns the number of external baskets the user is subscribed to."""
query = """ SELECT COUNT(ubsk.id_bskBASKET)
FROM user_bskBASKET ubsk
LEFT JOIN bskBASKET bsk
ON (bsk.id=ubsk.id_bskBASKET AND ubsk.id_user=%s)
WHERE bsk.id_owner!=%s"""
params = (int(uid), int(uid))
res = run_sql(query, params)
return __wash_sql_count(res)
def get_all_external_baskets_names(uid,
min_rights=CFG_WEBBASKET_SHARE_LEVELS['ADDCMT']):
""" for a given user returns every basket which he has subscribed to and in which
he can <min_rights>
return a list of tuples: (bskid, bsk_name)
"""
uid = int(uid)
try:
min_rights_num = CFG_WEBBASKET_SHARE_LEVELS_ORDERED.index(min_rights)
except ValueError:
return ()
where_clause = ' AND ('
for right in CFG_WEBBASKET_SHARE_LEVELS_ORDERED[min_rights_num:-1]:
where_clause += "ugbsk.share_level = '%s' OR " % right
where_clause += "ugbsk.share_level = '%s')" % CFG_WEBBASKET_SHARE_LEVELS_ORDERED[-1]
query = """
SELECT bsk.id,
bsk.name
FROM bskBASKET bsk JOIN usergroup_bskBASKET ugbsk
ON bsk.id=ugbsk.id_bskBASKET
JOIN user_bskBASKET ubsk
ON ubsk.id_bskBASKET=bsk.id
WHERE ugbsk.id_usergroup=0 AND
ubsk.id_user=%s AND
NOT(bsk.id_owner=%s) AND
NOT(ugbsk.share_level='NO')
""" + where_clause
params = (uid, uid)
return run_sql(query, params)
def get_all_items_in_user_public_baskets(uid,
format='hb'):
"""For the specified user, return all the items in the public baskets they
are subscribed to, grouped by basket if local or as a list if external."""
query_local = """
SELECT rec.id_bskBASKET,
bsk.name,
GROUP_CONCAT(rec.id_bibrec_or_bskEXTREC)
FROM bskREC AS rec
JOIN bskBASKET AS bsk
ON bsk.id=rec.id_bskBASKET
AND bsk.id_owner!=%s
JOIN user_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=rec.id_bskBASKET
AND ubsk.id_user=%s
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=rec.id_bskBASKET
AND ugbsk.id_usergroup=0
WHERE rec.id_bibrec_or_bskEXTREC > 0
GROUP BY rec.id_bskBASKET"""
params_local = (uid, uid)
res_local = run_sql(query_local, params_local)
query_external = """
SELECT rec.id_bskBASKET,
bsk.name,
rec.id_bibrec_or_bskEXTREC,
ext.value
FROM bskREC AS rec
JOIN bskBASKET AS bsk
ON bsk.id=rec.id_bskBASKET
AND bsk.id_owner!=%s
JOIN user_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=rec.id_bskBASKET
AND ubsk.id_user=%s
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=rec.id_bskBASKET
AND ugbsk.id_usergroup=0
JOIN bskEXTFMT AS ext
ON ext.id_bskEXTREC=-rec.id_bibrec_or_bskEXTREC
AND ext.format=%s
WHERE rec.id_bibrec_or_bskEXTREC < 0
ORDER BY rec.id_bskBASKET"""
params_external = (uid, uid, format)
res_external = run_sql(query_external, params_external)
return (res_local, res_external)
def get_all_items_in_user_public_baskets_by_matching_notes(uid,
p=""):
"""For the specified user, return all the items in the public baskets they
are subscribed to, matching their notes' titles and bodies,
grouped by basket"""
p = p and '%' + p + '%' or '%'
query = """ SELECT notes.id_bskBASKET,
bsk.name,
GROUP_CONCAT(DISTINCT(notes.id_bibrec_or_bskEXTREC))
FROM bskRECORDCOMMENT AS notes
JOIN bskBASKET AS bsk
ON bsk.id=notes.id_bskBASKET
AND bsk.id_owner!=%s
JOIN user_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=notes.id_bskBASKET
AND ubsk.id_user=%s
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=notes.id_bskBASKET
AND ugbsk.id_usergroup=0
AND ugbsk.share_level IS NOT NULL
AND ugbsk.share_level!='NO'
AND ugbsk.share_level!='RI'
WHERE notes.title like %s
OR notes.body like %s
GROUP BY notes.id_bskBASKET"""
params = (uid, uid, p, p)
res = run_sql(query, params)
return res
def get_all_items_in_all_public_baskets(format='hb'):
"""Return all the items in all the public baskets,
grouped by basket if local or as a list if external."""
query_local = """
SELECT rec.id_bskBASKET,
bsk.name,
GROUP_CONCAT(rec.id_bibrec_or_bskEXTREC)
FROM bskREC AS rec
JOIN bskBASKET AS bsk
ON bsk.id=rec.id_bskBASKET
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=rec.id_bskBASKET
AND ugbsk.id_usergroup=0
WHERE rec.id_bibrec_or_bskEXTREC > 0
GROUP BY rec.id_bskBASKET"""
res_local = run_sql(query_local)
query_external = """
SELECT rec.id_bskBASKET,
bsk.name,
rec.id_bibrec_or_bskEXTREC,
ext.value
FROM bskREC AS rec
JOIN bskBASKET AS bsk
ON bsk.id=rec.id_bskBASKET
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=rec.id_bskBASKET
AND ugbsk.id_usergroup=0
JOIN bskEXTFMT AS ext
ON ext.id_bskEXTREC=-rec.id_bibrec_or_bskEXTREC
AND ext.format=%s
WHERE rec.id_bibrec_or_bskEXTREC < 0
ORDER BY rec.id_bskBASKET"""
params_external = (format,)
res_external = run_sql(query_external, params_external)
return (res_local, res_external)
def get_all_items_in_all_public_baskets_by_matching_notes(p=""):
"""Return all the items in all the public baskets matching
their notes' titles and bodies, grouped by basket"""
p = p and '%' + p + '%' or '%'
query = """ SELECT notes.id_bskBASKET,
bsk.name,
GROUP_CONCAT(DISTINCT(notes.id_bibrec_or_bskEXTREC))
FROM bskRECORDCOMMENT AS notes
JOIN bskBASKET AS bsk
ON bsk.id=notes.id_bskBASKET
JOIN usergroup_bskBASKET AS ugbsk
ON ugbsk.id_bskBASKET=notes.id_bskBASKET
AND ugbsk.id_usergroup=0
AND ugbsk.share_level IS NOT NULL
AND ugbsk.share_level!='NO'
AND ugbsk.share_level!='RI'
WHERE notes.title like %s
OR notes.body like %s
GROUP BY notes.id_bskBASKET"""
params = (p, p)
res = run_sql(query, params)
return res
############################ Public access ####################################
def get_public_basket_infos(bskid):
"""return (id, name, date modification, nb of views, id of owner, nickname of owner, rights for public access)
for a given basket"""
basket = []
query1 = """SELECT bsk.id,
bsk.name,
DATE_FORMAT(bsk.date_modification, '%%Y-%%m-%%d %%H:%%i:%%s'),
bsk.nb_views,
bsk.id_owner,
user.nickname
FROM bskBASKET bsk LEFT JOIN user
ON bsk.id_owner=user.id
WHERE bsk.id=%s"""
res1 = run_sql(query1, (int(bskid),))
if len(res1):
basket = list(res1[0])
query2 = """SELECT share_level
FROM usergroup_bskBASKET
WHERE id_usergroup=0 and id_bskBASKET=%s"""
res2 = run_sql(query2, (int(bskid),))
if res2:
basket.append(res2[0][0])
else:
basket.append(None)
return basket
def get_public_basket_info(bskid):
"""Return information about a given public basket."""
query = """ SELECT bsk.id,
bsk.name,
bsk.id_owner,
DATE_FORMAT(bsk.date_modification, '%%Y-%%m-%%d %%H:%%i:%%s'),
bsk.nb_views,
COUNT(rec.id_bibrec_or_bskEXTREC),
GROUP_CONCAT(rec.id_bibrec_or_bskEXTREC),
ubsk.share_level
FROM bskBASKET AS bsk
LEFT JOIN bskREC AS rec
ON rec.id_bskBASKET=bsk.id
JOIN usergroup_bskBASKET AS ubsk
ON ubsk.id_bskBASKET=bsk.id
AND ubsk.id_usergroup=0
WHERE bsk.id=%s
GROUP BY bsk.id;"""
params = (bskid,)
res = run_sql(query, params)
return res
def get_basket_general_infos(bskid):
"""return information about a basket, suited for public access.
@return: a (id, name, date of modification, nb of views, nb of records, id of owner) tuple
"""
query = """SELECT bsk.id,
bsk.name,
DATE_FORMAT(bsk.date_modification, '%%Y-%%m-%%d %%H:%%i:%%s'),
bsk.nb_views,
count(rec.id_bibrec_or_bskEXTREC),
bsk.id_owner
FROM bskBASKET bsk LEFT JOIN bskREC rec
ON bsk.id=rec.id_bskBASKET
WHERE bsk.id=%s
GROUP BY bsk.id"""
res = run_sql(query, (int(bskid),))
if res:
query2 = "UPDATE bskBASKET SET nb_views=nb_views+1 WHERE id=%s"
run_sql(query2, (int(bskid),))
return res[0]
return ()
def get_basket_owner_id(bskid):
"""Return the uid of the owner."""
query = """SELECT id_owner
FROM bskBASKET
WHERE id=%s"""
res = run_sql(query, (bskid, ))
if res:
return res[0][0]
return -1
def count_public_baskets():
"""Returns the number of public baskets."""
query = """ SELECT COUNT(id_bskBASKET)
FROM usergroup_bskBASKET
WHERE id_usergroup=0"""
res = run_sql(query)
return __wash_sql_count(res)
def get_public_baskets_list(inf_limit, max_number, order=1, asc=1):
"""Return list of public baskets
@param inf_limit: limit to baskets from number x
@param max_number: number of baskets to return
@order: 1: order by name of basket, 2: number of views, 3: owner
@return:
[(basket id, basket name, nb of views, uid of owner, nickname of owner)]"""
query = """SELECT bsk.id,
bsk.name,
bsk.nb_views,
u.id,
u.nickname
FROM bskBASKET bsk LEFT JOIN usergroup_bskBASKET ugbsk
on bsk.id=ugbsk.id_bskBASKET
LEFT JOIN user u
on bsk.id_owner=u.id
WHERE ugbsk.id_usergroup=0
"""
if order == 2:
query += 'ORDER BY bsk.nb_views'
elif order == 3:
query += 'ORDER BY u.nickname'
if asc:
query += ' ASC'
else:
query += ' DESC'
query += ', u.id'
else:
query += 'ORDER BY bsk.name'
if asc:
query += ' ASC '
else:
query += ' DESC '
query += "LIMIT %s,%s"
return run_sql(query, (inf_limit, max_number))
def count_all_public_baskets():
"""Return the number of all the public baskets."""
query = """ SELECT count(id_bskBASKET)
FROM usergroup_bskBASKET
WHERE id_usergroup=0"""
res = run_sql(query)
return __wash_sql_count(res)
def get_list_public_baskets(page, max_number, sort='name', asc=1):
"""Return list of public baskets
@param page: limit to baskets from number x
@param max_number: maximum number of baskets to return
@sort: 1: order by name of basket, 2: number of views, 3: owner
@return:
[(basket id, basket name, nb of views, uid of owner, nickname of owner)]"""
query = """ SELECT bsk.id,
bsk.name,
bsk.id_owner,
u.nickname,
DATE_FORMAT(bsk.date_modification, '%%Y-%%m-%%d %%H:%%i:%%s'),
COUNT(rec.id_bibrec_or_bskEXTREC) AS items,
bsk.nb_views
FROM usergroup_bskBASKET AS ugbsk
JOIN bskBASKET AS bsk
ON bsk.id=ugbsk.id_bskBASKET
LEFT JOIN bskREC AS rec
ON rec.id_bskBASKET=bsk.id
LEFT JOIN user AS u
ON u.id=bsk.id_owner
WHERE ugbsk.id_usergroup=0
GROUP BY bsk.id"""
if sort == 'name':
query += """
ORDER BY bsk.name"""
elif sort == 'owner':
query += """
ORDER BY u.nickname"""
elif sort == 'views':
query += """
ORDER BY bsk.nb_views"""
elif sort == 'date':
query += """
ORDER BY bsk.date_modification"""
elif sort == 'items':
query += """
ORDER BY items"""
else:
query += """
ORDER BY bsk.name"""
if asc:
query += """ ASC"""
if sort == """owner""":
query += """, u.id"""
else:
query += """ DESC"""
if sort == """owner""":
query += """, u.id"""
query += """
LIMIT %s, %s"""
page = max(0, page)
res = run_sql(query, (page, max_number))
return res
def is_basket_public(bskid):
"""Check if the given basket is public.
Returns ((0,),) if False, ((1,),) if True."""
query = """ SELECT COUNT(*)
FROM usergroup_bskBASKET
WHERE id_usergroup=0
AND id_bskBASKET=%s"""
params = (bskid,)
res = run_sql(query, params)
return __wash_sql_count(res)
def subscribe(uid, bskid):
"""Subscribe the given user to the given basket."""
query1 = """SELECT COUNT(*)
FROM user_bskBASKET
WHERE id_user=%s
AND id_bskBASKET=%s"""
params1 = (uid, bskid)
res1 = run_sql(query1, params1)
if res1[0][0]:
# The user is either the owner of the basket or is already subscribed.
return False
else:
query2 = """INSERT INTO user_bskBASKET (id_user, id_bskBASKET)
VALUES (%s, %s)"""
params2 = (uid, bskid)
run_sql(query2, params2)
return True
def unsubscribe(uid, bskid):
"""Unsubscribe the given user from the given basket."""
query1 = """SELECT COUNT(*)
FROM bskBASKET
WHERE id_owner=%s
AND id=%s"""
params1 = (uid, bskid)
res1 = run_sql(query1, params1)
if res1[0][0]:
# The user is the owner of the basket.
return False
else:
query2 = """DELETE FROM user_bskBASKET
WHERE id_user=%s
AND id_bskBASKET=%s"""
params2 = (uid, bskid)
res2 = run_sql(query2, params2)
if res2:
return True
else:
return False
def is_user_subscribed_to_basket(uid, bskid):
"""Return ((1,),) if the user is subscribed to the given basket
or ((0,),) if the user is not subscribed or is the owner of the basket."""
query = """ SELECT COUNT(ubsk.id_bskBASKET)
FROM user_bskBASKET AS ubsk
JOIN bskBASKET AS bsk
ON bsk.id=ubsk.id_bskBASKET
AND bsk.id_owner!=ubsk.id_user
WHERE ubsk.id_user=%s
AND ubsk.id_bskBASKET=%s"""
params = (uid, bskid)
res = run_sql(query, params)
return __wash_sql_count(res)
def count_subscribers(uid, bskid):
"""Returns a (number of users, number of groups, number of alerts) tuple
for the given user (uid) and basket (bskid)."""
uid = int(uid)
bskid = int(bskid)
query_groups = """ SELECT count(id_usergroup)
FROM usergroup_bskBASKET
WHERE id_bskBASKET=%s
AND NOT(share_level='NO')
GROUP BY id_bskBASKET"""
params_groups = (bskid,)
res_groups = run_sql(query_groups, params_groups)
nb_groups = __wash_sql_count(res_groups)
query_users = """ SELECT count(id_user)
FROM user_bskBASKET
WHERE id_bskBASKET=%s
AND id_user!=%s
GROUP BY id_bskBASKET"""
params_users = (bskid, uid)
res_users = run_sql(query_users, params_users)
nb_users = __wash_sql_count(res_users)
query_alerts = """ SELECT count(id_query)
FROM user_query_basket
WHERE id_basket=%s
GROUP BY id_basket"""
params_alerts = (bskid,)
res_alerts = run_sql(query_alerts, params_alerts)
nb_alerts = __wash_sql_count(res_alerts)
return (nb_users, nb_groups, nb_alerts)
def get_groups_subscribing_to_basket(bskid):
""" get list of (group id, group name, rights) tuples for a given basket
Please note that group 0 is used to mean everybody.
"""
query = """SELECT ugb.id_usergroup,
ug.name,
ugb.share_level
FROM usergroup_bskBASKET ugb LEFT JOIN usergroup ug
ON ugb.id_usergroup=ug.id
WHERE ugb.id_bskBASKET=%s
ORDER BY ugb.id_usergroup"""
return run_sql(query, (int(bskid),))
def get_rights_on_public_basket(bskid):
""""""
query = """ SELECT share_level
FROM usergroup_bskBASKET
WHERE id_usergroup=0
AND id_bskBASKET=%s"""
params = (bskid,)
res = run_sql(query, params)
return res
def count_public_basket_subscribers(bskid):
"""Return the number of users subscribed to the given public basket."""
query = """ SELECT COUNT(ubsk.id_user)
FROM user_bskBASKET AS ubsk
JOIN bskBASKET AS bsk
ON bsk.id=ubsk.id_bskBASKET
AND bsk.id_owner!=ubsk.id_user
WHERE ubsk.id_bskBASKET=%s"""
params = (bskid,)
res = run_sql(query, params)
return __wash_sql_count(res)
################################ Notes ########################################
def get_notes(bskid, recid):
"""Return all comments for record recid in basket bskid."""
query = """
SELECT user.id,
user.nickname,
bskcmt.title,
bskcmt.body,
DATE_FORMAT(bskcmt.date_creation, '%%Y-%%m-%%d %%H:%%i:%%s'),
bskcmt.priority,
bskcmt.id,
bskcmt.in_reply_to_id_bskRECORDCOMMENT
FROM bskRECORDCOMMENT bskcmt LEFT JOIN user
ON (bskcmt.id_user=user.id)
WHERE bskcmt.id_bskBASKET=%s AND
bskcmt.id_bibrec_or_bskEXTREC=%s
ORDER BY bskcmt.reply_order_cached_data
"""
bskid = int(bskid)
recid = int(recid)
res = run_sql(query, (bskid, recid))
if res:
return res
else:
return ()
def get_note(cmtid):
"""Return comment cmtid as a (author's nickname, author's uid, title, body, date of creation, priority) tuple"""
out = ()
query = """
SELECT user.nickname,
user.id,
bskcmt.title,
bskcmt.body,
DATE_FORMAT(bskcmt.date_creation, '%%Y-%%m-%%d %%H:%%i:%%s'),
bskcmt.priority
FROM bskRECORDCOMMENT bskcmt LEFT JOIN user
ON (bskcmt.id_user=user.id)
WHERE bskcmt.id=%s
"""
cmtid = int(cmtid)
res = run_sql(query, (cmtid,))
if res:
return res[0]
return out
def save_note(uid, bskid, recid, title, body, reply_to=None):
"""Save then given note (title, body) on the given item in the given basket."""
if reply_to and CFG_WEBBASKET_MAX_COMMENT_THREAD_DEPTH >= 0:
# Check that we have not reached max depth
note_ancestors = get_note_ancestors(reply_to)
if len(note_ancestors) >= CFG_WEBBASKET_MAX_COMMENT_THREAD_DEPTH:
if CFG_WEBBASKET_MAX_COMMENT_THREAD_DEPTH == 0:
reply_to = None
else:
reply_to = note_ancestors[CFG_WEBBASKET_MAX_COMMENT_THREAD_DEPTH - 1]
date = convert_datestruct_to_datetext(localtime())
res = run_sql("""INSERT INTO bskRECORDCOMMENT (id_user, id_bskBASKET,
id_bibrec_or_bskEXTREC, title, body, date_creation, in_reply_to_id_bskRECORDCOMMENT)
VALUES (%s, %s, %s, %s, %s, %s, %s)""",
(int(uid), int(bskid), int(recid), title, body, date, reply_to or 0))
if res:
new_comid = int(res)
parent_reply_order = run_sql("""SELECT reply_order_cached_data from bskRECORDCOMMENT where id=%s""", (reply_to,))
if not parent_reply_order or parent_reply_order[0][0] is None:
parent_reply_order = ''
else:
parent_reply_order = parent_reply_order[0][0]
run_sql("""UPDATE bskRECORDCOMMENT SET reply_order_cached_data=%s WHERE id=%s""",
(parent_reply_order + get_reply_order_cache_data(new_comid), new_comid))
return int(res)
return 0
def delete_note(bskid, recid, cmtid):
"""Delete a comment on an item of a basket"""
query = """ DELETE
FROM bskRECORDCOMMENT
WHERE id_bskBASKET=%s
AND id_bibrec_or_bskEXTREC=%s
AND id=%s"""
params = (int(bskid), int(recid), int(cmtid))
run_sql(query, params)
def get_note_ancestors(cmtid, depth=None):
"""
Returns the list of ancestors of the given note, ordered from
oldest to newest ("top-down": direct parent of cmtid is at last position),
up to given depth
@param cmtid: the ID of the note for which we want to retrieve ancestors
@type cmtid: int
@param depth: the maximum of levels up from the given note we
want to retrieve ancestors. None for no limit, 1 for
direct parent only, etc.
@type depth: int
@return the list of ancestors
@rtype: list
"""
if depth == 0:
return []
res = run_sql("SELECT in_reply_to_id_bskRECORDCOMMENT FROM bskRECORDCOMMENT WHERE id=%s", (cmtid,))
if res:
parent_cmtid = res[0][0]
if parent_cmtid == 0:
return []
parent_ancestors = []
if depth:
depth -= 1
parent_ancestors = get_note_ancestors(parent_cmtid, depth)
parent_ancestors.append(parent_cmtid)
return parent_ancestors
else:
return []
def note_belongs_to_item_in_basket_p(cmtid, recid, bskid):
"""Returns 1 (True) if the given note (cmtid) belongs to the given item
(recid) and the given basket (bskid) or 0 (False)."""
query = """ SELECT COUNT(*)
FROM bskRECORDCOMMENT
WHERE id=%s
AND id_bibrec_or_bskEXTREC=%s
AND id_bskBASKET=%s"""
params = (cmtid, recid, bskid)
res = run_sql(query, params)
return __wash_sql_count(res)
########################## Usergroup functions ################################
def get_group_infos(uid):
"""For each group the user with uid is a member of return the id, name and number of baskets."""
query = """SELECT g.id,
g.name,
count(ugb.id_bskBASKET)
FROM usergroup g LEFT JOIN (user_usergroup ug,
usergroup_bskBASKET ugb)
ON (g.id=ug.id_usergroup
AND
g.id=ugb.id_usergroup)
WHERE ug.id_user=%s AND NOT(ugb.share_level='NO') AND ug.user_status!=%s
GROUP BY g.id
ORDER BY g.name"""
params = (int(uid), CFG_WEBSESSION_USERGROUP_STATUS['PENDING'])
res = run_sql(query, params)
return res
def count_groups_user_member_of(uid):
"""Returns the number of groups the user has joined."""
query = """ SELECT COUNT(id_usergroup)
FROM user_usergroup
WHERE id_user=%s
AND user_status!=%s"""
params = (int(uid), CFG_WEBSESSION_USERGROUP_STATUS['PENDING'])
res = run_sql(query, params)
return __wash_sql_count(res)
def get_groups_user_member_of(uid):
"""
Get uids and names of groups user is member of.
@param uid: user id (int)
@return: a tuple of (group_id, group_name) tuples
"""
query = """
SELECT g.id,
g.name
FROM usergroup g JOIN user_usergroup ug
ON (g.id=ug.id_usergroup)
WHERE ug.id_user=%s and ug.user_status!=%s
ORDER BY g.name
"""
params = (int(uid), CFG_WEBSESSION_USERGROUP_STATUS['PENDING'])
res = run_sql(query, params)
if res:
return res
return ()
########################## auxilliary functions ###############################
def __wash_sql_count(res):
"""Wash the result of SQL COUNT function and return only an integer."""
if res:
return res[0][0]
return 0
def __decompress_last(item):
"""private function, used to shorten code"""
item = list(item)
item[-1] = decompress(item[-1])
return item
def create_pseudo_record(es_title, es_desc, es_url, of="hb"):
"""Return a pseudo record representation given a title and a description."""
if of == 'hb':
record = '\n'.join([es_title, es_desc, es_url])
if of == 'xm':
# In case we want to use the controlfield,
# the -es_id must be used.
#<controlfield tag="001">%s</controlfield>
record = """<record>
<datafield tag="245" ind1=" " ind2=" ">
<subfield code="a">%s</subfield>
</datafield>
<datafield tag="520" ind1=" " ind2=" ">
<subfield code="a">%s</subfield>
</datafield>
<datafield tag="856" ind1="4" ind2=" ">
<subfield code="u">%s</subfield>
</datafield>
</record>""" % (encode_for_xml(es_title), encode_for_xml(es_desc), es_url)
return record
def prettify_url(url, char_limit=50, nb_dots=3):
"""If the url has more characters than char_limit return a shortened version of it
keeping the beginning and ending and replacing the rest with dots."""
if len(url) > char_limit:
# let's set a minimum character limit
if char_limit < 5:
char_limit = 5
# let's set a maximum number of dots in relation to the character limit
if nb_dots > char_limit/4:
nb_dots = char_limit/5
nb_char_url = char_limit - nb_dots
nb_char_end = nb_char_url/4
nb_char_beg = nb_char_url - nb_char_end
return url[:nb_char_beg] + '.'*nb_dots + url[-nb_char_end:]
else:
return url