· 5 years ago · May 30, 2020, 05:12 AM
1import sqlite3
2
3conn = sqlite3.connect('warframe.db')
4c = conn.cursor()
5
6c.execute("PRAGMA foreign_keys = ON")
7
8
9
10def get_default_tables():
11 from voidparse import RelicStatus, RelicRarity
12 drop_all()
13
14 c.execute("CREATE TABLE relic_status (id integer PRIMARY KEY, status text collate nocase, status_print text)")
15
16
17 status_list = [(RelicStatus.NORMAL,"Normal", ""),
18 (RelicStatus.VAULTED,"Vaulted", "(V)"),
19 (RelicStatus.BARO,"Baro", "(B)")]
20
21
22 relic_status = [ (a.value,b,c) for a,b,c in status_list]
23
24
25 c.executemany("INSERT INTO relic_status VALUES (?,?,?)", relic_status)
26
27
28
29 c.execute("CREATE TABLE reward_rarity (id integer PRIMARY KEY, rarity text collate nocase)")
30
31 rarity_list = [(RelicRarity.COMMON,'Common'),
32 (RelicRarity.UNCOMMON,'Uncommon'),
33 (RelicRarity.RARE,'Rare')]
34
35 reward_rarity = [ (a.value, b) for a,b in rarity_list ]
36
37 c.executemany("INSERT INTO reward_rarity VALUES (?,?)", reward_rarity)
38
39 conn.commit()
40
41
42def drop_table(x):
43 c.execute("DROP TABLE IF EXISTS {}".format(x))
44 conn.commit()
45
46def drop_all():
47 #First drop the data
48 drop_data()
49
50 #Now drop the static tables
51 drop_table("relic_status")
52 drop_table("reward_rarity")
53
54
55def drop_data():
56 #First drop inventory items (as these will be invalid)
57 drop_user_data()
58
59 #Clear the database
60 drop_table("rewards")
61 drop_table("relics")
62 drop_table("parts")
63 drop_table("items")
64
65
66def drop_user_data():
67 drop_table("relic_inv")
68
69def parseStatus(status):
70 from voidparse import RelicStatus
71 return status.value
72
73
74
75def reset_data_tables():
76 drop_data()
77
78 #Create items table
79 c.execute('''CREATE TABLE items
80 (
81 id INTEGER PRIMARY KEY NOT NULL,
82 name TEXT COLLATE NOCASE,
83 status_id INTEGER,
84 FOREIGN KEY(status_id) REFERENCES relic_status(id)
85 )''')
86
87
88
89
90 #Parts table
91 c.execute('''CREATE TABLE parts
92 (
93 id INTEGER PRIMARY KEY NOT NULL,
94 item_id INTEGER REFERENCES items,
95 name TEXT COLLATE NOCASE,
96 status_id INTEGER,
97 FOREIGN KEY(status_id) REFERENCES relic_status(id)
98 )''')
99
100
101 #Now create the tables
102 c.execute('''CREATE TABLE relics
103 (
104 id INTEGER PRIMARY KEY NOT NULL,
105 tier TEXT COLLATE NOCASE,
106 name TEXT COLLATE NOCASE,
107 status_id INTEGER,
108 FOREIGN KEY(status_id) REFERENCES relic_status(id)
109 )''')
110
111 #Rewards
112 c.execute('''CREATE TABLE rewards
113 (
114 id INTEGER PRIMARY KEY NOT NULL,
115 relic_id INTEGER,
116 part_id INTEGER,
117 rarity_id INTEGER,
118 FOREIGN KEY(rarity_id) REFERENCES reward_rarity(id)
119 FOREIGN KEY(relic_id) REFERENCES relics(id)
120 FOREIGN KEY(part_id) REFERENCES parts(id)
121 )''')
122
123
124
125
126 conn.commit()
127
128def vaulted(i):
129 return i.status.value
130
131
132def update_voiddata():
133 from voidparse import voiddata_t
134
135 #Clear the tables
136 reset_data_tables()
137
138 #First grab the items/relics lists
139 void_url = u"http://warframe.wikia.com/wiki/Module:Void/data?action=raw"
140 void = voiddata_t(void_url,refresh=True)
141
142 #Insert items into db
143 item_data = [(i.full_name.title(), vaulted(i)) for i in void.items.values()]
144 c.executemany("INSERT INTO items(name,status_id) VALUES (?,?)",item_data)
145
146 #Insert relics
147 relic_data = [(r.tier, r.name, parseStatus(r.status)) for r in void.relics]
148 c.executemany("INSERT INTO relics(tier,name,status_id) VALUES (?,?,?)",relic_data)
149
150 #Loop over items again
151 for i in void.items.values():
152
153 #Find the item in the db
154 c.execute("SELECT id FROM items WHERE name=?",(i.full_name,))
155 item_id = c.fetchone()[0]
156 part_data = [(item_id, p.part.title(), vaulted(p)) for p in i.parts.values()]
157 c.executemany("INSERT INTO parts(item_id,name,status_id) VALUES (?,?,?)",part_data)
158
159
160 #Finally build the rewards table
161 for relic in void.relics:
162
163 c.execute("SELECT id FROM relics WHERE tier=? and name=?",(relic.tier,relic.name))
164 relic_id = c.fetchone()[0]
165
166 for i in relic.rewards:
167
168 #Get the item id
169 c.execute('''
170 SELECT parts.id FROM parts
171 INNER JOIN items ON parts.item_id = items.id
172 WHERE items.name=? AND parts.name=?''', (i.item, i.part))
173
174 part_id = c.fetchone()[0]
175 c.execute("INSERT INTO rewards(relic_id, part_id, rarity_id) VALUES (?,?,?)",(relic_id, part_id, i.rarity.value))
176
177
178 conn.commit()
179
180
181
182def reset_db():
183 get_default_tables()
184 update_voiddata()
185
186
187
188def printitem(itemtype, itemname, vaulted):
189 if vaulted == "Normal":
190 return "{}: {:20}\n".format(itemtype, itemname)
191 else:
192 return "{}: {:20} {}\n".format(itemtype, itemname, vaulted)
193
194
195def print_item(item_name,vaulted=True):
196 #Get the item info
197 c.execute('''
198 SELECT items.id, status_print
199 FROM items
200 INNER JOIN relic_status on items.status_id = relic_status.id
201 WHERE name=?''',(item_name,))
202
203 item_id, item_status = c.fetchone()
204 printstr = printitem("Item",item_name, item_status)
205
206 #Get the part names
207 c.execute('''
208 SELECT parts.id, name, status_print
209 FROM parts
210 INNER JOIN relic_status on status_id = relic_status.id
211 WHERE item_id=?''',(item_id,))
212
213 parts = c.fetchall()
214
215 for part_id, part_name, part_status in parts:
216 printstr += printitem("\tPart", part_name, part_status)
217
218 relics = get_relics(part_id,vaulted)
219
220 for r in relics:
221 #print(r)
222 printstr += "\t\tRelic: {:4} {:3} {:10} {}\n".format(*r)
223
224 return printstr
225
226
227
228def get_relics(part_id,vaulted):
229 if vaulted:
230 #Get the part names
231 c.execute('''
232 SELECT relics.tier, relics.name, rarity, status_print
233 FROM rewards
234 INNER JOIN relics on relics.id = relic_id
235 INNER JOIN reward_rarity on rarity_id = reward_rarity.id
236 INNER JOIN relic_status on status_id = relic_status.id
237 WHERE part_id=?
238 ORDER BY status_id ASC, rarity_id ASC''',(part_id,))
239 else:
240 #Get the part names
241 c.execute('''
242 SELECT relics.tier, relics.name, rarity, status_print
243 FROM rewards
244 INNER JOIN relics on relics.id = relic_id
245 INNER JOIN reward_rarity on rarity_id = reward_rarity.id
246 INNER JOIN relic_status on status_id = relic_status.id
247 WHERE part_id=? AND (status="Normal" OR status="Baro")
248 ORDER BY status_id ASC, rarity_id ASC, relic_id ASC''',(part_id,))
249 return c.fetchall()
250
251
252
253def get_unvaulted():
254 c.execute('''
255 SELECT name FROM items
256 INNER JOIN relic_status on relic_status.id = status_id
257 WHERE status = "Normal"
258 ORDER BY name COLLATE NOCASE ASC
259 ''')
260
261 return c.fetchall()
262
263
264
265def find_item(item):
266 import difflib
267
268 #Build list of items
269 c.execute("SELECT name from items")
270 item_list = set(i[0] for i in c.fetchall())
271
272 search_val = item.title()
273
274 if search_val in item_list:
275 return [search_val]
276 else:
277 return difflib.get_close_matches(search_val,item_list,cutoff=0.2)
278
279
280
281def create_users_table():
282 drop_table("relic_inv")
283 drop_table("users")
284
285 #Create users table
286 c.execute('''CREATE TABLE users
287 (
288 id INTEGER PRIMARY KEY NOT NULL,
289 name TEXT COLLATE NOCASE
290 )''')
291
292 #Create relic inventory table
293 c.execute('''CREATE TABLE relic_inv
294 (
295 id INTEGER PRIMARY KEY NOT NULL,
296 relic_id INTEGER,
297 user_id INTEGER,
298 number INTEGER,
299 FOREIGN KEY(relic_id) REFERENCES relics(id)
300 FOREIGN KEY(user_id) REFERENCES users(id)
301 )''')
302
303 conn.commit()
304
305
306
307def add_user(user):
308 print("Adding user", user)
309 c.execute("INSERT INTO users(name) VALUES (?)",(user,))
310 conn.commit()
311
312def add_relic(user,relic_tier,relic_name,number):
313 #Check user exists
314 if not user_exists(user):
315 add_user(user)
316
317
318 #Look up user and relic id
319 c.execute('''
320 INSERT INTO relic_inv(user_id,relic_id,number)
321 VALUES (
322 (SELECT id FROM users WHERE users.name=?),
323 (SELECT id FROM relics WHERE relics.tier=? AND relics.name=?),
324 ?
325 )''', (user,relic_tier,relic_name,number))
326
327 #print(c.fetchall())
328
329 conn.commit()
330
331
332
333def user_exists(username):
334 c.execute("SELECT id FROM users WHERE name=?",(username,))
335 return c.fetchone() is not None
336
337
338def print_item_owned(item_name,user):
339 #Get the item info
340 c.execute('''
341 SELECT items.id, status_print
342 FROM items
343 INNER JOIN relic_status on items.status_id = relic_status.id
344 WHERE name=?''',(item_name,))
345
346 item_id, item_status = c.fetchone()
347 printstr = printitem("Item",item_name, item_status)
348
349 #Get the part names
350 c.execute('''
351 SELECT parts.id, name, status_print
352 FROM parts
353 INNER JOIN relic_status on status_id = relic_status.id
354 WHERE item_id=?''',(item_id,))
355
356 parts = c.fetchall()
357
358 for part_id, part_name, part_status in parts:
359 printstr += printitem("\tPart", part_name, part_status)
360
361 relics = get_relics_owned(part_id,user)
362
363 for r in relics:
364 #print(r)
365 printstr += "\t\tRelic: {:4} {:2} {:8} {:3}-owned {}\n".format(*r)
366
367 return printstr
368
369
370
371def get_relics_owned(part_id,user):
372 #Get the part names
373 c.execute('''
374 SELECT relics.tier, relics.name, rarity, number, status_print
375 FROM rewards
376 INNER JOIN relics on relics.id = rewards.relic_id
377 INNER JOIN reward_rarity on rarity_id = reward_rarity.id
378 INNER JOIN relic_status on status_id = relic_status.id
379 INNER JOIN relic_inv on relic_inv.relic_id = rewards.relic_id
380 INNER JOIN users on users.id = relic_inv.user_id
381 WHERE part_id=? AND users.name = ?
382 ORDER BY status_id ASC, rarity_id ASC''',(part_id,user))
383 return c.fetchall()
384
385
386
387def import_json(user,filename):
388 import json
389
390 with open(filename,'r') as fd:
391 data = json.load(fd)
392
393 c.execute("SELECT id FROM users WHERE name=?",(user,))
394 user_id = c.fetchone()[0]
395
396
397 for a in data:
398 for b in a:
399 #Look up user and relic id
400 c.execute('''
401 INSERT INTO relic_inv(user_id,relic_id,number)
402 VALUES (
403 ?,
404 (SELECT id FROM relics WHERE relics.tier=? AND relics.name=?),
405 ?
406 )''', (user_id,*b))
407
408 conn.commit()