Add option to use external DB

[adepssimius]

it would be nice to use postgres or mysql since I already host those and have backups automated, etc.

[pbogre]

Thanks for the issue. That's a good idea, I'll look into it, probably after the first release though.

[adepssimius]

Do you have an ORM preference and are you open to a PR for this? it would likely be a medium-large conceptual change and refactoring, but would be easier to define and use a new paradigm earlier rather than later.

I'm primarily a PHP and TS/node dev, so I don't have a good view of the ORM landscape in python, but looking at spoolman which is written in python and offers MySQL, Postgres, and SQLite support it appears that SQLAlchemy is an option to allow usage of whatever DB you want to use.

[pbogre]

I'd be more than happy to review a PR. I have looked into SQLAlchemy before however I opted for the standard sqlite3 for simplicity.

I am fine with switching to SQLAlchemy for this. The way I would like this change to happen is to keep the structure of the database model the same, and only implement different behavior for the various database types.

What I mean is i would like to have an abstract class for the databases which declares the basic methods (execute_query, execute_read_query, ...), and then have a class for each database type (Postgres, Sqlite, MySql, ...) that implements this abstract class (the base methods should all return the data in the exact same format). This way there wouldn't need to be many changes to the whole of the backend.

However, as I have little experience with Postgres and MySql, i'm not sure how different they are syntactically, and if we would need to change the queries for each database type. For example, would the queries in the initialize_tables method also valid with Postgres and MySql? If they are, a lot of these methods can already be implemented on the abstract class, and the same goes for most of the database interactions outside of database.py could be left untouched.

Edit: another problem i hadn't considered is that the airports.db file used to query airports (when running Jetlog for the first time, it is moved to jetlog.db and never used again) is in SQLite, so I'm not sure how it would be possible to move it to other database types.

[adepssimius]

Still talking without knowing exactly what SQLAlchemy does here but from what it seems like in spoolman and what other typical ORMs do is the creation of queries is abstracted away from your application code entirely and instead you make calls to the ORM, which can look kind of like calls to a DB but always the same no matter what back end you are talking to. The ORM handles the writing of the query in whatever specific dialect you have set up and returns in a standard way as well.

In your initialize tables, you would specify what kind of fields you want and the ORM would come up with the actual queries to make that happen in whatever DB dialect it is set up to use.

[pbogre]

Oh alright, if that's actually how SQLAlchemy handles it then it would be neat, and not too confusing of a change.