write meme `query` command

5 files changed, 137 insertions, 5 deletions

diff --git a/src/commands/meme/history.rs b/src/commands/meme/history.rs
index ea79ecc..dd88320 100644
--- a/src/commands/meme/history.rs
+++ b/src/commands/meme/history.rs
@@ -234,4 +234,86 @@ pub fn memers(_: &mut Context, msg: &Message, _args: Args) -> Result<()> {
         .join("\n");
 
     send(msg.channel_id, &s, msg.tts)
+}
+
+pub fn query(_: &mut Context, msg: &Message, mut args: Args) -> Result<()> {
+    use std::borrow::Borrow;
+
+    use itertools::Itertools;
+    use regex::Regex;
+    use failure::err_msg;
+    use serenity::model::id::UserId;
+
+    use crate::{
+        game::get_user_id,
+        db,
+        TARGET_GUILD_ID,
+    };
+
+    lazy_static! {
+        static ref CREATOR_REGEX: Regex = Regex::new(r"(?i)(?:by|creator)=(.*)").unwrap();
+        static ref AGE_REGEX: Regex = Regex::new(r"(?i)(?:age|order)=(.*)").unwrap();
+    }
+
+    let guild = msg.channel_id.to_channel()?
+        .guild()
+        .ok_or(err_msg("couldn't find guild"))?;
+
+    let guild = guild.read()
+        .guild()
+        .ok_or(err_msg("couldn't find guild"))?;
+
+    let guild = guild
+        .read();
+
+    let creator: Option<u64> = {
+        let creator = args.current_quoted().map(|s| CREATOR_REGEX.is_match(s)).unwrap_or(false);
+        if creator {
+            args.single_quoted::<String>()
+                .ok()
+                .and_then(|s| CREATOR_REGEX.captures(&s).and_then(|c| c.get(1)).map(|x| x.as_str().to_owned()))
+                .and_then(|s| get_user_id(guild.borrow(), s).ok().map(|s| s.0))
+        } else {
+            None
+        }
+    };
+
+    let order = {
+        let order = args.current_quoted().map(|s| AGE_REGEX.is_match(s)).unwrap_or(false);
+
+        if order {
+            args.single_quoted::<String>().ok()
+                .and_then(|s| AGE_REGEX.captures(&s).and_then(|c| c.get(1)).map(|x| x.as_str().to_owned()))
+                .map(|s| s.contains("new"))
+                .unwrap_or(true)
+        } else {
+            true
+        }
+    };
+
+    let result = db::query_meme(args.rest(), creator, order)?
+        .into_iter()
+        .map(|(meme, metadata)| {
+            let user = UserId(metadata.created_by as u64).to_user()?;
+            let username = user.nick_in(*TARGET_GUILD_ID).unwrap_or(user.name);
+
+            Ok(format!("*{}* by **{}** ({}). text length: **{}**, image: **{}**, audio: **{}**",
+                       meme.title,
+                       username,
+                       metadata.created.date().format(CLEAN_DATE_FORMAT),
+                       meme.content.map_or(0, |s| s.len()),
+                       meme.image_id.map_or("NO", |_s| "YES"),
+                       meme.audio_id.map_or("NO", |_s| "YES"),
+            ))
+        })
+        .collect::<Result<Vec<_>>>()?
+        .into_iter()
+        .join("\n");
+
+    if result.len() == 0 {
+        info!("no memes matched query");
+        return send(msg.channel_id, "no match".to_owned(), msg.tts);
+    }
+
+    send(msg.channel_id, &result, msg.tts)
 }
\ No newline at end of file
diff --git a/src/commands/mod.rs b/src/commands/mod.rs
index 3ceb15f..c315278 100644
--- a/src/commands/mod.rs
+++ b/src/commands/mod.rs
@@ -139,6 +139,11 @@ fn register_db(f: StandardFramework) -> StandardFramework {
             .desc("list stats for all server memers")
             .cmd(memers)
         )
+        .command("query", |c| c
+            .guild_only(true)
+            .desc("find a lot of matching memes")
+            .cmd(query)
+        )
 }
 
 #[cfg(not(feature = "diesel"))]
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 6a71dd5..8702099 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -67,6 +67,51 @@ pub fn find_meme<T: AsRef<str>>(conn: &PgConnection, search: T) -> Result<Meme>
         .map_err(Error::from)
 }
 
+pub fn query_meme<T: AsRef<str>>(search: T, user_id: Option<u64>, age_desc: bool) -> Result<Vec<(Meme, Metadata)>> {
+    let raw_conn = raw_connection()?;
+
+    let search = format!("%{}%", search.as_ref());
+
+    let rows = raw_conn.query(&format!(r#"
+    SELECT memes.id, title, content, image_id, audio_id, metadata_id, created, created_by
+    FROM memes
+    INNER JOIN metadata ON memes.metadata_id = metadata.id
+    WHERE (memes.title ILIKE $1 OR memes.content ILIKE $1)
+              AND (metadata.created_by = $2 OR $3)
+    ORDER BY metadata.created {}
+    LIMIT 100
+    "#,
+        if age_desc { "DESC" } else { "ASC" },
+    ), &[
+        &search,
+        &(user_id.unwrap_or(0) as i64),
+        &user_id.is_none(),
+    ])?;
+
+    let result = rows.iter()
+        .map(|row| {
+            let meme = Meme {
+                id: row.get(0),
+                title: row.get(1),
+                content: row.get(2),
+                image_id: row.get(3),
+                audio_id: row.get(4),
+                metadata_id: row.get(5),
+            };
+
+            let metadata = Metadata {
+                id: row.get(5),
+                created: row.get(6),
+                created_by: row.get(7),
+            };
+
+            (meme, metadata)
+        })
+        .collect();
+
+    Ok(result)
+}
+
 pub fn delete_meme<T: AsRef<str>>(conn: &PgConnection, search: T, deleted_by: u64) -> Result<()> {
     conn.transaction::<(), Error, _>(|| {
         let deleted = memes::table
diff --git a/src/db/models.rs b/src/db/models.rs
index 66e161a..a01a9c1 100644
--- a/src/db/models.rs
+++ b/src/db/models.rs
@@ -7,7 +7,7 @@ use crate::{
     Result,
 };
 
-#[derive(Queryable, Identifiable, PartialEq, Debug)]
+#[derive(Queryable, Identifiable, PartialEq, Debug, Clone)]
 #[table_name="memes"]
 pub struct Meme {
     pub id: i32,
@@ -32,7 +32,7 @@ impl Meme {
     }
 }
 
-#[derive(Insertable, PartialEq, Debug)]
+#[derive(Insertable, PartialEq, Debug, Clone)]
 #[table_name="memes"]
 pub struct NewMeme {
     pub title: String,
@@ -157,7 +157,7 @@ pub struct NewImage {
 }
 
 
-#[derive(Queryable, Identifiable, PartialEq, Debug)]
+#[derive(Queryable, Identifiable, PartialEq, Debug, Clone)]
 #[table_name="metadata"]
 pub struct Metadata {
     pub id: i32,
diff --git a/src/game.rs b/src/game.rs
index 3a51a29..368831b 100644
--- a/src/game.rs
+++ b/src/game.rs
@@ -153,7 +153,7 @@ fn ownedgame(ctx: &mut Context, msg: &Message, args: Args) -> Result<()> {
 }
 
 #[derive(Copy, Clone, Debug, Fail, PartialEq, Eq, Hash)]
-enum UserLookupError {
+pub enum UserLookupError {
     #[fail(display = "too many possible options ({}) for query", _0)]
     Ambiguous(usize),
 
@@ -161,7 +161,7 @@ enum UserLookupError {
     NotFound,
 }
 
-fn get_user_id<S: AsRef<str>>(g: &Guild, s: S) -> StdResult<UserId, UserLookupError> {
+pub fn get_user_id<S: AsRef<str>>(g: &Guild, s: S) -> StdResult<UserId, UserLookupError> {
     let s = s.as_ref().trim_start_matches("@").to_lowercase();
 
     if let Some(info) = USER_INFO_MAP.get(&s) {