2019-09-13 – Mostly Software

Always a cool video about encryption and diffie-helman: https://www.youtube.com/watch?v=3QnD2c4Xovk. You just need a function that’s easy in one direction and hard in another. Think paint. Easy to determine what two color mix to make, hard to determine what two colors went in a mixture.

Start with one color. Server and client each have a private color that they mix with the public color. Then send it over. Then mix private color again. Each then has a 3 color mixture of their private, the other’s private, and the public color. A third party sniffer knows the combo of each private+public, but doesn’t know all 3 because it has never seen the privates without being mixed with the public.
Instead of paint, a discrete logarithm is usually used. Example: 3^Xmod17. Each party’s private is an X. Easy to calculate what that would yield, very hard to figure out what X is if only the answer is known (especially as the numbers get large).

The n+1 query problem in databases is for one-many relationships (or many-many, as well). You query once for the parent and then n times for the children, which can be very overloading if there are a lot of queries. This is called lazy loading. It’s the default for most ORMs. It’s faster if the relationships are small.

The opposite is called eager loading. It’s simply a join on the data. For large datasets, it’s much faster.

Pagination: https://flask-paginate.readthedocs.io/en/latest/.
i18n and l10n: https://pythonhosted.org/Flask-Babel/. Don’t need it yet with only-californian users, but is very easy to add. Just wrap your app with Babel(app).
Request tracking: https://matomo.org/. You must have an account with the third-party site, then wrap with Matomo(app, creds) and you can see all your request tracking. https://github.com/Lanseuo/flask-matomo.

flask-analytics can do it for google analytics, gaug, chartbeat, gosquared, more. https://github.com/citruspi/Flask-Analytics. All report to a third-party.
Here is another one that collects data and gives an endpoint to your site where it shows! flask-profiler: https://github.com/muatik/flask-profiler.
Here is another one: https://flask-monitoringdashboard.readthedocs.io/en/latest/functionality.html. This one looks the most comprehensive.

Tracing: zipkin is the main program. Python has a specific version. And Flask has a specific version: https://github.com/qiajigou/flask-zipkin.
CORS: https://github.com/corydolphin/flask-cors.
Webassets: If you want to bundle, minify, and compress all of your html/css/js and simply have jinja only import one file: https://flask-assets.readthedocs.io/en/latest/. Faster, smaller for production.
Frontend debug toolbar: Same as the one from django: https://flask-debugtoolbar.readthedocs.io/en/latest/. Add this.
n+1: Will help avoid lazy loading problems. Notifies you when this occurs and tells you to use a join instead: https://github.com/jmcarp/nplusone#flask-sqlalchemy.

My migration was trying to move scores from lines with UPDATE, but scores is a new table. Needed INSERT INTO. Changed.
Query for the full leaderboard (without totals) is:

old models: select season, week, email, sum(points) from picks, users where picks.user_id = users.id group by season, week, email order by season asc, week asc, sum(points) desc;
new models:

select row_number() over (order by sum(points) desc) as row_number, email, sum(points) from picks, users where picks.user_id = users.id and season = 2019 group by email order by sum(points) desc;

select sum(points) from picks, users where picks.user_id = users.id and season = 2019 group by email order by sum(points) desc;
Change year as necessary.

SUM in postgres returns null if the sum is zero, so this only shows people who scored a point over the whole season. You can use max(coalesce(col, 0)) to avoid that.
Cycled this to test db: restore backup, upgrade, query.
Quick interim ticket with UI clarifications: https://github.com/brianmahlstedt/supercontest/issues/79

Highlighting pick capability on the matchups tab.
Sorting second tier after datetime on matchups. Sorting team abbvs on picks tab.
LB shows percentage now.
Niners->Warriors in adspace.

Made the graph endpoint respect “don’t show this week’s results until complete” just like the leaderboard does for its colorization. Abstracted that logic out into an initializer for both views.
Added the INSERTs to the user-season association table in the migration. Everyone goes to 19, a list is crosschecked against 18.
Made a ticket to remove the src volume in the production app, and bundle all the static content with flask-assets: https://github.com/brianmahlstedt/supercontest/issues/81.
TNF game was delayed due to lightning. The nfl’s xml scoresheet reported back “Suspended” as the status. The app handled it just fine.
Changed manage.py’s shell context to inject the symbols for the model names explicitly, not just the models object. Now you can copy-paste exact queries from the app (db.session.query(User) instead of db.session.query(models.User)).
Abstracted all interactions with the db to a package called dbsession, with modules `queries` and `commits`. This will make the application side of migrations much easier to update.
You can incrementally build tables for further querying in sqlalchemy, like you would naturally do with nested SELECT and JOIN statements in SQL. This is super useful for layered relationships, like Pick -> Line -> Week -> Season. The trick is calling .subquery() at the end of your query. Then, you can query it (or join it) later. To access the labelled columns, you use c.

See supercontest.dbsession.queries for examples.
You can rename columns with .label(‘new_name’) if you want to change name, deduplicate, or do math and create a new col, etc.

I’m starting to agree that ORM usage can be way more complicated and abstract than SQL for complex queries.
All of this model and query redesign made the views module much slimmer. Almost all logic lives outside the routes, which is the way it should be.
To query on a relationship column, use .has().

Entry fee is $1500.
Winner last year was at 70.1%, 59.5 total out of 85 (17*5). He won $1,422, 214.20.
There were 3,123 contestants for a total of $4,309,740.

Generated a personal access token for octotree (it uses the free auth for the first X requests).
In sqlalchemy, use .one() if you expect one and only one result. Returns the object, not a list like .all(). If there might be multiple, use .first(). This won’t raise exceptions like .one() will.
In python dir(var) will give private and dunder attributes, whereas var.__dict__ will just give the main ones (better).