SQL, hva kan være enklere? Hver av oss kan skrive en enkel forespørsel - vi skriver velg, liste opp de nødvendige kolonnene, og deretter fra, tabellnavn, noen betingelser i hvor og det er alt - nyttig data er i lommen vår, og (nesten) uavhengig av hvilket DBMS som er under panseret på det tidspunktet (eller kanskje ). Som et resultat kan det å jobbe med nesten hvilken som helst datakilde (relasjonelt og ikke) vurderes fra synspunktet til vanlig kode (med alt det innebærer - versjonskontroll, kodegjennomgang, statisk analyse, autotester, og det er alt). Og dette gjelder ikke bare selve dataene, skjemaer og migreringer, men generelt hele lagringens levetid. I denne artikkelen vil vi snakke om dagligdagse oppgaver og problemer med å jobbe med ulike databaser under linsen "database som kode".
Og la oss starte rett fra . De første kampene av typen "SQL vs ORM" ble lagt merke til igjen .
Objektrelasjonell kartlegging
ORM-supportere verdsetter tradisjonelt hastighet og enkel utvikling, uavhengighet fra DBMS og ren kode. For mange av oss er koden for å jobbe med databasen (og ofte selve databasen)
det ser vanligvis sånn ut...
@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;
}
...Modellen er hengt med smarte merknader, og et sted bak kulissene genererer og utfører en tapper ORM tonnevis av SQL-kode. Utviklere prøver forresten sitt beste for å isolere seg fra databasen med kilometerlange abstraksjoner, noe som indikerer noen .
På den andre siden av barrikadene merker tilhengere av ren "håndlaget" SQL muligheten til å presse all saften ut av DBMS uten ekstra lag og abstraksjoner. Som et resultat dukker det opp "datasentriske" prosjekter, der spesialtrente personer er involvert i databasen (de er også "basicister", de er også "basicister", de er også "basdenere", etc.), og utviklerne trenger bare å "trekke" de ferdige visningene og lagrede prosedyrene, uten å gå i detaljer.
Hva om vi hadde det beste fra begge verdener? Hvordan dette gjøres i et fantastisk verktøy med et livsbekreftende navn . Jeg vil gi et par linjer fra det generelle konseptet i min gratis oversettelse, og du kan gjøre deg mer detaljert kjent med det .
Clojure er et kult språk for å lage DSL-er, men SQL i seg selv er en kul DSL, og vi trenger ikke en til. S-uttrykk er flotte, men de tilfører ikke noe nytt her. Som et resultat får vi parentes for parentes skyld. Er ikke enig? Vent så til øyeblikket når abstraksjonen over databasen begynner å lekke og du begynner å kjempe med funksjonen (raw-sql)
Så hva bør jeg gjøre? La oss la SQL være vanlig SQL - én fil per forespørsel:
-- name: users-by-country
select *
from users
where country_code = :country_code... og les deretter denne filen, og gjør den om til en vanlig Clojure-funksjon:
(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" ...} ...)Ved å følge "SQL av seg selv, Clojure av seg selv"-prinsippet får du:
- Ingen syntaktiske overraskelser. Databasen din (som alle andre) er ikke 100 % kompatibel med SQL-standarden - men dette spiller ingen rolle for Yesql. Du vil aldri kaste bort tid på å lete etter funksjoner med SQL-ekvivalent syntaks. Du trenger aldri å gå tilbake til en funksjon (raw-sql "some('funky'::SYNTAX)")).
- Beste redaktørstøtte. Redaktøren din har allerede utmerket SQL-støtte. Ved å lagre SQL som SQL kan du ganske enkelt bruke den.
- Lagkompatibilitet. Dine DBA-er kan lese og skrive SQL-en du bruker i Clojure-prosjektet ditt.
- Enklere ytelsesinnstilling. Trenger du å lage en plan for et problematisk spørsmål? Dette er ikke et problem når søket ditt er vanlig SQL.
- Gjenbruk av spørringer. Dra og slipp de samme SQL-filene inn i andre prosjekter fordi det bare er ren gammel SQL - bare del den.
Etter min mening er ideen veldig kul og samtidig veldig enkel, takket være at prosjektet har fått mange på en rekke språk. Og vi vil deretter prøve å bruke en lignende filosofi om å skille SQL-kode fra alt annet langt utenfor ORM.
IDE og DB ledere
La oss starte med en enkel hverdagsoppgave. Ofte må vi søke etter noen objekter i databasen, for eksempel finne en tabell i skjemaet og studere strukturen (hvilke kolonner, nøkler, indekser, begrensninger osv. som brukes). Og fra enhver grafisk IDE eller en liten DB-manager forventer vi først og fremst akkurat disse evnene. Slik at det går raskt og du slipper å vente en halvtime til et vindu med nødvendig informasjon er tegnet (spesielt med en treg tilkobling til en ekstern database), og samtidig er informasjonen som mottas fersk og relevant, og ikke bufret søppel. Dessuten, jo mer kompleks og større databasen og jo større antall av dem, desto vanskeligere er det å gjøre dette.
Men vanligvis kaster jeg musen og bare skriver kode. La oss si at du må finne ut hvilke tabeller (og med hvilke egenskaper) som finnes i "HR"-skjemaet. I de fleste DBMS-er kan det ønskede resultatet oppnås med denne enkle spørringen fra information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Fra database til database varierer innholdet i slike referansetabeller avhengig av egenskapene til hver DBMS. Og, for eksempel, for MySQL, fra samme oppslagsbok kan du få tabellparametere som er spesifikke for denne DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle kjenner ikke til informasjonsskjema, men det har det , og ingen store problemer oppstår:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse er intet unntak:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Noe lignende kan gjøres i Cassandra (som har kolonnefamilier i stedet for tabeller og nøkkelrom i stedet for skjemaer):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'For de fleste andre databaser kan du også komme med lignende spørsmål (selv Mongo har , som inneholder informasjon om alle samlinger i systemet).
Selvfølgelig kan du på denne måten få informasjon ikke bare om tabeller, men om ethvert objekt generelt. Fra tid til annen deler snille mennesker slik kode for forskjellige databaser, som for eksempel i serien med habra-artikler "Funksjoner for å dokumentere PostgreSQL-databaser" (, , ). Selvfølgelig er det en fornøyelse å ha hele dette fjellet av spørringer i hodet mitt og konstant skrive dem, så i min favoritt IDE/editor har jeg et forhåndsforberedt sett med utdrag for ofte brukte spørringer, og alt som gjenstår er å skrive inn objektnavn inn i malen.
Som et resultat er denne metoden for å navigere og søke etter objekter mye mer fleksibel, sparer mye tid og lar deg få nøyaktig informasjonen i den formen den nå er nødvendig (som for eksempel beskrevet i innlegget ).
Operasjoner med objekter
Etter at vi har funnet og studert de nødvendige gjenstandene, er det på tide å gjøre noe nyttig med dem. Naturligvis også uten å ta fingrene fra tastaturet.
Det er ingen hemmelighet at bare sletting av en tabell vil se likt ut i nesten alle databaser:
drop table hr.personsMen med opprettelsen av bordet blir det mer interessant. Nesten alle DBMS (inkludert mange NoSQL) kan "lage tabeller" i en eller annen form, og hoveddelen av den vil til og med avvike litt (navn, liste over kolonner, datatyper), men andre detaljer kan variere dramatisk og avhenge av intern enhet og egenskapene til et spesifikt DBMS. Mitt favoritteksempel er at i Oracle-dokumentasjonen er det bare "nakne" BNF-er for "opprett tabell"-syntaksen . Andre DBMS-er har mer beskjedne muligheter, men hver av dem har også mange interessante og unike funksjoner for å lage tabeller (, , , ). Det er usannsynlig at noen grafisk "veiviser" fra en annen IDE (spesielt en universell) vil være i stand til å dekke alle disse egenskapene fullt ut, og selv om den kan, vil det ikke være et skue for sarte sjeler. Samtidig en korrekt og rettidig skriftlig uttalelse lage bord lar deg enkelt bruke dem alle, gjøre lagring og tilgang til dataene dine pålitelig, optimal og så komfortabel som mulig.
Mange DBMS-er har også sine egne spesifikke typer objekter som ikke er tilgjengelige i andre DBMS-er. Dessuten kan vi utføre operasjoner ikke bare på databaseobjekter, men også på selve DBMS, for eksempel "drepe" en prosess, frigjøre noe minneområde, aktivere sporing, bytte til "skrivebeskyttet"-modus og mye mer.
La oss nå tegne litt
En av de vanligste oppgavene er å bygge et diagram med databaseobjekter og se objektene og sammenhengene mellom dem i et vakkert bilde. Nesten hvilken som helst grafisk IDE, separate "kommandolinje"-verktøy, spesialiserte grafiske verktøy og modellerere kan gjøre dette. De vil tegne noe for deg "så godt de kan", og du kan påvirke denne prosessen litt bare ved hjelp av noen få parametere i konfigurasjonsfilen eller avmerkingsbokser i grensesnittet.
Men dette problemet kan løses mye enklere, mer fleksibelt og elegant, og selvfølgelig ved hjelp av kode. For å lage diagrammer av enhver kompleksitet, har vi flere spesialiserte markup-språk (DOT, GraphML etc), og for dem en hel spredning av applikasjoner (GraphViz, PlantUML, Mermaid) som kan lese slike instruksjoner og visualisere dem i en rekke formater . Vel, vi vet allerede hvordan vi får informasjon om objekter og forbindelser mellom dem.
Her er et lite eksempel på hvordan dette kan se ut, ved å bruke PlantUML og (til venstre er en SQL-spørring som vil generere den nødvendige instruksjonen for PlantUML, og til høyre er resultatet):
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'Og hvis du prøver litt, så basert på du kan få noe som ligner på et ekte ER-diagram:
SQL-spørringen er litt mer komplisert
-- Шапка
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'
Hvis du ser nøye etter, under panseret bruker mange visualiseringsverktøy også lignende spørringer. Riktignok er disse forespørslene vanligvis dypt , for ikke å nevne noen modifikasjon av dem.
Målinger og overvåking
La oss gå videre til et tradisjonelt komplekst emne - overvåking av databaseytelse. Jeg husker en liten sann historie fortalt meg av «en av vennene mine». På et annet prosjekt bodde det en viss kraftig DBA, og få av utviklerne kjente ham personlig, eller hadde noen gang sett ham personlig (til tross for at han ifølge ryktene jobbet et sted i neste bygning). På time "X", da produksjonssystemet til en stor forhandler begynte å "føles dårlig" igjen, sendte han stille skjermbilder av grafer fra Oracle Enterprise Manager, hvor han nøye fremhevet kritiske steder med en rød markør for "forståelighet" ( dette hjalp mildt sagt lite). Og basert på dette "fotokortet" måtte jeg behandle. Samtidig hadde ingen tilgang til den dyrebare (i begge betydninger av ordet) Enterprise Manager, fordi systemet er komplekst og dyrt, plutselig "snubler utviklerne over noe og bryter alt." Derfor fant utviklerne "empirisk" plasseringen og årsaken til bremsene og ga ut en oppdatering. Hvis det truende brevet fra DBA ikke kom igjen i nær fremtid, ville alle puste lettet ut og gå tilbake til sine nåværende oppgaver (inntil det nye brevet).
Men overvåkingsprosessen kan se morsommere og vennligere ut, og viktigst av alt, tilgjengelig og gjennomsiktig for alle. I det minste den grunnleggende delen, som et tillegg til hovedovervåkingssystemene (som absolutt er nyttige og i mange tilfeller uerstattelige). Enhver DBMS er fritt og helt gratis for å dele informasjon om dens nåværende tilstand og ytelse. I den samme "blodige" Oracle DB kan nesten all informasjon om ytelse hentes fra systemvisninger, alt fra prosesser og økter til tilstanden til bufferbufferen (f.eks. , avsnittet "Overvåking"). Postgresql har også en hel haug med systemvisninger for , spesielt de som er uunnværlige i hverdagen til enhver DBA, som f.eks , , . MySQL har til og med et eget skjema for dette. . A In Mongo innebygd samler ytelsesdata til en systemsamling .
Bevæpnet med en slags metrikksamler (Telegraf, Metricbeat, Collectd) som kan utføre tilpassede sql-spørringer, en lagring av disse metrikkene (InfluxDB, Elasticsearch, Timescaledb) og en visualizer (Grafana, Kibana), kan du få en ganske enkel og et fleksibelt overvåkingssystem som vil være tett integrert med andre systemomfattende beregninger (innhentet for eksempel fra applikasjonsserveren, fra OS, etc.). Som for eksempel gjøres dette i pgwatch2, som bruker InfluxDB + Grafana-kombinasjonen og et sett med spørringer til systemvisninger, som også kan nås .
Totalt
Og dette er bare en omtrentlig liste over hva som kan gjøres med databasen vår ved å bruke vanlig SQL-kode. Jeg er sikker på at du kan finne mange flere bruksområder, skriv i kommentarfeltet. Og vi skal snakke om hvordan (og viktigst av alt hvorfor) å automatisere alt dette og inkludere det i CI/CD-pipeline neste gang.
Kilde: www.habr.com