MySQL support

[prostolyubo]

So, I've spent the last 2 days trying to get this awesome tool to put data into MySQL database instead of SQLite.

If anyone wants to do it, I'll leave a guide here. I won't make a PR because I did not manage to "port" all the data and made some ugly hotfixes. So first

Configuration

Install aditionally:

RUN pip install PyMySQL
RUN pip install cryptography

in your GarminConnectConfig.json modify the db entry:

    "db": {
        "type" : "mysql",
        "user" : "yourusername",
        "password" : "yourpassword",
        "host" : "yourhostname"
    },

Maximum field length

Now you need to fix the files. EVERY string field needs a maximum length of characters defined. So we are changing all String values into String(XX) where XX is the maximum length. If any field of any entry will exceed this value entire entry will be rejected. Here's an excerpt from my activities_db.py for reference:

    activity_id = Column(String(32), primary_key=True)
    name = Column(String(64))
    description = Column(String(64))
    type = Column(String(16))
    course_id = Column(Integer)
    laps = Column(Integer)
    sport = Column(String(32))
    sub_sport = Column(String(32))

also any fields like serial_number or file_id must be of type BigInteger instead of Integer (my serial number exceeded the maximum value for int). To do so, find all fields like that and replace Integer with BigInteger. For each file you modify like that you must also add a following line at the top of the file:
from sqlalchemy import BigInteger

Keep in mind there are some files in https://github.com/tcgoetz/utilities which is a submodule and you need to take care of those too.

SQL Syntax Errors

Double enums

Enums are case insensitive so we need to remove 'Unknown' manufacturer from the list below since it already contains 'unknown' one and MySQL will complain.

  Manufacturer = idbutils.derived_enum.derive('Manufacturer', fitfile.Manufacturer, {'Microsoft' : 100001, 'Unknown': 100000})

Repairing views

Change every CREATE VIEW IF EXISTS into CREATE OR REPLACE VIEW. I don't know MySQL enough to understand the difference or the right approach, I'm fine with doing some redundant replacements though.

There are functions that create views that use the following syntax:

WHERE
  Activities.sport == "walking"

the ones I found were i.e. _create_sport_view
we have to correct two things in them:

1. == should be = (this will make the comparison case sensitive, so I guess there might be some edge cases)
2. Activities.sport should be activities.sport. It is taken from a variable, but I didn't want to break the working program so here's my dirty hack: I just hardcoded a literal string into the function.

Before:

        filter = literal_column(f'{Activities.sport} == "{sport}"')

After

        filter = literal_column(f'activities.sport = "{sport}"')

So this is enough to sync your "steps", "sleep", "activities" and "monitoring". I didn't try any other stats.
I believe that with this guide you will be able to get MySQL connection in 20 minutes instead of 2 days.

[tcgoetz]

I suggest you submit the non-hacky parts as a PR and the rest can be an article we add to the wiki. If you want to publish it as an article elsewhere we could link to it from the third party projects section.