Content-Type: multipart/mixed; boundary="------------XTnvASIXnDu6Wp0edTpWVl51"
Message-ID: <21fbb020-83c6-33e5-9cfe-0aee56badf3e@cmatte.me>
Date: Fri, 22 Oct 2021 16:59:17 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.2.0
To: PostgreSQL WWW <pgsql-www@lists.postgresql.org>
Content-Language: en-US
From: =?UTF-8?Q?C=c3=a9lestin_Matte?= <celestin.matte@cmatte.me>
Subject: [PATCH] pgweb: Search: bugfix: replace sites.baseurl with
 sites.hostname
Archived-At: 
 <https://www.postgresql.org/message-id/21fbb020-83c6-33e5-9cfe-0aee56badf3e%40cmatte.me>
Precedence: bulk

This is a multi-part message in MIME format.
--------------XTnvASIXnDu6Wp0edTpWVl51
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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
--------------XTnvASIXnDu6Wp0edTpWVl51
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-Search-bugfix-replace-sites.baseurl-with-sites.hostn.patch"
Content-Disposition: attachment;
 filename*0="0001-Search-bugfix-replace-sites.baseurl-with-sites.hostn.pa";
 filename*1="tch"
Content-Transfer-Encoding: base64

RnJvbSBmYTdkOTNkZjcyMzY3MjFlZDk3ZjFmODVkOTc0ZWIxYzkxYTAxNTM3IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiA9P1VURi04P3E/Qz1DMz1BOWxlc3Rpbj0yME1hdHRl
Pz0gPGdpdGxhYkBjbWF0dGUubWU+CkRhdGU6IEZyaSwgMjIgT2N0IDIwMjEgMTY6MzI6MDQg
KzAyMDAKU3ViamVjdDogW1BBVENIXSBTZWFyY2g6IGJ1Z2ZpeDogcmVwbGFjZSBzaXRlcy5i
YXNldXJsIHdpdGggc2l0ZXMuaG9zdG5hbWUKCi0tLQogdG9vbHMvc2VhcmNoL3NxbC9mdW5j
dGlvbnMuc3FsIHwgNCArKy0tCiAxIGZpbGUgY2hhbmdlZCwgMiBpbnNlcnRpb25zKCspLCAy
IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3Rvb2xzL3NlYXJjaC9zcWwvZnVuY3Rpb25z
LnNxbCBiL3Rvb2xzL3NlYXJjaC9zcWwvZnVuY3Rpb25zLnNxbAppbmRleCA5ZWU1YzRhNS4u
YjNkY2M2YzQgMTAwNjQ0Ci0tLSBhL3Rvb2xzL3NlYXJjaC9zcWwvZnVuY3Rpb25zLnNxbAor
KysgYi90b29scy9zZWFyY2gvc3FsL2Z1bmN0aW9ucy5zcWwKQEAgLTIwLDkgKzIwLDkgQEAg
QkVHSU4KIAogICAgIFNFTEVDVCBJTlRPIHBhZ2Vjb3VudCBzaXRlcy5wYWdlY291bnQgRlJP
TSBzaXRlcyBXSEVSRSBpZD0xOwogICAgIElGIF9zdWJ1cmwgSVMgTlVMTCBUSEVOCi0gICAg
ICAgIE9QRU4gY3VycyBGT1IgU0VMRUNUIHNpdGVzLmlkIEFTIHNpdGVpZCwgc2l0ZXMuYmFz
ZXVybCwgd2VicGFnZXMuc3VidXJsLCB0c19yYW5rX2NkKGZ0aSx0c3EpICogcmVscHJpbyBB
UyB0c19yYW5rX2NkIEZST00gd2VicGFnZXMgSU5ORVIgSk9JTiBzaXRlcyBPTiB3ZWJwYWdl
cy5zaXRlPXNpdGVzLmlkIFdIRVJFIGZ0aSBAQCB0c3EgQU5EIHNpdGU9MSBBTkQgKGluY2x1
ZGVpbnRlcm5hbCBPUiBOT1QgaXNpbnRlcm5hbCkgT1JERVIgQlkgdHNfcmFua19jZChmdGks
dHNxKSAqIHJlbHByaW8gREVTQyBMSU1JVCAxMDAwOworICAgICAgICBPUEVOIGN1cnMgRk9S
IFNFTEVDVCBzaXRlcy5pZCBBUyBzaXRlaWQsIHNpdGVzLmhvc3RuYW1lIEFTIGJhc2V1cmws
IHdlYnBhZ2VzLnN1YnVybCwgdHNfcmFua19jZChmdGksdHNxKSAqIHJlbHByaW8gQVMgdHNf
cmFua19jZCBGUk9NIHdlYnBhZ2VzIElOTkVSIEpPSU4gc2l0ZXMgT04gd2VicGFnZXMuc2l0
ZT1zaXRlcy5pZCBXSEVSRSBmdGkgQEAgdHNxIEFORCBzaXRlPTEgQU5EIChpbmNsdWRlaW50
ZXJuYWwgT1IgTk9UIGlzaW50ZXJuYWwpIE9SREVSIEJZIHRzX3JhbmtfY2QoZnRpLHRzcSkg
KiByZWxwcmlvIERFU0MgTElNSVQgMTAwMDsKICAgICBFTFNFCi0gICAgICAgIE9QRU4gY3Vy
cyBGT1IgU0VMRUNUIHNpdGVzLmlkIEFTIHNpdGVpZCwgc2l0ZXMuYmFzZXVybCwgd2VicGFn
ZXMuc3VidXJsLCB0c19yYW5rX2NkKGZ0aSx0c3EpICogcmVscHJpbyBBUyB0c19yYW5rX2Nk
IEZST00gd2VicGFnZXMgSU5ORVIgSk9JTiBzaXRlcyBPTiB3ZWJwYWdlcy5zaXRlPXNpdGVz
LmlkIFdIRVJFIGZ0aSBAQCB0c3EgQU5EIHNpdGU9MSBBTkQgd2VicGFnZXMuc3VidXJsIExJ
S0UgX3N1YnVybHx8JyUnIEFORCAoaW5jbHVkZWludGVybmFsIE9SIE5PVCBpc2ludGVybmFs
KSBPUkRFUiBCWSB0c19yYW5rX2NkKGZ0aSx0c3EpICogcmVscHJpbyBERVNDIExJTUlUIDEw
MDA7CisgICAgICAgIE9QRU4gY3VycyBGT1IgU0VMRUNUIHNpdGVzLmlkIEFTIHNpdGVpZCwg
c2l0ZXMuaG9zdG5hbWUgQVMgYmFzZXVybCwgd2VicGFnZXMuc3VidXJsLCB0c19yYW5rX2Nk
KGZ0aSx0c3EpICogcmVscHJpbyBBUyB0c19yYW5rX2NkIEZST00gd2VicGFnZXMgSU5ORVIg
Sk9JTiBzaXRlcyBPTiB3ZWJwYWdlcy5zaXRlPXNpdGVzLmlkIFdIRVJFIGZ0aSBAQCB0c3Eg
QU5EIHNpdGU9MSBBTkQgd2VicGFnZXMuc3VidXJsIExJS0UgX3N1YnVybHx8JyUnIEFORCAo
aW5jbHVkZWludGVybmFsIE9SIE5PVCBpc2ludGVybmFsKSBPUkRFUiBCWSB0c19yYW5rX2Nk
KGZ0aSx0c3EpICogcmVscHJpbyBERVNDIExJTUlUIDEwMDA7CiAgICAgRU5EIElGOwogICAg
IExPT1AKICAgICAgICBGRVRDSCBjdXJzIElOVE8gaGl0OwotLSAKMi4zMy4xCgo=
--------------XTnvASIXnDu6Wp0edTpWVl51--