Опит „База данни като код“.

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

И да започнем направо от 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 мразя“.

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

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

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

И така, какво трябва да направя? Нека оставим 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})

;;; A function with the name `users-by-country` has been created.
;;; Let's use it:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)

Като се придържате към принципа „SQL сам по себе си, Clojure сам по себе си“, получавате:

• Няма синтактични изненади. Вашата база данни (както всяка друга) не е 100% съвместима със стандарта SQL - но това няма значение за 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" etc)
     , row_format     -- Формат строки ("Fixed", "Dynamic" etc)
     , ...
  from information_schema.tables
 where schema = 'HR'

Oracle не знае информация_схема, но има Метаданни на Oracle, и не възникват големи проблеми:

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

ClickHouse не прави изключение:

select name
     , engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
     , ...
  from system.tables
 where database = 'HR'

Нещо подобно може да се направи в Cassandra (която има семейства колони вместо таблици и ключови пространства вместо схеми):

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

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

Разбира се, по този начин можете да получите информация не само за таблици, но и за всеки обект като цяло. От време на време мили хора споделят такъв код за различни бази данни, като например в поредицата от статии на habra „Функции за документиране на бази данни PostgreSQL“ (Ayb, Бен, фитнес). Разбира се, поддържането на цялата тази планина от заявки в главата ми и постоянното им въвеждане е такова удоволствие, така че в любимия ми IDE/редактор имам предварително подготвен набор от фрагменти за често използвани заявки и всичко, което остава, е да напиша имена на обекти в шаблона.

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

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

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

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

drop table hr.persons

Но със създаването на масата става по-интересно. Почти всяка СУБД (включително много NoSQL) може да „създаде таблица“ под една или друга форма и основната част от нея дори ще се различава леко (име, списък с колони, типове данни), но други подробности могат да се различават драматично и зависят от вътрешно устройство и възможности на конкретна СУБД. Любимият ми пример е, че в документацията на Oracle има само „голи“ BNF за синтаксиса „създаване на таблица“ заемат 31 страници. Други СУБД имат по-скромни възможности, но всяка от тях също има много интересни и уникални функции за създаване на таблици (Postgres, MySQL, хлебарка, Касандра). Малко вероятно е някой графичен „магьосник“ от друга IDE (особено универсална) да може да покрие напълно всички тези способности, а дори и да може, няма да е спектакъл за хора със слаби сърца. В същото време правилно и навременно написано изявление създайте таблица ще ви позволи лесно да използвате всички тях, ще направи съхранението и достъпа до вашите данни надеждни, оптимални и възможно най-удобни.

Освен това много СУБД имат свои специфични типове обекти, които не са налични в други СУБД. Освен това можем да извършваме операции не само върху обекти на база данни, но и върху самата СУБД, например да „убием“ процес, да освободим част от паметта, да активираме проследяване, да превключим в режим „само за четене“ и много други.

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

Една от най-често срещаните задачи е да се изгради диаграма с обекти на база данни и да се видят обектите и връзките между тях в красива картина. Почти всяка графична 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) >>
        !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 information about %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" --> "0..N" ' || tc.table_name || format(' : "A %s may haven many %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_база данни, pg_stat_bgwriter. MySQL дори има отделна схема за това. схема_за_производителност. A В Mongo вграден профайлър агрегира данни за ефективността в системна колекция system.profile.

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

Общо

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

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