Donya database wis suwe didominasi dening DBMS relasional, sing nggunakake basa SQL. Dadi akeh varian sing muncul diarani NoSQL. Dheweke bisa ngukir papan tartamtu kanggo awake dhewe ing pasar iki, nanging DBMS relasional ora bakal mati, lan terus digunakake kanthi aktif kanggo tujuane.
Ing artikel iki aku pengin njlèntrèhaké konsep database fungsional. Kanggo pangerten sing luwih apik, aku bakal nindakake iki kanthi mbandhingake karo model relasional klasik. Masalah saka macem-macem tes SQL sing ditemokake ing Internet bakal digunakake minangka conto.
Pambuka
Database relasional beroperasi ing tabel lan kolom. Ing basis data fungsional, kelas lan fungsi bakal digunakake. Bidang ing tabel kanthi tombol N bakal dituduhake minangka fungsi paramèter N. Tinimbang hubungan antarane tabel, fungsi bakal digunakake sing ngasilake obyek saka kelas sing sambungan digawe. Komposisi fungsi bakal digunakake tinimbang JOIN.
Sadurunge pindhah langsung menyang tugas, aku bakal njlèntrèhaké tugas logika domain. Kanggo DDL aku bakal nggunakake sintaks PostgreSQL. Kanggo fungsional nduweni sintaks dhewe.
Tabel lan lapangan
Objek Sku sing prasaja kanthi jeneng lan kolom rega:
Relasional
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
Fungsional
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
We ngumumake loro fungsi, sing njupuk siji parameter Sku minangka input lan ngasilake jinis primitif.
Dianggep yen ing DBMS fungsional saben obyek bakal duwe sawetara kode internal sing digawe kanthi otomatis lan bisa diakses yen perlu.
Ayo nyetel rega kanggo produk / toko / supplier. Bisa uga owah saka wektu, mula ayo nambah kolom wektu ing tabel. Aku bakal ngliwati deklarasi tabel kanggo direktori ing basis data relasional kanggo nyepetake kode:
Relasional
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)
)
Fungsional
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Indeks
Kanggo conto pungkasan, kita bakal mbangun indeks ing kabeh tombol lan tanggal supaya bisa cepet nemokake rega kanggo wektu tartamtu.
Relasional
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
Fungsional
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
tugas
Ayo dadi miwiti karo masalah relatif prasaja dijupuk saka cocog ing Habr.
Pisanan, ayo ngumumake logika domain (kanggo database relasional iki ditindakake langsung ing artikel ing ndhuwur).
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);
Masalah 1.1
Tampilake dhaptar karyawan sing entuk gaji luwih gedhe tinimbang supervisor langsung.
Relasional
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
Fungsional
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Masalah 1.2
Dhaptar karyawan sing nampa gaji maksimal ing departemen
Relasional
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
Fungsional
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));
Loro-lorone implementasine padha. Kanggo kasus sing sepisanan, ing basis data relasional sampeyan bisa nggunakake CREATE VIEW, sing kanthi cara sing padha bakal ngetung gaji maksimum kanggo departemen tartamtu. Ing ngisor iki, kanggo kajelasan, aku bakal nggunakake kasus pertama, amarga luwih nggambarake solusi kasebut.
Masalah 1.3
Tampilake dhaptar ID departemen, jumlah karyawan sing ora ngluwihi 3 wong.
Relasional
select department_id
from employee
group by department_id
having count(*) <= 3
Fungsional
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Masalah 1.4
Tampilake dhaptar karyawan sing ora duwe manajer sing ditunjuk sing kerja ing departemen sing padha.
Relasional
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
Fungsional
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Masalah 1.5
Temokake dhaptar ID departemen kanthi total gaji karyawan maksimal.
Relasional
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 )
Fungsional
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();
Ayo pindhah menyang tugas sing luwih rumit saka liyane . Isine analisis rinci babagan carane ngleksanakake tugas iki ing MS SQL.
Masalah 2.1
Sing bakul sing adol luwih saka 1997 unit produk No. 30 ing taun 1?
Logika domain (kaya sadurunge ing RDBMS kita ngliwati deklarasi):
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);
Relasional
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
Fungsional
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;
Masalah 2.2
Kanggo saben panuku (jeneng, jeneng), temokake rong barang (jeneng) sing dituku paling akeh dhuwit ing taun 1997.
Kita ngluwihi logika domain saka conto sadurunge:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Relasional
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
Fungsional
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;
Operator PARTITION nggarap prinsip ing ngisor iki: nyimpulake ekspresi sing ditemtokake sawise SUM (kene 1), ing grup sing ditemtokake (kene Pelanggan lan Taun, nanging bisa dadi ekspresi apa wae), ngurutake ing grup kanthi ekspresi sing ditemtokake ing ORDER ( kene tuku, lan yen padha, banjur miturut kode produk internal).
Masalah 2.3
Pira barang sing kudu dipesen saka pemasok kanggo nepaki pesenan saiki.
Ayo nggedhekake logika domain maneh:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Relasional
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
Fungsional
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;
Masalah karo tanda bintang
Lan conto pungkasan saka kula pribadi. Ana logika jaringan sosial. Wong bisa kekancan karo saben liyane lan seneng saben liyane. Saka perspektif basis data fungsional bakal katon kaya iki:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Iku perlu kanggo golek calon bisa kanggo kekancan. Luwih formal, sampeyan kudu nemokake kabeh wong A, B, C kayata A kanca karo B, lan B kanca karo C, A seneng C, nanging A ora kanca karo C.
Saka perspektif basis data fungsional, pitakon bakal katon kaya iki:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Sing maca dianjurake kanggo ngatasi masalah iki ing SQL dhewe. Dianggep manawa ana kanca sing luwih sithik tinimbang wong sing disenengi. Mulane padha ing tabel kapisah. Yen sukses, ana uga tugas karo loro lintang. Ing kono, kekancan ora simetris. Ing basis data fungsional bakal katon kaya iki:
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: solusi kanggo masalah karo tanda bintang pisanan lan kaloro saka :
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
kesimpulan
Perlu dicathet yen sintaksis basa sing diwenehake mung minangka salah sawijining pilihan kanggo ngetrapake konsep sing diwenehake. SQL dijupuk minangka basis, lan goal iku kanggo dadi padha sabisa kanggo iku. Mesthi wae, sawetara bisa uga ora seneng karo jeneng tembung kunci, daftar tembung, lsp. Ingkang utama ing kene yaiku konsep kasebut dhewe. Yen dikarepake, sampeyan bisa nggawe sintaksis C ++ lan Python sing padha.
Konsep database sing diterangake, miturut pendapatku, nduweni kaluwihan ing ngisor iki:
- ease. Iki minangka indikator sing relatif subyektif sing ora katon ing kasus sing prasaja. Nanging yen sampeyan ndeleng kasus sing luwih rumit (contone, masalah karo tanda bintang), mula, miturut pendapatku, nulis pitakon kasebut luwih gampang.
- Инкапсуляция. Ing sawetara conto, aku nyatakake fungsi penengah (contone, didol, tuku etc.), saka ngendi fungsi sakteruse dibangun. Iki ngidini sampeyan ngganti logika fungsi tartamtu, yen perlu, tanpa ngganti logika sing gumantung ing. Contone, sampeyan bisa nggawe dodolan didol padha diwilang saka obyek temen beda, nalika liyane saka logika ora bakal ngganti. Ya, iki bisa ditindakake ing RDBMS nggunakake CREATE VIEW. Nanging yen kabeh logika ditulis kanthi cara iki, ora bakal katon banget diwaca.
- Ora ana kesenjangan semantik. Basis data kasebut ngoperasikake fungsi lan kelas (tinimbang tabel lan kolom). Kaya ing pemrograman klasik (yen kita nganggep yen metode minangka fungsi kanthi parameter pisanan ing wangun kelas sing diduweni). Mulane, mesthine luwih gampang "gawe kanca" karo basa program universal. Kajaba iku, konsep iki ngidini fungsi sing luwih rumit bisa ditindakake. Contone, sampeyan bisa masang operator kaya:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Warisan lan polimorfisme. Ing basis data fungsional, sampeyan bisa ngenalake macem-macem warisan liwat CLASS ClassP: Class1, Class2 mbangun lan ngleksanakake macem-macem polimorfisme. Aku mbokmenawa bakal nulis carane persis ing artikel mangsa.
Sanajan iki mung konsep, kita wis duwe sawetara implementasi ing Jawa sing nerjemahake kabeh logika fungsional menyang logika relasional. Kajaba iku, logika perwakilan lan akeh perkara liyane sing dipasang kanthi apik, amarga kita entuk kabeh. . Ateges, kita nggunakake RDBMS (mung PostgreSQL kanggo saiki) minangka "mesin virtual". Masalah kadhangkala muncul karo terjemahan iki amarga pangoptimal query RDBMS ora ngerti statistik tartamtu sing FDBMS ngerti. Ing teori, bisa ngetrapake sistem manajemen basis data sing bakal nggunakake struktur tartamtu minangka panyimpenan, diadaptasi khusus kanggo logika fungsional.
Source: www.habr.com