diff options
author | Yorhel <git@yorhel.nl> | 2021-11-18 10:51:58 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-11-18 10:52:04 +0100 |
commit | 5b4bd7befcc80cbaf43c07a356acbd45dfdd5dfa (patch) | |
tree | 1fece90a062eb79de5a3060d989225ace66137cb /util | |
parent | 4a167aaad01fae953ecd72ecccff78ee6248d1fb (diff) |
Releases: Experiment with new SQL search implementation
Goal is to use the improved search normalisation mechanism that VN
search has had for ages on other database types as well (releases with
this commit; producers, staff and characters to come later).
Another goal is to do the search cache updates within SQL, removing the
Perl search normalization and the round-trip through Multi every time an
entry needs to be updated.
Requires the 'unaccent' Postgres extension. I considered writing my own
unaccent rules file, but decided against it for now. Current approach
works pretty well.
Diffstat (limited to 'util')
-rwxr-xr-x | util/devdump.pl | 2 | ||||
-rw-r--r-- | util/updates/2021-11-18-release-search.sql | 3 |
2 files changed, 4 insertions, 1 deletions
diff --git a/util/devdump.pl b/util/devdump.pl index 38ffa16d..387e0bb9 100755 --- a/util/devdump.pl +++ b/util/devdump.pl @@ -84,7 +84,7 @@ sub copy_entry { for(@$tables) { my $add = ''; $add = " AND vid IN($vids)" if /^releases_vn/ || /^vn_relations/ || /^chars_vns/; - copy $_ => "SELECT * FROM $_ WHERE id IN($ids) $add"; + copy $_ => "SELECT * FROM $_ WHERE id IN($ids) $add", $_ eq 'releases' ? { c_search => 'del' } : (); copy "${_}_hist" => "SELECT x.* FROM ${_}_hist x JOIN changes c ON c.id = x.chid WHERE c.itemid IN($ids) $add"; } } diff --git a/util/updates/2021-11-18-release-search.sql b/util/updates/2021-11-18-release-search.sql new file mode 100644 index 00000000..9627a188 --- /dev/null +++ b/util/updates/2021-11-18-release-search.sql @@ -0,0 +1,3 @@ +CREATE EXTENSION unaccent; +\i sql/func.sql +ALTER TABLE releases ADD COLUMN c_search text NOT NULL GENERATED ALWAYS AS (public.search_gen(hidden, ARRAY[title, original])) STORED; |