summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-11-18 10:51:58 +0100
committerYorhel <git@yorhel.nl>2021-11-18 10:52:04 +0100
commit5b4bd7befcc80cbaf43c07a356acbd45dfdd5dfa (patch)
tree1fece90a062eb79de5a3060d989225ace66137cb /util
parent4a167aaad01fae953ecd72ecccff78ee6248d1fb (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-xutil/devdump.pl2
-rw-r--r--util/updates/2021-11-18-release-search.sql3
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;