model/seed2inishietable.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 skilltable
  12
  13 # seed2 -> inishie table
  14 u""" 古の錬金テーブル """
  15 NAME = u"seed2_inishie"
  16 COL_SEED2 = u"seed2"
  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(
  24         table_name=NAME,
  25         seed=COL_SEED2,
  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(
  32         table_name=NAME,
  33         seed=COL_SEED2,
  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(
  39         table_name=NAME,
  40         seed2=COL_SEED2,
  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(
  47         table_name=NAME,
  48         seed2=COL_SEED2,
  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(
  54         table_name=NAME,
  55         seed2=COL_SEED2,
  56         threshold1=COL_THRESHOLD1,
  57         threshold2=COL_THRESHOLD2)
  58
  59 class Seed2InishieTableGenerator(object):
  60     u""" Seed2とテーブルNo.&通し番号の組み合わせテーブルの作成するクラス """
  61     def __init__(self):
  62         u""" コンストラクタ """
  63         pass
  64
  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()
  70
  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             #if not isinstance(skill_name, unicode):
  75             #    skill_name = unicode(skill_name, u"utf-8")
  76             skill_id = skill_name2id[skill_name]
  77             seed2, th1, th2 = int(row[0].strip()), int(row[2].strip()), int(row[3].strip())
  78             db_cursor.execute(INSERT_SQL, (seed2, skill_id, th1, th2))
  79
  80
  81 class Seed2InishieTableAccessor(object):
  82     u"""Seed2と古の錬金結果の組合わせテーブルへのアクセスをするクラス"""
  83     def __init__(self, db_cursor):
  84         u""" db_cursor: cursor of sqlite3 database """
  85         self._cursor = db_cursor
  86         accessor = skilltable.SkillTableAccessor(db_cursor)
  87         self._skill_id2name, self._skill_name2id = accessor.get_dict()
  88
  89     def select_ids_from_seed2(self, seed2):
  90         u""" Seed2に対応するスキルId、判定値1, 判定値2を返す """
  91         sql = SELECT_FROM_SEED2_SQL.format(seed2=seed2)
  92         self._cursor.execute(sql)
  93         skill_id, th1, th2 = self._cursor.fetchone()
  94         return (skill_id, th1, th2)
  95
  96     def select_names_from_seed2(self, seed2):
  97         u""" Seed2に対応するスキル名、判定値1, 判定値2を返す """
  98         sql = SELECT_FROM_SEED2_SQL.format(seed2=seed2)
  99         self._cursor.execute(sql)
 100         skill_id, th1, th2 = self._cursor.fetchone()
 101         return (self._skill_id2name[skill_id], th1, th2)
 102
 103