1use super::*;
2use crate::geo::{GeoBounds, GeoFilter, WORLD_WIDTH};
3
4pub(super) const SORT_THRESHOLD: usize = 4096;
5
6pub(super) fn migrate(conn: &Connection) -> Result<()> {
7 conn.execute_batch(
8 "ALTER TABLE stations ADD COLUMN geo_x INTEGER;
9 ALTER TABLE stations ADD COLUMN geo_y INTEGER;
10 CREATE INDEX station_geo ON stations(geo_x,geo_y) WHERE active=1 AND geo_x IS NOT NULL;
11 INSERT INTO meta(key,value) VALUES ('geo_revision','1');",
12 )?;
13 let mut read = conn.prepare("SELECT rowid,json FROM stations WHERE json_extract(json,'$.geo_lat') IS NOT NULL AND json_extract(json,'$.geo_long') IS NOT NULL")?;
14 let mut write = conn.prepare("UPDATE stations SET geo_x=?2,geo_y=?3 WHERE rowid=?1")?;
15 for row in read.query_map([], |r| Ok((r.get::<_, i64>(0)?, r.get::<_, String>(1)?)))? {
16 let (id, json) = row?;
17 let s: Station = serde_json::from_str(&json)?;
18 if let Some((x, y)) = crate::geo::coordinates(s.geo_lat, s.geo_long) {
19 write.execute(params![id, x, y])?;
20 }
21 }
22 Ok(())
23}
24pub(super) fn changed(conn: &Connection) -> Result<()> {
25 conn.execute(
26 "UPDATE meta SET value=CAST(value AS INTEGER)+1 WHERE key='geo_revision'",
27 [],
28 )?;
29 Ok(())
30}
31pub(super) fn predicate(b: GeoBounds, args: &mut Vec<Value>) -> String {
32 args.extend([b.y.into(), (b.y + b.height).into()]);
33 let mut sql = "s.geo_x IS NOT NULL AND s.geo_y>=? AND s.geo_y<?".to_owned();
34 if b.width < WORLD_WIDTH {
35 args.push(b.x.into());
36 args.push(((b.x + b.width) % WORLD_WIDTH).into());
37 sql.push_str(if b.x + b.width < WORLD_WIDTH {
38 " AND (s.geo_x>=? AND s.geo_x<?)"
39 } else {
40 " AND (s.geo_x>=? OR s.geo_x<?)"
41 });
42 }
43 sql
44}
45pub(super) fn plain(f: &Filter) -> bool {
46 f.query.trim().is_empty()
47 && f.country.is_none()
48 && f.language.is_none()
49 && f.tag.is_none()
50 && f.min_bitrate == 0
51 && !f.custom
52 && !f.favorites
53 && !f.history
54}
55impl Store {
56 pub fn geo_revision(&self) -> Result<u64> {
57 Ok(self.conn.query_row(
58 "SELECT CAST(value AS INTEGER) FROM meta WHERE key='geo_revision'",
59 [],
60 |r| r.get(0),
61 )?)
62 }
63 pub fn geo_counts(
65 &self,
66 filter: &Filter,
67 bounds: GeoBounds,
68 columns: usize,
69 rows: usize,
70 mut cancelled: impl FnMut() -> bool,
71 ) -> Result<Vec<u32>> {
72 anyhow::ensure!(
73 (1..=320).contains(&columns) && (1..=80).contains(&rows),
74 "invalid map size"
75 );
76 let mut f = filter.clone();
77 f.geo = Some(GeoFilter::Cell(bounds));
78 let plain = plain(&f);
79 let (conditions, args, source) = if plain {
80 let mut args = vec![];
81 (
82 format!("s.active=1 AND {}", predicate(bounds, &mut args)),
83 args,
84 "stations s INDEXED BY station_geo",
85 )
86 } else {
87 self.conditions(&f)
88 };
89 let mut stmt = self.conn.prepare_cached(&format!(
90 "SELECT s.geo_x,s.geo_y FROM {source} WHERE {conditions}"
91 ))?;
92 let mut points = stmt.query(params_from_iter(args))?;
93 let mut grid = vec![0u32; columns * rows];
94 let mut n = 0usize;
95 while let Some(row) = points.next()? {
96 if n.is_multiple_of(1024) && cancelled() {
97 bail!(DomainError::new(ErrorCode::Cancelled));
98 }
99 n += 1;
100 if let Some((x, y)) = bounds.project(row.get(0)?, row.get(1)?, columns, rows) {
101 grid[y * columns + x] = grid[y * columns + x].saturating_add(1);
102 }
103 }
104 Ok(grid)
105 }
106 pub fn geo_missing_countries(&self, filter: &Filter) -> Result<Vec<crate::model::Facet>> {
107 let mut f = filter.clone();
108 f.geo = Some(GeoFilter::Missing);
109 f.country = None;
110 let (conditions, args, source) = self.conditions(&f);
111 let mut stmt = self.conn.prepare_cached(&format!(
112 "SELECT s.country,count(*) FROM {source} WHERE {conditions} \
113 GROUP BY s.country ORDER BY s.country LIMIT 1000"
114 ))?;
115 Ok(stmt
116 .query_map(params_from_iter(args), |r| {
117 Ok(crate::model::Facet {
118 value: r.get(0)?,
119 count: r.get(1)?,
120 })
121 })?
122 .collect::<rusqlite::Result<_>>()?)
123 }
124}