eversal_lib/database/
map_region.rs

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

eversal_lib/database/
map_region.rs
