summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/VN.pm
AgeCommit message (Collapse)AuthorFilesLines
2011-01-02Bugfix: Don't show NSFW screenshots on homepage with filtersYorhel1-1/+1
2011-01-01Also apply permanent VN filters to the random screenshots on /Yorhel1-3/+22
2010-12-21Pass VN tag filters by ID rather than nameYorhel1-9/+15
This makes the UI slightly uglier and less intuitive. I'll see if I can find a way around that. This update is required for the permanent browsing filters to be fast and reliable.
2010-11-28VN Filter Selector: Added "original language" filterYorhel1-1/+3
The original language is the language of the first release of the VN, and is cached in vn.c_olang. (Geez, the vn table has more caching columns than actual information >.>)
2010-11-27VN Filter Selector: Added "has anime" filterYorhel1-1/+3
Also requested several times before.
2010-11-27VN Filter Selector: Added length field filterYorhel1-1/+3
Has been requested quite often, now finally implemented.
2010-11-27Replaced old VN advanced options with the new filter selection systemYorhel1-5/+5
Had to fix some bugs here and there and add some new functionality to the abstractions at some places, but it appears to be working now. There are still a few TODOs left, I'll get to those in a bit.
2010-11-10Started on adding an "official" flag to vn<->vn relationsYorhel1-5/+6
This is the first part. The flag is stored in the database, can be edited through the usual VN edit form, and is displayed in the diff viewer. Things to do to make this feature fully functional: - display "official" status on VN page at the relation listing - update relation graphs to display unofficial relations differently - update guidelines
2010-11-07Optimized random VN fetching queryYorhel1-1/+7
Similar to ab64b573846da39622b8d430b079d7e8806a35d3, but with a few more constraints as dbVNGet() is a more generic function. This and the other commit greatly improve the page generation time of the homepage. From ~250ms to ~110ms in my tests.
2010-11-07Optimized random screenshot fetching queryYorhel1-8/+12
By rewriting the query and using the trick documented here: http://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/ Can be further optimized by putting an index on vn_screenshots.scr
2010-11-02Multi::Image: Abstracted image dimension calculation into VNDBUtilYorhel1-1/+2
Which may also be useful for other scripts.
2010-03-13Improved VN searchYorhel1-28/+5
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.
2010-02-05Merge branch 'master' into betaYorhel1-2/+2
Conflicts: data/lang.txt
2010-02-02SQL: Converted language columns to an ENUM typeYorhel1-3/+3
And changed vn.c_languages to an array type while I was at it. This required some changes in the Perl code, and I found a bug in DBD::Pg while I was at it: https://rt.cpan.org/Ticket/Display.html?id=54224 Luckily, there's an easy workaround for that.
2010-01-24Versioned the deleting and locking of database entriesYorhel1-1/+1
This is implemented by adding ihid (item hidden) and ilock (item locked) columns to the changes table, The (vn|release|producer).(hidden|locked) columns now work as a cache, refering to the changes.(ihid|ilock) columns with changes.id = (vn|release|producer).latest. The cached columns are updated automatically each time a new revision is inserted. This is a pretty large change, bugs are quite likely.
2010-01-23DB::VN: Fixed ranking bug on VN pages with a NULL popularity or ratingYorhel1-2/+2
2010-01-01SQL: Made a start on revision insertion abstractionYorhel1-27/+28
This will make it easier to do automated edits, either from cron jobs, Multi, update scripts, or from within SQL triggers. So far only the VN related functions have been defined/updated, trying to edit/add releases or producers will not work at the moment. The functions for editing or adding a new database entry have been merged, as the procedure is rather similar. util/dump.sql will be updated later on.
2009-12-05SQL: Removed changes.causedbyYorhel1-1/+1
This column was used to differentiate between automated edits and user edits, but that later changed to checking for changes.requester = 1. The column has since never really been used, and due to a bug introduced in VNDB 2.0, it has never been updated, either. Meaning it's not even accurate for any database changes made after december 2008...
2009-12-05SQL: Call update_vncache() in a triggerYorhel1-8/+1
This removes the need for the dbVNCache() function in perl.
2009-12-05Merged db[VN|Producer|Release][Edit|Add] into dbItemEdit and dbItemAddYorhel1-23/+3
And also changed the way the item_table.latest column was updated: it is now only updated after the revision insert has completed, making it easier to write trigger functions in SQL.
2009-11-29SQL: Replace index on tags_vn_inherit (tag) with an index on (tag,vid)Yorhel1-0/+1
This drastically improves the performance of the search-VN-tag-filter feature, and it seems PostgreSQL can use the index even when only filtering results by the tag column.
2009-11-27DB: Abstracted all ORDER BY clauses in the DB abstraction layerYorhel1-4/+14
The ORDER BY was previously specified using an 'order' argument, which would then be directly inserted into the query. The new method is the same as what I used for the public API: two 'sort' and 'reverse' arguments. This should be less error-prone and more readable. This changes quite a lot of code, so I hope I haven't forgotten to update something along the way.
2009-11-27Changed VN score on tags pages to display plain averagesYorhel1-3/+3
For three reasons: - Speed tag_vn_calc() is now more than 10 times faster (granted, it could have been a lot faster even with the bayesian rating, but whatever) - Consistency with the tag scores displayed on the VN pages (which are raw averages as well) - It didn't always make sense
2009-11-14SQL: Cached bayesian VN rating and vote countsYorhel1-6/+4
Was a good idea after all...
2009-11-14Added bayesian rating info to VN pagesYorhel1-2/+5
This adds about 100ms (sometimes more) to the page generation time of VN pages... maybe I should cache the ratings after all.
2009-11-14Added bayesian rating and vote count to the VN listYorhel1-1/+4
It's even realtime! To my surprise this calculation isn't very heavy, or PostgreSQL is just extremely fast. The GetVN query on /v/all takes 100ms in the worst case (instead of the usual 30-60ms). Can always cache this later on.
2009-10-21SQL: Renamed vn_graphs to relgraphs and synced dbgraph.plYorhel1-1/+1
As the same table can easily be used to store producer relation graphs as well.
2009-10-12SQL: Converted changes.type to an ENUMYorhel1-2/+2
This is a very important column in a very important table, I hope I didn't forget to update a piece of code somewhere...
2009-09-26Properly handle UTF-8 in relation graph SVGsYorhel1-0/+5
This is a workaround for a bug in DBD::Pg: http://rt.cpan.org/Public/Bug/Display.html?id=40199 Also added a charset to the content type header of the relation graph pages, though this wasn't really necessary for my Firefox to work.
2009-09-25Use inline SVG for relation graphsYorhel1-2/+2
The graphs are now stored in the DB in SVG format, the static/rg/ directory can be removed (not used anymore). SVG data is stored using the xml data type, so now I can say for sure you'd need at least PostgreSQL 8.3. This feature still needs some tweaking, though. Current state isn't perfect.
2009-08-09Converted changes.added to timestamptzYorhel1-1/+1
That was the last one. I hope I haven't forgotten to update anything.
2009-08-08Improved handling of the timestamp columns in anime and session tablesYorhel1-1/+1
Anything fetched from the DB to Perl should be converted to a UNIX timestamp, and everything that goes from Perl to the DB should be converted from a UNIX timestamp to a timestamptz data type. Also, when creating a session, don't rely on the fact that the expiration default happens to be the same as the cookie expiration time calculated in Perl. It's cleaner to calculate the date at one place and then use that everywhere else.
2009-08-08Removed last traces of the category systemYorhel1-20/+3
2009-07-19Converted and renamed screenshots.status (int) to processed (bool)Yorhel1-1/+1
There were only two states, processed and unprocessed, so simply using a boolean column with correct naming is more clarifying.
2009-07-18Automatically insert rows into anime table for new vn_anime.aid itemsYorhel1-18/+5
This is a lot less error-prone than doing it from Perl. <3 PostgreSQL
2009-07-05Tag exclude filters on VN searchYorhel1-1/+3
2009-07-05Added spoiler options for tag filters on VN searchYorhel1-3/+5
2009-07-05Made a start on the multiple-tag-VN-searchYorhel1-1/+6
TODO: - spoiler settings? - auto-complete tag names - exclude filter - Improved UI? current location isn't very intuitive - Improve previous tag browser to make use of the VN search?
2009-06-07Merge branch 'beta'2.4Yorhel1-19/+4
+ Set 2.4 date in ChangeLog
2009-05-24Removed category filters from /v/allYorhel1-14/+1
The tags work fine already. I'll keep the categories on the VN pages and in the history for now, though.
2009-05-24Don't perform search subquery when there are no keywords to search onYorhel1-4/+3
2009-05-24Allow search queries with only one characterYorhel1-1/+0
2009-05-10Fixed off-by-one error in popularity ranking on v+Yorhel1-1/+1
2009-05-03Don't show screenshots of hidden VNs on homepageYorhel1-1/+1
2009-05-03Don't count hidden VNs in ranking + first rank considered with shared placeYorhel1-1/+1
Fixes: For newly added vns popularity status is something like Ranked #1709 out of 1692 with a score of 0.00.
2009-01-16Let VN search match on vn->original, and regard . and _ as spacesYorhel1-3/+3
2009-01-15Fixed a very old bug with the VN search matching on release titles of older ↵Yorhel1-1/+3
revisions It's a pretty tricky bug, so finding a proper fix took a while...
2009-01-10Popularity indicator on VN pagesYorhel1-1/+2
2009-01-10Added VN popularity rankingYorhel1-1/+1
The following query should be run periodically to update the rankings: SELECT update_vnpopularity(); I'll fix Multi::Maintenance to do this automatically.
2008-12-15Decreased number of screenshots on the homepage to 4Yorhel1-1/+1