From d4b5c22023a0bbb17bbcd7c45cb718a363f57450 Mon Sep 17 00:00:00 2001 From: kei9 Date: Wed, 4 Dec 2013 14:47:53 +0900 Subject: [PATCH] create amulet id table --- model/data.zip | Bin 1670372 -> 1670568 bytes model/db_generator.py | 62 ++++++++++++++++++++++++++++++++++++++++++-------- model/db_supports.py | 55 ++++++++++++++++++++++++++++++++------------ 3 files changed, 94 insertions(+), 23 deletions(-) diff --git a/model/data.zip b/model/data.zip index 1294177aca7535c21a0a8075962f06b81be14bd7..252ac8aa5850b0395f592a156d0d81ed28048858 100644 GIT binary patch delta 365 zcmaFzGj+x9)P@$u7N!>F7M2#)7Pc1l7LFFq7OocV7M>Q~7QPn#7J(MQ7NHj57LgXw z7O@ub7KtsAf8*mSvs;`Kzk545FfcGg05KO3C+3#sq?YI<7nk{-u;x0TAi#1V?!$%6 z0vxwPSt;hU8BpB)QQm$K~Te_v(QX>{H?*6dW?<6oU#d!9x0O=QuF43ge{d`Zba%OzNf6r`AY1 T@`*5rFf3F7M2#)7Pc1l7LFFq7OocV7M>Q~7QPn#7J(MQ7NHj57LgXw z7O@ub7KtsAf8)2OCP>zaPq(j-lwV1X6BQKq_(i3J~Q#ovT_hk?CCC^xSI6NTxr9(@#`O#xX4~nyyhJnF>_Z lS0kCis5SjFkYd_jGCi_Z(veS?L72gtoq?gbRFZ*#0RYcNLCpXF diff --git a/model/db_generator.py b/model/db_generator.py index f50bdcf..aab7b41 100644 --- a/model/db_generator.py +++ b/model/db_generator.py @@ -1,6 +1,7 @@ # -*- coding: utf-8 -*- # databaseの生成用スクリプト +# 2013/12/04 written by kei9 import sqlite3 import csv @@ -14,14 +15,15 @@ import cStringIO 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がある場合は上書きする """ @@ -41,6 +43,7 @@ class DataBaseGenerator(object): # 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() @@ -87,6 +90,24 @@ class DataBaseGenerator(object): 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" @@ -103,8 +124,25 @@ class DataBaseGenerator(object): 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() @@ -124,8 +162,10 @@ class DataBaseGenerator(object): 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 @@ -208,8 +248,10 @@ class DataBaseGenerator(object): 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() @@ -268,8 +310,10 @@ class DataBaseGenerator(object): 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() diff --git a/model/db_supports.py b/model/db_supports.py index cbc6d57..49416f1 100644 --- a/model/db_supports.py +++ b/model/db_supports.py @@ -2,7 +2,7 @@ # constant numbers of database # データベース関連の定数値など -# 2013/12/03 written by kei9 +# 2013/12/04 written by kei9 # import modules import os.path @@ -13,6 +13,7 @@ ZIP_FILE = "data.zip" 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" @@ -37,10 +38,28 @@ SKILL_TABLE_SKILL2ID_SQL = u"""select id from {table} where {skill_col}='{{skill 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" @@ -48,9 +67,11 @@ MIN_MAX_COL_MAX1 = u"max1" 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, @@ -63,10 +84,10 @@ MIN_MAX_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 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, @@ -79,7 +100,7 @@ MIN_MAX_TABLE_INSERT_SQL = u"""insert into {{table_name}} 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" @@ -89,6 +110,7 @@ SECOND_COL_SKILL_ID4 = u"skill_id4" 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" @@ -96,10 +118,13 @@ SECOND_COL_SLOT4 = u"slot4" 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, @@ -138,7 +163,7 @@ SECOND_MASTER_TABLE_INSERT_SQL = u"""insert into {table} ({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( @@ -166,16 +191,18 @@ SECOND_SLOT_TABLE_INSERT_SQL = u"""insert into {table} # 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, @@ -188,7 +215,7 @@ SUFFICIENT_MASTER_TABLE_INSERT_SQL = u"""insert into {table} ({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( -- 2.11.0