Store Transcriptions in local cache

Depends on !67 (merged)

Closes #43 (closed)

arkindex_worker/cache.py

@@ -12,6 +12,14 @@ SQL_ELEMENTS_TABLE_CREATION = """CREATE TABLE IF NOT EXISTS elements (
     initial BOOLEAN DEFAULT 0 NOT NULL,
     worker_version_id VARCHAR(32)
 )"""
+SQL_TRANSCRIPTIONS_TABLE_CREATION = """CREATE TABLE IF NOT EXISTS transcriptions (
+    id VARCHAR(32) PRIMARY KEY,
+    element_id VARCHAR(32) NOT NULL,
+    text TEXT NOT NULL,
+    confidence REAL NOT NULL,
+    worker_version_id VARCHAR(32) NOT NULL,
+    FOREIGN KEY(element_id) REFERENCES elements(id)
+)"""
 
 
 CachedElement = namedtuple(
@@ -19,6 +27,10 @@ CachedElement = namedtuple(
     ["id", "type", "polygon", "worker_version_id", "parent_id", "initial"],
     defaults=[None, 0],
 )
+CachedTranscription = namedtuple(
+    "CachedTranscription",
+    ["id", "element_id", "text", "confidence", "worker_version_id"],
+)
 
 
 def convert_table_tuple(table):
@@ -37,6 +49,7 @@ class LocalDB(object):
 
     def create_tables(self):
         self.cursor.execute(SQL_ELEMENTS_TABLE_CREATION)
+        self.cursor.execute(SQL_TRANSCRIPTIONS_TABLE_CREATION)
 
     def insert(self, table, lines):
         if not lines: