Random quote feature

6 files changed, 39 insertions, 3 deletions

diff --git a/ChangeLog b/ChangeLog
index 8b5b26e1..718eb457 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,5 +1,6 @@
 2.3 - ?
 	- No page reload needed when changing rlist status from vn page
+	- Random VN quotes to the footer of every page
 
 2.2 - 2009-01-16
 	- Additional custom CSS field to user profile
@@ -42,7 +43,7 @@
 	  - Global statistics are cached in stats_cache
 	- URL changes:
 	  - /p and /v don't work anymore, use /[pv]/all
-	  - /u/list/* -> /u/* and /u/list -> /u/list/all
+	  - /u/list/* -> /u/* and /u/list -> /u/all
 	  - Revert URL changed from /x99/edit?rev=1 to /x99.1/edit
 	  - /v+/stats and /v+/scr moved into /v+
 	- Functionality changes:
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index 14ef45ac..c13a38d2 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -6,7 +6,7 @@ use warnings;
 use Exporter 'import';
 
 our @EXPORT = qw|
-  dbStats dbRevisionInsert dbItemInsert dbRevisionGet dbItemMod dbLanguages
+  dbStats dbRevisionInsert dbItemInsert dbRevisionGet dbItemMod dbLanguages dbRandomQuote
 |;
 
 
@@ -173,6 +173,16 @@ sub dbLanguages {
 }
 
 
+# Returns a random quote (hashref with keys = vid, quote)
+sub dbRandomQuote {
+  return $_[0]->dbRow(q|
+    SELECT vid, quote
+      FROM quotes
+      ORDER BY RANDOM()
+      LIMIT 1|);
+}
+
+
 
 
 1;
diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm
index ccf2a9c6..9f0ccc75 100644
--- a/lib/VNDB/Util/LayoutHTML.pm
+++ b/lib/VNDB/Util/LayoutHTML.pm
@@ -141,6 +141,14 @@ sub _menu {
 sub htmlFooter {
   my $self = shift;
      div id => 'footer';
+
+      my $q = $self->dbRandomQuote;
+      if($q && $q->{vid}) {
+        lit '"';
+        a href => "/v$q->{vid}", style => 'text-decoration: none', $q->{quote};
+        txt qq|"\n|;
+      }
+
       txt "vndb $self->{version} | ";
       a href => '/d7', 'about us';
       txt ' | ';
diff --git a/util/dbgraph.pl b/util/dbgraph.pl
index be76bca9..5fd52b75 100755
--- a/util/dbgraph.pl
+++ b/util/dbgraph.pl
@@ -21,7 +21,7 @@ my %subgraphs = (
   'Visual Novels'    => [qw| FFE6BE vn vn_rev vn_relations vn_categories vn_anime vn_screenshots |],
   'Users'            => [qw| CCFFFF users votes vnlists rlists wlists |],
   'Discussion board' => [qw| FFDCDC threads threads_tags threads_posts |],
-  'Misc'             => [qw| F5F5F5 changes anime screenshots relgraph stats_cache |],
+  'Misc'             => [qw| F5F5F5 changes anime screenshots relgraph stats_cache quotes |],
 );
 
 my %tables; # table_name => [ [ col1, pri ], ... ]
diff --git a/util/dump.sql b/util/dump.sql
index f23d6d62..3d937832 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -61,6 +61,15 @@ CREATE TABLE producers_rev (
   "desc" text NOT NULL DEFAULT ''
 );
 
+
+-- quotes
+CREATE TABLE quotes (
+  vid integer NOT NULL,
+  quote varchar(250) NOT NULL,
+  PRIMARY KEY(vid, quote)
+);
+
+
 -- releases
 CREATE TABLE releases (
   id SERIAL NOT NULL PRIMARY KEY,
@@ -296,6 +305,7 @@ ALTER TABLE changes            ADD FOREIGN KEY (causedby)  REFERENCES changes
 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 quotes             ADD FOREIGN KEY (vid)       REFERENCES vn            (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;
diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql
new file mode 100644
index 00000000..4faeba5a
--- /dev/null
+++ b/util/updates/update_2.3.sql
@@ -0,0 +1,7 @@
+
+CREATE TABLE quotes (
+  vid integer NOT NULL REFERENCES vn (id),
+  quote varchar(250) NOT NULL,
+  PRIMARY KEY(vid, quote)
+) WITHOUT OIDS;
+