O le lalolagi o faʻamaumauga ua leva ona pulea e DBMS faʻafesoʻotaʻi, lea e faʻaaogaina le gagana SQL. O lea la, o suiga fou ua taʻua o le NoSQL. Na mafai ona latou vaneina se nofoaga patino mo i latou lava i lenei maketi, ae o le DBMSs sootaga o le a le oti, ma faʻaauau pea ona faʻaaogaina mo latou faamoemoega.
I totonu o lenei tusiga ou te manaʻo e faʻamatalaina le manatu o se faʻamaumauga galue. Mo se malamalama sili atu, o le a ou faia lenei mea e ala i le faʻatusatusaina ma le faʻataʻitaʻiga masani masani. Faʻafitauli mai suʻega SQL eseese o loʻo maua i luga ole Initaneti ole a faʻaaogaina e fai ma faʻataʻitaʻiga.
Faatomuaga
O feso'ota'iga fa'amaumauga o lo'o galue i luga o laulau ma fanua. I totonu o faʻamaumauga faʻamaumauga, vasega ma galuega tauave o le a faʻaaogaina, i le faasologa. O se fanua i totonu o se laulau ma N ki o le a fa'atusalia o se galuega a N parakalafa. Nai lo sootaga i le va o laulau, o le a faʻaaogaina galuega e toe faʻafoʻi ai mea faitino o le vasega lea e fai ai le fesoʻotaʻiga. O le a fa'aogaina le tu'ufa'atasiga o galuega nai lo le SOI.
Aʻo leʻi alu saʻo i galuega, o le a ou faʻamatalaina le galuega ole domain logic. Mo DDL o le a ou faʻaogaina le PostgreSQL syntax. Mo le fa'atinoga o lo'o i ai lana lava syntax.
Laupapa ma fanua
Se mea faigofie Sku ma igoa ma tau fanua:
Fegalegaleaiga
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
Faagaioia
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Matou te faasilasila atu lua galuega tauave, lea e ave ai se parakalafa e tasi Sku e fai ma fa'aulu ma toe fa'afo'i mai se ituaiga muamua.
E fa'apea i totonu o se DBMS fa'atino o mea ta'itasi o le ai ai ni fa'ailoga i totonu e otometi lava ona fa'atupuina ma e mafai ona maua pe a mana'omia.
Tatou seti le tau mo le oloa/faleoloa/fa'atau. Atonu e suia i le aluga o taimi, o lea se'i o tatou fa'aopoopo se fanua taimi i le laulau. O le a ou misia le taʻuina atu o laulau mo faʻamaumauga i totonu o se faʻamaumauga faʻamaumauga e faʻapuupuu ai le code:
Fegalegaleaiga
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)
)
Faagaioia
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Faasino igoa
Mo le faʻataʻitaʻiga mulimuli, o le a matou fausia se faʻailoga i luga o ki uma ma le aso ina ia mafai ai ona vave maua le tau mo se taimi patino.
Fegalegaleaiga
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
Faagaioia
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
Galuega
Sei o tatou amata i faʻafitauli faigofie e maua mai le fetaui i luga o Habr.
Muamua, se'i o tatou fa'ailoa atu le fa'atatau o le domain (mo le fa'amaumauga tu'ufa'atasiga o lo'o faia sa'o i le tusiga o lo'o i luga).
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);
Galuega 1.1
Faaali atu se lisi o tagata faigaluega e maua se totogi e sili atu nai lo le latou supavaisa.
Fegalegaleaiga
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
Faagaioia
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Galuega 1.2
Lisi tagata faigaluega e maua le maualuga o totogi i la latou matagaluega
Fegalegaleaiga
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
Faagaioia
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));
E tutusa uma faatinoga. Mo le mataupu muamua, i totonu o se faʻamaumauga tuʻufaʻatasiga e mafai ona e faʻaogaina CREATE VIEW, lea i le auala lava e tasi o le a muamua faʻatatau le totogi maualuga mo se matagaluega faʻapitoa i totonu. I le mea o loʻo mulimuli mai, mo le manino, o le a ou faʻaaogaina le mataupu muamua, talu ai e sili atu ona atagia ai le fofo.
Galuega 1.3
Faaali atu se lisi o ID matagaluega, le numera o tagata faigaluega e le sili atu i le 3 tagata.
Fegalegaleaiga
select department_id
from employee
group by department_id
having count(*) <= 3
Faagaioia
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Galuega 1.4
Faaali atu se lisi o tagata faigaluega e leai se pule tofia e faigaluega i le matagaluega lava e tasi.
Fegalegaleaiga
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
Faagaioia
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Galuega 1.5
Su'e se lisi o ID matagaluega o lo'o iai le aofa'iga maualuga o totogi o tagata faigaluega.
Fegalegaleaiga
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 )
Faagaioia
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();
Se'i o tatou aga'i atu i galuega faigata mai le isi . O loʻo i ai se auiliiliga auiliili o le auala e faʻatino ai lenei galuega ile MS SQL.
Galuega 2.1
O ai na fa'atau atu na fa'atau atu le silia ma le 1997 iunite o oloa Numera 30 i le 1?
Logic domain (pei o le taimi muamua i luga o le RDBMS matou te faamisi le taʻutinoga):
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);
Fegalegaleaiga
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
Faagaioia
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;
Galuega 2.2
Mo tagata faʻatau taʻitasi (igoa, igoa), suʻe oloa e lua (igoa) na faʻaalu ai e le tagata faʻatau le tele o tupe i le 1997.
Matou te faʻalauteleina le faʻaogaina o le domain mai le faʻataʻitaʻiga muamua:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Fegalegaleaiga
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
Faagaioia
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;
O le PARTITION operator e galue i luga o le mataupu faavae: o loʻo faʻaputuina le faʻamatalaga faʻamaonia pe a uma le SUM (i'inei 1), i totonu o vaega faʻapitoa (i'inei Tagata Faʻatau ma le Tausaga, ae mafai ona avea ma faʻamatalaga), faʻavasega i totonu o vaega e ala i faʻamatalaga faʻamaonia i le ORDER ( iinei faʻatau, ma afai e tutusa, ona tusa lea ma le tulafono o oloa i totonu).
Galuega 2.3
E fia ni oloa e mana'omia ona oka mai le au fa'atau oloa e fa'ataunu'u ai oka o lo'o iai nei.
Se'i o tatou toe fa'alautele le fa'atatau o le domain:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Fegalegaleaiga
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
Faagaioia
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;
Fa'afitauli ile fetu
Ma o le faataitaiga mulimuli e mai ia te au lava ia. O loʻo i ai le manatu o se fesoʻotaʻiga lautele. E mafai e tagata ona faauo le tasi i le isi ma fiafia le tasi i le isi. Mai se va'aiga fa'amaumauga tu'ufa'atasiga e foliga fa'apenei:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
E manaʻomia le suʻeina o ni sui tauva mo faauoga. I se tulaga faʻapitoa, e tatau ona e suʻeina tagata uma A, B, C e faʻapea o A e faauo ma B, ma B o uo ma C, A e fiafia ia C, ae A e le o se uo ma C.
Mai se va'aiga fa'amaumauga tu'ufa'atasiga, o le fesili e pei o lenei:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
E faʻamalosia le tagata faitau e foia lenei faʻafitauli ile SQL na o ia. E fa'apea e to'aitiiti au uo nai lo tagata e te fiafia i ai. O lea la ua i ai i laulau eseese. Afai e manuia, e iai foi se galuega e lua fetu. I totonu, o faigauo e le tutusa. I luga o se faʻamaumauga faʻamaumauga e foliga faʻapea:
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: fofo ile fa'afitauli ile fa'ailoga muamua ma le lua mai :
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
iʻuga
E tatau ona maitauina o le gagana tu'ufa'atasiga ua na'o se tasi o filifiliga mo le fa'atinoina o le manatu. Sa avea le SQL ma faavae, ma o le sini ia tutusa lelei ma ia. Ioe, o nisi atonu e le fiafia i igoa o upu autu, tusi resitala, ma isi. O le mea autu iinei o le manatu lava ia. Afai e manaʻo ai, e mafai ona e faia uma le C++ ma le Python syntax tutusa.
O le faʻamatalaga faʻamatalaga faʻamatalaga, i loʻu manatu, ei ai tulaga lelei nei:
- fīlēmū. O se fa'ailoga fa'apitoa lea e le'o iloa i mataupu faigofie. Ae afai e te vaʻavaʻai i mataupu sili atu ona faigata (mo se faʻataʻitaʻiga, faʻafitauli i asterisk), ona, i loʻu manatu, o le tusiaina o ia fesili e sili atu ona faigofie.
- Faʻamatalaga. I nisi o faʻataʻitaʻiga na ou faʻaalia galuega faʻapitoa (mo se faʻataʻitaʻiga, faʻatau, faʻatau ma isi), lea na fausia ai galuega mulimuli ane. O lenei mea e mafai ai e oe ona suia le faʻaogaina o nisi o galuega, pe a manaʻomia, e aunoa ma le suia o le faʻaogaina o i latou e faʻalagolago ia i latou. Mo se faʻataʻitaʻiga, e mafai ona e faia faʻatau faʻatau na fa'atatauina mai mea 'ese'ese atoa, a'o le a le suia le isi vaega o manatu. Ioe, e mafai ona faʻatinoina i se RDBMS faʻaaoga CREATE VIEW. Ae afai e tusia uma manatu i le auala lenei, o le a le foliga sili ona faitau.
- Leai se vase semantic. O sea fa'amaumauga o lo'o galue i luga o galuega ma vasega (nai lo laulau ma fanua). E pei lava o polokalame masani (pe a tatou manatu o se metotia o se galuega ma le parakalafa muamua i le tulaga o le vasega o loʻo i ai). E tusa ai, e tatau ona sili atu ona faigofie le "faʻauo" i gagana faʻalapotopotoga lautele. E le gata i lea, o lenei manatu e mafai ai ona faʻatinoina galuega sili atu ona faigata. Mo se faʻataʻitaʻiga, e mafai ona e faʻapipiʻi faʻalapotopotoga e pei o:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Tofi ma polymorphism. I totonu o se faʻamaumauga faʻamaumauga, e mafai ona e faʻafeiloaʻi le tele o tofi e ala i le CLASS ClassP: Class1, Class2 fausia ma faʻatino le tele o polymorphism. Atonu o le a ou tusia pe faʻapefea tonu i tala o lumanaʻi.
E ui lava o lenei mea ua na o se manatu, ua uma ona i ai se faʻatinoga i Java e faʻaliliuina uma le faʻaogaina o le faʻaogaina i le fesoʻotaʻiga fesoʻotaʻiga. E le gata i lea, o le faʻaogaina o faʻamatalaga ma le tele o isi mea e faʻapipiʻi matagofie i ai, faʻafetai tatou te maua atoa . O le mea moni, matou te faʻaaogaina le RDBMS (mo le taimi nei naʻo PostgreSQL) o se "masini virtual". O nisi taimi e tula'i mai ai fa'afitauli i lenei fa'aliliuga ona e le o iloa e le tagata su'esu'e fesili a le RDBMS ni fa'amaumauga patino e iloa e le FDBMS. I le aʻoaʻoga, e mafai ona faʻatinoina se faʻaogaina o faʻamaumauga o faʻamaumauga o le a faʻaogaina ai se fausaga faʻapitoa e pei o le teuina, faʻapitoa mo le faʻaogaina o manatu.
puna: www.habr.com