| 1 | //! Database connection and schema setup via Toasty. |
| 2 | |
| 3 | use std::path::Path; |
| 4 | |
| 5 | use crate::{ |
| 6 | error::Result, |
| 7 | models::{ |
| 8 | AdminCache, |
| 9 | AgentSession, |
| 10 | ApiToken, |
| 11 | Attachment, |
| 12 | CiArtifact, |
| 13 | CiRun, |
| 14 | Issue, |
| 15 | IssueComment, |
| 16 | RepoSecret, |
| 17 | Repository, |
| 18 | Session, |
| 19 | SshKey, |
| 20 | User, |
| 21 | UserSecret, |
| 22 | }, |
| 23 | }; |
| 24 | |
| 25 | /// Open (creating if necessary) the SQLite database at `path`, creating the |
| 26 | /// schema on first run. |
| 27 | /// |
| 28 | /// Note: Toasty's `push_schema` emits plain `CREATE TABLE` (no |
| 29 | /// `IF NOT EXISTS`) for model tables, so it is *not* idempotent — calling it on |
| 30 | /// an existing database errors (still true as of 0.10). We therefore only push |
| 31 | /// the schema when the database file does not yet exist. Skipping it on an |
| 32 | /// existing database issues no DDL, so data is never touched. Schema evolution |
| 33 | /// will move to Toasty's migration system when models start changing. |
| 34 | pub async fn connect(path: impl AsRef<Path>) -> Result<toasty::Db> { |
| 35 | let path = path.as_ref(); |
| 36 | let fresh = !path.exists(); |
| 37 | let url = format!("sqlite:{}", path.display()); |
| 38 | |
| 39 | let db = toasty::Db::builder() |
| 40 | .models(toasty::models!( |
| 41 | User, |
| 42 | Repository, |
| 43 | SshKey, |
| 44 | Session, |
| 45 | CiRun, |
| 46 | CiArtifact, |
| 47 | Issue, |
| 48 | IssueComment, |
| 49 | Attachment, |
| 50 | ApiToken, |
| 51 | AdminCache, |
| 52 | RepoSecret, |
| 53 | AgentSession, |
| 54 | UserSecret |
| 55 | )) |
| 56 | .connect(&url) |
| 57 | .await?; |
| 58 | |
| 59 | if fresh { |
| 60 | db.push_schema().await?; |
| 61 | } else { |
| 62 | migrate_existing(path)?; |
| 63 | } |
| 64 | Ok(db) |
| 65 | } |
| 66 | |
| 67 | /// Tables added after a deployment's database was first created, as idempotent |
| 68 | /// DDL applied to existing databases (Toasty's `push_schema` only runs on a |
| 69 | /// fresh file; see above). Each statement must match what `push_schema` would |
| 70 | /// generate for the model — `schema_shim_matches_push_schema` asserts that. |
| 71 | const SCHEMA_SHIMS: &[&str] = &[ |
| 72 | CI_ARTIFACTS_DDL, |
| 73 | r#"CREATE INDEX IF NOT EXISTS "index_ci_artifacts_by_repo_id" ON "ci_artifacts" ("repo_id")"#, |
| 74 | r#"CREATE INDEX IF NOT EXISTS "index_ci_artifacts_by_run_id" ON "ci_artifacts" ("run_id")"#, |
| 75 | ISSUES_DDL, |
| 76 | r#"CREATE INDEX IF NOT EXISTS "index_issues_by_repo_id" ON "issues" ("repo_id")"#, |
| 77 | ISSUE_COMMENTS_DDL, |
| 78 | r#"CREATE INDEX IF NOT EXISTS "index_issue_comments_by_issue_id" ON "issue_comments" ("issue_id")"#, |
| 79 | ATTACHMENTS_DDL, |
| 80 | r#"CREATE INDEX IF NOT EXISTS "index_attachments_by_repo_id" ON "attachments" ("repo_id")"#, |
| 81 | API_TOKENS_DDL, |
| 82 | r#"CREATE INDEX IF NOT EXISTS "index_api_tokens_by_user_id" ON "api_tokens" ("user_id")"#, |
| 83 | r#"CREATE UNIQUE INDEX IF NOT EXISTS "index_api_tokens_by_token_hash" ON "api_tokens" ("token_hash")"#, |
| 84 | ADMIN_CACHE_DDL, |
| 85 | REPO_SECRETS_DDL, |
| 86 | r#"CREATE INDEX IF NOT EXISTS "index_repo_secrets_by_repo_id" ON "repo_secrets" ("repo_id")"#, |
| 87 | AGENT_SESSIONS_DDL, |
| 88 | r#"CREATE INDEX IF NOT EXISTS "index_agent_sessions_by_repo_id" ON "agent_sessions" ("repo_id")"#, |
| 89 | r#"CREATE INDEX IF NOT EXISTS "index_agent_sessions_by_status" ON "agent_sessions" ("status")"#, |
| 90 | USER_SECRETS_DDL, |
| 91 | r#"CREATE INDEX IF NOT EXISTS "index_user_secrets_by_user_id" ON "user_secrets" ("user_id")"#, |
| 92 | ]; |
| 93 | |
| 94 | const CI_ARTIFACTS_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "ci_artifacts" ( |
| 95 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 96 | "run_id" BIGINT NOT NULL, |
| 97 | "repo_id" BIGINT NOT NULL, |
| 98 | "commit" TEXT NOT NULL, |
| 99 | "name" TEXT NOT NULL, |
| 100 | "size" BIGINT NOT NULL, |
| 101 | "is_dir" BOOLEAN NOT NULL, |
| 102 | "browse" BOOLEAN NOT NULL, |
| 103 | "meta" TEXT NOT NULL, |
| 104 | "created_at" BIGINT NOT NULL )"#; |
| 105 | |
| 106 | const ISSUES_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "issues" ( |
| 107 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 108 | "repo_id" BIGINT NOT NULL, |
| 109 | "number" BIGINT NOT NULL, |
| 110 | "title" TEXT NOT NULL, |
| 111 | "body" TEXT NOT NULL, |
| 112 | "author_id" BIGINT NOT NULL, |
| 113 | "state" TEXT NOT NULL, |
| 114 | "created_at" BIGINT NOT NULL, |
| 115 | "updated_at" BIGINT NOT NULL )"#; |
| 116 | |
| 117 | const ISSUE_COMMENTS_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "issue_comments" ( |
| 118 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 119 | "issue_id" BIGINT NOT NULL, |
| 120 | "author_id" BIGINT NOT NULL, |
| 121 | "body" TEXT NOT NULL, |
| 122 | "created_at" BIGINT NOT NULL )"#; |
| 123 | |
| 124 | const ATTACHMENTS_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "attachments" ( |
| 125 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 126 | "repo_id" BIGINT NOT NULL, |
| 127 | "hash" TEXT NOT NULL, |
| 128 | "content_type" TEXT NOT NULL, |
| 129 | "size" BIGINT NOT NULL, |
| 130 | "uploader_id" BIGINT NOT NULL, |
| 131 | "created_at" BIGINT NOT NULL )"#; |
| 132 | |
| 133 | const API_TOKENS_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "api_tokens" ( |
| 134 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 135 | "user_id" BIGINT NOT NULL, |
| 136 | "name" TEXT NOT NULL, |
| 137 | "token_hash" TEXT NOT NULL, |
| 138 | "scopes" TEXT NOT NULL, |
| 139 | "created_at" BIGINT NOT NULL )"#; |
| 140 | |
| 141 | const REPO_SECRETS_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "repo_secrets" ( |
| 142 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 143 | "repo_id" BIGINT NOT NULL, |
| 144 | "name" TEXT NOT NULL, |
| 145 | "envelope" TEXT NOT NULL, |
| 146 | "recipients" TEXT NOT NULL, |
| 147 | "created_at" BIGINT NOT NULL, |
| 148 | "updated_at" BIGINT NOT NULL )"#; |
| 149 | |
| 150 | // `admin_caches`, plural, is what Toasty names the `AdminCache` model's table. |
| 151 | // This shim said `admin_cache` for its whole life, so every database created |
| 152 | // before the model existed was missing the table the code actually queries, and |
| 153 | // each disk-usage refresh panicked its worker. `SHIMMED_TABLES` now covers it. |
| 154 | const ADMIN_CACHE_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "admin_caches" ( |
| 155 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 156 | "key" TEXT NOT NULL, |
| 157 | "value" TEXT NOT NULL, |
| 158 | "computed_at" BIGINT NOT NULL )"#; |
| 159 | |
| 160 | const AGENT_SESSIONS_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "agent_sessions" ( |
| 161 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 162 | "repo_id" BIGINT NOT NULL, |
| 163 | "user_id" BIGINT NOT NULL, |
| 164 | "status" TEXT NOT NULL, |
| 165 | "kind" TEXT NOT NULL, |
| 166 | "base_ref" TEXT NOT NULL, |
| 167 | "base_commit" TEXT NOT NULL, |
| 168 | "branch" TEXT NOT NULL, |
| 169 | "prompt" TEXT NOT NULL, |
| 170 | "container_id" TEXT NOT NULL, |
| 171 | "image" TEXT NOT NULL, |
| 172 | "created_at" BIGINT NOT NULL, |
| 173 | "started_at" BIGINT NOT NULL, |
| 174 | "finished_at" BIGINT NOT NULL, |
| 175 | "last_attach_at" BIGINT NOT NULL, |
| 176 | "exit_code" BIGINT NOT NULL, |
| 177 | "error" TEXT NOT NULL, |
| 178 | "secret_names" TEXT NOT NULL )"#; |
| 179 | |
| 180 | const USER_SECRETS_DDL: &str = r#"CREATE TABLE IF NOT EXISTS "user_secrets" ( |
| 181 | "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, |
| 182 | "user_id" BIGINT NOT NULL, |
| 183 | "name" TEXT NOT NULL, |
| 184 | "kind" TEXT NOT NULL, |
| 185 | "dest_path" TEXT NOT NULL, |
| 186 | "field" TEXT NOT NULL, |
| 187 | "envelope" TEXT NOT NULL, |
| 188 | "recipients" TEXT NOT NULL, |
| 189 | "created_at" BIGINT NOT NULL, |
| 190 | "updated_at" BIGINT NOT NULL )"#; |
| 191 | |
| 192 | /// Columns added to existing tables after deployment, applied as |
| 193 | /// `ALTER TABLE … ADD COLUMN` when missing (SQLite has no `IF NOT EXISTS` |
| 194 | /// for columns, so presence is checked via `pragma_table_info`). The model |
| 195 | /// must declare the field *last* so fresh and migrated column orders agree. |
| 196 | /// The `DEFAULT` backfills existing rows; Toasty's fresh DDL omits it, which |
| 197 | /// is fine — inserts always provide every field. |
| 198 | const COLUMN_SHIMS: &[(&str, &str, &str)] = &[ |
| 199 | ( |
| 200 | "repositories", |
| 201 | "mirror_url", |
| 202 | r#"ALTER TABLE "repositories" ADD COLUMN "mirror_url" TEXT NOT NULL DEFAULT ''"#, |
| 203 | ), |
| 204 | ( |
| 205 | "repositories", |
| 206 | "preview_image_hash", |
| 207 | r#"ALTER TABLE "repositories" ADD COLUMN "preview_image_hash" TEXT NOT NULL DEFAULT ''"#, |
| 208 | ), |
| 209 | ( |
| 210 | "repositories", |
| 211 | "primary_language", |
| 212 | r#"ALTER TABLE "repositories" ADD COLUMN "primary_language" TEXT NOT NULL DEFAULT ''"#, |
| 213 | ), |
| 214 | ( |
| 215 | "repositories", |
| 216 | "languages_json", |
| 217 | r#"ALTER TABLE "repositories" ADD COLUMN "languages_json" TEXT NOT NULL DEFAULT ''"#, |
| 218 | ), |
| 219 | ( |
| 220 | "users", |
| 221 | "sso_sub", |
| 222 | r#"ALTER TABLE "users" ADD COLUMN "sso_sub" TEXT NOT NULL DEFAULT ''"#, |
| 223 | ), |
| 224 | ( |
| 225 | "agent_sessions", |
| 226 | "secret_names", |
| 227 | r#"ALTER TABLE "agent_sessions" ADD COLUMN "secret_names" TEXT NOT NULL DEFAULT ''"#, |
| 228 | ), |
| 229 | ]; |
| 230 | |
| 231 | /// Apply [`SCHEMA_SHIMS`] and [`COLUMN_SHIMS`] to an existing database. Uses |
| 232 | /// rusqlite directly (already in-tree via Toasty's SQLite driver); every |
| 233 | /// statement is a no-op once applied. |
| 234 | fn migrate_existing(path: &Path) -> Result<()> { |
| 235 | let migrate_err = |e: &dyn std::fmt::Display| crate::Error::Config(format!("schema shim: {e}")); |
| 236 | let conn = rusqlite::Connection::open(path) |
| 237 | .map_err(|e| crate::Error::Config(format!("opening {} to migrate: {e}", path.display())))?; |
| 238 | for ddl in SCHEMA_SHIMS { |
| 239 | conn.execute_batch(ddl).map_err(|e| migrate_err(&e))?; |
| 240 | } |
| 241 | for (table, column, ddl) in COLUMN_SHIMS { |
| 242 | let present: i64 = conn |
| 243 | .query_row( |
| 244 | "SELECT count(*) FROM pragma_table_info(?1) WHERE name = ?2", |
| 245 | (table, column), |
| 246 | |r| r.get(0), |
| 247 | ) |
| 248 | .map_err(|e| migrate_err(&e))?; |
| 249 | if present == 0 { |
| 250 | conn.execute_batch(ddl).map_err(|e| migrate_err(&e))?; |
| 251 | } |
| 252 | } |
| 253 | Ok(()) |
| 254 | } |
| 255 | |
| 256 | #[cfg(test)] |
| 257 | mod tests { |
| 258 | use super::*; |
| 259 | |
| 260 | /// Tables created by shims (i.e. added after the first deployment). |
| 261 | const SHIMMED_TABLES: &[&str] = &[ |
| 262 | "ci_artifacts", |
| 263 | "issues", |
| 264 | "issue_comments", |
| 265 | "attachments", |
| 266 | "api_tokens", |
| 267 | "admin_caches", |
| 268 | "repo_secrets", |
| 269 | "agent_sessions", |
| 270 | "user_secrets", |
| 271 | ]; |
| 272 | |
| 273 | /// Every schema object (table + indexes) for `table`, normalized. |
| 274 | fn schema_objects(path: &Path, table: &str) -> Vec<String> { |
| 275 | let conn = rusqlite::Connection::open(path).unwrap(); |
| 276 | let mut stmt = conn |
| 277 | .prepare( |
| 278 | "SELECT sql FROM sqlite_master WHERE tbl_name=?1 \ |
| 279 | AND sql IS NOT NULL ORDER BY name", |
| 280 | ) |
| 281 | .unwrap(); |
| 282 | stmt.query_map([table], |r| r.get::<_, String>(0)) |
| 283 | .unwrap() |
| 284 | .map(|s| { |
| 285 | s.unwrap() |
| 286 | .replace(" IF NOT EXISTS", "") |
| 287 | .split_whitespace() |
| 288 | .collect::<Vec<_>>() |
| 289 | .join(" ") |
| 290 | }) |
| 291 | .collect() |
| 292 | } |
| 293 | |
| 294 | /// The hand-written shim DDL must produce the same tables and indexes as |
| 295 | /// a fresh `push_schema`, or fresh and migrated databases would diverge. |
| 296 | #[tokio::test] |
| 297 | async fn schema_shim_matches_push_schema() { |
| 298 | let dir = tempfile::tempdir().unwrap(); |
| 299 | |
| 300 | let fresh = dir.path().join("fresh.db"); |
| 301 | connect(&fresh).await.unwrap(); |
| 302 | |
| 303 | // A database from before the shimmed tables existed. |
| 304 | let migrated = dir.path().join("migrated.db"); |
| 305 | connect(&migrated).await.unwrap(); |
| 306 | let conn = rusqlite::Connection::open(&migrated).unwrap(); |
| 307 | for table in SHIMMED_TABLES { |
| 308 | conn.execute_batch(&format!("DROP TABLE {table}")).unwrap(); |
| 309 | } |
| 310 | drop(conn); |
| 311 | connect(&migrated).await.unwrap(); |
| 312 | |
| 313 | for table in SHIMMED_TABLES { |
| 314 | assert_eq!( |
| 315 | schema_objects(&fresh, table), |
| 316 | schema_objects(&migrated, table), |
| 317 | "schema diverges for {table}" |
| 318 | ); |
| 319 | assert!( |
| 320 | !schema_objects(&fresh, table).is_empty(), |
| 321 | "no schema objects for {table}" |
| 322 | ); |
| 323 | } |
| 324 | } |
| 325 | |
| 326 | /// Column name/type/notnull triples for a table. |
| 327 | fn columns(path: &Path, table: &str) -> Vec<(String, String, bool)> { |
| 328 | let conn = rusqlite::Connection::open(path).unwrap(); |
| 329 | let mut stmt = conn |
| 330 | .prepare("SELECT name, type, \"notnull\" FROM pragma_table_info(?1)") |
| 331 | .unwrap(); |
| 332 | stmt.query_map([table], |r| { |
| 333 | Ok((r.get(0)?, r.get(1)?, r.get::<_, i64>(2)? != 0)) |
| 334 | }) |
| 335 | .unwrap() |
| 336 | .map(|r| r.unwrap()) |
| 337 | .collect() |
| 338 | } |
| 339 | |
| 340 | /// A column shim must converge an old table to the fresh schema's columns |
| 341 | /// (same names, order, types, nullability — the DDL text itself differs |
| 342 | /// because of the backfill `DEFAULT`). |
| 343 | /// |
| 344 | /// The shimmed columns are dropped newest-first, which is the only shape a |
| 345 | /// real database takes: each was appended by a deployment, so an older one |
| 346 | /// is missing that column *and every column added after it*. Dropping one |
| 347 | /// from the middle instead would re-add it at the end and diverge — which |
| 348 | /// says nothing about the migration, only about `ALTER TABLE`. |
| 349 | #[tokio::test] |
| 350 | async fn column_shim_matches_push_schema() { |
| 351 | let dir = tempfile::tempdir().unwrap(); |
| 352 | |
| 353 | let fresh = dir.path().join("fresh.db"); |
| 354 | connect(&fresh).await.unwrap(); |
| 355 | |
| 356 | let migrated = dir.path().join("migrated.db"); |
| 357 | connect(&migrated).await.unwrap(); |
| 358 | let conn = rusqlite::Connection::open(&migrated).unwrap(); |
| 359 | for (table, column, _) in COLUMN_SHIMS.iter().rev() { |
| 360 | conn.execute_batch(&format!(r#"ALTER TABLE "{table}" DROP COLUMN "{column}""#)) |
| 361 | .unwrap(); |
| 362 | } |
| 363 | drop(conn); |
| 364 | connect(&migrated).await.unwrap(); |
| 365 | connect(&migrated).await.unwrap(); // idempotent |
| 366 | |
| 367 | for table in ["repositories", "users"] { |
| 368 | assert_eq!( |
| 369 | columns(&fresh, table), |
| 370 | columns(&migrated, table), |
| 371 | "columns diverge for {table}" |
| 372 | ); |
| 373 | } |
| 374 | } |
| 375 | |
| 376 | /// Reconnecting to an existing database that predates a table must create |
| 377 | /// it (and reconnecting again must be a no-op). |
| 378 | #[tokio::test] |
| 379 | async fn migrate_existing_adds_missing_tables() { |
| 380 | let dir = tempfile::tempdir().unwrap(); |
| 381 | let path = dir.path().join("old.db"); |
| 382 | connect(&path).await.unwrap(); |
| 383 | |
| 384 | // Simulate a database from before the table existed. |
| 385 | let conn = rusqlite::Connection::open(&path).unwrap(); |
| 386 | conn.execute_batch("DROP TABLE ci_artifacts").unwrap(); |
| 387 | drop(conn); |
| 388 | |
| 389 | connect(&path).await.unwrap(); |
| 390 | connect(&path).await.unwrap(); // idempotent |
| 391 | |
| 392 | let conn = rusqlite::Connection::open(&path).unwrap(); |
| 393 | let n: i64 = conn |
| 394 | .query_row( |
| 395 | "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='ci_artifacts'", |
| 396 | [], |
| 397 | |r| r.get(0), |
| 398 | ) |
| 399 | .unwrap(); |
| 400 | assert_eq!(n, 1); |
| 401 | } |
| 402 | } |