40 lines
1.4 KiB
PL/PgSQL
40 lines
1.4 KiB
PL/PgSQL
-- Internationalization: locale-specific catalog text (canonical English stays in main tables).
|
|
|
|
CREATE TABLE IF NOT EXISTS entity_translations (
|
|
id SERIAL PRIMARY KEY,
|
|
entity_type VARCHAR(32) NOT NULL,
|
|
entity_id INTEGER NOT NULL,
|
|
locale VARCHAR(10) NOT NULL,
|
|
field_name VARCHAR(64) NOT NULL,
|
|
value TEXT NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft',
|
|
source VARCHAR(120),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (entity_type, entity_id, locale, field_name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS entity_translations_lookup_idx
|
|
ON entity_translations (entity_type, entity_id, locale);
|
|
|
|
CREATE INDEX IF NOT EXISTS entity_translations_locale_field_idx
|
|
ON entity_translations (locale, field_name);
|
|
|
|
-- Search only matches name/title aliases; exclude long prose (bio_full) from btree index.
|
|
DROP INDEX IF EXISTS entity_translations_value_lower_idx;
|
|
CREATE INDEX IF NOT EXISTS entity_translations_search_alias_idx
|
|
ON entity_translations (locale, lower(value))
|
|
WHERE field_name IN ('name', 'title') AND char_length(value) <= 512;
|
|
|
|
CREATE OR REPLACE FUNCTION entity_translations_touch_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS entity_translations_updated_at ON entity_translations;
|
|
CREATE TRIGGER entity_translations_updated_at
|
|
BEFORE UPDATE ON entity_translations
|
|
FOR EACH ROW EXECUTE PROCEDURE entity_translations_touch_updated_at();
|