Кто-нибудь добился успеха в модульном тестировании хранимых процедур SQL?

Мы обнаружили, что модульные тесты, которые мы написали для нашего кода C#/C++, действительно окупились.
Но у нас все еще есть тысячи линий бизнес-логики в хранимых процедурах, которые только действительно проверяются в гневе, когда наш продукт выкатывается большому количеству пользователей.

Хуже всего то, что некоторые из этих хранимых процедур оказываются очень длинными из-за снижения производительности при передаче временных таблиц между SPs. Это помешало нам от рефакторинга, чтобы сделать код проще.

Мы сделали несколько попыток построить модульные тесты вокруг некоторых из наших ключевых хранимых процедур (в первую очередь тестирования производительности), но обнаружили, что настройка тестовых данных для этих тестов действительно трудно. Например, мы в конечном итоге копируем вокруг тестовых баз данных. В дополнение к этому, тесты в конечном итоге очень чувствительны к изменениям, и даже самое маленькое изменение в сохраненном proc. или таблица требует большого количества изменений в тестах. Таким образом, после того, как многие сборки ломаются из-за этих тестов базы данных, периодически терпящих неудачу, мы просто должны были вытащить их из процесса сборки.

Итак, основная часть моих вопросов: кто-нибудь когда-нибудь успешно писал модульные тесты для своих хранимых процедур?

Вторая часть моих вопросов заключается в том, будет ли модульное тестирование/проще с linq?

Я думал, что вместо того, чтобы создавать таблицы тестовых данных, вы можете просто создать коллекцию тестовых объектов и протестировать код linq в ситуации “linq to objects”? (Я совершенно новый для linq, поэтому не знаю, будет ли это вообще работать)

16 ответов

  1. Вы пробовали DBUnit ? Он предназначен для модульного тестирования базы данных, и только вашей базы данных, без необходимости проходить через код C#.

  2. LINQ упростит это только в том случае, если вы удалите логику из хранимых процедур и повторно примените ее в качестве запросов linq. Что было бы намного надежнее и легче проверить, определенно. Тем не менее, похоже, что ваши требования исключат это.

    TL; DR: ваш дизайн имеет проблемы.

  3. Если вы подумаете о том, какой код, как правило, способствует модульному тестированию: небольшие высокосвязные и низко связанные подпрограммы, то вы должны в значительной степени быть в состоянии увидеть, где, по крайней мере, часть проблемы может быть.

    В моем циничном мире хранимые процедуры являются частью давней попытки RDBMS убедить вас переместить обработку вашего бизнеса в базу данных, что имеет смысл, когда вы считаете, что стоимость лицензии сервера, как правило, связана с такими вещами, как подсчет процессоров. Чем больше материала вы запускаете в своей базе данных, тем больше они делают из вас.

    Но у меня сложилось впечатление, что вы на самом деле больше озабочены производительностью, которая на самом деле не является сохранением модульного тестирования вообще. Модульные тесты должны быть достаточно атомарными и предназначены для проверки поведения, а не производительности. И в этом случае вам почти наверняка понадобится загрузка производственного класса для проверки планов запросов.

    Я думаю, что вам нужен другой класс среды тестирования. Я бы предложил копию производства как самую простую, предполагая, что безопасность не является проблемой. Затем для каждого выпуска-кандидата, вы начинаете с предыдущей версии, переносите с помощью процедур выпуска (которые дадут им хорошее тестирование в качестве побочного эффекта) и запускаете тайминги.

    Что-то вроде того.

  4. Модульное тестирование кода C#, вызывающего SPs.
    Мы строим скрипты, создаем чистые тестовые базы данных.
    И более большие одни мы прикрепляем и разделяем во время приспособления теста.
    Эти тесты могут занять несколько часов, но я думаю, что это стоит того.

  5. Мы используем DataFresh для отката изменений между каждым тестом, тогда тестирование sprocs относительно легко.

    Чего еще не хватает, так это инструментов покрытия кода.

  6. Но у меня сложилось впечатление, что вы на самом деле больше озабочены производительностью, которая на самом деле не является сохранением модульного тестирования вообще. Модульные тесты должны быть достаточно атомарными и предназначены для проверки поведения, а не производительности. И в этом случае вам почти наверняка понадобится загрузка производственного класса для проверки планов запросов.

    Я думаю, что здесь есть две совершенно разные области тестирования: производительность и фактическая логика хранимых процедур.

    Я привел пример тестирования производительности БД в прошлом, и, к счастью, мы достигли точки, где производительность достаточно хорошая.

    Я полностью согласен с тем, что ситуация со всей бизнес-логикой в базе данных является плохой, но это то, что мы унаследовали от до того, как большинство наших разработчиков присоединились к компании.

    Тем не менее, теперь мы принимаем модель веб-служб для наших новых функций, и мы стараемся избегать хранимых процедур, насколько это возможно, сохраняя логику в коде C# и запуская SQLCommands в базе данных (хотя linq теперь был бы предпочтительным методом). Существует еще некоторое использование существующих SPs, поэтому я думал о ретроспективном модульном тестировании их.

  7. Один из вариантов пересчитать код (я признаю уродливый хак) будет генерировать его через CPP (препроцессор C) M4 (никогда не пробовал) или тому подобное. У меня есть проект, который делает именно это, и он на самом деле в основном работоспособен.

    Единственный случай, который я думаю, что может быть верным, это 1) как альтернатива KLOC+ хранимым процедурам и 2) и это мои случаи, когда смысл проекта заключается в том, чтобы увидеть, как далеко (в безумие) вы можете продвинуть технологию.

  8. Ключ к тестированию хранимых процедур-это написание скрипта, который заполняет пустую базу данных данными, заранее запланированными для обеспечения согласованного поведения при вызове хранимых процедур.

    Я должен отдать свой голос за то, чтобы отдать предпочтение хранимым процедурам и разместить вашу бизнес-логику там, где я (и большинство БД) думаю, что она принадлежит, в базе данных.

    Я знаю, что мы, инженеры-программисты, хотим, чтобы красиво переработанный код, написанный на нашем любимом языке, содержал всю нашу важную логику, но реалии производительности в больших объемных системах и критический характер целостности данных требуют от нас некоторых компромиссов. Sql-код может быть уродливым, повторяющимся и трудным для тестирования, но я не могу представить себе трудности настройки базы данных без полного контроля над дизайном запросов.

    Я часто вынужден полностью перепроектировать запросы, включать изменения в модель данных, чтобы заставить вещи работать в приемлемое время. С помощью хранимых процедур я могу гарантировать, что изменения будут прозрачными для вызывающего, так как хранимая процедура обеспечивает такую превосходную инкапсуляцию.

  9. О, боже. sproc не поддаются (автоматизированному) модульному тестированию. I sort-of «unit test» my complex sprocs by writing tests in t-sql batch files and hand checking the output of the print statements and the results.

  10. Проблема модульного тестирования любого вида программирования, связанного с данными, заключается в том, что для начала необходимо иметь надежный набор тестовых данных. Многое также зависит от сложности сохраненного proc и того, что он делает. Было бы очень трудно автоматизировать модульное тестирование для очень сложной процедуры, которая модифицировала многие таблицы.

    Некоторые из других плакатов отметили некоторые простые способы автоматизации тестирования вручную, а также некоторые средства, которые можно использовать с SQL Server. На стороне Oracle, гуру PL/SQL Стивен Feuerstein работал над бесплатным инструментом модульного тестирования для хранимых процедур PL/SQL под названием utPLSQL.

    Тем не менее, он бросил эти усилия, а затем пошел на коммерческий с тестером кода Quest для PL/SQL. Quest предлагает бесплатную загружаемую пробную версию. Я на грани того, чтобы попробовать его; мое понимание заключается в том, что он хорошо справляется с накладными расходами при настройке платформы тестирования, так что вы можете сосредоточиться только на самих тестах, и он сохраняет тесты, чтобы вы могли повторно использовать их в регрессионном тестировании, одном из больших преимуществ тестовой разработки.Кроме того, предполагается, что он хорош не только для проверки выходной переменной и имеет возможность проверки изменений данных, но мне все еще нужно взглянуть поближе. Я подумал, что эта информация может быть полезна для пользователей Oracle.

  11. Я столкнулся с этой же проблемой некоторое время назад и обнаружил, что если я создал простой абстрактный базовый класс для доступа к данным, который позволил мне ввести соединение и транзакцию, я мог бы модульно протестировать мои спроки, чтобы увидеть, если они сделали работу в SQL, что я попросил их сделать, а затем откат, так что ни один из тестовых данных не остается в БД.

    Это было лучше, чем обычно «запустите сценарий для установки моей тестовой БД, а затем после запуска тестов выполните очистку от нежелательных данных/тестовых данных». Это также было ближе к модульному тестированию, потому что эти тесты могут быть запущены в одиночку без большого количества «все в БД должно быть»просто так», прежде чем я запущу эти тесты».

    Вот фрагмент абстрактного базового класса, используемого для доступа к данным

    Public MustInherit Class Repository(Of T As Class)
        Implements IRepository(Of T)
    
        Private mConnectionString As String = ConfigurationManager.ConnectionStrings("Northwind.ConnectionString").ConnectionString
        Private mConnection As IDbConnection
        Private mTransaction As IDbTransaction
    
        Public Sub New()
            mConnection = Nothing
            mTransaction = Nothing
        End Sub
    
        Public Sub New(ByVal connection As IDbConnection, ByVal transaction As IDbTransaction)
            mConnection = connection
            mTransaction = transaction
        End Sub
    
        Public MustOverride Function BuildEntity(ByVal cmd As SqlCommand) As List(Of T)
    
        Public Function ExecuteReader(ByVal Parameter As Parameter) As List(Of T) Implements IRepository(Of T).ExecuteReader
            Dim entityList As List(Of T)
            If Not mConnection Is Nothing Then
                Using cmd As SqlCommand = mConnection.CreateCommand()
                    cmd.Transaction = mTransaction
                    cmd.CommandType = Parameter.Type
                    cmd.CommandText = Parameter.Text
                    If Not Parameter.Items Is Nothing Then
                        For Each param As SqlParameter In Parameter.Items
                            cmd.Parameters.Add(param)
                        Next
                    End If
                    entityList = BuildEntity(cmd)
                    If Not entityList Is Nothing Then
                        Return entityList
                    End If
                End Using
            Else
                Using conn As SqlConnection = New SqlConnection(mConnectionString)
                    Using cmd As SqlCommand = conn.CreateCommand()
                        cmd.CommandType = Parameter.Type
                        cmd.CommandText = Parameter.Text
                        If Not Parameter.Items Is Nothing Then
                            For Each param As SqlParameter In Parameter.Items
                                cmd.Parameters.Add(param)
                            Next
                        End If
                        conn.Open()
                        entityList = BuildEntity(cmd)
                        If Not entityList Is Nothing Then
                            Return entityList
                        End If
                    End Using
                End Using
            End If
    
            Return Nothing
        End Function
    End Class
    

    далее вы увидите пример класса доступа к данным, используя вышеуказанную базу, чтобы получить список продуктов

    Public Class ProductRepository
        Inherits Repository(Of Product)
        Implements IProductRepository
    
        Private mCache As IHttpCache
    
        'This const is what you will use in your app
        Public Sub New(ByVal cache As IHttpCache)
            MyBase.New()
            mCache = cache
        End Sub
    
        'This const is only used for testing so we can inject a connectin/transaction and have them roll'd back after the test
        Public Sub New(ByVal cache As IHttpCache, ByVal connection As IDbConnection, ByVal transaction As IDbTransaction)
            MyBase.New(connection, transaction)
            mCache = cache
        End Sub
    
        Public Function GetProducts() As System.Collections.Generic.List(Of Product) Implements IProductRepository.GetProducts
            Dim Parameter As New Parameter()
            Parameter.Type = CommandType.StoredProcedure
            Parameter.Text = "spGetProducts"
            Dim productList As List(Of Product)
            productList = MyBase.ExecuteReader(Parameter)
            Return productList
        End Function
    
        'This function is used in each class that inherits from the base data access class so we can keep all the boring left-right mapping code in 1 place per object
        Public Overrides Function BuildEntity(ByVal cmd As System.Data.SqlClient.SqlCommand) As System.Collections.Generic.List(Of Product)
            Dim productList As New List(Of Product)
            Using reader As SqlDataReader = cmd.ExecuteReader()
                Dim product As Product
                While reader.Read()
                    product = New Product()
                    product.ID = reader("ProductID")
                    product.SupplierID = reader("SupplierID")
                    product.CategoryID = reader("CategoryID")
                    product.ProductName = reader("ProductName")
                    product.QuantityPerUnit = reader("QuantityPerUnit")
                    product.UnitPrice = reader("UnitPrice")
                    product.UnitsInStock = reader("UnitsInStock")
                    product.UnitsOnOrder = reader("UnitsOnOrder")
                    product.ReorderLevel = reader("ReorderLevel")
                    productList.Add(product)
                End While
                If productList.Count > 0 Then
                    Return productList
                End If
            End Using
            Return Nothing
        End Function
    End Class
    

    И теперь в вашем модульном тесте вы также можете наследовать от очень простого базового класса, который выполняет вашу работу по настройке / откату — или сохранить это на основе модульного теста

    ниже приведен простой базовый класс тестирования, который я использовал

    Imports System.Configuration
    Imports System.Data
    Imports System.Data.SqlClient
    Imports Microsoft.VisualStudio.TestTools.UnitTesting
    
    Public MustInherit Class TransactionFixture
        Protected mConnection As IDbConnection
        Protected mTransaction As IDbTransaction
        Private mConnectionString As String = ConfigurationManager.ConnectionStrings("Northwind.ConnectionString").ConnectionString
    
        <TestInitialize()> _
        Public Sub CreateConnectionAndBeginTran()
            mConnection = New SqlConnection(mConnectionString)
            mConnection.Open()
            mTransaction = mConnection.BeginTransaction()
        End Sub
    
        <TestCleanup()> _
        Public Sub RollbackTranAndCloseConnection()
            mTransaction.Rollback()
            mTransaction.Dispose()
            mConnection.Close()
            mConnection.Dispose()
        End Sub
    End Class
    

    и, наконец, — ниже приведен простой тест с использованием этого тестового базового класса, который показывает, как протестировать весь цикл CRUD, чтобы убедиться, что все sprocs делают свою работу, и что ваш ado.net код правильно выполняет сопоставление слева направо

    Я знаю, что это не тестирует sproc «spGetProducts», используемый в приведенном выше примере доступа к данным, но вы должны увидеть силу этого подхода к модульному тестированию sproc

    Imports SampleApplication.Library
    Imports System.Collections.Generic
    Imports Microsoft.VisualStudio.TestTools.UnitTesting
    
    <TestClass()> _
    Public Class ProductRepositoryUnitTest
        Inherits TransactionFixture
    
        Private mRepository As ProductRepository
    
        <TestMethod()> _
        Public Sub Should-Insert-Update-And-Delete-Product()
            mRepository = New ProductRepository(New HttpCache(), mConnection, mTransaction)
            '** Create a test product to manipulate throughout **'
            Dim Product As New Product()
            Product.ProductName = "TestProduct"
            Product.SupplierID = 1
            Product.CategoryID = 2
            Product.QuantityPerUnit = "10 boxes of stuff"
            Product.UnitPrice = 14.95
            Product.UnitsInStock = 22
            Product.UnitsOnOrder = 19
            Product.ReorderLevel = 12
            '** Insert the new product object into SQL using your insert sproc **'
            mRepository.InsertProduct(Product)
            '** Select the product object that was just inserted and verify it does exist **'
            '** Using your GetProductById sproc **'
            Dim Product2 As Product = mRepository.GetProduct(Product.ID)
            Assert.AreEqual("TestProduct", Product2.ProductName)
            Assert.AreEqual(1, Product2.SupplierID)
            Assert.AreEqual(2, Product2.CategoryID)
            Assert.AreEqual("10 boxes of stuff", Product2.QuantityPerUnit)
            Assert.AreEqual(14.95, Product2.UnitPrice)
            Assert.AreEqual(22, Product2.UnitsInStock)
            Assert.AreEqual(19, Product2.UnitsOnOrder)
            Assert.AreEqual(12, Product2.ReorderLevel)
            '** Update the product object **'
            Product2.ProductName = "UpdatedTestProduct"
            Product2.SupplierID = 2
            Product2.CategoryID = 1
            Product2.QuantityPerUnit = "a box of stuff"
            Product2.UnitPrice = 16.95
            Product2.UnitsInStock = 10
            Product2.UnitsOnOrder = 20
            Product2.ReorderLevel = 8
            mRepository.UpdateProduct(Product2) '**using your update sproc
            '** Select the product object that was just updated to verify it completed **'
            Dim Product3 As Product = mRepository.GetProduct(Product2.ID)
            Assert.AreEqual("UpdatedTestProduct", Product2.ProductName)
            Assert.AreEqual(2, Product2.SupplierID)
            Assert.AreEqual(1, Product2.CategoryID)
            Assert.AreEqual("a box of stuff", Product2.QuantityPerUnit)
            Assert.AreEqual(16.95, Product2.UnitPrice)
            Assert.AreEqual(10, Product2.UnitsInStock)
            Assert.AreEqual(20, Product2.UnitsOnOrder)
            Assert.AreEqual(8, Product2.ReorderLevel)
            '** Delete the product and verify it does not exist **'
            mRepository.DeleteProduct(Product3.ID)
            '** The above will use your delete product by id sproc **'
            Dim Product4 As Product = mRepository.GetProduct(Product3.ID)
            Assert.AreEqual(Nothing, Product4)
        End Sub
    
    End Class
    

    Я знаю, что это длинный пример, но это помогло иметь многоразовый класс для работы с доступом к данным и еще один многоразовый класс для моего тестирования, поэтому мне не пришлось снова и снова выполнять работу по настройке/демонтажу 😉

  12. Я нахожусь в той же ситуации, что и оригинальный плакат. Это сводится к производительности по сравнению с тестируемостью. Я склоняюсь к тестируемости (заставьте его работать, сделайте его правильным, сделайте его быстрым), что предполагает сохранение бизнес-логики из базы данных. Базам данных не только не хватает платформ тестирования, конструкций факторинга кода, а также инструментов анализа кода и навигации, найденных в таких языках, как Java, но и высоко факторизованный код базы данных также медленный (где высоко факторизованный код Java не является).

    Тем не менее, я признаю силу обработки набора баз данных. При правильном использовании SQL может сделать некоторые невероятно мощные вещи с очень небольшим количеством кода. Таким образом, я в порядке с некоторой логикой, основанной на наборе, живущей в базе данных, хотя я все еще буду делать все, что могу, чтобы юнит-тестировать ее.

    С другой стороны, кажется, что очень длинный и процедурный код базы данных часто является симптомом чего-то другого, и я думаю, что такой код может быть преобразован в тестируемый код без ущерба для производительности. Теория заключается в том, что такой код часто представляет пакетные процессы, которые периодически обрабатывают большие объемы данных.Если эти пакетные процессы должны быть преобразованы в более мелкие фрагменты бизнес-логики в реальном времени, которая выполняется при изменении входных данных, эта логика может выполняться на среднем уровне (где она может быть протестирована) без снижения производительности (так как работа выполняется небольшими фрагментами в реальном времени). Как побочный эффект, это также исключает длинные петли обратной связи обработки ошибок пакетного процесса. Конечно, этот подход не будет работать во всех случаях, но он может работать в некоторых.Кроме того, если в вашей системе есть тонны такого непроверяемого кода базы данных пакетной обработки, путь к спасению может быть долгим и трудным. YMMV.

  13. Лучший вопрос.

    У меня похожие проблемы, и я встал на путь наименьшего сопротивления (для меня, во всяком случае).

    Есть куча других решений, о которых другие упоминали. Многие из них лучше / более чистые / более подходящие для других.

    Я уже использовал Testdriven.NET/MbUnit чтобы проверить мой C#, поэтому я просто добавил тесты к каждому проекту, чтобы вызвать хранимые процедуры, используемые этим приложением.

    Я знаю, знаю. Это звучит ужасно, но что мне нужно, это подняться с земли с некоторыми испытаниями, и идти оттуда. Этот подход означает, что, хотя мой охват низок, я тестирую некоторые сохраненные procs в то же время, как я тестирую код, который будет вызывать их. В этом есть определенная логика.

  14. Я провожу юнит-тесты для бедных. Если я ленив, тест — это всего лишь несколько допустимых вызовов с потенциально проблемными значениями параметров.

    /*
    
    --setup
    Declare @foo int Set @foo = (Select top 1 foo from mytable)
    
    --test
    execute wish_I_had_more_Tests @foo
    
    --look at rowcounts/look for errors
    If @@rowcount=1 Print 'Ok!' Else Print 'Nokay!'
    
    --Teardown
    Delete from mytable where foo = @foo
    */
    create procedure wish_I_had_more_Tests
    as
    select....