summaryrefslogtreecommitdiff
path: root/util/updates
AgeCommit message (Collapse)AuthorFilesLines
2020-01-01ulist: Time to put this live2.27Yorhel1-0/+0
Decided to change the naming scheme of files in util/updates/
2019-12-30Minor refactor: Move all trigger functions and declarations to separate fileYorhel1-6/+4
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.
2019-12-30Merge branch 'ulist'Yorhel1-33/+14
2019-12-30ulist: Remove remnants of old votes/vnlists/wlists tablesYorhel1-3/+7
2019-12-22ulist: Use new lists for VN vote stats & listingYorhel1-0/+2
To my surprise, I actually managed to achieve acceptable performance by just adding two indices. I totally expected I'd have to keep a cache column in ulist_vns whether the row is private or not. The partial index on the users table in fact improves the performance of the vote graph query. A covering index improves that even further, but that requires Postgres 11+, which the Docker image doesn't have yet (and isn't all that crucial anyway). There's a rather annoying potential for confusion regarding the private flag on votes. The user page & list stats only look at whether the 'Voted' label is private, whereas the VN stats use the "proper" approach of checking for any public label. Not entirely sure which of the two is more intuitive.
2019-12-20Keep track of poll vote datesYorhel1-0/+2
That'd make it much easier to find fake accounts.
2019-12-19ulist: Update user list with vnlist/votes/wishlist statsYorhel1-8/+7
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.
2019-12-17ulist: Synchronize changes between rlists and ulist_vnsYorhel1-0/+1
These ensure that, for each row in rlists, all the linked VNs have an entry in ulist_vns. This implementation should be more robust than before; It now isn't possible to remove an item from ulist_vns without also removing all relevant rows from rlists, and adding more VNs to an existing release entry will ensure that those VNs are also added to ulist_vns.
2019-12-16ulist: Convert vn.c_* vote stat updating to use ulist_vns tableYorhel1-0/+2
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.
2019-12-16ulist: Integrate update script into SQL + update notify_listdel()Yorhel1-28/+1
2019-12-03API: Add "get ulist-labels" and "get ulist"Yorhel1-0/+4
To directly query the new list feature. I'll migrate the old vnlist/wishlist/votelist APIs later to query the 'ulist' tables.
2019-11-10ulist: Fix migration script to properly delete tables & handle concurrent ↵Yorhel1-2/+7
changes
2019-11-10ulist: SQL: Create labels for new users + auto-assign Voted label + import ↵Yorhel1-5/+57
rlisted VNs
2019-11-10ulist: Rename tables to something more sensibleYorhel1-16/+16
2019-11-10ulist: Use lower numbers for custom labels + add VN listingYorhel1-54/+17
2019-11-10ulist: Add data model for new lists + Perl/Elm for label managementYorhel1-0/+132
Very much experimental. I'm not sure this is even the right model.
2019-11-08Wikidata: Add support for doujinshi.org author linksYorhel1-0/+1
2019-11-02Add character age field + conversionYorhel1-6/+27
2019-11-02Add character cup size field + conversion + filterYorhel1-0/+51
2019-10-10rewards: Add user settingsYorhel1-0/+10
They don't do anything yet.
2019-10-07SQL: Perform incremental updates on traits_chars when characters are editedYorhel1-1/+2
Same thing as 65ed955890c7ee7250a2bce4467c8c092c1bbbbe, with the same limitations (except this does take hiding/unhiding characters into account).
2019-10-07SQL: Perform incremental updates on tags_vn_inherit when tags are modifiedYorhel1-1/+3
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
2019-10-07SQL: Adjust tag_vn_inherit and trait_chars calculationsYorhel1-0/+7
Most importantly, downvoted parent tags are now still included for searching as per https://vndb.org/t12750 - in particular, a downvoted tag is considered to be exactly the same thing as a tag with no votes at all. Also made a few other adjustments: - Removed the "ignore child tags/traits if parent has its own value" rule. This was done because the 'spoiler' rating would get inherited using the average, but that should just have been the minimum (i.e. if a single child tag is not a spoiler, then neither is the parent). https://vndb.org/t10082 - The score of a parent tag is now avg(avg(votes_on_child_tags)) rather than avg(votes_on_child_tags), i.e. tags with more votes don't have more weight than tags with fewer votes. I don't think vote counts really matter in determining the parent score. - Deleted VNs now don't count towards tag statistics (this is consistent with traits). - Performance improvements: Smaller indices and more clever querying.
2019-10-03Disallow changing email address to one already used by another accountYorhel1-0/+1
2019-10-03Require email confirmation when changing email addressYorhel1-1/+3
This ensures that the email address linked to a user is always valid and actually belong(s|ed) to that user.
2019-10-03SQL: Add session types + use sessions for password reset tokensYorhel1-0/+12
This improves the password reset workflow a bit. The users.passwd field is now no longer used for reset tokens, meaning that the current password isn't affected until the user actually clicks the link and changes it. It is now also possible to have multiple active password reset tokens, in case one of the emails got lost. All existing tokens are invalidated when the user finally changes their password. Tokens are now valid for at most 1 week instead of indefinitely.
2019-10-03SQL: Change sessions.lastused into sessions.expiresYorhel1-0/+8
This will be helpful when adding other types of sessions with different expiration.
2019-10-03Fix spoilers preference to be a smallint rather than booleanYorhel1-2/+2
And for those who (like me) already applied update_20191003.sql, here's a partial fix: alter table users alter column spoilers drop default; alter table users alter column spoilers type smallint using (case when spoilers then 1 else 0 end); alter table users alter column spoilers set default 0; But you'll still need to import the old preference for users who had spoilers=2, because those will have been lost.
2019-10-03SQL: Get rid of the users_prefs table, store preferences in users tableYorhel1-0/+40
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.
2019-09-27Add Malay languageYorhel1-0/+1
Sorry for re-using update_20190923.sql, but I'm not sure anyone even uses those scripts. For documentation purposes it's neat to have similar things in a short time window combined, anyway.
2019-09-24Add language: Scottish GaelicYorhel1-0/+1
2019-09-23Add 4 new languagesYorhel1-0/+4
https://vndb.org/t12897.6
2019-09-14SQL: Replace "shop_*.found" with "deadsince" timestampYorhel1-0/+19
Has the inverted meaning. Keeping track of how long we've been unable to find a certain shop link is useful in weeding out temporary unavailability when querying for dead links.
2019-09-03Add Pixiv ID to staff entriesYorhel1-0/+24
2019-09-03Now with the update scriptYorhel1-0/+7
2019-09-03Clean up old update scriptsYorhel64-4273/+0
These have no value anymore at this point. And if they have, they'll still be in the git history.
2019-09-02Allow multiple Gyutto & DMM links per releaseYorhel1-0/+12
To handle https://vndb.org/t12755.37
2019-09-01Add ErogeTrailers.com links to releasesYorhel1-2/+4
2019-09-01Add EGS links to releasesYorhel1-0/+4
2019-08-31Add doc page for the query.vndb.org interfaceYorhel1-0/+4
2019-08-26Display shop+price info on VN pages + delete old affiliate codeYorhel1-0/+5
The current affiliate links format doesn't display as much information as before but it's also a lot less messy.
2019-08-26Display PlayAsia links on release pages + use crawled info to correct shop linksYorhel1-0/+6
2019-08-25Add PlayAsia shop crawlerYorhel1-0/+24
Most of this code isn't new - there's been a hidden 'Affiliates.pm' module for a while. This just consolidates the code with the other shops and opens up the code. The API endpoint is hidden. :)
2019-08-25Add DLsite price & shop info crawlerYorhel1-0/+9
2019-08-24Add DenpaSoft price info crawlerYorhel1-0/+9
2019-08-24Add MangaGamer price info crawlerYorhel1-0/+9
2019-08-24Add J-List price info crawlerYorhel1-0/+9
2019-08-22Add JAST USA links to releasesYorhel1-0/+4
2019-08-21Remove protocol from l_dmm linksYorhel1-0/+4
It's redundant, and normalization is actually easier when removed.
2019-08-21Add Itch.io links to releasesYorhel1-0/+31