Age | Commit message (Collapse) | Author | Files | Lines |
|
I don't know why I didn't apply this one before, I did make this change
when benchmarking the fulltext search queries and with the introduction
of the bb_tsvector() function this change pretty much always improves
performance.
|
|
The new database schema doesn't allow an alias to be removed when it is
still linked to a VN.
|
|
This broken filter would cause all staff info to be deleted from a VN
upon edit. Not so nice.
|
|
These indices provide a significant speed-up of /v+ and /u+ pages, and
improve some other stuff as well.
|
|
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.
|
|
Two main improvements:
- Filtering on (non)hidden items now doesn't join any of the item
tables, instead it looks up the latest revision from the changes table
itself, using the index on (type,itemid,rev). It's still not super
fast, but a pretty large improvement nonetheless.
- The item titles/names are obtained in a separate query. I tried to
modify the main query in various ways, but couldn't make it as fast as
I'd have liked.
I also removed the 'what' flag while I was at it, all uses of the method
request all information anyway.
|
|
That should be the last thing to convert to the new schema.
|
|
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 basically makes VNDB browsable again, but editing entries is still
broken.
I split off the get-old-revision functionality from the db*Get() methods
into db*GetRev(). This split makes sense even with the old SQL schema:
db*Get() had to special-case some joins/filters when fetching an older
revision, and none of the other filters would work in that case. This
split does cause some code duplication in that all db*GetRev() methods
look very much alike, and that the columns they fetch is almost
identical to the db*Get() methods. Not sure yet how to avoid the
duplication elegantly.
I didn't do a whole lot of query optimization yet (most issues require
extra indices, I'll investigate later which indices will make a big
difference), but I did fix some low hanging fruit whenever I encountered
something.
I don't think I've worsened anything, performance-wise.
|
|
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.
|
|
|
|
Adds slightly more strict validation and simplifies further processing.
|
|
|
|
No more need for extra json_encode/json_decode calls, and the
form_compare() function is more lenient w.r.t. integer/string
comparison.
This is the improvement I described in commit
ed86cfd12b0bed7352e2be525b8e63cb4d6d5448
|
|
This might have broken the screenshot uploader on some crappy browsers,
but it's much cleaner than the old iframe hack. The ability to upload
multiple files in one go is also very convenient.
|
|
|
|
As suggested by https://vndb.org/t2520.168
|
|
Looks like 0 is actually used often to indicate some special value.
Affects basically all 'check all' boxes (had to modify some of those
boxes because some used -1, but that wasn't a problem).
|
|
It can be 0 when creating a new alias as primary name.
|
|
|
|
This is less convenient than I had expected, because all the form
handling code is designed to work with plain strings rather than any
scalar. This means the json data has to be encoded again to get into
$frm (not doing this means that, if the form didn't validate, the field
won't be filled out correctly). And then decoded for validation, and
then encoded again for comparison.
I suspect the better solution is to fix the form handling code to handle
arbitrary data structures: comparison can be done by deep comparison
rather than a simple string compare, and the form generator can
auto-encode-to-json if it sees a complex object.
Another advantage of this solution is that the comparison function can
be less strict with respect to number formatting. In the current scheme
you have to be very careful that numbers are not automatically coerced
into string format, otherwise the comparison will fail.
Either way, that's an idea for the future...
|
|
|
|
Surprisingly, this was the only change I needed to make to comply with
the TUWF reqGet-style function split.
|
|
|
|
And added new 'page' and 'id' templates for more strict validation.
|
|
As requested at https://vndb.org/t6862.28
|
|
This (only very slightly) simplified further processing of the data. It
does add more validation than was present: Previously it was possible to
send invalid roles (would give a 500) or invalid numeric IDs (would
throw some perl warnings). These issues are now handled earlier on. This
change also puts a maximum length on the notes field, but nobody has
passed the 300 characters so far.
|
|
CamelCase is used for methods, C-style names for functions. I'm just
nitpicking now.
|
|
The intention is to move more JS editing forms to use JSON, but manually
verifying JSON objects is both painful and likely to introduce errors or
vulnerabilities. json_validate() is a bit of a hack, but has the
advantage that its validation syntax is the same as for normal forms,
and it automatically strips whitespace. I intent to give kv_validate()
an upgrade to be more flexible/modular so it can do more custom
normalization. But that's for later.
I've been meaning to rewrite the JS forms anyway together with the large
JS rewrite, but I'm rather lazy. This is one small step in the right
direction anyway.
Note that json_validate() assumes that the JS code will provide
user-friendly messages on bad input, but the staff alias editor doesn't
quite do this yet.
|
|
Hopefully prevents issues like https://vndb.org/t3599.53
|
|
|
|
And also fix strip_bb_tags() to be case-insensitive and fix a bug in
converting the query into a tsquery.
|
|
It's not verified and only uglifies the URLs.
|
|
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.
|
|
From 50 to 200. https://vndb.org/t6892
|
|
- Individual blocks don't leak variables into the parent scope anymore.
Previously some blocks would re-use variables from other blocks,
creating (almost invisible) dependencies between te blocks.
- More consistent code for ulist-change-dropdowns, and all of them have
a ref= argument now.
- Use 'hidden' class instead of style.display wherever that makes sense.
- Remove dead 'advselect' code. Hasn't been used since the addition of
search filters.
- lang_select doesn't rely on the position of the language class in
className anymore (seriously that stuff is fragile...)
|
|
This simplifies the JS code in some places and removes a whole number of
L10N strings from the "l10n_str" var, thus shrinking the JS size a bit
(uncompressed about 1500 bytes, in fact. 500 bytes after gzip).
|
|
First part of a Javascript cleanup.
|
|
|
|
|
|
It's not a preference yet and the sexual traits are still visible by
default. I'll fix that later.
|
|
- Remove the obsolete 'use's
- Re-add max-width style when requested
- Fix comparison in displaying media
- Fix sorting on animation column
|
|
The previous code was using experimental perl features (switch /
smartmatch) that weren't really needed, and the information about
individual columns was spread around in multiple functions. This rewrite
makes the code consistent with the rest of VNDB, and has *all* of the
column-specific information in one data structure.
I did not replicate the similar-cell-merging feature, partly because the
code for it is definitely not trivial and partly because it doesn't make
the table look any less cluttered. In fact, I feel that it only makes
the table harder to interpret because it looks messy. This is a matter
opinion, of course, so I might reimplement the feature if people who
actually use this comparison page want to.
Overall, I'm still undecided on whether this comparison page should
exist at all in its current form - it's not very user-friendly and often
looks cluttered. I'm keeping it because it does have some use-cases
where it avoids opening every release page to do a manual comparison,
but I'd love a more friendly-looking alternative.
|
|
|
|
Reported by dim0k at https://www.xssposed.org/incidents/74523/
|
|
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.
|
|
|