# --- filenames ---
ZIP_FILE = "data.zip"
-DATA_DIRECTORY = "data"
-OUTPUT_DB_FILE_NAME = "OmamoriMH4.sqlite3"
+DB_FILE_NAME = "OmamoriMH4.sqlite3"
SKILL_FILE_NAME = "skill.csv"
AMULET_FILE_NAME = "amulet.csv"
MIN_MAX_FILE_NAME = "minmax.csv"
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"
(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_TABLE_INSERT_SQL = u"""insert into {table}
({amulet_col}) values(?);""".format(
table=AMULET_TABLE_NAME,
amulet_col=AMULET_TABLE_COL_AMULET_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"
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"
slot5=SECOND_COL_SLOT5,
slot6=SECOND_COL_SLOT6,
slot7=SECOND_COL_SLOT7)
+SECOND_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
+ amu_id=SECOND_COL_AMULET_ID,
+ table_col=SECOND_COL_SECOND_TABLE_NAME,
+ table=SECOND_MASTER_TABLE_NAME)
+SECOND_TABLE_SELECT_SEED_SQL = u"""select {seed} from {{table_name}} """.format(
+ seed=SECOND_COL_RANDOM_SEED)
+SECOND_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=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_SELECT_ALL_SQL = u"""select {seed}, {slot1}, {slot2},
+ {slot3}, {slot4}, {slot5}, {slot6}, {slot7} from {table} """.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_SLOT_TABLE_SELECT_SEEDS_FROM_THRESHOLD_AND_SKILL2_SQL = u"""
+ select {slot_table}.{seed} from {slot_table}
+ inner join {{skill_table}} on {slot_table}.{seed} = {{skill_table}}.{seed}
+ where ({{skill_table}}.{skill_id1} == {{skill_id}} and {slot_table}.{slot1} >= {{threshold}}) or
+ ({{skill_table}}.{skill_id2} == {{skill_id}} and {slot_table}.{slot2} >= {{threshold}}) or
+ ({{skill_table}}.{skill_id3} == {{skill_id}} and {slot_table}.{slot3} >= {{threshold}}) or
+ ({{skill_table}}.{skill_id4} == {{skill_id}} and {slot_table}.{slot4} >= {{threshold}}) or
+ ({{skill_table}}.{skill_id5} == {{skill_id}} and {slot_table}.{slot5} >= {{threshold}}) or
+ ({{skill_table}}.{skill_id6} == {{skill_id}} and {slot_table}.{slot6} >= {{threshold}}) or
+ ({{skill_table}}.{skill_id7} == {{skill_id}} and {slot_table}.{slot7} >= {{threshold}})
+ """.format(
+ slot_table=SECOND_SLOT_TABLE_NAME,
+ 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,
+ 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_SLOT_TABLE_SELECT_SEEDS_FROM_THRESHOLD_SQL = u"""
+ select {seed} from {slot_table}
+ where {slot1} >= {{threshold}} or
+ {slot2} >= {{threshold}} or
+ {slot3} >= {{threshold}} or
+ {slot4} >= {{threshold}} or
+ {slot5} >= {{threshold}} or
+ {slot6} >= {{threshold}} or
+ {slot7} >= {{threshold}}
+ """.format(
+ slot_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)
# for sufficient value for slots
SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master"
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_THRESHOLDS_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)
OSDN Git Service
