From b558fd94527e6a3f359c3ca766eeabaf0c7a8a57 Mon Sep 17 00:00:00 2001
From: Jeffrey Armstrong <jeff@approximatrix.com>
Date: Thu, 28 Apr 2022 12:51:26 -0400
Subject: Sessions are now created and stored at login.

---
 captain/db.f90 | 122 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 122 insertions(+)

(limited to 'captain/db.f90')

diff --git a/captain/db.f90 b/captain/db.f90
index 7a8052d..7500b8a 100644
--- a/captain/db.f90
+++ b/captain/db.f90
@@ -1502,6 +1502,27 @@ contains
         
     end function validate_user_db
     
+    function get_user_id_db(username)
+    implicit none
+        
+        character(len=*), intent(in)::username
+        integer::get_user_id_db
+    
+        type(sqlite3_stmt)::stmt
+        
+        get_user_id_db = -1
+        
+        if(stmt%prepare(db, "SELECT id FROM users WHERE username=? LIMIT 1") == SQLITE_OK) then
+            if(stmt%bind_text(1, username) == SQLITE_OK) then
+                if(stmt%step() == SQLITE_ROW) then
+                    get_user_id_db = stmt%column_int(0)
+                end if
+            end if
+            call stmt%finalize()
+        end if
+    
+    end function get_user_id_db
+    
     function get_user_auth_db(username)
     implicit none
         
@@ -1564,5 +1585,106 @@ contains
         end if
     
     end subroutine get_session_username_db
+    
+    function create_user_session_db(username) result(session)
+    use m_uuid
+    implicit none
+    
+        character(len=*), intent(in)::username
+        character(len=UUID_LENGTH)::session, internal_session
+        
+        integer::userid, res
+        type(sqlite3_stmt)::stmt
+        
+        internal_session = generate_uuid4()
+        userid = get_user_id_db(username)
+        session = ' '
+        
+        if(stmt%prepare(db, "INSERT INTO sessions(user, session, accessed) VALUES(?, ?, datetime('now'))") == SQLITE_OK) then
+            if(stmt%bind_int(1, userid) == SQLITE_OK .AND. &
+               stmt%bind_text(2, internal_session) == SQLITE_OK) then
+                
+                if(any(stmt%step() == (/SQLITE_OK, SQLITE_DONE, SQLITE_ROW/))) then
+                    session = internal_session
+                end if
+                
+            end if
+        
+            call stmt%finalize()
+        end if
+    
+    end function create_user_session_db
+
+    subroutine destroy_session_db(session)
+    implicit none
+    
+        character(len=*), intent(in)::session
+        type(sqlite3_stmt)::stmt
+        
+        if(stmt%prepare(db, "DELETE FROM sessions WHERE session=?") == SQLITE_OK) then
+            if(stmt%bind_text(1, session) == SQLITE_OK) then
+                call stmt%step_now()
+            end if
+            call stmt%finalize()
+        end if
+           
+    end subroutine destroy_session_db
+
+    subroutine destroy_old_sessions_db()
+    implicit none
+    
+        type(sqlite3_stmt)::stmt
+
+        if(stmt%prepare(db, "DELETE FROM sessions WHERE accessed < datetime('now', '-30 minutes')") == SQLITE_OK) then
+            call stmt%step_now()
+            call stmt%finalize()
+        end if
+
+    end subroutine destroy_old_sessions_db
+    
+    subroutine update_session_db(session)
+    implicit none
+    
+        character(len=*), intent(in)::session
+        type(sqlite3_stmt)::stmt
+        
+        if(stmt%prepare(db, "UPDATE sessions SET accessed=datetime('now') WHERE session=?") == SQLITE_OK) then
+            if(stmt%bind_text(1, session) == SQLITE_OK) then
+                call stmt%step_now()
+            end if
+            call stmt%finalize()
+        end if
+    
+    end subroutine update_session_db
+
+    function session_expired_db(session)
+    implicit none
+    
+        character(len=*), intent(in)::session
+        logical::session_expired_db
+        
+        type(sqlite3_stmt)::stmt
+        
+        session_expired_db = .true.
+        
+        if(stmt%prepare(db, "SELECT COUNT(*) FROM sessions WHERE accessed < datetime('now', '-30 minutes') AND session=?") &
+           == SQLITE_OK) &
+        then
+            if(stmt%bind_text(1, session) == SQLITE_OK) then
+                
+                if(stmt%step() == SQLITE_ROW) then
+                    session_expired_db = .not. (stmt%column_int(0) > 0)
+                end if
+                
+            end if
+        
+            call stmt%finalize()
+        end if
+        
+        if(session_expired_db) then
+            call destroy_session_db(session)
+        end if
+        
+    end function session_expired_db
 
 end module captain_db
-- 
cgit v1.2.3