| Age | Commit message (Collapse) | Author | Files | Lines |
|---|
|
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.
|
|
https://vndb.org/t11296.3
(And I forgot to add update_20180929.sql in the previous commit)
|
|
https://vndb.org/t950.481
Also, the resolution should really be stored as an ENUM in the database,
this integer thing is waay too fragile.
|
|
As discussed in https://vndb.org/t10665
|
|
|
|
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
|
|
For consistency with the VN & character alias fields. It's also less
ambiguous, as there are a few aliases that contain commas.
|
|
|
|
https://vndb.org/t950.317
|
|
|
|
VNDB tends to get unresponsive for a few minutes when the daily cron is
run. This should help somewhat.
|
|
|
|
This is a generalization of the search improvements made in
7da2edeaa0f6cf7794f4f8f68960497dc1be893c and
92235222dba4e5d0c7713d53ef12e0f10e371b83
And has been applied to the dropdown searches for producers, staff, tags
and traits.
For all those searches, exact matches are listed first, followed by
prefix matches, and then substring matches. Relevance is currently only
based on the primary name/title and ignores aliases (except for staff).
This is fixable, but not trivial, and I'm not sure it's all that useful.
|
|
|
|
- Merged polls table into threads table. Not much of a
storage/performance difference, and it's a bit simpler this way.
- Merged DB::Polls into DB::Discussions. Mainly because of the above
change in DB structure.
- Add option to remove an existing poll.
- Allow preview and recast to be changed without deleting the votes
- Set preview option by default. Because personal preferences. :)
- Minor form validation differences
|
|
I'd have preferred to stick with XHTML 1.0, but unfortunately browsers
won't allow you to use modern Javascript APIs with an older doctype.
Note that most pages don't actually validate correctly as HTML5, I'm
relying on browsers to be lenient.
In either case, I'd like VNDB to stay valid XML (XHTML5, then), and
luckily that shouldn't be a problem.
|
|
They had to be deleted from the database at some point, otherwise we
still have thousands of easily-cracked password hashes in the database.
Note that I could have opted to use scrypt on top of the sha256 hashes
so the passwords would remain secure without needing to reset
everything, but doing that after one year of switching to scrypt is
likely not worth it. Everyone who still actively uses his account has
already been converted to scrypt, everyone else should just reset their
password whevener they decide to come back.
|
|
|
|
An index on threads_posts.date was necessary to speed up some very
common "recent posts" queries on both the homepage and the thread index.
Postgres thought that the same index could be used to speed up the
full-text search (because it's ordered by date, after all), but that
completely killed performance. That was solved with a bb_tsvector()
wrapper to tell the query planner that not using the full-text index is
incredibly show, which in turn improved the search performance beyond
what it was.
Many thread-related queries are still somewhat slow, but that seems to
be a limitation in the schema. I'll just keep monitoring to see if
that's worth fixing in the future.
Interestingly, dbThreadCount() needs to use a sequential scan, but it's
still remarkably fast.
|
|
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.
|
|
This commit breaks pretty much everything. Lots of code will have to be
fixed to work with this new schema.
The basic idea is to separate live data from archived data, which allows
for smaller and more effective indices on the live data, and the
archived data doesn't need such indices and have to be accessed at all
for most operations. Another goal is to eliminate table joins to fetch
some necessary information, e.g. it's not necessary anymore to join the
main item tables in order to fetch only the latest revision of some item
data.
This is very much work in progress. I might stumble upon some weird
issue while fixing the code, and might have to redesign everything
from scratch again. Let's just see how things go.
|
|
Turns out that fetching whether or not you have unread notifications
(done on every pageview if you're logged in) was pretty slow. The index
speeds up both that query and the "my notifications" view.
The extra purge for old notifications for users with more than 500
notifications ensures that the index stays effective for the unread
notifications count. Otherwise it'll have to read half of the
notifications table anyway to check the 'unread' filter.
|
|
Same reasoning as 19ce5fcf536ed478ad34b6b1014bf6f44841d25d
|
|
As suggested by https://vndb.org/t2520.168
|
|
And also fix strip_bb_tags() to be case-insensitive and fix a bug in
converting the query into a tsquery.
|
|
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
|
|
|
|
|
|
Note that it's still in the postgresql ENUM type. Removing that is
possible, but not very trivial.
|
|
|
|
So that the /util/sql/ files are in sync with the actual DB again.
|
|
Used to link to a visual-novels.net review or something. Links have been
hidden and dead since ages. No need to keep the column around.
|
|
|
|
This ensures that, if an attacker evers gets read access to the
database, he will not be able to compromise any accounts. SHA-1 suffices
here, because the data being hashed is a random 20 byte string. The
search space is so damn large that you can't sanely brute force it, nor
are rainbow tables any use at that scale.
They're not salted. The password reset tokens are also hashed in the
database and do include salt, but I've no idea why we did that.
|
|
It doesn't make a whole lot to separate the hashed password and the salt
from each other, you need both to do anything with them, and from the
database perspective they're both completely opaque strings only usable
for direct comparison with other hashed strings.
This change is mostly as preparation for switching to a proper key
derivation function (sha256 isn't...) and to add support for longer
and/or binary salt.
Because the passwd field now needs to be interpreted in Perl, it's being
passed around as a binary string rather than a hex-encoded value.
API login is broken in this commit. I'll get to that.
|
|
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.
|
|
Easier to work with in custom queries.
|
|
|
|
|
|
|
|
|
|
All the async stuff isn't necessary now that images are processed
synchronously.
|
