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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
use std::time::{Duration, SystemTime, UNIX_EPOCH};
use rusqlite::{OptionalExtension, Row};
use tracing::instrument;
use crate::types::post::Post;
use super::Database;
impl Database {
pub fn init_posts(&self) -> Result<(), rusqlite::Error> {
let sql = "
CREATE TABLE IF NOT EXISTS posts (
post_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
content VARCHAR(5000) NOT NULL,
date INTEGER NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(user_id)
);
";
self.0.execute(sql, ())?;
Ok(())
}
fn post_from_row(&self, row: &Row) -> Result<Post, rusqlite::Error> {
let post_id = row.get(0)?;
let user_id = row.get(1)?;
let content = row.get(2)?;
let date = row.get(3)?;
let comments = self
.get_comments_page(0, post_id)
.unwrap_or_else(|_| Vec::new());
let likes = self.get_like_count(post_id).unwrap_or(None).unwrap_or(0);
Ok(Post {
post_id,
user_id,
content,
date,
likes,
liked: false,
comments,
})
}
#[instrument(skip(self))]
pub fn get_post(&self, post_id: u64) -> Result<Option<Post>, rusqlite::Error> {
tracing::trace!("Retrieving post");
let mut stmt = self.0.prepare("SELECT * FROM posts WHERE post_id = ?")?;
let row = stmt
.query_row([post_id], |row| {
let row = self.post_from_row(row)?;
Ok(row)
})
.optional()?;
Ok(row)
}
#[instrument(skip(self))]
pub fn get_post_page(&self, page: u64) -> Result<Vec<Post>, rusqlite::Error> {
tracing::trace!("Retrieving posts page");
let page_size = 10;
let mut stmt = self
.0
.prepare("SELECT * FROM posts ORDER BY post_id DESC LIMIT ? OFFSET ?")?;
let row = stmt.query_map([page_size, page_size * page], |row| {
let row = self.post_from_row(row)?;
Ok(row)
})?;
Ok(row.into_iter().flatten().collect())
}
#[instrument(skip(self))]
pub fn get_all_posts(&self) -> Result<Vec<Post>, rusqlite::Error> {
tracing::trace!("Retrieving posts page");
let mut stmt = self
.0
.prepare("SELECT * FROM posts ORDER BY post_id DESC")?;
let row = stmt.query_map([], |row| {
let row = self.post_from_row(row)?;
Ok(row)
})?;
Ok(row.into_iter().flatten().collect())
}
#[instrument(skip(self))]
pub fn get_users_post_page(
&self,
user_id: u64,
page: u64,
) -> Result<Vec<Post>, rusqlite::Error> {
tracing::trace!("Retrieving users posts");
let page_size = 10;
let mut stmt = self.0.prepare(
"SELECT * FROM posts WHERE user_id = ? ORDER BY post_id DESC LIMIT ? OFFSET ?",
)?;
let row = stmt.query_map([user_id, page_size, page_size * page], |row| {
let row = self.post_from_row(row)?;
Ok(row)
})?;
Ok(row.into_iter().flatten().collect())
}
#[instrument(skip(self))]
pub fn add_post(&self, user_id: u64, content: &str) -> Result<Post, rusqlite::Error> {
tracing::trace!("Adding post");
let date = u64::try_from(
SystemTime::now()
.duration_since(UNIX_EPOCH)
.unwrap_or(Duration::ZERO)
.as_millis(),
)
.unwrap_or(0);
let mut stmt = self
.0
.prepare("INSERT INTO posts (user_id, content, date) VALUES(?,?,?) RETURNING *;")?;
let post = stmt.query_row((user_id, content, date), |row| {
let row = self.post_from_row(row)?;
Ok(row)
})?;
Ok(post)
}
}
|
