From 4304d30193646fdecbe2fcd081cfbce46ad82ed6 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Thu, 8 Oct 2009 22:19:35 +0200 Subject: 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. --- util/dbgraph.pl | 6 +++--- util/dump.sql | 31 ++++++++++++++----------------- 2 files changed, 17 insertions(+), 20 deletions(-) (limited to 'util') diff --git a/util/dbgraph.pl b/util/dbgraph.pl index 167680cd..51e7302e 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -18,11 +18,11 @@ use warnings; my %subgraphs = ( 'Producers' => [qw| FFFFCC producers producers_rev |], 'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |], - 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_categories vn_anime vn_screenshots |], - 'Users' => [qw| CCFFFF users votes rlists wlists |], + 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_anime vn_screenshots vn_graphs |], + 'Users' => [qw| CCFFFF users votes rlists wlists sessions |], 'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |], 'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |], - 'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache quotes sessions |], + 'Misc' => [qw| F5F5F5 changes anime screenshots stats_cache quotes |], ); my %tables; # table_name => [ [ col1, pri ], ... ] 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); -- cgit v1.2.3