From d8a75119f2e0478572da3f59d7b2ddb731da7fba Mon Sep 17 00:00:00 2001 From: kei9 Date: Wed, 4 Dec 2013 18:30:50 +0900 Subject: [PATCH] create database accessor and its support class --- empty_develop | 0 initial | 1 - model/__init__.py | 6 ++ model/amuletskillsholder.py | 35 +++++++++ model/db_accessor.py | 179 ++++++++++++++++++++++++++++++++++++++++++++ model/db_supports.py | 56 +++++++++++++- model/randomseedsholder.py | 30 ++++++++ 7 files changed, 304 insertions(+), 3 deletions(-) delete mode 100644 empty_develop delete mode 100644 initial create mode 100644 model/__init__.py create mode 100644 model/amuletskillsholder.py create mode 100644 model/db_accessor.py create mode 100644 model/randomseedsholder.py diff --git a/empty_develop b/empty_develop deleted file mode 100644 index e69de29..0000000 diff --git a/initial b/initial deleted file mode 100644 index c6cac69..0000000 --- a/initial +++ /dev/null @@ -1 +0,0 @@ -empty diff --git a/model/__init__.py b/model/__init__.py new file mode 100644 index 0000000..3637ed8 --- /dev/null +++ b/model/__init__.py @@ -0,0 +1,6 @@ +# -*- coding: utf-8 -*- + +# modelƒ‚ƒWƒ…[ƒ‹‚Ìimport—pƒXƒNƒŠƒvƒg + +from db_accessor import DataBaseAccessor +from db_generator import DataBaseGenerator diff --git a/model/amuletskillsholder.py b/model/amuletskillsholder.py new file mode 100644 index 0000000..b334fd3 --- /dev/null +++ b/model/amuletskillsholder.py @@ -0,0 +1,35 @@ +# -*- coding: utf-8 -*- + +# お守りのスキル保持用クラス +# 2013/12/04 written by kei9 + +import db_supports + +class AmuletSkillsHolder(object): + u""" お守りのスキルを保持するクラス """ + def __init__(self, skill_id_list): + u""" skill_id_list: list of skill id + if skill is unknown, element must be None + """ + self.skill_id_list = list(skill_id_list) + + def is_empty(self): + u""" すべての要素がNoneのときTrueを返す """ + if all([x is None for x in self.skill_id_list]): + return True + else: + return False + + def get_where_sql(self, remove_where=False): + u""" SQLのWhere文に変換する(AND結合) """ + if any([x is not None for x in self.skill_id_list]): + valid_eqls = [] + for col, skill_id in zip(db_supports.SECOND_COL_SKILL_LIST, self.skill_id_list): + if skill_id is not None: + valid_eqls.append(u"{col}={skill_id}".format(col=col,skill_id=skill_id)) + if remove_where: + return u" and ".join(valid_eqls) + else: + return u" where " + u" and ".join(valid_eqls) + else: + return "" diff --git a/model/db_accessor.py b/model/db_accessor.py new file mode 100644 index 0000000..93c8985 --- /dev/null +++ b/model/db_accessor.py @@ -0,0 +1,179 @@ +# -*- coding: utf-8 -*- + +# database のアクセス用スクリプト +# 2013/12/04 written by kei9 + +import sqlite3 + +import db_supports +from amuletskillsholder import AmuletSkillsHolder +from randomseedsholder import RandomSeedsHolder + +class DataBaseAccessor(object): + u""" this is access class to database """ + def __init__(self, db_name): + u""" db_name is database name to access """ + self._db_name = db_name + self._connect = sqlite3.connect(self._db_name) + self._cursor = self._connect.cursor() + + self._skill_id2name_dict, self._skill_name2id_dict = self._get_skill_dict() + self._amulet_id2name_dict, self._amulet_name2id_dict, self._amulet_id2minmax_dict, self._amulet_id2second_dict, self._amulet_id2suff_dict = self._get_amulet_dict() + + def _print_dicts(self): + u""" for debug """ + print self._skill_id2name_dict + print self._skill_name2id_dict + print self._amulet_id2name_dict + print self._amulet_name2id_dict + print self._amulet_id2minmax_dict + print self._amulet_id2second_dict + print self._amulet_id2suff_dict + + def get_dicts(self, clone_enable=False): + u""" get all dicts + if clone_enable is True, return cloend dictionary, + otherwise return reference dictionary + + returns (skill_id2name_dict, skill_name2id_dict, + amulet_id2name_dict, amulet_name2id_dict, + amulet_id2minmax_dict, amulet_id2second_dict, + amulet_id2stuff_dict) + """ + if clone_enable: + return (dict(self._skill_id2name_dict), + dict(self._skill_name2id_dict), + dict(self._amulet_id2name_dict), + dict(self._amulet_name2id_dict), + dict(self._amulet_id2minmax_dict), + dict(self._amulet_id2second_dict), + dict(self._amulet_id2stuff_dict)) + else: + return (self._skill_id2name_dict, + self._skill_name2id_dict, + self._amulet_id2name_dict, + self._amulet_name2id_dict, + self._amulet_id2minmax_dict, + self._amulet_id2second_dict, + self._amulet_id2suff_dict) + + + def _get_skill_dict(self): + u""" create id2name, name2id dict of skill + return (dict_id2name, dict_name2id) + """ + skill_id2name_dict, skill_name2id_dict = {}, {} + self._cursor.execute(db_supports.SKILL_TABLE_SELECT_ALL_SQL) + for val in self._cursor.fetchall(): + skill_id, name = val[0], val[1] + skill_id2name_dict[skill_id] = name + skill_name2id_dict[name] = skill_id + + return skill_id2name_dict, skill_name2id_dict + + def _get_amulet_dict(self): + u""" create id2name, name2id, id2minmax, id2second, id2suff dict of amulet + return (dict_id2name, dict_name2id, id2minmax, id2second, id2suff) + """ + amulet_id2name_dict, amulet_name2id_dict = {}, {} + id2minmax, id2second, id2suff = {}, {}, {} + self._cursor.execute(db_supports.AMULET_TABLE_SELECT_ALL_SQL) + for val in self._cursor.fetchall(): + amulet_id, name = val[0], val[1] + amulet_id2name_dict[amulet_id] = name + amulet_name2id_dict[name] = amulet_id + + # id 2 minmax table name + self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL) + for val in self._cursor.fetchall(): + amulet_id, table = val[0], val[1] + id2minmax[amulet_id] = table + + # id 2 second table name + self._cursor.execute(db_supports.SECOND_MASTER_TABLE_SELECT_ALL_SQL) + for val in self._cursor.fetchall(): + amulet_id, table = val[0], val[1] + id2second[amulet_id] = table + + # id 2 sufficient table name + self._cursor.execute(db_supports.MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL) + for val in self._cursor.fetchall(): + amulet_id, table = val[0], val[1] + id2suff[amulet_id] = table + + return amulet_id2name_dict, amulet_name2id_dict, id2minmax, id2second, id2suff + + def select_seeds(self, amu_id2skill_id_list_dict): + u""" from dict of amulet_id to skill_id_list, + skill_id_list: list of skill_id, if skill_id is unknown, skill_id must be None + """ + seed_set = None + for key, val in amu_id2skill_id_list_dict.items(): + if key in self._amulet_id2second_dict: + second_skill_table = self._amulet_id2second_dict[key] + amulet_skill = AmuletSkillsHolder(val) + + if amulet_skill.is_empty(): + continue + + sql = db_supports.SECOND_TABLE_SELECT_SEED_SQL.format(table_name=second_skill_table) + amulet_skill.get_where_sql() + self._cursor.execute(sql) + + if seed_set is None: + seed_set = set([x[0] for x in self._cursor.fetchall()]) + else: + seed_set = seed_set.intersection(set([x[0] for x in self._cursor.fetchall()])) + + if seed_set is None: + return set() + else: + return seed_set + + 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 + return: skill_dict, slot_dict + """ + skill_dict, slot_dict = {}, {} + + # skill dict + seeds = RandomSeedsHolder(seed_set) + for amu_id, table in self._amulet_id2second_dict.items(): + sql = db_supports.SECOND_TABLE_SELECT_ALL_SQL.format( + table_name=table) + seeds.get_where_sql() + self._cursor.execute(sql) + seed2skills_dic = {} + for row in self._cursor.fetchall(): + seed2skills_dic[row[0]] = [x for x in row[1:]] + skill_dict[amu_id] = seed2skills_dic + + # slot dict + sql = db_supports.SECOND_SLOT_TABLE_SELECT_ALL_SQL + seeds.get_where_sql() + self._cursor.execute(sql) + for row in self._cursor.fetchall(): + slot_dict[row[0]] = [x for x in row[1:]] + + return skill_dict, slot_dict + + + def close(self): + u""" close database accessor """ + self._cursor.close() + self._connect.close() + +if __name__ == "__main__": + db = DataBaseAccessor("test.sqlite3") + #db._print_dicts() + hikari = [None, 57, None, None, None, None, None] + huru = [54, None, None, None, None, None, None] + #yuga = [None, None, 98, 75, None, None, None] + yuga = [None, None, 98, None, None, None, None] + dic = {2:hikari, 3:huru, 4:yuga} + #seeds = db.select_seeds(dic) + seeds = set([58241, 176]) + skill_dic, slot_dic = db.select_skills_from_seeds(seeds) + + print "seeds: ", seeds + print "amu_id to seed2skill dict: ", skill_dic + print "seed2slot dict", slot_dic + db.close() diff --git a/model/db_supports.py b/model/db_supports.py index 49416f1..0bf28c1 100644 --- a/model/db_supports.py +++ b/model/db_supports.py @@ -37,6 +37,9 @@ SKILL_TABLE_ID2SKILL_SQL = u"""select {skill_col} from {table} where id='{{id}}' SKILL_TABLE_SKILL2ID_SQL = u"""select id from {table} where {skill_col}='{{skill_name}}'""".format( skill_col=SKILL_TABLE_COL_SKILL_NAME, table=SKILL_TABLE_NAME) +SKILL_TABLE_SELECT_ALL_SQL = u"""select id, {skill_col} from {table}""".format( + skill_col=SKILL_TABLE_COL_SKILL_NAME, + table=SKILL_TABLE_NAME) # for amulet table AMULET_TABLE_NAME = u"amulet_table" @@ -45,7 +48,7 @@ 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_TABLE_INSERT_SQL = u"""insert into {table} ({amulet_col}) values(?);""".format( table=AMULET_TABLE_NAME, amulet_col=AMULET_TABLE_COL_AMULET_NAME) @@ -55,6 +58,9 @@ AMULET_TABLE_ID2AMULET_SQL = u"""select {amulet_col} from {table} where id='{{id 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) +AMULET_TABLE_SELECT_ALL_SQL = u"""select id, {amulet_col} from {table}""".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" @@ -95,6 +101,17 @@ MIN_MAX_TABLE_INSERT_SQL = u"""insert into {{table_name}} min2=MIN_MAX_COL_MIN2, max1=MIN_MAX_COL_MAX1, max2=MIN_MAX_COL_MAX2) +MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format( + amu_id=MIN_MAX_COL_AMULET_ID, + table_col=MIN_MAX_COL_SKILL_TABLE_NAME, + table=MIN_MAX_MASTER_TABLE_NAME) +MIN_MAX_TABLE_SELECT_ALL_SQL = u"""select {skill_id}, {min1}, {max1}, {min2}, {max2} from {{table_name}} """.format( + skill_id=MIN_MAX_COL_SKILL_ID, + min1=MIN_MAX_COL_MIN1, + max1=MIN_MAX_COL_MAX1, + min2=MIN_MAX_COL_MIN2, + max2=MIN_MAX_COL_MAX2) + # for Second skill SECOND_MASTER_TABLE_NAME = u"skill_second_master" @@ -187,6 +204,33 @@ SECOND_SLOT_TABLE_INSERT_SQL = u"""insert into {table} slot5=SECOND_COL_SLOT5, slot6=SECOND_COL_SLOT6, slot7=SECOND_COL_SLOT7) +SECOND_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format( + amu_id=SECOND_COL_AMULET_ID, + table_col=SECOND_COL_SECOND_TABLE_NAME, + table=SECOND_MASTER_TABLE_NAME) +SECOND_TABLE_SELECT_SEED_SQL = u"""select {seed} from {{table_name}} """.format( + seed=SECOND_COL_RANDOM_SEED) +SECOND_TABLE_SELECT_ALL_SQL = u"""select {seed}, {skill_id1}, {skill_id2}, + {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} """.format( + seed=SECOND_COL_RANDOM_SEED, + skill_id1=SECOND_COL_SKILL_ID1, + skill_id2=SECOND_COL_SKILL_ID2, + skill_id3=SECOND_COL_SKILL_ID3, + skill_id4=SECOND_COL_SKILL_ID4, + skill_id5=SECOND_COL_SKILL_ID5, + skill_id6=SECOND_COL_SKILL_ID6, + skill_id7=SECOND_COL_SKILL_ID7) +SECOND_SLOT_TABLE_SELECT_ALL_SQL = u"""select {seed}, {slot1}, {slot2}, + {slot3}, {slot4}, {slot5}, {slot6}, {slot7} from {table} """.format( + table=SECOND_SLOT_TABLE_NAME, + seed=SECOND_COL_RANDOM_SEED, + slot1=SECOND_COL_SLOT1, + slot2=SECOND_COL_SLOT2, + slot3=SECOND_COL_SLOT3, + slot4=SECOND_COL_SLOT4, + slot5=SECOND_COL_SLOT5, + slot6=SECOND_COL_SLOT6, + slot7=SECOND_COL_SLOT7) # for sufficient value for slots SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master" @@ -223,4 +267,12 @@ SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}} slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD, slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD, slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD) - +SUFFICIENT_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format( + amu_id=SUFFICIENT_COL_AMULET_ID, + table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME, + table=SUFFICIENT_MASTER_TABLE_NAME) +SUFFICIENT_TABLE_SELECT_ALL_SQL = u"""select {sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}""".format( + sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE, + slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD, + slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD, + slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD) diff --git a/model/randomseedsholder.py b/model/randomseedsholder.py new file mode 100644 index 0000000..0915897 --- /dev/null +++ b/model/randomseedsholder.py @@ -0,0 +1,30 @@ +# -*- coding: utf-8 -*- + +# Random Seedを保持するクラス +# 2013/12/04 written by kei9 + +import db_supports + +class RandomSeedsHolder(object): + u""" ランダムのSeed値を保持するクラス """ + def __init__(self, seed_list): + u""" seed_list: list of seed + """ + self.seed_set = set(seed_list) + + def get_where_sql(self, remove_where=False): + 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( + col=db_supports.SECOND_COL_RANDOM_SEED, + in_phrase=u",".join(uni_list)) + + if remove_where: + return sql + else: + return u" where " + sql + else: + return "" + + -- 2.11.0