Опит «База данни като код»

SQL, какво може да бъде по-лесно? Всеки от нас може да напише простичък заявка — просто въвеждаме select, изброяваме необходимите колони, след което from, името на таблицата, малко условия в where и всичко — полезните данни са в нашия джоб, като (почти) независимо от това коя СУБД работи в момента (а може и да не е СУБД изобщо). В резултат работа почти с всеки източник на данни (релационен и не много) може да се разглежда от гледна точка на обикновен код (с всички предпоставки — управление на версиите, преглед на кода, статичен анализ, автоматизирани тестове и всичко това). И това се отнася не само до самите данни, схеми и миграции, а всъщност до цялостната дейност на хранилището. В тази статия ще говорим за ежедневни задачи и проблеми при работа с различни БД под угледа на "база данни като код".

И започваме направо с ORM. Първите сблъсъци тип "SQL срещу ORM" бяха забелязани още в допетровска Русия.

Обектно-релационен мапинг

Подкрепящите ORM традиционно ценят скоростта и простотата на разработката, независимостта от СУБД и чистотата на кода. За много от нас кодът за работа с БД (а често и самата БД)

обикновено изглежда приблизително така…

@Entity
@Table(name = "stock", catalog = "maindb", uniqueConstraints = {
        @UniqueConstraint(columnNames = "STOCK_NAME"),
        @UniqueConstraint(columnNames = "STOCK_CODE") })
public class Stock implements java.io.Serializable {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "STOCK_ID", unique = true, nullable = false)
    public Integer getStockId() {
        return this.stockId;
    }
  ...

Моделът е обогатен с умни анотации, а някъде зад кулисите доблестният ORM генерира и изпълнява тонове SQL код. Като заудка, разработчиците полагат усилия да се разграничат от своята БД с километри абстракции, което показва за известна "ОМРАЗА КЪМ SQL".

От другата страна на барикадата поддръжниците на чистия "handmade"-SQL отбелязват възможността да извлекат максимума от своята СУБД без допълнителни слоеве и абстракции. В резултат на което се появяват "data-centric" проекти, където базата данни се грижи за специално обучен екип (те също са "даннисти", те също са "базовци", те също са "базденьщики" и т.н.), а разработчиците само трябва да "вземат" готови изгледи и съхранени процедури, без да навлизат в подробности.

А какво ако вземем най-доброто от двата свята? Както е направено в чудесния инструмент с жизнеутвърдително име Yesql. Ще цитирам няколко реда от общата концепция в моя свободен превод, а по-подробно с нея можете да се запознаете тук..

Clojure е страхотен език за създаване на DSL-и, но SQL сам по себе си вече е страхотен DSL и не ни е нужен още един. S-изразите са прекрасни, но тук те не добавят нищо ново. В крайна сметка получаваме скоби просто заради скобите. Не сте съгласни? Тогава изчакайте момент, когато абстракцията над БД започне да прелива и ще започнете борба с функцията. (raw-sql)

И какво да правим? Нека оставим SQL такъв, какъвто е — един файл за един запит:

-- name: users-by-country
select *
  from users
 where country_code = :country_code

… а след това прочетете този файл и го превърнете в обикновена Clojure функция:

(defqueries "some/where/users_by_country.sql"
   {:connection db-spec})

;;; Създадена е функция с име `users-by-country`.
;;; Нека я използваме:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)

Следвайки принципа "SQL отделно, Clojure отделно", получавате:

• Никаки синтактични изненади. Вашата база данни (както и всяка друга) не съответства на стандартите на SQL на 100% — но за Yesql това не е важно. Никога няма да губите време в търсене на функции със синтаксис, еквивалентен на SQL. Никога няма да ви се наложи да се връщате към функция (raw-sql "some (‘funky’ :: SYNTAX)")).
• Най-добра поддръжка на редактора. Вашият редактор вече има отлична поддръжка на SQL. Запазвайки SQL като SQL, можете просто да го използвате.
• Съвместимост с екипа. Вашите DBA могат да четат и пишат SQL, който използвате в своя Clojure проект.
• По-лесна настройка на производителността. Трябва да изградите план за проблемно запитване? Това не е проблем, когато вашето запитване е обикновен SQL.
• Повторно използване на запитвания. Преместете тези същите SQL файлове в други проекти, защото това е просто добрия стар SQL — просто го споделете.

Според мен идеята е много страхотна и същевременно много проста, благодарение на което проектът спечели много последователи на различни езици. А ние по-нататък ще се опитаме да приложим подобна философия за разделяне на SQL кода от всичко останало далеч извън ORM.

IDE & DB-мениджъри

Нека започнем с проста ежедневна задача. Често ни се налага да търсим определени обекти в БД, например, да намерим таблица в схемата и да изучим нейната структура (какви колони, ключове, индекси, ограничения и т.н. се използват). От всеки графичен IDE или малък DB-мениджър, на първо място, очакваме именно тези способности. За да бъде бързо и да не е нужно да чакаме половин час, докато се появи прозорец с необходимата информация (особено при бавна връзка с отдалечена БД), и за да бъде получената информация свежа и актуална, а не кеширана стара информация. Освен това, колкото по-сложна и обемна е БД и колкото повече са те, толкова по-трудно става това.

Но обикновено захвърлям мишката настрана и просто пиша код. Да предположим, че е необходимо да разберем кои таблици (и с какви свойства) се съдържат в схемата "HR". В повечето СУБД необходимият резултат може да бъде постигнат с такава проста заявка от information_schema:

select table_name
     , ...
  from information_schema.tables
 where schema = 'HR'

От база до база съдържанието на такива справочни таблици варира в зависимост от възможностите на всяка СУБД. И например, за MySQL от този същия справочник можем да получим специфични за тази СУБД параметри на таблицата:

select table_name
     , storage_engine -- Използван "движок" ("MyISAM", "InnoDB" и т.н.)
     , row_format     -- Формат на реда ("Fixed", "Dynamic" и т.н.)
     , ...
  from information_schema.tables
 where schema = 'HR'

Oracle не поддържа information_schema, но вместо това има метаданни на Oracle, и не възникват големи проблеми:

select table_name
     , pct_free       -- Минимално свободно място в блока с данни (%)
     , pct_used       -- Минимално използвано място в блока с данни (%)
     , last_analyzed  -- Дата на последния сбор на статистиката
     , ...
  from all_tables
 where owner = 'HR'

ClickHouse не е изключение:

select name
     , engine -- Използван "движок" ("MergeTree", "Dictionary" и т.н.)
     , ...
  from system.tables
 where database = 'HR'

Нещо подобно може да се направи и в Cassandra (където има columnfamilies вместо таблици и keyspace вместо схеми):

select columnfamily_name
     , compaction_strategy_class  -- Стратегия на събиране на боклуци
     , gc_grace_seconds           -- Време на живот на боклука
     , ...
  from system.schema_columnfamilies
 where keyspace_name = 'HR'

За повечето останали БД също може да се измислят подобни заявки (дори в Mongo има специална системна колекция, която съдържа информация за всички колекции в системата).

Разбира се, по този начин може да се получи информация не само за таблиците, а и за всякакви обекти. Периодично добри хора споделят такъв код за различни БД, например, в серия хабра-статии "Функции за документиране на бази данни PostgreSQL" (айб, бен, гим). Разбира се, да запомниш всичките тези заявки и постоянно да ги въвеждаш — това е "такова си" удоволствие, затова в любимата си IDE/редактор имам предварително подготвен набор от снипети за често използвани заявки, и остава само да впиша имената на обектите в шаблона.

В крайна сметка, такъв начин на навигация и търсене на обекти е много по-гъвкав, спестява много време, позволява да се получи точно тази информация и в този вид, в който е необходима в момента (както е описано в поста "Експорт на данни от БД в какъвто и да е формат: какво умеят IDE на платформата IntelliJ").

Операции с обекти

След като намерихме и изучихме нужните обекти, е време да направим нещо полезно с тях. Естествено, също така, без да отлепяме пръстите от клавиатурата.

Не е тайна, че простото изтриване на таблица изглежда по един и същ начин почти във всички БД:

drop table hr.persons

А създаването на таблица вече е по-интересно. Практически всяка СУБД (включително и много NoSQL) по един или друг начин умее "create table", а основната му част дори ще се различава малко (име, списък с колони, типове данни), но останалите детайли могат да се различават драстично и зависят от вътрешната структура и възможностите на конкретната СУБД. Моят любим пример — в документацията на Oracle само "голи" БНФ’и за синтаксиса "create table" отнемат 31 страница. Други СУБД разполагат с по-скромни възможности, но всяка от тях също има множество интересни и уникални функционалности за създаване на таблици (postgres, mysql, cockroach, cassandra). Малко вероятно е някой графичен "wizard" от поредната IDE (особено универсалната) да може напълно да покрие всичките тези способности, а ако и успее, то това ще е зрелище не за слабонервни. В същото време, правилно и навреме написаният оператор create table ще позволи без труда да се възползвате от всички тях, да направите съхранението и достъпа до вашите данни надеждни, оптимални и максимално удобни.

Съществуват и специфични типове обекти в различни СУБД, които липсват в други. Можем да извършваме операции не само с обектите на базата данни, но и със самата СУБД, например да "убием" процес, да освободим определена област от паметта, да активираме трасировка, да преминем в режим "read only" и много други.

А сега малко да нарисуваме

Една от най-често срещаните задачи е да се построи диаграма с обектите на базата данни, за да видим обектите и връзките помежду им в красив изображение. Това може да направи почти всяка графична IDE, отделни командни инструменти и специализирани графични инструменти и моделиращи приложения. Които ще нарисуват нещо "както могат", а малко да повлияете на този процес можете само с помощта на няколко параметъра в конфигурационния файл или отметки в интерфейса.

Но този проблем може да бъде решен много по-просто, по-гъвкаво и елегантно, разбира се с помощта на код. За изграждане на диаграми с всякаква сложност имаме няколко специализирани езика за маркиране (DOT, GraphML и т.н.), а към тях - цял куп приложения (GraphViz, PlantUML, Mermaid), които могат да четат такива инструкции и визуализират в най-разнообразни формати. А информацията за обектите и връзките между тях вече знаем как да получим.

Нека приведем малък пример за това как би могло да изглежда, като използваме PlantUML и демонстрационна база данни за PostgreSQL (вляво SQL заявка, която ще генерира нужната инструкция за PlantUML, а вдясно резултатът):

select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all
select distinct ccu.table_name || ' --|> ' ||
       tc.table_name as val
  from table_constraints as tc
  join key_column_usage as kcu
    on tc.constraint_name = kcu.constraint_name
  join constraint_column_usage as ccu
    on ccu.constraint_name = tc.constraint_name
 where tc.constraint_type = 'FOREIGN KEY'
   and tc.table_name ~ '.*' union all
select '@enduml'

Ако положим малко усилия, базирайки се на ER шаблона за PlantUML можем да получим нещо, което прилича на истинска ER диаграма:

SQL заявката е малко по-сложна

-- Заглавие
select '@startuml
        !define Table(name,desc) class name as "desc" << (T,#FFAAAA) >&gt;
        !define primary_key(x) <b>x</b>
        !define unique(x) <color:green>x</color>
        !define not_null(x) <u>x</u>
        hide methods
        hide stereotypes'
 union all
-- Таблици
select format('Table(%s, "%s n информация за %s") {'||chr(10), table_name, table_name, table_name) ||
       (select string_agg(column_name || ' ' || upper(udt_name), chr(10))
          from information_schema.columns
         where table_schema = 'public'
           and table_name = t.table_name) || chr(10) || '}'
  from information_schema.tables t
 where table_schema = 'public'
 union all
-- Връзки между таблиците
select distinct ccu.table_name || ' "1" --&gt; "0..N" ' || tc.table_name || format(' : "A %s може да има много %s"', ccu.table_name, tc.table_name)
  from information_schema.table_constraints as tc
  join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name
  join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
 where tc.constraint_type = 'FOREIGN KEY'
   and ccu.constraint_schema = 'public'
   and tc.table_name ~ '.*'
 union all
-- Футер
select '@enduml'

Ако погледнете внимателно, много от визуализаторските инструменти под капака точно така използват подобни заявки. Вярно е, че заявките обикновено са дълбоко "вградени" в самия код на приложението и сложни за разбиране, а да не говорим за каквато и да е тяхна модификация.

Метрики и мониторинг

Да преминем към традиционно сложната тема — мониторинг на производителността на базите данни. Спомням си една малка истинска история, разказана ми от "един мой приятел". В един проект живеел един могъщ DBA, и малко от разработчиците го познавали лично, даже го били виждали на живо (въпреки че се говореше, че работи някъде в съседната сграда). В часа "X", когато производствената система на голям търговец започвала отново да "не се чувства добре", той безмълвно изпращал скрийншоти от графики в Oracle Enterprise Manager, на които деликатно отбелязвал критичните места с червен маркер за "яснота" (това, меко казано, малко помагало). И така, по тази "фотография" се налагало да се търсят решения. При това никой нямал достъп до ценния (в двата смисъла на термина) Enterprise Manager, тъй като системата била сложна и скъпа, иначе "разработчиците ще натиснат нещо и всичко ще се счупи". Затова разработчиците "емпирично" намирали мястото и причината за забавянията и пускали пач. Ако страшното писмо от DBA не пристигне повторно в близко време, всички с облекчение дишали и се връщали към текущите си задачи (до новото Писмо).

Но процесът на мониторинг може да бъде по-весел, дружелюбен и, най-важното, достъпен и прозрачен за всички. Поне базовата му част, като допълнение към основните системи за мониторинг (които безспорно са полезни и в много случаи незаменими). Всяка СУБД с радост и напълно безплатно е готова да сподели информация за текущото си състояние и производителност. В същата "кървава" Oracle DB почти всяка информация за производителността може да бъде получена от системните представления, започвайки от процесите и сесиите и завършвайки със състоянието на буферния кеш (например, DBA Скриптове, раздел "Мониторинг"). В PostgreSQL също има множество системни представления за мониторинг на работата на БД, в частност такива незаменими в ежедневието на всеки DBA, като pg_stat_activity, pg_stat_database, pg_stat_bgwriter. За MySQL е създадена дори отделна схема performance_schema. А в Mongo вграден профайлер агрегира данни за производителността в системната колекция system.profile.

Така, въоръжени с някакъв метрик сборник (Telegraf, Metricbeat, Collectd), който може да изпълнява персонализирани SQL заявки, хранилище на тези метрики (InfluxDB, Elasticsearch, Timescaledb) и визуализатор (Grafana, Kibana), можем да получим доста лесна и гъвкава система за мониторинг, която ще бъде тясно интегрирана с други общосистемни метрики (получавани, например, от приложения, от ОС и т.н.). Както е направено в pgwatch2, където се използва комбинация от InfluxDB + Grafana и набор от заявки към системните представяния, към които също могат да се добавят персонализирани заявки.

Общо

И това е само приблизителен списък на това, което можем да направим с нашата база данни чрез обикновен SQL код. Сигурен съм, че можем да намерим още много приложения, пишете в коментарите. А за това как (и най-вече защо) да автоматизираме всичко това и да го включим в своя CI/CD pipeline ще поговорим следващия път.

Източник: habr.com