Mercurial > lbo > hg > analyrics

changeset 71:eade8c0055bf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix timezone handling
author Lewin Bormann <lbo@spheniscida.de>
date Wed, 17 Aug 2022 17:46:40 +0200
parents e76d019d95d2
children b9d9b4508898
files assets/settings.html.hbs src/configdb.rs src/logsdb.rs src/main.rs
diffstat 4 files changed, 43 insertions(+), 43 deletions(-) [+]
line wrap: on
line diff
--- a/assets/settings.html.hbs	Mon Aug 01 09:45:39 2022 -0700
+++ b/assets/settings.html.hbs	Wed Aug 17 17:46:40 2022 +0200
@@ -41,5 +41,16 @@
         <input type="submit" value="Save" />
     </form>
 
+    {{ #if admin.active }}
+    <h2 style="color: darkred">Administration</h2>
+    <form id="pwform" action="{{thispath}}" method="GET">
+        <label for="configure_user">Manage user:</label>
+        <input type="text" name="configure_user" value="{{{admin.configure_user}}}" />
+        <input type="submit" value="Manage..." />
+    </form>
+
+
+    {{/if}}
+
     </body>
 </html>
--- a/src/configdb.rs	Mon Aug 01 09:45:39 2022 -0700
+++ b/src/configdb.rs	Wed Aug 17 17:46:40 2022 +0200
@@ -104,4 +104,12 @@
             .await?;
         Ok(())
     }
+
+    pub async fn is_admin<S0: AsRef<str>>(&mut self, user: S0) -> Result<bool, Error> {
+        Ok(sqlx::query("SELECT is_admin FROM users JOIN permissions ON (users.id = permissions.user_id) WHERE username = ?")
+            .bind(user.as_ref())
+            .fetch_one(&mut *self.0)
+            .await?
+            .get(0))
+    }
 }
--- a/src/logsdb.rs	Mon Aug 01 09:45:39 2022 -0700
+++ b/src/logsdb.rs	Wed Aug 17 17:46:40 2022 +0200
@@ -23,7 +23,6 @@
     pub domain: Option<String>,
     pub from: OffsetDateTime,
     pub to: OffsetDateTime,
-    pub tz_offset: i64,
     pub include_bots: bool,
 }
 
@@ -133,28 +132,23 @@
         ctx: &LogsQueryContext,
     ) -> Result<(Vec<String>, Vec<u32>, Vec<u32>), Error> {
         let domain = ctx.domain.as_ref().map(String::as_str).unwrap_or("%");
-        let tz_offset = ctx.tz_offset;
         let include_bots = if ctx.include_bots { 1 } else { 0 };
         let mut results = sqlx::query(
             r#"
-SELECT DATE(atime + ?, 'unixepoch') AS rqdate, COUNT(requestlog.id) AS rqcount, sesscount
+SELECT DATE(atime, 'unixepoch') AS rqdate, COUNT(requestlog.id) AS rqcount, sesscount
 FROM requestlog
 JOIN (
-    SELECT DATE(start + ?, 'unixepoch') AS sessdate, COUNT(*) AS sesscount
+    SELECT DATE(start, 'unixepoch') AS sessdate, COUNT(*) AS sesscount
     FROM sessions WHERE sessions.domain LIKE ? AND sessions.is_bot <= ? GROUP BY sessdate)
 AS sc ON (rqdate = sessdate)
 JOIN sessions ON (sessions.id = requestlog.session)
-WHERE atime+? > ? AND atime+? < ? AND requestlog.domain LIKE ? AND sessions.is_bot <= ?
+WHERE atime > ? AND atime < ? AND requestlog.domain LIKE ? AND sessions.is_bot <= ?
 GROUP BY rqdate
 ORDER BY rqdate ASC;"#,
         )
-        .bind(tz_offset)
-        .bind(tz_offset)
         .bind(domain)
         .bind(include_bots)
-        .bind(tz_offset)
         .bind(ctx.from.unix_timestamp())
-        .bind(tz_offset)
         .bind(ctx.to.unix_timestamp())
         .bind(domain)
         .bind(include_bots)
@@ -187,19 +181,16 @@
         &mut self,
         ctx: &LogsQueryContext,
     ) -> Result<Vec<(String, i64)>, Error> {
-        let tz_offset = ctx.tz_offset;
         let include_bots = if ctx.include_bots { 1 } else { 0 };
         let result = sqlx::query(
             r#"
 SELECT origin_country, COUNT(*) AS count
 FROM sessions
-WHERE start+? > ? AND start+? < ? AND domain LIKE ? AND is_bot <= ?
+WHERE start > ? AND start < ? AND domain LIKE ? AND is_bot <= ?
 GROUP BY origin_country
 ORDER BY count DESC;"#,
         )
-        .bind(tz_offset)
         .bind(ctx.from.unix_timestamp())
-        .bind(tz_offset)
         .bind(ctx.to.unix_timestamp())
         .bind(ctx.domain.as_ref().map(String::as_str).unwrap_or("%"))
         .bind(include_bots)
@@ -217,21 +208,18 @@
         ctx: &LogsQueryContext,
         n: i64,
     ) -> Result<Vec<(String, i64)>, Error> {
-        let tz_offset = ctx.tz_offset;
         let include_bots = if ctx.include_bots { 1 } else { 0 };
         let result = sqlx::query(
             r#"
 SELECT path, COUNT(*) AS pathcount
 FROM RequestLog
 JOIN Sessions ON (Sessions.id = Requestlog.session)
-WHERE atime+? > ? AND atime+? < ? AND RequestLog.domain LIKE ? AND Sessions.is_bot <= ?
+WHERE atime > ? AND atime < ? AND RequestLog.domain LIKE ? AND Sessions.is_bot <= ?
 GROUP BY path
 ORDER BY pathcount DESC
 LIMIT ?;"#,
         )
-        .bind(tz_offset)
         .bind(ctx.from.unix_timestamp())
-        .bind(tz_offset)
         .bind(ctx.to.unix_timestamp())
         .bind(ctx.domain.as_ref().map(String::as_str).unwrap_or("%"))
         .bind(include_bots)
@@ -257,38 +245,31 @@
         &mut self,
         ctx: &LogsQueryContext,
     ) -> Result<Vec<(String, f32)>, Error> {
-        let tz_offset = ctx.tz_offset;
         let include_bots = if ctx.include_bots { 1 } else { 0 };
         let result = sqlx::query(
             r#"
 SELECT d, CAST(nreq AS REAL)/nses
 FROM
-    (SELECT DATE(atime+?, 'unixepoch') AS d,
+    (SELECT DATE(atime, 'unixepoch') AS d,
             COUNT(*) AS nreq
     FROM RequestLog
     JOIN Sessions ON (Sessions.id = RequestLog.session)
-    WHERE atime+? > ? AND atime+? < ? AND RequestLog.domain LIKE ? AND is_bot <= ?
+    WHERE atime > ? AND atime < ? AND RequestLog.domain LIKE ? AND is_bot <= ?
     GROUP BY d)
 JOIN
-    (SELECT DATE(start+?, 'unixepoch') AS d2,
+    (SELECT DATE(start, 'unixepoch') AS d2,
             COUNT(*) as nses
      FROM Sessions
-     WHERE start+? > ? AND start+? < ? AND Sessions.domain LIKE ? AND is_bot <= ?
+     WHERE start > ? AND start < ? AND Sessions.domain LIKE ? AND is_bot <= ?
      GROUP BY d2)
 ON (d = d2)
 ORDER BY d ASC"#,
         )
-        .bind(tz_offset)
-        .bind(tz_offset)
         .bind(ctx.from.unix_timestamp())
-        .bind(tz_offset)
         .bind(ctx.to.unix_timestamp())
         .bind(ctx.domain.as_ref().map(String::as_str).unwrap_or("%"))
         .bind(include_bots)
-        .bind(tz_offset)
-        .bind(tz_offset)
         .bind(ctx.from.unix_timestamp())
-        .bind(tz_offset)
         .bind(ctx.to.unix_timestamp())
         .bind(ctx.domain.as_ref().map(String::as_str).unwrap_or("%"))
         .bind(include_bots)
@@ -316,7 +297,6 @@
         &mut self,
         ctx: &LogsQueryContext,
     ) -> Result<Vec<(String, i64)>, Error> {
-        let tz_offset = ctx.tz_offset;
         let include_bots = if ctx.include_bots { 1 } else { 0 };
         let result = sqlx::query(
             r#"
@@ -324,15 +304,13 @@
 FROM RequestLog
 JOIN Sessions ON (RequestLog.session = Sessions.id)
 WHERE instr(RequestLog.refer, RequestLog.domain) = 0 AND
-    RequestLog.atime+? > ? AND RequestLog.atime+? < ? AND
+    RequestLog.atime > ? AND RequestLog.atime < ? AND
     Sessions.is_bot <= ? AND
     RequestLog.domain LIKE ?
 GROUP BY refer;
 "#,
         )
-        .bind(tz_offset)
         .bind(ctx.from.unix_timestamp())
-        .bind(tz_offset)
         .bind(ctx.to.unix_timestamp())
         .bind(include_bots)
         .bind(ctx.domain.as_ref().map(String::as_str).unwrap_or("%"))
@@ -380,10 +358,10 @@
         ctx: &LogsQueryContext,
         n: i64,
     ) -> Result<Vec<RecentSessionsRow>, Error> {
-        let tz_offset = ctx.tz_offset;
         let include_bots = if ctx.include_bots { 1 } else { 0 };
         // Check later if this query possibly has bad scaling behavior due to no restrictions on
         // requesttags query.
+        log::warn!("from/to {}/{}", ctx.from, ctx.to);
         let result = sqlx::query(
             r#"
 SELECT Sessions.start AS start,
@@ -402,14 +380,12 @@
 		GROUP_CONCAT(IIF(value, PRINTF("%s=%s", IIF(key, key, ""), value), key)) AS tagstring
 	FROM RequestTags GROUP BY requestid) AS AccumTags
 	ON (RequestLog.id = AccumTags.requestid)
-WHERE atime+? >= ? AND atime+? <= ? AND Sessions.is_bot <= ?
+WHERE atime >= ? AND atime <= ? AND Sessions.is_bot <= ?
 GROUP BY Sessions.id
 ORDER BY Sessions.start DESC, RequestLog.atime DESC
-LIMIT 10;"#,
+LIMIT ?;"#,
         )
-        .bind(tz_offset)
         .bind(ctx.from.unix_timestamp())
-        .bind(tz_offset)
         .bind(ctx.to.unix_timestamp())
         .bind(include_bots)
         .bind(n)
--- a/src/main.rs	Mon Aug 01 09:45:39 2022 -0700
+++ b/src/main.rs	Wed Aug 17 17:46:40 2022 +0200
@@ -207,10 +207,8 @@
     }
 
     // Parameter treatment
-    let duration = duration
-        .map(|d| i64::from_str_radix(d, 10).unwrap_or(30))
-        .map(|d| Duration::new(86400 * d, 0))
-        .unwrap_or(Duration::days(30));
+    let duration = Duration::days(i64::from_str_radix(duration
+        .unwrap_or("30"), 10).unwrap_or(30));
     let from = from
         .map(|p| {
             OffsetDateTime::parse(p, &time::format_description::well_known::Iso8601::PARSING)
@@ -249,7 +247,6 @@
         domain: domain.clone(),
         from: begin,
         to: end,
-        tz_offset,
         include_bots: includebots,
     };
 
@@ -510,6 +507,7 @@
     tz_offset: Option<i64>,
 }
 
+/// Settings: Submit form
 #[rocket::post("/settings", data = "<settings>")]
 async fn route_settings_post(
     mut cdb: Connection<ConfigDB>,
@@ -560,12 +558,13 @@
     Flash::success(Redirect::to(settingspath), format!("Saved successfully!"))
 }
 
-#[rocket::get("/settings", rank = 1)]
+#[rocket::get("/settings?<configure_user>", rank = 1)]
 async fn route_settings(
     mut cdb: Connection<ConfigDB>,
     flash: Option<FlashMessage<'_>>,
     cc: &rocket::State<CustomConfig>,
     lig: LoggedInGuard,
+    configure_user: Option<String>,
 ) -> Template {
     let mut db = ConfigDBSession(&mut cdb);
     let user = match db.get_user_details(&lig.0).await {
@@ -590,6 +589,11 @@
         (None, None)
     };
 
+    let is_admin = match db.is_admin(&lig.0).await {
+        Ok(b) => b,
+        Err(e) => { error!("Couldn't query admin status: {}", e); false }
+    };
+
     Template::render(
         "settings",
         context![
@@ -598,6 +602,7 @@
             user: &user.name,
             error: error,
             flash: success,
+            admin: context![ active: is_admin, configure_user: configure_user ],
 
             tz_offset: format!("{:}", user.tz_offset / 3600),