OSDN Git Service

rename database_ids & some variables in order to understand easily
[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 seedsholder import SeedsHolder
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_id2skill2_table_dict,
25             self._amulet_id2sufficient_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_id2skill2_table_dict
36         print self._amulet_id2sufficient_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, id2skill2_table, id2sufficient_table,
80             id2skill1_ids, id2skill2_id dict of amulet 
81         return (dict_id2name, dict_name2id, 
82             id2minmax_table, id2skill2_table, id2sufficient_table, id2skill1_ids, id2skill2_ids)
83         """
84         amulet_id2name_dict, amulet_name2id_dict = {}, {}
85         id2minmax_table, id2skill2_table, id2sufficient_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 skill2 table name
100         self._cursor.execute(db_supports.SEED2_SKILL2_MASTER_TABLE_SELECT_ALL_SQL)
101         for val in self._cursor.fetchall():
102             amulet_id, table = val[0], val[1]
103             id2skill2_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             id2sufficient_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, id2skill2_table, id2sufficient_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_id2skill2_table_dict:
136                 second_skill_table = self._amulet_id2skill2_table_dict[key]
137                 amulet_skill = AmuletSkillsHolder(val)
138
139                 if amulet_skill.is_empty():
140                     continue
141
142                 sql = db_supports.SEED2_SKILL2_TABLE_SELECT_SEED_SQL.format(table_name=second_skill_table) + amulet_skill.get_where_and_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         is skill2_id is None, search all skills
158         """
159         if amu_id in self._amulet_id2name_dict.keys():
160             table = self._amulet_id2sufficient_table_dict[amu_id]
161             sql = db_supports.SUFFICIENT_TABLE_SELECT_THRESHOLD1_SQL.format(
162                     table_name=table, sufficient_val=sufficient_val)
163             self._cursor.execute(sql)
164             try:
165                 threshold1 = self._cursor.fetchone()[slot_num-1]
166             except TypeError, e:
167                 threshold1 = None
168
169             if threshold1 is not None:
170                 table = self._amulet_id2skill2_table_dict[amu_id]
171                 if skill2_id is not None:
172                     sql = db_supports.SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_AND_SKILL2_SQL.format(
173                         threshold1=threshold1,
174                         skill_table=table,
175                         skill_id=skill2_id)
176                 else:
177                     sql = db_supports.SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_SQL.format(
178                         threshold1=threshold1)
179                 self._cursor.execute(sql)
180                 return set([x[0] for x in self._cursor.fetchall()])
181             else:
182                 return set()
183         else:
184             return set()
185
186     def select_minmax_from_skill_ids(self, amulet_id, skill_ids):
187         u""" お守りのidとスキルidのリストから、スキルIDと取りうるスキルの最大最小値の辞書を得る
188             return dict{skill_id:(min1, max1, min2, max2)}
189         """
190         minmax_table = self._amulet_id2minmax_table_dict[amulet_id]
191         amulet_skill = AmuletSkillsHolder(skill_ids)
192         result_dict = {}
193         if amulet_skill.is_empty() is False:
194             sql = db_supports.MIN_MAX_TABLE_SELECT_ALL_SQL.format(table_name=minmax_table) + amulet_skill.get_where_in_sql()
195             self._cursor.execute(sql)
196
197             for row in self._cursor.fetchall():
198                 result_dict[row[0]] = tuple(row[1:])
199         return result_dict
200
201     def select_threshold1_from_sufficient(self, amulet_id, sufficient_vals):
202         u""" お守りのidと充足値のリストから、充足値とスロットごとの判定値1の辞書を得る
203             return dict({sufficient_val:(slot1_th1, slot2_th1, slot3_th1)})
204         """
205         suff_table = self._amulet_idsufficient_table_dict[amulet_id]
206         amulet_skill = SufficientsHolder(sufficient_vals)
207         result_dict = {}
208         if amulet_skill.is_empty() is False:
209             sql = db_supports.SUFFICIENT_TABLE_SELECT_ALL_SQL.format(table_name=suff_table) + amulet_skill.get_where_in_sql()
210             self._cursor.execute(sql)
211
212             for row in self._cursor.fetchall():
213                 result_dict[row[0]] = tuple(row[1])
214         return result_dict
215
216     def select_skills_from_seeds(self, seed_set):
217         u""" get amulet id to (random_seed2 to skill ids dict) dict,
218         and random_seed2 to threshold1 dict
219         return: skill_dict, threshold1_dict
220         """
221         skill_dict, threshold1_dict = {}, {}
222
223         # skill dict
224         seeds = SeedsHolder(seed_set)
225         for amu_id, table in self._amulet_id2skill2_table_dict.items():
226             sql = db_supports.SEED2_SKILL2_TABLE_SELECT_ALL_SQL.format(
227                     table_name=table) + seeds.get_where_in_sql()
228             self._cursor.execute(sql)
229             seed2skills_dic = {}
230             for row in self._cursor.fetchall():
231                 seed2skills_dic[row[0]] = [x for x in row[1:]]
232             skill_dict[amu_id] = seed2skills_dic
233
234         # threshold1 dict
235         sql = db_supports.SEED2_THRESHOLD1_TABLE_SELECT_ALL_SQL + seeds.get_where_in_sql()
236         self._cursor.execute(sql)
237         for row in self._cursor.fetchall():
238             threshold1_dict[row[0]] = [x for x in row[1:]]
239
240         return skill_dict, threshold1_dict
241
242     def get_sufficient_value(self, amu_id, skill1_id, skill2_id, skill1_val, skill2_val):
243         u""" 充足値を計算する。
244         指定したスキルが見つからない場合はNoneを返す 
245         skill2_idがNoneのときはskill1のみから計算する
246         return (sufficient_val, max1, max2)
247         """
248         
249         if amu_id in self._amulet_id2minmax_table_dict:
250             table = self._amulet_id2minmax_table_dict[amu_id]
251             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_OF_SKILL_SQL.format(
252                     table_name=table,skill_id=skill1_id)
253             self._cursor.execute(sql)
254             max1 = self._cursor.fetchone()[0]
255             if skill2_id is not None:
256                 sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_OF_SKILL_SQL.format(
257                         table_name=table,skill_id=skill2_id)
258                 self._cursor.execute(sql)
259                 max2 = self._cursor.fetchone()[0]
260             else:
261                 max2 = 1
262
263             if max1 is None or max2 is None:
264                 return None
265
266             #print skill1_val, "/", max1 , skill2_val, "/", max2
267             try:
268                 skill1_val = max1 if skill1_val > max1 else skill1_val
269                 if skill2_id is None:
270                     skill2_val = 0
271                 else:
272                     skill2_val = max2 if skill2_val > max2 else skill2_val
273                 val1 = (10 * skill1_val) // max1 if skill1_val >= 0 else 0
274                 val2 = (10 * skill2_val) // max2 if skill2_val >= 0 else 0
275             except TypeError, e:
276                 return None
277             except ZeroDivisionError, e:
278                 return None
279             else:
280                 return  (val1 + val2, max1, max2)
281
282         else:
283             return None
284
285     def get_skill_minmax(self):
286         u""" 全てのお守りのスキルにおいて、その最大値と最小値を返す 
287             return (min1, max1, min2, max2)
288         """
289         min1_ls, max1_ls, min2_ls, max2_ls = [],[],[],[]
290         # get min max of each amulet
291         for amu_id, table in self._amulet_id2minmax_table_dict.items():
292             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN1_SQL.format(table_name=table)
293             self._cursor.execute(sql)
294             min1_ls.append(self._cursor.fetchone()[0])
295
296             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_SQL.format(table_name=table)
297             self._cursor.execute(sql)
298             max1_ls.append(self._cursor.fetchone()[0])
299
300             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN2_SQL.format(table_name=table)
301             self._cursor.execute(sql)
302             min2_ls.append(self._cursor.fetchone()[0])
303
304             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_SQL.format(table_name=table)
305             self._cursor.execute(sql)
306             max2_ls.append(self._cursor.fetchone()[0])
307
308         return (min(min1_ls), max(max1_ls), min(min2_ls), max(max2_ls))
309
310     def get_skill_ids(self, amulet_id):
311         u""" 全てのお守りのスキルにおいて、その最大値と最小値を返す 
312             return (min1, max1, min2, max2)
313         """
314         min1_ls, max1_ls, min2_ls, max2_ls = [],[],[],[]
315         # get min max of each amulet
316         for amu_id, table in self._amulet_id2minmax_table_dict.items():
317             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN1_SQL.format(table_name=table)
318             self._cursor.execute(sql)
319             min1_ls.append(self._cursor.fetchone()[0])
320
321             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_SQL.format(table_name=table)
322             self._cursor.execute(sql)
323             max1_ls.append(self._cursor.fetchone()[0])
324
325             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN2_SQL.format(table_name=table)
326             self._cursor.execute(sql)
327             min2_ls.append(self._cursor.fetchone()[0])
328
329             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_SQL.format(table_name=table)
330             self._cursor.execute(sql)
331             max2_ls.append(self._cursor.fetchone()[0])
332
333         return (min(min1_ls), max(max1_ls), min(min2_ls), max(max2_ls))
334
335     def close(self):
336         u""" close database accessor """
337         self._cursor.close()
338         self._connect.close()
339
340 if __name__ == "__main__":
341     db = DataBaseAccessor("test.sqlite3")
342     #db._print_dicts()
343     hikari = [None, 57, None, None, None, None, None]
344     huru = [54, None, None, None, None, None, None]
345     #yuga = [None, None, 98, 75, None, None, None]
346     yuga = [None, None, 98, None, None, None, None]
347     dic = {2:hikari, 3:huru, 4:yuga}
348     #seeds = db.select_seeds(dic)
349     seeds = set([58241, 176])
350     skill_dic, slot_dic = db.select_skills_from_seeds(seeds)
351
352     print "seeds: ", seeds
353     print "amu_id to seed2skill dict: ", skill_dic
354     print "seed2slot dict", slot_dic
355     db.close()