Dziko lazosungirako lakhala likulamulidwa ndi ma DBMS ogwirizana, omwe amagwiritsa ntchito chinenero cha SQL. Mochuluka kotero kuti mitundu yomwe ikubwera imatchedwa NoSQL. Anatha kudzipangira malo ena pamsika, koma ma DBMS ogwirizana sangafe, ndipo akupitirizabe kugwiritsidwa ntchito pazolinga zawo.
M'nkhaniyi ndikufuna kufotokoza lingaliro la database yogwira ntchito. Kuti mumvetse bwino, ndichita izi pozifanizitsa ndi chitsanzo chaubale chachikale. Mavuto ochokera ku mayeso osiyanasiyana a SQL opezeka pa intaneti adzagwiritsidwa ntchito ngati zitsanzo.
Mau oyamba
Zolemba zaubwenzi zimagwira ntchito pamatebulo ndi minda. Mu database yogwira ntchito, makalasi ndi ntchito zidzagwiritsidwa ntchito m'malo mwake, motsatana. Munda womwe uli patebulo wokhala ndi makiyi a N udzayimiridwa ngati ntchito ya magawo a N. M'malo mwa maubwenzi pakati pa matebulo, ntchito zidzagwiritsidwa ntchito zomwe zimabwezera zinthu za kalasi yomwe kugwirizanako kumapangidwira. Zolemba zake zidzagwiritsidwa ntchito m'malo mwa JOIN.
Ndisanasamuke mwachindunji ku ntchitozo, ndikufotokozerani ntchito ya domain logic. Kwa DDL ndigwiritsa ntchito mawu a PostgreSQL. Kwa magwiridwe antchito ali ndi mawu ake.
Matebulo ndi minda
Chinthu chosavuta cha Sku chokhala ndi dzina ndi magawo amitengo:
Zachibale
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
ntchito
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Timalengeza ziwiri ntchito, zomwe zimatenga gawo limodzi la Sku ngati cholowetsa ndikubweza mtundu wakale.
Zimaganiziridwa kuti mu DBMS yogwira ntchito chinthu chilichonse chidzakhala ndi code yamkati yomwe imapangidwa yokha ndipo imatha kupezeka ngati kuli kofunikira.
Tiyeni tiyike mtengo wa chinthu/sitolo/wopereka. Zitha kusintha pakapita nthawi, kotero tiyeni tiwonjezere gawo la nthawi patebulo. Ndilumpha kulengeza matebulo aakalozera mu nkhokwe yaubale kuti ndifupikitse kachidindo:
Zachibale
CREATE TABLE prices
(
skuId bigint NOT NULL,
storeId bigint NOT NULL,
supplierId bigint NOT NULL,
dateTime timestamp without time zone,
price numeric(10,5),
CONSTRAINT prices_pkey PRIMARY KEY (skuId, storeId, supplierId)
)
ntchito
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Zisonyezero
Pachitsanzo chomaliza, tidzapanga ndondomeko pa makiyi onse ndi tsiku kuti tipeze mwamsanga mtengo wa nthawi inayake.
Zachibale
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
ntchito
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
ntchito
Tiyeni tiyambe ndi zovuta zosavuta zomwe zatengedwa kuchokera ku zofanana
Choyamba, tiyeni tilengeze zachidziwitso cha domain (kwa database yolumikizana izi zachitika mwachindunji m'nkhani yomwe ili pamwambapa).
CLASS Department;
name = DATA STRING[100] (Department);
CLASS Employee;
department = DATA Department (Employee);
chief = DATA Employee (Employee);
name = DATA STRING[100] (Employee);
salary = DATA NUMERIC[14,2] (Employee);
Ntchito 1.1
Onetsani mndandanda wa antchito omwe amalandira malipiro ochulukirapo kuposa omwe amawayang'anira.
Zachibale
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
ntchito
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Ntchito 1.2
Lembani antchito omwe amalandira malipiro apamwamba mu dipatimenti yawo
Zachibale
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
ntchito
maxSalary 'ΠΠ°ΠΊΡΠΈΠΌΠ°Π»ΡΠ½Π°Ρ Π·Π°ΡΠΏΠ»Π°ΡΠ°' (Department s) =
GROUP MAX salary(Employee e) IF department(e) = s;
SELECT name(Employee a) WHERE salary(a) = maxSalary(department(a));
// ΠΈΠ»ΠΈ Π΅ΡΠ»ΠΈ "Π·Π°ΠΈΠ½Π»Π°ΠΉΠ½ΠΈΡΡ"
SELECT name(Employee a) WHERE
salary(a) = maxSalary(GROUP MAX salary(Employee e) IF department(e) = department(a));
Zokhazikitsa zonsezo ndizofanana. Pachiyambi choyamba, mu database yaubale mungagwiritse ntchito CREATE VIEW, yomwe mofananamo idzawerengera malipiro apamwamba a dipatimenti inayake mmenemo. Zotsatirazi, kuti zimveke bwino, ndigwiritsa ntchito yoyamba, chifukwa ikuwonetsera bwino yankho.
Ntchito 1.3
Onetsani mndandanda wa ma ID a dipatimenti, kuchuluka kwa ogwira ntchito omwe sikudutsa anthu atatu.
Zachibale
select department_id
from employee
group by department_id
having count(*) <= 3
ntchito
countEmployees 'ΠΠΎΠ»ΠΈΡΠ΅ΡΡΠ²ΠΎ ΡΠΎΡΡΡΠ΄Π½ΠΈΠΊΠΎΠ²' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Ntchito 1.4
Onetsani mndandanda wa antchito omwe alibe manejala wosankhidwa omwe amagwira ntchito mu dipatimenti yomweyo.
Zachibale
select a.*
from employee a
left join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where b.id is null
ntchito
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Ntchito 1.5
Pezani mndandanda wa ma ID a dipatimenti omwe ali ndi malipiro apamwamba a antchito.
Zachibale
with sum_salary as
( select department_id, sum(salary) salary
from employee
group by department_id )
select department_id
from sum_salary a
where a.salary = ( select max(salary) from sum_salary )
ntchito
salarySum 'ΠΠ°ΠΊΡΠΈΠΌΠ°Π»ΡΠ½Π°Ρ Π·Π°ΡΠΏΠ»Π°ΡΠ°' (Department d) =
GROUP SUM salary(Employee e) IF department(e) = d;
maxSalarySum 'ΠΠ°ΠΊΡΠΈΠΌΠ°Π»ΡΠ½Π°Ρ Π·Π°ΡΠΏΠ»Π°ΡΠ° ΠΎΡΠ΄Π΅Π»ΠΎΠ²' () =
GROUP MAX salarySum(Department d);
SELECT Department d WHERE salarySum(d) = maxSalarySum();
Tiyeni tipitirire ku ntchito zovuta kwambiri kuchokera ku zina
Ntchito 2.1
Ndi ogulitsa ati omwe adagulitsa mayunitsi opitilira 1997 azinthu No. 30 mu 1?
Domain logic (monga kale pa RDBMS timalumpha chilengezo):
CLASS Employee 'ΠΡΠΎΠ΄Π°Π²Π΅Ρ';
lastName 'Π€Π°ΠΌΠΈΠ»ΠΈΡ' = DATA STRING[100] (Employee);
CLASS Product 'ΠΡΠΎΠ΄ΡΠΊΡ';
id = DATA INTEGER (Product);
name = DATA STRING[100] (Product);
CLASS Order 'ΠΠ°ΠΊΠ°Π·';
date = DATA DATE (Order);
employee = DATA Employee (Order);
CLASS Detail 'Π‘ΡΡΠΎΠΊΠ° Π·Π°ΠΊΠ°Π·Π°';
order = DATA Order (Detail);
product = DATA Product (Detail);
quantity = DATA NUMERIC[10,5] (Detail);
Zachibale
select LastName
from Employees as e
where (
select sum(od.Quantity)
from [Order Details] as od
where od.ProductID = 1 and od.OrderID in (
select o.OrderID
from Orders as o
where year(o.OrderDate) = 1997 and e.EmployeeID = o.EmployeeID)
) > 30
ntchito
sold (Employee e, INTEGER productId, INTEGER year) =
GROUP SUM quantity(OrderDetail d) IF
employee(order(d)) = e AND
id(product(d)) = productId AND
extractYear(date(order(d))) = year;
SELECT lastName(Employee e) WHERE sold(e, 1, 1997) > 30;
Ntchito 2.2
Kwa wogula aliyense (dzina, surname), pezani zinthu ziwiri (dzina) zomwe wogula adawononga ndalama zambiri mu 1997.
Timakulitsa logic ya domain kuchokera ku chitsanzo cham'mbuyomu:
CLASS Customer 'ΠΠ»ΠΈΠ΅Π½Ρ';
contactName 'Π€ΠΠ' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Zachibale
SELECT ContactName, ProductName FROM (
SELECT c.ContactName, p.ProductName
, ROW_NUMBER() OVER (
PARTITION BY c.ContactName
ORDER BY SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) DESC
) AS RatingByAmt
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
WHERE YEAR(o.OrderDate) = 1997
GROUP BY c.ContactName, p.ProductName
) t
WHERE RatingByAmt < 3
ntchito
sum (Detail d) = quantity(d) * unitPrice(d) * (1 - discount(d));
bought 'ΠΡΠΏΠΈΠ»' (Customer c, Product p, INTEGER y) =
GROUP SUM sum(Detail d) IF
customer(order(d)) = c AND
product(d) = p AND
extractYear(date(order(d))) = y;
rating 'Π Π΅ΠΉΡΠΈΠ½Π³' (Customer c, Product p, INTEGER y) =
PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y;
SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;
Wogwiritsa PARTITION amagwira ntchito motsatira mfundo iyi: amawerengera mawu omwe atchulidwa pambuyo pa SUM (apa 1), m'magulu omwe atchulidwa (pano Makasitomala ndi Chaka, koma atha kukhala mawu aliwonse), kusanja m'magulu ndi mawu omwe afotokozedwa mu ORDER ( apa adagula, ndipo ngati ali ofanana, ndiye molingana ndi code yamkati).
Ntchito 2.3
Ndi katundu angati omwe amayenera kuyitanidwa kuchokera kwa ogulitsa kuti akwaniritse maoda apano.
Tiyeni tikulitsenso logic ya domain:
CLASS Supplier 'ΠΠΎΡΡΠ°Π²ΡΠΈΠΊ';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'ΠΡΡΠ°ΡΠΎΠΊ Π½Π° ΡΠΊΠ»Π°Π΄Π΅' = DATA NUMERIC[10,3] (Product);
reorderLevel 'ΠΠΎΡΠΌΠ° ΠΏΡΠΎΠ΄Π°ΠΆΠΈ' = DATA NUMERIC[10,3] (Product);
Zachibale
select s.CompanyName, p.ProductName, sum(od.Quantity) + p.ReorderLevel β p.UnitsInStock as ToOrder
from Orders o
join [Order Details] od on o.OrderID = od.OrderID
join Products p on od.ProductID = p.ProductID
join Suppliers s on p.SupplierID = s.SupplierID
where o.ShippedDate is null
group by s.CompanyName, p.ProductName, p.UnitsInStock, p.ReorderLevel
having p.UnitsInStock < sum(od.Quantity) + p.ReorderLevel
ntchito
orderedNotShipped 'ΠΠ°ΠΊΠ°Π·Π°Π½ΠΎ, Π½ΠΎ Π½Π΅ ΠΎΡΠ³ΡΡΠΆΠ΅Π½ΠΎ' (Product p) =
GROUP SUM quantity(OrderDetail d) IF product(d) = p;
toOrder 'Π Π·Π°ΠΊΠ°Π·Ρ' (Product p) = orderedNotShipped(p) + reorderLevel(p) - unitsInStock(p);
SELECT companyName(supplier(Product p)), name(p), toOrder(p) WHERE toOrder(p) > 0;
Vuto ndi nyenyezi
Ndipo chitsanzo chomaliza ndi cha ine ndekha. Pali malingaliro a malo ochezera a pa Intaneti. Anthu amatha kukhala paubwenzi komanso ngati wina ndi mnzake. Kuchokera pamawonekedwe a database yogwira ntchito zitha kuwoneka motere:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Ndikofunikira kupeza ofuna kukhala paubwenzi. Mwamwayi, muyenera kupeza anthu onse A, B, C kuti A ndi abwenzi ndi B, ndipo B ndi abwenzi ndi C, A amakonda C, koma A siabwenzi ndi C.
Kuchokera pamawonekedwe a database yogwira ntchito, funso limawoneka motere:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Owerenga akulimbikitsidwa kuthetsa vutoli mu SQL yekha. Zimaganiziridwa kuti pali anzanu ochepa kwambiri kuposa omwe mumakonda. Chifukwa chake ali m'magome osiyana. Ngati zikuyenda bwino, palinso ntchito yokhala ndi nyenyezi ziwiri. Mmenemo, ubwenzi siwofanana. Pa database yogwira ntchito zitha kuwoneka motere:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
(friends(a, b) OR friends(b, a)) AND
(friends(b, c) OR friends(c, b));
UPD: yankho ku vuto ndi nyenyezi yoyamba ndi yachiwiri kuchokera
SELECT
pl.PersonAID
,pf.PersonAID
,pff.PersonAID
FROM Persons AS p
--ΠΠ°ΠΉΠΊΠΈ
JOIN PersonRelationShip AS pl ON pl.PersonAID = p.PersonID
AND pl.Relation = 'Like'
--ΠΡΡΠ·ΡΡ
JOIN PersonRelationShip AS pf ON pf.PersonAID = p.PersonID
AND pf.Relation = 'Friend'
--ΠΡΡΠ·ΡΡ ΠΡΡΠ·Π΅ΠΉ
JOIN PersonRelationShip AS pff ON pff.PersonAID = pf.PersonBID
AND pff.PersonBID = pl.PersonBID
AND pff.Relation = 'Friend'
--ΠΡΡ Π½Π΅ Π΄ΡΡΠΆΠ°Ρ
LEFT JOIN PersonRelationShip AS pnf ON pnf.PersonAID = p.PersonID
AND pnf.PersonBID = pff.PersonBID
AND pnf.Relation = 'Friend'
WHERE pnf.PersonAID IS NULL
;WITH PersonRelationShipCollapsed AS (
SELECT pl.PersonAID
,pl.PersonBID
,pl.Relation
FROM #PersonRelationShip AS pl
UNION
SELECT pl.PersonBID AS PersonAID
,pl.PersonAID AS PersonBID
,pl.Relation
FROM #PersonRelationShip AS pl
)
SELECT
pl.PersonAID
,pf.PersonBID
,pff.PersonBID
FROM #Persons AS p
--ΠΠ°ΠΉΠΊΠΈ
JOIN PersonRelationShipCollapsed AS pl ON pl.PersonAID = p.PersonID
AND pl.Relation = 'Like'
--ΠΡΡΠ·ΡΡ
JOIN PersonRelationShipCollapsed AS pf ON pf.PersonAID = p.PersonID
AND pf.Relation = 'Friend'
--ΠΡΡΠ·ΡΡ ΠΡΡΠ·Π΅ΠΉ
JOIN PersonRelationShipCollapsed AS pff ON pff.PersonAID = pf.PersonBID
AND pff.PersonBID = pl.PersonBID
AND pff.Relation = 'Friend'
--ΠΡΡ Π½Π΅ Π΄ΡΡΠΆΠ°Ρ
LEFT JOIN PersonRelationShipCollapsed AS pnf ON pnf.PersonAID = p.PersonID
AND pnf.PersonBID = pff.PersonBID
AND pnf.Relation = 'Friend'
WHERE pnf.[PersonAID] IS NULL
Pomaliza
Zindikirani kuti chilankhulo chomwe chaperekedwa ndi chimodzi mwazosankha zogwiritsira ntchito lingaliro lomwe laperekedwa. SQL idatengedwa ngati maziko, ndipo cholinga chake chinali chakuti ikhale yofanana ndi momwe ingathere. Zachidziwikire, ena sangakonde mayina a mawu osakira, zolembera mawu, ndi zina. Chinthu chachikulu apa ndi lingaliro lokha. Ngati mungafune, mutha kupanga onse C ++ ndi Python mawu ofanana.
Lingaliro lofotokozedwa la database, m'malingaliro mwanga, lili ndi zabwino izi:
- tisaletse. Ichi ndi chisonyezo chokhazikika chomwe sichidziwika muzochitika zosavuta. Koma ngati muyang'ana milandu yovuta kwambiri (mwachitsanzo, mavuto ndi asterisks), ndiye, mwa lingaliro langa, kulemba mafunso otere ndikosavuta.
- ΠΠ½ΠΊΠ°ΠΏΡΡΠ»ΡΡΠΈΡ. Mu zitsanzo zina ndidalengeza zapakati (mwachitsanzo, kugulitsidwa, adagulidwa etc.), komwe ntchito zotsatila zidamangidwa. Izi zimakuthandizani kuti musinthe malingaliro a ntchito zina, ngati kuli kofunikira, popanda kusintha malingaliro omwe amadalira. Mwachitsanzo, mukhoza kupanga malonda kugulitsidwa adawerengedwa kuchokera kuzinthu zosiyana kotheratu, pomwe malingaliro ena onse sangasinthe. Inde, izi zitha kukhazikitsidwa mu RDBMS pogwiritsa ntchito CREATE VIEW. Koma ngati mfundo zonse zalembedwa motere, siziwoneka bwino.
- Palibe kusiyana kwa semantic. Dongosolo loterolo limagwira ntchito ndi makalasi (m'malo mwa matebulo ndi magawo). Monga momwe ziliri pamapulogalamu akale (ngati tikuganiza kuti njira ndi ntchito yokhala ndi gawo loyamba mu mawonekedwe a kalasi yomwe ili). Chifukwa chake, kuyenera kukhala kosavuta "kupanga mabwenzi" ndi zilankhulo zapadziko lonse lapansi. Kuphatikiza apo, lingaliro ili limalola magwiridwe antchito ovuta kwambiri kuti akhazikitsidwe. Mwachitsanzo, mutha kuphatikizira ogwira ntchito ngati:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'ΠΠ΅ΡΡ' MESSAGE 'Π§ΡΠΎ-ΡΠΎ ΠΠ΅ΡΡ ΠΏΡΠΎΠ΄Π°Π΅Ρ ΡΠ»ΠΈΡΠΊΠΎΠΌ ΠΌΠ½ΠΎΠ³ΠΎ ΠΎΠ΄Π½ΠΎΠ³ΠΎ ΡΠΎΠ²Π°ΡΠ° Π² 2019 Π³ΠΎΠ΄Ρ';
- Cholowa ndi polymorphism. Mu nkhokwe yogwira ntchito, mutha kuwonetsa zolowa zingapo kudzera mu CLASS ClassP: Class1, Class2 imapanga ndikukhazikitsa ma polymorphism angapo. Ndidzalemba momwe ndendende m'nkhani zamtsogolo.
Ngakhale ili ndi lingaliro chabe, takhazikitsa kale ku Java komwe kumamasulira malingaliro onse ogwira ntchito kukhala malingaliro ogwirizana. Kuphatikiza apo, malingaliro oyimira ndi zinthu zina zambiri zimalumikizidwa bwino, chifukwa chomwe timapeza zonse.
Source: www.habr.com