From 19ce5fcf536ed478ad34b6b1014bf6f44841d25d Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sun, 6 Sep 2015 03:35:36 +0200
Subject: SQL: Convert threads_board.type to ENUM

The char(2) solution is both inefficient and ugly. Also needed to be
careful with handling the extra space that Postgres would automatically
add to single-character types.
---
 lib/VNDB/DB/Discussions.pm   | 4 ++--
 util/sql/all.sql             | 1 +
 util/sql/schema.sql          | 2 +-
 util/updates/update_2.25.sql | 5 +++++
 4 files changed, 9 insertions(+), 3 deletions(-)

diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 9da25384..574560a0 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -70,7 +70,7 @@ sub dbThreadGet {
     } 0..$#$r;
 
     if($o{what} =~ /boards/) {
-      ($_->{type}=~s/ +//||1) && push(@{$r->[$r{$_->{tid}}]{boards}}, [ $_->{type}, $_->{iid} ]) for (@{$self->dbAll(q|
+      push(@{$r->[$r{$_->{tid}}]{boards}}, [ $_->{type}, $_->{iid} ]) for (@{$self->dbAll(q|
         SELECT tid, type, iid
           FROM threads_boards
           WHERE tid IN(!l)|,
@@ -78,7 +78,7 @@ sub dbThreadGet {
       )});
     }
     if($o{what} =~ /boardtitles/) {
-      ($_->{type}=~s/ +//||1) && push(@{$r->[$r{$_->{tid}}]{boards}}, $_) for (@{$self->dbAll(q|
+      push(@{$r->[$r{$_->{tid}}]{boards}}, $_) for (@{$self->dbAll(q|
         SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, vr.title, pr.name) AS title, COALESCE(u.username, vr.original, pr.original) AS original
           FROM threads_boards tb
           LEFT JOIN vn v ON tb.type = 'v' AND v.id = tb.iid
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 7c535f7a..833f5890 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -5,6 +5,7 @@
 
 CREATE TYPE anime_type        AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
 CREATE TYPE blood_type        AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
+CREATE TYPE board_type        AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
 CREATE TYPE char_role         AS ENUM ('main', 'primary', 'side', 'appears');
 CREATE TYPE credit_type       AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
 CREATE TYPE dbentry_type      AS ENUM ('v', 'r', 'p', 'c', 's');
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 5c4342e2..8de05fde 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -352,7 +352,7 @@ CREATE TABLE threads_posts (
 -- threads_boards
 CREATE TABLE threads_boards (
   tid integer NOT NULL DEFAULT 0,
-  type character(2) NOT NULL DEFAULT 0,
+  type board_type NOT NULL,
   iid integer NOT NULL DEFAULT 0,
   PRIMARY KEY(tid, type, iid)
 );
diff --git a/util/updates/update_2.25.sql b/util/updates/update_2.25.sql
index eb2f2776..8207adc6 100644
--- a/util/updates/update_2.25.sql
+++ b/util/updates/update_2.25.sql
@@ -44,3 +44,8 @@ ALTER TYPE prefs_key ADD VALUE 'tags_cat';
 ALTER TYPE prefs_key ADD VALUE 'spoilers';
 ALTER TYPE prefs_key ADD VALUE 'traits_sexual';
 
+
+-- Convert threads_boards.type to enum
+CREATE TYPE board_type        AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
+ALTER TABLE threads_boards ALTER COLUMN type DROP DEFAULT;
+ALTER TABLE threads_boards ALTER COLUMN type TYPE board_type USING trim(type)::board_type;
-- 
cgit v1.2.3