The world of databases has long been dominated by relational DBMS, which use SQL. It has become so entrenched that emerging varieties are referred to as NoSQL. They have managed to carve out a niche in this market, but relational DBMS are not going away and continue to be actively used for their purposes.
In this article, I want to describe the concept of a functional database. For better understanding, I will do this by comparing it with the classical relational model. Examples will include tasks from various SQL tests found online.
Introduction
Relational databases operate on tables and fields. In a functional database, classes and functions respectively replace these. A field in a table with N keys will be represented as a function of N parameters. Instead of relationships between tables, functions will be used that return instances of the class being referenced. JOIN will be replaced with function composition.
Before moving directly to the tasks, I will describe the domain logic assignment. For DDL, I will use PostgreSQL syntax. For functional, I will use my own syntax.
Tables and fields
A simple Sku object with fields for name and price:
Relational
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
Functional
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
We declare two functions, which take a single Sku parameter as input and return a primitive type.
It is assumed that in a functional DBMS, each object will have an internal code that is generated automatically and can be referenced as needed.
We will set the price for the product/store/supplier. It may change over time, so we will add a time field to the table. I will skip the table declarations for references in the relational database to shorten the code:
Relational
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)
)
Functional
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Indexes
For the last example, we will build an index on all keys and the date so that we can quickly find the price at a specific time.
Relational
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
Functional
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
Since I have already learned to "somewhat" port QEMU to JavaScript, this time it was decided to do it wisely and not repeat past mistakes.
Let's start with relatively simple tasks drawn from the corresponding on Habr.
First, we will declare the domain logic (for the relational database, this is done directly in the article provided).
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);
Task 1.1
Display a list of employees who earn more than their immediate supervisor.
Relational
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
Functional
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Task 1.2
Display a list of employees earning the highest salary in their department.
Relational
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
Functional
maxSalary 'Maximum salary' (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));
Both implementations are equivalent. In the first case for a relational database, you can use CREATE VIEW, which will similarly calculate the maximum salary for a specific department first. For clarity, I will use the first case as it better represents the solution.
Task 1.3
Display a list of department IDs where the number of employees is no more than 3.
Relational
select department_id
from employee
group by department_id
having count(*) <= 3
Functional
countEmployees 'Number of Employees' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Task 1.4
Display a list of employees without an assigned supervisor who works in the same department.
Relational
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
Functional
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Task 1.5
Find a list of department IDs with the highest total salary of employees.
Relational
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 )
Functional
salarySum 'Maximum Salary' (Department d) =
GROUP SUM salary(Employee e) IF department(e) = d;
maxSalarySum 'Maximum Salary of Departments' () =
GROUP MAX salarySum(Department d);
SELECT Department d WHERE salarySum(d) = maxSalarySum();
Let's move on to more complex tasks from another . It provides a detailed analysis of how to implement this task in MS SQL.
Task 2.1
Which salespeople sold more than 30 units of product No. 1 in 1997?
Domain logic (like before, we skip the declaration on RDBMS):
CLASS Employee 'Seller';
lastName 'Last Name' = DATA STRING[100] (Employee);
CLASS Product 'Product';
id = DATA INTEGER (Product);
name = DATA STRING[100] (Product);
CLASS Order 'Order';
date = DATA DATE (Order);
employee = DATA Employee (Order);
CLASS Detail 'Order line';
order = DATA Order (Detail);
product = DATA Product (Detail);
quantity = DATA NUMERIC[10,5] (Detail);
Relational
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
Functional
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;
Task 2.2
For each customer (first name, last name), find two products (name) that the customer spent the most money on in 1997.
Expanding the domain logic from the previous example:
CLASS Customer 'Client';
contactName 'Full Name' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Relational
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
Functional
sum(Detail d) = quantity(d) * unitPrice(d) * (1 - discount(d));
bought '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 '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;
The PARTITION operator works as follows: it sums the expression specified after SUM (in this case 1) within the specified groups (here Customer and Year, but it can be any expression), sorting within groups by the expressions specified in ORDER (in this case bought, and if equal, by the internal product code).
Task 2.3
How many products need to be ordered from suppliers to fulfill current orders.
We are again expanding the domain logic:
CLASS Supplier 'Supplier';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Stock Remaining' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Sales Norm' = DATA NUMERIC[10,3] (Product);
Relational
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
Functional
Ordered but not shipped (Product p) =
GROUP SUM quantity(OrderDetail d) IF product(d) = p;
toOrder 'To order' (Product p) = orderedNotShipped(p) + reorderLevel(p) - unitsInStock(p);
SELECT companyName(supplier(Product p)), name(p), toOrder(p) WHERE toOrder(p) > 0;
Star Task
And the last example is personally from me. There is a social network logic. People can be friends with each other and like each other. From the perspective of the functional database, it would look like this:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
It is necessary to find possible friendship candidates. More formally, we need to find all people A, B, C such that A is friends with B, B is friends with C, A likes C, but A is not friends with C.
From the perspective of the functional database, the query would look like this:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
The reader is invited to independently solve this task in SQL. It is assumed that friends are much fewer than those who are liked. Therefore, they are in separate tables. In the case of successful resolution, there is also a task with two stars. In it, friendship is not symmetrical. From the functional database perspective, this would look like:
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: solution to the task with the first and second stars from :
SELECT
pl.PersonAID
,pf.PersonAID
,pff.PersonAID
FROM Persons AS p
--Likes
JOIN PersonRelationShip AS pl ON pl.PersonAID = p.PersonID
AND pl.Relation = 'Like'
--Friends
JOIN PersonRelationShip AS pf ON pf.PersonAID = p.PersonID
AND pf.Relation = 'Friend'
--Friends of Friends
JOIN PersonRelationShip AS pff ON pff.PersonAID = pf.PersonBID
AND pff.PersonBID = pl.PersonBID
AND pff.Relation = 'Friend'
--Not Friends
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
--Likes
JOIN PersonRelationShipCollapsed AS pl ON pl.PersonAID = p.PersonID
AND pl.Relation = 'Like'
--Friends
JOIN PersonRelationShipCollapsed AS pf ON pf.PersonAID = p.PersonID
AND pf.Relation = 'Friend'
--Friends of Friends
JOIN PersonRelationShipCollapsed AS pff ON pff.PersonAID = pf.PersonBID
AND pff.PersonBID = pl.PersonBID
AND pff.Relation = 'Friend'
--Not Friends
LEFT JOIN PersonRelationShipCollapsed AS pnf ON pnf.PersonAID = p.PersonID
AND pnf.PersonBID = pff.PersonBID
AND pnf.Relation = 'Friend'
WHERE pnf.[PersonAID] IS NULL
Conclusion
It should be noted that the provided syntax is just one of the possible implementations of the given concept. The basis was SQL, and the goal was to make it as similar to it as possible. Of course, some may dislike the naming of keywords, the case of words, and so on. Here, the main point is actually the concept itself. If desired, similar syntax can be made in C++ or Python.
The described database concept, in my opinion, has the following advantages:
- Simplicity. This is a relatively subjective measure that is not obvious in simple cases. But if we look at more complex cases (for example, tasks with stars), it seems to me that writing such queries is significantly easier.
- Encapsulation. In some examples, I declared intermediate functions (for example, sold, bought etc.), from which the subsequent functions were built. This allows changing the logic of certain functions when necessary without changing the logic of the functions dependent on them. For example, it's possible to make sales sold they were considered from completely different objects, while the remaining logic will remain unchanged. Yes, this can be implemented in RDBMS using CREATE VIEW. However, if all the logic is written this way, it will look quite unreadable.
- Lack of semantic gap. Such a database operates with functions and classes (instead of tables and fields). Just like in classical programming (if we consider a method as a function with the first parameter being the class to which it belongs). Accordingly, it should be much easier to "tame" it with universal programming languages. Furthermore, this concept allows for much more complex functions to be implemented. For example, it is possible to embed operators of the type into the database:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Petya' MESSAGE 'Petya is selling too much of one product in 2019';
- Inheritance and polymorphism. In a functional database, multiple inheritance can be introduced through constructs like CLASS ClassP: Class1, Class2 and multiple polymorphism can also be implemented. How exactly, I may write in subsequent articles.
Despite the fact that this is just a concept, we already have some implementation in Java that translates all functional logic into relational logic. Additionally, it is beautifully integrated with view logic and much more, resulting in a whole . Essentially, we are using RDBMS (currently only PostgreSQL) as a "virtual machine". During such translation, problems sometimes arise since the query optimizer of the RDBMS lacks specific statistics that the FDBMS knows. In theory, it is possible to implement a database management system that will use some structure adapted specifically for functional logic as storage.
Source: habr.com
