diff options
author | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-08-18 10:43:33 +0000 |
---|---|---|
committer | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-08-18 10:43:33 +0000 |
commit | a6854187b0e2fcc8a89874090eeacdb938bc285a (patch) | |
tree | 1fee81148452d593d149131705e7618d41ff8cf8 /util | |
parent | a528cb22d0459b0b76ac163c40cbe28a2ef27b70 (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.sql | 10 | ||||
-rwxr-xr-x | util/updates/update_1.22.pl | 28 | ||||
-rw-r--r-- | util/updates/update_1.22.sql | 14 |
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; + |