| Age | Commit message (Collapse) | Author | Files | Lines |
|---|
|
|
|
|
|
Somewhat hacked together, should better integrate this feature with
Auth.pm but the code is already annoying enough as it is. :(
|
|
With a better listing, submissions form, moderation interface and
voting.
|
|
|
|
As discussed in https://vndb.org/t20453
|
|
+ update filters and APIs to respect the 'listread' permission.
|
|
This reduces the average row size from 145.7 to 101.4 bytes (including
row headers). Probably not going to result in a noticeable performance
difference, but the table is referenced pretty often while many columns
are only ever read by direct id lookup. I could reduce the size even
further, but that'll get into diminishing returns territory. This split
makes it easier to add more preferences later on without having to worry
about performance.
Also improved user privacy a bit by moving the 'ip' field to a
write-only column in users_shadow, and deleted the unused changes.ip
column while I was at it.
|
|
|
|
|
|
others
https://vndb.org/t15139.10
A more elegant solution is probably to keep the votes but make them not
count, as right now it means that votes won't be restored when the user
who "overruled" Multi's vote ends up getting their votes
ignored/deleted, but I don't expect that to be a common scenario.
|
|
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.
|
|
Something I was planning to do in the v2rw rewrite. VNDB::Func used to
be a module intended for use inside TUWF (i.e. the web backend) while
VNDBUtil was for utility functions that could also be used outside of
that. Now that the web backend has moved to the VNWeb::* namespace,
the VNDB::Func module has taken over the role of VNDBUtil.
Also updated the API code to make use of the 'imgurl' function, which
is a nice additional cleanup.
|
|
Not strictly necessary for the current use, but I plan to add some
browsing features and those really will need it.
|
|
|
|
|
|
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)
|
|
This adds the users.c_vns and c_wish columns and a function to update
the cache.
Unlike my previous cache update approaches, I did not use SQL triggers
here, as that seemed more complex and less efficient than updating the
cache manually. That's not to say that I'm happy with the current
approach, but meh...
The cache update function is not automatically run for all users, but
that could be added to Multi::Maintenance if it turns out that the
cached values will not be updated properly in some cases.
|
|
It turned out that naively changing the existing queries to refer to
ulist_vns with a (WHERE vote IS NOT NULL) made them ultra slow (like,
DNF-slow), for a reason I haven't been able to figure out yet. I had to
rewrite the queries to use CTEs and in doing so figured that I could get
an extra performance improvement by combining the popularity and rating
updates in one query. The combined update function now runs faster than
the old queries.
|
|
Same thing as 65ed955890c7ee7250a2bce4467c8c092c1bbbbe, with the same
limitations (except this does take hiding/unhiding characters into
account).
|
|
This has some limitations:
- tags.c_items is not updated, so that may be out of sync.
(The UPDATE takes about 400ms, so doing that more regularly from
Multi::Maintenance should be a viable option)
- When the hidden flag of a VN is changed, the tags will also be out of
sync.
- I don't see a way to do fast incremental updates when tag entries
themselves are changed, e.g. to handle changes in the tag tree or
searchable flag
|
|
This will be helpful when adding other types of sessions with different
expiration.
|
|
This gets rid of global.pl, config.pl and config3.pl and uses the
cleaner config3.pl format for the config file. The config is easily
accessible from anywhere by importing the new VNDB::Config module; The
global $VNDB::S,O,M,ROOT variables have been removed.
Sorry for all the churn...
|
|
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.
|
|
The database has grown and we're on SSDs now, so it's good to revisit
these timings and see what needs optimizing, if anything.
|
|
That should be the last thing to convert to the new schema.
|
|
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.
|
|
AE::timer accepts a time interval as argument, not a complete
timestamp. So the monthly cron job hasn't run in a while...
|
|
|
|
|
|
Easier to work with in custom queries.
|
|
The usercache maintenance cron is causing significant downtime each
month, so I've disabled it for now.
|
|
|
|
In particular, don't run the tasks when I'm asleep. The SQL queries that
are run during maintenance can deadlock and cause multi to crash. I want
to be awake when that happens.
|
|
Required in order to search for hidden entries (obviously :P)
|
|
|
|
|
|
I'd really prefer incremental updates, but I'll first need to find a
proper algorithm...
|
|
|
|
|
|
This adds a new column to the vn table: c_search, which holds the
normalized titles for speedy search results using LIKE.
Also split some functions from VNDB::Func that didn't require YAWF into
a VNDBUtil module, so Multi can also make use of them. The normalization
functions are the same for Multi and VNDB, after all.
The API and Multi::IRC still use the old search, these should be updated
as well.
|
|
|
|
past 5 days
And run only run a full update_vncache() monthly.
This was the last daily cron that took quite a while to run, the
complete daily cron time has now been reduced from about 90 seconds to
about 5 seconds.
|
|
To batch update, simply do a
SELECT update_vncache(id) FROM vn;
The function is now more readable as well.
|
|
This is more efficient, and doesn't require the tag_tree() or
tag_vn_childs() stored procedures. Does require PostgreSQL 8.4+
|
|
avg_rating should be the average rating of all VNs, not the global average
vote.
|
|
They still have influence on the average number of votes per VN and the
overall average vote, but this isn't significant. (at least, not at the
moment)
|
|
Was a good idea after all...
|
|
The changes.rev column should be correct in the first place, and in the
(most likely) impossible condition that it isn't, the update_rev()
function is more likely to make things worse.
|
|
TODO:
- document the relations
- emit a relgraph notify when needed
|
