
Пример логической модели данных:

«Насетапим» немного данных.
У некоторых спортсменов нет категории, клуба или тренера (или они неизвестны).
Встречаются спортсмены, которых ведёт несколько тренеров.
Про одни сущности есть чуть больше информации, чем про другие.
Сначала удалим старые данные.
Ниже не очень хороший код:
- возможны нарушения целостности при отсутствии внешних ключей или исключения при их присутствии (из-за использования truncate table);
- используется схема по умолчанию, что чревато возможным удалением «чужих» данных (в одноименных таблицах, но другой схеме).
Вариант №1 (truncate):
In [0]: use tempdb go truncate table SwimmerCoach truncate table SwimmingClub truncate table Swimmer truncate table Category truncate table Coach go
Вариант №2 (delete):
In [1]: use tempdb go --clear first tables having no dependencies on others delete from dbo.SwimmerCoach go delete from dbo.SwimmingClub go delete from dbo.Swimmer go delete from dbo.Category go delete from dbo.Coach go
Вариант выше обладает недостатками: он медленный, привязанный к текущему набору таблиц и построен на знании текущих зависимостей между таблицами.
А что, если мы …
- хотим минимизировать число проблем, связанных с возможными изменениями в схеме?
- хотим иметь право выбора в отношении того, удалять ли «старые» данные перед вставкой новых?
- хотим выбирать из каких таблиц удалять данные?
- и делать это легко и, по возможности, без проблем с производительностью (то есть быстро)?
Хранимка ниже упрощает процесс очистки таблиц, причем позволяет запустить его в “отладочном” режиме, генерируя весь необходимый код с выводом последнего в окно стандартного вывода (на экран). Удаление данных – вещь серьезная и, в общем случае, неплохо иметь возможность контролировать этот процесс.
Предлагаем читателю самому разобраться с алгоритмом хранимой процедуры.
Обратим внимание лишь на то, что по умолчанию действует режим отладки (значение по умолчанию 1 параметра @debug), благодаря чему ничего не удаляется, но генерируется информативный набор SQL-команд с комментариями.
Вариант №3 (framework-procedure):
In [2]: use tempdb go if not exists(select 1 from INFORMATION_SCHEMA.SCHEMATA where [SCHEMA_NAME] = 'admin') exec('create schema [admin]') go if object_id('[admin].usp_ClearTables', 'P') is not null drop procedure [admin].usp_ClearTables go --------------------------------------------------------------------------------------- -- procedure removes data from given list of tables (input json-parameter) -- created by: Timofey Gavrilenko -- created date: 4/23/2019 -- sample call: -- exec [admin].usp_ClearTables N'["dbo.SwimmerCoach", "dbo.SwimmingClub", "dbo.Swimmer", "dbo.Category", "dbo.Coach"]' --------------------------------------------------------------------------------------- create procedure [admin].usp_ClearTables @list nvarchar(max) = null, @debug tinyint = 1 as begin set nocount on if @list is null return --table to store sql scripts create table #scripts ( id int not null identity primary key, [object_id] int not null, [table_name] sysname not null, add_sql nvarchar(max), drop_sql nvarchar(max), truncate_sql nvarchar(max) ) --get table list from input json create table #tables ( id int not null identity primary key, [table_name] sysname not null, [object_id] int ) insert into #tables([table_name], [object_id]) select [value], object_id([value]) from openjson(@list) --get drop-scripts insert into #scripts ([object_id], [table_name], drop_sql) select t.[object_id], t.[name], formatmessage( N'alter table %s.%s drop constraint %s', quotename(s.[name]), quotename(t.[name]), quotename(fk.[name]) ) from sys.foreign_keys fk join sys.tables t on fk.[parent_object_id] = t.[object_id] join sys.schemas s on s.[schema_id] = t.[schema_id] join #tables tl on tl.[object_id] = t.[object_id] order by t.[object_id] --get add-scripts update s set add_sql = q.add_sql, truncate_sql = case when exists( select 1 from #scripts where id > s.id and [table_name] = s.[table_name] ) then null else formatmessage( N'truncate table %s.%s', quotename(q.[schema_name]), quotename(q.[table_name]) ) end from #scripts s join ( select t2.[object_id], s2.[name] [schema_name], t2.[name] [table_name], formatmessage( N'alter table %s.%s add constraint %s foreign key (%s) references %s.%s (%s)', --alter table quotename(s2.[name]), quotename(t2.[name]), --add constraint quotename(fk.[name]), --foreign key stuff( ( select ',' + quotename(c.[name]) from sys.columns as c join sys.foreign_key_columns fkc on fkc.parent_column_id = c.column_id and fkc.parent_object_id = c.[object_id] where fkc.constraint_object_id = fk.[object_id] order by fkc.constraint_column_id for xml path(N''), type ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'' ), --references quotename(s1.[name]), quotename(t1.[name]), stuff( ( select ',' + quotename(c.[name]) from sys.columns as c join sys.foreign_key_columns fkc on fkc.referenced_column_id = c.column_id and fkc.referenced_object_id = c.[object_id] where fkc.constraint_object_id = fk.[object_id] order by fkc.constraint_column_id for xml path(N''), type ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'' ) ) add_sql from sys.foreign_keys fk join sys.tables t1 on fk.referenced_object_id = t1.[object_id] join sys.schemas s1 on t1.[schema_id] = s1.[schema_id] join sys.tables t2 on fk.parent_object_id = t2.[object_id] join sys.schemas s2 on t2.[schema_id] = s2.[schema_id] ) q on q.[object_id] = s.[object_id] --get list of sql scripts to execute in right order create table #scripts_in_order ( id int not null identity primary key, script nvarchar(max) not null ) insert into #scripts_in_order(script) select N'-- pass @debug = 0 for executing scripts' union all select N'-- drop all fk constraints...' union all select drop_sql from #scripts union all select N'-- truncate all tables...' union all select truncate_sql from #scripts where truncate_sql is not null union all select formatmessage(N'truncate table %s', t.table_name) from #tables t left join #scripts s on t.[object_id] = s.[object_id] where s.[object_id] is null union all select N'-- recreate fk constraints...' union all select add_sql from #scripts union all select N'-- done!' --run drop constraint, truncate table, add constraint scripts declare @id int = 1, @maxid int = @@rowcount, @sql nvarchar(max) begin try begin tran while @id <= @maxid begin select @sql = script from #scripts_in_order where id = @id if @debug <> 0 print @sql else execute(@sql) set @id +=1 end --while commit end try begin catch print 'Oops. There were issues when executing [admin].[usp_ClearTables]' rollback end catch end go
Теперь можно писать скрипт на вставку данных
Ниже не очень хороший код:
- всегда удаляются имеющиеся данные (а, возможно, мы не всегда этого хотим!);
- привязанность к текущей схеме данных, код слишком “низкоуровневый”;
- хардкод в отношении данных, значений primary и foreign ключей.
В случае любых изменений схемы данных (таких, как нормализация или переименование атрибутов) или самих данных (например, добавление дополнительных записей) код ниже придется переписать.
In [3]: use tempdb go --очистка данных exec [admin].usp_ClearTables N'["dbo.SwimmerCoach", "dbo.SwimmingClub", "dbo.Swimmer", "dbo.Category", "dbo.Coach"]', @debug = 0 go insert into dbo.SwimmingClub([Name], City) values (N'ГЦОР Трактор', N'Минск'), (N'ДЮСШ Янтарь', N'Минск'), (N'СК Олимпик-2011', N'Молодечно') insert into dbo.Category([Name]) values (N'1 юн'), (N'III'), (N'II'), (N'I'), (N'кмс'), (N'тренер 1 кат') insert into dbo.Swimmer(SwimmingClubID, CategoryId, FirstName, LastName, YearOfBirth, Gender) values (1, 3, N'Илья', N'Гавриленко', 2006, N'м'), (1, 3, N'Максим', N'Кликушин', 2007, N'м'), (2, 5, N'Анна', N'Высоцкая', 2006, N'ж'), (2, 3, N'Никита', N'Клюй', 2005, N'м'), (3, 4, N'Матвей', N'Данкевич', 2006, N'м') insert into dbo.Coach(FirstName, LastName) values (N'Алла', N'Усенок'), (N'Виталий', N'Барташевич'), (N'Евгения', N'Жукова') insert into dbo.SwimmerCoach(SwimmerID, CoachID) values (1, 1), (1, 2), (2, 1), (2, 2), (3, 3)
Попробуем сделать вставку данных более гибкой.
Идея:
- вставку осуществлять с помощью специальной хранимой процедуры;
- данные для вставки передавать в процедуру извне;
- помимо данных, передавать флаг очищать ли уже имеющиеся данные.
Передавать мы будем сериализованные данные с помощью JSON-параметра, что очень удобно для интеграции нескольких частей сложной программной системы. Так, например, мы можем вызывать хранимую процедуру после конвертации любых исходных форматов данных, в тот JSON, что она принимает на вход, или даже просто сериализовав в режиме реального времени объекты приложения, работающего с этой БД. JSON с данными в этом случае будет служить интерфейсом между приложением и БД.
В первую очередь, надо продумать структуру JSON.
Чтобы сделать это, нужно заглянуть в возможную объектную модель приложения или представить себе альтернативную, документ-ориентированную модель данных (эквивалентную текущей реляционной модели).
Допустим, в ходе дизайна схемы JSON, у нас получилось что-то такое (обратите внимание, наш случай довольно прост, зачастую мы не сможем обойтись лишь одним документом):
[ { "FirstName": "Илья", "LastName": "Гавриленко", "YearOfBirth": 2006, "Gender": "м", "Club": { "Name": "ГЦОР Трактор", "City": "Минск" }, "Category": "II", "Coach": [ {"FirstName": "Алла", "LastName": "Усенок"}, {"FirstName": "Виталий", "LastName": "Барташевич"} ] }, { "FirstName": "Максим", "LastName": "Кликушин", "YearOfBirth": 2007, "Gender": "м", "Club": { "Name": "ГЦОР Трактор", "City": "Минск" }, "Category": "II", "Coach": [ {"FirstName": "Алла", "LastName": "Усенок"}, {"FirstName": "Виталий", "LastName": "Барташевич"} ] }, { "FirstName": "Анна", "LastName": "Высоцкая", "YearOfBirth": 2006, "Gender": "ж", "Club": { "Name": "ДЮСШ Янтарь", "City": "Минск" }, "Category": "кмс", "Coach": [ {"FirstName": "Евгения", "LastName": "Жукова"} ] }, { "FirstName": "Никита", "LastName": "Клюй", "YearOfBirth": 2005, "Gender": "м", "Club": { "Name": "ДЮСШ Янтарь", "City": "Минск" }, "Category": "II" }, { "FirstName": "Матвей", "LastName": "Данкевич", "YearOfBirth": 2006, "Gender": "м", "Club": { "Name": "СК Олимпик-2011", "City": "Молодечно" }, "Category": "I" } ]
Теперь нам нужно создать процедуру импорта данных, способную разобрать подобный JSON и вставить данные в множество связанных между собой реляционных таблиц.
Можно создать одну большую процедуру импорта, выполняющую всю работу.
Минусами этого подхода будут:
- привязанность одновременно и к схеме реляционной БД, и к схеме JSON;
- высокая сложность одного программного объекта и необходимость его рефакторинга при любых изменениях требований или багах;
- отсутствие возможности «тонкого тюнинга» и вставки более гранулированных данных (например, клубы или тренеры без привязки к спортсменам);
- трудности распараллеливания работы над несколькими сущностями.
Альтернативным (и, вероятно, предпочтительным в общем случае вариантом) будет создание “мелких” хранимых процедур на вставку спортсменов, тренеров, клубов, категорий и одной управляющей процедуры, парсящей входящий “большой” JSON и вызывающей в нужном порядке эти “мелкие” процедуры узкого назначения.
В нашем случае мы реализуем первый вариант.
In [3]: use tempdb go if object_id('dbo.usp_ImportSwimmersData', 'P') is not null drop procedure dbo.usp_ImportSwimmersData go --------------------------------------------------------------------------------------- -- procedure imports data from incoming JSON-parameter into a number of relation tables -- created by: Timofey Gavrilenko -- created date: 4/26/2019 -- sample call: -- exec dbo.usp_ImportSwimmersData @parameters = --N' --[ -- { -- "FirstName": "Илья", -- "LastName": "Гавриленко", -- "YearOfBirth": 2006, -- "Gender": "м", -- "Club": { -- "Name": "ГЦОР Трактор", -- "City": "Минск" -- }, -- "Category": "II", -- "Coach": [ -- {"FirstName": "Алла", "LastName": "Усенок"}, -- {"FirstName": "Виталий", "LastName": "Барташевич"} -- ] -- } --]' --------------------------------------------------------------------------------------- create procedure dbo.usp_ImportSwimmersData @parameters nvarchar(max) = null as begin set nocount on --if @parameters is null -- return create table #Swimmer ( id int not null identity primary key, FirstName nvarchar(20) not null, LastName nvarchar(30) not null, YearOfBirth smallint not null, Gender nchar(1) not null, Category nvarchar(20), Club nvarchar(max), Coach nvarchar(max) ) create table #Club ( id int not null identity primary key, [Name] nvarchar(100) not null, City nvarchar(30) not null, [Address] nvarchar(200), Phone nvarchar(15), YearEstablished smallint, ) create table #Coach ( id int not null identity primary key, FirstName nvarchar(20) not null, LastName nvarchar(30) not null, DateOfBirth date, Category nvarchar(20) ) --get list of swimmers from json insert into #Swimmer(FirstName, LastName, YearOfBirth, Gender, Category, Club, Coach) select * from openjson(@parameters) with ( FirstName nvarchar(20) N'$.FirstName', LastName nvarchar(30) N'$.LastName', YearOfBirth smallint N'$.YearOfBirth', Gender nchar(1) N'$.Gender', Category nvarchar(20) N'$.Category', Club nvarchar(max) N'$.Club' as json, Coach nvarchar(max) N'$.Coach' as json ) js --get list of clubs from json insert into #Club([Name], City, [Address], Phone, YearEstablished) select distinct jc.[Name], jc.City, jc.[Address], jc.Phone, jc.YearEstablished from #Swimmer s cross apply openjson(s.Club) with ( [Name] nvarchar(100) N'$.Name', City nvarchar(30) N'$.City', [Address] nvarchar(200) N'$.Address', Phone nvarchar(15) N'$.Phone', YearEstablished smallint N'$.YearEstablished' ) jc --get list of coaches from json insert into #Coach(FirstName, LastName, DateOfBirth, Category) select distinct jc.FirstName, jc.LastName, jc.DateOfBirth, jc.Category from #Swimmer s cross apply openjson(s.Coach) with ( FirstName nvarchar(20) N'$.FirstName', LastName nvarchar(30) N'$.LastName', DateOfBirth date N'$.DateOfBirth', Category nvarchar(20) N'$.Category' ) jc --let's first insert what is easy (data without foreign keys) --insert new categories insert into dbo.Category ([Name]) select jc.[Name] from ( select Category as [Name] from #Swimmer union select Category from #Coach ) jc left join dbo.Category c on jc.[Name] = c.[Name] where c.CategoryID is null and jc.[Name] is not null --below we'll use natural/alternative keys from JSON data to get ids (foreign keys) --insert new clubs insert into dbo.SwimmingClub ([Name], City, [Address], Phone, YearEstablished) select jsc.[Name], jsc.City, jsc.[Address], jsc.Phone, jsc.YearEstablished from ( select distinct [Name], City, [Address], Phone, YearEstablished from #Club ) jsc left join dbo.SwimmingClub sc on jsc.[Name] = sc.[Name] and jsc.City = sc.City where sc.SwimmingClubID is null --insert new coaches insert into dbo.Coach (FirstName, LastName, DateOfBirth, CategoryID) select jc.FirstName, jc.LastName, jc.DateOfBirth, jc.CategoryID from ( --dbo.Coach table has dependency on dbo.Category (not required however) select distinct FirstName, LastName, DateOfBirth, c.CategoryID from #Coach left join dbo.Category c on #Coach.Category = c.[Name] ) jc left join dbo.Coach c on jc.FirstName = c.FirstName and jc.LastName = c.LastName where c.CoachID is null --insert new swimmers insert into dbo.Swimmer (FirstName, LastName, YearOfBirth, Gender, SwimmingClubID, CategoryID) select js.FirstName, js.LastName, js.YearOfBirth, js.Gender, js.SwimmingClubID, js.CategoryID from ( --dbo.Swimmer table has dependency on dbo.Club (not required) select distinct FirstName, LastName, YearOfBirth, Gender, sc.SwimmingClubID, c.CategoryID from #Swimmer s left join dbo.Category c on s.Category = c.[Name] outer apply openjson(s.Club) with ( [Name] nvarchar(100) N'$.Name', City nvarchar(30) N'$.City' ) jsc left join dbo.SwimmingClub sc on jsc.[Name] = sc.[Name] and jsc.City = sc.City ) js left join dbo.Swimmer s on js.FirstName = s.FirstName and js.LastName = s.LastName and js.YearOfBirth = s.YearOfBirth and js.Gender = s.Gender where s.SwimmerID is null --insert new relationships between swimmers and coaches insert into dbo.SwimmerCoach (SwimmerID, CoachID) select s.SwimmerID, c.CoachID from #Swimmer js join dbo.Swimmer s on js.FirstName = s.FirstName and js.LastName = s.LastName and js.YearOfBirth = s.YearOfBirth and js.Gender = s.Gender cross apply openjson(js.Coach) with ( FirstName nvarchar(20) N'$.FirstName', LastName nvarchar(30) N'$.LastName' ) jc join dbo.Coach c on jc.FirstName = c.FirstName and jc.LastName = c.LastName left join dbo.SwimmerCoach sc on s.SwimmerID = sc.SwimmerID and c.CoachID = sc.CoachID where sc.SwimmerID is null and sc.CoachID is null end go
Теперь мы можем добиться инкапсулирования данных и отделения backend-а в виде БД от приложения со всеми вытекающими “плюшками”:
- возможностью внесения изменений, как на стороне приложения, так и на стороне БД, до тех пор, пока сохраняется формат интерфейса данных (JSON-схема);
- возможностью наполнять БД данными из любых источников, если мы в состоянии предварительно сериализовать их в JSON той схемы, что принимает на вход хранимая процедура;
- отделением модели БД от самих данных. Данные можно вставлять несколькими процессами в параллели, в любой последовательности, в том числе в уже непустую БД;
- при желании, можно создать вспомогательное приложение генерации данных в формате JSON с заданными параметрами и автоматизировать наполнение БД на тестовом сервере (тема отдельной статьи).
Автор материала – Тимофей Гавриленко, преподаватель Тренинг-центра ISsoft.

Образование: окончил с отличием математический факультет Гомельского Государственного Университета им. Франциска Скорины.
Microsoft Certified Professional (70-464, 70-465).
Работа: c 2011 года работает в компании ISsoft (ETL/BI Developer, Release Manager, Data Analyst/Architect, SQL Training Manager), на протяжении 10 лет до этого выступал как Sysadmin, DBA, Software Engineer.
В свободное время ведет бесплатные образовательные курсы для детей и взрослых, желающих повысить свой уровень компьютерной грамотности и переквалифицироваться в IT-специалиста.