Been a couple of days… apparently more like 193… well, not to be deterred I’m going to try this again.
Tonight I am hacking on a CTFtime stats application. CTFtime is “All about CTF (Capture the Flag)”. It is the defacto scoreboard aggregator for all the wonderful CTFs that occur each week. They actually have been doing it for some time (since 2011) so there is a lot of really great data there.
Since they provide an API I’d like to pull down this data and make a simple app to visualize it and draw some interesting conclusions. For example:
- What CTFs did the top teams compete in?
- What was team X’s rank over time?
- Compare the qualifier rank vs finals rank for team X in CTF A?
Nothing too crazy, but could provide interesting insights, in the past a collection of one off spreadsheet has served this purpose but this is a perfect excuse to get better at building real things.
One specific query I find myself often wanting to run is something like the following:
INSERT OR REPLACE into table (id, name) VALUES (?, ?);
This seems to come up all the time when I want to store data locally that I have fetched from some canonical source (for example an API). This way I can always fetch data from the remote source, and have it override my local data without worrying if I’ve already inserted it. Is there a cleaner way to do this pattern?
Perhaps this is something gorm supports (1188, 1346), but again I’m
back to fighting with a dependency. This long story is all to say I am going to
try a different approach, namely sqlx which is just an extension of the
database/sql. Back to the code.
Thanks for sticking around.
Quick oneliners I looked up today but have also looked up before
sqlite column names (courtesy of stack overflow):
vim find and replace from cursor (courtesy of stack overflow again):