chardb: Removed unique constraints from traits and traits_aliases

This makes things somewhat simpler.

4 files changed, 68 insertions, 59 deletions

diff --git a/data/lang.txt b/data/lang.txt
index 590cd31f..c02ff76b 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -5379,11 +5379,11 @@ hu*:
 nl : Hoofdkenmerk
 
 :_traite_frm_parents_msg
-en : Comma separated list of trait names to be used as parent for this trait.
+en : List of trait IDs to be used as parent for this trait, separated by a space.
 ru*:
 cs*:
 hu*:
-nl : Kommagescheiden lijst van hoofdkenmerken.
+nl : Spatiegescheiden lijst van hoofdkenmerken (numerieke IDs).
 
 
 # Plain trait browser (/i/list)
diff --git a/lib/VNDB/DB/Traits.pm b/lib/VNDB/DB/Traits.pm
index d30477c6..8bbcdbf3 100644
--- a/lib/VNDB/DB/Traits.pm
+++ b/lib/VNDB/DB/Traits.pm
@@ -14,7 +14,7 @@ our @EXPORT = qw|dbTraitGet dbTraitTree dbTraitEdit dbTraitAdd|;
 
 
 # Options: id noid name what results page sort reverse
-# what: parents childs(n) aliases addedby
+# what: parents childs(n) addedby
 # sort: id name added
 sub dbTraitGet {
   my $self = shift;
@@ -29,19 +29,16 @@ sub dbTraitGet {
 
   my %where = (
     $o{id}   ? ('t.id = ?'  => $o{id}) : (),
-    $o{noid} ? ('t.id <> ?' => $o{noid}) : (),
-    $o{name} ? (
-      't.id = (SELECT id FROM traits LEFT JOIN traits_aliases ON id = trait WHERE lower(name) = ? OR lower(alias) = ? LIMIT 1)' => [ lc $o{name}, lc $o{name} ]) : (),
     defined $o{state} && $o{state} != -1 ? (
       't.state = ?' => $o{state} ) : (),
     !defined $o{state} && !$o{id} && !$o{name} ? (
       't.state = 2' => 1 ) : (),
     $o{search} ? (
-      't.id IN (SELECT id FROM traits LEFT JOIN traits_aliases ON id = trait WHERE name ILIKE ? OR alias ILIKE ?)' => [ "%$o{search}%", "%$o{search}%" ] ) : (),
+      't.name ILIKE ? OR t.alias ILIKE ?' => [ "%$o{search}%", "%$o{search}%" ] ) : (),
   );
 
   my @select = (
-    qw|t.id t.meta t.name t.description t.state|,
+    qw|t.id t.meta t.name t.description t.state t.alias|,
     q|extract('epoch' from t.added) as added|,
     $o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (),
   );
@@ -62,17 +59,6 @@ sub dbTraitGet {
     join(', ', @select), join(' ', @join), \%where, $order
   );
 
-  if(@$r && $o{what} =~ /aliases/) {
-    my %r = map {
-      $_->{aliases} = [];
-      ($_->{id}, $_->{aliases})
-    } @$r;
-
-    push @{$r{$_->{trait}}}, $_->{alias} for (@{$self->dbAll(q|
-      SELECT trait, alias FROM traits_aliases WHERE trait IN(!l)|, [ keys %r ]
-    )});
-  }
-
   if($o{what} =~ /parents\((\d+)\)/) {
     $_->{parents} = $self->dbTraitTree($_->{id}, $1, 1) for(@$r);
   }
@@ -113,18 +99,14 @@ sub dbTraitTree {
 }
 
 
-# args: trait id, %options->{ columns in the traits table + parents + aliases }
+# args: trait id, %options->{ columns in the traits table + parents }
 sub dbTraitEdit {
   my($self, $id, %o) = @_;
 
   $self->dbExec('UPDATE traits !H WHERE id = ?', {
     $o{upddate} ? ('added = NOW()' => 1) : (),
-    map exists($o{$_}) ? ("$_ = ?" => $o{$_}) : (), qw|name meta description state|
+    map exists($o{$_}) ? ("$_ = ?" => $o{$_}) : (), qw|name meta description state alias|
   }, $id);
-  if($o{aliases}) {
-    $self->dbExec('DELETE FROM traits_aliases WHERE trait = ?', $id);
-    $self->dbExec('INSERT INTO traits_aliases (trait, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}});
-  }
   if($o{parents}) {
     $self->dbExec('DELETE FROM traits_parents WHERE trait = ?', $id);
     $self->dbExec('INSERT INTO traits_parents (trait, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
@@ -136,11 +118,10 @@ 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, addedby) VALUES (!l, ?) RETURNING id',
-    [ map $o{$_}, qw|name meta description state| ], $o{addedby}||$self->authInfo->{id}
+  my $id = $self->dbRow('INSERT INTO traits (name, meta, description, state, alias, addedby) VALUES (!l, ?) RETURNING id',
+    [ map $o{$_}, qw|name meta description state alias| ], $o{addedby}||$self->authInfo->{id}
   )->{id};
   $self->dbExec('INSERT INTO traits_parents (trait, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
-  $self->dbExec('INSERT INTO traits_aliases (trait, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}});
   return $id;
 }
 
diff --git a/lib/VNDB/Handler/Traits.pm b/lib/VNDB/Handler/Traits.pm
index 7f79aa27..da5928bc 100644
--- a/lib/VNDB/Handler/Traits.pm
+++ b/lib/VNDB/Handler/Traits.pm
@@ -3,7 +3,7 @@ package VNDB::Handler::Traits;
 
 use strict;
 use warnings;
-use TUWF ':html';
+use TUWF ':html', 'html_escape';
 use VNDB::Func;
 
 
@@ -20,7 +20,7 @@ TUWF::register(
 sub traitpage {
   my($self, $trait) = @_;
 
-  my $t = $self->dbTraitGet(id => $trait, what => 'parents(0) childs(2) aliases')->[0];
+  my $t = $self->dbTraitGet(id => $trait, what => 'parents(0) childs(2)')->[0];
   return $self->resNotFound if !$t;
 
   my $title = mt '_traitp_title', $t->{meta}?0:1, $t->{name};
@@ -57,11 +57,11 @@ sub traitpage {
       lit bb2html $t->{description};
      end;
    }
-   if(@{$t->{aliases}}) {
+   if($t->{alias}) {
      p class => 'center';
       b mt('_traitp_aliases');
       br;
-      lit xml_escape($_).'<br />' for (@{$t->{aliases}});
+      lit html_escape($t->{alias});
      end;
    }
   end 'div';
@@ -87,7 +87,7 @@ sub traitedit {
 
   return $self->htmlDenied if !$self->authCan('charedit') || $trait && !$self->authCan('tagmod');
 
-  my $t = $trait && $self->dbTraitGet(id => $trait, what => 'parents(1) aliases addedby')->[0];
+  my $t = $trait && $self->dbTraitGet(id => $trait, what => 'parents(1) addedby')->[0];
   return $self->resNotFound if $trait && !$t;
 
   if($self->reqMethod eq 'POST') {
@@ -98,21 +98,13 @@ sub traitedit {
       { 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 => '' },
-      { post => 'parents',     required => !$self->authCan('tagmod'), default => '' },
+      { post => 'parents',     required => !$self->authCan('tagmod'), default => '', regex => [ qr/^(?:$|(?:[1-9]\d*)(?: +[1-9]\d*)*)$/, 'Parent traits must be a space-separated list of trait IDs' ] },
     );
-    my @aliases = split /[\t\s]*\n[\t\s]*/, $frm->{alias};
-    my @parents = split /[\t\s]*,[\t\s]*/, $frm->{parents};
+    my @parents = split /[\t ]+/, $frm->{parents};
     if(!$frm->{_err}) {
-      my $c = $self->dbTraitGet(name => $frm->{name}, noid => $trait);
-      push @{$frm->{_err}}, [ 'name', 'tagexists', $c->[0] ] if @$c; # should be traitexists... but meh
-      for (@aliases) {
-        $c = $self->dbTraitGet(name => $_, noid => $trait);
-        push @{$frm->{_err}}, [ 'alias', 'tagexists', $c->[0] ] if @$c;
-      }
       for(@parents) {
-        $c = $self->dbTraitGet(name => $_, noid => $trait);
+        my $c = $self->dbTraitGet(id => $_);
         push @{$frm->{_err}}, [ 'parents', 'func', [ 0, mt '_tagedit_err_notfound', $_ ]] if !@$c;
-        $_ = $c->[0]{id};
       }
     }
 
@@ -123,7 +115,7 @@ sub traitedit {
         state => $frm->{state},
         description => $frm->{description},
         meta => $frm->{meta}?1:0,
-        aliases => \@aliases,
+        alias => $frm->{alias},
         parents => \@parents,
       );
       if(!$trait) {
@@ -137,9 +129,8 @@ sub traitedit {
   }
 
   if($t) {
-    $frm->{$_} ||= $t->{$_} for (qw|name meta description state|);
-    $frm->{alias} ||= join "\n", @{$t->{aliases}};
-    $frm->{parents} ||= join ', ', map $_->{name}, @{$t->{parents}};
+    $frm->{$_} ||= $t->{$_} for (qw|name meta description state alias|);
+    $frm->{parents} ||= join ' ', map $_->{id}, @{$t->{parents}};
   }
 
   my $title = $par ? mt('_traite_title_add', $par->{name}) : $t ? mt('_traite_title_edit', $t->{name}) : mt '_traite_title_new';
diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql
index 94f32bc0..88058006 100644
--- a/util/updates/update_2.19.sql
+++ b/util/updates/update_2.19.sql
@@ -5,7 +5,8 @@
 
 CREATE TABLE traits (
   id SERIAL PRIMARY KEY,
-  name varchar(250) NOT NULL UNIQUE,
+  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(),
@@ -13,11 +14,6 @@ CREATE TABLE traits (
   addedby integer NOT NULL DEFAULT 0 REFERENCES users (id)
 );
 
-CREATE TABLE traits_aliases (
-  alias varchar(250) NOT NULL PRIMARY KEY,
-  trait integer NOT NULL REFERENCES traits (id)
-);
-
 CREATE TABLE traits_parents (
   trait integer NOT NULL REFERENCES traits (id),
   parent integer NOT NULL REFERENCES traits (id),
@@ -99,9 +95,50 @@ CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON chars
 CREATE TRIGGER chars_rev_image_notify     AFTER  INSERT OR UPDATE ON chars_rev     FOR EACH ROW WHEN (NEW.image < 0) EXECUTE PROCEDURE chars_rev_image_notify();
 
 
--- test
---SELECT edit_char_init(null);
---UPDATE edit_revision SET comments = 'New test entry', requester = 2, ip = '0.0.0.0';
---UPDATE edit_char SET name = 'Phorni', original = 'フォーニ', "desc" = 'Sprite of Music';
---SELECT edit_char_commit();
+/* Debugging data *-/
+
+-- some traits, based on Echo's draft
+INSERT INTO traits (name, meta, state, addedby) VALUES
+  ('Hair', true, 2, 2),
+  ('Hair Color', true, 2, 2),
+  ('Auburn', false, 2, 2),
+  ('Black', false, 2, 2),
+  ('Blond', false, 2, 2),
+  ('Brown', false, 2, 2),
+  ('Hairstyle', true, 2, 2),
+  ('Bun', false, 2, 2),
+  ('Odango', false, 2, 2),
+  ('Ponytail', false, 2, 2),
+  ('Twin Tails', false, 2, 2),
+  ('Short', false, 2, 2),
+  ('Straight', false, 2, 2);
+INSERT INTO traits_parents (trait, parent) VALUES
+  (2, 1),
+  (3, 2),
+  (4, 2),
+  (5, 2),
+  (6, 2),
+  (7, 1),
+  (8, 7),
+  (9, 8),
+  (9, 11),
+  (10, 7),
+  (11, 10),
+  (12, 7),
+  (13, 7);
+
+
+-- phorni!
+SELECT edit_char_init(null);
+UPDATE edit_revision SET comments = 'New test entry', requester = 2, ip = '0.0.0.0';
+UPDATE edit_char SET name = 'Phorni', original = 'フォーニ', "desc" = 'Sprite of Music', height = 14;
+SELECT edit_char_commit();
+
+-- saya (incorrect test data)
+SELECT edit_char_init(null);
+UPDATE edit_revision SET comments = '2nd test entry', requester = 2, ip = '0.0.0.0';
+UPDATE edit_char SET name = 'Saya', original = '沙耶', "desc" = 'There is more than meets the eye!', alias = 'Cute monster', height = 140, weight = 52, s_bust = 41, s_waist = 38, s_hip = 40, b_month = 3, b_day = 15, bloodt = 'a';
+SELECT edit_char_commit();
+
+-- */