欧美亚洲综合久久,激情久久五月,欧美精品日韩三级

主頁 > 知識庫 > PostgreSQL實現一個通用標簽系統

PostgreSQL實現一個通用標簽系統

前言

對資源打標簽在建站過程中是很常見的需求，有些時候我們需要給文章打標簽，有些時候我們需要給用戶打標簽。實現一個標簽系統其實并不難，其本質就是一個多對多的關系-我可以對同一篇博客打多個標簽，同時也可以把一個標簽打到不同的博客身上。這篇文章主要通過分析標簽系統的原理，并用PostgreSQL來實現一個能夠為多種資源打標簽的標簽系統。

1. 單一資源標簽系統

先從單一資源開始，所謂單一資源便是，我們只給一種數據資源打標簽。假設我們需要給博客文章打標簽，那么我們需要構建以下幾個表：

文章表posts，用于存儲文章的基本信息。
標簽表tags，用于存儲標簽的基本信息。
標簽-文章表tags_posts，存儲雙方的id并形成多對多的關系。

表設計圖大概是

先進入數據庫引擎并創建對應的數據庫

postgres=# create database blog;
CREATE DATABASE

postgres=# \c blog;
blog=#

通過SQL語句創建上面所提到的數據表

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

每個表都只是包含了該資源最基礎的字段, 到這一步為止其實已經構建好了一個最簡單的標簽系統了。接下來則是填充數據，我的策略是添加兩篇文章，五個標簽，給標題為Ruby的文章打上language標簽，給標題為Docker的文章打上container的標簽，兩篇文章都要打上tech標簽

-- 填充文章數據
INSERT INTO posts (body, title) VALUES ('Hello Ruby', 'Ruby');
INSERT INTO posts (body, title) VALUES ('Hello Docker', 'Docker');

-- 填充標簽數據
INSERT INTO tags (name) VALUES ('language');
INSERT INTO tags (name) VALUES ('container');
INSERT INTO tags (name) VALUES ('tech');

-- 為相關資源打上標簽
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'));

然后分別查詢兩篇文章都被打上了什么標簽。

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)

兩篇文章都被打上期望的標簽了，相關的語句有點長，一般生產線上不會這樣直接操作數據庫。各種編程語言的社區一般都對這種數據庫操作進行了封裝，這為編寫業務代碼帶來了不少的便利性。

2. 為多種資源打標簽

如果只需要對一個數據表打標簽的話，依照上面的邏輯來設計表已經足夠了。但是現實世界往往沒那么簡單，假設除了要給博客文章打標簽之外，還需要給用戶表打標簽呢？我們需要把表設計得更靈活一些。如果繼續用tags表來存標簽數據，為了給用戶打標簽還得另外建一個名為tags_users的表來存儲標簽與用戶數據之間的關系。

但更好的做法應該是采用名為多態的設計。創建關聯表taggings，這個關聯表除了會存儲關聯的兩個id之外，還會存儲被打上標簽的資源類型，我們根據類型來區分被打標簽的到底是哪種資源，這會在每條記錄上多存了類型數據，不過好處就是可以少建表，所有的標簽關系都通過一個表來存儲。

Ruby比較流行的標簽系統ActsAsTaggableOn 就沿用了這個設計，不過它的類型字段直接存的是對應資源的類名，或許是為了更方便編程吧，數據大概如下：

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

先通過taggable_type獲取類名，然后再利用taggable_id的數據就能準確獲取相關的資源了。

a. 修改原表

表設計圖大概如下

這里我不重新建表了，而直接修改原有的表，并進行數據遷移

增加type字段用于存儲資源類型。
把原來的數據表改名為更通用的名字taggings。
把原來的post_id字段改成更通用的名字taggable_id。
給原有的資源填充數據，type字段統一填數據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. 添加用戶

在給用戶打標簽之前先創建用戶表，并填充數據

-- 創建簡單的用戶表
CREATE TABLE users (
 id    SERIAL,
 username  varchar(80),
 age    integer
);


-- 添加一個名為lan的用戶，并添加兩個相關的標簽

INSERT INTO users (username, age) values ('lan', 26);

INSERT INTO tags (name) VALUES ('student');
INSERT INTO tags (name) VALUES ('programmer');

c. 給用戶打標簽

接下來需要給用戶lan打上標簽，對原有的SQL語句做一些調整，并在打標簽的時候把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語句為用戶打上了student以及programmer兩個標簽。

d. 查看標簽情況

為了完成這個任務我們依然要聯合三張表進行查詢，同時還要約束type的類型

用戶名為lan的用戶被打上的所有標簽

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)

標題為Ruby的文章被打上的所有標簽

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，都跟預期一樣，現在的標簽系統就比較通用了。

總結

本文通過PostgreSQL的基礎語句來構建了一個標簽系統。實現了一個標簽系統其實并不難，各個語言的社區應該都有相關的集成。本人也就是想拋開編程語言，從數據庫層面來剖析一個標簽系統的基本原理。

PS: 另外推薦一個比較好用的Model Design工具dbdiagram，可以用文本的方式對數據表進行設計，邊設計邊預覽。最后還能以PNG，PDF甚至SQL源文件的形式導出。本文的數據表配圖均由用該軟件制作。

好了，以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

您可能感興趣的文章: