1 # -*- coding: utf-8 -*-
4 SEED2とスキル名の組み合わせテーブルの作成・アクセスをするモジュール
5 2013/12/19 written by kei9
15 u""" SEED2から各お守りの第2スキルへのテーブル """
16 MASTER_NAME = u"seed2_skill2_master"
17 NAME = u"seed2_skill2_{amulet_id}"
18 COL_AMULET_ID = u"amulet_id"
19 COL_SEED2_SKILL2_TABLE_NAME = u"seed2_skill2_table_name"
21 COL_SKILL2_ID1 = u"skill2_id1"
22 COL_SKILL2_ID2 = u"skill2_id2"
23 COL_SKILL2_ID3 = u"skill2_id3"
24 COL_SKILL2_ID4 = u"skill2_id4"
25 COL_SKILL2_ID5 = u"skill2_id5"
26 COL_SKILL2_ID6 = u"skill2_id6"
27 COL_SKILL2_ID7 = u"skill2_id7"
28 COL_SKILL2_LIST = [COL_SKILL2_ID1, COL_SKILL2_ID2, COL_SKILL2_ID3, COL_SKILL2_ID4, COL_SKILL2_ID5, COL_SKILL2_ID6, COL_SKILL2_ID7]
29 CREATE_MASTER_SQL = u"""create table if not exists {master}
30 (id integer primary key, {amulet_id} integer, {table_name} varchar,
31 foreign key(amulet_id) references {amulet_table}(id));""".format(
33 amulet_id=COL_AMULET_ID,
34 amulet_table=amulettable.NAME,
35 table_name=COL_SEED2_SKILL2_TABLE_NAME)
36 CREATE_SQL = u"""create table if not exists {{table_name}}
37 (id integer primary key, {seed} integer unique,
38 {skill_id1} integer, {skill_id2} integer, {skill_id3} integer,
39 {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer,
40 foreign key({skill_id1}) references {skill_table}(id),
41 foreign key({skill_id2}) references {skill_table}(id),
42 foreign key({skill_id3}) references {skill_table}(id),
43 foreign key({skill_id4}) references {skill_table}(id),
44 foreign key({skill_id5}) references {skill_table}(id),
45 foreign key({skill_id6}) references {skill_table}(id),
46 foreign key({skill_id7}) references {skill_table}(id));""".format(
48 skill_id1=COL_SKILL2_ID1,
49 skill_id2=COL_SKILL2_ID2,
50 skill_id3=COL_SKILL2_ID3,
51 skill_id4=COL_SKILL2_ID4,
52 skill_id5=COL_SKILL2_ID5,
53 skill_id6=COL_SKILL2_ID6,
54 skill_id7=COL_SKILL2_ID7,
55 skill_table=skilltable.NAME)
56 INSERT_MASTER_SQL = u"""insert into {table}
57 ({amulet_col}, {table_col}) values(?,?);""".format(
59 table_col=COL_SEED2_SKILL2_TABLE_NAME,
60 amulet_col=COL_AMULET_ID)
61 INSERT_SQL = u"""insert into {{table_name}}
62 ({seed}, {skill_id1}, {skill_id2}, {skill_id3},
63 {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) values(?,?,?,?,?,?,?,?)""".format(
65 skill_id1=COL_SKILL2_ID1,
66 skill_id2=COL_SKILL2_ID2,
67 skill_id3=COL_SKILL2_ID3,
68 skill_id4=COL_SKILL2_ID4,
69 skill_id5=COL_SKILL2_ID5,
70 skill_id6=COL_SKILL2_ID6,
71 skill_id7=COL_SKILL2_ID7)
72 SELECT_MASTER_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
74 table_col=COL_SEED2_SKILL2_TABLE_NAME,
76 SELECT_SEED2_SQL = u"""select {seed} from {{table_name}} """.format(
78 SELECT_ALL_SQL = u"""select {seed}, {skill_id1}, {skill_id2},
79 {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} """.format(
81 skill_id1=COL_SKILL2_ID1,
82 skill_id2=COL_SKILL2_ID2,
83 skill_id3=COL_SKILL2_ID3,
84 skill_id4=COL_SKILL2_ID4,
85 skill_id5=COL_SKILL2_ID5,
86 skill_id6=COL_SKILL2_ID6,
87 skill_id7=COL_SKILL2_ID7)
88 SELECT_ALL_FROM_SEED2_SQL = u"""select {skill_id1}, {skill_id2},
89 {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} where {seed}={{seed2}}""".format(
91 skill_id1=COL_SKILL2_ID1,
92 skill_id2=COL_SKILL2_ID2,
93 skill_id3=COL_SKILL2_ID3,
94 skill_id4=COL_SKILL2_ID4,
95 skill_id5=COL_SKILL2_ID5,
96 skill_id6=COL_SKILL2_ID6,
97 skill_id7=COL_SKILL2_ID7)
99 class Seed2Skill2TableGenerator(object):
100 u""" Seed2とスキル名の組み合わせテーブルを作成するクラス """
103 self._amulet_id2skill2_filename_dict = {}
105 def insert_master_data(self, db_cursor, csv_reader):
108 db_cursor.execute(CREATE_MASTER_SQL)
109 accessor = amulettable.AmuletTableAccessor(db_cursor)
110 amu_id2name, amu_name2id = accessor.get_dict()
112 csv_reader.next() # skip header row
113 for row in csv_reader:
114 amulet_name = row[0].strip()
115 skill2_file = row[1].strip()
116 amulet_id = amu_name2id[amulet_name]
117 table_name = NAME.format(amulet_id=amulet_id)
118 self._amulet_id2skill2_filename_dict[amulet_id] = skill2_file
119 db_cursor.execute(INSERT_MASTER_SQL, (amulet_id, table_name))
121 def get_skill_filenames(self):
122 u""" 出現するスキル2が記載されたファイル名をお守りIDに関連付けて返す
123 return {amulet_id: skill2_filename}"""
125 for amu_id in self._amulet_id2skill2_filename_dict.keys():
126 result_dict[amu_id] = self._amulet_id2skill2_filename_dict[amu_id]
129 def insert_data(self, db_cursor, amulet_id2csv_reader_dict):
130 u""" お守りIDと関連付けられたcsv_reader(skill2)からデータを読み込み、
132 ammulet_id2csv_readers_dict:{amulet_id:csv_reader_skill2}"""
133 accessor = skilltable.SkillTableAccessor(db_cursor)
134 skill_id2name, skill_name2id = accessor.get_dict()
136 for amulet_id, reader in amulet_id2csv_reader_dict.items():
137 table_name = NAME.format(amulet_id=amulet_id)
138 db_cursor.execute(CREATE_SQL.format(table_name=table_name))
141 reader.next() # skip header row
142 for row in reader: # (skill_name, min1, max1)
143 seed2 = int(row[0].strip())
144 skill_ids = [skill_name2id[x.strip()] for x in row[1:]]
146 sql = INSERT_SQL.format(table_name=table_name)
147 db_cursor.execute(sql, tuple([seed2] + skill_ids))
149 class Seed2Skill2TableAccessor(object):
150 u""" Seed2とスキル名の組み合わせテーブルへのアクセス用クラス """
151 def __init__(self, db_cursor):
152 u""" db_cursor: cursor of sqlite3 database """
153 self._cursor = db_cursor
154 amu_accessor = amulettable.AmuletTableAccessor(db_cursor)
155 skill_accessor = skilltable.SkillTableAccessor(db_cursor)
156 self._amu_id2name, self._amu_name2id = amu_accessor.get_dict()
157 self._skill_id2name, self._skill_name2id = skill_accessor.get_dict()
158 self._amu_id2table_name = {}
160 self._cursor.execute(SELECT_MASTER_ALL_SQL)
161 for row in self._cursor.fetchall():
162 amu_id, table_name = row
163 self._amu_id2table_name[amu_id] = table_name
165 def select_seed2s_by_ids(self, amu_id2skill_ids):
166 u""" お守りIdとスキルIdのリストの辞書からSeed2を特定する
167 skill_ids: (skill_id1, skill_id2, ...., skill_id7)
168 不明であればNoneあるいはNO_DATAを入れておくものとする。
171 for amulet_id, skill_ids in amu_id2skill_ids.items():
172 table_name = self._amu_id2table_name[amulet_id]
173 where_list = ["{0}={1}".format(col, skill_id)
174 for col, skill_id in zip(COL_SKILL2_LIST, skill_ids)
175 if skill_id in self._skill_id2name]
176 if len(where_list) == 0:
179 where_sql = " where " + " and ".join(where_list)
180 sql = SELECT_SEED2_SQL.format(table_name=table_name) + where_sql
181 self._cursor.execute(sql)
182 if len(result_set) == 0:
183 result_set = set([x[0] for x in self._cursor.fetchall()])
185 result_set = result_set & set([x[0] for x in self._cursor.fetchall()])
189 def select_seed2s_by_name(self, amu_name2skill_names):
190 u""" お守り名とスキル名のリストの辞書からSeed2を特定する
191 skill_names: (skill_name1, skill_name2, ...., skill_name7)
192 不明であればNoneあるいはNO_DATAを入れておくものとする。
194 amu_id2skill_ids = {}
195 for amu_name, skill_names in amu_name2skill_names.items():
196 if amu_name in self._amu_name2id:
197 amu_id = self._amu_name2id[amu_name]
199 print u"{0} is not found!".format(amu_name)
201 skill_ids = [self._skill_name2id[name] if name in self._skill_name2id else None
202 for name in skill_names]
203 amu_id2skill_ids[amu_id] = skill_ids
205 if len(amu_id2skill_ids) > 0:
206 return self.select_seed2s_by_ids(amu_id2skill_ids)
210 def select_skill_ids_by_seed2(self, seed2):
211 u""" seed2から対応するお守りIdごとの第2スキルId(1-7枠)を返す
212 return {amulet_id:(skill2_id1, skill2_id2, ..., skill2_id7)}"""
214 for amu_id, table_name in self._amu_id2table_name.items():
215 sql = SELECT_ALL_FROM_SEED2_SQL.format(table_name=table_name, seed2=seed2)
216 self._cursor.execute(sql)
217 id1, id2, id3, id4, id5, id6, id7 = self._cursor.fetchone()
218 result_dict[amu_id] = (id1, id2, id3, id4, id5, id6, id7)
221 def select_skill_names_by_seed2(self, seed2):
222 u""" seed2から対応するお守り名ごとの第2スキル名(1-7枠)を返す
223 return {amulet_name:(skill2_name1, skill2_name2, ..., skill2_name7)}"""
225 for amu_id, table_name in self._amu_id2table_name.items():
226 sql = SELECT_ALL_FROM_SEED2_SQL.format(table_name=table_name, seed2=seed2)
227 self._cursor.execute(sql)
228 names = [self._skill_id2name[x] for x in self._cursor.fetchone()]
229 result_dict[self._amu_id2name[amu_id]] = tuple(names)