[Improvement] Supporting other databases about pogom

[totoberg123]

I would prefer MySQL support.

[compuguy]

I think an advantage is that we could offload the database over to a separate server/instance if necessary.

[Sigi-cz]

what about using this as DB format? store static (forts/spawns) and dynamic (pokemon sightings) separately? Right now too much garbage is stored.
https://www.reddit.com/r/pokemongodev/comments/4ull2o/working_on_a_database_standard_for_map_objects/

[favll]

Peewee (the orm library we use) supports SQLite, MySQL and PostgreSQL. So it should be possible to add support for those other DBMS in the near future.

[nborrmann]

I defnitely want to support more dbs in the future, but not via a clunky command line flag.

We can do it, when we have a HTML config page.

[lisandro52]

@favll

Peewee (the orm library we use)

Damn, I should've taken a better look at the model. I'm sorry. Next time, I'll examine the codebase better before posting here.

@nborrmann

but not via a clunky command line flag

What else do you propose, besides the config object inside __init__.py and the web config interface?

[nborrmann]

Just the web config interface actually.

[bas-stringer]

Why use an "external" database at all? Even with large search radii, the amount of data to store is small enough to simply store and process in-memory, or isn't it?

[nborrmann]

@bas-stringer I want stats to work across restarts. And in the future we want heatmaps, which depend on a large dataset.

[bas-stringer]

@nborrmann I'm unsure what exactly you mean by stats working across restarts. The heatmap thing sounds fun, but again, I have doubts if the quantity of data is so big it can't be done in-memory.

How about going the in-memory route for updating the map ASAP while scanning, and then in between scan rounds updating the database? (Or, possibly, dumping serialized data e.g. through (c)pickle, simplejson or ujson? Caveat emptor with all of these, as the speedups they provide over pickle and json resp. come at the cost of weird quirks that can be very difficult to debug.)

Analyses requiring historical data can still reload it from databases/dumps as required, and the max velocity of your data insertion/(de)serialization becomes far less impactful on the speed of your scan.

[MrE81]

SQLite causes too much of a bottleneck to run an efficient number of accounts (at least on my POS box). I would love to see MySQL implementation.

[lisandro52]

I already added MySQL support on my fork (I didn't push the changes, though. Sorry!). I simply changed line 7 SqliteDatabase with MySQLDatabase, and changed lines 15-19 on models.py with the following:

db = MySQLDatabase('database_name', user='user', passwd='password', host='localhost')

(Adjust the options according to your system)
You'll also need to install PyMySQL (pip install PyMySQL) and have a database with database_name (or whatever you want) created on your MySQL/MariaDB instance.

[nborrmann]

@lisandro52 Is the performance better with MySql?

[lisandro52]

@nborrmann I couldn't say, actually, because I'm using no more than 10 accounts. I added MySQL because I wanted to keep the data on something else that wasn't a file and be able to better share the data with other instances of pogom.

[MrE81]

PERFECT!!!

BTW, the tables created by pogom do not play well with the tables created by pokemongomap (AAHHH...), so creating a new DB is a necessity.

[lisandro52]

@MrE81 That's odd, I've been using pogom to gather the data (two instances), and PokemonGo-Map to display it, both sharing the same MySQL DB, and it's working great.

[nborrmann]

I just profiled it. A worker thread spends about 5-7% of the time writing to the database. So this really seems not to be a huge performance benefit.

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
      690   49.620    0.072   49.620    0.072 {method 'read' of '_ssl._SSLSocket' objects}
       22   13.482    0.613   13.482    0.613 {time.sleep}
      103    4.086    0.040    7.583    0.074 C:\pogom\pogom\models.py:105(parse_map)
      162    2.883    0.018    2.888    0.018 {method 'execute' of 'sqlite3.Cursor' objects}
     4349    1.796    0.000    1.796    0.000 {method 'acquire' of 'thread.lock' objects}
       22    1.046    0.048    1.046    0.048 {method 'do_handshake' of '_ssl._SSLSocket' objects}

[MrE81]

@lisandro52 I am far from a SQL guru, I am barely a novice, but my guess would be that reading the data via PokemonGo-Map might be more accommodating than trying to write to the tables created by same using pogom. Of course, I didn't put much work into it, I just saw the updates freeze up when I tried to use the same database, with no mons showing on the map, so I quickly created a new map.

[lisandro52]

@MrE81 You're totally right on that. But what I forgot to say is that the DB I'm using with pogom was originally created by the PokemonGo-Map worker, and later reused by pogom.

[vinsilsim]

@lisandro52

Traceback (most recent call last):
File "runserver.py", line 13, in
from pogom.app import Pogom
File "/root/pogom/pogom/app.py", line 17, in
from .models import Pokemon, Gym, Pokestop
File "/root/pogom/pogom/models.py", line 23
log = logging.getLogger(name)
^

=/

[diputz42]

@lisandro52 Can you explain how you're using PokemonGo-Map to display the pokemon? I'm trying to run a server only with the PokemonGo-Map for visualization using the data collected from Pogom. Pogom is writing to MySQL and PokemonGo-Map is pointed to MySQL. But no pokemon are showing up on the map. Although, the stats function is working fine...

[lisandro52]

@vinsilsim I'm so sorry for answering so late. That line on models.py looks odd. Instead of log = logging.getLogger(name) it should be log = logging.getLogger(__name__)

@diputz42 That's strange... If the stats page is doing fine, it's probably something else. Have you run all the steps to install the PokeMap? (grunt build, for example). Like I said here, or another issue, it was pretty plug&play: started pogom without the webserver -> started PokemonGo-Map -> that's it. If you are still having trouble with that, I'd be happy to help you, just send me a PM.

[diputz42]

@lisandro52 I got it working now, but thanks for the offer! The community here has been great. I cloned the latest release of PoGoMap. I was previously running a dev build. Seems pogom and PoGoMap have slightly different field names and settings in the data tables. I had to create a new DB with pogom, copy all rows from my old DB over, add a couple columns, and now PoGoMap and pogom play nicely. It didn't seem to work the other way around for me, which is how I understand you did it.

[favll]

8f0b05d