model/db_accessor.py

   1 # -*- coding: utf-8 -*-
   2
   3 # database のアクセス用スクリプト
   4 # 2013/12/04 written by kei9
   5
   6 import sqlite3
   7
   8 import db_supports
   9 from amuletskillsholder import AmuletSkillsHolder
  10 from randomseedsholder import RandomSeedsHolder
  11 from sufficientsholder import SufficientsHolder
  12
  13 class DataBaseAccessor(object):
  14     u""" this is access class to database """
  15     def __init__(self, db_name):
  16         u""" db_name is database name to access """
  17         self._db_name = db_name
  18         self._connect = sqlite3.connect(self._db_name)
  19         self._cursor = self._connect.cursor()
  20
  21         self._skill_id2name_dict, self._skill_name2id_dict = self._get_skill_dict()
  22         self._amulet_id2name_dict, self._amulet_name2id_dict, self._amulet_id2minmax_dict, self._amulet_id2second_dict, self._amulet_id2suff_dict = self._get_amulet_dict()
  23
  24     def _print_dicts(self):
  25         u""" for debug """
  26         print self._skill_id2name_dict
  27         print self._skill_name2id_dict
  28         print self._amulet_id2name_dict
  29         print self._amulet_name2id_dict
  30         print self._amulet_id2minmax_dict
  31         print self._amulet_id2second_dict
  32         print self._amulet_id2suff_dict
  33
  34     def get_dicts(self, clone_enable=False):
  35         u""" get all dicts
  36             if clone_enable is True, return cloend dictionary,
  37             otherwise return reference dictionary
  38
  39             returns (skill_id2name_dict, skill_name2id_dict,
  40                 amulet_id2name_dict, amulet_name2id_dict,
  41                 amulet_id2minmax_dict, amulet_id2second_dict,
  42                 amulet_id2stuff_dict)
  43         """
  44         if clone_enable:
  45             return (dict(self._skill_id2name_dict),
  46                     dict(self._skill_name2id_dict),
  47                     dict(self._amulet_id2name_dict),
  48                     dict(self._amulet_name2id_dict),
  49                     dict(self._amulet_id2minmax_dict),
  50                     dict(self._amulet_id2second_dict),
  51                     dict(self._amulet_id2stuff_dict))
  52         else:
  53             return (self._skill_id2name_dict,
  54                     self._skill_name2id_dict,
  55                     self._amulet_id2name_dict,
  56                     self._amulet_name2id_dict,
  57                     self._amulet_id2minmax_dict,
  58                     self._amulet_id2second_dict,
  59                     self._amulet_id2suff_dict)
  60
  61
  62     def _get_skill_dict(self):
  63         u""" create id2name, name2id dict of skill
  64             return (dict_id2name, dict_name2id)
  65         """
  66         skill_id2name_dict, skill_name2id_dict = {}, {}
  67         self._cursor.execute(db_supports.SKILL_TABLE_SELECT_ALL_SQL)
  68         for val in self._cursor.fetchall():
  69             skill_id, name = val[0], val[1]
  70             skill_id2name_dict[skill_id] = name
  71             skill_name2id_dict[name] = skill_id
  72
  73         return skill_id2name_dict, skill_name2id_dict
  74
  75     def _get_amulet_dict(self):
  76         u""" create id2name, name2id, id2minmax, id2second, id2suff dict of amulet
  77             return (dict_id2name, dict_name2id, id2minmax, id2second, id2suff)
  78         """
  79         amulet_id2name_dict, amulet_name2id_dict = {}, {}
  80         id2minmax, id2second, id2suff = {}, {}, {}
  81         self._cursor.execute(db_supports.AMULET_TABLE_SELECT_ALL_SQL)
  82         for val in self._cursor.fetchall():
  83             amulet_id, name = val[0], val[1]
  84             amulet_id2name_dict[amulet_id] = name
  85             amulet_name2id_dict[name] = amulet_id
  86
  87         # id 2 minmax table name
  88         self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL)
  89         for val in self._cursor.fetchall():
  90             amulet_id, table = val[0], val[1]
  91             id2minmax[amulet_id] = table
  92
  93         # id 2 second table name
  94         self._cursor.execute(db_supports.SECOND_MASTER_TABLE_SELECT_ALL_SQL)
  95         for val in self._cursor.fetchall():
  96             amulet_id, table = val[0], val[1]
  97             id2second[amulet_id] = table
  98
  99         # id 2 sufficient table name
 100         self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL)
 101         for val in self._cursor.fetchall():
 102             amulet_id, table = val[0], val[1]
 103             id2suff[amulet_id] = table
 104
 105         return amulet_id2name_dict, amulet_name2id_dict, id2minmax, id2second, id2suff
 106
 107     def select_seeds(self, amu_id2skill_id_list_dict):
 108         u""" from dict of amulet_id to skill_id_list,
 109         skill_id_list: list of skill_id, if skill_id is unknown, skill_id must be None
 110         """
 111         seed_set = None
 112         for key, val in amu_id2skill_id_list_dict.items():
 113             if key in self._amulet_id2second_dict:
 114                 second_skill_table = self._amulet_id2second_dict[key]
 115                 amulet_skill = AmuletSkillsHolder(val)
 116
 117                 if amulet_skill.is_empty():
 118                     continue
 119
 120                 sql = db_supports.SECOND_TABLE_SELECT_SEED_SQL.format(table_name=second_skill_table) + amulet_skill.get_where_sql()
 121                 self._cursor.execute(sql)
 122
 123                 if seed_set is None:
 124                     seed_set = set([x[0] for x in self._cursor.fetchall()])
 125                 else:
 126                     seed_set = seed_set.intersection(set([x[0] for x in self._cursor.fetchall()]))
 127
 128         if seed_set is None:
 129             return set()
 130         else:
 131             return seed_set
 132
 133     def select_minmax_from_skill_ids(self, amulet_id, skill_ids):
 134         u""" お守りのidとスキルidのリストから、スキルIDと取りうるスキルの最大最小値の辞書を得る
 135             return dict{skill_id:(min1, max1, min2, max2)}
 136         """
 137         minmax_table = self._amulet_id2minmax_dict[amulet_id]
 138         amulet_skill = AmuletSkillsHolder(skill_ids)
 139         result_dict = {}
 140         if amulet_skill.is_empty() is False:
 141             sql = db_supports.MIN_MAX_TABLE_SELECT_ALL_SQL.format(table_name=minmax_table) + amulet_skill.get_where_in_sql()
 142             self._cursor.execute(sql)
 143
 144             for row in self._cursor.fetchall():
 145                 result_dict[row[0]] = tuple(row[1])
 146         return result_dict
 147
 148     def select_threshold_from_sufficient(self, amulet_id, sufficient_vals):
 149         u""" お守りのidと充足値のリストから、充足値とスロットごとの判定値の辞書を得る
 150             return dict({sufficient_val:(slot1_th, slot2_th, slot3_th)})
 151         """
 152         suff_table = self._amulet_id2suff_dict[amulet_id]
 153         amulet_skill = SufficientsHolder(sufficient_vals)
 154         result_dict = {}
 155         if amulet_skill.is_empty() is False:
 156             sql = db_supports.SUFFICIENT_TABLE_SELECT_ALL_SQL.format(table_name=suff_table) + amulet_skill.get_where_in_sql()
 157             self._cursor.execute(sql)
 158
 159             for row in self._cursor.fetchall():
 160                 result_dict[row[0]] = tuple(row[1])
 161         return result_dict
 162
 163     def select_skills_from_seeds(self, seed_set):
 164         u""" get amulet id to (random_seed to skill ids dict) dict,
 165         and rendom_seed to slot value dict
 166         return: skill_dict, slot_dict
 167         """
 168         skill_dict, slot_dict = {}, {}
 169
 170         # skill dict
 171         seeds = RandomSeedsHolder(seed_set)
 172         for amu_id, table in self._amulet_id2second_dict.items():
 173             sql = db_supports.SECOND_TABLE_SELECT_ALL_SQL.format(
 174                     table_name=table) + seeds.get_where_sql()
 175             self._cursor.execute(sql)
 176             seed2skills_dic = {}
 177             for row in self._cursor.fetchall():
 178                 seed2skills_dic[row[0]] = [x for x in row[1:]]
 179             skill_dict[amu_id] = seed2skills_dic
 180
 181         # slot dict
 182         sql = db_supports.SECOND_SLOT_TABLE_SELECT_ALL_SQL + seeds.get_where_sql()
 183         self._cursor.execute(sql)
 184         for row in self._cursor.fetchall():
 185             slot_dict[row[0]] = [x for x in row[1:]]
 186
 187         return skill_dict, slot_dict
 188
 189
 190     def close(self):
 191         u""" close database accessor """
 192         self._cursor.close()
 193         self._connect.close()
 194
 195 if __name__ == "__main__":
 196     db = DataBaseAccessor("test.sqlite3")
 197     #db._print_dicts()
 198     hikari = [None, 57, None, None, None, None, None]
 199     huru = [54, None, None, None, None, None, None]
 200     #yuga = [None, None, 98, 75, None, None, None]
 201     yuga = [None, None, 98, None, None, None, None]
 202     dic = {2:hikari, 3:huru, 4:yuga}
 203     #seeds = db.select_seeds(dic)
 204     seeds = set([58241, 176])
 205     skill_dic, slot_dic = db.select_skills_from_seeds(seeds)
 206
 207     print "seeds: ", seeds
 208     print "amu_id to seed2skill dict: ", skill_dic
 209     print "seed2slot dict", slot_dic
 210     db.close()