Populating model database with normalized (RDBMS-wise) data, with cut-off from config applied

3 files changed, 72 insertions, 1 deletions

diff --git a/ausbutler/butler.py b/ausbutler/butler.py
new file mode 100644
index 0000000..424e385
--- /dev/null
+++ b/ausbutler/butler.py
@@ -0,0 +1,23 @@
+import json
+from os import path
+import __main__
+
+config = json.load(
+    open(path.join(path.dirname(__main__.__file__), 'config', 'butler.json')))
+
+def cutoff(score):
+    sign = 1 if score > 0 else -1
+    score = abs(score)
+    if score > config['cutoff_point']:
+        score -= config['cutoff_point']
+        score *= config['cutoff_rate']
+        score += config['cutoff_point']
+    return score * sign
+
+def get_room(butler, player):
+    table = butler.table
+    if player in [table.openE, table.openW, table.openN, table.openS]:
+        return 'open'
+    if player in [table.closeE, table.closeW, table.closeN, table.closeS]:
+        return 'closed'
+
diff --git a/ausbutler/interface.py b/ausbutler/interface.py
index 95696c1..50bd8b3 100644
--- a/ausbutler/interface.py
+++ b/ausbutler/interface.py
@@ -1,4 +1,7 @@
 from .db import Session
+from .model import AusButler, Butler
+from .butler import cutoff, get_room
+import re
 
 
 class Interface:
@@ -12,3 +15,22 @@ class Interface:
             Base.metadata.drop_all(self.session.get_bind())
         Base.metadata.create_all(self.session.get_bind())
 
+    def populate_db(self):
+        self.session.query(AusButler).delete()
+        column_name = re.compile(r'^seg(\d+)_(\d+)$')
+        for butler in self.session.query(Butler).all():
+            for column, value in butler.__dict__.iteritems():
+                column_match = re.match(column_name, column)
+                if column_match:
+                    if value is not None:
+                        aus_b = AusButler()
+                        aus_b.id = butler.id
+                        aus_b.match = int(column_match.group(1), base=10)
+                        aus_b.segment = int(column_match.group(2))
+                        aus_b.score = float(value)
+                        aus_b.cut_score = cutoff(aus_b.score)
+                        aus_b.board_count = aus_b.table.butler_count[
+                            get_room(aus_b, butler.id)]
+                        self.session.add(aus_b)
+        self.session.commit()
+
diff --git a/ausbutler/model.py b/ausbutler/model.py
index c237359..89c7701 100644
--- a/ausbutler/model.py
+++ b/ausbutler/model.py
@@ -1,4 +1,5 @@
-from sqlalchemy import Column, ForeignKey, MetaData, Table
+from cached_property import cached_property
+from sqlalchemy import Column, ForeignKey, MetaData, Table, func
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.types import Float, Integer
 from .db import Session
@@ -17,6 +18,17 @@ class AusButler(Base):
     corrected_score = Column(Float)
     board_count = Column(Integer)
 
+    @cached_property
+    def table(self):
+        for table in session.query(Segment).filter(
+                (Segment.rnd == self.match) & (Segment.segment == self.segment)
+        ).all():
+            if self.id in [
+                    table.openN, table.openS, table.openW, table.openE,
+                    table.closeN, table.closeS, table.closeW, table.closeE]:
+                return table
+        return None
+
     def __repr__(self):
         return '[%d] %d-%d: %.2f-%.2f=%.2f' % (self.id,
                                                self.match, self.segment,
@@ -45,3 +57,17 @@ class Segment(Base):
                       Column('tabl', Integer, primary_key=True),
                       autoload=True)
 
+    count_cache = {
+        (b.rnd, b.segment, b.tabl) : {
+            'open': int(b.open), 'closed': int(b.closed)
+        } for b in
+        session.query(
+            Score.rnd, Score.segment, Score.tabl,
+            func.sum(Score.butler * (Score.room == 1)).label('open'),
+            func.sum(Score.butler * (Score.room == 2)).label('closed')
+        ).group_by(Score.rnd, Score.segment, Score.tabl).all()
+    }
+
+    @cached_property
+    def butler_count(self):
+        return Segment.count_cache[(self.rnd, self.segment, self.tabl)]