Modified definition of the anime table

Removed most NOT NULL constraints, and converted lastfetch to a
timestamp data type.

The site has been updated to handle this, but Multi::Anime won't work.

2 files changed, 42 insertions, 7 deletions

diff --git a/util/dump.sql b/util/dump.sql
index b169f02c..db185d18 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -20,13 +20,13 @@ SET default_with_oids = false;
 -- anime
 CREATE TABLE anime (
   id integer NOT NULL PRIMARY KEY,
-  year smallint NOT NULL DEFAULT 0,
-  ann_id integer NOT NULL DEFAULT 0,
-  nfo_id varchar(200) NOT NULL DEFAULT '',
-  type smallint NOT NULL DEFAULT 0,
-  title_romaji varchar(200) NOT NULL DEFAULT '',
-  title_kanji varchar(200) NOT NULL DEFAULT '',
-  lastfetch bigint NOT NULL DEFAULT 0
+  year smallint,
+  ann_id integer,
+  nfo_id varchar(200),
+  type smallint,
+  title_romaji,
+  title_kanji,
+  lastfetch timestamp
 );
 
 -- changes
diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql
new file mode 100644
index 00000000..2b0f5478
--- /dev/null
+++ b/util/updates/update_2.6.sql
@@ -0,0 +1,35 @@
+
+
+-- The anime table:
+--  - use timestamp data type for anime.lastfetch
+--  - allow NULL for all columns except id
+ALTER TABLE anime ALTER COLUMN lastfetch DROP NOT NULL;
+ALTER TABLE anime ALTER COLUMN lastfetch DROP DEFAULT;
+UPDATE anime SET lastfetch = NULL WHERE lastfetch <= 0;
+ALTER TABLE anime ALTER COLUMN lastfetch TYPE timestamp USING timestamp 'epoch' + lastfetch * interval '1 second';
+
+ALTER TABLE anime ALTER COLUMN ann_id DROP NOT NULL;
+ALTER TABLE anime ALTER COLUMN ann_id DROP DEFAULT;
+UPDATE anime SET ann_id = NULL WHERE ann_id = 0;
+
+ALTER TABLE anime ALTER COLUMN nfo_id DROP NOT NULL;
+ALTER TABLE anime ALTER COLUMN nfo_id DROP DEFAULT;
+UPDATE anime SET nfo_id = NULL WHERE nfo_id = '';
+
+ALTER TABLE anime ALTER COLUMN title_kanji DROP NOT NULL;
+ALTER TABLE anime ALTER COLUMN title_kanji DROP DEFAULT;
+UPDATE anime SET title_kanji = NULL WHERE title_kanji = '';
+
+ALTER TABLE anime ALTER COLUMN title_romaji DROP NOT NULL;
+ALTER TABLE anime ALTER COLUMN title_romaji DROP DEFAULT;
+UPDATE anime SET title_romaji = NULL WHERE title_romaji = '';
+
+ALTER TABLE anime ALTER COLUMN type DROP NOT NULL;
+ALTER TABLE anime ALTER COLUMN type DROP DEFAULT;
+UPDATE anime SET type = NULL WHERE type = 0;
+UPDATE anime SET type = type-1;
+
+ALTER TABLE anime ALTER COLUMN year DROP NOT NULL;
+ALTER TABLE anime ALTER COLUMN year DROP DEFAULT;
+UPDATE anime SET year = NULL WHERE year = 0;
+