Page Menu
Home
c4science
Search
Configure Global Search
Log In
Files
F81965539
webbasket_dblayer.py
No One
Temporary
Actions
Download File
Edit File
Delete File
View Transforms
Subscribe
Mute Notifications
Award Token
Subscribers
None
File Metadata
Details
File Info
Storage
Attached
Created
Sun, Sep 8, 20:59
Size
87 KB
Mime Type
text/x-python
Expires
Tue, Sep 10, 20:59 (2 d)
Engine
blob
Format
Raw Data
Handle
20639550
Attached To
R3600 invenio-infoscience
webbasket_dblayer.py
View Options
# -*- 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.config
import
CFG_SITE_URL
from
invenio.dateutils
import
convert_datestruct_to_datetext
from
invenio.websession_config
import
CFG_WEBSESSION_USERGROUP_STATUS
from
invenio.search_engine
import
get_fieldvalues
########################### 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
# - get_basket_item_title_and_URL
# - 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_user_personal_basket_ids_by_topic
(
uid
):
"""For a given user return all their personal basket ids grouped by topic."""
query
=
""" SELECT ubsk.topic,
GROUP_CONCAT(bsk.id)
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
get_basket_ids_and_names
(
bskids
,
limit
=
0
):
"""For the given basket ids, return their ids and names,
ordered by basket name.
If 'limit' is greater than 0, limit the number of results returned."""
if
not
((
type
(
bskids
)
is
list
)
or
(
type
(
bskids
)
is
tuple
)):
bskids
=
[
bskids
]
query
=
""" SELECT bsk.id,
bsk.name
FROM bskBASKET AS bsk
WHERE %s
ORDER BY bsk.name
%s"""
sep
=
' OR '
query
%=
(
sep
.
join
([
'id=
%s
'
]
*
len
(
bskids
)),
limit
and
'LIMIT
%i
'
%
limit
or
''
)
params
=
tuple
(
bskids
)
res
=
run_sql
(
query
,
params
)
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
get_basket_item_title_and_URL
(
recid
):
"""
Retrieves the title and URL for the specified item in the specified basket.
@param bskid: The basked id
@type bskid: int
@param recid: The record (item) id
@type recid: int
@return: A tuple containing the title as a sting and the URL as a string.
"""
if
recid
>
0
:
# This is a local record, we can easily retrieve the title using the
# search engine's get_fieldvalues function and the MARC field and tag.
title_list
=
get_fieldvalues
(
recid
,
'245___'
)
# Check if the main title is always the first element in the list
if
title_list
:
title
=
title_list
[
0
]
else
:
title
=
""
url
=
'
%s
/record/
%i
'
%
(
CFG_SITE_URL
,
recid
)
elif
recid
<
0
:
# This is an external record or item, use
title
=
"This is an external record or item."
url
=
'
%s
'
%
(
CFG_SITE_URL
,)
query
=
""" SELECT rec.collection_id,
rec.original_url,
fmt.value
FROM bskEXTREC as rec,
bskEXTFMT as fmt
WHERE rec.id=%s
AND fmt.id_bskEXTREC=%s
AND fmt.format='hb'"""
params
=
(
-
recid
,
-
recid
)
result
=
run_sql
(
query
,
params
)
if
result
:
item
=
__decompress_last
(
result
[
0
])
collection
=
item
[
0
]
url
=
item
[
1
]
hb
=
item
[
2
]
if
collection
==
0
:
# This is an external item
title
=
hb
.
split
(
'
\n
'
,
1
)[
0
]
elif
collection
>
0
:
# This is an external record from a hosted collection
title
=
hb
.
split
(
'</strong>'
,
1
)[
0
]
.
split
(
'<strong>'
)[
-
1
]
return
(
title
,
url
)
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))
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_user_group_basket_ids_by_group
(
uid
):
"""For a given user return all their group basket ids grouped by group."""
query
=
""" SELECT ug.id,
ug.name,
GROUP_CONCAT(ugbsk.id_bskBASKET)
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_user_group_basket_ids_by_group_with_add_rights
(
uid
):
"""For a given user return all their group basket ids grouped by group.
Return only the basket ids to which it is allowed to add records."""
query
=
""" SELECT ug.name,
GROUP_CONCAT(ugbsk.id_bskBASKET)
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 ugbsk.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,
ugbsk.share_level,
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
%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 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,
ugbsk.share_level,
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
%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
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,
ugbsk.share_level,
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
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,
ugbsk.share_level,
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,
ugbsk.share_level,
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,
ugbsk.share_level,
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,
ugbsk.share_level,
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,
ugbsk.share_level,
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,
ugbsk.share_level,
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
,
date_creation
=
None
,
reply_to
=
None
):
"""Save then given note (title, body) on the given item in the given basket.
@param date_creation: date in which the note was created
@type date_creation: None or String, e.g: '2011-07-04 14:20:57'
Note: convert_datestruct_to_datetext((2005, 11, 16, 15, 11, 44, 2, 320, 0)) -> '2005-11-16 15:11:44'
"""
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
]
if
not
date_creation
:
date
=
convert_datestruct_to_datetext
(
localtime
())
else
:
#the date comes with the proper format
date
=
date_creation
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
)
def
get_number_of_notes_per_record_in_basket
(
bskid
,
recids
):
"""Returns the number of comments per record
for all the given records in the given basket"""
# We need to convert the list of recids into a string of commma separated
# numbers (recids), instead of a tuple, to cover the case where we have
# single element lists of recids. Example:
# [1] --> '1' instaed of [1] --> (1,)
# Single element tuples would cause the query to fail due to the syntax.
query
=
""" SELECT rec.id_bibrec_or_bskEXTREC,
COUNT(cmt.id_bibrec_or_bskEXTREC)
FROM bskREC as rec
LEFT JOIN bskRECORDCOMMENT as cmt
ON cmt.id_bibrec_or_bskEXTREC = rec.id_bibrec_or_bskEXTREC
WHERE rec.id_bskBASKET=%%s
AND rec.id_bibrec_or_bskEXTREC IN (%s)
GROUP BY id_bibrec_or_bskEXTREC
ORDER BY rec.score"""
%
(
str
(
map
(
int
,
recids
))[
1
:
-
1
],)
params
=
(
bskid
,)
result
=
run_sql
(
query
,
params
)
return
result
########################## 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
),
encode_for_xml
(
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
Event Timeline
Log In to Comment