1use crate::webserver::ErrorWithStatus;
2use crate::webserver::{make_placeholder, Database};
3use crate::{AppState, TEMPLATES_DIR};
4use anyhow::Context;
5use chrono::{DateTime, Utc};
6use sqlx::any::{AnyKind, AnyStatement, AnyTypeInfo};
7use sqlx::postgres::types::PgTimeTz;
8use sqlx::{Postgres, Statement, Type};
9use std::io::ErrorKind;
10use std::path::{Component, Path, PathBuf};
11
12pub(crate) struct FileSystem {
13 local_root: PathBuf,
14 db_fs_queries: Option<DbFsQueries>,
15}
16
17impl FileSystem {
18 pub async fn init(local_root: impl Into<PathBuf>, db: &Database) -> Self {
19 Self {
20 local_root: local_root.into(),
21 db_fs_queries: match DbFsQueries::init(db).await {
22 Ok(q) => Some(q),
23 Err(e) => {
24 log::debug!(
25 "Using local filesystem only, could not initialize on-database filesystem. \
26 You can host sql files directly in your database by creating the following table: \n\
27 {} \n\
28 The error while trying to use the database file system is: {e:#}",
29 DbFsQueries::get_create_table_sql(db.connection.any_kind())
30 );
31 None
32 }
33 },
34 }
35 }
36
37 pub async fn modified_since(
38 &self,
39 app_state: &AppState,
40 path: &Path,
41 since: DateTime<Utc>,
42 priviledged: bool,
43 ) -> anyhow::Result<bool> {
44 let local_path = self.safe_local_path(app_state, path, priviledged)?;
45 let local_result = file_modified_since_local(&local_path, since).await;
46 match (local_result, &self.db_fs_queries) {
47 (Ok(modified), _) => Ok(modified),
48 (Err(e), Some(db_fs)) if e.kind() == ErrorKind::NotFound => {
49 db_fs
51 .file_modified_since_in_db(app_state, path, since)
52 .await
53 }
54 (Err(e), _) => Err(e).with_context(|| {
55 format!("Unable to read local file metadata for {}", path.display())
56 }),
57 }
58 }
59
60 pub async fn read_to_string(
61 &self,
62 app_state: &AppState,
63 path: &Path,
64 priviledged: bool,
65 ) -> anyhow::Result<String> {
66 let bytes = self.read_file(app_state, path, priviledged).await?;
67 String::from_utf8(bytes).with_context(|| {
68 format!(
69 "The file at {} contains invalid UTF8 characters",
70 path.display()
71 )
72 })
73 }
74
75 pub async fn read_file(
79 &self,
80 app_state: &AppState,
81 path: &Path,
82 priviledged: bool,
83 ) -> anyhow::Result<Vec<u8>> {
84 let local_path = self.safe_local_path(app_state, path, priviledged)?;
85 log::debug!(
86 "Reading file {} from {}",
87 path.display(),
88 local_path.display()
89 );
90 let local_result = tokio::fs::read(&local_path).await;
91 match (local_result, &self.db_fs_queries) {
92 (Ok(f), _) => Ok(f),
93 (Err(e), Some(db_fs)) if e.kind() == ErrorKind::NotFound => {
94 db_fs.read_file(app_state, path.as_ref()).await
96 }
97 (Err(e), None) if e.kind() == ErrorKind::NotFound => Err(ErrorWithStatus {
98 status: actix_web::http::StatusCode::NOT_FOUND,
99 }
100 .into()),
101 (Err(e), _) => {
102 Err(e).with_context(|| format!("Unable to read local file {}", path.display()))
103 }
104 }
105 }
106
107 fn safe_local_path(
108 &self,
109 app_state: &AppState,
110 path: &Path,
111 priviledged: bool,
112 ) -> anyhow::Result<PathBuf> {
113 if priviledged {
114 if let Ok(template_path) = path.strip_prefix(TEMPLATES_DIR) {
117 let normalized = app_state
118 .config
119 .configuration_directory
120 .join("templates")
121 .join(template_path);
122 log::trace!(
123 "Normalizing template path {} to {}",
124 path.display(),
125 normalized.display()
126 );
127 return Ok(normalized);
128 }
129 } else {
130 for (i, component) in path.components().enumerate() {
131 if let Component::Normal(c) = component {
132 if i == 0 && c.eq_ignore_ascii_case("sqlpage") {
133 anyhow::bail!(ErrorWithStatus {
134 status: actix_web::http::StatusCode::FORBIDDEN,
135 });
136 }
137 if c.as_encoded_bytes().starts_with(b".") {
138 anyhow::bail!(ErrorWithStatus {
139 status: actix_web::http::StatusCode::FORBIDDEN,
140 });
141 }
142 } else {
143 anyhow::bail!(
144 "Unsupported path: {path:?}. Path component '{component:?}' is not allowed."
145 );
146 }
147 }
148 }
149 Ok(self.local_root.join(path))
150 }
151
152 pub(crate) async fn file_exists(
153 &self,
154 app_state: &AppState,
155 path: &Path,
156 ) -> anyhow::Result<bool> {
157 let local_exists = match self.safe_local_path(app_state, path, false) {
158 Ok(safe_path) => tokio::fs::try_exists(safe_path).await?,
159 Err(e) => return Err(e),
160 };
161
162 if !local_exists {
164 log::debug!(
165 "File {} not found in local filesystem, checking database",
166 path.display()
167 );
168 if let Some(db_fs) = &self.db_fs_queries {
169 return db_fs.file_exists(app_state, path).await;
170 }
171 }
172 Ok(local_exists)
173 }
174}
175
176async fn file_modified_since_local(path: &Path, since: DateTime<Utc>) -> tokio::io::Result<bool> {
177 tokio::fs::metadata(path)
178 .await
179 .and_then(|m| m.modified())
180 .map(|modified_at| DateTime::<Utc>::from(modified_at) > since)
181}
182
183pub struct DbFsQueries {
184 was_modified: AnyStatement<'static>,
185 read_file: AnyStatement<'static>,
186 exists: AnyStatement<'static>,
187}
188
189impl DbFsQueries {
190 #[must_use]
191 pub fn get_create_table_sql(db_kind: AnyKind) -> &'static str {
192 match db_kind {
193 AnyKind::Mssql => "CREATE TABLE sqlpage_files(path NVARCHAR(255) NOT NULL PRIMARY KEY, contents VARBINARY(MAX), last_modified DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP);",
194 AnyKind::Postgres => "CREATE TABLE IF NOT EXISTS sqlpage_files(path VARCHAR(255) NOT NULL PRIMARY KEY, contents BYTEA, last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP);",
195 _ => "CREATE TABLE IF NOT EXISTS sqlpage_files(path VARCHAR(255) NOT NULL PRIMARY KEY, contents BLOB, last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP);",
196 }
197 }
198
199 async fn init(db: &Database) -> anyhow::Result<Self> {
200 log::debug!("Initializing database filesystem queries");
201 let db_kind = db.connection.any_kind();
202 Ok(Self {
203 was_modified: Self::make_was_modified_query(db, db_kind).await?,
204 read_file: Self::make_read_file_query(db, db_kind).await?,
205 exists: Self::make_exists_query(db, db_kind).await?,
206 })
207 }
208
209 async fn make_was_modified_query(
210 db: &Database,
211 db_kind: AnyKind,
212 ) -> anyhow::Result<AnyStatement<'static>> {
213 let was_modified_query = format!(
214 "SELECT 1 from sqlpage_files WHERE last_modified >= {} AND path = {}",
215 make_placeholder(db_kind, 1),
216 make_placeholder(db_kind, 2)
217 );
218 let param_types: &[AnyTypeInfo; 2] = &[
219 PgTimeTz::type_info().into(),
220 <str as Type<Postgres>>::type_info().into(),
221 ];
222 log::debug!("Preparing the database filesystem was_modified_query: {was_modified_query}");
223 db.prepare_with(&was_modified_query, param_types).await
224 }
225
226 async fn make_read_file_query(
227 db: &Database,
228 db_kind: AnyKind,
229 ) -> anyhow::Result<AnyStatement<'static>> {
230 let read_file_query = format!(
231 "SELECT contents from sqlpage_files WHERE path = {}",
232 make_placeholder(db_kind, 1),
233 );
234 let param_types: &[AnyTypeInfo; 1] = &[<str as Type<Postgres>>::type_info().into()];
235 log::debug!("Preparing the database filesystem read_file_query: {read_file_query}");
236 db.prepare_with(&read_file_query, param_types).await
237 }
238
239 async fn make_exists_query(
240 db: &Database,
241 db_kind: AnyKind,
242 ) -> anyhow::Result<AnyStatement<'static>> {
243 let exists_query = format!(
244 "SELECT 1 from sqlpage_files WHERE path = {}",
245 make_placeholder(db_kind, 1),
246 );
247 let param_types: &[AnyTypeInfo; 1] = &[<str as Type<Postgres>>::type_info().into()];
248 db.prepare_with(&exists_query, param_types).await
249 }
250
251 async fn file_modified_since_in_db(
252 &self,
253 app_state: &AppState,
254 path: &Path,
255 since: DateTime<Utc>,
256 ) -> anyhow::Result<bool> {
257 let query = self
258 .was_modified
259 .query_as::<(i32,)>()
260 .bind(since)
261 .bind(path.display().to_string());
262 log::trace!(
263 "Checking if file {} was modified since {} by executing query: \n\
264 {}\n\
265 with parameters: {:?}",
266 path.display(),
267 since,
268 self.was_modified.sql(),
269 (since, path)
270 );
271 query
272 .fetch_optional(&app_state.db.connection)
273 .await
274 .map(|modified| modified == Some((1,)))
275 .with_context(|| {
276 format!(
277 "Unable to check when {} was last modified in the database",
278 path.display()
279 )
280 })
281 }
282
283 async fn read_file(&self, app_state: &AppState, path: &Path) -> anyhow::Result<Vec<u8>> {
284 log::debug!("Reading file {} from the database", path.display());
285 self.read_file
286 .query_as::<(Vec<u8>,)>()
287 .bind(path.display().to_string())
288 .fetch_optional(&app_state.db.connection)
289 .await
290 .map_err(anyhow::Error::from)
291 .and_then(|modified| {
292 if let Some((modified,)) = modified {
293 Ok(modified)
294 } else {
295 Err(ErrorWithStatus {
296 status: actix_web::http::StatusCode::NOT_FOUND,
297 }
298 .into())
299 }
300 })
301 .with_context(|| format!("Unable to read {} from the database", path.display()))
302 }
303
304 async fn file_exists(&self, app_state: &AppState, path: &Path) -> anyhow::Result<bool> {
305 let query = self
306 .exists
307 .query_as::<(i32,)>()
308 .bind(path.display().to_string());
309 log::trace!(
310 "Checking if file {} exists by executing query: \n\
311 {}\n\
312 with parameters: {:?}",
313 path.display(),
314 self.exists.sql(),
315 (path,)
316 );
317 let result = query.fetch_optional(&app_state.db.connection).await;
318 log::debug!("DB File exists result: {result:?}");
319 result.map(|result| result.is_some()).with_context(|| {
320 format!(
321 "Unable to check if {} exists in the database",
322 path.display()
323 )
324 })
325 }
326}
327
328#[actix_web::test]
329async fn test_sql_file_read_utf8() -> anyhow::Result<()> {
330 use crate::app_config;
331 use sqlx::Executor;
332 let config = app_config::tests::test_config();
333 let state = AppState::init(&config).await?;
334 let create_table_sql = DbFsQueries::get_create_table_sql(state.db.connection.any_kind());
335 state
336 .db
337 .connection
338 .execute(format!("DROP TABLE IF EXISTS sqlpage_files; {create_table_sql}").as_str())
339 .await?;
340
341 let db_kind = state.db.connection.any_kind();
342 let insert_sql = format!(
343 "INSERT INTO sqlpage_files(path, contents) VALUES ({}, {})",
344 make_placeholder(db_kind, 1),
345 make_placeholder(db_kind, 2)
346 );
347 sqlx::query(&insert_sql)
348 .bind("unit test file.txt")
349 .bind("Héllö world! 😀".as_bytes())
350 .execute(&state.db.connection)
351 .await?;
352
353 let fs = FileSystem::init("/", &state.db).await;
354 let actual = fs
355 .read_to_string(&state, "unit test file.txt".as_ref(), false)
356 .await?;
357 assert_eq!(actual, "Héllö world! 😀");
358
359 let one_hour_ago = Utc::now() - chrono::Duration::hours(1);
360 let one_hour_future = Utc::now() + chrono::Duration::hours(1);
361
362 let was_modified = fs
363 .modified_since(&state, "unit test file.txt".as_ref(), one_hour_ago, false)
364 .await?;
365 assert!(was_modified, "File should be modified since one hour ago");
366
367 let was_modified = fs
368 .modified_since(
369 &state,
370 "unit test file.txt".as_ref(),
371 one_hour_future,
372 false,
373 )
374 .await?;
375 assert!(
376 !was_modified,
377 "File should not be modified since one hour in the future"
378 );
379
380 Ok(())
381}