1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
|
use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::FromRow;
use utoipa::ToSchema;
#[derive(Debug, Serialize, Deserialize, FromRow, Clone, PartialEq, ToSchema)]
pub struct WeatherApiData {
pub id: i64,
pub user_id: i64,
pub location_id: i64,
pub api_type: String,
pub data: String, // JSON data from the API
#[sqlx(rename = "fetched_at")]
pub fetched_at: String, // Store as string, convert when needed
}
impl WeatherApiData {
#[allow(dead_code)]
pub fn fetched_at_datetime(&self) -> Result<DateTime<Utc>, chrono::ParseError> {
DateTime::parse_from_rfc3339(&self.fetched_at).map(|dt| dt.with_timezone(&Utc))
}
}
pub struct WeatherApiDataRepository<'a> {
pub db: &'a sqlx::SqlitePool,
}
impl<'a> WeatherApiDataRepository<'a> {
pub async fn create_weather_data(
&self,
user_id: i64,
location_id: i64,
api_type: String,
data: String,
) -> Result<WeatherApiData, sqlx::Error> {
sqlx::query_as::<_, WeatherApiData>(
"INSERT INTO weather_api_data (user_id, location_id, api_type, data) VALUES (?, ?, ?, ?) RETURNING id, user_id, location_id, api_type, data, fetched_at"
)
.bind(user_id)
.bind(location_id)
.bind(api_type)
.bind(data)
.fetch_one(self.db)
.await
}
#[allow(dead_code)]
pub async fn get_latest_weather_data(
&self,
user_id: i64,
location_id: i64,
api_type: &str,
) -> Result<Option<WeatherApiData>, sqlx::Error> {
sqlx::query_as::<_, WeatherApiData>(
"SELECT id, user_id, location_id, api_type, data, fetched_at FROM weather_api_data WHERE user_id = ? AND location_id = ? AND api_type = ? ORDER BY fetched_at DESC LIMIT 1"
)
.bind(user_id)
.bind(location_id)
.bind(api_type)
.fetch_optional(self.db)
.await
}
#[allow(dead_code)]
pub async fn list_weather_data_by_user(
&self,
user_id: i64,
limit: Option<i64>,
) -> Result<Vec<WeatherApiData>, sqlx::Error> {
let limit = limit.unwrap_or(100);
sqlx::query_as::<_, WeatherApiData>(
"SELECT id, user_id, location_id, api_type, data, fetched_at FROM weather_api_data WHERE user_id = ? ORDER BY fetched_at DESC LIMIT ?"
)
.bind(user_id)
.bind(limit)
.fetch_all(self.db)
.await
}
#[allow(dead_code)]
pub async fn delete_old_weather_data(&self, days_old: i64) -> Result<u64, sqlx::Error> {
let result = sqlx::query(
"DELETE FROM weather_api_data WHERE fetched_at < datetime('now', '-{} days')",
)
.bind(days_old)
.execute(self.db)
.await?;
Ok(result.rows_affected())
}
}
#[cfg(test)]
mod tests {
use super::*;
use crate::locations::LocationRepository;
use crate::users::{UserRepository, UserRole};
use sqlx::{Executor, SqlitePool};
async fn setup_db() -> SqlitePool {
let pool = SqlitePool::connect(":memory:").await.unwrap();
pool.execute(
"CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL UNIQUE,
role TEXT NOT NULL DEFAULT 'user'
);",
)
.await
.unwrap();
pool.execute(
"CREATE TABLE locations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
latitude REAL NOT NULL,
longitude REAL NOT NULL,
user_id INTEGER NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE NO ACTION
);",
)
.await
.unwrap();
pool.execute(
"CREATE TABLE weather_api_data (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
location_id INTEGER NOT NULL,
api_type TEXT NOT NULL DEFAULT 'openweathermap',
data TEXT NOT NULL,
fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(location_id) REFERENCES locations(id) ON DELETE CASCADE
);",
)
.await
.unwrap();
pool
}
async fn create_user(pool: &SqlitePool) -> i64 {
let repo = UserRepository { db: pool };
let user = repo
.create_user(Some("test_user".to_string()), Some(UserRole::User))
.await
.unwrap();
user.id
}
async fn create_location(pool: &SqlitePool, user_id: i64) -> i64 {
let repo = LocationRepository { db: pool };
let location = repo
.create_location(60.1699, 24.9384, user_id) // Helsinki coordinates
.await
.unwrap();
location.id
}
#[tokio::test]
async fn test_create_and_get_weather_data() {
let pool = setup_db().await;
let user_id = create_user(&pool).await;
let location_id = create_location(&pool, user_id).await;
let repo = WeatherApiDataRepository { db: &pool };
let test_data = r#"{"temp": 20.5, "humidity": 65}"#.to_string();
let weather_data = repo
.create_weather_data(
user_id,
location_id,
"openweathermap".to_string(),
test_data.clone(),
)
.await
.unwrap();
assert_eq!(weather_data.user_id, user_id);
assert_eq!(weather_data.location_id, location_id);
assert_eq!(weather_data.api_type, "openweathermap");
assert_eq!(weather_data.data, test_data);
let latest = repo
.get_latest_weather_data(user_id, location_id, "openweathermap")
.await
.unwrap()
.unwrap();
assert_eq!(latest.id, weather_data.id);
assert_eq!(latest.data, test_data);
}
#[tokio::test]
async fn test_list_weather_data_by_user() {
let pool = setup_db().await;
let user_id = create_user(&pool).await;
let location_id = create_location(&pool, user_id).await;
let repo = WeatherApiDataRepository { db: &pool };
let test_data1 = r#"{"temp": 20.5}"#.to_string();
let test_data2 = r#"{"temp": 22.0}"#.to_string();
repo.create_weather_data(
user_id,
location_id,
"openweathermap".to_string(),
test_data1.clone(),
)
.await
.unwrap();
repo.create_weather_data(
user_id,
location_id,
"openweathermap".to_string(),
test_data2.clone(),
)
.await
.unwrap();
let data_list = repo
.list_weather_data_by_user(user_id, Some(10))
.await
.unwrap();
assert_eq!(data_list.len(), 2);
// Check that both data entries are present (order may vary)
let data_values: Vec<&str> = data_list.iter().map(|d| d.data.as_str()).collect();
assert!(data_values.contains(&test_data1.as_str()));
assert!(data_values.contains(&test_data2.as_str()));
}
}
|
