· 8 years ago · Jul 16, 2018, 12:36 PM
1from pathlib import Path
2import os
3import time
4import math
5import datetime
6import time
7from sqlalchemy import create_engine, Column, Boolean, Integer, String, Float, SmallInteger, \
8 BigInteger, ForeignKey, Index, UniqueConstraint, \
9 create_engine, cast, func, desc, asc, desc, and_, exists
10from sqlalchemy.orm import sessionmaker, relationship, eagerload, foreign, remote, scoped_session
11from sqlalchemy.types import TypeDecorator, Numeric, Text, TIMESTAMP
12from sqlalchemy.ext.declarative import declarative_base
13from sqlalchemy.orm.exc import NoResultFound
14from logging import basicConfig, getLogger, FileHandler, StreamHandler, DEBUG, INFO, ERROR, Formatter
15from geopy.distance import vincenty
16from sys import argv
17import importlib
18LOG = getLogger('')
19
20if len(argv) >= 2:
21 config = importlib.import_module(str(argv[1]))
22else:
23 config = importlib.import_module('config')
24
25class DBCacheFortIdsWithinRange:
26
27 def __init__(self, range, lat, lon, ids):
28 self.range = range
29 self.lat = lat
30 self.lon = lon
31 self.ids = ids
32
33class DBCache:
34
35 fort_ids_within_range = []
36 unknown_fort_id = None
37 not_a_fort_id = None
38
39if config.DB_ENGINE.startswith('mysql'):
40 from sqlalchemy.dialects.mysql import TINYINT, MEDIUMINT, BIGINT, DOUBLE, LONGTEXT
41
42 TINY_TYPE = TINYINT(unsigned=True) # 0 to 255
43 MEDIUM_TYPE = MEDIUMINT(unsigned=True) # 0 to 4294967295
44 UNSIGNED_HUGE_TYPE = BIGINT(unsigned=True) # 0 to 18446744073709551615
45 HUGE_TYPE = BigInteger
46 PRIMARY_HUGE_TYPE = HUGE_TYPE
47 FLOAT_TYPE = DOUBLE(precision=18, scale=14, asdecimal=False)
48 LONG_TEXT = LONGTEXT
49elif config.DB_ENGINE.startswith('postgres'):
50 from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, TEXT
51
52 class NumInt(TypeDecorator):
53 '''Modify Numeric type for integers'''
54 impl = Numeric
55
56 def process_bind_param(self, value, dialect):
57 if value is None:
58 return None
59 return int(value)
60
61 def process_result_value(self, value, dialect):
62 if value is None:
63 return None
64 return int(value)
65
66 @property
67 def python_type(self):
68 return int
69
70 TINY_TYPE = SmallInteger # -32768 to 32767
71 MEDIUM_TYPE = Integer # -2147483648 to 2147483647
72 UNSIGNED_HUGE_TYPE = NumInt(precision=20, scale=0) # up to 20 digits
73 HUGE_TYPE = BigInteger
74 PRIMARY_HUGE_TYPE = HUGE_TYPE
75 FLOAT_TYPE = DOUBLE_PRECISION(asdecimal=False)
76 LONG_TEXT = TEXT
77else:
78 class TextInt(TypeDecorator):
79 '''Modify Text type for integers'''
80 impl = Text
81
82 def process_bind_param(self, value, dialect):
83 return str(value)
84
85 def process_result_value(self, value, dialect):
86 return int(value)
87
88 TINY_TYPE = SmallInteger
89 MEDIUM_TYPE = Integer
90 UNSIGNED_HUGE_TYPE = TextInt
91 HUGE_TYPE = Integer
92 PRIMARY_HUGE_TYPE = HUGE_TYPE
93 FLOAT_TYPE = Float(asdecimal=False)
94
95Base = declarative_base()
96engine = create_engine(config.DB_ENGINE, pool_recycle=150, pool_size=config.POOL_SIZE, pool_pre_ping=True)
97
98class Fort(Base):
99 __tablename__ = 'forts'
100
101 id = Column(Integer, primary_key=True)
102 external_id = Column(String(35), unique=True)
103 lat = Column(FLOAT_TYPE)
104 lon = Column(FLOAT_TYPE)
105 name = Column(String(128))
106 url = Column(String(200))
107 sponsor = Column(SmallInteger)
108 weather_cell_id = Column(UNSIGNED_HUGE_TYPE)
109 park = Column(String(128))
110 parkid = Column(HUGE_TYPE)
111
112 sightings = relationship(
113 'FortSighting',
114 backref='fort',
115 order_by='FortSighting.last_modified'
116 )
117
118 raids = relationship(
119 'Raid',
120 backref='fort',
121 order_by='Raid.time_end'
122 )
123
124class FortSighting(Base):
125 __tablename__ = 'fort_sightings'
126
127 id = Column(PRIMARY_HUGE_TYPE, primary_key=True)
128 fort_id = Column(Integer, ForeignKey('forts.id'))
129 last_modified = Column(Integer, index=True)
130 team = Column(TINY_TYPE)
131 guard_pokemon_id = Column(SmallInteger)
132 slots_available = Column(SmallInteger)
133 is_in_battle = Column(Boolean, default=False)
134 updated = Column(Integer,default=time,onupdate=time)
135 total_cp = Column(SmallInteger)
136
137 __table_args__ = (
138 UniqueConstraint(
139 'fort_id',
140 'last_modified',
141 name='fort_id_last_modified_unique'
142 ),
143 )
144
145class Raid(Base):
146 __tablename__ = 'raids'
147
148 id = Column(Integer, primary_key=True)
149 external_id = Column(BigInteger, unique=True)
150 fort_id = Column(Integer, ForeignKey('forts.id'))
151 level = Column(TINY_TYPE)
152 pokemon_id = Column(SmallInteger)
153 move_1 = Column(SmallInteger)
154 move_2 = Column(SmallInteger)
155 time_spawn = Column(Integer, index=True)
156 time_battle = Column(Integer)
157 time_end = Column(Integer)
158 cp = Column(Integer)
159
160class GymImage(Base):
161 __tablename__ = 'gym_images'
162
163 id = Column(Integer, primary_key=True)
164 fort_id = Column(Integer)
165 param_1 = Column(Integer)
166 param_2 = Column(Integer)
167 param_3 = Column(Integer)
168 param_4 = Column(Integer)
169 param_5 = Column(Integer)
170 param_6 = Column(Integer)
171 created = Column(Integer,default=time)
172
173class PokemonImage(Base):
174 __tablename__ = 'pokemon_images'
175
176 id = Column(Integer, primary_key=True)
177 pokemon_id = Column(Integer)
178 param_1 = Column(Integer)
179 param_2 = Column(Integer)
180 param_3 = Column(Integer)
181 param_4 = Column(Integer)
182 param_5 = Column(Integer)
183 param_6 = Column(Integer)
184 param_7 = Column(Integer)
185 created = Column(Integer,default=time)
186
187class DeviceLocationHistory(Base):
188 __tablename__ = 'device_location_history'
189
190 device_uuid = Column(String(40), primary_key=True)
191 timestamp = Column(Integer, default=time, primary_key=True)
192 lat = Column(FLOAT_TYPE)
193 lon = Column(FLOAT_TYPE)
194
195# create gym_images and pokemon_images table if non
196Base.metadata.create_all(bind=engine)
197Session = sessionmaker(bind=engine)
198
199def get_gym_images(session):
200 gym_images = session.query(GymImage).all()
201 return gym_images
202
203def get_pokemon_images(session):
204 pokemon_images = session.query(PokemonImage).all()
205 return pokemon_images
206
207unknown_fort_name = 'UNKNOWN FORT'
208def get_unknown_fort_id(session):
209 if DBCache.unknown_fort_id is not None:
210 return DBCache.unknown_fort_id
211
212 unknown_fort = session.query(Fort).filter_by(name=unknown_fort_name).first()
213 session.commit()
214 # Check UNKNOWN FORT existance if not, add
215 if unknown_fort is None:
216 session.add(Fort(name=unknown_fort_name))
217 session.commit()
218 unknown_fort = session.query(Fort).filter_by(name=unknown_fort_name).first()
219 session.commit()
220
221 DBCache.unknown_fort_id = unknown_fort.id
222 return DBCache.unknown_fort_id
223
224not_a_fort_name = 'NOT A FORT'
225def get_not_a_fort_id(session):
226 if DBCache.not_a_fort_id is not None:
227 return DBCache.not_a_fort_id
228
229 not_a_fort = session.query(Fort).filter_by(name=not_a_fort_name).first()
230 session.commit()
231 # Check NOT A FORT existance if not, add
232 if not_a_fort is None:
233 session.add(Fort(name=not_a_fort_name))
234 session.commit()
235 not_a_fort = session.query(Fort).filter_by(name=not_a_fort_name).first()
236 DBCache.not_a_fort_id = not_a_fort.id
237 return DBCache.not_a_fort_id
238
239def get_raid_battle_time(session, fort_id):
240 raid = session.query(Raid).filter_by(fort_id=fort_id).first()
241 session.commit()
242 if raid is None:
243 session.add(Raid(fort_id=fort_id))
244 session.commit()
245 raid = session.query(Raid).filter_by(fort_id=fort_id).first()
246 raid.time_battle = 0
247 if raid.time_battle is None:
248 raid.time_battle = 0
249 return raid.time_battle
250
251def get_raid_pokemon_id(session, fort_id):
252 raid = session.query(Raid).filter_by(fort_id=fort_id).first()
253 session.commit()
254 if raid is None:
255 session.add(Raid(fort_id=fort_id))
256 session.commit()
257 raid = session.query(Raid).filter_by(fort_id=fort_id).first()
258 raid.pokemon_id = -1
259 if raid.pokemon_id is None:
260 raid.pokemon_id = -1
261 return raid.pokemon_id
262
263def update_raid_egg(session, fort_id, level, time_battle):
264 raid = session.query(Raid).filter_by(fort_id=fort_id).first()
265 if raid is None:
266 session.add(Raid(fort_id=fort_id))
267 session.commit()
268 raid = session.query(Raid).filter_by(fort_id=fort_id).first()
269 raid.level = int(level)
270 raid.pokemon_id = 0
271 raid.time_spawn = time_battle - 3600
272 raid.time_battle = time_battle
273 raid.time_end = time_battle + 2700
274 raid.move_1 = 0
275 raid.move_2 = 0
276 raid.cp = 0
277 session.commit()
278
279def update_raid_mon(session, fort_id, pokemon_id):
280 print('DEBUG - Updating Raid Mon')
281 raid = session.query(Raid).filter_by(fort_id=fort_id).first()
282 if raid is None:
283 session.add(Raid(fort_id=fort_id))
284 raid = session.query(Raid).filter_by(fort_id=fort_id).first()
285 raid.pokemon_id = int(pokemon_id)
286 raid.move_1 = 133
287 raid.move_2 = 133
288 raid.cp = 0
289 print('DEBUG - Done Updating Raid Mon')
290
291def updata_fort_sighting(session, fort_id, unix_time):
292 print('DEBUG - Updating Fort Sightings')
293 fort_sighting = session.query(FortSighting).filter_by(fort_id=fort_id).first()
294 if fort_sighting is None:
295 session.add(FortSighting(fort_id=fort_id, team=int(0), last_modified=int(unix_time), updated=int(unix_time)))
296 fort_sighting = session.query(FortSighting).filter_by(fort_id=fort_id).first()
297 fort_sighting.updated = int(unix_time)
298 fort_sighting.last_modified = int(unix_time)
299 fort_sighting.team = int(0)
300 print('DEBUG - Done Updating Fort Sightings')
301
302def add_gym_image(session,fort_id,top_mean0,top_mean1,top_mean2,left_mean0,left_mean1,left_mean2):
303 session.add(GymImage(fort_id=fort_id,param_1=top_mean0,param_2=top_mean1,param_3=top_mean2,param_4=left_mean0,param_5=left_mean1,param_6=left_mean2,created=int(datetime.datetime.now().timestamp())))
304 session.commit()
305
306def update_gym_image(session,gym_image_id,gym_image_fort_id):
307 gym_image = session.query(GymImage).filter_by(id=gym_image_id).first()
308 session.commit()
309 if gym_image is None:
310 LOG.info('No gym image found with id:{}'.format(gym_image_fort_id))
311 return False
312 else:
313 gym_image.fort_id = gym_image_fort_id
314 session.commit()
315 LOG.info('gym image {} is set to fort_id {}'.format(gym_image_id,gym_image_fort_id))
316 return True
317
318def add_pokemon_image(session,mon_id,mean1,mean2,mean3,mean4,mean5,mean6,mean7):
319 session.add(PokemonImage(pokemon_id=mon_id,param_1=mean1,param_2=mean2,param_3=mean3,param_4=mean4,param_5=mean5,param_6=mean6,param_7=mean7,created=int(datetime.datetime.now().timestamp())))
320 session.commit()
321
322def update_pokemon_image(session,pokemon_image_id, pokemon_id):
323 pokemon_image = session.query(PokemonImage).filter_by(id=pokemon_image_id).first()
324 session.commit()
325 if pokemon_image is None:
326 LOG.info('No pokemon image found with id: {}'.format(pokemon_image_id))
327 return False
328 else:
329 pokemon_image.pokemon_id = pokemon_id
330 session.commit()
331 LOG.info('pokemon image {} is set to pokemon_id {}'.format(pokemon_image_id,pokemon_id))
332 return True
333
334def get_gym_image_id(session,top_mean0,top_mean1,top_mean2,left_mean0,left_mean1,left_mean2):
335 gym_image = session.query(GymImage).filter_by(param_1=top_mean0,param_2=top_mean1,param_3=top_mean2,param_4=left_mean0,param_5=left_mean1,param_6=left_mean2).first()
336 session.commit()
337 if gym_image is None:
338 unknown_fort_id = get_unknown_fort_id(session)
339 add_gym_image(session,unknown_fort_id,top_mean0,top_mean1,top_mean2,left_mean0,left_mean1,left_mean2)
340 gym_image = session.query(GymImage).filter_by(param_1=top_mean0,param_2=top_mean1,param_3=top_mean2,param_4=left_mean0,param_5=left_mean1,param_6=left_mean2).first()
341 return gym_image.id
342
343def get_gym_image_fort_id(session, gym_image_id):
344 gym_image = session.query(GymImage).filter_by(id=gym_image_id).first()
345 session.commit()
346 if gym_image is None:
347 return None
348 else:
349 return gym_image.fort_id
350
351def get_pokemon_image_id(session,mean1_in,mean2_in,mean3_in,mean4_in,mean5_in,mean6_in,mean7_in):
352 pokemon_image = session.query(PokemonImage).filter_by(param_1=mean1_in,param_2=mean2_in,param_3=mean3_in,param_4=mean4_in,param_5=mean5_in,param_6=mean6_in,param_7=mean7_in).first()
353 if pokemon_image is None:
354 add_pokemon_image(session,0,mean1_in,mean2_in,mean3_in,mean4_in,mean5_in,mean6_in,mean7_in)
355 pokemon_image = session.query(PokemonImage).filter_by(param_1=mean1_in,param_2=mean2_in,param_3=mean3_in,param_4=mean4_in,param_5=mean5_in,param_6=mean6_in,param_7=mean7_in).first()
356 session.commit()
357 return pokemon_image.id
358
359def get_pokemon_image_pokemon_id(session, pokemon_image_id):
360 pokemon_image = session.query(PokemonImage).filter_by(id=pokemon_image_id).first()
361 session.commit()
362 if pokemon_image is None:
363 return None
364 else:
365 return pokemon_image.pokemon_id
366
367def get_forts(session):
368 forts = session.query(Fort).all()
369 return forts
370
371def get_raids_for_forts(session, forts):
372 raids = session.query(Raid)\
373 .filter(Raid.fort_id.in_([fort.id for fort in forts]))\
374 .filter(Raid.time_end >= time.time())\
375 .all()
376 session.commit()
377 return raids
378
379def add_device_location_history(session, device_uuid, timestamp, lat, lon):
380 session.add(DeviceLocationHistory(device_uuid=device_uuid, timestamp=timestamp, lat=lat, lon=lon))
381 session.commit()
382
383def delete_old_device_location_history(session):
384 session.query(DeviceLocationHistory).filter(DeviceLocationHistory.timestamp <= (time.time() - 3600)).delete()
385 session.commit()
386
387def get_device_location_history(session, near, uuid):
388 device_location = session.query(DeviceLocationHistory).filter(DeviceLocationHistory.device_uuid == uuid).filter(DeviceLocationHistory.timestamp <= near).order_by(DeviceLocationHistory.timestamp.desc()).first()
389 session.commit()
390 return device_location
391
392def get_fort_ids_within_range(session, forts, range, lat, lon):
393
394 for cache in DBCache.fort_ids_within_range:
395 if cache.range == range and cache.lat == lat and cache.lon == lon:
396 return cache.ids
397
398 if forts is None:
399 forts = get_forts(session)
400
401 ids = []
402 for fort in forts:
403 distance = vincenty((fort.lat, fort.lon), (lat, lon)).meters
404 if distance <= range:
405 ids.append(fort.id)
406
407 session.commit()
408 cache_object = DBCacheFortIdsWithinRange(range, lat, lon, ids)
409 DBCache.fort_ids_within_range.append(cache_object)
410
411 return ids