SQL, ano ang maaaring maging mas simple? Ang bawat isa sa atin ay maaaring magsulat ng isang simpleng kahilingan - nagta-type tayo piliin, ilista ang mga kinakailangang column, pagkatapos mula, pangalan ng talahanayan, ilang kundisyon sa saan at iyon lang - ang kapaki-pakinabang na data ay nasa aming bulsa, at (halos) anuman ang DBMS ay nasa ilalim ng hood sa oras na iyon (o marahil ). Bilang resulta, ang pagtatrabaho sa halos anumang mapagkukunan ng data (relational at hindi ganoon) ay maaaring isaalang-alang mula sa punto ng view ng ordinaryong code (kasama ang lahat ng ipinahihiwatig nito - kontrol sa bersyon, pagsusuri ng code, static na pagsusuri, mga autotest, at iyon lang). At nalalapat ito hindi lamang sa data mismo, mga schema at paglilipat, ngunit sa pangkalahatan sa buong buhay ng imbakan. Sa artikulong ito ay pag-uusapan natin ang tungkol sa mga pang-araw-araw na gawain at mga problema sa pagtatrabaho sa iba't ibang mga database sa ilalim ng lente ng "database bilang code".
At magsimula tayo mula mismo sa . Ang mga unang laban ng uri ng "SQL vs ORM" ay napansin muli .
Object-relational na pagmamapa
Tradisyonal na pinahahalagahan ng mga tagasuporta ng ORM ang bilis at kadalian ng pag-unlad, kalayaan mula sa DBMS at malinis na code. Para sa marami sa atin, ang code para sa pagtatrabaho sa database (at madalas ang database mismo)
kadalasan ganito ang itsura...
@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;
}
...Ang modelo ay nakabitin na may matalinong mga anotasyon, at sa isang lugar sa likod ng mga eksena ay isang magiting na ORM ang bumubuo at nagpapatupad ng ilang toneladang SQL code. Sa pamamagitan ng paraan, sinusubukan ng mga developer ang kanilang makakaya upang ihiwalay ang kanilang sarili mula sa kanilang database gamit ang mga kilometro ng abstraction, na nagpapahiwatig ng ilang .
Sa kabilang panig ng mga barikada, ang mga tagasunod ng dalisay na "gawa ng kamay" na SQL ay nagpapansin ng kakayahang i-squeeze ang lahat ng juice sa kanilang DBMS nang walang karagdagang mga layer at abstraction. Bilang resulta, lumilitaw ang mga "data-centric" na proyekto, kung saan ang mga espesyal na sinanay na tao ay kasangkot sa database (sila rin ay "basicists", sila rin ay "basicists", sila rin ay "basdeners", atbp.), at ang mga developer kailangan lamang na "hilahin" ang mga yari na tingnan at nakaimbak na mga pamamaraan, nang hindi naglalagay ng mga detalye.
Paano kung mayroon tayong pinakamahusay sa parehong mundo? Paano ito ginagawa sa isang kahanga-hangang tool na may pangalang nagpapatibay sa buhay . Magbibigay ako ng ilang linya mula sa pangkalahatang konsepto sa aking libreng pagsasalin, at maaari mo itong makilala nang mas detalyado .
Ang Clojure ay isang cool na wika para sa paglikha ng mga DSL, ngunit ang SQL mismo ay isang cool na DSL, at hindi na namin kailangan ng isa pa. Ang mga S-expression ay mahusay, ngunit hindi sila nagdaragdag ng anumang bago dito. Bilang resulta, nakakakuha kami ng mga bracket para sa kapakanan ng mga bracket. hindi sumasang-ayon? Pagkatapos ay maghintay para sa sandali kapag ang abstraction sa database ay nagsimulang tumagas at nagsimula kang makipaglaban sa function (raw-sql)
Kaya ano ang dapat kong gawin? Iwanan natin ang SQL bilang regular na SQL - isang file bawat kahilingan:
-- name: users-by-country
select *
from users
where country_code = :country_code... at pagkatapos ay basahin ang file na ito, ginagawa itong isang regular na Clojure function:
(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" ...} ...)Sa pamamagitan ng pagsunod sa prinsipyong "SQL by itself, Clojure by itself", makakakuha ka ng:
- Walang syntactic surprises. Ang iyong database (tulad ng iba pa) ay hindi 100% na sumusunod sa pamantayan ng SQL - ngunit hindi ito mahalaga para sa Yesql. Hinding-hindi ka mag-aaksaya ng oras sa paghahanap ng mga function na may katumbas na syntax ng SQL. Hindi mo na kailangang bumalik sa isang function (raw-sql "some('funky'::SYNTAX)")).
- Pinakamahusay na suporta sa editor. Ang iyong editor ay mayroon nang mahusay na suporta sa SQL. Sa pamamagitan ng pag-save ng SQL bilang SQL maaari mo lamang itong gamitin.
- Pagkakatugma ng koponan. Maaaring basahin at isulat ng iyong mga DBA ang SQL na ginagamit mo sa iyong proyekto sa Clojure.
- Mas madaling pag-tune ng performance. Kailangang bumuo ng isang plano para sa isang may problemang query? Hindi ito problema kapag ang iyong query ay regular na SQL.
- Muling paggamit ng mga query. I-drag at i-drop ang parehong mga SQL file sa iba pang mga proyekto dahil ito ay simpleng lumang SQL - ibahagi lamang ito.
Sa palagay ko, ang ideya ay napaka-cool at sa parehong oras ay napaka-simple, salamat sa kung saan ang proyekto ay nakakuha ng marami sa iba't ibang wika. At susubukan naming ilapat ang isang katulad na pilosopiya ng paghihiwalay ng SQL code mula sa lahat ng iba pang malayo sa ORM.
Mga tagapamahala ng IDE at DB
Magsimula tayo sa isang simpleng gawain araw-araw. Kadalasan kailangan nating maghanap ng ilang mga bagay sa database, halimbawa, maghanap ng isang talahanayan sa schema at pag-aralan ang istraktura nito (kung anong mga column, key, index, constraints, atbp. ang ginagamit). At mula sa anumang graphical na IDE o isang maliit na DB-manager, una sa lahat, inaasahan namin ang eksaktong mga kakayahan na ito. Upang ito ay mabilis at hindi mo na kailangang maghintay ng kalahating oras hanggang sa isang window na may kinakailangang impormasyon ay iguguhit (lalo na sa isang mabagal na koneksyon sa isang malayong database), at sa parehong oras, ang impormasyon na natanggap ay sariwa at may kaugnayan, at hindi naka-cache na basura. Bukod dito, mas kumplikado at mas malaki ang database at mas malaki ang bilang ng mga ito, mas mahirap gawin ito.
Ngunit kadalasan ay itinatapon ko ang mouse at nagsusulat lamang ng code. Sabihin nating kailangan mong malaman kung aling mga talahanayan (at kung aling mga katangian) ang nakapaloob sa schema ng "HR". Sa karamihan ng mga DBMS, ang nais na resulta ay maaaring makamit gamit ang simpleng query na ito mula sa information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Mula sa database hanggang sa database, ang mga nilalaman ng naturang reference table ay nag-iiba depende sa mga kakayahan ng bawat DBMS. At, halimbawa, para sa MySQL, mula sa parehong reference na libro maaari kang makakuha ng mga parameter ng talahanayan na tiyak sa DBMS na ito:
select table_name
, storage_engine -- ΠΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌΡΠΉ "Π΄Π²ΠΈΠΆΠΎΠΊ" ("MyISAM", "InnoDB" etc)
, row_format -- Π€ΠΎΡΠΌΠ°Ρ ΡΡΡΠΎΠΊΠΈ ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Hindi alam ng Oracle ang information_schema, ngunit mayroon ito , at walang malalaking problema na lumitaw:
select table_name
, pct_free -- ΠΠΈΠ½ΠΈΠΌΡΠΌ ΡΠ²ΠΎΠ±ΠΎΠ΄Π½ΠΎΠ³ΠΎ ΠΌΠ΅ΡΡΠ° Π² Π±Π»ΠΎΠΊΠ΅ Π΄Π°Π½Π½ΡΡ
(%)
, pct_used -- ΠΠΈΠ½ΠΈΠΌΡΠΌ ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌΠΎΠ³ΠΎ ΠΌΠ΅ΡΡΠ° Π² Π±Π»ΠΎΠΊΠ΅ Π΄Π°Π½Π½ΡΡ
(%)
, last_analyzed -- ΠΠ°ΡΠ° ΠΏΠΎΡΠ»Π΅Π΄Π½Π΅Π³ΠΎ ΡΠ±ΠΎΡΠ° ΡΡΠ°ΡΠΈΡΡΠΈΠΊΠΈ
, ...
from all_tables
where owner = 'HR'Ang ClickHouse ay walang pagbubukod:
select name
, engine -- ΠΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌΡΠΉ "Π΄Π²ΠΈΠΆΠΎΠΊ" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Ang isang bagay na katulad ay maaaring gawin sa Cassandra (na mayroong mga columnfamilies sa halip na mga talahanayan at keyspace sa halip na mga schema):
select columnfamily_name
, compaction_strategy_class -- Π‘ΡΡΠ°ΡΠ΅Π³ΠΈΡ ΡΠ±ΠΎΡΠΊΠΈ ΠΌΡΡΠΎΡΠ°
, gc_grace_seconds -- ΠΡΠ΅ΠΌΡ ΠΆΠΈΠ·Π½ΠΈ ΠΌΡΡΠΎΡΠ°
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Para sa karamihan ng iba pang mga database, maaari ka ring magkaroon ng mga katulad na query (kahit na ang Mongo ay may , na naglalaman ng impormasyon tungkol sa lahat ng mga koleksyon sa system).
Siyempre, sa ganitong paraan makakakuha ka ng impormasyon hindi lamang tungkol sa mga talahanayan, ngunit tungkol sa anumang bagay sa pangkalahatan. Paminsan-minsan, ang mga mababait na tao ay nagbabahagi ng naturang code para sa iba't ibang mga database, tulad ng, halimbawa, sa serye ng mga artikulo ng habra na "Mga Function para sa pagdodokumento ng mga database ng PostgreSQL" (, , ). Siyempre, ang pag-iingat sa buong bundok ng mga query sa aking isipan at patuloy na pag-type ng mga ito ay isang kasiyahan, kaya sa aking paboritong IDE/editor mayroon akong isang paunang inihanda na hanay ng mga snippet para sa mga madalas na ginagamit na mga query, at ang natitira lamang ay ang pag-type ng mga pangalan ng bagay sa template.
Bilang isang resulta, ang pamamaraang ito ng pag-navigate at paghahanap ng mga bagay ay mas nababaluktot, nakakatipid ng maraming oras, at nagbibigay-daan sa iyo upang makakuha ng eksaktong impormasyon sa form kung saan ito ay kinakailangan (tulad ng, halimbawa, na inilarawan sa post. ).
Mga operasyon sa mga bagay
Matapos nating mahanap at mapag-aralan ang mga kinakailangang bagay, oras na para gumawa ng isang bagay na kapaki-pakinabang sa kanila. Naturally, hindi rin inaalis ang iyong mga daliri sa keyboard.
Hindi lihim na ang simpleng pagtanggal ng isang talahanayan ay magiging pareho sa halos lahat ng mga database:
drop table hr.personsNgunit sa paglikha ng talahanayan ito ay nagiging mas kawili-wili. Halos anumang DBMS (kabilang ang maraming NoSQL) ay maaaring "lumikha ng talahanayan" sa isang anyo o iba pa, at ang pangunahing bahagi nito ay bahagyang mag-iiba (pangalan, listahan ng mga hanay, mga uri ng data), ngunit ang ibang mga detalye ay maaaring mag-iba nang malaki at depende sa panloob na aparato at mga kakayahan ng isang partikular na DBMS. Ang aking paboritong halimbawa ay na sa dokumentasyon ng Oracle mayroon lamang mga "hubad" na BNF para sa syntax na "lumikha ng talahanayan" . Ang iba pang mga DBMS ay may mas katamtamang mga kakayahan, ngunit ang bawat isa sa kanila ay mayroon ding maraming kawili-wili at natatanging mga tampok para sa paglikha ng mga talahanayan (, , , ). Hindi malamang na ang anumang graphical na "wizard" mula sa isa pang IDE (lalo na ang isang unibersal) ay maaaring ganap na masakop ang lahat ng mga kakayahan na ito, at kahit na magagawa nito, hindi ito magiging isang panoorin para sa mahina ang puso. Kasabay nito, isang tama at napapanahong nakasulat na pahayag lumikha ng talahanayan ay magbibigay-daan sa iyong madaling gamitin ang lahat ng mga ito, gawing maaasahan, pinakamainam at kumportable hangga't maaari ang pag-iimbak at pag-access sa iyong data.
Gayundin, maraming mga DBMS ang may sariling mga partikular na uri ng mga bagay na hindi available sa ibang mga DBMS. Bukod dito, maaari kaming magsagawa ng mga operasyon hindi lamang sa mga object ng database, kundi pati na rin sa DBMS mismo, halimbawa, "pumatay" ng isang proseso, palayain ang ilang lugar ng memorya, paganahin ang pagsubaybay, lumipat sa mode na "read only", at marami pa.
Ngayon gumuhit tayo ng kaunti
Ang isa sa mga pinaka-karaniwang gawain ay ang bumuo ng isang diagram na may mga bagay sa database at makita ang mga bagay at koneksyon sa pagitan ng mga ito sa isang magandang larawan. Halos anumang graphical na IDE, hiwalay na "command line" na mga utility, mga espesyal na graphical na tool at mga modelo ay maaaring gawin ito. Gumuhit sila ng isang bagay para sa iyo "sa abot ng kanilang makakaya," at maaari mong maimpluwensyahan ang prosesong ito nang kaunti lamang sa tulong ng ilang mga parameter sa file ng pagsasaayos o mga checkbox sa interface.
Ngunit ang problemang ito ay maaaring malutas nang mas simple, mas nababaluktot at eleganteng, at siyempre sa tulong ng code. Upang lumikha ng mga diagram ng anumang kumplikado, mayroon kaming ilang mga espesyal na wika ng markup (DOT, GraphML atbp), at para sa kanila ang isang buong pagkakalat ng mga application (GraphViz, PlantUML, Mermaid) na maaaring basahin ang mga naturang tagubilin at mailarawan ang mga ito sa iba't ibang mga format . Well, alam na natin kung paano makakuha ng impormasyon tungkol sa mga bagay at koneksyon sa pagitan nila.
Narito ang isang maliit na halimbawa ng maaaring hitsura nito, gamit ang PlantUML at (sa kaliwa ay isang SQL query na bubuo ng kinakailangang pagtuturo para sa PlantUML, at sa kanan ay ang resulta):
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'At kung susubukan mo ng kaunti, pagkatapos ay batay sa maaari kang makakuha ng isang bagay na halos kapareho sa isang tunay na diagram ng ER:
Ang SQL query ay medyo mas kumplikado
-- Π¨Π°ΠΏΠΊΠ°
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'
Kung titingnan mong mabuti, sa ilalim ng hood maraming mga visualization tool ay gumagamit din ng mga katulad na query. Totoo, ang mga kahilingang ito ay karaniwang malalim , hindi banggitin ang anumang pagbabago sa mga ito.
Mga sukatan at pagsubaybay
Lumipat tayo sa isang tradisyonal na kumplikadong paksa - pagsubaybay sa pagganap ng database. Naaalala ko ang isang maliit na totoong kuwento na sinabi sa akin ng "isa sa aking mga kaibigan." Sa isa pang proyekto ay may nakatirang isang malakas na DBA, at kakaunti sa mga developer ang personal na nakakilala sa kanya, o nakita na siya nang personal (sa kabila ng katotohanan na, ayon sa mga alingawngaw, nagtrabaho siya sa isang lugar sa susunod na gusali) . Sa oras na "X", kapag ang sistema ng poduction ng isang malaking retailer ay nagsimulang muling "masama ang pakiramdam", tahimik siyang nagpadala ng mga screenshot ng mga graph mula sa Oracle Enterprise Manager, kung saan maingat niyang itinampok ang mga kritikal na lugar na may pulang marker para sa "comprehensibility" ( ito, upang ilagay ito nang mahinahon, ay hindi nakatulong nang malaki). At batay sa "photo card" na ito kailangan kong gamutin. Kasabay nito, walang sinuman ang may access sa mahalagang (sa parehong kahulugan ng salita) Enterprise Manager, dahil ang sistema ay kumplikado at mahal, biglang "ang mga developer ay natitisod sa isang bagay at sinisira ang lahat." Samakatuwid, "empirically" natagpuan ng mga developer ang lokasyon at sanhi ng mga preno at naglabas ng isang patch. Kung ang nagbabantang liham mula sa DBA ay hindi na dumating muli sa malapit na hinaharap, ang lahat ay makahinga ng maluwag at babalik sa kanilang kasalukuyang mga gawain (hanggang sa bagong Liham).
Ngunit ang proseso ng pagsubaybay ay maaaring magmukhang mas masaya at palakaibigan, at higit sa lahat, naa-access at transparent para sa lahat. Hindi bababa sa pangunahing bahagi nito, bilang karagdagan sa mga pangunahing sistema ng pagsubaybay (na tiyak na kapaki-pakinabang at sa maraming mga kaso ay hindi maaaring palitan). Anumang DBMS ay malaya at ganap na walang bayad upang magbahagi ng impormasyon tungkol sa kasalukuyang estado at pagganap nito. Sa parehong "madugong" Oracle DB, halos anumang impormasyon tungkol sa pagganap ay maaaring makuha mula sa mga view ng system, mula sa mga proseso at session hanggang sa estado ng buffer cache (halimbawa, , seksyong "Pagsubaybay"). Ang Postgresql ay mayroon ding isang buong grupo ng mga view ng system para sa , partikular ang mga kailangang-kailangan sa pang-araw-araw na buhay ng anumang DBA, gaya ng , , . Ang MySQL ay mayroon ding hiwalay na schema para dito. . A In Mongo built-in pinagsasama-sama ang data ng pagganap sa isang koleksyon ng system .
Kaya, armado ng ilang uri ng metrics collector (Telegraf, Metricbeat, Collectd) na maaaring magsagawa ng mga custom na sql query, isang storage ng mga sukatang ito (InfluxDB, Elasticsearch, Timescaledb) at isang visualizer (Grafana, Kibana), maaari kang makakuha ng medyo madali. at isang flexible na sistema ng pagsubaybay na malapit na isasama sa iba pang sukatan sa buong system (nakukuha, halimbawa, mula sa server ng application, mula sa OS, atbp.). Bilang, halimbawa, ginagawa ito sa pgwatch2, na gumagamit ng kumbinasyon ng InfluxDB + Grafana at isang hanay ng mga query sa mga view ng system, na maaari ding ma-access .
Sa kabuuan
At ito ay isang tinatayang listahan lamang ng kung ano ang maaaring gawin sa aming database gamit ang regular na SQL code. Natitiyak kong marami ka pang magagamit, sumulat sa mga komento. At pag-uusapan natin kung paano (at pinakamahalaga kung bakit) i-automate ang lahat ng ito at isama ito sa iyong pipeline ng CI/CD sa susunod.
Pinagmulan: www.habr.com