PostgreSQL實(shí)現(xiàn)一個(gè)通用標(biāo)簽系統(tǒng)
前言
對(duì)資源打標(biāo)簽在建站過(guò)程中是很常見(jiàn)的需求,有些時(shí)候我們需要給文章打標(biāo)簽,有些時(shí)候我們需要給用戶打標(biāo)簽。實(shí)現(xiàn)一個(gè)標(biāo)簽系統(tǒng)其實(shí)并不難,其本質(zhì)就是一個(gè)多對(duì)多的關(guān)系-我可以對(duì)同一篇博客打多個(gè)標(biāo)簽,同時(shí)也可以把一個(gè)標(biāo)簽打到不同的博客身上。這篇文章主要通過(guò)分析標(biāo)簽系統(tǒng)的原理,并用PostgreSQL來(lái)實(shí)現(xiàn)一個(gè)能夠?yàn)槎喾N資源打標(biāo)簽的標(biāo)簽系統(tǒng)。
1. 單一資源標(biāo)簽系統(tǒng)
先從單一資源開(kāi)始,所謂單一資源便是,我們只給一種數(shù)據(jù)資源打標(biāo)簽。假設(shè)我們需要給博客文章打標(biāo)簽,那么我們需要構(gòu)建以下幾個(gè)表:
- 文章表posts,用于存儲(chǔ)文章的基本信息。
- 標(biāo)簽表tags,用于存儲(chǔ)標(biāo)簽的基本信息。
- 標(biāo)簽-文章表tags_posts,存儲(chǔ)雙方的id并形成多對(duì)多的關(guān)系。
表設(shè)計(jì)圖大概是
先進(jìn)入數(shù)據(jù)庫(kù)引擎并創(chuàng)建對(duì)應(yīng)的數(shù)據(jù)庫(kù)
postgres=# create database blog; CREATE DATABASE postgres=# \c blog; blog=#
通過(guò)SQL語(yǔ)句創(chuàng)建上面所提到的數(shù)據(jù)表
CREATE TABLE posts ( id SERIAL, body text, title varchar(80) ); CREATE TABLE tags ( id SERIAL, name varchar(80) ); CREATE TABLE tags_posts ( id SERIAL, tag_id integer, post_id integer );
每個(gè)表都只是包含了該資源最基礎(chǔ)的字段, 到這一步為止其實(shí)已經(jīng)構(gòu)建好了一個(gè)最簡(jiǎn)單的標(biāo)簽系統(tǒng)了。接下來(lái)則是填充數(shù)據(jù),我的策略是添加兩篇文章,五個(gè)標(biāo)簽,給標(biāo)題為Ruby的文章打上language標(biāo)簽,給標(biāo)題為Docker的文章打上container的標(biāo)簽,兩篇文章都要打上tech標(biāo)簽
-- 填充文章數(shù)據(jù) INSERT INTO posts (body, title) VALUES ('Hello Ruby', 'Ruby'); INSERT INTO posts (body, title) VALUES ('Hello Docker', 'Docker'); -- 填充標(biāo)簽數(shù)據(jù) INSERT INTO tags (name) VALUES ('language'); INSERT INTO tags (name) VALUES ('container'); INSERT INTO tags (name) VALUES ('tech'); -- 為相關(guān)資源打上標(biāo)簽 INSERT INTO tags_posts (tag_id, post_id) VALUES ((SELECT id FROM tags WHERE name = 'container'), (SELECT id FROM posts WHERE title = 'Docker')); INSERT INTO tags_posts (tag_id, post_id) VALUES ((SELECT id FROM tags WHERE name = 'tech'), (SELECT id FROM posts WHERE title = 'Docker')); INSERT INTO tags_posts (tag_id, post_id) VALUES ((SELECT id FROM tags WHERE name = 'tech'), (SELECT id FROM posts WHERE title = 'Ruby')); INSERT INTO tags_posts (tag_id, post_id) VALUES ((SELECT id FROM tags WHERE name = 'language'), (SELECT id FROM posts WHERE title = 'Ruby'));
然后分別查詢兩篇文章都被打上了什么標(biāo)簽。
blog=# SELECT tags.name FROM tags, posts, tags_posts WHERE tags.id = tags_posts.tag_id AND posts.id = tags_posts.post_id AND posts.title = 'Ruby'; name ---------- language tech (2 rows) blog=# SELECT tags.name FROM tags, posts, tags_posts WHERE tags.id = tags_posts.tag_id AND posts.id = tags_posts.post_id AND posts.title = 'Docker'; name ----------- container tech (2 rows)
兩篇文章都被打上期望的標(biāo)簽了,相關(guān)的語(yǔ)句有點(diǎn)長(zhǎng),一般生產(chǎn)線上不會(huì)這樣直接操作數(shù)據(jù)庫(kù)。各種編程語(yǔ)言的社區(qū)一般都對(duì)這種數(shù)據(jù)庫(kù)操作進(jìn)行了封裝,這為編寫業(yè)務(wù)代碼帶來(lái)了不少的便利性。
2. 為多種資源打標(biāo)簽
如果只需要對(duì)一個(gè)數(shù)據(jù)表打標(biāo)簽的話,依照上面的邏輯來(lái)設(shè)計(jì)表已經(jīng)足夠了。但是現(xiàn)實(shí)世界往往沒(méi)那么簡(jiǎn)單,假設(shè)除了要給博客文章打標(biāo)簽之外,還需要給用戶表打標(biāo)簽?zāi)??我們需要把表設(shè)計(jì)得更靈活一些。如果繼續(xù)用tags表來(lái)存標(biāo)簽數(shù)據(jù),為了給用戶打標(biāo)簽還得另外建一個(gè)名為tags_users的表來(lái)存儲(chǔ)標(biāo)簽與用戶數(shù)據(jù)之間的關(guān)系。
但更好的做法應(yīng)該是采用名為多態(tài)的設(shè)計(jì)。創(chuàng)建關(guān)聯(lián)表taggings,這個(gè)關(guān)聯(lián)表除了會(huì)存儲(chǔ)關(guān)聯(lián)的兩個(gè)id之外,還會(huì)存儲(chǔ)被打上標(biāo)簽的資源類型,我們根據(jù)類型來(lái)區(qū)分被打標(biāo)簽的到底是哪種資源,這會(huì)在每條記錄上多存了類型數(shù)據(jù),不過(guò)好處就是可以少建表,所有的標(biāo)簽關(guān)系都通過(guò)一個(gè)表來(lái)存儲(chǔ)。
Ruby比較流行的標(biāo)簽系統(tǒng)ActsAsTaggableOn 就沿用了這個(gè)設(shè)計(jì),不過(guò)它的類型字段直接存的是對(duì)應(yīng)資源的類名,或許是為了更方便編程吧,數(shù)據(jù)大概如下:
naive_development=# select id, tag_id, taggable_type, taggable_id from taggings; id | tag_id | taggable_type | taggable_id ----+--------+----------------------+------------- 1 | 1 | Refinery::Blog::Post | 1 2 | 2 | Refinery::Blog::Post | 1 3 | 3 | Refinery::Blog::Post | 1
先通過(guò)taggable_type獲取類名,然后再利用taggable_id的數(shù)據(jù)就能準(zhǔn)確獲取相關(guān)的資源了。
a. 修改原表
表設(shè)計(jì)圖大概如下
這里我不重新建表了,而直接修改原有的表,并進(jìn)行數(shù)據(jù)遷移
- 增加type字段用于存儲(chǔ)資源類型。
- 把原來(lái)的數(shù)據(jù)表改名為更通用的名字taggings。
- 把原來(lái)的post_id字段改成更通用的名字taggable_id。
- 給原有的資源填充數(shù)據(jù),type字段統(tǒng)一填數(shù)據(jù)post。
ALTER TABLE tags_posts ADD COLUMN type varchar(80); ALTER TABLE tags_posts RENAME TO taggings; ALTER TABLE taggings RENAME COLUMN post_id TO taggable_id; UPDATE taggings SET type='post';
b. 添加用戶
在給用戶打標(biāo)簽之前先創(chuàng)建用戶表,并填充數(shù)據(jù)
-- 創(chuàng)建簡(jiǎn)單的用戶表 CREATE TABLE users ( id SERIAL, username varchar(80), age integer ); -- 添加一個(gè)名為lan的用戶,并添加兩個(gè)相關(guān)的標(biāo)簽 INSERT INTO users (username, age) values ('lan', 26); INSERT INTO tags (name) VALUES ('student'); INSERT INTO tags (name) VALUES ('programmer');
c. 給用戶打標(biāo)簽
接下來(lái)需要給用戶lan打上標(biāo)簽,對(duì)原有的SQL語(yǔ)句做一些調(diào)整,并在打標(biāo)簽的時(shí)候把type字段填充為user。
INSERT INTO taggings (tag_id, taggable_id, type) VALUES ((SELECT id FROM tags WHERE name = 'student'), (SELECT id FROM users WHERE username = 'lan'), 'user'); INSERT INTO taggings (tag_id, taggable_id, type) VALUES ((SELECT id FROM tags WHERE name = 'programmer'), (SELECT id FROM users WHERE username = 'lan'), 'user');
上述的SQL語(yǔ)句為用戶打上了student以及programmer兩個(gè)標(biāo)簽。
d. 查看標(biāo)簽情況
為了完成這個(gè)任務(wù)我們依然要聯(lián)合三張表進(jìn)行查詢,同時(shí)還要約束type的類型
用戶名為lan的用戶被打上的所有標(biāo)簽
blog=# SELECT tags.name FROM tags, users, taggings WHERE tags.id = taggings.tag_id AND users.id = taggings.taggable_id AND taggings.type = 'user' AND users.username = 'lan'; name ------------ student programmer (2 rows)
標(biāo)題為Ruby的文章被打上的所有標(biāo)簽
blog=# SELECT tags.name FROM tags, posts, taggings WHERE tags.id = taggings.tag_id AND posts.id = taggings.taggable_id AND taggings.type = 'post' AND posts.title = 'Ruby'; name ---------- language tech
OK,都跟預(yù)期一樣,現(xiàn)在的標(biāo)簽系統(tǒng)就比較通用了。
總結(jié)
本文通過(guò)PostgreSQL的基礎(chǔ)語(yǔ)句來(lái)構(gòu)建了一個(gè)標(biāo)簽系統(tǒng)。實(shí)現(xiàn)了一個(gè)標(biāo)簽系統(tǒng)其實(shí)并不難,各個(gè)語(yǔ)言的社區(qū)應(yīng)該都有相關(guān)的集成。本人也就是想拋開(kāi)編程語(yǔ)言,從數(shù)據(jù)庫(kù)層面來(lái)剖析一個(gè)標(biāo)簽系統(tǒng)的基本原理。
PS: 另外推薦一個(gè)比較好用的Model Design工具dbdiagram,可以用文本的方式對(duì)數(shù)據(jù)表進(jìn)行設(shè)計(jì),邊設(shè)計(jì)邊預(yù)覽。最后還能以PNG,PDF甚至SQL源文件的形式導(dǎo)出。本文的數(shù)據(jù)表配圖均由用該軟件制作。
好了,以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問(wèn)大家可以留言交流,謝謝大家對(duì)腳本之家的支持。
相關(guān)文章
PostgreSQL分區(qū)表(partitioning)應(yīng)用實(shí)例詳解
這篇文章主要為大家詳細(xì)介紹了PostgreSQL分區(qū)表(partitioning)應(yīng)用實(shí)例,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2016-11-11postgreSQL自動(dòng)生成隨機(jī)數(shù)值的實(shí)例
這篇文章主要介紹了postgreSQL自動(dòng)生成隨機(jī)數(shù)值的實(shí)例,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01PostgreSQL教程(十二):角色和權(quán)限管理介紹
這篇文章主要介紹了PostgreSQL教程(十二):角色和權(quán)限管理介紹,本文講解了數(shù)據(jù)庫(kù)角色、角色屬性、權(quán)限、角色成員,需要的朋友可以參考下2015-05-05PostgreSQL創(chuàng)建自增序列、查詢序列及使用序列代碼示例
數(shù)據(jù)庫(kù)中主鍵的生成一般是通過(guò)序列來(lái)生成,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL創(chuàng)建自增序列、查詢序列及使用序列的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-11-11Postgresql 賦予用戶權(quán)限和撤銷權(quán)限的實(shí)例
這篇文章主要介紹了Postgresql 賦予用戶權(quán)限和撤銷權(quán)限的實(shí)例,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01postgresql數(shù)據(jù)庫(kù) timescaledb 時(shí)序庫(kù) 把大數(shù)據(jù)量表轉(zhuǎn)換為超表的問(wèn)題
這篇文章主要介紹了postgresql數(shù)據(jù)庫(kù) timescaledb 時(shí)序庫(kù) 把大數(shù)據(jù)量表轉(zhuǎn)換為超表,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-02-02SpringBoot連接使用PostgreSql數(shù)據(jù)庫(kù)的方法
這篇文章主要介紹了SpringBoot連接使用PostgreSql數(shù)據(jù)庫(kù)的方法,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01postgresql 查看當(dāng)前用戶名的實(shí)現(xiàn)
這篇文章主要介紹了postgresql 查看當(dāng)前用戶名的實(shí)現(xiàn),具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01