diff options
Diffstat (limited to 'util')
152 files changed, 3676 insertions, 567 deletions
diff --git a/util/README.md b/util/README.md new file mode 100644 index 00000000..2c4d499e --- /dev/null +++ b/util/README.md @@ -0,0 +1,65 @@ +# VNDB utility scripts + +(Only interesting scripts are documented here) + +dbdump.pl +: Can generate various database dumps, refer to its help text for details. + +devdump.pl +: Generates a tarball containing a [small subset of the + database](https://vndb.org/d8#3) for development purposes. + +hibp-dl.pl +: Utility to fetch the [Pwned + Passwords](https://haveibeenpwned.com/Passwords) database and store it in + `$VNDB_VAR/hibp`. The web backend can use this to warn about compromised + passwords. + +multi.pl +: Runs the background service for the old API and various maintenance tasks. + The actual code for the service lives in */lib/Multi/*. + +unusedimages.pl +: Purges unreferenced images from the database and scans `$VNDB_VAR/static/` + for files to be deleted. + +vndb.pl +: This is the main entry point of the web backend. This script does some + setup and loads all the code from */lib/VNWeb/*. Can be started from CGI or + FastCGI context. When run on the command line it will spawn a simple + single-threaded web server on port 3000. + +vndb-dev-server.pl +: A handy wrapper around *vndb.pl* for development use. Spawns a web server + on port 3000 that will automatically run `make` and reload the backend code + on changes. + + +## imgproc.c + +*imgproc.c* this is a tool that wraps [libvips](https://www.libvips.org/) image +processing operations used by VNDB in a simple CLI. It can be built in two ways: + +The default *imgproc* links against your system-provided libvips and should be +portable across various systems. + +`make gen/imgproc-custom` builds and links against a custom build of libvips +with support for better JPEG compression through jpegli. It also enables fairly +restrictive seccomp rules for secure sandboxing, to protect against potential +vulnerabilities in the used image codecs. This version likely only works on +x86\_64 Linux with glibc. To use this custom version, update `imgproc_path` in +your conf.pl. + +Build requirements for *imgproc-custom*: + +- C & C++ build system +- Linux x86\_64 with glibc +- meson +- cmake +- glib +- lcms +- libexpat +- libheif (with libaom for AVIF support) +- libpng +- libseccomp +- libwebp diff --git a/util/dbdump.pl b/util/dbdump.pl index 51503b44..fef8c5da 100755 --- a/util/dbdump.pl +++ b/util/dbdump.pl @@ -6,12 +6,18 @@ util/dbdump.pl export-db output.tar.zst Write a full database export as a .tar.zst - The uncompressed directory is written to "output.tar.zst_dir" - util/dbdump.pl export-img output-dir Create or update a directory with hardlinks to images. +util/dbdump.pl export-data data.sql + + Create an SQL script that is usable as replacement for 'sql/all.sql'. + (Similar to the dump created by devdump.pl, except this one includes *all* data) + + This allows recreating the full database using the definitions in sql/*. + The script does not rely on column order, so can be used to re-order table columns. + util/dbdump.pl export-votes output.gz util/dbdump.pl export-tags output.gz util/dbdump.pl export-traits output.gz @@ -28,6 +34,7 @@ use DBI; use DBD::Pg; use File::Copy 'cp'; use File::Find 'find'; +use File::Path 'rmtree'; use Time::HiRes 'time'; use Cwd 'abs_path'; @@ -36,6 +43,38 @@ BEGIN { ($ROOT = abs_path $0) =~ s{/util/dbdump\.pl$}{}; } use lib "$ROOT/lib"; use VNDB::Schema; +use VNDB::ExtLinks; + +$ENV{VNDB_VAR} //= 'var'; + +# Ridiculous query to export 'ulist_vns' with private labels removed. +# Since doing a lookup in ulist_labels for each row+label in ulist_vns is +# rather slow, this query takes a shortcut: for users that do not have any +# private labels at all (i.e. the common case), this query just dumps the rows +# without any modification. Only for users that have at least one private label +# are the labels filtered. +my $sql_ulist_vns_cols = q{ + uid, vid, date_trunc('day',added) AS added, date_trunc('day',lastmod) AS lastmod + , date_trunc('day',vote_date), started, finished, vote, notes +}; +my $sql_ulist_vns = qq{ + SELECT * FROM ( + SELECT $sql_ulist_vns_cols, array_agg(lblid ORDER BY lblid) AS labels + FROM ulist_vns, unnest(labels) x(lblid) + WHERE NOT c_private + AND NOT EXISTS(SELECT 1 FROM ulist_labels WHERE uid = ulist_vns.uid AND id = lblid AND private) + AND uid IN(SELECT uid FROM ulist_labels WHERE private) + GROUP BY uid, vid + UNION ALL + SELECT $sql_ulist_vns_cols, labels + FROM ulist_vns + WHERE NOT c_private + AND uid NOT IN(SELECT uid FROM ulist_labels WHERE private) + ) z + WHERE vid IN(SELECT id FROM vn WHERE NOT hidden) + ORDER BY uid, vid +}; + # Tables and columns to export. @@ -48,59 +87,61 @@ use VNDB::Schema; # interesting references are excluded from the dumps. Keeping all references # consistent with those omissions complicates the WHERE clauses somewhat. my %tables = ( - anime => { where => 'id IN(SELECT va.aid FROM vn_anime va JOIN vn v ON v.id = va.id WHERE NOT v.hidden)' }, - chars => { where => 'NOT hidden' }, - chars_traits => { where => 'id IN(SELECT id FROM chars WHERE NOT hidden) AND tid IN(SELECT id FROM traits WHERE state = 2)' }, - chars_vns => { where => 'id IN(SELECT id FROM chars WHERE NOT hidden)' - .' AND vid IN(SELECT id FROM vn WHERE NOT hidden)' - .' AND (rid IS NULL OR rid IN(SELECT id FROM releases WHERE NOT hidden))' - , order => 'id, vid, rid' }, - docs => { where => 'NOT hidden' }, - images => { where => "c_weight > 0" }, # Only images with a positive weight are referenced. - image_votes => { where => "id IN(SELECT id FROM images WHERE c_weight > 0)", order => 'uid, id' }, - producers => { where => 'NOT hidden' }, - producers_relations => { where => 'id IN(SELECT id FROM producers WHERE NOT hidden)' }, - releases => { where => 'NOT hidden' }, - releases_lang => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' }, - releases_media => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' }, - releases_platforms => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' }, - releases_producers => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden) AND pid IN(SELECT id FROM producers WHERE NOT hidden)' }, - releases_vn => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, + anime => { where => 'x.id IN(SELECT va.aid FROM vn_anime va JOIN vn v ON v.id = va.id WHERE NOT v.hidden)' }, + chars => { where => 'NOT x.hidden' }, + chars_traits => { where => 'x.id IN(SELECT id FROM chars WHERE NOT hidden) AND tid IN(SELECT id FROM traits WHERE NOT hidden)' }, + chars_vns => { where => 'x.id IN(SELECT id FROM chars WHERE NOT hidden)' + .' AND x.vid IN(SELECT id FROM vn WHERE NOT hidden)' + .' AND (x.rid IS NULL OR x.rid IN(SELECT id FROM releases WHERE NOT hidden))' + , order => 'x.id, x.vid, x.rid' }, + docs => { where => 'NOT x.hidden' }, + images => { where => "x.c_weight > 0" }, # Only images with a positive weight are referenced. + image_votes => { where => "x.id IN(SELECT id FROM images WHERE c_weight > 0)", order => 'x.uid, x.id' }, + producers => { where => 'NOT x.hidden' }, + producers_relations => { where => 'x.id IN(SELECT id FROM producers WHERE NOT hidden)' }, + quotes => { where => 'x.rand IS NOT NULL' }, + releases => { where => 'NOT x.hidden' }, + releases_media => { where => 'x.id IN(SELECT id FROM releases WHERE NOT hidden)' }, + releases_platforms => { where => 'x.id IN(SELECT id FROM releases WHERE NOT hidden)' }, + releases_producers => { where => 'x.id IN(SELECT id FROM releases WHERE NOT hidden) AND pid IN(SELECT id FROM producers WHERE NOT hidden)' }, + releases_titles => { where => 'x.id IN(SELECT id FROM releases WHERE NOT hidden)' }, + releases_vn => { where => 'x.id IN(SELECT id FROM releases WHERE NOT hidden) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, rlists => { where => 'EXISTS(SELECT 1 FROM releases r' .' JOIN releases_vn rv ON rv.id = r.id' .' JOIN vn v ON v.id = rv.vid' - .' JOIN ulist_vns_labels uvl ON uvl.vid = rv.vid' - .' JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl' - .' WHERE r.id = rlists.rid AND uvl.uid = rlists.uid AND NOT r.hidden AND NOT v.hidden AND NOT ul.private)' }, - staff => { where => 'NOT hidden' }, - staff_alias => { where => 'id IN(SELECT id FROM staff WHERE NOT hidden)' }, - tags => { where => 'state = 2' }, - tags_aliases => { where => 'tag IN(SELECT id FROM tags WHERE state = 2)' }, - tags_parents => { where => 'tag IN(SELECT id FROM tags WHERE state = 2)' }, - tags_vn => { where => 'tag IN(SELECT id FROM tags WHERE state = 2) AND vid IN(SELECT id FROM vn WHERE NOT hidden)', order => 'tag, vid, uid, date' }, - traits => { where => 'state = 2' }, - traits_parents => { where => 'trait IN(SELECT id FROM traits WHERE state = 2)' }, - ulist_labels => { where => 'NOT private AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.lbl = id AND ulist_labels.uid = uvl.uid)' }, - ulist_vns => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)' - .' AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl' - .' JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl' - .' WHERE ulist_vns.uid = uvl.uid AND ulist_vns.vid = uvl.vid AND NOT ul.private)' }, - ulist_vns_labels => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)' - .' AND EXISTS(SELECT 1 FROM ulist_labels ul WHERE ul.uid = ulist_vns_labels.uid AND id = lbl AND NOT ul.private)' }, - users => { where => 'id IN(SELECT DISTINCT uvl.uid FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE NOT ul.private)' - .' OR id IN(SELECT DISTINCT uid FROM tags_vn)' - .' OR id IN(SELECT DISTINCT uid FROM image_votes)' }, - vn => { where => 'NOT hidden' }, - vn_anime => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, - vn_relations => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, - vn_screenshots => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, - vn_seiyuu => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' - .' AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' - .' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' }, - vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' }, - wikidata => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden - UNION SELECT l_wikidata FROM staff WHERE NOT hidden - UNION SELECT l_wikidata FROM vn WHERE NOT hidden)} }, + .' JOIN ulist_vns uv ON uv.vid = rv.vid' + .' WHERE r.id = x.rid AND uv.uid = x.uid AND NOT r.hidden AND NOT v.hidden AND NOT uv.c_private)' }, + staff => { where => 'NOT x.hidden' }, + staff_alias => { where => 'x.id IN(SELECT id FROM staff WHERE NOT hidden)' }, + tags => { where => 'NOT x.hidden' }, + tags_parents => { where => 'x.id IN(SELECT id FROM tags WHERE NOT hidden)' }, + tags_vn => { where => 'x.tag IN(SELECT id FROM tags WHERE NOT hidden) AND x.vid IN(SELECT id FROM vn WHERE NOT hidden)', order => 'x.tag, x.vid, x.uid, x.date' }, + traits => { where => 'NOT x.hidden' }, + traits_parents => { where => 'x.id IN(SELECT id FROM traits WHERE NOT hidden)' }, + ulist_labels => { where => 'NOT x.private AND EXISTS(SELECT 1 FROM ulist_vns uv JOIN vn v ON v.id = uv.vid + WHERE NOT v.hidden AND uv.labels && ARRAY[x.id] AND x.uid = uv.uid)' }, + ulist_vns => { sql => $sql_ulist_vns }, + users => { where => 'x.username IS NOT NULL AND (' + .' x.id IN(SELECT DISTINCT uid FROM ulist_vns WHERE NOT c_private)' + .' OR x.id IN(SELECT DISTINCT uid FROM tags_vn)' + .' OR x.id IN(SELECT DISTINCT uid FROM image_votes)' + .' OR x.id IN(SELECT DISTINCT uid FROM vn_length_votes WHERE NOT private))' }, + vn => { where => 'NOT x.hidden' }, + vn_anime => { where => 'x.id IN(SELECT id FROM vn WHERE NOT hidden)' }, + vn_editions => { where => 'x.id IN(SELECT id FROM vn WHERE NOT hidden)' }, + vn_relations => { where => 'x.id IN(SELECT id FROM vn WHERE NOT hidden)' }, + vn_screenshots => { where => 'x.id IN(SELECT id FROM vn WHERE NOT hidden)' }, + vn_seiyuu => { where => 'x.id IN(SELECT id FROM vn WHERE NOT hidden)' + .' AND x.aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' + .' AND x.cid IN(SELECT id FROM chars WHERE NOT hidden)' }, + vn_staff => { where => 'x.id IN(SELECT id FROM vn WHERE NOT hidden) AND x.aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' + , order => 'x.id, x.eid, x.aid, x.role' }, + vn_titles => { where => 'x.id IN(SELECT id FROM vn WHERE NOT hidden)' }, + vn_length_votes => { where => 'x.vid IN(SELECT id FROM vn WHERE NOT hidden) AND NOT x.private' + , order => 'x.vid, x.uid' }, + wikidata => { where => q{x.id IN(SELECT l_wikidata FROM producers WHERE NOT hidden + UNION SELECT l_wikidata FROM staff WHERE NOT hidden + UNION SELECT l_wikidata FROM vn WHERE NOT hidden)} }, ); my @tables = map +{ name => $_, %{$tables{$_}} }, sort keys %tables; @@ -110,7 +151,29 @@ my $references = VNDB::Schema::references; my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1, AutoCommit => 0 }); $db->do('SET TIME ZONE +0'); -$db->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'); + + +sub consistent_snapshot { + my($func) = @_; + my($standby) = $db->selectrow_array('SELECT pg_is_in_recovery()'); + if($standby) { + $db->do('SELECT pg_wal_replay_pause()'); + } else { + $db->rollback; + $db->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'); + } + eval { $func->() }; + warn $@ if length $@; + $db->do('SELECT pg_wal_replay_resume()') if $standby; +} + + +sub table_order { + my $s = $schema->{$_[0]}; + my $c = $tables{$_[0]}; + my $o = $s->{primary} ? join ', ', map "x.$_", $s->{primary}->@* : $c ? $c->{order} : ''; + $o ? "ORDER BY $o" : ''; +} sub export_timestamp { @@ -129,14 +192,32 @@ sub export_table { my $fn = "$dest/$table->{name}"; - # Truncate all timestamptz columns to a day, to avoid leaking privacy-sensitive info. - my $cols = join ', ', map $_->{type} eq 'timestamptz' ? "date_trunc('day', \"$_->{name}\")" : qq{"$_->{name}"}, @cols; - my $where = $table->{where} ? "WHERE $table->{where}" : ''; - my $order = $schema->{primary} ? join ', ', map "\"$_\"", @{$schema->{primary}} : $table->{order}; - die "Table '$table->{name}' is missing an ORDER BY clause\n" if !$order; + my $sql = $table->{sql} // do { + my %isuid = + map +($_->{from_cols}[0], 1), + grep $_->{to_table} eq 'users' && $_->{to_cols}[0] eq 'id' && $_->{from_table} eq $table->{name}, @$references; + my $join = ''; + + my $cols = join ', ', map { + # For uid columns, check against the users table and export NULL for deleted accounts + $isuid{$_->{name}} ? do { + my $t = "u_$_->{name}"; + $join .= " LEFT JOIN users $t ON $t.id = x.$_->{name}"; + "CASE WHEN $t.username IS NULL THEN NULL ELSE $t.id END" + } + # Truncate all timestamptz columns to a day, to avoid leaking privacy-sensitive info. + : $_->{type} eq 'timestamptz' ? "date_trunc('day', x.$_->{name})" + : qq{x.$_->{name}} + } @cols; + + my $where = $table->{where} ? "WHERE $table->{where}" : ''; + my $order = table_order $table->{name}; + die "Table '$table->{name}' is missing an ORDER BY clause\n" if !$order; + qq{SELECT $cols FROM $table->{name} x $join $where $order} + }; my $start = time; - $db->do(qq{COPY (SELECT $cols FROM "$table->{name}" $where ORDER BY $order) TO STDOUT}); + $db->do(qq{COPY ($sql) TO STDOUT}); open my $F, '>:utf8', $fn; my $v; print $F $v while($db->pg_getcopydata($v) >= 0); @@ -182,10 +263,11 @@ sub export_import_script { for my $table (@tables) { my $schema = $schema->{$table->{name}}; + my @primary = grep { my $n=$_; !!grep $_->{name} eq $n && $_->{pub}, $schema->{cols}->@* } ($schema->{primary}||[])->@*; print $F "\n"; - print $F "CREATE TABLE \"$table->{name}\" (\n"; - print $F join ",\n", map " $_->{decl}" =~ s/" serial/" integer/ir =~ s/ +(?:check|constraint) +.*//ir, grep $_->{pub}, @{$schema->{cols}}; - print $F ",\n PRIMARY KEY(".join(', ', map "\"$_\"", @{$schema->{primary}}).")" if $schema->{primary}; + print $F "CREATE TABLE $table->{name} (\n"; + print $F join ",\n", map " $_->{decl}" =~ s/ serial/ integer/ir =~ s/ +(?:check|constraint|default) +.*//ir, grep $_->{pub}, @{$schema->{cols}}; + print $F ",\n PRIMARY KEY(".join(', ', map "$_", @primary).")" if @primary; print $F "\n);\n"; } @@ -201,6 +283,19 @@ sub export_import_script { next if grep !$pub{$_}, @{$ref->{from_cols}}; print $F "$ref->{decl}\n"; } + + print $F "\n\n"; + print $F "-- Sparse documentation, but it's something!\n"; + my $L = \%VNDB::ExtLinks::LINKS; + for my $table (@tables) { + my $schema = $schema->{$table->{name}}; + print $F "COMMENT ON TABLE $table->{name} IS ".$db->quote($schema->{comment}).";\n" if $schema->{comment}; + my $l = ($schema->{dbentry_type} && $L->{$schema->{dbentry_type}}) || {}; + for (grep $_->{pub}, $schema->{cols}->@*) { + $_->{comment} = "$l->{$_->{name}}{label}, $l->{$_->{name}}{fmt} $_->{comment}" if $l->{$_->{name}} && $l->{$_->{name}}{fmt}; + print $F "COMMENT ON COLUMN $table->{name}.$_->{name} IS ".$db->quote($_->{comment}).";\n" if $_->{comment}; + } + } } @@ -215,7 +310,7 @@ sub export_db { README.txt }; - # This will die if it already exists, which is good because we want to write to a new empty dir. + rmtree "${dest}_dir"; mkdir "${dest}_dir"; mkdir "${dest}_dir/db"; @@ -226,7 +321,8 @@ sub export_db { export_import_script "${dest}_dir/import.sql"; #print "# Compressing\n"; - `tar -cf "$dest" -I 'zstd -7' --sort=name -C "${dest}_dir" @static import.sql TIMESTAMP db` + `tar -cf "$dest" -I 'zstd -7' --sort=name -C "${dest}_dir" @static import.sql TIMESTAMP db`; + rmtree "${dest}_dir"; } @@ -242,55 +338,86 @@ sub cp_p { sub export_img { my $dest = shift; - { - no autodie; - mkdir ${dest}; - mkdir sprintf '%s/%s', $dest, $_ for qw/ch cv sf st/; - mkdir sprintf '%s/%s/%02d', $dest, $_->[0], $_->[1] for map +([ch=>$_], [cv=>$_], [sf=>$_], [st=>$_]), 0..99; - } + no autodie; + mkdir ${dest}; + mkdir sprintf '%s/%s', $dest, $_ for qw/ch cv sf sf.t/; + mkdir sprintf '%s/%s/%02d', $dest, $_->[0], $_->[1] for map +([ch=>$_], [cv=>$_], [sf=>$_], ['sf.t'=>$_]), 0..99; cp_p "$ROOT/util/dump/LICENSE-ODBL.txt", "$dest/LICENSE-ODBL.txt"; cp_p "$ROOT/util/dump/README-img.txt", "$dest/README.txt"; export_timestamp "$dest/TIMESTAMP"; my %scr; - my %dir = (ch => {}, cv => {}, sf => \%scr, st => \%scr); - $dir{sf}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(scr) FROM vn_screenshots WHERE $tables{vn_screenshots}{where}"); - $dir{cv}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(image) FROM vn WHERE image IS NOT NULL AND $tables{vn}{where}"); - $dir{ch}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(image) FROM chars WHERE image IS NOT NULL AND $tables{chars}{where}"); + my %dir = (ch => {}, cv => {}, sf => \%scr, 'sf.t' => \%scr); + $dir{sf}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(scr) FROM vn_screenshots x WHERE $tables{vn_screenshots}{where}"); + $dir{cv}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(image) FROM vn x WHERE image IS NOT NULL AND $tables{vn}{where}"); + $dir{ch}{$_->[0]} = 1 for $db->selectall_array("SELECT vndbid_num(image) FROM chars x WHERE image IS NOT NULL AND $tables{chars}{where}"); $db->rollback; undef $db; find { no_chdir => 1, wanted => sub { - unlink $File::Find::name if $File::Find::name =~ m{(cv|ch|sf|st)/[0-9][0-9]/([0-9]+)\.jpg$} && !$dir{$1}{$2}; + unlink $File::Find::name or warn "Unable to unlink $File::Find::name: $!\n" + if $File::Find::name =~ m{(cv|ch|sf|sf\.t)/[0-9][0-9]/([0-9]+)\.jpg$} && !$dir{$1}{$2}; } }, $dest; for my $d (keys %dir) { for my $i (keys %{$dir{$d}}) { my $f = sprintf('%s/%02d/%d.jpg', $d, $i % 100, $i); - link "$ROOT/static/$f", "$dest/$f" if !-e "$dest/$f"; + link "$ENV{VNDB_VAR}/static/$f", "$dest/$f" or warn "Unable to link $f: $!\n" if !-e "$dest/$f"; } } } +sub export_data { + my $dest = shift; + my $F = *STDOUT; + open $F, '>', $dest if $dest ne '-'; + binmode($F, ":utf8"); + select $F; + print "\\set ON_ERROR_STOP 1\n"; + print "\\i sql/util.sql\n"; + print "\\i sql/schema.sql\n"; + # Would be nice if VNDB::Schema could list sequences, too. + my @seq = sort @{ $db->selectcol_arrayref( + "SELECT oid::regclass::text FROM pg_class WHERE relkind = 'S' AND relnamespace = 'public'::regnamespace" + ) }; + printf "SELECT setval('%s', %d);\n", $_, $db->selectrow_array("SELECT last_value FROM \"$_\"", {}) for @seq; + for my $t (sort { $a->{name} cmp $b->{name} } values %$schema) { + my $cols = join ',', map $_->{name}, grep $_->{decl} !~ /\sGENERATED\s/, $t->{cols}->@*; + my $order = table_order $t->{name}; + print "\nCOPY $t->{name} ($cols) FROM STDIN;\n"; + $db->do("COPY (SELECT $cols FROM $t->{name} x $order) TO STDOUT"); + my $v; + print $v while($db->pg_getcopydata($v) >= 0); + print "\\.\n"; + } + print "\\i sql/func.sql\n"; + print "\\i sql/editfunc.sql\n"; + print "\\i sql/tableattrs.sql\n"; + print "\\i sql/triggers.sql\n"; + print "\\set ON_ERROR_STOP 0\n"; + print "\\i sql/perms.sql\n"; +} + + sub export_votes { my $dest = shift; require PerlIO::gzip; open my $F, '>:gzip:utf8', $dest; $db->do(q{COPY ( - SELECT uv.vid||' '||uv.uid||' '||uv.vote||' '||to_char(uv.vote_date, 'YYYY-MM-DD') + SELECT vndbid_num(uv.vid)||' '||vndbid_num(uv.uid)||' '||uv.vote||' '||to_char(uv.vote_date, 'YYYY-MM-DD') FROM ulist_vns uv JOIN users u ON u.id = uv.uid JOIN vn v ON v.id = uv.vid WHERE NOT v.hidden AND NOT u.ign_votes AND uv.vote IS NOT NULL - AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = uvl.uid WHERE uv.uid = uvl.uid AND uv.vid = uvl.vid AND NOT ul.private) + AND NOT uv.c_private ORDER BY uv.vid, uv.uid ) TO STDOUT }); @@ -305,10 +432,9 @@ sub export_tags { require PerlIO::gzip; my $lst = $db->selectall_arrayref(q{ - SELECT id, name, description, searchable, applicable, c_items AS vns, cat, - (SELECT string_agg(alias,'$$$-$$$') FROM tags_aliases where tag = id) AS aliases, - (SELECT string_agg(parent::text, ',') FROM tags_parents WHERE tag = id) AS parents - FROM tags WHERE state = 2 ORDER BY id + SELECT vndbid_num(id) AS id, name, description, searchable, applicable, c_items AS vns, cat, alias, + (SELECT string_agg(vndbid_num(parent)::text, ',' ORDER BY main desc, parent) FROM tags_parents tp WHERE tp.id = t.id) AS parents + FROM tags t WHERE NOT hidden ORDER BY id }, { Slice => {} }); for(@$lst) { $_->{id} *= 1; @@ -316,7 +442,7 @@ sub export_tags { $_->{searchable} = $_->{searchable} ? JSON::XS::true() : JSON::XS::false(); $_->{applicable} = $_->{applicable} ? JSON::XS::true() : JSON::XS::false(); $_->{vns} *= 1; - $_->{aliases} = [ split /\$\$\$-\$\$\$/, ($_->{aliases}||'') ]; + $_->{aliases} = [ split /\n/, delete $_->{alias} ]; $_->{parents} = [ map $_*1, split /,/, ($_->{parents}||'') ]; } @@ -331,9 +457,9 @@ sub export_traits { require PerlIO::gzip; my $lst = $db->selectall_arrayref(q{ - SELECT id, name, alias AS aliases, description, searchable, applicable, c_items AS chars, - (SELECT string_agg(parent::text, ',') FROM traits_parents WHERE trait = id) AS parents - FROM traits WHERE state = 2 ORDER BY id + SELECT vndbid_num(id) AS id, name, alias AS aliases, description, searchable, applicable, c_items AS chars, + (SELECT string_agg(vndbid_num(parent)::text, ',' ORDER BY main desc, parent) FROM traits_parents tp WHERE tp.id = t.id) AS parents + FROM traits t WHERE NOT hidden ORDER BY id }, { Slice => {} }); for(@$lst) { $_->{id} *= 1; @@ -351,9 +477,11 @@ sub export_traits { if($ARGV[0] && $ARGV[0] eq 'export-db' && $ARGV[1]) { - export_db $ARGV[1]; + consistent_snapshot sub { export_db $ARGV[1] }; } elsif($ARGV[0] && $ARGV[0] eq 'export-img' && $ARGV[1]) { export_img $ARGV[1]; +} elsif($ARGV[0] && $ARGV[0] eq 'export-data' && $ARGV[1]) { + export_data $ARGV[1]; } elsif($ARGV[0] && $ARGV[0] eq 'export-votes' && $ARGV[1]) { export_votes $ARGV[1]; } elsif($ARGV[0] && $ARGV[0] eq 'export-tags' && $ARGV[1]) { diff --git a/util/devdump.pl b/util/devdump.pl index 73c79566..e0f0f80f 100755 --- a/util/devdump.pl +++ b/util/devdump.pl @@ -17,35 +17,46 @@ use lib $ROOT.'/lib'; my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1 }); +sub ids { join ',', map "'$_'", @{$_[0]} } +sub idq { ids $db->selectcol_arrayref($_[0]) } + +chdir($ENV{VNDB_VAR}//'var'); # Figure out which DB entries to export -my @vids = (3, 17, 97, 183, 264, 266, 384, 407, 1910, 2932, 5922, 6438, 9837); -my $vids = join ',', @vids; -my $staff = $db->selectcol_arrayref( +my $large = ($ARGV[0]||'') eq 'large'; + +my $vids = $large ? 'SELECT id FROM vn' : ids [qw/v3 v17 v97 v183 v264 v266 v384 v407 v1910 v2932 v5922 v6438 v9837/]; +my $staff = $large ? 'SELECT id FROM staff' : idq( "SELECT c2.itemid FROM vn_staff_hist v JOIN changes c ON c.id = v.chid JOIN staff_alias_hist a ON a.aid = v.aid JOIN changes c2 ON c2.id = a.chid WHERE c.itemid IN($vids) " ."UNION " ."SELECT c2.itemid FROM vn_seiyuu_hist v JOIN changes c ON c.id = v.chid JOIN staff_alias_hist a ON a.aid = v.aid JOIN changes c2 ON c2.id = a.chid WHERE c.itemid IN($vids)" ); -my $releases = $db->selectcol_arrayref("SELECT DISTINCT c.itemid FROM releases_vn_hist v JOIN changes c ON c.id = v.chid WHERE v.vid IN($vids)"); -my $producers = $db->selectcol_arrayref("SELECT pid FROM releases_producers_hist p JOIN changes c ON c.id = p.chid WHERE c.type = 'r' AND c.itemid IN(".join(',',@$releases).")"); -my $characters = $db->selectcol_arrayref( +my $releases = $large ? 'SELECT id FROM releases' : idq( + "SELECT DISTINCT c.itemid FROM releases_vn_hist v JOIN changes c ON c.id = v.chid WHERE v.vid IN($vids)" +); +my $producers = $large ? 'SELECT id FROM producers' : idq( + "SELECT pid FROM releases_producers_hist p JOIN changes c ON c.id = p.chid WHERE c.itemid IN($releases)" +); +my $characters = $large ? 'SELECT id FROM chars' : idq( "SELECT DISTINCT c.itemid FROM chars_vns_hist e JOIN changes c ON c.id = e.chid WHERE e.vid IN($vids) " ."UNION " ."SELECT DISTINCT h.main FROM chars_vns_hist e JOIN changes c ON c.id = e.chid JOIN chars_hist h ON h.chid = e.chid WHERE e.vid IN($vids) AND h.main IS NOT NULL" ); -my $images = $db->selectcol_arrayref(q{ - SELECT image FROM chars_hist ch JOIN changes c ON c.id = ch.chid WHERE c.type = 'c' AND c.itemid IN(}.join(',',@$characters).qq{) AND ch.image IS NOT NULL - UNION SELECT image FROM vn_hist vh JOIN changes c ON c.id = vh.chid WHERE c.type = 'v' AND c.itemid IN($vids) AND vh.image IS NOT NULL - UNION SELECT scr FROM vn_screenshots_hist vs JOIN changes c ON c.id = vs.chid WHERE c.type = 'v' AND c.itemid IN($vids) -}); +my $imageids = !$large && $db->selectcol_arrayref(" + SELECT image FROM chars_hist ch JOIN changes c ON c.id = ch.chid WHERE c.itemid IN($characters) AND ch.image IS NOT NULL + UNION SELECT image FROM vn_hist vh JOIN changes c ON c.id = vh.chid WHERE c.itemid IN($vids) AND vh.image IS NOT NULL + UNION SELECT scr FROM vn_screenshots_hist vs JOIN changes c ON c.id = vs.chid WHERE c.itemid IN($vids) +"); +my $images = $large ? 'SELECT id FROM images' : ids($imageids); # Helper function to copy a table or SQL statement. Can do modifications on a # few columns (the $specials). sub copy { my($dest, $sql, $specials) = @_; + warn "$dest...\n"; $sql ||= "SELECT * FROM $dest"; $specials ||= {}; @@ -56,15 +67,11 @@ sub copy { grep !($specials->{$_} && $specials->{$_} eq 'del'), @{$s->{NAME}} }; - printf "COPY %s (%s) FROM stdin;\n", $dest, join ', ', map "\"$_\"", @cols; + printf "COPY %s (%s) FROM stdin;\n", $dest, join ', ', @cols; $sql = "SELECT " . join(',', map { my $s = $specials->{$_} || ''; - if($s eq 'user') { - qq{"$_" % 10 AS "$_"} - } else { - qq{"$_"} - } + $s eq 'user' ? "CASE WHEN vndbid_num($_) % 10 = 0 THEN NULL ELSE vndbid('u', vndbid_num($_) % 10) END AS $_" : $_; } @cols) . " FROM ($sql) AS x"; #warn $sql; $db->do("COPY ($sql) TO STDOUT"); @@ -77,14 +84,13 @@ sub copy { # Helper function to copy a full DB entry with history and all (doesn't handle references) sub copy_entry { - my($type, $tables, $ids) = @_; - $ids = join ',', @$ids; - copy changes => "SELECT * FROM changes WHERE type = '$type' AND itemid IN($ids)", {requester => 'user', ip => 'del'}; + my($tables, $ids) = @_; + copy changes => "SELECT * FROM changes WHERE itemid IN($ids)", {requester => 'user', ip => 'del'}; 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 "${_}_hist" => "SELECT x.* FROM ${_}_hist x JOIN changes c ON c.id = x.chid WHERE c.type = '$type' AND c.itemid IN($ids) $add"; + copy "${_}_hist" => "SELECT x.* FROM ${_}_hist x JOIN changes c ON c.id = x.chid WHERE c.itemid IN($ids) $add"; } } @@ -95,6 +101,7 @@ sub copy_entry { print "-- This file replaces 'sql/all.sql'.\n"; print "\\set ON_ERROR_STOP 1\n"; + print "\\i sql/util.sql\n"; print "\\i sql/schema.sql\n"; print "\\i sql/data.sql\n"; print "\\i sql/func.sql\n"; @@ -109,65 +116,66 @@ sub copy_entry { # A few pre-defined users # This password is 'hunter2' with the default salt my $pass = '000100000801ec4185fed438752d6b3b968e2b2cd045f70005cb7e10cafdbb694a82246bd34a065b6e977e0c3dcc'; - printf "INSERT INTO users (id, username, mail, perm_usermod, passwd, email_confirmed) VALUES (%d, '%s', '%s', %s, decode('%s', 'hex'), true);\n", @$_, $pass for( - [ 2, 'admin', 'admin@vndb.org', 'true' ], - [ 3, 'user1', 'user1@vndb.org', 'false'], - [ 4, 'user2', 'user2@vndb.org', 'false'], - [ 5, 'user3', 'user3@vndb.org', 'false'], - [ 6, 'user4', 'user4@vndb.org', 'false'], - [ 7, 'user5', 'user5@vndb.org', 'false'], - [ 8, 'user6', 'user6@vndb.org', 'false'], - [ 9, 'user7', 'user7@vndb.org', 'false'], - ); + for( + [ 'u2', 'admin', 'admin@vndb.org', 'true', 'true'], + [ 'u3', 'mod', 'mod@vndb.org', 'false', 'true'], + [ 'u4', 'user1', 'user1@vndb.org', 'false', 'false'], + [ 'u5', 'user2', 'user2@vndb.org', 'false', 'false'], + [ 'u6', 'user3', 'user3@vndb.org', 'false', 'false'], + [ 'u7', 'user4', 'user4@vndb.org', 'false', 'false'], + [ 'u8', 'user5', 'user5@vndb.org', 'false', 'false'], + [ 'u9', 'user6', 'user6@vndb.org', 'false', 'false'], + ) { + printf "INSERT INTO users (id, username, email_confirmed, perm_dbmod, perm_tagmod) VALUES ('%s', '%s', true, '%s', '%s');\n", @{$_}[0,1,4,4]; + printf "INSERT INTO users_shadow (id, mail, perm_usermod, passwd) VALUES ('%s', '%s', %s, decode('%s', 'hex'));\n", @{$_}[0,2,3], $pass; + printf "INSERT INTO users_prefs (id) VALUES ('%s');\n", $_->[0]; + } print "SELECT ulist_labels_create(id) FROM users;\n"; # Tags & traits - copy tags => undef, {addedby => 'user'}; - copy 'tags_aliases'; - copy 'tags_parents'; - copy traits => undef, {addedby => 'user'}; - copy 'traits_parents'; + copy_entry [qw/tags tags_parents/], 'SELECT id FROM tags'; + copy_entry [qw/traits traits_parents/], 'SELECT id FROM traits'; # Wikidata (TODO: This could be a lot more selective) copy 'wikidata'; # Image metadata - my $image_ids = join ',', map "'$_'", @$images; - copy images => "SELECT * FROM images WHERE id IN($image_ids)"; - copy image_votes => "SELECT DISTINCT ON (id,uid%10) * FROM image_votes WHERE id IN($image_ids)", { uid => 'user' }; + copy images => "SELECT * FROM images WHERE id IN($images)", { uploader => 'user' }; + copy image_votes => "SELECT DISTINCT ON (id,vndbid('u', vndbid_num(uid)%10+10)) * FROM image_votes WHERE id IN($images)", { uid => 'user' }; # Threads (announcements) - my $threads = join ',', map "'$_'", @{ $db->selectcol_arrayref("SELECT tid FROM threads_boards b WHERE b.type = 'an'") }; + my $threads = idq("SELECT tid FROM threads_boards b WHERE b.type = 'an'"); copy threads => "SELECT * FROM threads WHERE id IN($threads)"; copy threads_boards => "SELECT * FROM threads_boards WHERE tid IN($threads)"; copy threads_posts => "SELECT * FROM threads_posts WHERE tid IN($threads)", { uid => 'user' }; # Doc pages - copy_entry d => ['docs'], $db->selectcol_arrayref('SELECT id FROM docs'); + copy_entry ['docs'], 'SELECT id FROM docs'; # Staff - copy_entry s => [qw/staff staff_alias/], $staff; + copy_entry [qw/staff staff_alias/], $staff; # Producers (TODO: Relations) - copy_entry p => [qw/producers/], $producers; + copy_entry [qw/producers/], $producers; # Characters - copy_entry c => [qw/chars chars_traits chars_vns/], $characters; + copy_entry [qw/chars chars_traits chars_vns/], $characters; # Visual novels - copy anime => "SELECT DISTINCT a.* FROM anime a JOIN vn_anime_hist v ON v.aid = a.id JOIN changes c ON c.id = v.chid WHERE c.type = 'v' AND c.itemid IN($vids)"; - copy_entry v => [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@vids; + copy anime => "SELECT DISTINCT a.* FROM anime a JOIN vn_anime_hist v ON v.aid = a.id JOIN changes c ON c.id = v.chid WHERE c.itemid IN($vids)"; + copy_entry [qw/vn vn_anime vn_editions vn_seiyuu vn_staff vn_relations vn_screenshots vn_titles/], $vids; # VN-related niceties - copy tags_vn => "SELECT DISTINCT ON (tag,vid,uid%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'}; - copy quotes => "SELECT * FROM quotes WHERE vid IN($vids)"; - my $votes = "SELECT vid, uid%8+2 AS uid, (percentile_cont((uid%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(vote_date) AS vote_date" - ." FROM ulist_vns WHERE vid IN($vids) AND vote IS NOT NULL GROUP BY vid, uid%8"; - copy ulist_vns => $votes, {uid => 'user'}; - copy ulist_vns_labels => "SELECT vid, uid, 7 AS lbl FROM ($votes) x", {uid => 'user'}; + copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE NOT private AND vid IN($vids)", {uid => 'user'}; + copy tags_vn => "SELECT DISTINCT ON (tag,vid,vndbid_num(uid)%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'}; + copy quotes => "SELECT * FROM quotes WHERE rand IS NOT NULL AND vid IN($vids)", {addedby => 'user'}; + copy ulist_vns => "SELECT vid, vndbid('u', vndbid_num(uid)%8+2) AS uid, MIN(vote_date) AS vote_date, '{7}' AS labels, false AS c_private + , (percentile_cont((vndbid_num(uid)%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote + FROM ulist_vns WHERE vid IN($vids) AND vote IS NOT NULL GROUP BY vid, vndbid_num(uid)%8", {uid => 'user'}; # Releases - copy_entry r => [qw/releases releases_lang releases_media releases_platforms releases_producers releases_vn/], $releases; + copy 'drm'; + copy_entry [qw/releases releases_drm releases_media releases_platforms releases_producers releases_titles releases_vn/], $releases; print "\\i sql/tableattrs.sql\n"; print "\\i sql/triggers.sql\n"; @@ -175,16 +183,18 @@ sub copy_entry { # Update some caches print "SELECT tag_vn_calc(NULL);\n"; print "SELECT traits_chars_calc(NULL);\n"; - print "SELECT update_vncache(id) FROM vn;\n"; + print "SELECT count(*) FROM (SELECT update_vncache(id) FROM vn) x;\n"; print "SELECT update_stats_cache_full();\n"; print "SELECT update_vnvotestats();\n"; print "SELECT update_users_ulist_stats(NULL);\n"; print "SELECT update_images_cache(NULL);\n"; + print "SELECT count(*) FROM (SELECT update_search(id) FROM $_) x;\n" for (qw/chars producers vn releases staff tags traits/); print "UPDATE users u SET c_tags = (SELECT COUNT(*) FROM tags_vn v WHERE v.uid = u.id);\n"; print "UPDATE users u SET c_changes = (SELECT COUNT(*) FROM changes c WHERE c.requester = u.id);\n"; print "\\set ON_ERROR_STOP 0\n"; print "\\i sql/perms.sql\n"; + print "VACUUM ANALYZE;\n"; select STDOUT; close $OUT; @@ -192,10 +202,11 @@ sub copy_entry { - # Now figure out which images we need, and throw everything in a tarball -sub img { sprintf 'static/%s/%02d/%d.jpg', $_[0], $_[1]%100, $_[1] } -my @imgpaths = sort map { my($t,$id) = /([a-z]+)([0-9]+)/; (img($t, $id), $t eq 'sf' ? img('st', $id) : ()) } @$images; +if(!$large) { + sub img { sprintf 'static/%s/%02d/%d.jpg', $_[0], $_[1]%100, $_[1] } + my @imgpaths = sort map { my($t,$id) = /([a-z]+)([0-9]+)/; (img($t, $id), $t eq 'sf' ? img('sf.t', $id) : ()) } @$imageids; -system("tar -czf devdump.tar.gz dump.sql ".join ' ', @imgpaths); -unlink 'dump.sql'; + system("tar -czf devdump.tar.gz dump.sql ".join ' ', @imgpaths); + unlink 'dump.sql'; +} diff --git a/util/dl-cron.sh b/util/dl-cron.sh new file mode 100755 index 00000000..8149ccbd --- /dev/null +++ b/util/dl-cron.sh @@ -0,0 +1,44 @@ +#!/bin/sh + +[ -z "$VNDB_VAR" ] && VNDB_VAR=var + +mkdir -p "$VNDB_VAR/dl/dump" "$VNDB_VAR/dl/img" "$VNDB_VAR/tmp" + +# Keep only the last (non-symlink) files matching the given pattern, delete the rest. +cleanup() { + ( + cd "$VNDB_VAR/dl/dump" + for f in $(find . -type f -name "$1" | sort | head -n -1); do + rm "$f" + done + ) + util/dl-gendir.pl +} + + +dumpfile() { + FN=$1 + LATEST=$2 + CMD=$3 + test -f "$VNDB_VAR/dl/dump/$FN" && echo "$FN already exists" && return + util/dbdump.pl $CMD "$VNDB_VAR/tmp/$FN" + mv "$VNDB_VAR/tmp/$FN" "$VNDB_VAR/dl/dump/$FN" + ln -sf "$FN" "$VNDB_VAR/dl/dump/$LATEST" + util/dl-gendir.pl +} + +cleanup "vndb-dev-*.tar.gz" + +cleanup "vndb-votes-*.gz" +dumpfile "vndb-votes-`date +%F`.gz" "vndb-votes-latest.gz" export-votes + +cleanup "vndb-tags-*.json.gz" +dumpfile "vndb-tags-`date +%F`.json.gz" "vndb-tags-latest.json.gz" export-tags + +cleanup "vndb-traits-*.json.gz" +dumpfile "vndb-traits-`date +%F`.json.gz" "vndb-traits-latest.json.gz" export-traits + +cleanup "vndb-db-*.tar.zst" +dumpfile "vndb-db-`date +%F`.tar.zst" "vndb-db-latest.tar.zst" export-db + +util/dbdump.pl export-img "$VNDB_VAR/dl/img" diff --git a/util/dl-gendir.pl b/util/dl-gendir.pl new file mode 100755 index 00000000..3ca9e1f3 --- /dev/null +++ b/util/dl-gendir.pl @@ -0,0 +1,51 @@ +#!/usr/bin/perl + +use strict; +use warnings; +use autodie; +use POSIX 'strftime'; + +chdir(($ENV{VNDB_VAR}//'var').'/dl/dump'); + +my @pub = (glob('vndb-db-*'), glob('vndb-dev-*'), glob('vndb-votes-*'), glob('vndb-tags-*'), glob('vndb-traits-*')); + +open my $F, '>', 'index.html~'; +print $F q{<!DOCTYPE html> +<html> + <head> + <title>VNDB Database Downloads</title> + <style type="text/css"> + th { text-align: left } + td, th { padding: 1px 5px } + td:nth-child(3), th:nth-child(3) { text-align: right } + </style> + </head> + <body> + <h1>VNDB Database Downloads</h1> + <p>Refer to the <a href="https://vndb.org/d14">Database Dumps</a> page on VNDB.org for more information about these files.</p> + <h2>Latest versions</h2> + <table> + <thead> + <thead><tr><th>Name</th><th>Destination</th></tr></thead> + <tbody> +}; + +printf $F q{<tr><td><a href="%s">%s</a></td><td>%s</td></tr>}, + $_, $_, readlink + for (grep -l, @pub); + +print $F q{ + </tbody> + </table> + <h2>Files</h2> + <table> + <thead><tr><th>Name</th><th>Last modified</th><th>Size</th></tr></thead> + <tbody> +}; +printf $F q{<tr><td><a href="%s">%s</a></td><td>%s</td><td>%d</td></tr>}, + $_, $_, strftime('%F %T', gmtime((stat)[9])), -s + for (grep !-l, @pub); + +print $F q{</tbody></table></body>}; +close $F; +rename 'index.html~', 'index.html'; diff --git a/util/docker-init.sh b/util/docker-init.sh index 42ff99a9..e3ae25b8 100755 --- a/util/docker-init.sh +++ b/util/docker-init.sh @@ -1,6 +1,6 @@ #!/bin/sh -VER=`test -f /var/www/Dockerfile && grep VNDB_DOCKER_VERSION= /var/www/Dockerfile | sed -E s/^.+=//` +VER=`test -f /vndb/Dockerfile && grep VNDB_DOCKER_VERSION= /vndb/Dockerfile | sed -E s/^.+=//` if [ -z "$VER" -o -z "$VNDB_DOCKER_VERSION" -o "$VER" != "$VNDB_DOCKER_VERSION" ]; then echo "The Docker image version ($VNDB_DOCKER_VERSION) does not match the version in the currently checked out source code ($VER)." @@ -24,8 +24,8 @@ mkdevuser() { # If the owner is root, we're probably running under Docker for Mac or # similar and don't need to match UID/GID. See https://vndb.org/t9959 #38 # to #44. - USER_UID=`stat -c '%u' /var/www` - USER_GID=`stat -c '%g' /var/www` + USER_UID=`stat -c '%u' /vndb` + USER_GID=`stat -c '%g' /vndb` if test $USER_UID -eq 0; then addgroup devgroup adduser -s /bin/sh devuser @@ -39,20 +39,25 @@ mkdevuser() { # Should run as root installvndbid() { - make -C /var/www/sql/c install || exit + mkdir -p /tmp/vndbid + cp /vndb/sql/c/vndbfuncs.c /vndb/sql/c/Makefile /tmp/vndbid + make -C /tmp/vndbid install || exit } # Should run as devuser pg_start() { - if [ ! -d /var/www/data/docker-pg/12 ]; then - mkdir -p /var/www/data/docker-pg/12 - initdb -D /var/www/data/docker-pg/12 --locale en_US.UTF-8 -A trust + cd /vndb + make -j4 + util/setup-var.sh + + if [ ! -d docker/pg15 ]; then + mkdir -p docker/pg15 + initdb -D docker/pg15 --locale en_US.UTF-8 -A trust fi - pg_ctl -D /var/www/data/docker-pg/12 -l /var/www/data/docker-pg/12/logfile start + pg_ctl -D /vndb/docker/pg15 -l /vndb/docker/pg15/logfile start - cd /var/www - if test -f data/docker-pg/vndb-init-done; then + if test -f docker/pg15/vndb-init-done; then echo echo "Database initialization already done." echo @@ -65,13 +70,11 @@ pg_start() { echo "If you want to have some data to play around with," echo "I can download and install a development database for you." echo "For information, see https://vndb.org/d8#3" - echo "(Warning: This will also write images to static/)" echo echo "Enter n to setup an empty database, y to download the dev database." [ -f dump.sql ] && echo " Or e to import the existing dump.sql." read -p "Choice: " opt - make sql/editfunc.sql psql postgres -f sql/superuser_init.sql psql -U devuser vndb -f sql/vndbid.sql echo "ALTER ROLE vndb LOGIN" | psql postgres @@ -83,14 +86,14 @@ pg_start() { psql -U vndb -f dump.sql elif [ $opt = y ] then - curl -L https://dl.vndb.org/dump/vndb-dev-latest.tar.gz | tar -xzf- - psql -U vndb -f dump.sql - rm dump.sql + curl -sL https://dl.vndb.org/dump/vndb-dev-latest.tar.gz | tar -C docker/var -xzf- + psql -U vndb -f docker/var/dump.sql + rm docker/var/dump.sql else psql -U vndb -f sql/all.sql fi - touch data/docker-pg/vndb-init-done + touch docker/pg15/vndb-init-done echo echo "Database initialization done!" @@ -100,7 +103,7 @@ pg_start() { # Should run as devuser devshell() { - cd /var/www + cd /vndb util/vndb-dev-server.pl sh } @@ -110,8 +113,8 @@ case "$1" in '') mkdevuser installvndbid - su devuser -c '/var/www/util/docker-init.sh pg_start' - exec su devuser -c '/var/www/util/docker-init.sh devshell' + su devuser -c '/vndb/util/docker-init.sh pg_start' + exec su devuser -c '/vndb/util/docker-init.sh devshell' ;; pg_start) pg_start diff --git a/util/hibp-dl.pl b/util/hibp-dl.pl new file mode 100755 index 00000000..c3abd8c0 --- /dev/null +++ b/util/hibp-dl.pl @@ -0,0 +1,89 @@ +#!/usr/bin/perl + +# This script downloads a full copy of the Have I Been Pwned SHA1 database +# using their range API. +# -> https://haveibeenpwned.com/API/v3#PwnedPasswords +# +# Output database format: +# var/hibp/#### -> file for hashes prefixed with those two bytes +# +# Each file is an ordered concatenation of raw hashes, excluding the first +# two bytes (part of the filename) and the last 8 bytes (truncated hashes), +# so each hash is represented with 10 bytes. +# +# This means we actually store 96bit truncated SHA1 hashes, which should +# still provide a very low probability of collision. A bloom filter may have +# a lower collision probability for the same amount of space, but is also +# more complex and expensive to manage. + +use v5.28; +use warnings; +use AE; +use AnyEvent::HTTP; +use Cwd 'abs_path'; + +my $API = 'https://api.pwnedpasswords.com/range/'; +my $concurrency = 5; +my $lastnum = 0; +my $run = AE::cv; + +my $ROOT = abs_path($0) =~ s{/util/hibp-dl\.pl$}{}r; + +$ENV{VNDB_VAR} //= 'var'; + +mkdir "$ENV{VNDB_VAR}/hibp"; +chdir "$ENV{VNDB_VAR}/hibp" or die $!; + + +$AnyEvent::HTTP::MAX_PER_HOST = $concurrency; + +sub save { + my($file, $count, $data) = @_; + { + open my $OUT, '>', "$file~" or die $!; + print $OUT $data; + } + rename "$file~", $file or die $!; + say sprintf '%s -> %d hashes, %.0f KiB', $file, $count, length($data)/1024; +} + +sub fetch_one { + my($file, $count, $data, $midnum) = @_; + + my $mid = sprintf '%X', $midnum; + http_request GET => $API.$file.$mid, persistent => 1, sub { + my($body, $hdr) = @_; + if($hdr->{Status} =~ /^2/) { + for (split /\r?\n/, $body) { + # 40-5 -> 35 hex chars per hash; 16 of which we discard so 19 we grab. + warn "$file.$mid Unrecognized line: $_\n" if !/^([a-fA-F0-9]{19})[a-fA-F0-9]{16}:[0-9]+$/; + $count++; + $data .= pack 'H*', $mid.$1; + } + if($midnum == 15) { + save $file, $count, $data; + fetch_next(); + } else { + fetch_one($file, $count, $data, $midnum+1); + } + } else { + warn "$file.$mid: $hdr->{Status}\n"; + fetch_next(); + } + }; +} + +sub fetch_next { + my $file; + do { + my $filenum = $lastnum++; + return $run->end if $filenum > 65535; + $file = sprintf '%04X', $filenum; + } while(-s $file); + + fetch_one $file, 0, '', 0; +} + +$run->begin for (1..$concurrency); +fetch_next() for (1..$concurrency); +$run->recv; diff --git a/util/imgproc.c b/util/imgproc.c new file mode 100644 index 00000000..0e5e49d7 --- /dev/null +++ b/util/imgproc.c @@ -0,0 +1,245 @@ +/* + * USAGE: imgproc [commands] <input.png + * + * Commands: + * + * size - Output image dimensions to standard error. + * jpeg n - Write a jpeg to fd n. + * fit x y - Resize the image to fit within the given dimensions. Does not upscale. + * composite - For util/pngsprite.pl, must be first and only command. + Combine multiple input images and write a png to stdout. + */ +#include <stdio.h> +#include <stdlib.h> +#include <time.h> +#include <unistd.h> + +#ifndef DISABLE_SECCOMP +#include <seccomp.h> +#include <fcntl.h> +#include <locale.h> +#include <sys/mman.h> +#include <sys/prctl.h> +#include <sys/ioctl.h> +#include <malloc.h> +#endif + +#include <vips/vips.h> + +#define MAX_INPUT_SIZE (10*1024*1024) + +char input_buffer[MAX_INPUT_SIZE]; +size_t input_len; + + +#ifndef DISABLE_SECCOMP + +static void setup_seccomp() { + scmp_filter_ctx ctx = seccomp_init(SCMP_ACT_KILL_PROCESS); + if (ctx == NULL) goto err; + + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(exit_group), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(exit), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(brk), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(mmap), 2, + SCMP_A2_32(SCMP_CMP_EQ, PROT_READ|PROT_WRITE), + SCMP_A4_32(SCMP_CMP_EQ, -1) + )) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(mremap), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(munmap), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(madvise), 1, SCMP_A2_32(SCMP_CMP_EQ, MADV_DONTNEED))) goto err; + + /* (nearly) impossible to prevent glibc from trying to read /proc and /sys + * stuff, just block the attempts and have it use fallback code instead. */ + if (seccomp_rule_add(ctx, SCMP_ACT_ERRNO(ENOSYS), SCMP_SYS(openat), 0)) goto err; + + /* Threading, very fiddly :( + * These are likely specific to a particular glibc version on x86_64. + * I made an attempt to patch libvips to not use threads, but that turned out to be far more challenging. + */ + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(futex), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(clone3), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(rseq), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(set_robust_list), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(mmap), 3, + SCMP_A2_32(SCMP_CMP_EQ, PROT_NONE), + SCMP_A3_32(SCMP_CMP_MASKED_EQ, MAP_PRIVATE&MAP_ANONYMOUS, MAP_PRIVATE|MAP_ANONYMOUS), + SCMP_A4_32(SCMP_CMP_EQ, -1) + )) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(mprotect), 1, SCMP_A2_32(SCMP_CMP_EQ, PROT_READ|PROT_WRITE))) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(prctl), 1, SCMP_A0_32(SCMP_CMP_EQ, PR_SET_NAME))) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(sched_getaffinity), 0)) goto err; + + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(rt_sigaction), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(rt_sigprocmask), 0)) goto err; + + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(close), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(dup), 0)) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(read), 1, SCMP_A0(SCMP_CMP_EQ, 0))) goto err; + if (seccomp_rule_add(ctx, SCMP_ACT_ALLOW, SCMP_SYS(write), 0)) goto err; + + if (seccomp_load(ctx) < 0) goto err; + seccomp_release(ctx); + return; +err: + perror("setting up seccomp"); + exit(1); +} + +#endif + + +/* The default glib logging handler attempt to do charset conversion, color + * detection and other unnecessary crap that complicates parsing and sandboxing. */ +static void log_func(const gchar *log_domain, GLogLevelFlags log_level, const gchar *message, gpointer user_data) { + if (g_log_writer_default_would_drop(log_level, log_domain)) return; + fprintf(stderr, "[%s#%d] %s\n", log_domain, (int)log_level, message); +} + + +static int composite(void) { + if (input_len < 8) return 1; + + int offset = 0; +#define RDINT ({ offset += 4; *((int *)(input_buffer+offset-4)); }) + + int width = RDINT; + int height = RDINT; + /*fprintf(stderr, "Output of %dx%d\n", width, height);*/ + VipsImage *img; + vips_black(&img, width, height, "bands", 4, NULL); + + while (input_len - offset > 12) { + int x = RDINT; + int y = RDINT; + int bytes = RDINT; + /*fprintf(stderr, "Image at %dx%d of %d bytes\n", x, y, bytes);*/ + if (input_len - offset < bytes) return 1; + VipsImage *sub = vips_image_new_from_buffer(input_buffer+offset, bytes, "", NULL); + if (!img) vips_error_exit(NULL); + offset += bytes; + + VipsImage *tmp; + if (!vips_image_hasalpha(sub)) { + if (vips_addalpha(sub, &tmp, NULL)) vips_error_exit(NULL); + VIPS_UNREF(sub); + sub = tmp; + } + + if (vips_insert(img, sub, &tmp, x, y, NULL)) vips_error_exit(NULL); + VIPS_UNREF(img); + VIPS_UNREF(sub); + img = tmp; + } + + VipsTarget *target = vips_target_new_to_descriptor(1); + if (vips_pngsave_target(img, target, "strip", TRUE, NULL)) + vips_error_exit(NULL); + VIPS_UNREF(target); + return 0; +} + + +int main(int argc, char **argv) { +#ifndef DISABLE_SECCOMP + /* don't write to temporary files when working with large images, + unless we need more than 1g, then we'll just crash. */ + putenv("VIPS_DISC_THRESHOLD=1g"); + + /* error messages go through gettext(), prevent that from loading translation files */ + putenv("LANGUAGE=C"); + + /* Timezone initialization loads data from disk */ + putenv("TZ="); + tzset(); +#endif + + if (VIPS_INIT(argv[0])) vips_error_exit(NULL); + g_log_set_default_handler(log_func, NULL); + +#ifndef DISABLE_SECCOMP + /* vips error logging attempt to do charset stuff + (must be a UTF-8 locale otherwise it tries to load iconv modules, sigh) */ + setlocale(LC_ALL, "C.utf8"); + g_get_charset(NULL); + + setup_seccomp(); +#endif + + /* Reading into a buffer allows for more strict seccomp rules than using vips_source_new_from_descriptor() */ + int r = 0; + while ((r = read(0, input_buffer + input_len, MAX_INPUT_SIZE - input_len)) > 0) + input_len += r; + if (r < 0) { + perror("reading input"); + exit(1); + } + + if (argc == 2 && strcmp(argv[1], "composite") == 0) return composite(); + + VipsImage *img = vips_image_new_from_buffer(input_buffer, input_len, "", NULL); + if (!img) vips_error_exit(NULL); + + /* Remove alpha channel */ + VipsImage *tmp; + if (vips_image_hasalpha(img)) { + /* "white" is 256 for 8-bit images and 65536 for 16-bit, the latter works for both. + (where is this documented!?) */ + VipsArrayDouble *white = vips_array_double_newv(1, 65536.0); + if (vips_flatten(img, &tmp, "background", white, NULL)) vips_error_exit(NULL); + VIPS_UNREF(img); + img = tmp; + } + + /* This approach to processing CLI arguments is sloppy and unsafe, but the + * CLI is considered trusted input. */ + while (*++argv) { + if (strcmp(*argv, "size") == 0) + fprintf(stderr, "%dx%d\n", vips_image_get_width(img), vips_image_get_height(img)); + + else if (strcmp(*argv, "jpeg") == 0) { + int fd = atoi(*++argv); + + /* Always save as sRGB (suboptimal for greyscale images... do we have those?) */ + if (vips_colourspace(img, &tmp, VIPS_INTERPRETATION_sRGB, NULL)) + vips_error_exit(NULL); + + /* Ignore DPI values from the original image, enforce a consistent 72 DPI */ + vips_copy(tmp, &img, "xres", 2.83, "yres", 2.83, NULL); + VIPS_UNREF(tmp); + + VipsTarget *target = vips_target_new_to_descriptor(fd); + if (vips_jpegsave_target(img, target, "Q", 90, "optimize_coding", TRUE, "strip", TRUE, NULL)) + vips_error_exit(NULL); + VIPS_UNREF(target); + + } else if (strcmp(*argv, "fit") == 0) { + int width = atoi(*++argv); + int height = atoi(*++argv); + if (width >= vips_image_get_width(img) && height >= vips_image_get_height(img)) + continue; + + /* The "linear" option is supposedly quite slow (haven't benchmarked, seems + fast enough) but it offers a very significant quality boost. */ + if (vips_thumbnail_image(img, &tmp, width, "height", height, "linear", TRUE, NULL)) + vips_error_exit(NULL); + VIPS_UNREF(img); + img = tmp; + + /* The lanczos3 kernel used by vips_thumbnail tends to be overly blurry for small images. + Ideally we should use a sharper downscaler instead, but I couldn't find any in VIPS, + so just use a sharpen post-processing filter for now. */ + if (width * height < 400*400) { + if (vips_sharpen(img, &tmp, "m2", 2.0, NULL)) vips_error_exit(NULL); + VIPS_UNREF(img); + img = tmp; + } + + } else { + fprintf(stderr, "Unknown argument: %s\n", *argv); + return 1; + } + } + + return 0; +} diff --git a/util/jsgen.pl b/util/jsgen.pl index 84a5c8f1..87641a12 100755 --- a/util/jsgen.pl +++ b/util/jsgen.pl @@ -1,62 +1,70 @@ #!/usr/bin/perl -use strict; -use warnings; -use Encode 'encode_utf8'; use Cwd 'abs_path'; -use JSON::XS; - -my $ROOT; +our $ROOT; BEGIN { ($ROOT = abs_path $0) =~ s{/util/jsgen\.pl$}{}; } use lib "$ROOT/lib"; -use VNDB::Config; +use TUWF; +use TUWF::Validate::Interop; +use JSON::XS; +use VNWeb::Validation (); +use VNWeb::TimeZone; +use VNDB::ExtLinks (); +use VNDB::Skins; use VNDB::Types; +my $js = JSON::XS->new->pretty->canonical; -sub vars { - my %vars = ( - rlist_status => [ map [ $_, $RLIST_STATUS{$_} ], keys %RLIST_STATUS ], - cookie_prefix => config->{tuwf}{cookie_prefix}, - age_ratings => [ map [ $_, $AGE_RATING{$_}{txt}], keys %AGE_RATING ], - languages => [ map [ $_, $LANGUAGE{$_} ], sort { $LANGUAGE{$a} cmp $LANGUAGE{$b} } keys %LANGUAGE ], - platforms => [ map [ $_, $PLATFORM{$_} ], keys %PLATFORM ], - char_roles => [ map [ $_, $CHAR_ROLE{$_}{txt} ], keys %CHAR_ROLE ], - media => [ map [ $_, $MEDIUM{$_}{txt}, $MEDIUM{$_}{qty} ], keys %MEDIUM ], - release_types => [ map [ $_, $RELEASE_TYPE{$_} ], keys %RELEASE_TYPE ], - animated => [ map [ $_, $ANIMATED{$_}{txt} ], keys %ANIMATED ], - voiced => [ map [ $_, $VOICED{$_}{txt} ], keys %VOICED ], - vn_lengths => [ map [ $_, $VN_LENGTH{$_}{txt} ], keys %VN_LENGTH ], - blood_types => [ map [ $_, $BLOOD_TYPE{$_} ], keys %BLOOD_TYPE ], - genders => [ map [ $_, $GENDER{$_} ], keys %GENDER ], - credit_type => [ map [ $_, $CREDIT_TYPE{$_} ], keys %CREDIT_TYPE ], - cup_size => [ grep $_, keys %CUP_SIZE ], - ); - JSON::XS->new->encode(\%vars); +sub validations { + print 'window.formVals = '.$js->encode({ + map +($_, { tuwf->compile({ $_ => 1 })->analyze->html5_validation() }->{pattern}), + qw/ email weburl / + }).";\n"; } - -# Reads main.js and any included files. -sub readjs { - my $f = shift || 'main.js'; - open my $JS, '<:utf8', "$ROOT/data/js/$f" or die $!; - local $/ = undef; - local $_ = <$JS>; - close $JS; - s{^//include (.+)$}{'(function(){'.readjs($1).'})();'}meg; - $_; +sub types { + print 'window.vndbTypes = '.$js->encode({ + language => [ map [$_, $LANGUAGE{$_}{txt}, $LANGUAGE{$_}{latin}?\1:\0, $LANGUAGE{$_}{rank}], keys %LANGUAGE ], + platform => [ map [$_, $PLATFORM{$_} ], keys %PLATFORM ], + medium => [ map [$_, $MEDIUM{$_}{txt}, $MEDIUM{$_}{qty}?\1:\0 ], keys %MEDIUM ], + voiced => [ map [$VOICED{$_}{txt}], keys %VOICED ], + ageRating => [ map [1*$_, $AGE_RATING{$_}{txt}.($AGE_RATING{$_}{ex}?" ($AGE_RATING{$_}{ex})":'')], keys %AGE_RATING ], + releaseType => [ map [$_, $RELEASE_TYPE{$_}], keys %RELEASE_TYPE ], + drmProperty => [ map [$_, $DRM_PROPERTY{$_}], keys %DRM_PROPERTY ], + producerType => [ map [$_, $PRODUCER_TYPE{$_}], keys %PRODUCER_TYPE ], + producerRelation => [ map [$_, $PRODUCER_RELATION{$_}{txt}], keys %PRODUCER_RELATION ], + vnRelation => [ map [$_, $VN_RELATION{$_}{txt}, $VN_RELATION{$_}{reverse}, $VN_RELATION{$_}{pref}], keys %VN_RELATION ], + tagCategory => [ map [$_, $TAG_CATEGORY{$_}], keys %TAG_CATEGORY ], + }).";\n"; } +sub zones { + print 'window.timeZones = '.$js->encode(\@ZONES).";\n"; +} -sub save { - my($f, $body) = @_; - open my $F, '>', "$f~" or die $!; - print $F encode_utf8($body); - close $F; - rename "$f~", $f or die $!; +sub vskins { + print 'window.vndbSkins = '.$js->encode([ map [$_, skins->{$_}{name}], sort { skins->{$a}{name} cmp skins->{$b}{name} } keys skins->%*]).";\n"; } +sub extlinks { + sub t { + [ map +{ + id => $_->{id}, + name => $_->{name}, + fmt => $_->{fmt}, + default => $_->{default}, + int => $_->{int}, + regex => TUWF::Validate::Interop::_re_compat($_->{regex}), + patt => $_->{pattern}, + }, VNDB::ExtLinks::extlinks_sites($_[0]) ] + } + print 'window.extLinks = '.$js->encode({ + release => t('r'), + staff => t('s'), + }).";\n"; +} -my $js = readjs; -$js =~ s{/\*VARS\*/}{vars()}eg; -save "$ROOT/static/f/vndb.js", $js; +if ($ARGV[0] eq 'types') { validations; types; } +if ($ARGV[0] eq 'user') { zones; vskins; } +if ($ARGV[0] eq 'extlinks') { extlinks; } diff --git a/util/multi.pl b/util/multi.pl index 1ad92ef4..6dc3cf5c 100755 --- a/util/multi.pl +++ b/util/multi.pl @@ -10,4 +10,6 @@ BEGIN { ($ROOT = abs_path $0) =~ s{/util/multi\.pl$}{} } use lib $ROOT.'/lib'; use Multi::Core; -Multi::Core->run(); +my $quiet = grep '-q', @ARGV; + +Multi::Core::run $quiet; diff --git a/util/pngsprite.pl b/util/pngsprite.pl new file mode 100755 index 00000000..79fc2719 --- /dev/null +++ b/util/pngsprite.pl @@ -0,0 +1,122 @@ +#!/usr/bin/perl + +use v5.28; + +my $GEN = $ENV{VNDB_GEN} // 'gen'; + +my $icons = "$GEN/static/icons.png"; +my $ticons = "$GEN/static/icons~.png"; +my $css = "$GEN/png.css"; +my $imgproc = "$GEN/imgproc"; + +my @img = map { + local $/ = undef; + open my $F, '<', $_ or die $_; + my $data = <$F>; + # 8 byte PNG header, 4 byte IHDR chunk length, 4 bytes IHDR chunk identifier, 4 bytes width, 4 bytes height + my($w,$h) = unpack 'NN', substr $data, 16, 8; + { + f => /^icons\/(.+)\.png/ && $1, + w => $w, + h => $h, + d => $data, + } +} glob("icons/*.png"), glob("icons/*/*.png"); + + +@img = sort { $b->{h} <=> $a->{h} || $b->{w} <=> $a->{w} } @img; + + +# Simple strip packing algortihm, First-Fit Decreasing Height. +sub genstrip { + my $w = shift; + my @l; + my $h = 0; + for my $i (@img) { + my $found = 0; + # @img is assumed to be sorted by height, so image always fits + # (height-wise) in any of the previously created levels. + for my $l (@l) { + next if $l->{left} + $i->{w} > $w; + # Image fits, add to level + $i->{x} = $l->{left}; + $i->{y} = $l->{top}; + $l->{left} += $i->{w}; + $found = 1; + last; + } + next if $found; + + # No level found, create a new one + push @l, { top => $h, left => $i->{w} }; + $i->{x} = 0; + $i->{y} = $h; + $h += $i->{h}; + } + + # Recalculate the (actually used) width + $w = 0; + $w < $_->{x}+$_->{w} && ($w = $_->{x}+$_->{w}) for (@img); + ($w, $h); +} + + +# Tries to find the width of the strip for which the number of unused pixels is +# the minimum. Simple and dumb linear search; it's fast enough. +# +# Note that minimum number of unused pixels does not imply minimum file size, +# although there is some correlation. To further minimize the file size, it's +# possible to attempt to group similar-looking images close together so that +# the final png image might compress better. Finding a good (and fast) +# algorithm for this is not a trivial task, however. +sub minstrip { + my($minwidth, $maxwidth) = (0,0); + for(@img) { + $minwidth = $_->{w} if $_->{w} > $minwidth; + $maxwidth += $_->{w}; + } + + my($optsize, $w, $h, $optw, $opth) = (1e9, $maxwidth); + while($w >= $minwidth) { + ($w, $h) = genstrip($w); + my $size = $w*$h; + if($size < $optsize) { + $optw = $w; + $opth = $h; + $optsize = $size; + } + $w--; + } + genstrip($optw); +} + + +sub img { + my($w, $h) = @_; + open my $CMD, "|$imgproc composite >$ticons" or die $!; + print $CMD pack 'll', $w, $h; + print $CMD pack('lll', $_->{x}, $_->{y}, length $_->{d}).$_->{d} for @img; +} + + +sub css { + # The gender icons need special treatment, they're 3 icons in one image. + my $gender; + + open my $F, '>', $css or die $!; + for my $i (@img) { + if($i->{f} eq 'gender') { + $gender = $i; + next; + } + printf $F ".icon-%s { background-position: %dpx %dpx; width: %dpx; height: %dpx }\n", $i->{f} =~ s#/#-#rg, -$i->{x}, -$i->{y}, $i->{w}, $i->{h}; + } + printf $F ".icon-gen-f, .icon-gen-b { background-position: %dpx %dpx; width: 14px; height: 14px }\n", -$gender->{x}, -$gender->{y}; + print $F ".icon-gen-b { width: 28px }\n"; + printf $F ".icon-gen-m { background-position: %dpx %dpx; width: 14px; height: 14px }\n", -($gender->{x}+14), -$gender->{y}; +} + + +img minstrip; +css; +rename $ticons, $icons or die $!; diff --git a/util/revision-integrity.pl b/util/revision-integrity.pl index 3c8a552d..4bed133d 100755 --- a/util/revision-integrity.pl +++ b/util/revision-integrity.pl @@ -22,7 +22,7 @@ use VNDB::Schema; my $schema = VNDB::Schema::schema; for my $table (sort { $a->{name} cmp $b->{name} } values %$schema) { - next if $table->{name} !~ /^(.+)_hist$/; + next if $table->{name} !~ /^(.+)_hist$/ || $table->{name} eq 'users_username_hist'; my($main, $type) = ($1, $1); $type =~ s/_[^_]+$// while !$schema->{$type}{dbentry_type}; @@ -34,6 +34,6 @@ for my $table (sort { $a->{name} cmp $b->{name} } values %$schema) { EXCEPT SELECT '$main', c.itemid, $histlock $cols FROM $table->{name} e - JOIN changes c ON c.type = '$schema->{$type}{dbentry_type}' AND e.chid = c.id - WHERE NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev > c.rev);\n\n" + JOIN changes c ON e.chid = c.id + WHERE NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.itemid = c.itemid AND c2.rev > c.rev);\n\n" } diff --git a/util/setup-var.sh b/util/setup-var.sh new file mode 100755 index 00000000..f153237e --- /dev/null +++ b/util/setup-var.sh @@ -0,0 +1,21 @@ +#!/bin/sh + +[ -z "$VNDB_GEN" ] && VNDB_GEN=gen +[ -z "$VNDB_VAR" ] && VNDB_VAR=var + +mkdir -p "$VNDB_VAR/static" + +[ -e "$VNDB_VAR/conf.pl" ] || cp conf_example.pl "$VNDB_VAR/conf.pl" + +# Symlink for compatibility with old URLs +ln -sfT "$(realpath $VNDB_GEN/static)" "$VNDB_VAR/static/g" + +cd "$VNDB_VAR" +mkdir -p tmp log + +for d in ch ch.orig cv cv.orig sf sf.orig sf.t; do + for i in `seq -w 0 1 99`; do + mkdir -p static/$d/$i + done +done +ln -sfT sf.t static/st diff --git a/util/skingen.pl b/util/skingen.pl deleted file mode 100755 index 8d9f3b4e..00000000 --- a/util/skingen.pl +++ /dev/null @@ -1,100 +0,0 @@ -#!/usr/bin/perl - -use v5.12; -use warnings; -use Cwd 'abs_path'; - -our($ROOT, %S); -BEGIN { ($ROOT = abs_path $0) =~ s{/util/skingen\.pl$}{}; } - -use lib "$ROOT/lib"; -use SkinFile; - - -my $iconcss = do { - open my $F, '<', "$ROOT/data/icons/icons.css" or die $!; - local $/=undef; - <$F>; -}; - - -sub imgsize { - open my $IMG, '<', $_[0] or die $!; - sysread $IMG, my $buf, 1024 or die $!; - $buf =~ /\xFF\xC0...(....)/s ? unpack('nn', $1) : $buf =~ /IHDR(.{8})/s ? unpack('NN', $1) : die; -} - - -sub rdcolor { - length $_[0] == 4 ? map hex($_)/15, $_[0] =~ /#(.)(.)(.)/ : #RGB - length $_[0] == 7 ? map hex($_)/255, $_[0] =~ /#(..)(..)(..)/ : #RRGGBB - length $_[0] == 9 ? map hex($_)/255, $_[0] =~ /#(..)(..)(..)(..)/ : #RRGGBBAA - die; -} - - -sub blend { - my($f, $b) = @_; - my @f = rdcolor $f; - my @b = rdcolor $b; - $f[3] //= 1; - sprintf '#%02x%02x%02x', - ($f[0] * $f[3] + $b[0] * (1 - $f[3]))*255, - ($f[1] * $f[3] + $b[1] * (1 - $f[3]))*255, - ($f[2] * $f[3] + $b[2] * (1 - $f[3]))*255; -} - -sub mtime($) { [stat("$ROOT/static$_[0]")]->[9] } - - -sub writeskin { # $name - my $name = shift; - my $skin = SkinFile->new("$ROOT/static/s", $name); - my %o = map +($_ => $skin->get($_)), $skin->get; - $o{iconcss} = $iconcss; - - # get the right top image - if($o{imgrighttop}) { - my $path = "/s/$name/$o{imgrighttop}"; - my($h, $w) = imgsize "$ROOT/static$path"; - $o{_bgright} = sprintf 'background: url(%s?%s) no-repeat; width: %dpx; height: %dpx', $path, mtime $path, $w, $h; - } else { - $o{_bgright} = 'display: none'; - } - - # body background - if($o{imglefttop}) { - my $path = "/s/$name/$o{imglefttop}"; - $o{_bodybg} = sprintf 'background: %s url(%s?%s) no-repeat', $o{bodybg}, $path, mtime $path; - } else { - $o{_bodybg} = sprintf 'background-color: %s', $o{bodybg}; - } - - # boxbg blended with bodybg - $o{_blendbg} = blend $o{boxbg}, $o{bodybg}; - - # version - $o{icons_version} = mtime '/f/icons.png'; - - # write the CSS - open my $CSS, '<', "$ROOT/data/style.css" or die $!; - local $/=undef; - my $css = <$CSS>; - close $CSS; - - my $f = "$ROOT/static/s/$name/style.css"; - open my $SKIN, '>', "$f~" or die $!; - print $SKIN $css =~ s{\$([a-z_]+)\$}{$o{$1} // die "Unknown variable $1"}egr; - close $SKIN; - - rename "$f~", $f; -} - - -if(@ARGV) { - writeskin($_) for (@ARGV); -} else { - writeskin($_) for (SkinFile->new("$ROOT/static/s")->list); -} - - diff --git a/util/spritegen.pl b/util/spritegen.pl deleted file mode 100755 index 5b2b5982..00000000 --- a/util/spritegen.pl +++ /dev/null @@ -1,136 +0,0 @@ -#!/usr/bin/perl - -use strict; -use warnings; -use Image::Magick; -use Cwd 'abs_path'; - -our $ROOT; -BEGIN { ($ROOT = abs_path $0) =~ s{/util/spritegen\.pl$}{}; } - -my $path = "$ROOT/data/icons"; -my $icons = "$ROOT/static/f/icons.png"; -my $ticons = "$ROOT/static/f/icons~.png"; -my $css = "$ROOT/data/icons/icons.css"; - -my @img = map { - my $i = Image::Magick->new(); - $i->Read($_) and die $_; - { - f => /^\Q$path\E\/(.+)\.png/ && $1, - i => $i, - h => scalar $i->Get('height'), - w => scalar $i->Get('width') - } -} glob("$path/*.png"), glob("$path/*/*.png"); - - -@img = sort { $b->{h} <=> $a->{h} || $b->{w} <=> $a->{w} } @img; - -my $minpixels = 0; -$minpixels += $_->{w}*$_->{h} for @img; - - -# Simple strip packing algortihm, First-Fit Decreasing Height. -sub genstrip { - my $w = shift; - my @l; - my $h = 0; - for my $i (@img) { - my $found = 0; - # @img is assumed to be sorted by height, so image always fits - # (height-wise) in any of the previously created levels. - for my $l (@l) { - next if $l->{left} + $i->{w} > $w; - # Image fits, add to level - $i->{x} = $l->{left}; - $i->{y} = $l->{top}; - $l->{left} += $i->{w}; - $found = 1; - last; - } - next if $found; - - # No level found, create a new one - push @l, { top => $h, left => $i->{w} }; - $i->{x} = 0; - $i->{y} = $h; - $h += $i->{h}; - } - - # Recalculate the (actually used) width - $w = 0; - $w < $_->{x}+$_->{w} && ($w = $_->{x}+$_->{w}) for (@img); - ($w, $h); -} - - -# Tries to find the width of the strip for which the number of unused pixels is -# the minimum. Simple and dumb linear search; it's fast enough. -# -# Note that minimum number of unused pixels does not imply minimum file size, -# although there is some correlation. To further minimize the file size, it's -# possible to attempt to group similar-looking images close together so that -# the final png image might compress better. Finding a good (and fast) -# algorithm for this is not a trivial task, however. -sub minstrip { - my($minwidth, $maxwidth) = (0,0); - for(@img) { - $minwidth = $_->{w} if $_->{w} > $minwidth; - $maxwidth += $_->{w}; - } - - my($optsize, $w, $h, $optw, $opth) = (1e9, $maxwidth); - while($w >= $minwidth) { - ($w, $h) = genstrip($w); - my $size = $w*$h; - if($size < $optsize) { - $optw = $w; - $opth = $h; - $optsize = $size; - } - $w--; - } - genstrip($optw); -} - - -sub img { - my($w, $h) = @_; - my $img = Image::Magick->new; - print $img->Set(size => "${w}x$h"); - print $img->ReadImage('canvas:rgba(0,0,0,0)'); - my $pixels = $w*$h; - for my $i (@img) { - print $img->Composite(image => $i->{i}, x => $i->{x}, y => $i->{y}); - } - print $img->Write("png32:$ticons"); - undef $img; - - my $size = -s $ticons; - #printf "Dim: %dx%d, size: %d, pixels wasted: %d\n", $w, $h, $size, $w*$h-$minpixels; - $size; -} - - -sub css { - # The gender icons need special treatment, they're 3 icons in one image. - my $gender; - - open my $F, '>', $css or die $!; - for my $i (@img) { - if($i->{f} eq 'gender') { - $gender = $i; - next; - } - $i->{f} =~ /([^\/]+)$/; - printf $F ".icons.%s { background-position: %dpx %dpx }\n", $1, -$i->{x}, -$i->{y}; - } - printf $F ".icons.gen.f, .icons.gen.b { background-position: %dpx %dpx }\n", -$gender->{x}, -$gender->{y}; - printf $F ".icons.gen.m { background-position: %dpx %dpx }\n", -($gender->{x}+14), -$gender->{y}; -} - - -img minstrip; -css; -rename $ticons, $icons or die $!; diff --git a/util/sql b/util/sql deleted file mode 120000 index 44657b95..00000000 --- a/util/sql +++ /dev/null @@ -1 +0,0 @@ -../sql
\ No newline at end of file diff --git a/util/sqleditfunc.pl b/util/sqleditfunc.pl index 2af28b6a..59558822 100755 --- a/util/sqleditfunc.pl +++ b/util/sqleditfunc.pl @@ -1,9 +1,7 @@ #!/usr/bin/perl -use strict; -use warnings; +use v5.28; use List::Util 'any'; - use Cwd 'abs_path'; our $ROOT; BEGIN { ($ROOT = abs_path $0) =~ s{/util/sqleditfunc\.pl$}{}; } @@ -19,13 +17,13 @@ sub gensql { # table_name_without_hist => [ column_names_without_chid ] my %ts = map - +($_, [ map "\"$_->{name}\"", grep $_->{name} !~ /^chid$/, @{$schema->{"${_}_hist"}{cols}} ]), + +($_, [ map $_->{name}, grep $_->{name} !~ /^chid$/, @{$schema->{"${_}_hist"}{cols}} ]), map /^${item}_/ && /^(.+)_hist$/ ? $1 : (), keys %$schema; my %replace = ( item => $item, itemtype => $schema->{$item}{dbentry_type} ); $replace{createtemptables} = join "\n", map sprintf( - " CREATE TEMPORARY TABLE edit_%s (LIKE %s INCLUDING DEFAULTS INCLUDING CONSTRAINTS);\n". + " CREATE TEMPORARY TABLE edit_%s (LIKE %s INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING GENERATED);\n". " ALTER TABLE edit_%1\$s DROP COLUMN %s;", $_, $_ eq 'staff_alias' ? ($_, 'id') : ("${_}_hist", 'chid') # staff_alias copies from the non-_hist table, because it needs the sequence ), sort keys %ts; @@ -37,29 +35,28 @@ sub gensql { $_, join ', ', @{$ts{$_}}), sort keys %ts; $replace{copyfromtemp} = join "\n", map sprintf( - " DELETE FROM %1\$s WHERE id = r.itemid;\n". - " INSERT INTO %1\$s (id, %2\$s) SELECT r.itemid, %2\$s FROM edit_%1\$s;\n". - " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;", + " DELETE FROM %1\$s WHERE id = nitemid;\n". + " INSERT INTO %1\$s (id, %2\$s) SELECT nitemid, %2\$s FROM edit_%1\$s;\n". + " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT nchid, %2\$s FROM edit_%1\$s;", $_, join ', ', @{$ts{$_}}), grep $_ ne $item, sort keys %ts; $replace{copymainfromtemp} = sprintf - " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;\n". + " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT nchid, %2\$s FROM edit_%1\$s;\n". " UPDATE %1\$s SET locked = (SELECT ilock FROM edit_revision), hidden = (SELECT ihid FROM edit_revision),\n". - " %3\$s FROM edit_%1\$s x WHERE id = r.itemid;", + " %3\$s FROM edit_%1\$s x WHERE id = nitemid;", $item, join(', ', @{$ts{$item}}), join(', ', map "$_ = x.$_", @{$ts{$item}}); $template =~ s/{([a-z]+)}/$replace{$1}/gr; } -open my $F, '>', "$ROOT/sql/editfunc.sql" or die $!; -print $F "-- Automatically generated by util/sqleditfunc.pl. DO NOT EDIT.\n"; -print $F gensql $_ for sort grep $schema->{$_}{dbentry_type}, keys %$schema; +print "-- Automatically generated by util/sqleditfunc.pl. DO NOT EDIT.\n"; +print gensql $_ for sort grep $schema->{$_}{dbentry_type}, keys %$schema; __DATA__ -CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid integer, xrev integer) RETURNS void AS $$ +CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid vndbid, xrev integer) RETURNS void AS $$ DECLARE xchid integer; BEGIN @@ -70,7 +67,7 @@ BEGIN TRUNCATE {temptablenames}; END; -- Create edit_revision table and get relevant change ID. - SELECT edit_revtable('{itemtype}', xid, xrev) INTO xchid; + SELECT edit_revtable(xid, xrev) INTO xchid; -- new entry, load defaults IF xchid IS NULL THEN INSERT INTO edit_{item} DEFAULT VALUES; @@ -82,18 +79,26 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION edit_{itemtype}_commit() RETURNS edit_rettype AS $$ -DECLARE - r edit_rettype; +CREATE OR REPLACE FUNCTION edit_{itemtype}_commit(out nchid integer, out nitemid vndbid, out nrev integer) AS $$ BEGIN IF (SELECT COUNT(*) FROM edit_{item}) <> 1 THEN RAISE 'edit_{item} must have exactly one row!'; END IF; - SELECT INTO r * FROM edit_commit(); + SELECT itemid INTO nitemid FROM edit_revision; + -- figure out revision number + SELECT MAX(rev)+1 INTO nrev FROM changes WHERE itemid = nitemid; + SELECT COALESCE(nrev, 1) INTO nrev; + -- insert DB item + IF nitemid IS NULL THEN + INSERT INTO {item} DEFAULT VALUES RETURNING id INTO nitemid; + END IF; + -- insert change + INSERT INTO changes (itemid, rev, requester, comments, ihid, ilock) + SELECT nitemid, nrev, requester, comments, ihid, ilock FROM edit_revision RETURNING id INTO nchid; + -- insert data {copyfromtemp} {copymainfromtemp} - PERFORM edit_committed('{itemtype}', r); - RETURN r; + PERFORM edit_committed(nchid, nitemid, nrev); END; $$ LANGUAGE plpgsql; diff --git a/util/svgsprite.pl b/util/svgsprite.pl new file mode 100755 index 00000000..910e0454 --- /dev/null +++ b/util/svgsprite.pl @@ -0,0 +1,54 @@ +#!/usr/bin/perl + +# I had planned to use fragment identifiers as described in +# https://css-tricks.com/svg-fragment-identifiers-work/ +# But it turns out Firefox doesn't cache/reuse the SVG when referenced with +# different fragments. :facepalm: + +use v5.26; +use strict; +use autodie; + +my $GEN = $ENV{VNDB_GEN} // 'gen'; + +my %icons = map +((m{^icons/(.+)\.svg$})[0] =~ s#/#-#rg, $_), glob('icons/*.svg'), glob('icons/*/*.svg'); +my $idnum = 'a'; +my($width, $height) = (-10,0); +my($defs, $group, $css) = ('','',''); + +for my $id (sort keys %icons) { + my $data = do { local $/=undef; open my $F, '<', $icons{$id}; <$F> }; + $data =~ s{<\?xml[^>]*>}{}; + $data =~ s{</svg>}{}g; + $data =~ s/\n//g; + $data =~ s{<svg [^>]*viewBox="0 0 ([^ ]+) ([^ ]+)"[^>]*>}{}; + my($w,$h) = ($1,$2); + my $viewbox = $w // die "No suitable viewBox property found in $icons{$id}\n"; + + # Identifiers must be globally unique, so need to renumber. + my %idmap; + $data =~ s{(id="|href="#|url\(#)([^"\)]+)}{ $idmap{$2}||=$idnum++; $1.$idmap{$2} }eg; + + # Take out the <defs> and put them in global scope, otherwise some(?) renderers can't find the definitions. + $defs .= $1 if $data =~ s{<defs>(.+)</defs>}{}; + + $width += 10; + $group .= qq{<g transform="translate($width)">$data</g>}; + $css .= sprintf ".icon-%s { background-position: %dpx 0; width: %dpx; height: %dpx }\n", $id, -$width, $w, $h; + + $width += $w; + $height = $h if $height < $h; +} + +{ + open my $F, '>', "$GEN/svg.css"; + print $F $css; +} + +{ + open my $F, '>', "$GEN/static/icons.svg"; + print $F qq{<svg xmlns="http://www.w3.org/2000/svg" width="$width" height="$height" viewBox="0 0 $width $height">}; + print $F qq{<defs>$defs</defs>} if $defs; + print $F $group; + print $F '</svg>'; +} diff --git a/util/test/basn4a08.png b/util/test/basn4a08.png Binary files differnew file mode 100644 index 00000000..3e130522 --- /dev/null +++ b/util/test/basn4a08.png diff --git a/util/test/basn6a16.png b/util/test/basn6a16.png Binary files differnew file mode 100644 index 00000000..984a9952 --- /dev/null +++ b/util/test/basn6a16.png diff --git a/util/bbcode-test.pl b/util/test/bbcode.pl index e306c952..94128684 100755 --- a/util/bbcode-test.pl +++ b/util/test/bbcode.pl @@ -5,13 +5,10 @@ use strict; use warnings; -use Cwd 'abs_path'; use Test::More; use Benchmark 'timethese'; -our($ROOT, %S); -BEGIN { ($ROOT = abs_path $0) =~ s{/util/bbcode-test\.pl$}{}; } -use lib "$ROOT/lib"; +use lib 'lib'; use VNDB::BBCode; @@ -37,11 +34,11 @@ my @tests = ( "`some code\n\nalso newlines;`", '[spoiler]some spoiler[/spoiler]', - '<b class="spoiler">some spoiler</b>', + '<span class="spoiler">some spoiler</span>', '', '[b][i][u][s]Formatting![/s][/u][/i][/b]', - '<b><em><span class="underline"><s>Formatting!</s></span></em></b>', + '<strong><em><span class="underline"><s>Formatting!</s></span></em></strong>', '*/_-Formatting!-_/*', "[raw][quote]not parsed\n[url=https://vndb.org/]valid url[/url]\n[url=asdf]invalid url[/url][/quote][/raw]", @@ -49,11 +46,11 @@ my @tests = ( "[quote]not parsed\n[url=https://vndb.org/]valid url[/url]\n[url=asdf]invalid url[/url][/quote]", '[quote]basic [spoiler]single[/spoiler]-line [spoiler][url=/g]tag[/url] nesting [raw](without [url=/v3333]special[/url] cases)[/raw][/spoiler][/quote]', - '<div class="quote">basic <b class="spoiler">single</b>-line <b class="spoiler"><a href="/g" rel="nofollow">tag</a> nesting (without [url=/v3333]special[/url] cases)</b></div>', + '<div class="quote">basic <span class="spoiler">single</span>-line <span class="spoiler"><a href="/g" rel="nofollow">tag</a> nesting (without [url=/v3333]special[/url] cases)</span></div>', '"basic -line "', '[quote][b]more [spoiler]nesting [code]mkay?', - '<div class="quote"><b>more <b class="spoiler">nesting [code]mkay?</b></b></div>', + '<div class="quote"><strong>more <span class="spoiler">nesting [code]mkay?</span></strong></div>', '"*more *"', '[url=/v][b]does not work here[/b][/url]', @@ -82,7 +79,7 @@ my @tests = ( # the new implementation doesn't special-case [code], as the first newline shouldn't matter either way "[quote]\n\nhello, rmnewline test[code]\n#!/bin/sh\n\nfunction random_username() {\n </dev/urandom tr -cd 'a-zA-Z0-9' | dd bs=1 count=16 2>/dev/null\n}\n[/code]\nsome text after the code tag\n[/quote]\n\n[spoiler]\nsome newlined spoiler\n[/spoiler]", - '<div class="quote"><br>hello, rmnewline test<pre>#!/bin/sh<br><br>function random_username() {<br> </dev/urandom tr -cd \'a-zA-Z0-9\' | dd bs=1 count=16 2>/dev/null<br>}<br></pre>some text after the code tag<br></div><br><b class="spoiler"><br>some newlined spoiler<br></b>', + '<div class="quote"><br>hello, rmnewline test<pre>#!/bin/sh<br><br>function random_username() {<br> </dev/urandom tr -cd \'a-zA-Z0-9\' | dd bs=1 count=16 2>/dev/null<br>}<br></pre>some text after the code tag<br></div><br><span class="spoiler"><br>some newlined spoiler<br></span>', "\"\nhello, rmnewline test`#!/bin/sh\n\nfunction random_username() {\n </dev/urandom tr -cd 'a-zA-Z0-9' | dd bs=1 count=16 2>/dev/null\n}\n`some text after the code tag\n\"\n", "[quote]\n[raw]\nrmnewline test with made-up elements\n[/raw]\nwelp\n[dumbtag]\nnone\n[/dumbtag]\n[/quote]", @@ -110,11 +107,11 @@ my @tests = ( 'http://192.168.1.1:8080/some/path (literal ipv4 address, port included)', '[Quote]non-lowercase tags [SpOILER]here[/sPOilER][/qUOTe]', - '<div class="quote">non-lowercase tags <b class="spoiler">here</b></div>', + '<div class="quote">non-lowercase tags <span class="spoiler">here</span></div>', '"non-lowercase tags "', 'some text [spoiler]with (v17) tags[/spoiler] and internal ids such as s1', - 'some text <b class="spoiler">with (<a href="/v17">v17</a>) tags</b> and internal ids such as <a href="/s1">s1</a>', + 'some text <span class="spoiler">with (<a href="/v17">v17</a>) tags</span> and internal ids such as <a href="/s1">s1</a>', 'some text and internal ids such as s1', 'r12.1 v6.3 s1.2 w5.3', @@ -146,16 +143,16 @@ my @tests = ( '<tag>html escapes (&)</tag>', '[spoiler]stray open tag', - '<b class="spoiler">stray open tag</b>', + '<span class="spoiler">stray open tag</span>', '', # TODO: This isn't ideal '[quote][spoiler]stray open tag (nested)[/quote]', - '<div class="quote"><b class="spoiler">stray open tag (nested)[/quote]</b></div>', + '<div class="quote"><span class="spoiler">stray open tag (nested)[/quote]</span></div>', '""', '[quote][spoiler]two stray open tags', - '<div class="quote"><b class="spoiler">two stray open tags</b></div>', + '<div class="quote"><span class="spoiler">two stray open tags</span></div>', '""', "[url=https://cat.xyz/]that's [spoiler]some [quote]uncommon[/quote][/spoiler] combination[/url]", diff --git a/util/test/imgproc-custom.pl b/util/test/imgproc-custom.pl new file mode 100755 index 00000000..3318ad42 --- /dev/null +++ b/util/test/imgproc-custom.pl @@ -0,0 +1,76 @@ +#!/usr/bin/perl + +# This script requires an imagemagick compiled with all image formats supported by imgproc-custom. + +use v5.28; +use warnings; +use Cwd 'abs_path'; + +my $ROOT; +BEGIN { ($ROOT = abs_path $0) =~ s{/util/test/imgproc-custom\.pl$}{}; } + +use lib $ROOT.'/lib'; +use VNDB::Func; + +my $bin = ($ENV{VNDB_GEN} // 'gen').'/imgproc-custom'; + +sub cmphash { + my($fn, $out, $hash) = @_; + my $outd = `$bin size fit 500 500 jpeg 1 <$fn 2>&1 >tst.jpg`; + chomp($outd); + my($hashd) = split / /, `sha1sum tst.jpg`; + die "Hash mismatch for $fn, got $hashd see tst.jpg\n" if $hash ne $hashd; + unlink 'tst.jpg'; + die "Output mismatch for $fn, got $outd" if $out ne $outd; +} + +sub cmpmagick { + my($fn, $arg, $size, $hash) = @_; + `convert -size $size $arg $fn`; + cmphash $fn, $size, $hash; + unlink $fn; +} + +# Test pngs from http://www.schaik.com/pngsuite/ + +# These hashes are likely to change with libvips / libjpeg versions, output +# should be manually verified and the hashes updated in that case. +cmphash 'util/test/basn4a08.png', '32x32', '62c4f502c6e8f13fe72cd511267616ea75724503'; +cmphash 'util/test/basn6a16.png', '32x32', 'f85f1bb196ad6f8c284370bcb74d5cd8b19fc432'; + +# Triggers g_warning() output +die if `$bin size <util/test/xd9n2c08.png 2>&1` !~ /Invalid IHDR data/; +# Triggers vips_error_exit() output +die if `$bin jpeg 5 <util/test/basn4a08.png 2>&1` !~ /write error/; + +# Large images are tested to see if extra memory or thread pool use triggers more unique system calls. +# (it does, and yes it varies per input format) +cmpmagick 'large.png', '"canvas:rgb(100,50,30)"', '5000x5000', 'c5f1d23d43f3ec42ce04a31ba67334c2b5f68ee2'; + +cmpmagick 'large-lossless.webp', '"canvas:rgb(100,50,30)" -define webp:lossless=true', '5000x5000', 'c5f1d23d43f3ec42ce04a31ba67334c2b5f68ee2'; +cmpmagick 'large-lossy.webp', '"canvas:rgb(100,50,30)" -define webp:lossless=false', '5000x5000', 'e043021ad032a8dbfbb21bef373ea9e2851baf51'; +cmpmagick 'gray.webp', 'pattern:GRAY50 -colorspace GRAY -define webp:lossless=true', '32x32', '8de7aebd2d86572f9dc320886a3bc4cf59bb53ca'; + +cmpmagick 'large.jpg', '"canvas:rgb(100,50,30)"', '5000x5000', '7a54b06bdf1b742c5a97f2a105de48da81f3b284'; +cmpmagick 'gray.jpg', 'pattern:GRAY50 -colorspace GRAY', '32x32', '13980f3168cdddbe193b445552dab40fa9afa0a1'; +cmpmagick 'cmyk.jpg', 'LOGO: -colorspace CMYK', '640x480', '3ff8566e661a0faef5a90d11195819983b595876'; + +cmpmagick 'large.avif', '"canvas:rgb(100,50,30)"', '5000x5000', 'b42788bf491a9a73d30d58c3a3a843e219f36f91'; + +cmpmagick 'large.jxl', '"canvas:rgb(100,50,30)"', '5000x5000', '76b8bbca1df2184319ec9d7e57250e0b8b7b5c2f'; + +# TODO: Test metadata stripping? + +# Slow, dumb and somewhat comprehensive thumbnail size checks, it's important +# that the dimensions match with imgsize(). +exit; # don't need to test this often +for my $w (10, 50, 256, 400) { + for my $h (300..1000) { + `convert -size ${w}x$h 'canvas:rgb(0,0,0)' tst.png`; + my $dim = `$bin fit 256 300 size <tst.png 2>&1`; + unlink 'tst.png'; + chomp($dim); + my $size = join 'x', imgsize $w, $h, 256, 300; + die "$dim != $size\n" if $dim ne $size; + } +} diff --git a/util/test/xd9n2c08.png b/util/test/xd9n2c08.png Binary files differnew file mode 100644 index 00000000..2c3b91aa --- /dev/null +++ b/util/test/xd9n2c08.png diff --git a/util/unusedimages.pl b/util/unusedimages.pl index 019d3c9d..01678f77 100755 --- a/util/unusedimages.pl +++ b/util/unusedimages.pl @@ -14,10 +14,13 @@ use Cwd 'abs_path'; my $ROOT; BEGIN { ($ROOT = abs_path $0) =~ s{/util/unusedimages\.pl$}{}; } +$ENV{VNDB_VAR} //= 'var'; + my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1 }); my $count = 0; -my $fnmatch = '/(cv|ch|sf|st)/[0-9][0-9]/([1-9][0-9]{0,6})\.jpg'; +my $dirmatch = '/(cv|ch|sf|st)(?:\.orig|\.t)?/'; +my $fnmatch = $dirmatch.'[0-9][0-9]/([1-9][0-9]{0,6})\.(?:jpg|webp|png|avif|jxl)?'; my(%scr, %cv, %ch); my %dir = (cv => \%cv, ch => \%ch, sf => \%scr, st => \%scr); @@ -48,18 +51,20 @@ sub cleandb { SELECT vndbid(case when img[1] = 'st' then 'sf' else img[1] end, img[2]::int) FROM ( SELECT content FROM docs UNION ALL SELECT content FROM docs_hist - UNION ALL SELECT "desc" FROM vn - UNION ALL SELECT "desc" FROM vn_hist - UNION ALL SELECT "desc" FROM chars - UNION ALL SELECT "desc" FROM chars_hist - UNION ALL SELECT "desc" FROM producers - UNION ALL SELECT "desc" FROM producers_hist + UNION ALL SELECT description FROM vn + UNION ALL SELECT description FROM vn_hist + UNION ALL SELECT description FROM chars + UNION ALL SELECT description FROM chars_hist + UNION ALL SELECT description FROM producers + UNION ALL SELECT description FROM producers_hist UNION ALL SELECT notes FROM releases UNION ALL SELECT notes FROM releases_hist - UNION ALL SELECT "desc" FROM staff - UNION ALL SELECT "desc" FROM staff_hist + UNION ALL SELECT description FROM staff + UNION ALL SELECT description FROM staff_hist UNION ALL SELECT description FROM tags + UNION ALL SELECT description FROM tags_hist UNION ALL SELECT description FROM traits + UNION ALL SELECT description FROM traits_hist UNION ALL SELECT comments FROM changes UNION ALL SELECT msg FROM threads_posts UNION ALL SELECT msg FROM reviews_posts @@ -91,11 +96,10 @@ sub findunused { my $left = 0; find { no_chdir => 1, - follow => 1, wanted => sub { return if -d "$File::Find::name"; if($File::Find::name !~ /($fnmatch)$/) { - print "# Unknown file: $File::Find::name\n"; + print "# Unknown file: $File::Find::name\n" if $File::Find::name =~ /$dirmatch/; return; } if(!$dir{$2}{$3}) { @@ -107,7 +111,7 @@ sub findunused { $left++; } } - }, "$ROOT/static/cv", "$ROOT/static/ch", "$ROOT/static/sf", "$ROOT/static/st"; + }, "$ENV{VNDB_VAR}/static"; printf "# Deleted %d files, left %d files, saved %d KiB\n", $count, $left, $size; } diff --git a/util/updates/2020-12-14-release-extlinks.sql b/util/updates/2020-12-14-release-extlinks.sql new file mode 100644 index 00000000..83da4083 --- /dev/null +++ b/util/updates/2020-12-14-release-extlinks.sql @@ -0,0 +1,46 @@ +ALTER TABLE releases ADD COLUMN l_animateg integer NOT NULL DEFAULT 0; +ALTER TABLE releases_hist ADD COLUMN l_animateg integer NOT NULL DEFAULT 0; +ALTER TABLE releases ADD COLUMN l_freem integer NOT NULL DEFAULT 0; +ALTER TABLE releases_hist ADD COLUMN l_freem integer NOT NULL DEFAULT 0; +-- I don't think I've actually seen app store IDs that didn't fit in an int, but they can get pretty close. +ALTER TABLE releases ADD COLUMN l_appstore bigint NOT NULL DEFAULT 0; +ALTER TABLE releases_hist ADD COLUMN l_appstore bigint NOT NULL DEFAULT 0; +ALTER TABLE releases ADD COLUMN l_googplay text NOT NULL DEFAULT ''; +ALTER TABLE releases_hist ADD COLUMN l_googplay text NOT NULL DEFAULT ''; +\i sql/editfunc.sql + + +CREATE OR REPLACE FUNCTION migrate_website_to_freem(rid integer) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid AND type = 'r')); + UPDATE edit_releases SET l_freem = regexp_replace(website, '^https?://(?:www\.)?freem\.ne\.jp/win/game/([0-9]+)$', '\1')::int, website = ''; + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic conversion of website to Freem link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_website_to_freem(id) FROM releases WHERE NOT hidden AND website ~ '^https?://(?:www\.)?freem\.ne\.jp/win/game/([0-9]+)$'; +DROP FUNCTION migrate_website_to_freem(integer); + + +CREATE OR REPLACE FUNCTION migrate_website_to_googplay(rid integer) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid AND type = 'r')); + UPDATE edit_releases SET l_googplay = regexp_replace(website, '^https?://play\.google\.com/store/apps/details\?id=([^/&\?]+)(?:&.*)?$', '\1'), website = ''; + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic conversion of website to Google Play store link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_website_to_googplay(id) FROM releases WHERE NOT hidden AND website ~ '^https?://play\.google\.com/store/apps/details\?id=([^/&\?]+)(?:&.*)?$'; +DROP FUNCTION migrate_website_to_googplay(integer); + + +CREATE OR REPLACE FUNCTION migrate_website_to_appstore(rid integer) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid AND type = 'r')); + UPDATE edit_releases SET l_appstore = regexp_replace(website, '^https?://(?:itunes|apps)\.apple\.com/(?:[^/]+/)?app/(?:[^/]+/)?id([0-9]+)([\?/].*)?$', '\1')::bigint, website = ''; + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic conversion of website to Apple App Store link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_website_to_appstore(id) FROM releases WHERE NOT hidden AND website ~ '^https?://(?:itunes|apps)\.apple\.com/(?:[^/]+/)?app/(?:[^/]+/)?id([0-9]+)([\?/].*)?$'; +DROP FUNCTION migrate_website_to_appstore(integer); diff --git a/util/updates/2020-12-15-release-extlinks.sql b/util/updates/2020-12-15-release-extlinks.sql new file mode 100644 index 00000000..d554aa43 --- /dev/null +++ b/util/updates/2020-12-15-release-extlinks.sql @@ -0,0 +1,16 @@ +ALTER TABLE releases ADD COLUMN l_fakku text NOT NULL DEFAULT ''; +ALTER TABLE releases_hist ADD COLUMN l_fakku text NOT NULL DEFAULT ''; +ALTER TABLE releases ADD COLUMN l_novelgam integer NOT NULL DEFAULT 0; +ALTER TABLE releases_hist ADD COLUMN l_novelgam integer NOT NULL DEFAULT 0; +\i sql/editfunc.sql + +CREATE OR REPLACE FUNCTION migrate_website_to_novelgam(rid integer) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid AND type = 'r')); + UPDATE edit_releases SET l_novelgam = regexp_replace(website, '^https?://(?:www\.)?novelgame\.jp/games/show/([0-9]+)$', '\1')::int, website = ''; + UPDATE edit_revision SET requester = 1, ip = '0.0.0.0', comments = 'Automatic conversion of website to NovelGame link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_website_to_novelgam(id) FROM releases WHERE NOT hidden AND website ~ '^https?://(?:www\.)?novelgame\.jp/games/show/([0-9]+)$'; +DROP FUNCTION migrate_website_to_novelgam(integer); diff --git a/util/updates/2021-01-03-advsearch-saved-queries.sql b/util/updates/2021-01-03-advsearch-saved-queries.sql new file mode 100644 index 00000000..89a6f844 --- /dev/null +++ b/util/updates/2021-01-03-advsearch-saved-queries.sql @@ -0,0 +1,10 @@ +CREATE TABLE saved_queries ( + uid integer NOT NULL, + name text NOT NULL, + qtype dbentry_type NOT NULL, + query text NOT NULL, -- compact encoded form + PRIMARY KEY(uid, qtype, name) +); + +ALTER TABLE saved_queries ADD CONSTRAINT saved_queries_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +GRANT SELECT, INSERT, UPDATE, DELETE ON saved_queries TO vndb_site; diff --git a/util/updates/2021-01-10-advsearch-convert-saved-filters.pl b/util/updates/2021-01-10-advsearch-convert-saved-filters.pl new file mode 100755 index 00000000..16e569b0 --- /dev/null +++ b/util/updates/2021-01-10-advsearch-convert-saved-filters.pl @@ -0,0 +1,46 @@ +#!/usr/bin/perl + +use v5.24; +use warnings; +use TUWF; +use Cwd 'abs_path'; + +my $ROOT; +BEGIN { ($ROOT = abs_path $0) =~ s{/util/updates/[^/]+.pl$}{}; } + +use lib $ROOT.'/lib'; +use VNDB::Config; + +BEGIN { TUWF::set %{ config->{tuwf} } }; + +use VNWeb::AdvSearch; +use VNWeb::Filters; + +for my $r (tuwf->dbAlli('SELECT id, filter_vn AS fil FROM users WHERE filter_vn <> \'\' AND NOT EXISTS(SELECT 1 FROM saved_queries WHERE uid = id AND name = \'\' AND qtype = \'v\') ORDER BY id')->@*) { + next if $r->{fil} =~ /^tagspoil-\d+$/; + + # HACK: trick VNWeb code into thinking we're logged in as the user owning the filter. + tuwf->{_TUWF}{request_data}{auth} = bless { user => { user_id => $r->{id} } }, 'VNWeb::Auth'; + + my $q = eval { tuwf->compile({advsearch => 'v'})->validate(filter_vn_adv filter_parse v => $r->{fil})->data }; + if(!$q) { + warn "Unable to convert VN filter for u$r->{id}, \"$r->{fil}\": $@"; + next; + } + my $qs = $q->query_encode; + tuwf->dbExeci('INSERT INTO saved_queries', { uid => $r->{id}, qtype => 'v', name => '', query => $qs }) if $qs; +} + +for my $r (tuwf->dbAlli('SELECT id, filter_release AS fil FROM users WHERE filter_release <> \'\' AND NOT EXISTS(SELECT 1 FROM saved_queries WHERE uid = id AND name = \'\' AND qtype = \'r\') ORDER BY id')->@*) { + tuwf->{_TUWF}{request_data}{auth} = bless { user => { user_id => $r->{id} } }, 'VNWeb::Auth'; + + my $q = eval { tuwf->compile({advsearch => 'r'})->validate(filter_release_adv filter_parse r => $r->{fil})->data }; + if(!$q) { + warn "Unable to convert release filter for u$r->{id}, \"$r->{fil}\": $@"; + next; + } + my $qs = $q->query_encode; + tuwf->dbExeci('INSERT INTO saved_queries', { uid => $r->{id}, qtype => 'r', name => '', query => $qs }) if $qs; +} + +tuwf->dbCommit; diff --git a/util/updates/2021-01-17-irish-language.sql b/util/updates/2021-01-17-irish-language.sql new file mode 100644 index 00000000..4b35d174 --- /dev/null +++ b/util/updates/2021-01-17-irish-language.sql @@ -0,0 +1 @@ +ALTER TYPE language ADD VALUE 'ga' AFTER 'fr'; diff --git a/util/updates/2021-01-21-update-saved-queries.pl b/util/updates/2021-01-21-update-saved-queries.pl new file mode 100755 index 00000000..f93d4643 --- /dev/null +++ b/util/updates/2021-01-21-update-saved-queries.pl @@ -0,0 +1,66 @@ +#!/usr/bin/perl + +# This script checks and updates all queries in the saved_queries table. + +use v5.24; +use warnings; +use Cwd 'abs_path'; +use TUWF; + +my $ROOT; +BEGIN { ($ROOT = abs_path $0) =~ s{/util/saved-queries\.pl$}{}; } + +use lib $ROOT.'/lib'; +use VNDB::Config; + +TUWF::set %{ config->{tuwf} }; + +require VNWeb::AdvSearch; + + +my($total, $updated, $err) = (0,0,0); + +for my $r (tuwf->dbAlli('SELECT uid, qtype, name, query FROM saved_queries')->@*) { + $total++; + my $q = eval { tuwf->compile({advsearch => $r->{qtype}})->validate($r->{query})->data }; + if(!$q) { + $err++; + warn "Invalid query: $r->{uid}, $r->{qtype}, \"$r->{name}\": $r->{query}\n"; + next; + } + + # The old filter->advsearch conversion had a bug that caused length filters to get AND'ed together, which doesn't make sense. + if($r->{qtype} eq 'v' && !$r->{name} && $q->{query}[0] eq 'and') { + my @lengths = grep ref $_ && $_->[0] eq 'length', $q->{query}->@*; + $q->{query} = [ grep(!ref $_ || $_->[0] ne 'length', $q->{query}->@*), [ 'or', @lengths ] ] if @lengths > 1; + warn "Converted 'AND length' to 'OR length' for $r->{uid}\n" if @lengths > 1; + } + + # "Unlabeled && !Unlabeled" used to mean "on my list" and was what the old filter conversions used. + # That meaning has changed and we now have a better on-list filter. + if($r->{qtype} eq 'v' && $q->{query}[0] eq 'and') { + my sub isonlist { + my $q = $_; + ref $q && $q->[0] eq 'or' && @$q == 3 + && $q->[1][0] eq 'label' && $q->[1][1] eq '=' && ref $q->[1][2] && $q->[1][2][0] eq $r->{uid} && $q->[1][2][1] eq 0 + && $q->[2][0] eq 'label' && $q->[2][1] eq '!=' && ref $q->[2][2] && $q->[2][2][0] eq $r->{uid} && $q->[2][2][1] eq 0 + } + my $e=0; + $q->{query} = [ map isonlist($_) ? do { $e=1; [ 'on-list', '=', 1 ] } : $_, $q->{query}->@* ]; + warn "Converted Unlabaled hack to on-list for $r->{uid}\n" if $e; + } + + my $qs = $q->query_encode; + if(!$qs) { + warn "Empty query: $r->{uid}, $r->{qtype}, \"$r->{name}\": $r->{query}\n"; + next; + } + if($qs ne $r->{query}) { + $updated++; + tuwf->dbExeci('UPDATE saved_queries SET query =', \$qs, 'WHERE', { uid => $r->{uid}, qtype => $r->{qtype}, name => $r->{name} }); + } +} + +tuwf->dbCommit; + +printf "Updated %d/%d saved queries, %d errors.\n", $updated, $total, $err; diff --git a/util/updates/2021-01-30-vn-olang.sql b/util/updates/2021-01-30-vn-olang.sql new file mode 100644 index 00000000..4f12c179 --- /dev/null +++ b/util/updates/2021-01-30-vn-olang.sql @@ -0,0 +1,35 @@ +ALTER TABLE vn ADD COLUMN olang language NOT NULL DEFAULT 'ja'; +ALTER TABLE vn_hist ADD COLUMN olang language NOT NULL DEFAULT 'ja'; + + +-- Initial original language: Use c_olang if it only has a single language, +-- fall back to developer's language if there are multiple languages. +-- (Based on the idea from https://vndb.org/t12800.23) +-- There are still ~50 games for which that fails due to the lack of a +-- developer entry, and ~20 games for which we have no releases at all. +-- These will have to be updated manually. +WITH dl(id, lang) AS ( + SELECT rv.vid, MIN(p.lang) + FROM releases_vn rv + JOIN releases r ON r.id = rv.id + JOIN releases_producers rp ON rp.id = rv.id + JOIN producers p ON p.id = rp.pid + WHERE NOT p.hidden AND NOT r.hidden AND rp.developer + GROUP BY rv.vid +), vl(id, hidden, lang) AS ( + SELECT vn.id, vn.hidden, CASE WHEN array_length(vn.c_olang, 1) = 1 THEN vn.c_olang[1] ELSE dl.lang END + FROM vn + LEFT JOIN dl ON dl.id = vn.id +) UPDATE vn SET olang = vl.lang FROM vl WHERE vn.id = vl.id AND vl.lang IS NOT NULL; +--) SELECT 'https://vndb.org/v'||id FROM vl WHERE NOT hidden AND lang IS NULL ORDER BY id; + +-- Make sure vn_hist is consistent with vn. +WITH ch(id, lang) AS ( + SELECT c.id, v.olang + FROM changes c + JOIN vn v ON v.id = c.itemid + WHERE c.type = 'v' +) UPDATE vn_hist SET olang = ch.lang FROM ch WHERE vn_hist.chid = ch.id; + +\i sql/editfunc.sql +\i sql/func.sql diff --git a/util/updates/2021-02-02-cleanup.sql b/util/updates/2021-02-02-cleanup.sql new file mode 100644 index 00000000..9165e1df --- /dev/null +++ b/util/updates/2021-02-02-cleanup.sql @@ -0,0 +1,8 @@ +ALTER TABLE threads DROP COLUMN poll_preview; +ALTER TABLE threads DROP COLUMN poll_recast; +ALTER TABLE users DROP COLUMN filter_vn; +ALTER TABLE users DROP COLUMN filter_release; +ALTER TABLE users DROP COLUMN show_nsfw; +ALTER TABLE users DROP COLUMN vn_list_own; +ALTER TABLE users DROP COLUMN vn_list_wish; +ALTER TABLE vn DROP COLUMN c_olang; diff --git a/util/updates/2021-02-08-user-lookup-by-mail.sql b/util/updates/2021-02-08-user-lookup-by-mail.sql new file mode 100644 index 00000000..02d18a88 --- /dev/null +++ b/util/updates/2021-02-08-user-lookup-by-mail.sql @@ -0,0 +1,2 @@ +DROP FUNCTION user_emailexists(text, integer); +\i sql/func.sql diff --git a/util/updates/2021-02-13-uid-0.sql b/util/updates/2021-02-13-uid-0.sql new file mode 100644 index 00000000..c3ccb328 --- /dev/null +++ b/util/updates/2021-02-13-uid-0.sql @@ -0,0 +1,11 @@ +-- columns that could still refer to uid=0 +ALTER TABLE changes ALTER COLUMN requester DROP DEFAULT; +ALTER TABLE changes ALTER COLUMN requester DROP NOT NULL; +UPDATE changes SET requester = NULL WHERE requester = 0; +ALTER TABLE tags ALTER COLUMN addedby DROP DEFAULT; +ALTER TABLE tags ALTER COLUMN addedby DROP NOT NULL; +UPDATE tags SET addedby = NULL WHERE addedby = 0; +ALTER TABLE traits ALTER COLUMN addedby DROP DEFAULT; +ALTER TABLE traits ALTER COLUMN addedby DROP NOT NULL; +UPDATE traits SET addedby = NULL WHERE addedby = 0; +DELETE FROM users WHERE id = 0; diff --git a/util/updates/2021-02-22-tableopts-char.sql b/util/updates/2021-02-22-tableopts-char.sql new file mode 100644 index 00000000..af5f5168 --- /dev/null +++ b/util/updates/2021-02-22-tableopts-char.sql @@ -0,0 +1,2 @@ +ALTER TABLE users ADD COLUMN tableopts_c int; +\i sql/perms.sql diff --git a/util/updates/2021-03-01-entries-to-vndbid.sql b/util/updates/2021-03-01-entries-to-vndbid.sql new file mode 100644 index 00000000..29669bea --- /dev/null +++ b/util/updates/2021-03-01-entries-to-vndbid.sql @@ -0,0 +1,245 @@ +-- Public dump breakage: +-- SELECT .. FROM vn WHERE id = 10; +-- SELECT .. FROM vn WHERE id IN(1,2,3); +-- SELECT 'https://vndb.org/v'||id FROM vn; + +BEGIN; + +ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey; +ALTER TABLE chars DROP CONSTRAINT chars_main_fkey; +ALTER TABLE chars_hist DROP CONSTRAINT chars_hist_main_fkey; +ALTER TABLE chars_traits DROP CONSTRAINT chars_traits_id_fkey; +ALTER TABLE chars_vns DROP CONSTRAINT chars_vns_id_fkey; +ALTER TABLE chars_vns DROP CONSTRAINT chars_vns_rid_fkey; +ALTER TABLE chars_vns DROP CONSTRAINT chars_vns_vid_fkey; +ALTER TABLE chars_vns_hist DROP CONSTRAINT chars_vns_hist_rid_fkey; +ALTER TABLE chars_vns_hist DROP CONSTRAINT chars_vns_hist_vid_fkey; +ALTER TABLE image_votes DROP CONSTRAINT image_votes_uid_fkey; +ALTER TABLE notification_subs DROP CONSTRAINT notification_subs_uid_fkey; +ALTER TABLE notifications DROP CONSTRAINT notifications_uid_fkey; +ALTER TABLE producers_relations DROP CONSTRAINT producers_relations_pid_fkey; +ALTER TABLE producers_relations_hist DROP CONSTRAINT producers_relations_hist_pid_fkey; +ALTER TABLE quotes DROP CONSTRAINT quotes_vid_fkey; +ALTER TABLE releases_lang DROP CONSTRAINT releases_lang_id_fkey; +ALTER TABLE releases_media DROP CONSTRAINT releases_media_id_fkey; +ALTER TABLE releases_platforms DROP CONSTRAINT releases_platforms_id_fkey; +ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_id_fkey; +ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_pid_fkey; +ALTER TABLE releases_producers_hist DROP CONSTRAINT releases_producers_hist_pid_fkey; +ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_id_fkey; +ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_vid_fkey; +ALTER TABLE releases_vn_hist DROP CONSTRAINT releases_vn_hist_vid_fkey; +ALTER TABLE reviews DROP CONSTRAINT reviews_rid_fkey; +ALTER TABLE reviews DROP CONSTRAINT reviews_uid_fkey; +ALTER TABLE reviews DROP CONSTRAINT reviews_vid_fkey; +ALTER TABLE reviews_posts DROP CONSTRAINT reviews_posts_uid_fkey; +ALTER TABLE reviews_votes DROP CONSTRAINT reviews_votes_uid_fkey; +ALTER TABLE rlists DROP CONSTRAINT rlists_rid_fkey; +ALTER TABLE rlists DROP CONSTRAINT rlists_uid_fkey; +ALTER TABLE saved_queries DROP CONSTRAINT saved_queries_uid_fkey; +ALTER TABLE sessions DROP CONSTRAINT sessions_uid_fkey; +ALTER TABLE staff_alias DROP CONSTRAINT staff_alias_id_fkey; +ALTER TABLE tags DROP CONSTRAINT tags_addedby_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_vid_fkey; +ALTER TABLE threads_poll_votes DROP CONSTRAINT threads_poll_votes_uid_fkey; +ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_uid_fkey; +ALTER TABLE traits DROP CONSTRAINT traits_addedby_fkey; +ALTER TABLE ulist_labels DROP CONSTRAINT ulist_labels_uid_fkey; +ALTER TABLE ulist_vns DROP CONSTRAINT ulist_vns_uid_fkey; +ALTER TABLE ulist_vns DROP CONSTRAINT ulist_vns_vid_fkey; +ALTER TABLE ulist_vns_labels DROP CONSTRAINT ulist_vns_labels_uid_fkey; +ALTER TABLE ulist_vns_labels DROP CONSTRAINT ulist_vns_labels_uid_lbl_fkey; +ALTER TABLE ulist_vns_labels DROP CONSTRAINT ulist_vns_labels_uid_vid_fkey; +ALTER TABLE ulist_vns_labels DROP CONSTRAINT ulist_vns_labels_vid_fkey; +ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_id_fkey; +ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_id_fkey; +ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid_fkey; +ALTER TABLE vn_relations_hist DROP CONSTRAINT vn_relations_vid_fkey; +ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_id_fkey; +ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_rid_fkey; +ALTER TABLE vn_screenshots_hist DROP CONSTRAINT vn_screenshots_hist_rid_fkey; +ALTER TABLE vn_seiyuu DROP CONSTRAINT vn_seiyuu_cid_fkey; +ALTER TABLE vn_seiyuu DROP CONSTRAINT vn_seiyuu_id_fkey; +ALTER TABLE vn_seiyuu_hist DROP CONSTRAINT vn_seiyuu_hist_cid_fkey; +ALTER TABLE vn_staff DROP CONSTRAINT vn_staff_id_fkey; + +DROP INDEX chars_vns_pkey; +DROP INDEX chars_vns_hist_pkey; + +ALTER TABLE rlists ALTER COLUMN uid DROP DEFAULT; +ALTER TABLE rlists ALTER COLUMN rid DROP DEFAULT; + + +DROP INDEX changes_itemrev; +ALTER TABLE changes ALTER COLUMN itemid TYPE vndbid USING vndbid(type::text, itemid); +ALTER TABLE changes DROP COLUMN type; + +ALTER TABLE threads_boards DROP CONSTRAINT threads_boards_pkey; +ALTER TABLE threads_boards ALTER COLUMN iid DROP DEFAULT; +ALTER TABLE threads_boards ALTER COLUMN iid DROP NOT NULL; +ALTER TABLE threads_boards ALTER COLUMN iid TYPE vndbid USING CASE WHEN iid = 0 THEN NULL ELSE vndbid(type::text, iid) END; + +ALTER TABLE audit_log ALTER COLUMN by_uid TYPE vndbid USING vndbid('u', by_uid); +ALTER TABLE audit_log ALTER COLUMN affected_uid TYPE vndbid USING vndbid('u', affected_uid); +ALTER TABLE reports ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); + + +ALTER TABLE chars ALTER COLUMN id DROP DEFAULT; +ALTER TABLE chars ALTER COLUMN id TYPE vndbid USING vndbid('c', id); +ALTER TABLE chars ALTER COLUMN id SET DEFAULT vndbid('c', nextval('chars_id_seq')::int); +ALTER TABLE chars ADD CONSTRAINT chars_id_check CHECK(vndbid_type(id) = 'c'); + +ALTER TABLE chars ALTER COLUMN main TYPE vndbid USING vndbid('c', main); +ALTER TABLE chars_hist ALTER COLUMN main TYPE vndbid USING vndbid('c', main); +ALTER TABLE chars_traits ALTER COLUMN id TYPE vndbid USING vndbid('c', id); +ALTER TABLE chars_vns ALTER COLUMN id TYPE vndbid USING vndbid('c', id); +ALTER TABLE traits_chars ALTER COLUMN cid TYPE vndbid USING vndbid('c', cid); +ALTER TABLE vn_seiyuu ALTER COLUMN cid TYPE vndbid USING vndbid('c', cid); +ALTER TABLE vn_seiyuu_hist ALTER COLUMN cid TYPE vndbid USING vndbid('c', cid); + + +ALTER TABLE docs ALTER COLUMN id DROP DEFAULT; +ALTER TABLE docs ALTER COLUMN id TYPE vndbid USING vndbid('d', id); +ALTER TABLE docs ALTER COLUMN id SET DEFAULT vndbid('d', nextval('docs_id_seq')::int); +ALTER TABLE docs ADD CONSTRAINT docs_id_check CHECK(vndbid_type(id) = 'd'); + + +ALTER TABLE producers ALTER COLUMN id DROP DEFAULT; +ALTER TABLE producers ALTER COLUMN id TYPE vndbid USING vndbid('p', id); +ALTER TABLE producers ALTER COLUMN id SET DEFAULT vndbid('p', nextval('producers_id_seq')::int); +ALTER TABLE producers ADD CONSTRAINT producers_id_check CHECK(vndbid_type(id) = 'p'); + +ALTER TABLE producers_relations ALTER COLUMN id TYPE vndbid USING vndbid('p', id); +ALTER TABLE producers_relations ALTER COLUMN pid TYPE vndbid USING vndbid('p', pid); +ALTER TABLE producers_relations_hist ALTER COLUMN pid TYPE vndbid USING vndbid('p', pid); +ALTER TABLE releases_producers ALTER COLUMN pid TYPE vndbid USING vndbid('p', pid); +ALTER TABLE releases_producers_hist ALTER COLUMN pid TYPE vndbid USING vndbid('p', pid); + + +ALTER TABLE releases ALTER COLUMN id DROP DEFAULT; +ALTER TABLE releases ALTER COLUMN id TYPE vndbid USING vndbid('r', id); +ALTER TABLE releases ALTER COLUMN id SET DEFAULT vndbid('r', nextval('releases_id_seq')::int); +ALTER TABLE releases ADD CONSTRAINT releases_id_check CHECK(vndbid_type(id) = 'r'); + +ALTER TABLE chars_vns ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid); +ALTER TABLE chars_vns_hist ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid); +ALTER TABLE releases_lang ALTER COLUMN id TYPE vndbid USING vndbid('r', id); +ALTER TABLE releases_media ALTER COLUMN id TYPE vndbid USING vndbid('r', id); +ALTER TABLE releases_platforms ALTER COLUMN id TYPE vndbid USING vndbid('r', id); +ALTER TABLE releases_producers ALTER COLUMN id TYPE vndbid USING vndbid('r', id); +ALTER TABLE releases_vn ALTER COLUMN id TYPE vndbid USING vndbid('r', id); +ALTER TABLE reviews ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid); +ALTER TABLE rlists ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid); +ALTER TABLE vn_screenshots ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid); +ALTER TABLE vn_screenshots_hist ALTER COLUMN rid TYPE vndbid USING vndbid('r', rid); + + +ALTER TABLE staff ALTER COLUMN id DROP DEFAULT; +ALTER TABLE staff ALTER COLUMN id TYPE vndbid USING vndbid('s', id); +ALTER TABLE staff ALTER COLUMN id SET DEFAULT vndbid('s', nextval('staff_id_seq')::int); +ALTER TABLE staff ADD CONSTRAINT staff_id_check CHECK(vndbid_type(id) = 's'); + +ALTER TABLE staff_alias ALTER COLUMN id TYPE vndbid USING vndbid('s', id); + + +ALTER TABLE vn ALTER COLUMN id DROP DEFAULT; +ALTER TABLE vn ALTER COLUMN id TYPE vndbid USING vndbid('v', id); +ALTER TABLE vn ALTER COLUMN id SET DEFAULT vndbid('v', nextval('vn_id_seq')::int); +ALTER TABLE vn ADD CONSTRAINT vn_id_check CHECK(vndbid_type(id) = 'v'); + +ALTER TABLE chars_vns ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE chars_vns_hist ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE quotes ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE releases_vn ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE releases_vn_hist ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE reviews ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE tags_vn ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE tags_vn_inherit ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE ulist_vns ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE ulist_vns_labels ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE vn_anime ALTER COLUMN id TYPE vndbid USING vndbid('v', id); +ALTER TABLE vn_relations ALTER COLUMN id TYPE vndbid USING vndbid('v', id); +ALTER TABLE vn_relations ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE vn_relations_hist ALTER COLUMN vid TYPE vndbid USING vndbid('v', vid); +ALTER TABLE vn_screenshots ALTER COLUMN id TYPE vndbid USING vndbid('v', id); +ALTER TABLE vn_seiyuu ALTER COLUMN id TYPE vndbid USING vndbid('v', id); +ALTER TABLE vn_staff ALTER COLUMN id TYPE vndbid USING vndbid('v', id); + + +ALTER TABLE users ALTER COLUMN id DROP DEFAULT; +ALTER TABLE users ALTER COLUMN id TYPE vndbid USING vndbid('u', id); +ALTER TABLE users ALTER COLUMN id SET DEFAULT vndbid('u', nextval('users_id_seq')::int); +ALTER TABLE users ADD CONSTRAINT users_id_check CHECK(vndbid_type(id) = 'u'); + +ALTER TABLE changes ALTER COLUMN requester TYPE vndbid USING vndbid('u', requester); +ALTER TABLE image_votes ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE notification_subs ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE notifications ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE reviews ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE reviews_posts ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE reviews_votes ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE rlists ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE saved_queries ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE sessions ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE tags ALTER COLUMN addedby TYPE vndbid USING vndbid('u', addedby); +ALTER TABLE tags_vn ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE threads_poll_votes ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE threads_posts ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE traits ALTER COLUMN addedby TYPE vndbid USING vndbid('u', addedby); +ALTER TABLE ulist_labels ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE ulist_vns ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); +ALTER TABLE ulist_vns_labels ALTER COLUMN uid TYPE vndbid USING vndbid('u', uid); + +ALTER TABLE images ALTER COLUMN c_uids DROP DEFAULT; +ALTER TABLE images ALTER COLUMN c_uids TYPE vndbid[] USING '{}'; +ALTER TABLE images ALTER COLUMN c_uids SET DEFAULT '{}'; + +DROP FUNCTION edit_revtable(dbentry_type, integer, integer); +DROP FUNCTION edit_commit(); +DROP FUNCTION edit_committed(dbentry_type, edit_rettype); +DROP FUNCTION edit_c_init(integer, integer); +DROP FUNCTION edit_d_init(integer, integer); +DROP FUNCTION edit_p_init(integer, integer); +DROP FUNCTION edit_r_init(integer, integer); +DROP FUNCTION edit_s_init(integer, integer); +DROP FUNCTION edit_v_init(integer, integer); +DROP FUNCTION edit_c_commit(); +DROP FUNCTION edit_d_commit(); +DROP FUNCTION edit_p_commit(); +DROP FUNCTION edit_r_commit(); +DROP FUNCTION edit_s_commit(); +DROP FUNCTION edit_v_commit(); + +DROP FUNCTION update_vncache(integer); +DROP FUNCTION tag_vn_calc(integer); +DROP FUNCTION traits_chars_calc(integer); +DROP FUNCTION ulist_labels_create(integer); +DROP FUNCTION item_info(id vndbid, num int); +DROP FUNCTION notify(iid vndbid, num integer, uid integer); +DROP FUNCTION update_users_ulist_stats(integer); +DROP FUNCTION user_getscryptargs(integer); +DROP FUNCTION user_login(integer, bytea, bytea); +DROP FUNCTION user_logout(integer, bytea); +DROP FUNCTION user_isvalidsession(integer, bytea, session_type); +DROP FUNCTION user_emailtoid(text); +DROP FUNCTION user_resetpass(text, bytea); +DROP FUNCTION user_setpass(integer, bytea, bytea); +DROP FUNCTION user_isauth(integer, integer, bytea); +DROP FUNCTION user_getmail(integer, integer, bytea); +DROP FUNCTION user_setmail_token(integer, bytea, bytea, text); +DROP FUNCTION user_setmail_confirm(integer, bytea); +DROP FUNCTION user_setperm_usermod(integer, integer, bytea, boolean); +DROP FUNCTION user_admin_setpass(integer, integer, bytea, bytea); +DROP FUNCTION user_admin_setmail(integer, integer, bytea, text); +\i sql/func.sql +\i sql/editfunc.sql +DROP TYPE edit_rettype; + +COMMIT; + +-- Need to do this analyze to ensure adding the foreign key constraints will use proper query plans. +ANALYZE; +\i sql/tableattrs.sql +\i sql/perms.sql +SELECT update_images_cache(NULL); diff --git a/util/updates/2021-03-02-reviews-modnote.sql b/util/updates/2021-03-02-reviews-modnote.sql new file mode 100644 index 00000000..f6313303 --- /dev/null +++ b/util/updates/2021-03-02-reviews-modnote.sql @@ -0,0 +1,4 @@ +ALTER TABLE reviews ADD COLUMN modnote text NOT NULL DEFAULT ''; + +-- Not sure why NULL was allowed for the text column, let's fix that while we're here. +ALTER TABLE reviews ALTER COLUMN text SET NOT NULL; diff --git a/util/updates/2021-03-04-releases-minage.sql b/util/updates/2021-03-04-releases-minage.sql new file mode 100644 index 00000000..c4eb0bb4 --- /dev/null +++ b/util/updates/2021-03-04-releases-minage.sql @@ -0,0 +1,2 @@ +UPDATE releases SET minage = NULL WHERE minage = -1; +UPDATE releases_hist SET minage = NULL WHERE minage = -1; diff --git a/util/updates/2021-03-06-medium-cassette-tape.sql b/util/updates/2021-03-06-medium-cassette-tape.sql new file mode 100644 index 00000000..370a293d --- /dev/null +++ b/util/updates/2021-03-06-medium-cassette-tape.sql @@ -0,0 +1 @@ +ALTER TYPE medium ADD VALUE 'cas' AFTER 'flp'; diff --git a/util/updates/2021-03-07-platforms.sql b/util/updates/2021-03-07-platforms.sql new file mode 100644 index 00000000..a0d19533 --- /dev/null +++ b/util/updates/2021-03-07-platforms.sql @@ -0,0 +1,9 @@ +ALTER TYPE platform ADD VALUE 'tdo' BEFORE 'oth'; +ALTER TYPE platform ADD VALUE 'fm7' BEFORE 'fmt'; +ALTER TYPE platform ADD VALUE 'fm8' BEFORE 'fmt'; +ALTER TYPE platform ADD VALUE 'ps5' BEFORE 'psv'; +ALTER TYPE platform ADD VALUE 'smd' BEFORE 'sat'; +ALTER TYPE platform ADD VALUE 'scd' BEFORE 'sat'; +ALTER TYPE platform ADD VALUE 'x1s' BEFORE 'x68'; +ALTER TYPE platform ADD VALUE 'vnd' AFTER 'n3d'; +ALTER TYPE platform ADD VALUE 'xxs' AFTER 'xbo'; diff --git a/util/updates/2021-03-11-platform-mobile.sql b/util/updates/2021-03-11-platform-mobile.sql new file mode 100644 index 00000000..cf062b4a --- /dev/null +++ b/util/updates/2021-03-11-platform-mobile.sql @@ -0,0 +1 @@ +ALTER TYPE platform ADD VALUE 'mob' BEFORE 'oth'; diff --git a/util/updates/2021-03-11-tag-history.sql b/util/updates/2021-03-11-tag-history.sql new file mode 100644 index 00000000..ddbdd674 --- /dev/null +++ b/util/updates/2021-03-11-tag-history.sql @@ -0,0 +1,89 @@ +BEGIN; + +-- 'deleted' state is now represented as (hidden && locked) +-- (hidden && !locked) now means 'awaiting moderation' +UPDATE vn SET locked = true WHERE hidden AND NOT locked; +UPDATE producers SET locked = true WHERE hidden AND NOT locked; +UPDATE staff SET locked = true WHERE hidden AND NOT locked; +UPDATE chars SET locked = true WHERE hidden AND NOT locked; +UPDATE releases SET locked = true WHERE hidden AND NOT locked; +UPDATE docs SET locked = true WHERE hidden AND NOT locked; +UPDATE changes SET ilock = true WHERE ihid AND NOT ilock; + +ALTER TABLE tags_aliases DROP CONSTRAINT tags_aliases_tag_fkey; +ALTER TABLE tags_parents DROP CONSTRAINT tags_parents_tag_fkey; +ALTER TABLE tags_parents DROP CONSTRAINT tags_parents_parent_fkey; +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_tag_fkey; + +DROP TRIGGER insert_notify ON tags; +DROP TRIGGER stats_cache_new ON tags; +DROP TRIGGER stats_cache_edit ON tags; + +-- Move tags_alias into tags as 'alias' column, to be consistent with how aliases are stored for traits. +-- No real need to enforce uniqueness on aliasses as they're just search helpers. +ALTER TABLE tags ADD COLUMN alias varchar(500) NOT NULL DEFAULT ''; +UPDATE tags SET alias = COALESCE((SELECT string_agg(alias, E'\n') FROM tags_aliases WHERE tag = tags.id), ''); +DROP TABLE tags_aliases; + +ALTER TABLE tags ALTER COLUMN name SET DEFAULT ''; + +-- State -> hidden,locked +ALTER TABLE tags ADD COLUMN hidden boolean NOT NULL DEFAULT FALSE; +ALTER TABLE tags ADD COLUMN locked boolean NOT NULL DEFAULT TRUE; +UPDATE tags SET hidden = (state <> 2), locked = (state = 1); +ALTER TABLE tags DROP COLUMN state; + +-- id -> vndbid +ALTER TABLE tags ALTER COLUMN id DROP DEFAULT; +ALTER TABLE tags ALTER COLUMN id TYPE vndbid USING vndbid('g', id); +ALTER TABLE tags ALTER COLUMN id SET DEFAULT vndbid('g', nextval('tags_id_seq')::int); +ALTER TABLE tags ADD CONSTRAINT tags_id_check CHECK(vndbid_type(id) = 'g'); + +ALTER TABLE tags_parents RENAME COLUMN tag TO id; +ALTER TABLE tags_parents ALTER COLUMN id TYPE vndbid USING vndbid('g', id); +ALTER TABLE tags_parents ALTER COLUMN parent TYPE vndbid USING vndbid('g', parent); + + +CREATE TABLE tags_hist ( + chid integer NOT NULL PRIMARY KEY, + cat tag_category NOT NULL DEFAULT 'cont', + defaultspoil smallint NOT NULL DEFAULT 0, + searchable boolean NOT NULL DEFAULT TRUE, + applicable boolean NOT NULL DEFAULT TRUE, + name varchar(250) NOT NULL DEFAULT '', + description text NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '' +); + +CREATE TABLE tags_parents_hist ( + chid integer NOT NULL, + parent vndbid NOT NULL, + PRIMARY KEY(chid, parent) +); + +ALTER TABLE tags_vn ALTER COLUMN tag TYPE vndbid USING vndbid('g', tag); +ALTER TABLE tags_vn_inherit ALTER COLUMN tag TYPE vndbid USING vndbid('g', tag); + +INSERT INTO changes (requester,itemid,rev,ihid,ilock,comments) + SELECT 'u1', id, 1, hidden, locked, +'Automated import from when the tag database did not keep track of change histories. +This tag was initially submitted by '||coalesce(nullif(addedby::text, 'u1'), 'an anonymous user')||' on '||added::date||', but has no doubt been updated over time by moderators.' + FROM tags; + +INSERT INTO tags_hist (chid, cat, defaultspoil, searchable, applicable, name, description, alias) + SELECT c.id, t.cat, t.defaultspoil, t.searchable, t.applicable, t.name, t.description, t.alias + FROM tags t JOIN changes c ON c.itemid = t.id; + +INSERT INTO tags_parents_hist (chid, parent) SELECT c.id, t.parent FROM tags_parents t JOIN changes c ON c.itemid = t.id; + +ALTER TABLE tags DROP COLUMN addedby; + + +\i sql/func.sql +\i sql/editfunc.sql + +COMMIT; + +\i sql/tableattrs.sql +\i sql/triggers.sql +\i sql/perms.sql diff --git a/util/updates/2021-03-16-release-dlsiteen.sql b/util/updates/2021-03-16-release-dlsiteen.sql new file mode 100644 index 00000000..a3a65a50 --- /dev/null +++ b/util/updates/2021-03-16-release-dlsiteen.sql @@ -0,0 +1,16 @@ +-- Create a temporary copy of the DLsite English shop status information in case we want to revert. +CREATE TABLE shop_dlsiteen_old AS SELECT * FROM shop_dlsite WHERE id LIKE 'RE%'; +DELETE FROM shop_dlsite WHERE id LIKE 'RE%'; + +CREATE OR REPLACE FUNCTION migrate_dlsiteen_to_dlsite(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET l_dlsite = regexp_replace(l_dlsiteen, '^RE', 'RJ'); + UPDATE edit_revision SET requester = 'u1', ip = '0.0.0.0', comments = 'DLsite English has been merged into the main DLsite, automatically migrating shop link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_dlsiteen_to_dlsite(id) FROM releases + WHERE NOT hidden AND l_dlsite = '' AND l_dlsiteen <> '' + AND NOT EXISTS(SELECT 1 FROM shop_dlsite WHERE id = l_dlsiteen AND deadsince < NOW()-'7 days'::interval); +DROP FUNCTION migrate_dlsiteen_to_dlsite(vndbid); diff --git a/util/updates/2021-03-23-trait-history.sql b/util/updates/2021-03-23-trait-history.sql new file mode 100644 index 00000000..a940799f --- /dev/null +++ b/util/updates/2021-03-23-trait-history.sql @@ -0,0 +1,74 @@ +BEGIN; + +ALTER TABLE chars_traits DROP CONSTRAINT chars_traits_tid_fkey; +ALTER TABLE chars_traits_hist DROP CONSTRAINT chars_traits_hist_tid_fkey; +ALTER TABLE traits DROP CONSTRAINT traits_group_fkey; +ALTER TABLE traits_parents DROP CONSTRAINT traits_parents_trait_fkey; +ALTER TABLE traits_parents DROP CONSTRAINT traits_parents_parent_fkey; + +DROP TRIGGER insert_notify ON traits; +DROP TRIGGER stats_cache_new ON traits; +DROP TRIGGER stats_cache_edit ON traits; + +ALTER TABLE traits ADD COLUMN hidden boolean NOT NULL DEFAULT FALSE; +ALTER TABLE traits ADD COLUMN locked boolean NOT NULL DEFAULT TRUE; +UPDATE traits SET hidden = (state <> 2), locked = (state = 1); +ALTER TABLE traits DROP COLUMN state; + +ALTER TABLE traits ALTER COLUMN id DROP DEFAULT; +ALTER TABLE traits ALTER COLUMN id TYPE vndbid USING vndbid('i', id); +ALTER TABLE traits ALTER COLUMN id SET DEFAULT vndbid('i', nextval('traits_id_seq')::int); +ALTER TABLE traits ADD CONSTRAINT traits_id_check CHECK(vndbid_type(id) = 'i'); + +ALTER TABLE traits ALTER COLUMN "group" TYPE vndbid USING vndbid('i', "group"); +ALTER TABLE traits ALTER COLUMN name SET DEFAULT ''; + +ALTER TABLE traits_parents RENAME COLUMN trait TO id; +ALTER TABLE traits_parents ALTER COLUMN id TYPE vndbid USING vndbid('i', id); +ALTER TABLE traits_parents ALTER COLUMN parent TYPE vndbid USING vndbid('i', parent); + +ALTER TABLE traits_chars ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid); +ALTER TABLE chars_traits ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid); +ALTER TABLE chars_traits_hist ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid); + +CREATE TABLE traits_hist ( + chid integer NOT NULL, + "order" smallint NOT NULL DEFAULT 0, + defaultspoil smallint NOT NULL DEFAULT 0, + sexual boolean NOT NULL DEFAULT false, + searchable boolean NOT NULL DEFAULT true, + applicable boolean NOT NULL DEFAULT true, + name varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + description text NOT NULL DEFAULT '' +); + +CREATE TABLE traits_parents_hist ( + chid integer NOT NULL, + parent vndbid NOT NULL, + PRIMARY KEY(chid, parent) +); + + +INSERT INTO changes (requester,itemid,rev,ihid,ilock,comments) + SELECT 'u1', id, 1, hidden, locked, +'Automated import from when the trait database did not keep track of change histories. +This trait was initially submitted by '||coalesce(nullif(addedby::text, 'u1'), 'an anonymous user')||' on '||added::date||', but has no doubt been updated over time by moderators.' + FROM traits; + +INSERT INTO traits_hist (chid, "order", defaultspoil, sexual, searchable, applicable, name, description, alias) + SELECT c.id, t."order", t.defaultspoil, t.sexual, t.searchable, t.applicable, t.name, t.description, t.alias + FROM traits t JOIN changes c ON c.itemid = t.id; + +INSERT INTO traits_parents_hist (chid, parent) SELECT c.id, t.parent FROM traits_parents t JOIN changes c ON c.itemid = t.id; + +ALTER TABLE traits DROP COLUMN addedby; + +\i sql/func.sql +\i sql/editfunc.sql + +COMMIT; + +\i sql/tableattrs.sql +\i sql/triggers.sql +\i sql/perms.sql diff --git a/util/updates/2021-04-09-item-info.sql b/util/updates/2021-04-09-item-info.sql new file mode 100644 index 00000000..22728127 --- /dev/null +++ b/util/updates/2021-04-09-item-info.sql @@ -0,0 +1,2 @@ +DROP FUNCTION item_info(vndbid,int); +\i sql/func.sql diff --git a/util/updates/2021-05-05-latin-language.sql b/util/updates/2021-05-05-latin-language.sql new file mode 100644 index 00000000..7612430e --- /dev/null +++ b/util/updates/2021-05-05-latin-language.sql @@ -0,0 +1 @@ +ALTER TYPE language ADD VALUE 'la' AFTER 'ms'; diff --git a/util/updates/2021-05-14-releases-lang-mtl.sql b/util/updates/2021-05-14-releases-lang-mtl.sql new file mode 100644 index 00000000..43723117 --- /dev/null +++ b/util/updates/2021-05-14-releases-lang-mtl.sql @@ -0,0 +1,4 @@ +ALTER TABLE releases_lang ADD COLUMN mtl boolean NOT NULL DEFAULT FALSE; +ALTER TABLE releases_lang_hist ADD COLUMN mtl boolean NOT NULL DEFAULT FALSE; +\i sql/editfunc.sql +\i sql/func.sql diff --git a/util/updates/2021-05-21-tt-primary-parent.sql b/util/updates/2021-05-21-tt-primary-parent.sql new file mode 100644 index 00000000..00f513a4 --- /dev/null +++ b/util/updates/2021-05-21-tt-primary-parent.sql @@ -0,0 +1,17 @@ +ALTER TABLE tags_parents ADD COLUMN main boolean NOT NULL DEFAULT false; +ALTER TABLE tags_parents_hist ADD COLUMN main boolean NOT NULL DEFAULT false; +ALTER TABLE traits_parents ADD COLUMN main boolean NOT NULL DEFAULT false; +ALTER TABLE traits_parents_hist ADD COLUMN main boolean NOT NULL DEFAULT false; +\i sql/editfunc.sql + +UPDATE tags_parents tp SET main = true WHERE NOT EXISTS(SELECT 1 FROM tags_parents tp2 WHERE tp2.id = tp.id AND tp2.parent < tp.parent); +UPDATE tags_parents_hist tp SET main = true WHERE NOT EXISTS(SELECT 1 FROM tags_parents_hist tp2 WHERE tp2.chid = tp.chid AND tp2.parent < tp.parent); +UPDATE traits_parents tp SET main = true WHERE NOT EXISTS(SELECT 1 FROM traits_parents tp2 WHERE tp2.id = tp.id AND tp2.parent < tp.parent); +UPDATE traits_parents_hist tp SET main = true WHERE NOT EXISTS(SELECT 1 FROM traits_parents_hist tp2 WHERE tp2.chid = tp.chid AND tp2.parent < tp.parent); + +-- Update the traits.group cache for consistency with the above selected 'main' flags. +WITH RECURSIVE childs (id, grp) AS ( + SELECT id, id FROM traits t WHERE NOT EXISTS(SELECT 1 FROM traits_parents tp WHERE tp.id = t.id) + UNION ALL + SELECT tp.id, childs.grp FROM childs JOIN traits_parents tp ON tp.parent = childs.id AND tp.main +) UPDATE traits SET "group" = grp FROM childs WHERE childs.id = traits.id AND "group" IS DISTINCT FROM grp AND grp <> childs.id; diff --git a/util/updates/2021-05-25-users-shadow.sql b/util/updates/2021-05-25-users-shadow.sql new file mode 100644 index 00000000..bf48d0af --- /dev/null +++ b/util/updates/2021-05-25-users-shadow.sql @@ -0,0 +1,19 @@ +CREATE TABLE users_shadow ( + id vndbid NOT NULL PRIMARY KEY, + perm_usermod boolean NOT NULL DEFAULT false, + mail varchar(100) NOT NULL, + passwd bytea NOT NULL DEFAULT '' +); + +BEGIN; +INSERT INTO users_shadow SELECT id, perm_usermod, mail, passwd FROM users; + +ALTER TABLE users_shadow ADD CONSTRAINT users_shadow_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE users DROP COLUMN perm_usermod; +ALTER TABLE users DROP COLUMN mail; +ALTER TABLE users DROP COLUMN passwd; +COMMIT; + +\i sql/perms.sql +\i sql/func.sql diff --git a/util/updates/2021-05-25-users-vnlang.sql b/util/updates/2021-05-25-users-vnlang.sql new file mode 100644 index 00000000..d480c60a --- /dev/null +++ b/util/updates/2021-05-25-users-vnlang.sql @@ -0,0 +1 @@ +ALTER TABLE users ADD COLUMN vnlang jsonb; diff --git a/util/updates/2021-06-04-vn-developers-and-average-cache.sql b/util/updates/2021-06-04-vn-developers-and-average-cache.sql new file mode 100644 index 00000000..4fc6a510 --- /dev/null +++ b/util/updates/2021-06-04-vn-developers-and-average-cache.sql @@ -0,0 +1,11 @@ +ALTER TABLE users ADD COLUMN tableopts_v integer; +ALTER TABLE users ADD COLUMN tableopts_vt integer; + +ALTER TABLE vn ADD COLUMN c_developers vndbid[] NOT NULL DEFAULT '{}'; +ALTER TABLE vn ADD COLUMN c_average smallint; +ALTER TABLE vn ALTER COLUMN c_popularity TYPE smallint USING c_popularity*10000; +ALTER TABLE vn ALTER COLUMN c_rating TYPE smallint USING c_rating*10; +\i sql/func.sql +\timing +SELECT count(*) FROM (SELECT update_vncache(id) FROM vn) x; +SELECT update_vnvotestats(); diff --git a/util/updates/2021-06-22-indi-urdu-languages.sql b/util/updates/2021-06-22-indi-urdu-languages.sql new file mode 100644 index 00000000..9de77172 --- /dev/null +++ b/util/updates/2021-06-22-indi-urdu-languages.sql @@ -0,0 +1,2 @@ +ALTER TYPE language ADD VALUE 'hi' AFTER 'he'; +ALTER TYPE language ADD VALUE 'ur' AFTER 'uk'; diff --git a/util/updates/2021-06-28-lockdown-mode.sql b/util/updates/2021-06-28-lockdown-mode.sql new file mode 100644 index 00000000..d0b51cbe --- /dev/null +++ b/util/updates/2021-06-28-lockdown-mode.sql @@ -0,0 +1,13 @@ +CREATE TABLE global_settings ( + -- Only permit a single row in this table + id boolean NOT NULL PRIMARY KEY DEFAULT FALSE CONSTRAINT global_settings_single_row CHECK(id), + -- locks down any DB edits, including image voting and tagging + lockdown_edit boolean NOT NULL DEFAULT FALSE, + -- locks down any forum & review posting + lockdown_board boolean NOT NULL DEFAULT FALSE, + lockdown_registration boolean NOT NULL DEFAULT FALSE +); + +INSERT INTO global_settings (id) VALUES (TRUE); + +\i sql/perms.sql diff --git a/util/updates/2021-07-24-more-wikidata-ids.sql b/util/updates/2021-07-24-more-wikidata-ids.sql new file mode 100644 index 00000000..e5e80359 --- /dev/null +++ b/util/updates/2021-07-24-more-wikidata-ids.sql @@ -0,0 +1,3 @@ +ALTER TABLE wikidata ADD COLUMN soundcloud text[]; +ALTER TABLE wikidata ADD COLUMN humblestore text[]; +ALTER TABLE wikidata ADD COLUMN itchio text[]; diff --git a/util/updates/2021-07-28-merge-imgmod.sql b/util/updates/2021-07-28-merge-imgmod.sql new file mode 100644 index 00000000..eab67c41 --- /dev/null +++ b/util/updates/2021-07-28-merge-imgmod.sql @@ -0,0 +1,2 @@ +-- imgmod permissions merged into dbmod, no need to separate these. +ALTER TABLE users DROP COLUMN perm_imgmod; diff --git a/util/updates/2021-07-30-vn-length-voting.sql b/util/updates/2021-07-30-vn-length-voting.sql new file mode 100644 index 00000000..48dedb52 --- /dev/null +++ b/util/updates/2021-07-30-vn-length-voting.sql @@ -0,0 +1,17 @@ +CREATE TABLE vn_length_votes ( + vid vndbid NOT NULL, + rid vndbid NOT NULL, + date timestamptz NOT NULL DEFAULT NOW(), + uid vndbid, + length smallint NOT NULL, -- minutes + notes text NOT NULL DEFAULT '' +); +ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; +CREATE UNIQUE INDEX vn_length_votes_pkey ON vn_length_votes (vid, uid); + +-- DEFAULT false while it's in development. +ALTER TABLE users ADD COLUMN perm_lengthvote boolean NOT NULL DEFAULT false; + +\i sql/perms.sql diff --git a/util/updates/2021-08-03-vnlength-speed.sql b/util/updates/2021-08-03-vnlength-speed.sql new file mode 100644 index 00000000..f2809a59 --- /dev/null +++ b/util/updates/2021-08-03-vnlength-speed.sql @@ -0,0 +1,6 @@ +ALTER TABLE vn_length_votes ADD COLUMN speed smallint NOT NULL DEFAULT 0; +ALTER TABLE vn_length_votes ALTER COLUMN speed DROP DEFAULT; +ALTER TABLE vn_length_votes ADD COLUMN notes2 text NOT NULL DEFAULT ''; +UPDATE vn_length_votes SET notes2 = notes; +ALTER TABLE vn_length_votes DROP COLUMN notes; +ALTER TABLE vn_length_votes RENAME COLUMN notes2 TO notes; diff --git a/util/updates/2021-08-04-vnlength-index.sql b/util/updates/2021-08-04-vnlength-index.sql new file mode 100644 index 00000000..f9e93d01 --- /dev/null +++ b/util/updates/2021-08-04-vnlength-index.sql @@ -0,0 +1,2 @@ +ALTER TABLE users ALTER COLUMN perm_lengthvote SET DEFAULT true; +CREATE INDEX vn_length_votes_uid ON vn_length_votes (uid); diff --git a/util/updates/2021-08-08-lengthvote-ignore.sql b/util/updates/2021-08-08-lengthvote-ignore.sql new file mode 100644 index 00000000..594961d7 --- /dev/null +++ b/util/updates/2021-08-08-lengthvote-ignore.sql @@ -0,0 +1 @@ +ALTER TABLE vn_length_votes ADD COLUMN ignore boolean NOT NULL DEFAULT false; diff --git a/util/updates/2021-08-09-vnlength-multirelease.sql b/util/updates/2021-08-09-vnlength-multirelease.sql new file mode 100644 index 00000000..e5917f34 --- /dev/null +++ b/util/updates/2021-08-09-vnlength-multirelease.sql @@ -0,0 +1,4 @@ +ALTER TABLE vn_length_votes ADD COLUMN rid2 vndbid[] NOT NULL DEFAULT '{}'; +UPDATE vn_length_votes SET rid2 = ARRAY[rid]; +ALTER TABLE vn_length_votes DROP COLUMN rid; +ALTER TABLE vn_length_votes RENAME COLUMN rid2 TO rid; diff --git a/util/updates/2021-08-09b-vnlength-primarykey.sql b/util/updates/2021-08-09b-vnlength-primarykey.sql new file mode 100644 index 00000000..5bb1df32 --- /dev/null +++ b/util/updates/2021-08-09b-vnlength-primarykey.sql @@ -0,0 +1,28 @@ +-- Recreate the vn_length_votes table to cleanly add a primary key and for more efficient storage. +-- The table layout had gotten messy with all the recent edits. +BEGIN; +DROP INDEX vn_length_votes_pkey; +DROP INDEX vn_length_votes_uid; +ALTER TABLE vn_length_votes RENAME TO vn_length_votes_tmp; + +CREATE TABLE vn_length_votes ( + id SERIAL PRIMARY KEY, + vid vndbid NOT NULL, -- [pub] + date timestamptz NOT NULL DEFAULT NOW(), -- [pub] + length smallint NOT NULL, -- [pub] minutes + speed smallint NOT NULL, -- [pub] 0=slow, 1=normal, 2=fast + uid vndbid, -- [pub] + ignore boolean NOT NULL DEFAULT false, -- [pub] + rid vndbid[] NOT NULL, -- [pub] + notes text NOT NULL DEFAULT '' -- [pub] +); + +INSERT INTO vn_length_votes (vid,date,uid,length,speed,ignore,rid,notes) + SELECT vid,date,uid,length,speed,ignore,rid,notes FROM vn_length_votes_tmp; + +CREATE UNIQUE INDEX vn_length_votes_vid_uid ON vn_length_votes (vid, uid); +CREATE INDEX vn_length_votes_uid ON vn_length_votes (uid); +ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; +COMMIT; +\i sql/perms.sql diff --git a/util/updates/2021-09-02-some-foreign-key-stuff.sql b/util/updates/2021-09-02-some-foreign-key-stuff.sql new file mode 100644 index 00000000..09abff70 --- /dev/null +++ b/util/updates/2021-09-02-some-foreign-key-stuff.sql @@ -0,0 +1,5 @@ +-- Add an ON UPDATE CASCADE clause to these contraints to simplify moving lists across users or VNs. +ALTER TABLE ulist_vns_labels DROP CONSTRAINT ulist_vns_labels_uid_lbl_fkey; +ALTER TABLE ulist_vns_labels DROP CONSTRAINT ulist_vns_labels_uid_vid_fkey; +ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/util/updates/2021-09-26-vn-length-cache.sql b/util/updates/2021-09-26-vn-length-cache.sql new file mode 100644 index 00000000..40dfa0a0 --- /dev/null +++ b/util/updates/2021-09-26-vn-length-cache.sql @@ -0,0 +1,6 @@ +ALTER TABLE vn ADD COLUMN c_length smallint; +ALTER TABLE vn ADD COLUMN c_lengthnum smallint NOT NULL DEFAULT 0; + +\i sql/func.sql +\i sql/triggers.sql +select update_vn_length_cache(null); diff --git a/util/updates/2021-10-27-freegame-mugen.sql b/util/updates/2021-10-27-freegame-mugen.sql new file mode 100644 index 00000000..cc3f487b --- /dev/null +++ b/util/updates/2021-10-27-freegame-mugen.sql @@ -0,0 +1,3 @@ +ALTER TABLE releases ADD COLUMN l_freegame text NOT NULL DEFAULT ''; +ALTER TABLE releases_hist ADD COLUMN l_freegame text NOT NULL DEFAULT ''; +\i sql/editfunc.sql diff --git a/util/updates/2021-10-28-username-casefold.sql b/util/updates/2021-10-28-username-casefold.sql new file mode 100644 index 00000000..88bc1238 --- /dev/null +++ b/util/updates/2021-10-28-username-casefold.sql @@ -0,0 +1,2 @@ +ALTER TABLE users DROP CONSTRAINT users_username_key; +CREATE UNIQUE INDEX users_username_key ON users (lower(username)); diff --git a/util/updates/2021-10-28-username-history.sql b/util/updates/2021-10-28-username-history.sql new file mode 100644 index 00000000..ac703fc8 --- /dev/null +++ b/util/updates/2021-10-28-username-history.sql @@ -0,0 +1,16 @@ +CREATE TABLE users_username_hist ( + id vndbid NOT NULL, + date timestamptz NOT NULL DEFAULT NOW(), + old text NOT NULL, + new text NOT NULL, + PRIMARY KEY(id, date) +); +ALTER TABLE users_username_hist ADD CONSTRAINT users_username_hist_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE; +\i sql/perms.sql + +INSERT INTO users_username_hist (id, date, old, new) + SELECT affected_uid, date + , regexp_replace(detail, 'username: "([^"]+)" -> "([^"]+)"', '\1', '') AS old + , regexp_replace(detail, 'username: "([^"]+)" -> "([^"]+)"', '\2', '') AS new + FROM audit_log + WHERE detail ~ 'username: "([^"]+)" -> "([^"]+)"' AND EXISTS(SELECT 1 FROM users WHERE id = affected_uid); diff --git a/util/updates/2021-10-28-website-length.sql b/util/updates/2021-10-28-website-length.sql new file mode 100644 index 00000000..a666e05f --- /dev/null +++ b/util/updates/2021-10-28-website-length.sql @@ -0,0 +1,4 @@ +ALTER TABLE producers ALTER COLUMN website TYPE varchar(1024); +ALTER TABLE producers_hist ALTER COLUMN website TYPE varchar(1024); +ALTER TABLE releases ALTER COLUMN website TYPE varchar(1024); +ALTER TABLE releases_hist ALTER COLUMN website TYPE varchar(1024); diff --git a/util/updates/2021-10-29-fix-thumbnail-resolution.pl b/util/updates/2021-10-29-fix-thumbnail-resolution.pl new file mode 100755 index 00000000..8da530f7 --- /dev/null +++ b/util/updates/2021-10-29-fix-thumbnail-resolution.pl @@ -0,0 +1,50 @@ +#!/usr/bin/perl + +use v5.26; +use warnings; +use Cwd 'abs_path'; +use lib ((abs_path $0) =~ s{/\Q$0\E$}{}r).'/lib'; + +use VNDB::Func 'imgsize', 'imgpath'; +use VNDB::Config; +use VNWeb::DB; +use TUWF; + +TUWF::set %{ config->{tuwf} }; + +sub jpgsize { + my($f) = @_; + my $id = config->{identify_path}; + return split 'x', `$id -format "%wx%h" "$f"`; + + use bytes; + open my $F, '<', $f or die "$f: $!"; + die "$f: $!" if 1 > read $F, my $buf, 16*1024; + die "$f: Not a JPEG\n" if $buf !~ /\xFF[\xC0\xC2]...(....)/s; + my($h,$w) = unpack 'nn', $1; + return ($w,$h); +} + +for (tuwf->dbAlli('SELECT id, width, height FROM images WHERE id BETWEEN \'sf1\' AND vndbid_max(\'sf\')')->@*) { + my $fullpath = imgpath $_->{id}; + my $thumbpath = imgpath $_->{id}, 1; + next if !$_->{width} || !-s $fullpath; + my ($thumbw, $thumbh) = imgsize $_->{width}, $_->{height}, config->{scr_size}->@*; + my ($filew, $fileh) = jpgsize $thumbpath; + if($filew != $thumbw || $fileh != $thumbh) { + warn "$thumbpath: dimensions don't match, recreating; file=${filew}x$fileh expected=${thumbw}x$thumbh\n"; + my $conv = config->{convert_path}; + my $resize = config->{scr_size}[0].'x'.config->{scr_size}[1].'>'; + unlink 'tmpimg.jpg'; + my ($neww, $newh) = split /x/, `$conv "$fullpath" -strip -quality 90 -resize "$resize" -unsharp 0x0.75+0.75+0.008 -print %wx%h tmpimg.jpg`; + if(!$neww || !$newh) { + warn "$thumbpath: unable to write new image\n"; + next; + } + if($neww != $thumbw || $newh != $thumbh) { + warn "$thumbpath: new thumbnail doesn't match expected dimensions, got ${neww}x$newh instead.\n"; + next; + } + rename 'tmpimg.jpg', $thumbpath; + } +} diff --git a/util/updates/2021-11-07-posts-hidden-msg.sql b/util/updates/2021-11-07-posts-hidden-msg.sql new file mode 100644 index 00000000..878ae0ab --- /dev/null +++ b/util/updates/2021-11-07-posts-hidden-msg.sql @@ -0,0 +1,17 @@ +BEGIN; +ALTER TABLE threads_posts + DROP CONSTRAINT threads_posts_first_nonhidden, + ALTER COLUMN hidden DROP NOT NULL, + ALTER COLUMN hidden DROP DEFAULT, + ALTER COLUMN hidden TYPE text USING case when hidden then '' else null end, + ADD CONSTRAINT threads_posts_first_nonhidden CHECK(num > 1 OR hidden IS NULL); + +ALTER TABLE reviews_posts + ALTER COLUMN hidden DROP NOT NULL, + ALTER COLUMN hidden DROP DEFAULT, + ALTER COLUMN hidden TYPE text USING case when hidden then '' else null end; + +\i sql/func.sql +COMMIT; + +\i sql/triggers.sql diff --git a/util/updates/2021-11-07-threads-board-lock.sql b/util/updates/2021-11-07-threads-board-lock.sql new file mode 100644 index 00000000..6b25a187 --- /dev/null +++ b/util/updates/2021-11-07-threads-board-lock.sql @@ -0,0 +1 @@ +ALTER TABLE threads ADD COLUMN boards_locked boolean NOT NULL DEFAULT FALSE; diff --git a/util/updates/2021-11-15-release-vn-type.sql b/util/updates/2021-11-15-release-vn-type.sql new file mode 100644 index 00000000..54916086 --- /dev/null +++ b/util/updates/2021-11-15-release-vn-type.sql @@ -0,0 +1,12 @@ +BEGIN; +ALTER TABLE releases_vn ADD COLUMN rtype release_type NOT NULL DEFAULT 'complete'; +ALTER TABLE releases_vn_hist ADD COLUMN rtype release_type NOT NULL DEFAULT 'complete'; +ALTER TABLE releases_vn ALTER COLUMN rtype DROP DEFAULT; +ALTER TABLE releases_vn_hist ALTER COLUMN rtype DROP DEFAULT; +UPDATE releases_vn SET rtype = type FROM releases r WHERE r.id = releases_vn.id; +UPDATE releases_vn_hist SET rtype = type FROM releases_hist r WHERE r.chid = releases_vn_hist.chid; +ALTER TABLE releases DROP COLUMN type; +ALTER TABLE releases_hist DROP COLUMN type; +\i sql/editfunc.sql +\i sql/func.sql +COMMIT; diff --git a/util/updates/2021-11-15-reviews-fulltext-search.sql b/util/updates/2021-11-15-reviews-fulltext-search.sql new file mode 100644 index 00000000..c6f60211 --- /dev/null +++ b/util/updates/2021-11-15-reviews-fulltext-search.sql @@ -0,0 +1,2 @@ +CREATE INDEX reviews_ts ON reviews USING gin(bb_tsvector(text)); +CREATE INDEX reviews_posts_ts ON reviews_posts USING gin(bb_tsvector(msg)); 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; diff --git a/util/updates/2021-11-19-more-search.sql b/util/updates/2021-11-19-more-search.sql new file mode 100644 index 00000000..5b6a99b0 --- /dev/null +++ b/util/updates/2021-11-19-more-search.sql @@ -0,0 +1,9 @@ +BEGIN; +\i sql/func.sql +ALTER TABLE releases DROP COLUMN c_search; +DROP FUNCTION search_gen(boolean,text[]); +ALTER TABLE releases ADD COLUMN c_search text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[title, original])) STORED; +ALTER TABLE producers ADD COLUMN c_search text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name, original]::text[]||string_to_array(alias,E'\n'))) STORED; +ALTER TABLE chars ADD COLUMN c_search text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name, original]::text[]||string_to_array(alias,E'\n'))) STORED; +ALTER TABLE staff_alias ADD COLUMN c_search text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name, original])) STORED; +COMMIT; diff --git a/util/updates/2021-11-19-vn-search.sql b/util/updates/2021-11-19-vn-search.sql new file mode 100644 index 00000000..56ce6661 --- /dev/null +++ b/util/updates/2021-11-19-vn-search.sql @@ -0,0 +1,7 @@ +DROP TRIGGER vn_vnsearch_notify ON vn; +DROP FUNCTION vn_vnsearch_notify(); +\i sql/func.sql + +-- Warning: slow +\timing +UPDATE vn SET c_search = search_gen_vn(id); diff --git a/util/updates/2021-11-24-tagtrait-search.sql b/util/updates/2021-11-24-tagtrait-search.sql new file mode 100644 index 00000000..7e4aaf50 --- /dev/null +++ b/util/updates/2021-11-24-tagtrait-search.sql @@ -0,0 +1,2 @@ +ALTER TABLE tags ADD COLUMN c_search text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name]::text[]||string_to_array(alias,E'\n'))) STORED; +ALTER TABLE traits ADD COLUMN c_search text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name]::text[]||string_to_array(alias,E'\n'))) STORED; diff --git a/util/updates/2021-11-29-release-unknown-uncensored.sql b/util/updates/2021-11-29-release-unknown-uncensored.sql new file mode 100644 index 00000000..a3db3873 --- /dev/null +++ b/util/updates/2021-11-29-release-unknown-uncensored.sql @@ -0,0 +1,5 @@ +ALTER TABLE releases ALTER COLUMN uncensored DROP NOT NULL, ALTER COLUMN uncensored DROP DEFAULT; +ALTER TABLE releases_hist ALTER COLUMN uncensored DROP NOT NULL, ALTER COLUMN uncensored DROP DEFAULT; +\i sql/editfunc.sql +UPDATE releases SET uncensored = NULL WHERE minage <> 18; +UPDATE releases_hist SET uncensored = NULL WHERE minage <> 18; diff --git a/util/updates/2021-12-06-extlinks-playstation-stores.sql b/util/updates/2021-12-06-extlinks-playstation-stores.sql new file mode 100644 index 00000000..648fb74d --- /dev/null +++ b/util/updates/2021-12-06-extlinks-playstation-stores.sql @@ -0,0 +1,13 @@ +ALTER TABLE releases + ADD COLUMN l_playstation_jp text NOT NULL DEFAULT '', + ADD COLUMN l_playstation_na text NOT NULL DEFAULT '', + ADD COLUMN l_playstation_eu text NOT NULL DEFAULT ''; +ALTER TABLE releases_hist + ADD COLUMN l_playstation_jp text NOT NULL DEFAULT '', + ADD COLUMN l_playstation_na text NOT NULL DEFAULT '', + ADD COLUMN l_playstation_eu text NOT NULL DEFAULT ''; +ALTER TABLE wikidata + ADD COLUMN playstation_jp text[], + ADD COLUMN playstation_na text[], + ADD COLUMN playstation_eu text[]; +\i sql/editfunc.sql diff --git a/util/updates/2021-12-15-api-sessions.sql b/util/updates/2021-12-15-api-sessions.sql new file mode 100644 index 00000000..005fdb52 --- /dev/null +++ b/util/updates/2021-12-15-api-sessions.sql @@ -0,0 +1,3 @@ +ALTER TYPE session_type ADD VALUE 'api'; +DROP FUNCTION user_login(vndbid, bytea, bytea); +\i sql/func.sql diff --git a/util/updates/2022-02-05-popularity-non-null.sql b/util/updates/2022-02-05-popularity-non-null.sql new file mode 100644 index 00000000..238d7867 --- /dev/null +++ b/util/updates/2022-02-05-popularity-non-null.sql @@ -0,0 +1,7 @@ +\i sql/func.sql +SELECT update_vnvotestats(); +ALTER TABLE vn + ALTER COLUMN c_popularity SET NOT NULL, + ALTER COLUMN c_pop_rank SET NOT NULL, + ALTER COLUMN c_popularity SET DEFAULT 0, + ALTER COLUMN c_pop_rank SET DEFAULT 0; diff --git a/util/updates/2022-02-11-vn-titles.sql b/util/updates/2022-02-11-vn-titles.sql new file mode 100644 index 00000000..9332c2c4 --- /dev/null +++ b/util/updates/2022-02-11-vn-titles.sql @@ -0,0 +1,41 @@ +BEGIN; + +CREATE TABLE vn_titles ( + id vndbid NOT NULL, + lang language NOT NULL, + title text NOT NULL, + latin text, + official boolean NOT NULL, + PRIMARY KEY(id, lang) +); + +CREATE TABLE vn_titles_hist ( + chid integer NOT NULL, + lang language NOT NULL, + title text NOT NULL, + latin text, + official boolean NOT NULL, + PRIMARY KEY(chid, lang) +); + +INSERT INTO vn_titles SELECT id, olang, CASE WHEN original = '' THEN title ELSE original END, CASE WHEN original = '' THEN NULL ELSE title END, true FROM vn; +INSERT INTO vn_titles_hist SELECT chid, olang, CASE WHEN original = '' THEN title ELSE original END, CASE WHEN original = '' THEN NULL ELSE title END, true FROM vn_hist; + +ALTER TABLE vn_titles ADD CONSTRAINT vn_titles_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_titles_hist ADD CONSTRAINT vn_titles_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn ADD CONSTRAINT vn_olang_fkey FOREIGN KEY (id,olang) REFERENCES vn_titles (id,lang) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_olang_fkey FOREIGN KEY (chid,olang)REFERENCES vn_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE vn DROP COLUMN original +ALTER TABLE vn DROP COLUMN title; +ALTER TABLE vn_hist DROP COLUMN original +ALTER TABLE vn_hist DROP COLUMN title; + +CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang; + +ALTER TABLE users ADD COLUMN title_langs jsonb, ADD COLUMN alttitle_langs jsonb; + +COMMIT; +\i sql/func.sql +\i sql/editfunc.sql +\i sql/perms.sql diff --git a/util/updates/2022-02-12-chinese-languages.sql b/util/updates/2022-02-12-chinese-languages.sql new file mode 100644 index 00000000..330d9224 --- /dev/null +++ b/util/updates/2022-02-12-chinese-languages.sql @@ -0,0 +1,30 @@ +ALTER TYPE language ADD VALUE 'zh-Hans' AFTER 'zh'; +ALTER TYPE language ADD VALUE 'zh-Hant' AFTER 'zh-Hans'; + + +CREATE OR REPLACE FUNCTION migrate_notes_to_lang(rid vndbid, rlang language) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases_lang SET lang = rlang WHERE lang = 'zh'; + UPDATE edit_releases SET notes = regexp_replace(notes, '\s*(Simplified|Traditional) Chinese\.?\s*', '', 'i'); + UPDATE edit_revision SET requester = 'u1', ip = '0.0.0.0', comments = 'Automatic extraction of Chinese language from the notes.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; + +SELECT COUNT(*) FROM (SELECT migrate_notes_to_lang(id, 'zh-Hans') +--SELECT 'http://whatever.blicky.net/'||r.id, regexp_replace(r.notes, '\s*Simplified Chinese\.?\s*', '', 'i') + FROM releases r WHERE NOT hidden + AND EXISTS(SELECT 1 FROM releases_lang rl WHERE rl.id = r.id AND rl.lang = 'zh') + AND NOT EXISTS(SELECT 1 FROM releases_lang rl WHERE rl.id = r.id AND rl.lang IN('zh-Hans', 'zh-Hant')) + AND notes ~* '(^|\n)Simplified Chinese(\.|\n|$)' +) x; + +SELECT COUNT(*) FROM (SELECT migrate_notes_to_lang(id, 'zh-Hant') + FROM releases r WHERE NOT hidden + AND EXISTS(SELECT 1 FROM releases_lang rl WHERE rl.id = r.id AND rl.lang = 'zh') + AND NOT EXISTS(SELECT 1 FROM releases_lang rl WHERE rl.id = r.id AND rl.lang IN('zh-Hans', 'zh-Hant')) + AND notes ~* '(^|\n)Traditional Chinese(\.|\n|$)' +) x; + +DROP FUNCTION migrate_notes_to_lang(vndbid, language); diff --git a/util/updates/2022-02-19-vnt-sorttitle.sql b/util/updates/2022-02-19-vnt-sorttitle.sql new file mode 100644 index 00000000..189b19fe --- /dev/null +++ b/util/updates/2022-02-19-vnt-sorttitle.sql @@ -0,0 +1,3 @@ +DROP VIEW vnt; +CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, COALESCE(vo.latin, vo.title) AS sorttitle, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang; +\i sql/perms.sql diff --git a/util/updates/2022-03-23-vn-length-votes-uncounted.sql b/util/updates/2022-03-23-vn-length-votes-uncounted.sql new file mode 100644 index 00000000..fa24d44c --- /dev/null +++ b/util/updates/2022-03-23-vn-length-votes-uncounted.sql @@ -0,0 +1,6 @@ +BEGIN; +ALTER TABLE vn_length_votes ALTER COLUMN speed DROP NOT NULL; +UPDATE vn_length_votes SET speed = NULL WHERE ignore; +ALTER TABLE vn_length_votes DROP COLUMN ignore; +COMMIT; +\i sql/func.sql diff --git a/util/updates/2022-03-29-lengthvotes-private.sql b/util/updates/2022-03-29-lengthvotes-private.sql new file mode 100644 index 00000000..5c721818 --- /dev/null +++ b/util/updates/2022-03-29-lengthvotes-private.sql @@ -0,0 +1,3 @@ +ALTER TABLE vn_length_votes ADD COLUMN private boolean NOT NULL DEFAULT FALSE; +ALTER TABLE vn_length_votes ALTER COLUMN private DROP DEFAULT; +\i sql/func.sql diff --git a/util/updates/2022-03-29-release-animation.sql b/util/updates/2022-03-29-release-animation.sql new file mode 100644 index 00000000..cc6a5a20 --- /dev/null +++ b/util/updates/2022-03-29-release-animation.sql @@ -0,0 +1,29 @@ +BEGIN; + +CREATE DOMAIN animation AS smallint CHECK(value IS NULL OR value IN(0,1) OR ((value & (4+8+16+32)) > 0 AND (value & (256+512)) <> (256+512))); + +ALTER TABLE releases ADD COLUMN ani_story_sp animation; +ALTER TABLE releases ADD COLUMN ani_story_cg animation; +ALTER TABLE releases ADD COLUMN ani_cutscene animation; +ALTER TABLE releases ADD COLUMN ani_ero_sp animation; +ALTER TABLE releases ADD COLUMN ani_ero_cg animation; +ALTER TABLE releases ADD COLUMN ani_bg boolean; +ALTER TABLE releases ADD COLUMN ani_face boolean; + +ALTER TABLE releases_hist ADD COLUMN ani_story_sp animation; +ALTER TABLE releases_hist ADD COLUMN ani_story_cg animation; +ALTER TABLE releases_hist ADD COLUMN ani_cutscene animation; +ALTER TABLE releases_hist ADD COLUMN ani_ero_sp animation; +ALTER TABLE releases_hist ADD COLUMN ani_ero_cg animation; +ALTER TABLE releases_hist ADD COLUMN ani_bg boolean; +ALTER TABLE releases_hist ADD COLUMN ani_face boolean; + +UPDATE releases SET ani_story_sp = 0, ani_story_cg = 0, ani_face = false, ani_bg = false WHERE ani_story = 1; +UPDATE releases_hist SET ani_story_sp = 0, ani_story_cg = 0, ani_face = false, ani_bg = false WHERE ani_story = 1; +UPDATE releases SET ani_ero_sp = 0, ani_ero_cg = 0 WHERE ani_ero = 1; +UPDATE releases_hist SET ani_ero_sp = 0, ani_ero_cg = 0 WHERE ani_ero = 1; + +ALTER TABLE releases ADD CONSTRAINT releases_cutscene_check CHECK(ani_cutscene <> 0 AND (ani_cutscene & (256+512)) = 0); + +\i sql/editfunc.sql +COMMIT; diff --git a/util/updates/2022-04-01-user-traits.sql b/util/updates/2022-04-01-user-traits.sql new file mode 100644 index 00000000..a99b3d3a --- /dev/null +++ b/util/updates/2022-04-01-user-traits.sql @@ -0,0 +1,8 @@ +CREATE TABLE users_traits ( + id vndbid NOT NULL, + tid vndbid NOT NULL, + PRIMARY KEY(id, tid) +); +ALTER TABLE users_traits ADD CONSTRAINT users_traits_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE users_traits ADD CONSTRAINT users_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); +GRANT SELECT, INSERT, UPDATE, DELETE ON users_traits TO vndb_site; diff --git a/util/updates/2022-04-05-releases-has-ero.sql b/util/updates/2022-04-05-releases-has-ero.sql new file mode 100644 index 00000000..f31d9f04 --- /dev/null +++ b/util/updates/2022-04-05-releases-has-ero.sql @@ -0,0 +1,5 @@ +ALTER TABLE releases ADD COLUMN has_ero boolean NOT NULL DEFAULT FALSE; +ALTER TABLE releases_hist ADD COLUMN has_ero boolean NOT NULL DEFAULT FALSE; +UPDATE releases SET has_ero = TRUE WHERE minage = 18; +UPDATE releases_hist SET has_ero = TRUE WHERE minage = 18; +\i sql/editfunc.sql diff --git a/util/updates/2022-04-19-vn-default-poprank.sql b/util/updates/2022-04-19-vn-default-poprank.sql new file mode 100644 index 00000000..080269e2 --- /dev/null +++ b/util/updates/2022-04-19-vn-default-poprank.sql @@ -0,0 +1 @@ +ALTER TABLE vn ALTER COLUMN c_pop_rank SET DEFAULT 10000000; diff --git a/util/updates/2022-04-23-inuktitut-language.sql b/util/updates/2022-04-23-inuktitut-language.sql new file mode 100644 index 00000000..ae9507c6 --- /dev/null +++ b/util/updates/2022-04-23-inuktitut-language.sql @@ -0,0 +1 @@ +ALTER TYPE language ADD VALUE 'iu' AFTER 'it'; diff --git a/util/updates/2022-06-16-users-debloat.sql b/util/updates/2022-06-16-users-debloat.sql new file mode 100644 index 00000000..aa2ced78 --- /dev/null +++ b/util/updates/2022-06-16-users-debloat.sql @@ -0,0 +1,90 @@ +CREATE TABLE users_prefs ( + id vndbid NOT NULL PRIMARY KEY, + max_sexual smallint NOT NULL DEFAULT 0, + max_violence smallint NOT NULL DEFAULT 0, + last_reports timestamptz, -- For mods: Most recent activity seen on the reports listing + tableopts_c integer, + tableopts_v integer, + tableopts_vt integer, -- VN listing on tag pages + spoilers smallint NOT NULL DEFAULT 0, + tags_all boolean NOT NULL DEFAULT false, + tags_cont boolean NOT NULL DEFAULT true, + tags_ero boolean NOT NULL DEFAULT false, + tags_tech boolean NOT NULL DEFAULT true, + traits_sexual boolean NOT NULL DEFAULT false, + skin text NOT NULL DEFAULT '', + customcss text NOT NULL DEFAULT '', + ulist_votes jsonb, + ulist_vnlist jsonb, + ulist_wish jsonb, + vnlang jsonb, -- '$lang(-mtl)?' => true/false, which languages to expand/collapse on VN pages + title_langs jsonb, + alttitle_langs jsonb +); + +INSERT INTO users_prefs SELECT id + , max_sexual + , max_violence + , last_reports + , tableopts_c + , tableopts_v + , tableopts_vt + , spoilers + , tags_all + , tags_cont + , tags_ero + , tags_tech + , traits_sexual + , skin + , customcss + , ulist_votes + , ulist_vnlist + , ulist_wish + , vnlang + , title_langs + , alttitle_langs + FROM users; + +ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE users DROP COLUMN max_sexual ; +ALTER TABLE users DROP COLUMN max_violence ; +ALTER TABLE users DROP COLUMN last_reports ; +ALTER TABLE users DROP COLUMN tableopts_c ; +ALTER TABLE users DROP COLUMN tableopts_v ; +ALTER TABLE users DROP COLUMN tableopts_vt ; +ALTER TABLE users DROP COLUMN spoilers ; +ALTER TABLE users DROP COLUMN tags_all ; +ALTER TABLE users DROP COLUMN tags_cont ; +ALTER TABLE users DROP COLUMN tags_ero ; +ALTER TABLE users DROP COLUMN tags_tech ; +ALTER TABLE users DROP COLUMN traits_sexual ; +ALTER TABLE users DROP COLUMN skin ; +ALTER TABLE users DROP COLUMN customcss ; +ALTER TABLE users DROP COLUMN ulist_votes ; +ALTER TABLE users DROP COLUMN ulist_vnlist ; +ALTER TABLE users DROP COLUMN ulist_wish ; +ALTER TABLE users DROP COLUMN vnlang ; +ALTER TABLE users DROP COLUMN title_langs ; +ALTER TABLE users DROP COLUMN alttitle_langs; + +ALTER TABLE users_shadow ADD COLUMN ip inet NOT NULL DEFAULT '0.0.0.0'; +UPDATE users_shadow SET ip = users.ip FROM users WHERE users.id = users_shadow.id; +ALTER TABLE users DROP COLUMN ip; + +-- Rewrite the table to properly remove the columns. +CLUSTER users USING users_pkey; + +-- users.ip is not accessible anymore, so we need a separate table to throttle +-- registrations per IP. +CREATE TABLE registration_throttle ( + ip inet NOT NULL PRIMARY KEY, + timeout timestamptz NOT NULL +); + +-- While I'm at it, let's remove changes.ip too. I've not used it in the past decade. +ALTER TABLE changes DROP COLUMN ip; + +\i sql/func.sql +\i sql/editfunc.sql +\i sql/perms.sql diff --git a/util/updates/2022-06-18-user-prefs-prodrelexpand.sql b/util/updates/2022-06-18-user-prefs-prodrelexpand.sql new file mode 100644 index 00000000..96fe5fe5 --- /dev/null +++ b/util/updates/2022-06-18-user-prefs-prodrelexpand.sql @@ -0,0 +1 @@ +ALTER TABLE users_prefs ADD COLUMN prodrelexpand boolean NOT NULL DEFAULT true; diff --git a/util/updates/2022-06-19-user-prefs-vnrel.sql b/util/updates/2022-06-19-user-prefs-vnrel.sql new file mode 100644 index 00000000..f9321b93 --- /dev/null +++ b/util/updates/2022-06-19-user-prefs-vnrel.sql @@ -0,0 +1,31 @@ +ALTER TABLE users_prefs ADD COLUMN vnrel_langs language[], + ADD COLUMN vnrel_olang boolean NOT NULL DEFAULT true, + ADD COLUMN vnrel_mtl boolean NOT NULL DEFAULT false; + +-- Attempt to infer vnrel_langs and vnrel_mtl from the old 'vnlang' column. +BEGIN; + +CREATE OR REPLACE FUNCTION vnlang_to_langs(vnlang jsonb) RETURNS language[] AS $$ +DECLARE + ret language[]; + del language; +BEGIN + ret := enum_range(null::language); + FOR del IN SELECT key::language FROM jsonb_each(vnlang) x WHERE key NOT LIKE '%-mtl' AND value = 'false' + LOOP + ret := array_remove(ret, del); + END LOOP; + RETURN CASE WHEN array_length(ret,1) = array_length(enum_range(null::language),1) THEN NULL ELSE RET END; +END$$ LANGUAGE plpgsql; + +WITH p(id,langs,mtl) AS ( + SELECT id, vnlang_to_langs(vnlang), vnlang->'en-mtl' is not distinct from 'true' + FROM users_prefs WHERE vnlang IS NOT NULL +) UPDATE users_prefs + SET vnrel_langs = langs, vnrel_mtl = mtl + FROM p + WHERE p.id = users_prefs.id AND (langs IS NOT NULL OR mtl); + +DROP FUNCTION vnlang_to_langs(jsonb); + +COMMIT; diff --git a/util/updates/2022-06-20-changes-patrolling.sql b/util/updates/2022-06-20-changes-patrolling.sql new file mode 100644 index 00000000..32ad9929 --- /dev/null +++ b/util/updates/2022-06-20-changes-patrolling.sql @@ -0,0 +1,8 @@ +CREATE TABLE changes_patrolled ( + id integer NOT NULL, + uid vndbid NOT NULL, + PRIMARY KEY(id,uid) +); +ALTER TABLE changes_patrolled ADD CONSTRAINT changes_patrolled_id_fkey FOREIGN KEY (id) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE changes_patrolled ADD CONSTRAINT changes_patrolled_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +\i sql/perms.sql diff --git a/util/updates/2022-06-21-tags-vn-lie.sql b/util/updates/2022-06-21-tags-vn-lie.sql new file mode 100644 index 00000000..b4aafad9 --- /dev/null +++ b/util/updates/2022-06-21-tags-vn-lie.sql @@ -0,0 +1 @@ +ALTER TABLE tags_vn ADD COLUMN lie boolean; diff --git a/util/updates/2022-07-31-vn-devstatus.sql b/util/updates/2022-07-31-vn-devstatus.sql new file mode 100644 index 00000000..7bc709a0 --- /dev/null +++ b/util/updates/2022-07-31-vn-devstatus.sql @@ -0,0 +1,24 @@ +ALTER TABLE vn ADD COLUMN devstatus smallint NOT NULL DEFAULT 0; +ALTER TABLE vn_hist ADD COLUMN devstatus smallint NOT NULL DEFAULT 0; +\i sql/editfunc.sql + +UPDATE vn SET devstatus = 0 WHERE devstatus <> 0; + +-- Heuristic: VN is considered cancelled if it meets all of the following criteria: +-- * doesn't have a complete release +-- * doesn't have any release after 2020 +-- * doesn't have multiple partial releases +-- * doesn't have both a trial and partial release (weird heuristic, but there's many matching in-dev games) +UPDATE vn SET devstatus = 2 WHERE + id NOT IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype = 'complete' OR released > 20200000) + AND id NOT IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype = 'partial' GROUP BY vid HAVING COUNT(r.id) > 1) + AND id NOT IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype IN('partial','trial') GROUP BY vid HAVING COUNT(DISTINCT rtype) = 2); + +-- Heuristic: VN is considerd in development if it's not cancelled and meets one of the following: +-- * Has a future release date +-- * Has no complete releases and only a single partial release +UPDATE vn SET devstatus = 1 WHERE devstatus = 0 AND (c_released > 22020731 OR ( + id NOT IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype = 'complete') + AND id IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype = 'partial' GROUP BY vid HAVING COUNT(r.id) = 1))); + +UPDATE vn_hist SET devstatus = v.devstatus FROM changes c JOIN vn v ON c.itemid = v.id WHERE vn_hist.chid = c.id AND v.devstatus <> vn_hist.devstatus; diff --git a/util/updates/2022-08-03-tags_vn_direct.sql b/util/updates/2022-08-03-tags_vn_direct.sql new file mode 100644 index 00000000..e8a2445c --- /dev/null +++ b/util/updates/2022-08-03-tags_vn_direct.sql @@ -0,0 +1,10 @@ +CREATE TABLE tags_vn_direct ( + tag vndbid NOT NULL, + vid vndbid NOT NULL, + rating real NOT NULL, + spoiler smallint NOT NULL, + lie boolean NOT NULL +); +\i sql/func.sql +\i sql/perms.sql +SELECT tag_vn_calc(NULL); diff --git a/util/updates/2022-08-24-ipinfo.sql b/util/updates/2022-08-24-ipinfo.sql new file mode 100644 index 00000000..ffa00708 --- /dev/null +++ b/util/updates/2022-08-24-ipinfo.sql @@ -0,0 +1,17 @@ +CREATE TYPE ipinfo AS ( + ip inet, + country text, + asn integer, + as_name text, + anonymous_proxy boolean, + sattelite_provider boolean, + anycast boolean, + drop boolean +); + +ALTER TABLE audit_log ALTER COLUMN by_ip TYPE ipinfo USING ROW(by_ip,null,null,null,null,null,null,null); +ALTER TABLE reports ALTER COLUMN ip TYPE ipinfo USING CASE WHEN ip IS NULL THEN NULL ELSE ROW(ip,null,null,null,null,null,null,null)::ipinfo END; + +ALTER TABLE users_shadow ALTER COLUMN ip DROP DEFAULT; +ALTER TABLE users_shadow ALTER COLUMN ip DROP NOT NULL; +ALTER TABLE users_shadow ALTER COLUMN ip TYPE ipinfo USING CASE WHEN ip = '0.0.0.0' THEN NULL ELSE ROW(ip,null,null,null,null,null,null,null)::ipinfo END; diff --git a/util/updates/2022-08-25-customcss-csum.sql b/util/updates/2022-08-25-customcss-csum.sql new file mode 100644 index 00000000..8a2a8938 --- /dev/null +++ b/util/updates/2022-08-25-customcss-csum.sql @@ -0,0 +1,3 @@ +ALTER TABLE users_prefs ADD COLUMN customcss_csum bigint NOT NULL DEFAULT 0; +-- '1' is not exactly a checksum, but it'll do fine for the first version. +UPDATE users_prefs SET customcss_csum = 1 WHERE customcss <> ''; diff --git a/util/updates/2022-08-25-staff-editions.sql b/util/updates/2022-08-25-staff-editions.sql new file mode 100644 index 00000000..d5a731e5 --- /dev/null +++ b/util/updates/2022-08-25-staff-editions.sql @@ -0,0 +1,43 @@ +ALTER TYPE credit_type ADD VALUE 'translator' AFTER 'director'; +ALTER TYPE credit_type ADD VALUE 'editor' AFTER 'translator'; +ALTER TYPE credit_type ADD VALUE 'qa' AFTER 'editor'; + +CREATE TABLE vn_editions ( + id vndbid NOT NULL, -- [pub] + lang language, -- [pub] + eid smallint NOT NULL, -- [pub] (not stable across entry revisions) + official boolean NOT NULL DEFAULT TRUE, -- [pub] + name text NOT NULL, -- [pub] + PRIMARY KEY(id, eid) +); + +CREATE TABLE vn_editions_hist ( + chid integer NOT NULL, + lang language, + eid smallint NOT NULL, + official boolean NOT NULL DEFAULT TRUE, + name text NOT NULL, + PRIMARY KEY(chid, eid) +); + +ALTER TABLE vn_staff ADD COLUMN eid smallint; +ALTER TABLE vn_staff DROP CONSTRAINT vn_staff_pkey; +CREATE UNIQUE INDEX vn_staff_pkey ON vn_staff (id, COALESCE(eid,-1::smallint), aid, role); + +ALTER TABLE vn_staff_hist ADD COLUMN eid smallint; +ALTER TABLE vn_staff_hist DROP CONSTRAINT vn_staff_hist_pkey; +CREATE UNIQUE INDEX vn_staff_hist_pkey ON vn_staff_hist (chid, COALESCE(eid,-1::smallint), aid, role); + +ALTER TABLE vn_staff DROP CONSTRAINT vn_staff_id_fkey; +ALTER TABLE vn_staff_hist DROP CONSTRAINT vn_staff_hist_chid_fkey; + +ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_eid_fkey FOREIGN KEY (id,eid) REFERENCES vn_editions (id,eid) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_eid_fkey FOREIGN KEY (chid,eid) REFERENCES vn_editions_hist (chid,eid) DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE users_prefs + ADD COLUMN staffed_langs language[], + ADD COLUMN staffed_olang boolean NOT NULL DEFAULT true, + ADD COLUMN staffed_unoff boolean NOT NULL DEFAULT false; + +\i sql/editfunc.sql +\i sql/perms.sql diff --git a/util/updates/2022-08-28-basque-language.sql b/util/updates/2022-08-28-basque-language.sql new file mode 100644 index 00000000..a0bd3899 --- /dev/null +++ b/util/updates/2022-08-28-basque-language.sql @@ -0,0 +1 @@ +ALTER TYPE language ADD VALUE 'eu' AFTER 'es'; diff --git a/util/updates/2022-08-30-tag-trait-prefs.sql b/util/updates/2022-08-30-tag-trait-prefs.sql new file mode 100644 index 00000000..db2bec02 --- /dev/null +++ b/util/updates/2022-08-30-tag-trait-prefs.sql @@ -0,0 +1,23 @@ +CREATE TABLE users_prefs_tags ( + id vndbid NOT NULL, + tid vndbid NOT NULL, + spoil smallint NOT NULL, + childs boolean NOT NULL, + PRIMARY KEY(id, tid) +); + +ALTER TABLE users_prefs_tags ADD CONSTRAINT users_prefs_tags_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE users_prefs_tags ADD CONSTRAINT users_prefs_tags_tid_fkey FOREIGN KEY (tid) REFERENCES tags (id) ON DELETE CASCADE; + +CREATE TABLE users_prefs_traits ( + id vndbid NOT NULL, + tid vndbid NOT NULL, + spoil smallint NOT NULL, + childs boolean NOT NULL, + PRIMARY KEY(id, tid) +); + +ALTER TABLE users_prefs_traits ADD CONSTRAINT users_prefs_traits_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE users_prefs_traits ADD CONSTRAINT users_prefs_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id) ON DELETE CASCADE; + +\i sql/perms.sql diff --git a/util/updates/2022-09-28-release-titles.sql b/util/updates/2022-09-28-release-titles.sql new file mode 100644 index 00000000..4e875a14 --- /dev/null +++ b/util/updates/2022-09-28-release-titles.sql @@ -0,0 +1,81 @@ +BEGIN; + +CREATE TABLE releases_titles ( + id vndbid NOT NULL, + lang language NOT NULL, + mtl boolean NOT NULL DEFAULT false, + title text, + latin text, + PRIMARY KEY(id, lang) +); + +CREATE TABLE releases_titles_hist ( + chid integer NOT NULL, + lang language NOT NULL, + mtl boolean NOT NULL DEFAULT false, + title text, + latin text, + PRIMARY KEY(chid, lang) +); + +-- Fixup some old (deleted) entries that are missing a language field +INSERT INTO releases_lang SELECT rv.id, v.olang, false FROM releases_vn rv JOIN vn v ON v.id = rv.vid WHERE NOT EXISTS(SELECT 1 FROM releases_lang rl WHERE rl.id = rv.id); +INSERT INTO releases_lang_hist SELECT rv.chid, v.olang, false FROM releases_vn_hist rv JOIN vn v ON v.id = rv.vid WHERE NOT EXISTS(SELECT 1 FROM releases_lang_hist rl WHERE rl.chid = rv.chid); + +ALTER TABLE releases ADD COLUMN olang language NOT NULL DEFAULT 'ja'; +ALTER TABLE releases_hist ADD COLUMN olang language NOT NULL DEFAULT 'ja'; + +-- 'releases' table needs an olang field now in order to select the proper +-- default title to display. Inherit these from the related (lowest-id) VN +-- entry if the release language matches, otherwise select an arbitrary one +-- (preferring English). +WITH rl (id, ol) AS ( + SELECT DISTINCT ON(rv.id) rv.id, COALESCE(rl.lang, re.lang, rf.lang, v.olang) + FROM releases_vn rv + JOIN vn v ON v.id = rv.vid + LEFT JOIN releases_lang rl ON rl.id = rv.id AND rl.lang = v.olang + LEFT JOIN releases_lang re ON re.id = rv.id AND re.lang = 'en' + LEFT JOIN releases_lang rf ON rf.id = rv.id AND (rf.lang <> v.olang AND rf.lang <> 'en') + ORDER BY rv.id, rl.id NULLS LAST, rv.vid, rl.lang +) UPDATE releases SET olang = ol FROM rl WHERE releases.id = rl.id AND ol <> 'ja'; + +WITH rl (id, ol) AS ( + SELECT DISTINCT ON(rv.chid) rv.chid, COALESCE(rl.lang, re.lang, rf.lang, v.olang) + FROM releases_vn_hist rv + JOIN vn v ON v.id = rv.vid + LEFT JOIN releases_lang_hist rl ON rl.chid = rv.chid AND rl.lang = v.olang + LEFT JOIN releases_lang_hist re ON re.chid = rv.chid AND re.lang = 'en' + LEFT JOIN releases_lang_hist rf ON rf.chid = rv.chid AND (rf.lang <> v.olang AND rf.lang <> 'en') + ORDER BY rv.chid, rl.chid NULLS LAST, rv.vid, rl.lang +) UPDATE releases_hist SET olang = ol FROM rl WHERE chid = id AND ol <> 'ja'; + +-- Copy all languages and set the title only for the "main" language as determined above. +INSERT INTO releases_titles + SELECT rl.id, rl.lang, rl.mtl + , CASE WHEN rl.lang <> r.olang THEN NULL WHEN r.original = '' THEN r.title ELSE r.original END + , CASE WHEN rl.lang <> r.olang THEN NULL WHEN r.original = '' THEN NULL ELSE r.title END + FROM releases_lang rl + JOIN releases r ON r.id = rl.id; + +INSERT INTO releases_titles_hist + SELECT rl.chid, rl.lang, rl.mtl + , CASE WHEN rl.lang <> r.olang THEN NULL WHEN r.original = '' THEN r.title ELSE r.original END + , CASE WHEN rl.lang <> r.olang THEN NULL WHEN r.original = '' THEN NULL ELSE r.title END + FROM releases_lang_hist rl + JOIN releases_hist r ON r.chid = rl.chid; + +ALTER TABLE releases ALTER COLUMN c_search DROP NOT NULL, ALTER COLUMN c_search DROP EXPRESSION; + +ALTER TABLE releases DROP COLUMN title, DROP COLUMN original; +ALTER TABLE releases_hist DROP COLUMN title, DROP COLUMN original; + +CREATE VIEW releasest AS SELECT r.*, COALESCE(ro.latin, ro.title) AS title, COALESCE(ro.latin, ro.title) AS sorttitle, CASE WHEN ro.latin IS NULL THEN '' ELSE ro.title END AS alttitle FROM releases r JOIN releases_titles ro ON ro.id = r.id AND ro.lang = r.olang; + +DROP TABLE releases_lang, releases_lang_hist; + +COMMIT; + +\i sql/tableattrs.sql +\i sql/func.sql +\i sql/editfunc.sql +\i sql/perms.sql diff --git a/util/updates/2022-10-08-images-smallints.sql b/util/updates/2022-10-08-images-smallints.sql new file mode 100644 index 00000000..316bf0c8 --- /dev/null +++ b/util/updates/2022-10-08-images-smallints.sql @@ -0,0 +1,19 @@ +ALTER TABLE images + ALTER c_votecount TYPE smallint, + ALTER c_weight TYPE smallint, + ALTER c_sexual_avg TYPE smallint USING COALESCE(c_sexual_avg *100, 200), + ALTER c_sexual_stddev TYPE smallint USING COALESCE(c_sexual_stddev *100, 0), + ALTER c_violence_avg TYPE smallint USING COALESCE(c_violence_avg *100, 200), + ALTER c_violence_stddev TYPE smallint USING COALESCE(c_violence_stddev*100, 0), + ALTER c_sexual_avg SET DEFAULT 200, + ALTER c_sexual_stddev SET DEFAULT 0, + ALTER c_violence_avg SET DEFAULT 200, + ALTER c_violence_stddev SET DEFAULT 0, + ALTER c_sexual_avg SET NOT NULL, + ALTER c_sexual_stddev SET NOT NULL, + ALTER c_violence_avg SET NOT NULL, + ALTER c_violence_stddev SET NOT NULL; + +\i sql/func.sql + +SELECT update_images_cache(NULL); diff --git a/util/updates/2022-10-16-release-shop-links.sql b/util/updates/2022-10-16-release-shop-links.sql new file mode 100644 index 00000000..6be52706 --- /dev/null +++ b/util/updates/2022-10-16-release-shop-links.sql @@ -0,0 +1,11 @@ +ALTER TABLE releases + ADD COLUMN l_nintendo_jp bigint NOT NULL DEFAULT 0, + ADD COLUMN l_nintendo_hk bigint NOT NULL DEFAULT 0, + ADD COLUMN l_nintendo text NOT NULL DEFAULT '', + ADD COLUMN l_playstation_hk text NOT NULL DEFAULT ''; +ALTER TABLE releases_hist + ADD COLUMN l_nintendo_jp bigint NOT NULL DEFAULT 0, + ADD COLUMN l_nintendo_hk bigint NOT NULL DEFAULT 0, + ADD COLUMN l_nintendo text NOT NULL DEFAULT '', + ADD COLUMN l_playstation_hk text NOT NULL DEFAULT ''; +\i sql/editfunc.sql diff --git a/util/updates/2022-10-22-tags_vn_inherit-lie.sql b/util/updates/2022-10-22-tags_vn_inherit-lie.sql new file mode 100644 index 00000000..9ab1e329 --- /dev/null +++ b/util/updates/2022-10-22-tags_vn_inherit-lie.sql @@ -0,0 +1,4 @@ +ALTER TABLE tags_vn_inherit ADD COLUMN lie boolean; +\i sql/func.sql +SELECT tag_vn_calc(null); +ALTER TABLE tags_vn_inherit ALTER COLUMN lie DROP NOT NULL; diff --git a/util/updates/2022-10-27-trait-lies.sql b/util/updates/2022-10-27-trait-lies.sql new file mode 100644 index 00000000..fff91ca8 --- /dev/null +++ b/util/updates/2022-10-27-trait-lies.sql @@ -0,0 +1,5 @@ +ALTER TABLE chars_traits ADD COLUMN lie boolean NOT NULL DEFAULT false; +ALTER TABLE chars_traits_hist ADD COLUMN lie boolean NOT NULL DEFAULT false; +ALTER TABLE traits_chars ADD COLUMN lie boolean NOT NULL DEFAULT false; +\i sql/editfunc.sql +\i sql/func.sql diff --git a/util/updates/2022-10-31-ulist-vns-labels.sql b/util/updates/2022-10-31-ulist-vns-labels.sql new file mode 100644 index 00000000..04973343 --- /dev/null +++ b/util/updates/2022-10-31-ulist-vns-labels.sql @@ -0,0 +1,137 @@ +-- This migration script is written so that it can be run while keeping VNDB +-- online in read-only mode. Any writes to the database while this script is +-- active will likely result in a deadlock or a bit of data loss. + +-- (An older version of this script attempted to do an in-place UPDATE on +-- ulist_vns, but postgres didn't properly optimize that query in production +-- and ended up taking the site down for 30 minutes. This version is both +-- faster and doesn't require the site to go fully down) + +CREATE TABLE ulist_vns_tmp ( + uid vndbid NOT NULL, + vid vndbid NOT NULL, + added timestamptz NOT NULL DEFAULT NOW(), + lastmod timestamptz NOT NULL DEFAULT NOW(), + vote_date timestamptz, + started date, + finished date, + vote smallint, + c_private boolean NOT NULL DEFAULT true, + labels smallint[] NOT NULL DEFAULT '{}', + notes text NOT NULL DEFAULT '' +); + +INSERT INTO ulist_vns_tmp + SELECT uv.uid, uv.vid, uv.added, uv.lastmod, uv.vote_date, uv.started, uv.finished, uv.vote, coalesce(l.private, true), coalesce(l.labels, '{}'), uv.notes + FROM ulist_vns uv + LEFT JOIN ( + SELECT uvl.uid, uvl.vid, bool_and(ul.private), array_agg(uvl.lbl::smallint ORDER BY uvl.lbl) + FROM ulist_vns_labels uvl + JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl + GROUP BY uvl.uid, uvl.vid + ) l(uid, vid, private, labels) ON l.uid = uv.uid AND l.vid = uv.vid + ORDER BY uv.uid, uv.vid; + +-- Attempt a perfect reconstruction of 'ulist_vns', so that constraint & index +-- names match those of a newly created table with the correct name. +ALTER INDEX ulist_vns_pkey RENAME TO ulist_vns_old_pkey; +ALTER INDEX ulist_vns_voted RENAME TO ulist_vns_old_voted; + +\timing +ALTER TABLE ulist_vns_tmp ADD CONSTRAINT ulist_vns_pkey PRIMARY KEY (uid, vid); +ALTER TABLE ulist_vns_tmp ADD CONSTRAINT ulist_vns_vote_check CHECK(vote IS NULL OR vote BETWEEN 10 AND 100); +CREATE INDEX ulist_vns_voted ON ulist_vns_tmp (vid, vote_date) WHERE vote IS NOT NULL; +ALTER TABLE ulist_vns_tmp ADD CONSTRAINT ulist_vns_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE ulist_vns_tmp ADD CONSTRAINT ulist_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); + +ANALYZE ulist_vns_tmp; +GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_tmp TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_tmp TO vndb_multi; + +BEGIN; +ALTER TABLE ulist_vns RENAME TO ulist_vns_old; +ALTER TABLE ulist_vns_tmp RENAME TO ulist_vns; +COMMIT; + + +-- Let's not \i SQL files here, since we're running this script on an older commit. + +-- From util.sql + +CREATE OR REPLACE FUNCTION array_set(arr anycompatiblearray, elem anycompatible) RETURNS anycompatiblearray AS $$ +DECLARE + ret arr%TYPE; + e elem%TYPE; + added boolean := false; +BEGIN + FOREACH e IN ARRAY arr LOOP + IF e = elem THEN RETURN arr; + ELSIF added or e < elem THEN ret := ret || e; + ELSE + ret := ret || elem || e; + added := true; + END IF; + END LOOP; + RETURN CASE WHEN added THEN ret ELSE ret || elem END; +END; +$$ LANGUAGE plpgsql IMMUTABLE; + + + +-- From func.sql + +CREATE OR REPLACE FUNCTION update_users_ulist_stats(vndbid) RETURNS void AS $$ +BEGIN + WITH cnt(uid, votes, vns, wish) AS ( + SELECT u.id + , COUNT(uv.vid) FILTER (WHERE NOT uv.c_private AND uv.vote IS NOT NULL) -- Voted + , COUNT(uv.vid) FILTER (WHERE NOT uv.c_private AND NOT (uv.labels <@ ARRAY[5,6]::smallint[])) -- Labelled, but not wishlish/blacklist + , COUNT(uv.vid) FILTER (WHERE uwish.private IS NOT DISTINCT FROM false AND uv.labels && ARRAY[5::smallint]) -- Wishlist + FROM users u + LEFT JOIN ulist_vns uv ON uv.uid = u.id + LEFT JOIN ulist_labels uwish ON uwish.uid = u.id AND uwish.id = 5 + WHERE $1 IS NULL OR u.id = $1 + GROUP BY u.id + ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish + FROM cnt WHERE id = uid AND (c_votes, c_vns, c_wish) IS DISTINCT FROM (votes, vns, wish); +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION update_users_ulist_private(vndbid, vndbid) RETURNS void AS $$ +BEGIN + WITH p(uid,vid,private) AS ( + SELECT uv.uid, uv.vid, COALESCE(bool_and(l.private), true) + FROM ulist_vns uv + LEFT JOIN unnest(uv.labels) x(id) ON true + LEFT JOIN ulist_labels l ON l.id = x.id AND l.uid = uv.uid + WHERE ($1 IS NULL OR uv.uid = $1) + AND ($2 IS NULL OR uv.vid = $2) + GROUP BY uv.uid, uv.vid + ) UPDATE ulist_vns SET c_private = p.private FROM p + WHERE ulist_vns.uid = p.uid AND ulist_vns.vid = p.vid AND ulist_vns.c_private <> p.private; +END; +$$ LANGUAGE plpgsql; + + + +-- From triggers.sql + +CREATE OR REPLACE FUNCTION ulist_voted_label() RETURNS trigger AS $$ +BEGIN + NEW.labels := CASE WHEN NEW.vote IS NULL THEN array_remove(NEW.labels, 7) ELSE array_set(NEW.labels, 7) END; + RETURN NEW; +END +$$ LANGUAGE plpgsql; + +CREATE TRIGGER ulist_voted_label_ins BEFORE INSERT ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label(); +CREATE TRIGGER ulist_voted_label_upd BEFORE UPDATE ON ulist_vns FOR EACH ROW WHEN ((OLD.vote IS NULL) <> (NEW.vote IS NULL)) EXECUTE PROCEDURE ulist_voted_label(); + + + + +ALTER TABLE ulist_labels ALTER COLUMN id TYPE smallint; + + +-- These should be run after restarting vndb.pl with the new codebase. +DROP TABLE ulist_vns_labels; +DROP TABLE ulist_vns_old; diff --git a/util/updates/2022-11-11-serbian-language.sql b/util/updates/2022-11-11-serbian-language.sql new file mode 100644 index 00000000..48cda88e --- /dev/null +++ b/util/updates/2022-11-11-serbian-language.sql @@ -0,0 +1 @@ +ALTER TYPE language ADD VALUE 'sr' AFTER 'sl'; diff --git a/util/updates/2022-11-29-api2-tokens.sql b/util/updates/2022-11-29-api2-tokens.sql new file mode 100644 index 00000000..f88e9754 --- /dev/null +++ b/util/updates/2022-11-29-api2-tokens.sql @@ -0,0 +1,9 @@ +ALTER TYPE session_type ADD VALUE 'api2' AFTER 'api'; + +ALTER TABLE sessions + ADD COLUMN notes text, + ADD COLUMN listread boolean NOT NULL DEFAULT false; + +\i sql/func.sql + +DROP FUNCTION user_isvalidsession(vndbid, bytea, session_type); diff --git a/util/updates/2022-12-13-users-prefs-timezone.sql b/util/updates/2022-12-13-users-prefs-timezone.sql new file mode 100644 index 00000000..1e90d967 --- /dev/null +++ b/util/updates/2022-12-13-users-prefs-timezone.sql @@ -0,0 +1 @@ +ALTER TABLE users_prefs ADD COLUMN timezone text NOT NULL DEFAULT ''; diff --git a/util/updates/2022-12-18-sql-tags-cache-merge.sql b/util/updates/2022-12-18-sql-tags-cache-merge.sql new file mode 100644 index 00000000..83730e56 --- /dev/null +++ b/util/updates/2022-12-18-sql-tags-cache-merge.sql @@ -0,0 +1,8 @@ +DROP INDEX IF EXISTS tags_vn_direct_tag_vid; +ALTER TABLE tags_vn_direct ADD PRIMARY KEY (tag, vid); + +DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; +ALTER TABLE tags_vn_inherit ADD PRIMARY KEY (tag, vid); + +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2022-12-19-sql-traits-chars-cache-merge.sql b/util/updates/2022-12-19-sql-traits-chars-cache-merge.sql new file mode 100644 index 00000000..e6196d68 --- /dev/null +++ b/util/updates/2022-12-19-sql-traits-chars-cache-merge.sql @@ -0,0 +1,5 @@ +DROP INDEX traits_chars_tid; +ALTER TABLE traits_chars ADD PRIMARY KEY (tid, cid); +CREATE INDEX traits_chars_cid ON traits_chars (cid); +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2022-12-19-sql-unique-null-not-distinct.sql b/util/updates/2022-12-19-sql-unique-null-not-distinct.sql new file mode 100644 index 00000000..02c00628 --- /dev/null +++ b/util/updates/2022-12-19-sql-unique-null-not-distinct.sql @@ -0,0 +1,12 @@ +DROP INDEX threads_boards_pkey; +CREATE UNIQUE INDEX threads_boards_pkey ON threads_boards (tid,type,iid) NULLS NOT DISTINCT; + +DROP INDEX vn_staff_pkey; +CREATE UNIQUE INDEX vn_staff_pkey ON vn_staff (id, eid, aid, role) NULLS NOT DISTINCT; +DROP INDEX vn_staff_hist_pkey; +CREATE UNIQUE INDEX vn_staff_hist_pkey ON vn_staff_hist (chid, eid, aid, role) NULLS NOT DISTINCT; + +DROP INDEX chars_vns_pkey; +CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, rid) NULLS NOT DISTINCT; +DROP INDEX chars_vns_hist_pkey; +CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, rid) NULLS NOT DISTINCT; diff --git a/util/updates/2023-01-08-cherokee-language.sql b/util/updates/2023-01-08-cherokee-language.sql new file mode 100644 index 00000000..f48c5694 --- /dev/null +++ b/util/updates/2023-01-08-cherokee-language.sql @@ -0,0 +1 @@ +ALTER TYPE language ADD VALUE 'ck' AFTER 'cs'; diff --git a/util/updates/2023-01-17-api2-listwrite.sql b/util/updates/2023-01-17-api2-listwrite.sql new file mode 100644 index 00000000..75279206 --- /dev/null +++ b/util/updates/2023-01-17-api2-listwrite.sql @@ -0,0 +1,3 @@ +ALTER TABLE sessions ADD COLUMN listwrite boolean NOT NULL DEFAULT false; +DROP FUNCTION user_api2_set_token(vndbid, vndbid, bytea, bytea, text, boolean); +\i sql/func.sql diff --git a/util/updates/2023-01-19-delete-admin-setpass.sql b/util/updates/2023-01-19-delete-admin-setpass.sql new file mode 100644 index 00000000..3f9b158d --- /dev/null +++ b/util/updates/2023-01-19-delete-admin-setpass.sql @@ -0,0 +1 @@ +DROP FUNCTION user_admin_setpass(vndbid, vndbid, bytea, bytea); diff --git a/util/updates/2023-02-01-sql-titleprefs.sql b/util/updates/2023-02-01-sql-titleprefs.sql new file mode 100644 index 00000000..4f49427d --- /dev/null +++ b/util/updates/2023-02-01-sql-titleprefs.sql @@ -0,0 +1,67 @@ +\i sql/schema.sql + +-- The old JSON structure is messy; the same language may be listed multiple +-- times and original language isn't always present or the last option. This +-- function attempts a clean conversion, where the preference is the same but +-- without the weirdness. +CREATE OR REPLACE FUNCTION json2titleprefs(title_langs jsonb, alttitle_langs jsonb) RETURNS titleprefs AS $$ + WITH t_parsed (rank, lang, latin, prio, official) AS ( + -- Parse, add rank & prio + SELECT row_number() OVER(ROWS CURRENT ROW), lang, COALESCE(latin, false) + , CASE WHEN original IS NOT DISTINCT FROM true THEN 3 WHEN official IS NOT DISTINCT FROM true THEN 2 ELSE 1 END + , CASE WHEN original IS NOT DISTINCT FROM true THEN NULL ELSE COALESCE(official, false) END + FROM jsonb_to_recordset(COALESCE(title_langs, '[{"latin":true}]')) + AS x(lang language, latin bool, official bool, original bool) + ), t (rank, lang, latin, official) AS ( + -- Filter, remove duplicates and re-rank + SELECT CASE WHEN lang IS NULL THEN NULL ELSE row_number() OVER(ORDER BY rank) END, lang, latin, official + FROM t_parsed x + WHERE rank <= COALESCE((SELECT MIN(rank) FROM t_parsed WHERE lang IS NULL), 10) + AND NOT EXISTS(SELECT 1 FROM t_parsed y WHERE x.lang = y.lang AND y.rank < x.rank AND y.prio <= x.prio) + + -- Same, for alttitle + ), a_parsed (rank, lang, latin, prio, official) AS ( + SELECT row_number() OVER(ROWS CURRENT ROW), lang, COALESCE(latin, false) + , CASE WHEN original IS NOT DISTINCT FROM true THEN 3 WHEN official IS NOT DISTINCT FROM true THEN 2 ELSE 1 END + , CASE WHEN original IS NOT DISTINCT FROM true THEN NULL ELSE COALESCE(official, false) END + FROM jsonb_to_recordset(alttitle_langs) + AS x(lang language, latin bool, official bool, original bool) + ), a (rank, lang, latin, official) AS ( + SELECT CASE WHEN lang IS NULL THEN NULL ELSE row_number() OVER(ORDER BY rank) END, lang, latin, official + FROM a_parsed x + WHERE rank <= COALESCE((SELECT MIN(rank) FROM a_parsed WHERE lang IS NULL), 10) + AND NOT EXISTS(SELECT 1 FROM a_parsed y WHERE x.lang = y.lang AND y.rank < x.rank AND y.prio <= x.prio) + + ) SELECT ROW( + (SELECT lang FROM t WHERE rank = 1) + , (SELECT lang FROM t WHERE rank = 2) + , (SELECT lang FROM t WHERE rank = 3) + , (SELECT lang FROM t WHERE rank = 4) + , (SELECT lang FROM a WHERE rank = 1) + , (SELECT lang FROM a WHERE rank = 2) + , (SELECT lang FROM a WHERE rank = 3) + , (SELECT lang FROM a WHERE rank = 4) + , COALESCE((SELECT latin FROM t WHERE rank = 1), false) + , COALESCE((SELECT latin FROM t WHERE rank = 2), false) + , COALESCE((SELECT latin FROM t WHERE rank = 3), false) + , COALESCE((SELECT latin FROM t WHERE rank = 4), false) + , COALESCE((SELECT latin FROM t WHERE lang IS NULL), false) + , COALESCE((SELECT latin FROM a WHERE rank = 1), false) + , COALESCE((SELECT latin FROM a WHERE rank = 2), false) + , COALESCE((SELECT latin FROM a WHERE rank = 3), false) + , COALESCE((SELECT latin FROM a WHERE rank = 4), false) + , COALESCE((SELECT latin FROM a WHERE lang IS NULL), false) + , (SELECT official FROM t WHERE rank = 1) + , (SELECT official FROM t WHERE rank = 2) + , (SELECT official FROM t WHERE rank = 3) + , (SELECT official FROM t WHERE rank = 4) + , (SELECT official FROM a WHERE rank = 1) + , (SELECT official FROM a WHERE rank = 2) + , (SELECT official FROM a WHERE rank = 3) + , (SELECT official FROM a WHERE rank = 4) + )::titleprefs +$$ LANGUAGE SQL IMMUTABLE; + + +ALTER TABLE users_prefs ADD COLUMN titles titleprefs; +UPDATE users_prefs SET titles = json2titleprefs(title_langs, alttitle_langs) WHERE title_langs IS NOT NULL OR alttitle_langs IS NOT NULL; diff --git a/util/updates/2023-02-02-sql-titleprefs.sql b/util/updates/2023-02-02-sql-titleprefs.sql new file mode 100644 index 00000000..73f7c6de --- /dev/null +++ b/util/updates/2023-02-02-sql-titleprefs.sql @@ -0,0 +1,5 @@ +CREATE TYPE item_info_type AS (title text, alttitle text, uid vndbid, hidden boolean, locked boolean); +\i sql/func.sql + +-- Can be dropped after reloading all code. +--DROP FUNCTION item_info(vndbid, int); diff --git a/util/updates/2023-02-04-producerst.sql b/util/updates/2023-02-04-producerst.sql new file mode 100644 index 00000000..ee5804a9 --- /dev/null +++ b/util/updates/2023-02-04-producerst.sql @@ -0,0 +1,15 @@ +ALTER TABLE producers ALTER COLUMN original DROP NOT NULL; +ALTER TABLE producers ALTER COLUMN original DROP DEFAULT; +ALTER TABLE producers_hist ALTER COLUMN original DROP NOT NULL; +ALTER TABLE producers_hist ALTER COLUMN original DROP DEFAULT; +UPDATE producers SET original = NULL WHERE original = ''; +UPDATE producers_hist SET original = NULL WHERE original = ''; + +CREATE VIEW producerst AS + SELECT id, type, lang, l_wikidata, locked, hidden, alias, website, "desc", l_wp, c_search + , name, original AS altname, name AS sortname + FROM producers; + +\i sql/editfunc.sql +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2023-02-19-title-langs.sql b/util/updates/2023-02-19-title-langs.sql new file mode 100644 index 00000000..62510e2b --- /dev/null +++ b/util/updates/2023-02-19-title-langs.sql @@ -0,0 +1,5 @@ +DROP TYPE item_info_type CASCADE; +DROP VIEW vnt, releasest, producerst CASCADE; +\i sql/schema.sql +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2023-02-20-titleprefs-staff.sql b/util/updates/2023-02-20-titleprefs-staff.sql new file mode 100644 index 00000000..b7e3047e --- /dev/null +++ b/util/updates/2023-02-20-titleprefs-staff.sql @@ -0,0 +1,18 @@ +ALTER TABLE staff_alias ALTER COLUMN original DROP NOT NULL, ALTER COLUMN original DROP DEFAULT; +ALTER TABLE staff_alias_hist ALTER COLUMN original DROP NOT NULL, ALTER COLUMN original DROP DEFAULT; +UPDATE staff_alias SET original = null WHERE original = ''; +UPDATE staff_alias_hist SET original = null WHERE original = ''; + +CREATE VIEW staff_aliast AS + -- Everything from 'staff', except 'aid' is renamed to 'main' + SELECT s.id, s.gender, s.lang, s.l_anidb, s.l_wikidata, s.l_pixiv, s.locked, s.hidden, s."desc", s.l_wp, s.l_site, s.l_twitter, s.aid AS main + , sa.aid, sa.name, sa.original + , ARRAY [ s.lang::text, sa.name + , s.lang::text, COALESCE(sa.original, sa.name) ] AS title + , sa.name AS sorttitle + FROM staff s + JOIN staff_alias sa ON sa.id = s.id; + +\i sql/editfunc.sql +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2023-02-21-tt-prefs.sql b/util/updates/2023-02-21-tt-prefs.sql new file mode 100644 index 00000000..24d527e0 --- /dev/null +++ b/util/updates/2023-02-21-tt-prefs.sql @@ -0,0 +1,7 @@ +ALTER TABLE users_prefs_tags ALTER COLUMN spoil DROP NOT NULL; +ALTER TABLE users_prefs_tags ADD COLUMN color text; +ALTER TABLE users_prefs_traits ALTER COLUMN spoil DROP NOT NULL; +ALTER TABLE users_prefs_traits ADD COLUMN color text; + +UPDATE users_prefs_tags SET spoil = 0, color = 'standout' WHERE spoil = -1; +UPDATE users_prefs_traits SET spoil = 0, color = 'standout' WHERE spoil = -1; diff --git a/util/updates/2023-03-09-chars-lang.sql b/util/updates/2023-03-09-chars-lang.sql new file mode 100644 index 00000000..dcfffa0d --- /dev/null +++ b/util/updates/2023-03-09-chars-lang.sql @@ -0,0 +1,10 @@ +ALTER TABLE chars ADD COLUMN c_lang language NOT NULL DEFAULT 'ja'; + +WITH x(id,lang) AS ( + SELECT DISTINCT ON (cv.id) cv.id, v.olang + FROM chars_vns cv + JOIN vn v ON v.id = cv.vid + ORDER BY cv.id, v.hidden, v.c_released +) UPDATE chars c SET c_lang = x.lang FROM x WHERE c.id = x.id AND c.c_lang <> x.lang; + +\i sql/func.sql diff --git a/util/updates/2023-03-09b-chars-titleprefs.sql b/util/updates/2023-03-09b-chars-titleprefs.sql new file mode 100644 index 00000000..c78a62d6 --- /dev/null +++ b/util/updates/2023-03-09b-chars-titleprefs.sql @@ -0,0 +1,14 @@ +ALTER TABLE chars ALTER COLUMN original DROP NOT NULL, ALTER COLUMN original DROP DEFAULT; +ALTER TABLE chars_hist ALTER COLUMN original DROP NOT NULL, ALTER COLUMN original DROP DEFAULT; +UPDATE chars SET original = NULL WHERE original = ''; +UPDATE chars_hist SET original = NULL WHERE original = ''; + +CREATE VIEW charst AS + SELECT * + , ARRAY [ c_lang::text, name + , c_lang::text, COALESCE(original, name) ] AS title + , name AS sorttitle + FROM chars; + +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2023-03-20-producer-name-swap.sql b/util/updates/2023-03-20-producer-name-swap.sql new file mode 100644 index 00000000..bf04fb12 --- /dev/null +++ b/util/updates/2023-03-20-producer-name-swap.sql @@ -0,0 +1,13 @@ +ALTER TABLE producers RENAME COLUMN original TO latin; +ALTER TABLE producers_hist RENAME COLUMN original TO latin; + +UPDATE producers SET name = latin, latin = name WHERE latin IS NOT NULL; +UPDATE producers_hist SET name = latin, latin = name WHERE latin IS NOT NULL; + +DROP FUNCTION titleprefs_swap(titleprefs, language, text, text); +DROP VIEW producerst CASCADE; + +\i sql/schema.sql +\i sql/editfunc.sql +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2023-03-20b-chars-name-swap.sql b/util/updates/2023-03-20b-chars-name-swap.sql new file mode 100644 index 00000000..952aba80 --- /dev/null +++ b/util/updates/2023-03-20b-chars-name-swap.sql @@ -0,0 +1,12 @@ +ALTER TABLE chars RENAME COLUMN original TO latin; +ALTER TABLE chars_hist RENAME COLUMN original TO latin; + +UPDATE chars SET name = latin, latin = name WHERE latin IS NOT NULL; +UPDATE chars_hist SET name = latin, latin = name WHERE latin IS NOT NULL; + +DROP VIEW charst CASCADE; + +\i sql/schema.sql +\i sql/editfunc.sql +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2023-03-20c-staff-name-swap.sql b/util/updates/2023-03-20c-staff-name-swap.sql new file mode 100644 index 00000000..c2474d2f --- /dev/null +++ b/util/updates/2023-03-20c-staff-name-swap.sql @@ -0,0 +1,14 @@ +ALTER TABLE staff_alias RENAME COLUMN original TO latin; +ALTER TABLE staff_alias_hist RENAME COLUMN original TO latin; + +UPDATE staff_alias SET name = latin, latin = name WHERE latin IS NOT NULL; +UPDATE staff_alias_hist SET name = latin, latin = name WHERE latin IS NOT NULL; + +DROP VIEW staff_aliast CASCADE; + +\i sql/schema.sql +\i sql/editfunc.sql +\i sql/func.sql +\i sql/perms.sql + +DROP FUNCTION titleprefs_swapold(titleprefs, language, text, text); diff --git a/util/updates/2023-03-24-search-cache.sql b/util/updates/2023-03-24-search-cache.sql new file mode 100644 index 00000000..f72034cf --- /dev/null +++ b/util/updates/2023-03-24-search-cache.sql @@ -0,0 +1,44 @@ +-- Part one, can be done while the site is running old code + +CREATE EXTENSION pg_trgm; + +CREATE TABLE search_cache ( + id vndbid NOT NULL, + subid integer, -- only for staff_alias.id at the moment + prio smallint NOT NULL, -- 1 for indirect titles, 2 for aliases, 3 for main titles + label text NOT NULL COLLATE "C" +) PARTITION BY RANGE(id); + +CREATE TABLE search_cache_v PARTITION OF search_cache FOR VALUES FROM ('v1') TO (vndbid_max('v')); +CREATE TABLE search_cache_r PARTITION OF search_cache FOR VALUES FROM ('r1') TO (vndbid_max('r')); +CREATE TABLE search_cache_c PARTITION OF search_cache FOR VALUES FROM ('c1') TO (vndbid_max('c')); +CREATE TABLE search_cache_p PARTITION OF search_cache FOR VALUES FROM ('p1') TO (vndbid_max('p')); +CREATE TABLE search_cache_s PARTITION OF search_cache FOR VALUES FROM ('s1') TO (vndbid_max('s')); +CREATE TABLE search_cache_g PARTITION OF search_cache FOR VALUES FROM ('g1') TO (vndbid_max('g')); +CREATE TABLE search_cache_i PARTITION OF search_cache FOR VALUES FROM ('i1') TO (vndbid_max('i')); + +CREATE INDEX search_cache_id ON search_cache (id); +CREATE INDEX search_cache_label ON search_cache USING GIN (label gin_trgm_ops); + +\i sql/perms.sql +\i sql/func.sql +\i sql/rebuild-search-cache.sql + + +-- Part two, can be done after the site has been reloaded with the new code + +ALTER TABLE chars DROP COLUMN c_search CASCADE; +ALTER TABLE producers DROP COLUMN c_search CASCADE; +ALTER TABLE releases DROP COLUMN c_search CASCADE; +ALTER TABLE staff_alias DROP COLUMN c_search CASCADE; +ALTER TABLE tags DROP COLUMN c_search CASCADE; +ALTER TABLE traits DROP COLUMN c_search CASCADE; +ALTER TABLE vn DROP COLUMN c_search CASCADE; + +\i sql/schema.sql +\i sql/func.sql +\i sql/perms.sql + +DROP FUNCTION search_gen_vn(vndbid); +DROP FUNCTION search_gen_release(vndbid); +DROP FUNCTION search_gen(text[]); diff --git a/util/updates/2023-04-03-extlinks-booth.sql b/util/updates/2023-04-03-extlinks-booth.sql new file mode 100644 index 00000000..7185b289 --- /dev/null +++ b/util/updates/2023-04-03-extlinks-booth.sql @@ -0,0 +1,57 @@ +ALTER TABLE releases ADD COLUMN l_booth integer NOT NULL DEFAULT 0; +ALTER TABLE releases_hist ADD COLUMN l_booth integer NOT NULL DEFAULT 0; +\i sql/editfunc.sql + +DROP VIEW releasest CASCADE; +\i sql/schema.sql +\i sql/func.sql +\i sql/perms.sql + + +-- Extract from website field +CREATE OR REPLACE FUNCTION migrate_website_to_booth(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET l_booth = regexp_replace(website, '^https?://(?:[a-z0-9_-]+\.)?booth\.pm/(?:[a-z-]+\/)?items/([0-9]+).*', '\1')::int, website = ''; + UPDATE edit_revision SET requester = 'u1', comments = 'Automatic conversion of website to BOOTH link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_website_to_booth(id) FROM releases WHERE NOT hidden AND website ~ '^https?://(?:[a-z0-9_-]+\.)?booth\.pm/(?:[a-z-]+\/)?items/([0-9]+)'; +DROP FUNCTION migrate_website_to_booth(vndbid); + + + +-- Extract from notes in "Available at .." format +CREATE OR REPLACE FUNCTION migrate_notes_to_booth(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET + l_booth = regexp_replace(notes, '^.*\s*(?:Also available|Available) (?:on|at|from) \[url=https?://(?:[a-z0-9_-]+\.)?booth\.pm/(?:[a-z-]+\/)?items/([0-9]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*).*$', '\1', 'i')::int, + notes = regexp_replace(notes, '\s*(?:Also available|Available) (?:on|at|from) \[url=https?://(?:[a-z0-9_-]+\.)?booth\.pm/(?:[a-z-]+\/)?items/([0-9]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*)', '', 'i'); + UPDATE edit_revision SET requester = 'u1', comments = 'Automatic extraction of BOOTH link from the notes.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_notes_to_booth(id) FROM releases WHERE NOT hidden AND l_booth = 0 + AND notes ~* '\s*(?:Also available|Available) (?:on|at|from) \[url=https?://(?:[a-z0-9_-]+\.)?booth\.pm/(?:[a-z-]+\/)?items/([0-9]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*)' + AND id <> 'r104675'; +DROP FUNCTION migrate_notes_to_booth(vndbid); + + + +-- Extract from notes when it's the only thing in the note +CREATE OR REPLACE FUNCTION migrate_notes_to_booth2(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET l_booth = regexp_replace(notes, '^(?:booth|available on)?:?\s*(?:\[url=)?https?://(?:[a-z0-9_-]+\.)?booth\.pm/(?:[a-z-]+\/)?items/([0-9]+)(?:\][^\[]*\[/url\])?\.?$', '\1', 'i')::int, notes = ''; + UPDATE edit_revision SET requester = 'u1', comments = 'Automatic extraction of BOOTH link from the notes.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT migrate_notes_to_booth2(id) FROM releases WHERE NOT hidden AND l_booth = 0 + AND notes ~* '^(?:booth|available on)?:?\s*(?:\[url=)?https?://(?:[a-z0-9_-]+\.)?booth\.pm/(?:[a-z-]+\/)?items/([0-9]+)(?:\][^\[]*\[/url\])?\.?$'; +DROP FUNCTION migrate_notes_to_booth2(vndbid); + + +-- select 'https://vndb.org/'||id, title[2] from releasest where not hidden and notes like '%booth.pm%' order by id; diff --git a/util/updates/2023-04-05-extlinks-patreon-substar.sql b/util/updates/2023-04-05-extlinks-patreon-substar.sql new file mode 100644 index 00000000..1699c924 --- /dev/null +++ b/util/updates/2023-04-05-extlinks-patreon-substar.sql @@ -0,0 +1,102 @@ +ALTER TABLE releases + ADD COLUMN l_patreonp integer NOT NULL DEFAULT 0, + ADD COLUMN l_patreon text NOT NULL DEFAULT '', + ADD COLUMN l_substar text NOT NULL DEFAULT ''; +ALTER TABLE releases_hist + ADD COLUMN l_patreonp integer NOT NULL DEFAULT 0, + ADD COLUMN l_patreon text NOT NULL DEFAULT '', + ADD COLUMN l_substar text NOT NULL DEFAULT ''; +\i sql/editfunc.sql + +DROP VIEW releasest CASCADE; +\i sql/schema.sql +\i sql/func.sql +\i sql/perms.sql + + + +-- patreonp from website field +CREATE OR REPLACE FUNCTION migrate_website_to_patreonp(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET l_patreonp = regexp_replace(website, '^https?://(?:www\.)?patreon\.com/posts/(?:[^/?]+-)?([0-9]+).*$', '\1')::int, website = ''; + UPDATE edit_revision SET requester = 'u1', comments = 'Automatic conversion of website to Patreon link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT count(*) FROM (SELECT migrate_website_to_patreonp(id) FROM releases WHERE NOT hidden AND website ~ '^https?://(?:www\.)?patreon\.com/posts/(?:[^/?]+-)?([0-9]+)') x; +DROP FUNCTION migrate_website_to_patreonp(vndbid); + + + +-- patreon from website field +CREATE OR REPLACE FUNCTION migrate_website_to_patreon(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET l_patreon = regexp_replace(website, '^https?://(?:www\.)?patreon\.com/(?!user[\?/]|posts[\?/]|join[\?/])([^/?]+).*$', '\1'), website = ''; + UPDATE edit_revision SET requester = 'u1', comments = 'Automatic conversion of website to Patreon link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT count(*) FROM (SELECT migrate_website_to_patreon(id) FROM releases WHERE NOT hidden AND website ~ '^https?://(?:www\.)?patreon\.com/(?!user[\?/]|posts[\?/]|join[\?/])([^/?]+)') x; +DROP FUNCTION migrate_website_to_patreon(vndbid); + + + + +-- patreon from notes field +CREATE OR REPLACE FUNCTION migrate_notes_to_patreon(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET + l_patreon = regexp_replace(notes, '^.*\s*(?:Also available|Were only available|Only available|Available) (?:on|at|from) \[url=https?://(?:www\.)?patreon\.com/(?!user[\?/]|posts[\?/]|join[\?/])([^/?]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*).*$', '\1', 'i'), + notes = regexp_replace(notes, '\s*(?:Also available|Were only available|Only available|Available) (?:on|at|from) \[url=https?://(?:www\.)?patreon\.com/(?!user[\?/]|posts[\?/]|join[\?/])([^/?]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*)', '', 'i'); + UPDATE edit_revision SET requester = 'u1', comments = 'Automatic extraction of Patreon link from the notes.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT count(*) FROM (SELECT migrate_notes_to_patreon(id) FROM releases WHERE NOT hidden AND l_patreon = '' + AND notes ~* '\s*(?:Also available|Were only available|Only available|Available) (?:on|at|from) \[url=https?://(?:www\.)?patreon\.com/(?!user[\?/]|posts[\?/]|join[\?/])([^/?]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*)' + AND id NOT IN('r55516', 'r54903', 'r50178') +) x; +DROP FUNCTION migrate_notes_to_patreon(vndbid); + + + + +-- substar from website field +CREATE OR REPLACE FUNCTION migrate_website_to_substar(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET l_substar = regexp_replace(website, '^https?://(?:www\.)?subscribestar\.((?:adult|com)/[^/?]+).*$', '\1'), website = ''; + UPDATE edit_revision SET requester = 'u1', comments = 'Automatic conversion of website to SubscribeStar link.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT count(*) FROM (SELECT migrate_website_to_substar(id) FROM releases WHERE NOT hidden AND website ~ '^https?://(?:www\.)?subscribestar\.((?:adult|com)/[^/?]+)') x; +DROP FUNCTION migrate_website_to_substar(vndbid); + + + + +-- substar from notes field +CREATE OR REPLACE FUNCTION migrate_notes_to_substar(rid vndbid) RETURNS void AS $$ +BEGIN + PERFORM edit_r_init(rid, (SELECT MAX(rev) FROM changes WHERE itemid = rid)); + UPDATE edit_releases SET + l_substar = regexp_replace(notes, '^.*\s*(?:Also available|Were only available|Only available|Available) (?:on|at|from) \[url=https?://(?:www\.)?subscribestar\.((?:adult|com)/[^/?]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*).*$', '\1', 'i'), + notes = regexp_replace(notes, '\s*(?:Also available|Were only available|Only available|Available) (?:on|at|from) \[url=https?://(?:www\.)?subscribestar\.((?:adult|com)/[^/?]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*)', '', 'i'); + UPDATE edit_revision SET requester = 'u1', comments = 'Automatic extraction of SubscribeStar link from the notes.'; + PERFORM edit_r_commit(); +END; +$$ LANGUAGE plpgsql; +SELECT count(*) FROM (SELECT migrate_notes_to_substar(id) FROM releases WHERE NOT hidden AND l_substar = '' + AND notes ~* '\s*(?:Also available|Were only available|Only available|Available) (?:on|at|from) \[url=https?://(?:www\.)?subscribestar\.((?:adult|com)/[^/?]+)[^\]]*\][^\[]+\[/url\](?:\,?$|\.\s*)' +) x; +DROP FUNCTION migrate_notes_to_substar(vndbid); + + + +--select 'https://vndb.org/'||id, title[2], website from releasest where not hidden and website like 'https://www.patreon.com%' order by id; +--select 'https://vndb.org/'||id, title[2] from releasest where not hidden and notes like '%https://www.patreon.com%' order by id; +--select 'https://vndb.org/'||id, title[2] from releasest where not hidden and notes like '%subscribestar%' order by id; diff --git a/util/updates/2023-04-19-images-uploader.sql b/util/updates/2023-04-19-images-uploader.sql new file mode 100644 index 00000000..c6255775 --- /dev/null +++ b/util/updates/2023-04-19-images-uploader.sql @@ -0,0 +1,29 @@ +ALTER TABLE images ADD COLUMN uploader vndbid; +ALTER TABLE images ADD CONSTRAINT images_uploader_fkey FOREIGN KEY (uploader) REFERENCES users (id) ON DELETE SET DEFAULT; + + +-- Attempt to find the original uploader of an image by finding the first +-- change that references it. +WITH cv (id, uid) AS ( + SELECT DISTINCT ON (v.image) v.image, c.requester + FROM vn_hist v + JOIN changes c ON c.id = v.chid + WHERE v.image IS NOT NULL AND c.requester IS NOT NULL AND c.requester <> 'u1' + ORDER BY v.image, v.chid +) UPDATE images SET uploader = uid FROM cv WHERE uploader IS NULL AND cv.id = images.id; + +WITH sf (id, uid) AS ( + SELECT DISTINCT ON (v.scr) v.scr, c.requester + FROM vn_screenshots_hist v + JOIN changes c ON c.id = v.chid + WHERE c.requester IS NOT NULL AND c.requester <> 'u1' + ORDER BY v.scr, v.chid +) UPDATE images SET uploader = uid FROM sf WHERE uploader IS NULL AND sf.id = images.id; + +WITH ch (id, uid) AS ( + SELECT DISTINCT ON (v.image) v.image, c.requester + FROM chars_hist v + JOIN changes c ON c.id = v.chid + WHERE v.image IS NOT NULL AND c.requester IS NOT NULL AND c.requester <> 'u1' + ORDER BY v.image, v.chid +) UPDATE images SET uploader = uid FROM ch WHERE uploader IS NULL AND ch.id = images.id; diff --git a/util/updates/2023-04-19-jastusa-shoplinks.sql b/util/updates/2023-04-19-jastusa-shoplinks.sql new file mode 100644 index 00000000..7d93fe9e --- /dev/null +++ b/util/updates/2023-04-19-jastusa-shoplinks.sql @@ -0,0 +1,8 @@ +CREATE TABLE shop_jastusa ( + lastfetch timestamptz, + deadsince timestamptz, + id text NOT NULL PRIMARY KEY, + price text NOT NULL DEFAULT '', + slug text NOT NULL DEFAULT '' +); +\i sql/perms.sql diff --git a/util/updates/2023-05-03-sql-noquote.sql b/util/updates/2023-05-03-sql-noquote.sql new file mode 100644 index 00000000..9a8168a0 --- /dev/null +++ b/util/updates/2023-05-03-sql-noquote.sql @@ -0,0 +1,23 @@ +ALTER TABLE chars RENAME COLUMN "desc" TO description; +ALTER TABLE chars_hist RENAME COLUMN "desc" TO description; +ALTER TABLE producers RENAME COLUMN "desc" TO description; +ALTER TABLE producers_hist RENAME COLUMN "desc" TO description; +ALTER TABLE staff RENAME COLUMN "desc" TO description; +ALTER TABLE staff_hist RENAME COLUMN "desc" TO description; +ALTER TABLE vn RENAME COLUMN "desc" TO description; +ALTER TABLE vn_hist RENAME COLUMN "desc" TO description; +ALTER TABLE traits RENAME COLUMN "group" TO gid; +ALTER TABLE traits RENAME COLUMN "order" TO gorder; +ALTER TABLE traits_hist RENAME COLUMN "order" TO gorder; + +ALTER TABLE traits DROP CONSTRAINT traits_group_fkey; +ALTER TABLE traits ADD CONSTRAINT traits_gid_fkey FOREIGN KEY (gid) REFERENCES traits (id); + +DROP VIEW charst CASCADE; +DROP VIEW producerst CASCADE; +DROP VIEW staff_aliast CASCADE; +DROP VIEW vnt CASCADE; +\i sql/schema.sql +\i sql/func.sql +\i sql/editfunc.sql +\i sql/perms.sql diff --git a/util/updates/2023-06-19-tags-vn-direct-count.sql b/util/updates/2023-06-19-tags-vn-direct-count.sql new file mode 100644 index 00000000..d784725c --- /dev/null +++ b/util/updates/2023-06-19-tags-vn-direct-count.sql @@ -0,0 +1,4 @@ +ALTER TABLE tags_vn_direct ADD COLUMN count smallint NOT NULL DEFAULT 0; +\i sql/func.sql +SELECT tag_vn_calc(NULL); +ALTER TABLE tags_vn_direct ALTER COLUMN count DROP DEFAULT; diff --git a/util/updates/2023-07-11-vn-rating.sql b/util/updates/2023-07-11-vn-rating.sql new file mode 100644 index 00000000..9996df88 --- /dev/null +++ b/util/updates/2023-07-11-vn-rating.sql @@ -0,0 +1,8 @@ +DROP VIEW vnt CASCADE; +ALTER TABLE vn DROP COLUMN c_popularity; +\i sql/schema.sql +\i sql/func.sql +\i sql/perms.sql +-- Twice, to stabilize the "top50" variable. +SELECT update_vnvotestats(); +SELECT update_vnvotestats(); diff --git a/util/updates/2023-09-15-quotes-rand.sql b/util/updates/2023-09-15-quotes-rand.sql new file mode 100644 index 00000000..001f0770 --- /dev/null +++ b/util/updates/2023-09-15-quotes-rand.sql @@ -0,0 +1,37 @@ +BEGIN; +ALTER TABLE quotes + DROP CONSTRAINT quotes_pkey, + DROP CONSTRAINT quotes_vid_fkey; +ALTER TABLE quotes RENAME TO quotes_old; + +CREATE TABLE quotes ( + vid vndbid NOT NULL, + rand real, + approved boolean NOT NULL DEFAULT FALSE, + quote text NOT NULL, + PRIMARY KEY(vid, quote) +); + +INSERT INTO quotes SELECT vid, NULL, TRUE, quote FROM quotes_old; + +ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +CREATE INDEX quotes_rand ON quotes (rand) WHERE rand IS NOT NULL; + +CREATE OR REPLACE FUNCTION quotes_rand_calc() RETURNS void AS $$ + WITH q(vid,quote) AS ( + SELECT vid, quote FROM quotes q WHERE approved AND EXISTS(SELECT 1 FROM vn v WHERE v.id = q.vid AND NOT v.hidden) + ), r(vid,quote,rand) AS ( + SELECT vid, quote, + -- 'rand' is chosen such that each VN has an equal probability to be selected, regardless of how many quotes it has. + ((dense_rank() OVER (ORDER BY vid)) - 1)::real / (SELECT COUNT(DISTINCT vid) FROM q) + + (percent_rank() OVER (PARTITION BY vid ORDER BY quote)) / (SELECT COUNT(DISTINCT vid)+1 FROM q) + FROM q + ), u AS ( + UPDATE quotes SET rand = NULL WHERE NOT EXISTS(SELECT 1 FROM r WHERE quotes.vid = r.vid AND quotes.quote = r.quote) + ) UPDATE quotes SET rand = r.rand FROM r WHERE r.vid = quotes.vid AND r.quote = quotes.quote +$$ LANGUAGE SQL; + +SELECT quotes_rand_calc(); +COMMIT; + +\i sql/perms.sql diff --git a/util/updates/2023-09-17-wikidata-props.sql b/util/updates/2023-09-17-wikidata-props.sql new file mode 100644 index 00000000..1e58f42a --- /dev/null +++ b/util/updates/2023-09-17-wikidata-props.sql @@ -0,0 +1,3 @@ +ALTER TABLE wikidata + ADD COLUMN lutris text[], + ADD COLUMN wine integer[]; diff --git a/util/updates/2023-09-21-reset-throttle.sql b/util/updates/2023-09-21-reset-throttle.sql new file mode 100644 index 00000000..3557f66e --- /dev/null +++ b/util/updates/2023-09-21-reset-throttle.sql @@ -0,0 +1,5 @@ +CREATE TABLE reset_throttle ( + ip inet NOT NULL PRIMARY KEY, + timeout timestamptz NOT NULL +); +\i sql/perms.sql diff --git a/util/updates/2023-10-14-drm.sql b/util/updates/2023-10-14-drm.sql new file mode 100644 index 00000000..d7c55982 --- /dev/null +++ b/util/updates/2023-10-14-drm.sql @@ -0,0 +1,7 @@ +\i sql/schema.sql +\i sql/tableattrs.sql +\i sql/editfunc.sql +\i sql/func.sql +\i sql/perms.sql + +INSERT INTO drm VALUES (0, 0, 0, false, false, false, false, false, false, false, false, 'DRM-free', 'This release is available without DRM.'); diff --git a/util/updates/2023-12-03-staff-aid.sql b/util/updates/2023-12-03-staff-aid.sql new file mode 100644 index 00000000..f6deb3e6 --- /dev/null +++ b/util/updates/2023-12-03-staff-aid.sql @@ -0,0 +1,11 @@ +ALTER TABLE staff RENAME COLUMN aid TO main; +ALTER TABLE staff_hist RENAME COLUMN aid TO main; + +ALTER TABLE staff DROP CONSTRAINT staff_aid_fkey; +ALTER TABLE staff ADD CONSTRAINT staff_main_fkey FOREIGN KEY (main) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED; + +DROP VIEW staff_aliast CASCADE; +\i sql/schema.sql +\i sql/func.sql +\i sql/editfunc.sql +\i sql/perms.sql diff --git a/util/updates/2023-12-03-staff-extlinks.sql b/util/updates/2023-12-03-staff-extlinks.sql new file mode 100644 index 00000000..96ef0501 --- /dev/null +++ b/util/updates/2023-12-03-staff-extlinks.sql @@ -0,0 +1,24 @@ +ALTER TABLE staff + ADD COLUMN l_vgmdb integer NOT NULL DEFAULT 0, + ADD COLUMN l_discogs integer NOT NULL DEFAULT 0, + ADD COLUMN l_mobygames integer NOT NULL DEFAULT 0, + ADD COLUMN l_bgmtv integer NOT NULL DEFAULT 0, + ADD COLUMN l_imdb integer NOT NULL DEFAULT 0, + ADD COLUMN l_vndb vndbid, + ADD COLUMN l_mbrainz uuid, + ADD COLUMN l_scloud text NOT NULL DEFAULT ''; +ALTER TABLE staff_hist + ADD COLUMN l_vgmdb integer NOT NULL DEFAULT 0, + ADD COLUMN l_discogs integer NOT NULL DEFAULT 0, + ADD COLUMN l_mobygames integer NOT NULL DEFAULT 0, + ADD COLUMN l_bgmtv integer NOT NULL DEFAULT 0, + ADD COLUMN l_imdb integer NOT NULL DEFAULT 0, + ADD COLUMN l_vndb vndbid, + ADD COLUMN l_mbrainz uuid, + ADD COLUMN l_scloud text NOT NULL DEFAULT ''; + +DROP VIEW staff_aliast CASCADE; +\i sql/schema.sql +\i sql/editfunc.sql +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2024-02-23-quotes.sql b/util/updates/2024-02-23-quotes.sql new file mode 100644 index 00000000..810a2bec --- /dev/null +++ b/util/updates/2024-02-23-quotes.sql @@ -0,0 +1,89 @@ +BEGIN; + +CREATE TABLE quotes_tmp ( + id serial PRIMARY KEY, + vid vndbid NOT NULL, + cid vndbid, + addedby vndbid, + rand real, + score smallint NOT NULL DEFAULT 0, + state smallint NOT NULL DEFAULT 0, + quote text NOT NULL +); + +CREATE TABLE quotes_log ( + date timestamptz NOT NULL DEFAULT NOW(), + id integer NOT NULL, + uid vndbid, + action text NOT NULL +); + +CREATE TABLE quotes_votes ( + date timestamptz NOT NULL DEFAULT NOW(), + id integer NOT NULL, + uid vndbid NOT NULL, + vote smallint NOT NULL, + PRIMARY KEY(id, uid) +); + +WITH s (date, uid, vid, quote) AS ( + SELECT DISTINCT ON (detail) date, by_uid, regexp_replace(detail, '^([^ ]+): .+$', '\1', '')::vndbid, regexp_replace(detail, '^[^ ]+: (.+)$', '\1', '') + FROM audit_log a + WHERE action = 'submit quote' + AND EXISTS(SELECT 1 FROM users WHERE id = by_uid) + ORDER BY detail, date +), q AS ( + INSERT INTO quotes_tmp (vid, rand, addedby, state, quote, score) +SELECT q.vid, q.rand, s.uid, CASE WHEN q.approved THEN 1 ELSE 0 END, q.quote, 1 + FROM quotes q + LEFT JOIN s ON s.vid = q.vid AND s.quote = q.quote + ORDER BY s.date NULLS FIRST + RETURNING id, vid, quote +), l AS ( + INSERT INTO quotes_log + SELECT COALESCE(s.date, '2023-09-15 12:00 UTC'), q.id, s.uid, CASE WHEN s.uid IS NULL THEN 'Added to the database before the submission form existed' ELSE 'Submitted' END + FROM q LEFT JOIN s ON s.vid = q.vid AND s.quote = q.quote + RETURNING date, id, uid +) INSERT INTO quotes_votes + SELECT date, id, COALESCE(uid, 'u1'), 1 FROM l; + + +DROP TABLE quotes; +ALTER TABLE quotes_tmp RENAME TO quotes; +ALTER INDEX quotes_tmp_pkey RENAME TO quotes_pkey; +ALTER SEQUENCE quotes_tmp_id_seq RENAME TO quotes_id_seq; + + +CREATE INDEX quotes_rand ON quotes (rand) WHERE rand IS NOT NULL; +CREATE INDEX quotes_vid ON quotes (vid); +CREATE INDEX quotes_log_id ON quotes_log (id); +ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE quotes ADD CONSTRAINT quotes_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE quotes ADD CONSTRAINT quotes_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE quotes_log ADD CONSTRAINT quotes_log_id_fkey FOREIGN KEY (id) REFERENCES quotes (id) ON DELETE CASCADE; +ALTER TABLE quotes_log ADD CONSTRAINT quotes_log_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE quotes_votes ADD CONSTRAINT quotes_votes_id_fkey FOREIGN KEY (id) REFERENCES quotes (id) ON DELETE CASCADE; +ALTER TABLE quotes_votes ADD CONSTRAINT quotes_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + + +GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_site; +GRANT SELECT, INSERT, UPDATE ON quotes TO vndb_site; +GRANT SELECT, INSERT ON quotes_log TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON quotes_votes TO vndb_site; +GRANT SELECT, UPDATE ON quotes TO vndb_multi; + + +CREATE OR REPLACE FUNCTION update_quotes_votes_cache() RETURNS trigger AS $$ +BEGIN + UPDATE quotes + SET score = (SELECT SUM(vote) FROM quotes_votes WHERE quotes_votes.id = quotes.id) + WHERE id IN(OLD.id, NEW.id); + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +CREATE TRIGGER quotes_votes_cache AFTER INSERT OR UPDATE OR DELETE ON quotes_votes FOR EACH ROW EXECUTE PROCEDURE update_quotes_votes_cache(); + +COMMIT; + +\i sql/func.sql diff --git a/util/updates/2024-02-26-quotes-adjustments.sql b/util/updates/2024-02-26-quotes-adjustments.sql new file mode 100644 index 00000000..bbb09801 --- /dev/null +++ b/util/updates/2024-02-26-quotes-adjustments.sql @@ -0,0 +1,12 @@ +BEGIN; +ALTER TABLE quotes + ADD COLUMN hidden boolean NOT NULL DEFAULT FALSE, + ADD COLUMN added timestamptz NOT NULL DEFAULT NOW(); +UPDATE quotes SET hidden = true WHERE state = 2; +ALTER TABLE quotes DROP COLUMN state; + +CREATE INDEX quotes_addedby ON quotes (addedby); + +COMMIT; + +\i sql/func.sql diff --git a/util/updates/2024-03-01-reports-log.sql b/util/updates/2024-03-01-reports-log.sql new file mode 100644 index 00000000..51c7d6f2 --- /dev/null +++ b/util/updates/2024-03-01-reports-log.sql @@ -0,0 +1,14 @@ +CREATE TABLE reports_log ( + date timestamptz NOT NULL DEFAULT NOW(), + id integer NOT NULL, + status report_status NOT NULL, + uid vndbid, + message text NOT NULL +); + +CREATE INDEX reports_log_id ON reports_log (id); + +ALTER TABLE reports_log ADD CONSTRAINT reports_log_id_fkey FOREIGN KEY (id) REFERENCES reports (id); +ALTER TABLE reports_log ADD CONSTRAINT reports_log_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; + +GRANT SELECT, INSERT ON reports_log TO vndb_site; diff --git a/util/updates/2024-03-08-belarusian-language.sql b/util/updates/2024-03-08-belarusian-language.sql new file mode 100644 index 00000000..23520558 --- /dev/null +++ b/util/updates/2024-03-08-belarusian-language.sql @@ -0,0 +1 @@ +ALTER TYPE language ADD VALUE 'be' AFTER 'ar'; diff --git a/util/updates/2024-03-14-sql-email-normalization.sql b/util/updates/2024-03-14-sql-email-normalization.sql new file mode 100644 index 00000000..3e9eb93f --- /dev/null +++ b/util/updates/2024-03-14-sql-email-normalization.sql @@ -0,0 +1,9 @@ +\i sql/util.sql + +DROP INDEX users_shadow_mail; +CREATE INDEX users_shadow_mail ON users_shadow (hash_email(mail)); + +DROP FUNCTION user_emailtoid(text); +DROP FUNCTION user_resetpass(text, bytea); + +\i sql/func.sql diff --git a/util/updates/2024-03-20-account-softdelete.sql b/util/updates/2024-03-20-account-softdelete.sql new file mode 100644 index 00000000..1f1cb055 --- /dev/null +++ b/util/updates/2024-03-20-account-softdelete.sql @@ -0,0 +1,11 @@ +CREATE TABLE email_optout ( + mail uuid, -- hash_email() + date timestamptz NOT NULL DEFAULT NOW(), + PRIMARY KEY (mail) +); + +ALTER TABLE users ALTER COLUMN username DROP NOT NULL; +ALTER TABLE audit_log ALTER COLUMN by_ip DROP NOT NULL; + +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/2024-03-22-delayed-account-deletion.sql b/util/updates/2024-03-22-delayed-account-deletion.sql new file mode 100644 index 00000000..8d55fc13 --- /dev/null +++ b/util/updates/2024-03-22-delayed-account-deletion.sql @@ -0,0 +1,4 @@ +ALTER TABLE users_shadow ADD COLUMN delete_at timestamptz; + +\i sql/func.sql +\i sql/perms.sql diff --git a/util/updates/README.md b/util/updates/README.md new file mode 100644 index 00000000..a6032c4b --- /dev/null +++ b/util/updates/README.md @@ -0,0 +1,51 @@ +# SQL Update Scripts + +This directory contains scripts to keep the live database schema synchronized +with the code in the git repo, in particular with the definitions in the `sql/` +directory. + +## Naming scheme + +```sh +`date +%F`-description.sql +``` + +The date is the date on which the script is applied to the production database. +For work-in-progress updates where that date is not yet known, use a `wip-` +prefix instead. + +(The older `update_{date}.sql` naming scheme is deprecated) + +## Applying the updates + +Do not blindly apply these scripts in order and expect them to work. Since the +scripts were written for the sole purpose of updating the live production +database - which only needs to happen once per update - I often take some +shortcuts: + +- The scripts often directly import other scripts from `sql/`. Later changes to + files in `sql/` may break the update scripts, so generally the safest way to + apply a particular script is to find the latest commit where the script has + been edited, then do a checkout of that commit and run the script in that + context. +- Always run `make` before running a script, it may rely on `sql/editfunc.sql`. +- Not all changes get an update script. Sometimes just running `sql/func.sql` + is sufficient to apply a change. In rare cases an update requires a full dump + & reload using `util/dbdump.pl export-data`, such as changes to column order + (which I sometimes do around a PostgreSQL version upgrade since those can + benefit from a dump & reload anyway) or changes to the definition of an + important data type (`vndbid` in particular, but such changes should be very + rare). + +## Downtime + +I'm not consistent with respect to whether these scripts can be run without +downtime. Most scripts work just fine while the site is up and running, others +may require that the site is taken down for a few minutes. + +Likewise, some scripts will leave the database in a state that an already +running process can't deal with. That may result in some 500 errors until the +process is restarted with the new code. + +Scripts often contain comments regarding the above. They're worth reading +before applying, in any case. diff --git a/util/vndb-dev-server.pl b/util/vndb-dev-server.pl index a7571e10..214b0ee0 100755 --- a/util/vndb-dev-server.pl +++ b/util/vndb-dev-server.pl @@ -15,6 +15,8 @@ use Cwd 'abs_path'; my $listen_port = $ENV{TUWF_HTTP_SERVER_PORT} || 3000; $ENV{TUWF_HTTP_SERVER_PORT} = $listen_port+1; +$ENV{VNDB_VAR} //= 'var'; + my($pid, $prog, $killed); sub prog_start { @@ -58,7 +60,7 @@ sub make_run { print "\n" if !$newline++; print $d; }; - my $cb = run_cmd "cd $ROOT && make", '>', $out, '2>', $out; + my $cb = run_cmd "cd $ROOT && make -j4", '>', $out, '2>', $out; $cb->recv; print "\n" if $newline; } @@ -100,10 +102,8 @@ sub checkmod { }, "$ROOT/lib"; chdir $ROOT; - $check->($_) for (qw{ - util/vndb.pl - data/conf.pl - }); + $check->('util/vndb.pl'); + $check->("$ENV{VNDB_VAR}/conf.pl"); my $ismod = $newlastmod > $lastmod; $lastmod = $newlastmod; diff --git a/util/vndb.pl b/util/vndb.pl index 49e27b6a..6690f4c9 100755 --- a/util/vndb.pl +++ b/util/vndb.pl @@ -1,79 +1,98 @@ #!/usr/bin/perl +# Usage: +# vndb.pl # Run from the CLI to get a webserver, or spawn from CGI/FastCGI +# vndb.pl noapi # Same, but disable /api/ calls +# vndb.pl onlyapi # Same, but disable everything but /api/ calls +# +# vndb.pl elmgen # Generate Elm files and quit + use v5.24; use warnings; use Cwd 'abs_path'; -use TUWF ':html_'; +use JSON::XS; +use TUWF ':html5_'; use Time::HiRes 'time'; $|=1; # Disable buffering on STDOUT, otherwise vndb-dev-server.pl won't pick up our readyness notification. -my $ROOT; -BEGIN { ($ROOT = abs_path $0) =~ s{/util/vndb\.pl$}{}; } +# Force the pure-perl AnyEvent backend; More lightweight and we don't need the +# performance of EV. Fixes an issue with subprocess spawning under TUWF's +# built-in web server that I haven't been able to track down. +BEGIN { $ENV{PERL_ANYEVENT_MODEL} = 'Perl'; } + + +our($ROOT, $NOAPI, $ONLYAPI); +BEGIN { + ($ROOT = abs_path $0) =~ s{/util/vndb\.pl$}{}; + ($NOAPI) = grep $_ eq 'noapi', @ARGV; + ($ONLYAPI) = grep $_ eq 'onlyapi', @ARGV; +} use lib $ROOT.'/lib'; -use SkinFile; -use VNDB::Func (); use VNDB::Config; use VNWeb::Auth; use VNWeb::HTML (); use VNWeb::Validation (); +use VNWeb::TitlePrefs (); +use VNWeb::TimeZone (); - -# load the skins -my $skin = SkinFile->new("$ROOT/static/s"); -tuwf->{skins} = { map +($_ => [ $skin->get($_, 'name'), $skin->get($_, 'userid') ]), $skin->list }; - -# Some global variables -tuwf->{scr_size} = [ 136, 102 ]; # w*h of screenshot thumbnails -tuwf->{ch_size} = [ 256, 300 ]; # max. w*h of char images -tuwf->{cv_size} = [ 256, 400 ]; # max. w*h of cover images -tuwf->{$_} = config->{$_} for keys %{ config() }; - +$ENV{TZ} = 'UTC'; TUWF::set %{ config->{tuwf} }; +TUWF::set import_modules => 0; +TUWF::set db_login => sub { + DBI->connect(config->{tuwf}{db_login}->@*, { PrintError => 0, RaiseError => 1, AutoCommit => 0, pg_enable_utf8 => 1, ReadOnly => 1 }) +} if config->{read_only}; # Signal to VNWeb::Elm whether it should generate the Elm files. # Should be done before loading any more modules. tuwf->{elmgen} = $ARGV[0] && $ARGV[0] eq 'elmgen'; -sub _path { - my($t, $id) = $_[1] =~ /([a-z]+)([0-9]+)/; - $t = 'st' if $t eq 'sf' && $_[2]; - sprintf '%s/%s/%02d/%d.jpg', $_[0], $t, $id%100, $id; -} - -# tuwf->imgpath($image_id, $thumb) -sub TUWF::Object::imgpath { _path "$ROOT/static", $_[1], $_[2] } - -# tuwf->imgurl($image_id, $thumb) -sub TUWF::Object::imgurl { _path $_[0]{url_static}, $_[1], $_[2] } - -# tuwf->samesite() - returns true if this request originated from the same site, i.e. not an external referer. -sub TUWF::Object::samesite { !!tuwf->reqCookie('samesite') } +TUWF::hook before => sub { + return if VNWeb::Validation::is_api; + # Serve static files from www/ + if(tuwf->resFile(config->{var_path}.'/www', tuwf->reqPath)) { + tuwf->resHeader('Cache-Control' => 'max-age=86400'); + tuwf->done; + } -TUWF::hook before => sub { - # If we're running standalone, serve www/ and static/ too. - if(tuwf->{_TUWF}{http}) { - if(tuwf->resFile("$ROOT/www", tuwf->reqPath) || tuwf->resFile("$ROOT/static", tuwf->reqPath)) { - tuwf->resHeader('Cache-Control' => 'max-age=31536000'); - tuwf->done; - } + # If we're running standalone, serve static/ too. + if(tuwf->{_TUWF}{http} && ( + tuwf->resFile(config->{var_path}.'/static', tuwf->reqPath) || + tuwf->resFile(config->{gen_path}.'/static', tuwf->reqPath) || + tuwf->resFile("$ROOT/static", tuwf->reqPath) + )) { + tuwf->resHeader('Cache-Control' => 'max-age=31536000'); + tuwf->done; } # Use a 'SameSite=Strict' cookie to determine whether this page was loaded from internal or external. # Ought to be more reliable than checking the Referer header, but it's unfortunately a bit uglier. - tuwf->resCookie(samesite => 1, httponly => 1, samesite => 'Strict') if !tuwf->samesite; + tuwf->resCookie(samesite => 1, httponly => 1, samesite => 'Strict') if !VNWeb::Validation::samesite; tuwf->req->{trace_start} = time if config->{trace_log}; +} if !$ONLYAPI; + + +# Provide a default /robots.txt +TUWF::get '/robots.txt', sub { + tuwf->resHeader('Content-Type' => 'text/plain'); + lit_ "User-agent: *\nDisallow: /\n"; }; +TUWF::set error_400_handler => sub { + return eval { VNWeb::API::err(400, 'Invalid request (most likely: invalid JSON or non-UTF8 data).') } if VNWeb::Validation::is_api; + TUWF::_error_400(); +}; + TUWF::set error_404_handler => sub { + return eval { VNWeb::API::err(404, 'Not found.') } if VNWeb::Validation::is_api; tuwf->resStatus(404); VNWeb::HTML::framework_ title => 'Page Not Found', noindex => 1, sub { - div_ class => 'mainbox', sub { + article_ sub { h1_ 'Page not found'; div_ class => 'warning', sub { h2_ 'Oops!'; @@ -86,11 +105,16 @@ TUWF::set error_404_handler => sub { } }; +TUWF::set error_500_handler => sub { + return eval { VNWeb::API::err(500, 'Internal server error. Can be temporary, but usually points to a server bug.') } if VNWeb::Validation::is_api; + TUWF::_error_500(); +}; + sub TUWF::Object::resDenied { tuwf->resStatus(403); VNWeb::HTML::framework_ title => 'Access Denied', noindex => 1, sub { - div_ class => 'mainbox', sub { + article_ sub { h1_ 'Access Denied'; div_ class => 'warning', sub { if(!auth) { @@ -102,6 +126,8 @@ sub TUWF::Object::resDenied { a_ href => '/u/register', 'create an account'; txt_ " if you don't have one yet."; } + } elsif(VNWeb::DB::global_settings()->{lockdown_edit} || VNWeb::DB::global_settings()->{lockdown_board}) { + h2_ 'The database is in temporary lockdown.'; } else { h2_ 'You are not allowed to perform this action.'; p_ 'You do not have the proper rights to perform the action you wanted to perform.'; @@ -112,36 +138,37 @@ sub TUWF::Object::resDenied { } -# Intercept TUWF::any() and TUWF::register() to figure out which module is processing the request. -if(config->{trace_log}) { - my sub wrap { - my $f = shift; - sub { - my $i = 0; - my $loc = ['',0]; - while(my($pack, undef, $line) = caller($i++)) { - if($pack !~ '^(?:main|TUWF|VNWeb::Elm)') { - $loc = [$pack,$line]; - last; - } - } - my sub subwrap { my $sub = shift; sub { tuwf->req->{trace_loc} = $loc; $sub->(@_) } } - $f->(map ref($_) eq 'CODE' ? subwrap($_) : $_, @_) - } - } +# Intercept TUWF::any() to figure out which module is processing the request. +# Used by VNWeb::HTML::framework_ and trace logging. +{ no warnings 'redefine'; - my $x = \&TUWF::register; *TUWF::register = wrap($x); - my $y = \&TUWF::any; *TUWF::any = wrap($y); + my $f = \&TUWF::any; + *TUWF::any = sub { + my($meth, $path, $sub) = @_; + my $i = 0; + my $loc = ['',0]; + while(my($pack, undef, $line, undef, undef, undef, undef, $is_require) = caller($i++)) { + last if $is_require; + $loc = [$pack,$line]; + } + $f->($meth, $path, sub { tuwf->req->{trace_loc} = $loc; $sub->(@_) }); + }; } -TUWF::load_recursive('VNDB::Util', 'VNDB::DB', 'VNDB::Handler'); -TUWF::set import_modules => 0; -TUWF::load_recursive('VNWeb'); +if($ONLYAPI) { + require VNWeb::API; +} else { + TUWF::load_recursive('VNWeb'); +} TUWF::hook after => sub { return if rand() > config->{trace_log} || !tuwf->req->{trace_start}; my $sqlt = List::Util::sum(map $_->[2], tuwf->{_TUWF}{DB}{queries}->@*); - my %elm = map +($_->[0], 1), tuwf->req->{pagevars}{elm}->@*; + my %js = ( + (map +("$_.js",1), keys tuwf->req->{js}->%*), + (map +($_,1), keys tuwf->req->{pagevars}{widget}->%*), + (map +($_->[0], 1), tuwf->req->{pagevars}{elm}->@*) + ); tuwf->dbExeci('INSERT INTO trace_log', { method => tuwf->reqMethod(), path => tuwf->reqPath(), @@ -153,7 +180,7 @@ TUWF::hook after => sub { perl_time => time() - tuwf->req->{trace_start}, has_txn => VNWeb::DB::sql('txid_current_if_assigned() IS NOT NULL'), loggedin => auth?1:0, - elm_mods => '{'.join(',', sort keys %elm).'}' + js => '{'.join(',', sort keys %js).'}' }); } if config->{trace_log}; |
