path: root/lib/VNDB/DB
AgeCommit message (Collapse)AuthorFilesLines
2018-12-06DB::Chars: Fix SQL operator precedence bug in search filterYorhel1-1/+1
This would cause other filters to sometimes be ignored, depending on the order in which the %where hash was being serialized. Broken in 8802eac4f.
2018-10-31VNEdit: Remove "import cast" functionalityYorhel1-13/+1
2018-10-25DB::Releases: Don't return duplicate releases in dbReleaseGet(vid => [...])Yorhel1-2/+1
In the special case where the releases of multiple VNs are requested, and those VNs have releases in common, dbReleaseGet() would return those releases multiple times. Using a JOIN in order to filter rows isn't safe if the join condition isn't unique - so use an "id IN(SELECT ..)" filter instead. (I found this while editing c15068 and noticing that some releases were listed twice in the edit form. Editing that entry without manually removing those duplicates would trigger an internal server error due to duplicate relations)
2018-09-29Add default spoiler level to tags and use it in spoiler calculationYorhel1-5/+5 (And I forgot to add update_20180929.sql in the previous commit)
2018-09-29Add default spoiler level to traits and use it on character editYorhel1-4/+4
2018-05-25Add uncensored flag to release entriesYorhel1-6/+7
As discussed in
2018-05-24DB::Chars: Ignore spaces when matching original namesYorhel1-1/+1
2018-02-08Store d+ pages in the DB as versioned entries + use markdownYorhel2-1/+56
This touches a bunch of things: - Adds a new first-class database entry type - Removes the d+.+.+ BBCode link syntax, adds a new d+#+ and d+#+.+ link syntax (references have been updated where possible) - Adds a new dependency on Text::MultiMarkdown
2018-01-12Don't allow unlikely short GTIN codesYorhel1-1/+1
Should fix
2017-03-03Add date filters to VN searchYorhel1-1/+6
2016-11-27SQL: Use separate role for the website + disallow access to user dataYorhel1-33/+65
Previously the website was connected to the database with a "database owner" user, which has far too many permissions. Now there's a special vndb_site user with only the necessary permissions. The primary reason to do this is to decrease the impact if the site process is compromised. E.g. it's now no longer possible to delete or modify old entry revisions. An attacker can still do a lot of damage, however. Additionally (and this was the main reason to implement this change in the first place), the user sessions, passwords and email data is now not easily accessible anymore. Hopefully, the new user management abstractions will prevent email and password dumps in case of an SQL injection or RCE vulnerability in the site code. Of course, this only works if my implementation is fully correct and there's no privilige escalation vulnerability somewhere. Furthermore, changing your password now invalidates any existing sessions, and the password reset function is disabled for 'usermods' (because usermods can list email addresses from the database, and the password reset function could still allow an attacker to gain access to anyone's account). I also changed the format of the password reset tokens, as they totally don't need to be salted.
2016-11-03List deleted releases when adding a new releaseYorhel1-5/+6
2016-11-03Add duplicate check to producer entryYorhel1-2/+2
2016-09-18Suppress silly Perl 5.22 warnings about extra sprintf argsYorhel2-0/+2
2016-07-03Generalize substring search relevance + apply to most dropdown searchesYorhel4-19/+34
This is a generalization of the search improvements made in 7da2edeaa0f6cf7794f4f8f68960497dc1be893c and 92235222dba4e5d0c7713d53ef12e0f10e371b83 And has been applied to the dropdown searches for producers, staff, tags and traits. For all those searches, exact matches are listed first, followed by prefix matches, and then substring matches. Relevance is currently only based on the primary name/title and ignores aliases (except for staff). This is fixable, but not trivial, and I'm not sure it's all that useful.
2016-07-03DB::Tags::dbTTTree: Use hash lookup for parent IDsYorhel1-3/+4
Reduces page load time of the trait index from 200ms to 20ms. Also provides a slight improvement for other tag/trait tree views.
2016-07-03Charedit/traits: Prioritize exact match when finding traitsYorhel1-1/+2
2016-07-03Various staff search improvementsYorhel1-2/+3
- Exact match is now case-insensitive - Main staff search supports exact match with =-prefix - On VN edit dropdown: exact matches are sorted before other matches - VN edit dropdown now also displays original name
2016-01-10DB::Users: Perform case-insensitive email matchingYorhel1-1/+1
This fixes two things: - It's not possible to create two accounts with the same mail address with different case (although the user+xyz@domain trick still works). - The password reset form is now case-insensitive as well. Some people had problems with the case-sensitive behavior in the past.
2016-01-10DB::(VN|Producers): Don't decode_utf8() xml valuesYorhel2-4/+0
Newer versions of DBD::Pg do this automatically.
2015-12-31DB::Releases: Don't match non-patch fields for patch releasesYorhel1-6/+6
2015-12-30Add release filters to VN browserYorhel2-16/+33
2015-11-28VNPage: Generalize seiyuu listing to character summary listingYorhel1-12/+6
The comment already suggested this: I wonder whether it's better to just ask database for character list instead of doing this manual group/sort So yeah, let's just do that.
2015-11-26Handler::VNPage: Move staff tab into main VN page + some styling changesYorhel1-9/+1
The styling of the staff info can be a bit awkward at times, but it looks slightly better than a table, IMO. I didn't really know what to do with the the seiyuu info - it wastes a lot of screen space in its current implementation, but I can't think of anything better at the moment.
2015-11-14DB::VN: Fix undeterministic ordering on tag score in VN listingsYorhel1-1/+1
2015-11-11Misc poll improvementsYorhel2-107/+72
- Merged polls table into threads table. Not much of a storage/performance difference, and it's a bit simpler this way. - Merged DB::Polls into DB::Discussions. Mainly because of the above change in DB structure. - Add option to remove an existing poll. - Allow preview and recast to be changed without deleting the votes - Set preview option by default. Because personal preferences. :) - Minor form validation differences
2015-11-10Merge branch 'poll' of into pollsYorhel2-0/+97
2015-11-10Merge branch 'master' into pollmorkt10-472/+568
2015-11-10Add language filter to staff browserYorhel1-0/+1
2015-11-02DB::VN: Fix dbVNHasStaff() query when VN has only seiyuu and no staffYorhel1-1/+1
2015-11-01DB::(Producers|VN): Convert svg column to perl encoding when fetchingYorhel2-0/+3
DBD::Pg doesn't recognize the 'xml' data type as textual data, and thus doesn't decode it for us. This fixes the display of non-ASCII characters.
2015-10-25DB::Discussions: Fix bug in 'notusers' queryYorhel1-1/+1
'<> ANY' doesn't work that way. NOT EXISTS() is also pretty fast and does what we want.
2015-10-25DB::ULists: Revert change from 'numeric' to 'float' to fix vote graphsYorhel1-2/+2
Broken in bbe989de364ddc654bfc6385e22f1eaff23faad1. I forgot that floats can't accurately represent some .5 numbers.
2015-10-25DB::Discussions: Minor improvement to post <-> board filter query2.25Yorhel1-1/+1
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.
2015-10-25Staff: Add error msg when removing used alias + fix bug in alias editingYorhel1-2/+2
The new database schema doesn't allow an alias to be removed when it is still linked to a VN.
2015-10-24DB::Staff: Fix 'aid' filter, used when editing VN staff infoYorhel1-1/+1
This broken filter would cause all staff info to be deleted from a VN upon edit. Not so nice.
2015-10-24SQL: Throwing around some indicesYorhel1-2/+2
These indices provide a significant speed-up of /v+ and /u+ pages, and improve some other stuff as well.
2015-10-24Improve several discussion board SQL queriesYorhel1-8/+14
An index on 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.
2015-10-21Improve SQL in dbRevisionGet()Yorhel1-46/+33
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.
2015-10-21SQL: Fix editing + func.sql + triggers.sql + autocreate editing funcsYorhel5-33/+32
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.
2015-10-18discussion board polls.morkt1-0/+2
2015-10-18discussion board polls.morkt1-0/+95
2015-10-17SQL: Fix all browsing queries to use the new schemaYorhel10-395/+496
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.
2015-10-03Handle JSON data natively when processing form dataYorhel1-2/+2
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
2015-10-03Rewrote screenshot uploader to support multiple files + use jsonYorhel1-1/+1
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.
2015-09-17Created json_validate() for JSON form data, used for Staff alias editorYorhel1-6/+5
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.
2015-09-07Handler::Discussions: Fix two bugs introduced in last commitYorhel1-1/+1
2015-09-07Handler::Discussions: Use ts_headline() to format search resultsYorhel1-2/+20
And also fix strip_bb_tags() to be case-insensitive and fix a bug in converting the query into a tsquery.
2015-09-07Implement discussion board search functionYorhel1-5/+16
Inspired by wakaranai's implementation at 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
2015-09-06SQL: Convert threads_board.type to ENUMYorhel1-2/+2
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.