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.

1 files changed, 35 insertions, 0 deletions

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;
+