C, PHP, VB, .NET

С MySQL 5.7.7 се появава още една сериозна добавка – виртуални колони в таблици. Друг възможен адекватен превод би бил „изчислими колони“ – такива, които се генерират динамично на базата на друга съществуваща информация. Нека разгледаме най-простия пример – дадена е следната таблица:

CREATE TABLE people(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) NOT NULL,
 password VARCHAR(255) NOT NULL,
 firstname VARCHAR(255) NOT NULL,
 lastname VARCHAR(255) NOT NULL
);

INSERT INTO people(username, password, firstname, lastname)
VALUES ("ivan", "123456", "Ivan", "Ivanov"),
       ("petar", "password", "Petar", "Petrov"),
       ("maria", "asdfqwer", "Maria", "Todorova");

Ако искаме да извадим пълното име на потребител с username „petar“, то би трябвало да направим следната заявка:

SELECT CONCAT(firstname, " ", lastname) AS full_name
FROM people
WHERE username = "petar";

Ако ни се налага често да вадим пълното име по този начин, очевидно ще имаме неудобството от доста дълъг и трудночетим SELECT. Един начин да се справим с този проблем е да използваме специално подготвено VIEW и да работим с него, вместо с таблица people (направете го). Другият начин е да се възползваме от новите виртуални колони, с които можем да добавим колона full_name в таблицата, която обаче не заема допълнително място, а данните се генерират динамично тогава, когато имаме нужда от тях:

ALTER TABLE PEOPLE
ADD COLUMN full_name VARCHAR(510) GENERATED ALWAYS
    AS (CONCAT(firstname, " ", lastname)) VIRTUAL;

Ще видите, че в таблицата ще се появи новата колона. Тя обаче няма да заема значимо дисково пространство, а данните от нея ще се генерират в момента на ползване. Важно е да се запомни, че тази колона не може да участва в INSERT и UPDATE заявки (ще се изпълни, но ще генерира warning) – по нея може само да се търси чрез SELECT.

Ключовата дума „VIRTUAL“ е незадължителна – тя ще бъде избрана по подразбиране. Силно непрепоръчителната алтернатива е „stored“, при която колоната вече няма да е виртуална, а данните ще бъдат записани в реална колона в базата от данни – по този начин тя ще се явява изчислимо поле и ще бъде нарушение на втора нормална форма.

Употребата на виртуалните колони е само за удобство при писане на SELECT заявки. Ето още един пример – дадена е таблица с продукти, в която записваме цена на продукта и бройки наличност в склада. Можем да добавим виртуална колона за обща сума на бройките на тези продукти:

CREATE TABLE products(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 price DECIMAL(6,2) NOT NULL,
 quantity INT UNSIGNED NOT NULL DEFAULT 0,
 total_stock_value DOUBLE(16,2)
       GENERATED ALWAYS AS (price*quantity)
);

От тук насетне можем да използваме въображението си. Например ако имаме база от данни за пътувания, в които записваме начална дата и час на пътуването, можем лесно да си запишем генерирани спрямо нея виртуални колони за година, месец, ден и час. Ако имате записана цена на пътуването в лева, можете лесно да генерирате цена в евро (курсът е фиксиран и затова е възможно).

Вероятно един често използван и много удобен пример би бил този, в който използваме виртуални колони за прикриване на нерелационни данни, като например XML или JSON. Нека го илюстрираме с пример – имаме таблица за потребители и имаме таблица с коментари, в които съдържанието се записва в XML формат.

CREATE TABLE users(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) NOT NULL,
 password VARCHAR(255) NOT NULL
);

INSERT INTO users(username, password) VALUES
("ivan", "123456"), ("petar", "password");

CREATE TABLE comments(
 content TEXT NOT NULL
);

INSERT INTO comments(content) VALUES
("<comment>
    <date>2016.12.16 00:20</date>
    <userid>1</userid>
    <text>Hello World from Ivan</text>
 </comment>"
),
("<comment>
    <date>2016.12.16 00:25</date>
    <userid>2</userid>
    <text>Hello World from Petar</text>
 </comment>"
);

Сега за да извадим коментарите заедно с потребителското име на потребител №1, ще трябва да се наложи да направим JOIN между двете таблици. Но понеже comments няма връзка с users, ще трябва да направим това чрез VIEW (тук го реализираме като вложена заявка в JOIN):

SELECT users.username, t.comment, t.cdate
FROM users
JOIN (SELECT ExtractValue(content,'/comment/userid') AS userid,
             ExtractValue(content,'/comment/text') AS comment,
             ExtractValue(content,'/comment/date') AS cdate
      FROM comments) AS t
   ON users.id = t.userid
WHERE users.id = 1;

или директно „ръчно“:

SELECT users.username, 
       ExtractValue(comments.content,'/comment/text') AS comment,
       ExtractValue(comments.content,'/comment/date') AS cdate
FROM users
JOIN comments 
  ON users.id = ExtractValue(comments.content,'/comment/userid')
WHERE users.id = 1;

Другият вариант за реализиране на JOIN е да се пази допълнителен външен ключ от comments към users, с което пратически се дублира информация в базата (не нещо фатално, но ако може да не се прави е по-добре, защото се предпазваме от аномалии). С наличието на виртуални таблици тази практика може да се промени по следния начин:

ALTER TABLE comments 
ADD COLUMN userid INT UNSIGNED
GENERATED ALWAYS AS (ExtractValue(content,'/comment/userid'));

Вече можем лесно да опростим SELECT заявката като:

SELECT users.username, 
       ExtractValue(comments.content,'/comment/text') AS comment,
       ExtractValue(content,'/comment/date') AS cdate
FROM users
JOIN comments ON users.id = comments.userid
WHERE users.id = 1;

Естествено няма проблем да си направите виртуални колони и за comment и за cdate – направете го!

Нека покажем същия пример, но реализиран чрез въведената с MySQL 5.7.8 функционалност за запазване на данни във формат JSON:

CREATE TABLE users_t(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) NOT NULL,
 password VARCHAR(255) NOT NULL
);

INSERT INTO users_t(username, password) VALUES
("ivan", "123456"), ("petar", "password");

CREATE TABLE comments_t(
 content JSON NOT NULL
);

INSERT INTO comments_t(content) VALUES
('{"date": "2016.12.16 00:20", "userid": 1, 
   "text": "Hello World from Ivan"}'
),
('{"date": "2016.12.16 00:25", "userid": 2, 
   "text": "Hello World from Petar"}'
);

Нормално бихме реализирали заявката по следния начин:

SELECT users_t.username,
       comments_t.content->"$.date" AS cdate,
       comments_t.content->"$.text" AS comment
FROM comments_t
JOIN users_t ON users_t.id = comments_t.content->"$.userid"
WHERE users_t.id = 1;

Ето как можем да „скрием“ цялото JSON парсване чрез виртуални колони, като тук дори за още по-голямо удобство ще превърнем датата към нормален MySQL DATETIME тип:

ALTER TABLE comments_t 
ADD COLUMN userid INT UNSIGNED
GENERATED ALWAYS AS (comments_t.content->"$.userid");,
ADD COLUMN cdate DATETIME
GENERATED ALWAYS AS (STR_TO_DATE(comments_t.content->"$.date",'"%Y.%m.%d %H:%i"')),
ADD COLUMN comment TEXT
GENERATED ALWAYS AS (comments_t.content->"$.text");

Вече можем да пишем по-познати чисто SQL заявки:

SELECT users_t.username, comments_t.cdate, comments_t.comment
FROM comments_t
JOIN users_t ON users_t.id = comments_t.userid
WHERE users_t.id = 1;

Впрочем по този начин направихме и още нещо, което ни помага – ние парсваме целия JSON масив още при INSERT заявката и по този начин се подсигуряваме, че сме въвели валиден формат. Проверете – ако се опитате да вкарате дата в различен формат или userid, което не е число, MySQL ще ви спре със съобщение за грешка „Incorrect datetime value: … for function str_to_date“ или „Invalid JSON value for CAST to INTEGER from column json_extract“. Или в този случай виртуалните колони могат да служат за валидиране на формат на данни при INSERT.

Друг възможен интересен вариант за употреба е чрез виртуални колони да се симулира CHECK! Това може да се получи като направим колоната NOT NULL, а после направим така, че при неизпълнено условие се връща NULL:

CREATE TABLE employees(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 firstname VARCHAR(255) NOT NULL,
 lastname VARCHAR(255) NOT NULL,
 salary DECIMAL(8,2) NOT NULL,
 sal_chk CHAR(1) GENERATED ALWAYS AS(
                   CASE WHEN salary>=460.00
                   THEN 'Y' 
                   END
                 ) NOT NULL
);

Идеята е, че ако условието е изпълнено, ще се върне една буква, а ако не е, ще генерира NULL, което не е валидна стойност (колоната sal_chk е декларирана накрая като NOT NULL). Пробвайте – ако вмъквате редове със заплата над 460.00, ще се вкарват коректно, а с по-малка ще има съобщение за грешка. Неприятното, спрямо другите алтернативи за симулиране на CHECK не е само типовото и неясно съобщение за грешка, но и това, че с този метод не трябва да вкарвате повече от един ред накуп (ако един се провали, цялата заявка става невалидна и нито един ред не се вмъква). Също така имайте предвид, че този метод няма да работи с алтернативната на MySQL СУБД MariaDB – при нея няма възможност за NOT NULL върху генерирана колона. Друг недостатък е, че не може да съдържа вложени заявки. Все пак техниката е доста по-проста от тригерите и определено е по-удачен заместител на симулирането на CHECK с VIEW.

В заключение виждаме, че най-често виртуалните колони се явяват като заместител на VIEW. Може да се каже, че те до известна степен създават хибрид между нормална таблица и VIEW. Това е удобно, защото намалява общия брой таблици в базата от данни (виртуалните не се запазват със собствено име).