OSDN Git Service

create database accessor and its support class
authorkei9 <kei9@users.sourceforge.jp>
Wed, 4 Dec 2013 09:30:50 +0000 (18:30 +0900)
committerkei9 <kei9@users.sourceforge.jp>
Wed, 4 Dec 2013 09:30:50 +0000 (18:30 +0900)
empty_develop [deleted file]
initial [deleted file]
model/__init__.py [new file with mode: 0644]
model/amuletskillsholder.py [new file with mode: 0644]
model/db_accessor.py [new file with mode: 0644]
model/db_supports.py
model/randomseedsholder.py [new file with mode: 0644]

diff --git a/empty_develop b/empty_develop
deleted file mode 100644 (file)
index e69de29..0000000
diff --git a/initial b/initial
deleted file mode 100644 (file)
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 (file)
index 0000000..3637ed8
--- /dev/null
@@ -0,0 +1,6 @@
+# -*- coding: utf-8 -*-
+
+# model\83\82\83W\83\85\81[\83\8b\82Ìimport\97p\83X\83N\83\8a\83v\83g
+
+from db_accessor import DataBaseAccessor
+from db_generator import DataBaseGenerator
diff --git a/model/amuletskillsholder.py b/model/amuletskillsholder.py
new file mode 100644 (file)
index 0000000..b334fd3
--- /dev/null
@@ -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 (file)
index 0000000..93c8985
--- /dev/null
@@ -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()
index 49416f1..0bf28c1 100644 (file)
@@ -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_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"
 
 # 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)
     (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_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_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"
 
 # 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)
         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"
 
 # 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)
         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"
 
 # 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)
         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 (file)
index 0000000..0915897
--- /dev/null
@@ -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 ""
+
+