Я хочу рассказать о рефакторинге схем баз данных MS SQL Server.
Рефакторинг — изменение во внутренней структуре программного обеспечения, имеющее целью облегчить понимание его работы и упростить модификацию, не затрагивая наблюдаемого поведения.
— Martin Fowler
О рефакторинге кода говорят уже давно. На данный момент написано немало литературы, создано множество инструментов, помогающих выполнять рефакторинг кода.
А вот про рефакторинг схем баз данных не так уж и много информации. Я решил немного восполнить этот пробел и поделиться своим опытом.
Как понять что настала пора проводить рефакторинг?
Делая что-то в первый раз, вы просто это делаете. Делая что-то аналогичное во второй раз, вы морщитесь от необходимости повторения, но все-таки повторяете то же самое. Делая что-то похожее в третий раз, вы начинаете рефакторинг.
— Don Roberts
Мартин Фаулер ввел понятие «Код с душком», обозначив так код который нужно подвергнуть рефакторингу.
С душком называется код в котором:
- встречается дублирование
- есть большие методы
- существуют методы с большим количеством параметров
- встречается оператор switch
По аналогии с этим можно выделить общие недостатки схемы базы данных, которые указывают на необходимость применения рефакторинга. К этим недостаткам можно выделить следующие.
- Многоцелевые столбцы (или столбцы используемые не по назначению). Допустим у нас есть таблица содержащая информацию по заказам. В таблице есть необязательное для заполнения поле InvoiceId типа int. Представим что процесс продаж в компании построен таким образом, что это поле никогда не заполняется. Начиная с нового года менеджерам стало необходимо проставлять у заказов оценку клиента (от 1 до 10 по результатам обзвона). Такого поля в таблице нет и менеджеры начинают вбивать эти данные в поле InvoiceId (например потому, что IT-шники сказали им что на добавление нового поля уйдет целый месяц). Это приведет к проблемам когда поле InvoiceId станет использоваться по назначению.
- Многоцелевые таблицы. Примером может послужить таблица Customer в которой хранится информация о физических и юридических лицах. В подобном случае неизбежно появляются столбы с NULL значениями.
- Избыточные данные. Например, наличие поля Адрес клиента в таблице заказов может привести к случаю когда у нескольких заказов одного и того же клиента будут разные адреса.
- Таблицы с большим количеством столбцов. Наличие большого количества столбцов может означать что в таблице хранятся атрибуты более чем одной сущности. В таком случае вероятно нужно применить рефакторинг «Разбиение таблицы».
- Многозначные столбцы. Многозначными называются столбцы, в которых в различных позициях представлено несколько разных фрагментов информации. Например в таблице заказов есть поле OrderNumber содержащее данные вида XXX20150908000125. Где XXX — код товара, 20150908 — дата заказа, 000125 — порядковый номер заказа. На практике часто обнаруживается необходимость разбить поле на части, чтобы можно было проще обрабатывать эти поля в виде отдельных элементов.
Несколько полезных советов по применению рефакторинга
- Не делайте много изменений за один раз. Чем меньше будет изменение, тем проще будет найти ошибку в случае сбоя.
- Проверяйте изменения тестами. После каждого изменения запускайте тесты, чтобы убедиться что ничего не сломалось.
- Используйте песочницы. Не нужно заниматься рефакторингом на продуктиве, даже если изменение ничтожно мало. Используйте для рефакторинга тестовые площадки. После чего проводите полное регрессионное тестирование. И только после этого выполняйте изменение в продуктивной базе данных.
Практические примеры
Я покажу применение некоторых методов рефакторинга на примере базы данных Northwind (ссылка на скачивание).
В качестве инструмента я буду использовать SQL Server Management Studio (SSMS) с установленным плагином SQL Refactor Studio. Данный плагин добавляет в SSMS функции рефакторинга.
Исходная схема
Тестирование
После каждого изменения мы будем запускать тест, чтобы убедиться что все по прежнему работает.
Для примера я создал процедуру dbo.RunTests, которая выбирает данные из всех представлений в базе (разумеется это не обеспечивает нам полное покрытие тестами).
Если в процессе работы процедуры не было ошибок, процедура выдает OK, иначе Failed.
CREATE PROCEDURE dbo.RunTests AS DECLARE @Script nvarchar(max) = '', @Failed bit = 0 DECLARE crs CURSOR FOR SELECT 'IF OBJECT_ID(''tempdb..#tmp'') IS NOT NULL DROP TABLE #tmp SELECT * INTO #tmp FROM [' + object_schema_name(o.object_id) + '].[' + o.name + ']' FROM sys.objects o WHERE o.type = 'V' OPEN crs FETCH NEXT FROM crs INTO @Script WHILE @@fetch_status = 0 BEGIN BEGIN TRY EXEC sp_executesql @Script END TRY BEGIN CATCH SET @Failed = 1 SELECT 'Failed' AS Status, ERROR_MESSAGE() AS Details, @Script AS [Script] END CATCH FETCH NEXT FROM crs INTO @Script END CLOSE crs DEALLOCATE crs IF @Failed = 0 SELECT 'OK' AS [Status] RETURN 0 /* EXEC dbo.RunTests */ GO Рефакторинг «Переименование объекта»
Не знаю конечно как Вы, но я при создании таблицы даю ей имя в единственном числе (dbo.Entry а не dbo.Entries).
Итак, давайте попробуем переименовать таблицу dbo.Customers в dbo.Customer. Тут есть один неприятный (и очень рутинный) процесс. Нужно переименовать таблицу так чтобы не сломался код использующий ее. Для этого его нужно найти и внести в него исправление. Воспользовавшись стандартным View Dependencies видим что таблица используется в одном преставлении и есть две таблицы ссылающиеся на dbo.Customers.
В принципе, внести исправление в одно представление после переименования таблицы — плевое дело.
Ну что же, в бой! Переименовываем таблицу и запускаем тест (должна сломаться вьюха dbo.Customer and Suppliers by City).
Однако, вместо ожидаемой одной строки тест мне выдал целых пять.
Тогда я решил проверить зависимости таблицы используя расширенный View Dependencies входящий в состав пакета SQL Refactor Studio. Он уже насчитал пять вьюшек (тест показал что сломалось именно пять представлений) и нашел одну процедуру (не покрыта тестами).
Шесть объектов — это уже посерьезнее. А представьте себе что Вам нужно поправить код в 50+ объектах. Вы все еще хотите переименовать таблицу? 🙂 Ручками будет уже тяжело, поэтому, прибегнем к автоматизации.
Воспользуемся функцией Rename входящий в пакет SQL Refactor Studio. Выбираем таблицу в Object Explorer (далее OE), из контекстного меню выбираем пункт SQL Refactor Studio -> Rename. Вводим новое имя (Customer) и нажимаем кнопку Genarate rename script. Тут также есть возможность посмотреть зависимости и снять галочки напротив объектов в которых не нужно производить переименование.
В результате открылась новая вкладка с сформированным скриптом.
use northwind go set transaction isolation level serializable set xact_abort on go if object_id('tempdb..#err') is not null drop table #err go create table #err(flag bit) go begin transaction go exec sp_rename 'dbo.Customers', 'Customer', 'OBJECT' go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.[Orders Qry] AS SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customer.CompanyName, Customer.Address, Customer.City, Customer.Region, Customer.PostalCode, Customer.Country FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID go raiserror('update view <dbo.Orders Qry>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.[Quarterly Orders] AS SELECT DISTINCT Customer.CustomerID, Customer.CompanyName, Customer.City, Customer.Country FROM Customer RIGHT JOIN Orders ON Customer.CustomerID = Orders.CustomerID WHERE Orders.OrderDate BETWEEN '19970101' And '19971231' go raiserror('update view <dbo.Quarterly Orders>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.Invoices AS SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customer.CompanyName AS CustomerName, Customer.Address, Customer.City, Customer.Region, Customer.PostalCode, Customer.Country, (FirstName + ' ' + LastName) AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight FROM Shippers INNER JOIN (Products INNER JOIN ( (Employees INNER JOIN (Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID) ON Shippers.ShipperID = Orders.ShipVia go raiserror('update view <dbo.Invoices>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER PROCEDURE dbo.CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customer C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName go raiserror('update storedprocedure <dbo.CustOrderHist>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.[Customer and Suppliers by City] AS SELECT City, CompanyName, ContactName, 'Customers' AS Relationship FROM Customer UNION SELECT City, CompanyName, ContactName, 'Suppliers' FROM Suppliers --ORDER BY City, CompanyName go raiserror('update view <dbo.Customer and Suppliers by City>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.[Sales Totals by Amount] AS SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customer.CompanyName, Orders.ShippedDate FROM Customer INNER JOIN (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) ON Customer.CustomerID = Orders.CustomerID WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231') go raiserror('update view <dbo.Sales Totals by Amount>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go if exists (select * from #err) begin print 'the database <northwind> update failed' rollback transaction end else begin print 'the database <northwind> update succeeded' commit transaction end go
Запускаем скрипт на выполнение.
Запускаем тест.
Вуаля! Мы переименовали таблицу и не сломали существующий код.
Поступим также со всеми таблицами, имена которых заканчиваются на s. Это ведь так просто, неправда ли?
Рефакторинг «Добавление поисковой таблицы»
Эта операция позволяет создать поисковую таблицу для существующего столбца.
Необходимость этой операции может быть обусловлена следующими причинами:
- Введение ссылочной целостности для обеспечения качества данных;
- Предоставление подробных описаний. Например, может понадобится добавить в описание той или иной сущности новый атрибут. Если сущность при этой не выделена в таблицу — придется добавлять этот атрибут в нужные таблицы, что приведет к денормализации схемы.
Посмотрим еще раз на нашу таблицу dbo.Customer. Вас не смущает наличие полей City, Region и Country в одном месте? Это ведь атрибуты одной сущности.
В таблице dbo.Employees та же беда. На вид явное нарушение 3-й нормальной формы.
Давайте начинать исправлять дело следующим образом:
1. Создадим справочник dbo.City(CityId, CityName)
2. В таблице dbo.Customer добавим поле CityId.
3. Создадим внешний ключ.
Опять же, для экономии времени, используем функцию Add Lookup Table. В OE выбираем поле City таблицы dbo.Customer и в контекстном меню вызываем пункт SQL Refactor Studio -> Add Lookup Table.
В появившемся окне заполняем поля. Жмем Next, потом Finish и в новом окне формируется скрипт.
Скрипт создает таблицу dbo.City, заполняет ее данными, создает поле CityId в таблице dbo.Customer, создает внешний ключ.
use northwind go -- Step 1. Create lookup table. CREATE TABLE dbo.City ( CityId INT NOT NULL identity(1, 1) ,CityName NVARCHAR(15) NULL ,CONSTRAINT PK_City PRIMARY KEY CLUSTERED (CityId) ,CONSTRAINT City_ixCityName UNIQUE (CityName) ) GO -- Step 2. Fill lookup table. INSERT dbo.City (CityName) SELECT DISTINCT City FROM dbo.Customer GO -- Step 3. Add column. ALTER TABLE dbo.Customer ADD CityId INT NULL GO -- Step 4. Update table dbo.Customer. UPDATE s SET s.CityId = t.CityId FROM dbo.Customer s INNER JOIN dbo.City t ON s.City = t.CityName GO -- Step 5. Create foreign key constraint. ALTER TABLE dbo.Customer ADD CONSTRAINT FK_Customer_City FOREIGN KEY (CityId) REFERENCES dbo.City (CityId) GO
Запускаем скрипт и тесты (хотя тут мы вроде ничего не должны были сломать).
Все готово. Идем далее.
Рефакторинг «Перемещение полей»
Итак, с полем City мы разобрались. Осталось разобраться с полями Region и Country. Данные поля являются атрибутами сущности City. Так давайте же перенесем их из таблицы dbo.Customer в dbo.City.
Опять же, SQL Refactor Studio предоставляет функцию Move Columns. Ею и воспользуемся!
Выбираем в OE таблицу dbo.Customer, в контекстном меню выбираем пункт «SQL Refactor Studio -> Move columns». В появившемся диалоге, в выпадающем списке, выбираем таблицу dbo.City. Переносим поля Region и Country в dbo.City.
Если при переносе поля, вы получите сообщение о том, что нельзя переместить поле на котором построен индекс — удалите на время этот индекс.
Нажимаем Next, потом Finish. Получаем скрипт в новом окне.
Скрипт создает поля Region и Country в таблице dbo.City и заполняет их данными.
В скрипте есть также закомментированный код удаляющий поля и приведен список объектов в которых нужно внести изменения.
Давайте не будем сейчас удалять поля, сделаем это на следующем шаге.
USE northwind GO -- STEP 1. Add new column(s) -- IF NOT EXISTS ( SELECT * FROM syscolumns s WHERE s.NAME = 'Region' AND s.id = object_id(N'dbo.City') ) BEGIN ALTER TABLE dbo.City ADD Region NVARCHAR(15) NULL END GO IF NOT EXISTS ( SELECT * FROM syscolumns s WHERE s.NAME = 'Country' AND s.id = object_id(N'dbo.City') ) BEGIN ALTER TABLE dbo.City ADD Country NVARCHAR(15) NULL END GO GO -- STEP 2. Copy data -- -- (You can modify this query if needed) SET IDENTITY_INSERT dbo.City ON INSERT INTO dbo.City WITH (TABLOCKX) (CityId) SELECT CityId FROM dbo.Customer src WHERE NOT EXISTS ( SELECT * FROM dbo.City dest WHERE src.CityId = dest.CityId ) SET IDENTITY_INSERT dbo.City OFF UPDATE dest WITH (TABLOCKX) SET dest.Region = src.Region ,dest.Country = src.Country FROM dbo.City dest INNER JOIN dbo.Customer src ON (src.CityId = dest.CityId) GO -- STEP 3. Check and modify this dependent objects -- /* northwind.dbo.[Orders Qry] /*View*/ northwind.dbo.[Quarterly Orders] /*View*/ northwind.dbo.Invoices /*View*/ northwind.dbo.CustOrderHist /*StoredProcedure*/ northwind.dbo.[Customer and Suppliers by City] /*View*/ northwind.dbo.[Sales Totals by Amount] /*View*/ */ -- STEP 4. Drop column(s) -- -- (Uncomment or run separately this query) /* alter table dbo.Customer drop column Region alter table dbo.Customer drop column Country */ GO
Выполняем скрипт и тесты.
Переходим к следующему шагу.
Рефакторинг «Удаление объекта»
Выполняя предыдущие рефакторинги, мы оставили немного мусора (поля City, Region, Country в таблице dbo.Customer).
Давайте наводить чистоту! Но если мы просто так удалим поля, у нас опять все сломается.
Можно воспользоваться рефакторингом Encapsulate Table With View.
Создадим представление dbo.CustomerV и заменим использование таблицы его представлением во всей базе данных.
CREATE VIEW dbo.CustomerV AS SELECT c.CustomerID, c.CompanyName, c.ContactName, c.ContactTitle, c.Address, ct.CityName City, ct.Region, c.PostalCode, ct.Country, c.Phone, c.Fax, c.CityId FROM dbo.Customer AS c LEFT JOIN dbo.City AS ct ON c.CityId = ct.CityId Далее, при помощи View Dependencies смотрим зависимости для таблицы dbo.Customer:
Просматриваем каждый объект. Если в каком-либо объекте используются наши поля, скриптуем объект (кнопка Script object на тулбаре) и вносим изменения.
В результате у меня получился вот такой скрипт:
ALTER view dbo.[Sales Totals by Amount] AS SELECT st.Subtotal AS SaleAmount, o.OrderID, c.CompanyName, o.ShippedDate FROM dbo.CustomerV c JOIN ( dbo.Orders o JOIN "Order Subtotals" st ON o.OrderID = st.OrderID ) ON c.CustomerID = o.CustomerID WHERE (st.Subtotal >2500) AND (o.ShippedDate BETWEEN '19970101' And '19971231') GO ALTER view dbo.[Quarterly Orders] AS SELECT DISTINCT c.CustomerID, c.CompanyName, c.City, c.Country FROM dbo.CustomerV c RIGHT JOIN dbo.Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate BETWEEN '19970101' And '19971231' GO ALTER view dbo.[Orders Qry] AS SELECT o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity, o.ShipRegion, o.ShipPostalCode, o.ShipCountry, c.CompanyName, c.Address, c.City, c.Region, c.PostalCode, c.Country FROM dbo.CustomerV c INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID GO ALTER view dbo.Invoices AS SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customer.CompanyName AS CustomerName, Customer.Address, Customer.City, Customer.Region, Customer.PostalCode, Customer.Country, (FirstName + ' ' + LastName) AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, (CONVERT(money,("Order Details".UnitPrice * Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight FROM Shippers INNER JOIN (Products INNER JOIN ( (Employees INNER JOIN (dbo.CustomerV Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID) ON Shippers.ShipperID = Orders.ShipVia GO ALTER view dbo.[Customer and Suppliers by City] AS SELECT City, CompanyName, ContactName, 'Customers' AS Relationship FROM dbo.CustomerV UNION SELECT City, CompanyName, ContactName, 'Suppliers' FROM Suppliers --ORDER BY City, CompanyName GO
Запускаем скрипт, после чего удаляем поля и прогоняем тесты.
Если все ОК — Вы молодец, все аккуратно сделали.
Рефакторинг «Добавление методов CRUD»
Данный рефакторинг предусматривает создание хранимых процедур, обеспечивающих доступ (SELECT, INSERT, UPDATE, DELETE) к таблицам базы данных.
Можно выделить следующие причины использовать этот рефакторинг:
- Скрыть от внешних приложений структуру базы данных. Внешние приложения будут обращаться к базе данных только посредством хранимых процедур. Это позволит с легкостью изменять структуру базы данных без изменения внешних приложений.
- Реализация в процедурах дополнительных проверок (бизнес-логики, прав доступа и т.д.);
- Сохранение информации о том кто и когда какие данные запрашивал;
- Проблемы с производительностью эффективнее решать когда SQL-код находится в базе данных.
Итак, давайте создадим для нашей замученной таблицы dbo.Customer методы доступа. Воспользуемся методом Add CRUD Methods из пакета SQL Refactor Studio. Выбираем в OE таблицу, далее в контекстном меню выбираем пункт SQL Refactor Studio -> Add CRUD Methods.
В появившемся диалоге выбираем какие методы нам нужно создать. При необходимости меняем названия методов. Жмем Next. При желании можно настроить права на процедуры, для этого нужно отметить нужные роли. Нажимаем Finish и получаем скрипт с хранимыми процедурами. Выполняем скрипт.
IF (object_id(N'dbo.Customer_Create') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_Create as return 0') END GO -- ============================================= -- -- dbo.Customer_Create -- -- Create method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_Create @CustomerID NCHAR(5) ,@CompanyName NVARCHAR(40) ,@ContactName NVARCHAR(30) = NULL ,@ContactTitle NVARCHAR(30) = NULL ,@Address NVARCHAR(60) = NULL ,@PostalCode NVARCHAR(10) = NULL ,@Phone NVARCHAR(24) = NULL ,@Fax NVARCHAR(24) = NULL ,@CityId INT = NULL AS BEGIN SET NOCOUNT ON INSERT INTO dbo.Customer ( CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,PostalCode ,Phone ,Fax ,CityId ) VALUES ( @CustomerID ,@CompanyName ,@ContactName ,@ContactTitle ,@Address ,@PostalCode ,@Phone ,@Fax ,@CityId ) RETURN 0 END /* declare @CustomerID NChar(5), @CompanyName NVarChar(40), @ContactName NVarChar(30), @ContactTitle NVarChar(30), @Address NVarChar(60), @PostalCode NVarChar(10), @Phone NVarChar(24), @Fax NVarChar(24), @CityId Int select @CustomerID = 'CustomerID', @CompanyName = 'CompanyName', @ContactName = 'ContactName', @ContactTitle = 'ContactTitle', @Address = 'Address', @PostalCode = 'PostalCode', @Phone = 'Phone', @Fax = 'Fax', @CityId = null exec dbo.Customer_Create @CustomerID = @CustomerID, @CompanyName = @CompanyName, @ContactName = @ContactName, @ContactTitle = @ContactTitle, @Address = @Address, @PostalCode = @PostalCode, @Phone = @Phone, @Fax = @Fax, @CityId = @CityId */ GO IF (object_id(N'dbo.Customer_Get') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_Get as return 0') END GO -- ============================================= -- -- dbo.Customer_Get -- -- Read method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_Get @CustomerID NCHAR(5) AS BEGIN SET NOCOUNT ON SELECT CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,PostalCode ,Phone ,Fax ,CityId FROM dbo.Customer WHERE CustomerID = @CustomerID RETURN 0 END /* declare @CustomerID NChar(5) select @CustomerID = ? exec dbo.Customer_Get @CustomerID = @CustomerID */ GO IF (object_id(N'dbo.Customer_Save') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_Save as return 0') END GO -- ============================================= -- -- dbo.Customer_Save -- -- Update method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_Save @CustomerID NCHAR(5) ,@CompanyName NVARCHAR(40) ,@ContactName NVARCHAR(30) = NULL ,@ContactTitle NVARCHAR(30) = NULL ,@Address NVARCHAR(60) = NULL ,@PostalCode NVARCHAR(10) = NULL ,@Phone NVARCHAR(24) = NULL ,@Fax NVARCHAR(24) = NULL ,@CityId INT = NULL AS BEGIN SET NOCOUNT ON UPDATE t SET t.CompanyName = @CompanyName ,t.ContactName = @ContactName ,t.ContactTitle = @ContactTitle ,t.Address = @Address ,t.PostalCode = @PostalCode ,t.Phone = @Phone ,t.Fax = @Fax ,t.CityId = @CityId FROM dbo.Customer AS t WHERE t.CustomerID = @CustomerID RETURN 0 END /* set nocount on set quoted_identifier, ansi_nulls, ansi_warnings, arithabort, concat_null_yields_null, ansi_padding on set numeric_roundabort off set transaction isolation level read uncommitted declare @CustomerID NChar(5), @CompanyName NVarChar(40), @ContactName NVarChar(30), @ContactTitle NVarChar(30), @Address NVarChar(60), @PostalCode NVarChar(10), @Phone NVarChar(24), @Fax NVarChar(24), @CityId Int select @CustomerID = 'CustomerID', @CompanyName = 'CompanyName', @ContactName = 'ContactName', @ContactTitle = 'ContactTitle', @Address = 'Address', @PostalCode = 'PostalCode', @Phone = 'Phone', @Fax = 'Fax', @CityId = null begin try begin tran exec dbo.Customer_Save @CustomerID = @CustomerID, @CompanyName = @CompanyName, @ContactName = @ContactName, @ContactTitle = @ContactTitle, @Address = @Address, @PostalCode = @PostalCode, @Phone = @Phone, @Fax = @Fax, @CityId = @CityId select t.* from dbo.Customer as t where t.CustomerID = @CustomerID if @@trancount > 0 rollback tran end try begin catch if @@trancount > 0 rollback tran declare @err nvarchar(2000) set @err = 'login: ' + suser_sname() + char(10) + 'ErrorNumber: ' + cast(isnull(error_number(), 0) as varchar) + char(10) + 'ErrorProcedure: ' + isnull(error_procedure(), '') + char(10) + 'ErrorLine: ' + cast(isnull(error_line(), 0) as varchar) + char(10) + 'ErrorMessage: ' + isnull(error_message(), '') + char(10) + 'Date: ' + cast(getdate() as varchar) + char(10) print @err raiserror(@err, 16, 1) end catch */ GO IF (object_id(N'dbo.Customer_Del') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_Del as return 0') END GO -- ============================================= -- -- dbo.Customer_Del -- -- Delete method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_Del @CustomerID NCHAR(5) AS BEGIN SET NOCOUNT ON BEGIN TRY BEGIN TRAN /* uncomment if needed delete from dbo.CustomerCustomerDemo where CustomerID = ? delete from dbo.Orders where CustomerID = ? */ DELETE FROM dbo.Customer WHERE CustomerID = @CustomerID COMMIT TRAN END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRAN -- catch exception (add you code here) DECLARE @err NVARCHAR(2000) SET @err = ERROR_MESSAGE() RAISERROR (@err, 16, 1) END CATCH RETURN 0 END /* declare @CustomerID NChar(5) select @CustomerID = ? exec dbo.Customer_Del @CustomerID = @CustomerID */ GO IF (object_id(N'dbo.Customer_List') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_List as return 0') END GO -- ============================================= -- -- dbo.Customer_List -- -- List method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_List AS BEGIN SET NOCOUNT ON SELECT CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,PostalCode ,Phone ,Fax ,CityId FROM dbo.Customer /* uncomment if needed left join dbo.City as t1 on t1.CityId = t.CityId */ RETURN 0 END /* exec dbo.Customer_List */ GO
Если Вам нужно поправить тело генерируемых процедур, заходим в настройки.
Каждая процедура представляет собой шаблон T4. Про T4 можно почитать тут и тут.
Рефакторинг «Введение триггера для накопления исторических данных»
Эта операция позволяет ввести новый триггер, предназначенный для накопления информации об изменениях в данных в целях изучения истории внесения изменений или проведения аудита.
Необходимость в применении операции «Введение триггера для накопления исторических данных» в основном обусловлена требованием передать функции отслеживания изменений в данных самой базе данных. Такой подход гарантирует, что в случае модификации важных данных в любом внешнем приложении это изменение можно будет отследить и подвергнуть аудиту.
Единственным недостатком, на мой взгляд, является тот факт что наличие триггера будет увеличивать время выполнения операции DML.
Как альтернативу данному методу можно рассмотреть Change Data Capture (работает асинхронно, тем самым не увеличивает время операции, но имеет ряд особенностей).
Давайте для таблицы dbo.Customer применим данный рефакторинг.
Выбираем таблицу в OE, выбираем в контекстном меню пункт SQL Refactor Studio — Introduce Trigger for History. Выбираем поля таблицы для отслеживания изменений.
Жмем Next. Изменяем при необходимости имя создаваемой таблицы и триггера.
Жмем Finish и получаем скрипт. Скрипт создает триггер и таблицу для хранения истории изменений.
CREATE TABLE [dbo].[CustomerHistory] ( [id] [bigint] IDENTITY NOT NULL ,[action_type] [char](1) NOT NULL ,[modified_date] [datetime] CONSTRAINT [DF_CustomerHistory_modified_date] DEFAULT getdate() ,[modified_login] [sysname] CONSTRAINT [DF_CustomerHistory_modified_login] DEFAULT suser_sname() ,[host_name] [nvarchar](128) CONSTRAINT [DF_CustomerHistory_host_name] DEFAULT host_name() ,[program_name] [nvarchar](128) CONSTRAINT [DF_CustomerHistory_program_name] DEFAULT program_name() ,[CompanyName_old] [nvarchar](40) ,[CompanyName_new] [nvarchar](40) ,[ContactName_old] [nvarchar](30) ,[ContactName_new] [nvarchar](30) ,[ContactTitle_old] [nvarchar](30) ,[ContactTitle_new] [nvarchar](30) ,[Address_old] [nvarchar](60) ,[Address_new] [nvarchar](60) ,[PostalCode_old] [nvarchar](10) ,[PostalCode_new] [nvarchar](10) ,[Phone_old] [nvarchar](24) ,[Phone_new] [nvarchar](24) ,[Fax_old] [nvarchar](24) ,[Fax_new] [nvarchar](24) ,[CityId_old] [int] ,[CityId_new] [int] ,[CustomerID_old] [nchar](5) ,[CustomerID_new] [nchar](5) ,CONSTRAINT [PK_CustomerHistory] PRIMARY KEY ([id]) ) GO CREATE TRIGGER [dbo].[trg_CustomerHistory] ON [dbo].[Customer] AFTER INSERT, DELETE, UPDATE AS SET NOCOUNT ON DECLARE @action_type CHAR(1) IF EXISTS (SELECT *FROM inserted) AND EXISTS (SELECT * FROM deleted) SET @action_type = 'U' ELSE IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) SET @action_type = 'I' ELSE IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) SET @action_type = 'D' INSERT INTO dbo.CustomerHistory ( action_type ,CompanyName_old ,CompanyName_new ,ContactName_old ,ContactName_new ,ContactTitle_old ,ContactTitle_new ,Address_old ,Address_new ,PostalCode_old ,PostalCode_new ,Phone_old ,Phone_new ,Fax_old ,Fax_new ,CityId_old ,CityId_new ,CustomerID_old ,CustomerID_new ) SELECT @action_type ,d.CompanyName ,i.CompanyName ,d.ContactName ,i.ContactName ,d.ContactTitle ,i.ContactTitle ,d.Address ,i.Address ,d.PostalCode ,i.PostalCode ,d.Phone ,i.Phone ,d.Fax ,i.Fax ,d.CityId ,i.CityId ,d.CustomerID ,i.CustomerID FROM inserted i FULL OUTER JOIN deleted d ON (i.CustomerID = d.CustomerID) GO
На этом пока всё. Надеюсь информация Вам пригодится и в Ваших базах данных всегда будет полный порядок.
Удачи!
Полезные ресурсы
- http://databaserefactoring.com
- Скотт Амблер, Прамодкумар Дж. Садаладж Рефакторинг баз данных. Эволюционное проектирование
- Мартин Фаулер Рефакторинг.Улучшение существующего кода
- Плагин для SSMS SQL Refactor Studio
ссылка на оригинал статьи http://habrahabr.ru/post/258629/
Добавить комментарий