package mestionora import ( "context" "database/sql" "errors" "fmt" "sort" "strings" "time" ) // PostgreSQL's initial search profile is metadata-only. It deliberately does // not scan canonical files or the legacy body column to emulate SQLite FTS. // The caller owns the pool and must provision the native metadata schema. type PostgreSQL struct{ db *sql.DB } func NewPostgreSQL(db *sql.DB) *PostgreSQL { return &PostgreSQL{db: db} } const pgSearchMIME = `lower(trim(split_part(COALESCE(r.content_type,''),';',1)))` var pgSearchFileType = buildSearchFileTypeSQL(pgSearchMIME, func(suffixes ...string) string { clauses := make([]string, 0, len(suffixes)) for _, suffix := range suffixes { clauses = append(clauses, searchFilenameSQL+fmt.Sprintf(` LIKE '%%.%s'`, suffix)) } return "(" + strings.Join(clauses, " OR ") + ")" }, func(pattern string) string { // Only the package-owned MIME glob constants above reach this function. return pgSearchMIME + ` LIKE '` + strings.ReplaceAll(pattern, "*", "%") + `'` }) func (s *PostgreSQL) SearchPathsInDomainWithOptions(ctx context.Context, domainUUID, scope, query string, limit int, allowed func(string) bool, options SearchOptions) (found []SearchResult, err error) { if err := options.Validate(); err != nil { return nil, err } scope, query = normalizePath(scope), strings.TrimSpace(query) if allowed == nil || query == "" && !options.HasFilters() { return nil, nil } if len(query) > 512 || len(strings.Fields(query)) > 16 { return nil, errors.New("search query is too long") } if limit <= 0 || limit > 200 { limit = 100 } ctx, cancel := context.WithTimeout(ctx, 2*time.Second) defer cancel() defer func() { if ctx.Err() != nil { found, err = nil, ctx.Err() } }() var args []any bind := func(v any) string { args = append(args, v); return fmt.Sprintf("$%d", len(args)) } prefix := strings.TrimRight(scope, "/") + "/" upper := prefix[:len(prefix)-1] + "0" predicate := "p.domain_uuid=" + bind(domainUUID) + " AND (p.path=" + bind(scope) + " OR (p.path>=" + bind(prefix) + " AND p.path<" + bind(upper) + "))" if query != "" { q := bind(query) predicate += " AND (strpos(lower(p.path),lower(" + q + "))>0 OR strpos(lower(COALESCE(r.filename,'')),lower(" + q + "))>0)" } date := "r.created_at_ms" if options.DateField == "created" { date = "(SELECT o.created_at*1000 FROM objects o WHERE o.uuid=p.object_uuid)" } if options.FromMS != nil { predicate += " AND " + date + ">=" + bind(*options.FromMS) } if options.BeforeMS != nil { predicate += " AND " + date + "<" + bind(*options.BeforeMS) } if options.Extension != "" { ext := bind("." + options.Extension) predicate += " AND right(" + searchFilenameSQL + ",length(" + ext + "))=" + ext } if options.FileType != "" { predicate += " AND (" + pgSearchFileType + ")=" + bind(options.FileType) } rows, err := s.db.QueryContext(ctx, `SELECT p.path,COALESCE(r.content_type,''),COALESCE(r.filename,''),COALESCE(r.document_hash,''),COALESCE(r.uuid,''),COALESCE(r.created_at_ms/1000,0) FROM paths p LEFT JOIN LATERAL (SELECT uuid,content_type,filename,document_hash,created_at_ms FROM revisions WHERE object_uuid=p.object_uuid ORDER BY created_at_ms DESC,uuid DESC LIMIT 1) r ON true WHERE `+predicate+` ORDER BY p.path LIMIT `+bind(SearchCandidateLimit), args...) if err != nil { return nil, err } candidates := make([]SearchResult, 0) for rows.Next() { var result SearchResult if err = rows.Scan(&result.Path, &result.ContentType, &result.Filename, &result.DocumentHash, &result.RevisionUUID, &result.UpdatedAt); err != nil { rows.Close() return nil, err } result.PathMatch = query != "" && strings.Contains(strings.ToLower(result.Path), strings.ToLower(query)) result.FilenameMatch = query != "" && strings.Contains(strings.ToLower(result.Filename), strings.ToLower(query)) candidates = append(candidates, result) } err = rows.Err() rows.Close() if err != nil { return nil, err } // Release the cursor before policy callbacks: authorization may need the // same single-connection pool. Never return a denied Path or load its body. sort.Slice(candidates, func(i, j int) bool { if len(candidates[i].Path) != len(candidates[j].Path) { return len(candidates[i].Path) < len(candidates[j].Path) } return candidates[i].Path < candidates[j].Path }) for _, candidate := range candidates { if err = ctx.Err(); err != nil { return nil, err } if allowed(candidate.Path) { found = append(found, candidate) if len(found) == limit { break } } } return found, nil } func (s *PostgreSQL) SearchPathSuggestionsInDomain(ctx context.Context, domainUUID, scope, query string, limit int, allowed func(string) bool) ([]PathSuggestion, error) { results, err := s.SearchPathsInDomainWithOptions(ctx, domainUUID, scope, query, limit, allowed, SearchOptions{}) if err != nil { return nil, err } out := make([]PathSuggestion, 0, len(results)) for _, result := range results { out = append(out, PathSuggestion{SearchResult: result}) } return out, nil }