Files
pages/internal/store/schema_modules_test.go
Anya Tokugawa e65ca56f92
Build and publish container images / images (push) Successful in 12m27s
feat: add audited work records and controlled digest retries
2026-11-08 02:35:29 +09:00

318 lines
15 KiB
Go

package store
import (
"context"
"errors"
"path/filepath"
"testing"
"pageswiki/internal/db"
)
func TestContactDAVUpgradePreservesRowsAndTracksChanges(t *testing.T) {
st := newSchemaTestStore(t)
ctx := context.Background()
if e := db.ApplySQLiteMigrations(ctx, st.DB, "contacts", contactMigrations[:1]); e != nil {
t.Fatal(e)
}
if _, e := st.DB.Exec(`INSERT INTO organizer_contacts VALUES('d','u','c','uid',1,'Name','name','{"name":"Name"}','hash',0)`); e != nil {
t.Fatal(e)
}
for range 2 {
if e := st.ensureSchemaComponent("contacts"); e != nil {
t.Fatal(e)
}
}
var name, data string
var before, after int64
var count int
if e := st.DB.QueryRow(`SELECT name FROM organizer_contact_names WHERE uuid='c'`).Scan(&name); e != nil || name != "c.vcf" {
t.Fatal(name, e)
}
if e := st.DB.QueryRow(`SELECT data FROM organizer_contacts WHERE uuid='c'`).Scan(&data); e != nil || data != `{"name":"Name"}` {
t.Fatal(data, e)
}
if e := st.DB.QueryRow(`SELECT sequence FROM organizer_contact_changes WHERE uuid='c'`).Scan(&before); e != nil {
t.Fatal(e)
}
for range 3 {
if _, e := st.DB.Exec(`UPDATE organizer_contacts SET deleted=1 WHERE uuid='c'`); e != nil {
t.Fatal(e)
}
}
if e := st.DB.QueryRow(`SELECT count(*),max(sequence) FROM organizer_contact_changes WHERE uuid='c'`).Scan(&count, &after); e != nil || count != 1 || after <= before {
t.Fatal(count, before, after, e)
}
if _, e := st.DB.Exec(`INSERT INTO organizer_dav_credentials VALUES('credential'); INSERT INTO organizer_contact_credentials VALUES('credential'); DELETE FROM organizer_dav_credentials WHERE id='credential'`); e != nil {
t.Fatal(e)
}
if e := st.DB.QueryRow(`SELECT count(*) FROM organizer_contact_credentials`).Scan(&count); e != nil || count != 0 {
t.Fatal(count, e)
}
}
func newSchemaTestStore(t *testing.T) *Store {
t.Helper()
database, err := db.Open(filepath.Join(t.TempDir(), "schema.db"))
if err != nil {
t.Fatal(err)
}
t.Cleanup(func() { database.Close() })
// task-productivity follows the legacy task schema at application startup.
// Provide its source projection, not the component under test itself.
if _, err = database.Exec(`CREATE TABLE organizer_items(domain_uuid TEXT,owner_uuid TEXT,uuid TEXT,kind TEXT,data TEXT)`); err != nil {
t.Fatal(err)
}
if _, err = database.Exec(`CREATE TABLE organizer_dav_credentials(id TEXT PRIMARY KEY)`); err != nil {
t.Fatal(err)
}
return &Store{DB: database}
}
func TestTaskProductivityMigrationBackfillsOnce(t *testing.T) {
st := newSchemaTestStore(t)
if _, e := st.DB.Exec(`INSERT INTO organizer_items VALUES('d','u','t','task','{"completed_at_ms":123,"estimate_minutes":60}')`); e != nil {
t.Fatal(e)
}
for range 2 {
if e := st.ensureSchemaComponent("task-productivity"); e != nil {
t.Fatal(e)
}
}
var count, complexity int
var completed int64
if e := st.DB.QueryRow(`SELECT count(*),completed_ms,complexity FROM organizer_task_metrics`).Scan(&count, &completed, &complexity); e != nil || count != 1 || completed != 123 || complexity != 1 {
t.Fatal(count, completed, complexity, e)
}
}
func TestDashboardUpgradePreservesTaskPreferences(t *testing.T) {
st := newSchemaTestStore(t)
if err := db.ApplySQLiteMigrations(context.Background(), st.DB, "task-productivity", taskProductivityMigrations[:1]); err != nil {
t.Fatal(err)
}
if _, err := st.DB.Exec(`INSERT INTO organizer_task_preferences VALUES('domain','owner',7,'{"estimate_minutes":90}',123)`); err != nil {
t.Fatal(err)
}
for range 2 {
if err := st.ensureSchemaComponent("task-productivity"); err != nil {
t.Fatal(err)
}
}
var version, next int64
var raw string
if err := st.DB.QueryRow(`SELECT version,data,next_digest_ms FROM organizer_task_preferences`).Scan(&version, &raw, &next); err != nil || version != 7 || next != 123 || raw != `{"estimate_minutes":90}` {
t.Fatal(version, raw, next, err)
}
var count int
if err := st.DB.QueryRow(`SELECT count(*) FROM organizer_dashboard_layouts`).Scan(&count); err != nil || count != 0 {
t.Fatal(count, err)
}
}
func TestWorkHistoryUpgradePreservesLegacySessions(t *testing.T) {
st := newSchemaTestStore(t)
if err := db.ApplySQLiteMigrations(context.Background(), st.DB, "task-productivity", taskProductivityMigrations[:2]); err != nil {
t.Fatal(err)
}
if _, err := st.DB.Exec(`INSERT INTO organizer_work_sessions VALUES('w','d','u','t',100,200,2)`); err != nil {
t.Fatal(err)
}
for range 2 {
if err := st.ensureSchemaComponent("task-productivity"); err != nil {
t.Fatal(err)
}
}
var start, end, version, cancelled int64
if err := st.DB.QueryRow(`SELECT start_ms,end_ms,version,cancelled_ms FROM organizer_work_sessions WHERE uuid='w'`).Scan(&start, &end, &version, &cancelled); err != nil || start != 100 || end != 200 || version != 2 || cancelled != 0 {
t.Fatal(start, end, version, cancelled, err)
}
var count int
if err := st.DB.QueryRow(`SELECT count(*) FROM organizer_work_revisions`).Scan(&count); err != nil || count != 0 {
t.Fatal("invented history", count, err)
}
}
func TestDigestHistoryUpgradePreservesReservations(t *testing.T) {
st := newSchemaTestStore(t)
ctx := context.Background()
if err := db.ApplySQLiteMigrations(ctx, st.DB, "task-productivity", taskProductivityMigrations[:3]); err != nil {
t.Fatal(err)
}
for i, state := range []string{"sent", "sending", "uncertain", "cancelled"} {
if _, err := st.DB.Exec(`INSERT INTO organizer_digest_deliveries VALUES(?,?,?, ?,?)`, "d", state, "2026-11-01", state, 100+i); err != nil {
t.Fatal(err)
}
}
for range 2 {
if err := st.ensureSchemaComponent("task-productivity"); err != nil {
t.Fatal(err)
}
}
var count int
if err := st.DB.QueryRow(`SELECT count(*) FROM organizer_digest_deliveries d JOIN organizer_digest_attempts a ON a.uuid=d.attempt_uuid AND a.domain_uuid=d.domain_uuid AND a.owner_uuid=d.owner_uuid AND a.day=d.day AND a.state=d.state AND a.updated_ms=d.updated_ms WHERE a.kind='legacy' AND a.preference_version=0`).Scan(&count); err != nil || count != 4 {
t.Fatal("reservation migration", count, err)
}
var integrity string
if err := st.DB.QueryRow(`PRAGMA integrity_check`).Scan(&integrity); err != nil || integrity != "ok" {
t.Fatal(integrity, err)
}
}
func TestDigestHistoryMigrationRollback(t *testing.T) {
st := newSchemaTestStore(t)
ctx := context.Background()
if err := db.ApplySQLiteMigrations(ctx, st.DB, "task-productivity", taskProductivityMigrations[:3]); err != nil {
t.Fatal(err)
}
if _, err := st.DB.Exec(`INSERT INTO organizer_digest_deliveries VALUES('d','u','2026-11-01','sent',123)`); err != nil {
t.Fatal(err)
}
steps := append([]db.SQLMigration(nil), taskProductivityMigrations...)
steps[3].Statements = append(append([]string(nil), steps[3].Statements...), `INVALID MIGRATION STATEMENT`)
if err := db.ApplySQLiteMigrations(ctx, st.DB, "task-productivity", steps); err == nil {
t.Fatal("failed migration committed")
}
var columns, ledger, attempts int
if err := st.DB.QueryRow(`SELECT count(*) FROM pragma_table_info('organizer_digest_deliveries')`).Scan(&columns); err != nil || columns != 5 {
t.Fatal("DDL not rolled back", columns, err)
}
if err := st.DB.QueryRow(`SELECT count(*) FROM schema_migration_steps WHERE component='task-productivity'`).Scan(&ledger); err != nil || ledger != 3 {
t.Fatal(ledger, err)
}
if err := st.DB.QueryRow(`SELECT count(*) FROM sqlite_master WHERE name='organizer_digest_attempts'`).Scan(&attempts); err != nil || attempts != 0 {
t.Fatal(attempts, err)
}
var state string
if err := st.DB.QueryRow(`SELECT state FROM organizer_digest_deliveries`).Scan(&state); err != nil || state != "sent" {
t.Fatal(state, err)
}
if err := st.ensureSchemaComponent("task-productivity"); err != nil {
t.Fatal("retry migration", err)
}
}
func TestManagedSchemasFreshRepeatAndReadOnly(t *testing.T) {
for component := range managedStoreSchemas {
t.Run(component, func(t *testing.T) {
st := newSchemaTestStore(t)
if err := st.ensureSchemaComponent(component); err != nil {
t.Fatal(err)
}
if err := st.ensureSchemaComponent(component); err != nil {
t.Fatal(err)
}
var count int
if err := st.DB.QueryRow(`SELECT count(*) FROM schema_migration_steps WHERE component=?`, component).Scan(&count); err != nil || count != len(managedStoreSchemas[component]) {
t.Fatalf("ledger=%d err=%v", count, err)
}
st.ReadOnly = true
if err := st.ensureSchemaComponent(component); !errors.Is(err, ErrReadOnly) {
t.Fatalf("standby migration: %v", err)
}
})
}
}
func TestManagedSchemasAdoptLegacyRowsWithoutRecreatingTables(t *testing.T) {
for _, tc := range []struct{ component, ddl, insert, query, want string }{
{"browser-navigation", `CREATE TABLE browser_navigation_links(uuid TEXT PRIMARY KEY,domain_uuid TEXT NOT NULL,principal_uuid TEXT NOT NULL,label TEXT NOT NULL,path TEXT NOT NULL,position INTEGER NOT NULL DEFAULT 0,created_at_ms INTEGER NOT NULL,updated_at_ms INTEGER NOT NULL,UNIQUE(domain_uuid,principal_uuid,path))`,
`INSERT INTO browser_navigation_links VALUES('u','d','p','keep','/page',3,10,20)`, `SELECT label||':'||position||':'||is_directory FROM browser_navigation_links WHERE uuid='u'`, "keep:3:0"},
{"adapter-sessions", `CREATE TABLE adapter_sessions(ticket_hash TEXT PRIMARY KEY,session_hash TEXT NOT NULL DEFAULT '',domain_uuid TEXT NOT NULL,plugin_id TEXT NOT NULL,object_uuid TEXT NOT NULL,base_revision_uuid TEXT NOT NULL,ticket_expires_at_ms INTEGER NOT NULL,session_expires_at_ms INTEGER NOT NULL DEFAULT 0,exchanged_at_ms INTEGER NOT NULL DEFAULT 0,created_at_ms INTEGER NOT NULL)`,
`INSERT INTO adapter_sessions VALUES('ticket','session','d','code','o','r',100,200,50,10)`, `SELECT session_hash||':'||root_path||':'||principal_uuid FROM adapter_sessions WHERE ticket_hash='ticket'`, "session::"},
{"imports", `CREATE TABLE import_journal(session_uuid TEXT NOT NULL,sequence INTEGER NOT NULL,path TEXT NOT NULL,object_uuid TEXT NOT NULL,created_at_ms INTEGER NOT NULL,PRIMARY KEY(session_uuid,sequence))`,
`INSERT INTO import_journal VALUES('s',2,'/keep','o',20)`, `SELECT path||':'||completed FROM import_journal WHERE session_uuid='s' AND sequence=2`, "/keep:0"},
{"resource-history", `CREATE TABLE resource_samples(id INTEGER PRIMARY KEY,job_uuid TEXT NOT NULL UNIQUE,timestamp_ms INTEGER NOT NULL,payload TEXT NOT NULL)`,
`INSERT INTO resource_samples VALUES(9,'j',100,'keep')`, `SELECT id||':'||payload FROM resource_samples WHERE job_uuid='j'`, "9:keep"},
{"projection-metadata", `CREATE TABLE projection_metadata(document_hash TEXT NOT NULL,kind TEXT NOT NULL,version INTEGER NOT NULL,payload TEXT NOT NULL DEFAULT '{}',error TEXT NOT NULL DEFAULT '',updated_at_ms INTEGER NOT NULL,retry_at_ms INTEGER NOT NULL DEFAULT 0,PRIMARY KEY(document_hash,kind,version))`,
`INSERT INTO projection_metadata VALUES('h','pdf',1,'keep','',10,0)`, `SELECT payload FROM projection_metadata WHERE document_hash='h'`, "keep"},
{"broadcasts", `CREATE TABLE broadcasts(uuid TEXT PRIMARY KEY,domain_uuid TEXT NOT NULL,title TEXT NOT NULL,body TEXT NOT NULL,severity TEXT NOT NULL DEFAULT 'info' CHECK(severity IN ('info','success','warning','critical')),starts_at_ms INTEGER NOT NULL DEFAULT 0,ends_at_ms INTEGER NOT NULL DEFAULT 0,created_at_ms INTEGER NOT NULL,updated_at_ms INTEGER NOT NULL)`,
`INSERT INTO broadcasts VALUES('u','d','title','keep','info',0,0,10,20)`, `SELECT body FROM broadcasts WHERE uuid='u'`, "keep"},
} {
t.Run(tc.component, func(t *testing.T) {
st := newSchemaTestStore(t)
for _, statement := range []string{tc.ddl, tc.insert} {
if _, err := st.DB.Exec(statement); err != nil {
t.Fatal(err)
}
}
if err := st.ensureSchemaComponent(tc.component); err != nil {
t.Fatal(err)
}
var value string
if err := st.DB.QueryRow(tc.query).Scan(&value); err != nil || value != tc.want {
t.Fatalf("row=%q want=%q err=%v", value, tc.want, err)
}
if err := st.ensureSchemaComponent(tc.component); err != nil {
t.Fatal(err)
}
})
}
}
func TestManagedSchemasRejectBrokenAdoptionAndLaterDrift(t *testing.T) {
st := newSchemaTestStore(t)
// Matching columns without the conflict-target uniqueness is not sufficient.
if _, err := st.DB.Exec(`CREATE TABLE resource_samples(id INTEGER PRIMARY KEY,job_uuid TEXT NOT NULL,timestamp_ms INTEGER NOT NULL,payload TEXT NOT NULL); INSERT INTO resource_samples VALUES(1,'j',10,'keep')`); err != nil {
t.Fatal(err)
}
if err := st.EnsureResourceHistorySchema(); err == nil {
t.Fatal("missing unique constraint accepted")
}
var payload string
if err := st.DB.QueryRow(`SELECT payload FROM resource_samples WHERE id=1`).Scan(&payload); err != nil || payload != "keep" {
t.Fatal("failed adoption modified data", err)
}
var count int
if err := st.DB.QueryRow(`SELECT count(*) FROM sqlite_master WHERE name='idx_resource_samples_time'`).Scan(&count); err != nil || count != 0 {
t.Fatal("failed adoption left partial DDL", err)
}
if _, err := st.DB.Exec(`CREATE UNIQUE INDEX restored_resource_unique ON resource_samples(job_uuid)`); err != nil {
t.Fatal(err)
}
if err := st.EnsureResourceHistorySchema(); err != nil {
t.Fatal(err)
}
if _, err := st.DB.Exec(`DROP INDEX idx_resource_samples_time`); err != nil {
t.Fatal(err)
}
if err := st.EnsureResourceHistorySchema(); err == nil {
t.Fatal("ledger masked missing index")
}
if err := st.EnsureAdapterSessionsSchema(); err != nil {
t.Fatal(err)
}
if _, err := st.DB.Exec(`DROP INDEX idx_adapter_session_hash; CREATE UNIQUE INDEX idx_adapter_session_hash ON adapter_sessions(session_hash) WHERE session_hash = ''`); err != nil {
t.Fatal(err)
}
if err := st.EnsureAdapterSessionsSchema(); err == nil {
t.Fatal("incorrect partial index predicate accepted")
}
}
func TestEventJobsAdoptLegacyDueIndex(t *testing.T) {
st := newSchemaTestStore(t)
// The deployed pre-ledger scheduler used the same index name without priority.
for _, statement := range []string{
eventJobMigrations[0].Statements[0],
`CREATE INDEX idx_event_jobs_due ON event_jobs(status,run_at_ms,created_at_ms)`,
`INSERT INTO event_jobs(uuid,event_type,event_key,payload,status,attempts,run_at_ms,created_at_ms,updated_at_ms) VALUES('keep','test','key','unchanged','running',2,10,20,30)`,
} {
if _, err := st.DB.Exec(statement); err != nil {
t.Fatal(err)
}
}
for range 2 {
if err := st.ensureSchemaComponent("event-jobs"); err != nil {
t.Fatal(err)
}
}
var columns, row string
if err := st.DB.QueryRow(`SELECT group_concat(name,',') FROM (SELECT name FROM pragma_index_info('idx_event_jobs_due') ORDER BY seqno)`).Scan(&columns); err != nil || columns != "status,priority,run_at_ms,created_at_ms" {
t.Fatalf("index=%q err=%v", columns, err)
}
if err := st.DB.QueryRow(`SELECT payload||':'||status||':'||attempts||':'||run_at_ms||':'||created_at_ms||':'||updated_at_ms FROM event_jobs WHERE uuid='keep'`).Scan(&row); err != nil || row != "unchanged:running:2:10:20:30" {
t.Fatalf("adoption changed job: %q err=%v", row, err)
}
}