owid-grapher :sparkles: add type and sort columns to variables table

Grapher part of adding indicator type to MySQL and adding ordinal type. Here's ETL pull request.

Mar 18 '24 08:03 Marigold

@danyx23 we've agreed on using dimensions.values.values for ordinal variable categories, but @sophiamersmann had a good question about what this should look like in the database. This PR currently uses fields type and sort (specifically for storing an array of categories). We have a couple of options:

Don't store sort field in MySQL at all and keep it only in JSON
Keep it as it is
Change type field to JSON and store it as {"type": "ordinal", "sort": ["a", "b", "c"]} - this is future-proof and flexible

What do you think?

Mar 19 '24 15:03 Marigold

There's another https://github.com/owid/owid-grapher/pull/2039 we had open back then. We should either merge them or simply kill the old one.

Mar 20 '24 10:03 Marigold

I copy-pasted the code that I needed from #2039 into this PR, so I'm happy for it to be killed

Mar 20 '24 14:03 sophiamersmann

I think the current approach is good @Marigold. We'll probably query the type field much more often than the sort field in the DB, so having the type field as a column makes sense to me.

Mar 20 '24 16:03 danyx23

Thanks! @sophiamersmann I removed sort field from JSON and kept only dimensions.values.values there. Leaving this PR in your hands now. Once you merge it, I can merge the ETL part.

Mar 21 '24 08:03 Marigold

Thanks for the review!

I pushed one more commit that updates Grapher's db types and asked Daniel to sign off on it. This is good to merge now, but I'll wait for the holidays to be over :)

Mar 28 '24 12:03 sophiamersmann