Implement worker

Closes #2 (closed)

worker_generic_training_dataset/db.py

 # -*- coding: utf-8 -*-
 from typing import NamedTuple
 
-from arkindex_export import Classification
+from arkindex_export import Classification, ElementPath, Image
 from arkindex_export.models import (
     Element,
     Entity,
@@ -23,8 +23,8 @@ def retrieve_element(element_id: str):
     return Element.get_by_id(element_id)
 
 
-def list_classifications(element: Element):
-    query = Classification.select().where(Classification.element == element)
+def list_classifications(element_id: str):
+    query = Classification.select().where(Classification.element_id == element_id)
     return query
 
 
@@ -33,7 +33,8 @@ def parse_transcription(transcription: NamedTuple, element: CachedElement):
         id=transcription.id,
         element=element,
         text=transcription.text,
-        confidence=transcription.confidence,
+        # Dodge not-null constraint for now
+        confidence=transcription.confidence or 1.0,
         orientation=DEFAULT_TRANSCRIPTION_ORIENTATION,
         worker_version_id=transcription.worker_version.id
         if transcription.worker_version
@@ -89,3 +90,47 @@ def retrieve_entities(transcription: CachedTranscription):
         return [], []
 
     return zip(*data)
+
+
+def list_children(parent_id):
+    # First, build the base query to get direct children
+    base = (
+        ElementPath.select(
+            ElementPath.child_id, ElementPath.parent_id, ElementPath.ordering
+        )
+        .where(ElementPath.parent_id == parent_id)
+        .cte("children", recursive=True, columns=("child_id", "parent_id", "ordering"))
+    )
+
+    # Then build the second recursive query, using an alias to join both queries on the same table
+    EP = ElementPath.alias()
+    recursion = EP.select(EP.child_id, EP.parent_id, EP.ordering).join(
+        base, on=(EP.parent_id == base.c.child_id)
+    )
+
+    # Combine both queries, using UNION and not UNION ALL to deduplicate parents
+    # that might be found multiple times with complex element structures
+    cte = base.union(recursion)
+
+    # And load all the elements found in the CTE
+    query = (
+        Element.select(
+            Element.id,
+            Element.type,
+            Image.id.alias("image_id"),
+            Image.width.alias("image_width"),
+            Image.height.alias("image_height"),
+            Image.url.alias("image_url"),
+            Element.polygon,
+            Element.rotation_angle,
+            Element.mirrored,
+            Element.worker_version,
+            Element.confidence,
+            cte.c.parent_id,
+        )
+        .with_cte(cte)
+        .join(cte, on=(Element.id == cte.c.child_id))
+        .join(Image, on=(Element.image_id == Image.id))
+        .order_by(cte.c.ordering.asc())
+    )
+    return query