feat: [#469] add update datetime for tracker stasts importation

josecelano · josecelano · commit feffd09a03c8 · 2024-03-13T12:47:24.000Z
Add a new column to the table `torrust_torrent_tracker_stats` with the
datetime of the update date, which means the date when the stats
(leechers and seeders) were synchronized from the tracker API.
diff --git a/migrations/mysql/20240312130530_torrust_add_update_data_to_tracker_stats.sql b/migrations/mysql/20240312130530_torrust_add_update_data_to_tracker_stats.sql
@@ -0,0 +1,4 @@
+-- New field to track when stats were updated from the tracker
+ALTER TABLE torrust_torrent_tracker_stats ADD COLUMN updated_at DATETIME DEFAULT NULL;
+UPDATE torrust_torrent_tracker_stats SET updated_at = '1000-01-01 00:00:00';
+ALTER TABLE torrust_torrent_tracker_stats MODIFY COLUMN updated_at DATETIME NOT NULL;
diff --git a/migrations/sqlite3/20240312130530_torrust_add_update_data_to_tracker_stats.sql b/migrations/sqlite3/20240312130530_torrust_add_update_data_to_tracker_stats.sql
@@ -0,0 +1,2 @@
+-- New field to track when stats were updated from the tracker
+ALTER TABLE torrust_torrent_tracker_stats ADD COLUMN updated_at TEXT DEFAULT "1000-01-01 00:00:00";
diff --git a/src/databases/mysql.rs b/src/databases/mysql.rs
@@ -20,7 +20,7 @@ use crate::models::torrent_tag::{TagId, TorrentTag};
 use crate::models::tracker_key::TrackerKey;
 use crate::models::user::{User, UserAuthentication, UserCompact, UserId, UserProfile};
 use crate::services::torrent::{CanonicalInfoHashGroup, DbTorrentInfoHash};
-use crate::utils::clock;
+use crate::utils::clock::{self, datetime_now};
 use crate::utils::hex::from_bytes;
 
 pub struct Mysql {
@@ -1055,11 +1055,12 @@ impl Database for Mysql {
         seeders: i64,
         leechers: i64,
     ) -> Result<(), database::Error> {
-        query("REPLACE INTO torrust_torrent_tracker_stats (torrent_id, tracker_url, seeders, leechers) VALUES (?, ?, ?, ?)")
+        query("REPLACE INTO torrust_torrent_tracker_stats (torrent_id, tracker_url, seeders, leechers, updated_at) VALUES (?, ?, ?, ?, ?)")
             .bind(torrent_id)
             .bind(tracker_url)
             .bind(seeders)
             .bind(leechers)
+            .bind(datetime_now())
             .execute(&self.pool)
             .await
             .map(|_| ())
diff --git a/src/databases/sqlite.rs b/src/databases/sqlite.rs
@@ -20,7 +20,7 @@ use crate::models::torrent_tag::{TagId, TorrentTag};
 use crate::models::tracker_key::TrackerKey;
 use crate::models::user::{User, UserAuthentication, UserCompact, UserId, UserProfile};
 use crate::services::torrent::{CanonicalInfoHashGroup, DbTorrentInfoHash};
-use crate::utils::clock;
+use crate::utils::clock::{self, datetime_now};
 use crate::utils::hex::from_bytes;
 
 pub struct Sqlite {
@@ -1047,11 +1047,12 @@ impl Database for Sqlite {
         seeders: i64,
         leechers: i64,
     ) -> Result<(), database::Error> {
-        query("REPLACE INTO torrust_torrent_tracker_stats (torrent_id, tracker_url, seeders, leechers) VALUES ($1, $2, $3, $4)")
+        query("REPLACE INTO torrust_torrent_tracker_stats (torrent_id, tracker_url, seeders, leechers, updated_at) VALUES ($1, $2, $3, $4, $5)")
             .bind(torrent_id)
             .bind(tracker_url)
             .bind(seeders)
             .bind(leechers)
+            .bind(datetime_now())
             .execute(&self.pool)
             .await
             .map(|_| ())
diff --git a/src/utils/clock.rs b/src/utils/clock.rs
@@ -1,3 +1,5 @@
+use chrono::Utc;
+
 /// Returns the current timestamp in seconds.
 ///
 /// # Panics
@@ -8,3 +10,11 @@
 pub fn now() -> u64 {
     u64::try_from(chrono::prelude::Utc::now().timestamp()).expect("timestamp should be positive")
 }
+
+/// Returns the current time in database format.
+///
+/// For example: `2024-03-12 15:56:24`.
+#[must_use]
+pub fn datetime_now() -> String {
+    Utc::now().format("%Y-%m-%d %H:%M:%S").to_string()
+}

Original file line number	Diff line number	Diff line change
`@@ -0,0 +1,2 @@`
	`1`	`+-- New field to track when stats were updated from the tracker`
	`2`	`+ALTER TABLE torrust_torrent_tracker_stats ADD COLUMN updated_at TEXT DEFAULT "1000-01-01 00:00:00";`