🥇"Database seCode" Zvakaitika

SQL, chii chingave chiri nyore? Mumwe nemumwe wedu anogona kunyora chikumbiro chiri nyore - tinonyora sarudza, nyora makoramu anodiwa, ipapo kubva, zita retafura, mamwe mamiriro mukati apo uye ndizvo zvese - data inobatsira iri muhomwe yedu, uye (inenge) zvisinei kuti ndeipi DBMS iri pasi pehodhi panguva iyoyo (kana pamwe kwete DBMS zvachose) Nekuda kweizvozvo, kushanda nechero chero sosi yedata (yehukama uye kwete) inogona kutariswa kubva pakuona kweyakajairwa kodhi (nezvese zvazvinoreva - vhezheni kutonga, ongororo yekodhi, static ongororo, autotests, uye ndizvo zvese). Uye izvi zvinoshanda kwete chete kune data pachayo, schemas uye kutama, asi kazhinji kune hupenyu hwese hwekuchengetedza. Muchikamu chino tichataura pamusoro pemabasa ezuva nezuva uye matambudziko ekushanda nemadhata akasiyana-siyana pasi pe lens ye "database as code".

Uye ngatitangei kubva ORM. Hondo dzekutanga dze "SQL vs ORM" mhando dzakaonekwa kumashure pre-Petrine Rus '.

Chinhu-chine hukama mepu

Vatsigiri veORM vanowanzokoshesa kumhanya uye kureruka kwekusimudzira, rusununguko kubva kuDBMS uye yakachena kodhi. Kune vazhinji vedu, iyo kodhi yekushanda ne database (uye kazhinji dhatabhesi pachayo)

kazhinji zvinotaridzika seizvi ...

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

Iyo modhi yakaturikwa neakangwara zvirevo, uye kumwe kuseri kwezviitiko ORM yakashinga inogadzira uye inoita matani eimwe SQL kodhi. Nenzira, vanogadzira vari kuyedza nepavanogona napo kuti vazviparadzanise kubva kune yavo dhatabhesi nemakiromita ekubvisa, izvo zvinoratidza zvimwe. "SQL kuvenga".

Kune rimwe divi rezvivharo, vateveri veyakachena "yakaitwa nemaoko" SQL inocherekedza kugona kusvina muto wese kubva muDBMS yavo pasina mamwe machira uye abstractions. Nekuda kweizvozvo, mapurojekiti e "data-centric" anooneka, apo vanhu vakanyatsodzidziswa vanobatanidzwa mune dhata (ivo zvakare "basicists", ivo zvakare "basicists", ivo zvakare "basdeners", nezvimwewo), uye vanogadzira chete unofanirwa "kudhonza" iwo akagadzirira-akagadzirwa maonero uye akachengetwa maitiro, pasina kupinda mune zvakadzama.

Ko dai taiva neakanakisa enyika dzese? Izvi zvinoitwa sei mumudziyo unoshamisa une zita rinosimbisa hupenyu Yesql. Ini ndinopa mitsetse yakati wandei kubva kune yakajairika mushanduro yangu yemahara, uye iwe unogona kujairana nayo zvakadzama. pano.

Clojure mutauro unotonhorera wekugadzira DSL, asi SQL pachayo inotonhorera DSL, uye isu hatidi mumwe. S-mataurirwo akanaka, asi haawedzere chero chinhu chitsva pano. Nekuda kweizvozvo, tinowana mabhureki nekuda kwemabhureki. Hamubvumirani here? Wobva wamirira iyo nguva iyo iyo yekubvisa pamusoro peiyo dhatabhesi inotanga kudonha uye unotanga kurwa nebasa (raw-sql)

Saka ndoita sei? Ngatisiye SQL seyakajairika SQL - faira rimwe pachikumbiro:

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

... wobva waverenga iyi faira, uchiishandura kuita yenguva dzose basa reClojure:

(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" ...} ...)

Nekunamatira kune "SQL yega, Clojure yega" musimboti, unowana:

No syntactic zvinoshamisa. Yako dhatabhesi (seimwe chero ipi zvayo) haina 100% inoenderana neSQL standard - asi izvi hazvina basa kune Yesql. Iwe haungambotambisa nguva uchivhima mabasa neSQL yakaenzana syntax. Iwe hauzombofa wakafanirwa kudzokera kune basa (raw-sql "zvimwe('funky'::SYNTAX)")).

Best editor support. Mupepeti wako atove nerutsigiro rwakanakisa rweSQL. Nekuchengetedza SQL seSQL unogona kungoishandisa.

Kuenderana kwechikwata. MaDBA ako anogona kuverenga nekunyora SQL yaunoshandisa muchirongwa chako cheClojure.

Kugadzirisa kuita kuri nyore. Unoda kugadzira chirongwa chemubvunzo unonetsa? Iri harisi dambudziko kana mubvunzo wako uri wenguva dzose SQL.

Kushandisazve mibvunzo. Dhova uye udonhedze iwo mamwe mafaera eSQL mune mamwe mapurojekiti nekuti ingori pachena SQL yekare - ingogovana.

Mukuona kwangu, pfungwa yacho inotonhorera uye panguva imwe chete iri nyore, nekuda kweiyo purojekiti yakawana vakawanda vateveri mumitauro yakasiyana-siyana. Uye isu tichazoedza kushandisa huzivi hwakafanana hwekuparadzanisa SQL kodhi kubva kune zvimwe zvese zviri kure kupfuura ORM.

IDE & DB mamaneja

Ngatitange nebasa riri nyore remazuva ese. Kazhinji isu tinofanirwa kutsvaga zvimwe zvinhu mudhatabhesi, semuenzaniso, tsvaga tafura mune schema uye tidzidze chimiro chayo (zvipi makoramu, makiyi, indexes, zvipingaidzo, nezvimwewo zvinoshandiswa). Uye kubva kune chero graphical IDE kana diki DB-maneja, chekutanga pane zvese, isu tinotarisira chaizvo izvi kugona. Saka kuti inokurumidza uye haufaniri kumirira hafu yeawa kusvikira hwindo rine ruzivo rwakakosha rakakweverwa (kunyanya nekubatanidza kunononoka kune database iri kure), uye panguva imwe chete, ruzivo rwakagamuchirwa rutsva uye rwakakodzera, uye kwete cached junk. Uyezve, iyo yakanyanya kuoma uye yakakura dhatabhesi uye yakakura nhamba yavo, zvakanyanya kuoma kuita izvi.

Asi kazhinji ndinokanda mbeva kure uye ndinongonyora kodhi. Ngatiti iwe unofanirwa kuziva kuti ndeapi matafura (uye ane zvivakwa) ari mu "HR" schema. MumaDBMS mazhinji, mhedzisiro inodiwa inogona kuwanikwa nemubvunzo uyu wakapusa kubva information_schema:

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

Kubva padhatabhesi kuenda kune dhatabhesi, zviri mukati meiyo mareferensi matafura zvinosiyana zvichienderana nekugona kweDBMS yega yega. Uye, semuenzaniso, yeMySQL, kubva mubhuku rimwechete rereferensi unogona kuwana tafura paramita yakanangana neiyo DBMS:

select table_name
     , storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
     , row_format     -- Формат строки ("Fixed", "Dynamic" etc)
     , ...
  from information_schema.tables
 where schema = 'HR'

Oracle haazive information_schema, asi ine Oracle metadata, uye hapana matambudziko makuru anomuka:

select table_name
     , pct_free       -- Минимум свободного места в блоке данных (%)
     , pct_used       -- Минимум используемого места в блоке данных (%)
     , last_analyzed  -- Дата последнего сбора статистики
     , ...
  from all_tables
 where owner = 'HR'

ClickHouse haina mutsauko:

select name
     , engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
     , ...
  from system.tables
 where database = 'HR'

Chimwe chinhu chakafanana chinogona kuitwa muCassandra (iyo ine mhuri yemhuri panzvimbo yematafura nenzvimbo dzekiyi pachinzvimbo chezvirongwa):

select columnfamily_name
     , compaction_strategy_class  -- Стратегия сборки мусора
     , gc_grace_seconds           -- Время жизни мусора
     , ...
  from system.schema_columnfamilies
 where keyspace_name = 'HR'

Kune mamwe akawanda dhatabhesi, unogona zvakare kuuya nemibvunzo yakafanana (kunyangwe Mongo ane special system collection, iyo ine ruzivo nezvese kuunganidzwa muhurongwa).

Zvechokwadi, nenzira iyi unogona kuwana ruzivo kwete chete pamusoro pematafura, asi pamusoro pechinhu chero chipi zvacho. Nguva nenguva, vanhu vane mutsa vanogovana kodhi yakadaro kune akasiyana dhatabhesi, semuenzaniso, munhevedzano yezvinyorwa zvehabra "Mashandiro ekunyora PostgreSQL dhatabhesi" (Ayb, Ben, gym) Ehe, kuchengeta iri gomo rese remibvunzo mumusoro mangu uye kugara ndichivanyora kunonakidza, saka muIDE / mupepeti wangu wandinoda ndine seti yakafanogadzirirwa yezvimedu zvemibvunzo inowanzo shandiswa, uye chasara kunyora mazita echinhu mutemplate.

Nekuda kweizvozvo, iyi nzira yekufamba nekutsvaga zvinhu inoshanduka zvakanyanya, inochengetedza nguva yakawanda, uye inobvumidza iwe kuti uwane iyo chaiyo ruzivo muchimiro chayave kudikanwa (senge, semuenzaniso, inotsanangurwa mune post. "Kutumira kunze data kubva kudhatabhesi mune chero fomati: izvo maIDE anogona kuita paIntelliJ papuratifomu").

Kushanda nezvinhu

Mushure mekunge tawana uye nekudzidza zvinhu zvinodiwa, inguva yekuita chimwe chinhu chinobatsira navo. Sezvingatarisirwa, zvakare pasina kubvisa zvigunwe zvako pa keyboard.

Haisi chakavanzika kuti kungodzima tafura kunotaridzika zvakafanana mune angangoita ese dhatabhesi:

drop table hr.persons

Asi nekusikwa kwetafura kunowedzera kunakidza. Inenge chero DBMS (kusanganisira yakawanda NoSQL) inogona "kugadzira tafura" mune imwe nzira kana imwe, uye chikamu chikuru chayo chinotosiyana zvishoma (zita, rondedzero yemakoramu, mhando dzedata), asi mamwe madhata anogona kusiyana zvakanyanya uye zvinoenderana ne mudziyo wemukati uye kugona kweiyo chaiyo DBMS. Muenzaniso wangu wandinoda ndewekuti muzvinyorwa zveOracle munongori "kushama" maBNF eiyo "gadzira tafura" syntax. tora mapeji makumi matatu nerimwe. Mamwe maDBMS ane mamwe maitiro ane mwero, asi rimwe nerimwe rawo rine akawanda anonakidza uye akasiyana maficha ekugadzira matafura (postgres, mysql, jongwe, cassandra) Hazvigoneki kuti chero graphical "wizard" kubva kune imwe IDE (kunyanya iyo yepasirese) ichakwanisa kuvhara zvizere zvese izvi kugona, uye kunyangwe zvichigona, haizove chishamiso kune vakaneta vemoyo. Panguva imwecheteyo, chirevo chakanyorwa chakarurama uye panguva yakakodzera kugadzira tafura ichakubvumidza kuti ushandise ese ari nyore, ita kuchengetedza uye kuwana kune yako data yakavimbika, yakakwana uye yakasununguka sezvinobvira.

Zvakare, maDBMS mazhinji ane marudzi awo chaiwo ezvinhu zvisingawanikwe mune mamwe maDBMS. Uyezve, isu tinogona kuita mashandiro kwete pazvinhu zvepa database chete, asiwo paDBMS pachayo, semuenzaniso, "kuuraya" maitiro, kusunungura imwe nzvimbo yekurangarira, kugonesa kutsvaga, chinja kune "kuverenga chete" maitiro, nezvimwe zvakawanda.

Zvino ngatidhirowei zvishoma

Rimwe remabasa akajairika ndeyekuvaka dhayagiramu ine dhatabhesi zvinhu uye kuona zvinhu uye kubatana pakati pazvo mumufananidzo wakanaka. Anenge chero graphical IDE, yakaparadzana "command line" zvishandiso, nyanzvi yemifananidzo maturusi uye modhi vanogona kuita izvi. Vachakukwevera chimwe chinhu "sezvavanogona," uye iwe unogona kupesvedzera maitiro aya zvishoma chete nerubatsiro rwema paramita mashoma mufaira rekugadzirisa kana mabhokisi ekutarisa mune interface.

Asi dambudziko iri rinogona kugadziriswa zvakanyanya nyore, rinoshanduka uye rakanaka, uye zvechokwadi nerubatsiro rwekodhi. Kugadzira madhayagiramu emhando ipi neipi yakaoma, isu tine akati wandei mitauro yekumaka (DOT, GraphML nezvimwewo), uye kwavari kupararira kwese kwekushandisa (GraphViz, PlantUML, Mermaid) iyo inogona kuverenga mirairo yakadai uye kuiona mumhando dzakasiyana siyana. . Zvakanaka, isu tatoziva nzira yekuwana ruzivo nezvezvinhu uye kubatana pakati pazvo.

Heino muenzaniso mudiki wekuti izvi zvingaite sei, uchishandisa PlantUML uye demo database yePostgreSQL (kuruboshwe kune SQL query inoburitsa rairo inodiwa yePlantUML, uye kurudyi ndiko mhedzisiro):

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'

Uye kana iwe ukaedza zvishoma, zvino zvichibva pane ER template yePlantUML iwe unogona kuwana chimwe chinhu chakafanana neicho chaiyo ER dhizaini:

Mubvunzo weSQL wakaoma zvishoma

-- Шапка
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'

Kana iwe ukanyatsotarisisa, pasi pehodhi maturusi mazhinji ekuona anoshandisawo mibvunzo yakafanana. Chokwadi, zvikumbiro izvi zvinowanzova zvakadzama "hardwired" mune kodhi yekushandisa pachayo uye yakaoma kunzwisisa, tisingatauri kuchinjwa kwadzo.

Metrics uye monitoring

Ngatienderei kune imwe nyaya yakajairika yakaoma - database performance monitoring. Ndinoyeuka nyaya duku yechokwadi yandakaudzwa ne“mumwe weshamwari dzangu.” Pane imwe purojekiti paigara imwe DBA ine simba, uye vashoma vevagadziri vaimuziva iye pachake, kana kuti vakanga vambomuona iye pachake (pasinei nokuti, maererano nerunyerekupe, akashanda pane imwe nzvimbo muchivako chinotevera). Paawa "X", apo poduction system yemutengesi muhombe yakatanga "kunzwa zvakaipa" zvakare, akatumira chinyararire magirafu magirafu kubva kuOracle Enterprise Manager, paari akanyatso simbisa nzvimbo dzakakosha nechiratidzo chitsvuku che "kunzwisisa" ( izvi, kutaura zvinyoro, hazvina kubatsira zvakanyanya). Uye zvichibva pane iyi "kadhi remufananidzo" ndaifanira kurapa. Panguva imwecheteyo, hapana aikwanisa kuwana iyo yakakosha (mune zvese pfungwa dzeizwi) Enterprise Manager, nekuti iyo sisitimu yakaoma uye inodhura, kamwe kamwe "vagadziri vanogumburwa pane chimwe chinhu uye vanopwanya zvese." Naizvozvo, vagadziri "empirically" vakawana nzvimbo uye chikonzero chemabhureki uye vakaburitsa chigamba. Kana tsamba inotyisa kubva kuDBA isina kusvika zvakare munguva pfupi iri kutevera, ipapo munhu wese aizotura befu uye odzokera kumabasa avo azvino (kusvikira Tsamba itsva).

Asi maitiro ekutarisa anogona kutaridzika zvakanyanya kunakidza uye ane hushamwari, uye zvakanyanya kukosha, kuwanikwa uye pachena kune wese munhu. Irinani chikamu chayo chakakosha, sekuwedzera kune makuru ekutarisa masisitimu (ayo anonyatsobatsira uye mune dzakawanda zviitiko zvisingadzoreki). Chero DBMS yakasununguka uye isingabhadharise kugovera ruzivo nezve mamiriro ayo uye kuita kwayo. Mune imwecheteyo "yeropa" Oracle DB, chero ruzivo rwekuita runogona kuwanikwa kubva pakuona kwehurongwa, kubva kumatanho uye zvikamu kusvika kune iyo buffer cache (semuenzaniso, DBA Zvinyorwa, chikamu "Monitoring"). Postgresql zvakare ine boka rese rekuona system ye database monitoring, kunyanya izvo zvinonyanya kukosha muhupenyu hwezuva nezuva chero DBA, senge pg_stat_activity, pg_stat_database, pg_stat_bgwriter. MySQL inotova neyakasiyana schema yeizvi. performance_schema. A MuMongo yakavakirwa-mukati profiler inounganidza data yekuita muunganidzwa wehurongwa system.profile.

Saka, yakashongedzerwa neimwe mhando yemametric muunganidzi (Telegraf, Metricbeat, Yakaunganidzwa) inogona kuita tsika sql mibvunzo, chengetedzo yeaya metrics (InfluxDB, Elasticsearch, Timescaledb) uye inoona (Grafana, Kibana), unogona kuwana zviri nyore. uye inochinjika yekutarisa sisitimu iyo ichave yakanyatso kubatanidzwa nemamwe masisitimu-yakakura metrics (yakawanikwa, semuenzaniso, kubva kune server application, kubva kuOS, nezvimwewo). Sezvo, semuenzaniso, izvi zvinoitwa mu pgwatch2, iyo inoshandisa iyo InfluxDB + Grafana musanganiswa uye seti yemibvunzo kune system maonero, ayo anogona kuwanikwa zvakare. wedzera mibvunzo yakajairika.

Total

Uye iyi ingori rondedzero yezvinogona kuitwa nedhatabhesi redu uchishandisa yakajairwa SQL kodhi. Ndine chokwadi chekuti unogona kuwana mamwe mashandisiro akawanda, nyora mumashoko. Uye isu tichataura nezve sei (uye zvinonyanya kukosha nei) kuita otomatiki zvese izvi uye nekuzvisanganisa muCI / CD pombi yako nguva inotevera.

Source: www.habr.com

"Database seCode" Zvakaitika