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_seed2_threshold2_table()
  50         self._create_seed2_inishie_table()
  51         self._create_min_max_table()
  52         self._create_sufficient_value_table()
  53         self._create_seed1_tenun_table()
  54
  55         self._zipfile.close()
  56         self._close()
  57
  58         # rename operation of tmp file
  59         if self._tmp_db is not None:
  60             with tempfile.NamedTemporaryFile(delete=True) as tmp_file:
  61                 tmp_name = tmp_file.name
  62             os.rename(self._db_name, tmp_name)
  63             os.rename(self._tmp_db, self._db_name)
  64             os.remove(tmp_name)
  65
  66     def _open(self):
  67         if self._tmp_db is None:
  68             self._connect = sqlite3.connect(self._db_name)
  69         else:
  70             self._connect = sqlite3.connect(self._tmp_db)
  71         self._connect.text_factory = str   # for usage of utf-8
  72         self._cursor = self._connect.cursor()
  73
  74     def _close(self):
  75         self._connect.commit()
  76         self._cursor.close()
  77         self._connect.close()
  78
  79     def _get_skill_id(self, skill_name):
  80         u""" 与えられたスキル名からスキルのIDを得る関数。スキルが存在しなければNoneを返す """
  81         skill_name = unicode(skill_name, 'utf-8')
  82
  83         if skill_name in self._dict_skill_id:
  84             return self._dict_skill_id[skill_name]
  85         else:
  86             sql = db_supports.SKILL_TABLE_SKILL2ID_SQL.format(skill_name=skill_name)
  87             self._cursor.execute(sql)
  88             skill_ids = []
  89             for val in self._cursor.fetchall():
  90                 skill_ids.append(val[0])
  91             if len(skill_ids) < 1:
  92                 return None
  93             else:
  94                 self._dict_skill_id[skill_name] = skill_ids[0]
  95                 return skill_ids[0]
  96
  97     def _get_amulet_id(self, amulet_name):
  98         u""" 与えられたお守り名からお守りのIDを得る関数。スキルが存在しなければNoneを返す """
  99         amulet_name = unicode(amulet_name, 'utf-8')
 100
 101         if amulet_name in self._dict_amulet_id:
 102             return self._dict_amulet_id[amulet_name]
 103         else:
 104             sql = db_supports.AMULET_TABLE_AMULET2ID_SQL.format(amulet_name=amulet_name)
 105             self._cursor.execute(sql)
 106             amulet_ids = []
 107             for val in self._cursor.fetchall():
 108                 amulet_ids.append(val[0])
 109             if len(amulet_ids) < 1:
 110                 return None
 111             else:
 112                 self._dict_amulet_id[amulet_name] = amulet_ids[0]
 113                 return amulet_ids[0]
 114
 115     def _create_skill_table(self):
 116         u"""スキルとIDの組み合わせテーブルを作成する"""
 117         print "create skill table"
 118         self._cursor.execute(db_supports.SKILL_TABLE_CREATE_SQL)
 119
 120         # read from zip file with StringIO wrapper
 121         f = cStringIO.StringIO(self._zipfile.read(db_supports.SKILL_FILE_NAME, "r"))
 122         reader = csv.reader(f)  # (skillName)
 123
 124         reader.next()   # skip header row
 125         for row in reader:
 126             try:
 127                 self._cursor.execute(db_supports.SKILL_TABLE_INSERT_SQL, (row[0].strip(),))
 128             except sqlite3.IntegrityError, e:
 129                 print "not unique:", row[0].decode("utf-8")
 130                 raise e
 131         self._connect.commit()
 132         f.close()
 133
 134     def _create_amulet_table(self):
 135         u"""お守り名とIDの組み合わせテーブルを作成する"""
 136         print "create amulet table"
 137         self._cursor.execute(db_supports.AMULET_TABLE_CREATE_SQL)
 138
 139         # read from zip file with StringIO wrapper
 140         f = cStringIO.StringIO(self._zipfile.read(db_supports.AMULET_FILE_NAME, "r"))
 141         reader = csv.reader(f)  # (amuleteName)
 142
 143         reader.next()   # skip header row
 144         for row in reader:
 145             try:
 146                 self._cursor.execute(db_supports.AMULET_TABLE_INSERT_SQL, (row[0].strip(),))
 147             except sqlite3.IntegrityError, e:
 148                 print "not unique:", row[0].decode("utf-8")
 149                 raise e
 150         self._connect.commit()
 151         f.close()
 152
 153     def _create_min_max_table(self):
 154         u"""お守り名と対応するスキルの最大最小値の記載されたcsvファイルから
 155         お守りごとのスキルの最大最小値を記載したテーブルを作成する
 156         """
 157         print "load min & max of skill"
 158         # create master table of skill min max
 159         self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_CREATE_SQL)
 160         # read from zip file with StringIO wrapper
 161         f = cStringIO.StringIO(self._zipfile.read(db_supports.MIN_MAX_FILE_NAME, "r"))
 162         reader = csv.reader(f)  # (name, filename of minmax1, filename of minmax2)
 163
 164         reader.next()   # skip header row
 165         cnt = 0
 166         for row in reader:
 167             table_name = db_supports.MIN_MAX_TABLE_NAME.format(id=cnt)
 168             insert_sql = db_supports.MIN_MAX_MASTER_TABLE_INSERT_SQL
 169             amu_id = self._get_amulet_id(row[0].strip())
 170             if amu_id is None:
 171                 print "amulet name:", row[0].decode("utf-8")
 172             self._cursor.execute(insert_sql, (amu_id, table_name))
 173
 174             create_sql = db_supports.MIN_MAX_TABLE_CREATE_SQL.format(table_name=table_name)
 175             self._cursor.execute(create_sql) # create minmax table for each omamori
 176
 177             skill_dict = {} # dict for skill1, skill2
 178
 179             # get min max of skill1
 180             fname = row[1].strip()
 181             if fname:
 182                 # not empty
 183                 if fname in self._zipfile.namelist():
 184                     # read from zip file with StringIO wrapper
 185                     file_minmax = cStringIO.StringIO(self._zipfile.read(fname, "r"))
 186                     reader_minmax = csv.reader(file_minmax)  # (name of skill, min1, max1)
 187
 188                     reader_minmax.next()  # skip header
 189                     for row_minmax in reader_minmax:
 190                         min_val, max_val = int(row_minmax[1]), int(row_minmax[2])
 191                         skill_id = self._get_skill_id(row_minmax[0].strip())
 192                         if skill_id is None:
 193                             print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
 194                         skill_dict[skill_id] = (min_val, max_val, 0, 0)
 195
 196                     file_minmax.close()
 197                 else:
 198                     print "file1 ", fname, " doesn't exist!"
 199             else:
 200                 print "file1 ", fname, " doesn't exist!"
 201
 202             # get min max of skill2
 203             fname = row[2].strip()
 204             if fname:
 205                 # not empty
 206                 if fname in self._zipfile.namelist():
 207                     # read from zip file with StringIO wrapper
 208                     file_minmax = cStringIO.StringIO(self._zipfile.read(fname, "r"))
 209                     reader_minmax = csv.reader(file_minmax)  # (name of skill, min2, max)
 210
 211                     reader_minmax.next()  # skip header
 212                     for row_minmax in reader_minmax:
 213                         min_val, max_val = int(row_minmax[1]), int(row_minmax[2])
 214                         skill_id = self._get_skill_id(row_minmax[0].strip())
 215                         if skill_id is None:
 216                             print "name:", row_minmax[0].decode("utf-8"), ", skill_id", skill_id
 217                         elif skill_id in skill_dict:
 218                             val = skill_dict[skill_id]
 219                             skill_dict[skill_id] = (val[0], val[1], min_val, max_val)
 220                         else:
 221                             skill_dict[skill_id] = (0, 0, min_val, max_val)
 222
 223                     file_minmax.close()
 224                 else:
 225                     print "file2 ", fname, " doesn't exist, all of the skill2 become zero"
 226             else:
 227                 print "file2 ", fname, " doesn't exist, all of the skill2 become zero"
 228
 229             # insert to minmax table
 230             insert_sql = db_supports.MIN_MAX_TABLE_INSERT_SQL.format(table_name=table_name)
 231             for k, v in skill_dict.items():
 232                 self._cursor.execute(insert_sql, (k,) + v)
 233
 234             cnt += 1
 235
 236         self._connect.commit()
 237         f.close()
 238
 239     def _create_seed2_skill2_table(self):
 240         u"""csvファイルからSEED2から第2スキルへの表を生成する
 241         """
 242         print "load Second skill"
 243         # create master table of seed2 to skill2
 244         self._cursor.execute(db_supports.SEED2_SKILL2_MASTER_TABLE_CREATE_SQL)
 245         # read from zip file with StringIO wrapper
 246         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_SKILL2_FILE_NAME, "r"))
 247         reader = csv.reader(f)  # (omamori_name, filename of Second_skill)
 248
 249         reader.next()   # skip header row
 250         cnt = 0
 251         for row in reader:
 252             table_name = db_supports.SEED2_SKILL2_TABLE_NAME.format(id=cnt)
 253             create_sql = db_supports.SEED2_SKILL2_TABLE_CREATE_SQL.format(table_name=table_name)
 254             insert_sql = db_supports.SEED2_SKILL2_MASTER_TABLE_INSERT_SQL
 255             amu_id = self._get_amulet_id(row[0].strip())
 256             if amu_id is None:
 257                 print "amulet name:", row[0].decode("utf-8")
 258             self._cursor.execute(insert_sql, (amu_id, table_name))
 259             self._cursor.execute(create_sql) # create skill table for each omamori
 260
 261             fname_skill = row[1].strip()
 262             if fname_skill in self._zipfile.namelist():
 263                 # read from zip file with StringIO wrapper
 264                 file_skill = cStringIO.StringIO(self._zipfile.read(fname_skill, "r"))
 265                 reader_skill = csv.reader(file_skill)  # (seed2, skill1, skil2, ..., skill7)
 266
 267                 reader_skill.next()  # skip header
 268                 insert_sql = db_supports.SEED2_SKILL2_TABLE_INSERT_SQL.format(table_name=table_name)
 269                 for row1 in reader_skill:
 270                     seed = row1[0]
 271                     vals = [seed]
 272                     for name in row1[1:]:
 273                         skill_id = self._get_skill_id(name.strip())
 274                         if skill_id is None:
 275                             print "name:", name.decode("utf-8"), ", skill_id", skill_id
 276                         else:
 277                             vals.append(skill_id)
 278                     self._cursor.execute(insert_sql, tuple(vals))
 279                 file_skill.close()
 280             else:
 281                 print "file1 ", fname_skill, " doesn't exist!"
 282
 283             cnt += 1
 284         f.close()
 285         self._create_seed2_table_no_table()
 286         self._connect.commit()
 287
 288     def _create_seed2_table_no_table(self):
 289         u""" Seed2に対応するテーブル値を格納するテーブルを作成する """
 290         self._cursor.execute(db_supports.SEED2_TABLE_NO_TABLE_CREATE_SQL)
 291
 292         # read from zip file with StringIO wrapper
 293         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_TABLE_NO_FILE_NAME, "r"))
 294         reader = csv.reader(f)  # (seed2, table_no, no)
 295
 296         reader.next()   # skip header row
 297         for row in reader:
 298             vals = [int(r.strip()) for r in row]
 299             seed, table_no, no = vals[0], vals[1], vals[2]
 300             self._cursor.execute(db_supports.SEED2_TABLE_NO_TABLE_INSERT_SQL, (no, seed, table_no))
 301         self._connect.commit()
 302         f.close()
 303
 304     def _create_seed2_inishie_table(self):
 305         u""" いにしえの錬金の第2Seed対応テーブルを作成する """
 306         self._cursor.execute(db_supports.SEED2_INISHIE_TABLE_CREATE_SQL)
 307
 308         # read from zip file with StringIO wrapper
 309         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_INISHIE_FILE_NAME, "r"))
 310         reader = csv.reader(f)  # (seed2, skill_name, threshold1, threshold2)
 311
 312         reader.next()   # skip header row
 313         for row in reader:
 314             try:
 315                 skill_id = self._get_skill_id(row[1].strip())
 316                 if skill_id is None:
 317                     print "name:", row[1].strip().decode("utf-8"), ", skill_id", skill_id
 318
 319                 seed, th1, th2 = row[0].strip(), row[2].strip(), row[3].strip()
 320                 self._cursor.execute(db_supports.SEED2_INISHIE_TABLE_INSERT_SQL,
 321                         (seed, skill_id, th1, th2))
 322             except sqlite3.IntegrityError, e:
 323                 print "not unique:", row[0].decode("utf-8")
 324                 raise e
 325         self._connect.commit()
 326         f.close()
 327
 328     def _create_seed2_threshold1_table(self):
 329         u""" csvファイルよりSEED2から判定値1へのテーブルを作成する
 330         """
 331         print "load Threshold1"
 332         # for threshold1 table
 333         # create threshold1 table from seed2
 334         self._cursor.execute(db_supports.SEED2_THRESHOLD1_TABLE_CREATE_SQL)
 335
 336         # read from zip file with StringIO wrapper
 337         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_THRESHOLD1_FILE_NAME, "r"))
 338         reader = csv.reader(f)  # (seed2, threshold1_1, threshold1_2, ..., threshold1_7)
 339
 340         reader.next()   # skip header row
 341         insert_sql = db_supports.SEED2_THRESHOLD1_TABLE_INSERT_SQL
 342         for row in reader:
 343             val_tup = tuple([int(x.strip()) for x in row])
 344             self._cursor.execute(insert_sql, val_tup)
 345
 346         f.close()
 347         self._connect.commit()
 348
 349     def _create_seed2_threshold2_table(self):
 350         u""" csvファイルよりSEED2から判定値2へのテーブルを作成する
 351         """
 352         print "load Threshold2"
 353         # for threshold2 table
 354         # create threshold2 table from seed2
 355         self._cursor.execute(db_supports.SEED2_THRESHOLD2_TABLE_CREATE_SQL)
 356
 357         # read from zip file with StringIO wrapper
 358         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_THRESHOLD2_FILE_NAME, "r"))
 359         reader = csv.reader(f)  # (seed2, threshold2_1, threshold2_2, ..., threshold2_7)
 360
 361         reader.next()   # skip header row
 362         insert_sql = db_supports.SEED2_THRESHOLD2_TABLE_INSERT_SQL
 363         for row in reader:
 364             val_tup = tuple([int(x.strip()) for x in row])
 365             self._cursor.execute(insert_sql, val_tup)
 366
 367         f.close()
 368         self._connect.commit()
 369
 370     def _create_sufficient_value_table(self):
 371         u"""csvファイルから充足値の表を生成する
 372         """
 373         print "load Sufficient Value"
 374         # create master table of sufficient value
 375         self._cursor.execute(db_supports.SUFFICIENT_MASTER_TABLE_CREATE_SQL)
 376         # read from zip file with StringIO wrapper
 377         f = cStringIO.StringIO(self._zipfile.read(db_supports.SUFFICIENT_FILE_NAME, "r"))
 378         reader = csv.reader(f)  # (omamori_name, filename of sufficient values)
 379
 380         reader.next()   # skip header row
 381         cnt = 0
 382         for row in reader:
 383             table_name = db_supports.SUFFICIENT_TABLE_NAME.format(id=cnt)
 384             create_sql = db_supports.SUFFICIENT_TABLE_CREATE_SQL.format(table_name=table_name)
 385             insert_sql = db_supports.SUFFICIENT_MASTER_TABLE_INSERT_SQL
 386             amu_id = self._get_amulet_id(row[0].strip())
 387             if amu_id is None:
 388                 print "amulet name:", row[0].decode("utf-8")
 389             self._cursor.execute(insert_sql, (amu_id, table_name))
 390             self._cursor.execute(create_sql) # create skill table for each omamori
 391
 392             fname_threshold = row[1].strip()
 393             if fname_threshold in self._zipfile.namelist():
 394                 # read from zip file with StringIO wrapper
 395                 file_threshold = cStringIO.StringIO(self._zipfile.read(fname_threshold, "r"))
 396                 reader_threshold = csv.reader(file_threshold)  # (sufficient value, slot1 threshold, ..., slot3 threshold)
 397
 398                 reader_threshold.next()  # skip header
 399                 insert_sql = db_supports.SUFFICIENT_TABLE_INSERT_SQL.format(table_name=table_name)
 400                 for row_vals in reader_threshold:
 401                     val_tup = tuple([int(x.strip()) for x in row_vals])
 402                     self._cursor.execute(insert_sql, val_tup)
 403                 file_threshold.close()
 404             else:
 405                 print "file1 ", fname_threshold, " doesn't exist!"
 406
 407             cnt += 1
 408         f.close()
 409         self._connect.commit()
 410
 411     def _create_seed1_tenun_table(self):
 412         u""" csvファイルより天運の錬金結果からSEED1へのテーブルを作成する
 413         """
 414         print "load seed1"
 415         # for seed1 table
 416         # tenun555
 417         self._cursor.execute(db_supports.SEED1_TENUN555_TABLE_CREATE_SQL)
 418         # read from zip file with StringIO wrapper
 419         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED1_TENUN555_FILE_NAME, "r"))
 420         # (table_no, no, seed1, result_no, amu1, amu2, ...,, amu6, skill_id1, skill_id2, ..., skill_id6)
 421         reader = csv.reader(f)
 422         reader.next()   # skip header row
 423         reader.next()   # skip header row
 424         insert_sql = db_supports.SEED1_TENUN555_TABLE_INSERT_SQL
 425         for row in reader:
 426             vals = [x.strip() for x in row]
 427             table_no, no, seed1, result_no = vals[0], vals[1], vals[2],vals[3]
 428             amu_ids = [self._get_amulet_id(x) for x in vals[4:10]]
 429             if len(amu_ids) < 6:
 430                 amu_ids += [None] * (6-len(amu_ids))
 431             skill_ids = [self._get_skill_id(x) for x in vals[10:16]]
 432             if len(skill_ids) < 6:
 433                 skill_ids += [None] * (6-len(skill_ids))
 434             vals = [no, table_no, seed1, result_no] + amu_ids + skill_ids
 435             self._cursor.execute(insert_sql,
 436                     tuple([int(x) if x is not None else db_supports.NO_DATA for x in vals]))
 437         f.close()
 438
 439         # tenun888
 440         self._cursor.execute(db_supports.SEED1_TENUN888_TABLE_CREATE_SQL)
 441         # read from zip file with StringIO wrapper
 442         f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED1_TENUN888_FILE_NAME, "r"))
 443         # (table_no, no, seed1, result_no, amu1, amu2, ...,, amu7, skill_id1, skill_id2, ..., skill_id7)
 444         reader = csv.reader(f)
 445         reader.next()   # skip header row
 446         reader.next()   # skip header row
 447         insert_sql = db_supports.SEED1_TENUN888_TABLE_INSERT_SQL
 448         for row in reader:
 449             vals = [x.strip() for x in row]
 450             table_no, no, seed1, result_no = vals[0], vals[1], vals[2], vals[3]
 451             amu_ids = [self._get_amulet_id(x) for x in vals[4:11]]
 452             if len(amu_ids) < 7:
 453                 amu_ids += [None] * (7-len(amu_ids))
 454             skill_ids = [self._get_skill_id(x) for x in vals[11:18]]
 455             if len(skill_ids) < 7:
 456                 skill_ids += [None] * (7-len(skill_ids))
 457             vals = [no, table_no, seed1, result_no] + amu_ids + skill_ids
 458             self._cursor.execute(insert_sql,
 459                     tuple([int(x) if x is not None else db_supports.NO_DATA for x in vals]))
 460         f.close()
 461         self._connect.commit()
 462
 463
 464 if __name__ == "__main__":
 465     #db = DataBaseGenerator(DB_FILE_NAME)
 466     db = DataBaseGenerator("test.sqlite3")
 467     #db = DataBaseGenerator()    # for memory
 468     db.generate_db()
 469