summaryrefslogtreecommitdiff
path: root/util
AgeCommit message (Collapse)AuthorFilesLines
3 daysAPI2: Implement token-based authentication + GET /authinfoYorhel1-0/+9
+ update filters and APIs to respect the 'listread' permission.
2022-11-11Add Serbian languageYorhel1-0/+1
2022-10-31dbdump.pl: Improve performance of dumping ulist_vnsYorhel1-9/+40
Going from 29s before the merge to 35s after the merge to 7s with this change.
2022-10-31SQL: Merge ulist_vns_labels into ulist_vns as array + cache private flagYorhel3-17/+150
This reduces the database size a bit and slightly simplifies and significantly speeds up certain queries. Touches a fair amount of tricky code, so I'm sure there's bugs. The only visible change I'm aware of is that the deprecated 'get/set wishlist' API command now doesn't handle priorities anymore.
2022-10-27Chars: add lie flag for traitsYorhel1-0/+5
Still TODO: filtering & searching.
2022-10-24devdump: Add mod userYorhel1-9/+9
2022-10-22AdvSearch: Add option to exclude lie tagsYorhel1-0/+4
https://vndb.org/t18886.24 The compact form encoding is a big ugly mess, I wouldn't be surprised if there's bugs.
2022-10-16ExtLinks: Add Playstation (HK) + Nintendo (EN/JP/HK)Yorhel1-0/+11
2022-10-09API2: Add noapi/onlyapi modes to the web processYorhel1-6/+22
Having a single process that can handle both API and web requests is useful for development, but in production it's more efficient and reliable to separate the two. Less routes and hooks to match, after all.
2022-10-08API2: Initial experimentsYorhel1-0/+8
It's looking pretty powerful and functional already.
2022-10-08SQL: Reduce size of images table + set a max of 15 votesYorhel1-0/+19
Reduction is by 12 bytes per row, by simply encoding the same data as smallints. We never needed the full range of the real/integer types. (Could go even further, but postgres doesn't have a uint8 type and encoding avg + stddev in the same column feels a bit too hacky) Having more than 15 votes on an image is pretty useless, so by removing images with sufficient votes from the image voting selection we can avoid further bloating the table. (c_weight is still >0 even for images that shouldn't be selected anymore, because this field is also abused to check if an image is referenced somewhere in the DB. Instead, images with c_weight=1 are now excluded from selection. This has the side effect of also excluding images with 14 votes and stddev=0, but that seems okay)
2022-09-28Let's put the release titles feature liveYorhel1-0/+0
2022-09-28Allow non-main release titles to be emptyYorhel1-18/+17
https://vndb.org/t17804.113 and beyond.
2022-09-28Add update scriptYorhel1-0/+82
2022-09-28WIP add language-specific titles to releasesYorhel2-2/+2
2022-08-30Also add trait overridesYorhel1-0/+11
2022-08-30Rename users_prefs_tags.tag -> tid, add update scriptYorhel1-0/+12
2022-08-29devdump: Update with vn_editions tableYorhel1-1/+1
2022-08-28Add Basque languageYorhel1-0/+1
2022-08-25Move loading custom CSS to separate request + increase maximum sizeYorhel1-0/+3
This reduces potential the attack surface of HTML inlining... I hope, and is also more efficient than including the users' CSS on every page.
2022-08-25Staff editionsYorhel2-1/+46
As discussed in https://vndb.org/t13027 and from https://vndb.org/t6138.327 onwards.
2022-08-24Use libloc to add IP location information to the DBYorhel1-0/+17
This should save me considerable time in finding duplicate account voters.
2022-08-21Clear unused cookie & localStorageYorhel1-0/+3
2022-08-03Add tags_vn_direct cache tableYorhel1-0/+10
This speeds up VN pages a little bit, but the main reason for this table is to implement no-parent-inherit tag search. Because the rows in tags_vn_direct represent an intermediate calculation for tags_vn_inherit, generating and updating these caches isn't all that much slower with this change. I'm not a fan of the DELETE & re-INSERT approach on individual-VN updates, though, as that tends to cause unnecessary (index) writes. Not sure how complex a more incremental update is to implement, but it sounds painful. :/
2022-07-31Add VN development status fieldYorhel1-0/+24
https://vndb.org/t18854
2022-07-31Fix util/revision-integrity.plYorhel1-1/+1
2022-06-21VN/tags: add support for flagging tags as "false"Yorhel1-0/+1
Somewhat experimental feature, needs better testing, guidelines and integration with the search function. Probably.
2022-06-20Add changes patrolling mod featureYorhel1-0/+8
2022-06-19Move & streamline VN release expanding settingsYorhel1-0/+31
Problems with the old implementation were that the setting wasn't an intentional user action, so there were a LOT of preference writes that were reverted a minute or so later. Wasting both server and visitor time. For regular visitors, the localstorage-based approach also caused a reflow if their preferences didn't match the default. Downside of the new implementation is that regular visitors can't change their preference at all anymore, this now requires an account. :/
2022-06-18Users/prefs: Move prodrelexpand setting from cookie to user settingsYorhel1-0/+1
For better consistency with other settings. This means it is not remembered for regular visitors anymore, and there's now an unused cookie in people's browser that should eventually be deleted.
2022-06-16Debloat users table my moving some columns to other tablesYorhel3-2/+93
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.
2022-05-09Implement site-wide read-only modeYorhel1-0/+3
Somewhat surprising I haven't implemented this earlier, I'm sure it might have been useful during server migrations. Anyway, with my current plans for a postgres 14 upgrade I could significantly reduce full downtime by temporarily putting the site in read-only mode instead.
2022-04-29SQL: Move utility functions to separate util.sqlYorhel2-1/+3
This is a cleaner solution to the problem of the schema relying on a few utility functions and creating a dependency cycle between schema.sql and func.sql. Also fix dbdump.pl to not export generated columns. These changes are to prepare for an upgrade to PostgreSQL 14 with a clean import through dbdump.pl. Which appears to work surprisingly well in my testing so far.
2022-04-23Add Inuktitut languageYorhel1-0/+1
2022-04-19SQL: Adjust default VN popularity rank to 10MYorhel1-0/+1
So that new VNs will be ranked as least popular (with 0 votes). The daily update_vnvotestats() cron will update the value with something more sensible. Fixes https://vndb.org/t950.1219
2022-04-05Releases: Add "has ero" flagYorhel1-0/+5
2022-04-01Add user traits featureYorhel1-0/+8
2022-03-29Add new release animation fieldsYorhel1-0/+29
As discussed in t17596 The code and storage method for this is really quite ugly, but I tried to not bloat the code too much.
2022-03-29VN length voting: support private votesYorhel3-3/+6
The database model supports private votes with a proper speed setting, but that is currently not possible through the UI.
2022-03-23VN length voting: remove ignore flag and allow speed=NULL for uncounted votesYorhel1-0/+6
2022-02-19Some fixes for update scriptsYorhel2-6/+5
2022-02-19Use special "vnt.sorttitle" column for sorting & alphabet filteringYorhel1-0/+2
This column simply picks the latin version of the chosen display title, as if the "latin" checkbox is always set.
2022-02-12Fixup: correct dateYorhel1-0/+0
2022-02-12Add Traditional & Simplified Chinese languagesYorhel1-0/+30
The old 'Chinese' option should not be available for selection in new entries, but I'll have to fix that later.
2022-02-11Fix title display on VN edit form + release update scriptYorhel1-0/+0
2022-02-10Add title language user preferenceYorhel2-0/+3
2022-02-08VN: Add support for multiple titles in different languagesYorhel3-1/+43
This implements the main database model part of custom title languages (https://vndb.org/t12465). Selecting the right title for display is done in SQL through the 'vnt' VIEW, which can be overridden in each session with a TEMPORARY VIEW in order to support user title preferences, but that part has not been implemented yet. I had started out using an sql_vn() function that returned a subquery instead of using a VIEW, but then ran into trouble with the item_info() SQL function. This VIEW approach also happened to simplify much of the code. I did have to get rid of the Discusssions::Lib::sql_boards() function, as Postgres was unable to optimize the subquery inside a UNION inside a subquery for some reason. Haven't run into any other noticeable performance regressions yet. TODO: - Implement actual user title preferences - Add the correct 'lang' HTML attributes everywhere a title is displayed (we do have the information now, though it still isn't trivial) - Add title fetching support to API
2022-02-05Count VNs without votes as popularity=0Yorhel1-0/+7
As opposed to NULL, i.e. not having a popularity at all. Fixes https://vndb.org/t2520.623 and https://vndb.org/t2520.624
2021-12-15API: Add support for session tokensYorhel1-0/+3
2021-12-06ExtLinks: Add PlayStation storesYorhel1-0/+13