summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Makefile6
-rw-r--r--lib/Multi/Maintenance.pm53
-rw-r--r--lib/VNDB/DB/VN.pm33
-rw-r--r--lib/VNDB/Func.pm151
-rw-r--r--lib/VNDBUtil.pm202
-rw-r--r--util/sql/all.sql3
-rw-r--r--util/sql/func.sql46
-rw-r--r--util/updates/update_2.12.sql9
8 files changed, 325 insertions, 178 deletions
diff --git a/Makefile b/Makefile
index 9ea395df..81562a8f 100644
--- a/Makefile
+++ b/Makefile
@@ -37,7 +37,7 @@
# environments. Patches to improve the portability are always welcome.
-.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10 update-2.11
+.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10 update-2.11 update-2.12
all: dirs js skins robots data/config.pl
@@ -141,3 +141,7 @@ update-2.11: all
${runpsql} < util/updates/update_2.11.sql
$(multi-start)
+update-2.12: all
+ $(multi-stop)
+ ${runpsql} < util/updates/update_2.12.sql
+ $(multi-start)
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index be267aba..497b160e 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -9,6 +9,7 @@ use strict;
use warnings;
use POE;
use PerlIO::gzip;
+use VNDBUtil 'normalize_titles';
sub spawn {
@@ -19,11 +20,13 @@ sub spawn {
_start shutdown set_daily daily set_monthly monthly log_stats
vncache_inc tagcache vnpopularity vnrating cleangraphs cleansessions
vncache_full usercache statscache logrotate
+ vnsearch_check vnsearch_gettitles vnsearch_update
|],
],
heap => {
daily => [qw|vncache_inc tagcache vnpopularity vnrating cleangraphs cleansessions|],
monthly => [qw|vncache_full usercache statscache logrotate|],
+ vnsearch_checkdelay => 3600,
@_,
},
);
@@ -35,6 +38,8 @@ sub _start {
$_[KERNEL]->sig(shutdown => 'shutdown');
$_[KERNEL]->yield('set_daily');
$_[KERNEL]->yield('set_monthly');
+ $_[KERNEL]->yield('vnsearch_check');
+ $_[KERNEL]->post(pg => listen => vnsearch => 'vnsearch_check');
}
@@ -235,5 +240,53 @@ sub logrotate {
}
+#
+# V N S E A R C H C A C H E
+#
+
+
+sub vnsearch_check {
+ $_[KERNEL]->call(pg => query =>
+ 'SELECT id FROM vn WHERE NOT hidden AND c_search IS NULL LIMIT 1',
+ undef, 'vnsearch_gettitles');
+}
+
+
+sub vnsearch_gettitles { # num, res
+ return $_[KERNEL]->delay('vnsearch_check', $_[HEAP]{vnsearch_checkdelay}) if $_[ARG0] == 0;
+ my $id = $_[ARG1][0]{id};
+
+ # fetch the titles
+ $_[KERNEL]->call(pg => query => q{
+ SELECT vr.title, vr.original, vr.alias
+ FROM vn v
+ JOIN vn_rev vr ON vr.id = v.latest
+ WHERE v.id = ?
+ UNION
+ SELECT rr.title, rr.original, NULL
+ FROM releases r
+ JOIN releases_rev rr ON rr.id = r.latest
+ JOIN releases_vn rv ON rv.rid = r.latest
+ WHERE rv.vid = ?
+ AND NOT r.hidden
+ }, [ $id, $id ], 'vnsearch_update', $id);
+}
+
+
+sub vnsearch_update { # num, res, vid, time
+ my($res, $id, $time) = @_[ARG1..ARG3];
+ my @t = map +($_->{title}, $_->{original}), @$res;
+ # alias fields are a bit special
+ for (@$res) {
+ push @t, split /,/, $_->{alias} if $_->{alias};
+ }
+ my $t = normalize_titles(@t);
+ $_[KERNEL]->call(core => log => 'Updated search cache for v%d', $id);
+ $_[KERNEL]->call(pg => do =>
+ q|UPDATE vn SET c_search = ? WHERE id = ?|,
+ [ $t, $id ], 'vnsearch_check');
+}
+
+
1;
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index cb1a878c..da479a9a 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -4,7 +4,7 @@ package VNDB::DB::VN;
use strict;
use warnings;
use Exporter 'import';
-use VNDB::Func 'gtintype';
+use VNDB::Func 'gtintype', 'normalize_query';
use Encode 'decode_utf8';
our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;
@@ -19,7 +19,7 @@ sub dbVNGet {
$o{page} ||= 1;
$o{what} ||= '';
- my %where = (
+ my @where = (
$o{id} ? (
'v.id = ?' => $o{id} ) : (),
$o{rev} ? (
@@ -38,36 +38,13 @@ sub dbVNGet {
) : (),
$o{tags_exclude} && @{$o{tags_exclude}} ? (
'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (),
+ $o{search} ? (
+ map +('v.c_search like ?', "%$_%"), normalize_query($o{search})) : (),
# don't fetch hidden items unless we ask for an ID
!$o{id} && !$o{rev} ? (
'v.hidden = FALSE' => 0 ) : (),
);
- if($o{search}) {
- my @w;
- for (split /[ -,._]/, $o{search}) {
- s/%//g;
- if(/^\d+$/ && gtintype($_)) {
- push @w, 'irr.gtin = ?', $_;
- } elsif(length($_) > 0) {
- $_ = "%$_%";
- push @w, '(ivr.title ILIKE ? OR ivr.original ILIKE ? OR ivr.alias ILIKE ? OR irr.title ILIKE ? OR irr.original ILIKE ?)',
- [ $_, $_, $_, $_, $_ ];
- }
- }
- push @w, '(irr.id IS NULL OR ir.latest = irr.id)' => 1 if @w;
- $where{ q|
- v.id IN(SELECT iv.id
- FROM vn iv
- JOIN vn_rev ivr ON iv.latest = ivr.id
- LEFT JOIN releases_vn irv ON irv.vid = iv.id
- LEFT JOIN releases_rev irr ON irr.id = irv.rid
- LEFT JOIN releases ir ON ir.latest = irr.id
- !W
- GROUP BY iv.id)|
- } = [ \@w ] if @w;
- }
-
my @join = (
$o{rev} ?
'JOIN vn v ON v.id = vr.vid' :
@@ -114,7 +91,7 @@ sub dbVNGet {
!s
!W
ORDER BY !s|,
- join(', ', @select), join(' ', @join), \%where, $order,
+ join(', ', @select), join(' ', @join), \@where, $order,
);
if($o{what} =~ /relgraph/) {
diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm
index 1801e3b8..f3f9d009 100644
--- a/lib/VNDB/Func.pm
+++ b/lib/VNDB/Func.pm
@@ -6,155 +6,8 @@ use warnings;
use YAWF ':html';
use Exporter 'import';
use POSIX 'strftime', 'ceil', 'floor';
-our @EXPORT = qw| shorten bb2html gtintype liststat clearfloat cssicon tagscore mt minage |;
-
-
-# I would've done this as a #define if this was C...
-sub shorten {
- my($str, $len) = @_;
- return length($str) > $len ? substr($str, 0, $len-3).'...' : $str;
-}
-
-
-# Arguments: input, and optionally the maximum length
-# Parses:
-# [url=..] [/url]
-# [raw] .. [/raw]
-# [spoiler] .. [/spoiler]
-# [quote] .. [/quote]
-# [code] .. [/code]
-# v+, v+.+
-# http://../
-sub bb2html {
- my $raw = shift;
- my $maxlength = shift;
- $raw =~ s/\r//g;
- $raw =~ s/\n{5,}/\n\n/g;
- return '' if !$raw && $raw ne "0";
-
- my($result, $length, $rmnewline, @open) = ('', 0, 0, 'first');
-
- my $e = sub {
- local $_ = shift;
- s/&/&amp;/g;
- s/>/&gt;/g;
- s/</&lt;/g;
- s/\n/<br \/>/g if !$maxlength;
- s/\n/ /g if $maxlength;
- return $_;
- };
-
- for (split /(\s|\n|\[[^\]]+\])/, $raw) {
- next if !defined $_;
- next if $_ eq '';
-
- # (note to self: stop using unreadable hacks like these!)
- $rmnewline-- && $_ eq "\n" && next if $rmnewline;
-
- my $lit = $_;
- if($open[$#open] ne 'raw' && $open[$#open] ne 'code') {
- if (lc$_ eq '[raw]') { push @open, 'raw'; next }
- elsif (lc$_ eq '[spoiler]') { push @open, 'spoiler'; $result .= '<b class="spoiler">'; next }
- elsif (lc$_ eq '[quote]') {
- push @open, 'quote';
- $result .= '<div class="quote">' if !$maxlength;
- $rmnewline = 1;
- next
- } elsif (lc$_ eq '[code]') {
- push @open, 'code';
- $result .= '<pre>' if !$maxlength;
- $rmnewline = 1;
- next
- } elsif (lc$_ eq '[/spoiler]') {
- if($open[$#open] eq 'spoiler') {
- $result .= '</b>';
- pop @open;
- }
- next;
- } elsif (lc$_ eq '[/quote]') {
- if($open[$#open] eq 'quote') {
- $result .= '</div>' if !$maxlength;
- $rmnewline = 1;
- pop @open;
- }
- next;
- } elsif(lc$_ eq '[/url]') {
- if($open[$#open] eq 'url') {
- $result .= '</a>';
- pop @open;
- }
- next;
- } elsif(s{\[url=((https?://|/)[^\]>]+)\]}{<a href="$1" rel="nofollow">}i) {
- $result .= $_;
- push @open, 'url';
- next;
- } elsif(!grep(/url/, @open) &&
- s{(.*)(http|https)://(.+[\d\w=/-])(.*)}
- {$e->($1).qq|<a href="$2://|.$e->($3, 1).'" rel="nofollow">'.$e->('link').'</a>'.$e->($4)}e) {
- $length += 4;
- last if $maxlength && $length > $maxlength;
- $result .= $_;
- next;
- } elsif(!grep(/url/, @open) && (
- s{^(.*[^\w]|)([tdvpr][1-9][0-9]*)\.([1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2.$3">$2.$3</a>|.$e->($4)}e ||
- s{^(.*[^\w]|)([tdvprug][1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2">$2</a>|.$e->($3)}e)) {
- $length += length $lit;
- last if $maxlength && $length > $maxlength;
- $result .= $_;
- next;
- }
- } elsif($open[$#open] eq 'raw' && lc$_ eq '[/raw]') {
- pop @open;
- next;
- } elsif($open[$#open] eq 'code' && lc$_ eq '[/code]') {
- $result .= '</pre>' if !$maxlength;
- pop @open;
- next;
- }
-
- # normal text processing
- $length += length $_;
- last if $maxlength && $length > $maxlength;
- $result .= $e->($_);
- }
-
- # close open tags
- while((local $_ = pop @open) ne 'first') {
- $result .= $_ eq 'url' ? '</a>' : $_ eq 'spoiler' ? '</b>' : '';
- $result .= $_ eq 'quote' ? '</div>' : $_ eq 'code' ? '</pre>' : '' if !$maxlength;
- }
- $result .= '...' if $maxlength && $length > $maxlength;
-
- return $result;
-}
-
-
-# GTIN code as argument,
-# Returns 'JAN', 'EAN', 'UPC' or undef,
-# Also 'normalizes' the first argument in place
-sub gtintype {
- $_[0] =~ s/[^\d]+//g;
- $_[0] =~ s/^0+//;
- my $c = shift;
- return undef if $c !~ /^[0-9]{12,13}$/; # only gtin-12 and 13
- $c = ('0'x(13-length $c)) . $c; # pad with zeros
-
- # calculate check digit according to
- # http://www.gs1.org/productssolutions/barcodes/support/check_digit_calculator.html#how
- my @n = reverse split //, $c;
- my $n = shift @n;
- $n += $n[$_] * ($_ % 2 != 0 ? 1 : 3) for (0..$#n);
- return undef if $n % 10 != 0;
-
- # Do some rough guesses based on:
- # http://www.gs1.org/productssolutions/barcodes/support/prefix_list.html
- # and http://en.wikipedia.org/wiki/List_of_GS1_country_codes
- local $_ = $c;
- return 'JAN' if /^4[59]/; # prefix code 450-459 & 490-499
- return 'UPC' if /^(?:0[01]|0[6-9]|13|75[45])/; # prefix code 000-019 & 060-139 & 754-755
- return undef if /(?:0[2-5]|2|97[789]|9[6-9])/; # some codes we don't want: 020–059 & 200-299 & 977-999
- return 'EAN'; # let's just call everything else EAN :)
-}
+use VNDBUtil;
+our @EXPORT = (@VNDBUtil::EXPORT, qw| liststat clearfloat cssicon tagscore mt minage |);
# Argument: hashref with rstat and vstat
diff --git a/lib/VNDBUtil.pm b/lib/VNDBUtil.pm
new file mode 100644
index 00000000..0b70db4d
--- /dev/null
+++ b/lib/VNDBUtil.pm
@@ -0,0 +1,202 @@
+# Misc. utility functions, do not rely on YAWF or POE and can be used from any script
+
+package VNDBUtil;
+
+use strict;
+use warnings;
+use Exporter 'import';
+use Unicode::Normalize 'NFKD';
+
+our @EXPORT = qw|shorten bb2html gtintype normalize normalize_titles normalize_query|;
+
+
+sub shorten {
+ my($str, $len) = @_;
+ return length($str) > $len ? substr($str, 0, $len-3).'...' : $str;
+}
+
+
+# Arguments: input, and optionally the maximum length
+# Parses:
+# [url=..] [/url]
+# [raw] .. [/raw]
+# [spoiler] .. [/spoiler]
+# [quote] .. [/quote]
+# [code] .. [/code]
+# v+, v+.+
+# http://../
+sub bb2html {
+ my $raw = shift;
+ my $maxlength = shift;
+ $raw =~ s/\r//g;
+ $raw =~ s/\n{5,}/\n\n/g;
+ return '' if !$raw && $raw ne "0";
+
+ my($result, $length, $rmnewline, @open) = ('', 0, 0, 'first');
+
+ my $e = sub {
+ local $_ = shift;
+ s/&/&amp;/g;
+ s/>/&gt;/g;
+ s/</&lt;/g;
+ s/\n/<br \/>/g if !$maxlength;
+ s/\n/ /g if $maxlength;
+ return $_;
+ };
+
+ for (split /(\s|\n|\[[^\]]+\])/, $raw) {
+ next if !defined $_;
+ next if $_ eq '';
+
+ # (note to self: stop using unreadable hacks like these!)
+ $rmnewline-- && $_ eq "\n" && next if $rmnewline;
+
+ my $lit = $_;
+ if($open[$#open] ne 'raw' && $open[$#open] ne 'code') {
+ if (lc$_ eq '[raw]') { push @open, 'raw'; next }
+ elsif (lc$_ eq '[spoiler]') { push @open, 'spoiler'; $result .= '<b class="spoiler">'; next }
+ elsif (lc$_ eq '[quote]') {
+ push @open, 'quote';
+ $result .= '<div class="quote">' if !$maxlength;
+ $rmnewline = 1;
+ next
+ } elsif (lc$_ eq '[code]') {
+ push @open, 'code';
+ $result .= '<pre>' if !$maxlength;
+ $rmnewline = 1;
+ next
+ } elsif (lc$_ eq '[/spoiler]') {
+ if($open[$#open] eq 'spoiler') {
+ $result .= '</b>';
+ pop @open;
+ }
+ next;
+ } elsif (lc$_ eq '[/quote]') {
+ if($open[$#open] eq 'quote') {
+ $result .= '</div>' if !$maxlength;
+ $rmnewline = 1;
+ pop @open;
+ }
+ next;
+ } elsif(lc$_ eq '[/url]') {
+ if($open[$#open] eq 'url') {
+ $result .= '</a>';
+ pop @open;
+ }
+ next;
+ } elsif(s{\[url=((https?://|/)[^\]>]+)\]}{<a href="$1" rel="nofollow">}i) {
+ $result .= $_;
+ push @open, 'url';
+ next;
+ } elsif(!grep(/url/, @open) &&
+ s{(.*)(http|https)://(.+[\d\w=/-])(.*)}
+ {$e->($1).qq|<a href="$2://|.$e->($3, 1).'" rel="nofollow">'.$e->('link').'</a>'.$e->($4)}e) {
+ $length += 4;
+ last if $maxlength && $length > $maxlength;
+ $result .= $_;
+ next;
+ } elsif(!grep(/url/, @open) && (
+ s{^(.*[^\w]|)([tdvpr][1-9][0-9]*)\.([1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2.$3">$2.$3</a>|.$e->($4)}e ||
+ s{^(.*[^\w]|)([tdvprug][1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2">$2</a>|.$e->($3)}e)) {
+ $length += length $lit;
+ last if $maxlength && $length > $maxlength;
+ $result .= $_;
+ next;
+ }
+ } elsif($open[$#open] eq 'raw' && lc$_ eq '[/raw]') {
+ pop @open;
+ next;
+ } elsif($open[$#open] eq 'code' && lc$_ eq '[/code]') {
+ $result .= '</pre>' if !$maxlength;
+ pop @open;
+ next;
+ }
+
+ # normal text processing
+ $length += length $_;
+ last if $maxlength && $length > $maxlength;
+ $result .= $e->($_);
+ }
+
+ # close open tags
+ while((local $_ = pop @open) ne 'first') {
+ $result .= $_ eq 'url' ? '</a>' : $_ eq 'spoiler' ? '</b>' : '';
+ $result .= $_ eq 'quote' ? '</div>' : $_ eq 'code' ? '</pre>' : '' if !$maxlength;
+ }
+ $result .= '...' if $maxlength && $length > $maxlength;
+
+ return $result;
+}
+
+
+# GTIN code as argument,
+# Returns 'JAN', 'EAN', 'UPC' or undef,
+# Also 'normalizes' the first argument in place
+sub gtintype {
+ $_[0] =~ s/[^\d]+//g;
+ $_[0] =~ s/^0+//;
+ my $c = shift;
+ return undef if $c !~ /^[0-9]{12,13}$/; # only gtin-12 and 13
+ $c = ('0'x(13-length $c)) . $c; # pad with zeros
+
+ # calculate check digit according to
+ # http://www.gs1.org/productssolutions/barcodes/support/check_digit_calculator.html#how
+ my @n = reverse split //, $c;
+ my $n = shift @n;
+ $n += $n[$_] * ($_ % 2 != 0 ? 1 : 3) for (0..$#n);
+ return undef if $n % 10 != 0;
+
+ # Do some rough guesses based on:
+ # http://www.gs1.org/productssolutions/barcodes/support/prefix_list.html
+ # and http://en.wikipedia.org/wiki/List_of_GS1_country_codes
+ local $_ = $c;
+ return 'JAN' if /^4[59]/; # prefix code 450-459 & 490-499
+ return 'UPC' if /^(?:0[01]|0[6-9]|13|75[45])/; # prefix code 000-019 & 060-139 & 754-755
+ return undef if /(?:0[2-5]|2|97[789]|9[6-9])/; # some codes we don't want: 020–059 & 200-299 & 977-999
+ return 'EAN'; # let's just call everything else EAN :)
+}
+
+
+# a rather aggressive normalization
+sub normalize {
+ local $_ = lc shift;
+ # remove combining markings. assuming the string is in NFD or NFKD,
+ # this effectively removes all accents from the characters (e.g. é -> e)
+ s/\pM//g;
+ # remove some characters that have no significance when searching
+ use utf8;
+ tr/\r\n\t ,_\-.~:[]()%+!?&#$"'`♥★☆♪†「」『』【】・”//d;
+ tr/@/a/;
+ # remove commonly used release titles ("x Edition" and "x Version")
+ # this saves some space and speeds up the search
+ s/(?:
+ first|firstpress|firstpresslimited|limited|regular|standard
+ |package|boxed|download|complete|popular
+ |lowprice|best|cheap|budget
+ |special|trial|allages|fullvoice
+ |cd|cdr|cdrom|dvdrom|dvd|dvdpack|dvdpg|windows
+ |初回限定|初回|限定|通常|廉価|パッケージ|ダウンロード
+ )(?:edition|version|版|生産)//xg;
+ no utf8;
+ return $_;
+}
+
+
+# normalizes each title and returns a concatenated string of unique titles
+sub normalize_titles {
+ my %t = map +(normalize(NFKD($_)), 1), @_;
+ return join ' ', grep $_, keys %t;
+}
+
+
+sub normalize_query {
+ my $q = NFKD shift;
+ # remove spaces within quotes, so that it's considered as one search word
+ $q =~ s/"([^"]+)"/(my $s=$1)=~y{ }{}d;$s/ge;
+ # split into search words, normalize, and remove too short words
+ return map length($_)>(/^[\x01-\x7F]+$/?2:0) ? quotemeta($_) : (), map normalize($_), split / /, $q;
+}
+
+
+1;
+
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 9a9eace0..fd1f0a73 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -74,6 +74,9 @@ CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers
CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_announce();
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
diff --git a/util/sql/func.sql b/util/sql/func.sql
index f9f0e490..7b8b9302 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -586,6 +586,52 @@ $$ LANGUAGE plpgsql;
+-- Check for updates to vn.c_search
+-- 1. NOTIFY is sent when vn.c_search goes from non-NULL to NULL
+-- vn.c_search is set to NULL when:
+-- 2. UPDATE on VN with the hidden field going from TRUE to FALSE
+-- 3. VN add/edit of which the title/original/alias fields differ from previous revision
+-- 4. Release gets hidden or unhidden
+-- 5. Release add/edit of which the title/original/vn fields differ from the previous revision
+CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS $$
+BEGIN
+ IF TG_TABLE_NAME = 'vn' THEN
+ -- 1.
+ IF NEW.c_search IS NULL AND NOT NEW.hidden THEN
+ NOTIFY vnsearch;
+ -- 2.
+ ELSIF NEW.hidden IS DISTINCT FROM OLD.hidden THEN
+ UPDATE vn SET c_search = NULL WHERE id = NEW.id;
+ -- 3.
+ ELSIF NEW.latest IS DISTINCT FROM OLD.latest THEN
+ IF EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2
+ WHERE v1.id = OLD.latest AND v2.id = NEW.latest
+ AND (v1.title IS DISTINCT FROM v2.title OR v1.original IS DISTINCT FROM v2.original OR v1.alias IS DISTINCT FROM v2.alias)
+ ) THEN
+ UPDATE vn SET c_search = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ ELSIF TG_TABLE_NAME = 'releases' THEN
+ -- 4. & 5.
+ IF NEW.hidden IS DISTINCT FROM OLD.hidden OR (
+ NEW.latest IS DISTINCT FROM OLD.latest AND (
+ EXISTS(
+ SELECT 1 FROM releases_rev r1, releases_rev r2
+ WHERE r1.id = OLD.latest AND r2.id = NEW.latest
+ AND (r1.title IS DISTINCT FROM r2.title OR r1.original IS DISTINCT FROM r2.original)
+ )
+ OR EXISTS(SELECT vid FROM releases_vn WHERE rid = OLD.latest EXCEPT SELECT vid FROM releases_vn WHERE rid = NEW.latest)
+ OR (SELECT COUNT(*) FROM releases_vn WHERE rid = OLD.latest) <> (SELECT COUNT(*) FROM releases_vn WHERE rid = NEW.latest)
+ )) THEN
+ UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest);
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
----------------------------------------------------------
diff --git a/util/updates/update_2.12.sql b/util/updates/update_2.12.sql
new file mode 100644
index 00000000..4d3dc4a4
--- /dev/null
+++ b/util/updates/update_2.12.sql
@@ -0,0 +1,9 @@
+
+-- cache for search
+ALTER TABLE vn ADD COLUMN c_search text;
+
+\i util/sql/func.sql
+
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-16 04:43:27 +0000