Как я отслеживаю свою музыкальную коллекцию с помощью простой базы данных SQLite

Резюме

SQLite идеально подходит для локального управления физической музыкальной коллекцией.
SQLite имеет простой процесс установки, подходящий для людей без опыта работы с базами данных. Графические интерфейсы, такие как DB Browser, также упрощают управление базами данных для начинающих пользователей.
Такие возможности SQLite, как динамическая типизация, позволяют эффективно управлять базами данных и настраивать их, обеспечивая оптимальную организацию.

Если вы меломан, у вас наверняка есть полки с пластинками или компакт-дисками. Как вы за ними следите? Вы можете использовать базу данных, но разве они не сложны в настройке? SQLite — это мощный инструмент, позволяющий вам настраивать базы данных SQL без сервера. На удивление легко настроить. Вот как я это сделал, несмотря на смутные знания SQL.

Почему SQLite?

Даже в эпоху потокового вещания в физической музыкальной коллекции все еще есть что-то особенное. Именно поэтому, по данным RIAA, продажи виниловых пластинок за последние несколько лет выросли. Самый популярный новый музыкальный формат — это не Spotify, MP3 или FLAC, а старый добрый LP.

Будучи миллениалом, мои привычки в прослушивании музыки сформировались в самом конце «Эры альбомов». Это означало коллекционирование альбомов. Поскольку это были 90-е, когда я начал серьезно интересоваться музыкой, доминирующим форматом был CD, за исключением нескольких старых чудаков, которые предпочитали винил. (Кому нужны эти старые пластинки?)

Когда у вас есть существенная физическая коллекция любого типа, не только музыка, одному человеку становится сложно за ней следить. Вот тут-то и появляются базы данных. Реляционная база данных с ее строками и столбцами проста в понимании, но SQL имеет репутацию сложного для изучения языка. Я познакомился с ним на занятиях по компьютерным информационным системам в колледже, но мне хотелось освоиться с базами данных и SQL.

Я думал об установке сервера базы данных на моей машине, например MySQL или PostgreSQL, но это большие, сложные системы, которые требуют много работы для обслуживания. Я просто парень с клавиатурой и ящиком альбомов.

Я также мог бы настроить базу данных в Discogs. У меня там есть учетная запись пользователя, но я хотел базу данных, которая работала бы локально под моим контролем. Я мог бы даже настроить ее как базу данных с плоским файлом, используя текстовые инструменты Linux, но это не масштабировалось бы до более крупной коллекции.

SQLite казался идеальным. Это была реляционная база данных, которая использовала SQL в качестве языка запросов. Она легкая и будет работать без сервера. Многие реальные приложения использовали SQLite, включая iTunes. Если SQLite может управлять цифровой музыкальной библиотекой, я подумал, что она легко справится и с физической. Возможно, именно поэтому коллекции записей являются популярными примерами для настройки реляционных баз данных. Они как «Hello, world!» среди баз данных.

Установка SQLite

Поскольку SQLite бесплатен и доступен во многих дистрибутивах Linux, мне нужно было всего лишь использовать apt в Ubuntu (он был в WSL, но в любой версии Debian или Ubuntu он будет одинаковым):

sudo apt установить sqlite

Вот и все. У меня был установлен SQLite в одном из них. Это показывает, насколько легко установить SQLite.

Я мог бы скачать родную версию Windows, но я просто предпочитаю Linux для программных вещей. Unix-подобные системы лучше всего подходят для программных проектов больших и малых, и именно для этого существует WSL.

Создание базы данных

После установки SQLite пришло время начать создавать мою базу данных. Я подумал о том, что мне понадобится. База данных для моей коллекции компакт-дисков и пластинок должна иметь поля для исполнителя, названия, года оригинального выпуска и формата.

Я запустил клиент командной строки, указав имя файла базы данных, который я хотел создать:

sqlite3 музыка.db

Я ввел код SQL для создания таблицы:

СОЗДАТЬ ТАБЛИЦУ альбомов (исполнитель,название,год,формат)

Завершайте каждый оператор SQL символом точки с запятой (;).

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

Хотя операторы SQL традиционно пишутся в верхнем регистре, это не обязательно. Это соглашение, но вы можете вводить команды в нижнем регистре, поскольку SQLite принимает и то, и другое. Разработчики SQLite используют нижний регистр в своей документации.

После создания таблицы мне теперь нужно было внести в нее записи, и я имею в виду записи базы данных, а не виниловые пластинки. Вот тут-то и пригодится команда INSERT INTO.

ВСТАВИТЬ В АЛЬБОМАХ ЦЕННОСТИ («Pink Floyd», «Темная сторона Луны», 1973,»LP»,)

Я мог бы сделать это для всей моей коллекции, но есть более простой способ. Ввод всех операторов SQL может быть утомительным, поэтому доступно несколько вспомогательных интерфейсов. Хороший из них — DB Browser. Он похож на PHPMyAdmin для баз данных MySQL. Это графический интерфейс, который позволяет вам легко создавать и управлять базами данных SQLite.

Мне пришлось всего лишь установить его в Ubuntu, хотя он также доступен и для Windows.

Чтобы установить его в Ubuntu, используйте эту команду:

sudo apt install sqlitebrowser

Для работы с базой данных SQLite я просто открываю файл music.db, который я создал, перейдя в Файл > Открыть базу данных > и перейдя к файлу базы данных. После создания базы данных я могу добавлять поля с помощью вкладки «Обзор данных», а затем нажать кнопку «Вставить запись». Она показывает таблицу, похожую на электронную таблицу. Я могу заполнить поля для имеющихся у меня альбомов. За кулисами она использует транзакции, упомянутые ниже, для защиты от нежелательных изменений.

Несмотря на то, что это личная база данных, я хочу выработать несколько хороших привычек. При работе с данными лучше всего иметь резервную копию. Клиент командной строки SQLite позволяет мне сохранять резервную копию моей базы данных с помощью команды .backup:

.резервная копия музыки.db.bak

Я мог бы назвать файл как угодно, но я обычно использую расширение .bak для резервных копий файлов.

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

Что еще важнее, если вы допустили ошибку, вы можете вернуться к предыдущим состояниям с помощью команды ROLLBACK. Это похоже на операцию отмены в текстовом процессоре.

Том Скотт на собственном горьком опыте узнал, насколько опасно пренебрегать транзакциями и резервным копированием при работе с базой данных:

Чтобы начать транзакцию, используйте эту команду:

НАЧАТЬ ТРАНЗАКЦИЮ;

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

СОВЕРШИТЬ;

Вот пример добавления нескольких альбомов в базу данных.

НАЧАТЬ ТРАНЗАКЦИЮ;
ВСТАВИТЬ В АЛЬБОМАХ ЦЕННОСТИ («Pearl Jam»,»Десятка»,1991,»CD»);
ВСТАВИТЬ В АЛЬБОМАХ ЦЕННОСТИ («Nirvana», «Nevermind»,1991,»CD»);
ВСТАВИТЬ В альбомы ЦЕННОСТИ («Pink Floyd», «Wish You Were Here», 1975, «Pink Floyd Records»);
СОВЕРШИТЬ;

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

Изучение моей базы данных

Поскольку моя база данных медленно заполняется, я могу попробовать сделать запрос. По умолчанию SQLite выводит записи, разделенные символом «|». Чтобы увидеть их в столбцах, я устанавливаю режим:

.столбец режима

Команды, являющиеся частью SQLite 3, подобные этой, не используют точку с запятой в конце.

Чтобы увидеть всю свою коллекцию, я могу использовать команду SELECT *:

ВЫБРАТЬ * ИЗ альбомов;

* — это подстановочный знак, похожий на подстановочные знаки в оболочке Linux, который позволяет мне выбрать каждую запись. Конечно, я хотел бы увидеть только несколько записей за один раз. Я могу сделать это с помощью команды LIMIT:

ВЫБРАТЬ * ИЗ АЛЬБОМА ОГРАНИЧЕНИЕ 5;

Чтобы найти поля, соответствующие критериям, например, исполнители, я могу использовать предложение WHERE:

ВЫБЕРИТЕ * ИЗ альбомов, ГДЕ исполнитель, НАПРИМЕР, ‘%pink floyd%’;

Операторы SQLite LIKE частично соответствуют полю и нечувствительны к регистру в SQLite.

Ой, мне нужно больше полей!

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

Чтобы посмотреть, как структурированы данные, я использую команду .schema.

Я открыл клиент командной строки и набрал:

ИЗМЕНИТЬ ТАБЛИЦУ альбомов ДОБАВИТЬ метку;

Я также мог бы легко сделать это через DB Browser. Я просто нажимаю на таблицу альбомов на вкладке «Структура базы данных» и нажимаю функцию «Изменить таблицу». Я могу добавлять или удалять строки, а также выбирать типы в качестве ограничения. Когда я вводил год выпуска в DB Browser, я заметил, что он не сортирует год выпуска правильно для записей, которые я добавлял. Я явно указал SQLite, что год выпуска — это число через меню, выбрав опцию «INTEGER», и это, похоже, решило проблему.

Можно добавить метку к уже добавленным альбомам с помощью команды UPDATE, например:

ОБНОВЛЕНИЕ альбомов УСТАНОВИТЬ метку = «Factory», ГДЕ исполнитель = «Joy Division»;

Для простых обновлений использование DB Browser может быть лучше, поскольку вы можете редактировать записи напрямую, и он автоматически использует транзакции для безопасности. Это также была возможность создать представление. Представление похоже на сохраненный поиск. Это позволяет вам сохранять сложные запросы, которые вы часто делаете.

Я создал один для своих виниловых альбомов:

СОЗДАТЬ ПРОСМОТР винила КАК ВЫБРАТЬ * ИЗ альбомов ГДЕ формат = «LP»;