OSDN Git Service

Merge remote-tracking branch 'origin/develop' into develop
[amulettoolsmh4/main.git] / 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()