Когда вопрос заходит о хранении в БД гибких (заранее не известных, часто изменяемых) структур данных, разработчики обычно обращаются к «великому и ужасному» EAV-паттерну, либо к ныне модным NOSQL базам данных.
Не так давно такая задача стала и передо мной.
— EAV. Вызывает у меня стойкую неприязнь, да и сказано и написано об этом было очень много всего негативного (Кайт, Фаулер, Карвин, Горман). Главный минус в том, что при написании запросов приходится оперировать уже не реальными сущностями («Сотрудник», «Дом», «Клиент», то для чего и предназначен SQL), а объектами, орагнизованными на более низком уровне (извините за сумбур). Поэтому это был самый не желательный вариант.
— NOSQL. Поначалу очень заинтересовал этот вариант (в частности MongoDB). После продолжительного использования реляционок, первое время начинаешь испытывать чувство тотальной свободы, от кот��рого захватывает дыхание. Хранение документов любой структуры, моментальное создание новых коллекций, запросы к ним — красота! Но после непродолжительного использования эйфория начала спадать, а проблемы обнаруживаться:
— Бедный язык запросов (ИМХО) + отсутствие джойнов;
— Отсутствие схем (хорошая статья недавно была на эту тему (и не только на эту) habrahabr.ru/post/164361);
— Отсутствие встроенной поддержки ссылочной целостности;
— Отсутствие прибамбасов в виде хранимых процедур/функций, триггеров, представлений и многого другого.
— В моем приложении помимо данных с гибкой(изменяемой) структурой также необходимо хранить обычные статические данные — таблица пользователей, посещений, сотрудников и т.д. Работать с которыми (опять же имхо) гораздо проще и (самое главное) надежнее в обычной реляционной базе (та же самая ссылочная целостность и пр.).
Первую проблему (частично) я пытался решать с помощью ORM (это был Spring Data), он позволял писать сносные запросы к объектам, однако для этого нужно заранее создать и скомпилить все классы (соответствующие нужным коллекциям) и в запросах оперировать уже ими. Для меня это не подходило, т.к. коллекции должны создаваться и изменяться часто и оперативно — «на ходу».
Вторую — с помощью создания отдельной коллекции для хранения структур всех остальных коллекций, чтобы проверять корректность вводимых данных и т.д.
До решения остальных проблемм дело не дошло, бросил…
Уже на данном этапе моя база стала напоминать очень хрупкое сооружение, полностью зависящее от приложения, плюс я должен был реализовывать вручную многие вещи, которые большинство реляционок могут делать и так, из коробки. Может это и нормально, но как то не привык я к этому, как то не по себе стало.
Далее я задумался о том, как здорово было бы совместить реляционную и NOSQL СУБД. С одной стороны вся мощь реляционки со всеми прилагающимися, с другой — легкость и элегантность документоориентированного решения. И действительно, что мешает хранить объекты с гибкой структурой в некой отдельной специальной таблице (таблицах) например в формате xml, а обращаться к ним с помощью XPATH, тем более, что многие современные СУБД имеют развитые средства работы с XML (включая индексирование).
Решил попробовать на небольшом примере с использованием Postgresql, что из этого получится, как будут выглядеть запросы:
Для начала хватит двух служебных таблиц, думаю комментарии излишни:
Создаем две сущности для экспериметнов:
Подготовим две функции для генерации тестовых случайных данных (взяты на просторах Интернета):
Заполнение таблицы случайными данными, объектами классов «Клиент» и «Заказ» (связь один ко многим, каждый клиент сделал по пять заказов):
Первым запросом выберем максимальную стоимость заказа:
Запрос получился немного заковыристым, но все же вполне понятным: сразу понятно к какой сущности выпоняется запрос и по какому атрибуту. Как ни странно получился фулл скан, однако ничто не мешает п��строить индекс по атрибуту Cost:
И теперь запрос отрабатывает горазд быстрее и использует индекс:
Теперь попробуем выбрать информацию о заказах нескольких конкретных сотрудников, т.е. связку двух таблиц:
Ожидаемый фуллскан, теперь слегка проиндексируем:
Теперь получается веселее:
Этот запрос так же не потерял своей наглядности, однако его можно еще больше причесать: разобраться с преобразованием типов, оптимизировать xml структуру и т.д. Работы еше много, это просто небольшой пример.
Что можно сделать еще:
1. Гибкий поиск по атрибутам объектов любых классов;
2. Таблицу objects можно партицировать (хотябы частично), например хранить объекты больших классов физически отдельно.
Хранение данных в БД в формате xml естествеено не является новинкой, однако при поиске решения данного моего вопроса информации об этом было очень мало, не говоря уже о конкретных примерах. Надеюсь кому нибудь пригодиться или(и) услышать в комментариях отзывы и мнения людей, поработавших с похожей схемой.
Не так давно такая задача стала и передо мной.
— EAV. Вызывает у меня стойкую неприязнь, да и сказано и написано об этом было очень много всего негативного (Кайт, Фаулер, Карвин, Горман). Главный минус в том, что при написании запросов приходится оперировать уже не реальными сущностями («Сотрудник», «Дом», «Клиент», то для чего и предназначен SQL), а объектами, орагнизованными на более низком уровне (извините за сумбур). Поэтому это был самый не желательный вариант.
— NOSQL. Поначалу очень заинтересовал этот вариант (в частности MongoDB). После продолжительного использования реляционок, первое время начинаешь испытывать чувство тотальной свободы, от кот��рого захватывает дыхание. Хранение документов любой структуры, моментальное создание новых коллекций, запросы к ним — красота! Но после непродолжительного использования эйфория начала спадать, а проблемы обнаруживаться:
— Бедный язык запросов (ИМХО) + отсутствие джойнов;
— Отсутствие схем (хорошая статья недавно была на эту тему (и не только на эту) habrahabr.ru/post/164361);
— Отсутствие встроенной поддержки ссылочной целостности;
— Отсутствие прибамбасов в виде хранимых процедур/функций, триггеров, представлений и многого другого.
— В моем приложении помимо данных с гибкой(изменяемой) структурой также необходимо хранить обычные статические данные — таблица пользователей, посещений, сотрудников и т.д. Работать с которыми (опять же имхо) гораздо проще и (самое главное) надежнее в обычной реляционной базе (та же самая ссылочная целостность и пр.).
Первую проблему (частично) я пытался решать с помощью ORM (это был Spring Data), он позволял писать сносные запросы к объектам, однако для этого нужно заранее создать и скомпилить все классы (соответствующие нужным коллекциям) и в запросах оперировать уже ими. Для меня это не подходило, т.к. коллекции должны создаваться и изменяться часто и оперативно — «на ходу».
Вторую — с помощью создания отдельной коллекции для хранения структур всех остальных коллекций, чтобы проверять корректность вводимых данных и т.д.
До решения остальных проблемм дело не дошло, бросил…
Уже на данном этапе моя база стала напоминать очень хрупкое сооружение, полностью зависящее от приложения, плюс я должен был реализовывать вручную многие вещи, которые большинство реляционок могут делать и так, из коробки. Может это и нормально, но как то не привык я к этому, как то не по себе стало.
Далее я задумался о том, как здорово было бы совместить реляционную и NOSQL СУБД. С одной стороны вся мощь реляционки со всеми прилагающимися, с другой — легкость и элегантность документоориентированного решения. И действительно, что мешает хранить объекты с гибкой структурой в некой отдельной специальной таблице (таблицах) например в формате xml, а обращаться к ним с помощью XPATH, тем более, что многие современные СУБД имеют развитые средства работы с XML (включая индексирование).
Решил попробовать на небольшом примере с использованием Postgresql, что из этого получится, как будут выглядеть запросы:
Для начала хватит двух служебных таблиц, думаю комментарии излишни:
CREATE TABLE classes
(
id integer NOT NULL,
name text,
is_closed boolean,
obects_count integer,
CONSTRAINT classes_pk PRIMARY KEY (id )
);
CREATE TABLE objects
(
id integer NOT NULL,
body xml,
id_classes integer,
CONSTRAINT objects_pk PRIMARY KEY (id ),
CONSTRAINT classes_objects FOREIGN KEY (id_classes)
REFERENCES classes (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE INDEX fki_classes_objects
ON objects
USING btree
(id_classes );
Создаем две сущности для экспериметнов:
INSERT INTO classes(
id, name, is_closed, obects_count)
VALUES (1, 'customers', FALSE, 0);
INSERT INTO classes(
id, name, is_closed, obects_count)
VALUES (2, 'orders', FALSE, 0);
Подготовим две функции для генерации тестовых случайных данных (взяты на просторах Интернета):
CREATE OR REPLACE FUNCTION random(numeric, numeric)
RETURNS numeric AS
$BODY$
SELECT ($1 + ($2 - $1) * random())::numeric;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION random_string(length integer)
RETURNS text AS
$BODY$
declare
chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
result text := '';
i integer := 0;
begin
if length < 0 then
raise exception 'Given length cannot be less than 0';
end if;
for i in 1..length loop
result := result || chars[1+random()*(array_length(chars, 1)-1)];
end loop;
return result;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Заполнение таблицы случайными данными, объектами классов «Клиент» и «Заказ» (связь один ко многим, каждый клиент сделал по пять заказов):
DO $$
DECLARE
customer_pk integer;
order_pk integer;
BEGIN
FOR i in 1..10000 LOOP
customer_pk := nextval('objects_id_seq');
order_pk := nextval('objects_id_seq');
insert into objects (body, id_classes) values((
'<Customers>
<Customer>
<ID>' || customer_pk || '</ID>
<Name>' || random_string('10') || '</Name>
<Partners>' || random_string('10') || '</Partners>
</Customer>
</Customers>')::xml, 1);
for j in 1..5 LOOP
insert into objects (body, id_classes) values((
'<Orders>
<Order>
<ID>' || order_pk || '</ID>
<Customer_id>' || customer_pk || '</Customer_id>
<Cost>' || random(1, 1000) || '</Cost>
</Order>
</Orders>')::xml, 2);
end loop;
END LOOP;
END$$;
Первым запросом выберем максимальную стоимость заказа:
explain select max(((xpath('/Orders/Order/Cost/text()', O.body))[1])::text::float) as cost_of_order
from Objects O
where O.id_classes = 2;
/*
Aggregate (cost=2609.10..2609.11 rows=1 width=32)
-> Seq Scan on objects o (cost=0.00..2104.50 rows=50460 width=32)
Filter: (id_classes = 2)
*/
Запрос получился немного заковыристым, но все же вполне понятным: сразу понятно к какой сущности выпоняется запрос и по какому атрибуту. Как ни странно получился фулл скан, однако ничто не мешает п��строить индекс по атрибуту Cost:
create index obj_orders_cost_idx on objects using btree (((xpath('/Orders/Order/Cost/text()', body))[1]::text::float));
И теперь запрос отрабатывает горазд быстрее и использует индекс:
/*
Result (cost=0.15..0.16 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.15 rows=1 width=32)
-> Index Scan Backward using obj_orders_cost_idx on objects o (cost=0.00..7246.26 rows=50207 width=32)
Index Cond: ((((xpath('/Orders/Order/Cost/text()'::text, body, '{}'::text[]))[1])::text)::double precision IS NOT NULL)
Filter: (id_classes = 2)
*/
Теперь попробуем выбрать информацию о заказах нескольких конкретных сотрудников, т.е. связку двух таблиц:
explain select (xpath('/Customers/Customer/Name/text()', C.body))[1] as customer
, (xpath('/Orders/Order/Cost/text()', O.body))[1] as cost_of_order
from objects C
, objects O
where C.id_classes = 1
and O.id_classes = 2
and (xpath('/Orders/Order/Customer_id/text()', O.body))[1]::text::int = (xpath('/Customers/Customer/ID/text()', C.body))[1]::text::int
and ((xpath('/Customers/Customer/ID/text()' ,C.body))[1])::text::int between 1997585 and 1997595;
/*
Hash Join (cost=1873.57..6504.85 rows=12867 width=64)
Hash Cond: ((((xpath('/Orders/Order/Customer_id/text()'::text, o.body, '{}'::text[]))[1])::text)::integer = (((xpath('/Customers/Customer/ID/text()'::text, c.body, '{}'::text[]))[1])::text)::integer)
-> Seq Scan on objects o (cost=0.00..2104.50 rows=50460 width=32)
Filter: (id_classes = 2)
-> Hash (cost=1872.93..1872.93 rows=51 width=32)
-> Bitmap Heap Scan on objects c (cost=196.38..1872.93 rows=51 width=32)
Recheck Cond: (id_classes = 1)
Filter: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595))
-> Bitmap Index Scan on fki_classes_objects (cost=0.00..196.37 rows=10140 width=0)
Index Cond: (id_classes = 1)
*/
Ожидаемый фуллскан, теперь слегка проиндексируем:
create index obj_customers_id_idx on objects using btree (((xpath('/Customers/Customer/ID/text()', body))[1]::text::int));
create index obj_orders_id_idx on objects using btree (((xpath('/Orders/Order/ID/text()', body))[1]::text::int));
create index obj_orders_customerid_idx on objects using btree (((xpath('/Orders/Order/Customer_id/text()', body))[1]::text::int));
Теперь получается веселее:
/*
Hash Join (cost=380.52..5011.80 rows=12867 width=64)
Hash Cond: ((((xpath('/Orders/Order/Customer_id/text()'::text, o.body, '{}'::text[]))[1])::text)::integer = (((xpath('/Customers/Customer/ID/text()'::text, c.body, '{}'::text[]))[1])::text)::integer)
-> Seq Scan on objects o (cost=0.00..2104.50 rows=50460 width=32)
Filter: (id_classes = 2)
-> Hash (cost=379.88..379.88 rows=51 width=32)
-> Bitmap Heap Scan on objects c (cost=204.00..379.88 rows=51 width=32)
Recheck Cond: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595) AND (id_classes = 1))
-> BitmapAnd (cost=204.00..204.00 rows=51 width=0)
-> Bitmap Index Scan on obj_customers_id_idx (cost=0.00..7.35 rows=303 width=0)
Index Cond: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595))
-> Bitmap Index Scan on fki_classes_objects (cost=0.00..196.37 rows=10140 width=0)
Index Cond: (id_classes = 1)
*/
Этот запрос так же не потерял своей наглядности, однако его можно еще больше причесать: разобраться с преобразованием типов, оптимизировать xml структуру и т.д. Работы еше много, это просто небольшой пример.
Что можно сделать еще:
1. Гибкий поиск по атрибутам объектов любых классов;
2. Таблицу objects можно партицировать (хотябы частично), например хранить объекты больших классов физически отдельно.
Хранение данных в БД в формате xml естествеено не является новинкой, однако при поиске решения данного моего вопроса информации об этом было очень мало, не говоря уже о конкретных примерах. Надеюсь кому нибудь пригодиться или(и) услышать в комментариях отзывы и мнения людей, поработавших с похожей схемой.
