SQL, què podria ser més senzill? Cadascun de nosaltres pot escriure una sol·licitud senzilla: escrivim select, enumereu les columnes necessàries i, a continuació de, nom de la taula, algunes condicions a where i això és tot: les dades útils són a la nostra butxaca i (gairebé) independentment de quin SGBD es troba sota el capó en aquell moment (o potser ). Com a resultat, treballar amb gairebé qualsevol font de dades (relacional i no tan) es pot considerar des del punt de vista del codi ordinari (amb tot el que implica: control de versions, revisió de codi, anàlisi estàtica, autotests, i això és tot). I això s'aplica no només a les dades en si, esquemes i migracions, sinó en general a tota la vida útil de l'emmagatzematge. En aquest article parlarem de les tasques quotidianes i dels problemes de treballar amb diverses bases de dades sota la lent de "base de dades com a codi".
I comencem des de . Les primeres batalles del tipus "SQL vs ORM" es van notar de nou .
Mapeig relacional objecte
Els partidaris d'ORM valoren tradicionalment la velocitat i la facilitat de desenvolupament, la independència del SGBD i el codi net. Per a molts de nosaltres, el codi per treballar amb la base de dades (i sovint la pròpia base de dades)
normalment sembla una cosa així...
@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;
}
...El model està penjat amb anotacions intel·ligents i, entre bastidors, un valent ORM genera i executa tones de codi SQL. Per cert, els desenvolupadors estan fent tot el possible per aïllar-se de la seva base de dades amb quilòmetres d'abstraccions, cosa que indica algunes .
A l'altra banda de les barricades, els seguidors de l'SQL pur "fet a mà" assenyalen la capacitat d'extreure tot el suc del seu SGBD sense capes i abstraccions addicionals. Com a resultat, apareixen projectes “centrics en dades”, on a la base de dades hi participen persones especialment formades (també són “bàsics”, també són “bàsics”, també són “basdeners”, etc.), i els desenvolupadors. només cal "treure" les vistes i procediments emmagatzemats ja fets, sense entrar en detalls.
I si tinguéssim el millor dels dos mons? Com es fa això en una eina meravellosa amb un nom que afirma la vida . Donaré un parell de línies del concepte general a la meva traducció gratuïta i us podreu familiaritzar amb més detall .
Clojure és un llenguatge fantàstic per crear DSL, però SQL en si és un DSL genial i no en necessitem un altre. Les expressions S són genials, però no afegeixen res de nou aquí. Com a resultat, obtenim claudàtors pel bé dels claudàtors. No estàs d'acord? A continuació, espereu el moment en què l'abstracció de la base de dades comenci a filtrar-se i comenceu a lluitar amb la funció (raw-sql)
Llavors què hauria de fer? Deixem SQL com a SQL normal: un fitxer per sol·licitud:
-- name: users-by-country
select *
from users
where country_code = :country_code... i després llegiu aquest fitxer, convertint-lo en una funció normal de 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" ...} ...)En adherir-se al principi "SQL per si mateix, Clojure per si mateix", obteniu:
- Sense sorpreses sintàctiques. La vostra base de dades (com qualsevol altra) no és 100% compatible amb l'estàndard SQL, però això no importa per a Yesql. Mai perdreu el temps buscant funcions amb sintaxi equivalent a SQL. Mai hauràs de tornar a una funció (raw-sql "some('funky'::SYNTAX)")).
- Millor suport d'editor. El vostre editor ja té un excel·lent suport SQL. Si deseu SQL com a SQL, simplement podeu utilitzar-lo.
- Compatibilitat d'equip. Els vostres DBA poden llegir i escriure l'SQL que feu servir al vostre projecte Clojure.
- Ajust de rendiment més fàcil. Necessites crear un pla per a una consulta problemàtica? Això no és un problema quan la vostra consulta és SQL normal.
- Reutilització de consultes. Arrossegueu i deixeu anar aquests mateixos fitxers SQL a altres projectes perquè només és un SQL antic; només heu de compartir-lo.
Al meu parer, la idea és molt xula i alhora molt senzilla, gràcies a la qual el projecte n'ha guanyat molts en diversos idiomes. A continuació, intentarem aplicar una filosofia semblant de separar el codi SQL de tota la resta molt més enllà de l'ORM.
Gestors IDE i DB
Comencem amb una tasca diària senzilla. Sovint hem de buscar alguns objectes a la base de dades, per exemple, trobar una taula a l'esquema i estudiar-ne l'estructura (quines columnes, claus, índexs, restriccions, etc. s'utilitzen). I de qualsevol IDE gràfic o un petit gestor de base de dades, en primer lloc, esperem exactament aquestes habilitats. Perquè sigui ràpid i no hagis d'esperar mitja hora fins que es dibuixi una finestra amb la informació necessària (sobretot amb una connexió lenta a una base de dades remota), i al mateix temps, la informació rebuda és fresca i rellevant, i no escombraries a la memòria cau. A més, com més complexa i gran sigui la base de dades i com més gran sigui el nombre d'elles, més difícil serà fer-ho.
Però normalment llence el ratolí i només escric codi. Suposem que necessiteu esbrinar quines taules (i amb quines propietats) estan contingudes a l'esquema "HR". A la majoria de SGBD, el resultat desitjat es pot aconseguir amb aquesta senzilla consulta des de l'esquema_informació:
select table_name
, ...
from information_schema.tables
where schema = 'HR'De base de dades a base de dades, el contingut d'aquestes taules de referència varien en funció de les capacitats de cada SGBD. I, per exemple, per a MySQL, des del mateix llibre de referència podeu obtenir paràmetres de taula específics per a aquest SGBD:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle no sap information_schema, però sí , i no sorgeixen grans problemes:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse no és una excepció:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Es pot fer alguna cosa semblant a Cassandra (que té famílies de columnes en lloc de taules i espais de claus en lloc d'esquemes):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Per a la majoria de les altres bases de dades, també podeu fer consultes similars (fins i tot Mongo té , que conté informació sobre totes les col·leccions del sistema).
Per descomptat, d'aquesta manera es pot obtenir informació no només sobre taules, sinó sobre qualsevol objecte en general. De tant en tant, persones amables comparteixen aquest codi per a diferents bases de dades, com, per exemple, a la sèrie d'articles d'habra "Funcions per a documentar bases de dades PostgreSQL" (, , ). Per descomptat, mantenir tota aquesta muntanya de consultes al meu cap i escriure-les constantment és un plaer, així que al meu IDE/editor preferit tinc un conjunt pre-preparat de fragments per a consultes d'ús freqüent, i només queda escriure el noms dels objectes a la plantilla.
Com a resultat, aquest mètode de navegació i cerca d'objectes és molt més flexible, estalvia molt de temps i permet obtenir exactament la informació en la forma en què ara és necessària (com, per exemple, es descriu a la publicació). ).
Operacions amb objectes
Després d'haver trobat i estudiat els objectes necessaris, és hora de fer-hi alguna cosa útil. Naturalment, també sense treure els dits del teclat.
No és cap secret que simplement suprimir una taula tindrà el mateix aspecte a gairebé totes les bases de dades:
drop table hr.personsPerò amb la creació de la taula es fa més interessant. Gairebé qualsevol SGBD (inclosos molts NoSQL) pot "crear taula" d'una forma o una altra, i la part principal fins i tot diferirà lleugerament (nom, llista de columnes, tipus de dades), però altres detalls poden diferir dràsticament i dependre de la dispositiu intern i capacitats d'un SGBD específic. El meu exemple preferit és que a la documentació d'Oracle només hi ha BNF "nues" per a la sintaxi "crear taula". . Altres DBMS tenen capacitats més modestes, però cadascun d'ells també té moltes característiques interessants i úniques per crear taules (, , , ). És poc probable que cap "assistent" gràfic d'un altre IDE (especialment un universal) pugui cobrir completament totes aquestes habilitats, i encara que pugui, no serà un espectacle per als dèbils de cor. Al mateix temps, una declaració escrita correcta i oportuna crear una taula us permetrà utilitzar-les fàcilment, fer que l'emmagatzematge i l'accés a les vostres dades siguin fiables, òptims i el més còmodes possibles.
A més, molts SGBD tenen els seus propis tipus d'objectes específics que no estan disponibles en altres SGBD. A més, podem realitzar operacions no només en objectes de base de dades, sinó també en el mateix DBMS, per exemple, "matar" un procés, alliberar una àrea de memòria, habilitar el rastreig, canviar al mode "només lectura" i molt més.
Ara dibuixem una mica
Una de les tasques més habituals és crear un diagrama amb objectes de base de dades i veure els objectes i les connexions entre ells en una imatge bonica. Gairebé qualsevol IDE gràfic, utilitats de "línia d'ordres" separades, eines gràfiques especialitzades i modeladors poden fer-ho. Dibuixaran alguna cosa per a tu "el millor que puguin" i pots influir una mica en aquest procés només amb l'ajuda d'alguns paràmetres del fitxer de configuració o caselles de selecció de la interfície.
Però aquest problema es pot resoldre molt més senzill, més flexible i elegant, i per descomptat amb l'ajuda del codi. Per crear diagrames de qualsevol complexitat, disposem de diversos llenguatges de marcatge especialitzats (DOT, GraphML, etc.), i per a ells tota una dispersió d'aplicacions (GraphViz, PlantUML, Mermaid) que poden llegir aquestes instruccions i visualitzar-les en diversos formats. . Bé, ja sabem com obtenir informació sobre els objectes i les connexions entre ells.
Aquí teniu un petit exemple de com podria semblar això, utilitzant PlantUML i (a l'esquerra hi ha una consulta SQL que generarà la instrucció necessària per a PlantUML, i a la dreta el resultat):
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'I si ho proveu una mica, basat en podeu obtenir alguna cosa molt semblant a un diagrama ER real:
La consulta SQL és una mica més complicada
-- Шапка
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'
Si us fixeu bé, sota el capó moltes eines de visualització també utilitzen consultes similars. És cert que aquestes peticions solen ser profundes , sense oblidar-ne cap modificació.
Mètriques i seguiment
Passem a un tema tradicionalment complex: el seguiment del rendiment de la base de dades. Recordo una petita història real que em va explicar "un dels meus amics". En un altre projecte hi vivia un cert DBA potent, i pocs dels desenvolupadors el coneixien personalment, o l'havien vist mai en persona (malgrat que, segons els rumors, treballava en algun lloc de l'edifici següent). A l'hora "X", quan el sistema de producció d'un gran minorista va començar a "sentir-se malament" una vegada més, va enviar silenciosament captures de pantalla de gràfics d'Oracle Enterprise Manager, on va destacar acuradament els llocs crítics amb un marcador vermell per a la "comprensibilitat" ( això, per dir-ho suaument, no va ajudar gaire). I a partir d'aquesta "targeta fotogràfica" vaig haver de tractar. Al mateix temps, ningú tenia accés al preuat (en els dos sentits de la paraula) Enterprise Manager, perquè el sistema és complex i car, de sobte "els desenvolupadors s'ensopeguen amb alguna cosa i ho trenquen tot". Per tant, els desenvolupadors van trobar "empíricamente" la ubicació i la causa dels frens i van llançar un pedaç. Si la carta amenaçadora del DBA no tornés a arribar en un futur proper, tothom respiraria alleujat i tornaria a les seves tasques actuals (fins a la nova Carta).
Però el procés de seguiment pot semblar més divertit i amigable i, el més important, accessible i transparent per a tothom. Almenys la seva part bàsica, com a complement als principals sistemes de monitoratge (que certament són útils i en molts casos insubstituïbles). Qualsevol SGBD és lliure i absolutament gratuït per compartir informació sobre el seu estat i rendiment actuals. A la mateixa base de dades "sagnant" d'Oracle, es pot obtenir gairebé qualsevol informació sobre el rendiment de les vistes del sistema, des de processos i sessions fins a l'estat de la memòria cau del buffer (per exemple, , secció "Vigilància"). Postgresql també té un munt de vistes del sistema per , en particular aquells que són indispensables en la vida quotidiana de qualsevol DBA, com ara , , . MySQL fins i tot té un esquema separat per a això. . A In Mongo incorporat agrega les dades de rendiment en una col·lecció del sistema .
Així, armat amb algun tipus de col·lector de mètriques (Telegraf, Metricbeat, Collectd) que pot realitzar consultes sql personalitzades, un emmagatzematge d'aquestes mètriques (InfluxDB, Elasticsearch, Timescaledb) i un visualitzador (Grafana, Kibana), podeu obtenir una informació bastant fàcil. i un sistema de monitorització flexible que s'integrarà estretament amb altres mètriques de tot el sistema (obtinguda, per exemple, del servidor d'aplicacions, del SO, etc.). Com, per exemple, això es fa a pgwatch2, que utilitza la combinació InfluxDB + Grafana i un conjunt de consultes a les vistes del sistema, a les quals també es pot accedir .
En total
I aquesta és només una llista aproximada del que es pot fer amb la nostra base de dades mitjançant el codi SQL normal. Segur que podeu trobar molts més usos, escriviu als comentaris. I parlarem de com (i el més important per què) automatitzar tot això i incloure-ho al vostre pipeline CI/CD la propera vegada.
Font: www.habr.com