ef-core-sqlite
EF Core + SQLite practices for DysonHarness (DysonDbContext, DysonDbAccessor, IDbContextFactory, migrations, value converters, WAL/timeout, SubjectId). Use when changing entities, DbContext configuration, repository concurrency, SQLite schema, migrations, value converters, indexes, cascade deletes, or any service that touches dyson.db in this repository.
- License 许可
- AGPL-3.0
- 最近更新
- 2026-11-13
- Stars
- ★ 4
- Forks
- 0
01 前言 Front Matter
- name
- ef-core-sqlite
- description
- EF Core + SQLite practices for DysonHarness (DysonDbContext, DysonDbAccessor, IDbContextFactory, migrations, value converters, WAL/timeout, SubjectId). Use when changing entities, DbContext configuration, repository concurrency, SQLite schema, migrations, value converters, indexes, cascade deletes, or any service that touches dyson.db in this repository.
02 资源文件 1
- 参考文档reference.md3.5 KB
03 SKILL.md 正文
EF Core + SQLite (DysonHarness)
Apply when working on persistence in Harness.LocalDb (DysonDbContext, repository impls, migrations). Contracts and EF-free POCOs live in Harness.Abstractions/Storage. Prefer Microsoft EF Core guidance adapted to this app’s single-file SQLite model. Subject scoping / shared providers: docs/storage/cloud-hosting.md.
Repo map
| Piece | Location |
|---|---|
| Contracts + POCOs | src/Harness/Harness.Abstractions/Storage/ |
| DbContext | src/Harness/Harness.LocalDb/Storage/DysonDbContext.cs |
| Connection + WAL/timeout | DysonSqliteConfigurator |
| Thread-safe EF entrypoint | DysonDbAccessor (singleton) |
| Design-time factory | src/Harness/Harness.LocalDb/Storage/DysonDbContextFactory.cs |
| Runtime registration | AddDysonLocalDb(databasePath) (UI Program.cs) |
| Migrations | src/Harness/Harness.LocalDb/Migrations/ |
| Value converter example | src/Harness/Harness.LocalDb/Storage/StringListJsonValueConverter.cs |
| Apply on startup | once via factory EnsureMigrated() in Program.cs |
| Schema docs | docs/storage/models.md, docs/storage/sessions.md, docs/storage/cloud-hosting.md |
DB file: DysonAppPaths.GetDatabasePath(DysonBuildInfo.Current) → {app-data}/dyson.db. Timestamps: DateTime UTC only — never DateTimeOffset on entities or EF OrderBy (SQLite limitation; see csharp skill / rules).
Subject columns: required SubjectId on subject-owned tables and on model_providers (owner or DysonSubjects.Shared). Existing rows migrate to "local". Child rows (slugs, turns, logs, todos) stay parent-scoped.
When SQLite fits
- Local / single-user app data (this harness): one file, no server, simple deploy.
- Multi-subject LocalDb on one file is fine for desktop + small cloud hosts; not a substitute for multi-writer server DBs when you need strong concurrent writers, schemas, sequences, or rich ALTER support.
- Other DB backends implement the same
IDyson*Repositoryinterfaces outside this repo.
Limitations vs other providers (act on these)
- No schemas / sequences; no native DB-generated concurrency tokens.
- Prefer
DateTime(UTC) overDateTimeOffset; avoid ordering/comparingdecimal/TimeSpan/ulongin SQL — EF may client-eval. - Some schema ops need table rebuilds; EF may rebuild or throw
NotSupportedException— review generated migrations for SQLite. - Idempotent migration scripts are limited; prefer
Database.Migrate()/dotnet ef database update.
Connection (timeout + WAL)
Always configure through DysonSqliteConfigurator (not bare Data Source=):
- Connection string:
Data Source={path};Default Timeout=30(busy timeout seconds) - On open:
PRAGMA journal_mode=WAL;+PRAGMA synchronous=NORMAL; - Used by DI factory registration,
OnConfiguring, design-time factory, and tests that open file-backed DBs - Compaction is
DysonSqliteVacuumHostedService(AddDysonLocalDb); do not ad-hocVACUUMfrom repositories
DysonSqliteConfigurator.Configure(options, databasePath);
Mandatory service rule (concurrency)
Any DB access in a service must use either:
- A new
DysonDbContextfromIDbContextFactory/DysonDbAccessor.RunAsyncfor that operation (preferred default), or - A
DysonDbContextpassed down from the caller that already owns it — one logical owner / one thread of execution at a time on that instance.
Hard bans:
- Never inject a long-lived shared scoped
DysonDbContextinto multithreaded services for ad-hoc use. - Never share one context across parallel tools /
Task.WhenAll/Task.Run/ multiple repositories. - Never stash a context on a singleton beyond the owning
RunAsynclifetime.
Accessor pattern
// Public repository API
public Task<Result<Guid, string>> CreateAsync(...) =>
_accessor.RunAsync((db, ct) => CreateCoreAsync(db, ..., ct), cancellationToken);
// Pass-down helper (same unit of work; no parallel ops on db)
private static async Task TouchAsync(DysonDbContext db, Guid id, CancellationToken ct) { ... }
Semantics of RunAsync: acquire process-wide gate (keyed by DB path) → CreateDbContext → run work → dispose → release gate. DysonDbAccessor.SaveChangesAsync retries SQLITE_BUSY (5) / SQLITE_LOCKED (6).
SQLite one-writer
SQLite allows one writer at a time. The process-wide gate serializes writers in-process; Default Timeout=30 waits on external lock holders. Error 5 = database busy (another connection holds a write lock). Prefer short transactions; do not hold a context across UI awaits outside RunAsync.
UpsertTurn contention retry
IDysonSessionRepository.UpsertTurnAsync (LocalDb impl) alone retries (~5 attempts, short backoff, fresh RunAsync each time) on:
- EF
InvalidOperationException(“second operation … on this context”) SqliteExceptionbusy (5) / locked (6)
Other errors return Failed to upsert turn: … immediately.
EnsureCreated vs migrations
| API | Use |
|---|---|
Database.Migrate() / EnsureMigrated() |
This repo’s default — creates DB if missing and applies pending migrations |
EnsureCreated() |
Prototypes / throwaway in-memory tests only |
Migrate once at app startup (Program.cs factory context). Do not call EnsureMigrated on every scope.
Do not mix EnsureCreated with migrations on the same durable DB file.
Migrations workflow
After entity / OnModelCreating changes:
- Add a migration (from repo, targeting LocalDb):
dotnet ef migrations add <Name> \
--project src/Harness/Harness.LocalDb/Harness.LocalDb.csproj \
--startup-project src/Harness/Harness.UI/Harness.UI.csproj
- Review the generated
Up/Downfor SQLite rebuilds and data loss. - Apply: app startup (
EnsureMigrated) ordotnet ef database updatewith an explicit connection if needed. - Commit migration
.cs+DysonDbContextModelSnapshot.cstogether with model changes. - Update storage docs when schema/behavior changes (
docs/storage/…).
Never hand-edit the live dyson.db schema without a migration — that causes schema drift.
Value converters
Prefer EF ValueConverter / HasConversion over manual serialize/deserialize in repository methods.
In this repo:
List<string>↔ JSON TEXT:StringListJsonValueConverter+ itsValueCompareronHasConversion(..., Comparer)so change tracking sees list mutations.- Enums → int:
.HasConversion<int>(). - Opaque JSON blobs (e.g.
ToolStateJson,PayloadJson,CommentsJson) may stay asstringcolumns when the repository owns parse/format.
Rules:
- Configure converters in
OnModelCreating, not ad hoc at call sites. - For mutable collections/owned types, supply a
ValueComparer(or EF won’t detect in-place edits). - Normalize on write; define safe read behavior for null/invalid provider values (see
StringListJsonValueConverter). - Changing converter output shape usually needs a migration (column type/data rewrite).
Indexes, uniqueness, cascade
Configure in OnModelCreating (match existing style):
- Unique: composite business keys, e.g.
(SubjectId, AbsolutePath),(SubjectId, Key)PK on app settings,(SubjectId, Name)on shells,(SubjectId, ModelSlugId)on favorites,(ProviderId, Slug),(SessionId, Sequence),(SessionId, TaskCode). - Index: filter/sort columns (
SubjectId,LastActivityUtc, FKs used in lookups). - Cascade: owned children of a session/provider (turns, logs, todos, slugs) →
DeleteBehavior.Cascade. - SetNull: optional FKs that should survive parent delete (e.g. session → work directory / slug).
- Restrict: relationships that must not silently wipe graphs (e.g. parent session).
Pick delete behavior deliberately; SQLite will enforce FKs when enabled. Always filter repository queries by IDysonSubjectContext.SubjectId (and shared visibility for model providers).
Pitfalls checklist
- [ ] Schema change → migration + snapshot + docs; never rely on
EnsureCreatedin app code - [ ] Service DB access → factory/accessor or pass-down context with single-thread ownership
- [ ] No shared scoped
DysonDbContextacross parallel tools / repositories - [ ] No
DateTimeOffseton EF entities / ordered queries - [ ] Collection JSON properties use converter + comparer (or immutable replace of the list)
- [ ] Connection uses
DysonSqliteConfigurator(timeout + WAL), not bareData Source= - [ ] Do not ad-hoc
VACUUMfrom repositories — compaction isDysonSqliteVacuumHostedService(AddDysonLocalDb) - [ ] Migrate once at startup — not per DI scope
- [ ] Subject filters on every list/get/write; never ensure
"shared"as a real subject row - [ ] Shared provider writes go through
IDysonAccessEvaluator.Can(ManageSharedProviders) - [ ] Secrets: API keys live in DB for the app’s use — do not log them, do not commit
dyson.db/.envcredentials - [ ] Design-time DB (
dyson-design.db) is not user data; don’t treat it as source of truth - [ ] Abandoned migration lock (EF9+
__EFMigrationsLock): if migrate hangs after a kill, clear that table (see reference.md)
Additional resources
- Deeper Microsoft links and extended notes: reference.md
- Storage overview: docs/storage/models.md
- Cloud hosting / subjects: docs/storage/cloud-hosting.md
- C# / DateTime rule: skills/csharp/SKILL.md
04 同仓库更多 Skills
cursor-skill
Pinned Cursor test skill
codex-skill
Pinned Codex test skill
sample-skill
Pinned test skill
csharp
Modern C# / .NET practices for DysonHarness (Harness.Engine): coding conventions, null safety, async/TAP, naming, shallow architecture (1–2 abstraction layers), and Result-pattern APIs. Use when writing, reviewing, or refactoring C#, .NET, or Harness.Engine code in this repository.