diff options
Diffstat (limited to 'src/db/mod.rs')
| -rw-r--r-- | src/db/mod.rs | 315 |
1 files changed, 171 insertions, 144 deletions
diff --git a/src/db/mod.rs b/src/db/mod.rs index 1ac44e9..94953d5 100644 --- a/src/db/mod.rs +++ b/src/db/mod.rs @@ -10,46 +10,53 @@ use chrono::{ Utc, }; use diesel::{ - NotFound, prelude::*, - r2d2::{ConnectionManager, ManageConnection}, + r2d2::{ + ConnectionManager, + ManageConnection, + }, + NotFound, }; use postgres::Client as RawPgConn; use r2d2_postgres::{ - PostgresConnectionManager as RawPgConnMgr, postgres::{ - NoTls, Config, + NoTls, }, + PostgresConnectionManager as RawPgConnMgr, }; use anyhow::anyhow; -use lazy_static::lazy_static; use diesel_migrations::MigrationHarness; +use lazy_static::lazy_static; -use crate::{Error, Result}; +use crate::{ + Error, + Result, +}; pub use self::models::*; use self::schema::*; -mod schema; mod models; +mod schema; const MIGRATIONS: diesel_migrations::EmbeddedMigrations = diesel_migrations::embed_migrations!(); static MIGRATE: std::sync::Once = std::sync::Once::new(); lazy_static! { - static ref DB_URL: String = env::var("DATABASE_URL").expect("no database url in environment").into(); + static ref DB_URL: String = + env::var("DATABASE_URL").expect("no database url in environment").into(); static ref DB_CONFIG: Config = Config::from_str(&DB_URL).expect("parsing db url as config"); static ref CONN_MGR: ConnectionManager<PgConnection> = ConnectionManager::new(DB_URL.clone()); static ref RAW_CONN_MGR: RawPgConnMgr<NoTls> = RawPgConnMgr::new(DB_CONFIG.clone(), NoTls); } - #[inline] pub fn connection() -> Result<PgConnection> { - CONN_MGR.connect() + CONN_MGR + .connect() .map(|mut conn| { MIGRATE.call_once(|| { log::info!("running migrations"); @@ -69,17 +76,13 @@ fn raw_connection() -> Result<RawPgConn> { connection()?; } - RAW_CONN_MGR.connect() - .map_err(Error::from) + RAW_CONN_MGR.connect().map_err(Error::from) } pub fn find_meme<T: AsRef<str>>(conn: &mut PgConnection, search: T) -> Result<Meme> { let search = search.as_ref(); - let mut meme = memes::table - .filter(memes::title.eq(search)) - .limit(1) - .first::<Meme>(conn); + let mut meme = memes::table.filter(memes::title.eq(search)).limit(1).first::<Meme>(conn); if let Err(NotFound) = meme { let format_search = format!("%{}%", search); @@ -90,16 +93,21 @@ pub fn find_meme<T: AsRef<str>>(conn: &mut PgConnection, search: T) -> Result<Me .first::<Meme>(conn); } - meme - .map_err(Error::from) + 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)>> { +pub fn query_meme<T: AsRef<str>>( + search: T, + user_id: Option<u64>, + age_desc: bool, +) -> Result<Vec<(Meme, Metadata)>> { let mut raw_conn = raw_connection()?; let search = format!("%{}%", search.as_ref()); - let rows = raw_conn.query(&format!(r#" + 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 @@ -108,27 +116,30 @@ pub fn query_meme<T: AsRef<str>>(search: T, user_id: Option<u64>, age_desc: bool ORDER BY metadata.created {} LIMIT 100 "#, - if age_desc { "DESC" } else { "ASC" }, - ), &[ - &search, - &(user_id.unwrap_or(0) as i64), - &user_id.is_none(), - ])?; + if age_desc { + "DESC" + } else { + "ASC" + }, + ), + &[&search, &(user_id.unwrap_or(0) as i64), &user_id.is_none()], + )?; - let result = rows.iter() + 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), + 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), + id: row.get(5), + created: row.get(6), created_by: row.get(7), }; @@ -139,26 +150,21 @@ pub fn query_meme<T: AsRef<str>>(search: T, user_id: Option<u64>, age_desc: bool Ok(result) } -pub fn delete_meme<T: AsRef<str>>(conn: &mut PgConnection, search: T, deleted_by: u64) -> Result<()> { +pub fn delete_meme<T: AsRef<str>>( + conn: &mut PgConnection, + search: T, + deleted_by: u64, +) -> Result<()> { conn.transaction::<(), Error, _>(|tx| { - let deleted = memes::table - .filter(memes::title.eq(search.as_ref())) - .first::<Meme>(tx)?; + let deleted = memes::table.filter(memes::title.eq(search.as_ref())).first::<Meme>(tx)?; - ::diesel::delete(memes::table) - .filter(memes::id.eq(deleted.id)) - .execute(tx)?; + diesel::delete(memes::table).filter(memes::id.eq(deleted.id)).execute(tx)?; if let Some(image_id) = deleted.image_id { - let count = memes::table - .filter(memes::image_id.eq(image_id)) - .count() - .execute(tx)?; + let count = memes::table.filter(memes::image_id.eq(image_id)).count().execute(tx)?; if count == 0 { - ::diesel::delete(images::table) - .filter(images::id.eq(image_id)) - .execute(tx)?; + diesel::delete(images::table).filter(images::id.eq(image_id)).execute(tx)?; } } @@ -169,21 +175,17 @@ pub fn delete_meme<T: AsRef<str>>(conn: &mut PgConnection, search: T, deleted_by .execute(tx)?; if count == 0 { - ::diesel::delete(audio::table) - .filter(audio::id.eq(audio_id)) - .execute(tx)?; + diesel::delete(audio::table).filter(audio::id.eq(audio_id)).execute(tx)?; } } let tombstone = NewTombstone { - deleted_by: deleted_by as i64, + deleted_by: deleted_by as i64, metadata_id: deleted.metadata_id, - meme_id: deleted.id, + meme_id: deleted.id, }; - let _ = ::diesel::insert_into(tombstones::table) - .values(&tombstone) - .execute(tx)?; + let _ = diesel::insert_into(tombstones::table).values(&tombstone).execute(tx)?; Ok(()) }) @@ -194,7 +196,8 @@ pub fn rare_meme(conn: &mut PgConnection, audio: bool) -> Result<Meme> { let mut raw_conn = raw_connection()?; - let rows = raw_conn.query(r#" + let rows = raw_conn.query( + r#" WITH meme_count AS ( SELECT @@ -224,9 +227,12 @@ pub fn rare_meme(conn: &mut PgConnection, audio: bool) -> Result<Meme> { sum(play_prop) OVER (ORDER BY play_prop DESC) as play_prop FROM least_used LIMIT 100; - "#, &[&!audio, &audio])?; + "#, + &[&!audio, &audio], + )?; - let elems = rows.iter() + let elems = rows + .iter() .map(|row| (row.get::<_, i32>(0), row.get::<_, f64>(1) as i64)) .collect::<Vec<_>>(); @@ -237,7 +243,8 @@ pub fn rare_meme(conn: &mut PgConnection, audio: bool) -> Result<Meme> { let mut rng = thread_rng(); let target_prob = rng.gen_range(0..elems.last().unwrap().1); - let meme_id = elems.into_iter() + let meme_id = elems + .into_iter() .find(|(_, x)| target_prob < *x) .ok_or_else(|| anyhow!("couldn't locate meme satisfying target probability"))? .0; @@ -246,36 +253,40 @@ pub fn rare_meme(conn: &mut PgConnection, audio: bool) -> Result<Meme> { } pub fn rand_meme(conn: &mut PgConnection, audio: bool) -> Result<Meme> { - use rand::{thread_rng, seq::SliceRandom}; + use rand::{ + seq::SliceRandom, + thread_rng, + }; let ids: Vec<i32> = if audio { memes::table .select(memes::id) - .filter(memes::content.is_not_null() - .or(memes::image_id.is_not_null()) - .or(memes::audio_id.is_not_null())) + .filter( + memes::content + .is_not_null() + .or(memes::image_id.is_not_null()) + .or(memes::audio_id.is_not_null()), + ) .load(conn) .map_err(Error::from)? } else { memes::table .select(memes::id) - .filter(memes::content.is_not_null() - .or(memes::image_id.is_not_null())) + .filter(memes::content.is_not_null().or(memes::image_id.is_not_null())) .load(conn) .map_err(Error::from)? }; - let id = ids.choose(&mut thread_rng()) - .ok_or_else(|| anyhow!("couldn't load meme"))?; + let id = ids.choose(&mut thread_rng()).ok_or_else(|| anyhow!("couldn't load meme"))?; - memes::table - .find(id) - .first::<Meme>(conn) - .map_err(Error::from) + memes::table.find(id).first::<Meme>(conn).map_err(Error::from) } pub fn rand_audio_meme(conn: &mut PgConnection) -> Result<Meme> { - use rand::{thread_rng, seq::SliceRandom}; + use rand::{ + seq::SliceRandom, + thread_rng, + }; let ids: Vec<i32> = memes::table .select(memes::id) @@ -283,17 +294,16 @@ pub fn rand_audio_meme(conn: &mut PgConnection) -> Result<Meme> { .load(conn) .map_err(Error::from)?; - let id = ids.choose(&mut thread_rng()) - .ok_or_else(|| anyhow!("couldn't load audio meme"))?; + let id = ids.choose(&mut thread_rng()).ok_or_else(|| anyhow!("couldn't load audio meme"))?; - memes::table - .find(id) - .first::<Meme>(conn) - .map_err(Error::from) + memes::table.find(id).first::<Meme>(conn).map_err(Error::from) } pub fn rand_silent_meme(conn: &mut PgConnection) -> Result<Meme> { - use rand::{thread_rng, seq::SliceRandom}; + use rand::{ + seq::SliceRandom, + thread_rng, + }; let ids: Vec<i32> = memes::table .select(memes::id) @@ -301,67 +311,63 @@ pub fn rand_silent_meme(conn: &mut PgConnection) -> Result<Meme> { .load(conn) .map_err(Error::from)?; - let id = ids.choose(&mut thread_rng()) - .ok_or_else(|| anyhow!("couldn't load audio meme"))?; + let id = ids.choose(&mut thread_rng()).ok_or_else(|| anyhow!("couldn't load audio meme"))?; - memes::table - .find(id) - .first::<Meme>(conn) - .map_err(Error::from) + memes::table.find(id).first::<Meme>(conn).map_err(Error::from) } #[derive(Debug, Clone)] pub struct Stats { - pub memes_overall: usize, - pub audio_memes: usize, - pub image_memes: usize, - pub started_recording: DateTime<Utc>, - pub total_meme_invocations: usize, - pub audio_meme_invocations: usize, + pub memes_overall: usize, + pub audio_memes: usize, + pub image_memes: usize, + pub started_recording: DateTime<Utc>, + pub total_meme_invocations: usize, + pub audio_meme_invocations: usize, pub random_meme_invocations: usize, - pub most_active_day: Date<Utc>, + pub most_active_day: Date<Utc>, pub most_active_day_count: usize, - pub most_audio_active_day: Date<Utc>, + pub most_audio_active_day: Date<Utc>, pub most_audio_active_count: usize, - pub most_random_meme_user: u64, - pub most_random_meme_user_count: usize, - pub most_directly_named_meme_user: u64, + pub most_random_meme_user: u64, + pub most_random_meme_user_count: usize, + pub most_directly_named_meme_user: u64, pub most_directly_named_meme_count: usize, - pub most_popular_named_meme: String, + pub most_popular_named_meme: String, pub most_popular_named_meme_count: usize, - pub most_popular_random_meme: String, + pub most_popular_random_meme: String, pub most_popular_random_meme_count: usize, - pub most_popular_meme_overall: String, + pub most_popular_meme_overall: String, pub most_popular_meme_overall_count: usize, } pub fn stats(conn: &mut PgConnection) -> Result<Stats> { - use diesel::dsl::{count_star, count}; use chrono::{ - NaiveDateTime, NaiveDate, + NaiveDateTime, + }; + use diesel::dsl::{ + count, + count_star, }; #[inline] fn to_utc(ndt: NaiveDateTime) -> DateTime<Utc> { - DateTime::from_utc(ndt, Utc{}) + DateTime::from_utc(ndt, Utc {}) } #[inline] fn to_utc_date(nd: NaiveDate) -> Date<Utc> { - Date::from_utc(nd, Utc{}) + Date::from_utc(nd, Utc {}) } - let total_count: i64 = memes::table - .select(count_star()) - .first(conn) - .map_err(Error::from)?; + let total_count: i64 = memes::table.select(count_star()).first(conn).map_err(Error::from)?; let image_count: i64 = memes::table .select(count(memes::image_id)) @@ -383,10 +389,8 @@ pub fn stats(conn: &mut PgConnection) -> Result<Stats> { let started_recording = to_utc(started_recording); - let total_meme_invocations: i64 = invocation_records::table - .select(count_star()) - .first(conn) - .map_err(Error::from)?; + let total_meme_invocations: i64 = + invocation_records::table.select(count_star()).first(conn).map_err(Error::from)?; let audio_meme_invocations: i64 = invocation_records::table .inner_join(memes::table) @@ -403,81 +407,102 @@ pub fn stats(conn: &mut PgConnection) -> Result<Stats> { let mut raw_conn = raw_connection()?; - let row = raw_conn.query_one(r#" + let row = raw_conn.query_one( + r#" SELECT DATE(time) as dt, COUNT(*) FROM invocation_records GROUP BY dt ORDER BY COUNT(*) DESC LIMIT 1; - "#, &[])?; + "#, + &[], + )?; let most_active_day = to_utc_date(row.get(0)); let most_active_day_count: i64 = row.get(1); - let row = raw_conn.query_one(r#" + let row = raw_conn.query_one( + r#" SELECT DATE(time) as dt, COUNT(*) FROM invocation_records INNER JOIN memes ON invocation_records.meme_id = memes.id WHERE memes.audio_id IS NOT NULL GROUP BY dt ORDER BY COUNT(*) DESC LIMIT 1; - "#, &[])?; + "#, + &[], + )?; let most_active_audio_day = to_utc_date(row.get(0)); let most_active_audio_day_count: i64 = row.get(1); - let row = raw_conn.query_one(r#" + let row = raw_conn.query_one( + r#" SELECT user_id, COUNT(*) FROM invocation_records WHERE random IS TRUE GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 1; - "#, &[])?; + "#, + &[], + )?; let most_random_invoker: i64 = row.get(0); let most_random_invoker_count: i64 = row.get(1); - let row = raw_conn.query_one(r#" + let row = raw_conn.query_one( + r#" SELECT user_id, COUNT(*) FROM invocation_records WHERE random IS FALSE GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 1; - "#, &[])?; + "#, + &[], + )?; let most_specific_invoker: i64 = row.get(0); let most_specific_invoker_count: i64 = row.get(1); - let row = raw_conn.query_one(r#" + let row = raw_conn.query_one( + r#" SELECT memes.title, COUNT(*) FROM invocation_records INNER JOIN memes ON meme_id = memes.id WHERE random IS FALSE GROUP BY memes.title ORDER BY COUNT(*) DESC LIMIT 1; - "#, &[])?; + "#, + &[], + )?; let most_requested_meme = row.get(0); let most_requested_meme_count: i64 = row.get(1); - let row = raw_conn.query_one(r#" + let row = raw_conn.query_one( + r#" SELECT memes.title, COUNT(*) FROM invocation_records INNER JOIN memes ON meme_id = memes.id WHERE random IS TRUE GROUP BY memes.title ORDER BY COUNT(*) DESC LIMIT 1; - "#, &[])?; + "#, + &[], + )?; let most_random_meme = row.get(0); let most_random_meme_count: i64 = row.get(1); - let row = raw_conn.query_one(r#" + let row = raw_conn.query_one( + r#" SELECT memes.title, COUNT(*) FROM invocation_records INNER JOIN memes ON meme_id = memes.id GROUP BY memes.title ORDER BY COUNT(*) DESC LIMIT 1; - "#, &[])?; + "#, + &[], + )?; let most_invoked_meme = row.get(0); let most_invoked_meme_count: i64 = row.get(1); @@ -514,10 +539,10 @@ pub fn stats(conn: &mut PgConnection) -> Result<Stats> { #[derive(Clone, Debug, Hash, PartialEq, Eq, Default)] pub struct MemerInfo { - pub user_id: u64, - pub random_memes: usize, - pub specific_memes: usize, - pub most_used_meme: String, + pub user_id: u64, + pub random_memes: usize, + pub specific_memes: usize, + pub most_used_meme: String, pub most_used_meme_count: usize, } @@ -563,21 +588,23 @@ pub fn memers() -> Result<Vec<MemerInfo>> { ORDER BY (random_count.count + specific_count.count) DESC "#, &[])?; - let result = rows.iter().map(|row| { - let user_id: i64 = row.get(0); - let random_count: i64 = row.get(1); - let specific_count: i64 = row.get(2); - let most_memed_meme: String = row.get(3); - let most_memed_count: i64 = row.get(4); + let result = rows + .iter() + .map(|row| { + let user_id: i64 = row.get(0); + let random_count: i64 = row.get(1); + let specific_count: i64 = row.get(2); + let most_memed_meme: String = row.get(3); + let most_memed_count: i64 = row.get(4); - MemerInfo { - user_id: user_id as u64, - random_memes: random_count as usize, - specific_memes: specific_count as usize, - most_used_meme: most_memed_meme, - most_used_meme_count: most_memed_count as usize, - } - }) + MemerInfo { + user_id: user_id as u64, + random_memes: random_count as usize, + specific_memes: specific_count as usize, + most_used_meme: most_memed_meme, + most_used_meme_count: most_memed_count as usize, + } + }) .collect(); Ok(result) |