Skip to content

Instantly share code, notes, and snippets.

View danielfone's full-sized avatar

Daniel Fone danielfone

View GitHub Profile
@danielfone
danielfone / kml2geoguessr.rb
Last active January 11, 2022 23:01
Convert Stats NZ Urban/Rural KML to Geoguessr map regions JSON
# Get the file from https://datafinder.stats.govt.nz/layer/105158-urban-rural-2021-generalised/
# USAGE: ruby kml2geoguessr.rb urban-rural-2021-generalised.kml > rural-nz.geoguesser.json
require "nokogiri"
require "json"
TYPES = [
# "Major urban area",
# "Large urban area",
"Medium urban area",
"Small urban area",

In order to get Rails to reload when you change a sql file in development, you need to add the query directory to the watchable_dirs hash.

# watchable_dirs is keyed by path with an array of extensions as the value
config.watchable_dirs['db/queries'] = [:sql]

Rails automatically adds the autoload paths to watchable dirs with an extension of [:rb]. This includes all the directories in app/. This will override any matching paths you add to watchable_dirs.

reqTx = new Date();
await fetch("/now")
.then((response) => {
respRx = new Date();
return response.text();
})
.then((d) => {
rtt = respRx - reqTx;
expected = new Date(respRx - rtt / 2);
serverTime = new Date(d);
create table enrolments (
id serial PRIMARY KEY,
organisation_name text
)
-- Let's say we want to partition our table into pages of 50 rows
-- and sorted alphabetically.
with
dist as (
select
with recursive
-- extract all the messages as rows from the several `conversations.history`
-- API responses in the `slack_history` table
messages as (
select jsonb_array_elements(response->'messages') as message from markov.slack_history
),
-- remove @heres, @names, exclude bot messages, etc
cleaned_messages as (
#!/usr/bin/env ruby -w
require 'time'
require 'json'
#
# Helper methods
#
module Enumerable
def mean(method=:self)
@danielfone
danielfone / authenticate.sql
Created August 4, 2019 23:11
Timing-safe SQL authentication in postgresql
-- This is a constant time query for authenticating password-based credentials
-- The key is that the bcrypt work is done whether or not the user key (email)
-- matches so the query takes the same time whether or not there was a matching
-- record. In this example, the supplied credentials are:
-- `admin@example.com` / `password`
-- This was written for postgres with pgcrypto
with
-- This is an effective but naive approach to selecting an authenticated user from a users table.
-- The problem is that the query is (intentionally) slow if it has to check a digest,
-- but finishes very quickly if the email doesn't match, and so provides a mechanism for user enumeration.
select *
from users
where email = 'daniel@fone.net.nz'
and password_digest = crypt('password', password_digest);
-- The key goal is to provide a roughly constant time query whether the email or the password is wrong
with
-- Create table to hold raw imported data
--
DROP TABLE IF EXISTS imported_reports;
CREATE TABLE imported_reports (
reference varchar primary key,
project_reference varchar,
project_name varchar,
version varchar,
generation_time varchar,
-- etc
DROP TABLE imported_reports;
VACUUM FULL ANALYZE VERBOSE;