🥇«Database as Code» kogemus

SQL, mis võiks olla lihtsam? Igaühel meist on võimalik kirjutada lihtne päring — kirjutame select, loetleme vajalikud veerud, siis from, tabeli nime, paar tingimust where ja kõik — kasulikud andmed on meil taskus, peaaegu sõltumata sellest, milline andmebaas praegu töötamas on (või ei ole see üldse andmebaas). Selle tulemusena saab töö peaaegu igasuguste andmeallikatega (suhtelised ja mitte eriti) käsitleda tavalise koodina (kõik, mis sellega kaasneb — versiooni kontroll, koodi ülevaatus, staatiline analüüs, automaatne testimine ja nii edasi). Ja see ei puuduta ainult andmeid, skeeme ja migratsioone, vaid tegelikult kogu andmehoidla tegevust. Selles artiklis räägime igapäevastest ülesannetest ja probleemidest erinevate andmebaasidega töötamisel "andmebaas kui kood" kontekstis.

Ja alustame otse ORM. Esimesed lahingud "SQL vs ORM" olid märgatud juba enne Peeter I.

Objekti-relatsiooniline kaardistamine

ORM-i pooldajad hindavad traditsiooniliselt arenduse kiirust ja lihtsust, sõltumatust andmebaasidest ning koodi puhtust. Paljude jaoks meie seas näeb andmebaasiga töötamise kood (ja sageli ka sama andmebaas)

tavaliselt välja umbes nii…

@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;
    }
  ...

Mudel on kaunistatud nutikate annotatsioonidega, ja kusagil lava taga genereerib ja käivitab vapper ORM tonnide viisi mingit SQL-koodi. Ütlemata selgelt, arendajad püüavad igal juhul tõkestada ennast oma andmebaasi eest kilomeetrite kaupa abstraktsioone, mis viitab teatud "SQL-vihkamine".

Teiselt poolt barricade pooldajad puhtast "handmade"-SQL-ist märgivad, et on võimalus välja pigistada kõik, mis suudab nende andmebaasi, ilma täiendavate vahekihtide ja abstraktsioonideta. Seetõttu tekivad "andmekesksed" projektid, kus andmetega tegelevad spetsiaalselt koolitatud inimesed (nemad on "basiistid", nad on "basiinikud", nad on "basiidid" jne), ja arendajad saavad lihtsalt "tõmmata" valmis vaatamisväärsusi ja salajasi protseduure, mitte süvenedes detailidesse.

Aga mis kui võtta parem osa kahest maailmast? Kuidas seda on tehtud imelises tööriistas, millel on elujõuline nimi Yesql. Tõin välja paar rida üldkonseptsioonist minu vabalt tõlgitud versioonis, kuid selle kohtadega saab tutvuda põhjalikumalt siin.

Clojure on suurepärane keel DSL-ide loomiseks, kuid SQL ise on juba lahe DSL ning me ei vajagi veel ühte. S-expressionid on imelised, kuid siin ei lisa need midagi uut. Lõpuks saame ainult sulud sulude pärast. Ei nõustu? Siis oodake aega, mil andmebaasi abstraktsioon hakkab lekima, ja te alustate funktsiooni vastu võitlemist. (raw-sql)
Ja mida teha? Jätame SQL lihtsaks SQL-iks — üks fail ühe päringu jaoks:

-- name: users-by-country
select *
  from users
 where country_code = :country_code

… ja seejärel loe see fail ning muuda see tavaliseks Clojure funktsiooniks:

(defqueries "some/where/users_by_country.sql"
   {:connection db-spec})

;;; Funktsioon nimega `users-by-country` on loodud.
;;; Kasutame seda:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)

Järgides põhimõtet "SQL eraldi, Clojure eraldi", saate:

Mitte mingeid süntaktilisi üllatusi. Teie andmebaas (nagu iga teine) ei vasta 100% SQL standardile — kuid Yesqli jaoks ei ole see oluline. Te ei pea kunagi aega kulutama funktsioonide jahtimisele, mille süntaks vastab SQL-ile. Te ei pea kunagi tagasi pöörduma funktsiooni (raw-sql "some (‘funky’ :: SYNTAX)")).

Parim toimetaja tugi. Teie toimetaja juba toetab SQLi suurepäraselt. SQL-i säilitades saate seda lihtsalt kasutada.

Meeskonna ühilduvus. Teie DBA-d saavad lugeda ja kirjutada SQL-i, mida te kasutate oma Clojure projektis.

Lihtsustatud jõudluse seadistamine. Peate koostama plaani probleemse päringu jaoks? See pole probleem, kui teie päring on tavaline SQL.

Päringute taaskasutamine. Tõstke need samad SQL-failid teistesse projektidesse, sest see on lihtsalt hea vana SQL — jagage seda lihtsalt.

Minu arvates on idee väga lahe ja samas väga lihtne, tänu millele on projekt omandanud palju järgijaid erinevates keeltes. Ja proovime edasi rakendada sarnast filosoofiat SQL-koodi eraldamiseks kõigest muust, kaugel ORM-ist.

IDE & DB-haldurid

Alustame lihtsast igapäevasest ülesandest. Tihti peame andmebaasist otsima objekte, näiteks leidma skeemist tabeli ja uurima selle struktuuri (milliseid veerge, võtmeid, indekseid, piiranguid jms kasutatakse). Iga graafilise IDE või vähesegi DB-manageri puhul ootame nende funktsioonide olemasolu esmajärjekorras. See peab olema kiire ja me ei peaks ootama poole tunni kuni akna ilmumiseni vajaliku teabega (eriti aeglase ühenduse korral kaugarvutiga), ja samas peab saadud teave olema värske ja asjakohane, mitte mingisugune vahemällu salvestatud vanarahu. Mida keerulisem ja suurem on andmebaas ning mida rohkem neid on, seda keerulisem on seda teha.

Aga tavaliselt panen hiire kuhugi nurka ja lihtsalt kirjutan koodi. Oletame, et peame välja selgitama, millised tabelid (ja milliste omadustega) on skeemis "HR". Enamikus andmebaasihaldussüsteemides saab vajaliku tulemuse kätte sellise lihtsa päringuga information_schema-st:

select table_name
     , ...
  from information_schema.tables
 where schema = 'HR'

Andmebaasist andmebaasi sisaldus varieerub sõltuvalt iga andmebaasihaldussüsteemi võimalustest. Näiteks MySQL-i puhul võib sellelt samalt registrilt saada selle andmebaasihaldussüsteemi spetsiifilisi tabeli parameetreid:

select table_name
     , storage_engine -- Kasutatav "mootor" ("MyISAM", "InnoDB" jne)
     , row_format     -- Ridade formaat ("Fixed", "Dynamic" jne)
     , ...
  from information_schema.tables
 where schema = 'HR'

Oracle ei tunne information_schema-d, aga sellel on olemas Oracle metadata, ja suuri probleeme ei teki:

select table_name
     , pct_free       -- Minimaalne vaba ruum andmepaketis (%)
     , pct_used       -- Minimaalne kasutatud ruum andmepaketis (%)
     , last_analyzed  -- Viimane statistika kogumise kuupäev
     , ...
  from all_tables
 where owner = 'HR'

Sama käib ka ClickHouse'i kohta:

select name
     , engine -- Kasutatav "mootor" ("MergeTree", "Dictionary" jne)
     , ...
  from system.tables
 where database = 'HR'

M midagi sarnast saab teha ka Cassandra's (kus on columnfamilies tabelite asemele ja keyspace'id skeemide asemele):

select columnfamily_name
     , compaction_strategy_class  -- Prügi kogumise strateegia
     , gc_grace_seconds           -- Prügi eluiga
     , ...
  from system.schema_columnfamilies
 where keyspace_name = 'HR'

Enamikule teistest andmebaasidest võib ka sarnaseid päringuid välja mõelda (isegi Mongos on eriline süsteemikollektsioon, mis sisaldab teavet kõigi süsteemi kollektsioonide kohta).

Muidugi võib selle meetodi abil saada teavet mitte ainult tabelite, vaid ka üldiselt iga objekti kohta. Aeg-ajalt jagavad head inimesed sellist koodi erinevate andmebaaside jaoks, nagu näiteks Habr'i artiklis "Funktsioonid PostgreSQL andmebaaside dokumenteerimiseks" (ib, ben, gym). Loomulikult ei ole kogu selle hulga päringute meeles hoidmine ja pidev nende sisestamine just "tohutu meelelahutus", seetõttu on minu lemmik-IDE/redaktoris eelnevalt ettevalmistatud komplekt sageli kasutatavaid snippet'e, ning jääb vaid objekti nimed mallidesse sisestada.

Nii et selline navigeerimise ja objektide otsimise meetod on palju paindlikum, säästab palju aega ning võimaldab saada täpselt seda teavet ja sellisel kujul, nagu praegu vajalik on (nagu näiteks postituses "Andmete eksportimine andmebaasist igas formaadis: mida oskavad IntelliJ platvormi IDE-d").

Objektidega operatsioonid

Pärast seda, kui oleme leidnud ja uurinud vajalikud objektid, on õige aeg nendega midagi kasulikku teha. Loomulikult, hoides samal ajal sõrmed klaviatuurilt eemal.

Ei ole saladus, et lihtne tabeli kustutamine näeb välja peaaegu sama kõigis andmebaasides:

drop table hr.persons

Kuid tabeli loomine on juba huvitavam. Praktikas teab praktiliselt iga andmebaasitarkvara (sealhulgas paljud NoSQL lahendused) mingil kujul "create table", ning selle peamine osa ei erine eriti (nimi, veergude loetelu, andmetüübid), kuid teised detailid võivad oluliselt erineda ning sõltuvad konkreetse andmebaasihalduse sisemisest struktuurist ja võimalustest. Minu lemmiknäide – Oracle'i dokumentatsioonis on ainult tühi BNF andmestruktuuri kohta "create table" täidab 31 lehekülge. Teised andmebaasid pakuvad tagasihoidlikumaid võimalusi, kuid igaühel on ka palju huvitavaid ja ainulaadseid funktsioone tabelite loomisel (postgres, mysql, cockroach, cassandra). Vaevalt suudab mõni graafiline "tuntud" IDE (eriti universaalne) täielikult katta kõiki neid võimalusi, ja isegi kui suudab, on see kindlasti nägemine mitte nõrganärvilistele. Samal ajal võimaldab õigesti ja õigeaegselt kirjutatud käsk create table kasutada kõiki neid võimalusi tõrgeteta, tagades teie andmete salvestamise ja juurdepääsu usaldusväärsuse, optimaalsuse ja maksimaalse mugavuse.

Paljude andmebaaside puhul on ka spetsiaalsed objektitybid, mis puuduvad teistes andmebaasides. Lisaks saame sooritada toiminguid mitte ainult andmebaasi objektide, vaid ka andmebaasi enda üle, näiteks "tappa" protsess, vabastada mälu, lubada jälgimist, minna "ainult lugemise" režiimi ja palju muud.

Nüüd joonistame natuke.

Üks levinumaid ülesandeid on luua diagramm andmebaasi objektidest, et ilusas pildis näha objekte ja nende omavahelisi seoseid. Peaaegu iga graafiline IDE, mõned käsurea utiliidid, spetsialiseeritud graafilised tööriistad ja modelleerijad saavad sellega hakkama. Nad joonistavad teile "nagu oskavad", ning sellele protsessile saab mõjuda vaid mõne parameetri abil konfiguratsioonifailis või valikutega liideses.

Kuid seda probleemi saab lahendada palju lihtsamalt, paindlikumalt ja elegantsemalt, loomulikult koodi abil. Igasuguste diagrammide koostamiseks on meil mitmeid spetsialiseeritud markeerimiskeeli (DOT, GraphML jne), ja nende jaoks terve rida rakendusi (GraphViz, PlantUML, Mermaid), mis oskavad selliseid juhiseid lugeda ja visualiseerida erinevates formaatides. Üldiselt teame, kuidas hankida teavet objektide ja nende seoste kohta.

Tuletame meelde väikese näite selle kohta, kuidas see võiks välja näha, kasutades PlantUML ja demo andmebaasi PostgreSQL jaoks. (vasakul SQL-päring, mis genereerib vajaliku juhise PlantUML jaoks, ja paremal tulemus):

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'

Ja kui natuke vaeva näha, siis ER-mallile toetudes PlantUML jaoks saame midagi, mis sarnaneb tõelise ER-diagrammiga:

SQL-päring on veidi keerulisem.

-- Pealkiri
select '@startuml
        !define Table(name,desc) class name as "desc" << (T,#FFAAAA) >&gt;
        !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
-- Tabelid
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
-- Suhted tabelite vahel
select distinct ccu.table_name || ' "1" --&gt; "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
-- Jalus
select '@enduml'

Kui tähelepanelikult vaadata, siis paljud visualiseerimistööriistad kasutavad ka alla sarnaseid päringuid. Tõsi, et need päringud on tavaliselt sügavalt "koodis peidetud" ja raskesti mõistetavad., rääkimata nende modifitseerimisest.

Mõõdikud ja jälgimine.

Lähme edasi traditsiooniliselt keerulise teema juurde - andmebaasi jõudluse jälgimine. Kõnelen ühest väikesest tõestisündinud loost, mille rääkis mulle "üks mu sõber". Ühes projektis oli olemas ülivõimas DBA, kellel oli vähe tutvusi arendajate seas ning kelle nägu oli paljudele tundmatu (kuigi kuulujuttude järgi töötas ta kusagil naaberhoones). Hetkel "X", kui suure jaemüüja tootmisse süsteem hakkas taas "halvasti tundma", saatis ta vaikselt ekraanipilte Oracle Enterprise Managerist, kus ta hoolikalt tähistas kriitilisi kohasid punase markeriga "arusaadavuse" jaoks (see aitas, õrnalt öeldes, vähe). Nii tuli selle "fotoga" probleeme lahendada. Samas ei olnud kellelgi juurdepääsu kulukale (nii otseses kui kaudses mõttes) Enterprise Managerile, kuna süsteem oli keeruline ja kallis, äkki "arendajad puutuvad millegi külge ja rikuvad kõik ära". Seetõttu leidsid arendajad "empriirilisel" teel viivituste põhjused ja väljastasid parandused. Kui DBA ähvardav kiri ei saabunud varsti uuesti, hingasid kõik kergendatult välja ja naasid oma praeguste ülesannete juurde (enne uut Kirja).

Kuid jälgimisprotsess võib olla palju toredam ja sõbralikum ning mis kõige tähtsam - juurdepääsetav ja läbipaistev kõigile. Olgugi et võiks vähemalt põhiosa, kui täiendus peamistele jälgimissüsteemidele (mis on kindlasti kasulikud ja paljudes olukordades asendamatud). Iga andmebaas on valmis jagama teavet oma hetkeseisust ja jõudlusest vabalt ja täiesti tasuta. Samas "veres" Oracle DB-s saab praktiliselt iga teabe jõudluse kohta saada süsteemsetest vaadetest, alates protsessidest ja sessioonidest kuni vahemälu olekuni (näiteks, DBA skriptid, jaotis "Jälgimine"). Postgresql-is on samuti terve hulk süsteemseid vaateid, et andmebaasi töö jälgimine, sealhulgas mõned, mis on asendamatud iga DBA igapäevaelus, nagu pg_stat_activity, pg_stat_database, pg_stat_bgwriter. MySQL-is on selle jaoks isegi eraldi skeem performance_schema. Ja MongoDB-s on sisseehitatud profilera , mis kogub jõudlusandmeid süsteemilikku kollektsiooni system.profile.

Seega, varustades end mõne mõõdikute koguja (Telegraf, Metricbeat, Collectd) ja mõõdikute salvestiga (InfluxDB, Elasticsearch, Timescaledb) ning visualiseerijaga (Grafana, Kibana), on võimalik luua piisavalt kerge ja paindlik jälgimissüsteem, mis on tihedalt integreeritud teiste üldiste süsteemimeetriatega (mida saadakse näiteks rakenduste serverilt, operatsioonisüsteemilt jne). Nagu näiteks on tehtud pgwatch2-s, kus kasutatakse kombinatsiooni InfluxDB + Grafana ja komplekti päringutest süsteemivaadetele, kuhu saab ka lisada kohandatud päringuid. lisada kohandatud päringud.

Kokkuvõttes

Ja see on ainult umbkaudne nimekiri sellest, mida meie andmebaasiga saab teha tavalise SQL-koodi abil. Olen kindel, et leidub veel palju kasutusvõimalusi, kirjutage kommentaaridesse. Ja sellest, kuidas (ja mis kõige tähtsam, miks) kõik see automatiseerida ja oma CI/CD torujuhtmesse kaasata, räägime järgmine kord.

Allikas: habr.com

„Andmebaas kui kood“ kogemus