1 # -*- coding: utf-8 -*-
4 SEED1とスキル名、お守り名の組み合わせテーブルの作成・アクセスをするモジュール
5 2013/12/19 written by kei9
11 import mh4constnumbers
16 # for seed1 table by tenun
17 NAME = u"seed1_table_{alchemy_type}"
18 COL_TABLE = u"table_no"
21 COL_RESULT_NO = u"result_no"
22 COL_AMULET1 = u"amulet_id1"
23 COL_AMULET2 = u"amulet_id2"
24 COL_AMULET3 = u"amulet_id3"
25 COL_AMULET4 = u"amulet_id4"
26 COL_AMULET5 = u"amulet_id5"
27 COL_AMULET6 = u"amulet_id6"
28 COL_AMULET7 = u"amulet_id7" # 555 doesn't has this col
29 COL_AMULET_LIST = [COL_AMULET1, COL_AMULET2, COL_AMULET3,
30 COL_AMULET4, COL_AMULET5, COL_AMULET6, COL_AMULET7]
31 COL_SKILL1_1 = u"skill1_1"
32 COL_SKILL1_2 = u"skill1_2"
33 COL_SKILL1_3 = u"skill1_3"
34 COL_SKILL1_4 = u"skill1_4"
35 COL_SKILL1_5 = u"skill1_5"
36 COL_SKILL1_6 = u"skill1_6"
37 COL_SKILL1_7 = u"skill1_7" # 555 doesn't has this col
38 COL_SKILL1_LIST = [COL_SKILL1_1, COL_SKILL1_2, COL_SKILL1_3,
39 COL_SKILL1_4, COL_SKILL1_5, COL_SKILL1_6, COL_SKILL1_7]
42 CREATE_SQL = u"""create table if not exists {{table_name}}
43 ({no} integer primary key, {table_no} integer, {seed} integer unique, {result_num} integer,
44 {amulet_id1} integer, {amulet_id2} integer, {amulet_id3} integer,
45 {amulet_id4} integer, {amulet_id5} integer, {amulet_id6} integer, {amulet_id7} integer,
46 {skill_id1} integer, {skill_id2} integer, {skill_id3} integer,
47 {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer,
48 foreign key({amulet_id1}) references {amulet_table}(id),
49 foreign key({amulet_id2}) references {amulet_table}(id),
50 foreign key({amulet_id3}) references {amulet_table}(id),
51 foreign key({amulet_id4}) references {amulet_table}(id),
52 foreign key({amulet_id5}) references {amulet_table}(id),
53 foreign key({amulet_id6}) references {amulet_table}(id),
54 foreign key({amulet_id7}) references {amulet_table}(id),
55 foreign key({skill_id1}) references {skill_table}(id),
56 foreign key({skill_id2}) references {skill_table}(id),
57 foreign key({skill_id3}) references {skill_table}(id),
58 foreign key({skill_id4}) references {skill_table}(id),
59 foreign key({skill_id5}) references {skill_table}(id),
60 foreign key({skill_id6}) references {skill_table}(id),
61 foreign key({skill_id7}) references {skill_table}(id)
66 result_num=COL_RESULT_NO,
67 amulet_id1=COL_AMULET1,
68 amulet_id2=COL_AMULET2,
69 amulet_id3=COL_AMULET3,
70 amulet_id4=COL_AMULET4,
71 amulet_id5=COL_AMULET5,
72 amulet_id6=COL_AMULET6,
73 amulet_id7=COL_AMULET7,
74 skill_id1=COL_SKILL1_1,
75 skill_id2=COL_SKILL1_2,
76 skill_id3=COL_SKILL1_3,
77 skill_id4=COL_SKILL1_4,
78 skill_id5=COL_SKILL1_5,
79 skill_id6=COL_SKILL1_6,
80 skill_id7=COL_SKILL1_7,
81 amulet_table=amulettable.NAME,
82 skill_table=skilltable.NAME)
83 INSERT_SQL = u"""insert into {{table_name}}
84 ({no}, {table_no}, {seed}, {result_num},
85 {amulet_id1}, {amulet_id2}, {amulet_id3},
86 {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7},
87 {skill_id1}, {skill_id2}, {skill_id3},
88 {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7})
89 values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);""".format(
93 result_num=COL_RESULT_NO,
94 amulet_id1=COL_AMULET1,
95 amulet_id2=COL_AMULET2,
96 amulet_id3=COL_AMULET3,
97 amulet_id4=COL_AMULET4,
98 amulet_id5=COL_AMULET5,
99 amulet_id6=COL_AMULET6,
100 amulet_id7=COL_AMULET7,
101 skill_id1=COL_SKILL1_1,
102 skill_id2=COL_SKILL1_2,
103 skill_id3=COL_SKILL1_3,
104 skill_id4=COL_SKILL1_4,
105 skill_id5=COL_SKILL1_5,
106 skill_id6=COL_SKILL1_6,
107 skill_id7=COL_SKILL1_7)
108 SELECT_SQL = u""" select {seed} from {{table_name}}
109 """.format(seed=COL_SEED1)
110 SELECT_TABLE_NOS_FROM_SEED1_SQL = u"""select {table_no}, {no} from {{table_name}}
111 where {seed1}={{seed1}}""".format(
115 SELECT_NEAR_SEED1S_FROM_NO_SQL = u"""select {no}, {seed1}, {result_num},
116 {amulet_id1}, {amulet_id2}, {amulet_id3},
117 {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7},
118 {skill1_id1}, {skill1_id2}, {skill1_id3},
119 {skill1_id4}, {skill1_id5}, {skill1_id6}, {skill1_id7}
121 where {table_no}={{table_no}} and {no}>={{min_no}} and {no}<={{max_no}}
122 order by {no} ASC """.format(
126 result_num=COL_RESULT_NO,
127 amulet_id1=COL_AMULET1,
128 amulet_id2=COL_AMULET2,
129 amulet_id3=COL_AMULET3,
130 amulet_id4=COL_AMULET4,
131 amulet_id5=COL_AMULET5,
132 amulet_id6=COL_AMULET6,
133 amulet_id7=COL_AMULET7,
134 skill1_id1=COL_SKILL1_1,
135 skill1_id2=COL_SKILL1_2,
136 skill1_id3=COL_SKILL1_3,
137 skill1_id4=COL_SKILL1_4,
138 skill1_id5=COL_SKILL1_5,
139 skill1_id6=COL_SKILL1_6,
140 skill1_id7=COL_SKILL1_7)
141 SELECT_NEAR_NAMES_FROM_NO_SQL = u"""select {no}, {seed1} from {{table_name}}
142 where {table_no}={{table_no}} and {no}>={{min_no}} and {no}<={{max_no}}
143 order by {no} ASC """.format(
147 SELECT_ALL_FROM_SEED1_SQL = u"""select {no}, {table_no}, {result_num},
148 {amulet_id1}, {amulet_id2}, {amulet_id3},
149 {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7},
150 {skill1_id1}, {skill1_id2}, {skill1_id3},
151 {skill1_id4}, {skill1_id5}, {skill1_id6}, {skill1_id7}
152 from {{table_name}} where {seed1}={{seed1}} """.format(
156 result_num=COL_RESULT_NO,
157 amulet_id1=COL_AMULET1,
158 amulet_id2=COL_AMULET2,
159 amulet_id3=COL_AMULET3,
160 amulet_id4=COL_AMULET4,
161 amulet_id5=COL_AMULET5,
162 amulet_id6=COL_AMULET6,
163 amulet_id7=COL_AMULET7,
164 skill1_id1=COL_SKILL1_1,
165 skill1_id2=COL_SKILL1_2,
166 skill1_id3=COL_SKILL1_3,
167 skill1_id4=COL_SKILL1_4,
168 skill1_id5=COL_SKILL1_5,
169 skill1_id6=COL_SKILL1_6,
170 skill1_id7=COL_SKILL1_7)
172 # extract skill1 by place
173 SELECT_SEED1_SQL = u"""select
174 {seed1} from {{table_name}} """.format(
176 # select seed1 number with skill1 and place
177 SELECT_SEED1_BY_SKILL1_SQL = u"""select
178 {seed1} from {{table_name}}
179 where {{amulet_col}}={{amulet_id}} and
180 {{skill1_col}}={{skill1_id}}""".format(
183 # count seed1 number with skill1 and place
184 COUNT_SEED1_SQL = u"""select
185 count({seed1}) from {{table_name}} """.format(
187 # count seed1 number with skill1 and place
188 COUNT_SEED1_BY_SKILL1_SQL = u"""select
189 count({seed1}) from {{table_name}}
190 where {{amulet_col}}={{amulet_id}} and
191 {{skill1_col}}={{skill1_id}}""".format(
194 class Seed1TenunTableGenerator(object):
195 u""" スキルIDとスキル名の組み合わせテーブルを作成するクラス """
200 def insert_data(self, db_cursor, csv_reader, key_alchemy):
201 u""" csv_readerからデータを読み込み、 db_cursorへデータを挿入する。
202 key_alchemyはKEY_TENUN555 or KEY_TENUN888"""
203 if (key_alchemy == mh4constnumbers.KEY_TENUN555 or
204 key_alchemy == mh4constnumbers.KEY_TENUN888):
205 table_name = NAME.format(alchemy_type=key_alchemy)
207 raise NotImplementedError(u"this alchemy type is not supported")
208 amu_accessor = amulettable.AmuletTableAccessor(db_cursor)
209 skill_accessor = skilltable.SkillTableAccessor(db_cursor)
210 amu_id2name, amu_name2id = amu_accessor.get_dict()
211 skill_id2name, skill_name2id = skill_accessor.get_dict()
213 db_cursor.execute(CREATE_SQL.format(table_name=table_name))
215 insert_sql = INSERT_SQL.format(table_name=table_name)
216 csv_reader.next() # skip header row
217 csv_reader.next() # skip header row
218 for row in csv_reader:
219 # (table_no, no, seed1, result_no, amu1, amu2, ...,, amu7, skill_id1, skill_id2, ..., skill_id7)
220 vals = [x.strip() for x in row]
221 table_no, no, seed1, result_no = vals[0], vals[1], vals[2],vals[3]
222 amu_ids = [amu_name2id[x] if x in amu_name2id else mh4constnumbers.NO_DATA for x in vals[4:11]]
224 amu_ids += [mh4constnumbers.NO_DATA] * (7-len(amu_ids))
225 skill_ids = [skill_name2id[x] if x in skill_name2id else mh4constnumbers.NO_DATA for x in vals[11:18]]
226 if len(skill_ids) < 7:
227 skill_ids += [mh4constnumbers.NO_DATA] * (7-len(skill_ids))
228 vals = [no, table_no, seed1, result_no] + amu_ids + skill_ids
229 db_cursor.execute(insert_sql, tuple(vals))
231 class Seed1TenunTableAccessor(object):
232 u""" スキルIDとスキル名の組み合わせテーブルへのアクセス用クラス """
233 #__metaclass__ = singleton.Singleton
234 def __init__(self, db_cursor):
235 u""" db_cursor: cursor of sqlite3 database """
236 self._cursor = db_cursor
237 amu_accessor = amulettable.AmuletTableAccessor(db_cursor)
238 skill_accessor = skilltable.SkillTableAccessor(db_cursor)
239 self._amu_id2name, self._amu_name2id = amu_accessor.get_dict()
240 self._skill_id2name, self._skill_name2id = skill_accessor.get_dict()
242 def select_seed1s_from_ids(self, amuid_skillid_list, key_alchemy):
243 u""" お守りIDとスキルIDのリストから、条件を満たすSeed1を検索する
244 amuid_skillid_list: [(amu_id, skill_id),...]"""
245 if (key_alchemy == mh4constnumbers.KEY_TENUN555 or
246 key_alchemy == mh4constnumbers.KEY_TENUN888):
247 table_name = NAME.format(alchemy_type=key_alchemy)
249 raise NotImplementedError(u"this alchemy type is not supported")
253 for (amu_id, skill_id), amu_col, skill_col in zip(
254 amuid_skillid_list, COL_AMULET_LIST, COL_SKILL1_LIST):
255 if amu_id in self._amu_id2name and skill_id in self._skill_id2name:
256 where_list.append("{0}={1} and {2}={3}".format(amu_id, amu_col, skill_id, skill_col))
258 if len(where_list) > 0:
259 sql = SELECT_SQL.format(table_name=table_name) + " where " + " and ".join(where_list)
260 self._cursor.execute(sql)
261 result = set([x[0] for x in self._cursor.fetchall()])
265 def select_seed1s_from_names(self, amuname_skillname_list, key_alchemy):
266 u""" お守りIDとスキルIDのリストから、条件を満たすSeed1を検索する
267 amuname_skillname_list: [(amu_name, skill_name),...]"""
268 amuid_skillid_list = [
269 (self._amu_name2id[amu_name] if amu_name in self._amu_name2id else None,
270 self._skill_name2id[skill_name] if skill_name in self._skill_name2id else None)
271 for amu_name, skill_name in amuname_skillname_list]
272 return self.select_seed1s_from_ids(amuid_skillid_list, key_alchemy)
274 def select_table_nos_from_seed1(self, seed1, key_alchemy):
275 u""" Seed1と錬金の種類から通しNo,テーブルNo,お守り個数を返す。
276 return (no, table_no, result_num)"""
277 if (key_alchemy == mh4constnumbers.KEY_TENUN555 or
278 key_alchemy == mh4constnumbers.KEY_TENUN888):
279 table_name = NAME.format(alchemy_type=key_alchemy)
281 raise NotImplementedError(u"this alchemy type is not supported")
283 sql = SELECT_ALL_FROM_SEED1_SQL.format(table_name=table_name, seed1=seed1)
284 self._cursor.execute(sql)
285 row = self._cursor.fetchone()
287 no, table_no, result_num = row[0], row[1], row[2]
288 return (no, table_no, result_num)
289 except TypeError as e:
290 raise KeyError(u"seed1:{0} not found".format(seed1))
292 def select_ids_from_seed1(self, seed1, key_alchemy):
293 u""" Seed1と錬金の種類から通しNo,テーブルNo,お守り個数,
294 お守りId(1-7)、スキルID(1-7)を返す。値が存在しない場所はNoneで埋める。
295 return (no, table_no, result_num, (amulet_ids), (skill_ids))"""
296 if (key_alchemy == mh4constnumbers.KEY_TENUN555 or
297 key_alchemy == mh4constnumbers.KEY_TENUN888):
298 table_name = NAME.format(alchemy_type=key_alchemy)
300 raise NotImplementedError(u"this alchemy type is not supported")
302 sql = SELECT_ALL_FROM_SEED1_SQL.format(table_name=table_name, seed1=seed1)
303 self._cursor.execute(sql)
304 row = self._cursor.fetchone()
305 no, table_no, result_num = row[0], row[1], row[2]
306 amulet_ids = [x if x in self._amu_id2name else None for x in row[3:10]]
307 skill_ids = [x if x in self._skill_id2name else None for x in row[10:]]
308 return (no, table_no, result_num, amulet_ids, skill_ids)
310 def select_names_from_seed1(self, seed1, key_alchemy):
311 u""" Seed1と錬金の種類から通しNo,テーブルNo,お守り個数,
312 お守り名(1-7)、スキル名(1-7)を返す。値が存在しない場所は空文字列で埋める。
313 return (no, table_no, result_num, (amulet_names), (skill_names))"""
314 (no, table_no, result_num, amulet_ids, skill_ids) = self.select_ids_from_seed1(seed1, key_alchemy)
315 amulet_names = [self._amu_id2name[x] if x in self._amu_id2name else u"" for x in amulet_ids]
316 skill_names = [self._skill_id2name[x] if x in self._skill_id2name else u"" for x in skill_ids]
317 return (no, table_no, result_num, amulet_names, skill_names)
319 def select_near_ids_from_seed1(self, no, table_no, smaller_num, larger_num, key_alchemy):
320 u""" 通し番号とテーブル番および錬金の種類からその周囲のseed1の通し番号、テーブルNo.、
321 お守り個数, お守りId(1-7)、スキルID(1-7)を辞書として返す。
324 return {no: (seed1, result_num, (amulet_ids), (skill_ids))}"""
325 if (key_alchemy == mh4constnumbers.KEY_TENUN555 or
326 key_alchemy == mh4constnumbers.KEY_TENUN888):
327 table_name = NAME.format(alchemy_type=key_alchemy)
329 raise NotImplementedError(u"this alchemy type is not supported")
330 max_no = no + larger_num
331 min_no = no - smaller_num
333 sql = SELECT_NEAR_SEED1S_FROM_NO_SQL.format(
334 table_name=table_name,
335 max_no=max_no, min_no=min_no, table_no=table_no)
336 self._cursor.execute(sql)
339 for row in self._cursor.fetchall():
340 no, seed1, result_num = row[0], row[1], row[2]
341 amulet_ids = [x if x in self._amu_id2name else None for x in row[3:10]]
342 skill_ids = [x if x in self._skill_id2name else None for x in row[10:]]
343 result_dict[no] = (seed1, result_num, amulet_ids, skill_ids)
347 def select_near_names_from_seed1(self, no, table_no, smaller_num, larger_num, key_alchemy):
348 u""" 通し番号とテーブル番および錬金の種類からその周囲のseed1の通し番号、テーブルNo.、
349 お守り個数, お守り名(1-7)、スキル名(1-7)を辞書として返す。
352 return {no: (seed1, result_num, (amulet_names), (skill_names))}"""
353 if (key_alchemy == mh4constnumbers.KEY_TENUN555 or
354 key_alchemy == mh4constnumbers.KEY_TENUN888):
355 table_name = NAME.format(alchemy_type=key_alchemy)
357 raise NotImplementedError(u"this alchemy type is not supported")
358 max_no = no + larger_num
359 min_no = no - smaller_num
361 sql = SELECT_NEAR_SEED1S_FROM_NO_SQL.format(
362 table_name=table_name,
363 max_no=max_no, min_no=min_no, table_no=table_no)
364 self._cursor.execute(sql)
367 for row in self._cursor.fetchall():
368 no, seed1, result_num = row[0], row[1], row[2]
369 amulet_names = [self._amu_id2name[x] if x in self._amu_id2name else u"" for x in row[3:10]]
370 skill_names = [self._skill_id2name[x] if x in self._skill_id2name else u"" for x in row[10:]]
371 result_dict[no] = (seed1, result_num, amulet_names, skill_names)
375 def select_seed1s_from_skill_pos_ids(self, amulet_id, skill1_id, skill_pos, key_alchemy):
376 u""" 指定されたお守りId,スキルIdを指定された枠に持つSeed1を返す """
377 if (key_alchemy == mh4constnumbers.KEY_TENUN555 or
378 key_alchemy == mh4constnumbers.KEY_TENUN888):
379 table_name = NAME.format(alchemy_type=key_alchemy)
381 raise NotImplementedError(u"this alchemy type is not supported")
383 skill_col = COL_SKILL1_LIST[skill_pos]
384 amulet_col = COL_AMULET_LIST[skill_pos]
385 sql = SELECT_SEED1_BY_SKILL1_SQL.format(table_name=table_name,
386 skill1_col=skill_col, skill1_id=skill1_id,
387 amulet_col=amulet_col, amulet_id=amulet_id)
388 self._cursor.execute(sql)
389 return set([x[0] for x in self._cursor.fetchall()])