From: kei9 Date: Wed, 4 Dec 2013 10:41:28 +0000 (+0900) Subject: add support functions to db accessor X-Git-Tag: 0.1.2~2^2~6 X-Git-Url: http://git.sourceforge.jp/view?p=amulettoolsmh4%2Fmain.git;a=commitdiff_plain;h=932f259dcede6dce5ed72426b53456cc07c3bcfe add support functions to db accessor --- diff --git a/.gitignore b/.gitignore index 26860e5..302010d 100644 --- a/.gitignore +++ b/.gitignore @@ -1,3 +1,4 @@ *.pyc *.swp *.sqlite3 +*.bak diff --git a/amulettool.py b/amulettool.py new file mode 100644 index 0000000..ae047f4 --- /dev/null +++ b/amulettool.py @@ -0,0 +1,4 @@ +# -*- coding: utf-8 -*- + +# ‚¨Žç‚èƒXƒLƒ‹‚ÌSeed“Á’èƒNƒ‰ƒX +# 2013/12/04 written by kei9 diff --git a/model/__init__.py b/model/__init__.py index 3637ed8..3e3d64e 100644 --- a/model/__init__.py +++ b/model/__init__.py @@ -1,6 +1,8 @@ # -*- coding: utf-8 -*- # modelƒ‚ƒWƒ…[ƒ‹‚Ìimport—pƒXƒNƒŠƒvƒg +# 2013/12/04 written by kei9 from db_accessor import DataBaseAccessor from db_generator import DataBaseGenerator +from db_supports import DB_FILE_NAME diff --git a/model/amuletskillsholder.py b/model/amuletskillsholder.py index b334fd3..355f5dc 100644 --- a/model/amuletskillsholder.py +++ b/model/amuletskillsholder.py @@ -33,3 +33,19 @@ class AmuletSkillsHolder(object): return u" where " + u" and ".join(valid_eqls) else: return "" + + def get_where_in_sql(self, remove_where=False): + u""" SQLのWhere文に変換する(IN結合) """ + if len(self.skill_id_list) >= 1: + uni_list = [u"{0}".format(x) for x in self.skill_id_list if x is not None] + sql = u" {col} IN({in_phrase})".format( + col=db_supports.MIN_MAX_COL_SKILL_ID, + in_phrase=u",".join(uni_list)) + + if remove_where: + return sql + else: + return u" where" + sql + else: + return "" + diff --git a/model/db_accessor.py b/model/db_accessor.py index 93c8985..a44ffb9 100644 --- a/model/db_accessor.py +++ b/model/db_accessor.py @@ -8,6 +8,7 @@ import sqlite3 import db_supports from amuletskillsholder import AmuletSkillsHolder from randomseedsholder import RandomSeedsHolder +from sufficientsholder import SufficientsHolder class DataBaseAccessor(object): u""" this is access class to database """ @@ -129,6 +130,36 @@ class DataBaseAccessor(object): else: return seed_set + def select_minmax_from_skill_ids(self, amulet_id, skill_ids): + u""" お守りのidとスキルidのリストから、スキルIDと取りうるスキルの最大最小値の辞書を得る + return dict{skill_id:(min1, max1, min2, max2)} + """ + minmax_table = self._amulet_id2minmax_dict[amulet_id] + amulet_skill = AmuletSkillsHolder(skill_ids) + result_dict = {} + if amulet_skill.is_empty() is False: + sql = db_supports.MIN_MAX_TABLE_SELECT_ALL_SQL.format(table_name=minmax_table) + amulet_skill.get_where_in_sql() + self._cursor.execute(sql) + + for row in self._cursor.fetchall(): + result_dict[row[0]] = tuple(row[1]) + return result_dict + + def select_threshold_from_sufficient(self, amulet_id, sufficient_vals): + u""" お守りのidと充足値のリストから、充足値とスロットごとの判定値の辞書を得る + return dict({sufficient_val:(slot1_th, slot2_th, slot3_th)}) + """ + suff_table = self._amulet_id2suff_dict[amulet_id] + amulet_skill = SufficientsHolder(sufficient_vals) + result_dict = {} + if amulet_skill.is_empty() is False: + sql = db_supports.SUFFICIENT_TABLE_SELECT_ALL_SQL.format(table_name=suff_table) + amulet_skill.get_where_in_sql() + self._cursor.execute(sql) + + for row in self._cursor.fetchall(): + result_dict[row[0]] = tuple(row[1]) + return result_dict + def select_skills_from_seeds(self, seed_set): u""" get amulet id to (random_seed to skill ids dict) dict, and rendom_seed to slot value dict diff --git a/model/db_generator.py b/model/db_generator.py index aab7b41..a0973b4 100644 --- a/model/db_generator.py +++ b/model/db_generator.py @@ -336,7 +336,7 @@ class DataBaseGenerator(object): self._connect.commit() if __name__ == "__main__": - #db = DataBaseGenerator(OutputDBFileName) + #db = DataBaseGenerator(DB_FILE_NAME) db = DataBaseGenerator("test.sqlite3") #db = DataBaseGenerator() # for memory db.generate_db() diff --git a/model/db_supports.py b/model/db_supports.py index 0bf28c1..e86167e 100644 --- a/model/db_supports.py +++ b/model/db_supports.py @@ -10,8 +10,7 @@ import codecs # --- filenames --- ZIP_FILE = "data.zip" -DATA_DIRECTORY = "data" -OUTPUT_DB_FILE_NAME = "OmamoriMH4.sqlite3" +DB_FILE_NAME = "OmamoriMH4.sqlite3" SKILL_FILE_NAME = "skill.csv" AMULET_FILE_NAME = "amulet.csv" MIN_MAX_FILE_NAME = "minmax.csv" diff --git a/model/randomseedsholder.py b/model/randomseedsholder.py index 0915897..54be9e5 100644 --- a/model/randomseedsholder.py +++ b/model/randomseedsholder.py @@ -16,14 +16,14 @@ class RandomSeedsHolder(object): u""" SQLのWhere文に変換する(IN句) """ if len(self.seed_set) >= 1: uni_list = [u"{0}".format(x) for x in self.seed_set] - sql = u"{col} IN({in_phrase})".format( + sql = u" {col} IN({in_phrase})".format( col=db_supports.SECOND_COL_RANDOM_SEED, in_phrase=u",".join(uni_list)) if remove_where: return sql else: - return u" where " + sql + return u" where" + sql else: return "" diff --git a/model/sufficientsholder.py b/model/sufficientsholder.py new file mode 100644 index 0000000..c665894 --- /dev/null +++ b/model/sufficientsholder.py @@ -0,0 +1,30 @@ +# -*- coding: utf-8 -*- + +# sufficient valueを保持するクラス +# 2013/12/04 written by kei9 + +import db_supports + +class SufficientsHolder(object): + u""" ランダムのSeed値を保持するクラス """ + def __init__(self, sufficient_list): + u""" sufficient_list: list of sufficient + """ + self.sufficient_set = set(sufficient_list) + + def get_where_sql(self, remove_where=False): + u""" SQLのWhere文に変換する(IN句) """ + if len(self.sufficient_set) >= 1: + uni_list = [u"{0}".format(x) for x in self.sufficient_set] + sql = u" {col} IN({in_phrase})".format( + col=db_supports.SUFFICIENT_COL_SUFFICIENT_VALUE, + in_phrase=u",".join(uni_list)) + + if remove_where: + return sql + else: + return u" where" + sql + else: + return "" + +