SQL, vad kan vara enklare? Var och en av oss kan skriva en enkel förfrågan - vi skriver välj, lista de obligatoriska kolumnerna och sedan från, tabellnamn, några villkor i var och det är allt - användbar data finns i vår ficka, och (nästan) oavsett vilket DBMS som finns under huven vid den tiden (eller kanske ). Som ett resultat kan man överväga att arbeta med nästan vilken datakälla som helst (relationell och inte så) ur synvinkeln av vanlig kod (med allt vad det innebär - versionskontroll, kodgranskning, statisk analys, autotester och det är allt). Och detta gäller inte bara själva data, scheman och migreringar, utan generellt sett hela lagringstiden. I den här artikeln kommer vi att prata om vardagliga uppgifter och problem med att arbeta med olika databaser under linsen "databas som kod".
Och låt oss börja direkt från . De första striderna av typen "SQL vs ORM" märktes tillbaka in .
Objektrelationell kartläggning
ORM-anhängare värdesätter traditionellt hastighet och enkel utveckling, oberoende från DBMS och ren kod. För många av oss är koden för att arbeta med databasen (och ofta själva databasen)
det brukar se ut så här...
@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 är hängd med smarta kommentarer, och någonstans bakom kulisserna genererar och exekverar en tapper ORM massor av SQL-kod. Förresten, utvecklare försöker sitt bästa för att isolera sig från sin databas med kilometervis av abstraktioner, vilket indikerar några .
På andra sidan barrikaderna noterar anhängare av ren "handgjord" SQL möjligheten att pressa all juice ur deras DBMS utan ytterligare lager och abstraktioner. Som ett resultat uppstår "datacentrerade" projekt, där specialutbildade personer är involverade i databasen (de är också "basicister", de är också "basicister", de är också "basdeners", etc.), och utvecklarna behöver bara "dra" de färdiga vyerna och lagrade procedurerna, utan att gå in på detaljer.
Tänk om vi hade det bästa av två världar? Hur detta görs i ett underbart verktyg med ett livsbejakande namn . Jag kommer att ge ett par rader från det allmänna konceptet i min fria översättning, och du kan bekanta dig mer detaljerat med det .
Clojure är ett coolt språk för att skapa DSL, men SQL i sig är en cool DSL, och vi behöver inte en till. S-uttryck är bra, men de tillför inget nytt här. Som ett resultat får vi parentes för parentes skull. Håller inte med? Vänta sedan till ögonblicket när abstraktionen över databasen börjar läcka och du börjar slåss med funktionen (raw-sql)
Så vad skall jag göra? Låt oss lämna SQL som vanlig SQL - en fil per begäran:
-- name: users-by-country
select *
from users
where country_code = :country_code... och läs sedan den här filen och gör den till en vanlig Clojure-funktion:
(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" ...} ...)Genom att följa principen "SQL i sig, Clojure i sig" får du:
- Inga syntaktiska överraskningar. Din databas (som alla andra) är inte 100% kompatibel med SQL-standarden - men detta spelar ingen roll för Yesql. Du kommer aldrig att slösa tid på att leta efter funktioner med SQL-likvärdig syntax. Du behöver aldrig återgå till en funktion (raw-sql "some('funky'::SYNTAX)")).
- Bästa redaktörsstöd. Din redaktör har redan utmärkt SQL-stöd. Genom att spara SQL som SQL kan du helt enkelt använda den.
- Lagkompatibilitet. Dina DBA:er kan läsa och skriva den SQL du använder i ditt Clojure-projekt.
- Enklare prestandajustering. Behöver du bygga en plan för en problematisk fråga? Detta är inte ett problem när din fråga är vanlig SQL.
- Återanvända frågor. Dra och släpp samma SQL-filer till andra projekt för det är helt enkelt gammal SQL - dela det bara.
Enligt mig är idén väldigt cool och samtidigt väldigt enkel, tack vare vilken projektet har fått många på en mängd olika språk. Och vi kommer nästa att försöka tillämpa en liknande filosofi att separera SQL-kod från allt annat långt bortom ORM.
IDE & DB-hanterare
Låt oss börja med en enkel vardagsuppgift. Ofta måste vi söka efter några objekt i databasen, till exempel hitta en tabell i schemat och studera dess struktur (vilka kolumner, nycklar, index, begränsningar etc. som används). Och från vilken grafisk IDE eller en liten DB-hanterare förväntar vi oss först och främst exakt dessa förmågor. Så att det går snabbt och du inte behöver vänta en halvtimme tills ett fönster med nödvändig information har ritats (särskilt med en långsam anslutning till en fjärrdatabas), och samtidigt är informationen som tas emot färsk och relevant, och inte cachat skräp. Dessutom, ju mer komplex och större databasen är och ju fler av dem, desto svårare är det att göra detta.
Men oftast slänger jag musen och bara skriver kod. Låt oss säga att du behöver ta reda på vilka tabeller (och med vilka egenskaper) som finns i "HR"-schemat. I de flesta DBMS:er kan det önskade resultatet uppnås med denna enkla fråga från information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Från databas till databas varierar innehållet i sådana referenstabeller beroende på kapaciteten hos varje DBMS. Och, till exempel, för MySQL, från samma referensbok kan du få tabellparametrar som är specifika för detta DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle känner inte till information_schema, men det har det , och inga stora problem uppstår:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse är inget undantag:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Något liknande kan göras i Cassandra (som har kolumnfamiljer istället för tabeller och tangentrymder istället för scheman):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'För de flesta andra databaser kan du också komma med liknande frågor (även Mongo har , som innehåller information om alla samlingar i systemet).
Naturligtvis kan du på detta sätt få information inte bara om tabeller, utan om vilket objekt som helst i allmänhet. Då och då delar snälla människor sådan kod för olika databaser, som till exempel i serien med habra-artiklar "Funktioner för att dokumentera PostgreSQL-databaser" (, , ). Naturligtvis är det ett nöje att ha hela det här berget av frågor i mitt huvud och ständigt skriva dem, så i min favorit-IDE/redigerare har jag en förberedd uppsättning utdrag för ofta använda frågor, och allt som återstår är att skriva in objektnamn i mallen.
Som ett resultat är denna metod för att navigera och söka efter objekt mycket mer flexibel, sparar mycket tid och låter dig få exakt informationen i den form som den nu är nödvändig (som till exempel beskrivs i inlägget ).
Operationer med objekt
När vi har hittat och studerat de nödvändiga föremålen är det dags att göra något användbart med dem. Naturligtvis också utan att ta fingrarna från tangentbordet.
Det är ingen hemlighet att bara ta bort en tabell kommer att se likadant ut i nästan alla databaser:
drop table hr.personsMen med skapandet av bordet blir det mer intressant. Nästan alla DBMS (inklusive många NoSQL) kan "skapa tabeller" i en eller annan form, och huvuddelen av den kommer till och med att skilja sig något (namn, lista över kolumner, datatyper), men andra detaljer kan skilja sig dramatiskt och beror på intern enhet och funktioner hos ett specifikt DBMS. Mitt favoritexempel är att i Oracle-dokumentationen finns det bara "nakna" BNF:er för "skapa tabell"-syntaxen . Andra DBMS har mer blygsamma möjligheter, men var och en av dem har också många intressanta och unika funktioner för att skapa tabeller (, , , ). Det är osannolikt att någon grafisk "trollkarl" från en annan IDE (särskilt en universell) kommer att kunna täcka alla dessa förmågor fullt ut, och även om den kan, kommer det inte att vara ett skådespel för svaga hjärtan. Samtidigt ett korrekt och lägligt skrivet uttalande skapa bord gör att du enkelt kan använda dem alla, göra lagring och tillgång till dina data tillförlitlig, optimal och så bekväm som möjligt.
Många DBMS har också sina egna specifika typer av objekt som inte är tillgängliga i andra DBMS. Dessutom kan vi utföra operationer inte bara på databasobjekt, utan också på själva DBMS, till exempel "döda" en process, frigöra lite minne, aktivera spårning, byta till "skrivskyddat" läge och mycket mer.
Låt oss nu rita lite
En av de vanligaste uppgifterna är att bygga ett diagram med databasobjekt och se objekten och sambanden mellan dem i en vacker bild. Nästan alla grafiska IDE, separata "kommandorads"-verktyg, specialiserade grafiska verktyg och modellerare kan göra detta. De kommer att rita något åt dig "så gott de kan", och du kan påverka denna process lite bara med hjälp av några parametrar i konfigurationsfilen eller kryssrutor i gränssnittet.
Men detta problem kan lösas mycket enklare, mer flexibelt och elegant, och givetvis med hjälp av kod. För att skapa diagram av någon komplexitet har vi flera specialiserade märkningsspråk (DOT, GraphML etc), och för dem en hel spridning av applikationer (GraphViz, PlantUML, Mermaid) som kan läsa sådana instruktioner och visualisera dem i en mängd olika format . Tja, vi vet redan hur man får information om objekt och kopplingar mellan dem.
Här är ett litet exempel på hur detta kan se ut, med PlantUML och (till vänster finns en SQL-fråga som genererar den nödvändiga instruktionen för PlantUML, och till höger är 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'Och om du försöker lite, då baserat på du kan få något som liknar ett riktigt ER-diagram:
SQL-frågan är lite mer komplicerad
-- Шапка
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'
Om du tittar noga, under huven använder många visualiseringsverktyg också liknande frågor. Det är sant att dessa förfrågningar vanligtvis är djupt , för att inte tala om någon modifiering av dem.
Mätvärden och övervakning
Låt oss gå vidare till ett traditionellt komplext ämne - övervakning av databasprestanda. Jag minns en liten sann historia berättad för mig av "en av mina vänner". På ett annat projekt fanns det en viss kraftfull DBA, och få av utvecklarna kände honom personligen, eller hade någonsin sett honom personligen (trots att han, enligt rykten, arbetade någonstans i nästa byggnad). I timmen "X", när en stor återförsäljares produktsystem började "må dåligt" igen, skickade han tyst skärmdumpar av grafer från Oracle Enterprise Manager, där han noggrant framhävde kritiska platser med en röd markör för "begriplighet" ( detta, milt sagt, hjälpte inte mycket). Och baserat på detta "fotokort" var jag tvungen att behandla. Samtidigt hade ingen tillgång till den dyrbara (i ordets båda betydelser) Enterprise Manager, eftersom systemet är komplext och dyrt, plötsligt "snubblar utvecklarna på något och bryter allt." Därför hittade utvecklarna "empiriskt" platsen och orsaken till bromsarna och släppte en patch. Om det hotfulla brevet från DBA inte anlände igen inom en snar framtid, då skulle alla andas ut och återgå till sina nuvarande uppgifter (tills det nya brevet).
Men övervakningsprocessen kan se roligare och vänligare ut, och viktigast av allt, tillgänglig och transparent för alla. Åtminstone dess grundläggande del, som ett tillägg till de viktigaste övervakningssystemen (som säkert är användbara och i många fall oersättliga). Varje DBMS är fritt och helt kostnadsfritt att dela information om dess nuvarande tillstånd och prestanda. I samma "blodiga" Oracle DB kan nästan all information om prestanda erhållas från systemvyer, allt från processer och sessioner till tillståndet för buffertcachen (till exempel, , avsnittet "Övervakning"). Postgresql har också en hel massa systemvyer för , särskilt de som är oumbärliga i det dagliga livet för alla DBA, som t.ex , , . MySQL har till och med ett separat schema för detta. . A In Mongo inbyggd aggregerar prestandadata till en systemsamling .
Således, beväpnad med någon slags metriksamlare (Telegraf, Metricbeat, Collectd) som kan utföra anpassade sql-frågor, en lagring av dessa mätvärden (InfluxDB, Elasticsearch, Timescaledb) och en visualizer (Grafana, Kibana), kan du få en ganska enkel och ett flexibelt övervakningssystem som kommer att vara nära integrerat med andra systemomfattande mätvärden (erhållna till exempel från applikationsservern, från OS, etc.). Som till exempel görs detta i pgwatch2, som använder kombinationen InfluxDB + Grafana och en uppsättning frågor till systemvyer, som också kan nås .
Totalt
Och detta är bara en ungefärlig lista över vad som kan göras med vår databas med vanlig SQL-kod. Jag är säker på att du kan hitta många fler användningsområden, skriv i kommentarerna. Och vi kommer att prata om hur (och viktigast av allt varför) att automatisera allt detta och inkludera det i din CI/CD-pipeline nästa gång.
Källa: will.com