SQL

CREATE TABLE textbook_pages  (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  lesson_id   INTEGER NOT NULL REFERENCES textbook_lessons(id),
  page_number INTEGER NOT NULL,
  -- 教材页码
    image_path  TEXT,
  -- 扫描图本地路径
    image_url   TEXT,
  -- 公网URL
    page_type   TEXT DEFAULT 'content',
  -- content/vocab/exercise/cover
    created_at  DATETIME DEFAULT (datetime('now'))
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
lesson_id INTEGER Rename | Drop
page_number INTEGER Rename | Drop
image_path TEXT ✓ Rename | Drop
image_url TEXT ✓ Rename | Drop
page_type TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
lesson_id textbook_lessons.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_pages_lesson lesson_id SQL 
CREATE INDEX idx_pages_lesson
ON textbook_pages(lesson_id)
Drop