7. ltree 插件 · postgresql教程

# 7. ltree 插件 #### ltree介紹 ltree是PostgreSQL的一個擴展插件，即extension，使用它可以實現樹型結構，而且還支持索引和豐富的查詢。 [ltree](http://www.postgresql.org/docs/current/static/ltree.html)官方文檔給出了詳細的解釋。它的概念很簡單，打個比方，比如，我們要存一個樹型菜單，不限定級數，有祖先(根)，根子節有子節點，子節點又有子節點，以此類推，形成一顆樹。假如我們存公司的數據，它的表名叫companies，有個字段叫name，存的是分公司的名稱。有一個很簡單的方法，來實現這種樹型結構，只要在companies表中增加一個字段parent\_id即可，它存的是父節點的id，以此來找到父節點，根節點的parent\_id為null，其他節點都為父節點的id。這種方式是可以的，它也能查詢到所有的節點，由于存有parent\_id，它找子節點，父節點，根節點都很簡單，若要找其他節點，只能通過遍歷了，效率較低。而且，每次添加節點，都要查找父節點的id，即parent\_id，這樣也不夠直觀和靈活。而ltree是在數據庫級別支持的樹型結構。它支持豐富的查詢。 #### ltree使用我們來演示一下搭建這樣的樹型結構。 ``` Top / | \ Science Hobbies Collections / | \ Astronomy Amateurs_Astronomy Pictures / \ | Astrophysics Cosmology Astronomy / | \ Galaxies Stars Astronauts ``` 也會演示它強大的查詢方法。首先開啟ltree擴展。 ``` sudo -u postgres psql CREATE EXTENSION IF NOT EXISTS ltree; ``` 創建數據庫表。表名為test，字段名為path，類型指定為ltree。 ``` CREATE TABLE test (path ltree); ``` 插入數據。 ``` INSERT INTO test VALUES ('Top'); INSERT INTO test VALUES ('Top.Science'); INSERT INTO test VALUES ('Top.Science.Astronomy'); INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics'); INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology'); INSERT INTO test VALUES ('Top.Hobbies'); INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy'); INSERT INTO test VALUES ('Top.Collections'); INSERT INTO test VALUES ('Top.Collections.Pictures'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts'); ``` 因為要查詢，給數據表加上索引，索引有兩種，分別是btree和gist，GiST支持的操作符更為豐富些。具體可看官方文檔。 ``` CREATE INDEX path_gist_idx ON test USING gist(path); CREATE INDEX path_idx ON test USING btree(path); ``` 現在整張表的結果是這樣。 ``` rails365_pro=# select * from test; path ----------------------------------------------- Top Top.Science Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology Top.Hobbies Top.Hobbies.Amateurs_Astronomy Top.Collections Top.Collections.Pictures Top.Collections.Pictures.Astronomy Top.Collections.Pictures.Astronomy.Stars Top.Collections.Pictures.Astronomy.Galaxies Top.Collections.Pictures.Astronomy.Astronauts (13 rows) ``` 接下來演示查詢方法。先來演示第一個，再來介紹語法。 ``` rails365_pro=# SELECT path FROM test WHERE path ~ '*.Astronomy.*'; path ----------------------------------------------- Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology Top.Collections.Pictures.Astronomy Top.Collections.Pictures.Astronomy.Stars Top.Collections.Pictures.Astronomy.Galaxies Top.Collections.Pictures.Astronomy.Astronauts (7 rows) ``` 這樣會查找所有包含Astronomy的項。根據官方的解釋。語法大約是這樣的。 ``` SELECT path FROM test WHERE ltree 操作符 lquery; ``` `ltree`就是要查找的字段名。`~`就是操作符，官方列出了所有支持的操作符，也給了解釋。`lquery`是表示被匹配的正則表達式的字符串。 ``` rails365_pro=# SELECT path FROM test WHERE path <@ 'Top.Science'; path ------------------------------------ Top.Science Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology (4 rows) ``` `<@`的意思是"is left argument a descendant of right (or equal)?"，就是返回指定元素的后代啦，返回的結果正是我們期待的。只要懂得了語法，ltree支持的所有操作符只要看官方文檔的解釋就可以使用了。 ltree還支持函數。用于選擇和組合返回我們想要的結果。比如: ``` rails365_pro=# select * from test WHERE path <@ 'Top.Science.Astronomy'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology (3 rows) rails365_pro=# SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy'; ?column? ------------------------------------------ Top.Science.Space.Astronomy Top.Science.Space.Astronomy.Astrophysics Top.Science.Space.Astronomy.Cosmology (3 rows) ``` subpath的語法是這樣的。`subltree(ltree, int start, int end)`中`start`是起始位置(從0開始算)，`end`是結束位置，但不包含結束位置。`subpath(path,0,2)`返回的就是第一個元素和第二個,即`Top.Science.`。還有其他函數，看官方文檔的解釋就好了。 ltree介紹完了。 #### ltree\_hierarchy的使用 [ltree\_hierarchy](https://github.com/Leadformance/ltree_hierarchy)是一個ruby的gem，它實現了PostgreSQL的ltree的功能。提供了簡單的方法來實現樹型結構的功能。先安裝**ltree\_hierarchy**這個gem。 ``` gem 'ltree_hierarchy' ``` 然后執行`bundle`。我們用已存在的articles這張表來演示。 ``` # 20151010060005_add_ltree_to_articles.rb class AddLtreeToArticles < ActiveRecord::Migration def change enable_extension "ltree" add_column :articles, :parent_id, :integer, index: true add_column :articles, :path, :ltree end end ``` 執行`rake db:migrate`。在app/models/article.rb文件中添加下面那行。 ``` class Article < ActiveRecord::Base has_ltree_hierarchy end ``` ``` root = Article.create!(name: 'UK') child = Article.create!(name: 'London', parent: root) subchild = Article.create!(name: 'Hackney', parent: child) root.parent # => nil child.parent # => root root.children # => [child] root.children.first.children.first # => subchild subchild.root # => root ``` `parent_id`存的是交節點的id，像上述所說的，`path`存的是以"."分隔的id。除了`root`，`children`，`parent`，ltree\_hierarchy還實現了其他查詢方法。比如查葉子節點，查后代所有節點之類的。具體的查看官方的readme文件就好了。完結。