OSDN Git Service

rename database_ids & some variables in order to understand easily
[amulettoolsmh4/main.git] / model / db_supports.py
index 9e41cd2..ffb37ce 100644 (file)
@@ -2,20 +2,19 @@
 
 # constant numbers of database
 # データベース関連の定数値など
-# 2013/12/03 written by kei9 
+# 2013/12/09 written by kei9 
 
 # import modules
-import os.path
-import codecs
 
 # --- filenames ---
-DATA_DIRECTORY = "data"
-OUTPUT_DB_FILE_NAME = "OmamoriMH4.sqlite3"
+ZIP_FILE = "data.zip"
+DB_FILE_NAME = "OmamoriMH4.sqlite3"
 SKILL_FILE_NAME = "skill.csv"
+AMULET_FILE_NAME = "amulet.csv"
 MIN_MAX_FILE_NAME = "minmax.csv"
 SUFFICIENT_FILE_NAME = "sufficient.csv"
-SECOND_SKILL_FILE_NAME = "2ndskill.csv"
-SECOND_SLOT_FILE_NAME = "2ndskill_slot.csv"
+SEED2_SKILL2_FILE_NAME = "seed2_skill2.csv"
+SEED2_THRESHOLD1_FILE_NAME = "seed2_threshold1.csv"
 
 # --- sql sentences of database ---
 # for skill table
@@ -35,11 +34,35 @@ 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_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"
+AMULET_TABLE_COL_AMULET_NAME = u"amulet_name"
+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)
+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_ID2AMULET_SQL = u"""select {amulet_col} from {table} where id='{{id}}'""".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"
 MIN_MAX_TABLE_NAME = u"skill_minmax_{id}"
-MIN_MAX_COL_AMULET_NAME = u"amulet_name"
+MIN_MAX_COL_AMULET_ID = u"amulet_id"
 MIN_MAX_COL_SKILL_TABLE_NAME = u"skill_table_name"
 MIN_MAX_COL_MIN1 = u"min1"
 MIN_MAX_COL_MIN2 = u"min2"
@@ -47,9 +70,11 @@ MIN_MAX_COL_MAX1 = u"max1"
 MIN_MAX_COL_MAX2 = u"max2"
 MIN_MAX_COL_SKILL_ID = u"skill_id"
 MIN_MAX_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
-    (id integer primary key, {amulet_name} varchar, {table_name} varchar);""".format(
+    (id integer primary key, {amulet_id} integer, {table_name} varchar,
+    foreign key(amulet_id) references {amulet_table}(id));""".format(
         master=MIN_MAX_MASTER_TABLE_NAME,
-        amulet_name=MIN_MAX_COL_AMULET_NAME,
+        amulet_id=MIN_MAX_COL_AMULET_ID,
+        amulet_table=AMULET_TABLE_NAME,
         table_name=MIN_MAX_COL_SKILL_TABLE_NAME)
 MIN_MAX_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
     (id integer primary key, {skill_id} integer,
@@ -62,10 +87,10 @@ MIN_MAX_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
         max2=MIN_MAX_COL_MAX2,
         skill_table=SKILL_TABLE_NAME)
 MIN_MAX_MASTER_TABLE_INSERT_SQL = u"""insert into {master}
-    ({amulet_name}, {table_col}) values(?,?);""".format(
+    ({amulet_id}, {table_col}) values(?,?);""".format(
         master=MIN_MAX_MASTER_TABLE_NAME,
         table_col=MIN_MAX_COL_SKILL_TABLE_NAME,
-        amulet_name=MIN_MAX_COL_AMULET_NAME)
+        amulet_id=MIN_MAX_COL_AMULET_ID)
 MIN_MAX_TABLE_INSERT_SQL = u"""insert into {{table_name}}
     ({skill_id}, {min1}, {max1}, {min2}, {max2}) values(?,?,?,?,?)""".format(
         skill_id=MIN_MAX_COL_SKILL_ID,
@@ -73,34 +98,64 @@ 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)
+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)
+MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL1_ID_SQL = u"""select {skill_id} from {{table_name}} 
+    where {min1} != 0 and {max1} != 0""".format(
+        skill_id=MIN_MAX_COL_SKILL_ID,
+        min1=MIN_MAX_COL_MIN1,
+        max1=MIN_MAX_COL_MAX1)
+MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL2_ID_SQL = u"""select {skill_id} from {{table_name}} 
+    where {min2} != 0 and {max2} != 0""".format(
+        skill_id=MIN_MAX_COL_SKILL_ID,
+        min2=MIN_MAX_COL_MIN2,
+        max2=MIN_MAX_COL_MAX2)
+MIN_MAX_TABLE_SELECT_MIN1_SQL = u"""select min({col}) from {{table_name}} """.format(
+        col=MIN_MAX_COL_MIN1)
+MIN_MAX_TABLE_SELECT_MAX1_SQL = u"""select max({col}) from {{table_name}} """.format(
+        col=MIN_MAX_COL_MAX1)
+MIN_MAX_TABLE_SELECT_MIN2_SQL = u"""select min({col}) from {{table_name}} """.format(
+        col=MIN_MAX_COL_MIN2)
+MIN_MAX_TABLE_SELECT_MAX2_SQL = u"""select max({col}) from {{table_name}} """.format(
+        col=MIN_MAX_COL_MAX2)
+MIN_MAX_TABLE_SELECT_MAX1_OF_SKILL_SQL = u"""select max({col}) from {{table_name}} 
+    where skill_id={{skill_id}}""".format(
+        col=MIN_MAX_COL_MAX1)
+MIN_MAX_TABLE_SELECT_MAX2_OF_SKILL_SQL = u"""select max({col}) from {{table_name}} 
+    where skill_id={{skill_id}}""".format(
+        col=MIN_MAX_COL_MAX2)
 
-# for Second skill
-SECOND_MASTER_TABLE_NAME = u"skill_second_master"
-SECOND_TABLE_NAME = u"skill_second_{id}"
-SECOND_SLOT_TABLE_NAME = u"skill_second_slot"
-SECOND_COL_AMULET_NAME = u"amulet_name"
-SECOND_COL_SECOND_TABLE_NAME = u"second_table_name"
-SECOND_COL_RANDOM_SEED = u"random_seed"
-SECOND_COL_SKILL_ID1 = u"skill_id1"
-SECOND_COL_SKILL_ID2 = u"skill_id2"
-SECOND_COL_SKILL_ID3 = u"skill_id3"
-SECOND_COL_SKILL_ID4 = u"skill_id4"
-SECOND_COL_SKILL_ID5 = u"skill_id5"
-SECOND_COL_SKILL_ID6 = u"skill_id6"
-SECOND_COL_SKILL_ID7 = u"skill_id7"
-SECOND_COL_SLOT1 = u"slot1"
-SECOND_COL_SLOT2 = u"slot2"
-SECOND_COL_SLOT3 = u"slot3"
-SECOND_COL_SLOT4 = u"slot4"
-SECOND_COL_SLOT5 = u"slot5"
-SECOND_COL_SLOT6 = u"slot6"
-SECOND_COL_SLOT7 = u"slot7"
-SECOND_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
-    (id integer primary key, {amulet} varchar, {table_name} varchar);""".format(
-        master=SECOND_MASTER_TABLE_NAME,
-        amulet=SECOND_COL_AMULET_NAME,
-        table_name=SECOND_COL_SECOND_TABLE_NAME)
-SECOND_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
+# for Seed2 to skill2
+u""" SEED2から各お守りの第2スキルへのテーブル """
+SEED2_SKILL2_MASTER_TABLE_NAME = u"seed2_skill2_master"
+SEED2_SKILL2_TABLE_NAME = u"seed2_skill2_{id}"
+SEED2_SKILL2_COL_AMULET_ID = u"amulet_id"
+SEED2_SKILL2_COL_SEED2_SKILL2_TABLE_NAME = u"seed2_skill2_table_name"
+SEED2_SKILL2_COL_SEED2 = u"random_seed2"
+SEED2_SKILL2_COL_SKILL2_ID1 = u"skill2_id1"
+SEED2_SKILL2_COL_SKILL2_ID2 = u"skill2_id2"
+SEED2_SKILL2_COL_SKILL2_ID3 = u"skill2_id3"
+SEED2_SKILL2_COL_SKILL2_ID4 = u"skill2_id4"
+SEED2_SKILL2_COL_SKILL2_ID5 = u"skill2_id5"
+SEED2_SKILL2_COL_SKILL2_ID6 = u"skill2_id6"
+SEED2_SKILL2_COL_SKILL2_ID7 = u"skill2_id7"
+SEED2_SKILL2_COL_SKILL2_LIST = [SEED2_SKILL2_COL_SKILL2_ID1, SEED2_SKILL2_COL_SKILL2_ID2, SEED2_SKILL2_COL_SKILL2_ID3, SEED2_SKILL2_COL_SKILL2_ID4, SEED2_SKILL2_COL_SKILL2_ID5, SEED2_SKILL2_COL_SKILL2_ID6, SEED2_SKILL2_COL_SKILL2_ID7]
+SEED2_SKILL2_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
+    (id integer primary key, {amulet_id} integer, {table_name} varchar,
+    foreign key(amulet_id) references {amulet_table}(id));""".format(
+        master=SEED2_SKILL2_MASTER_TABLE_NAME,
+        amulet_id=SEED2_SKILL2_COL_AMULET_ID,
+        amulet_table=AMULET_TABLE_NAME,
+        table_name=SEED2_SKILL2_COL_SEED2_SKILL2_TABLE_NAME)
+SEED2_SKILL2_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
     (id integer primary key, {seed} integer unique, 
     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer, 
     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer, 
@@ -111,70 +166,161 @@ SECOND_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
     foreign key({skill_id5}) references {skill_table}(id),
     foreign key({skill_id6}) references {skill_table}(id),
     foreign key({skill_id7}) references {skill_table}(id));""".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,
+        seed=SEED2_SKILL2_COL_SEED2,
+        skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
+        skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
+        skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
+        skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
+        skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
+        skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
+        skill_id7=SEED2_SKILL2_COL_SKILL2_ID7,
         skill_table=SKILL_TABLE_NAME)
-SECOND_SLOT_TABLE_CREATE_SQL = u"""create table if not exists {table} 
-    (id integer primary key, {seed} integer unique,
-    {slot1} integer, {slot2} integer, {slot3} integer,
-    {slot4} integer, {slot5} integer, {slot6} integer, {slot7} integer);""".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)
-SECOND_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
+SEED2_SKILL2_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
     ({amulet_col}, {table_col}) values(?,?);""".format(
-        table=SECOND_MASTER_TABLE_NAME,
-        table_col=SECOND_COL_SECOND_TABLE_NAME,
-        amulet_col=SECOND_COL_AMULET_NAME)
-SECOND_TABLE_INSERT_SQL = u"""insert into {{table_name}}
+        table=SEED2_SKILL2_MASTER_TABLE_NAME,
+        table_col=SEED2_SKILL2_COL_SEED2_SKILL2_TABLE_NAME,
+        amulet_col=SEED2_SKILL2_COL_AMULET_ID)
+SEED2_SKILL2_TABLE_INSERT_SQL = u"""insert into {{table_name}}
     ({seed}, {skill_id1}, {skill_id2}, {skill_id3}, 
     {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) values(?,?,?,?,?,?,?,?)""".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_INSERT_SQL = u"""insert into {table}
-    ({seed}, {slot1}, {slot2}, {slot3}, {slot4}, {slot5}, {slot6}, {slot7})
+        seed=SEED2_SKILL2_COL_SEED2,
+        skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
+        skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
+        skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
+        skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
+        skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
+        skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
+        skill_id7=SEED2_SKILL2_COL_SKILL2_ID7)
+SEED2_SKILL2_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
+        amu_id=SEED2_SKILL2_COL_AMULET_ID,
+        table_col=SEED2_SKILL2_COL_SEED2_SKILL2_TABLE_NAME,
+        table=SEED2_SKILL2_MASTER_TABLE_NAME)
+SEED2_SKILL2_TABLE_SELECT_SEED_SQL = u"""select {seed} from {{table_name}} """.format(
+        seed=SEED2_SKILL2_COL_SEED2)
+SEED2_SKILL2_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=SEED2_SKILL2_COL_SEED2,
+        skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
+        skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
+        skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
+        skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
+        skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
+        skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
+        skill_id7=SEED2_SKILL2_COL_SKILL2_ID7)
+
+# for seed2 to threshold1
+u""" SEED2から判定値1へのテーブル """
+SEED2_THRESHOLD1_TABLE_NAME = u"seed2_threshold1"
+SEED2_THRESHOLD1_COL_SEED2 = u"random_seed2"
+SEED2_THRESHOLD1_COL_THRESHOLD1_1 = u"threshold1_1"
+SEED2_THRESHOLD1_COL_THRESHOLD1_2 = u"threshold1_2"
+SEED2_THRESHOLD1_COL_THRESHOLD1_3 = u"threshold1_3"
+SEED2_THRESHOLD1_COL_THRESHOLD1_4 = u"threshold1_4"
+SEED2_THRESHOLD1_COL_THRESHOLD1_5 = u"threshold1_5"
+SEED2_THRESHOLD1_COL_THRESHOLD1_6 = u"threshold1_6"
+SEED2_THRESHOLD1_COL_THRESHOLD1_7 = u"threshold1_7"
+SEED2_THRESHOLD1_COL_THRESHOLD1_LIST = [SEED2_THRESHOLD1_COL_THRESHOLD1_1, SEED2_THRESHOLD1_COL_THRESHOLD1_2, SEED2_THRESHOLD1_COL_THRESHOLD1_3, SEED2_THRESHOLD1_COL_THRESHOLD1_4, SEED2_THRESHOLD1_COL_THRESHOLD1_5, SEED2_THRESHOLD1_COL_THRESHOLD1_6, SEED2_THRESHOLD1_COL_THRESHOLD1_7]
+SEED2_THRESHOLD1_TABLE_CREATE_SQL = u"""create table if not exists {table} 
+    (id integer primary key, {seed} integer unique,
+    {threshold1_1} integer, {threshold1_2} integer, {threshold1_3} integer,
+    {threshold1_4} integer, {threshold1_5} integer, {threshold1_6} integer, {threshold1_7} integer);""".format(
+        table=SEED2_THRESHOLD1_TABLE_NAME,
+        seed=SEED2_THRESHOLD1_COL_SEED2,
+        threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
+        threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
+        threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
+        threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
+        threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
+        threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
+        threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
+SEED2_THRESHOLD1_TABLE_INSERT_SQL = u"""insert into {table}
+    ({seed}, {threshold1_1}, {threshold1_2}, {threshold1_3}, {threshold1_4}, {threshold1_5}, {threshold1_6}, {threshold1_7})
     values(?,?,?,?,?,?,?,?);""".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)
+        table=SEED2_THRESHOLD1_TABLE_NAME,
+        seed=SEED2_THRESHOLD1_COL_SEED2,
+        threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
+        threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
+        threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
+        threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
+        threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
+        threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
+        threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
+SEED2_THRESHOLD1_TABLE_SELECT_ALL_SQL = u"""select {seed}, {threshold1_1}, {threshold1_2},
+    {threshold1_3}, {threshold1_4}, {threshold1_5}, {threshold1_6}, {threshold1_7} from {table} """.format(
+        table=SEED2_THRESHOLD1_TABLE_NAME,
+        seed=SEED2_THRESHOLD1_COL_SEED2,
+        threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
+        threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
+        threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
+        threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
+        threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
+        threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
+        threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
+SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_SQL = u"""
+    select {seed} from {threshold1_table} 
+    where {threshold1_1} >= {{threshold1}} or 
+    {threshold1_2} >= {{threshold1}} or 
+    {threshold1_3} >= {{threshold1}} or 
+    {threshold1_4} >= {{threshold1}} or 
+    {threshold1_5} >= {{threshold1}} or 
+    {threshold1_6} >= {{threshold1}} or 
+    {threshold1_7} >= {{threshold1}}
+    """.format(
+        threshold1_table=SEED2_THRESHOLD1_TABLE_NAME,
+        seed=SEED2_THRESHOLD1_COL_SEED2,
+        threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
+        threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
+        threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
+        threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
+        threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
+        threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
+        threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
+
+
+# inner join of seed2 -> skill2 & tthreshol1
+SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_AND_SKILL2_SQL = u"""
+    select {threshold1_table}.{seed} from {threshold1_table} 
+    inner join {{skill_table}} on {threshold1_table}.{seed} = {{skill_table}}.{seed}
+    where ({{skill_table}}.{skill_id1} == {{skill_id}} and {threshold1_table}.{threshold1_1} >= {{threshold1}}) or 
+    ({{skill_table}}.{skill_id2} == {{skill_id}} and {threshold1_table}.{threshold1_2} >= {{threshold1}}) or 
+    ({{skill_table}}.{skill_id3} == {{skill_id}} and {threshold1_table}.{threshold1_3} >= {{threshold1}}) or 
+    ({{skill_table}}.{skill_id4} == {{skill_id}} and {threshold1_table}.{threshold1_4} >= {{threshold1}}) or 
+    ({{skill_table}}.{skill_id5} == {{skill_id}} and {threshold1_table}.{threshold1_5} >= {{threshold1}}) or 
+    ({{skill_table}}.{skill_id6} == {{skill_id}} and {threshold1_table}.{threshold1_6} >= {{threshold1}}) or 
+    ({{skill_table}}.{skill_id7} == {{skill_id}} and {threshold1_table}.{threshold1_7} >= {{threshold1}})
+    """.format(
+        threshold1_table=SEED2_THRESHOLD1_TABLE_NAME,
+        seed=SEED2_THRESHOLD1_COL_SEED2,
+        skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
+        skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
+        skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
+        skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
+        skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
+        skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
+        skill_id7=SEED2_SKILL2_COL_SKILL2_ID7,
+        threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
+        threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
+        threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
+        threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
+        threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
+        threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
+        threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
 
 # for sufficient value for slots
 SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master"
 SUFFICIENT_TABLE_NAME = u"sufficient_{id}"
-SUFFICIENT_COL_AMULET_NAME = u"amulet_name"
+SUFFICIENT_COL_AMULET_ID = u"amulet_id"
 SUFFICIENT_COL_SUFFICIENT_TABLE_NAME = u"sufficient_table_name"
 SUFFICIENT_COL_SUFFICIENT_VALUE = u"sufficient_value"
 SUFFICIENT_COL_SLOT1_THRESHOLD = u"slot1_threshold"
 SUFFICIENT_COL_SLOT2_THRESHOLD = u"slot2_threshold"
 SUFFICIENT_COL_SLOT3_THRESHOLD = u"slot3_threshold"
 SUFFICIENT_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
-    (id integer primary key, {amulet} varchar, {table_name} varchar);""".format(
+    (id integer primary key, {amulet_id} integer, {table_name} varchar, 
+    foreign key(amulet_id) references {amulet_table}(id));""".format(
         master=SUFFICIENT_MASTER_TABLE_NAME,
-        amulet=SUFFICIENT_COL_AMULET_NAME,
+        amulet_id=SUFFICIENT_COL_AMULET_ID,
+        amulet_table=AMULET_TABLE_NAME,
         table_name=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME)
 SUFFICIENT_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
     (id integer primary key, {sufficient_val} integer unique, 
@@ -187,7 +333,7 @@ SUFFICIENT_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
     ({amulet_col}, {table_col}) values(?,?);""".format(
         table=SUFFICIENT_MASTER_TABLE_NAME,
         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
-        amulet_col=SUFFICIENT_COL_AMULET_NAME)
+        amulet_col=SUFFICIENT_COL_AMULET_ID)
 SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}}
     ({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val})
     values(?,?,?,?);""".format(
@@ -195,11 +341,19 @@ 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)
-
-# support function
-def get_data_file_path(filename):
-    u""" ファイル名とデータディレクトリを結合して返す
-    """
-    return os.path.join(DATA_DIRECTORY, filename)
-
+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)
+SUFFICIENT_TABLE_SELECT_THRESHOLD1_SQL = u"""select {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}
+    where {sufficient_val}={{sufficient_val}}""".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)