2019-10-07 21:43:34 +00:00
|
|
|
//! Helper functions for SQL queries
|
|
|
|
|
2019-10-08 22:46:42 +00:00
|
|
|
/// Query string for SELECT queries
|
|
|
|
#[derive(Deserialize)]
|
|
|
|
pub struct SelectQuery {
|
|
|
|
/// Left creation bounder timestamp
|
2019-10-15 12:16:23 +00:00
|
|
|
pub from: Option<i32>,
|
2019-10-08 22:46:42 +00:00
|
|
|
/// Right creation bounder timestamp
|
2019-10-15 12:16:23 +00:00
|
|
|
pub to: Option<i32>,
|
2019-10-08 22:46:42 +00:00
|
|
|
/// Query size limit
|
|
|
|
pub limit: Option<i64>,
|
|
|
|
/// Whether to sort the results in ascending order
|
|
|
|
pub asc: Option<bool>,
|
|
|
|
}
|
2019-10-07 21:43:34 +00:00
|
|
|
|
2019-10-08 22:46:42 +00:00
|
|
|
/// Filters for SELECT queries
|
|
|
|
pub struct SelectFilters {
|
|
|
|
/// Creation and update date and time ranges
|
2019-10-15 12:16:23 +00:00
|
|
|
pub range: (Option<i32>, Option<i32>),
|
2019-10-08 22:46:42 +00:00
|
|
|
/// Query size limit
|
|
|
|
pub limit: Option<i64>,
|
|
|
|
/// Whether to sort the results in ascending order
|
2019-10-15 12:16:23 +00:00
|
|
|
pub asc: bool,
|
2019-10-08 22:46:42 +00:00
|
|
|
}
|
2019-10-08 16:57:12 +00:00
|
|
|
|
2019-10-08 22:46:42 +00:00
|
|
|
impl From<SelectQuery> for SelectFilters {
|
|
|
|
fn from(query: SelectQuery) -> Self {
|
|
|
|
SelectFilters {
|
2019-10-15 12:16:23 +00:00
|
|
|
range: (query.from, query.to),
|
2019-10-08 22:46:42 +00:00
|
|
|
limit: query.limit,
|
2019-10-15 12:16:23 +00:00
|
|
|
asc: query.asc.unwrap_or(false),
|
2019-10-08 16:57:12 +00:00
|
|
|
}
|
2019-10-08 22:46:42 +00:00
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
/// Code common to all select functions
|
|
|
|
macro_rules! common_select {
|
|
|
|
($q:expr, $f:expr) => {
|
2019-10-15 12:16:23 +00:00
|
|
|
if let Some(from) = $f.range.0 {
|
|
|
|
$q = $q.filter(created.ge(from));
|
2019-10-08 16:57:12 +00:00
|
|
|
}
|
2019-10-15 12:16:23 +00:00
|
|
|
if let Some(to) = $f.range.1 {
|
|
|
|
$q = $q.filter(created.lt(to));
|
2019-10-08 16:57:12 +00:00
|
|
|
}
|
2019-10-08 22:46:42 +00:00
|
|
|
if let Some(limit) = $f.limit {
|
|
|
|
$q = $q.limit(limit);
|
2019-10-08 16:57:12 +00:00
|
|
|
}
|
2019-10-25 23:41:11 +00:00
|
|
|
$q = if $f.asc {
|
|
|
|
$q.order(created.asc())
|
|
|
|
} else {
|
|
|
|
$q.order(created.desc())
|
|
|
|
};
|
2019-10-08 22:46:42 +00:00
|
|
|
};
|
|
|
|
}
|
2019-10-08 16:57:12 +00:00
|
|
|
|
2019-10-08 22:46:42 +00:00
|
|
|
/// SELECT a single entry given its id
|
|
|
|
macro_rules! find {
|
|
|
|
($n:ident, $t:ty) => {
|
2020-01-16 04:47:38 +00:00
|
|
|
pub fn find(f_id: i32) -> diesel::result::QueryResult<$t> {
|
|
|
|
let conn: &SqliteConnection = &crate::globals::POOL.get().unwrap();
|
2019-10-08 22:46:42 +00:00
|
|
|
$n.find(f_id).first::<$t>(conn)
|
2019-10-08 18:17:08 +00:00
|
|
|
}
|
2019-10-08 22:46:42 +00:00
|
|
|
};
|
|
|
|
}
|
2019-10-08 18:17:08 +00:00
|
|
|
|
2019-10-08 22:46:42 +00:00
|
|
|
/// DELETE an entry
|
|
|
|
macro_rules! delete {
|
2020-03-09 19:07:56 +00:00
|
|
|
($n:ident, $t:ty) => {
|
2020-01-16 04:47:38 +00:00
|
|
|
pub fn delete(d_id: i32) -> diesel::result::QueryResult<()> {
|
|
|
|
let conn: &SqliteConnection = &crate::globals::POOL.get().unwrap();
|
2020-03-09 19:07:56 +00:00
|
|
|
diesel::delete(&$n.find(d_id).first::<$t>(conn)?).execute(conn)?;
|
2019-10-08 22:46:42 +00:00
|
|
|
Ok(())
|
|
|
|
}
|
|
|
|
};
|
|
|
|
}
|
2019-10-08 16:57:12 +00:00
|
|
|
|
2019-10-08 22:46:42 +00:00
|
|
|
/// Queries affecting the `files` table
|
|
|
|
pub mod files {
|
2019-10-21 18:29:39 +00:00
|
|
|
use crate::{
|
2020-01-16 06:27:50 +00:00
|
|
|
globals::{CONFIG, POOL},
|
2019-10-21 18:29:39 +00:00
|
|
|
models::files::*,
|
|
|
|
queries::SelectFilters,
|
|
|
|
schema::files::{dsl::*, table},
|
|
|
|
};
|
2020-01-16 06:27:50 +00:00
|
|
|
use diesel::{
|
|
|
|
prelude::*,
|
|
|
|
result::{DatabaseErrorKind, Error, QueryResult},
|
|
|
|
};
|
|
|
|
use std::fs;
|
2019-10-08 22:46:42 +00:00
|
|
|
|
2020-01-16 04:47:38 +00:00
|
|
|
find!(files, File);
|
|
|
|
|
2019-10-08 22:46:42 +00:00
|
|
|
/// SELECT multiple file entries
|
2020-01-16 04:47:38 +00:00
|
|
|
pub fn select(filters: SelectFilters) -> QueryResult<Vec<File>> {
|
|
|
|
let conn: &SqliteConnection = &POOL.get().unwrap();
|
2019-10-08 22:46:42 +00:00
|
|
|
let mut query = files.into_boxed();
|
|
|
|
common_select!(query, filters);
|
|
|
|
query.load::<File>(conn)
|
2019-10-07 21:43:34 +00:00
|
|
|
}
|
|
|
|
|
2020-01-16 06:27:50 +00:00
|
|
|
/// Delete an existing file on disk
|
|
|
|
fn fs_del(fid: i32) -> QueryResult<()> {
|
|
|
|
let mut path = CONFIG.files_dir.clone();
|
|
|
|
path.push(match find(fid) {
|
|
|
|
Ok(f) => f.filepath,
|
|
|
|
Err(e) => {
|
|
|
|
return match e {
|
|
|
|
Error::NotFound => Ok(()),
|
|
|
|
_ => Err(e),
|
|
|
|
}
|
|
|
|
}
|
|
|
|
});
|
|
|
|
if !path.exists() {
|
|
|
|
return Ok(());
|
|
|
|
}
|
|
|
|
|
|
|
|
fs::remove_file(path).map_err(|e| {
|
|
|
|
Error::DatabaseError(
|
|
|
|
DatabaseErrorKind::UnableToSendCommand,
|
|
|
|
Box::new(format!("{}", e)),
|
|
|
|
)
|
|
|
|
})
|
|
|
|
}
|
|
|
|
|
2019-10-09 15:59:52 +00:00
|
|
|
/// REPLACE a file entry
|
2020-01-16 04:47:38 +00:00
|
|
|
pub fn replace(r_id: i32, r_filepath: &str) -> QueryResult<File> {
|
2020-01-16 06:27:50 +00:00
|
|
|
fs_del(r_id)?;
|
|
|
|
|
2020-01-16 04:47:38 +00:00
|
|
|
let conn: &SqliteConnection = &POOL.get().unwrap();
|
2019-10-07 21:43:34 +00:00
|
|
|
let new_file = NewFile {
|
2019-10-09 15:59:52 +00:00
|
|
|
id: r_id,
|
|
|
|
filepath: r_filepath,
|
2019-10-07 21:43:34 +00:00
|
|
|
};
|
2019-10-09 15:59:52 +00:00
|
|
|
diesel::replace_into(table)
|
|
|
|
.values(&new_file)
|
|
|
|
.execute(conn)?;
|
2020-01-16 04:47:38 +00:00
|
|
|
find(r_id)
|
2019-10-07 21:43:34 +00:00
|
|
|
}
|
2020-01-16 06:27:50 +00:00
|
|
|
|
|
|
|
/// DELETE an entry
|
|
|
|
pub fn delete(d_id: i32) -> QueryResult<()> {
|
|
|
|
fs_del(d_id)?;
|
|
|
|
|
|
|
|
let conn: &SqliteConnection = &POOL.get().unwrap();
|
2020-03-09 19:07:56 +00:00
|
|
|
diesel::delete(&files.find(d_id).first::<File>(conn)?).execute(conn)?;
|
2020-01-16 06:27:50 +00:00
|
|
|
Ok(())
|
|
|
|
}
|
2019-10-07 21:43:34 +00:00
|
|
|
}
|
|
|
|
|
|
|
|
/// Queries affecting the `links` table
|
|
|
|
pub mod links {
|
2019-10-21 18:29:39 +00:00
|
|
|
use crate::{
|
2020-01-16 04:47:38 +00:00
|
|
|
globals::POOL,
|
2019-10-21 18:29:39 +00:00
|
|
|
models::links::*,
|
|
|
|
queries::SelectFilters,
|
|
|
|
schema::links::{dsl::*, table},
|
|
|
|
};
|
|
|
|
use diesel::{prelude::*, result::QueryResult};
|
2019-10-07 21:43:34 +00:00
|
|
|
|
2020-01-16 04:47:38 +00:00
|
|
|
find!(links, Link);
|
2020-03-09 19:07:56 +00:00
|
|
|
delete!(links, Link);
|
2020-01-16 04:47:38 +00:00
|
|
|
|
2019-10-08 16:57:12 +00:00
|
|
|
/// SELECT multiple link entries
|
2020-01-16 04:47:38 +00:00
|
|
|
pub fn select(filters: SelectFilters) -> QueryResult<Vec<Link>> {
|
|
|
|
let conn: &SqliteConnection = &POOL.get().unwrap();
|
2019-10-08 16:57:12 +00:00
|
|
|
let mut query = links.into_boxed();
|
2019-10-08 22:46:42 +00:00
|
|
|
common_select!(query, filters);
|
2019-10-08 16:57:12 +00:00
|
|
|
query.load::<Link>(conn)
|
|
|
|
}
|
|
|
|
|
2019-10-09 15:59:52 +00:00
|
|
|
/// REPLACE a link entry
|
2020-01-16 04:47:38 +00:00
|
|
|
pub fn replace(r_id: i32, r_forward: &str) -> QueryResult<Link> {
|
|
|
|
let conn: &SqliteConnection = &POOL.get().unwrap();
|
2019-10-07 21:43:34 +00:00
|
|
|
let new_link = NewLink {
|
2019-10-09 15:59:52 +00:00
|
|
|
id: r_id,
|
|
|
|
forward: r_forward,
|
2019-10-07 21:43:34 +00:00
|
|
|
};
|
2019-10-09 15:59:52 +00:00
|
|
|
diesel::replace_into(table)
|
|
|
|
.values(&new_link)
|
|
|
|
.execute(conn)?;
|
2020-01-16 04:47:38 +00:00
|
|
|
find(r_id)
|
2019-10-07 21:43:34 +00:00
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
/// Queries affecting the `texts` table
|
|
|
|
pub mod texts {
|
2019-10-21 18:29:39 +00:00
|
|
|
use crate::{
|
2020-01-16 04:47:38 +00:00
|
|
|
globals::POOL,
|
2019-10-21 18:29:39 +00:00
|
|
|
models::texts::*,
|
|
|
|
queries::SelectFilters,
|
|
|
|
schema::texts::{dsl::*, table},
|
|
|
|
};
|
|
|
|
use diesel::{prelude::*, result::QueryResult};
|
2019-10-07 21:43:34 +00:00
|
|
|
|
2020-01-16 04:47:38 +00:00
|
|
|
find!(texts, Text);
|
2020-03-09 19:07:56 +00:00
|
|
|
delete!(texts, Text);
|
2020-01-16 04:47:38 +00:00
|
|
|
|
2019-10-08 16:57:12 +00:00
|
|
|
/// SELECT multiple text entries
|
2020-01-16 04:47:38 +00:00
|
|
|
pub fn select(filters: SelectFilters) -> QueryResult<Vec<Text>> {
|
|
|
|
let conn: &SqliteConnection = &POOL.get().unwrap();
|
2019-10-08 16:57:12 +00:00
|
|
|
let mut query = texts.into_boxed();
|
2019-10-08 22:46:42 +00:00
|
|
|
common_select!(query, filters);
|
2019-10-08 16:57:12 +00:00
|
|
|
query.load::<Text>(conn)
|
|
|
|
}
|
|
|
|
|
2019-10-09 15:59:52 +00:00
|
|
|
/// REPLACE a text entry
|
2020-01-16 04:47:38 +00:00
|
|
|
pub fn replace(r_id: i32, r_contents: &str, r_highlight: bool) -> QueryResult<Text> {
|
|
|
|
let conn: &SqliteConnection = &POOL.get().unwrap();
|
2019-10-07 21:43:34 +00:00
|
|
|
let new_text = NewText {
|
2019-10-09 15:59:52 +00:00
|
|
|
id: r_id,
|
|
|
|
contents: r_contents,
|
2020-01-15 06:50:54 +00:00
|
|
|
highlight: r_highlight,
|
2019-10-07 21:43:34 +00:00
|
|
|
};
|
2019-10-09 15:59:52 +00:00
|
|
|
diesel::replace_into(table)
|
|
|
|
.values(&new_text)
|
|
|
|
.execute(conn)?;
|
2020-01-16 04:47:38 +00:00
|
|
|
find(r_id)
|
2019-10-07 21:43:34 +00:00
|
|
|
}
|
|
|
|
}
|