From d6f9ce81e4fd27c94cf656699ef4952956dff608 Mon Sep 17 00:00:00 2001 From: Jeffrey Armstrong Date: Tue, 22 Jun 2021 17:04:59 -0400 Subject: Added new database table for tracking last player checkin with requisite Fortran procedures. --- captain/api.f90 | 17 ++++- captain/db.f90 | 164 ++++++++++++++++++++++++++++++++++++++++++++----- captain/sql/create.sql | 2 + captain/web.f90 | 30 +++++++++ 4 files changed, 198 insertions(+), 15 deletions(-) diff --git a/captain/api.f90 b/captain/api.f90 index 2f0a746..4d9545e 100644 --- a/captain/api.f90 +++ b/captain/api.f90 @@ -84,15 +84,17 @@ contains use captain_db use special_filenames use logging + use query_utilities implicit none type(request), intent(in)::req type(response)::resp character(PLAYER_NAME_LENGTH)::player, instruction - integer::job_i, player_i + integer::job_i, player_i, qs_platform_index character(len=:), pointer::checkin_work_json + type(query)::q ! Complete - "/api/player/{name}/job/{jobid}/complete" ! Failed - "/api/player/{name}/job/{jobid}/failed" @@ -130,6 +132,19 @@ contains ! the jobs as failed. call mark_working_jobs_as_failed(player_i) + ! Acknowledge the checkin in the database + if(associated(req%query_string)) then + call q%init(req%query_string) + if(associated(q%get_value("platform"))) then + call acknowledge_checkin(player_i, q%get_value("platform")) + else + call acknowledge_checkin(player_i) + end if + call q%destroy() + else + call acknowledge_checkin(player_i) + end if + job_i = get_pending_job_for_player(player_i) if(job_i < 0) then resp%code = GEMINI_CODE_SUCCESS diff --git a/captain/db.f90 b/captain/db.f90 index e4b4a4b..b789280 100644 --- a/captain/db.f90 +++ b/captain/db.f90 @@ -200,44 +200,84 @@ contains end function get_player_names - function get_instructions_count() + function get_instructions_count(player) implicit none type(sqlite3_stmt)::stmt + integer, intent(in), optional::player integer::get_instructions_count get_instructions_count = 0 - if(stmt%prepare(db, "SELECT COUNT(*) FROM instructions") == SQLITE_OK) then - if(stmt%step() == SQLITE_ROW) then - get_instructions_count = stmt%column_int(0) + + if(present(player)) then + + if(stmt%prepare(db, "SELECT COUNT(*) FROM available WHERE player=?") == SQLITE_OK) then + if(stmt%bind_int(1, player) == SQLITE_OK) then + if(stmt%step() == SQLITE_ROW) then + get_instructions_count = stmt%column_int(0) + end if + end if + end if + call stmt%finalize() + + else + + if(stmt%prepare(db, "SELECT COUNT(*) FROM instructions") == SQLITE_OK) then + if(stmt%step() == SQLITE_ROW) then + get_instructions_count = stmt%column_int(0) + end if end if + call stmt%finalize() + end if - call stmt%finalize() end function get_instructions_count - function get_instruction_ids() result(res) + function get_instruction_ids(player) result(res) implicit none type(sqlite3_stmt)::stmt + integer, intent(in), optional::player integer, dimension(:), pointer::res integer::i,n res => null() - n = get_instructions_count() + if(present(player)) then + n = get_instructions_count(player) + else + n = get_instructions_count() + end if + if(n > 0) then allocate(res(n)) res = -1 + + if(present(player)) then + + if(stmt%prepare(db, "SELECT instruction FROM available WHERE player=?") == SQLITE_OK) then + if(stmt%bind_int(1, player) == SQLITE_OK) then + i = 1 + do while(stmt%step() == SQLITE_ROW .and. i <= n) + res(i) = stmt%column_int(0) + i = i + 1 + end do + end if + end if + call stmt%finalize() + + else - if(stmt%prepare(db, "SELECT id, name FROM instructions ORDER BY name") == SQLITE_OK) then - i = 1 - do while(stmt%step() == SQLITE_ROW .and. i <= n) - res(i) = stmt%column_int(0) - i = i + 1 - end do + if(stmt%prepare(db, "SELECT id, name FROM instructions ORDER BY name") == SQLITE_OK) then + i = 1 + do while(stmt%step() == SQLITE_ROW .and. i <= n) + res(i) = stmt%column_int(0) + i = i + 1 + end do + end if + call stmt%finalize() + end if - call stmt%finalize() end if @@ -1113,4 +1153,100 @@ contains end function get_available_work_pairs_db + subroutine acknowledge_checkin(player, platform) + implicit none + + integer, intent(in)::player + character(*), intent(in), optional::platform + type(sqlite3_stmt)::stmt + integer, dimension(8)::right_now + integer::res + + call date_and_time(values=right_now) + + if(stmt%prepare(db, & + "INSERT OR UPDATE INTO checkin(player, year, month, day, hour, minute, second, platform) VALUES(?, ?, ?)") & + == SQLITE_OK) & + then + if(stmt%bind_int(1, player) == SQLITE_OK .AND. & + stmt%bind_int(2, right_now(1)) == SQLITE_OK .AND. & + stmt%bind_int(3, right_now(2)) == SQLITE_OK .AND. & + stmt%bind_int(4, right_now(3)) == SQLITE_OK .AND. & + stmt%bind_int(5, right_now(5)) == SQLITE_OK .AND. & + stmt%bind_int(6, right_now(6)) == SQLITE_OK .AND. & + stmt%bind_int(7, right_now(7)) == SQLITE_OK ) & + then + + if(.not. present(platform)) then + res = stmt%bind_null(8) + else + res = stmt%bind_text(8, platform) + end if + + if(res == SQLITE_OK) then + call stmt%step_now() + end if + + end if + end if + call stmt%finalize() + + end subroutine acknowledge_checkin + + ! For definition of values. see Fortran's DATE_AND_TIME + subroutine get_last_checkin_time(player, values) + use sqlite + implicit none + + integer, intent(in)::player + integer, dimension(8), intent(out)::values + + type(sqlite3_stmt)::stmt + integer::i + + ! This call properly retrieves the offset from UTC if you want it... + call date_and_time(values=values) + values(1:3) = 0 + values(5:8) = 0 + + if(stmt%prepare(db, "SELECT year, month, day, hour, minute, second FROM checkin WHERE player=? LIMIT 1") == SQLITE_OK) then + if(stmt%bind_int(1, player) == SQLITE_OK) then + if(stmt%step() == SQLITE_ROW) then + do i = 1, 7 + if(i < 4) then + values(i) = stmt%column_int(i-1) + else if(i >= 5) then + values(i-1) = stmt%column_int(i-1) + end if + end do + end if + end if + end if + call stmt%finalize() + + end subroutine get_last_checkin_time + + subroutine get_player_platform(player, platform) + use sqlite + implicit none + + integer, intent(in)::player + character(*), intent(out)::platform + type(sqlite3_stmt)::stmt + + platform = " " + + if(stmt%prepare(db, "SELECT platform FROM checkin WHERE player=? LIMIT 1") == SQLITE_OK) then + if(stmt%bind_int(1, player) == SQLITE_OK) then + if(stmt%step() == SQLITE_ROW) then + if(stmt%column_type(0) == SQLITE_TEXT) then + call stmt%column_text(0, platform) + end if + end if + end if + end if + call stmt%finalize() + + end subroutine get_player_platform + end module captain_db diff --git a/captain/sql/create.sql b/captain/sql/create.sql index 6457296..e4a944c 100644 --- a/captain/sql/create.sql +++ b/captain/sql/create.sql @@ -12,3 +12,5 @@ CREATE TABLE available(instruction INTEGER, player INTEGER, FOREIGN KEY(instruct CREATE TABLE groups(id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL); CREATE TABLE group_instructions(group_id INTEGER, instruction INTEGER, player INTEGER, FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE, FOREIGN KEY(instruction) REFERENCES instructions(id) ON DELETE CASCADE, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE); + +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); diff --git a/captain/web.f90 b/captain/web.f90 index dac658b..ea3127e 100644 --- a/captain/web.f90 +++ b/captain/web.f90 @@ -555,6 +555,36 @@ contains end function generate_players_html + function generate_one_player_html(req) result(res) + use captain_db + use server_response + use request_utils + implicit none + + type(request), intent(in)::req + character(len=:), pointer::res + + character(len=PLAYER_NAME_LENGTH)::player_name + integer::n, pid + + call req%last_component(player_name) + pid = get_player_id(trim(player_name)) + + n = get_instructions_count(player=pid) + allocate(character(len=(2*n*PLAYER_NAME_LENGTH + 1024)) :: res) + + res = "

"//trim(player_name)//"

" + + ! Last checkin + + + ! List of instructions + + ! Token assignment + + + end function generate_one_player_html + function generate_one_job_html(req) result(res) use captain_db use server_response -- cgit v1.2.3