eversal_lib/database/
map_constellation.rs

1use sqlx::{Postgres, QueryBuilder};
2
3use crate::{LibResult, Error};
4
5const TABLE_NAME: &str = "map_constellations";
6
7#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
8pub struct ConstellationDb {
9  #[serde(rename = "regionId")]
10  pub region_id: i32,
11  #[serde(rename = "constellationId")]
12  pub constellation_id: i32,
13  #[serde(rename = "constellationName")]
14  pub constellation_name: String,
15  pub x: f64,
16  pub y: f64,
17  pub z: f64,
18  #[serde(rename = "xMin")]
19  pub x_min: f64,
20  #[serde(rename = "xMax")]
21  pub x_max: f64,
22  #[serde(rename = "yMin")]
23  pub y_min: f64,
24  #[serde(rename = "yMax")]
25  pub y_max: f64,
26  #[serde(rename = "zMin")]
27  pub z_min: f64,
28  #[serde(rename = "zMax")]
29  pub z_max: f64,
30  #[serde(rename = "factionId", skip_serializing_if = "Option::is_none")]
31  pub faction_id: Option<i32>,
32  pub radius: f64,
33}
34
35#[derive(Debug, serde::Serialize, serde::Deserialize)]
36pub struct ConstellationFilter {
37  #[serde(rename = "constellationId")]
38  pub constellation_id: Option<Vec<i32>>,
39  #[serde(rename = "regionId")]
40  pub region_id: Option<Vec<i32>>,
41  pub page: Option<i64>,
42  pub limit: Option<i64>,
43}
44
45impl ConstellationDb {
46  pub async fn get_by_id(id: i32) -> LibResult<Self> {
47    let pool: &sqlx::Pool<sqlx::Postgres> = crate::pool();
48    let row = sqlx::query_as::<_, Self>(&format!(
49      "SELECT * FROM {} WHERE constellation_id = $1",
50      TABLE_NAME
51    ))
52    .bind(id)
53    .fetch_one(pool)
54    .await?;
55    Ok(row)
56  }
57
58  pub async fn count(filter: &ConstellationFilter) -> LibResult<i64> {
59    let pool = crate::pool();
60    let mut query = QueryBuilder::new(format!("SELECT COUNT(*) FROM {}", TABLE_NAME));
61    query = Self::build_query(query, filter);
62    let query = query.build_query_scalar();
63
64    match query.fetch_one(pool).await {
65      Ok(count) => Ok(count),
66      Err(err) => {
67        log::error!("Error counting items: {}", err);
68        return Err(Error::new(500, format!("Error counting items: {}", err)));
69      }
70    }
71  }
72
73  pub async fn get_multiple(filter: &ConstellationFilter) -> LibResult<Vec<Self>> {
74    let page = filter.page.unwrap_or(1);
75    let limit = filter.limit.unwrap_or(100);
76
77    let pool = crate::pool();
78    let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
79    query = Self::build_query(query, filter);
80    query.push(" ORDER BY type_id LIMIT ");
81    query.push_bind(limit);
82    query.push(" OFFSET ");
83    query.push_bind((page - 1) * limit);
84    let query = query.build_query_as();
85
86    match query.fetch_all(pool).await {
87      Ok(items) => Ok(items),
88      Err(err) => {
89        log::error!("Error fetching items: {}", err);
90        return Err(Error::new(500, format!("Error fetching items: {}", err)));
91      }
92    }
93  }
94
95  fn build_query<'a>(
96    mut query: QueryBuilder<'a, Postgres>,
97    filter: &'a ConstellationFilter,
98  ) -> QueryBuilder<'a, Postgres> {
99    let mut has_where = false;
100
101    // Parse the constellation_id query parameter
102    if let Some(constellation_ids) = &filter.constellation_id {
103      if !has_where {
104        query.push(" WHERE");
105        has_where = true;
106      } else {
107        query.push(" AND");
108      }
109      query.push(" (");
110      for (i, constellation_id) in constellation_ids.iter().enumerate() {
111        if i > 0 {
112          query.push(" OR");
113        }
114        query.push(" constellation_id ILIKE ");
115        query.push_bind(format!("%{}%", constellation_id));
116      }
117      query.push(" )");
118    }
119
120    // Parse the region_id query parameter
121    if let Some(region_ids) = &filter.region_id {
122      if !has_where {
123        query.push(" WHERE");
124      } else {
125        query.push(" AND");
126      }
127      query.push(" (");
128      for (i, region_id) in region_ids.iter().enumerate() {
129        if i > 0 {
130          query.push(" OR");
131        }
132        query.push(" region_id = ");
133        query.push_bind(region_id);
134      }
135      query.push(" )");
136    }
137
138    query
139  }
140
141  pub async fn insert(&self) -> LibResult<()> {
142    let pool = crate::pool();
143    sqlx::query(&format!(
144      "INSERT INTO {} (
145        region_id, constellation_id, constellation_name, x, y, z, 
146        x_min, x_max, y_min, y_max, z_min, z_max, faction_id, radius
147      ) VALUES (
148        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14
149      )",
150      TABLE_NAME
151    ))
152    .bind(&self.region_id)
153    .bind(&self.constellation_id)
154    .bind(&self.constellation_name)
155    .bind(&self.x)
156    .bind(&self.y)
157    .bind(&self.z)
158    .bind(&self.x_min)
159    .bind(&self.x_max)
160    .bind(&self.y_min)
161    .bind(&self.y_max)
162    .bind(&self.z_min)
163    .bind(&self.z_max)
164    .bind(&self.faction_id)
165    .bind(&self.radius)
166    .execute(pool)
167    .await?;
168    Ok(())
169  }
170
171  pub async fn insert_multiple(constellations: &Vec<Self>) -> LibResult<()> {
172    let pool = crate::pool();
173    let step = 1000;
174    for i in (0..constellations.len()).step_by(step) {
175      let mut query = sqlx::QueryBuilder::new(
176        format!(
177          r#"
178          INSERT INTO {} (region_id, constellation_id, constellation_name, x, y, z, x_min, x_max, 
179            y_min, y_max, z_min, z_max, faction_id, radius)
180          VALUES
181          "#,
182          TABLE_NAME
183        )
184        .as_str(),
185      );
186
187      for j in 0..step {
188        if i + j >= constellations.len() {
189          break;
190        }
191        if j > 0 {
192          query.push(", ");
193        }
194        let constellation = &constellations[i + j];
195        query
196          .push(" (")
197          .push_bind(constellation.region_id)
198          .push(", ")
199          .push_bind(constellation.constellation_id)
200          .push(", ")
201          .push_bind(&constellation.constellation_name)
202          .push(", ")
203          .push_bind(constellation.x)
204          .push(", ")
205          .push_bind(constellation.y)
206          .push(", ")
207          .push_bind(constellation.z)
208          .push(", ")
209          .push_bind(constellation.x_min)
210          .push(", ")
211          .push_bind(constellation.x_max)
212          .push(", ")
213          .push_bind(constellation.y_min)
214          .push(", ")
215          .push_bind(constellation.y_max)
216          .push(", ")
217          .push_bind(constellation.z_min)
218          .push(", ")
219          .push_bind(constellation.z_max)
220          .push(", ")
221          .push_bind(constellation.faction_id)
222          .push(", ")
223          .push_bind(constellation.radius)
224          .push(") ");
225      }
226      query
227        .push("ON CONFLICT (constellation_id) DO UPDATE SET ")
228        .push("region_id = EXCLUDED.region_id, ")
229        .push("constellation_name = EXCLUDED.constellation_name, ")
230        .push("x = EXCLUDED.x, ")
231        .push("y = EXCLUDED.y, ")
232        .push("z = EXCLUDED.z, ")
233        .push("x_min = EXCLUDED.x_min, ")
234        .push("x_max = EXCLUDED.x_max, ")
235        .push("y_min = EXCLUDED.y_min, ")
236        .push("y_max = EXCLUDED.y_max, ")
237        .push("z_min = EXCLUDED.z_min, ")
238        .push("z_max = EXCLUDED.z_max, ")
239        .push("faction_id = EXCLUDED.faction_id, ")
240        .push("radius = EXCLUDED.radius");
241      query.build().execute(pool).await?;
242    }
243
244    Ok(())
245  }
246
247  pub async fn update(&self) -> LibResult<()> {
248    let pool = crate::pool();
249    sqlx::query(&format!(
250      "UPDATE {} SET
251        region_id = $2,
252        constellation_name = $3,
253        x = $4, 
254        y = $5,
255        z = $6, 
256        x_min = $7,
257        x_max = $8,
258        y_min = $9,
259        y_max = $10,
260        z_min = $11
261        z_max = $12,
262        faction_id = $13,
263        radius = $14
264      WHERE constellation_id = $1",
265      TABLE_NAME
266    ))
267    .bind(&self.constellation_id)
268    .bind(&self.region_id)
269    .bind(&self.constellation_name)
270    .bind(&self.x)
271    .bind(&self.y)
272    .bind(&self.z)
273    .bind(&self.x_min)
274    .bind(&self.x_max)
275    .bind(&self.y_min)
276    .bind(&self.y_max)
277    .bind(&self.z_min)
278    .bind(&self.z_max)
279    .bind(&self.faction_id)
280    .bind(&self.radius)
281    .fetch_one(pool)
282    .await?;
283    Ok(())
284  }
285
286  pub async fn delete(constellation_id: i32) -> LibResult<()> {
287    let pool = crate::pool();
288    sqlx::query(&format!(
289      "DELETE FROM {} WHERE constellation_id = $1",
290      TABLE_NAME
291    ))
292    .bind(constellation_id)
293    .fetch_one(pool)
294    .await?;
295    Ok(())
296  }
297}
eversal_lib/database/map_constellation.rs

eversal_lib/database/
map_constellation.rs
