1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
use std::time::{Duration, SystemTime, UNIX_EPOCH};
use rusqlite::Row;
use tracing::instrument;
use crate::{database, types::comment::Comment};
pub fn init() -> Result<(), rusqlite::Error> {
let sql = "
CREATE TABLE IF NOT EXISTS comments (
comment_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
post_id INTEGER NOT NULL,
date INTEGER NOT NULL,
content VARCHAR(255) NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(user_id),
FOREIGN KEY(post_id) REFERENCES posts(post_id)
);
";
let conn = database::connect()?;
conn.execute(sql, ())?;
let sql2 = "CREATE INDEX IF NOT EXISTS post_ids on comments (post_id);";
conn.execute(sql2, ())?;
Ok(())
}
fn comment_from_row(row: &Row) -> Result<Comment, rusqlite::Error> {
let comment_id = row.get(0)?;
let user_id = row.get(1)?;
let post_id = row.get(2)?;
let date = row.get(3)?;
let content = row.get(4)?;
Ok(Comment {
comment_id,
user_id,
post_id,
date,
content,
})
}
#[instrument()]
pub fn get_comments_page(page: u64, post_id: u64) -> Result<Vec<Comment>, rusqlite::Error> {
tracing::trace!("Retrieving comments page");
let page_size = 5;
let conn = database::connect()?;
let mut stmt = conn.prepare(
"SELECT * FROM comments WHERE post_id = ? ORDER BY comment_id ASC LIMIT ? OFFSET ?",
)?;
let row = stmt.query_map([post_id, page_size, page_size * page], |row| {
let row = comment_from_row(row)?;
Ok(row)
})?;
Ok(row.into_iter().flatten().collect())
}
#[instrument()]
pub fn get_all_comments() -> Result<Vec<Comment>, rusqlite::Error> {
tracing::trace!("Retrieving comments page");
let conn = database::connect()?;
let mut stmt = conn.prepare("SELECT * FROM comments ORDER BY comment_id DESC")?;
let row = stmt.query_map([], |row| {
let row = comment_from_row(row)?;
Ok(row)
})?;
Ok(row.into_iter().flatten().collect())
}
#[instrument()]
pub fn add_comment(user_id: u64, post_id: u64, content: &str) -> Result<Comment, rusqlite::Error> {
tracing::trace!("Adding comment");
let date = u64::try_from(
SystemTime::now()
.duration_since(UNIX_EPOCH)
.unwrap_or(Duration::ZERO)
.as_millis(),
)
.unwrap_or(0);
let conn = database::connect()?;
let mut stmt = conn.prepare(
"INSERT INTO comments (user_id, post_id, date, content) VALUES(?,?,?,?) RETURNING *;",
)?;
let post = stmt.query_row((user_id, post_id, date, content), |row| {
let row = comment_from_row(row)?;
Ok(row)
})?;
Ok(post)
}
|
