OSDN Git Service

rename database_ids & some variables in order to understand easily
[amulettoolsmh4/main.git] / model / db_generator.py
1 # -*- coding: utf-8 -*-
2
3 # databaseの生成用スクリプト
4 # 2013/12/09 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 db_name 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_seed2_skill2_table()
48         self._create_seed2_threshold1_table()
49         self._create_min_max_table()
50         self._create_sufficient_value_table()
51
52         self._zipfile.close()
53         self._close()
54
55         # rename operation of tmp file
56         if self._tmp_db is not None:
57             with tempfile.NamedTemporaryFile(delete=True) as tmp_file:
58                 tmp_name = tmp_file.name
59             os.rename(self._db_name, tmp_name)
60             os.rename(self._tmp_db, self._db_name)
61             os.remove(tmp_name)
62
63     def _open(self):
64         if self._tmp_db is None:
65             self._connect = sqlite3.connect(self._db_name)
66         else:
67             self._connect = sqlite3.connect(self._tmp_db)
68         self._connect.text_factory = str   # for usage of utf-8
69         self._cursor = self._connect.cursor()
70
71     def _close(self):
72         self._connect.commit()
73         self._cursor.close()
74         self._connect.close()
75
76     def _get_skill_id(self, skill_name):
77         u""" 与えられたスキル名からスキルのIDを得る関数。スキルが存在しなければNoneを返す """
78         skill_name = unicode(skill_name, 'utf-8')
79
80         if skill_name in self._dict_skill_id:
81             return self._dict_skill_id[skill_name]
82         else:
83             sql = db_supports.SKILL_TABLE_SKILL2ID_SQL.format(skill_name=skill_name)
84             self._cursor.execute(sql)
85             skill_ids = []
86             for val in self._cursor.fetchall():
87                 skill_ids.append(val[0])
88             if len(skill_ids) < 1:
89                 return None
90             else:
91                 self._dict_skill_id[skill_name] = skill_ids[0]
92                 return skill_ids[0]
93
94     def _get_amulet_id(self, amulet_name):
95         u""" 与えられたお守り名からお守りのIDを得る関数。スキルが存在しなければNoneを返す """
96         amulet_name = unicode(amulet_name, 'utf-8')
97
98         if amulet_name in self._dict_amulet_id:
99             return self._dict_amulet_id[amulet_name]
100         else:
101             sql = db_supports.AMULET_TABLE_AMULET2ID_SQL.format(amulet_name=amulet_name)
102             self._cursor.execute(sql)
103             amulet_ids = []
104             for val in self._cursor.fetchall():
105                 amulet_ids.append(val[0])
106             if len(amulet_ids) < 1:
107                 return None
108             else:
109                 self._dict_amulet_id[amulet_name] = amulet_ids[0]
110                 return amulet_ids[0]
111
112     def _create_skill_table(self):
113         u"""スキルとIDの組み合わせテーブルを作成する"""
114         print "create skill table"
115         self._cursor.execute(db_supports.SKILL_TABLE_CREATE_SQL)
116
117         # read from zip file with StringIO wrapper
118         f = cStringIO.StringIO(self._zipfile.read(db_supports.SKILL_FILE_NAME, "r"))
119         reader = csv.reader(f)  # (skillName)
120
121         reader.next()   # skip header row
122         for row in reader:
123             try:
124                 self._cursor.execute(db_supports.SKILL_TABLE_INSERT_SQL, (row[0].strip(),))
125             except sqlite3.IntegrityError, e:
126                 print "not unique:", row[0].decode("utf-8")
127                 raise e
128         self._connect.commit()
129         f.close()
130
131     def _create_amulet_table(self):
132         u"""お守り名とIDの組み合わせテーブルを作成する"""
133         print "create amulet table"
134         self._cursor.execute(db_supports.AMULET_TABLE_CREATE_SQL)
135
136         # read from zip file with StringIO wrapper
137         f = cStringIO.StringIO(self._zipfile.read(db_supports.AMULET_FILE_NAME, "r"))
138         reader = csv.reader(f)  # (amuleteName)
139
140         reader.next()   # skip header row
141         for row in reader:
142             try:
143                 self._cursor.execute(db_supports.AMULET_TABLE_INSERT_SQL, (row[0].strip(),))
144             except sqlite3.IntegrityError, e:
145                 print "not unique:", row[0].decode("utf-8")
146                 raise e
147         self._connect.commit()
148         f.close()
149
150     def _create_min_max_table(self):
151         u"""お守り名と対応するスキルの最大最小値の記載されたcsvファイルから
152         お守りごとのスキルの最大最小値を記載したテーブルを作成する
153         """
154         print "load min & max of skill" 
155         # create master table of skill min max
156         self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_CREATE_SQL)
157         # read from zip file with StringIO wrapper
158         f = cStringIO.StringIO(self._zipfile.read(db_supports.MIN_MAX_FILE_NAME, "r"))
159         reader = csv.reader(f)  # (name, filename of minmax1, filename of minmax2)
160
161         reader.next()   # skip header row
162         cnt = 0
163         for row in reader:
164             table_name = db_supports.MIN_MAX_TABLE_NAME.format(id=cnt)
165             insert_sql = db_supports.MIN_MAX_MASTER_TABLE_INSERT_SQL
166             amu_id = self._get_amulet_id(row[0].strip())
167             if amu_id is None:
168                 print "amulet name:", row[0].decode("utf-8")
169             self._cursor.execute(insert_sql, (amu_id, table_name))
170
171             create_sql = db_supports.MIN_MAX_TABLE_CREATE_SQL.format(table_name=table_name)
172             self._cursor.execute(create_sql) # create minmax table for each omamori
173
174             skill_dict = {} # dict for skill1, skill2
175
176             # get min max of skill1
177             fname = row[1].strip()
178             if fname:
179                 # not empty
180                 if fname in self._zipfile.namelist():
181                     # read from zip file with StringIO wrapper
182                     file_minmax = cStringIO.StringIO(self._zipfile.read(fname, "r"))
183                     reader_minmax = csv.reader(file_minmax)  # (name of skill, min1, max1)
184
185                     reader_minmax.next()  # skip header
186                     for row_minmax in reader_minmax:
187                         min_val, max_val = int(row_minmax[1]), int(row_minmax[2])
188                         skill_id = self._get_skill_id(row_minmax[0].strip())
189                         if skill_id is None:
190                             print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
191                         skill_dict[skill_id] = (min_val, max_val, 0, 0)
192
193                     file_minmax.close()
194                 else:
195                     print "file1 ", fname, " doesn't exist!"
196             else:
197                 print "file1 ", fname, " doesn't exist!"
198
199             # get min max of skill2
200             fname = row[2].strip()
201             if fname:
202                 # not empty
203                 if fname in self._zipfile.namelist():
204                     # read from zip file with StringIO wrapper
205                     file_minmax = cStringIO.StringIO(self._zipfile.read(fname, "r"))
206                     reader_minmax = csv.reader(file_minmax)  # (name of skill, min2, max)
207
208                     reader_minmax.next()  # skip header
209                     for row_minmax in reader_minmax:
210                         min_val, max_val = int(row_minmax[1]), int(row_minmax[2])
211                         skill_id = self._get_skill_id(row_minmax[0].strip())
212                         if skill_id is None:
213                             print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
214                         elif skill_id in skill_dict:
215                             val = skill_dict[skill_id]
216                             skill_dict[skill_id] = (val[0], val[1], min_val, max_val)
217                         else:
218                             skill_dict[skill_id] = (0, 0, min_val, max_val)
219
220                     file_minmax.close()
221                 else:
222                     print "file2 ", fname, " doesn't exist, all of the skill2 become zero"
223             else:
224                 print "file2 ", fname, " doesn't exist, all of the skill2 become zero"
225
226             # insert to minmax table
227             insert_sql = db_supports.MIN_MAX_TABLE_INSERT_SQL.format(table_name=table_name)
228             for k, v in skill_dict.items():
229                 self._cursor.execute(insert_sql, (k,) + v)
230
231             cnt += 1
232
233         self._connect.commit()
234         f.close()
235
236     def _create_seed2_skill2_table(self):
237         u"""csvファイルからSEED2から第2スキルへの表を生成する
238         """
239         print "load Second skill"
240         # create master table of seed2 to skill2
241         self._cursor.execute(db_supports.SEED2_SKILL2_MASTER_TABLE_CREATE_SQL)
242         # read from zip file with StringIO wrapper
243         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_SKILL2_FILE_NAME, "r"))
244         reader = csv.reader(f)  # (omamori_name, filename of Second_skill)
245
246         reader.next()   # skip header row
247         cnt = 0
248         for row in reader:
249             table_name = db_supports.SEED2_SKILL2_TABLE_NAME.format(id=cnt)
250             create_sql = db_supports.SEED2_SKILL2_TABLE_CREATE_SQL.format(table_name=table_name)
251             insert_sql = db_supports.SEED2_SKILL2_MASTER_TABLE_INSERT_SQL
252             amu_id = self._get_amulet_id(row[0].strip())
253             if amu_id is None:
254                 print "amulet name:", row[0].decode("utf-8")
255             self._cursor.execute(insert_sql, (amu_id, table_name))
256             self._cursor.execute(create_sql) # create skill table for each omamori
257
258             fname_skill = row[1].strip()
259             if fname_skill in self._zipfile.namelist(): 
260                 # read from zip file with StringIO wrapper
261                 file_skill = cStringIO.StringIO(self._zipfile.read(fname_skill, "r"))
262                 reader_skill = csv.reader(file_skill)  # (seed2, skill1, skil2, ..., skill7)
263
264                 reader_skill.next()  # skip header
265                 insert_sql = db_supports.SEED2_SKILL2_TABLE_INSERT_SQL.format(table_name=table_name)
266                 for row1 in reader_skill:
267                     seed = row1[0]
268                     vals = [seed]
269                     for name in row1[1:]:
270                         skill_id = self._get_skill_id(name.strip())
271                         if skill_id is None:
272                             print "name:", name.decode("utf-8"), ", skill_id", skill_id
273                         else:
274                             vals.append(skill_id)
275                     self._cursor.execute(insert_sql, tuple(vals))
276                 file_skill.close()
277             else:
278                 print "file1 ", fname_skill, " doesn't exist!"
279
280             cnt += 1
281         f.close()
282         self._connect.commit()
283
284     def _create_seed2_threshold1_table(self):
285         u""" csvファイルよりSEED2から判定値1へのテーブルを作成する
286         """
287         print "load Threshold1"
288         # for threshold1 table
289         # create threshold1 table from seed2
290         self._cursor.execute(db_supports.SEED2_THRESHOLD1_TABLE_CREATE_SQL)    
291         
292         # read from zip file with StringIO wrapper
293         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_THRESHOLD1_FILE_NAME, "r"))
294         reader = csv.reader(f)  # (seed2, threshold1_1, threshold1_2, ..., threshold1_7)
295
296         reader.next()   # skip header row
297         insert_sql = db_supports.SEED2_THRESHOLD1_TABLE_INSERT_SQL
298         for row in reader:
299             val_tup = tuple([int(x.strip()) for x in row])
300             self._cursor.execute(insert_sql, val_tup)
301
302         f.close()
303         self._connect.commit()
304
305     def _create_sufficient_value_table(self):
306         u"""csvファイルから充足値の表を生成する
307         """
308         print "load Sufficient Value"
309         # create master table of sufficient value
310         self._cursor.execute(db_supports.SUFFICIENT_MASTER_TABLE_CREATE_SQL)
311         # read from zip file with StringIO wrapper
312         f = cStringIO.StringIO(self._zipfile.read(db_supports.SUFFICIENT_FILE_NAME, "r"))
313         reader = csv.reader(f)  # (omamori_name, filename of sufficient values)
314
315         reader.next()   # skip header row
316         cnt = 0
317         for row in reader:
318             table_name = db_supports.SUFFICIENT_TABLE_NAME.format(id=cnt)
319             create_sql = db_supports.SUFFICIENT_TABLE_CREATE_SQL.format(table_name=table_name)
320             insert_sql = db_supports.SUFFICIENT_MASTER_TABLE_INSERT_SQL
321             amu_id = self._get_amulet_id(row[0].strip())
322             if amu_id is None:
323                 print "amulet name:", row[0].decode("utf-8")
324             self._cursor.execute(insert_sql, (amu_id, table_name))
325             self._cursor.execute(create_sql) # create skill table for each omamori
326
327             fname_threshold = row[1].strip()
328             if fname_threshold in self._zipfile.namelist():
329                 # read from zip file with StringIO wrapper
330                 file_threshold = cStringIO.StringIO(self._zipfile.read(fname_threshold, "r"))
331                 reader_threshold = csv.reader(file_threshold)  # (sufficient value, slot1 threshold, ..., slot3 threshold)
332
333                 reader_threshold.next()  # skip header
334                 insert_sql = db_supports.SUFFICIENT_TABLE_INSERT_SQL.format(table_name=table_name)
335                 for row_vals in reader_threshold:
336                     val_tup = tuple([int(x.strip()) for x in row_vals])
337                     self._cursor.execute(insert_sql, val_tup)
338                 file_threshold.close()
339             else:
340                 print "file1 ", fname_threshold, " doesn't exist!"
341
342             cnt += 1
343         f.close()
344         self._connect.commit()
345
346 if __name__ == "__main__":
347     #db = DataBaseGenerator(DB_FILE_NAME)
348     db = DataBaseGenerator("test.sqlite3")
349     #db = DataBaseGenerator()    # for memory
350     db.generate_db()
351