Query

pilot.db — campaigns

SQL

CREATE TABLE campaigns  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trainer_id INTEGER NOT NULL REFERENCES trainers(id),
  title TEXT NOT NULL,
  -- intern
  subject TEXT NOT NULL,
  -- Mail-Betreff
  intro_text TEXT,
  -- persönlicher Einstieg in der Mail
  status TEXT NOT NULL DEFAULT 'draft',
  -- 'draft' | 'sent'
  sent_at TEXT,
  recipients_count INTEGER,
  show_in_reminders INTEGER NOT NULL DEFAULT 0,
  -- Stufe 2: Block in Transaktionsmails
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  show_after_training INTEGER NOT NULL DEFAULT 0
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
trainer_id INTEGER Rename | Drop
title TEXT Rename | Drop
subject TEXT Rename | Drop
intro_text TEXT ✓ Rename | Drop
status TEXT Rename | Drop
sent_at TEXT ✓ Rename | Drop
recipients_count INTEGER ✓ Rename | Drop
show_in_reminders INTEGER Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT Rename | Drop
show_after_training INTEGER Rename | Drop

Foreign Keys

Column Destination
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_campaigns_trainer
  • trainer_id
  • status
 SQL 
CREATE INDEX idx_campaigns_trainer
ON campaigns(trainer_id, status)
Drop