-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.rs
165 lines (151 loc) · 5.59 KB
/
db.rs
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
use crate::{
error::{AppError, AppErrorType},
models::{QuestionId, Questions, ScrapedQuestion, Tag, TagQuestion, TagQuestionRelation, TagId},
};
use deadpool_postgres::Client;
use tokio_pg_mapper::FromTokioPostgresRow;
pub async fn get_tags(client: &Client) -> Result<Vec<Tag>, AppError> {
let statement = client.prepare("select * from tag limit 10;").await?;
// .map_err(AppError::db_error)?;
// We dont need to explicitly convert from Error to AppError. We had implemented the From trait for Postegress Error to AppError
// query will take the query statement and a refrence to the list of parameters
// once we received the rows as result, we want to use the model struct so we use the iterator and map each item to the tag list
// to convert a row we need to import a trait from pg mapper, it provide row ref
let tags = client
.query(&statement, &[])
.await
.expect("Error getting tags")
.iter()
.map(|row| Tag::from_row_ref(row).unwrap())
.collect::<Vec<Tag>>();
Ok(tags)
}
pub async fn get_questions(client: &Client) -> Result<Vec<Questions>, AppError> {
let statement = client.prepare("select * from question;").await?;
let questions = client
.query(&statement, &[])
.await
.expect("Error getting tags")
.iter()
.map(|row| Questions::from_row_ref(row).unwrap())
.collect::<Vec<Questions>>();
Ok(questions)
}
pub async fn get_related_question(
client: &Client,
tag_id: i32,
) -> Result<Vec<TagQuestionRelation>, AppError> {
let statement = client
.prepare("select qt.tag_id,qt.question_id, t.tag_title, q.title as q_title,q.q_description, q.question_link, q.votes,q.views,q.stack_id,q.answer from tag_question qt, tag t, question q
where qt.tag_id = $1 and qt.question_id = q.question_id and qt.tag_id=t.tag_id;")
.await
?;
let questions = client
.query(&statement, &[&tag_id])
.await
.expect("Error getting tags")
.iter()
.map(|row| TagQuestionRelation::from_row_ref(row).unwrap())
.collect::<Vec<TagQuestionRelation>>();
Ok(questions)
}
pub async fn create_tag(client: &Client, tag_title: String) -> Result<Tag, AppError> {
let statement = client
.prepare("insert into tag (tag_title) values ($1) returning tag_id, tag_title;")
.await?;
client
.query(&statement, &[&tag_title])
.await
.expect("Error creating tag")
.iter()
.map(|row| Tag::from_row_ref(row).unwrap())
.collect::<Vec<Tag>>()
.pop()
.ok_or(AppError {
cause: Some("Unknown error".to_string()),
message: Some("Error creating todolist".to_string()),
error_type: AppErrorType::DbError,
})
}
pub async fn update_tag(client: &Client, tag_id: i32, tag_title: String) -> Result<bool, AppError> {
let statement = client
.prepare("update tag set tag_title = $2 where tag_id=$1;")
.await?;
let result = client
.execute(&statement, &[&tag_id, &tag_title])
.await
.expect("Error updating tag");
match result {
ref updated if *updated == 1 => Ok(true),
_ => Ok(false),
}
}
// It will create or get tag id
pub async fn get_tag_id(client: &Client, tag_name: String) -> Result<TagId, AppError> {
let statement = client
.prepare("with s as (select tag_id from tag where tag_title = $1), i as (insert into tag (tag_title) select $1 where not exists (select 1 from s) returning tag_id) select tag_id from i union all select tag_id from s;")
.await?;
println!("tag name {:?}",tag_name);
client
.query(&statement, &[&tag_name])
.await
.expect("Error creating tag")
.iter()
.map(|row| TagId::from_row_ref(row).unwrap())
.collect::<Vec<TagId>>()
.pop()
.ok_or(AppError {
cause: Some("Unknown error".to_string()),
message: Some("Error creating todolist".to_string()),
error_type: AppErrorType::DbError,
})
}
pub async fn create_or_skip(
client: &Client,
question: &ScrapedQuestion,
) -> Result<QuestionId, AppError> {
let statement = client
.prepare(
"insert into question (title,q_description,question_link,votes,stack_id,views,answer) values ($1,$2,$3,$4,$5,$6,$7) on conflict (stack_id) do nothing returning question_id")
.await?;
client
.query(
&statement,
&[
&question.title,
&question.q_description,
&question.question_link,
&question.votes,
&question.stack_id,
&question.views,
&question.answer,
],
)
.await
.expect("Error creating tag")
.iter()
.map(|row| QuestionId::from_row_ref(row).unwrap())
.collect::<Vec<QuestionId>>()
.pop()
.ok_or(AppError {
cause: Some("Unknown error".to_string()),
message: Some("Error creating todolist".to_string()),
error_type: AppErrorType::DbError,
})
}
pub async fn create_tag_quest_rel(
client: &Client,
question: &TagQuestion,
) -> Result<bool, AppError> {
let statement = client
.prepare("insert into tag_question (tag_id,question_id) values ($1,$2);")
.await?;
let result = client
.execute(&statement, &[&question.tag_id, &question.question_id])
.await
.expect("Error updating tag");
match result {
ref updated if *updated == 1 => Ok(true),
_ => Ok(false),
}
}