Настройка базы данных PostgreSQL

Полезное#

Видео:

• YouTube
• ВК
• Дзен

Вопросы, можно задать в комментариях телеграмма: TG: fitgame

Таблицы базы данных#

Структура users_social:

• id: integer айди (AUTO_INCREMENT)
• social_type: character varying тип площадки (vk/ok/yandex)
• social_id: character varying айди на площадке

Структура users:

• id: integer айди
• json_data: json основные данные в формате JSON
• points: integer Очки игрока, рейтинг и т.д.

Ключи и индексы#

Ключи и индексы в users_social:

• PRIMARY id (создан автоматически)
• UNIQUE social_type, social_id (для запросов по этим параметрам)

Ключи и индексы в users:

• PRIMARY id (для запросов по id)
• INDEX points (для запросов рейтинга)

Создание профиля#

Хранимая процедура для создание профиля игрока

CREATE OR REPLACE FUNCTION create_user(in_social json, in_user json)
RETURNS integer AS $$
DECLARE _user_id integer;
        in_inventory JSON;
BEGIN

  INSERT INTO users_social (social_type, social_id) VALUES(in_social->>'type',in_social->>'social_id') RETURNING id INTO _user_id;
  INSERT INTO users(id, json_data, points) VALUES(_user_id, (in_user->>'json_data')::json, ((in_user->>'json_data')::json->>'points')::int);

RETURN _user_id;

END;
$$ LANGUAGE plpgsql;

Запрос

select create_user('{"type": "vk", "social_id": 1}', '{"json_data": {"name": "FITGAME", "gold": 100, "energy": 50, "points": 100}}');

Получение профиля#

Хранимая процедура для получения айди на основе типа площадки и айди площадки

CREATE OR REPLACE FUNCTION get_social_info(in_social JSONB)
RETURNS integer AS $$
DECLARE _user_id integer = 0;
BEGIN

  SELECT id INTO _user_id FROM users_social WHERE social_type = (in_social->>'type') AND social_id = (in_social->>'social_id');

  IF _user_id IS NULL THEN
    _user_id = 0;
  END IF;

  RETURN _user_id;

END;
$$ LANGUAGE plpgsql STABLE;

Запрос

select get_social_info('{"type":"vk", "social_id": "1"}');

Хранимая процедура для получения данных профиля на основе айди

CREATE OR REPLACE FUNCTION get_user_info(in_user_id integer)
RETURNS JSONB AS $$
DECLARE out_json JSONB = '{}';
BEGIN

  SELECT row_to_json(row) INTO out_json FROM (SELECT * FROM users WHERE id = in_user_id) row;

  IF out_json IS NULL THEN
    out_json = '{}';
  END IF;

  RETURN out_json;

END;
$$ LANGUAGE plpgsql STABLE;

Запрос

select get_user_info(1);

Хранимая процедура для получения всех данных профиля на основе типа площадки и айди площадки

CREATE OR REPLACE FUNCTION get_user_info_2 (in_social JSONB)
RETURNS JSONB AS $$
DECLARE out_json JSONB = '{}';
BEGIN

  SELECT row_to_json(row) INTO out_json FROM (SELECT * FROM users JOIN users_social ON users_social.id = users.id WHERE social_type = (in_social->>'type') AND social_id = (in_social->>'social_id')) row;

  IF out_json IS NULL THEN
    out_json = '{}';
  END IF;

  RETURN out_json;

END;
$$ LANGUAGE plpgsql STABLE;

Запрос

select get_user_info_2('{"type":"vk", "social_id": "1"}');

Обновление профиля#

Хранимая процедура для обновления профиля игрока

CREATE OR REPLACE FUNCTION update_user(in_user_id integer, in_user JSONB)
RETURNS boolean AS $$
BEGIN

  UPDATE users SET 
    json_data =  (in_user->>'json_data')::json, points =  ((in_user->>'json_data')::json->>'points')::int
    WHERE id = in_user_id;
 

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Запрос

select update_user(1, '{"json_data": {"name": "FITGAME", "gold": 100, "energy": 50, "points": 300}}');

Топ лучших#

Хранимая процедура для обновления профиля игрока

CREATE OR REPLACE FUNCTION get_top(in_type text, in_val integer)
RETURNS JSONB AS $$
DECLARE out_json JSONB = '{}';
  json_list JSONB;
  place_num JSONB;
BEGIN

  IF in_type = 'points' THEN

    SELECT json_agg(row_to_json(t)) INTO json_list 
    FROM (
      SELECT json_data FROM users ORDER BY points DESC LIMIT 100
    ) t;

    SELECT count(1) INTO place_num FROM users WHERE points >= in_val;

  END IF;


  IF json_list IS NOT NULL THEN
    out_json = jsonb_set(out_json, '{list}', json_list, true);
  ELSE
    out_json = jsonb_set(out_json, '{list}', '[]', true);
  END IF;


  IF place_num IS NOT NULL THEN 
    out_json = jsonb_set(out_json, '{pos}', place_num, true);
  ELSE
    out_json = jsonb_set(out_json, '{pos}', -1, true);
  END IF;

  RETURN out_json;

END;
$$ LANGUAGE plpgsql STABLE;

Запрос

select get_top('points', 5);