пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ
По вашему запросу ничего не найдено :(
Убедитесь, что запрос написан правильно, или посмотрите другие
наши статьи:
Итак, вы полностью укомплектовали и настроили ваш умный дом. И конечно, вам нравится периодически показывать выпендриваться перед друзьям, как круто включать лампы, проигрывать видео и фильмы подсказкой голосовому помощнику, приготовить кофе или регулировать термостат коснувшись приложения на экране смартфона. Поздравляем!
Но если вы любитель автоматизации (как и мы), который редко останавливается на достигнутом, то возможно будете разочарованы количеством необходимых программ, которые нужно загрузить, интерфейсов, которые вам придётся усваивать, чтобы управлять гаджетами. Скорее всего, будут отдельные приложения для управления освещением, медиацентром, термостатом и приложение Google Home, который изо всех сил (но безнадежно) старается собрать всё это воедино.
Большая вероятность того, что некоторые приложения будут несовместимы с другими и, вероятно, многие из них не будут работать, если они не в одной сети с гаджетом. Представьте если бы мы смогли управлять всем этим из одного интерфейса, на засоряя телефон или компьютер сотнями приложений, через интерфейс, который доступен как на смартфонах, так и на компьютерах, а также с помощью сторонних сценариев вне зависимости от того, находимся ли мы в одной сети с умным домом или нет. Интерфейс, который был бы легким и простым в использовании?
А что если мы будем делать это через мессенджер или чат? В конце концов, разве не легче было бы контролировать наш дом, гаджеты и облачные сервисы через тот же интерфейс, который мы используем для отправки фотографий котиков нашим друзьям, и через бот, полностью адаптированный к нашим потребностям?
В этой статье я покажу вам, как настроить команды и процедуры в дополнение к существующим сетапам умного дома. В данном руководстве мы используем два основных инструмента:
Telegram: существует много мессенджеров и платформ, но до сих пор попытки многих из них (Facebook Messenger, Whatsapp, Hangouts и т.д.) в предоставлении пригодного для разработчиков API, мягко говоря, были тщетны. Ушли те дни, когда все использовали XMPP или IRC в качестве своего мессенджер. Сегодняшний мир мессенджеров очень разнообразен.
Кроме того, поскольку в интересах многих крупных игроков создавать изолированные ИТ экосистемы, наиболее часто используемые решения не поставляются с официально поддерживаемыми API/интерфейсами разработчиков. Мало того: некоторые из них активно отговаривает пользователей от использования чего-либо, кроме официального приложения, для взаимодействия с платформой (почитайте, как Whatsapp может забанить вас).
В этом чрезвычайно разнообразном мире, состоящем из нескольких несвязанных островов, Telegram представляет собой радостное исключение: их официальный bot API хорошо задокументирован и поддерживается, и для тех, кто знает немного программирования, очень легок в интеграции.
Platypush: Platypush поставляется с плагином для Telegram и бэкэндом. Так что давайте начнем и создадим первый бот для автоматизации управления домом!
Создание Telegram-бота
Начните беседу с Botfather.
Наберите /start, а затем /newbot для создания нового бота. Задайте боту ник и имя.
Вы получите ссылку, чтобы начать беседу с вашим ботом и уникальный API-ключ. Сохраните его где-нибудь, так как он нам понадобится для конфигурации плагина platypush.
Конфигурация бота в platypush
1. Установите platypush с основными расширения и интеграцией с Telegram:
pip install 'platypush[http,db,telegram]'
apt-get install redis-server
[sudo] systemctl start redis
[sudo] systemctl enable redis
2. Изучите platypush хотя бы немного, если еще не сделали этого. Определите несколько вещей, которыми вы хотите управлять/автоматизировать - источники света, музыку, датчики, базу данных, роботы - и установите/настройте соответствующие расширения.
В этой статье мы рассмотрим, как настроить наш новый бот для управления освещением Philips Hue, воспроизведением музыки и потоковой передачей PiCamera.
3. Добавьте настройки Telegram в файл ~/.config/platypush/config.yaml:
chat.telegram:
api_token: <your bot token>
backend.chat.telegram:
enabled: true
Бэкэнд-система позволяет получать события (например, новые сообщения, вложения, запросы и т.д.) и создавать на них пользовательские "хуки". Плагин позволяет писать вам чаты, программно отправлять сообщения и вложения, администрировать каналы и т.д.
Допустим, мы хотим, чтобы бот реализовал следующие команды:
/start
Приветствие пользователя
/help
Показать доступные команды
/lights_on
Включить свет
/lights_off
Выключить свет
/music_play
Включить музыку
/music_pause
Приостановить музыку
/music_next
Перейти на следующую песню
/music_prev
Перейти на предыдущую песню
/start_streaming
Начать удаленное вещание PiCamera
/stop_streaming
Остановить удалённое вещание PiCamera
Всё что мы должны сделать это создать действие в конфигурационном файле platypush config.yaml. В этом контексте вы должны:
Установить и настроить плагины Philips Hue, mopidy и PiCamera:
pip install 'platypush[hue,mpd,picamera]'
# Hue lights configuration
light.hue:
# Hue bridge IP address
bridge: 192.168.1.10
# Default groups to control
groups:
- Living Room
# MPD/Mopidy configuration
music.mpd:
host: localhost
port: 6600
# PiCamera configuration
camera.pi:
vflip: False
hflip: False
Чтобы не засорять файл config.yaml, создайте новый файл с названием ~/.config/platypush/include/bot.yaml:
# /start command handler
event.hook.OnTelegramStartCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: start
then:
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "Welcome! Type /help to see the available commands"
# /help command handler
event.hook.OnTelegramHelpCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: help
then:
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "Available commands:
- /lights_on
- /lights_off
- /music_play [resource]
- /music_pause
- /music_prev
- /music_next
- /start_streaming
- /stop_streaming
"
# /lights_on command handler
event.hook.OnTelegramLightsOnCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: lights_on
then:
- action: light.hue.on
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "Lights turned on"
# /lights_off command handler
event.hook.OnTelegramLightsOffCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: lights_off
then:
- action: light.hue.off
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "Lights turned off"
# /music_play command handler
event.hook.OnTelegramMusicPlayCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: music_play
then:
- if ${cmdargs}:
- action: music.mpd.play
args:
resource: cmdargs[0]
- else:
- action: music.mpd.play
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "Music playing"
# /music_pause command handler
event.hook.OnTelegramMusicPauseCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: music_pause
then:
- action: music.mpd.pause
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "Music paused"
# /music_prev command handler
event.hook.OnTelegramMusicPrevCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: music_prev
then:
- action: music.mpd.previous
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "Playing previous track"
# /music_next command handler
event.hook.OnTelegramMusicNextCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: music_next
then:
- action: music.mpd.next
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "Playing next track"
# /start_streaming command handler
event.hook.OnTelegramCameraStartStreamingCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: start_streaming
then:
- action: camera.pi.start_streaming
args:
listen_port: 2222
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "PiCamera streaming started. Check it out with vlc tcp/h264://hostname:2222"
# /stop_streaming command handler
event.hook.OnTelegramCameraStopStreamingCmd:
if:
type: platypush.message.event.chat.telegram.CommandMessageEvent
command: stop_streaming
then:
- action: camera.pi.stop_streaming
- action: chat.telegram.send_message
args:
chat_id: ${chat_id}
text: "PiCamera streaming stopped"
Подключите файл конфигурации бота в config.yaml:
include:
-include/bot.yaml
Запустите platypush:
# Manual start
platypush
# Service start
systemctl start platypush.service
Создайте беседу в вашим ботом перейдя по ссылке, выданной BotFather и начните говорить ему, что делать:
Сейчас бот доступен любому мы этого явно не хотим. Представьте, что кто-то включит на полную громкость System Of A Down- Jet Pilot вам ночью. Так себе пробуждение. Можно настроить бэкэнд Telegram так, чтобы он принимал сообщения только из определенного списка идентификаторов чатов (в Telegram chat_id используется как для частных пользователей, так и для групп).
Отправьте сообщение боту и откройте журналы platypush или проверьте его стандартные выходные данные. На экране появятся следующие сообщения:
2020-01-03 19:09:32,701| INFO|platypush|Received event: {"type": "event", "target": "turing", "origin": "turing", "id": "***", "args": {"type": "platypush.message.event.chat.telegram.CommandMessageEvent", "chat_id": your_chat_id, "message": {"text": "/help", ...}, "user": {"user_id": your_user_id, "username": "****", "is_bot": false, "link": "https://t.me/you", "language_code": "en", "first_name": "***", "last_name": "***"}, "command": "help", "cmdargs": []}}
Скопируйте chat_id своего пользователя и вставьте в бак-энд файл:
backend.chat.telegram:
authorized_chat_ids:
- your_user_id
Теперь бот ответит ошибкой, если вы попытаетесь отправить сообщение от неавторизованного пользователя.
Вы также можете пригласить своего бота в групповой чат и позволить вашим друзьям или членам семьи регулировать свет в вашем доме, если вы захотите!
Что дальше?
В этой статье мы изучили только одну специфическую особенность интеграции Telegram - способность бота реагировать на события в команде, запускать действия и отвечать текстовыми сообщениями.
Как видно из списка поддерживаемых событий Telegram, можно сделать больше, например:
Создавать обработчики, когда кто-то делится контактной информацией - когда-нибудь думали разрешить боту автоматически хранить новые контакты, отправленные вам вашими друзьями в чате?
Создавайте обработчики при совместном использовании документов, видео или изображения - например, автоматически загружайте все файлы мультимедиа, отправленные в чат, на жесткий диск или удаленную папку Dropbox.
Выполнять действия с текстовыми сообщениями вместо команд - можно использовать TextNewsEvent, например, если вы предпочитаете вводить "включить свет" вместо "/lights_on."
Сделайте снимок на камеру наблюдения и отправьте ее себе командой send_photo. Можно также развернуть несколько ботов, например, для каждого устройства, чтобы можно было запускать действия на конкретном устройстве из связанного чата или вместо этого использовать один бот в качестве точки входа и доставлять сообщения другим устройствам через MQTT, Kafka или HTTP API.
Все, что вам нужно знать о Linux, можно найти в man. Это интерфейс, используемый для просмотра справочных руководств системы, отсюда и название: man - сокращение от manual. Например, можно выполнить поиск команды для выполнения задачи, даже если неизвестно, как она называется. Но как найти само руководство? В этой статье расскажем о некоторых скрытых возможностях этой команды.
Встроенное руководство Linux
Существует старая шутка: единственная команда, которую нужно знать в Linux это man – точка входа в руководство пользователя. Несмотря на то, что тут есть доля правды, но даже сама команда man может ввести в тупик вначале. Вернее, поиск информации с помощью этой команды.
Наверное, у всех был случай, когда знали, что вы хотите сделать, но не знали какая команда поможет выполнить поставленную задачу. Это похоже на то, как искать слово в словаре при том, не зная самого слова.
Итак, как же можно найти, то что нужно? С man можно легко обойти эту сложность.
Цифры - еще одна сложность перед новичками. Что они означают? Вы документации или в просторах Интернета часто можно увидеть такие ссылки, man (2) или man (5). Также можно встретить ссылки на команды, за которыми следуют цифры, такие как mount (2) и mount (8). Конечно, не может быть больше одной команды mount, верно? Как мы увидим, цифры важны и их понять относительно просто.
Проще говоря, вести поиск по man достаточно легко если один раз понять, как это работает. На самом деле, есть несколько способов поиска и навигации в man.
Как перейти к руководству
Чтобы запустить руководство по какой либо-команде достаточно в командной строке набрать команду man, а затем через пробел название команды, по которой нужно руководство. После этого система запустить руководство по команде – конечно, если найдет. Давайте посмотрим, что покажет команда man по man:
man man
Ниже показано руководство по команде man:
Как видно, это первая страница руководства man (1). Чтобы просмотреть другие страница выполните одно из следующих действий:
Чтобы прокрутить по одной строчке: используйте колесо мыши, стрелки вверх или вниз и клавишу Enter.
Для перехода на следующую страницу: Нажмите клавишу пробел, или же кнопки PgUp PgDown.
Для перехода в начало и конец руководства: Клавиши Home и End
Если нажать H (заглавная h), то можно перейти в раздел помощи, где можно найти альтернативные комбинации, которыми можно пользоваться для навигации. Чтобы выйти из руководства нажмите Q.
Структура руководства
В начале страницы можно увидеть Название (Name) и Описание (Synopsis). Есть определённые правила оформления страницы руководства. Есть руководства по командам, программам, функциям и т.д. Не во всех руководствах есть эти заголовки, так как некоторые из них применимы только к конкретным командам.
Ниже приведён список заголовков, которые можно встретить в руководстве.
Название (Name): название команды, по которой просматривается руководство
Синопсис (Synopsis): Краткое описание команды и синтаксиса
Конфигурация (Configuration): Детали настройки для устройства
Описание (Description): Описание основного назначения программы
Опции (Ключи): опции которые принимает команда
Выходной статус (Exit Status): Возможные значения, возвращаемые командой при завершении работы
Возвращаемое значение (Return Value): Если руководство запущено по какой-то библиотеке, то это указывает на значение, которое вернет библиотека функции, которая вызвала ее.
Ошибки (Errors): Список всех значение, которые может принимать errno в случае ошибки выполнения команды
Окружение (Environment): Список переменных окружения, которые относятся к команде или программе
Файлы (Files): Список файлов, которые использует команда или программа, например, конфигурационный файл
Атрибуты (Attributes): Список различных атрибутов команды
Версии (Versions): Список изменений в ядре Linux или библиотеке, которую использует команда
Соответствие (Conforming to): Описание любых стандартов, которым может соответствовать команда, например, POSIX.
Заметки (Notes): Дополнительные заметки
Баги (Bugs): Известные ошибки
Примеры (Examples): Один или несколько примеров использования команды
Авторы (Authors): Люди, которые разработали и поддерживают команду
Просмотрите также (See Also): Рекомендуемые материалы по команде
Разделы руководства
Прокрутив ниже на несколько страниц увидите список разделов в данном руководстве:
Это следующие разделы:
Основные команды (General commands): Команды, которые используются в командной строке
Системные вызовы (System calls): Функции ядра, которые может вызвать программа
Функции библиотек (Library functions): общий набор функций и возможностей, используемых программами
Форматы файлов и соглашения (File formats and conventions): Форматы файлов как passwd, cron table, tar архивы
Специальные файлы (Special files): обычно устройства, например, найденные в /dev, и их драйверы.
Игры (Games): Описание команд, например, fortuna, которая при запуске показывает цитаты из БД
Дополнительно (Miscellaneous): Описание таких вещей как inodes, параметры загрузку
Администрирование системы (System administration): Команды и демоны, зарезервированные для использования root-ом.
Распорядок ядра (Kernel Routines): Информация, касающаяся внутренних операций ядра. Сюда входят функциональные интерфейсы и переменные, которые могут быть использованы программистами, которые разрабатывает драйвера устройств.
Цифры в скобках рядом с командой указывают на раздел руководства. Например, man (1) означает первый раздел руководства, которая описывает работу команды man.
На скриншоте выше видна ссылка на man (7). Это значит, что подробную информацию о команде можно найти в другом разделе. Когда впервые открываем руководство по команде, оно показывает man (1). Если ввели команду man без указания раздела, команда будет искать переданные параметр во всех разделы по очереди и конечно же первым выведет первый раздел.
Если нужно найти информацию в конкретном разделе нужно передать команде номер этого раздела.
Например, чтобы открыть седьмой раздел руководства по команде man введем следующую команду:
man 7 man
Руководство откроется с седьмого раздела:
Эта страница руководства содержит инструкции по созданию руководства. Она описывает формат файлов и макросы, которые можно использовать для автоматизации части работы. man (1) же в начале руководства описывает как вообще использовать саму команду man.
Поиск записей в разделах
В основном, если нужно просто узнать, как пользоваться той или иной командой, не надо указывать номер раздела. man найдёт стандартную запись в первом разделе руководства, которая описывает как нужно пользоваться командой. Иногда же, в поиске нестандартной информации, нужно открыть конкретный раздел, содержащий запись по команде.
В Linux легко можно найти разделы, в которых встречается нужная записб. Каждое руководство обладает названием и кратким описанием. Ключ –f (whatis) ведёт поиск по заголовкам и возвращает все вхождения.
Например, введем следующую команду:
man -f man
Команда нашла два совпадения для команды man с разделами и кратким описанием. Однако будьте осторожны - некоторые записи имеют одинаковое название, но описывают разные команды и функции.
Например, введём следующую команду:
man -f printf
Как видно, для команды printf были найдены две записи: одна в первом разделе, и другая в третьем разделе. Однако это разные команды. Информация в разделе 1 описывает команду printf командной строки, которая форматирует данные при выводе в окно терминала. В третьем же разделе описывается семейство функций библиотеки printf в языке программирования C.
Также возможен поиск по кратким описаниям, а также заголовкам страниц. Для этого используется параметр -k (apropos). Это также будет искать соответствия искомому термину поиска внутри других, более длинных слов.
Вводим следующее:
man -k printf
Многие из этих команд описаны в одних и тех же информационных страницах, поскольку их основные функциональные возможности в основном одинаковы. Справочная страница для vprintf описывает функциональность 10 команд, перечисленных на рисунке выше.
Эту функцию можно использовать для поиска информации, для выполнения конкретной задачи, даже если не знаете имя команды, которую хотите использовать.
Допустим, нужно изменить пароль учетной записи пользователя. Мы можем искать любые команды, которые упоминают "user" в заголовках или описаниях страницы. Затем его можно пропустить через функцию grep для поиска записей, содержащих слово "password".
Для этого нужно ввести следующую команду:
man -k 'user ' | grep password
Так как слово user мы выделили одинарными кавычками и в конце поставили пробел, команда будет искать только слово “user”, а не “users”. Бегло просмотрев результат, можно заметить, что самая подходящая команда это passwd.
Так как правило использования указано в первом разделе руководства, не нужно указывать конкретный раздел:
man passwd
Допустим на нужна команда, которая выводит количество слове в текстовом файле. Набираем в командной строке, что-то подобное:
man –k word | grep count
Чтобы узнать все, что нужно знать о подсчете слов, введите следующую команду:
man wc
Говоря о wc, мы также можем в качестве значения передать параметру –k точку ., что означает любой символ. А затем передать вывод команде wc, которой передадим еще и параметр –l (lines), на выводе мы получим число страниц руководства.
Чтобы сделать все это введем команду:
man -k . | wc -l
Итого, у нас 6 706 страниц руководства, но не пусть вас не путает, если у вас это число отличается, так как объем руководства напрямую зависит от установленных в системе программ и предустановленных справочников.
Поиск по руководству
Также есть возможность вести поиск по самому руководству. Например, давайте рассмотрим руководство по команде history:
man history
Чтобы вести поиск в следующих страницах от текущей вводим символ прямой косой черты / и набираем искомое слово. Результат этих действий будет отображаться внизу командной строки. Чтобы начать поиск нажимаем Enter.
Система выведет и выделит первое совпадение по искомому слову:
Чтобы перейти к следующему результату нажмите n, а чтобы перейти к предыдущим результатам – N.
Включить или выключить подсветку найденного слова можно сочетанием клавиш Esc+U.
Если же дошли до конца руководства, но не нашли нужную информацию, то можно вести поиск в обратном направлении. Для этого нажимаем ? и набираем нужный текст:
Также можно перемещаться вперед и назад по найденным результатам.
Есть другой способ поиска по руководству. Он скрывает все строки, которые не содержат совпадения с искомым словом, поэтому лучше использовать номера строк с этим методом.
Если набрать –N и нажать Enter во время просмотра руководства, то радом со строками отобразятся номера строк.
Теперь нажимаем на &, набираем искомое слово и нажимаем Enter.
Теперь отобразятся только строки, в которых найдено искомая фраза:
Просмотре вывод можно найти наиболее интересные результаты. Мы видим, что строка 292 наиболее подходящая и хотим просмотреть данный раздел руководства.
Чтобы показать все снова держим нажатым & и нажимаем Enter.
Теперь набираем номер строки: 292, а затем букву «g», чтобы перейти к указанной строке.
Как только мы нажмем «g» нас перебросит на 292 строку (именно поэтому на скриншоте выше не показана буква «g»).
Чтобы убрать нумерацию строк достаточно набрать –n и нажать Enter.
Прочитайте волшебное руководство
На страницах руководства много полезной информации. Даже у команд, которые вы думаете, вы хорошо знаете, есть такие возможности, о которых вы никогда не слышали.
Вы также определенно найдете команды, о существовании которых вы не знали. С таким количеством различных способов поиска и отслеживания информации, потрясающе иметь под рукой такую команду.
Соединение (JOIN) — одна из самых важных операций, выполняемых реляционными системами управления базами данных. В этой статье мы рассмотрим разные типы соединений: перекрестное соединение (Cross Join), полное внешнее соединение (Full Outer Join), внутреннее соединение (Inner Join), левое (Left Join) и правое соединение (Right Join).
Что такое соединение?
Соединение - это операция, которая объединяет две строки в одну.
Обычно эти строки берутся из двух разных таблиц, но это не обязательно так.
Прежде чем мы рассмотрим, как написать саму операцию объединения, давайте посмотрим, как будет выглядеть результат объединения.
Возьмем, к примеру, систему, хранящую информацию о пользователях и их адресах.
Строки из таблицы, хранящей информацию о пользователях, могут выглядеть следующим образом:
id | name | email | age
----+--------------+---------------------+-----
1 | John Smith | johnsmith@gmail.com | 25
2 | Jane Doe | janedoe@Gmail.com | 28
3 | Xavier Wills | xavier@wills.io | 3
...
(7 rows)
Строки из таблицы, хранящей адресную информацию, могут выглядеть следующим образом:
id | street | city | state | user_id
----+-------------------+---------------+-------+---------
1 | 1234 Main Street | Oklahoma City | OK | 1
2 | 4444 Broadway Ave | Oklahoma City | OK | 2
3 | 5678 Party Ln | Tulsa | OK | 3
(3 rows)
Мы могли бы написать отдельные запросы для получения информации о пользователях и адресах, но в идеале мы могли бы написать один запрос и получить всех пользователей и их адреса в одном наборе результатов.
Именно это и позволяет сделать объединение!
Мы скоро рассмотрим, как писать такие соединения, но если бы мы соединили информацию о пользователях с информацией об адресах, то получили бы такой результат:
id | name | email | age | id | street | city | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
1 | John Smith | johnsmith@gmail.com | 25 | 1 | 1234 Main Street | Oklahoma City | OK | 1
2 | Jane Doe | janedoe@Gmail.com | 28 | 2 | 4444 Broadway Ave | Oklahoma City | OK | 2
3 | Xavier Wills | xavier@wills.io | 35 | 3 | 5678 Party Ln | Tulsa | OK | 3
(3 rows)
Здесь мы видим всех наших пользователей и их адреса в одном красивом наборе результатов.
Помимо получения объединенного набора результатов, еще одно важное применение объединений - это добавление в запрос дополнительной информации, которую можно отфильтровать.
Например, если бы мы хотели отправить физическую почту всем пользователям, живущим в Оклахома-Сити, мы могли бы использовать этот объединенный набор результатов и отфильтровать его на основе столбца «Город».
Теперь, когда мы знаем назначение соединений, давайте приступим к их написанию!
Настройка базы данных
Прежде чем писать запросы, необходимо настроить базу данных.
Для этих примеров мы будем использовать PostgreSQL, но запросы и концепции, показанные здесь, можно легко перенести на любую другую современную систему баз данных (например, MySQL, SQL Server и т. д.).
Для работы с базой данных PostgreSQL мы можем использовать psql - интерактивную программу командной строки PostgreSQL. Если у вас есть другой клиент базы данных, с которым вам нравится работать, это тоже хорошо.
Для начала давайте создадим нашу базу данных. Если PostgreSQL уже установлен, мы можем выполнить команду createdb в терминале, чтобы создать новую базу данных. Я назвал свою базу данных fcc:
$ createdb fcc
Далее запустим интерактивную консоль командой psql и подключимся к базе данных, которую мы только что создали, используя \c :
$ psql
psql (11.5)
Type "help" for help.
john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#
Примечание: я очистил вывод psql в этих примерах, чтобы его было легче читать, поэтому не беспокойтесь, если вывод, показанный здесь, не совсем такой, как в вашем терминале.
Я рекомендую вам последовать за этими примерами и выполнить эти запросы самостоятельно. Вы узнаете и запомните гораздо больше, если будете работать с этими примерами, а не просто читать их.
Теперь перейдем к соединениям!
CROSS JOIN — перекрестное соединение
Самый простой вид соединения — это CROSS JOIN или «декартово произведение».
Это соединение берет каждую строку из одной таблицы и соединяет ее с каждой строкой другой таблицы.
Если бы у нас было два списка, один из которых содержал 1, 2, 3, а другой - A, B, C, то декартово произведение этих двух списков было бы таким:
1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C
Каждое значение из первого списка сопоставляется с каждым значением из второго списка.
Давайте запишем этот же пример в виде SQL-запроса.
Сначала создадим две очень простые таблицы и вставим в них некоторые данные:
CREATE TABLE letters(
letter TEXT
);
INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');
CREATE TABLE numbers(
number TEXT
);
INSERT INTO numbers(number) VALUES (1), (2), (3);
Наши две таблицы, буквы и цифры, имеют только один столбец: простое текстовое поле.
Теперь давайте соединим их вместе с помощью CROSS JOIN:
SELECT *
FROM letters
CROSS JOIN numbers;
letter | number
--------+--------
A | 1
A | 2
A | 3
B | 1
B | 2
B | 3
C | 1
C | 2
C | 3
(9 rows)
Это самый простой тип соединения, но даже в этом простом примере мы видим, что соединение работает: две отдельные строки (одна из букв, другая из цифр) были объединены в одну строку.
Хотя этот тип соединения часто обсуждается как просто академический пример, у него есть, по крайней мере, один хороший вариант использования: покрытие диапазонов дат.
CROSS JOIN с диапазонами дат
Один из хороших вариантов использования CROSS JOIN - взять каждую строку из таблицы и применить ее к каждому дню в диапазоне дат.
Допустим, вы создаете приложение, которое отслеживает ежедневные задачи - например, чистку зубов, завтрак или принятие душа.
Если бы вам нужно было создать запись для каждой задачи и для каждого дня прошедшей недели, вы могли бы использовать CROSS JOIN в диапазоне дат.
Чтобы создать этот диапазон дат, мы можем использовать функцию [generate_series](https://www.postgresql.org/docs/current/functions-srf.html):
SELECT generate_series(
(CURRENT_DATE - INTERVAL '5 day'),
CURRENT_DATE,
INTERVAL '1 day'
)::DATE AS day;
Функция generate_series принимает три параметра.
Первый параметр - это начальное значение. В этом примере мы используем CURRENT_DATE - INTERVAL '5 day'. Это возвращает текущую дату минус пять дней - или «пять дней назад».
Второй параметр - текущая дата (CURRENT_DATE).
Третий параметр - это «интервал шага», то есть то, на сколько мы хотим увеличивать значение каждый раз. Поскольку это ежедневные задачи, мы будем использовать интервал в один день (INTERVAL '1 day').
Если сложить все вместе, то получится серия дат, начинающаяся пять дней назад, заканчивающаяся сегодня и увеличивающаяся на один день за раз.
Наконец, мы удаляем временную часть, приводя выходные значения к дате с помощью ::DATE, и присваиваем этому столбцу псевдоним AS day, чтобы сделать вывод немного красивее.
Результатом этого запроса являются последние пять дней плюс сегодняшний:
day
------------
2020-08-19
2020-08-20
2020-08-21
2020-08-22
2020-08-23
2020-08-24
(6 rows)
Возвращаясь к примеру с задачами на день, давайте создадим простую таблицу для хранения задач, которые мы хотим выполнить, и вставим в нее несколько задач:
CREATE TABLE tasks(
name TEXT
);
INSERT INTO tasks(name) VALUES
('Brush teeth'),
('Eat breakfast'),
('Shower'),
('Get dressed');
В нашей таблице задач есть только один столбец, имя, и мы вставили в эту таблицу четыре задачи.
Теперь давайте сделаем CROSS JOIN наших задач с запросом, чтобы сгенерировать даты:
SELECT
tasks.name,
dates.day
FROM tasks
CROSS JOIN
(
SELECT generate_series(
(CURRENT_DATE - INTERVAL '5 day'),
CURRENT_DATE,
INTERVAL '1 day'
)::DATE AS day
) AS dates
(Поскольку наш запрос на генерацию даты не является таблицей, мы просто записываем его в виде подзапроса).
Из этого запроса мы возвращаем название задачи и день, а набор результатов выглядит следующим образом:
name | day
---------------+------------
Brush teeth | 2020-08-19
Brush teeth | 2020-08-20
Brush teeth | 2020-08-21
Brush teeth | 2020-08-22
Brush teeth | 2020-08-23
Brush teeth | 2020-08-24
Eat breakfast | 2020-08-19
Eat breakfast | 2020-08-20
Eat breakfast | 2020-08-21
Eat breakfast | 2020-08-22
...
(24 rows)
Как мы и ожидали, мы получим строку для каждой задачи за каждый день в нашем диапазоне дат.
CROSS JOIN — это простейшее соединение, но для рассмотрения следующих нескольких типов нам понадобится более реалистичная настройка таблицы.
Создание режиссеров и фильмов
Чтобы проиллюстрировать следующие типы соединений, мы воспользуемся примером фильмов и режиссеров.
В этой ситуации у фильма есть один режиссер, но фильм не обязан иметь режиссера - представьте, что анонсирован новый фильм, но выбор режиссера еще не подтвержден.
В нашей таблице directors будет храниться имя каждого режиссера, а в таблице movies - название фильма и ссылка на режиссера фильма (если он есть).
Давайте создадим эти две таблицы и вставим в них данные:
CREATE TABLE directors(
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
INSERT INTO directors(name) VALUES
('John Smith'),
('Jane Doe'),
('Xavier Wills')
('Bev Scott'),
('Bree Jensen');
CREATE TABLE movies(
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
director_id INTEGER REFERENCES directors
);
INSERT INTO movies(name, director_id) VALUES
('Movie 1', 1),
('Movie 2', 1),
('Movie 3', 2),
('Movie 4', NULL),
('Movie 5', NULL);
У нас есть пять режиссеров, пять фильмов, и у трех из этих фильмов есть назначенные режиссеры. У режиссера ID 1 есть два фильма, а у режиссера ID 2 - один.
FULL OUTER JOIN — полное внешнее соединение
Теперь, когда у нас есть данные для работы, давайте рассмотрим полное внешнее соединение (FULL OUTER JOIN).
FULL OUTER JOIN имеет некоторые сходства с CROSS JOIN, но у него есть и пара ключевых отличий.
Первое отличие заключается в том, что для FULL OUTER JOIN требуется условие присоединения.
Условие соединения определяет, как строки двух таблиц связаны друг с другом и по каким критериям они должны быть объединены.
В нашем примере таблица «Фильмы» имеет ссылку на режиссера через столбец director_id, и этот столбец совпадает со столбцом id таблицы «Режиссеры». Именно эти два столбца мы будем использовать в качестве условия объединения.
Вот как мы напишем это соединение между нашими двумя таблицами:
SELECT *
FROM movies
FULL OUTER JOIN directors
ON directors.id = movies.director_id;
Обратите внимание на указанное нами условие объединения, которое сопоставляет фильм с его режиссером: ON movies.director_id = directors.id.
Наш набор результатов выглядит как нечетное декартово произведение:
id | name | director_id | id | name
------+---------+-------------+------+--------------
1 | Movie 1 | 1 | 1 | John Smith
2 | Movie 2 | 1 | 1 | John Smith
3 | Movie 3 | 2 | 2 | Jane Doe
4 | Movie 4 | NULL | NULL | NULL
5 | Movie 5 | NULL | NULL | NULL
NULL | NULL | NULL | 5 | Bree Jensen
NULL | NULL | NULL | 4 | Bev Scott
NULL | NULL | NULL | 3 | Xavier Wills
(8 rows)
Первыми мы видим строки, в которых у фильма есть режиссер, и наше условие объединения оценивается как true.
Однако после этих строк мы видим все оставшиеся строки из каждой таблицы - но с NULL-значениями, поскольку в другой таблице не было совпадений.
Примечание: если вы не знакомы со значениями NULL, посмотрите мое объяснение в этом учебнике по операторам SQL.
Здесь мы также видим еще одно различие между CROSS JOIN и FULL OUTER JOIN. FULL OUTER JOIN возвращает одну отдельную строку из каждой таблицы - в отличие от CROSS JOIN, который имеет несколько строк.
INNER JOIN — внутреннее соединение
Следующий тип соединения, INNER JOIN, является одним из наиболее часто используемых типов соединения.
Внутреннее соединение возвращает только те строки, для которых условие соединения истинно.
В нашем примере внутреннее соединение между таблицами «Фильмы» и «Режиссеры» вернет только те записи, в которых фильму назначен режиссер.
Синтаксис в основном такой же, как и раньше:
SELECT *
FROM movies
INNER JOIN directors
ON directors.id = movies.director_id;
Наш результат показывает три фильма, у которых есть режиссер:
id | name | director_id | id | name
----+---------+-------------+----+------------
1 | Movie 1 | 1 | 1 | John Smith
2 | Movie 2 | 1 | 1 | John Smith
3 | Movie 3 | 2 | 2 | Jane Doe
(3 rows)
Поскольку внутреннее соединение включает только строки, соответствующие условию соединения, порядок двух таблиц в соединении не имеет значения.
Если мы изменим порядок таблиц в запросе, то получим тот же результат:
SELECT *
FROM directors
INNER JOIN movies
ON movies.director_id = directors.id;
id | name | id | name | director_id
----+------------+----+---------+-------------
1 | John Smith | 1 | Movie 1 | 1
1 | John Smith | 2 | Movie 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2
(3 rows)
Поскольку в этом запросе мы сначала перечислили таблицу «Режиссеры» и выбрали все столбцы (SELECT *), мы видим сначала данные столбца «Режиссеры», а затем столбцы «Фильмы», но результирующие данные одинаковы.
Это полезное свойство внутренних объединений, но оно верно не для всех типов объединений - например, для следующего типа.
LEFT JOIN / RIGHT JOIN
Следующие два типа соединений используют модификатор (LEFT или RIGHT), который влияет на то, данные какой таблицы будут включены в результирующий набор.
Примечание: LEFT JOIN и RIGHT JOIN также могут называться LEFT OUTER JOIN и RIGHT OUTER JOIN.
Эти соединения используются в запросах, где мы хотим вернуть все данные определенной таблицы и, если она существует, данные связанной таблицы.
Если связанной таблицы не существует, мы все равно получим все данные «основной» таблицы.
Это запрос информации о конкретной вещи и бонусной информации, если такая информация существует.
Это легко понять на примере. Давайте найдем все фильмы и их режиссеров, но нам неважно, есть у них режиссер или нет - это бонус:
SELECT *
FROM movies
LEFT JOIN directors
ON directors.id = movies.director_id;
Запрос выполняется по той же схеме, что и раньше - мы только указали объединение как LEFT JOIN.
В этом примере «левой» таблицей является таблица «Фильмы».
Если мы напишем запрос в одну строку, это будет немного проще увидеть:
... FROM movies LEFT JOIN directors ...
Левое соединение возвращает все записи из «левой» таблицы.
Левое соединение возвращает все строки из «правой» таблицы, которые соответствуют условию соединения.
Строки из «правой» таблицы, не соответствующие условию соединения, возвращаются как NULL.
id | name | director_id | id | name
----+---------+-------------+------+------------
1 | Movie 1 | 1 | 1 | John Smith
2 | Movie 2 | 1 | 1 | John Smith
3 | Movie 3 | 2 | 2 | Jane Doe
4 | Movie 4 | NULL | NULL | NULL
5 | Movie 5 | NULL | NULL | NULL
(5 rows)
Глядя на этот набор результатов, мы можем понять, почему этот тип объединения полезен для запросов типа «все это и, если оно существует, кое-что из этого».
RIGHT JOIN работает точно так же, как LEFT JOIN, за исключением того, что правила работы с двумя таблицами меняются местами.
В правом соединении возвращаются все строки из «правой» таблицы. Данные из «левой» таблицы возвращаются условно, на основании условия соединения.
Давайте используем тот же запрос, что и выше, но заменим LEFT JOIN на RIGHT JOIN:
SELECT *
FROM movies
RIGHT JOIN directors
ON directors.id = movies.director_id;
id | name | director_id | id | name
------+---------+-------------+----+--------------
1 | Movie 1 | 1 | 1 | John Smith
2 | Movie 2 | 1 | 1 | John Smith
3 | Movie 3 | 2 | 2 | Jane Doe
NULL | NULL | NULL | 5 | Bree Jensen
NULL | NULL | NULL | 4 | Bev Scott
NULL | NULL | NULL | 3 | Xavier Wills
(6 rows)
Теперь наш набор результатов возвращает каждую строку директора и, если она существует, данные о фильмах.
LEFT JOIN / RIGHT JOIN в производственных приложениях
В производственных приложениях я всегда использую только LEFT JOIN и никогда не использую RIGHT JOIN.
Я делаю это потому, что, по моему мнению, LEFT JOIN делает запрос более легким для чтения и понимания.
Когда я пишу запросы, мне нравится думать о том, чтобы начинать с «базового» набора результатов, скажем, всех фильмов, а затем добавлять (или вычитать) группы вещей из этой базы.
Поскольку мне нравится начинать с базы, LEFT JOIN подходит для этой линии мышления. Мне нужны все строки из моей базовой таблицы («левая» таблица), и я условно хочу получить строки из «правой» таблицы.
На практике я не думаю, что когда-либо видел RIGHT JOIN в производственном приложении. Нет ничего плохого в RIGHT JOIN - я просто думаю, что это делает запрос более сложным для понимания.
Переписывание RIGHT JOIN
Если бы мы хотели перевернуть наш сценарий выше и вместо этого вернуть всех режиссеров и, условно, их фильмы, мы можем легко переписать RIGHT JOIN в LEFT JOIN.
Все, что нам нужно сделать, — это изменить порядок таблиц в запросе и заменить RIGHT на LEFT:
SELECT *
FROM directors
LEFT JOIN movies
ON movies.director_id = directors.id;
Примечание: мне нравится ставить таблицу, к которой происходит присоединение («правая» таблица - в примере с фильмами), первой в условии присоединения (ON movies.director_id = ...) - но это только мои личные предпочтения.
Фильтрация с помощью LEFT JOIN
Существует два варианта использования LEFT JOIN (или RIGHT JOIN).
Первый случай мы уже рассмотрели: возврат всех строк из одной таблицы и условно из другой.
Второй вариант использования - возврат строк из первой таблицы, когда данные из второй таблицы отсутствуют.
Сценарий будет выглядеть следующим образом: найти режиссеров, которые не принадлежат к фильму.
Для этого мы начнем с LEFT JOIN, и наша таблица режиссеров будет первичной или «левой» таблицей:
SELECT *
FROM directors
LEFT JOIN movies
ON movies.director_id = directors.id;
Для режиссера, не относящегося к фильму, столбцы таблицы «Фильмы» имеют значение NULL:
id | name | id | name | director_id
----+--------------+------+---------+-------------
1 | John Smith | 1 | Movie 1 | 1
1 | John Smith | 2 | Movie 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2
5 | Bree Jensen | NULL | NULL | NULL
4 | Bev Scott | NULL | NULL | NULL
3 | Xavier Wills | NULL | NULL | NULL
(6 rows)
В нашем примере идентификаторы режиссера 3, 4 и 5 не принадлежат ни одному фильму.
Чтобы отфильтровать наш набор результатов только по этим строкам, мы можем добавить предложение WHERE, чтобы возвращать только строки, в которых данные о фильме равны NULL:
SELECT *
FROM directors
LEFT JOIN movies
ON movies.director_id = directors.id
WHERE movies.id IS NULL;
id | name | id | name | director_id
----+--------------+------+------+-------------
5 | Bree Jensen | NULL | NULL | NULL
4 | Bev Scott | NULL | NULL | NULL
3 | Xavier Wills | NULL | NULL | NULL
(3 rows)
И вот наши три режиссера без фильмов!
Обычно для фильтрации используется столбец id таблицы (WHERE movies.id IS NULL), но все столбцы таблицы movies имеют NULL, поэтому подойдет любой из них.
(Поскольку мы знаем, что все столбцы из таблицы movies будут NULL, в приведенном выше запросе мы могли бы просто написать SELECT directors.* вместо SELECT *, чтобы просто вернуть всю информацию о режиссерах).
Использование LEFT JOIN для поиска совпадений
В нашем предыдущем запросе мы нашли режиссеров, которые не принадлежали фильмам.
Используя ту же структуру, мы можем найти режиссеров, которые действительно принадлежат фильмам, изменив условие WHERE, чтобы искать строки, в которых данные о фильме не являются NULL:
SELECT *
FROM directors
LEFT JOIN movies
ON movies.director_id = directors.id
WHERE movies.id IS NOT NULL;
id | name | id | name | director_id
----+------------+----+---------+-------------
1 | John Smith | 1 | Movie 1 | 1
1 | John Smith | 2 | Movie 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2
(3 rows)
Это может показаться удобным, но на самом деле мы просто повторно реализовали INNER JOIN!
Множественные соединения
Мы уже видели, как объединить две таблицы, но как насчет нескольких объединений подряд?
На самом деле это довольно просто, но чтобы проиллюстрировать это, нам понадобится третья таблица: билеты.
В этой таблице будут представлены билеты, проданные на фильм:
CREATE TABLE tickets(
id SERIAL PRIMARY KEY,
movie_id INTEGER REFERENCES movies NOT NULL
);
INSERT INTO tickets(movie_id) VALUES (1), (1), (3);
В таблице билетов есть только идентификатор и ссылка на фильм: movie_id.
Мы также вставили два билета, проданных на фильм ID 1, и один билет, проданный на фильм ID 3.
Теперь давайте соединим режиссеров с фильмами, а затем фильмы с билетами!
SELECT *
FROM directors
INNER JOIN movies
ON movies.director_id = directors.id
INNER JOIN tickets
ON tickets.movie_id = movies.id;
Поскольку это внутренние соединения, порядок, в котором мы записываем соединения, не имеет значения. Мы могли бы начать с билетов, затем присоединиться к фильмам, а потом присоединиться к режиссерам.
Все снова сводится к тому, что вы пытаетесь запросить и что делает запрос наиболее понятным.
В нашем наборе результатов можно заметить, что мы еще больше сузили возвращаемые строки:
id | name | id | name | director_id | id | movie_id
----+------------+----+---------+-------------+----+----------
1 | John Smith | 1 | Movie 1 | 1 | 1 | 1
1 | John Smith | 1 | Movie 1 | 1 | 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2 | 3 | 3
(3 rows)
Это имеет смысл, потому что мы добавили еще один INNER JOIN. По сути, это добавляет еще одно условие «И» к нашему запросу.
Наш запрос, по сути, говорит: «Вернуть всех режиссеров, которые принадлежат к фильмам, на которые также есть продажи билетов».
Если бы вместо этого мы хотели найти режиссеров, которые принадлежат к фильмам, на которые еще не проданы билеты, мы могли бы заменить наш последний INNER JOIN на LEFT JOIN:
SELECT *
FROM directors
JOIN movies
ON movies.director_id = directors.id
LEFT JOIN tickets
ON tickets.movie_id = movies.id;
Мы видим, что фильм №2 снова появился в наборе результатов:
id | name | id | name | director_id | id | movie_id
----+------------+----+---------+-------------+------+----------
1 | John Smith | 1 | Movie 1 | 1 | 1 | 1
1 | John Smith | 1 | Movie 1 | 1 | 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2 | 3 | 3
1 | John Smith | 2 | Movie 2 | 1 | NULL | NULL
(4 rows)
У этого фильма не было продаж билетов, поэтому он был ранее исключен из набора результатов благодаря INNER JOIN.
Я оставлю это упражнение для читателей, но как найти режиссеров, которые принадлежат к фильмам, на которые не было продано ни одного билета?
Порядок выполнения соединения
В конце концов, нам неважно, в каком порядке выполняются соединения.
Одно из ключевых отличий SQL от других современных языков программирования заключается в том, что SQL - это декларативный язык.
Это означает, что мы указываем желаемый результат, но не указываем детали выполнения - эти детали остаются на усмотрение планировщика запросов к базе данных. Мы указываем нужные нам соединения и условия для них, а планировщик запросов делает все остальное.
Но в реальности база данных не будет соединять три таблицы одновременно. Вместо этого она, скорее всего, объединит первые две таблицы в один промежуточный результат, а затем присоединит этот промежуточный набор результатов к третьей таблице.
(Примечание: это несколько упрощенное объяснение).
Поэтому, работая с множественными соединениями в запросах, мы можем рассматривать их как серию соединений между двумя таблицами, хотя одна из этих таблиц может быть довольно большой.
Объединения с дополнительными условиями
Последняя тема, которую мы рассмотрим, — это объединение с дополнительными условиями.
Подобно предложению WHERE, мы можем добавить столько условий, сколько захотим.
Например, если мы хотим найти фильмы с режиссерами, которых не зовут «Джон Смит», мы можем добавить это дополнительное условие к нашему соединению с помощью AND:
SELECT *
FROM movies
INNER JOIN directors
ON directors.id = movies.director_id
AND directors.name <> 'John Smith';
Мы можем использовать любые операторы, которые мы бы поместили в предложение WHERE в этом условии присоединения.
Мы также получим тот же результат, если поместим условие в предложение WHERE:
SELECT *
FROM movies
INNER JOIN directors
ON directors.id = movies.director_id
WHERE directors.name <> 'John Smith';
Здесь есть несколько тонких различий, но для целей этой статьи набор результатов одинаков.
(Если вы не знакомы со всеми способами фильтрации SQL-запросов, ознакомьтесь с ранее упомянутой статьей здесь).
Реальность написания запросов с соединениями
На самом деле я использую соединения только в трех различных вариантах:
INNER JOIN
Первый вариант использования - это записи, в которых существует связь между двумя таблицами. Для этого используется соединение INNER JOIN.
Это такие ситуации, как поиск «фильмов, у которых есть режиссеры» или «пользователей с постами».
LEFT JOIN
Второй вариант использования - это записи из одной таблицы и, если существует связь, записи из второй таблицы. Для этого используется LEFT JOIN.
Это такие ситуации, как «фильмы с режиссерами, если у них есть режиссер» или «пользователи с постами, если у них есть посты».
Исключение LEFT JOIN
Третий наиболее распространенный случай использования - это наш второй случай использования LEFT JOIN: поиск записей в одной таблице, которые не имеют отношения ко второй таблице.
Это такие ситуации, как «фильмы без режиссеров» или «пользователи без сообщений».
Два самых полезных типа объединений
Не думаю, что я когда-либо использовал FULL OUTER JOIN или RIGHT JOIN в производственном приложении. Просто такие случаи не так часто встречаются, или запрос можно написать более понятным способом (в случае RIGHT JOIN).
Итак, хорошие новости! На самом деле существует только два типа объединений, которые необходимо понимать для 99,9% случаев использования: INNER JOIN и LEFT JOIN!