OSDN Git Service

implemet almost gui events
[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 import sys
8
9 import db_supports
10 from amuletskillsholder import AmuletSkillsHolder
11 from randomseedsholder import RandomSeedsHolder
12 from sufficientsholder import SufficientsHolder
13
14 class DataBaseAccessor(object):
15     u""" this is access class to database """
16     def __init__(self, db_name):
17         u""" db_name is database name to access """
18         self._db_name = db_name
19         self._connect = sqlite3.connect(self._db_name)
20         self._cursor = self._connect.cursor()
21
22         self._skill_id2name_dict, self._skill_name2id_dict = self._get_skill_dict()
23         (self._amulet_id2name_dict, self._amulet_name2id_dict, 
24             self._amulet_id2minmax_table_dict, self._amulet_id2second_table_dict,
25             self._amulet_id2suff_table_dict,
26             self._amulet_id2skill1_id_dict, self._amulet_id2skill2_id_dict) = self._get_amulet_dict()
27
28     def _print_dicts(self):
29         u""" for debug """
30         print self._skill_id2name_dict
31         print self._skill_name2id_dict
32         print self._amulet_id2name_dict
33         print self._amulet_name2id_dict
34         print self._amulet_id2minmax_table_dict
35         print self._amulet_id2second_table_dict
36         print self._amulet_id2suff_table_dict
37         print self._amulet_id2skill1_id_dict
38         print self._amulet_id2skill2_id_dict
39
40     def get_dicts(self, clone_enable=False):
41         u""" get all dicts 
42             if clone_enable is True, return cloend dictionary,
43             otherwise return reference dictionary
44
45             returns (skill_id2name_dict, skill_name2id_dict, 
46                 amulet_id2name_dict, amulet_name2id_dict,
47                 amulet_id2skill_ids)
48         """
49         if clone_enable:
50             return (dict(self._skill_id2name_dict), 
51                     dict(self._skill_name2id_dict),
52                     dict(self._amulet_id2name_dict), 
53                     dict(self._amulet_name2id_dict),
54                     dict(self._amulet_id2skill1_id_dict),
55                     dict(self._amulet_id2skill2_id_dict))
56         else:
57             return (self._skill_id2name_dict, 
58                     self._skill_name2id_dict,
59                     self._amulet_id2name_dict, 
60                     self._amulet_name2id_dict,
61                     self._amulet_id2skill1_id_dict,
62                     self._amulet_id2skill2_id_dict)
63
64
65     def _get_skill_dict(self):
66         u""" create id2name, name2id dict of skill 
67             return (dict_id2name, dict_name2id)
68         """
69         skill_id2name_dict, skill_name2id_dict = {}, {}
70         self._cursor.execute(db_supports.SKILL_TABLE_SELECT_ALL_SQL)
71         for val in self._cursor.fetchall():
72             skill_id, name = val[0], val[1]
73             skill_id2name_dict[skill_id] = name
74             skill_name2id_dict[name] = skill_id
75
76         return skill_id2name_dict, skill_name2id_dict
77
78     def _get_amulet_dict(self):
79         u""" create id2name, name2id, id2minmax_table, id2second_table, id2suff_table,
80             id2skill1_ids, id2skill2_id dict of amulet 
81         return (dict_id2name, dict_name2id, 
82             id2minmax_table, id2second_table, id2suff_table, id2skill1_ids, id2skill2_ids)
83         """
84         amulet_id2name_dict, amulet_name2id_dict = {}, {}
85         id2minmax_table, id2second_table, id2suff_table = {}, {}, {}
86         id2skill1_ids, id2skill2_ids = {}, {}
87         self._cursor.execute(db_supports.AMULET_TABLE_SELECT_ALL_SQL)
88         for val in self._cursor.fetchall():
89             amulet_id, name = val[0], val[1]
90             amulet_id2name_dict[amulet_id] = name
91             amulet_name2id_dict[name] = amulet_id
92
93         # id 2 minmax table name
94         self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL)
95         for val in self._cursor.fetchall():
96             amulet_id, table = val[0], val[1]
97             id2minmax_table[amulet_id] = table
98  
99         # id 2 second table name
100         self._cursor.execute(db_supports.SECOND_MASTER_TABLE_SELECT_ALL_SQL)
101         for val in self._cursor.fetchall():
102             amulet_id, table = val[0], val[1]
103             id2second_table[amulet_id] = table
104
105         # id 2 sufficient table name
106         self._cursor.execute(db_supports.SUFFICIENT_MASTER_TABLE_SELECT_ALL_SQL)
107         for val in self._cursor.fetchall():
108             amulet_id, table = val[0], val[1]
109             id2suff_table[amulet_id] = table
110
111         # id 2 non zero skill1 ids
112         # id 2 non zero skill2 ids
113         for amu_id in amulet_id2name_dict.keys():
114             table = id2minmax_table[amu_id]
115             sql = db_supports.MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL1_ID_SQL.format(table_name=table)
116             self._cursor.execute(sql)
117             ls = [x[0] for x in self._cursor.fetchall()]
118             id2skill1_ids[amu_id] = tuple(ls)
119
120             sql = db_supports.MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL2_ID_SQL.format(table_name=table)
121             self._cursor.execute(sql)
122             ls = [x[0] for x in self._cursor.fetchall()]
123             id2skill2_ids[amu_id] = tuple(ls)
124
125         return (amulet_id2name_dict, amulet_name2id_dict, 
126                 id2minmax_table, id2second_table, id2suff_table,
127                 id2skill1_ids, id2skill2_ids)
128
129     def select_seeds(self, amu_id2skill_id_list_dict):
130         u""" from dict of amulet_id to skill_id_list,
131         skill_id_list: list of skill_id, if skill_id is unknown, skill_id must be None
132         """
133         seed_set = None
134         for key, val in amu_id2skill_id_list_dict.items():
135             if key in self._amulet_id2second_table_dict:
136                 second_skill_table = self._amulet_id2second_table_dict[key]
137                 amulet_skill = AmuletSkillsHolder(val)
138
139                 if amulet_skill.is_empty():
140                     continue
141
142                 sql = db_supports.SECOND_TABLE_SELECT_SEED_SQL.format(table_name=second_skill_table) + amulet_skill.get_where_sql()
143                 self._cursor.execute(sql)
144
145                 if seed_set is None:
146                     seed_set = set([x[0] for x in self._cursor.fetchall()])
147                 else:
148                     seed_set = seed_set.intersection(set([x[0] for x in self._cursor.fetchall()]))
149
150         if seed_set is None:
151             return set()
152         else:
153             return seed_set
154
155     def select_seeds_from_sufficient_val(self, amu_id, sufficient_val, slot_num, skill2_id):
156         u""" get seeds set from slot number, skill2_id, amulet id, sufficient value
157         """
158         if amu_id in self._amulet_id2name_dict.keys():
159             table = self._amulet_id2suff_table_dict[amu_id]
160             sql = db_supports.SUFFICIENT_TABLE_SELECT_THRESHOLDS_SQL.format(
161                     table_name=table, sufficient_val=sufficient_val)
162             self._cursor.execute(sql)
163             try:
164                 threshold2 = self._cursor.fetchone()[slot_num-1]
165             except TypeError, e:
166                 threshold2 = None
167
168             if threshold2 is not None:
169                 table = self._amulet_id2second_table_dict[amu_id]
170                 sql = db_supports.SECOND_SLOT_TABLE_SELECT_SEEDS_FROM_THRESHOLD_AND_SKILL2_SQL.format(
171                         threshold=threshold2,
172                         skill_table=table,
173                         skill_id=skill2_id)
174                 self._cursor.execute(sql)
175                 return set([x[0] for x in self._cursor.fetchall()])
176             else:
177                 return set()
178         else:
179             return set()
180
181     def select_minmax_from_skill_ids(self, amulet_id, skill_ids):
182         u""" お守りのidとスキルidのリストから、スキルIDと取りうるスキルの最大最小値の辞書を得る
183             return dict{skill_id:(min1, max1, min2, max2)}
184         """
185         minmax_table = self._amulet_id2minmax_table_dict[amulet_id]
186         amulet_skill = AmuletSkillsHolder(skill_ids)
187         result_dict = {}
188         if amulet_skill.is_empty() is False:
189             sql = db_supports.MIN_MAX_TABLE_SELECT_ALL_SQL.format(table_name=minmax_table) + amulet_skill.get_where_in_sql()
190             self._cursor.execute(sql)
191
192             for row in self._cursor.fetchall():
193                 result_dict[row[0]] = tuple(row[1:])
194         return result_dict
195
196     def select_threshold_from_sufficient(self, amulet_id, sufficient_vals):
197         u""" お守りのidと充足値のリストから、充足値とスロットごとの判定値の辞書を得る
198             return dict({sufficient_val:(slot1_th, slot2_th, slot3_th)})
199         """
200         suff_table = self._amulet_id2suff_table_dict[amulet_id]
201         amulet_skill = SufficientsHolder(sufficient_vals)
202         result_dict = {}
203         if amulet_skill.is_empty() is False:
204             sql = db_supports.SUFFICIENT_TABLE_SELECT_ALL_SQL.format(table_name=suff_table) + amulet_skill.get_where_in_sql()
205             self._cursor.execute(sql)
206
207             for row in self._cursor.fetchall():
208                 result_dict[row[0]] = tuple(row[1])
209         return result_dict
210
211     def select_skills_from_seeds(self, seed_set):
212         u""" get amulet id to (random_seed to skill ids dict) dict,
213         and random_seed to slot value dict
214         return: skill_dict, slot_dict
215         """
216         skill_dict, slot_dict = {}, {}
217
218         # skill dict
219         seeds = RandomSeedsHolder(seed_set)
220         for amu_id, table in self._amulet_id2second_table_dict.items():
221             sql = db_supports.SECOND_TABLE_SELECT_ALL_SQL.format(
222                     table_name=table) + seeds.get_where_sql()
223             self._cursor.execute(sql)
224             seed2skills_dic = {}
225             for row in self._cursor.fetchall():
226                 seed2skills_dic[row[0]] = [x for x in row[1:]]
227             skill_dict[amu_id] = seed2skills_dic
228
229         # slot dict
230         sql = db_supports.SECOND_SLOT_TABLE_SELECT_ALL_SQL + seeds.get_where_sql()
231         self._cursor.execute(sql)
232         for row in self._cursor.fetchall():
233             slot_dict[row[0]] = [x for x in row[1:]]
234
235         return skill_dict, slot_dict
236
237     def get_sufficient_value(self, amu_id, skill1_id, skill2_id, skill1_val, skill2_val):
238         u""" 充足値を計算する。
239         指定したスキルが見つからない場合はNoneを返す 
240         return (sufficient_val, max1, max2)
241         """
242         
243         if amu_id in self._amulet_id2minmax_table_dict:
244             table = self._amulet_id2minmax_table_dict[amu_id]
245             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_OF_SKILL_SQL.format(
246                     table_name=table,skill_id=skill1_id)
247             self._cursor.execute(sql)
248             max1 = self._cursor.fetchone()[0]
249             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_OF_SKILL_SQL.format(
250                     table_name=table,skill_id=skill2_id)
251             self._cursor.execute(sql)
252             max2 = self._cursor.fetchone()[0]
253
254             if max1 is None or max2 is None:
255                 return None
256
257             #print skill1_val, "/", max1 , skill2_val, "/", max2
258             try:
259                 skill1_val = max1 if skill1_val > max1 else skill1_val
260                 skill2_val = max2 if skill2_val > max2 else skill2_val
261                 val1 = (10 * skill1_val) // max1 if skill1_val >= 0 else 0
262                 val2 = (10 * skill2_val) // max2 if skill2_val >= 0 else 0
263             except TypeError, e:
264                 return None
265             except ZeroDivisionError, e:
266                 return None
267             else:
268                 return  (val1 + val2, max1, max2)
269
270         else:
271             return None
272
273     def get_skill_minmax(self):
274         u""" 全てのお守りのスキルにおいて、その最大値と最小値を返す 
275             return (min1, max1, min2, max2)
276         """
277         min1_ls, max1_ls, min2_ls, max2_ls = [],[],[],[]
278         # get min max of each amulet
279         for amu_id, table in self._amulet_id2minmax_table_dict.items():
280             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN1_SQL.format(table_name=table)
281             self._cursor.execute(sql)
282             min1_ls.append(self._cursor.fetchone()[0])
283
284             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_SQL.format(table_name=table)
285             self._cursor.execute(sql)
286             max1_ls.append(self._cursor.fetchone()[0])
287
288             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN2_SQL.format(table_name=table)
289             self._cursor.execute(sql)
290             min2_ls.append(self._cursor.fetchone()[0])
291
292             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_SQL.format(table_name=table)
293             self._cursor.execute(sql)
294             max2_ls.append(self._cursor.fetchone()[0])
295
296         return (min(min1_ls), max(max1_ls), min(min2_ls), max(max2_ls))
297
298     def get_skill_ids(self, amulet_id):
299         u""" 全てのお守りのスキルにおいて、その最大値と最小値を返す 
300             return (min1, max1, min2, max2)
301         """
302         min1_ls, max1_ls, min2_ls, max2_ls = [],[],[],[]
303         # get min max of each amulet
304         for amu_id, table in self._amulet_id2minmax_table_dict.items():
305             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN1_SQL.format(table_name=table)
306             self._cursor.execute(sql)
307             min1_ls.append(self._cursor.fetchone()[0])
308
309             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_SQL.format(table_name=table)
310             self._cursor.execute(sql)
311             max1_ls.append(self._cursor.fetchone()[0])
312
313             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN2_SQL.format(table_name=table)
314             self._cursor.execute(sql)
315             min2_ls.append(self._cursor.fetchone()[0])
316
317             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_SQL.format(table_name=table)
318             self._cursor.execute(sql)
319             max2_ls.append(self._cursor.fetchone()[0])
320
321         return (min(min1_ls), max(max1_ls), min(min2_ls), max(max2_ls))
322
323     def close(self):
324         u""" close database accessor """
325         self._cursor.close()
326         self._connect.close()
327
328 if __name__ == "__main__":
329     db = DataBaseAccessor("test.sqlite3")
330     #db._print_dicts()
331     hikari = [None, 57, None, None, None, None, None]
332     huru = [54, None, None, None, None, None, None]
333     #yuga = [None, None, 98, 75, None, None, None]
334     yuga = [None, None, 98, None, None, None, None]
335     dic = {2:hikari, 3:huru, 4:yuga}
336     #seeds = db.select_seeds(dic)
337     seeds = set([58241, 176])
338     skill_dic, slot_dic = db.select_skills_from_seeds(seeds)
339
340     print "seeds: ", seeds
341     print "amu_id to seed2skill dict: ", skill_dic
342     print "seed2slot dict", slot_dic
343     db.close()