Обзор
Если вы любите музыку, у вас, вероятно, есть полки с виниловыми пластинками или компакт-дисками. Но как же их отслеживать? Можно использовать базу данных, но разве они не слишком сложны для настройки? SQLite — это мощный инструмент, позволяющий создавать SQL-базы данных без необходимости в сервере. На самом деле, это довольно просто. Вот как я это сделал, несмотря на свои скромные знания в области SQL.
Почему SQLite?
Физическая музыкальная коллекция все еще привлекает внимание, даже в эпоху стриминга. Именно поэтому продажи виниловых пластинок значительно возросли за последние несколько лет, согласно данным RIAA. Самым популярным форматом музыки в наши дни остаётся добрый старый винил, а не Spotify, MP3 или FLAC.
Как представитель поколения миллениалов, я сформировал свои музыкальные предпочтения в конце "Эры альбомов". Это означало, что я собирал альбомы. Когда я начал серьезно увлекаться музыкой в 90-х, доминирующим форматом был компакт-диск, хотя были и некоторые старомодные любители винила. (Кто бы хотел эти старые пластинки?)
Когда у вас есть большая физическая коллекция чего-либо, не только музыки, одному человеку становится сложнее за ней следить. Вот тут и приходят на помощь базы данных. Реляционная база данных, состоящая из строк и столбцов, проста для понимания, но SQL имеет репутацию сложного для изучения языка. Я немного знакомился с ним на уроках информационных систем в колледже, но хотел стать более уверенным в работе с базами данных и SQL.
Я думал установить сервер базы данных на свой компьютер, например, MySQL или PostgreSQL, но эти системы громоздкие и сложные, требующие много усилий для обслуживания. Я просто парень с клавиатурой и ящиком альбомов.
Я мог бы также создать базу данных на Discogs. У меня там есть пользовательская учетная запись, но я предпочитал бы иметь базу данных, работающую локально и под моим контролем. Я мог бы даже создать её как простую текстовую базу данных с использованием инструментов Linux, но это ограничивало бы масштабируемость моет коллекции.
SQLite показалась мне идеальной. Это реляционная база данных, использующая SQL в качестве языка запросов. Она легковесная и функционирует без сервера. Многие реальные приложения используют SQLite, включая iTunes. Если SQLite может управлять цифровой музыкальной библиотекой, я подумал, что она справится и с физической коллекцией. Возможно, именно поэтому коллекции виниловых пластинок часто используют в примерах для настройки реляционных баз данных. Они как "Hello, world!" для баз данных.
Установка SQLite
Так как SQLite бесплатна и доступна во многих дистрибутивах Linux, мне нужно было всего лишь воспользоваться командой apt в Ubuntu (это было в WSL, но на любой версии Debian или Ubuntu будет то же самое):
Вот и всё. Установка SQLite заняла всего одно действие. Это демонстрирует, насколько легко можно установить SQLite.
Я мог бы скачать версию для Windows, но предпочитаю Linux для программирования. Unix-подобные системы лучше подходят для проектов разработки, и именно поэтому существует WSL.
Создание базы данных
С установленным SQLite пришло время создавать мою базу данных. Я подумал, какие поля мне нужны. База данных для моей коллекции компакт-дисков и виниловых пластинок должна содержать исполнителя, название, год первоначального релиза и формат.
Я запустил командный клиент с именем файла базы данных, который хочу создать:
Я ввёл SQL-код для создания таблицы:
Каждое SQL-выражение заканчивается символом точки с запятой (;).
Хорошая новость об SQLite по сравнению с другими системами баз данных заключается в том, что мне не нужно заранее жестко задавать типы данных. SQLite использует динамическую типизацию, определяя тип данных в поле на основе введённой информации. Если я ввожу строку, она понимает, что это строка. Это сэкономило мне время и усилия на определение структуры таблицы.
Хотя SQL-операторы традиционно пишутся заглавными буквами, это не является обязательным. Это просто привычка, а команды можно вводить в нижнем регистре, так как SQLite принимает оба варианта. Разработчики SQLite предпочитают использовать нижний регистр в своей документации.
С созданной таблицей мне нужно было ввести данные в неё, и я имею в виду записи баз данных, а не виниловые пластинки. Для этого используется команда INSERT INTO.
Я мог бы сделать это для всей своей коллекции, но есть более простой способ. Вводить все SQL-команды вручную может быть утомительно, поэтому существуют несколько удобных интерфейсов. Один из них — DB Browser. Он напоминает PHPMyAdmin для баз данных MySQL. Это графический интерфейс, который позволяет легко создавать и управлять базами данных SQLite.
Мне нужно было просто установить его в Ubuntu, хотя оно также доступно для Windows.
Чтобы установить его в Ubuntu, используйте эту команду:
Для работы с базой данных SQLite мне просто нужно открыть файл music.db, который я создал, выбрав Файл > Открыть базу данных > и перейдя к файлу базы данных. С созданной базой данных я могу добавлять поля с помощью вкладки "Просмотр данных", а затем нажать кнопку "Вставить запись". Она отображает таблицу, аналогичную электронной таблице. Я могу заполнить поля для альбомов, которые у меня есть. За кулисами используется механизм транзакций, о котором будет рассказано позже, чтобы предотвратить нежелательные изменения.
Несмотря на то, что это личная база данных, я хочу придерживаться нескольких полезных привычек. Работая с данными, лучше иметь резервную копию. Командный клиент SQLite позволяет мне сохранить резервную копию базы данных с помощью команды .backup:
Я мог бы назвать файл как угодно, но обычно использую расширение .bak для резервных копий.
Когда вы работаете с базой данных, вам нужен способ откатить изменения, если что-то пошло не так. SQLite, как и многие другие системы баз данных, имеет систему транзакций. В транзакциях операция либо завершается полностью, либо нет.
Кроме того, если вы допустили ошибку, вы можете откатиться к предыдущему состоянию с помощью команды ROLLBACK. Это похоже на команду "отменить" в текстовом процессоре.
Том Скотт жестоко усвоил урок о последствиях игнорирования использования транзакций и резервных копий при работе с базой данных:
Чтобы начать транзакцию, используйте эту команду:
Все действия, которые вы выполняете с базой данных, такие как добавление, удаление или изменение записей, будут видны, но изменения не будут сохранены до завершения транзакции с командой COMMIT:
Вот пример добавления нескольких альбомов в базу данных:
Создавайте резервные копии и включайте транзакции перед внесением изменений в базу данных.
Исследуем мою базу данных
Постепенно заполняя свою базу данных, я мог начать делать запросы к ней. По умолчанию SQLite выводит записи, разделённые символом "|". Чтобы увидеть их в столбцах, я установил режим:
Команды, входящие в состав SQLite 3, как эта, не требуют точки с запятой в конце.
Чтобы увидеть всю свою коллекцию, я мог бы использовать команду SELECT *:
Звездочка (*) — это подстановка, подобная подстановкам в командной строке Linux, которая позволяет выбрать каждую запись. Конечно, я хотел бы видеть только несколько записей за раз. Я могу сделать это с помощью команды LIMIT:
Чтобы найти записи, соответствующие определённым критериям, таким как исполнители, я мог бы использовать оператор WHERE:
Операторы LIKE в SQLite позволяют частично совпадать с полем и не чувствительны к регистру.
Ой, мне нужно больше полей!
Даже при тщательном проектировании полей в базе данных, порой возникает необходимость внести изменения в таблицы, когда понимаешь, что нужно добавить ещё одно поле. Я осознал, что неплохо было бы добавить поле для лейбла записи. К счастью, изменить таблицу в SQLite несложно.
Чтобы увидеть, как структурированы данные, я использую команду .schema.
Я открыл командный клиент и ввёл;
Я также мог бы сделать это просто через DB Browser. Мне нужно лишь нажать на таблицу альбомов на вкладке "Структура базы данных" и выбрать функцию "Изменить таблицу". Я могу добавлять или удалять строки, а также задавать типы данных в качестве ограничения. Когда я вводил год релиза в DB Browser, я заметил, что он неправильно сортировал год релиза для добавленных записей. Я уточнил SQLite, что год релиза является числом через меню, выбрав опцию "INTEGER", и это, похоже, устранило проблему.
Я могу добавить лейбл к уже введённым альбомам с помощью команды UPDATE, как в этом примере:
Для простых обновлений лучше использовать DB Browser, так как вы можете редактировать записи напрямую, и он автоматически использует транзакции для безопасности. В это же время я создал представление. Представление похоже на сохранённый поиск. Оно позволяет сохранять сложные запросы, которые вы часто выполняете.
Я создал одно для своих виниловых альбомов:
Чтобы вернуться к своему представлению, я просто запускаю этот запрос, как будто представление — это другая таблица:
Теперь я могу продолжать добавлять в и исследовать свою музыкальную коллекцию, и SQLite сделала это возможным.
Как я использовал QR-коды и Google Sheets, чтобы организовать свой дом
Прекратите бессмысленно искать вещи, которые вы знаете, что у вас есть.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете читать наши материалы в:
- Telegram: https://t.me/gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru