Merge branch 'master' of github.com:adamhrv/megapixels_dev

1 files changed, 112 insertions, 68 deletions

diff --git a/megapixels/app/models/sql_factory.py b/megapixels/app/models/sql_factory.py
index a580f28e..5b3cb5a3 100644
--- a/megapixels/app/models/sql_factory.py
+++ b/megapixels/app/models/sql_factory.py
@@ -3,7 +3,7 @@ import glob
 import time
 import pandas as pd
 
-from sqlalchemy import create_engine, Table, Column, String, Integer, DateTime, Float
+from sqlalchemy import create_engine, Table, Column, String, Integer, DateTime, Float, func
 from sqlalchemy.orm import sessionmaker
 from sqlalchemy.ext.declarative import declarative_base
 
@@ -62,7 +62,8 @@ def load_sql_dataset(path, replace=False, engine=None, base_model=None):
       df = pd.read_csv(fn)
       # fix columns that are named "index", a sql reserved word
       df.reindex_axis(sorted(df.columns), axis=1)
-      df.columns = sorted(table.__table__.columns).keys()
+      columns = [column.name for column in table.__table__.columns]
+      df.columns = columns
       df.to_sql(name=table.__tablename__, con=engine, if_exists='replace', index=False)
   return dataset
 
@@ -82,87 +83,153 @@ class SqlDataset:
     self.base_model = base_model
 
   def describe(self):
+    """
+    List the available SQL tables for a given dataset.
+    """
     return {
       'name': self.name,
       'tables': list(self.tables.keys()),
     }
 
   def get_identity(self, id):
-    table = self.get_table('identity_meta')
+    """
+    Get an identity given an ID.
+    """
     # id += 1
-    identity = table.query.filter(table.image_id <= id).order_by(table.image_id.desc()).first().toJSON()
+    file_record_table = self.get_table('file_record')
+    file_record = file_record_table.query.filter(file_record_table.id == id).first()
+
+    if not file_record:
+      return None
+
+    identity_table = self.get_table('identity')
+    identity = identity_table.query.filter(identity_table.id == file_record.identity_id).first()
+
+    if not identity:
+      return None
+
     return {
-      'uuid': self.select('uuids', id),
-      'identity': identity,
-      'roi': self.select('roi', id),
-      'pose': self.select('pose', id),
+      'file_record': file_record.toJSON(),
+      'identity': identity.toJSON(),
+      'face_roi': self.select('face_roi', id),
+      'face_pose': self.select('face_pose', id),
     }
 
   def search_name(self, q):
-    table = self.get_table('identity_meta')
-    uuid_table = self.get_table('uuids')
+    """
+    Find an identity by name.
+    """
+    table = self.get_table('identity')
+    identity_list = table.query.filter(table.fullname.ilike(q)).order_by(table.fullname.desc()).limit(15)
+    return identity_list
 
-    identity = table.query.filter(table.fullname.like(q)).order_by(table.fullname.desc()).limit(30)
+  def search_description(self, q):
+    """
+    Find an identity by description.
+    """
+    table = self.get_table('identity')
+    identity_list = table.query.filter(table.description.ilike(q)).order_by(table.description.desc()).limit(15)
+    return identity_list
+
+  def get_file_records_for_identities(self, identity_list):
+    """
+    Given a list of identities, map these to file records.
+    """
     identities = []
-    for row in identity:
-      uuid = uuid_table.query.filter(uuid_table.id == row.image_id).first()
-      identities.append({
-        'uuid': uuid.toJSON(),
-        'identity': row.toJSON(),
-      })
+    file_record_table = self.get_table('file_record')
+    for row in identity_list:
+      file_record = file_record_table.query.filter(file_record_table.identity_id == row.id).first()
+      if file_record:
+        identities.append({
+          'file_record': file_record.toJSON(),
+          'identity': row.toJSON(),
+        })
     return identities
 
   def select(self, table, id):
+    """
+    Perform a generic select.
+    """
     table = self.get_table(table)
     if not table:
       return None
     session = Session()
     # for obj in session.query(table).filter_by(id=id):
-    # print(table)
+    #   print(table)
     obj = session.query(table).filter(table.id == id).first()
     session.close()
     return obj.toJSON()
 
   def get_table(self, type):
+    """
+    Get one of these memoized, dynamically generated tables.
+    """
     if type in self.tables:
       return self.tables[type]
-    elif type == 'uuids':
-      self.tables[type] = self.uuid_table()
-    elif type == 'roi':
-      self.tables[type] = self.roi_table()
-    elif type == 'identity_meta':
+    elif type == 'file_record':
+      self.tables[type] = self.file_record_table()
+    elif type == 'identity':
       self.tables[type] = self.identity_table()
-    elif type == 'pose':
-      self.tables[type] = self.pose_table()
+    elif type == 'face_roi':
+      self.tables[type] = self.face_roi_table()
+    elif type == 'face_pose':
+      self.tables[type] = self.face_pose_table()
     else:
       return None
     return self.tables[type]
 
-  # ==> uuids.csv <==
-  # index,uuid
-  # 0,f03fd921-2d56-4e83-8115-f658d6a72287
-  def uuid_table(self):
-    class UUID(self.base_model):
-      __tablename__ = self.name + "_uuid"
+  # ==> file_record.csv <==
+  # index,ext,fn,identity_key,sha256,subdir,uuid,identity_index
+  def file_record_table(self):
+    class FileRecord(self.base_model):
+      __tablename__ = self.name + "_file_record"
       id = Column(Integer, primary_key=True)
+      ext = Column(String(3, convert_unicode=True), nullable=False)
+      fn = Column(String(36, convert_unicode=True), nullable=False)
+      identity_key = Column(String(36, convert_unicode=True), nullable=False)
+      sha256 = Column(String(36, convert_unicode=True), nullable=False)
+      subdir = Column(String(36, convert_unicode=True), nullable=False)
       uuid = Column(String(36, convert_unicode=True), nullable=False)
+      identity_id = Column(Integer)
       def toJSON(self):
         return {
           'id': self.id,
           'uuid': self.uuid,
+          'identity_id': self.identity_id,
         }
-    return UUID
+    return FileRecord
 
-  # ==> roi.csv <==
+  # ==> identity.csv <==
+  # index,description,gender,images,fullname
+  # 0,A. J. Cook,Canadian actress,f,1,0
+  def identity_table(self):
+    class Identity(self.base_model):
+      __tablename__ = self.name + "_identity"
+      id = Column(Integer, primary_key=True)
+      description = Column(String(36, convert_unicode=True), nullable=False)
+      gender = Column(String(1, convert_unicode=True), nullable=False)
+      images = Column(Integer, nullable=False)
+      fullname = Column(String(36, convert_unicode=True), nullable=False)
+      def toJSON(self):
+        return {
+          'id': self.id,
+          'fullname': self.fullname,
+          'images': self.images,
+          'gender': self.gender,
+          'description': self.description,
+        }
+    return Identity
+
+  # ==> face_roi.csv <==
   # index,h,image_height,image_index,image_width,w,x,y
   # 0,0.33000000000000007,250,0,250,0.32999999999999996,0.33666666666666667,0.35
-  def roi_table(self):
-    class ROI(self.base_model):
+  def face_roi_table(self):
+    class FaceROI(self.base_model):
       __tablename__ = self.name + "_roi"
       id = Column(Integer, primary_key=True)
       h = Column(Float, nullable=False)
       image_height = Column(Integer, nullable=False)
-      image_index = Column(Integer, nullable=False)
+      record_id = Column(Integer, nullable=False)
       image_width = Column(Integer, nullable=False)
       w = Column(Float, nullable=False)
       x = Column(Float, nullable=False)
@@ -170,7 +237,7 @@ class SqlDataset:
       def toJSON(self):
         return {
           'id': self.id,
-          'image_index': self.image_index,
+          'record_id': self.record_id,
           'image_height': self.image_height,
           'image_width': self.image_width,
           'w': self.w,
@@ -178,48 +245,25 @@ class SqlDataset:
           'x': self.x,
           'y': self.y,
         }
-    return ROI
-
-  # ==> identity.csv <==
-  # index,fullname,description,gender,images,image_index
-  # 0,A. J. Cook,Canadian actress,f,1,0
-  def identity_table(self):
-    class Identity(self.base_model):
-      __tablename__ = self.name + "_identity"
-      id = Column(Integer, primary_key=True)
-      fullname = Column(String(36, convert_unicode=True), nullable=False)
-      description = Column(String(36, convert_unicode=True), nullable=False)
-      gender = Column(String(1, convert_unicode=True), nullable=False)
-      images = Column(Integer, nullable=False)
-      image_id = Column(Integer, nullable=False)
-      def toJSON(self):
-        return {
-          'id': self.id,
-          'image_id': self.image_id,
-          'fullname': self.fullname,
-          'images': self.images,
-          'gender': self.gender,
-          'description': self.description,
-        }
-    return Identity
+    return FaceROI
 
-  # ==> pose.csv <==
-  # index,image_index,pitch,roll,yaw
+  # ==> face_pose.csv <==
+  # index,record_index,pitch,roll,yaw
   # 0,0,11.16264458441435,10.415885631337728,22.99719032415318
-  def pose_table(self):
-    class Pose(self.base_model):
+  def face_pose_table(self):
+    class FacePose(self.base_model):
       __tablename__ = self.name + "_pose"
       id = Column(Integer, primary_key=True)
-      image_id = Column(Integer, primary_key=True)
+      record_id = Column(Integer, nullable=False)
       pitch = Column(Float, nullable=False)
       roll = Column(Float, nullable=False)
       yaw = Column(Float, nullable=False)
       def toJSON(self):
         return {
           'id': self.id,
-          'image_id': self.image_id,
+          'record_id': self.record_id,
           'pitch': self.pitch,
           'roll': self.roll,
           'yaw': self.yaw,
         }
-    return Pose
+    return FacePose