OSDN Git Service

1af496d21a3337967fdda087f5e436c30c73023e
[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 amuletidskillidholder import AmuletIdSkillIdHolder
12 from seedsholder import SeedsHolder
13 from sufficientsholder import SufficientsHolder
14
15 class DataBaseAccessor(object):
16     u""" this is access class to database """
17     def __init__(self, db_name):
18         u""" db_name is database name to access """
19         self._db_name = db_name
20         self._connect = sqlite3.connect(self._db_name)
21         self._cursor = self._connect.cursor()
22
23         self._skill_id2name_dict, self._skill_name2id_dict = self._get_skill_dict()
24         (self._amulet_id2name_dict, self._amulet_name2id_dict, 
25             self._amulet_id2minmax_table_dict, self._amulet_id2skill2_table_dict,
26             self._amulet_id2sufficient_table_dict,
27             self._amulet_id2skill1_id_dict, self._amulet_id2skill2_id_dict) = self._get_amulet_dict()
28
29     def _print_dicts(self):
30         u""" for debug """
31         print self._skill_id2name_dict
32         print self._skill_name2id_dict
33         print self._amulet_id2name_dict
34         print self._amulet_name2id_dict
35         print self._amulet_id2minmax_table_dict
36         print self._amulet_id2skill2_table_dict
37         print self._amulet_id2sufficient_table_dict
38         print self._amulet_id2skill1_id_dict
39         print self._amulet_id2skill2_id_dict
40
41     def get_dicts(self, clone_enable=False):
42         u""" get all dicts 
43             if clone_enable is True, return cloend dictionary,
44             otherwise return reference dictionary
45
46             returns (skill_id2name_dict, skill_name2id_dict, 
47                 amulet_id2name_dict, amulet_name2id_dict,
48                 amulet_id2skill_ids)
49         """
50         if clone_enable:
51             return (dict(self._skill_id2name_dict), 
52                     dict(self._skill_name2id_dict),
53                     dict(self._amulet_id2name_dict), 
54                     dict(self._amulet_name2id_dict),
55                     dict(self._amulet_id2skill1_id_dict),
56                     dict(self._amulet_id2skill2_id_dict))
57         else:
58             return (self._skill_id2name_dict, 
59                     self._skill_name2id_dict,
60                     self._amulet_id2name_dict, 
61                     self._amulet_name2id_dict,
62                     self._amulet_id2skill1_id_dict,
63                     self._amulet_id2skill2_id_dict)
64
65
66     def _get_skill_dict(self):
67         u""" create id2name, name2id dict of skill 
68             return (dict_id2name, dict_name2id)
69         """
70         skill_id2name_dict, skill_name2id_dict = {}, {}
71         self._cursor.execute(db_supports.SKILL_TABLE_SELECT_ALL_SQL)
72         for val in self._cursor.fetchall():
73             skill_id, name = val[0], val[1]
74             skill_id2name_dict[skill_id] = name
75             skill_name2id_dict[name] = skill_id
76
77         return skill_id2name_dict, skill_name2id_dict
78
79     def _get_amulet_dict(self):
80         u""" create id2name, name2id, id2minmax_table, id2skill2_table, id2sufficient_table,
81             id2skill1_ids, id2skill2_id dict of amulet 
82         return (dict_id2name, dict_name2id, 
83             id2minmax_table, id2skill2_table, id2sufficient_table, id2skill1_ids, id2skill2_ids)
84         """
85         amulet_id2name_dict, amulet_name2id_dict = {}, {}
86         id2minmax_table, id2skill2_table, id2sufficient_table = {}, {}, {}
87         id2skill1_ids, id2skill2_ids = {}, {}
88         self._cursor.execute(db_supports.AMULET_TABLE_SELECT_ALL_SQL)
89         for val in self._cursor.fetchall():
90             amulet_id, name = val[0], val[1]
91             amulet_id2name_dict[amulet_id] = name
92             amulet_name2id_dict[name] = amulet_id
93
94         # id 2 minmax table name
95         self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL)
96         for val in self._cursor.fetchall():
97             amulet_id, table = val[0], val[1]
98             id2minmax_table[amulet_id] = table
99  
100         # id 2 skill2 table name
101         self._cursor.execute(db_supports.SEED2_SKILL2_MASTER_TABLE_SELECT_ALL_SQL)
102         for val in self._cursor.fetchall():
103             amulet_id, table = val[0], val[1]
104             id2skill2_table[amulet_id] = table
105
106         # id 2 sufficient table name
107         self._cursor.execute(db_supports.SUFFICIENT_MASTER_TABLE_SELECT_ALL_SQL)
108         for val in self._cursor.fetchall():
109             amulet_id, table = val[0], val[1]
110             id2sufficient_table[amulet_id] = table
111
112         # id 2 non zero skill1 ids
113         # id 2 non zero skill2 ids
114         for amu_id in amulet_id2name_dict.keys():
115             table = id2minmax_table[amu_id]
116             sql = db_supports.MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL1_ID_SQL.format(table_name=table)
117             self._cursor.execute(sql)
118             ls = [x[0] for x in self._cursor.fetchall()]
119             id2skill1_ids[amu_id] = tuple(ls)
120
121             sql = db_supports.MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL2_ID_SQL.format(table_name=table)
122             self._cursor.execute(sql)
123             ls = [x[0] for x in self._cursor.fetchall()]
124             id2skill2_ids[amu_id] = tuple(ls)
125
126         return (amulet_id2name_dict, amulet_name2id_dict, 
127                 id2minmax_table, id2skill2_table, id2sufficient_table,
128                 id2skill1_ids, id2skill2_ids)
129
130     def select_seed2s(self, amu_id2skill_id_list_dict):
131         u""" select seed2 from dict of amulet_id to skill2_id_list,
132         skill_id_list: list of skill_id, if skill_id is unknown, skill_id must be None
133         """
134         seed_set = None
135         for key, val in amu_id2skill_id_list_dict.items():
136             if key in self._amulet_id2skill2_table_dict:
137                 second_skill_table = self._amulet_id2skill2_table_dict[key]
138                 amulet_skill = AmuletSkillsHolder(val)
139
140                 if amulet_skill.is_empty():
141                     continue
142
143                 sql = db_supports.SEED2_SKILL2_TABLE_SELECT_SEED_SQL.format(table_name=second_skill_table) + amulet_skill.get_where_and_sql()
144                 self._cursor.execute(sql)
145
146                 if seed_set is None:
147                     seed_set = set([x[0] for x in self._cursor.fetchall()])
148                 else:
149                     seed_set = seed_set.intersection(set([x[0] for x in self._cursor.fetchall()]))
150
151         if seed_set is None:
152             return set()
153         else:
154             return seed_set
155
156     def select_seed1s_tenun(self, amu_id_skill1_id_list, key_tenun):
157         u""" select seed1 from list of amulet_id and skill1_id,
158         key_tenun must be KEY_TENUN555 or KEY_TENUN888
159         result_num: result number of amulet
160         when either amu_id or skill1_id is None, this means that no. has no result
161         """
162         result_num = len(amu_id_skill1_id_list)
163         for amu_id, skill1_id in reversed(amu_id_skill1_id_list):
164             if amu_id is not None and skill1_id is not None:
165                 break
166             else:
167                 result_num -= 1
168         amulet_ids = [x[0] for x in amu_id_skill1_id_list[:result_num]]
169         skill1_ids = [x[1] for x in amu_id_skill1_id_list[:result_num]]
170         holder = AmuletIdSkillIdHolder(amulet_ids, skill1_ids)
171
172         if key_tenun == db_supports.KEY_TENUN555:
173             sql = db_supports.SEED1_TENUN555_TABLE_SELECT_SQL + holder.get_where_and_sql()
174         elif key_tenun == db_supports.KEY_TENUN888:
175             sql = db_supports.SEED1_TENUN888_TABLE_SELECT_SQL + holder.get_where_and_sql()
176         else:
177             raise NotImplementedError(u"Tenun must be 555 or 888")
178         self._cursor.execute(sql)
179         seed_set = set([x[0] for x in self._cursor.fetchall()])
180         return seed_set
181
182     def select_seed2s_from_sufficient_val(self, amu_id, sufficient_val, slot_num, skill2_id):
183         u""" get seeds set from slot number, skill2_id, amulet id, sufficient value
184         if skill2_id is None, search all skills
185         return (threshold, threshold1_seed2_set, threshold2_seed2_set, inishie_seed2_set)
186         """
187         th1_seeds_set = set()
188         th2_seeds_set = set()
189         ini_seeds_set = set()
190         if amu_id in self._amulet_id2name_dict.keys():
191             table = self._amulet_id2sufficient_table_dict[amu_id]
192             # threshold
193             sql = db_supports.SUFFICIENT_TABLE_SELECT_THRESHOLD_SQL.format(
194                     table_name=table, sufficient_val=sufficient_val)
195             self._cursor.execute(sql)
196             try:
197                 threshold = self._cursor.fetchone()[slot_num-1]
198             except TypeError, e:
199                 threshold = None
200
201             if threshold is not None:
202                 table = self._amulet_id2skill2_table_dict[amu_id]
203                 if skill2_id is not None:
204                     sql1 = db_supports.SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_AND_SKILL2_SQL.format(
205                         threshold1=threshold,
206                         skill_table=table,
207                         skill_id=skill2_id)
208                     sql2 = db_supports.SEED2_THRESHOLD2_TABLE_SELECT_SEEDS_FROM_THRESHOLD2_AND_SKILL2_SQL.format(
209                         threshold2=threshold,
210                         skill_table=table,
211                         skill_id=skill2_id)
212                     sql3 = db_supports.SEED2_INISHIE_TABLE_SELECT_SEED2_FROM_SKILL2_THRESHOLD_SQL.format(
213                         threshold=threshold,
214                         skill2_id=skill2_id)
215                 else:
216                     sql1 = db_supports.SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_SQL.format(
217                         threshold1=threshold)
218                     sql2 = db_supports.SEED2_THRESHOLD2_TABLE_SELECT_SEEDS_FROM_THRESHOLD2_SQL.format(
219                         threshold2=threshold)
220                     sql3 = db_supports.SEED2_INISHIE_TABLE_SELECT_SEED2_FROM_THRESHOLD_SQL.format(
221                         threshold=threshold)
222                 self._cursor.execute(sql1)
223                 th1_seeds_set = set([x[0] for x in self._cursor.fetchall()])
224                 self._cursor.execute(sql2)
225                 th2_seeds_set = set([x[0] for x in self._cursor.fetchall()])
226
227                 if amu_id == self._amulet_name2id_dict[db_supports.INISHIE_AMULET]:
228                     self._cursor.execute(sql3)
229                     ini_seeds_set = set([x[0] for x in self._cursor.fetchall()])
230
231         return (threshold, th1_seeds_set, th2_seeds_set, ini_seeds_set)
232
233     def select_minmax_from_skill_ids(self, amulet_id, skill_ids):
234         u""" お守りのidとスキルidのリストから、スキルIDと取りうるスキルの最大最小値の辞書を得る
235             return dict{skill_id:(min1, max1, min2, max2)}
236         """
237         minmax_table = self._amulet_id2minmax_table_dict[amulet_id]
238         amulet_skill = AmuletSkillsHolder(skill_ids)
239         result_dict = {}
240         if amulet_skill.is_empty() is False:
241             sql = db_supports.MIN_MAX_TABLE_SELECT_ALL_SQL.format(table_name=minmax_table) + amulet_skill.get_where_in_sql()
242             self._cursor.execute(sql)
243
244             for row in self._cursor.fetchall():
245                 result_dict[row[0]] = tuple(row[1:])
246         return result_dict
247
248     def select_threshold_from_sufficient(self, amulet_id, sufficient_vals):
249         u""" お守りのidと充足値のリストから、充足値とスロットごとの判定値の辞書を得る
250             return dict({sufficient_val:(slot1_th, slot2_th, slot3_th)})
251         """
252         suff_table = self._amulet_idsufficient_table_dict[amulet_id]
253         amulet_skill = SufficientsHolder(sufficient_vals)
254         result_dict = {}
255         if amulet_skill.is_empty() is False:
256             sql = db_supports.SUFFICIENT_TABLE_SELECT_ALL_SQL.format(table_name=suff_table) + amulet_skill.get_where_in_sql()
257             self._cursor.execute(sql)
258
259             for row in self._cursor.fetchall():
260                 result_dict[row[0]] = tuple(row[1])
261         return result_dict
262
263     def select_skill2s_from_seed2s(self, seed_set):
264         u""" get amulet id to (random_seed2 to skill ids dict) dict,
265         and random_seed2 to threshold1 dict
266         and random_seed2 to threshold2 dict
267         return: skill_dict, threshold1_dict, threshold2_dict
268         """
269         skill_dict, threshold1_dict, threshold2_dict = {}, {}, {}
270
271         # skill dict
272         seeds = SeedsHolder(seed_set)
273         for amu_id, table in self._amulet_id2skill2_table_dict.items():
274             sql = db_supports.SEED2_SKILL2_TABLE_SELECT_ALL_SQL.format(
275                     table_name=table) + seeds.get_where_in_sql()
276             self._cursor.execute(sql)
277             seed2skills_dic = {}
278             for row in self._cursor.fetchall():
279                 seed2skills_dic[row[0]] = [x for x in row[1:]]
280             skill_dict[amu_id] = seed2skills_dic
281
282         # threshold1 dict
283         sql = db_supports.SEED2_THRESHOLD1_TABLE_SELECT_ALL_SQL + seeds.get_where_in_sql()
284         self._cursor.execute(sql)
285         for row in self._cursor.fetchall():
286             threshold1_dict[row[0]] = [x for x in row[1:]]
287
288         # threshold2 dict
289         sql = db_supports.SEED2_THRESHOLD2_TABLE_SELECT_ALL_SQL + seeds.get_where_in_sql()
290         self._cursor.execute(sql)
291         for row in self._cursor.fetchall():
292             threshold2_dict[row[0]] = [x for x in row[1:]]
293
294         return skill_dict, threshold1_dict, threshold2_dict
295
296     def select_inishie_skill2_from_seed2(self, seed2_set):
297         u""" get inishie's dict of (skill2_id, threshold1, threshold2) from seed2 value """
298         result_dict = {}
299         for seed2 in seed2_set:
300             sql = db_supports.SEED2_INISHIE_TABLE_SELECT_FROM_SEED2_SQL.format(seed2=seed2)
301             self._cursor.execute(sql)
302             row = self._cursor.fetchone()
303             result_dict[seed2] = (row[0], row[1], row[2])
304         return result_dict
305
306     def select_table_nos_from_seed2(self, seed2_set):
307         u""" get dict of (table_no, no) from seed2 value """
308         result_dict = {}
309         for seed2 in seed2_set:
310             sql = db_supports.SEED2_TABLE_NO_TABLE_SELECT_FROM_SEED2_SQL.format(seed2=seed2)
311             self._cursor.execute(sql)
312             row = self._cursor.fetchone()
313             result_dict[seed2] = (row[0], row[1])
314         return result_dict
315
316     def select_table_nos_from_seed1(self, seed1_set, key_tenun):
317         u""" get dict of (table_no, no) from seed1 value based on key_tenun"""
318         result_dict = {}
319         if key_tenun == db_supports.KEY_TENUN555:
320             sql_format = db_supports.SEED1_TENUN555_TABLE_SELECT_TABLE_NOS_FROM_SEED1_SQL
321         elif key_tenun == db_supports.KEY_TENUN888:
322             sql_format = db_supports.SEED1_TENUN888_TABLE_SELECT_TABLE_NOS_FROM_SEED1_SQL
323         else:
324             raise NotImplementedError(u"Tenun must be 555 or 888")
325
326         for seed1 in seed1_set:
327             sql = sql_format.format(seed1=seed1)
328             self._cursor.execute(sql)
329             row = self._cursor.fetchone()
330             result_dict[seed1] = (row[0], row[1])
331         return result_dict
332
333     def select_near_seed1s_from_seed1(self, seed1, smaller_num, larger_num, key_tenun):
334         u""" get list of (no, seed1) near arg's seed1 
335         smaller_num means numbers to get smaller than arg's seed1
336         larger_num means numbers to get larger than arg's seed1"""
337         result_list = []
338         if key_tenun == db_supports.KEY_TENUN555:
339             sql_format = db_supports.SEED1_TENUN555_TABLE_SELECT_NEAR_SEED1S_FROM_NO_SQL
340         elif key_tenun == db_supports.KEY_TENUN888:
341             sql_format = db_supports.SEED1_TENUN888_TABLE_SELECT_NEAR_SEED1S_FROM_NO_SQL
342         else:
343             raise NotImplementedError(u"Tenun must be 555 or 888")
344         table_no, no = self.select_table_nos_from_seed1([seed1], key_tenun)[seed1]
345         sql = sql_format.format(table_no=table_no,min_no=(no-smaller_num),max_no=(no+larger_num))
346         self._cursor.execute(sql)
347         for row in self._cursor.fetchall():
348             result_list.append((row[0], row[1]))
349         return result_list
350
351     def select_all_from_seed1s(self, seed1_set, key_tenun):
352         u""" seed1に対応する(no, table_no, result_num, 
353         (amulet_id1, ..., amulet_id7), (skill1_id1, ..., skill1_id7))
354         の辞書を得る。存在しない場所はNoneで置き換えてある。
355         """
356         result_dict = {}
357         if key_tenun == db_supports.KEY_TENUN555:
358             sql_format = db_supports.SEED1_TENUN555_TABLE_SELECT_ALL_FROM_SEED1_SQL
359         elif key_tenun == db_supports.KEY_TENUN888:
360             sql_format = db_supports.SEED1_TENUN888_TABLE_SELECT_ALL_FROM_SEED1_SQL
361         else:
362             raise NotImplementedError(u"Tenun must be 555 or 888")
363
364         for seed1 in seed1_set:
365             sql = sql_format.format(seed1=seed1)
366             self._cursor.execute(sql)
367             row = self._cursor.fetchone()
368             if key_tenun == db_supports.KEY_TENUN555:
369                 amu_ids = [None if x == db_supports.NO_DATA else x for x in row[3:9]]
370                 skill_ids = [None if x == db_supports.NO_DATA else x for x in row[9:15]]
371             elif key_tenun == db_supports.KEY_TENUN888:
372                 amu_ids = [None if x == db_supports.NO_DATA else x for x in row[3:10]]
373                 skill_ids = [None if x == db_supports.NO_DATA else x for x in row[10:17]]
374             else:
375                 raise NotImplementedError(u"Tenun must be 555 or 888")
376             result_dict[seed1] = (row[0], row[1], row[2], amu_ids, skill_ids)
377         return result_dict
378
379     def get_sufficient_value(self, amu_id, skill1_id, skill2_id, skill1_val, skill2_val):
380         u""" 充足値を計算する。
381         指定したスキルが見つからない場合はNoneを返す 
382         skill2_idがNoneのときはskill1のみから計算する
383         return (sufficient_val, max1, max2)
384         """
385         if amu_id in self._amulet_id2minmax_table_dict:
386             table = self._amulet_id2minmax_table_dict[amu_id]
387             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_OF_SKILL_SQL.format(
388                     table_name=table,skill_id=skill1_id)
389             self._cursor.execute(sql)
390             max1 = self._cursor.fetchone()[0]
391             if skill2_id is not None:
392                 sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_OF_SKILL_SQL.format(
393                         table_name=table,skill_id=skill2_id)
394                 self._cursor.execute(sql)
395                 max2 = self._cursor.fetchone()[0]
396             else:
397                 max2 = 1
398
399             if max1 is None or max2 is None:
400                 return None
401
402             #print skill1_val, "/", max1 , skill2_val, "/", max2
403             try:
404                 skill1_val = max1 if skill1_val > max1 else skill1_val
405                 if skill2_id is None:
406                     skill2_val = 0
407                 else:
408                     skill2_val = max2 if skill2_val > max2 else skill2_val
409                 val1 = (10 * skill1_val) // max1 if skill1_val >= 0 else 0
410                 val2 = (10 * skill2_val) // max2 if skill2_val >= 0 else 0
411             except TypeError, e:
412                 return None
413             except ZeroDivisionError, e:
414                 return None
415             else:
416                 return  (val1 + val2, max1, max2)
417
418         else:
419             return None
420
421     def get_skill_minmax(self):
422         u""" 全てのお守りのスキルにおいて、その最大値と最小値を返す 
423             return (min1, max1, min2, max2)
424         """
425         min1_ls, max1_ls, min2_ls, max2_ls = [],[],[],[]
426         # get min max of each amulet
427         for amu_id, table in self._amulet_id2minmax_table_dict.items():
428             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN1_SQL.format(table_name=table)
429             self._cursor.execute(sql)
430             min1_ls.append(self._cursor.fetchone()[0])
431
432             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_SQL.format(table_name=table)
433             self._cursor.execute(sql)
434             max1_ls.append(self._cursor.fetchone()[0])
435
436             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN2_SQL.format(table_name=table)
437             self._cursor.execute(sql)
438             min2_ls.append(self._cursor.fetchone()[0])
439
440             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_SQL.format(table_name=table)
441             self._cursor.execute(sql)
442             max2_ls.append(self._cursor.fetchone()[0])
443
444         return (min(min1_ls), max(max1_ls), min(min2_ls), max(max2_ls))
445
446     def get_skill_ids(self, amulet_id):
447         u""" 全てのお守りのスキルにおいて、その最大値と最小値を返す 
448             return (min1, max1, min2, max2)
449         """
450         min1_ls, max1_ls, min2_ls, max2_ls = [],[],[],[]
451         # get min max of each amulet
452         for amu_id, table in self._amulet_id2minmax_table_dict.items():
453             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN1_SQL.format(table_name=table)
454             self._cursor.execute(sql)
455             min1_ls.append(self._cursor.fetchone()[0])
456
457             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX1_SQL.format(table_name=table)
458             self._cursor.execute(sql)
459             max1_ls.append(self._cursor.fetchone()[0])
460
461             sql = db_supports.MIN_MAX_TABLE_SELECT_MIN2_SQL.format(table_name=table)
462             self._cursor.execute(sql)
463             min2_ls.append(self._cursor.fetchone()[0])
464
465             sql = db_supports.MIN_MAX_TABLE_SELECT_MAX2_SQL.format(table_name=table)
466             self._cursor.execute(sql)
467             max2_ls.append(self._cursor.fetchone()[0])
468
469         return (min(min1_ls), max(max1_ls), min(min2_ls), max(max2_ls))
470
471     def close(self):
472         u""" close database accessor """
473         self._cursor.close()
474         self._connect.close()
475
476 if __name__ == "__main__":
477     db = DataBaseAccessor("test.sqlite3")
478     #db._print_dicts()
479     hikari = [None, 57, None, None, None, None, None]
480     huru = [54, None, None, None, None, None, None]
481     #yuga = [None, None, 98, 75, None, None, None]
482     yuga = [None, None, 98, None, None, None, None]
483     dic = {2:hikari, 3:huru, 4:yuga}
484     #seeds = db.select_seeds(dic)
485     seeds = set([58241, 176])
486     skill_dic, slot_dic = db.select_skills_from_seeds(seeds)
487
488     print "seeds: ", seeds
489     print "amu_id to seed2skill dict: ", skill_dic
490     print "seed2slot dict", slot_dic
491     db.close()