1 # -*- coding: utf-8 -*-
4 Seed2と古の錬金結果の組合わせテーブルの作成・アクセスをするモジュール
5 2013/12/19 written by kei9
13 # seed2 -> inishie table
15 NAME = u"seed2_inishie"
17 COL_SKILL2_ID = u"skill2_id"
18 COL_THRESHOLD1 = u"treshold1"
19 COL_THRESHOLD2 = u"treshold2"
20 CREATE_SQL = u"""create table if not exists {table_name}
21 (id integer primary key, {seed} integer unique,
22 {skill_id} integer, {threshold1} integer, {threshold2} integer,
23 foreign key({skill_id}) references {skill_table}(id));""".format(
26 skill_id=COL_SKILL2_ID,
27 threshold1=COL_THRESHOLD1,
28 threshold2=COL_THRESHOLD2,
29 skill_table=skilltable.NAME)
30 INSERT_SQL = u"""insert into {table_name}
31 ({seed}, {skill_id}, {threshold1}, {threshold2}) values(?,?,?,?)""".format(
34 threshold1=COL_THRESHOLD1,
35 threshold2=COL_THRESHOLD2,
36 skill_id=COL_SKILL2_ID)
37 SELECT_FROM_SEED2_SQL = u"""select {skill_id}, {threshold1}, {threshold2}
38 from {table_name} where {seed2}={{seed2}}""".format(
41 threshold1=COL_THRESHOLD1,
42 threshold2=COL_THRESHOLD2,
43 skill_id=COL_SKILL2_ID)
44 SELECT_SEED2_FROM_SKILL2_THRESHOLD_SQL = u"""select {seed2}
45 from {table_name} where {skill_id}={{skill2_id}} and
46 ({threshold1}>={{threshold}} or {threshold2}>={{threshold}})""".format(
49 threshold1=COL_THRESHOLD1,
50 threshold2=COL_THRESHOLD2,
51 skill_id=COL_SKILL2_ID)
52 SELECT_SEED2_FROM_THRESHOLD_SQL = u"""select {seed2}
53 from {table_name} where {threshold1}>={{threshold}} or {threshold2}>={{threshold}}""".format(
56 threshold1=COL_THRESHOLD1,
57 threshold2=COL_THRESHOLD2)
59 class Seed2InishieTableGenerator(object):
60 u""" Seed2とテーブルNo.&通し番号の組み合わせテーブルの作成するクラス """
65 def insert_data(self, db_cursor, csv_reader):
66 u""" csv_readerからデータを読み込み、 db_cursorへデータを挿入する。"""
67 db_cursor.execute(CREATE_SQL)
68 accessor = skilltable.SkillTableAccessor(db_cursor)
69 skill_id2name, skill_name2id = accessor.get_dict()
71 csv_reader.next() # skip header row
72 for row in csv_reader: # (seed2, skill_name, th1, th2)
73 skill_name = row[1].strip()
74 skill_id = skill_name2id[skill_name]
75 seed2, th1, th2 = int(row[0].strip()), int(row[2].strip()), int(row[3].strip())
76 db_cursor.execute(INSERT_SQL, (seed2, skill_id, th1, th2))
79 class Seed2InishieTableAccessor(object):
80 u"""Seed2と古の錬金結果の組合わせテーブルへのアクセスをするクラス"""
81 def __init__(self, db_cursor):
82 u""" db_cursor: cursor of sqlite3 database """
83 self._cursor = db_cursor
84 accessor = skilltable.SkillTableAccessor(db_cursor)
85 self._skill_id2name, self._skill_name2id = accessor.get_dict()
87 def select_ids_from_seed2(self, seed2):
88 u""" Seed2に対応するスキルId、判定値1, 判定値2を返す """
89 sql = SELECT_FROM_SEED2_SQL.format(seed2=seed2)
90 self._cursor.execute(sql)
91 skill_id, th1, th2 = self._cursor.fetchone()
92 return (skill_id, th1, th2)
94 def select_names_from_seed2(self, seed2):
95 u""" Seed2に対応するスキル名、判定値1, 判定値2を返す """
96 sql = SELECT_FROM_SEED2_SQL.format(seed2=seed2)
97 self._cursor.execute(sql)
98 skill_id, th1, th2 = self._cursor.fetchone()
99 return (self._skill_id2name[skill_id], th1, th2)