summaryrefslogtreecommitdiff
path: root/util/dump.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-10-08 22:19:35 +0200
committerYorhel <git@yorhel.nl>2009-10-08 22:25:54 +0200
commit4304d30193646fdecbe2fcd081cfbce46ad82ed6 (patch)
tree8c67d603375e2ade3f4b08c4b327f3959c029da8 /util/dump.sql
parentbbc03376d61e2e557d473368611dbba5eead6338 (diff)
Synchronised and updated dump.sql and dbgraph.pl
Removed the 'SET default_with_oids', as the minimum required PostgreSQL version (8.3) has OIDS disables by default already. Also uncommented the CREATE LANGUAGE, plpgsql is a "trusted language" and as such doesn't need superuser priveleges to create anymore.
Diffstat (limited to 'util/dump.sql')
-rw-r--r--util/dump.sql31
1 files changed, 14 insertions, 17 deletions
diff --git a/util/dump.sql b/util/dump.sql
index aaa1a5e9..7a621103 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -1,15 +1,12 @@
-
--- we don't use PgSQL's OIDS
-SET default_with_oids = false;
-
--- for the functions to work, the following query must
--- be executed on the database by a superuser:
--- CREATE PROCEDURAL LANGUAGE plpgsql
-
+-- plpgsql is required for our (trigger) functions
+CREATE LANGUAGE plpgsql;
+-- data types
+CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
-----------------------------------------
@@ -23,7 +20,7 @@ CREATE TABLE anime (
year smallint,
ann_id integer,
nfo_id varchar(200),
- type smallint,
+ type anime_type,
title_romaji,
title_kanji,
lastfetch timestamptz
@@ -137,12 +134,6 @@ CREATE TABLE releases_vn (
PRIMARY KEY(rid, vid)
);
--- relgraph
-CREATE TABLE relgraph (
- id SERIAL NOT NULL PRIMARY KEY,
- cmap text NOT NULL DEFAULT ''
-);
-
-- rlists
CREATE TABLE rlists (
uid integer NOT NULL DEFAULT 0,
@@ -288,11 +279,17 @@ CREATE TABLE vn_anime (
PRIMARY KEY(vid, aid)
);
+-- vn_graphs
+CREATE TABLE vn_graphs (
+ id SERIAL PRIMARY KEY,
+ svg xml NOT NULL
+);
+
-- vn_relations
CREATE TABLE vn_relations (
vid1 integer NOT NULL DEFAULT 0,
vid2 integer NOT NULL DEFAULT 0,
- relation integer NOT NULL DEFAULT 0,
+ relation vn_relation NOT NULL,
PRIMARY KEY(vid1, vid2)
);
@@ -380,7 +377,7 @@ ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads
ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id);
ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id);
ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id);
+ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES vn_graphs (id);
ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id);
ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id);