________________________________
From: Ganguly, Arindam
Sent: Monday, December 03, 2007 4:48 PM
To: 'Martin Flack'
Cc: '[EMAIL PROTECTED]'
Subject: connotea install complete...but still some errors
Hi martin,
I've been talking to you regarding the connotea install. I was able to
get the installation part complete and actually see and register on the
local implement of the site.
I'm still seeing a couple of issues however. After logging in and
clicking on 'My Library'
I get the following error.
Error exception report:
user aganguly (1) requests /user/aganguly bringing load to with db at
1196728958
http://localhost/bibliotech/bibliotech/user/aganguly
<http://localhost/bibliotech/bibliotech/user/aganguly>
undef error - DBD::mysql::st execute failed: Unknown column 'node' in
'where clause' [for Statement "SELECT metadata_type, metadata_value FROM
metadata WHERE node='User:aganguly' AND version='0'"] at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/ContextualFetch.pm line 52.
Issue with the wiki-tookkit?
Secondly doing a search returns
Error exception report:
user aganguly (1) requests /search?q=tag bringing load to with db at
1196729034
http://localhost/bibliotech/bibliotech/search
<http://localhost/bibliotech/bibliotech/search>
undef error - DBD::mysql::st execute failed: Can't find FULLTEXT index
matching the column list [for Statement "SELECT ub.user_bookmark_id,
ub.user, ub.bookmark, ub.updated, ub.citation, ub.user_is_author
, ub.def_public, ub.private, ub.private_gang, ub.private_until,
ub.quarantined, ub.created, u.user_id, u.username, u.openurl_resolver,
u.openurl_name, u.updated, IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(g.gang_id),':/:',IFNULL(
g.name, '+NULL'),':/:',IFNULL(g.owner, '+NULL'),':/:',IFNULL(g.private,
'+NULL'),':/:',IFNULL(g.updated, '+NULL') ORDER BY ug.created SEPARATOR
'///'), '') AS _u_gangs_packed,
b.bookmark_id, b.url, b.hash, b.updated, b.citation,
ubd.user_bookmark_id, ubd.title, ubd.description, bd.bookmark_id,
bd.title, bd.created, ct.citation_id, ct.title, ct.journal, ct.volume,
ct.issue, ct.start_page, ct.end_page
, ct.pubmed, ct.doi, ct.asin, ct.ris_type, ct.raw_date, ct.date,
ct.last_modified_date, ct.user_supplied, ct.cs_module, ct.cs_type,
ct.cs_source, ct.created, j.journal_id, j.name, j.issn, j.coden
, j.country, j.medline_code, j.medline_ta, j.nlm_unique_id,
cta.citation_author_id, cta.citation, cta.author, cta.displayorder,
IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(a.author_id),':/:',IFNULL(a.firstname, '+NULL'),':/:',IFNULL(
a.forename, '+NULL'),':/:',IFNULL(a.initials,
'+NULL'),':/:',IFNULL(a.middlename, '+NULL'),':/:',IFNULL(a.lastname,
'+NULL'),':/:',IFNULL(a.suffix, '+NULL'),':/:',IFNULL(
a.misc, '+NULL'),':/:',IFNULL(a.postal_address,
'+NULL'),':/:',IFNULL(a.affiliation, '+NULL'),':/:',IFNULL(a.email,
'+NULL'),':/:',IFNULL(a.user, '+NULL') ORDER BY
cta.displayorder SEPARATOR '///'), '') AS _ct_authors_packed,
ct2.citation_id, ct2.title, ct2.journal, ct2.volume, ct2.issue,
ct2.start_page, ct2.end_page, ct2.pubmed, ct2.doi, ct2.asin,
ct2.ris_type, ct2.raw_date
, ct2.date, ct2.last_modified_date, ct2.user_supplied, ct2.cs_module,
ct2.cs_type, ct2.cs_source, ct2.created, j2.journal_id, j2.name,
j2.issn, j2.coden, j2.country, j2.medline_code, j2.medline_ta
, j2.nlm_unique_id, cta2.citation_author_id, cta2.citation, cta2.author,
cta2.displayorder, IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(a2.author_id),':/:',IFNULL(a2.firstname,
'+NULL'),':/:',IFNULL(a2.forename
, '+NULL'),':/:',IFNULL(a2.initials,
'+NULL'),':/:',IFNULL(a2.middlename, '+NULL'),':/:',IFNULL(a2.lastname,
'+NULL'),':/:',IFNULL(a2.suffix, '+NULL'),':/:',IFNULL(
a2.misc, '+NULL'),':/:',IFNULL(a2.postal_address,
'+NULL'),':/:',IFNULL(a2.affiliation, '+NULL'),':/:',IFNULL(a2.email,
'+NULL'),':/:',IFNULL(a2.user, '+NULL') ORDER BY
cta2.displayorder SEPARATOR '///'), '') AS _ct2_authors_packed,
IFNULL(GROUP_CONCAT(DISTINCT CONCAT(t2.tag_id),':/:',IFNULL(t2.name,
'+NULL') ORDER BY ubt2.created
SEPARATOR '///'), '') AS _ub_tags_packed, COUNT(DISTINCT
ub2.user_bookmark_id) as _ub_user_bookmarks_count, COUNT(DISTINCT
c2.comment_id) as _ub_comments_count, COUNT(DISTINCT
ub3.user_bookmark_id) as _ub_bookmark_is_linked_by_current_user,
COUNT(DISTINCT
t4.tag_id) as _ub_is_geotagged, MAX(UNIX_TIMESTAMP(ub.created))
FROM
(SELECT MAX(ub.user_bookmark_id) as max_user_bookmark_id, fm.sortvalue
as sortvalue
FROM user_bookmark ub
LEFT JOIN bookmark b ON (ub.bookmark=b.bookmark_id
)
INNER JOIN (SELECT fmat.user_bookmark_id,
MAX(score)*1000000000+UNIX_TIMESTAMP(ub.created) as sortvalue
FROM
(
SELECT user_bookmark_id, MAX(score) as score, 1 as positive FROM (SELECT
ubd_s.user_bookmark_id, 100 as score
FROM user_bookmark_details ubd_s
WHERE ubd_s.title = ?
UNION
SELECT ub.user_bookmark_id, 100 as score
FROM bookmark_details bd_s
LEFT JOIN bookmark b_s ON (bd_s.bookmark_id=b_s.bookmark_id)
LEFT JOIN user_bookmark ub ON (b_s.bookmark_id=ub.bookmark)
WHERE bd_s.title = ? AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 99 as score
FROM citation c_s
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE c_s.title = ? AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 99 as score
FROM citation c_s
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE c_s.title = ? AND ub.user_bookmark_id
IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 98 as score
FROM journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE j_s.name = ? AND c_s.citation_id IS NOT NULL AND
b_s.bookmark_id IS NOT NULL AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id
, 98 as score
FROM journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE j_s.name = ? AND c_s.citation_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 98 as score
FROM journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE j_s.medline_ta = ? AND c_s.citation_id IS NOT NULL AND
b_s.bookmark_id IS NOT NULL AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT
ub.user_bookmark_id, 98 as score
FROM journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE j_s.medline_ta = ? AND c_s.citation_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 97 as score
FROM author a_s
LEFT JOIN citation_author cta_s ON (a_s.author_id=cta_s.author)
LEFT JOIN citation c_s ON (c_s.citation_id=cta_s.citation)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (b_s.bookmark_id=ub.bookmark)
WHERE a_s.lastname = ? AND cta_s.citation_author_id IS NOT NULL AND
c_s.citation_id IS NOT NULL AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 97 as score
FROM author a_s
LEFT JOIN citation_author cta_s ON (a_s.author_id=cta_s.author)
LEFT JOIN citation c_s ON (c_s.citation_id=cta_s.citation)
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE a_s.lastname = ? AND cta_s.citation_author_id IS NOT NULL AND
c_s.citation_id IS NOT NULL AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ubd_s.user_bookmark_id, 50 as score
FROM bibliotech_search.user_bookmark_details ubd_s
WHERE MATCH(ubd_s.title) AGAINST (?)
UNION
SELECT ub.user_bookmark_id, 50 as score
FROM bibliotech_search.bookmark_details bd_s
LEFT JOIN bookmark b_s ON (bd_s.bookmark_id=b_s.bookmark_id)
LEFT JOIN user_bookmark ub ON (b_s.bookmark_id=ub.bookmark)
WHERE MATCH(bd_s.title) AGAINST (?) AND b_s.bookmark_id IS NOT NULL
AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 49 as score
FROM bibliotech_search.citation c_s
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (
ub.bookmark=b_s.bookmark_id)
WHERE MATCH(c_s.title) AGAINST (?) AND b_s.bookmark_id IS NOT NULL
AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 49 as score
FROM bibliotech_search.citation c_s
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE MATCH(c_s.title) AGAINST (?) AND ub.user_bookmark_id IS NOT
NULL
UNION
SELECT ub.user_bookmark_id, 48 as score
FROM bibliotech_search.journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE MATCH(j_s.name) AGAINST (?) AND c_s.citation_id IS NOT NULL AND
b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 48 as score
FROM bibliotech_search.journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN user_bookmark ub ON (
ub.citation=c_s.citation_id)
WHERE MATCH(j_s.name) AGAINST (?) AND c_s.citation_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 48 as score
FROM bibliotech_search.journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE MATCH(j_s.medline_ta) AGAINST (?) AND c_s.citation_id IS NOT
NULL AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 48 as score
FROM bibliotech_search.journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN user_bookmark ub ON (
ub.citation=c_s.citation_id)
WHERE MATCH(j_s.medline_ta) AGAINST (?) AND c_s.citation_id IS NOT
NULL AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 47 as score
FROM bibliotech_search.bookmark b_s
LEFT JOIN user_bookmark ub ON (b_s.bookmark_id=ub.bookmark)
WHERE MATCH(b_s.url) AGAINST (?) AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ubd_s.user_bookmark_id, 45 as score
FROM bibliotech_search.user_bookmark_details ubd_s
WHERE MATCH(ubd_s.description) AGAINST (?)
UNION
SELECT ub.user_bookmark_id, 44 as score
FROM bibliotech_search.comment c_s
LEFT JOIN user_bookmark_comment ubc_s ON
(c_s.comment_id=ubc_s.comment)
LEFT JOIN user_bookmark ub ON
(ubc_s.user_bookmark=ub.user_bookmark_id)
WHERE MATCH(c_s.entry) AGAINST (?) AND ubc_s.user_bookmark_comment_id
IS NOT NULL AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 43 as score
FROM bibliotech_search.author a_s
LEFT JOIN citation_author cta_s ON (a_s.author_id=cta_s.author)
LEFT JOIN citation c_s ON (c_s.citation_id=cta_s.citation)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE MATCH(a_s.lastname, a_s.forename, a_s.firstname) AGAINST (?)
AND cta_s.citation_author_id IS NOT NULL AND c_s.citation_id IS NOT NULL
AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 43 as score
FROM bibliotech_search.author a_s
LEFT JOIN citation_author cta_s ON (a_s.author_id=cta_s.author)
LEFT JOIN citation c_s ON (c_s.citation_id=cta_s.citation)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE MATCH(a_s.lastname, a_s.forename, a_s.firstname) AGAINST (?)
AND cta_s.citation_author_id IS NOT NULL AND c_s.citation_id IS NOT NULL
AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ubt_s.user_bookmark as user_bookmark_id, 20 as score
FROM tag t_s
LEFT JOIN user_bookmark_tag ubt_s ON (ubt_s.tag=t_s.tag_id)
WHERE t_s.name = ? AND ubt_s.user_bookmark_tag_id IS NOT NULL
UNION
SELECT ubt_s.user_bookmark as user_bookmark_id, 20 as score
FROM bibliotech_search.tag t_s
LEFT JOIN user_bookmark_tag ubt_s ON (ubt_s.tag=t_s.tag_id)
WHERE MATCH(t_s.name) AGAINST (?) AND ubt_s.user_bookmark_tag_id IS
NOT NULL
) as fmot1 GROUP BY user_bookmark_id
) as fmat
LEFT JOIN user_bookmark ub ON
(fmat.user_bookmark_id=ub.user_bookmark_id)
GROUP BY fmat.user_bookmark_id
HAVING SUM(fmat.positive) = ? AND MIN(fmat.positive) = ?
) as fm ON (ub.user_bookmark_id=fm.user_bookmark_id)
WHERE ub.user_bookmark_id IS NOT NULL AND ((((ub.private = 0 AND
ub.private_gang IS NULL) OR (ub.private_until IS NOT NULL AND
ub.private_until <= NOW())) AND
ub.quarantined IS NULL) OR ub.user = ?)
GROUP BY b.bookmark_id
ORDER BY sortvalue DESC
LIMIT 0, 10) as ubp
LEFT JOIN user_bookmark ub ON
(ubp.max_user_bookmark_id=ub.user_bookmark_id)
LEFT JOIN user u ON (
ub.user=u.user_id)
LEFT JOIN user_bookmark_tag ubt2 ON
(ub.user_bookmark_id=ubt2.user_bookmark)
LEFT JOIN tag t2 ON (ubt2.tag=t2.tag_id)
LEFT JOIN user_bookmark_details ubd ON
(ub.user_bookmark_id=ubd.user_bookmark_id
)
LEFT JOIN citation ct ON (ub.citation=ct.citation_id)
LEFT JOIN citation_author cta ON (ct.citation_id=cta.citation)
LEFT JOIN author a ON (cta.author=a.author_id)
LEFT JOIN journal j ON (ct.journal=j.journal_id
)
LEFT JOIN bookmark b ON (ub.bookmark=b.bookmark_id)
LEFT JOIN bookmark_details bd ON (b.bookmark_id=bd.bookmark_id)
LEFT JOIN citation ct2 ON (b.citation=ct2.citation_id)
LEFT JOIN citation_author cta2 ON (ct2.citation_id=cta2.citation
)
LEFT JOIN author a2 ON (cta2.author=a2.author_id)
LEFT JOIN journal j2 ON (ct2.journal=j2.journal_id)
LEFT JOIN user_gang ug ON (u.user_id=ug.user)
LEFT JOIN gang g ON (ug.gang=g.gang_id)
LEFT JOIN bookmark b2 ON (
ub.bookmark=b2.bookmark_id)
LEFT JOIN user_bookmark ub2 ON (b2.bookmark_id=ub2.bookmark AND
((((ub2.private = 0 AND ub2.private_gang IS NULL) OR (ub2.private_until
IS NOT NULL AND ub2.private_until <= NOW())) AND ub2.quarantined
IS NULL) OR ub2.user = ?))
LEFT JOIN user_bookmark_comment ubc2 ON
(ub2.user_bookmark_id=ubc2.user_bookmark)
LEFT JOIN comment c2 ON (ubc2.comment=c2.comment_id)
LEFT JOIN user_bookmark ub3 ON (ubc2.user_bookmark=ub3.user_bookmark_id
AND ub3.user = ?)
LEFT JOIN user_bookmark_tag ubt4 ON
(ub.user_bookmark_id=ubt4.user_bookmark)
LEFT JOIN tag t4 ON (ubt4.tag=t4.tag_id AND t4.name = ?)
WHERE ub.user_bookmark_id IS NOT NULL
GROUP BY ubp.max_user_bookmark_id
ORDER BY sortvalue DESC" with ParamValues: 11="tag", 21="tag", 7="tag",
26=1, 17="tag", 2="tag", 22="tag", 1="tag", 18="tag", 0="tag",
30='geotagged', 23="tag", 16="tag", 13="tag", 29='1', 27='1', 25=1,
6="tag", 28='1', 3="tag", 9="tag", 12="tag", 20="tag", 14="tag",
15="tag", 8="tag", 4="tag", 24="tag", 19="tag", 10="tag", 5="tag"] at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/ContextualFetch.pm line 52.
SQL is:
----------
SELECT ub.user_bookmark_id, ub.user, ub.bookmark, ub.updated,
ub.citation, ub.user_is_author, ub.def_public, ub.private,
ub.private_gang, ub.private_until, ub.quarantined, ub.created, u.user_id
, u.username, u.openurl_resolver, u.openurl_name, u.updated,
IFNULL(GROUP_CONCAT(DISTINCT CONCAT(g.gang_id),':/:',IFNULL(g.name,
'+NULL'),':/:',IFNULL(g.owner, '+NULL'),':/:',IFNULL(
g.private, '+NULL'),':/:',IFNULL(g.updated, '+NULL') ORDER BY ug.created
SEPARATOR '///'), '') AS _u_gangs_packed, b.bookmark_id, b.url, b.hash,
b.updated, b.citation, ubd.user_bookmark_id
, ubd.title, ubd.description, bd.bookmark_id, bd.title, bd.created,
ct.citation_id, ct.title, ct.journal, ct.volume, ct.issue,
ct.start_page, ct.end_page, ct.pubmed, ct.doi, ct.asin, ct.ris_type,
ct.raw_date, ct.date, ct.last_modified_date
, ct.user_supplied, ct.cs_module, ct.cs_type, ct.cs_source, ct.created,
j.journal_id, j.name, j.issn, j.coden, j.country, j.medline_code,
j.medline_ta, j.nlm_unique_id, cta.citation_author_id,
cta.citation, cta.author, cta.displayorder, IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(a.author_id),':/:',IFNULL(a.firstname,
'+NULL'),':/:',IFNULL(a.forename, '+NULL'),':/:',IFNULL(a.initials
, '+NULL'),':/:',IFNULL(a.middlename, '+NULL'),':/:',IFNULL(a.lastname,
'+NULL'),':/:',IFNULL(a.suffix, '+NULL'),':/:',IFNULL(a.misc,
'+NULL'),':/:',IFNULL(
a.postal_address, '+NULL'),':/:',IFNULL(a.affiliation,
'+NULL'),':/:',IFNULL(a.email, '+NULL'),':/:',IFNULL(a.user, '+NULL')
ORDER BY cta.displayorder SEPARATOR '///'), '') AS _ct_authors_packed,
ct2.citation_id, ct2.title, ct2.journal, ct2.volume, ct2.issue,
ct2.start_page, ct2.end_page, ct2.pubmed, ct2.doi, ct2.asin,
ct2.ris_type, ct2.raw_date, ct2.date, ct2.last_modified_date,
ct2.user_supplied, ct2.cs_module,
ct2.cs_type, ct2.cs_source, ct2.created, j2.journal_id, j2.name,
j2.issn, j2.coden, j2.country, j2.medline_code, j2.medline_ta,
j2.nlm_unique_id, cta2.citation_author_id, cta2.citation, cta2.author
, cta2.displayorder, IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(a2.author_id),':/:',IFNULL(a2.firstname,
'+NULL'),':/:',IFNULL(a2.forename, '+NULL'),':/:',IFNULL(a2.initials,
'+NULL'),':/:',IFNULL(
a2.middlename, '+NULL'),':/:',IFNULL(a2.lastname,
'+NULL'),':/:',IFNULL(a2.suffix, '+NULL'),':/:',IFNULL(a2.misc,
'+NULL'),':/:',IFNULL(a2.postal_address, '+NULL'),':/:',IFNULL(
a2.affiliation, '+NULL'),':/:',IFNULL(a2.email,
'+NULL'),':/:',IFNULL(a2.user, '+NULL') ORDER BY cta2.displayorder
SEPARATOR '///'), '') AS _ct2_authors_packed,
IFNULL(GROUP_CONCAT(DISTINCT CONCAT(
t2.tag_id),':/:',IFNULL(t2.name, '+NULL') ORDER BY ubt2.created
SEPARATOR '///'), '') AS _ub_tags_packed, COUNT(DISTINCT
ub2.user_bookmark_id) as _ub_user_bookmarks_count, COUNT(DISTINCT
c2.comment_id) as _ub_comments_count, COUNT(DISTINCT
ub3.user_bookmark_id) as _ub_bookmark_is_linked_by_current_user,
COUNT(DISTINCT t4.tag_id) as _ub_is_geotagged,
MAX(UNIX_TIMESTAMP(ub.created))
FROM
(SELECT MAX(
ub.user_bookmark_id) as max_user_bookmark_id, fm.sortvalue as sortvalue
FROM user_bookmark ub
LEFT JOIN bookmark b ON (ub.bookmark=b.bookmark_id)
INNER JOIN (SELECT fmat.user_bookmark_id,
MAX(score)*1000000000+UNIX_TIMESTAMP(
ub.created) as sortvalue
FROM
(
SELECT user_bookmark_id, MAX(score) as score, 1 as positive FROM (SELECT
ubd_s.user_bookmark_id, 100 as score
FROM user_bookmark_details ubd_s
WHERE ubd_s.title = ?
UNION
SELECT ub.user_bookmark_id, 100 as score
FROM bookmark_details bd_s
LEFT JOIN bookmark b_s ON (bd_s.bookmark_id=b_s.bookmark_id)
LEFT JOIN user_bookmark ub ON (b_s.bookmark_id=ub.bookmark
)
WHERE bd_s.title = ? AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 99 as score
FROM citation c_s
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE c_s.title = ? AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 99 as score
FROM citation c_s
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE c_s.title = ? AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 98 as score
FROM journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE j_s.name = ? AND c_s.citation_id IS NOT NULL AND
b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 98 as score
FROM journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id
)
WHERE j_s.name = ? AND c_s.citation_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 98 as score
FROM journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE j_s.medline_ta = ? AND c_s.citation_id IS NOT NULL AND
b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 98 as score
FROM journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id
)
WHERE j_s.medline_ta = ? AND c_s.citation_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 97 as score
FROM author a_s
LEFT JOIN citation_author cta_s ON (a_s.author_id=cta_s.author)
LEFT JOIN citation c_s ON (c_s.citation_id=cta_s.citation)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (b_s.bookmark_id=ub.bookmark)
WHERE a_s.lastname = ? AND cta_s.citation_author_id IS NOT NULL AND
c_s.citation_id IS NOT NULL AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 97 as score
FROM author a_s
LEFT JOIN citation_author cta_s ON (a_s.author_id=cta_s.author)
LEFT JOIN citation c_s ON (c_s.citation_id=cta_s.citation)
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE a_s.lastname = ? AND cta_s.citation_author_id IS NOT NULL AND
c_s.citation_id IS NOT NULL AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ubd_s.user_bookmark_id, 50 as score
FROM bibliotech_search.user_bookmark_details ubd_s
WHERE MATCH(ubd_s.title) AGAINST (?)
UNION
SELECT ub.user_bookmark_id, 50 as score
FROM bibliotech_search.bookmark_details bd_s
LEFT JOIN bookmark b_s ON (bd_s.bookmark_id=b_s.bookmark_id)
LEFT JOIN user_bookmark ub ON (b_s.bookmark_id=ub.bookmark)
WHERE MATCH(bd_s.title) AGAINST (?) AND b_s.bookmark_id IS NOT NULL
AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 49 as score
FROM bibliotech_search.citation c_s
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (
ub.bookmark=b_s.bookmark_id)
WHERE MATCH(c_s.title) AGAINST (?) AND b_s.bookmark_id IS NOT NULL
AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 49 as score
FROM bibliotech_search.citation c_s
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE MATCH(c_s.title) AGAINST (?) AND ub.user_bookmark_id IS NOT
NULL
UNION
SELECT ub.user_bookmark_id, 48 as score
FROM bibliotech_search.journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE MATCH(j_s.name) AGAINST (?) AND c_s.citation_id IS NOT NULL AND
b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 48 as score
FROM bibliotech_search.journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN user_bookmark ub ON (
ub.citation=c_s.citation_id)
WHERE MATCH(j_s.name) AGAINST (?) AND c_s.citation_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 48 as score
FROM bibliotech_search.journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE MATCH(j_s.medline_ta) AGAINST (?) AND c_s.citation_id IS NOT
NULL AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 48 as score
FROM bibliotech_search.journal j_s
LEFT JOIN citation c_s ON (c_s.journal=j_s.journal_id)
LEFT JOIN user_bookmark ub ON (
ub.citation=c_s.citation_id)
WHERE MATCH(j_s.medline_ta) AGAINST (?) AND c_s.citation_id IS NOT
NULL AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 47 as score
FROM bibliotech_search.bookmark b_s
LEFT JOIN user_bookmark ub ON (b_s.bookmark_id=ub.bookmark)
WHERE MATCH(b_s.url) AGAINST (?) AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ubd_s.user_bookmark_id, 45 as score
FROM bibliotech_search.user_bookmark_details ubd_s
WHERE MATCH(ubd_s.description) AGAINST (?)
UNION
SELECT ub.user_bookmark_id, 44 as score
FROM bibliotech_search.comment c_s
LEFT JOIN user_bookmark_comment ubc_s ON
(c_s.comment_id=ubc_s.comment)
LEFT JOIN user_bookmark ub ON
(ubc_s.user_bookmark=ub.user_bookmark_id)
WHERE MATCH(c_s.entry) AGAINST (?) AND ubc_s.user_bookmark_comment_id
IS NOT NULL AND ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 43 as score
FROM bibliotech_search.author a_s
LEFT JOIN citation_author cta_s ON (a_s.author_id=cta_s.author)
LEFT JOIN citation c_s ON (c_s.citation_id=cta_s.citation)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.bookmark=b_s.bookmark_id)
WHERE MATCH(a_s.lastname, a_s.forename, a_s.firstname) AGAINST (?)
AND cta_s.citation_author_id IS NOT NULL AND c_s.citation_id IS NOT NULL
AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ub.user_bookmark_id, 43 as score
FROM bibliotech_search.author a_s
LEFT JOIN citation_author cta_s ON (a_s.author_id=cta_s.author)
LEFT JOIN citation c_s ON (c_s.citation_id=cta_s.citation)
LEFT JOIN bookmark b_s ON (b_s.citation=c_s.citation_id)
LEFT JOIN user_bookmark ub ON (ub.citation=c_s.citation_id)
WHERE MATCH(a_s.lastname, a_s.forename, a_s.firstname) AGAINST (?)
AND cta_s.citation_author_id IS NOT NULL AND c_s.citation_id IS NOT NULL
AND b_s.bookmark_id IS NOT NULL AND
ub.user_bookmark_id IS NOT NULL
UNION
SELECT ubt_s.user_bookmark as user_bookmark_id, 20 as score
FROM tag t_s
LEFT JOIN user_bookmark_tag ubt_s ON (ubt_s.tag=t_s.tag_id)
WHERE t_s.name = ? AND ubt_s.user_bookmark_tag_id IS NOT NULL
UNION
SELECT ubt_s.user_bookmark as user_bookmark_id, 20 as score
FROM bibliotech_search.tag t_s
LEFT JOIN user_bookmark_tag ubt_s ON (ubt_s.tag=t_s.tag_id)
WHERE MATCH(t_s.name) AGAINST (?) AND ubt_s.user_bookmark_tag_id IS
NOT NULL
) as fmot1 GROUP BY user_bookmark_id
) as fmat
LEFT JOIN user_bookmark ub ON
(fmat.user_bookmark_id=ub.user_bookmark_id)
GROUP BY fmat.user_bookmark_id
HAVING SUM(fmat.positive) = ? AND MIN(fmat.positive) = ?
) as fm ON (ub.user_bookmark_id=fm.user_bookmark_id)
WHERE ub.user_bookmark_id IS NOT NULL AND ((((ub.private = 0 AND
ub.private_gang IS NULL) OR (ub.private_until IS NOT NULL AND
ub.private_until <= NOW())) AND
ub.quarantined IS NULL) OR ub.user = ?)
GROUP BY b.bookmark_id
ORDER BY sortvalue DESC
LIMIT 0, 10) as ubp
LEFT JOIN user_bookmark ub ON
(ubp.max_user_bookmark_id=ub.user_bookmark_id)
LEFT JOIN user u ON (
ub.user=u.user_id)
LEFT JOIN user_bookmark_tag ubt2 ON
(ub.user_bookmark_id=ubt2.user_bookmark)
LEFT JOIN tag t2 ON (ubt2.tag=t2.tag_id)
LEFT JOIN user_bookmark_details ubd ON
(ub.user_bookmark_id=ubd.user_bookmark_id
)
LEFT JOIN citation ct ON (ub.citation=ct.citation_id)
LEFT JOIN citation_author cta ON (ct.citation_id=cta.citation)
LEFT JOIN author a ON (cta.author=a.author_id)
LEFT JOIN journal j ON (ct.journal=j.journal_id
)
LEFT JOIN bookmark b ON (ub.bookmark=b.bookmark_id)
LEFT JOIN bookmark_details bd ON (b.bookmark_id=bd.bookmark_id)
LEFT JOIN citation ct2 ON (b.citation=ct2.citation_id)
LEFT JOIN citation_author cta2 ON (ct2.citation_id=cta2.citation
)
LEFT JOIN author a2 ON (cta2.author=a2.author_id)
LEFT JOIN journal j2 ON (ct2.journal=j2.journal_id)
LEFT JOIN user_gang ug ON (u.user_id=ug.user)
LEFT JOIN gang g ON (ug.gang=g.gang_id)
LEFT JOIN bookmark b2 ON (
ub.bookmark=b2.bookmark_id)
LEFT JOIN user_bookmark ub2 ON (b2.bookmark_id=ub2.bookmark AND
((((ub2.private = 0 AND ub2.private_gang IS NULL) OR (ub2.private_until
IS NOT NULL AND ub2.private_until <= NOW())) AND ub2.quarantined
IS NULL) OR ub2.user = ?))
LEFT JOIN user_bookmark_comment ubc2 ON
(ub2.user_bookmark_id=ubc2.user_bookmark)
LEFT JOIN comment c2 ON (ubc2.comment=c2.comment_id)
LEFT JOIN user_bookmark ub3 ON (ubc2.user_bookmark=ub3.user_bookmark_id
AND ub3.user = ?)
LEFT JOIN user_bookmark_tag ubt4 ON
(ub.user_bookmark_id=ubt4.user_bookmark)
LEFT JOIN tag t4 ON (ubt4.tag=t4.tag_id AND t4.name = ?)
WHERE ub.user_bookmark_id IS NOT NULL
GROUP BY ubp.max_user_bookmark_id
ORDER BY sortvalue DESC
----------
$VAR1 = {
'freematch' => [
'tag'
],
'join_ub' => '((((
ub.private = 0 AND ub.private_gang IS NULL) OR (ub.private_until IS NOT
NULL AND ub.private_until <= NOW())) AND ub.quarantined IS NULL) OR
ub.user = ?)',
'order_by' => 'ORDER BY sortvalue DESC',
'having' => '',
'wbind' => [],
'bind_ub' => [
'1'
],
'bind_ub3' => [
'1'
],
'group_by' => 'GROUP BY b.bookmark_id',
'limit' => 'LIMIT 0, 10',
'bind_t4' => [
'geotagged'
],
'select' => [
'MAX(ub.user_bookmark_id)',
'ub.user',
'ub.bookmark',
'ub.updated',
'ub.citation',
'ub.user_is_author',
'
ub.def_public',
'ub.private',
'ub.private_gang',
'ub.private_until',
'ub.quarantined
',
'ub.created',
'u.user_id',
'u.username',
'u.openurl_resolver',
'
u.openurl_name',
'u.updated',
'IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(g.gang_id),\':/:\',IFNULL(g.name, \'+NULL\'),\':/:\',IFNULL(
g.owner, \'+NULL\'),\':/:\',IFNULL(g.private,
\'+NULL\'),\':/:\',IFNULL(g.updated, \'+NULL\') ORDER BY ug.created
SEPARATOR \'///\'), \'\') AS _u_gangs_packed',
'b.bookmark_id',
'b.url',
'b.hash',
'b.updated',
'b.citation
',
'ubd.user_bookmark_id',
'ubd.title',
'ubd.description',
'bd.bookmark_id',
'bd.title',
'bd.created',
'ct.citation_id',
'ct.title',
'
ct.journal',
'ct.volume',
'ct.issue',
'ct.start_page',
'ct.end_page',
'
ct.pubmed',
'ct.doi',
'ct.asin',
'ct.ris_type',
'ct.raw_date',
'
ct.date',
'ct.last_modified_date',
'ct.user_supplied',
'ct.cs_module',
'ct.cs_type
',
'ct.cs_source',
'ct.created',
'j.journal_id',
'j.name
',
'j.issn',
'j.coden',
'j.country',
'j.medline_code',
'
j.medline_ta',
'j.nlm_unique_id',
'cta.citation_author_id',
'cta.citation',
'cta.author
',
'cta.displayorder',
'IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(a.author_id),\':/:\',IFNULL(a.firstname,
\'+NULL\'),\':/:\',IFNULL(
a.forename, \'+NULL\'),\':/:\',IFNULL(a.initials,
\'+NULL\'),\':/:\',IFNULL(a.middlename,
\'+NULL\'),\':/:\',IFNULL(a.lastname, \'+NULL\'),\':/:\',IFNULL(a.suffix
, \'+NULL\'),\':/:\',IFNULL(a.misc,
\'+NULL\'),\':/:\',IFNULL(a.postal_address,
\'+NULL\'),\':/:\',IFNULL(a.affiliation,
\'+NULL\'),\':/:\',IFNULL(a.email, \'+NULL\'),\':/:\',IFNULL(
a.user, \'+NULL\') ORDER BY cta.displayorder SEPARATOR \'///\'), \'\')
AS _ct_authors_packed',
'ct2.citation_id',
'ct2.title',
'ct2.journal',
'ct2.volume',
'ct2.issue',
'ct2.start_page',
'
ct2.end_page',
'ct2.pubmed',
'ct2.doi',
'ct2.asin',
'ct2.ris_type',
'
ct2.raw_date',
'ct2.date',
'ct2.last_modified_date',
'ct2.user_supplied',
'ct2.cs_module
',
'ct2.cs_type',
'ct2.cs_source',
'ct2.created',
'j2.journal_id',
'
j2.name',
'j2.issn',
'j2.coden',
'j2.country',
'j2.medline_code
',
'j2.medline_ta',
'j2.nlm_unique_id',
'cta2.citation_author_id',
'cta2.citation
',
'cta2.author',
'cta2.displayorder',
'IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(a2.author_id),\':/:\',IFNULL(a2.firstname
, \'+NULL\'),\':/:\',IFNULL(a2.forename,
\'+NULL\'),\':/:\',IFNULL(a2.initials,
\'+NULL\'),\':/:\',IFNULL(a2.middlename,
\'+NULL\'),\':/:\',IFNULL(a2.lastname
, \'+NULL\'),\':/:\',IFNULL(a2.suffix,
\'+NULL\'),\':/:\',IFNULL(a2.misc,
\'+NULL\'),\':/:\',IFNULL(a2.postal_address,
\'+NULL\'),\':/:\',IFNULL(a2.affiliation
, \'+NULL\'),\':/:\',IFNULL(a2.email, \'+NULL\'),\':/:\',IFNULL(a2.user,
\'+NULL\') ORDER BY cta2.displayorder SEPARATOR \'///\'), \'\') AS
_ct2_authors_packed',
'IFNULL(GROUP_CONCAT(DISTINCT
CONCAT(t2.tag_id),\':/:\',IFNULL(t2.name, \'+NULL\') ORDER BY
ubt2.created SEPARATOR \'///\'), \'\') AS _ub_tags_packed',
'COUNT(DISTINCT ub2.user_bookmark_id) as
_ub_user_bookmarks_count',
'COUNT(DISTINCT c2.comment_id) as
_ub_comments_count',
'COUNT(DISTINCT
ub3.user_bookmark_id) as _ub_bookmark_is_linked_by_current_user',
'COUNT(DISTINCT t4.tag_id) as _ub_is_geotagged',
'MAX(UNIX_TIMESTAMP(ub.created)) AS sortvalue'
],
'join_ub2' => '((((ub2.private = 0 AND ub2.private_gang IS
NULL) OR (ub2.private_until IS NOT NULL AND ub2.private_until <= NOW()))
AND ub2.quarantined IS NULL) OR
ub2.user = ?)',
'where' => 'ub.user_bookmark_id IS NOT NULL',
'join_ub3' => 'ub3.user = ?',
'join_t4' => ' <http://t4.name>
t4.name = ?',
'class' => 'Bibliotech::User_Bookmark',
'hbind' => [],
'bind_ub2' => $VAR1->{'bind_ub'}
};
$VAR2 = [
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
'tag',
1,
1,
'1',
'1',
'1',
'geotagged'
];
Do you have any suggestions?
Thanks
arindam
-------------------------------------------------------------------------
SF.Net email is sponsored by: The Future of Linux Business White Paper
from Novell. From the desktop to the data center, Linux is going
mainstream. Let it simplify your IT future.
http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4
_______________________________________________
Connotea-code-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/connotea-code-devel
