functions.sql

-- When support for new languages is added, this file needs to be updated to reflect the newly added language

-- Find the best suitable language for an entry
-- Returns the preferred input if it is present as a key, otherwise falls back through the list to different values
CREATE OR REPLACE FUNCTION find_language(store hstore, preferred varchar) RETURNS varchar AS
$$
SELECT CASE
           WHEN store ? preferred THEN preferred
           WHEN store ? 'en' THEN 'en'
           WHEN store ? 'de' THEN 'de'
           END
$$
    LANGUAGE SQL IMMUTABLE
                 STRICT;

-- Find the actual translations for an entry
-- Returns the preferred language if it is present as a key, otherwise falls back through the list to different values
CREATE OR REPLACE FUNCTION find_translations(store hstore, preferred varchar) RETURNS varchar AS
$$
SELECT COALESCE(store -> preferred, store -> 'en', store -> 'de')
$$
    LANGUAGE SQL IMMUTABLE
                 STRICT;

-- Search for a search term in a text indexed column
-- This should be used for long data like descriptions or instructions
CREATE OR REPLACE FUNCTION match_text_index(store hstore, query text) RETURNS BOOLEAN AS
$$
SELECT to_tsvector('english', store -> 'en') @@ plainto_tsquery('english', query) OR
       to_tsvector('german', store -> 'en') @@ plainto_tsquery('german', query)
$$
    LANGUAGE SQL;

-- Search for a search term in a trigram indexed column
-- This should be used for short data like tags or names
CREATE OR REPLACE FUNCTION match_trigram_index(store hstore, query text) RETURNS BOOLEAN AS
$$
SELECT store -> 'en' ILIKE query OR store -> 'de' ILIKE query
$$
    LANGUAGE SQL;

-- Create text indexes for columns with text
CREATE INDEX IF NOT EXISTS ingredient_description_index_en
    ON ingredient USING GIN (to_tsvector('english', description -> 'en'));
CREATE INDEX IF NOT EXISTS ingredient_description_index_de
    ON ingredient USING GIN (to_tsvector('german', description -> 'de'));

-- Create trigram indexes for word columns
CREATE INDEX IF NOT EXISTS ingredient_name_index_en
    ON ingredient USING GIN ((name -> 'en') gin_trgm_ops);
CREATE INDEX IF NOT EXISTS ingredient_name_index_de
    ON ingredient USING GIN ((name -> 'de') gin_trgm_ops);
CREATE INDEX IF NOT EXISTS ingredient_type_name_index_en
    ON ingredient_type USING GIN ((name -> 'en') gin_trgm_ops);
CREATE INDEX IF NOT EXISTS ingredient_type_name_index_de
    ON ingredient_type USING GIN ((name -> 'de') gin_trgm_ops);

schema.sql

CREATE EXTENSION hstore;
CREATE EXTENSION pg_trgm;

CREATE TABLE ingredient_type
(
    id   BIGSERIAL PRIMARY KEY,
    name HSTORE NOT NULL
);

CREATE TABLE ingredient
(
    id                 BIGSERIAL PRIMARY KEY,
    ingredient_type_id BIGINT NOT NULL REFERENCES ingredient_type (id),
    name               HSTORE NOT NULL,
    description        HSTORE NOT NULL
);
ALTER TABLE ingredient
    ADD CONSTRAINT ingredient_consistency_check CHECK (
        akeys(name) = akeys(description)
        ),
    ADD CONSTRAINT ingredient_non_empty_check CHECK (
        name <> '' AND description <> ''
        );

INSERT INTO ingredient_type (id, name)
VALUES (1, '"en" => "Hard Liquor","de" => "Starker Alkohol"'),
       (2, '"en" => "Juice","de" => "Fruchtsaft"'),
       (3, '"en" => "Syrup","de" => "Sirup"'),
       (4, '"en" => "Ice","de" => "Eis"');
ALTER SEQUENCE ingredient_type_id_seq RESTART WITH 5;
INSERT INTO ingredient (id, ingredient_type_id, name, description)
VALUES (1, 1, '"en" => "White rum", "de" => "Weißer Rum"',
        '"en" => "A high proof spirit made from sugar cane", "de" => "Ein hochprozentiges Destillat aus Zuckerrohr"'),
       (2, 2, '"en" => "Lime juice", "de" => "Limettensaft"',
        '"en" => "A very sour citrus juice", "de" => "Ein sehr saurer Zitrussaft"'),
       (3, 3, '"en" => "Simple syrup", "de" => "Zuckersirup"',
        '"en" => "A syrup made from water and sugar with a ratio of 1:1 by weight", "de" => "Ein Sirup aus Zucker und Wasser im Gewichtsverhältnis 1:1"'),
       (4, 4, '"en" => "Ice cubes", "de" => "Eiswürfel"',
        '"en" => "Ice in cube shape", "de" => "Eis in Würfelform"');
ALTER SEQUENCE ingredient_id_seq RESTART WITH 5;