# constant numbers of database
# データベース関連の定数値など
-# 2013/12/03 written by kei9
+# 2013/12/04 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"
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"
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,
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,
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)
+
# 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_AMULET_ID = u"amulet_id"
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_ID5 = u"skill_id5"
SECOND_COL_SKILL_ID6 = u"skill_id6"
SECOND_COL_SKILL_ID7 = u"skill_id7"
+SECOND_COL_SKILL_LIST = [SECOND_COL_SKILL_ID1, SECOND_COL_SKILL_ID2, SECOND_COL_SKILL_ID3, SECOND_COL_SKILL_ID4, SECOND_COL_SKILL_ID5, SECOND_COL_SKILL_ID6, SECOND_COL_SKILL_ID7]
SECOND_COL_SLOT1 = u"slot1"
SECOND_COL_SLOT2 = u"slot2"
SECOND_COL_SLOT3 = u"slot3"
SECOND_COL_SLOT5 = u"slot5"
SECOND_COL_SLOT6 = u"slot6"
SECOND_COL_SLOT7 = u"slot7"
+SECOND_COL_SLOT_LIST = [SECOND_COL_SLOT1, SECOND_COL_SLOT2, SECOND_COL_SLOT3, SECOND_COL_SLOT4, SECOND_COL_SLOT5, SECOND_COL_SLOT6, SECOND_COL_SLOT7]
SECOND_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=SECOND_MASTER_TABLE_NAME,
- amulet=SECOND_COL_AMULET_NAME,
+ amulet_id=SECOND_COL_AMULET_ID,
+ amulet_table=AMULET_TABLE_NAME,
table_name=SECOND_COL_SECOND_TABLE_NAME)
SECOND_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
(id integer primary key, {seed} integer unique,
({amulet_col}, {table_col}) values(?,?);""".format(
table=SECOND_MASTER_TABLE_NAME,
table_col=SECOND_COL_SECOND_TABLE_NAME,
- amulet_col=SECOND_COL_AMULET_NAME)
+ amulet_col=SECOND_COL_AMULET_ID)
SECOND_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(
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)
# 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,
({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(
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)