S1 filet (backend + mobile) : corbeille 30j (deleted_at réutilisé, trash/restore récursifs, purge auto serveur), versions N=3 à chaque overwrite, ops outbox trash/restore/purge (delete legacy mappé), Room v19 (trashed_at), Deleters -> trash, écran Corbeille, walk guards. S2 extract (backend) : package extract (règles FR stdlib, corpus testé), resource_facts 000019 (origin auto/user), hook worker OCR + backfill boot, GET /files/:id/facts (viewer+). Contrat docs/api-v1.md étendu, V3.md (vision + plan).
192 lines
5.5 KiB
Go
192 lines
5.5 KiB
Go
package repository
|
|
|
|
import (
|
|
"database/sql"
|
|
"time"
|
|
)
|
|
|
|
// TrashedRow is a trashed resource for GET /trash.
|
|
type TrashedRow struct {
|
|
ID string
|
|
Type string
|
|
Name string
|
|
Size int64
|
|
TrashedAt time.Time
|
|
UpdatedAt time.Time
|
|
}
|
|
|
|
// subtreeCTE selects a resource and all its descendants (any depth).
|
|
const subtreeCTE = `
|
|
subtree(id) AS (
|
|
SELECT $2::text
|
|
UNION ALL
|
|
SELECT r.resource_id FROM resources r JOIN subtree s ON r.parent_id = s.id
|
|
)`
|
|
|
|
// ancestorsCTE selects a resource and all its ancestors up to the root.
|
|
const ancestorsCTE = `
|
|
ancestors(id) AS (
|
|
SELECT $2::text
|
|
UNION ALL
|
|
SELECT r.parent_id FROM resources r JOIN ancestors a ON r.resource_id = a.id
|
|
WHERE r.parent_id IS NOT NULL
|
|
)`
|
|
|
|
// TrashResource soft-deletes a resource and its whole subtree
|
|
// (`deleted_at = NOW()`), owner-scoped. Already-trashed rows are untouched.
|
|
// The target must be verified owned beforehand (ExistsOwner / GetResourceOwner)
|
|
// for the NOT_FOUND halt semantics — this method only applies.
|
|
func (r *Resources) TrashResource(ownerID, resourceID string) error {
|
|
_, err := r.DB.Exec(
|
|
`WITH RECURSIVE `+subtreeCTE+`
|
|
UPDATE resources SET deleted_at = NOW(), updated_at = NOW()
|
|
WHERE resource_id IN (SELECT id FROM subtree)
|
|
AND user_id = $1 AND deleted_at IS NULL`,
|
|
ownerID, resourceID,
|
|
)
|
|
return err
|
|
}
|
|
|
|
// RestoreResource clears the trash flag on a resource, its subtree AND its
|
|
// ancestor chain (a file whose folder is still trashed would stay
|
|
// unreachable otherwise). Independently-trashed descendants are restored too
|
|
// — documented V3 behaviour (restoring the folder restores what it contains).
|
|
func (r *Resources) RestoreResource(ownerID, resourceID string) error {
|
|
_, err := r.DB.Exec(
|
|
`WITH RECURSIVE `+subtreeCTE+`, `+ancestorsCTE+`
|
|
UPDATE resources SET deleted_at = NULL, updated_at = NOW()
|
|
WHERE (resource_id IN (SELECT id FROM subtree)
|
|
OR resource_id IN (SELECT id FROM ancestors))
|
|
AND user_id = $1 AND deleted_at IS NOT NULL`,
|
|
ownerID, resourceID,
|
|
)
|
|
return err
|
|
}
|
|
|
|
// ExistsOwnerAny reports ownership regardless of trash state (for purge :
|
|
// a trashed resource is still owned).
|
|
func (r *Resources) ExistsOwnerAny(ownerID, resourceID string) (bool, error) {
|
|
var exists int
|
|
err := r.DB.QueryRow(
|
|
`SELECT 1 FROM resources WHERE resource_id = $1 AND user_id = $2`,
|
|
resourceID, ownerID,
|
|
).Scan(&exists)
|
|
if err == sql.ErrNoRows {
|
|
return false, nil
|
|
}
|
|
return err == nil, err
|
|
}
|
|
|
|
// PurgeResource hard-deletes a resource and its subtree (FK CASCADE on
|
|
// parent_id + resource_versions), returning every purged resource_id so the
|
|
// caller can remove physical files best-effort. Owner-scoped.
|
|
func (r *Resources) PurgeResource(ownerID, resourceID string) ([]string, error) {
|
|
rows, err := r.DB.Query(
|
|
`WITH RECURSIVE subtree(id) AS (
|
|
SELECT $1::text
|
|
UNION ALL
|
|
SELECT r.resource_id FROM resources r JOIN subtree s ON r.parent_id = s.id
|
|
)
|
|
SELECT id FROM subtree`,
|
|
resourceID,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
var ids []string
|
|
for rows.Next() {
|
|
var id string
|
|
if err := rows.Scan(&id); err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
ids = append(ids, id)
|
|
}
|
|
rows.Close()
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
if _, err := r.DB.Exec(
|
|
`DELETE FROM resources WHERE resource_id = $1 AND user_id = $2`,
|
|
resourceID, ownerID,
|
|
); err != nil {
|
|
return nil, err
|
|
}
|
|
return ids, nil
|
|
}
|
|
|
|
// ListTrashed returns the owner's trashed resources, most recently trashed
|
|
// first, paginated.
|
|
func (r *Resources) ListTrashed(ownerID string, limit, offset int) ([]TrashedRow, int, error) {
|
|
var total int
|
|
if err := r.DB.QueryRow(
|
|
`SELECT COUNT(*) FROM resources WHERE user_id = $1 AND deleted_at IS NOT NULL`,
|
|
ownerID,
|
|
).Scan(&total); err != nil {
|
|
return nil, 0, err
|
|
}
|
|
rows, err := r.DB.Query(
|
|
`SELECT resource_id, type, name, COALESCE(size_bytes, 0), deleted_at, updated_at
|
|
FROM resources WHERE user_id = $1 AND deleted_at IS NOT NULL
|
|
ORDER BY deleted_at DESC LIMIT $2 OFFSET $3`,
|
|
ownerID, limit, offset,
|
|
)
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
defer rows.Close()
|
|
out := make([]TrashedRow, 0)
|
|
for rows.Next() {
|
|
var row TrashedRow
|
|
if err := rows.Scan(&row.ID, &row.Type, &row.Name, &row.Size, &row.TrashedAt, &row.UpdatedAt); err != nil {
|
|
return nil, 0, err
|
|
}
|
|
out = append(out, row)
|
|
}
|
|
return out, total, rows.Err()
|
|
}
|
|
|
|
// PurgedResource identifies a row destroyed by the retention pass.
|
|
type PurgedResource struct {
|
|
UserID string
|
|
ResourceID string
|
|
}
|
|
|
|
// PurgeExpiredTrash hard-deletes every resource trashed before cutoff
|
|
// (corbeille 30j), oldest first, bounded by limit. Returns the destroyed
|
|
// identities for physical-file cleanup (best-effort, caller-side).
|
|
func (r *Resources) PurgeExpiredTrash(cutoff time.Time, limit int) ([]PurgedResource, error) {
|
|
rows, err := r.DB.Query(
|
|
`SELECT user_id, resource_id FROM resources
|
|
WHERE deleted_at IS NOT NULL AND deleted_at < $1
|
|
ORDER BY deleted_at ASC LIMIT $2`,
|
|
cutoff, limit,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
var out []PurgedResource
|
|
for rows.Next() {
|
|
var p PurgedResource
|
|
if err := rows.Scan(&p.UserID, &p.ResourceID); err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
out = append(out, p)
|
|
}
|
|
rows.Close()
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
for _, p := range out {
|
|
// Hard delete cascades to children (parent_id FK) and versions.
|
|
if _, err := r.DB.Exec(
|
|
`DELETE FROM resources WHERE resource_id = $1 AND user_id = $2 AND deleted_at IS NOT NULL AND deleted_at < $3`,
|
|
p.ResourceID, p.UserID, cutoff,
|
|
); err != nil {
|
|
return out, err
|
|
}
|
|
}
|
|
return out, nil
|
|
}
|