OSDN Git Service

b8bf68f363301cdb7019dd2ef09c2f060c753d5d
[amulettoolsmh4/main.git] / model / db_supports.py
1 # -*- coding: utf-8 -*-
2
3 # constant numbers of database
4 # データベース関連の定数値など
5 # 2013/12/14 written by kei9 
6
7 # import modules
8
9 # --- filenames ---
10 ZIP_FILE = u"data.zip"
11 DB_FILE_NAME = u"OmamoriMH4.sqlite3"
12 SKILL_FILE_NAME = u"skill.csv"
13 AMULET_FILE_NAME = u"amulet.csv"
14 MIN_MAX_FILE_NAME = u"minmax.csv"
15 SUFFICIENT_FILE_NAME = u"sufficient.csv"
16 SEED2_SKILL2_FILE_NAME = u"seed2_skill2.csv"
17 SEED2_THRESHOLD1_FILE_NAME = u"seed2_threshold1.csv"
18 SEED2_THRESHOLD2_FILE_NAME = u"seed2_threshold2.csv"
19 SEED2_TABLE_NO_FILE_NAME = u"seed2_table.csv"
20
21 SEED2_INISHIE_FILE_NAME = u"seed2_inishie.csv"
22 SEED1_TENUN555_FILE_NAME = "seed1_tenun555.csv"
23 SEED1_TENUN888_FILE_NAME = "seed1_tenun888.csv"
24
25 # no data
26 NO_DATA = -1
27
28 #tenun
29 KEY_TENUN555 = u"tenun555"
30 KEY_TENUN888 = u"tenun888"
31
32 # inishie amulet
33 INISHIE_AMULET = u"古びたお守り"
34
35 # --- sql sentences of database ---
36 # for skill table
37 SKILL_TABLE_NAME = u"skill_table"
38 SKILL_TABLE_COL_SKILL_NAME = u"skill_name"
39 SKILL_TABLE_CREATE_SQL = u"""create table if not exists {table} 
40     (id integer primary key, {skill_name} varchar unique);""".format(
41         table=SKILL_TABLE_NAME, 
42         skill_name=SKILL_TABLE_COL_SKILL_NAME)
43 SKILL_TABLE_INSERT_SQL = u"""insert into {table} 
44     ({skill_col}) values(?);""".format(
45         table=SKILL_TABLE_NAME,
46         skill_col=SKILL_TABLE_COL_SKILL_NAME)
47 SKILL_TABLE_ID2SKILL_SQL = u"""select {skill_col} from {table} where id='{{id}}'""".format(
48         skill_col=SKILL_TABLE_COL_SKILL_NAME,
49         table=SKILL_TABLE_NAME)
50 SKILL_TABLE_SKILL2ID_SQL = u"""select id from {table} where {skill_col}='{{skill_name}}'""".format(
51         skill_col=SKILL_TABLE_COL_SKILL_NAME,
52         table=SKILL_TABLE_NAME)
53 SKILL_TABLE_SELECT_ALL_SQL = u"""select id, {skill_col} from {table}""".format(
54         skill_col=SKILL_TABLE_COL_SKILL_NAME,
55         table=SKILL_TABLE_NAME)
56
57 # for amulet table
58 AMULET_TABLE_NAME = u"amulet_table"
59 AMULET_TABLE_COL_AMULET_NAME = u"amulet_name"
60 AMULET_TABLE_CREATE_SQL = u"""create table if not exists {table} 
61     (id integer primary key, {amulet_name} varchar unique);""".format(
62         table=AMULET_TABLE_NAME, 
63         amulet_name=AMULET_TABLE_COL_AMULET_NAME)
64 AMULET_TABLE_INSERT_SQL = u"""insert into {table}
65     ({amulet_col}) values(?);""".format(
66         table=AMULET_TABLE_NAME,
67         amulet_col=AMULET_TABLE_COL_AMULET_NAME)
68 AMULET_TABLE_ID2AMULET_SQL = u"""select {amulet_col} from {table} where id='{{id}}'""".format(
69         amulet_col=AMULET_TABLE_COL_AMULET_NAME,
70         table=AMULET_TABLE_NAME)
71 AMULET_TABLE_AMULET2ID_SQL = u"""select id from {table} where {amulet_col}='{{amulet_name}}'""".format(
72         amulet_col=AMULET_TABLE_COL_AMULET_NAME,
73         table=AMULET_TABLE_NAME)
74 AMULET_TABLE_SELECT_ALL_SQL = u"""select id, {amulet_col} from {table}""".format(
75         amulet_col=AMULET_TABLE_COL_AMULET_NAME,
76         table=AMULET_TABLE_NAME)
77
78 # for minmax of skill
79 MIN_MAX_MASTER_TABLE_NAME = u"skill_minmax_master"
80 MIN_MAX_TABLE_NAME = u"skill_minmax_{id}"
81 MIN_MAX_COL_AMULET_ID = u"amulet_id"
82 MIN_MAX_COL_SKILL_TABLE_NAME = u"skill_table_name"
83 MIN_MAX_COL_MIN1 = u"min1"
84 MIN_MAX_COL_MIN2 = u"min2"
85 MIN_MAX_COL_MAX1 = u"max1"
86 MIN_MAX_COL_MAX2 = u"max2"
87 MIN_MAX_COL_SKILL_ID = u"skill_id"
88 MIN_MAX_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
89     (id integer primary key, {amulet_id} integer, {table_name} varchar,
90     foreign key(amulet_id) references {amulet_table}(id));""".format(
91         master=MIN_MAX_MASTER_TABLE_NAME,
92         amulet_id=MIN_MAX_COL_AMULET_ID,
93         amulet_table=AMULET_TABLE_NAME,
94         table_name=MIN_MAX_COL_SKILL_TABLE_NAME)
95 MIN_MAX_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
96     (id integer primary key, {skill_id} integer,
97     {min1} integer, {max1} integer, {min2} integer, {max2} integer, 
98     foreign key(skill_id) references {skill_table}(id));""".format(
99         skill_id=MIN_MAX_COL_SKILL_ID,
100         min1=MIN_MAX_COL_MIN1,
101         min2=MIN_MAX_COL_MIN2,
102         max1=MIN_MAX_COL_MAX1,
103         max2=MIN_MAX_COL_MAX2,
104         skill_table=SKILL_TABLE_NAME)
105 MIN_MAX_MASTER_TABLE_INSERT_SQL = u"""insert into {master}
106     ({amulet_id}, {table_col}) values(?,?);""".format(
107         master=MIN_MAX_MASTER_TABLE_NAME,
108         table_col=MIN_MAX_COL_SKILL_TABLE_NAME,
109         amulet_id=MIN_MAX_COL_AMULET_ID)
110 MIN_MAX_TABLE_INSERT_SQL = u"""insert into {{table_name}}
111     ({skill_id}, {min1}, {max1}, {min2}, {max2}) values(?,?,?,?,?)""".format(
112         skill_id=MIN_MAX_COL_SKILL_ID,
113         min1=MIN_MAX_COL_MIN1,
114         min2=MIN_MAX_COL_MIN2,
115         max1=MIN_MAX_COL_MAX1,
116         max2=MIN_MAX_COL_MAX2)
117 MIN_MAX_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
118         amu_id=MIN_MAX_COL_AMULET_ID,
119         table_col=MIN_MAX_COL_SKILL_TABLE_NAME,
120         table=MIN_MAX_MASTER_TABLE_NAME)
121 MIN_MAX_TABLE_SELECT_ALL_SQL = u"""select {skill_id}, {min1}, {max1}, {min2}, {max2} from {{table_name}} """.format(
122         skill_id=MIN_MAX_COL_SKILL_ID,
123         min1=MIN_MAX_COL_MIN1,
124         max1=MIN_MAX_COL_MAX1,
125         min2=MIN_MAX_COL_MIN2,
126         max2=MIN_MAX_COL_MAX2)
127 MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL1_ID_SQL = u"""select {skill_id} from {{table_name}} 
128     where {min1} != 0 and {max1} != 0""".format(
129         skill_id=MIN_MAX_COL_SKILL_ID,
130         min1=MIN_MAX_COL_MIN1,
131         max1=MIN_MAX_COL_MAX1)
132 MIN_MAX_TABLE_SELECT_NON_ZERO_SKILL2_ID_SQL = u"""select {skill_id} from {{table_name}} 
133     where {min2} != 0 and {max2} != 0""".format(
134         skill_id=MIN_MAX_COL_SKILL_ID,
135         min2=MIN_MAX_COL_MIN2,
136         max2=MIN_MAX_COL_MAX2)
137 MIN_MAX_TABLE_SELECT_MIN1_SQL = u"""select min({col}) from {{table_name}} """.format(
138         col=MIN_MAX_COL_MIN1)
139 MIN_MAX_TABLE_SELECT_MAX1_SQL = u"""select max({col}) from {{table_name}} """.format(
140         col=MIN_MAX_COL_MAX1)
141 MIN_MAX_TABLE_SELECT_MIN2_SQL = u"""select min({col}) from {{table_name}} """.format(
142         col=MIN_MAX_COL_MIN2)
143 MIN_MAX_TABLE_SELECT_MAX2_SQL = u"""select max({col}) from {{table_name}} """.format(
144         col=MIN_MAX_COL_MAX2)
145 MIN_MAX_TABLE_SELECT_MAX1_OF_SKILL_SQL = u"""select max({col}) from {{table_name}} 
146     where skill_id={{skill_id}}""".format(
147         col=MIN_MAX_COL_MAX1)
148 MIN_MAX_TABLE_SELECT_MAX2_OF_SKILL_SQL = u"""select max({col}) from {{table_name}} 
149     where skill_id={{skill_id}}""".format(
150         col=MIN_MAX_COL_MAX2)
151
152 # table no of seed2
153 u""" Seed2とテーブル、No.の表 """
154 SEED2_TABLE_NO_TABLE = u"seed2_table_no"
155 SEED2_TABLE_NO_COL_SEED2 = u"seed2"
156 SEED2_TABLE_NO_COL_TABLE = u"table_no"
157 SEED2_TABLE_NO_COL_NO = u"no"
158 SEED2_TABLE_NO_TABLE_CREATE_SQL = u"""create table if not exists {table_name}
159     ({no} integer primary key, {seed} integer unique, {table} integer);""".format(
160         table_name=SEED2_TABLE_NO_TABLE,
161         seed=SEED2_TABLE_NO_COL_SEED2,
162         no=SEED2_TABLE_NO_COL_NO,
163         table=SEED2_TABLE_NO_COL_TABLE)
164 SEED2_TABLE_NO_TABLE_INSERT_SQL = u"""insert into {table_name}
165     ({no}, {seed}, {table}) values(?,?,?);""".format(
166         table_name=SEED2_TABLE_NO_TABLE,
167         seed=SEED2_TABLE_NO_COL_SEED2,
168         no=SEED2_TABLE_NO_COL_NO,
169         table=SEED2_TABLE_NO_COL_TABLE)
170 SEED2_TABLE_NO_TABLE_SELECT_FROM_SEED2_SQL = u"""select {table_no}, {no} from {table_name}
171     where {seed2}={{seed2}}""".format(
172         table_name=SEED2_TABLE_NO_TABLE,
173         table_no=SEED2_TABLE_NO_COL_TABLE,
174         no=SEED2_TABLE_NO_COL_NO,
175         seed2=SEED2_TABLE_NO_COL_SEED2)
176
177 # for Seed2 to skill2
178 u""" SEED2から各お守りの第2スキルへのテーブル """
179 SEED2_SKILL2_MASTER_TABLE_NAME = u"seed2_skill2_master"
180 SEED2_SKILL2_TABLE_NAME = u"seed2_skill2_{id}"
181 SEED2_SKILL2_COL_AMULET_ID = u"amulet_id"
182 SEED2_SKILL2_COL_SEED2_SKILL2_TABLE_NAME = u"seed2_skill2_table_name"
183 SEED2_SKILL2_COL_SEED2 = u"seed2"
184 SEED2_SKILL2_COL_SKILL2_ID1 = u"skill2_id1"
185 SEED2_SKILL2_COL_SKILL2_ID2 = u"skill2_id2"
186 SEED2_SKILL2_COL_SKILL2_ID3 = u"skill2_id3"
187 SEED2_SKILL2_COL_SKILL2_ID4 = u"skill2_id4"
188 SEED2_SKILL2_COL_SKILL2_ID5 = u"skill2_id5"
189 SEED2_SKILL2_COL_SKILL2_ID6 = u"skill2_id6"
190 SEED2_SKILL2_COL_SKILL2_ID7 = u"skill2_id7"
191 SEED2_SKILL2_COL_SKILL2_LIST = [SEED2_SKILL2_COL_SKILL2_ID1, SEED2_SKILL2_COL_SKILL2_ID2, SEED2_SKILL2_COL_SKILL2_ID3, SEED2_SKILL2_COL_SKILL2_ID4, SEED2_SKILL2_COL_SKILL2_ID5, SEED2_SKILL2_COL_SKILL2_ID6, SEED2_SKILL2_COL_SKILL2_ID7]
192 SEED2_SKILL2_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
193     (id integer primary key, {amulet_id} integer, {table_name} varchar,
194     foreign key(amulet_id) references {amulet_table}(id));""".format(
195         master=SEED2_SKILL2_MASTER_TABLE_NAME,
196         amulet_id=SEED2_SKILL2_COL_AMULET_ID,
197         amulet_table=AMULET_TABLE_NAME,
198         table_name=SEED2_SKILL2_COL_SEED2_SKILL2_TABLE_NAME)
199 SEED2_SKILL2_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
200     (id integer primary key, {seed} integer unique, 
201     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer, 
202     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer, 
203     foreign key({skill_id1}) references {skill_table}(id),
204     foreign key({skill_id2}) references {skill_table}(id),
205     foreign key({skill_id3}) references {skill_table}(id),
206     foreign key({skill_id4}) references {skill_table}(id),
207     foreign key({skill_id5}) references {skill_table}(id),
208     foreign key({skill_id6}) references {skill_table}(id),
209     foreign key({skill_id7}) references {skill_table}(id));""".format(
210         seed=SEED2_SKILL2_COL_SEED2,
211         skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
212         skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
213         skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
214         skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
215         skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
216         skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
217         skill_id7=SEED2_SKILL2_COL_SKILL2_ID7,
218         skill_table=SKILL_TABLE_NAME)
219 SEED2_SKILL2_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
220     ({amulet_col}, {table_col}) values(?,?);""".format(
221         table=SEED2_SKILL2_MASTER_TABLE_NAME,
222         table_col=SEED2_SKILL2_COL_SEED2_SKILL2_TABLE_NAME,
223         amulet_col=SEED2_SKILL2_COL_AMULET_ID)
224 SEED2_SKILL2_TABLE_INSERT_SQL = u"""insert into {{table_name}}
225     ({seed}, {skill_id1}, {skill_id2}, {skill_id3}, 
226     {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7}) values(?,?,?,?,?,?,?,?)""".format(
227         seed=SEED2_SKILL2_COL_SEED2,
228         skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
229         skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
230         skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
231         skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
232         skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
233         skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
234         skill_id7=SEED2_SKILL2_COL_SKILL2_ID7)
235 SEED2_SKILL2_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
236         amu_id=SEED2_SKILL2_COL_AMULET_ID,
237         table_col=SEED2_SKILL2_COL_SEED2_SKILL2_TABLE_NAME,
238         table=SEED2_SKILL2_MASTER_TABLE_NAME)
239 SEED2_SKILL2_TABLE_SELECT_SEED_SQL = u"""select {seed} from {{table_name}} """.format(
240         seed=SEED2_SKILL2_COL_SEED2)
241 SEED2_SKILL2_TABLE_SELECT_ALL_SQL = u"""select {seed}, {skill_id1}, {skill_id2}, 
242     {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} """.format(
243         seed=SEED2_SKILL2_COL_SEED2,
244         skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
245         skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
246         skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
247         skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
248         skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
249         skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
250         skill_id7=SEED2_SKILL2_COL_SKILL2_ID7)
251 SEED2_SKILL2_TABLE_SELECT_ALL_FROM_SEED2_SQL = u"""select {skill_id1}, {skill_id2}, 
252     {skill_id3}, {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7} from {{table_name}} where {seed}={{seed2}}""".format(
253         seed=SEED2_SKILL2_COL_SEED2,
254         skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
255         skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
256         skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
257         skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
258         skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
259         skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
260         skill_id7=SEED2_SKILL2_COL_SKILL2_ID7)
261 # for seed2 to threshold1
262 u""" SEED2から判定値1へのテーブル """
263 SEED2_THRESHOLD1_TABLE_NAME = u"seed2_threshold1"
264 SEED2_THRESHOLD1_COL_SEED2 = u"seed2"
265 SEED2_THRESHOLD1_COL_THRESHOLD1_1 = u"threshold1_1"
266 SEED2_THRESHOLD1_COL_THRESHOLD1_2 = u"threshold1_2"
267 SEED2_THRESHOLD1_COL_THRESHOLD1_3 = u"threshold1_3"
268 SEED2_THRESHOLD1_COL_THRESHOLD1_4 = u"threshold1_4"
269 SEED2_THRESHOLD1_COL_THRESHOLD1_5 = u"threshold1_5"
270 SEED2_THRESHOLD1_COL_THRESHOLD1_6 = u"threshold1_6"
271 SEED2_THRESHOLD1_COL_THRESHOLD1_7 = u"threshold1_7"
272 SEED2_THRESHOLD1_COL_THRESHOLD1_LIST = [SEED2_THRESHOLD1_COL_THRESHOLD1_1, SEED2_THRESHOLD1_COL_THRESHOLD1_2, SEED2_THRESHOLD1_COL_THRESHOLD1_3, SEED2_THRESHOLD1_COL_THRESHOLD1_4, SEED2_THRESHOLD1_COL_THRESHOLD1_5, SEED2_THRESHOLD1_COL_THRESHOLD1_6, SEED2_THRESHOLD1_COL_THRESHOLD1_7]
273 SEED2_THRESHOLD1_TABLE_CREATE_SQL = u"""create table if not exists {table} 
274     (id integer primary key, {seed} integer unique,
275     {threshold1_1} integer, {threshold1_2} integer, {threshold1_3} integer,
276     {threshold1_4} integer, {threshold1_5} integer, {threshold1_6} integer, {threshold1_7} integer);""".format(
277         table=SEED2_THRESHOLD1_TABLE_NAME,
278         seed=SEED2_THRESHOLD1_COL_SEED2,
279         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
280         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
281         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
282         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
283         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
284         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
285         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
286 SEED2_THRESHOLD1_TABLE_INSERT_SQL = u"""insert into {table}
287     ({seed}, {threshold1_1}, {threshold1_2}, {threshold1_3}, {threshold1_4}, {threshold1_5}, {threshold1_6}, {threshold1_7})
288     values(?,?,?,?,?,?,?,?);""".format(
289         table=SEED2_THRESHOLD1_TABLE_NAME,
290         seed=SEED2_THRESHOLD1_COL_SEED2,
291         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
292         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
293         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
294         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
295         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
296         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
297         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
298 SEED2_THRESHOLD1_TABLE_SELECT_ALL_SQL = u"""select {seed}, {threshold1_1}, {threshold1_2},
299     {threshold1_3}, {threshold1_4}, {threshold1_5}, {threshold1_6}, {threshold1_7} from {table} """.format(
300         table=SEED2_THRESHOLD1_TABLE_NAME,
301         seed=SEED2_THRESHOLD1_COL_SEED2,
302         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
303         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
304         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
305         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
306         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
307         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
308         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
309 SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_SQL = u"""
310     select {seed} from {threshold1_table} 
311     where {threshold1_1} >= {{threshold1}} or 
312     {threshold1_2} >= {{threshold1}} or 
313     {threshold1_3} >= {{threshold1}} or 
314     {threshold1_4} >= {{threshold1}} or 
315     {threshold1_5} >= {{threshold1}} or 
316     {threshold1_6} >= {{threshold1}} or 
317     {threshold1_7} >= {{threshold1}}
318     """.format(
319         threshold1_table=SEED2_THRESHOLD1_TABLE_NAME,
320         seed=SEED2_THRESHOLD1_COL_SEED2,
321         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
322         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
323         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
324         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
325         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
326         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
327         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
328
329 # for seed2 to threshold2
330 u""" SEED2から判定値2へのテーブル """
331 SEED2_THRESHOLD2_TABLE_NAME = u"seed2_threshold2"
332 SEED2_THRESHOLD2_COL_SEED2 = u"seed2"
333 SEED2_THRESHOLD2_COL_THRESHOLD2_1 = u"threshold2_1"
334 SEED2_THRESHOLD2_COL_THRESHOLD2_2 = u"threshold2_2"
335 SEED2_THRESHOLD2_COL_THRESHOLD2_3 = u"threshold2_3"
336 SEED2_THRESHOLD2_COL_THRESHOLD2_4 = u"threshold2_4"
337 SEED2_THRESHOLD2_COL_THRESHOLD2_5 = u"threshold2_5"
338 SEED2_THRESHOLD2_COL_THRESHOLD2_6 = u"threshold2_6"
339 SEED2_THRESHOLD2_COL_THRESHOLD2_7 = u"threshold2_7"
340 SEED2_THRESHOLD2_COL_THRESHOLD2_LIST = [SEED2_THRESHOLD2_COL_THRESHOLD2_1, SEED2_THRESHOLD2_COL_THRESHOLD2_2, SEED2_THRESHOLD2_COL_THRESHOLD2_3, SEED2_THRESHOLD2_COL_THRESHOLD2_4, SEED2_THRESHOLD2_COL_THRESHOLD2_5, SEED2_THRESHOLD2_COL_THRESHOLD2_6, SEED2_THRESHOLD2_COL_THRESHOLD2_7]
341 SEED2_THRESHOLD2_TABLE_CREATE_SQL = u"""create table if not exists {table} 
342     (id integer primary key, {seed} integer unique,
343     {threshold2_1} integer, {threshold2_2} integer, {threshold2_3} integer,
344     {threshold2_4} integer, {threshold2_5} integer, {threshold2_6} integer, {threshold2_7} integer);""".format(
345         table=SEED2_THRESHOLD2_TABLE_NAME,
346         seed=SEED2_THRESHOLD2_COL_SEED2,
347         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
348         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
349         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
350         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
351         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
352         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
353         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
354 SEED2_THRESHOLD2_TABLE_INSERT_SQL = u"""insert into {table}
355     ({seed}, {threshold2_1}, {threshold2_2}, {threshold2_3}, {threshold2_4}, {threshold2_5}, {threshold2_6}, {threshold2_7})
356     values(?,?,?,?,?,?,?,?);""".format(
357         table=SEED2_THRESHOLD2_TABLE_NAME,
358         seed=SEED2_THRESHOLD2_COL_SEED2,
359         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
360         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
361         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
362         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
363         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
364         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
365         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
366 SEED2_THRESHOLD2_TABLE_SELECT_ALL_SQL = u"""select {seed}, {threshold2_1}, {threshold2_2},
367     {threshold2_3}, {threshold2_4}, {threshold2_5}, {threshold2_6}, {threshold2_7} from {table} """.format(
368         table=SEED2_THRESHOLD2_TABLE_NAME,
369         seed=SEED2_THRESHOLD2_COL_SEED2,
370         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
371         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
372         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
373         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
374         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
375         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
376         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
377 SEED2_THRESHOLD2_TABLE_SELECT_SEEDS_FROM_THRESHOLD2_SQL = u"""
378     select {seed} from {threshold2_table} 
379     where {threshold2_1} >= {{threshold2}} or 
380     {threshold2_2} >= {{threshold2}} or 
381     {threshold2_3} >= {{threshold2}} or 
382     {threshold2_4} >= {{threshold2}} or 
383     {threshold2_5} >= {{threshold2}} or 
384     {threshold2_6} >= {{threshold2}} or 
385     {threshold2_7} >= {{threshold2}}
386     """.format(
387         threshold2_table=SEED2_THRESHOLD2_TABLE_NAME,
388         seed=SEED2_THRESHOLD2_COL_SEED2,
389         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
390         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
391         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
392         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
393         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
394         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
395         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
396
397 # inner join of seed2 -> skill2 & threshold1
398 SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_AND_SKILL2_SQL = u"""
399     select {threshold1_table}.{seed} from {threshold1_table} 
400     inner join {{skill_table}} on {threshold1_table}.{seed} = {{skill_table}}.{seed}
401     where ({{skill_table}}.{skill_id1} == {{skill_id}} and {threshold1_table}.{threshold1_1} >= {{threshold1}}) or 
402     ({{skill_table}}.{skill_id2} == {{skill_id}} and {threshold1_table}.{threshold1_2} >= {{threshold1}}) or 
403     ({{skill_table}}.{skill_id3} == {{skill_id}} and {threshold1_table}.{threshold1_3} >= {{threshold1}}) or 
404     ({{skill_table}}.{skill_id4} == {{skill_id}} and {threshold1_table}.{threshold1_4} >= {{threshold1}}) or 
405     ({{skill_table}}.{skill_id5} == {{skill_id}} and {threshold1_table}.{threshold1_5} >= {{threshold1}}) or 
406     ({{skill_table}}.{skill_id6} == {{skill_id}} and {threshold1_table}.{threshold1_6} >= {{threshold1}}) or 
407     ({{skill_table}}.{skill_id7} == {{skill_id}} and {threshold1_table}.{threshold1_7} >= {{threshold1}})
408     """.format(
409         threshold1_table=SEED2_THRESHOLD1_TABLE_NAME,
410         seed=SEED2_THRESHOLD1_COL_SEED2,
411         skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
412         skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
413         skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
414         skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
415         skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
416         skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
417         skill_id7=SEED2_SKILL2_COL_SKILL2_ID7,
418         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
419         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
420         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
421         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
422         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
423         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
424         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
425
426 # inner join of seed2 -> skill2 & threshold2
427 SEED2_THRESHOLD2_TABLE_SELECT_SEEDS_FROM_THRESHOLD2_AND_SKILL2_SQL = u"""
428     select {threshold2_table}.{seed} from {threshold2_table} 
429     inner join {{skill_table}} on {threshold2_table}.{seed} = {{skill_table}}.{seed}
430     where ({{skill_table}}.{skill_id1} == {{skill_id}} and {threshold2_table}.{threshold2_1} >= {{threshold2}}) or 
431     ({{skill_table}}.{skill_id2} == {{skill_id}} and {threshold2_table}.{threshold2_2} >= {{threshold2}}) or 
432     ({{skill_table}}.{skill_id3} == {{skill_id}} and {threshold2_table}.{threshold2_3} >= {{threshold2}}) or 
433     ({{skill_table}}.{skill_id4} == {{skill_id}} and {threshold2_table}.{threshold2_4} >= {{threshold2}}) or 
434     ({{skill_table}}.{skill_id5} == {{skill_id}} and {threshold2_table}.{threshold2_5} >= {{threshold2}}) or 
435     ({{skill_table}}.{skill_id6} == {{skill_id}} and {threshold2_table}.{threshold2_6} >= {{threshold2}}) or 
436     ({{skill_table}}.{skill_id7} == {{skill_id}} and {threshold2_table}.{threshold2_7} >= {{threshold2}})
437     """.format(
438         threshold2_table=SEED2_THRESHOLD2_TABLE_NAME,
439         seed=SEED2_THRESHOLD2_COL_SEED2,
440         skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
441         skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
442         skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
443         skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
444         skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
445         skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
446         skill_id7=SEED2_SKILL2_COL_SKILL2_ID7,
447         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
448         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
449         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
450         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
451         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
452         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
453         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
454
455
456 # seed2 -> inishie table
457 u""" 古の錬金テーブル """
458 SEED2_INISHIE_TABLE_NAME = u"seed2_inishie"
459 SEED2_INISHIE_COL_SEED2 = u"seed2"
460 SEED2_INISHIE_COL_SKILL2_ID = u"skill2_id"
461 SEED2_INISHIE_COL_THRESHOLD1 = u"treshold1"
462 SEED2_INISHIE_COL_THRESHOLD2 = u"treshold2"
463 SEED2_INISHIE_TABLE_CREATE_SQL = u"""create table if not exists {table_name} 
464     (id integer primary key, {seed} integer unique, 
465     {skill_id} integer, {threshold1} integer, {threshold2} integer,
466     foreign key({skill_id}) references {skill_table}(id));""".format(
467         table_name=SEED2_INISHIE_TABLE_NAME,
468         seed=SEED2_INISHIE_COL_SEED2,
469         skill_id=SEED2_INISHIE_COL_SKILL2_ID,
470         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
471         threshold2=SEED2_INISHIE_COL_THRESHOLD2,
472         skill_table=SKILL_TABLE_NAME)
473 SEED2_INISHIE_TABLE_INSERT_SQL = u"""insert into {table_name}
474     ({seed}, {skill_id}, {threshold1}, {threshold2}) values(?,?,?,?)""".format(
475         table_name=SEED2_INISHIE_TABLE_NAME,
476         seed=SEED2_INISHIE_COL_SEED2,
477         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
478         threshold2=SEED2_INISHIE_COL_THRESHOLD2,
479         skill_id=SEED2_INISHIE_COL_SKILL2_ID)
480 SEED2_INISHIE_TABLE_SELECT_FROM_SEED2_SQL = u"""select {skill_id}, {threshold1}, {threshold2}
481     from {table_name} where {seed2}={{seed2}}""".format(
482         table_name=SEED2_INISHIE_TABLE_NAME,
483         seed2=SEED2_INISHIE_COL_SEED2,
484         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
485         threshold2=SEED2_INISHIE_COL_THRESHOLD2,
486         skill_id=SEED2_INISHIE_COL_SKILL2_ID)
487 SEED2_INISHIE_TABLE_SELECT_SEED2_FROM_SKILL2_THRESHOLD_SQL = u"""select {seed2}
488     from {table_name} where {skill_id}={{skill2_id}} and 
489     ({threshold1}>={{threshold}} or {threshold2}>={{threshold}})""".format(
490         table_name=SEED2_INISHIE_TABLE_NAME,
491         seed2=SEED2_INISHIE_COL_SEED2,
492         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
493         threshold2=SEED2_INISHIE_COL_THRESHOLD2,
494         skill_id=SEED2_INISHIE_COL_SKILL2_ID)
495 SEED2_INISHIE_TABLE_SELECT_SEED2_FROM_THRESHOLD_SQL = u"""select {seed2}
496     from {table_name} where {threshold1}>={{threshold}} or {threshold2}>={{threshold}}""".format(
497         table_name=SEED2_INISHIE_TABLE_NAME,
498         seed2=SEED2_INISHIE_COL_SEED2,
499         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
500         threshold2=SEED2_INISHIE_COL_THRESHOLD2)
501
502 # for sufficient value for slots
503 SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master"
504 SUFFICIENT_TABLE_NAME = u"sufficient_{id}"
505 SUFFICIENT_COL_AMULET_ID = u"amulet_id"
506 SUFFICIENT_COL_SUFFICIENT_TABLE_NAME = u"sufficient_table_name"
507 SUFFICIENT_COL_SUFFICIENT_VALUE = u"sufficient_value"
508 SUFFICIENT_COL_SLOT1_THRESHOLD = u"slot1_threshold"
509 SUFFICIENT_COL_SLOT2_THRESHOLD = u"slot2_threshold"
510 SUFFICIENT_COL_SLOT3_THRESHOLD = u"slot3_threshold"
511 SUFFICIENT_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
512     (id integer primary key, {amulet_id} integer, {table_name} varchar, 
513     foreign key({amulet_id}) references {amulet_table}(id));""".format(
514         master=SUFFICIENT_MASTER_TABLE_NAME,
515         amulet_id=SUFFICIENT_COL_AMULET_ID,
516         amulet_table=AMULET_TABLE_NAME,
517         table_name=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME)
518 SUFFICIENT_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
519     (id integer primary key, {sufficient_val} integer unique, 
520     {slot1_val} integer, {slot2_val} integer, {slot3_val} integer) ;""".format(
521         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
522         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
523         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
524         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
525 SUFFICIENT_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
526     ({amulet_col}, {table_col}) values(?,?);""".format(
527         table=SUFFICIENT_MASTER_TABLE_NAME,
528         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
529         amulet_col=SUFFICIENT_COL_AMULET_ID)
530 SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}}
531     ({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val})
532     values(?,?,?,?);""".format(
533         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
534         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
535         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
536         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
537 SUFFICIENT_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
538         amu_id=SUFFICIENT_COL_AMULET_ID,
539         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
540         table=SUFFICIENT_MASTER_TABLE_NAME)
541 SUFFICIENT_TABLE_SELECT_ALL_SQL = u"""select {sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}""".format(
542         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
543         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
544         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
545         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
546 SUFFICIENT_TABLE_SELECT_THRESHOLD_SQL = u"""select {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}
547     where {sufficient_val}={{sufficient_val}}""".format(
548         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
549         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
550         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
551         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
552
553 # for seed1 table by tenun
554 SEED1_TENUN555_TABLE = u"seed1_table_tenun555"
555 SEED1_TENUN888_TABLE = u"seed1_table_tenun888"
556 SEED1_TENUN_COL_TABLE = u"table_no"
557 SEED1_TENUN_COL_NO = u"no"
558 SEED1_TENUN_COL_SEED1 = u"seed1"
559 SEED1_TENUN_COL_RESULT_NO = u"result_no"
560 SEED1_TENUN_COL_AMULET1 = u"amulet_id1"
561 SEED1_TENUN_COL_AMULET2 = u"amulet_id2"
562 SEED1_TENUN_COL_AMULET3 = u"amulet_id3"
563 SEED1_TENUN_COL_AMULET4 = u"amulet_id4"
564 SEED1_TENUN_COL_AMULET5 = u"amulet_id5"
565 SEED1_TENUN_COL_AMULET6 = u"amulet_id6"
566 SEED1_TENUN_COL_AMULET7 = u"amulet_id7" # 555 doesn't has this col
567 SEED1_TENUN_COL_AMULET_LIST = [SEED1_TENUN_COL_AMULET1, SEED1_TENUN_COL_AMULET2, SEED1_TENUN_COL_AMULET3,
568         SEED1_TENUN_COL_AMULET4, SEED1_TENUN_COL_AMULET5, SEED1_TENUN_COL_AMULET6, SEED1_TENUN_COL_AMULET7]
569 SEED1_TENUN_COL_SKILL1_1 = u"skill1_1"
570 SEED1_TENUN_COL_SKILL1_2 = u"skill1_2"
571 SEED1_TENUN_COL_SKILL1_3 = u"skill1_3"
572 SEED1_TENUN_COL_SKILL1_4 = u"skill1_4"
573 SEED1_TENUN_COL_SKILL1_5 = u"skill1_5"
574 SEED1_TENUN_COL_SKILL1_6 = u"skill1_6"
575 SEED1_TENUN_COL_SKILL1_7 = u"skill1_7"  # 555 doesn't has this col
576 SEED1_TENUN_COL_SKILL1_LIST = [SEED1_TENUN_COL_SKILL1_1, SEED1_TENUN_COL_SKILL1_2, SEED1_TENUN_COL_SKILL1_3,
577         SEED1_TENUN_COL_SKILL1_4, SEED1_TENUN_COL_SKILL1_5, SEED1_TENUN_COL_SKILL1_6, SEED1_TENUN_COL_SKILL1_7]
578 SEED1_TENUN555_TABLE_CREATE_SQL =  u"""create table if not exists {table_name}
579     (id integer primary key, {table_no} integer, {seed} integer unique, {result_num} integer,
580     {amulet_id1} integer, {amulet_id2} integer, {amulet_id3} integer,
581     {amulet_id4} integer, {amulet_id5} integer, {amulet_id6} integer,
582     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer,
583     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer,
584     foreign key({amulet_id1}) references {amulet_table}(id),
585     foreign key({amulet_id2}) references {amulet_table}(id),
586     foreign key({amulet_id3}) references {amulet_table}(id),
587     foreign key({amulet_id4}) references {amulet_table}(id),
588     foreign key({amulet_id5}) references {amulet_table}(id),
589     foreign key({amulet_id6}) references {amulet_table}(id),
590     foreign key({skill_id1}) references {skill_table}(id),
591     foreign key({skill_id2}) references {skill_table}(id),
592     foreign key({skill_id3}) references {skill_table}(id),
593     foreign key({skill_id4}) references {skill_table}(id),
594     foreign key({skill_id5}) references {skill_table}(id),
595     foreign key({skill_id6}) references {skill_table}(id)
596     );""".format(
597         seed=SEED1_TENUN_COL_SEED1,
598         table_no=SEED1_TENUN_COL_TABLE,
599         result_num=SEED1_TENUN_COL_RESULT_NO,
600         amulet_id1=SEED1_TENUN_COL_AMULET1,
601         amulet_id2=SEED1_TENUN_COL_AMULET2,
602         amulet_id3=SEED1_TENUN_COL_AMULET3,
603         amulet_id4=SEED1_TENUN_COL_AMULET4,
604         amulet_id5=SEED1_TENUN_COL_AMULET5,
605         amulet_id6=SEED1_TENUN_COL_AMULET6,
606         skill_id1=SEED1_TENUN_COL_SKILL1_1,
607         skill_id2=SEED1_TENUN_COL_SKILL1_2,
608         skill_id3=SEED1_TENUN_COL_SKILL1_3,
609         skill_id4=SEED1_TENUN_COL_SKILL1_4,
610         skill_id5=SEED1_TENUN_COL_SKILL1_5,
611         skill_id6=SEED1_TENUN_COL_SKILL1_6,
612         amulet_table=AMULET_TABLE_NAME,
613         skill_table=SKILL_TABLE_NAME,
614         table_name=SEED1_TENUN555_TABLE)
615 SEED1_TENUN555_TABLE_INSERT_SQL = u"""insert into {table_name}
616     (id, {table_no}, {seed}, {result_num},
617     {amulet_id1}, {amulet_id2}, {amulet_id3},
618     {amulet_id4}, {amulet_id5}, {amulet_id6},
619     {skill_id1}, {skill_id2}, {skill_id3},
620     {skill_id4}, {skill_id5}, {skill_id6})
621     values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);""".format(
622         table_name=SEED1_TENUN555_TABLE,
623         seed=SEED1_TENUN_COL_SEED1,
624         table_no=SEED1_TENUN_COL_TABLE,
625         result_num=SEED1_TENUN_COL_RESULT_NO,
626         amulet_id1=SEED1_TENUN_COL_AMULET1,
627         amulet_id2=SEED1_TENUN_COL_AMULET2,
628         amulet_id3=SEED1_TENUN_COL_AMULET3,
629         amulet_id4=SEED1_TENUN_COL_AMULET4,
630         amulet_id5=SEED1_TENUN_COL_AMULET5,
631         amulet_id6=SEED1_TENUN_COL_AMULET6,
632         skill_id1=SEED1_TENUN_COL_SKILL1_1,
633         skill_id2=SEED1_TENUN_COL_SKILL1_2,
634         skill_id3=SEED1_TENUN_COL_SKILL1_3,
635         skill_id4=SEED1_TENUN_COL_SKILL1_4,
636         skill_id5=SEED1_TENUN_COL_SKILL1_5,
637         skill_id6=SEED1_TENUN_COL_SKILL1_6)
638 SEED1_TENUN555_TABLE_SELECT_SQL = u""" select {seed} from {table_name} 
639     """.format(seed=SEED1_TENUN_COL_SEED1,
640         table_name=SEED1_TENUN555_TABLE)
641 SEED1_TENUN555_TABLE_SELECT_TABLE_NOS_FROM_SEED1_SQL = u"""select {table_no}, id from {table_name} 
642     where {seed1}={{seed1}}""".format(
643         seed1=SEED1_TENUN_COL_SEED1,
644         table_no=SEED1_TENUN_COL_TABLE,
645         table_name=SEED1_TENUN555_TABLE)
646 SEED1_TENUN555_TABLE_SELECT_NEAR_SEED1S_FROM_NO_SQL = u"""select id, {seed1} from {table_name} 
647     where {table_no}={{table_no}} and id>={{min_no}} and id<={{max_no}}
648     order by id ASC """.format(
649         seed1=SEED1_TENUN_COL_SEED1,
650         table_no=SEED1_TENUN_COL_TABLE,
651         table_name=SEED1_TENUN555_TABLE)
652 SEED1_TENUN555_TABLE_SELECT_ALL_FROM_SEED1_SQL = u"""select id, {table_no}, {result_num},
653     {amulet_id1}, {amulet_id2}, {amulet_id3},
654     {amulet_id4}, {amulet_id5}, {amulet_id6},
655     {skill1_id1}, {skill1_id2}, {skill1_id3},
656     {skill1_id4}, {skill1_id5}, {skill1_id6}
657     from {table_name} where {seed1}={{seed1}} """.format(
658         seed1=SEED1_TENUN_COL_SEED1,
659         table_no=SEED1_TENUN_COL_TABLE,
660         table_name=SEED1_TENUN555_TABLE,
661         result_num=SEED1_TENUN_COL_RESULT_NO,
662         amulet_id1=SEED1_TENUN_COL_AMULET1,
663         amulet_id2=SEED1_TENUN_COL_AMULET2,
664         amulet_id3=SEED1_TENUN_COL_AMULET3,
665         amulet_id4=SEED1_TENUN_COL_AMULET4,
666         amulet_id5=SEED1_TENUN_COL_AMULET5,
667         amulet_id6=SEED1_TENUN_COL_AMULET6,
668         skill1_id1=SEED1_TENUN_COL_SKILL1_1,
669         skill1_id2=SEED1_TENUN_COL_SKILL1_2,
670         skill1_id3=SEED1_TENUN_COL_SKILL1_3,
671         skill1_id4=SEED1_TENUN_COL_SKILL1_4,
672         skill1_id5=SEED1_TENUN_COL_SKILL1_5,
673         skill1_id6=SEED1_TENUN_COL_SKILL1_6)
674 # extract skill1 by place
675 SEED1_TENUN555_TABLE_SELECT_SEED1_SQL = u"""select
676     {seed1} from {table_name} """.format(
677         seed1=SEED1_TENUN_COL_SEED1,
678         table_name=SEED1_TENUN555_TABLE)
679 # count seed1 number with skill1 and place
680 SEED1_TENUN555_TABLE_COUNT_SEED1_SQL = u"""select
681     count({seed1}) from {table_name} """.format(
682         seed1=SEED1_TENUN_COL_SEED1,
683         table_name=SEED1_TENUN555_TABLE)
684
685 # tenun888
686 SEED1_TENUN888_TABLE_CREATE_SQL =  u"""create table if not exists {table_name}
687     (id integer primary key, {table_no} integer, {seed} integer unique, {result_num} integer,
688     {amulet_id1} integer, {amulet_id2} integer, {amulet_id3} integer,
689     {amulet_id4} integer, {amulet_id5} integer, {amulet_id6} integer, {amulet_id7} integer,
690     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer,
691     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer,
692     foreign key({amulet_id1}) references {amulet_table}(id),
693     foreign key({amulet_id2}) references {amulet_table}(id),
694     foreign key({amulet_id3}) references {amulet_table}(id),
695     foreign key({amulet_id4}) references {amulet_table}(id),
696     foreign key({amulet_id5}) references {amulet_table}(id),
697     foreign key({amulet_id6}) references {amulet_table}(id),
698     foreign key({amulet_id7}) references {amulet_table}(id),
699     foreign key({skill_id1}) references {skill_table}(id),
700     foreign key({skill_id2}) references {skill_table}(id),
701     foreign key({skill_id3}) references {skill_table}(id),
702     foreign key({skill_id4}) references {skill_table}(id),
703     foreign key({skill_id5}) references {skill_table}(id),
704     foreign key({skill_id6}) references {skill_table}(id),
705     foreign key({skill_id7}) references {skill_table}(id)
706     );""".format(
707         seed=SEED1_TENUN_COL_SEED1,
708         table_no=SEED1_TENUN_COL_TABLE,
709         result_num=SEED1_TENUN_COL_RESULT_NO,
710         amulet_id1=SEED1_TENUN_COL_AMULET1,
711         amulet_id2=SEED1_TENUN_COL_AMULET2,
712         amulet_id3=SEED1_TENUN_COL_AMULET3,
713         amulet_id4=SEED1_TENUN_COL_AMULET4,
714         amulet_id5=SEED1_TENUN_COL_AMULET5,
715         amulet_id6=SEED1_TENUN_COL_AMULET6,
716         amulet_id7=SEED1_TENUN_COL_AMULET7,
717         skill_id1=SEED1_TENUN_COL_SKILL1_1,
718         skill_id2=SEED1_TENUN_COL_SKILL1_2,
719         skill_id3=SEED1_TENUN_COL_SKILL1_3,
720         skill_id4=SEED1_TENUN_COL_SKILL1_4,
721         skill_id5=SEED1_TENUN_COL_SKILL1_5,
722         skill_id6=SEED1_TENUN_COL_SKILL1_6,
723         skill_id7=SEED1_TENUN_COL_SKILL1_7,
724         amulet_table=AMULET_TABLE_NAME,
725         skill_table=SKILL_TABLE_NAME,
726         table_name=SEED1_TENUN888_TABLE)
727 SEED1_TENUN888_TABLE_INSERT_SQL = u"""insert into {table_name}
728     (id, {table_no}, {seed}, {result_num},
729     {amulet_id1}, {amulet_id2}, {amulet_id3},
730     {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7},
731     {skill_id1}, {skill_id2}, {skill_id3},
732     {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7})
733     values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);""".format(
734         table_name=SEED1_TENUN888_TABLE,
735         seed=SEED1_TENUN_COL_SEED1,
736         table_no=SEED1_TENUN_COL_TABLE,
737         result_num=SEED1_TENUN_COL_RESULT_NO,
738         amulet_id1=SEED1_TENUN_COL_AMULET1,
739         amulet_id2=SEED1_TENUN_COL_AMULET2,
740         amulet_id3=SEED1_TENUN_COL_AMULET3,
741         amulet_id4=SEED1_TENUN_COL_AMULET4,
742         amulet_id5=SEED1_TENUN_COL_AMULET5,
743         amulet_id6=SEED1_TENUN_COL_AMULET6,
744         amulet_id7=SEED1_TENUN_COL_AMULET7,
745         skill_id1=SEED1_TENUN_COL_SKILL1_1,
746         skill_id2=SEED1_TENUN_COL_SKILL1_2,
747         skill_id3=SEED1_TENUN_COL_SKILL1_3,
748         skill_id4=SEED1_TENUN_COL_SKILL1_4,
749         skill_id5=SEED1_TENUN_COL_SKILL1_5,
750         skill_id6=SEED1_TENUN_COL_SKILL1_6,
751         skill_id7=SEED1_TENUN_COL_SKILL1_7)
752 SEED1_TENUN888_TABLE_SELECT_SQL = u""" select {seed} from {table_name} 
753     """.format(seed=SEED1_TENUN_COL_SEED1,
754         table_name=SEED1_TENUN888_TABLE)
755 SEED1_TENUN888_TABLE_SELECT_TABLE_NOS_FROM_SEED1_SQL = u"""select {table_no}, id from {table_name} 
756     where {seed1}={{seed1}}""".format(
757         seed1=SEED1_TENUN_COL_SEED1,
758         table_no=SEED1_TENUN_COL_TABLE,
759         table_name=SEED1_TENUN888_TABLE)
760 SEED1_TENUN888_TABLE_SELECT_NEAR_SEED1S_FROM_NO_SQL = u"""select id, {seed1} from {table_name} 
761     where {table_no}={{table_no}} and id>={{min_no}} and id<={{max_no}}
762     order by id ASC """.format(
763         seed1=SEED1_TENUN_COL_SEED1,
764         table_no=SEED1_TENUN_COL_TABLE,
765         table_name=SEED1_TENUN888_TABLE)
766 SEED1_TENUN888_TABLE_SELECT_ALL_FROM_SEED1_SQL = u"""select id, {table_no}, {result_num},
767     {amulet_id1}, {amulet_id2}, {amulet_id3},
768     {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7},
769     {skill1_id1}, {skill1_id2}, {skill1_id3},
770     {skill1_id4}, {skill1_id5}, {skill1_id6}, {skill1_id7}
771     from {table_name} where {seed1}={{seed1}} """.format(
772         seed1=SEED1_TENUN_COL_SEED1,
773         table_no=SEED1_TENUN_COL_TABLE,
774         table_name=SEED1_TENUN888_TABLE,
775         result_num=SEED1_TENUN_COL_RESULT_NO,
776         amulet_id1=SEED1_TENUN_COL_AMULET1,
777         amulet_id2=SEED1_TENUN_COL_AMULET2,
778         amulet_id3=SEED1_TENUN_COL_AMULET3,
779         amulet_id4=SEED1_TENUN_COL_AMULET4,
780         amulet_id5=SEED1_TENUN_COL_AMULET5,
781         amulet_id6=SEED1_TENUN_COL_AMULET6,
782         amulet_id7=SEED1_TENUN_COL_AMULET7,
783         skill1_id1=SEED1_TENUN_COL_SKILL1_1,
784         skill1_id2=SEED1_TENUN_COL_SKILL1_2,
785         skill1_id3=SEED1_TENUN_COL_SKILL1_3,
786         skill1_id4=SEED1_TENUN_COL_SKILL1_4,
787         skill1_id5=SEED1_TENUN_COL_SKILL1_5,
788         skill1_id6=SEED1_TENUN_COL_SKILL1_6,
789         skill1_id7=SEED1_TENUN_COL_SKILL1_7)
790
791 # extract skill1 by place
792 SEED1_TENUN888_TABLE_SELECT_SEED1_SQL = u"""select
793     {seed1} from {table_name} """.format(
794         seed1=SEED1_TENUN_COL_SEED1,
795         table_name=SEED1_TENUN888_TABLE)
796 # count seed1 number with skill1 and place
797 SEED1_TENUN888_TABLE_COUNT_SEED1_SQL = u"""select
798     count({seed1}) from {table_name} """.format(
799         seed1=SEED1_TENUN_COL_SEED1,
800         table_name=SEED1_TENUN888_TABLE)