OSDN Git Service

marshal database generator & accessor
[amulettoolsmh4/main.git] / model / seed2skill2table.py
1 # -*- coding: utf-8 -*-
2
3 u"""
4 SEED2とスキル名の組み合わせテーブルの作成・アクセスをするモジュール
5 2013/12/19 written by kei9
6 """
7
8 import sqlite3
9 import csv
10
11 import amulettable
12 import skilltable
13
14 # for Seed2 to skill2
15 u""" SEED2から各お守りの第2スキルへのテーブル """
16 MASTER_NAME = u"seed2_skill2_master"
17 NAME = u"seed2_skill2_{amulet_id}"
18 COL_AMULET_ID = u"amulet_id"
19 COL_SEED2_SKILL2_TABLE_NAME = u"seed2_skill2_table_name"
20 COL_SEED2 = u"seed2"
21 COL_SKILL2_ID1 = u"skill2_id1"
22 COL_SKILL2_ID2 = u"skill2_id2"
23 COL_SKILL2_ID3 = u"skill2_id3"
24 COL_SKILL2_ID4 = u"skill2_id4"
25 COL_SKILL2_ID5 = u"skill2_id5"
26 COL_SKILL2_ID6 = u"skill2_id6"
27 COL_SKILL2_ID7 = u"skill2_id7"
28 COL_SKILL2_LIST = [COL_SKILL2_ID1, COL_SKILL2_ID2, COL_SKILL2_ID3, COL_SKILL2_ID4, COL_SKILL2_ID5, COL_SKILL2_ID6, COL_SKILL2_ID7]
29 CREATE_MASTER_SQL = u"""create table if not exists {master}
30     (id integer primary key, {amulet_id} integer, {table_name} varchar,
31     foreign key(amulet_id) references {amulet_table}(id));""".format(
32         master=MASTER_NAME,
33         amulet_id=COL_AMULET_ID,
34         amulet_table=amulettable.NAME,
35         table_name=COL_SEED2_SKILL2_TABLE_NAME)
36 CREATE_SQL = u"""create table if not exists {{table_name}} 
37     (id integer primary key, {seed} integer unique, 
38     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer, 
39     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer, 
40     foreign key({skill_id1}) references {skill_table}(id),
41     foreign key({skill_id2}) references {skill_table}(id),
42     foreign key({skill_id3}) references {skill_table}(id),
43     foreign key({skill_id4}) references {skill_table}(id),
44     foreign key({skill_id5}) references {skill_table}(id),
45     foreign key({skill_id6}) references {skill_table}(id),
46     foreign key({skill_id7}) references {skill_table}(id));""".format(
47         seed=COL_SEED2,
48         skill_id1=COL_SKILL2_ID1,
49         skill_id2=COL_SKILL2_ID2,
50         skill_id3=COL_SKILL2_ID3,
51         skill_id4=COL_SKILL2_ID4,
52         skill_id5=COL_SKILL2_ID5,
53         skill_id6=COL_SKILL2_ID6,
54         skill_id7=COL_SKILL2_ID7,
55         skill_table=skilltable.NAME)
56 INSERT_MASTER_SQL = u"""insert into {table}
57     ({amulet_col}, {table_col}) values(?,?);""".format(
58         table=MASTER_NAME,
59         table_col=COL_SEED2_SKILL2_TABLE_NAME,
60         amulet_col=COL_AMULET_ID)
61 INSERT_SQL = u"""insert into {{table_name}}
62     ({seed}, {skill_id1}, {skill_id2}, {skill_id3}, 
63     {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) values(?,?,?,?,?,?,?,?)""".format(
64         seed=COL_SEED2,
65         skill_id1=COL_SKILL2_ID1,
66         skill_id2=COL_SKILL2_ID2,
67         skill_id3=COL_SKILL2_ID3,
68         skill_id4=COL_SKILL2_ID4,
69         skill_id5=COL_SKILL2_ID5,
70         skill_id6=COL_SKILL2_ID6,
71         skill_id7=COL_SKILL2_ID7)
72 SELECT_MASTER_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
73         amu_id=COL_AMULET_ID,
74         table_col=COL_SEED2_SKILL2_TABLE_NAME,
75         table=MASTER_NAME)
76 SELECT_SEED2_SQL = u"""select {seed} from {{table_name}} """.format(
77         seed=COL_SEED2)
78 SELECT_ALL_SQL = u"""select {seed}, {skill_id1}, {skill_id2}, 
79     {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} """.format(
80         seed=COL_SEED2,
81         skill_id1=COL_SKILL2_ID1,
82         skill_id2=COL_SKILL2_ID2,
83         skill_id3=COL_SKILL2_ID3,
84         skill_id4=COL_SKILL2_ID4,
85         skill_id5=COL_SKILL2_ID5,
86         skill_id6=COL_SKILL2_ID6,
87         skill_id7=COL_SKILL2_ID7)
88 SELECT_ALL_FROM_SEED2_SQL = u"""select {skill_id1}, {skill_id2}, 
89     {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} where {seed}={{seed2}}""".format(
90         seed=COL_SEED2,
91         skill_id1=COL_SKILL2_ID1,
92         skill_id2=COL_SKILL2_ID2,
93         skill_id3=COL_SKILL2_ID3,
94         skill_id4=COL_SKILL2_ID4,
95         skill_id5=COL_SKILL2_ID5,
96         skill_id6=COL_SKILL2_ID6,
97         skill_id7=COL_SKILL2_ID7)
98
99 class Seed2Skill2TableGenerator(object):
100     u""" Seed2とスキル名の組み合わせテーブルを作成するクラス """
101     def __init__(self):
102         u""" コンストラクタ """
103         self._amulet_id2skill2_filename_dict = {}
104
105     def insert_master_data(self, db_cursor, csv_reader):
106         u""" マスターテーブルを作成する。
107         """
108         db_cursor.execute(CREATE_MASTER_SQL)
109         accessor = amulettable.AmuletTableAccessor(db_cursor)
110         amu_id2name, amu_name2id = accessor.get_dict()
111
112         csv_reader.next()   # skip header row
113         for row in csv_reader:
114             amulet_name = row[0].strip()
115             skill2_file = row[1].strip()
116             amulet_id = amu_name2id[amulet_name]
117             table_name = NAME.format(amulet_id=amulet_id)
118             self._amulet_id2skill2_filename_dict[amulet_id] = skill2_file
119             db_cursor.execute(INSERT_MASTER_SQL, (amulet_id, table_name))
120
121     def get_skill_filenames(self):
122         u""" 出現するスキル2が記載されたファイル名をお守りIDに関連付けて返す
123         return {amulet_id: skill2_filename}"""
124         result_dict = {}
125         for amu_id in self._amulet_id2skill2_filename_dict.keys():
126             result_dict[amu_id] = self._amulet_id2skill2_filename_dict[amu_id]
127         return result_dict
128
129     def insert_data(self, db_cursor, amulet_id2csv_reader_dict):
130         u""" お守りIDと関連付けられたcsv_reader(skill2)からデータを読み込み、
131         db_cursorへデータを挿入する。
132         ammulet_id2csv_readers_dict:{amulet_id:csv_reader_skill2}"""
133         accessor = skilltable.SkillTableAccessor(db_cursor)
134         skill_id2name, skill_name2id = accessor.get_dict()
135
136         for amulet_id, reader in amulet_id2csv_reader_dict.items():
137             table_name = NAME.format(amulet_id=amulet_id)
138             db_cursor.execute(CREATE_SQL.format(table_name=table_name))
139             insert_values = {}
140
141             reader.next()   # skip header row
142             for row in reader:  # (skill_name, min1, max1)
143                 seed2 = int(row[0].strip())
144                 skill_ids = [skill_name2id[x.strip()] for x in row[1:]]
145
146                 sql = INSERT_SQL.format(table_name=table_name)
147                 db_cursor.execute(sql, tuple([seed2] + skill_ids))
148
149 class Seed2Skill2TableAccessor(object):
150     u""" Seed2とスキル名の組み合わせテーブルへのアクセス用クラス """
151     def __init__(self, db_cursor):
152         u""" db_cursor: cursor of sqlite3 database """
153         self._cursor = db_cursor
154         amu_accessor = amulettable.AmuletTableAccessor(db_cursor)
155         skill_accessor = skilltable.SkillTableAccessor(db_cursor)
156         self._amu_id2name, self._amu_name2id = amu_accessor.get_dict()
157         self._skill_id2name, self._skill_name2id = skill_accessor.get_dict()
158         self._amu_id2table_name = {}
159
160         self._cursor.execute(SELECT_MASTER_ALL_SQL)
161         for row in self._cursor.fetchall():
162             amu_id, table_name = row
163             self._amu_id2table_name[amu_id] = table_name
164
165     def select_seed2s_by_ids(self, amu_id2skill_ids):
166         u""" お守りIdとスキルIdのリストの辞書からSeed2を特定する 
167         skill_ids: (skill_id1, skill_id2, ...., skill_id7)
168         不明であればNoneあるいはNO_DATAを入れておくものとする。
169         """
170         result_set = set()
171         for amulet_id, skill_ids in amu_id2skill_ids.items():
172             table_name = self._amu_id2table_name[amulet_id]
173             where_list = ["{0}={1}".format(col, skill_id) 
174                     for col, skill_id in zip(COL_SKILL2_LIST, skill_ids) 
175                     if skill_id in self._skill_id2name]
176             if len(where_list) == 0:
177                 continue
178
179             where_sql = " where " + " and ".join(where_list)
180             sql = SELECT_SEED2_SQL.format(table_name=table_name) + where_sql
181             self._cursor.execute(sql)
182             if len(result_set) == 0:
183                 result_set = set([x[0] for x in self._cursor.fetchall()])
184             else:
185                 result_set = result_set & set([x[0] for x in self._cursor.fetchall()])
186
187         return result_set
188
189     def select_seed2s_by_name(self, amu_name2skill_names):
190         u""" お守り名とスキル名のリストの辞書からSeed2を特定する 
191         skill_names: (skill_name1, skill_name2, ...., skill_name7)
192         不明であればNoneあるいはNO_DATAを入れておくものとする。
193         """
194         amu_id2skill_ids = {}
195         for amu_name, skill_names in amu_name2skill_names.items():
196             if amu_name in self._amu_name2id:
197                 amu_id = self._amu_name2id[amu_name]
198             else:
199                 print u"{0} is not found!".format(amu_name)
200                 continue
201             skill_ids = [self._skill_name2id[name] if name in self._skill_name2id else None
202                     for name in skill_names]
203             amu_id2skill_ids[amu_id] = skill_ids
204
205         if len(amu_id2skill_ids) > 0:
206             return self.select_seed2s_by_ids(amu_id2skill_ids)
207         else:
208             return set()
209
210     def select_skill_ids_by_seed2(self, seed2):
211         u""" seed2から対応するお守りIdごとの第2スキルId(1-7枠)を返す
212         return {amulet_id:(skill2_id1, skill2_id2, ..., skill2_id7)}"""
213         result_dict = {}
214         for amu_id, table_name in self._amu_id2table_name.items():
215             sql = SELECT_ALL_FROM_SEED2_SQL.format(table_name=table_name, seed2=seed2)
216             self._cursor.execute(sql)
217             id1, id2, id3, id4, id5, id6, id7 = self._cursor.fetchone()
218             result_dict[amu_id] = (id1, id2, id3, id4, id5, id6, id7)
219         return result_dict
220
221     def select_skill_names_by_seed2(self, seed2):
222         u""" seed2から対応するお守り名ごとの第2スキル名(1-7枠)を返す
223         return {amulet_name:(skill2_name1, skill2_name2, ..., skill2_name7)}"""
224         result_dict = {}
225         for amu_id, table_name in self._amu_id2table_name.items():
226             sql = SELECT_ALL_FROM_SEED2_SQL.format(table_name=table_name, seed2=seed2)
227             self._cursor.execute(sql)
228             names = [self._skill_id2name[x] for x in self._cursor.fetchone()]
229             result_dict[self._amu_id2name[amu_id]] = tuple(names)
230         return result_dict