summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2011-02-08 12:43:44 +0100
committerYorhel <git@yorhel.nl>2011-02-08 12:44:08 +0100
commit5bdd59de9b33eac81fb418cc6ca354a662d98822 (patch)
tree193a7a1b532099420ba2892e8479cbba4ba2d7e6
parent449a8d9a29e1658a19f4b7a9fc630fe76409dd32 (diff)
parent5360e67f5808c397a1e931405d47c2f031a95a11 (diff)
Merge branch 'beta'2.18
+ ChangeLog update for 2.18
-rw-r--r--ChangeLog9
-rw-r--r--Makefile9
-rw-r--r--README5
-rw-r--r--data/global.pl1
-rw-r--r--data/lang.txt156
-rw-r--r--data/notes/atom-feeds26
-rw-r--r--data/notes/mylist-revamp86
-rw-r--r--data/notes/notifications20
-rw-r--r--data/notes/permanent-filters88
-rw-r--r--data/notes/preferences117
-rw-r--r--data/notes/sponsored-links108
-rw-r--r--data/notes/tagmod-overrule55
-rw-r--r--data/script.js81
-rw-r--r--data/style.css2
-rw-r--r--lib/VNDB/DB/Tags.pm26
-rw-r--r--lib/VNDB/DB/VN.pm12
-rw-r--r--lib/VNDB/Handler/Tags.pm125
-rw-r--r--lib/VNDB/Handler/VNPage.pm7
-rw-r--r--lib/VNDB/Util/Misc.pm7
-rw-r--r--util/sql/all.sql1
-rw-r--r--util/sql/func.sql3
-rw-r--r--util/sql/schema.sql3
-rw-r--r--util/updates/update_2.18.sql8
23 files changed, 880 insertions, 75 deletions
diff --git a/ChangeLog b/ChangeLog
index d51f3801..13065aaa 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -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
diff --git a/Makefile b/Makefile
index 3e62d98d..16246871 100644
--- a/Makefile
+++ b/Makefile
@@ -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)
diff --git a/README b/README
index 1155ee3f..fe7778cc 100644
--- a/README
+++ b/README
@@ -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>&#9656;</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
+