SQL, wat kan eenvoudiger wees? Elkeen van ons kan 'n eenvoudige versoek skryf - ons tik kies, lys die vereiste kolomme, dan van, tabelnaam, sommige voorwaardes in waar en dit is al - nuttige data is in ons sak, en (amper) ongeag watter DBMS op daardie tydstip onder die enjinkap is (of dalk ). As gevolg hiervan kan werk met byna enige databron (relasioneel en nie so nie) beskou word vanuit die oogpunt van gewone kode (met alles wat dit impliseer - weergawebeheer, kodehersiening, statiese analise, outotoetse, en dit is al). En dit geld nie net vir die data self, skemas en migrasies nie, maar in die algemeen vir die hele lewe van die stoor. In hierdie artikel sal ons praat oor alledaagse take en probleme om met verskeie databasisse te werk onder die lens van "databasis as kode".
En kom ons begin reg van . Die eerste gevegte van die tipe "SQL vs ORM" is terug opgemerk .
Objek-relasionele kartering
ORM-ondersteuners waardeer tradisioneel spoed en gemak van ontwikkeling, onafhanklikheid van die DBBS en skoon kode. Vir baie van ons is die kode vir die werk met die databasis (en dikwels die databasis self)
dit lyk gewoonlik so...
@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;
}
...Die model is gehang met slim aantekeninge, en iewers agter die skerms genereer en voer 'n dapper ORM tonne SQL-kode uit. Terloops, ontwikkelaars probeer hul bes om hulself van hul databasis te isoleer met kilometers se abstraksies, wat dui op sommige .
Aan die ander kant van die versperrings merk aanhangers van suiwer "handgemaakte" SQL op die vermoë om al die sap uit hul DBBS te druk sonder bykomende lae en abstraksies. Gevolglik verskyn "data-sentriese" projekte, waar spesiaal opgeleide mense by die databasis betrokke is (hulle is ook "basiciste", hulle is ook "basiciste", hulle is ook "basdeners", ens.), en die ontwikkelaars hoef net die klaargemaakte aansigte en gestoorde prosedures te "trek", sonder om in besonderhede in te gaan.
Wat as ons die beste van albei wêrelde gehad het? Hoe dit gedoen word in 'n wonderlike hulpmiddel met 'n lewensbevestigende naam . Ek gee 'n paar reëls uit die algemene konsep in my vrye vertaling, en jy kan in meer besonderhede daarmee kennis maak .
Clojure is 'n oulike taal om DSL's te skep, maar SQL self is 'n koel DSL, en ons het nie 'n ander een nodig nie. S-uitdrukkings is wonderlik, maar hulle voeg niks nuuts hier by nie. As gevolg hiervan kry ons hakies ter wille van hakies. Stem nie saam nie? Wag dan vir die oomblik wanneer die abstraksie oor die databasis begin lek en jy begin baklei met die funksie (raw-sql)
So wat moet ek doen? Kom ons laat SQL as gewone SQL - een lêer per versoek:
-- name: users-by-country
select *
from users
where country_code = :country_code... en lees dan hierdie lêer en verander dit in 'n gewone Clojure-funksie:
(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" ...} ...)Deur aan die "SQL op sigself, Clojure op sigself"-beginsel te voldoen, kry jy:
- Geen sintaktiese verrassings nie. Jou databasis (soos enige ander) voldoen nie 100% aan die SQL-standaard nie - maar dit maak nie saak vir Yesql nie. Jy sal nooit tyd mors op soek na funksies met SQL-ekwivalente sintaksis nie. Jy sal nooit hoef terug te keer na 'n funksie nie (raw-sql "sommige('funky'::SYNTAX)")).
- Beste redakteurondersteuning. Jou redigeerder het reeds uitstekende SQL-ondersteuning. Deur SQL as SQL te stoor kan jy dit eenvoudig gebruik.
- Spanversoenbaarheid. Jou DBA's kan die SQL wat jy in jou Clojure-projek gebruik, lees en skryf.
- Makliker prestasie-instelling. Moet jy 'n plan bou vir 'n problematiese navraag? Dit is nie 'n probleem wanneer jou navraag gereelde SQL is nie.
- Hergebruik van navrae. Sleep en los daardie selfde SQL-lêers in ander projekte, want dit is net ou SQL - deel dit net.
Na my mening is die idee baie gaaf en terselfdertyd baie eenvoudig, waardeur die projek baie bygekry het in 'n verskeidenheid tale. En ons sal volgende probeer om 'n soortgelyke filosofie toe te pas om SQL-kode te skei van alles ver buite die ORM.
IO & DB bestuurders
Kom ons begin met 'n eenvoudige alledaagse taak. Dikwels moet ons vir sommige voorwerpe in die databasis soek, byvoorbeeld, 'n tabel in die skema vind en die struktuur daarvan bestudeer (watter kolomme, sleutels, indekse, beperkings, ens. word gebruik). En van enige grafiese IDE of 'n bietjie DB-bestuurder verwag ons eerstens presies hierdie vermoëns. Sodat dit vinnig is en jy nie 'n halfuur hoef te wag totdat 'n venster met die nodige inligting geteken is nie (veral met 'n stadige verbinding met 'n afgeleë databasis), en terselfdertyd is die inligting wat ontvang word vars en relevant, en nie gemors in die kas nie. Boonop, hoe meer kompleks en groter die databasis en hoe groter die aantal, hoe moeiliker is dit om dit te doen.
Maar gewoonlik gooi ek die muis weg en skryf net kode. Kom ons sê jy moet uitvind watter tabelle (en met watter eienskappe) in die "HR"-skema vervat is. In die meeste DBBS'e kan die gewenste resultaat bereik word met hierdie eenvoudige navraag vanaf information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Van databasis tot databasis, die inhoud van sulke verwysingstabelle verskil na gelang van die vermoëns van elke DBBS. En, byvoorbeeld, vir MySQL, uit dieselfde naslaanboek kan u tabelparameters spesifiek vir hierdie DBMS kry:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle ken nie information_schema nie, maar dit het wel , en geen groot probleme ontstaan nie:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse is geen uitsondering nie:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Iets soortgelyks kan in Cassandra gedoen word (wat kolomfamilies in plaas van tabelle en sleutelspasies in plaas van skemas het):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Vir die meeste ander databasisse kan jy ook met soortgelyke navrae vorendag kom (selfs Mongo het , wat inligting oor alle versamelings in die stelsel bevat).
Natuurlik, op hierdie manier kan jy nie net inligting oor tabelle kry nie, maar oor enige voorwerp in die algemeen. Van tyd tot tyd deel vriendelike mense sulke kode vir verskillende databasisse, soos byvoorbeeld in die reeks habra-artikels “Funksies vir die dokumentasie van PostgreSQL-databasisse” (, , ). Dit is natuurlik so 'n plesier om hierdie hele berg navrae in my kop te hou en dit gedurig te tik, so in my gunsteling IDE/redigeerder het ek 'n vooraf-voorbereide stel brokkies vir gereeld gebruikte navrae, en al wat oorbly is om die voorwerpname in die sjabloon.
As gevolg hiervan is hierdie metode om na voorwerpe te navigeer en te soek baie meer buigsaam, spaar baie tyd en laat jou toe om presies die inligting te kry in die vorm waarin dit nou nodig is (soos byvoorbeeld beskryf in die pos ).
Bewerkings met voorwerpe
Nadat ons die nodige voorwerpe gevind en bestudeer het, is dit tyd om iets nuttigs daarmee te doen. Natuurlik ook sonder om jou vingers van die sleutelbord af te haal.
Dit is geen geheim dat die uitvee van 'n tabel dieselfde sal lyk in byna alle databasisse nie:
drop table hr.personsMaar met die skepping van die tafel word dit interessanter. Byna enige DBBS (insluitend baie NoSQL) kan "tabel" in een of ander vorm skep, en die hoofgedeelte daarvan sal selfs effens verskil (naam, lys van kolomme, datatipes), maar ander besonderhede kan dramaties verskil en afhang van die interne toestel en vermoëns van 'n spesifieke DBBS. My gunsteling voorbeeld is dat daar in die Oracle-dokumentasie slegs "naakte" BNF'e is vir die "skep tabel"-sintaksis . Ander DBBS'e het meer beskeie vermoëns, maar elkeen van hulle het ook baie interessante en unieke kenmerke om tabelle te skep (, , , ). Dit is onwaarskynlik dat enige grafiese "towenaar" van 'n ander IDE (veral 'n universele een) al hierdie vermoëns ten volle sal kan dek, en selfs al kan dit, sal dit nie 'n skouspel wees vir die flou van hart nie. Terselfdertyd 'n korrek en tydige geskrewe verklaring tabel skep sal jou toelaat om almal maklik te gebruik, berging en toegang tot jou data betroubaar, optimaal en so gemaklik as moontlik te maak.
Baie DBBS'e het ook hul eie spesifieke tipes voorwerpe wat nie in ander DBBS'e beskikbaar is nie. Boonop kan ons nie net operasies op databasisvoorwerpe uitvoer nie, maar ook op die DBMS self, byvoorbeeld, 'n proses "doodmaak", 'n mate van geheue-area vrymaak, opsporing aktiveer, oorskakel na "leesalleen"-modus, en nog baie meer.
Kom ons teken nou 'n bietjie
Een van die mees algemene take is om 'n diagram met databasisvoorwerpe te bou en die voorwerpe en verbande tussen hulle in 'n pragtige prentjie te sien. Byna enige grafiese IDE, aparte "opdragreël"-nutsprogramme, gespesialiseerde grafiese gereedskap en modelleerders kan dit doen. Hulle sal iets vir jou teken "so goed hulle kan," en jy kan hierdie proses net 'n bietjie beïnvloed met behulp van 'n paar parameters in die konfigurasielêer of merkblokkies in die koppelvlak.
Maar hierdie probleem kan baie eenvoudiger, meer buigsaam en elegant opgelos word, en natuurlik met behulp van kode. Om diagramme van enige kompleksiteit te skep, het ons verskeie gespesialiseerde opmaaktale (DOT, GraphML, ens.), en vir hulle 'n hele verspreiding van toepassings (GraphViz, PlantUML, Mermaid) wat sulke instruksies kan lees en dit in 'n verskeidenheid formate kan visualiseer . Wel, ons weet reeds hoe om inligting oor voorwerpe en verbindings tussen hulle te kry.
Hier is 'n klein voorbeeld van hoe dit kan lyk, met behulp van PlantUML en (aan die linkerkant is 'n SQL-navraag wat die vereiste instruksie vir PlantUML sal genereer, en aan die regterkant is die resultaat):
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'En as jy 'n bietjie probeer, dan gebaseer op jy kan iets kry wat baie soortgelyk is aan 'n regte ER-diagram:
Die SQL-navraag is 'n bietjie meer ingewikkeld
-- Шапка
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'
As jy mooi kyk, onder die enjinkap gebruik baie visualiseringsinstrumente ook soortgelyke navrae. Dit is waar, hierdie versoeke is gewoonlik diep , om nie eens te praat van enige wysiging daarvan nie.
Metrieke en monitering
Kom ons gaan oor na 'n tradisioneel komplekse onderwerp - databasisprestasiemonitering. Ek onthou 'n klein ware storie wat vir my vertel is deur "een van my vriende." Op 'n ander projek het daar 'n sekere kragtige DBA gewoon, en min van die ontwikkelaars het hom persoonlik geken, of hom ooit persoonlik gesien (ten spyte van die feit dat hy, volgens gerugte, iewers in die volgende gebou gewerk het). Op uur “X”, toe die poduksiestelsel van ’n groot kleinhandelaar weer begin “sleg” voel, het hy stil-stil skermkiekies van grafieke van Oracle Enterprise Manager gestuur, waarop hy kritieke plekke versigtig uitgelig het met ’n rooi merker vir “begryplikheid” ( dit, om dit sagkens te stel, het nie veel gehelp nie). En op grond van hierdie "fotokaart" wat ek moes behandel. Terselfdertyd het niemand toegang gehad tot die kosbare (in albei betekenisse van die woord) Ondernemingsbestuurder nie, want die stelsel is kompleks en duur, skielik "strompel die ontwikkelaars oor iets en breek alles." Daarom het die ontwikkelaars "empiries" die ligging en oorsaak van die remme gevind en 'n pleister vrygestel. As die dreigende brief van die DBA nie in die nabye toekoms weer opdaag nie, dan sou almal 'n sug van verligting slaak en terugkeer na hul huidige take (tot die nuwe Brief).
Maar die moniteringsproses kan lekkerder en vriendeliker lyk, en bowenal, toeganklik en deursigtig vir almal. Ten minste sy basiese deel, as 'n toevoeging tot die hoofmoniteringstelsels (wat beslis nuttig en in baie gevalle onvervangbaar is). Enige DBBS is vrylik en absoluut gratis om inligting oor sy huidige toestand en prestasie te deel. In dieselfde "bloedige" Oracle DB kan byna enige inligting oor werkverrigting verkry word vanaf stelselaansigte, wat wissel van prosesse en sessies tot die toestand van die bufferkas (byvoorbeeld, , afdeling "Monitoring"). Postgresql het ook 'n hele klomp stelselaansigte vir , in die besonder dié wat onontbeerlik is in die daaglikse lewe van enige DBA, soos , , . MySQL het selfs 'n aparte skema hiervoor. . A In Mongo ingebou versamel prestasiedata in 'n stelselversameling .
Dus, gewapen met 'n soort maatstafversamelaar (Telegraf, Metricbeat, Collectd) wat pasgemaakte sql-navrae kan uitvoer, 'n berging van hierdie maatstawwe (InfluxDB, Elasticsearch, Timescaledb) en 'n visualiseerder (Grafana, Kibana), kan jy 'n redelik maklike en 'n buigsame moniteringstelsel wat nou geïntegreer sal wees met ander stelselwye maatstawwe (byvoorbeeld verkry vanaf die toepassingsbediener, vanaf die bedryfstelsel, ens.). Soos byvoorbeeld, word dit gedoen in pgwatch2, wat die InfluxDB + Grafana-kombinasie en 'n stel navrae na stelselaansigte gebruik, wat ook toeganklik is .
In totaal
En dit is slegs 'n benaderde lys van wat met ons databasis gedoen kan word deur gewone SQL-kode te gebruik. Ek is seker jy kan baie meer gebruike vind, skryf in die kommentaar. En ons sal praat oor hoe (en die belangrikste hoekom) om dit alles te outomatiseer en volgende keer in jou CI/CD-pyplyn in te sluit.
Bron: will.com