model/sufficienttable.py

   1 # -*- coding: utf-8 -*-
   2
   3 u"""
   4 充足値から判定値の組み合わせテーブルの作成・アクセスをするモジュール
   5 2013/12/19 written by kei9
   6 """
   7
   8 import sqlite3
   9 import csv
  10
  11 import amulettable
  12
  13 # for sufficient value for slots
  14 MASTER_NAME = u"sufficient_master"
  15 NAME = u"sufficient_{amulet_id}"
  16 COL_AMULET_ID = u"amulet_id"
  17 COL_NAME = u"sufficient_table_name"
  18 COL_SUFFICIENT_VALUE = u"sufficient_value"
  19 COL_SLOT1_THRESHOLD = u"slot1_threshold"
  20 COL_SLOT2_THRESHOLD = u"slot2_threshold"
  21 COL_SLOT3_THRESHOLD = u"slot3_threshold"
  22 CREATE_MASTER_SQL = u"""create table if not exists {master}
  23     (id integer primary key, {amulet_id} integer, {table_name} varchar,
  24     foreign key({amulet_id}) references {amulet_table}(id));""".format(
  25         master=MASTER_NAME,
  26         amulet_id=COL_AMULET_ID,
  27         amulet_table=amulettable.NAME,
  28         table_name=COL_NAME)
  29 CREATE_SQL = u"""create table if not exists {{table_name}}
  30     (id integer primary key, {sufficient_val} integer unique,
  31     {slot1_val} integer, {slot2_val} integer, {slot3_val} integer) ;""".format(
  32         sufficient_val=COL_SUFFICIENT_VALUE,
  33         slot1_val=COL_SLOT1_THRESHOLD,
  34         slot2_val=COL_SLOT2_THRESHOLD,
  35         slot3_val=COL_SLOT3_THRESHOLD)
  36 INSERT_MASTER_SQL = u"""insert into {table}
  37     ({amulet_col}, {table_col}) values(?,?);""".format(
  38         table=MASTER_NAME,
  39         table_col=COL_NAME,
  40         amulet_col=COL_AMULET_ID)
  41 INSERT_SQL = u"""insert into {{table_name}}
  42     ({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val})
  43     values(?,?,?,?);""".format(
  44         sufficient_val=COL_SUFFICIENT_VALUE,
  45         slot1_val=COL_SLOT1_THRESHOLD,
  46         slot2_val=COL_SLOT2_THRESHOLD,
  47         slot3_val=COL_SLOT3_THRESHOLD)
  48 SELECT_MASTER_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
  49         amu_id=COL_AMULET_ID,
  50         table_col=COL_NAME,
  51         table=MASTER_NAME)
  52 SELECT_ALL_SQL = u"""select {sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}""".format(
  53         sufficient_val=COL_SUFFICIENT_VALUE,
  54         slot1_val=COL_SLOT1_THRESHOLD,
  55         slot2_val=COL_SLOT2_THRESHOLD,
  56         slot3_val=COL_SLOT3_THRESHOLD)
  57 SELECT_THRESHOLD_SQL = u"""select {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}
  58     where {sufficient_val}={{sufficient_val}}""".format(
  59         sufficient_val=COL_SUFFICIENT_VALUE,
  60         slot1_val=COL_SLOT1_THRESHOLD,
  61         slot2_val=COL_SLOT2_THRESHOLD,
  62         slot3_val=COL_SLOT3_THRESHOLD)
  63
  64 class SufficientTableGenerator(object):
  65     u"""充足値から判定値の組み合わせテーブルの作成をするクラス"""
  66     def __init__(self):
  67         u""" コンストラクタ """
  68         self._amulet_id2suff_filename_dict = {}
  69
  70     def insert_master_data(self, db_cursor, csv_reader):
  71         u""" マスターテーブルを作成する。
  72         """
  73         db_cursor.execute(CREATE_MASTER_SQL)
  74         accessor = amulettable.AmuletTableAccessor(db_cursor)
  75         amu_id2name, amu_name2id = accessor.get_dict()
  76
  77         csv_reader.next()   # skip header row
  78         for row in csv_reader: # (omamori_name, filename of sufficient values)
  79             amulet_name = row[0].strip()
  80             suff_file = row[1].strip()
  81             amulet_id = amu_name2id[amulet_name]
  82             table_name = NAME.format(amulet_id=amulet_id)
  83             self._amulet_id2suff_filename_dict[amulet_id] = suff_file
  84             db_cursor.execute(INSERT_MASTER_SQL, (amulet_id, table_name))
  85
  86     def get_skill_filenames(self):
  87         u""" スキル1, スキル2の最大最小が記載されたファイル名をお守りIDに関連付けて返す
  88         return {amulet_id:(skill1_filename, skill2_filename)}"""
  89         result_dict = {}
  90         for amu_id in self._amulet_id2suff_filename_dict.keys():
  91             result_dict[amu_id] = self._amulet_id2suff_filename_dict[amu_id]
  92         return result_dict
  93
  94     def insert_data(self, db_cursor, amulet_id2csv_reader_dict):
  95         u""" お守りIDと関連付けられたcsv_reader(sufficient)からデータを読み込み、
  96         db_cursorへデータを挿入する。
  97         ammulet_id2csv_reader_dict:{amulet_id:csv_reader_sufficient}"""
  98         for amulet_id, reader in amulet_id2csv_reader_dict.items():
  99             table_name = NAME.format(amulet_id=amulet_id)
 100             db_cursor.execute(CREATE_SQL.format(table_name=table_name))
 101
 102             reader.next()  # skip header
 103             insert_sql = INSERT_SQL.format(table_name=table_name)
 104             for row_vals in reader:
 105                 val_tup = tuple([int(x.strip()) for x in row_vals])
 106                 db_cursor.execute(insert_sql, val_tup)
 107
 108 class SufficientTableAccessor(object):
 109     u"""充足値から判定値の組み合わせテーブルへのアクセスをするクラス"""
 110     def __init__(self, db_cursor):
 111         u""" db_cursor: cursor of sqlite3 database """
 112         self._cursor = db_cursor
 113         accessor = amulettable.AmuletTableAccessor(db_cursor)
 114         self._amu_id2name, self._amu_name2id = accessor.get_dict()
 115         self._amu_id2table_name = {}
 116
 117         self._cursor.execute(SELECT_MASTER_ALL_SQL)
 118         for row in self._cursor.fetchall():
 119             amu_id, table_name = row
 120             self._amu_id2table_name[amu_id] = table_name
 121
 122     def select_thresholds_by_id(self, amulet_id, sufficient_val):
 123         u""" お守りIdと充足値から、スロットごとの判定値を得る """
 124         table_name = self._amu_id2table_name[amulet_id]
 125         sql = SELECT_THRESHOLD_SQL.format(table_name=table_name, sufficient_val=sufficient_val)
 126         self._cursor.execute(sql)
 127         th_slot1, th_slot2, th_slot3 = self._cursor.fetchone()
 128         return (th_slot1, th_slot2, th_slot3)
 129
 130     def select_thresholds_by_name(self, amulet_name, sufficient_val):
 131         u""" お守り名と充足値から、スロットごとの判定値を得る """
 132         amulet_id = self._amu_name2id[amulet_name]
 133         return self.select_thresholds_by_id(amulet_id, sufficient_val)