PostgreSQL的Ltree模块是否适合线程评论？ [英] Is PostgreSQL's Ltree module a good fit for threaded comments?
问题描述
我正在考虑使用PostgreSQL的 Ltree模组我的应用程序帮助线程评论。我一直在使用它一段时间用于线程评论。我认为这将有助于您需要更新一个节点及其子节点的情况，例如当你想要隐藏一个评论及其回复。
我认为ltree（或类似的东西）如果它与传统的邻接列表（comment_id/parent_comment_id）耦合将是有用的。
在尝试使用ltree之前，我想知道一些事情：
- 你是或者是你，使用过ltree吗？
- 如果是这样，您使用它解决什么问题？
- 您认为这是否适合
线程评论系统？
- 如果你使用它，你对路径的文本部分使用了什么？你设置了像DMOZ的例子，他们使用Top.Astronomy.Cosmology或基于类似主键1.403.29.5？
- 有更好的方法做这个？我使用嵌套列表方法有点紧张 - 我读过的一切建议，它不是全部热与UPDATES或INSERTS（你不必重新排序整个事情？）。我也不是一个CS专业，这种数据结构是我可能在未来忘记的东西。是否有人使用嵌套列表进行评论或类似操作？
任何帮助，这里是我正在考虑的模式：
CREATE TABLE注释（
comment_id SERIAL PRIMARY KEY，
parent_comment_id int REFERENCES注释（comment_id）ON UPDATE CASCADE ON DELETE CASCADE，
thread_id int NOT NULL REFERENCES线程（thread_id）ON UPDATE CASCADE ON删除CASCADE，
路径ltree NOT NULL，
comment_body text NOT NULL，
hide boolean not null默认false
）;
ltree使用的路径列将类似于：
< thread_id>。< parent_comment_id_＃1>< parent_comment_id_＃2>< my_comment_id>
在路径中使用主键有什么问题吗？我应该在路径中包括节点自己的主键吗？
- 是和是;
- 知识库中的部分层次（其中一个实现）;
- 是;
如果我这样做，
有问题的表之一的定义：
Tableknowledgebase.section
Column |类型|修饰符
---------------------------- + ---------------- ---------- + --------------------------------------- --------------------------------------
section_sid |整数| not null default nextval（'knowledgebase.section_section_sid_seq':: regclass）
section |字符变化| not null
description |字符变化|
path | ltree | not null
is_active | boolean | not null default true
role_sid |整数| not null
last_modified_by |整数| not null
creation_datetime |带时区的时间戳| not null default now（）
last_modification_datetime |带时区的时间戳| not null default now（）
is_expanded | boolean | not null默认false
section_idx | tsvector |
索引：
section_sid_pkeyPRIMARY KEY，btree（section_sid）
section_section_keyUNIQUE，btree（section）
idxsection_idxgist（section_idx）
path_gist_idx gist（path）
外键约束：
last_modified_by_fkeyFOREIGN KEY（last_modified_by）REFERENCESuserrole（role_sid）ON UPDATE CASCADE ON DELETE RESTRICT
role_sid_fkey FOREIGN KEY（role_sid）REFERENCESuser。role（role_sid）ON UPDATE CASCADE ON DELETE RESTRICT
触发器：
section_idx_update BEFORE INSERT或UPDATE ON knowledgebase.section每个ROW执行过程tsearch2（'section_idx '，'section'）
path列使用主键作为标签。 p>
该表当前内容的示例（关于主键和路径列）：
section_sid |路径
------------- + -------
53 | 34.53
56 | 56
55 | 29.55
35 | 35
54 | 34.54
37 | 30.37
... | ...
I'm considering using PostgreSQL's Ltree module in my application to help with threaded comments. I've been eying it for a while to use for threaded comments. I figure it would help with cases where you need to update a node and its children, like when you want to hide a comment and its replies.
I'm thinking ltree (or something like it) it would be useful if it was coupled with a traditional adjacency list ("comment_id"/"parent_comment_id").
Before taking the plunge into using ltree, I'm wondering a few things:
- Are you, or have you, used ltree? Is it what one might call "production ready"?
- If so, what problems did you use it to solve? Did it do a good job?
- Do you think it is a good fit for a
threaded comment system?
- If you used it, what did you use for the "text" part of the path? Did you set up something like the DMOZ example they use "Top.Astronomy.Cosmology" or base it on something like the primary key "1.403.29.5"?
- Is there a better way to do this? I'm a bit nervous using a nested list approach--everything I've read suggests that it isn't all to hot with UPDATES or INSERTS (don't you have to reorder the whole thing?). I'm also not a CS major and that kind of data structure is something I might forget in the future. Is anybody using nested lists for comments or something like it?
If it is of any help, here is the schema I'm considering:
CREATE TABLE comments (
comment_id SERIAL PRIMARY KEY,
parent_comment_id int REFERENCES comments(comment_id) ON UPDATE CASCADE ON DELETE CASCADE,
thread_id int NOT NULL REFERENCES threads(thread_id) ON UPDATE CASCADE ON DELETE CASCADE,
path ltree NOT NULL,
comment_body text NOT NULL,
hide boolean not null default false
);
The "path" column, used by ltree, would look something like:
<thread_id>.<parent_comment_id_#1>.<parent_comment_id_#2>.<my_comment_id>
Is there anything wrong with using the primary keys in the path? Should I be including the node's own primary key in the path? If I did, would it make sense to put a unique index on it to serve as a constraint?
- Yes and yes;
- Hierarchy of sections in a knowledge base (one of the implementations);
- Yes;
The definition of one of the tables in question:
Table "knowledgebase.section"
Column | Type | Modifiers
----------------------------+--------------------------+-----------------------------------------------------------------------------
section_sid | integer | not null default nextval('knowledgebase.section_section_sid_seq'::regclass)
section | character varying | not null
description | character varying |
path | ltree | not null
is_active | boolean | not null default true
role_sid | integer | not null
last_modified_by | integer | not null
creation_datetime | timestamp with time zone | not null default now()
last_modification_datetime | timestamp with time zone | not null default now()
is_expanded | boolean | not null default false
section_idx | tsvector |
Indexes:
"section_sid_pkey" PRIMARY KEY, btree (section_sid)
"section_section_key" UNIQUE, btree (section)
"idxsection_idx" gist (section_idx)
"path_gist_idx" gist (path)
Foreign-key constraints:
"last_modified_by_fkey" FOREIGN KEY (last_modified_by) REFERENCES "user"."role"(role_sid) ON UPDATE CASCADE ON DELETE RESTRICT
"role_sid_fkey" FOREIGN KEY (role_sid) REFERENCES "user"."role"(role_sid) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
section_idx_update BEFORE INSERT OR UPDATE ON knowledgebase.section FOR EACH ROW EXECUTE PROCEDURE tsearch2('section_idx', 'section')
The "path" column uses the primary key as a label.
A sample of the current contents of that table (regarding the primary key and the "path" column):
section_sid | path
-------------+-------
53 | 34.53
56 | 56
55 | 29.55
35 | 35
54 | 34.54
37 | 30.37
... | ...
这篇关于PostgreSQL的Ltree模块是否适合线程评论？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
