prehnite_core/db/
query.rs

1use crate::db::schema::{
2    BackgroundInfo, BackgroundReference, BibliographyAuthor, Draft, Headline, HeadlineChildren,
3    Item, ItemReference, ItemType, Paragraph, ParagraphSummary, Setting, Tag, Task,
4};
5use crate::settings::SettingKey;
6use crate::{opt_unwrap_or_continue, opt_unwrap_or_return, to_hash_map_key_id};
7use indexmap::IndexMap;
8use sqlx::SqliteConnection;
9
10const UPDATE_SETTING_SQL: &str = include_str!("../../../assets/query/update_settings.sql");
11#[tracing::instrument]
12/// 設定を更新します。
13/// ```sql
14#[doc = include_str!("../../../assets/query/update_settings.sql")]
15/// ```
16pub async fn update_setting(
17    conn: &mut SqliteConnection,
18    setting_key: SettingKey,
19    setting_value: Option<String>,
20) -> Result<(), sqlx::Error> {
21    let v = sqlx::query(UPDATE_SETTING_SQL)
22        .bind(setting_key.to_string())
23        .bind(setting_value)
24        .execute(conn)
25        .await?;
26    Ok(())
27}
28
29const FETCH_SETTING_SQL: &str = include_str!("../../../assets/query/fetch_settings.sql");
30#[tracing::instrument]
31/// 設定を取得します。
32/// ```sql
33#[doc = include_str!("../../../assets/query/fetch_settings.sql")]
34/// ```
35pub async fn fetch_setting(
36    conn: &mut SqliteConnection,
37    setting_key: SettingKey,
38) -> Result<Option<Setting>, sqlx::Error> {
39    sqlx::query_as::<_, Setting>(FETCH_SETTING_SQL)
40        .bind(setting_key.to_string())
41        .fetch_optional(conn)
42        .await
43}
44
45const FETCH_BACKGROUND_INFO_FROM_ITEM_ID_SQL: &str =
46    include_str!("../../../assets/query/fetch_background_info_from_item_id.sql");
47#[tracing::instrument]
48/// アイテムに紐づいた背景情報をアイテムIDから取得します。
49/// ```sql
50#[doc = include_str!("../../../assets/query/fetch_background_info_from_item_id.sql")]
51/// ```
52pub async fn fetch_background_info_from_item_id(
53    conn: &mut SqliteConnection,
54    item_id: i64,
55) -> Result<Vec<BackgroundInfo>, sqlx::Error> {
56    sqlx::query_as::<_, BackgroundInfo>(FETCH_BACKGROUND_INFO_FROM_ITEM_ID_SQL)
57        .bind(item_id)
58        .fetch_all(conn)
59        .await
60}
61
62const FETCH_HEADLINE_CHILDREN_RECURSE_SQL: &str =
63    include_str!("../../../assets/query/fetch_headline_children_recurse.sql");
64#[tracing::instrument]
65/// 再帰的に見出しアイテムの子孫を取得します。
66// TODO: 要確認 要変更
67/// ```sql
68#[doc = include_str!("../../../assets/query/fetch_headline_children_recurse.sql")]
69/// ```
70pub async fn fetch_headline_children_recurse(
71    conn: &mut SqliteConnection,
72    headline_id: i64,
73) -> Result<Option<HeadlineChildren>, sqlx::Error> {
74    let query_result = sqlx::query_as::<_, Headline>(FETCH_HEADLINE_CHILDREN_RECURSE_SQL)
75        .bind(headline_id)
76        .fetch_all(conn)
77        .await?;
78    let headlines: IndexMap<i64, Headline> = to_hash_map_key_id!(query_result);
79    let parent = opt_unwrap_or_return!(headlines.get(&headline_id).cloned(), Ok(None));
80    let mut children: IndexMap<i64, Vec<Headline>> = IndexMap::new();
81    for i in headlines.keys() {
82        let headline = opt_unwrap_or_continue!(headlines.get(i));
83        match children.get_mut(&opt_unwrap_or_continue!(headline.parent_id)) {
84            None => {
85                children.insert(headline.parent_id.unwrap(), vec![headline.clone()]);
86            }
87            Some(v) => v.push(headline.clone()),
88        }
89    }
90    Ok(Some(HeadlineChildren { parent, children }))
91}
92
93const FETCH_BACKGROUND_REFERENCES_SQL: &str =
94    include_str!("../../../assets/query/fetch_background_references.sql");
95#[tracing::instrument]
96/// 背景情報に紐づいた参考文献リストを背景情報idから取得します。
97/// ```sql
98#[doc = include_str!("../../../assets/query/fetch_background_references.sql")]
99/// ```
100pub async fn fetch_background_references(
101    conn: &mut SqliteConnection,
102    background_info_id: i64,
103) -> Result<Vec<BackgroundReference>, sqlx::Error> {
104    sqlx::query_as::<_, BackgroundReference>(FETCH_BACKGROUND_REFERENCES_SQL)
105        .bind(background_info_id)
106        .fetch_all(conn)
107        .await
108}
109
110const FETCH_BIBLIOGRAPHY_AUTHORS_SQL: &str =
111    include_str!("../../../assets/query/fetch_bibliography_authors.sql");
112#[tracing::instrument]
113/// 参考文献の著者リストを参考文献idから取得します。
114/// ```sql
115#[doc = include_str!("../../../assets/query/fetch_bibliography_authors.sql")]
116/// ```
117pub async fn fetch_bibliography_authors(
118    conn: &mut SqliteConnection,
119    bibliography_id: i64,
120) -> Result<Vec<BibliographyAuthor>, sqlx::Error> {
121    sqlx::query_as::<_, BibliographyAuthor>(FETCH_BIBLIOGRAPHY_AUTHORS_SQL)
122        .bind(bibliography_id)
123        .fetch_all(conn)
124        .await
125}
126
127const FETCH_ITEM_REFERENCES_SQL: &str =
128    include_str!("../../../assets/query/fetch_item_references.sql");
129#[tracing::instrument]
130/// アイテムに紐づいた参考文献リストをアイテムidから取得します。
131/// ```sql
132#[doc = include_str!("../../../assets/query/fetch_item_references.sql")]
133/// ```
134pub async fn fetch_item_references(
135    conn: &mut SqliteConnection,
136    item_id: i64,
137) -> Result<Vec<ItemReference>, sqlx::Error> {
138    sqlx::query_as::<_, ItemReference>(FETCH_ITEM_REFERENCES_SQL)
139        .bind(item_id)
140        .fetch_all(conn)
141        .await
142}
143
144const FETCH_ITEM_RELATED_TAGS_SQL: &str =
145    include_str!("../../../assets/query/fetch_item_related_tags.sql");
146#[tracing::instrument]
147/// アイテムに紐づいたタグリストをアイテムidから取得します。
148/// ```sql
149#[doc = include_str!("../../../assets/query/fetch_item_related_tags.sql")]
150/// ```
151pub async fn fetch_item_related_tags(
152    conn: &mut SqliteConnection,
153    item_id: i64,
154) -> Result<Vec<Tag>, sqlx::Error> {
155    sqlx::query_as::<_, Tag>(FETCH_ITEM_RELATED_TAGS_SQL)
156        .bind(item_id)
157        .fetch_all(conn)
158        .await
159}
160
161const FETCH_ITEM_RELATED_TASKS_SQL: &str =
162    include_str!("../../../assets/query/fetch_item_related_tasks.sql");
163#[tracing::instrument]
164/// アイテムに紐づいたタスクをアイテムidから取得します。
165/// ```sql
166#[doc = include_str!("../../../assets/query/fetch_item_related_tasks.sql")]
167/// ```
168pub async fn fetch_item_related_tasks(
169    conn: &mut SqliteConnection,
170    item_id: i64,
171) -> Result<Vec<Task>, sqlx::Error> {
172    sqlx::query_as::<_, Task>(FETCH_ITEM_RELATED_TASKS_SQL)
173        .bind(item_id)
174        .fetch_all(conn)
175        .await
176}
177
178const FETCH_HEADLINE_RELATED_PARAGRAPH_SQL: &str =
179    include_str!("../../../assets/query/fetch_headline_related_paragraph.sql");
180#[tracing::instrument]
181/// 見出しに紐づいた段落リストを見出しidから取得します。
182/// ```sql
183#[doc = include_str!("../../../assets/query/fetch_headline_related_paragraph.sql")]
184/// ```
185pub async fn fetch_headline_related_paragraph(
186    conn: &mut SqliteConnection,
187    headline_id: i64,
188) -> Result<Vec<Paragraph>, sqlx::Error> {
189    sqlx::query_as::<_, Paragraph>(FETCH_HEADLINE_RELATED_PARAGRAPH_SQL)
190        .bind(headline_id)
191        .fetch_all(conn)
192        .await
193}
194
195const FETCH_PARAGRAPH_RELATED_SUMMARIES_SQL: &str =
196    include_str!("../../../assets/query/fetch_paragraph_related_summaries.sql");
197#[tracing::instrument]
198/// 段落に紐づいた要約を段落idから取得します。
199/// ```sql
200#[doc = include_str!("../../../assets/query/fetch_paragraph_related_summaries.sql")]
201/// ```
202pub async fn fetch_paragraph_related_summaries(
203    conn: &mut SqliteConnection,
204    paragraph_id: i64,
205) -> Result<Vec<ParagraphSummary>, sqlx::Error> {
206    sqlx::query_as::<_, ParagraphSummary>(FETCH_PARAGRAPH_RELATED_SUMMARIES_SQL)
207        .bind(paragraph_id)
208        .fetch_all(conn)
209        .await
210}
211
212const FETCH_PARAGRAPH_RELATED_DRAFT_SQL: &str =
213    include_str!("../../../assets/query/fetch_paragraph_related_draft.sql");
214#[tracing::instrument]
215/// 段落に紐づいた下書きを段落idから取得します。
216/// ```sql
217#[doc = include_str!("../../../assets/query/fetch_paragraph_related_draft.sql")]
218/// ```
219pub async fn fetch_paragraph_related_draft(
220    conn: &mut SqliteConnection,
221    paragraph_id: i64,
222) -> Result<Vec<Draft>, sqlx::Error> {
223    sqlx::query_as::<_, Draft>(FETCH_PARAGRAPH_RELATED_DRAFT_SQL)
224        .bind(paragraph_id)
225        .fetch_all(conn)
226        .await
227}
228
229const FETCH_ROOT_HEADLINES_SQL: &str =
230    include_str!("../../../assets/query/fetch_root_headline_query.sql");
231
232/// 親が存在しない見出しを取得します。
233/// ```sql
234#[doc = include_str!("../../../assets/query/fetch_root_headline_query.sql")]
235/// ```
236pub async fn fetch_root_headline_items(
237    conn: &mut SqliteConnection,
238    per_page: u8,
239    page: u32,
240) -> Result<IndexMap<i64, Item>, sqlx::Error> {
241    Ok(sqlx::query_as::<_, Item>(FETCH_ROOT_HEADLINES_SQL)
242        .bind(per_page)
243        .bind(page * per_page as u32)
244        .fetch_all(conn)
245        .await?
246        .into_iter()
247        .map(|v| (v.id, v))
248        .collect())
249}
250
251const FETCH_ROOT_HEADLINE_RELATED_PARAGRAPH_SQL: &str =
252    include_str!("../../../assets/query/fetch_root_headline_related_paragraph.sql");
253
254/// 親が存在しない見出しとそれらに紐づいた段落を取得します。
255/// ```sql
256#[doc = include_str!("../../../assets/query/fetch_root_headline_related_paragraph.sql")]
257/// ```
258pub async fn fetch_root_headline_related_paragraph(
259    conn: &mut SqliteConnection,
260    headline_per_page: u8,
261    headline_page: u32,
262) -> Result<IndexMap<i64, IndexMap<i64, Item>>, sqlx::Error> {
263    let mut result: IndexMap<i64, IndexMap<i64, Item>> = IndexMap::new();
264    sqlx::query_as::<_, Item>(FETCH_ROOT_HEADLINE_RELATED_PARAGRAPH_SQL)
265        .bind(headline_per_page)
266        .bind(headline_page * headline_per_page as u32)
267        .fetch_all(conn)
268        .await?
269        .into_iter()
270        .for_each(|v| {
271            let p = match &v.item_type {
272                ItemType::Headline(_) => return,
273                ItemType::Paragraph(v) => {
274                    opt_unwrap_or_return!(v, ())
275                }
276            };
277            let headline_itm_id = p.headline.id;
278            if result.contains_key(&headline_itm_id) {
279                result.get_mut(&headline_itm_id).unwrap().insert(v.id, v);
280            } else {
281                let mut tmp = IndexMap::new();
282                tmp.insert(v.id, v);
283                result.insert(headline_itm_id, tmp);
284            }
285        });
286    Ok(result)
287}
prehnite_core/db/query.rs

prehnite_core/db/
query.rs
