public inbox for [email protected]  

help / color / mirror / Atom feed

From: Célestin Matte <[email protected]>
To: PostgreSQL WWW <[email protected]>
Subject: [PATCH] pgweb: Search: bugfix: replace sites.baseurl with sites.hostname
Date: Fri, 22 Oct 2021 16:59:17 +0200
Message-ID: <[email protected]> (raw)

Hello,

There is an issue in the sql files for search in pgweb, as schema.sql does not correspond to what is used in functions.sql.
functions.sql uses sites.baseurl, which is not a field in schema.sql. I replaced it with sites.hostname, which I guess is the targeted field.

Without that patch, I get the following error when performing a search:

Traceback (most recent call last):                                                                                     
  File "/srv/pgweb/local/pgweb/search/views.py", line 284, in search                                                   
    'internal': include_internal,                                                                                      
psycopg2.errors.UndefinedColumn: column sites.baseurl does not exist                                                   
LINE 1: SELECT sites.id AS siteid, sites.baseurl, webpages.suburl, t...                                                
                                   ^                                                                                   
QUERY:  SELECT sites.id AS siteid, sites.baseurl, webpages.suburl, ts_rank_cd(fti,tsq) * relprio AS ts_rank_cd FROM web
pages INNER JOIN sites ON webpages.site=sites.id WHERE fti @@ tsq AND site=1 AND (includeinternal OR NOT isinternal) OR
DER BY ts_rank_cd(fti,tsq) * relprio DESC LIMIT 1000                                                                   
CONTEXT:  PL/pgSQL function site_search(text,integer,integer,text,boolean) line 21 at OPEN

Cheers,
-- 
Célestin Matte

Attachments:

  [text/x-patch] 0001-Search-bugfix-replace-sites.baseurl-with-sites.hostn.patch (2.0K, 2-0001-Search-bugfix-replace-sites.baseurl-with-sites.hostn.patch)
  download | inline diff:
From fa7d93df7236721ed97f1f85d974eb1c91a01537 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email protected]>
Date: Fri, 22 Oct 2021 16:32:04 +0200
Subject: [PATCH] Search: bugfix: replace sites.baseurl with sites.hostname

---
 tools/search/sql/functions.sql | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

diff --git a/tools/search/sql/functions.sql b/tools/search/sql/functions.sql
index 9ee5c4a5..b3dcc6c4 100644
--- a/tools/search/sql/functions.sql
+++ b/tools/search/sql/functions.sql
@@ -20,9 +20,9 @@ BEGIN
 
     SELECT INTO pagecount sites.pagecount FROM sites WHERE id=1;
     IF _suburl IS NULL THEN
-        OPEN curs FOR SELECT sites.id AS siteid, sites.baseurl, webpages.suburl, ts_rank_cd(fti,tsq) * relprio AS ts_rank_cd FROM webpages INNER JOIN sites ON webpages.site=sites.id WHERE fti @@ tsq AND site=1 AND (includeinternal OR NOT isinternal) ORDER BY ts_rank_cd(fti,tsq) * relprio DESC LIMIT 1000;
+        OPEN curs FOR SELECT sites.id AS siteid, sites.hostname AS baseurl, webpages.suburl, ts_rank_cd(fti,tsq) * relprio AS ts_rank_cd FROM webpages INNER JOIN sites ON webpages.site=sites.id WHERE fti @@ tsq AND site=1 AND (includeinternal OR NOT isinternal) ORDER BY ts_rank_cd(fti,tsq) * relprio DESC LIMIT 1000;
     ELSE
-        OPEN curs FOR SELECT sites.id AS siteid, sites.baseurl, webpages.suburl, ts_rank_cd(fti,tsq) * relprio AS ts_rank_cd FROM webpages INNER JOIN sites ON webpages.site=sites.id WHERE fti @@ tsq AND site=1 AND webpages.suburl LIKE _suburl||'%' AND (includeinternal OR NOT isinternal) ORDER BY ts_rank_cd(fti,tsq) * relprio DESC LIMIT 1000;
+        OPEN curs FOR SELECT sites.id AS siteid, sites.hostname AS baseurl, webpages.suburl, ts_rank_cd(fti,tsq) * relprio AS ts_rank_cd FROM webpages INNER JOIN sites ON webpages.site=sites.id WHERE fti @@ tsq AND site=1 AND webpages.suburl LIKE _suburl||'%' AND (includeinternal OR NOT isinternal) ORDER BY ts_rank_cd(fti,tsq) * relprio DESC LIMIT 1000;
     END IF;
     LOOP
        FETCH curs INTO hit;
-- 
2.33.1

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: [PATCH] pgweb: Search: bugfix: replace sites.baseurl with sites.hostname
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox