🥇Kukumana ndi "Database as Code" | Pulogalamu ya ProHoster

SQL, chomwe chingakhale chophweka? Aliyense wa ife akhoza kulemba pempho losavuta - timalemba sankhani, tchulani zigawo zofunika, ndiye kuchokera, dzina la tebulo, zinthu zina mkati kumene ndipo ndizo zonse - deta yothandiza ili m'thumba mwathu, ndipo (pafupifupi) mosasamala kanthu kuti DBMS ili pansi pa hood panthawiyo (kapena mwinamwake osati DBMS konse). Zotsatira zake, kugwira ntchito ndi pafupifupi gwero lililonse lachidziwitso (zachiyanjano osati choncho) zitha kuganiziridwa potengera ma code wamba (ndi zonse zomwe zikutanthawuza - kuwongolera mtundu, kuwunikiranso kachidindo, kusanthula kokhazikika, ma autotests, ndizo zonse). Ndipo izi sizikugwira ntchito kokha ku deta yokha, schemas ndi kusamuka, koma kawirikawiri ku moyo wonse wa yosungirako. M'nkhaniyi tikambirana za ntchito za tsiku ndi tsiku ndi mavuto ogwira ntchito ndi ma database osiyanasiyana pansi pa lens la "database as code".

Ndipo tiyeni tiyambire pomwe ORM. Nkhondo zoyamba za mtundu wa "SQL vs ORM" zidadziwikanso Pre-Petrine Rus.

Mapu okhudzana ndi chinthu

Othandizira ORM mwamwambo amayamikira kuthamanga ndi kumasuka kwa chitukuko, kudziyimira pawokha ku DBMS ndi code yoyera. Kwa ambiri aife, code yogwirira ntchito ndi nkhokwe (ndipo nthawi zambiri nkhokweyo)

nthawi zambiri zimawoneka ngati izi ...

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

Mtunduwu umapachikidwa ndi zofotokozera zanzeru, ndipo kwinakwake kumbuyo kwazithunzi ORM yamphamvu imapanga ndikutulutsa matani a SQL code. Mwa njira, Madivelopa akuyesetsa kuti adzilekanitse okha kuchokera ku database yawo ndi ma kilomita azinthu, zomwe zikuwonetsa zina. "SQL chidani".

Kumbali ina ya zotchinga, otsatira SQL yoyera "yopangidwa ndi manja" amazindikira kuthekera kofinya madzi onse mu DBMS yawo popanda zigawo zowonjezera ndi zotsalira. Chotsatira chake, mapulojekiti a "data-centric" akuwonekera, kumene anthu ophunzitsidwa mwapadera akuphatikizidwa mu database (iwo ndi "basicists", amakhalanso "basicist", amakhalanso "basdeners", etc.), ndi omangamanga kungoyenera "kukoka" mawonedwe okonzeka okonzeka ndi njira zosungidwa, osapita mwatsatanetsatane.

Bwanji tikanakhala ndi zabwino koposa zonse? Momwe izi zimachitikira mu chida chodabwitsa chokhala ndi dzina lotsimikizira moyo Yesql. Ndipereka mizere ingapo kuchokera ku lingaliro wamba pakumasulira kwanga kwaulere, ndipo mutha kudziwa bwino mwatsatanetsatane apa.

Clojure ndi chilankhulo chabwino chopangira ma DSL, koma SQL palokha ndi DSL yabwino, ndipo sitifuna ina. Mawu a S ndi abwino, koma samawonjezera china chatsopano pano. Zotsatira zake, timapeza mabulaketi chifukwa cha mabakiteriya. Simukuvomereza? Kenako dikirani nthawi yomwe kuchotsedwa kwa database kumayamba kutsika ndikuyamba kulimbana ndi ntchitoyi (yaiwisi-sql)

Ndiye nditani? Tiyeni tisiye SQL ngati SQL wamba - fayilo imodzi pa pempho lililonse:

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

... ndiyeno werengani fayiloyi, ndikuisintha kukhala ntchito yanthawi zonse ya 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" ...} ...)

Potsatira mfundo ya "SQL yokha, Clojure palokha", mumapeza:

Palibe zodabwitsa. Nawonsonkhokwe yanu (monga ina iliyonse) sikugwirizana ndi SQL muyezo wa 100% - koma izi zilibe kanthu kwa Yesql. Simudzawononga nthawi kusaka ntchito ndi mawu ofanana ndi SQL. Simudzasowa kubwerera kuntchito (raw-sql "ena('funky'::SYNTAX)")).

Thandizo labwino kwambiri la mkonzi. Mkonzi wanu ali ndi chithandizo chabwino kwambiri cha SQL. Mwa kusunga SQL ngati SQL mutha kuyigwiritsa ntchito.

Kugwirizana kwamagulu. Ma DBA anu amatha kuwerenga ndi kulemba SQL yomwe mumagwiritsa ntchito mu polojekiti yanu ya Clojure.

Kusintha kosavuta kwa magwiridwe antchito. Mukufuna kupanga dongosolo la funso lomwe lavuta? Ili si vuto ngati funso lanu ndi SQL wamba.

Kugwiritsanso ntchito mafunso. Kokani ndikuponya mafayilo omwewo a SQL muzinthu zina chifukwa ndi SQL yakale - ingogawanani.

Malingaliro anga, lingalirolo ndi lozizira kwambiri komanso panthawi imodzimodziyo losavuta, chifukwa cha zomwe polojekitiyi yapeza ambiri otsatira m'zinenero zosiyanasiyana. Ndipo tidzayesanso kugwiritsa ntchito nzeru yofananira yolekanitsa kachidindo ka SQL ndi china chilichonse kupitilira ORM.

Oyang'anira IDE & DB

Tiyeni tiyambe ndi ntchito yosavuta ya tsiku ndi tsiku. Nthawi zambiri timafunika kufufuza zinthu zina mu database, mwachitsanzo, kupeza tebulo mu schema ndikuphunzira momwe mizati, makiyi, indexes, zopinga, etc. ntchito. Ndipo kuchokera ku IDE iliyonse yojambula kapena woyang'anira DB pang'ono, choyamba, tikuyembekeza ndendende maluso awa. Kotero kuti ndizofulumira ndipo simukuyenera kudikirira theka la ola mpaka zenera lomwe lili ndi chidziwitso chofunikira likukokedwa (makamaka ndi kugwirizana kwapang'onopang'ono ku database yakutali), ndipo panthawi imodzimodziyo, chidziwitso cholandiridwa ndi chatsopano komanso chofunikira, osati zonyansa zosungidwa. Kuphatikiza apo, zovuta komanso zazikulu za database komanso kuchuluka kwake, zimakhala zovuta kwambiri kuchita izi.

Koma nthawi zambiri ndimataya mbewa ndikungolemba khodi. Tiyerekeze kuti muyenera kudziwa kuti ndi matebulo ati (ndi zinthu ziti) zomwe zili mu "HR" schema. Mu ma DBMS ambiri, zotsatira zomwe mukufuna zitha kupezedwa ndi funso losavuta ili kuchokera ku information_schema:

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

Kuchokera ku database kupita ku database, zomwe zili m'matebulo amawu amasiyana malinga ndi kuthekera kwa DBMS iliyonse. Ndipo, mwachitsanzo, kwa MySQL, kuchokera ku bukhu lomwelo lolozera mutha kupeza magawo a tebulo okhudzana ndi DBMS iyi:

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

Oracle sadziwa zambiri_schema, koma akudziwa Oracle metadata, ndipo palibe mavuto aakulu omwe amabwera:

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

ClickHouse ndizosiyana:

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

Chinanso chofanana chitha kuchitika ku Cassandra (yomwe ili ndi mabanja m'malo mwa matebulo ndi makiyi m'malo mwa schemas):

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

Pazinthu zina zambiri, mutha kubweranso ndi mafunso ofanana (ngakhale Mongo ali nawo wapadera dongosolo kusonkhanitsa, yomwe ili ndi chidziwitso chokhudza zosonkhanitsidwa zonse mudongosolo).

Zoonadi, mwanjira imeneyi mungapeze zambiri osati za matebulo, komanso za chinthu chilichonse. Nthawi ndi nthawi, anthu okoma mtima amagawana ma code awa pama database osiyanasiyana, monga, mwachitsanzo, mndandanda wa zolemba za habra "Ntchito zolembera nkhokwe za PostgreSQL" (Ayib, Ben, Kolimbitsira Thupi). Zachidziwikire, kusunga mafunso onsewa m'mutu mwanga ndikuwalemba mosalekeza ndikosangalatsa, kotero mu IDE/mkonzi wanga yemwe ndimakonda ndili ndi zidule zokonzekereratu zamafunso omwe amagwiritsidwa ntchito pafupipafupi, ndipo chomwe chatsala ndikulemba mayina azinthu mu template.

Zotsatira zake, njira iyi yoyendetsera ndikufufuza zinthu ndi yosinthika kwambiri, imapulumutsa nthawi yambiri, ndipo imakupatsani mwayi kuti mupeze chidziwitso chomwe chili chofunikira (monga, mwachitsanzo, chofotokozedwa positi. "Kutumiza deta kuchokera ku database mumtundu uliwonse: zomwe ma IDE angachite pa nsanja ya IntelliJ").

Zochita ndi zinthu

Titapeza ndikuphunzira zinthu zofunika, ndi nthawi yoti tichite nawo zinthu zothandiza. Mwachibadwa, komanso popanda kuchotsa zala zanu pa kiyibodi.

Si chinsinsi kuti kungochotsa tebulo kudzawoneka chimodzimodzi pafupifupi ma database onse:

drop table hr.persons

Koma ndi kulengedwa kwa tebulo kumakhala kosangalatsa kwambiri. Pafupifupi DBMS iliyonse (kuphatikiza NoSQL yambiri) imatha "kupanga tebulo" mwanjira ina, ndipo gawo lalikulu la izo lidzakhala losiyana pang'ono (dzina, mndandanda wa mizati, mitundu ya deta), koma zina zimatha kusiyana kwambiri ndikudalira chipangizo chamkati ndi kuthekera kwa DBMS inayake. Chitsanzo changa chomwe ndimakonda ndichakuti muzolemba za Oracle muli ma BNF "amaliseche" okha a "create table" syntax. ili ndi masamba 31. Ma DBMS ena ali ndi kuthekera kocheperako, koma aliyense waiwo ali ndi zinthu zambiri zosangalatsa komanso zapadera popanga matebulo (postgres, MySQL, mphemvu, cassandra). Ndizokayikitsa kuti "mfiti" iliyonse yojambula kuchokera ku IDE ina (makamaka yapadziko lonse) imatha kubisa luso lonseli, ndipo ngakhale itatha, sichikhala chochititsa chidwi kwa ofooka mtima. Pa nthawi yomweyo, mawu olembedwa molondola ndi pa nthawi yake pangani tebulo ikulolani kuti mugwiritse ntchito zonse mosavuta, kupanga kusungirako ndi kupeza deta yanu yodalirika, yabwino komanso yomasuka momwe mungathere.

Komanso, ma DBMS ambiri ali ndi mitundu yawoyawo ya zinthu zomwe sizipezeka mu ma DBMS ena. Komanso, tikhoza kuchita ntchito osati pazinthu zachinsinsi, komanso pa DBMS yokha, mwachitsanzo, "kupha" ndondomeko, kumasula malo ena okumbukira, kuthandizira kufufuza, kusintha "kuwerenga" mode, ndi zina zambiri.

Tsopano tiyeni tijambule pang'ono

Imodzi mwa ntchito zofala kwambiri ndikumanga chithunzi chokhala ndi zinthu za database ndikuwona zinthu ndi kulumikizana pakati pawo pachithunzi chokongola. Pafupifupi IDE iliyonse yojambula, zida zapadera za "command line", zida zapadera zojambulira ndi ma modelers amatha kuchita izi. Adzakukokerani china chake "momwe angathere," ndipo mutha kukhudza njirayi pang'ono mothandizidwa ndi magawo angapo mufayilo yosinthira kapena mabokosi ochezera pamawonekedwe.

Koma vutoli likhoza kuthetsedwa mosavuta, losavuta komanso lokongola, komanso mothandizidwa ndi code. Kuti tipange zojambula zazovuta zilizonse, tili ndi zilankhulo zingapo zapadera (DOT, GraphML ndi zina), komanso kwa iwo kufalikira kwa mapulogalamu (GraphViz, PlantUML, Mermaid) omwe amatha kuwerenga malangizowa ndikuwonera m'mawonekedwe osiyanasiyana. . Chabwino, tikudziwa kale momwe tingapezere zambiri za zinthu ndi kugwirizana pakati pawo.

Nachi chitsanzo chaching'ono cha momwe izi zingawonekere, pogwiritsa ntchito PlantUML ndi Demo database ya PostgreSQL (kumanzere kuli funso la SQL lomwe lipanga malangizo ofunikira a PlantUML, ndipo kumanja ndi zotsatira):

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'

Ndipo ngati muyesa pang'ono, ndiye potengera ER template ya PlantUML mutha kupeza chofanana kwambiri ndi chithunzi chenicheni cha ER:

Funso la SQL ndizovuta kwambiri

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

Ngati muyang'anitsitsa, pansi pa hood zida zambiri zowonetsera zimagwiritsanso ntchito mafunso ofanana. Zowona, zopempha izi nthawi zambiri zimakhala zozama "hardwired" mu code ya pulogalamuyo ndipo ndizovuta kumvetsetsa, osatchula kusinthidwa kulikonse kwawo.

Metrics ndi kuwunika

Tiyeni tipitirire pamutu womwe umakhala wovuta kwambiri - kuyang'anira magwiridwe antchito a database. Ndimakumbukira nkhani yaing’ono yoona yomwe inandiuza “m’modzi wa anzanga.” Pa pulojekiti ina panali DBA yamphamvu kwambiri, ndipo ochepa mwa omangawo adamudziwa, kapena adamuwonapo payekha (ngakhale kuti, malinga ndi mphekesera, adagwira ntchito kwinakwake m'nyumba yotsatira). Pa ola la "X", pamene makina opangira malonda a wogulitsa wamkulu adayambanso "kukhumudwa" kachiwiri, adatumiza mwakachetechete zithunzi za ma graph kuchokera kwa Oracle Enterprise Manager, pomwe adawunikira mosamala malo ovuta omwe ali ndi chizindikiro chofiira cha "kumvetsetsa" ( izi, kunena mofatsa, sizinathandize kwambiri). Ndipo kutengera "khadi lachithunzi" ichi ndimayenera kuchiza. Nthawi yomweyo, palibe amene anali ndi mwayi wopeza zamtengo wapatali (m'mawu onse awiri) Enterprise Manager, chifukwa dongosololi ndi lovuta komanso lokwera mtengo, mwadzidzidzi "opanga amapunthwa pa chinachake ndikuphwanya chirichonse." Chifukwa chake, opanga "empirically" adapeza malo ndi chifukwa cha mabuleki ndikutulutsa chigamba. Ngati kalata yowopsa yochokera ku DBA sinabwerenso posachedwa, ndiye kuti aliyense atha kupuma ndikubwerera ku ntchito zawo zamakono (mpaka Kalata yatsopano).

Koma njira yowunikirayi imatha kuwoneka yosangalatsa komanso yochezeka, komanso yofunika kwambiri, yopezeka komanso yowonekera kwa aliyense. Osachepera gawo lake lofunikira, monga chowonjezera pamakina oyang'anira (omwe alidi othandiza komanso osasinthika). DBMS iliyonse ndi yaulere komanso yaulere kugawana zambiri za momwe ikugwirira ntchito. Mu "magazi" omwewo Oracle DB, pafupifupi zidziwitso zilizonse zokhudzana ndi magwiridwe antchito zitha kupezeka kuchokera pamawonedwe adongosolo, kuyambira pamachitidwe ndi magawo mpaka momwe malo osungira (mwachitsanzo, Zolemba za DBA, gawo "Monitoring"). Postgresql ilinso ndi mawonedwe ambiri adongosolo kuyang'anira database, makamaka omwe ali ofunikira m'moyo watsiku ndi tsiku wa DBA iliyonse, monga pg_stat_activity, pg_stat_database, pg_stat_bgwriter. MySQL ngakhale ili ndi schema yosiyana ya izi. performance_schema. A Mu Mongo yomangidwa wolemba mbiri amawunjikitsa deta yogwira ntchito muzosonkhanitsa zadongosolo system.mbiri.

Chifukwa chake, okhala ndi mtundu wina wa otolera ma metrics (Telegraf, Metricbeat, Collectd) omwe amatha kufunsa mafunso a sql, kusungirako ma metric awa (InfluxDB, Elasticsearch, Timescaledb) ndi chowonera (Grafana, Kibana), mutha kupeza zosavuta. ndi njira yowunikira yosinthika yomwe idzaphatikizidwa kwambiri ndi ma metrics ena a dongosolo lonse (zopezedwa, mwachitsanzo, kuchokera ku seva yogwiritsira ntchito, kuchokera ku OS, etc.). Monga, mwachitsanzo, izi zimachitika mu pgwatch2, yomwe imagwiritsa ntchito kuphatikiza kwa InfluxDB + Grafana ndi mafunso angapo pamawonekedwe adongosolo, omwe amathanso kupezeka. onjezani mafunso okonda.

Chiwerengero

Ndipo uwu ndi mndandanda chabe wa zomwe tingathe kuchita ndi database yathu pogwiritsa ntchito SQL code nthawi zonse. Ndikukhulupirira kuti mutha kupeza zambiri zogwiritsa ntchito, lembani mu ndemanga. Ndipo tikambirana za momwe (ndipo chofunikira kwambiri chifukwa chake) mungasinthire zonsezi ndikuziphatikiza mupaipi yanu ya CI/CD nthawi ina.

Source: www.habr.com

"Database monga Code" Zochitika