/
pg.go
106 lines (86 loc) · 3.52 KB
/
pg.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
// Package pg implements the search.Searcher interface with the existing PostgreSQL database.
package pg
import (
"context"
"strings"
"time"
"github.com/georgysavva/scany/pgxscan"
"github.com/jackc/pgx/v4/pgxpool"
"github.com/termora/berry/db/search"
)
// New returns a Searcher based on the given pgxpool.Pool
func New(pool *pgxpool.Pool, debugFunc func(string, ...interface{})) search.Searcher {
if debugFunc == nil {
debugFunc = func(string, ...interface{}) {
return
}
}
return &pg{pool, debugFunc}
}
var _ search.Searcher = (*pg)(nil)
type pg struct {
*pgxpool.Pool
Debug func(template string, args ...interface{})
}
func getContext() (context.Context, context.CancelFunc) {
return context.WithTimeout(context.Background(), 10*time.Second)
}
// Search searches the database for terms
func (db *pg) Search(input string, limit int, ignore []string) (terms []*search.Term, err error) {
if limit == 0 {
limit = 50
}
db.Debug("Searching for terms `%v`, limit %v, ignoring `%v`", input, limit, ignore)
ctx, cancel := getContext()
defer cancel()
err = pgxscan.Select(ctx, db.Pool, &terms, `select
t.id, t.category, c.name as category_name, t.name, t.aliases, t.description, t.note, t.source, t.created, t.last_modified, t.flags, t.tags, t.content_warnings, t.image_url,
array(select display from public.tags where normalized = any(t.tags)) as display_tags,
ts_rank_cd(t.searchtext, websearch_to_tsquery('english', $1), 8) as rank,
ts_headline(t.description, websearch_to_tsquery('english', $1), 'StartSel=**, StopSel=**') as headline
from public.terms as t, public.categories as c
where t.searchtext @@ websearch_to_tsquery('english', $1) and t.category = c.id and t.flags & $3 = 0
and not $4 && tags
order by rank desc
limit $2`, input, limit, search.FlagSearchHidden, ignore)
return terms, err
}
func (db *pg) Autocomplete(input string) (terms []string, err error) {
ctx, cancel := getContext()
defer cancel()
input = strings.ToLower(input)
err = db.QueryRow(ctx, "select array(select name from terms where position($1 in lower(name)) > 0 order by name limit 25)", input).Scan(&terms)
return terms, err
}
// SearchCat searches for terms from a single category
func (db *pg) SearchCat(input string, cat, limit int, ignore []string) (terms []*search.Term, err error) {
if limit == 0 {
limit = 50
}
db.Debug("Searching for terms `%v` in category %v, limit %v, ignoring `%v`", input, cat, limit, ignore)
ctx, cancel := getContext()
defer cancel()
err = pgxscan.Select(ctx, db.Pool, &terms, `select
t.id, t.category, c.name as category_name, t.name, t.aliases, t.description, t.note, t.source, t.created, t.last_modified, t.flags, t.tags, t.content_warnings, t.image_url,
array(select display from public.tags where normalized = any(t.tags)) as display_tags,
ts_rank_cd(t.searchtext, websearch_to_tsquery('english', $1), 8) as rank,
ts_headline(t.description, websearch_to_tsquery('english', $1), 'StartSel=**, StopSel=**') as headline
from public.terms as t, public.categories as c
where t.searchtext @@ websearch_to_tsquery('english', $1) and t.category = c.id and t.flags & $3 = 0 and t.category = $4
and not $5 && tags
order by rank desc
limit $2`, input, limit, search.FlagSearchHidden, cat, ignore)
return terms, err
}
// SyncTerms is no-op in the postgres backend.
func (*pg) SyncTerms([]*search.Term) error {
return nil
}
// SyncTerm is no-op in the postgres backend.
func (*pg) SyncTerm(*search.Term) error {
return nil
}
// SyncDelete is no-op in the postgres backend.
func (*pg) SyncDelete(int) error {
return nil
}