Claude VM
a6c03a091e
Moved from gov-agreg/src/pages/achizitii/* to root (drop prefix). - 22 pages migrated, 127 files total - All internal links: /achizitii/X → /X (176 occurrences fixed) - AchizitiiLayout subnav rewritten: /X paths, top-right link to vreaudigital.ro hub - BaseLayout new (vreau.digital branding, OG tags, site URL) - astro.config.mjs: site https://vreau.digital, server output (was static) - docker-compose: port 5096 (vreaudigital is 5095), container vreau-digital - deploy.sh: paths /opt/vreau-digital, log /var/log/vreau-digital-deploy.log Backend shared with gov-agreg: - PostgreSQL satra (same schemas: seap, firms, anaf, anre, ...) - Photon, Martin tiles - Infisical /vreaudigital path (DATABASE_URL etc. shared) build: PASS (npx astro check 0 errors, npm run build 5s vite + 10s server)
59 lines
2.8 KiB
PL/PgSQL
59 lines
2.8 KiB
PL/PgSQL
-- Full-text search infrastructure for /api/cauta and /achizitii/cauta.
|
|
-- Uses 'simple' config + unaccent for diacritic-insensitive matching, since RO
|
|
-- doesn't have a built-in PG text search config and we don't want stemming bias.
|
|
--
|
|
-- Idempotent: safe to re-run.
|
|
|
|
BEGIN;
|
|
|
|
-- Ensure unaccent extension
|
|
CREATE EXTENSION IF NOT EXISTS unaccent;
|
|
|
|
-- Wrap unaccent as IMMUTABLE so it can be used in expression indexes / generated cols.
|
|
-- Safe because we don't reload the unaccent dictionary at runtime.
|
|
CREATE OR REPLACE FUNCTION seap.immutable_unaccent(text) RETURNS text
|
|
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
|
|
AS $$ SELECT public.unaccent('public.unaccent', $1) $$;
|
|
|
|
-- Plain (non-generated) tsvector column populated by trigger.
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS search_tsv tsvector;
|
|
|
|
CREATE OR REPLACE FUNCTION seap.update_search_tsv() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
NEW.search_tsv :=
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(NEW.title, ''))), 'A') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(NEW.description, ''))), 'B') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(NEW.authority_name, ''))), 'C') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(NEW.supplier_name, ''))), 'C') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(NEW.cpv_name_ro, ''))), 'D') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(NEW.cpv_name, ''))), 'D');
|
|
RETURN NEW;
|
|
END $$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_announcements_search_tsv ON seap.announcements;
|
|
CREATE TRIGGER trg_announcements_search_tsv
|
|
BEFORE INSERT OR UPDATE OF title, description, authority_name, supplier_name, cpv_name_ro, cpv_name
|
|
ON seap.announcements
|
|
FOR EACH ROW EXECUTE FUNCTION seap.update_search_tsv();
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ann_search_tsv ON seap.announcements USING gin(search_tsv);
|
|
|
|
-- Title-only trgm for "starts-with" or substring autocompletes
|
|
CREATE INDEX IF NOT EXISTS idx_ann_title_trgm
|
|
ON seap.announcements USING gin(title gin_trgm_ops);
|
|
|
|
COMMIT;
|
|
|
|
-- Backfill existing rows (run outside the transaction). Long-running on 642K
|
|
-- rows but does NOT block reads.
|
|
UPDATE seap.announcements
|
|
SET search_tsv =
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(title, ''))), 'A') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(description, ''))), 'B') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(authority_name, ''))), 'C') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(supplier_name, ''))), 'C') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(cpv_name_ro, ''))), 'D') ||
|
|
setweight(to_tsvector('simple', seap.immutable_unaccent(coalesce(cpv_name, ''))), 'D')
|
|
WHERE search_tsv IS NULL;
|