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

2 files changed, 42 insertions, 0 deletions

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;
+