克隆分层数据 [英] cloning hierarchical data
问题描述
假设我有一个自我引用的层次表，构建这样的经典方式：
let's assume i have a self referencing hierarchical table build the classical way like this one:
CREATE TABLE test
(name text,id serial primary key,parent_id integer
references test);
insert into test (name,id,parent_id) values
('root1',1,NULL),('root2',2,NULL),('root1sub1',3,1),('root1sub2',4,1),('root
2sub1',5,2),('root2sub2',6,2);
testdb=# select * from test;
name | id | parent_id
-----------+----+-----------
root1 | 1 |
root2 | 2 |
root1sub1 | 3 | 1
root1sub2 | 4 | 1
root2sub1 | 5 | 2
root2sub2 | 6 | 2
我现在需要的是一个函数（最好在普通的sql中）测试记录和
克隆所有附加的记录（包括给定的记录）。克隆的记录当然需要有新的id。所需的结果
想要这样：例如：
What i need now is a function (preferrably in plain sql) that would take the id of a test record and clone all attached records (including the given one). The cloned records need to have new ids of course. The desired result would like this for example:
Select * from cloningfunction(2);
name | id | parent_id
-----------+----+-----------
root2 | 7 |
root2sub1 | 8 | 7
root2sub2 | 9 | 7
任何指针？我使用PostgreSQL 8.3。
Any pointers? Im using PostgreSQL 8.3.
推荐答案
递归拉这个结果是棘手的（尽管可能）。然而，它通常不是很有效率，而且有一个更好的方式来解决这个问题。
Pulling this result in recursively is tricky (although possible). However, it's typically not very efficient and there is a much better way to solve this problem.
基本上，你增加了一个额外的表列将树追溯到顶端 - 我将其称为Upchain。它只是一个长的字符串，看起来像这样：
Basically, you augment the table with an extra column which traces the tree to the top - I'll call it the "Upchain". It's just a long string that looks something like this:
name | id | parent_id | upchain
root1 | 1 | NULL | 1:
root2 | 2 | NULL | 2:
root1sub1 | 3 | 1 | 1:3:
root1sub2 | 4 | 1 | 1:4:
root2sub1 | 5 | 2 | 2:5:
root2sub2 | 6 | 2 | 2:6:
root1sub1sub1 | 7 | 3 | 1:3:7:
通过在桌面上使用触发器来保持此字段更新非常简单。 （对于术语抱歉，但我一直使用SQL Server）。每次添加或删除记录或更新parent_id字段时，只需要更新该部分树上的upchain字段即可。这是一个微不足道的工作，因为你只需要掌握父记录，并附加当前记录的id。所有的孩子记录都可以使用LIKE轻松识别，以检查其起始字符串的记录。
It's very easy to keep this field updated by using a trigger on the table. (Apologies for terminology but I have always done this with SQL Server). Every time you add or delete a record, or update the parent_id field, you just need to update the upchain field on that part of the tree. That's a trivial job because you just take the upchain of the parent record and append the id of the current record. All child records are easily identified using LIKE to check for records with the starting string in their upchain.
您正在做的有效的是交易一些额外的写入活动当您读取数据时，请保存大。
What you're doing effectively is trading a bit of extra write activity for a big saving when you come to read the data.
当您想在树中选择完整的分支时，这很简单。假设你想要在节点1下的分支。节点1有一个upchain'1：'，所以你知道该节点下的树的分支中的任何节点必须有一个起始于'1：...'的upchain。所以你只要这样做：
When you want to select a complete branch in the tree it's trivial. Suppose you want the branch under node 1. Node 1 has an upchain '1:' so you know that any node in the branch of the tree under that node must have an upchain starting '1:...'. So you just do this:
SELECT *
FROM table
WHERE upchain LIKE '1:%'
这是非常 fast（当然，索引upchain字段）。作为一个奖励，它还使很多活动非常简单，例如查找部分树，树中的级别等。
This is extremely fast (index the upchain field of course). As a bonus it also makes a lot of activities extremely simple, such as finding partial trees, level within the tree, etc.
我在跟踪的应用程序中使用了大型员工报告层次结构，但您可以使用它几乎任何树结构（零件细分等）。
I've used this in applications that track large employee reporting hierarchies but you can use it for pretty much any tree structure (parts breakdown, etc.)
注意（任何有兴趣的人）：
Notes (for anyone who's interested):
- 我没有给出SQL代码的一步一步，但一旦得到原则，实现起来就很简单。我不是一个伟大的程序员，所以我从经验来说。
- 如果你已经在表中有数据，你需要做一次更新，以便最初同步起动。再次，这并不困难，因为代码与触发器中的UPDATE代码非常相似。
- 这种技术也是识别循环引用的好方法，否则这些循环引用可能是棘手的。
这篇关于克隆分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
