summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB
AgeCommit message (Collapse)AuthorFilesLines
2021-01-20v2rw: Rewrite done, time to clean up old v2 codeYorhel8-1115/+0
Yay! There are no more request handlers in the VNDB::* namespace and no more Javascript in data/js/. This cleans up a lot of old legacy code that wasn't fun to maintain.
2020-09-29v2rw/TraitEdit: Rewrite trait edit form + cleanup bunch of old codeYorhel1-28/+1
This was the last form in the old v2 code, so this allowed me to get rid of the old form validation and generation abstractions. The form is mostly a duplicate of the tag editing form, as expected. It's really annoying to have to maintain pretty much duplicate code for tags and traits, but there are just enough differences that the complexity of sharing code or creating a new abstraction outweigh the benefits. :(
2020-09-27v2rw: Delete old tag edit formYorhel1-54/+1
2020-09-12v2rw: Add new filter querying framework + rewrite homepageYorhel3-295/+1
The new filter framework hasn't been extensively tested yet, but seems to mostly work. No real changes to the homepage, but I did manage to make a few queries a little bit faster. The most significant source of slowness is the releases listing, but I can't optimize that without adding indices.
2020-08-07Discussions: Fix handling of user deletionYorhel1-3/+3
Which I broke by removing the DEFAULT clause on threads_posts.uid. That column still used the old uid=0 for deleted users rather than the uid=NULL that I was planning to migrate the entire DB to. So while I'm fixing up the threads schema, may as well update this too. There's still a bunch of columns relying on uid=0, but I can fix that later.
2020-08-06Discussions: Allow discontinuous post numbers + ignore hidden posts in ↵Yorhel1-2/+2
counts/lastpost This solves a few problems: - 'hidden' posts will no longer cause the thread to be bumped to the front page. - Deleting posts will no longer cause other posts to be renumbered (and hence will not break existing links to posts) - Numbers of deleted posts will no longer be re-used (except when they were the last post in the thread - fixing this would require an additional column in 'threads', but it didn't seem worth the trouble)
2020-07-21Delete old producer edit form + a few more cleanupsYorhel2-44/+2
2020-07-19Delete old VN edit form + some other unreferenced codeYorhel2-89/+7
There's a lot more unreferenced code in VNDB::DB::*, but I'll not spend too much effort cleaning that up right now. All of it can be deleted in bulk at some point.
2020-07-12DB::VN: Exclude deleted VNs in random screenshot selectionYorhel1-0/+1
Deleted VNs could only be selected in the rare scenario where the screenshots from the deleted entry have also been assigned to another (non-deleted) entry. Fully deleted screenshots would already have been excluded with the c_weight check. Fixes https://vndb.org/t2520.457
2020-07-10DB::VN: Use image flagging status for random screenshots with filterYorhel1-4/+5
Instead of the old NSFW flag.
2020-06-15Releases: Allow custom resolutions to be enteredYorhel1-4/+6
The resolution field now works much like the engine field.
2020-06-07DB::VN: Fetch random homepage screenshots using TABLESAMPLEYorhel1-14/+20
For some reason the old query has gotten really slow (>300ms) in production after 50b9a5b7a59e896ed4a1f65141a31b8e64c33b55. It still ran in like 30ms on my dev setup, Postgres somehow found it necessary to switch to a different query plan. This new query also uses the new image flagging votes to determine which screenshots should be visible. Just happened to be easier and faster to implement.
2020-06-04Delete old character edit formYorhel2-49/+1
Not used anymore.
2020-04-24v2rw/VN::Page: Remove old and unused codeYorhel1-77/+0
There's a lot of unused code in VNDB::DB::{VN,Release} still, but I'll not fiddle with that for now.
2020-04-15SQL: Split "perm" column into multiple booleansYorhel1-1/+1
This simplifies several actions and makes permission checks more obvious. This is also yack shaving for another feature I've been planning to add: boardmods should be able to set other users' "board" permission, tagmods for tags, etc. So that partial user bans don't need the full "usermod" permission.
2020-04-09Stop keeping track of users/threads/posts count cacheYorhel1-11/+1
Those stats havent been displayed on the site for ages, anyway. The API will now report '0' for these stats.
2020-04-06Delete old relation graph generating and caching machineryYorhel2-13/+7
2020-04-06DB::VN: Fix 500 by randomly generating a "sf0" vndbidYorhel1-1/+1
2020-04-05SQL: Add C-based "vndbid" type and use it for image IDsYorhel3-16/+14
I had already rambled on the current composite type solution in 583ae868dfd3c882a8d2dd40b5d5ed099170c1c2 and I had already explored a few alternatives. This was the one alternative I hadn't yet explored because I wasn't sure the operational complexity was going to be worth it, but after seeing how bad PostgreSQL was at optimizing queries with composite types, I figured I might as well just go with this approach. It improves performance of some queries by a *lot* (especially the image selection query) and it's pretty elegant and convenient to work with. Only downside is the complexity of compiling, installing and maintaining a vndbid.so library for PostgreSQL.
2020-03-24DB::Releases: Also fetch the new links for the old code -.-Yorhel1-2/+2
2020-03-22Delete old release edit formYorhel2-49/+1
So that I can now work on adding a few more fields.
2020-03-12SQL: Keep track of dimensions for all images in the DBYorhel3-22/+11
Ought to simplify the image flagging UI.
2020-03-12SQL: Use global "images" table for metadata of all images in the DBYorhel2-19/+26
In preparation for an image flagging feature. This replaces the 'screenshots' table. I played around with various implementations of a primary key for the images table; A single integer encoding both the type and id is most efficient, but kind of ugly to work with. Two separate columns are also ugly and force the creation of a separate 'image_type' column on all referencing tables. The current composite type isn't all that much better, but at least it makes it easy to keep treating the image id as a single entity. Old VNDB::* code still treats it as an integer with different namespaces, new VNWeb::* code treats it as a proper composite type.
2020-02-21SQL: Allow tags_vn.uid to be NULL for deleted usersYorhel1-42/+1
This fixes the issue of tag votes getting deleted when a user account is deleted, despite these votes being part of the kept "database contributions" mentioned in the privacy policy.
2020-01-28v2rw: Convert the VN tagmod interfaceYorhel1-33/+1
This also changes the voting interface a little bit: - Spoiler options are a bit more concise - Mouse-over a button indicates what it does - The -1 and -2 options are not available anymore - Downvoted tags are hidden by default - Moderators can now vote-and-overrule in a single go
2019-12-29ulist: Display labels on VN browserYorhel1-0/+8
Replaces the old "wishlist status" column that I removed earlier. It's kind of awkward, but at least the functionality still exists now.
2019-12-24ulist: Delete old lists code and redirect old URLsYorhel1-274/+2
2019-12-22ulist: Update "My lists" VN filters to use new ulists featureYorhel1-9/+6
This is just a simple backwards-compatible port, proper integration should allow one to filter on individual labels. The "On my VN list" filter now works slightly differently in that it considers *everything* in ulist_vns to be on the users' VN list, including votes and wishlist. I've removed the option to display a "Wishlist" column. The wishlist works rather differently now, maybe this should instead just display all assigned labels - but that would consume quite a bit of space. :(
2019-12-22ulist: Use new lists for VN vote stats & listingYorhel1-9/+7
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-12v2rw: Discussion board editing & thread creationYorhel1-160/+1
Now with BBCode preview, interactive board search, client-side error reporting and lots of new bugs. This took me far too long, turns out it wasn't such a trivial rewrite.
2019-11-15Move pubskin fetching responsibility to VNWeb::HTML::framework_Yorhel1-2/+0
Requiring callers to fetch the right fields and pass it along to framework_() just seems brittle. This implementation requires an extra query but does make an effort to avoid fetching the skin and css when it's not needed.
2019-11-15v2rw: Convert discussion board searchYorhel1-19/+0
With minor styling improvements and full pagination for thread title search. I did combine a few queries which I initially separated for performance reasons. A few quick benchmarks show that it's fast enough, but I'll have to see how it performs in production.
2019-11-14v2rw: Convert staff pagesYorhel1-79/+2
This is where the ExtLink module comes in handy: generating the revision comparison thing is much easier now. Did find and fix a bunch of issues with the new revision box generator code, but that was to be expected, I hadn't tested that code well yet and this is its first more demanding use. Rest of this is a pretty direct rewrite, nothing too special.
2019-11-12v2rw: Move entryLinks() into separate VNDB::ExtLinks moduleYorhel1-30/+4
This is yak shaving. The new module doesn't have much value as it is, apart from having a central place to define link formats. This new enrich_extlinks() approach is also a bit more efficient in that it can avoid separate SQL queries for multiple objects. But the real reason for these changes is that I can use that %LINKS hash table to automatically generate the links part in the edit summary and it should (hopefully) also be useful to generate a more convenient/streamlined Elm edit form.
2019-11-02Add character age field + conversionYorhel1-3/+3
2019-11-02Add character cup size field + conversion + filterYorhel1-3/+5
2019-10-16v2rw: Convert staff adding/editing formYorhel2-42/+1
This is the first major editing form to be converted, so I'm expecting a little breakage. A good chunk of this code has been copied from v3. In terms of the UI there has been a small change: aliases that are still referenced do not have the 'remove' link and instead have a flag that shows that they are still referenced. This ought to be a bit friendlier than throwing an error message after the user has submitted the form. Some other things I'd like to improve in this form: - BBCode preview - Pasting in external links and letting the form figure out the Pixiv ID, etc. - Or perhaps even: Integrate AniDB/Wikidata search/autocompletion.
2019-10-14v2rw: Convert user listingYorhel1-36/+4
2019-10-14Delete the user deletion featureYorhel1-8/+1
It's rather much code just to run a single SQL statement that I almost never need. Incidentally, the feature was also broken because the DELETE permission wasn't granted in perms.sql.
2019-10-14Fix fetching of c_olang for revision pagesYorhel1-1/+1
2019-10-14Add lang HTML attribute to some original language fieldsYorhel1-1/+1
The database doesn't have a language attribute for every field, so this is more of a best-effort heuristic. The attribute should allow browsers to choose the correct font. https://vndb.org/t2520.300
2019-10-12rewards: Fix applying the public skin even when disabledYorhel1-1/+1
2019-10-12v2rw: Convert user notifications interface (/u+/notifies)Yorhel1-70/+0
2019-10-10rewards: Honor public custom css/skinYorhel1-1/+2
2019-10-10rewards: Apply supporters badge and unicode name (almost) everywhereYorhel11-29/+19
Only place where this isn't applied (yet?): Sorting user lists still goes by the old username and board names don't use the new unicode names. I have to say, I quite like the sql_user() and user_() pattern. It's not without problems when applied to everything, but it's good enough for several use cases.
2019-10-07SQL: Perform incremental updates on tags_vn_inherit when tags are modifiedYorhel1-0/+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-04VNDB::DB::Discussions: Don't count private threads in the discussions tab numberYorhel1-1/+1
2019-10-03Disallow changing email address to one already used by another accountYorhel1-50/+1
2019-10-03Fix 500 when sorting userlist by vote countYorhel1-1/+1
Broken in 8795f8a55df40603e3e589b584cc5d4c66e78f3a
2019-10-03SQL: Get rid of the users_prefs table, store preferences in users tableYorhel2-39/+7
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.
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-18 18:59:17 +0000