SQL, yini engaba lula? Ngamunye wethu angabhala isicelo esilula - siyathayipha khetha, bhala amakholomu adingekayo, bese kusukela, igama lethebula, izimo ezithile ku lapho futhi yilokho kuphela - idatha ewusizo isesikhwameni sethu, futhi (cishe) kungakhathaliseki ukuthi iyiphi i-DBMS engaphansi kwe-hood ngaleso sikhathi (noma mhlawumbe ). Ngenxa yalokho, ukusebenza cishe nanoma yimuphi umthombo wedatha (okuhlobene futhi akunjalo) kungacatshangwa ngokombono wekhodi evamile (ngakho konke okushoyo - ukulawula inguqulo, ukubuyekezwa kwekhodi, ukuhlaziya okumile, ukuhlola okuzenzakalelayo, futhi yilokho kuphela). Futhi lokhu akusebenzi kuphela kudatha ngokwayo, ama-schemas nokufuduka, kodwa ngokuvamile kuyo yonke impilo yesitoreji. Kulesi sihloko sizokhuluma ngemisebenzi yansuku zonke kanye nezinkinga zokusebenza ngemininingwane ehlukahlukene ngaphansi kwelensi ye "database njengekhodi".
Ake siqale kusukela . Izimpi zokuqala zohlobo lwe-"SQL vs ORM" zibonwe emuva .
Imephu ehlobene nento
Abasekeli be-ORM ngokwesiko bayakwazisa isivinini nokuthuthuka kalula, ukuzimela ku-DBMS kanye nekhodi ehlanzekile. Kwabaningi bethu, ikhodi yokusebenza nesizindalwazi (futhi ngokuvamile isizindalwazi ngokwaso)
ngokuvamile kubukeka kanje...
@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;
}
...Imodeli ilengiswe ngezichasiselo ezihlakaniphile, futhi endaweni ethile ngemuva kwenkundla i-ORM enesibindi ikhiqiza futhi isebenzise amathani wekhodi ethile ye-SQL. Kodwa-ke, abathuthukisi bazama konke okusemandleni abo ukuzehlukanisa nedathabhesi yabo ngamakhilomitha okungaziwa, okubonisa okunye .
Ngakolunye uhlangothi lwezivimbelo, abalandeli be-SQL ehlanzekile “eyenziwe ngezandla” baqaphela ikhono lokukhama yonke ijusi ku-DBMS yabo ngaphandle kwezendlalelo ezengeziwe kanye nezinto ezikhishwayo. Ngenxa yalokho, amaphrojekthi "e-data-centric" avela, lapho abantu abaqeqeshwe ngokukhethekile behileleke ku-database (baphinde "basicist", baphinde "basicist", baphinde "basdeners", njll.), kanye nabathuthukisi kufanele kuphela "ukudonsa" ukubukwa esenziwe ngomumo kanye nezinqubo ezigciniwe, ngaphandle kokungena emininingwaneni.
Kuthiwani ukube besinemihlaba emihle kakhulu yomibili? Lokhu kwenziwa kanjani ngethuluzi elihle elinegama eliqinisekisa impilo . Ngizokunikeza imigqa embalwa evela kumqondo ojwayelekile ekuhumusheni kwami kwamahhala, futhi ungajwayelana nakho ngemininingwane eyengeziwe. .
I-Clojure iwulimi olupholile lokudala ama-DSL, kodwa i-SQL ngokwayo iyi-DSL epholile, futhi asiyidingi enye. Izisho zika-S zinhle, kodwa azingezi lutho olusha lapha. Ngenxa yalokho, sithola amabakaki ngenxa yabakaki. Awuvumi? Bese ulinda isikhathi lapho ukukhishwa phezu kwe-database kuqala ukuvuza futhi uqala ukulwa nomsebenzi (i-sql eluhlaza)
Manje kufanele ngenzeni? Masishiye i-SQL njenge-SQL evamile - ifayela elilodwa ngesicelo ngasinye:
-- name: users-by-country
select *
from users
where country_code = :country_code... bese ufunda leli fayela, uliguqule libe umsebenzi ojwayelekile we-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" ...} ...)Ngokunamathela kumgomo othi "SQL iyodwa, iClojure ngokwayo", uthola:
- Azikho izimanga ze-syntactic. Isizindalwazi sakho (njenganoma iyiphi enye) ayihambisani 100% nezinga le-SQL - kodwa lokhu akunandaba ku-Yesql. Awusoze wamosha isikhathi ngokuzingela imisebenzi nge-syntax elinganayo ye-SQL. Ngeke kudingeke ukuthi ubuyele emsebenzini (raw-sql "okunye('funky'::SYNTAX)")).
- Ukusekelwa komhleli okuhle kakhulu. Umhleli wakho usenokusekelwa okuhle kakhulu kwe-SQL. Ngokulondoloza i-SQL njenge-SQL ungamane uyisebenzise.
- Ukuhambisana kweqembu. Ama-DBA akho angafunda futhi abhale i-SQL oyisebenzisayo kuphrojekthi yakho ye-Clojure.
- Ukushuna ukusebenza okulula. Udinga ukwakha uhlelo lombuzo oyinkinga? Lokhu akuyona inkinga uma umbuzo wakho kuyi-SQL ejwayelekile.
- Ukusebenzisa kabusha imibuzo. Hudula bese udedela lawo mafayela e-SQL afanayo kwamanye amaphrojekthi ngoba iyi-SQL endala nje - vele wabelane ngayo.
Ngokubona kwami, lo mbono upholile kakhulu futhi ngesikhathi esifanayo ulula kakhulu, ngenxa yokuthi iphrojekthi izuze abaningi ngezilimi ezihlukahlukene. Futhi ngokulandelayo sizozama ukusebenzisa ifilosofi efanayo yokuhlukanisa ikhodi ye-SQL nayo yonke enye into edlula i-ORM.
Abaphathi be-IDE ne-DB
Ake siqale ngomsebenzi olula wansuku zonke. Ngokuvamile kufanele sifune ezinye izinto ku-database, isibonelo, thola itafula ku-schema futhi sifunde isakhiwo salo (iziphi amakholomu, okhiye, izinkomba, izithiyo, njll. ezisetshenziswayo). Futhi kusukela kunoma iyiphi i-IDE eyisithombe noma umphathi we-DB, okokuqala nje, silindele lawa makhono. Ukuze kushesha futhi akudingeki ulinde isigamu sehora kuze kube yilapho kukhishwa iwindi elinolwazi oludingekayo (ikakhulukazi ngokuxhumeka okunensayo ku-database ekude), futhi ngesikhathi esifanayo, ulwazi olutholiwe lusha futhi lufanelekile, futhi hhayi udoti ogciniwe. Ngaphezu kwalokho, lapho i-database iyinkimbinkimbi futhi inkulu futhi isibalo esikhulu sazo, kuba nzima nakakhulu ukwenza lokhu.
Kodwa ngokuvamile ngilahla igundane bese ngibhala ikhodi. Ake sithi udinga ukuthola ukuthi yimaphi amathebula (futhi yiziphi izakhiwo) aqukethwe ku-schema "HR". Kuma-DBMS amaningi, umphumela owufunayo ungafinyelelwa ngalo mbuzo olula ovela ku-information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Kusukela kusizindalwazi kuye kusizindalwazi, okuqukethwe kulawo mathebula ayizethenjwa kuyahlukahluka kuye ngamakhono e-DBMS ngayinye. Futhi, isibonelo, ku-MySQL, encwadini efanayo yereferensi ungathola imingcele yethebula eqondene nale DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'I-Oracle akayazi i-information_schema, kodwa inakho , futhi azikho izinkinga ezinkulu eziphakamayo:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'I-ClickHouse nayo ihlukile:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Okufanayo kungenziwa e-Cassandra (enemindeni yamakholomu esikhundleni samathebula nezikhala zokhiye esikhundleni sama-schema):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Kwezinye izizindalwazi eziningi, ungase futhi uqhamuke nemibuzo efanayo (ngisho neMongo unayo , equkethe ulwazi mayelana nawo wonke amaqoqo ohlelweni).
Yiqiniso, ngale ndlela ungathola ulwazi hhayi kuphela ngamatafula, kodwa nganoma iyiphi into ngokuvamile. Ngokuhamba kwesikhathi, abantu abanomusa babelana ngekhodi enjalo kusizindalwazi esihlukene, njengokuthi, ngokwesibonelo, ochungechungeni lwezihloko ze-habra “Imisebenzi yokubhala imibhalo yolwazi lwe-PostgreSQL” (, , ). Impela, ukugcina yonke le mibuzo ekhanda lami futhi ngihlale ngiyibhala kuyinjabulo enkulu, ngakho-ke ku-IDE/umhleli engiwuthandayo nginesethi elungiselelwe kusengaphambili yamazwibela emibuzweni esetshenziswa njalo, futhi okusele ukuthayipha amagama ezinto kusifanekiso.
Ngenxa yalokho, le ndlela yokuzulazula nokucinga izinto iguquguquka kakhulu, isindisa isikhathi esiningi, futhi ikuvumela ukuthi uthole ulwazi olunembile ngendlela okudingeka ngayo manje (njengoba, isibonelo, kuchazwe kokuthunyelwe. ).
Ukusebenza ngezinto
Ngemva kokuthola nokutadisha izinto ezidingekayo, yisikhathi sokwenza okuthile okuwusizo ngazo. Ngokwemvelo, futhi ngaphandle kokukhipha iminwe yakho kukhibhodi.
Akuyona imfihlo ukuthi ukususa nje ithebula kuzobukeka ngendlela efanayo cishe kuzo zonke izingobo zolwazi:
drop table hr.personsKodwa ngokudalwa kwetafula kuba mnandi kakhulu. Cishe noma iyiphi i-DBMS (kuhlanganise nama-NoSQL amaningi) “ingakha ithebula” ngendlela eyodwa noma enye, futhi ingxenye enkulu yayo izohluka kancane (igama, uhlu lwamakholomu, izinhlobo zedatha), kodwa eminye imininingwane ingahluka kakhulu futhi incike idivayisi yangaphakathi namandla e-DBMS ethile. Isibonelo sami engisithandayo ukuthi emibhalweni ye-Oracle kukhona kuphela ama-BNF "enqunu" we-syntax "yokudala ithebula". . Amanye ama-DBMS anamakhono anesizotha kakhudlwana, kodwa ngayinye yawo inezici eziningi ezithakazelisayo nezihlukile zokudala amatafula (, , , ). Akunakwenzeka ukuthi noma iyiphi “iwizadi” enemifanekiso evela kwenye i-IDE (ikakhulukazi eyendawo yonke) ikwazi ukumboza ngokugcwele wonke lawa makhono, futhi noma ingakwazi, ngeke ibe umbukwane kwabaphelelwe umoya. Ngesikhathi esifanayo, isitatimende esibhaliwe esifanele futhi esifike ngesikhathi dala ithebula izokuvumela ukuthi uzisebenzise kalula zonke, wenze isitoreji kanye nokufinyelela kudatha yakho kuthembeke, kube ngokufanele futhi kukhululeke ngangokunokwenzeka.
Futhi, ama-DBMS amaningi anezinhlobo zawo ezithile zezinto ezingatholakali kwamanye ama-DBMS. Ngaphezu kwalokho, singakwazi ukwenza imisebenzi hhayi kuphela ezintweni ze-database, kodwa naku-DBMS ngokwayo, isibonelo, "ukubulala" inqubo, ukukhulula indawo ethile yememori, ukunika amandla ukulandelela, ukushintshela kumodi "yokufunda kuphela", nokunye okuningi.
Manje ake sidwebe kancane
Omunye wemisebenzi evamile ukwakha umdwebo onezinto ze-database futhi ubone izinto nokuxhumana phakathi kwazo esithombeni esihle. Cishe noma iyiphi i-IDE yesithombe, izinsiza ezihlukene “zomugqa womyalo”, amathuluzi akhethekile wezithombe namamodeli angenza lokhu. Bazokudwebela okuthile “ngokusemandleni abo,” futhi ungakwazi ukuthonya le nqubo kancane kuphela ngosizo lwamapharamitha ambalwa kufayela lokucushwa noma amabhokisi okuhlola kusixhumi esibonakalayo.
Kodwa le nkinga ingaxazululwa kalula kakhulu, eguquguqukayo futhi nenhle, futhi-ke ngosizo lwekhodi. Ukwakha imidwebo yanoma ibuphi ubunkimbinkimbi, sinezilimi ezimbalwa ezikhethekile zokumaka (i-DOT, i-GraphML njll), futhi kuzo zonke izinhlelo zokusebenza ezihlakazekayo (i-GraphViz, i-PlantUML, i-Mermaid) engakwazi ukufunda leyo miyalo futhi uyibone ngeso lengqondo ngamafomethi ahlukahlukene. . Hhayi-ke, sesivele sazi ukuthi singaluthola kanjani ulwazi mayelana nezinto kanye nokuxhumana phakathi kwazo.
Nasi isibonelo esincane sokuthi lokhu kungase kubukeke kanjani, kusetshenziswa i-PlantUML kanye (ngakwesokunxele kunombuzo we-SQL ozokhiqiza imiyalelo edingekayo ye-PlantUML, futhi kwesokudla kukhona umphumela):
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'Futhi uma uzama kancane, bese kusekelwe ungathola into efana kakhulu nomdwebo wangempela we-ER:
Umbuzo we-SQL uyinkimbinkimbi kancane
-- Шапка
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'
Uma ubhekisisa, ngaphansi kwe-hood amathuluzi amaningi okubuka nawo asebenzisa imibuzo efanayo. Yiqiniso, lezi zicelo ngokuvamile zijulile , ingasaphathwa yokuguqulwa kwazo.
Amamethrikhi nokuqapha
Ake sidlulele esihlokweni esijwayelekile esiyinkimbinkimbi - ukuqapha ukusebenza kwesizindalwazi. Ngikhumbula indaba encane eyiqiniso engatshelwa “ngomunye wabangane bami.” Kwenye iphrojekthi kwakuhlala i-DBA ethile enamandla, futhi abambalwa babathuthukisi babemazi mathupha, noma abake bambona mathupha (naphezu kweqiniso lokuthi, ngokusho kwamahemuhemu, wasebenza endaweni ethile esakhiweni esilandelayo). Ngehora elithi “X”, lapho uhlelo lwe-poduction lomthengisi omkhulu luqala “ukuzizwa kabi” futhi, wathumela buthule izithombe-skrini zamagrafu kuMphathi we-Oracle Enterprise, lapho aqokomisa ngokucophelela izindawo ezibucayi ezinomaka obomvu “wokuqonda” ( lokhu, ukukubeka kancane, akuzange kusize kakhulu). Futhi ngokusekelwe kuleli "khadi lesithombe" kwadingeka ngiphathe. Ngesikhathi esifanayo, akekho owayekwazi ukufinyelela okuyigugu (ngemizwa yomibili yegama) I-Enterprise Manager, ngoba uhlelo luyinkimbinkimbi futhi luyabiza, kungazelelwe "abathuthukisi bakhubeka kokuthile futhi baphule yonke into." Ngakho-ke, abathuthukisi "ngokuqina" bathola indawo kanye nembangela yamabhuleki futhi bakhulula isichibi. Uma incwadi esongelayo evela ku-DBA ingafikanga futhi esikhathini esizayo esiseduze, khona-ke wonke umuntu wayezophefumula futhi abuyele emisebenzini yakhe yamanje (kuze kufike Incwadi entsha).
Kodwa inqubo yokuqapha ingabukeka ijabulisa kakhulu futhi inobungane, futhi okubaluleke kakhulu, ifinyeleleka futhi isobala kuwo wonke umuntu. Okungenani ingxenye yayo eyisisekelo, njengesengezo ezinhlelweni zokuqapha eziyinhloko (eziwusizo ngempela futhi ezimweni eziningi ezingenakubuyiselwa). Noma iyiphi i-DBMS imahhala futhi imahhala ukwabelana ngolwazi mayelana nesimo sayo samanje nokusebenza kwayo. Ku-Oracle DB efanayo “yegazi”, cishe noma yiluphi ulwazi olumayelana nokusebenza lungatholwa ekubukweni kwesistimu, kusukela ezinqubweni namaseshini kuya kusimo senqolobane yebhafa (isibonelo, , isigaba "Ukuqapha"). I-Postgresql nayo inenqwaba yokubukwa kwesistimu , ikakhulukazi lezo ezibalulekile empilweni yansuku zonke yanoma iyiphi i-DBA, njenge , , . I-MySQL ine-schema ehlukile yalokhu. . A In Mongo eyakhelwe ngaphakathi ihlanganisa idatha yokusebenza ibe iqoqo lesistimu .
Ngakho-ke, ehlome ngohlobo oluthile lomqoqi wamamethrikhi (iTelegraf, Metricbeat, Collectd) ekwazi ukwenza imibuzo yangokwezifiso ye-sql, indawo yokugcina yalawa mamethrikhi (InfluxDB, Elasticsearch, Timescaledb) kanye nesibonisi (Grafana, Kibana), ungathola kalula kanye nesistimu yokuqapha eguquguqukayo ezohlanganiswa eduze namanye amamethrikhi esistimu (etholwe, isibonelo, kuseva yohlelo lokusebenza, ku-OS, njll.). Njengoba, isibonelo, lokhu kwenziwa ku-pgwatch2, esebenzisa inhlanganisela ye-InfluxDB + Grafana kanye nesethi yemibuzo ekubukweni kwesistimu, engafinyelelwa futhi. .
Inani
Futhi lolu wuhlu olulinganiselwe kuphela lwalokho okungenziwa ngesizindalwazi sethu kusetshenziswa ikhodi evamile ye-SQL. Ngiyaqiniseka ukuthi ungathola ukusetshenziswa okuningi okwengeziwe, bhala kumazwana. Futhi sizokhuluma ngokuthi (futhi okubaluleke kakhulu ukuthi kungani) ukwenza ngokuzenzakalelayo konke lokhu futhi sikufake epayipini lakho le-CI/CD ngokuzayo.
Source: www.habr.com