OSDN Git Service

revise database generator for seed1 decision & threshold2 & inishie
[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
252 # for seed2 to threshold1
253 u""" SEED2から判定値1へのテーブル """
254 SEED2_THRESHOLD1_TABLE_NAME = u"seed2_threshold1"
255 SEED2_THRESHOLD1_COL_SEED2 = u"seed2"
256 SEED2_THRESHOLD1_COL_THRESHOLD1_1 = u"threshold1_1"
257 SEED2_THRESHOLD1_COL_THRESHOLD1_2 = u"threshold1_2"
258 SEED2_THRESHOLD1_COL_THRESHOLD1_3 = u"threshold1_3"
259 SEED2_THRESHOLD1_COL_THRESHOLD1_4 = u"threshold1_4"
260 SEED2_THRESHOLD1_COL_THRESHOLD1_5 = u"threshold1_5"
261 SEED2_THRESHOLD1_COL_THRESHOLD1_6 = u"threshold1_6"
262 SEED2_THRESHOLD1_COL_THRESHOLD1_7 = u"threshold1_7"
263 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]
264 SEED2_THRESHOLD1_TABLE_CREATE_SQL = u"""create table if not exists {table} 
265     (id integer primary key, {seed} integer unique,
266     {threshold1_1} integer, {threshold1_2} integer, {threshold1_3} integer,
267     {threshold1_4} integer, {threshold1_5} integer, {threshold1_6} integer, {threshold1_7} integer);""".format(
268         table=SEED2_THRESHOLD1_TABLE_NAME,
269         seed=SEED2_THRESHOLD1_COL_SEED2,
270         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
271         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
272         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
273         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
274         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
275         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
276         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
277 SEED2_THRESHOLD1_TABLE_INSERT_SQL = u"""insert into {table}
278     ({seed}, {threshold1_1}, {threshold1_2}, {threshold1_3}, {threshold1_4}, {threshold1_5}, {threshold1_6}, {threshold1_7})
279     values(?,?,?,?,?,?,?,?);""".format(
280         table=SEED2_THRESHOLD1_TABLE_NAME,
281         seed=SEED2_THRESHOLD1_COL_SEED2,
282         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
283         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
284         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
285         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
286         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
287         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
288         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
289 SEED2_THRESHOLD1_TABLE_SELECT_ALL_SQL = u"""select {seed}, {threshold1_1}, {threshold1_2},
290     {threshold1_3}, {threshold1_4}, {threshold1_5}, {threshold1_6}, {threshold1_7} from {table} """.format(
291         table=SEED2_THRESHOLD1_TABLE_NAME,
292         seed=SEED2_THRESHOLD1_COL_SEED2,
293         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
294         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
295         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
296         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
297         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
298         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
299         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
300 SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_SQL = u"""
301     select {seed} from {threshold1_table} 
302     where {threshold1_1} >= {{threshold1}} or 
303     {threshold1_2} >= {{threshold1}} or 
304     {threshold1_3} >= {{threshold1}} or 
305     {threshold1_4} >= {{threshold1}} or 
306     {threshold1_5} >= {{threshold1}} or 
307     {threshold1_6} >= {{threshold1}} or 
308     {threshold1_7} >= {{threshold1}}
309     """.format(
310         threshold1_table=SEED2_THRESHOLD1_TABLE_NAME,
311         seed=SEED2_THRESHOLD1_COL_SEED2,
312         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
313         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
314         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
315         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
316         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
317         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
318         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
319
320 # for seed2 to threshold2
321 u""" SEED2から判定値2へのテーブル """
322 SEED2_THRESHOLD2_TABLE_NAME = u"seed2_threshold2"
323 SEED2_THRESHOLD2_COL_SEED2 = u"seed2"
324 SEED2_THRESHOLD2_COL_THRESHOLD2_1 = u"threshold2_1"
325 SEED2_THRESHOLD2_COL_THRESHOLD2_2 = u"threshold2_2"
326 SEED2_THRESHOLD2_COL_THRESHOLD2_3 = u"threshold2_3"
327 SEED2_THRESHOLD2_COL_THRESHOLD2_4 = u"threshold2_4"
328 SEED2_THRESHOLD2_COL_THRESHOLD2_5 = u"threshold2_5"
329 SEED2_THRESHOLD2_COL_THRESHOLD2_6 = u"threshold2_6"
330 SEED2_THRESHOLD2_COL_THRESHOLD2_7 = u"threshold2_7"
331 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]
332 SEED2_THRESHOLD2_TABLE_CREATE_SQL = u"""create table if not exists {table} 
333     (id integer primary key, {seed} integer unique,
334     {threshold2_1} integer, {threshold2_2} integer, {threshold2_3} integer,
335     {threshold2_4} integer, {threshold2_5} integer, {threshold2_6} integer, {threshold2_7} integer);""".format(
336         table=SEED2_THRESHOLD2_TABLE_NAME,
337         seed=SEED2_THRESHOLD2_COL_SEED2,
338         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
339         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
340         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
341         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
342         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
343         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
344         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
345 SEED2_THRESHOLD2_TABLE_INSERT_SQL = u"""insert into {table}
346     ({seed}, {threshold2_1}, {threshold2_2}, {threshold2_3}, {threshold2_4}, {threshold2_5}, {threshold2_6}, {threshold2_7})
347     values(?,?,?,?,?,?,?,?);""".format(
348         table=SEED2_THRESHOLD2_TABLE_NAME,
349         seed=SEED2_THRESHOLD2_COL_SEED2,
350         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
351         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
352         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
353         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
354         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
355         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
356         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
357 SEED2_THRESHOLD2_TABLE_SELECT_ALL_SQL = u"""select {seed}, {threshold2_1}, {threshold2_2},
358     {threshold2_3}, {threshold2_4}, {threshold2_5}, {threshold2_6}, {threshold2_7} from {table} """.format(
359         table=SEED2_THRESHOLD2_TABLE_NAME,
360         seed=SEED2_THRESHOLD2_COL_SEED2,
361         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
362         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
363         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
364         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
365         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
366         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
367         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
368 SEED2_THRESHOLD2_TABLE_SELECT_SEEDS_FROM_THRESHOLD2_SQL = u"""
369     select {seed} from {threshold2_table} 
370     where {threshold2_1} >= {{threshold2}} or 
371     {threshold2_2} >= {{threshold2}} or 
372     {threshold2_3} >= {{threshold2}} or 
373     {threshold2_4} >= {{threshold2}} or 
374     {threshold2_5} >= {{threshold2}} or 
375     {threshold2_6} >= {{threshold2}} or 
376     {threshold2_7} >= {{threshold2}}
377     """.format(
378         threshold2_table=SEED2_THRESHOLD2_TABLE_NAME,
379         seed=SEED2_THRESHOLD2_COL_SEED2,
380         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
381         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
382         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
383         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
384         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
385         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
386         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
387
388 # inner join of seed2 -> skill2 & threshold1
389 SEED2_THRESHOLD1_TABLE_SELECT_SEEDS_FROM_THRESHOLD1_AND_SKILL2_SQL = u"""
390     select {threshold1_table}.{seed} from {threshold1_table} 
391     inner join {{skill_table}} on {threshold1_table}.{seed} = {{skill_table}}.{seed}
392     where ({{skill_table}}.{skill_id1} == {{skill_id}} and {threshold1_table}.{threshold1_1} >= {{threshold1}}) or 
393     ({{skill_table}}.{skill_id2} == {{skill_id}} and {threshold1_table}.{threshold1_2} >= {{threshold1}}) or 
394     ({{skill_table}}.{skill_id3} == {{skill_id}} and {threshold1_table}.{threshold1_3} >= {{threshold1}}) or 
395     ({{skill_table}}.{skill_id4} == {{skill_id}} and {threshold1_table}.{threshold1_4} >= {{threshold1}}) or 
396     ({{skill_table}}.{skill_id5} == {{skill_id}} and {threshold1_table}.{threshold1_5} >= {{threshold1}}) or 
397     ({{skill_table}}.{skill_id6} == {{skill_id}} and {threshold1_table}.{threshold1_6} >= {{threshold1}}) or 
398     ({{skill_table}}.{skill_id7} == {{skill_id}} and {threshold1_table}.{threshold1_7} >= {{threshold1}})
399     """.format(
400         threshold1_table=SEED2_THRESHOLD1_TABLE_NAME,
401         seed=SEED2_THRESHOLD1_COL_SEED2,
402         skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
403         skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
404         skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
405         skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
406         skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
407         skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
408         skill_id7=SEED2_SKILL2_COL_SKILL2_ID7,
409         threshold1_1=SEED2_THRESHOLD1_COL_THRESHOLD1_1,
410         threshold1_2=SEED2_THRESHOLD1_COL_THRESHOLD1_2,
411         threshold1_3=SEED2_THRESHOLD1_COL_THRESHOLD1_3,
412         threshold1_4=SEED2_THRESHOLD1_COL_THRESHOLD1_4,
413         threshold1_5=SEED2_THRESHOLD1_COL_THRESHOLD1_5,
414         threshold1_6=SEED2_THRESHOLD1_COL_THRESHOLD1_6,
415         threshold1_7=SEED2_THRESHOLD1_COL_THRESHOLD1_7)
416
417 # inner join of seed2 -> skill2 & threshold2
418 SEED2_THRESHOLD2_TABLE_SELECT_SEEDS_FROM_THRESHOLD2_AND_SKILL2_SQL = u"""
419     select {threshold2_table}.{seed} from {threshold2_table} 
420     inner join {{skill_table}} on {threshold2_table}.{seed} = {{skill_table}}.{seed}
421     where ({{skill_table}}.{skill_id1} == {{skill_id}} and {threshold2_table}.{threshold2_1} >= {{threshold2}}) or 
422     ({{skill_table}}.{skill_id2} == {{skill_id}} and {threshold2_table}.{threshold2_2} >= {{threshold2}}) or 
423     ({{skill_table}}.{skill_id3} == {{skill_id}} and {threshold2_table}.{threshold2_3} >= {{threshold2}}) or 
424     ({{skill_table}}.{skill_id4} == {{skill_id}} and {threshold2_table}.{threshold2_4} >= {{threshold2}}) or 
425     ({{skill_table}}.{skill_id5} == {{skill_id}} and {threshold2_table}.{threshold2_5} >= {{threshold2}}) or 
426     ({{skill_table}}.{skill_id6} == {{skill_id}} and {threshold2_table}.{threshold2_6} >= {{threshold2}}) or 
427     ({{skill_table}}.{skill_id7} == {{skill_id}} and {threshold2_table}.{threshold2_7} >= {{threshold2}})
428     """.format(
429         threshold2_table=SEED2_THRESHOLD2_TABLE_NAME,
430         seed=SEED2_THRESHOLD2_COL_SEED2,
431         skill_id1=SEED2_SKILL2_COL_SKILL2_ID1,
432         skill_id2=SEED2_SKILL2_COL_SKILL2_ID2,
433         skill_id3=SEED2_SKILL2_COL_SKILL2_ID3,
434         skill_id4=SEED2_SKILL2_COL_SKILL2_ID4,
435         skill_id5=SEED2_SKILL2_COL_SKILL2_ID5,
436         skill_id6=SEED2_SKILL2_COL_SKILL2_ID6,
437         skill_id7=SEED2_SKILL2_COL_SKILL2_ID7,
438         threshold2_1=SEED2_THRESHOLD2_COL_THRESHOLD2_1,
439         threshold2_2=SEED2_THRESHOLD2_COL_THRESHOLD2_2,
440         threshold2_3=SEED2_THRESHOLD2_COL_THRESHOLD2_3,
441         threshold2_4=SEED2_THRESHOLD2_COL_THRESHOLD2_4,
442         threshold2_5=SEED2_THRESHOLD2_COL_THRESHOLD2_5,
443         threshold2_6=SEED2_THRESHOLD2_COL_THRESHOLD2_6,
444         threshold2_7=SEED2_THRESHOLD2_COL_THRESHOLD2_7)
445
446
447 # seed2 -> inishie table
448 u""" 古の錬金テーブル """
449 SEED2_INISHIE_TABLE_NAME = u"seed2_inishie"
450 SEED2_INISHIE_COL_SEED2 = u"seed2"
451 SEED2_INISHIE_COL_SKILL2_ID = u"skill2_id"
452 SEED2_INISHIE_COL_THRESHOLD1 = u"treshold1"
453 SEED2_INISHIE_COL_THRESHOLD2 = u"treshold2"
454 SEED2_INISHIE_TABLE_CREATE_SQL = u"""create table if not exists {table_name} 
455     (id integer primary key, {seed} integer unique, 
456     {skill_id} integer, {threshold1} integer, {threshold2} integer,
457     foreign key({skill_id}) references {skill_table}(id));""".format(
458         table_name=SEED2_INISHIE_TABLE_NAME,
459         seed=SEED2_INISHIE_COL_SEED2,
460         skill_id=SEED2_INISHIE_COL_SKILL2_ID,
461         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
462         threshold2=SEED2_INISHIE_COL_THRESHOLD2,
463         skill_table=SKILL_TABLE_NAME)
464 SEED2_INISHIE_TABLE_INSERT_SQL = u"""insert into {table_name}
465     ({seed}, {skill_id}, {threshold1}, {threshold2}) values(?,?,?,?)""".format(
466         table_name=SEED2_INISHIE_TABLE_NAME,
467         seed=SEED2_INISHIE_COL_SEED2,
468         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
469         threshold2=SEED2_INISHIE_COL_THRESHOLD2,
470         skill_id=SEED2_INISHIE_COL_SKILL2_ID)
471 SEED2_INISHIE_TABLE_SELECT_FROM_SEED2_SQL = u"""select {skill_id}, {threshold1}, {threshold2}
472     from {table_name} where {seed2}={{seed2}}""".format(
473         table_name=SEED2_INISHIE_TABLE_NAME,
474         seed2=SEED2_INISHIE_COL_SEED2,
475         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
476         threshold2=SEED2_INISHIE_COL_THRESHOLD2,
477         skill_id=SEED2_INISHIE_COL_SKILL2_ID)
478 SEED2_INISHIE_TABLE_SELECT_SEED2_FROM_SKILL2_THRESHOLD_SQL = u"""select {seed2}
479     from {table_name} where {skill_id}={{skill2_id}} and 
480     ({threshold1}>={{threshold}} or {threshold2}>={{threshold}})""".format(
481         table_name=SEED2_INISHIE_TABLE_NAME,
482         seed2=SEED2_INISHIE_COL_SEED2,
483         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
484         threshold2=SEED2_INISHIE_COL_THRESHOLD2,
485         skill_id=SEED2_INISHIE_COL_SKILL2_ID)
486 SEED2_INISHIE_TABLE_SELECT_SEED2_FROM_THRESHOLD_SQL = u"""select {seed2}
487     from {table_name} where {threshold1}>={{threshold}} or {threshold2}>={{threshold}}""".format(
488         table_name=SEED2_INISHIE_TABLE_NAME,
489         seed2=SEED2_INISHIE_COL_SEED2,
490         threshold1=SEED2_INISHIE_COL_THRESHOLD1,
491         threshold2=SEED2_INISHIE_COL_THRESHOLD2)
492
493 # for sufficient value for slots
494 SUFFICIENT_MASTER_TABLE_NAME = u"sufficient_master"
495 SUFFICIENT_TABLE_NAME = u"sufficient_{id}"
496 SUFFICIENT_COL_AMULET_ID = u"amulet_id"
497 SUFFICIENT_COL_SUFFICIENT_TABLE_NAME = u"sufficient_table_name"
498 SUFFICIENT_COL_SUFFICIENT_VALUE = u"sufficient_value"
499 SUFFICIENT_COL_SLOT1_THRESHOLD = u"slot1_threshold"
500 SUFFICIENT_COL_SLOT2_THRESHOLD = u"slot2_threshold"
501 SUFFICIENT_COL_SLOT3_THRESHOLD = u"slot3_threshold"
502 SUFFICIENT_MASTER_TABLE_CREATE_SQL = u"""create table if not exists {master}
503     (id integer primary key, {amulet_id} integer, {table_name} varchar, 
504     foreign key({amulet_id}) references {amulet_table}(id));""".format(
505         master=SUFFICIENT_MASTER_TABLE_NAME,
506         amulet_id=SUFFICIENT_COL_AMULET_ID,
507         amulet_table=AMULET_TABLE_NAME,
508         table_name=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME)
509 SUFFICIENT_TABLE_CREATE_SQL = u"""create table if not exists {{table_name}} 
510     (id integer primary key, {sufficient_val} integer unique, 
511     {slot1_val} integer, {slot2_val} integer, {slot3_val} integer) ;""".format(
512         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
513         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
514         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
515         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
516 SUFFICIENT_MASTER_TABLE_INSERT_SQL = u"""insert into {table}
517     ({amulet_col}, {table_col}) values(?,?);""".format(
518         table=SUFFICIENT_MASTER_TABLE_NAME,
519         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
520         amulet_col=SUFFICIENT_COL_AMULET_ID)
521 SUFFICIENT_TABLE_INSERT_SQL = u"""insert into {{table_name}}
522     ({sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val})
523     values(?,?,?,?);""".format(
524         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
525         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
526         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
527         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
528 SUFFICIENT_MASTER_TABLE_SELECT_ALL_SQL = u"""select {amu_id}, {table_col} from {table}""".format(
529         amu_id=SUFFICIENT_COL_AMULET_ID,
530         table_col=SUFFICIENT_COL_SUFFICIENT_TABLE_NAME,
531         table=SUFFICIENT_MASTER_TABLE_NAME)
532 SUFFICIENT_TABLE_SELECT_ALL_SQL = u"""select {sufficient_val}, {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}""".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_TABLE_SELECT_THRESHOLD_SQL = u"""select {slot1_val}, {slot2_val}, {slot3_val} from {{table_name}}
538     where {sufficient_val}={{sufficient_val}}""".format(
539         sufficient_val=SUFFICIENT_COL_SUFFICIENT_VALUE,
540         slot1_val=SUFFICIENT_COL_SLOT1_THRESHOLD,
541         slot2_val=SUFFICIENT_COL_SLOT2_THRESHOLD,
542         slot3_val=SUFFICIENT_COL_SLOT3_THRESHOLD)
543
544 # for seed1 table by tenun
545 SEED1_TENUN555_TABLE = u"seed1_table_tenun555"
546 SEED1_TENUN888_TABLE = u"seed1_table_tenun888"
547 SEED1_TENUN_COL_TABLE = u"table_no"
548 SEED1_TENUN_COL_NO = u"no"
549 SEED1_TENUN_COL_SEED1 = u"seed1"
550 SEED1_TENUN_COL_RESULT_NO = u"result_no"
551 SEED1_TENUN_COL_AMULET1 = u"amulet_id1"
552 SEED1_TENUN_COL_AMULET2 = u"amulet_id2"
553 SEED1_TENUN_COL_AMULET3 = u"amulet_id3"
554 SEED1_TENUN_COL_AMULET4 = u"amulet_id4"
555 SEED1_TENUN_COL_AMULET5 = u"amulet_id5"
556 SEED1_TENUN_COL_AMULET6 = u"amulet_id6"
557 SEED1_TENUN_COL_AMULET7 = u"amulet_id7" # 555 doesn't has this col
558 SEED1_TENUN_COL_AMULET_LIST = [SEED1_TENUN_COL_AMULET1, SEED1_TENUN_COL_AMULET2, SEED1_TENUN_COL_AMULET3,
559         SEED1_TENUN_COL_AMULET4, SEED1_TENUN_COL_AMULET5, SEED1_TENUN_COL_AMULET6, SEED1_TENUN_COL_AMULET7]
560 SEED1_TENUN_COL_SKILL1_1 = u"skill1_1"
561 SEED1_TENUN_COL_SKILL1_2 = u"skill1_2"
562 SEED1_TENUN_COL_SKILL1_3 = u"skill1_3"
563 SEED1_TENUN_COL_SKILL1_4 = u"skill1_4"
564 SEED1_TENUN_COL_SKILL1_5 = u"skill1_5"
565 SEED1_TENUN_COL_SKILL1_6 = u"skill1_6"
566 SEED1_TENUN_COL_SKILL1_7 = u"skill1_7"  # 555 doesn't has this col
567 SEED1_TENUN_COL_SKILL1_LIST = [SEED1_TENUN_COL_SKILL1_1, SEED1_TENUN_COL_SKILL1_2, SEED1_TENUN_COL_SKILL1_3,
568         SEED1_TENUN_COL_SKILL1_4, SEED1_TENUN_COL_SKILL1_5, SEED1_TENUN_COL_SKILL1_6, SEED1_TENUN_COL_SKILL1_7]
569 SEED1_TENUN555_TABLE_CREATE_SQL =  u"""create table if not exists {table_name}
570     (id integer primary key, {table_no} integer, {seed} integer unique, {result_num} integer,
571     {amulet_id1} integer, {amulet_id2} integer, {amulet_id3} integer,
572     {amulet_id4} integer, {amulet_id5} integer, {amulet_id6} integer,
573     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer,
574     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer,
575     foreign key({amulet_id1}) references {amulet_table}(id),
576     foreign key({amulet_id2}) references {amulet_table}(id),
577     foreign key({amulet_id3}) references {amulet_table}(id),
578     foreign key({amulet_id4}) references {amulet_table}(id),
579     foreign key({amulet_id5}) references {amulet_table}(id),
580     foreign key({amulet_id6}) references {amulet_table}(id),
581     foreign key({skill_id1}) references {skill_table}(id),
582     foreign key({skill_id2}) references {skill_table}(id),
583     foreign key({skill_id3}) references {skill_table}(id),
584     foreign key({skill_id4}) references {skill_table}(id),
585     foreign key({skill_id5}) references {skill_table}(id),
586     foreign key({skill_id6}) references {skill_table}(id)
587     );""".format(
588         seed=SEED1_TENUN_COL_SEED1,
589         table_no=SEED1_TENUN_COL_TABLE,
590         result_num=SEED1_TENUN_COL_RESULT_NO,
591         amulet_id1=SEED1_TENUN_COL_AMULET1,
592         amulet_id2=SEED1_TENUN_COL_AMULET2,
593         amulet_id3=SEED1_TENUN_COL_AMULET3,
594         amulet_id4=SEED1_TENUN_COL_AMULET4,
595         amulet_id5=SEED1_TENUN_COL_AMULET5,
596         amulet_id6=SEED1_TENUN_COL_AMULET6,
597         skill_id1=SEED1_TENUN_COL_SKILL1_1,
598         skill_id2=SEED1_TENUN_COL_SKILL1_2,
599         skill_id3=SEED1_TENUN_COL_SKILL1_3,
600         skill_id4=SEED1_TENUN_COL_SKILL1_4,
601         skill_id5=SEED1_TENUN_COL_SKILL1_5,
602         skill_id6=SEED1_TENUN_COL_SKILL1_6,
603         amulet_table=AMULET_TABLE_NAME,
604         skill_table=SKILL_TABLE_NAME,
605         table_name=SEED1_TENUN555_TABLE)
606 SEED1_TENUN555_TABLE_INSERT_SQL = u"""insert into {table_name}
607     (id, {table_no}, {seed}, {result_num},
608     {amulet_id1}, {amulet_id2}, {amulet_id3},
609     {amulet_id4}, {amulet_id5}, {amulet_id6},
610     {skill_id1}, {skill_id2}, {skill_id3},
611     {skill_id4}, {skill_id5}, {skill_id6})
612     values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);""".format(
613         table_name=SEED1_TENUN555_TABLE,
614         seed=SEED1_TENUN_COL_SEED1,
615         table_no=SEED1_TENUN_COL_TABLE,
616         result_num=SEED1_TENUN_COL_RESULT_NO,
617         amulet_id1=SEED1_TENUN_COL_AMULET1,
618         amulet_id2=SEED1_TENUN_COL_AMULET2,
619         amulet_id3=SEED1_TENUN_COL_AMULET3,
620         amulet_id4=SEED1_TENUN_COL_AMULET4,
621         amulet_id5=SEED1_TENUN_COL_AMULET5,
622         amulet_id6=SEED1_TENUN_COL_AMULET6,
623         skill_id1=SEED1_TENUN_COL_SKILL1_1,
624         skill_id2=SEED1_TENUN_COL_SKILL1_2,
625         skill_id3=SEED1_TENUN_COL_SKILL1_3,
626         skill_id4=SEED1_TENUN_COL_SKILL1_4,
627         skill_id5=SEED1_TENUN_COL_SKILL1_5,
628         skill_id6=SEED1_TENUN_COL_SKILL1_6)
629 SEED1_TENUN555_TABLE_SELECT_SQL = u""" select {seed} from {table_name} 
630     """.format(seed=SEED1_TENUN_COL_SEED1,
631         table_name=SEED1_TENUN555_TABLE)
632 SEED1_TENUN555_TABLE_SELECT_TABLE_NOS_FROM_SEED1_SQL = u"""select {table_no}, id from {table_name} 
633     where {seed1}={{seed1}}""".format(
634         seed1=SEED1_TENUN_COL_SEED1,
635         table_no=SEED1_TENUN_COL_TABLE,
636         table_name=SEED1_TENUN555_TABLE)
637 SEED1_TENUN555_TABLE_SELECT_NEAR_SEED1S_FROM_NO_SQL = u"""select id, {seed1} from {table_name} 
638     where {table_no}={{table_no}} and id>={{min_no}} and id<={{max_no}}
639     order by id ASC """.format(
640         seed1=SEED1_TENUN_COL_SEED1,
641         table_no=SEED1_TENUN_COL_TABLE,
642         table_name=SEED1_TENUN555_TABLE)
643 SEED1_TENUN555_TABLE_SELECT_ALL_FROM_SEED1_SQL = u"""select id, {table_no}, {result_num},
644     {amulet_id1}, {amulet_id2}, {amulet_id3},
645     {amulet_id4}, {amulet_id5}, {amulet_id6},
646     {skill1_id1}, {skill1_id2}, {skill1_id3},
647     {skill1_id4}, {skill1_id5}, {skill1_id6}
648     from {table_name} where {seed1}={{seed1}} """.format(
649         seed1=SEED1_TENUN_COL_SEED1,
650         table_no=SEED1_TENUN_COL_TABLE,
651         table_name=SEED1_TENUN555_TABLE,
652         result_num=SEED1_TENUN_COL_RESULT_NO,
653         amulet_id1=SEED1_TENUN_COL_AMULET1,
654         amulet_id2=SEED1_TENUN_COL_AMULET2,
655         amulet_id3=SEED1_TENUN_COL_AMULET3,
656         amulet_id4=SEED1_TENUN_COL_AMULET4,
657         amulet_id5=SEED1_TENUN_COL_AMULET5,
658         amulet_id6=SEED1_TENUN_COL_AMULET6,
659         skill1_id1=SEED1_TENUN_COL_SKILL1_1,
660         skill1_id2=SEED1_TENUN_COL_SKILL1_2,
661         skill1_id3=SEED1_TENUN_COL_SKILL1_3,
662         skill1_id4=SEED1_TENUN_COL_SKILL1_4,
663         skill1_id5=SEED1_TENUN_COL_SKILL1_5,
664         skill1_id6=SEED1_TENUN_COL_SKILL1_6)
665 SEED1_TENUN888_TABLE_CREATE_SQL =  u"""create table if not exists {table_name}
666     (id integer primary key, {table_no} integer, {seed} integer unique, {result_num} integer,
667     {amulet_id1} integer, {amulet_id2} integer, {amulet_id3} integer,
668     {amulet_id4} integer, {amulet_id5} integer, {amulet_id6} integer, {amulet_id7} integer,
669     {skill_id1} integer, {skill_id2} integer, {skill_id3} integer,
670     {skill_id4} integer, {skill_id5} integer, {skill_id6} integer, {skill_id7} integer,
671     foreign key({amulet_id1}) references {amulet_table}(id),
672     foreign key({amulet_id2}) references {amulet_table}(id),
673     foreign key({amulet_id3}) references {amulet_table}(id),
674     foreign key({amulet_id4}) references {amulet_table}(id),
675     foreign key({amulet_id5}) references {amulet_table}(id),
676     foreign key({amulet_id6}) references {amulet_table}(id),
677     foreign key({amulet_id7}) references {amulet_table}(id),
678     foreign key({skill_id1}) references {skill_table}(id),
679     foreign key({skill_id2}) references {skill_table}(id),
680     foreign key({skill_id3}) references {skill_table}(id),
681     foreign key({skill_id4}) references {skill_table}(id),
682     foreign key({skill_id5}) references {skill_table}(id),
683     foreign key({skill_id6}) references {skill_table}(id),
684     foreign key({skill_id7}) references {skill_table}(id)
685     );""".format(
686         seed=SEED1_TENUN_COL_SEED1,
687         table_no=SEED1_TENUN_COL_TABLE,
688         result_num=SEED1_TENUN_COL_RESULT_NO,
689         amulet_id1=SEED1_TENUN_COL_AMULET1,
690         amulet_id2=SEED1_TENUN_COL_AMULET2,
691         amulet_id3=SEED1_TENUN_COL_AMULET3,
692         amulet_id4=SEED1_TENUN_COL_AMULET4,
693         amulet_id5=SEED1_TENUN_COL_AMULET5,
694         amulet_id6=SEED1_TENUN_COL_AMULET6,
695         amulet_id7=SEED1_TENUN_COL_AMULET7,
696         skill_id1=SEED1_TENUN_COL_SKILL1_1,
697         skill_id2=SEED1_TENUN_COL_SKILL1_2,
698         skill_id3=SEED1_TENUN_COL_SKILL1_3,
699         skill_id4=SEED1_TENUN_COL_SKILL1_4,
700         skill_id5=SEED1_TENUN_COL_SKILL1_5,
701         skill_id6=SEED1_TENUN_COL_SKILL1_6,
702         skill_id7=SEED1_TENUN_COL_SKILL1_7,
703         amulet_table=AMULET_TABLE_NAME,
704         skill_table=SKILL_TABLE_NAME,
705         table_name=SEED1_TENUN888_TABLE)
706 SEED1_TENUN888_TABLE_INSERT_SQL = u"""insert into {table_name}
707     (id, {table_no}, {seed}, {result_num},
708     {amulet_id1}, {amulet_id2}, {amulet_id3},
709     {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7},
710     {skill_id1}, {skill_id2}, {skill_id3},
711     {skill_id4}, {skill_id5}, {skill_id6}, {skill_id7})
712     values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);""".format(
713         table_name=SEED1_TENUN888_TABLE,
714         seed=SEED1_TENUN_COL_SEED1,
715         table_no=SEED1_TENUN_COL_TABLE,
716         result_num=SEED1_TENUN_COL_RESULT_NO,
717         amulet_id1=SEED1_TENUN_COL_AMULET1,
718         amulet_id2=SEED1_TENUN_COL_AMULET2,
719         amulet_id3=SEED1_TENUN_COL_AMULET3,
720         amulet_id4=SEED1_TENUN_COL_AMULET4,
721         amulet_id5=SEED1_TENUN_COL_AMULET5,
722         amulet_id6=SEED1_TENUN_COL_AMULET6,
723         amulet_id7=SEED1_TENUN_COL_AMULET7,
724         skill_id1=SEED1_TENUN_COL_SKILL1_1,
725         skill_id2=SEED1_TENUN_COL_SKILL1_2,
726         skill_id3=SEED1_TENUN_COL_SKILL1_3,
727         skill_id4=SEED1_TENUN_COL_SKILL1_4,
728         skill_id5=SEED1_TENUN_COL_SKILL1_5,
729         skill_id6=SEED1_TENUN_COL_SKILL1_6,
730         skill_id7=SEED1_TENUN_COL_SKILL1_7)
731 SEED1_TENUN888_TABLE_SELECT_SQL = u""" select {seed} from {table_name} 
732     """.format(seed=SEED1_TENUN_COL_SEED1,
733         table_name=SEED1_TENUN888_TABLE)
734 SEED1_TENUN888_TABLE_SELECT_TABLE_NOS_FROM_SEED1_SQL = u"""select {table_no}, id from {table_name} 
735     where {seed1}={{seed1}}""".format(
736         seed1=SEED1_TENUN_COL_SEED1,
737         table_no=SEED1_TENUN_COL_TABLE,
738         table_name=SEED1_TENUN888_TABLE)
739 SEED1_TENUN888_TABLE_SELECT_NEAR_SEED1S_FROM_NO_SQL = u"""select id, {seed1} from {table_name} 
740     where {table_no}={{table_no}} and id>={{min_no}} and id<={{max_no}}
741     order by id ASC """.format(
742         seed1=SEED1_TENUN_COL_SEED1,
743         table_no=SEED1_TENUN_COL_TABLE,
744         table_name=SEED1_TENUN888_TABLE)
745 SEED1_TENUN888_TABLE_SELECT_ALL_FROM_SEED1_SQL = u"""select id, {table_no}, {result_num},
746     {amulet_id1}, {amulet_id2}, {amulet_id3},
747     {amulet_id4}, {amulet_id5}, {amulet_id6}, {amulet_id7},
748     {skill1_id1}, {skill1_id2}, {skill1_id3},
749     {skill1_id4}, {skill1_id5}, {skill1_id6}, {skill1_id7}
750     from {table_name} where {seed1}={{seed1}} """.format(
751         seed1=SEED1_TENUN_COL_SEED1,
752         table_no=SEED1_TENUN_COL_TABLE,
753         table_name=SEED1_TENUN888_TABLE,
754         result_num=SEED1_TENUN_COL_RESULT_NO,
755         amulet_id1=SEED1_TENUN_COL_AMULET1,
756         amulet_id2=SEED1_TENUN_COL_AMULET2,
757         amulet_id3=SEED1_TENUN_COL_AMULET3,
758         amulet_id4=SEED1_TENUN_COL_AMULET4,
759         amulet_id5=SEED1_TENUN_COL_AMULET5,
760         amulet_id6=SEED1_TENUN_COL_AMULET6,
761         amulet_id7=SEED1_TENUN_COL_AMULET7,
762         skill1_id1=SEED1_TENUN_COL_SKILL1_1,
763         skill1_id2=SEED1_TENUN_COL_SKILL1_2,
764         skill1_id3=SEED1_TENUN_COL_SKILL1_3,
765         skill1_id4=SEED1_TENUN_COL_SKILL1_4,
766         skill1_id5=SEED1_TENUN_COL_SKILL1_5,
767         skill1_id6=SEED1_TENUN_COL_SKILL1_6,
768         skill1_id7=SEED1_TENUN_COL_SKILL1_7)