Postgres functions that power the Crypto Map: map.nimiq.com

get_establishment_by_uuid.plpgsql

CREATE OR REPLACE FUNCTION public.get_establishment_by_uuid(uuid UUID)
RETURNS JSONB AS
$$
DECLARE
    result JSONB;
BEGIN
    SELECT
        json_build_object(
            'uuid', e.uuid,
            'name', e.name,
            'address', e.address,
            'lat', ST_Y(e.geo_location::geometry),
            'lng', ST_X(e.geo_location::geometry),
            'category', ec.label,
            'gmaps_types', e.gmaps_types,
            'place_id', e.gmaps_place_id,
            'rating', e.rating::numeric,
            'photo', e.photo,
            'providers', (
                SELECT
                    json_agg(
                        json_build_object(
                            'provider', p.name,
                            'buy', COALESCE(aggr.buy_symbols, '{}'),
                            'sell', COALESCE(aggr.sell_symbols, '{}')
                        )
                    )
                FROM
                    public.establishments_provider_currencies epc
                    LEFT JOIN public.providers p ON epc.provider_id = p.id
                    LEFT JOIN public.currencies c ON epc.currency_id = c.id
                    LEFT JOIN (
                        SELECT
                            epc.establishment_id,
                            epc.provider_id,
                            array_agg(DISTINCT c.symbol) FILTER (WHERE epc.buy = true) AS buy_symbols,
                            array_agg(DISTINCT c.symbol) FILTER (WHERE epc.sell = true) AS sell_symbols
                        FROM
                            public.establishments_provider_currencies epc
                            LEFT JOIN public.currencies c ON epc.currency_id = c.id
                        GROUP BY
                            epc.establishment_id, epc.provider_id
                    ) aggr ON epc.establishment_id = aggr.establishment_id AND epc.provider_id = aggr.provider_id
                WHERE
                    epc.establishment_id = e.id
            )
        ) INTO result
    FROM
        public.establishments e
        JOIN public.establishment_categories ec ON e.establishment_category_id = ec.id
    WHERE
        e.uuid = establishment_uuid;

    RETURN result;
END;
$$
LANGUAGE plpgsql;

get_establishments.plpgsql

CREATE OR REPLACE FUNCTION public.get_establishments(nelat double precision, nelng double precision, swlat double precision, swlng double precision)
RETURNS TABLE (
    uuid uuid,
    name character varying,
    geoLocation json,
    categoryId bigint,
    providersId bigint[]
) AS $$
BEGIN
    RETURN QUERY
    SELECT e.uuid, e.name,
           json_build_object('lat', ST_Y(e.geo_location::geometry), 'lng', ST_X(e.geo_location::geometry)) AS geoLocation,
           e.establishment_category_id as categoryId,
           array_agg(DISTINCT epc.provider_id) as providersId
    FROM public.establishments e
    INNER JOIN public.establishment_categories ec
    ON e.establishment_category_id = ec.id
    LEFT JOIN public.establishments_provider_currencies epc
    ON e.id = epc.establishment_id
    WHERE ST_Intersects(e.geo_location::geometry, ST_MakeEnvelope(swlng, swlat, nelng, nelat, 4326)::geometry)
    GROUP BY e.uuid, e.name, e.geo_location, e.establishment_category_id;
END;
$$ LANGUAGE plpgsql;

insert_establishment.plpgsql

CREATE OR REPLACE FUNCTION public.insert_establishment(
    p_place_id TEXT,
    p_provider TEXT,
    p_sell TEXT[],
    p_buy TEXT[]
)
RETURNS VOID AS
$$
<<main_block>>
DECLARE
  establishment_record public.establishments%ROWTYPE;
BEGIN
  establishment_record := upsert_establishment_by_place_id(p_place_id);
  PERFORM public.insert_establishment_currencies_and_providers(establishment_record.uuid, p_provider, p_sell, p_buy);
END;
$$
LANGUAGE 'plpgsql';

insert_establishment_currencies_and_providers.plpgsql

CREATE OR REPLACE FUNCTION public.insert_establishment_currencies_and_providers(
    p_uuid UUID,
    p_provider TEXT,
    p_sell TEXT[],
    p_buy TEXT[]
)
RETURNS VOID AS
$$
<<main_block>>
DECLARE
    new_establishment_id BIGINT;
    new_provider_id BIGINT;
    currency_id BIGINT;
    currency_symbol TEXT;
    sell_currencies TEXT[];
    buy_currencies TEXT[];
    i INT;
BEGIN
    -- Get the establishment ID, raise error if not exists
    SELECT id INTO new_establishment_id FROM public.establishments
    WHERE uuid = uuid;
    
    IF new_establishment_id IS NULL THEN
        RAISE EXCEPTION 'Establishmnet not found: %', uuid;
    END IF;

    -- Get the provider ID, raise error if not exists
    SELECT id INTO new_provider_id FROM public.providers
    WHERE name = p_provider;
    
    IF new_provider_id IS NULL THEN
        RAISE EXCEPTION 'Provider not found: %', p_provider;
    END IF;

    -- Extract sell and buy currencies
    sell_currencies := p_sell;
    buy_currencies := p_buy;

    -- Insert the sell currencies and link them with provider and establishment
    IF array_length(sell_currencies, 1) IS NOT NULL THEN
        FOR i IN 1 .. array_length(sell_currencies, 1)
        LOOP
            currency_symbol := sell_currencies[i];
            INSERT INTO public.currencies (symbol, name)
            VALUES (currency_symbol, currency_symbol)
            ON CONFLICT (symbol) DO UPDATE
                SET symbol = EXCLUDED.symbol
            RETURNING id INTO main_block.currency_id;

            INSERT INTO public.establishments_provider_currencies (establishment_id, provider_id, currency_id, sell)
            VALUES (new_establishment_id, new_provider_id, main_block.currency_id, true)
            ON CONFLICT (establishment_id, provider_id, currency_id) DO UPDATE
                SET sell = EXCLUDED.sell;
        END LOOP;
    END IF;

    -- Insert the buy currencies and link them with provider and establishment
    IF array_length(buy_currencies, 1) IS NOT NULL THEN
        FOR i IN 1 .. array_length(buy_currencies, 1)
        LOOP
            currency_symbol := buy_currencies[i];
            INSERT INTO public.currencies (symbol, name)
            VALUES (currency_symbol, currency_symbol)
            ON CONFLICT (symbol) DO UPDATE
                SET symbol = EXCLUDED.symbol
            RETURNING id INTO main_block.currency_id;

            INSERT INTO public.establishments_provider_currencies (establishment_id, provider_id, currency_id, buy)
            VALUES (new_establishment_id, new_provider_id, main_block.currency_id, true)
            ON CONFLICT (establishment_id, provider_id, currency_id) DO UPDATE
                SET buy = EXCLUDED.buy;
        END LOOP;
    END IF;

END;
$$
LANGUAGE 'plpgsql';

insert_raw_establishment

CREATE OR REPLACE FUNCTION public.insert_raw_establishment(
    p_uuid UUID,
    p_name TEXT,
    p_address TEXT,
    p_lng NUMERIC,
    p_lat NUMERIC,
    p_rating NUMERIC,
    p_photo TEXT,
    p_place_id TEXT,
    p_category TEXT,
    p_gmaps_types JSONB,
    p_instagram TEXT,
    p_facebook TEXT,
    p_provider TEXT,
    p_sell TEXT[],
    p_buy TEXT[]
)
RETURNS VOID AS
$$
<<main_block>>
DECLARE
    new_establishment_id BIGINT;
    new_provider_id BIGINT;
    new_category_id BIGINT;
    currency_id BIGINT;
    currency_symbol TEXT;
    sell_currencies TEXT[];
    buy_currencies TEXT[];
    i INT;
BEGIN

    -- Get the category ID, raise error if not exists
    SELECT id INTO new_category_id FROM public.establishment_categories
    WHERE label = p_category;
    
    IF new_category_id IS NULL THEN
        RAISE EXCEPTION 'Category not found: %', p_category;
    END IF;

    -- Insert the establishment
    INSERT INTO public.establishments (
        uuid, name, address, geo_location, rating, photo,
        gmaps_place_id, establishment_category_id, gmaps_types, instagram, facebook
    )
    VALUES (
        p_uuid,
        p_name,
        p_address,
        ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography(Point),
        p_rating,
        p_photo,
        p_place_id,
        new_category_id,
        p_gmaps_types,
        p_instagram,
        p_facebook
    )
    RETURNING id INTO new_establishment_id;

    PERFORM public.insert_establishment_currencies_and_providers(p_uuid, p_provider, p_sell, p_buy);

END;
$$
LANGUAGE 'plpgsql';

migration-v1-to-v2.plpgsql

-- Creating the providers table
CREATE TABLE providers (
    id serial PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Inserting the default, Bluecode, and Kurant providers
INSERT INTO providers (name) VALUES ('DEFAULT'), ('Bluecode'), ('Kurant');

-- Creating the establishments_provider_currencies table
CREATE TABLE establishments_provider_currencies (
    id serial PRIMARY KEY,
    establishment_id INT NOT NULL,
    provider_id INT NOT NULL,
    currency_id INT NOT NULL,
    sell BOOLEAN DEFAULT false,
    buy BOOLEAN DEFAULT false,
    FOREIGN KEY(establishment_id) REFERENCES establishments(id),
    FOREIGN KEY(provider_id) REFERENCES providers(id),
    FOREIGN KEY(currency_id) REFERENCES currencies(id)
);

-- Add a UNIQUE constraint across the three columns
ALTER TABLE establishments_provider_currencies
ADD CONSTRAINT unique_establishment_provider_currency UNIQUE (establishment_id, provider_id, currency_id);

-- Migrating data from currency_establishment to establishments_provider_currencies
INSERT INTO establishments_provider_currencies (establishment_id, provider_id, currency_id, sell, buy)
SELECT
    currency_establishment.establishment_id,
    CASE
        WHEN currencies.symbol = 'bluecode' THEN (SELECT id FROM providers WHERE name = 'Bluecode')
        WHEN currencies.symbol = 'atm' THEN (SELECT id FROM providers WHERE name = 'Kurant')
        ELSE (SELECT id FROM providers WHERE name = 'DEFAULT')
    END,
    currency_establishment.currency_id,
    false,
    true
FROM
    currency_establishment
JOIN
    currencies ON currency_establishment.currency_id = currencies.id
WHERE
    currencies.symbol NOT IN ('bluecode', 'atm');

-- Deleting references in currency_establishment_candidate for bluecode and atm
DELETE FROM currency_establishment_candidate WHERE currency_id IN (
    SELECT id FROM currencies WHERE symbol IN ('bluecode', 'atm')
);

-- Deleting references in currency_establishment for bluecode and atm
DELETE FROM currency_establishment WHERE currency_id IN (
    SELECT id FROM currencies WHERE symbol IN ('bluecode', 'atm')
);

-- Removing the bluecode and atm rows from the currencies table
DELETE FROM currencies WHERE symbol IN ('bluecode', 'atm');

-- Dropping the currency_establishment table
DROP TABLE currency_establishment;

-- Altering the establishments table
ALTER TABLE establishments
    DROP COLUMN user_id,
    DROP COLUMN gmaps_url,
    DROP COLUMN source,
    DROP COLUMN source_id,
    DROP COLUMN updated_at,
    DROP COLUMN created_at,
    ADD COLUMN instagram VARCHAR(255),
    ADD COLUMN facebook VARCHAR(255);

alter table establishments
alter column gmaps_place_id
drop not null;

-- Applying additional changes to the establishments table
ALTER TABLE establishments ALTER COLUMN gmaps_place_id DROP NOT NULL; -- make gmaps_place_id nullable
ALTER TABLE establishments DROP COLUMN gmaps_type; -- drop gmaps_type column
ALTER TABLE establishments ADD COLUMN gmaps_types VARCHAR(255)[] NULL; -- add gmaps_types column
ALTER TABLE establishments DROP COLUMN enabled; -- drop enabled column

-- Altering the currencies table
ALTER TABLE currencies
    DROP COLUMN created_at,
    DROP COLUMN updated_at;

-- Creating unique indices on the establishments table
CREATE UNIQUE INDEX idx_gmaps_place_id ON establishments(gmaps_place_id);
CREATE UNIQUE INDEX idx_instagram ON establishments(instagram);
CREATE UNIQUE INDEX idx_facebook ON establishments(facebook);

-- Dropping additional tables
DROP TABLE IF EXISTS currency_establishment_candidate;
DROP TABLE IF EXISTS establishment_candidates;
DROP TABLE IF EXISTS failed_jobs;
DROP TABLE IF EXISTS issues;
DROP TABLE IF EXISTS issue_categories;
DROP TABLE IF EXISTS migrations;
DROP TABLE IF EXISTS password_resets;
DROP TABLE IF EXISTS personal_access_tokens;
DROP TABLE IF EXISTS users;

-- Add a new row to the currencies table with symbol LBTC and name Lighting Bitcoin
INSERT INTO
  currencies (symbol, name)
VALUES
  ('LBTC', 'Lighting Bitcoin');
  
ALTER TABLE public.currencies
ADD CONSTRAINT unique_currency_symbol UNIQUE (symbol);

ALTER TABLE establishment RENAME COLUMN photo_reference TO photo;

parse_google_types.plpgsql

CREATE OR REPLACE FUNCTION parse_google_types(google_types jsonb) RETURNS TEXT AS $$
DECLARE
  nimiq_types JSONB := '{
    "cash": ["atm", "bank", "currency_exchange", "finance", "insurance_agency", "lawyer", "money_transfer", "travel_agency"],
    "cars_bikes": ["car_dealer", "car_rental", "car_repair", "car_wash", "gas_station", "parking", "taxi_stand", "train_station", "transit_station"],
    "computer_electronics": ["hardware_store", "locksmith", "moving_company", "painter", "plumber", "roofing_contractor"],
    "entertainment": ["amusement_park", "aquarium", "art_gallery", "bowling_alley", "casino", "movie_theater", "night_club", "stadium", "zoo"],
    "leisure_activities": ["beauty_salon", "bicycle_store", "campground", "laundry", "library", "movie_rental", "museum"],
    "food_drinks": ["bakery", "cafe", "food"],
    "restaurant_bar": ["bar", "meal_delivery", "meal_takeaway", "restaurant"],
    "health_beauty": ["dentist", "doctor", "drugstore", "hair_care", "hospital", "pharmacy", "physiotherapist", "spa", "veterinary_care"],
    "sports_fitness": ["gym", "park"],
    "hotel_lodging": ["lodging", "rv_park"],
    "shop": ["book_store", "clothing_store", "convenience_store", "department_store", "electronics_store", "florist", "furniture_store", "home_goods_store", "jewelry_store", "liquor_store", "pet_store", "shoe_store", "shopping_mall", "store", "supermarket"],
    "miscellaneous": ["accounting", "airport", "bus_station", "cemetery", "church", "city_hall", "courthouse", "electrician", "embassy", "fire_station", "funeral_home", "hindu_temple", "light_rail_station", "local_government_office", "mosque", "police", "post_office", "primary_school", "real_estate_agency", "school", "secondary_school", "storage", "subway_station", "synagogue", "tourist_attraction", "university"]
  }';
  category TEXT;
  type TEXT;
BEGIN
  FOR type IN SELECT * FROM jsonb_array_elements_text(google_types)
  LOOP
    FOR category IN SELECT * FROM jsonb_object_keys(nimiq_types)
    LOOP
      IF (nimiq_types -> category) ? type THEN
        RETURN category;
      END IF;
    END LOOP;
  END LOOP;

  RETURN 'miscellaneous';
END;
$$ LANGUAGE plpgsql;


-- SELECT parse_google_types('["hardware_store"]'::jsonb) AS result;

upsert_establishment_by_place_id.plpgsql

CREATE OR REPLACE FUNCTION upsert_establishment_by_place_id(place_id TEXT)
RETURNS public.establishments
AS $$
DECLARE
  raw_response_content TEXT;
  gmapsApiKey TEXT = 'YOUR_KEY';
  apiUrl TEXT = 'https://maps.googleapis.com/maps/api/place/details/json?place_id=' || place_id || '&key=' || gmapsApiKey || '&fields=name,formatted_address,geometry,rating,photos,types';
  response RECORD;
  response_content JSONB;
  category TEXT;
  category_id BIGINT;
  local_geo_location GEOGRAPHY;
  local_photo_reference TEXT;
  lng text;
  lat text;
  list_gmaps_types jsonb[];
  gmaps_types_item text;
  result public.establishments%ROWTYPE;
BEGIN
  SELECT status, content::jsonb
  INTO response
  FROM http_get(apiUrl);

  IF response.status != 200 THEN
    RAISE EXCEPTION 'HTTP request failed with status %', response.status;
  END IF;

  raw_response_content := response.content;
  response_content := raw_response_content::jsonb;

  lng := (response_content->'result'->'geometry'->'location'->'lng')::text;
  lat := (response_content->'result'->'geometry'->'location'->'lat')::text;
  local_geo_location := ST_GeomFromText('POINT(' || lng || ' ' || lat || ')');
  category := parse_google_types(response_content->'result'->'types');
  category_id := (SELECT establishment_categories.id FROM establishment_categories WHERE label = category);
  local_photo_reference := response_content->'result'->'photos'->0->>'photo_reference';
  
  FOR gmaps_types_item IN SELECT jsonb_array_elements_text(response_content->'result'->'types')
  LOOP
    list_gmaps_types := array_append(list_gmaps_types, to_jsonb(gmaps_types_item));
  END LOOP;

  IF NOT EXISTS (SELECT 1 FROM public.establishments WHERE gmaps_place_id = place_id) THEN
    INSERT INTO public.establishments (name, address, geo_location, rating, photo_reference, gmaps_place_id, establishment_category_id, gmaps_types, uuid)
    VALUES (
        response_content->'result'->>'name',
        response_content->'result'->>'formatted_address',
        local_geo_location,
        response_content->'result'->>'rating',
        local_photo_reference,
        place_id,
        category_id,
        list_gmaps_types,
        uuid_generate_v4()
    )
    RETURNING * INTO result;
  ELSE
      UPDATE public.establishments
      SET name = response_content->'result'->>'name',
          address = response_content->'result'->>'formatted_address',
          geo_location = local_geo_location,
          rating = response_content->'result'->>'rating',
          photo_reference = local_photo_reference,
          establishment_category_id = category_id,
          gmaps_types = list_gmaps_types
      WHERE gmaps_place_id = place_id
      RETURNING * INTO result;
  END IF;

  RETURN result;
END;
$$ LANGUAGE plpgsql;


-- Example usage:
SELECT * FROM upsert_establishment_by_place_id('ChIJ6206MDWvEmsRLfWRuAXpIPc');