diff options
author | Yorhel <git@yorhel.nl> | 2009-07-18 13:04:58 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-07-18 13:04:58 +0200 |
commit | 07467841ff1670b2f365aa265de94fb6b5c421b3 (patch) | |
tree | 1bb1230f9e3d3199ccc63013a7122ce30c8ae79c /util | |
parent | 10160385d7c726774b0e1c66f9e06b1f7ba29af2 (diff) |
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.
Diffstat (limited to 'util')
-rw-r--r-- | util/dump.sql | 14 | ||||
-rw-r--r-- | util/updates/update_2.6.sql | 35 |
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; + |