Query

pilot.db — drills_library

SQL

CREATE TABLE drills_library  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trainer_id INTEGER NOT NULL REFERENCES trainers(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT,
  difficulty TEXT,
  goal TEXT,
  keywords TEXT,
  times_used INTEGER NOT NULL DEFAULT 0,
  last_used_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  media_type TEXT,
  media_ref TEXT,
  sketch_svg TEXT
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
trainer_id INTEGER Rename | Drop
title TEXT Rename | Drop
description TEXT Rename | Drop
category TEXT ✓ Rename | Drop
difficulty TEXT ✓ Rename | Drop
goal TEXT ✓ Rename | Drop
keywords TEXT ✓ Rename | Drop
times_used INTEGER Rename | Drop
last_used_at TEXT ✓ Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT Rename | Drop
media_type TEXT ✓ Rename | Drop
media_ref TEXT ✓ Rename | Drop
sketch_svg TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_drills_library_category
  • trainer_id
  • category
 SQL 
CREATE INDEX idx_drills_library_category
ON drills_library(trainer_id, category)
Drop
idx_drills_library_trainer trainer_id SQL 
CREATE INDEX idx_drills_library_trainer
ON drills_library(trainer_id)
Drop
idx_drills_library_usage
  • trainer_id
  • times_used
  • last_used_at
 SQL 
CREATE INDEX idx_drills_library_usage
ON drills_library(trainer_id, times_used DESC, last_used_at DESC)
Drop