Files
Anya Tokugawa d5d21b7841
Build and publish container images / images (push) Successful in 9m34s
Release container images / release (push) Successful in 36s
Refactor architecture into eight components and consolidate reliability work
Extract Grammaratur, Dregarnuhr, Geduldh, Mestionora, Ordoschnellic, Schutzaria, Kunstzeal and Seheweit behind compatible application contracts.

Include accumulated editor resilience, navigation and theme UX, remote CLI, storage/schema and DR groundwork, tests and design/operations documentation.

Verified: uncached full Go suite; go vet ./...; race tests for all eight components; 11 targeted Chromium tests; Node suites; 19 Python CLI tests; reproducible editor build. No push, release tag or deployment in this checkpoint.
2026-11-01 15:23:31 +09:00

263 lines
6.6 KiB
Go

package db
import (
"crypto/sha256"
"database/sql"
"encoding/hex"
"fmt"
)
func Migrate(
db *sql.DB,
) error {
stmts := []string{
`
CREATE TABLE IF NOT EXISTS objects (
uuid TEXT PRIMARY KEY,
kind TEXT,
created_at INTEGER DEFAULT (
strftime('%s','now')
)
)
`,
`
CREATE TABLE IF NOT EXISTS object_origins (
object_uuid TEXT PRIMARY KEY REFERENCES objects(uuid) ON DELETE CASCADE,
domain_uuid TEXT NOT NULL,
created_at INTEGER NOT NULL
)
`,
`
CREATE TABLE IF NOT EXISTS revisions (
uuid TEXT PRIMARY KEY,
object_uuid TEXT,
body TEXT,
document_hash TEXT,
lang TEXT NOT NULL DEFAULT 'und',
content_type TEXT NOT NULL DEFAULT 'text/html; charset=utf-8',
filename TEXT NOT NULL DEFAULT '',
message TEXT,
created_at_ms INTEGER,
created_at INTEGER DEFAULT (
strftime('%s','now')
)
)
`,
`
CREATE TABLE IF NOT EXISTS paths (
path TEXT PRIMARY KEY,
object_uuid TEXT,
created_at INTEGER DEFAULT (
strftime('%s','now')
)
)
`,
`
CREATE TABLE IF NOT EXISTS tags (
object_uuid TEXT,
tag TEXT,
created_at INTEGER DEFAULT (
strftime('%s','now')
)
)
`,
`
CREATE TABLE IF NOT EXISTS assets (
address TEXT PRIMARY KEY,
object_uuid TEXT NOT NULL,
head_document_hash TEXT,
created_at_ms INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL
)
`,
`
CREATE TABLE IF NOT EXISTS revision_document_refs (
revision_uuid TEXT NOT NULL,
document_hash TEXT NOT NULL,
PRIMARY KEY (revision_uuid, document_hash),
FOREIGN KEY (revision_uuid) REFERENCES revisions(uuid) ON DELETE CASCADE
)
`,
}
for _, stmt := range stmts {
_, err := db.Exec(stmt)
if err != nil {
return err
}
}
columns := []struct{ table, name, declaration string }{
{"paths", "created_at", "INTEGER"},
{"objects", "created_at", "INTEGER"},
{"revisions", "created_at", "INTEGER"},
{"revisions", "document_hash", "TEXT"},
{"revisions", "lang", "TEXT NOT NULL DEFAULT 'und'"},
{"revisions", "created_at_ms", "INTEGER"},
{"revisions", "content_type", "TEXT NOT NULL DEFAULT 'text/html; charset=utf-8'"},
{"revisions", "filename", "TEXT NOT NULL DEFAULT ''"},
{"tags", "created_at", "INTEGER"},
}
for _, column := range columns {
if err := ensureSQLiteColumn(db, column.table, column.name, column.declaration); err != nil {
return err
}
}
if err := migrateRevisionMetadata(db); err != nil {
return err
}
if _, err := db.Exec(`CREATE INDEX IF NOT EXISTS idx_revision_document_refs_hash ON revision_document_refs(document_hash,revision_uuid)`); err != nil {
return err
}
// Older databases can already carry revision_metadata_v1 without the
// subsequently added index. Ensure it independently of the data backfill.
if _, err := db.Exec(`CREATE INDEX IF NOT EXISTS idx_revisions_object_history ON revisions(object_uuid, created_at_ms)`); err != nil {
return err
}
_, err := db.Exec(`CREATE INDEX IF NOT EXISTS idx_revisions_object_created
ON revisions(object_uuid, created_at_ms DESC)`)
return err
}
func migrateRevisionMetadata(db *sql.DB) error {
if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS schema_migrations (
name TEXT PRIMARY KEY,
applied_at INTEGER NOT NULL
)
`); err != nil {
return err
}
var applied int
if err := db.QueryRow(`
SELECT EXISTS(
SELECT 1
FROM schema_migrations
WHERE name = 'revision_metadata_v1'
)
`).Scan(&applied); err != nil {
return err
}
if applied != 0 {
return nil
}
// Fetch only identifiers in batches, then hydrate one legacy body at a
// time. The old implementation accumulated all payloads in memory.
lastUUID := ""
for {
rows, err := db.Query(`SELECT uuid FROM revisions WHERE uuid>? AND (document_hash IS NULL OR document_hash='') ORDER BY uuid LIMIT 128`, lastUUID)
if err != nil {
return err
}
var ids []string
for rows.Next() {
var id string
if err := rows.Scan(&id); err != nil {
rows.Close()
return err
}
ids = append(ids, id)
}
if err := rows.Err(); err != nil {
rows.Close()
return err
}
if err := rows.Close(); err != nil {
return err
}
for _, id := range ids {
var body string
if err := db.QueryRow(`SELECT COALESCE(body,'') FROM revisions WHERE uuid=?`, id).Scan(&body); err != nil {
return err
}
sum := sha256.Sum256([]byte(body))
if _, err := db.Exec(`UPDATE revisions SET document_hash=? WHERE uuid=? AND (document_hash IS NULL OR document_hash='')`, hex.EncodeToString(sum[:]), id); err != nil {
return err
}
lastUUID = id
}
if len(ids) < 128 {
break
}
}
if _, err := db.Exec(`
UPDATE revisions
SET
lang = COALESCE(NULLIF(lang, ''), 'und'),
created_at_ms = COALESCE(
created_at_ms,
COALESCE(created_at, 0) * 1000
)
WHERE lang IS NULL
OR lang = ''
OR created_at_ms IS NULL
`); err != nil {
return err
}
if _, err := db.Exec(`
CREATE INDEX IF NOT EXISTS idx_revisions_object_created
ON revisions (
object_uuid,
created_at_ms DESC
)
`); err != nil {
return err
}
_, err := db.Exec(`
INSERT OR IGNORE INTO schema_migrations (name, applied_at)
VALUES ('revision_metadata_v1', strftime('%s','now'))
`)
return err
}
func sqliteColumnExists(database *sql.DB, table, column string) (bool, error) {
// All identifiers originate in the fixed legacy bootstrap above.
rows, err := database.Query(fmt.Sprintf("PRAGMA table_info(%q)", table))
if err != nil {
return false, err
}
defer rows.Close()
found := false
for rows.Next() {
var cid, notNull, primaryKey int
var name, kind string
var defaultValue any
if err := rows.Scan(&cid, &name, &kind, &notNull, &defaultValue, &primaryKey); err != nil {
return false, err
}
found = found || name == column
}
return found, rows.Err()
}
func ensureSQLiteColumn(database *sql.DB, table, column, declaration string) error {
exists, err := sqliteColumnExists(database, table, column)
if err != nil || exists {
return err
}
_, err = database.Exec(fmt.Sprintf("ALTER TABLE %q ADD COLUMN %q %s", table, column, declaration))
if err != nil {
// Only tolerate another bootstrap adding the exact column concurrently.
if exists, checkErr := sqliteColumnExists(database, table, column); checkErr == nil && exists {
return nil
}
return fmt.Errorf("add column %s.%s: %w", table, column, err)
}
return nil
}