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