X-Git-Url: http://git.sourceforge.jp/view?p=amulettoolsmh4%2Fmain.git;a=blobdiff_plain;f=model%2Fdb_supports.py;h=fb8a998af3d8638ec83d48fa2749e94c364eaac7;hp=b8bf68f363301cdb7019dd2ef09c2f060c753d5d;hb=2c086b0aad62482e9cd905eb0a2c64b484ce55de;hpb=2ff5f0a846a7d2958bb179794a45751a89e73b53;ds=sidebyside diff --git a/model/db_supports.py b/model/db_supports.py index b8bf68f..fb8a998 100644 --- a/model/db_supports.py +++ b/model/db_supports.py @@ -22,779 +22,3 @@ SEED2_INISHIE_FILE_NAME = u"seed2_inishie.csv" SEED1_TENUN555_FILE_NAME = "seed1_tenun555.csv" SEED1_TENUN888_FILE_NAME = "seed1_tenun888.csv" -# no data -NO_DATA = -1 - -#tenun -KEY_TENUN555 = u"tenun555" -KEY_TENUN888 = u"tenun888" - -# inishie amulet -INISHIE_AMULET = u"古びたお守り" - -# --- sql sentences of database --- -# for skill table -SKILL_TABLE_NAME = u"skill_table" -SKILL_TABLE_COL_SKILL_NAME = u"skill_name" -SKILL_TABLE_CREATE_SQL = u"""create table if not exists {table} - (id integer primary key, {skill_name} varchar unique);""".format( - table=SKILL_TABLE_NAME, - skill_name=SKILL_TABLE_COL_SKILL_NAME) -SKILL_TABLE_INSERT_SQL = u"""insert into {table} - ({skill_col}) values(?);""".format( - table=SKILL_TABLE_NAME, - skill_col=SKILL_TABLE_COL_SKILL_NAME) -SKILL_TABLE_ID2SKILL_SQL = u"""select {skill_col} from {table} where id='{{id}}'""".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" -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_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" -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_id} integer, {table_name} varchar, - foreign key(amulet_id) references {amulet_table}(id));""".format( - master=MIN_MAX_MASTER_TABLE_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, - {min1} integer, {max1} integer, {min2} integer, {max2} integer, - foreign key(skill_id) references {skill_table}(id));""".format( - skill_id=MIN_MAX_COL_SKILL_ID, - min1=MIN_MAX_COL_MIN1, - min2=MIN_MAX_COL_MIN2, - max1=MIN_MAX_COL_MAX1, - max2=MIN_MAX_COL_MAX2, - skill_table=SKILL_TABLE_NAME) -MIN_MAX_MASTER_TABLE_INSERT_SQL = u"""insert into {master} - ({amulet_id}, {table_col}) values(?,?);""".format( - master=MIN_MAX_MASTER_TABLE_NAME, - table_col=MIN_MAX_COL_SKILL_TABLE_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, - min1=MIN_MAX_COL_MIN1, - 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) - -# table no of seed2 -u""" Seed2とテーブル、No.の表 """ -SEED2_TABLE_NO_TABLE = u"seed2_table_no" -SEED2_TABLE_NO_COL_SEED2 = u"seed2" -SEED2_TABLE_NO_COL_TABLE = u"table_no" -SEED2_TABLE_NO_COL_NO = u"no" -SEED2_TABLE_NO_TABLE_CREATE_SQL = u"""create table if not exists {table_name} - ({no} integer primary key, {seed} integer unique, {table} integer);""".format( - table_name=SEED2_TABLE_NO_TABLE, - seed=SEED2_TABLE_NO_COL_SEED2, - no=SEED2_TABLE_NO_COL_NO, - table=SEED2_TABLE_NO_COL_TABLE) -SEED2_TABLE_NO_TABLE_INSERT_SQL = u"""insert into {table_name} - ({no}, {seed}, {table}) values(?,?,?);""".format( - table_name=SEED2_TABLE_NO_TABLE, - seed=SEED2_TABLE_NO_COL_SEED2, - no=SEED2_TABLE_NO_COL_NO, - table=SEED2_TABLE_NO_COL_TABLE) -SEED2_TABLE_NO_TABLE_SELECT_FROM_SEED2_SQL = u"""select {table_no}, {no} from {table_name} - where {seed2}={{seed2}}""".format( - table_name=SEED2_TABLE_NO_TABLE, - table_no=SEED2_TABLE_NO_COL_TABLE, - no=SEED2_TABLE_NO_COL_NO, - seed2=SEED2_TABLE_NO_COL_SEED2) - -# 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"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, - foreign key({skill_id1}) references {skill_table}(id), - foreign key({skill_id2}) references {skill_table}(id), - foreign key({skill_id3}) references {skill_table}(id), - foreign key({skill_id4}) references {skill_table}(id), - 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=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) -SEED2_SKILL2_MASTER_TABLE_INSERT_SQL = u"""insert into {table} - ({amulet_col}, {table_col}) values(?,?);""".format( - 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=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) -SEED2_SKILL2_TABLE_SELECT_ALL_FROM_SEED2_SQL = u"""select {skill_id1}, {skill_id2}, - {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} where {seed}={{seed2}}""".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"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=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) - -# for seed2 to threshold2 -u""" SEED2から判定値2へのテーブル """ -SEED2_THRESHOLD2_TABLE_NAME = u"seed2_threshold2" -SEED2_THRESHOLD2_COL_SEED2 = u"seed2" -SEED2_THRESHOLD2_COL_THRESHOLD2_1 = u"threshold2_1" -SEED2_THRESHOLD2_COL_THRESHOLD2_2 = u"threshold2_2" -SEED2_THRESHOLD2_COL_THRESHOLD2_3 = u"threshold2_3" -SEED2_THRESHOLD2_COL_THRESHOLD2_4 = u"threshold2_4" -SEED2_THRESHOLD2_COL_THRESHOLD2_5 = u"threshold2_5" -SEED2_THRESHOLD2_COL_THRESHOLD2_6 = u"threshold2_6" -SEED2_THRESHOLD2_COL_THRESHOLD2_7 = u"threshold2_7" -SEED2_THRESHOLD2_COL_THRESHOLD2_LIST = [SEED2_THRESHOLD2_COL_THRESHOLD2_1, SEED2_THRESHOLD2_COL_THRESHOLD2_2, SEED2_THRESHOLD2_COL_THRESHOLD2_3, SEED2_THRESHOLD2_COL_THRESHOLD2_4, SEED2_THRESHOLD2_COL_THRESHOLD2_5, SEED2_THRESHOLD2_COL_THRESHOLD2_6, SEED2_THRESHOLD2_COL_THRESHOLD2_7] -SEED2_THRESHOLD2_TABLE_CREATE_SQL = u"""create table if not exists {table} - (id integer primary key, {seed} integer unique, - {threshold2_1} integer, {threshold2_2} integer, {threshold2_3} integer, - {threshold2_4} integer, {threshold2_5} integer, {threshold2_6} integer, {threshold2_7} integer);""".format( - table=SEED2_THRESHOLD2_TABLE_NAME, - seed=SEED2_THRESHOLD2_COL_SEED2, - threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1, - threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2, - threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3, - threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4, - threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5, - threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6, - threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7) -SEED2_THRESHOLD2_TABLE_INSERT_SQL = u"""insert into {table} - ({seed}, {threshold2_1}, {threshold2_2}, {threshold2_3}, {threshold2_4}, {threshold2_5}, {threshold2_6}, {threshold2_7}) - values(?,?,?,?,?,?,?,?);""".format( - table=SEED2_THRESHOLD2_TABLE_NAME, - seed=SEED2_THRESHOLD2_COL_SEED2, - threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1, - threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2, - threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3, - threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4, - threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5, - threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6, - threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7) -SEED2_THRESHOLD2_TABLE_SELECT_ALL_SQL = u"""select {seed}, {threshold2_1}, {threshold2_2}, - {threshold2_3}, {threshold2_4}, {threshold2_5}, {threshold2_6}, {threshold2_7} from {table} """.format( - table=SEED2_THRESHOLD2_TABLE_NAME, - seed=SEED2_THRESHOLD2_COL_SEED2, - threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1, - threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2, - threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3, - threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4, - threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5, - threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6, - threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7) -SEED2_THRESHOLD2_TABLE_SELECT_SEEDS_FROM_THRESHOLD2_SQL = u""" - select {seed} from {threshold2_table} - where {threshold2_1} >= {{threshold2}} or - {threshold2_2} >= {{threshold2}} or - {threshold2_3} >= {{threshold2}} or - {threshold2_4} >= {{threshold2}} or - {threshold2_5} >= {{threshold2}} or - {threshold2_6} >= {{threshold2}} or - {threshold2_7} >= {{threshold2}} - """.format( - threshold2_table=SEED2_THRESHOLD2_TABLE_NAME, - seed=SEED2_THRESHOLD2_COL_SEED2, - threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1, - threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2, - threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3, - threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4, - threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5, - threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6, - threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7) - -# inner join of seed2 -> skill2 & threshold1 -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) - -# inner join of seed2 -> skill2 & threshold2 -SEED2_THRESHOLD2_TABLE_SELECT_SEEDS_FROM_THRESHOLD2_AND_SKILL2_SQL = u""" - select {threshold2_table}.{seed} from {threshold2_table} - inner join {{skill_table}} on {threshold2_table}.{seed} = {{skill_table}}.{seed} - where ({{skill_table}}.{skill_id1} == {{skill_id}} and {threshold2_table}.{threshold2_1} >= {{threshold2}}) or - ({{skill_table}}.{skill_id2} == {{skill_id}} and {threshold2_table}.{threshold2_2} >= {{threshold2}}) or - ({{skill_table}}.{skill_id3} == {{skill_id}} and {threshold2_table}.{threshold2_3} >= {{threshold2}}) or - ({{skill_table}}.{skill_id4} == {{skill_id}} and {threshold2_table}.{threshold2_4} >= {{threshold2}}) or - ({{skill_table}}.{skill_id5} == {{skill_id}} and {threshold2_table}.{threshold2_5} >= {{threshold2}}) or - ({{skill_table}}.{skill_id6} == {{skill_id}} and {threshold2_table}.{threshold2_6} >= {{threshold2}}) or - ({{skill_table}}.{skill_id7} == {{skill_id}} and {threshold2_table}.{threshold2_7} >= {{threshold2}}) - """.format( - threshold2_table=SEED2_THRESHOLD2_TABLE_NAME, - seed=SEED2_THRESHOLD2_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, - threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1, - threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2, - threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3, - threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4, - threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5, - threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6, - threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7) - - -# seed2 -> inishie table -u""" 古の錬金テーブル """ -SEED2_INISHIE_TABLE_NAME = u"seed2_inishie" -SEED2_INISHIE_COL_SEED2 = u"seed2" -SEED2_INISHIE_COL_SKILL2_ID = u"skill2_id" -SEED2_INISHIE_COL_THRESHOLD1 = u"treshold1" -SEED2_INISHIE_COL_THRESHOLD2 = u"treshold2" -SEED2_INISHIE_TABLE_CREATE_SQL = u"""create table if not exists {table_name} - (id integer primary key, {seed} integer unique, - {skill_id} integer, {threshold1} integer, {threshold2} integer, - foreign key({skill_id}) references {skill_table}(id));""".format( - table_name=SEED2_INISHIE_TABLE_NAME, - seed=SEED2_INISHIE_COL_SEED2, - skill_id=SEED2_INISHIE_COL_SKILL2_ID, - threshold1=SEED2_INISHIE_COL_THRESHOLD1, - threshold2=SEED2_INISHIE_COL_THRESHOLD2, - skill_table=SKILL_TABLE_NAME) -SEED2_INISHIE_TABLE_INSERT_SQL = u"""insert into {table_name} - ({seed}, {skill_id}, {threshold1}, {threshold2}) values(?,?,?,?)""".format( - table_name=SEED2_INISHIE_TABLE_NAME, - seed=SEED2_INISHIE_COL_SEED2, - threshold1=SEED2_INISHIE_COL_THRESHOLD1, - threshold2=SEED2_INISHIE_COL_THRESHOLD2, - skill_id=SEED2_INISHIE_COL_SKILL2_ID) -SEED2_INISHIE_TABLE_SELECT_FROM_SEED2_SQL = u"""select {skill_id}, {threshold1}, {threshold2} - from {table_name} where {seed2}={{seed2}}""".format( - table_name=SEED2_INISHIE_TABLE_NAME, - seed2=SEED2_INISHIE_COL_SEED2, - threshold1=SEED2_INISHIE_COL_THRESHOLD1, - threshold2=SEED2_INISHIE_COL_THRESHOLD2, - skill_id=SEED2_INISHIE_COL_SKILL2_ID) -SEED2_INISHIE_TABLE_SELECT_SEED2_FROM_SKILL2_THRESHOLD_SQL = u"""select {seed2} - from {table_name} where {skill_id}={{skill2_id}} and - ({threshold1}>={{threshold}} or {threshold2}>={{threshold}})""".format( - table_name=SEED2_INISHIE_TABLE_NAME, - seed2=SEED2_INISHIE_COL_SEED2, - threshold1=SEED2_INISHIE_COL_THRESHOLD1, - threshold2=SEED2_INISHIE_COL_THRESHOLD2, - skill_id=SEED2_INISHIE_COL_SKILL2_ID) -SEED2_INISHIE_TABLE_SELECT_SEED2_FROM_THRESHOLD_SQL = u"""select {seed2} - from {table_name} where {threshold1}>={{threshold}} or {threshold2}>={{threshold}}""".format( - table_name=SEED2_INISHIE_TABLE_NAME, - seed2=SEED2_INISHIE_COL_SEED2, - threshold1=SEED2_INISHIE_COL_THRESHOLD1, - threshold2=SEED2_INISHIE_COL_THRESHOLD2) - -# for sufficient value for slots -SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master" -SUFFICIENT_TABLE_NAME = u"sufficient_{id}" -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_id} integer, {table_name} varchar, - foreign key({amulet_id}) references {amulet_table}(id));""".format( - master=SUFFICIENT_MASTER_TABLE_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, - {slot1_val} integer, {slot2_val} integer, {slot3_val} integer) ;""".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_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_ID) -SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}} - ({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val}) - values(?,?,?,?);""".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_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_THRESHOLD_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) - -# for seed1 table by tenun -SEED1_TENUN555_TABLE = u"seed1_table_tenun555" -SEED1_TENUN888_TABLE = u"seed1_table_tenun888" -SEED1_TENUN_COL_TABLE = u"table_no" -SEED1_TENUN_COL_NO = u"no" -SEED1_TENUN_COL_SEED1 = u"seed1" -SEED1_TENUN_COL_RESULT_NO = u"result_no" -SEED1_TENUN_COL_AMULET1 = u"amulet_id1" -SEED1_TENUN_COL_AMULET2 = u"amulet_id2" -SEED1_TENUN_COL_AMULET3 = u"amulet_id3" -SEED1_TENUN_COL_AMULET4 = u"amulet_id4" -SEED1_TENUN_COL_AMULET5 = u"amulet_id5" -SEED1_TENUN_COL_AMULET6 = u"amulet_id6" -SEED1_TENUN_COL_AMULET7 = u"amulet_id7" # 555 doesn't has this col -SEED1_TENUN_COL_AMULET_LIST = [SEED1_TENUN_COL_AMULET1, SEED1_TENUN_COL_AMULET2, SEED1_TENUN_COL_AMULET3, - SEED1_TENUN_COL_AMULET4, SEED1_TENUN_COL_AMULET5, SEED1_TENUN_COL_AMULET6, SEED1_TENUN_COL_AMULET7] -SEED1_TENUN_COL_SKILL1_1 = u"skill1_1" -SEED1_TENUN_COL_SKILL1_2 = u"skill1_2" -SEED1_TENUN_COL_SKILL1_3 = u"skill1_3" -SEED1_TENUN_COL_SKILL1_4 = u"skill1_4" -SEED1_TENUN_COL_SKILL1_5 = u"skill1_5" -SEED1_TENUN_COL_SKILL1_6 = u"skill1_6" -SEED1_TENUN_COL_SKILL1_7 = u"skill1_7" # 555 doesn't has this col -SEED1_TENUN_COL_SKILL1_LIST = [SEED1_TENUN_COL_SKILL1_1, SEED1_TENUN_COL_SKILL1_2, SEED1_TENUN_COL_SKILL1_3, - SEED1_TENUN_COL_SKILL1_4, SEED1_TENUN_COL_SKILL1_5, SEED1_TENUN_COL_SKILL1_6, SEED1_TENUN_COL_SKILL1_7] -SEED1_TENUN555_TABLE_CREATE_SQL = u"""create table if not exists {table_name} - (id integer primary key, {table_no} integer, {seed} integer unique, {result_num} integer, - {amulet_id1} integer, {amulet_id2} integer, {amulet_id3} integer, - {amulet_id4} integer, {amulet_id5} integer, {amulet_id6} integer, - {skill_id1} integer, {skill_id2} integer, {skill_id3} integer, - {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, - foreign key({amulet_id1}) references {amulet_table}(id), - foreign key({amulet_id2}) references {amulet_table}(id), - foreign key({amulet_id3}) references {amulet_table}(id), - foreign key({amulet_id4}) references {amulet_table}(id), - foreign key({amulet_id5}) references {amulet_table}(id), - foreign key({amulet_id6}) references {amulet_table}(id), - foreign key({skill_id1}) references {skill_table}(id), - foreign key({skill_id2}) references {skill_table}(id), - foreign key({skill_id3}) references {skill_table}(id), - foreign key({skill_id4}) references {skill_table}(id), - foreign key({skill_id5}) references {skill_table}(id), - foreign key({skill_id6}) references {skill_table}(id) - );""".format( - seed=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - result_num=SEED1_TENUN_COL_RESULT_NO, - amulet_id1=SEED1_TENUN_COL_AMULET1, - amulet_id2=SEED1_TENUN_COL_AMULET2, - amulet_id3=SEED1_TENUN_COL_AMULET3, - amulet_id4=SEED1_TENUN_COL_AMULET4, - amulet_id5=SEED1_TENUN_COL_AMULET5, - amulet_id6=SEED1_TENUN_COL_AMULET6, - skill_id1=SEED1_TENUN_COL_SKILL1_1, - skill_id2=SEED1_TENUN_COL_SKILL1_2, - skill_id3=SEED1_TENUN_COL_SKILL1_3, - skill_id4=SEED1_TENUN_COL_SKILL1_4, - skill_id5=SEED1_TENUN_COL_SKILL1_5, - skill_id6=SEED1_TENUN_COL_SKILL1_6, - amulet_table=AMULET_TABLE_NAME, - skill_table=SKILL_TABLE_NAME, - table_name=SEED1_TENUN555_TABLE) -SEED1_TENUN555_TABLE_INSERT_SQL = u"""insert into {table_name} - (id, {table_no}, {seed}, {result_num}, - {amulet_id1}, {amulet_id2}, {amulet_id3}, - {amulet_id4}, {amulet_id5}, {amulet_id6}, - {skill_id1}, {skill_id2}, {skill_id3}, - {skill_id4}, {skill_id5}, {skill_id6}) - values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);""".format( - table_name=SEED1_TENUN555_TABLE, - seed=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - result_num=SEED1_TENUN_COL_RESULT_NO, - amulet_id1=SEED1_TENUN_COL_AMULET1, - amulet_id2=SEED1_TENUN_COL_AMULET2, - amulet_id3=SEED1_TENUN_COL_AMULET3, - amulet_id4=SEED1_TENUN_COL_AMULET4, - amulet_id5=SEED1_TENUN_COL_AMULET5, - amulet_id6=SEED1_TENUN_COL_AMULET6, - skill_id1=SEED1_TENUN_COL_SKILL1_1, - skill_id2=SEED1_TENUN_COL_SKILL1_2, - skill_id3=SEED1_TENUN_COL_SKILL1_3, - skill_id4=SEED1_TENUN_COL_SKILL1_4, - skill_id5=SEED1_TENUN_COL_SKILL1_5, - skill_id6=SEED1_TENUN_COL_SKILL1_6) -SEED1_TENUN555_TABLE_SELECT_SQL = u""" select {seed} from {table_name} - """.format(seed=SEED1_TENUN_COL_SEED1, - table_name=SEED1_TENUN555_TABLE) -SEED1_TENUN555_TABLE_SELECT_TABLE_NOS_FROM_SEED1_SQL = u"""select {table_no}, id from {table_name} - where {seed1}={{seed1}}""".format( - seed1=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - table_name=SEED1_TENUN555_TABLE) -SEED1_TENUN555_TABLE_SELECT_NEAR_SEED1S_FROM_NO_SQL = u"""select id, {seed1} from {table_name} - where {table_no}={{table_no}} and id>={{min_no}} and id<={{max_no}} - order by id ASC """.format( - seed1=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - table_name=SEED1_TENUN555_TABLE) -SEED1_TENUN555_TABLE_SELECT_ALL_FROM_SEED1_SQL = u"""select id, {table_no}, {result_num}, - {amulet_id1}, {amulet_id2}, {amulet_id3}, - {amulet_id4}, {amulet_id5}, {amulet_id6}, - {skill1_id1}, {skill1_id2}, {skill1_id3}, - {skill1_id4}, {skill1_id5}, {skill1_id6} - from {table_name} where {seed1}={{seed1}} """.format( - seed1=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - table_name=SEED1_TENUN555_TABLE, - result_num=SEED1_TENUN_COL_RESULT_NO, - amulet_id1=SEED1_TENUN_COL_AMULET1, - amulet_id2=SEED1_TENUN_COL_AMULET2, - amulet_id3=SEED1_TENUN_COL_AMULET3, - amulet_id4=SEED1_TENUN_COL_AMULET4, - amulet_id5=SEED1_TENUN_COL_AMULET5, - amulet_id6=SEED1_TENUN_COL_AMULET6, - skill1_id1=SEED1_TENUN_COL_SKILL1_1, - skill1_id2=SEED1_TENUN_COL_SKILL1_2, - skill1_id3=SEED1_TENUN_COL_SKILL1_3, - skill1_id4=SEED1_TENUN_COL_SKILL1_4, - skill1_id5=SEED1_TENUN_COL_SKILL1_5, - skill1_id6=SEED1_TENUN_COL_SKILL1_6) -# extract skill1 by place -SEED1_TENUN555_TABLE_SELECT_SEED1_SQL = u"""select - {seed1} from {table_name} """.format( - seed1=SEED1_TENUN_COL_SEED1, - table_name=SEED1_TENUN555_TABLE) -# count seed1 number with skill1 and place -SEED1_TENUN555_TABLE_COUNT_SEED1_SQL = u"""select - count({seed1}) from {table_name} """.format( - seed1=SEED1_TENUN_COL_SEED1, - table_name=SEED1_TENUN555_TABLE) - -# tenun888 -SEED1_TENUN888_TABLE_CREATE_SQL = u"""create table if not exists {table_name} - (id integer primary key, {table_no} integer, {seed} integer unique, {result_num} integer, - {amulet_id1} integer, {amulet_id2} integer, {amulet_id3} integer, - {amulet_id4} integer, {amulet_id5} integer, {amulet_id6} integer, {amulet_id7} integer, - {skill_id1} integer, {skill_id2} integer, {skill_id3} integer, - {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer, - foreign key({amulet_id1}) references {amulet_table}(id), - foreign key({amulet_id2}) references {amulet_table}(id), - foreign key({amulet_id3}) references {amulet_table}(id), - foreign key({amulet_id4}) references {amulet_table}(id), - foreign key({amulet_id5}) references {amulet_table}(id), - foreign key({amulet_id6}) references {amulet_table}(id), - foreign key({amulet_id7}) references {amulet_table}(id), - foreign key({skill_id1}) references {skill_table}(id), - foreign key({skill_id2}) references {skill_table}(id), - foreign key({skill_id3}) references {skill_table}(id), - foreign key({skill_id4}) references {skill_table}(id), - 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=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - result_num=SEED1_TENUN_COL_RESULT_NO, - amulet_id1=SEED1_TENUN_COL_AMULET1, - amulet_id2=SEED1_TENUN_COL_AMULET2, - amulet_id3=SEED1_TENUN_COL_AMULET3, - amulet_id4=SEED1_TENUN_COL_AMULET4, - amulet_id5=SEED1_TENUN_COL_AMULET5, - amulet_id6=SEED1_TENUN_COL_AMULET6, - amulet_id7=SEED1_TENUN_COL_AMULET7, - skill_id1=SEED1_TENUN_COL_SKILL1_1, - skill_id2=SEED1_TENUN_COL_SKILL1_2, - skill_id3=SEED1_TENUN_COL_SKILL1_3, - skill_id4=SEED1_TENUN_COL_SKILL1_4, - skill_id5=SEED1_TENUN_COL_SKILL1_5, - skill_id6=SEED1_TENUN_COL_SKILL1_6, - skill_id7=SEED1_TENUN_COL_SKILL1_7, - amulet_table=AMULET_TABLE_NAME, - skill_table=SKILL_TABLE_NAME, - table_name=SEED1_TENUN888_TABLE) -SEED1_TENUN888_TABLE_INSERT_SQL = u"""insert into {table_name} - (id, {table_no}, {seed}, {result_num}, - {amulet_id1}, {amulet_id2}, {amulet_id3}, - {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7}, - {skill_id1}, {skill_id2}, {skill_id3}, - {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) - values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);""".format( - table_name=SEED1_TENUN888_TABLE, - seed=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - result_num=SEED1_TENUN_COL_RESULT_NO, - amulet_id1=SEED1_TENUN_COL_AMULET1, - amulet_id2=SEED1_TENUN_COL_AMULET2, - amulet_id3=SEED1_TENUN_COL_AMULET3, - amulet_id4=SEED1_TENUN_COL_AMULET4, - amulet_id5=SEED1_TENUN_COL_AMULET5, - amulet_id6=SEED1_TENUN_COL_AMULET6, - amulet_id7=SEED1_TENUN_COL_AMULET7, - skill_id1=SEED1_TENUN_COL_SKILL1_1, - skill_id2=SEED1_TENUN_COL_SKILL1_2, - skill_id3=SEED1_TENUN_COL_SKILL1_3, - skill_id4=SEED1_TENUN_COL_SKILL1_4, - skill_id5=SEED1_TENUN_COL_SKILL1_5, - skill_id6=SEED1_TENUN_COL_SKILL1_6, - skill_id7=SEED1_TENUN_COL_SKILL1_7) -SEED1_TENUN888_TABLE_SELECT_SQL = u""" select {seed} from {table_name} - """.format(seed=SEED1_TENUN_COL_SEED1, - table_name=SEED1_TENUN888_TABLE) -SEED1_TENUN888_TABLE_SELECT_TABLE_NOS_FROM_SEED1_SQL = u"""select {table_no}, id from {table_name} - where {seed1}={{seed1}}""".format( - seed1=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - table_name=SEED1_TENUN888_TABLE) -SEED1_TENUN888_TABLE_SELECT_NEAR_SEED1S_FROM_NO_SQL = u"""select id, {seed1} from {table_name} - where {table_no}={{table_no}} and id>={{min_no}} and id<={{max_no}} - order by id ASC """.format( - seed1=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - table_name=SEED1_TENUN888_TABLE) -SEED1_TENUN888_TABLE_SELECT_ALL_FROM_SEED1_SQL = u"""select id, {table_no}, {result_num}, - {amulet_id1}, {amulet_id2}, {amulet_id3}, - {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7}, - {skill1_id1}, {skill1_id2}, {skill1_id3}, - {skill1_id4}, {skill1_id5}, {skill1_id6}, {skill1_id7} - from {table_name} where {seed1}={{seed1}} """.format( - seed1=SEED1_TENUN_COL_SEED1, - table_no=SEED1_TENUN_COL_TABLE, - table_name=SEED1_TENUN888_TABLE, - result_num=SEED1_TENUN_COL_RESULT_NO, - amulet_id1=SEED1_TENUN_COL_AMULET1, - amulet_id2=SEED1_TENUN_COL_AMULET2, - amulet_id3=SEED1_TENUN_COL_AMULET3, - amulet_id4=SEED1_TENUN_COL_AMULET4, - amulet_id5=SEED1_TENUN_COL_AMULET5, - amulet_id6=SEED1_TENUN_COL_AMULET6, - amulet_id7=SEED1_TENUN_COL_AMULET7, - skill1_id1=SEED1_TENUN_COL_SKILL1_1, - skill1_id2=SEED1_TENUN_COL_SKILL1_2, - skill1_id3=SEED1_TENUN_COL_SKILL1_3, - skill1_id4=SEED1_TENUN_COL_SKILL1_4, - skill1_id5=SEED1_TENUN_COL_SKILL1_5, - skill1_id6=SEED1_TENUN_COL_SKILL1_6, - skill1_id7=SEED1_TENUN_COL_SKILL1_7) - -# extract skill1 by place -SEED1_TENUN888_TABLE_SELECT_SEED1_SQL = u"""select - {seed1} from {table_name} """.format( - seed1=SEED1_TENUN_COL_SEED1, - table_name=SEED1_TENUN888_TABLE) -# count seed1 number with skill1 and place -SEED1_TENUN888_TABLE_COUNT_SEED1_SQL = u"""select - count({seed1}) from {table_name} """.format( - seed1=SEED1_TENUN_COL_SEED1, - table_name=SEED1_TENUN888_TABLE)