# -*- coding: utf-8 -*-
# databaseの生成用スクリプト
+# 2013/12/04 written by kei9
import sqlite3
import csv
import db_supports
class DataBaseGenerator(object):
- u""" this is access class to database """
- def __init__(self, dbName=None):
+ u""" this is generate class of database """
+ def __init__(self, db_name=None):
if dbName is None:
self._db_name = ":memory:"
else :
- self._db_name = dbName
+ self._db_name = db_name
self._tmp_db = None
self._dict_skill_id = {}
+ self._dict_amulet_id = {}
def generate_db(self, over_write=True):
u""" DBを作成する関数。over_writeがTrueで既存のDBがある場合は上書きする """
# db generation
self._create_skill_table()
+ self._create_amulet_table()
self._create_second_skill_table()
self._create_min_max_table()
self._create_sufficient_value_table()
self._dict_skill_id[skill_name] = skill_ids[0]
return skill_ids[0]
+ def _get_amulet_id(self, amulet_name):
+ u""" 与えられたお守り名からお守りのIDを得る関数。スキルが存在しなければNoneを返す """
+ amulet_name = unicode(amulet_name, 'utf-8')
+
+ if amulet_name in self._dict_amulet_id:
+ return self._dict_amulet_id[amulet_name]
+ else:
+ sql = db_supports.AMULET_TABLE_AMULET2ID_SQL.format(amulet_name=amulet_name)
+ self._cursor.execute(sql)
+ amulet_ids = []
+ for val in self._cursor.fetchall():
+ amulet_ids.append(val[0])
+ if len(amulet_ids) < 1:
+ return None
+ else:
+ self._dict_amulet_id[amulet_name] = amulet_ids[0]
+ return amulet_ids[0]
+
def _create_skill_table(self):
u"""スキルとIDの組み合わせテーブルを作成する"""
print "create skill table"
except sqlite3.IntegrityError, e:
print "not unique:", row[0].decode("utf-8")
raise e
+ self._connect.commit()
+ f.close()
+ def _create_amulet_table(self):
+ u"""お守り名とIDの組み合わせテーブルを作成する"""
+ print "create amulet table"
+ self._cursor.execute(db_supports.AMULET_TABLE_CREATE_SQL)
+ # read from zip file with StringIO wrapper
+ f = cStringIO.StringIO(self._zipfile.read(db_supports.AMULET_FILE_NAME, "r"))
+ reader = csv.reader(f) # (amuleteName)
+
+ reader.next() # skip header row
+ for row in reader:
+ try:
+ self._cursor.execute(db_supports.AMULET_TABLE_INSERT_SQL, (row[0].strip(),))
+ except sqlite3.IntegrityError, e:
+ print "not unique:", row[0].decode("utf-8")
+ raise e
self._connect.commit()
f.close()
for row in reader:
table_name = db_supports.MIN_MAX_TABLE_NAME.format(id=cnt)
insert_sql = db_supports.MIN_MAX_MASTER_TABLE_INSERT_SQL
- oma_name = row[0].strip()
- self._cursor.execute(insert_sql, (oma_name, table_name))
+ amu_id = self._get_amulet_id(row[0].strip())
+ if amu_id is None:
+ print "amulet name:", row[0].decode("utf-8")
+ self._cursor.execute(insert_sql, (amu_id, table_name))
create_sql = db_supports.MIN_MAX_TABLE_CREATE_SQL.format(table_name=table_name)
self._cursor.execute(create_sql) # create minmax table for each omamori
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
- oma_name = row[0].strip()
- self._cursor.execute(insert_sql, (oma_name, table_name))
+ amu_id = self._get_amulet_id(row[0].strip())
+ if amu_id is None:
+ print "amulet name:", row[0].decode("utf-8")
+ self._cursor.execute(insert_sql, (amu_id, table_name))
self._cursor.execute(create_sql) # create skill table for each omamori
fname_skill = row[1].strip()
table_name = db_supports.SUFFICIENT_TABLE_NAME.format(id=cnt)
create_sql = db_supports.SUFFICIENT_TABLE_CREATE_SQL.format(table_name=table_name)
insert_sql = db_supports.SUFFICIENT_MASTER_TABLE_INSERT_SQL
- oma_name = row[0].strip()
- self._cursor.execute(insert_sql, (oma_name, table_name))
+ amu_id = self._get_amulet_id(row[0].strip())
+ if amu_id is None:
+ print "amulet name:", row[0].decode("utf-8")
+ self._cursor.execute(insert_sql, (amu_id, table_name))
self._cursor.execute(create_sql) # create skill table for each omamori
fname_threshold = row[1].strip()
# constant numbers of database
# データベース関連の定数値など
-# 2013/12/03 written by kei9
+# 2013/12/04 written by kei9
# import modules
import os.path
DATA_DIRECTORY = "data"
OUTPUT_DB_FILE_NAME = "OmamoriMH4.sqlite3"
SKILL_FILE_NAME = "skill.csv"
+AMULET_FILE_NAME = "amulet.csv"
MIN_MAX_FILE_NAME = "minmax.csv"
SUFFICIENT_FILE_NAME = "sufficient.csv"
SECOND_SKILL_FILE_NAME = "2ndskill.csv"
skill_col=SKILL_TABLE_COL_SKILL_NAME,
table=SKILL_TABLE_NAME)
+# for amulet table
+AMULET_TABLE_NAME = u"amulet_table"
+AMULET_TABLE_COL_AMULET_NAME = u"amulet_name"
+AMULET_TABLE_CREATE_SQL = u"""create table if not exists {table}
+ (id integer primary key, {amulet_name} varchar unique);""".format(
+ table=AMULET_TABLE_NAME,
+ amulet_name=AMULET_TABLE_COL_AMULET_NAME)
+AMULET_TABLE_INSERT_SQL = u"""insert into {table}
+ ({amulet_col}) values(?);""".format(
+ table=AMULET_TABLE_NAME,
+ amulet_col=AMULET_TABLE_COL_AMULET_NAME)
+AMULET_TABLE_ID2AMULET_SQL = u"""select {amulet_col} from {table} where id='{{id}}'""".format(
+ amulet_col=AMULET_TABLE_COL_AMULET_NAME,
+ table=AMULET_TABLE_NAME)
+AMULET_TABLE_AMULET2ID_SQL = u"""select id from {table} where {amulet_col}='{{amulet_name}}'""".format(
+ amulet_col=AMULET_TABLE_COL_AMULET_NAME,
+ table=AMULET_TABLE_NAME)
+
# for minmax of skill
MIN_MAX_MASTER_TABLE_NAME = u"skill_minmax_master"
MIN_MAX_TABLE_NAME = u"skill_minmax_{id}"
-MIN_MAX_COL_AMULET_NAME = u"amulet_name"
+MIN_MAX_COL_AMULET_ID = u"amulet_id"
MIN_MAX_COL_SKILL_TABLE_NAME = u"skill_table_name"
MIN_MAX_COL_MIN1 = u"min1"
MIN_MAX_COL_MIN2 = u"min2"
MIN_MAX_COL_MAX2 = u"max2"
MIN_MAX_COL_SKILL_ID = u"skill_id"
MIN_MAX_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
- (id integer primary key, {amulet_name} varchar, {table_name} varchar);""".format(
+ (id integer primary key, {amulet_id} integer, {table_name} varchar,
+ foreign key(amulet_id) references {amulet_table}(id));""".format(
master=MIN_MAX_MASTER_TABLE_NAME,
- amulet_name=MIN_MAX_COL_AMULET_NAME,
+ amulet_id=MIN_MAX_COL_AMULET_ID,
+ amulet_table=AMULET_TABLE_NAME,
table_name=MIN_MAX_COL_SKILL_TABLE_NAME)
MIN_MAX_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
(id integer primary key, {skill_id} integer,
max2=MIN_MAX_COL_MAX2,
skill_table=SKILL_TABLE_NAME)
MIN_MAX_MASTER_TABLE_INSERT_SQL = u"""insert into {master}
- ({amulet_name}, {table_col}) values(?,?);""".format(
+ ({amulet_id}, {table_col}) values(?,?);""".format(
master=MIN_MAX_MASTER_TABLE_NAME,
table_col=MIN_MAX_COL_SKILL_TABLE_NAME,
- amulet_name=MIN_MAX_COL_AMULET_NAME)
+ amulet_id=MIN_MAX_COL_AMULET_ID)
MIN_MAX_TABLE_INSERT_SQL = u"""insert into {{table_name}}
({skill_id}, {min1}, {max1}, {min2}, {max2}) values(?,?,?,?,?)""".format(
skill_id=MIN_MAX_COL_SKILL_ID,
SECOND_MASTER_TABLE_NAME = u"skill_second_master"
SECOND_TABLE_NAME = u"skill_second_{id}"
SECOND_SLOT_TABLE_NAME = u"skill_second_slot"
-SECOND_COL_AMULET_NAME = u"amulet_name"
+SECOND_COL_AMULET_ID = u"amulet_id"
SECOND_COL_SECOND_TABLE_NAME = u"second_table_name"
SECOND_COL_RANDOM_SEED = u"random_seed"
SECOND_COL_SKILL_ID1 = u"skill_id1"
SECOND_COL_SKILL_ID5 = u"skill_id5"
SECOND_COL_SKILL_ID6 = u"skill_id6"
SECOND_COL_SKILL_ID7 = u"skill_id7"
+SECOND_COL_SKILL_LIST = [SECOND_COL_SKILL_ID1, SECOND_COL_SKILL_ID2, SECOND_COL_SKILL_ID3, SECOND_COL_SKILL_ID4, SECOND_COL_SKILL_ID5, SECOND_COL_SKILL_ID6, SECOND_COL_SKILL_ID7]
SECOND_COL_SLOT1 = u"slot1"
SECOND_COL_SLOT2 = u"slot2"
SECOND_COL_SLOT3 = u"slot3"
SECOND_COL_SLOT5 = u"slot5"
SECOND_COL_SLOT6 = u"slot6"
SECOND_COL_SLOT7 = u"slot7"
+SECOND_COL_SLOT_LIST = [SECOND_COL_SLOT1, SECOND_COL_SLOT2, SECOND_COL_SLOT3, SECOND_COL_SLOT4, SECOND_COL_SLOT5, SECOND_COL_SLOT6, SECOND_COL_SLOT7]
SECOND_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
- (id integer primary key, {amulet} varchar, {table_name} varchar);""".format(
+ (id integer primary key, {amulet_id} integer, {table_name} varchar,
+ foreign key(amulet_id) references {amulet_table}(id));""".format(
master=SECOND_MASTER_TABLE_NAME,
- amulet=SECOND_COL_AMULET_NAME,
+ amulet_id=SECOND_COL_AMULET_ID,
+ amulet_table=AMULET_TABLE_NAME,
table_name=SECOND_COL_SECOND_TABLE_NAME)
SECOND_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
(id integer primary key, {seed} integer unique,
({amulet_col}, {table_col}) values(?,?);""".format(
table=SECOND_MASTER_TABLE_NAME,
table_col=SECOND_COL_SECOND_TABLE_NAME,
- amulet_col=SECOND_COL_AMULET_NAME)
+ amulet_col=SECOND_COL_AMULET_ID)
SECOND_TABLE_INSERT_SQL = u"""insert into {{table_name}}
({seed}, {skill_id1}, {skill_id2}, {skill_id3},
{skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) values(?,?,?,?,?,?,?,?)""".format(
# for sufficient value for slots
SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master"
SUFFICIENT_TABLE_NAME = u"sufficient_{id}"
-SUFFICIENT_COL_AMULET_NAME = u"amulet_name"
+SUFFICIENT_COL_AMULET_ID = u"amulet_id"
SUFFICIENT_COL_SUFFICIENT_TABLE_NAME = u"sufficient_table_name"
SUFFICIENT_COL_SUFFICIENT_VALUE = u"sufficient_value"
SUFFICIENT_COL_SLOT1_THRESHOLD = u"slot1_threshold"
SUFFICIENT_COL_SLOT2_THRESHOLD = u"slot2_threshold"
SUFFICIENT_COL_SLOT3_THRESHOLD = u"slot3_threshold"
SUFFICIENT_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
- (id integer primary key, {amulet} varchar, {table_name} varchar);""".format(
+ (id integer primary key, {amulet_id} integer, {table_name} varchar,
+ foreign key(amulet_id) references {amulet_table}(id));""".format(
master=SUFFICIENT_MASTER_TABLE_NAME,
- amulet=SUFFICIENT_COL_AMULET_NAME,
+ amulet_id=SUFFICIENT_COL_AMULET_ID,
+ amulet_table=AMULET_TABLE_NAME,
table_name=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME)
SUFFICIENT_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
(id integer primary key, {sufficient_val} integer unique,
({amulet_col}, {table_col}) values(?,?);""".format(
table=SUFFICIENT_MASTER_TABLE_NAME,
table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
- amulet_col=SUFFICIENT_COL_AMULET_NAME)
+ amulet_col=SUFFICIENT_COL_AMULET_ID)
SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}}
({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val})
values(?,?,?,?);""".format(
OSDN Git Service
