#52 Automatisk oppdatering av kodebok basert på tid

Kodebok skal automatisk oppdateres hver 24t. Hver gang den oppdateres skrives dato og tid (datetime) ti tabellen LastUpdated i db. Utifra denne datoen vil programvaren oppdatere kodeboken når det er gått 24t.

soitool/database.py

@@ -2,6 +2,7 @@
 import os
 import sqlite3
 import json
+from datetime import datetime
 import soitool.coder
 
 # Set name and path to (future) database
@@ -9,6 +10,9 @@ DBNAME = "database"
 CURDIR = os.path.dirname(__file__)
 DBPATH = os.path.join(CURDIR, DBNAME)
 
+# Number og seconds in 24h
+SECONDS_IN_24H = 24 * 60 * 60
+
 # DDL-statements for creating tables
 CODEBOOK = (
     "CREATE TABLE CodeBook"
@@ -22,6 +26,8 @@ CATEGORYWORDS = (
 )
 BYHEART = "CREATE TABLE ByHeart(Word VARCHAR PRIMARY KEY)"
 
+LASTUPDATED = "CREATE TABLE LastUpdated(Timestamp DATETIME PRIMARY KEY)"
+
 
 class Database:
     """
@@ -29,9 +35,12 @@ class Database:
 
     Connects to existing db if found, creates new db if not.
     If db is created, tables are created and filled.
+
+    Holds a QTimer that requests an update of CodeBook on every timeout.
     """
 
     def __init__(self):
+
         db_exists = os.path.exists(DBPATH)
 
         if db_exists:
@@ -52,7 +61,7 @@ class Database:
 
     def create_tables(self):
         """Create tables CodeBook, CategoryWords and ByHeart."""
-        stmts = [CODEBOOK, CATEGORYWORDS, BYHEART]
+        stmts = [CODEBOOK, CATEGORYWORDS, BYHEART, LASTUPDATED]
 
         for stmt in stmts:
             self.conn.execute(stmt)
@@ -64,6 +73,7 @@ class Database:
         self.fill_codebook()
         self.fill_by_heart()
         self.fill_category_words()
+        self.fill_last_updated()
         self.conn.commit()
 
     def fill_codebook(self):
@@ -71,9 +81,9 @@ class Database:
         file_path = os.path.join(CURDIR, "testdata/long_codebook.json")
 
         # Load json as dict
-        f = open(file_path, "r", encoding="utf-8")
-        entries = json.load(f)
-        f.close()
+        file = open(file_path, "r", encoding="utf-8")
+        entries = json.load(file)
+        file.close()
 
         # Generate codes
         code_len = soitool.coder.get_code_length_needed(len(entries))
@@ -94,35 +104,45 @@ class Database:
     def fill_by_heart(self):
         """Read data from ByHeart.txt and fill DB-table ByHeart."""
         file_path = os.path.join(CURDIR, "testdata/ByHeart.txt")
-        f = open(file_path, "r", encoding="utf-8")
+        file = open(file_path, "r", encoding="utf-8")
 
         # Loop through words on file and insert them into ByHeart-table
         stmt = "INSERT INTO ByHeart(Word) VALUES(?)"
-        for expr in f:
+        for expr in file:
             self.conn.execute(stmt, (expr.rstrip(),))
-        f.close()
+        file.close()
 
     def fill_category_words(self):
         """Read data from CategoryWords.txt and fill DB-table CategoryWords."""
         file_path = os.path.join(CURDIR, "testdata/CategoryWords.txt")
-        f = open(file_path, "r", encoding="utf-8")
+        file = open(file_path, "r", encoding="utf-8")
 
         # Get number of categories on file
-        no_of_categories = int(f.readline().rstrip())
+        no_of_categories = int(file.readline().rstrip())
 
         # Loop through categories on file
         for _ in range(no_of_categories):
             # Get category and number of words in category
-            line = f.readline().split(", ")
+            line = file.readline().split(", ")
             category = line[0]
             no_of_words = int(line[1].rstrip())
 
             # Loop through words in category and add rows to DB
             stmt = "INSERT INTO CategoryWords(Word, Category) VALUES(?, ?)"
             for _ in range(no_of_words):
-                word = f.readline().rstrip()
+                word = file.readline().rstrip()
                 self.conn.execute(stmt, (word, category,))
-        f.close()
+        file.close()
+
+    def fill_last_updated(self):
+        """Fill table with current date and time."""
+        stmt = "INSERT INTO LastUpdated(Timestamp) VALUES(?)"
+        self.conn.execute(stmt, (str(datetime.now()),))
+
+    def update_last_updated(self):
+        """Update Timestamp in LastUpdated to current time."""
+        stmt = "UPDATE LastUpdated SET Timestamp = ?"
+        self.conn.execute(stmt, (str(datetime.now()),))
 
     def get_categories(self):
         """
@@ -194,13 +214,64 @@ class Database:
         stmt = stmt + "WHERE Word = ?"
         for i in range(number_of_entries):
             self.conn.execute(stmt, (codes.pop(), words[i][0]))
+        # Updates LastUpdated with current time
+        self.update_last_updated()
 
         print("Code in CodeBook updated")
 
+    def seconds_to_next_update(self, period):
+        """
+        Return time to next update of Codebook in seconds.
+
+        Parameters
+        ----------
+        period : int
+            The number of seconds between each update
+
+        Returns
+        -------
+        seconds_to_update : float
+            Time to next update in seconds
+        """
+        stmt = "SELECT Timestamp FROM LastUpdated"
+        last_updated = self.conn.execute(stmt).fetchall()[0][0]
+        # Convert datetime string to datetime object
+        last_updated = datetime.strptime(last_updated, "%Y-%m-%d %H:%M:%S.%f")
+        # Calculate the number of seconds until next update
+        seconds_to_update = (
+            period - (datetime.now() - last_updated).total_seconds()
+        )
+        # Since QTimer does not handle negative values
+        if seconds_to_update <= 0:
+            return 0
+
+        return seconds_to_update
+
+    def update_codebook_auto(self, timer):
+        """
+        Update Codebook if needed and update time for timer.
+
+        Parameters
+        ----------
+        timer : QTimer
+            Timer to set new interval on.
+        """
+        if self.seconds_to_next_update(SECONDS_IN_24H) <= 0:
+            self.update_codebook()
+        timer.setInterval(self.seconds_to_next_update(SECONDS_IN_24H) * 1000)
+
     def add_code_to(self, word, mode="ascii"):
         """
         Generate and insert a code for the new word in DB-table CodeBook.
 
+        This function is espescially designed for when a single word is added
+        to the CodeBook table. A unique code is generate and inserted in
+        Code which for the parameter word from before were NULL. Dependent on
+        the number of entries in the table various actions are performed. If
+        the new word makes the number of entries pass 26^x and the length of
+        the codes does not have he capacity, all the codes are updatet to an
+        appropriate length.
+
         Parameters
         ----------
         word : string
@@ -214,11 +285,18 @@ class Database:
         stmt = "SELECT COUNT(*) FROM CodeBook"
         number_of_entries = self.conn.execute(stmt).fetchall()[0][0]
         stmt = "SELECT Code FROM CodeBook"
-        # Incase db is approximate empty, min code lenght is 2
-        if number_of_entries < 2:
-            actual_code_len = len(self.conn.execute(stmt).fetchall()[1][0])
+        # In special case where table is empty
+        if number_of_entries <= 0:
+            raise ValueError("Can't add code to table with no words.")
+        # In special case table only has a single word
+        if number_of_entries == 1:
+            actual_code_len = soitool.coder.get_code_length_needed(
+                number_of_entries
+            )
         else:
-            actual_code_len = soitool.coder.get_code_length_needed(0)
+            # Since the newly added word's code is NULL and at [0][0],
+            # get [1][0] to get code length from an actual code
+            actual_code_len = len(self.conn.execute(stmt).fetchall()[1][0])
 
         needed_code_len = soitool.coder.get_code_length_needed(
             number_of_entries
@@ -230,7 +308,7 @@ class Database:
             # Get all codes and convert to set
             codes = self.conn.execute(stmt).fetchall()
             codes = {c[:][0] for c in codes}
-            # Get new unique code fro param word
+            # Get new unique code for param word
             code = soitool.coder.get_code(needed_code_len, mode)
             while code in codes:
                 code = soitool.coder.get_code(needed_code_len, mode)