DB: Store dates as integers

[kfsone]

We're storing dates as strings, increasing various calculation costs (DATETIME storage depends on what you put in it).

Propose we use a bigint (64bit) with a unix epoch timestamp in seconds, I don't think we care about miliseconds.

Currently, we're limiting station item rows on age in an expensive way:

592:                where_clauses.append("CAST(strftime('%s', modified) AS INTEGER) >= :cutoffS")
594:                where_clauses.append("UNIX_TIMESTAMP(modified) >= :cutoffS")

(in both cases, we're doing a string-parse on every matching stationitem to produce a datetime; this should probably at least be moved so it is the last where clause so that we've done as much culling as we can before we do this operation)

Q: It looks like the .sql file is still currently authoritative, how do we handle schema changes? I was looking for something like alembic or similar.

[Tromador]

Schema changes are simple enough. We just do it and patch orm_models.py to match, then splat the db and reimport the data. The nice thing about dumping our db to csv on the regular is that we can import it back with minimal disruption.

What I will say is if we're getting into a discussion about that we probably want to include @bgol Bernd, as his edmc plugin talks directly to TD database and he would also be affected.

[kfsone]

I did some preliminary, unscientific tests with a local db and the effects were pronounced. I'm thinking I did something wrong - import was about 30% faster, as the index build was much slimmer, and the "scanning market" took 70% less time when I used "--age 3". i probably broke something.

[bgol]

Oh, I'm all for it. In my own database I only use BIGINTs for storing the unix epoch for timestamps (using _ts as name suffix).

Another difference is that I'm using the station table to store the timestamps for commodity, outfitting and shipyrad markets because not every item needs its own timestamp (changing that may be some more work). It is an leftover from the old times when each item was transmitted separately over EDDN (if you ever wondered why the version is >1 for these three schemas).

I just need some way to check which database version the user has to adapt my plugin accordingly.

BTW, hi oliver ✋

[kfsone]

Hey, rockstar :)