diff options
-rw-r--r-- | ChangeLog | 1 | ||||
-rw-r--r-- | data/lang.txt | 21 | ||||
-rw-r--r-- | data/style.css | 2 | ||||
-rw-r--r-- | lib/Multi/Maintenance.pm | 3 | ||||
-rw-r--r-- | lib/VNDB/Util/LayoutHTML.pm | 2 | ||||
-rw-r--r-- | util/sql/all.sql | 9 | ||||
-rw-r--r-- | util/sql/func.sql | 16 | ||||
-rw-r--r-- | util/updates/update_2.20.sql | 13 |
8 files changed, 62 insertions, 5 deletions
@@ -6,6 +6,7 @@ - Generate dbedit/dbdel notifications on character edits - CSS: Hide links in [spoiler] tags - Added 'select' all to wishlist and moved 'select all' down on notifies + - Added char/tag/trait stats to database statistics box - Bugfix: don't accidentally remove char traits when editing - Bugfix: fixed possible SQL table name clash on history browser - Bugfix: properly announce chars and traits in Multi::IRC diff --git a/data/lang.txt b/data/lang.txt index ed0033e7..bee15a28 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -1985,6 +1985,27 @@ cs : Producenti hu : Készítők nl : Producenten +:_menu_stat_chars +en : Characters +ru*: +cs*: +hu*: +nl : Karakters + +:_menu_stat_tags +en : VN Tags +ru*: +cs*: +hu*: +nl : + +:_menu_stat_traits +en : Character traits +ru*: +cs*: +hu*: +nl : Karakter kenmerken + :_menu_stat_users en : Users ru : Пользователей diff --git a/data/style.css b/data/style.css index cb936dbc..1381114c 100644 --- a/data/style.css +++ b/data/style.css @@ -289,7 +289,7 @@ b.future, b.standout, a.standout { #menulist dt { display: block; float: left; - width: 85px; + width: 93px; font-style: italic; } #menulist dd { diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 5b092d0a..44cb6552 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -216,6 +216,9 @@ sub statscache { q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn'|, q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases'|, q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers'|, + q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars'|, + q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE state = 2) WHERE section = 'tags'|, + q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits'|, q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads WHERE hidden = FALSE) WHERE section = 'threads'|, q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE)) WHERE section = 'threads_posts'| diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index 22052a75..5dd8038f 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -130,7 +130,7 @@ sub _menu { h2 mt '_menu_dbstats'; div; dl; - for (qw|vn releases producers users threads posts|) { + for (qw|vn releases producers chars tags traits users threads posts|) { dt mt "_menu_stat_$_"; dd $self->{stats}{$_}; } diff --git a/util/sql/all.sql b/util/sql/all.sql index a98fbf95..eac8d58a 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -47,6 +47,12 @@ CREATE TRIGGER stats_cache_new AFTER INSERT ON producers CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); @@ -126,6 +132,9 @@ INSERT INTO stats_cache (section, count) VALUES ('vn', 0), ('producers', 0), ('releases', 0), + ('chars', 0), + ('tags', 0), + ('traits', 0), ('threads', 0), ('threads_posts', 0); diff --git a/util/sql/func.sql b/util/sql/func.sql index b0dbfff2..47b4511d 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -490,6 +490,9 @@ $$ LANGUAGE 'plpgsql'; -- the stats_cache table CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ +DECLARE + unhidden boolean; + hidden boolean; BEGIN IF TG_OP = 'INSERT' THEN IF TG_TABLE_NAME = 'users' THEN @@ -504,13 +507,20 @@ BEGIN END IF; END IF; - ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN - IF OLD.hidden = TRUE THEN + ELSIF TG_OP = 'UPDATE' THEN + IF TG_TABLE_NAME IN('tags', 'traits') THEN + unhidden := OLD.state <> 2 AND NEW.state = 2; + hidden := OLD.state = 2 AND NEW.state <> 2; + ELSE + unhidden := OLD.hidden AND NOT NEW.hidden; + hidden := NOT unhidden; + END IF; + IF unhidden THEN IF TG_TABLE_NAME = 'threads' THEN UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts'; END IF; UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - ELSIF OLD.hidden = FALSE THEN + ELSIF hidden THEN IF TG_TABLE_NAME = 'threads' THEN UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts'; END IF; diff --git a/util/updates/update_2.20.sql b/util/updates/update_2.20.sql index 236161d2..28efd713 100644 --- a/util/updates/update_2.20.sql +++ b/util/updates/update_2.20.sql @@ -9,3 +9,16 @@ DROP TYPE tmp; CREATE TRIGGER notify_dbdel AFTER UPDATE ON chars FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); CREATE TRIGGER notify_dbedit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); + +INSERT INTO stats_cache VALUES + ('chars', (SELECT COUNT(*) FROM chars WHERE NOT hidden)), + ('tags', (SELECT COUNT(*) FROM tags WHERE state = 2)), + ('traits', (SELECT COUNT(*) FROM traits WHERE state = 2)); + +CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache(); + |