SQL, o le a se mea e sili atu ona faigofie? E mafai e i tatou uma ona tusia se talosaga faigofie - tatou te taina filifili, lisi koluma mana'omia, ona mai, igoa laulau, nisi tulaga i lea ma na pau lava - o faʻamatalaga aoga o loʻo i totonu o la tatou taga, ma (toetoe lava) e tusa lava po o le a le DBMS o loʻo i lalo o le pulou i lena taimi (pe atonu ). O se taunuuga, o le galue ma toetoe lava o soʻo se faʻamatalaga faʻamatalaga (fesoʻotaʻiga ae le o lea) e mafai ona mafaufauina mai le manatu o le tulafono masani (faatasi ai ma mea uma e faʻaalia - faʻatonuga o faʻamatalaga, iloiloga o tulafono, suʻesuʻega static, autotests, ma na o mea uma). Ma e faʻaaoga lenei mea e le gata i faʻamatalaga lava ia, schemas ma migration, ae i se tulaga lautele i le olaga atoa o le teuina. I lenei tusiga o le a tatou talanoa e uiga i galuega i aso uma ma faʻafitauli o le galulue faʻatasi ma faʻamaumauga eseese i lalo o le tioata o le "faʻamaumauga e pei o le code".
Ma tatou amata sa'o mai . O taua muamua o le ituaiga "SQL vs ORM" na toe matauina i totonu .
Fa'afanua fa'atatau-mea
O le au lagolago a le ORM e masani ona faʻatauaina le saoasaoa ma le faigofie o le atinaʻe, tutoatasi mai le DBMS ma le tulafono mama. Mo le toatele oi tatou, o le code mo le galulue faatasi ma le database (ma e masani lava o le database lava ia)
e masani lava ona foliga fa'apenei...
@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;
}
...O le faʻataʻitaʻiga o loʻo tautau i faʻamatalaga atamai, ma i se mea i tua atu o vaaiga o se ORM totoa e faʻatupuina ma faʻatinoina le tele o nisi o code SQL. I le auala, o loʻo taumafai le au atinaʻe i le mea sili latou te mafaia e faʻaesea i latou lava mai a latou faʻamaumauga ma kilomita o faʻamatalaga, lea e faʻaalia ai nisi .
I le isi itu o pa puipui, o loʻo mulimulitaʻi i le "lima" SQL mama e matauina le mafai ona oomi uma le sua mai a latou DBMS e aunoa ma ni faʻaopoopoga faʻapipiʻi ma faʻamatalaga. O le iʻuga, o loʻo faʻaalia galuega faatino "faʻamaumauga-centric", lea e aofia ai tagata faʻapitoa aʻoaʻoga i totonu o faʻamaumauga (o latou foi o "basicists", o latou foi "basicists", o latou foi "basdeners", ma isi), ma le au atiae. naʻo le "toso" o mea ua saunia e vaʻaia ma faʻapipiʻiina taualumaga, e aunoa ma le alu i auiliiliga.
Ae faapefea pe ana tatou maua le mea sili o lalolagi uma e lua? E faʻafefea ona faia lenei mea i se meafaigaluega matagofie ma se igoa faʻamaonia-ola . O le a ou tuʻuina atu ni nai laina mai le manatu lautele i laʻu faʻaliliuga saoloto, ma e mafai ona e faʻamasani atili i ai. .
Clojure o se gagana manaia mo le fatuina o DSL, ae o le SQL lava ia o se DSL manaia, ma matou te le manaʻomia se isi. S-fa'aaliga e lelei tele, ae latou te le fa'aopoopoina se mea fou iinei. O se taunuuga, matou te maua faʻamau mo le lelei o puipui. Le ioe? Ona faʻatali lea mo le taimi e amata ai ona tafe le faʻaogaina i luga o le database ma amata ona e tau ma le galuega (raw-sql)
O le a la le mea e tatau ona ou faia? Sei o tatou tu'u le SQL e pei o SQL masani - tasi le faila i le talosaga:
-- name: users-by-country
select *
from users
where country_code = :country_code... ona faitau lea o le faila, liliu i se galuega masani 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" ...} ...)E ala i le tausisia o le "SQL na o ia, Clojure na o ia lava" mataupu faavae, e te maua:
- Leai ni fa'ate'ia fa'auiga. O lau 'upega tafaʻilagi (pei o isi) e le o 100% e faʻatatau i le SQL standard - ae e le afaina lea mo Yesql. E te le faʻaumatia le taimi e suʻe ai galuega faʻatasi ma SQL faʻatusa tutusa. E te le toe foi i se galuega (raw-sql "nisi ('funky'::SYNTAX)")).
- Lagolago faatonu sili. O lau fa'atonu o lo'o i ai le lagolago sili a SQL. E ala i le faʻasaoina o SQL e pei o SQL e mafai ona e faʻaaogaina.
- Fegalegaleaiga a le au. E mafai e au DBA ona faitau ma tusi le SQL e te fa'aogaina i lau poloketi Clojure.
- Fa'afaigofieina le fa'atinoga. Manaomia le fausiaina o se fuafuaga mo se faʻafitauli faʻafitauli? E le ose fa'afitauli pe a fai o lau fesili ole SQL masani.
- Toe fa'aaogaina fesili. Toso ma tu'u i lalo na faila SQL i isi galuega aua e na'o le SQL tuai - na'o le fa'asoa.
I loʻu manatu, o le manatu e manaia tele ma i le taimi lava e tasi e faigofie tele, faʻafetai i le mea na maua ai e le poloketi le tele i gagana eseese. Ma o le a matou taumafai e faʻaoga se filosofia tutusa o le vavaeeseina o le SQL code mai isi mea uma i tua atu o le ORM.
IDE & DB pule
Tatou amata i se galuega faigofie i aso uma. E masani lava e tatau ona tatou suʻeina nisi o mea i totonu o faʻamaumauga, mo se faʻataʻitaʻiga, suʻe se laulau i le schema ma suʻesuʻe lona fausaga (o le a le koluma, ki, faʻailoga, faʻalavelave, ma isi mea e faʻaaogaina). Ma mai soʻo se IDE faʻataʻitaʻi poʻo se DB-pule, muamua lava, matou te faʻamoemoe tonu i nei tomai. Ina ia vave ma e le tatau ona e faʻatali mo le afa itula seʻia oʻo i se faamalama ma faʻamatalaga talafeagai e tosoina (aemaise lava i se fesoʻotaʻiga faʻagesegese i se faʻamaumauga mamao), ma i le taimi lava e tasi, o faʻamatalaga na maua e fou ma talafeagai, ae le o mea leaga natia. E le gata i lea, o le sili atu le lavelave ma le tele o faʻamaumauga ma le tele o latou numera, o le sili atu ona faigata le faia o lenei mea.
Ae masani lava ou te togi ese le isumu ma na o le tusia o le code. Fa'apea e mana'omia ona e su'e po'o fea laulau (ma po'o a meatotino) o lo'o i totonu o le "HR" schema. I le tele o DBMS, e mafai ona maua le i'uga mana'omia i lenei fesili faigofie mai information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Mai fa'amaumauga i fa'amaumauga, o mea o lo'o i totonu o ia laulau fa'asino e eseese e fa'atatau i le gafatia o DBMS ta'itasi. Ma, mo se faʻataʻitaʻiga, mo MySQL, mai le tusi faʻamatalaga lava e tasi e mafai ona e mauaina faʻasologa o laulau faʻapitoa i lenei DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'E le iloa e Oracle information_schema, ae e iai , ma e leai ni fa'afitauli tetele e tula'i mai:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse e leai se tuusaunoaga:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'E mafai ona faia se mea faapena i Cassandra (lea e iai le columnfamilies nai lo laulau ma avanoa autu nai lo schemas):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Mo le tele o isi faʻamaumauga, e mafai foʻi ona e sau i luga ma fesili tutusa (e oʻo lava ia Mongo , lea e iai fa'amatalaga e uiga i fa'aputuga uma i totonu o le polokalama).
Ioe, i lenei auala e mafai ai ona e mauaina faʻamatalaga e le gata e uiga i laulau, ae e uiga i soʻo se mea i le lautele. Mai lea taimi i lea taimi, e faʻasoa e tagata agalelei ia faʻailoga mo faʻamaumauga eseese, e pei o, mo se faʻataʻitaʻiga, i le faasologa o tusiga habra "Functions for documenting PostgreSQL databases" (, , ). Ioe, o le teuina o lenei mauga atoa o fesili i loʻu ulu ma taina i taimi uma o se fiafiaga tele, o lea i laʻu IDE / faatonu e sili ona ou fiafia i ai, o loʻo i ai sau seti o snippets ua uma ona saunia mo fesili e masani ona faʻaaogaina, ma pau lava le mea o loʻo totoe o le taina o le igoa o mea i totonu o le mamanu.
O se taunuuga, o lenei metotia o le faʻaogaina ma le suʻeina o mea e sili atu ona fetuutuunai, faʻasaoina le tele o le taimi, ma faʻatagaina oe e maua tonu faʻamatalaga i le tulaga lea e manaʻomia nei (pei o, mo se faʻataʻitaʻiga, faʻamatalaina i le pou. ).
Gaoioiga ma mea faitino
A uma ona tatou mauaina ma suʻesuʻeina mea e manaʻomia, ua oʻo i le taimi e fai ai se mea aoga ia i latou. E masani lava, e aunoa ma le aveeseina o ou tamatamailima mai le piano.
E le o se mea lilo na o le tapeina o se laulau o le a foliga tutusa i le toetoe o faʻamaumauga uma:
drop table hr.personsAe o le fatuina o le laulau e sili atu ona manaia. Toeitiiti lava o soʻo se DBMS (e aofia ai le tele o NoSQL) e mafai ona "fausia le laulau" i se tasi ituaiga poʻo se isi, ma o le vaega autu o le a matua ese lava (igoa, lisi o koluma, ituaiga faʻamatalaga), ae o isi faʻamatalaga e mafai ona matua ese ma faʻalagolago i le masini i totonu ma gafatia o se DBMS patino. O laʻu faʻataʻitaʻiga e sili ona ou fiafia i ai o le i totonu o faʻamaumauga a Oracle o loʻo i ai naʻo "le lavalava" BNFs mo le faʻasologa o le "fausia laulau" . O isi DBMS e sili atu ona agavaʻa, ae o latou taʻitasi e iai foi le tele o mea manaia ma tulaga ese mo le fatuina o laulau (, , , ). E foliga mai e le mafai e soʻo se "taulaitu" faʻataʻitaʻi mai le isi IDE (aemaise lava le lautele) o le a mafai ona faʻaogaina atoatoa nei tomai uma, ma e tusa lava pe mafai, e le o se faʻaaliga mo le vaivai o le loto. I le taimi lava e tasi, o se faʻamatalaga tusitusia saʻo ma le taimi fatu laulau o le a faʻatagaina oe e faigofie ona faʻaoga uma, faʻapipiʻi ma maua au faʻamatalaga faʻalagolago, sili ona lelei ma faʻalelei pe a mafai.
E le gata i lea, o le tele o DBMS e iai a latou lava ituaiga o mea faitino e le maua i isi DBMS. E le gata i lea, e mafai ona tatou faʻatinoina gaioiga e le gata i luga o mea faʻamaumauga, ae faʻapea foʻi i luga o le DBMS lava ia, mo se faʻataʻitaʻiga, "fasiotia" se gaioiga, faʻasaʻoloto se vaega manatua, faʻatagaina le suʻeina, sui i le "faitau naʻo" mode, ma sili atu.
Ia tatou tusi teisi
O se tasi o galuega sili ona taatele o le fausia lea o se ata faʻatasi ma mea faʻamaumauga ma vaʻai i mea faitino ma fesoʻotaʻiga i le va oi latou i se ata matagofie. Toeitiiti lava o soʻo se IDE faʻataʻitaʻi, tuʻufaʻatasia "laina laina" faʻaoga, meafaigaluega faʻapitoa faʻataʻitaʻi ma faʻataʻitaʻiga e mafai ona faia lenei mea. O le a latou tusia se mea mo oe "i le mea sili latou te mafaia," ma e mafai ona e faʻatupuina lenei faʻagasologa naʻo le fesoasoani a ni nai faʻamaufaʻailoga i le faila faila poʻo pusa siaki i le atinaʻe.
Ae o lenei faafitauli e mafai ona foia sili atu ona faigofie, sili atu ona fetuutuunai ma aulelei, ma ioe faatasi ai ma le fesoasoani a code. Ina ia fatuina ata o soʻo se lavelave, e tele a matou gagana faʻapitoa faʻapitoa (DOT, GraphML ma isi), ma mo i latou le faʻasalalau atoa o talosaga (GraphViz, PlantUML, Mermaid) e mafai ona faitau ia faʻatonuga ma vaʻaia i latou i le tele o faatulagaga. . Ia, ua uma ona tatou iloa le auala e maua ai faʻamatalaga e uiga i mea faitino ma fesoʻotaʻiga i le va oi latou.
O se faʻataʻitaʻiga laʻititi o le mea e ono foliga mai, faʻaaoga PlantUML ma (i le agavale o se fesili SQL o le a maua ai le faʻatonuga manaʻomia mo PlantUML, ma i le taumatau o le iʻuga):
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'Ma afai e te taumafai teisi, ona faʻavae lea i luga e mafai ona e mauaina se mea e tutusa lelei ma se ata moni ER:
Ole fesili ole SQL e fai si lavelave
-- Шапка
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'
Afai e te vaʻavaʻai totoʻa, i lalo o le pulou e tele meafaigaluega faʻaaliga e faʻaaogaina foi fesili tutusa. E moni, o nei talosaga e masani ona loloto , ae le o taʻua soʻo se suiga o latou.
Fuafuaga ma le mataituina
Se'i o tatou aga'i atu ile mataupu fa'aleaganu'u lavelave - database performance monitoring. Ou te manatua se tala moni laitiiti na ta’u mai e “se tasi o a’u uo.” I luga o se isi poloketi sa i ai se DBA malosi, ma e toaitiiti le au atinaʻe na iloa o ia, pe na vaʻaia lava o ia (e ui i le mea moni, e tusa ai ma tala, na ia galue i se mea i le isi fale). I le itula "X", ina ua toe amata ona "lagona le leaga" le faiga o le poduction a se faleoloa tele, na ia lafoina lemu ni screenshots o kalafi mai le Oracle Enterprise Manager, lea na ia faʻamaonia ai ma le faaeteete nofoaga taua ma se faailoga mumu mo le "malamalama" ( o lenei, pe a faʻafefe, e leʻi fesoasoani tele). Ma e faavae i luga o lenei "ata ata" sa tatau ona ou togafitia. I le taimi lava e tasi, e leai se tasi na maua le taua (i lagona uma e lua o le upu) Enterprise Manager, aua o le faiga e lavelave ma taugata, faʻafuaseʻi "e tautevateva i luga o se mea ma talepe mea uma." O le mea lea, na maua ai e le au atinaʻe le nofoaga ma le mafuaʻaga o taofi ma tuʻuina atu se patch. Afai e le toe oo mai le tusi taufaamata'u mai le DBA i se taimi lata mai, ona manava lea o tagata uma i le mapuea ma toe foi atu i a latou galuega o loo i ai nei (seia oo i le Tusi fou).
Ae o le faagasologa o le mataituina e mafai ona foliga sili atu le malie ma le faauo, ma sili ona taua, avanoa ma manino mo tagata uma. Le itiiti ifo o lona vaega autu, o se faʻaopoopoga i faiga mataʻituina autu (lea e mautinoa lava aoga ma i le tele o tulaga e le mafai ona suia). So'o se DBMS e sa'oloto ma matua leai se totogi e fa'asoa fa'amatalaga e uiga i lona tulaga o iai nei ma le fa'atinoga. I le "toto" lava e tasi Oracle DB, toetoe lava o soʻo se faʻamatalaga e uiga i le faʻatinoga e mafai ona maua mai faʻaaliga faʻaogaina, e amata mai i faʻagasologa ma sauniga i le tulaga o le faʻamaufaʻailoga (mo se faʻataʻitaʻiga, , vaega "Monitoring"). O loʻo i ai foi i le Postgresql le tele o vaaiga faʻaogaina mo , aemaise lava i latou e taua i le olaga i aso uma o soʻo se DBA, pei o , , . O loʻo iai foʻi i MySQL se faʻasologa ese mo lenei. . A In Mongo fausia-i totonu tu'ufa'atasia fa'amaumauga o fa'atinoga i se fa'aputuga faiga .
O le mea lea, faʻaauupegaina i se ituaiga o metric collector (Telegraf, Metricbeat, Collectd) e mafai ona faia faʻataʻitaʻiga sql fesili, se teuina o nei metrics (InfluxDB, Elasticsearch, Timescaledb) ma se vaʻaia (Grafana, Kibana), e mafai ona e maua se faigofie faigofie. ma se faiga mata'ituina fetu'utu'una'i o le a tu'ufa'atasia fa'atasi ma isi fa'ata'ita'iga lautele (maua, mo se fa'ata'ita'iga, mai le server application, mai le OS, ma isi). E pei, mo se faʻataʻitaʻiga, e faia lenei mea i le pgwatch2, lea e faʻaogaina ai le InfluxDB + Grafana faʻatasi ma se seti o fesili i faʻamatalaga faʻaoga, lea e mafai foi ona maua. .
Aofaʻi
Ma e na'o se lisi fa'atatau lea o mea e mafai ona fai i la matou fa'amaumauga e fa'aaoga ai le code SQL masani. Ou te mautinoa e mafai ona e mauaina le tele o isi faʻaoga, tusi i faʻamatalaga. Ma o le a matou talanoa pe faʻafefea (ma sili ona taua pe aisea) e faʻaogaina ai nei mea uma ma faʻapipiʻi i lau CI / CD pipeline i le isi taimi.
puna: www.habr.com