🖕🏽 🕺🏻 😺 理想的なカタログ、実装オプション 🥫 ⚪️ 〽️

「理想的なカタログ、アーキテクチャスケッチ」という記事の続きでは、提案されたデータベース構造を使用して任意のデータを保存し、このデータに対して任意の検索を実行する方法を例で示します。スクリプトはリポジトリーuniversal_data_catalog_ideaにあります。

これらのスクリプトを作成者のコメントで見ることに興味のある人、猫に招待します。

リポジトリのコンテンツ

リポジトリには、インフラストラクチャスクリプトの完全なセットがあります。

テーブル作成
充填データ;
データからテーブルをクリーニングします。
作成されたすべてのテーブルを削除する

スクリプトを書き始めたとき、それらのボリュームは小さかったが、テストスイートを作成するようになったとき、コードの量は1000行以上に増加した。このような大きなスクリプトを記事に挿入する必要はないようです。そのため、「ライブ」データに触れたい場合は、リポジトリのクローンを作成し、実際にどのようになるかを確認してください。

データベーススキーマでは、小さな変更を加えました。編集者（redactor_id）をコンテンツから削除しました。現在はコンテンツ自体であり、編集者自身です。

データ編成のハイライト

システム自体は使用規則を規定せず、任意のロジックを適用できます。

システムの主な目的はデータ検索であり、データストレージが必要ですが、それでも二次機能です。

任意のデータ、任意の構成を保存できます。カタログデータは、エンティティ（アイテム）とその値（コンテンツ）で構成されます。各値は特定のプロパティの値です。同じ特性セットを持つエンティティは、ルーブリックに結合できます。

エンティティは1つのRubricに属することができますが、提示されたバージョンでは1つだけに属することができます。

データの追加（ストレージ）

ディレクトリにデータを追加する方法は、\ deploy \ commit_dml.sqlにあります。シーケンスは次のとおりです。

ルーブリックを作成します。
ルーブリックの階層が必要な場合は、階層（element_tree）を作成し、階層内のルーブリック（rubric_element_tree）を配布します。
特性（プロパティ）を追加します。
ルールに従って特性を使用する場合は、これらのルールのオプション（タグ）を追加し、特性とオプション（property_tag）を適切に一致させることができます。
Rubrics特性（rubric_property）に割り当てます。
エンティティ（アイテム）を追加します。
エンティティをカテゴリ別にグループ化（rubric_item）;
特性の値（コンテンツ）を追加します。
エンティティ（item_content）を持つ値をドッキングします。
値に複数のエディターがあると想定される場合は、エディター（redactor）を追加し、エディターの値（redactor_content）を割り当てます。
文字列検索だけが想定されていない場合、ユーザー入力（content.raw）を特定のデータ型に変換し、対応するテーブル（date_matter.date_time、digital_matter.digital、duration_matter.duration、string_matter.string）にデータを書き込みます。

リストは長く出てきましたが、実際にはすべてのステップが2つのアクションに収まります。

エンティティを追加します。
値を設定します。

残りの手順は必要です。情報ストレージの理論が整理されました。今実用的なアプリケーション。

カタログの実用的な内容

掘削機の販売用にAvitoを作りたいとします。
これを行うには、ルートセクション「掘削機」を追加し、それに2つの補助的な「掘削機採石場」と「掘削機ローダー」を追加します。
「掘削機の採掘」セクションでは、プロパティを割り当てます。
「製品モデル」;
「商標」;
「バケット容量」。
「ルーブルの商品の価格」;
「見出しの測定単位」。
「バックホーローダー」セクションでは、同様のプロパティセットとプロパティ「Shovel capacity」を割り当てます。
特徴：
「製品モデル」、
「商標」、
「バケット容量」、
「ショベル容量」、
システムである場合、これらの特性の値はコンテンツマネージャによって決定され、オプション-「SYSTEM_PROPERTY」を割り当てます。
「バケット容量」と「ショベル容量」は数値データです-オプション「DIGITAL_DATA_TYPE」、それらの検索は値の範囲での検索-「BETWEEN_SEARCH_TYPE」です。
「製品モデル」と「商標」は文字列データです-「STRING_DATA_TYPE」、「商標」の検索はメーカーディレクトリでのようになります-リスト-「ENUMERATION_SEARCH_TYPE」、「製品モデル」特性の場合はサブストリングを検索します-「LIKE_SEARCH_TYPE」。

エンティティとバリューを殺します。これで、一般的な検索を実行できます。

一般検索

一般的な検索はどこでも検索を意味し、どこでも検索はカテゴリとエンティティによってのみ制限されます。原則として、ユーザー入力を文字列形式で保存するため、値も検索できます。どこでも検索は、サブストリング検索を意味し、どの列でですか？どうやら「タイトル」と「説明」のみ。

行こう！

--  "" (       ) 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 LEFT JOIN rubric r 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 LEFT JOIN rubric r 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 LEFT JOIN rubric r 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 LEFT JOIN rubric r ON ret.rubric_id = r.id ) SELECT NULL FROM road_map rm WHERE rm.code = :CATALOG_ROOT ORDER BY horizont DESC LIMIT 1 ) R );

リクエストパラメータ

ルートセクション

 :CATALOG_ROOT

ルートルブリックコード（rubric.code）は、検索領域を制限するために使用されます。一般的に「掘削機」で検索できますが、キャリアのもののみを検索できます。

祖先の次のオブジェクトが指定されたルーブリックを持つかどうかの決定は、各DBMSに固有の階層クエリを通じて行われます。

地域ごとに制限なしで検索できます。カタログにサービス（掘削機のレンタルなど）と履歴書（掘削機のオペレーターなど）を追加し、サブストリング「掘削機」を探している場合は、掘削機のレンタル位置とオペレーターの位置も確認できますショベル。

検索文字列

 :SEARCH_PATTERN

検索文字列-実際に探している部分文字列です。検索文字列をスペース（またはその他の区切り文字）で分割し、条件で検索できます。

 WHERE (i.title ILIKE '%' || :PATTERN_PART1|| '%' OR i.description ILIKE '%' || :PATTERN_PART1 || '%') AND (i.title ILIKE '%' || :PATTERN_PART2|| '%' OR i.description ILIKE '%' || :PATTERN_PART2|| '%') --       AND (i.title ILIKE '%' || :PATTERN_PART_N|| '%' OR i.description ILIKE '%' || :PATTERN_PART_N|| '%')

結果のスクリプトは確かに「巨大」に見えますが、手動で作成されるのではなく、アプリケーションが作成し、DBMSがスクリプトを飲み込むため、文字数を確認する必要はありません-これは必須ではありません。

この検索の助けを借りて、ユーザーが必要な見出しを見つけたとします。今度は、興味深い位置を見つける必要がある見出しで、指定されたパラメーターのエンティティ間の見出しで検索を行う必要があります。

パラメータで検索

ユーザーが検索の境界を想像するために、これらの境界を計算する必要があります。
セクションで検索を行う場合、システムの特性のみの検索パラメーター（境界線）を表示します。

これを行うには、セクションの内容、システムの特性、設定されている検索方法を見てみましょう。

 --         -       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 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 */

リクエストの説明

どの特性が体系的であるかを調べます。

 tu.code = 'SYSTEM_PROPERTY'

これらの特性について、どのタイプの検索が定義されているかを確認します。

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

これらの特性のデータ型を調べます。

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

Characteristicの3つの検索パラメーターのいずれかが設定されていない場合、検索を実行できません（JOINを介してテーブルを結合します）。

「キャリアショベル」という見出しのプロパティを決定します。

 r.code = 'ekskavatory-karernye'

クエリ結果

その結果、3つの特性と検索パラメーターを取得します。

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

各特性について、境界を探しています。

検索境界の計算

「MANUFACTURER_MODEL」の検索範囲の計算

「MANUFACTURER_MODEL」-エントリによる検索のタイプ（「LIKE_SEARCH_TYPE」）-境界を計算せず、ユーザーがこの特性の検索文字列を入力するためのフィールドを表示します。

TRADE_MARKの検索境界の計算

 --    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; /*   */

データ型-「STRING_DATA_TYPE」-分析中-string_matter.stringを意味します。検索方法-「ENUMERATION_SEARCH_TYPE」-「GROUP BY」を実行することを意味します。「Uralmash」と「Donex」の2つの値を計算しました。ユーザーは2つのチェックボックスを表示します。

「BUCKET_CAPACITY_M3」の検索範囲の計算

 --    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 */

データタイプ-「DIGITAL_DATA_TYPE」-分析していることを意味します-digital_matter.digital。検索方法-"BETWEEN_SEARCH_TYPE"-は、MIN（）およびMAX（）を実行することを意味します。 0.75〜25の境界を計算すると、ユーザーは次のように表示します。

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

パラメータで検索

 --    /* 'ekskavatory-karernye' 'MANUFACTURER_MODEL' '12' 'TRADE_MARK' '' '' '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 ON c.property_id = p.id 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.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 ;

各特性について、ユーザーが指定した条件に従って検索を行い、すべての条件を満たすエンティティを選択します。つまり、各個別の検索結果の交差点-INTERSECTを作成します。

ユーザーが1つの条件を設定すると、検索は1つの特性で行われ、100の条件では、100のサブクエリが実行され、100のサブクエリのそれぞれの結果に存在するエンティティがユーザーに発行されます。

パラメータによる検索の結果、ユーザーは自分（エンティティ）にとって興味深い位置を決定しました。
ユーザー「SYSTEM」による選択を使用して適切な検索を実行する必要があることに注意する必要がありますが、私はこの点を逃しました。

カスタム値検索

掘削機用にAvitoを使用していることを思い出してください。つまり、各カタログアイテムには独自の価格があり、各ユーザーには独自の価格があります。ユーザーがカタログ位置カードを開いたとき、彼はいくつかのオファーを見て、それに応じてこれらのオファーを検索したいと考えました。

プロパティがありました：

「ルーブルの商品の価格」;
「見出しの測定単位」。

これらはユーザー定義のプロパティであり、それらの値はユーザーによって設定され、これはUSER_PROPERTYオプションによって制御されます。

ボーダーを検索

「掘削機ローダー」セクションのカスタム特性のセットを定義します。

 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 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：

「BETWEEN_SEARCH_TYPE」-範囲で検索。
「DIGITAL_DATA_TYPE」-数値データ。

GOODS_ITEM_UNITS_OF_MEASURE：

「ENUMERATION_SEARCH_TYPE」-列挙付きのリスト。
「STRING_DATA_TYPE」-文字データ。

 --      '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; /* /  . */

検索クエリ

すべての位置で、ピース単位の測定単位はさまざまな方法で単純に記録されるため、価格のみを確認します。

 /* :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 ; /*   , -3,3 800 000 ,      ,3 400 000 */

実際には、これは純粋に1つの特性「価格」の単純化された検索です。
リクエストの結果として、digital_matter.digitalを発行することもできますが、（複数の特性のINTERSECTがある場合）TEXTタイプ（digital_matter.digital::TEXT）にキャストする必要があり、原則として、データをテキスト形式でユーザーに表示するため、発行できますcontent.raw。

おわりに

実際、これは理想的なカタログのアイデアをどのように実践できるかを理解するのに十分です。もちろん、商品やサプライヤーの検索よりも桁違いに多いケースです。

私が見せたかった最も重要なことは、アイデアが完全に普遍的になるのに十分な柔軟性があるということです :)

私のrecovery_modeを考慮して、1週間でSQLクエリの動的生成のためのPHPスクリプトをレイアウトできるようになります。

読んでくださった皆さんのおかげで、私はどんな批判やアドバイスにも感謝します。

アドオン

完全を期すために、ルートからのすべてのルーブリックを表示したり、任意の要素のすべての親を表示するのに十分な階層スクリプトはありません。これらのスクリプトは、\ deploy \ view_catalog_settings_and_data.sqlにあります。

カテゴリーのマップ：

 --       ,    '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 LEFT JOIN rubric r 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 LEFT JOIN rubric r ON ret.rubric_id = r.id ) SELECT code, horizont FROM road_map ORDER BY horizont ASC;

ルートから指定されたノードへのパス（見出し）

 --         '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 LEFT JOIN rubric r 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 LEFT JOIN rubric r ON ret.rubric_id = r.id ) SELECT code, horizont FROM road_map ORDER BY horizont DESC;

1つのエンティティのすべての特性のすべての値を表示

 --     '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;

理想的なカタログ、実装オプション

リポジトリのコンテンツ

データ編成のハイライト

データの追加（ストレージ）

カタログの実用的な内容

一般検索

リクエストパラメータ

ルートセクション

検索文字列

パラメータで検索

リクエストの説明

クエリ結果

検索境界の計算

「MANUFACTURER_MODEL」の検索範囲の計算

TRADE_MARKの検索境界の計算

「BUCKET_CAPACITY_M3」の検索範囲の計算

パラメータで検索

カスタム値検索

ボーダーを検索

検索クエリ

おわりに

アドオン

More articles: