summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2011-02-21 10:19:48 +0100
committerYorhel <git@yorhel.nl>2011-02-21 10:19:48 +0100
commitacdbb717bb426d237efb7195fc8658eee64d107b (patch)
tree14c59ff7491b4b077baeb5dd4dde05341ddaff43
parent0d8e97158208681c63d934e0c75472d8f5eb11c8 (diff)
chardb: char-by-trait lookup + trait usage count + tag-code sharing
I'll have to optimize the updating of traits_chars as soon as I have some data to test with. Also renamed tags.c_vns to c_items, to have it share the same name as traits.c_items. This makes it a lot easier to re-use code for both tags and traits, such as what I did with dbTagTree/dbTraitTree -> dbTTTree and the childtags() and parenttags() functions.
-rw-r--r--data/lang.txt14
-rw-r--r--lib/VNDB/DB/Chars.pm8
-rw-r--r--lib/VNDB/DB/Tags.pm43
-rw-r--r--lib/VNDB/DB/Traits.pm39
-rw-r--r--lib/VNDB/Func.pm4
-rw-r--r--lib/VNDB/Handler/Chars.pm4
-rw-r--r--lib/VNDB/Handler/Tags.pm16
-rw-r--r--lib/VNDB/Handler/Traits.pm74
-rw-r--r--util/sql/func.sql36
-rw-r--r--util/updates/update_2.19.sql14
10 files changed, 179 insertions, 73 deletions
diff --git a/data/lang.txt b/data/lang.txt
index e45a8577..89558ec6 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -5289,6 +5289,20 @@ cs*:
hu*:
nl : nog [_1] [quant,_1,kenmerk,kenmerken]
+:_traitp_charlist
+en : Characters
+ru*:
+cs*:
+hu*:
+nl : Karakters
+
+:_traitp_nochars
+en : This trait has not been linked to any characters yet, or they were hidden because of your spoiler settings.
+ru*:
+cs*:
+hu*:
+nl : Dit kenmerk is nog niet gekoppeld aan een karakter, of deze worden niet weergegeven in verband met je spoilerinstelling.
+
# Trait add/edit form (/i+/edit, /i+/add, /i/new)
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index e1f045a7..22ddd5d5 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -8,7 +8,7 @@ use Exporter 'import';
our @EXPORT = qw|dbCharGet dbCharRevisionInsert dbCharImageId|;
-# options: id rev what results page
+# options: id rev traitspoil trait_inc trait_exc what results page
# what: extended traits changes
sub dbCharGet {
my $self = shift;
@@ -16,6 +16,7 @@ sub dbCharGet {
page => 1,
results => 10,
what => '',
+ traitspoil => 0,
@_
);
@@ -23,6 +24,11 @@ sub dbCharGet {
!$o{id} && !$o{rev} ? ( 'c.hidden = FALSE' => 1 ) : (),
$o{id} ? ( 'c.id = ?' => $o{id} ) : (),
$o{rev} ? ( 'h.rev = ?' => $o{rev} ) : (),
+ $o{trait_inc} ? (
+ 'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)',
+ [ ref $o{trait_inc} ? $o{trait_inc} : [$o{trait_inc}], $o{traitspoil}, ref $o{trait_inc} ? $#{$o{trait_inc}}+1 : 1 ]) : (),
+ $o{trait_exc} ? (
+ 'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (),
);
my @select = (qw|c.id cr.name cr.original|, 'cr.id AS cid');
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index 8ed4cec6..eee73f10 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -5,12 +5,12 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbTagGet dbTagTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats|;
+our @EXPORT = qw|dbTagGet dbTTTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats|;
# %options->{ id noid name search state meta page results what sort reverse }
# what: parents childs(n) aliases addedby
-# sort: id name added vns
+# sort: id name added items
sub dbTagGet {
my $self = shift;
my %o = (
@@ -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.cat t.c_vns|,
+ qw|t.id t.meta t.name t.description t.state t.cat t.c_items|,
q|extract('epoch' from t.added) as added|,
$o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (),
);
@@ -49,7 +49,7 @@ sub dbTagGet {
id => 't.id %s',
name => 't.name %s',
added => 't.added %s',
- vns => 't.c_vns %s',
+ items => 't.c_items %s',
}->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
my($r, $np) = $self->dbPage(\%o, q|
@@ -73,40 +73,41 @@ sub dbTagGet {
}
if($o{what} =~ /parents\((\d+)\)/) {
- $_->{parents} = $self->dbTagTree($_->{id}, $1, 1) for(@$r);
+ $_->{parents} = $self->dbTTTree(tag => $_->{id}, $1, 1) for(@$r);
}
if($o{what} =~ /childs\((\d+)\)/) {
- $_->{childs} = $self->dbTagTree($_->{id}, $1) for(@$r);
+ $_->{childs} = $self->dbTTTree(tag => $_->{id}, $1) for(@$r);
}
return wantarray ? ($r, $np) : $r;
}
-# Walks the tag tree
+# Walks the tag/trait tree
+# type = tag | trait
# id = tag to start with, or 0 to start with top-level tags
# lvl = max. recursion level
# back = false for parent->child, true for child->parent
-# Returns: [ { id, name, c_vns, sub => [ { id, name, c_vns, sub => [..] }, .. ] }, .. ]
-sub dbTagTree {
- my($self, $id, $lvl, $back) = @_;
+# Returns: [ { id, name, c_items, sub => [ { id, name, c_items, sub => [..] }, .. ] }, .. ]
+sub dbTTTree {
+ my($self, $type, $id, $lvl, $back) = @_;
$lvl ||= 15;
- my $r = $self->dbAll(q|
- WITH RECURSIVE tagtree(lvl, id, parent, name, c_vns) AS (
- SELECT ?::integer, id, 0, name, c_vns
- FROM tags
+ my $r = $self->dbAll(qq|
+ WITH RECURSIVE thetree(lvl, id, parent, name, c_items) AS (
+ SELECT ?::integer, id, 0, name, c_items
+ FROM ${type}s
!W
UNION ALL
- SELECT tt.lvl-1, t.id, tt.id, t.name, t.c_vns
- FROM tagtree tt
- JOIN tags_parents tp ON !s
- JOIN tags t ON !s
+ SELECT tt.lvl-1, t.id, tt.id, t.name, t.c_items
+ FROM thetree tt
+ JOIN ${type}s_parents tp ON !s
+ JOIN ${type}s t ON !s
WHERE tt.lvl > 0
AND t.state = 2
- ) SELECT DISTINCT id, parent, name, c_vns FROM tagtree ORDER BY name|, $lvl,
- $id ? {'id = ?' => $id} : {'NOT EXISTS(SELECT 1 FROM tags_parents WHERE tag = id)' => 1, 'state = 2' => 1},
- !$back ? ('tp.parent = tt.id', 't.id = tp.tag') : ('tp.tag = tt.id', 't.id = tp.parent')
+ ) SELECT DISTINCT id, parent, name, c_items FROM thetree ORDER BY name|, $lvl,
+ $id ? {'id = ?' => $id} : {"NOT EXISTS(SELECT 1 FROM ${type}s_parents WHERE $type = id)" => 1, 'state = 2' => 1},
+ !$back ? ('tp.parent = tt.id', "t.id = tp.$type") : ("tp.$type = tt.id", 't.id = tp.parent')
);
for my $i (@$r) {
$i->{'sub'} = [ grep $_->{parent} == $i->{id}, @$r ];
diff --git a/lib/VNDB/DB/Traits.pm b/lib/VNDB/DB/Traits.pm
index a501c942..0ee83f10 100644
--- a/lib/VNDB/DB/Traits.pm
+++ b/lib/VNDB/DB/Traits.pm
@@ -10,12 +10,12 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbTraitGet dbTraitTree dbTraitEdit dbTraitAdd|;
+our @EXPORT = qw|dbTraitGet dbTraitEdit dbTraitAdd|;
# Options: id what results page sort reverse
# what: parents childs(n) addedby
-# sort: id name groupname added
+# sort: id name groupname added items
sub dbTraitGet {
my $self = shift;
my %o = (
@@ -38,7 +38,7 @@ sub dbTraitGet {
);
my @select = (
- qw|t.id t.meta t.name t.description t.state t.alias t."group" |,
+ qw|t.id t.meta t.name t.description t.state t.alias t."group" t.c_items|,
'tg.name AS groupname', q|extract('epoch' from t.added) as added|,
$o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (),
);
@@ -50,6 +50,7 @@ sub dbTraitGet {
name => 't.name %s',
groupname => 'tg.name %s NULLS FIRST, t.name %1$s',
added => 't.added %s',
+ items => 't.c_items %s',
}->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
my($r, $np) = $self->dbPage(\%o, q|
@@ -62,45 +63,17 @@ sub dbTraitGet {
);
if($o{what} =~ /parents\((\d+)\)/) {
- $_->{parents} = $self->dbTraitTree($_->{id}, $1, 1) for(@$r);
+ $_->{parents} = $self->dbTTTree(trait => $_->{id}, $1, 1) for(@$r);
}
if($o{what} =~ /childs\((\d+)\)/) {
- $_->{childs} = $self->dbTraitTree($_->{id}, $1) for(@$r);
+ $_->{childs} = $self->dbTTTree(trait => $_->{id}, $1) for(@$r);
}
return wantarray ? ($r, $np) : $r;
}
-# almost much equivalent to dbTagTree
-sub dbTraitTree {
- my($self, $id, $lvl, $back) = @_;
- $lvl ||= 15;
- my $r = $self->dbAll(q|
- WITH RECURSIVE traittree(lvl, id, parent, name) AS (
- SELECT ?::integer, id, 0, name
- FROM traits
- !W
- UNION ALL
- SELECT tt.lvl-1, t.id, tt.id, t.name
- FROM traittree tt
- JOIN traits_parents tp ON !s
- JOIN traits t ON !s
- WHERE tt.lvl > 0
- AND t.state = 2
- ) SELECT DISTINCT id, parent, name FROM traittree ORDER BY name|, $lvl,
- $id ? {'id = ?' => $id} : {'NOT EXISTS(SELECT 1 FROM traits_parents WHERE trait = id)' => 1, 'state = 2' => 1},
- !$back ? ('tp.parent = tt.id', 't.id = tp.trait') : ('tp.trait = tt.id', 't.id = tp.parent')
- );
- for my $i (@$r) {
- $i->{'sub'} = [ grep $_->{parent} == $i->{id}, @$r ];
- }
- my @r = grep !delete($_->{parent}), @$r;
- return $id ? $r[0]{'sub'} : \@r;
-}
-
-
# args: trait id, %options->{ columns in the traits table + parents }
sub dbTraitEdit {
my($self, $id, %o) = @_;
diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm
index 20ade586..edb6e3aa 100644
--- a/lib/VNDB/Func.pm
+++ b/lib/VNDB/Func.pm
@@ -147,7 +147,7 @@ sub childtags {
for my $p (sort { @{$b->{'sub'}} <=> @{$a->{'sub'}} } @{$t->{childs}}) {
li;
a href => "/$type$p->{id}", $p->{name};
- b class => 'grayedout', " ($p->{c_vns})" if $type eq 'g' && $p->{c_vns};
+ b class => 'grayedout', " ($p->{c_items})" if $p->{c_items};
end, next if !@{$p->{'sub'}};
ul;
for (0..$#{$p->{'sub'}}) {
@@ -155,7 +155,7 @@ sub childtags {
li;
txt '> ';
a href => "/$type$p->{sub}[$_]{id}", $p->{'sub'}[$_]{name};
- b class => 'grayedout', " ($p->{sub}[$_]{c_vns})" if $type eq 'g' && $p->{'sub'}[$_]{c_vns};
+ b class => 'grayedout', " ($p->{sub}[$_]{c_items})" if $p->{'sub'}[$_]{c_items};
end;
}
if(@{$p->{'sub'}} > 6) {
diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index b05d94d7..20be01b4 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -218,6 +218,10 @@ sub edit {
$frm->{traits} = \@traits;
my $nrev = $self->dbItemEdit(c => $id ? $r->{cid} : undef, %$frm);
+
+ # TEMPORARY SOLUTION! I'll investigate more efficient solutions and incremental updates whenever I have more data
+ $self->dbExec('SELECT traits_chars_calc()');
+
return $self->resRedirect("/c$nrev->{iid}.$nrev->{rev}", 'post');
}
}
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
index 3d371a8a..2326ed17 100644
--- a/lib/VNDB/Handler/Tags.pm
+++ b/lib/VNDB/Handler/Tags.pm
@@ -33,12 +33,12 @@ sub tagpage {
{ get => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore rating| ] },
{ 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 => 'm', required => 0, default => undef, 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->{m} //= $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0;
$f->{fil} //= $self->authPref('filter_vn');
my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->filFetchDB(vn => $f->{fil}, undef, {
@@ -270,7 +270,7 @@ sub _set_childs_cat {
}
};
- my $childs = $self->dbTagTree($tag, 25);
+ my $childs = $self->dbTTTree(tag => $tag, 25);
$e->($childs);
}
@@ -624,7 +624,7 @@ sub tagindex {
end;
end;
- my $t = $self->dbTagTree(0, 2);
+ my $t = $self->dbTTTree(tag => 0, 2);
childtags($self, mt('_tagidx_tree'), 'g', {childs => $t});
table class => 'mainbox threelayout';
@@ -649,13 +649,13 @@ sub tagindex {
# Popular
td;
a class => 'addnew', href => "/g/links", mt '_tagidx_rawtags';
- $r = $self->dbTagGet(sort => 'vns', reverse => 1, meta => 0, results => 10);
+ $r = $self->dbTagGet(sort => 'items', reverse => 1, meta => 0, results => 10);
h1 mt '_tagidx_popular';
ul;
for (@$r) {
li;
a href => "/g$_->{id}", $_->{name};
- txt " ($_->{c_vns})";
+ txt " ($_->{c_items})";
end;
}
end;
@@ -702,14 +702,14 @@ sub fulltree {
li;
txt '> ';
a href => "/g$_->{id}", $_->{name};
- b class => 'grayedout', " ($_->{c_vns})" if $_->{c_vns};
+ b class => 'grayedout', " ($_->{c_items})" if $_->{c_items};
end;
$e->($_->{sub}) if $_->{sub};
}
end;
};
- my $tags = $self->dbTagTree(0, 25);
+ my $tags = $self->dbTTTree(tag => 0, 25);
$self->htmlHeader(title => '[DEBUG] Tag tree', noindex => 1);
div class => 'mainbox';
h1 '[DEBUG] Tag tree';
diff --git a/lib/VNDB/Handler/Traits.pm b/lib/VNDB/Handler/Traits.pm
index f86fb4cc..9d02ec0d 100644
--- a/lib/VNDB/Handler/Traits.pm
+++ b/lib/VNDB/Handler/Traits.pm
@@ -24,6 +24,14 @@ sub traitpage {
my $t = $self->dbTraitGet(id => $trait, what => 'parents(0) childs(2)')->[0];
return $self->resNotFound if !$t;
+ my $f = $self->formValidate(
+ { get => 'p', required => 0, default => 1, template => 'int' },
+ { get => 'm', required => 0, default => undef, enum => [qw|0 1 2|] },
+ );
+ return $self->resNotFound if $f->{_err};
+ my $tagspoil = $self->reqCookie('tagspoil')||'';
+ $f->{m} //= $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0;
+
my $title = mt '_traitp_title', $t->{meta}?0:1, $t->{name};
$self->htmlHeader(title => $title, noindex => $t->{state} != 2);
$self->htmlMainTabs('i', $t);
@@ -69,8 +77,50 @@ sub traitpage {
childtags($self, mt('_traitp_childs'), 'i', $t) if @{$t->{childs}};
- # TODO: list of characters
-
+ if(!$t->{meta} && $t->{state} == 2) {
+ my($chars, $np) = $self->dbCharGet(
+ trait_inc => $trait,
+ traitspoil => $f->{m},
+ results => 50, page => $f->{p},
+ );
+
+ div class => 'mainbox';
+ h1 mt '_traitp_charlist';
+
+ p class => 'browseopts';
+ # Q: tagp!? A: lazyness >_>
+ a href => "/i$trait?m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", mt '_tagp_spoil0';
+ a href => "/i$trait?m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", mt '_tagp_spoil1';
+ a href => "/i$trait?m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", mt '_tagp_spoil2';
+ end;
+
+ if(!@$chars) {
+ p; br; br; txt mt '_traitp_nochars'; end;
+ }
+ # not really cached at the moment
+ # p; br; txt mt '_traitp_cached'; end;
+ end 'div';
+
+ # TODO: proper table with info and such
+ $self->htmlBrowse(
+ class => 'traitchars',
+ options => $f,
+ nextpage => $np,
+ items => $chars,
+ pageurl => "/i$trait?m=$f->{m}",
+ sorturl => "/i$trait?m=$f->{m}",
+ header => [
+ [ 'Name' ],
+ ],
+ row => sub {
+ my($s, $n, $l) = @_;
+ Tr $n%2?(class => 'odd') : ();
+ td class => 'tc1'; a href => "/c$l->{id}", $l->{name}; end;
+ end;
+ },
+ ) if @$chars;
+ }
+
$self->htmlFooter;
}
@@ -126,7 +176,10 @@ sub traitedit {
$trait = $self->dbTraitAdd(%opts);
} else {
$self->dbTraitEdit($trait, %opts, upddate => $frm->{state} == 2 && $t->{state} != 2) if $trait;
- _set_childs_group($self, $trait, $group||$trait) if ($group||0) != ($t->{group});
+ _set_childs_group($self, $trait, $group||$trait) if ($group||0) != ($t->{group}||0);
+
+ # TEMPORARY SOLUTION! I'll investigate more efficient solutions and incremental updates whenever I have more data
+ $self->dbExec('SELECT traits_chars_calc()');
}
$self->resRedirect("/i$trait", 'post');
return;
@@ -185,7 +238,7 @@ sub _set_childs_group {
$e->($_->{sub}) if $_->{sub};
}
};
- $e->($self->dbTraitTree($trait, 25));
+ $e->($self->dbTTTree(trait => $trait, 25));
}
@@ -272,7 +325,7 @@ sub traitindex {
end;
end;
- my $t = $self->dbTraitTree(0, 2);
+ my $t = $self->dbTTTree(trait => 0, 2);
childtags($self, mt('_traiti_tree'), 'i', {childs => $t});
table class => 'mainbox threelayout';
@@ -298,7 +351,16 @@ sub traitindex {
# Popular
td;
h1 mt '_traiti_popular';
- p 'TODO';
+ ul;
+ $r = $self->dbTraitGet(sort => 'items', reverse => 1, results => 10);
+ for (@$r) {
+ li;
+ b class => 'grayedout', $_->{groupname}.' / ' if $_->{group};
+ a href => "/i$_->{id}", $_->{name};
+ txt " ($_->{c_items})";
+ end;
+ }
+ end;
end;
# Moderation queue
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 0938f76d..d7081aef 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -127,13 +127,47 @@ BEGIN
-- recreate index
CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
-- and update the VN count in the tags table
- UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
+ UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
RETURN;
END;
$$ LANGUAGE plpgsql;
+-- recalculate traits_chars
+CREATE OR REPLACE FUNCTION traits_chars_calc() RETURNS void AS $$
+BEGIN
+ TRUNCATE traits_chars;
+ INSERT INTO traits_chars (tid, cid, spoil)
+ -- all char<->trait links of the latest revisions, including chars inherited from child traits
+ -- (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, c.id, spoil, false
+ FROM chars_traits ct
+ JOIN chars c ON c.latest = ct.cid
+ WHERE NOT c.hidden
+ UNION ALL
+ SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, t.meta
+ FROM traits_chars_all tc
+ JOIN traits_parents tp ON tp.trait = tc.tid
+ JOIN traits t ON t.id = tp.parent
+ WHERE t.state = 2
+ AND tc.lvl > 0
+ )
+ -- now grouped by (tid, cid) and with meta 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
+ 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);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+
----------------------------------------------------------
diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql
index 14c3e30e..1332d666 100644
--- a/util/updates/update_2.19.sql
+++ b/util/updates/update_2.19.sql
@@ -12,7 +12,8 @@ CREATE TABLE traits (
added timestamptz NOT NULL DEFAULT NOW(),
state smallint NOT NULL DEFAULT 0,
addedby integer NOT NULL DEFAULT 0 REFERENCES users (id),
- "group" integer
+ "group" integer,
+ c_items integer NOT NULL DEFAULT 0
);
ALTER TABLE traits ADD FOREIGN KEY ("group") REFERENCES traits (id);
@@ -24,6 +25,7 @@ CREATE TABLE traits_parents (
CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+ALTER TABLE tags RENAME c_vns TO c_items;
-- character database -> chars
@@ -75,6 +77,14 @@ CREATE TABLE chars_vns (
PRIMARY KEY(cid, vid, rid)
);
+-- cache table
+CREATE TABLE traits_chars (
+ cid integer NOT NULL REFERENCES chars (id),
+ tid integer NOT NULL REFERENCES traits (id),
+ spoil smallint NOT NULL DEFAULT 0,
+ PRIMARY KEY(cid, tid)
+);
+
CREATE SEQUENCE charimg_seq;
@@ -144,5 +154,7 @@ UPDATE edit_char SET name = 'Saya', original = '沙耶', "desc" = 'There is more
INSERT INTO edit_char_traits VALUES (4, 0), (12, 0);
SELECT edit_char_commit();
+SELECT traits_chars_calc();
+
-- */