SQL, cosa può essere più semplice? Ognuno di noi può scrivere una semplice query — digitiamo select, elenchiamo le colonne necessarie, poi from, il nome della tabella, qualche condizione in dove e voilà — i dati utili sono a portata di mano, praticamente (quasi) indipendentemente dal tipo di DBMS che sta funzionando sotto il cofano (o forse non è nemmeno . Di conseguenza, il lavoro con praticamente qualsiasi fonte di dati (relazionale e non) può essere considerato dal punto di vista del codice normale (con tutte le conseguenze del caso — version control, code review, analisi statica, test automatici e così via). E questo riguarda non solo i dati stessi, gli schemi e le migrazioni, ma anche l'intera vita operativa del repository. In questo articolo parleremo delle attività quotidiane e dei problemi nel lavorare con vari DB prospettando il "database as code".
E iniziamo proprio con . Le prime battaglie tra "SQL vs ORM" furono notate ancora nella .
Mapping oggetto-relazionale
I sostenitori dell'ORM tradizionalmente apprezzano la velocità e la semplicità dello sviluppo, l'indipendenza dal DBMS e la pulizia del codice. Per molti di noi, il codice per l'interazione con il DB (e spesso il DB stesso)
di solito appare circa così…
@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;
}
...Il modello è arricchito con annotazioni intelligenti, mentre dietro le quinte un valoroso ORM genera ed esegue tonnellate di codice SQL. A proposito, gli sviluppatori si sforzano di separarsi dal loro DB con chilometri di astrazioni, il che indica una certa .
Dall'altra parte della barricata, i sostenitori del "handmade"-SQL sottolineano la possibilità di sfruttare al massimo il loro DBMS senza ulteriori strati e astrazioni. Il risultato sono progetti "data-centric", in cui la gestione del database è affidata a persone specificamente formate (i cosiddetti "dbas", "database professionals" e simili), mentre gli sviluppatori si limitano a "richiamare" le viste pronte e le procedure memorizzate, senza entrare nei dettagli.
E se prendessimo il meglio di entrambi i mondi? Come è realizzato nel meraviglioso strumento dal nome incoraggiante . Ecco un paio di frasi dall'idea generale in una mia libera traduzione, per approfondire ulteriormente il tema, si può leggere di più. .
Clojure è un linguaggio fantastico per creare DSL, ma SQL di per sé è già un ottimo DSL, e non abbiamo bisogno di un altro. Le S-expression sono meravigliose, ma qui non apportano nulla di nuovo. Alla fine, abbiamo parentesi per il gusto di avere parentesi. Non siete d'accordo? Allora aspettate il momento in cui l'astrazione sopra il database inizierà a cedere, e inizierete a combattere con la funzione. (raw-sql)
E cosa fare? Lasciamo SQL com'è: un file per ogni query:
-- name: users-by-country
select *
from users
where country_code = :country_code… e poi leggete questo file, trasformandolo in una normale funzione Clojure:
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; È stata creata una funzione con il nome `users-by-country`.
;;; Usiamola:
(users-by-country {:country_code "GB"})
;=>( {:name "Kris" :country_code "GB" ...} ...)Seguendo il principio "SQL separato, Clojure separato", ottieni:
- Nessuna sorpresa sintattica. Il tuo database (come qualsiasi altro) non corrisponde mai allo standard SQL al 100% — ma per Yesql questo non è un problema. Non perderai mai tempo a cercare funzioni con una sintassi equivalente a SQL. Non dovrai mai tornare a una funzione. (raw-sql "some (‘funky’ :: SYNTAX)")).
- Il miglior supporto per l'editor. Il tuo editor ha già un eccellente supporto per SQL. Mantenendo SQL come SQL, puoi semplicemente usarlo.
- Compatibilità di comando. I tuoi DBA possono leggere e scrivere SQL che utilizzi nel tuo progetto Clojure.
- Configurazione delle prestazioni semplificata. Hai bisogno di un piano per una query problematica? Non è un problema quando la tua query è un semplice SQL.
- Riutilizzo delle query. Trascina questi stessi file SQL in altri progetti, perché è semplicemente un vecchio buon SQL — condividilo.
Penso che l'idea sia molto interessante e allo stesso tempo molto semplice, il che ha portato il progetto a molteplici in molte lingue diverse. E noi cercheremo di applicare una filosofia simile di separazione del codice SQL da tutto il resto, ben oltre l'ORM.
IDE & DB-gestori
Iniziamo con un compito semplice della vita quotidiana. Spesso ci troviamo a dover cercare determinati oggetti in un database, ad esempio trovare una tabella in uno schema e studiarne la struttura (quali colonne, chiavi, indici, vincoli, ecc. sono utilizzati). E da qualsiasi IDE grafica o anche da un semplice DB-manager, ci aspettiamo prima di tutto proprio queste capacità. Deve essere veloce e non dobbiamo aspettare mezz'ora perché si apra una finestra con le informazioni necessarie (soprattutto con una connessione lenta a un database remoto), e inoltre le informazioni devono essere fresche e aggiornate, non una vecchia versione memorizzata nella cache. E più complicato e grande è il database e maggiore è il numero di database, più difficile è farlo.
Ma solitamente metto il mouse da parte e scrivo semplicemente codice. Supponiamo di voler scoprire quali tabelle (e con quali proprietà) sono contenute nello schema "HR". Nella maggior parte dei sistemi di gestione di database, è possibile ottenere il risultato desiderato con una semplice query da information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Dalla base alla base, il contenuto di tali tabelle di riferimento varia a seconda delle capacità di ciascun DBMS. Ad esempio, per MySQL, da questo stesso riferimento è possibile ottenere parametri specifici per questo DBMS:
select table_name
, storage_engine -- Motore utilizzato ("MyISAM", "InnoDB" ecc.)
, row_format -- Formato della riga ("Fixed", "Dynamic" ecc.)
, ...
from information_schema.tables
where schema = 'HR'Oracle non supporta information_schema, ma ha , e non ci sono grandi problemi:
select table_name
, pct_free -- Minimo spazio libero nel blocco dati (%)
, pct_used -- Minimo spazio utilizzato nel blocco dati (%)
, last_analyzed -- Data dell'ultimo raccolto statistico
, ...
from all_tables
where owner = 'HR'Non fa eccezione ClickHouse:
select name
, engine -- Motore utilizzato ("MergeTree", "Dictionary" ecc.)
, ...
from system.tables
where database = 'HR'Qualcosa di simile può essere fatto anche in Cassandra (dove ci sono columnfamilies invece di tabelle e keyspace invece di schemi):
select columnfamily_name
, compaction_strategy_class -- Strategia di raccolta spazzatura
, gc_grace_seconds -- Tempo di vita della spazzatura
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Per la maggior parte degli altri DB, è possibile ideare query simili (anche in Mongo c'è , che contiene informazioni su tutte le collezioni nel sistema).
Naturalmente, in questo modo è possibile ottenere informazioni non solo sulle tabelle, ma su qualsiasi oggetto. Periodicamente, persone gentili condividono codice di questo tipo per diversi database, come nella serie di articoli del blog "Funzioni per la documentazione dei database PostgreSQL" (, , ). Ovviamente, tenere a mente tutta questa montagna di query e doverle digitare continuamente è un "dispiacere" piuttosto grande; quindi nella mia IDE/editor preferita ho un insieme predefinito di snippet per le query più utilizzate, e rimane solo da inserire i nomi degli oggetti nel modello.
Di conseguenza, questo modo di navigare e cercare oggetti è molto più flessibile, risparmia molto tempo e consente di ottenere le informazioni esattamente nel formato di cui abbiamo bisogno (come descritto nel post ).
Operazioni con gli oggetti
Dopo aver trovato e studiato gli oggetti necessari, è tempo di fare qualcosa di utile con loro. Naturalmente, senza staccare le dita dalla tastiera.
Non è un segreto che la semplice rimozione di una tabella appaia quasi identica in tutti i DB:
drop table hr.personsMa la creazione di una tabella è decisamente più interessante. Praticamente ogni DBMS (compresi molti NoSQL) supporta in qualche modo il comando "create table", e gran parte di esso non differirà molto (nome, elenco colonne, tipi di dati), ma i dettagli rimanenti possono variare notevolmente e dipendono dall'architettura interna e dalle capacità specifiche del DBMS. Il mio esempio preferito — nella documentazione di Oracle ci sono solo le "pure" BNF per la sintassi "create table" . Altri DBMS hanno funzionalità più limitate, ma ognuno di essi ha anche molte caratteristiche interessanti e uniche per la creazione di tabelle (, , , ). È improbabile che un "wizard" grafico di qualche IDE (soprattutto universale) possa coprire completamente tutte queste capacità, e se anche lo fa, sarà uno spettacolo non per i nervi deboli. Allo stesso tempo, un'istruzione ben scritta e tempestiva create table permetterà di sfruttare senza sforzo tutte queste funzionalità, rendendo l'archiviazione e l'accesso ai tuoi dati sicuri, ottimali e il più confortevoli possibile.
In molte DBMS ci sono tipi di oggetti specifici che mancano in altri DBMS. Possiamo eseguire operazioni non solo sugli oggetti del database, ma anche sul DBMS stesso, come "terminare" un processo, liberare una certa area di memoria, attivare il tracciamento, passare in modalità "sola lettura" e molto altro ancora.
Adesso un po' di disegno
Una delle attività più comuni è costruire un diagramma con gli oggetti del database, per vedere in una bella immagine gli oggetti e le relazioni tra di essi. Quasi qualsiasi IDE grafica, insieme a utilità "da linea di comando", strumenti grafici specializzati e modellatori, riesce a farlo. Questi strumenti possono creare un'immagine "come sanno fare", e possiamo influenzare leggermente questo processo solo attraverso alcuni parametri nel file di configurazione o selezionando opzioni nell'interfaccia.
Ma questo problema può essere risolto in modo molto più semplice, flessibile ed elegante, e ovviamente con l'ausilio del codice. Per la creazione di diagrammi di qualsiasi complessità, abbiamo a disposizione diversi linguaggi di markup specializzati (DOT, GraphML, ecc.), e una miriade di applicazioni (GraphViz, PlantUML, Mermaid) capaci di leggere tali istruzioni e visualizzarle in vari formati. Inoltre, già sappiamo come ottenere informazioni sugli oggetti e le relazioni tra di essi.
Facciamo un breve esempio di come potrebbe apparire, utilizzando PlantUML e (a sinistra la query SQL che genererà la necessaria istruzione per PlantUML, e a destra il risultato):
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'E se ci si impegna un po', sulla base del si può ottenere qualcosa di molto simile a un vero diagramma ER:
La query SQL è un po' più complessa
-- Intestazione
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
-- Tabelle
select format('Table(%s, "%s n informazioni su %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
-- Relazioni tra tabelle
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "Una %s può avere molte %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
-- Fondo
select '@enduml' 
Se si guarda attentamente, molti strumenti di visualizzazione utilizzano richieste simili nello stesso modo. Tuttavia, queste richieste sono spesso "incorporate" nel codice stesso dell'applicazione e sono difficili da comprendere Metriche e monitoraggio
Metriche e monitoraggio
Passiamo a un tema tradizionalmente complesso: il monitoraggio delle prestazioni del database. Ricordo una piccola storia vera, raccontatami "da un amico". In un progetto precedente c'era un potente DBA, e poche persone tra gli sviluppatori lo conoscevano di persona, o lo avevano mai visto in faccia (nonostante si dicesse che lavorasse, a sentire i rumors, da qualche parte nell'edificio accanto). Nell'ora "X", quando il sistema di produzione di un grande rivenditore cominciava a "stare male" di nuovo, lui inviava silenziosamente screenshot dei grafici dall'Oracle Enterprise Manager, in cui evidenziava con un evidenziatore rosso le aree critiche per una "maggiore chiarezza" (il che, per usare un eufemismo, aiutava poco). E così bisognava intervenire basandosi su questa "fotocopia". Nel frattempo, nessuno aveva accesso al prezioso (in entrambi i sensi) Enterprise Manager, poiché si trattava di un sistema complesso e costoso; si teme infatti che "gli sviluppatori possano incappare in qualcosa e rompere tutto". Così gli sviluppatori scoprivano "empiricamente" il punto e la causa dei rallentamenti e rilasciavano una patch. Se la temuta lettera dal DBA non arrivava di nuovo nel giro di poco tempo, tutti tiravano un sospiro di sollievo e tornavano ai propri compiti attuali (fino alla nuova lettera).
Ma il processo di monitoraggio può apparire più divertente e amichevole, e soprattutto - accessibile e trasparente per tutti. Almeno la sua parte fondamentale, come complemento ai principali sistemi di monitoraggio (che sono senza dubbio utili e in molti casi indispensabili). Qualunque DBMS è libero e assolutamente gratuito di condividere informazioni sul proprio stato attuale e sulle performance. Nella stessa "cruenta" Oracle DB, praticamente qualsiasi informazione sulle performance può essere ottenuta dalle viste di sistema, partendo dai processi e dalle sessioni fino allo stato della cache dei buffer (ad esempio, , sezione "Monitoring"). In Postgresql ci sono anche una serie di viste di sistema per , in particolare alcune indispensabili nella vita quotidiana di ogni DBA, come , , . In MySQL, c'è addirittura uno schema dedicato . E in Mongo, un aggregato dei dati sulle performance in una collezione di sistema .
Pertanto, dotandosi di un qualsiasi aggregatore di metriche (Telegraf, Metricbeat, Collectd), in grado di eseguire query SQL personalizzate, di un archivio per queste metriche (InfluxDB, Elasticsearch, Timescaledb) e di uno strumento di visualizzazione (Grafana, Kibana), si può ottenere un sistema di monitoraggio piuttosto semplice e flessibile, che sarà strettamente integrato con altre metriche di sistema (ottenute, ad esempio, dal server delle applicazioni, dal sistema operativo, ecc.). Come ad esempio è stato fatto in pgwatch2, dove è utilizzato il binomio InfluxDB + Grafana insieme a un insieme di query sulle viste di sistema, a cui è possibile anche .
Totale
E questo è solo un elenco approssimativo di ciò che si può fare con il nostro database tramite codice SQL standard. Sono certo che ci sono molte altre applicazioni, fateci sapere nei commenti. E di come (e soprattutto perché) automatizzare tutto questo e integrarlo nel proprio pipeline CI/CD parleremo la prossima volta.
Fonte: habr.com
