eversal_lib/database/
map_system_jump.rs

1use sqlx::{Postgres, QueryBuilder};
2
3use crate::{LibResult, Error};
4
5const TABLE_NAME: &str = "map_system_jumps";
6
7#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
8pub struct SystemJumpDb {
9  #[serde(rename = "fromRegionId")]
10  pub from_region_id: i32,
11  #[serde(rename = "fromConstellationId")]
12  pub from_constellation_id: i32,
13  #[serde(rename = "fromSystemId")]
14  pub from_system_id: i32,
15  #[serde(rename = "toSystemId")]
16  pub to_system_id: i32,
17  #[serde(rename = "toConstellationId")]
18  pub to_constellation_id: i32,
19  #[serde(rename = "toRegionId")]
20  pub to_region_id: i32,
21}
22
23#[derive(Debug, serde::Serialize, serde::Deserialize)]
24pub struct SystemJumpFilter {
25  #[serde(rename = "fromRegionId")]
26  pub from_system_id: Option<Vec<i32>>,
27  #[serde(rename = "toRegionId")]
28  pub to_system_id: Option<Vec<i32>>,
29  pub page: Option<i64>,
30  pub limit: Option<i64>,
31}
32
33impl SystemJumpDb {
34  pub async fn get_by_id(id: i32) -> LibResult<Self> {
35    let pool = crate::pool();
36    let row = sqlx::query_as::<_, Self>(&format!(
37      "SELECT * FROM {} WHERE from_system_id = $1",
38      TABLE_NAME
39    ))
40    .bind(id)
41    .fetch_one(pool)
42    .await?;
43    Ok(row)
44  }
45
46  pub async fn count(filter: &SystemJumpFilter) -> LibResult<i64> {
47    let pool = crate::pool();
48    let mut query = QueryBuilder::new(format!("SELECT COUNT(*) FROM {}", TABLE_NAME));
49    query = Self::build_query(query, filter);
50    let query = query.build_query_scalar();
51
52    match query.fetch_one(pool).await {
53      Ok(count) => Ok(count),
54      Err(err) => {
55        log::error!("Error counting items: {}", err);
56        return Err(Error::new(500, format!("Error counting items: {}", err)));
57      }
58    }
59  }
60
61  pub async fn get_multiple(filter: &SystemJumpFilter) -> LibResult<Vec<Self>> {
62    let page = filter.page.unwrap_or(1);
63    let limit = filter.limit.unwrap_or(100);
64
65    let pool = crate::pool();
66    let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
67    query = Self::build_query(query, filter);
68    query.push(" ORDER BY from_system_id LIMIT ");
69    query.push_bind(limit);
70    query.push(" OFFSET ");
71    query.push_bind((page - 1) * limit);
72    let query = query.build_query_as();
73
74    match query.fetch_all(pool).await {
75      Ok(items) => Ok(items),
76      Err(err) => {
77        log::error!("Error fetching items: {}", err);
78        return Err(Error::new(500, format!("Error fetching items: {}", err)));
79      }
80    }
81  }
82
83  fn build_query<'a>(
84    mut query: QueryBuilder<'a, Postgres>,
85    filter: &'a SystemJumpFilter,
86  ) -> QueryBuilder<'a, Postgres> {
87    let mut has_where = false;
88
89    // Parse the to_system_id query parameter
90    if let Some(to_system_ids) = &filter.to_system_id {
91      if !has_where {
92        query.push(" WHERE");
93        has_where = true;
94      } else {
95        query.push(" AND");
96      }
97      query.push(" (");
98      for (i, to_system_id) in to_system_ids.iter().enumerate() {
99        if i > 0 {
100          query.push(" OR");
101        }
102        query.push(" to_system_id ILIKE ");
103        query.push_bind(format!("%{}%", to_system_id));
104      }
105      query.push(" )");
106    }
107
108    // Parse the from_system_id query parameter
109    if let Some(from_system_ids) = &filter.from_system_id {
110      if !has_where {
111        query.push(" WHERE");
112      } else {
113        query.push(" AND");
114      }
115      query.push(" (");
116      for (i, from_system_id) in from_system_ids.iter().enumerate() {
117        if i > 0 {
118          query.push(" OR");
119        }
120        query.push(" from_system_id = ");
121        query.push_bind(from_system_id);
122      }
123      query.push(" )");
124    }
125
126    query
127  }
128
129  pub async fn insert(&self) -> LibResult<()> {
130    let pool = crate::pool();
131    sqlx::query(&format!(
132      "INSERT INTO {} (
133        from_system_id, to_system_id, from_constellation_id, to_constellation_id, 
134        from_region_id, to_region_id
135      ) VALUES ($1, $2, $3, $4, $5, $6)",
136      TABLE_NAME
137    ))
138    .bind(self.from_system_id)
139    .bind(self.to_system_id)
140    .bind(self.from_constellation_id)
141    .bind(self.to_constellation_id)
142    .bind(self.from_region_id)
143    .bind(self.to_region_id)
144    .execute(pool)
145    .await?;
146    Ok(())
147  }
148
149  pub async fn insert_multiple(system_jumps: &Vec<Self>) -> LibResult<()> {
150    let pool = crate::pool();
151    let step = 1000;
152    for i in (0..system_jumps.len()).step_by(step) {
153      let mut query = sqlx::QueryBuilder::new(format!(
154        r#"
155        INSERT INTO {} (from_region_id, from_constellation_id, from_solar_system_id, 
156          to_solar_system_id, to_constellation_id, to_region_id)
157        VALUES
158        "#,
159        TABLE_NAME
160      ));
161
162      for j in 0..step {
163        if i + j >= system_jumps.len() {
164          break;
165        }
166        if j > 0 {
167          query.push(", ");
168        }
169
170        let system_jump = &system_jumps[i + j];
171        query
172          .push(" (")
173          .push_bind(system_jump.from_region_id)
174          .push(", ")
175          .push_bind(system_jump.from_constellation_id)
176          .push(", ")
177          .push_bind(system_jump.from_system_id)
178          .push(", ")
179          .push_bind(system_jump.to_system_id)
180          .push(", ")
181          .push_bind(system_jump.to_constellation_id)
182          .push(", ")
183          .push_bind(system_jump.to_region_id)
184          .push(") ");
185      }
186      query
187        .push(" ON CONFLICT (from_solar_system_id, to_solar_system_id) DO UPDATE SET ")
188        .push("from_constellation_id = EXCLUDED.from_constellation_id, ")
189        .push("from_region_id = EXCLUDED.from_region_id, ")
190        .push("to_constellation_id = EXCLUDED.to_constellation_id, ")
191        .push("to_region_id = EXCLUDED.to_region_id");
192      query.build().execute(pool).await?;
193    }
194    Ok(())
195  }
196
197  pub async fn update(&self) -> LibResult<()> {
198    let pool = crate::pool();
199    sqlx::query(&format!(
200      "UPDATE {} SET
201        from_system_id = $1, to_system_id = $2, from_constellation_id = $3, to_constellation_id = $4, 
202        from_region_id = $5, to_region_id = $6
203      WHERE from_system_id = $7",
204      TABLE_NAME
205    ))
206    .bind(self.from_system_id)
207    .bind(self.to_system_id)
208    .bind(self.from_constellation_id)
209    .bind(self.to_constellation_id)
210    .bind(self.from_region_id)
211    .bind(self.to_region_id)
212    .bind(self.from_system_id)
213    .execute(pool)
214    .await?;
215    Ok(())
216  }
217
218  pub async fn delete(from_system_id: i32, to_system_id: i32) -> LibResult<()> {
219    let pool = crate::pool();
220    sqlx::query(&format!(
221      "DELETE FROM {} WHERE from_system_id = $1 AND to_system_id = $2",
222      TABLE_NAME
223    ))
224    .bind(from_system_id)
225    .bind(to_system_id)
226    .execute(pool)
227    .await?;
228    Ok(())
229  }
230}
eversal_lib/database/map_system_jump.rs

eversal_lib/database/
map_system_jump.rs
