OSDN Git Service

Merge remote-tracking branch 'origin/develop' into develop
[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 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
114
115 # for Second skill
116 SECOND_MASTER_TABLE_NAME = u"skill_second_master"
117 SECOND_TABLE_NAME = u"skill_second_{id}"
118 SECOND_SLOT_TABLE_NAME = u"skill_second_slot"
119 SECOND_COL_AMULET_ID = u"amulet_id"
120 SECOND_COL_SECOND_TABLE_NAME = u"second_table_name"
121 SECOND_COL_RANDOM_SEED = u"random_seed"
122 SECOND_COL_SKILL_ID1 = u"skill_id1"
123 SECOND_COL_SKILL_ID2 = u"skill_id2"
124 SECOND_COL_SKILL_ID3 = u"skill_id3"
125 SECOND_COL_SKILL_ID4 = u"skill_id4"
126 SECOND_COL_SKILL_ID5 = u"skill_id5"
127 SECOND_COL_SKILL_ID6 = u"skill_id6"
128 SECOND_COL_SKILL_ID7 = u"skill_id7"
129 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]
130 SECOND_COL_SLOT1 = u"slot1"
131 SECOND_COL_SLOT2 = u"slot2"
132 SECOND_COL_SLOT3 = u"slot3"
133 SECOND_COL_SLOT4 = u"slot4"
134 SECOND_COL_SLOT5 = u"slot5"
135 SECOND_COL_SLOT6 = u"slot6"
136 SECOND_COL_SLOT7 = u"slot7"
137 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]
138 SECOND_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
139     (id integer primary key, {amulet_id} integer, {table_name} varchar,
140     foreign key(amulet_id) references {amulet_table}(id));""".format(
141         master=SECOND_MASTER_TABLE_NAME,
142         amulet_id=SECOND_COL_AMULET_ID,
143         amulet_table=AMULET_TABLE_NAME,
144         table_name=SECOND_COL_SECOND_TABLE_NAME)
145 SECOND_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
146     (id integer primary key, {seed} integer unique, 
147     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer, 
148     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer, 
149     foreign key({skill_id1}) references {skill_table}(id),
150     foreign key({skill_id2}) references {skill_table}(id),
151     foreign key({skill_id3}) references {skill_table}(id),
152     foreign key({skill_id4}) references {skill_table}(id),
153     foreign key({skill_id5}) references {skill_table}(id),
154     foreign key({skill_id6}) references {skill_table}(id),
155     foreign key({skill_id7}) references {skill_table}(id));""".format(
156         seed=SECOND_COL_RANDOM_SEED,
157         skill_id1=SECOND_COL_SKILL_ID1,
158         skill_id2=SECOND_COL_SKILL_ID2,
159         skill_id3=SECOND_COL_SKILL_ID3,
160         skill_id4=SECOND_COL_SKILL_ID4,
161         skill_id5=SECOND_COL_SKILL_ID5,
162         skill_id6=SECOND_COL_SKILL_ID6,
163         skill_id7=SECOND_COL_SKILL_ID7,
164         skill_table=SKILL_TABLE_NAME)
165 SECOND_SLOT_TABLE_CREATE_SQL = u"""create table if not exists {table} 
166     (id integer primary key, {seed} integer unique,
167     {slot1} integer, {slot2} integer, {slot3} integer,
168     {slot4} integer, {slot5} integer, {slot6} integer, {slot7} integer);""".format(
169         table=SECOND_SLOT_TABLE_NAME,
170         seed=SECOND_COL_RANDOM_SEED,
171         slot1=SECOND_COL_SLOT1,
172         slot2=SECOND_COL_SLOT2,
173         slot3=SECOND_COL_SLOT3,
174         slot4=SECOND_COL_SLOT4,
175         slot5=SECOND_COL_SLOT5,
176         slot6=SECOND_COL_SLOT6,
177         slot7=SECOND_COL_SLOT7)
178 SECOND_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
179     ({amulet_col}, {table_col}) values(?,?);""".format(
180         table=SECOND_MASTER_TABLE_NAME,
181         table_col=SECOND_COL_SECOND_TABLE_NAME,
182         amulet_col=SECOND_COL_AMULET_ID)
183 SECOND_TABLE_INSERT_SQL = u"""insert into {{table_name}}
184     ({seed}, {skill_id1}, {skill_id2}, {skill_id3}, 
185     {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) values(?,?,?,?,?,?,?,?)""".format(
186         seed=SECOND_COL_RANDOM_SEED,
187         skill_id1=SECOND_COL_SKILL_ID1,
188         skill_id2=SECOND_COL_SKILL_ID2,
189         skill_id3=SECOND_COL_SKILL_ID3,
190         skill_id4=SECOND_COL_SKILL_ID4,
191         skill_id5=SECOND_COL_SKILL_ID5,
192         skill_id6=SECOND_COL_SKILL_ID6,
193         skill_id7=SECOND_COL_SKILL_ID7)
194 SECOND_SLOT_TABLE_INSERT_SQL = u"""insert into {table}
195     ({seed}, {slot1}, {slot2}, {slot3}, {slot4}, {slot5}, {slot6}, {slot7})
196     values(?,?,?,?,?,?,?,?);""".format(
197         table=SECOND_SLOT_TABLE_NAME,
198         seed=SECOND_COL_RANDOM_SEED,
199         slot1=SECOND_COL_SLOT1,
200         slot2=SECOND_COL_SLOT2,
201         slot3=SECOND_COL_SLOT3,
202         slot4=SECOND_COL_SLOT4,
203         slot5=SECOND_COL_SLOT5,
204         slot6=SECOND_COL_SLOT6,
205         slot7=SECOND_COL_SLOT7)
206 SECOND_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
207         amu_id=SECOND_COL_AMULET_ID,
208         table_col=SECOND_COL_SECOND_TABLE_NAME,
209         table=SECOND_MASTER_TABLE_NAME)
210 SECOND_TABLE_SELECT_SEED_SQL = u"""select {seed} from {{table_name}} """.format(
211         seed=SECOND_COL_RANDOM_SEED)
212 SECOND_TABLE_SELECT_ALL_SQL = u"""select {seed}, {skill_id1}, {skill_id2}, 
213     {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} """.format(
214         seed=SECOND_COL_RANDOM_SEED,
215         skill_id1=SECOND_COL_SKILL_ID1,
216         skill_id2=SECOND_COL_SKILL_ID2,
217         skill_id3=SECOND_COL_SKILL_ID3,
218         skill_id4=SECOND_COL_SKILL_ID4,
219         skill_id5=SECOND_COL_SKILL_ID5,
220         skill_id6=SECOND_COL_SKILL_ID6,
221         skill_id7=SECOND_COL_SKILL_ID7)
222 SECOND_SLOT_TABLE_SELECT_ALL_SQL = u"""select {seed}, {slot1}, {slot2},
223     {slot3}, {slot4}, {slot5}, {slot6}, {slot7} from {table} """.format(
224         table=SECOND_SLOT_TABLE_NAME,
225         seed=SECOND_COL_RANDOM_SEED,
226         slot1=SECOND_COL_SLOT1,
227         slot2=SECOND_COL_SLOT2,
228         slot3=SECOND_COL_SLOT3,
229         slot4=SECOND_COL_SLOT4,
230         slot5=SECOND_COL_SLOT5,
231         slot6=SECOND_COL_SLOT6,
232         slot7=SECOND_COL_SLOT7)
233
234 # for sufficient value for slots
235 SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master"
236 SUFFICIENT_TABLE_NAME = u"sufficient_{id}"
237 SUFFICIENT_COL_AMULET_ID = u"amulet_id"
238 SUFFICIENT_COL_SUFFICIENT_TABLE_NAME = u"sufficient_table_name"
239 SUFFICIENT_COL_SUFFICIENT_VALUE = u"sufficient_value"
240 SUFFICIENT_COL_SLOT1_THRESHOLD = u"slot1_threshold"
241 SUFFICIENT_COL_SLOT2_THRESHOLD = u"slot2_threshold"
242 SUFFICIENT_COL_SLOT3_THRESHOLD = u"slot3_threshold"
243 SUFFICIENT_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
244     (id integer primary key, {amulet_id} integer, {table_name} varchar, 
245     foreign key(amulet_id) references {amulet_table}(id));""".format(
246         master=SUFFICIENT_MASTER_TABLE_NAME,
247         amulet_id=SUFFICIENT_COL_AMULET_ID,
248         amulet_table=AMULET_TABLE_NAME,
249         table_name=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME)
250 SUFFICIENT_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
251     (id integer primary key, {sufficient_val} integer unique, 
252     {slot1_val} integer, {slot2_val} integer, {slot3_val} integer) ;""".format(
253         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
254         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
255         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
256         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
257 SUFFICIENT_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
258     ({amulet_col}, {table_col}) values(?,?);""".format(
259         table=SUFFICIENT_MASTER_TABLE_NAME,
260         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
261         amulet_col=SUFFICIENT_COL_AMULET_ID)
262 SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}}
263     ({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val})
264     values(?,?,?,?);""".format(
265         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
266         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
267         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
268         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
269 SUFFICIENT_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
270         amu_id=SUFFICIENT_COL_AMULET_ID,
271         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
272         table=SUFFICIENT_MASTER_TABLE_NAME)
273 SUFFICIENT_TABLE_SELECT_ALL_SQL = u"""select {sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}""".format(
274         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
275         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
276         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
277         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)