diff options
-rw-r--r-- | ChangeLog | 9 | ||||
-rw-r--r-- | Makefile | 9 | ||||
-rw-r--r-- | README | 5 | ||||
-rw-r--r-- | data/global.pl | 1 | ||||
-rw-r--r-- | data/lang.txt | 156 | ||||
-rw-r--r-- | data/notes/atom-feeds | 26 | ||||
-rw-r--r-- | data/notes/mylist-revamp | 86 | ||||
-rw-r--r-- | data/notes/notifications | 20 | ||||
-rw-r--r-- | data/notes/permanent-filters | 88 | ||||
-rw-r--r-- | data/notes/preferences | 117 | ||||
-rw-r--r-- | data/notes/sponsored-links | 108 | ||||
-rw-r--r-- | data/notes/tagmod-overrule | 55 | ||||
-rw-r--r-- | data/script.js | 81 | ||||
-rw-r--r-- | data/style.css | 2 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 26 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 12 | ||||
-rw-r--r-- | lib/VNDB/Handler/Tags.pm | 125 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 7 | ||||
-rw-r--r-- | lib/VNDB/Util/Misc.pm | 7 | ||||
-rw-r--r-- | util/sql/all.sql | 1 | ||||
-rw-r--r-- | util/sql/func.sql | 3 | ||||
-rw-r--r-- | util/sql/schema.sql | 3 | ||||
-rw-r--r-- | util/updates/update_2.18.sql | 8 |
23 files changed, 880 insertions, 75 deletions
@@ -1,4 +1,11 @@ -2.18 - +2.18 - 2011-02-08 + - Added category field to tags (content/ero/technical) + - Group tags on /v+/tagmod by their category + - Added tag visibility options by category on /v+ + - Added filter selector to tag pages (excl. tags tab) + - Added new VN filters: wish/blacklist, voted, on VN list + - Added tooltip to the overruled-exclamation-mark + - Bugfix: don't generate listdel notify for the user who deleted 2.17 - 2011-02-04 - Allow moderators to overrule VN tag votes @@ -37,8 +37,8 @@ # environments. Patches to improve the portability are always welcome. -.PHONY: all dirs js skins robots chmod chmod-tladmin multi-stop multi-start multi-restart\ - sql-import update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 update-2.15 update-2.16 update-2.17 +.PHONY: all dirs js skins robots chmod chmod-tladmin multi-stop multi-start multi-restart sql-import\ + update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 update-2.15 update-2.16 update-2.17 update-2.18 all: dirs js skins robots data/config.pl @@ -168,3 +168,8 @@ update-2.17: all $(multi-stop) ${runpsql} < util/updates/update_2.17.sql $(multi-start) + +update-2.18: all + $(multi-stop) + ${runpsql} < util/updates/update_2.18.sql + $(multi-start) @@ -7,6 +7,11 @@ Installation & documentation Documentation is lacking, you're pretty much on your own if you want to get things running. :-( + Development notes for specific features or ideas can be found in data/notes/. + Keep in mind, however, that these notes are often mostly technical and may + not reflect the actual (current) implementation. They do elaborate on various + design decisions and may be useful for understanding how certain things work. + Requirements diff --git a/data/global.pl b/data/global.pl index 971db80a..30feb436 100644 --- a/data/global.pl +++ b/data/global.pl @@ -99,6 +99,7 @@ our %S = (%S, [ '1280x800', '_scrres_ws' ], [ '1920x1080', '_scrres_ws' ], ], + tag_categories => [ qw|cont ero tech| ], voiced => [ 0..4 ], animated => [ 0..4 ], wishlist_status => [ 0..3 ], diff --git a/data/lang.txt b/data/lang.txt index 7a470697..37218e03 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -1137,6 +1137,29 @@ hu : Lemondva nl : Opgegeven +# Tag categories + +:_tagcat_cont +en : Content +ru*: +cs*: +hu*: +nl : Inhoud + +:_tagcat_ero +en : Sexual content +ru*: +cs*: +hu*: +nl : Erotisch + +:_tagcat_tech +en : Technical +ru*: +cs*: +hu*: +nl : Technisch + ############################################################################# @@ -3930,6 +3953,13 @@ cs : Tagy hu : Címkék nl : +:_tagp_cat +en : Category +ru*: +cs*: +hu*: +nl : Categorie + :_tagp_aliases en : Aliases ru : Прочие названия @@ -4158,6 +4188,27 @@ cs : VAROVÁNÍ: Zaškrtnutí této volby nebo výběr položky "Smazáno" jako hu : FIGYELEM: Ezen opció bejelölése vagy a "Törölve" állapot kiválasztása véglegesen megsemmisít minden összefüggést a VN-ekel! nl : WAARSCHUWING: Als je deze optie selecteerd of als je de status op "verwijderd" zet zullen alle stemmen permanent verwijderd worden! +:_tagedit_frm_cat +en : Category +ru*: +cs*: +hu*: +nl : Categorie + +:_tagedit_frm_catrec +en : Also edit all child tags to have this category +ru*: +cs*: +hu*: +nl : Zet deze categorie ook voor alle subtags + +:_tagedit_frm_catrec_warn +en : WARNING: This will overwrite the category field for all child tags, this action can not be reverted! +ru*: +cs*: +hu*: +nl : WAARSCHUWING: Hiermee wordt het categoriefeld voor alle subtags overschreven, dit kan niet ongedaan worden gemaakt! + :_tagedit_frm_alias en : Aliases (separated by newlines) @@ -4511,6 +4562,13 @@ cs : Spoiler hu : Spoiler nl : +:_tagv_overruletip +en : Tag overruled. All votes other than that of the moderator who overruled it will be ignored. +ru*: +cs*: +hu*: +nl : Tag overschreven. Alle stemmen, behalve die van de moderator die de tag heeft overschreven, zullen worden genegeerd. + :_tagv_who en : Who? ru : Кто? @@ -4518,6 +4576,13 @@ cs*: hu : Kicsoda? nl : Wie? +:_tagv_newlyadded +en : Newly added +ru*: +cs*: +hu*: +nl : Net toegevoegd + :_tagv_save en : Save changes ru : Сохранить изменения @@ -5839,6 +5904,13 @@ cs : Tagy hu : Címkék nl : +:_vnbrowse_tagnothere +en : Additional tag filters are not available on this page. Use the visual novel browser instead (available from the main menu -> visual novels). +ru*: +cs*: +hu*: +nl : Extra tag filters zijn niet aanwezig op deze pagina. Gebruik de visual novel browser voor deze functionaliteit (beschikbaar via het hoofdmenu -> visual novels). + :_vnbrowse_tagactive en : These filters are ignored on tag pages (when set as default). ru : Эти фильтры недействительны на страницах тегов (по умолчанию). @@ -5909,6 +5981,90 @@ cs : Platforma hu : Platformok nl : +:_vnbrowse_ul +en : My lists +ru*: +cs*: +hu*: +nl : Mijn lijsten + +:_vnbrowse_ul_notblack +en : Blacklist +ru*: +cs*: +hu*: +nl : + +:_vnbrowse_ul_notblackmsg +en : Exclude VNs on my blacklist +ru*: +cs*: +hu*: +nl : Sluit VNs op mijn blacklist uit + +:_vnbrowse_ul_onwish +en : Wishlist +ru*: +cs*: +hu*: +nl : Wensenlijst + +:_vnbrowse_ul_onwishno +en : Not on my wishlist +ru*: +cs*: +hu*: +nl : Niet op mijn wensenlijst + +:_vnbrowse_ul_onwishyes +en : On my wishlist +ru*: +cs*: +hu*: +nl : Op mijn wensenlijst + +:_vnbrowse_ul_voted +en : Voted +ru*: +cs*: +hu*: +nl : Gestemd + +:_vnbrowse_ul_votedno +en : Not voted on +ru*: +cs*: +hu*: +nl : Niet op gestemd + +:_vnbrowse_ul_votedyes +en : Voted on +ru*: +cs*: +hu*: +nl : Op gestemd + +:_vnbrowse_ul_onlist +en : VN list +ru*: +cs*: +hu*: +nl : VN lijst + +:_vnbrowse_ul_onlistno +en : Not on my VN list +ru*: +cs*: +hu*: +nl : Niet op mijn VN lijst + +:_vnbrowse_ul_onlistyes +en : On my VN list +ru*: +cs*: +hu*: +nl : Op mijn VN lijst + # VN add/edit form (/v+/edit) diff --git a/data/notes/atom-feeds b/data/notes/atom-feeds new file mode 100644 index 00000000..f1be17e8 --- /dev/null +++ b/data/notes/atom-feeds @@ -0,0 +1,26 @@ +Atom Feeds + +Last modified: 2010-11-13 +Status: Implemented + + +New module: Multi::Feed +Automatically generates and updates the following feeds: + www/feeds/ + announcements.atom + Updated?: LISTEN 'newpost'; post.num = 1 and board = 'an' + (what about an edit of the annoucement title/content?) + changes.atom + Updated?: LISTEN 'changes' + posts.atom + Updated?: LISTEN 'newpost' + (what about edits of posts? title/contents can change...) + released.atom (not implemented) + Updated?: daily + LISTEN 'changes'; c.type = 'r' + (more restrictions can be added if the generation time of this feed is long) + +All feeds are updated once every 15 minutes; this is easier and less +error-prone than the above notify solutions that differ for each feed. +Assuming all feeds can be generated in one second, this takes +(1/(15*60))*100 = ~0.1% of server CPU time on average. + diff --git a/data/notes/mylist-revamp b/data/notes/mylist-revamp new file mode 100644 index 00000000..4335b7fd --- /dev/null +++ b/data/notes/mylist-revamp @@ -0,0 +1,86 @@ +RFC-01: Mylist revamp + +Last modified: 2010-12-19 +Status: Implemented + + +CREATE TABLE vnlists ( + uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE, + vid integer NOT NULL REFERENCES vn (id), + status smallint NOT NULL DEFAULT 0, + added TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- likely not used, but whatever + PRIMARY KEY(uid, vid) +); + +-- after converting: +ALTER TABLE rlists DROP COLUMN vstat; +ALTER TABLE rlists ALTER COLUMN rstat RENAME TO status; + +vnlist.status: Unknown / Playing / Finished / Stalled / Dropped + + +Converting from old rlists: + vstat = X for all releases -> status = X + vstat = (X\{unknown}) for all releases with vstat != unknown -> status = X + vstat = (stalled, dropped) for all releases with vstat != unknown -> status = stalled + vstat = (finished, stalled, dropped) for all releases with vstat != unknown -> status = finished + vstat = (playing, ..) for all releases with vstat != unknown -> status = playing +Rephrased in easier terms: + status = first_present([playing, finished, stalled, dropped, unknown], @vstat) + Where first_present(<order>, <list>) returns the first item in <list> when using the order of <order> + Since the statusses are coincidentally defined as an integer with a mapping + in that order (with playing being the lowest number), we can simply say: + status = min(@vstat without unknown) || unknown + + +Constraint: + For each row in rlists, there should be at least one corresponding row in + vnlists for at least one of the VNs linked to that release. + This will significantly simplify the the "show my VN list" query, and gives + the user the option to not add *all* VNs linked to the release to his list. + + Example: the "Infinity Plus" release can be in your rlist, even when only + E17 is in your vnlist. As long as at least one of the infinity series is + in your vnlist. + + How to enforce: + - When a row is deleted from vnlists, also remove all rows from rlists that + would otherwise not have a corresponding row in vnlists + - When a row is inserted to rlists and there is not yet a corresponding row + in vnlists, add a row in vnlists (with status=unknown) for each vn linked + to the release. + Alternatively it's possible to add only one of the linked vns, but since + we can't decide for the user which one he wants, let's just add all of + them. + - Deleting a row from rlists or inserting a row to vnlists will never cause + the constraint to be violated. + - Strictly, updating rlists.rid or vnlists.vid should also trigger a check, + but since those columns are never updated we can ignore that. + + How to implement: + - Unfortunately it's not possible to use a real SQL CONSTRAINT for this, + due to the complexity of the references. + - SQL triggers would work. This is the easiest way to ensure the constraint + is enforced even when rows are inserted/deleted in rlists or vnlists from + within other triggers or constraints. (e.g. auto-delete vnlist entry when + VN is hidden or something - bad idea but whatever :P) + The triggers should probably be defined as CONSTRAINT TRIGGERs and be + DEFFERABLE. CONSTRAINT TRIGGERs because otherwise the "ON DELETE CASCADE" + on users.id might do too much work when a user is deleted. DEFFERABLE + because otherwise one would have to be careful when adding rlists rows + before vnlists rows. (Doesn't happen with the current code, but oh well) + + +"My VN List" table layout: + H: | | | Title <sort> | Status | Releases* | Vote <sort> | + V: | check | expand | title | status | releases | vote | + R: | | check | date | icons | title | <pad> status | | | + F: | <all> | <all> | <select> <select> <send> | <expl> | + C: | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | + + +Misc. things to keep in mind: +- Update 'listdel' notification to also check the vnlists table +- Allow users to remove rows from vnlists and rlists even when the + corresponding vn/release entry is hidden. + diff --git a/data/notes/notifications b/data/notes/notifications new file mode 100644 index 00000000..4743f6ee --- /dev/null +++ b/data/notes/notifications @@ -0,0 +1,20 @@ +Notifications + +Last modified: 2010-02-06 +Status: Implemented + + ++ = implemented +- = planned + +Always: ++ pm notify for a new post or thread in my discussion board ++ dbdel notify for the deletion of an entry I made or edited ++ listdel notify for the deletion of an entry I voted on / have in my release list / wishlist + +Option "Notify me about database entries I contributed to" (enabled by default) ++ dbedit notify for each edit of an entry I made or edited + +Option "Notify me for site announcements" (disabled by default - too many notifications otherwise) ++ announce notify for each new thread in the 'an' board + diff --git a/data/notes/permanent-filters b/data/notes/permanent-filters new file mode 100644 index 00000000..768a8a71 --- /dev/null +++ b/data/notes/permanent-filters @@ -0,0 +1,88 @@ +Permanent VN/release filters + +Last modified: 2011-01-01 +Status: Implemented + + +Storage: +- format: the usual filter string (as used in fil=X query string) +- location: users_prefs, key = filter_(vn|release) + + +How to fetch entries within Perl with the filters applied: + Special wrapper function for db(VN|Release)Get(), which does the following: + + # compatibility checking/converting + function check_compat(fil, save): + if filters_contain_old_stuff then + fil = convert_old_stuff(filters) + if save then + save_preference(filter_vn, serialize_filter(filters)) + end if + end if + return fil + + function filVNGet(fil_overwrite, opts): + if (not logged_in or not filter_preference) and not fil_overwrite then + return dbFunc(opts) + end if + + filters = check_compat(parse_filter(fil_overwrite || filter_preference), fil_overwrite?dontsave:save) + + # incorrect filters can trigger an error, catch such an error and remove + # the preference if that was what caused the error + if(fil_overwrite) # preferences can't cause the error + return dbFunc(filters + opts); + else + try + create_sql_savepoint() + return dbFunc(filters + opts) + error + rollback_to_sql_savepoint() + results = dbFunc(opts) + # if the previous call also fails, the next command won't be executed + delete_filters_preference() + return results + + A filReleaseGet() would do something similar. In fact, it might make sense + to combine it into a single function filFetchDB(type, fil, opts) + Filters can be disabled by adding a '<filter_name> => undef' to opts. + + +All cases where the current code calls dbVNGet() should be checked and +considered for replacing with the above fetching function. Some cases are: +VN: +- Random visual novels on homepage +- "Random visual novel" menu link +- VN browser + In this case the query string should overwrite preferences? Since + the preference is loaded in the filter selector as a default anyway +- Tag page VN listing + The tag_inc and tag_exc filters should be disabled here? +- Preferably also the random screenshots on the homepage. But this requires + some more code changes. +Release: +- "Upcoming releases" and "Just released" on homepage +- Release browser + Same note as VN browser above + + +Some cases that shouldn't be affected by the filter preferences: +- Edit histories +- User lists (votes, vnlist, wishlist) +- Tag link browser +- VN page release listing +- VN page relations listing +- Producer page VN/release listing +- Release page VN listing +- Database Statistics + (Even if they should, I wouldn't do it. Too heavy on server resources) + + +User interface considerations: +- An extra button "Save as default" will be added to the filter selector if + the visitor is logged in +- Ideally, there should be some indication that filters were applied to all + places where they are used, with the possibility of changing them. + (this is going to to be a pain to implement :-/) + diff --git a/data/notes/preferences b/data/notes/preferences new file mode 100644 index 00000000..0629e51f --- /dev/null +++ b/data/notes/preferences @@ -0,0 +1,117 @@ +User preference storage + +Last modified: 2011-02-06 +Status: Long-term plans / partially implemented + + +up = SQL: users_prefs +Preference old storage method Current storage method Can be changed at +- Interface language Browser or cookie: l10n Browser/up/cookie Perl: Link in main menu (explicit) +- Main skin SQL: users.skin up: skin Perl: Users' profile (explicit) +- Additional CSS SQL: users.customcss up: customcss Perl: Users' profile (explicit) +- NSFW toggle SQL: users.show_nsfw up: show_nsfw Perl: Users' profile (explicit) +- List is private SQL: users.show_list up: hide_list Perl: Users' profile (explicit) +- Notify on announce SQL: users.notify_announce up: notify_announce Perl: Users' notifications page (explicit) +- Notify on DB edit SQL: users.notify_dbedit up: notify_nodbedit Perl: Users' notifications page (explicit) +- Tag spoil level Cookie: tagspoil Cookie: tagspoil JS: VN pages, Tag pages, VN filter settings (all implicit) +- Tag VN page cat - Cookie: tagcat JS: VN pages (implicit) +- Producer page view Cookie: prodrelexpand Cookie: prodrelexpand JS: Producer pages (implicit) +- VN filters - up: filter_vn JS: VN filter settings (explicit) +- Release filters - up: filter_release JS: Release filter settings (explicit) + + +What do we want? +- Ideally, all preferences are saved explicitly. That is, the user can + indicate whether the change of a preference is temporary or should be saved + as the new default. +- Ideally, all preferences are stored on the server. This makes it easy to + convert the preference data on VNDB updates, without having to provide + backwards compatibility with old data. It also scales better than cookies. +- Preferably, you don't have to have an account to set or change preferences. + In the case of the interface language it's quite important that users don't + have to be logged in. For other preferences it's not very important, but I + don't really like the idea of forcing people to create an account. +- Preferably, the user can change each preference at the place where it makes + most sense: + - Default NSFW flag should be set when encountering an NSFW image + - Skin and custom CSS settings should be somewhere in the global page + layout (like the language setting currently is) + - The "my list is private" setting should be set when viewing your + wish/vote/VN list. + Although... this one might be okay on the profile page. + - Most other preferences already are at sensible locations + In particular, I don't like the idea of grouping all preferences on a + single "settings" or "profile" page. This is likely to become a mess (see + AniDB for a nice example), and users might not know something is available + as a preference (like how most users don't know VNDB has skins). +- Don't store everything in separate columns of the users table. Most users + don't actually change their preferences from the defaults, so only saving + the non-default settings will save a significant amount of space. Bloating + the users table with information that is only ever accessed by the user + itself is also a bad idea - this table is used in a lot of joins and can be + browsed on with the user list. + + +Concrete ideas: +- (done) + User preferences can be stored in a separate table: + -- incomplete list of preference keys + CREATE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'show_nsfw', + 'hide_list', 'notify_nodbedit', 'notify_announce'); + CREATE TABLE users_prefs ( + uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE, + key prefs_key NOT NULL, + value varchar NOT NULL, + PRIMARY KEY(uid, key) + ); + This doesn't store the data in a properly normalized fashion, but is likely + easier to work with anyway. +- (done) + Accessing the prefs table from Perl: + - authCheck() loads all of the users' preferences in a hash + - authPref($key) returns the value of the preference (from the hash) + - authPref($key, $val) sets the preference (in hash and DB) +- (done) + Keep the interface language setting as-is for anonymous visitors. + For logged-in users: + - Store the users' preferred language in the database instead of cookie. + - Contrary to the cookie: do not automatically remove the db preference + even if it's the same as what the browser requests. This is to ensure + that a user gets the same language even when switching PCs. + - When a user logs in and the l10n cookie is set, copy its value into the + DB and remove the cookie. + - Similar with logging out: copy l10n setting to cookie (but keep the DB) + "What language to use" checking order: database, cookie, browser +- (done - except some JS'ed preferences) + All other preferences can be moved to the users_prefs table. It is a lot of + work to correctly save and handle all preferences for anonymous visitors, + so let's stick with logged-in users for now to keep things simple. +- (done - at least the abstraction) + Some preferences need to be read and modified in Javascript. + Reading: + Add global JS variable using inline <script> to the bottom of the page, + before loading the global JS file, and store the required preferences in + there for the JS code to read. + Since some preferences are specific to some pages, add an option to + htmlFooter() to indicate which preferences need to be added. + Writing: + AJAX call to some .xml page. This will kind-of force the input method to + be explicit, since with AJAX you need some kind UI interaction to + indicate when the save is successful. Implicit saving is an especially + bad idea with this approach since that might make a lot of AJAX calls. +- Make implicit preference saving explicit: + - On producer pages, add a link 'Save as default' to the left of the + expand/collapse link when the user is logged in AND the current view is + different from the default. + - On VN pages: same for the spoiler level + - On Tag pages: same for spoiler level + - On VN filter settings: same for spoiler level + I'm not sure I like this idea... unless I can figure out a good abstraction + to nicely add those links with a single line of code. +- Remove "Don't hide NSFW" checkbox from profile page and add similar "Save + as default" links to the VN page. Close to the "show/hide NSFW" at the + screenshots and "Flagged as NSFW" note at the VN image. +- Add a "settings" icon to the user menu title box thing, and have it show a + CSS'ed window when clicked with settings for the skin and custom CSS. + Optionally with Javascripted previewing of the settings. + diff --git a/data/notes/sponsored-links b/data/notes/sponsored-links new file mode 100644 index 00000000..c31001d2 --- /dev/null +++ b/data/notes/sponsored-links @@ -0,0 +1,108 @@ +Advertisements + +Last modified: 2011-01 +Status: Long-term plans / nothing implemented yet + + +Idea: (semi-)large "Buy now" / "Download now" button on VN pages, linking +either to the product on a webshop or displaying a dropdown list with +available releases with links to webshops. + +A link to a webshop only appears if it has at least one release of the VN on +their site, and the link always points directly to the product page, not to +the search function or the homepage. + +A webshop link is internally linked to a release in the database, so we have +all kinds of information including whether it's a download or package, and in +what language it is. + +Preferably, the link also indicates the price and whether it is in stock. + + +Possible parties interested in advertising: +- J-List + Has an affiliate system that includes direct links + doesn't store JAN/UPC/catalog numbers +- Play-asia + Has an affiliate system that includes direct links + stores JAN, UPC, and catalog numbers +- Himeyashop / Erogeshop + Has no affiliate system but has shown interest in link exchanges in the past + Does not store JAN/UPC/catalog numbers + "Temporarily" closed, so probably not a good time to ask for ads? +- DLSite English + Seems to have an affiliate system, haven't really looked at it yet + Most releases don't even have a JAN code or catalog number +- MangaGamer + Rather specific "shop", but could count as one. + Has no affiliate system, but is planning to add one, as announced in + http://mangagamer.wordpress.com/2010/12/31/holidays-passing/ + Releases don't have catalog numbers or EAN codes +- Eroge-Europe.com + Seems to have an affiliate system, haven't really looked at it yet + Does not store JAN/UPC/catalog numbers +- PaletWeb + Has no affiliate system + Does have JAN codes for a few titles, but inconsistent +- CDJapan + Doesn't have that many VNs from what I've browsed, but still several + Has an affiliate system (seems to include direct links) + Has catalog numbers for most (all?) releases +- Hendane! + Does not seem to have many VNs (3 or 4?) + Has no affiliate system + Does not have JAN or catalog numbers + + +So who is going to update all those links? +Three possibilities: + +1. Automatically + By matching JAN/EAN/UPC or catalog numbers from our database with the + information on the webshop, and fetching the information necessary for the + links. + Since Play-asia is the only one storing that kind of information, this + will be rather specific. We can't really expect all other parties to + update their system, and for DLSite and MangaGamer it would involve + creating (official) catalog numbers for each entry - which would be easy + for MG, but certainly not for DLSite. + Even if a shop stores it, we'd need fast and up-to-date access to it. We + have several thousand JAN codes in the database. If we want to make sure + our information is accurate and up-to-date we'd have to check for the + availability of each release each day. Doing this will most likely require + the other party to update their site with an API providing this + information. I somehow doubt they would... + +2. Let the advertiser add and update the info + Add an admin interface to the site allowing advertisers to add links to + their shop to release entries - also allowing them to indicate the price + and stock availability. + Since advertisers benefit from these links, we can assume that, if they + agree to do this, they will keep the info up-to-date. + However, for some reason I don't think many advertisers would want to + invest that much time in advertising on a single site. + Instead of the advertiser itself, it would also be possible to look for a + dedicated user to do this for them. Though somehow I doubt we'd find + someone like that, and I don't feel like doing that myself. + +3. Let our users add and update the info + Add webshop links to release entries. Since the price and stock + availability tend to change over time and our dear users are either pretty + slow on the uptake or too lazy to update VNDB, we can forget about any + other information besides the links. :-( + It might, however, be possible to automatically fetch the price and stock + information anyway since we have the URLs, but in that case the webshop + should either allow us to crawl quite a lot or provide an alternative + method. + Since the list of webshops we link to is not a static one - shops can be + added or removed after a while - we can expect these links to be edited + quite often, which could make a mess with the edit histories. + Alternatively, we could do it VGMdb-like: allow users to simply manage + links where the release is sold, regardless of whether they are + advertising on VNDB or not. This would still make it possible to + special-case advertisers and give them special treatment or fetch + additional information. + +I would greatly prefer option #1, but since that's not very practical option +#3 (the VGMdb-like solution) is probably the best. + diff --git a/data/notes/tagmod-overrule b/data/notes/tagmod-overrule new file mode 100644 index 00000000..6209b79f --- /dev/null +++ b/data/notes/tagmod-overrule @@ -0,0 +1,55 @@ +Allow moderators to overrule a VN tag score + +Last modified: 2011-01-03 +Status: Implemented + + +SQL implementation #1: + Extra column to tags_vn: + ALTER TABLE tags_vn ADD COLUMN overrule boolean NOT NULL DEFAULT false; + There can only be one row in tags_vn with the same (tag, vid) combination + when one is set with overrule = true; this row then automatically indicates + the final score and spoiler setting. + - Pro: This way none of the final score calculating functions will have to be + modified, and this won't incur an extra performance penalty. + - Con: the votes of all other users for that tag and VN will have to be + removed. This makes overruling a VN a non-reversible operation. + - Determining whether a score was forced by a mod: bool_or(tv.overwrite) + - Regular voting on an overruled tag is simply not allowed + - An other mod should be able to remove the overruled vote and replace it + +SQL implementation #2: + Extra column to tags_vn: + ALTER TABLE tags_vn ADD COLUMN ignore boolean NOT NULL DEFAULT false; + Any tag vote with the ignore flag set is ignored in the score calculation. + When a moderator "overrules" a score, all votes with that (tag, vid) will + have ignore=true, except the mods own vote. + - Pro: Far more flexible than #1, can be used to ignore individual votes. + However, using it for anything other than overruling will make it very + hard or even impossible to reliably implement the overruling feature, so + we'll have avoid making use of this flexibility. + - Pro: Votes of other users don't have to be removed + - Pro: Users can still add votes to the tag (although it will be ignored) + - Con: Requires special coding to automatically set new votes on ignore + - Con: Requires modifying score calculation functions, possibly slower + - Determining whether a score was forced by a mod: bool_or(tv.ignore) + (Assumes we don't use the added flexibility) + +Let's go with #2. Will be slightly more work; but at least it's less prone to +irriversible moderation mistakes and more "friendly" to taggers. + + +UI changes: + Add extra 'overrule' checkbox to the 'you' column for moderators. + - Checking this will take over the mods' tagvote and spoiler level and + ignore the votes of all others. + - Unchecking it will de-overrule the score + - When an overruled vote is removed by the mod (setting '-' as vote), the + tag is de-overruled again. + + Add "overruled" indication to "others" column + - A red "!" next to the score column would work + - Simply indicates whether the score has been overruled by a mod + + Add "ignored" / "not counted" indication to tag link browser + diff --git a/data/script.js b/data/script.js index 0cadd300..45c42162 100644 --- a/data/script.js +++ b/data/script.js @@ -474,7 +474,7 @@ function tvsInit() { var l = byName(byId('tagops'), 'a'); for(var i=0;i<l.length; i++) l[i].onclick = tvsClick; - tvsSet(getCookie('tagspoil'), true); + tvsSet(getCookie('tagspoil'), true, (getCookie('tagcat')||'cont,tech').split(',')); } function tvsClick() { @@ -482,29 +482,43 @@ function tvsClick() { var l = byName(byId('tagops'), 'a'); for(var i=0; i<l.length; i++) if(l[i] == this) { - if(i < 3) { - tvsSet(i, null); - setCookie('tagspoil', i); - } else - tvsSet(null, i == 3 ? true : false); + if(i < 3) { /* categories */ + setClass(l[i], 'tsel', !hasClass(l[i], 'tsel')); + var c = tvsSet(); + setCookie('tagcat', c.length ? c.join(',') : '-'); + } else if(i < 6) { /* spoiler level */ + tvsSet(i-3, null); + setCookie('tagspoil', i-3); + } else /* limit */ + tvsSet(null, i == 6 ? true : false); } return false; } -function tvsSet(lvl, lim) { +function tvsSet(lvl, lim, cats) { /* set/get level and limit to/from the links */ var l = byName(byId('tagops'), 'a'); + var cat = cats || []; for(var i=0; i<l.length; i++) { - if(i < 3) { /* spoiler level */ + if(i < 3) { /* categories */ + var c = l[i].href.substr(l[i].href.indexOf('#')+1); + if(cats) { + for(var j=0; j<cats.length && c != cats[j]; j++) ; + setClass(l[i], 'tsel', j != cats.length); + } else { + if(hasClass(l[i], 'tsel')) + cat.push(c); + } + } else if(i < 6) { /* spoiler level */ if(lvl != null) - setClass(l[i], 'tsel', i == lvl); + setClass(l[i], 'tsel', i-3 == lvl); if(lvl == null && hasClass(l[i], 'tsel')) - lvl = i; - } else { /* display limit (3 = summary) */ + lvl = i-3; + } else { /* display limit (6 = summary) */ if(lim != null) - setClass(l[i], 'tsel', lim == (i == 3)); + setClass(l[i], 'tsel', lim == (i == 6)); if(lim == null && hasClass(l[i], 'tsel')) - lim = i == 3; + lim = i == 6; } } @@ -514,13 +528,14 @@ function tvsSet(lvl, lim) { var s=0; for(i=0;i<l.length;i++) { var thislvl = l[i].className.substr(6, 1); - if(thislvl <= lvl && s < lim) { + for(var j=0; j<cat.length && !hasClass(l[i], 'cat_'+cat[j]); j++) ; + if(thislvl <= lvl && s < lim && j != cat.length) { setClass(l[i], 'hidden', false); s++; } else setClass(l[i], 'hidden', true); } - return false; + return cat; } tvsInit(); @@ -1257,6 +1272,8 @@ function tglLoad() { tglStripe(); var trs = byName(byId('tagtable'), 'tr'); for(var i=0; i<trs.length; i++) { + if(hasClass(trs[i], 'tagmod_cat')) + continue; var vote = byClass(trs[i], 'td', 'tc_myvote')[0]; vote.tgl_vote = getText(vote)*1; tglVoteBar(vote); @@ -1353,6 +1370,10 @@ function tglAdd() { if(byId('tgl_'+id)) return alert(mt('_tagv_double')); + if(!byId('tagmod_newtags')) + byId('tagtable').appendChild(tag('tr', {'class':'tagmod_cat', id:'tagmod_newtags'}, + tag('td', {colspan:7}, mt('_tagv_newlyadded')))); + var vote = tag('td', {'class':'tc_myvote', tgl_vote: 2}, ''); tglVoteBar(vote); var spoil = tag('td', {'class':'tc_myspoil', tgl_spoil: 0}, tglSpoilers[0]); @@ -1385,6 +1406,8 @@ function tglSerialize() { var r = []; var l = byName(byId('tagtable'), 'tr'); for(var i=0; i<l.length; i++) { + if(hasClass(l[i], 'tagmod_cat')) + continue; var vote = byClass(l[i], 'td', 'tc_myvote')[0].tgl_vote; if(vote != 0) r[r.length] = [ @@ -1767,14 +1790,19 @@ function filLoad() { var p = tag('p', {'class':'browseopts'}); var c = tag('div', null); + var idx = 0; for(var i=1; i<l.length; i++) { + if(!l[i]) + continue; + idx++; + // category link - var a = tag('a', { href: '#', onclick: filSelectCat, fil_num: i, fil_onshow:[] }, l[i][0]); + var a = tag('a', { href: '#', onclick: filSelectCat, fil_num: idx, fil_onshow:[] }, l[i][0]); p.appendChild(a); p.appendChild(tag(' ')); // category contents - var t = tag('table', {'class':'formtable', fil_num: i}, null); + var t = tag('table', {'class':'formtable', fil_num: idx}, null); setClass(t, 'hidden', true); a.fil_t = t; for(var j=1; j<l[i].length; j++) { @@ -1795,7 +1823,7 @@ function filLoad() { } c.appendChild(t); - fil_cats[i] = a; + fil_cats[idx] = a; } addBody(tag('div', { id: 'fil_div', 'class':'hidden' }, @@ -1921,6 +1949,8 @@ function filDeSerialize() { f[fn] = ''; for(var fn in f) { var c = byId('fil_check_'+fn); + if(!c) + continue; c.checked = f[fn] == '' ? false : true; var v = f[fn].split('~'); for(var i=0; i<v.length; i++) @@ -2155,13 +2185,17 @@ function filVN() { for(var i=0; i<len.length; i++) // l10n /_vnlength_.+/ len[i] = [ len[i], mt('_vnlength_'+len[i]) ]; + var ontagpage = location.pathname.indexOf('/v/') < 0; + return [ mt('_vnbrowse_fil_title'), [ mt('_vnbrowse_general'), filFSelect( 'length', mt('_vnbrowse_length'), 6, len), filFOptions('hasani', mt('_vnbrowse_anime'), [[1, mt('_vnbrowse_anime_yes')],[0, mt('_vnbrowse_anime_no')]]) ], - [ mt('_vnbrowse_tags'), + ontagpage ? [ mt('_vnbrowse_tags'), + [ '', ' ', tag(mt('_vnbrowse_tagnothere')) ], + ] : [ mt('_vnbrowse_tags'), [ '', ' ', tag(mt('_js_fil_booland')) ], [ '', ' ', PREF_CODE != '' ? tag(mt('_vnbrowse_tagactive')) : null ], filFTagInput('tag_inc', mt('_vnbrowse_taginc')), @@ -2171,7 +2205,14 @@ function filVN() { ], [ mt('_vnbrowse_language'), filFSelect('lang', mt('_vnbrowse_language'), 20, lang) ], [ mt('_vnbrowse_olang'), filFSelect('olang',mt('_vnbrowse_olang'), 20, lang) ], - [ mt('_vnbrowse_platform'), filFSelect('plat', mt('_vnbrowse_platform'), 20, plat) ] + [ mt('_vnbrowse_platform'), filFSelect('plat', mt('_vnbrowse_platform'), 20, plat) ], + PREF_CODE == '' ? null : [ + mt('_vnbrowse_ul'), + filFOptions('ul_notblack', mt('_vnbrowse_ul_notblack'), [[1, mt('_vnbrowse_ul_notblackmsg')]]), + filFOptions('ul_onwish', mt('_vnbrowse_ul_onwish'), [[0, mt('_vnbrowse_ul_onwishno')],[1, mt('_vnbrowse_ul_onwishyes')]]), + filFOptions('ul_voted', mt('_vnbrowse_ul_voted'), [[0, mt('_vnbrowse_ul_votedno')], [1, mt('_vnbrowse_ul_votedyes') ]]), + filFOptions('ul_onlist', mt('_vnbrowse_ul_onlist'), [[0, mt('_vnbrowse_ul_onlistno')],[1, mt('_vnbrowse_ul_onlistyes')]]) + ], ]; } diff --git a/data/style.css b/data/style.css index f64596c0..f5445c2a 100644 --- a/data/style.css +++ b/data/style.css @@ -964,12 +964,14 @@ table.tgl tfoot td { padding-top: 20px!important; } table.tgl .tc_you { border-right: 1px solid $border$; border-left: 1px solid $border$; width: 150px; text-align: center } table.tgl .tc_others { border-left: 1px solid $border$; width: 150px; text-align: center } table.tgl .tc_tagname { min-width: 200px; border-right: 1px solid $border$ } +table.tgl tbody .tc_tagname { padding-left: 15px!important } table.tgl .tc_myvote { padding-left: 30px!important } table.tgl .tc_myover { padding: 0!important } table.tgl .tc_myspoil { border-right: 1px solid $border$; padding-right: 30px!important; text-align: right; padding-left: 10px!important; cursor: pointer } table.tgl .tc_allvote { padding-left: 30px!important; } table.tgl .tc_allvote i { font-style: normal; font-size: 8px } table.tgl .tc_allspoil { text-align: right; padding-right: 15px!important; } +table.tgl .tagmod_cat td { font-weight: bold } .taglvl { display: block; float: left; width: 8px; height: 12px; border: 1px solid $border$; font-size: 1px; color: $maintext$!important } .taglvl0 { width: 15px; border: none!important; font-size: 10px; text-align: center; } div.taglvl0 { font-size: 8px; width: 20px!important } diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index b7792eb8..8ed4cec6 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -39,7 +39,7 @@ sub dbTagGet { 't.meta = ?' => $o{meta}?1:0 ) : (), ); my @select = ( - qw|t.id t.meta t.name t.description t.state t.c_vns|, + qw|t.id t.meta t.name t.description t.state t.cat t.c_vns|, q|extract('epoch' from t.added) as added|, $o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (), ); @@ -122,13 +122,17 @@ sub dbTagEdit { $self->dbExec('UPDATE tags !H WHERE id = ?', { $o{upddate} ? ('added = NOW()' => 1) : (), - map { +"$_ = ?" => $o{$_} } qw|name meta description state| + map exists($o{$_}) ? ("$_ = ?" => $o{$_}) : (), qw|name meta description state cat| }, $id); - $self->dbExec('DELETE FROM tags_aliases WHERE tag = ?', $id); - $self->dbExec('INSERT INTO tags_aliases (tag, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}}); - $self->dbExec('DELETE FROM tags_parents WHERE tag = ?', $id); - $self->dbExec('INSERT INTO tags_parents (tag, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}}); - $self->dbExec('DELETE FROM tags_vn WHERE tag = ?', $id) if $o{meta} || $o{state} == 1; + if($o{aliases}) { + $self->dbExec('DELETE FROM tags_aliases WHERE tag = ?', $id); + $self->dbExec('INSERT INTO tags_aliases (tag, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}}); + } + if($o{parents}) { + $self->dbExec('DELETE FROM tags_parents WHERE tag = ?', $id); + $self->dbExec('INSERT INTO tags_parents (tag, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}}); + } + $self->dbExec('DELETE FROM tags_vn WHERE tag = ?', $id) if $o{meta} || ($o{state} && $o{state} == 1); } @@ -136,8 +140,8 @@ sub dbTagEdit { # returns the id of the new tag sub dbTagAdd { my($self, %o) = @_; - my $id = $self->dbRow('INSERT INTO tags (name, meta, description, state, addedby) VALUES (!l, ?) RETURNING id', - [ map $o{$_}, qw|name meta description state| ], $o{addedby}||$self->authInfo->{id} + my $id = $self->dbRow('INSERT INTO tags (name, meta, description, state, cat, addedby) VALUES (!l, ?) RETURNING id', + [ map $o{$_}, qw|name meta description state cat| ], $o{addedby}||$self->authInfo->{id} )->{id}; $self->dbExec('INSERT INTO tags_parents (tag, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}}); $self->dbExec('INSERT INTO tags_aliases (tag, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}}); @@ -245,13 +249,13 @@ sub dbTagStats { }->{ $o{sort}||'name' }, $o{reverse} ? 'DESC' : 'ASC'; my($r, $np) = $self->dbPage(\%o, qq| - SELECT t.id, t.name, count(*) as cnt, $rating as rating, + SELECT t.id, t.name, t.cat, count(*) as cnt, $rating as rating, COALESCE(avg(CASE WHEN tv.ignore THEN NULL ELSE tv.spoiler END), 0) as spoiler, bool_or(tv.ignore) AS overruled FROM tags t JOIN tags_vn tv ON tv.tag = t.id WHERE tv.vid = ? - GROUP BY t.id, t.name + GROUP BY t.id, t.name, t.cat !s ORDER BY !s|, $o{vid}, defined $o{minrating} ? "HAVING $rating > $o{minrating}" : '', $order diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index b506d94d..0f6c5731 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -11,7 +11,7 @@ our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreen # Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil, -# hasani, hasshot, results, page, what, sort, reverse +# hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, reverse # What: extended anime relations screenshots relgraph rating ranking changes # Sort: id rel pop rating title tagscore rand sub dbVNGet { @@ -27,6 +27,8 @@ sub dbVNGet { grep !defined($_) || $_!~/^\d+$/, $o{tagspoil}, !$o{tag_inc} ? () : (ref($o{tag_inc}) ? @{$o{tag_inc}} : $o{tag_inc}); + my $uid = $self->authInfo->{id}; + my @where = ( $o{id} ? ( 'v.id = ?' => $o{id} ) : (), @@ -55,6 +57,14 @@ sub dbVNGet { 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ ref $o{tag_exc} ? $o{tag_exc} : [$o{tag_exc}] ] ) : (), $o{search} ? ( map +('v.c_search like ?', "%$_%"), normalize_query($o{search})) : (), + $uid && $o{ul_notblack} ? ( + 'v.id NOT IN(SELECT vid FROM wlists WHERE uid = ? AND wstat = 3)' => $uid ) : (), + $uid && defined $o{ul_onwish} ? ( + 'v.id !s IN(SELECT vid FROM wlists WHERE uid = ?)' => [ $o{ul_onwish} ? '' : 'NOT', $uid ] ) : (), + $uid && defined $o{ul_voted} ? ( + 'v.id !s IN(SELECT vid FROM votes WHERE uid = ?)' => [ $o{ul_voted} ? '' : 'NOT', $uid ] ) : (), + $uid && defined $o{ul_onlist} ? ( + 'v.id !s IN(SELECT vid FROM vnlists WHERE uid = ?)' => [ $o{ul_onlist} ? '' : 'NOT', $uid ] ) : (), # don't fetch hidden items unless we ask for an ID !$o{id} && !$o{rev} ? ( 'v.hidden = FALSE' => 0 ) : (), diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm index 43137506..cff5d980 100644 --- a/lib/VNDB/Handler/Tags.pm +++ b/lib/VNDB/Handler/Tags.pm @@ -34,12 +34,14 @@ sub tagpage { { get => 'o', required => 0, default => 'd', enum => [ 'a','d' ] }, { get => 'p', required => 0, default => 1, template => 'int' }, { get => 'm', required => 0, default => -1, enum => [qw|0 1 2|] }, + { get => 'fil', required => 0 }, ); return $self->resNotFound if $f->{_err}; my $tagspoil = $self->reqCookie('tagspoil')||''; $f->{m} = $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0 if $f->{m} == -1; + $f->{fil} //= $self->authPref('filter_vn'); - my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->filFetchDB(vn => undef, undef, { + my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->filFetchDB(vn => $f->{fil}, undef, { what => 'rating', results => 50, page => $f->{p}, @@ -94,6 +96,11 @@ sub tagpage { lit bb2html $t->{description}; end; } + p class => 'center'; + b mt('_tagp_cat'); + br; + txt mt("_tagcat_$t->{cat}"); + end; if(@{$t->{aliases}}) { p class => 'center'; b mt('_tagp_aliases'); @@ -106,23 +113,32 @@ sub tagpage { _childtags($self, $t) if @{$t->{childs}}; if(!$t->{meta} && $t->{state} == 2) { + form action => "/g$t->{id}", 'accept-charset' => 'UTF-8', method => 'get'; div class => 'mainbox'; a class => 'addnew', href => "/g/links?t=$tag", mt '_tagp_rawvotes'; h1 mt '_tagp_vnlist'; + p class => 'browseopts'; - a href => "/g$t->{id}?m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", mt '_tagp_spoil0'; - a href => "/g$t->{id}?m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", mt '_tagp_spoil1'; - a href => "/g$t->{id}?m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", mt '_tagp_spoil2'; + a href => "/g$t->{id}?fil=$f->{fil};m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", mt '_tagp_spoil0'; + a href => "/g$t->{id}?fil=$f->{fil};m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", mt '_tagp_spoil1'; + a href => "/g$t->{id}?fil=$f->{fil};m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", mt '_tagp_spoil2'; + end; + + a id => 'filselect', href => '#v'; + lit '<i>▸</i> '.mt('_js_fil_filters').'<i></i>'; end; + input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil}; + if(!@$list) { p; br; br; txt mt '_tagp_novn'; end; } p; br; txt mt '_tagp_cached'; end; end 'div'; - $self->htmlBrowseVN($list, $f, $np, "/g$t->{id}?m=$f->{m}", 1) if @$list; + end 'form'; + $self->htmlBrowseVN($list, $f, $np, "/g$t->{id}?fil=$f->{fil};m=$f->{m}", 1) if @$list; } - $self->htmlFooter; + $self->htmlFooter(prefs => ['filter_vn']); } @@ -198,6 +214,8 @@ sub tagedit { $frm = $self->formValidate( { post => 'name', required => 1, maxlength => 250, regex => [ qr/^[^,]+$/, 'A comma is not allowed in tag names' ] }, { post => 'state', required => 0, default => 0, enum => [ 0..2 ] }, + { post => 'cat', required => 1, enum => $self->{tag_categories} }, + { post => 'catrec', required => 0 }, { post => 'meta', required => 0, default => 0 }, { post => 'alias', required => 0, maxlength => 1024, default => '', regex => [ qr/^[^,]+$/s, 'No comma allowed in aliases' ] }, { post => 'description', required => 0, maxlength => 10240, default => '' }, @@ -220,11 +238,13 @@ sub tagedit { $_ = $c->[0]{id}; } } + if(!$frm->{_err}) { $frm->{state} = $frm->{meta} = 0 if !$self->authCan('tagmod'); my %opts = ( name => $frm->{name}, state => $frm->{state}, + cat => $frm->{cat}, description => $frm->{description}, meta => $frm->{meta}?1:0, aliases => \@aliases, @@ -234,6 +254,7 @@ sub tagedit { $tag = $self->dbTagAdd(%opts); } else { $self->dbTagEdit($tag, %opts, upddate => $frm->{state} == 2 && $t->{state} != 2); + _set_childs_cat($self, $tag, $frm->{cat}) if $frm->{catrec}; } $self->dbTagMerge($tag, @merge) if $self->authCan('tagmod') && @merge; $self->resRedirect("/g$tag", 'post'); @@ -242,7 +263,7 @@ sub tagedit { } if($tag) { - $frm->{$_} ||= $t->{$_} for (qw|name meta description state|); + $frm->{$_} ||= $t->{$_} for (qw|name meta description state cat|); $frm->{alias} ||= join "\n", @{$t->{aliases}}; $frm->{parents} ||= join ', ', map $_->{name}, @{$t->{parents}}; } @@ -274,6 +295,12 @@ sub tagedit { $tag ? [ static => content => mt '_tagedit_frm_meta_warn' ] : (), ) : (), + [ select => short => 'cat', name => mt('_tagedit_frm_cat'), options => [ + map [$_, mt "_tagcat_$_"], @{$self->{tag_categories}} ] ], + $self->authCan('tagmod') && $tag ? ( + [ checkbox => short => 'catrec', name => mt '_tagedit_frm_catrec' ], + [ static => content => mt '_tagedit_frm_catrec_warn' ], + ) : (), [ textarea => short => 'alias', name => mt('_tagedit_frm_alias'), cols => 30, rows => 4 ], [ textarea => short => 'description', name => mt '_tagedit_frm_desc' ], [ static => content => mt '_tagedit_frm_desc_msg' ], @@ -288,6 +315,27 @@ sub tagedit { $self->htmlFooter; } +# recursively edit all child tags and set the category field +# Note: this can be done more efficiently by doing everything in one UPDATE +# query, but that takes more code and this feature isn't used very often +# anyway. +sub _set_childs_cat { + my($self, $tag, $cat) = @_; + my %done; + + my $e; + $e = sub { + my $l = shift; + for (@$l) { + $self->dbTagEdit($_->{id}, cat => $cat) if !$done{$_->{id}}++; + $e->($_->{sub}) if $_->{sub}; + } + }; + + my $childs = $self->dbTagTree($tag, 25); + $e->($childs); +} + sub taglist { my $self = shift; @@ -579,30 +627,7 @@ sub vntagmod { end; end; end 'tfoot'; tbody id => 'tagtable'; - for my $t (sort { $a->{name} cmp $b->{name} } @$tags) { - my $m = (grep $_->{tag} == $t->{id}, @$my)[0] || {}; - Tr id => "tgl_$t->{id}"; - td class => 'tc_tagname'; a href => "/g$t->{id}", $t->{name}; end; - td class => 'tc_myvote', $m->{vote}||0; - if($self->authCan('tagmod')) { - td class => 'tc_myover'; - input type => 'checkbox', name => 'overrule', value => $t->{id}, - $m->{vote} && !$m->{ignore} && $t->{overruled} ? (checked => 'checked') : () - if $t->{cnt} > 1; - end; - } - td class => 'tc_myspoil', defined $m->{spoiler} ? $m->{spoiler} : -1; - td class => 'tc_allvote'; - tagscore $t->{rating}; - i $t->{overruled} ? (class => 'grayedout') : (), " ($t->{cnt})"; - b class => 'standout', style => 'font-weight: bold', ' !' if $t->{overruled}; - end; - td class => 'tc_allspoil', sprintf '%.2f', $t->{spoiler}; - td class => 'tc_allwho'; - a href => "/g/links?v=$vid;t=$t->{id}", mt '_tagv_who'; - end; - end; - } + _tagmod_list($self, $vid, $tags, $my); end 'tbody'; end 'table'; } ], @@ -610,6 +635,44 @@ sub vntagmod { $self->htmlFooter; } +sub _tagmod_list { + my($self, $vid, $tags, $my) = @_; + + my %my = map +($_->{tag} => $_), @$my; + + for my $cat (@{$self->{tag_categories}}) { + my @tags = grep $_->{cat} eq $cat, @$tags; + next if !@tags; + Tr class => 'tagmod_cat'; + td colspan => 7, mt "_tagcat_$cat"; + end; + for my $t (@tags) { + my $m = $my{$t->{id}}; + Tr id => "tgl_$t->{id}"; + td class => 'tc_tagname'; a href => "/g$t->{id}", $t->{name}; end; + td class => 'tc_myvote', $m->{vote}||0; + if($self->authCan('tagmod')) { + td class => 'tc_myover'; + input type => 'checkbox', name => 'overrule', value => $t->{id}, + $m->{vote} && !$m->{ignore} && $t->{overruled} ? (checked => 'checked') : () + if $t->{cnt} > 1; + end; + } + td class => 'tc_myspoil', defined $m->{spoiler} ? $m->{spoiler} : -1; + td class => 'tc_allvote'; + tagscore $t->{rating}; + i $t->{overruled} ? (class => 'grayedout') : (), " ($t->{cnt})"; + b class => 'standout', style => 'font-weight: bold', title => mt('_tagv_overruletip'), ' !' if $t->{overruled}; + end; + td class => 'tc_allspoil', sprintf '%.2f', $t->{spoiler}; + td class => 'tc_allwho'; + a href => "/g/links?v=$vid;t=$t->{id}", mt '_tagv_who'; + end; + end; + } + } +} + sub tagindex { my $self = shift; diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index ee121c9b..aa3c0538 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -156,7 +156,10 @@ sub page { if(@$t) { div id => 'tagops'; # NOTE: order of these links is hardcoded in JS - a href => '#', class => 'tsel', mt '_vnpage_tags_spoil0'; + a href => '#cont', lc mt '_tagcat_cont'; + a href => '#ero', lc mt '_tagcat_ero'; + a href => '#tech', lc mt '_tagcat_tech'; + a href => '#', class => 'sec tsel', mt '_vnpage_tags_spoil0'; a href => '#', mt '_vnpage_tags_spoil1'; a href => '#', mt '_vnpage_tags_spoil2'; a href => '#', class => 'sec', mt '_vnpage_tags_summary'; @@ -164,7 +167,7 @@ sub page { end; div id => 'vntags'; for (@$t) { - span class => sprintf 'tagspl%.0f %s', $_->{spoiler}, $_->{spoiler} > 0 ? 'hidden' : ''; + span class => sprintf 'tagspl%.0f cat_%s %s', $_->{spoiler}, $_->{cat}, $_->{spoiler} > 0 ? 'hidden' : ''; a href => "/g$_->{id}", style => sprintf('font-size: %dpx', $_->{rating}*3.5+6), $_->{name}; b class => 'grayedout', sprintf ' %.1f', $_->{rating}; end; diff --git a/lib/VNDB/Util/Misc.pm b/lib/VNDB/Util/Misc.pm index 9f281ee7..5406fe06 100644 --- a/lib/VNDB/Util/Misc.pm +++ b/lib/VNDB/Util/Misc.pm @@ -11,7 +11,7 @@ our @EXPORT = qw|filFetchDB ieCheck|; my %filfields = ( - vn => [qw|length hasani tag_inc tag_exc taginc tagexc tagspoil lang olang plat|], + vn => [qw|length hasani tag_inc tag_exc taginc tagexc tagspoil lang olang plat ul_notblack ul_onwish ul_voted ul_onlist|], release => [qw|type patch freeware doujin date_before date_after released minage lang olang resolution plat med voiced ani_story ani_ero|], ); @@ -34,9 +34,6 @@ sub filFetchDB { my $prefname = 'filter_'.$type; my $pref = $self->authPref($prefname); - # simply call the DB if we're not applying filters - return $dbfunc->($self, %$pre, %$post) if !$pref && !$overwrite; - my $filters = fil_parse $overwrite // $pref, @{$filfields{$type}}; # compatibility @@ -50,7 +47,7 @@ sub filFetchDB { exists($pre->{$_}) ? ($_ => $pre->{$_}) : (), ), @{$filfields{$type}}}) if defined $overwrite; - return $dbfunc->($self, %$pre, %$filters, %$post) if defined $overwrite; + return $dbfunc->($self, %$pre, %$filters, %$post) if defined $overwrite or !keys %$filters;; # since incorrect filters can throw a database error, we have to special-case # filters that originate from a preference setting, so that in case these are diff --git a/util/sql/all.sql b/util/sql/all.sql index 09a6d214..0a6d1037 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -13,6 +13,7 @@ CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't'); CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce'); CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori'); CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); +CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); diff --git a/util/sql/func.sql b/util/sql/func.sql index 2f87bdb9..d7b5ab2f 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -763,7 +763,8 @@ BEGIN JOIN ( SELECT id, title FROM vn_rev WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id UNION SELECT id, title FROM releases_rev WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id - ) x ON c.id = x.id; + ) x ON c.id = x.id + WHERE c.requester <> u.uid; RETURN NULL; END; $$ LANGUAGE plpgsql; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 2a3a394a..dd2b2ffd 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -193,7 +193,8 @@ CREATE TABLE tags ( added timestamptz NOT NULL DEFAULT NOW(), state smallint NOT NULL DEFAULT 0, c_vns integer NOT NULL DEFAULT 0, - addedby integer NOT NULL DEFAULT 0 + addedby integer NOT NULL DEFAULT 0, + cat tag_category NOT NULL DEFAULT 'cont' ); -- tags_aliases diff --git a/util/updates/update_2.18.sql b/util/updates/update_2.18.sql new file mode 100644 index 00000000..90a5bcd3 --- /dev/null +++ b/util/updates/update_2.18.sql @@ -0,0 +1,8 @@ + +CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech'); + +ALTER TABLE tags ADD COLUMN cat tag_category NOT NULL DEFAULT 'cont'; + +-- load new function(s) +\i util/sql/func.sql + |