--- /dev/null
+# -*- 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()
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"
(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)
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"
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"
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"
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)
OSDN Git Service
