SQL, yintoni enokuba lula? Ngamnye wethu unokubhala isicelo esilula - sichwetheza khetha, dwelisa imiqolo efunekayo, ngoko ku, igama letheyibhile, ezinye iimeko ngaphakathi apho kwaye yiyo yonke loo nto-idatha eluncedo epokothweni yethu, kwaye (phantse) nokuba yeyiphi iDBMS ephantsi kwehod ngelo xesha (okanye mhlawumbi ). Ngenxa yoko, ukusebenza phantse nawuphi na umthombo wedatha (ubudlelwane kwaye akunjalo) kunokuqwalaselwa ngokwembono yekhowudi eqhelekileyo (kunye nayo yonke into ekuthethwa ngayo - ulawulo lwenguqulelo, uphononongo lwekhowudi, uhlalutyo lwe-static, uvavanyo oluzenzekelayo, kwaye konke oko). Kwaye oku akusebenzi kuphela kwidatha ngokwayo, i-schemas kunye nokufuduka, kodwa ngokubanzi kubomi bonke bokugcina. Kule nqaku siza kuthetha ngemisebenzi yemihla ngemihla kunye neengxaki zokusebenza kunye neendawo ezahlukeneyo zolwazi phantsi kwelensi ye "database njengekhowudi".
Kwaye masiqale ukusuka . Iimfazwe zokuqala zohlobo lwe "SQL vs ORM" zaqatshelwa emva .
Imephu enxulumene nenjongo
Abaxhasi be-ORM ngokuqhelekileyo baxabisa isantya kunye nokukhululeka kophuhliso, ukuzimela kwi-DBMS kunye nekhowudi ecocekileyo. Kwabaninzi bethu, ikhowudi yokusebenza kunye nesiseko sedatha (kwaye rhoqo isiseko sedatha ngokwayo)
iqhele ukukhangeleka ngolu hlobo...
@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 ixhonywe ngezichasiselo ezikrelekrele, kwaye kwenye indawo ngasemva kwemiboniso iORM ekhaliphileyo ivelisa kwaye iphumeze iitoni zekhowudi yeSQL. Ngendlela, abaphuhlisi bazama konke okusemandleni abo ukuba bazihlukanise nedatha yabo ngeekhilomitha zokutsalwa, nto leyo ebonisa ukuba .
Ngakolunye uhlangothi lwezithintelo, abalandeli be-SQL ecocekileyo "eyenziwe ngesandla" baqaphela ukukwazi ukukhama yonke ijusi ngaphandle kwe-DBMS yabo ngaphandle kweeleyile ezongezelelweyo kunye nezithintelo. Ngenxa yoko, iiprojekthi "zedatha-centric" zivela, apho abantu abaqeqeshwe ngokukodwa babandakanyeka kwisiseko sedatha (kwakhona "basicists", nabo "basicists", nabo "basdeners", njl.), kunye nabaphuhlisi. kufuneka kuphela "ukudonsa" iimbono ezisele zenziwe kunye neenkqubo ezigciniweyo, ngaphandle kokungena kwiinkcukacha.
Kuthekani ukuba besinowona mhlaba ubalaseleyo? Kwenziwa njani oku ngesixhobo esimangalisayo esinegama eliqinisekisa ubomi . Ndiza kunika imigca embalwa ukusuka kwingqikelelo eqhelekileyo kwinguqulelo yam yasimahla, kwaye ungaqhelana nayo ngokweenkcukacha ezithe vetshe. .
I-Clojure lulwimi olupholileyo lokudala ii-DSL, kodwa i-SQL ngokwayo yi-DSL epholileyo, kwaye asiyifuni enye. Iintetho zika-S zilungile, kodwa awongezi nto intsha apha. Ngenxa yoko, sifumana izibiyeli ngenxa yezibiyeli. Awuvumi? Emva koko linda umzuzu xa ukukhutshwa kwi-database kuqala ukuvuza kwaye uqala ukulwa nomsebenzi (krwada-sql)
Ndithini ke? Masiyishiye iSQL njengeSQL eqhelekileyo-ifayile enye ngesicelo ngasinye:
-- name: users-by-country
select *
from users
where country_code = :country_code... kwaye emva koko ufunde le fayile, uyiguqule ibe ngumsebenzi oqhelekileyo weClojure:
(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" ...} ...)Ngokubambelela kumgaqo othi "SQL iyodwa, iClojure ngokwayo", ufumana:
- Akukho nto imangalisayo. I-database yakho (njengayo nayiphi na enye) ayihambelani ne-100% nomgangatho we-SQL - kodwa oku akunamsebenzi ku-Yesql. Awunakuze uchithe ixesha lokuzingela imisebenzi kunye ne-SQL elinganayo syntax. Awunakuze ubuyele kumsebenzi (krwada-sql "ezinye('funky'::SYNTAX)")).
- Eyona nkxaso yomhleli. Umhleli wakho sele enenkxaso ebalaseleyo yeSQL. Ngokugcina iSQL njengeSQL unokuyisebenzisa ngokulula.
- Ukuhambelana kweqela. Ii-DBA zakho zinokufunda kwaye zibhale iSQL oyisebenzisayo kwiprojekthi yakho yeClojure.
- Ulungelelwaniso lokusebenza olulula. Ngaba ufuna ukwakha isicwangciso sombuzo onengxaki? Oku akuyongxaki xa umbuzo wakho uyi-SQL eqhelekileyo.
- Ukusebenzisa kwakhona imibuzo. Tsala kwaye ulahle ezo fayile zeSQL ezifanayo kwezinye iiprojekthi kuba yiSQL endala ecacileyo-yabelane ngayo.
Ngokombono wam, ingcamango ipholile kakhulu kwaye ngexesha elifanayo ilula kakhulu, ngenxa yokuba iprojekthi ifumene abaninzi kwiilwimi ezahlukeneyo. Kwaye ngokulandelayo siza kuzama ukusebenzisa ifilosofi efanayo yokwahlula ikhowudi ye-SQL kuyo yonke enye into engaphaya kwe-ORM.
IDE & DB abaphathi
Masiqale ngomsebenzi olula wemihla ngemihla. Ngokuqhelekileyo kufuneka sikhangele izinto ezithile kwisiseko sedatha, umzekelo, ukufumana itafile kwi-schema kwaye sifunde isakhiwo sayo (zeziphi iikholamu, izitshixo, izalathisi, izithintelo, njl. njl.). Kwaye kuyo nayiphi na i-IDE yomzobo okanye i-DB-manejala encinci, okokuqala kuzo zonke, silindele kanye ezi zakhono. Ngoko ke ngokukhawuleza kwaye akufuneki ulinde isiqingatha seyure de iwindow eneenkcukacha eziyimfuneko izotywe (ingakumbi ngoqhagamshelwano olucothayo kwisiseko sedatha esikude), kwaye ngexesha elifanayo, ulwazi olufunyenweyo lutsha kwaye lufanelekile, kwaye ayigcinwanga yinkunkuma. Ngaphezu koko, okukhona intsonkothe ngakumbi kwaye inkulu i-database kunye nenani elikhulu lazo, kokukhona kunzima ukwenza oku.
Kodwa ngesiqhelo ndilahla imawusi kwaye ndibhala ikhowudi. Masithi kufuneka ufumanise ukuba zeziphi iitafile (kwaye zeziphi iipropathi) eziqulethwe kwi-"HR" schema. KwiiDBMS ezininzi, isiphumo esifunwayo sinokufikelelwa ngalo mbuzo ulula kwi-information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Ukusuka kwisiseko sedatha ukuya kwisiseko sedatha, imixholo yezo tafile zereferensi ziyahluka ngokuxhomekeke kubuchule beDBMS nganye. Kwaye, umzekelo, kwi-MySQL, kwincwadi enye yereferensi unokufumana iiparamitha zetafile ezikhethekileyo kule DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'U-Oracle akayazi i-information_schema, kodwa unayo , kwaye akukho ngxaki zinkulu zivelayo:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'I-ClickHouse nayo iyahluka:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Into efana nale inokwenziwa kwiCassandra (eneentsapho eziziintsika endaweni yeetafile nezithuba ezingundoqo endaweni yeschema):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Kuninzi lwabanye oovimba beenkcukacha, ungeza nemibuzo efanayo (nokuba uMongo unayo , equlethe ulwazi malunga nazo zonke iingqokelela kwinkqubo).
Ngokuqinisekileyo, ngale ndlela ungafumana ulwazi malunga neetafile kuphela, kodwa malunga nayiphi na into ngokubanzi. Ngamaxesha ngamaxesha, abantu abanobubele babelana ngekhowudi enjalo kwiinkcukacha ezahlukeneyo, njengokuba, umzekelo, kuluhlu lwamanqaku e-habra "Imisebenzi yokubhala i-PostgreSQL yolwazi" (, , ). Ewe, ukuyigcina yonke le ntaba yemibuzo entlokweni yam kwaye ndisoloko ndiyichwetheza kuluyolo, ke kwi-IDE/umhleli endiwuthandayo ndineseti esele ilungiselelwe ngaphambili yemibuzo esetyenziswa rhoqo, kwaye okuseleyo kukuchwetheza amagama ezinto kwitemplate.
Ngenxa yoko, le ndlela yokukhangela kunye nokukhangela izinto iguquguquka ngakumbi, igcina ixesha elininzi, kwaye ikuvumela ukuba ufumane ulwazi oluchanekileyo ngendlela ekuyimfuneko ngayo ngoku (njengoko, umzekelo, echazwe kwisithuba. ).
Ukusebenza ngezinto
Emva kokuba sifumene kwaye sifunde izinto eziyimfuneko, lixesha lokuba senze into eluncedo kunye nabo. Ngokwemvelo, ngaphandle kokuthatha iminwe yakho kwikhibhodi.
Akuyomfihlo ukuba ukucima nje itafile kuya kujongeka ngokufanayo phantse kuzo zonke iidathabheyisi:
drop table hr.personsKodwa ngokudalwa kwetafile kuba yinto enomdla ngakumbi. Phantse nayiphi na i-DBMS (kubandakanywa ezininzi ze-NoSQL) "inokwenza itafile" ngendlela enye okanye enye, kwaye inxalenye ephambili yayo iya kwahluka kancinane (igama, uluhlu lweekholamu, iintlobo zedatha), kodwa ezinye iinkcukacha zinokwahluka kakhulu kwaye zixhomekeke isixhobo sangaphakathi kunye nobuchule be-DBMS ethile. Umzekelo wam endiwuthandayo kukuba kuxwebhu lwe-Oracle kukho kuphela ii-BNF “zeze” ze-"yenza itafile" isintaksi. . Ezinye ii-DBMS zinamandla athobekileyo, kodwa nganye kuzo inezinto ezininzi ezinomdla kunye nezohlukileyo zokudala iitafile (, , , ). Akunakwenzeka ukuba nayiphi na i-graphical "wizard" esuka kwenye i-IDE (ingakumbi i-universal) iya kukwazi ukugubungela ngokupheleleyo zonke ezi zakhono, kwaye nokuba iyakwazi, ayiyi kuba ngumboniso wentliziyo etyhafileyo. Ngexesha elifanayo, ingxelo ebhaliweyo ngokuchanekileyo nangexesha Yenza itafile iya kukuvumela ukuba uzisebenzise ngokulula zonke, wenze ugcino kunye nokufikelela kwidatha yakho ethembekileyo, ilunge kwaye ikhululeke kangangoko.
Kwakhona, ii-DBMS ezininzi zineentlobo zazo ezithile zezinto ezingafumanekiyo kwezinye ii-DBMS. Ngaphezu koko, sinokwenza imisebenzi kungekhona kuphela kwizinto zedatha, kodwa nakwi-DBMS ngokwayo, umzekelo, "ukubulala" inkqubo, ukukhulula indawo ethile yememori, vumela ukulandelwa, ukutshintshela kwimodi "yokufunda kuphela", kunye nokunye okuninzi.
Ngoku masizobe kancinane
Omnye wemisebenzi eqhelekileyo kukwakha umzobo kunye nezinto zedatha kwaye ubone izinto kunye nokudibanisa phakathi kwabo kumfanekiso omhle. Phantse nayiphi na i-IDE yomzobo, izinto eziluncedo “zomgca womyalelo” ezahlukeneyo, izixhobo ezikhethekileyo zemizobo kunye neemodeli ezinokwenza oku. Baza kukutsalela into "ngeyona nto banokuyenza," kwaye unokuphembelela le nkqubo kancinci kuphela ngoncedo lweeparamitha ezimbalwa kwifayile yoqwalaselo okanye iibhokisi zokukhangela kwi-interface.
Kodwa le ngxaki ingasonjululwa ngokulula kakhulu, iguquguquka ngakumbi kwaye intle, kwaye kunjalo ngoncedo lwekhowudi. Ukwenza imizobo yazo nakuphi na ukuntsonkotha, sineelwimi ezininzi ezikhethekileyo zokuphawula (DOT, GraphML njl njl), kwaye kubo ukusasazeka kwezicelo (iGraphViz, iPlantUML, iMermaid) ekwazi ukufunda imiyalelo enjalo kwaye ibonakale kwiifomathi ezahlukeneyo. . Ewe, sele siyayazi indlela yokufumana ulwazi malunga nezinto kunye nonxibelelwano phakathi kwazo.
Nanku umzekelo omncinci wokuba oku kunokujongeka njani, usebenzisa iPlantUML kunye (ngasekhohlo ngumbuzo weSQL ozakuvelisa umyalelo ofunekayo wePlantUML, kwaye ekunene sisiphumo):
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'Kwaye ukuba uzama kancinane, ngoko ngokusekelwe ungafumana into efana kakhulu nomzobo we-ER wokwenyani:
Umbuzo weSQL unzima kancinci
-- Шапка
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' 
Ukuba ujongisisa, phantsi kwe-hood izixhobo ezininzi zokujonga nazo zisebenzisa imibuzo efanayo. Enyanisweni, ezi zicelo zidla ngokunzulu , singasathethi ke ngokuguqulwa kwazo.
Iimetriki kunye nokubeka iliso
Masiqhubele phambili kwisihloko esintsonkothileyo ngokwesiko-ukubeka iliso kugcino lokusebenza. Ndikhumbula ibali elincinci lokwenyani elibaliselwa kum "ngomnye wabahlobo bam." Kwenye iprojekthi kwakuhlala i-DBA ethile enamandla, kwaye abambalwa abaphuhlisi babemazi buqu, okanye bake bambona ngokwakhe (nangona inyani yokuba, ngokutsho kwamahemuhemu, wayesebenza kwindawo ethile kwisakhiwo esilandelayo). Ngeyure "X", xa inkqubo ye-poduction yomthengisi omkhulu yaqala "ukuziva kakubi" kwakhona, wathumela ngokuthula izikrini zeegrafu ezivela kuMphathi we-Oracle Enterprise, apho wagxininisa ngononophelo iindawo ezibalulekileyo ezinophawu olubomvu "lokuqonda" ( oku, ukuyibeka ngobulali, akuzange kuncede kakhulu). Kwaye ngokusekelwe kule "ikhadi lesithombe" kwafuneka ndiyiphathe. Kwangaxeshanye, akukho mntu wayenokufikelela kwizinto ezixabisekileyo (kuzo zombini iingqiqo zegama) uMphathi weShishini, kuba inkqubo inzima kwaye iyabiza, ngokukhawuleza "abaphuhlisi bayakhubeka kwinto baze baphule yonke into." Ke ngoko, abaphuhlisi "ngokuqina" bafumene indawo kunye nonobangela weebhuleki kwaye bakhulula isiqwenga. Ukuba ileta esongelayo evela kwi-DBA ayizange ifike kwakhona kwixesha elizayo elingekude, ngoko wonke umntu uya kuphefumla kwaye abuyele kwimisebenzi yakhe yangoku (de kube yiLetter entsha).
Kodwa inkqubo yokubeka iliso inokubonakala imnandi kwaye inobubele, kwaye okona kubaluleke kakhulu, ifikeleleke kwaye ivuleleke kumntu wonke. Ubuncinci inxalenye yayo esisiseko, njengokongeza kwiinkqubo eziphambili zokubeka iliso (eziluncedo ngokuqinisekileyo kwaye kwiimeko ezininzi ezingenakubuyiselwa). Nayiphi na i-DBMS ikhululekile kwaye ikhululekile ngokupheleleyo ukwabelana ngolwazi malunga nesimo sayo sangoku kunye nokusebenza. Kwakwi-Oracle DB efanayo "yegazi", phantse naluphi na ulwazi malunga nokusebenza kunokufunyanwa kwiimbono zenkqubo, ukusuka kwiinkqubo kunye neeseshini ukuya kwimeko ye-buffer cache (umzekelo, , icandelo "Ukubeka iliso"). I-Postgresql nayo ineqela elipheleleyo leembono zenkqubo , ngakumbi ezo ziyimfuneko kubomi bemihla ngemihla bayo nayiphi na i-DBA, njenge , , . I-MySQL ine-schema eyahlukileyo yale nto. . A EMongo eyakhelwe-ngaphakathi ihlanganisa idatha yokusebenza ibe yingqokelela yesixokelelwano .
Ke, uxhobe ngohlobo oluthile lomqokeleli weemetrics (iTelegraf, Metricbeat, Collectd) enokwenza imibuzo yesiko le-sql, ukugcinwa kwezi metrics (InfluxDB, Elasticsearch, Timescaledb) kunye nombonisi (Grafana, Kibana), ungafumana lula ngokufanelekileyo. kunye nenkqubo yokubeka iliso eguquguqukayo eya kuhlanganiswa ngokusondeleyo kunye nezinye iimethrikhi zenkqubo (efunyenwe, umzekelo, kwiseva yesicelo, kwi-OS, njl.). Njengoko, umzekelo, oku kwenziwa kwi-pgwatch2, esebenzisa i-InfluxDB + iGrafana indibaniselwano kunye neqela lemibuzo kwiimbono zenkqubo, enokufikelelwa kwakhona. .
Iyonke
Kwaye olu luhlu kuphela oluqikelelweyo lwento enokwenziwa ngesiseko sedatha yethu kusetyenziswa ikhowudi yeSQL eqhelekileyo. Ndiqinisekile ukuba ungafumana ukusetyenziswa okuninzi, bhala kumagqabantshintshi. Kwaye siza kuthetha malunga nendlela (kwaye okona kubaluleke kakhulu ukuba kutheni) ukwenza ngokuzenzekelayo konke oku kwaye udibanise kumbhobho wakho weCI / CD kwixesha elizayo.
umthombo: www.habr.com
