Files
pages/internal/db/migrate.go
Anya Tokugawa c1175d389f Implement Knowledge City platform, organizer and DAV integration
Capture accumulated application, UI, CLI, regression tests and design documentation changes, including task project mapping and default CalDAV list naming.

Keep deployment data, backups, local credentials and build artifacts out of Git. Move the Compose enrollment token to an ignored local environment file.
2026-09-27 14:44:43 +09:00

266 lines
5.4 KiB
Go

package db
import (
"crypto/sha256"
"database/sql"
"encoding/hex"
)
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
}
}
alterStmts := []string{
`
ALTER TABLE paths
ADD COLUMN created_at INTEGER
`,
`
ALTER TABLE objects
ADD COLUMN created_at INTEGER
`,
`
ALTER TABLE revisions
ADD COLUMN created_at INTEGER
`,
`
ALTER TABLE revisions
ADD COLUMN document_hash TEXT
`,
`
ALTER TABLE revisions
ADD COLUMN lang TEXT NOT NULL DEFAULT 'und'
`,
`
ALTER TABLE revisions
ADD COLUMN created_at_ms INTEGER
`,
`
ALTER TABLE revisions
ADD COLUMN content_type TEXT NOT NULL DEFAULT 'text/html; charset=utf-8'
`,
`
ALTER TABLE revisions
ADD COLUMN filename TEXT NOT NULL DEFAULT ''
`,
`
ALTER TABLE tags
ADD COLUMN created_at INTEGER
`,
}
for _, stmt := range alterStmts {
_, _ = db.Exec(stmt)
}
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
}
rows, err := db.Query(`
SELECT
uuid,
COALESCE(body, '')
FROM revisions
WHERE document_hash IS NULL
OR document_hash = ''
`)
if err != nil {
return err
}
type legacyRevision struct {
uuid string
body string
}
var revisions []legacyRevision
for rows.Next() {
var revision legacyRevision
if err := rows.Scan(&revision.uuid, &revision.body); err != nil {
rows.Close()
return err
}
revisions = append(revisions, revision)
}
if err := rows.Close(); err != nil {
return err
}
for _, revision := range revisions {
sum := sha256.Sum256([]byte(revision.body))
documentHash := hex.EncodeToString(sum[:])
if _, err := db.Exec(`
UPDATE revisions
SET document_hash = ?
WHERE uuid = ?
`, documentHash, revision.uuid); err != nil {
return err
}
}
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
}