summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authoryorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>2008-08-18 10:43:33 +0000
committeryorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b>2008-08-18 10:43:33 +0000
commita6854187b0e2fcc8a89874090eeacdb938bc285a (patch)
tree1fee81148452d593d149131705e7618d41ff8cf8 /util
parenta528cb22d0459b0b76ac163c40cbe28a2ef27b70 (diff)
Stored relgraph image maps in the DB, instead of using plain text files in /data/rg/
git-svn-id: svn://vndb.org/vndb@93 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b
Diffstat (limited to 'util')
-rw-r--r--util/dump.sql10
-rwxr-xr-xutil/updates/update_1.22.pl28
-rw-r--r--util/updates/update_1.22.sql14
3 files changed, 50 insertions, 2 deletions
diff --git a/util/dump.sql b/util/dump.sql
index 3294179e..abb04935 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -113,6 +113,12 @@ 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,
@@ -177,7 +183,7 @@ CREATE TABLE vn (
latest integer NOT NULL DEFAULT 0,
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
- rgraph integer NOT NULL DEFAULT 0,
+ rgraph integer,
c_released integer NOT NULL DEFAULT 0,
c_languages varchar(32) NOT NULL DEFAULT '',
c_platforms varchar(32) NOT NULL DEFAULT ''
@@ -287,6 +293,7 @@ ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads
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 ADD FOREIGN KEY (rgraph) REFERENCES relgraph (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;
@@ -399,7 +406,6 @@ $$ LANGUAGE plpgsql;
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
-CREATE SEQUENCE relgraph_seq;
INSERT INTO users (id, username, mail, rank)
VALUES (0, 'deleted', 'del@vndb.org', 0);
diff --git a/util/updates/update_1.22.pl b/util/updates/update_1.22.pl
new file mode 100755
index 00000000..465c8c40
--- /dev/null
+++ b/util/updates/update_1.22.pl
@@ -0,0 +1,28 @@
+#!/usr/bin/perl
+
+
+# update_1.22.sql must be executed before this script
+
+
+use strict;
+use warnings;
+use DBI;
+
+BEGIN { require '/www/vndb/lib/global.pl' }
+
+my $sql = DBI->connect(@VNDB::DBLOGIN,
+ { PrintError => 1, RaiseError => 1, AutoCommit => 0 });
+
+my $q = $sql->prepare('INSERT INTO relgraph (id, cmap) VALUES(?,?)');
+for (glob "/www/vndb/data/rg/*/*.cmap") {
+ my $id = $1 if /([0-9]+)\.cmap$/;
+ open my $F, '<', $_ or die $!;
+ $q->execute($id, join "\n", <$F>);
+ close $F;
+}
+
+$sql->do('ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id) DEFERRABLE INITIALLY DEFERRED');
+$sql->commit;
+
+# it's now safe to delete /data/rg
+
diff --git a/util/updates/update_1.22.sql b/util/updates/update_1.22.sql
new file mode 100644
index 00000000..cc475b32
--- /dev/null
+++ b/util/updates/update_1.22.sql
@@ -0,0 +1,14 @@
+
+-- store relation graph image maps in the database
+CREATE TABLE relgraph (
+ id SERIAL NOT NULL PRIMARY KEY,
+ cmap text NOT NULL DEFAULT ''
+) WITHOUT OIDS;
+
+SELECT SETVAL('relgraph_id_seq', NEXTVAL('relgraph_seq'));
+DROP SEQUENCE relgraph_seq;
+
+ALTER TABLE vn ALTER COLUMN rgraph DROP NOT NULL;
+ALTER TABLE vn ALTER COLUMN rgraph SET DEFAULT NULL;
+UPDATE vn SET rgraph = NULL WHERE rgraph = 0;
+