OSDN Git Service

Merge remote-tracking branch 'origin/develop' into develop
[amulettoolsmh4/main.git] / model / db_generator.py
1 # -*- coding: utf-8 -*-
2
3 # databaseの生成用スクリプト
4 # 2013/12/04 written by kei9 
5
6 import sqlite3
7 import csv
8 import os.path
9 import os
10 import sys
11 import tempfile
12 import zipfile
13 import cStringIO
14
15 import db_supports
16
17 class DataBaseGenerator(object):
18     u""" this is generate class of database """
19     def __init__(self, db_name=None):
20         if dbName is None:
21             self._db_name = ":memory:"
22         else :
23             self._db_name = db_name
24         self._tmp_db = None
25         self._dict_skill_id = {}
26         self._dict_amulet_id = {}
27
28     def generate_db(self, over_write=True):
29         u""" DBを作成する関数。over_writeがTrueで既存のDBがある場合は上書きする """
30         if self._db_name == ":memory:":
31             # nothing to do
32             pass
33         elif os.path.exists(self._db_name) and os.path.isfile(self._db_name):
34             if over_write is False:
35                 return
36             with tempfile.NamedTemporaryFile(delete=False) as tmp_file:
37                 self._tmp_db = tmp_file.name
38
39         self._open()
40
41         # read data from zip file
42         self._zipfile = zipfile.ZipFile(db_supports.ZIP_FILE, "r")
43
44         # db generation
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()
50
51         self._zipfile.close()
52         self._close()
53
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)
60             os.remove(tmp_name)
61
62     def _open(self):
63         if self._tmp_db is None:
64             self._connect = sqlite3.connect(self._db_name)
65         else:
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()
69
70     def _close(self):
71         self._connect.commit()
72         self._cursor.close()
73         self._connect.close()
74
75     def _get_skill_id(self, skill_name):
76         u""" 与えられたスキル名からスキルのIDを得る関数。スキルが存在しなければNoneを返す """
77         skill_name = unicode(skill_name, 'utf-8')
78
79         if skill_name in self._dict_skill_id:
80             return self._dict_skill_id[skill_name]
81         else:
82             sql = db_supports.SKILL_TABLE_SKILL2ID_SQL.format(skill_name=skill_name)
83             self._cursor.execute(sql)
84             skill_ids = []
85             for val in self._cursor.fetchall():
86                 skill_ids.append(val[0])
87             if len(skill_ids) < 1:
88                 return None
89             else:
90                 self._dict_skill_id[skill_name] = skill_ids[0]
91                 return skill_ids[0]
92
93     def _get_amulet_id(self, amulet_name):
94         u""" 与えられたお守り名からお守りのIDを得る関数。スキルが存在しなければNoneを返す """
95         amulet_name = unicode(amulet_name, 'utf-8')
96
97         if amulet_name in self._dict_amulet_id:
98             return self._dict_amulet_id[amulet_name]
99         else:
100             sql = db_supports.AMULET_TABLE_AMULET2ID_SQL.format(amulet_name=amulet_name)
101             self._cursor.execute(sql)
102             amulet_ids = []
103             for val in self._cursor.fetchall():
104                 amulet_ids.append(val[0])
105             if len(amulet_ids) < 1:
106                 return None
107             else:
108                 self._dict_amulet_id[amulet_name] = amulet_ids[0]
109                 return amulet_ids[0]
110
111     def _create_skill_table(self):
112         u"""スキルとIDの組み合わせテーブルを作成する"""
113         print "create skill table"
114         self._cursor.execute(db_supports.SKILL_TABLE_CREATE_SQL)
115
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)
119
120         reader.next()   # skip header row
121         for row in reader:
122             try:
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")
126                 raise e
127         self._connect.commit()
128         f.close()
129
130     def _create_amulet_table(self):
131         u"""お守り名とIDの組み合わせテーブルを作成する"""
132         print "create amulet table"
133         self._cursor.execute(db_supports.AMULET_TABLE_CREATE_SQL)
134
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)
138
139         reader.next()   # skip header row
140         for row in reader:
141             try:
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")
145                 raise e
146         self._connect.commit()
147         f.close()
148
149     def _create_min_max_table(self):
150         u"""お守り名と対応するスキルの最大最小値の記載されたcsvファイルから
151         お守りごとのスキルの最大最小値を記載したテーブルを作成する
152         """
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)
159
160         reader.next()   # skip header row
161         cnt = 0
162         for row in reader:
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())
166             if amu_id is None:
167                 print "amulet name:", row[0].decode("utf-8")
168             self._cursor.execute(insert_sql, (amu_id, table_name))
169
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
172
173             skill_dict = {} # dict for skill1, skill2
174
175             # get min max of skill1
176             fname = row[1].strip()
177             if fname:
178                 # not empty
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)
183
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())
188                         if skill_id is None:
189                             print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
190                         skill_dict[skill_id] = (min_val, max_val, 0, 0)
191
192                     file_minmax.close()
193                 else:
194                     print "file1 ", fname, " doesn't exist!"
195             else:
196                 print "file1 ", fname, " doesn't exist!"
197
198             # get min max of skill2
199             fname = row[2].strip()
200             if fname:
201                 # not empty
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)
206
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())
211                         if skill_id is None:
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)
216                         else:
217                             skill_dict[skill_id] = (0, 0, min_val, max_val)
218
219                     file_minmax.close()
220                 else:
221                     print "file2 ", fname, " doesn't exist!"
222             else:
223                 print "file2 ", fname, " doesn't exist!"
224
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)
229
230             cnt += 1
231
232         self._connect.commit()
233         f.close()
234
235     def _create_second_skill_table(self):
236         u"""csvファイルから第2スキルの表を生成する
237         """
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)
244
245         reader.next()   # skip header row
246         cnt = 0
247         for row in reader:
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())
252             if amu_id is None:
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
256
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)
262
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:
266                     seed = row1[0]
267                     vals = [seed]
268                     for name in row1[1:]:
269                         skill_id = self._get_skill_id(name.strip())
270                         if skill_id is None:
271                             print "name:", name.decode("utf-8"), ", skill_id", skill_id
272                         else:
273                             vals.append(skill_id)
274                     self._cursor.execute(insert_sql, tuple(vals))
275                 file_skill.close()
276             else:
277                 print "file1 ", fname_skill, " doesn't exist!"
278
279             cnt += 1
280         f.close()
281
282         # for Slot table
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)
287
288         reader.next()   # skip header row
289         insert_sql = db_supports.SECOND_SLOT_TABLE_INSERT_SQL
290         for row in reader:
291             val_tup = tuple([int(x.strip()) for x in row])
292             self._cursor.execute(insert_sql, val_tup)
293
294         f.close()
295         self._connect.commit()
296
297     def _create_sufficient_value_table(self):
298         u"""csvファイルから充足値の表を生成する
299         """
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)
306
307         reader.next()   # skip header row
308         cnt = 0
309         for row in reader:
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())
314             if amu_id is None:
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
318
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)
324
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()
331             else:
332                 print "file1 ", fname_threshold, " doesn't exist!"
333
334             cnt += 1
335         f.close()
336         self._connect.commit()
337
338 if __name__ == "__main__":
339     #db = DataBaseGenerator(DB_FILE_NAME)
340     db = DataBaseGenerator("test.sqlite3")
341     #db = DataBaseGenerator()    # for memory
342     db.generate_db()
343