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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
use std::collections::HashSet;
use std::time::{Duration, SystemTime, UNIX_EPOCH};
use rusqlite::{OptionalExtension, Row};
use tracing::instrument;
use crate::database;
use crate::types::post::Post;
pub fn init() -> Result<(), rusqlite::Error> {
let sql = "
CREATE TABLE IF NOT EXISTS posts (
post_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
content VARCHAR(500) NOT NULL,
likes TEXT NOT NULL,
comments TEXT NOT NULL,
date INTEGER NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(user_id)
);
";
let conn = database::connect()?;
conn.execute(sql, ())?;
Ok(())
}
fn post_from_row(row: &Row) -> Result<Post, rusqlite::Error> {
let post_id = row.get(0)?;
let user_id = row.get(1)?;
let content = row.get(2)?;
let likes_json: String = row.get(3)?;
let comments_json: String = row.get(4)?;
let date = row.get(5)?;
let Ok(likes) = serde_json::from_str(&likes_json) else {
return Err(rusqlite::Error::InvalidQuery)
};
let Ok(comments) = serde_json::from_str(&comments_json) else {
return Err(rusqlite::Error::InvalidQuery)
};
Ok(Post {
post_id,
user_id,
content,
likes,
comments,
date,
})
}
#[instrument()]
pub fn get_post(post_id: u64) -> Result<Option<Post>, rusqlite::Error> {
tracing::trace!("Retrieving post");
let conn = database::connect()?;
let mut stmt = conn.prepare("SELECT * FROM posts WHERE post_id = ?")?;
let row = stmt
.query_row([post_id], |row| {
let row = post_from_row(row)?;
Ok(row)
})
.optional()?;
Ok(row)
}
#[instrument()]
pub fn get_post_page(page: u64) -> Result<Vec<Post>, rusqlite::Error> {
tracing::trace!("Retrieving posts page");
let page_size = 10;
let conn = database::connect()?;
let mut stmt = conn.prepare("SELECT * FROM posts ORDER BY post_id DESC LIMIT ? OFFSET ?")?;
let row = stmt.query_map([page_size, page_size * page], |row| {
let row = post_from_row(row)?;
Ok(row)
})?;
Ok(row.into_iter().flatten().collect())
}
#[instrument()]
pub fn get_all_posts() -> Result<Vec<Post>, rusqlite::Error> {
tracing::trace!("Retrieving posts page");
let conn = database::connect()?;
let mut stmt = conn.prepare("SELECT * FROM posts ORDER BY post_id DESC")?;
let row = stmt.query_map([], |row| {
let row = post_from_row(row)?;
Ok(row)
})?;
Ok(row.into_iter().flatten().collect())
}
#[instrument()]
pub fn get_users_post_page(user_id: u64, page: u64) -> Result<Vec<Post>, rusqlite::Error> {
tracing::trace!("Retrieving users posts");
let page_size = 10;
let conn = database::connect()?;
let mut stmt = conn
.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 = post_from_row(row)?;
Ok(row)
})?;
Ok(row.into_iter().flatten().collect())
}
#[instrument()]
pub fn add_post(user_id: u64, content: &str) -> Result<Post, rusqlite::Error> {
tracing::trace!("Adding post");
let likes: HashSet<u64> = HashSet::new();
let comments: Vec<(u64, String)> = Vec::new();
let Ok(likes_json) = serde_json::to_string(&likes) else {
return Err(rusqlite::Error::InvalidQuery)
};
let Ok(comments_json) = serde_json::to_string(&comments) else {
return Err(rusqlite::Error::InvalidQuery)
};
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 posts (user_id, content, likes, comments, date) VALUES(?,?,?,?,?) RETURNING *;")?;
let post = stmt.query_row((user_id, content, likes_json, comments_json, date), |row| {
let row = post_from_row(row)?;
Ok(row)
})?;
Ok(post)
}
#[instrument()]
pub fn update_post(
post_id: u64,
likes: &HashSet<u64>,
comments: &Vec<(u64, String)>,
) -> Result<(), rusqlite::Error> {
tracing::trace!("Updating post");
let Ok(likes_json) = serde_json::to_string(&likes) else {
return Err(rusqlite::Error::InvalidQuery)
};
let Ok(comments_json) = serde_json::to_string(&comments) else {
return Err(rusqlite::Error::InvalidQuery)
};
let conn = database::connect()?;
let sql = "UPDATE posts SET likes = ?, comments = ? WHERE post_id = ?";
conn.execute(sql, (likes_json, comments_json, post_id))?;
Ok(())
}
|
