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