Kouhei Sutou 2019-01-16 18:20:35 +0900 (Wed, 16 Jan 2019) Revision: a7f5417b9c45534d62b2f34b4bed93b2cd566078 https://github.com/groonga/groonga-schema-design-example/commit/a7f5417b9c45534d62b2f34b4bed93b2cd566078 Message: Add deep nested data structure case Added files: LICENSE.txt README.md literature/README.md literature/index.grn literature/load.grn literature/run.sh literature/schema.grn literature/select.grn Added: LICENSE.txt (+121 -0) 100644 =================================================================== --- /dev/null +++ LICENSE.txt 2019-01-16 18:20:35 +0900 (0e259d4) @@ -0,0 +1,121 @@ +Creative Commons Legal Code + +CC0 1.0 Universal + + CREATIVE COMMONS CORPORATION IS NOT A LAW FIRM AND DOES NOT PROVIDE + LEGAL SERVICES. DISTRIBUTION OF THIS DOCUMENT DOES NOT CREATE AN + ATTORNEY-CLIENT RELATIONSHIP. CREATIVE COMMONS PROVIDES THIS + INFORMATION ON AN "AS-IS" BASIS. CREATIVE COMMONS MAKES NO WARRANTIES + REGARDING THE USE OF THIS DOCUMENT OR THE INFORMATION OR WORKS + PROVIDED HEREUNDER, AND DISCLAIMS LIABILITY FOR DAMAGES RESULTING FROM + THE USE OF THIS DOCUMENT OR THE INFORMATION OR WORKS PROVIDED + HEREUNDER. + +Statement of Purpose + +The laws of most jurisdictions throughout the world automatically confer +exclusive Copyright and Related Rights (defined below) upon the creator +and subsequent owner(s) (each and all, an "owner") of an original work of +authorship and/or a database (each, a "Work"). + +Certain owners wish to permanently relinquish those rights to a Work for +the purpose of contributing to a commons of creative, cultural and +scientific works ("Commons") that the public can reliably and without fear +of later claims of infringement build upon, modify, incorporate in other +works, reuse and redistribute as freely as possible in any form whatsoever +and for any purposes, including without limitation commercial purposes. +These owners may contribute to the Commons to promote the ideal of a free +culture and the further production of creative, cultural and scientific +works, or to gain reputation or greater distribution for their Work in +part through the use and efforts of others. + +For these and/or other purposes and motivations, and without any +expectation of additional consideration or compensation, the person +associating CC0 with a Work (the "Affirmer"), to the extent that he or she +is an owner of Copyright and Related Rights in the Work, voluntarily +elects to apply CC0 to the Work and publicly distribute the Work under its +terms, with knowledge of his or her Copyright and Related Rights in the +Work and the meaning and intended legal effect of CC0 on those rights. + +1. Copyright and Related Rights. A Work made available under CC0 may be +protected by copyright and related or neighboring rights ("Copyright and +Related Rights"). Copyright and Related Rights include, but are not +limited to, the following: + + i. the right to reproduce, adapt, distribute, perform, display, + communicate, and translate a Work; + ii. moral rights retained by the original author(s) and/or performer(s); +iii. publicity and privacy rights pertaining to a person's image or + likeness depicted in a Work; + iv. rights protecting against unfair competition in regards to a Work, + subject to the limitations in paragraph 4(a), below; + v. rights protecting the extraction, dissemination, use and reuse of data + in a Work; + vi. database rights (such as those arising under Directive 96/9/EC of the + European Parliament and of the Council of 11 March 1996 on the legal + protection of databases, and under any national implementation + thereof, including any amended or successor version of such + directive); and +vii. other similar, equivalent or corresponding rights throughout the + world based on applicable law or treaty, and any national + implementations thereof. + +2. Waiver. To the greatest extent permitted by, but not in contravention +of, applicable law, Affirmer hereby overtly, fully, permanently, +irrevocably and unconditionally waives, abandons, and surrenders all of +Affirmer's Copyright and Related Rights and associated claims and causes +of action, whether now known or unknown (including existing as well as +future claims and causes of action), in the Work (i) in all territories +worldwide, (ii) for the maximum duration provided by applicable law or +treaty (including future time extensions), (iii) in any current or future +medium and for any number of copies, and (iv) for any purpose whatsoever, +including without limitation commercial, advertising or promotional +purposes (the "Waiver"). Affirmer makes the Waiver for the benefit of each +member of the public at large and to the detriment of Affirmer's heirs and +successors, fully intending that such Waiver shall not be subject to +revocation, rescission, cancellation, termination, or any other legal or +equitable action to disrupt the quiet enjoyment of the Work by the public +as contemplated by Affirmer's express Statement of Purpose. + +3. Public License Fallback. Should any part of the Waiver for any reason +be judged legally invalid or ineffective under applicable law, then the +Waiver shall be preserved to the maximum extent permitted taking into +account Affirmer's express Statement of Purpose. In addition, to the +extent the Waiver is so judged Affirmer hereby grants to each affected +person a royalty-free, non transferable, non sublicensable, non exclusive, +irrevocable and unconditional license to exercise Affirmer's Copyright and +Related Rights in the Work (i) in all territories worldwide, (ii) for the +maximum duration provided by applicable law or treaty (including future +time extensions), (iii) in any current or future medium and for any number +of copies, and (iv) for any purpose whatsoever, including without +limitation commercial, advertising or promotional purposes (the +"License"). The License shall be deemed effective as of the date CC0 was +applied by Affirmer to the Work. Should any part of the License for any +reason be judged legally invalid or ineffective under applicable law, such +partial invalidity or ineffectiveness shall not invalidate the remainder +of the License, and in such case Affirmer hereby affirms that he or she +will not (i) exercise any of his or her remaining Copyright and Related +Rights in the Work or (ii) assert any associated claims and causes of +action with respect to the Work, in either case contrary to Affirmer's +express Statement of Purpose. + +4. Limitations and Disclaimers. + + a. No trademark or patent rights held by Affirmer are waived, abandoned, + surrendered, licensed or otherwise affected by this document. + b. Affirmer offers the Work as-is and makes no representations or + warranties of any kind concerning the Work, express, implied, + statutory or otherwise, including without limitation warranties of + title, merchantability, fitness for a particular purpose, non + infringement, or the absence of latent or other defects, accuracy, or + the present or absence of errors, whether or not discoverable, all to + the greatest extent permissible under applicable law. + c. Affirmer disclaims responsibility for clearing rights of other persons + that may apply to the Work or any use thereof, including without + limitation any person's Copyright and Related Rights in the Work. + Further, Affirmer disclaims responsibility for obtaining any necessary + consents, permissions or other rights required for any use of the + Work. + d. Affirmer understands and acknowledges that Creative Commons is not a + party to this document and has no duty or obligation with respect to + this CC0 or use of the Work. Added: README.md (+5 -0) 100644 =================================================================== --- /dev/null +++ README.md 2019-01-16 18:20:35 +0900 (76cc7bc) @@ -0,0 +1,5 @@ +# Groonga's schema design examples + +Here are available examples: + + * [literature](literature/): Deep nested data structure. Added: literature/README.md (+30 -0) 100644 =================================================================== --- /dev/null +++ literature/README.md 2019-01-16 18:20:35 +0900 (2c400a8) @@ -0,0 +1,30 @@ +# Literature + +A deep nested data structure. + +See also: https://www.clear-code.com/blog/2019/1/16.html (Japanese) + +## How to try + +There are the following Groonga command files: + + * `schema.grn`: Schema definitions + * `index.grn`: Index definitions + * `load.grn`: Sample data + * `select.grn`: Sample queries + +You should use these command files in the following order: + + 1. `schema.grn` + 2. `load.grn` + 3. `index.grn` + 4. `select.grn` + +`run.sh` does all you need: + +```console +% ./run.sh +``` + +It defines schema, loads sample data, creates indexes and shows query +results. Added: literature/index.grn (+61 -0) 100644 =================================================================== --- /dev/null +++ literature/index.grn 2019-01-16 18:20:35 +0900 (7464c6f) @@ -0,0 +1,61 @@ +# For full text searches +# 各種全文検索用 +table_create Terms TABLE_PAT_KEY ShortText \ + --default_tokenizer TokenNgram \ + --normalizer NormalizerNFKC100 +column_create Terms authors_key \ + COLUMN_INDEX|WITH_POSITION Authors _key +column_create Terms paper_publishers_key \ + COLUMN_INDEX|WITH_POSITION PaperPublishers _key +column_create Terms magazines_key \ + COLUMN_INDEX|WITH_POSITION Magazines _key +column_create Terms book_publishers_key \ + COLUMN_INDEX|WITH_POSITION BookPublishers _key +column_create Terms parent_categories_key \ + COLUMN_INDEX|WITH_POSITION ParentCategories _key +column_create Terms child_categories_key \ + COLUMN_INDEX|WITH_POSITION ChildCategories _key +column_create Terms series_key \ + COLUMN_INDEX|WITH_POSITION Series _key +column_create Terms literature_title \ + COLUMN_INDEX|WITH_POSITION Literature title + +# For finding papers found by full text search against authors +# 著者名で全文検索して論文を絞り込むときに必要 +column_create Authors literature_authors \ + COLUMN_INDEX Literature authors + +# For finding magazines found by full text search against publisher name +# 出版元名で全文検索して雑誌を絞り込むときに必要 +column_create PaperPublishers magazines_publisher \ + COLUMN_INDEX Magazines publisher + +# For finding volumes found by full text search against magazine name +# 雑誌名で全文検索して号を絞り込むときに必要 +column_create Magazines volumes_magazine \ + COLUMN_INDEX Volumes magazine + +# For finding papers found by full text search against volume +# 号の情報で検索して論文を絞り込むときに必要 +column_create Volumes literature_volume \ + COLUMN_INDEX Literature volume + +# For finding books found by full text search against publisher name +# 出版社名で全文検索して書籍を絞り込むときに必要 +column_create BookPublishers literature_book_publisher \ + COLUMN_INDEX Literature book_publisher + +# For finding books found by search against chidl category +# 子カテゴリの情報で検索して書籍を絞り込むときに必要 +column_create ChildCategories literature_child_category \ + COLUMN_INDEX Literature child_category + +# For finding child categories found by search against parent category +# 親カテゴリの情報で検索して子カテゴリーを絞り込むときに必要 +column_create ParentCategories child_categories_parent \ + COLUMN_INDEX ChildCategories parent + +# For finding books found by full text search against series name +# シリーズ名で全文検索して書籍を絞り込むときに必要 +column_create Series literature_series \ + COLUMN_INDEX Literature series Added: literature/load.grn (+82 -0) 100644 =================================================================== --- /dev/null +++ literature/load.grn 2019-01-16 18:20:35 +0900 (782a6e2) @@ -0,0 +1,82 @@ +load --table PaperPublishers +[ +{"_key": "イケてる論文出版"}, +{"_key": "すごい論文出版"} +] + +load --table Magazines +[ +{"_key": "イケてる雑誌", "publisher": "イケてる論文出版"}, +{"_key": "おもしろ雑誌", "publisher": "イケてる論文出版"}, +{"_key": "簡単雑誌", "publisher": "すごい論文出版"} +] + +load --table Volumes +[ +{"_key": "イケてる1号", "magazine": "イケてる雑誌"}, +{"_key": "イケてる2号", "magazine": "イケてる雑誌"}, +{"_key": "イケてる3号", "magazine": "イケてる雑誌"}, +{"_key": "おもしろ1号", "magazine": "おもしろ雑誌"}, +{"_key": "簡単1号", "magazine": "簡単雑誌"}, +{"_key": "簡単2号", "magazine": "簡単雑誌"} +] + +load --table Literature +[ +{"_key": "ISSN:1:1", "title": "イケてる論文1", "authors": ["山田太郎", "山田五郎"], + "type": "paper", "volume": "イケてる1号"}, +{"_key": "ISSN:1:2", "title": "驚き論文", "authors": ["山田花子"], + "type": "paper", "volume": "イケてる1号"}, +{"_key": "ISSN:2:1", "title": "イケてる論文2", "authors": ["田中はじめ"], + "type": "paper", "volume": "イケてる2号"}, +{"_key": "ISSN:3:1", "title": "イケてる論文3", "authors": ["山田一郎"], + "type": "paper", "volume": "イケてる3号"}, +{"_key": "ISSN:4:1", "title": "おもしろ論文1", "authors": ["佐藤あかね"], + "type": "paper", "volume": "おもしろ1号"}, +{"_key": "ISSN:5:1", "title": "簡単論文1", "authors": ["山中太郎"], + "type": "paper", "volume": "簡単1号"}, +{"_key": "ISSN:5:2", "title": "簡単論文2", "authors": ["佐藤邦子"], + "type": "paper", "volume": "簡単1号"} +] + +load --table BookPublishers +[ +{"_key": "イケてる書籍出版"}, +{"_key": "すごい書籍出版"} +] + +load --table ParentCategories +[ +{"_key": "イケてる親カテゴリー"}, +{"_key": "すごい親カテゴリー"} +] + +load --table ChildCategories +[ +{"_key": "イケてるカテゴリー", parent: "イケてる親カテゴリー"}, +{"_key": "すごいカテゴリー", parent: "すごい親カテゴリー"} +] + +load --table Series +[ +{"_key": "イケてるシリーズ"}, +{"_key": "すごいシリーズ"} +] + +load --table Literature +[ +{"_key": "ISBN:1", "title": "イケてる書籍", authors: ["山田太郎"], + "type": "book", + "book_publisher": "イケてる書籍出版", + "child_category": "イケてるカテゴリー", + "series": "イケてるシリーズ"}, +{"_key": "ISBN:2", "title": "驚き書籍", authors: ["鈴木太郎"], + "type": "book", + "book_publisher": "イケてる書籍出版", + "child_category": "イケてるカテゴリー"}, +{"_key": "ISBN:3", "title": "簡単書籍", authors: ["田中太郎"], + "type": "book", + "book_publisher": "簡単書籍出版", + "child_category": "すごいカテゴリー", + "series": "すごいシリーズ"} +] Added: literature/run.sh (+15 -0) 100755 =================================================================== --- /dev/null +++ literature/run.sh 2019-01-16 18:20:35 +0900 (59c8b9d) @@ -0,0 +1,15 @@ +#!/bin/sh + +set -x +set -e + +cd "$(dirname $0)" + +rm -rf db +mkdir -p db + +(cat schema.grn + cat load.grn + cat index.grn + cat select.grn) | \ + groonga --log-path db/log -n db/db Added: literature/schema.grn (+30 -0) 100644 =================================================================== --- /dev/null +++ literature/schema.grn 2019-01-16 18:20:35 +0900 (c4a2bd6) @@ -0,0 +1,30 @@ +# "paper" or "book" +table_create Types TABLE_HASH_KEY ShortText + +table_create Authors TABLE_HASH_KEY ShortText + +table_create PaperPublishers TABLE_HASH_KEY ShortText + +table_create Magazines TABLE_HASH_KEY ShortText +column_create Magazines publisher COLUMN_SCALAR PaperPublishers + +table_create Volumes TABLE_PAT_KEY ShortText +column_create Volumes magazine COLUMN_SCALAR Magazines + +table_create BookPublishers TABLE_HASH_KEY ShortText + +table_create ParentCategories TABLE_HASH_KEY ShortText + +table_create ChildCategories TABLE_HASH_KEY ShortText +column_create ChildCategories parent COLUMN_SCALAR ParentCategories + +table_create Series TABLE_HASH_KEY ShortText + +table_create Literature TABLE_HASH_KEY ShortText +column_create Literature type COLUMN_SCALAR Types +column_create Literature title COLUMN_SCALAR ShortText +column_create Literature authors COLUMN_VECTOR Authors +column_create Literature volume COLUMN_SCALAR Volumes +column_create Literature book_publisher COLUMN_SCALAR BookPublishers +column_create Literature child_category COLUMN_SCALAR ChildCategories +column_create Literature series COLUMN_SCALAR Series Added: literature/select.grn (+157 -0) 100644 =================================================================== --- /dev/null +++ literature/select.grn 2019-01-16 18:20:35 +0900 (3bfa543) @@ -0,0 +1,157 @@ +# Full text search against paper and book +# 横断全文検索 +select \ + --table Literature \ + --match_columns 'title || authors._key' \ + --query '簡単 OR 山田' \ + --output_columns 'title, type, authors' \ + --output_pretty yes + +# Full text search against only paper +# 論文だけ全文検索 +select \ + --table Literature \ + --match_columns 'title || authors._key' \ + --query '簡単 OR 山田' \ + --filter 'type == "paper"' \ + --output_columns 'title, type, authors' \ + --output_pretty yes + +# Full text search against only book +# 書籍だけ全文検索 +select \ + --table Literature \ + --match_columns 'title || authors._key' \ + --query '簡単 OR 山田' \ + --filter 'type == "book"' \ + --output_columns 'title, type, authors' \ + --output_pretty yes + +# Full text search + drilldown +# Drilldown: +# * types: paper or book +# * volumes: Volume +# * magazines: Magazine +# * paper_publishers: Publisher for paper +# * book_publishers: Publisher for book +# * child_categories: Child category +# * parent_categories: Parent category +# * series: Series +# +# 横断全文検索 + ドリルダウン +# ドリルダウン： +# * types: 論文（paper）か書籍（book）か +# * volumes: 号 +# * magazines: 雑誌 +# * paper_publishers: 論文の出版社 +# * book_publishers: 書籍の出版社 +# * child_categories: 子カテゴリ +# * parent_categories: 親カテゴリ +# * series: シリーズ +select \ + --table Literature \ + --match_columns 'title || authors._key' \ + --query '簡単 OR 山田' \ + --output_columns 'title, authors' \ + --drilldowns[types].keys 'type' \ + --drilldowns[types].output_columns '_key,_nsubrecs' \ + --drilldowns[volumes].keys 'volume' \ + --drilldowns[volumes].output_columns '_key,_nsubrecs' \ + --drilldowns[magazines].table 'volumes' \ + --drilldowns[magazines].keys 'magazine' \ + --drilldowns[magazines].calc_target '_nsubrecs' \ + --drilldowns[magazines].calc_types 'SUM' \ + --drilldowns[magazines].output_columns '_key,_sum' \ + --drilldowns[paper_publishers].table 'magazines' \ + --drilldowns[paper_publishers].keys 'publisher' \ + --drilldowns[paper_publishers].calc_target '_sum' \ + --drilldowns[paper_publishers].calc_types 'SUM' \ + --drilldowns[paper_publishers].output_columns '_key,_sum' \ + --drilldowns[book_publishers].keys 'book_publisher' \ + --drilldowns[book_publishers].output_columns '_key,_nsubrecs' \ + --drilldowns[child_categories].keys 'child_category' \ + --drilldowns[child_categories].output_columns '_key,_nsubrecs' \ + --drilldowns[parent_categories].table 'child_categories' \ + --drilldowns[parent_categories].keys 'parent' \ + --drilldowns[parent_categories].calc_target '_nsubrecs' \ + --drilldowns[parent_categories].calc_types 'SUM' \ + --drilldowns[parent_categories].output_columns '_key,_sum' \ + --drilldowns[series].keys 'series' \ + --drilldowns[series].output_columns '_key,_nsubrecs' \ + --output_pretty yes + +# List magazines of found publisheres +# Full text search against publisher name +# 出版元が発行している雑誌の一覧 +# 出版元名で全文検索 +select \ + --table Magazines \ + --match_columns 'publisher._key' \ + --query 'イケてる論文出版' \ + --output_columns '_key, publisher' \ + --output_pretty yes + +# List volumns of found magazines +# Full text search against magazine name +# 雑誌の号の一覧 +# 雑誌名で全文検索 +select \ + --table Volumes \ + --match_columns 'magazine._key' \ + --query 'イケてる雑誌' \ + --output_columns '_key, magazine' \ + --output_pretty yes + +# List papers of found volumes +# Full text search against volume name +# 号内の論文の一覧 +# 号名で検索 +select \ + --table Literature \ + --filter 'volume == "イケてる1号"' \ + --output_columns '_key, title, volume, volume.magazine' \ + --output_pretty yes + +# List books of found publisheres +# Full text search against publisher name +# 出版社が発行している書籍の一覧 +# 出版社名で全文検索 +select \ + --table Literature \ + --match_columns 'book_publisher._key' \ + --query 'イケてる書籍出版' \ + --output_columns '_key, title, book_publisher' \ + --output_pretty yes + +# List books of found child categories +# Full text search against child category name +# 子カテゴリに属する書籍の一覧 +# 子カテゴリ名で検索 +select \ + --table Literature \ + --match_columns 'child_category._key' \ + --query 'イケてるカテゴリー' \ + --output_columns '_key, title, child_category' \ + --output_pretty yes + +# List books of found parent categories +# Full text search against parent category name +# 親カテゴリに属する書籍の一覧 +# 親カテゴリ名で検索 +select \ + --table Literature \ + --match_columns 'child_category.parent._key' \ + --query 'イケてる親カテゴリー' \ + --output_columns '_key, title, child_category, child_category.parent' \ + --output_pretty yes + +# List books of found series +# Full text search against series name +# シリーズに属している書籍の一覧 +# シリーズ名で全文検索 +select \ + --table Literature \ + --match_columns 'series._key' \ + --query 'イケてるシリーズ' \ + --output_columns '_key, title, series' \ + --output_pretty yes -------------- next part -------------- An HTML attachment was scrubbed... URL: <https://lists.osdn.me/mailman/archives/groonga-commit/attachments/20190116/61d961db/attachment-0001.html>
