Query

pilot.db — offers

SQL

CREATE TABLE offers  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trainer_id INTEGER NOT NULL REFERENCES trainers(id),
  type TEXT NOT NULL DEFAULT 'event',
  -- 'reise' | 'equipment' | 'event'
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  price_cents INTEGER,
  -- optional (z. B. bei "Preis auf Anfrage")
  discount_text TEXT,
  -- z. B. "20 % Frühbucher bis 31.08."
  valid_until TEXT,
  -- ISO-Datum,
  optional
  external_url TEXT,
  -- optionaler Link (Reiseveranstalter etc.)
  media_ref TEXT,
  -- Bild-Dateiname im OFFER_MEDIA_DIR
  active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
trainer_id INTEGER Rename | Drop
type TEXT Rename | Drop
title TEXT Rename | Drop
description TEXT Rename | Drop
price_cents INTEGER ✓ Rename | Drop
discount_text TEXT ✓ Rename | Drop
valid_until TEXT ✓ Rename | Drop
external_url TEXT ✓ Rename | Drop
media_ref TEXT ✓ Rename | Drop
active INTEGER Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT Rename | Drop

Foreign Keys

Column Destination
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_offers_trainer
  • trainer_id
  • active
 SQL 
CREATE INDEX idx_offers_trainer
ON offers(trainer_id, active)
Drop