Добавление новой страны в БД¶
Для добавления новой страны необходимо выполнить следующие шаги
Репозиторий для перевода страны и города на используемые языки и перевод их в Unicode
Сайт для конвертации jpg флагов
https://image.online-convert.com/convert-to-jpg
Сайт с флагами стран
https://www.flagistrany.ru/download/images
Картинка с флагом представляет собой JPG (150x100, YCbCr4:4:4) закодированный в base64
То есть флаг страны должен быть в разрешении 150х100 пикселей
Расширение картинки .jpg
Чтобы добавить картинку в БД нужно перевести её в base64 — то есть в строку
Для этого нужно открыть терминал в директории с флагами и выполнить
На выходе получим длинную строку, пример
/9j/4AAQSkZJRgABAQAAAQABAAD/4QAYRXhpZgAASUkqAAgAAAAAAAAAAAAAAP/hAxlodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvADw/eHBhY2tldCBiZWdpbj0i77u/IiBpZD0iVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkIj8+IDx4OnhtcG1ldGEgeG1sbnM6eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IkFkb2JlIFhNUCBDb3JlIDUuMy1jMDExIDY2LjE0NTY2MSwgMjAxMi8wMi8wNi0xNDo1NjoyNyAgICAgICAgIj4gPHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4gPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wTU09Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC9tbS8iIHhtbG5zOnN0UmVmPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvc1R5cGUvUmVzb3VyY2VSZWYjIiB4bWxuczp4bXA9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC8iIHhtcE1NOkRvY3VtZW50SUQ9InhtcC5kaWQ6OTQwOTZBQ0Y5NDA5MTFFODk1RDg4NkRGMkZBMTUzQUUiIHhtcE1NOkluc3RhbmNlSUQ9InhtcC5paWQ6OTQwOTZBQ0U5NDA5MTFFODk1RDg4NkRGMkZBMTUzQUUiIHhtcDpDcmVhdG9yVG9vbD0iQWRvYmUgUGhvdG9zaG9wIENTNiBXaW5kb3dzIj4gPHhtcE1NOkRlcml2ZWRGcm9tIHN0UmVmOmluc3RhbmNlSUQ9IjQ4RjE0RkRCQzAxQ0JFMzJCNUFBRTU0RENDNEVERUFGIiBzdFJlZjpkb2N1bWVudElEPSI0OEYxNEZEQkMwMUNCRTMyQjVBQUU1NERDQzRFREVBRiIvPiA8L3JkZjpEZXNjcmlwdGlvbj4gPC9yZGY6UkRGPiA8L3g6eG1wbWV0YT4gPD94cGFja2V0IGVuZD0iciI/Pv/bAEMAAwICAgICAwICAgMDAwMEBgQEBAQECAYGBQYJCAoKCQgJCQoMDwwKCw4LCQkNEQ0ODxAQERAKDBITEhATDxAQEP/bAEMBAwMDBAMECAQECBALCQsQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEP/AABEIAGQAlgMBEQACEQEDEQH/xAAbAAEBAQADAQEAAAAAAAAAAAAACQcBAggGA//EADcQAAEBAwkHAwIFBQAAAAAAAAABAgMEBggREhMxU5K0FBg3V5XR4xVxcgUHFyEyRGEkM0FRc//EAB0BAQADAQEBAQEBAAAAAAAAAAAGBwgJAQIDBQT/xAA8EQABAQIFFAIBBAIDAAAAAAAAAQITAxJRUpEEBQYHCBEXGDY3VFWCoqOys9Hi4zFxgxUhIjIWYiNjcv/aAAwDAQACEQMRAD8A8pGuChwAAAAAAl4BQ+ajwFkz7xmqeHLC6PzmVy/F0WDT1rzJyp9vnaNbKOJqZTOm4DSp+ELqXRdlzrnLrZ9wnRbIdZ/k5VOzzsk61vOqhl0AAAAAAAAAAAAAAAAAAAAAJeAUPmo8BZM+8Zqnhywuj85lcvxdFg09a8ycqfb52jWyjiamUzpuA0qfhC6l0XZc65y62fcJ0WyHWf5OVTs87JOtbzqoZdAAAAAAAAAAAAAAAAAAAAACXgFD5qPAWTPvGap4csLo/OZXL8XRYNPWvMnKn2+do1so4mplM6bgNKn4QupdF2XOucutn3CdFsh1n+TlU7POyTrW86qGXQAAAAAAAAAAAAAAAAAAAAAl4BQ+ajwFkz7xmqeHLC6PzmVy/F0WDT1rzJyp9vnaNbKOJqZTOm4DSp+ELqXRdlzrnLrZ9wnRbIdZ/k5VOzzsk61vOqhl0AAAAAAAAAAAAAH6bPEYD3IvY/J/BT0pTufbpuQbPEYD3IvYP4KelKdw6bkGzxGA9yL2D+CnpSncOm5Bs8RgPci9g/gp6Up3DpuQbPEYD3IvYP4KelKdw6bkCQ8RgPci9jx/BT0pTuHTchQyamy0x9hpNMtMqyqLGfkqUfunhy1ujmkatl1yVlb6f8XRYNOWvUVLHanRf9+do1oo8mhlU6VFa+w8qUZZVVqQ35IlP7l0XXc7NIzbKrYqre/eE6LZDrPkVbHapRP9edkncsPEU/2HuRex1PfwU9KU7mYXTcg2eIwHuRex6/gp6Up3PHTcg2eIwHuRewfwU9KU7h03INniMB7kXsH8FPSlO4dNyDZ4jAe5F7B/BT0pTuHTcg2eIwHuRewfwU9KU7h03INniMB7kXsH8FPSlO4dNyDZ4jAe5F7B/BT0pTuHTcg2eIwHuRewfwU9KU7h03INniMB7kXsH8FPSlO4dNyFZLJ3hsZUOI71uctKmzoqSCyd4bGVA9bnLSoipILJ3hsZUD1uctKiKkgsneGxlQPW5y0qIqSCyd4bGVA9bnLSoipILJ3hsZUD1uctKiKkh2REZShERE/hKD5VVaW+p78A8PQqIqUKiKn+lQ9RVRb6Hipf+TrZO8NjKh9PW5y0qeRUkFk7w2MqB63OWlRFSQWTvDYyoHrc5aVEVJBZO8NjKgetzlpURUkFk7w2MqB63OWlRFSQWTvDYyoHrc5aVEVJBZO8NjKgetzlpURUkFk7w2MqB63OWlRFSQWTvDYyoHrc5aVEVJBZO8NjKgetzlpURUkOx8H0AAAAAAAAAAAAAAAAAAAAAAAAAAAeS9/VnlavWPCbfxNl1zwPaUvhfTQ9/wARv6s8rV6x4RibLrnge0YX00Pf8Rv6s8rV6x4RibLrnge0YX00Pf8AEb+rPK1eseEYmy654HtGF9ND3/Eb+rPK1eseEYmy654HtGF9ND3/ABG/qzytXrHhGJsuueB7RhfTQ9/xPQ/2pl9+J0gvpkt/SvTvUbb+mtrWzs3rTH6qEppq03f5MsWyLDMH9k1U2OvnzmJ/OLFvxmGW/wCt9q9ejXvlfi+WdY9Xj9erdB1wiRI9/wDa/fvXlVPm8kkh9aQc/tHyn3Tl1+Gkg/qkt/S/UfTWXS7NbWVpXessfqoWiitTctxNrXVh3+fWS1LY6+cvla/nFjXorDTf9b7N+/FvfKfJ/Gsgrt+h1uha4RI8S9+1+9fvqifN5ZZDzvv6s8rV6x4TVOJsuueB7SsMLyaHv+Jxv6s8rV6x4RibLrnge0YX00Pf8Rv6s8rV6x4RibLrnge0YX00Pf8AEb+rPK1eseEYmy654HtGF9ND3/Eb+rPK1eseEYmy654HtGF9ND3/ABG/qzytXrHhGJsuueB7RhfTQ9/xG/qzytXrHhGJsuueB7RhfTQ9/wARv6s8rV6x4RibLrnge0YX00Pf8Rv6s8rV6x4RibLrnge0YX00Pf8AEb+rPK1eseEYmy654HtGF9ND3/E8km4SlAAAAAAAl4BQ+ajwFkz7xmqeHLC6PzmVy/F0WDT1rzJyp9vnaNbKOJqZTOm4DSp+ELqXRdlzrnLrZ9wnRbIdZ/k5VOzzsk61vOqhl0AAAAAAAAAAAAAAAAAAAAAJeAUPmo8BZM+8Zqnhywuj85lcvxdFg09a8ycqfb52jWyjiamUzpuA0qfhC6l0XZc65y62fcJ0WyHWf5OVTs87JOtbzqoZdAAAAAAAAAAAAAAAAAAAAACXgFD5qPAWTPvGap4csLo/OZXL8XRYNPWvMnKn2+do1so4mplM6bgNKn4QupdF2XOucutn3CdFsh1n+TlU7POyTrW86qGXQAAAAAAAAAAAAAAAAAAAAAl4BQ+ajwFkz7xmqeHLC6PzmVy/F0WDT1rzJyp9vnaNbKOJqZTOm4DSp+ELqXRdlzrnLrZ9wnRbIdZ/k5VOzzsk61vOqhl0AAAAAAAAAAAAAHNVD/O+U/d2gqoHyh2gqoHyh2gqoHyh2gqoHyh2hyjKUh8odoUNmpcBZMp/MZqnpy7ui2UbtlVxaX/q6LBpa18sWx2p9rnaNbKRdITKMplE6haPsLKr/nC6p0XTc8soxbJrYqSwnRhCIWerfsdqlP8Azzsk7lZSk6kpDKZmdocVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7Q//2Q==
Добавляем страну, если её еще нет
INSERT INTO country (id, code, name_key, picture)
VALUES (UUID_TO_BIN(UUID()), 'SE', 'sweden', '/9j/4AAQSkZJRgABAQAAAQABAAD/4QAYRXhpZgAASUkqAAgAAAAAAAAAAAAAAP/hAxlodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvADw/eHBhY2tldCBiZWdpbj0i77u/IiBpZD0iVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkIj8+IDx4OnhtcG1ldGEgeG1sbnM6eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IkFkb2JlIFhNUCBDb3JlIDUuMy1jMDExIDY2LjE0NTY2MSwgMjAxMi8wMi8wNi0xNDo1NjoyNyAgICAgICAgIj4gPHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4gPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wTU09Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC9tbS8iIHhtbG5zOnN0UmVmPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvc1R5cGUvUmVzb3VyY2VSZWYjIiB4bWxuczp4bXA9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC8iIHhtcE1NOkRvY3VtZW50SUQ9InhtcC5kaWQ6OTQwOTZBQ0Y5NDA5MTFFODk1RDg4NkRGMkZBMTUzQUUiIHhtcE1NOkluc3RhbmNlSUQ9InhtcC5paWQ6OTQwOTZBQ0U5NDA5MTFFODk1RDg4NkRGMkZBMTUzQUUiIHhtcDpDcmVhdG9yVG9vbD0iQWRvYmUgUGhvdG9zaG9wIENTNiBXaW5kb3dzIj4gPHhtcE1NOkRlcml2ZWRGcm9tIHN0UmVmOmluc3RhbmNlSUQ9IjQ4RjE0RkRCQzAxQ0JFMzJCNUFBRTU0RENDNEVERUFGIiBzdFJlZjpkb2N1bWVudElEPSI0OEYxNEZEQkMwMUNCRTMyQjVBQUU1NERDQzRFREVBRiIvPiA8L3JkZjpEZXNjcmlwdGlvbj4gPC9yZGY6UkRGPiA8L3g6eG1wbWV0YT4gPD94cGFja2V0IGVuZD0iciI/Pv/bAEMAAwICAgICAwICAgMDAwMEBgQEBAQECAYGBQYJCAoKCQgJCQoMDwwKCw4LCQkNEQ0ODxAQERAKDBITEhATDxAQEP/bAEMBAwMDBAMECAQECBALCQsQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEP/AABEIAGQAlgMBEQACEQEDEQH/xAAbAAEBAQADAQEAAAAAAAAAAAAACQcBAggGA//EADcQAAEBAwkHAwIFBQAAAAAAAAABAgMEBggREhMxU5K0FBg3V5XR4xVxcgUHFyEyRGEkM0FRc//EAB0BAQADAQEBAQEBAAAAAAAAAAAGBwgJAQIDBQT/xAA8EQABAQIFFAIBBAIDAAAAAAAAAQITAxJRUpEEBQYHCBEXGDY3VFWCoqOys9Hi4zFxgxUhIjIWYiNjcv/aAAwDAQACEQMRAD8A8pGuChwAAAAAAl4BQ+ajwFkz7xmqeHLC6PzmVy/F0WDT1rzJyp9vnaNbKOJqZTOm4DSp+ELqXRdlzrnLrZ9wnRbIdZ/k5VOzzsk61vOqhl0AAAAAAAAAAAAAAAAAAAAAJeAUPmo8BZM+8Zqnhywuj85lcvxdFg09a8ycqfb52jWyjiamUzpuA0qfhC6l0XZc65y62fcJ0WyHWf5OVTs87JOtbzqoZdAAAAAAAAAAAAAAAAAAAAACXgFD5qPAWTPvGap4csLo/OZXL8XRYNPWvMnKn2+do1so4mplM6bgNKn4QupdF2XOucutn3CdFsh1n+TlU7POyTrW86qGXQAAAAAAAAAAAAAAAAAAAAAl4BQ+ajwFkz7xmqeHLC6PzmVy/F0WDT1rzJyp9vnaNbKOJqZTOm4DSp+ELqXRdlzrnLrZ9wnRbIdZ/k5VOzzsk61vOqhl0AAAAAAAAAAAAAH6bPEYD3IvY/J/BT0pTufbpuQbPEYD3IvYP4KelKdw6bkGzxGA9yL2D+CnpSncOm5Bs8RgPci9g/gp6Up3DpuQbPEYD3IvYP4KelKdw6bkCQ8RgPci9jx/BT0pTuHTchQyamy0x9hpNMtMqyqLGfkqUfunhy1ujmkatl1yVlb6f8XRYNOWvUVLHanRf9+do1oo8mhlU6VFa+w8qUZZVVqQ35IlP7l0XXc7NIzbKrYqre/eE6LZDrPkVbHapRP9edkncsPEU/2HuRex1PfwU9KU7mYXTcg2eIwHuRex6/gp6Up3PHTcg2eIwHuRewfwU9KU7h03INniMB7kXsH8FPSlO4dNyDZ4jAe5F7B/BT0pTuHTcg2eIwHuRewfwU9KU7h03INniMB7kXsH8FPSlO4dNyDZ4jAe5F7B/BT0pTuHTcg2eIwHuRewfwU9KU7h03INniMB7kXsH8FPSlO4dNyFZLJ3hsZUOI71uctKmzoqSCyd4bGVA9bnLSoipILJ3hsZUD1uctKiKkgsneGxlQPW5y0qIqSCyd4bGVA9bnLSoipILJ3hsZUD1uctKiKkh2REZShERE/hKD5VVaW+p78A8PQqIqUKiKn+lQ9RVRb6Hipf+TrZO8NjKh9PW5y0qeRUkFk7w2MqB63OWlRFSQWTvDYyoHrc5aVEVJBZO8NjKgetzlpURUkFk7w2MqB63OWlRFSQWTvDYyoHrc5aVEVJBZO8NjKgetzlpURUkFk7w2MqB63OWlRFSQWTvDYyoHrc5aVEVJBZO8NjKgetzlpURUkOx8H0AAAAAAAAAAAAAAAAAAAAAAAAAAAeS9/VnlavWPCbfxNl1zwPaUvhfTQ9/wARv6s8rV6x4RibLrnge0YX00Pf8Rv6s8rV6x4RibLrnge0YX00Pf8AEb+rPK1eseEYmy654HtGF9ND3/Eb+rPK1eseEYmy654HtGF9ND3/ABG/qzytXrHhGJsuueB7RhfTQ9/xPQ/2pl9+J0gvpkt/SvTvUbb+mtrWzs3rTH6qEppq03f5MsWyLDMH9k1U2OvnzmJ/OLFvxmGW/wCt9q9ejXvlfi+WdY9Xj9erdB1wiRI9/wDa/fvXlVPm8kkh9aQc/tHyn3Tl1+Gkg/qkt/S/UfTWXS7NbWVpXessfqoWiitTctxNrXVh3+fWS1LY6+cvla/nFjXorDTf9b7N+/FvfKfJ/Gsgrt+h1uha4RI8S9+1+9fvqifN5ZZDzvv6s8rV6x4TVOJsuueB7SsMLyaHv+Jxv6s8rV6x4RibLrnge0YX00Pf8Rv6s8rV6x4RibLrnge0YX00Pf8AEb+rPK1eseEYmy654HtGF9ND3/Eb+rPK1eseEYmy654HtGF9ND3/ABG/qzytXrHhGJsuueB7RhfTQ9/xG/qzytXrHhGJsuueB7RhfTQ9/wARv6s8rV6x4RibLrnge0YX00Pf8Rv6s8rV6x4RibLrnge0YX00Pf8AEb+rPK1eseEYmy654HtGF9ND3/E8km4SlAAAAAAAl4BQ+ajwFkz7xmqeHLC6PzmVy/F0WDT1rzJyp9vnaNbKOJqZTOm4DSp+ELqXRdlzrnLrZ9wnRbIdZ/k5VOzzsk61vOqhl0AAAAAAAAAAAAAAAAAAAAAJeAUPmo8BZM+8Zqnhywuj85lcvxdFg09a8ycqfb52jWyjiamUzpuA0qfhC6l0XZc65y62fcJ0WyHWf5OVTs87JOtbzqoZdAAAAAAAAAAAAAAAAAAAAACXgFD5qPAWTPvGap4csLo/OZXL8XRYNPWvMnKn2+do1so4mplM6bgNKn4QupdF2XOucutn3CdFsh1n+TlU7POyTrW86qGXQAAAAAAAAAAAAAAAAAAAAAl4BQ+ajwFkz7xmqeHLC6PzmVy/F0WDT1rzJyp9vnaNbKOJqZTOm4DSp+ELqXRdlzrnLrZ9wnRbIdZ/k5VOzzsk61vOqhl0AAAAAAAAAAAAAHNVD/O+U/d2gqoHyh2gqoHyh2gqoHyh2gqoHyh2hyjKUh8odoUNmpcBZMp/MZqnpy7ui2UbtlVxaX/q6LBpa18sWx2p9rnaNbKRdITKMplE6haPsLKr/nC6p0XTc8soxbJrYqSwnRhCIWerfsdqlP8Azzsk7lZSk6kpDKZmdocVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7QVUD5Q7Q//2Q==')
Поле ID генерируется внутри БД
код страны должен соответствовать стандарту ISO 3166-1
https://en.wikipedia.org/wiki/ISO_3166-1
имя страны должно соответствовать ISO 3166-2
https://en.wikipedia.org/wiki/ISO_3166-2
Whitelist-страны
Для Whitelist-серверов (обход блокировок через российский proxy) процедура отличается.
Коды WL-стран начинаются с W (WA=Austria, WD=Germany, WN=Netherlands и т.д.).
Используется отдельная конвенция для name_key (whitelist_austria) и city (moscow_wla).
Подробности: Добавление Whitelist-сервера.
Добавляем город, если его еще нет
INSERT INTO city (id, name_key, country_id)
VALUES (UUID_TO_BIN(UUID()), 'stockholm', (SELECT id FROM country WHERE code = 'SE'))
Поле ID генерируется внутри БД
код страны должен соответствовать стандарту ISO 3166-1
имя города должно соответствовать ISO 3166-2