Sessions are now created and stored at login.

3 files changed, 177 insertions, 20 deletions

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
diff --git a/captain/sql/create.sql b/captain/sql/create.sql
index 7517e07..40cbc0c 100644
--- a/captain/sql/create.sql
+++ b/captain/sql/create.sql
@@ -15,10 +15,13 @@ CREATE TABLE group_instructions(group_id INTEGER, instruction INTEGER, player IN
 
 CREATE TABLE checkin(player INTEGER PRIMARY KEY, year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minute INTEGER, second INTEGER, os TEXT DEFAULT NULL, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE);
 
-CREATE TABLE schedule(instruction INTEGER NOT NULL, player INTEGER NOT NULL, day INTEGER DEFAULT 0, hour INTEGER DEFAULT 0, FOREIGN KEY(instruction) REFERENCES instructions(id) ON DELETE CASCADE, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE );
+CREATE TABLE schedule(id INTEGER PRIMARY KEY, instruction INTEGER NOT NULL, player INTEGER NOT NULL, day INTEGER DEFAULT 0, hour INTEGER DEFAULT 0, FOREIGN KEY(instruction) REFERENCES instructions(id) ON DELETE CASCADE, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE );
+
+CREATE TABLE scheduled_jobs(schedule INTEGER UNIQUE NOT NULL, job INTEGER UNIQUE NOT NULL, FOREIGN KEY(schedule) REFERENCES schedules(id) ON DELETE CASCADE, FOREIGN KEY(job) REFERENCES jobs(id));
 
 CREATE TABLE users(id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT UNIQUE NOT NULL, email TEXT NOT NULL, password TEXT NOT NULL, level INTEGER DEFAULT 1);
 
-CREATE TABLE sessions(user INTEGER, session TEXT NOT NULL, expiration TEXT NOT NULL, FOREIGN KEY(user) REFERENCES users(id) ON DELETE CASCADE);
+CREATE TABLE sessions(user INTEGER, session TEXT NOT NULL, accessed TEXT NOT NULL, FOREIGN KEY(user) REFERENCES users(id) ON DELETE CASCADE);
 
 CREATE VIEW session_auth AS SELECT sessions.session, users.username, users.level FROM sessions INNER JOIN users ON sessions.user = users.id;
+
diff --git a/captain/web.f90 b/captain/web.f90
index 09d687b..c3841c0 100644
--- a/captain/web.f90
+++ b/captain/web.f90
@@ -52,8 +52,45 @@ contains
         
     end function method
     
+    subroutine handle_basic_template_components(req, page)
+    use server_response
+    use page_template
+    use captain_db
+    use config
+    implicit none
+    
+        type(request), intent(in)::req
+        type(template), intent(inout)::page
+        character(len=128)::username
+    
+        call page%assign('project', project)
+        call page%assign('base_url', req%server)
+        
+        if(associated(req%token)) then
+            if(get_session_auth_db(req%token) > 0) then
+            
+                call page%assign('user_link_page', "profile")
+                call get_session_username_db(req%token, username)
+                call page%assign('user_link_text', trim(username))
+                
+            else
+
+                call page%assign('user_link_page', "login")
+                call page%assign('user_link_text', "Login")
+
+            end if
+        else
+        
+            call page%assign('user_link_page', "login")
+            call page%assign('user_link_text', "Login")
+        
+        end if
+    
+    end subroutine handle_basic_template_components
+    
     subroutine build_request_object(req)
     use server_response, only:request
+    implicit none
     
         type(request), intent(out)::req
         character(len=:), allocatable::url, script_name
@@ -1026,22 +1063,7 @@ contains
             
         end if
         
-        call page%assign('project', project)
-        call page%assign('base_url', req%server)
-        
-        if(associated(req%token)) then
-            if(get_session_auth_db(req%token) > 0) then
-                call page%assign('user_link_page', "profile")
-                call get_session_username_db(req%token, username)
-                call page%assign('user_link_text', trim(username))
-            else
-                call page%assign('user_link_page', "login")
-                call page%assign('user_link_text', "Login")
-            end if
-        else
-            call page%assign('user_link_page', "login")
-            call page%assign('user_link_text', "Login")
-        end if
+        call handle_basic_template_components(req, page)
         
         call write_log("Rendering page for "//req%location)
         call page%render()
@@ -1054,7 +1076,8 @@ contains
     end function request_templated
     
     function handle_post(req) result(resp)
-    use captain_db, only: add_player_db, add_group_db, update_player_token_db
+    use captain_db, only: add_player_db, add_group_db, update_player_token_db, create_user_session_db, &
+                          validate_user_db
     use page_template
     use config, only: template_filepath
     use logging
@@ -1111,11 +1134,20 @@ contains
                     call page%assign('destination', 'groups.html')
     
                 end if
+                
+            else if(trim(category) == "login.html") then
+            
+                ! Determine if logged in
+                if(validate_user_db(posted%get_value("username"), posted%get_value("password"))) then
+                    call page%assign('destination', "home.html?token="//create_user_session_db(posted%get_value("username")))
+                else
+                    call page%assign('destination', "login.html?failed=1")
+                end if
             
             end if
             
             ! Handle the template
-            call page%assign('base_url', req%server)
+            call handle_basic_template_components(req, page)
             call page%render()
 
             call write_log("Finalizing response", LOG_INFO)