From 8d477e62a32f8e8de21341aca7d3b01a5c2bf118 Mon Sep 17 00:00:00 2001
From: tim b <timb@camcompu.home>
Date: Wed, 22 Sep 2010 10:07:01 -0700
Subject: use image_urls table... should be slightly less brutal on the db

---
 db/0-create.psql |  8 +++++++-
 src/site.clj     | 10 ++++------
 2 files changed, 11 insertions(+), 7 deletions(-)

diff --git a/db/0-create.psql b/db/0-create.psql
index 68f9bfd..1162f86 100644
--- a/db/0-create.psql
+++ b/db/0-create.psql
@@ -35,7 +35,6 @@ CREATE TABLE rooms (
     max_image_height integer NOT NULL DEFAULT 2000
 );
 
-
 -- ALTER TABLE rooms ADD COLUMN history_size integer NOT NULL DEFAULT 25;
 -- ALTER TABLE rooms ADD COLUMN max_file_size integer NOT NULL DEFAULT 1048576;
 -- ALTER TABLE rooms ADD COLUMN max_image_width integer NOT NULL DEFAULT 2000;
@@ -58,6 +57,13 @@ CREATE INDEX messages_user_created_on_image_only_idx ON messages (user_id, creat
 -- historical image dumps in a room. needs to handle non-image messages.
 CREATE INDEX messages_room_id_created_on_idx ON messages (room_id, created_on);
 
+
+CREATE TABLE image_urls (
+  url text,
+  last_posted timestamp NOT NULL DEFAULT now(),
+  PRIMARY KEY(url)
+);
+
 -- Queries to support:
 -- 1) What messages are tagged x? (ordered by time, popularity)
 -- 2) What did I tag x?
diff --git a/src/site.clj b/src/site.clj
index bb4d2b8..4ff0c45 100644
--- a/src/site.clj
+++ b/src/site.clj
@@ -1002,12 +1002,10 @@ WHERE  u.user_id = ANY(?)"
 
 (defn ghetto-search-query [num-tokens]
   (str "select 
-  content from messages
-  where room_id = 1 
-    and content ilike " (str-join " and content ilike " (take num-tokens (repeat "?"))) "
-    and content like '%http://%'
-  order by message_id desc 
-  limit 250;"))
+  url from image_urls
+  where content ilike " (str-join " and content ilike " (take num-tokens (repeat "?"))) "
+  order by last_posted desc 
+  limit 200;"))
 
 (def *ghetto-search-regex* #"^[A-Za-z0-9\-_.+]*$")
 
-- 
cgit v1.2.3-70-g09d2