model/db_supports.py

   1 # -*- coding: utf-8 -*-
   2
   3 # constant numbers of database
   4 # データベース関連の定数値など
   5 # 2013/12/04 written by kei9
   6
   7 # import modules
   8 import os.path
   9 import codecs
  10
  11 # --- filenames ---
  12 ZIP_FILE = "data.zip"
  13 DB_FILE_NAME = "OmamoriMH4.sqlite3"
  14 SKILL_FILE_NAME = "skill.csv"
  15 AMULET_FILE_NAME = "amulet.csv"
  16 MIN_MAX_FILE_NAME = "minmax.csv"
  17 SUFFICIENT_FILE_NAME = "sufficient.csv"
  18 SECOND_SKILL_FILE_NAME = "2ndskill.csv"
  19 SECOND_SLOT_FILE_NAME = "2ndskill_slot.csv"
  20
  21 # --- sql sentences of database ---
  22 # for skill table
  23 SKILL_TABLE_NAME = u"skill_table"
  24 SKILL_TABLE_COL_SKILL_NAME = u"skill_name"
  25 SKILL_TABLE_CREATE_SQL = u"""create table if not exists {table}
  26     (id integer primary key, {skill_name} varchar unique);""".format(
  27         table=SKILL_TABLE_NAME,
  28         skill_name=SKILL_TABLE_COL_SKILL_NAME)
  29 SKILL_TABLE_INSERT_SQL = u"""insert into {table}
  30     ({skill_col}) values(?);""".format(
  31         table=SKILL_TABLE_NAME,
  32         skill_col=SKILL_TABLE_COL_SKILL_NAME)
  33 SKILL_TABLE_ID2SKILL_SQL = u"""select {skill_col} from {table} where id='{{id}}'""".format(
  34         skill_col=SKILL_TABLE_COL_SKILL_NAME,
  35         table=SKILL_TABLE_NAME)
  36 SKILL_TABLE_SKILL2ID_SQL = u"""select id from {table} where {skill_col}='{{skill_name}}'""".format(
  37         skill_col=SKILL_TABLE_COL_SKILL_NAME,
  38         table=SKILL_TABLE_NAME)
  39 SKILL_TABLE_SELECT_ALL_SQL = u"""select id, {skill_col} from {table}""".format(
  40         skill_col=SKILL_TABLE_COL_SKILL_NAME,
  41         table=SKILL_TABLE_NAME)
  42
  43 # for amulet table
  44 AMULET_TABLE_NAME = u"amulet_table"
  45 AMULET_TABLE_COL_AMULET_NAME = u"amulet_name"
  46 AMULET_TABLE_CREATE_SQL = u"""create table if not exists {table}
  47     (id integer primary key, {amulet_name} varchar unique);""".format(
  48         table=AMULET_TABLE_NAME,
  49         amulet_name=AMULET_TABLE_COL_AMULET_NAME)
  50 AMULET_TABLE_INSERT_SQL = u"""insert into {table}
  51     ({amulet_col}) values(?);""".format(
  52         table=AMULET_TABLE_NAME,
  53         amulet_col=AMULET_TABLE_COL_AMULET_NAME)
  54 AMULET_TABLE_ID2AMULET_SQL = u"""select {amulet_col} from {table} where id='{{id}}'""".format(
  55         amulet_col=AMULET_TABLE_COL_AMULET_NAME,
  56         table=AMULET_TABLE_NAME)
  57 AMULET_TABLE_AMULET2ID_SQL = u"""select id from {table} where {amulet_col}='{{amulet_name}}'""".format(
  58         amulet_col=AMULET_TABLE_COL_AMULET_NAME,
  59         table=AMULET_TABLE_NAME)
  60 AMULET_TABLE_SELECT_ALL_SQL = u"""select id, {amulet_col} from {table}""".format(
  61         amulet_col=AMULET_TABLE_COL_AMULET_NAME,
  62         table=AMULET_TABLE_NAME)
  63
  64 # for minmax of skill
  65 MIN_MAX_MASTER_TABLE_NAME = u"skill_minmax_master"
  66 MIN_MAX_TABLE_NAME = u"skill_minmax_{id}"
  67 MIN_MAX_COL_AMULET_ID = u"amulet_id"
  68 MIN_MAX_COL_SKILL_TABLE_NAME = u"skill_table_name"
  69 MIN_MAX_COL_MIN1 = u"min1"
  70 MIN_MAX_COL_MIN2 = u"min2"
  71 MIN_MAX_COL_MAX1 = u"max1"
  72 MIN_MAX_COL_MAX2 = u"max2"
  73 MIN_MAX_COL_SKILL_ID = u"skill_id"
  74 MIN_MAX_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
  75     (id integer primary key, {amulet_id} integer, {table_name} varchar,
  76     foreign key(amulet_id) references {amulet_table}(id));""".format(
  77         master=MIN_MAX_MASTER_TABLE_NAME,
  78         amulet_id=MIN_MAX_COL_AMULET_ID,
  79         amulet_table=AMULET_TABLE_NAME,
  80         table_name=MIN_MAX_COL_SKILL_TABLE_NAME)
  81 MIN_MAX_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
  82     (id integer primary key, {skill_id} integer,
  83     {min1} integer, {max1} integer, {min2} integer, {max2} integer,
  84     foreign key(skill_id) references {skill_table}(id));""".format(
  85         skill_id=MIN_MAX_COL_SKILL_ID,
  86         min1=MIN_MAX_COL_MIN1,
  87         min2=MIN_MAX_COL_MIN2,
  88         max1=MIN_MAX_COL_MAX1,
  89         max2=MIN_MAX_COL_MAX2,
  90         skill_table=SKILL_TABLE_NAME)
  91 MIN_MAX_MASTER_TABLE_INSERT_SQL = u"""insert into {master}
  92     ({amulet_id}, {table_col}) values(?,?);""".format(
  93         master=MIN_MAX_MASTER_TABLE_NAME,
  94         table_col=MIN_MAX_COL_SKILL_TABLE_NAME,
  95         amulet_id=MIN_MAX_COL_AMULET_ID)
  96 MIN_MAX_TABLE_INSERT_SQL = u"""insert into {{table_name}}
  97     ({skill_id}, {min1}, {max1}, {min2}, {max2}) values(?,?,?,?,?)""".format(
  98         skill_id=MIN_MAX_COL_SKILL_ID,
  99         min1=MIN_MAX_COL_MIN1,
 100         min2=MIN_MAX_COL_MIN2,
 101         max1=MIN_MAX_COL_MAX1,
 102         max2=MIN_MAX_COL_MAX2)
 103 MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
 104         amu_id=MIN_MAX_COL_AMULET_ID,
 105         table_col=MIN_MAX_COL_SKILL_TABLE_NAME,
 106         table=MIN_MAX_MASTER_TABLE_NAME)
 107 MIN_MAX_TABLE_SELECT_ALL_SQL = u"""select {skill_id}, {min1}, {max1}, {min2}, {max2} from {{table_name}} """.format(
 108         skill_id=MIN_MAX_COL_SKILL_ID,
 109         min1=MIN_MAX_COL_MIN1,
 110         max1=MIN_MAX_COL_MAX1,
 111         min2=MIN_MAX_COL_MIN2,
 112         max2=MIN_MAX_COL_MAX2)
 113 MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL1_ID_SQL = u"""select {skill_id} from {{table_name}}
 114     where {min1} != 0 and {max1} != 0""".format(
 115         skill_id=MIN_MAX_COL_SKILL_ID,
 116         min1=MIN_MAX_COL_MIN1,
 117         max1=MIN_MAX_COL_MAX1)
 118 MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL2_ID_SQL = u"""select {skill_id} from {{table_name}}
 119     where {min2} != 0 and {max2} != 0""".format(
 120         skill_id=MIN_MAX_COL_SKILL_ID,
 121         min2=MIN_MAX_COL_MIN2,
 122         max2=MIN_MAX_COL_MAX2)
 123 MIN_MAX_TABLE_SELECT_MIN1_SQL = u"""select min({col}) from {{table_name}} """.format(
 124         col=MIN_MAX_COL_MIN1)
 125 MIN_MAX_TABLE_SELECT_MAX1_SQL = u"""select max({col}) from {{table_name}} """.format(
 126         col=MIN_MAX_COL_MAX1)
 127 MIN_MAX_TABLE_SELECT_MIN2_SQL = u"""select min({col}) from {{table_name}} """.format(
 128         col=MIN_MAX_COL_MIN2)
 129 MIN_MAX_TABLE_SELECT_MAX2_SQL = u"""select max({col}) from {{table_name}} """.format(
 130         col=MIN_MAX_COL_MAX2)
 131 MIN_MAX_TABLE_SELECT_MAX1_OF_SKILL_SQL = u"""select max({col}) from {{table_name}}
 132     where skill_id={{skill_id}}""".format(
 133         col=MIN_MAX_COL_MAX1)
 134 MIN_MAX_TABLE_SELECT_MAX2_OF_SKILL_SQL = u"""select max({col}) from {{table_name}}
 135     where skill_id={{skill_id}}""".format(
 136         col=MIN_MAX_COL_MAX2)
 137
 138 # for Second skill
 139 SECOND_MASTER_TABLE_NAME = u"skill_second_master"
 140 SECOND_TABLE_NAME = u"skill_second_{id}"
 141 SECOND_SLOT_TABLE_NAME = u"skill_second_slot"
 142 SECOND_COL_AMULET_ID = u"amulet_id"
 143 SECOND_COL_SECOND_TABLE_NAME = u"second_table_name"
 144 SECOND_COL_RANDOM_SEED = u"random_seed"
 145 SECOND_COL_SKILL_ID1 = u"skill_id1"
 146 SECOND_COL_SKILL_ID2 = u"skill_id2"
 147 SECOND_COL_SKILL_ID3 = u"skill_id3"
 148 SECOND_COL_SKILL_ID4 = u"skill_id4"
 149 SECOND_COL_SKILL_ID5 = u"skill_id5"
 150 SECOND_COL_SKILL_ID6 = u"skill_id6"
 151 SECOND_COL_SKILL_ID7 = u"skill_id7"
 152 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]
 153 SECOND_COL_SLOT1 = u"slot1"
 154 SECOND_COL_SLOT2 = u"slot2"
 155 SECOND_COL_SLOT3 = u"slot3"
 156 SECOND_COL_SLOT4 = u"slot4"
 157 SECOND_COL_SLOT5 = u"slot5"
 158 SECOND_COL_SLOT6 = u"slot6"
 159 SECOND_COL_SLOT7 = u"slot7"
 160 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]
 161 SECOND_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
 162     (id integer primary key, {amulet_id} integer, {table_name} varchar,
 163     foreign key(amulet_id) references {amulet_table}(id));""".format(
 164         master=SECOND_MASTER_TABLE_NAME,
 165         amulet_id=SECOND_COL_AMULET_ID,
 166         amulet_table=AMULET_TABLE_NAME,
 167         table_name=SECOND_COL_SECOND_TABLE_NAME)
 168 SECOND_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
 169     (id integer primary key, {seed} integer unique,
 170     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer,
 171     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer,
 172     foreign key({skill_id1}) references {skill_table}(id),
 173     foreign key({skill_id2}) references {skill_table}(id),
 174     foreign key({skill_id3}) references {skill_table}(id),
 175     foreign key({skill_id4}) references {skill_table}(id),
 176     foreign key({skill_id5}) references {skill_table}(id),
 177     foreign key({skill_id6}) references {skill_table}(id),
 178     foreign key({skill_id7}) references {skill_table}(id));""".format(
 179         seed=SECOND_COL_RANDOM_SEED,
 180         skill_id1=SECOND_COL_SKILL_ID1,
 181         skill_id2=SECOND_COL_SKILL_ID2,
 182         skill_id3=SECOND_COL_SKILL_ID3,
 183         skill_id4=SECOND_COL_SKILL_ID4,
 184         skill_id5=SECOND_COL_SKILL_ID5,
 185         skill_id6=SECOND_COL_SKILL_ID6,
 186         skill_id7=SECOND_COL_SKILL_ID7,
 187         skill_table=SKILL_TABLE_NAME)
 188 SECOND_SLOT_TABLE_CREATE_SQL = u"""create table if not exists {table}
 189     (id integer primary key, {seed} integer unique,
 190     {slot1} integer, {slot2} integer, {slot3} integer,
 191     {slot4} integer, {slot5} integer, {slot6} integer, {slot7} integer);""".format(
 192         table=SECOND_SLOT_TABLE_NAME,
 193         seed=SECOND_COL_RANDOM_SEED,
 194         slot1=SECOND_COL_SLOT1,
 195         slot2=SECOND_COL_SLOT2,
 196         slot3=SECOND_COL_SLOT3,
 197         slot4=SECOND_COL_SLOT4,
 198         slot5=SECOND_COL_SLOT5,
 199         slot6=SECOND_COL_SLOT6,
 200         slot7=SECOND_COL_SLOT7)
 201 SECOND_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
 202     ({amulet_col}, {table_col}) values(?,?);""".format(
 203         table=SECOND_MASTER_TABLE_NAME,
 204         table_col=SECOND_COL_SECOND_TABLE_NAME,
 205         amulet_col=SECOND_COL_AMULET_ID)
 206 SECOND_TABLE_INSERT_SQL = u"""insert into {{table_name}}
 207     ({seed}, {skill_id1}, {skill_id2}, {skill_id3},
 208     {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) values(?,?,?,?,?,?,?,?)""".format(
 209         seed=SECOND_COL_RANDOM_SEED,
 210         skill_id1=SECOND_COL_SKILL_ID1,
 211         skill_id2=SECOND_COL_SKILL_ID2,
 212         skill_id3=SECOND_COL_SKILL_ID3,
 213         skill_id4=SECOND_COL_SKILL_ID4,
 214         skill_id5=SECOND_COL_SKILL_ID5,
 215         skill_id6=SECOND_COL_SKILL_ID6,
 216         skill_id7=SECOND_COL_SKILL_ID7)
 217 SECOND_SLOT_TABLE_INSERT_SQL = u"""insert into {table}
 218     ({seed}, {slot1}, {slot2}, {slot3}, {slot4}, {slot5}, {slot6}, {slot7})
 219     values(?,?,?,?,?,?,?,?);""".format(
 220         table=SECOND_SLOT_TABLE_NAME,
 221         seed=SECOND_COL_RANDOM_SEED,
 222         slot1=SECOND_COL_SLOT1,
 223         slot2=SECOND_COL_SLOT2,
 224         slot3=SECOND_COL_SLOT3,
 225         slot4=SECOND_COL_SLOT4,
 226         slot5=SECOND_COL_SLOT5,
 227         slot6=SECOND_COL_SLOT6,
 228         slot7=SECOND_COL_SLOT7)
 229 SECOND_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
 230         amu_id=SECOND_COL_AMULET_ID,
 231         table_col=SECOND_COL_SECOND_TABLE_NAME,
 232         table=SECOND_MASTER_TABLE_NAME)
 233 SECOND_TABLE_SELECT_SEED_SQL = u"""select {seed} from {{table_name}} """.format(
 234         seed=SECOND_COL_RANDOM_SEED)
 235 SECOND_TABLE_SELECT_ALL_SQL = u"""select {seed}, {skill_id1}, {skill_id2},
 236     {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} """.format(
 237         seed=SECOND_COL_RANDOM_SEED,
 238         skill_id1=SECOND_COL_SKILL_ID1,
 239         skill_id2=SECOND_COL_SKILL_ID2,
 240         skill_id3=SECOND_COL_SKILL_ID3,
 241         skill_id4=SECOND_COL_SKILL_ID4,
 242         skill_id5=SECOND_COL_SKILL_ID5,
 243         skill_id6=SECOND_COL_SKILL_ID6,
 244         skill_id7=SECOND_COL_SKILL_ID7)
 245 SECOND_SLOT_TABLE_SELECT_ALL_SQL = u"""select {seed}, {slot1}, {slot2},
 246     {slot3}, {slot4}, {slot5}, {slot6}, {slot7} from {table} """.format(
 247         table=SECOND_SLOT_TABLE_NAME,
 248         seed=SECOND_COL_RANDOM_SEED,
 249         slot1=SECOND_COL_SLOT1,
 250         slot2=SECOND_COL_SLOT2,
 251         slot3=SECOND_COL_SLOT3,
 252         slot4=SECOND_COL_SLOT4,
 253         slot5=SECOND_COL_SLOT5,
 254         slot6=SECOND_COL_SLOT6,
 255         slot7=SECOND_COL_SLOT7)
 256 SECOND_SLOT_TABLE_SELECT_SEEDS_FROM_THRESHOLD_AND_SKILL2_SQL = u"""
 257     select {slot_table}.{seed} from {slot_table}
 258     inner join {{skill_table}} on {slot_table}.{seed} = {{skill_table}}.{seed}
 259     where ({{skill_table}}.{skill_id1} == {{skill_id}} and {slot_table}.{slot1} >= {{threshold}}) or
 260     ({{skill_table}}.{skill_id2} == {{skill_id}} and {slot_table}.{slot2} >= {{threshold}}) or
 261     ({{skill_table}}.{skill_id3} == {{skill_id}} and {slot_table}.{slot3} >= {{threshold}}) or
 262     ({{skill_table}}.{skill_id4} == {{skill_id}} and {slot_table}.{slot4} >= {{threshold}}) or
 263     ({{skill_table}}.{skill_id5} == {{skill_id}} and {slot_table}.{slot5} >= {{threshold}}) or
 264     ({{skill_table}}.{skill_id6} == {{skill_id}} and {slot_table}.{slot6} >= {{threshold}}) or
 265     ({{skill_table}}.{skill_id7} == {{skill_id}} and {slot_table}.{slot7} >= {{threshold}})
 266     """.format(
 267         slot_table=SECOND_SLOT_TABLE_NAME,
 268         seed=SECOND_COL_RANDOM_SEED,
 269         skill_id1=SECOND_COL_SKILL_ID1,
 270         skill_id2=SECOND_COL_SKILL_ID2,
 271         skill_id3=SECOND_COL_SKILL_ID3,
 272         skill_id4=SECOND_COL_SKILL_ID4,
 273         skill_id5=SECOND_COL_SKILL_ID5,
 274         skill_id6=SECOND_COL_SKILL_ID6,
 275         skill_id7=SECOND_COL_SKILL_ID7,
 276         slot1=SECOND_COL_SLOT1,
 277         slot2=SECOND_COL_SLOT2,
 278         slot3=SECOND_COL_SLOT3,
 279         slot4=SECOND_COL_SLOT4,
 280         slot5=SECOND_COL_SLOT5,
 281         slot6=SECOND_COL_SLOT6,
 282         slot7=SECOND_COL_SLOT7)
 283
 284 # for sufficient value for slots
 285 SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master"
 286 SUFFICIENT_TABLE_NAME = u"sufficient_{id}"
 287 SUFFICIENT_COL_AMULET_ID = u"amulet_id"
 288 SUFFICIENT_COL_SUFFICIENT_TABLE_NAME = u"sufficient_table_name"
 289 SUFFICIENT_COL_SUFFICIENT_VALUE = u"sufficient_value"
 290 SUFFICIENT_COL_SLOT1_THRESHOLD = u"slot1_threshold"
 291 SUFFICIENT_COL_SLOT2_THRESHOLD = u"slot2_threshold"
 292 SUFFICIENT_COL_SLOT3_THRESHOLD = u"slot3_threshold"
 293 SUFFICIENT_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
 294     (id integer primary key, {amulet_id} integer, {table_name} varchar,
 295     foreign key(amulet_id) references {amulet_table}(id));""".format(
 296         master=SUFFICIENT_MASTER_TABLE_NAME,
 297         amulet_id=SUFFICIENT_COL_AMULET_ID,
 298         amulet_table=AMULET_TABLE_NAME,
 299         table_name=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME)
 300 SUFFICIENT_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}}
 301     (id integer primary key, {sufficient_val} integer unique,
 302     {slot1_val} integer, {slot2_val} integer, {slot3_val} integer) ;""".format(
 303         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
 304         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
 305         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
 306         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
 307 SUFFICIENT_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
 308     ({amulet_col}, {table_col}) values(?,?);""".format(
 309         table=SUFFICIENT_MASTER_TABLE_NAME,
 310         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
 311         amulet_col=SUFFICIENT_COL_AMULET_ID)
 312 SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}}
 313     ({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val})
 314     values(?,?,?,?);""".format(
 315         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
 316         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
 317         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
 318         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
 319 SUFFICIENT_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
 320         amu_id=SUFFICIENT_COL_AMULET_ID,
 321         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
 322         table=SUFFICIENT_MASTER_TABLE_NAME)
 323 SUFFICIENT_TABLE_SELECT_ALL_SQL = u"""select {sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}""".format(
 324         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
 325         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
 326         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
 327         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
 328 SUFFICIENT_TABLE_SELECT_THRESHOLDS_SQL = u"""select {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}
 329     where {sufficient_val}={{sufficient_val}}""".format(
 330         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
 331         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
 332         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
 333         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
 334