| Age | Commit message (Collapse) | Author | Files | Lines |
|---|
|
My testing, deployment and backup scripts were getting more complicated
with files from various stages being lumped into a single directory
structure, so all generated files (= anything touched by 'make') and
runtime files (= anything touched by the web backend or multi) have now
been moved into separate directories. These directories are also
configurable with $VNDB_GEN and $VNDB_VAR, making it possible to manage
multiple instances from a single source checkout.
I also got rid of *data/* and *dl/* while I was at it, and moved
*static/st/* (that is, the screenshot thumbnails) to */sf.t/*, to be
consistent with the newer and more flexible image directory naming
scheme.
This commit breaks all existing installations and upgrading requires
manual action. General upgrade instructions:
# BEFORE doing a checkout of this commit
make clean
# AFTER
make
mkdir -p var/static var/log
mv static/st var/static/sf.t
mv static/{sf,ch,cv}{,.orig} var/static/
mv data/conf.pl var/
mv data/log var/
mv data/hibp var/
mv dl var/
util/setup-var.sh
Use `git status` find leftover files to clean up or move.
Don't forget to update conf.pl and web server configuration to make sure
they access the new paths.
|
|
|
|
This work is to support a safe self-service account deletion feature,
but I haven't implemented that part yet.
|
|
So we have a little bit more documentation on SQLBin's schema page:
https://sqlbin.vndb.org/schema
|
|
|
|
So that there aren't any columns in need of quoting anymore.
|
|
The standby server filesystem may lag behind a bit on the database, but
that's not really a problem for these exports.
|
|
|
|
Going from 29s before the merge to 35s after the merge to 7s with this
change.
|
|
This reduces the database size a bit and slightly simplifies and
significantly speeds up certain queries. Touches a fair amount of tricky
code, so I'm sure there's bugs.
The only visible change I'm aware of is that the deprecated
'get/set wishlist' API command now doesn't handle priorities anymore.
|
|
|
|
As discussed in https://vndb.org/t13027 and from
https://vndb.org/t6138.327 onwards.
|
|
This is a cleaner solution to the problem of the schema relying on a few
utility functions and creating a dependency cycle between schema.sql and
func.sql.
Also fix dbdump.pl to not export generated columns.
These changes are to prepare for an upgrade to PostgreSQL 14 with a
clean import through dbdump.pl. Which appears to work surprisingly well
in my testing so far.
|
|
The database model supports private votes with a proper speed setting,
but that is currently not possible through the UI.
|
|
This implements the main database model part of custom title languages
(https://vndb.org/t12465). Selecting the right title for display is done
in SQL through the 'vnt' VIEW, which can be overridden in each session
with a TEMPORARY VIEW in order to support user title preferences, but
that part has not been implemented yet.
I had started out using an sql_vn() function that returned a subquery
instead of using a VIEW, but then ran into trouble with the item_info()
SQL function. This VIEW approach also happened to simplify much of the
code. I did have to get rid of the Discusssions::Lib::sql_boards()
function, as Postgres was unable to optimize the subquery inside a UNION
inside a subquery for some reason. Haven't run into any other noticeable
performance regressions yet.
TODO:
- Implement actual user title preferences
- Add the correct 'lang' HTML attributes everywhere a title is displayed
(we do have the information now, though it still isn't trivial)
- Add title fetching support to API
|
|
Which I broke in the previous two commits.
|
|
|
|
This way there is always a single canonical path for each tag/trait,
which fixes the problem with traits that could belong to multiple groups
yet you couldn't control which one was selected, and this also removes
duplication in the VN->tags tab, which now doesn't have to non-canonical
tag paths.
|
|
Huh, I thought I tested this.
|
|
Much the same as the previous conversion of tags.
|
|
Another commit with changes all across the tree. But at least we have a
tangible improvement now: edit histories for tags.
|
|
I had wanted to split this up into multiple commits and roll out in
stages, but couldn't really find a natural way to do so. There are
several places that take a generic identifier and expect it to work the
same for all entries they support, so changing one entry at a time
wasn't going to be any easier. Only the tags & traits haven't been
updated yet, I'll convert those later.
While this is a major change and affects a lot of code, the individual
changes are all pretty simple. I'm surprised how much code did not have
to be updated at all. No doubt I've missed a few places, though, so this
commit will almost certainly break something.
|
|
Roughly similar to 'pg_dump --data-only', but with added includes for
sql/ scripts, without the SET commands (these will cause the includes to
fail) and with a "free" built-in CLUSTER on the primary key for most
tables.
|
|
The full list of quotes used to be one of VNDB's most well guarded
secrets, but it's been scraped often enough now that I might as well
just make it public.
|
|
I had already rambled on the current composite type solution in
583ae868dfd3c882a8d2dd40b5d5ed099170c1c2 and I had already explored a
few alternatives. This was the one alternative I hadn't yet explored
because I wasn't sure the operational complexity was going to be worth
it, but after seeing how bad PostgreSQL was at optimizing queries with
composite types, I figured I might as well just go with this approach.
It improves performance of some queries by a *lot* (especially the image
selection query) and it's pretty elegant and convenient to work with.
Only downside is the complexity of compiling, installing and maintaining
a vndbid.so library for PostgreSQL.
|
|
|
|
Lots of TODO's left to work on, but you have to start somewhere.
I've bumped the Docker image version because this change requires TUWF
commit 74aad378d49592df4359ea8a9f6f36d4a0013c04 (Elm decoder for structs
with more than 8 fields)
|
|
In preparation for an image flagging feature. This replaces the
'screenshots' table.
I played around with various implementations of a primary key for the
images table; A single integer encoding both the type and id is most
efficient, but kind of ugly to work with. Two separate columns are also
ugly and force the creation of a separate 'image_type' column on all
referencing tables. The current composite type isn't all that much
better, but at least it makes it easy to keep treating the image id as a
single entity. Old VNDB::* code still treats it as an integer with
different namespaces, new VNWeb::* code treats it as a proper composite
type.
|
|
This fixes the issue of tag votes getting deleted when a user account is
deleted, despite these votes being part of the kept "database
contributions" mentioned in the privacy policy.
|
|
Ended up exporting the 'users.ign_votes' column as well, as that's both
easier and (potentially) more useful than not exporting ignored votes in
the first place.
|
|
Fixes a (harmless) warning:
Issuing rollback() due to DESTROY without explicit disconnect() of
DBD::Pg::db handle dbname=vndb at util/dbdump.pl line 247.
|
|
Today's export had a little inconsistency caused by a database update
while the export script was running.
|
|
This bloats the users table a little bit, but that's fine. The main
advantage of this change is that we now have a proper schema for user
preferences, rather than the schemaless key-value mess we had before.
This commit also splits the 'tags_cat' preference up into tags_cont,
tags_ero and tags_tech bools, as that's more compact to store and easier
to work with.
This commit also changes the 'notify_nodbedit' preference to
'notify_dbedit' with inverted meaning. The reason the value was negated
in the first place was because the old schemaless approach did not
support positive defaults.
|
|
I always avoided using the VNDB::* schema for non-web related utility
modules, but thats pretty silly (especially as I'm slowly trying to move
away from the old VNDB::* web code).
|
|
|
|
I never really liked the hack that devdump.pl had to use to temporarily
disable triggers and references. This new importer first imports all
schema-related things, then the data, then the functions and table
attributes - like an actual database dump. This restructuring should
also make it (slightly) easier to import the "near-complete" database
dump, but that's still going to involve a fair amount of scripting.
This also fixes #22 - the script now asks whether to import a 'dump.sql'
if it exists.
|
|
+ Automatically fetch other links from Wikidata
|
|
For use with rsync daemon.
|
|
|
|
|
|
This provides more control over when the dumps are being made and makes
it easier to generate quick dumps for debugging.
|
|
|
