summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-06-26 12:49:55 +0200
committerYorhel <git@yorhel.nl>2019-06-26 12:49:55 +0200
commit5d98346cb7163557c55ff2c850a6362edb9772ba (patch)
treeed3bc27acdfda2e7191ae6c2f1b1948b7812c78a
parent4d67310a2cb148925b6f8ba96a4fade124e83854 (diff)
Traits: Split "meta" field into "searchable" and "applicable"
-rw-r--r--data/js/chartraits.js6
-rw-r--r--lib/Multi/APIDump.pm6
-rw-r--r--lib/VNDB/DB/Traits.pm11
-rw-r--r--lib/VNDB/Handler/Chars.pm6
-rw-r--r--lib/VNDB/Handler/Traits.pm31
-rw-r--r--util/sql/func.sql13
-rw-r--r--util/sql/schema.sql3
-rw-r--r--util/updates/update_20190626.sql7
8 files changed, 54 insertions, 29 deletions
diff --git a/data/js/chartraits.js b/data/js/chartraits.js
index 05f6e53d..968b68ee 100644
--- a/data/js/chartraits.js
+++ b/data/js/chartraits.js
@@ -26,7 +26,7 @@ function ctrLoad() {
tr.appendChild(tag('td', { style: 'text-align: right; padding-right: 5px'}, 'i'+item.getAttribute('id')));
tr.appendChild(tag('td',
tag('b', {'class':'grayedout'}, g), item.firstChild.nodeValue,
- tag('b', {'class':'grayedout'}, item.getAttribute('meta')=='yes' ? 'meta' : '')));
+ tag('b', {'class':'grayedout'}, item.getAttribute('applicable')=='no' ? 'not applicable' : '')));
}, ctrFormAdd);
}
@@ -67,8 +67,8 @@ function ctrFormAdd(item) {
break;
if(i < l.length)
alert('Selected trait is already present.');
- else if(item.getAttribute('meta') == 'yes')
- alert('Meta traits can\'t be used here.');
+ else if(item.getAttribute('applicable') == 'no')
+ alert('This trait can\'t be used here.');
else
ctrAdd(item, Math.floor(item.getAttribute('defaultspoil')));
return '';
diff --git a/lib/Multi/APIDump.pm b/lib/Multi/APIDump.pm
index 28e944ca..79458f8d 100644
--- a/lib/Multi/APIDump.pm
+++ b/lib/Multi/APIDump.pm
@@ -45,7 +45,7 @@ sub tags_gen {
sub traits_gen {
pg_cmd q|
- SELECT id, name, alias AS aliases, description, meta, c_items AS chars,
+ SELECT id, name, alias AS aliases, description, searchable, applicable, c_items AS chars,
(SELECT string_agg(parent::text, ',') FROM traits_parents WHERE trait = id) AS parents
FROM traits WHERE state = 2
|, undef, sub {
@@ -55,7 +55,9 @@ sub traits_gen {
my @res = $res->rowsAsHashes;
for(@res) {
$_->{id} *= 1;
- $_->{meta} = $_->{meta} eq 't' ? JSON::XS::true : JSON::XS::false;
+ $_->{meta} = $_->{searchable} ne 't' ? JSON::XS::true : JSON::XS::false; # For backwards compat
+ $_->{searchable} = $_->{searchable} eq 't' ? JSON::XS::true : JSON::XS::false;
+ $_->{applicable} = $_->{applicable} eq 't' ? JSON::XS::true : JSON::XS::false;
$_->{chars} *= 1;
$_->{aliases} = [ split /\r?\n/, ($_->{aliases}||'') ];
$_->{parents} = [ map $_*1, split /,/, ($_->{parents}||'') ];
diff --git a/lib/VNDB/DB/Traits.pm b/lib/VNDB/DB/Traits.pm
index 4ed64e3f..250a7dae 100644
--- a/lib/VNDB/DB/Traits.pm
+++ b/lib/VNDB/DB/Traits.pm
@@ -13,7 +13,7 @@ use Exporter 'import';
our @EXPORT = qw|dbTraitGet dbTraitEdit dbTraitAdd|;
-# Options: id noid search name state what results page sort reverse
+# Options: id noid search name state applicable what results page sort reverse
# what: parents childs(n) addedby
# sort: id name name added items search
sub dbTraitGet {
@@ -39,10 +39,11 @@ sub dbTraitGet {
'(t.name ILIKE ? OR t.alias ILIKE ?)' => [ "%$o{search}%", "%$o{search}%" ] ) : (),
$o{name} ? ( # TODO: This is terribly ugly, use an aliases table.
q{(LOWER(t.name) = LOWER(?) OR t.alias ~ ('(!sin)^'||?||'$'))} => [ $o{name}, '?', quotemeta $o{name} ] ) : (),
+ defined $o{applicable} ? ('t.applicable = ?' => $o{applicable}) : (),
);
my @select = (
- qw|t.id t.meta t.name t.description t.state t.alias t."group" t."order" t.sexual t.c_items t.defaultspoil|,
+ qw|t.id t.searchable t.applicable t.name t.description t.state t.alias t."group" t."order" t.sexual t.c_items t.defaultspoil|,
'tg.name AS groupname', 'tg."order" AS grouporder', q|extract('epoch' from t.added) as added|,
$o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (),
);
@@ -86,7 +87,7 @@ sub dbTraitEdit {
$self->dbExec('UPDATE traits !H WHERE id = ?', {
$o{upddate} ? ('added = NOW()' => 1) : (),
- map exists($o{$_}) ? ("\"$_\" = ?" => $o{$_}) : (), qw|name meta description state alias group order sexual defaultspoil|
+ map exists($o{$_}) ? ("\"$_\" = ?" => $o{$_}) : (), qw|name searchable applicable description state alias group order sexual defaultspoil|
}, $id);
if($o{parents}) {
$self->dbExec('DELETE FROM traits_parents WHERE trait = ?', $id);
@@ -99,8 +100,8 @@ sub dbTraitEdit {
# returns the id of the new trait
sub dbTraitAdd {
my($self, %o) = @_;
- my $id = $self->dbRow('INSERT INTO traits (name, meta, description, state, alias, "group", "order", sexual, defaultspoil, addedby) VALUES (!l, ?) RETURNING id',
- [ map $o{$_}, qw|name meta description state alias group order sexual defaultspoil| ], $o{addedby}||$self->authInfo->{id}
+ my $id = $self->dbRow('INSERT INTO traits (name, searchable, applicable, description, state, alias, "group", "order", sexual, defaultspoil, addedby) VALUES (!l, ?) RETURNING id',
+ [ map $o{$_}, qw|name searchable applicable description state alias group order sexual defaultspoil| ], $o{addedby}||$self->authInfo->{id}
)->{id};
$self->dbExec('INSERT INTO traits_parents (trait, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
return $id;
diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index 46fc745c..814fce7b 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -351,17 +351,17 @@ sub edit {
my(@traits, @vns);
if(!$frm->{_err}) {
# parse and normalize
- @traits = sort { $a->[0] <=> $b->[0] } map /^(\d+)-(\d+)$/&&[$1,$2], split / /, $frm->{traits};
@vns = sort { $a->[0] <=> $b->[0] || $a->[1] <=> $b->[1] } map [split /-/], split / /, $frm->{vns};
- $frm->{traits} = join(' ', map sprintf('%d-%d', @$_), @traits);
$frm->{vns} = join(' ', map sprintf('%d-%d-%d-%s', @$_), @vns);
$frm->{ihid} = $frm->{ihid} ?1:0;
$frm->{ilock} = $frm->{ilock}?1:0;
$frm->{desc} = $self->bbSubstLinks($frm->{desc});
$frm->{main_spoil} = 0 if !$frm->{main};
- my %traits = @traits ? map +($_->{id}, 1), @{$self->dbTraitGet(results => 500, state => 2, id => [ map $_->[0], @traits ])} : ();
+ @traits = sort { $a->[0] <=> $b->[0] } map /^(\d+)-(\d+)$/&&[$1,$2], split / /, $frm->{traits};
+ my %traits = @traits ? map +($_->{id}, 1), @{$self->dbTraitGet(results => 500, state => 2, applicable => 1, id => [ map $_->[0], @traits ])} : ();
@traits = grep $traits{$_->[0]}, @traits;
+ $frm->{traits} = join(' ', map sprintf('%d-%d', @$_), @traits);
# check for changes
my $same = $id && !grep +($frm->{$_}//'') ne ($b4{$_}//''), keys %b4;
diff --git a/lib/VNDB/Handler/Traits.pm b/lib/VNDB/Handler/Traits.pm
index 38f7c9ac..8d798245 100644
--- a/lib/VNDB/Handler/Traits.pm
+++ b/lib/VNDB/Handler/Traits.pm
@@ -31,7 +31,7 @@ sub traitpage {
);
return $self->resNotFound if $f->{_err};
- my $title = sprintf '%s: %s', $t->{meta} ? 'Meta trait' : 'Trait', $t->{name};
+ my $title = "Trait: $t->{name}";
$self->htmlHeader(title => $title, noindex => $t->{state} != 2);
$self->htmlMainTabs('i', $t);
@@ -67,6 +67,15 @@ sub traitpage {
lit bb2html $t->{description};
end;
}
+ if(!$t->{applicable} || !$t->{searchable}) {
+ p class => 'center';
+ b 'Properties';
+ br;
+ txt 'Not searchable.' if !$t->{searchable};
+ br;
+ txt 'Can not be directly applied to characters.' if !$t->{applicable};
+ end;
+ }
if($t->{sexual}) {
p class => 'center';
b 'Sexual content';
@@ -83,7 +92,7 @@ sub traitpage {
childtags($self, 'Child traits', 'i', $t) if @{$t->{childs}};
- if(!$t->{meta} && $t->{state} == 2) {
+ if($t->{searchable} && $t->{state} == 2) {
my($chars, $np) = $self->filFetchDB(char => $f->{fil}, {}, {
trait_inc => $trait,
tagspoil => $f->{m},
@@ -144,7 +153,8 @@ sub traitedit {
$frm = $self->formValidate(
{ post => 'name', required => 1, maxlength => 250, regex => [ qr/^[^,]+$/, 'A comma is not allowed in trait names' ] },
{ post => 'state', required => 0, default => 0, enum => [ 0..2 ] },
- { post => 'meta', required => 0, default => 0 },
+ { post => 'searchable', required => 0, default => 0 },
+ { post => 'applicable', required => 0, default => 0 },
{ post => 'sexual', 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 => '' },
@@ -168,12 +178,16 @@ sub traitedit {
}
if(!$frm->{_err}) {
- $frm->{state} = $frm->{meta} = 0 if !$self->authCan('tagmod');
+ if(!$self->authCan('tagmod')) {
+ $frm->{state} = 0;
+ $frm->{applicable} = $frm->{searchable} = 1;
+ }
my %opts = (
name => $frm->{name},
state => $frm->{state},
description => $frm->{description},
- meta => $frm->{meta}?1:0,
+ searchable => $frm->{searchable}?1:0,
+ applicable => $frm->{applicable}?1:0,
sexual => $frm->{sexual}?1:0,
alias => $frm->{alias},
order => $frm->{order},
@@ -193,7 +207,7 @@ sub traitedit {
}
if($t) {
- $frm->{$_} ||= $t->{$_} for (qw|name meta sexual description state alias order defaultspoil|);
+ $frm->{$_} ||= $t->{$_} for (qw|name searchable applicable sexual description state alias order defaultspoil|);
$frm->{parents} ||= join ' ', map $_->{id}, @{$t->{parents}};
}
@@ -220,7 +234,8 @@ sub traitedit {
[ static => label => 'Added by', content => fmtuser($t->{addedby}, $t->{username}) ] : (),
[ select => short => 'state', name => 'State', options => [
[0,'Awaiting moderation'], [1,'Deleted/hidden'], [2,'Approved'] ] ],
- [ checkbox => short => 'meta', name => 'This is a meta trait (only to be used as parent for other traits, not for direct use with characters)' ]
+ [ checkbox => short => 'searchable', name => 'Searchable (people can use this trait to filter characters)' ],
+ [ checkbox => short => 'applicable', name => 'Applicable (people can apply this trait to characters)' ],
) : (),
[ checkbox => short => 'sexual', name => 'Indicates sexual content' ],
[ textarea => short => 'alias', name => "Aliases\n(Separated by newlines)", cols => 30, rows => 4 ],
@@ -427,7 +442,7 @@ sub traitxml {
xml;
tag 'traits', more => $np ? 'yes' : 'no';
for(@$list) {
- tag 'item', id => $_->{id}, meta => $_->{meta} ? 'yes' : 'no', group => $_->{group}||'',
+ tag 'item', id => $_->{id}, searchable => $_->{searchable} ? 'yes' : 'no', applicable => $_->{applicable} ? 'yes' : 'no', group => $_->{group}||'',
groupname => $_->{groupname}||'', state => $_->{state}, defaultspoil => $_->{defaultspoil}, $_->{name};
}
end;
diff --git a/util/sql/func.sql b/util/sql/func.sql
index a3e5bc8d..494a6046 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -171,7 +171,6 @@ END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-
-- recalculate traits_chars
CREATE OR REPLACE FUNCTION traits_chars_calc() RETURNS void AS $$
BEGIN
@@ -180,14 +179,14 @@ BEGIN
-- all char<->trait links of the latest revisions, including chars
-- inherited from child traits if the parent trait was not mentioned
-- directly.
- -- (also includes meta traits, because they could have a normal trait as parent)
- WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler, meta) AS (
- SELECT 15, tid, ct.id, spoil, false
+ -- (also includes non-searchable traits, because they could have a searchable trait as parent)
+ WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler, searchable) AS (
+ SELECT 15, tid, ct.id, spoil, true
FROM chars_traits ct
JOIN chars c ON c.id = ct.id
WHERE NOT c.hidden
UNION ALL
- SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, t.meta
+ SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, t.searchable
FROM traits_chars_all tc
JOIN traits_parents tp ON tp.trait = tc.tid
JOIN traits t ON t.id = tp.parent
@@ -195,10 +194,10 @@ BEGIN
AND tc.lvl > 0
AND NOT EXISTS(SELECT 1 FROM chars_traits cti WHERE cti.tid = tp.parent AND cti.id = tc.cid)
)
- -- now grouped by (tid, cid) and with meta traits filtered out
+ -- now grouped by (tid, cid) and with non-searchable traits filtered out
SELECT tid, cid, (CASE WHEN AVG(spoiler) > 1.3 THEN 2 WHEN AVG(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler
FROM traits_chars_all
- WHERE NOT meta
+ WHERE searchable
GROUP BY tid, cid;
-- and update the VN count in the tags table
UPDATE traits SET c_items = (SELECT COUNT(*) FROM traits_chars WHERE tid = id);
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 054e0706..40eb6a2b 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -554,7 +554,6 @@ CREATE TABLE traits (
name varchar(250) NOT NULL,
alias varchar(500) NOT NULL DEFAULT '',
description text NOT NULL DEFAULT '',
- meta boolean NOT NULL DEFAULT false,
added timestamptz NOT NULL DEFAULT NOW(),
state smallint NOT NULL DEFAULT 0,
addedby integer NOT NULL DEFAULT 0,
@@ -563,6 +562,8 @@ CREATE TABLE traits (
sexual boolean NOT NULL DEFAULT false,
c_items integer NOT NULL DEFAULT 0,
defaultspoil smallint NOT NULL DEFAULT 0
+ searchable boolean NOT NULL DEFAULT true,
+ applicable boolean NOT NULL DEFAULT true
);
-- traits_chars
diff --git a/util/updates/update_20190626.sql b/util/updates/update_20190626.sql
index fea09ac0..cab7a8a7 100644
--- a/util/updates/update_20190626.sql
+++ b/util/updates/update_20190626.sql
@@ -2,3 +2,10 @@ ALTER TABLE tags ADD COLUMN searchable boolean NOT NULL DEFAULT TRUE;
ALTER TABLE tags ADD COLUMN applicable boolean NOT NULL DEFAULT TRUE;
UPDATE tags SET searchable = NOT meta, applicable = NOT meta;
ALTER TABLE tags DROP COLUMN meta;
+
+ALTER TABLE traits ADD COLUMN searchable boolean NOT NULL DEFAULT TRUE;
+ALTER TABLE traits ADD COLUMN applicable boolean NOT NULL DEFAULT TRUE;
+UPDATE traits SET searchable = NOT meta, applicable = NOT meta;
+ALTER TABLE traits DROP COLUMN meta;
+
+-- NOTE: Be sure to run util/sql/func.sql