· 7 years ago · Sep 20, 2018, 06:32 PM
1###################################################################################
2#
3# KEEBOT database module
4#
5###################################################################################
6
7import os, sys, sqlite3
8
9# DB Setup
10
11if not os.path.exists('keebot.sqlite'):
12 print('keebot.sqlite not found, setting up...')
13 con = sqlite3.connect('keebot.sqlite')
14 cur = con.cursor()
15
16 # mm_filter object - Saved search queries
17 # UINT id: ID of this mm_filter
18 # STRING owner: Discord ID of the "owner" of this mm_filter
19 # STRING query: Search match query
20
21 sql = 'CREATE TABLE mm_filter (id INTEGER PRIMARY KEY, owner STRING, query STRING)'
22 cur.execute(sql)
23
24 #sql = 'INSERT INTO mm_filter (owner, query) VALUES ("0", "[EU-")'
25 #cur.execute(sql)
26
27 # mm_listing object - Previously found listings to avoid alerting with the same shit over and over
28 # UINT id: ID of this mm_listing
29 # STRING url: Shorthand URL for this mm_listing
30
31 sql = 'CREATE TABLE mm_listing (id INTEGER PRIMARY KEY, url STRING)'
32 cur.execute(sql)
33
34 con.commit()
35 con.close()
36 print('keebot.sqlite setup complete')
37
38 # EBAY
39
40if not os.path.exists('ebot.sqlite'):
41 print('ebot.sqlite not found, setting up...')
42 con = sqlite3.connect('ebot.sqlite')
43 cur = con.cursor()
44
45 sql = 'CREATE TABLE eb_listing (id INTEGER PRIMARY KEY, ebay_id STRING)'
46 cur.execute(sql)
47
48 sql = 'CREATE TABLE eb_blacklist (id INTEGER PRIMARY KEY, term STRING)'
49 cur.execute(sql)
50
51 con.commit()
52 con.close()
53 print('ebot.sqlite setup complete')
54
55# Add Ebay Listing to DB
56
57def eb_listing_add(id):
58 con = sqlite3.connect('ebot.sqlite')
59 cur = con.cursor()
60
61 sql = 'INSERT INTO eb_listing (ebay_id) VALUES ("{id1}")'.format(id1=id)
62 cur.execute(sql)
63
64 con.commit()
65 con.close()
66
67def eb_listing_get(id):
68 con = sqlite3.connect('ebot.sqlite')
69 cur = con.cursor()
70
71 sql = 'SELECT * FROM eb_listing WHERE ebay_id="{id1}"'.format(id1=id)
72 cur.execute(sql)
73
74 ret = cur.fetchone()
75 con.close()
76 return ret
77
78def eb_get_blacklist():
79 con = sqlite3.connect('ebot.sqlite')
80 cur = con.cursor()
81
82 sql = 'SELECT term FROM eb_blacklist'
83 cur.execute(sql)
84
85 ret = cur.fetchall()
86 con.close()
87 return ret
88
89
90# Add term to Ebay blacklist
91
92def eb_blacklist_add(term):
93 con = sqlite3.connect('ebot.sqlite')
94 cur = con.cursor()
95
96 sql = 'INSERT INTO eb_blacklist (term) VALUES ("{term1}")'.format(term1=term)
97 cur.execute(sql)
98
99 con.commit()
100 con.close()
101
102# Remove term from ebay blacklist
103
104def eb_blacklist_remove(term):
105 con = sqlite3.connect('ebot.sqlite')
106 cur = con.cursor()
107
108 sql = 'DELETE FROM eb_blacklist WHERE term=?'
109 cur.execute(sql, (term,))
110
111 con.commit()
112 con.close()
113
114# Get Ebay blacklist term
115
116def eb_blacklist_get(term):
117 con = sqlite3.connect('ebot.sqlite')
118 cur = con.cursor()
119
120 sql = 'SELECT * FROM eb_blacklist WHERE term="{term1}"'.format(term1=term)
121 cur.execute(sql)
122
123 ret = cur.fetchone()
124 con.close()
125 return ret
126
127
128# Get MM listing by url
129
130def mm_listing_get(url):
131 con = sqlite3.connect('keebot.sqlite')
132 cur = con.cursor()
133
134 sql = 'SELECT * FROM mm_listing WHERE url="{url1}"'.format(url1=url)
135 cur.execute(sql)
136
137 ret = cur.fetchone()
138 con.close()
139 return ret
140
141# Add a MM listing to the database
142
143def mm_listing_add(url):
144 con = sqlite3.connect('keebot.sqlite')
145 cur = con.cursor()
146
147 sql = 'INSERT INTO mm_listing (url) VALUES ("{url1}")'.format(url1=url)
148 cur.execute(sql)
149
150 con.commit()
151 con.close()
152
153# Add a MM filter to the database
154
155def mm_filter_add(user, query):
156 con = sqlite3.connect('keebot.sqlite')
157 cur = con.cursor()
158
159 sql = 'INSERT INTO mm_filter (owner, query) VALUES (?, ?)'
160 cur.execute(sql, (user, query,))
161
162 con.commit()
163 con.close()
164
165# Remove a MM filter from the database
166
167def mm_filter_delete(user, query):
168 con = sqlite3.connect('keebot.sqlite')
169 cur = con.cursor()
170
171 sql = 'DELETE FROM mm_filter WHERE owner=? AND query=?'
172 cur.execute(sql, (user, query,))
173
174 con.commit()
175 con.close()
176
177# Get all MM filters for user
178
179def mm_filters_get(user):
180 con = sqlite3.connect('keebot.sqlite')
181 cur = con.cursor()
182
183 sql = 'SELECT * FROM mm_filter WHERE owner="{user1}"'.format(user1=user)
184 cur.execute(sql)
185
186 ret = cur.fetchall()
187 con.close()
188 return ret
189
190# Checks if MM filter for user already exists
191
192def mm_filter_exists(user, query):
193 con = sqlite3.connect('keebot.sqlite')
194 cur = con.cursor()
195
196 sql = 'SELECT * FROM mm_filter WHERE owner=? AND query=?'
197 cur.execute(sql, (user, query,))
198
199 ret = cur.fetchone()
200 con.close()
201
202 return ret
203
204# Get all filter queries
205
206def mm_get_all_filters():
207 con = sqlite3.connect('keebot.sqlite')
208 con.row_factory = lambda cursor, row: row[0]
209 cur = con.cursor()
210
211 sql = 'SELECT query FROM mm_filter'
212 cur.execute(sql)
213
214 ret = cur.fetchall()
215 con.close()
216
217 return ret
218
219# Get all owners of a query
220
221def mm_get_all_owners(query):
222 con = sqlite3.connect('keebot.sqlite')
223 con.row_factory = lambda cursor, row: row[0]
224 cur = con.cursor()
225
226 sql = 'SELECT owner FROM mm_filter WHERE query=?'
227 cur.execute(sql, (query,))
228
229 ret = cur.fetchall()
230 con.close()
231
232 return ret