Age | Commit message (Collapse) | Author | Files | Lines |
|
Been wanting to do this for a while...
I've kept util/sql as a symlink for compatibility with the devdump, old
update scripts and other code I may have forgotten. I'll remove it
later.
|
|
This keeps the function body and the trigger declaration closer to each
other, so it's easier to tell when the function will be called and which
cases it needs to handle. If you ignore errors from existing
trigger declarations, the triggers.sql script is idempotent and can be
used in from scripts.
|
|
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.
|
|
|
|
https://vndb.org/t950.514
|
|
Been wanting to do this for a long time - using an integer index into an
array that changes once in a while is way too fragile. Doubly so when
said indices are also used in filters and URLs that can't be updated
every time a new resolution is added.
|
|
|
|
This touches a bunch of things:
- Adds a new first-class database entry type
- Removes the d+.+.+ BBCode link syntax, adds a new d+#+ and d+#+.+
link syntax (references have been updated where possible)
- Adds a new dependency on Text::MultiMarkdown
|
|
Otherwise the vndb_site/_multi users won't have usage rights on those.
Fixes https://vndb.org/t10218.6
|
|
|
|
Previously the website was connected to the database with a "database
owner" user, which has far too many permissions. Now there's a special
vndb_site user with only the necessary permissions. The primary
reason to do this is to decrease the impact if the site process is
compromised. E.g. it's now no longer possible to delete or modify old
entry revisions. An attacker can still do a lot of damage, however.
Additionally (and this was the main reason to implement this change in
the first place), the user sessions, passwords and email data is now not
easily accessible anymore. Hopefully, the new user management
abstractions will prevent email and password dumps in case of an SQL
injection or RCE vulnerability in the site code. Of course, this only
works if my implementation is fully correct and there's no privilige
escalation vulnerability somewhere.
Furthermore, changing your password now invalidates any existing
sessions, and the password reset function is disabled for 'usermods'
(because usermods can list email addresses from the database, and the
password reset function could still allow an attacker to gain access to
anyone's account).
I also changed the format of the password reset tokens, as they totally
don't need to be salted.
|
|
|
|
|
|
|
|
This changes quite a bit to the way the editing functions work. Because
these functions are very repetitive and it's easy to keep things out of
sync, I created a script to generate them automatically. I had to rename
a few function and table names for consistency to make this work.
Since database entries don't have a 'latest' column anymore, and since
the order in which tables are updated doesn't have to be fixed, I
dropped many of the SQL triggers and replaced them with a
edit_committed() function which is called from edit_*_commit() and
checks for stuff to be done.
Don't forget to run 'make' before importing the update script.
|
|
Turning the foreign key references into idempotent statements required
adding the name for each reference in the query. I used the names of the
production database, but since the names are autogenerated at creation
time, it's possible that they have other names if the database has been
created slightly differently.
Using explicit names for everything and having idempotent SQL statements
is rather useful when making nontrivial modifications to the database
schema. Which is something I consider doing.
|
|
Same reasoning as 19ce5fcf536ed478ad34b6b1014bf6f44841d25d
|
|
Inspired by wakaranai's implementation at
https://github.com/morkt/vndb/commit/b852c87ad145fdaaa09c79b6378dd819b46f7e87
This version is different in a number of aspects:
- Separate search functions for title search and fulltext post search.
Perhaps not the most convenient option, but the downside of a combined
search is that if the query matches the threads' title, then all of
the posts in that thread will show up in the results. This didn't seem
very useful.
- Sorting is based purely on post date. Rank-based sort is slow without
a separate caching column, and in my opinion not all that useful.
Implementation differences:
- Integrated in the existing DB::Discussions functions, so less code to
maintain and more code reuse.
- No separate caching column for the tsvector, a functional index is
used instead. This is a bit slower (index results need to be
re-checked against the actual messages, hence the slowdown), but has
the advantage of smaller database dumps and less complexity in
updating the cache.
Things to fix or look at:
- Highlighting of the search query in message contents.
- Allow or-style query matching
|
|
The char(2) solution is both inefficient and ugly. Also needed to be
careful with handling the extra space that Postgres would automatically
add to single-character types.
|
|
|
|
This fixes the unexpected behaviour that changing the spoiler setting on
one page will change it for all pages. All manual spoiler changing
options are temporary now.
|
|
The name of the profile setting isn't very clear. Not sure what to do
with it.
|
|
|
|
|
|
https://vndb.org/t6138.226 - https://vndb.org/t6048.132
|
|
|
|
|
|
|
|
So that the /util/sql/ files are in sync with the actual DB again.
|
|
I believe I didn't do this conversion earlier (back when I converted the
language types) because PostgreSQL didn't support dynamically adding new
values to an existing enum back then, and modifying an enum was a huge
pain. Recent versions do support this, so there's no reason to keep it
as a string.
...I just felt like adding some churn to the code base.
|
|
|
|
|
|
TODO: Get rid of the 'processing' flag and all the async loading of
screenshot data in the screenshot uploader.
|
|
|
|
I used to do this to avoid loading Image::Magick in each TUWF process,
decreasing memory usage, and lowering the blocking time by avoiding too
much processing. Memory isn't much of a problem nowadays, and processing
images is fast enough, too, so this complexity isn't necessary anymore.
(Character images and screenshots pending)
|
|
|
|
|
|
|
|
And added an update_2.23.sql file which now also includes the previously
added indices. Currently, this update file can be run as often as you
want, it doesn't make any noticable changes when you run it on a
database that has already been updated. (i.e. I can update the main site
without a new release)
|
|
Required in order to search for hidden entries (obviously :P)
|
|
|
|
|
|
|
|
Not very useful at the moment, but will be used to improve several other
things.
|
|
I changed the ENUM in 2.11, and apparently forgot to update the local
SQL files... I'm sure there are more such issues. >_>
|
|
- Added a 'prefs' option to htmlFooter() to add preference data for use by
Javascript.
- Added an /xml/prefs.xml URL for setting preferences from JS.
- Added 'filter_*' keys to the prefs_key ENUM
- Load filters by default on VN and Release browser
|
|
And renamed notify_dbedit to notify_nodbedit, since the default is to
provide a notify on a database edit.
Also fixed a few bugs along the way.
|
|
Will convert the other preferences later.
|
|
- Created vnlists table
- Converted rlists.vstat into that table
- Added triggers to make sure that there is always a corresponding row
in vnlists for every row in rlists.
- Added a check on vnlists for the 'listdel' notify
|
|
plpgsql is available by default in PostgreSQL 9.0, so there's no need
for this statement anymore.
|