summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2018-06-03 19:10:57 +0200
committerYorhel <git@yorhel.nl>2018-06-03 19:10:57 +0200
commit0c0007630fd310bc6857a94fe65703d1fb446177 (patch)
tree9dd9f1c2b0d09460898ca580fe9067e6e9670269
parentb0f7086211dd326ef612d30561ddc69e7abf98fb (diff)
Add development DB dump, remove old devdb, convert README to markdown
-rw-r--r--README133
-rw-r--r--README.md112
-rwxr-xr-xutil/devdump.pl203
-rwxr-xr-xutil/docker-init.sh30
-rwxr-xr-xutil/gendevdb.sh26
-rw-r--r--util/sql/all.sql17
-rw-r--r--util/sql/data.sql17
-rw-r--r--util/sql/devdb.sql361
-rw-r--r--util/sql/tableattrs.sql12
9 files changed, 358 insertions, 553 deletions
diff --git a/README b/README
deleted file mode 100644
index 9ea26f7e..00000000
--- a/README
+++ /dev/null
@@ -1,133 +0,0 @@
-The VNDB.org Source Code
-------------------------
-
-Quick and dirty setup using Docker
-
- # Setup
- docker build -t vndb .
- docker volume create --name vndb-data
-
- # Run (will run on the foreground)
- docker run -ti --name vndb -p 3000:3000 -v vndb-data:/var/lib/postgresql -v "`pwd`":/var/www --rm vndb
-
- # While running, if you need another terminal into the container
- docker exec -ti vndb bash # root shell
- docker exec -ti vndb su -l devuser # development shell
- docker exec -ti vndb su postgres -c psql # postgres superuser shell
- docker exec -ti vndb su devuser -c 'psql -U vndb' # postgres vndb shell
-
-
-Development database
-
- There's a small database with a few pre-filled entries for testing purposes.
- It is automatically imported in the Docker image, but you can also manually
- import it as follows (requires an initialized DB and needs to be run with a
- superuser account. WARNING: This throws away anything in the current DB):
-
- psql -U postgres vndb -1f util/sql/devdb.sql
-
- This database includes two user accounts with the following login:
-
- admin / hunter2
- user / hunter1
-
- (The development database is still very minimal, feel free to upload an
- improved DB dump to the forums if you have more data)
-
-
-Requirements (when not using Docker)
-
- global requirements:
- Linux, or an OS that resembles Linux. Chances are VNDB won't run on Windows.
- PostgreSQL 9.3+ (don't try older versions or other SQL databases, it won't work)
- perl 5.22 recommended, 5.10+ may also work
- A webserver that works with TUWF (lighttpd and Apache are known to work)
-
- (perl 5.22 core modules are not listed.)
-
- General:
- Crypt::ScryptKDF
- Crypt::URandom
- DBD::Pg
- DBI
- Image::Magick
- JSON::XS
- Tie::IxHash
-
- util/vndb.pl:
- Algorithm::Diff::XS
- Text::MultiMarkdown
- TUWF
- HTTP::Server::Simple (optional, but greatly simplifies development setup)
- FCGI (optional, for running as a FastCGI script)
- PerlIO::gzip (optional, for output compression)
-
- util/multi.pl:
- APIDump:
- PerlIO::gzip
- Core:
- AnyEvent
- AnyEvent::Pg
- IRC:
- AnyEvent::IRC
- Maintenance:
- PerlIO::gzip
- RG:
- XML::Parser
- graphviz (/usr/bin/dot is used by default)
-
- util/skingen.pl
- CSS::Minifier::XS (optional, minimizes CSS output)
-
- util/jsgen.pl
- JavaScript::Minifier::XS (optional, minimizes JS output)
- uglifyjs (optional, slower but better JS compression)
-
- util/spritegen.pl
- pngcrush (optional)
-
-
-Setup
-
- - Make sure all the required dependencies (see above) are installed
- - Create a suitable data/config.pl, using data/config_example.pl as base.
- - Run the build system:
-
- make
-
- - Setup a PostgreSQL server and make sure you can login with some admin user
- - Initialize the VNDB database (assuming 'postgres' is a superuser):
-
- # Create the database & roles
- psql -U postgres -f util/sql/superuser_init.sql
-
- # Set a password for each database role:
- echo "ALTER ROLE vndb LOGIN PASSWORD 'pwd1'" | psql -U postgres
- echo "ALTER ROLE vndb_site LOGIN PASSWORD 'pwd2'" | psql -U postgres
- echo "ALTER ROLE vndb_multi LOGIN PASSWORD 'pwd3'" | psql -U postgres
-
- # Now import the rest
- psql -U vndb -f util/sql/all.sql
-
- - Update the vndb_site password in data/config.pl to whatever you set it in
- the previous step.
- - (Optional) Import the "Development database" as explained above.
- - Now simply run:
-
- util/vndb-dev-server.pl
-
- (Note: At the time of writing, the above command will require the git
- version of TUWF installed, but I intent to upload a new version to CPAN
- after a bit more testing).
-
-
-Contact
-
- IRC: #vndb @ irc.synirc.net
- Email: contact@vndb.org
-
-
-License
-
- GNU AGPL, see COPYING file for details.
-
diff --git a/README.md b/README.md
new file mode 100644
index 00000000..7ddc7755
--- /dev/null
+++ b/README.md
@@ -0,0 +1,112 @@
+# The VNDB.org Source Code
+
+## Quick and dirty setup using Docker
+
+Setup:
+
+```
+ docker build -t vndb .
+ docker volume create --name vndb-data
+```
+
+Run (will run on the foreground):
+
+```
+ docker run -ti --name vndb -p 3000:3000 -v vndb-data:/var/lib/postgresql -v "`pwd`":/var/www --rm vndb
+```
+
+While running, if you need another terminal into the container:
+
+```
+ docker exec -ti vndb bash # root shell
+ docker exec -ti vndb su -l devuser # development shell
+ docker exec -ti vndb su postgres -c psql # postgres superuser shell
+ docker exec -ti vndb su devuser -c 'psql -U vndb' # postgres vndb shell
+```
+
+
+## Development database
+
+There is a development database available for download at
+[https://vndb.org/d8#3](https://vndb.org/d8#3).
+When you first run the docker image, you will be asked whether you want to
+download and import this database. If you do not use docker, you can import
+this database manually as follows:
+
+- Follow the steps below to setup PostgreSQL and initialze the database
+- Download and extract the development database
+- psql -U vndb -f dump.sql
+
+
+## Requirements (when not using Docker)
+
+Global requirements:
+
+- Linux, or an OS that resembles Linux. Chances are VNDB won't run on Windows.
+- PostgreSQL 9.10 (older versions may work)
+- perl 5.24 recommended, 5.10+ may also work
+
+**Perl modules** (core modules are not listed):
+
+General:
+- Crypt::ScryptKDF
+- Crypt::URandom
+- DBD::Pg
+- DBI
+- Image::Magick
+- JSON::XS
+- PerlIO::gzip
+- Tie::IxHash
+
+util/vndb.pl (the web backend):
+- Algorithm::Diff::XS
+- Text::MultiMarkdown
+- TUWF
+- HTTP::Server::Simple
+
+util/multi.pl (application server, optional):
+- AnyEvent
+- AnyEvent::Pg
+- AnyEvent::IRC
+- XML::Parser
+- graphviz (/usr/bin/dot is used by default)
+
+
+## Setup
+
+- Make sure all the required dependencies (see above) are installed
+- Create a suitable data/config.pl, using data/config_example.pl as base.
+- Run the build system:
+
+```
+ make
+```
+
+- Setup a PostgreSQL server and make sure you can login with some admin user
+- Initialize the VNDB database (assuming 'postgres' is a superuser):
+
+```
+ # Create the database & roles
+ psql -U postgres -f util/sql/superuser_init.sql
+
+ # Set a password for each database role:
+ echo "ALTER ROLE vndb LOGIN PASSWORD 'pwd1'" | psql -U postgres
+ echo "ALTER ROLE vndb_site LOGIN PASSWORD 'pwd2'" | psql -U postgres
+ echo "ALTER ROLE vndb_multi LOGIN PASSWORD 'pwd3'" | psql -U postgres
+
+ # Now import the rest
+ psql -U vndb -f util/sql/all.sql
+```
+
+- Update the vndb_site password in data/config.pl to whatever you set it in
+ the previous step.
+- (Optional) Import the "Development database" as explained above.
+- Now simply run:
+
+```
+ util/vndb-dev-server.pl
+```
+
+## License
+
+GNU AGPL, see COPYING file for details.
diff --git a/util/devdump.pl b/util/devdump.pl
new file mode 100755
index 00000000..6adc361b
--- /dev/null
+++ b/util/devdump.pl
@@ -0,0 +1,203 @@
+#!/usr/bin/perl
+
+# This script generates the devdump.tar.gz
+# See https://vndb.org/d8#3 for info.
+
+use strict;
+use warnings;
+use autodie;
+use DBI;
+use DBD::Pg;
+
+my $db = DBI->connect('dbi:Pg:dbname=vndb', 'vndb', undef, { RaiseError => 1 });
+
+
+
+# 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(
+ "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(
+ "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"
+);
+
+
+
+# Helper function to copy a table or SQL statement. Can do modifications on a
+# few columns (the $specials).
+sub copy {
+ my($dest, $sql, $specials) = @_;
+
+ $sql ||= "SELECT * FROM $dest";
+ $specials ||= {};
+
+ my @cols = do {
+ my $s = $db->prepare($sql);
+ $s->execute();
+ grep !($specials->{$_} && $specials->{$_} eq 'del'), @{$s->{NAME}}
+ };
+
+ printf "COPY %s (%s) FROM stdin;\n", $dest, join ', ', map "\"$_\"", @cols;
+
+ $sql = "SELECT " . join(',', map {
+ my $s = $specials->{$_} || '';
+ if($s eq 'user') {
+ qq{"$_" % 10 AS "$_"}
+ } else {
+ qq{"$_"}
+ }
+ } @cols) . " FROM ($sql) AS x";
+ #warn $sql;
+ $db->do("COPY ($sql) TO STDOUT");
+ my $v;
+ print $v while $db->pg_getcopydata($v) >= 0;
+ print "\\.\n\n";
+}
+
+
+
+# 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'};
+ 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";
+ }
+}
+
+
+{
+ open my $OUT, '>:utf8', 'dump.sql';
+ select $OUT;
+
+ # Header
+ my @tables = grep !/^multi_/, sort @{ $db->selectcol_arrayref(
+ "SELECT oid::regclass::text FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace"
+ ) };
+ print "\\set ON_ERROR_STOP 1\n";
+ print "BEGIN;\n";
+ printf "TRUNCATE TABLE %s CASCADE;\n", join ',', @tables;
+ print "SET CONSTRAINTS ALL DEFERRED;\n";
+ printf "ALTER TABLE %s DISABLE TRIGGER USER;\n", $_ for @tables;
+
+ # Copy over some required defaults
+ open my $F, '<', 'util/sql/data.sql';
+ print while <$F>;
+ close $F;
+
+ # Copy over all sequence values
+ 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 nextval(?)', {}, $_) for @seq;
+
+ # A few pre-defined users
+ # This password is 'hunter2' with the default salt
+ my $pass = '000100000801ec4185fed438752d6b3b968e2b2cd045f70005cb7e10cafdbb694a82246bd34a065b6e977e0c3dcc';
+ printf "INSERT INTO users (id, username, mail, perm, passwd, email_confirmed) VALUES (%d, '%s', '%s', %d, decode('%s', 'hex'), true);\n", @$_, $pass for(
+ [ 2, 'admin', 'admin@vndb.org', 503 ],
+ [ 3, 'user1', 'user1@vndb.org', 21 ],
+ [ 4, 'user2', 'user2@vndb.org', 21 ],
+ [ 5, 'user3', 'user3@vndb.org', 21 ],
+ [ 6, 'user4', 'user4@vndb.org', 21 ],
+ [ 7, 'user5', 'user5@vndb.org', 21 ],
+ [ 8, 'user6', 'user6@vndb.org', 21 ],
+ [ 9, 'user7', 'user7@vndb.org', 21 ],
+ );
+
+ # Tags & traits
+ copy tags => undef, {addedby => 'user'};
+ copy 'tags_aliases';
+ copy 'tags_parents';
+ copy traits => undef, {addedby => 'user'};
+ copy 'traits_parents';
+
+ # Threads (announcements)
+ my $threads = join ',', @{ $db->selectcol_arrayref("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');
+
+ # Staff
+ copy_entry s => [qw/staff staff_alias/], $staff;
+
+ # Producers (TODO: Relations)
+ copy 'relgraphs', "SELECT DISTINCT ON (r.id) r.* FROM relgraphs r JOIN producers p ON p.rgraph = r.id WHERE p.id IN(".join(',', @$producers).")", {};
+ copy_entry p => [qw/producers/], $producers;
+
+ # Characters
+ copy_entry c => [qw/chars chars_traits chars_vns/], $characters;
+
+ # Visual novels
+ copy screenshots => "SELECT DISTINCT s.* FROM screenshots s JOIN vn_screenshots_hist v ON v.scr = s.id JOIN changes c ON c.id = v.chid WHERE c.type = 'v' AND c.itemid IN($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.type = 'v' AND c.itemid IN($vids)";
+ copy relgraphs => "SELECT DISTINCT ON (r.id) r.* FROM relgraphs r JOIN vn v ON v.rgraph = r.id WHERE v.id IN($vids)", {};
+ copy_entry v => [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@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)";
+ copy votes => "SELECT vid, uid%8+2 AS uid, (percentile_cont((uid%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(date) AS date FROM votes WHERE vid IN($vids) GROUP BY vid, uid%8", {uid => 'user'};
+
+ # Releases
+ copy_entry r => [qw/releases releases_lang releases_media releases_platforms releases_producers releases_vn/], $releases;
+
+ # Caches
+ print "SELECT tag_vn_calc();\n";
+ print "SELECT traits_chars_calc();\n";
+ print "SELECT update_vncache(id) FROM vn;\n";
+ print "UPDATE users u SET c_votes = (SELECT COUNT(*) FROM votes v WHERE v.uid = u.id);\n";
+ 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";
+ # These were copied from Multi::Maintenance
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM users)-1 WHERE section = 'users';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM staff WHERE hidden = FALSE) WHERE section = 'staff';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE state = 2) WHERE section = 'tags';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads WHERE hidden = FALSE) WHERE section = 'threads';\n";
+ print "UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE
+ AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE)) WHERE section = 'threads_posts';\n";
+ # TODO: Something with the 'stats_cache' table. The vn.c_* stats are also inconsistent
+
+ # Footer
+ # Apparently we can't do an ALTER TABLE while the (deferred) foreign key checks
+ # haven't been executed, so do a commit first.
+ print "COMMIT;\n";
+ printf "ALTER TABLE %s ENABLE TRIGGER USER;\n", $_ for @tables;
+
+ select STDOUT;
+ close $OUT;
+}
+
+
+
+
+# Now figure out which images we need, and throw everything in a tarball
+sub imgs { map sprintf('static/%s/%02d/%d.jpg', $_[0], $_%100, $_), @{$_[1]} }
+
+my $ch = $db->selectcol_arrayref("SELECT DISTINCT e.image FROM chars_hist e JOIN changes c ON c.id = e.chid WHERE c.type = 'c' AND e.image <> 0 AND c.itemid IN(".join(',', @$characters).")");
+my $cv = $db->selectcol_arrayref("SELECT DISTINCT e.image FROM vn_hist e JOIN changes c ON c.id = e.chid WHERE c.type = 'v' AND e.image <> 0 AND c.itemid IN($vids)");
+my $sf = $db->selectcol_arrayref("SELECT DISTINCT e.scr FROM vn_screenshots_hist e JOIN changes c ON c.id = e.chid WHERE c.type = 'v' AND c.itemid IN($vids)");
+
+system("tar -czf devdump.tar.gz dump.sql ".join ' ', imgs(ch => $ch), imgs(cv => $cv), imgs(sf => $sf), imgs(st => $sf));
+unlink 'dump.sql';
diff --git a/util/docker-init.sh b/util/docker-init.sh
index d4e0e65c..6beaf9f4 100755
--- a/util/docker-init.sh
+++ b/util/docker-init.sh
@@ -1,4 +1,4 @@
-#!/bin/sh
+#!/bin/bash
if ! test -f /var/vndb-docker-image; then
echo "This script should only be run from within the VNDB docker container."
@@ -45,15 +45,16 @@ pg_init() {
if test -f /var/lib/postgresql/vndb-init-done; then
echo
echo "Database initialization already done."
- echo "Run the following as root to bypass this check:"
- echo " rm /var/lib/postgresql/vndb-init-done"
echo
return
fi
su postgres -c '/var/www/util/docker-init.sh pg_load_superuser'
su devuser -c '/var/www/util/docker-init.sh pg_load_vndb'
- su postgres -c '/var/www/util/docker-init.sh pg_load_devdb'
touch /var/lib/postgresql/vndb-init-done
+
+ echo
+ echo "Database initialization done!"
+ echo
}
# Should run as the postgres user
@@ -69,11 +70,21 @@ pg_load_vndb() {
cd /var/www
make util/sql/editfunc.sql
psql -U vndb -f util/sql/all.sql
-}
-# Should be run as the postgres user
-pg_load_devdb() {
- psql vndb -1f /var/www/util/sql/devdb.sql
+ echo
+ echo "You now have a functional, but empty, database."
+ 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 keep an empty database, y to download the dev database."
+ read -p "Choice: " opt
+ if [[ $opt =~ ^[Yy] ]]
+ then
+ curl https://s.vndb.org/devdump.tar.gz | tar -xzf-
+ psql -U vndb -f dump.sql
+ fi
}
@@ -98,9 +109,6 @@ case "$1" in
pg_load_vndb)
pg_load_vndb
;;
- pg_load_devdb)
- pg_load_devdb
- ;;
devshell)
devshell
;;
diff --git a/util/gendevdb.sh b/util/gendevdb.sh
deleted file mode 100755
index 466d3f65..00000000
--- a/util/gendevdb.sh
+++ /dev/null
@@ -1,26 +0,0 @@
-#!/bin/sh
-
-# This script generates util/sql/devdb.sql from the current DB. It assumes that
-# the DB is accessible through a passwordless 'psql -U vndb'.
-
-# WARNING: This script will throw away sessions and IP addresses from the DB!
-
-psql -U vndb -c 'TRUNCATE sessions'
-psql -U vndb -c "UPDATE users SET ip = '0.0.0.0'"
-psql -U vndb -c "UPDATE changes SET ip = '0.0.0.0'"
-
-cat <<'EOF' >util/sql/devdb.sql
--- See the README for instructions.
--- This file was automatically generated by util/gendevdb.sh.
-
-SET CONSTRAINTS ALL DEFERRED;
--- Hack to disable triggers
-SET session_replication_role = replica;
-EOF
-
-psql -U vndb -qAtc \
- "SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE;'
- FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace"\
- >>util/sql/devdb.sql
-
-pg_dump -U vndb --data-only | grep -Ev '^(--( .*|$))?$' >>util/sql/devdb.sql
diff --git a/util/sql/all.sql b/util/sql/all.sql
index a8009b0d..12f16b11 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -55,20 +55,5 @@ CREATE SEQUENCE charimg_seq;
-- Rows that are assumed to be available
-INSERT INTO users (id, username, mail, perm) VALUES (0, 'deleted', 'del@vndb.org', 0);
-INSERT INTO users (username, mail, perm) VALUES ('multi', 'multi@vndb.org', 0);
-INSERT INTO users_prefs (uid, key, value) VALUES (0, 'notify_nodbedit', '1');
-INSERT INTO users_prefs (uid, key, value) VALUES (1, 'notify_nodbedit', '1');
-
-INSERT INTO stats_cache (section, count) VALUES
- ('users', 1),
- ('vn', 0),
- ('producers', 0),
- ('releases', 0),
- ('chars', 0),
- ('staff', 0),
- ('tags', 0),
- ('traits', 0),
- ('threads', 0),
- ('threads_posts', 0);
+\i util/sql/data.sql
diff --git a/util/sql/data.sql b/util/sql/data.sql
new file mode 100644
index 00000000..b921899b
--- /dev/null
+++ b/util/sql/data.sql
@@ -0,0 +1,17 @@
+INSERT INTO users (id, username, mail, perm) VALUES (0, 'deleted', 'del@vndb.org', 0);
+INSERT INTO users (id, username, mail, perm) VALUES (1, 'multi', 'multi@vndb.org', 0);
+INSERT INTO users_prefs (uid, key, value) VALUES (0, 'notify_nodbedit', '1');
+INSERT INTO users_prefs (uid, key, value) VALUES (1, 'notify_nodbedit', '1');
+SELECT setval('users_id_seq', 2);
+
+INSERT INTO stats_cache (section, count) VALUES
+ ('users', 1),
+ ('vn', 0),
+ ('producers', 0),
+ ('releases', 0),
+ ('chars', 0),
+ ('staff', 0),
+ ('tags', 0),
+ ('traits', 0),
+ ('threads', 0),
+ ('threads_posts', 0);
diff --git a/util/sql/devdb.sql b/util/sql/devdb.sql
deleted file mode 100644
index dea9ab29..00000000
--- a/util/sql/devdb.sql
+++ /dev/null
@@ -1,361 +0,0 @@
--- See the README for instructions.
--- This file was automatically generated by util/gendevdb.sh.
-
-SET CONSTRAINTS ALL DEFERRED;
--- Hack to disable triggers
-SET session_replication_role = replica;
-TRUNCATE TABLE chars_traits_hist, chars_vns_hist, releases_media_hist, releases_lang_hist, releases_platforms_hist, releases_producers_hist, releases_vn_hist, threads_poll_options, threads_poll_votes, vn_relations_hist, vn_seiyuu_hist, vn_anime_hist, affiliate_links, anime, changes, chars, chars_hist, chars_traits, chars_vns, docs, docs_hist, login_throttle, notifications, producers, producers_hist, producers_relations, quotes, releases, releases_hist, releases_lang, releases_media, releases_platforms, releases_producers, releases_vn, relgraphs, rlists, screenshots, staff, staff_alias, staff_alias_hist, staff_hist, stats_cache, tags, tags_aliases, tags_parents, tags_vn, tags_vn_inherit, threads, threads_boards, threads_posts, traits, traits_chars, traits_parents, users, users_prefs, vn, vn_anime, vn_hist, vn_relations, vn_screenshots, vn_screenshots_hist, vn_seiyuu, vn_staff, vn_staff_hist, vnlists, producers_relations_hist, votes, wlists, sessions CASCADE;
-SET statement_timeout = 0;
-SET lock_timeout = 0;
-SET idle_in_transaction_session_timeout = 0;
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = on;
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-SET row_security = off;
-SET search_path = public, pg_catalog;
-COPY releases (id, locked, hidden, title, original, type, website, catalog, gtin, released, notes, minage, patch, freeware, doujin, resolution, voiced, ani_story, ani_ero) FROM stdin;
-1 f f Test tesuto complete 0 20140606 18 f f f 14 4 4 4
-2 f f Test tes complete 0 20150711 9 f f f 0 1 1 1
-3 f f BlazBlue: Centralfiction ブレイブルー セントラルフィクション complete http://store.steampowered.com/app/586140/ 0 20170426 -1 f f f 0 4 3 0
-4 f f BlazBlue: Chronophantasma Extend - Steam Edition complete http://store.steampowered.com/app/388750/ 0 20160312 -1 f f f 0 4 3 0
-\.
-COPY affiliate_links (id, rid, hidden, priority, affiliate, url, version, lastfetch, price, data) FROM stdin;
-\.
-SELECT pg_catalog.setval('affiliate_links_id_seq', 1, false);
-COPY anime (id, year, ann_id, nfo_id, type, title_romaji, title_kanji, lastfetch) FROM stdin;
-\.
-COPY users (id, username, mail, perm, passwd, registered, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed) FROM stdin;
-0 deleted del@vndb.org 0 \\x 2018-01-04 19:13:32.823908+00 0 0 0.0.0.0 0 f f
-6 user user@vndb.org 21 \\x0001000008013fb73157f3cdcd27990198726e7e083007b307adcc70a4705170acd69e307f5e764e031d28d39eb4 2018-01-04 22:25:42.054632+00 0 3 0.0.0.0 0 f f
-4 admin admin@vndb.org 503 \\x000100000801ec4185fed438752d6b3b968e2b2cd045f70005cb7e10cafdbb694a82246bd34a065b6e977e0c3dcc 2018-01-04 19:26:10.731713+00 6 48 0.0.0.0 3 f f
-1 multi multi@vndb.org 0 \\x 2018-01-04 19:13:32.82486+00 0 17 0.0.0.0 0 f f
-\.
-COPY changes (id, type, itemid, rev, added, requester, ip, comments, ihid, ilock) FROM stdin;
-1 v 1 1 2018-01-04 22:30:48.928438+00 4 0.0.0.0 add f f
-2 r 1 1 2018-01-04 22:32:33.447427+00 4 0.0.0.0 add f f
-3 v 1 2 2018-01-06 21:03:51.765904+00 6 0.0.0.0 edit f f
-4 r 2 1 2018-01-10 14:32:33.274624+00 4 0.0.0.0 add f f
-5 p 1 1 2018-02-03 21:31:37.836707+00 4 0.0.0.0 added f f
-6 v 2 1 2018-02-03 21:34:53.920269+00 4 0.0.0.0 added f f
-7 r 3 1 2018-02-03 21:37:09.836493+00 4 0.0.0.0 added f f
-8 v 2 2 2018-02-03 21:39:48.179712+00 4 0.0.0.0 added ss f f
-9 v 3 1 2018-02-03 21:41:23.272482+00 4 0.0.0.0 added f f
-10 r 4 1 2018-02-03 21:43:58.827747+00 4 0.0.0.0 added f f
-11 c 1 1 2018-02-03 21:48:50.156202+00 4 0.0.0.0 added f f
-12 p 2 1 2018-02-03 21:52:48.107812+00 4 0.0.0.0 added f f
-13 c 1 2 2018-02-03 21:56:59.201313+00 4 0.0.0.0 add trait f f
-14 s 1 1 2018-02-03 21:58:23.386769+00 4 0.0.0.0 added f f
-15 v 2 3 2018-02-03 21:59:12.993416+00 4 0.0.0.0 add staff f f
-16 v 2 4 2018-02-03 22:01:48.939799+00 4 0.0.0.0 relation f f
-17 v 3 2 2018-02-03 22:01:48.939799+00 1 0.0.0.0 Reverse relation update caused by revision v2.4 f f
-18 v 1 3 2018-02-03 22:02:58.404134+00 4 0.0.0.0 more info f f
-19 d 2 1 2018-02-08 14:54:17.489737+00 1 0.0.0.0 Empty page f f
-20 d 3 1 2018-02-08 14:54:17.502614+00 1 0.0.0.0 Empty page f f
-21 d 4 1 2018-02-08 14:54:17.511867+00 1 0.0.0.0 Empty page f f
-22 d 5 1 2018-02-08 14:54:17.528422+00 1 0.0.0.0 Empty page f f
-23 d 6 1 2018-02-08 14:54:17.531662+00 1 0.0.0.0 Empty page f f
-24 d 7 1 2018-02-08 14:54:17.54061+00 1 0.0.0.0 Empty page f f
-25 d 9 1 2018-02-08 14:54:17.5497+00 1 0.0.0.0 Empty page f f
-26 d 10 1 2018-02-08 14:54:17.553128+00 1 0.0.0.0 Empty page f f
-27 d 11 1 2018-02-08 14:54:17.564129+00 1 0.0.0.0 Empty page f f
-28 d 12 1 2018-02-08 14:54:17.57348+00 1 0.0.0.0 Empty page f f
-29 d 13 1 2018-02-08 14:54:17.582157+00 1 0.0.0.0 Empty page f f
-30 d 14 1 2018-02-08 14:54:17.590882+00 1 0.0.0.0 Empty page f f
-31 d 15 1 2018-02-08 14:54:17.599512+00 1 0.0.0.0 Empty page f f
-32 d 16 1 2018-02-08 14:54:17.608296+00 1 0.0.0.0 Empty page f f
-\.
-SELECT pg_catalog.setval('changes_id_seq', 32, true);
-SELECT pg_catalog.setval('charimg_seq', 1, false);
-COPY chars (id, locked, hidden, name, original, alias, image, "desc", gender, s_bust, s_waist, s_hip, b_month, b_day, height, weight, bloodt, main, main_spoil) FROM stdin;
-1 f f Celica A. Mercury セリカ゠アヤツキ゠マーキュリー Antenna Girl, Celica Ayatsuki Mercury 0 Hobbies: Strolls\nLikes: Sister, family\nDislikes: Nothing\n\nCelica Ayatsuki Mercury is the younger sister of Nine, Jubei's sister-in-law and aunt of Kokonoe. She is a playable character in Chronophantasma Extend and Centralfiction. After the Dark War, she watched over the Black Beast's remains and built a church. She was also the previous owner of the Nox Nyctores, Deus Machina: Nirvana, but both she and Trinity sealed Nirvana at some point.\n<hidden by spoiler settings>\n\nCelica is a kind, generous, and sociable person who deeply loves her sister and friends. She is a sentimental individual who maintains a firm stance that all life is precious and will use her healing magic to assist those in need of it, regardless of who or what they are. She is also shown to be selfless to the point that she will shield a person with her own body without any hesitation and is willing to sacrifice herself, believing that her sacrifice will be a small price to pay for the chance to defeat the Black Beast and save the world.\nIt has also been noted by people that she has no sense of direction, which usually ends with her getting lost. She is also shown to be very stubborn, such as when she ignores her sister's warnings and heads to Japan by herself to search for her missing father and is also unwilling to admit that she has a poor sense of direction.\n\nCelica has the rare ability to use healing Magic. She has been shown to heal most wounds, although she cannot use her magic to remove illnesses such as seithr poisoning. She has also exhibited the latent ability to suppress seithr, and it is for this reason that Celica is the key to Kushinada's Lynchpin, a device created to seal the seithr within the Gate, and thus disrupt the source of the Black Beast's power, although at the cost of her life. This also makes her the ultimate weakness of the Azure Grimoire as Ragna was unable to see through his right eye and move or use his Grimoire as long as he was near her. f 0 0 0 9 8 160 51 a \N 0
-\.
-COPY chars_hist (chid, name, original, alias, image, "desc", gender, s_bust, s_waist, s_hip, b_month, b_day, height, weight, bloodt, main, main_spoil) FROM stdin;
-11 Celica A. Mercury セリカ゠アヤツキ゠マーキュリー Antenna Girl, Celica Ayatsuki Mercury 0 Hobbies: Strolls\nLikes: Sister, family\nDislikes: Nothing\n\nCelica Ayatsuki Mercury is the younger sister of Nine, Jubei's sister-in-law and aunt of Kokonoe. She is a playable character in Chronophantasma Extend and Centralfiction. After the Dark War, she watched over the Black Beast's remains and built a church. She was also the previous owner of the Nox Nyctores, Deus Machina: Nirvana, but both she and Trinity sealed Nirvana at some point.\n<hidden by spoiler settings>\n\nCelica is a kind, generous, and sociable person who deeply loves her sister and friends. She is a sentimental individual who maintains a firm stance that all life is precious and will use her healing magic to assist those in need of it, regardless of who or what they are. She is also shown to be selfless to the point that she will shield a person with her own body without any hesitation and is willing to sacrifice herself, believing that her sacrifice will be a small price to pay for the chance to defeat the Black Beast and save the world.\nIt has also been noted by people that she has no sense of direction, which usually ends with her getting lost. She is also shown to be very stubborn, such as when she ignores her sister's warnings and heads to Japan by herself to search for her missing father and is also unwilling to admit that she has a poor sense of direction.\n\nCelica has the rare ability to use healing Magic. She has been shown to heal most wounds, although she cannot use her magic to remove illnesses such as seithr poisoning. She has also exhibited the latent ability to suppress seithr, and it is for this reason that Celica is the key to Kushinada's Lynchpin, a device created to seal the seithr within the Gate, and thus disrupt the source of the Black Beast's power, although at the cost of her life. This also makes her the ultimate weakness of the Azure Grimoire as Ragna was unable to see through his right eye and move or use his Grimoire as long as he was near her. f 0 0 0 9 8 160 51 a \N 0
-13 Celica A. Mercury セリカ゠アヤツキ゠マーキュリー Antenna Girl, Celica Ayatsuki Mercury 0 Hobbies: Strolls\nLikes: Sister, family\nDislikes: Nothing\n\nCelica Ayatsuki Mercury is the younger sister of Nine, Jubei's sister-in-law and aunt of Kokonoe. She is a playable character in Chronophantasma Extend and Centralfiction. After the Dark War, she watched over the Black Beast's remains and built a church. She was also the previous owner of the Nox Nyctores, Deus Machina: Nirvana, but both she and Trinity sealed Nirvana at some point.\n<hidden by spoiler settings>\n\nCelica is a kind, generous, and sociable person who deeply loves her sister and friends. She is a sentimental individual who maintains a firm stance that all life is precious and will use her healing magic to assist those in need of it, regardless of who or what they are. She is also shown to be selfless to the point that she will shield a person with her own body without any hesitation and is willing to sacrifice herself, believing that her sacrifice will be a small price to pay for the chance to defeat the Black Beast and save the world.\nIt has also been noted by people that she has no sense of direction, which usually ends with her getting lost. She is also shown to be very stubborn, such as when she ignores her sister's warnings and heads to Japan by herself to search for her missing father and is also unwilling to admit that she has a poor sense of direction.\n\nCelica has the rare ability to use healing Magic. She has been shown to heal most wounds, although she cannot use her magic to remove illnesses such as seithr poisoning. She has also exhibited the latent ability to suppress seithr, and it is for this reason that Celica is the key to Kushinada's Lynchpin, a device created to seal the seithr within the Gate, and thus disrupt the source of the Black Beast's power, although at the cost of her life. This also makes her the ultimate weakness of the Azure Grimoire as Ragna was unable to see through his right eye and move or use his Grimoire as long as he was near her. f 0 0 0 9 8 160 51 a \N 0
-\.
-SELECT pg_catalog.setval('chars_id_seq', 1, true);
-COPY traits (id, name, alias, description, meta, added, state, addedby, "group", "order", sexual, c_items) FROM stdin;
-2 Eyes t 2018-02-03 21:55:40.220142+00 2 4 \N 0 f 0
-1 Tareme Down-turned Eyes This character's eyes are drawn with the top eyelid slanted outwards. This usually produces a weak, gentle look.\n\nDue to it's common usage in anime and games it's often noticeable only in series when other characters don't share the trait or as a contrast to characters with Tsurime. f 2018-02-03 21:55:23.803959+00 2 4 2 0 f 0
-\.
-COPY chars_traits (id, tid, spoil) FROM stdin;
-1 1 0
-\.
-COPY chars_traits_hist (chid, tid, spoil) FROM stdin;
-13 1 0
-\.
-COPY relgraphs (id, svg) FROM stdin;
-\.
-COPY vn (id, locked, hidden, title, original, alias, length, img_nsfw, image, "desc", l_wp, l_encubed, l_renai, rgraph, c_released, c_languages, c_olang, c_platforms, c_popularity, c_rating, c_votecount, c_search) FROM stdin;
-3 f f BlazBlue: Chrono Phantasma ブレイブルークロノファンタズマ BBCP, BBCPE 4 f 0 The game takes place after the events of BlazBlue: Continuum Shift. The Story Mode has three scenarios: Chronophantasma, Sector Seven and Six Heroes. \n\nJanuary, 2200AD \nIt's been over one week since the loop came to an end. \nHaving destroyed the Cauldrons, Ragna had finished his quest.\n\nHowever... \nThere was still unfinished business in his books. \nTo defeat his now-revived nemesis, Terumi.\nTerumi abducts the Successor of the Azure, Noel, and forces the Master Unit, Amaterasu to appear in the human plane. \nHatred and vengeance awaken the Sword of the Godslayer, Kusanagi (Mu -No.12-).\n\nRagna sacrifices his own flesh and blood to save Noel, but even that Terumi had predicted. \nIn a small window of opportunity, Terumi nullifies Takamagahara.\n\nIt was all a part of their— \nThe NOL's highest commanding leader, the Imperator's will. \nLater, Kokonoe restores Ragna's flesh... \n...and he sets his course for the Federation's Hierarchical City: Ikaruga.\nLittle did he know, he would encounter the phantoms of time, the Chronophantasma, at his destination.\n\n[From Blazblue Wiki] Chrono_Phantasma \N 20160312 {en,ja,ko,zh} {en,ja,ko,zh} {win} \N \N 0 \N
-1 f f Minimal Test tes alias 1\nalias 2 3 f 0 This is a description invalid invalid invalid \N 20140606 {ja} {ja} {ps2} \N \N 0 \N
-2 f f BlazBlue: Centralfiction ブレイブルー セントラルフィクション Blazblue CF, BlazBlue 4, BBCF 5 f 0 Ragna and the others were in the Union of Hierarchical Cities, called "Ikaruga". They were rebelling against the ruler "Imperator" by their own reason.\n\nAs an answer for the consequences, the mankind all over the world were decomposed into "Seithr". While the power inside Ragna was forcibly provoked to be out of control by the Imperator.\n\nThereafter, the Imperator has vanished somewhere, leaving the huge black sphere called the "Embryo". And Ragna was lost, after he transformed into his internal existence, the "Black Beast".\n\nHowever... Ragna is still alive. When he gained consciousness at the 13th Hierarchical City "Kagutsuchi", the power of the Black Beast was sedated. Moreover, he was still himself though, he lost all of his memory.\n\n"Now time to rebel against the end."\n\n[From Blazblue Wiki]\n\nThe most notable update in the game is that the Arcade has now three Acts instead of just featuring 8 battles. The first Act is named "Phantom of Labyrinth", while the second one is "Nightmare Memory", and the last and third one is called "The Replacement Blue". \N 20170426 {en,ja,ko} {en,ja,ko} {win} \N \N 0 \N
-\.
-COPY chars_vns (id, vid, rid, spoil, role) FROM stdin;
-1 2 \N 0 primary
-\.
-COPY chars_vns_hist (chid, vid, rid, spoil, role) FROM stdin;
-11 2 \N 0 primary
-13 2 \N 0 primary
-\.
-SELECT pg_catalog.setval('covers_seq', 1, false);
-COPY docs (id, locked, hidden, title, content) FROM stdin;
-2 f f Adding/Editing a Visual Novel
-3 f f Adding/Editing a Release
-4 f f Adding/Editing a Producer
-5 f f Editing guidelines
-6 f f Frequently Asked Questions
-7 f f About us
-9 f f Discussion board
-10 f f Tags & traits
-11 f f Public Database API
-12 f f Adding/Editing Characters
-13 f f How to Capture Screenshots
-14 f f Database Dumps
-15 f f Special Games
-16 f f Adding/Editing Staff Members
-\.
-COPY docs_hist (chid, title, content) FROM stdin;
-19 Adding/Editing a Visual Novel
-20 Adding/Editing a Release
-21 Adding/Editing a Producer
-22 Editing guidelines
-23 Frequently Asked Questions
-24 About us
-25 Discussion board
-26 Tags & traits
-27 Public Database API
-28 Adding/Editing Characters
-29 How to Capture Screenshots
-30 Database Dumps
-31 Special Games
-32 Adding/Editing Staff Members
-\.
-SELECT pg_catalog.setval('docs_id_seq', 16, true);
-COPY login_throttle (ip, timeout) FROM stdin;
-127.0.0.0 2018-02-04 14:45:01+00
-\.
-COPY notifications (id, uid, date, read, ntype, ltype, iid, subid, c_title, c_byuser) FROM stdin;
-1 4 2018-01-06 21:03:51.765904+00 \N dbedit v 1 2 Test 6
-2 4 2018-02-03 22:01:48.939799+00 \N dbedit v 3 2 BlazBlue: Chrono Phantasma 1
-3 6 2018-02-03 22:02:58.404134+00 \N dbedit v 1 3 Minimal Test 4
-\.
-SELECT pg_catalog.setval('notifications_id_seq', 3, true);
-COPY producers (id, locked, hidden, type, name, original, website, lang, "desc", alias, l_wp, rgraph) FROM stdin;
-1 f f co Arc System Works http://www.arcsystemworks.jp/ ja Arc_System_Works \N
-2 f f co Mieno Hitomi 三重野 瞳 http://hitodama.info/ ja Mieno Hitomi, born in December 21, 1977, is a singer, songwriter and radio personality from Fukuoka.\n\nShe is also well-known as a scenario writer, especially in anime, under the alias "Akao Deko".\n\nBlog: link Akao Deko\t赤尾 でこ \N \N
-\.
-COPY producers_hist (chid, type, name, original, website, lang, "desc", alias, l_wp) FROM stdin;
-5 co Arc System Works http://www.arcsystemworks.jp/ ja Arc_System_Works
-12 co Mieno Hitomi 三重野 瞳 http://hitodama.info/ ja Mieno Hitomi, born in December 21, 1977, is a singer, songwriter and radio personality from Fukuoka.\n\nShe is also well-known as a scenario writer, especially in anime, under the alias "Akao Deko".\n\nBlog: link Akao Deko\t赤尾 でこ \N
-\.
-SELECT pg_catalog.setval('producers_id_seq', 2, true);
-COPY producers_relations (id, pid, relation) FROM stdin;
-\.
-COPY producers_relations_hist (chid, pid, relation) FROM stdin;
-\.
-COPY quotes (vid, quote) FROM stdin;
-1 This is a silly quote.
-2 Hello, World!
-\.
-COPY releases_hist (chid, title, original, type, website, catalog, gtin, released, notes, minage, patch, freeware, doujin, resolution, voiced, ani_story, ani_ero) FROM stdin;
-2 Test tesuto complete 0 20140606 18 f f f 14 4 4 4
-4 Test tes complete 0 20150711 9 f f f 0 1 1 1
-7 BlazBlue: Centralfiction ブレイブルー セントラルフィクション complete http://store.steampowered.com/app/586140/ 0 20170426 -1 f f f 0 4 3 0
-10 BlazBlue: Chronophantasma Extend - Steam Edition complete http://store.steampowered.com/app/388750/ 0 20160312 -1 f f f 0 4 3 0
-\.
-SELECT pg_catalog.setval('releases_id_seq', 4, true);
-COPY releases_lang (id, lang) FROM stdin;
-1 ja
-2 ja
-3 en
-3 ja
-3 ko
-4 en
-4 ja
-4 ko
-4 zh
-\.
-COPY releases_lang_hist (chid, lang) FROM stdin;
-2 ja
-4 ja
-7 en
-7 ja
-7 ko
-10 en
-10 ja
-10 ko
-10 zh
-\.
-COPY releases_media (id, medium, qty) FROM stdin;
-1 dvd 2
-3 in 0
-4 in 0
-\.
-COPY releases_media_hist (chid, medium, qty) FROM stdin;
-2 dvd 2
-7 in 0
-10 in 0
-\.
-COPY releases_platforms (id, platform) FROM stdin;
-1 ps2
-3 win
-4 win
-\.
-COPY releases_platforms_hist (chid, platform) FROM stdin;
-2 ps2
-7 win
-10 win
-\.
-COPY releases_producers (id, pid, developer, publisher) FROM stdin;
-3 1 t f
-4 1 t f
-\.
-COPY releases_producers_hist (chid, pid, developer, publisher) FROM stdin;
-7 1 t f
-10 1 t f
-\.
-COPY releases_vn (id, vid) FROM stdin;
-1 1
-2 1
-3 2
-4 3
-\.
-COPY releases_vn_hist (chid, vid) FROM stdin;
-2 1
-4 1
-7 2
-10 3
-\.
-SELECT pg_catalog.setval('relgraphs_id_seq', 1, false);
-COPY rlists (uid, rid, status, added) FROM stdin;
-\.
-COPY screenshots (id, width, height) FROM stdin;
-1 1280 720
-2 1280 720
-3 1280 720
-\.
-SELECT pg_catalog.setval('screenshots_id_seq', 3, true);
-COPY sessions (uid, token, added, lastused) FROM stdin;
-\.
-COPY staff (id, locked, hidden, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb) FROM stdin;
-1 f f 1 m ja Mori Toshimichi is a Japanese game designer and illustrator working for Arc System Works, especially for the Blazblue series. \N
-\.
-COPY staff_alias (id, aid, name, original) FROM stdin;
-1 1 Mori Toshimichi 森 利道
-\.
-SELECT pg_catalog.setval('staff_alias_aid_seq', 1, true);
-COPY staff_alias_hist (chid, aid, name, original) FROM stdin;
-14 1 Mori Toshimichi 森 利道
-\.
-COPY staff_hist (chid, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb) FROM stdin;
-14 1 m ja Mori Toshimichi is a Japanese game designer and illustrator working for Arc System Works, especially for the Blazblue series. \N
-\.
-SELECT pg_catalog.setval('staff_id_seq', 1, true);
-COPY stats_cache (section, count) FROM stdin;
-users 3
-vn 3
-releases 4
-chars 1
-producers 2
-traits 2
-staff 1
-tags 6
-threads 1
-threads_posts 1
-\.
-COPY tags (id, name, description, meta, added, state, c_items, addedby, cat) FROM stdin;
-1 Duel Most of the fighting in this game is in the form of one on one fights. They can be either of, in the form of a competition, or the classic anime style where everyone else just stops doing anything and watches the fight while discussing and throwing comments.\n\nYou can check the Wikipedia entry for duels for more information. f 2018-02-03 21:50:16.536555+00 2 0 4 cont
-2 Beat 'em Up A game with this tag involves beat 'em up gameplay.\n\nBeat 'em up is a video game genre featuring hand-to-hand combat between the protagonist and an improbably large number of opponents. f 2018-02-03 21:50:51.339121+00 2 0 4 tech
-\.
-COPY tags_aliases (alias, tag) FROM stdin;
-one-on-one fight 1
-Brawler 2
-\.
-SELECT pg_catalog.setval('tags_id_seq', 2, true);
-COPY tags_parents (tag, parent) FROM stdin;
-2 1
-\.
-COPY tags_vn (tag, vid, uid, vote, spoiler, date, ignore) FROM stdin;
-1 2 4 3 \N 2018-02-03 21:51:28.308199+00 f
-\.
-COPY tags_vn_inherit (tag, vid, users, rating, spoiler) FROM stdin;
-\.
-COPY threads (id, title, locked, hidden, count, poll_question, poll_max_options, poll_preview, poll_recast) FROM stdin;
-1 It works! f f 1 \N 1 f f
-\.
-COPY threads_boards (tid, type, iid) FROM stdin;
-1 an 0
-\.
-SELECT pg_catalog.setval('threads_id_seq', 1, true);
-COPY threads_poll_options (id, tid, option) FROM stdin;
-\.
-SELECT pg_catalog.setval('threads_poll_options_id_seq', 1, false);
-COPY threads_poll_votes (tid, uid, optid) FROM stdin;
-\.
-COPY threads_posts (tid, num, uid, date, edited, msg, hidden) FROM stdin;
-1 1 4 2018-02-04 09:58:59.789808+00 \N Congratulations. f
-\.
-COPY traits_chars (cid, tid, spoil) FROM stdin;
-\.
-SELECT pg_catalog.setval('traits_id_seq', 2, true);
-COPY traits_parents (trait, parent) FROM stdin;
-1 2
-\.
-SELECT pg_catalog.setval('users_id_seq', 6, true);
-COPY users_prefs (uid, key, value) FROM stdin;
-0 notify_nodbedit 1
-1 notify_nodbedit 1
-\.
-COPY vn_anime (id, aid) FROM stdin;
-\.
-COPY vn_anime_hist (chid, aid) FROM stdin;
-\.
-COPY vn_hist (chid, title, original, alias, length, img_nsfw, image, "desc", l_wp, l_encubed, l_renai) FROM stdin;
-1 Test tesuto 3 f 0
-3 Test tes 3 f 0
-6 BlazBlue: Centralfiction ブレイブルー セントラルフィクション Blazblue CF, BlazBlue 4, BBCF 5 f 0 Ragna and the others were in the Union of Hierarchical Cities, called "Ikaruga". They were rebelling against the ruler "Imperator" by their own reason.\n\nAs an answer for the consequences, the mankind all over the world were decomposed into "Seithr". While the power inside Ragna was forcibly provoked to be out of control by the Imperator.\n\nThereafter, the Imperator has vanished somewhere, leaving the huge black sphere called the "Embryo". And Ragna was lost, after he transformed into his internal existence, the "Black Beast".\n\nHowever... Ragna is still alive. When he gained consciousness at the 13th Hierarchical City "Kagutsuchi", the power of the Black Beast was sedated. Moreover, he was still himself though, he lost all of his memory.\n\n"Now time to rebel against the end."\n\n[From Blazblue Wiki]\n\nThe most notable update in the game is that the Arcade has now three Acts instead of just featuring 8 battles. The first Act is named "Phantom of Labyrinth", while the second one is "Nightmare Memory", and the last and third one is called "The Replacement Blue".
-8 BlazBlue: Centralfiction ブレイブルー セントラルフィクション Blazblue CF, BlazBlue 4, BBCF 5 f 0 Ragna and the others were in the Union of Hierarchical Cities, called "Ikaruga". They were rebelling against the ruler "Imperator" by their own reason.\n\nAs an answer for the consequences, the mankind all over the world were decomposed into "Seithr". While the power inside Ragna was forcibly provoked to be out of control by the Imperator.\n\nThereafter, the Imperator has vanished somewhere, leaving the huge black sphere called the "Embryo". And Ragna was lost, after he transformed into his internal existence, the "Black Beast".\n\nHowever... Ragna is still alive. When he gained consciousness at the 13th Hierarchical City "Kagutsuchi", the power of the Black Beast was sedated. Moreover, he was still himself though, he lost all of his memory.\n\n"Now time to rebel against the end."\n\n[From Blazblue Wiki]\n\nThe most notable update in the game is that the Arcade has now three Acts instead of just featuring 8 battles. The first Act is named "Phantom of Labyrinth", while the second one is "Nightmare Memory", and the last and third one is called "The Replacement Blue".
-9 BlazBlue: Chrono Phantasma ブレイブルークロノファンタズマ BBCP, BBCPE 4 f 0 The game takes place after the events of BlazBlue: Continuum Shift. The Story Mode has three scenarios: Chronophantasma, Sector Seven and Six Heroes. \n\nJanuary, 2200AD \nIt's been over one week since the loop came to an end. \nHaving destroyed the Cauldrons, Ragna had finished his quest.\n\nHowever... \nThere was still unfinished business in his books. \nTo defeat his now-revived nemesis, Terumi.\nTerumi abducts the Successor of the Azure, Noel, and forces the Master Unit, Amaterasu to appear in the human plane. \nHatred and vengeance awaken the Sword of the Godslayer, Kusanagi (Mu -No.12-).\n\nRagna sacrifices his own flesh and blood to save Noel, but even that Terumi had predicted. \nIn a small window of opportunity, Terumi nullifies Takamagahara.\n\nIt was all a part of their— \nThe NOL's highest commanding leader, the Imperator's will. \nLater, Kokonoe restores Ragna's flesh... \n...and he sets his course for the Federation's Hierarchical City: Ikaruga.\nLittle did he know, he would encounter the phantoms of time, the Chronophantasma, at his destination.\n\n[From Blazblue Wiki] Chrono_Phantasma
-15 BlazBlue: Centralfiction ブレイブルー セントラルフィクション Blazblue CF, BlazBlue 4, BBCF 5 f 0 Ragna and the others were in the Union of Hierarchical Cities, called "Ikaruga". They were rebelling against the ruler "Imperator" by their own reason.\n\nAs an answer for the consequences, the mankind all over the world were decomposed into "Seithr". While the power inside Ragna was forcibly provoked to be out of control by the Imperator.\n\nThereafter, the Imperator has vanished somewhere, leaving the huge black sphere called the "Embryo". And Ragna was lost, after he transformed into his internal existence, the "Black Beast".\n\nHowever... Ragna is still alive. When he gained consciousness at the 13th Hierarchical City "Kagutsuchi", the power of the Black Beast was sedated. Moreover, he was still himself though, he lost all of his memory.\n\n"Now time to rebel against the end."\n\n[From Blazblue Wiki]\n\nThe most notable update in the game is that the Arcade has now three Acts instead of just featuring 8 battles. The first Act is named "Phantom of Labyrinth", while the second one is "Nightmare Memory", and the last and third one is called "The Replacement Blue".
-16 BlazBlue: Centralfiction ブレイブルー セントラルフィクション Blazblue CF, BlazBlue 4, BBCF 5 f 0 Ragna and the others were in the Union of Hierarchical Cities, called "Ikaruga". They were rebelling against the ruler "Imperator" by their own reason.\n\nAs an answer for the consequences, the mankind all over the world were decomposed into "Seithr". While the power inside Ragna was forcibly provoked to be out of control by the Imperator.\n\nThereafter, the Imperator has vanished somewhere, leaving the huge black sphere called the "Embryo". And Ragna was lost, after he transformed into his internal existence, the "Black Beast".\n\nHowever... Ragna is still alive. When he gained consciousness at the 13th Hierarchical City "Kagutsuchi", the power of the Black Beast was sedated. Moreover, he was still himself though, he lost all of his memory.\n\n"Now time to rebel against the end."\n\n[From Blazblue Wiki]\n\nThe most notable update in the game is that the Arcade has now three Acts instead of just featuring 8 battles. The first Act is named "Phantom of Labyrinth", while the second one is "Nightmare Memory", and the last and third one is called "The Replacement Blue".
-17 BlazBlue: Chrono Phantasma ブレイブルークロノファンタズマ BBCP, BBCPE 4 f 0 The game takes place after the events of BlazBlue: Continuum Shift. The Story Mode has three scenarios: Chronophantasma, Sector Seven and Six Heroes. \n\nJanuary, 2200AD \nIt's been over one week since the loop came to an end. \nHaving destroyed the Cauldrons, Ragna had finished his quest.\n\nHowever... \nThere was still unfinished business in his books. \nTo defeat his now-revived nemesis, Terumi.\nTerumi abducts the Successor of the Azure, Noel, and forces the Master Unit, Amaterasu to appear in the human plane. \nHatred and vengeance awaken the Sword of the Godslayer, Kusanagi (Mu -No.12-).\n\nRagna sacrifices his own flesh and blood to save Noel, but even that Terumi had predicted. \nIn a small window of opportunity, Terumi nullifies Takamagahara.\n\nIt was all a part of their— \nThe NOL's highest commanding leader, the Imperator's will. \nLater, Kokonoe restores Ragna's flesh... \n...and he sets his course for the Federation's Hierarchical City: Ikaruga.\nLittle did he know, he would encounter the phantoms of time, the Chronophantasma, at his destination.\n\n[From Blazblue Wiki] Chrono_Phantasma
-18 Minimal Test tes alias 1\nalias 2 3 f 0 This is a description invalid invalid invalid
-\.
-SELECT pg_catalog.setval('vn_id_seq', 3, true);
-COPY vn_relations (id, vid, relation, official) FROM stdin;
-2 3 preq t
-3 2 seq t
-\.
-COPY vn_relations_hist (chid, vid, relation, official) FROM stdin;
-16 3 preq t
-17 2 seq t
-\.
-COPY vn_screenshots (id, scr, rid, nsfw) FROM stdin;
-2 2 3 f
-\.
-COPY vn_screenshots_hist (chid, scr, rid, nsfw) FROM stdin;
-8 2 3 f
-15 2 3 f
-16 2 3 f
-\.
-COPY vn_seiyuu (id, aid, cid, note) FROM stdin;
-\.
-COPY vn_seiyuu_hist (chid, aid, cid, note) FROM stdin;
-\.
-COPY vn_staff (id, aid, role, note) FROM stdin;
-2 1 scenario
-\.
-COPY vn_staff_hist (chid, aid, role, note) FROM stdin;
-15 1 scenario
-16 1 scenario
-\.
-COPY vnlists (uid, vid, status, added, notes) FROM stdin;
-\.
-COPY votes (vid, uid, vote, date) FROM stdin;
-2 4 50 2018-02-03 21:37:44.946845+00
-1 4 50 2018-02-03 22:03:18.419115+00
-\.
-COPY wlists (uid, vid, wstat, added) FROM stdin;
-\.
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 705954fe..a43edc3c 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -8,11 +8,11 @@ ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey
ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_id_fkey FOREIGN KEY (id) REFERENCES chars (id);
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE;
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
-ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE;
+ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
@@ -75,10 +75,10 @@ ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_chid_fkey
ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
-ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);