1 (* The SQL schema migrations, as data. This module holds no logic. It holds only 2 the ordered list of migrations and the ledger table that records which ones a 3 database file already carries. {!Migrations} reads this and applies it. 4 5 Keeping the SQL here, apart from the apply logic, means a schema change edits 6 one place and never touches OCaml control flow. 7 8 Seven incremental migrations first grew this schema. A reset of the deployed 9 database consolidated them into a single initial migration. No file was left 10 to migrate, so the drop-and-recreate churn of the old chain served no 11 purpose. Add the next schema change as version 2. *) 12 13 type t = { version : int; name : string; statements : string list } 14 (** One migration: a version, a human name, and the ordered statements it runs. 15 *) 16 17 (* The ledger of applied migrations. A row per version, so a reconnect knows 18 what the file already carries and skips it. *) 19 let ledger = 20 {|CREATE TABLE IF NOT EXISTS schema_migrations ( 21 version INTEGER PRIMARY KEY, 22 name TEXT NOT NULL, 23 applied_at REAL NOT NULL 24 )|} 25 26 let migrations = 27 [ 28 { 29 version = 1; 30 name = "initial schema"; 31 statements = 32 [ 33 {|CREATE TABLE IF NOT EXISTS trainee ( 34 id TEXT PRIMARY KEY, 35 username TEXT NOT NULL UNIQUE, 36 credential TEXT NOT NULL 37 )|}; 38 {|CREATE TABLE IF NOT EXISTS active_routine ( 39 trainee_id TEXT PRIMARY KEY, 40 routine_id TEXT NOT NULL 41 )|}; 42 {|CREATE TABLE IF NOT EXISTS in_progress ( 43 trainee_id TEXT PRIMARY KEY, 44 encoded TEXT NOT NULL 45 )|}; 46 {|CREATE TABLE IF NOT EXISTS workout ( 47 id TEXT PRIMARY KEY, 48 trainee_id TEXT NOT NULL, 49 seq INTEGER NOT NULL, 50 encoded TEXT NOT NULL 51 )|}; 52 {|CREATE INDEX IF NOT EXISTS workout_by_trainee 53 ON workout (trainee_id, seq DESC)|}; 54 (* Retained from when Dream's sql_sessions back end stored sessions 55 here. Sessions now live in a signed cookie. Kept so an existing 56 file keeps its rows; otherwise unused. *) 57 {|CREATE TABLE IF NOT EXISTS dream_session ( 58 id TEXT PRIMARY KEY, 59 label TEXT NOT NULL, 60 expires_at REAL NOT NULL, 61 payload TEXT NOT NULL 62 )|}; 63 {|CREATE TABLE IF NOT EXISTS feedback ( 64 trainee_id TEXT NOT NULL, 65 seq INTEGER NOT NULL, 66 encoded TEXT NOT NULL, 67 PRIMARY KEY (trainee_id, seq) 68 )|}; 69 {|CREATE INDEX IF NOT EXISTS feedback_by_trainee 70 ON feedback (trainee_id, seq DESC)|}; 71 {|CREATE TABLE IF NOT EXISTS app_feedback ( 72 trainee_id TEXT NOT NULL, 73 seq INTEGER NOT NULL, 74 submitted_at TEXT NOT NULL, 75 message TEXT NOT NULL, 76 PRIMARY KEY (trainee_id, seq) 77 )|}; 78 {|CREATE INDEX IF NOT EXISTS app_feedback_by_trainee 79 ON app_feedback (trainee_id, seq DESC)|}; 80 {|CREATE TABLE IF NOT EXISTS app_feedback_vote ( 81 feedback_trainee_id TEXT NOT NULL, 82 feedback_seq INTEGER NOT NULL, 83 voter_id TEXT NOT NULL, 84 PRIMARY KEY (feedback_trainee_id, feedback_seq, voter_id) 85 )|}; 86 {|CREATE INDEX IF NOT EXISTS app_feedback_vote_by_voter 87 ON app_feedback_vote (voter_id)|}; 88 (* An import batch is provisional: it holds the raw export source and 89 the cleaning done so far, encoded as one snapshot blob. A promoted 90 record is canonical evidence with an independent lifecycle, so it 91 carries no foreign key to its batch. Deleting the batch must never 92 cascade to the record. The batch's [imported_at] is a column, so 93 listing can order without decoding every blob. A record is keyed by 94 (trainee, workout) so a re-promotion replaces it. *) 95 {|CREATE TABLE IF NOT EXISTS import_batch ( 96 trainee_id TEXT NOT NULL, 97 batch_id TEXT NOT NULL, 98 imported_at TEXT NOT NULL, 99 source TEXT NOT NULL, 100 fingerprint TEXT NOT NULL, 101 snapshot TEXT NOT NULL, 102 PRIMARY KEY (trainee_id, batch_id) 103 )|}; 104 {|CREATE INDEX IF NOT EXISTS import_batch_by_trainee 105 ON import_batch (trainee_id, imported_at DESC, batch_id DESC)|}; 106 {|CREATE TABLE IF NOT EXISTS imported_record ( 107 trainee_id TEXT NOT NULL, 108 workout_id TEXT NOT NULL, 109 batch_id TEXT NOT NULL, 110 revision INTEGER NOT NULL, 111 title TEXT NOT NULL, 112 started_at TEXT NOT NULL, 113 ended_at TEXT NOT NULL, 114 snapshot TEXT NOT NULL, 115 PRIMARY KEY (trainee_id, workout_id) 116 )|}; 117 {|CREATE INDEX IF NOT EXISTS imported_record_by_trainee 118 ON imported_record (trainee_id, ended_at DESC)|}; 119 ]; 120 }; 121 ] 122