2019-09-11 – Mostly Software

Uber laid off about 8% of its workforce.
Tax-gain harvesting. Just balancing tax brackets for the year. If your income or investments are lower than usual at some point, then you can sell some of your better stocks (and usually buy them back immediately). Do this to maximize your current income tax bracket (10-12-22-24-32-35-37) and cap gains tax bracket (0-15-20). This allows you to pay a portion of the tax at a time that is cheapest for you.

The opposite is tax-loss harvesting. If you have stocks that have suffered, and your year had particularly high capital gains, then you can sell them at a loss to offset. Then you buy them back immediately, effectively shifting your basis in that holding to a lower amount.

Did a little fantasy research to set lineups for this week. Forgot how annoying it was lol. Watching the games is more fun without that stress.
Bought and smoked a whole octopus.

$13/lb, 4.7lbs. This was from redondo pier – I’ll try an asian market next time, probably a little cheaper.
Frozen is fine, unlike most poultry/game/cattle. They’ll usually have the beak/eyes/guts removed, but do so if the market didn’t.
Let it sit out at room temp for about an hour, with a fan, before cooking. This creates a pellicle – a dried outer layer which smoke can cling to much better than the natural underwater skin.
Pepper dry rub with maple syrup binder. Mesquite chunks.
Take to ~140. About 2hrs with the smoker @150.
Octopus is super low in fats and carbs. Super high in protein and cholesterol.
They’re in the cephalapod class, but above that they’re in the mollusk phylum!

Today was almost all SQL.
You can query distinct on multiple columns, and it will return rows with all the permutations.
Remember kids, don’t hardcode static values into your migrations. If it’s based on the current data (moving rows/cols, checking max, etc), but that update infer from the existing data. Also: write and test your downgrades!!
Foreign keys need definition in the database, and therefore need to be in the alembic migrations. Relationships, however, do not! They’re only on the sqlalchemy side, the portion that decides how to write to the db. But the db structure itself, the sql side, does not have knowledge of a “relationship”. You still need to define the association tables, foreign keys, etc in the migrations.

Along the same lines – whether or not the many-many relationship here is unidirectional or bidirectional is purely in the python side as well. The raw db has an association table. That’s it. The ORM can choose to reference in both directions on both original tables, or just one.

Remember, SELECT statements (and subqueries) just return rows. That’s why you can pass them to an INSERT statement or likewise. That’s also why you can nest them.
Example join for common supercontest use case:

select email, team from users inner join picks on users.id = picks.user_id where season = 2019;
You can be specific about what you want back if the tables share column names:

select users.id, picks.points from users inner join picks on users.id = picks.user_id;

Outer join does an inner join first, then checks a condition on one of the tables. Left checks the condition for the first table in the join. Right checks the condition on the second A full outer join does both.
Cross join does permutations, returning a table with all the columns from both tables in the join.
Remember, can’t add a non-nullable col to existing rows. Needs a default, or add it regular and execute whatever inserts you want, then add the nullable=False constraint last.
Listing all fk constraints for a table is a long query: https://stackoverflow.com/a/1152321.
If you do `FROM table1, table2` postgres does an implicit CROSS JOIN, for all permutations. You usually don’t want this naked, but often the query comes in the form `FROM table1, table2 WHERE table1.col1 = table2.col2`. This implicitly makes it an INNER JOIN. It’s the same syntax as `FROM table1 INNER JOIN table2 on col1 = col2`.
You can join more than two, as well. Example syntax: `FROM table1, table2, table3`.
For postgresql and alembic, you have to manually create the sequence in the migration before creating the table:

INSERT INTO requires parentheses around the columns that follow. SELECT should not have parentheses around the multiple values. If you put parentheses, it tries to combine the contents into a compound value.

Small bug in the prod app. sorted_user_ids wasn’t defined in the picks view when it’s in “only-me” mode wed-sat. It’s just your id, one line change.
Added the week 2 lines. Backed up before and after.
Verified that “last week” colorizes on the leaderboard now. Verified all the new picking UI functionalities work. The light blue is a great change. The /picks tab shows yours on wed-sat also (only yours).
Made my picks for this week.
Overall db change in hierarchy, following foreign keys and relationships:

Manually created the extensive migration to the new table structure. Some changes were easier: table creation, rename, not nullable, fk, etc. Some changes were harder: conditional updates, moving cols, etc. Practiced a lot of SQL.
The final migration (just upgrade) was over 100 lines. Not great.
While you should write downgrades whenever possible, some migrations are not reversible. This is one of them. Data was lost (Pick.points, Matchup.winner, etc). It’s not worth recalculating those and programmatically allowing a downgrade. You have backups from before then.
Got the db to upgrade. Now going to test with sql (manually) and update the app to use the new structure.
Rebased on the master prod changes.