There's a really good list of lessons in the second half of this page which I think are really interesting (in general, but also in specific for this project). I don't have a Lobste.rs account but I have some comments about some of them so I'll add them here
* If you want to fail on full table scans in tests you could take advantage of EXPLAIN - though, because it relies on the built in analysis/stats this may exactly mimic production tables. I believe Ruby is dynamic enough that you could probably attach an EXPLAIN to all queries in the unit tests [0] and parse the output.
* For the three UDFs - Sqlite has a regexp module that you can build and load rather than using a udf in rails (probably a different version of regexp though, so user beware) [1] there is also an extensions_functions.c in the external contributions that provides 'stddev' [2] (or you could fake it with the percentile module). I'm confused why there's a udf for 'if' but it'd be trivial to build as C module, but I agree it's best to patch over this sort of thing separately from the migration, so perhaps limited benefit.
* 'I'm constantly surprised by the default choices of SQLite.' I think this is fair, but they're quite dedicated in not breaking existing installations, they even have pages dedicated to explaining some peculiar architecture [3]
Really cool, the page is noticeably snappier. I'm wondering why they don't put the database on a network-attached block volume. DigitalOcean offers Volumes Block Storage, which is replicated across multiple hosts. Keeping SQLite there would allow the volume to be attached to a replacement Droplet rather than restoring from the latest nightly backup.
There's a really good list of lessons in the second half of this page which I think are really interesting (in general, but also in specific for this project). I don't have a Lobste.rs account but I have some comments about some of them so I'll add them here
* If you want to fail on full table scans in tests you could take advantage of EXPLAIN - though, because it relies on the built in analysis/stats this may exactly mimic production tables. I believe Ruby is dynamic enough that you could probably attach an EXPLAIN to all queries in the unit tests [0] and parse the output.
* For the three UDFs - Sqlite has a regexp module that you can build and load rather than using a udf in rails (probably a different version of regexp though, so user beware) [1] there is also an extensions_functions.c in the external contributions that provides 'stddev' [2] (or you could fake it with the percentile module). I'm confused why there's a udf for 'if' but it'd be trivial to build as C module, but I agree it's best to patch over this sort of thing separately from the migration, so perhaps limited benefit.
* 'I'm constantly surprised by the default choices of SQLite.' I think this is fair, but they're quite dedicated in not breaking existing installations, they even have pages dedicated to explaining some peculiar architecture [3]
[0] https://www.sqlite.org/eqp.html EDIT: there's an alternative suggestion here - https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlit...
[1] https://sqlite.org/src/file/ext/misc/regexp.c
[2] https://sqlite.org/src/ext/contrib/ (ctrl-f for 'extensions-functions.c'. It has more than just stddev but you could strip the rest)
[3] https://www.sqlite.org/rowidtable.html I find this a really touching note 'The designer of SQLite offers his sincere apology for the current mess'
Really cool, the page is noticeably snappier. I'm wondering why they don't put the database on a network-attached block volume. DigitalOcean offers Volumes Block Storage, which is replicated across multiple hosts. Keeping SQLite there would allow the volume to be attached to a replacement Droplet rather than restoring from the latest nightly backup.
https://sqlite.org/useovernet.html