Архив рубрики: базы данных

Забывчивый верстак

MySQL Workbench — графический клиент к популярной СУБД MySQL — штука хорошая. Я, пожалуй, поставил бы его на второе место среди известных мне клиентов, но в связи с тем, что самый с моей точки зрения лучший клиент — EMS Studio for MySQL — сейчас выпускается только под винду, приходится использовать всё-таки верстак воркбенч.

Среди нужных мне функций — сохранение паролей к базам данных и SSH-туннелям до них: достаточно ввести один раз, поставить галочку, что пароль должен быть сохранён, и всё — пароль сохранится в Gnome Keyring. Точнее, сохранялся в предыдущих версиях, а в 6.2 эта штука сломалась. Пишут, что в версиях 6.2.5 и 6.3.0 проблема устранена, но у меня установлена более древняя версия.

Проблема решается путём присваивания переменной окружения GNOME_KEYRING_CONTROL значения 1 — можно сделать это прямо в файле /usr/bin/mysql-workbench

#!/bin/bash

# Uncomment the following line if you're having trouble with gnome-keyring lockups.
# This will cause passwords to be stored only temporarily for the session.
#WB_NO_GNOME_KEYRING=1
export GNOME_KEYRING_CONTROL=1

/usr/bin/workbench

После этого MySQL Workbench пароли всё-таки запоминает.

А воз и ныне там

В Open Journal Systems данные людей — пользователей и авторов — могут быть только на одном языке. В багтрекере уже шесть лет висит bug 5598 — allow for author names in multiple languages. В последнем комментарии разработчик честно сообщает:

This hasn’t yet been prioritized for a specific release, but I’d say it’s very unlikely to be implemented for OJS 2.x; it’ll become a higher priority after OJS 3.0 is released.

Итак, OJS 3.0.0 вышла — и действительно, ничего не поменялось: раз эта задача не была приоритетной, то и многоязычности нет.

SQL-запрос

Значит, придётся и дальше поддерживать свой хак, пытаясь всё-таки сделать из него нормальный плагин.

Справился с ошибкой svn E155010

Решил как-то добавить древние файлы в систему контроля версий, да не смог:

$ svn st | grep -e '^\?' | cut -c9-999 | xargs svn add
A         noframe.html.en
svn: E000013: Can't create temporary file from template '/usr/local/www/path/to/svn-XXXXXX': Permission denied

Оказалось, не имел права писать во временный каталог Subversion .svn/tmp где-то выше. Изменил права, повторяю попытку добавления — фиг:

$ svn st | grep -e '^\?' | cut -c9-999 | xargs svn add
svn: E155037: Previous operation has not finished; run 'cleanup' if it was interrupted

Команда svn cleanup тоже не помогла

$ svn cleanup
svn: E155010: The node '/usr/local/www/path/to/af_0011.html.ru' was not found.

Гугление с чтением форумов не особо помогло — пришлось ковырять базу данных, где Subversion хранит состояние рабочей копии — это файл .svn/wc.db, для работы с которым нужен SQLite. Будете ковырять — не забудьте сделать резервную копию!

$ sqlite3 wc.db

sqlite> SELECT * FROM LOCK;
sqlite> SELECT * FROM WC_LOCK;
1|path/to|-1
sqlite> DELETE FROM WC_LOCK;
sqlite> SELECT * FROM WORK_QUEUE;
26|(sync-file-flags path/to/af_0011.html.ru)
sqlite> DELETE FROM WORK_QUEUE;

В моём случае сработало.

Karta ne po-russki

Недавно появилась задача — перевести карту студенческого городка на английский. Или хотя бы транслитерировать её, избавившись от кириллицы. Карта состоит из двух групп слоёв, в одной из них содержатся слои с маркерами, задаными яваскриптовым кодом — перевести их не составит труда, а вот растровый слой, подложку, поверх которой отображаются маркеры, перевести чуть сложнее — об этом сегодняшняя история.

Для того, чтоб иметь контроль над внешним видом подложки, не зависеть от размещающих тайлы (квадратные растровые фрагменты карт) сторонних сервисов, и не платить им денег в конце концов, тайлы генерируются из геоданных OpenStreetMap самостоятельно. OpenStreetMap для любого объекта может содержать множество имён — это и то, что хранится с ключом name — имя вообще, и int_name — международное имя, и куча имён с ключами вида name:ru, name:en, name:что_попало. Если делать тайлы с помощью TileMill, а свой стиль создавать на основе OSM Bright, то доступно только одно имя — name, однако в настройках сопоставления для imposm можно выбрать нужный язык — по умолчанию эта строка в файле imposm-mapping.py закомментирована:

set_default_name_type(LocalizedName(['name:en', 'int_name', 'name']))

Запускаем импорт, английские имена попадают в базу… Однако английских имён мало, сильно меньше, чем объектов с именами, записанными кириллицей.

Выхода из этой ситуации два — правильный и быстрый.
Правильный заключается в аккуратном переводе имён в OSM — слишком долго, да и неохота руками ковыряться.
Быстрый способ — не трогать OSM, а имена транслитерировать локально, в своём экземпляре базы данных. Так и поступим: создадим функцию транслитерации (прообраз подсмотрел на sql.ru) и выполним кучу UPDATE, вызывающих эту функцию. Мне, как перловому программисту, больше был симпатичен вариант с написанием перловой функции внутри PostgreSQL, но сразу такой вариант у меня не заработал, а разбираться было лень.

Итак, скармливаем постгресу такой код:

CREATE OR REPLACE FUNCTION ru_translit(p_string character varying)
  RETURNS character varying AS
$BODY$
-- Transliteration of Cyrillic letters
select
replace(
  replace(
    replace(
      replace(
        replace(
          replace(
            replace(
              replace(
                replace(
                  replace(
                    replace(
                      replace(
                        replace(
                          replace(
                            replace(
                              replace(
                                replace(
                                  replace(
                                    replace(
                                      replace(
                                        replace(
                                          replace(
                                            replace(
                                              replace(
                                                translate(
                                                  $1, 
                                                  'АБВГДЕЗИЙКЛМНОПРСТУФЫЭабвгдезийклмнопрстуфыэ',
                                                  'ABVGDEZIYKLMNOPRSTUFYEabvgdeziyklmnoprstufye'
                                                ),
                                              'ё', 'yo'),
                                            'ж', 'zh'),
                                          'х', 'kh'),
                                        'ц', 'ts'),
                                      'ч', 'ch'),
                                    'ш', 'sh'),
                                  'щ', 'shch'),
                                'ъ', ''),
                              'ь', ''),
                            'э', 'e'),
                          'ю', 'yu'),
                        'я', 'ya'),
                      'Ё', 'Yo'),
                    'Ж', 'Zh'),
                  'Х', 'Kh'),
                'Ц', 'Ts'),
              'Ч', 'Ch'),
            'Ш', 'Sh'),
          'Щ', 'Shch'),
        'Ъ', ''),
      'Ь', ''),
    'Э', 'E'),
  'Ю', 'Yu'),
'Я', 'Ya');
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

UPDATE osm_admin            SET name=ru_translit(name);
UPDATE osm_aeroways         SET name=ru_translit(name);
UPDATE osm_amenities        SET name=ru_translit(name);
UPDATE osm_barrierpoints    SET name=ru_translit(name);
UPDATE osm_barrierways      SET name=ru_translit(name);
UPDATE osm_buildings        SET name=ru_translit(name);
UPDATE osm_landusages       SET name=ru_translit(name);
UPDATE osm_landusages_gen0  SET name=ru_translit(name);
UPDATE osm_landusages_gen1  SET name=ru_translit(name);
UPDATE osm_mainroads        SET name=ru_translit(name);
UPDATE osm_mainroads_gen0   SET name=ru_translit(name);
UPDATE osm_mainroads_gen1   SET name=ru_translit(name);
UPDATE osm_minorroads       SET name=ru_translit(name);
UPDATE osm_motorways        SET name=ru_translit(name);
UPDATE osm_motorways_gen0   SET name=ru_translit(name);
UPDATE osm_motorways_gen1   SET name=ru_translit(name);
UPDATE osm_places           SET name=ru_translit(name);
UPDATE osm_railways         SET name=ru_translit(name);
UPDATE osm_railways_gen0    SET name=ru_translit(name);
UPDATE osm_railways_gen1    SET name=ru_translit(name);
UPDATE osm_transport_points SET name=ru_translit(name);
UPDATE osm_waterareas       SET name=ru_translit(name);
UPDATE osm_waterareas_gen0  SET name=ru_translit(name);
UPDATE osm_waterareas_gen1  SET name=ru_translit(name);
UPDATE osm_waterways        SET name=ru_translit(name);

Если мы добавляли номера домов на карту — транслитерируем и их заодно:

UPDATE osm_buildings SET "addr:housenumber"=ru_translit("addr:housenumber");

После чего можно запускать отрисовку своих тайлов — кириллицы там уже не будет.

Карта без кириллицы

Больше букв

Функция selectall_arrayref перлового модуля DBI хороша для тех, кому лень писать:

This utility method combines «prepare», «execute» and «fetchall_arrayref» into a single call. It returns a reference to an array containing a reference to an array (or hash, see below) for each row of data fetched.

В большинстве случаев её вполне можно применять, что я и делаю, однако такой подход хорош не всегда: при попытке выполнить такой функцией запрос, возвращающий много данных, потребуется память под все эти данные.

Реальный пример: скрипт, извлекающий тайлы из пакета, созданного Тайлмиллом, пытался читать данные как раз функцией selectall_arrayref. Зная, что применяется запрос

SELECT * FROM tiles

и что представление tiles содержит, помимо прочего, содержимое тайлов, занимающее места больше всего остального, нетрудно догадаться, что попытка выполнения запроса потребует выделения памяти в объёме, сопоставимом с размером файла, в котором сидит база (пакет с тайлами — это база SQLite).

Набор тайлов для территории размером 600×400 км в средних широтах — например, с Челябинском по центру, Ашой на западе, Карталами на юге и Тюменью на северо-востоке — займёт больше гигабайта для набора масштабов не больше шестнадцатого. На практике так и получилось: скрипт отжирал больше гигабайта памяти и всё никак не мог приступить к полезной части, пытаясь отожрать ещё. Если же увеличивать масштаб, затраты вырастут ещё сильнее: добавим семнадцатый зум масштаб — понадобятся ещё три-четыре гигабайта, Добавим восемнадцатый, которого хватит даже для любопытных исследователей карт — ещё на десять-двадцать объём вырастет. Если будем сохранять тайлы с глубиной цвета 24 бита, а не восемь — ещё больше места израсходуем. Получается, что средних размеров российская область может занять своими тайлами десятки гигабайт. И скрипт бы безуспешно пытался эти десятки получить.

Переписал:

-my $tiles = $dbh->selectall_arrayref(
-    'SELECT * FROM tiles',
-    { Slice => {} }
-);
-
-foreach my $tile ( @$tiles ) {

+my $sth = $dbh->prepare('SELECT * FROM tiles');
+   $sth->execute;
+
+while ( my $tile = $sth->fetchrow_hashref ) {

и всё наладилось: скрипт перестал жрать память (ему хватило десяти мегабайт) и ждать её выделения — сразу работает.

Вывод: не всегда надо экономить рабочее время программиста — иногда надо и о машинном времени задумываться.

Трамвайная схема — можно и в TileMill

В марте я рисовал для википедии челябинскую трамвайную схему при помощи TileMill, однако как выделить трамвайные пути самим тайлмиллом, я не знал. Пришлось тогда экспортировать схему в SVG и затем редактировать полученный SVG-файл в Inkscape, вручную выделяя нужные рельсы и меняя им внешний вид.

На самом деле в том, чтоб TileMill нарисовал трамвайные пути, нет особо хитрой магии. Более того, даже не надо, пугаясь питона, ковырять imposm-mapping.py — трамвайные пути можно выделить при помощи CartoCSS, потому что для железных дорог сохраняется их тип:

SELECT DISTINCT type FROM osm_railways;
type
--------------
preserved
narrow_gauge
light_rail
subway
tram
rail

Стиль трамвайных путей можно задать в файле roads.mss. В OSM Bright все доро́ги: и железные, и обычные — слиты в один слой. Правила рисования дорог весьма витиеваты, поэтому, чтоб не тратить на эксперименты лишние усилия, мы не будем пытаться переопределить стиль трамвайных путей — вместо этого добавим новое правило, рисующее широкие красные линии поверх трамвайных путей, не трогая более ничего. Правило это надо поместить после правил, описывающих слой #roads_high:

#roads_high::tram_highlight [type='tram'] {
  line-width: 5;
  line-color: @tram_line;
  line-comp-op: darken;
}

Результат:

Схема трамвайных путей

Для того, чтоб убедиться в работоспособности метода, этого вполне достаточно.

Бросается в глаза отсутствие подсветки на мостах — связано это с тем, что слой #bridge находится выше, чем #roads_high. Возможный способ решения — завести свой слой железных дорог (а для этого надо уже́ лезть в imposm-mapping.py, но теперь это не страшно), поместить его выше слоя с мостами и написать для железнодорожного слоя свои стилевые правила.

Раскрасим домики

Итак, после предыдущих упражнений здания на нашей карте обзавелись номерами и высотой. Что дальше? Дальше можно, например, раскрасить здания в разные цвета. Можно раскрасить дома в зависимости от основного их предназначения (то есть, в зависимости от содержимого тэгов amenity и building). Можно — в зависимости от принадлежности здания.

Когда-то на сайте нашего университета располагалась схема студенческого городка, на которой были здания четырёх разных цветов: общежития, учебные корпуса, прочие здания студгородка, а также посторонние здания, в студгородок не входящие. То есть, на той схеме здания выделялись и по предназначению, и по принадлежности.

Достичь подобного результат с картами OpenStreetMap, рисуемыми через библиотеку Leaflet, можно разными путями: можно, взяв какую-либо карту, не трогать её растровый слой, оставить его без изменений, добавив свой векторный слой, куда нанести контуры зданий. Пример — usjeans.ru/map. Другой подход — сделать собственный растровый слой, на котором сразу и отметить необходимое. Так как предыдущие эксперименты касались как раз собственного растрового слоя — продолжим опыты в том же направлении.

Отметить принадлежность зданий в OpenStreetMap можно разными путями: первый, очевидный — задать у каждого нужного нам здания какой-нибудь новый тэг. Тэг может быть практически любым — даже при существующих ограничениях на имена можно придумать что-нибудь. То множество тэгов, что применяется в OSM — результат не жёсткого диктата разработчиков, а договорённости сообщества участников и множество это легко расширяемо. Другой путь, который мне представляется более верным — создать отношение. Для группировки объектов, представляющих собой что-то общее, предназначено отношение с типом site. Как выяснилось, нужное мне отношение уже́ существовало — надо было лишь проверить его корректность и внести необходимые изменения (например, добавить здания, в отношение не попавшие).

На следующем этапе, импорте данных, выяснилось, что в imposm не предусмотрен способ сохранить в базе данных информацию о принадлежности к отношению. Во всяком случае, в документации он не описан. Не беда — эту информацию можно внести и после импорта, напрямую обратившись к базе данных. Для хранения информации о принадлежности нужно создать дополнительное поле. Так как нам нужен только признак принадлежности здания конкретному отношению, достаточно будет типа Bool. После всех изменений описание сопоставления данных для зданий в файле imposm-mapping.py стало выглядеть так:

buildings = Polygons(
    name = 'buildings',
    fields = (
        ('area', PseudoArea()),
        ('addr:housenumber',String()),
        ('building:levels',Integer()),
        ('height',Integer()),
        ('belongs_to_susu',Bool()),
    ),
    mapping = {
        'building': (
            '__any__',
        ),
        'railway': (
            'station',
        ),
        'aeroway': (
            'terminal',
        ),
    }
)

Выставить флаг принадлежности можно выполнением SQL-запроса

UPDATE osm_buildings
SET belongs_to_susu=1
WHERE osm_id IN (список_id, через запятую);

Чтоб не перечислять объекты вручную, был написан перловый скрипт. Попутно выяснилось, что, несмотря на то, что отношение может в себе содержать другие отношения, нет смысла устраивать рекурсивный обход: в поле osm_buildings.osm_id хранится первый попавшийся номер: он может быть как номером линии (для большинства зданий), так и номером отношения.

Задача определения роли здания после всех предыдущих манипуляций кажется не такой уж и сложной: во-первых, тип здания (точнее, его предназначение) может хранится в тэге building — его содержимое при импорте попадает в поле osm_buildings.type. Среди допустимых типов есть и dormitory — общежития (кстати, не надо путать с tourism:hostel — это совсем разные тэги). Тип «учебные корпуса» (например, academic_building) среди часто применяемых не встречается — есть лишь university, который не совсем подходит: университет — это не только учебные корпуса. Можно, конечно, прямо у самих зданий указывать building=academic, но мне более правильным показался вариант указания роли здания внутри отношения — там и поле для роли есть, и оно не используется совсем. Больше шансов, что данные спокойно сохранятся именно тогда, когда они лежат внутри отношения. А вот в базу данных роль вносится в поле osm_buildings.type:

UPDATE osm_buildings
SET type='academic'
WHERE osm_id IN (список_id, через, запятую);

Как и прежде, для упрощения назначения ролей, написан ещё один перловый скрипт.

Стилевые правила для зданий (в OSM Bright он лежат в файле base.mss) начинаются так:

#buildings[zoom>=12] {
  polygon-fill:@building;
  // Our buildings
  [belongs_to_susu=1] {
    polygon-fill: @susu_building;
    [type='dormitory'] {
     polygon-fill: @susu_dormitory;
    }
    [type='academic'] {
     polygon-fill: @susu_academic;
    }
  }

Определение цветов @susu_building, @susu_dormitory, @susu_academic добавлено в файл palette.mss — там для них самое подходящее место.

@susu_building:     saturate(darken(@building, 15%), 10);
@susu_dormitory:    mix(@susu_building, #f60, 95%);
@susu_academic:     mix(@susu_building, #06f, 93%);

Результат:
Карта с разноцветными домами