From 6bd0b0cd1f3892253d881f71533940f0cf07c13d Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Tue, 2 Oct 2018 12:54:41 +0200
Subject: DB: Convert resolution into an enum

Been wanting to do this for a long time - using an integer index into an
array that changes once in a while is way too fragile. Doubly so when
said indices are also used in filters and URLs that can't be updated
every time a new resolution is added.
---
 util/jsgen.pl                    |  4 ++--
 util/sql/all.sql                 |  1 +
 util/sql/schema.sql              |  4 ++--
 util/updates/update_20181002.sql | 32 ++++++++++++++++++++++++++++++++
 4 files changed, 37 insertions(+), 4 deletions(-)
 create mode 100644 util/updates/update_20181002.sql

(limited to 'util')

diff --git a/util/jsgen.pl b/util/jsgen.pl
index 8317f86f..828d090f 100755
--- a/util/jsgen.pl
+++ b/util/jsgen.pl
@@ -18,8 +18,8 @@ sub resolutions {
   my $cat = '';
   my @r;
   my $push = \@r;
-  for my $i (0..$#{$S{resolutions}}) {
-    my $r = $S{resolutions}[$i];
+  for my $i (keys %{$S{resolutions}}) {
+    my $r = $S{resolutions}{$i};
     if($cat ne $r->[1]) {
       push @r, [$r->[1]];
       $cat = $r->[1];
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 12f16b11..b96c1020 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -22,6 +22,7 @@ CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa',
 CREATE TYPE release_type      AS ENUM ('complete', 'partial', 'trial');
 CREATE TYPE tag_category      AS ENUM('cont', 'ero', 'tech');
 CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+CREATE TYPE resolution        AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080');
 
 
 -- schema
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 6c2d785b..9b031ae6 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -262,7 +262,7 @@ CREATE TABLE releases ( -- dbentry_type=r
   patch      boolean NOT NULL DEFAULT FALSE,
   freeware   boolean NOT NULL DEFAULT FALSE,
   doujin     boolean NOT NULL DEFAULT FALSE,
-  resolution smallint NOT NULL DEFAULT 0,
+  resolution resolution NOT NULL DEFAULT 'unknown',
   voiced     smallint NOT NULL DEFAULT 0,
   ani_story  smallint NOT NULL DEFAULT 0,
   ani_ero    smallint NOT NULL DEFAULT 0,
@@ -284,7 +284,7 @@ CREATE TABLE releases_hist (
   patch      boolean NOT NULL DEFAULT FALSE,
   freeware   boolean NOT NULL DEFAULT FALSE,
   doujin     boolean NOT NULL DEFAULT FALSE,
-  resolution smallint NOT NULL DEFAULT 0,
+  resolution resolution NOT NULL DEFAULT 'unknown',
   voiced     smallint NOT NULL DEFAULT 0,
   ani_story  smallint NOT NULL DEFAULT 0,
   ani_ero    smallint NOT NULL DEFAULT 0,
diff --git a/util/updates/update_20181002.sql b/util/updates/update_20181002.sql
new file mode 100644
index 00000000..d98ed764
--- /dev/null
+++ b/util/updates/update_20181002.sql
@@ -0,0 +1,32 @@
+CREATE TYPE resolution AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080');
+
+CREATE OR REPLACE FUNCTION conv_resolution(integer) RETURNS resolution AS $$
+SELECT CASE
+  WHEN $1 =  0 THEN 'unknown'::resolution
+  WHEN $1 =  1 THEN 'nonstandard'
+  WHEN $1 =  2 THEN '640x480'
+  WHEN $1 =  3 THEN '800x600'
+  WHEN $1 =  4 THEN '1024x768'
+  WHEN $1 =  5 THEN '1280x960'
+  WHEN $1 =  6 THEN '1600x1200'
+  WHEN $1 =  7 THEN '640x400'
+  WHEN $1 =  8 THEN '960x600'
+  WHEN $1 =  9 THEN '1024x576'
+  WHEN $1 = 10 THEN '1024x600'
+  WHEN $1 = 11 THEN '1024x640'
+  WHEN $1 = 12 THEN '1280x720'
+  WHEN $1 = 13 THEN '1280x800'
+  WHEN $1 = 14 THEN '1366x768'
+  WHEN $1 = 15 THEN '1600x900'
+  WHEN $1 = 16 THEN '1920x1080'
+END $$ LANGUAGE SQL;
+
+ALTER TABLE releases ALTER COLUMN resolution DROP DEFAULT;
+ALTER TABLE releases ALTER COLUMN resolution TYPE resolution USING conv_resolution(resolution);
+ALTER TABLE releases ALTER COLUMN resolution SET DEFAULT 'unknown';
+
+ALTER TABLE releases_hist ALTER COLUMN resolution DROP DEFAULT;
+ALTER TABLE releases_hist ALTER COLUMN resolution TYPE resolution USING conv_resolution(resolution);
+ALTER TABLE releases_hist ALTER COLUMN resolution SET DEFAULT 'unknown';
+
+DROP FUNCTION conv_resolution(int);
-- 
cgit v1.2.3