SQL, menene zai iya zama mafi sauƙi? Kowannenmu zai iya rubuta buƙatu mai sauƙi - muna rubutawa zaži, jera ginshiƙan da ake buƙata, sannan daga, tebur sunan, wasu yanayi a inda kuma shi ke nan - bayanai masu amfani suna cikin aljihunmu, kuma (kusan) ko da wane irin DBMS ke ƙarƙashin hular a wancan lokacin (ko wataƙila. ). A sakamakon haka, yin aiki tare da kusan kowane tushen bayanai (dangantaka kuma ba haka ba) za a iya la'akari da shi daga ra'ayi na code na yau da kullun (tare da duk abin da yake nufi - sarrafa sigar, sake duba lambar, bincike mai mahimmanci, autotests, kuma shi ke nan). Kuma wannan ya shafi ba kawai ga data kanta, schemas da ƙaura, amma a general ga dukan rayuwa na ajiya. A cikin wannan labarin za mu yi magana game da ayyuka na yau da kullum da matsalolin aiki tare da bayanai daban-daban a ƙarƙashin ruwan tabarau na "database as code".
Kuma bari mu fara dama daga . Yaƙe-yaƙe na farko na nau'in "SQL vs ORM" an lura da baya a ciki .
Abu-dangantakar taswira
Magoya bayan ORM bisa ga al'ada suna darajar sauri da sauƙi na haɓakawa, 'yancin kai daga DBMS da lambar tsabta. Ga yawancin mu, lambar don aiki tare da bayanan bayanai (kuma sau da yawa database kanta)
yawanci yana kama da wani abu kamar wannan...
@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;
}
...An rataye samfurin tare da bayanai masu wayo, kuma wani wuri a bayan fage wani ƙwaƙƙwaran ORM ya haifar da aiwatar da ton na wasu lambar SQL. Af, masu haɓakawa suna ƙoƙarin keɓe kansu daga bayanansu tare da abubuwan ɓoye na kilomita, wanda ke nuna wasu. .
A gefe guda na shingen, masu bin tsantsar "na hannu" SQL suna lura da ikon matse duk ruwan 'ya'yan itace daga DBMS ɗin su ba tare da ƙarin yadudduka da abstractions ba. A sakamakon haka, ayyukan "data-centric" sun bayyana, inda mutane masu horarwa na musamman ke shiga cikin ma'ajin bayanai (suma "masu ƙididdiga", su ma "basicists", su ma "basdeners", da dai sauransu), da kuma masu haɓakawa. kawai dole ne a "jawo" abubuwan da aka shirya da kuma hanyoyin da aka adana, ba tare da shiga cikin cikakkun bayanai ba.
Idan muna da mafi kyawun duniyoyin biyu fa? Yadda ake yin wannan a cikin kayan aiki mai ban mamaki tare da suna mai tabbatar da rayuwa . Zan ba da layuka guda biyu daga ra'ayi na gaba ɗaya a cikin fassarar kyauta, kuma za ku iya sanin su daki-daki. .
Clojure harshe ne mai sanyi don ƙirƙirar DSLs, amma SQL kanta DSL mai sanyi ne, kuma ba ma buƙatar wani. S-bayanin suna da kyau, amma ba sa ƙara sabon abu a nan. A sakamakon haka, muna samun ƙwanƙwasa don kare kullun. Ban yarda ba? Sa'an nan kuma jira lokacin da abstraction a kan database ya fara yabo kuma ku fara fada da aikin (raw-sql)
To me zan yi? Bari mu bar SQL azaman SQL na yau da kullun - fayil ɗaya akan buƙata:
-- name: users-by-country
select *
from users
where country_code = :country_code... sannan karanta wannan fayil ɗin, juya shi zuwa aikin Clojure na yau da kullun:
(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" ...} ...)Ta hanyar bin ka'idar "SQL da kanta, Clojure da kanta", kuna samun:
- Babu abubuwan ban mamaki syntactic. Bayanan bayanan ku (kamar kowane) baya bin ka'idodin SQL 100% - amma wannan ba shi da mahimmanci ga Yesql. Ba za ku taɓa ɓata lokaci don farauta don ayyuka tare da daidaitaccen ma'anar SQL ba. Ba za ku taɓa komawa aiki ba (raw-sql "wasu ('funky':: SYNTAX)")).
- Mafi kyawun tallafin edita. Editan ku ya riga ya sami ingantaccen tallafin SQL. Ta hanyar adana SQL azaman SQL zaka iya amfani dashi kawai.
- Dacewar ƙungiyar. DBAs ɗin ku na iya karantawa da rubuta SQL ɗin da kuke amfani da su a cikin aikin ku na Clojure.
- Sauƙaƙan daidaita aikin. Kuna buƙatar gina tsari don tambaya mai matsala? Wannan ba matsala ba ne lokacin da tambayar ku ta zama SQL na yau da kullun.
- Sake amfani da tambayoyin. Jawo da sauke waɗannan fayilolin SQL iri ɗaya zuwa wasu ayyukan saboda kawai a sarari tsohon SQL - kawai raba shi.
A ganina, ra'ayin yana da kyau sosai kuma a lokaci guda mai sauƙi, godiya ga abin da aikin ya samu da yawa a cikin harsuna daban-daban. Kuma za mu yi ƙoƙari mu yi amfani da irin wannan falsafar na raba lambar SQL daga duk abin da ya wuce ORM.
IDE & DB manajoji
Bari mu fara da aiki na yau da kullun mai sauƙi. Sau da yawa dole ne mu nemo wasu abubuwa a cikin bayanan, misali, sami tebur a cikin tsarin kuma muyi nazarin tsarinsa (abin da ake amfani da ginshiƙai, maɓalli, fihirisa, ƙuntatawa, da dai sauransu). Kuma daga kowane IDE mai hoto ko ƙaramin manajan DB, da farko, muna tsammanin daidai waɗannan iyawar. Domin ya yi sauri kuma ba za ku jira rabin sa'a ba har sai an zana taga mai mahimman bayanai (musamman tare da jinkirin haɗi zuwa rumbun bayanai), kuma a lokaci guda, bayanan da aka karɓa sabo ne kuma masu dacewa. kuma ba cached takarce. Haka kuma, idan ya fi rikitarwa da girma da kuma yawan adadin su, yin hakan yana da wahala.
Amma yawanci na jefar da linzamin kwamfuta kuma in rubuta lamba kawai. Bari mu ce kana buƙatar gano ko wane tebur (da kuma waɗanne kaddarorin) ke ƙunshe a cikin tsarin "HR". A yawancin DBMSs, ana iya samun sakamakon da ake so tare da wannan tambaya mai sauƙi daga information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Daga bayanan bayanai zuwa bayanan bayanai, abubuwan da ke cikin irin waɗannan teburan tunani sun bambanta dangane da iyawar kowane DBMS. Kuma, alal misali, don MySQL, daga littafin tunani guda ɗaya zaka iya samun sigogi na tebur musamman ga wannan DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle bai san information_schema ba, amma yana da , kuma babu wata babbar matsala da ta taso:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse ba banda:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Ana iya yin wani abu makamancin haka a Cassandra (wanda ke da iyalai a maimakon teburi da wuraren maɓalli maimakon tsarawa):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Ga yawancin sauran bayanan bayanai, kuna iya zuwa da irin waɗannan tambayoyin (ko da Mongo yana da , wanda ya ƙunshi bayanai game da duk tarin da ke cikin tsarin).
Tabbas, ta wannan hanyar zaku iya samun bayanai ba kawai game da tebur ba, amma game da kowane abu gabaɗaya. Daga lokaci zuwa lokaci, mutane masu kirki suna raba irin wannan lambar don bayanan bayanai daban-daban, kamar, alal misali, a cikin jerin labaran habra "Ayyukan don tattara bayanan bayanan PostgreSQL" (, , ). Tabbas, ajiye wannan dutsen tambayoyin a cikin kaina da kuma buga su akai-akai abu ne mai daɗi, don haka a cikin IDE/edita da na fi so ina da snippets da aka riga aka shirya don tambayoyin da ake yawan amfani da su, kuma abin da ya rage shi ne buga faifan. abu sunaye a cikin samfuri.
A sakamakon haka, wannan hanyar kewayawa da neman abubuwa ya fi sauƙi, yana adana lokaci mai yawa, kuma yana ba ku damar samun ainihin bayanin a cikin hanyar da ya zama dole (kamar yadda, alal misali, aka bayyana a cikin post). ).
Ayyuka tare da abubuwa
Bayan mun samo da kuma nazarin abubuwan da ake bukata, lokaci ya yi da za mu yi wani abu mai amfani tare da su. A zahiri, kuma ba tare da cire yatsun ku daga madannai ba.
Ba asiri ba ne cewa kawai share tebur zai yi kama da shi a kusan dukkanin bayanan bayanai:
drop table hr.personsAmma tare da ƙirƙirar tebur ya zama mafi ban sha'awa. Kusan kowane DBMS (ciki har da NoSQL da yawa) na iya "ƙirƙirar tebur" a cikin nau'i ɗaya ko wani, kuma babban ɓangarensa zai ɗan bambanta kaɗan (suna, jerin ginshiƙai, nau'ikan bayanai), amma sauran cikakkun bayanai na iya bambanta sosai kuma sun dogara da na'urar ciki da iyawar takamaiman DBMS. Misali na fi so shine a cikin takaddun Oracle akwai "tsirara" BNFs don "ƙirƙirar tebur" . Sauran DBMSs suna da mafi girman iyawa, amma kowannensu kuma yana da fasali masu ban sha'awa da na musamman don ƙirƙirar tebur (, , , ). Yana da wuya cewa kowane "mayen" mai hoto daga wani IDE (musamman na duniya) zai iya cika dukkanin waɗannan iyawar, kuma ko da zai iya, ba zai zama abin kallo ga masu raunin zuciya ba. A lokaci guda kuma, daidaitaccen bayanin da aka rubuta ƙirƙirar tebur zai ba ku damar amfani da su cikin sauƙi, sanya ajiya da samun damar yin amfani da bayanan ku abin dogaro, mafi kyau da kwanciyar hankali kamar yadda zai yiwu.
Har ila yau, yawancin DBMSs suna da nasu takamaiman nau'ikan abubuwa waɗanda babu su a cikin wasu DBMSs. Bugu da ƙari, za mu iya yin ayyuka ba kawai akan abubuwan bayanai ba, har ma a kan DBMS kanta, alal misali, "kashe" tsari, 'yantar da wasu wuraren ƙwaƙwalwar ajiya, ba da damar ganowa, canzawa zuwa yanayin "karanta kawai", da ƙari mai yawa.
Yanzu bari mu zana kadan
Ɗaya daga cikin ayyuka na yau da kullum shine gina zane tare da abubuwan bayanai da kuma ganin abubuwa da haɗin da ke tsakanin su a cikin kyakkyawan hoto. Kusan kowane IDE mai hoto, keɓancewar “layin umarni”, kayan aikin zane na musamman da masu ƙira na iya yin wannan. Za su zana muku wani abu "kamar yadda za su iya," kuma za ku iya rinjayar wannan tsari kadan kawai tare da taimakon wasu sigogi a cikin fayil ɗin sanyi ko akwatunan rajista a cikin dubawa.
Amma wannan matsala za a iya warware mafi sauki, mafi m da m, kuma ba shakka tare da taimakon code. Don ƙirƙirar zane na kowane hadaddun, muna da yaruka na musamman na musamman (DOT, GraphML da sauransu), kuma a gare su duka aikace-aikacen watsawa (GraphViz, PlantUML, Mermaid) waɗanda za su iya karanta irin waɗannan umarnin kuma su hango su a cikin tsari iri-iri. . To, mun riga mun san yadda ake samun bayanai game da abubuwa da alaƙa tsakanin su.
Anan ga ƙaramin misalin yadda wannan zai iya kama, ta amfani da PlantUML da (a hagu akwai tambayar SQL wacce zata samar da umarnin da ake buƙata don PlantUML, kuma a dama shine sakamakon):
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'Kuma idan kun gwada kadan, to bisa ga Kuna iya samun wani abu mai kama da ainihin zane na ER:
Tambayar SQL ta ɗan fi rikitarwa
-- Шапка
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'
Idan ka duba da kyau, a ƙarƙashin hular, kayan aikin gani da yawa kuma suna amfani da tambayoyi iri ɗaya. Gaskiya ne, waɗannan buƙatun yawanci suna da zurfi , ba tare da ambaton wani gyare-gyaren su ba.
Ma'auni da saka idanu
Bari mu matsa zuwa wani maudu'i mai sarkakiya na al'ada - database performance monitoring. Na tuna wani ɗan ƙaramin labari na gaskiya wanda “ɗayan abokaina” ya ba ni. A kan wani aikin akwai wani DBA mai ƙarfi, kuma kaɗan daga cikin masu haɓakawa sun san shi da kansa, ko kuma sun taɓa ganinsa a cikin mutum (duk da cewa, a cewar jita-jita, ya yi aiki a wani wuri a cikin ginin na gaba). A sa'a "X", lokacin da tsarin poduction na babban dillali ya fara "ji dadi" sake, ya yi shiru ya aika da hotunan hotuna daga Manajan Kasuwancin Oracle, wanda a hankali ya ba da alama a wurare masu mahimmanci tare da alamar ja don "fahimta" ( wannan, don sanya shi a hankali, bai taimaka sosai ba). Kuma bisa wannan "katin hoto" dole ne in yi magani. A lokaci guda, babu wanda ya sami dama ga mai daraja (a cikin ma'anar kalmar) Manager Enterprise, saboda tsarin yana da rikitarwa kuma yana da tsada, ba zato ba tsammani "masu haɓakawa sun yi tuntuɓe a kan wani abu kuma sun karya komai." Saboda haka, masu haɓakawa "a zahiri" sun sami wuri da dalilin birki kuma sun saki faci. Idan har wasiƙar barazana daga DBA ba ta sake zuwa nan gaba kaɗan ba, to kowa zai yi numfashin annashuwa kuma ya koma kan ayyukan da suke yi a yanzu (har zuwa sabon Wasika).
Amma tsarin sa ido zai iya zama mafi ban sha'awa da abokantaka, kuma mafi mahimmanci, samun dama da bayyane ga kowa da kowa. Aƙalla ɓangaren sa na asali, azaman ƙari ga babban tsarin sa ido (waɗanda tabbas suna da amfani kuma a yawancin lokuta ba za a iya maye gurbinsu ba). Duk wani DBMS kyauta ne kuma cikakken kyauta don raba bayanai game da halin da ake ciki da aikin sa na yanzu. A cikin Oracle DB guda "jini", kusan duk wani bayani game da aiki ana iya samun shi daga ra'ayoyin tsarin, kama daga matakai da zaman zuwa yanayin cache na buffer (misali, , sashe "Monitoring"). Postgresql kuma yana da ɗimbin ra'ayoyin tsarin don , musamman waɗanda ba makawa a cikin rayuwar yau da kullun na kowane DBA, kamar , , . MySQL har ma yana da tsari daban don wannan. . A cikin Mongo ginannen yana tattara bayanan aiki cikin tarin tsarin .
Don haka, dauke da wani nau'i na masu tara ma'auni (Telegraf, Metricbeat, Tattara) wanda zai iya yin tambayoyin sql na al'ada, ajiyar waɗannan ma'auni (InfluxDB, Elasticsearch, Timescaledb) da mai gani (Grafana, Kibana), zaku iya samun sauƙi mai sauƙi. da tsarin sa ido mai sassauƙa wanda za a haɗa shi tare da sauran ma'auni mai faɗin tsarin (samuwa, misali, daga uwar garken aikace-aikacen, daga OS, da sauransu). Misali, ana yin wannan a cikin pgwatch2, wanda ke amfani da haɗin InfluxDB + Grafana da saitin tambayoyin zuwa ra'ayoyin tsarin, wanda kuma za'a iya samun dama ga .
Jimlar
Kuma wannan shine kawai lissafin abin da za a iya yi tare da bayanan mu ta amfani da lambar SQL na yau da kullum. Na tabbata za ku iya samun ƙarin amfani, rubuta a cikin sharhi. Kuma za mu yi magana game da yadda (kuma mafi mahimmanci dalilin da ya sa) za a sarrafa duk wannan kuma mu haɗa shi a cikin bututun CI / CD na gaba lokaci na gaba.
source: www.habr.com