DEV Community: Johnß

UPDATE … FROM for database normalisation

Johnß — Fri, 04 Dec 2020 11:22:14 +0000

Normalisation?

Normalisation is the process of reducing the “copies” of data in a database, for example, in a fixtures table having a foreign-key to a sport table rather than repeating the value Football in each of the fixtures.

fixtures

id	sport	home_team	away_team
1	Football	Liverpool	Everton
2	Football	Newcastle	Chelsea
3	Football	Sunderland	Arsenal

Might become:

sports

id	sport
1	Football

fixtures

id	sport_id	home_team	away_team
1	1	Liverpool	Everton
2	1	Newcastle	Chelsea
3	1	Sunderland	Arsenal

home_team and away_team should probably also be foreign-keys to a teams table.

Migration queries

Create the new sports table:

CREATE TABLE sports (
  id SERIAL PRIMARY KEY NOT NULL,
  Name VARCHAR(100) NOT NULL
);

Insert all of the sports:

INSERT INTO sports (name)
  SELECT DISTINCT sport FROM fixtures;

Add the sport_id foreign-key but don’t validate as there won’t be any values yet:

ALTER TABLE fixtures 
  ADD COLUMN sport_id INT;
ALTER TABLE fixtures
  ADD CONSTRAINT fixtures_sport_id
    FOREIGN KEY (sport_id)
    REFERENCES sports (id) NOT VALID;

Update all of the fixtures with the correct value for sport_id:

UPDATE fixtures SET sport_id = sports.id
FROM sports
WHERE fixtures.sport = sports.name;

Validate the foreign-key constraint:

ALTER TABLE fixtures
  VALIDATE CONSTRAINT fixtures_sport_id;

SQL CROSS JOINs

Johnß — Thu, 26 Nov 2020 17:50:32 +0000

I found a use for CROSS JOIN in SQL recently when generating content for a pivot table.

Given a fixtures table:

CREATE SEQUENCE IF NOT EXISTS fixtures_id_seq;

CREATE TABLE "public"."fixtures" (
  "id" int4 NOT NULL DEFAULT nextval('fixtures_id_seq'::regclass),
  "start_time" TIMESTAMPTZ,
  "sport" VARCHAR(40),
  PRIMARY KEY ("id")
);

The SQL is something similar to the below:

WITH sports AS (
  SELECT
    DISTINCT sport
  FROM
    fixtures
  ORDER BY
    sport
),
days AS (
  SELECT
    day
  FROM
    generate_series(
      date_trunc('day', '2020-09-09'::date)::date,
      date_trunc('day', '2020-09-21'::date)::date,
      '1 day'::interval
    ) AS day
),
fixtures_per_day AS (
  SELECT
    date_trunc('day', start_time) AS day,
    sport,
    COUNT(*) AS c
  FROM
    fixtures
  GROUP BY
    date_trunc('day', start_time),
    sport
)
SELECT
  days.day,
  sports.sport,
  COALESCE(fixtures_per_day.c, 0) AS fixture_count
FROM
  days
  CROSS JOIN sports
  INNER JOIN fixtures_per_day ON (
    days.day = fixtures_per_day.day
    AND sports.sport = fixtures_per_day.sport
  )

There are three CTEs (Common Table Expressions) which are like sub-queries but can be re-used. I’ve used them here to keep the query tidy.

The first “table”, sports just gathers us a list of each of the sports in a fixtures table. Next, days generates a sequence of dates from 9/Sep/2020 to 21/Sep/2020. Finally fixtures_per_day create a table with the count of events on any given day for any given sport.

Our query then CROSS JOINs the days and sports “tables” to create a cartesian product, a maths-y name for the result of the following Python code:

result = []

for day in days:
    for sport in sports:
        result.append((day, sport))

Once we have the CROSS JOIN we can LEFT JOIN on the fixtures_per_day to get something which would make sense in a pivot table or chart without having to fill in blanks by hand. We use the COALESCE function to make sure that any NULL rows become 0.

The results for some test data look like the following:

day	sport	fixture_count
2020-09-12 00:00:00+01	EPL	3
2020-09-12 00:00:00+01	NFL	0
2020-09-12 00:00:00+01	SPL	6
2020-09-13 00:00:00+01	EPL	2
2020-09-13 00:00:00+01	NFL	3
2020-09-13 00:00:00+01	SPL	0
2020-09-14 00:00:00+01	EPL	2
2020-09-14 00:00:00+01	NFL	1
2020-09-14 00:00:00+01	SPL	0
2020-09-15 00:00:00+01	EPL	0
2020-09-15 00:00:00+01	NFL	0
2020-09-15 00:00:00+01	SPL	0

Getting started with TypeScript & React

Johnß — Wed, 25 Nov 2020 13:16:26 +0000

A quick and dirty guide for myself to get started with TypeScript. Based on the notes from Simon Willison.

Install

With npm installed, install TypeScript into the dev-dependencies with:

$ npm install --save-dev typescript

--save-dev is the same as -D
npm is Node Package Manager

TypeScript config

Create a new TypeScript config with:"

$ npx tsc --init

npx is Node Package e*X*ecutor, and part of npm: it allows you to run Node “scripts” from a project rather than having to install them globally or to the PATH

Make a TypeScript file, greeting.ts:

const greeting = (person: string): void => {
  console.log(`Hello ${person}!`);
};
greeting("John");

string is the type of person
void is a special type to denote that this function doesn't return anything

Compile it with tsc, the Type*Script **C*ompiler:

$ npx tsc

And then run it:

$ node greeting.js
Hello John!

TypeScript & React

Install create-react-app and create a new React app with TypeScript support with:

$ npx create-react-app my-app --template typescript

npx here is install create-react-app before executing it

A simple TSX component

A straight forward React component, Greeting.tsx showing TypeScript with JSX.

import React from "react";
import { useState } from "react";

function Greeting(props: { name: string }) {
  const [value, setValue] = useState(props.name);

  const onChange = (event: React.ChangeEvent<HTMLInputElement>): void => {
    setValue(event.target.value);
  };

  return (
    <>
      <input value={value} onChange={onChange} />
      <h1>Hello {value}!</h1>
    </>
  );
}

export default Greeting;

Add it to the App.tsx:

import React from "react";

import Greeting from "./components/Greeting";

function App() {
  return (
    <div>
      <Greeting name="John" />
    </div>
  );
}

export default App;

Start the Node server:

npm start