SQL, he aha ka mea maʻalahi? Hiki i kēlā me kēia o mākou ke kākau i kahi noi maʻalahi - paʻi mākou koho i, papa inoa i nā kolamu i makemake ʻia, a laila mai, inoa papa, kekahi mau kūlana ma kahi a ʻo ia wale nō - aia ka ʻikepili pono i loko o kā mākou ʻeke, a (kokoke) me ka nānā ʻole i ka DBMS ma lalo o ka pā i kēlā manawa (a i ʻole paha. ). ʻO ka hopena, hiki ke noʻonoʻo ʻia ka hana ʻana me kahi kumu ʻikepili (pili a ʻaʻole pēlā) mai ka manaʻo o ke code maʻamau (me nā mea āpau e pili ana - mana mana, loiloi code, static analysis, autotests, a ʻo ia wale nō). ʻAʻole pili kēia i ka ʻikepili ponoʻī, nā schemas a me nā neʻe ʻana, akā ma ka laulā i ke ola holoʻokoʻa o ka waiho ʻana. Ma kēiaʻatikala e kamaʻilio mākou e pili ana i nā hana o kēlā me kēia lā a me nā pilikia o ka hana ʻana me nā ʻikepili like ʻole ma lalo o ka lens o "database as code".
A e hoʻomaka kāua mai . Ua ʻike hou ʻia nā kaua mua o ke ʻano "SQL vs ORM". .
Ka palapala 'āina pili-mea
Hoʻohanohano ka poʻe kākoʻo ORM i ka wikiwiki a me ka maʻalahi o ka hoʻomohala ʻana, ke kūʻokoʻa mai ka DBMS a me ka code maʻemaʻe. No ka hapa nui o mākou, ʻo ke code no ka hana ʻana me ka waihona (a pinepine ka waihona ʻikepili ponoʻī)
he mea maʻamau e like me kēia...
@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;
}
...Hoʻopili ʻia ke kumu hoʻohālike me nā annotations akamai, a ma kekahi wahi ma hope o nā hiʻohiʻona kahi ORM wiwo ʻole e hana a hoʻokō i nā tona o kekahi code SQL. Ma ke ala, ke hoʻāʻo nei nā mea hoʻomohala e hoʻokaʻawale iā lākou iho mai kā lākou waihona me nā kilomika o abstractions, e hōʻike ana i kekahi .
Ma ka ʻaoʻao ʻē aʻe o nā pā, ʻike ka poʻe pili i ka SQL "hana lima" maʻemaʻe i ka hiki ke ʻoki i ka wai āpau mai kā lākou DBMS me ka ʻole o nā papa a me nā abstractions. ʻO ka hopena, ʻike ʻia nā papahana "data-centric", kahi e komo ai nā poʻe i hoʻomaʻamaʻa kūikawā i ka waihona (he "basicists" nō hoʻi lākou, he "basicists", he "basdeners", etc.), a me nā mea hoʻomohala. pono wale nō e "huki" i nā manaʻo i mākaukau a mālama ʻia, me ka ʻole e komo i nā kikoʻī.
Pehea inā loaʻa iā mākou ka maikaʻi o nā ao ʻelua? Pehea e hana ʻia ai kēia ma kahi mea hana kupanaha me ka inoa hoʻohiki ola . E hāʻawi wau i ʻelua mau laina mai ka manaʻo nui ma kaʻu unuhi manuahi, a hiki iā ʻoe ke kamaʻāina me ia i nā kikoʻī hou aku. .
He ʻōlelo ʻoluʻolu ʻo Clojure no ka hoʻokumu ʻana i nā DSL, akā ʻo SQL ponoʻī he DSL maikaʻi loa, ʻaʻole pono mākou i kekahi. He maikaʻi nā ʻōlelo S, akā ʻaʻole lākou e hoʻohui i kahi mea hou ma aneʻi. ʻO ka hopena, loaʻa iā mākou nā brackets no ka pono o nā brackets. ʻAʻole ʻae? A laila e kali no ka manawa e hoʻomaka ai ka leak o ka abstraction ma luna o ka waihona a hoʻomaka ʻoe e hakakā me ka hana (raw-sql)
No laila he aha kaʻu e hana ai? E waiho iā SQL ma ke ʻano he SQL maʻamau - hoʻokahi faila i kēlā me kēia noi:
-- name: users-by-country
select *
from users
where country_code = :country_code... a laila heluhelu i kēia faila, e hoʻololi iā ia i kahi hana Clojure maʻamau:
(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" ...} ...)Ma ka pili ʻana i ka "SQL iā ia iho, Clojure iā ia iho", loaʻa iā ʻoe:
- ʻAʻohe pūʻiwa syntactic. ʻAʻole 100% kūpono kāu waihona (e like me nā mea ʻē aʻe) me ka maʻamau SQL - akā ʻaʻole pili kēia iā Yesql. ʻAʻole ʻoe e hoʻopau manawa i ka ʻimi ʻana i nā hana me ka syntax like SQL. ʻAʻole pono ʻoe e hoʻi i kahi hana (raw-sql "kekahi ('funky'::SYNTAX)")).
- Kākoʻo hoʻoponopono maikaʻi loa. Loaʻa i kāu mea hoʻoponopono ke kākoʻo SQL maikaʻi loa. Ma ka mālama ʻana iā SQL e like me SQL hiki iā ʻoe ke hoʻohana maʻalahi.
- Hui pū. Hiki i kāu DBA ke heluhelu a kākau i ka SQL āu e hoʻohana ai i kāu papahana Clojure.
- ʻOi aku ka maʻalahi o ka hoʻokō ʻana. Pono e kūkulu i kahi hoʻolālā no kahi nīnau pilikia? ʻAʻole pilikia kēia inā he SQL maʻamau kāu nīnau.
- Hoʻohana hou i nā nīnau. Kauo a hoʻokuʻu i kēlā mau faila SQL i nā papahana ʻē aʻe no ka mea he SQL kahiko wale nō ia - e kaʻana like.
I koʻu manaʻo, ʻoluʻolu loa ka manaʻo a ma ka manawa like maʻalahi loa, mahalo i ka mea i loaʻa i ka papahana he nui ma nā ʻōlelo like ʻole. A e hoʻāʻo hou mākou e hoʻopili i kahi ʻano kuʻuna like o ka hoʻokaʻawale ʻana i ka code SQL mai nā mea āpau ma waho o ka ORM.
IDE & DB manakia
E hoʻomaka kākou me kahi hana maʻalahi o kēlā me kēia lā. I ka manawa pinepine, pono mākou e ʻimi i kekahi mau mea i loko o ka waihona, no ka laʻana, e ʻimi i kahi papa ma ka schema a e aʻo i kona ʻano (he aha nā kolamu, nā kī, nā kuhikuhi, nā palena, a me nā mea e hoʻohana ʻia). A mai kekahi IDE kiʻi a i ʻole kahi DB-manager, ʻo ka mea mua, manaʻo mākou i kēia mau mana. No laila e wikiwiki a ʻaʻole pono ʻoe e kali i ka hapalua hola a hiki i ka pukaaniani me ka ʻike e pono ai ke huki ʻia (ʻoi aku me ka pili lohi i kahi ʻikepili mamao), a i ka manawa like, he hou a pili ka ʻike i loaʻa. a ʻaʻole i hūnā ʻia nā ʻōpala. Eia kekahi, ʻoi aku ka paʻakikī a me ka nui o ka waihona a me ka nui o ka nui o lākou, ʻoi aku ka paʻakikī o ka hana ʻana i kēia.
Akā ʻo ka maʻamau, hoʻolei wau i ka ʻiole a kākau wale i ke code. E ʻōlelo mākou pono ʻoe e ʻike i nā papa (a me nā waiwai) i loko o ka schema "HR". Ma ka hapanui o nā DBMS, hiki ke loaʻa ka hopena i makemake ʻia me kēia nīnau maʻalahi mai information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Mai ka waihona a i ka waihona, ʻokoʻa nā ʻike o ia mau papa kuhikuhi ma muli o ka hiki o kēlā me kēia DBMS. A, no ka laʻana, no MySQL, mai ka puke kuhikuhi like hiki iā ʻoe ke kiʻi i nā ʻāpana papaʻaina kikoʻī i kēia DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'ʻAʻole ʻike ʻo Oracle i ka information_schema, akā aia , a ʻaʻohe pilikia nui:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ʻAʻole ʻokoʻa ʻo ClickHouse:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Hiki ke hana i kekahi mea like ma Cassandra (he columnfamilies ma kahi o nā papa a me nā kī kī ma kahi o nā schemas):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'No ka hapa nui o nā ʻikepili ʻē aʻe, hiki iā ʻoe ke hele mai me nā nīnau like (ʻo Mongo hoʻi , i loaʻa ka ʻike e pili ana i nā hōʻiliʻili āpau i ka ʻōnaehana).
ʻOiaʻiʻo, ma kēia ala hiki iā ʻoe ke loaʻa ka ʻike e pili ana i nā papaʻaina wale nō, akā e pili ana i kekahi mea i ka laulā. Mai kēlā manawa kēia manawa, kaʻana like ka poʻe lokomaikaʻi i kēlā code no nā waihona ʻikepili like ʻole, e like me, no ka laʻana, i ka moʻolelo habra "Nā hana no ka palapala ʻana i nā waihona ʻikepili PostgreSQL" (, , ). ʻOiaʻiʻo, ʻo ka mālama ʻana i kēia mauna mau nīnau ma koʻu poʻo a me ka paʻi mau ʻana iā lākou he mea leʻaleʻa ia, no laila i kaʻu IDE/editor punahele loaʻa iaʻu kahi pūʻulu o nā snippets i hoʻomākaukau mua ʻia no nā nīnau i hoʻohana pinepine ʻia, a ʻo ke koena wale nō ke kākau i ka. inoa mea i loko o ka la'ana.
ʻO ka hopena, ʻoi aku ka maʻalahi o kēia ʻano o ka hoʻokele ʻana a me ka ʻimi ʻana i nā mea, mālama i ka manawa, a hiki iā ʻoe ke kiʻi pololei i ka ʻike ma ke ʻano e pono ai i kēia manawa (e like me ka laʻana, i wehewehe ʻia ma ka pou. ).
Nā hana me nā mea
Ma hope o ka loaʻa ʻana a aʻo ʻana i nā mea e pono ai, ʻo ia ka manawa e hana i kahi mea pono me lākou. Ma keʻano maʻamau, me ka lawe ʻole ʻana i kou mau manamana lima mai ka papa keyboard.
ʻAʻole ia he mea huna ʻo ka holoi wale ʻana i kahi papaʻaina e like ke ʻano ma kahi kokoke i nā ʻikepili āpau:
drop table hr.personsAkā me ka hana ʻana o ka papaʻaina e lilo ia i mea hoihoi. Aneane hiki i nā DBMS (me nā NoSQL he nui) ke "hana i ka papaʻaina" ma kekahi ʻano a i ʻole, a ʻokoʻa iki ka hapa nui o ia mea (inoa, papa inoa o nā kolamu, ʻano ʻikepili), akā hiki ke ʻokoʻa nā kikoʻī ʻē aʻe a hilinaʻi i ka mea i loko a me ka hiki o kahi DBMS kiko'ī. ʻO kaʻu hiʻohiʻona punahele ʻo ia i loko o ka palapala Oracle aia wale nō nā BNF "huna" no ka syntax "hana papa" . Loaʻa i nā DBMS ʻē aʻe nā mana haʻahaʻa, akā loaʻa i kēlā me kēia o lākou nā hiʻohiʻona hoihoi a kū hoʻokahi no ka hana ʻana i nā papa (, , , ). ʻAʻole paha e hiki i kekahi "wizard" kiʻi mai kahi IDE ʻē aʻe (ʻoi aku ka honua holoʻokoʻa) e hiki ke uhi piha i kēia mau mana āpau, a inā hiki iā ia, ʻaʻole ia he mea nānā no ka poʻe nawaliwali. I ka manawa like, he ʻōlelo i kākau ʻia a pololei hana papaʻaina e ʻae iā ʻoe e hoʻohana maʻalahi iā lākou a pau, e mālama i ka mālama ʻana a me ke komo ʻana i kāu ʻikepili i hilinaʻi ʻia, kūpono a ʻoluʻolu hoʻi.
Eia kekahi, nui nā DBMS i kā lākou mau ʻano kikoʻī o nā mea i loaʻa ʻole i nā DBMS ʻē aʻe. Eia kekahi, hiki iā mākou ke hana i nā hana ʻaʻole wale ma nā mea waihona, akā ma ka DBMS ponoʻī, no ka laʻana, "pepehi" i kahi kaʻina hana, hoʻokuʻu i kahi wahi hoʻomanaʻo, hiki i ka huli ʻana, hoʻololi i ke ʻano "heluhelu wale nō", a ʻoi aku.
I kēia manawa, e kaha iki kāua
ʻO kekahi o nā hana maʻamau ʻo ke kūkulu ʻana i kahi kiʻi me nā mea waihona waihona a ʻike i nā mea a me nā pilina ma waena o lākou i kahi kiʻi nani. Kokoke i kekahi IDE kiʻi, hoʻokaʻawale i nā pono "laina kauoha", nā mea hana kiʻi kūikawā a me nā mea hoʻohālike hiki ke hana i kēia. E kahakiʻi lākou i kahi mea no ʻoe "e like me ka mea hiki iā lākou," a hiki iā ʻoe ke hoʻololi iki i kēia kaʻina hana me ke kōkua ʻana o kekahi mau ʻāpana i ka faila hoʻonohonoho a i ʻole nā pahu pahu i ka interface.
Akā hiki ke hoʻoponopono ʻia kēia pilikia me ka maʻalahi, ʻoi aku ka maʻalahi a me ka nani, a ʻoiaʻiʻo hoʻi me ke kōkua o ke code. No ka hana ʻana i nā kiʻikuhi o nā paʻakikī, loaʻa iā mākou kekahi mau ʻōlelo markup kūikawā (DOT, GraphML etc.), a no lākou ka hoʻopuehu holoʻokoʻa o nā noi (GraphViz, PlantUML, Mermaid) hiki ke heluhelu i nā ʻōlelo aʻo a nānā iā lākou i nā ʻano like ʻole. . ʻAe, ua ʻike mua mākou pehea e loaʻa ai ka ʻike e pili ana i nā mea a me nā pilina ma waena o lākou.
Eia kahi hiʻohiʻona liʻiliʻi o ke ʻano o kēia, me ka hoʻohana ʻana iā PlantUML a (ma ka hema he nīnau SQL e hoʻopuka i ka ʻōlelo aʻo i koi ʻia no PlantUML, a ma ka ʻākau ka hopena):
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'A inā ʻoe e hoʻāʻo liʻiliʻi, a laila ma muli o hiki iā ʻoe ke loaʻa i kahi mea like loa me kahi kiʻi ER maoli:
ʻOi aku ka paʻakikī o ka nīnau SQL
-- Шапка
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'
Inā ʻoe e nānā pono, ma lalo o ka puʻupuʻu, hoʻohana pū kekahi mau mea hana ʻike i nā nīnau like. ʻOiaʻiʻo, hohonu loa kēia mau noi , ʻaʻole e haʻi i kekahi hoʻololi o lākou.
Nā ana a me ka nānā ʻana
E neʻe kākou i kahi kumuhana paʻakikī - ka nānā ʻana i ka hana ʻikepili. Ke hoʻomanaʻo nei au i kahi moʻolelo ʻoiaʻiʻo liʻiliʻi i haʻi ʻia mai iaʻu e "kekahi o koʻu mau hoaaloha." Ma kahi papahana ʻē aʻe i noho kekahi DBA ikaika, a he kakaikahi o nā mea hoʻomohala i ʻike pono iā ia, a i ʻole i ʻike iā ia ma ke kino (ʻoiai ʻo ia, e like me nā lono, ua hana ʻo ia ma kahi o ka hale aʻe). I ka hola "X", i ka wā i hoʻomaka hou ai ka ʻōnaehana poduction o kahi hale kūʻai nui e "hōʻino" hou, ua hoʻouna ʻo ia i nā screenshots o nā kiʻi mai Oracle Enterprise Manager, kahi āna i kuhikuhi pono ai i nā wahi koʻikoʻi me kahi māka ʻulaʻula no ka "hoʻomaopopo" ( ʻo kēia, ʻaʻole i kōkua nui). A ma muli o kēia "kāleka kiʻi" pono wau e mālama. I ka manawa like, ʻaʻohe mea i loaʻa i ka waiwai (ma nā manaʻo ʻelua o ka huaʻōlelo) Enterprise Manager, no ka mea paʻakikī a pipiʻi ka ʻōnaehana, hikiwawe "ua hina nā mea hoʻomohala i kekahi mea a wāwahi i nā mea āpau." No laila, ua loaʻa i nā mea hoʻomohala "empirically" kahi a me ke kumu o nā kaʻa a hoʻokuʻu i kahi pā. Inā ʻaʻole hiki hou mai ka leka hoʻoweliweli mai ka DBA i ka wā e hiki mai ana, a laila e hanu ka poʻe a pau a hoʻi i kā lākou mau hana i kēia manawa (a hiki i ka Letter hou).
Akā ʻoi aku ka leʻaleʻa a me ke aloha o ke kaʻina hana nānā, a ʻo ka mea nui loa, hiki ke ʻike a maopopo no nā mea a pau. Ma ka liʻiliʻi o kāna ʻāpana kumu, ma ke ʻano he hoʻohui i nā ʻōnaehana kiaʻi nui (ʻo ia ka mea e pono ai a i nā manawa he nui i hiki ʻole ke hoʻololi). ʻO kēlā me kēia DBMS he manuahi a manuahi ʻole e kaʻana like i ka ʻike e pili ana i kona kūlana o kēia manawa a me kāna hana. Ma ka Oracle DB "koko" like, kokoke i nā ʻike e pili ana i ka hana hiki ke loaʻa mai nā ʻike ʻōnaehana, mai nā kaʻina hana a me nā kau a hiki i ke kūlana o ka cache buffer (no ka laʻana, , pauku "Monitoring"). Loaʻa iā Postgresql kahi pūʻulu o nā ʻōnaehana ʻōnaehana no , i nā mea e pono ai i ke ola o kēlā me kēia lā o kekahi DBA, e like me , , . Loaʻa iā MySQL kahi schema kaʻawale no kēia. . A In Mongo i kūkulu ʻia hōʻuluʻulu i ka ʻikepili hana i loko o kahi hōʻiliʻili ʻōnaehana .
No laila, i hoʻopaʻa ʻia me kekahi ʻano mea hōʻiliʻili metric (Telegraf, Metricbeat, Collectd) hiki ke hana i nā nīnau sql maʻamau, kahi mālama o kēia mau metric (InfluxDB, Elasticsearch, Timescaledb) a me kahi mea nānā (Grafana, Kibana), hiki iā ʻoe ke maʻalahi. a me kahi ʻōnaehana nānā maʻalahi e hoʻopili pono ʻia me nā anana ākea ākea ʻē aʻe (loaʻa, no ka laʻana, mai ke kikowaena noi, mai ka OS, etc.). No ka laʻana, hana ʻia kēia ma pgwatch2, e hoʻohana ana i ka hui InfluxDB + Grafana a me kahi hoʻonohonoho o nā nīnau i nā ʻike ʻōnaehana, hiki ke kiʻi ʻia. .
Hōʻuluʻulu
A ʻo kēia wale nō kahi papa inoa o nā mea hiki ke hana me kā mākou waihona me ka hoʻohana ʻana i ka code SQL maʻamau. Manaʻo wau hiki iā ʻoe ke loaʻa i nā mea hoʻohana hou aʻe, e kākau i nā manaʻo. A e kamaʻilio mākou e pili ana (a ʻo ka mea nui loa) e hoʻomaʻamaʻa i kēia mau mea āpau a hoʻokomo iā ia i kāu pipeline CI / CD i ka manawa aʻe.
Source: www.habr.com