Files
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

309 lines
13 KiB
Go

package store
import (
"database/sql"
"errors"
"strings"
"time"
"github.com/google/uuid"
)
type ImportSession struct {
UUID, DomainUUID, ActorUUID, ArchiveName, ArchivePath, TargetPrefix string
Status, Error string
TotalFiles, MarkdownFiles int
CreatedAtMS, UpdatedAtMS int64
}
type ImportItem struct {
Sequence int `json:"sequence"`
SourcePath string `json:"source_path"`
TargetPath string `json:"target_path"`
Size int64 `json:"size"`
ContentType string `json:"content_type"`
Title string `json:"title"`
Quartier, Street, Spot string
Confidence float64
Selected bool
Imported bool
}
func (s *Store) EnsureImportSchema() error {
statements := []string{
`CREATE TABLE IF NOT EXISTS import_sessions(uuid TEXT PRIMARY KEY,domain_uuid TEXT NOT NULL,actor_uuid TEXT NOT NULL DEFAULT '',archive_name TEXT NOT NULL,archive_path TEXT NOT NULL,target_prefix TEXT NOT NULL DEFAULT '/',status TEXT NOT NULL DEFAULT 'queued',total_files INTEGER NOT NULL DEFAULT 0,markdown_files INTEGER NOT NULL DEFAULT 0,error TEXT NOT NULL DEFAULT '',created_at_ms INTEGER NOT NULL,updated_at_ms INTEGER NOT NULL)`,
`CREATE TABLE IF NOT EXISTS import_items(session_uuid TEXT NOT NULL,sequence INTEGER NOT NULL,source_path TEXT NOT NULL,target_path TEXT NOT NULL,size INTEGER NOT NULL DEFAULT 0,content_type TEXT NOT NULL DEFAULT '',title TEXT NOT NULL DEFAULT '',suggested_quartier TEXT NOT NULL DEFAULT '',suggested_street TEXT NOT NULL DEFAULT '',suggested_spot TEXT NOT NULL DEFAULT '',confidence REAL NOT NULL DEFAULT 0,selected INTEGER NOT NULL DEFAULT 1,PRIMARY KEY(session_uuid,sequence))`,
`CREATE TABLE IF NOT EXISTS 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))`,
`CREATE TABLE IF NOT EXISTS import_city_journal(session_uuid TEXT NOT NULL,kind TEXT NOT NULL,node_uuid TEXT NOT NULL,created_at_ms INTEGER NOT NULL,PRIMARY KEY(session_uuid,kind,node_uuid))`,
`CREATE TABLE IF NOT EXISTS import_city_edges(session_uuid TEXT NOT NULL,spot_uuid TEXT NOT NULL,page_uuid TEXT NOT NULL,created_at_ms INTEGER NOT NULL,PRIMARY KEY(session_uuid,spot_uuid,page_uuid))`,
`CREATE INDEX IF NOT EXISTS idx_import_sessions_domain ON import_sessions(domain_uuid,created_at_ms DESC)`,
}
for _, statement := range statements {
if _, err := s.DB.Exec(statement); err != nil {
return err
}
}
_ = ensureImportColumn(s.DB, "import_journal", "completed", "INTEGER NOT NULL DEFAULT 0")
return nil
}
func ensureImportColumn(db *sql.DB, table, column, declaration string) error {
rows, err := db.Query(`PRAGMA table_info(` + table + `)`)
if err != nil {
return err
}
found := false
for rows.Next() {
var cid, notnull, pk int
var name, kind string
var defaultValue any
if err := rows.Scan(&cid, &name, &kind, &notnull, &defaultValue, &pk); err != nil {
rows.Close()
return err
}
if name == column {
found = true
}
}
rows.Close()
if found {
return nil
}
_, err = db.Exec(`ALTER TABLE ` + table + ` ADD COLUMN ` + column + ` ` + declaration)
return err
}
func (s *Store) CreateImportSession(domainUUID, actor, archiveName, archivePath, targetPrefix string) (string, error) {
id, now := uuid.NewString(), time.Now().UnixMilli()
_, err := s.DB.Exec(`INSERT INTO import_sessions(uuid,domain_uuid,actor_uuid,archive_name,archive_path,target_prefix,created_at_ms,updated_at_ms) VALUES(?,?,?,?,?,?,?,?)`, id, domainUUID, actor, archiveName, archivePath, targetPrefix, now, now)
return id, err
}
func (s *Store) ImportSession(domainUUID, id string) (ImportSession, error) {
var x ImportSession
err := s.DB.QueryRow(`SELECT uuid,domain_uuid,actor_uuid,archive_name,archive_path,target_prefix,status,total_files,markdown_files,error,created_at_ms,updated_at_ms FROM import_sessions WHERE domain_uuid=? AND uuid=?`, domainUUID, id).Scan(&x.UUID, &x.DomainUUID, &x.ActorUUID, &x.ArchiveName, &x.ArchivePath, &x.TargetPrefix, &x.Status, &x.TotalFiles, &x.MarkdownFiles, &x.Error, &x.CreatedAtMS, &x.UpdatedAtMS)
return x, err
}
func (s *Store) ListImportSessions(domainUUID string) ([]ImportSession, error) {
rows, err := s.DB.Query(`SELECT uuid,domain_uuid,actor_uuid,archive_name,archive_path,target_prefix,status,total_files,markdown_files,error,created_at_ms,updated_at_ms FROM import_sessions WHERE domain_uuid=? ORDER BY created_at_ms DESC LIMIT 50`, domainUUID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []ImportSession
for rows.Next() {
var x ImportSession
if err := rows.Scan(&x.UUID, &x.DomainUUID, &x.ActorUUID, &x.ArchiveName, &x.ArchivePath, &x.TargetPrefix, &x.Status, &x.TotalFiles, &x.MarkdownFiles, &x.Error, &x.CreatedAtMS, &x.UpdatedAtMS); err != nil {
return nil, err
}
out = append(out, x)
}
return out, rows.Err()
}
func (s *Store) ReplaceImportItems(sessionUUID string, items []ImportItem) error {
tx, err := s.DB.Begin()
if err != nil {
return err
}
defer tx.Rollback()
if _, err = tx.Exec(`DELETE FROM import_items WHERE session_uuid=?`, sessionUUID); err != nil {
return err
}
markdown := 0
for _, x := range items {
if x.ContentType == "text/markdown" {
markdown++
}
if _, err = tx.Exec(`INSERT INTO import_items(session_uuid,sequence,source_path,target_path,size,content_type,title,suggested_quartier,suggested_street,suggested_spot,confidence) VALUES(?,?,?,?,?,?,?,?,?,?,?)`, sessionUUID, x.Sequence, x.SourcePath, x.TargetPath, x.Size, x.ContentType, x.Title, x.Quartier, x.Street, x.Spot, x.Confidence); err != nil {
return err
}
}
_, err = tx.Exec(`UPDATE import_sessions SET status='ready',total_files=?,markdown_files=?,error='',updated_at_ms=? WHERE uuid=?`, len(items), markdown, time.Now().UnixMilli(), sessionUUID)
if err != nil {
return err
}
return tx.Commit()
}
func (s *Store) FailImportSession(id string, failure error) {
_, _ = s.DB.Exec(`UPDATE import_sessions SET status='failed',error=?,updated_at_ms=? WHERE uuid=?`, failure.Error(), time.Now().UnixMilli(), id)
}
func (s *Store) MarkImportAnalyzing(id string) {
_, _ = s.DB.Exec(`UPDATE import_sessions SET status='analyzing',updated_at_ms=? WHERE uuid=?`, time.Now().UnixMilli(), id)
}
func (s *Store) ListImportItems(sessionUUID string) ([]ImportItem, error) {
rows, err := s.DB.Query(`SELECT i.sequence,i.source_path,i.target_path,i.size,i.content_type,i.title,i.suggested_quartier,i.suggested_street,i.suggested_spot,i.confidence,i.selected,COALESCE(j.completed,0) FROM import_items i LEFT JOIN import_journal j ON j.session_uuid=i.session_uuid AND j.sequence=i.sequence WHERE i.session_uuid=? ORDER BY i.sequence`, sessionUUID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []ImportItem
for rows.Next() {
var x ImportItem
var selected, imported int
if err := rows.Scan(&x.Sequence, &x.SourcePath, &x.TargetPath, &x.Size, &x.ContentType, &x.Title, &x.Quartier, &x.Street, &x.Spot, &x.Confidence, &selected, &imported); err != nil {
return nil, err
}
x.Selected = selected != 0
x.Imported = imported != 0
if x.ContentType == "text/markdown" {
x.TargetPath = strings.TrimSuffix(strings.TrimSuffix(x.TargetPath, ".md"), ".markdown")
}
out = append(out, x)
}
return out, rows.Err()
}
func (s *Store) UpdateImportPlan(domainUUID, sessionUUID string, items []ImportItem) error {
tx, err := s.DB.Begin()
if err != nil {
return err
}
defer tx.Rollback()
var status string
if err := tx.QueryRow(`SELECT status FROM import_sessions WHERE uuid=? AND domain_uuid=?`, sessionUUID, domainUUID).Scan(&status); err != nil {
return err
}
if status != "ready" && status != "failed" {
return errors.New("import plan is not editable")
}
for _, x := range items {
x.Quartier, x.Street, x.Spot = strings.TrimSpace(x.Quartier), strings.TrimSpace(x.Street), strings.TrimSpace(x.Spot)
if x.Street != "" && x.Quartier == "" {
return errors.New("Street requires a Quartier")
}
if x.Spot != "" && x.Street == "" {
return errors.New("Spot requires a Street")
}
if x.Spot == x.Street {
x.Spot = ""
}
for _, name := range []string{x.Quartier, x.Street, x.Spot} {
if name != "" {
if _, err := normalizeCityName(name); err != nil {
return err
}
}
}
if strings.TrimSpace(x.TargetPath) == "" || normalizePath(x.TargetPath) == "/" {
return errors.New("import target Path must identify a document")
}
var contentType string
if err := tx.QueryRow(`SELECT content_type FROM import_items WHERE session_uuid=? AND sequence=?`, sessionUUID, x.Sequence).Scan(&contentType); err != nil {
return err
}
target := normalizePath(x.TargetPath)
if contentType == "text/markdown" {
target = strings.TrimSuffix(strings.TrimSuffix(target, ".md"), ".markdown")
}
result, err := tx.Exec(`UPDATE import_items SET target_path=?,suggested_quartier=?,suggested_street=?,suggested_spot=?,selected=? WHERE session_uuid=? AND sequence=? AND NOT EXISTS(SELECT 1 FROM import_journal j WHERE j.session_uuid=import_items.session_uuid AND j.sequence=import_items.sequence AND j.completed=1)`, target, x.Quartier, x.Street, x.Spot, x.Selected, sessionUUID, x.Sequence)
if err != nil {
return err
}
if n, _ := result.RowsAffected(); n != 1 {
continue
}
}
_, err = tx.Exec(`UPDATE import_sessions SET updated_at_ms=? WHERE uuid=?`, time.Now().UnixMilli(), sessionUUID)
if err != nil {
return err
}
return tx.Commit()
}
func (s *Store) QueueImportCommit(domainUUID, sessionUUID string) error {
result, err := s.DB.Exec(`UPDATE import_sessions SET status='queued_import',error='',updated_at_ms=? WHERE uuid=? AND domain_uuid=? AND status IN ('ready','failed')`, time.Now().UnixMilli(), sessionUUID, domainUUID)
if err != nil {
return err
}
if n, _ := result.RowsAffected(); n != 1 {
return errors.New("import is not ready")
}
return nil
}
func (s *Store) MarkImportStatus(id, status string) {
_, _ = s.DB.Exec(`UPDATE import_sessions SET status=?,updated_at_ms=? WHERE uuid=?`, status, time.Now().UnixMilli(), id)
}
func (s *Store) AddImportJournal(sessionUUID string, sequence int, path, objectUUID string) error {
_, err := s.DB.Exec(`INSERT INTO import_journal(session_uuid,sequence,path,object_uuid,created_at_ms,completed) VALUES(?,?,?,?,?,0)`, sessionUUID, sequence, path, objectUUID, time.Now().UnixMilli())
return err
}
func (s *Store) CompleteImportJournal(sessionUUID string, sequence int) error {
_, err := s.DB.Exec(`UPDATE import_journal SET completed=1 WHERE session_uuid=? AND sequence=?`, sessionUUID, sequence)
return err
}
func (s *Store) AddImportCityJournal(sessionUUID, kind, nodeUUID string) error {
_, err := s.DB.Exec(`INSERT OR IGNORE INTO import_city_journal(session_uuid,kind,node_uuid,created_at_ms) VALUES(?,?,?,?)`, sessionUUID, kind, nodeUUID, time.Now().UnixMilli())
return err
}
func (s *Store) AddImportCityEdge(sessionUUID, spotUUID, pageUUID string) error {
_, err := s.DB.Exec(`INSERT OR IGNORE INTO import_city_edges(session_uuid,spot_uuid,page_uuid,created_at_ms) VALUES(?,?,?,?)`, sessionUUID, spotUUID, pageUUID, time.Now().UnixMilli())
return err
}
type ImportCityJournalEntry struct{ Kind, UUID string }
func (s *Store) ImportCityJournal(sessionUUID string) ([]ImportCityJournalEntry, error) {
rows, err := s.DB.Query(`SELECT kind,node_uuid FROM import_city_journal WHERE session_uuid=? ORDER BY CASE kind WHEN 'spot' THEN 1 WHEN 'street' THEN 2 ELSE 3 END`, sessionUUID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []ImportCityJournalEntry
for rows.Next() {
var x ImportCityJournalEntry
if err := rows.Scan(&x.Kind, &x.UUID); err != nil {
return nil, err
}
out = append(out, x)
}
return out, rows.Err()
}
type ImportCityEdge struct{ SpotUUID, PageUUID string }
func (s *Store) ImportCityEdges(sessionUUID string) ([]ImportCityEdge, error) {
rows, err := s.DB.Query(`SELECT spot_uuid,page_uuid FROM import_city_edges WHERE session_uuid=?`, sessionUUID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []ImportCityEdge
for rows.Next() {
var x ImportCityEdge
if err := rows.Scan(&x.SpotUUID, &x.PageUUID); err != nil {
return nil, err
}
out = append(out, x)
}
return out, rows.Err()
}
type ImportJournalEntry struct {
Sequence int
Path, ObjectUUID string
Completed bool
}
func (s *Store) ImportJournal(sessionUUID string) ([]ImportJournalEntry, error) {
rows, err := s.DB.Query(`SELECT sequence,path,object_uuid,completed FROM import_journal WHERE session_uuid=? ORDER BY sequence`, sessionUUID)
if err != nil {
return nil, err
}
defer rows.Close()
var out []ImportJournalEntry
for rows.Next() {
var x ImportJournalEntry
var completed int
if err := rows.Scan(&x.Sequence, &x.Path, &x.ObjectUUID, &completed); err != nil {
return nil, err
}
x.Completed = completed != 0
out = append(out, x)
}
return out, rows.Err()
}