OSDN Git Service

create amulet id table
[amulettoolsmh4/main.git] / 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 DATA_DIRECTORY = "data"
14 OUTPUT_DB_FILE_NAME = "OmamoriMH4.sqlite3"
15 SKILL_FILE_NAME = "skill.csv"
16 AMULET_FILE_NAME = "amulet.csv"
17 MIN_MAX_FILE_NAME = "minmax.csv"
18 SUFFICIENT_FILE_NAME = "sufficient.csv"
19 SECOND_SKILL_FILE_NAME = "2ndskill.csv"
20 SECOND_SLOT_FILE_NAME = "2ndskill_slot.csv"
21
22 # --- sql sentences of database ---
23 # for skill table
24 SKILL_TABLE_NAME = u"skill_table"
25 SKILL_TABLE_COL_SKILL_NAME = u"skill_name"
26 SKILL_TABLE_CREATE_SQL = u"""create table if not exists {table} 
27     (id integer primary key, {skill_name} varchar unique);""".format(
28         table=SKILL_TABLE_NAME, 
29         skill_name=SKILL_TABLE_COL_SKILL_NAME)
30 SKILL_TABLE_INSERT_SQL = u"""insert into {table} 
31     ({skill_col}) values(?);""".format(
32         table=SKILL_TABLE_NAME,
33         skill_col=SKILL_TABLE_COL_SKILL_NAME)
34 SKILL_TABLE_ID2SKILL_SQL = u"""select {skill_col} from {table} where id='{{id}}'""".format(
35         skill_col=SKILL_TABLE_COL_SKILL_NAME,
36         table=SKILL_TABLE_NAME)
37 SKILL_TABLE_SKILL2ID_SQL = u"""select id from {table} where {skill_col}='{{skill_name}}'""".format(
38         skill_col=SKILL_TABLE_COL_SKILL_NAME,
39         table=SKILL_TABLE_NAME)
40
41 # for amulet table
42 AMULET_TABLE_NAME = u"amulet_table"
43 AMULET_TABLE_COL_AMULET_NAME = u"amulet_name"
44 AMULET_TABLE_CREATE_SQL = u"""create table if not exists {table} 
45     (id integer primary key, {amulet_name} varchar unique);""".format(
46         table=AMULET_TABLE_NAME, 
47         amulet_name=AMULET_TABLE_COL_AMULET_NAME)
48 AMULET_TABLE_INSERT_SQL = u"""insert into {table} 
49     ({amulet_col}) values(?);""".format(
50         table=AMULET_TABLE_NAME,
51         amulet_col=AMULET_TABLE_COL_AMULET_NAME)
52 AMULET_TABLE_ID2AMULET_SQL = u"""select {amulet_col} from {table} where id='{{id}}'""".format(
53         amulet_col=AMULET_TABLE_COL_AMULET_NAME,
54         table=AMULET_TABLE_NAME)
55 AMULET_TABLE_AMULET2ID_SQL = u"""select id from {table} where {amulet_col}='{{amulet_name}}'""".format(
56         amulet_col=AMULET_TABLE_COL_AMULET_NAME,
57         table=AMULET_TABLE_NAME)
58
59 # for minmax of skill
60 MIN_MAX_MASTER_TABLE_NAME = u"skill_minmax_master"
61 MIN_MAX_TABLE_NAME = u"skill_minmax_{id}"
62 MIN_MAX_COL_AMULET_ID = u"amulet_id"
63 MIN_MAX_COL_SKILL_TABLE_NAME = u"skill_table_name"
64 MIN_MAX_COL_MIN1 = u"min1"
65 MIN_MAX_COL_MIN2 = u"min2"
66 MIN_MAX_COL_MAX1 = u"max1"
67 MIN_MAX_COL_MAX2 = u"max2"
68 MIN_MAX_COL_SKILL_ID = u"skill_id"
69 MIN_MAX_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
70     (id integer primary key, {amulet_id} integer, {table_name} varchar,
71     foreign key(amulet_id) references {amulet_table}(id));""".format(
72         master=MIN_MAX_MASTER_TABLE_NAME,
73         amulet_id=MIN_MAX_COL_AMULET_ID,
74         amulet_table=AMULET_TABLE_NAME,
75         table_name=MIN_MAX_COL_SKILL_TABLE_NAME)
76 MIN_MAX_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
77     (id integer primary key, {skill_id} integer,
78     {min1} integer, {max1} integer, {min2} integer, {max2} integer, 
79     foreign key(skill_id) references {skill_table}(id));""".format(
80         skill_id=MIN_MAX_COL_SKILL_ID,
81         min1=MIN_MAX_COL_MIN1,
82         min2=MIN_MAX_COL_MIN2,
83         max1=MIN_MAX_COL_MAX1,
84         max2=MIN_MAX_COL_MAX2,
85         skill_table=SKILL_TABLE_NAME)
86 MIN_MAX_MASTER_TABLE_INSERT_SQL = u"""insert into {master}
87     ({amulet_id}, {table_col}) values(?,?);""".format(
88         master=MIN_MAX_MASTER_TABLE_NAME,
89         table_col=MIN_MAX_COL_SKILL_TABLE_NAME,
90         amulet_id=MIN_MAX_COL_AMULET_ID)
91 MIN_MAX_TABLE_INSERT_SQL = u"""insert into {{table_name}}
92     ({skill_id}, {min1}, {max1}, {min2}, {max2}) values(?,?,?,?,?)""".format(
93         skill_id=MIN_MAX_COL_SKILL_ID,
94         min1=MIN_MAX_COL_MIN1,
95         min2=MIN_MAX_COL_MIN2,
96         max1=MIN_MAX_COL_MAX1,
97         max2=MIN_MAX_COL_MAX2)
98
99 # for Second skill
100 SECOND_MASTER_TABLE_NAME = u"skill_second_master"
101 SECOND_TABLE_NAME = u"skill_second_{id}"
102 SECOND_SLOT_TABLE_NAME = u"skill_second_slot"
103 SECOND_COL_AMULET_ID = u"amulet_id"
104 SECOND_COL_SECOND_TABLE_NAME = u"second_table_name"
105 SECOND_COL_RANDOM_SEED = u"random_seed"
106 SECOND_COL_SKILL_ID1 = u"skill_id1"
107 SECOND_COL_SKILL_ID2 = u"skill_id2"
108 SECOND_COL_SKILL_ID3 = u"skill_id3"
109 SECOND_COL_SKILL_ID4 = u"skill_id4"
110 SECOND_COL_SKILL_ID5 = u"skill_id5"
111 SECOND_COL_SKILL_ID6 = u"skill_id6"
112 SECOND_COL_SKILL_ID7 = u"skill_id7"
113 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]
114 SECOND_COL_SLOT1 = u"slot1"
115 SECOND_COL_SLOT2 = u"slot2"
116 SECOND_COL_SLOT3 = u"slot3"
117 SECOND_COL_SLOT4 = u"slot4"
118 SECOND_COL_SLOT5 = u"slot5"
119 SECOND_COL_SLOT6 = u"slot6"
120 SECOND_COL_SLOT7 = u"slot7"
121 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]
122 SECOND_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
123     (id integer primary key, {amulet_id} integer, {table_name} varchar,
124     foreign key(amulet_id) references {amulet_table}(id));""".format(
125         master=SECOND_MASTER_TABLE_NAME,
126         amulet_id=SECOND_COL_AMULET_ID,
127         amulet_table=AMULET_TABLE_NAME,
128         table_name=SECOND_COL_SECOND_TABLE_NAME)
129 SECOND_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
130     (id integer primary key, {seed} integer unique, 
131     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer, 
132     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer, 
133     foreign key({skill_id1}) references {skill_table}(id),
134     foreign key({skill_id2}) references {skill_table}(id),
135     foreign key({skill_id3}) references {skill_table}(id),
136     foreign key({skill_id4}) references {skill_table}(id),
137     foreign key({skill_id5}) references {skill_table}(id),
138     foreign key({skill_id6}) references {skill_table}(id),
139     foreign key({skill_id7}) references {skill_table}(id));""".format(
140         seed=SECOND_COL_RANDOM_SEED,
141         skill_id1=SECOND_COL_SKILL_ID1,
142         skill_id2=SECOND_COL_SKILL_ID2,
143         skill_id3=SECOND_COL_SKILL_ID3,
144         skill_id4=SECOND_COL_SKILL_ID4,
145         skill_id5=SECOND_COL_SKILL_ID5,
146         skill_id6=SECOND_COL_SKILL_ID6,
147         skill_id7=SECOND_COL_SKILL_ID7,
148         skill_table=SKILL_TABLE_NAME)
149 SECOND_SLOT_TABLE_CREATE_SQL = u"""create table if not exists {table} 
150     (id integer primary key, {seed} integer unique,
151     {slot1} integer, {slot2} integer, {slot3} integer,
152     {slot4} integer, {slot5} integer, {slot6} integer, {slot7} integer);""".format(
153         table=SECOND_SLOT_TABLE_NAME,
154         seed=SECOND_COL_RANDOM_SEED,
155         slot1=SECOND_COL_SLOT1,
156         slot2=SECOND_COL_SLOT2,
157         slot3=SECOND_COL_SLOT3,
158         slot4=SECOND_COL_SLOT4,
159         slot5=SECOND_COL_SLOT5,
160         slot6=SECOND_COL_SLOT6,
161         slot7=SECOND_COL_SLOT7)
162 SECOND_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
163     ({amulet_col}, {table_col}) values(?,?);""".format(
164         table=SECOND_MASTER_TABLE_NAME,
165         table_col=SECOND_COL_SECOND_TABLE_NAME,
166         amulet_col=SECOND_COL_AMULET_ID)
167 SECOND_TABLE_INSERT_SQL = u"""insert into {{table_name}}
168     ({seed}, {skill_id1}, {skill_id2}, {skill_id3}, 
169     {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) values(?,?,?,?,?,?,?,?)""".format(
170         seed=SECOND_COL_RANDOM_SEED,
171         skill_id1=SECOND_COL_SKILL_ID1,
172         skill_id2=SECOND_COL_SKILL_ID2,
173         skill_id3=SECOND_COL_SKILL_ID3,
174         skill_id4=SECOND_COL_SKILL_ID4,
175         skill_id5=SECOND_COL_SKILL_ID5,
176         skill_id6=SECOND_COL_SKILL_ID6,
177         skill_id7=SECOND_COL_SKILL_ID7)
178 SECOND_SLOT_TABLE_INSERT_SQL = u"""insert into {table}
179     ({seed}, {slot1}, {slot2}, {slot3}, {slot4}, {slot5}, {slot6}, {slot7})
180     values(?,?,?,?,?,?,?,?);""".format(
181         table=SECOND_SLOT_TABLE_NAME,
182         seed=SECOND_COL_RANDOM_SEED,
183         slot1=SECOND_COL_SLOT1,
184         slot2=SECOND_COL_SLOT2,
185         slot3=SECOND_COL_SLOT3,
186         slot4=SECOND_COL_SLOT4,
187         slot5=SECOND_COL_SLOT5,
188         slot6=SECOND_COL_SLOT6,
189         slot7=SECOND_COL_SLOT7)
190
191 # for sufficient value for slots
192 SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master"
193 SUFFICIENT_TABLE_NAME = u"sufficient_{id}"
194 SUFFICIENT_COL_AMULET_ID = u"amulet_id"
195 SUFFICIENT_COL_SUFFICIENT_TABLE_NAME = u"sufficient_table_name"
196 SUFFICIENT_COL_SUFFICIENT_VALUE = u"sufficient_value"
197 SUFFICIENT_COL_SLOT1_THRESHOLD = u"slot1_threshold"
198 SUFFICIENT_COL_SLOT2_THRESHOLD = u"slot2_threshold"
199 SUFFICIENT_COL_SLOT3_THRESHOLD = u"slot3_threshold"
200 SUFFICIENT_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
201     (id integer primary key, {amulet_id} integer, {table_name} varchar, 
202     foreign key(amulet_id) references {amulet_table}(id));""".format(
203         master=SUFFICIENT_MASTER_TABLE_NAME,
204         amulet_id=SUFFICIENT_COL_AMULET_ID,
205         amulet_table=AMULET_TABLE_NAME,
206         table_name=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME)
207 SUFFICIENT_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
208     (id integer primary key, {sufficient_val} integer unique, 
209     {slot1_val} integer, {slot2_val} integer, {slot3_val} integer) ;""".format(
210         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
211         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
212         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
213         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
214 SUFFICIENT_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
215     ({amulet_col}, {table_col}) values(?,?);""".format(
216         table=SUFFICIENT_MASTER_TABLE_NAME,
217         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
218         amulet_col=SUFFICIENT_COL_AMULET_ID)
219 SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}}
220     ({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val})
221     values(?,?,?,?);""".format(
222         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
223         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
224         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
225         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
226