1 # -*- coding: utf-8 -*-
4 # 2013/12/04 written by kei9
17 class DataBaseGenerator(object):
18 u""" this is generate class of database """
19 def __init__(self, db_name=None):
21 self._db_name = ":memory:"
23 self._db_name = db_name
25 self._dict_skill_id = {}
26 self._dict_amulet_id = {}
28 def generate_db(self, over_write=True):
29 u""" DBを作成する関数。over_writeがTrueで既存のDBがある場合は上書きする """
30 if self._db_name == ":memory:":
33 elif os.path.exists(self._db_name) and os.path.isfile(self._db_name):
34 if over_write is False:
36 with tempfile.NamedTemporaryFile(delete=False) as tmp_file:
37 self._tmp_db = tmp_file.name
41 # read data from zip file
42 self._zipfile = zipfile.ZipFile(db_supports.ZIP_FILE, "r")
45 self._create_skill_table()
46 self._create_amulet_table()
47 self._create_second_skill_table()
48 self._create_min_max_table()
49 self._create_sufficient_value_table()
54 # rename operation of tmp file
55 if self._tmp_db is not None:
56 with tempfile.NamedTemporaryFile(delete=True) as tmp_file:
57 tmp_name = tmp_file.name
58 os.rename(self._db_name, tmp_name)
59 os.rename(self._tmp_db, self._db_name)
63 if self._tmp_db is None:
64 self._connect = sqlite3.connect(self._db_name)
66 self._connect = sqlite3.connect(self._tmp_db)
67 self._connect.text_factory = str # for usage of utf-8
68 self._cursor = self._connect.cursor()
71 self._connect.commit()
75 def _get_skill_id(self, skill_name):
76 u""" 与えられたスキル名からスキルのIDを得る関数。スキルが存在しなければNoneを返す """
77 skill_name = unicode(skill_name, 'utf-8')
79 if skill_name in self._dict_skill_id:
80 return self._dict_skill_id[skill_name]
82 sql = db_supports.SKILL_TABLE_SKILL2ID_SQL.format(skill_name=skill_name)
83 self._cursor.execute(sql)
85 for val in self._cursor.fetchall():
86 skill_ids.append(val[0])
87 if len(skill_ids) < 1:
90 self._dict_skill_id[skill_name] = skill_ids[0]
93 def _get_amulet_id(self, amulet_name):
94 u""" 与えられたお守り名からお守りのIDを得る関数。スキルが存在しなければNoneを返す """
95 amulet_name = unicode(amulet_name, 'utf-8')
97 if amulet_name in self._dict_amulet_id:
98 return self._dict_amulet_id[amulet_name]
100 sql = db_supports.AMULET_TABLE_AMULET2ID_SQL.format(amulet_name=amulet_name)
101 self._cursor.execute(sql)
103 for val in self._cursor.fetchall():
104 amulet_ids.append(val[0])
105 if len(amulet_ids) < 1:
108 self._dict_amulet_id[amulet_name] = amulet_ids[0]
111 def _create_skill_table(self):
112 u"""スキルとIDの組み合わせテーブルを作成する"""
113 print "create skill table"
114 self._cursor.execute(db_supports.SKILL_TABLE_CREATE_SQL)
116 # read from zip file with StringIO wrapper
117 f = cStringIO.StringIO(self._zipfile.read(db_supports.SKILL_FILE_NAME, "r"))
118 reader = csv.reader(f) # (skillName)
120 reader.next() # skip header row
123 self._cursor.execute(db_supports.SKILL_TABLE_INSERT_SQL, (row[0].strip(),))
124 except sqlite3.IntegrityError, e:
125 print "not unique:", row[0].decode("utf-8")
127 self._connect.commit()
130 def _create_amulet_table(self):
131 u"""お守り名とIDの組み合わせテーブルを作成する"""
132 print "create amulet table"
133 self._cursor.execute(db_supports.AMULET_TABLE_CREATE_SQL)
135 # read from zip file with StringIO wrapper
136 f = cStringIO.StringIO(self._zipfile.read(db_supports.AMULET_FILE_NAME, "r"))
137 reader = csv.reader(f) # (amuleteName)
139 reader.next() # skip header row
142 self._cursor.execute(db_supports.AMULET_TABLE_INSERT_SQL, (row[0].strip(),))
143 except sqlite3.IntegrityError, e:
144 print "not unique:", row[0].decode("utf-8")
146 self._connect.commit()
149 def _create_min_max_table(self):
150 u"""お守り名と対応するスキルの最大最小値の記載されたcsvファイルから
151 お守りごとのスキルの最大最小値を記載したテーブルを作成する
153 print "load min & max of skill"
154 # create master table of skill min max
155 self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_CREATE_SQL)
156 # read from zip file with StringIO wrapper
157 f = cStringIO.StringIO(self._zipfile.read(db_supports.MIN_MAX_FILE_NAME, "r"))
158 reader = csv.reader(f) # (name, filename of minmax1, filename of minmax2)
160 reader.next() # skip header row
163 table_name = db_supports.MIN_MAX_TABLE_NAME.format(id=cnt)
164 insert_sql = db_supports.MIN_MAX_MASTER_TABLE_INSERT_SQL
165 amu_id = self._get_amulet_id(row[0].strip())
167 print "amulet name:", row[0].decode("utf-8")
168 self._cursor.execute(insert_sql, (amu_id, table_name))
170 create_sql = db_supports.MIN_MAX_TABLE_CREATE_SQL.format(table_name=table_name)
171 self._cursor.execute(create_sql) # create minmax table for each omamori
173 skill_dict = {} # dict for skill1, skill2
175 # get min max of skill1
176 fname = row[1].strip()
179 if fname in self._zipfile.namelist():
180 # read from zip file with StringIO wrapper
181 file_minmax = cStringIO.StringIO(self._zipfile.read(fname, "r"))
182 reader_minmax = csv.reader(file_minmax) # (name of skill, min1, max1)
184 reader_minmax.next() # skip header
185 for row_minmax in reader_minmax:
186 min_val, max_val = int(row_minmax[1]), int(row_minmax[2])
187 skill_id = self._get_skill_id(row_minmax[0].strip())
189 print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
190 skill_dict[skill_id] = (min_val, max_val, 0, 0)
194 print "file1 ", fname, " doesn't exist!"
196 print "file1 ", fname, " doesn't exist!"
198 # get min max of skill2
199 fname = row[2].strip()
202 if fname in self._zipfile.namelist():
203 # read from zip file with StringIO wrapper
204 file_minmax = cStringIO.StringIO(self._zipfile.read(fname, "r"))
205 reader_minmax = csv.reader(file_minmax) # (name of skill, min2, max)
207 reader_minmax.next() # skip header
208 for row_minmax in reader_minmax:
209 min_val, max_val = int(row_minmax[1]), int(row_minmax[2])
210 skill_id = self._get_skill_id(row_minmax[0].strip())
212 print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
213 elif skill_id in skill_dict:
214 val = skill_dict[skill_id]
215 skill_dict[skill_id] = (val[0], val[1], min_val, max_val)
217 skill_dict[skill_id] = (0, 0, min_val, max_val)
221 print "file2 ", fname, " doesn't exist!"
223 print "file2 ", fname, " doesn't exist!"
225 # insert to minmax table
226 insert_sql = db_supports.MIN_MAX_TABLE_INSERT_SQL.format(table_name=table_name)
227 for k, v in skill_dict.items():
228 self._cursor.execute(insert_sql, (k,) + v)
232 self._connect.commit()
235 def _create_second_skill_table(self):
236 u"""csvファイルから第2スキルの表を生成する
238 print "load Second skill"
239 # create master table of Second skill
240 self._cursor.execute(db_supports.SECOND_MASTER_TABLE_CREATE_SQL)
241 # read from zip file with StringIO wrapper
242 f = cStringIO.StringIO(self._zipfile.read(db_supports.SECOND_SKILL_FILE_NAME, "r"))
243 reader = csv.reader(f) # (omamori_name, filename of Second_skill)
245 reader.next() # skip header row
248 table_name = db_supports.SECOND_TABLE_NAME.format(id=cnt)
249 create_sql = db_supports.SECOND_TABLE_CREATE_SQL.format(table_name=table_name)
250 insert_sql = db_supports.SECOND_MASTER_TABLE_INSERT_SQL
251 amu_id = self._get_amulet_id(row[0].strip())
253 print "amulet name:", row[0].decode("utf-8")
254 self._cursor.execute(insert_sql, (amu_id, table_name))
255 self._cursor.execute(create_sql) # create skill table for each omamori
257 fname_skill = row[1].strip()
258 if fname_skill in self._zipfile.namelist():
259 # read from zip file with StringIO wrapper
260 file_skill = cStringIO.StringIO(self._zipfile.read(fname_skill, "r"))
261 reader_skill = csv.reader(file_skill) # (random seed, skill1, skil2, ..., skill7)
263 reader_skill.next() # skip header
264 insert_sql = db_supports.SECOND_TABLE_INSERT_SQL.format(table_name=table_name)
265 for row1 in reader_skill:
268 for name in row1[1:]:
269 skill_id = self._get_skill_id(name.strip())
271 print "name:", name.decode("utf-8"), ", skill_id", skill_id
273 vals.append(skill_id)
274 self._cursor.execute(insert_sql, tuple(vals))
277 print "file1 ", fname_skill, " doesn't exist!"
283 self._cursor.execute(db_supports.SECOND_SLOT_TABLE_CREATE_SQL) # create slot table of Second skill
284 # read from zip file with StringIO wrapper
285 f = cStringIO.StringIO(self._zipfile.read(db_supports.SECOND_SLOT_FILE_NAME, "r"))
286 reader = csv.reader(f) # (random_seed, slot1, slot2, ..., slot7)
288 reader.next() # skip header row
289 insert_sql = db_supports.SECOND_SLOT_TABLE_INSERT_SQL
291 val_tup = tuple([int(x.strip()) for x in row])
292 self._cursor.execute(insert_sql, val_tup)
295 self._connect.commit()
297 def _create_sufficient_value_table(self):
298 u"""csvファイルから充足値の表を生成する
300 print "load Sufficient Value"
301 # create master table of sufficient value
302 self._cursor.execute(db_supports.SUFFICIENT_MASTER_TABLE_CREATE_SQL)
303 # read from zip file with StringIO wrapper
304 f = cStringIO.StringIO(self._zipfile.read(db_supports.SUFFICIENT_FILE_NAME, "r"))
305 reader = csv.reader(f) # (omamori_name, filename of sufficient values)
307 reader.next() # skip header row
310 table_name = db_supports.SUFFICIENT_TABLE_NAME.format(id=cnt)
311 create_sql = db_supports.SUFFICIENT_TABLE_CREATE_SQL.format(table_name=table_name)
312 insert_sql = db_supports.SUFFICIENT_MASTER_TABLE_INSERT_SQL
313 amu_id = self._get_amulet_id(row[0].strip())
315 print "amulet name:", row[0].decode("utf-8")
316 self._cursor.execute(insert_sql, (amu_id, table_name))
317 self._cursor.execute(create_sql) # create skill table for each omamori
319 fname_threshold = row[1].strip()
320 if fname_threshold in self._zipfile.namelist():
321 # read from zip file with StringIO wrapper
322 file_threshold = cStringIO.StringIO(self._zipfile.read(fname_threshold, "r"))
323 reader_threshold = csv.reader(file_threshold) # (sufficient value, slot1 threshold, ..., slot3 threshold)
325 reader_threshold.next() # skip header
326 insert_sql = db_supports.SUFFICIENT_TABLE_INSERT_SQL.format(table_name=table_name)
327 for row_vals in reader_threshold:
328 val_tup = tuple([int(x.strip()) for x in row_vals])
329 self._cursor.execute(insert_sql, val_tup)
330 file_threshold.close()
332 print "file1 ", fname_threshold, " doesn't exist!"
336 self._connect.commit()
338 if __name__ == "__main__":
339 #db = DataBaseGenerator(DB_FILE_NAME)
340 db = DataBaseGenerator("test.sqlite3")
341 #db = DataBaseGenerator() # for memory