Perfect directory implementation

article "Perfect directory, sketch of architecture" I will show with examples how you can use the suggested structure of the database to store arbitrary data and execute arbitrary searches on the data. The scripts are in the repository — universal_data_catalog_idea.

I invite under kat, those who are interested to see those scripts with the author's comments.

the

Content repository

In the repository a complete set of infrastructure scripts:

the
1. create tables
the
2. fill data;
the
3. empty tables from data;
the
4. delete all the created tables.

When I started writing the scripts, their volume was small, but when I got to create the test sets, the amount of code has grown to 1000+ lines. Such large scripts to insert into the article it seems to me excessive, so if you want to touch "live" data, then clone the repository and see how it turns out in life.

In the database schema, I made small changes — editors (redactor_id) carried out content (content), now the content itself, the editors themselves.

the

highlights of data organization

The system itself does not dictate the rules of use, the logic can be applied to any.

The main purpose of this search, data warehousing is a necessary but nevertheless secondary features.

You can keep any data of any configuration. Directory information consists of the Entity (item) and their Values (content). Each Value is the value of specific Characteristics (property). Entities with the same set of Characteristics can be combined into a Rubric (rubric).

An entity can belong to one Category, maybe several, in the illustrated embodiment is only one.

the

Adding data (storage)

How to add data to the catalogue can be viewed in the \deploy\commit_dml.sql. This sequence:

the
1. create Rubrics (rubric);
the
2. if necessary the hierarchy of Headings, to create a Hierarchy (element_tree) and distribute the rubric of hierarchy (rubric_element_tree);
the
3. to add the properties (property);
the
4. if you intend to use features in accordance with any of the rules, it is possible to add Options to these rules (tag) and properly attach the Features and Options (property_tag);
the
5. to assign the Headings Characteristics (rubric_property);
the
6. to add an Entity (item);
the
7. to group Entities by category (rubric_item);
the
8. add the Value (content) for the Characteristics;
the
9. attach Values to Entities (item_content);
the
10. if it is assumed that the Values will be multiple editors, then add the Editor (redactor) and assign the values of the Editors (redactor_content);
the
11. if you intend to not only a string search, it to convert user input (content.raw) to a specific data type and record data in a table (date_matter.date_time, digital_matter.digital, duration_matter.duration string_matter.string);

The list went long, but in fact all the steps are stacked in two steps:

the
1. to add an Entity;
the
2. set Values;

The rest of the steps as needed. With the theory of information storage figured out. Now the practical application.

the

the Practical content of the directory

Let's say we want to make our own "Craigslist" for sale excavators.
To do this, we add root category "Excavators," and her two daughter "career Excavators and Backhoe loaders".
For the section "Excavators career" assigned properties:
"Product model";
"Brand";
"Bucket capacity";
"the price of goods in rubles,";
"a unit of measure for the product item";
For the section "Backhoe loaders" assigned to the same set of properties plus the property "Capacity shovels".
Features:
"Model articles",
"Trademark"
"Capacity of bucket",
"The capacity of a shovel"
"Bucket capacity" and "Capacity shovels" is a numeric data — Option "DIGITAL_DATA_TYPE", the search will search the range of values is "BETWEEN_SEARCH_TYPE".
"Product model" and "brand" is a string data — "STRING_DATA_TYPE" search by Characteristics "brand" is as a directory of producers — transfer — "ENUMERATION_SEARCH_TYPE", according to the Characteristics of "Model articles" will search for occurrence of a substring — "LIKE_SEARCH_TYPE".

Score of the Essence and Values. Now you can perform a General search.

the

General search

General search means a search everywhere, everywhere restricted to our Topics and Entities, in principle it is possible to search also in Values, since they store user input in string format. Search everywhere is the search substring in any columns? Apparently only the "title" and "description".

Go!

the

-- Search "everywhere" ( in the headings and in the catalog items )
SELECT
'RUBRIC',
rr.code
rr.title
rr.description
FROM rubric rr
WHERE
(rr.title ILIKE '%' || :SEARCH_PATTERN || '%'
OR rr.description ILIKE '%' || :SEARCH_PATTERN || '%')
AND EXISTS
(
SELECT NULL
FROM
(
WITH RECURSIVE road_map ( id, element_tree_id, code, horizont ) AS
(
SELECT
cet.id AS id,
cet.element_tree_id AS element_tree_id,
r.code AS code,
0 AS horizont
FROM
element_tree cet
LEFT JOIN rubric_element_tree ret
ON cet.id = ret.element_tree_id
Rubric r LEFT JOIN
ON ret.rubric_id = r.id
WHERE r.code = rr.code
UNION
SELECT
pet.id
pet.element_tree_id,
r.code
horizont + 1
FROM
element_tree pet
JOIN road_map c
ON (c.element_tree_id = pet.id)
LEFT JOIN rubric_element_tree ret
ON pet.id = ret.element_tree_id
Rubric r LEFT JOIN
ON ret.rubric_id = r.id
)
SELECT NULL
FROM
road_map rm
WHERE
rm.code = :CATALOG_ROOT
ORDER BY
horizont DESC
LIMIT 1
) R
)
UNION
SELECT
'ITEM',
i.code
i.title
i.description
FROM
rubric rr
JOIN rubric_item ri
ON rr.id = ri.rubric_id
JOIN item i
ON ri.item_id = i.id
WHERE
(i.title ILIKE '%' || :SEARCH_PATTERN || '%'
OR i.description ILIKE '%' || :SEARCH_PATTERN || '%')
AND EXISTS
(
SELECT NULL
FROM
(
WITH RECURSIVE road_map ( id, element_tree_id, code, horizont ) AS
(
SELECT
cet.id AS id,
cet.element_tree_id AS element_tree_id,
r.code AS code,
0 AS horizont
FROM
element_tree cet
LEFT JOIN rubric_element_tree ret
ON cet.id = ret.element_tree_id
Rubric r LEFT JOIN
ON ret.rubric_id = r.id
WHERE r.code = rr.code
UNION
SELECT
pet.id
pet.element_tree_id,
r.code
horizont + 1
FROM
element_tree pet
JOIN road_map c
ON (c.element_tree_id = pet.id)
LEFT JOIN rubric_element_tree ret
ON pet.id = ret.element_tree_id
Rubric r LEFT JOIN
ON ret.rubric_id = r.id
)
SELECT NULL
FROM
road_map rm
WHERE
rm.code = :CATALOG_ROOT
ORDER BY
horizont DESC
LIMIT 1
) R
);

the

request Parameters

Root category

the

:CATALOG_ROOT

Code root Rubric (rubric.code), is used to limit the search, you can search in the "Diggers" at all, and can only search in a career, or can search the products in General, but only in excavators.

Determining whether another object is in the ancestors of the specified Category occurs via a hierarchical query is specific for each DBMS.

You can search without restriction on the area, if we added to our catalogue services (e.g. rental of excavators) and the summary (e.g. excavator operator) and looking for the substring "excavator", we have the results to you and the position of the excavators and the position of the Pro excavator driver.

search String

the

:SEARCH_PATTERN

The search string is actually the substring which will search, the search string split by spaces (or any other separator) and look for with the condition:

the

WHERE
(i.title ILIKE '%' || :PATTERN_PART1|| '%'
OR i.description ILIKE '%' || :PATTERN_PART1 || '%')
AND 
(i.title ILIKE '%' || :PATTERN_PART2|| '%'
OR i.description ILIKE '%' || :PATTERN_PART2|| '%')
-- as many another parts of the search string
AND 
(i.title ILIKE '%' || :PATTERN_PART_N|| '%'
OR i.description ILIKE '%' || :PATTERN_PART_N|| '%')

The script certainly looks giant, but it is not written in the manual, it says our application, and the DBMS will swallow any script, so no need to look at the number of letters is not significant.
For example with the help of this search you have found the desired category, now in the rubric need to find an interesting position, for this we need the site to do a search among the Entities according to the specified parameters.

the

Search parameters

What would the user imagined the boundaries of search, it is necessary for these boundaries to calculate.
If we do a search by category, we show the parameters (boundaries) to search only for the system Characteristics.

To do this, look at our column, there are system Characteristics and what methods of search are specified:

the

-- To search by category you must create the search parameters - the range of valid values for the system properties
SELECT
btrim(p.code) AS "property",
btrim(tu.code) AS "author_type",
btrim(ts.code) AS "search_type",
btrim(tt.code) AS "data_type"
FROM
rubric r
JOIN rubric_property rp
ON rp.rubric_id = r.id
JOIN property p
ON rp.property_id = p.id
JOIN property_tag ptu
on p.id = ptu.property_id
JOIN tag tu
on ptu.tag_id = tu.id

JOIN property_tag pts
on p.id = pts.property_id
JOIN tag ts
on the pts.tag_id = ts.id

JOIN property_tag ptt
on p.id = ptt.property_id
JOIN tag tt
on ptt.tag_id = tt.id
WHERE
r.code = 'ekskavatory-karernye'
AND tu.code = 'SYSTEM_PROPERTY'
AND ts.code IN ('LIKE_SEARCH_TYPE','BETWEEN_SEARCH_TYPE','ENUMERATION_SEARCH_TYPE')
AND tt.code IN ('DIGITAL_DATA_TYPE','STRING_DATA_TYPE')
;
/*
MANUFACTURER_MODEL, SYSTEM_PROPERTY, LIKE_SEARCH_TYPE, STRING_DATA_TYPE
TRADE_MARK, SYSTEM_PROPERTY, ENUMERATION_SEARCH_TYPE, STRING_DATA_TYPE
BUCKET_CAPACITY_M3, SYSTEM_PROPERTY, BETWEEN_SEARCH_TYPE, DIGITAL_DATA_TYPE
*/

explanation

Watch what are the system Characteristics:

the

tu.code = 'SYSTEM_PROPERTY'

Look what type of search identified for these Characteristics:

the

ts.code IN ('LIKE_SEARCH_TYPE','BETWEEN_SEARCH_TYPE','ENUMERATION_SEARCH_TYPE')

Look at the type of data for these Characteristics:

the

tt.code IN ('DIGITAL_DATA_TYPE','STRING_DATA_TYPE')

If one of the three search options for Features is not specified, the search perform is not possible (connection tables tu ts tt via JOIN).

Define the properties for the heading "Excavators career".

the

r.code = 'ekskavatory-karernye'

query Result

In the end we get three Features and search options:

MANUFACTURER_MODEL SYSTEM_PROPERTY, LIKE_SEARCH_TYPE, STRING_DATA_TYPE

TRADE_MARK SYSTEM_PROPERTY,ENUMERATION_SEARCH_TYPE, STRING_DATA_TYPE

BUCKET_CAPACITY_M3 SYSTEM_PROPERTY, BETWEEN_SEARCH_TYPE, DIGITAL_DATA_TYPE

Now for each feature, looking for the border.

the

Compute the boundaries of the search

the

Calculating the limits of the search for "MANUFACTURER_MODEL"

For "MANUFACTURER_MODEL" — type search for occurrence ("LIKE_SEARCH_TYPE") — boundaries are not calculated, the user derive the box to enter search string Feature.

the

Calculating the limits of the search for "TRADE_MARK"

the

-- formation search settings
SELECT
sm.string
FROM
rubric r
JOIN rubric_item ri
ON r.id = ri.rubric_id
JOIN item i
ON ri.item_id = i.id
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN property p
ON c.property_id = p.id
JOIN string_matter sm
ON c.id = sm.content_id
WHERE
p.code = 'TRADE_MARK'
AND r.code = 'ekskavatory-karernye'
GROUP BY sm.string;
/*
Uralmash
Donex
*/

The data type is "STRING_DATA_TYPE" means analyzing string_matter.string. Method search — "ENUMERATION_SEARCH_TYPE" — then do "GROUP BY". Calculated two values — "Uralmash" and "Donex", user print checkbox two.

the

Calculating the limits of the search for "BUCKET_CAPACITY_M3"

the

-- formation search settings
SELECT
max(dm.digital) AS maximum,
min(dm.digital) AS minimum
FROM
rubric r
JOIN rubric_item ri
ON r.id = ri.rubric_id
JOIN item i
ON ri.item_id = i.id
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN property p
ON c.property_id = p.id
JOIN digital_matter dm
ON c.id = dm.content_id
WHERE
p.code = 'BUCKET_CAPACITY_M3'
AND r.code = 'ekskavatory-karernye';
/*
25,0.75
*/

The data type is "DIGITAL_DATA_TYPE" means analyzing digital_matter.digital. Method search — "BETWEEN_SEARCH_TYPE" — then do the MIN() and MAX(). Calculated boundaries from 0.75 to 25, the user output something like this:

the

<input type="range" min="0.75" max="25">

the

Search parameters

the

-- search by category
/*
'ekskavatory-karernye'
'MANUFACTURER_MODEL'
'12'
'TRADE_MARK'
'Uralmash'
'Donex'
'BUCKET_CAPACITY_M3'
0.75
25
*/
SELECT
i.code
FROM
rubric_item ri
join rubric r
on ri.rubric_id = r.id
JOIN item i
ON ri.item_id = i.id
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN string_matter sm
ON c.id = sm.content_id
JOIN rubric_property rp
ON ri.rubric_id = rp.rubric_id
JOIN property p

AND p.id = rp.property_id
WHERE
r.code = :CODE
AND p.code = :MODEL_PROPERTY
AND sm.string LIKE '%'||:MODEL_LIKE||'%'
INTERSECT
SELECT
i.code
FROM
rubric_item ri
join rubric r
on ri.rubric_id = r.id
JOIN item i
ON ri.item_id = i.id
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN string_matter sm
ON c.id = sm.content_id
JOIN rubric_property rp
ON ri.rubric_id = rp.rubric_id
JOIN property p
ON c.property_id = p.id
AND p.id = rp.property_id
WHERE
r.code = :CODE
AND p.code = :MARK_PROPERTY
AND sm.the string IN ( :MARK1 , :MARK2)
INTERSECT
SELECT
i.code
FROM
rubric_item ri
join rubric r
on ri.rubric_id = r.id
JOIN item i
ON ri.item_id = i.id
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN digital_matter dm
ON c.id = dm.content_id
JOIN rubric_property rp
ON ri.rubric_id = rp.rubric_id
JOIN property p
ON c.property_id = p.id
AND p.id = rp.property_id
WHERE
r.code = :CODE
AND p.code = :BUCKET_PROPERTY
AND dm.digital BETWEEN :MIN_BUCKET AND :MAX_BUCKET
;

For each Characteristic make a search in accordance with the terms specified by the user and select those Entities that satisfy all of the conditions, that is, make the intersection — INTERSECT — the results of each separate search with each other.

If the user sets one condition for the search is one Characteristic if 100 conditions, you will be fulfilled 100 subqueries and the user will be given to those Entities which are present in the results of each of the 100 subqueries.

As a result of the search parameters the user has identified an interesting position for him (Essence).
It should be noted that a good search it was necessary to perform the selection according to the user "SYSTEM", but I missed this moment.

the

Search by custom values

I remind you that we do, "avito" for excavators. That is, each catalog item has a price, and each user has its own price. And when the user opened the card catalog items he saw some suggestions and accordingly want to do a search on these proposals.

We had a properties:

the

the
• "the price of goods in rubles,";
the
• "units of measure for commodity items";

It's custom properties, the values set by the user is governed by the Option "USER_PROPERTY".

the

results

Define a set of user Characteristics for the section "Backhoe loaders":

the

SELECT
btrim(p.code) AS "property",
btrim(tu.code) AS "author_type",
btrim(ts.code) AS "search_type",
btrim(tt.code) AS "data_type"
FROM
rubric r
JOIN rubric_property rp
ON rp.rubric_id = r.id
JOIN property p
ON rp.property_id = p.id
JOIN property_tag ptu
on p.id = ptu.property_id
JOIN tag tu
on ptu.tag_id = tu.id

JOIN property_tag pts
on p.id = pts.property_id
JOIN tag ts
on the pts.tag_id = ts.id

JOIN property_tag ptt
on p.id = ptt.property_id
JOIN tag tt
on ptt.tag_id = tt.id
WHERE
r.code = 'ekskavatory-pogruzchiki'
AND tu.code = 'USER_PROPERTY'
AND ts.code IN ('LIKE_SEARCH_TYPE','BETWEEN_SEARCH_TYPE','ENUMERATION_SEARCH_TYPE')
AND tt.code IN ('DIGITAL_DATA_TYPE','STRING_DATA_TYPE')
;
/*
GOODS_ITEM_PRICE_RUB,USER_PROPERTY,BETWEEN_SEARCH_TYPE,DIGITAL_DATA_TYPE
GOODS_ITEM_UNITS_OF_MEASURE,USER_PROPERTY,ENUMERATION_SEARCH_TYPE,STRING_DATA_TYPE
*/

"GOODS_ITEM_PRICE_RUB":

the

"GOODS_ITEM_UNITS_OF_MEASURE":

the

the

-- formation search settings in Essence 'jcb-4cx'
SELECT
min(dm.digital) AS minimum,
max(dm.digital) AS maximum
FROM

item i
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN property p
ON c.property_id = p.id
JOIN digital_matter dm
ON c.id = dm.content_id
WHERE
p.code = 'GOODS_ITEM_PRICE_RUB'
AND i.code = 'jcb-4cx';
/*
3400000
4700000
*/

SELECT
sm.string
FROM
item i
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN property p
ON c.property_id = p.id
JOIN string_matter sm
ON c.id = sm.content_id
WHERE
p.code = 'GOODS_ITEM_UNITS_OF_MEASURE'
AND i.code = 'jcb-4cx'
GROUP BY sm.string;
/*
p/piece
PCs
PCs
*/

the

Search query

See that all items of unit of measurement in units, just written differently, so only looking at the price.

the

/*
:ITEM_CODE => 'jcb-4cx'
:PRICE_PROPERTY => 'GOODS_ITEM_PRICE_RUB'
:MIN_PRICE => 3400000
:MAX_PRICE => 4000000
*/
SELECT
r.id
r.title
r.description
c.raw
FROM
item i
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN redactor_content rc
ON c.id = rc.content_id
JOIN redactor r
ON rc.redactor_id = r.id
JOIN digital_matter dm
ON c.id = dm.content_id
JOIN rubric_item ri
ON i.id = ri.item_id
JOIN rubric_property rp
ON ri.rubric_id = rp.rubric_id
JOIN property p
ON c.property_id = p.id
AND p.id = rp.property_id
WHERE
i.code = :ITEM_CODE
AND p.code = :PRICE_PROPERTY
AND dm.digital BETWEEN :MIN_PRICE AND :MAX_PRICE
;
/*
Vasily Alibabaevich Urumchi,the foreman of SMU-3,3 800 000

*/

Actually this is a simplified search purely on one aspect "price".
The query can be issued digital_matter.digital, but then (if we have the INTERSECT on several Characteristics) to do the conversion to TEXT ( digital_matter.digital::TEXT), in principle we derive the data in text form, so you can give content.raw.

the

Conclusion

Actually that would be enough to understand how the idea of an ideal catalog to apply in practice. Cases of course much more than search for the item and vendor.

The most important thing that I wanted to show is that the idea of flexible enough what would be ideal is Universal :)

Given my recovery_mode, a week later I can upload php scripts to dynamically generate SQL queries.

Thanks to all who read, I would be grateful for any criticism and any advice.

the

Addon

To complete the picture lacks a hierarchical scripts, to display all headings from the root, and to all parents for an arbitrary element. These scripts are in the \deploy\view_catalog_settings_and_data.sql.

The map entries are:

the

-- show map an Entity hierarchy to a hierarchy level for root headings 'GOODS'
WITH RECURSIVE road_map ( id, element_tree_id, code, horizont ) AS
(
SELECT
pet.id AS id,
pet.element_tree_id AS element_tree_id,
r.code AS code,
0 AS horizont
FROM
element_tree pet
LEFT JOIN rubric_element_tree ret
ON pet.id = ret.element_tree_id
Rubric r LEFT JOIN
ON ret.rubric_id = r.id
WHERE r.code = :ROOT
UNION
SELECT
cet.id
cet.element_tree_id,
r.code
horizont + 1
FROM
element_tree cet
JOIN road_map c
ON (c.id = cet.element_tree_id)
LEFT JOIN rubric_element_tree ret
ON cet.id = ret.element_tree_id
Rubric r LEFT JOIN
ON ret.rubric_id = r.id
)
SELECT
code
horizont
FROM
road_map
ORDER BY
horizont ASC;

The path from the root to a given node (column)

the

-- show the path from the descendant to the parent of the root  element  'ekskavatory-karernye'
WITH RECURSIVE road_map ( id, element_tree_id, code, horizont ) AS
(
SELECT
cet.id AS id,
cet.element_tree_id AS element_tree_id,
r.code AS code,
0 AS horizont
FROM
element_tree cet
LEFT JOIN rubric_element_tree ret
ON cet.id = ret.element_tree_id
Rubric r LEFT JOIN
ON ret.rubric_id = r.id
WHERE r.code = :CHILD
UNION
SELECT
pet.id
pet.element_tree_id,
r.code
horizont + 1
FROM
element_tree pet
JOIN road_map c
ON (c.element_tree_id = pet.id)
LEFT JOIN rubric_element_tree ret
ON pet.id = ret.element_tree_id
Rubric r LEFT JOIN
ON ret.rubric_id = r.id
)
SELECT
code
horizont
FROM
road_map
ORDER BY
horizont DESC;

Show all the values of all the Characteristics of a single Entity

the

-- the values of the Characteristics of a single Entity 'doneks-eo-4112a-1'
SELECT
i.title
p.title
dm.digital::TEXT
FROM
rubric_item ri
JOIN item i
ON ri.item_id = i.id
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN digital_matter dm
ON c.id = dm.content_id
JOIN rubric_property rp
ON ri.rubric_id = rp.rubric_id
JOIN property p
ON c.property_id = p.id
AND p.id = rp.property_id
WHERE
i.code = :CODE
UNION
SELECT
i.title
p.title
sm.string::TEXT
FROM
rubric_item ri
JOIN item i
ON ri.item_id = i.id
JOIN item_content ic
ON i.id = ic.item_id
JOIN content c
ON ic.content_id = c.id
JOIN string_matter sm
ON c.id = sm.content_id
JOIN rubric_property rp
ON ri.rubric_id = rp.rubric_id
JOIN property p
ON c.property_id = p.id
AND p.id = rp.property_id
WHERE
i.code = :CODE;

Article based on information from habrahabr.ru