world_tables_base/
lib.rs

1
2use anyhow::{Context, Result};
3use rusqlite::{
4    Connection,
5    OptionalExtension,
6    params,
7    named_params,
8};
9use serde::{Serialize, Deserialize};
10use url::Url;
11
12pub use dbent::prelude::*;
13
14pub trait Model {
15    fn all(conn: &Connection, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> where Self: Sized;
16    fn count(conn: &Connection) -> Result<usize>;
17    fn get(conn: &Connection, key: &str) -> Result<Self> where Self: Sized;
18}
19
20//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
21//<<>><=========================  COUNTRY  ==========================><<>>//
22//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
23
24#[derive(Clone, Default, Debug, Entity, Label, Serialize, Deserialize)]
25pub struct Country {
26    pub iso2: Key<String>,
27    pub iso3: String,
28    #[label] pub name: String,
29    pub code: u32,
30    pub capital: EntityLabelInt<City>,
31    pub currency: EntityLabelString<Currency>,
32    pub tld: String,
33    pub native: String,
34    pub region: EntityLabelInt<WorldRegion>,
35    pub subregion: EntityLabelInt<WorldSubregion>,
36    pub latitude: f32,
37    pub longitude: f32,
38    pub emoji: String,
39    pub emoji_u: String,
40    pub states: Many<State>,
41}
42
43impl Model for Country {
44    fn count(conn: &Connection) -> Result<usize> {
45        let mut stmt = conn.prepare_cached(
46            "SELECT count(*) FROM countries")
47            .context("Failed preparing SQL for fetching countries count")?;
48
49        stmt
50            .query_row([], |row| {
51                row.get(0)
52            })
53            .context("Failed querying countries count")
54    }
55
56    fn all(conn: &Connection, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
57        let mut stmt = conn.prepare_cached(
58                "SELECT iso2, name, world_region_id, world_region, world_subregion_id, world_subregion
59                FROM countries
60                LIMIT ?1
61                OFFSET ?2")
62            .context("Failed preparing SQL for fetching countries")?;
63        let records = stmt
64            .query_map([limit, offset], |row| {
65                Ok(
66                    Self {
67                        iso2: row.get(0)?,
68                        name: row.get(1)?,
69                        region: EntityLabel::KeyLabel(row.get(2).unwrap_or_default(), row.get(3).unwrap_or_default()),
70                        subregion: EntityLabel::KeyLabel(row.get(4).unwrap_or_default(), row.get(5).unwrap_or_default()),
71                        ..Default::default()
72                    }
73                )
74            })?
75            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
76
77        Ok((Self::count(conn)?, records))
78    }
79
80    fn get(conn: &Connection, key: &str) -> Result<Self> {
81        let mut stmt = conn.prepare_cached(
82               "SELECT iso2, iso3, name, code, capital_id, capital, currency_id, currency,
83               tld, native, world_region_id, world_region, world_subregion_id, world_subregion,
84               latitude, longitude, emoji, emoji_u
85               FROM countries
86               WHERE iso2 = ?")
87            .context("Failed preparing SQL for fetching country data")?;
88
89        stmt
90            .query_row([key], |row| {
91                Ok(
92                    Self {
93                        iso2: row.get(0)?,
94                        iso3: row.get(1)?,
95                        name: row.get(2)?,
96                        code: row.get(3)?,
97                        capital: EntityLabel::KeyLabel(row.get(4)?, row.get(5).unwrap_or_default()),
98                        currency: EntityLabel::KeyLabel(row.get(6)?, row.get(7).unwrap_or_default()),
99                        tld: row.get(8)?,
100                        native: row.get(9)?,
101                        region: EntityLabel::KeyLabel(row.get(10).unwrap_or_default(), row.get(11).unwrap_or_default()),
102                        subregion: EntityLabel::KeyLabel(row.get(12).unwrap_or_default(), row.get(13).unwrap_or_default()),
103                        latitude: row.get(14)?,
104                        longitude: row.get(15)?,
105                        emoji: row.get(16)?,
106                        emoji_u: row.get(17)?,
107                        ..Default::default()
108                    }
109                )
110            })
111            .context("Failed querying country data")
112    }
113}
114
115impl Country {
116    pub fn save(&self, conn: &mut Connection) -> Result<()> {
117        let Self {
118            iso2,
119            iso3,
120            name,
121            code,
122            capital,
123            currency,
124            tld,
125            native,
126            region,
127            subregion,
128            latitude,
129            longitude,
130            emoji,
131            emoji_u,
132            ..
133        } = self;
134
135        conn.execute(
136            "INSERT INTO countries
137                (iso2, iso3, name, code, capital_id, capital, currency_id, currency,
138                tld, native, world_region_id, world_region, world_subregion_id, world_subregion,
139                latitude, longitude, emoji, emoji_u)
140            VALUES
141                (:iso2, :iso3, :name, :code, :capital_id, :capital, :currency_id, :currency,
142                :tld, :native, :region_id, :region, :subregion_id, :subregion,
143                :latitude, :longitude, :emoji, :emoji_u)
144            ON CONFLICT(iso2) DO UPDATE
145            SET
146                iso3=:iso3, name=:name, code=:code, capital_id=:capital_id, capital=:capital,
147                currency_id=:currency_id, currency=:currency, tld=:tld, native=:native,
148                world_region_id=:region_id, world_region=:region, world_subregion_id=:subregion_id,
149                world_subregion=:subregion, latitude=:latitude, longitude=:longitude, emoji=:emoji,
150                emoji_u=:emoji_u;",
151            named_params! {
152                ":iso2": iso2,
153                ":iso3": iso3,
154                ":name": name,
155                ":code": code,
156                ":capital_id": capital.key().ok(),
157                ":capital": capital.label().ok(),
158                ":currency_id": currency.key().ok(),
159                ":currency": currency.label().ok(),
160                ":tld": tld,
161                ":native": native,
162                ":region_id": region.key().ok(),
163                ":region": region.label().ok(),
164                ":subregion_id": subregion.key().ok(),
165                ":subregion": subregion.label().ok(),
166                ":latitude": latitude,
167                ":longitude": longitude,
168                ":emoji": emoji,
169                ":emoji_u": emoji_u,
170            }
171        )?;
172
173        Ok(())
174    }
175
176    pub fn from_region(conn: &Connection, key: &str, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
177        let mut stmt = conn
178            .prepare_cached(
179                "SELECT iso2, name, world_region_id, world_region, world_subregion_id, world_subregion
180                FROM countries
181                WHERE world_region_id = ?1
182                LIMIT ?2
183                OFFSET ?3")
184            .context("Failed preparing SQL for fetching countries")?;
185
186        let records = stmt
187            .query_map(params![key, limit, offset], |row| {
188                Ok(
189                    Self {
190                        iso2: row.get(0)?,
191                        name: row.get(1)?,
192                        region: EntityLabel::KeyLabel(row.get(2).unwrap_or_default(), row.get(3).unwrap_or_default()),
193                        subregion: EntityLabel::KeyLabel(row.get(4).unwrap_or_default(), row.get(5).unwrap_or_default()),
194                        ..Default::default()
195                    }
196                )
197            })?
198            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
199
200        Ok((Self::from_region_count(conn, key)?, records))
201    }
202
203    pub fn from_subregion(conn: &Connection, key: &str, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
204        let mut stmt = conn
205            .prepare_cached(
206                "SELECT iso2, name, world_region_id, world_region, world_subregion_id, world_subregion
207                FROM countries
208                WHERE world_subregion_id = ?1
209                LIMIT ?2
210                OFFSET ?3")
211            .context("Failed preparing SQL for fetching countries")?;
212
213        let records = stmt
214            .query_map(params![key, limit, offset], |row| {
215                Ok(
216                    Self {
217                        iso2: row.get(0)?,
218                        name: row.get(1)?,
219                        region: EntityLabel::KeyLabel(row.get(2).unwrap_or_default(), row.get(3).unwrap_or_default()),
220                        subregion: EntityLabel::KeyLabel(row.get(4).unwrap_or_default(), row.get(5).unwrap_or_default()),
221                        ..Default::default()
222                    }
223                )
224            })?
225            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
226
227        Ok((Self::from_subregion_count(conn, key)?, records))
228    }
229
230    pub fn from_currency(conn: &Connection, key: &str, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
231        let mut stmt = conn
232            .prepare_cached(
233                "SELECT iso2, name, world_region_id, world_region, world_subregion_id, world_subregion, currency_id, currency
234                FROM countries
235                WHERE currency_id = ?1
236                LIMIT ?2
237                OFFSET ?3")
238            .context("Failed preparing SQL for fetching countries")?;
239
240        let records = stmt
241            .query_map(params![key, limit, offset], |row| {
242                Ok(
243                    Self {
244                        iso2: row.get(0)?,
245                        name: row.get(1)?,
246                        region: EntityLabel::KeyLabel(row.get(2).unwrap_or_default(), row.get(3).unwrap_or_default()),
247                        subregion: EntityLabel::KeyLabel(row.get(4).unwrap_or_default(), row.get(5).unwrap_or_default()),
248                        currency: EntityLabel::KeyLabel(row.get(6).unwrap_or_default(), row.get(7).unwrap_or_default()),
249                        ..Default::default()
250                    }
251                )
252            })?
253            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
254
255        Ok((Self::from_currency_count(conn, key)?, records))
256    }
257
258    pub fn from_region_count(conn: &Connection, key: &str) -> Result<usize> {
259        let mut stmt = conn.prepare_cached(
260            "SELECT count(*) FROM countries
261            WHERE world_region_id = ?")
262            .context("Failed preparing SQL for fetching countries count")?;
263
264        stmt
265            .query_row([key], |row| {
266                row.get(0)
267            })
268            .context("Failed querying countries count")
269    }
270
271    pub fn from_subregion_count(conn: &Connection, key: &str) -> Result<usize> {
272        let mut stmt = conn.prepare_cached(
273            "SELECT count(*) FROM countries
274            WHERE world_subregion_id = ?")
275            .context("Failed preparing SQL for fetching countries count")?;
276
277        stmt
278            .query_row([key], |row| {
279                row.get(0)
280            })
281            .context("Failed querying countries count")
282    }
283
284    pub fn from_currency_count(conn: &Connection, key: &str) -> Result<usize> {
285        let mut stmt = conn.prepare_cached(
286            "SELECT count(*) FROM countries
287            WHERE currency_id = ?")
288            .context("Failed preparing SQL for fetching countries count")?;
289
290        stmt
291            .query_row([key], |row| {
292                row.get(0)
293            })
294            .context("Failed querying countries count")
295    }
296}
297
298//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
299//<<>><========================  CURRENCY  ==========================><<>>//
300//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
301
302#[derive(Clone, Default, Debug, Entity, Label, Serialize, Deserialize)]
303pub struct Currency {
304    pub iso: Key<String>,
305    #[label] pub name: String,
306    pub symbol: String,
307    pub countries: Many<Country>,
308}
309
310impl PartialEq for Currency {
311    fn eq(&self, other: &Self) -> bool {
312        self.iso == other.iso
313    }
314}
315
316impl Eq for Currency {}
317
318impl std::hash::Hash for Currency {
319    fn hash<H: std::hash::Hasher>(&self, state: &mut H) {
320        self.iso.hash(state);
321    }
322}
323
324impl Model for Currency {
325    fn count(conn: &Connection) -> Result<usize> {
326        let mut stmt = conn.prepare_cached(
327            "SELECT count(*) FROM currencies")
328            .context("Failed preparing SQL for fetching currencies count")?;
329
330        stmt
331            .query_row([], |row| {
332                row.get(0)
333            })
334            .context("Failed querying currencies count")
335    }
336
337    fn all(conn: &Connection, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
338        let mut stmt = conn.prepare_cached(
339            "SELECT iso, name, symbol FROM currencies
340            LIMIT ?1
341            OFFSET ?2")
342            .context("Failed preparing SQL for fetching currencies")?;
343        let records = stmt
344            .query_map([limit, offset], |row| {
345                Ok(
346                    Self {
347                        iso: row.get(0)?,
348                        name: row.get(1)?,
349                        symbol: row.get(2)?,
350                        ..Default::default()
351                    }
352                )
353            })?
354            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
355
356        Ok((Self::count(conn)?, records))
357    }
358
359    fn get(conn: &Connection, key: &str) -> Result<Self> {
360        let mut stmt = conn.prepare_cached(
361           "SELECT iso, name, symbol FROM currencies
362           WHERE iso = ?")
363            .context("Failed preparing SQL for fetching currencies data")?;
364
365        stmt
366            .query_row([key], |row| {
367                Ok(
368                    Self {
369                        iso: row.get(0)?,
370                        name: row.get(1)?,
371                        symbol: row.get(2)?,
372                        ..Default::default()
373                    }
374                )
375            })
376            .context("Failed querying currencies data")
377    }
378}
379
380impl Currency {
381    pub fn save(&self, conn: &mut Connection) -> Result<()> {
382        let Self {
383            iso,
384            name,
385            symbol,
386            ..
387        } = self;
388
389        let mut stmt = conn.prepare_cached("INSERT INTO currencies (iso, name, symbol) VALUES (?1, ?2, ?3)")?;
390        stmt.execute(params![iso, name, symbol])?;
391
392        Ok(())
393    }
394}
395
396//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
397//<<>><======================  WORLD REGION  ========================><<>>//
398//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
399
400#[derive(Clone, Default, Debug, Entity, Label, Serialize, Deserialize)]
401pub struct WorldRegion {
402    pub id: Key<Int>,
403    #[label] pub name: String,
404    pub subregions: Many<WorldSubregion>,
405    pub countries: Many<Country>,
406}
407
408impl Model for WorldRegion {
409    fn count(conn: &Connection) -> Result<usize> {
410        let mut stmt = conn.prepare_cached(
411            "SELECT count(*) FROM world_regions")
412            .context("Failed preparing SQL for fetching world regions count")?;
413
414        stmt
415            .query_row([], |row| {
416                row.get(0)
417            })
418            .context("Failed querying world regions count")
419    }
420
421    fn all(conn: &Connection, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
422        let mut stmt = conn.prepare_cached(
423            "SELECT id, name FROM world_regions
424            LIMIT ?1
425            OFFSET ?2")
426            .context("Failed preparing SQL for fetching world regions")?;
427        let records = stmt
428            .query_map([limit, offset], |row| {
429                Ok(
430                    Self {
431                        id: row.get(0)?,
432                        name: row.get(1)?,
433                        ..Default::default()
434                    }
435                )
436            })?
437            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
438
439        Ok((Self::count(conn)?, records))
440    }
441
442    fn get(conn: &Connection, key: &str) -> Result<Self> {
443        let mut stmt = conn.prepare_cached(
444           "SELECT id, name FROM world_regions
445           WHERE id = ?")
446            .context("Failed preparing SQL for fetching world regions data")?;
447
448        stmt
449            .query_row([key], |row| {
450                Ok(
451                    Self {
452                        id: row.get(0)?,
453                        name: row.get(1)?,
454                        ..Default::default()
455                    }
456                )
457            })
458            .context("Failed querying world regions data")
459    }
460}
461
462impl WorldRegion {
463    pub fn key_with_name(conn: &Connection, name: &str) -> Result<Key<Int>> {
464        let mut stmt = conn.prepare_cached(
465            "SELECT id FROM world_regions
466            WHERE name = ?")?;
467
468        Ok(
469            stmt
470                .query_row([name], |row| {
471                    row.get(0)
472                })
473                .optional()?
474                .into()
475        )
476    }
477}
478
479//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
480//<<>><====================  WORLD SUBREGION  =======================><<>>//
481//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
482
483#[derive(Clone, Default, Debug, Entity, Label, Serialize, Deserialize)]
484pub struct WorldSubregion {
485    pub id: Key<Int>,
486    #[label] pub name: String,
487    pub region: EntityLabelInt<WorldRegion>,
488    pub countries: Many<Country>,
489}
490
491impl Model for WorldSubregion {
492    fn count(conn: &Connection) -> Result<usize> {
493        let mut stmt = conn.prepare_cached(
494            "SELECT count(*) FROM world_subregions")
495            .context("Failed preparing SQL for fetching world subregions count")?;
496
497        stmt
498            .query_row([], |row| {
499                row.get(0)
500            })
501            .context("Failed querying world subregions count")
502    }
503
504    fn all(conn: &Connection, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
505        let mut stmt = conn.prepare_cached(
506            "SELECT sub.id, sub.name, sub.world_region_id, reg.name
507            FROM world_subregions as sub
508            LEFT JOIN world_regions as reg
509            ON sub.world_region_id = reg.id
510            LIMIT ?1
511            OFFSET ?2")
512            .context("Failed preparing SQL for fetching world subregions")?;
513
514        let records = stmt
515            .query_map([limit, offset], |row| {
516                Ok(
517                    Self {
518                        id: row.get(0)?,
519                        name: row.get(1)?,
520                        region: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
521                        ..Default::default()
522                    }
523                )
524            })?
525            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
526
527        Ok((Self::count(conn)?, records))
528    }
529
530    fn get(conn: &Connection, key: &str) -> Result<Self> {
531        let mut stmt = conn.prepare_cached(
532           "SELECT sub.id, sub.name, sub.world_region_id, reg.name
533           FROM world_subregions as sub
534           LEFT JOIN world_regions as reg
535           ON sub.world_region_id = reg.id
536           WHERE sub.id = ?")
537            .context("Failed preparing SQL for fetching world subregions data")?;
538
539        stmt
540            .query_row([key], |row| {
541                Ok(
542                    Self {
543                        id: row.get(0)?,
544                        name: row.get(1)?,
545                        region: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
546                        ..Default::default()
547                    }
548                )
549            })
550            .context("Failed querying world subregions data")
551    }
552}
553
554impl WorldSubregion {
555    pub fn key_with_name(conn: &Connection, name: &str) -> Result<Key<Int>> {
556        let mut stmt = conn.prepare_cached(
557            "SELECT id FROM world_subregions
558            WHERE name = ?")?;
559
560        Ok(
561            stmt
562                .query_row([name], |row| {
563                    row.get(0)
564                })
565                .optional()?
566                .into()
567        )
568    }
569
570    pub fn from_region(conn: &Connection, key: &str, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
571        let mut stmt = conn.prepare_cached(
572            "SELECT sub.id, sub.name, sub.world_region_id, reg.name
573            FROM world_subregions as sub
574            LEFT JOIN world_regions as reg
575            ON sub.world_region_id = reg.id
576            WHERE reg.id = ?1
577            LIMIT ?2
578            OFFSET ?3")
579            .context("Failed preparing SQL for fetching world subregions")?;
580
581        let records = stmt
582            .query_map(params![key, limit, offset], |row| {
583                Ok(
584                    Self {
585                        id: row.get(0)?,
586                        name: row.get(1)?,
587                        region: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
588                        ..Default::default()
589                    }
590                )
591            })?
592            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
593
594        Ok((Self::from_region_count(conn, key)?, records))
595    }
596
597    pub fn from_region_count(conn: &Connection, key: &str) -> Result<usize> {
598        let mut stmt = conn.prepare_cached(
599            "SELECT count(*) FROM world_subregions
600            WHERE world_region_id = ?")
601            .context("Failed preparing SQL for fetching subregions count")?;
602
603        stmt
604            .query_row([key], |row| {
605                row.get(0)
606            })
607            .context("Failed querying subregions count")
608    }
609}
610
611//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
612//<<>><=========================  STATE  ============================><<>>//
613//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
614
615#[derive(Clone, Default, Debug, Entity, Label, Serialize, Deserialize)]
616pub struct State {
617    pub id: Key<Int>,
618    #[label] pub name: String,
619    pub code: String,
620    pub country: EntityLabelString<Country>,
621    pub latitude: Option<f32>,
622    pub longitude: Option<f32>,
623    pub cities: Many<City>,
624}
625
626impl Model for State {
627    fn count(conn: &Connection) -> Result<usize> {
628        let mut stmt = conn.prepare_cached(
629            "SELECT count(*) FROM states")
630            .context("Failed preparing SQL for fetching states count")?;
631
632        stmt
633            .query_row([], |row| {
634                row.get(0)
635            })
636            .context("Failed querying states count")
637    }
638
639    fn all(conn: &Connection, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
640        let mut stmt = conn.prepare_cached(
641            "SELECT id, name, country_id, country
642            FROM states
643            LIMIT ?1
644            OFFSET ?2")
645            .context("Failed preparing SQL for fetching states")?;
646        let records = stmt
647            .query_map([limit, offset], |row| {
648                Ok(
649                    Self {
650                        id: row.get(0)?,
651                        name: row.get(1)?,
652                        country: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
653                        ..Default::default()
654                    }
655                )
656            })?
657            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
658
659        Ok((Self::count(conn)?, records))
660    }
661
662    fn get(conn: &Connection, key: &str) -> Result<Self> {
663        let mut stmt = conn.prepare_cached(
664           "SELECT id, name, code, country_id, country, latitude, longitude
665           FROM states
666           WHERE id = ?")
667            .context("Failed preparing SQL for fetching state data")?;
668
669        stmt
670            .query_row([key], |row| {
671                Ok(
672                    Self {
673                        id: row.get(0)?,
674                        name: row.get(1)?,
675                        code: row.get(2)?,
676                        country: EntityLabel::KeyLabel(row.get(3)?, row.get(4).unwrap_or_default()),
677                        latitude: row.get(5)?,
678                        longitude: row.get(6)?,
679                        ..Default::default()
680                    }
681                )
682            })
683            .context("Failed querying state data")
684    }
685}
686
687impl State {
688    pub fn save(&self, conn: &mut Connection) -> Result<()> {
689        let Self {
690            id,
691            name,
692            code,
693            country,
694            latitude,
695            longitude,
696            ..
697        } = self;
698
699        conn.execute(
700            "INSERT INTO states (id, name, code, country_id, country, latitude, longitude)
701            VALUES (:id, :name, :code, :country_id, :country, :latitude, :longitude)
702            ON CONFLICT(id) DO UPDATE
703            SET
704                name=:name,
705                code=:code,
706                country_id=:country_id,
707                country=:country,
708                latitude=:latitude,
709                longitude=:longitude;",
710            named_params! {
711                ":id": id,
712                ":name": name,
713                ":code": code,
714                ":country_id": country.key().ok(),
715                ":country": country.label().ok(),
716                ":latitude": latitude,
717                ":longitude": longitude,
718            }
719        )?;
720
721        Ok(())
722    }
723
724    pub fn key_with_name(conn: &Connection, name: &str) -> Result<Key<Int>> {
725        let mut stmt = conn.prepare_cached(
726            "SELECT id FROM states
727            WHERE name = ?")?;
728
729        Ok(
730            stmt
731                .query_row([name], |row| {
732                    row.get(0)
733                })
734                .optional()?
735                .into()
736        )
737    }
738
739    pub fn from_country(conn: &Connection, key: &str, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
740        let mut stmt = conn
741            .prepare_cached(
742                "SELECT id, name, country_id, country FROM states
743                WHERE country_id = ?1
744                LIMIT ?2
745                OFFSET ?3")
746            .context("Failed preparing SQL for fetching states")?;
747
748        let records = stmt
749            .query_map(params![key, limit, offset], |row| {
750                Ok(
751                    Self {
752                        id: row.get(0)?,
753                        name: row.get(1)?,
754                        country: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
755                        ..Default::default()
756                    }
757                )
758            })?
759            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
760
761        Ok((Self::from_country_count(conn, key)?, records))
762    }
763
764    pub fn from_country_count(conn: &Connection, key: &str) -> Result<usize> {
765        let mut stmt = conn.prepare_cached(
766            "SELECT count(*) FROM states
767            WHERE country_id = ?")
768            .context("Failed preparing SQL for fetching states count")?;
769
770        stmt
771            .query_row([key], |row| {
772                row.get(0)
773            })
774            .context("Failed querying states count")
775    }
776}
777
778//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
779//<<>><=========================  CITY  =============================><<>>//
780//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
781
782#[derive(Clone, Default, Debug, Entity, Label, Serialize, Deserialize)]
783pub struct City {
784    pub id: Key<Int>,
785    #[label] pub name: String,
786    pub state: EntityLabelInt<State>,
787    pub country: EntityLabelString<Country>,
788    pub latitude: Option<f32>,
789    pub longitude: Option<f32>,
790}
791
792impl Model for City {
793    fn count(conn: &Connection) -> Result<usize> {
794        let mut stmt = conn.prepare_cached(
795            "SELECT count(*) FROM cities")
796            .context("Failed preparing SQL for fetching cities count")?;
797
798        stmt
799            .query_row([], |row| {
800                row.get(0)
801            })
802            .context("Failed querying cities count")
803    }
804
805    fn all(conn: &Connection, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
806        let mut stmt = conn.prepare_cached(
807            "SELECT id, name, state_id, state, country_id, country FROM cities
808            LIMIT ?1
809            OFFSET ?2")
810            .context("Failed preparing SQL for fetching cities")?;
811        let records = stmt
812            .query_map([limit, offset], |row| {
813                Ok(
814                    Self {
815                        id: row.get(0)?,
816                        name: row.get(1)?,
817                        state: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
818                        country: EntityLabel::KeyLabel(row.get(4)?, row.get(5).unwrap_or_default()),
819                        ..Default::default()
820                    }
821                )
822            })?
823            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
824
825        Ok((Self::count(conn)?, records))
826    }
827
828    fn get(conn: &Connection, key: &str) -> Result<Self> {
829        let mut stmt = conn.prepare_cached(
830           "SELECT * FROM cities
831           WHERE id = ?")
832            .context("Failed preparing SQL for fetching city data")?;
833
834        stmt
835            .query_row([key], |row| {
836                Ok(
837                    Self {
838                        id: row.get(0)?,
839                        name: row.get(1)?,
840                        state: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
841                        country: EntityLabel::KeyLabel(row.get(4)?, row.get(5).unwrap_or_default()),
842                        latitude: row.get(6)?,
843                        longitude: row.get(7)?,
844                    }
845                )
846            })
847            .context("Failed querying city data")
848    }
849}
850
851impl City {
852    pub fn save(&self, conn: &mut Connection) -> Result<()> {
853        let Self {
854            id,
855            name,
856            state,
857            country,
858            latitude,
859            longitude,
860        } = self;
861
862        conn.execute(
863            "INSERT INTO cities (id, name, state_id, state, country_id, country, latitude, longitude)
864            VALUES (:id, :name, :state_id, :state, :country_id, :country, :latitude, :longitude)
865            ON CONFLICT(id) DO UPDATE
866            SET
867                name=:name,
868                state_id=:state_id,
869                state=:state,
870                country_id=:country_id,
871                country=:country,
872                latitude=:latitude,
873                longitude=:longitude;",
874            named_params![
875                ":id": id,
876                ":name": name,
877                ":state_id": state.key().ok(),
878                ":state": state.label().ok(),
879                ":country_id": country.key().ok(),
880                ":country": country.label().ok(),
881                ":latitude": latitude,
882                ":longitude": longitude,
883            ]
884        )?;
885
886        Ok(())
887    }
888
889    pub fn from_country(conn: &Connection, key: &str, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
890        let mut stmt = conn
891            .prepare_cached(
892                "SELECT id, name, state_id, state, country_id, country
893                FROM cities
894                WHERE country_id = ?1
895                LIMIT ?2
896                OFFSET ?3")
897            .context("Failed preparing SQL for fetching cities")?;
898
899        let records = stmt
900            .query_map(params![key, limit, offset], |row| {
901                Ok(
902                    Self {
903                        id: row.get(0)?,
904                        name: row.get(1)?,
905                        state: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
906                        country: EntityLabel::KeyLabel(row.get(4)?, row.get(5).unwrap_or_default()),
907                        ..Default::default()
908                    }
909                )
910            })?
911            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
912
913        Ok((Self::from_country_count(conn, key)?, records))
914    }
915
916    pub fn from_state(conn: &Connection, key: &str, limit: usize, offset: usize) -> Result<(usize, Vec<Self>)> {
917        let mut stmt = conn
918            .prepare_cached(
919                "SELECT id, name, state_id, state, country_id, country
920                FROM cities
921                WHERE state_id = ?1
922                LIMIT ?2
923                OFFSET ?3")
924            .context("Failed preparing SQL for fetching cities")?;
925
926        let records = stmt
927            .query_map(params![key, limit, offset], |row| {
928                Ok(
929                    Self {
930                        id: row.get(0)?,
931                        name: row.get(1)?,
932                        state: EntityLabel::KeyLabel(row.get(2)?, row.get(3).unwrap_or_default()),
933                        country: EntityLabel::KeyLabel(row.get(4)?, row.get(5).unwrap_or_default()),
934                        ..Default::default()
935                    }
936                )
937            })?
938            .collect::<Result<Vec<_>, rusqlite::Error>>()?;
939
940        Ok((Self::from_state_count(conn, key)?, records))
941    }
942
943    pub fn from_country_count(conn: &Connection, key: &str) -> Result<usize> {
944        let mut stmt = conn.prepare_cached(
945            "SELECT count(*) FROM cities
946            WHERE country_id = ?")
947            .context("Failed preparing SQL for fetching cities count")?;
948
949        stmt
950            .query_row([key], |row| {
951                row.get(0)
952            })
953            .context("Failed querying cities count")
954    }
955
956    pub fn from_state_count(conn: &Connection, key: &str) -> Result<usize> {
957        let mut stmt = conn.prepare_cached(
958            "SELECT count(*) FROM cities
959            WHERE state_id = ?")
960            .context("Failed preparing SQL for fetching cities count")?;
961
962        stmt
963            .query_row([key], |row| {
964                row.get(0)
965            })
966            .context("Failed querying cities count")
967    }
968}
969
970//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
971//<<>><=========================  URL  ==============================><<>>//
972//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
973
974#[derive(Clone, Debug)]
975pub struct UrlBuilder {
976    url: Url,
977}
978
979impl Default for UrlBuilder {
980    fn default() -> Self {
981        Self {
982            url: "http://127.0.0.1:3000".parse().unwrap(),
983        }
984    }
985}
986
987impl UrlBuilder {
988    pub fn new() -> Self {
989        Default::default()
990    }
991
992    pub fn with_addr(addr: std::net::SocketAddr) -> Result<Self> {
993        Ok(Self { url: format!("http://{}", &addr).parse()? })
994    }
995
996    pub fn with_base(host: &str) -> Self {
997        Self {
998            url: host.parse().unwrap(),
999        }
1000    }
1001
1002    pub fn as_str(&self) -> &str {
1003        self.url.as_ref()
1004    }
1005    // This builder is a bit different from normal ones
1006    // as the 'for' methods make clones of the base builder
1007
1008    pub fn for_metadata(&self) -> Self {
1009        let mut builder = self.clone();
1010        builder.url.set_path("metadata");
1011        builder
1012    }
1013
1014    pub fn for_country(&self, key: &str) -> Self {
1015        let mut builder = self.clone();
1016        builder
1017            .url
1018            .path_segments_mut()
1019            .unwrap()
1020            .extend(&["country", key]);
1021
1022        builder
1023    }
1024
1025    pub fn for_state(&self, key: &str) -> Self {
1026        let mut builder = self.clone();
1027        builder
1028            .url
1029            .path_segments_mut()
1030            .unwrap()
1031            .extend(&["state", key]);
1032
1033        builder
1034    }
1035
1036    pub fn for_city(&self, key: &str) -> Self {
1037        let mut builder = self.clone();
1038        builder
1039            .url
1040            .path_segments_mut()
1041            .unwrap()
1042            .extend(&["city", key]);
1043
1044        builder
1045    }
1046
1047    pub fn for_world_region(&self, key: &str) -> Self {
1048        let mut builder = self.clone();
1049        builder
1050            .url
1051            .path_segments_mut()
1052            .unwrap()
1053            .extend(&["region", key]);
1054
1055        builder
1056    }
1057
1058    pub fn for_world_subregion(&self, key: &str) -> Self {
1059        let mut builder = self.clone();
1060        builder
1061            .url
1062            .path_segments_mut()
1063            .unwrap()
1064            .extend(&["subregion", key]);
1065
1066        builder
1067    }
1068
1069    pub fn for_currency(&self, key: &str) -> Self {
1070        let mut builder = self.clone();
1071        builder
1072            .url
1073            .path_segments_mut()
1074            .unwrap()
1075            .extend(&["currency", key]);
1076
1077        builder
1078    }
1079
1080    pub fn for_countries(&self) -> Self {
1081        let mut builder = self.clone();
1082        builder.url.set_path("countries");
1083        builder
1084    }
1085
1086    pub fn for_states(&self) -> Self {
1087        let mut builder = self.clone();
1088        builder.url.set_path("states");
1089        builder
1090    }
1091
1092    pub fn for_cities(&self) -> Self {
1093        let mut builder = self.clone();
1094        builder.url.set_path("cities");
1095        builder
1096    }
1097
1098    pub fn for_world_regions(&self) -> Self {
1099        let mut builder = self.clone();
1100        builder.url.set_path("regions");
1101        builder
1102    }
1103
1104    pub fn for_world_subregions(&self) -> Self {
1105        let mut builder = self.clone();
1106        builder.url.set_path("subregions");
1107        builder
1108    }
1109
1110    pub fn for_currencies(&self) -> Self {
1111        let mut builder = self.clone();
1112        builder.url.set_path("currencies");
1113        builder
1114    }
1115
1116    pub fn for_countries_from_region(&self, key: &str) -> Self {
1117        let mut builder = self.clone();
1118        builder
1119            .url
1120            .path_segments_mut()
1121            .unwrap()
1122            .extend(&["region", key, "countries"]);
1123
1124        builder
1125    }
1126
1127    pub fn for_countries_from_subregion(&self, key: &str) -> Self {
1128        let mut builder = self.clone();
1129        builder
1130            .url
1131            .path_segments_mut()
1132            .unwrap()
1133            .extend(&["subregion", key, "countries"]);
1134
1135        builder
1136    }
1137
1138    pub fn for_countries_from_currency(&self, key: &str) -> Self {
1139        let mut builder = self.clone();
1140        builder
1141            .url
1142            .path_segments_mut()
1143            .unwrap()
1144            .extend(&["currency", key, "countries"]);
1145
1146        builder
1147    }
1148
1149    pub fn for_states_from_country(&self, key: &str) -> Self {
1150        let mut builder = self.clone();
1151        builder
1152            .url
1153            .path_segments_mut()
1154            .unwrap()
1155            .extend(&["country", key, "states"]);
1156
1157        builder
1158    }
1159
1160    pub fn for_cities_from_country(&self, key: &str) -> Self {
1161        let mut builder = self.clone();
1162        builder
1163            .url
1164            .path_segments_mut()
1165            .unwrap()
1166            .extend(&["country", key, "cities"]);
1167
1168        builder
1169    }
1170
1171    pub fn for_cities_from_state(&self, key: &str) -> Self {
1172        let mut builder = self.clone();
1173        builder
1174            .url
1175            .path_segments_mut()
1176            .unwrap()
1177            .extend(&["state", key, "cities"]);
1178
1179        builder
1180    }
1181
1182    pub fn for_subregions_from_region(&self, key: &str) -> Self {
1183        let mut builder = self.clone();
1184        builder
1185            .url
1186            .path_segments_mut()
1187            .unwrap()
1188            .extend(&["region", key, "subregions"]);
1189
1190        builder
1191    }
1192
1193    pub fn with_pagination(mut self, page: usize, limit: usize) -> Self {
1194        self.url
1195            .query_pairs_mut()
1196            .append_pair("page", &page.to_string())
1197            .append_pair("limit", &limit.to_string());
1198        self
1199    }
1200
1201    pub fn build(self) -> String {
1202        self.url.into()
1203    }
1204
1205    pub fn path(self) -> String {
1206        self.url.path().into()
1207    }
1208}
1209
1210//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
1211//<<>><======================  PROTOCOLS  ===========================><<>>//
1212//<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>//
1213
1214#[derive(Clone, Debug, Serialize, Deserialize)]
1215pub struct Metadata {
1216    pub version: String,
1217    pub countries: usize,
1218    pub states: usize,
1219    pub cities: usize,
1220    pub regions: usize,
1221    pub subregions: usize,
1222    pub currencies: usize,
1223}
world_tables_base/lib.rs

world_tables_base/
lib.rs
