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
  12 class DataBaseAccessor(object):
  13     u""" this is access class to database """
  14     def __init__(self, db_name):
  15         u""" db_name is database name to access """
  16         self._db_name = db_name
  17         self._connect = sqlite3.connect(self._db_name)
  18         self._cursor = self._connect.cursor()
  19
  20         self._skill_id2name_dict, self._skill_name2id_dict = self._get_skill_dict()
  21         self._amulet_id2name_dict, self._amulet_name2id_dict, self._amulet_id2minmax_dict, self._amulet_id2second_dict, self._amulet_id2suff_dict = self._get_amulet_dict()
  22
  23     def _print_dicts(self):
  24         u""" for debug """
  25         print self._skill_id2name_dict
  26         print self._skill_name2id_dict
  27         print self._amulet_id2name_dict
  28         print self._amulet_name2id_dict
  29         print self._amulet_id2minmax_dict
  30         print self._amulet_id2second_dict
  31         print self._amulet_id2suff_dict
  32
  33     def get_dicts(self, clone_enable=False):
  34         u""" get all dicts
  35             if clone_enable is True, return cloend dictionary,
  36             otherwise return reference dictionary
  37
  38             returns (skill_id2name_dict, skill_name2id_dict,
  39                 amulet_id2name_dict, amulet_name2id_dict,
  40                 amulet_id2minmax_dict, amulet_id2second_dict,
  41                 amulet_id2stuff_dict)
  42         """
  43         if clone_enable:
  44             return (dict(self._skill_id2name_dict),
  45                     dict(self._skill_name2id_dict),
  46                     dict(self._amulet_id2name_dict),
  47                     dict(self._amulet_name2id_dict),
  48                     dict(self._amulet_id2minmax_dict),
  49                     dict(self._amulet_id2second_dict),
  50                     dict(self._amulet_id2stuff_dict))
  51         else:
  52             return (self._skill_id2name_dict,
  53                     self._skill_name2id_dict,
  54                     self._amulet_id2name_dict,
  55                     self._amulet_name2id_dict,
  56                     self._amulet_id2minmax_dict,
  57                     self._amulet_id2second_dict,
  58                     self._amulet_id2suff_dict)
  59
  60
  61     def _get_skill_dict(self):
  62         u""" create id2name, name2id dict of skill
  63             return (dict_id2name, dict_name2id)
  64         """
  65         skill_id2name_dict, skill_name2id_dict = {}, {}
  66         self._cursor.execute(db_supports.SKILL_TABLE_SELECT_ALL_SQL)
  67         for val in self._cursor.fetchall():
  68             skill_id, name = val[0], val[1]
  69             skill_id2name_dict[skill_id] = name
  70             skill_name2id_dict[name] = skill_id
  71
  72         return skill_id2name_dict, skill_name2id_dict
  73
  74     def _get_amulet_dict(self):
  75         u""" create id2name, name2id, id2minmax, id2second, id2suff dict of amulet
  76             return (dict_id2name, dict_name2id, id2minmax, id2second, id2suff)
  77         """
  78         amulet_id2name_dict, amulet_name2id_dict = {}, {}
  79         id2minmax, id2second, id2suff = {}, {}, {}
  80         self._cursor.execute(db_supports.AMULET_TABLE_SELECT_ALL_SQL)
  81         for val in self._cursor.fetchall():
  82             amulet_id, name = val[0], val[1]
  83             amulet_id2name_dict[amulet_id] = name
  84             amulet_name2id_dict[name] = amulet_id
  85
  86         # id 2 minmax table name
  87         self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL)
  88         for val in self._cursor.fetchall():
  89             amulet_id, table = val[0], val[1]
  90             id2minmax[amulet_id] = table
  91
  92         # id 2 second table name
  93         self._cursor.execute(db_supports.SECOND_MASTER_TABLE_SELECT_ALL_SQL)
  94         for val in self._cursor.fetchall():
  95             amulet_id, table = val[0], val[1]
  96             id2second[amulet_id] = table
  97
  98         # id 2 sufficient table name
  99         self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL)
 100         for val in self._cursor.fetchall():
 101             amulet_id, table = val[0], val[1]
 102             id2suff[amulet_id] = table
 103
 104         return amulet_id2name_dict, amulet_name2id_dict, id2minmax, id2second, id2suff
 105
 106     def select_seeds(self, amu_id2skill_id_list_dict):
 107         u""" from dict of amulet_id to skill_id_list,
 108         skill_id_list: list of skill_id, if skill_id is unknown, skill_id must be None
 109         """
 110         seed_set = None
 111         for key, val in amu_id2skill_id_list_dict.items():
 112             if key in self._amulet_id2second_dict:
 113                 second_skill_table = self._amulet_id2second_dict[key]
 114                 amulet_skill = AmuletSkillsHolder(val)
 115
 116                 if amulet_skill.is_empty():
 117                     continue
 118
 119                 sql = db_supports.SECOND_TABLE_SELECT_SEED_SQL.format(table_name=second_skill_table) + amulet_skill.get_where_sql()
 120                 self._cursor.execute(sql)
 121
 122                 if seed_set is None:
 123                     seed_set = set([x[0] for x in self._cursor.fetchall()])
 124                 else:
 125                     seed_set = seed_set.intersection(set([x[0] for x in self._cursor.fetchall()]))
 126
 127         if seed_set is None:
 128             return set()
 129         else:
 130             return seed_set
 131
 132     def select_skills_from_seeds(self, seed_set):
 133         u""" get amulet id to (random_seed to skill ids dict) dict,
 134         and rendom_seed to slot value dict
 135         return: skill_dict, slot_dict
 136         """
 137         skill_dict, slot_dict = {}, {}
 138
 139         # skill dict
 140         seeds = RandomSeedsHolder(seed_set)
 141         for amu_id, table in self._amulet_id2second_dict.items():
 142             sql = db_supports.SECOND_TABLE_SELECT_ALL_SQL.format(
 143                     table_name=table) + seeds.get_where_sql()
 144             self._cursor.execute(sql)
 145             seed2skills_dic = {}
 146             for row in self._cursor.fetchall():
 147                 seed2skills_dic[row[0]] = [x for x in row[1:]]
 148             skill_dict[amu_id] = seed2skills_dic
 149
 150         # slot dict
 151         sql = db_supports.SECOND_SLOT_TABLE_SELECT_ALL_SQL + seeds.get_where_sql()
 152         self._cursor.execute(sql)
 153         for row in self._cursor.fetchall():
 154             slot_dict[row[0]] = [x for x in row[1:]]
 155
 156         return skill_dict, slot_dict
 157
 158
 159     def close(self):
 160         u""" close database accessor """
 161         self._cursor.close()
 162         self._connect.close()
 163
 164 if __name__ == "__main__":
 165     db = DataBaseAccessor("test.sqlite3")
 166     #db._print_dicts()
 167     hikari = [None, 57, None, None, None, None, None]
 168     huru = [54, None, None, None, None, None, None]
 169     #yuga = [None, None, 98, 75, None, None, None]
 170     yuga = [None, None, 98, None, None, None, None]
 171     dic = {2:hikari, 3:huru, 4:yuga}
 172     #seeds = db.select_seeds(dic)
 173     seeds = set([58241, 176])
 174     skill_dic, slot_dic = db.select_skills_from_seeds(seeds)
 175
 176     print "seeds: ", seeds
 177     print "amu_id to seed2skill dict: ", skill_dic
 178     print "seed2slot dict", slot_dic
 179     db.close()