danielfone

-- 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

danielfone

-- 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

danielfone

DROP TABLE imported_reports;
VACUUM FULL ANALYZE VERBOSE;

danielfone

INSERT INTO reports (reference, program_id, generation_time, version)
  SELECT
    i.reference
    programs.id AS program_id,
    nullif(i.generation_time, '')::timestamp AS generation_time,
    nullif(i.version, '')::numeric AS version,
    -- etc
  FROM imported_reports i
  JOIN programs ON programs.reference = i.program_reference
ON CONFLICT (external_id) DO UPDATE SET

danielfone

-- Load projects from import
--
INSERT INTO projects (reference, name, created_at, updated_at)
  SELECT
    i.project_reference AS reference,
    i.project_name      AS name
    now()               AS created_at,
    now()               AS updated_at
  FROM (SELECT DISTINCT project_reference, project_name FROM imported_reports) i
ON CONFLICT (reference) DO UPDATE SET

danielfone

-- Import data
--
COPY imported_reports
FROM '/tmp/large-enterprise-export.csv'
WITH (format csv, header)
;

danielfone

-- 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

danielfone

-- Queries a table like this:
--
-- id	    settings	                  updated_at
-- 10013  {"reports.url_expiry":400}  2018-04-30 00:00:00
-- 10014  {"setting2":"foo"}          2018-05-01 00:00:00
-- 10015  {"reports.url_expiry":200}  2018-05-02 00:00:00
--
-- to roll up the settings column into:
--
-- { "reports.url_expiry" : 200, "setting2" : "foo" }

danielfone

Declarative APIs: State of Play

JSON Schema

• http://json-schema.org/
• Describes/defines a JSON document
• Very popular and well-tooled, lots of validators in many different languages.
• Great for describing/defining/validating JSON responses, request payloads, etc.
• JSON schema is JSON, so human-readability is mediocre.
• Spec is obtuse.
• Tutorial: https://spacetelescope.github.io/understanding-json-schema/

danielfone

MyExceptions = [RangeError, RegexpError, IOError].freeze

begin
  raise IOError, "should be rescued"
rescue *MyExceptions => e
  puts e
end