SQL, што можа быць прасцей? Кожны з нас можа напісаць прасценькі запыт - набіраем выбраць, пералічваем неабходныя калонкі, затым ад, імя табліцы, крыху умоў у дзе і ўсё - карысныя дадзеныя ў нас у кішэні, прычым (амаль) незалежна ад таго якая СКБД у гэты час знаходзіцца пад капотам (а можа і ). У выніку працу практычна з любой крыніцай дадзеных (рэляцыйным і не вельмі) можна разглядаць з пункта гледжання звычайнага кода (з усімі вынікаючымі - version control, code review, статычны аналіз, аўтатэсты і вось гэта ўсё). І гэта датычыцца не толькі саміх даных, схем і міграцый, а ўвогуле ўсёй жыццядзейнасці сховішча. У гэтым артыкуле пагаворым аб паўсядзённых задачах і праблемах працы з рознымі БД пад прыцэлам "database as code".
І пачнем прама з . Першыя батлы выгляду "SQL vs 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-кода. Дарэчы сказаць, распрацоўшчыкі ўсімі сіламі спрабуюць адгарадзіцца ад сваёй БД кіламетрамі абстракцый, што кажа аб некаторай .
Па іншым боку барыкад прыхільнікі чыстага "handmade"-SQL адзначаюць магчымасць выціскаць усе сокі са сваёй СКБД без дадатковых праслоек і абстракцый. У выніку чаго з'яўляюцца "data-centric" праекты, дзе базай займаюцца спецыяльна навучаныя людзі (яны ж "базісты", яны ж "базавікі", яны ж "базеншчыкі" і г.д.), а распрацоўнікам толькі застаецца "тузаць" гатовыя завірухі і захоўваемыя працэдуркі, не ўдаючыся ў падрабязнасці.
А што калі ўзяць лепшае з двух міроў? Як гэта зроблена ў выдатным інструменце з жыццесцвярджальнай назвай . Прывяду пару радкоў з агульнай канцэпцыі ў маім вольным перакладзе, а больш падрабязна з ёй можна пазнаёміцца .
Clojure гэта крутая мова для стварэння DSL'ей, але SQL ужо сам па сабе з'яўляецца крутым DSL, і нам не патрэбен яшчэ адзін. S-выразы выдатныя, але тут яны не дадаюць нічога новага. У выніку атрымліваем дужкі дзеля дужак. Не згодны? Тады дачакайцеся таго моманту калі абстракцыя над БД дасць цечу, і вы пачнеце дужанне з функцыяй (raw-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 асобна", вы атрымліваеце:
- Ніякіх сінтаксічных сюрпрызаў. Ваша база дадзеных (як і любая іншая) не адпавядае SQL стандарту на 100% – але для Yesql гэта не важна. Вы ніколі не будзеце марнаваць час на паляванне за функцыямі з сінтаксісам эквівалентным SQL. Вам ніколі не давядзецца вяртацца да функцыі (raw-sql "some ('funky' :: SYNTAX)")).
- Лепшая падтрымка рэдактара. Ваш рэдактар ужо мае выдатную падтрымку SQL. Захоўваючы SQL як SQL, вы можаце проста выкарыстоўваць яго.
- Камандная сумяшчальнасць. Вашы DBA могуць чытаць і пісаць SQL, які вы карыстаецеся ў сваім Clojure праекце.
- Прасцейшая настройка прадукцыйнасці. Патрэбна пабудаваць план для праблемнага запыту? Гэта не праблема, калі ваш запыт з'яўляецца звычайным SQL.
- Паўторнае выкарыстанне запытаў. Перацягнуць гэтыя ж SQL-файлы ў іншы праекты, таму што гэта проста стары добры SQL - проста падзяліцеся ім.
На маю ідэя вельмі крутая і пры гэтым вельмі простая, дзякуючы чаму праект набыў мноства на самых розных мовах. А мы далей паспрабуем ужыць падобную філасофію аддзялення SQL-кода ад усяго астатняга далёка за межамі ORM.
IDE & DB-менеджэры
Пачнём з простай паўсядзённай задачы. Часта нам даводзіцца шукаць якія-небудзь аб'екты ў БД, напрыклад, знайсці табліцу ў схеме і вывучыць яе структуру (якія выкарыстоўваюцца калонкі, ключы, азначнікі, канстрэйнты і іншае). І ад любой графічнай IDE або маломальского DB-manager'а, у першую чаргу, мы чакаем менавіта гэтых здольнасцяў. Каб было хутка і не прыйшлося чакаць па паўгадзіны, пакуль намалюецца акенца з патрэбнай інфармацыяй (асабліва пры павольным злучэнні з выдаленай БД), і пры гэтым каб атрыманая інфармацыя была свежай і актуальнай, а не закэшаваная старызна. Прычым чым складаней і буйней БД і больш іх колькасць, тым складаней гэта зрабіць.
Але звычайна я закідваю мыш куды далей і проста пішу код. Дапушчальны, неабходна пазнаць, якія табліцы (і з якімі ўласцівасцямі) утрымоўвацца ў схеме "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 не ўмее information_schema, затое ў яго ёсць , і вялікіх праблем не ўзнікае:
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 (дзе есць columnfamilies замест tables і keyspace'ы замест схем):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Для большасці астатніх БД таксама можна прыдумаць падобныя запыты (нават у Mongo ёсць , якая змяшчае ў сабе інфармацыю аб усіх калекцыях у сістэме).
Само сабой, такім спосабам можна атрымаць інфармацыю не толькі аб табліцах, а наогул аб любым аб'екце. Перыядычна добрыя людзі дзеляцца такім кодам для розных БД, як, напрыклад, у серыі хабра-артыкул "Функцыі для дакументавання баз дадзеных PostgreSQL" (, , ). Само сабой, трымаць усю гэтую гару запытаў у галаве і ўвесь час набіраць іх — гэта "такое сабе" задавальненне, таму ў каханай IDE/рэдактары ў мяне ёсць загадзя нарыхтаваны набор сніпетаў для часта выкарыстоўваных запытаў, і застаецца толькі ўдрукаваць імёны аб'ектаў у шаблон.
У выніку такі спосаб навігацыі і пошуку аб'ектаў значна больш гнуткі, эканоміць шмат часу, дазваляе атрымаць менавіта тую інфармацыю і ў тым выглядзе, у якім цяпер неабходна (як, напрыклад, апісана ў пасце. ).
Аперацыі з аб'ектамі
Пасля таго як мы знайшлі і вывучылі патрэбныя аб'екты, самы час з імі зрабіць што-небудзь карыснае. Натуральна, таксама не адрываючы пальцаў ад клавіятуры.
Не сакрэт, што простае выдаленне табліцы будзе выглядаць аднолькава амаль ва ўсіх БД:
drop table hr.personsА вось са стварэннем табліцы ўжо цікавей. Практычна любая СКБД (у тым ліку і шматлікія NoSQL) у тым ці іншым выглядзе ўмее "create table", і асноўная яго частка нават мала будзе адрознівацца (імя, спіс калонак, тыпы дадзеных), але астатнія дэталі могуць ашаламляльна адрознівацца і залежаць ад унутранага прылады і магчымасцяў канкрэтнай СКБД. Мой любімы прыклад - у дакументацыі Oracle толькі адны "голыя" БНФ'ы для сінтаксісу "create table" . Іншыя СКБД валодаюць больш сціплымі магчымасцямі, але кожная з іх таксама валодае мноствам цікавых і ўнікальных фіч па стварэнні табліц (, , , ). Ці наўрад які-небудзь графічны "wizard" з чарговай IDE (асабліва ўніверсальнай) зможа цалкам пакрыць усе гэтыя здольнасці, а калі і зможа, тое гэта будзе відовішча не для слабонервных. У той жа час правільна і своечасова напісаны аператар стварыць табліцу дазволіць без працы скарыстацца ўсімі з іх, зрабіць захоўванне і доступ да вашых дадзеных надзейным, аптымальным і максімальна камфортным.
Таксама ў многіх СКБД ёсць свае спецыфічныя тыпы аб'ектаў, якія адсутнічаюць у іншых СКБД. Прычым мы можам выконваць аперацыі не толькі над аб'ектамі БД, але і над самой СКБД, напрыклад "забіць" працэс, вызваліць якую-небудзь вобласць памяці, уключыць трасіроўку, перайсці ў рэжым "read only" і шматлікае іншае.
А цяпер крыху памалюем
Адна з самых распаўсюджаных задач - пабудаваць дыяграму з аб'ектамі БД, на прыгожым малюнку ўбачыць аб'екты і сувязі паміж імі. Гэта ўмее практычна любая графічная IDE, асобныя "command line"-ўтыліты, спецыялізаваныя графічныя тулы і мадэллеры. Якія вам нешта намалююць "як умеюць", а крыху паўплываць на гэты працэс можна толькі з дапамогай некалькіх параметраў у канфігурацыйным файле або галачак у інтэрфейсе.
Але гэтую праблему можна вырашыць значна прасцей, гнутчэй і элегантней, і вядома ж з дапамогай кода. Для пабудовы дыяграм любой складанасці ў нас ёсць адразу некалькі спецыялізаваных моў разметкі (DOT, GraphML etc), а да іх - цэлы россып прыкладанняў (GraphViz, PlantUML, Mermaid), якія ўмеюць чытаць такія інструкцыі і візуалізаваць у самых розных фарматах. Ну а інфармацыю аб аб'ектах і сувязях паміж імі мы ўжо ведаем як атрымаць.
Прывядзем невялікі прыклад таго, як гэта магло б выглядаць, з выкарыстаннем PlantUML і (злева 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-дыяграму:
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'
Калі ўважліва прыгледзецца, то пад капотам шматлікія прылады-візуалізатары сапраўды таксама выкарыстоўваюць падобныя запыты. Праўда, запыты гэтыя звычайна глыбока , не кажучы ўжо пра якую-небудзь іх мадыфікацыі.
Метрыкі і маніторынг
Пяройдзем да традыцыйна складанай тэмы - маніторынг прадукцыйнасці БД. Успомню невялікую true story, расказаную мне "адным маім сябрам". На чарговым праекце жыў-быў нейкі магутны DBA, і мала хто з распрацоўшчыкаў быў з ім знакам асабіста, ды і наогул бачыў калі-небудзь яго ў вочы (нягледзячы на тое, што працаваў ён, па чутках, недзе ў суседнім корпусе) . У гадзіну "X", калі poduction-сістэма буйнага рэтэйлера пачынала ў чарговы раз "дрэнна сябе адчуваць", ён моўчкі дасылаў скрыншоты графікаў з араклавага Enterprise Manager, на якіх беражліва вылучаў крытычныя месцы чырвоным маркерам для "зразумеласці" (гэта, мякка кажучы, мала дапамагала). І вось па гэтай "фотакартцы" даводзілася лячыць. Пры гэтым доступу да каштоўнага (у абодвух сэнсах гэтага слова) Enterprise Manager ні ў кога не было, т.я. сістэма складаная і дарагая, раптам "распрацоўнікі чаго-нітка наторкаюць і ўсё паламаюць". Таму распрацоўшчыкі "эмпірычным" шляхам знаходзілі месца і прычыну тармазоў і выпускалі патч. Калі грознае ліст ад DBA не прыходзіла паўторна хуткім часам, то ўсё з палёгкай выдыхалі і вярталіся да сваіх бягучых задач (да новага Ліста).
Але працэс маніторынгу можа выглядаць больш весела і прыязна, а самае галоўнае - даступна і празрыста для ўсіх. Хаця б базавая яго частка, як дадатак да асноўных сістэм маніторынгу (якія безумоўна карысныя і ў многіх выпадках незаменныя). Любая СКБД свабодна і абсалютна бязвыплатна гатова падзяліцца інфармацыяй аб сваім бягучым стане і прадукцыйнасці. У той жа самай "крывавай" Oracle DB практычна любую інфармацыю аб прадукцыйнасці можна атрымаць з сістэмных уяўленняў, пачынаючы ад працэсаў і сесій і заканчваючы станам буфернага кэша (напрыклад, , раздзел "Monitoring"). У Postgresql таксама ёсць цэлы россып сістэмных уяўленняў для , у прыватнасці такія незаменныя ў паўсядзённым жыцці любога DBA, як , , . У MySQL для гэтага прызначаная нават асобная схема . А У Mongo убудаваны агрэгуе дадзеныя аб прадукцыйнасці ў сістэмную калекцыю .
Г.зн., узброіўшыся якім-небудзь зборшчыкам метрык (Telegraf, Metricbeat, Collectd), які ўмее выконваць кастамныя sql-запыты, сховішчам гэтых метрык (InfluxDB, Elasticsearch, Timescaledb) і візуалізатарам (Grafana, Kibana), можна атрымаць дастаткова лёгкую гнуткую сістэму маніторынгу, якая будзе цесна інтэграваная з іншымі агульнасістэмнымі метрыкамі (атрымліваюцца, напрыклад, ад сервера прыкладанняў, ад АС і інш.). Як, напрыклад, гэта зроблена ў pgwatch2, дзе выкарыстоўваецца звязак InfluxDB + Grafana і набор запытаў да сістэмных уяўленняў, да якіх таксама можна .
Разам
І гэта толькі прыблізны пералік таго, што можна зрабіць з нашай БД з дапамогай звычайнага SQL-кода. Упэўнены, можна знайсці яшчэ мноства ужыванняў, пішыце ў каментарах. А пра тое, як (і самае галоўнае навошта) гэта ўсё зааўтаматызаваць і ўключыць у свой CI/CD pipeline мы пагаворым у наступны раз.
Крыніца: habr.com