From 892f6a866c149b3460d8c45a03706659d269129b Mon Sep 17 00:00:00 2001
From: yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>
Date: Sun, 13 Jul 2008 12:16:57 +0000
Subject: Added an SQL script to initialize the database needed to run the
 site, and some minor code fixes

git-svn-id: svn://vndb.org/vndb@67 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b
---
 data/tpl/home           |   2 +
 lib/VNDB/Discussions.pm |   1 +
 lib/global.pl           |   1 -
 util/dump.sql           | 367 ++++++++++++++++++++++++++++++++++++++++++++++++
 4 files changed, 370 insertions(+), 1 deletion(-)
 create mode 100644 util/dump.sql

diff --git a/data/tpl/home b/data/tpl/home
index f8dd692c..1d041841 100644
--- a/data/tpl/home
+++ b/data/tpl/home
@@ -13,6 +13,7 @@
  or to participate in the discussions about visual novels or VNDB on our <a href="/t">discussion board</a>.
 </p>
 
+[[ if($d{an}{title}) { ]]-
 <h3 class="home">[[: $d{an}{title} ]]-
  <p class="actions">[[= age $d{anpost}{date} ]]</p></h3>
 <p class="desc">
@@ -20,6 +21,7 @@
  <br />
  <a href="/t[[= $d{an}{id} ]]">Read more...</a> - <a href="/t/an">news archive</a>.
 </p>
+[[ } ]]-
 
 <ul class="home">
  <li><b>Recent changes</b></li>
diff --git a/lib/VNDB/Discussions.pm b/lib/VNDB/Discussions.pm
index 6f47aa63..4c015357 100644
--- a/lib/VNDB/Discussions.pm
+++ b/lib/VNDB/Discussions.pm
@@ -145,6 +145,7 @@ sub TTag {
   my $self = shift;
   my $tag = shift;
   my($type, $iid) = ($1, $2||0) if $tag =~ /^([a-z]{1,2})([0-9]*)$/;
+  return $self->ResNotFound if !$type;
 
   my $f = $self->FormCheck(
     { name => 'p', required => 0, default => 1, template => 'int' },
diff --git a/lib/global.pl b/lib/global.pl
index 999e082a..353ffb6d 100644
--- a/lib/global.pl
+++ b/lib/global.pl
@@ -50,7 +50,6 @@ our $DTAGS = {
   an => 'Announcements',    # 0   - usage restricted to boardmods
   db => 'VNDB Discussions', # 0
   v  => 'Visual novels',    # vid
-  #r  => 'Releases',         # rid
   p  => 'Producers',        # pid
   u  => 'Users',            # uid
 };
diff --git a/util/dump.sql b/util/dump.sql
new file mode 100644
index 00000000..1ed22c22
--- /dev/null
+++ b/util/dump.sql
@@ -0,0 +1,367 @@
+
+
+-- 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
+
+
+
+
+
+
+-----------------------------------------
+--  T A B L E   D E F I N I T I O N S  --
+-----------------------------------------
+
+
+-- 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
+);
+
+-- changes
+CREATE TABLE changes (
+  id SERIAL NOT NULL PRIMARY KEY,
+  type smallint NOT NULL DEFAULT 0,
+  rev integer NOT NULL DEFAULT 1,
+  added bigint NOT NULL DEFAULT DATE_PART('epoch', NOW()),
+  requester integer NOT NULL DEFAULT 0,
+  ip inet NOT NULL DEFAULT '0.0.0.0',
+  comments text NOT NULL DEFAULT '',
+  causedby integer NOT NULL DEFAULT 0
+);
+
+-- producers
+CREATE TABLE producers (
+  id SERIAL NOT NULL PRIMARY KEY,
+  latest integer NOT NULL DEFAULT 0,
+  locked smallint NOT NULL DEFAULT 0,
+  hidden smallint NOT NULL DEFAULT 0
+);
+
+-- producers_rev
+CREATE TABLE producers_rev (
+  id integer NOT NULL PRIMARY KEY,
+  pid integer NOT NULL DEFAULT 0,
+  type character(2) NOT NULL DEFAULT 'co',
+  name varchar(200) NOT NULL DEFAULT '',
+  original varchar(200) NOT NULL DEFAULT '',
+  website varchar(250) NOT NULL DEFAULT '',
+  lang varchar NOT NULL DEFAULT 'ja',
+  "desc" text NOT NULL DEFAULT ''
+);
+
+-- releases
+CREATE TABLE releases (
+  id SERIAL NOT NULL PRIMARY KEY,
+  latest integer NOT NULL DEFAULT 0,
+  locked smallint NOT NULL DEFAULT 0,
+  hidden smallint NOT NULL DEFAULT 0
+);
+
+-- releases_media
+CREATE TABLE releases_media (
+  rid integer NOT NULL DEFAULT 0,
+  medium character(3) NOT NULL DEFAULT '',
+  qty smallint NOT NULL DEFAULT 1,
+  PRIMARY KEY(rid, medium, qty)
+);
+
+-- releases_platforms
+CREATE TABLE releases_platforms (
+  rid integer NOT NULL DEFAULT 0,
+  platform character(3) NOT NULL DEFAULT 0,
+  PRIMARY KEY(rid, platform)
+);
+
+-- releases_producers
+CREATE TABLE releases_producers (
+  rid integer NOT NULL,
+  pid integer NOT NULL,
+  PRIMARY KEY(pid, rid)
+);
+
+-- releases_rev
+CREATE TABLE releases_rev (
+  id integer NOT NULL PRIMARY KEY,
+  rid integer NOT NULL DEFAULT 0,
+  title varchar(250) NOT NULL DEFAULT '',
+  original varchar(250) NOT NULL DEFAULT '',
+  type smallint NOT NULL DEFAULT 0,
+  language varchar NOT NULL DEFAULT 'ja',
+  website varchar(250) NOT NULL DEFAULT '',
+  released integer NOT NULL,
+  notes text NOT NULL DEFAULT '',
+  minage smallint NOT NULL DEFAULT -1,
+  gtin bigint NOT NULL DEFAULT 0
+);
+
+-- releases_vn
+CREATE TABLE releases_vn (
+  rid integer NOT NULL DEFAULT 0,
+  vid integer NOT NULL DEFAULT 0,
+  PRIMARY KEY(rid, vid)
+);
+
+-- threads
+CREATE TABLE threads (
+  id SERIAL NOT NULL PRIMARY KEY,
+  title varchar(50) NOT NULL DEFAULT '',
+  locked smallint NOT NULL DEFAULT 0,
+  hidden smallint NOT NULL DEFAULT 0,
+  count smallint NOT NULL DEFAULT 0
+);
+
+-- threads_posts
+CREATE TABLE threads_posts (
+  tid integer NOT NULL DEFAULT 0,
+  num integer NOT NULL DEFAULT 0,
+  uid integer NOT NULL DEFAULT 0,
+  date bigint NOT NULL DEFAULT DATE_PART('epoch', NOW()),
+  edited bigint NOT NULL DEFAULT 0,
+  msg text NOT NULL DEFAULT '',
+  hidden smallint NOT NULL DEFAULT 0,
+  PRIMARY KEY(tid, num)
+);
+
+-- threads_tags
+CREATE TABLE threads_tags (
+  tid integer NOT NULL DEFAULT 0,
+  type character(2) NOT NULL DEFAULT 0,
+  iid integer NOT NULL DEFAULT 0,
+  PRIMARY KEY(tid, type, iid)
+);
+
+-- users
+CREATE TABLE users (
+  id SERIAL NOT NULL PRIMARY KEY,
+  username varchar(20) NOT NULL UNIQUE,
+  mail varchar(100) NOT NULL,
+  rank smallint NOT NULL DEFAULT 2,
+  passwd bytea NOT NULL DEFAULT '',
+  registered bigint NOT NULL DEFAULT 0,
+  flags integer NOT NULL DEFAULT 7
+);
+
+-- vn
+CREATE TABLE vn (
+  id SERIAL NOT NULL PRIMARY KEY,
+  latest integer NOT NULL DEFAULT 0,
+  locked smallint NOT NULL DEFAULT 0,
+  hidden smallint NOT NULL DEFAULT 0,
+  rgraph integer NOT NULL DEFAULT 0,
+  c_released integer NOT NULL DEFAULT 0,
+  c_languages varchar(32) NOT NULL DEFAULT '',
+  c_platforms varchar(32) NOT NULL DEFAULT ''
+);
+
+-- vn_anime
+CREATE TABLE vn_anime (
+  vid integer NOT NULL,
+  aid integer NOT NULL,
+  PRIMARY KEY(vid, aid)
+);
+
+-- vn_categories
+CREATE TABLE vn_categories (
+  vid integer NOT NULL DEFAULT 0,
+  cat character(3) NOT NULL DEFAULT '',
+  lvl smallint NOT NULL DEFAULT 3,
+  PRIMARY KEY(vid, cat)
+);
+
+-- vn_relations
+CREATE TABLE vn_relations (
+  vid1 integer NOT NULL DEFAULT 0,
+  vid2 integer NOT NULL DEFAULT 0,
+  relation integer NOT NULL DEFAULT 0,
+  PRIMARY KEY(vid1, vid2)
+);
+
+-- vn_rev
+CREATE TABLE vn_rev (
+  id integer NOT NULL PRIMARY KEY,
+  vid integer NOT NULL DEFAULT 0,
+  title varchar(250) NOT NULL DEFAULT '',
+  alias varchar(500) NOT NULL DEFAULT '',
+  img_nsfw smallint NOT NULL DEFAULT 0,
+  length smallint NOT NULL DEFAULT 0,
+  "desc" text NOT NULL DEFAULT '',
+  l_wp varchar(150) NOT NULL DEFAULT '',
+  l_vnn integer NOT NULL DEFAULT 0,
+  image integer NOT NULL DEFAULT 0,
+  l_encubed varchar(100) NOT NULL DEFAULT '',
+  l_renai varchar(100) NOT NULL DEFAULT ''
+);
+
+-- vnlists
+CREATE TABLE vnlists (
+  uid integer DEFAULT 0,
+  vid integer NOT NULL DEFAULT 0,
+  status smallint NOT NULL DEFAULT 0,
+  date bigint NOT NULL DEFAULT 0,
+  comments varchar(500) NOT NULL DEFAULT '',
+  PRIMARY KEY(uid, vid)
+);
+
+-- votes
+CREATE TABLE votes (
+  vid integer NOT NULL DEFAULT 0,
+  uid integer NOT NULL DEFAULT 0,
+  vote integer NOT NULL DEFAULT 0,
+  date bigint NOT NULL DEFAULT 0,
+  PRIMARY KEY(vid, uid)
+);
+
+
+
+
+
+
+
+-----------------------------------------------
+--  F O R E I G N   K E Y   C H E C K I N G  --
+-----------------------------------------------
+
+
+ALTER TABLE changes            ADD FOREIGN KEY (requester) REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers          ADD FOREIGN KEY (latest)    REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers_rev      ADD FOREIGN KEY (id)        REFERENCES changes       (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers_rev      ADD FOREIGN KEY (pid)       REFERENCES producers     (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases           ADD FOREIGN KEY (latest)    REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_media     ADD FOREIGN KEY (rid)       REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_platforms ADD FOREIGN KEY (rid)       REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_producers ADD FOREIGN KEY (rid)       REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_producers ADD FOREIGN KEY (pid)       REFERENCES producers     (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_rev       ADD FOREIGN KEY (id)        REFERENCES changes       (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_rev       ADD FOREIGN KEY (rid)       REFERENCES releases      (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_vn        ADD FOREIGN KEY (rid)       REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_vn        ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_posts      ADD FOREIGN KEY (tid)       REFERENCES threads       (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_posts      ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_tags       ADD FOREIGN KEY (tid)       REFERENCES threads       (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn                 ADD FOREIGN KEY (latest)    REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_anime           ADD FOREIGN KEY (aid)       REFERENCES anime         (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_anime           ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_categories      ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_relations       ADD FOREIGN KEY (vid1)      REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_relations       ADD FOREIGN KEY (vid2)      REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_rev             ADD FOREIGN KEY (id)        REFERENCES changes       (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn_rev             ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vnlists            ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vnlists            ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE votes              ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE votes              ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
+
+
+
+
+
+
+-------------------------
+--  F U N C T I O N S  --
+-------------------------
+
+ 
+-- update_rev(table, ids) - updates the rev column in the changes table
+CREATE FUNCTION update_rev(tbl text, ids text) RETURNS void AS $$
+DECLARE
+  r RECORD;
+  r2 RECORD;
+  i integer;
+  t text;
+  e text;
+BEGIN
+  SELECT INTO t SUBSTRING(tbl, 1, 1);
+  e := '';
+  IF ids <> '' THEN
+    e := ' WHERE id IN('||ids||')';
+  END IF;
+  FOR r IN EXECUTE 'SELECT id FROM '||tbl||e LOOP
+    i := 1;
+    FOR r2 IN EXECUTE 'SELECT id FROM '||tbl||'_rev WHERE '||t||'id = '||r.id||' ORDER BY id ASC' LOOP
+      UPDATE changes SET rev = i WHERE id = r2.id;
+      i := i+1;
+    END LOOP;
+  END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+
+-- update_vncache(id) - updates the c_* columns in the vn table
+CREATE FUNCTION update_vncache(id integer) RETURNS void AS $$
+DECLARE
+  w text := '';
+BEGIN
+  IF id > 0 THEN
+    w := ' WHERE id = '||id;
+  END IF;
+  EXECUTE 'UPDATE vn SET 
+    c_released = COALESCE((SELECT
+      MIN(rr1.released)
+      FROM releases_rev rr1
+      JOIN releases r1 ON rr1.id = r1.latest
+      JOIN releases_vn rv1 ON rr1.id = rv1.rid
+      WHERE rv1.vid = vn.id
+      AND rr1.type <> 2
+      AND r1.hidden = 0
+      AND rr1.released <> 0
+      GROUP BY rv1.vid
+    ), 0),
+    c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+      SELECT language
+      FROM releases_rev rr2
+      JOIN releases r2 ON rr2.id = r2.latest
+      JOIN releases_vn rv2 ON rr2.id = rv2.rid
+      WHERE rv2.vid = vn.id
+      AND rr2.type <> 2
+      AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+      AND r2.hidden = 0
+      GROUP BY rr2.language
+      ORDER BY rr2.language
+    ), ''/''), ''''),
+    c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
+      SELECT rp3.platform
+      FROM releases_platforms rp3
+      JOIN releases_rev rr3 ON rp3.rid = rr3.id
+      JOIN releases r3 ON rp3.rid = r3.latest
+      JOIN releases_vn rv3 ON rp3.rid = rv3.rid
+      WHERE rv3.vid = vn.id
+      AND rr3.type <> 2
+      AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
+      AND r3.hidden = 0
+      GROUP BY rp3.platform
+      ORDER BY rp3.platform
+    ), ''/''), '''')
+  '||w;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+
+
+
+---------------------------------
+--  M I S C E L L A N E O U S  --
+---------------------------------
+
+
+-- Two sequences used for cover and relation graph ID numbers
+CREATE SEQUENCE covers_seq;
+CREATE SEQUENCE relgraph_seq;
+
+-- We can't live without multi
+INSERT INTO users (id, username, mail, rank, registered)
+  VALUES (1, 'multi', 'multi@vndb.org', 0, EXTRACT(EPOCH FROM NOW()));
+
-- 
cgit v1.2.3