Агрегирование выборки в MongoDB + PHP

MongoDB - очередное веяние моды в веб-разработке, когда хранение данных заранее планируется таким огромным, что необходимо их распределение на несколько серверов с помощью шардирования. Поизучаем же непростой синтаксис агрегирования данных в mongo + php

mysql_mongo_query_comparison.png

Уникальное поле

Итак допустим есть коллекция объектов с повторяющимися полями, а нам хочется вытянуть только уникальные поля. В MySQL это делает DISTINCT(поле), здесь же надо запустить комманду которая вернёт только поле person_id.

$aObjects = $Mongo->command(array( 'distinct'=>'my_userlog_collection', 'key'=>'person_id', 'query'=>array( '$or'=>array( array('person_id' => new MongoRegex('/'.$sSearch.'.*/i')), array('firstname' => new MongoRegex('/.*'.$sSearch.'.*/i')), array('lastname' => new MongoRegex('/.*'.$sSearch.'.*/i')), ) ), ));

Список стран, ISO и международные данные

При разработке сайтов с международной аудиторией надо не только иметь списки стран и языков, но и учитывать их взаимосвязи, форматы, временные зоны. Я уже не говорю про трудности перевода и силлогизмы которые могут быть неправильно истолкованы публикой (например blue ? голубой). Но для простоты условимся что настала эпоха тотального глобализма и можно запросто скачать такие универсальные списки в SQL-формате как..

Страны

В основном этим занимается стандарт ISO3166-1. Проблем с нормализацией таблицы стран несколько.

Во-первых это само определение независимого государства. Существует целый список единично, частично или полностью не признанных  мировым сообществом государств. Начиная с Израиля с Китаем до Косово и Палестины, Абхазии и Южной Осетии. Несомненно политику отображения той или иной страны на сайте зависит от клиента - будет он следовать признанию других стран согласно своему гражданству или же либерально будет показывать всё. Однако следует учитывать процессы в которых эта переменная будет учитываться, например отправка почты (billing address).

Во-вторых, как уже было замечено многими специалистами по useability, возникает вопрос useability - на каком языке стоит показывать страну? Соответсвенно переводить надо названия стран на некие языки (все или государственные?), либо использовать написание на коренном языке. Опять же - который брать если их несколько, как в Швейцарии или Индии? Наконец, к странам можно приписать множество параметров, которые могут характеризовать различные социологические, политические, исторические, финансовые и прочие.

Языки

Флаг и герб стран это символ государства, а не языка. Языков в мире более десяти тысяч и их все пытается классифицировать стандарт ISO 639-3, включающий и взаимосвязи и нынешнее состояние этноса. Я недавно работал с международным сайтом где было 24 страны и каждая страна должна была быть представлена на своём языке. Так вот сам сайт очень простой, но из-за обилия языков возникали постоянно проблемы что где должно показываться. Тут нужна система переводов и чёткое описание иерархии объектов - зависят баннеры от языка или нет, каталог продуктов, статьи и тп.

С точки зрения удобства, как в админке, так и на публичной части сайта, языки должны быть написаны без перевода (native) - "English, Русский, Eesti, Українська" и тд. Если языков более трёх-пяти, то надо делать select если места мало, а дизайн не сильно приспособлен как на Wikipedia. Иметь огромную таблицу всех языков мира может быть полезно только в случае если вы хотите позволить пользователю добавлять языки на сайт самому и при этом ограничить его существующими языками и кодами. Иначе это лишний груз.

Валюта

Почему валюту я выделяю в отдельные таблицы? Во-первых, логически это финансовая характеристика страны которая может достаточно быстро меняться по сравнению с остальными параметрами. Во-вторых к валютам часто приходится приделывать курсы валют, процент налога с оборота, систему оплаты и счетов (billing, invoicing). Наконец это отдельный ISO-4217 стандарт.

Даты и часовые пояса

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

  • Юлианский календарь (4 г. н.э - 24 февраля 1582 г. н.э.), по нему в году - 365? дней (каждый четвёртый год - високосный). Простолюдинам число дней в месяце можно было легко запомнить по "горам" и "впадинам" на пальцах на кисти двух рук, считая слева направо. Тоесть хрящ (гора) означала январь - 31, впадина (февраль) - 28 или 29, март (31) и тд. Православная церковь до сих пор использует этот календарь.
  • Григорианский календарь заменил Юлианский, было пропущено 10 дней и для улучшения точности введено правило что каждый 100й год - невисокосный, но делящиеся на 400 всё-таки високосные. В среднем за эти 400 лет получается что год равен 365.2425 дней.
  • Длинный счёт календаря майя я немогу не упомянуть, поскольку он точней чем Григорианский, хотя нигде и не используется. В среднем точность года составляет 365.242036 дней и формируется группировкой дней в месяцы (уинал) по 20, а те в свою очередь дальше, получается цикл = 20 дней ? 18 уинал ? 20 тун ? 20 катун ? 13 бактун = 1872000 дней. Когда точно началось исчисление - неизвестно, но по существующей теории этот цикл "Пятого солнца" закончится 20 декабря 2012.
  • Персидский солнечный календарь cоставленный при участии Омар Хаяма. В отличие от Григорианского где ошибка составляет 1 день в 3000 лет, тут год в среднем состоит из 365,24242 дня, с ошибкой 1 день в 4500 лет. Возрождённый в статус официального Иранского календаря в 1925 году, вычислением високосного года явно занимался математик, потому что первый считается таковым если при делении года на 33 остаток составляет 1, 5, 9, 13, 17, 22, 26 или 30.

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

Из шести комбинаций упорядочивания даты, в мире наиболее популярной стала логичная возрастающая, в странах могут незначительно различаться только разделители и число цифр. Только под США приходится подстраиваться. Не забывайте что для этого формата тоже нужны переводы — для датского например "DD.MM.????"

dd.mm.yyyy 13 января 2010 Little endian. Ирландия, Россия, Бельгия, Бразилия, Франция, Норвегия, Дания, Нидерланды
y-m-d
2010 январь 13 Big endian, ISO 8601. Канада, Германия, Япония, Китай, Венгрия
m-d-y сентябрь 8, 1380 Middle endian, США
y-d-m
 США (службы охраны природы)
m-y-d
 ?
d-y-m
 ?

Автомобильные номера

Валидация автомобильных номеров дело бесмысленное. В Эстонии например приняты несколько стандартов этих номеров и судя по списку они отличаются и цветом и формой и числом букв/цифр. При этом надо помнить что бывают и заграничные и заказные номера. Поэтому какую-нибудь автоматическую парковку или определение номеров по изображению делать очень проблематично.

Итого SQL-dump весит 16 мб, состоит из 4 таблиц:

  • iso_countries
  • iso_currency
  • iso_languages
  • iso_country_ip

Источники:

Файлы

Системные таблицы в базах данных

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

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

Mysql - отдельный тип запроса. SHOW так-же может показывать состояние БД, нагруженность процессами и тп.

SHOW TABLE status
SHOW COLUMNS FROM my_custom_table

Postgre - Иерархия разделена по принципу database.schema.table, поэтому часто администраторы не любят создавать новые базы данных - для них каждый раз дублируются системные схемы pg_catalog и information_schema.

select table_name, table_schema from information_schema.tables //все таблицы
select column_name from information_schema.columns WHERE table_name='columns' // замкнутый круг

Oracle - обычные таблицы, которые правда мало кто может просто так взять и поменять

select * from ALL_TABLES
select * from USER_TABLES

Межтабличный UPDATE

Редко когда, но всё же приходится делать в базе данных одиночные запросы по перемещению существующих данных из одной таблицы в другую. И если с добавлением рядов используя INSERT проблем возникает мало, то с обновлением существующих таблиц у меня возникла трудность. А именно - была старая таблица Invision Powerboard форума, и имелась новая таблица пользователей, с одинаковыми пользователями.

Хотелось перенести поля из одной таблицы в другую. В самом худшем случае можно было написать цикл в php, где ряд за рядом делался бы одиночный UPDATE. Но поскольку лень - лучшая мотивация человечества к прогрессу, то по принципу наименьшего сопротивления пришлось обойтись таким запросом..

UPDATE sys_users
SET avatar = ( SELECT ibf_members.avatar
                            FROM ibf_members
                            WHERE sys_users.login = ibf_members.name LIMIT 0,1)
WHERE EXISTS ( SELECT 1
                                FROM ibf_members
                                WHERE sys_users.login = ibf_members.name )

Если интересно, то EXISTS выражение нужно только для возможности использования подзапроса, связывающего обе таблицы, раз уж в обычном UPDATE нельзя использовать несколько таблиц, единица это первое поле, а LIMIT нужен на случай дубликатов.

Удаление дубликатов в Mysql

Очень часто всплывает тема поиска дубликатов в таблицах, особенно когда надо установить UNIQUE constraint. Всегда можно обойтись таким запросом..

SELECT login, COUNT(login) AS cnt
FROM sys_users GROUP BY login
HAVING ( COUNT(login) > 1 )

Удалить соответсвенно можно запросом с простым JOIN таблиц самой на себя:

DELETE t1 FROM sys_users t1, sys_users t2 WHERE t1.login=t2.login AND t1.ID > t2.ID

Ещё один красивый и лаконичный способ..

ALTER IGNORE TABLE sys_users ADD UNIQUE INDEX(login);

Читайте также: