SQL, cosa potrebbe essere più semplice? Ognuno di noi può scrivere una semplice richiesta: digitiamo select, elencare le colonne richieste, quindi da, nome della tabella, alcune condizioni in where e questo è tutto: i dati utili sono nelle nostre tasche e (quasi) indipendentemente da quale DBMS si trova sotto il cofano in quel momento (o forse ). Di conseguenza, lavorare con quasi tutte le fonti di dati (relazionali e non) può essere considerato dal punto di vista del codice ordinario (con tutto ciò che implica: controllo della versione, revisione del codice, analisi statica, test automatici e basta). E questo vale non solo per i dati in sé, schemi e migrazioni, ma in generale per l’intera vita dello storage. In questo articolo parleremo delle attività quotidiane e dei problemi legati al lavoro con vari database sotto la lente del "database come codice".
E cominciamo proprio da . Le prime battaglie del tipo "SQL vs ORM" furono notate già in passato .
Mappatura relazionale degli oggetti
I sostenitori dell'ORM tradizionalmente apprezzano la velocità e la facilità di sviluppo, l'indipendenza dal DBMS e il codice pulito. Per molti di noi, il codice per lavorare con il database (e spesso con il database stesso)
di solito assomiglia a questo...
@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 è pieno di annotazioni intelligenti e da qualche parte dietro le quinte un valoroso ORM genera ed esegue tonnellate di codice SQL. A proposito, gli sviluppatori stanno facendo del loro meglio per isolarsi dal loro database con chilometri di astrazioni, il che ne indica alcune .
Dall'altro lato delle barricate, i sostenitori del puro SQL “fatto a mano” notano la capacità di spremere tutto il succo dai loro DBMS senza ulteriori livelli e astrazioni. Di conseguenza, compaiono progetti "incentrati sui dati", in cui persone appositamente formate sono coinvolte nel database (sono anche "basicisti", sono anche "basicisti", sono anche "basdeners", ecc.), E gli sviluppatori Non resta che “tirare” le view e le stored procedure già pronte, senza entrare nei dettagli.
E se avessimo il meglio di entrambi i mondi? Come questo viene fatto in uno strumento meraviglioso con un nome che afferma la vita . Darò un paio di righe dal concetto generale nella mia traduzione gratuita e potrai conoscerlo in modo più dettagliato .
Clojure è un linguaggio interessante per la creazione di DSL, ma SQL stesso è un DSL interessante e non ne abbiamo bisogno di un altro. Le espressioni S sono fantastiche, ma non aggiungono nulla di nuovo qui. Di conseguenza, otteniamo parentesi per il bene delle parentesi. Non essere d'accordo? Quindi attendi il momento in cui l'astrazione sul database inizia a perdere e inizi a combattere con la funzione (raw-sql)
Quindi cosa dovrei fare? Lasciamo SQL come SQL normale: un file per richiesta:
-- name: users-by-country
select *
from users
where country_code = :country_code... e poi leggi questo file, trasformandolo in una normale funzione 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" ...} ...)Aderendo al principio "SQL da solo, Clojure da solo", ottieni:
- Nessuna sorpresa sintattica. Il tuo database (come qualsiasi altro) non è compatibile al 100% con lo standard SQL, ma questo non ha importanza per Yesql. Non perderai mai tempo a cercare funzioni con sintassi SQL equivalente. 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 SQL. Salvando SQL come SQL puoi semplicemente usarlo.
- Compatibilità di squadra. I tuoi DBA possono leggere e scrivere l'SQL che usi nel tuo progetto Clojure.
- Ottimizzazione delle prestazioni più semplice. Hai bisogno di creare un piano per una query problematica? Questo non è un problema quando la tua query è SQL normale.
- Riutilizzo delle query. Trascina e rilascia gli stessi file SQL in altri progetti perché è semplicemente il vecchio SQL: condividilo e basta.
Secondo me l'idea è molto interessante e allo stesso tempo molto semplice, grazie alla quale il progetto ha guadagnato molti in una varietà di lingue. Successivamente proveremo ad applicare una filosofia simile separando il codice SQL da tutto il resto ben oltre l'ORM.
Gestori IDE e DB
Cominciamo con un semplice compito quotidiano. Spesso dobbiamo cercare alcuni oggetti nel database, ad esempio, trovare una tabella nello schema e studiarne la struttura (quali colonne, chiavi, indici, vincoli, ecc. vengono utilizzati). E da qualsiasi IDE grafico o piccolo DB manager, prima di tutto, ci aspettiamo esattamente queste capacità. In modo che sia veloce e non devi aspettare mezz'ora finché non viene disegnata una finestra con le informazioni necessarie (soprattutto con una connessione lenta a un database remoto) e, allo stesso tempo, le informazioni ricevute sono fresche e pertinenti, e non spazzatura memorizzata nella cache. Inoltre, quanto più complesso e grande è il database e quanto maggiore è il suo numero, tanto più difficile è farlo.
Ma di solito butto via il mouse e scrivo solo codice. Diciamo che devi scoprire quali tabelle (e con quali proprietà) sono contenute nello schema "HR". Nella maggior parte dei DBMS, il risultato desiderato può essere ottenuto con questa semplice query da information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Da database a database, il contenuto di tali tabelle di riferimento varia a seconda delle capacità di ciascun DBMS. E, ad esempio, per MySQL, dallo stesso libro di consultazione puoi ottenere parametri di tabella specifici per questo DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle non conosce information_schema, ma lo fa , e non sorgono grossi problemi:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse non fa eccezione:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Qualcosa di simile può essere fatto in Cassandra (che ha famiglie di colonne invece di tabelle e spazi chiave invece di schemi):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Per la maggior parte degli altri database, puoi anche creare query simili (anche Mongo ha , che contiene informazioni su tutte le raccolte nel sistema).
Naturalmente, in questo modo puoi ottenere informazioni non solo sulle tabelle, ma su qualsiasi oggetto in generale. Di tanto in tanto, persone gentili condividono tale codice per diversi database, come, ad esempio, nella serie di articoli di habra “Funzioni per documentare i database PostgreSQL” (, , ). Naturalmente, tenere tutta questa montagna di domande in testa e digitarle costantemente è un vero piacere, quindi nel mio IDE/editor preferito ho un set pre-preparato di snippet per le query usate di frequente, e tutto ciò che resta è digitare il nomi degli oggetti nel modello.
Di conseguenza, questo metodo di navigazione e ricerca degli oggetti è molto più flessibile, fa risparmiare molto tempo e consente di ottenere esattamente le informazioni nella forma in cui sono ora necessarie (come, ad esempio, descritto nel post ).
Operazioni con oggetti
Dopo aver trovato e studiato gli oggetti necessari, è il momento di farci qualcosa di utile. Naturalmente, anche senza staccare le dita dalla tastiera.
Non è un segreto che la semplice eliminazione di una tabella avrà lo stesso aspetto in quasi tutti i database:
drop table hr.personsMa con la realizzazione del tavolo la cosa diventa più interessante. Quasi tutti i DBMS (inclusi molti NoSQL) possono "creare tabelle" in una forma o nell'altra, e la parte principale di essa sarà anche leggermente diversa (nome, elenco di colonne, tipi di dati), ma altri dettagli possono differire notevolmente e dipendere dal tipo di dati. dispositivo interno e capacità di uno specifico DBMS. Il mio esempio preferito è che nella documentazione Oracle ci sono solo BNF “nudi” per la sintassi “crea tabella” . Altri DBMS hanno capacità più modeste, ma ognuno di essi ha anche molte caratteristiche interessanti e uniche per la creazione di tabelle (, , , ). È improbabile che qualsiasi "mago" grafico di un altro IDE (soprattutto universale) sarà in grado di coprire completamente tutte queste capacità e, anche se fosse possibile, non sarà uno spettacolo per i deboli di cuore. Allo stesso tempo, una dichiarazione scritta corretta e tempestiva crea tabella ti consentirà di utilizzarli tutti facilmente, di rendere l'archiviazione e l'accesso ai tuoi dati affidabile, ottimale e il più comodo possibile.
Inoltre, molti DBMS hanno tipi specifici di oggetti che non sono disponibili in altri DBMS. Inoltre, possiamo eseguire operazioni non solo sugli oggetti del database, ma anche sul DBMS stesso, ad esempio, "uccidere" un processo, liberare un'area di memoria, abilitare la traccia, passare alla modalità "sola lettura" e molto altro.
Ora disegniamo un po'
Uno dei compiti più comuni è costruire un diagramma con oggetti di database e vedere gli oggetti e le connessioni tra loro in una bella immagine. Quasi tutti gli IDE grafici, le utilità separate della "riga di comando", gli strumenti grafici specializzati e i modellatori possono farlo. Disegneranno qualcosa per te “come meglio possono” e tu potrai influenzare un po’ questo processo solo con l’aiuto di alcuni parametri nel file di configurazione o delle caselle di controllo nell’interfaccia.
Ma questo problema può essere risolto in modo molto più semplice, flessibile ed elegante e, ovviamente, con l'aiuto del codice. Per creare diagrammi di qualsiasi complessità, disponiamo di diversi linguaggi di markup specializzati (DOT, GraphML ecc.), e per essi tutta una serie di applicazioni (GraphViz, PlantUML, Sirena) in grado di leggere tali istruzioni e visualizzarle in una varietà di formati . Bene, sappiamo già come ottenere informazioni sugli oggetti e sulle connessioni tra loro.
Ecco un piccolo esempio di come potrebbe apparire, utilizzando PlantUML e (a sinistra c'è una query SQL che genererà l'istruzione richiesta per PlantUML e a destra c'è 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 provi un po ', allora in base puoi ottenere qualcosa di molto simile a un vero diagramma ER:
La query SQL è un po' più complicata
-- Шапка
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'
Se guardi da vicino, dietro le quinte molti strumenti di visualizzazione utilizzano anche query simili. È vero, queste richieste sono solitamente profonde , per non parlare di qualsiasi modifica degli stessi.
Metriche e monitoraggio
Passiamo a un argomento tradizionalmente complesso: il monitoraggio delle prestazioni del database. Ricordo una piccola storia vera raccontatami da “uno dei miei amici”. In un altro progetto viveva un certo potente DBA, e pochi sviluppatori lo conoscevano personalmente, o lo avevano mai visto di persona (nonostante il fatto che, secondo le voci, lavorasse da qualche parte nell'edificio vicino). All'ora “X”, quando il sistema di produzione di una grande distribuzione ha cominciato di nuovo a “stare male”, ha inviato silenziosamente screenshot di grafici da Oracle Enterprise Manager, sui quali ha attentamente evidenziato i punti critici con un pennarello rosso di “comprensibilità” ( questo, per usare un eufemismo, non è servito a molto). E sulla base di questa “cartolina fotografica” ho dovuto trattare. Allo stesso tempo, nessuno aveva accesso al prezioso (in entrambi i sensi della parola) Enterprise Manager, perché il sistema è complesso e costoso, all’improvviso “gli sviluppatori si imbattono in qualcosa e rompono tutto”. Pertanto, gli sviluppatori hanno trovato "empiricamente" la posizione e la causa dei freni e hanno rilasciato una patch. Se la minacciosa lettera della DBA non arrivasse nuovamente nel prossimo futuro, allora tutti tirerebbero un sospiro di sollievo e tornerebbero ai loro compiti attuali (fino alla nuova lettera).
Ma il processo di monitoraggio può sembrare più divertente e amichevole e, soprattutto, accessibile e trasparente per tutti. Almeno nella sua parte base, in aggiunta ai principali sistemi di monitoraggio (certamente utili e in molti casi insostituibili). Qualsiasi DBMS può condividere liberamente e assolutamente gratuitamente informazioni sul suo stato e sulle sue prestazioni attuali. Nello stesso "maledetto" DB Oracle, quasi tutte le informazioni sulle prestazioni possono essere ottenute dalle visualizzazioni di sistema, dai processi e sessioni allo stato della cache del buffer (ad esempio, , sezione "Monitoraggio"). Postgresql ha anche un sacco di visualizzazioni di sistema per , in particolare quelli indispensabili nella vita quotidiana di qualsiasi DBA, come , , . MySQL ha anche uno schema separato per questo. . A In Mongo integrato aggrega i dati sulle prestazioni in una raccolta di sistema .
Pertanto, armato di una sorta di raccoglitore di metriche (Telegraf, Metricbeat, Collectd) in grado di eseguire query SQL personalizzate, un archivio di queste metriche (InfluxDB, Elasticsearch, Timescaledb) e un visualizzatore (Grafana, Kibana), puoi ottenere un metodo abbastanza semplice e un sistema di monitoraggio flessibile che sarà strettamente integrato con altri parametri a livello di sistema (ottenuti, ad esempio, dal server delle applicazioni, dal sistema operativo, ecc.). Come, ad esempio, viene fatto in pgwatch2, che utilizza la combinazione InfluxDB + Grafana e una serie di query sulle viste di sistema, a cui è possibile accedere anche .
In totale
E questo è solo un elenco approssimativo di cosa si può fare con il nostro database utilizzando il normale codice SQL. Sono sicura che potrai trovare molti altri usi, scrivi nei commenti. E la prossima volta parleremo di come (e, soprattutto, perché) automatizzare tutto questo e includerlo nella pipeline CI/CD.
Fonte: habr.com