🥇Ntchito ya DBMS | Pulogalamu ya ProHoster

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 zolemba pa Habr.

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 zolemba. Lili ndi kusanthula mwatsatanetsatane momwe mungagwiritsire ntchito ntchitoyi mu MS SQL.

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 dss_kalika:

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. nsanja. Kwenikweni, timagwiritsa ntchito RDBMS (PostgreSQL yokha pakadali pano) ngati "makina enieni". Mavuto nthawi zina amawuka ndi kumasuliraku chifukwa chowonjezera mafunso cha RDBMS sadziwa ziwerengero zina zomwe FDBMS imadziwa. Mwachidziwitso, ndizotheka kukhazikitsa dongosolo la kasamalidwe ka database lomwe lidzagwiritse ntchito kamangidwe kake monga kusungirako, kusinthidwa kuti ikhale yogwira ntchito.

Source: www.habr.com

DBMS yogwira ntchito

Mau oyamba

Matebulo ndi minda

Zisonyezero

ntchito

Ntchito 1.1

Ntchito 1.2

Ntchito 1.3

Ntchito 1.4

Ntchito 1.5

Ntchito 2.1

Ntchito 2.2

Ntchito 2.3

Vuto ndi nyenyezi

Pomaliza

Kuwonjezera ndemanga kuletsa reply