Whitelist — SQL-запросы¶

SQL-запросы для работы с whitelist серверами в MySQL.

Database: MySQL 10.99.87.62 (vpn) Подключение: см. Подключение к production БД Учётные данные: Vaultwarden → Infrastructure → MySQL

Инвентарь¶

Все WL страны¶

SELECT HEX(id), code, name_key
FROM country WHERE code LIKE 'W%'
ORDER BY code;

Серверов по WL странам¶

SELECT c.code, COUNT(s.id) as server_count,
  GROUP_CONCAT(DISTINCT s.change_ip ORDER BY s.change_ip SEPARATOR ', ') as proxy_ips
FROM country c
JOIN city ct ON ct.country_id = c.id
JOIN server s ON s.city_id = ct.id
WHERE c.code LIKE 'W%'
GROUP BY c.code ORDER BY c.code;

Все WL proxy IP¶

SELECT DISTINCT change_ip FROM server
WHERE panel_type='WHITELIST' AND change_ip IS NOT NULL AND change_ip != ''
ORDER BY change_ip;

Порты каждого прокси¶

SELECT change_ip,
  GROUP_CONCAT(DISTINCT port ORDER BY port SEPARATOR ', ') as ports,
  COUNT(DISTINCT port) as port_count
FROM server WHERE panel_type='WHITELIST'
GROUP BY change_ip ORDER BY change_ip;

Поиск¶

Серверы конкретной WL страны¶

SELECT HEX(s.id), s.name, s.ip, s.port, s.change_ip, s.is_active
FROM server s
JOIN city ct ON s.city_id = ct.id
JOIN country c ON ct.country_id = c.id
WHERE c.code='WA'   -- WA=Austria, WDE=Germany, WNL=Netherlands
ORDER BY s.change_ip, s.port;

Серверы на proxy IP¶

SELECT HEX(s.id), s.name, s.ip, s.port, ct.name_key
FROM server s
JOIN city ct ON s.city_id = ct.id
WHERE s.change_ip='90.156.218.106'
ORDER BY s.port;

Дубликаты портов на прокси¶

-- Должен вернуть 0 строк если данные корректны
SELECT change_ip, port, COUNT(*) as cnt
FROM server WHERE panel_type='WHITELIST'
GROUP BY change_ip, port HAVING cnt > 1;

Валидация¶

Целостность связей¶

-- Города с несуществующими странами
SELECT HEX(id), name_key FROM city
WHERE country_id NOT IN (SELECT id FROM country);

-- Серверы с несуществующими городами
SELECT HEX(id), name FROM server
WHERE city_id NOT IN (SELECT id FROM city);

Дубликаты¶

-- Дублирующиеся коды стран
SELECT code, COUNT(*) FROM country GROUP BY code HAVING COUNT(*) > 1;

-- Дублирующиеся имена городов
SELECT name_key, COUNT(*) FROM city GROUP BY name_key HAVING COUNT(*) > 1;

Неактивные WL серверы¶

SELECT s.name, s.change_ip, s.port, s.is_active
FROM server s WHERE s.panel_type='WHITELIST' AND s.is_active=0
ORDER BY s.change_ip, s.port;

Конфигурация¶

Полный конфиг WL страны¶

SELECT c.code, c.name_key, ct.name_key as city,
  s.name, s.ip, s.port, s.change_ip, s.is_active
FROM country c
JOIN city ct ON ct.country_id = c.id
JOIN server s ON s.city_id = ct.id
WHERE c.code='WA'
ORDER BY s.change_ip, s.port;

Экспорт для деплоя¶

SELECT CONCAT_WS(',', s.change_ip, s.port, s.ip) as proxy_config
FROM server s WHERE s.panel_type='WHITELIST' AND s.is_active=1
ORDER BY s.change_ip, s.port;

Формат: proxy_ip,listen_port,upstream_ip

Изменение данных¶

⚠️ L3 — только по согласованию с DevOps Lead

Активация/деактивация WL страны¶

UPDATE server s SET s.is_active=0
WHERE s.city_id IN (
  SELECT ct.id FROM city ct
  WHERE ct.country_id=(SELECT id FROM country WHERE code='WDN')
);

Смена proxy IP для страны¶

UPDATE server s SET s.change_ip='NEW_PROXY_IP'
WHERE s.city_id IN (
  SELECT ct.id FROM city ct
  WHERE ct.country_id=(SELECT id FROM country WHERE code='WDN')
)
AND s.change_ip='OLD_PROXY_IP';

См. также: Whitelist — структура БД · Добавление Whitelist-сервера · Подключение к production БД