1 # -*- coding: utf-8 -*-
4 お守りID、スキルIDから取りうるスキルの最大値への組み合わせテーブルの作成・アクセスをするモジュール
5 2013/12/19 written by kei9
16 MASTER_NAME = u"skill_minmax_master"
17 NAME = u"skill_minmax_{amulet_id}"
18 COL_AMULET_ID = u"amulet_id"
19 COL_SKILL_TABLE_NAME = u"skill_table_name"
24 COL_SKILL_ID = u"skill_id"
25 CREATE_MASTER_SQL = u"""create table if not exists {master}
26 (id integer primary key, {amulet_id} integer, {table_name} varchar,
27 foreign key(amulet_id) references {amulet_table}(id));""".format(
29 amulet_id=COL_AMULET_ID,
30 amulet_table=amulettable.NAME,
31 table_name=COL_SKILL_TABLE_NAME)
32 CREATE_SQL = u"""create table if not exists {{table_name}}
33 (id integer primary key, {skill_id} integer,
34 {min1} integer, {max1} integer, {min2} integer, {max2} integer,
35 foreign key(skill_id) references {skill_table}({skill_id_col}));""".format(
36 skill_id=COL_SKILL_ID,
37 min1=COL_MIN1, min2=COL_MIN2,
38 max1=COL_MAX1, max2=COL_MAX2,
39 skill_table=skilltable.NAME,
40 skill_id_col=skilltable.COL_SKILL_ID)
41 INSERT_MASTER_SQL = u"""insert into {master}
42 ({amulet_id}, {table_col}) values(?,?);""".format(
44 table_col=COL_SKILL_TABLE_NAME,
45 amulet_id=COL_AMULET_ID)
46 INSERT_SQL = u"""insert into {{table_name}}
47 ({skill_id}, {min1}, {max1}, {min2}, {max2}) values(?,?,?,?,?)""".format(
48 skill_id=COL_SKILL_ID,
49 min1=COL_MIN1, min2=COL_MIN2,
50 max1=COL_MAX1, max2=COL_MAX2)
51 SELECT_MASTER_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
53 table_col=COL_SKILL_TABLE_NAME,
55 SELECT_ALL_SQL = u"""select {skill_id}, {min1}, {max1}, {min2}, {max2} from {{table_name}} """.format(
56 skill_id=COL_SKILL_ID,
57 min1=COL_MIN1, max1=COL_MAX1,
58 min2=COL_MIN2, max2=COL_MAX2)
59 SELECT_NON_ZERO_SKILL1_ID_SQL = u"""select {skill_id} from {{table_name}}
60 where {min1} != 0 and {max1} != 0""".format(
61 skill_id=COL_SKILL_ID,
62 min1=COL_MIN1, max1=COL_MAX1)
63 SELECT_NON_ZERO_SKILL2_ID_SQL = u"""select {skill_id} from {{table_name}}
64 where {min2} != 0 and {max2} != 0""".format(
65 skill_id=COL_SKILL_ID,
66 min2=COL_MIN2, max2=COL_MAX2)
67 SELECT_MIN1_SQL = u"""select min({col}) from {{table_name}} """.format(
69 SELECT_MAX1_SQL = u"""select max({col}) from {{table_name}} """.format(
71 SELECT_MIN2_SQL = u"""select min({col}) from {{table_name}} """.format(
73 SELECT_MAX2_SQL = u"""select max({col}) from {{table_name}} """.format(
75 SELECT_MAX1_OF_SKILL_SQL = u"""select max({col}) from {{table_name}}
76 where skill_id={{skill_id}}""".format(
78 SELECT_MAX2_OF_SKILL_SQL = u"""select max({col}) from {{table_name}}
79 where skill_id={{skill_id}}""".format(
82 class SkillMinMaxTableGenerator(object):
83 u"""お守りID、スキルIDから取りうるスキルの最大値への組み合わせテーブルの作成クラス"""
86 self._amulet_id2skill1_filename_dict = {}
87 self._amulet_id2skill2_filename_dict = {}
89 def insert_master_data(self, db_cursor, csv_reader):
92 db_cursor.execute(CREATE_MASTER_SQL)
93 accessor = amulettable.AmuletTableAccessor(db_cursor)
94 amu_id2name, amu_name2id = accessor.get_dict()
96 csv_reader.next() # skip header row
97 for row in csv_reader:
98 amulet_name = row[0].strip()
99 #if not isinstance(amulet_name, unicode):
100 # amulet_name = unicode(amulet_name, u"utf-8")
101 skill1_file, skill2_file = row[1].strip(), row[2].strip()
102 amulet_id = amu_name2id[amulet_name]
103 table_name = NAME.format(amulet_id=amulet_id)
104 self._amulet_id2skill1_filename_dict[amulet_id] = skill1_file
105 self._amulet_id2skill2_filename_dict[amulet_id] = skill2_file
106 db_cursor.execute(INSERT_MASTER_SQL, (amulet_id, table_name))
108 def get_skill_filenames(self):
109 u""" スキル1, スキル2の最大最小が記載されたファイル名をお守りIDに関連付けて返す
110 return {amulet_id:(skill1_filename, skill2_filename)}"""
112 for amu_id in self._amulet_id2skill1_filename_dict.keys():
113 result_dict[amu_id] = (self._amulet_id2skill1_filename_dict[amu_id],
114 self._amulet_id2skill2_filename_dict[amu_id])
117 def insert_data(self, db_cursor, amulet_id2csv_readers_dict):
118 u""" お守りIDと関連付けられたcsv_reader(skill1,skill2)からデータを読み込み、
120 ammulet_id2csv_readers_dict:{amulet_id:(csv_reader_skill1, csv_reader_skill2)}"""
121 accessor = skilltable.SkillTableAccessor(db_cursor)
122 skill_id2name, skill_name2id = accessor.get_dict()
124 for amulet_id in amulet_id2csv_readers_dict.keys():
125 table_name = NAME.format(amulet_id=amulet_id)
126 db_cursor.execute(CREATE_SQL.format(table_name=table_name))
127 skill1_reader, skill2_reader = amulet_id2csv_readers_dict[amulet_id]
130 skill1_reader.next() # skip header row
131 for row in skill1_reader: # (skill_name, min1, max1)
132 skill_name = row[0].strip()
133 #if not isinstance(skill_name, unicode):
134 # skill_name = unicode(skill_name, u"utf-8")
135 min1, max1 = int(row[1].strip()), int(row[2].strip())
136 skill_id = skill_name2id[skill_name]
137 insert_values[skill_id] = (skill_id, min1, max1, 0, 0)
139 if skill2_reader is not None:
140 skill2_reader.next() # skip header row
141 for row in skill2_reader: # (skill_name, min2, max2)
142 skill_name = row[0].strip()
143 #if not isinstance(skill_name, unicode):
144 # skill_name = unicode(skill_name, u"utf-8")
145 min2, max2 = int(row[1].strip()), int(row[2].strip())
146 skill_id = skill_name2id[skill_name]
147 if skill_id in insert_values:
148 val = insert_values[skill_id]
149 insert_values[skill_id] = (skill_id, val[1], val[2], min2, max2)
151 insert_values[skill_id] = (skill_id, 0, 0, min2, max2)
152 sql = INSERT_SQL.format(table_name=table_name)
153 for skill_id, values in insert_values.items():
154 db_cursor.execute(sql, values)
156 class SkillMinMaxTableAccessor(object):
157 u""" お守りID、スキルIDから取りうるスキルの最大値への組み合わせテーブルのアクセスクラス"""
158 #__metaclass__ = singleton.Singleton
159 def __init__(self, db_cursor):
160 u""" db_cursor: cursor of sqlite3 database """
161 self._cursor = db_cursor
162 accessor = amulettable.AmuletTableAccessor(db_cursor)
163 self._amu_id2name, self._amu_name2id = accessor.get_dict()
165 accessor = skilltable.SkillTableAccessor(db_cursor)
166 self._skill_id2name, self._skill_name2id = accessor.get_dict()
167 self._amu_id2table_name = {}
169 self._cursor.execute(SELECT_MASTER_ALL_SQL)
170 for row in self._cursor.fetchall():
171 amu_id, table_name = row
172 self._amu_id2table_name[amu_id] = table_name
174 def get_minmax_by_amulet(self, amulet_id):
175 u""" 指定されたお守りIDにおけるスキルIDと最大値、最小値の組み合わせを返す
176 return {skill1_id:(min1,max1)}, {skill2_id;(min2,max2)}"""
177 skill1_dict, skill2_dict = {}, {}
178 if amulet_id in self._amu_id2table_name:
179 table_name = self._amu_id2table_name[amulet_id]
180 #get non zero skill1_ids
181 sql = SELECT_NON_ZERO_SKILL1_ID_SQL.format(table_name=table_name)
182 self._cursor.execute(sql)
183 skill1_ids = [row[0] for row in self._cursor.fetchall()]
185 #get non zero skill2_ids
186 sql = SELECT_NON_ZERO_SKILL2_ID_SQL.format(table_name=table_name)
187 self._cursor.execute(sql)
188 skill2_ids = [row[0] for row in self._cursor.fetchall()]
190 self._cursor.execute(SELECT_ALL_SQL.format(table_name=table_name))
191 for (skill_id, min1, max1, min2, max2) in self._cursor.fetchall():
192 if skill_id in skill1_ids:
193 skill1_dict[skill_id] = (min1, max1)
194 if skill_id in skill2_ids:
195 skill2_dict[skill_id] = (min2, max2)
197 return (skill1_dict, skill2_dict)
199 def get_minmax_by_id(self):
200 u""" お守りIDごとのスキルIDと最大値、最小値の組み合わせを辞書として返す
201 return {amulet_id:({skill1_id:(min1,max1)}, {skill2_id;(min2,max2)})}"""
203 for amu_id in self._amu_id2name.keys():
204 result_dict[amu_id] = self.get_minmax_by_amulet(amu_id)
207 def get_minmax_by_name(self):
208 u""" お守り名ごとのスキルIDと最大値、最小値の組み合わせを辞書として返す
209 return {amulet_name:({skill1_name:(min1,max1)}, {skill2_name;(min2,max2)})}"""
211 for amu_id in self._amu_id2name.keys():
212 id_dict1, id_dict2 = self.get_minmax_by_amulet(amu_id)
213 name_dict1, name_dict2 = {}, {}
214 for skill_id, vals in id_dict1.items():
215 name_dict1[self._skill_id2name[skill_id]] = vals
216 for skill_id, vals in id_dict2.items():
217 name_dict2[self._skill_id2name[skill_id]] = vals
218 result_dict[self._amu_id2name[amu_id]] = (name_dict1, name_dict2)