В этом посте мы хотели бы показать, как быстро генерировать табличные значения, основанные на значениях другого столбца той же таблицы. Для этой цели мы будем использовать Python Generator, являющийся частью dbForge Data Generator для SQL Server .
Сначала давайте создадим тестовую таблицу, используя следующий скрипт:
CREATE TABLE [dbo].[TestLinkedListTable](
[id] [int] NULL,
[name] [nchar](100) NULL,
[previous_id] [int] NULL,
[next_id] [int] NULL
)
GO
Затем мы запускаем мастер создания данных, выбираем генератор Python для столбца previous_id и вводим следующий скрипт:
import clr
clr.AddReference("System")
from System import DBNull
def main():
i = 0
while i != config["n_rows"]:
if (i == 0):
yield DBNull.Value
i = i + 1
yield i
main()
Для столбца next_id мы также выбираем генератор Python и вводим следующий скрипт:
import clr
clr.AddReference("System")
from System import DBNull
def main():
i = 2
while 1 == 1:
yield i
i = i + 1
if (i == config["n_rows"] + 1):
yield DBNull.Value
break
main()
Вуаля! В результате вышеописанных манипуляций у нас есть записи, где каждая запись ссылается на следующую запись, используя столбец next_id :
Это было хорошее начало, но давайте немного усложним задачу и попробуем сгенерировать несколько связанных списков разных размеров, тогда как каждый список должен быть связан с уникальным Родителем. И более того, значение в каждом дочернем элементе должно быть вычислено из столбца значения в его Parent.
Итак, еще раз, давайте создадим две таблицы:
CREATE TABLE [dbo].[TestLinkedListTableChild](
[id] [int] NULL,
[name] [nchar](100) NULL,
[previous_id] [int] NULL,
[next_id] [int] NULL,
[valueX] [int] NULL,
[commonParent] [int] NULL
)
CREATE TABLE [dbo].[Parent](
[id] [int] NULL,
[name] [nchar](100) NULL,
[value] [int] NULL
)
Затем мы запускаем мастер создания данных и очищаем опцию « Включить значения NULL» .
Давайте введем следующие коды Python:
- Для столбца previous_id :
import clr import random clr.AddReference("System") from System import DBNull clr.AddReference("System") from System import Random random = Random(1) def main(): i = 0 max_possible_count_in_chain = 5 while 1 == 1: items_count_in_chain = random.Next(1, max_possible_count_in_chain) while 1 == 1: if (items_count_in_chain == 0 or i == 0): yield DBNull.Value if (items_count_in_chain == 0): i+= 1 break i+= 1 yield i items_count_in_chain -= 1 main()
- Для столбца next_id :
import clr import random clr.AddReference("System") from System import DBNull clr.AddReference("System") from System import Random random = Random(1) def main(): i = 1 max_possible_count_in_chain = 5 while 1 == 1: items_count_in_chain = random.Next(1, max_possible_count_in_chain) while 1 == 1: if (items_count_in_chain == 0): yield DBNull.Value if (items_count_in_chain == 0): i+= 1 break i+= 1 yield i items_count_in_chain -= 1 main()
- Для таблицы commonParent :
import clr import random clr.AddReference("System") from System import DBNull clr.AddReference("System") from System import Random random = Random(1) def main(): i = 0 max_possible_count_in_chain = 5 while 1 == 1: i +=1 items_count_in_chain = random.Next(1, max_possible_count_in_chain) + 1 while 1 == 1: if (items_count_in_chain == 0): break yield i items_count_in_chain -= 1 main()
После этого мы генерируем скрипт и запускаем его для базы данных.
Чтобы удалить записи из родительской таблицы, у которых не было соответствующих записей в таблице TestLinkedListTableChild , мы запускаем следующий скрипт:
delete parent
where not exists
(select 1 from TestLinkedListTableChild tl where tl.commonParent = parent.id)
Так как последняя цепочка не может быть не закрыта,
Давайте запустим следующий скрипт, чтобы удалить последнюю поврежденную запись:
delete TestLinkedListTableChild where commonParent not in (
select commonParent from TestLinkedListTableChild tl
where exists (
select commonParent from TestLinkedListTableChild tl_child
where next_id is null and tl.commonParent = tl_child.commonParent
) and exists (
select commonParent from TestLinkedListTableChild tl_child
where previous_id is null and tl.commonParent = tl_child.commonParent
)
)
Наконец, мы получили желаемый результат — полностью корректные цепочки: