# -*- coding: utf-8 -*-
# databaseの生成用スクリプト
-# 2013/12/04 written by kei9
+# 2013/12/09 written by kei9
import sqlite3
import csv
# db generation
self._create_skill_table()
self._create_amulet_table()
- self._create_second_skill_table()
+ self._create_seed2_skill2_table()
+ self._create_seed2_threshold1_table()
+ self._create_seed2_threshold2_table()
+ self._create_seed2_inishie_table()
self._create_min_max_table()
self._create_sufficient_value_table()
+ self._create_seed1_tenun_table()
self._zipfile.close()
self._close()
file_minmax.close()
else:
- print "file2 ", fname, " doesn't exist!"
+ print "file2 ", fname, " doesn't exist, all of the skill2 become zero"
else:
- print "file2 ", fname, " doesn't exist!"
+ print "file2 ", fname, " doesn't exist, all of the skill2 become zero"
# insert to minmax table
insert_sql = db_supports.MIN_MAX_TABLE_INSERT_SQL.format(table_name=table_name)
self._connect.commit()
f.close()
- def _create_second_skill_table(self):
- u"""csvファイルから第2スキルの表を生成する
+ def _create_seed2_skill2_table(self):
+ u"""csvファイルからSEED2から第2スキルへの表を生成する
"""
print "load Second skill"
- # create master table of Second skill
- self._cursor.execute(db_supports.SECOND_MASTER_TABLE_CREATE_SQL)
+ # create master table of seed2 to skill2
+ self._cursor.execute(db_supports.SEED2_SKILL2_MASTER_TABLE_CREATE_SQL)
# read from zip file with StringIO wrapper
- f = cStringIO.StringIO(self._zipfile.read(db_supports.SECOND_SKILL_FILE_NAME, "r"))
+ f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_SKILL2_FILE_NAME, "r"))
reader = csv.reader(f) # (omamori_name, filename of Second_skill)
reader.next() # skip header row
cnt = 0
for row in reader:
- table_name = db_supports.SECOND_TABLE_NAME.format(id=cnt)
- create_sql = db_supports.SECOND_TABLE_CREATE_SQL.format(table_name=table_name)
- insert_sql = db_supports.SECOND_MASTER_TABLE_INSERT_SQL
+ table_name = db_supports.SEED2_SKILL2_TABLE_NAME.format(id=cnt)
+ create_sql = db_supports.SEED2_SKILL2_TABLE_CREATE_SQL.format(table_name=table_name)
+ insert_sql = db_supports.SEED2_SKILL2_MASTER_TABLE_INSERT_SQL
amu_id = self._get_amulet_id(row[0].strip())
if amu_id is None:
print "amulet name:", row[0].decode("utf-8")
if fname_skill in self._zipfile.namelist():
# read from zip file with StringIO wrapper
file_skill = cStringIO.StringIO(self._zipfile.read(fname_skill, "r"))
- reader_skill = csv.reader(file_skill) # (random seed, skill1, skil2, ..., skill7)
+ reader_skill = csv.reader(file_skill) # (seed2, skill1, skil2, ..., skill7)
reader_skill.next() # skip header
- insert_sql = db_supports.SECOND_TABLE_INSERT_SQL.format(table_name=table_name)
+ insert_sql = db_supports.SEED2_SKILL2_TABLE_INSERT_SQL.format(table_name=table_name)
for row1 in reader_skill:
seed = row1[0]
vals = [seed]
cnt += 1
f.close()
+ self._create_seed2_table_no_table()
+ self._connect.commit()
+
+ def _create_seed2_table_no_table(self):
+ u""" Seed2に対応するテーブル値を格納するテーブルを作成する """
+ self._cursor.execute(db_supports.SEED2_TABLE_NO_TABLE_CREATE_SQL)
+
+ # read from zip file with StringIO wrapper
+ f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_TABLE_NO_FILE_NAME, "r"))
+ reader = csv.reader(f) # (seed2, table_no, no)
+
+ reader.next() # skip header row
+ for row in reader:
+ vals = [int(r.strip()) for r in row]
+ seed, table_no, no = vals[0], vals[1], vals[2]
+ self._cursor.execute(db_supports.SEED2_TABLE_NO_TABLE_INSERT_SQL, (no, seed, table_no))
+ self._connect.commit()
+ f.close()
+
+ def _create_seed2_inishie_table(self):
+ u""" いにしえの錬金の第2Seed対応テーブルを作成する """
+ self._cursor.execute(db_supports.SEED2_INISHIE_TABLE_CREATE_SQL)
+
+ # read from zip file with StringIO wrapper
+ f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_INISHIE_FILE_NAME, "r"))
+ reader = csv.reader(f) # (seed2, skill_name, threshold1, threshold2)
+
+ reader.next() # skip header row
+ for row in reader:
+ try:
+ skill_id = self._get_skill_id(row[1].strip())
+ if skill_id is None:
+ print "name:", row[1].strip().decode("utf-8"), ", skill_id", skill_id
+
+ seed, th1, th2 = row[0].strip(), row[2].strip(), row[3].strip()
+ self._cursor.execute(db_supports.SEED2_INISHIE_TABLE_INSERT_SQL,
+ (seed, skill_id, th1, th2))
+ except sqlite3.IntegrityError, e:
+ print "not unique:", row[0].decode("utf-8")
+ raise e
+ self._connect.commit()
+ f.close()
+
+ def _create_seed2_threshold1_table(self):
+ u""" csvファイルよりSEED2から判定値1へのテーブルを作成する
+ """
+ print "load Threshold1"
+ # for threshold1 table
+ # create threshold1 table from seed2
+ self._cursor.execute(db_supports.SEED2_THRESHOLD1_TABLE_CREATE_SQL)
- # for Slot table
- self._cursor.execute(db_supports.SECOND_SLOT_TABLE_CREATE_SQL) # create slot table of Second skill
# read from zip file with StringIO wrapper
- f = cStringIO.StringIO(self._zipfile.read(db_supports.SECOND_SLOT_FILE_NAME, "r"))
- reader = csv.reader(f) # (random_seed, slot1, slot2, ..., slot7)
+ f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_THRESHOLD1_FILE_NAME, "r"))
+ reader = csv.reader(f) # (seed2, threshold1_1, threshold1_2, ..., threshold1_7)
reader.next() # skip header row
- insert_sql = db_supports.SECOND_SLOT_TABLE_INSERT_SQL
+ insert_sql = db_supports.SEED2_THRESHOLD1_TABLE_INSERT_SQL
+ for row in reader:
+ val_tup = tuple([int(x.strip()) for x in row])
+ self._cursor.execute(insert_sql, val_tup)
+
+ f.close()
+ self._connect.commit()
+
+ def _create_seed2_threshold2_table(self):
+ u""" csvファイルよりSEED2から判定値2へのテーブルを作成する
+ """
+ print "load Threshold2"
+ # for threshold2 table
+ # create threshold2 table from seed2
+ self._cursor.execute(db_supports.SEED2_THRESHOLD2_TABLE_CREATE_SQL)
+
+ # read from zip file with StringIO wrapper
+ f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED2_THRESHOLD2_FILE_NAME, "r"))
+ reader = csv.reader(f) # (seed2, threshold2_1, threshold2_2, ..., threshold2_7)
+
+ reader.next() # skip header row
+ insert_sql = db_supports.SEED2_THRESHOLD2_TABLE_INSERT_SQL
for row in reader:
val_tup = tuple([int(x.strip()) for x in row])
self._cursor.execute(insert_sql, val_tup)
f.close()
self._connect.commit()
+ def _create_seed1_tenun_table(self):
+ u""" csvファイルより天運の錬金結果からSEED1へのテーブルを作成する
+ """
+ print "load seed1"
+ # for seed1 table
+ # tenun555
+ self._cursor.execute(db_supports.SEED1_TENUN555_TABLE_CREATE_SQL)
+ # read from zip file with StringIO wrapper
+ f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED1_TENUN555_FILE_NAME, "r"))
+ # (table_no, no, seed1, result_no, amu1, amu2, ...,, amu6, skill_id1, skill_id2, ..., skill_id6)
+ reader = csv.reader(f)
+ reader.next() # skip header row
+ reader.next() # skip header row
+ insert_sql = db_supports.SEED1_TENUN555_TABLE_INSERT_SQL
+ for row in reader:
+ vals = [x.strip() for x in row]
+ table_no, no, seed1, result_no = vals[0], vals[1], vals[2],vals[3]
+ amu_ids = [self._get_amulet_id(x) for x in vals[4:10]]
+ if len(amu_ids) < 6:
+ amu_ids += [None] * (6-len(amu_ids))
+ skill_ids = [self._get_skill_id(x) for x in vals[10:16]]
+ if len(skill_ids) < 6:
+ skill_ids += [None] * (6-len(skill_ids))
+ vals = [no, table_no, seed1, result_no] + amu_ids + skill_ids
+ self._cursor.execute(insert_sql,
+ tuple([int(x) if x is not None else db_supports.NO_DATA for x in vals]))
+ f.close()
+
+ # tenun888
+ self._cursor.execute(db_supports.SEED1_TENUN888_TABLE_CREATE_SQL)
+ # read from zip file with StringIO wrapper
+ f = cStringIO.StringIO(self._zipfile.read(db_supports.SEED1_TENUN888_FILE_NAME, "r"))
+ # (table_no, no, seed1, result_no, amu1, amu2, ...,, amu7, skill_id1, skill_id2, ..., skill_id7)
+ reader = csv.reader(f)
+ reader.next() # skip header row
+ reader.next() # skip header row
+ insert_sql = db_supports.SEED1_TENUN888_TABLE_INSERT_SQL
+ for row in reader:
+ vals = [x.strip() for x in row]
+ table_no, no, seed1, result_no = vals[0], vals[1], vals[2], vals[3]
+ amu_ids = [self._get_amulet_id(x) for x in vals[4:11]]
+ if len(amu_ids) < 7:
+ amu_ids += [None] * (7-len(amu_ids))
+ skill_ids = [self._get_skill_id(x) for x in vals[11:18]]
+ if len(skill_ids) < 7:
+ skill_ids += [None] * (7-len(skill_ids))
+ vals = [no, table_no, seed1, result_no] + amu_ids + skill_ids
+ self._cursor.execute(insert_sql,
+ tuple([int(x) if x is not None else db_supports.NO_DATA for x in vals]))
+ f.close()
+ self._connect.commit()
+
+
if __name__ == "__main__":
#db = DataBaseGenerator(DB_FILE_NAME)
db = DataBaseGenerator("test.sqlite3")