diff options
| -rw-r--r-- | db/0-create.psql | 8 | ||||
| -rw-r--r-- | 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\-_.+]*$")
|